<?php

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

class Closingstock_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }
    //GET STOCK DIARY
function getclosingstock($mcategory, $products, $datefrom , $dateto){
    $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 = 'CATEGORY';
    if (!$sord) $sord = 'ASC';

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

    //Total Returned Rows Count
    $this->db->from('PRODUCTS P');
        if ($mcategory) {
            $this->db->where("C.NAME", $mcategory);
        }
        if ($products) {
            $this->db->where("P.NAME", $products);
        }

    $this->db->select("P.ID");
        
    $this->db->join('CATEGORIES C', 'P.CATEGORY = C.ID');


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

    //Total Pages
        if ($count > 0 && $limit > 0) {
            $total_pages = ceil($count / $limit);
            $start = $limit * $page - $limit;
        } else {
            $total_pages = 0;
            $start=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."";

    //Fetch data
    //$this->db->select("C.NAME AS CATEGORY, P.CODE AS BARCODE, P.NAME AS PRODUCTNAME, P.PRICEBUY AS COST, SUM(IFNULL(S.UNITS,0)) AS OPENING, SUM(IFNULL(S.UNITS,0) * IFNULL(S.PRICE,0)) AS OPENINGVALUE",false);
    $this->db->select("C.NAME AS CATEGORY, P.CODE AS BARCODE, P.NAME AS PRODUCTNAME, P.PRICEBUY AS COST, SUM(IFNULL(S.UNITS,0)) AS OPENING",false);
    if ($datefrom) {
        $this->db->join("STOCKDIARY S","P.ID = S.PRODUCT AND DATE(S.DATENEW) < '".$datefrom."'",'LEFT');
    }
    else{
         $this->db->join("STOCKDIARY S","P.ID = S.PRODUCT AND DATE(S.DATENEW) < '".date('Y-m-d')."'",'LEFT');
    }

         if ($mcategory) {
            $this->db->where("C.NAME", $mcategory);
        }
         if ($products) {
            $this->db->where("P.NAME", $products);
        }
            $this->db->join('CATEGORIES C', 'P.CATEGORY = C.ID');
            $this->db->group_by("P.ID");
        $this->db->order_by($sidx, $sord);
        $this->db->limit($limit, $start);
        $rows = $this->db->get("PRODUCTS P")->result_array();

        // IN VALUES
        //$this->db->select("SUM(IFNULL(S.UNITS,0)) AS STOCKIN, SUM(IFNULL(S.UNITS,0) * IFNULL(S.PRICE,0)) AS INVALUE",false);
        $this->db->select("SUM(IFNULL(S.UNITS,0)) AS STOCKIN",false);
    if ($datefrom && $dateto) {
        $this->db->join("STOCKDIARY S","P.ID = S.PRODUCT AND S.REASON>0 AND DATE(S.DATENEW) >= '".$datefrom."' AND DATE(S.DATENEW) <= '".$dateto."'",'LEFT');
    }
    elseif ($datefrom) {
        $this->db->join("STOCKDIARY S","P.ID = S.PRODUCT AND S.REASON>0 AND DATE(S.DATENEW) = '".$datefrom."'",'LEFT');
    }
    else{
         $this->db->join("STOCKDIARY S","P.ID = S.PRODUCT AND S.REASON>0 AND DATE(S.DATENEW) = '".date('Y-m-d')."'",'LEFT');
    }

         if ($mcategory) {
            $this->db->where("C.NAME", $mcategory);
        }
         if ($products) {
            $this->db->where("P.NAME", $products);
        }
            $this->db->join('CATEGORIES C', 'P.CATEGORY = C.ID');
            $this->db->group_by("P.ID");
        $this->db->order_by($sidx, $sord);
        $this->db->limit($limit, $start);
        $rows2 = $this->db->get("PRODUCTS P")->result_array();
        
        // OUT VALUES
        $this->db->select("SUM(IFNULL(S.UNITS,0)) AS STOCKOUT",false);
    if ($datefrom && $dateto) {
        $this->db->join("STOCKDIARY S","P.ID = S.PRODUCT AND S.REASON>0 AND DATE(S.DATENEW) >= '".$datefrom."' AND DATE(S.DATENEW) <= '".$dateto."'",'LEFT');
    }
        elseif ($datefrom) {
        $this->db->join("STOCKDIARY S","P.ID = S.PRODUCT AND S.REASON<0 AND DATE(S.DATENEW) = '".$datefrom."'",'LEFT');
        
    }
    else{
         $this->db->join("STOCKDIARY S","P.ID = S.PRODUCT AND S.REASON<0 AND DATE(S.DATENEW) = '".date('Y-m-d')."'",'LEFT');
    }

         if ($mcategory) {
            $this->db->where("C.NAME", $mcategory);
        }
         if ($products) {
            $this->db->where("P.NAME", $products);
        }
            $this->db->join('CATEGORIES C', 'P.CATEGORY = C.ID');
            $this->db->group_by("P.ID");
        $this->db->order_by($sidx, $sord);
        $this->db->limit($limit, $start);
        $rows3 = $this->db->get("PRODUCTS P")->result_array();
            //Format data
            $i=0;
            foreach($rows as $row){
                $data['rows'][$i]['id']=$i+1;
                $data['rows'][$i]['cell']=array($row['CATEGORY'],$row['BARCODE'],$row['PRODUCTNAME'],$row['COST'],$row['OPENING'],$row['OPENING']*$row['COST'],$rows2[$i]['STOCKIN'],$rows2[$i]['STOCKIN']*$row['COST'],$rows3[$i]['STOCKOUT'],$rows3[$i]['STOCKOUT']*$row['COST'],$row['OPENING']+$rows2[$i]['STOCKIN']+$rows3[$i]['STOCKOUT'],($row['OPENING']+$rows2[$i]['STOCKIN']+$rows3[$i]['STOCKOUT'])*$row['COST']);
                $i++;
            }
       return json_encode($data);
    }

    

}