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

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
ini_set('max_execution_time',259200);
ini_set('mysql.connect_timeout', 259200);
ini_set('default_socket_timeout', 259200);
class Salesreport extends CI_Controller {
  public function __construct()
  {
    parent::__construct();
    $this->load->library('excel');
    $this->load->model(array('Salesreport_model'));
    $this->load->model(array('Quotation_model'));
    $this->load->model(array('Invoice_model','Ledger_model'));
    $this->load->library('ajax_pagination');
    //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', 'Sales Report');
  }

public function sales_report_export()
  {
    $fdate = isset($_SESSION['salereportfdate'])?$_SESSION['salereportfdate']:'';
    $tdate = isset($_SESSION['salereporttdate'])?$_SESSION['salereporttdate']:'';
    $cat = isset($_SESSION['salereportcat'])?$_SESSION['salereportcat']:'';
    $prod = isset($_SESSION['salereportprod'])?$_SESSION['salereportprod']:'';
    $gsm = isset($_SESSION['salereportgsm'])?$_SESSION['salereportgsm']:'';
    $color = isset($_SESSION['salereportcolor'])?$_SESSION['salereportcolor']:'';
    $customer = isset($_SESSION['salereportcustomer'])?$_SESSION['salereportcustomer']:'';
    $unit = isset($_SESSION['salereportunit'])?$_SESSION['salereportunit']:'';
    $comp = isset($_SESSION['salereportcomp'])?$_SESSION['salereportcomp']:'';
    $rmcat = isset($_SESSION['salereportrmcat'])?$_SESSION['salereportrmcat']:'';
    $wid = isset($_SESSION['salereportwid'])?$_SESSION['salereportwid']:'';

if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $unit=='' && $comp=='' && $rmcat=='' && $wid=='')
    {
      $resqry = $this->db->query("(SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id,sl.state,pc.hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id,sl.state,pc.hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,r.rawmaterial,u.uom,c.state_id,sl.state,'' as hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,customer c,customer_type ct,rawmaterial r,uom u,state_list sl WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.rawmaterial_id = r.rawmaterial_id AND ip.uom_id = u.uom_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY ip.invoice_product_id)UNION(SELECT ip.*,i.invoice_no,i.invoice_date,'' as net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,'' as rawmaterial,'' as uom,c.state_id,sl.state,'' as hsn_code,cdn.amount as cdnamt FROM credit_debit_note cdn ,`invoice_product` ip,invoice i,customer c,customer_type ct,state_list sl WHERE cdn.type_id = i.invoice_id AND ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND c.state_id = sl.state_id AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND cdn.credit_debit = 'Credit' GROUP BY cdn.credit_debit_note_id)UNION(SELECT ip.*,i.invoice_no,i.invoice_date,'' as net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,'' as rawmaterial,'' as uom,c.state_id,sl.state,'' as hsn_code,cdn.amount as cdnamt FROM credit_debit_note cdn ,`invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,state_list sl WHERE cdn.type_id = i.invoice_id AND ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND c.state_id = sl.state_id AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND cdn.credit_debit = 'Credit' GROUP BY cdn.credit_debit_note_id) ORDER BY invoice_id ASC");

$sales_report_list = $resqry->result_array();
    }
    else
    {
      if($cat!='')
        $catqry = ' AND p.category_id='.$cat;
      else
        $catqry = '';

if($prod!='')
        $prodqry = ' AND p.product_id='.$prod;
      else
        $prodqry = '';

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

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

if($customer!='')
        $custqry = " AND c.customer_id='".$customer."'";
      else
        $custqry = '';

if($unit!='')
      {
        $unitqry = " AND o.prod_unit='".$unit."'";
        $dunitqry = " AND i.prod_unit='".$unit."'";
      }
      else
      {
        $unitqry = '';
        $dunitqry = '';
      }

if($comp!='')
      {
        $compqry = " AND o.location_id='".$comp."'";
        $dcompqry = " AND i.location_id='".$comp."'";
      }
      else
      {
        $compqry = '';
        $dcompqry = '';
      }

if($rmcat!='')
      {
        $rmcatqry = " AND r.rawmaterial_category_id='".$rmcat."'";
      }
      else
      {
        $rmcatqry = '';
      }

if($wid!='')
      {
        $whid = " AND i.warehouse_id='".$wid."'";
      }
      else
      {
        $whid = '';
      }

$resqry = $this->db->query("(SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id,sl.state,pc.hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $catqry $prodqry $gsmqry $colorqry $custqry $unitqry $compqry $whid GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id,sl.state,pc.hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $catqry $prodqry $gsmqry $colorqry $custqry $dunitqry $dcompqry $whid GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,r.rawmaterial,u.uom,c.state_id,sl.state,'' as hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,customer c,customer_type ct,rawmaterial r,rawmaterial_category rc,uom u,state_list sl WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.rawmaterial_id = r.rawmaterial_id AND r.rawmaterial_category_id = rc.rawmaterial_category_id AND ip.uom_id = u.uom_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $rmcatqry $custqry $dunitqry $dcompqry $whid GROUP BY ip.invoice_product_id)UNION(SELECT ip.*,i.invoice_no,i.invoice_date,'' as net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,'' as rawmaterial,'' as uom,c.state_id,sl.state,'' as hsn_code,cdn.amount as cdnamt FROM credit_debit_note cdn ,`invoice_product` ip,invoice i,customer c,customer_type ct,state_list sl WHERE cdn.type_id = i.invoice_id AND ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND c.state_id = sl.state_id AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND cdn.credit_debit = 'Credit' $dcompqry $whid GROUP BY cdn.credit_debit_note_id)UNION(SELECT ip.*,i.invoice_no,i.invoice_date,'' as net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,'' as rawmaterial,'' as uom,c.state_id,sl.state,'' as hsn_code,cdn.amount as cdnamt FROM credit_debit_note cdn ,`invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,state_list sl WHERE cdn.type_id = i.invoice_id AND ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND c.state_id = sl.state_id AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND cdn.credit_debit = 'Credit' $compqry $whid GROUP BY cdn.credit_debit_note_id) ORDER BY invoice_id ASC");

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

$g_settings = general_setting_details();

if($comp!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($comp);
      $locationName = $locationlist->location_name;
      $reptitle = 'Sales 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 = 'Sales 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:Q1');
    $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', 'Customer Type');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Customer Name');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'GST No');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'State');
    $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'Product');
    $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'HSN Code');
    $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('I2', 'UOM');
    $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('J2', 'Qty');
    $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('K2', 'Nett Wt');
    $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('L2', 'Taxable Value');
    $objPHPExcel->getActiveSheet()->getStyle('L2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('M2', 'Tax Rate');
    $objPHPExcel->getActiveSheet()->getStyle('M2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('N2', 'SGST');
    $objPHPExcel->getActiveSheet()->getStyle('N2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('O2', 'CGST');
    $objPHPExcel->getActiveSheet()->getStyle('O2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('P2', 'IGST');
    $objPHPExcel->getActiveSheet()->getStyle('P2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('Q2', 'Total Amount');
    $objPHPExcel->getActiveSheet()->getStyle('Q2')->applyFromArray($styleArray);

$i=3;  $qt=0;$wgt=0;$tval=0;$sg=0;$cg=0;$ig=0;$tamt=0;
    foreach ($sales_report_list as $pplist ) {

if($pplist['product_name']!='')
      {
          $totamt = $pplist['amount'];
          $taxamt = ($pplist['amount']*$pplist['product_gst'])/100;
          $amtxt = ($pplist['amount']+($taxamt*2));
          $rofftot = round($amtxt);
          $roff = $amtxt - $rofftot;
          //$totval = number_format(($pplist['amount']+($taxamt*2)),2);
          $totval = ($rofftot);
          if($pplist['state_id']==33)
          {
              $cgst = $taxamt;
              $sgst = $taxamt;
              $cgstval = number_format($taxamt,2);
              $sgstval = number_format($taxamt,2);
              /*$cgstval = $taxamt;
              $sgstval = $taxamt;*/
              $igst = '-';
              $igstval=0;
          }
          else
          {
              $cgst = '-';
              $sgst = '-';
              $cgstval = 0;
              $sgstval = 0;
              /*$igst = number_format($taxamt,2)*2;
              $igstval=number_format($taxamt,2)*2;*/
              $igst = ($taxamt*2);
              $igstval=number_format(($taxamt*2),2);
          }
      }
      else
      {
          $taxableamt = ($pplist['cdnamt']*100)/(100+($pplist['product_gst']*2));

$taxamt = ($taxableamt*$pplist['product_gst'])/100;
          $totval = ($taxableamt+($taxamt*2));
          if($pplist['state_id']==33)
          {
              $cgst = -1*$taxamt;
              $sgst = -1*$taxamt;
              $cgstval = -1*number_format($taxamt,2);
              $sgstval = -1*number_format($taxamt,2);
              /*$cgstval = $taxamt;
              $sgstval = $taxamt;*/
              $igst = '-';
              $igstval=0;
          }
          else
          {
              $cgst = '-';
              $sgst = '-';
              $cgstval = 0;
              $sgstval = 0;
              /*$igst = number_format($taxamt,2)*2;
              $igstval=number_format($taxamt,2)*2;*/
              $igst = -1*($taxamt*2);
              $igstval=-1*number_format(($taxamt*2),2);
          }
          $totamt = -1*$pplist['cdnamt'];
      }
      $iswgt = $pplist['weight']!=0?$pplist['weight']:$pplist['net_weight'];
      $cusDetails = $this->Ledger_model->get_customer_contact_details($pplist['customer_id']);
      $phone_no = $cusDetails->phone_no;
      if($pplist['product_name']!='')
      {
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $pplist['invoice_no']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, date_format(date_create($pplist['invoice_date']),$g_settings->date_format));
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $pplist['customer_type']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $pplist['customer_name'].' - '.$phone_no);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $pplist['gst_no']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $pplist['state']);
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $pplist['product_name']);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $pplist['hsn_code']);
        $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $pplist['uom']);
        $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $pplist['quantity']);
        $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $pplist['weight']!=0?round($pplist['weight'],2):round($pplist['net_weight'],2));
        $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, round($pplist['amount'],2));
        $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, $pplist['product_gst']);
        $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, round($sgst,2));
        $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, round($cgst,2));
        $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, round($igst,2));
        $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, round($totval,2));
      }
      else
      {
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $pplist['invoice_no']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, date_format(date_create($pplist['invoice_date']),$g_settings->date_format));
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $pplist['customer_type']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $pplist['customer_name'].' - '.$phone_no);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $pplist['gst_no']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $pplist['state']);
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $pplist['product_name']);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $pplist['hsn_code']);
        $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $pplist['uom']);
        $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, '');
        $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, '');
        $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, -1*round($taxableamt,2));
        $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, $pplist['product_gst']*2);
        $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, round($sgst,2));
        $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, round($cgst,2));
        $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, round($igst,2));
        $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, -1*round($pplist['cdnamt'],2));
        $totval = -1*$pplist['cdnamt'];        
      }

$i++;
      $qt+=$pplist['quantity'];$wgt+=$iswgt;$tval+=$totamt;$cg+=(float)str_replace( ',', '', $cgstval);$sg+=(float)str_replace( ',', '', $sgstval);$ig+=(float)str_replace( ',', '', $igstval);$tamt+=(float)str_replace( ',', '', $totval);
    }

$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, 'Total');
    $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $qt);
    $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, round($wgt,2));
    $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, round($tval,2));
    $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, round($sg,2));
    $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, round($cg,2));
    $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, round($ig,2));
    $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, round($tamt,2));

$filename='Sales_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 sales_register_export()
  {
    $fdate = isset($_SESSION['salereportfdate'])?$_SESSION['salereportfdate']:'';
    $tdate = isset($_SESSION['salereporttdate'])?$_SESSION['salereporttdate']:'';
    $cat = isset($_SESSION['salereportcat'])?$_SESSION['salereportcat']:'';
    $prod = isset($_SESSION['salereportprod'])?$_SESSION['salereportprod']:'';
    $gsm = isset($_SESSION['salereportgsm'])?$_SESSION['salereportgsm']:'';
    $color = isset($_SESSION['salereportcolor'])?$_SESSION['salereportcolor']:'';
    $customer = isset($_SESSION['salereportcustomer'])?$_SESSION['salereportcustomer']:'';
    $unit = isset($_SESSION['salereportunit'])?$_SESSION['salereportunit']:'';
    $comp = isset($_SESSION['salereportcomp'])?$_SESSION['salereportcomp']:'';
    $rmcat = isset($_SESSION['salereportrmcat'])?$_SESSION['salereportrmcat']:'';
    $wid = isset($_SESSION['salereportwid'])?$_SESSION['salereportwid']:'';

if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $unit=='' && $comp=='' && $wid=='')
    {
      $invoice_list = $this->Salesreport_model->get_basic_sales_register_invoice_list($fdate,$tdate);
      $sales_register_list = $this->Salesreport_model->get_basic_sales_report_list($fdate,$tdate);
    }
    else
    {
      if($cat!='')
        $catqry = ' AND p.category_id='.$cat;
      else
        $catqry = '';

if($prod!='')
        $prodqry = ' AND p.product_id='.$prod;
      else
        $prodqry = '';

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

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

if($customer!='')
        $custqry = " AND c.customer_id='".$customer."'";
      else
        $custqry = '';

if($rmcat!='')
      {
        $rmcatqry = " AND r.rawmaterial_category_id='".$rmcat."'";
      }
      else
      {
        $rmcatqry = '';
      }

if($wid!='')
      {
        $whid = " AND i.warehouse_id='".$wid."'";
      }
      else
      {
        $whid = '';
      }

$invoice_list = $this->Salesreport_model->get_basic_sales_register_invoice_list($fdate,$tdate);
      $resqry = $this->db->query("(SELECT ip.*,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id FROM `invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,product p,uom u WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $catqry $prodqry $gsmqry $colorqry $custqry $unitqry $compqry $whid GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id FROM `invoice_product` ip,invoice i,customer c,customer_type ct,product p,uom u WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $catqry $prodqry $gsmqry $colorqry $custqry $unitqry $dcompqry $whid GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,r.rawmaterial as product_name,ru.rawmaterial_uom,c.state_id FROM `invoice_product` ip,invoice i,customer c,customer_type ct,rawmaterial r,rawmaterial_uom ru WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.rawmaterial_id = r.rawmaterial_id AND ip.uom_id = ru.rawmaterial_uom_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $rmcatqry $custqry $dunitqry $dcompqry $whid GROUP BY ip.invoice_product_id) ORDER BY invoice_id");
      $sales_register_list = $resqry->result_array();
    }

$g_settings = general_setting_details();

if($comp!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($comp);
      $locationName = $locationlist->location_name;
      $reptitle = 'Sales 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 = 'Sales 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->getActiveSheet()->mergeCells('A1:J1');
    $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', 'Customer Name');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Product');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Qty');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Nett Wt');
    $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'Total Wt');
    $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Rate Before Tax');
    $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('I2', 'Rate Per KG');
    $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('J2', 'Total Value');
    $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleArray);

$i=3;  $twgt=0;$trte=0;$kgrte=0;$tval=0;
    foreach($invoice_list as $pplist){
      $j=0;foreach($sales_register_list as $srlist){ 
        if($srlist['invoice_id'] == $pplist['invoice_id']){
          if($j==0){
            if($pplist['totwgt']==0)
                $pplist['totwgt'] = 1;
            $kgr = round($pplist['totrate']/$pplist['totwgt'],2);
            $tv = $pplist['grand_total'];
            $tw = $pplist['totwgt'];
            $tr = $pplist['totrate'];
            $cusDetails = $this->Ledger_model->get_customer_contact_details($srlist['customer_id']);
            $phone_no = $cusDetails->phone_no;

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $pplist['invoice_no']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, date_format(date_create($pplist['invoice_date']),$g_settings->date_format));
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $pplist['customer_name'].' - '.$phone_no);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $srlist['product_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $srlist['quantity']);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $srlist['weight']);
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, round($pplist['totwgt'],3));
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, round($pplist['totrate'],2));
            $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, round($pplist['totrate']/$pplist['totwgt'],2));
            $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, round($pplist['grand_total'],2));

$twgt+=$tw;$trte+=$tr;$kgrte+=$kgr;$tval+=$tv;
          }
          else
          {
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $srlist['product_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $srlist['quantity']);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $srlist['weight']);
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, '');
            $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, '');
          }

$i++;$j++;
        }
      }
    }

$filename='Sales_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

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 sales_gst_report_export()
  {
    $fdate = isset($_SESSION['salereportfdate'])?$_SESSION['salereportfdate']:'';
    $tdate = isset($_SESSION['salereporttdate'])?$_SESSION['salereporttdate']:'';
    $cat = isset($_SESSION['salereportcat'])?$_SESSION['salereportcat']:'';
    $prod = isset($_SESSION['salereportprod'])?$_SESSION['salereportprod']:'';
    $gsm = isset($_SESSION['salereportgsm'])?$_SESSION['salereportgsm']:'';
    $color = isset($_SESSION['salereportcolor'])?$_SESSION['salereportcolor']:'';
    $customer = isset($_SESSION['salereportcustomer'])?$_SESSION['salereportcustomer']:'';
    $unit = isset($_SESSION['salereportunit'])?$_SESSION['salereportunit']:'';
    $comp = isset($_SESSION['salereportcomp'])?$_SESSION['salereportcomp']:'';
    $rmcat = isset($_SESSION['salereportrmcat'])?$_SESSION['salereportrmcat']:'';
    $wid = isset($_SESSION['salereportwid'])?$_SESSION['salereportwid']:'';

$cate = $cat;
    if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $unit=='' && $comp=='' && $rmcat=='' && $wid=='')
    {
      $resqry = $this->db->query("(SELECT ip.*,i.pos_invoice_id,i.invoice_no,i.invoice_date,sum(ip.weight) as issue_weight,i.net_weight,i.taxable_amount,i.packing_charges,i.other_charges,i.tax_total,i.round_off,i.disc_amount,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,GROUP_CONCAT(p.product_name) as product_name,GROUP_CONCAT(u.uom) as uom,c.state_id,sl.state,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.amount) as prod_amount,GROUP_CONCAT(ip.product_gst) as prod_tax FROM `invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY i.invoice_id) UNION (SELECT ip.*,i.pos_invoice_id,i.invoice_no,i.invoice_date,sum(ip.weight) as issue_weight,i.net_weight,i.taxable_amount,i.packing_charges,i.other_charges,i.tax_total,i.round_off,i.disc_amount,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,GROUP_CONCAT(p.product_name) as product_name,GROUP_CONCAT(u.uom) as uom,c.state_id,sl.state,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.amount) as prod_amount,GROUP_CONCAT(ip.product_gst) as prod_tax FROM `invoice_product` ip,invoice i,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY i.invoice_id) UNION (SELECT ip.*,i.pos_invoice_id,i.invoice_no,i.invoice_date,sum(ip.weight) as issue_weight,i.net_weight,i.taxable_amount,i.packing_charges,i.other_charges,i.tax_total,i.round_off,i.disc_amount,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,GROUP_CONCAT(r.rawmaterial) as product_name,GROUP_CONCAT(u.uom) as uom,c.state_id,sl.state,'' as hsn_code,GROUP_CONCAT(ip.quantity) AS prod_quantity,GROUP_CONCAT(ip.amount) as prod_amount,GROUP_CONCAT(ip.product_gst) as prod_tax FROM `invoice_product` ip,invoice i,customer c,customer_type ct,rawmaterial r,uom u,state_list sl WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.rawmaterial_id = r.rawmaterial_id AND ip.uom_id = u.uom_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY i.invoice_id) ORDER BY invoice_id ASC");
      $sales_gst_report_list = $resqry->result_array();
    }
    else
    {
      if($cat!='')
        $catqry = ' AND p.category_id='.$cat;
      else
        $catqry = '';

if($prod!='')
        $prodqry = ' AND p.product_id='.$prod;
      else
        $prodqry = '';

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

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

if($customer!='')
        $custqry = " AND c.customer_id='".$customer."'";
      else
        $custqry = '';

if($rmcat!='')
      {
        $rmcatqry = " AND r.rawmaterial_category_id='".$rmcat."'";
      }
      else
      {
        $rmcatqry = '';
      }

if($wid!='')
      {
        $whid = " AND i.warehouse_id='".$wid."'";
      }
      else
      {
        $whid = '';
      }

$resqry = $this->db->query("(SELECT ip.*,i.pos_invoice_id,i.invoice_no,i.invoice_date,sum(ip.weight) as issue_weight,i.net_weight,i.taxable_amount,i.packing_charges,i.other_charges,i.tax_total,i.round_off,i.disc_amount,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,GROUP_CONCAT(p.product_name) as product_name,GROUP_CONCAT(u.uom) as uom,c.state_id,sl.state,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.amount) as prod_amount,GROUP_CONCAT(ip.product_gst) as prod_tax FROM `invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $catqry $prodqry $gsmqry $colorqry $custqry $unitqry $compqry $whid GROUP BY i.invoice_id) UNION (SELECT ip.*,i.pos_invoice_id,i.invoice_no,i.invoice_date,sum(ip.weight) as issue_weight,i.net_weight,i.taxable_amount,i.packing_charges,i.other_charges,i.tax_total,i.round_off,i.disc_amount,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,GROUP_CONCAT(p.product_name) as product_name,GROUP_CONCAT(u.uom) as uom,c.state_id,sl.state,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.amount) as prod_amount,GROUP_CONCAT(ip.product_gst) as prod_tax FROM `invoice_product` ip,invoice i,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $catqry $prodqry $gsmqry $colorqry $custqry $dunitqry $dcompqry $whid GROUP BY i.invoice_id) UNION (SELECT ip.*,i.pos_invoice_id,i.invoice_no,i.invoice_date,sum(ip.weight) as issue_weight,i.net_weight,i.taxable_amount,i.packing_charges,i.other_charges,i.tax_total,i.round_off,i.disc_amount,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,GROUP_CONCAT(r.rawmaterial) as product_name,GROUP_CONCAT(u.uom) as uom,c.state_id,sl.state,'' as hsn_code,GROUP_CONCAT(ip.quantity) AS prod_quantity,GROUP_CONCAT(ip.amount) as prod_amount,GROUP_CONCAT(ip.product_gst) as prod_tax FROM `invoice_product` ip,invoice i,customer c,customer_type ct,rawmaterial r,uom u,state_list sl WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.rawmaterial_id = r.rawmaterial_id AND ip.uom_id = u.uom_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $rmcatqry $custqry $dunitqry $dcompqry $whid GROUP BY i.invoice_id) ORDER BY invoice_id ASC");

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

$g_settings = general_setting_details();

if($comp!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($comp);
      $locationName = $locationlist->location_name;
      $reptitle = 'Sales GST 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 = 'Sales GST 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:U1');
    $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', 'Reference No');
    $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Invoice Date');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Customer Type');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Customer Name');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'GST No');
    $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'State');
    $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Product');
    $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('I2', 'HSN Code');
    $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('J2', 'UOM');
    $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('K2', 'Qty');
    $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('L2', 'Nett Wt');
    $objPHPExcel->getActiveSheet()->getStyle('L2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('M2', 'Basic Value');
    $objPHPExcel->getActiveSheet()->getStyle('M2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('N2', 'Packing & Forwarding Charges');
    $objPHPExcel->getActiveSheet()->getStyle('N2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('O2', 'Other Charges');
    $objPHPExcel->getActiveSheet()->getStyle('O2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('P2', 'Taxable Value');
    $objPHPExcel->getActiveSheet()->getStyle('P2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('Q2', 'Tax Rate');
    $objPHPExcel->getActiveSheet()->getStyle('Q2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('R2', 'SGST');
    $objPHPExcel->getActiveSheet()->getStyle('R2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('S2', 'CGST');
    $objPHPExcel->getActiveSheet()->getStyle('S2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('T2', 'IGST');
    $objPHPExcel->getActiveSheet()->getStyle('T2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('U2', 'Total Amount');
    $objPHPExcel->getActiveSheet()->getStyle('U2')->applyFromArray($styleArray);

$i=3;$qt=0;$wgt=0;$tval=0;$sg=0;$cg=0;$ig=0;$tamt=0;$bval=0;$pfc=0;$oc=0;
    foreach($sales_gst_report_list as $pplist){
    $hsn = explode(',', $pplist['hsn_code']);
      $hcode = '';
      for($j=0;$j<count($hsn);$j++)
      {
          if(($j+1)==count($hsn))
              $hcode.=$hsn[$j];
          else
              $hcode.=$hsn[$j].' , ';
      }

$pqty = explode(',', $pplist['prod_quantity']);
      $prodqty = '';
      for($j=0;$j<count($pqty);$j++)
      {
          if(($j+1)==count($pqty))
              $prodqty.=$pqty[$j];
          else
              $prodqty.=$pqty[$j].' , ';
      }

$pamt = explode(",", $pplist['prod_amount']);
      $ptax = explode(",", $pplist['prod_tax']);
      $totamt=0;
      $totaltax = 0;
      for($j=0;$j<count($pamt);$j++)
      {
          $totamt+= $pamt[$j];
          $totaltax+= ($pamt[$j]*$ptax[$j])/100;
      }

if($pplist['pos_invoice_id']!=0)
        {
          $posid = $pplist['pos_invoice_id'];
          $posref = $this->db->query("SELECT * FROM sma_sales WHERE id = $posid")->row();
          $refno = $posref->reference_no;
        }
        else
        {
          $refno = '';
        }

$invid = $pplist['invoice_id'];

$maxtax = $this->db->query("SELECT * FROM invoice_product WHERE invoice_id = $invid ORDER BY product_gst DESC LIMIT 1")->row();
      $maxtaxval = max(array_unique($ptax));

$alltax = $this->db->query("SELECT GROUP_CONCAT(product_gst) as product_tax FROM invoice_product WHERE invoice_id = $invid")->row();

$prodtax = explode(",", $alltax->product_tax);

$aunique = array_unique($prodtax);

if(count($aunique)==1 && $cate!='')
      {
          $maxtaxval = max(array_unique($prodtax));
          $firstProd = $this->db->query("SELECT ip.*,p.category_id FROM invoice_product ip,product p WHERE ip.product_id = p.product_id AND ip.invoice_id = $invid ORDER BY ip.invoice_product_id ASC LIMIT 1")->row();

if($firstProd->category_id==$cate)
          {
              $pcharge = $pplist['packing_charges'];
              $othcharge  = $pplist['other_charges'];
              $ocharge = $pplist['packing_charges']+$pplist['other_charges'];
              $octax = ($ocharge*$maxtaxval)/100;
              $discamt = $pplist['disc_amount'];
          }
          else
          {
              $pcharge = 0;
              $othcharge  = 0;
              $ocharge = 0;
              $octax = 0;
              $discamt = 0;
          }
      }
      else
      {
          if($maxtax->product_gst==$maxtaxval)
          {
              $pcharge = $pplist['packing_charges'];
              $othcharge  = $pplist['other_charges'];
              $ocharge = $pplist['packing_charges']+$pplist['other_charges'];
              $octax = ($ocharge*$maxtaxval)/100;
              $discamt = $pplist['disc_amount'];
          }
          else
          {
              $pcharge = 0;
              $othcharge  = 0;
              $ocharge = 0;
              $octax = 0;
              $discamt = 0;
          }
      }

/*if($pplist['pos_invoice_id']!=0)
      {
          $octax = 0;
      }*/

//$ttax = ($totamt*$pplist['product_gst'])/100;
      //$ttax = ($totamt+$totaltax);
      $ttax = $totaltax;
      $tottax = $ttax+$octax;

if($pplist['state_id']==33)
      {
          $cgst = $tottax;
          $sgst = $tottax;
          $cgstval = number_format($tottax,2);
          $sgstval = number_format($tottax,2);
          /*$cgstval = $taxamt;
          $sgstval = $taxamt;*/
          $igst = '-';
          $igstval=0;
      }
      else
      {
          $cgst = '-';
          $sgst = '-';
          $cgstval = 0;
          $sgstval = 0;
          /*$igst = number_format($taxamt,2)*2;
          $igstval=number_format($taxamt,2)*2;*/
          $igst = ($tottax*2);
          $igstval=number_format(($tottax*2),2);
      }
      $tblamt = ($totamt+$ocharge);
      //$totval = ($totamt+$ocharge+($tottax*2));
      //$totval = number_format((($totamt+$ocharge+($tottax*2))-$pplist['disc_amount']),2);
      $totval = (($totamt+$ocharge+($tottax*2))-$discamt);

$iswgt = $pplist['issue_weight']!=0?$pplist['issue_weight']:$pplist['net_weight'];

$cusDetails = $this->Ledger_model->get_customer_contact_details($pplist['customer_id']);
      $phone_no = $cusDetails->phone_no;

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $pplist['invoice_no']);
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $refno);
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, date_format(date_create($pplist['invoice_date']),$g_settings->date_format));
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $pplist['customer_type']);
      $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $pplist['customer_name'].' - '.$phone_no);
      $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $pplist['gst_no']);
      $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $pplist['state']);
      $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $pplist['product_name']);
      $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $hcode);
      $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $pplist['uom']);
      $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $prodqty);
      $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, $pplist['issue_weight']!=0?round($pplist['issue_weight'],2):round($pplist['net_weight'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, round($totamt,2));
      $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, round($pcharge,2));
      $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, round($othcharge,2));
      $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, round($tblamt,2));
      $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, $pplist['product_gst']*2);
      $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, round($sgst,2));
      $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, round($cgst,2));
      $objPHPExcel->getActiveSheet()->setCellValue('T'.$i, round($igst,2));
      $objPHPExcel->getActiveSheet()->setCellValue('U'.$i, round($totval,2));

$i++;$wgt+=(float)$iswgt;$tval+=(float)str_replace( ',', '', $tblamt);$cg+=(float)str_replace( ',', '', $cgstval);$sg+=(float)str_replace( ',', '', $sgstval);$ig+=(float)str_replace( ',', '', $igstval);$tamt+=(float)str_replace( ',', '', $totval);$bval+=(float)$totamt;$pfc+=(float)$pcharge;$oc+=(float)$othcharge;
    }

$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, 'Total');
    $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, round($wgt,3));
    $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, round($bval,2));
    $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, round($pfc,2));
    $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, round($oc,2));
    $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, round($tval,2));
    $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, '');
    $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, round($sg,2));
    $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, round($cg,2));
    $objPHPExcel->getActiveSheet()->setCellValue('T'.$i, round($ig,2));
    $objPHPExcel->getActiveSheet()->setCellValue('U'.$i, round($tamt,2));

$filename='Sales_GST_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 despatch_report_export()
  {
    $fdate = isset($_SESSION['salereportfdate'])?$_SESSION['salereportfdate']:'';
    $tdate = isset($_SESSION['salereporttdate'])?$_SESSION['salereporttdate']:'';
    $cat = isset($_SESSION['salereportcat'])?$_SESSION['salereportcat']:'';
    $prod = isset($_SESSION['salereportprod'])?$_SESSION['salereportprod']:'';
    $gsm = isset($_SESSION['salereportgsm'])?$_SESSION['salereportgsm']:'';
    $color = isset($_SESSION['salereportcolor'])?$_SESSION['salereportcolor']:'';
    $customer = isset($_SESSION['salereportcustomer'])?$_SESSION['salereportcustomer']:'';
    $unit = isset($_SESSION['salereportunit'])?$_SESSION['salereportunit']:'';
    $comp = isset($_SESSION['salereportcomp'])?$_SESSION['salereportcomp']:'';
    $rmcat = isset($_SESSION['salereportrmcat'])?$_SESSION['salereportrmcat']:'';
    $wid = isset($_SESSION['salereportwid'])?$_SESSION['salereportwid']:'';

if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $unit=='' && $comp=='' && $rmcat=='' && $wid=='')
    {
      $resqry = $this->db->query("(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(pc.product_category) as product_category,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(u.uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no FROM `invoice_product` ip,invoice i,`order` o,customer c,product p,uom u,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=1 GROUP BY i.invoice_id)UNION(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(pc.product_category) as product_category,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(u.uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no FROM `invoice_product` ip,invoice i,customer c,product p,uom u,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=1 GROUP BY i.invoice_id)UNION(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(rc.rawmaterial_category) as product_category,'' as hsn_code,GROUP_CONCAT(ru.rawmaterial_uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no FROM `invoice_product` ip,invoice i,customer c,rawmaterial r,rawmaterial_uom ru,rawmaterial_category rc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND ip.rawmaterial_id = r.rawmaterial_id AND ip.uom_id = ru.rawmaterial_uom_id AND r.rawmaterial_category_id = rc.rawmaterial_category_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=1 GROUP BY i.invoice_id) ORDER BY invoice_id ASC");
      $despatch_report_list = $resqry->result_array();
    }
    else
    {
      if($cat!='')
        $catqry = ' AND p.category_id='.$cat;
      else
        $catqry = '';

if($prod!='')
        $prodqry = ' AND p.product_id='.$prod;
      else
        $prodqry = '';

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

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

if($customer!='')
        $custqry = " AND c.customer_id='".$customer."'";
      else
        $custqry = '';

if($rmcat!='')
      {
        $rmcatqry = " AND r.rawmaterial_category_id='".$rmcat."'";
      }
      else
      {
        $rmcatqry = '';
      }

if($wid!='')
      {
        $whid = " AND i.warehouse_id='".$wid."'";
      }
      else
      {
        $whid = '';
      }

$resqry = $this->db->query("(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(pc.product_category) as product_category,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(u.uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no FROM `invoice_product` ip,invoice i,`order` o,customer c,product p,uom u,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=1 $catqry $prodqry $gsmqry $colorqry $custqry $unitqry $compqry $whid GROUP BY i.invoice_id)UNION(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(pc.product_category) as product_category,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(u.uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no FROM `invoice_product` ip,invoice i,customer c,product p,uom u,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=1 $catqry $prodqry $gsmqry $colorqry $custqry $dunitqry $dcompqry $whid GROUP BY i.invoice_id)UNION(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(rc.rawmaterial_category) as product_category,'' as hsn_code,GROUP_CONCAT(ru.rawmaterial_uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no FROM `invoice_product` ip,invoice i,customer c,rawmaterial r,rawmaterial_uom ru,rawmaterial_category rc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND ip.rawmaterial_id = r.rawmaterial_id AND ip.uom_id = ru.rawmaterial_uom_id AND r.rawmaterial_category_id = rc.rawmaterial_category_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=1 $rmcatqry $custqry $dunitqry $dcompqry $whid GROUP BY i.invoice_id) ORDER BY invoice_id ASC");

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

$g_settings = general_setting_details();

if($comp!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($comp);
      $locationName = $locationlist->location_name;
      $reptitle = 'Despatch 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 = 'Despatch 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:O1');
    $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', 'Customer Name');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Product');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'HSN Code');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'UOM');
    $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'Qty');
    $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Issue Wt');
    $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('I2', 'Nett Wt');
    $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('J2', 'Gross Wt');
    $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('K2', 'Total Amount');
    $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('L2', 'No.of.Bundles');
    $objPHPExcel->getActiveSheet()->getStyle('L2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('M2', 'Transport Details');
    $objPHPExcel->getActiveSheet()->getStyle('M2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('N2', 'Destination Point');
    $objPHPExcel->getActiveSheet()->getStyle('N2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('O2', 'LR / RR Detail');
    $objPHPExcel->getActiveSheet()->getStyle('O2')->applyFromArray($styleArray);

$i=3;$prqty=$iswgt=$nwgt=$gwgt=$gtot=0;
    foreach($despatch_report_list as $pplist){
      $hsn = explode(',', $pplist['hsn_code']);
      $hcode = '';
      for($j=0;$j<count($hsn);$j++)
      {
          if(($j+1)==count($hsn))
              $hcode.=$hsn[$j];
          else
              $hcode.=$hsn[$j].' , ';
      }

$iwgt = explode(',', $pplist['issue_weight']);
      $issue_weight = '';
      for($j=0;$j<count($iwgt);$j++)
      {
          if(($j+1)==count($iwgt))
              $issue_weight.=$iwgt[$j];
          else
              $issue_weight.=$iwgt[$j].' , ';
      }

$um = explode(',', $pplist['uom']);
      $uom = '';
      for($j=0;$j<count($um);$j++)
      {
          if(($j+1)==count($um))
              $uom.=$um[$j];
          else
              $uom.=$um[$j].' , ';
      }

$cusDetails = $this->Ledger_model->get_customer_contact_details($pplist['customer_id']);
      $phone_no = $cusDetails->phone_no;

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $pplist['invoice_no']);
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, date_format(date_create($pplist['invoice_date']),$g_settings->date_format));
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $pplist['customer_name'].' - '.$phone_no);
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $pplist['product_category']);
      $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $hcode);
      $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $uom);
      $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $prodqty);
      $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $issue_weight);
      $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, round($pplist['net_weight'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, round($pplist['gross_weight'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, round($pplist['grand_total'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, $pplist['no_of_packets']);
      $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, $pplist['transporter']);
      $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, $pplist['destination_point']);
      $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, $pplist['lr_rr_no']);

$i++;$prqty+=$prodqty;$iswgt+=$issue_weight;$nwgt+=$pplist['net_weight'];$gwgt+=$pplist['gross_weight'];$gtot+=$pplist['grand_total'];
    }

$filename='Despatch_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 delivery_report_export()
  {
    $fdate = isset($_SESSION['salereportfdate'])?$_SESSION['salereportfdate']:'';
    $tdate = isset($_SESSION['salereporttdate'])?$_SESSION['salereporttdate']:'';
    $cat = isset($_SESSION['salereportcat'])?$_SESSION['salereportcat']:'';
    $prod = isset($_SESSION['salereportprod'])?$_SESSION['salereportprod']:'';
    $gsm = isset($_SESSION['salereportgsm'])?$_SESSION['salereportgsm']:'';
    $color = isset($_SESSION['salereportcolor'])?$_SESSION['salereportcolor']:'';
    $customer = isset($_SESSION['salereportcustomer'])?$_SESSION['salereportcustomer']:'';
    $unit = isset($_SESSION['salereportunit'])?$_SESSION['salereportunit']:'';
    $comp = isset($_SESSION['salereportcomp'])?$_SESSION['salereportcomp']:'';
    $rmcat = isset($_SESSION['salereportrmcat'])?$_SESSION['salereportrmcat']:'';
    $wid = isset($_SESSION['salereportwid'])?$_SESSION['salereportwid']:'';

if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $unit=='' && $comp=='' && $rmcat=='' && $wid=='')
    {
      $resqry = $this->db->query("(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(pc.product_category) as product_category,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(u.uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no,GROUP_CONCAT(ib.invoice_barcode) as barcode FROM `invoice_product` ip,invoice i,`order` o,customer c,product p,uom u,product_category pc,invoice_barcode ib WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND i.invoice_id = ib.invoice_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=0 GROUP BY i.invoice_id)UNION(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(pc.product_category) as product_category,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(u.uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no,GROUP_CONCAT(ib.invoice_barcode) as barcode FROM `invoice_product` ip,invoice i,customer c,product p,uom u,product_category pc,invoice_barcode ib WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND i.invoice_id = ib.invoice_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=0 GROUP BY i.invoice_id)UNION(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(rc.rawmaterial_category) as product_category,'' as hsn_code,GROUP_CONCAT(ru.rawmaterial_uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no,GROUP_CONCAT(ib.invoice_barcode) as barcode FROM `invoice_product` ip,invoice i,customer c,rawmaterial r,rawmaterial_uom ru,rawmaterial_category rc,invoice_barcode ib WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND ip.rawmaterial_id = r.rawmaterial_id AND ip.uom_id = ru.rawmaterial_uom_id AND r.rawmaterial_category_id = rc.rawmaterial_category_id AND i.invoice_id = ib.invoice_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=0 GROUP BY i.invoice_id) ORDER BY invoice_id ASC");
      $delivery_report_list = $resqry->result_array();
    }
    else
    {
      if($cat!='')
        $catqry = ' AND p.category_id='.$cat;
      else
        $catqry = '';

if($prod!='')
        $prodqry = ' AND p.product_id='.$prod;
      else
        $prodqry = '';

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

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

if($customer!='')
        $custqry = " AND c.customer_id='".$customer."'";
      else
        $custqry = '';

if($rmcat!='')
      {
        $rmcatqry = " AND r.rawmaterial_category_id='".$rmcat."'";
      }
      else
      {
        $rmcatqry = '';
      }

if($wid!='')
      {
        $whid = " AND i.warehouse_id='".$wid."'";
      }
      else
      {
        $whid = '';
      }

$resqry = $this->db->query("(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(pc.product_category) as product_category,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(u.uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no,GROUP_CONCAT(ib.invoice_barcode) as barcode FROM `invoice_product` ip,invoice i,`order` o,customer c,product p,uom u,product_category pc,invoice_barcode ib WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND i.invoice_id = ib.invoice_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=0 $catqry $prodqry $gsmqry $colorqry $custqry $unitqry $compqry $whid GROUP BY i.invoice_id)UNION(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(pc.product_category) as product_category,GROUP_CONCAT(pc.hsn_code) as hsn_code,GROUP_CONCAT(u.uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no,GROUP_CONCAT(ib.invoice_barcode) as barcode FROM `invoice_product` ip,invoice i,customer c,product p,uom u,product_category pc,invoice_barcode ib WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND i.invoice_id = ib.invoice_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=0 $catqry $prodqry $gsmqry $colorqry $custqry $dunitqry $dcompqry $whid GROUP BY i.invoice_id)UNION(SELECT i.invoice_id,i.invoice_no,i.invoice_date,c.customer_name,c.customer_id,GROUP_CONCAT(rc.rawmaterial_category) as product_category,'' as hsn_code,GROUP_CONCAT(ru.rawmaterial_uom) as uom,GROUP_CONCAT(ip.quantity) as prod_quantity,GROUP_CONCAT(ip.weight) as issue_weight,i.net_weight,i.gross_weight,i.grand_total,i.no_of_packets,i.transporter,i.destination_point,i.lr_rr_no,GROUP_CONCAT(ib.invoice_barcode) as barcode FROM `invoice_product` ip,invoice i,customer c,rawmaterial r,rawmaterial_uom ru,rawmaterial_category rc,invoice_barcode ib WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND ip.rawmaterial_id = r.rawmaterial_id AND ip.uom_id = ru.rawmaterial_uom_id AND r.rawmaterial_category_id = rc.rawmaterial_category_id AND i.invoice_id = ib.invoice_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.delivery_type=0 $rmcatqry $custqry $dunitqry $dcompqry $whid GROUP BY i.invoice_id) ORDER BY invoice_id ASC");

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

$g_settings = general_setting_details();

if($comp!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($comp);
      $locationName = $locationlist->location_name;
      $reptitle = 'Delivery 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 = 'Delivery 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:N1');
    $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', 'Customer Name');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Product');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'HSN Code');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'UOM');
    $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('G2', 'Qty');
    $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('H2', 'Issue Wt');
    $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('I2', 'Nett Wt');
    $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('J2', 'Gross Wt');
    $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('K2', 'Total Amount');
    $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('L2', 'No.of.Bundles');
    $objPHPExcel->getActiveSheet()->getStyle('L2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('M2', 'Destination Point');
    $objPHPExcel->getActiveSheet()->getStyle('M2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('N2', 'Barcode');
    $objPHPExcel->getActiveSheet()->getStyle('N2')->applyFromArray($styleArray);

$i=3;$nwgt=$gwgt=$gtot=0;
    foreach($delivery_report_list as $pplist){
      $hsn = explode(',', $pplist['hsn_code']);
      $hcode = '';
      for($j=0;$j<count($hsn);$j++)
      {
          if(($j+1)==count($hsn))
              $hcode.=$hsn[$j];
          else
              $hcode.=$hsn[$j].' , ';
      }

$bc = explode(',', $pplist['barcode']);
      $bcode = '';
      for($j=0;$j<count($bc);$j++)
      {
          if(($j+1)==count($bc))
              $bcode.=$bc[$j];
          else
              $bcode.=$bc[$j].' , ';
      }

$cusDetails = $this->Ledger_model->get_customer_contact_details($pplist['customer_id']);
      $phone_no = $cusDetails->phone_no;

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $pplist['invoice_no']);
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, date_format(date_create($pplist['invoice_date']),$g_settings->date_format));
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $pplist['customer_name'].' - '.$phone_no);
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $pplist['product_category']);
      $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $hcode);
      $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $uom);
      $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $prodqty);
      $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $issue_weight);
      $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, round($pplist['net_weight'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, round($pplist['gross_weight'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, round($pplist['grand_total'],2));
      $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, $pplist['no_of_packets']);
      $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, $pplist['destination_point']);
      $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, $bcode);

$i++;$nwgt+=$pplist['net_weight'];$gwgt+=$pplist['gross_weight'];$gtot+=$pplist['grand_total'];
    }

$filename='Delivery_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 getcustomerList(){
    // POST data
    $postData = $this->input->post();

// Get data
    $data = $this->Salesreport_model->getcustomerList($postData);

echo json_encode($data);
  }

public function getProduct()
  {
    $cat_id = $_POST['cat_id'];
    $product = $this->Quotation_model->get_product_by_category_id($cat_id);
    if(count($product)>0)
    {
      $st = '<option value="">Select Product</option>';
      foreach ($product as $prod) {
          $st.='<option value='.$prod["product_id"].'>'.$prod["product_name"].'</option>';
      }
    }
    else
    {
      $st = '<option value="">No Products Available</option>';
    }
    echo $st;
  }

public function index()
  {

//SELECT ip.*,i.invoice_no,i.invoice_date,c.customer_name,c.gst_no,ct.customer_type,p.product_name,u.uom FROM `invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,product p,uom u WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND i.invoice_date>=fdate AND i.invoice_date<=tdate GROUP BY ip.invoice_product_id ORDER BY i.invoice_id

$data['product_category_list'] = $this->Salesreport_model->get_product_category();
    $data['rawmaterial_category_list'] = $this->Salesreport_model->get_rawmaterialcategory_list();
    $data['gsm_list'] = $this->Salesreport_model->get_active_rawmaterial_gsm();
    $data['color_list'] = $this->Salesreport_model->get_active_rawmaterial_color();
    $data['customer_list'] = $this->Salesreport_model->get_customer_list();
    //$data['product_list'] = $this->Productionreport_model->get_product_list();
    //$data['location_list'] = $this->Salesreport_model->get_active_location();
    if($_SESSION['company_id']==0)
      $data['location_list'] = $this->Quotation_model->get_location();
    else
      $data['location_list'] = $this->Quotation_model->get_location_by_comp_id($_SESSION['company_id']);

$this->load->view('salesreport/sales_report',$data);
  }

public function sales_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'];
    $cat = $_POST['cat'];
    $prod = $_POST['prod'];
    $gsm = $_POST['gsm'];
    $color = $_POST['color'];
    $customer = $_POST['customer_id'];
    $unit = $_POST['prod_unit'];
    $comp = $_POST['comp'];
    $rmcat = $_POST['rmcat'];
    $wid = $_POST['wid'];

$this->session->set_userdata('salereportfdate', $_POST['fdate']!=''?$fdate:'');
    $this->session->set_userdata('salereporttdate', $_POST['tdate']!=''?$tdate:'');
    $this->session->set_userdata('salereportcat', $_POST['cat']!=''?$_POST['cat']:'');
    $this->session->set_userdata('salereportprod', $_POST['prod']!=''?$_POST['prod']:'');
    $this->session->set_userdata('salereportgsm', $_POST['gsm']!=''?$_POST['gsm']:'');
    $this->session->set_userdata('salereportcolor', $_POST['color']!=''?$_POST['color']:'');
    $this->session->set_userdata('salereportcustomer', $_POST['customer_id']!=''?$_POST['customer_id']:'');
    $this->session->set_userdata('salereportunit', $_POST['prod_unit']!=''?$_POST['prod_unit']:'');
    $this->session->set_userdata('salereportcomp', $_POST['comp']!=''?$_POST['comp']:'');
    $this->session->set_userdata('salereportrmcat', $_POST['rmcat']!=''?$_POST['rmcat']:'');
    $this->session->set_userdata('salereportrtype', $_POST['rtype']!=''?$_POST['rtype']:'');
    $this->session->set_userdata('salereportwid', $_POST['wid']!=''?$_POST['wid']:'');

if($rtype == 'sales report')
    {
      if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $unit=='' && $comp=='' && $rmcat=='' && $wid=='')
      {

$data['clist'] = $this->db->query("(SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id,sl.state,pc.hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id,sl.state,pc.hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.gst_no,ct.customer_type,r.rawmaterial,u.uom,c.state_id,sl.state,'' as hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,customer c,customer_type ct,rawmaterial r,uom u,state_list sl WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.rawmaterial_id = r.rawmaterial_id AND ip.uom_id = u.uom_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY ip.invoice_product_id)UNION(SELECT ip.*,i.invoice_no,i.invoice_date,'' as net_weight,c.customer_name,c.gst_no,ct.customer_type,'' as rawmaterial,'' as uom,c.state_id,sl.state,'' as hsn_code,cdn.amount as cdnamt FROM credit_debit_note cdn ,`invoice_product` ip,invoice i,customer c,customer_type ct,state_list sl WHERE cdn.type_id = i.invoice_id AND ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND c.state_id = sl.state_id AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND cdn.credit_debit = 'Credit' GROUP BY cdn.credit_debit_note_id)UNION(SELECT ip.*,i.invoice_no,i.invoice_date,'' as net_weight,c.customer_name,c.gst_no,ct.customer_type,'' as rawmaterial,'' as uom,c.state_id,sl.state,'' as hsn_code,cdn.amount as cdnamt FROM credit_debit_note cdn ,`invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,state_list sl WHERE cdn.type_id = i.invoice_id AND ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND c.state_id = sl.state_id AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND cdn.credit_debit = 'Credit' GROUP BY cdn.credit_debit_note_id) ORDER BY invoice_id ASC")->result_array();

$config["total_rows"] = count($data['clist']);

// Initialize pagination library 
        $this->ajax_pagination->initialize($config);

$resqry = $this->db->query("(SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id,sl.state,pc.hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id,sl.state,pc.hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,r.rawmaterial,u.uom,c.state_id,sl.state,'' as hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,customer c,customer_type ct,rawmaterial r,uom u,state_list sl WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.rawmaterial_id = r.rawmaterial_id AND ip.uom_id = u.uom_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' GROUP BY ip.invoice_product_id)UNION(SELECT ip.*,i.invoice_no,i.invoice_date,'' as net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,'' as rawmaterial,'' as uom,c.state_id,sl.state,'' as hsn_code,cdn.amount as cdnamt FROM credit_debit_note cdn ,`invoice_product` ip,invoice i,customer c,customer_type ct,state_list sl WHERE cdn.type_id = i.invoice_id AND ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND c.state_id = sl.state_id AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND cdn.credit_debit = 'Credit' GROUP BY cdn.credit_debit_note_id)UNION(SELECT ip.*,i.invoice_no,i.invoice_date,'' as net_weight,c.customer_name,c.customer_id,c.gst_no,ct.customer_type,'' as rawmaterial,'' as uom,c.state_id,sl.state,'' as hsn_code,cdn.amount as cdnamt FROM credit_debit_note cdn ,`invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,state_list sl WHERE cdn.type_id = i.invoice_id AND ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND c.state_id = sl.state_id AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND cdn.credit_debit = 'Credit' GROUP BY cdn.credit_debit_note_id) ORDER BY invoice_id ASC LIMIT 10");
        $data['sales_report_list'] = $resqry->result_array();
      }
      else
      {
        if($cat!='')
          $catqry = ' AND p.category_id='.$cat;
        else
          $catqry = '';

if($prod!='')
          $prodqry = ' AND p.product_id='.$prod;
        else
          $prodqry = '';

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

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

if($customer!='')
          $custqry = " AND c.customer_id='".$customer."'";
        else
          $custqry = '';

if($unit!='')
        {
          $unitqry = " AND o.prod_unit='".$unit."'";
          $dunitqry = " AND i.prod_unit='".$unit."'";
        }
        else
        {
          $unitqry = '';
          $dunitqry = '';
        }

if($comp!='')
        {
          $compqry = " AND o.location_id='".$comp."'";
          $dcompqry = " AND i.location_id='".$comp."'";
        }
        else
        {
          $compqry = '';
          $dcompqry = '';
        }

if($rmcat!='')
        {
          $rmcatqry = " AND r.rawmaterial_category_id='".$rmcat."'";
        }
        else
        {
          $rmcatqry = '';
        }

if($wid!='')
        {
          $whid = " AND i.warehouse_id='".$wid."'";
        }
        else
        {
          $whid = '';
        }

$data['clist'] = $this->db->query("(SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id,sl.state,pc.hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $catqry $prodqry $gsmqry $colorqry $custqry $unitqry $compqry $whid GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.gst_no,ct.customer_type,p.product_name,u.uom,c.state_id,sl.state,pc.hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,customer c,customer_type ct,product p,uom u,state_list sl,product_category pc WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.product_id = p.product_id AND ip.uom_id = u.uom_id AND p.category_id = pc.product_category_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $catqry $prodqry $gsmqry $colorqry $custqry $dunitqry $dcompqry $whid GROUP BY ip.invoice_product_id) UNION (SELECT ip.*,i.invoice_no,i.invoice_date,i.net_weight,c.customer_name,c.gst_no,ct.customer_type,r.rawmaterial,u.uom,c.state_id,sl.state,'' as hsn_code,'' as cdnamt FROM `invoice_product` ip,invoice i,customer c,customer_type ct,rawmaterial r,rawmaterial_category rc,uom u,state_list sl WHERE ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND ip.rawmaterial_id = r.rawmaterial_id AND r.rawmaterial_category_id = rc.rawmaterial_category_id AND ip.uom_id = u.uom_id AND c.state_id = sl.state_id AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' $rmcatqry $custqry $dunitqry $dcompqry $whid GROUP BY ip.invoice_product_id)UNION(SELECT ip.*,i.invoice_no,i.invoice_date,'' as net_weight,c.customer_name,c.gst_no,ct.customer_type,'' as rawmaterial,'' as uom,c.state_id,sl.state,'' as hsn_code,cdn.amount as cdnamt FROM credit_debit_note cdn ,`invoice_product` ip,invoice i,customer c,customer_type ct,state_list sl WHERE cdn.type_id = i.invoice_id AND ip.invoice_id = i.invoice_id AND i.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND c.state_id = sl.state_id AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND cdn.credit_debit = 'Credit' $dcompqry $whid GROUP BY cdn.credit_debit_note_id)UNION(SELECT ip.*,i.invoice_no,i.invoice_date,'' as net_weight,c.customer_name,c.gst_no,ct.customer_type,'' as rawmaterial,'' as uom,c.state_id,sl.state,'' as hsn_code,cdn.amount as cdnamt FROM credit_debit_note cdn ,`invoice_product` ip,invoice i,`order` o,customer c,customer_type ct,state_list sl WHERE cdn.type_id = i.invoice_id AND ip.invoice_id = i.invoice_id AND i.order_id = o.order_id AND o.customer_id = c.customer_id AND c.customer_type=ct.customer_type_id AND c.state_id = sl.state_id AND cdn.credit_debit_note_date>='$fdate' AND cdn.credit_debit_note_date<='$tdate' AND cdn.credit_debit = 'Credit' $compqry $whid GROUP BY cdn.credit_debit_note_id) ORDER BY invoice_id ASC")->result_array();

$config["total_rows"] = count($data['clist']);

// Initialize pagination library 
        $this->ajax_pagination->initialize($config);

$data['sales_report_list'] = $resqry->result_array();
      }
      $this->load->view('salesreport/sales_report_table',$data);
    }

if($rtype == 'sales register')
    {
      if($cat=='' && $prod=='' && $gsm=='' && $color=='' && $customer=='' && $unit=='' && $comp=='' && $wid=='')
      {
        $data['invoice_list'] = $this->Salesreport_model->get_basic_sales_register_invoice_list($fdate,$tdate);
        $data['sales_register_list'] = $this->Salesreport_model->get_basic_sales_report_list($fdate,$tdate);
      }
      else
      {
        if($cat!='')
          $catqry = ' AND p.category_id='.$cat;
        else
          $catqry = '';