<?php
/*
 * @version			$Id: index.php 10381 2008-06-01 03:35:53Z $
 * @package			Carteasy
 * @copyright		Copyright (C) 2005 - 2011 Wlanium
 * @license			proprietary
 * @author			Thomas Bartelt
 * Carteasy is a web shop system
 */

include_once './core/output/invoicepdf.class.php';
include_once './core/output/deliverynotepdf.class.php';
include_once './core/output/deliveryitemshtml.class.php';
include_once './core/country.class.php';
include_once './core/order.class.php';
include_once './core/orderstatus.class.php';
include_once './core/customer.class.php';
include_once './core/customerhelper.class.php';
include_once './core/customergroups.class.php';
include_once './core/delivererhelper.class.php';
include_once './core/payment_method.class.php';
include_once './core/paymenthelper.class.php';
include_once './core/item.class.php';
include_once './core/tax.class.php';
include_once './core/money.class.php';
include_once './core/cancellationrequestshelper.class.php';
include_once './core/manufacturerhelper.class.php';

class OrderHelper {

	//=========================================
	//
	//	THIS CLASS IS STATIC ONLY
	//
	//=========================================
	private function __construct() {}

	private function __clone() {}

	//=========================================
	//
	//	THE PUBLIC INTERFACE
	//
	//=========================================

	public static function request_cancellation($order_id, $data) {
		$db = Registry::get('base')->db;

		$sql = "UPDATE orders SET
					cancellation_reason_id='".$db->real_escape_string($data['cancellation_reason_id'])."',
					cancellation_info='".$db->real_escape_string($data['cancellation_info'])."',
					cancellation_date='".$db->real_escape_string($data['cancellation_date'])."',
					cancellation_status='".$db->real_escape_string($data['cancellation_status'])."',
					order_status=12
					WHERE id=".$db->real_escape_string($order_id);
		$result = $db->query($sql);
		return $db->query($db);
	}

	public static function order_cancellability_status($order_id) {
		$has_cancellation_request = OrderHelper::order_has_cancellation_request($order_id);

		// default status
		$status = 'not_cancellable';

		if ($has_cancellation_request) {
			$cancellation_request_status = CancellationRequestsHelper::get_cancellation_request_status_by_order_id($order_id);

			if ($cancellation_request_status !== false) {
				if ($cancellation_request_status == '0') {
					$status = 'cancellation_requested';
				} else if ($cancellation_request_status == '1') {
					$status = 'cancellation_approved';
				} else if ($cancellation_request_status == '2') {
					$status = 'cancellation_denied';
				}
			}
		} else {
			$is_cancellable = OrderHelper::is_order_cancellable($order_id);
			if ($is_cancellable) {
				$status = 'cancellable';
			}
		}

		$order_is_cancelled = OrderHelper::is_order_cancelled($order_id);

		if ($order_is_cancelled) {
			$status = 'cancelled';
		}


		return $status;
	}

	public static function get_order_status_cancellability_array() {
		$db = Registry::get('base')->db;

		$sql = "SELECT id, cancellable FROM order_status";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$return_data[$obj->id] = $obj->cancellable;
			}

			return $return_data;
		}

		return false;
	}

	public static function is_order_cancellable($order_id) {
		$db = Registry::get('base')->db;

		$cancellability_array = OrderHelper::get_order_status_cancellability_array();

		$sql = "SELECT * FROM orders WHERE id=".$db->real_escape_string($order_id);

		$result = $db->query($sql);
		$is_order_cancellable = false;

		if ($result->num_rows > 0) {
			$order_data = $result->fetch_object();

			if ($cancellability_array[$order_data->order_status] == 1) {
				$is_order_cancellable = true;
			}
		}

		return $is_order_cancellable;
	}

	public static function is_order_cancelled($order_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT order_status FROM orders WHERE id=".$db->real_escape_string($order_id)." AND order_status=6";

		/*$sql = "SELECT
					cancellation_date
				FROM
					orders
				WHERE
					cancellation_date IS NOT NULL
				AND
					id=".$db->real_escape_string($order_id);*/

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			return true;
		}

		return false;
	}

	public static function order_has_cancellation_request($order_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT
					cancellation_reason_id,
					cancellation_info,
					cancellation_date,
					cancellation_status
				FROM
					orders
				WHERE
					id=".$db->real_escape_string($order_id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			$order_data = $result->fetch_object();

			if ($order_data->cancellation_status == 1) {
				return true;
			}
		}

		return false;
	}

	// TODO:check
	public static function reap_order_items($items) {
		$increment = array();
		$remove = array();

		// i iterate over items twice since i
		// am comparint every element to every other element
		$i = 0;
		foreach ($items as $item) {
			$j = 0;
			foreach ($items as $other_item) {
				// don't compare item to itself
				if ($i != $j) {
					// don't compare items to previous items
					// they where already compared
					if ($j > $i) {
						if ($item['id'] == $other_item['id']) {
							if ($item['variant1'] == $other_item['variant1']) {
								if ($item['variant2'] == $other_item['variant2']) {
									// we have a macht
									$increment[] = $i; // for copieing
									$remove[] = $j; // for removing
								}
							}
						}
					}
				}

				$j++;
			}
			$i++;
		}

		$len = count($remove);
		if ($len > 0) {
			// copy the quantity of the duplicate to the original
			for ($x = 0; $x < $len; $x++) {
				$items[$increment[$x]]['quantity'] += $items[$remove[$x]]['quantity'];
			}

			$y = 0;
			foreach ($items as $item) {
				if (!in_array($y, $remove)) {
					$return[] = $item;
				} 

				$y++;
			}
		}

		return $items;
	}

	public static function get_order_fiels($order_id, $field_array) {
		$db = Registry::get('base')->db;

		if (count($field_array) > 0) {
			$field_string = '';

			foreach ($field_array as $field) {
				if ($field_string == '') {
					$field_string .= $field;
				} else {
					$field_string .= ', '.$field;
				}
			}

			if ($field_string != '') {
				$sql = "SELECT ";
				$sql .= $field_string;
				$sql .= " FROM orders WHERE id=".$db->real_escape_string($order_id);

				$result = $db->query($sql);

				if ($result->num_rows > 0) {
					return $result->fetch_object();
				}
			}
		}

		return false;
	}

	public static function set_order_comment($order_id, $comment, $revisor_id) {
		$db = Registry::get('base')->db;

		$sql = "UPDATE orders SET
					customer_info='".$db->real_escape_string($comment)."',
					revisor_id='".$db->real_escape_string($revisor_id)."'
				WHERE
					id=".$db->real_escape_string($order_id);

		$result = $db->query($sql);

		if ($result) {
			return true;
		}

		return false;
	}

	public static function set_shipping_address($order_id, $shipping_address, $revisor_id) {
		$db = Registry::get('base')->db;

		$sql = "UPDATE orders SET ";

		$size = count($shipping_address);
		$i = 1;
		foreach ($shipping_address as $name => $value) {
			$sql .= $name."='".$db->real_escape_string($value)."'";

			if ($i < $size) {
				$sql .= ", ";
				$i++;
			}
		}

		$sql .= ", revisor_id='".$db->real_escape_string($revisor_id)."'
				WHERE
					id=".$db->real_escape_string($order_id);

		$result = $db->query($sql);

		if ($result) {
			return true;
		}

		return false;
	}

	public static function set_billing_address($order_id, $billing_address, $revisor_id) {
		$db = Registry::get('base')->db;

		$sql = "UPDATE orders SET ";

		$size = count($billing_address);
		$i = 1;
		foreach ($billing_address as $name => $value) {
			$sql .= $name."='".$db->real_escape_string($value)."'";

			if ($i < $size) {
				$sql .= ", ";
				$i++;
			}
		}

		$sql .= ", revisor_id='".$db->real_escape_string($revisor_id)."'WHERE id=".$db->real_escape_string($order_id);

		$result = $db->query($sql);

		if ($result) {
			return true;
		}

		return false;
	}

	// TODO: put this function in a general utility class with a better name
	private function php_2_string($data) {
		$string = '';
		if (is_array($data)) {
			$string .= 'array(';
			foreach ($data as $key => $value) {
				if (is_array($value)) {
					$string .= "'$key' => ".OrderHelper::php_2_string($value).", ";
				} else {
					$string .= "'$key' => '$value', ";
				}
			}
			$string .= ")";
		} else {
			$string .= "'".$data."'";
		};
		return $string;
	}


	// TODO: check
	public static function set_revision_in_order_history($data) {
		$db = Registry::get('base')->db;

		$sql = "INSERT INTO order_history ('order_id', 'revisor_id', 'previous_revisor_id', 'changes') ";
		$sql .= " VALUES ('".$data['order_id']."','".$data['revisor_id']."', '".$data['previous_revisor_id']."', '".$db->real_escape_string(OrderHelper::php_2_string($data['changes']))."')";

		echo $sql;
		exit();
	}

	public static function get_revision_history_for_order($id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT t.id FROM orders n 
				JOIN orders t ON t.order_number = n.order_number 
				WHERE n.id=".$db->real_escape_string($id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			$return = '';
			while ($obj = $result->fetch_object()) {
				if ($return == '') {
					$return .= $obj->id;
				} else {
					$return .= ', '.$obj->id;
				}
			}

			return $return;
		}

		return false;
	}

	public static function get_money_back($order_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT * FROM order_item WHERE order_id=".$db->real_escape_string($order_id)." AND item_status=1";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			$sum = 0.0;

			while ($obj = $result->fetch_object()) {
				$sum += $obj->price + $obj->taxes;
			}

			return $sum;
		}

		return false;
	}

	public static function set_paypal_transaction_data($order_id, $paypal_data) {
		$db = Registry::get('base')->db;
		$transactionid = $paypal_data['PAYMENTINFO_0_TRANSACTIONID'];

		$sql = "UPDATE orders SET paypal_transaction_id='".$db->real_escape_string($transactionid)."'
				WHERE id=".$db->real_escape_string($order_id);

		$return = $db->query($sql);

		return $return;
	}

	public static function set_paypal_user_data($order_id, $paypal_data) {
		$db = Registry::get('base')->db;
		$email = $paypal_data['EMAIL'];
		$payerid = $paypal_data['PAYERID'];

		$sql = "UPDATE orders SET 
				paypal_email='".$db->real_escape_string($email)."', 
				paypal_payerid='".$db->real_escape_string($payerid)."' 
				WHERE id=".$db->real_escape_string($order_id);

		$return = $db->query($sql);

		return $return;
	}

	public static function get_all_orders_with_money_back() {
		$db = Registry::get('base')->db;

		$sql = "SELECT
					o.id,
					o.order_number,
					o.order_revision,
					o.customer_name,
					o.order_date,
					oo.order_total,
					oo.shipping_charges,
					oo.payment_method_charges,
					oo.order_total - o.order_total AS total_back
				FROM
					orders o
				LEFT JOIN
					order_item i
				ON
					o.id = i.order_id
				RIGHT JOIN
					orders oo
				ON
					oo.id = o.revised_order_id
				WHERE
					o.payment_method = 4
				AND
					o.payment_status = 2
				AND
					i.item_status = 1";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$obj->total_charges = $obj->payment_method_charges + $obj->shipping_charges;
				$return[] = $obj;
			}

			return $return;
		}

		return false;
	}

	public static function get_all_customer_orders($customer_id) {
		$db = Registry::get('base')->db;

		$order_status_array = OrderStatus::get_id_to_name_array();

		$sql = "SELECT * FROM orders o1
				WHERE o1.order_revision=(SELECT 
						MAX(o2.order_revision) 
						FROM orders o2 
						WHERE o1.order_number = o2.order_number)
				OR o1.order_revision IS NULL
				AND o1.customer_id=".$db->real_escape_string($customer_id)."
				AND o1.order_addendum != 'N'
				ORDER BY o1.order_number DESC";

		$sql = "SELECT * FROM orders o1
				WHERE o1.customer_id=".$db->real_escape_string($customer_id)."
				AND o1.order_addendum != 'N'
				ORDER BY o1.order_number DESC";

		$sql = "SELECT * FROM orders o1
				WHERE o1.customer_id=".$db->real_escape_string($customer_id)."
				ORDER BY o1.order_number DESC";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$obj->invoice_status_text = $order_status_array[$obj->order_status];
				$return[] = $obj;
			}

			return $return;
		}

		return false;
	}

	public static function get_items_on_backorder() {
		$db = Registry::get('base')->db;

		$manufacturer_array = ManufacturerHelper::get_manufacturer_array();

		$sql = "SELECT
					o.id AS order_id,
					o.customer_id,
					o.customer_name,
					o.order_number,
					o.order_revision,
					o.order_addendum,
					oi.item_id,
					oi.item_name,
					oi.price AS item_price,
					oi.amount AS item_quantity,
					oi.delivery_date AS item_delivery_date,
					i.manufacturer_id
				FROM
					orders o
				LEFT JOIN
					order_item oi
				ON
					o.id = oi.order_id
				LEFT JOIN
					items i
				ON
					oi.item_id = i.id
				WHERE
					o.order_status = 9
				AND
					oi.item_status = 2";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				if ($manufacturer_array) {
					try {
						$obj->manufacturer_name = $manufacturer_array[(int)$obj->manufacturer_id];
					} catch (Exception $e) {
						$obj->manufacturer_name = '';
						$obj->manufacturer_id = 0;
					}
				}

				$return[] = $obj;
			}

			return $return;
		}

		return false;
	}

	public static function get_order_items_html($order_id) {
		$order = new Order(Registry::get('base'));
		$order->set_id($order_id);
		$order_data = $order->get_data();

		$cg_object = new CustomerGroups(Registry::get('base'));
		$cg_data = $cg_object->get_by_user($order_data->customer_id);
		$order_data->cg_show_taxes = $cg_data->show_tax;

		$di_html = new DeliveryItemsHTML($order_data);
		return $di_html->PrintOrder();
	}

	public static function get_payment_data($order_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT
					order_number,
					order_revision,
					payment_status,
					payment_method,
					paypal_token,
					paypal_transaction_id
				FROM
					orders
				WHERE
					id=".$db->real_escape_string($order_id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			return $result->fetch_object();
		}

		return false;
	}

	public static function order_exists($order_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT * FROM orders WHERE id=".$db->real_escape_string($order_id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			return true;
		}

		return false;
	}

	public static function make_order_addendum($order_id) {
		$new_order_id = OrderHelper::clone_order($order_id);
		if ($new_order_id) {
			OrderHelper::copy_items_on_back_order($order_id, $new_order_id);
		}
	}

	public static function get_addendum_for_order($order_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT order_number, order_revision FROM orders
				WHERE id =".$db->real_escape_string($order_id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			$order_info = $result->fetch_object();

			$sql = "SELECT id FROM orders 
					WHERE order_number=".$order_info->order_number;

			if (is_numeric($order_info->order_revision)) {
				$sql .= " AND order_revision=$order_info->order_revision";
			} else {
				$sql .= " AND order_revision IS NULL";
			}

			$sql .= " AND order_addendum='N'";

			$result = $db->query($sql);

			if ($result->num_rows > 0) {
				return $result->fetch_object()->id;
			}
		}

		return false;
	}

	public static function copy_items_on_back_order($old_order_id, $new_order_id) {
		$db = Registry::get('base')->db;
		$fields = OrderHelper::get_order_item_table_fields(false);

		$sql = "SELECT
					price,
					taxes,
					tax_id,
					tax_rate,
					item_name,
					item_number,
					variant1,
					variant2,
					item_status,
					item_status_text,
					delivery_date,
					backorder_quantity
				FROM
					order_item
				WHERE
					order_id=".$db->real_escape_string($old_order_id)." 
				AND
					item_status=2";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$queries[] = "INSERT INTO order_item 
							(
							order_id, 
							price, 
							taxes, 
							tax_id, 
							tax_rate, 
							item_name, 
							item_number, 
							variant1, 
							variant2, 
							item_status, 
							item_status_text, 
							delivery_date, 
							amount
							)
						VALUES 
							(
							'".$db->real_escape_string($new_order_id)."',
							'".$obj->price."',
							'".$obj->taxes."',
							'".$obj->tax_id."',
							'".$obj->tax_rate."',
							'".$obj->item_name."',
							'".$obj->item_number."',
							'".$obj->variant1."',
							'".$obj->variant2."',
							'".$obj->item_status."',
							'".$obj->item_status_text."',
							'".$obj->delivery_date."',
							'".$obj->backorder_quantity."'
							)
						";
			}

			foreach ($queries as $sql) {
				$db->query($sql);
			}

			return true; // I don't like this but ...
		}

		return false;
	}

	public static function clone_order($order_id) {
		$db = Registry::get('base')->db;
		$fields = OrderHelper::get_order_table_fields(false);

		$insert = "INSERT INTO orders (";
		$select = ") SELECT ";
		$from = " FROM orders WHERE id=".$db->real_escape_string($order_id);

		$i = 0;
		foreach ($fields as $field) {
			if ($field == 'order_addendum') {
			} else if ($field == 'order_status') {
			} else {
				if ($i == 0) {
					$insert .= $field;
					$select .= $field;
				} else {
					$insert .= ', '.$field;
					$select .= ', '.$field;
				}

				$i++;
			}
		}

		$insert .= ', order_addendum';
		$select .= ", 'N'";

		$insert .= ', order_status';
		$select .= ', 9';

		$sql = $insert.$select.$from;

		$result = $db->query($sql);

		if ($result) {
			return $db->insert_id;
		}

		return false;
	}

	public static function get_order_table_fields($with_id = true) {
		$db = Registry::get('base')->db;

		$sql = "SHOW COLUMNS FROM orders";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				if ($with_id) {
					$return[] = $obj->Field;
				} else {
					if ($obj->Key != 'PRI') {
						$return[] = $obj->Field;
					}
				}
			}

			return $return;
		}

		return false;
	}

	public static function get_order_item_table_fields($with_id = true) {
		$db = Registry::get('base')->db;

		$sql = "SHOW COLUMNS FROM order_item";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				if ($with_id) {
					$return[] = $obj->Field;
				} else {
					if ($obj->Key != 'PRI') {
						$return[] = $obj->Field;
					}
				}
			}

			return $return;
		}

		return false;
	}

	public static function order_has_addendum($order_id) {
		$db = Registry::get('base')->db;

		// 2 = nachliefern and needs an addendum
		$sql = "SELECT * FROM order_item
				WHERE order_id=".$db->real_escape_string($order_id).
				" AND item_status=2";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return $return;
		}

		return false;
	}

	public static function order_has_changed($order_id, $new_order_items_list) {
		$old_order_items_list = OrderHelper::__get_order_items_from_db($order_id);
		$new_order_items_list = OrderHelper::__get_order_items_from_new_order_list($new_order_items_list);

		$something_has_changed = true;

		if ($old_order_items_list) {
			$something_has_changed = OrderHelper::__compare_orders($old_order_items_list, $new_order_items_list);
		}

		return $something_has_changed; // if the previous order has no items the order has changed per default
	}

	public static function get_orders_by_type() {
		$db = Registry::get('base')->db;
		$order_statuses = OrderStatus::get_all();

		if ($order_statuses) {
			foreach ($order_statuses as $status) {
				if ($status->is_showable == 1) {
					$temp['count'] = OrderHelper::__get_order_status_count($status->id);
					$temp['name'] = $status->name;
					$temp['id'] = $status->id;
					$temp['selector'] = $status->selector;

					$return[] = $temp;
				}
			}
		}

		return $return;
	}

	public static function set_payment_status($id, $status) {
		$db = Registry::get('base')->db;

		$sql = "UPDATE orders SET
				payment_status = ".$db->real_escape_string($status)." 
				WHERE id = ".$db->real_escape_string($id);

		if ($db->query($sql)) {
			if ($status == '2') {
				$date = getdate();
				$today = $date['year'].'-'.$date['mon'].'-'.$date['mday'];

				$sql = "UPDATE orders SET payment_date = '$today'
						WHERE id = ".$db->real_escape_string($id)."
						AND payment_date IS NULL";

				if ($db->query($sql)) {
					return $id;
				}
			} else {
				return $id;
			}
		}

		return false;
	} // end set_payment_status

	public static function set_payment_method($id, $method) {
		$db = Registry::get('base')->db;

		$sql = "UPDATE orders SET payment_method = ".$db->real_escape_string($method)."
				WHERE id = ".$db->real_escape_string($id);

		if ($db->query($sql)) {
			return $id;
		}

		return false;
	} // end set_payment_method

	public static function set_order_status($id, $status) {
		$db = Registry::get('base')->db;

		$sql = "UPDATE orders SET order_status = ".$db->real_escape_string($status)."
				WHERE id = ".$db->real_escape_string($id);

		if ($db->query($sql)) {
			if ($status == 6) {
				$item_object = new Item(Registry::get('base'));
				$order_object = new Order(Registry::get('base'));

				$raw_order = $order_object->get_order_by_id($id);
				$items = $raw_order['items'];

				foreach ($items as $item) {
					$item_object->increase_inventory($item->amount, $item->variant1, $item->variant2, $item->item_id);
				}
			}
			return $id;
		}

		return false;
	} // end set_order_status

	public static function set_shipping_method($id, $method) {
		$db = Registry::get('base')->db;

		$sql = "UPDATE orders SET shipping_method = ".$db->real_escape_string($method)."
				WHERE id = ".$db->real_escape_string($id);

		if ($db->query($sql)) {
			return $id;
		}

		return false;
	} // end set_shipping_method

	public static function set_payment_statuses($seting, $ids) {
		$db = Registry::get('base')->db;

		$sql = "UPDATE orders SET payment_status = ".$db->real_escape_string($seting)."
				WHERE id IN (".$db->real_escape_string($ids).")";

		if ($db->query($sql)) {
			if ($seting == '2') {
				$date = getdate();
				$today = $date['year'].'-'.$date['mon'].'-'.$date['mday'];

				$sql = "UPDATE orders SET payment_date = '$today'
						WHERE id IN (".$db->real_escape_string($ids).") 
						AND payment_date IS NULL";

				if ($db->query($sql)) {
					return true;
				}
			} else {
				return true;
			}
		}

		return false;
	} // end set_payment_statuses

	public static function set_order_statuses($seting, $ids) {
		$db = Registry::get('base')->db;

		$sql = "UPDATE orders SET 
				order_status = ".$db->real_escape_string($seting)." WHERE id IN (".$db->real_escape_string($ids).")";

		if ($status == 6) {
			$ids = explode(', ', $ids);
			$item_object = new Item(Registry::get('base'));
			$order_object = new Order(Registry::get('base'));

			foreach ($ids as $id) {
				$raw_order = $order_object->get_order_by_id($id);
				$items = $raw_order['items'];

				foreach ($items as $item) {
					$item_object->increase_inventory($item->amount, $item->variant1, $item->variant2, $item->item_id);
				}
			}
		}

		return $db->query($sql);
	} // end set_order_statuses

	public static function get_all_orders_by_order_number($number) {
		$db = Registry::get('base')->db;

		$sql = "SELECT * FROM orders WHERE order_number=".$db->real_escape_string($number);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$obj->revisor_name = CustomerHelper::get_revisor_data($obj->revisor_id);

				$return[] = $obj;
			}

			return $return;
		}

		return false;
	} // end get_all_orders_by_order_number

	public static function order_has_revision($id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT order_number
				FROM orders
				WHERE id=".$db->real_escape_string($id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			$num = $result->fetch_object()->order_number;

			$sql = "SELECT * FROM orders
					WHERE order_number=".$db->real_escape_string($num);

			$result = $db->query($sql);

			if ($result->num_rows > 1) {
				return true; // there is more then one order with this order number
			}

			return false; // there is one order with this order number
		}

		return false; // there are no orders, very unlikely
	}

	public static function get_open_orders($limit) {
		$db = Registry::get('base')->db;

		$sql = "SELECT * FROM orders WHERE order_status = 1 LIMIT ".$db->real_escape_string($limit);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return $return;
		}

		return false;
	}

	public static function get_order_items($order_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT * FROM order_item oi WHERE order_id = ".$db->real_escape_string($order_id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$return = $obj;
			}

			return $return;
		}

		return false;
	} // end get_order_items

	public static function get_order_items_with_image($order_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT 
					a.id,
					a.order_id,
					a.item_id,
					a.amount,
					a.price,
					a.tax,
					a.item_name,
					a.item_number,
					i.file_name
				FROM
					order_item a
				LEFT JOIN
					item_files i
				ON
					i.item_id = a.item_id
				WHERE
					a.order_id = ".$db->real_escape_string($order_id).
				" AND
					(i.rang = 1
				OR
					i.rang IS NULL)
				GROUP BY
					a.id";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return $return;
		}

		return false;
	} // end get_order_items_with_image

	public static function get_all_order_items_by_customer_id($customer_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT
					i.id, i.order_id, i.item_id, i.amount, i.price, i.price, i.item_name, i.item_number
				FROM 
					order_item i
				JOIN 
					orders o
				WHERE
					i.order_id = o.id
				AND
					o.customer_id = ".$db->real_escape_string($customer_id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return $return;
		}

		return false;
	} // end get_all_order_items_by_customer_id

	public static function get_next_order_number() {
		$db = Registry::get('base')->db;

		$sql = "SELECT order_number FROM orders ORDER BY order_number DESC LIMIT 1";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			return $result->fetch_object()->order_number + 1;
		} else if ($result->num_rows == 0) {
			if (Config::has_key('invoice_start') && Config::is_set('invoice_start')) {
				return Config::get_value('invoice_start');
			}
		}

		return 100000;
	} // end get_next_order_number

	public static function get_order_number($id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT order_number FROM orders WHERE id=".$db->real_escape_string($id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			return $result->fetch_object()->order_number;
		}

		return false;
	} // end get_order_number

	public static function createOrderObject($data, $base_object) {
		$base = $db = Registry::get('base');
		$db = Registry::get('base')->db;

		$orderDataObject = new stdClass();

		// Customer info
		$orderDataObject->customer = CustomerHelper::getCustomerDataForOrder(
			$data['customerId']
		);

		// Billing address
		$orderDataObject->billingAddress = CustomerHelper::getAddressByIds(
			$data['customerId'], 
			$data['billingAddressId']
		);

		// Shipping address
		$orderDataObject->shippingAddress = CustomerHelper::getAddressByIds(
			$data['customerId'], 
			$data['shippingAddressId']
		);

		// Gift certificate
		if (isset($data['giftCertificate'])) {
			$orderDataObject->giftCertificateCode = $data['giftCertificate'];
		}

		// Items
		$orderDataObject->items = array();
		$orderDataObject->itemsQuantity = 0;
		$orderDataObject->itemsOverallQuantity = 0;
		$orderDataObject->itemSumNet = 0;
		$orderDataObject->itemSumGross = 0;
		$orderDataObject->itemSumVat = array('id' => array(), 'rate' => array());

		$itemsQuery = "
			SELECT
				item_id,
				parent_item_id,
				variant_1,
				variant_2,
				quantity,
				price_sum_net, 
				price_sum_gross,
				stamp,
				user_data
			FROM
				shopping_cart_items
			WHERE
				session_id = '".$db->real_escape_string($data['cartId'])."'
		";
		$rsItems = $db->query($itemsQuery);

		if ($base->customer_group->id) {
			$customer_group_id = $base->customer_group->id;
		} else {
			$customer_group_id = 1;
		}

		while ($itemDataSet = $rsItems->fetch_object()) {
			$item_object = new Item($base_object);

			$item_object->id = $itemDataSet->item_id;

			$item = $item_object->get_data($customer_group_id);

			$prices = $item_object->calculate_prices($item, $itemDataSet->quantity, $itemDataSet->variant_1, $itemDataSet->variant_2);

			$orderDataObject->items[] = array(
				'itemId' => $itemDataSet->item_id,
				'parentItemId' => $itemDataSet->parent_item_id,
				'variant1' => $itemDataSet->variant_1,
				'variant2' => $itemDataSet->variant_2,
				'quantity' => $itemDataSet->quantity,
				'prices' => $prices,
				'mainItemNumber' => $item->number,
				'variantItemNumber' => (isset($item->variant_data[$itemDataSet->variant_1][$itemDataSet->variant_2]['item_number']) ? $item->variant_data[$itemDataSet->variant_1][$itemDataSet->variant_2]['item_number'] : ''),
				'vatId' => $item->tax_id,
				'vatRate' => $item->tax,
				'vatValue' => $prices['itemPriceGross'] - $prices['itemPriceNet'],
				'name' => $item->name,
				'stamp' => $itemDataSet->stamp,
				'userData' => $itemDataSet->user_data
			);

			++$orderDataObject->itemsQuantity;
			$orderDataObject->itemsOverallQuantity += $itemDataSet->quantity;

			$orderDataObject->itemSumNet += $prices['itemPriceNet'] * $itemDataSet->quantity;
			$orderDataObject->itemSumGross += $prices['itemPriceGross'] * $itemDataSet->quantity;
			if (!isset($orderDataObject->itemSumVat['id'][$item->tax_id])) {
			$orderDataObject->itemSumVat['id'][$item->tax_id] = 0;
		}
		$orderDataObject->itemSumVat['id'][$item->tax_id] += ($prices['itemPriceGross'] - $prices['itemPriceNet']) * $itemDataSet->quantity;
			if (!isset($orderDataObject->itemSumVat['rate'][$item->tax])) {
			$orderDataObject->itemSumVat['rate'][$item->tax] = 0;
		}
		$orderDataObject->itemSumVat['rate'][$item->tax] += ($prices['itemPriceGross'] - $prices['itemPriceNet']) * $itemDataSet->quantity;
		}

		// Payment
		$orderDataObject->payment = PaymentHelper::getPaymentDataByIdAndCountry(
			$data['paymentMethodId'], 
			$orderDataObject->shippingAddress->country,
			$base_object->config->shopConfiguration['default_vat_value'],
			$orderDataObject->itemSumGross
		);

		// Deliverer
		$orderDataObject->shipping = DelivererHelper::getDeliveryDataByIdAndCountry(
			$data['shippingMethodId'], 
			$orderDataObject->shippingAddress->country,
			$base_object->config->shopConfiguration['default_vat_value']
		);

		return $orderDataObject;
	}

	public static function get_order_invoice_pdf($order_id, $return_type = false) {
		if (!isset($base_object)) {
			$base_object = $db = Registry::get('base');
		}

		if (isset($base_object->config->shopConfiguration['has_early_payers_rebate']) && $base_object->config->shopConfiguration['has_early_payers_rebate'] == 1) {
			$skonto = true;
			$skonto_amount = $base_object->config->shopConfiguration['early_payers_rebate_amount'] / 100.0;
		} else {
			$skonto = false;
		}

		if ($order_id) {
			$order = new Order(Registry::get('base'));
			$order_data = $order->get_order_by_id($order_id);

			$cg_object = new CustomerGroups();
			$cg_data = $cg_object->get_by_user($order_data['header']->customer_id);
			$order_data['header']->cg_show_taxes = $cg_data->show_tax;

			$pdf = new InvoicePDF();
			$pdf->startPage();

			$invoice_address = '';

			$invoice_address .= $order_data['header']->billing_honorific_text."\n";
			if ($order_data['header']->billing_company != '') {
				$invoice_address .= $order_data['header']->billing_company."\n";
			}
			$invoice_address .= $order_data['header']->billing_firstname.' '.$order_data['header']->billing_surname."\n";
			$invoice_address .= $order_data['header']->billing_street.' '.$order_data['header']->billing_house_number."\n";
			$invoice_address .= $order_data['header']->billing_zip_code.' '.$order_data['header']->billing_city."\n";
			$invoice_address .= Country::get_name_by_id($order_data['header']->billing_country);

			$pdf->PrintCustomerAddress($invoice_address);

			$order_date = explode('-', $order_data['header']->order_date);
			$billing_date = explode('-', $order_data['header']->billing_date);

			$order_number = $order_data['header']->order_number;
			if ($order_data['header']->order_revision != '') {
				$order_number .= '-'.$order_data['header']->order_revision;
			}

			$invoice_data = array(
				'Kundennummer' => $order_data['header']->customer_number,
				'Bestellnummer' => $order_number,
				'Bestelldatum' => $order_date[2].'.'.$order_date[1].'.'.$order_date[0],
				'Rechnungsnummer' => $order_data['header']->invoice_number,
				'Rechnungsdatum' => $billing_date[2].'.'.$billing_date[1].'.'.$billing_date[0]
			);

			$pdf->PrintInvoiceData($invoice_data);

			$pdf->PrintInvoiceOrder($order_data);

			// if payment method has a notice, print it
			$pm = new Payment_method(Registry::get('base'));
			$paymentmethod = $pm->get_by_id($order_data['header']->payment_method);
			// get paymorrow bill data
			if ($order_data['header']->payment_method == 10) {
				$paymentmethod->invoice_text .= "\n\nBitte überweisen Sie den Betrag auf folgendes Konto:\n";
				if ($order_data['header']->paymorrow_national_bank_code) {$paymentmethod->invoice_text .= "\nBLZ: ".$order_data['header']->paymorrow_national_bank_code."\n";}
				if ($order_data['header']->paymorrow_national_bank_account_number) {$paymentmethod->invoice_text .= "Konto-Nr: ".$order_data['header']->paymorrow_national_bank_account_number."\n";}
				if ($order_data['header']->paymorrow_bic) {$paymentmethod->invoice_text .= "BIC: ".$order_data['header']->paymorrow_bic."\n";}
				if ($order_data['header']->paymorrow_iban) {$paymentmethod->invoice_text .= "IBAN: ".$order_data['header']->paymorrow_iban."\n";}
				if ($order_data['header']->paymorrow_payment_reference) {$paymentmethod->invoice_text .= "\nBitte bei Überweisung mit angeben: ".$order_data['header']->paymorrow_payment_reference."\n";}
			}
			if ($paymentmethod->invoice_text != '' && $paymentmethod->invoice_text != NULL) {
				// get customer banking account data
				$customer_banking_account = CustomerHelper::getCustomerBankingAccount($order_data['header']->customer_id);
				$banking_account_string = 
					$customer_banking_account->bank.
						', BLZ.: '.$customer_banking_account->bank_number.
						', Kto-Nr.: '.$customer_banking_account->account_number;
				if ($customer_banking_account->iban && $customer_banking_account->swift_bic) {
					$banking_account_string .=
						', IBAN: '.$customer_banking_account->iban.
						', BIC: '.$customer_banking_account->swift_bic;
				}

				$paymentmethod->invoice_text = str_replace('[#customer_banking_account]',$banking_account_string,$paymentmethod->invoice_text);
				$pdf->PrintNotice('Zahlungshinweis - '.$paymentmethod->name, $paymentmethod->invoice_text);
			}

			// print skonto
			if ($skonto) {
				$pdf->PrintEarlyPaymentRebate($skonto_amount);
			}

			// if invoice has a notice, print it
			$invoice_notice = $order_data['header']->invoice_info;
			if ($invoice_notice != '' && $invoice_notice != NULL) {
				$pdf->printNotice('Zusatzinfo', $invoice_notice);
			}

			$delivery_address .= $order_data['header']->shipping_honorific_text."\n";
			if ($order_data['header']->shipping_company != '') {
				$delivery_address .= $order_data['header']->shipping_company."\n";
			}
			$delivery_address .= $order_data['header']->shipping_firstname.' '.$order_data['header']->shipping_surname."\n";
			$delivery_address .= $order_data['header']->shipping_street.' '.$order_data['header']->shipping_house_number."\n";
			$delivery_address .= $order_data['header']->shipping_zip_code.' '.$order_data['header']->shipping_city."\n";
			$delivery_address .= Country::get_name_by_id($order_data['header']->shipping_country);

			$pdf->PrintNotice('Lieferung erfolgt an:', $delivery_address);

			$filename = 'Rechnung-'.$order_data['header']->invoice_number.'.pdf';

			$filename = str_replace('/', '-', $filename);

			if ($return_type == 1) {
				return $pdf->Output('', 'S');

				exit();
			} else if ($return_type == 2) {
				$path = $_SERVER['DOCUMENT_ROOT'].'/web/'.SHOP_SYSTEM.'/documents/pdf/';

				$pdf->Output($path.$filename, 'F');
			} else {
				$pdf->Output($filename, 'D');

				exit();
			}
		} else {
			echo "ERROR: Keine ID.";
			exit();
		}
	}

	public static function get_order_delivery_note_pdf($order_id) {;
		if ($order_id) {
			$order = new Order(Registry::get('base'));
			$order->set_id($order_id);
			$order_data = $order->get_data();

			if ($order_data->items != null && sizeof($order_data->items) > 0 ) {
				foreach ($order_data->items As $index => $row) {
					if (sizeof($row->attribute_array) > 0) {
						foreach ($row->attribute_array AS $indece => $att) {

							if ($order_data->items[$index]->variant1 && $att->value && $att->name && $order_data->items[$index]->variant1 == $att->value) {
								$order_data->items[$index]->vn1 = $att->name;
							}

							if ($order_data->items[$index]->variant2 && $att->value && $att->name && $order_data->items[$index]->variant2 == $att->value) {
								$order_data->items[$index]->vn2 = $att->name;
							}
						}
					}
				}
			}
			$cg_object = new CustomerGroups();
			$cg_data = $cg_object->get_by_user($order_data->customer_id);
			$order_data->cg_show_taxes = $cg_data->show_tax;

			$pdf = new DeliveryNotePDF();
			$pdf->startPage();

			$delivery_address .= $order_data->shipping_honorific_text."\n";
			if ($order_data->shipping_company != '') {
				$delivery_address .= $order_data->shipping_company."\n";
			}
			$delivery_address .= $order_data->shipping_firstname.' '.$order_data->shipping_surname."\n";
			$delivery_address .= $order_data->shipping_street.' '.$order_data->shipping_house_number."\n";
			$delivery_address .= $order_data->shipping_zip_code.' '.$order_data->shipping_city."\n\n";
			$delivery_address .= Country::get_name_by_id($order_data->shipping_country);

			$pdf->PrintCustomerAddress($delivery_address);

			$order_date = explode('-', $order_data->order_date);
			$billing_date = explode('-', $order_data->billing_date);

			$order_number = $order_data->order_number;
			if ($order_data->order_revision != '') {
				$order_number .= '-'.$order_data->order_revision;
			}

			$invoice_data = array(
				'Kundennummer' => $order_data->customer_number,
				'Bestellnummer' => $order_number,
				'Bestelldatum' => $order_date[2].'.'.$order_date[1].'.'.$order_date[0],
				'Lieferscheinnummer' => $order_data->shipping_list_number
			);

			$pdf->PrintInvoiceData($invoice_data);

			$pdf->PrintInvoiceOrder($order_data);

			// if payment method has a notice, print it
			$pm = new Payment_method(Registry::get('base'));
			$paymentmethod = $pm->get_by_id($order_data->payment_method);
			if ($paymentmethod->invoice_text != '' && $paymentmethod->invoice_text != NULL) {
				$pdf->PrintNotice('Zahlungshinweis - '.$paymentmethod->name, $paymentmethod->invoice_text);
			}

			// if delivery note has a notice, print it
			$delivery_notice = $order_data->delivery_note_info;
			if ($delivery_notice != '' && $delivery_notice != NULL) {
				$pdf->printNotice('Zusatzinfo', $delivery_notice);
			}

			$filename = 'Lieferschein-'.$order_data->invoice_number.'.pdf';
			 
			$pdf->Output($filename, 'D');

			exit();
		} else {
			echo "ERROR: Keine ID.";
			exit();
		}
	}

	//=========================================
	//
	//	THE PRIVATE INTERFACE
	//
	//=========================================

	private static function __get_order_items_from_db($order_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT
					item_id AS id,
					amount AS quantity,
					price,
					item_status,
					item_status_text,
					delivery_date,
					backorder_quantity,
					variant1,
					variant2
				FROM
					order_item
				WHERE
					order_id=".$db->real_escape_string($order_id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			while ($item = $result->fetch_object()) {
				if ($item->backorder_quantity == '') {
					$item->backorder_quantity = 0;
				}
				$order_items[] = $item;
			}

			return $order_items;
		}

		return false;
	}

	private static function __get_order_items_from_new_order_list($order_items) {
		foreach ($order_items as $item) {
			$return[] = (object) array(
				'id' => $item['itemId'], 
				'quantity' => $item['quantity'],
				'price' => $item['prices']['itemPriceNet'],
				'item_status' => $item['item_status'],
				'item_status_text' => $item['item_status_text'],
				'delivery_date' => $item['delivery_date'],
				'backorder_quantity' => $item['backorder_quantity'],
				'variant1' => $item['variant1'],
				'variant2' => $item['variant2']
			);
		}

		return $return;
	}

	// TODO: unit test for this funciton
	// this is the perfect candidate for test cases
	private static function __compare_orders($order_list_one, $order_list_two) {
		$something_has_changed = false;

		// if the lists have differing length something must have changed
		if (count($order_list_one) != count($order_list_two)) {
			$something_has_changed = true;
		}

		foreach ($order_list_one as $order_one_item) {
			$is_in_new_list = false;

			foreach ($order_list_two as $order_two_item) {
				if ($order_one_item->id == $order_two_item->id) {
					if ($order_one_item->variant1 == $order_two_item->variant1) {
						if ($order_one_item->variant2 == $order_two_item->variant2) {
							$is_in_new_list = true;

							if ($order_one_item->price != $order_two_item->price) {
								$something_has_changed = true;
							}

							// if the quantity of an item has changed so has the list
							if ($order_one_item->quantity != $order_two_item->quantity) {
								$something_has_changed = true;
							}

							if ($order_one_item->item_status != $order_two_item->item_status) {
								$something_has_changed = true;
							} else {
								if ($order_one_item->item_status_text != $order_two_item->item_status_text) {
									$something_has_changed = true;
								}
								if ($order_one_item->delivery_date != $order_two_item->delivery_date) {
									$something_has_changed = true;
								}
								if ($order_one_item->backorder_quantity != $order_two_item->backorder_quantity) {
									$something_has_changed = true;
								}
							}
						}
					}
				}
			}

			// if an item from list one is not in list two something has chagned
			// if an item in list two is not in list one
			// the lists must either be of differing length (one or more items have been added to list two)
			// or if they have the same length one item from list on was replaced with another item and
			// an item from list one is not in list two
			if (!is_in_new_list) {
				$something_has_changed = true;
			}
		}

		return $something_has_changed;
	}

	private static function __get_order_status_count($order_status_id) {
		$db = Registry::get('base')->db;

		$sql = "SELECT COUNT(*) AS num FROM orders
				WHERE order_status=".$db->real_escape_string($order_status_id);

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			return $result->fetch_object()->num;
		}

		return false;
	}

	private static function __get_number_of_orders() {
		$db = Registry::get('base')->db;

		$sql = "SELECT COUNT(*) AS num FROM orders";

		$result = $db->query($sql);

		if ($result->num_rows > 0) {
			return $result->fetch_object()->num;
		}

		return false;
	}

	public static function get_csv_shipping_export($ids) {
		if (!isset($base_object)) {
			$base_object = $db = Registry::get('base');
		}

		$csv_text = '';
		$amount_sum = 0;
		if (sizeof($ids) > 0) {
			$customer_object = new Customer($base_object);
			$country_object = new Country($base_object);
			foreach ($ids As $id) {
				if (intval($id)) {
					// get order data
					$order = new Order(Registry::get('base'));
					$order->id = $id;
					$order_data = $order->get_data();

					// get customer data
					$customer_data = $customer_object->get_data($order_data->customer_id);
					$customer_email = ' ';
					$customer_phone = ' ';
					if ($customer_data) {
						$customer_email = $customer_data->email;
						$customer_phone = $customer_data->phone;
					}

					// get country data
					$country_object->id = $order_data->shipping_country;
					$country_data = $country_object->get_data();
					$country_iso_code = ' ';
					if ($country_data) {
						$country_iso_code = $country_data->ISO3316;
					}

					// shipping list number
					$shipping_list_number = ' ';
					if ($order_data->shipping_list_number) {
						$shipping_list_number = $order_data->shipping_list_number;
					} else {
						$shipping_list_number = $order_data->order_number;
					}

					foreach (array('shipping_company', 'shipping_surname', 'shipping_firstname', 'shipping_street', 'shipping_zip_code', 'shipping_city', 'delivery_note_info') as $key) {
						if (!$order_data->{$key}) {
							$order_data->{$key} = ' ';
						}
					}

					// order data
					$csv_text .= 'A|'.
						utf8_decode($shipping_list_number).'|'.
						utf8_decode($order_data->order_number).'|'.
						utf8_decode($order_data->customer_number).'|'.
						utf8_decode($order_data->shipping_company).'|'.
						utf8_decode($order_data->shipping_surname).'|'.
						utf8_decode($order_data->shipping_firstname).'|'.
						' |'.
						utf8_decode($order_data->shipping_street).' '.$order_data->shipping_house_number.'|'.
						utf8_decode($order_data->shipping_zip_code).'|'.
						utf8_decode($order_data->shipping_city).'|'.
						utf8_decode($country_iso_code).'|'.
						utf8_decode($customer_email).'|'.
						utf8_decode($customer_phone).'|'.
						utf8_decode($order_data->delivery_note_info).
						"\n";

					// order item data
					foreach ($order_data->items as $item_object) {
						$amount_sum += $item_object->amount;
						$csv_text .= 'P|'.
								$shipping_list_number.'|'.
								$item_object->item_number.'|'.
								' '.'|'.
								utf8_decode($item_object->item_name).'|'.
								$item_object->amount.'|'.
								utf8_decode('Stück').'|'.
								' '.
								"\n";
					}
				}
			}
		}

		$export_count = 1;
		if (Config::has_key('shipping_export_counter') && Config::is_set('shipping_export_counter')) {
			$export_count = Config::get_value('shipping_export_counter');
			$export_count++;
			Config::set_key('shipping_export_counter', $export_count);
		}
		$today = getdate();
		$today = $today['mday'].'.'.$today['mon'].'.'.$today['year'];
		$csv_text = "H|VGR-Vertrieb GmbH| |".$today."|".$export_count."|".$amount_sum."\n".$csv_text;

		return $csv_text;
	}

}