EVOLUTION-NINJA

Edit File: Excel.php

<?php

namespace App\Controllers;

use CodeIgniter\Controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xls;

class Excel extends Controller
{

public function __construct()  
    { 
        $this->db = \Config\Database::connect(); 
          
    } 
    
public function import()
{
    ini_set('memory_limit', '512M');
    
    $file = $this->request->getFile('import_file');
    $file_ext = $file->getClientExtension();
    $allowed_ext = ['xls', 'csv', 'xlsx'];

if (in_array($file_ext, $allowed_ext)) {
        $filePath = $file->getTempName();
        $spreadsheet = IOFactory::load($filePath);
        $data = $spreadsheet->getActiveSheet()->toArray();

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

foreach ($data as $row) {
            if ($count > 0) {
            
                $completed = $row[17];
                $progress = $row[18];
                $not_started = $row[19];

if ($completed == 1) {
                    $status = '3';
                } elseif ($progress == 1) {
                    $status = '1';
                } elseif ($not_started == 1) {
                    $status = '2';
                } else {
                    $status = '0'; 
                }

$adm_date = preg_match('/\d{2}\/\d{2}\/\d{4}/', $row[2], $match) ? $match[0] : null;
                
               
                             
                  $new_adm_date = $this->convertDate($adm_date);
                  
        
                  
                $builder->insert([
                    'department' => $row[0],
                    'project_name' => $row[1],
                     'adm_date' => $new_adm_date, 
                    'sl_no' => $row[3],
                    'Total_amount' => $row[4],
                    'Constituency' => $row[5],
                    'mla' => $row[6],
                    'fund_wise' => $row[7],
                    'APM' => $row[8],
                    'engineer' => $row[9],
                    'date_of_Proposal' => $row[10],
                    'financial_year' => $row[11],
                    'est_sub_date' => $row[12],
                    'estimated_cost' => $row[13],
                    'date_of_approval' => $row[14],
                    'adm_amount' => $row[15],
                    'start_date' => $row[16],
                    'status' => $status, 
                    'remarks' => $row[20],
                ]);
            } else {
                $count = 1;
            }
        }

return $this->response->setJSON(['result' => 1, 'message' => 'Import successfully']);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'Failed to Import']);
    }
}

private function convertDate($date)
{
    $date_parts = explode('/', $date);
    if (count($date_parts) == 3) {
     
        $day = str_pad($date_parts[0], 2, '0', STR_PAD_LEFT);
        $month = str_pad($date_parts[1], 2, '0', STR_PAD_LEFT);
        $year = $date_parts[2];

$reordered_date = $year. '-'. $month . '-' . $day;
        
                //   print_r($reordered_date);die();
        return $reordered_date;
        
        
    }
}

// public function import1()
// {
//     $file = $this->request->getFile('import_file');
//     $file_ext = $file->getClientExtension();
//     $allowed_ext = ['xls', 'csv', 'xlsx'];

//     if (in_array($file_ext, $allowed_ext)) {
//         $filePath = $file->getTempName();
//         $spreadsheet = IOFactory::load($filePath);
//         $data = $spreadsheet->getActiveSheet()->toArray();

// // print_r($data);die();
//         $builder = $this->db->table('released_amounts');
//         $projectBuilder = $this->db->table('create_project');
        
//         $count = 0;

//         foreach ($data as $row) {
//             if ($count > 0) {
               
                
//                 $raw_release_date = $row[2]; 
               
              
//                 $released_date = $this->convertDate($raw_release_date);

//                 // print_r($released_date);die();

//                 if (!$released_date) {
//                     $released_date = '1970-01-01'; 
//                 }

//                 $project = $projectBuilder->select('id')
//                                           ->where('project_name', $row[0])
//                                           ->get()
//                                           ->getRow();

//                 if ($project) {
//                     $builder->insert([
//                         'project_id' => $project->id ? $project->id: '',
//                         'released_amount' => $row[1], 
//                         'released_date' => $released_date,
//                     ]);
//                 } else {
             
//                       $builder->insert([
//                         'project_id' => '',
//                         'released_amount' => $row[1], 
//                         'released_date' => $released_date,
//                     ]);
//                 }
//             } else {
//                 $count = 1;
//             }
//         }

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

public function import1()
{
    $file = $this->request->getFile('import_file');
    $file_ext = $file->getClientExtension();
    $allowed_ext = ['xls', 'csv', 'xlsx'];

if (in_array($file_ext, $allowed_ext)) {
        $filePath = $file->getTempName();
        $spreadsheet = IOFactory::load($filePath);
        $data = $spreadsheet->getActiveSheet()->toArray();

// print_r($data);die();
        $builder = $this->db->table('released_amounts');
        $projectBuilder = $this->db->table('create_project');

$expenditureBuilder = $this->db->table('expenditure');
        
        $count = 0;

foreach ($data as $row) {
            if ($count > 0) {
               
                
                $raw_release_date = $row[2]; 
               
              
                $released_date = $this->convertDate($raw_release_date);
                $released_date1 = $this->convertDate($row[4]);

// print_r($released_date);die();
             
                if (!$released_date) {
                    $released_date = '1970-01-01'; 
                }
                if (!$released_date1) {
                    $released_date1 = '1970-01-01'; 
                }

$project = $projectBuilder->select('id')
                                              ->where('project_name', $row[0])
                                              ->get()
                                              ->getRow();

if ($project) {
                        if($row[1] != 0){
                            $builder->insert([
                                'project_id' => $project->id ? $project->id: '',
                                'released_amount' => $row[1], 
                                'released_date' => $released_date,
                            ]);
                        }

if($row[3] != 0){
                            $builder->insert([
                                'project_id' => $project->id ? $project->id: '',
                                'released_amount' => $row[3], 
                                'released_date' => $released_date1,
                            ]);
                        }

if($row[6] != 0){
                            $expenditureBuilder->insert([
                                'project_id' => $project->id ? $project->id: '',
                                'expenditure_amount ' => $row[6], 
                                'expenditure_date' => '2024-12-31',
                            ]);
                        }

} else {
                 
                        if($row[1] != 0){
                            $builder->insert([
                                'missing' => $row[0],
                                'released_amount' => $row[1], 
                                'released_date' => $released_date,
                            ]);
                        }

if($row[3] != 0){
                            $builder->insert([
                                'missing' => $row[0],
                                'released_amount' => $row[3], 
                                'released_date' => $released_date1,
                            ]);
                        }

if($row[6] != 0){
                            $expenditureBuilder->insert([
                                'missing' => $row[0],
                                'expenditure_amount ' => $row[6], 
                                'expenditure_date' => '2024-12-31',
                            ]);
                        }
                    }
            } 
            else {
                $count = 1;
            }
        }

return $this->response->setJSON(['result' => 1, 'message' => 'Import successfully']);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'Failed to Import']);
    }
}

}?>