<?php

namespace App\Http\Controllers\admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\EnrollmentClient;
use Illuminate\Support\Facades\DB;
use App\Models\Bankupload;
use App\Models\Branch;
use App\Models\Operator;
use Illuminate\Support\Facades\Session;
use Illuminate\Pagination\LengthAwarePaginator;
class AdminMICReportControllers extends Controller
{
    public function PSPL(Request $req){

        $enrollmentList = EnrollmentClient::select(
            'operators.branch_id as branch_code',
            'operators.pincode',
            'enrolment_clients.Station_ID',
            'operators.District',
            'operators.State',
            'operators.Operator_Code',
            'operators.Operator_Name',
            'enrolment_clients.c_date',
            DB::raw('COUNT(enrolment_clients.id) as total_enrollment'),
            DB::raw('SUM(CASE WHEN enrolment_clients.Type = "E" THEN 1 ELSE 0 END) as new_enrollments'),
            DB::raw('SUM(CASE WHEN enrolment_clients.Type = "U" THEN 1 ELSE 0 END) as total_no_of_update'),
            DB::raw('SUM(CASE WHEN enrolment_clients.Total_amount_charged = 50.0 THEN 1 ELSE 0 END) as total_demographic_update'),
            DB::raw('SUM(CASE WHEN enrolment_clients.Total_amount_charged = 0.0 AND enrolment_clients.Type = "U" THEN 1 ELSE 0 END) as mandatory_bio_biometric_update'),
            DB::raw('SUM(CASE WHEN enrolment_clients.Total_amount_charged = 100.0 THEN 1 ELSE 0 END) as biometric_update'),
            DB::raw('SUM(CASE WHEN enrolment_clients.Total_amount_charged = 50.0 THEN enrolment_clients.Total_amount_charged ELSE 0 END) as total_amount_collected_A'),
            DB::raw('SUM(CASE WHEN enrolment_clients.Total_amount_charged = 100.0 THEN enrolment_clients.Total_amount_charged ELSE 0 END) as total_amount_collected_B'),
            DB::raw('SUM(CASE WHEN enrolment_clients.Total_amount_charged = 0.0 THEN enrolment_clients.Total_amount_charged ELSE 0 END) as total_amount_collected_C')
        )
        ->join('operators', 'enrolment_clients.Operator_ID', '=', 'operators.Operator_Code');
       if(isset($req->from_date) && isset($req->to_date)){
            $enrollmentList = $enrollmentList->whereBetween('enrolment_clients.c_date', [$req->from_date, $req->to_date]);
       }
       if(Session::has('operatorsession')){
        $operator_code = Session::get('operatorsession')->Operator_Code;
        $enrollmentList = $enrollmentList->where('operators.Operator_Code', $operator_code);
       }elseif(Session::has('branchsession')){
        $branch_code = Session::get('branchsession')->branch_code;
        $enrollmentList = $enrollmentList->where('enrolment_clients.branch_code', $branch_code);
        if(isset($req->operator_code)){
            $enrollmentList = $enrollmentList->where('operators.Operator_Code', $req->operator_code);
        }
        }else{
            if(isset($req->operator_code)){
                    $enrollmentList = $enrollmentList->where('operators.Operator_Code', $req->operator_code);
            }
            if(isset($req->district)){
                    $enrollmentList = $enrollmentList->where('operators.District', $req->district);
            }
            if(isset($req->branch_code)){
                    $enrollmentList = $enrollmentList->where('operators.branch_id', $req->branch_code);
            }
       }
        $enrollmentList = $enrollmentList->groupBy('enrolment_clients.Station_ID', 'operators.District', 'operators.State', 'operators.branch_id','operators.Operator_Code',
        'operators.Operator_Name','enrolment_clients.c_date','operators.pincode')
        // ->orderBy('enrolment_clients.Operator_ID')
        ->orderBy('enrolment_clients.c_date','DESC')
        ->paginate(50);

        if(Session::has('operatorsession')){
            return view('operator.pspl_report', compact('enrollmentList'));
        }
        if(Session::has('branchsession')){
            $operatorList = Operator::select('Operator_Name','Operator_Code')->where([['status','1'],['branch_id',$branch_code]])->orderBy('Operator_Code','ASC')->get();

            return view('branch.pspl_report', compact('enrollmentList','operatorList'));
        }
        $operatorList = Operator::select('Operator_Name','Operator_Code')->where('status','1')->orderBy('Operator_Code','ASC')->get();
        $branchList   = Branch::select('branch_code','branch_name')->orderBy('branch_code')->get();
        $districtList = Operator::select('District')->groupBy('District')->orderBy('District','ASC')->get();
       return view('admin.report.pspl_report', compact('enrollmentList','operatorList','branchList','districtList'));
    }
    public function Streetron(Request $req){
       $enrollmentList = [];
        return view('admin.report.streetron_reporting', compact('enrollmentList'));
    }
    public function fundDeposit(Request $req){
       // dd('jjj');
        $enrollmentList = Bankupload::select(
                    'branches.branch_name',
                    'bankuplods.transaction_no',
                    'operators.Operator_Name',
                    'operators.Operator_Code',
                    'operators.Station_Code',
                    'bankuplods.date_of_transaction',
                    'bankuplods.district',
                    'bankuplods.amount_credited'
                )
            ->join('operators', 'bankuplods.Operator_Code', '=', 'operators.Operator_Code')
            ->join('branches', 'operators.branch_id', '=', 'branches.branch_code');
            if(isset($req->from_date) && isset($req->to_date)){
                $enrollmentList = $enrollmentList->whereBetween('bankuplods.date_of_transaction', [$req->from_date, $req->to_date]);
            }
            if(Session::has('operatorsession')){
                $operator_code = Session::get('operatorsession')->Operator_Code;
                $enrollmentList = $enrollmentList->where('bankuplods.Operator_Code', $operator_code);
            }elseif(Session::has('branchsession')){
                $branch_code = Session::get('branchsession')->branch_code;
                $enrollmentList = $enrollmentList->where('bankuplods.branch_code', $branch_code);
            }else{
                if(isset($req->operator_code)){
                    $enrollmentList = $enrollmentList->where('bankuplods.Operator_Code', $req->operator_code);
                }
            }

           $enrollmentList = $enrollmentList->orderBy('bankuplods.date_of_transaction','DESC')->paginate(10);
           if(Session::has('operatorsession')){
            return view('operator.funddeposit_reporting',compact('enrollmentList'));
           }
            if(Session::has('branchsession')){
                 $operatorList = Operator::select('Operator_Name','Operator_Code')->where([['status','1'],['branch_id',$branch_code]])->orderBy('Operator_Code','ASC')->get();
                //  dd($operatorList);
            return view('branch.funddeposit_reporting',compact('enrollmentList','operatorList'));
           }
           $operatorList = Operator::select('Operator_Name','Operator_Code')->where('status','1')->orderBy('Operator_Code','ASC')->get();
        return view('admin.report.funddeposit_reporting',compact('enrollmentList','operatorList'));
    }
     public function operatorBranchDue(Request $req){
        $branchList = DB::table('operators')
        ->select('operators.Station_Code', 'operators.Operator_Code', 'operators.Operator_Name', 'branches.branch_name','operators.EA_Code', 'branches.district')
        ->join('branches', 'operators.branch_id', '=', 'branches.branch_code');
        if(isset($req->branch_code)){
            $branchList = $branchList->where('branches.branch_code', $req->branch_code);
        }
        if(isset($req->operator_code)){
            $branchList = $branchList->where('operators.Operator_Code', $req->operator_code);
        }
        $branchLists = $branchList->get();
        $branchList = $branchList->paginate(50);


        $enrollmentList = [];
       foreach ($branchLists as $key => $val) {
            $enrollmentList[$key]['Operator_Code'] = $val->Operator_Code;
            $enrollmentList[$key]['Operator_Name'] = $val->Operator_Name;
            $enrollmentList[$key]['branch_name']   = $val->branch_name;
            $enrollmentList[$key]['district']      = $val->district;
            $enrollmentList[$key]['Station_Code']  = $val->Station_Code;
            $enrollmentList[$key]['EA_Code']  = $val->EA_Code;
            $enrollmentList[$key]['total_No_of_Enrollment']  =  EnrollmentClient::where('Operator_ID', $val->Operator_Code)->count();
            $enrollmentList[$key]['total_no_of_update']  =  EnrollmentClient::where([['Operator_ID', $val->Operator_Code],['Type', 'U']])->count();
            $enrollmentList[$key]['new_enrollments']  =  EnrollmentClient::where([['Operator_ID', $val->Operator_Code],['Type', 'E']])->count();
            $enrollmentList[$key]['total_demographic_update']  =  EnrollmentClient::where([['Operator_ID', $val->Operator_Code],['Total_amount_charged', '50.0'], ['Type', 'U']])->count();
            $enrollmentList[$key]['mandatory_bio_biometric_update']  =  EnrollmentClient::where([['Operator_ID', $val->Operator_Code],['Total_amount_charged', '0.0'], ['Type', 'U']])->count();
            $enrollmentList[$key]['biometric_update']  =  EnrollmentClient::where([['Operator_ID', $val->Operator_Code],['Total_amount_charged', '100.0'], ['Type', 'U']])->count();
            $enrollmentList[$key]['total_amount_collected_A']  =  EnrollmentClient::where([['Operator_ID', $val->Operator_Code],['Total_amount_charged', '50.0'], ['Type', 'U']])->sum('Total_amount_charged');
            $enrollmentList[$key]['total_amount_collected_B']  =  EnrollmentClient::where([['Operator_ID', $val->Operator_Code],['Total_amount_charged', '100.0'], ['Type', 'U']])->sum('Total_amount_charged');
            // $enrollmentList[$key]['total_amount_collected_C']  =  EnrollmentClient::where([['Operator_ID', $val->Operator_Code],['Type', 'E']])->count();
            $total_collect_amount = EnrollmentClient::where('Operator_ID', $val->Operator_Code)->sum('Total_amount_charged');
            $enrollmentList[$key]['total_collect_amount'] = $total_collect_amount;
            $enrollmentList[$key]['total_deposit_bank']   = Bankupload::where('Operator_Code', $val->Operator_Code)->sum('amount_credited');
            $enrollmentList[$key]['total_pending_amount']   =$total_collect_amount- Bankupload::where('Operator_Code', $val->Operator_Code)->sum('amount_credited');
        }
        usort($enrollmentList, function($a, $b) {
            return $b['total_pending_amount'] <=> $a['total_pending_amount'];
        });

        $page = request()->get('page', 1); // Get the current page from the request, default to page 1 if not set
        $perPage = 50; // Number of items per page

        // Paginate the array
        $enrollmentList = new LengthAwarePaginator(
            array_slice($enrollmentList, ($page - 1) * $perPage, $perPage),
            count($enrollmentList),
            $perPage,
            $page,
            ['path' => request()->url(), 'query' => request()->query()]
        );
        // dd($enrollmentList);

        // Displaying the top 5 entries
        // dd($top_5_highest_pending);
       $operatorList = Operator::select('Operator_Name','Operator_Code')->where('status','1')->orderBy('Operator_Code','ASC')->get();
       $branchDropdown = Branch::select('branch_name','branch_code')->orderBy('branch_code','ASC')->get();
        return view('admin.report.operator_branchdue_reporting', compact('enrollmentList','branchList','operatorList','branchDropdown'));
    }
    public function salaryReport(Request $req){
        $enrollmentList = [];
        return view('admin.report.salary_reporting', compact('enrollmentList'));
    }
    public function totalEid(Request $req){
        $enrollmentList =  DB::table('enrolment_clients')
                        ->select('enrolment_clients.c_date','branches.branch_name', 'branches.district', 'enrolment_clients.Operator_ID','operators.Operator_Name', 'enrolment_clients.Station_ID','enrolment_clients.Type','enrolment_clients.Resident','enrolment_clients.Total_amount_charged','enrolment_clients.Enrolment_No_and_Date')
                        ->join('operators', 'enrolment_clients.Operator_ID', '=', 'operators.Operator_Code')
                        ->join('branches', 'operators.branch_id', '=', 'branches.branch_code');
                        if(isset($req->from_date) && isset($req->to_date)){
                            $enrollmentList = $enrollmentList->whereBetween('enrolment_clients.c_date', [$req->from_date, $req->to_date]);
                       }
                        if (isset($req->district)) {
                            $enrollmentList = $enrollmentList->where('operators.District', 'like', '%' . $req->district . '%');
                        }
                        if (isset($req->operator_code)) {
                            $enrollmentList = $enrollmentList->where('operators.Operator_Code','=', $req->operator_code);
                        }
                        if (isset($req->branch_code)) {
                            $enrollmentList = $enrollmentList->where('operators.branch_id','=',$req->branch_code);
                        }
                        $enrollmentList = $enrollmentList->orderBy('enrolment_clients.c_date','DESC')
                        ->paginate(50);

                        $operatorList = Operator::select('Operator_Name','Operator_Code')->where('status','1')->orderBy('Operator_Code','ASC')->get();
                        $branchDropdown = Branch::select('branch_name','branch_code')->orderBy('branch_code','ASC')->get();
                        $districtList = Operator::select('District')->groupBy('District')->orderBy('District','ASC')->get();
        return view('admin.report.total_eid_reporting', compact('enrollmentList','operatorList','branchDropdown','districtList'));
    }
}
