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

class salereport_model extends CI_Model {

public function __construct() {
parent::__construct();
}
 function getproductsale($category,$datefrom,$dateto) {
/*
$this->db->from("RECEIPTS R");
$this->db->join('TICKETLINES TL', 'TL.TICKET = R.ID','LEFT');
$this->db->join('PRODUCTS P', 'P.ID = TL.PRODUCT','LEFT');
$this->db->join('CLOSEDCASH CC', 'CC.MONEY = R.MONEY','LEFT');

if ($datefrom && $dateto) {
            $this->db->where("R.DATENEW >=", $datefrom);
            $this->db->where("R.DATENEW <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("R.DATENEW >=", $datefrom);
        }

    if($category==3){
       $this->db->join('TARIFFAREAS TA', 'TA.ID = ExtractValue(CAST(R.ATTRIBUTES AS binary), \'//entry[@key="TariffID"]\')','LEFT',false);
       $this->db->select("TA.NAME AS CATEGORY, P.CODE , P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS,SUM(TL.UNITS*P.PRICEBUY) AS COST, (SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
       $this->db->group_by("TA.ID");
       $this->db->group_by("P.ID");
       
    }
   
    else if($category==2){
        $this->db->join('TICKETS T', 'T.ID = TL.TICKET');
        $this->db->join('PEOPLE U', 'U.ID = T.PERSON');
        $this->db->select("U.NAME AS CATEGORY, P.CODE , P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS,SUM(TL.UNITS*P.PRICEBUY) AS COST, (SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
       $this->db->group_by("U.ID");
       $this->db->group_by("P.ID");
       $this->db->order_by("UNITS","DESC");
    }
    else if($category==4){
        $this->db->join('LOCATIONS_PROD LP', 'LP.PRODUCT = TL.PRODUCT','LEFT');
        $this->db->join('LOCATIONS L', 'L.ID = LP.LOCATION','LEFT');
        $this->db->select("SUM(TL.UNITS) AS CATEGORY, P.CODE, P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS, SUM(TL.UNITS*P.PRICEBUY) AS COST,(SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
        $this->db->group_by("L.ID");
        $this->db->group_by("P.ID");
    }
    else if($category==5){
        $this->db->join('CATEGORIES C', 'C.ID = P.CATEGORY', 'LEFT');
        $this->db->select("TL.PRICE AS CATEGORY, P.CODE , P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS, SUM(TL.UNITS*P.PRICEBUY) AS COST,(SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
        $this->db->group_by("C.ID");
        $this->db->group_by("P.ID");
    }
    else if($category==6){
        
        $this->db->join('PRODUCTGROUPS_PROD PGP', 'PGP.PRODUCT = TL.PRODUCT', 'LEFT');
        $this->db->join('PRODUCTGROUPS PG', 'PG.ID = PGP.PRODUCTGROUP', 'LEFT');
        $this->db->select("PG.NAME AS CATEGORY, P.CODE , P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS, SUM(TL.UNITS*P.PRICEBUY) AS COST,(SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
        $this->db->group_by("PGP.PRODUCTGROUP");
        $this->db->group_by("P.ID");
    }
    else{
        $this->db->join('CATEGORIES C', 'C.ID = P.CATEGORY', 'LEFT');
        $this->db->select("C.NAME AS CATEGORY, P.CODE , P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS,SUM(TL.UNITS*P.PRICEBUY) AS COST, (SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
       $this->db->group_by("C.ID");
       $this->db->group_by("P.ID");
    }
        //echo $this->db->last_query();
        $rows = $this->db->get()->result();
       foreach ($rows as $value) {
            $row_data[] = array(
                'CATEGORY' => $value->CATEGORY,
                'CODE' => $value->CODE,
                'PRODUCT' => $value->PRODUCT,
                'UNITS' => $value->UNITS,
                'COST' => $value->COST,
                'TOTAL' => $value->TOTAL,
                 'PROFIT' => $value->TOTAL-$value->COST,
            );
        }
        return $row_data;*/




$this->db->from("RECEIPTS R");
$this->db->join('TICKETLINES TL', 'TL.TICKET = R.ID','LEFT');
$this->db->join('PRODUCTS P', 'P.ID = TL.PRODUCT','LEFT');
$this->db->join('CLOSEDCASH CC', 'CC.MONEY = R.MONEY','LEFT');



if ($datefrom && $dateto) {
            $this->db->where("R.DATENEW >=", $datefrom);
            $this->db->where("R.DATENEW <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("R.DATENEW >=", $datefrom);
        }

    if($category==3){
       $this->db->join('TARIFFAREAS TA', 'TA.ID = ExtractValue(CAST(R.ATTRIBUTES AS binary), \'//entry[@key="TariffID"]\')','LEFT',false);
       $this->db->select("TA.NAME AS CATEGORY, P.CODE , P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS,SUM(TL.UNITS*P.PRICEBUY) AS COST, (SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
       $this->db->group_by("TA.ID");
       $this->db->group_by("P.ID");
       
    }
   
    else if($category==2){
        $this->db->join('TICKETS T', 'T.ID = TL.TICKET');
        $this->db->join('PEOPLE U', 'U.ID = T.PERSON');
        $this->db->select("U.NAME AS CATEGORY, P.CODE , P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS,SUM(TL.UNITS*P.PRICEBUY) AS COST, (SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
        $this->db->group_by("U.ID");
       $this->db->group_by("P.ID");
       $this->db->order_by("UNITS","DESC");
    }
    else if($category==4){
        $this->db->join('LOCATIONS_PROD LP', 'LP.PRODUCT = TL.PRODUCT','LEFT');
        $this->db->join('LOCATIONS L', 'L.ID = LP.LOCATION','LEFT');
        $this->db->select("SUM(TL.UNITS) AS CATEGORY, P.CODE, P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS, SUM(TL.UNITS*P.PRICEBUY) AS COST,(SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
        $this->db->group_by("L.ID");
        $this->db->group_by("P.ID");
    }
    else if($category==5){
        $this->db->join('CATEGORIES C', 'C.ID = P.CATEGORY', 'LEFT');
        $this->db->select("TL.PRICE AS CATEGORY, P.CODE , P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS, SUM(TL.UNITS*P.PRICEBUY) AS COST,(SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
        $this->db->group_by("C.ID");
        $this->db->group_by("P.ID");
    }
    else if($category==6){
        
        $this->db->join('PRODUCTGROUPS_PROD PGP', 'PGP.PRODUCT = TL.PRODUCT', 'LEFT');
        $this->db->join('PRODUCTGROUPS PG', 'PG.ID = PGP.PRODUCTGROUP', 'LEFT');
        $this->db->select("PG.NAME AS CATEGORY, P.CODE , P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS, SUM(TL.UNITS*P.PRICEBUY) AS COST,(SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
        $this->db->group_by("PGP.PRODUCTGROUP");
        $this->db->group_by("P.ID");
    }
    else{
        $this->db->join('CATEGORIES C', 'C.ID = P.CATEGORY', 'LEFT');
        $this->db->select("C.NAME AS CATEGORY, P.CODE , P.NAME AS PRODUCT, SUM(TL.UNITS) AS UNITS,SUM(TL.UNITS*P.PRICEBUY) AS COST, (SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
        $this->db->group_by("C.ID");
        $this->db->group_by("P.ID");
        //$this->db->where("C.NAME is NOT NULL ");
        //$this->db->where("P.NAME is NOT NULL ");
    }

        $rows = $this->db->get()->result();
//        echo $this->db->last_query();exit;
        //die();
       $row_data = array();
        foreach ($rows as $value) {
            $row_data[] = array(
                'CATEGORY' => $value->CATEGORY,
                'CODE' => $value->CODE,
                'PRODUCT' => $value->PRODUCT,
                'UNITS' => $value->UNITS,
                'COST' => $value->COST,
                'TOTAL' => $value->TOTAL,
                 'PROFIT' => $value->TOTAL-$value->COST,
            );
        }
        return $row_data;

    }
    
 
  
}


