Been working on the forum statistics but need help on 1 item

Support Mod Joined: Mar 19, 2003 Posts: 308

Dear, I have been working on the forum statistics and all seem working very well ==> http://www.luchtzak.be/modules.php?name=Forums&file=statistics

However I have one unsolved problem, the phpbb-script that I like to install is the day-per-week registrations. Phpbb uses the UNIX-time in the database for the registration-date of the members while PHPNuke uses the '23 aug 2004'-format. I post the script below, is there a way that the script will check the registration dates in the PHPNuke users-table so that it perfectly works?

I think it's in the $sql and FROM_UNIXTIME but really don't know how set the conversion. Thanks for the help!

Code:

<?php
/***************************************************************************
* File: module.php
* -------------------
* Statistic: 'Day-of-Week Traffic: Registrations' v 1.0.0
* Description: Returns statistics for the volume of new registrations
*             grouped by day of the week.
*    Copyright: (C) 2003 John McKernan, All rights reserved.
*
****************************************************************************/

/***************************************************************************
*
* Statistics Module for use with the Statistics System by Acyd Burn.
*
****************************************************************************/

/***************************************************************************
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
***************************************************************************/

// setup
$days = array(1 => 'Sunday', 2 => 'Monday', 3 => 'Tuesday', 4 => 'Wednesday', 5 => 'Thursday', 6 => 'Friday', 7 => 'Saturday');

$traffic = array(1 => 0, 2 => 0, 3 => 0, 4 => 0, 5 => 0, 6 => 0, 7 => 0);

// start template
$template->assign_vars(array(
'L_TITLE' => $lang['Traffic_reg'],
'L_REG' => $lang['New_users'],
'L_PERCENTAGE' => $lang['Percent'],
'L_DOW' => $lang['Dow'],
'L_GRAPH' => $lang['Graph'])
);

// define graph bars
$statistics->init_bars();

// get total regs
$sql = "SELECT COUNT(user_id) as total_reg FROM " . USERS_TABLE;

if ( !($result = $stat_db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Unable to retrieve users data', '', __LINE__, __FILE__, $sql);
}

$row = $stat_db->sql_fetchrow($result);
$total_reg = $row['total_reg'];

// return statistics
$sql = 'SELECT DAYOFWEEK(FROM_UNIXTIME(user_regdate)) as dow, COUNT(*) AS ct
FROM ' . USERS_TABLE . '
WHERE user_id <> -1
GROUP BY DAYOFWEEK(FROM_UNIXTIME(user_regdate))
ORDER BY DAYOFWEEK(FROM_UNIXTIME(user_regdate)) ASC';

if ( !($result = $stat_db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Unable to retrieve users data', '', __LINE__, __FILE__, $sql);
}

$reg_data = $stat_db->sql_fetchrowset($result);

// get highest new users' count
$max_reg_ct = '0';

for ($i = 0; $i < 7; $i++)
{
$max_reg_ct = max($max_reg_ct, $reg_data[$i]['ct']);
}

// build rows
for ($i = 1; $i < 8; $i++)
{
$class = ( !($i+1 % 2) ) ? $theme['td_class2'] : $theme['td_class1'];

if ( $reg_data[$i - 1]['ct'] )
{
   $traffic[$i] = $reg_data[$i - 1]['ct'];
}

$statistics->do_math($max_reg_ct, $reg_data[$i - 1]['ct'], $total_reg);

$template->assign_block_vars('traffic', array(
   'CLASS' => $class,
   'DOW' => $lang['datetime'][$days[$i]],
   'REG' => $traffic[$i],
   'PERCENTAGE' => $statistics->percentage,
   'BAR' => $statistics->bar_percent)
);
}

?>