<?php

namespace App\Http\Controllers\admin;

use DB;
use Session;
use App\Exports\PSPL;
Use App\Exports\FundDepositEXC;
Use App\Exports\BranchDueExcel;
Use App\Exports\TotalEIDExcel;
use App\Models\Operator;
use App\Models\Bankupload;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Excel;
use App\Exports\operatorList;
use App\Models\EnrollmentClient;
use App\Http\Controllers\Controller;

class ExportExcelControllers extends Controller
{
    public function PSPLExcel(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(isset($req->Ea_Code)){
            $enrollmentList = $enrollmentList->where('enrolment_clients.Enrolment_Agency', $req->Ea_Code);
        }
        if(isset($req->state)){
            $enrollmentList = $enrollmentList->where('operators.State', $req->state);
        }
        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);
        }
        if(isset($req->operator_code)){
            $enrollmentList = $enrollmentList->where('operators.Operator_Code', $req->operator_code);
        }
        if(Session::has('operatorsession')){
            $operator_code = Session::get('operatorsession')->Operator_Code;

            $enrollmentList = $enrollmentList->where('operators.Operator_Code', $operator_code);
        }
        if(Session::has('branchsession')){
            $branch_code = Session::get('branchsession')->branch_code;
            $enrollmentList = $enrollmentList->where('operators.branch_id', $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.c_date','DESC')
        ->get();
     
        $cDate = date('d-m-Y');
        $excel = app(Excel::class);
        return $excel->download(new PSPL($enrollmentList), "pspl.$cDate.xlsx");
    }
    public function operatorListExcel(Request $req){
       
        if(Session::has('adminsession')){
            $operatorList = Operator::orderBy('id','DESC');

        }
        if(Session::has('branchsession')){
            $branch_id = Session::get('branchsession')->branch_code;
            $operatorList = Operator::where('branch_id', $branch_id);
        }
        $operatorList->When($req->operator_code, function ($query) use($req) {
            return $query->where('Operator_Code', $req->operator_code);
        })->When($req->state, function ($query) use($req) {
            return $query->where('State', $req->state );
        })->When($req->district, function ($query) use($req) {
            return $query->where('District', $req->district );
        });
        if(isset($req->from_date) && isset($req->to_date)){
                $operatorList = $operatorList->whereBetween('created_at', [$req->from_date, $req->to_date]);
        }
        if(isset($req->status) ){
                $operatorList = $operatorList->where('status', $req->status);
        }
        $operatorList = $operatorList->get();
        $cDate = date('d-m-Y');
        $excel = app(Excel::class);
        return $excel->download(new operatorList($operatorList), "operator-list.$cDate.xlsx");
    }
    public function FundDeposit(Request $req){
        $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." 00:00:00", $req->to_date." 23:59:59"]);
        }
        if(isset($req->operator)){
            $enrollmentList = $enrollmentList->where('bankuplods.Operator_Code', $req->operator);
        }
        if(isset($req->state)){
            $enrollmentList = $enrollmentList->where('operators.State', $req->state);
        }
        if(isset($req->district)){
            $enrollmentList = $enrollmentList->where('operators.District', $req->district);
        }
        if(isset($req->eacode)){
            $enrollmentList = $enrollmentList->where('operators.Ea_Code', $req->eacode);
        }
        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);
        }

        $enrollmentList = $enrollmentList->get();
       
        $cDate = date('d-m-Y');
        $excel = app(Excel::class);
        return $excel->download(new FundDepositEXC($enrollmentList), "fund_deposit.$cDate.xlsx");
    }
    public function branchDue(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);
        }
        if(isset($req->from_date) && isset($req->to_date)){
            $branchList = $branchList->whereBetween('operators.created_at', [$req->from_date, $req->to_date]);
        }
        if(isset($req->eacode)){
            $branchList = $branchList->where('operators.EA_Code', $req->eacode);
        }
        if(isset($req->state)){
            $branchList = $branchList->where('operators.State', $req->state);
        }
        if(isset($req->district)){
            $branchList = $branchList->where('operators.District', $req->district);
        }
        $branchList = $branchList->orderBy('branches.branch_name','ASC')->get();
        $enrollmentList = [];
        foreach ($branchList 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');
        }
        // usort($enrollmentList, function($a, $b) {
        //     return $b['total_pending_amount'] <=> $a['total_pending_amount'];
        // });

        $cDate = date('d-m-Y');
        $excel = app(Excel::class);
        return $excel->download(new BranchDueExcel($enrollmentList), "branch_Due.$cDate.xlsx");
    }
    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->district)) {
                            $enrollmentList = $enrollmentList->where('operators.District', 'like', '%' . $req->district . '%');
                        }
                        if (isset($req->state)) {
                            $enrollmentList = $enrollmentList->where('operators.State',$req->state);
                        }
                        if (isset($req->state)) {
                            $enrollmentList = $enrollmentList->where('operators.State',$req->state);
                        }
                        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);
                        }
                        if(isset($req->from_date) && isset($req->to_date)){
                            $enrollmentList = $enrollmentList->whereBetween('enrolment_clients.c_date', [$req->from_date, $req->to_date]);
                        }
                        $enrollmentList = $enrollmentList->orderBy('enrolment_clients.c_date','DESC')
                        ->get();

        $cDate = date('d-m-Y');
        $excel = app(Excel::class);
        return $excel->download(new TotalEIDExcel($enrollmentList), "Total_EID_List.$cDate.xlsx");
    }
}
