<?php

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

class Inventory_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }
 function getproductlabels($category, $product) {

        $table_name = "PRODUCTS";
        $this->db->select("PRODUCTS.REFERENCE,PRODUCTS.NAME AS PRODUCT,CATEGORIES.NAME AS CATEGORY,PRODUCTS.PRICESELL")->from($table_name);

        if ($category) {
            $this->db->where("CATEGORIES.NAME", $category);
        }
        if ($product) {
            $this->db->like("PRODUCTS.NAME", $product);
        }
        $this->db->join('CATEGORIES', 'CATEGORIES.ID=PRODUCTS.CATEGORY');

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

  function getproductsales($category, $product) {

        $table_name = "STOCKDIARY";
      $this->db->select("CLOSEDCASH.HOST AS CASHIER, STOCKDIARY.DATENEW, PRODUCTS.REFERENCE, PRODUCTS.NAME AS PRODUCT, STOCKDIARY.UNITS, STOCKDIARY.PRICE")->from($table_name);
         //$this->db->select("STOCKDIARY.DATENEW, PRODUCTS.REFERENCE, PRODUCTS.NAME AS PRODUCT, STOCKDIARY.UNITS, STOCKDIARY.PRICE")->from($table_name);
        if ($category) {
            $this->db->like("CATEGORIES.NAME", $category);
        }

        if ($product) {
            $this->db->like("PRODUCTS.NAME", $product);
        }
        $this->db->join('PRODUCTS', 'STOCKDIARY.PRODUCT=PRODUCTS.ID');
         $this->db->join('CATEGORIES', 'CATEGORIES.ID = PRODUCTS.CATEGORY','LEFT');
        $this->db->join('RECEIPTS', 'RECEIPTS.DATENEW = STOCKDIARY.DATENEW','LEFT');
        $this->db->join('CLOSEDCASH', 'CLOSEDCASH.MONEY = RECEIPTS.MONEY','LEFT');
        return $this->db->get()->result();
  }
  
  public function getinventorylinesbyid($warehouse, $category, $product,$productgroups) {
        $table_name = "STOCKCURRENT";
            $this->db->select(' CATEGORIES.NAME AS CATEGORY,PRODUCTS.REFERENCE,PRODUCTS.NAME AS PRODUCT,LOCATIONS.NAME AS LOCATION,STOCKCURRENT.UNITS,PRODUCTS.PRICEBUY AS UNITPRICE,(STOCKCURRENT.UNITS*PRODUCTS.PRICEBUY) AS LINETOTAL,STOCKLEVEL.STOCKSECURITY, STOCKLEVEL.STOCKMAXIMUM')->from($table_name);
        if ($warehouse) {
            $this->db->like("LOCATIONS.NAME", $warehouse);
        }
        if ($category) {
            $this->db->like("CATEGORIES.NAME", $category);
        }
        if ($product) {
            $this->db->like("PRODUCTS.NAME", $product);
        }
        if ($productgroups) {
            $this->db->like("PRODUCTGROUPS.NAME", $productgroups);
        }
        $this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT=PRODUCTS.ID');
        $this->db->join('STOCKLEVEL','PRODUCTS.ID = STOCKLEVEL.PRODUCT AND STOCKCURRENT.LOCATION = STOCKLEVEL.LOCATION','LEFT');
        $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY = CATEGORIES.ID');
        $this->db->join('LOCATIONS', 'STOCKCURRENT.LOCATION=LOCATIONS.ID');
        $this->db->join('PRODUCTGROUPS_PROD', 'PRODUCTGROUPS_PROD.PRODUCT=PRODUCTS.ID','LEFT');
        $this->db->join('PRODUCTGROUPS', 'PRODUCTGROUPS.ID=PRODUCTGROUPS_PROD.PRODUCTGROUP','LEFT');
            $this->db->group_by("CATEGORIES.ID");
            $this->db->group_by("PRODUCTS.ID");
            $this->db->group_by("LOCATIONS.ID");
            $this->db->group_by("LOCATIONS.ID");
            
        return $this->db->get()->result();
   }

   public function getinventorybrokenbyid($warehouse, $category, $product,$productgroups) {
        $table_name = "STOCKCURRENT";
            $this->db->select(' CATEGORIES.NAME AS CATEGORY,PRODUCTS.REFERENCE,PRODUCTS.NAME AS PRODUCT,LOCATIONS.NAME AS LOCATION,STOCKCURRENT.UNITS,UNITS.NAME AS UOM,STOCKLEVEL.STOCKSECURITY, STOCKLEVEL.STOCKMAXIMUM')->from($table_name);
            $this->db->where('STOCKCURRENT.UNITS < STOCKLEVEL.STOCKSECURITY');
        if ($warehouse) {
            $this->db->like("LOCATIONS.NAME", $warehouse);
        }
        if ($category) {
            $this->db->like("CATEGORIES.NAME", $category);
        }
          if ($product) {
            $this->db->like("PRODUCTS.NAME", $product);
        }
         if ($productgroups) {
                $this->db->like("PRODUCTGROUPS.NAME", $productgroups);
        }

        $this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT=PRODUCTS.ID');
        $this->db->join('STOCKLEVEL','PRODUCTS.ID = STOCKLEVEL.PRODUCT AND STOCKCURRENT.LOCATION = STOCKLEVEL.LOCATION','LEFT');
        $this->db->join('CATEGORIES', 'PRODUCTS.CATEGORY = CATEGORIES.ID','LEFT');
        $this->db->join('LOCATIONS', 'STOCKCURRENT.LOCATION=LOCATIONS.ID','LEFT');
        $this->db->join('PRODUCTGROUPS_PROD', 'PRODUCTGROUPS_PROD.PRODUCT=PRODUCTS.ID','LEFT');
        $this->db->join('PRODUCTGROUPS', 'PRODUCTGROUPS.ID=PRODUCTGROUPS_PROD.PRODUCTGROUP','LEFT');
        $this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID','LEFT');
        $this->db->join('UNITS','UNITS.ID = MATERIALS_UNITS.UNIT','LEFT');
            $this->db->group_by("CATEGORIES.ID");
            $this->db->group_by("PRODUCTS.ID");
            $this->db->group_by("LOCATIONS.ID");
            $this->db->group_by("MATERIALS_UNITS.MATERIAL");
            $this->db->group_by("UNITS.ID");
        return $this->db->get()->result();
    }
      
    public function getstocklinesbyid($warehouse,$product,$reason,$materialcategory,$taxcategory,$datefrom,$dateto) {
        
        $table_name = "STOCKDIARY";
        $this->db->select('LOCATIONS.NAME AS LOCATION, STOCKDIARY.REASON,STOCKDIARY.DATENEW,PRODUCTS.NAME AS PRODUCT,STOCKDIARY.UNITS,STOCKDIARY.PRICE,(STOCKDIARY.UNITS * STOCKDIARY.PRICE) AS TOTAL')->from($table_name);
        if ($warehouse) {
            $this->db->like("LOCATIONS.NAME", $warehouse);
        }
        if ($product) {
            $this->db->like("PRODUCTS.NAME", $product);
        }
        if ($reason) {
            $this->db->where("STOCKDIARY.REASON", $reason);
        }
         if ($materialcategory) {
            $this->db->like("CATEGORIES.NAME", $materialcategory);
//             $this->db->where("CATEGORIES.PARENTID == -1");
        }
          if ($taxcategory) {
            $this->db->like("TAXCATEGORIES.NAME", $taxcategory);
        }
        if ($datefrom && $dateto) {
            $this->db->where("DATENEW >=", $datefrom);
            $this->db->where("DATENEW <=", $dateto."+1 day");
        } else if ($datefrom) {
            $this->db->where("DATENEW LIKE", $datefrom."%");
        }
        $this->db->join('LOCATIONS', 'STOCKDIARY.LOCATION=LOCATIONS.ID','LEFT');
        $this->db->join('PRODUCTS', 'STOCKDIARY.PRODUCT=PRODUCTS.ID','LEFT');
        $this->db->join('TAXCATEGORIES','PRODUCTS.TAXCAT = TAXCATEGORIES.ID','LEFT');
           $this->db->join('CATEGORIES','PRODUCTS.CATEGORY = CATEGORIES.ID','LEFT');
          //$this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID','LEFT');
         //$this->db->join('UNITS','UNITS.ID = MATERIALS_UNITS.UNIT');
                     return $this->db->get()->result();
    }
    
    public function getdeadstocklinesbyid($warehouse,$datefrom,$dateto) {
        $table_name = "STOCKCURRENT";
        $this->db->select('distinct(PRODUCTS.REFERENCE),PRODUCTS.NAME AS PRODUCT,(STOCKCURRENT.UNITS) AS UNITS,STOCKDIARY.DATENEW,')->from($table_name);
          $where = ('REASON != "-1%" AND REASON != "-2" AND REASON != "-3" AND REASON != "-4" AND REASON != "2" AND REASON != "4"  AND REASON != "5" AND REASON != "1000"');
          $this->db->where($where);
          
          if ($warehouse) {
            $this->db->like("LOCATIONS.NAME", $warehouse);
          }
          if ($datefrom && $dateto) {
            $this->db->where("DATENEW >=", $datefrom);
            $this->db->where("DATENEW <=", $dateto."+1 day");
          } 
        else if ($datefrom) {
            $this->db->where("DATENEW LIKE", $datefrom."%");
        }
         $this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT=PRODUCTS.ID','LEFT');
         $this->db->join('STOCKDIARY', 'STOCKDIARY.PRODUCT=STOCKCURRENT.PRODUCT','LEFT');
           $this->db->join('LOCATIONS', 'STOCKCURRENT.LOCATION=LOCATIONS.ID','LEFT');
           $this->db->group_by("STOCKDIARY.DATENEW");
               return $this->db->get()->result();
}
     public function getinventory($warehouse,$reason) {
        $table_name = "STOCKDIARY";
        $this->db->select('PRODUCTS.REFERENCE,PRODUCTS.NAME AS PRODUCT, STOCKDIARY.UNITS AS IN')->from($table_name);
        if ($warehouse) {
            $this->db->like("LOCATIONS.NAME",$warehouse);
        }
         if ($reason) {
            $this->db->like("STOCKDIARY.REASON",$reason);
         }
        
           $this->db->join('PRODUCTS', 'STOCKDIARY.PRODUCT=PRODUCTS.ID,SUM(STOCKDIARY.IN * STOCKDIARY.OUT) AS DIFFERENCE');

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

    }
     

    public function getproductlinesbyid($category, $product) {
        $table_name = "PRODUCTS";
        $this->db->select("PRODUCTS.REFERENCE,PRODUCTS.NAME,CATEGORIES.NAME AS CATEGORY,TAXES.RATE AS TAX,PRODUCTS.PRICEBUY AS COST, PRODUCTS.PRICESELL AS PRICE")->from($table_name);

        if ($category) {
            $this->db->where("CATEGORIES.NAME", $category);
        }
        if ($product) {
            $this->db->like("PRODUCTS.NAME", $product);
        }
        $this->db->join('TAXES', 'TAXES.CATEGORY=PRODUCTS.TAXCAT');
        $this->db->join('CATEGORIES', 'CATEGORIES.ID=PRODUCTS.CATEGORY');
         return $this->db->get()->result();
          }
    public function getcataloglinesbyid($category,$product)
    {
       $table_name = "PRODUCTS";
        $this->db->select("PRODUCTS.REFERENCE,PRODUCTS.NAME,CATEGORIES.NAME AS CATEGORY,TAXES.RATE AS TAX,PRODUCTS.PRICEBUY AS COST, PRODUCTS.PRICESELL AS PRICE")->from($table_name);

        if ($product) {
            $this->db->like("PRODUCTS.NAME", $product);
        }

        if ($category) {
            $this->db->where("CATEGORIES.NAME", $category);
        }
        $this->db->join('TAXES', 'TAXES.ID=PRODUCTS.TAXCAT', 'LEFT');
        $this->db->join('CATEGORIES', 'CATEGORIES.ID=PRODUCTS.CATEGORY');
         return $this->db->get()->result();
          
    }
    public function getwarehouse($warehouse,$category,$product)
    {
       $table_name = "PRODUCTS";
            $this->db->select("PRODUCTS.REFERENCE,LOCATIONS.NAME AS LOCATION,CATEGORIES.NAME AS CATEGORY,PRODUCTS.NAME,PRODUCTS.PRICEBUY AS COST, PRODUCTS.PRICESELL AS SELL,STOCKLEVEL.STOCKSECURITY,STOCKLEVEL.STOCKMAXIMUM")->from($table_name);

        if ($warehouse) {
            $this->db->like("LOCATIONS.NAME", $warehouse);
        }
        if ($category) {
            $this->db->like("CATEGORIES.NAME", $category);
        }
        if ($product) {
            $this->db->where("PRODUCTS.NAME", $product);
        }

        $this->db->join('STOCKLEVEL', 'STOCKLEVEL.PRODUCT=PRODUCTS.ID', 'LEFT');
         $this->db->join('CATEGORIES', 'CATEGORIES.ID=PRODUCTS.CATEGORY');
           $this->db->join('LOCATIONS', 'STOCKLEVEL.LOCATION=LOCATIONS.ID');
           
        return $this->db->get()->result();
          
    }
    public function getassets($reference, $product,$category)
    {
       $table_name = "CATEGORIES";
            $this->db->select("PRODUCTS.REFERENCE,PRODUCTS.NAME AS PRODUCT,CATEGORIES.NAME AS CATEGORY")->from($table_name);
            $this->db->where("CATEGORIES.PARENTID = '-2'");
        if ($reference) {
            $this->db->like("PRODUCTS.REFERENCE", $reference);
        }
        if ($product) {
            $this->db->where("PRODUCTS.NAME", $product);
        }
        if ($category) {
            $this->db->like("CATEGORIES.NAME", $category);
        }
         $this->db->join('PRODUCTS', 'CATEGORIES.ID = PRODUCTS.CATEGORY');
                   return $this->db->get()->result();
    }
    function getcashbyuserid($datefrom, $dateto,$users, $cash) {

        $table_name = "CLOSEDCASH";
        $this->db->select("PEOPLE.NAME AS USER,CLOSEDCASH.DATESTART,CLOSEDCASH.DATEEND,SUM(PAYMENTS.TOTAL) AS SUM")->from($table_name);
        
        if ($datefrom && $dateto) {
            $this->db->where("DATESTART >=", $datefrom);
            $this->db->where("DATEEND <=", $dateto."+1 day");
        } else if ($datefrom) {
            $this->db->where("DATESTART", $datefrom);
        }
        if ($users) {
            $this->db->where("PEOPLE.NAME", $users);
        }
        $this->db->where("PAYMENTS.PAYMENT", $cash);
        $this->db->where("TICKETS.PERSON");
// $where="PAYMENTS.PAYMENT = 'CASH.'";
//        $this->db->where($where);
        $this->db->join('RECEIPTS', 'CLOSEDCASH.MONEY= RECEIPTS.MONEY', 'LEFT');
        $this->db->join('TICKETS', 'TICKETS.ID=RECEIPTS.ID', 'LEFT');
          $this->db->join('PEOPLE', 'PEOPLE.ID=TICKETS.PERSON','LEFT');
        $this->db->join('PAYMENTS', 'RECEIPTS.ID=PAYMENTS.RECEIPT','LEFT');
       
               return $this->db->get()->result();
            }
//   public function getopenclose($material,$category)
//    {
////       $table_name = "PRODUCTS";
////            $this->db->select("PRODUCTS.NAME AS PRODUCT,STOCKDIARY.UNITS AS OPENSTOCK,MATERIALREQUESTLINES.QUANTITY AS PURCHASE,MATERIALREQUESTLINES.QUANTITYDELIVERED AS ISSUE,STOCKDIARY.PRICE AS AMOUNT")->from($table_name);
////            $this->db->where("CATEGORIES.PARENTID" == '-1');
////        if ($material) {
////            $this->db->like("PRODUCTS.NAME", $material);
////                 }
////        if ($category) {
////            $this->db->like("CATEGORIES.NAME", $category);
////                    }
////        $this->db->join('STOCKDIARY', 'STOCKDIARY.PRODUCT=PRODUCTS.ID', 'LEFT');
////         $this->db->join('CATEGORIES', 'CATEGORIES.ID=PRODUCTS.CATEGORY','LEFT');
////           $this->db->join('MATERIALREQUESTLINES', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTS.ID','LEFT');
////                   return $this->db->get()->result();
//             $table_name = "CATEGORIES";
//            $this->db->select("PRODUCTS.NAME AS PRODUCT,STOCKDIARY.UNITS AS OPENSTOCK,MATERIALREQUESTLINES.QUANTITY AS PURCHASE,MATERIALREQUESTLINES.QUANTITYDELIVERED AS ISSUE,STOCKDIARY.PRICE AS AMOUNT")->from($table_name);
//             $where = 'PARENTID = "-1"';
//            $this->db->where($where);
//        if ($material) {
//            $this->db->like("PRODUCTS.NAME", $material);
//                 }
//        if ($category) {
//            $this->db->like("CATEGORIES.NAME", $category);
//                    }
//                 $this->db->join('PRODUCTS', 'CATEGORIES.ID=PRODUCTS.CATEGORY','LEFT');
//                 $this->db->join('STOCKDIARY', 'STOCKDIARY.PRODUCT=PRODUCTS.ID', 'LEFT');
//           $this->db->join('MATERIALREQUESTLINES', 'MATERIALREQUESTLINES.PRODUCT=PRODUCTS.ID','LEFT');
//                   return $this->db->get()->result();
//          }
}