//---------- bcr_report_2024 ----------

    public function budgetControlRegister(Request $request)
    {
       $page_title = 'reports';
       $deptID = $request->input('department', []);
       $headID = $request->input('head_id', []);
       $startDate = $request->input('from_date');
       $endDate = $request->input('to_date');
       

       $department= DB::table('department_mst as t1')
                    ->select('t1.*')
                    ->whereNull('t1.deleted_dt')
                    ->when(Auth::user()->usertype == 3, function ($query) {
                    $query->where('t1.dept_id', Auth::user()->dept_id);
                    })
                    ->orderBy('t1.department_name', 'asc')
                    ->get();

        $data = DB::table('budget_child')
                ->select(
                    'department_mst.department_name',
                    'budget_head.head_name',
                    'budget_child.spill_over',
                    'budget_child.budget_provision',
                    'budget_child.current_year_effective_budget'
                )
                ->join('department_mst', 'department_mst.dept_id', '=', 'budget_child.department')
                ->join('budget_head', 'budget_head.head_id', '=', 'budget_child.budget_head_id')
                ->where('budget_child.fy_year', Session::get('year'))
                ->where('budget_child.department', $deptID)
                ->where('budget_child.budget_head_id', $headID)
                ->whereNull('budget_child.deleted_dt')
                ->groupBy('budget_child.department', 'budget_child.budget_head_id')
                ->first();

        $results = DB::table('trans_bill_payment as bill_payment')
                    ->select(
                        'bcr_entry.financial_year',
                        'bcr_entry.bcr_no',
                        'bcr_entry.amount_booked_user_value',
                        'wo.work_order_amt',
                        'bill_booking.invoice_total',
                        'bill_payment.invoice_total as bill_amt'
                    )
                    ->join('trans_budget_entry as bcr_entry', 'bcr_entry.be_id', '=', 'bill_payment.be_id')
                    ->join('work_order as wo', 'wo.be_id', '=', 'bcr_entry.be_id')
                    ->join('trans_bill_booking as bill_booking', 'bill_booking.be_id', '=', 'bcr_entry.be_id')
                    ->whereNull('bcr_entry.deleted_dt')
                    ->whereNull('wo.deleted_dt')
                    ->whereNull('bill_booking.deleted_dt')
                    ->whereNull('bill_payment.deleted_dt')
                    ->where('bcr_entry.department', $deptID)
                    ->where('bcr_entry.budget_head', $headID)
                    ->whereBetween('bill_payment.inserted_dt', [$startDate, $endDate])
                    ->where('bill_booking.bill_book_status', 1)
                    ->orderby('financial_year','desc')
                    ->get();

       return view ('reports.budget_control_register',compact('page_title','department','data','results'));
    }


    public function BudgetHeadReport(Request $request)
    {
       $page_title = 'reports';
       $deptID = $request->input('department', []);
       $headID = $request->input('head_id', []);
       $startDate = $request->input('from_date');
       $endDate = $request->input('to_date');


       $department= DB::table('department_mst as t1')
                          ->select('t1.*')
                          ->whereNull('t1.deleted_dt')
                          ->when(Auth::user()->usertype == 3, function ($query) {
                            $query->where('t1.dept_id', Auth::user()->dept_id);
                           })
                          ->orderBy('t1.department_name', 'asc')
                          ->get();

        $data = DB::table('trans_bill_payment as bill_payment')
                ->select(
                    'bcr_entry.financial_year',
                    'd1.department_name',
                    'b1.head_name','d1.dept_id','b1.head_id',
                    DB::raw('SUM(amount_booked_user_value) as bcr_total'),
                    DB::raw('SUM(work_order_amt) as wo_total'),
                    DB::raw('SUM(bill_booking.invoice_total) as bill_booking_total'),
                    DB::raw('SUM(bill_payment.invoice_total) as bill_amt')
                )
                ->join('trans_budget_entry as bcr_entry', 'bcr_entry.be_id', '=', 'bill_payment.be_id')
                ->join('department_mst as d1', 'd1.dept_id', '=', 'bcr_entry.department')
                ->join('budget_head as b1', 'b1.head_id', '=', 'bcr_entry.budget_head')
                ->join('work_order as wo', 'wo.be_id', '=', 'bcr_entry.be_id')
                ->join('trans_bill_booking as bill_booking', 'bill_booking.be_id', '=', 'bcr_entry.be_id')
                ->whereNull('bcr_entry.deleted_dt')
                ->whereNull('wo.deleted_dt')
                ->whereNull('bill_booking.deleted_dt')
                ->whereNull('bill_payment.deleted_dt')
                ->where('bcr_entry.department',$deptID)
                ->where('bill_booking.bill_book_status', 1)
                ->groupBy('bcr_entry.financial_year', 'bcr_entry.department', 'bcr_entry.budget_head')
                ->orderBy('financial_year', 'desc')
                ->get();

            $BudgetArray=[];
            foreach($data  as $value){
                $dept = $value->department_name;
                $head = $value->head_name;
                $year = $value->financial_year;

                $departmentId = $value->dept_id;
                $headId = $value->dept_id;

                if (!isset($BudgetArray[$dept][$head][$year])) {
                    $BudgetArray[$dept][$head][$year] =
                        [
                            'dept_id' => $departmentId,
                            'head_id' => $headId,
                            'bcr_total' => 0,
                            'wo_total' => 0,
                            'bill_booking_total' => 0,
                            'bill_amt' => 0,
                        ];
                }

                $BudgetArray[$dept][$head][$year]['bcr_total'] += $value->bcr_total;
                $BudgetArray[$dept][$head][$year]['wo_total'] += $value->wo_total;
                $BudgetArray[$dept][$head][$year]['bill_booking_total'] += $value->bill_booking_total;
                $BudgetArray[$dept][$head][$year]['bill_amt'] += $value->bill_amt;

            }

      return view ('reports.budget_head_report_new',compact('page_title','department','BudgetArray'));

    }


    public function DepartmentReport(Request $request)
    {
       $page_title = 'reports';
       $deptID = $request->input('department', []);
       $startDate = $request->input('from_date');
       $endDate = $request->input('to_date');

       $department= DB::table('department_mst as t1')
                          ->select('t1.*')
                          ->whereNull('t1.deleted_dt')
                          ->when(Auth::user()->usertype == 3, function ($query) {
                            $query->where('t1.dept_id', Auth::user()->dept_id);
                           })
                          ->orderBy('t1.department_name', 'asc')
                          ->get();

        $CurrBudget =  DB::table('budget_child')
                        ->select(
                            'department',
                            DB::raw('SUM(spill_over) as spill'),
                            DB::raw('SUM(budget_provision) as new_provision')
                        )
                        ->where('fy_year', Session::get('year'))
                        ->whereNull('deleted_dt')
                        ->groupBy('department')
                        ->orderBy('department', 'desc')
                        ->get();

        $deptData = DB::table('trans_bill_payment as bill_payment')
                          ->select(
                              'bcr_entry.financial_year',
                              'd1.department_name','d1.dept_id',
                              DB::raw('SUM(amount_booked_user_value) as bcr_total'),
                              DB::raw('SUM(work_order_amt) as wo_total'),
                              DB::raw('SUM(bill_booking.invoice_total) as bill_booking_total'),
                              DB::raw('SUM(bill_payment.invoice_total) as bill_payment_total')
                          )
                          ->join('trans_budget_entry as bcr_entry', 'bcr_entry.be_id', '=', 'bill_payment.be_id')
                          ->join('department_mst as d1', 'd1.dept_id', '=', 'bcr_entry.department')
                          ->join('work_order as wo', 'wo.be_id', '=', 'bcr_entry.be_id')
                          ->join('trans_bill_booking as bill_booking', 'bill_booking.be_id', '=', 'bcr_entry.be_id')
                          ->whereNull('bcr_entry.deleted_dt')
                          ->whereNull('wo.deleted_dt')
                          ->whereNull('bill_booking.deleted_dt')
                          ->whereNull('bill_payment.deleted_dt')
                          ->where('bill_booking.bill_book_status', 1)
                          ->whereIn('bcr_entry.department', $deptID)
                          ->whereBetween('bill_payment.inserted_dt', [$startDate, $endDate])
                          ->groupBy('bcr_entry.financial_year', 'bcr_entry.department')
                          ->orderBy('dept_id', 'desc')
                          ->get();

            $departmentData = [];
            foreach ($deptData as $item) {
                $departmentName = $item->department_name;
                $departmentId = $item->dept_id;

                if (!isset($departmentData[$departmentName])) {
                $departmentData[$departmentName] =
                    [
                        'dept_id' => $departmentId,
                        'spill_over_bcr' => 0,'new_works_bcr' => 0,'total_bcr' => 0,
                        'spill_over_wo' => 0,'new_works_wo' => 0,'total_bill_wo' => 0,
                        'spill_over_bill_booking' => 0,'new_works_bill_booking' => 0, 'total_bill_booking' => 0,
                        'spill_over_bill_payment' => 0,'new_works_bill_payment' => 0, 'total_bill_payment' => 0,
                    ];
                }

                if ($item->financial_year == 4) {
                    $departmentData[$departmentName]['spill_over_bcr'] = $item->bcr_total;
                    $departmentData[$departmentName]['spill_over_wo'] = $item->wo_total;
                    $departmentData[$departmentName]['spill_over_bill_booking'] = $item->bill_booking_total;
                    $departmentData[$departmentName]['spill_over_bill_payment'] = $item->bill_payment_total;
                } elseif ($item->financial_year == 3) {
                    $departmentData[$departmentName]['new_works_bcr'] = $item->bcr_total;
                    $departmentData[$departmentName]['new_works_wo'] = $item->wo_total;
                    $departmentData[$departmentName]['new_works_bill_booking'] = $item->bill_booking_total;
                    $departmentData[$departmentName]['new_works_bill_payment'] = $item->bill_payment_total;
                }
                $departmentData[$departmentName]['total_bcr'] = $departmentData[$departmentName]['spill_over_bcr'] + $departmentData[$departmentName]['new_works_bcr'];
                $departmentData[$departmentName]['total_wo'] = $departmentData[$departmentName]['spill_over_wo'] + $departmentData[$departmentName]['new_works_wo'];
                $departmentData[$departmentName]['total_bill_booking'] = $departmentData[$departmentName]['spill_over_bill_booking'] + $departmentData[$departmentName]['new_works_bill_booking'];
                $departmentData[$departmentName]['total_bill_payment'] = $departmentData[$departmentName]['spill_over_bill_payment'] + $departmentData[$departmentName]['new_works_bill_payment'];
            }

       return view ('reports.department_report',compact('page_title','department','departmentData','CurrBudget'));

    }


    public function partialBudgetBooked(Request $request)
    {
        $page_title = 'reports';
        $deptID = $request->input('department', []);
        $startDate = $request->input('from_date');
        $endDate = $request->input('to_date');

        $department= DB::table('department_mst as t1')
                          ->select('t1.*')
                          ->whereNull('t1.deleted_dt')
                          ->when(Auth::user()->usertype == 3, function ($query) {
                            $query->where('t1.dept_id', Auth::user()->dept_id);
                           })
                          ->orderBy('t1.department_name', 'asc')
                          ->get();

        $results = DB::table('year_wise_budget as y1')
                    ->select(
                        'y1.financial_year',
                        'y1.dept_id',
                        'y1.budget_head',
                        'd1.department_name',
                        'b1.head_name',
                        DB::raw('SUM(y1.amount_distribute) as amount')
                    )
                    ->leftJoin('department_mst as d1', 'd1.dept_id', '=', 'y1.dept_id')
                    ->leftJoin('budget_head as b1', 'b1.head_id', '=', 'y1.budget_head')
                    ->where('y1.choose_distribution', 'partial')
                    ->whereIn('y1.dept_id', $deptID)
                    ->whereBetween('y1.created_at', [$startDate, $endDate])
                    ->groupBy('y1.dept_id', 'y1.budget_head','y1.financial_year')
                    ->get();

        $yearArray=[];
        foreach($results as $result){
            $yearArray[$result->department_name][$result->head_name][$result->financial_year] = [$result->amount];
        }

       return view ('reports.partial_budget_booked_report',compact('page_title','department','yearArray'));
    }


    public function getNegativeBudgetReport(Request $request)
    {
            $page_title = 'reports';
            $deptID = $request->input('department',[]);
            $startDate = $request->input('from_date');
            $endDate = $request->input('to_date');

            $department= DB::table('department_mst as t1')
                              ->select('t1.*')
                              ->whereNull('t1.deleted_dt')
                              ->when(Auth::user()->usertype == 3, function ($query) {
                                $query->where('t1.dept_id', Auth::user()->dept_id);
                               })
                              ->orderBy('t1.department_name', 'asc')
                              ->get();

            $budgetData = DB::table('budget_child')
                ->select('department', 'budget_head_id as budget_head', 'current_year_effective_budget as budget')
                ->where('department', $deptID)
                ->where('fy_year', Session::get('year'))
                ->whereNull('deleted_dt')
                ->get()
                ->keyBy(function($item) {
                    return $item->department . '-' . $item->budget_head;
                });

            $results = DB::table('trans_budget_entry AS t1')
                ->select(
                    't1.department',
                    't1.budget_head',
                    'd1.department_name',
                    'h1.head_name',
                    DB::raw('SUM(t1.amount_booked_user_value) as bcr_booked')
                )
                ->leftJoin('department_mst AS d1', function($join) {
                    $join->on('d1.dept_id', '=', 't1.department')
                        ->whereNull('d1.deleted_dt');
                })
                ->leftJoin('budget_head AS h1', function($join) {
                    $join->on('h1.head_id', '=', 't1.budget_head')
                        ->whereNull('h1.deleted_dt');
                })
                ->where('t1.status', '!=', 2)
                ->where('t1.approve_reject_status', '=', 1)
                ->where('t1.financial_year', '=', Session::get('year'))
                ->where('t1.department', '=', $deptID)
                ->whereBetween('t1.inserted_dt', [$startDate, $endDate])
                ->whereNull('t1.deleted_dt')
                ->groupBy('t1.department', 't1.budget_head', 'd1.department_name', 'h1.head_name')
                ->get();

            $negativeArray = [];
            $grandTotal = [
                'budget' => 0,
                'bcr_booked' => 0,
                'negative_budget' => 0
            ];

            foreach ($results as $result) {
                $key = $result->department . '-' . $result->budget_head;
                $budget = isset($budgetData[$key]) ? $budgetData[$key]->budget : 0;

                if (!isset($negativeArray[$result->department_name])) {
                    $negativeArray[$result->department_name] = [
                        'details' => [],
                        'totals' => [
                            'budget' => 0,
                            'bcr_booked' => 0,
                            'negative_budget' => 0
                        ]
                    ];
                }

                $negativeArray[$result->department_name]['details'][$result->head_name] = [
                    'budget' => $budget,
                    'bcr_booked' => $result->bcr_booked,
                    'negative_budget' => $budget - $result->bcr_booked
                ];

                //  totals for each department
                $negativeArray[$result->department_name]['totals']['budget'] += $budget;
                $negativeArray[$result->department_name]['totals']['bcr_booked'] += $result->bcr_booked;
                $negativeArray[$result->department_name]['totals']['negative_budget'] += ($budget - $result->bcr_booked);

                // grand totals
                $grandTotal['budget'] += $budget;
                $grandTotal['bcr_booked'] += $result->bcr_booked;
                $grandTotal['negative_budget'] += ($budget - $result->bcr_booked);
            }


            return view ('reports.negative_budget_report',compact('page_title','department','negativeArray','grandTotal'));

    }


    public function BudgetPartReport(Request $request)
    {
        $page_title = 'reports';
        $partID = $request->input('part_id',[]);
        $startDate = $request->input('from_date');
        $endDate = $request->input('to_date');

        $part_names= DB::table('budget_part_mst as t1')
                        ->select('t1.*')
                        ->whereNull('t1.deleted_dt')
                        ->when(Auth::user()->usertype == 3, function ($query) {
                        $query->where('t1.dept_id', Auth::user()->dept_id);
                        })
                        ->orderBy('t1.part_id', 'asc')
                        ->get();


        $data = DB::table('trans_bill_payment as bill_payment')
                        ->join('trans_budget_entry as bcr_entry', 'bcr_entry.be_id', '=', 'bill_payment.be_id')
                        ->join('budget_part_mst as bp1', 'bp1.part_id', '=', 'bcr_entry.budget_part')
                        ->join('department_mst as d1', 'd1.dept_id', '=', 'bcr_entry.department')
                        ->join('budget_head as b1', 'b1.head_id', '=', 'bcr_entry.budget_head')
                        ->join('work_order as wo', 'wo.be_id', '=', 'bcr_entry.be_id')
                        ->join('trans_bill_booking as bill_booking', 'bill_booking.be_id', '=', 'bcr_entry.be_id')
                        ->select(
                            'bcr_entry.financial_year',
                            'bp1.part_name',
                            DB::raw('SUM(bcr_entry.amount_booked_user_value) as bcr_total'),
                            DB::raw('SUM(wo.work_order_amt) as wo_total'),
                            DB::raw('SUM(bill_booking.invoice_total) as bill_booking_total'),
                            DB::raw('SUM(bill_payment.invoice_total) as bill_amt')
                        )
                        ->whereNull('bcr_entry.deleted_dt')
                        ->whereNull('wo.deleted_dt')
                        ->whereNull('bill_booking.deleted_dt')
                        ->whereNull('bill_payment.deleted_dt')
                        ->where('bill_booking.bill_book_status', '=', 1)
                        ->groupBy('bcr_entry.financial_year', 'bp1.part_id')
                        ->orderBy('bcr_entry.financial_year', 'desc')
                        ->get();


                        // $partArray = [];
                        // foreach($budgetPart as $budget){
                        //     $partArray[$budget->financial_year][$budget->part_name] =
                        //     [
                        //      'bcr_total' =>   $budget->bcr_total,
                        //      'wo_total' =>   $budget->wo_total,
                        //      'bill_booking_total' =>   $budget->bill_booking_total,
                        //      'bill_amt' =>   $budget->bill_amt,
                        //     ];
                        // }


                        // echo "<pre>";
                        //     print_r($data);
                        // echo "</pre>";
                        // die;



         return view ('reports.budget_part_report',compact('page_title','part_names','data'));
    }

     //---------- bcr_report_2024 ----------