<?php

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

class Materialplanning_model extends CI_Model {

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

// GET MATERIALREQUESTS BY STANDARD
    function getlines($datefrom, $dateto, $product) {

        $table_name = "MATERIALREQUESTLINES";
        $this->db->select("MATERIALREQUESTLINES.MATERIALREQUEST, MATERIALREQUESTS.REQUIREDON, LOCATIONS.NAME AS LOCATION, PRODUCTS.NAME AS PRODUCT, MATERIALREQUESTLINES.APPROVEDQUANTITY - MATERIALREQUESTLINES.QUANTITYDELIVERED AS QUANTITY, SC.UNITS AS QOH, UNITS.NAME AS UOM,SUM(PRODUCTS.PRICEBUY * (MATERIALREQUESTLINES.APPROVEDQUANTITY - MATERIALREQUESTLINES.QUANTITYDELIVERED)) AS TOTAL,SL.STOCKSECURITY")->from($table_name);

        if ($datefrom && $dateto) {
            $this->db->where("REQUIREDON >=", $datefrom);
            $this->db->where("REQUIREDON <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("REQUIREDON", $datefrom);
        }
        if ($product) {
            $this->db->where("MATERIALREQUESTLINES.PRODUCT", $product);
        }
        $where = '(MATERIALREQUESTLINES.LINESTATUS = 4 OR MATERIALREQUESTLINES.LINESTATUS = 2)';
        $this->db->where($where);

        $this->db->join('PRODUCTS', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTS.ID', 'LEFT');
        $this->db->join('MATERIALREQUESTS', 'MATERIALREQUESTLINES.MATERIALREQUEST=MATERIALREQUESTS.ID', 'LEFT');
        $this->db->join('LOCATIONS', 'MATERIALREQUESTS.LOCATION=LOCATIONS.ID', 'LEFT');
        $this->db->join('UNITS', 'MATERIALREQUESTLINES.UNIT=UNITS.ID', 'LEFT');
        $this->db->join('STOCKCURRENT AS SC', 'SC.PRODUCT = MATERIALREQUESTLINES.PRODUCT', 'LEFT');
        $this->db->join('STOCKLEVEL AS SL', 'SL.PRODUCT = MATERIALREQUESTLINES.PRODUCT', 'LEFT');
        //$this->db->join('STOCKDIARY', 'STOCKDIARY.PRODUCT = MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.REASON=1 AND STOCKDIARY.DATENEW=(SELECT MAX(DATENEW) FROM STOCKDIARY WHERE PRODUCT= MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.LOCATION=0)', 'LEFT');
        //$this->db->join('PRODUCTS', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTS.ID', 'LEFT');
        $this->db->order_by("LOCATIONS.NAME", "asc");
        $this->db->group_by('LOCATIONS.NAME, PRODUCTS.NAME ');
        $rows = $this->db->get()->result_array();
        $i = 0;
        $loc1 = '';
        $loc2 = '';
        $qty = 0.0;
        $tot = 0.0;
        $gqty = 0.0;
        $gtot = 0.0;
        foreach ($rows as $row) {

            $loc1 = $row['LOCATION'];
            if ($loc2 == '') {
                $loc2 = $loc1;
            }

            if ($loc1 != $loc2) {
                $data['rows'][$i]['id'] = $i + 1;
                if ($product) {
                    $data['rows'][$i]['cell'] = array('', '', 'Total', '', $qty, '', '', $tot);
                } else {
                    $data['rows'][$i]['cell'] = array('', '', 'Total', '', '', '', '', $tot);
                }
                $i++;
                $loc2 = $loc1;
                $qty = 0.0;
                $tot = 0.0;
            }

            $data['rows'][$i]['id'] = $i + 1;
            $data['rows'][$i]['cell'] = array($row['MATERIALREQUEST'], $row['REQUIREDON'], $row['LOCATION'], $row['PRODUCT'], $row['QUANTITY'], $row['QOH'], $row['UOM'], $row['TOTAL'], $row['STOCKSECURITY']);
            $i++;
            $tot = $tot + $row['TOTAL'];
            $qty = $qty + $row['QUANTITY'];
            $gtot = $gtot + $row['TOTAL'];
            $gqty = $gqty + $row['QUANTITY'];
        }
        $data['rows'][$i]['id'] = $i + 1;
        if ($product) {
            $data['rows'][$i]['cell'] = array('', '', 'Total', '', $qty, '', '', $tot);
        } else {
            $data['rows'][$i]['cell'] = array('', '', 'Total', '', '', '', '', $tot);
        }
        $i++;
        $data['rows'][$i]['id'] = $i + 1;
        if ($product) {
            $data['rows'][$i]['cell'] = array('', '', 'Grand Total', '', $gqty, '', '', $gtot);
        } else {
            $data['rows'][$i]['cell'] = array('', '', 'Grand Total', '', '', '', '', $gtot);
        }

        return json_encode($data);
    }

//  GET MATERIALREQUESTS BY CATEGORY
//    function getlinesbycategory($datefrom, $dateto, $product) {
//
//        $this->load->helper('docstatus');
//        $table_name = "MATERIALREQUESTLINES";
//        $this->db->select("MATERIALREQUESTS.REQUIREDON, CATEGORIES.NAME AS CATEGORY, PRODUCTS.NAME AS PRODUCT, MATERIALREQUESTLINES.APPROVEDQUANTITY, SC.UNITS AS QOH, UNITS.NAME AS UOM,SUM(PURCHASEORDERLINES.QUANTITY-PURCHASEORDERLINES.QUANTITYRECEIVED) AS POQTY, PRODUCTS.PRICEBUY * SUM(MATERIALREQUESTLINES.QUANTITY - MATERIALREQUESTLINES.QUANTITYDELIVERED) AS TOTAL")->from($table_name);
//
//        if ($datefrom && $dateto) {
//            $this->db->where("REQUIREDON >=", $datefrom);
//            $this->db->where("REQUIREDON <=", $dateto);
//        } else if ($datefrom) {
//            $this->db->where("REQUIREDON", $datefrom);
//        }
//        if ($product) {
//            $this->db->where("MATERIALREQUESTLINES.PRODUCT", $product);
//        }
//        $where = '(MATERIALREQUESTLINES.LINESTATUS = 4 OR MATERIALREQUESTLINES.LINESTATUS = ' . LINE_APPROVED . ')';
//        $this->db->where($where);
//        $this->db->join('MATERIALREQUESTS', 'MATERIALREQUESTLINES.MATERIALREQUEST=MATERIALREQUESTS.ID', 'LEFT');
////        $this->db->join('PRODUCTGROUPS_PROD', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTGROUPS_PROD.PRODUCT', 'LEFT');
////        $this->db->join('PRODUCTGROUPS', 'PRODUCTGROUPS_PROD.PRODUCTGROUP=PRODUCTGROUPS.ID', 'LEFT');
//        $this->db->join('PRODUCTS', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTS.ID', 'LEFT');
//        $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY=CATEGORIES.ID', 'LEFT');
//        $this->db->join('UNITS', 'MATERIALREQUESTLINES.UNIT=UNITS.ID', 'LEFT');
//        $this->db->join('PURCHASEORDERLINES', 'MATERIALREQUESTLINES.PRODUCT=PURCHASEORDERLINES.PRODUCT AND PURCHASEORDERLINES.LINESTATUS = ' . LINE_ISSUED, 'LEFT');
//        $this->db->join('STOCKCURRENT AS SC', 'SC.PRODUCT = MATERIALREQUESTLINES.PRODUCT AND SC.LOCATION = "0"', 'LEFT');
//        //$this->db->join('STOCKDIARY', 'STOCKDIARY.PRODUCT = MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.REASON=1 AND STOCKDIARY.DATENEW=(SELECT MAX(DATENEW) FROM STOCKDIARY WHERE PRODUCT= MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.LOCATION="0")', 'LEFT');
//        $this->db->order_by("CATEGORIES.NAME", "asc");
//        $this->db->order_by("PRODUCTS.NAME", "asc");
//        $this->db->group_by('PRODUCTS.ID');
//        $this->db->group_by('MATERIALREQUESTS.REQUIREDON');
//        $rows = $this->db->get()->result_array();
//        $i = 0;
//        $loc1 = '';
//        $loc2 = '';
//        $qtot = 0.0;
//        $tot = 0.0;
//        $gtot = 0.0;
//
//        foreach ($rows as $row) {
//
//            $loc1 = $row['PRODUCT'];
//            if ($loc2 == '') {
//                $loc2 = $loc1;
//            }
//
//            if ($loc1 != $loc2) {
//                $data['rows'][$i]['id'] = $i + 1;
//                if ($product) {
//                    $data['rows'][$i]['cell'] = array('', 'Total', '', $qtot, $qoh, '', $poqty, $newpo, $tot);
//                } else {
//                    $data['rows'][$i]['cell'] = array('', 'Total', '', $qtot, $qoh, '', $poqty, $newpo, $tot);
//                }
//                $i++;
//                $loc2 = $loc1;
//                $qty = 0.0;
//                $tot = 0.0;
//                $qtot = 0.0;
//                $qoh = 0.0;
//            }
//            $poqty = $row['POQTY'];
//            $qoh = $row['QOH'];
//            $row['QOH'] = '';
//            $row['QTYSTS'] = '';
//            $row['POQTY'] = '';
//            $data['rows'][$i]['id'] = $i + 1;
//            $data['rows'][$i]['cell'] = array($row['REQUIREDON'], $row['CATEGORY'], $row['PRODUCT'], $row['APPROVEDQUANTITY'], $row['QOH'], $row['UOM'], $row['POQTY'], $row['QTYSTS'], $row['TOTAL']);
//            $i++;
//            $qtot = $qtot + $row['APPROVEDQUANTITY'];
//            $tot = $tot + $row['TOTAL'];
//            $gtot = $gtot + $row['TOTAL'];
//
//            $uom = $row['UOM'];
//            $newpo = ($qoh + $poqty) - $qtot;
//        }
//
//        $data['rows'][$i]['id'] = $i + 1;
//        if ($product) {
//            $data['rows'][$i]['cell'] = array('', 'Total', '', $qtot, $qoh, $uom, $poqty, $newpo, $tot);
//        } else {
//            $data['rows'][$i]['cell'] = array('', 'Total', '', $qtot, $qoh, $uom, $poqty, $newpo, $tot);
//        }
//        $i++;
//        $data['rows'][$i]['id'] = $i + 1;
//        if ($product) {
//            $data['rows'][$i]['cell'] = array('', 'Grand Total', '', '', '', '', '', '', $gtot);
//        } else {
//            $data['rows'][$i]['cell'] = array('', 'Grand Total', '', '', '', '', '', '', $gtot);
//        }
//
//        return json_encode($data);
//    }


     

// GET MATERIALREQUESTS BY LOCATION
    function getlinesbylocation($datefrom, $dateto, $product) {

        $table_name = "MATERIALREQUESTLINES";
        $this->db->select("MATERIALREQUESTS.REQUIREDON, LOCATIONS.NAME AS CATEGORY,SUM(PRODUCTS.PRICEBUY * (MATERIALREQUESTLINES.QUANTITY - MATERIALREQUESTLINES.QUANTITYDELIVERED)) AS TOTAL")->from($table_name);

        if ($datefrom && $dateto) {
            $this->db->where("REQUIREDON >=", $datefrom);
            $this->db->where("REQUIREDON <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("REQUIREDON", $datefrom);
        }
        if ($product) {
            $this->db->where("MATERIALREQUESTLINES.PRODUCT", $product);
        }
        $where = '(MATERIALREQUESTLINES.LINESTATUS = 4 OR MATERIALREQUESTLINES.LINESTATUS = 2)';
        $this->db->where($where);
        $this->db->join('MATERIALREQUESTS', 'MATERIALREQUESTLINES.MATERIALREQUEST=MATERIALREQUESTS.ID', 'LEFT');
        $this->db->join('LOCATIONS', 'MATERIALREQUESTS.LOCATION = LOCATIONS.ID', 'LEFT');
        //$this->db->join('STOCKDIARY', 'STOCKDIARY.PRODUCT = MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.REASON=1 AND STOCKDIARY.DATENEW=(SELECT MAX(DATENEW) FROM STOCKDIARY WHERE PRODUCT= MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.LOCATION=0)', 'LEFT');
        $this->db->join('PRODUCTS', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTS.ID', 'LEFT');
        $this->db->order_by("MATERIALREQUESTS.REQUIREDON", "asc");
        $this->db->group_by('LOCATIONS.NAME');
        $rows = $this->db->get()->result_array();
        $i = 0;
        $loc1 = '';
        $loc2 = '';
        $tot = 0.0;
        $gtot = 0.0;
        foreach ($rows as $row) {

            $loc1 = $row['REQUIREDON'];
            if ($loc2 == '') {
                $loc2 = $loc1;
            }

            if ($loc1 != $loc2) {
                $data['rows'][$i]['id'] = $i + 1;
                if ($product) {
                    $data['rows'][$i]['cell'] = array('', 'Total', $tot);
                } else {
                    $data['rows'][$i]['cell'] = array('', 'Total', $tot);
                }
                $i++;
                $loc2 = $loc1;
                $qty = 0.0;
                $tot = 0.0;
            }

            $data['rows'][$i]['id'] = $i + 1;
            $data['rows'][$i]['cell'] = array($row['REQUIREDON'], $row['CATEGORY'], $row['TOTAL']);
            $i++;
            $tot = $tot + $row['TOTAL'];
            $gtot = $gtot + $row['TOTAL'];
        }
        $data['rows'][$i]['id'] = $i + 1;
        if ($product) {
            $data['rows'][$i]['cell'] = array('', 'Total', $tot);
        } else {
            $data['rows'][$i]['cell'] = array('', 'Total', $tot);
        }
        $i++;
        $data['rows'][$i]['id'] = $i + 1;
        if ($product) {
            $data['rows'][$i]['cell'] = array('', 'Grand Total', $gtot);
        } else {
            $data['rows'][$i]['cell'] = array('', 'Grand Total', $gtot);
        }

        return json_encode($data);
    }

    public function getmplinesbyid($id) {
        $table_name = "MATERIALREQUESTLINES";
        $this->db->select("MATERIALREQUESTS.REQUIREDON, LOCATIONS.NAME AS LOCATION, PRODUCTS.NAME AS PRODUCT, MATERIALREQUESTLINES.APPROVEDQUANTITY - MATERIALREQUESTLINES.QUANTITYDELIVERED AS QUANTITY, SC.UNITS AS QOH, UNITS.NAME AS UOM,SUM(PRODUCTS.PRICEBUY * (MATERIALREQUESTLINES.APPROVEDQUANTITY - MATERIALREQUESTLINES.QUANTITYDELIVERED)) AS TOTAL")->from($table_name);
        $where = '(MATERIALREQUESTLINES.LINESTATUS = 4 OR MATERIALREQUESTLINES.LINESTATUS = 2)';
        $this->db->where($where);
        $this->db->join('PRODUCTS', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTS.ID', 'LEFT');
        $this->db->join('MATERIALREQUESTS', 'MATERIALREQUESTLINES.MATERIALREQUEST=MATERIALREQUESTS.ID', 'LEFT');
        $this->db->join('LOCATIONS', 'MATERIALREQUESTS.LOCATION=LOCATIONS.ID', 'LEFT');
        $this->db->join('UNITS', 'MATERIALREQUESTLINES.UNIT=UNITS.ID', 'LEFT');
        $this->db->join('STOCKCURRENT AS SC', 'SC.PRODUCT = MATERIALREQUESTLINES.PRODUCT', 'LEFT');
        $this->db->join('STOCKLEVEL AS SL', 'SL.PRODUCT = MATERIALREQUESTLINES.PRODUCT', 'LEFT');
        //$this->db->join('STOCKDIARY', 'STOCKDIARY.PRODUCT = MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.REASON=1 AND STOCKDIARY.DATENEW=(SELECT MAX(DATENEW) FROM STOCKDIARY WHERE PRODUCT= MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.LOCATION=0)', 'LEFT');
        //$this->db->join('PRODUCTS', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTS.ID', 'LEFT');
        $this->db->order_by("LOCATIONS.NAME", "asc");
        $this->db->group_by('LOCATIONS.NAME, PRODUCTS.NAME ');
        return $this->db->get()->result();
    }

    public function getmplinesbycategory($datefrom, $dateto, $product) {
        $table_name = "MATERIALREQUESTLINES";
        $this->db->select("MATERIALREQUESTS.REQUIREDON, LOCATIONS.NAME AS LOCATION, CATEGORIES.NAME AS CATEGORY, PRODUCTS.NAME AS PRODUCT, MATERIALREQUESTLINES.APPROVEDQUANTITY, SC.UNITS AS QOH, UNITS.NAME AS UOM,SUM(PURCHASEORDERLINES.QUANTITY-PURCHASEORDERLINES.QUANTITYRECEIVED) AS POQTY,SUM((`PURCHASEORDERLINES`.`QUANTITY`-`PURCHASEORDERLINES`.`QUANTITYRECEIVED`)+`SC`.`UNITS`-`MATERIALREQUESTLINES`.`APPROVEDQUANTITY`)
                AS QTYSTS, PRODUCTS.PRICEBUY * SUM(MATERIALREQUESTLINES.QUANTITY - MATERIALREQUESTLINES.QUANTITYDELIVERED) AS TOTAL")->from($table_name);

        if ($datefrom && $dateto) {
            $this->db->where("REQUIREDON >=", $datefrom);
            $this->db->where("REQUIREDON <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("REQUIREDON", $datefrom);
        }
        if ($product) {
            $this->db->where("MATERIALREQUESTLINES.PRODUCT", $product);
        }
        $where = '(MATERIALREQUESTLINES.LINESTATUS = 4 OR MATERIALREQUESTLINES.LINESTATUS = 2)';
        $this->db->where($where);

        $this->db->join('MATERIALREQUESTS', 'MATERIALREQUESTLINES.MATERIALREQUEST=MATERIALREQUESTS.ID', 'LEFT');
//        $this->db->join('PRODUCTGROUPS_PROD', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTGROUPS_PROD.PRODUCT', 'LEFT');
//        $this->db->join('PRODUCTGROUPS', 'PRODUCTGROUPS_PROD.PRODUCTGROUP=PRODUCTGROUPS.ID', 'LEFT');
        $this->db->join('PRODUCTS', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTS.ID', 'LEFT');
        $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY=CATEGORIES.ID', 'LEFT');
        $this->db->join('UNITS', 'MATERIALREQUESTLINES.UNIT=UNITS.ID', 'LEFT');
        $this->db->join('LOCATIONS', 'MATERIALREQUESTS.LOCATION =LOCATIONS.ID', 'LEFT');
        $this->db->join('PURCHASEORDERLINES', 'MATERIALREQUESTLINES.PRODUCT=PURCHASEORDERLINES.PRODUCT AND PURCHASEORDERLINES.LINESTATUS = 4', 'LEFT');
        $this->db->join('STOCKCURRENT AS SC', 'SC.PRODUCT = MATERIALREQUESTLINES.PRODUCT AND SC.LOCATION = "0"', 'LEFT');
        //$this->db->join('STOCKDIARY', 'STOCKDIARY.PRODUCT = MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.REASON=1 AND STOCKDIARY.DATENEW=(SELECT MAX(DATENEW) FROM STOCKDIARY WHERE PRODUCT= MATERIALREQUESTLINES.PRODUCT AND STOCKDIARY.LOCATION="0")', 'LEFT');
        $this->db->order_by("CATEGORIES.NAME", "asc");
        $this->db->order_by("PRODUCTS.NAME", "asc");
        $this->db->order_by("LOCATIONS.NAME", "asc");
        $this->db->group_by('PRODUCTS.ID');
        $this->db->group_by('LOCATIONS.ID');
        $this->db->group_by('MATERIALREQUESTS.REQUIREDON');
        return $this->db->get()->result();
        // $rows = $this->db->get()->result_array();
//      
    }

}