<?php

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

class Goodsreceipts_model extends CI_Model {

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

    function productInStockCurrent($pid, $lid) {
        $this->db->where('PRODUCT', $pid);
        $this->db->where('LOCATION', $lid);
        return $this->db->get('STOCKCURRENT')->result();
    }

    function updateStockcurrent($lid, $pid, $receiving) {
        $this->db->where('STOCKCURRENT.LOCATION', $lid);
        $this->db->where('STOCKCURRENT.PRODUCT', $pid);
        $this->db->where('STOCKCURRENT.ATTRIBUTESETINSTANCE_ID IS NULL');
        $this->db->set('UNITS', 'UNITS+' . $receiving, false);
        return $this->db->update('STOCKCURRENT');
    }

    function insertStockcurrent($lid, $pid, $receiving) {
        
            $data = array(
                'LOCATION' => $lid,
                'PRODUCT' => $pid,
                'UNITS' => $receiving
            );

        $this->db->insert('STOCKCURRENT', $data);
    }

    function insertStockDiary($id, $date, $reason, $lid, $pid, $unit, $price, $supplier, $desc) {
        // $this->db->where('STOCKCURRENT.LOCATION', $location);

        $data = array(
            'ID' => $id,
            'DATENEW' => $date,
            'REASON' => $reason,
            'LOCATION' => $lid,
            'PRODUCT' => $pid,
            'UNITS' => $unit,
            'PRICE' => $price,
            'SUPPLIER' => $supplier
        );

        $this->db->insert('STOCKDIARY', $data);
    }

 
    function getSuppliers() {
        $this->db->select('ID, NAME');
        //$this->db->where('ID <> "0"');
        return $this->db->get('SUPPLIERS')->result();
    }

    function getsidbn($name) {
        $this->db->where('NAME', $name);
        $q = $this->db->get('SUPPLIERS')->result();
        return $q[0]->ID;
    }

  

    function getnextgrn() {

        $this->db->select('NEXTGRNO');
        $q = $this->db->get('CONFIGURATION')->result();
        return $q[0]->NEXTGRNO;
    }

    function incrementgrn() {
        $this->db->set('NEXTGRNO', 'NEXTGRNO+1', FALSE);
        return $this->db->update('CONFIGURATION');
    }

    function insertgrn( $sdid, $grn, $refid) {

        $data = array(
//            'POID' => $poid,
//            'STOCKDIARYID' => $sdid,
//            'GRNUMBER' => $grn

            'STOCKDIARYID' => $sdid,
            'GRNUMBER' => $grn,
            'REFID' => $refid
        );
        return $this->db->insert('STOCKDIARY_GRN', $data);
    }

   


    function getgrnsbyparam($datefrom, $dateto, $sid,$po, $grn) {


        $table_name = "PURCHASEORDERLINES AS PL, STOCKDIARY, STOCKDIARY_GRN";

        $this->db->select('PL.ID, STOCKDIARY.ID AS SDID, STOCKDIARY.DATENEW, SUPPLIERS.NAME AS SUPPLIER, PURCHASEORDERS.PONUMBER, LOCATIONS.NAME AS LOCATION, PRODUCTS.NAME AS PRODUCT, PL.QUANTITY AS QTY, PL.QUANTITYRECEIVED AS TRQTY, STOCKDIARY.UNITS AS RQTY, STOCKDIARY.PRICE, STOCKDIARY_GRN.GRNUMBER')->from($table_name);
        //$this->db->group_by("STOCKDIARY_GRN.GRNUMBER");
        if ($datefrom && $dateto) {
            $where = '(YEAR(STOCKDIARY.DATENEW) >= YEAR("' . $datefrom . '")';
            $where = $where . 'AND YEAR(STOCKDIARY.DATENEW) <= YEAR("' . $dateto . '"))';
            $where = $where . 'AND (MONTH(STOCKDIARY.DATENEW) >= MONTH("' . $datefrom . '")';
            $where = $where . 'AND MONTH(STOCKDIARY.DATENEW) <= MONTH("' . $dateto . '"))';
            $where = $where . 'AND (DAY(STOCKDIARY.DATENEW) >= DAY("' . $datefrom . '")';
            $where = $where . 'AND DAY(STOCKDIARY.DATENEW) <= DAY("' . $dateto . '"))';
            $this->db->where($where);
            //$this->db->where("STOCKDIARY.DATENEW <=", date('d-m-Y h:i:s', $dateto));
        } else if ($datefrom) {
            $where = 'YEAR(STOCKDIARY.DATENEW) = YEAR("' . $datefrom . '")';
            $where = $where . 'AND MONTH(STOCKDIARY.DATENEW) = MONTH("' . $datefrom . '")';
            $where = $where . 'AND DAY(STOCKDIARY.DATENEW) = DAY("' . $datefrom . '")';
            $this->db->where($where);
        }
        if ($sid) {
            $this->db->where("PURCHASEORDERS.SUPPLIER", $sid);
        }
         if ($po) {
            $this->db->where("PURCHASEORDERS.PONUMBER", $po);
        }
         if ($grn) {
            $this->db->where("STOCKDIARY_GRN.GRNUMBER", $grn);
        }
        $this->db->where('PL.ID = STOCKDIARY_GRN.REFID AND STOCKDIARY.ID=STOCKDIARY_GRN.STOCKDIARYID');

        $this->db->join('PRODUCTS', 'PRODUCTS.ID = STOCKDIARY.PRODUCT', 'LEFT');
        $this->db->join('PURCHASEORDERS', 'PL.POID = PURCHASEORDERS.ID');
        $this->db->join('SUPPLIERS', 'PURCHASEORDERS.SUPPLIER = SUPPLIERS.ID', 'LEFT');
        $this->db->join('LOCATIONS', 'LOCATIONS.ID = PURCHASEORDERS.LOCATION', 'LEFT');

         $this->db->order_by('STOCKDIARY_GRN.GRNUMBER, PRODUCTS.NAME', 'ASC');
//



        $rows = $this->db->get()->result_array();
        $i = 0;

        foreach ($rows as $row) {
             $table_name = "STOCKDIARY_GRN";
            $this->db->select('SUM(STOCKDIARY.UNITS) AS REVQTY')->from($table_name);
             $this->db->where('STOCKDIARY_GRN.REFID',$row['SDID']);
              $this->db->join('STOCKDIARY', 'STOCKDIARY.ID = STOCKDIARY_GRN.STOCKDIARYID');
             $this->db->group_by('STOCKDIARY_GRN.REFID');
             $rev = $this->db->get()->result_array();
             //var_dump($rev);
            if($rev) $revqty = $rev[0]['REVQTY'];
            else $revqty = "0";

            $data['rows'][$i]['id'] = $i + 1;
            $data['rows'][$i]['cell'] = array(null, $row['ID'], $row['SDID'], $row['DATENEW'], $row['SUPPLIER'], $row['PONUMBER'], $row['LOCATION'], $row['PRODUCT'], $row['QTY'], $row['TRQTY'], $row['RQTY'], $revqty, $row['PRICE'], $row['GRNUMBER']);

            $i++;
        }
        return json_encode($data);
    }

    //ADDED BY SHAMEER ON 18-7-2011. USED FOR PRINTING
   public function getgrnlinesbygrn($grn) {
        $table_name = "STOCKDIARY_GRN AS GRN, PURCHASEORDERLINES AS PL, STOCKDIARY AS SD";
//        $this->db->select('PRODUCTS.REFERENCE, PRODUCTS.NAME, SD.DATENEW AS DATEIN, SD.REASON, SD.UNITS AS QUANTITY, SD.PRICE, UNITS.NAME AS UOM, LOCATIONS.NAME AS LOCATION')->from($table_name);
        $this->db->select('PRODUCTS.REFERENCE, PRODUCTS.NAME, SD.DATENEW AS DATEIN,SUPPLIERS.NAME AS SUPPLIER,SD.REASON, PL.QUANTITY AS POQTY, PL.QUANTITYRECEIVED, SD.UNITS AS RCVDQTY, PL.UNITPRICE AS POPRICE, SD.PRICE AS RCVDPRICE, PL.TAXAMOUNT, UNITS.NAME AS UOM')->from($table_name);
        $this->db->where('GRN.GRNUMBER', $grn);
        $where = 'GRN.REFID = PL.ID AND GRN.STOCKDIARYID = SD.ID';
        $this->db->where($where);

        $this->db->join('PRODUCTS', 'PL.PRODUCT = PRODUCTS.ID AND PL.PRODUCT=SD.PRODUCT');
        $this->db->join('UNITS', 'PL.UNIT = UNITS.ID', 'LEFT');
        $this->db->join('LOCATIONS', 'SD.LOCATION=LOCATIONS.ID','LEFT');
        $this->db->join('SUPPLIERS', 'SD.SUPPLIER=SUPPLIERS.ID','LEFT');

        return $this->db->get()->result();
    }

    //ADDED BY SHAMEER ON 18-7-2011. USED FOR PRINTING
 public function getgrnlineswithoutpo($grn) {
      $table_name = "STOCKDIARY_GRN AS GRN, STOCKDIARY AS SD";
//        $this->db->select('PRODUCTS.REFERENCE, PRODUCTS.NAME, SD.DATENEW AS DATEIN, SD.REASON, SD.UNITS AS QUANTITY, UNITS.NAME AS UOM,SD.PRICE, LOCATIONS.NAME AS LOCATION')->from($table_name);
         $this->db->select('LOCATIONS.NAME AS LOCATION,LOCATIONS.NAME AS LOCATION, PRODUCTS.REFERENCE, PRODUCTS.NAME, SD.DATENEW AS DATEIN,SUPPLIERS.NAME AS SUPPLIER,SD.REASON, SD.UNITS AS QUANTITY, SD.PRICE, UNITS.NAME AS UOM,(SD.UNITS*SD.PRICE)AS TOTAL')->from($table_name);
        $this->db->where('GRN.GRNUMBER', $grn);
        $where = 'GRN.STOCKDIARYID = SD.ID';
        $this->db->where($where);
        $this->db->join('PRODUCTS', 'PRODUCTS.ID=SD.PRODUCT');
        $this->db->join('LOCATIONS', 'SD.LOCATION=LOCATIONS.ID','LEFT');
        $this->db->join('MATERIALS_UNITS', 'MATERIALS_UNITS.MATERIAL = PRODUCTS.ID','LEFT');
        $this->db->join('UNITS', 'MATERIALS_UNITS.UNIT = UNITS.ID','LEFT');
         $this->db->join('SUPPLIERS', 'SD.SUPPLIER=SUPPLIERS.ID','LEFT');
        return $this->db->get()->result();
    }

    //ADDED BY SHAMEER ON 21-7-2011. USED FOR UPDATING PO TO SENT STATUS
    function getgrnbypo($poid) {

        $this->db->select('STOCKDIARY_GRN.GRNUMBER AS GRN, STOCKDIARY.ID AS SDID, STOCKDIARY.DATENEW AS DATE, LOCATIONS.NAME AS LOCATION');
        $this->db->where('PO.ID', $poid);
        $this->db->group_by('GRN');
        $this->db->join('PURCHASEORDERLINES AS PL', 'PL.POID = PO.ID');
        $this->db->join('STOCKDIARY_GRN', 'STOCKDIARY_GRN.REFID = PL.ID');
        $this->db->join('STOCKDIARY', 'STOCKDIARY_GRN.STOCKDIARYID = STOCKDIARY.ID');


        $this->db->join('LOCATIONS', 'LOCATIONS.ID = PO.LOCATION');
        $rows = $this->db->get("PURCHASEORDERS AS PO")->result_array();

//Format data

        $data['page'] = "1";
        $data['total'] = "1";
        $data['records'] = "10";

        $i = 0;
        foreach ($rows as $row) {

              $data['rows'][$i]['id'] = $i + 1;
            $data['rows'][$i]['cell'] = array($row['GRN'], $row['DATE'], $row['LOCATION']);
            $i++;

            $table_name = "STOCKDIARY_GRN";
            $this->db->select('STOCKDIARY_GRN.GRNUMBER AS GRN, STOCKDIARY.DATENEW AS DATE, LOCATIONS.NAME AS LOCATION')->from($table_name);
             $this->db->where('STOCKDIARY_GRN.REFID',$row['SDID']);
              $this->db->join('STOCKDIARY', 'STOCKDIARY.ID = STOCKDIARY_GRN.STOCKDIARYID');
              $this->db->join('LOCATIONS', 'LOCATIONS.ID = STOCKDIARY.LOCATION');
             $rev = $this->db->get()->result_array();

             foreach ($rev as $rowrev) {
                  $data['rows'][$i]['id'] = $i + 1;
            $data['rows'][$i]['cell'] = array($rowrev['GRN'], $rowrev['DATE'], $rowrev['LOCATION']);
            $i++;
             }


        }

        return json_encode($data);
    }

    
    
   function getpoidbygrn($grn) {
        $this->db->select('PL.POID');
        $this->db->where('GRNUMBER', $grn);
        $this->db->join('PURCHASEORDERLINES PL', 'STOCKDIARY_GRN.REFID = PL.ID');
        $q = $this->db->get('STOCKDIARY_GRN')->result();
        if($q)
        return $q[0]->POID;
        else
            return false;
    }

function updateProductCost($pid, $price) {
        $this->db->where('PRODUCTS.ID', $pid);
        $this->db->set('PRICEBUY', $price);
        return $this->db->update('PRODUCTS');
    }
    
}
