<?php

/**
 * Vvveb
 *
 * Copyright (C) 2020  Ziadin Givan
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 */

namespace Vvveb\Sql;

class ordersSQL extends \Vvveb\System\Db\mysqli {
	public function __construct() {
		parent::__construct();
	}

	function getOrders($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['language_id' => 'i', 'store_id' => 'i', 'customer_id' => 'i', 'order_status' => 's', 'start' => 'i', 'count' => 'i'];

		$prevSql = $sql ?? '';
		$sql     = 'SELECT orders.*,os.name as order_status FROM `order` AS orders 
		
			LEFT JOIN order_status AS os ON (orders.order_status_id = os.order_status_id AND os.language_id = :language_id) 
			
		WHERE 1 
		
			AND orders.store_id = :store_id';

		if (isset($params['customer_id'])) {
			$sql .= ' AND orders.customer_id = :customer_id ';
		}

		if (isset($params['order_status'])) {
			$sql .= ' AND os.name = :order_status ';
		}

		$sql .= ' 
	LIMIT :start, :count';

		if ($sql) {
			$stmt['orders'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['orders']) {
				if (method_exists($stmt['orders'], 'get_result')) {
					$result = $stmt['orders']->get_result();
				} else {
					$result = $this->get_result($stmt['orders']);
				}
			}

			/*
			if ('orders' == '_') {
				$value = $result->fetch_all(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('orders' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['orders'][$row['']] = $values;

							$results['orders'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('orders' == '_') {
					$value = $result->fetch_all(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['orders'] = $result->fetch_all(MYSQLI_ASSOC);
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = 'SELECT count(*) FROM (';

		$sql .= $this->sqlCount($prevSql, 'order_id', $this->prefix . 'order');
		$sql .= '
	
	) as count';

		if ($sql) {
			$stmt['count'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['count']) {
				if (method_exists($stmt['count'], 'get_result')) {
					$result = $stmt['count']->get_result();
				} else {
					$result = $this->get_result($stmt['count']);
				}
			}

			/*
			if ('count' == '_') {
				$value = $result->fetch_array(MYSQLI_NUM)[0];
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('count' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['count'][$row['']] = $values;

							$results['count'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('count' == '_') {
					$value = $result->fetch_array(MYSQLI_NUM)[0];

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['count'] = $result->fetch_array(MYSQLI_NUM)[0];
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}

	function getOrder($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['customer_id' => 'i', 'order_id' => 'i'];

		$prevSql = $sql ?? '';
		$sql     = 'SELECT * FROM `order` WHERE  1';

		if (isset($params['customer_id'])) {
			$sql .= ' AND `order``.customer_id = :customer_id ';
		}

		$sql .= ' 
	AND `order`.order_id = :order_id';

		if ($sql) {
			$stmt['order'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['order']) {
				if (method_exists($stmt['order'], 'get_result')) {
					$result = $stmt['order']->get_result();
				} else {
					$result = $this->get_result($stmt['order']);
				}
			}

			/*
			if ('order' == '_') {
				$value = $result->fetch_array(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('order' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['order'][$row['']] = $values;

							$results['order'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('order' == '_') {
					$value = $result->fetch_array(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['order'] = $result->fetch_array(MYSQLI_ASSOC);
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = 'SELECT `key` as array_key,`value` as array_value FROM order_meta as _
			WHERE _.order_id = :order_id';

		if ($sql) {
			$stmt['_'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['_']) {
				if (method_exists($stmt['_'], 'get_result')) {
					$result = $stmt['_']->get_result();
				} else {
					$result = $this->get_result($stmt['_']);
				}
			}

			/*
			if ('_' == '_') {
				$value = $result->fetch_all(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('key')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('value')) {
							//$values = $row['value'];
							$values = $row['array_value'];
						}

						if ('_' == '_') {
							//$results[$row['key']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['_'][$row['key']] = $values;

							$results['_'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('_' == '_') {
					$value = $result->fetch_all(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['_'] = $result->fetch_all(MYSQLI_ASSOC);
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = 'SELECT `product_id` as array_key FROM order_product as products
			WHERE products.order_id = :order_id';

		if ($sql) {
			$stmt['products'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['products']) {
				if (method_exists($stmt['products'], 'get_result')) {
					$result = $stmt['products']->get_result();
				} else {
					$result = $this->get_result($stmt['products']);
				}
			}

			/*
			if ('products' == '_') {
				$value = $result->fetch_all(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('product_id')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('products' == '_') {
							//$results[$row['product_id']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['products'][$row['product_id']] = $values;

							$results['products'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('products' == '_') {
					$value = $result->fetch_all(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['products'] = $result->fetch_all(MYSQLI_ASSOC);
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}

	function placeOrder($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['order' => 'a'];

		$prevSql = $sql ?? '';
		$sql     = '';

		$filterArray = [
			'order_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
				'e' => 'auto_increment',
			],
			'invoice_no' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'invoice_prefix' => [
				'd' => '\'INV-\'',
				'n' => false,
				't' => 'varchar',
			],
			'store_id' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'store_name' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'store_url' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'customer_id' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'customer_group_id' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'first_name' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'last_name' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'email' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'phone_number' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'fax' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'custom_field' => [
				'd' => NULL,
				'n' => false,
				't' => 'text',
			],
			'payment_first_name' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'payment_last_name' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'payment_company' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'payment_address_1' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'payment_address_2' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'payment_city' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'payment_postcode' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'payment_country' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'payment_country_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'payment_zone' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'payment_zone_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'payment_address_format' => [
				'd' => NULL,
				'n' => false,
				't' => 'text',
			],
			'payment_custom_field' => [
				'd' => NULL,
				'n' => false,
				't' => 'text',
			],
			'payment_method' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'payment_code' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_first_name' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_last_name' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_company' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_address_1' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_address_2' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_city' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_postcode' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_country' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_country_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'shipping_zone' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_zone_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'shipping_address_format' => [
				'd' => NULL,
				'n' => false,
				't' => 'text',
			],
			'shipping_custom_field' => [
				'd' => NULL,
				'n' => false,
				't' => 'text',
			],
			'shipping_method' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'shipping_code' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'comment' => [
				'd' => NULL,
				'n' => false,
				't' => 'text',
			],
			'total' => [
				'd' => '0.0000',
				'n' => false,
				't' => 'decimal',
			],
			'order_status_id' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'affiliate_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'commission' => [
				'd' => NULL,
				'n' => false,
				't' => 'decimal',
			],
			'marketing_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'tracking' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'language_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'currency_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'currency_code' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'currency_value' => [
				'd' => '1.00000000',
				'n' => false,
				't' => 'decimal',
			],
			'ip' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'forwarded_ip' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'user_agent' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'accept_language' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'date_added' => [
				'd' => NULL,
				'n' => false,
				't' => 'datetime',
			],
			'date_modified' => [
				'd' => NULL,
				'n' => false,
				't' => 'datetime',
			],
		];
		$params['order']= $this->filter($params['order'], $filterArray,false);

		$sql = 'INSERT INTO `order` 
			
			(';
		$sql .= '`' . implode('`,`', array_keys($params['order']));
		$sql .= '` )
			
	  	VALUES ( ';

		list($_sql, $_params) = $this->expandArray($params['order'], 'order');

		$sql .= $_sql;

		if (is_array($_params)) {
			$paramTypes = array_merge($paramTypes, $_params);
		}

		$sql .= ' )';

		if ($sql) {
			$stmt['order'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['order']) {
				if (method_exists($stmt['order'], 'get_result')) {
					$result = $stmt['order']->get_result();
				} else {
					$result = $this->get_result($stmt['order']);
				}
			}

			/*
			if ('order' == '_') {
				$value = $result->fetch_all(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('order' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['order'][$row['']] = $values;

							$results['order'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('order' == '_') {
					$value = $result->fetch_all(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['order'] = $result->fetch_all(MYSQLI_ASSOC);
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}
}
