<?php
/**
 * @author Aleksey Shutiy<shutiy@ligthsoft.ru>
 */
class ModelReportBrechSpa extends Model {

	public function order(array $data = []) {
		$sub_select = 'SELECT op.order_product_id, CONCAT_WS(" ",o.firstname,o.lastname) as c_name, '
			. 'o.date_added as date, '
			. 'op.name as service, '
			. 'op.price as price, '
			. 'op.quantity as quantity, '
			. 'IF(STRCMP(oo.name,"time"), null, oo.value) as option_time, '
			. 'IF(STRCMP(o.payment_code,"cod"), null, op.total) as total_cod, '
			. 'IF(STRCMP(o.payment_code,"free_checkout"), null, op.total) as total_free ';
		
		$from = 'FROM ' . DB_PREFIX . 'order_product op JOIN '
			. DB_PREFIX . 'order o USING(order_id) LEFT JOIN '
			. DB_PREFIX	. 'order_option oo ON op.order_id = oo.order_id AND op.order_product_id = oo.order_product_id ';

		$total_select = 'SELECT "" as c_name, "" as date, "" as price, "" as quantity, "" as option_time, "" as total_cod, "" as total_free, SUM(IF(STRCMP(o.payment_code,"cod"), 0, op.total)) as sum_cod, '
				. 'SUM(IF(STRCMP(o.payment_code,"free_checkout"), 0, op.total)) as sum_free';

		return SSP::complex($data['request'], $this->db, $sub_select, $total_select, $from, $data['primaryKey'], $data['columns']);
	}
	
	public function service(array $data = []) {
		$sql = "SELECT 
			MAX(op.order_product_id) AS order_product_id
			,MAX(o.order_id) AS order_id
			,MAX(o.order_status_id) AS order_status_id
			 
			,MAX(op.booking_date) AS booking_date
			,MAX(op.product_id) AS product_id
			,MAX(op.name) AS product_name
			,GROUP_CONCAT(oo.value SEPARATOR '<br>') AS options
			,max(if(op.product_id IN (" . implode(',',array_merge($this->config->get('config_payment'),$this->config->get('config_payment_auto'))) . "),1,0)) as is_pay ";

		if($data['category']){
			$sql .= ",max(
				if(
					op.product_id IN (
						SELECT p.product_id 
						FROM oc_category_path cp 
						LEFT JOIN oc_product_to_category p2c ON (cp.category_id = p2c.category_id)
						LEFT JOIN oc_product p ON (p2c.product_id = p.product_id)
						WHERE cp.path_id = {$data['category']}
					)
				,1,0)
			) as is_filtr ";
		}else{
			$sql .= ",1 as is_filtr ";
		}
 
		$sql .= ",MAX(o.customer_id) AS customer_id
			,MAX(concat(o.lastname, ' ', o.firstname)) AS customer_name
			,MAX(cg.name) AS customer_group_name
			 
			,MAX(op.quantity) AS quantity
			,MAX(op.total) AS total
			 
			,MAX(op.confirm) AS user_id
			,MAX(concat(u.lastname,' ', u.firstname)) AS user_name
		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 . "customer_group_description cg ON cg.customer_group_id = o.customer_group_id
			LEFT JOIN " . DB_PREFIX . "user u ON op.confirm = u.user_id
		WHERE op.STATUS = 1
			AND cg.language_id = '" . (int)$this->config->get('config_language_id') . "'";

		$implode = array();

		if (!$data['start_date']) {
			$data['start_date'] = date('Y-m-d');
		}
		$implode[] = "op.booking_date >= '" . $this->db->escape($data['start_date']) . "'";

		if (!$data['end_date']) {
			$data['end_date'] = date('Y-m-d');
		}
		$implode[] = "op.booking_date <= '" . $this->db->escape($data['end_date']) . "'";

		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}

		$sql .= " GROUP BY op.order_product_id
			ORDER BY booking_date ASC, user_id ASC, customer_id ASC, is_pay DESC, order_product_id ASC";
		
		$query = $this->db->query($sql);
		
		$ret = [];
		$booking_date = '';
		$user_id = '';
		$customer_id = '';
		$pays = [];
		$list_pay = [0 =>'На счет'];
		foreach($query->rows as $row){
			if(
				$row['booking_date'] != $booking_date OR
				$row['user_id'] != $user_id OR
				$row['customer_id'] != $customer_id){ // смена блока
				if (count($pays)>0){ // есть неразнесенные платежи - сбрасываем их в "Аванс"
					$one = [];
					$summ_one = 0;
					foreach($pays as $pay){
						if (!isset($one[$pay['product_id']])){
							$one[$pay['product_id']] = 0;
						}
						$one[$pay['product_id']] += $pay['total'];
						$summ_one += $pay['total'];
						$last = $pay;
					}
/*					$last['total'] = $summ_one;
					$last['product_name'] = 'Аванс';
					$last['pay'] = $one;
					$ret[] = $last;*/
				}
				$booking_date = $row['booking_date'];
				$user_id = $row['user_id'];
				$customer_id = $row['customer_id'];
				$pays = [];
			}
			if ($row['is_pay']){
				if(!isset($list_pay[$row['product_id']])){ //новая система оплаты, сохраним
					$list_pay[$row['product_id']] = $row['product_name'];
				}
				$row['total'] = 0 - $row['total'];
				$pays[] = $row;
			}else{
				$one = []; // сюда пишем оплаты
				$last_one = $row['total'];
				$bad_exit = false;
				$ccc=100;
				while($last_one > 0 AND count($pays)>0 AND !$bad_exit){
					reset($pays); 
					$ind = key($pays); // c какой оплаты списываем
					$summa = min($pays[$ind]['total'], $last_one); // какую сумму
					if (!isset($one[$pays[$ind]['product_id']])){
						$one[$pays[$ind]['product_id']] = 0; // ещё не платили этой системой
					}
					$one[$pays[$ind]['product_id']] += $summa;
					$last_one -= $summa;
					$pays[$ind]['total'] -= $summa;
					if ($pays[$ind]['total'] == 0 ){ // с этой оплаты выгребли всё
						unset($pays[$ind]);
					}elseif($last_one != 0){
						$bad_exit == true;
					}
					$bad_exit == true;
				}
				if ($last_one > 0){ // оплат не хватило, остачу "На счёт"
					if (!isset($one[0])){
						$one[0] = 0;
					}
					$one[0] += $last_one;
				}
				$row['pay'] = $one;
				if($row['is_filtr']){
					$ret[] = $row;
				}
			}
		}
/*		if (count($pays)>0){ // есть неразнесенные платежи в ПОСЛЕДНЕЙ СТРОКЕ - сбрасываем их в "Аванс"
			$one = [];
			$summ_one = 0;
			foreach($pays as $pay){
				if (!isset($one[$pay['product_id']])){
					$one[$pay['product_id']] = 0;
				}
				$one[$pay['product_id']] += $pay['total'];
				$summ_one += $pay['total'];
				$last = $pay;
			}
			$last['total'] = $summ_one;
			$last['product_name'] = 'Аванс';
			$last['pay'] = $one;
			$ret[] = $last;
		}
 * 
 */
		return ['data'=>$ret,'rows'=>$list_pay];
	}
	
	public function categories($parent_id = 0) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'  AND c.status = '1' ORDER BY c.sort_order, LCASE(cd.name)");

		return $query->rows;
	}
}
