EVOLUTION-NINJA

Edit File: Expense.php

<?php

namespace App\Controllers;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Expense extends BaseController
{
    public function add_expense_value()
    {
        $id=session()->get('id');
        if($id){
            $db=\Config\Database::connect();
            $builder=$db->table('expense_type');
            // $builder->where('header_id',$id);
            $data=$builder->get()->getResultArray();
            if($data){
                return $this->response->setJSON(['result'=>1,'data'=>$data]);
            }else{
                return $this->response->setJSON(['result'=>0,'message'=>'No data present in this Header']);
            }
        }
    }

public function add_expense()
    {
        $id=session()->get('id');
        if($id){
            $data=$this->request->getVar();
            $data['created_by']=session()->get('username');
            $data['created_at']=date('Y-m-d H:i:s');
            $db=\Config\Database::connect();
            $builder=$db->table('expence_list');
            $insert=$builder->insert($data);
            if($insert){
                return $this->response->setJSON(['result'=>1,'message'=>'Expense Added successfully']);
            }else{
                return $this->response->setJSON(['result'=>0,'message'=>'Failed to submit']);
            }
        }
    }
    public function update_expense_data()
    {
        $id=session()->get('id');
        if($id){
            $id=$this->request->getVar('id');
            $data=$this->request->getVar();
            // print_r($data);die();
            $data['created_by']=session()->get('username');
            $data['created_at']=date('Y-m-d H:i:s');
            $db=\Config\Database::connect();
            $builder=$db->table('expence_list');
            $builder->where('id',$id);
            $update=$builder->update($data);
            if($update){
                return $this->response->setJSON(['result'=>1,'message'=>'Expense Updated successfully']);
            }else{
                return $this->response->setJSON(['result'=>0,'message'=>'Failed to update']);
            }
        }
    }
    public function expense_list_data()
    {
        $id=session()->get('id');
        if($id){
            $toDate = date('Y-m-d');
            $fromDate = date('Y-m-d', strtotime('-3 months', strtotime($toDate)));
        
            $fromdate = $this->request->getPost('fromdate');
            $todate = $this->request->getPost('todate');
            
            if (!$fromdate || !$todate) {
                $fromdate = $fromDate;
                $todate = $toDate;
            }
            $db=\Config\Database::connect();
            $builder=$db->table('expence_list');
            $builder->select('expence_list.*,expense_type.expense_name');
            $builder->join('expense_type','expense_type.id=expence_list.expense_type');
            // $builder->where('expence_list.date >=', $fromdate );
            // $builder->where('expence_list.date <=', $todate );
            $table=$builder->get()->getResultArray();
            // $query=$db->getLastQuery();
            // echo $query;die();  
            // print_r($table);die();

$builder = $db->table('expense_type');
            $headers_value = $builder->get()->getResultArray();

$expenseCount = [];
            $totalExpense = 0;

foreach ($headers_value as $header) {
                $vid = $header['id'];

$builder = $db->table('expence_list');
                $builder->select('expence_amount');
                $builder->where('expense_type', $vid);
                // $builder->where('date >=', $fromdate );
                // $builder->where('date <=', $todate );
                $query = $builder->get();

$results = $query->getResultArray();
                $sumAmount = 0;
                foreach ($results as $result) {
                    // if($result['expence_amount']==string){
                    //      echo $result['expence_amount'];die();
                    // }
                    $sumAmount += $result['expence_amount'];
                }
            
                if ($sumAmount > 0) {
                    $expenseCount[$header['expense_name']] = $sumAmount;
                    $totalExpense += $sumAmount;
                }
            }

$data['total_expense'] = $totalExpense;
            $data['expense'] = $expenseCount;

if ($table) {
                return $this->response->setJSON(['result' => 1,'data'=>$data, 'table' => $table]);
            } else {
                return $this->response->setJSON(['result' => 0, 'message' => 'No data Found']);
            }
        }else{
            return $this->response->setJSON(['result'=>0,'message'=>'please login']);
        }
    }
    public function expense_delete()
    {
        $id=$this->request->getVar('id');
        $db=\Config\Database::connect();
        $builder=$db->table('expence_list');
        $builder->where('id',$id);
        $delete=$builder->delete();
        if($delete){
            return $this->response->setJSON(['result'=>1,'message'=>'Deleted Successfully']);
        }else{
            return $this->response->setJSON(['result'=>0,'message'=>'Failed to delete']);
        }
    }
    public function expense_edit()
    {
        $id=session()->get('id');
        if($id){
            $eid=$this->request->getVar('id');
            // echo $eid;die();
            $db=\Config\Database::connect();
            $builder=$db->table('expence_list');
            $builder->where('id',$eid);
            $data=$builder->get()->getRowArray();

// print_r($data);die();
            // $data['value']=$data;
            return view('expense/expense_edit',$data);
        }else{
            return readdir()->to('');
        }
    }

public function import_expense_from_excel()
    {
        $excelFile = $this->request->getFile('excel');
    
        if ($excelFile->isValid() && !$excelFile->hasMoved()) {
            $tempFilePath = $excelFile->getTempName();
    
            $spreadsheet = IOFactory::load($tempFilePath);
    
            $worksheet = $spreadsheet->getActiveSheet();
    
            $db = \Config\Database::connect();
            $builder = $db->table('expence_list');
    
            foreach ($worksheet->getRowIterator(2) as $row) {
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false);
    
                $rowData = [];
                foreach ($cellIterator as $cell) {
                    $rowData[] = $cell->getValue();
                }
    
  
                $expense_type = isset($rowData[0]) ? $rowData[0] : '';
                $expence_amount = isset($rowData[1]) ? $rowData[1] : 0;
                $date = isset($rowData[2]) ? $rowData[2] : '';
                $payment_mode = isset($rowData[3]) ? $rowData[3] : '';
    
                if (is_numeric($date)) {
                    $dateDate = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($date);
                    $date = $dateDate ? $dateDate->format('Y-m-d') : '';
                } else {
                    if ($date != '') {
                        $dateDate = \DateTime::createFromFormat('d-m-Y', $date);
                        $date = $dateDate ? $dateDate->format('Y-m-d') : '';
                    }
                }
    
                $insert = $builder->insert([
                    'expense_type' => $expense_type,
                    'expence_amount' => $expence_amount,
                    'date' => $date,
                    'payment_mode' => $payment_mode,
                    'created_at' => $date
                ]);
            }
    
            return $this->response->setJSON(['result' => 1, 'message' => 'Expense is imported successfully']);
        } else {
            $errors = $excelFile->getErrorString();
            return $this->response->setJSON(['result' => 0, 'message' => 'File upload failed: ' . $errors]);
        }
    }
    
}
?>