<?php
/**
 * Export customer details to Emarsys
 * - Select all customers
 */

$_SERVER['HTTP_HOST'] = 'www.bebeboutik.com';
$_SERVER['SERVER_NAME'] = 'www.bebeboutik.com';
$_SERVER['HTTP_PORT'] = 80;
include('www/config/config.inc.php');

ini_set('memory_limit', '6G');

$customers_to_import = array();

// Customer informations
foreach (Db::getInstance()->ExecuteS('
	SELECT
		c.`id_customer`,
		c.`firstname`,
		c.`lastname`,
		c.`email`,
		c.`ip_registration_newsletter`,
	 	IF(c.`deleted` = 1, 0, IF(c.`active` = 0, 0, c.`newsletter`)) AS `newsletter`,
	 	IFNULL(v.`version`, "fr") AS `version`
	 FROM `ps_customer` c
	LEFT JOIN `ps_customer_version` v ON v.`id_customer` = c.`id_customer`
	ORDER BY `id_customer` ASC
') as $row) {
	$customers_to_import[(int)$row['id_customer']] = array(
        'id_customer' => $row['id_customer'],
		'firstname' => $row['firstname'],
		'lastname' => $row['lastname'],
		'email' => $row['email'],
		'ip_registration_newsletter' => $row['ip_registration_newsletter'],
		'newsletter' => (int)$row['newsletter'],
	 	'version' => $row['version'],
	 	'date_first_order' => "",
	 	'date_last_order' => "",
	 	'discount' => ""
	);
}

$id_customers = array_keys($customers_to_import);
for($i=0, $l=count($id_customers); $i < $l; $i+=5000) {
	// First order
	foreach (Db::getInstance()->ExecuteS('
		SELECT o.`id_customer`, DATE_FORMAT(o.`date_add`,\'%d/%m/%Y\') AS `date_first_order`
		FROM `'._DB_PREFIX_.'orders` o
		LEFT JOIN `'._DB_PREFIX_.'order_history` oh ON o.`id_order` = oh.`id_order`
		WHERE oh.`id_order_history` = (SELECT MAX(`id_order_history`) FROM `'._DB_PREFIX_.'order_history` moh WHERE moh.`id_order` = o.`id_order` GROUP BY moh.`id_order`)
	    AND oh.`id_order_state` NOT IN (1,14,15,18,6,8,10,11)
	    AND o.`id_customer` IN ('.implode(', ', array_slice($id_customers, $i, 5000)).')
		GROUP BY o.`id_customer`
		ORDER BY o.`id_order` DESC
	') as $row) {
		$customers_to_import[(int)$row['id_customer']]['date_first_order'] = $row['date_first_order'];
	}

	// Last order
	foreach (Db::getInstance()->ExecuteS('
		SELECT o.`id_customer`, DATE_FORMAT(MAX(h.`date_add`), \'%d/%m/%Y\') AS `date_last_order`
		FROM `'._DB_PREFIX_.'order_history` h
		LEFT JOIN `'._DB_PREFIX_.'orders` o ON h.`id_order` = o.`id_order`
		WHERE h.`id_order_state` NOT IN (1,14,15,18,6,8,10,11)
		AND o.`id_customer` IN ('.implode(', ', array_slice($id_customers, $i, 5000)).')
		GROUP BY o.`id_customer`
		ORDER BY h.`id_order` DESC
	') as $row) {
		$customers_to_import[(int)$row['id_customer']]['date_last_order'] = $row['date_last_order'];
	}

	// Discount
	foreach (Db::getInstance()->ExecuteS('
		SELECT dh.`id_customer`, dh.`code` as `discount`
	    FROM `'._DB_PREFIX_.'ant_discount_history` dh
	    WHERE dh.`id_customer` IN ('.implode(', ', array_slice($id_customers, $i, 5000)).')
	    AND dh.`used` = 0
	    GROUP BY dh.`id_customer`
	    ORDER BY dh.`date_add` DESC
	') as $row) {
		$customers_to_import[(int)$row['id_customer']]['discount'] = $row['discount'];
	}
}

$filename = date('Y-m-d', mktime()).'.csv';
$f = fopen('extracts/webdav/'.$filename, 'w');
$header = array(
    'id_customer',
    'firstname',
    'lastname',
    'email',
    'ip_registration_newsletter',
    'newsletter',
    'langue',
    'date de premier achat',
    'Date de dernier achat',
    'discount',
);
fputcsv($f, $header, ';', '"');
foreach ($customers_to_import as $customer) {
	fwrite($f, implode(';', array_values($customer))."\n");
}
fclose($f);

// Send to FTP
$ftp = ftp_connect('ftp.emarsys.fr');
ftp_login($ftp, 'emarsys-bbb', 'XuCVuK64');
ftp_put($ftp, $filename, 'extracts/webdav/'.$filename, FTP_BINARY);
ftp_close($ftp);