add user registrations based on referers

4 files changed, 91 insertions, 32 deletions

diff --git a/Statistics.php b/Statistics.php
index d8e627e..2e58ea5 100644
--- a/Statistics.php
+++ b/Statistics.php
@@ -19,6 +19,33 @@ class Statistics extends BitBase {
 		BitBase::BitBase();
 	}
 
+	function prepGetList( &$pListHash ) {
+
+		if( !empty( $pListHash['period'] ) ) {
+			switch( $pListHash["period"] ) {
+				case 'year':
+					$format = 'Y';
+					break;
+				case 'quarter':
+					$format = 'Y-\QQ';
+					break;
+				case 'day':
+					$format = 'Y-m-d';
+					break;
+				case 'week':
+					$format = 'Y \Week W';
+					break;
+				case 'month':
+				default:
+					$format = 'Y-m';
+					break;
+			}
+			$pListHash['period_format'] = $format;
+		}
+
+		parent::prepGetList( $pListHash );
+	}
+
 	/**
 	 * getRefererList gets a list of referers
 	 *
@@ -27,36 +54,55 @@ class Statistics extends BitBase {
 	 * @return array of referers
 	 */
 	function getRefererList( &$pListHash ) {
+
+		$ret = $bindVars = array();
+		$selectSql = $joinSql = $whereSql = $groupSql = "";
+
 		if( empty( $pListHash['sort_mode'] )) {
-			$pListHash['sort_mode'] = 'hits_desc';
+			$pListHash['sort_mode'] = 'uu.`registration_date_desc`';
 		}
 
-		LibertyContent::prepGetList( $pListHash );
+		self::prepGetList( $pListHash );
 
-		// LibertyContent::prepGetList assumes that 'hits_' refers to liberty_content what is wrong in this case
-		if ( is_string( $pListHash['sort_mode'] ) && strpos( $pListHash['sort_mode'], 'lch.' ) === 0 ) {
-			$pListHash['sort_mode'] = substr($pListHash['sort_mode'],4);
+		if( !empty( $pListHash['period_format'] ) ) {
+			$whereSql .= empty( $whereSql ) ? ' WHERE ' : ' AND ';
+			$whereSql .= $this->mDb->SQLDate( $pListHash['period_format'], $this->mDb->SQLIntToTimestamp( 'registration_date' )).'=?';
+			$bindVars[] = $pListHash['itemize'];
 		}
 
-		$ret = $bindVars = array();
-		$selectSql = $joinSql = $whereSql = "";
-
 		if( !empty( $pListHash['find'] ) && is_string( $pListHash['find'] )) {
 			$whereSql  .= empty( $whereSql ) ? ' WHERE ' : ' AND ';
 			$whereSql  .= " UPPER( `referer` ) LIKE ?";
 			$bindVars[] = '%'.strtoupper( $pListHash['find'] ).'%';
 		}
 
-		$query = "SELECT * FROM `".BIT_DB_PREFIX."stats_referers` $whereSql ORDER BY ".$this->mDb->convertSortmode( $pListHash['sort_mode'] );;
-		$ret = $this->mDb->getAll( $query, $bindVars, $pListHash['max_records'], $pListHash['offset'] );
+		$query = "SELECT uu.`user_id` AS `hash_key`, uu.*, sru.`referer_url` 
+					FROM `".BIT_DB_PREFIX."users_users` uu
+					 	LEFT JOIN `".BIT_DB_PREFIX."stats_referer_users_map` srum ON(uu.`user_id`=srum.`user_id`)
+						LEFT JOIN  `".BIT_DB_PREFIX."stats_referer_urls` sru ON (sru.`referer_url_id`=srum.`referer_url_id`) 
+				$whereSql ORDER BY ".$this->mDb->convertSortmode( $pListHash['sort_mode'] );
+		if( $rs = $this->mDb->query( $query, $bindVars, -1, $pListHash['offset'] ) ) {
+			while( $row = $rs->fetchRow() ) {
+				$host = 'none';
+				if( !empty( $row['referer_url'] ) ) {
+					$parseUrl = parse_url( $row['referer_url'] );
+					$host = $parseUrl['host'];
+				}
+				$ret[$host][$row['user_id']] = $row;
+			}
+		}
 
-		$query = "SELECT COUNT(*) FROM `".BIT_DB_PREFIX."stats_referers` $whereSql";
-		$pListHash['cant'] = $this->mDb->getOne( $query, $bindVars);
 		LibertyContent::postGetList( $pListHash );
 
+		uasort( $ret, array( $this, 'sortRefererHash' ) );
+
 		return $ret;
 	}
 
+	function sortRefererHash( $a, $b ) {
+		return count( $a ) < count( $b );
+	}
+
 	/**
 	 * expungeReferers will remove all referers unconditionally
 	 *
diff --git a/referer_stats.php b/referer_stats.php
index 7056083..6e13bba 100644
--- a/referer_stats.php
+++ b/referer_stats.php
@@ -22,8 +22,20 @@ $gStats = new Statistics();
 if( isset( $_REQUEST["clear"] )) {
 	$gStats->expungeReferers();
 }
+$referers = $gStats->getRefererList( $_REQUEST );
+$totalRegistrations = 0;
+$maxRegistrations = 0;
+foreach( array_keys( $referers ) as $k ) {
+	$kCount = count( $referers[$k] );
+	$totalRegistrations += $kCount;
+	if( $kCount > $maxRegistrations ) {
+		$maxRegistrations = $kCount;
+	}
+}
 
-$gBitSmarty->assign( 'referers', $gStats->getRefererList( $_REQUEST ));
+$gBitSmarty->assign_by_ref( 'referers', $referers );
+$gBitSmarty->assign( 'totalRegistrations', $totalRegistrations );
+$gBitSmarty->assign( 'maxRegistrations', $maxRegistrations );
 $gBitSmarty->assign( 'listInfo', $_REQUEST['listInfo'] );
 $gBitSystem->display( 'bitpackage:stats/referer_stats.tpl', tra( 'Referer Statistics' ), array( 'display_mode' => 'display' ));
 ?>
diff --git a/templates/referer_stats.tpl b/templates/referer_stats.tpl
index 7a43de5..a7e4b09 100644
--- a/templates/referer_stats.tpl
+++ b/templates/referer_stats.tpl
@@ -2,31 +2,31 @@
 
 <div class="display statistics">
 	<div class="header">
-		<h1>{tr}Referer Statistics{/tr}</h1>
+		<h1>{tr}User Registration Statistics{/tr}</h1>
+		{minifind}
 	</div>
 
 	<div class="body">
-		<a href="{$smarty.const.STATS_PKG_URL}referer_stats.php?clear=1">{tr}Clear Referer Statistics{/tr}</a>
-
-		{minifind}
-
 		<table class="data">
-			<caption>{tr}Referer Statistics{/tr}</caption>
-			<tr>
-				<th style="width:5%;">{smartlink ititle="Hits" isort=hits iorder=desc idefault=1 offset=$offset}</th>
-				<th>{smartlink ititle="Referer" isort=referer offset=$offset}</th>
-				<th style="width:15%;">{smartlink ititle="Last Hit" isort=last offset=$offset}</th>
-			</tr>
+			<caption>{tr}User Registration Statistics{/tr}</caption>
 
-			{section name=ix loop=$referers}
-				<tr class="{cycle values='odd,even'}">
-					<td style="text-align:right;">{$referers[ix].hits}</td>
-					<td><a href="{$referers[ix].referer}">{$referers[ix].referer}</a></td>
-					<td style="text-align:right;">{$referers[ix].last|bit_short_datetime}</td>
+			{foreach from=$referers key=host item=reg}
+				{assign var=hostHash value=$host|md5}	
+				<tr>
+					<th style="width:5%;">{$reg|@count} ({math equation="round((x / y) * 100)" x=$reg|@count y=$totalRegistrations}%)</th>
+					<th>{biticon iname='folder-saved-search' onclick="BitBase.toggleElementDisplay('`$hostHash`','table-row-group');" class="floaticon"} <div style="width:{math equation="round( ( r / m ) * 100 )" r=$reg|@count m=$maxRegistrations}%; background:#f80;padding:0 0 0 5px;">{$host|escape}</div></th>
 				</tr>
-			{sectionelse}
+
+				<tbody id="{$hostHash}" style="display:none">
+				{foreach from=$reg key=userId item=user}
+					<tr class="{cycle values='odd,even'}">
+						<td colspan="2"><strong style="font-size:larger">{displayname hash=$user}</strong>{if $user.referer_url}<br/><a href="{$user.referer_url|escape}">{$user.referer_url|stats_referer_display_short}</a>{/if}</td>
+					</tr>
+				{/foreach}
+				</tbody>
+			{foreachelse}
 				<tr class="norecords"><td colspan="3">{tr}No records found{/tr}</td></tr>
-			{/section}
+			{/foreach}
 		</table>
 	</div> <!-- end .body -->
 
diff --git a/templates/user_stats.tpl b/templates/user_stats.tpl
index 1ede26d..ed49f89 100644
--- a/templates/user_stats.tpl
+++ b/templates/user_stats.tpl
@@ -18,12 +18,13 @@
 		<table class="clear data">
 			<caption>{tr}User Registrations at {$gBitSystem->getConfig('site_title')}{/tr}</caption>
 			<tr>
-				<th style="width:20%;">{tr}Period{/tr}</td>
+				<th style="width:20%;" colspan="2">{tr}Period{/tr}</td>
 				<th style="width:80%;">{tr}Number of Registrations{/tr}</td>
 			</tr>
 			{foreach item=reg key=period from=$userStats.per_period}
 				<tr class="{cycle values="even,odd"}">
 					<td><a href="{$smarty.const.STATS_PKG_URL}users.php?period={$smarty.request.period}&amp;itemize={$period|urlencode}">{$period}</td>
+					<td>[<a href="{$smarty.const.STATS_PKG_URL}referer_stats.php?period={$smarty.request.period}&amp;itemize={$period|urlencode}">Referrers</a>]</td>
 					<td><div style="width:{math equation="round( ( r / m ) * 100 )" r=$reg m=$userStats.max}%; background:#f80;padding:0 0 0 5px;">{$reg}</div></td>
 				</tr>
 			{foreachelse}