264 lines
7.5 KiB
PHP
264 lines
7.5 KiB
PHP
<?php
|
|
$_SERVER['HTTP_HOST'] = 'www.bebeboutik.com';
|
|
include('www/config/config.inc.php');
|
|
|
|
ini_set('memory_limit', '4G');
|
|
|
|
|
|
$monthdiff = ((int) date('Y') - 2012 + 1) * 12 - (12 - (int) date('m'));
|
|
|
|
|
|
$file = fopen(dirname(__FILE__).'/extracts/cohort/'.date('Y-m-d').'_1.csv', 'w');
|
|
|
|
$headers = array(
|
|
'date',
|
|
'customers',
|
|
);
|
|
|
|
for($i=0; $i < $monthdiff - 1; $i++) {
|
|
$headers[] = 'month'.($i > 0? ' + '.$i: '');
|
|
}
|
|
|
|
fputcsv($file, $headers, ';', '"');
|
|
|
|
for($y=2012, $ynow=(int) date('Y'); $y <= $ynow; $y++) {
|
|
for($m=1; $m < 13; $m++) {
|
|
if(date('Y-m') == $y.'-'.sprintf('%02d', $m)) {
|
|
break;
|
|
}
|
|
|
|
$customers = array();
|
|
foreach(Db::getInstance()->ExecuteS('
|
|
SELECT `id_customer`
|
|
FROM `'._DB_PREFIX_.'customer`
|
|
WHERE `date_add` BETWEEN "'.$y.'-'.sprintf('%02d', $m).'-01 00:00:00" AND "'.($m + 1 > 12? $y + 1: $y).'-'.($m + 1 > 12? '01': sprintf('%02d', $m + 1)).'-01 00:00:00"
|
|
') as $row) {
|
|
$customers[] = (int) $row['id_customer'];
|
|
}
|
|
|
|
echo $y.'-'.sprintf('%02d', $m);
|
|
|
|
$results = array($y.'-'.sprintf('%02d', $m), count($customers));
|
|
|
|
$date = new DateTime($y.'-'.sprintf('%02d', $m).'-01');
|
|
for($i = 1; $i < $monthdiff; $i++) {
|
|
$d1 = clone $date;
|
|
$d2 = clone $date;
|
|
$d1->add(new DateInterval('P'.($i - 1).'M'));
|
|
$d2->add(new DateInterval('P'.$i.'M'));
|
|
|
|
$total = array();
|
|
|
|
foreach(Db::getInstance()->ExecuteS('
|
|
SELECT DISTINCT `id_customer`
|
|
FROM `'._DB_PREFIX_.'orders`
|
|
WHERE `id_customer` IN ('.implode(', ', $customers).')
|
|
AND (`invoice_date` BETWEEN "'.$d1->format('Y-m-d').' 00:00:00" AND "'.$d2->format('Y-m-d').' 00:00:00")
|
|
') as $row) {
|
|
$total[] = (int) $row['id_customer'];
|
|
}
|
|
|
|
$results[] = count($total);
|
|
|
|
$customers = array_diff($customers, $total);
|
|
|
|
echo ' '.$i;
|
|
}
|
|
|
|
fputcsv($file, $results, ';', '"');
|
|
|
|
echo "\n";
|
|
|
|
$monthdiff--;
|
|
}
|
|
}
|
|
|
|
fclose($file);
|
|
|
|
|
|
/** End of cohort 1 **/
|
|
|
|
$monthdiff = ((int) date('Y') - 2012 + 1) * 12 - (12 - (int) date('m'));
|
|
|
|
|
|
$file2 = fopen(dirname(__FILE__).'/extracts/cohort/'.date('Y-m-d').'_2.csv', 'w');
|
|
$file3 = fopen(dirname(__FILE__).'/extracts/cohort/'.date('Y-m-d').'_3.csv', 'w');
|
|
|
|
$headers = array(
|
|
'date',
|
|
'first_orders',
|
|
);
|
|
|
|
for($i=0; $i < $monthdiff - 1; $i++) {
|
|
$headers[] = 'month'.($i > 0? ' + '.$i: '');
|
|
}
|
|
|
|
fputcsv($file2, $headers, ';', '"');
|
|
fputcsv($file3, $headers, ';', '"');
|
|
|
|
for($y=2012, $ynow=(int) date('Y'); $y <= $ynow; $y++) {
|
|
for($m=1; $m < 13; $m++) {
|
|
if(date('Y-m') == $y.'-'.sprintf('%02d', $m)) {
|
|
break;
|
|
}
|
|
|
|
$first_orders = array();
|
|
|
|
foreach(Db::getInstance()->ExecuteS('
|
|
SELECT DISTINCT o.`id_customer`
|
|
FROM `'._DB_PREFIX_.'orders` o
|
|
INNER JOIN (
|
|
SELECT `id_customer`, MIN(`invoice_date`) AS `mindate`
|
|
FROM `'._DB_PREFIX_.'orders`
|
|
GROUP BY `id_customer`
|
|
) o2
|
|
ON o.`id_customer` = o2.`id_customer`
|
|
WHERE o2.`mindate` BETWEEN "'.$y.'-'.sprintf('%02d', $m).'-01 00:00:00" AND "'.($m + 1 > 12? $y + 1: $y).'-'.($m + 1 > 12? '01': sprintf('%02d', $m + 1)).'-01 00:00:00"
|
|
') as $row) {
|
|
$first_orders[] = (int) $row['id_customer'];
|
|
}
|
|
|
|
echo $y.'-'.sprintf('%02d', $m);
|
|
|
|
$results = array($y.'-'.sprintf('%02d', $m), count($first_orders));
|
|
$results2 = array($y.'-'.sprintf('%02d', $m), count($first_orders));
|
|
|
|
$date = new DateTime($y.'-'.sprintf('%02d', $m).'-01');
|
|
for($i = 1; $i < $monthdiff; $i++) {
|
|
$d1 = clone $date;
|
|
$d2 = clone $date;
|
|
$d1->add(new DateInterval('P'.($i - 1).'M'));
|
|
$d2->add(new DateInterval('P'.$i.'M'));
|
|
|
|
if(count($first_orders) > 0) {
|
|
$total = Db::getInstance()->getValue('
|
|
SELECT COUNT(`id_order`)
|
|
FROM `'._DB_PREFIX_.'orders`
|
|
WHERE `id_customer` IN ('.implode(', ', $first_orders).')
|
|
AND (`invoice_date` BETWEEN "'.$d1->format('Y-m-d').' 00:00:00" AND "'.$d2->format('Y-m-d').' 00:00:00")
|
|
');
|
|
|
|
$results[] = $total;
|
|
|
|
$total2 = array();
|
|
|
|
foreach(Db::getInstance()->ExecuteS('
|
|
SELECT DISTINCT `id_customer`
|
|
FROM `'._DB_PREFIX_.'orders`
|
|
WHERE `id_customer` IN ('.implode(', ', $first_orders).')
|
|
AND (`invoice_date` BETWEEN "'.$d1->format('Y-m-d').' 00:00:00" AND "'.$d2->format('Y-m-d').' 00:00:00")
|
|
') as $row) {
|
|
$total2[] = (int) $row['id_customer'];
|
|
}
|
|
|
|
$results2[] = count($total2);
|
|
} else {
|
|
$results[] = 0;
|
|
$results2[] = 0;
|
|
}
|
|
|
|
echo ' '.$i;
|
|
}
|
|
|
|
fputcsv($file2, $results, ';', '"');
|
|
fputcsv($file3, $results2, ';', '"');
|
|
|
|
echo "\n";
|
|
|
|
$monthdiff--;
|
|
}
|
|
}
|
|
|
|
fclose($file2);
|
|
fclose($file3);
|
|
|
|
|
|
/** End of cohort 1 **/
|
|
|
|
$monthdiff = ((int) date('Y') - 2012 + 1) * 12 - (12 - (int) date('m'));
|
|
|
|
|
|
$file4 = fopen(dirname(__FILE__).'/extracts/cohort/'.date('Y-m-d').'_4.csv', 'w');
|
|
|
|
$headers = array(
|
|
'date',
|
|
'first_orders',
|
|
);
|
|
|
|
for($i=0; $i < $monthdiff - 1; $i++) {
|
|
$headers[] = 'month'.($i > 0? ' + '.$i: '');
|
|
}
|
|
|
|
fputcsv($file4, $headers, ';', '"');
|
|
|
|
$double_orders_customers = array();
|
|
foreach(Db::getInstance()->ExecuteS('
|
|
SELECT `id_customer`
|
|
FROM `'._DB_PREFIX_.'orders`
|
|
GROUP BY `id_customer`
|
|
HAVING COUNT(`id_order`) > 1
|
|
') as $row) {
|
|
$double_orders_customers[] = (int) $row['id_customer'];
|
|
}
|
|
|
|
for($y=2012, $ynow=(int) date('Y'); $y <= $ynow; $y++) {
|
|
for($m=1; $m < 13; $m++) {
|
|
if(date('Y-m') == $y.'-'.sprintf('%02d', $m)) {
|
|
break;
|
|
}
|
|
|
|
$first_orders = array();
|
|
|
|
foreach(Db::getInstance()->ExecuteS('
|
|
SELECT DISTINCT o.`id_customer`
|
|
FROM `'._DB_PREFIX_.'orders` o
|
|
INNER JOIN (
|
|
SELECT `id_customer`, MIN(`invoice_date`) AS `mindate`
|
|
FROM `'._DB_PREFIX_.'orders`
|
|
GROUP BY `id_customer`
|
|
) o2
|
|
ON o.`id_customer` = o2.`id_customer`
|
|
WHERE o2.`mindate` BETWEEN "'.$y.'-'.sprintf('%02d', $m).'-01 00:00:00" AND "'.($m + 1 > 12? $y + 1: $y).'-'.($m + 1 > 12? '01': sprintf('%02d', $m + 1)).'-01 00:00:00"
|
|
') as $row) {
|
|
if(in_array((int) $row['id_customer'], $double_orders_customers)) {
|
|
$first_orders[] = (int) $row['id_customer'];
|
|
}
|
|
}
|
|
|
|
echo $y.'-'.sprintf('%02d', $m);
|
|
|
|
$results = array($y.'-'.sprintf('%02d', $m), count($first_orders));
|
|
|
|
$date = new DateTime($y.'-'.sprintf('%02d', $m).'-01');
|
|
for($i = 1; $i < $monthdiff; $i++) {
|
|
$d1 = clone $date;
|
|
$d2 = clone $date;
|
|
$d1->add(new DateInterval('P'.($i - 1).'M'));
|
|
$d2->add(new DateInterval('P'.$i.'M'));
|
|
|
|
if(count($first_orders) > 0) {
|
|
$total = Db::getInstance()->getValue('
|
|
SELECT COUNT(`id_order`)
|
|
FROM `'._DB_PREFIX_.'orders`
|
|
WHERE `id_customer` IN ('.implode(', ', $first_orders).')
|
|
AND (`invoice_date` BETWEEN "'.$d1->format('Y-m-d').' 00:00:00" AND "'.$d2->format('Y-m-d').' 00:00:00")
|
|
');
|
|
|
|
$results[] = $total;
|
|
} else {
|
|
$results[] = 0;
|
|
}
|
|
|
|
echo ' '.$i;
|
|
}
|
|
|
|
fputcsv($file4, $results, ';', '"');
|
|
|
|
echo "\n";
|
|
|
|
$monthdiff--;
|
|
}
|
|
}
|
|
|
|
fclose($file4);
|