EVOLUTION-NINJA

Edit File: Report.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.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();

// 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,
                      COALESCE(last_released.last_released_amount, 0) AS last_released_amount,
                      COALESCE(last_released.last_released_date, NULL) AS last_released_date');

// 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);
}

$currentYear = date('Y');

//project completed in previous year

// $builder->where('
// (
//     create_project.status != 3
//     OR (
//         create_project.status = 3
//         AND (
//             last_released.last_released_date IS NULL
//             OR YEAR(last_released.last_released_date) = '.$currentYear.'
//         )
//     )
// )
// AND (
//     last_released.last_released_date IS NULL
//     OR last_released.last_released_date <= "'.$fromdate.'"
// )', null, false);

//status completed but full amount not sanctioned in previous year
$currentYear = date('Y');

$builder->where('
(
    create_project.status != 3
    OR (
        create_project.status = 3
        AND (
            create_project.total_amount != total_released.total_released_amount
        )
        AND (
            last_released.last_released_date IS NULL
            OR YEAR(last_released.last_released_date) = '.$currentYear.'
        )
    )
)
AND (
    last_released.last_released_date IS NULL
    OR last_released.last_released_date <= "'.$fromdate.'"
)', 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.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]);
}

}
 ?>