<?php
$all_table = 'all_statistics';
$all_by_sub_id_table = 'all_by_sub_id_statistics';
$db = 'torrents';
$pass = '';
$host = 'localhost';
$user = 'root';

function update_statistics($date) {
	global $all_by_sub_id_table;
	$query =
		"SELECT DATE(p.`payment_date`) AS the_date, ".
		"u.`sub_id`, ".
		"SUM(cost) AS cost, ".
		"SUM(u.`subscribed`) AS registered_cnt, ".
		"SUM(CASE WHEN (DATE (u.`subscribed_at`) = DATE (payment_date)) THEN 1 ELSE 0 END) AS subscriptions_cnt, ".
		"COUNT(u.`unsubscribed_at`) AS unsubscriptions_cnt, ".
		"SUM(`pay_status`) AS rebills ".
		"FROM ".
			"payments p JOIN users u ON p.`user_id` = u.user_id ".
			"WHERE DATE(payment_date) = '$date' ".
		"GROUP BY u.`sub_id` ";


	$result = mysql_query($query);

	if (!$result) return;

	while ($row = mysql_fetch_assoc($result)) {
		$s = "SELECT * FROM `$all_by_sub_id_table` WHERE the_date = '${row['the_date']}' AND sub_id = '{$row['sub_id']}'";
		$res = mysql_query($s);
		if (mysql_numrows($res) == 0) {
			//если нету добавить
			$sql = 	"INSERT INTO `$all_by_sub_id_table` (the_date, sub_id, cost, registered_cnt, subscriptions_cnt, unsubscriptions_cnt, rebills) ".
				"VALUES ('{$row['the_date']}','{$row['sub_id']}','{$row['cost']}',{$row['registered_cnt']},{$row['subscriptions_cnt']},{$row['unsubscriptions_cnt']},{$row['rebills']})";
			mysql_query($sql);
		} else {
			// иначе обновить
			$sql = "UPDATE `$all_by_sub_id_table` SET the_date = '{$row['the_date']}', sub_id = '{$row['sub_id']}', cost = '{$row['cost']}', registered_cnt = {$row['registered_cnt']}, subscriptions_cnt = {$row['subscriptions_cnt']}, unsubscriptions_cnt = {$row['unsubscriptions_cnt']},  rebills = {$row['rebills']} WHERE  (the_date = '${row['the_date']}') AND (sub_id = '{$row['sub_id']}')";
			$r = mysql_query($sql) or die(mysql_error());
		}
	}

	update_day_total_statistics($date);
}


function update_day_total_statistics($date = null) {
	global $all_table;

	if ($date == null) $date = date('Y-m-d');

	$day_statistics_query = "SELECT the_date, SUM(registered_cnt) as registered_cnt, SUM(subscriptions_cnt) as subscriptions_cnt, SUM(unsubscriptions_cnt) as unsubscriptions_cnt, SUM(rebills) as rebills, SUM(cost) as cost FROM  all_by_sub_id WHERE the_date = '$date'";

	$result = mysql_query($day_statistics_query);

	$day_statistics = mysql_fetch_assoc($result);

	if ($day_statistics['the_date'] == null) return;

	$s = "SELECT * FROM `$all_table` WHERE the_date = '$date'";

	$result = mysql_query($s);
//  Если нету в статистике, то добавить
	if (mysql_numrows($result) == 0)  {

		$query = "INSERT INTO `$all_table` ".
		"(the_date, cost, registered_cnt, subscriptions_cnt, unsubscriptions_cnt, rebills) ".
		"VALUES ('{$day_statistics['the_date']}','{$day_statistics['cost']}', {$day_statistics['registered_cnt']}, {$day_statistics['subscriptions_cnt']} , {$day_statistics['unsubscriptions_cnt']}, {$day_statistics['rebills']})";
		$result = mysql_query($query);
	} else {
//иначе обновить
		mysql_query("UPDATE `$all_table` SET cost = '{$day_statistics['cost']}', registered_cnt = {$day_statistics['registered_cnt']}, subscriptions_cnt = {$day_statistics['subscriptions_cnt']}, unsubscriptions_cnt = {$day_statistics['unsubscriptions_cnt']}, rebills = {$day_statistics['rebills']} WHERE the_date = {$day_statistics['the_date']}");
	}
}

function fill_statistics_tables() {
	$sql = "SELECT DISTINCT DATE(first_visit_at) FROM users";
	$result = mysql_query($sql) or die (mysql_error());
	while ($row = mysql_fetch_array($result)) {
		update_statistics($row[0]);
		update_day_total_statistics($row[0]);
	}
}
mysql_connect($host,$user,$pass);
@mysql_select_db($db) or die( "Unable to select database");
//update_statistics();
fill_statistics_tables();