EVOLUTION-NINJA

Edit File: Feecollection.php

<?php

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

class Feecollection extends BaseController
{
    public function student_details_admission()
    {
        $id=session()->get('id');
        if($id){
            $aid=$this->request->getVar('admisson_no');
            $name=$this->request->getVar('name');
            $phone_no=$this->request->getVar('phone_no');
            if($aid || $name || $phone_no){
                // echo $sid;die();
            $db=\Config\Database::connect();
            $builder=$db->table('student_list');
            $builder->select('student_list.*,header.header_name as header,header_value.value_name as value');
            $builder->join('header','header.id=student_list.header_name');
            $builder->join('header_value','header_value.id=student_list.value_name');
            if($aid!=''){
                $builder->where('student_list.admission_no',$aid);
            }
            if($name!=''){
                $builder->where('student_list.first_name',$name);
            }
            if($phone_no!=''){
                $builder->where('student_list.mobile_no',$phone_no);
            }
            
            $builder->where('student_list.delete_status',1);
            $data=$builder->get()->getRowArray();
   
            // $query=$db->getLastQuery();
            // echo $query;die();
            // print_r($data);die();
            // $data['value']=$data;
            if($data){
                $balace=$data['total_fees']-$data['paid_fees'];
                if($balace==0){
                    $data['balance']='No Balance is pending';
                }else{
                    $data['balance']=$balace;
                }
                
                return $this->response->setJSON(['result'=>1,'data'=>$data]);
            }else{
                return $this->response->setJSON(['result'=>0,'message'=>'Student Not found']);
            }
            }else{
                return $this->response->setJSON(['result'=>0,'message'=>'Please Enter Any field']);
            }
            
        }else{
            return $this->response->setJSON(['result'=>0,'message'=>'please login']);
        }
    }
    public function fees_collection_individual()
    {
        $id=session()->get('id');
     
        if($id){
            $aid=$this->request->getVar('admisson_no');
            $name=$this->request->getVar('name');
            $phone_no=$this->request->getVar('phone_no');
            if($aid || $name || $phone_no){
            $db=\Config\Database::connect();
            $builder=$db->table('student_list');
            if($aid!=''){
                $builder->where('admission_no',$aid);
            }
            if($name!=''){
                $builder->where('first_name',$name);
            }
            if($phone_no!=''){
                $builder->where('mobile_no',$phone_no);
            }
            $sdata=$builder->get()->getRowArray();

// echo $id;die();
                if($sdata){  
                    $id=$sdata['admission_no'];  
                    $builder=$db->table('fees_collection');
                    $builder->where('admission_no',$id);
                    $data=$builder->get()->getResultArray();
                    // echo $data;die();
                    return $this->response->setJSON($data);
                }else{
                    return $this->response->setJSON(['result'=>0,'message'=>'Failed to load data']);
                }
            } else{
                return $this->response->setJSON(['result'=>0,'message'=>'Please Enter Any field']);
            }
        }else{
            // echo 'hello';die();
                return $this->response->setJSON(['result'=>0,'message'=>'please login']);
            } 
    }
    public function add_fees()
    {
        $created_by=session()->get('username');
        $sid=$this->request->getVar('sid');
        $admission_no=$this->request->getVar('admission_no');
        $modeofpament=$this->request->getVar('modeofpament');
        $payment_description=$this->request->getVar('payment_description');
        $amount=$this->request->getVar('amount_paid');
        $payment_id  = "PAY-".date("YmdHis");

$db = \Config\Database::connect();
        $update_fees = $db->table('student_list');
        $current_fees_data = $update_fees->select('paid_fees')->where('id', $sid)->where('admission_no', $admission_no)->get()->getRowArray();

if ($current_fees_data) {
        $current_fees = $current_fees_data['paid_fees'];
        $total_paid_fees = $current_fees + $amount;
        // echo $current_fees_data['paid_fees'];die();
        $datapaid = [
        'paid_fees' => $total_paid_fees
        ];
        $update_fees->where('id', $sid)->where('admission_no', $admission_no);
        $update = $update_fees->update($datapaid);
    
        } else {
            return $this->response->setJSON(['result'=>0,'message'=>'Failed to update paid fees']);
        }

$receipt = $db->table('fees_collection');
        $receipt->select('recipt_no');
        $receipt->like('recipt_no', 'RECEIPT');
        $receipt->orderBy('recipt_no', 'DESC');
        $receipt->limit(1);
        $recno_data = $receipt->get()->getResultArray();
        $last = end($recno_data);
        
        if ($last) {
            $last_receipt_no = $last['recipt_no'];
            $re_no1 = intval(substr($last_receipt_no, 7)) + 1;
            $recipt_no = 'RECEIPT' . str_pad($re_no1, 6, '0', STR_PAD_LEFT);
        } else {
            $recipt_no = 'RECEIPT000001';
        }

$data=[
            's_id'=>$sid,
            'admission_no'=>$admission_no,
            'payment_id'=>$payment_id,
            'amount'=>$amount,
            'collected_by'=>$created_by,
            'created_at' => date('Y-m-d H:i:s'),
            'description'=>$payment_description,
            'recipt_no'=>$recipt_no,
            'mode_of_pament'=>$modeofpament
        ];
        $insert_data=$db->table('fees_collection');
        $insert=$insert_data->insert($data);

if($insert){
            $email = \Config\Services::email();
            $value=$db->table('student_list')->where('id',$sid)->get()->getRowArray();
            // print_r($value);die();
            $name=$value['first_name'];
            $toEmail = $value['email']; 
            $subject = ' Payment Confirmation';
            
            $baseUrl = 'https://jayblues.in/gtracERP/public/assets/images/';
    
            $message = '
                <section style="max-width: 600px; margin: 40px auto; padding: 20px; background-color: #F9F7FE; border-radius: 20px; box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);">
                    <section style="max-width: 600px; margin: 40px auto; padding: 20px; background-color: #F9F7FE; border-radius: 20px; box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);">
                    <div style="display: flex; align-items: center; justify-content: space-between; margin-bottom: 20px;">
                      <img src="[baseUrl]mygtrac%20logo%201.png" alt="" style="max-width: 100%; height: auto;">
                      <h5 style="font-size: 18px; color: #191E43; font-weight: 600;">Payment Confirmation </h5>
                    </div>
                    <div style="background-color: #FFFFFF; padding: 20px; border-radius: 10px; box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);">
                      <h6 style="font-size: 14px; font-weight: 600; color: #191E43;">Dear '.$name.',</h6>
                      <h6 style="font-size: 10px; font-weight: 600; color: #191E43;">Admission No '.$admission_no.',</h6>
                      <p style="font-size: 12px; font-weight: 500; color: #333333;">We are pleased to inform you that we have successfully received your payment  at GTRAC. Thank you for completing the payment process promptly.</p>
                      <table style="width: 100%; border-collapse: collapse; margin-top: 20px;">
                        <tr>
                          <th style="background-color: #F4FFF0; padding: 10px; border: 1px solid #CCCCCC;">Payment Details</th>
                        </tr>
                        <tr>
                          <td style="padding: 10px; border: 1px solid #CCCCCC;">Total Amount Paid: '.$amount.'</td>
                        </tr>
                        <tr>
                          <td style="padding: 10px; border: 1px solid #CCCCCC;">Payment Date: '.date('Y-m-d').'</td>
                        </tr>
                        <tr>
                          <td style="padding: 10px; border: 1px solid #CCCCCC;">Payment Method: '.$modeofpament.'</td>
                        </tr>
                        <tr>
                          <td style="padding: 10px; border: 1px solid #CCCCCC;">Receipt No: '.$recipt_no.'</td>
                        </tr>
                      </table>
                      <p style="font-size: 12px; font-weight: 500; color: #333333; margin-top: 20px;">We are excited to have you join our Institute and look forward to supporting you throughout your learning journey. If you have any questions or need further assistance, please do not hesitate to contact us <a href="mailto:enquiry09.gta@gmail.com" style="color: #6DA800; text-decoration: none;">enquiry09.gta@gmail.com</a>.</p>
                      <p style="font-size: 12px; font-weight: 500; color: #333333;">Once again, thank you for choosing GTRAC. We wish you all the best in your studies and future endeavors.</p>
                    </div>
                    <div style="text-align: center; margin-top: 20px;">
                      <p style="font-size: 12px; font-weight: 600; color: #426600;">Best regards,</p>
                      <p style="font-size: 12px; font-weight: 600; color: #333333;">GTRAC team</p>
                      <p style="font-size: 12px; font-weight: 600; color: #333333;">GTRAC</p>
                      <p style="font-size: 12px; font-weight: 600; color: #333333;">9606011223</p>
                      <p style="font-size: 12px; font-weight: 600; color: #333333;"><a href="http://www.mygtrac.com" style="color: #426600; text-decoration: none; font-weight: 600;">www.mygtrac.com</a></p>
                    </div>
                    </section>
                </section>
                ';
          $email->setFrom('enquiry09.gta@gmail.com', 'GTRAC'); 
          $email->setTo($toEmail);
          $email->setSubject('Payment Confirmation for Full Stack Development Course');
          $email->setMessage($message);
          $email->setMailType('html');
          $email->send();

//to ajay sir 
            $toEmail = 'ajay@jayblues.com'; 
            $subject = 'Student Payment Notification';  
            
            $message = '
              <section style="max-width: 600px; margin: 40px auto; padding: 20px; background-color: #F9F7FE; border-radius: 20px; box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);">
                <h5 style="font-size: 18px; color: #191E43; font-weight: 600;">Student Payment Notification</h5>
                <p style="font-size: 12px; font-weight: 500; color: #333333;">Student Name: '. $name. '</p>
                <p style="font-size: 12px; font-weight: 500; color: #333333;">Admission No: '. $admission_no. '</p>
                <table style="width: 100%; border-collapse: collapse; margin-top: 20px;">
                  <tr>
                    <th style="background-color: #F4FFF0; padding: 10px; border: 1px solid #CCCCCC;">Payment Details</th>
                  </tr>
                  <tr>
                    <td style="padding: 10px; border: 1px solid #CCCCCC;">Total Amount Paid: '. $amount. '</td>
                  </tr>
                  <tr>
                    <td style="padding: 10px; border: 1px solid #CCCCCC;">Payment Date: '. date('Y-m-d'). '</td>
                  </tr>
                  <tr>
                    <td style="padding: 10px; border: 1px solid #CCCCCC;">Payment Method: '. $modeofpament. '</td>
                  </tr>
                    <tr>
                        <td style="padding: 10px; border: 1px solid #CCCCCC;">Receipt No: '.$recipt_no.'</td>
                    </tr>
                </table>
              </section>
            ';  
            $email->setFrom('enquiry09.gta@gmail.com', 'GTRAC');
            $email->setTo($toEmail);
            $email->setSubject($subject);
            $email->setMessage($message);
            $email->setMailType('html');
            
            if ($email->send()) {
                return $this->response->setJSON(['result'=>1,'message'=>'Fees Collected successfully']);
            }else{
                return $this->response->setJSON(['result'=>1,'message'=>'Fees Collected successfully but failed to send email']);
            } 
        }else{
            return $this->response->setJSON(['result'=>0,'message'=>'Failed to Collect']);
        }
    }
    public function fees_report_main()
    {
        $db = \Config\Database::connect();
    
        $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;
        }
        
         $todate .= ' 23:59:59';
        
        $data['from']=$fromdate;
        $data['to']=$todate;
       
        $header = $this->request->getvar('header');
        
        
        // $builder = $db->table('fees_collection');
        //  $builder->where('created_at >=', $fromdate);
        // $builder->where('created_at <=', $todate);
     
          $builder = $db->table('fees_collection');
        $builder->select('fees_collection.*');
        $builder->join('student_list', 'student_list.id  = fees_collection.s_id');
        $builder->where('fees_collection.created_at >=', $fromdate);
        $builder->where('fees_collection.created_at <=', $todate);
        
        if($header !=''){
        $builder->where('student_list.header_name', $header);
        }
        
        $collected = $builder->get()->getResultArray();
        $collected_amount = 0;
        foreach ($collected as $value) {
            $collected_amount += $value['amount'];
        }
    
        $data['collected_amount'] = $collected_amount;
        
        
        $total_amount = 0;
        $builder = $db->table('student_list');
        $builder->where('delete_status', 1);
        $builder->where('created_at >=', $fromdate );
        $builder->where('created_at <=', $todate);
        $total = $builder->get()->getResultArray();
        
//               $db = \Config\Database::connect();
// $query = $db->getLastQuery();
// echo $query;
// die();
 
        foreach ($total as $value) {
            $total_amount += $value['total_fees'];
        }
        
        $builder = $db->table('student_list');
        $builder->select('sum(total_fees) as sum, student_list.dropout');
        $builder->where('student_list.dropout','1');
        $table = $builder->get()->getResultArray();
        $drop_total_amount = 0;
        
        foreach ($table as $value) {
            $drop_total_amount += $value['sum'];
        }
        
        
        $builder = $db->table('student_list');
        $builder->select('sum(fees_collection.amount) as sum, student_list.dropout');
        $builder->join('fees_collection', 'fees_collection.s_id = student_list.id');
        $builder->groupBy('fees_collection.s_id');
        $builder->where('student_list.dropout','1');
        $table = $builder->get()->getResultArray();
        $drop_collected_amount = 0;
        
       
 
        foreach ($table as $value) {
            $drop_collected_amount += $value['sum'];
        }
        
        $data['drop_amount'] = $drop_total_amount - $drop_collected_amount;
        
        $data['collected_amount'] = $collected_amount;
        
        
        
        $data['total_amount'] = $total_amount;
        
        
        
        $data['balance'] = $total_amount - $collected_amount;

//overall
        $total_amount_overall = 0;
        $builder = $db->table('student_list');
        $builder->where('delete_status', 1);
        $total_overall = $builder->get()->getResultArray();
        foreach ($total_overall as $value) {
            $total_amount_overall += $value['total_fees'];
        }
        $data['total_amount_overall']=$total_amount_overall;

$builder = $db->table('fees_collection');
        $collected_overall = $builder->get()->getResultArray();
        $collected_amount_overall = 0;
        foreach ($collected_overall as $value) {
            $collected_amount_overall += $value['amount'];
        }
        $data['collected_amount_overall']=$collected_amount_overall;

$data['overallbalace']= ($total_amount_overall-$collected_amount_overall - ($drop_total_amount - $drop_collected_amount));
        //table
        // $builder = $db->table('student_list');
        // $builder->select('fees_collection.*, student_list.first_name AS student_first_name');
        // $builder->join('fees_collection', 'fees_collection.s_id = student_list.id');
        // $builder->where('fees_collection.created_at >=', $fromdate . ' 00:00:00');
        // $builder->where('fees_collection.created_at <=', $todate . ' 23:59:59');
        // $table = $builder->get()->getResultArray();
        
        
        
        $builder = $db->table('fees_collection');
        $builder->select('fees_collection.*, student_list.first_name AS student_first_name');
        $builder->join('student_list', 'student_list.id  = fees_collection.s_id');
      
        $builder->where('fees_collection.created_at >=', $fromdate);
        $builder->where('fees_collection.created_at <=', $todate);
        
        if($header !=''){
        $builder->where('student_list.header_name', $header);
        }
        
        $table = $builder->get()->getResultArray();
       
        
        // print_r($total_amount);
        // die();
        
        
//       $db = \Config\Database::connect();
// $query = $db->getLastQuery();
// echo $query;
// die();

if ($total_amount >= 0) {
            return $this->response->setJSON(['result' => 1, 'from'=>$fromdate,'to'=>$todate,'data' => $data,'table'=>$table ,]);
        } else {
            return $this->response->setJSON(['result' => 1, 'message' => 'No data found']);
        }
    }
    
    public function receipt()
    {
        $db = \Config\Database::connect();
        $id = $this->request->getVar('id');
        
        $builder = $db->table('fees_collection');
        $builder->select('fees_collection.*, student_list.first_name, student_list.last_name,student_list.value_name,student_list.total_fees,student_list.paid_fees,student_list.header_name');
        $builder->join('student_list', 'fees_collection.s_id = student_list.id');
        $builder->where('fees_collection.id',$id);

$data = $builder->get()->getRowArray();
        
        
        $vid=$data['value_name'];
        $hid=$data['header_name'];
        $data['balance']=$data['total_fees']-$data['paid_fees'];
        
        $data['course_fee']=$data['total_fees'];
        $course=$db->table('header_value')->where('id',$vid)->get()->getRowArray();
        $data['course_name']=$course['value_name'];

$header=$db->table('header')->where('id',$hid)->get()->getRowArray();
        $data['header']=$header['header_name'];
        // print_r($data); 
        return view('studentapplication/recipt', $data);
    }

public function import_fees_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('fees_collection');

foreach ($worksheet->getRowIterator(2) as $row) {
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false);

$rowData = [];
                foreach ($cellIterator as $cell) {
                    $rowData[] = $cell->getValue();
                }
                
                $sid = $rowData[0]? $rowData[0] : '';
                $admission_no=$rowData[1]? $rowData[1] : '';
                $amount=$rowData[2]==0||$rowData[2]>0 ? $rowData[2] : '';
                $description=$rowData[3]? $rowData[3] : '';
                $mode_of_pament=$rowData[4]? $rowData[4] : '';
                $collected_by=$rowData[5]? $rowData[5] : '';
                $created_at=$rowData[6]? $rowData[6] : '';
                $total_fees=$rowData[7]==0||$rowData[7]>0? $rowData[7] : '0';
                $recipt_no = $rowData[8]? $rowData[8] : '';
                 //print_r($created_at);
                 
                if($created_at!=''){
                   //$dateDate = \DateTime::createFromFormat('m-d-Y', $created_at);
                  //    echo 'Hello';
                // $created_at = $dateDate ? $dateDate->format('Y-m-d') : null;
                }
            //print_r($created_at);
            //    echo 'ello';
              //  die();
                $receipt = $db->table('fees_collection');
                $receipt->select('recipt_no');
                $receipt->like('recipt_no', 'RECIPT');
                $receipt->orderBy('recipt_no', 'DESC');
                $receipt->limit(1);
                $recno_data = $receipt->get()->getResultArray();
                $last = end($recno_data);
                
                // if ($last) {
                //     $last_receipt_no = $last['recipt_no'];
                //     $re_no1 = intval(substr($last_receipt_no, 6)) + 1;
                //     $recipt_no = 'RECIPT' . str_pad($re_no1, 6, '0', STR_PAD_LEFT);
                // } else {
                //     $recipt_no = 'RECIPT000001';
                // }
                $payment_id  = "PAY-".date("YmdHis");
                
                
                $update_fees = $db->table('student_list');
                $current_fees_data = $update_fees->select('id')->where('admission_no', $admission_no)->get()->getRowArray();
                
                $sid = $current_fees_data['id'];
                
                // echo 'Heo';
                // echo $sid;
                 //echo $created_at;
                 //die();
                    
                $insert= $builder->insert([
                    's_id'=>$sid,
                    'admission_no'=>$admission_no,
                    'payment_id'=>$payment_id,
                    'amount'=>$amount,
                    'collected_by'=>$collected_by,
                    'created_at' => $created_at,
                    'description'=>$description,
                    'recipt_no'=>$recipt_no,
                    'mode_of_pament'=>$mode_of_pament
                ]);

if($insert){
                    $update_fees = $db->table('student_list');
                    $current_fees_data = $update_fees->select('paid_fees')->where('id', $sid)->where('admission_no', $admission_no)->get()->getRowArray();
            
                    if ($current_fees_data) {
                    $current_fees = $current_fees_data['paid_fees'];
                    $total_paid_fees = $current_fees + $amount;
                    $datapaid = [
                    //'total_fees'=>  $total_fees,
                    'paid_fees' => $total_paid_fees
                    ];
                    $update_fees->where('id', $sid)->where('admission_no', $admission_no);
                    $update = $update_fees->update($datapaid);
                }
            }else{
                return $this->response->setJSON(['result' => 0, 'message' => 'failed to update fees in student table: ' ]);
            }
            
        }
        
        return $this->response->setJSON(['result' => 1, 'message' => 'Fees Collection is imported successfully']);
        } else {
            $errors = $excelFile->getErrorString();
            return $this->response->setJSON(['result' => 0, 'message' => 'File upload failed: ' . $errors]);
        }
    }
    
 
public function autocomplete_admission_no() {
    $term = $this->request->getVar('term');
    $db=\Config\Database::connect();
    $builder=$db->table('student_list');
    
    $builder->select('admission_no');
    $builder->like('admission_no', $term);
 
    $results = $builder->get()->getResult();

$data = [];
    foreach ($results as $result) {
        $data[] = $result->admission_no;
    }

return $this->response->setJSON($data);
}

public function autocomplete_name() {
    $term = $this->request->getVar('term');
    $db=\Config\Database::connect();
    $builder=$db->table('student_list');
    
    $builder->select('first_name');
    $builder->like('first_name', $term);
 
    $results = $builder->get()->getResult();

$data = [];
    foreach ($results as $result) {
        $data[] = $result->first_name;
    }

return $this->response->setJSON($data);
}

public function autocomplete_mobile_no() {
    $term = $this->request->getVar('term');
    $db=\Config\Database::connect();
    $builder=$db->table('student_list');
    
    $builder->select('mobile_no');
    $builder->like('mobile_no', $term);
 
    $results = $builder->get()->getResult();

$data = [];
    foreach ($results as $result) {
        $data[] = $result->mobile_no;
    }

return $this->response->setJSON($data);
}

}
?>