805 lines
27 KiB
PHP
805 lines
27 KiB
PHP
<?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;
|