<?php

/*
 *  W-AGORA 4.2
 *  -----------
 *  $Id: dbstats.php,v 1.3 2006/01/10 11:19:48 mdruilhe Exp $
 *  Usage:      include file - Log management & Statistics functions
 *  Authors:    Marc Druilhe <mdruilhe@w-agora.com>
 *              Claudie <claudie.costes@wanadoo.fr>
 */

if (defined("_DBSTATS")) return;
define('_DBSTATS', 1);

/**
 * log management & statistics functions
 *
 * @author  Marc Druilhe <mdruilhe@w-agora.net>
 * @version $Id: dbstats.php,v 1.3 2006/01/10 11:19:48 mdruilhe Exp $
 * @package	statistics
 */
class DBStats {

var $site;

var $log_table;
var $log_table_t1;
var $log_table_t2;
var $log_table_t3;
var $log_table_t4;
var $log_tables = array();

var $dailyforumstat_table;
var $dailyuserstat_table;

/**
 * Constructor
 *
 * @access  public
 * @param   object	$db	The database object
 * @param   string	$site	the site
 * @since	4.2
 * @return	void
 */
function DBStats(& $db, $site) {
	$this->db =& $db;
	$this->site =& $site;

	$this->log_table = $site.'_log';
	$this->log_table_t1 = $site.'_log_t1';
	$this->log_table_t2 = $site.'_log_t2';
	$this->log_table_t3 = $site.'_log_t3';
	$this->log_table_t4 = $site.'_log_t4';
	$this->log_tables = array($this->log_table_t1, $this->log_table_t2, $this->log_table_t3, $this->log_table_t4);

	$this->dailyforumstat_table = $this->site.'_dailyforumstats';
	$this->dailyuserstat_table = $this->site.'_dailyuserstats';
}

/**
 * Purge the main log table and Update the statistics table
 *
 * This function first purge the main log table by splitting its entries into 
 * the four log tables (one table per quarter) then compute the daily statistics
 * storing results into the daily forum stats and daily userstats tables
 * Should be called before extracting any statistics
 * @access  public
 * @see		splitLog
 * @see		computeDailyForumStats
 * @see		computeDailyUserStats
 * @return	void
 */
function updateStats() {
	$this->splitLog();
	$this->computeDailyForumStats();
	$this->computeDailyUserStats();
}

/**
 * split main log table into circular log tables.
 *
 * move main log entries to four archive tables (one per quarter)
 * @since   4.2
 * @access  public
 * @return  boolean	true if OK
 */
function splitLog() {
	@set_time_limit(0);
	@ignore_user_abort(true);
	$this->db->query ("SELECT MAX(datestamp) AS last_log_entry FROM $this->log_table");
	if (! $this->db->next_record() ) {
		return false;
	}
	$last_log_entry = $this->db->f("last_log_entry");
	if (empty($last_log_entry)) {
		return true;
	}

	$this->db->query ("SELECT MAX(datestamp) AS last_update FROM $this->log_table_t1");
	if ($this->db->next_record() ) {
		$last_update = $this->db->f("last_update");
	} else {
		$last_update = '';
	}
	$query = "INSERT INTO $this->log_table_t1 SELECT * FROM $this->log_table WHERE EXTRACT(MONTH FROM datestamp) BETWEEN '01' AND '03' AND datestamp<='$last_log_entry'";
	if (!empty($last_update)) {
		$query .= " AND datestamp>'$last_update'";
	}
	$this->db->query ($query);

	$this->db->query ("SELECT MAX(datestamp) AS last_update FROM $this->log_table_t2");
	if ($this->db->next_record() ) {
		$last_update = $this->db->f("last_update");
	} else {
		$last_update = '';
	}
	$query = "INSERT INTO $this->log_table_t2 SELECT * FROM $this->log_table WHERE EXTRACT(MONTH FROM datestamp) BETWEEN '04' AND '06' AND datestamp<='$last_log_entry'";
	if (!empty($last_update)) {
		$query .= " AND datestamp>'$last_update'";
	}
	$this->db->query ($query);

	$this->db->query ("SELECT MAX(datestamp) AS last_update FROM $this->log_table_t3");
	if ($this->db->next_record() ) {
		$last_update = $this->db->f("last_update");
	} else {
		$last_update = '';
	}
	$query = "INSERT INTO $this->log_table_t3 SELECT * FROM $this->log_table WHERE EXTRACT(MONTH FROM datestamp) BETWEEN '07' AND '09' AND datestamp<='$last_log_entry'";
	if (!empty($last_update)) {
		$query .= " AND datestamp>'$last_update'";
	}
	$this->db->query ($query);

	$this->db->query ("SELECT MAX(datestamp) AS last_update FROM $this->log_table_t4");
	if ($this->db->next_record() ) {
		$last_update = $this->db->f("last_update");
	} else {
		$last_update = '';
	}
	$query = "INSERT INTO $this->log_table_t4 SELECT * FROM $this->log_table WHERE EXTRACT(MONTH FROM datestamp) BETWEEN '10' AND '12' AND datestamp<='$last_log_entry'";
	if (!empty($last_update)) {
		$query .= " AND datestamp>'$last_update'";
	}
	$this->db->query ($query);

	$this->db->query ("DELETE FROM $this->log_table WHERE datestamp<='$last_log_entry'");
	return true;

} // function splitLog()


/**
 * Purge the archive log tables
 *
 * Remove all log entries older than n months (default 12)
 * @param   integer $months	How many months to keep before last stats update 
 * @since   4.2
 * @access  public
 * @return  boolean	true if OK
 */
function purgeLogs($months=12) {

	settype($months, 'integer');

# Get last statistics computation date
# ------------------------------------
	$stat_table = $this->dailyforumstat_table;
	$this->db->query ("SELECT MAX(day_stat) - INTERVAL $months MONTH AS older_date FROM $stat_table");
	if (!$this->db->next_record() ) {
		return false;
	}

	$older_date = $this->db->f("older_date");
	if (empty($older_date) || ($older_date=='null') ) {
		return false;
	}

# remove all entries in archive logs where date is older than n months
# --------------------------------------------------------------------
	foreach ($this->log_tables as $log_table) {
		$this->db->query ("DELETE FROM $log_table WHERE datestamp < '$older_date'");
	}

	return true;

} // function purgeLogs()

/**
 * Compute daily statistics by forum
 *
 * Compute the daily statistics from the 4 log tables and insert results into the dailyforumstats table
 * @since   4.2
 * @access  public
 * @return  void
 */
function computeDailyForumStats() {

	$stat_table = $this->dailyforumstat_table;

# Delete all entries for the last computed day (since it must be re-calculated)
# -----------------------------------------------------------------------------
	$this->db->query ("SELECT MAX(day_stat) AS last_update FROM $stat_table");
	if (! $this->db->next_record() ) {
		return false;
	}

	$last_update = $this->db->f("last_update");
	if (empty($last_update) || ($last_update=='null') ) {
		$last_update = '2001-01-01 00:00:00';
	} else {
		$this->db->query ("DELETE FROM $stat_table WHERE day_stat='$last_update'");
	}

# Compute daily stats => take all entries since last stats update (including the day of the last update
# starting at 00h00
# ------------------------------------------------------------------------------------------------------
	foreach ($this->log_tables as $log_table) {
		$this->db->query ("INSERT into $stat_table 
				   (year_stat,month_stat,day_stat,forum,total_insert, total_login,total_view,total_subscribe,total_register)
					  SELECT YEAR(datestamp),
							 MONTH(datestamp),
							 DATE_FORMAT(datestamp, '%Y-%m-%d 00:00:00'),
							 forum,
							 SUM(action='insert'),
							 SUM(action='login'),
							 SUM(action='view'),
							 SUM(action='subscribe'),
							 SUM(action='register')
					FROM $log_table
					WHERE datestamp >= '$last_update'
					GROUP BY YEAR(datestamp), MONTH(datestamp), DATE_FORMAT(datestamp, '%Y-%m-%d 00:00:00'), forum ");
	}

}

/**
 * Compute daily and monthly statistics by distinct user
 *
 * Compute the daily statistics from the 4 log tables and insert results into the dailyuserstats table
 * @since   4.2
 * @access  public
 * @return  boolean
 */
function computeDailyUserStats() {

	$stat_table = $this->dailyuserstat_table;

# Get day of last table update
# ----------------------------
	$this->db->query ("SELECT MAX(day_stat) AS last_update FROM $stat_table");
	if ($this->db->next_record() ) {
		$last_update = $this->db->f("last_update");
		if (empty($last_update) || ($last_update=='null') ) {
			$last_update = '2001-01-01 00:00:00';
		} else {
# Remove last computed daily, weekly (month_stat is null and day_stat is null)
# and montly stats (day_stat is null) entries
# ----------------------------------------------------------------------
			$this->db->query ("
	DELETE FROM $stat_table 
		WHERE (day_stat = '$last_update') 
			OR ( year_stat = YEAR('$last_update')
				AND month_stat = MONTH('$last_update')
				AND week_stat IS NULL 
				AND day_stat IS NULL)
			OR ( year_stat = YEAR('$last_update')
				AND month_stat IS NULL 
				AND day_stat IS NULL 
				AND week_stat = WEEK('$last_update', 3))");
		}
	} else {
		return false;
	}

	// convert "last update date" to unix timestamp format
	$lu = strtotime($last_update);

	// Get Monday of week (in timestamp format)
	$sw = (date("D", $lu) == "Mon") ? $lu : (strtotime("next Monday", $lu)-604800);
	// Convert to SQL datetime format
	$start_week = date("YmdHis", $sw);

	// Get first day of month (in timestamp format)
	$sm = mktime (0, 0, 0, date("m", $lu), 1, date("Y", $lu) );
	// Convert to SQL datetime format
	$start_month = date("YmdHis", $sm);

	foreach ($this->log_tables as $log_table) {

		// Compute daily distinct users
		$this->db->query ("INSERT INTO $stat_table (year_stat, month_stat, week_stat, day_stat, total_users) 
			SELECT YEAR(datestamp), MONTH(datestamp), WEEK(datestamp, 3), DATE_FORMAT(datestamp, '%Y-%m-%d 00:00:00'), COUNT(distinct userid)	
				FROM $log_table	WHERE datestamp >= '$last_update'
				GROUP BY YEAR(datestamp), MONTH(datestamp), WEEK(datestamp, 3),	DATE_FORMAT(datestamp, '%Y-%m-%d 00:00:00')
		");

		// Compute weekly (day_stat=null, month_stat=null) distinct users
/*	FIX ME !! doesn't work for weeks divided into 2 log tables !!!
		$this->db->query ("INSERT INTO $stat_table (year_stat, month_stat, week_stat, day_stat, total_users)
			SELECT YEAR(datestamp), null, WEEK(datestamp, 3), null, COUNT(distinct userid)
				FROM $log_table	WHERE datestamp >= '$start_week'
				GROUP BY YEAR(datestamp), WEEK(datestamp, 3)
		");
*/
		// Compute monthly (day_stat=null, week_stat=null) distinct users
		$this->db->query ("INSERT INTO $stat_table (year_stat, month_stat, week_stat, day_stat, total_users)
			SELECT YEAR(datestamp), MONTH(datestamp), null, null, COUNT(distinct userid)
				FROM $log_table	WHERE datestamp >= '$start_month'
				GROUP BY YEAR(datestamp), MONTH(datestamp)
		");

	}

}

/**
 * Gets monthly statistics.
 *
 * @param   year    the year (YYYY) facultative
 * @param   month   the month (1-12) facultative (if set, year must also be set)
 * @since   4.1.3
 * @access  public
 * @return  array       A double dimension array : $array[month][action] = count
 *                      where month as the form YYYYMM, and action
 *                      can be one value of view|insert|subscribe|login
 */
function getMonthlyStats ($year='', $month='') {

	$stat_table = $this->dailyforumstat_table;

    $query = "SELECT year_stat, month_stat, SUM(total_insert) AS t_insert, SUM(total_login) AS t_login, SUM(total_view) AS t_view, SUM(total_subscribe) AS t_subscribe, SUM(total_register) AS t_register FROM $stat_table";

	if (!empty($year)) {
		$y = sprintf('%04d', $year);
		$query .= " WHERE year_stat=$y";
		if (!empty($month)) {
			$m = sprintf('%02d', $month);
			$query .= " AND month_stat=$m";
		}
	}

	$query .= ' GROUP BY year_stat, month_stat';

	$this->db->query($query);
	while ($this->db->next_record()) {
		$m = sprintf ('%04d%02d', $this->db->Record['year_stat'], $this->db->Record['month_stat']);
		$stats[$m]['insert'] = (int) $this->db->Record['t_insert'];
		$stats[$m]['login'] = (int) $this->db->Record['t_login'];
		$stats[$m]['view'] = (int) $this->db->Record['t_view'];
		$stats[$m]['subscribe'] = (int) $this->db->Record['t_subscribe'];
		$stats[$m]['register'] = (int) $this->db->Record['t_register'];
	}
	return $stats;
}


/**
 * Gets Daily statistics.
 *
 * @param   year    the year (YYYY)
 * @param   month   the month (1-12)
 * @since   4.1.3
 * @access  public
 * @return  array       A double dimension array : $array[day][action] = count
 *                      where day is in the rang 1..31 and action can be one value of view|insert|subscribe|login
 */
function getDailyStats ($year, $month, $forum='') {

	$stat_table = $this->dailyforumstat_table;
	$y = sprintf('%04d', $year);
	$m = sprintf('%02d', $month);

	$query = "SELECT dayofmonth(day_stat) AS d, SUM(total_insert) AS t_insert, SUM(total_login) AS t_login, SUM(total_view) AS t_view, SUM(total_subscribe) AS t_subscribe, SUM(total_register) AS t_register FROM $stat_table WHERE year_stat=$y AND month_stat=$m";
	
	if (!empty($forum)) {
		$query .= " AND forum='$forum'";
	}
	
	$query .= "	GROUP BY dayofmonth(day_stat)";


	$this->db->query("$query");

	while ($this->db->next_record()) {
		$d = sprintf('%02d', $this->db->Record['d']);
		$stats[$d]['insert'] = (int) $this->db->Record['t_insert'];
		$stats[$d]['login'] = (int) $this->db->Record['t_login'];
		$stats[$d]['view'] = (int) $this->db->Record['t_view'];
		$stats[$d]['subscribe'] = (int) $this->db->Record['t_subscribe'];
		$stats[$d]['register'] = (int) $this->db->Record['t_register'];
	}
	return $stats;
}


/**
 * Gets monthly visitors.
 *
 * Returns an array indexed by months with the number of distinct visitors
 * @since   4.1.3
 * @access  public
 * @return  array       An array of the form: $array[month] = visits where month as the form YYYYMM
 */
function getMonthlyVisitors ($year='', $month='') {

	$stat_table = $this->dailyuserstat_table;

	$query = "SELECT year_stat, month_stat, total_users FROM $stat_table WHERE day_stat IS NULL AND week_stat IS NULL";

	if (!empty($year)) {
		$y = sprintf('%04d', $year);
		$query .= " AND year_stat=$y";
		if (!empty($month)) {
			$m = sprintf('%02d', $month);
			$query .= " AND month_stat=$m";
		}
	}

	$this->db->query($query);
	while ($this->db->next_record()) {
		$m = sprintf ('%04d%02d', $this->db->Record['year_stat'], $this->db->Record['month_stat']);
		$visits[$m] = (int) $this->db->Record['total_users'];
	}
	return $visits;
}

/**
 * Gets Daily statistics.
 *
 * @param   year    the year (YYYY)
 * @param   month   the month (1-12)
 * @since   4.1.3
 * @access  public
 * @return  array       A double dimension array : $array[day][action] = count
 *                      where day is in the rang 1..31 and action can be one value of view|insert|subscribe|login
 */
function getDailyVisitors ($year, $month ) {
	
	$stat_table = $this->dailyuserstat_table;

	$y = sprintf('%04d', $year);
	$m = sprintf('%02d', $month);

	$this->db->query("SELECT dayofmonth(day_stat) AS d, total_users FROM $stat_table WHERE year_stat=$y AND month_stat=$m AND day_stat IS NOT NULL");
	while ($this->db->next_record()) {
		$d = sprintf('%02d', $this->db->Record['d']);
		$visits[$d] = (int) $this->db->Record['total_users'];
	}
	return $visits;
}

} // end class