EVOLUTION-NINJA

Edit File: Report (4).php

<?php

namespace App\Controllers;
 
class Report extends BaseController{

public function __construct()  
    { 
        $this->db = \Config\Database::connect(); 
          
    }

// public function get_report() {
//     // Get filters from POST request
//     $constituency = $this->request->getPost('constituency');
//     $mla = $this->request->getPost('mla');
//     $department = $this->request->getPost('department');
//     $apm = $this->request->getPost('apm');
//     $fund = $this->request->getPost('fund');
//     $engineer = $this->request->getPost('engineer');
//     $year = $this->request->getPost('year');
//     $status= $this->request->getPost('status');
//     $role = session()->get('role'); 
//     $user_id = session()->get('id');

//     if (date('m') <= 3) {
		    
// 		    $fromdate = (date('Y')-1).'-'.'04'.'-'.'01';
// 		    $enddate = (date('Y')).'-'.'03'.'-'.'31';
// 		}
// 	else {
//             $fromdate = (date('Y')).'-'.'04'.'-'.'01';
// 		    $enddate = (date('Y')+1).'-'.'03'.'-'.'31';
//       }
       
//     //   echo $fromdate;
//     //   echo "---";
//     //   echo $enddate;
//     //   die();

//     // Build the query with joins and selections
//     $builder = $this->db->table('create_project');
//     $builder->select('create_project.*, 
//                       department.department_name, 
//                       constituency.constituency_name, 
//                       constituency_master.mla AS mla, 
//                       roles_creation_apm.first_name AS apm_first_name, 
//                       fund_master.fund_name AS fund_name,
//                       roles_creation_engineer.first_name AS engineer_first_name,
//                       status_master.status AS status,status_master.status_name,
//                       GROUP_CONCAT(DISTINCT proposal_documents.document_name SEPARATOR ", ") AS proposal_document_names,
//                       GROUP_CONCAT(DISTINCT estimation_documents.document_name SEPARATOR ", ") AS estimation_document_names,
//                       GROUP_CONCAT(DISTINCT approval_documents.document_name SEPARATOR ", ") AS approval_document_names,
//                       COALESCE(total_released.total_released_amount, 0) AS total_released_amount,
//                       COALESCE(total_expenditure.total_expenditure, 0) AS total_expenditure,
//                       COALESCE(previous_year_released.previous_year_released, 0) AS previous_year_released, 
//                       COALESCE(present_year_released.present_year_released, 0) AS present_year_released');
    
//     // Join related tables
//     $builder->join('department', 'department.id = create_project.department', 'left');
//     $builder->join('fund_master','fund_master.id = create_project.fund_wise', 'left');
//     $builder->join('constituency', 'constituency.id = create_project.constituency', 'left');
//     $builder->join('constituency_master', 'constituency_master.constituency_name = create_project.constituency', 'left');
//     $builder->join('roles_creation AS roles_creation_apm', 'roles_creation_apm.id = create_project.apm', 'left');
//     $builder->join('roles_creation AS roles_creation_engineer', 'roles_creation_engineer.id = create_project.engineer', 'left');
//     $builder->join('status_master', 'status_master.status = create_project.status', 'left');  
//     $builder->join('proposal_documents', 'proposal_documents.project_id = create_project.id', 'left');
//     $builder->join('estimation_documents', 'estimation_documents.project_id = create_project.id', 'left');
//     $builder->join('approval_documents', 'approval_documents.project_id = create_project.id', 'left');
//     $builder->join('(SELECT project_id, SUM(released_amount) AS total_released_amount FROM released_amounts GROUP BY project_id) AS total_released', 'total_released.project_id = create_project.id', 'left');
//     $builder->join('(SELECT project_id, SUM(expenditure_amount) AS total_expenditure FROM expenditure GROUP BY project_id) AS total_expenditure', 'total_expenditure.project_id = create_project.id', 'left');

//     $builder->join("(SELECT project_id, SUM(released_amount) AS previous_year_released 
//                      FROM released_amounts 
//                      WHERE YEAR(released_date) = $fromdate 
//                      GROUP BY project_id) AS previous_year_released", 
//                     'previous_year_released.project_id = create_project.id', 'left');

//     $builder->join("(SELECT project_id, SUM(released_amount) AS present_year_released 
//                      FROM released_amounts 
//                      WHERE released_date BETWEEN '$fromdate' AND '$enddate'
//                      GROUP BY project_id) AS present_year_released", 
//                     'present_year_released.project_id = create_project.id', 'left');
    
  
//     $builder->groupBy('create_project.id');

//     if (!empty($constituency)) {
//         $builder->where('create_project.constituency', $constituency);
//     }
//     if (!empty($mla)) {
//         $builder->where('constituency_master.mla', $mla);
//     }
//     if (!empty($department)) {
//         $builder->where('create_project.department', $department);
//     }
//     if (!empty($apm)) {
//         $builder->where('create_project.apm', $apm);
//     }
//     if (!empty($engineer)) {
//         $builder->where('create_project.engineer', $engineer);
//     }
    
//     if (!empty($fund)) {
//         $builder->where('create_project.fund_wise', $fund);
//     }

//     if (!empty($year)) {

//         $builder->where('create_project.adm_financial_year', $year); 
//         }
        
 
   
//     if ($role == 2) { // APM specific filter
//         $builder->where('create_project.apm', $user_id);
//     } elseif ($role == 3 || $role==5) { // Engineer specific filter
//         $builder->where('create_project.engineer', $user_id);
//     }
//     elseif($role==4){
//           $builder->where('create_project.supervisor', $user_id);
//     }

//     // Order the results
//     $builder->orderBy('department.department_name', 'ASC');
//     $builder->orderBy('constituency.constituency_name', 'ASC');
//     $builder->orderBy('constituency_master.mla', 'ASC');

//     // Execute the query and return the result
//     $query = $builder->get();
//     $result = $query->getResultArray();
    
// //      $db = \Config\Database::connect();
// // $query = $db->getLastQuery();
// // echo $query;
// // die();

//     return $this->response->setJSON(['result' => 1, 'table' => $result]);
// }

//  }

// public function get_report() {
//     // Get filters from POST request
//     $constituency = $this->request->getPost('constituency');
//     $mla = $this->request->getPost('mla');
//     $department = $this->request->getPost('department');
//     $apm = $this->request->getPost('apm');
//     $fund = $this->request->getPost('fund');
//     $engineer = $this->request->getPost('engineer');
//     $year = $this->request->getPost('year');
//     $status= $this->request->getPost('Status');
    
   
//     $role = session()->get('role'); 
//     $user_id = session()->get('id');

//     if (date('m') <= 3) {
//         $fromdate = (date('Y')-1).'-04-01';
//         $enddate = date('Y').'-03-31';
//     } else {
//         $fromdate = date('Y').'-04-01';
//         $enddate = (date('Y')+1).'-03-31';
//     }
    
//     //  echo $fromdate;
//     //  echo $enddate;
//     //  die();

//     // Join related tables
//     $builder->join('department', 'department.id = create_project.department', 'left');
//     $builder->join('fund_master', 'fund_master.id = create_project.fund_wise', 'left');
//     $builder->join('constituency', 'constituency.id = create_project.constituency', 'left');
//     $builder->join('constituency_master', 'constituency_master.constituency_name = create_project.constituency', 'left');
//     $builder->join('roles_creation AS roles_creation_apm', 'roles_creation_apm.id = create_project.apm', 'left');
//     $builder->join('roles_creation AS roles_creation_engineer', 'roles_creation_engineer.id = create_project.engineer', 'left');
//     $builder->join('status_master', 'status_master.status = create_project.status', 'left');  
//     $builder->join('proposal_documents', 'proposal_documents.project_id = create_project.id', 'left');
//     $builder->join('estimation_documents', 'estimation_documents.project_id = create_project.id', 'left');
//     $builder->join('approval_documents', 'approval_documents.project_id = create_project.id', 'left');
//     $builder->join('(SELECT project_id, SUM(released_amount) AS total_released_amount FROM released_amounts GROUP BY project_id) AS total_released', 'total_released.project_id = create_project.id', 'left');
//     $builder->join('(SELECT project_id, SUM(expenditure_amount) AS total_expenditure FROM expenditure GROUP BY project_id) AS total_expenditure', 'total_expenditure.project_id = create_project.id', 'left');
    
    
//     // $builder->join("(SELECT project_id, SUM(released_amount) AS previous_year_released 
//     //                  FROM released_amounts 
//     //                  WHERE YEAR(released_date) = $fromdate 
//     //                  GROUP BY project_id) AS previous_year_released", 
//     //                 'previous_year_released.project_id = create_project.id', 'left');
    
    
//     $builder->join("(SELECT project_id, SUM(released_amount) AS previous_year_released 
//                     FROM released_amounts 
//                       WHERE released_date < '$fromdate'
//                       GROUP BY project_id) AS previous_year_released", 
//                   'previous_year_released.project_id = create_project.id', 'left');
                    
                    
//     $builder->join("(SELECT project_id, SUM(released_amount) AS present_year_released 
//                      FROM released_amounts 
//                      WHERE released_date BETWEEN '$fromdate' AND '$enddate'
//                      GROUP BY project_id) AS present_year_released", 
//                     'present_year_released.project_id = create_project.id', 'left');
                    
                    
                    
//     $builder->join("(SELECT project_id, 
//                             released_amount AS last_released_amount, 
//                             released_date AS last_released_date 
//                      FROM released_amounts 
//                      WHERE (project_id, released_date) IN 
//                           (SELECT project_id, MAX(released_date) 
//                             FROM released_amounts 
//                             GROUP BY project_id)
//                     ) AS last_released", 
//                   'last_released.project_id = create_project.id', 'left');

//     $builder->groupBy('create_project.id');

// if ($status == 2) {
//     $builder->where('(create_project.date_of_approval IS NULL OR create_project.date_of_approval = "")', null, false);
// } elseif ($status == 3) {
//     $builder->where('create_project.date_of_approval IS NOT NULL AND create_project.date_of_approval != ""', null, false);
// }

//   if ($constituency) {
//     $builder->where('create_project.constituency', $constituency);
//      }

//     if (!empty($mla)) {
//         $builder->where('create_project.mla', $mla);
//     }
//     if (!empty($department)) {
//         $builder->where('create_project.department', $department);
//     }
//     if (!empty($apm)) {
//         $builder->where('create_project.apm', $apm);
//     }
//     if (!empty($engineer)) {
//         $builder->where('create_project.engineer', $engineer);
//     }
//     if (!empty($fund)) {
//         $builder->where('create_project.fund_wise', $fund);
//     }
//     if (!empty($year)) {
//         $builder->where('create_project.adm_financial_year', $year); 
//     }
    
//     if ($role == 2) { // APM specific filter
//         $builder->where('create_project.apm', $user_id);
//     } elseif ($role == 3 || $role == 5) { // Engineer specific filter
//         $builder->where('create_project.engineer', $user_id);
//     } elseif ($role == 4) { // Supervisor specific filter
//         $builder->where('create_project.supervisor', $user_id);
//     }

// $builder->orderBy('(department.preference = 0)', 'ASC')
//         ->orderBy('department.preference', 'ASC')
//         //->orderBy('create_project.created_at', 'ASC'); // FIFO: Process oldest first
//  ->orderBy('create_project.id', 'ASC');

//     //   ->orderBy('constituency.constituency_name', 'ASC');
//     // $builder->orderBy('constituency.constituency_name', 'ASC');
//     // $builder->orderBy('constituency_master.mla', 'ASC');

// //  $this->db = \Config\Database::connect();
// //  $data = $builder->get()->getResult();
// //  echo $this->db->getLastQuery(); die();
 
 
//     $query = $builder->get();
//     $result = $query->getResultArray();

//     return $this->response->setJSON(['result' => 1, 'table' => $result]);
// }

//after add filter to report

// public function get_report() {
//     // Get filters from POST request
//     $constituency = $this->request->getPost('constituency');
//     $mla = $this->request->getPost('mla');
//     $department = $this->request->getPost('department');
//     $apm = $this->request->getPost('apm');
//     $fund = $this->request->getPost('fund');
//     $engineer = $this->request->getPost('engineer');
//     $year = $this->request->getPost('year');
//     $status= $this->request->getPost('Status');
    
   
//     $role = session()->get('role'); 
//     $user_id = session()->get('id');

//     $builder->groupBy('create_project.id');

// // -----------------------------------------------------------------------------------------------
// if (date('m') <= 3) {
//     // Jan–Mar → current FY is previous year–current year
//     $current_fy_start = date('Y', strtotime('-1 year'));
//     $current_fy_end   = date('y');
// } else {
//     // Apr–Dec → current FY is current year–next year
//     $current_fy_start = date('Y');
//     $current_fy_end   = date('y', strtotime('+1 year'));
// }

// $current_adm_financial_year = $current_fy_start . '-' . $current_fy_end;

// // Add condition:
// $builder->groupStart()
//     ->where('create_project.adm_financial_year', $current_adm_financial_year)
//     ->orGroupStart()
//         ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
//         ->where('create_project.status !=', 3)
//     ->groupEnd()
// ->groupEnd();

// //---------------------------------------------------------------------------------------------------------
//   if ($constituency) {
//     $builder->where('create_project.constituency', $constituency);
//      }

//     //   ->orderBy('constituency.constituency_name', 'ASC');
//     // $builder->orderBy('constituency.constituency_name', 'ASC');
//     // $builder->orderBy('constituency_master.mla', 'ASC');

//     return $this->response->setJSON(['result' => 1, 'table' => $result]);
// }

//  15/7/2025

public function get_report() {
    // Get filters from POST request
    $constituency = $this->request->getPost('constituency');
    $mla = $this->request->getPost('mla');
    $mp = $this->request->getPost('mp');
    $mlc = $this->request->getPost('mlc');
    $department = $this->request->getPost('department');
    $apm = $this->request->getPost('apm');
    $fund = $this->request->getPost('fund');
    $engineer = $this->request->getPost('engineer');
    $year = $this->request->getPost('year');
    $status = (int) $this->request->getPost('Status');

$role = session()->get('role'); 
    $user_id = session()->get('id');

if (date('m') <= 3) {
        $fromdate = (date('Y')-1).'-04-01';
        $enddate = date('Y').'-03-31';
    } else {
        $fromdate = date('Y').'-04-01';
        $enddate = (date('Y')+1).'-03-31';
    }
    
    //  echo $fromdate;
    //  echo $enddate;
    //  die();

// Build the query with joins and selections
    $builder = $this->db->table('create_project');
    // $builder->select('create_project.*,expenditure.expenditure_date, 
    //                   department.department_name,department.id as department_id, 
    //                   constituency.constituency_name,constituency.id as constituency_id,
    //                   constituency_master.mla AS mla, 
    //                   roles_creation_apm.first_name AS apm_first_name, 
    //                   fund_master.fund_name AS fund_name,
    //                   roles_creation_engineer.first_name AS engineer_first_name,
    //                   status_master.status AS status,status_master.status_name,
    //                   GROUP_CONCAT(DISTINCT proposal_documents.document_name SEPARATOR ", ") AS proposal_document_names,
    //                   GROUP_CONCAT(DISTINCT estimation_documents.document_name SEPARATOR ", ") AS estimation_document_names,
    //                   GROUP_CONCAT(DISTINCT approval_documents.document_name SEPARATOR ", ") AS approval_document_names,
    //                   COALESCE(total_released.total_released_amount, 0) AS total_released_amount,
    //                   COALESCE(total_expenditure.total_expenditure, 0) AS total_expenditure,
    //                   COALESCE(previous_year_released.previous_year_released, 0) AS previous_year_released, 
    //                   COALESCE(present_year_released.present_year_released, 0) AS present_year_released,
    //                   COALESCE(last_released.last_released_amount, 0) AS last_released_amount,
    //                   COALESCE(last_released.last_released_date, NULL) AS last_released_date'
    //                   );
    
    $builder->select('create_project.*,expenditure.expenditure_date, 
                  department.department_name,department.id as department_id, 
                  constituency.constituency_name,constituency.id as constituency_id,
                  constituency_master.mla AS mla, 
                  roles_creation_apm.first_name AS apm_first_name, 
                  fund_master.fund_name AS fund_name,
                  roles_creation_engineer.first_name AS engineer_first_name,
                  status_master.status AS status,status_master.status_name,
                  GROUP_CONCAT(DISTINCT proposal_documents.document_name SEPARATOR ", ") AS proposal_document_names,
                  GROUP_CONCAT(DISTINCT estimation_documents.document_name SEPARATOR ", ") AS estimation_document_names,
                  GROUP_CONCAT(DISTINCT approval_documents.document_name SEPARATOR ", ") AS approval_document_names,
                  COALESCE(total_released.total_released_amount, 0) AS total_released_amount,
                  COALESCE(total_expenditure.total_expenditure, 0) AS total_expenditure,
                  COALESCE(previous_year_released.previous_year_released, 0) AS previous_year_released, 
                  COALESCE(present_year_released.present_year_released, 0) AS present_year_released,
                  COALESCE(last_released.last_released_amount, 0) AS last_released_amount,
                  COALESCE(last_released.last_released_date, NULL) AS last_released_date,
               COALESCE(
    CASE 
        WHEN 
            total_released.total_released_amount < create_project.Total_amount
        AND total_expenditure.total_expenditure >= total_released.total_released_amount
        THEN 1 
        ELSE 0 
    END, 0
) AS released_against_completed');

// Join related tables
    $builder->join('expenditure', 'expenditure.project_id = create_project.id', 'left');
    $builder->join('department', 'department.id = create_project.department', 'left');
    $builder->join('fund_master', 'fund_master.id = create_project.fund_wise', 'left');
    $builder->join('constituency', 'constituency.id = create_project.constituency', 'left');
    $builder->join('constituency_master', 'constituency_master.constituency_name = create_project.constituency', 'left');
    $builder->join('roles_creation AS roles_creation_apm', 'roles_creation_apm.id = create_project.apm', 'left');
    $builder->join('roles_creation AS roles_creation_engineer', 'roles_creation_engineer.id = create_project.engineer', 'left');
    $builder->join('status_master', 'status_master.status = create_project.status', 'left');  
    $builder->join('proposal_documents', 'proposal_documents.project_id = create_project.id', 'left');
    $builder->join('estimation_documents', 'estimation_documents.project_id = create_project.id', 'left');
    $builder->join('approval_documents', 'approval_documents.project_id = create_project.id', 'left');
    $builder->join('(SELECT project_id, SUM(released_amount) AS total_released_amount FROM released_amounts GROUP BY project_id) AS total_released', 'total_released.project_id = create_project.id', 'left');
    $builder->join('(SELECT project_id, SUM(expenditure_amount) AS total_expenditure FROM expenditure GROUP BY project_id) AS total_expenditure', 'total_expenditure.project_id = create_project.id', 'left');
    
    
    // $builder->join("(SELECT project_id, SUM(released_amount) AS previous_year_released 
    //                  FROM released_amounts 
    //                  WHERE YEAR(released_date) = $fromdate 
    //                  GROUP BY project_id) AS previous_year_released", 
    //                 'previous_year_released.project_id = create_project.id', 'left');
    
    
    $builder->join("(SELECT project_id, SUM(released_amount) AS previous_year_released 
                    FROM released_amounts 
                      WHERE released_date < '$fromdate'
                      GROUP BY project_id) AS previous_year_released", 
                   'previous_year_released.project_id = create_project.id', 'left');
                    
                    
    $builder->join("(SELECT project_id, SUM(released_amount) AS present_year_released 
                     FROM released_amounts 
                     WHERE released_date BETWEEN '$fromdate' AND '$enddate'
                     GROUP BY project_id) AS present_year_released", 
                    'present_year_released.project_id = create_project.id', 'left');
                    
                    
                    
    $builder->join("(SELECT project_id, 
                            released_amount AS last_released_amount, 
                            released_date AS last_released_date 
                     FROM released_amounts 
                     WHERE (project_id, released_date) IN 
                           (SELECT project_id, MAX(released_date) 
                            FROM released_amounts 
                            GROUP BY project_id)
                    ) AS last_released", 
                   'last_released.project_id = create_project.id', 'left');

$builder->groupBy('create_project.id');
    
    // 11/8/2025
    // $builder->where('create_project.project_status != 1');
 
if (in_array($status, [1, 2, 3])) {
    // Progress / Not Started / Completed
    $builder->where('create_project.status', $status);
    $builder->where('create_project.project_status', 0); 
    $builder->having('released_against_completed', 0);

} elseif ($status === 4) {
    // Closed / Completed projects
    $builder->where('create_project.project_status', 1);
    $builder->having('released_against_completed', 0); // optional, safe to keep

} elseif ($status === 5) {

$builder->where('create_project.project_status', 0);
    $builder->having('released_against_completed', 1);

} else {

$builder->where('create_project.project_status', 0);

}
// -----------------------------------------------------------------------------------------------
if (date('m') <= 3) {
  
    $current_fy_start = date('Y', strtotime('-1 year'));
    $current_fy_end   = date('y');
} else {
   
    $current_fy_start = date('Y');
    $current_fy_end   = date('y', strtotime('+1 year'));
}

$current_adm_financial_year = $current_fy_start . '-' . $current_fy_end;

//-----------------------------------------------------------------------------------------------------------------------------

//today update:  7/8/25

$builder->groupStart()

// 1. Current FY
    ->where('create_project.adm_financial_year', $current_adm_financial_year)

// 2. Previous FY, not completed
    ->orGroupStart()
        ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
        ->where('create_project.status !=', 3)
    ->groupEnd()

// 3. Previous FY, completed, released in current FY
    ->orGroupStart()
        ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
        ->where("last_released.last_released_date >=", $fromdate)
        ->where("last_released.last_released_date <=", $enddate)
    ->groupEnd()

// 4: Previous FY but status updated in current year
 
//     ->orGroupStart()
//       ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
//       ->where('create_project.status', 3)
//       ->where("YEAR(create_project.status_updated_at)", date('Y')) 
//   ->groupEnd()

->orGroupStart()
    ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
    ->where('create_project.status', 3)
    ->groupStart()
        ->where("YEAR(create_project.status_updated_at)", date('Y'))
        // ->orWhere("YEAR(create_project.updated_at)", date('Y'))
    ->groupEnd()
->groupEnd()

// ->orGroupStart()
    //     ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
    //     ->where('create_project.status', 3)
    //     ->where("YEAR(COALESCE(NULLIF(create_project.updated_at, '0000-00-00 00:00:00.000000'), create_project.created_at))", date('Y'))
    // ->groupEnd()

// 5. Previous FY, completed, but amount not fully released
    ->orGroupStart()
        ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
        ->where('create_project.status', 3)
        ->where('create_project.total_amount != total_released.total_released_amount', null, false)
    ->groupEnd()
    
 
->orGroupStart()
    ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
    //->where('create_project.status', 3)
    ->where('create_project.total_amount = total_released.total_released_amount', null, false) // fully released
    ->where("expenditure.expenditure_date >=", $fromdate)
    ->where("expenditure.expenditure_date <=", $enddate)
->groupEnd()

->groupEnd();

//---------------------------------------------------------------------------------------------------------

if (!empty($constituency)) {
    if ($constituency == 34) {
        $builder->whereIn('create_project.constituency', [20, 22, 27]);

} else {
     
        $builder->where('create_project.constituency', $constituency);
    }
}

if (!empty($mla)) {
        $builder->where('create_project.mla', $mla);
    }
    
    if (!empty($mp)) {
        $builder->where('create_project.MP', $mp);
    }
    
    if (!empty($mlc)) {
        $builder->where('create_project.MLC', $mlc);
    }
    
    if (!empty($department)) {
        $builder->where('create_project.department', $department);
    }
    if (!empty($apm)) {
        $builder->where('create_project.apm', $apm);
    }
    if (!empty($engineer)) {
        $builder->where('create_project.engineer', $engineer);
    }
    if (!empty($fund)) {
        $builder->where('create_project.fund_wise', $fund);
    }
    if (!empty($year)) {
        $builder->where('create_project.adm_financial_year', $year); 
    }
    
    if ($role == 2) { // APM specific filter
        $builder->where('create_project.apm', $user_id);
    } elseif ($role == 3 || $role == 5) { // Engineer specific filter
        $builder->where('create_project.engineer', $user_id);
    } elseif ($role == 4) { // Supervisor specific filter
        $builder->where('create_project.supervisor', $user_id);
    }

$fromYear = $this->request->getPost('from_date');
$toYear   = $this->request->getPost('to_date');

if (!empty($fromYear) && !empty($toYear)) {
    $builder->where("CAST(SUBSTRING_INDEX(create_project.adm_financial_year, '-', 1) AS UNSIGNED) >=", intval($fromYear));
    $builder->where("CAST(SUBSTRING_INDEX(create_project.adm_financial_year, '-', 1) AS UNSIGNED) <=", intval($toYear));
}

$builder->orderBy('(department.preference = 0)', 'ASC')
        ->orderBy('department.preference', 'ASC')
    
 ->orderBy('create_project.id', 'ASC');

//   ->orderBy('constituency.constituency_name', 'ASC');
    // $builder->orderBy('constituency.constituency_name', 'ASC');
    // $builder->orderBy('constituency_master.mla', 'ASC');

//  $this->db = \Config\Database::connect();
//  $data = $builder->get()->getResult();
//  echo $this->db->getLastQuery(); die();
 
 
    $query = $builder->get();
    $result = $query->getResultArray();

return $this->response->setJSON(['result' => 1, 'table' => $result]);
}

public function get_report_cash() {
    // Get filters from POST request
    $constituency = $this->request->getPost('constituency');
    $mla = $this->request->getPost('mla');
    $mp = $this->request->getPost('mp');
    $mlc = $this->request->getPost('mlc');
    $department = $this->request->getPost('department');
    $apm = $this->request->getPost('apm');
    $fund = $this->request->getPost('fund');
    $engineer = $this->request->getPost('engineer');
    $year = $this->request->getPost('year');
    $status = (int) $this->request->getPost('Status');

$role = session()->get('role'); 
    $user_id = session()->get('id');

// Build the query with joins and selections
    $builder = $this->db->table('create_project');
    $builder->select('create_project.*,expenditure.expenditure_date, 
                      department.department_name,department.id as department_id, 
                      constituency.constituency_name,constituency.id as constituency_id,
                      constituency_master.mla AS mla, 
                      roles_creation_apm.first_name AS apm_first_name, 
                      fund_master.fund_name AS fund_name,
                      roles_creation_engineer.first_name AS engineer_first_name,
                      status_master.status AS status,status_master.status_name,
                      GROUP_CONCAT(DISTINCT proposal_documents.document_name SEPARATOR ", ") AS proposal_document_names,
                      GROUP_CONCAT(DISTINCT estimation_documents.document_name SEPARATOR ", ") AS estimation_document_names,
                      GROUP_CONCAT(DISTINCT approval_documents.document_name SEPARATOR ", ") AS approval_document_names,
                      COALESCE(total_released.total_released_amount, 0) AS total_released_amount,
                      COALESCE(total_expenditure.total_expenditure, 0) AS total_expenditure,
                      COALESCE(total_expenditure_new.cash, 0) AS total_cash,
                      COALESCE(previous_year_released.previous_year_released, 0) AS previous_year_released, 
                      COALESCE(present_year_released.present_year_released, 0) AS present_year_released,
                      COALESCE(last_released.last_released_amount, 0) AS last_released_amount,
                      COALESCE(last_released.last_released_date, NULL) AS last_released_date,
               COALESCE(
          CASE 
        WHEN 
            total_released.total_released_amount < create_project.Total_amount
        AND total_expenditure.total_expenditure >= total_released.total_released_amount
        THEN 1 
        ELSE 0 
    END, 0
) AS released_against_completed');

$builder->join("(SELECT project_id, SUM(released_amount) AS previous_year_released 
                    FROM released_amounts 
                      WHERE released_date < '$fromdate'
                      GROUP BY project_id) AS previous_year_released", 
                   'previous_year_released.project_id = create_project.id', 'left');
                    
                    
    $builder->join("(SELECT project_id, SUM(released_amount) AS present_year_released 
                     FROM released_amounts 
                     WHERE released_date BETWEEN '$fromdate' AND '$enddate'
                     GROUP BY project_id) AS present_year_released", 
                    'present_year_released.project_id = create_project.id', 'left');
                    
                    
                    
    $builder->join("(SELECT project_id, 
                            released_amount AS last_released_amount, 
                            released_date AS last_released_date 
                     FROM released_amounts 
                     WHERE (project_id, released_date) IN 
                           (SELECT project_id, MAX(released_date) 
                            FROM released_amounts 
                            GROUP BY project_id)
                    ) AS last_released", 
                   'last_released.project_id = create_project.id', 'left');

$builder->groupBy('create_project.id');
    
    // 11/8/2025
    // $builder->where('create_project.project_status != 1');
    //

if ($status === 1 || $status === 2 || $status === 3) {

// User selected Progress / Not Started / Completed
    $builder->where('create_project.status', $status);
    $builder->where('create_project.project_status', 0);

} elseif ($status === 4) {

// User selected "Hide" → show only hidden projects
    $builder->where('create_project.project_status', 1);

} else {

// DEFAULT → show only active projects
    $builder->where('create_project.project_status', 0);
}

// -----------------------------------------------------------------------------------------------
if (date('m') <= 3) {
  
    $current_fy_start = date('Y', strtotime('-1 year'));
    $current_fy_end   = date('y');
} else {
   
    $current_fy_start = date('Y');
    $current_fy_end   = date('y', strtotime('+1 year'));
}

$current_adm_financial_year = $current_fy_start . '-' . $current_fy_end;

// print_r($fromdate);
// print_r($enddate);
// die();

//-----------------------------------------------------------------------------------
// $builder->groupStart()

//     // Current financial year projects
//     ->where('create_project.adm_financial_year', $current_adm_financial_year)

//     ->orGroupStart()
//         // previous FY but not completed
//         ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
//         ->where('create_project.status !=', 3)
//     ->groupEnd()

//     ->orGroupStart()
//         // previous FY but released in current FY
//         ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
//         ->where("last_released.last_released_date >=", $fromdate)
//         ->where("last_released.last_released_date <=", $enddate)
//     ->groupEnd()
    
    
    
    
//   // Previous FY but released in date range
// // ->orGroupStart()
// //     ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
// //     ->where("YEAR(last_released.last_released_date) >=", date('Y', strtotime($fromdate)))
// //     ->where("YEAR(last_released.last_released_date) <=", date('Y', strtotime($enddate)))
// // ->groupEnd()

//  // New: Previous FY but status updated in current year
//     ->orGroupStart()
//       ->where('create_project.adm_financial_year !=', $current_adm_financial_year)
//       ->where('create_project.status', 3)
//       ->where("YEAR(create_project.created_at)", date('Y')) 
//   ->groupEnd()

// ->groupEnd();

//-----------------------------------------------------------------------------------------------------------------------------

//today update:  7/8/25

$builder->groupStart()

// 1. Current FY
    ->where('create_project.adm_financial_year', $current_adm_financial_year)

->groupEnd();

//---------------------------------------------------------------------------------------------------------
   if ($constituency) {
    $builder->where('create_project.constituency', $constituency);
     }

$builder->orderBy('(department.preference = 0)', 'ASC')
        ->orderBy('department.preference', 'ASC')
    
 ->orderBy('create_project.id', 'ASC');

//   ->orderBy('constituency.constituency_name', 'ASC');
    // $builder->orderBy('constituency.constituency_name', 'ASC');
    // $builder->orderBy('constituency_master.mla', 'ASC');

return $this->response->setJSON(['result' => 1, 'table' => $result]);
}

}
 ?>