<?php

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

class databoxes_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }
    
    function currentsequence() {
        $this->db->select('CLOSEDCASH.HOST, CLOSEDCASH.HOSTSEQUENCE, CLOSEDCASH.DATESTART, CLOSEDCASH.DATEEND , SUM(PAYMENTS.TOTAL) as TOTALAMOUUNT', false);
        $this->db->where('CLOSEDCASH.DATEEND IS NULL');
        $this->db->join('RECEIPTS', 'RECEIPTS.MONEY = CLOSEDCASH.MONEY');
        $this->db->join('PAYMENTS', 'RECEIPTS.ID = PAYMENTS.RECEIPT');
        $this->db->group_by('CLOSEDCASH.MONEY');
        return $this->db->get('CLOSEDCASH')->result();
    }
    
    function salestotal() {
        
        $this->db->select('COUNT(T.ID) SALESCOUNT, IFNULL(SUM(P.TOTAL),0) SALESTOTAL ', false);
        $this->db->join('RECEIPTS R', 'R.MONEY = C.MONEY');
        $this->db->join('PAYMENTS P', 'P.RECEIPT = R.ID');
        $this->db->join('TICKETS T', 'T.ID = R.ID AND T.TICKETTYPE = 0');
        $this->db->where('C.DATEEND IS NULL');
        $query = $this->db->get('CLOSEDCASH C');
        return $query->row(); 
    }
    
    function paymentstotal()
    {
        $this->db->select('COUNT(*) AS PAYMENTS, SUM(P.TOTAL) AS TOTAL', false);
        $this->db->join('RECEIPTS R', 'C.MONEY = R.MONEY');
        $this->db->join('PAYMENTS P', 'P.RECEIPT = R.ID');
        $this->db->where('C.DATEEND IS NULL');
        $query = $this->db->get('CLOSEDCASH C');
        
        if($query!=null)
            return $query->row(); 
        else
            return null;
    }
    
    function payments()
    {
        $this->db->select('P.PAYMENT, IFNULL(SUM(P.TOTAL),0) AS TOTAL, P.NOTES', false);
        $this->db->join('RECEIPTS R', 'C.MONEY = R.MONEY');
        $this->db->join('PAYMENTS P', 'P.RECEIPT = R.ID');
        $this->db->where('C.DATEEND IS NULL');
        $this->db->group_by('P.PAYMENT, P.NOTES');
        return $this->db->get('CLOSEDCASH C')->result();
    }
    
    function salesbycashier() { 
        $this->db->select('P.NAME AS CASHIER, MIN(R.DATENEW) AS STARTDATE, MAX(R.DATENEW) AS ENDDATE, SUM(TL.UNITS * TL.PRICE) AS TOTAL', false);
        $this->db->join('RECEIPTS R', 'C.MONEY = R.MONEY');
        $this->db->join('TICKETS T', 'T.ID = R.ID AND T.TICKETTYPE = 0');
        $this->db->join('TICKETLINES TL', 'TL.TICKET = T.ID');
        $this->db->join('PEOPLE P', 'P.ID=T.PERSON');
        $this->db->where('C.DATEEND IS NULL');
        $this->db->group_by('P.ID,R.MONEY,C.HOST');
        $this->db->order_by('TOTAL', 'DESC');
        return$this->db->get('CLOSEDCASH C')->result();
//        echo $this->db->last_query();exit;
    }
    
    function lastreceipts($count)
    {
        $this->db->select('T.TICKETID AS TICKETNO, R.DATENEW AS TICKETDATE, SUM(TL.UNITS * TL.PRICE) AS TOTAL, SUM(TL.UNITS) AS QTY, CU.NAME AS CUSTOMER', false);
        $this->db->join('RECEIPTS R', 'C.MONEY = R.MONEY');
        $this->db->join('TICKETS T', 'T.ID = R.ID AND T.TICKETTYPE = 0');
        $this->db->join('TICKETLINES TL', 'TL.TICKET = T.ID');
        $this->db->join('CUSTOMERS CU', 'T.CUSTOMER = CU.ID', 'LEFT');
        $this->db->where('C.DATEEND IS NULL');
        $this->db->order_by('R.DATENEW', 'DESC');
        $this->db->group_by('TL.TICKET');
        return $this->db->get('CLOSEDCASH C', $count)->result();
    }
    
    function lastrefunds($count)
    {
        $this->db->select('T.TICKETID AS TICKETNO, R.DATENEW AS TICKETDATE, SUM(TL.UNITS * TL.PRICE) AS TOTAL, SUM(TL.UNITS) AS QTY, CU.NAME AS CUSTOMER', false);
        $this->db->join('RECEIPTS R', 'C.MONEY = R.MONEY');
        $this->db->join('TICKETS T', 'T.ID = R.ID AND T.TICKETTYPE = 1');
        $this->db->join('TICKETLINES TL', 'TL.TICKET = T.ID');
        $this->db->join('CUSTOMERS CU', 'T.CUSTOMER = CU.ID', 'LEFT');
        $this->db->where('C.DATEEND IS NULL');
        $this->db->order_by('R.DATENEW', 'DESC');
        $this->db->group_by('TL.TICKET');
        return $this->db->get('CLOSEDCASH C', $count)->result();
    }
    
    
    
    function stockvaluebywarehouse()
    {
        $this->db->select('LOCATIONS.NAME AS LOCATION, SUM(PRODUCTS.PRICEBUY * STOCKCURRENT.UNITS) TOTAL', false);
        $this->db->join('LOCATIONS', 'STOCKCURRENT.LOCATION = LOCATIONS.ID');
        $this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT = PRODUCTS.ID');
        $this->db->group_by('STOCKCURRENT.LOCATION, LOCATIONS.NAME');
        $this->db->order_by('LOCATIONS.NAME', 'DESC');
        return $this->db->get('STOCKCURRENT')->result();
    }
    
    function stockvalue()
    {
//        $this->db->select('SUM(PRODUCTS.PRICEBUY * STOCKCURRENT.UNITS) AS TOTAL, COUNT(DISTINCT STOCKCURRENT.LOCATION) AS LOCATIONS',FALSE);
//        $this->db->join('PRODUCTS', 'STOCKCURRENT.PRODUCT = PRODUCTS.ID');
//        $this->db->where('STOCKCURRENT.UNITS>0');
//        //$this->db->group_by('STOCKCURRENT.LOCATION');
        $query = $this->db->query('SELECT SUM(PRODUCTS.PRICEBUY * STOCKCURRENT.UNITS) AS TOTAL, COUNT(DISTINCT STOCKCURRENT.LOCATION) AS LOCATIONS FROM STOCKCURRENT JOIN PRODUCTS ON STOCKCURRENT.PRODUCT = PRODUCTS.ID WHERE STOCKCURRENT.UNITS>0');
        
        if($query)
        return $query->row(); 
        else return '';
    }
    
    
    function stockin($count)
    {
        $this->db->select("STOCKDIARY.DATENEW AS DATE, " .
            "CASE STOCKDIARY.REASON 
                    WHEN '1' THEN '(IN) Purchase' 
                    WHEN '2' THEN '(IN) Refund'  
                    WHEN '4' THEN '(IN) Movement'
                    WHEN '5' THEN '(IN) Production'
                    WHEN '-1' THEN '(OUT) Sale'
                    WHEN '-2' THEN '(OUT) Refund'
                    WHEN '-3' THEN '(OUT) Break'
                    WHEN '-4' THEN '(OUT) Movement'
                    WHEN '-5' THEN '(OUT) Production'
                    WHEN '1000' THEN 'Crossing'  
                    WHEN '999' THEN 'Request'
                    ELSE '' 
                END as REASON,".  
            " LOCATIONS.NAME AS LOCATION, PRODUCTS.NAME, STOCKDIARY.UNITS, STOCKDIARY.PRICE, STOCKCURRENT.UNITS AS STOCK, (STOCKDIARY.PRICE * STOCKCURRENT.UNITS) AS COST", FALSE);
        $this->db->join('PRODUCTS','STOCKDIARY.PRODUCT = PRODUCTS.ID');
        $this->db->join('LOCATIONS','LOCATIONS.ID = STOCKDIARY.LOCATION');
        $this->db->join('STOCKCURRENT','STOCKCURRENT.PRODUCT = STOCKDIARY.PRODUCT AND STOCKCURRENT.LOCATION=STOCKDIARY.LOCATION');
        $this->db->where('STOCKDIARY.REASON > 0 AND STOCKDIARY.REASON != 999');
        $this->db->order_by('STOCKDIARY.DATENEW', 'DESC');
        
        $q = $this->db->get('STOCKDIARY',$count);
        if($q) return $q->result();
        else '';
    }
    
    function stockout($count)
    {
        $this->db->select("STOCKDIARY.DATENEW AS DATE,
            CASE STOCKDIARY.REASON WHEN -1 THEN 'Sale' WHEN -2 THEN 'Refund' WHEN -3 THEN 'Break' WHEN -4 THEN 'Movement' WHEN 5 THEN 'Production' END AS REASON, 
            LOCATIONS.NAME AS LOCATION, PRODUCTS.NAME, STOCKDIARY.UNITS, STOCKDIARY.PRICE, STOCKCURRENT.UNITS AS STOCK, (STOCKDIARY.PRICE * STOCKCURRENT.UNITS) AS COST",FALSE);
        $this->db->join('PRODUCTS','STOCKDIARY.PRODUCT = PRODUCTS.ID');
        $this->db->join('LOCATIONS','LOCATIONS.ID = STOCKDIARY.LOCATION');
        $this->db->join('STOCKCURRENT','STOCKCURRENT.PRODUCT = STOCKDIARY.PRODUCT AND STOCKCURRENT.LOCATION=STOCKDIARY.LOCATION');
        $this->db->where('STOCKDIARY.REASON < 0');
        $this->db->order_by('STOCKDIARY.DATENEW', 'DESC');
        $q = $this->db->get('STOCKDIARY', $count);
        if($q) return $q->result();
        else '';
        
    }
    
    function abcanalysis_list($count, $datefrom)
    {
        $this->db->select("P.NAME, SUM(S.UNITS * -1)  AS UNITS, SUM(S.PRICE * S.UNITS * -1) AS COST");
        $this->db->join('PRODUCTS P','S.PRODUCT = P.ID');
        $this->db->where('S.REASON < 0');
        $this->db->where('S.DATENEW > ',$datefrom);
        $this->db->order_by('COST', 'DESC');
        $this->db->order_by('UNITS', 'DESC');
        $this->db->group_by('S.PRODUCT');
        return $this->db->get('STOCKDIARY S', $count)->result();
    }
    
    function abcanalysis_total($count, $datefrom)
    {
        $this->db->select("SUM(S.PRICE * S.UNITS * -1) AS TOTALCOST");
        $this->db->where('S.REASON < 0');
        $this->db->where('S.DATENEW > ',$datefrom);
        $query = $this->db->get('STOCKDIARY S');
        return $query->row(); 
    }
    
    function topproducts($count)
    {

        $this->db->select("P.NAME,  IFNULL(SUM(TL.UNITS),0) AS UNITS , IFNULL(SUM(TL.UNITS * TL.PRICE),0) AS TOTAL",FALSE);
        $this->db->join('RECEIPTS R', 'C.MONEY = R.MONEY');
        $this->db->join('TICKETLINES TL', 'TL.TICKET = R.ID');
        $this->db->join('PRODUCTS P', 'TL.PRODUCT = P.ID');
        $this->db->where('C.DATEEND IS NULL');
        $this->db->group_by('TL.PRODUCT');
        $this->db->order_by('UNITS', 'DESC');
        $q = $this->db->get('CLOSEDCASH C', $count);
        if($q) return $q->result();
        else '';
    }
    
   
    function weeklysales($datefrom, $dateto)
    {

$this->db->select('IFNULL(SUM(P.TOTAL),0) AS TOTAL', FALSE);
        $this->db->join('PAYMENTS P', 'R.ID = P.RECEIPT');
        $this->db->join('TICKETS T', 'T.ID = R.ID AND T.TICKETTYPE=0', FALSE);
        $this->db->where('R.DATENEW >=', $datefrom);
        $this->db->where('R.DATENEW <=', $dateto);
        $q= $this->db->get('RECEIPTS R');

        if($q->row())
            $income = $q->row()->TOTAL;
        else {
            $income = 0.0;
        }
        return $income;
    }
}