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: Purchasereport.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Purchasereport extends CI_Controller {
  public function __construct()
  {
    parent::__construct();
    $this->load->library('excel');
    $this->load->model(array('Purchasereport_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', 'Purchase Report');
  }

public function purchase_register_export()
  {
    $fdate = isset($_SESSION['purreportfdate'])?$_SESSION['purreportfdate']:'';
    $tdate = isset($_SESSION['purreporttdate'])?$_SESSION['purreporttdate']:'';
    $cat = isset($_SESSION['purreportcat'])?$_SESSION['purreportcat']:'';
    $prod = isset($_SESSION['purreportprod'])?$_SESSION['purreportprod']:'';
    $gsm = isset($_SESSION['purreportgsm'])?$_SESSION['purreportgsm']:'';
    $color = isset($_SESSION['purreportcolor'])?$_SESSION['purreportcolor']:'';
    $customer = isset($_SESSION['purreportcustomer'])?$_SESSION['purreportcustomer']:'';
    $location = isset($_SESSION['purreportlocation'])?$_SESSION['purreportlocation']:'';

if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $location=='')
    {
      $purchase_list = $this->Purchasereport_model->get_basic_purchase_list($fdate,$tdate);
      $purchase_product_list = $this->Purchasereport_model->get_basic_purhcase_product_list($fdate,$tdate);
    }
    else
    {
      $loctab='';$locjoin='';$locqry='';$ploc = '';
      if($cat!='')
        $catqry = ' AND rm.rawmaterial_category_id='.$cat;
      else
        $catqry = '';

if($prod!='')
        $prodqry = ' AND rm.rawmaterial_id='.$prod;
      else
        $prodqry = '';

if($gsm!='')
        $gsmqry = ' AND rm.rawmaterial_gsm_id='.$gsm;
      else
        $gsmqry = '';

if($color!='')
        $colorqry = " AND rm.rawmaterial_color_id='".$color."'";
      else
        $colorqry = '';

if($customer!='')
        $custqry = " AND v.vendor_id='".$customer."'";
      else
        $custqry = '';

if($location!='')
      {
        $loctab = ',purchase_order po';
        $locjoin = ' AND p.purchase_order_id = po.purchase_order_id';
        $locqry = " AND po.location_id='".$location."'";
        $ploc = " AND p.location_id='".$location."'";
      }
      else
      {
        $loctab = '';
        $locjoin = '';
        $locqry = '';
        $ploc = '';
      }

$purresqry = $this->db->query("SELECT p.*,v.* FROM purchase p, vendor v WHERE p.vendor_id = v.vendor_id AND p.invoice_date>='$fdate' AND p.invoice_date<='$tdate' AND p.status!=5 $custqry ORDER BY p.invoice_date ASC");
      $purchase_list = $purresqry->result_array();

$resqry = $this->db->query("(SELECT pp.purchase_id, rm.rawmaterial,rmu.rawmaterial_uom,pp.quantity,pp.net_weight,pp.rate FROM `purchase_product` pp,purchase p, rawmaterial rm,rawmaterial_uom rmu,rawmaterial_category rmc,rawmaterial_gsm rmg,rawmaterial_color rmclr,vendor v $loctab WHERE p.purchase_id = pp.purchase_id AND pp.raw_material_id = rm.rawmaterial_id AND rm.rawmaterial_uom_id = rmu.rawmaterial_uom_id AND rm.rawmaterial_category_id = rmc.rawmaterial_category_id AND rm.rawmaterial_gsm_id = rmg.rawmaterial_gsm_id AND rm.rawmaterial_color_id = rmclr.rawmaterial_color_id AND v.vendor_id = p.vendor_id $locjoin AND p.invoice_date>='$fdate' AND p.invoice_date<='$tdate' AND p.status!=5 $catqry $prodqry $gsmqry $colorqry $custqry $locqry GROUP BY pp.purchase_product_id)

UNION

(SELECT pp.purchase_id, pro.product_name as rawmaterial,u.uom as rawmaterial_uom,pp.quantity,pp.net_weight,pp.rate FROM `purchase_product` pp,purchase p, product pro,uom u,vendor v $loctab WHERE p.purchase_id = pp.purchase_id AND pp.product_id = pro.product_id AND pro.uom_id = u.uom_id AND v.vendor_id = p.vendor_id $locjoin AND p.invoice_date>='$fdate' AND p.status!=5 AND p.invoice_date<='$tdate' $locqry GROUP BY pp.purchase_product_id) 
          
          UNION
          
          (SELECT pp.purchase_id, rm.rawmaterial,rmu.rawmaterial_uom,pp.quantity,pp.net_weight,pp.rate FROM `purchase_product` pp,purchase p, rawmaterial rm,rawmaterial_uom rmu,rawmaterial_category rmc,rawmaterial_gsm rmg,rawmaterial_color rmclr,vendor v WHERE p.purchase_id = pp.purchase_id AND pp.raw_material_id = rm.rawmaterial_id AND rm.rawmaterial_uom_id = rmu.rawmaterial_uom_id AND rm.rawmaterial_category_id = rmc.rawmaterial_category_id AND rm.rawmaterial_gsm_id = rmg.rawmaterial_gsm_id AND rm.rawmaterial_color_id = rmclr.rawmaterial_color_id AND v.vendor_id = p.vendor_id AND p.invoice_date>='$fdate' AND p.invoice_date<='$tdate' AND p.status!=5 $catqry $prodqry $gsmqry $colorqry $custqry $ploc GROUP BY pp.purchase_product_id)

UNION

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

$g_settings = general_setting_details();

if($location!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($location);
      $locationName = $locationlist->location_name;
      $reptitle = 'Purchase Register 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 = 'Purchase Register 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:S1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $reptitle);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Invoice No');
    $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Invoice Date');
    $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Material Recd on');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Supplier Type');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Supplier');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'GST No');
    $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'Product');
    $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'UOM');
    $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('I2', 'Qty');
    $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('J2', 'Weight');
    $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('K2', 'Unit Price');
    $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('L2', 'Disc %');
    $objPHPExcel->getActiveSheet()->getStyle('L2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('M2', 'Taxable Value');
    $objPHPExcel->getActiveSheet()->getStyle('M2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('N2', 'Other Charges');
    $objPHPExcel->getActiveSheet()->getStyle('N2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('O2', 'Tax Rate');
    $objPHPExcel->getActiveSheet()->getStyle('O2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('P2', 'CGST');
    $objPHPExcel->getActiveSheet()->getStyle('P2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('Q2', 'SGST');
    $objPHPExcel->getActiveSheet()->getStyle('Q2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('R2', 'IGST');
    $objPHPExcel->getActiveSheet()->getStyle('R2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('S2', 'Total Amount');
    $objPHPExcel->getActiveSheet()->getStyle('S2')->applyFromArray($styleArray);

$i=3;$tblamt=$ochar=$cst=$sst=$ist=$gtot=0;
    foreach($purchase_list as $pplist){

if($pplist['state_id']==33)
        {
            $cgst = round($pplist['tax_amount']/2,2);
            $sgst = round($pplist['tax_amount']/2,2);
            $igst = '';

$cgstval = round($pplist['tax_amount']/2,2);
            $sgstval = round($pplist['tax_amount']/2,2);
            $igstval = 0;
        }
        else
        {
            $cgst = '';
            $sgst = '';
            $igst = round($pplist['tax_amount'],2);

$cgstval = 0;
            $sgstval = 0;
            $igstval = round($pplist['tax_amount'],2);
        }
        /*$tblamt+=$pplist['taxable_amount'];
        $ochar+=$pplist['other_charges'];
        $cst+=$cgstval;
        $sst+=$sgstval;
        $ist+=$igstval;
        $gtot+=$pplist['total_amount'];*/

$cdlist = $this->db->query("SELECT cdn.*,p.*,v.* FROM credit_debit_note cdn,purchase p,vendor v WHERE cdn.type_id = p.purchase_id AND p.vendor_id = v.vendor_id AND cdn.credit_debit = 'DEBIT' AND cdn.debit_type='Purchase' AND cdn.credit_debit_note_date>='".$fdate."' AND cdn.credit_debit_note_date<='".$tdate."' AND cdn.type_id='".$pplist['purchase_id']."'")->result_array();

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

if($cdl['state_id']==33)
                {
                    $cdcgst = -1*round($taxamt/2,2);
                    $cdsgst = -1*round($taxamt/2,2);
                    $cdigst = '';

$cdcgstval = round($taxamt/2,2);
                    $cdsgstval = round($taxamt/2,2);
                    $cdigstval = 0;
                }
                else
                {
                    $cdcgst = '';
                    $cdsgst = '';
                    $cdigst = -1*round(($taxamt),2);

$cdcgstval = 0;
                    $cdsgstval = 0;
                    $cdigstval = round(($taxamt),2);
                }

$tblamt-=$taxableamt;
                $cst-=$cdcgstval;
                $sst-=$cdsgstval;
                $ist-=$cdigstval;
                $gtot-=$cdl['amount'];

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $cdl['purchase_no'].' - '.$cdl['invoice_no']);
                $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, date_format(date_create($cdl['credit_debit_note_date']),$g_settings->date_format));
                $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, '');
                $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, 'Registered');
                $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $cdl['vendor_name']);
                $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $cdl['gst_no']);
                $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, '');
                $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, '');
                $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, '');
                $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, '');
                $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, '');
                $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, '');
                $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, -1*round($taxableamt,2));
                $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, '');
                $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, $cdl['tax_percent']);
                $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, $cdcgst);
                $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, $cdsgst);
                $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, $cdigst);
                $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, -1*$cdl['amount']);

$i++;}
        }

$j=0;foreach($purchase_product_list as $srlist){
        if($srlist['purchase_id'] == $pplist['purchase_id']){
          if($j==0){
            $tblamt+=$pplist['taxable_amount'];
            $ochar+=$pplist['other_charges'];
            $cst+=$cgstval;
            $sst+=$sgstval;
            $ist+=$igstval;
            $gtot+=$pplist['total_amount'];
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $pplist['purchase_no'].' - '.$pplist['invoice_no']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, date_format(date_create($pplist['invoice_date']),$g_settings->date_format));
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, 'Registered');
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $pplist['vendor_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $pplist['gst_no']);
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $srlist['rawmaterial']);
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $srlist['rawmaterial_uom']);
            $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $srlist['quantity']);
            $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $srlist['net_weight']);
            $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $srlist['rate']);
            $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, $pplist['taxable_amount']);
            $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, $pplist['other_charges']);
            $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, $pplist['tax_percent']);
            $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, $cgst);
            $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, $sgst);
            $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, $igst);
            $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, $pplist['total_amount']);
          }
          else
          {
            $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, '');
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $srlist['rawmaterial']);
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $srlist['rawmaterial_uom']);
            $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $srlist['quantity']);
            $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $srlist['net_weight']);
            $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $srlist['rate']);
            $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, '');
          }
          $j++;$i++;
        }
      }
    }

$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, '');
    $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, 'Total');
    $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, round($tblamt,2));
    $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, round($ochar,2));
    $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, round($cst,2));
    $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, round($sst,2));
    $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, round($ist,2));
    $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, round($gtot,2));

$filename='Purchase_Register.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 pending_purchase_export()
  {
    $fdate = isset($_SESSION['purreportfdate'])?$_SESSION['purreportfdate']:'';
    $tdate = isset($_SESSION['purreporttdate'])?$_SESSION['purreporttdate']:'';
    $cat = isset($_SESSION['purreportcat'])?$_SESSION['purreportcat']:'';
    $prod = isset($_SESSION['purreportprod'])?$_SESSION['purreportprod']:'';
    $gsm = isset($_SESSION['purreportgsm'])?$_SESSION['purreportgsm']:'';
    $color = isset($_SESSION['purreportcolor'])?$_SESSION['purreportcolor']:'';
    $customer = isset($_SESSION['purreportcustomer'])?$_SESSION['purreportcustomer']:'';
    $location = isset($_SESSION['purreportlocation'])?$_SESSION['purreportlocation']:'';

if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $location=='')
    {
      $pending_purchase_list = $this->Purchasereport_model->get_basic_pending_purchase_list($fdate,$tdate);
      $pending_purchase_product_list = $this->Purchasereport_model->get_basic_pending_purhcase_product_list($fdate,$tdate);
    }
    else
    {
      $loctab='';$locjoin='';$locqry='';$ploc = '';
      if($cat!='')
        $catqry = ' AND rm.rawmaterial_category_id='.$cat;
      else
        $catqry = '';

if($prod!='')
        $prodqry = ' AND rm.rawmaterial_id='.$prod;
      else
        $prodqry = '';

if($gsm!='')
        $gsmqry = ' AND rm.rawmaterial_gsm_id='.$gsm;
      else
        $gsmqry = '';

if($color!='')
        $colorqry = " AND rm.rawmaterial_color_id='".$color."'";
      else
        $colorqry = '';

if($customer!='')
        $custqry = " AND v.vendor_id='".$customer."'";
      else
        $custqry = '';

$pending_purchase_list = $this->Purchasereport_model->get_basic_pending_purchase_list($fdate,$tdate);

$resqry = $this->db->query("(SELECT pp.purchase_id, rm.rawmaterial,rmu.rawmaterial_uom,pp.quantity,pp.net_weight,pp.rate FROM `purchase_product` pp,purchase p, rawmaterial rm,rawmaterial_uom rmu,rawmaterial_category rmc,rawmaterial_gsm rmg,rawmaterial_color rmclr,vendor v $loctab WHERE p.purchase_id = pp.purchase_id AND pp.raw_material_id = rm.rawmaterial_id AND rm.rawmaterial_uom_id = rmu.rawmaterial_uom_id AND rm.rawmaterial_category_id = rmc.rawmaterial_category_id AND rm.rawmaterial_gsm_id = rmg.rawmaterial_gsm_id AND rm.rawmaterial_color_id = rmclr.rawmaterial_color_id AND v.vendor_id = p.vendor_id $locjoin AND p.invoice_date>='$fdate' AND p.invoice_date<='$tdate' AND p.status=0 $catqry $prodqry $gsmqry $colorqry $custqry $locqry GROUP BY pp.purchase_product_id)

UNION

(SELECT pp.purchase_id, rm.rawmaterial,rmu.rawmaterial_uom,pp.quantity,pp.net_weight,pp.rate FROM `purchase_product` pp,purchase p, rawmaterial rm,rawmaterial_uom rmu,rawmaterial_category rmc,rawmaterial_gsm rmg,rawmaterial_color rmclr,vendor v WHERE p.purchase_id = pp.purchase_id AND pp.raw_material_id = rm.rawmaterial_id AND rm.rawmaterial_uom_id = rmu.rawmaterial_uom_id AND rm.rawmaterial_category_id = rmc.rawmaterial_category_id AND rm.rawmaterial_gsm_id = rmg.rawmaterial_gsm_id AND rm.rawmaterial_color_id = rmclr.rawmaterial_color_id AND v.vendor_id = p.vendor_id AND p.invoice_date>='$fdate' AND p.invoice_date<='$tdate' AND p.status=0 $catqry $prodqry $gsmqry $colorqry $custqry $ploc GROUP BY pp.purchase_product_id)

UNION

ORDER BY purchase_id");

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

$g_settings = general_setting_details();

if($location!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($location);
      $locationName = $locationlist->location_name;
      $reptitle = 'Pending Purchase 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 = 'Pending Purchase Report for All Company From '.date_format(date_create($fdate),$g_settings->date_format).' To '.date_format(date_create($tdate),$g_settings->date_format);
    }

$i=3;
    foreach($pending_purchase_list as $pplist){

if($pplist['state_id']==33)
    {
        $cgst = round($pplist['tax_amount']/2,2);
        $sgst = round($pplist['tax_amount']/2,2);
        $igst = '';
    }
    else
    {
        $cgst = '';
        $sgst = '';
        $igst = round($pplist['tax_amount'],2);;
    }
      $j=0;foreach($pending_purchase_product_list as $srlist){
        if($srlist['purchase_id'] == $pplist['purchase_id']){
          if($j==0){
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $pplist['purchase_no'].' - '.$pplist['invoice_no']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, date_format(date_create($pplist['invoice_date']),$g_settings->date_format));
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, 'Registered');
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $pplist['vendor_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $pplist['gst_no']);
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $srlist['rawmaterial']);
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $srlist['rawmaterial_uom']);
            $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $srlist['quantity']);
            $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $srlist['net_weight']);
            $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $srlist['rate']);
            $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, $pplist['taxable_amount']);
            $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, $pplist['other_charges']);
            $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, $pplist['tax_percent']);
            $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, $cgst);
            $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, $sgst);
            $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, $igst);
            $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, $pplist['total_amount']);
          }
          else
          {
            $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, '');
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $srlist['rawmaterial']);
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $srlist['rawmaterial_uom']);
            $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $srlist['quantity']);
            $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $srlist['net_weight']);
            $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $srlist['rate']);
            $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, '');
          }
          $j++;$i++;
        }
      }
    }

$filename='Pending_Purchase.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 getRawmaterial()
  {
    $cat_id = $_POST['cat_id'];
    $product = $this->Purchasereport_model->get_rawmaterial_by_category_id($cat_id);
    if(count($product)>0)
    {
      $st = '<option value="">Select Raw Material</option>';
      foreach ($product as $prod) {
          $st.='<option value='.$prod["rawmaterial_id"].'>'.$prod["rawmaterial"].'</option>';
      }
    }
    else
    {
      $st = '<option value="">No Raw Material Available</option>';
    }
    echo $st;
  }

public function index()
  {
    $data['rm_category_list'] = $this->Purchasereport_model->get_rawmaterial_category();
    $data['rm_gsm_list'] = $this->Purchasereport_model->get_active_rawmaterial_gsm();
    $data['rm_color_list'] = $this->Purchasereport_model->get_active_rawmaterial_color();
    $data['vendor_list'] = $this->Purchasereport_model->get_vendor_list();
    $data['location_list'] = $this->Purchasereport_model->get_location_list();

$this->load->view('purchasereport/purchase_report',$data);
  }

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

$data['fdate'] = $fdate;

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

$data['tdate'] = $tdate;

$rtype = $_POST['rtype'];
    $cat = $_POST['cat'];
    $prod = $_POST['prod'];
    $gsm = $_POST['gsm'];
    $color = $_POST['color'];
    $customer = $_POST['customer_id'];
    $location = $_POST['locid'];

$this->session->set_userdata('purreportfdate', $_POST['fdate']!=''?$fdate:'');
    $this->session->set_userdata('purreporttdate', $_POST['tdate']!=''?$tdate:'');
    $this->session->set_userdata('purreportcat', $_POST['cat']!=''?$_POST['cat']:'');
    $this->session->set_userdata('purreportprod', $_POST['prod']!=''?$_POST['prod']:'');
    $this->session->set_userdata('purreportgsm', $_POST['gsm']!=''?$_POST['gsm']:'');
    $this->session->set_userdata('purreportcolor', $_POST['color']!=''?$_POST['color']:'');
    $this->session->set_userdata('purreportcustomer', $_POST['customer_id']!=''?$_POST['customer_id']:'');
    $this->session->set_userdata('purreportlocation', $_POST['locid']!=''?$_POST['locid']:'');

if($rtype == 'purchase register')
    {
      if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $location=='')
      {
        $data['purchase_list'] = $this->Purchasereport_model->get_basic_purchase_list($fdate,$tdate);
        $data['purchase_product_list'] = $this->Purchasereport_model->get_basic_purhcase_product_list($fdate,$tdate);
      }
      else
      {
        $loctab='';$locjoin='';$locqry='';$ploc='';
        if($cat!='')
          $catqry = ' AND rm.rawmaterial_category_id='.$cat;
        else
          $catqry = '';

if($prod!='')
          $prodqry = ' AND rm.rawmaterial_id='.$prod;
        else
          $prodqry = '';

if($gsm!='')
          $gsmqry = ' AND rm.rawmaterial_gsm_id='.$gsm;
        else
          $gsmqry = '';

if($color!='')
          $colorqry = " AND rm.rawmaterial_color_id='".$color."'";
        else
          $colorqry = '';

if($customer!='')
          $custqry = " AND v.vendor_id='".$customer."'";
        else
          $custqry = '';

if($location!='')
        {
          $loctab = ',purchase_order po';
          $locjoin = ' AND p.purchase_order_id = po.purchase_order_id';
          $locqry = " AND po.location_id='".$location."'";
          $ploc = " AND p.location_id='".$location."'";
        }
        else
        {
          $loctab = '';
          $locjoin = '';
          $locqry = '';
          $ploc = '';
        }

//$data['purchase_list'] = $this->Purchasereport_model->get_basic_purchase_list($fdate,$tdate);

//$resqry = $this->db->query("SELECT pp.purchase_id,rm.rawmaterial,rmu.rawmaterial_uom,pp.quantity,pp.net_weight,pp.rate FROM `purchase_product` pp,purchase p, rawmaterial rm,rawmaterial_uom rmu,rawmaterial_category rmc,rawmaterial_gsm rmg,rawmaterial_color rmclr,vendor v WHERE p.purchase_id = pp.purchase_id AND pp.raw_material_id = rm.rawmaterial_id AND rm.rawmaterial_uom_id = rmu.rawmaterial_uom_id AND rm.rawmaterial_category_id = rmc.rawmaterial_category_id AND rm.rawmaterial_gsm_id = rmg.rawmaterial_gsm_id AND rm.rawmaterial_color_id = rmclr.rawmaterial_color_id AND v.vendor_id = p.vendor_id AND p.invoice_date>='$fdate' AND p.invoice_date<='$tdate' $catqry $prodqry $gsmqry $colorqry $custqry GROUP BY pp.purchase_product_id ORDER BY p.purchase_id");

/*echo "(SELECT pp.purchase_id, rm.rawmaterial,rmu.rawmaterial_uom,pp.quantity,pp.net_weight,pp.rate FROM `purchase_product` pp,purchase p, rawmaterial rm,rawmaterial_uom rmu,rawmaterial_category rmc,rawmaterial_gsm rmg,rawmaterial_color rmclr,vendor v $loctab WHERE p.purchase_id = pp.purchase_id AND pp.raw_material_id = rm.rawmaterial_id AND rm.rawmaterial_uom_id = rmu.rawmaterial_uom_id AND rm.rawmaterial_category_id = rmc.rawmaterial_category_id AND rm.rawmaterial_gsm_id = rmg.rawmaterial_gsm_id AND rm.rawmaterial_color_id = rmclr.rawmaterial_color_id AND v.vendor_id = p.vendor_id $locjoin AND p.invoice_date>='$fdate' AND p.invoice_date<='$tdate' $catqry $prodqry $gsmqry $colorqry $custqry $locqry GROUP BY pp.purchase_product_id)

UNION

/*$resqry = $this->db->query("(SELECT pp.purchase_id, rm.rawmaterial,rmu.rawmaterial_uom,pp.quantity,pp.net_weight,pp.rate FROM `purchase_product` pp,purchase p, rawmaterial rm,rawmaterial_uom rmu,rawmaterial_category rmc,rawmaterial_gsm rmg,rawmaterial_color rmclr,vendor v $loctab WHERE p.purchase_id = pp.purchase_id AND pp.raw_material_id = rm.rawmaterial_id AND rm.rawmaterial_uom_id = rmu.rawmaterial_uom_id AND rm.rawmaterial_category_id = rmc.rawmaterial_category_id AND rm.rawmaterial_gsm_id = rmg.rawmaterial_gsm_id AND rm.rawmaterial_color_id = rmclr.rawmaterial_color_id AND v.vendor_id = p.vendor_id $locjoin AND p.invoice_date>='$fdate' AND p.invoice_date<='$tdate' AND p.status!=5 $catqry $prodqry $gsmqry $colorqry $custqry $locqry GROUP BY pp.purchase_product_id)

UNION

$data['purchase_product_list'] = $resqry->result_array();
      }
      $this->load->view('purchasereport/purchase_register_table',$data);
    }

if($rtype == 'pending purchase')
    {
      if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $location=='')
      {
        $data['pending_purchase_list'] = $this->Purchasereport_model->get_basic_pending_purchase_list($fdate,$tdate);
        $data['pending_purchase_product_list'] = $this->Purchasereport_model->get_basic_pending_purhcase_product_list($fdate,$tdate);
      }
      else
      {
        $loctab='';$locjoin='';$locqry=''; $ploc='';
        if($cat!='')
          $catqry = ' AND rm.rawmaterial_category_id='.$cat;
        else
          $catqry = '';

if($prod!='')
          $prodqry = ' AND rm.rawmaterial_id='.$prod;
        else
          $prodqry = '';

if($gsm!='')
          $gsmqry = ' AND rm.rawmaterial_gsm_id='.$gsm;
        else
          $gsmqry = '';

if($color!='')
          $colorqry = " AND rm.rawmaterial_color_id='".$color."'";
        else
          $colorqry = '';

if($customer!='')
          $custqry = " AND v.vendor_id='".$customer."'";
        else
          $custqry = '';

if($location!='')
        {
          $loctab = ',purchase_order po';
          $locjoin = ' AND p.purchase_order_id = po.purchase_order_id';
          $locqry = " AND po.location_id='".$location."'";
          $ploc = " AND p.location_id='".$location."'";
        }
        else
        {
          $loctab = '';
          $locjoin = '';
          $locqry = '';
          $ploc = '';
        }

$data['pending_purchase_list'] = $this->Purchasereport_model->get_basic_pending_purchase_list($fdate,$tdate);

/*$purresqry = $this->db->query("SELECT p.*,v.* FROM purchase p, vendor v WHERE p.vendor_id = v.vendor_id AND p.invoice_date>='$fdate' AND p.invoice_date<='$tdate' $custqry AND p.status=0");
        $data['pending_purchase_list'] = $purresqry->result_array();*/

$resqry = $this->db->query("(SELECT pp.purchase_id, rm.rawmaterial,rmu.rawmaterial_uom,pp.quantity,pp.net_weight,pp.rate FROM `purchase_product` pp,purchase p, rawmaterial rm,rawmaterial_uom rmu,rawmaterial_category rmc,rawmaterial_gsm rmg,rawmaterial_color rmclr,vendor v $loctab WHERE p.purchase_id = pp.purchase_id AND pp.raw_material_id = rm.rawmaterial_id AND rm.rawmaterial_uom_id = rmu.rawmaterial_uom_id AND rm.rawmaterial_category_id = rmc.rawmaterial_category_id AND rm.rawmaterial_gsm_id = rmg.rawmaterial_gsm_id AND rm.rawmaterial_color_id = rmclr.rawmaterial_color_id AND v.vendor_id = p.vendor_id $locjoin AND p.invoice_date>='$fdate' AND p.invoice_date<='$tdate' AND p.status=0 $catqry $prodqry $gsmqry $colorqry $custqry $locqry GROUP BY pp.purchase_product_id)

UNION

ORDER BY purchase_id");

$data['pending_purchase_product_list'] = $resqry->result_array();
      }
      $this->load->view('purchasereport/pending_purchase_table',$data);
    }

}

}
?>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: Purchasereport.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation