<?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);