<?php
$_SERVER['HTTP_HOST'] = 'www.bebeboutik.com';
include('www/config/config.inc.php');

ini_set('memory_limit', '8G');
set_time_limit(3600);

//$date_to = '"'.date('Y-m-d').' 00:00:00"';
$date_to = '"'.$argv[1].' 00:00:00"';
//~ $date_to = '"2012-08-27 00:00:00"';

$orders = array();
$shipping = array();
$discounts = array();
$slips = array();
$slips_shipping = array();
$slips_discounts = array();


$orders_date = array();
foreach(Db::getInstance()->ExecuteS('
	SELECT DISTINCT `id_order`
	FROM `'._DB_PREFIX_.'order_history`
	WHERE (`id_order_state` = 2
	OR `id_order_state` = 13)
	AND `date_add` >= DATE_FORMAT(DATE_SUB('.$date_to.', INTERVAL 1 DAY), \'%Y-%m-%d 00:00:00\')
	AND `date_add` < '.$date_to.'
	AND `id_order` NOT IN (
		SELECT `id_order`
		FROM `'._DB_PREFIX_.'order_history`
		WHERE  (`id_order_state` = 2
		OR `id_order_state` = 13)
		AND `date_add` < DATE_FORMAT(DATE_SUB('.$date_to.', INTERVAL 1 DAY), \'%Y-%m-%d 00:00:00\')
	)
	ORDER BY `id_order`
') as $o) {
	$orders_date[] = (int) $o['id_order'];
}

if(count($orders_date) > 0) {
	$query = '
		SELECT
			ps.`id_sale`,
			psl.`name` AS sale_title,
			o.id_order AS id_order,
			o.`id_cart` AS `id_cart`,
			c.id_customer AS id_customer,
			c.email AS email,
			c.firstname AS firstname,
			c.lastname AS lastname,
			a.firstname AS shipping_firstname,
			a.lastname AS shipping_lastname,
			a.address1 AS shipping_street,
			a.address2 AS shipping_street2,
			a.postcode AS shipping_postcode,
			a.city AS shipping_city,
			a.phone AS shipping_phone,
			a.phone_mobile AS shipping_phone_mobile,
			REPLACE(REPLACE(REPLACE(a.other, "\n", " "), "\r", " "), "  ", " ") AS other_info,
			l.name AS shipping_country,
			d.product_id AS id_product,
			d.product_attribute_id AS id_product_attribute,
			d.product_name AS order_product_name,
			d.product_quantity AS product_quantity,
			d.product_price AS product_price_base_wo_taxes,
			d.tax_rate AS tax_rate,
			p.name AS product_name,
			IF(d.product_attribute_id=0,"",REPLACE(d.product_name, CONCAT(p.name, " - "), "")) AS product_combination,
			ROUND((d.product_price * (1 - d.reduction_percent / 100) - d.reduction_amount), 6) AS product_price_wo_taxes,
			ROUND((d.product_price * (1 - d.reduction_percent / 100) - d.reduction_amount) * (1 + d.tax_rate / 100), 6) AS product_price,
			pr.wholesale_price AS wholesale_price,
			pa.wholesale_price AS combination_wholesale_price,
			d.product_supplier_reference AS supplier_reference,
			o.total_shipping AS total_shipping,
			CAST(DATE_SUB('.$date_to.', INTERVAL 1 DAY) AS DATE) AS `date`,
			o.invoice_number AS invoice_number,
			o.payment AS payment_type,
			sl.`name` AS `order_state`,
			"" AS `refund_reason`,
			"" AS `refund_employee`,
			IF(ps.`id_employee` = 0, "", CONCAT(e.`firstname`, " ", e.`lastname`)) AS `sale_employee`,
			(SELECT pssl.`value` FROM `'._DB_PREFIX_.'privatesale_shipping_lang` pssl LEFT JOIN `'._DB_PREFIX_.'privatesale_shipping_sale` psss ON pssl.`id_shipping` = psss.`id_shipping` WHERE psss.`id_sale` = ps.`id_sale` AND pssl.`id_lang` = 2 LIMIT 1) AS `sale_shipping`,
			"" AS `spay_transaction`,
			"" AS `paypal_transaction`,
			"" AS `picker`,
			d.product_ean13 AS product_ean13,
			"" AS `vip`
		FROM
			`'._DB_PREFIX_.'orders` o,
			`'._DB_PREFIX_.'customer` c,
			`'._DB_PREFIX_.'address` a,
			`'._DB_PREFIX_.'privatesale_category` psc,
			`'._DB_PREFIX_.'category_lang` psl,
			`'._DB_PREFIX_.'country_lang` l,
			`'._DB_PREFIX_.'order_state_lang` sl,
			`'._DB_PREFIX_.'product` pr,
			`'._DB_PREFIX_.'product_lang` p,
			`'._DB_PREFIX_.'order_detail` d
		LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
		ON pa.id_product_attribute = d.product_attribute_id,
		`'._DB_PREFIX_.'privatesale` ps
		LEFT JOIN `'._DB_PREFIX_.'employee` e
		ON e.`id_employee` = ps.`id_employee`
		WHERE c.id_customer = o.id_customer
		AND l.id_country = a.id_country
		AND l.id_lang = 2
		AND psl.`id_lang` = 2
		AND psc.`id_category` = (
			SELECT `id_category_default`
			FROM `ps_product`
			WHERE `id_product` = d.product_id LIMIT 1
		)
		AND ps.`id_sale` = psc.`id_sale`
		AND psl.`id_category` = ps.`id_category`
		AND o.id_address_delivery = a.id_address
		AND d.id_order = o.id_order
		AND p.id_product = d.product_id
		AND pr.id_product = d.product_id
		AND p.id_lang = 2
		AND sl.`id_lang` = 2
		AND sl.`id_order_state` = (
			SELECT h.`id_order_state` FROM `'._DB_PREFIX_.'order_history` h
			WHERE h.`id_order` = o.`id_order`
			ORDER BY h.`date_add` DESC
			LIMIT 1
		)
		AND o.`id_order` IN ('.implode(', ', $orders_date).')
	';
	$orders = Db::getInstance()->ExecuteS($query);

	$query = '
		SELECT
			0 AS id_sale,
			"" AS sale_title,
			o.id_order AS id_order,
			o.`id_cart` AS `id_cart`,
			c.id_customer AS id_customer,
			c.email AS email,
			c.firstname AS firstname,
			c.lastname AS lastname,
			a.firstname AS shipping_firstname,
			a.lastname AS shipping_lastname,
			a.address1 AS shipping_street,
			a.address2 AS shipping_street2,
			a.postcode AS shipping_postcode,
			a.city AS shipping_city,
			a.phone AS shipping_phone,
			a.phone_mobile AS shipping_phone_mobile,
			REPLACE(REPLACE(REPLACE(a.other, "\n", " "), "\r", " "), "  ", " ") AS other_info,
			l.name AS shipping_country,
			od.id_discount AS id_product,
			0 AS id_product_attribute,
			od.name AS order_product_name,
			1 AS product_quantity,
			0-od.value AS product_price_base_wo_taxes,
			0 AS tax_rate,
			od.name AS product_name,
			"" AS product_combination,
			0-od.value AS product_price_wo_taxes,
			0-od.value AS product_price,
			0 AS wholesale_price,
			0 AS combination_wholesale_price,
			"DISCOUNT" AS supplier_reference,
			0 AS total_shipping,
			o.date_add AS `date`,
			o.invoice_number AS invoice_number,
			o.payment AS payment_type,
			sl.`name` AS `order_state`,
			"" AS `refund_reason`,
			"" AS `refund_employee`,
			"" AS `sale_employee`,
			"" AS `sale_shipping`,
			"" AS `spay_transaction`,
			"" AS `paypal_transaction`,
			"" AS `picker`,
			"" AS product_ean13,
			"" AS `vip`
		FROM
			`'._DB_PREFIX_.'orders` o,
			`'._DB_PREFIX_.'order_discount` od,
			`'._DB_PREFIX_.'customer` c,
			`'._DB_PREFIX_.'address` a,
			`'._DB_PREFIX_.'order_state_lang` sl,
			`'._DB_PREFIX_.'country_lang` l
		WHERE c.id_customer = o.id_customer
		AND o.`id_order` = od.`id_order`
		AND l.id_country = a.id_country
		AND l.id_lang = 2
		AND o.id_address_delivery = a.id_address
		AND sl.`id_lang` = 2
		AND sl.`id_order_state` = (
			SELECT h.`id_order_state` FROM `'._DB_PREFIX_.'order_history` h
			WHERE h.`id_order` = o.`id_order`
			ORDER BY h.`date_add` DESC
			LIMIT 1
		)
		AND o.`id_order` IN ('.implode(', ', $orders_date).')
		GROUP BY c.id_customer, od.id_discount
	';
	$discounts = Db::getInstance()->ExecuteS($query);


	$query = '
		SELECT
			0 AS id_sale,
			"" AS sale_title,
			o.id_order AS id_order,
			o.`id_cart` AS `id_cart`,
			c.id_customer AS id_customer,
			c.email AS email,
			c.firstname AS firstname,
			c.lastname AS lastname,
			a.firstname AS shipping_firstname,
			a.lastname AS shipping_lastname,
			a.address1 AS shipping_street,
			a.address2 AS shipping_street2,
			a.postcode AS shipping_postcode,
			a.city AS shipping_city,
			a.phone AS shipping_phone,
			a.phone_mobile AS shipping_phone_mobile,
			REPLACE(REPLACE(REPLACE(a.other, "\n", " "), "\r", " "), "  ", " ") AS other_info,
			l.name AS shipping_country,
			ca.id_carrier AS id_product,
			0 AS id_product_attribute,
			ca.name AS order_product_name,
			1 AS product_quantity,
			o.total_shipping / (1 + o.carrier_tax_rate / 100) AS product_price_base_wo_taxes,
			o.carrier_tax_rate AS tax_rate,
			ca.name AS product_name,
			"" AS product_combination,
			o.total_shipping / (1 + o.carrier_tax_rate / 100) AS product_price_wo_taxes,
			o.total_shipping AS product_price,
			0 AS wholesale_price,
			0 AS combination_wholesale_price,
			"SHIPPING" AS supplier_reference,
			0 AS total_shipping,
			o.date_add AS `date`,
			o.invoice_number AS invoice_number,
			o.payment AS payment_type,
			sl.`name` AS `order_state`,
			"" AS `refund_reason`,
			"" AS `refund_employee`,
			"" AS `sale_employee`,
			"" AS `sale_shipping`,
			"" AS product_ean13,
			"" AS `vip`
		FROM
			`'._DB_PREFIX_.'orders` o,
			`'._DB_PREFIX_.'carrier` ca,
			`'._DB_PREFIX_.'customer` c,
			`'._DB_PREFIX_.'address` a,
			`'._DB_PREFIX_.'order_state_lang` sl,
			`'._DB_PREFIX_.'country_lang` l
		WHERE c.id_customer = o.id_customer
		AND l.id_country = a.id_country
		AND l.id_lang = 2
		AND o.id_address_delivery = a.id_address
		AND o.id_carrier = ca.id_carrier
		AND sl.`id_lang` = 2
		AND o.id_address_delivery = a.id_address
		AND sl.`id_lang` = 2
		AND sl.`id_order_state` = (
			SELECT h.`id_order_state` FROM `'._DB_PREFIX_.'order_history` h
			WHERE h.`id_order` = o.`id_order`
			ORDER BY h.`date_add` DESC
			LIMIT 1
		)
		AND o.`id_order` IN ('.implode(', ', $orders_date).')
	';
	$shipping = Db::getInstance()->ExecuteS($query);
}

$query = '
	SELECT
		ps.`id_sale`,
		psl.`name` AS sale_title,
		o.id_order AS id_order,
			o.`id_cart` AS `id_cart`,
		c.id_customer AS id_customer,
		c.email AS email,
		c.firstname AS firstname,
		c.lastname AS lastname,
		a.firstname AS shipping_firstname,
		a.lastname AS shipping_lastname,
		a.address1 AS shipping_street,
		a.address2 AS shipping_street2,
		a.postcode AS shipping_postcode,
		a.city AS shipping_city,
		a.phone AS shipping_phone,
		a.phone_mobile AS shipping_phone_mobile,
		REPLACE(REPLACE(REPLACE(a.other, "\n", " "), "\r", " "), "  ", " ") AS other_info,
		l.name AS shipping_country,
		d.product_id AS id_product,
		d.product_attribute_id AS id_product_attribute,
		d.product_name AS order_product_name,
		-osd.product_quantity AS product_quantity,
		d.product_price AS product_price_base_wo_taxes,
		d.tax_rate AS tax_rate,
		p.name AS product_name,
		IF(d.product_attribute_id=0,"",REPLACE(d.product_name, CONCAT(p.name, " - "), "")) AS product_combination,
		ROUND((d.product_price * (1 - d.reduction_percent / 100) - d.reduction_amount), 6) AS product_price_wo_taxes,
		ROUND((d.product_price * (1 - d.reduction_percent / 100) - d.reduction_amount) * (1 + d.tax_rate / 100), 6) AS product_price,
		pr.wholesale_price AS wholesale_price,
		pa.wholesale_price AS combination_wholesale_price,
		d.product_supplier_reference AS supplier_reference,
		o.total_shipping AS total_shipping,
		oss.date_add AS `date`,
		o.invoice_number AS invoice_number,
		o.payment AS payment_type,
		sl.`name` AS `order_state`,
		IFNULL((SELECT `id_reason` FROM `'._DB_PREFIX_.'refundreason` rr WHERE rr.`id_order_slip` = oss.`id_order_slip` LIMIT 1), "---") AS `refund_reason`,
		IFNULL((SELECT CONCAT(`firstname`, " ", `lastname`) FROM `'._DB_PREFIX_.'employee` WHERE `id_employee` = (SELECT rr.`id_employee` FROM `'._DB_PREFIX_.'refundreason` rr WHERE rr.`id_order_slip` = oss.`id_order_slip` LIMIT 1)), "") AS `refund_employee`,
		IF(ps.`id_employee` = 0, "", CONCAT(e.`firstname`, " ", e.`lastname`)) AS `sale_employee`,
		(SELECT pssl.`value` FROM `'._DB_PREFIX_.'privatesale_shipping_lang` pssl LEFT JOIN `'._DB_PREFIX_.'privatesale_shipping_sale` psss ON pssl.`id_shipping` = psss.`id_shipping` WHERE psss.`id_sale` = ps.`id_sale` AND pssl.`id_lang` = 2 LIMIT 1) AS `sale_shipping`,
		"" AS `spay_transaction`,
		"" AS `paypal_transaction`,
		"" AS `picker`,
		d.product_ean13 AS product_ean13,
			"" AS `vip`
	FROM
		`'._DB_PREFIX_.'orders` o,
		`'._DB_PREFIX_.'customer` c,
		`'._DB_PREFIX_.'address` a,
		`'._DB_PREFIX_.'order_state_lang` sl,
		`'._DB_PREFIX_.'country_lang` l,
		`'._DB_PREFIX_.'product` pr,
		`'._DB_PREFIX_.'product_lang` p,
		`'._DB_PREFIX_.'privatesale_category` psc,
		`'._DB_PREFIX_.'category_lang` psl,
		`'._DB_PREFIX_.'order_slip` oss,
		`'._DB_PREFIX_.'order_slip_detail` osd,
		`'._DB_PREFIX_.'order_detail` d
	LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
	ON pa.id_product_attribute = d.product_attribute_id,
	`'._DB_PREFIX_.'privatesale` ps
	LEFT JOIN `'._DB_PREFIX_.'employee` e
	ON e.`id_employee` = ps.`id_employee`
	WHERE c.id_customer = o.id_customer
	AND l.id_country = a.id_country
	AND l.id_lang = 2
	AND ps.`id_sale` = psc.`id_sale`
	AND psl.`id_lang` = 2
		AND psc.`id_category` = (
		SELECT `id_category_default`
		FROM `ps_product`
		WHERE `id_product` = d.product_id LIMIT 1
	)
	AND psl.`id_category` = ps.`id_category`
	AND o.id_address_delivery = a.id_address
	AND d.id_order = o.id_order
	AND p.id_product = d.product_id
	AND sl.`id_lang` = 2
	AND sl.`id_order_state` =(
		SELECT h.`id_order_state`
		FROM `'._DB_PREFIX_.'order_history` h
		WHERE h.`id_order` = o.`id_order`
		ORDER BY h.`date_add` DESC
		LIMIT 1
	)
	AND pr.id_product = d.product_id
	AND oss.`id_order_slip` IN (
		SELECT sd.`id_order_slip`
		FROM `'._DB_PREFIX_.'order_slip` sd
		WHERE sd.date_add >= DATE_FORMAT(DATE_SUB('.$date_to.', INTERVAL 1 DAY), \'%Y-%m-%d 00:00:00\')
		AND sd.date_add <= '.$date_to.'
	)
	AND osd.`id_order_slip` = oss.`id_order_slip`
	AND osd.`id_order_detail` = d.`id_order_detail`
	AND p.id_lang = 2
';
$slips = Db::getInstance()->ExecuteS($query);

$query = '
	SELECT
		0 as `id_sale`,
		"" as `sale_title`,
		o.id_order AS id_order,
		o.`id_cart` AS `id_cart`,
		c.id_customer AS id_customer,
		c.email AS email,
		c.firstname AS firstname,
		c.lastname AS lastname,
		a.firstname AS shipping_firstname,
		a.lastname AS shipping_lastname,
		a.address1 AS shipping_street,
		a.address2 AS shipping_street2,
		a.postcode AS shipping_postcode,
		a.city AS shipping_city,
		a.phone AS shipping_phone,
		a.phone_mobile AS shipping_phone_mobile,
		REPLACE(REPLACE(REPLACE(a.other, "\n", " "), "\r", " "), "  ", " ") AS other_info,
		l.name AS shipping_country,
		ca.id_carrier AS id_product,
		0 AS id_product_attribute,
		ca.name AS order_product_name,
		-1 AS product_quantity,
		o.total_shipping / (1 + o.carrier_tax_rate / 100) AS product_price_base_wo_taxes,
		o.carrier_tax_rate AS tax_rate,
		ca.name AS product_name,
		"" AS product_combination,
		o.total_shipping / (1 + o.carrier_tax_rate / 100) AS product_price_wo_taxes,
		o.total_shipping AS product_price,
		0 AS wholesale_price,
		0 AS combination_wholesale_price,
		"SHIPPING" AS supplier_reference,
		0 AS total_shipping,
		oss.date_add AS `date`,
		o.invoice_number AS invoice_number,
		o.payment AS payment_type,
		sl.`name` AS `order_state`,
                IFNULL((SELECT `id_reason` FROM `'._DB_PREFIX_.'refundreason` rr WHERE rr.`id_order_slip` = oss.`id_order_slip` LIMIT 1), "---") AS `refund_reason`,
                IFNULL((SELECT CONCAT(`firstname`, " ", `lastname`) FROM `'._DB_PREFIX_.'employee` WHERE `id_employee` = (SELECT rr.`id_employee` FROM `'._DB_PREFIX_.'refundreason` rr WHERE rr.`id_order_slip` = oss.`id_order_slip` LIMIT 1)), "") AS `refund_employee`,
		"" AS `sale_employee`,
		"" AS `sale_shipping`,
		"" AS `spay_transaction`,
		"" AS `paypal_transaction`,
		"" AS `picker`,
		"" AS product_ean13,
			"" AS `vip`
	FROM
		`'._DB_PREFIX_.'orders` o,
		`'._DB_PREFIX_.'carrier` ca,
		`'._DB_PREFIX_.'customer` c,
		`'._DB_PREFIX_.'address` a,
		`'._DB_PREFIX_.'order_state_lang` sl,
		`'._DB_PREFIX_.'order_slip` oss,
		`'._DB_PREFIX_.'country_lang` l
	WHERE c.id_customer = o.id_customer
	AND l.id_country = a.id_country
	AND l.id_lang = 2
	AND o.id_address_delivery = a.id_address
	AND o.id_carrier = ca.id_carrier
	AND sl.`id_lang` = 2
	AND sl.`id_order_state` = (
		SELECT h.`id_order_state`
		FROM `'._DB_PREFIX_.'order_history` h
		WHERE h.`id_order` = o.`id_order`
		ORDER BY h.`date_add` DESC
		LIMIT 1
	)
	AND oss.`id_order_slip` IN (
		SELECT sd.`id_order_slip`
		FROM `'._DB_PREFIX_.'order_slip` sd
		WHERE sd.date_add >= DATE_FORMAT(DATE_SUB('.$date_to.', INTERVAL 1 DAY), \'%Y-%m-%d 00:00:00\')
		AND sd.date_add <= '.$date_to.'
		AND `shipping_cost` = 1
	)
	AND oss.`id_order` = o.`id_order`
	GROUP BY o.id_order
';
$slips_shipping = Db::getInstance()->ExecuteS($query);


$canceled_orders = array();
foreach(Db::getInstance()->ExecuteS('
	SELECT `id_order`
	FROM `'._DB_PREFIX_.'order_history`
	WHERE (`id_order_state` = 6
	OR `id_order_state` = 7)
	AND `date_add` >= DATE_FORMAT(DATE_SUB('.$date_to.', INTERVAL 1 DAY), \'%Y-%m-%d 00:00:00\')
	AND `date_add` <= '.$date_to.'
	GROUP BY `id_order`
	ORDER BY `date_add` DESC
') as $o) {
	$canceled_orders[] = (int) $o['id_order'];
}


if(count($canceled_orders) > 0) {
	$query = '
		SELECT
			0 AS id_sale,
			"" AS sale_title,
			o.id_order AS id_order,
			o.`id_cart` AS `id_cart`,
			c.id_customer AS id_customer,
			c.email AS email,
			c.firstname AS firstname,
			c.lastname AS lastname,
			a.firstname AS shipping_firstname,
			a.lastname AS shipping_lastname,
			a.address1 AS shipping_street,
			a.address2 AS shipping_street2,
			a.postcode AS shipping_postcode,
			a.city AS shipping_city,
			a.phone AS shipping_phone,
			a.phone_mobile AS shipping_phone_mobile,
			REPLACE(REPLACE(REPLACE(a.other, "\n", " "), "\r", " "), "  ", " ") AS other_info,
			l.name AS shipping_country,
			od.id_discount AS id_product,
			0 AS id_product_attribute,
			od.name AS order_product_name,
			-1 AS product_quantity,
			od.value AS product_price_base_wo_taxes,
			0 AS tax_rate,
			od.name AS product_name,
			"" AS product_combination,
			od.value AS product_price_wo_taxes,
			od.value AS product_price,
			0 AS wholesale_price,
			0 AS combination_wholesale_price,
			"DISCOUNT" AS supplier_reference,
			0 AS total_shipping,
			(
				SELECT h.`date_add`
				FROM `'._DB_PREFIX_.'order_history` h
				WHERE h.`id_order` = o.`id_order`
				AND (h.`id_order_state` = 6
				OR h.`id_order_state` = 7)
				ORDER BY h.`date_add` DESC
				LIMIT 1
			) AS `date`,
			o.invoice_number AS invoice_number,
			o.payment AS payment_type,
			sl.`name` AS `order_state`,
			"" AS `refund_reason`,
			"" AS `refund_employee`,
			"" AS `sale_employee`,
			"" AS `sale_shipping`,
			"" AS `spay_transaction`,
			"" AS `paypal_transaction`,
			"" AS `picker`,
			"" AS product_ean13,
			"" AS `vip`
		FROM
			`'._DB_PREFIX_.'orders` o,
			`'._DB_PREFIX_.'order_discount` od,
			`'._DB_PREFIX_.'customer` c,
			`'._DB_PREFIX_.'address` a,
			`'._DB_PREFIX_.'order_state_lang` sl,
			`'._DB_PREFIX_.'country_lang` l
		WHERE c.id_customer = o.id_customer
		AND l.id_country = a.id_country
		AND l.id_lang = 2
		AND o.id_address_delivery = a.id_address
		AND od.id_order = o.id_order
		AND sl.`id_lang` = 2
		AND o.valid = 0
		AND o.`id_order` IN ('.implode(', ', $canceled_orders).')
		AND sl.`id_order_state` = (
			SELECT h.`id_order_state`
			FROM `'._DB_PREFIX_.'order_history` h
			WHERE h.`id_order` = o.`id_order`
			ORDER BY h.`date_add` DESC
			LIMIT 1
		)
		GROUP BY c.id_customer, od.id_discount
	';
	$slips_discounts = Db::getInstance()->ExecuteS($query);
}


$partially_canceled_orders = array();
foreach(Db::getInstance()->ExecuteS('
	SELECT `id_order`
	FROM `'._DB_PREFIX_.'order_slip`
	WHERE `date_add` >= DATE_FORMAT(DATE_SUB('.$date_to.', INTERVAL 1 DAY), \'%Y-%m-%d 00:00:00\')
	AND `date_add` <= '.$date_to.'
	GROUP BY `id_order`
	ORDER BY `date_add` ASC
') as $o) {
	if(!in_array((int) $o['id_order'], $canceled_orders)) {
		$o_order = Db::getInstance()->getRow('
			SELECT `total_paid`, `total_shipping`
			FROM `'._DB_PREFIX_.'orders`
			WHERE `id_order` = '.(int) $o['id_order'].'
		');
		$o_discounts = Db::getInstance()->getRow('
			SELECT SUM(`value`) AS `value`
			FROM `'._DB_PREFIX_.'discount`
			WHERE `id_discount` IN (
				SELECT `id_discount`
				FROM `'._DB_PREFIX_.'order_discount`
				WHERE `id_order` = '.(int) $o['id_order'].'
			)
		');

		$o_slip = Db::getInstance()->getRow('
			SELECT `shipping_cost`
			FROM `'._DB_PREFIX_.'order_slip`
			WHERE `id_order` = '.$o['id_order'].'
		');
		$o_products = Db::getInstance()->getRow('
			SELECT SUM(ROUND((d.product_price * (1 - d.reduction_percent / 100) - d.reduction_amount) * (1 + d.tax_rate / 100), 6) * (d.`product_quantity` - d.`product_quantity_return`)) AS product_price
			FROM `'._DB_PREFIX_.'order_detail` d
			WHERE d.`id_order` = '.(int) $o['id_order'].'
			AND (d.`product_quantity` - d.`product_quantity_return`) > 0
		');

		$total = ($o_slip['shipping_cost'] == 1? 0: (float) $o_order['total_shipping']) + (float) $o_products['product_price'];
		$total_discounts = (float) $o_discounts['value'];

		if($total < $total_discounts) {
			$partially_canceled_orders[(int) $o['id_order']] = $total - $total_discounts;
		}
	}
}

// TODO: find a better solution with a single query
foreach($partially_canceled_orders as $porder => $value) {
	$query = '
		SELECT
			0 AS id_sale,
			"" AS sale_title,
			o.id_order AS id_order,
			o.`id_cart` AS `id_cart`,
			c.id_customer AS id_customer,
			c.email AS email,
			c.firstname AS firstname,
			c.lastname AS lastname,
			a.firstname AS shipping_firstname,
			a.lastname AS shipping_lastname,
			a.address1 AS shipping_street,
			a.address2 AS shipping_street2,
			a.postcode AS shipping_postcode,
			a.city AS shipping_city,
			a.phone AS shipping_phone,
			a.phone_mobile AS shipping_phone_mobile,
			REPLACE(REPLACE(REPLACE(a.other, "\n", " "), "\r", " "), "  ", " ") AS other_info,
			l.name AS shipping_country,
			0 AS id_product,
			0 AS id_product_attribute,
			"DISCOUNT (DIFF)" AS order_product_name,
			-1 AS product_quantity,
			'.(float) $value.' AS product_price_base_wo_taxes,
			0 AS tax_rate,
			"DISCOUNT (DIFF)" AS product_name,
			"" AS product_combination,
			'.(float) $value.' AS product_price_wo_taxes,
			'.(float) $value.' AS product_price,
			0 AS wholesale_price,
			0 AS combination_wholesale_price,
			"DISCOUNT (DIFF)" AS supplier_reference,
			0 AS total_shipping,
			(
				SELECT h.`date_add`
				FROM `'._DB_PREFIX_.'order_history` h
				WHERE h.`id_order` = o.`id_order`
				ORDER BY h.`date_add` DESC
				LIMIT 1
			) AS `date`,
			o.invoice_number AS invoice_number,
			o.payment AS payment_type,
			sl.`name` AS `order_state`,
			"" AS `refund_reason`,
			"" AS `refund_employee`,
			"" AS `sale_employee`,
			"" AS `sale_shipping`,
			"" AS `spay_transaction`,
			"" AS `paypal_transaction`,
			"" AS `picker`,
			"" AS product_ean13,
			"" AS `vip`
		FROM
			`'._DB_PREFIX_.'orders` o,
			`'._DB_PREFIX_.'customer` c,
			`'._DB_PREFIX_.'address` a,
			`'._DB_PREFIX_.'order_state_lang` sl,
			`'._DB_PREFIX_.'country_lang` l
		WHERE c.id_customer = o.id_customer
		AND l.id_country = a.id_country
		AND l.id_lang = 2
		AND o.id_address_delivery = a.id_address
		AND sl.`id_lang` = 2
		AND o.`id_order` = '.(int) $porder.'
		AND sl.`id_order_state` = (
			SELECT h.`id_order_state`
			FROM `'._DB_PREFIX_.'order_history` h
			WHERE h.`id_order` = o.`id_order`
			ORDER BY h.`date_add` DESC
			LIMIT 1
		)
	';
	$slips_discounts[] = Db::getInstance()->getRow($query);
}


$spay_transactions = array();
$paypal_transactions = array();
foreach(Db::getInstance()->ExecuteS('
	SELECT `id_order`, `id_transaction`
	FROM `'._DB_PREFIX_.'paypal_order`
') as $row) {
	$paypal_transactions[(int) $row['id_order']] = $row['id_transaction'];
}

$paypal_refunds = array();
foreach(Db::getInstance()->ExecuteS('
	SELECT `id_order`, `message`, `date_add`
	FROM `'._DB_PREFIX_.'message`
	WHERE `message` LIKE "%Remboursement fini avec PayPal%"
') as $row) {
	$m = explode('REFUNDTRANSACTIONID: ', $row['message']);
	$m = explode('<br>', $m[1]);

	$paypal_refunds[$row['id_order']] = array(array($row['date_add'], $m[0]));
}
foreach(Db::getInstance()->ExecuteS('
	SELECT `id_order`, `message`, `date_add`
	FROM `'._DB_PREFIX_.'message`
	WHERE `message` LIKE "A product%"
	AND `message` NOT LIKE "%ACK: Failure%"
') as $row) {
	$m = explode('REFUNDTRANSACTIONID: ', $row['message']);
	if(count($m) > 1) {
		$m = explode('<br>', $m[1]);

		if(!isset($paypal_refunds[$row['id_order']])) {
			$paypal_refunds[$row['id_order']] = array();
		}

		$paypal_refunds[$row['id_order']][] = array($row['date_add'], $m[0]);
	}
}


$shipping_details = array();

function exportCSV($items, $slips=FALSE) {
	global $spay_transactions;
	global $paypal_transactions;
	global $spay_refunds;
	global $paypal_refunds;
	global $shipping_details;
	$float = array('tax_rate', 'product_price_base_wo_taxes', 'product_price_wo_taxes', 'product_price', 'wholesale_price', 'combination_wholesale_price', 'total_shipping');
	if($items && count($items)) {
 		$data = '';
 		foreach($items as $row) {
 			$line = '';
 			foreach($row as $k => $v) {
				if(in_array($k, $float)) {
					$v = str_replace('.', ',', (string) $v);
				}
 				if($k == 'other_info') {
					$line .= ';"'.str_replace(';', ',', str_replace('"', '\"', $v)).'"';
				} else {
					$line .= ';'.str_replace(';', ',', str_replace(array("\r", "\n"), '', str_replace('"', '\"', $v)));
				}
 			}

 			if(strtolower($row['payment_type']) == 'paypal') {
				if($slips) {
					$refunds = isset($paypal_refunds[(int) $row['id_order']])? $paypal_refunds[(int) $row['id_order']]: '';

					if($refunds) {
						$ids = array();
						foreach($refunds as $r) {
							if(abs(strtotime($r[0]) - strtotime($row['date'])) != 60) {
								$ids[] = $r[1];
							}
						}

						$line .= ';;'.implode(',', $ids);
					} else {
						$line .= ';;';
					}
				} else {
					$line .= ';;'.(isset($paypal_transactions[(int) $row['id_order']])? $paypal_transactions[(int) $row['id_order']]: '');
				}
			} else {
				if($slips) {
					$refunds = isset($spay_refunds[(int) $row['id_cart']])? $spay_refunds[(int) $row['id_cart']]: '';

					if($refunds) {
						$ids = array();
						foreach($refunds as $r) {
							if(abs(strtotime($r[0]) - strtotime($row['date'])) != 60) {
								$ids[] = $r[1];
							}
						}

						$line .= ';'.implode(',', $ids).';';
					} else {
						$line .= ';;';
					}
				} else {
					$line .= ';'.(isset($spay_transactions[(int) $row['id_cart']])? $spay_transactions[(int) $row['id_cart']]: '').';';
				}
 			}

 			$line .= ';'.(isset($shipping_details[(int) $row['id_order']]) && isset($shipping_details[(int) $row['id_order']][(int) $row['id_product'].'_'.(int) $row['id_product_attribute']])? $shipping_details[(int) $row['id_order']][(int) $row['id_product'].'_'.(int) $row['id_product_attribute']]: '');

 			$data .= "\n".substr($line, 1);
 		}
		return $data;
	}
}

//$result = 'id_sale;sale_title;id_order;id_cart;id_customer;email;firstname;lastname;shipping_firstname;shipping_lastname;shipping_street;shipping_street2;shipping_postcode;shipping_city;shipping_phone;shipping_phone_mobile;other_info;shipping_country;id_product;id_product_attribute;order_product_name;product_quantity;product_price_base_wo_taxes;tax_rate;product_name;product_combination;product_price_wo_taxes;product_price;wholesale_price;combination_wholesale_price;supplier_reference;total_shipping;date;invoice_number;payment_type;order_state;refund_reason;refund_employee;sale_employee;spay_transaction;paypal_transaction;picker;product_ean13;vip';

$result = '';
$result .= exportCSV($orders);
$result .= exportCSV($shipping);
$result .= exportCSV($discounts);
$result .= exportCSV($slips, TRUE);
$result .= exportCSV($slips_shipping, TRUE);
$result .= exportCSV($slips_discounts, TRUE);


echo $result;

//file_put_contents('extract/daily-daily/'.date('Y-m-d', mktime()).'-dailydaily2_test.csv', $result);
//~ file_put_contents('extract/daily-daily/2012-08-27-dailydaily2.csv', $result);
exit;