EVOLUTION-NINJA

Edit File: Dashboard.php

<?php

namespace App\Controllers;

class Dashboard extends BaseController{
   
    
public function __construct()  
{ 
   $this->db = \Config\Database::connect(); 
          
}    
  
// public function index()
// {
//     $id = session()->get('id');

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

//         $project_count = $builder->countAllResults();
//         $data['project'] = $project_count;

//         $start = date('Y-m-d 00:00:00', strtotime("-1 day")); 
//         $end = date('Y-m-d 23:59:59');

//         $builder->where('created_at >=', $start);
//         $builder->where('created_at <=', $end);
//         $projects_added_yesterday = $builder->countAllResults();
//         $data['projects_added_yesterday'] = $projects_added_yesterday;

//         $builder->where('created_at >=', $start);
//         $builder->where('created_at <=', $end);
//         $builder->where('status',1);
//         $progress_added_yesterday = $builder->countAllResults();
//         $data['progress_added_yesterday'] = $progress_added_yesterday;

//         $builder->where('created_at >=', $start);
//         $builder->where('created_at <=', $end);
//         $builder->where('status', 2);
//         $started_added_yesterday = $builder->countAllResults();
//         $data['started_added_yesterday'] = $started_added_yesterday;

//         $builder->where('created_at >=', $start);
//         $builder->where('created_at <=', $end);
//         $builder->groupStart(); 
//         $builder->where('status', 3);
//         $builder->orWhere('status', 4);
//         $builder->groupEnd(); 
//         $completed_added_yesterday = $builder->countAllResults();
//         $data['completed_added_yesterday'] = $completed_added_yesterday;

//         $builder->where('status', 1);
//         $progress = $builder->countAllResults();
//         $data['progress'] = $progress;

//         $builder->where('status', 2);
//         $to_be_started = $builder->countAllResults();
//         $data['to_be_started'] = $to_be_started;

//         $builder->groupStart(); 
//         $builder->where('status', 3);
//         $builder->orWhere('status', 4);
//         $builder->groupEnd();
//         $completed = $builder->countAllResults();
//         $data['completed'] = $completed;
        
        
//   $builder = $this->db->table('released_amounts');
    
    
//     $builder->select('YEAR(released_date) as year, SUM(released_amount) as total_amount');
//     $builder->groupBy('year');
//     $query = $builder->get();

//     $data['fund_released'] = $query->getResultArray();  
//         return view('dashboard/dashboard', $data);
//     } else {
//         return redirect()->to('/');
//     }
// }

public function index()
{
    $id = session()->get('id');
    $user_role = session()->get('role'); 
    $user_id = session()->get('id');

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

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

// $project_count = $builder->countAllResults(false); 
        // $data['project'] = $project_count;
     $builder->selectSum('sl_no', 'total_sl_no'); 
$query = $builder->get();

$total_sl_no = $query->getRow()->total_sl_no;
$data['project'] = $total_sl_no;

// Date range for yesterday
        $start = date('Y-m-d 00:00:00', strtotime("-1 day"));
        $end = date('Y-m-d 23:59:59');

// Count projects added yesterday
        $builder->where('created_at >=', $start)
                ->where('created_at <=', $end);
        $projects_added_yesterday = $builder->countAllResults(false);
        $data['projects_added_yesterday'] = $projects_added_yesterday;

$builder->where('status', 1);
        $progress_added_yesterday = $builder->countAllResults(false);
        $data['progress_added_yesterday'] = $progress_added_yesterday;

// Started projects added yesterday
        $builder->where('status', 2);
        $started_added_yesterday = $builder->countAllResults(false);
        $data['started_added_yesterday'] = $started_added_yesterday;

$builder->groupStart()
                ->where('status', 3)
                ->orWhere('status', 4)
                ->groupEnd();
        $completed_added_yesterday = $builder->countAllResults(false);
        $data['completed_added_yesterday'] = $completed_added_yesterday;

// Total progress projects
        // $builder->resetQuery(); // Reset the builder
        // $builder->where('status', 1);
        // 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})");
        // }
        // $progress = $builder->countAllResults(false);
        // $data['progress'] = $progress;
        
        $builder->resetQuery();
$builder->selectSum('sl_no', 'total_sl_no');
$builder->where('status', 1);

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})");
}
$query = $builder->get();
$progress = $query->getRow()->total_sl_no; 
$data['progress'] = $progress;

// $builder->resetQuery();
        // $builder->where('status', 2);
        // 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})");
        // }
        // $to_be_started = $builder->countAllResults(false);
        // $data['to_be_started'] = $to_be_started;
        
$builder->resetQuery();
$builder->selectSum('sl_no', 'total_sl_no');
$builder->where('status', 2);

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})");
}
$query = $builder->get();
$to_be_started = $query->getRow()->total_sl_no; 
$data['to_be_started'] = $to_be_started;

// // Total completed projects
        // $builder->resetQuery();
        // $builder->groupStart()
        //         ->where('status', 3)
        //         ->groupEnd();
        // 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})");
        // }
        // $completed = $builder->countAllResults(false);
        // $data['completed'] = $completed;
        
$builder->resetQuery();
$builder->selectSum('sl_no', 'total_sl_no');
$builder->where('status', 3);

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})");
}
$query = $builder->get();
$completed = $query->getRow()->total_sl_no; 
$data['completed'] = $completed;

// Funds released by year
        // $builder = $this->db->table('released_amounts');
        // $builder->select('YEAR(released_date) as year, SUM(released_amount) as total_amount');
        // $builder->groupBy('year');
        // $query = $builder->get();
        // $data['fund_released'] = $query->getResultArray();
        
        $builder = $this->db->table('released_amounts');
$builder->select('YEAR(released_date) as year, SUM(released_amount) as total_amount');

if (in_array($user_role, [2, 3, 4, 5])) {
    // Add condition to join with create_project and filter based on roles
    $builder->join('create_project', 'released_amounts.project_id = create_project.id', 'inner');
    $builder->where("(create_project.apm = {$user_id} OR create_project.engineer = {$user_id} OR create_project.supervisor = {$user_id})");
}

$builder->groupBy('year');
$query = $builder->get();
$data['fund_released'] = $query->getResultArray();

return view('dashboard/dashboard', $data);
    } else {
        return redirect()->to('/');
    }
}

// public function index()

//     {
//           $id=session()->get('id');
     
        
//         if($id){
//             $builder = $this->db->table('create_project');
  
//             $project_count=$builder->countAllResults();
            
//             $data['project']=$project_count;
            
//              $yesterday = date('Y-m-d', strtotime("-1 day"));
    
    
   
//               $builder->where('DATE(created_at)', $yesterday); 
//                  $projects_added_yesterday = $builder->countAllResults();
//               $data['projects_added_yesterday'] = $projects_added_yesterday;
            
         
//           $builder->where('DATE(created_at)', $yesterday); 
//             $builder->where('status', 15);
//             $progress_added_yesterday=$builder->countAllResults();
//             $data['progress_added_yesterday']=$progress_added_yesterday; 
            
//             $builder->where('DATE(created_at)', $yesterday); 
//             $builder->where('status', 16);
//             $started_added_yesterday=$builder->countAllResults();
//             $data['started_added_yesterday']=$started_added_yesterday; 
            
          
    
//             $builder->where('DATE(created_at)', $yesterday); 
//             $builder->where('status', 22);
//             $builder->where('status', 23);
//             $completed_added_yesterday=$builder->countAllResults();
//             $data['completed_added_yesterday']=$completed_added_yesterday; 
            
            
//             $builder->where('status', 15);
//             $progress=$builder->countAllResults();
//             $data['progress']=$progress; 
              
            
//             $builder->where('status', 17);
//             $to_be_started=$builder->countAllResults();
//             $data['to_be_started']=$to_be_started;
        
//           $builder->groupStart();
//           $builder->where('status', 22);
//           $builder->orWhere('status', 23);
//           $builder->groupEnd();
//             $completed=$builder->countAllResults();
//             $data['completed']=$completed;
        
  
//             return view('dashboard/dashboard' ,$data);
         
            
//         }else{
//             return redirect()->to('/');
//         }
        
//     }

}