<?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/paymentstatus.class.php';
include_once './core/paymentmethod.class.php';
include_once './core/shippingmethod.class.php';
include_once './core/orderstatus.class.php';
include_once './core/invoicestatus.class.php';
include_once './core/structure.class.php';
include_once './core/item.class.php';
include_once './core/itemhelper.class.php';
include_once './core/orderstatus.class.php';
include_once './core/orderhelper.class.php';
include_once './core/database.class.php';
include_once './core/money.class.php';
include_once './core/customerhelper.class.php';
include_once './core/giftcertificatehelper.class.php';
include_once './core/tax.class.php';
include_once './core/gift_certificate.class.php';
include_once './core/logger.class.php';

class Order extends Main {

	protected $base_object;

	public $list_table_config = array (
		'title' => 'Bestellungen',
		'db_table' => 'orders',
		'list_fields' => array(
			array(
				'db_field' => 'order_number',
				'name' => 'Bestellnummer',
				'sortable' => 1
			),
			array(
				'db_field' => 'order_status',
				'name' => 'Bestellstatus',
				'rewrite_function' => 'order_state_values',
				'sortable' => 1
			),
			array(
				'db_field' => 'customer_number',
				'name' => 'Kundennummer',
				'sortable' => 1
			),
			array(
				'db_field' => 'billing_company',
				'name' => 'Firma',
				'sortable' => 1
			),
			array(
				'db_field' => 'customer_name',
				'name' => 'Kunde',
				'sortable' => 1
			),
			array(
				'db_field' => 'payment_method',
				'name' => 'Zahlungsart',
				'rewrite_function' => 'payment_method_values',
				'sortable' => 1
			),
			array(
				'db_field' => 'payment_status',
				'name' => 'Zahlungsstatus',
				'rewrite_function' => 'payment_state_values',
				'sortable' => 1
			),
			array(
				'db_field' => 'order_date',
				'name' => 'Datum',
				'format' => 'date_format',
				'sortable' => 1
			),
			array(
				'db_field' => 'order_total',
				'name' => 'Betrag',
				'format' => 'netto_price',
				'sortable' => 1
			),
			array(
				'db_field' => 'set_inventory',
				'name' => 'Inventar',
				'format' => 'format_inventar',
				'sortable' => 1
			)
		),
		'list_css_selector' => 'order_status',
		'default_sort_item' => 'order_number',
		'default_sort_direction' => 'down',
		'search_fields' => array('orders.order_number', 'orders.customer_name', 'orders.customer_number'),
		'db_id_field' => 'id',
		'list_actions' => array(
			array (
				'db_field' => 'order_status',
				'name' => 'Neuer Bestellstatus',
				'values' => 'order_state_values'
			),
			array (
				'db_field' => 'payment_status',
				'name' => 'Neuer Zahlungsstatus',
				'values' => 'payment_state_values'
			)
		),
		'list_filter' => array(
			array (
				'db_field' => 'order_status',
				'relational_operator' => '=',
				'name' => 'Bestellstatus',
				'values' => 'filter_order_state_values'
			),
			array (
				'db_field' => 'payment_status',
				'relational_operator' => '=',
				'name' => 'Zahlungsstatus',
				'values' => 'payment_state_values'
			), 
			array (
				'db_field' => 'payment_method',
				'relational_operator' => '=',
				'name' => 'Zahlungsart',
				'values' => 'payment_method_values'
			),
			array (
				'db_field' => 'set_inventory',
				'relational_operator' => '=',
				'name' => 'Inventar',
				'values' => 'inventar_values'
			) 
		),
		'quick_filter' => 0,
		'permanent_filter' => array(
			array(
				'db_field' => 'order_status',
				'relational_operator' => '!=',
				'value' => '10'
			) 
		),
		'edit_link' => 'index.php?admin_modul=admin_order_editor&id=',
		'toolbar' => array(
			'delete' => 0,
			'new' => 'index.php?admin_modul=admin_order_editor',
			'copy' => 0,
			'select_all' => 1,
			'edit' => 0,
			'actions' => 1,
			'filter' => 1,
			'print' => 1,
			'search' => 1,
			'export_to_shipping' => 0
		),
		'new_function' => 'ticket_event',
		'edit_fields' => array (
			array(
				'db_field' => 'order_status',
				'name' => 'Bestellstatus',
				'change_function' => 'ticket_event',
				'type' => 'int'
			),
			array(
				'db_field' => 'payment_status',
				'name' => 'Bezahlstatus',
				'change_function' => 'ticket_event',
				'type' => 'int'
			),
			array(
				'db_field' => 'export_to_shipping',
				'change_function' => 'set_order_shipping_export',
				'name' => 'Export',
				'type' => 'int'
			)
		),
	);


	var $id = false;
	private $article = array();
	private $config;
	protected $item_object;


	public function __construct($base_object) {
		$config = $base_object->config;
		if ($config->shopConfiguration['shipping_export_email']) {
			$this->list_table_config['list_fields'][] = array(
				'db_field' => 'export_to_shipping',
				'name' => 'Export',
				'format' => 'format_export',
				'sortable' => 1
			);
			$this->list_table_config['list_filter'][] = array(
				'db_field' => 'export_to_shipping',
				'name' => 'Export',
				'values' => 'export_filter',
				'relational_operator' => '='
			);
			$this->list_table_config['toolbar']['export_to_shipping'] = 1;
		}

		$this->base_object = $base_object;
		$this->db = $base_object->db;

		$this->item_object = new Item($base_object);
	}

	public function inventar_values() {
		return array(
			'0' => 'offen',
			'1' => 'gebucht'
		);
	}

	public function format_order_state($string) {
		return '';
	}

	public function format_export($string) {
		if ($string == 1) {
			return 'Versandfreigabe';
		} else if ($string == 2) {
			return 'Versendet';
		}
		return '-';
	}

	public function netto_price($string) {
		return '<span class="order_list_item_price">'.number_format($string,2,',','').' &euro;</span>';
	}

	public function date_format($string) {
		return substr($string,8,2).'.'.substr($string,5,2).'.'.substr($string,0,4);
	}

	public function format_inventar($string) {
		if ($string == 0) {
			return '<span class="order_list_item_inventar_0">&nbsp;offen</span>';
		} else if ($string == 1) {
			return '<span class="order_list_item_inventar_1">&nbsp;gebucht</span>';
		}
		return $string;
	}

	public function export_values() {
		return array(
			'1' => 'Versandfreigabe'
		);
	}

	public function export_filter() {
		return array(
			'1' => 'Versandfreigabe',
			'2' => 'Versendet'
		);
	}

	public function payment_method_values() {
		include_once './core/payment_method.class.php';
		$payment_method_object = new Payment_method($this->base_object);
		return $payment_method_object->get_all_names();
	}

	public function payment_state_values() {
		include_once './core/payment_method.class.php';
		$payment_method_object = new Payment_method($this->base_object);
		return $payment_method_object->get_all_states();
	}

	public function order_state_values() {
		return OrderStatus::get_all_names();
	}

	public function filter_order_state_values() {
		$order_states = OrderStatus::get_all_names();
		// get stats
		$sql = "SELECT count(id) AS stat, order_status FROM orders GROUP BY order_status";
		$result = $this->db->query($sql);
		$data = array();
		while ($obj = $result->fetch_object()) {
			$data[$obj->order_status] = $obj->stat;
		}
		$new_order_states = array();
		foreach ($order_states as $key => $value) {
			if (!isset($data[$key])) {
				$data[$key] = 0;
			}
			$new_order_states[$key] = $value.' ('.$data[$key].')';
		}
		return $new_order_states;
	}

	public function set_id($id) {
		$this->id = $id;
	}

	public function has_id() {
		if ($this->id) {
			return true;
		}

		return false;
	}

	public function get_id($id) {
		return $this->id;
	}

	public function set_paypal_token($token) {
		if ($this->id) {
			// save new order
			$sql = "UPDATE orders SET paypal_token = '".$this->db->real_escape_string($token)."' WHERE id = ".$this->id;

			mysql_unbuffered_query($sql);
			return true;
		}

		return false;
	} // end set_paypal_token

	public function get_by_id($id) {
		$sql = "SELECT * FROM orders WHERE id = $id";

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

		if ($result->num_rows > 0) {
			return $result->fetch_object();
		}

		return false;
	} // end get_by_id

	public function get_by_order_number($order_number) {
		$sql = "SELECT * FROM orders WHERE order_number=".$this->db->real_escape_string($order_number);

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

		if ($result->num_rows > 0) {
			return $result->fetch_object();
		}
	} // end get_by_order_number

	public function get_number_of_pages($items, $sort, $sort_direction, $order = false) {
		if ($order) {
			$sql = "SELECT COUNT(id) FROM orders WHERE order_status = $order";
		} else {
			$sql = "SELECT COUNT(id) FROM orders";
		}

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

		$num = $result->fetch_array();
		$pages = (int)ceil($num[0] / $items);

		return $pages;
	} // end get_number_of_pages

	public function get_pagination_array($items, $page, $sort, $sort_direction, $order = false) {
		if ($order) {
			$sql = "SELECT COUNT(id) FROM orders WHERE order_status = $order";
		} else {
			$sql = "SELECT COUNT(id) FROM orders";
		}

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

		$num = $result->fetch_array();
		$pages = (int)ceil($num[0] / $items);

		while ($i < $pages) {
			$data[] = ++$i;
		}

		if ($pages > 10) {
			if ($page <= 5) {
				$data = array_slice($data, 0, 10);
			} else if ($page > ($pages - 4)) {
				$data = array_slice($data, $pages-10, 10);
			} else {
				$data = array_slice($data, $page -5, 10);
			}
		}

		if ($pages == 1) {
			return;
		}

		return $data;
	} // end get_pagination_array

	public function get_all_paginated($items, $page, $order = false, $sort = false, $direction = false) {
		$page = ($page - 1) * $items;

		if ($direction && $direction == 'down') {
			$direction = " DESC ";
		} else {
			$direction = " ASC ";
		}

		if ($order) {
			$sql = "SELECT
						id, 
						order_number, 
						order_revision, 
						order_addendum, 
						customer_number,
						customer_name,
						payment_method,
						payment_status,
						order_date,
						order_total,
						order_status,
						set_inventory,
						gift_certificate_total,
						gift_certificate_id,
						gift_certificate_code
					FROM
						orders
					WHERE
						order_status = $order";
		} else {
			$sql = "SELECT
						id, 
						order_number, 
						order_revision, 
						order_addendum, 
						customer_number,
						customer_name,
						payment_method,
						payment_status,
						order_date,
						order_total,
						order_status,
						set_inventory,
						gift_certificate_total,
						gift_certificate_id,
						gift_certificate_code
					FROM
						orders";
		}

		if ($sort && $direction) {
			$sql .= " ORDER BY
				$sort $direction
				LIMIT $items OFFSET $page";
		} else {
			$sql .= " ORDER BY
				order_number,order_revision DESC
				LIMIT $items OFFSET $page";
		}

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

		while ($row = $result->fetch_object()) {
			$data[] = $row;
		}

		return $data;
	} // end get_all_paginated

	// DEPRCATED
	public function get_items_for_order($order_id) {
		$sql = "SELECT oi.id, oi.amount, i.number, i.name, i.price_1 AS price, oi.item_id, i.tax_id
				FROM order_item oi
				JOIN items i
				WHERE oi.order_id = $order_id
				AND oi.item_id = i.id";

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

		while ($obj = $result->fetch_object()) {
			$data[] = $obj;
		}

		return $data;
	} // end get_items_for_order

	// WHERE IS THIS USED? just get order item and ->order_id
	public function get_order_id_by_order_item_id($id) {
		$sql = "SELECT order_id
				FROM order_item
				WHERE id = $id";

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

		if ($result->num_rows > 0) {
			$obj = $result->fetch_object();

			return $obj->order_id;
		}

		return false;
	} // end get_order_id_by_order_item_id


	// DEPRECATED
	public function get_all_order_items_by_order_id($id) {
		$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 = $id
				AND
					(i.rang = 1
				OR
					i.rang IS NULL)
				GROUP BY
					a.id";

		$result = $this->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_order_id

	public function get_html_order_by_id($id) {
		$order = $this->get_order_by_id($id);
		$header = $order['header'];
		$items = $order['items'];
		$footer = $order['footer'];

		$html = '<table><thead><tr><th class="recipient"><div>';

		$invoice_address = '';

		if ($header->billing_honorific == 0) {
			$invoice_address = "Herr<br />";
			$invoice_address .= $header->billing_firstname.' '.$header->billing_surname."<br />";
		} else if ($order_data->billing_honorific == 1) {
			$invoice_address = "Frau<br />";
			$invoice_address .= $header->billing_firstname.' '.$header->billing_surname."<br />";
		} else {
			$invoice_address = "Firma<br />";
			$invoice_address .= $header->billing_company."<br />";
			$invoice_address .= $header->billing_firstname.' '.$header->billing_surname."<br />";
		}

		$invoice_address .= $header->billing_street.' '.$header->billing_house_number."<br />";
		$invoice_address .= $header->billing_zip_code.' '.$header->billing_city."<br /><br />";
		$invoice_address .= Country::get_name_by_id($header->billing_country);

		$html .= $invoice_address;

		$html .= '</div></th><th></th><th></th><th></th><th></th><th class="company"><div>';

		$address = Config::get_value('bill_header_text_address');
		$address = str_replace("\n", '<br />', $address);

		$html .= $address;
		$html .= "<br /><br />";

		$invoice_data = 'Kundennummer'.$header->number.'<br />';
		$invoice_data .= 'Rechnungsnummer'.$header->order_number.'<br />';
		$invoice_data .= 'Bestelldatum'.$header->order_date.'<br />';
		$invoice_data .= 'Rechnungsdatum'.$header->billing_date.'<br />';

		$html .= $invoice_data;

		$html .= "</div></th></tr></thad><tbody>";

		$subtotal = 0.00;
		$taxes = 0.00;

		$i = 1;

		$html .= '<tr><td>Pos.</td><td>Art-Nr./Beschreibung</td><td style="text-align: right;">Stückpreis</td><td>St.</td><td style="text-align: right;">Gesamtpreis</td><td style="text-align: right;">MwSt.</td></tr>';

		foreach ($items as $item) {
			$taxrate = $item->tax;
			$itemprice = Money::get_price_with_taxes($item->price, $taxrate);
			$itemtotal = ($itemprice * $item->amount);
			$itemtotal_no_tax = ($item->price * $item->amount);

			$html .= "<tr>";
			$html .= "<td>".$i."</td>";
			$html .= "<td>".$item->item_name.' '.$item->item_number."</td>";
			$html .= '<td style="text-align: right;">'.Money::show_money($itemprice)."&euro; </td>";
			$html .= "<td>".$item->amount."</td>";
			$html .= '<td style="text-align: right;">'.Money::show_money($itemtotal)."&euro; </td>";
			$html .= '<td style="text-align: right;">'.$taxrate.'%'."</td>"; // HACK
			$html .= "</tr>";

			// prepare footer info
			$subtotal += $itemtotal;
			$taxes += ($itemtotal_no_tax * ($taxrate / 100));

			// increment pos
			$i++;
		}

		$html .= "</tbody><tfoot>";

		$delivery_cost = 0.00;
		$payment_cost = 0.00;
		$total = $subtotal + $delivery_cost + $payment_cost;

		$html .= '<tr><td></td><td></td><td></td><td></td><td>Summe aller Artikel</td><td>'.Money::show_money($subtotal)."</td></tr>";
		$html .= '<tr><td></td><td></td><td></td><td></td><td>Darin enthaltene MwSt.</td><td>'.Money::show_money(round($taxes, 2, PHP_ROUND_HALF_UP))."</td></tr>";
		$html .= '<tr><td></td><td></td><td></td><td></td><td>Versandkosten</td><td>'.Money::show_money($delivery_cost)."</td></tr>";
		$html .= '<tr><td></td><td></td><td></td><td></td><td>Zahlungsgeb&uuml;hren</td><td>'.Money::show_money($payment_cost)."</td></tr>";
		$html .= '<tr><td></td><td></td><td></td><td></td><td>F&auml;lliger Betrag</td><td>'.Money::show_money($total)."</td></tr>";

		$html .= "</tfoot></table>";

		return $html;
	}

	// TODO: this is crap, building up hirachical objects leads to more
	// problems then just querying the parts seperatly
	public function get_order_by_id($id) {
		$header = $this->get_order_header($id);

		$customer_id = $header->customer_id;
		$group_id = CustomerHelper::get_customer_group_id_by_customer_id($customer_id);
		$customer_group_object = new CustomerGroups();
		$customer_group_data = $customer_group_object->get($group_id);
		$show_tax = $customer_group_data->show_tax;

		$header->customer_group_text = $customer_group_data->name;

		$parents = array();
		$children = array();
		$items = array();

		$sql = "SELECT
					o.*,
					i.schema_id
				FROM
					order_item AS o
				LEFT JOIN
					items AS i
				ON
					o.item_id = i.id
				WHERE
					o.order_id=".$this->db->real_escape_string($id);

		$result = $this->db->query($sql);
		if ($result->num_rows > 0) {
			$i = 1;
			while ($obj = $result->fetch_object()) {
				if ($obj->variant1) {
					$sql = "SELECT
								shippable,
								available_from,
								available_till,
								inventory
							FROM
								item_attribute_variants
							WHERE
								item_id=".$obj->item_id."
							AND
								attribut1='".$obj->variant1."'
							AND
								attribut2='".$obj->variant2."'
							AND
								customergroup_id=".$group_id;
				} else {
					$sql = "SELECT
								shippable,
								available_from,
								available_till,
								inventory
							FROM
								items
							WHERE
								id=".$obj->item_id;
				}

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

				if ($subresult->num_rows > 0) {
					$temp = $subresult->fetch_object();

					$obj->shippable = $temp->shippable;
					$obj->available_from = $temp->available_from;
					$obj->available_till = $temp->available_till;
					$obj->inventory = $temp->inventory;
				}

				$obj->pos = $i;

				// rk ??????? hier stimmt was mit der berechnung nicht!!!!
				$price_with_tax = number_format($obj->price * (((float)(100 + $obj->tax_rate)) / 100.0 ), 4, '.', '');
				//$price_with_tax = $obj->price + $obj->taxes;
				$total = $obj->price * $obj->amount;
				$total_with_tax = $price_with_tax * $obj->amount;

				$obj->price_with_tax = $price_with_tax;
				$obj->total = $total;
				$obj->total_with_tax = $total_with_tax;
				$obj->price = $obj->price;

				$obj->net_amount = $obj->price;
				$obj->gross_amount = $price_with_tax;
				$obj->vat_amount = $price_with_tax - $obj->price;

				$obj->net_total = $obj->net_amount * $obj->amount;
				$obj->gross_total = $obj->gross_amount * $obj->amount;

				if ($show_tax) {
					$obj->shown_amount = $obj->gross_amount;
					$obj->shown_total = $obj->gross_total;
				} else {
					$obj->shown_amount = $obj->net_amount;
					$obj->shown_total = $obj->net_total;
				}

				if ($obj->schema_id) {
					$schemeArray = ItemHelper::get_item_attributes($obj);

					if ($schemeArray != null && sizeof($schemeArray) > 0) {
						foreach ($schemeArray AS $index => $row) {
							if ($obj->variant1 && $row->value && $row->name && $obj->variant1 == $row->value) {
								$obj->vn1 = $row->name;
							}

							if ($obj->variant2 && $row->value && $row->name && $obj->variant2 == $row->value) {
								$obj->vn2 = $row->name;
							}
						}
					}
				}

				$items[] = $obj;

				if ($obj->parent_item_id == 0) {
					$obj->children = array();
					$parents[] = $obj;
				} else {
					$children[] = $obj;
				}

				$i += 1;
			}

			foreach ($children as $child) {
				foreach ($parents as $key => $parent) {
					if ($parent->stamp == $child->stamp) {
						$parents[$key]->children[] = $child;
					}
				}
			}

			$footer = $this->get_order_footer($items, $header);
		}

		return array('header' => $header, 'items' => $parents, 'footer' => $footer);
	} // end get_order_by_id

	// TODO: needs to take customer group into account
	private function get_order_footer($items, $order) {
		$footer = new stdClass();

		$taxes = array();
		$taxes_for_gift = array();

		$item_total = 0;
		$item_total_with_tax = 0;

		foreach ($items as $item) {
			$item_total += $item->total;
			$item_total_with_tax += $item->total_with_tax;

			if (isset($taxes[$item->tax_rate])) {
				$taxes[$item->tax_rate] += $item->total_with_tax - $item->total;

				$prices_gift[$item->tax_rate]['gross'] += $item->total_with_tax;
			} else {
				$taxes[$item->tax_rate] = $item->total_with_tax - $item->total;

				$prices_gift[$item->tax_rate]['gross'] = $item->total_with_tax;
			}
		}

		foreach ($taxes as $key => $value) {
			$taxes[$key] = $value;
		}

		if ($order->gift_certificate_id != '') {
			$footer->gift_certificate_total = $order->gift_certificate_total;
			$footer->gift_certificate_id = $order->gift_certificate_id;
			$footer->gift_certificate_code = $order->gift_certificate_code;

			$gift_certificate = GiftCertificateHelper::get_certificate_by_code($footer->gift_certificate_code);
			$gift_certificate_value = GiftCertificateHelper::get_certificate_value($footer->gift_certificate_code, $item_total_with_tax, $order->customer_group_id);

			$footer->gift_certificate_type = $gift_certificate->account_type;
			$footer->gift_certificate_amount = $gift_certificate->account;
			$footer->gift_certificate_min_order = $gift_certificate->min_order_sum;
		} else {
			$gift_certificate_total = 0;
		}

		$footer->item_total = $item_total;
		$footer->item_total_with_tax = $item_total_with_tax;

		$shipping = $order->shipping_charges;
		$footer->shipping = $shipping;
		$footer->shipping_charges = $shipping;

		$payment_method = $order->payment_method_charges;
		$footer->payment_method = $payment_method;
		$footer->payment_method_charges = $payment_method;

		$order_total = $item_total + $shipping + $payment_method;
		$footer->order_total = $order_total;

		$order_total_with_tax = $item_total_with_tax + $shipping + $payment_method;
		$footer->order_total_with_tax = $order_total_with_tax - $gift_certificate_total;

		$footer->taxes = $taxes;

		return $footer;
	}

	private function get_order_header($id) {
		$sql = "SELECT 
					o.*,
					c.group_id AS customer_group_id
				FROM
					orders o
				RIGHT JOIN
					customers c
				ON
					o.customer_id = c.id
				WHERE
					o.id=".$this->db->real_escape_string($id);

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

		if ($result->num_rows > 0) {
			$return = $result->fetch_object();

			$return->order_status_text = OrderStatus::get_name($return->order_status);
			$return->payment_status_text = PaymentStatus::get_name($return->payment_status);
			$return->payment_method_text = PaymentMethod::get_name($return->payment_method);
			$return->invoice_status_text = InvoiceStatus::get_name($return->invoice_status);

			$return->shipping_method_tesxt = ShippingMethod::get_name($return->shipping_method);

			$return->billing_honorific_text = Honorific::get_honorific($return->billing_honorific);
			$return->shipping_honorific_text = Honorific::get_honorific($return->shipping_honorific);

			$return->billing_country_text = Country::get_name_by_id($return->billing_country);
			$return->shipping_country_text = Country::get_name_by_id($return->shipping_country);

			return $return;
		}

		return false;
	}

	public function get_all_order_items_by_customer_id($id) {
		$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 = $id";

		$result = $this->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_order_id

	public function get_order_by_order_id($id) {
		$sql = "SELECT *
				FROM orders
				WHERE id = $id";

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

		if ($result->num_rows > 0) {
			return $result->fetch_object();
		}

		throw new Exception("Keine Bestelldaten f&uuml;r die ID gefunden.");
	} // end get_order_by_order_id

	public function get_next_order_number() {
		$sql = "SELECT order_number
				FROM orders
				ORDER BY order_number DESC
				LIMIT 1";

		$result = $this->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 function get_next_invoice_number() {
		$sql = "SELECT setting FROM configuration WHERE name='invoice_min'";

		$result = $this->db->query($sql);
		$num = (int)$result->fetch_object()->setting;
		$next = $num + 1;

		$sql = "UPDATE configuration SET setting=$next WHERE name='invoice_min'";
		$this->db->query($sql);


		$sql = "SELECT setting FROM configuration WHERE name='invoice_number_addition'";

		if (isset($this->base_object->config->shopConfiguration['invoice_number_addition'])) {
			$add = (int)$this->base_object->config->shopConfiguration['invoice_number_addition'];
			$num += $add;
		}

		if (isset($this->base_object->config->shopConfiguration['invoice_number_with_date']) && (int)$this->base_object->config->shopConfiguration['invoice_number_with_date'] == 1) {
			$date = getdate();
			$num = $date['year'].'/'.$num;
		}

		return $num;
	} // end get_next_invoice_number

	public function get_next_shipping_list_number() {
		$sql = "SELECT setting FROM configuration WHERE name='current_shipping_list_number'";

		$result = $this->db->query($sql);
		$num = (int)$result->fetch_object()->setting;
		$next = $num + 1;

		$sql = "UPDATE configuration SET setting=$next WHERE name='current_shipping_list_number'";

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

		if (isset($this->base_object->config->shopConfiguration['shipping_list_number_addition'])) {
			$add = (int)$this->base_object->config->shopConfiguration['shipping_list_number_addition'];
			$num += $add;
		}

		if (isset($this->base_object->config->shopConfiguration['shipping_list_number_width_date']) && (int)$this->base_object->config->shopConfiguration['shipping_list_number_width_date'] == 1) {
			$date = getdate();
			$num = $date['year'].'/'.$num;
		}

		return $num;
	} // end get_next_shipping_list_number

	// DEPRECATED set order status to cancelled instad
	public function cancel_order_by_id($id) {
		$sql = "UPDATE orders SET order_status = ".(6)." WHERE id = $id";

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

		// $result is a boolean
		if ($result) {
			return true;
		}

		false;
	}

	// takes data as object
	public function insert_order($data) {
		// take care of foreign key requirements
		if (!isset($data['order_status'])) {
			$data['order_status'] = 1;
		}

		if (!isset($data['invoice_status'])) {
			$data['invoice_status'] = 1;
		}

		if (!isset($data['payment_status'])) {
			$data['payment_status'] = 1;
		}

		$data = Database::clean($data);
		$sql = Database::insert('orders', $data);
		$result = $this->db->query($sql);

		if ($result) {
			return $this->db->insert_id;
		}

		return false;
	} // end insert_order

	// DEPRECATED use orderitem class
	public function insert_order_item($data) {
		$data = Database::clean($data);
		$sql = Database::insert('order_item', $data);

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

		if ($result) {
			return true;
		}

		return false;
	}

	// DEPRECATED there is a seperate class
	public function get_order_status_object() {
		$sql = "SELECT *
				FROM order_status
				WHERE is_showable=1";

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

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return $return;
		}

		throw new Exception("Es wurden noch keine Bestellzust&auml;nde gespeichert.");
	} // end get_order_status_object

	// DEPRECATED just get order
	public function get_order_status($id) {
		$sql = "SELECT order_status
				FROM orders
				WHERE id = $id";

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

		if ($result) {
			$return = $result->fetch_object();

			return $return->order_status;
		}

		return false;
	} // end get_order_status

	// DEPRECATED use payment status object
	public function get_billing_status_object() {
		$sql = "SELECT * FROM payment_status";

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

		if ($result->num_rows > 0) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return $return;
		}

		return false;
	} // end get_billing_status_object

	// DEPRECATED use payment method object
	public function get_payment_method_object() {
		$sql = "SELECT * FROM payment_methods";/*WHERE active = 1";*/
		$result = $this->db->query($sql);

		if ($result->num_rows > 0 ) {
			while ($obj = $result->fetch_object()) {
				$return[] = $obj;
			}

			return $return;
		}

		return false;
	} // end get_payment_method_object

	// DEPRECATED just get order
	public function get_order_info($id) {
		$sql = "SELECT order_number, order_revision FROM orders WHERE id = $id";

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

		if ($result) {
			return $result->fetch_object();
		}

		return false;
	} // end get_order_info

	// DEPRECATED use billing status object
	public function set_billing_status($state, $id) {
		$sql = "UPDATE orders SET payment_status = ".$this->db->real_escape_string($state)." WHERE id = ".$id;

		return $this->db->query($sql);
	} // end set_billing_status

	// deprecated
	public function set_order_status($state, $id) {
		$sql = "UPDATE orders SET order_status = ".$this->db->real_escape_string($state)." WHERE id = ".$id;

		return $this->db->query($sql);
	} // end set_order_status

	// deprecated
	public function set_payment_method($method, $id) {
		$sql = "UPDATE orders SET payment_method = ".$this->db->real_escape_string($method)."WHERE id = ".$id;

		return $this->db->query($sql);
	} // end set_payment_method

	// deprecated just get order
	public function get_order_total_by_id($id) {
		$sql = "SELECT order_total FROM orders WHERE id = $id";
		$result = $this->db->query($sql);

		if ($result) {
			$return = $result->fetch_object();

			return $return->order_total;
		}

		return false;
	} // end get_order_total_by_id

	public function set_order_paypal_token($token, $id) {
		$sql = "UPDATE orders SET paypal_token = '$token' WHERE id = $id";

		return $this->db->query($sql);
	} // end set_order_paypal_token

	public function get_order_by_paypal_token($token) {
		$sql = "SELECT * FROM orders WHERE paypal_token = '$token'";

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

		if ($result) {
			return $result->fetch_object();
		}

		return false;
	}

	public function get_order_id_by_paypal_token($token) {
		$sql = "SELECT id FROM orders WHERE paypal_token = '$token'";

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

		if ($result) {
			$return = $result->fetch_object();

			return $return->id;
		}

		return false;
	}

	public static function set_invoice_date_to_now($id) {
		$db = Registry::get('base')->db;

		$date = getdate();
		$today = $date['year'].'-'.$date['mon'].'-'.$date['mday'];

		$sql = "UPDATE orders SET billing_date='".$today."' WHERE id=".$id;

		return $db->query($sql);
	}

	public function update($data, $invoice_number = false, $shipping_list_number = false, $show_taxes = 1) {
		$old_order_id = $data->old_order_id;
		unset($data->old_order_id);

		$order_has_changed = OrderHelper::order_has_changed($old_order_id, $data->items);

		$mydate = getdate();
		$today = $mydate['year'].'-'.$mydate['mon'].'-'.$mydate['mday'];

		if ($order_has_changed) {
			if (isset($data->order_revision)) {
				$data->order_revision = 1 + (int)$data->order_revision;
			} else {
				$data->order_revision = 1;
			}

			$data->revised_order_id = $old_order_id;

			$new_order_id = $this->create($data, $invoice_number, $shipping_list_number, $show_taxes);

			if ($new_order_id) {
				OrderHelper::set_order_status($old_order_id, 10);

				if (OrderHelper::order_has_addendum($new_order_id)) {
					$addendum_order = OrderHelper::get_addendum_for_order($old_order_id);
					if ($addendum_order) {
						OrderHelper::set_order_status($addendum_order, 10);
					}
					OrderHelper::make_order_addendum($order_id);
				}

				return $new_order_id;
			}

			return false;
		} else {
			// create order object by copying the customer
			$order_object = new stdClass();

			if (isset($data->invoice_number)) {
				$order_object->invoice_number = $data->invoice_number;
			} else {
				if ($invoice_number) {
					$order_object->invoice_number = $this->get_next_invoice_number();
					if (!isset($data->billing_date)) {
						$order_object->billing_date = $today;
					}
				}
			}

			if (isset($data->shipping_list_number)) {
				$order_object->shipping_list_number = $data->shipping_list_number;
			} else {
				if ($shipping_list_number) {
					$order_object->shipping_list_number = $this->get_next_shipping_list_number();
				}
			}

			if (isset($data->is_eu_order)) {
				$order_object->is_eu_order = $data->is_eu_order;
			}

			if (isset($data->customer_vat_reg_num)) {
				$order_object->customer_vat_reg_num = $data->customer_vat_reg_num;
			}

			if (isset($data->invoice_email_sent)) {
				$order_object->invoice_email_sent = $data->invoice_email_sent;
			}

			if (isset($data->invoice_email_date)) {
				$order_object->invoice_email_date = $data->invoice_email_date;
			}

			if (isset($data->invoice_email_recipient)) {
				$order_object->invoice_email_recipient = $data->invoice_email_recipient;
			}

			if (isset($data->inventory_decremented)) {
				$order_object->inventory_decremented = $data->inventory_decremented;
			}

			if (isset($data->inventory_decremented_date)) {
				$order_object->inventory_decremented_date = $data->inventory_decremented_date;
			}

			if (isset($data->inventory_incremented)) {
				$order_object->inventory_incremented = $data->inventory_incremented;
			}

			if (isset($data->inventory_incremented_date)) {
				$order_object->inventory_incremented_date = $data->inventory_incremented_date;
			}

			$order_object->customer_id = $data->customer->customer_id;
			$order_object->customer_name = $data->customer->customer_name;

			$order_object->revisor_id = $data->revisor_id;

			// set ip
			$order_object->ip = $_SERVER['REMOTE_ADDR'];

			$order_object->order_status = $data->order_status;

			$order_object->order_number = $data->order_number;

			if (isset($data->order_revision)) {
				$order_object->order_revision = $data->order_revision;
			}

			if (isset($data->order_addendum)) {
				$order_object->order_addendum = $data->order_addendum;
			}

			if (isset($data->paypal)) {
				$order_object->paypal_token = $data->paypal['paypal_token'];
				$order_object->paypal_email = $data->paypal['paypal_email'];
				$order_object->paypal_payerid = $data->paypal['paypal_payerid'];
				$order_object->paypal_transaction_id = $data->paypal['paypal_transaction_id'];
			}

			// tracking code
			if (isset($data->trackingcode)) {
				$order_object->trackingcode = $data->trackingcode;
			}

			// add the payment information
			if ($data->payment->status) {
				$order_object->payment_status = $data->payment->status;
			} else {
				$order_object->payment_status = 1; // TODO: need a better way to do this
			}
			$order_object->payment_method = $data->payment->methodId;
			if ($data->itemSumGross == '0') {
				$order_object->payment_method_charges = 0.0;
			} else {
				$order_object->payment_method_charges = $data->payment->priceGross;
			}

			// add the shipping method and charges
			$order_object->shipping_method = $data->shipping->methodId;
			if ($data->without_shipping_charges == 0) {
				$order_object->shipping_charges = $data->shipping->priceGross;
			} else {
				$order_object->shipping_charges = 0.0;
			}
			$order_object->without_shipping_charges = $data->without_shipping_charges;

			if (isset($data->klarna_invoice_number)) {
				$order_object->klarna_invoice_number = $data->klarna_invoice_number;
			}

			if (isset($data->paymorrow_request_id)) {
				$order_object->paymorrow_request_id = $data->paymorrow_request_id;
			}

			if (isset($data->paymorrow_transaction_id)) {
				$order_object->paymorrow_transaction_id = $data->paymorrow_transaction_id;
			}

			if (isset($data->paymorrow_response_result_code)) {
				$order_object->paymorrow_response_result_code = $data->paymorrow_response_result_code;
			}

			if (isset($data->paymorrow_response_status_code)) {
				$order_object->paymorrow_response_status_code = $data->paymorrow_response_status_code;
			}

			if (isset($data->paymorrow_response_timestamp)) {
				$order_object->paymorrow_response_timestamp = $data->paymorrow_response_timestamp;
			}

			if (isset($data->paymorrow_bic)) {
				$order_object->paymorrow_bic = $data->paymorrow_bic;
			}


			if (isset($data->paymorrow_iban)) {
				$order_object->paymorrow_iban = $data->paymorrow_iban;
			}

			if (isset($data->paymorrow_national_bank_code)) {
				$order_object->paymorrow_national_bank_code = $data->paymorrow_national_bank_code;
			}

			if (isset($data->paymorrow_national_bank_account_number)) {
				$order_object->paymorrow_national_bank_account_number = $data->paymorrow_national_bank_account_number;
			}

			if (isset($data->paymorrow_payment_reference)) {
				$order_object->paymorrow_payment_reference = $data->paymorrow_payment_reference;
			}

			// dates
			if (isset($data->order_date)) {
				$order_object->order_date = $data->order_date;
			}

			if (isset($data->billing_date)) {
				$order_object->billing_date = $data->billing_date;
			}

			if (isset($data->payment_date)) {
				$order_object->payment_date = $data->payment_date;
			}

			if (isset($data->cancellation_date)) {
				$order_object->cancellation_date = $data->cancellation_date;
			}

			if (isset($data->cancellation_info)) {
				$order_object->cancellation_info = $data->cancellation_info;
			}

			if (isset($data->cancellation_reason_id)) {
				$order_object->cancellation_reason_id = $data->cancellation_reason_id;
			}

			if (isset($data->cancellation_status)) {
				$order_object->cancellation_status = $data->cancellation_status;
			}

			// notices
			if (isset($data->customer_info)) {
				$order_object->customer_info = $data->customer_info;
			}

			if (isset($data->invoice_info)) {
				$order_object->invoice_info = $data->invoice_info;
			}

			if (isset($data->internal_info)) {
				$order_object->internal_info = $data->internal_info;
			}

			if (isset($data->delivery_note_info)) {
				$order_object->delivery_note_info = $data->delivery_note_info;
			}

			if (isset($data->set_inventory)) {
				$order_object->set_inventory = $data->set_inventory;
			}

			// add the billing addres
			foreach ($data->billingAddress as $key => $value) {
				$prop = 'billing_'.$key;
				$order_object->$prop = $value;
			}

			// add the shipping address
			foreach ($data->shippingAddress as $key => $value) {
				$prop = 'shipping_'.$key;
				$order_object->$prop = $value;
			}

			if (isset($data->manual_shipping_charges)) {
				if ($show_taxes == 1) {
					$order_object->shipping_charges = $data->manual_shipping_charges;
				} else {
					$order_object->shipping_charges = $data->manual_shipping_charges * 1.19;
				}
			}

			$order_object->order_total = $data->itemSumGross + $order_object->shipping_charges + $order_object->payment_method_charges;
			$order_object->order_tax = $data->itemSumGross - $data->itemsSumNet;

			$sql = "UPDATE orders SET ";

			$first = true;

			foreach ($order_object as $key => $value) {
				if ($first) {
					$sql .= " ".$key."='".$this->db->real_escape_string($value)."'";
					$first = false;
				} else {
					$sql .= ", ".$key."='".$this->db->real_escape_string($value)."'";
				}
			}

			$sql .= " WHERE id=".$old_order_id;

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

			if ($result) {
				if ($order_object->payment_method == 8 || $order_object->payment_method == 9) {
					if ($order_object->order_status == 4) {
						if ($order_object->klarna_invoice_number != '') {
							if ($order_object->klarna_invoice_activated != 1) {
								$klarna_object = new Shop_klarna($this->base_object);
								$success = $klarna_object->activate($order_object->klarna_invoice_number);

								if ($success) {
									$this->klarna_invoice_activated($old_order_id);
								} else {
									$error = $klarna_object->get_error();
									$this->set_klarna_error($old_order_id, $error);
								}
							}
						}
					} else if ($order_object->order_status == 6) {
						if ($order_object->klarna_invoice_number != '') {
							if ($order_object->klarna_invoice_deleted != 1) {
								$klarna_object = new Shop_klarna($this->base_object);
								$success = $klarna_object->delete_invoice($order_object->klarna_invoice_number);

								if ($success) {
									$this->klarna_invoice_deleted($old_order_id);
								} else {
									$error = $klarna_object->get_error();
									$this->set_klarna_error($old_order_id, $error);
								}
							}
						}
					}
				}

				if (isset($order_object->set_inventory) && $order_object->set_inventory == 1) {
					$this->manage_inventory($old_order_id, 'set');
				} else {
					$this->manage_inventory($old_order_id, 'unset');
				}


				return $old_order_id;
			}

			return false;
		}

		return false;
	}

	public function create($data, $invoice_number = false, $shipping_list_number = false, $show_taxes = false) {
		// create order object by copying the customer
		$mydate = getdate();
		$today = $mydate['year'].'-'.$mydate['mon'].'-'.$mydate['mday'];

		$order_object = $data->customer;

		if (isset($data->revised_order_id)) {
			$order_object->revised_order_id = $data->revised_order_id;
		}

		if (isset($data->invoice_number)) {
			$order_object->invoice_number = $data->invoice_number;
		} else {
			if ($invoice_number) {
				$order_object->invoice_number = $this->get_next_invoice_number();
				if (!isset($data->billing_date)) {
					$order_object->billing_date = $today;
				}
			}
		}

		if (isset($data->shipping_list_number)) {
			$order_object->shipping_list_number = $data->shipping_list_number;
		} else {
			if ($shipping_list_number) {
				$order_object->shipping_list_number = $this->get_next_shipping_list_number();
			}
		}

		if (isset($data->revisor_id)) {
			$order_object->revisor_id = $data->revisor_id;
		} else {
			$order_object->revisor_id = $data->customer->customer_id;
		}

		if (isset($data->paymorrow_request_id)) {
			$order_object->paymorrow_request_id = $data->paymorrow_request_id;
		}

		if (isset($data->paymorrow_transaction_id)) {
			$order_object->paymorrow_transaction_id = $data->paymorrow_transaction_id;
		}

		if (isset($data->paymorrow_response_result_code)) {
			$order_object->paymorrow_response_result_code = $data->paymorrow_response_result_code;
		}

		if (isset($data->paymorrow_response_status_code)) {
			$order_object->paymorrow_response_status_code = $data->paymorrow_response_status_code;
		}

		if (isset($data->paymorrow_response_timestamp)) {
			$order_object->paymorrow_response_timestamp = $data->paymorrow_response_timestamp;
		}

		if (isset($data->paymorrow_bic)) {
			$order_object->paymorrow_bic = $data->paymorrow_bic;
		}

		if (isset($data->paymorrow_iban)) {
			$order_object->paymorrow_iban = $data->paymorrow_iban;
		}

		if (isset($data->paymorrow_national_bank_code)) {
			$order_object->paymorrow_national_bank_code = $data->paymorrow_national_bank_code;
		}

		if (isset($data->paymorrow_national_bank_account_number)) {
			$order_object->paymorrow_national_bank_account_number = $data->paymorrow_national_bank_account_number;
		}

		if (isset($data->paymorrow_payment_reference)) {
			$order_object->paymorrow_payment_reference = $data->paymorrow_payment_reference;
		}

		// set ip
		$order_object->ip = $_SERVER['REMOTE_ADDR'];

		// TODO: numbers should be defined somewhere, I would prefer orderstatus.class.php
		// add the order information
		if (isset($data->order_status)) {
			$order_object->order_status = $data->order_status;
		} else {
			/*if ($data->payment->methodId != 4 && $data->payment->methodId != 10 && $data->payment->methodId != 11) { // if not paypal
				$order_object->order_status = 1; // is Offen
			} else {
				$order_object->order_status = 8; // is limbo
			}*/
			$order_object->order_status = 1; // is Offen
		}

		if (isset($data->is_eu_order)) {
			$order_object->is_eu_order = $data->is_eu_order;
		}

		if (isset($data->klarna_invoice_number)) {
			$order_object->klarna_invoice_number = $data->klarna_invoice_number;
		}

		if (isset($data->customer_vat_reg_num)) {
			$order_object->customer_vat_reg_num = $data->customer_vat_reg_num;
		}

		if (isset($data->invoice_email_sent)) {
			$order_object->invoice_email_sent = $data->invoice_email_sent;
		}

		if (isset($data->invoice_email_date)) {
			$order_object->invoice_email_date = $data->invoice_email_date;
		}

		if (isset($data->invoice_email_recipient)) {
			$order_object->invoice_email_recipient = $data->invoice_email_recipient;
		}

		if (isset($data->inventory_decremented)) {
			$order_object->inventory_decremented = $data->inventory_decremented;
		}

		if (isset($data->inventory_decremented_date)) {
			$order_object->inventory_decremented_date = $data->inventory_decremented_date;
		}

		if (isset($data->inventory_incremented)) {
			$order_object->inventory_incremented = $data->inventory_incremented;
		}

		if (isset($data->inventory_incremented_date)) {
			$order_object->inventory_incremented_date = $data->inventory_incremented_date;
		}

		if (isset($data->set_inventory)) {
			$order_object->set_inventory = $data->set_inventory;
		}

		if (isset($data->export_to_shipping)) {
			$order_object->export_to_shipping = $data->export_to_shipping;
		}

		if (isset($data->paypal)) {
			$order_object->paypal_token = $data->paypal['paypal_token'];
			$order_object->paypal_email = $data->paypal['paypal_email'];
			$order_object->paypal_payerid = $data->paypal['paypal_payerid'];
			$order_object->paypal_transaction_id = $data->paypal['paypal_transaction_id'];
		}

		if (isset($data->order_date)) {
			$order_object->order_date = $data->order_date;
		} else {
			$order_object->order_date = $today;
		}

		if (isset($data->billing_date)) {
			$order_object->billing_date = $data->billing_date;
		}

		if (isset($data->payment_date)) {
			$order_object->payment_date = $data->payment_date;
		}

		if (isset($data->cancellation_date)) {
			$order_object->cancellation_date = $data->cancellation_date;
		}

		if (isset($data->cancellation_info)) {
			$order_object->cancellation_info = $data->cancellation_info;
		}

		if (isset($data->cancellation_reason_id)) {
			$order_object->cancellation_reason_id = $data->cancellation_reason_id;
		}

		if (isset($data->cancellation_status)) {
			$order_object->cancellation_status = $data->cancellation_status;
		}

		// notices
		if (isset($data->customer_info)) {
			$order_object->customer_info = $data->customer_info;
		}

		if (isset($data->invoice_info)) {
			$order_object->invoice_info = $data->invoice_info;
		}

		if (isset($data->internal_info)) {
			$order_object->internal_info = $data->internal_info;
		}

		if (isset($data->delivery_note_info)) {
			$order_object->delivery_note_info = $data->delivery_note_info;
		}

		if (isset($data->order_number)) {
			$order_object->order_number = $data->order_number;
		} else {
			$order_object->order_number = $this->get_next_order_number();
		}

		// no order revison
		if (isset($data->order_revision)) {
			$order_object->order_revision = $data->order_revision;
		}

		// no order addendum
		if (isset($data->order_addendum)) {
			$order_object->order_addendum = $data->order_addendum;
		}

		// add the payment information
		if ($data->payment->status) {
			$order_object->payment_status = $data->payment->status;
		} else {
			$order_object->payment_status = 1; // TODO: need a better way to do this
		}
		$order_object->payment_method = $data->payment->methodId;
		if ($data->itemSumGross == '0') {
			$order_object->payment_method_charges = 0.0;
		} else {
			$order_object->payment_method_charges = $data->payment->priceGross;
		}

		// tracking code
		if (isset($data->trackingcode)) {
			$order_object->trackingcode = $data->trackingcode;
		}

		if (isset($data->customer_info)) {
			$order_object->customer_info = $data->customer_info;
		}

		$order_object->shipping_method = $data->shipping->methodId;

		if (isset($data->without_shipping_charges)) {
			$order_object->without_shipping_charges = $data->without_shipping_charges;
		} else {
			$order_object->without_shipping_charges = 0;
		}

		$order_object->shipping_charges = $data->shipping->priceGross;

		if ($data->itemSumGross == '0') {
			$order_object->shipping_charges = 0.0;
		} else {
			if ($data->without_shipping_charges == 0) {

				if (isset($data->manual_shipping_charges)) {
					if ($show_taxes == 1) {
						$order_object->shipping_charges = $data->manual_shipping_charges;
					} else {
						$order_object->shipping_charges = $data->manual_shipping_charges * 1.19;
					}
				}
			} else {
				$order_object->shipping_charges = 0.0;
			}
		}

		if (isset($data->giftCertificateCode)) {
			//itemSumGross
			$order_object->gift_certificate_code = $data->giftCertificateCode;
			$order_object->gift_certificate_id = GiftCertificateHelper::get_certificate_id_by_code($data->giftCertificateCode);

			$gift_certificate_value = GiftCertificateHelper::get_certificate_value($data->giftCertificateCode, $data->itemSumGross);

			$order_object->gift_certificate_total = $gift_certificate_value->with_vat;
			$order_object->order_total = $data->itemSumGross + $order_object->shipping_charges + $order_object->payment_method_charges - $order_object->gift_certificate_total;
		} else {
			$order_object->order_total = $data->itemSumGross + $order_object->shipping_charges + $order_object->payment_method_charges;
		}
		$order_object->order_tax = $data->itemSumGross - $data->itemSumNet  + $data->payment->priceVat + $data->shipping->priceVat;

		// add the billing addres
		foreach ($data->billingAddress as $key => $value) {
			$prop = 'billing_'.$key;
			$order_object->$prop = $value;
		}

		// add the shipping address
		foreach ($data->shippingAddress as $key => $value) {
			$prop = 'shipping_'.$key;
			$order_object->$prop = $value;
		}

		$sql = Database::insert('orders', $order_object);

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

		if ($result) {
			$order_id = $this->db->insert_id;

			if ($order_object->payment_method == 8 || $order_object->payment_method == 9) {
				if ($order_object->order_status == 4) {
					if ($order_object->klarna_invoice_number != '') {
						if ($order_object->klarna_invoice_activated != 1) {
							$klarna_object = new Shop_klarna($this->base_object);
							$success = $klarna_object->activate($order_object->klarna_invoice_number);

							if ($success) {
								$this->klarna_invoice_activated($order_id);
							} else {
								$error = $klarna_object->get_error();
								$this->set_klarna_error($order_id, $error);
							}
						}
					}
				} else if ($order_object->order_status == 6) {
					if ($order_object->klarna_invoice_number != '') {
						if ($order_object->klarna_invoice_deleted != 1) {
							$klarna_object = new Shop_klarna($this->base_object);
							$success = $klarna_object->delete_invoice($order_object->klarna_invoice_number);

							if ($success) {
								$this->klarna_invoice_deleted($order_id);
							} else {
								$error = $klarna_object->get_error();
								$this->set_klarna_error($order_id, $error);
							}
						}
					}
				}
			}

			$items_saved = true;

			foreach ($data->items as $item) {
				//order_id
				$order_item['order_id'] = (int)$order_id;
				//item_id
				$order_item['item_id'] = $item['itemId'];
				//parent_id
				$order_item['parent_item_id'] = $item['parentItemId'];
				//amount
				$order_item['amount'] = $item['quantity'];
				//price
				$order_item['price'] = $item['prices']['itemPriceNet'];
				//taxes
				$order_item['taxes'] = $item['vatValue'];
				//tax_id
				$order_item['tax_id'] = $item['vatId'];
				//tax_rate
				$order_item['tax_rate'] = $item['vatRate'];
				//item_name
				$order_item['item_name'] = $item['name'];
				//item_number
				$order_item['item_number'] = $item['mainItemNumber'];
				//variant1
				$order_item['variant1'] = $item['variant1'];
				//variant2
				$order_item['variant2'] = $item['variant2'];
				//item_status
				$order_item['item_status'] = $item['item_status'];
				//item_status_text
				$order_item['item_status_text'] = $item['item_status_text'];
				//delivery_date
				$order_item['delivery_date'] = $item['delivery_date'];
				//backorder_quantity
				$order_item['backorder_quantity'] = $item['backorder_quantity'];
				//stamp
				$order_item['stamp'] = $item['stamp'];
				//user_data
				$order_item['user_data'] = $item['userData'];

				$item_saved = $this->insert_order_item($order_item);
			}

			$hasAddendum = OrderHelper::order_has_addendum($order_id);
			if ($hasAddendum) {
				OrderHelper::make_order_addendum($order_id);
			}

			//$this->manage_inventory($order_id);
			if (isset($order_object->set_inventory) && $order_object->set_inventory == 1) {
				$this->manage_inventory($order_id, 'set');
			} else {
				$this->manage_inventory($order_id, 'unset');
			}

			return $order_id;
		}

		return false;
	}

	public function get_data() {
		if ($this->id) {
			$sql = "SELECT * FROM orders WHERE id=".$this->db->real_escape_string($this->id);

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

			if ($result->num_rows > 0) {
				$order_data = $result->fetch_object();

				if ($order_data->billing_honorific == '0') {
					$order_data->billing_honorific_text = 'Herr';
				} else if ($order_data->billing_honorific == '1') {
					$order_data->billing_honorific_text = 'Frau';
				} else if ($order_data->billing_honorific == '2') {
					$order_data->billing_honorific_text = 'Firma';
				}

				if ($order_data->shipping_honorific == '0') {
					$order_data->shipping_honorific_text = 'Herr';
				} else if ($order_data->shipping_honorific == '1') {
					$order_data->shipping_honorific_text = 'Frau';
				} else {
					$order_data->shipping_honorific_text = 'Firma';
				}

				$sql = "SELECT
							oi.*,
							i.schema_id,
							i.attribute_1,
							i.attribute_2,
							i.attribute_3,
							i.attribute_4,
							i.attribute_5,
							i.attribute_6,
							i.attribute_7,
							i.attribute_8,
							i.attribute_9,
							i.attribute_10,
							i.attribute_11,
							i.attribute_12,
							i.attribute_13,
							i.attribute_14,
							i.attribute_15,
							i.attribute_16,
							i.attribute_17,
							i.attribute_18,
							i.attribute_19,
							i.attribute_20
						FROM order_item oi
						JOIN items i
						ON i.id = oi.item_id
						WHERE order_id=".$this->db->real_escape_string($this->id);

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

				if ($result->num_rows > 0) {
					$items = array();
					while ($obj = $result->fetch_object()) {
						$obj->attribute_array = ItemHelper::get_item_attributes($obj);

						$items[] = $obj;
					}

					$order_data->items = $items;

					return $order_data;
				}
			}
		}

		return false;
	} // end get_data

	public function set_payment_date($date, $id = false) {
		$sql = "UPDATE orders SET payment_date='".$this->db->real_escape_string($date)."'";

		if ($id) {
			$sql .= " WHERE id=".$this->db->real_escape_string($id);
			$this->id = $id;
		} else if ($this->id) {
			$sql .= " WHERE id=".$this->db->real_escape_string($this->id);
		} else {
			return false;
		}

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

	public function manage_inventory($order_id, $action) {
		$sql = "SELECT * FROM orders WHERE id=".$this->db->real_escape_string($order_id);
		$result = $this->db->query($sql);
		if ($result->num_rows > 0) {
			$order = $result->fetch_object();

				$sql = "SELECT * FROM order_item WHERE order_id=".$this->db->real_escape_string($order_id);

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

				if ($result->num_rows > 0) {

					$org_order_id = $order->id;
					if ($order->revised_order_id) {
						$sql = "SELECT id FROM orders WHERE revised_order_id=0 AND order_number=".$this->db->real_escape_string($order->order_number);
						$result2 = $this->db->query($sql);
						$org_order_id = $result2->fetch_object()->id;
					}

					while ($item = $result->fetch_object()) {
						// set inventory for this item
						$item_id = $item->item_id;
						$data = array();
						$data['item_id'] = $item_id;
						$data['inventory_object_type_id'] = 5;
						$data['inventory_object_id'] = $org_order_id;
						$data['amount'] = $item->amount;
						$data['price_sum'] = ($item->price + $item->taxes) * $item->amount;
						$data['update'] = 1;
						$data['create_time'] = $order->order_date.' 00:00:00';
						include_once './core/item_inventory.class.php';
						$inventory_object = new ItemInventory();
						if ($action == 'set') {
							$inventory_object->add($data);
						} else if ($action == 'unset') {
							$inventory_object->remove($data);
						}
					}

					$date = getdate();
					$today = $date['year'].'-'.$date['mon'].'-'.$date['mday'];

					$sql = "UPDATE orders SET inventory_decremented=1, inventory_decremented_date='".$today."' WHERE id=".$this->db->real_escape_string($order_id);

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

		}

		/*$sql = "SELECT * FROM orders WHERE id=".$this->db->real_escape_string($order_id);
		$result = $this->db->query($sql);
		if ($result->num_rows > 0) {
			$order = $result->fetch_object();

			if ($order->order_status == '4' && $order->inventory_decremented != 1) {
				$sql = "SELECT * FROM order_item WHERE order_id=".$this->db->real_escape_string($order_id);

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

				if ($result->num_rows > 0) {
					while ($item = $result->fetch_object()) {
						//$this->item_object->reduce_inventory(($item->amount - $item->backorder_quantity), $item->variant1, $item->variant2,  $item->item_id);
						$this->item_object->reduce_inventory($item->amount, $item->variant1, $item->variant2,  $item->item_id);
					}

					$date = getdate();
					$today = $date['year'].'-'.$date['mon'].'-'.$date['mday'];

					$sql = "UPDATE orders SET inventory_decremented=1, inventory_decremented_date='".$today."' WHERE id=".$this->db->real_escape_string($order_id);

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

			} else if ($order->order_status == '6' && $order->inventory_decremented == 1 && $order->inventory_incremented != 1) {
				$sql = "SELECT * FROM order_item WHERE order_id=".$this->db->real_escape_string($order_id);

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

				if ($result->num_rows > 0) {
					while ($item = $result->fetch_object()) {
						//$this->item_object->increase_inventory(($item->amount - $item->backorder_quantity), $item->variant1, $item->variant2,  $item->item_id);
						$this->item_object->increase_inventory($item->amount, $item->variant1, $item->variant2,  $item->item_id);
					}

					$date = getdate();
					$today = $date['year'].'-'.$date['mon'].'-'.$date['mday'];

					$sql = "UPDATE orders SET inventory_incremented=1, inventory_incremented_date='".$today."' WHERE id=".$this->db->real_escape_string($order_id);

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

		}
		*/

		return;
	} // end manage_inventory

	public function invoice_email_sent($order_id, $email) {
		$date = getdate();
		$today = $date['year'].'-'.$date['mon'].'-'.$date['mday'];

		$sql = "UPDATE orders SET invoice_email_sent=1, invoice_email_date='".$today."', invoice_email_recipient= '".$this->db->real_escape_string($email)."' WHERE id=".$this->db->real_escape_string($order_id);

		$this->db->query($sql);
	} // end invoice_email_sent

	public function klarna_invoice_activated($order_id) {
		$date = getdate();
		$today = $date['year'].'-'.$date['mon'].'-'.$date['mday'];

		$sql = "'UPDATE orders SET klarna_invoice_activated=1, klarna_invoice_activateion_date='".$this->db->real_escape_string($today)."'";

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

	public function klarna_invoice_deleted($order_id) {
		$date = getdate();
		$today = $date['year'].'-'.$date['mon'].'-'.$date['mday'];

		$sql = "UPDATE orders SET klarna_invoice_deleted=1, klarna_invoice_deletion_date='".$this->db->real_escape_string($today)."'";

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

	public function set_klarna_error($order_id, $error) {
		$sql = 'UPDATE orders SET klarna_error='.$this->db->real_escape_string($error);

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

	public function update_for_paymorrow($order_id, $data) {
		$sql = 'UPDATE orders SET ';

		$first = true;
		foreach ($data as $key => $value) {
			if ($first) {
				$first = false;
			} else {
				$sql .= ', ';
			}

			$sql .= "$key='$value'";

		}

		$sql .= ' WHERE id = '.$this->db->real_escape_string($order_id);

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


	public function export_to_shipping($object_ids, $setting) {

			$sql = "UPDATE orders SET export_to_shipping = ".$this->db->real_escape_string($setting).
				" WHERE id IN (".$this->db->real_escape_string(implode(',',$object_ids)).")";

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

			if (1) {
				// export in csv
				$csv_text = OrderHelper::get_csv_shipping_export($object_ids);

				$attachment1 = new stdClass();
				$today = getdate();
				$today = $today['year'].$today['mon'].$today['mday'].$today['hours'].$today['minutes'].$today['seconds'];
				$attachment1->file_name = 'order_export_'.$today.'.csv';
				$attachment1->mime_type = 'text/csv';
				$attachment1->content = $csv_text;
				$attachments = array($attachment1);

				//  send message
				include_once './core/mail.class.php';
				$mailer = new mail_tools($this->base_object);
				if (Config::has_key('shipping_export_email') && Config::is_set('shipping_export_email')) {
					$export_email = Config::get_value('shipping_export_email');
					$mresult = $mailer->send_mail('Logistikexport', 'Exportdatei als Anhang', $export_email, 'extern', false, false, false, false, $attachments);
				}

				// success
				return 1;
			} else {
				// cant save
				return 0;
			}

	}

	public function ticket_event($event_data) {
		$order_data = $this->get_by_id($event_data['object_id']);
		include_once './core/cs_ticket.class.php';
		$ticket_object = new Cs_ticket($this->base_object);
		$ticket_object->event(4, $order_data->customer_id, $event_data['object_id']);
		$event_type = 0;
		if ($order_data->order_status == 3) {
			$event_type = 5;
		} else if ($order_data->order_status == 4) {
			$event_type = 6;
		} else if ($order_data->order_status == 5) {
			$event_type = 7;
		} else if ($order_data->order_status == 6) {
			$event_type = 8;
		} else if ($order_data->order_status == 2) {
			$event_type = 14;
		}
		if ($event_type) {
			$ticket_object->event($event_type, $order_data->customer_id, $event_data['object_id']);
		}
		return 1;
	}
}