<?php

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

class Sales_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->library('Formatslist');
    }

// RECEIPTS LIST
    function getreceipts() {
        /*$this->db->select('CUSTOMERS.NAME as CUSTOMER_NAME,TICKETS.*');
        $this->db->from("TICKETS");
        $this->db->join("RECEIPTS", "RECEIPTS.ID = TICKETS.ID", "LEFT");
        $this->db->join("CUSTOMERS", "CUSTOMERS.ID = TICKETS.CUSTOMER", "LEFT");
        $this->db->join("PAYMENTS", "PAYMENTS.RECEIPT = RECEIPTS.ID", "LEFT");
        $this->db->join("PEOPLE", "TICKETS.PERSON = PEOPLE.ID", "LEFT");
        $this->db->group_by("TICKETS.TICKETID");
        $rows = $this->db->get()->result_array();
        echo $this->db->last_query();
        return $rows;*/
        //Fetch data
//        $this->db->select('TICKETS.TICKETID,PAYMENTS.TOTAL, PAYMENTS.PAYMENT,CUSTOMERS.NAME AS CUSTOMER, RECEIPTS.DATENEW, PEOPLE.NAME');
//        $this->db->join("RECEIPTS", "RECEIPTS.ID = TICKETS.ID", "LEFT");
//        $this->db->join("CUSTOMERS", "CUSTOMERS.ID = TICKETS.CUSTOMER", "LEFT");
//        $this->db->join("PAYMENTS", "PAYMENTS.RECEIPT = RECEIPTS.ID", "LEFT");
//        $this->db->join("PEOPLE", "TICKETS.PERSON = PEOPLE.ID", "LEFT");
//        $this->db->group_by("TICKETS.TICKETID");
//        $rows = $this->db->get("TICKETS")->result();
//        print_r($rows); die();
        $sql  =   "SELECT TICKETS.TICKETID AS TICKET_NO, RECEIPTS.DATENEW AS TICKET_DATE, SUM(PAYMENTS.TOTAL) AS MONEY, SUM(TAXLINES.AMOUNT) AS TAX,CUSTOMERS.NAME AS CUSTOMER, PEOPLE.NAME,
CASE TICKETS.TICKETTYPE WHEN 0 THEN 'SALE' WHEN '1' THEN 'REFUND' END AS HEAD 
        FROM RECEIPTS 
        LEFT JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID AND (TICKETS.TICKETTYPE=0 OR TICKETS.TICKETTYPE=1) 
        LEFT JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT 
        LEFT JOIN TAXLINES ON TAXLINES.RECEIPT = RECEIPTS.ID 
        LEFT JOIN CUSTOMERS ON CUSTOMERS.ID = TICKETS.CUSTOMER
        LEFT JOIN PEOPLE ON TICKETS.PERSON = PEOPLE.ID
        GROUP BY RECEIPTS.ID 
        ORDER BY RECEIPTS.DATENEW";
        $query = $this->db->query($sql);
        $rows =   $query->result();         
        $data_row   =   array();
        foreach ($rows as $value) {
            $data_row[]  = array(
                'TICKETID' => $value->TICKET_NO, 
                'HEAD' => $value->HEAD,  
                'MONEY' => $this->formatslist->formatCurrencywithHash($value->MONEY),
                'TAX' => $this->formatslist->formatCurrencywithHash($value->TAX),
                //'PAYMENT' => $value->PAYMENT, 
                'CUSTOMER' => $value->CUSTOMER, 
                'TICKET_DATE' => $this->formatslist->datetimeFormat($value->TICKET_DATE),
                'NAME' => $value->NAME, 
            );
        }
        return $data_row;
   }
    // RECEIPTS LIST
    function getdeletedreceipts($datefrom, $dateto, $ticket, $type, $customer,$user) {
         $page = $this->input->post('page');
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
//Default values
        if (!$sidx)
            $sidx = 'TICKETID';
        if (!$sord)
            $sord = 'asc';

        if (!$page)
            $page = 1;
        if (!$limit)
            $limit = 25;

//Total Returned Rows Count
        $this->db->from('TICKETS');
        
        
        
        
        $count = $this->db->count_all_results();

//Total Pages
        if ($count > 0 && $limit > 0) {
            $total_pages = ceil($count / $limit);
        } else {
            $total_pages = 0;
        }
//Check requested page
        if ($page > $total_pages)
            $page = $total_pages;

//Set start record number
        $start = $limit * $page - $limit;
//Format data for jqGrid output
        $data['page'] = "" . $page . "";
        $data['total'] = "" . $total_pages . "";
        $data['records'] = "" . $count . "";
//Fetch data
        $this->db->select('(TICKETS.TICKETID)+1 AS TICKETID');
//        $this->db->select_sum('PAYMENTS.TOTAL');
        //$this->db->where('TICKETS.TICKETTYPE = 0');
//        if($customer)
//        {
//            $this->db->where('CUSTOMER', $customer);
//        }
//         if($user)
//        {
//            $this->db->where('TICKETS.PERSON =', $user);
//        }
         if ($datefrom && $dateto) {
            $this->db->where("DATENEW >=", $datefrom);
            $this->db->where("DATENEW <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATENEW", $datefrom);
        }
//        if($ticket)
//        {
//            $this->db->where('TICKETID', $ticket);
//        }
//        if($type == 1)
//        {
//            $this->db->where('TICKETS.TICKETTYPE = 1');
//        }
//        else if($type == 0)
//        {
//            $this->db->where('TICKETS.TICKETTYPE = 0');
//        }



        $this->db->join("RECEIPTS", "RECEIPTS.ID = TICKETS.ID", "LEFT");
        $this->db->join("TICKETS AS R", "TICKETS.TICKETID = R.TICKETID", "LEFT OUTER");
//        $this->db->join("CUSTOMERS", "CUSTOMERS.ID = TICKETS.CUSTOMER", "LEFT");
//        $this->db->join("PAYMENTS", "PAYMENTS.RECEIPT = RECEIPTS.ID", "LEFT");
          $this->db->where("R.TICKETID IS NULL");
        $this->db->group_by("TICKETS.TICKETID");

        $this->db->order_by($sidx, $sord);
         $this->db->limit($limit, $start);
        $rows = $this->db->get("TICKETS")->result_array();
        //Format data
        $i = 0;
        foreach ($rows as $row) {
            $data['rows'][$i]['id'] = $i + 1;
            $data['rows'][$i]['cell'] = array($row['TICKETID'], $row['TICKETID']);
            //,$row['TOTAL']);

            $i++;
        }
        return json_encode($data);
    }

//select l.ticketid + 1 as start
//from tickets as l
//  left outer join tickets as r on l.ticketid + 1 = r.ticketid
//where r.ticketid is null;
// RECEIPT DETAILS
    public function getreceiptlinesbyparam($id) {
//        $table_name = "TICKETS, RECEIPTS, PAYMENTS";
//        $this->db->select('TICKETS.TICKETID, SUM(TAXLINES.AMOUNT) AS TAX, TICKETS.TICKETTYPE, CUSTOMERS.NAME AS CUSTOMER, RECEIPTS.DATENEW, RECEIPTS.ATTRIBUTES, PEOPLE.NAME AS CASHIER,PAYMENTS.TOTAL')->from($table_name);
//        $this->db->where('TICKETS.TICKETID', $id);
//        //TICKETS.TICKETTYPE = 0 AND
//        $this->db->where('TICKETS.ID = RECEIPTS.ID AND RECEIPTS.ID = PAYMENTS.RECEIPT ');
//        $this->db->join('CUSTOMERS', 'TICKETS.CUSTOMER = CUSTOMERS.ID', 'LEFT');
//        $this->db->join('TAXLINES', 'TAXLINES.RECEIPT = RECEIPTS.ID', 'LEFT');
//        $this->db->join('PEOPLE', 'TICKETS.PERSON = PEOPLE.ID', 'LEFT');
//        $this->db->order_by("TICKETS.TICKETID");        
        $sql="SELECT SUM(TAXLINES.AMOUNT) AS TAX,
A.TICKETID, RECEIPTS.DATENEW,RECEIPTS.ATTRIBUTES, A.TICKETTYPE,CUSTOMERS.TAXID, PAYMENTS.PAYMENT AS PAYMENTTYPE,PEOPLE.NAME AS CASHIER,PEOPLE.NAME AS CASHIER,
 CUSTOMERS.NAME AS CUSTOMER,CUSTOMERS.ADDRESS AS ADDRESS,CUSTOMERS.CITY AS CITY,
 CUSTOMERS.REGION AS REGION,CUSTOMERS.COUNTRY AS COUNTRY,CUSTOMERS.EMAIL AS EMAIL,CUSTOMERS.PHONE AS PHONE,
 (PAYMENTS.TOTAL) AS TOTAL ,CUSTOMERS.CURDEBT AS CURDEBT
 FROM (SELECT * FROM TICKETS WHERE TICKETS.ID='".$id."') A
 LEFT JOIN RECEIPTS ON A.ID=RECEIPTS.ID
 LEFT JOIN CUSTOMERS ON A.CUSTOMER=CUSTOMERS.ID 
 LEFT JOIN TAXLINES ON TAXLINES.RECEIPT = RECEIPTS.ID 
 LEFT JOIN PEOPLE ON A.PERSON = PEOPLE.ID 
 LEFT JOIN PAYMENTS ON PAYMENTS.RECEIPT=RECEIPTS.ID";
    $query = $this->db->query($sql);
    return $query->result(); 
        
    }

// RECEIPT LOGO
    public function getReceiptLogo() {

        $table_name = "RESOURCES";
        $this->db->select('CONTENT')->from($table_name);
        $this->db->where('NAME', "Printer.Ticket.Logo");
        return $this->db->get()->result();
    }

// RECEIPT PRODUCTS DETAILS eg: PRODUCT NAME, QUANTITY, PRICE etc.....
    public function getticketlinesbyparam($id) {
        $sql="SELECT PRODUCTS.NAME, TICKETLINES.ATTRIBUTES,
 TICKETLINES.UNITS,
 TICKETLINES.PRICE AS PRICE,
 (TICKETLINES.UNITS * TICKETLINES.PRICE) AS UNITTOTAL, TAXES.RATE FROM  
 (SELECT * FROM TICKETS WHERE TICKETS.ID='".$id."') A  
 LEFT JOIN TICKETLINES ON A.ID = TICKETLINES.TICKET 
LEFT JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID
LEFT JOIN TAXES ON PRODUCTS.TAXCAT = TAXES.CATEGORY AND TAXES.PARENTID IS NULL
LEFT JOIN PEOPLE ON A.PERSON = PEOPLE.ID";
        $query = $this->db->query($sql);
        return $query->result(); 

//        $table_name = "TICKETS, TICKETLINES";
//        $this->db->select('PRODUCTS.NAME, TICKETLINES.UNITS, TICKETLINES.PRICE AS PRICE,(TICKETLINES.UNITS * TICKETLINES.PRICE) AS UNITTOTAL')->from($table_name);
//        $this->db->join('PEOPLE', 'TICKETS.PERSON = PEOPLE.ID');
//        $this->db->join('PRODUCTS', 'TICKETLINES.PRODUCT = PRODUCTS.ID');
//        $this->db->where('TICKETS.TICKETID', $id);
//        $this->db->where('TICKETS.ID = TICKETLINES.TICKET');
//        
       //// $this->db->where('TICKETS.TICKETTYPE = 0');
//        return $this->db->get()->result();
    }

// RECEIPT PAYMENTS DETAILS eg: TOTAL AMOUNT ,PAYMENTTYPE etc..........
    public function getpaymentlinesbyparam($id) {
        $table_name = "PAYMENTS, TICKETS";
        $this->db->select('PAYMENTS.PAYMENT, PAYMENTS.TOTAL AS PAYMENTTOTAL')->from($table_name);
        $this->db->where('TICKETS.ID', $id);
        //TICKETS.TICKETTYPE = 0 AND
        $this->db->where('TICKETS.ID = PAYMENTS.RECEIPT');
        return $this->db->get()->result();
    }

// GET DIFFERENT DETAILS FROM RESOURCES TABLE eg:LOGO
    public function getresources($id) {
        $table_name = "RESOURCES";
        $this->db->select('RESOURCES.CONTENT, RESOURCES.ID')->from($table_name);
        $this->db->where('RESOURCES.ID = 1');
        return $this->db->get()->result();
    }

}