<?php

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

class Production_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    // GET PRODUCTION REPORT     HUSSAIN 02/05/2012
    function getproductionreport($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 = 'STOCKDIARY.DATENEW';
        if (!$sord)
            $sord = 'asc';

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

//Total Returned Rows Count
        $this->db->from('STOCKDIARY');
        $this->db->select("PRODUCTS.NAME, STOCKDIARY.UNITS, STOCKDIARY.PRICE ,(STOCKDIARY.UNITS*STOCKDIARY.PRICE) AS TOTAL");

        if ($datefrom && $dateto) {
            $this->db->where("DATE(STOCKDIARY.DATENEW)>=", $datefrom);
            $this->db->where("DATE(STOCKDIARY.DATENEW)<=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(STOCKDIARY.DATENEW)", $datefrom);
        }

        $where = 'PRODUCTS.CATEGORY <> -1 AND PRODUCTS.CATEGORY <> -2 AND PRODUCTS.ISCOM <> 1 AND PRODUCTS.CATEGORY=C.ID AND PRODUCTS.CATEGORY NOT IN (SELECT C1.ID FROM CATEGORIES AS C1 LEFT JOIN CATEGORIES AS C2 ON C1.PARENTID = C2.ID WHERE C1.PARENTID = -1 OR C1.PARENTID = C2.ID)';
        $this->db->where($where);
        $this->db->where('STOCKDIARY.REASON', "1");
        $this->db->join('PRODUCTS', 'PRODUCTS.ID = STOCKDIARY.PRODUCT', 'LEFT');
        $this->db->join('CATEGORIES AS C', 'C.ID = PRODUCTS.CATEGORY', 'LEFT');
        $this->db->join('STOCKDIARY_GRN', 'STOCKDIARY_GRN.STOCKDIARYID= STOCKDIARY.ID', 'LEFT');
        $this->db->group_by("PRODUCTS.NAME");
        $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 . "";


//Fetch data
        $this->db->select("PRODUCTS.NAME, SUM(STOCKDIARY.UNITS) AS UNITS, MAX(STOCKDIARY.PRICE) AS PRICE,(STOCKDIARY.UNITS*STOCKDIARY.PRICE) AS TOTAL");
        if ($datefrom && $dateto) {
            $this->db->where("DATE(STOCKDIARY.DATENEW)>=", $datefrom);
            $this->db->where("DATE(STOCKDIARY.DATENEW)<=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(STOCKDIARY.DATENEW)", $datefrom);
        }

        $where = 'PRODUCTS.CATEGORY <> -1 AND PRODUCTS.CATEGORY <> -2 AND PRODUCTS.ISCOM <> 1 AND PRODUCTS.CATEGORY=C.ID AND PRODUCTS.CATEGORY NOT IN (SELECT C1.ID FROM CATEGORIES AS C1 LEFT JOIN CATEGORIES AS C2 ON C1.PARENTID = C2.ID WHERE C1.PARENTID = -1 OR C1.PARENTID = C2.ID)';
        $this->db->where($where);
        $this->db->where('STOCKDIARY.REASON', "1");


        $this->db->join('PRODUCTS', 'PRODUCTS.ID = STOCKDIARY.PRODUCT', 'LEFT');
        $this->db->join('CATEGORIES AS C', 'C.ID = PRODUCTS.CATEGORY', 'LEFT');
        $this->db->join('STOCKDIARY_GRN', 'STOCKDIARY_GRN.STOCKDIARYID= STOCKDIARY.ID', 'LEFT');
        $this->db->group_by("PRODUCTS.NAME");
        $rows = $this->db->get("STOCKDIARY")->result_array();

//Format data
        $i = 0;
        foreach ($rows as $row) {
            $data['rows'][$i]['id'] = $i + 1;
            $data['rows'][$i]['cell'] = array($row['NAME'], $row['UNITS'], $row['PRICE'], $row['UNITS'] * $row['PRICE']);
            $i++;
        }
        return json_encode($data);
    }

}