<?php
class ModelToolSinc extends Model {
	public function sincData() {
	/* добавим новые даты+номера в таблицу состояний во внешней системе */
		$sql = 'INSERT INTO `oc_product_external` (`product_id`, `booking_date`, `quantity`, `quantity_new`) '
			. 'SELECT DISTINCT `op`.`product_id`, `op`.`booking_date`, `p`.`quantity`, `p`.`quantity` '
			. 'FROM `oc_order_product` `op` '
			. 'LEFT JOIN `oc_product_external` `pe` ON (`op`.`product_id`=`pe`.`product_id` AND `op`.`booking_date`=`pe`.`booking_date`) '
			. 'LEFT JOIN `oc_product` `p` ON (`op`.`product_id`=`p`.`product_id`) '
			. 'WHERE `pe`.`product_id` IS NULL '
			. 'AND (`p`.`upc`<>"" OR `p`.`mpn`<>"") '
			. 'AND `p`.`status`=1 '
			. 'AND `op`.`booking_date` >= NOW()';	
		$query = $this->db->query($sql);
	
		$datemin = date('Y-m-d');
		$datemax = date('Y-m-d', strtotime($datemin . ' +1 year'));
		$date = $datemin;
		while($date < $datemax){
			$sql = "INSERT INTO `oc_product_external` (`product_id`, `booking_date`, `quantity`, `quantity_new`) "
				. "SELECT DISTINCT `p`.`product_id`, '" . $date . "', `p`.`quantity`, `p`.`quantity` "
				. 'FROM `oc_product` `p` '
				. "LEFT JOIN `oc_product_external` `pe` ON (`p`.`product_id`=`pe`.`product_id` AND `pe`.`booking_date` = '" . $date . "') "
				. 'WHERE `pe`.`product_id` IS NULL '
				. 'AND (`p`.`upc`<>"" OR `p`.`mpn`<>"") ';
			$query = $this->db->query($sql);
			$date = date('Y-m-d', strtotime($date . ' +1 day'));
			
		}

		/* обновим остатки в таблице состояния */
		/* поля ALTER TABLE `oc_product_external` ADD `price` FLOAT(15,4) NULL DEFAULT '0.0' AFTER `quantity_new`, ADD `price_new` FLOAT(15,4) NOT NULL DEFAULT '0.0' AFTER `price`;*/
		$sql="update `oc_product_external` pe, `oc_product` p set pe.price_new=p.price WHERE pe.product_id=p.product_id and `pe`.`booking_date` >= NOW();";
        $this->db->query($sql);
		$sql= "update 	`oc_product_external` pe, ("
			. "SELECT `pe2`.`product_id`, `pe2`.`booking_date`, "
			. "("
			. "SELECT ps.price "
			. "FROM oc_product_special ps "
			. "WHERE ps.product_id = pe2.product_id "
			. "AND ps.customer_group_id = 1 "
			. "AND ((ps.date_start = '0000-00-00' OR ps.date_start <= `pe2`.`booking_date`) "
			. "AND (ps.date_end = '0000-00-00' OR ps.date_end >= `pe2`.`booking_date`) "
			. "AND ((ps.disable_day1 = '0' "
			. "AND DAYOFWEEK(`pe2`.`booking_date`) = 2) "
			. "OR (ps.disable_day2 = '0' AND DAYOFWEEK(`pe2`.`booking_date`) = 3) "
			. "OR (ps.disable_day3 = '0' AND DAYOFWEEK(`pe2`.`booking_date`) = 4) "
			. "OR (ps.disable_day4 = '0' AND DAYOFWEEK(`pe2`.`booking_date`) = 5) "
			. "OR (ps.disable_day5 = '0' AND DAYOFWEEK(`pe2`.`booking_date`) = 6) "
			. "OR (ps.disable_day6 = '0' AND DAYOFWEEK(`pe2`.`booking_date`) = 7) "
			. "OR (ps.disable_day7 = '0' AND DAYOFWEEK(`pe2`.`booking_date`) = 1))) "
			. "ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS price FROM `oc_product_external` pe2 "
			. "WHERE `pe2`.`booking_date` >= NOW() HAVING price IS NOT NULL) AS internal set pe.price_new=internal.price "
			. "WHERE pe.product_id=internal.product_id and `pe`.`booking_date` = internal.booking_date";
        $this->db->query($sql);
	
		$sql = 'UPDATE `oc_product_external` `pe`, '
			. '( '
			. 'SELECT DISTINCT `pe`.`product_id`, `pe`.`booking_date`, MAX(`p`.`quantity`)-SUM(if(`op`.`quantity` is null ,0, `op`.`quantity`)) as `quantity` '
			. 'FROM `oc_product_external` `pe` '
			. 'LEFT JOIN `oc_order_product` `op` ON (`op`.`product_id`=`pe`.`product_id` AND `op`.`booking_date`=`pe`.`booking_date`) '
			. 'LEFT JOIN `oc_product` `p` ON (`pe`.`product_id`=`p`.`product_id`) '
			. 'WHERE `pe`.`booking_date`>=NOW() '
			. 'GROUP BY `pe`.`product_id`, `pe`.`booking_date` '
			. ') '
			. '`db` SET `pe`.`quantity_new`	= `db`.`quantity` '
			. 'WHERE `db`.`product_id`=`pe`.`product_id` AND `db`.`booking_date`=`pe`.`booking_date`';
		$query = $this->db->query($sql);

		/* заберём на отправку во внешние системы */
		$sql = 'UPDATE `oc_product_external` `pe` SET `send1_ok`=0, `send2_ok`=0 where `quantity`<>`quantity_new` OR `price`<>`price_new`';
		$query = $this->db->query($sql); 	// изменившиеся и новые пометим к отправке в букинг и хотел
		
		$sql = 'UPDATE `oc_product_external` `pe` SET `quantity`=`quantity_new`, `price`=`price_new`';
		$query = $this->db->query($sql);	// запомним новые значения остатков и цен

		/* запрос получения пакета для отправки */
		/* для того чтобы залить 1 месяц и более - закоментировать: AND `quantity`<>`quantity_upd */
		
		$sql = 'SELECT * FROM `oc_product_external` pe LEFT JOIN `oc_product` op on (op.product_id=pe.product_id) WHERE '.
			' op.status =1   AND `booking_date`>=NOW() AND (`send1_ok` in(0,2,3,4)  OR `send2_ok`=4) LIMIT 50 '; // можно сюда лимит для ограничения пакета          i          AND `booking_date`>=NOW()
		
		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function send_ok($product_id, $booking_date, $type,$status=1) {

		$sql = 'UPDATE `oc_product_external` SET `send' .(int)$type . '_ok`='.(int)$status.' WHERE booking_date="' . $this->db->escape($booking_date) . '" AND product_id=' . (int)$product_id;
		$queryes = $this->db->query($sql);
		/* подтвердим успешную отправку */
	}		
		
	public function getReferens($command, $referens) {
		$sql = "SELECT * FROM `" . DB_PREFIX . "product_import` WHERE `status` = '".$command."' AND `referens_id` = '".$referens."'";
		$query = $this->db->query($sql);
		if ($query) {
			return $query->rows;
		} else {
			return false;
		}
	}
	
	public function addReferensBooking($referens) {

		$log = new Log('sync.log');
		$this->load->model('extension/module/booking');
		$ses = $this->model_extension_module_booking->login();
			
		$message = $ses;
		$log->write($message); 

		if ($ses) { 
				
			
			foreach ($referens as $value) {
				
				//toDo make right
				$data = $this->model_extension_module_booking->getReferens($ses, $value);
				
				$message = "OK";
				$log->write($message); 
				
				if ($data) {
					$sql = "INSERT INTO `" . DB_PREFIX . "product_import` "
						. "(`status`, `referens_id`, `date_add`, `processed`, `room_id`, `date_in`, `date_out`, `guests`, `rooms`, `price`, `customer`, `source`) "
						. "VALUES "
						. "('".$value['status']."', '".$value['referens_id']."', '".$value['date_add']."', '0', ".$data['room_id'].", '".$data['date_in']."', '".$data['date_out']."', ".$data['count_guest'].", ".$data['count_room'].", ".$data['price'].", '".$data['name']."', 'booking')";


					$query = $this->db->query($sql);

					$sql = "SELECT product_id FROM `"  . DB_PREFIX . "product` WHERE `upc`='" . $data['room_id'] . "'";
		
					$query = $this->db->query($sql);
					if (isset($query->row['product_id'])){
						$a = $query->row['product_id'];
						$sql = "UPDATE `oc_product_import` SET `product_id`='" .$a. "' WHERE `room_id` = '" . $data['room_id'] . "'";
						$query = $this->db->query($sql);

					}
					
				} else {
					$message = 'No LOGIN!!!!!!! ALARM!!!';
					$log->write($message); 
					
				/*	$mail = new Mail();
					$mail->protocol = $this->config->get('config_mail_protocol');
					$mail->parameter = $this->config->get('config_mail_parameter');
					$mail->smtp_hostname = $this->config->get('config_mail_smtp_hostname');
					$mail->smtp_username = $this->config->get('config_mail_smtp_username');
					$mail->smtp_password = html_entity_decode($this->config->get('config_mail_smtp_password'), ENT_QUOTES, 'UTF-8');
					$mail->smtp_port = $this->config->get('config_mail_smtp_port');
					$mail->smtp_timeout = $this->config->get('config_mail_smtp_timeout');

					$mail->setTo('igor.zabuga@gmail.com');
					$mail->setFrom($this->config->get('config_email'));
					$mail->setSender('Brech - error avtorization for Booking!');
					$mail->setSubject('Brech - error avtorization for Booking!');
					$mail->setText('Brech - error avtorization for Booking!');
					$mail->send();*/

/* Не пишем в базу ничего если не за логинены!!!!
 
 
					$sql = "INSERT INTO `" . DB_PREFIX . "product_import` "
						. "(`status`, `referens_id`, `date_add`, `processed`, `source`) "
						. "VALUES "
						. "('".$value['status']."', '".$value['referens_id']."', '".$value['date_add']."', '0', 'booking')";
					$query = $this->db->query($sql);
 
*/					
				}
			}
		}
	}

	public function addReferensHotels24($referens) {
		$this->load->model('extension/module/hotels24');
		$all_from_hotels = $this->model_extension_module_hotels24->login();
		$log = new Log('sync.log');
		
		foreach ($referens as $value) {
			foreach ($all_from_hotels as $order) {
				if ($order->callId == $value['referens_id']) {
					$sql = "SELECT * FROM `" . DB_PREFIX . "product_import` WHERE `referens_id`='" . $value['referens_id'] . "'";
					$query = $this->db->query($sql);
					if (empty($query->rows)) {
						foreach ($order->roomData as $rooms) {
							$sql = "SELECT DISTINCT p.product_id FROM `"  . DB_PREFIX . "product` p LEFT JOIN `"  . DB_PREFIX . "product_description` pd ON p.product_id = pd.product_id WHERE pd.`name`='" . $rooms->value . "'";
							$query = $this->db->query($sql);
							if ($query->num_rows) {
								$product_id = $query->row['product_id'];
								$sql = "INSERT INTO `" . DB_PREFIX . "product_import` "
									. "(`status`, `referens_id`, `product_id`, `date_add`, `processed`, `date_in`, `date_out`, `rooms`, `price`, `customer`, `source`) "
									. "VALUES "
									. "('".$order->status->status."', '".$value['referens_id']."', '".$product_id."', '". date( "Y-m-d", strtotime($order->date_added)) ."', '0', '".date( "Y-m-d", strtotime($order->begin_date))."', '".date( "Y-m-d", strtotime($order->end_date))."', ".$rooms->amount.", ".$order->sum.", '".$order->person."', 'hotels24')";
								$query = $this->db->query($sql);
								$message = 'Write Ok = ' . $product_id . ' - ' . $rooms->value;
								$log->write($message);						
							}
						}
					}
				}
			}

		}
	}

	public function  getNewImport(){
	    $query=$this->db->query("SELECT * FROM " . DB_PREFIX . "product_import WHERE processed=0 ORDER BY status DESC")->rows;
	   return $query;
    }
    public  function  setOrderId($order_id,$customer,$referens_id){
	    $this->db->query("UPDATE " . DB_PREFIX . "product_import SET order_id=".(int)$order_id." WHERE customer='".$this->db->escape($customer)."'AND  referens_id=".(int)$referens_id);
    }

    public  function processed($referens_id){
        $this->db->query("UPDATE " . DB_PREFIX . "product_import SET processed=1 WHERE referens_id=".(int)$referens_id);

    }

    public function checkOrderId($referens_id){
        $order_id=$this->db->query("SELECT order_id FROM " . DB_PREFIX . "product_import WHERE referens_id=".(int)$referens_id." ORDER BY order_id DESC ")->row;
        $this->db->query("UPDATE  " . DB_PREFIX . "product_import SET order_id=" . (int)$order_id['order_id'] . " WHERE referens_id=".(int)$referens_id);

        return $order_id['order_id'];
    }

    public function addProduct($order_id,$product_id,$quantity,$date){
        $this->db->query("INSERT INTO " . DB_PREFIX ."order_product 
            SET 
                order_id =".(int)$order_id.",
                product_id=".(int)$product_id.",
                quantity=".(int)$quantity.",
                booking_date='".$date."'");
    }
}
