Party Fix query logic

PartyApiController.php

<?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')
        );
    }
}