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

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

public function stock_ledger_export()
  {
    $location_id = isset($_SESSION['stkreportlocation_id'])?$_SESSION['stkreportlocation_id']:'';
    $gsm = isset($_SESSION['stkreportgsm'])?$_SESSION['stkreportgsm']:'';
    $color = isset($_SESSION['stkreportcolor'])?$_SESSION['stkreportcolor']:'';
    $rmid = isset($_SESSION['stkreportrmid'])?$_SESSION['stkreportrmid']:'';
    $typ = isset($_SESSION['stkreporttyp'])?$_SESSION['stkreporttyp']:'';
    $tpid = isset($_SESSION['stkreporttpid'])?$_SESSION['stkreporttpid']:'';
    $spid = isset($_SESSION['stkreportspid'])?$_SESSION['stkreportspid']:'';
    $ormid = isset($_SESSION['stkreportormid'])?$_SESSION['stkreportormid']:'';
    $otyp = isset($_SESSION['stkreportotyp'])?$_SESSION['stkreportotyp']:'';
    $otpid = isset($_SESSION['stkreportotpid'])?$_SESSION['stkreportotpid']:'';
    $ospid = isset($_SESSION['stkreportospid'])?$_SESSION['stkreportospid']:'';
    $sldlocation_id = isset($_SESSION['stkreportsldlocation_id'])?$_SESSION['stkreportsldlocation_id']:'';

if($otyp=='raw material')
    {
      if($ormid!='')
        $prodqry = ' AND r.rawmaterial_id='.$ormid;
      else
        $prodqry = '';

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

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

if($location_id!='')
        $custqry = " AND l.location_id='".$location_id."'";
      else
        $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,r.rawmaterial,sum(rs.stock) as stock,rs.location_id,rs.rawmaterial_id,ruom.rawmaterial_uom FROM `rawmaterial_stock` rs,location l,rawmaterial r, rawmaterial_uom ruom WHERE l.location_id = rs.location_id AND r.rawmaterial_id = rs.rawmaterial_id AND r.rawmaterial_uom_id = ruom.rawmaterial_uom_id $prodqry $gsmqry $colorqry $custqry GROUP BY rs.rawmaterial_id,rs.location_id) UNION (SELECT l.location_name,w.warehouse_name,r.rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.rawmaterial_id,ruom.rawmaterial_uom FROM `warehouse_stock` ws, warehouse w,location l,rawmaterial r, rawmaterial_uom ruom WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND r.rawmaterial_id = ws.rawmaterial_id AND r.rawmaterial_uom_id = ruom.rawmaterial_uom_id $prodqry $gsmqry $colorqry $custqry GROUP BY ws.rawmaterial_id,ws.warehouse_id,ws.location_id) ORDER BY rawmaterial DESC");
      
      $stock_ledger_list = $resqry->result_array();
      $otyp = $otyp;
    }

if($otyp=='trading product')
    {
      if($otpid!='')
        $prodqry = ' AND p.product_id='.$otpid;
      else
        $prodqry = '';

if($location_id!='')
        $custqry = " AND l.location_id='".$location_id."'";
      else
        $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,p.product_name as rawmaterial,sum(ps.stock) as stock,ps.location_id,ps.product_id,u.uom as rawmaterial_uom FROM `product_stock` ps,location l,product p, uom u WHERE l.location_id = ps.location_id AND p.product_id = ps.product_id AND p.uom_id = u.uom_id AND p.is_purchase = 1 $prodqry $custqry GROUP BY ps.product_id,ps.location_id) UNION (SELECT l.location_name,w.warehouse_name,p.product_name as rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.product_id,u.uom as rawmaterial_uom FROM `warehouse_stock` ws, warehouse w,location l,product p, uom u WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND p.product_id = ws.product_id AND p.uom_id = u.uom_id AND p.is_purchase = 1 $prodqry $custqry GROUP BY ws.product_id,ws.warehouse_id,ws.location_id) ORDER BY rawmaterial DESC");
      
      $stock_ledger_list = $resqry->result_array();
      $otyp = $otyp;
    }

if($otyp=='standard product')
    {
      if($ospid!='')
        $prodqry = ' AND p.product_id='.$ospid;
      else
        $prodqry = '';

if($location_id!='')
        $custqry = " AND l.location_id='".$location_id."'";
      else
        $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,p.product_name as rawmaterial,sum(ps.stock) as stock,ps.location_id,ps.product_id,u.uom as rawmaterial_uom FROM `product_stock` ps,location l,product p, uom u WHERE l.location_id = ps.location_id AND p.product_id = ps.product_id AND p.uom_id = u.uom_id AND p.type_id = 1 and p.is_purchase = 0 $prodqry $custqry GROUP BY ps.product_id,ps.location_id) UNION (SELECT l.location_name,w.warehouse_name,p.product_name as rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.product_id,u.uom as rawmaterial_uom FROM `warehouse_stock` ws, warehouse w,location l,product p, uom u WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND p.product_id = ws.product_id AND p.uom_id = u.uom_id AND p.type_id = 1 and p.is_purchase = 0 $prodqry $custqry GROUP BY ws.product_id,ws.warehouse_id,ws.location_id) ORDER BY rawmaterial DESC");
      
      $stock_ledger_list = $resqry->result_array();
      $otyp = $otyp;
    }

$g_settings = general_setting_details();

if($location_id!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($location_id);
      $locationName = $locationlist->location_name;
      $reptitle = 'Inventory Status Report For '.$locationName;
    }
    else
    {
      $locationName = '';
      $reptitle = 'Inventory Status Report for All Company';
    }

$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:E1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $reptitle);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Company');
    $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Type');
    $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
    if($otyp=='raw material'){
      $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Raw Material');
      $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    }
    else
    {
      $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Product');
      $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    }
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'UOM');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Stock');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);

$i=3;
    foreach($stock_ledger_list as $pplist){
      $name = $pplist['location_name'];
      $wname = $pplist['warehouse_name']!=''?' / '.$pplist['warehouse_name']:'';
      $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $name.''.$wname);
      if($otyp=='raw material'){
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, 'Raw Material');
      }
      else if($otyp=='trading product'){
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, 'Trading Product');
      }
      else{
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, 'Standard Product');
      }
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $pplist['rawmaterial']);
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $pplist['rawmaterial_uom']);
      $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, round($pplist['stock'],2));
      
      $i++;
    }

$filename='Inventory_Status.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 stock_ledger_details_export()
  {
    $location_id = isset($_SESSION['stkreportlocation_id'])?$_SESSION['stkreportlocation_id']:'';
    $gsm = isset($_SESSION['stkreportgsm'])?$_SESSION['stkreportgsm']:'';
    $color = isset($_SESSION['stkreportcolor'])?$_SESSION['stkreportcolor']:'';
    $rmid = isset($_SESSION['stkreportrmid'])?$_SESSION['stkreportrmid']:'';
    $typ = isset($_SESSION['stkreporttyp'])?$_SESSION['stkreporttyp']:'';
    $tpid = isset($_SESSION['stkreporttpid'])?$_SESSION['stkreporttpid']:'';
    $spid = isset($_SESSION['stkreportspid'])?$_SESSION['stkreportspid']:'';
    $ormid = isset($_SESSION['stkreportormid'])?$_SESSION['stkreportormid']:'';
    $otyp = isset($_SESSION['stkreportotyp'])?$_SESSION['stkreportotyp']:'';
    $otpid = isset($_SESSION['stkreportotpid'])?$_SESSION['stkreportotpid']:'';
    $ospid = isset($_SESSION['stkreportospid'])?$_SESSION['stkreportospid']:'';
    $sldlocation_id = isset($_SESSION['stkreportsldlocation_id'])?$_SESSION['stkreportsldlocation_id']:'';

$fdate = isset($_SESSION['stkreportfdate'])?$_SESSION['stkreportfdate']:'';
    $tdate = isset($_SESSION['stkreporttdate'])?$_SESSION['stkreporttdate']:'';

$pDay = new DateTime(date($fdate).' - 1 day');
    $pdate = $pDay->format('Y-m-d');
    $psdate = '2019-04-01';
    $fdate = $fdate;

if($typ=='raw material')
    {
      if($sldlocation_id!='')
      {
        if($fdate>'2019-04-01')
        {
          $prevresqry = $this->db->query("SELECT rm_date,sum(net_weight) as qty, 'inword' as type, '' as particulars,'' as order_no,'' as invoice_no FROM `rawmaterial_stock` WHERE rawmaterial_id='$rmid' AND rm_date>='$psdate' AND rm_date<='$pdate' AND location_id = '$sldlocation_id' GROUP BY rm_date UNION SELECT wl.work_log_date as rm_date,sum(wlp.weight) as qty, 'outword' as type, wl.work_log_no as particulars,o.order_no,i.invoice_no FROM `work_log_product` wlp INNER JOIN work_log wl ON wlp.work_log_id = wl.work_log_id INNER JOIN work_order wo ON wl.work_order_id = wo.work_order_id INNER JOIN `order` o ON wo.order_id = o.order_id LEFT JOIN invoice i ON o.order_id = i.order_id WHERE wlp.rawmaterial_id='$rmid' AND wl.work_log_date>='$psdate' AND wl.work_log_date<='$pdate' AND o.location_id = '$sldlocation_id' GROUP BY wlp.work_log_product_id UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.rawmaterial_id='$rmid' AND i.invoice_date>='$psdate' AND i.invoice_date<='$pdate' AND i.location_id = '$sldlocation_id' GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
      
          $prev_stock_ledger_details = $prevresqry->result_array();
        }
        $resqry = $this->db->query("SELECT rm_date,sum(net_weight) as qty, 'inword' as type, '' as particulars,'' as order_no,'' as invoice_no FROM `rawmaterial_stock` WHERE rawmaterial_id='$rmid' AND rm_date>='$fdate' AND rm_date<='$tdate' AND location_id = '$sldlocation_id' GROUP BY rm_date UNION SELECT wl.work_log_date as rm_date,sum(wlp.weight) as qty, 'outword' as type, wl.work_log_no as particulars,o.order_no,i.invoice_no FROM `work_log_product` wlp INNER JOIN work_log wl ON wlp.work_log_id = wl.work_log_id INNER JOIN work_order wo ON wl.work_order_id = wo.work_order_id INNER JOIN `order` o ON wo.order_id = o.order_id LEFT JOIN invoice i ON o.order_id = i.order_id WHERE wlp.rawmaterial_id='$rmid' AND wl.work_log_date>='$fdate' AND wl.work_log_date<='$tdate' AND o.location_id = '$sldlocation_id' GROUP BY wlp.work_log_product_id UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.rawmaterial_id='$rmid' AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.location_id = '$sldlocation_id' GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
      
        $stock_ledger_details = $resqry->result_array();
      }
      else
      {
        if($fdate>'2019-04-01')
        {
          $prev_stock_ledger_details = $this->Stockreport_model->get_stock_ledger_details_report_by_rmid($rmid,$psdate,$pdate);
        }
        $stock_ledger_details = $this->Stockreport_model->get_stock_ledger_details_report_by_rmid($rmid,$fdate,$tdate);
      }
    }
    if($typ=='trading product')
    {
      if($sldlocation_id!='')
      {
        if($fdate>'2019-04-01')
        {
          $prevresqry = $this->db->query("SELECT p.purchase_date as rm_date,sum(pp.quantity) as qty, 'inword' as type, '' as particulars,'' as order_no,'' as invoice_no FROM `purchase` p,purchase_product pp, grn g WHERE p.purchase_id = pp.purchase_id AND pp.product_id='$tpid' AND p.grn_id = g.grn_id AND p.status=1 AND p.purchase_date>='$psdate' AND p.purchase_date<='$pdate' AND g.location_id = '$sldlocation_id' GROUP BY p.purchase_date UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.product_id='$tpid' AND i.invoice_date>='$psdate' AND i.invoice_date<='$pdate' AND i.location_id = '$sldlocation_id' AND i.order_id = 0 GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
      
          $prev_stock_ledger_details = $prevresqry->result_array();
        }

$resqry = $this->db->query("SELECT p.purchase_date as rm_date,sum(pp.quantity) as qty, 'inword' as type, '' as particulars,'' as order_no,'' as invoice_no FROM `purchase` p,purchase_product pp, grn g WHERE p.purchase_id = pp.purchase_id AND pp.product_id='$tpid' AND p.grn_id = g.grn_id AND p.status=1 AND p.purchase_date>='$fdate' AND p.purchase_date<='$tdate' AND g.location_id = '$sldlocation_id' GROUP BY p.purchase_date UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.product_id='$tpid' AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.location_id = '$sldlocation_id' AND i.order_id = 0 GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
      
        $stock_ledger_details = $resqry->result_array();
      }
      else
      {
        if($fdate>'2019-04-01')
        {
          $prev_stock_ledger_details = $this->Stockreport_model->get_stock_ledger_details_report_by_prodid($tpid,$psdate,$pdate);
        }
        $stock_ledger_details = $this->Stockreport_model->get_stock_ledger_details_report_by_prodid($tpid,$fdate,$tdate);
      }
    }
    if($typ=='standard product')
    {
      if($sldlocation_id!='')
      {
        if($fdate>'2019-04-01')
        {
          $prevresqry = $this->db->query("SELECT dc.delivery_challan_date as rm_date,sum(dcp.quantity) as qty, 'inword' as type, '' as particulars, '' as order_no, '' as invoice_no FROM delivery_challan dc, delivery_challan_product dcp, `order` o, customer c WHERE dc.delivery_challan_id = dcp.delivery_challan_id AND dc.order_id = o.order_id AND o.customer_id = c.customer_id AND dcp.product_id = '$spid' AND c.is_self = 1 AND o.location_id = '$sldlocation_id' AND dc.delivery_challan_date>='$psdate' AND dc.delivery_challan_date<='$pdate' UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.product_id='$spid' AND i.invoice_date>='$psdate' ANd i.invoice_date<='$pdate' AND i.location_id = '$sldlocation_id' AND i.order_id = 0 GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
      
          $prev_stock_ledger_details = $prevresqry->result_array();
        }

$resqry = $this->db->query("SELECT dc.delivery_challan_date as rm_date,sum(dcp.quantity) as qty, 'inword' as type, '' as particulars, '' as order_no, '' as invoice_no FROM delivery_challan dc, delivery_challan_product dcp, `order` o, customer c WHERE dc.delivery_challan_id = dcp.delivery_challan_id AND dc.order_id = o.order_id AND o.customer_id = c.customer_id AND dcp.product_id = '$spid' AND c.is_self = 1 AND o.location_id = '$sldlocation_id' AND dc.delivery_challan_date>='$fdate' AND dc.delivery_challan_date<='$tdate' UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.product_id='$spid' AND i.invoice_date>='$fdate' ANd i.invoice_date<='$tdate' AND i.location_id = '$sldlocation_id' AND i.order_id = 0 GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
      
        $stock_ledger_details = $resqry->result_array();
      }
      else
      {
        if($fdate>'2019-04-01')
        {
          $prev_stock_ledger_details = $this->Stockreport_model->get_stock_ledger_details_report_by_sprodid($spid,$psdate,$pdate);
        }
        $stock_ledger_details = $this->Stockreport_model->get_stock_ledger_details_report_by_sprodid($spid,$fdate,$tdate);
      }
    }

$g_settings = general_setting_details();

if($sldlocation_id!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($sldlocation_id);
      $locationName = $locationlist->location_name;
      $reptitle = 'Stock Ledger Details 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 = 'Stock Ledger Details 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:E1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $reptitle);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Date');
    $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Particulars');
    $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Inward');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Outward');
    $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Balance');
    $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleArray);

$i=3;$pinw=$poutw=0;$bal=0;
    if(isset($prev_stock_ledger_details)){
      if(count($prev_stock_ledger_details>0)){
        foreach($prev_stock_ledger_details as $opdet)
        {
            if($opdet['type']=='inword')
            {
                $pinw+= $opdet['qty'];
            }
            else
            {
                $poutw+= $opdet['qty'];
            }
         }
        $bal = $pinw-$poutw;

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, date_format(date_create($fdate),$g_settings->date_format));
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, 'Opening Balance');
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, round($pinw,2));
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, round($poutw,2));
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, round($bal,2));

$i++;
      }
    }

foreach($stock_ledger_details as $pplist){
      if($pplist['type']=='inword')
      {
          $bal+=$pplist['qty'];
      }
      else
      {
          $bal-=$pplist['qty'];
      }

if($pplist['order_no']!='')
      {
          $ono = 'SO No -> '.$pplist['order_no'];
          $ttiono = $ono;
      }
      else
      {
          $ono = '';
          $ttiono = '';
      }

if($pplist['invoice_no']!='' || $pplist['invoice_no']!=NULL)
      {
          $inv = 'Invoice No -> '.$pplist['invoice_no'];
          $ttiono.=' ; '.$inv;
      }
      else
      {
          $inv = '';
          if($ttiono=='')
              $ttiono = '';
      }

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, date_format(date_create($pplist['rm_date']),$g_settings->date_format));
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $pplist['particulars']);
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $pplist['type']=='inword'?round($pplist['qty'],2):'');
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $pplist['type']=='outword'?round($pplist['qty'],2):'');
      $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, round($bal,2));

$i++;
    }

$filename='Stock_Ledger_Details.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 zero_stock_export()
  {
    $location_id = isset($_SESSION['stkreportlocation_id'])?$_SESSION['stkreportlocation_id']:'';
    $gsm = isset($_SESSION['stkreportgsm'])?$_SESSION['stkreportgsm']:'';
    $color = isset($_SESSION['stkreportcolor'])?$_SESSION['stkreportcolor']:'';
    $rmid = isset($_SESSION['stkreportrmid'])?$_SESSION['stkreportrmid']:'';
    $typ = isset($_SESSION['stkreporttyp'])?$_SESSION['stkreporttyp']:'';
    $tpid = isset($_SESSION['stkreporttpid'])?$_SESSION['stkreporttpid']:'';
    $spid = isset($_SESSION['stkreportspid'])?$_SESSION['stkreportspid']:'';
    $ormid = isset($_SESSION['stkreportormid'])?$_SESSION['stkreportormid']:'';
    $otyp = isset($_SESSION['stkreportotyp'])?$_SESSION['stkreportotyp']:'';
    $otpid = isset($_SESSION['stkreportotpid'])?$_SESSION['stkreportotpid']:'';
    $ospid = isset($_SESSION['stkreportospid'])?$_SESSION['stkreportospid']:'';
    $sldlocation_id = isset($_SESSION['stkreportsldlocation_id'])?$_SESSION['stkreportsldlocation_id']:'';

if($otyp=='raw material')
    {
      if($ormid!='')
        $prodqry = ' AND r.rawmaterial_id='.$ormid;
      else
        $prodqry = '';

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

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

if($location_id!='')
        $custqry = " AND l.location_id='".$location_id."'";
      else
        $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,r.rawmaterial,sum(rs.stock) as stock,rs.location_id,rs.rawmaterial_id,r.low_stock FROM `rawmaterial_stock` rs,location l,rawmaterial r, rawmaterial_uom ruom WHERE l.location_id = rs.location_id AND r.rawmaterial_id = rs.rawmaterial_id AND r.rawmaterial_uom_id = ruom.rawmaterial_uom_id $prodqry $gsmqry $colorqry $custqry GROUP BY rs.rawmaterial_id,rs.location_id HAVING stock<r.low_stock) UNION (SELECT l.location_name,w.warehouse_name,r.rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.rawmaterial_id,r.low_stock FROM `warehouse_stock` ws, warehouse w,location l,rawmaterial r, rawmaterial_uom ruom WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND r.rawmaterial_id = ws.rawmaterial_id AND r.rawmaterial_uom_id = ruom.rawmaterial_uom_id $prodqry $gsmqry $colorqry $custqry GROUP BY ws.rawmaterial_id,ws.warehouse_id,ws.location_id HAVING stock<r.low_stock) ORDER BY rawmaterial DESC");
      
      $zero_stock_list = $resqry->result_array();
      $otyp = $otyp;
    }

if($otyp=='trading product')
    {
      if($otpid!='')
        $prodqry = ' AND p.product_id='.$otpid;
      else
        $prodqry = '';

if($location_id!='')
        $custqry = " AND l.location_id='".$location_id."'";
      else
        $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,p.product_name as rawmaterial,sum(ps.stock) as stock,ps.location_id,ps.product_id,p.min_stock_qty FROM `product_stock` ps,location l,product p, uom u WHERE l.location_id = ps.location_id AND p.product_id = ps.product_id AND p.uom_id = u.uom_id AND p.is_purchase = 1 $prodqry $custqry GROUP BY ps.product_id,ps.location_id HAVING stock<p.min_stock_qty) UNION (SELECT l.location_name,w.warehouse_name,p.product_name as rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.product_id,p.min_stock_qty as rawmaterial_uom FROM `warehouse_stock` ws, warehouse w,location l,product p, uom u WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND p.product_id = ws.product_id AND p.uom_id = u.uom_id AND p.is_purchase = 1 $prodqry $custqry GROUP BY ws.product_id,ws.warehouse_id,ws.location_id HAVING stock<p.min_stock_qty) ORDER BY rawmaterial DESC");
      
      $zero_stock_list = $resqry->result_array();
      $otyp = $otyp;
    }

if($otyp=='standard product')
    {
      if($ospid!='')
        $prodqry = ' AND p.product_id='.$ospid;
      else
        $prodqry = '';

if($location_id!='')
        $custqry = " AND l.location_id='".$location_id."'";
      else
        $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,p.product_name as rawmaterial,sum(ps.stock) as stock,ps.location_id,ps.product_id,p.min_stock_qty FROM `product_stock` ps,location l,product p, uom u WHERE l.location_id = ps.location_id AND p.product_id = ps.product_id AND p.uom_id = u.uom_id AND p.type_id = 1 AND p.is_purchase = 0 $prodqry $custqry GROUP BY ps.product_id,ps.location_id HAVING stock<p.min_stock_qty) UNION (SELECT l.location_name,w.warehouse_name,p.product_name as rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.product_id,p.min_stock_qty as rawmaterial_uom FROM `warehouse_stock` ws, warehouse w,location l,product p, uom u WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND p.product_id = ws.product_id AND p.uom_id = u.uom_id AND p.type_id = 1 AND p.is_purchase = 0 $prodqry $custqry GROUP BY ws.product_id,ws.warehouse_id,ws.location_id HAVING stock<p.min_stock_qty) ORDER BY rawmaterial DESC");
      
      $zero_stock_list = $resqry->result_array();
      $otyp = $otyp;
    }

$g_settings = general_setting_details();

if($location_id!='')
    {
      $locationlist = $this->Invoice_model->get_location_by_id($location_id);
      $locationName = $locationlist->location_name;
      $reptitle = 'Zero Stock Report For '.$locationName;
    }
    else
    {
      $locationName = '';
      $reptitle = 'Zero Stock Report for All Company';
    }

$objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
    $objPHPExcel->getActiveSheet()->setCellValue('A1', $reptitle);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Company');
    $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
    if($otyp=='raw material'){
      $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Raw Material');
      $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
    }
    else
    {
      $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Product');
      $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray);
    }
    $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Stock');
    $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray);

$i=3;

foreach($zero_stock_list as $pplist){
      $name = $pplist['location_name'];
      $wname = $pplist['warehouse_name']!=''?' / '.$pplist['warehouse_name']:'';

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $name.''.$wname);
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $pplist['rawmaterial']);
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, round($pplist['stock'],2));

$i++;
    }

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

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

}

public function getRawmaterial()
  {
    $cat_id = $_POST['cat_id'];
    $product = $this->Stockreport_model->get_rawmaterial_by_category_id($cat_id);
    if(count($product)>0)
    {
      $st = '<option value="">Select Raw Material</option>';
      foreach ($product as $prod) {
          $st.='<option value='.$prod["rawmaterial_id"].'>'.$prod["rawmaterial"].'</option>';
      }
    }
    else
    {
      $st = '<option value="">No Raw Material Available</option>';
    }
    echo $st;
  }

public function index()
  {
    $data['rm_category_list'] = $this->Stockreport_model->get_rawmaterial_category();
    $data['rm_gsm_list'] = $this->Stockreport_model->get_active_rawmaterial_gsm();
    $data['rm_color_list'] = $this->Stockreport_model->get_active_rawmaterial_color();
    $data['rawmaterial_list'] = $this->Stockreport_model->get_active_rawmaterials();
    $data['trading_product'] = $this->Stockreport_model->get_active_trading_products();
    $data['standard_product'] = $this->Stockreport_model->get_active_standard_products();
    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('stockreport/stock_report',$data);
  }

public function stock_report()
  {
    $rtype = $_POST['rtype'];
    $location_id = $_POST['location_id'];
    $gsm = $_POST['gsm'];
    $color = $_POST['color'];
    $rmid = $_POST['rmid'];
    $typ = $_POST['typ'];
    $tpid = $_POST['tpid'];
    $spid = $_POST['spid'];
    $ormid = $_POST['ormid'];
    $otyp = $_POST['otyp'];
    $otpid = $_POST['otpid'];
    $ospid = $_POST['ospid'];
    $sldlocation_id = $_POST['sldlocation_id'];

$this->session->set_userdata('stkreportlocation_id', $_POST['location_id']!=''?$_POST['location_id']:'');
    $this->session->set_userdata('stkreportgsm', $_POST['gsm']!=''?$_POST['gsm']:'');
    $this->session->set_userdata('stkreportcolor', $_POST['color']!=''?$_POST['color']:'');
    $this->session->set_userdata('stkreportrmid', $_POST['rmid']!=''?$_POST['rmid']:'');
    $this->session->set_userdata('stkreporttyp', $_POST['typ']!=''?$_POST['typ']:'');
    $this->session->set_userdata('stkreporttpid', $_POST['tpid']!=''?$_POST['tpid']:'');
    $this->session->set_userdata('stkreportspid', $_POST['spid']!=''?$_POST['spid']:'');
    $this->session->set_userdata('stkreportormid', $_POST['ormid']!=''?$_POST['ormid']:'');
    $this->session->set_userdata('stkreportotyp', $_POST['otyp']!=''?$_POST['otyp']:'');
    $this->session->set_userdata('stkreportotpid', $_POST['otpid']!=''?$_POST['otpid']:'');
    $this->session->set_userdata('stkreportospid', $_POST['ospid']!=''?$_POST['ospid']:'');
    $this->session->set_userdata('stkreportsldlocation_id', $_POST['sldlocation_id']!=''?$_POST['sldlocation_id']:'');

if($rtype == 'stock ledger details')
    {

$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];
      $pDay = new DateTime(date($fdate).' - 1 day');
      $pdate = $pDay->format('Y-m-d');
      $psdate = '2019-04-01';
      $data['fdate'] = $fdate;

$this->session->set_userdata('stkreportfdate', $_POST['fdate']!=''?$fdate:'');
      $this->session->set_userdata('stkreporttdate', $_POST['tdate']!=''?$tdate:'');

/*if($sldlocation_id!='' && $sldlocation_id != 'Warehouse')
      {
        $custqry = " AND l.location_id='".$sldlocation_id."'";
      }
      else if($sldlocation_id!='' && $sldlocation_id == 'Warehouse')
      {

}
      else
      {
        $custqry = '';
      }*/

if($typ=='raw material')
      {
        if($sldlocation_id!='')
        {
          if($fdate>'2019-04-01')
          {
            $prevresqry = $this->db->query("SELECT rm_date,sum(net_weight) as qty, 'inword' as type, '' as particulars,'' as order_no,'' as invoice_no FROM `rawmaterial_stock` WHERE rawmaterial_id='$rmid' AND rm_date>='$psdate' AND rm_date<='$pdate' AND location_id = '$sldlocation_id' GROUP BY rm_date UNION SELECT wl.work_log_date as rm_date,sum(wlp.weight) as qty, 'outword' as type, wl.work_log_no as particulars,o.order_no,i.invoice_no FROM `work_log_product` wlp INNER JOIN work_log wl ON wlp.work_log_id = wl.work_log_id INNER JOIN work_order wo ON wl.work_order_id = wo.work_order_id INNER JOIN `order` o ON wo.order_id = o.order_id LEFT JOIN invoice i ON o.order_id = i.order_id WHERE wlp.rawmaterial_id='$rmid' AND wl.work_log_date>='$psdate' AND wl.work_log_date<='$pdate' AND o.location_id = '$sldlocation_id' GROUP BY wlp.work_log_product_id UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.rawmaterial_id='$rmid' AND i.invoice_date>='$psdate' AND i.invoice_date<='$pdate' AND i.location_id = '$sldlocation_id' GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
        
            $data['prev_stock_ledger_details'] = $prevresqry->result_array();
          }
          $resqry = $this->db->query("SELECT rm_date,sum(net_weight) as qty, 'inword' as type, '' as particulars,'' as order_no,'' as invoice_no FROM `rawmaterial_stock` WHERE rawmaterial_id='$rmid' AND rm_date>='$fdate' AND rm_date<='$tdate' AND location_id = '$sldlocation_id' GROUP BY rm_date UNION SELECT wl.work_log_date as rm_date,sum(wlp.weight) as qty, 'outword' as type, wl.work_log_no as particulars,o.order_no,i.invoice_no FROM `work_log_product` wlp INNER JOIN work_log wl ON wlp.work_log_id = wl.work_log_id INNER JOIN work_order wo ON wl.work_order_id = wo.work_order_id INNER JOIN `order` o ON wo.order_id = o.order_id LEFT JOIN invoice i ON o.order_id = i.order_id WHERE wlp.rawmaterial_id='$rmid' AND wl.work_log_date>='$fdate' AND wl.work_log_date<='$tdate' AND o.location_id = '$sldlocation_id' GROUP BY wlp.work_log_product_id UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.rawmaterial_id='$rmid' AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.location_id = '$sldlocation_id' GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
        
          $data['stock_ledger_details'] = $resqry->result_array();
        }
        else
        {
          if($fdate>'2019-04-01')
          {
            $data['prev_stock_ledger_details'] = $this->Stockreport_model->get_stock_ledger_details_report_by_rmid($rmid,$psdate,$pdate);
          }
          $data['stock_ledger_details'] = $this->Stockreport_model->get_stock_ledger_details_report_by_rmid($rmid,$fdate,$tdate);
        }
      }
      if($typ=='trading product')
      {
        if($sldlocation_id!='')
        {
          if($fdate>'2019-04-01')
          {
            $prevresqry = $this->db->query("SELECT p.purchase_date as rm_date,sum(pp.quantity) as qty, 'inword' as type, '' as particulars,'' as order_no,'' as invoice_no FROM `purchase` p,purchase_product pp, grn g WHERE p.purchase_id = pp.purchase_id AND pp.product_id='$tpid' AND p.grn_id = g.grn_id AND p.status=1 AND p.purchase_date>='$psdate' AND p.purchase_date<='$pdate' AND g.location_id = '$sldlocation_id' GROUP BY p.purchase_date UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.product_id='$tpid' AND i.invoice_date>='$psdate' AND i.invoice_date<='$pdate' AND i.location_id = '$sldlocation_id' AND i.order_id = 0 GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
        
            $data['prev_stock_ledger_details'] = $prevresqry->result_array();
          }

$resqry = $this->db->query("SELECT p.purchase_date as rm_date,sum(pp.quantity) as qty, 'inword' as type, '' as particulars,'' as order_no,'' as invoice_no FROM `purchase` p,purchase_product pp, grn g WHERE p.purchase_id = pp.purchase_id AND pp.product_id='$tpid' AND p.grn_id = g.grn_id AND p.status=1 AND p.purchase_date>='$fdate' AND p.purchase_date<='$tdate' AND g.location_id = '$sldlocation_id' GROUP BY p.purchase_date UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.product_id='$tpid' AND i.invoice_date>='$fdate' AND i.invoice_date<='$tdate' AND i.location_id = '$sldlocation_id' AND i.order_id = 0 GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
        
          $data['stock_ledger_details'] = $resqry->result_array();
        }
        else
        {
          if($fdate>'2019-04-01')
          {
            $data['prev_stock_ledger_details'] = $this->Stockreport_model->get_stock_ledger_details_report_by_prodid($tpid,$psdate,$pdate);
          }
          $data['stock_ledger_details'] = $this->Stockreport_model->get_stock_ledger_details_report_by_prodid($tpid,$fdate,$tdate);
        }
      }
      if($typ=='standard product')
      {
        if($sldlocation_id!='')
        {
          if($fdate>'2019-04-01')
          {
            $prevresqry = $this->db->query("SELECT dc.delivery_challan_date as rm_date,sum(dcp.quantity) as qty, 'inword' as type, '' as particulars, '' as order_no, '' as invoice_no FROM delivery_challan dc, delivery_challan_product dcp, `order` o, customer c WHERE dc.delivery_challan_id = dcp.delivery_challan_id AND dc.order_id = o.order_id AND o.customer_id = c.customer_id AND dcp.product_id = '$spid' AND c.is_self = 1 AND o.location_id = '$sldlocation_id' AND dc.delivery_challan_date>='$psdate' AND dc.delivery_challan_date<='$pdate' UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.product_id='$spid' AND i.invoice_date>='$psdate' ANd i.invoice_date<='$pdate' AND i.location_id = '$sldlocation_id' AND i.order_id = 0 GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
        
            $data['prev_stock_ledger_details'] = $prevresqry->result_array();
          }

$resqry = $this->db->query("SELECT dc.delivery_challan_date as rm_date,sum(dcp.quantity) as qty, 'inword' as type, '' as particulars, '' as order_no, '' as invoice_no FROM delivery_challan dc, delivery_challan_product dcp, `order` o, customer c WHERE dc.delivery_challan_id = dcp.delivery_challan_id AND dc.order_id = o.order_id AND o.customer_id = c.customer_id AND dcp.product_id = '$spid' AND c.is_self = 1 AND o.location_id = '$sldlocation_id' AND dc.delivery_challan_date>='$fdate' AND dc.delivery_challan_date<='$tdate' UNION SELECT i.invoice_date as rm_date,sum(ip.quantity) as qty, 'outword' as type, i.invoice_no as particulars,'' as order_no,'' as invoice_no FROM `invoice_product` ip,invoice i WHERE i.invoice_id = ip.invoice_id AND ip.product_id='$spid' AND i.invoice_date>='$fdate' ANd i.invoice_date<='$tdate' AND i.location_id = '$sldlocation_id' AND i.order_id = 0 GROUP BY ip.invoice_product_id ORDER BY rm_date,particulars");
        
          $data['stock_ledger_details'] = $resqry->result_array();
        }
        else
        {
          if($fdate>'2019-04-01')
          {
            $data['prev_stock_ledger_details'] = $this->Stockreport_model->get_stock_ledger_details_report_by_sprodid($spid,$psdate,$pdate);
          }
          //print_r($data['prev_stock_ledger_details']);
          $data['stock_ledger_details'] = $this->Stockreport_model->get_stock_ledger_details_report_by_sprodid($spid,$fdate,$tdate);
          //print_r($data['stock_ledger_details']);exit;
        }
      }
      $this->load->view('stockreport/stock_ledger_details_table',$data);
    }

if($rtype == 'stock ledger')
    {
      if($otyp=='raw material')
      {
        if($ormid!='')
          $prodqry = ' AND r.rawmaterial_id='.$ormid;
        else
          $prodqry = '';

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

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

if($location_id!='')
          $custqry = " AND l.location_id='".$location_id."'";
        else
          $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,r.rawmaterial,sum(rs.stock) as stock,rs.location_id,rs.rawmaterial_id,ruom.rawmaterial_uom FROM `rawmaterial_stock` rs,location l,rawmaterial r, rawmaterial_uom ruom WHERE l.location_id = rs.location_id AND r.rawmaterial_id = rs.rawmaterial_id AND r.rawmaterial_uom_id = ruom.rawmaterial_uom_id $prodqry $gsmqry $colorqry $custqry GROUP BY rs.rawmaterial_id,rs.location_id) UNION (SELECT l.location_name,w.warehouse_name,r.rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.rawmaterial_id,ruom.rawmaterial_uom FROM `warehouse_stock` ws, warehouse w,location l,rawmaterial r, rawmaterial_uom ruom WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND r.rawmaterial_id = ws.rawmaterial_id AND r.rawmaterial_uom_id = ruom.rawmaterial_uom_id $prodqry $gsmqry $colorqry $custqry GROUP BY ws.rawmaterial_id,ws.warehouse_id,ws.location_id) ORDER BY rawmaterial DESC");
        
        $data['stock_ledger_list'] = $resqry->result_array();
        $data['otyp'] = $otyp;
      }

if($otyp=='trading product')
      {
        if($otpid!='')
          $prodqry = ' AND p.product_id='.$otpid;
        else
          $prodqry = '';

if($location_id!='')
          $custqry = " AND l.location_id='".$location_id."'";
        else
          $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,p.product_name as rawmaterial,sum(ps.stock) as stock,ps.location_id,ps.product_id,u.uom as rawmaterial_uom FROM `product_stock` ps,location l,product p, uom u WHERE l.location_id = ps.location_id AND p.product_id = ps.product_id AND p.uom_id = u.uom_id AND p.is_purchase = 1 $prodqry $custqry GROUP BY ps.product_id,ps.location_id) UNION (SELECT l.location_name,w.warehouse_name,p.product_name as rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.product_id,u.uom as rawmaterial_uom FROM `warehouse_stock` ws, warehouse w,location l,product p, uom u WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND p.product_id = ws.product_id AND p.uom_id = u.uom_id AND p.is_purchase = 1 $prodqry $custqry GROUP BY ws.product_id,ws.warehouse_id,ws.location_id) ORDER BY rawmaterial DESC");
        
        $data['stock_ledger_list'] = $resqry->result_array();
        $data['otyp'] = $otyp;
      }

if($otyp=='standard product')
      {
        if($ospid!='')
          $prodqry = ' AND p.product_id='.$ospid;
        else
          $prodqry = '';

if($location_id!='')
          $custqry = " AND l.location_id='".$location_id."'";
        else
          $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,p.product_name as rawmaterial,sum(ps.stock) as stock,ps.location_id,ps.product_id,u.uom as rawmaterial_uom FROM `product_stock` ps,location l,product p, uom u WHERE l.location_id = ps.location_id AND p.product_id = ps.product_id AND p.uom_id = u.uom_id AND p.type_id = 1 and p.is_purchase = 0 $prodqry $custqry GROUP BY ps.product_id,ps.location_id) UNION (SELECT l.location_name,w.warehouse_name,p.product_name as rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.product_id,u.uom as rawmaterial_uom FROM `warehouse_stock` ws, warehouse w,location l,product p, uom u WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND p.product_id = ws.product_id AND p.uom_id = u.uom_id AND p.type_id = 1 and p.is_purchase = 0 $prodqry $custqry GROUP BY ws.product_id,ws.warehouse_id,ws.location_id) ORDER BY rawmaterial DESC");
        
        $data['stock_ledger_list'] = $resqry->result_array();
        $data['otyp'] = $otyp;
      }
      $this->load->view('stockreport/stock_ledger_table',$data);
    }

if($rtype == 'zero stock')
    {
      if($otyp=='raw material')
      {
        if($ormid!='')
          $prodqry = ' AND r.rawmaterial_id='.$ormid;
        else
          $prodqry = '';

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

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

if($location_id!='')
          $custqry = " AND l.location_id='".$location_id."'";
        else
          $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,r.rawmaterial,sum(rs.stock) as stock,rs.location_id,rs.rawmaterial_id,r.low_stock FROM `rawmaterial_stock` rs,location l,rawmaterial r, rawmaterial_uom ruom WHERE l.location_id = rs.location_id AND r.rawmaterial_id = rs.rawmaterial_id AND r.rawmaterial_uom_id = ruom.rawmaterial_uom_id $prodqry $gsmqry $colorqry $custqry GROUP BY rs.rawmaterial_id,rs.location_id HAVING stock<r.low_stock) UNION (SELECT l.location_name,w.warehouse_name,r.rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.rawmaterial_id,r.low_stock FROM `warehouse_stock` ws, warehouse w,location l,rawmaterial r, rawmaterial_uom ruom WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND r.rawmaterial_id = ws.rawmaterial_id AND r.rawmaterial_uom_id = ruom.rawmaterial_uom_id $prodqry $gsmqry $colorqry $custqry GROUP BY ws.rawmaterial_id,ws.warehouse_id,ws.location_id HAVING stock<r.low_stock) ORDER BY rawmaterial DESC");
        
        $data['zero_stock_list'] = $resqry->result_array();
        $data['otyp'] = $otyp;
      }

if($otyp=='trading product')
      {
        if($otpid!='')
          $prodqry = ' AND p.product_id='.$otpid;
        else
          $prodqry = '';

if($location_id!='')
          $custqry = " AND l.location_id='".$location_id."'";
        else
          $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,p.product_name as rawmaterial,sum(ps.stock) as stock,ps.location_id,ps.product_id,p.min_stock_qty FROM `product_stock` ps,location l,product p, uom u WHERE l.location_id = ps.location_id AND p.product_id = ps.product_id AND p.uom_id = u.uom_id AND p.is_purchase = 1 $prodqry $custqry GROUP BY ps.product_id,ps.location_id HAVING stock<p.min_stock_qty) UNION (SELECT l.location_name,w.warehouse_name,p.product_name as rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.product_id,p.min_stock_qty as rawmaterial_uom FROM `warehouse_stock` ws, warehouse w,location l,product p, uom u WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND p.product_id = ws.product_id AND p.uom_id = u.uom_id AND p.is_purchase = 1 $prodqry $custqry GROUP BY ws.product_id,ws.warehouse_id,ws.location_id HAVING stock<p.min_stock_qty) ORDER BY rawmaterial DESC");
        
        $data['zero_stock_list'] = $resqry->result_array();
        $data['otyp'] = $otyp;
      }

if($otyp=='standard product')
      {
        if($ospid!='')
          $prodqry = ' AND p.product_id='.$ospid;
        else
          $prodqry = '';

if($location_id!='')
          $custqry = " AND l.location_id='".$location_id."'";
        else
          $custqry = '';

$resqry = $this->db->query("(SELECT l.location_name,'' as warehouse_name,p.product_name as rawmaterial,sum(ps.stock) as stock,ps.location_id,ps.product_id,p.min_stock_qty FROM `product_stock` ps,location l,product p, uom u WHERE l.location_id = ps.location_id AND p.product_id = ps.product_id AND p.uom_id = u.uom_id AND p.type_id = 1 AND p.is_purchase = 0 $prodqry $custqry GROUP BY ps.product_id,ps.location_id HAVING stock<p.min_stock_qty) UNION (SELECT l.location_name,w.warehouse_name,p.product_name as rawmaterial,sum(ws.stock) as stock,ws.location_id,ws.product_id,p.min_stock_qty as rawmaterial_uom FROM `warehouse_stock` ws, warehouse w,location l,product p, uom u WHERE l.location_id = ws.location_id AND w.warehouse_id = ws.warehouse_id AND p.product_id = ws.product_id AND p.uom_id = u.uom_id AND p.type_id = 1 AND p.is_purchase = 0 $prodqry $custqry GROUP BY ws.product_id,ws.warehouse_id,ws.location_id HAVING stock<p.min_stock_qty) ORDER BY rawmaterial DESC");
        
        $data['zero_stock_list'] = $resqry->result_array();
        $data['otyp'] = $otyp;
      }
      $this->load->view('stockreport/zero_stock_table',$data);
    }

}

}
?>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: Stockreport.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation