Enhanced PHP File Manager

⬆️ Go Up (..)

📊 Server Information

Server:

LiteSpeed

System:

Linux 4.18.0-553.54.1.lve.el8.x86_64

PHP Version:

7.3.33

User:

u435171512

Directory:

/home/u435171512/domains/demo.sigmacomputers.in/public_html/arjun_erp/application/controllers

Free Space:

4.09 TB

🔧 Quick Actions

✏️ Editing: Expensereport.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Expensereport extends CI_Controller {
  public function __construct()
  {
    parent::__construct();
    $this->load->library('excel');
    $this->load->model(array('Expensereport_model'));
    $this->load->model(array('Quotation_model'));
    $this->load->model(array('Paymentreport_model'));
    $this->load->model(array('Invoice_model'));
    //if ($this->session->userdata['username'] == TRUE)
    if (isset($this->session->userdata['username']) && $this->session->userdata['username'] == TRUE)
        {
            //do something
        }else{
            redirect('login'); //if session is not there, redirect to login page
        } 
    date_default_timezone_set("Asia/Kolkata");
    $this->session->set_userdata('comtitle', 'Expense Report');
  }

public function general_expense_export()
  {
    $fdate = isset($_SESSION['expreportfdate'])?$_SESSION['expreportfdate']:'';
    $tdate = isset($_SESSION['expreporttdate'])?$_SESSION['expreporttdate']:'';
    $ven = isset($_SESSION['expreportven'])?$_SESSION['expreportven']:'';
    $cid = isset($_SESSION['expreportcid'])?$_SESSION['expreportcid']:'';
    $sid = isset($_SESSION['expreportsid'])?$_SESSION['expreportsid']:'';
    $lgid = isset($_SESSION['expreportlgid'])?$_SESSION['expreportlgid']:'';
    $etype = isset($_SESSION['expreporteetype'])?$_SESSION['expreporteetype']:'';
    $salledgroup = isset($_SESSION['expreportsalledgroup'])?$_SESSION['expreportsalledgroup']:'';

if($lgid=='' && $cid=='' && $etype =='')
    {
      $expense_report_list = $this->Expensereport_model->get_basic_expense_report_list($fdate,$tdate);

$cdexpqry = $this->db->query("(SELECT cdn.*,e.*,v.vendor_name,v.gst_no,v.state_id,lg.ledger_group FROM `credit_debit_note` cdn,`expense` e,vendor v,ledger_group lg,ledger_type lt WHERE e.expense_id = cdn.type_id AND e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND cdn.debit_type='Expense' AND cdn.credit_debit = 'DEBIT' AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND e.status!=2)");
      $cdn_expense_report_list = $cdexpqry->result_array();
    }
    else
    {
      if($lgid!='')
        $lgqry = " AND lg.ledger_group_id='".$lgid."'";
      else
        $lgqry = '';

if($cid!='')
      {
        $compqry = " AND e.location_id='".$cid."'";
        $vouchreqry = " AND v.location_id='".$cid."'";
      }
      else
      {
        $compqry = '';
        $vouchreqry = '';
      }

if($etype!='')
        {
          $ltqry = " AND lt.expense_type='".$etype."'";
        }
        else
        {
          $ltqry = '';
        }

$resqry = $this->db->query("(SELECT e.expense_date,e.invoice_no,e.taxable_value,e.other_charges,e.sgst,e.cgst,e.igst,e.total_amount,e.tds_value,v.vendor_name,v.gst_no,lg.ledger_group FROM `expense` e,vendor v,ledger_group lg,ledger_type lt WHERE e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.expense_date>='$fdate' AND e.expense_date<='$tdate' AND e.status!=2 $ltqry $lgqry $compqry) UNION (SELECT v.ledger_date,v.ledger_no as invoice_no,v.amount as taxable_value,0 as other_charges,0 as sgst,0 as cgst,0 as igst,v.amount as total_amount,0 as tds_value,v.payee_name as vendor_name ,'' as gst_no,lg.ledger_group FROM ledger v,ledger_sub_group lsg,ledger_group lg,ledger_type lt WHERE v.ledger_sub_group_id = lsg.ledger_sub_group_id AND lsg.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND v.ledger_date>='$fdate' AND v.ledger_date<='$tdate' $ltqry $lgqry $vouchreqry) ORDER BY expense_date ASC");

$expense_report_list = $resqry->result_array();

$cdexpqry = $this->db->query("(SELECT cdn.*,e.*,v.vendor_name,v.gst_no,v.state_id,lg.ledger_group FROM `credit_debit_note` cdn,`expense` e,vendor v,ledger_group lg,ledger_type lt WHERE e.expense_id = cdn.type_id AND e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND cdn.debit_type='Expense' AND cdn.credit_debit = 'DEBIT' AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND e.status!=2 $ltqry $lgqry $compqry)");
      $cdn_expense_report_list = $cdexpqry->result_array();
    }

$g_settings = general_setting_details();

if($cid!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($cid);
      $locationName = $locationlist->location_name;
      $reptitle = 'General Expense Report For '.$locationName.' From '.date_format(date_create($fdate),$g_settings->date_format).' To '.date_format(date_create($tdate),$g_settings->date_format);
    }
    else
    {
      $locationName = '';
      $reptitle = 'General Expense Report for All Company From '.date_format(date_create($fdate),$g_settings->date_format).' To '.date_format(date_create($tdate),$g_settings->date_format);
    }

$objPHPExcel = new PHPExcel();
    $activeSheet = $objPHPExcel->getActiveSheet();
    $styleArray = array(
        'font' => array(
            'bold' => true,
            'color' => array('rgb' => '000000')
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

$objPHPExcel->getActiveSheet()->mergeCells('A1:M1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $reptitle);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Date');
    $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Vendor/Payee');
    $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'GST Dtails');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Invoice No / Voucher No');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Nature of Expense');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Basic Value');
    $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'Other Charges');
    $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'SGST');
    $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('I2', 'CGST');
    $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('J2', 'IGST');
    $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('K2', 'Gross Value');
    $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('L2', 'TDS Deducted');
    $objPHPExcel->getActiveSheet()->getStyle('L2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('M2', 'Balance Payable');
    $objPHPExcel->getActiveSheet()->getStyle('M2')->applyFromArray($styleArray);

$i=3;$taxval=$oc=$sg=$cg=$ig=$tamt=$tdval=$balpay=0;
    foreach ($expense_report_list as $erlist ) {

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, date_format(date_create($erlist['expense_date']),$g_settings->date_format));
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $erlist['vendor_name']);
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $erlist['gst_no']);
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $erlist['invoice_no']);
      $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $erlist['ledger_group']);
      $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, round($erlist['taxable_value'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, round($erlist['other_charges'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, round($erlist['sgst'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, round($erlist['cgst'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, round($erlist['igst'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, round($erlist['total_amount'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, round($erlist['tds_value'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, round($erlist['total_amount']-$erlist['tds_value'],2));

$i++;
      $taxval+=$erlist['taxable_value'];$oc+=$erlist['other_charges'];$sg+=$erlist['sgst'];$cg+=$erlist['cgst'];$ig+=$erlist['igst'];$tamt+=$erlist['total_amount'];$tdval+=$erlist['tds_value'];$balpay+=($erlist['total_amount']-$erlist['tds_value']);
    }

if(count($cdn_expense_report_list)>0){
      foreach($cdn_expense_report_list as $cerlist){
        $taxableamt = ($cerlist['amount']*100)/(100+($cerlist['tax_rate']));
        $taxamt = ($taxableamt*$cerlist['tax_rate'])/100;
        $totval = number_format(($taxableamt+($taxamt*2)),2);

if($cerlist['state_id']==33)
        {
            $sgst = -1*($taxamt/2);
            $cgst = -1*($taxamt/2);
            $igst = 0;
        }
        else
        {
            $sgst = 0;
            $cgst = 0;
            $igst = -1*$taxamt;
        }

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, date_format(date_create($cerlist['credit_debit_note_date']),$g_settings->date_format));
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $cerlist['vendor_name']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $cerlist['gst_no']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $cerlist['invoice_no']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $cerlist['ledger_group']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, -1*round($taxableamt,2));
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, 0);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, round($sgst,2));
        $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, round($cgst,2));
        $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, round($igst,2));
        $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, -1*round($cerlist['amount'],2));
        $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, 0);
        $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, -1*round($cerlist['amount'],2));

$i++;

$taxval-=round($taxableamt,2);$sg+=round($sgst,2);$cg+=round($cgst,2);$ig+=round($igst,2);$tamt-=round($cerlist['amount'],2);$balpay-=round($cerlist['amount'],2);}
    }

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, 'Total');
    $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, round($taxval,2));
    $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, round($oc,2));
    $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, round($sg,2));
    $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, round($cg,2));
    $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, round($ig,2));
    $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, round($tamt,2));
    $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, round($tdval,2));
    $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, round($balpay,2));

$filename='General_Expense_Report.xlsx'; //save our workbook as this file name
    header('Content-Type: application/vnd.ms-excel'); //mime type
    header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
    header('Cache-Control: max-age=0'); //no cache

//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
    //if you want to save it as .XLSX Excel 2007 format
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); 
    $objPHPExcel->setActiveSheetIndex(0);

if (ob_get_contents()) ob_end_clean();
        //force user to download the Excel file without writing it to server's HD
        $objWriter->save('php://output');

}

public function gst_expense_export()
  {
    $fdate = isset($_SESSION['expreportfdate'])?$_SESSION['expreportfdate']:'';
    $tdate = isset($_SESSION['expreporttdate'])?$_SESSION['expreporttdate']:'';
    $ven = isset($_SESSION['expreportven'])?$_SESSION['expreportven']:'';
    $cid = isset($_SESSION['expreportcid'])?$_SESSION['expreportcid']:'';
    $sid = isset($_SESSION['expreportsid'])?$_SESSION['expreportsid']:'';
    $lgid = isset($_SESSION['expreportlgid'])?$_SESSION['expreportlgid']:'';
    $etype = isset($_SESSION['expreporteetype'])?$_SESSION['expreporteetype']:'';
    $salledgroup = isset($_SESSION['expreportsalledgroup'])?$_SESSION['expreportsalledgroup']:'';

if($ven=='' && $cid=='' && $etype =='')
    {
      $expense_report_list = $this->Expensereport_model->get_basic_expense_report_gst_list($fdate,$tdate);
    }
    else
    {
      if($ven!='')
        $venqry = " AND e.vendor_id='".$ven."'";
      else
        $venqry = '';

if($etype!='')
        {
          $ltqry = " AND lt.expense_type='".$etype."'";
        }
        else
        {
          $ltqry = '';
        }

$resqry = $this->db->query("SELECT e.*,v.vendor_name,v.gst_no,lg.ledger_group FROM `expense` e,vendor v,ledger_group lg,ledger_type lt WHERE e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND e.expense_date>='$fdate' AND e.expense_date<='$tdate' AND e.status!=2 $ltqry $venqry $compqry ORDER BY e.expense_date ASC");
      
      $expense_report_list = $resqry->result_array();
    }

$g_settings = general_setting_details();

if($cid!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($cid);
      $locationName = $locationlist->location_name;
      $reptitle = 'GST Expense Report For '.$locationName.' From '.date_format(date_create($fdate),$g_settings->date_format).' To '.date_format(date_create($tdate),$g_settings->date_format);
    }
    else
    {
      $locationName = '';
      $reptitle = 'GST Expense Report for All Company From '.date_format(date_create($fdate),$g_settings->date_format).' To '.date_format(date_create($tdate),$g_settings->date_format);
    }

$objPHPExcel->getActiveSheet()->mergeCells('A1:M1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $reptitle);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Date');
    $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Vendor');
    $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'GST Dtails');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Invoice No');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Nature of Expense');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Basic Value');
    $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'Other Charges');
    $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'SGST');
    $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('I2', 'CGST');
    $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('J2', 'IGST');
    $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('K2', 'Gross Value');
    $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($styleArray);

$i=3;$taxval=$oc=$sg=$cg=$ig=$tamt=0;
    foreach ($expense_report_list as $erlist ) {

$i++;
      $taxval+=$erlist['taxable_value'];$oc+=$erlist['other_charges'];$sg+=$erlist['sgst'];$cg+=$erlist['cgst'];$ig+=$erlist['igst'];$tamt+=$erlist['total_amount'];
    }

$filename='GST_Expense_Report.xlsx'; //save our workbook as this file name
    header('Content-Type: application/vnd.ms-excel'); //mime type
    header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
    header('Cache-Control: max-age=0'); //no cache

if (ob_get_contents()) ob_end_clean();
        //force user to download the Excel file without writing it to server's HD
        $objWriter->save('php://output');

}

public function salary_expense_export()
  {
    $fdate = isset($_SESSION['expreportfdate'])?$_SESSION['expreportfdate']:'';
    $tdate = isset($_SESSION['expreporttdate'])?$_SESSION['expreporttdate']:'';
    $ven = isset($_SESSION['expreportven'])?$_SESSION['expreportven']:'';
    $cid = isset($_SESSION['expreportcid'])?$_SESSION['expreportcid']:'';
    $sid = isset($_SESSION['expreportsid'])?$_SESSION['expreportsid']:'';
    $lgid = isset($_SESSION['expreportlgid'])?$_SESSION['expreportlgid']:'';
    $etype = isset($_SESSION['expreporteetype'])?$_SESSION['expreporteetype']:'';
    $salledgroup = isset($_SESSION['expreportsalledgroup'])?$_SESSION['expreportsalledgroup']:'';

if($sid=='' && $cid=='' && $etype =='')
    {
      $expense_report_list = $this->Expensereport_model->get_basic_expense_report_salary_list($fdate,$tdate);
    }
    else
    {
      if($sid!='')
        $stfqry = " AND se.staff_id='".$sid."'";
      else
        $stfqry = '';

if($salledgroup!='')
        $sal = " AND lg.ledger_group_id='".$salledgroup."'";
      else
        $sal = '';

if($cid!='')
      {
        $compqry = " AND se.location_id='".$cid."'";
      }
      else
      {
        $compqry = '';
      }

if($etype!='')
      {
        $ltqry = " AND lt.expense_type='".$etype."'";
      }
      else
      {
        $ltqry = '';
      }

$resqry = $this->db->query("SELECT se.*,s.staff_name,lg.ledger_group FROM staff_expense se, staff s, ledger_group lg,ledger_type lt WHERE se.staff_id = s.staff_id AND lg.ledger_group_id = se.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND se.staff_expense_date>='$fdate' AND se.staff_expense_date<='$tdate' $ltqry $stfqry $compqry $sal AND se.status=0 ORDER BY se.staff_expense_date ASC");

$expense_report_list = $resqry->result_array();
    }

$g_settings = general_setting_details();

if($cid!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($cid);
      $locationName = $locationlist->location_name;
      $reptitle = 'Salary Expense Report For '.$locationName.' From '.date_format(date_create($fdate),$g_settings->date_format).' To '.date_format(date_create($tdate),$g_settings->date_format);
    }
    else
    {
      $locationName = '';
      $reptitle = 'Salary Expense Report for All Company From '.date_format(date_create($fdate),$g_settings->date_format).' To '.date_format(date_create($tdate),$g_settings->date_format);
    }

$objPHPExcel->getActiveSheet()->mergeCells('A1:M1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $reptitle);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Date');
    $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Staff');
    $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Voucher No');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Nature of Expense');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Calculated Days');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Gross Salary');
    $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'ESI');
    $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'EPF');
    $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('I2', 'Allowance');
    $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('J2', 'Deduction');
    $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('K2', 'Net Salary');
    $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($styleArray);

$i=3;$gs=0;$esi=0;$epf=0;$allo=0;$dedu=0;$nsal=0;
    foreach ($expense_report_list as $erlist ) {

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, date_format(date_create($erlist['staff_expense_date']),$g_settings->date_format));
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $erlist['staff_name']);
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $erlist['voucher_no']);
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $erlist['ledger_group']);
      $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $erlist['days_to_be_calculated']);
      $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, round($erlist['gross_salary'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, round($erlist['esi_amount'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, round($erlist['epf_amount'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, round($erlist['allowance'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, round($erlist['deduction'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, round($erlist['net_salary'],2));

$i++;
      $gs+=$erlist['gross_salary'];$esi+=$erlist['esi_amount'];$epf+=$erlist['epf_amount'];$allo+=$erlist['allowance'];$dedu+=$erlist['deduction'];$nsal+=$erlist['net_salary'];
    }

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, 'Total');
    $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, round($gs,2));
    $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, round($esi,2));
    $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, round($epf,2));
    $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, round($allo,2));
    $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, round($dedu,2));
    $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, round($nsal,2));

$filename='Salary_Expense_Report.xlsx'; //save our workbook as this file name
    header('Content-Type: application/vnd.ms-excel'); //mime type
    header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
    header('Cache-Control: max-age=0'); //no cache

if (ob_get_contents()) ob_end_clean();
        //force user to download the Excel file without writing it to server's HD
        $objWriter->save('php://output');

}

public function index()
  {
    $data['vendor_list'] = $this->Expensereport_model->get_vendor_list();
    $data['staff_list'] = $this->Expensereport_model->get_staff_list();
    $data['voucher_group_list'] = $this->Expensereport_model->get_ledgergroup_list();

if($_SESSION['company_id']==0)
      $data['location_list'] = $this->Expensereport_model->get_location();
    else
      $data['location_list'] = $this->Expensereport_model->get_location_by_comp_id($_SESSION['company_id']);

$this->load->view('expensereport/expense_report',$data);
  }

public function expense_report()
  {
    $fdate = $_POST['fdate'];
    $fd = explode('/', $fdate);
    $fdate = $fd[2].'-'.$fd[0].'-'.$fd[1];

$tdate = $_POST['tdate'];
    $td = explode('/', $tdate);
    $tdate = $td[2].'-'.$td[0].'-'.$td[1];

$rtype = $_POST['rtype'];
    $ven = $_POST['ven'];
    $cid = $_POST['cid'];
    $sid = $_POST['sid'];
    $lgid = $_POST['lgid'];
    $etype = $_POST['etype'];
    $salledgroup = $_POST['salledgroup'];

$this->session->set_userdata('expreportfdate', $_POST['fdate']!=''?$fdate:'');
    $this->session->set_userdata('expreporttdate', $_POST['tdate']!=''?$tdate:'');
    $this->session->set_userdata('expreportven', $_POST['ven']!=''?$_POST['ven']:'');
    $this->session->set_userdata('expreportcid', $_POST['cid']!=''?$_POST['cid']:'');
    $this->session->set_userdata('expreportsid', $_POST['sid']!=''?$_POST['sid']:'');
    $this->session->set_userdata('expreportlgid', $_POST['lgid']!=''?$_POST['lgid']:'');
    $this->session->set_userdata('expreporteetype', $_POST['etype']!=''?$_POST['etype']:'');
    $this->session->set_userdata('expreportsalledgroup', $_POST['salledgroup']!=''?$_POST['salledgroup']:'');

if($rtype == 'expense_report_general')
    {
      if($lgid=='' && $cid=='' && $etype =='')
      {
        $data['expense_report_list'] = $this->Expensereport_model->get_basic_expense_report_list($fdate,$tdate);

$cdexpqry = $this->db->query("(SELECT cdn.*,e.*,v.vendor_name,v.gst_no,v.state_id,lg.ledger_group FROM `credit_debit_note` cdn,`expense` e,vendor v,ledger_group lg,ledger_type lt WHERE e.expense_id = cdn.type_id AND e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND cdn.debit_type='Expense' AND cdn.credit_debit = 'DEBIT' AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND e.status!=2)");
        $data['cdn_expense_report_list'] = $cdexpqry->result_array();
      }
      else
      {
        if($lgid!='')
          $lgqry = " AND lg.ledger_group_id='".$lgid."'";
        else
          $lgqry = '';

if($cid!='')
        {
          $compqry = " AND e.location_id='".$cid."'";
          $vouchreqry = " AND v.location_id='".$cid."'";
        }
        else
        {
          $compqry = '';
          $vouchreqry = '';
        }

if($etype!='')
        {
          $ltqry = " AND lt.expense_type='".$etype."'";
        }
        else
        {
          $ltqry = '';
        }

//$resqry = $this->db->query("SELECT e.*,v.vendor_name,v.gst_no,lg.ledger_group FROM `expense` e,vendor v,ledger_group lg WHERE e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND e.expense_date>='$fdate' AND e.expense_date<='$tdate' AND e.status!=2 $venqry $compqry ORDER BY e.expense_date ASC");

$data['expense_report_list'] = $resqry->result_array();

$cdexpqry = $this->db->query("(SELECT cdn.*,e.*,v.vendor_name,v.gst_no,v.state_id,lg.ledger_group FROM `credit_debit_note` cdn,`expense` e,vendor v,ledger_group lg,ledger_type lt WHERE e.expense_id = cdn.type_id AND e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND lg.ledger_type_id = lt.ledger_type_id AND cdn.debit_type='Expense' AND cdn.credit_debit = 'DEBIT' AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND e.status!=2 $ltqry $lgqry $compqry)");
        $data['cdn_expense_report_list'] = $cdexpqry->result_array();
      }
      $this->load->view('expensereport/expense_report_general_table',$data);
    }

if($rtype == 'expense_report_gst')
    {
      if($ven=='' && $cid=='' && $etype =='')
      {
        $data['expense_report_list'] = $this->Expensereport_model->get_basic_expense_report_gst_list($fdate,$tdate);
      }
      else
      {
        if($ven!='')
          $venqry = " AND e.vendor_id='".$ven."'";
        else
          $venqry = '';

if($cid!='')
        {
          $compqry = " AND e.location_id='".$cid."'";
          $vouchreqry = " AND v.location_id='".$cid."'";
        }
        else
        {
          $compqry = '';
          $vouchreqry = '';
        }

if($etype!='')
        {
          $ltqry = " AND lt.expense_type='".$etype."'";
        }
        else
        {
          $ltqry = '';
        }

//$resqry = $this->db->query("(SELECT e.expense_date,e.invoice_no,e.taxable_value,e.other_charges,e.sgst,e.cgst,e.igst,e.total_amount,e.tds_value,v.vendor_name,v.gst_no,lg.ledger_group FROM `expense` e,vendor v,ledger_group lg WHERE e.vendor_id = v.vendor_id AND e.ledger_group_id = lg.ledger_group_id AND e.expense_date>='$fdate' AND e.expense_date<='$tdate' AND e.status!=2 $venqry $compqry) UNION (SELECT v.ledger_date,v.ledger_no as invoice_no,v.amount as taxable_value,0 as other_charges,0 as sgst,0 as cgst,0 as igst,v.amount as total_amount,0 as tds_value,v.payee_name as vendor_name ,'' as gst_no,lg.ledger_group FROM ledger v,ledger_sub_group lsg,ledger_group lg WHERE v.ledger_sub_group_id = lsg.ledger_sub_group_id AND lsg.ledger_group_id = lg.ledger_group_id AND v.ledger_date>='$fdate' AND v.ledger_date<='$tdate' $vouchreqry) ORDER BY expense_date ASC");
        
        $data['expense_report_list'] = $resqry->result_array();
      }
      $this->load->view('expensereport/expense_report_gst_table',$data);
    }

if($rtype == 'expense_report_salary')
    {
      if($sid=='' && $cid=='' && $etype =='')
      {
        $data['expense_report_list'] = $this->Expensereport_model->get_basic_expense_report_salary_list($fdate,$tdate);
      }
      else
      {
        if($sid!='')
          $stfqry = " AND se.staff_id='".$sid."'";
        else
          $stfqry = '';

if($salledgroup!='')
          $sal = " AND lg.ledger_group_id='".$salledgroup."'";
        else
          $sal = '';

if($cid!='')
        {
          $compqry = " AND se.location_id='".$cid."'";
        }
        else
        {
          $compqry = '';
        }

if($etype!='')
        {
          $ltqry = " AND lt.expense_type='".$etype."'";
        }
        else
        {
          $ltqry = '';
        }

$data['expense_report_list'] = $resqry->result_array();
      }
      $this->load->view('expensereport/expense_report_salary_table',$data);
    }

}

}
?>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: Expensereport.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation