<?php
class ModelSaleToday extends Model {

    public function getCategories(){
        $sql = "SELECT cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '&nbsp;&nbsp;&gt;&nbsp;&nbsp;') AS name, c1.parent_id, c1.sort_order FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "category c1 ON (cp.category_id = c1.category_id) LEFT JOIN " . DB_PREFIX . "category c2 ON (cp.path_id = c2.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (cp.path_id = cd1.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (cp.category_id = cd2.category_id) WHERE cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' AND cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'";
        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function delete($order_product_id){
        $this->db->query("DELETE FROM ". DB_PREFIX . "order_option
            WHERE
                order_product_id=" . (int)$order_product_id);
        $this->db->query("DELETE FROM " . DB_PREFIX . "order_product
            WHERE
                order_product_id=" . (int)$order_product_id);
    }
    
    public function getAllProducts($booking_date,$category_id){

        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX ."order o
	LEFT JOIN " . DB_PREFIX ."order_product op ON op.order_id = o.order_id
	LEFT JOIN " . DB_PREFIX ."order_option oo ON op.order_product_id = oo.order_product_id
	LEFT JOIN " . DB_PREFIX ."product_to_category p2c ON p2c.product_id = op.product_id
	LEFT JOIN " . DB_PREFIX ."category_path cp ON cp.category_id = p2c.category_id
	LEFT JOIN " . DB_PREFIX ."customer_group_description cg ON cg.customer_group_id = o.customer_group_id
WHERE op.STATUS <> 1
	AND op.booking_date = '". $booking_date ."'
	AND cg.language_id = '" . (int)$this->config->get('config_language_id')."'
	AND cp.path_id = ". (int)$category_id);
            $total=$query->row['total'];
        return  $total;

    }
    public function getProduct($data = array()) {
        $sql="SELECT product_id , model FROM " . DB_PREFIX . "product";

        if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}
        $query = $this->db->query($sql);
        return $query->rows;

	}
     public function getUsers() {
        $query=$this->db->query("SELECT user_id , username, firstname,lastname FROM " . DB_PREFIX . "user" );
        return $query->rows;
   
	}
    public function confirmStatus($order_product_id){
        $this->db->query("UPDATE " . DB_PREFIX . "order_product SET status=1 WHERE order_product_id='" . (int)$order_product_id . "'");
    }
    
    public function getToday($data = array(),$booking_date,$category_id,$product_status_id) {
        if($data['report_id']==2){
            $booking_date=date('Y-m-d',strtotime($booking_date.'-1 day'));

        }
        $sql="SELECT
    MAX(op.order_product_id) AS order_product_id
	,MAX(op.NAME) AS product
	,MAX(op.status) AS product_status_id
	,MAX(o.firstname) AS firstname
	,MAX(o.lastname) AS lastname
	,MAX(cg.NAME) AS customer_group_id
	,MAX(op.booking_date) AS booking_date";
        if($data['report_id']==1){
            $sql.=",MIN(op.booking_date) AS booking_date_";
        }elseif($data['report_id']==2){
            $sql.=",MAX(op.booking_date) AS booking_date_";
        }

        $sql.="
	,MAX(op.total) AS total
	,MAX(op.quantity) AS quantity
	,MAX(o.order_id) AS order_id
	,MAX(o.order_status_id) AS order_status_id
	,GROUP_CONCAT(oo.value SEPARATOR '<br>') AS options
FROM " . DB_PREFIX ."order_product op 
	LEFT JOIN " . DB_PREFIX ."order o ON op.order_id = o.order_id
	LEFT JOIN " . DB_PREFIX ."order_option oo ON op.order_product_id = oo.order_product_id
	LEFT JOIN " . DB_PREFIX ."product_to_category p2c ON p2c.product_id = op.product_id
	LEFT JOIN " . DB_PREFIX ."category_path cp ON cp.category_id = p2c.category_id
	LEFT JOIN " . DB_PREFIX ."customer_group_description cg ON cg.customer_group_id = o.customer_group_id
	 WHERE cg.language_id = '" . (int)$this->config->get('config_language_id')."' AND ";
		if($product_status_id!=2) {
            $sql .= "
           op.STATUS = " . (int)$product_status_id." AND ";
        }
        if($data['report_id']==0){
		$sql.="
	 op.booking_date = '". $booking_date ."'  AND ";
		}
        $sql.="
	 cp.path_id = ". (int)$category_id;
        $sql .=" GROUP BY  o.order_id,op.product_id";
		$sort_data = array(
			'firstname',
			'customer_group_id',
			'product'
		);
		if($data['report_id']!=0) {

            $sql .= " HAVING  booking_date_ = '" . $booking_date . "' ";
        }
		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY sort_order";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}

		$query = $this->db->query($sql);
		return $query->rows;
	}
}
