<?php

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

class Sales_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }
 function getclosedcash($datefrom,$dateto,$hostsequence) {

        $table_name = "CLOSEDCASH";
        $this->db->select("CLOSEDCASH.HOST,CLOSEDCASH.HOSTSEQUENCE AS SEQUENCE,DATE_FORMAT(CLOSEDCASH.DATESTART, ('%Y-%m-%d')) DATESTART,DATE_FORMAT(CLOSEDCASH.DATEEND, ('%Y-%m-%d')) DATEEND,PAYMENTS.PAYMENT,PAYMENTS.TOTAL")->from($table_name);

        if ($datefrom && $dateto) {
            $this->db->where("DATESTART >=", $datefrom);
            $this->db->where("DATESTART <=", $dateto."+1 day");
             }
        else if ($datefrom) {
            $this->db->where("DATESTART LIKE", $datefrom."%");
        }
        else if ($dateto) {
            $this->db->where("DATESTART <=", $dateto."+1 day");
        }
        if ($hostsequence) {
            $this->db->where("HOSTSEQUENCE =", $hostsequence);
        }
        
        $this->db->join('RECEIPTS','RECEIPTS.MONEY=CLOSEDCASH.MONEY','LEFT');
        $this->db->join('PAYMENTS','PAYMENTS.RECEIPT=RECEIPTS.ID','LEFT');
        
         $this->db->order_by("CLOSEDCASH.DATESTART");
         return $this->db->get()->result();
    }


    /*function getsaleslog($datefrom,$dateto) {
        
        $table_name = "TICKETS";
        $this->db->select("TICKETS.TICKETID,DATE_FORMAT(RECEIPTS.DATENEW,('%Y-%m-%d')) DATENEW,CUSTOMERS.NAME,PAYMENTS.PAYMENT,PAYMENTS.TOTAL")->from($table_name);
        
        if ($datefrom && $dateto) {
            $this->db->where("DATENEW >=", $datefrom);
            $this->db->where("DATENEW <=", $dateto."+1 day");
        } else if ($datefrom) {
            $this->db->where("DATENEW LIKE", $datefrom."%");
        }
//        if($productgroups){
//            $this->db->like("PRODUCTGROUPS.NAME",$productgroups);
//        }
         $this->db->join('RECEIPTS','RECEIPTS.ID=TICKETS.ID','LEFT');
         $this->db->join('PAYMENTS','RECEIPTS.ID=PAYMENTS.RECEIPT','LEFT');
          $this->db->join('CUSTOMERS','TICKETS.CUSTOMER=CUSTOMERS.ID','LEFT');
          $this->db->order_by('RECEIPTS.DATENEW');
         return $this->db->get()->result();
    }*/
    function getproductsales($category, $product,$productgroups,$datefrom,$dateto) {

$table_name = "TICKETLINES";
      $this->db->select("CLOSEDCASH.HOST AS CASHIER,DATE_FORMAT(RECEIPTS.DATENEW, ('%Y-%m-%d')) DATENEW, PRODUCTS.REFERENCE,PRODUCTGROUPS.NAME AS KITCHEN, PRODUCTS.NAME AS PRODUCT,ROUND(SUM(TICKETLINES.UNITS)) AS UNITS,ROUND((SUM(TICKETLINES.UNITS * TICKETLINES.PRICE)))AS TOTAL")->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);
        }
        if($productgroups)
        {
           $this->db->like("PRODUCTGROUPS.NAME", $productgroups);
        }
           if ($datefrom && $dateto) {
//                     $this->db->where("DATENEW BETWEEN '".$datefrom."' AND '".$dateto."'");
            $this->db->where("RECEIPTS.DATENEW >=", $datefrom);
            $this->db->where("RECEIPTS.DATENEW <=", $dateto ."+1 day");
        } else if ($datefrom) {
            $this->db->where("RECEIPTS.DATENEW LIKE", $datefrom."%");
        }
        else if ($dateto) {
            $this->db->where("RECEIPTS.DATENEW LIKE", $datefrom."%");
        }
                $this->db->join('PRODUCTS', 'PRODUCTS.ID = TICKETLINES.PRODUCT','LEFT');
                $this->db->join('PRODUCTGROUPS_PROD', 'PRODUCTGROUPS_PROD.PRODUCT = TICKETLINES.PRODUCT','LEFT');
                $this->db->join('PRODUCTGROUPS', 'PRODUCTGROUPS.ID = PRODUCTGROUPS_PROD.PRODUCTGROUP','LEFT');
                $this->db->join('CATEGORIES', 'CATEGORIES.ID = PRODUCTS.CATEGORY');
                $this->db->join('TICKETS', 'TICKETLINES.TICKET = TICKETS.ID','LEFT');
                $this->db->join('RECEIPTS', 'TICKETS.ID = RECEIPTS.ID','LEFT');
                $this->db->join('PAYMENTS', 'RECEIPTS.ID = PAYMENTS.RECEIPT','LEFT');
                $this->db->join('CLOSEDCASH', 'CLOSEDCASH.MONEY = RECEIPTS.MONEY','LEFT');
                //$this->db->order_by('RECEIPTS.DATENEW');
                $this->db->group_by('PRODUCTS.NAME');
                
        return $this->db->get()->result();
  
    }
    /*function gettariffsales($tariff,$datefrom,$dateto) {

        $table_name = "TARIFFAREAS_PROD";
      $this->db->select("TARIFFAREAS.NAME AS TARIFF,DATE_FORMAT(RECEIPTS.DATENEW, ('%Y-%m-%d')) DATENEW,PRODUCTS.NAME AS PRODUCT,TICKETLINES.UNITS,TARIFFAREAS_PROD.PRICESELL AS PRICE,(TICKETLINES.UNITS * TARIFFAREAS_PROD.PRICESELL) AS TOTAL")->from($table_name);
                 if ($tariff) {
            $this->db->where("TARIFFAREAS.NAME", $tariff);
        }
         if ($datefrom && $dateto) {
            $this->db->where("DATENEW >=", $datefrom);
            $this->db->where("DATENEW <=", $dateto."+1 day");
        } else if ($datefrom) {
            $this->db->where("DATENEW LIKE", $datefrom."%");
        }
        else if ($dateto) {
            $this->db->where("DATENEW <=", $dateto."+1 day");
        }
                $this->db->join('TARIFFAREAS', 'TARIFFAREAS.ID = TARIFFAREAS_PROD.TARIFFID');
                 $this->db->join('PRODUCTS', 'PRODUCTS.ID = TARIFFAREAS_PROD.PRODUCTID','LEFT');
                  $this->db->join('TICKETLINES', 'PRODUCTS.ID = TICKETLINES.PRODUCT','LEFT');
                   $this->db->join('TICKETS', 'TICKETLINES.TICKET = TICKETS.ID','LEFT');
                   $this->db->join('RECEIPTS', 'TICKETS.ID = RECEIPTS.ID','LEFT');
                   $this->db->order_by('RECEIPTS.DATENEW');
//                   $this->db->order_by('TARIFFAREAS.NAME');
         return $this->db->get()->result();
    }*/
    function getstation() {
        $this->db->select('MONEY, HOST');
         $this->db->group_by('HOST');
        return $this->db->get('CLOSEDCASH')->result();
    }
    
    function gettariffsales($datefrom,$dateto)
    {
        $this->db->from("RECEIPTS R");
$this->db->join('TICKETLINES TL', 'TL.TICKET = R.ID','LEFT');
        $this->db->join('CLOSEDCASH CC','R.MONEY=CC.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);
        }

        $this->db->join('TARIFFAREAS TA', 'TA.ID = ExtractValue(CAST(R.ATTRIBUTES AS binary), \'//entry[@key="TariffID"]\')','LEFT',false);
       $this->db->select("TA.NAME AS TARIFF, CC.DATESTART, (SUM(TL.UNITS*TL.PRICE)) AS TOTAL");
       $this->db->group_by("TA.ID");
       $this->db->group_by("R.MONEY");
       $this->db->order_by("CC.DATESTART");
       return $this->db->get()->result_array();

    }
 function gettariffsales1($tariff,$datefrom,$dateto) {

        $table_name = "RECEIPTS R";
        $this->db->select('R.*')->from($table_name);
        $this->db->where("R.ATTRIBUTES is not null");
        if ($datefrom && $dateto) {
            $this->db->where("R.DATENEW >=", $datefrom );
            $this->db->where("R.DATENEW <=", $dateto. "+1 day" );
        } else if ($datefrom) {
            $this->db->where("R.DATENEW LIKE", $datefrom . "%");
        } else if ($dateto) {
            $this->db->where("R.DATENEW <=", $dateto . "+1 day");
        }

        $rows = $this->db->get()->result_array();
/*var_dump($rows);*/
        if ($rows) {
            $tariffsales = array();
            $salesdata = array();
            foreach ($rows as $row) {
                $xml = simplexml_load_string(htmlspecialchars_decode($row['ATTRIBUTES']));
                    foreach ($xml->entry[0]->attributes() as $a => $b) {
                        /*var_dump($xml->entry[0]);*/
                    if ($b == 'TariffArea') {
                        if (!in_array((string) $xml->entry, $tariffsales)) {
                            $tariffsales[] = (string) $xml->entry;
                        }
                        $tariffsales[(string) $xml->entry][] = $row['ID'];
                    }
                }
            }

             foreach ($tariffsales as $sale) {
                if (!is_array($sale) && !in_array((string) $sale, $salesdata))
                    array_push($salesdata, (string) $sale);
                else {
                    $this->db->from('TICKETLINES TL');
                    $this->db->select("P.NAME AS PRODUCT,DATE_FORMAT(R.DATENEW, ('%Y-%m-%d'))DATENEW, (SUM(TL.UNITS * TL.PRICE))/(TL.PRICE) AS UNITS,TL.PRICE,SUM(TL.UNITS * TL.PRICE) AS TOTALAMOUNT");
                    $this->db->join('PRODUCTS P', 'P.ID=TL.PRODUCT');                  
                    $this->db->join('TICKETS T', 'TL.TICKET=T.ID');
                    $this->db->join('RECEIPTS R', 'T.ID=R.ID');
                    $this->db->group_by('TL.PRODUCT');
                    $this->db->where_in('TL.TICKET', $sale);
                    $salesdata[] = $this->db->get()->result_array();
                }
            }
            return $salesdata;
        }
    }
}