Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Party Fix query logic
<?php
namespace App\Http\Controllers\Api;
use App\Http\Controllers\Traits\APIResponser;
use App\Http\Controllers\Controller;
use App\Http\Resources\ElectionRegionCollection;
use App\Http\Resources\PartyCollection;
use App\Models\AmyothaElectionRegion;
use App\Models\District;
use App\Models\Party;
use App\Models\PyiThuElectionRegion;
use App\Models\Representative;
use App\Models\StateElectionRegion;
use App\Models\StateEthnicElectionRegion;
use App\Models\SubTownship;
use App\Models\Township;
use App\Utils\FontConverter;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Http\Request;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Pagination\Paginator;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Yajra\Datatables\Datatables;
class PartyApiController extends Controller
{
use APIResponser;
function getAllParty()
{
$parties = Party::with('representatives')->get();
return $this->respondCollection('success', $parties);
}
function getAllPartyWithFilter(Request $request)
{
if ($request->input('search') != null) {
$parties = $this->filterPartyWithInput($request);
} else {
$parties = $this->filterParty($request);
}
return $parties;
return response()->json(new PartyCollection($parties));
}
function showParty($partId)
{
$party = Party::find($partId);
return $this->respondCollection('success party', $party);
}
function electionRegionParty($partyId)
{
//TODO - re-check query logic coz region တွေအကုန်ထွက်နေတယ်။
//TODO - need todo refector
$stateElectionRegions = StateElectionRegion::with(['representative' => function ($query) use ($partyId) {
$query->where('party_id', $partyId);
}, 'representative.party'])->get();
$stateElectionRegions = $this->checkRepresentativeNull($stateElectionRegions);
$pyiThuElectionRegions = PyiThuElectionRegion::with(['representative' => function ($query) use ($partyId) {
$query->where('party_id', $partyId)->where('party_id', '!=', 0);
}, 'representative.party'])->get();
$pyiThuElectionRegions = $this->checkRepresentativeNull($pyiThuElectionRegions);
$amyothaElectionRegions = AmyothaElectionRegion::with(['representative' => function ($query) use ($partyId) {
$query->where('party_id', $partyId);
}, 'representative.party'])->get();
$amyothaElectionRegions = $this->checkRepresentativeNull($amyothaElectionRegions);
$stateEthnicElectionRegions = StateEthnicElectionRegion::with(['representative' => function ($query) use ($partyId) {
$query->where('party_id', $partyId)->where('party_id', '!=', 0);
}, 'representative.party'])->get();
$stateEthnicElectionRegions = $this->checkRepresentativeNull($stateEthnicElectionRegions);
$mergeStateAndAmyotarElectionRegion = $stateElectionRegions->toBase()->merge($amyothaElectionRegions); //merge state and amyotha election region.
$mergePyiThuElectionRegion = $mergeStateAndAmyotarElectionRegion->toBase()->merge($pyiThuElectionRegions); //merge pyithu election region.
$allElectionRegion = $mergePyiThuElectionRegion->toBase()->merge($stateEthnicElectionRegions); //merge state ethnic election region.
return response()->json(ElectionRegionCollection::collection($allElectionRegion));
}
function getAllPartyForDataTable()
{
return Datatables::of(Party::with('partyStatistic')->get())
->addColumn('current_voting_count', function ($row) {
$pyiThuElectionCount = $row->partyStatistic->pyi_thu_election_result_count ?? 0;
$amyoThaElectionCount = $row->partyStatistic->amyotha_election_result_count ?? 0;
$stateElectionCount = $row->partyStatistic->state_election_result_count ?? 0;
$stateEthnicElectionCount = $row->partyStatistic->state_ethnic_election_result_count ?? 0;
return "ပြည်သူလွှတ်တော် count $pyiThuElectionCount - အမျိုးသားလွှတ်တော် count $amyoThaElectionCount - တိုင်းဒေသကြီးလွှတ်တော် count $stateElectionCount - တိုင်းဒေသကြီး(တိုင်းရင်းသား)လွှတ်တော် count $stateEthnicElectionCount";
})->addColumn('action', function ($row) {
$viewBtn = '<a href="' . route('party.show', ['partyId' => $row->id]) . '" class="edit btn btn-primary">View</a>';
return $viewBtn; // . '&nbsp;' . $deleteBtn;
})->make(true);
}
/**
* @param Request $request
* @return mixed
*/
private function filterParty($request)
{
$partiesQuery = $this->partiesBaseQuery();
//if condition true/false flag
$withAllFilter = $request->input('divisionId') != 0 && $request->input('townshipId') != 0 &&
$request->input('electionType') != null ? true : false;
$withDivisionIdTownshipId = $request->input('divisionId') != 0 && $request->input('townshipId') != 0 ? true : false;
$withDivisionId = $request->input('divisionId') != 0 ? true : false;
$withDivisionIdElectionType = $request->input('divisionId') != 0 && $request->input('electionType') != null ? true : false;
$withElectionType = $request->input('electionType') != null ? true : false;
if ($withAllFilter) {
// division,townships and electionType filter
$representativesPartyIds = $this->composeAllRepresentativePartyIdWithTownship($request->input('electionType'), $request->input('townshipId'), $request->input('divisionId'));
$partiesQuery->whereIn('parties.id', $representativesPartyIds->pluck('party_id')->toArray());
$parties = $this->composeParties($request, $partiesQuery);
} elseif ($withDivisionIdElectionType) {
// division and election type filter
$electionRegionModel = getElectionTypeModel($request->input('electionType'));
$partiesQuery->where('election_region_type', $electionRegionModel)
->where('representatives.division_id', $request->input('divisionId'));
$parties = $this->composeParties($request, $partiesQuery);
} elseif ($withDivisionIdTownshipId) {
// division and townships filter
$township = Township::where('id', $request->input('townshipId'))->first();
$district = District::where('id', $township->district_id)->first();
$representativesPartyIds = $this->composeAllRepresentativePartyId($request, $township, $district);
$partiesQuery->whereIn('parties.id', $representativesPartyIds->pluck('party_id')->toArray());
$parties = $this->composeParties($request, $partiesQuery);
} elseif ($withElectionType) {
// only electionType filter
$electionRegionModel = getElectionTypeModel($request->input('electionType'));
$partiesQuery->where('election_region_type', $electionRegionModel);
$parties = $this->composeParties($request, $partiesQuery);
} elseif ($withDivisionId) {
// only division filter
$partiesQuery->where('representatives.division_id', $request->input('divisionId'));
$parties = $this->composeParties($request, $partiesQuery);
} else {
// all
//TODO- this code block can be replace in 'composeParties' method.but do it later
$partiesQuery = $this->prepareFamousEthnicQuery($partiesQuery, $request->input('isFamous'), $request->input('isEthnic'));
$parties = $partiesQuery->join('parties', 'representatives.party_id', '=', 'parties.id')
->groupBy('party_id')
->paginate(50);
}
return $parties;
}
private function prepareFamousEthnicQuery($partiesQuery, $isFamous, $isEthnic)
{
if ($isFamous != 0) {
$partiesQuery->where('parties.is_famous', $isFamous);
} elseif ($isEthnic != 0) {
$partiesQuery->where('parties.is_ethnic', $isEthnic);
} else {
$partiesQuery->where('parties.is_famous', 0)->where('parties.is_ethnic', 0);
}
return $partiesQuery;
}
/**
* @param Request $request
* @return mixed
*/
private function filterPartyWithInput($request)
{
$partiesQuery = $this->partiesBaseQuery();
$searchingKeyword = FontConverter::UniConverter($request->input('search'));
$searchingField = $this->determinePartySearchColumn($searchingKeyword);
//if condition true/false flag
$searchWithAllFilter = $request->input('divisionId') != 0 && $request->input('townshipId') != 0 &&
$request->input('electionType') != null && $request->input('search') != null ? true : false;
$searchWithDivisionIdTownshipId = $request->input('divisionId') != 0 && $request->input('townshipId') != 0
&& $request->input('search') != null ? true : false;
$searchWithDivisionId = $request->input('divisionId') != 0 && $request->input('search') != null ? true : false;
$searchWithDivisionIdElectionType = $request->input('divisionId') != 0 && $request->input('electionType') != null && $request->input('search') != null ? true : false;
$searchWithElectionType = $request->input('electionType') != null && $request->input('search') != null ? true : false;
if ($searchWithAllFilter) {
$representativesPartyIds = $this->composeAllRepresentativePartyIdWithTownship($request->input('electionType'), $request->input('townshipId'), $request->input('divisionId'));
$partiesQuery->whereIn('parties.id', $representativesPartyIds->pluck('party_id')->toArray())
->where($searchingField, 'like', '%' . $searchingKeyword . '%');;
$parties = $this->composeParties($request, $partiesQuery);
} elseif ($searchWithDivisionIdTownshipId) {
$township = Township::where('id', $request->input('townshipId'))->first();
$district = District::where('id', $township->district_id)->first();
$representativesPartyIds = $this->composeAllRepresentativePartyId($request, $township, $district);
$partiesQuery->whereIn('parties.id', $representativesPartyIds->pluck('party_id')->toArray())
->where($searchingField, 'like', '%' . $searchingKeyword . '%');
$parties = $this->composeParties($request, $partiesQuery);
} elseif ($searchWithDivisionIdElectionType) {
// division and election type filter
$electionRegionModel = getElectionTypeModel($request->input('electionType'));
$partiesQuery->where('election_region_type', $electionRegionModel)
->where('representatives.division_id', $request->input('divisionId'))
->where($searchingField, 'like', '%' . $searchingKeyword . '%');
$parties = $this->composeParties($request, $partiesQuery);
} elseif ($searchWithElectionType) {
// only electionType filter
$electionRegionModel = getElectionTypeModel($request->input('electionType'));
$partiesQuery->where('election_region_type', $electionRegionModel)
->where($searchingField, 'like', '%' . $searchingKeyword . '%');
$parties = $this->composeParties($request, $partiesQuery);
} elseif ($searchWithDivisionId) {
// only division filter
$partiesQuery->where('representatives.division_id', $request->input('divisionId'))
->where($searchingField, 'like', '%' . $searchingKeyword . '%');
$parties = $this->composeParties($request, $partiesQuery);
} else {
// all
$partiesQuery->where($searchingField, 'like', '%' . $searchingKeyword . '%');
$partiesQuery = $this->prepareFamousEthnicQuery($partiesQuery, $request->input('isFamous'), $request->input('isEthnic'));
$parties = $partiesQuery->join('parties', 'representatives.party_id', '=', 'parties.id')
->groupBy('party_id')
->paginate(50);
}
return $parties;
}
/**
* @param $searchingKeyword
* @return string
*/
private function determinePartySearchColumn($searchingKeyword): string
{
if (isMyanmarSar($searchingKeyword)) {
return 'parties.burmese_name';
}
return 'parties.english_name';
}
/**
* @param $electionRegions
* @return mixed
*/
private function checkRepresentativeNull($electionRegions)
{
$electionRegions = $electionRegions->filter(function ($value, $key) {
return count($value->representative) != 0;
});
return $electionRegions;
}
function composeAllRepresentativePartyIdWithTownship($type, $townshipId, $divisionId)
{
$township = Township::where('id', $townshipId)->first();
$district = District::where('id', $township->district_id)->first();
$amyotha_election_region_ids = [$township->amyotha_election_region_id, $district->amyotha_election_region_id];
$electionRegionIdsQuery = "";
switch ($type) {
case 'pyi_thu_election':
$pyiThuElectionRegion = PyiThuElectionRegion::where('township_id', $townshipId)->first();
$pyiThuElectionRegionId = $pyiThuElectionRegion ? $pyiThuElectionRegion->id : -1;
$electionRegionIdsQuery = '(election_region_type = "App\\\\Models\\\\PyiThuElectionRegion" and election_region_id in (' . $pyiThuElectionRegionId . '))';
break;
case 'amyotha_election':
$subTownshipRegionIds = SubTownship::where('township_id', $townshipId)->where('amyotha_election_region_id', '!=', 0)->get(['amyotha_election_region_id']);
$amyotharElectionRegionIds = array_merge(
$subTownshipRegionIds->pluck('amyotha_election_region_id')->toArray(),
$amyotha_election_region_ids
);
$electionRegionIdsQuery = '(election_region_type = "App\\\\Models\\\\AmyothaElectionRegion" and election_region_id in (' . implode(",", $amyotharElectionRegionIds) . '))';
break;
case 'state_election':
$stateElectionRegion = StateElectionRegion::where('township_id', $townshipId)->get('id');
$stateElectionRegionIds = $stateElectionRegion ? $stateElectionRegion->pluck('id')->toArray() : -1;
$electionRegionIdsQuery = '(election_region_type = "App\\\\Models\\\\StateElectionRegion" and election_region_id in (' . implode(",", $stateElectionRegionIds) . '))';
break;
case 'state_ethnic_election':
$stateEthnicElectionRegion = StateEthnicElectionRegion::where('division_id', $divisionId)->get('id');
$stateEthnicElectionRegionIds = $stateEthnicElectionRegion ? $stateEthnicElectionRegion->pluck('id')->toArray() : -1;
$electionRegionIdsQuery = '(election_region_type = "App\\\\Models\\\\StateEthnicElectionRegion" and election_region_id in (' . implode(",", $stateEthnicElectionRegionIds) . '))';
break;
default:
break;
}
$representativesPartyIds = Db::table('representatives')
->select("party_id")
->WhereRaw($electionRegionIdsQuery)
->where('division_id', $divisionId)
->get();
return $representativesPartyIds;
}
/**
* @param $request
* @param $township
* @param $district
* @return \Illuminate\Support\Collection
*/
private function composeAllRepresentativePartyId($request, $township, $district)
{
$pyiThuElectionRegion = PyiThuElectionRegion::where('township_id', $township->id)->first();
$pyiThuElectionRegionId = $pyiThuElectionRegion ? $pyiThuElectionRegion->id : -1;
$stateElectionRegion = StateElectionRegion::where('township_id', $township->id)->get('id');
$stateElectionRegionIds = $stateElectionRegion ? $stateElectionRegion->pluck('id')->toArray() : -1;
$stateEthnicElectionRegion = StateEthnicElectionRegion::where('division_id', $request->input('divisionId'))->get('id');
$stateEthnicElectionRegionIds = $stateEthnicElectionRegion ? $stateEthnicElectionRegion->pluck('id')->toArray() : -1;
$subTownshipRegionIds = SubTownship::where('township_id', $township->id)->where('amyotha_election_region_id', '!=', 0)->get(['amyotha_election_region_id']);
$amyotharElectionRegionIds = array_merge(
$subTownshipRegionIds->pluck('amyotha_election_region_id')->toArray(),
[$township->amyotha_election_region_id, $district->amyotha_election_region_id]
);
$representativesPartyIds = Db::table('representatives')
->select("party_id")
->WhereRaw('(election_region_type = "App\\\\Models\\\\PyiThuElectionRegion" and election_region_id in (' . $pyiThuElectionRegionId . '))')
->orWhereRaw('(election_region_type = "App\\\\Models\\\\StateElectionRegion" and election_region_id in (' . implode(",", $stateElectionRegionIds) . '))')
->orWhereRaw('(election_region_type = "App\\\\Models\\\\AmyothaElectionRegion" and election_region_id in (' . implode(",", $amyotharElectionRegionIds) . '))')
->orWhereRaw('(election_region_type = "App\\\\Models\\\\StateEthnicElectionRegion" and election_region_id in (' . implode(",", $stateEthnicElectionRegionIds) . '))')
->get();
return $representativesPartyIds;
}
/**
* @param $request
* @param $partiesQuery
* @return mixed
*/
private function composeParties($request, $partiesQuery)
{
$partiesQuery = $this->prepareFamousEthnicQuery($partiesQuery, $request->input('isFamous'), $request->input('isEthnic'));
$parties = $partiesQuery->join('parties', 'representatives.party_id', '=', 'parties.id')
->groupBy('party_id')
->paginate(50);
return $parties;
}
/**
* @return \Illuminate\Database\Query\Builder
*/
private function partiesBaseQuery()
{
return Db::table('representatives')->select(
"representatives.party_id",
"parties.english_name",
"parties.id",
"parties.burmese_name",
"parties.photo",
"parties.is_famous",
"parties.is_ethnic",
"parties.party_registration_date",
"parties.party_confirmation_date",
"parties.party_registration_no",
DB::raw('COUNT(representatives.party_id) as representative_count')
);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.