EVOLUTION-NINJA

Edit File: Project.php

<?php
namespace App\Controllers;
use App\Models\Project_model;

class Project extends BaseController{

public function __construct()  
    { 
        $this->db = \Config\Database::connect(); 
          date_default_timezone_set('Asia/Kolkata');
          
}

public function create_project() {
    $projectName= $this->request->getPost('projectName');
    $total_amount= $this->request->getPost('total_amount');
    $page_no=$this->request->getpost('page_no');
    $department = $this->request->getPost('department');
    $Constituency = $this->request->getPost('constituency');
    $fundwise = $this->request->getPost('fundwise');
    $slno = $this->request->getPost('slno');
    $apm = $this->request->getPost('apm');
    $supervisor = $this->request->getPost('supervisor');
    $mla = $this->request->getPost('mla');
    $engineer = $this->request->getPost('engineer');
    $dop = $this->request->getPost('dop');
    $financial_year = $this->request->getPost('financial_year');
    $estimated_date = $this->request->getPost('est_sub_date');
    $estimated_cost = $this->request->getPost('estimated_cost');
    $date_approval = $this->request->getPost('date_approval');
    $adm_amount = $this->request->getPost('adm_amount');
    $released_amounts = $this->request->getPost('released_amount');
    $expenditures = $this->request->getPost('expenditure');
    $expenditure_dates=$this->request->getpost('expenditure_date');
    $released_dates = $this->request->getPost('released_date');
    $start_date = $this->request->getPost('start_date');
    $status = $this->request->getPost('status');
    $labour_contract = $this->request->getPost('labour_contract');
    $admDate = $this->request->getPost('admDate');
    $remarks = $this->request->getPost('remarks');
    $mp = $this->request->getPost('mp');
    $mlc = $this->request->getPost('mlc');
  
    $remarks_payment = $this->request->getPost('remarks_payment');
    $remarks_uploads = $this->request->getPost('remarks1');
    
    $created_at = date('Y-m-d H:i:s'); 
    $created_by = session()->get('username');

$data = [
        'project_name'=>$projectName,
        'Total_amount'=>$total_amount,
        'page_no'=>$page_no,
        'department' => $department,
        'Constituency' => $Constituency,
        'fund_wise' => $fundwise,
        'sl_no' => $slno,
        'apm' => $apm,
        'supervisor' => $supervisor,
        'engineer' => $engineer,
        'date_of_proposal' => $dop,
        'financial_year' => $financial_year,
        'est_sub_date' => $estimated_date,
        'estimated_cost' => $estimated_cost,
        'date_of_approval' => $date_approval,
        'adm_amount' => $adm_amount,
        'start_date' => $start_date,
        'status' => $status,
        'labour_contract' => $labour_contract,
        'remarks' => $remarks,
        'remarks_upload' => $remarks_uploads,
        'remarks_payment' => $remarks_payment,
        'mla'=>$mla,
        'MP'=>$mp,
        'MLC'=>$mlc,
        'adm_date'=>$admDate,
       
        'created_at' => $created_at,
        'created_by' => $created_by,
    ];

$documents = $this->request->getFiles();

$uploadDirectories = [
        'proposal' => 'public/proposal_documents/',
        'estimate' => 'public/estimation_documents/',
        'approval' => 'public/approval_documents/'
    ];
    
    $builder = $this->db->table('create_project');
    try {
        
        if ($builder->insert(array_filter($data))) {
            $projectId = $this->db->insertID();

if (is_array($released_amounts) && is_array($released_dates)) {
                $releasedAmountTable = $this->db->table('released_amounts');
          
            
                foreach ($released_amounts as $index => $released_amount) {
                    $released_date = $released_dates[$index];
                    
                    if (is_numeric($released_amount) && $released_amount > 0 && !empty($released_date)) {
                        $releasedAmountData = [
                            'project_id' => $projectId,
                            'released_amount' => $released_amount,
                            'released_date' => $released_date,
                            'created_at' =>$created_at,
                            'created_by' => $created_by,
                        ];
                        $releasedAmountTable->insert($releasedAmountData);
                    }
                }
            }
            
            if (is_array($expenditures) && is_array($expenditure_dates)) {
                $expenditureTable = $this->db->table('expenditure');
            
                foreach ($expenditures as $index => $expenditure) {
                    $expenditure_date = $expenditure_dates[$index];
            
                    if (is_numeric($expenditure) && $expenditure > 0 && !empty($expenditure_date)) {
                        $expenditureData = [
                            'project_id' => $projectId,  // Use the same project ID
                            'expenditure_amount' => $expenditure,
                            'expenditure_date' => $expenditure_date,
                            'created_at' => $created_at,
                            'created_by' => $created_by,
                        ];
                        $expenditureTable->insert($expenditureData);
                    }
                }
            }
            
            
          
            foreach ($uploadDirectories as $key => $uploadDir) {
                if (isset($documents[$key]) && is_array($documents[$key])) {
                    foreach ($documents[$key] as $document) {
                        if ($document->isValid() && !$document->hasMoved()) {
                            $originalName = $document->getClientName();
                            $docName = preg_replace('/[^a-zA-Z0-9\._-]/', '_', $originalName);
                            $document->move(ROOTPATH . $uploadDir, $docName);

$documentData = [
                                'project_id' => $projectId,
                                'document_name' => $docName,
                                'created_at' => $created_at,
                                'created_by' => $created_by,
                            ];

// Insert into the respective document table
                            switch ($key) {
                                case 'proposal':
                                    $this->db->table('proposal_documents')->insert($documentData);
                                    break;
                                case 'estimate':
                                    $this->db->table('estimation_documents')->insert($documentData);
                                    break;
                                case 'approval':
                                    $this->db->table('approval_documents')->insert($documentData);
                                    break;
                            }
                        }
                    }
                }
            }

$notificationTable = $this->db->table('notifications');
            $message = "A new project '{$projectName}' has been created.";

// Insert notification for APM
            if ($apm) {
                $notificationTable->insert([
                    'user_id' => $apm,
                    'message' => $message,
                    'created_at' => $created_at
                ]);
            }

// Insert notification for Engineer
            if ($engineer) {
                $notificationTable->insert([
                    'user_id' => $engineer,
                    'message' => $message,
                    'created_at' => $created_at
                ]);
            }
          
             // Insert notification for Engineer
            if ($supervisor) {
                $notificationTable->insert([
                    'user_id' => $supervisor,
                    'message' => $message,
                    'created_at' => $created_at
                ]);
            }

return $this->response->setJSON(['result' => 1, 'message' => 'Create project successfully']);
        } else {
            return $this->response->setJSON(['result' => 0, 'message' => 'Failed to create project']);
        }
    } catch (\Exception $e) {
        return $this->response->setJSON(['result' => 0, 'message' => $e->getMessage()]);
    }
}

public function fetch_department(){

$db = \Config\Database::connect();
    $builder = $db->table('department'); 
    $builder->select('id ,department_name'); 
    $query = $builder->get();
    $departments = $query->getResultArray();

if ($departments) {
        return $this->response->setJSON($departments);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'No department found']);
    }
}

public function fetch_Constituency(){

$db = \Config\Database::connect();
    $builder = $db->table('Constituency'); 
    $builder->select('id ,constituency_name'); 
    $query = $builder->get();
    $Constituency= $query->getResultArray();

if ($Constituency) {
        return $this->response->setJSON($Constituency);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'No Constituency found']);
    }
}

public function fetch_mp(){
    $db = \Config\Database::connect();
    $builder = $db->table('constituency_master'); 
    $builder->select('id,mp'); 
    $builder->where('mp !=', ''); 
    $query = $builder->get();
    $Constituency= $query->getResultArray();

if ($Constituency) {
        return $this->response->setJSON($Constituency);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'No Constituency found']);
    }

}
public function fetch_mlc(){
    $db = \Config\Database::connect();
    $builder = $db->table('constituency_master'); 
    $builder->select('id ,mlc'); 
    $builder->where('mlc !=', '');    
    $query = $builder->get();
    $Constituency= $query->getResultArray();

if ($Constituency) {
        return $this->response->setJSON($Constituency);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'No Constituency found']);
    }

}
public function fetch_mla_by_constituency($id){
    $db = \Config\Database::connect();
    $builder = $db->table('constituency_master'); 
    $builder->select('id ,mla'); 
    $builder->where('constituency_name',$id);
    $query = $builder->get();
    $Constituency= $query->getResultArray();

if ($Constituency) {
        return $this->response->setJSON($Constituency);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'No Constituency found']);
    }

}
public function fetch_mla_without_constituency(){
    $db = \Config\Database::connect();
    $builder = $db->table('constituency_master'); 
    $builder->select('id, mla'); 
    // Check for both empty string and NULL
    $builder->where('constituency_name', '')->orWhere('constituency_name IS NULL', null, false);
    $query = $builder->get();
    $Constituency = $query->getResultArray();

if (!empty($Constituency)) {
        return $this->response->setJSON($Constituency);
    } else {
        return $this->response->setJSON([]); // Return an empty array for consistency
    }
}

public function fetch_supervisor(){

$db = \Config\Database::connect();
    $builder = $db->table('users'); 
    $builder->select('id ,username'); 
    $builder->where('role','4'); 
    $query = $builder->get();
    $supervisor = $query->getResultArray();

if ($supervisor) {
        return $this->response->setJSON($supervisor);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'No supervisor found']);
    }

}

public function fetch_engineer(){

$db = \Config\Database::connect();
   $builder = $db->table('users'); 
    $builder->select('id,username,role');
  $builder->where('role', '3');
    $builder->orWhere('role', '5');
    $query = $builder->get();
    $engineer = $query->getResultArray();

if ($engineer) {
        return $this->response->setJSON($engineer);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'No engineer found']);
    }

}

public function project_list_data() {
    $session = \Config\Services::session();
    $user_id = $session->get('id');  
    $user_role = $session->get('role');
    
    
    $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');

$builder = $this->db->table('create_project');

$builder->select('create_project.*, 
                      department.department_name, 
                      constituency.constituency_name, 
                      constituency_master.mla AS mla,
                      apm_user.username AS apm_first_name, 
                      apm_user.id AS apm_user_id, 
                      engineer_user.username AS engineer_first_name, 
                      engineer_user.id AS engineer_user_id,
                      supervisor_user.username AS supervisor_first_name,
                      supervisor_user.id AS supervisor_user_id,
                      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,
                      GROUP_CONCAT(DISTINCT progress_documents.document_name SEPARATOR ", ") AS progress_document_names,
                      COALESCE(total_released.total_released_amount, 0) AS total_released_amount,
                      COALESCE(total_expenditure.total_expenditure, 0) AS total_expenditure,
                      status_master.status,status_master.status_name,
                      fund_master.id AS fund_id, 
                      fund_master.fund_name');

// Join other tables
    $builder->join('department', 'department.id = create_project.department', 'left'); 
    $builder->join('constituency', 'constituency.id = create_project.constituency', 'left'); 
    $builder->join('constituency_master', 'constituency_master.constituency_name = create_project.constituency', 'left'); 
    $builder->join('users AS apm_user', 'apm_user.id = create_project.apm', 'left');
    $builder->join('users AS engineer_user', 'engineer_user.id = create_project.engineer', 'left');
    $builder->join('users AS supervisor_user', 'supervisor_user.id = create_project.supervisor', '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('progress_documents', 'progress_documents.project_id = create_project.id', 'left');

// Join the status_master and fund_master tables
    $builder->join('status_master', 'status_master.status = create_project.status', 'left');
    $builder->join('fund_master', 'fund_master.id = create_project.fund_wise', 'left');

// Join for released_amounts and expenditures
    $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');

if (in_array($user_role, [2, 3, 4,5])) {
        $builder->where("(create_project.apm = {$user_id} OR create_project.engineer = {$user_id} OR create_project.supervisor = {$user_id})");
    }

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

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

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

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

if ($fund) {
            $builder->where('create_project.fund_wise', $fund);
        }

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

if ($year) {
            $builder->where('create_project.financial_year', $year);
        }
        
        if ($status == 2) {
      $builder->where('(create_project.date_of_approval IS NULL OR create_project.date_of_approval = "")', null, false);
         } elseif ($status == 3) { // Action Plan
            $builder->where('create_project.date_of_approval IS NOT NULL AND create_project.date_of_approval != ""', null, false);
    }
    $builder->groupBy('create_project.id'); 
    // $builder->orderBy('create_project.created_at', 'DESC');
    $builder->orderBy('create_project.id', 'DESC');

$query = $builder->get();
    $data = $query->getResultArray();

if ($data) {
        return $this->response->setJSON($data);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'Failed to load data']); 
    }
}

// public function project_list_data() {
//     $session = \Config\Services::session();
//     $user_id = $session->get('id');  
//     $user_role = $session->get('role');

//     $builder = $this->db->table('create_project');

//     $builder->select('create_project.*, 
//                       department.department_name, 
//                       constituency.constituency_name, 
//                       constituency_master.mla AS mla,
//                       apm_user.username AS apm_first_name, apm_user.id as apm_user_id, 
//                       engineer_user.username AS engineer_first_name, engineer_user.id as engineer_user_id,
//                       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');

//     $builder->join('department', 'department.id = create_project.department', 'left'); 
//     $builder->join('constituency', 'constituency.id = create_project.constituency', 'left'); 
//     $builder->join('constituency_master', 'constituency_master.constituency_name = create_project.constituency', 'left'); 
//     $builder->join('users AS apm_user', 'apm_user.id = create_project.apm', 'left');
//     $builder->join('users AS engineer_user', 'engineer_user.id = create_project.engineer', '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');

//     // If user role is 2, 3, or 4, show only relevant data
//     if (in_array($user_role, [2, 3, 4])) {
       
//         $builder->where("(create_project.apm = {$user_id} OR create_project.engineer = {$user_id})");
//     }

//     $builder->groupBy('create_project.id'); 
//     $builder->orderBy('create_project.created_at', 'DESC');

//     $query = $builder->get();
//     $data = $query->getResultArray();

//     if ($data) {
//         return $this->response->setJSON($data);
//     } else {
//         return $this->response->setJSON(['result' => 0, 'message' => 'Failed to load data']); 
//     }
// }

public function project_delete($id){
    try {
        $builder = $this->db->table('create_project');
        $builder->where('id', $id);
        $projectDelete=$builder->delete();
        
        $builder=$this->db->table('released_amounts');
        $builder->where('project_id',$id);
        $releasedDelete=$builder->delete();
        
        if ($projectDelete && $releasedDelete) {
            return $this->response->setJSON(['result' => 1, 'message' => 'project deleted successfully']);
        } else {
            return $this->response->setJSON(['result' => 0, 'message' => 'Failed to delete project']);
        }
    } catch (\Exception $e) {
        return $this->response->setJSON(['result' => 0, 'message' => $e->getMessage()]);
    }
}

public function project_edit($id) {
    $db = \Config\Database::connect();
    $builder = $db->table('create_project');

$builder->select('create_project.*, constituency.constituency_name AS constituency_name');
    $builder->join('constituency', 'constituency.id = create_project.Constituency', 'left');
    $project = $builder->getWhere(['create_project.id' => $id])->getRowArray();
    
    if (!$project) {
        return redirect()->to('/projects')->with('error', 'Project not found');
    }

$departmentBuilder = $db->table('department');
    $departments = $departmentBuilder->select('id, department_name')->get()->getResultArray();

$approvalDocsBuilder = $db->table('approval_documents');
    $approvalDocuments = $approvalDocsBuilder->select('document_name')
                                             ->where('project_id', $id)
                                             ->get()
                                             ->getResultArray();
    foreach ($approvalDocuments as &$doc) {
        $doc['document_path'] = base_url('public/approval_documents/' . $doc['document_name']);
    }

$estimationDocsBuilder = $db->table('estimation_documents');
    $estimationDocuments = $estimationDocsBuilder->select('document_name')
                                                 ->where('project_id', $id)
                                                 ->get()
                                                 ->getResultArray();
    foreach ($estimationDocuments as &$doc) {
        $doc['document_path'] = base_url('public/estimation_documents/' . $doc['document_name']);
    }

$progressDocsBuilder = $db->table('progress_documents');
    $progressDocuments =  $progressDocsBuilder->select('document_name,remarks,created_at,status,created_by')
                                                 ->where('project_id', $id)
                                                 ->get()
                                                 ->getResultArray();
    foreach ($progressDocuments as &$doc) {
        $doc['document_path'] = base_url('public/Progress_documents/' . $doc['document_name']);
    }

$apmBuilder = $db->table('users');
    $apms = $apmBuilder->select('id, username')
                        ->where('role', 2) 
                        ->get()
                        ->getResultArray();

$engineerBuilder = $db->table('users');
    $engineers = $engineerBuilder->select('id, username')
                             ->where('role', 3)  
                            ->orwhere('role', 5)  
                             ->get()
                             ->getResultArray();
                             
    $supervisorBuilder = $db->table('users');
    $supervisors = $supervisorBuilder->select('id, username')
                             ->where('role', 4)  
                           
                             ->get()
                             ->getResultArray();
                             
    $proposalDocsBuilder = $db->table('proposal_documents');
    $proposalDocuments = $proposalDocsBuilder->select('document_name')
                                             ->where('project_id', $id)
                                             ->get()
                                             ->getResultArray();
    foreach ($proposalDocuments as &$doc) {
        $doc['document_path'] = base_url('public/proposal_documents/' . $doc['document_name']);
    }

$constituencyBuilder = $db->table('constituency');
    $constituencies = $constituencyBuilder->select('id, constituency_name')->get()->getResultArray();

$mlaBuilder = $db->table('constituency_master');
    $mla = $mlaBuilder->select('id, mla')
                      ->where('constituency_name', $project['Constituency'])
                      ->get()
                      ->getResultArray();

$releasedAmountBuilder = $db->table('released_amounts');
    $releasedAmounts = $releasedAmountBuilder->select('released_amount, released_date')
                                             ->where('project_id', $id)
                                             ->get()
                                             ->getResultArray();

$expenditureBuilder = $db->table('expenditure');
    $expenditures = $expenditureBuilder->select('expenditure_amount, expenditure_date')
                                        ->where('project_id', $id)
                                        ->get()
                                        ->getResultArray();

$statusBuilder = $db->table('status_master');
    $statuses = $statusBuilder->select('id, status,status_name')->get()->getResultArray();
    
        $fundBuilder = $db->table('fund_master');
    $funds = $fundBuilder->select('id, fund_name')->get()->getResultArray();

return view('createproject/project_edit', [
        'project' => $project,
        'departments' => $departments,
        'constituencies' => $constituencies,
        'approvalDocuments' => $approvalDocuments,
        'estimationDocuments' => $estimationDocuments,
        'proposalDocuments' => $proposalDocuments,
        'progressDocuments' => $progressDocuments,
        'releasedAmounts' => $releasedAmounts,
        'expenditures' => $expenditures,
        'apms' => $apms,
        'engineers' => $engineers,
        'supervisors'=>$supervisors,
        'mla' => $mla,
        'statuses' => $statuses,
        'funds'=>$funds,
        
    ]);
}

public function delete_document()
{
    if ($this->request->getpost()) {
        $documentName = $this->request->getJSON()->document_name;
        $projectId = $this->request->getJSON()->project_id;

$approvalDocsBuilder = $this->db->table('approval_documents');
        $approvalDocsBuilder->where('document_name', $documentName)
                            ->where('project_id', $projectId);

if ($approvalDocsBuilder->delete()) {

$filePath = ROOTPATH . 'public/approval_documents' . $documentName;
            if (file_exists($filePath)) {
                unlink($filePath);
            }

return $this->response->setJSON(['success' => true, 'message' => 'Document deleted successfully']);
        } else {
            return $this->response->setJSON(['success' => false, 'message' => 'Failed to delete document']);
        }
    }
    return $this->response->setJSON(['success' => false, 'message' => 'Invalid request']);
}

public function update_project($projectId) {
    $projectName= $this->request->getPost('projectName');
    $total_amount= $this->request->getPost('total_amount');
    // $page_no=$this->request->getpost('page_no');
    $department = $this->request->getPost('department');
    $Constituency = $this->request->getPost('constituency');
    $fundwise = $this->request->getPost('fundwise');
    $slno = $this->request->getPost('sl_no');
    $apm = $this->request->getPost('apm');
    $engineer = $this->request->getPost('engineer');
    $supervisor = $this->request->getPost('supervisor');
    $dop = $this->request->getPost('date_of_Proposal');
    $financial_year = $this->request->getPost('financial_year');
    $estimated_date = $this->request->getPost('est_sub_date');
    $estimated_cost = $this->request->getPost('estimated_cost');
    $date_approval = $this->request->getPost('date_of_approval');
    $adm_amount = $this->request->getPost('adm_amount');
    $released_amounts = $this->request->getPost('released_amount');
    $released_dates = $this->request->getPost('released_date');
    $expenditures = $this->request->getPost('expenditure');
    $expenditure_dates = $this->request->getPost('expenditure_date');
    $start_date = $this->request->getPost('start_date');
    $status = $this->request->getPost('status');
    $labour_contract = $this->request->getPost('labour_contract');
    $remarks = $this->request->getPost('remarks');
    $progress_remarks = $this->request->getPost('progress_remarks');
    $progress_date = $this->request->getPost('progress_date');
    
    $admDate = $this->request->getPost('admDate');
    $updated_at = date('Y-m-d H:i:s'); 
    $updated_by = session()->get('username');
  
// print_r($progress_date);die();
    $data = [
        'project_name'=>$projectName,
        'Total_amount'=>$total_amount,
        // 'page_no'=>$page_no,
        'department' => $department,
        'Constituency' => $Constituency,
        'fund_wise' => $fundwise,
        'sl_no' => $slno,
        'apm' => $apm,
        'supervisor' => $supervisor,
        'engineer' => $engineer,
        'date_of_proposal' => $dop,
        'financial_year' => $financial_year,
        'est_sub_date' => $estimated_date,
        'estimated_cost' => $estimated_cost,
        'date_of_approval' => $date_approval,
        'adm_amount' => $adm_amount,
        'start_date' => $start_date,
        'status' => $status,
        'labour_contract' => $labour_contract,
        'remarks' => $remarks,
         'adm_date'=>$admDate,
        'created_at' => $updated_at,
        'created_by' => $updated_by,
    ];

$builder = $this->db->table('create_project');
    
    try {
        if ($builder->where('id', $projectId)->update($data)) {

// Update released amounts and dates
            if (is_array($released_amounts) && is_array($released_dates)) {
                $releasedAmountTable = $this->db->table('released_amounts');
                $releasedAmountTable->where('project_id', $projectId)->delete(); 
            
                foreach ($released_amounts as $index => $released_amount) {
                    $released_date = $released_dates[$index];
                    
                    if (is_numeric($released_amount) && $released_amount > 0 && !empty($released_date)) {
                        $releasedAmountData = [
                            'project_id' => $projectId,
                            'released_amount' => $released_amount,
                            'released_date' => $released_date,
                            'created_at' =>$updated_at,
                            'created_by' =>  $updated_by,
                        ];
                        $releasedAmountTable->insert($releasedAmountData);
                    }
                }
            }

// Update expenditures and dates
            if (is_array($expenditures) && is_array($expenditure_dates)) {
                $expenditureTable = $this->db->table('expenditure');
                $expenditureTable->where('project_id', $projectId)->delete();

// Handle document uploads
            $documents = $this->request->getFiles();
            $uploadDirectories = [
                'proposal' => 'public/proposal_documents/',
                'estimate' => 'public/estimation_documents/',
                'approval' => 'public/approval_documents/',
                'progress' => 'public/Progress_documents/',
               
            ];

foreach ($uploadDirectories as $key => $uploadDir) {
    if (isset($documents[$key])) {
        foreach ($documents[$key] as $index => $document) { // use $index here
            if ($document->isValid() && !$document->hasMoved()) {
                $originalName = $document->getClientName();
                $docName = preg_replace('/[^a-zA-Z0-9\._-]/', '_', $originalName);
                $document->move(ROOTPATH . $uploadDir, $docName);
                
                $documentData = [
                    'project_id'     => $projectId,
                    'document_name'  => $docName,
                    'created_at'     => $updated_at,
                    'created_by'     => $updated_by,
                ];

switch ($key) {
                    case 'proposal':
                        $this->db->table('proposal_documents')->insert($documentData);
                        break;
                    case 'estimate':
                        $this->db->table('estimation_documents')->insert($documentData);
                        break;
                    case 'approval':
                        $this->db->table('approval_documents')->insert($documentData);
                        break;
                    case 'progress':
                        // Assign specific remark based on file index
                        $documentData['remarks']    = isset($progress_remarks[$index]) ? $progress_remarks[$index] : null;
                        $documentData['created_at'] = $progress_date;
                        $this->db->table('progress_documents')->insert($documentData);
                        break;
                }
            }
        }
    }
}

return $this->response->setJSON(['result' => 1, 'message' => 'Project updated successfully']);
        } else {
            return $this->response->setJSON(['result' => 0, 'message' => 'Failed to update project']);
        }
    } catch (\Exception $e) {
        return $this->response->setJSON(['result' => 0, 'message' => $e->getMessage()]);
    }
}

public function project_update_payment($projectId) {
    $remarks = $this->request->getPost('remarks');
    $updated_at = date('Y-m-d H:i:s'); 
    $updated_by = session()->get('username');
    $released_amounts = $this->request->getPost('released_amount');
    $released_dates = $this->request->getPost('released_date');
    $expenditures = $this->request->getPost('expenditure');
    $expenditure_dates = $this->request->getPost('expenditure_date');

$data = [
        'remarks' => $remarks,
        'created_at' => $updated_at,
        'created_by' => $updated_by,
    ];

$builder = $this->db->table('create_project');
    
    try {
        if ($builder->where('id', $projectId)->update($data)) {

if (is_array($released_amounts) && is_array($released_dates)) {
                $releasedAmountTable = $this->db->table('released_amounts');
                $releasedAmountTable->where('project_id', $projectId)->delete(); 
            
                foreach ($released_amounts as $index => $released_amount) {
                    $released_date = $released_dates[$index];
                    
                    if (is_numeric($released_amount) && $released_amount > 0 && !empty($released_date)) {
                        $releasedAmountData = [
                            'project_id' => $projectId,
                            'released_amount' => $released_amount,
                            'released_date' => $released_date,
                            'created_at' =>$updated_at,
                            'created_by' =>  $updated_by,
                        ];
                        $releasedAmountTable->insert($releasedAmountData);
                    }
                }
            }

if (is_array($expenditures) && is_array($expenditure_dates)) {
                $expenditureTable = $this->db->table('expenditure');
                $expenditureTable->where('project_id', $projectId)->delete();

foreach ($expenditures as $index => $expenditure) {
                    $expenditure_date = $expenditure_dates[$index];
            
                    if (is_numeric($expenditure) && $expenditure > 0 && !empty($expenditure_date)) {
                        $expenditureData = [
                            'project_id' => $projectId, 
                            'expenditure_amount' => $expenditure,
                            'expenditure_date' => $expenditure_date,
                            'created_at' => $updated_at,
                            'created_by' => $updated_by,
                        ];
                        $expenditureTable->insert($expenditureData);
                    }
                }
            }
            return $this->response->setJSON(['result' => 1, 'message' => 'Project updated successfully']);
        } else {
            return $this->response->setJSON(['result' => 0, 'message' => 'Failed to update project']);
        }
    } catch (\Exception $e) {
        return $this->response->setJSON(['result' => 0, 'message' => $e->getMessage()]);
    }
}

public function delete_released_amount()
{
    if ($this->request->getpost()) {
        $releasedAmountBuilder = $this->db->table('released_amounts');
        $projectId = $this->request->getPost('project_id');
        $releasedAmount = $this->request->getPost('released_amount');
        if ($projectId && $releasedAmount) {
            $releasedAmountBuilder->where('project_id', $projectId)
                                  ->where('released_amount', $releasedAmount)
                                  ->delete();

if ($this->db->affectedRows() > 0) {
                return $this->response->setJSON(['success' => true]);
            } else {
                return $this->response->setJSON(['success' => false, 'message' => 'Failed to delete released amount.']);
            }
        } else {
            return $this->response->setJSON(['success' => false, 'message' => 'Invalid data provided.']);
        }
    } else {
        throw new \CodeIgniter\Exceptions\PageNotFoundException();
    }
}

public function delete_expenditure_amount()
{
    if ($this->request->getpost()) {
        $expenditureBuilder = $this->db->table('expenditure');
        $projectId = $this->request->getPost('project_id');
        $expenditure = $this->request->getPost('expenditure_amount');
        // print_r($projectId);
        // die();
        if ($projectId && $expenditure) {
            $expenditureBuilder->where('project_id', $projectId)
                                  ->where('expenditure_amount', $expenditure)
                                  ->delete();

if ($this->db->affectedRows() > 0) {
                return $this->response->setJSON(['success' => true]);
            } else {
                return $this->response->setJSON(['success' => false, 'message' => 'Failed to delete expenditure amount.']);
            }
        } else {
            return $this->response->setJSON(['success' => false, 'message' => 'Invalid data provided.']);
        }
    } else {
        throw new \CodeIgniter\Exceptions\PageNotFoundException();
    }
}

public function project_payment_edit($id){
    $db = \Config\Database::connect();
    $builder = $db->table('create_project');

$builder->select('create_project.*');

$project = $builder->getWhere(['create_project.id' => $id])->getRowArray();
    
    if (!$project) {
        return redirect()->to('/projects')->with('error', 'Project not found');
    }

$releasedAmountBuilder = $db->table('released_amounts');

$releasedAmounts = $releasedAmountBuilder->select('released_amount, released_date')
                                             ->where('project_id', $id)
                                             ->get()
                                             ->getResultArray();

return view('createproject/project_payment_edit', [
        'project' => $project,
        
        'releasedAmounts' => $releasedAmounts,
        'expenditures' => $expenditures,
      
    ]);
 
}

public function delete_proposal_document()
{
    if ($this->request->getpost()) {
        $documentName = $this->request->getpost('document_name');
        $projectId = $this->request->getpost('project_id'); // 
      
        $approvalDocsBuilder = $this->db->table('proposal_documents');
        $approvalDocsBuilder->where('document_name', $documentName)
                            ->where('project_id', $projectId);

if ($approvalDocsBuilder->delete()) {

$filePath = ROOTPATH . 'public/proposal_documents' . $documentName;
            if (file_exists($filePath)) {
                unlink($filePath);
            }

public function delete_approval_document()
{
    if ($this->request->getpost()) {
        $documentName = $this->request->getpost('document_name');
        $projectId = $this->request->getpost('project_id'); // 
      
        $approvalDocsBuilder = $this->db->table('approval_documents');
        $approvalDocsBuilder->where('document_name', $documentName)
                            ->where('project_id', $projectId);

if ($approvalDocsBuilder->delete()) {

$filePath = ROOTPATH . 'public/approval_documents' . $documentName;
            if (file_exists($filePath)) {
                unlink($filePath);
            }

public function delete_estimate_document()
{
    if ($this->request->getpost()) {
        $documentName = $this->request->getpost('document_name');
        $projectId = $this->request->getpost('project_id'); // 
      
        $approvalDocsBuilder = $this->db->table('estimation_documents');
        $approvalDocsBuilder->where('document_name', $documentName)
                            ->where('project_id', $projectId);

if ($approvalDocsBuilder->delete()) {

$filePath = ROOTPATH . 'public/estimation_documents' . $documentName;
            if (file_exists($filePath)) {
                unlink($filePath);
            }

public function uploaded_documents($id){
 $db = \Config\Database::connect();
 
    $builder = $db->table('create_project');

$builder->select('create_project.*');

$project = $builder->getWhere(['create_project.id' => $id])->getRowArray();
    
    if (!$project) {
        return redirect()->to('/projects')->with('error', 'Project not found');
    }

$estimation_documentsBuilder = $db->table('estimation_documents');

$estimation_documents = $estimation_documentsBuilder->select('document_name')
                                             ->where('project_id', $id)
                                             ->get()
                                             ->getResultArray();

$proposal_documentsBuilder = $db->table('proposal_documents');
    $proposal_documents = $proposal_documentsBuilder->select('document_name')
                                        ->where('project_id', $id)
                                        ->get()
                                        ->getResultArray();

// print_r($proposal_documents);die();
    $approval_documentsBuilder = $db->table('approval_documents');
    $approval_documents = $approval_documentsBuilder->select('document_name')
                                        ->where('project_id', $id)
                                        ->get()
                                        ->getResultArray();
                                        
    $progress_documentsBuilder = $db->table('progress_documents');
    $progress_documents = $progress_documentsBuilder->select('document_name')
                                        ->where('project_id', $id)
                                        ->get()
                                        ->getResultArray();

return view('createproject/project_uploads', [
        'project' => $project,
        'estimation_documents' =>$estimation_documents,
        'proposal_documents' => $proposal_documents,
        'approval_documents' => $approval_documents,
        'progress_documents' => $progress_documents,
      
    ]);
 
 
}

public function approve()
{

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

if ($role == 2) {

$requestBody = $this->request->getJSON();

$approval = $requestBody->approval ?? null;
        $projectId = $requestBody->projectid ?? null;
        $docId = $requestBody->id ?? null;

if (!$approval || !$projectId || !$docId) {
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Missing required fields: approval, project ID, or document ID',
            ]);
        }

$builder = $this->db->table('progress_documents');
        $data = [
            'status' => $approval ? '1' : '0', 
        ];

$builder->where('project_id', $projectId);
        $builder->where('status', 'pending');

if ($builder->update($data)) {
            return $this->response->setJSON([
                'status' => 'success',
                'message' => 'Document approval updated successfully',
            ]);
        } else {
           
            log_message('error', 'Database Error: ' . json_encode($this->db->error()));

return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Failed to update document approval',
            ]);
        }
    } else {
       return $this->response->setJSON([
                'status' => 'error',
                'message' => "You don't have access to approve",
            ]);
    }
}

}?>