<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Stocksummaryitemwise_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }
    //GET STOCK DIARY
function getstocksummaryitemwise($location, $mcategory, $products, $date)
{

$page = $this->input->post('page');
$limit = $this->input->post('rows'); // get how many rows we want to have into the grid
$sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
$sord = $this->input->post('sord'); // get the direction

//Default values
if (!$sidx) $sidx = 'PRODUCTS.NAME';
if (!$sord) $sord = 'desc';

if (!$page) $page = 1;
if (!$limit) $limit = 25;

//Total Returned Rows Count
$this->db->from('PRODUCTS AS P');

$this->db->select("P.NAME, IFNULL(DATE(S.DATENEW),CURDATE()) AS DATE, IFNULL(SUM(S.UNITS),0) AS OPENINGSTOCK", false);
$this->db->join('STOCKDIARY AS S','S.PRODUCT = P.ID AND DATE(S.DATENEW)=(SELECT MAX(DATE(S2.DATENEW)) FROM STOCKDIARY S2 WHERE PRODUCT=P.ID AND S2.DATENEW < CURDATE()) AND S.REASON=1 AND S.LOCATION="0"','LEFT');

 $this->db->limit(10000);
$count = $this->db->count_all_results();

//Total Pages
if( $count > 0 && $limit > 0) {
$total_pages = ceil($count/$limit);
} else {
$total_pages = 0;
}

//Check requested page
if ($page > $total_pages) $page=$total_pages;

//Set start record number
$start = $limit*$page - $limit;

//Format data for jqGrid output
$data['page'] = "".$page."";
$data['total'] = "".$total_pages."";
$data['records'] = "".$count."";



//         $where='DATE(S.DATENEW)=(SELECT MAX(DATE(S2.DATENEW)))';
//       $this->db->where($where);

$this->db->select("P.NAME, IFNULL(DATE(S.DATENEW),CURDATE()) AS DATE, IFNULL(SUM(S.UNITS),0) AS OPENINGSTOCK", false);
$this->db->join('STOCKDIARY AS S','S.PRODUCT = P.ID AND DATE(S.DATENEW)=(SELECT MAX(DATE(S2.DATENEW)) FROM STOCKDIARY S2 WHERE PRODUCT=P.ID AND S2.DATENEW < CURDATE()) AND S.REASON=1 AND S.LOCATION="0"','LEFT');
$this->db->join('CATEGORIES AS C','C.ID = P.CATEGORY','LEFT');
$this->db->where('C.PARENTID','-1');

$this->db->group_by("P.NAME,DATE(S.DATENEW)");
 $this->db->limit(10000);
// $this->db->order_by($sidx, $sord);
$this->db->limit($limit, $start);
$rows1 = $this->db->get("PRODUCTS AS P")->result_array();

$this->db->select("IFNULL(SUM(S.UNITS),0) AS STOCKIN", false);
$this->db->join('STOCKDIARY AS S','S.PRODUCT = P.ID AND DATE(S.DATENEW)=CURDATE() AND S.REASON=1 AND S.LOCATION="0"','LEFT');
$this->db->join('CATEGORIES AS C','C.ID = P.CATEGORY','LEFT');
$this->db->where('C.PARENTID','-1');

$this->db->group_by("P.NAME,DATE(S.DATENEW)");
 $this->db->limit(10000);
 //$this->db->order_by($sidx, $sord);
$this->db->limit($limit, $start);
$rows2 = $this->db->get("PRODUCTS AS P")->result_array();

$this->db->select("IFNULL(SUM(S.UNITS),0) AS STOCKOUT", false);
$this->db->join('STOCKDIARY AS S','S.PRODUCT = P.ID AND DATE(S.DATENEW)=CURDATE() AND S.REASON<>1 AND S.LOCATION="0"','LEFT');
$this->db->join('CATEGORIES AS C','C.ID = P.CATEGORY','LEFT');
$this->db->where('C.PARENTID','-1');

$this->db->group_by("P.NAME,DATE(S.DATENEW)");
 $this->db->limit(10000);
// $this->db->order_by($sidx, $sord);
$this->db->limit($limit, $start);
$rows3 = $this->db->get("PRODUCTS AS P")->result_array();


//Format data
$i=0;
foreach($rows1 as $row){



$data['rows'][$i]['id']=$i+1;
$data['rows'][$i]['cell']=array($row['NAME'],$row['DATE'],$row['OPENINGSTOCK'],$rows2[$i]['STOCKIN'],$rows3[$i]['STOCKOUT'],$row['OPENINGSTOCK']+$rows2[$i]['STOCKIN']-$rows3[$i]['STOCKOUT']);
$i++;
}
return json_encode($data);
}

  public function getcurrentstockdetailsprint($location,$mcategory,$products) {
        $table_name = "STOCKCURRENT";
        $this->db->select("PRODUCTS.REFERENCE,PRODUCTS.NAME AS PRODUCT,STOCKCURRENT.UNITS,UNITS.NAME AS UOM,PRODUCTS.PRICEBUY AS UNITPRICE,(STOCKCURRENT.UNITS*PRODUCTS.PRICEBUY) AS LINETOTAL,STOCKLEVEL.STOCKSECURITY, STOCKLEVEL.STOCKMAXIMUM")->from($table_name);

        if ($location) {
            $this->db->where("STOCKCURRENT.LOCATION", $location);
        }
         if ($mcategory) {
            $this->db->where("CATEGORIES.NAME", $mcategory);
        }
         if ($products) {
            $this->db->where("PRODUCTS.NAME", $products);
        }
         $this->db->where('CATEGORIES.PARENTID', '-1');

         $this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT=PRODUCTS.ID');
        $this->db->join('STOCKLEVEL','PRODUCTS.ID = STOCKLEVEL.PRODUCT AND STOCKCURRENT.LOCATION = STOCKLEVEL.LOCATION','LEFT');
        $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY = CATEGORIES.ID');
        $this->db->join('LOCATIONS', 'STOCKCURRENT.LOCATION=LOCATIONS.ID');
         $this->db->join('PRODUCTGROUPS_PROD', 'PRODUCTGROUPS_PROD.PRODUCT=PRODUCTS.ID','LEFT');
        $this->db->join('PRODUCTGROUPS', 'PRODUCTGROUPS.ID=PRODUCTGROUPS_PROD.PRODUCTGROUP','LEFT');
          $this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID','LEFT');
          $this->db->join('UNITS','UNITS.ID = MATERIALS_UNITS.UNIT');

            $this->db->group_by("PRODUCTS.ID");
//
//$this->db->order_by($sidx, $sord);
//$this->db->limit($limit, $start);
return $this->db->get()->result();
    }


}