<?php

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

class Purchases_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }
    public function getpurchase($supplier,$product,$materialcategory,$datefrom,$dateto)
    {
        $table_name='PURCHASEORDERS';
        $this->db->select("PURCHASEORDERS.PONUMBER,PURCHASEORDERS.CREATEDDATE,SUPPLIERS.NAME AS SUPPLIER,PRODUCTS.NAME AS MATERIAL,PURCHASEORDERLINES.QUANTITY,PURCHASEORDERLINES.UNITPRICE,UNITS.NAME AS UOM,PURCHASEORDERLINES.TAXAMOUNT,(PURCHASEORDERLINES.QUANTITY*(PURCHASEORDERLINES.UNITPRICE+PURCHASEORDERLINES.TAXAMOUNT)) AS LINETOTAL")->from($table_name);
//        if($warehouse)
//        {
//            $this->db->like('LOCATIONS.NAME',$warehouse);
//        }
        if($supplier){
             $this->db->like('SUPPLIERS.NAME',$supplier);
        }
        if($product){
            $this->db->like('PRODUCTS.NAME',$product);
        }
       if($materialcategory){
          $this->db->like('CATEGORIES.NAME',$materialcategory);
        }
        if ($datefrom && $dateto) {
            $this->db->where("CREATEDDATE >=", $datefrom);
            $this->db->where("CREATEDDATE <=", $dateto."+1 day");
        } else if ($datefrom) {
            $this->db->where("CREATEDDATE LIKE", $datefrom."%");
        }

        $this->db->join('LOCATIONS','LOCATIONS.ID = PURCHASEORDERS.LOCATION');
        $this->db->join('SUPPLIERS','SUPPLIERS.ID = PURCHASEORDERS.SUPPLIER');
        $this->db->join('PURCHASEORDERLINES','PURCHASEORDERLINES.POID = PURCHASEORDERS.ID','LEFT');
         $this->db->join('UNITS', 'PURCHASEORDERLINES.UNIT= UNITS.ID', 'LEFT');
         $this->db->join('PRODUCTS','PURCHASEORDERLINES.PRODUCT = PRODUCTS.ID','LEFT');
          $this->db->join('CATEGORIES','CATEGORIES.ID = PRODUCTS.CATEGORY','LEFT');
//                $this->db->join('MATERIALREQUESTLINES','MATERIALREQUESTLINES.PRODUCT = PRODUCTS.ID');
            $this->db->order_by('PURCHASEORDERS.CREATEDDATE');
        return $this->db->get()-> result();
    }
     public function getrequest($warehouse,$productgroups,$datefrom,$dateto)
    {
        $table_name='MATERIALREQUESTLINES';
        $this->db->select("MATERIALREQUESTS.REQUIREDON,LOCATIONS.NAME AS LOCATION,MATERIALREQUESTLINES.QUANTITY,MATERIALREQUESTLINES.UNIT,PRODUCTS.PRICEBUY AS UNITPRICE")->from($table_name);
        if($warehouse)
        {
            $this->db->where('LOCATIONS.NAME',$warehouse);
        }
        if($productgroups){
        $this->db->select("PRODUCTS.NAME AS PRODUCT");
         $this->db->where('PRODUCTGROUPS.NAME',$productgroups);
      $this->db->join('PRODUCTGROUPS_PROD','PRODUCTGROUPS_PROD.PRODUCT = MATERIALREQUESTLINES.PRODUCT','LEFT');
       $this->db->join('PRODUCTS','PRODUCTS.ID = MATERIALREQUESTLINES.PRODUCT');
       $this->db->join('PRODUCTGROUPS','PRODUCTGROUPS.ID = PRODUCTGROUPS_PROD.PRODUCTGROUP','LEFT');
                      }
          if ($datefrom && $dateto) {
            $this->db->where("MATERIALREQUESTS.REQUIREDON >=", $datefrom);
            $this->db->where("MATERIALREQUESTS.REQUIREDON <=", $dateto."+1 day");
        } else if ($datefrom) {
            $this->db->where("MATERIALREQUESTS.REQUIREDON LIKE", $datefrom."%");
        }
          $this->db->join('MATERIALREQUESTS','MATERIALREQUESTS.ID = MATERIALREQUESTLINES.MATERIALREQUEST');
          $this->db->join('LOCATIONS','LOCATIONS.ID = MATERIALREQUESTS.LOCATION');
          if(!$productgroups)
          {
              $this->db->select("PRODUCTS.NAME AS PRODUCT");
            $this->db->join('PRODUCTS','PRODUCTS.ID = MATERIALREQUESTLINES.PRODUCT');
    }
//        $this->db->join('SUPPLIERS','SUPPLIERS.ID = MATERIALREQUESTLINES.SUPPLIER','LEFT');
         $this->db->order_by('MATERIALREQUESTS.REQUIREDON');
      return $this->db->get()-> result();
    }
    public function getissue($warehouse,$productgroups,$products,$datefrom,$dateto)
    {
          $table_name='MATERIALREQUESTLINES';
        $this->db->select("MATERIALREQUESTS.REQUIREDON,LOCATIONS.NAME AS LOCATION,MATERIALREQUESTLINES.QUANTITYDELIVERED,UNITS.NAME AS UOM,PRODUCTS.PRICEBUY AS UNITPRICE,(MATERIALREQUESTLINES.QUANTITYDELIVERED*PRODUCTS.PRICEBUY) AS LINETOTAL")->from($table_name);
         $this->db->where('MATERIALREQUESTS.REQUESTSTATUS = 3');
        if($warehouse)
                    {
                     $this->db->where('LOCATIONS.NAME',$warehouse);
                     }
                          if($products)
                      {
                       $this->db->where('PRODUCTS.NAME',$products);
                      }
        if($productgroups){
                        $this->db->select("PRODUCTS.NAME AS PRODUCT");
                        $this->db->where('PRODUCTGROUPS.NAME',$productgroups);
                         $this->db->join('PRODUCTGROUPS_PROD','PRODUCTGROUPS_PROD.PRODUCT = MATERIALREQUESTLINES.PRODUCT','LEFT');
                        $this->db->join('PRODUCTS','PRODUCTS.ID = MATERIALREQUESTLINES.PRODUCT');
                        
                        $this->db->join('PRODUCTGROUPS','PRODUCTGROUPS.ID = PRODUCTGROUPS_PROD.PRODUCTGROUP','LEFT');
                      }
  
        if ($datefrom && $dateto) {
                     $this->db->where("MATERIALREQUESTS.REQUIREDON >=", $datefrom);
                     $this->db->where("MATERIALREQUESTS.REQUIREDON <=", $dateto."+1 day");
        } else if ($datefrom) {
                      $this->db->where("MATERIALREQUESTS.REQUIREDON LIKE", $datefrom."%");
        }
          $this->db->join('MATERIALREQUESTS','MATERIALREQUESTS.ID = MATERIALREQUESTLINES.MATERIALREQUEST','LEFT');
          $this->db->join('LOCATIONS','LOCATIONS.ID = MATERIALREQUESTS.LOCATION');
          if(!$productgroups)
          {
              $this->db->select("PRODUCTS.NAME AS PRODUCT");
            $this->db->join('PRODUCTS','PRODUCTS.ID = MATERIALREQUESTLINES.PRODUCT');
    }
//        $this->db->join('SUPPLIERS','SUPPLIERS.ID = MATERIALREQUESTLINES.SUPPLIER','LEFT');
    $this->db->order_by('MATERIALREQUESTS.REQUIREDON');
     $this->db->join('UNITS','UNITS.ID = MATERIALREQUESTLINES.UNIT');
      return $this->db->get()-> result();
    }

}
