major overhaul of referer stats to include commerce data if available

8 files changed, 228 insertions, 56 deletions

diff --git a/Statistics.php b/Statistics.php
index 6531778..86c243e 100644
--- a/Statistics.php
+++ b/Statistics.php
@@ -71,7 +71,7 @@ class Statistics extends BitBase {
 					 	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'] ) ) {
+		if( $rs = $this->mDb->query( $query, $bindVars, -1, $pListHash['offset'], 1800 ) ) {
 
 			while( $row = $rs->fetchRow() ) {
 				$key = $row['hash_key'];
diff --git a/bit_setup_inc.php b/bit_setup_inc.php
index 53e18b3..255a8db 100644
--- a/bit_setup_inc.php
+++ b/bit_setup_inc.php
@@ -71,10 +71,17 @@ if( $gBitSystem->isPackageActive( 'stats' )) {
 		if( $urlHash = parse_url( $pRefererUrl ) ) {
 			$ret = $urlHash['host'];
 			if( !empty( $urlHash['query'] ) && strpos( $urlHash['query'], 'q=' ) !== FALSE ) {
+				// google and bing search param
 				parse_str( $urlHash['query'] );
 				if( !empty( $q ) ) {
 					$ret .= '/...q='.$q;
 				}
+			} elseif( !empty( $urlHash['query'] ) && strpos( $urlHash['query'], 'p=' ) !== FALSE ) {
+				// yahoo search param
+				parse_str( $urlHash['query'] );
+				if( !empty( $p ) ) {
+					$ret .= '/...p='.$p;
+				}
 			}
 		} else {
 			$ret = tra( 'Unknown URL' );
diff --git a/referer_stats.php b/referer_stats.php
deleted file mode 100644
index 6e13bba..0000000
--- a/referer_stats.php
+++ /dev/null
@@ -1,41 +0,0 @@
-<?php
-/**
- * $Header$
- *
- * $Id$
- * @package stats
- */
-
-/**
- * required setup
- */
-require_once( '../kernel/setup_inc.php' );
-include_once ( STATS_PKG_PATH.'Statistics.php');
-
-$gBitSystem->verifyPackage( 'stats' );
-$gBitSystem->verifyFeature( 'stats_referers' );
-$gBitSystem->verifyPermission( 'p_stats_view_referer' );
-
-$gStats = new Statistics();
-
-// get rid of all referers in the database
-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_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/referers.php b/referers.php
new file mode 100644
index 0000000..37eb6cd
--- /dev/null
+++ b/referers.php
@@ -0,0 +1,99 @@
+<?php
+/**
+ * $Header$
+ *
+ * $Id$
+ * @package stats
+ */
+
+/**
+ * required setup
+ */
+require_once( '../kernel/setup_inc.php' );
+include_once ( STATS_PKG_PATH.'Statistics.php');
+
+$gBitSystem->verifyPackage( 'stats' );
+$gBitSystem->verifyFeature( 'stats_referers' );
+$gBitSystem->verifyPermission( 'p_stats_view_referer' );
+
+$gStats = new Statistics();
+
+// get rid of all referers in the database
+if( isset( $_REQUEST["clear"] )) {
+	$gStats->expungeReferers();
+}
+$referers = $gStats->getRefererList( $_REQUEST );
+$totalRegistrations = 0;
+$maxRegistrations = 0;
+
+$aggregateStats = array();
+foreach( array_keys( $referers ) as $k ) {
+	$kCount = count( $referers[$k] );
+	$totalRegistrations += $kCount;
+	if( $kCount > $maxRegistrations ) {
+		$maxRegistrations = $kCount;
+	}
+	foreach( array_keys( $referers[$k] ) as $r ) {
+		$url = parse_url( $referers[$k][$r]['referer_url'] );
+		$revenue = array();
+		if( $gBitSystem->isPackageActive( 'bitcommerce' ) ) {
+			require_once( BITCOMMERCE_PKG_PATH.'includes/bitcommerce_start_inc.php' );
+			require_once( BITCOMMERCE_PKG_PATH.'classes/CommerceStatistics.php' );
+			$revenue = $gCommerceStatistics::getCustomerRevenue( array( 'customers_id' => $referers[$k][$r]['user_id'] ) );
+			$referers[$k][$r]['revenue'] = $revenue;
+		}
+		if( !empty( $url['query'] ) ) {
+			$urlParams = array();
+			parse_str( $url['query'], $urlParams );
+			if( !empty( $urlParams['adurl'] ) ) {
+				$adUrl = parse_url( $urlParams['adurl'] );
+				if( !empty( $adUrl['query'] ) ) {
+					$adParams = array();
+					parse_str( $adUrl['query'], $adParams );
+					foreach( $adParams as $key => $value ) {
+						computeStats( $aggregateStats, $k, $key, $value, $revenue, $referers[$k][$r] );
+					}
+				} else {
+					$key = 'Paid';
+					$value = $adUrl['path'];
+					computeStats( $aggregateStats, $k, $key, $value, $revenue, $referers[$k][$r] );
+				}
+			} else {
+				// bing paid query
+				foreach( array( 'pq' => 'Paid', 'q' => 'Organic', 'p' => 'Organic', 'unknown' => 'Unknown' ) as $key=>$title ) {
+					if( $key == 'unknown' || isset( $urlParams[$key] ) ) {
+						$value = BitBase::getParameter( $urlParams, $key, 'unknown' );
+						computeStats( $aggregateStats, $k, $title, $value, $revenue, $referers[$k][$r] );
+						break;
+					}
+				}
+			}
+		}
+		if( !empty( $revenue['total_orders'] ) ) {
+			@$aggregateStats[$k]['revenue'] += $revenue['total_revenue'];
+			@$aggregateStats[$k]['orders'] += $revenue['total_orders'];
+		}
+	}
+}
+
+function computeStats( &$aggregateStats, $k, $key, $value, $revenue, $userHash ) {
+	if( !empty( $revenue['total_orders'] ) ) {
+		@$aggregateStats[$k]['values'][$key]['values'][$value]['revenue'] += $revenue['total_revenue'];
+		@$aggregateStats[$k]['values'][$key]['values'][$value]['orders'] += $revenue['total_orders'];
+		@$aggregateStats[$k]['values'][$key]['revenue'] += $revenue['total_revenue'];
+		@$aggregateStats[$k]['values'][$key]['orders'] += $revenue['total_orders'];
+	}
+	$aggregateStats[$k]['values'][$key]['values'][$value]['users'][] = $userHash;
+	@$aggregateStats[$k]['values'][$key]['registrations']++;
+}
+
+$gBitThemes->loadCss( CONFIG_PKG_PATH.'themes/bootstrap/bootstrap-table/bootstrap-table.css');
+$gBitThemes->loadJavascript( CONFIG_PKG_PATH.'themes/bootstrap/bootstrap-table/bootstrap-table.js');
+
+$gBitSmarty->assign_by_ref( 'aggregateStats', $aggregateStats );
+$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/menu_stats.tpl b/templates/menu_stats.tpl
index c61cc1b..976f6c4 100644
--- a/templates/menu_stats.tpl
+++ b/templates/menu_stats.tpl
@@ -8,7 +8,7 @@
 		{/if}
 	{/if}
 	{if $gBitSystem->isFeatureActive( 'stats_referers' ) and $gBitUser->hasPermission( 'p_stats_view_referer' )}
-		<li><a class="item" href="{$smarty.const.STATS_PKG_URL}referer_stats.php">{booticon iname="icon-bullhorn" iexplain="Referer Stats" ilocation=menu}</a></li>
+		<li><a class="item" href="{$smarty.const.STATS_PKG_URL}referer.php">{booticon iname="icon-bullhorn" iexplain="Referer Stats" ilocation=menu}</a></li>
 	{/if}
 </ul>
 {/strip}
diff --git a/templates/menu_stats_admin.tpl b/templates/menu_stats_admin.tpl
index 3502a17..6db21d0 100644
--- a/templates/menu_stats_admin.tpl
+++ b/templates/menu_stats_admin.tpl
@@ -7,7 +7,7 @@
 		<li><a class="item" href="{$smarty.const.STATS_PKG_URL}users.php">{tr}User Stats{/tr}</a></li>
 	{/if}
 	{if $gBitSystem->isFeatureActive( 'stats_referers' ) and $gBitUser->hasPermission( 'p_stats_view_referer' )}
-		<li><a class="item" href="{$smarty.const.STATS_PKG_URL}referer_stats.php">{tr}Referer Stats{/tr}</a></li>
+		<li><a class="item" href="{$smarty.const.STATS_PKG_URL}referer.php">{tr}Referer Stats{/tr}</a></li>
 	{/if}
 </ul>
 {/strip}
diff --git a/templates/referer_stats.tpl b/templates/referer_stats.tpl
index 2cacaed..d33831f 100644
--- a/templates/referer_stats.tpl
+++ b/templates/referer_stats.tpl
@@ -1,3 +1,17 @@
+<script>
+    function priceSorter(a, b) {
+		if( a ) {
+	        a = parseFloat( a.replace(/[^\d.]/g,'') );
+		}
+		if( b ) {
+	        b = parseFloat( b.replace(/[^\d.]/g,'') );
+		}
+		if (a > b) return 1;
+		if (a < b) return -1;
+        return 0;
+    }
+</script>
+
 <div class="floaticon">{bithelp}</div>
 
 <div class="display statistics">
@@ -6,30 +20,123 @@
 		{minifind period_format="`$smarty.request.period_format`" timeframe="`$smarty.request.timeframe`"}
 	</div>
 
+
+
 	<div class="body">
 		<table class="table data">
 			<caption>{tr}User Registration Statistics{/tr}</caption>
 			{assign var=refCount value=0}
 			{foreach from=$referers key=host item=reg}
+				{assign var=hostKey value=$host|strip:'.':''}
 				{assign var=regCount value=$reg|@count}
 				{assign var=totalReg value=$totalReg+$regCount}
 				{assign var=hostHash value=$host|md5}	
 				<tr>
-					<th style="width:5%;">{$reg|@count}</th>
-					<th><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>
-					<th><div class="floaticon"> [{math equation="round((x / y) * 100)" x=$reg|@count y=$totalRegistrations}% ] <a href="{$smarty.server.SCRIPT_NAME}?period={$smarty.request.period}&amp;find={$host|escape}">{booticon iname='icon-clock'}</a>{biticon iname='folder-saved-search' onclick="BitBase.toggleElementDisplay('`$hostHash`','table-row-group');"}</div></th>
+					<th>{booticon iname='icon-search' class="btn btn-default btn-sm" onclick="BitBase.toggleElementDisplay('`$hostHash`','table-row-group');"}</th>
+					<th style="position:relative"><div style="position:absolute;z-index:-1;width:{math equation="round( ( r / m ) * 100 )" r=$reg|@count m=$maxRegistrations}%; background:#CAF3FF;padding:0 0 0 5px;">&nbsp;</div>{$host|escape}</th>
+					<th class="text-right"><div class="floaticon"> [{math equation="round((x / y) * 100)" x=$reg|@count y=$totalRegistrations}% ] <a href="{$smarty.server.SCRIPT_NAME}?period={$smarty.request.period}&amp;find={$host|escape}">{booticon iname='icon-clock'}</a></div></th>
+				{if $aggregateStats.$host}
+					<th class="text-right">{$reg|@count} {booticon iname="icon-user"}</th>
+					<th class="text-right">{$aggregateStats.$host.orders|default:"0"} {booticon iname="icon-shopping-cart"}</th>
+					<th class="text-right">{$gCommerceCurrencies->format($aggregateStats.$host.revenue|default:"0.00")}</th>
+				{/if}
 				</tr>
 
 				<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>
-						<td class="date">{$user.registration_date|bit_date_format}</td>
-					</tr>
-				{/foreach}
+					<tr><td colspan="6">
+						<div class="panel-group" id="accordion-{$hostHash}" role="tablist" aria-multiselectable="true">
+						{foreach from=$aggregateStats.$host.values item=paramValues key=paramKey name=agstat}
+							<div class="panel panel-default">
+								<div class="panel-heading" role="tab" id="accordion-{$paramKey}">
+									<div class="pull-right">{$paramValues.registrations} {booticon iname="icon-user"} {$paramValues.orders} {booticon iname="icon-shopping-cart"} {$gCommerceCurrencies->format($paramValues.revenue)}</div>
+									<h4 class="panel-title">
+										<a class="collapsed" data-toggle="collapse" data-parent="#accordion-{$hostHash}" href="#collapse-{$hostHash}-{$paramKey}" aria-expanded="false" aria-controls="collapse-{$hostHash}-{$paramKey}">{$paramKey}</a>
+									</h4>
+								</div>
+								<div id="collapse-{$hostHash}-{$paramKey}" class="panel-collapse collapse" role="tabpanel" aria-labelledby="heading{$smarty.foreach.agstat.iter}">
+									<div class="panel-body">
+										<div class="panel-group" id="accordion-{$hostHash}-{$paramKey}" role="tablist" aria-multiselectable="true">
+<table data-toggle="table">
+<thead>
+<tr>
+	<th>{$paramKey}</th>
+	<th class="text-center" data-field="registrations" data-sortable="true">{booticon iname="icon-user"}</th>
+	<th class="text-center" data-field="order_count" data-sortable="true">{booticon iname="icon-shopping-cart"}</th>
+	<th class="text-center" data-field="revenue" data-sortable="true" data-sorter="priceSorter">Revenue</th>
+</tr>
+</thead>
+<tbody>
+										{foreach from=$paramValues.values item=valueHash key=valueKey name=paramval}
+<tr>
+	<td><a href="#" onclick="BitBase.toggleElementDisplay('order-list-{$hostHash}-{$paramKey}-{$valueKey}','block');return false;">{$valueKey|default:"unknown"}</a>
+		<ol class="data" id="order-list-{$hostHash}-{$paramKey}-{$valueKey}" style="display:none">
+		{foreach from=$valueHash.users item=userHash}
+			<li class="item width100p" style="border-bottom:1px solid #cccccc;">
+				<div class="inline-block width10p date">{$userHash.registration_date|bit_date_format}</div>
+				<div class="inline-block width65p">{if $userHash.referer_url}<a href="{$userHash.referer_url|escape}">{$userHash.referer_url|stats_referer_display_short}</a><br/>{/if}{BitUser::getDisplayLink(1,$userHash)} - {$userHash.email}</div>
+				<div class="inline-block text-right width10p">{if $userHash.revenue.total_orders}<a target="_new" href="{$smarty.const.BITCOMMERCE_PKG_URL}admin/list_orders.php?user_id={$userHash.user_id}">{$userHash.revenue.total_orders} {tr}orders{/tr}</a>{/if}</div>
+				<div class="inline-block text-right width10p">{if $userHash.revenue.total_revenue}{$gCommerceCurrencies->format($userHash.revenue.total_revenue)}{/if}</div>
+			</li>
+		{/foreach}
+		</ol>
+	</td>
+	<td class="text-right">{if $valueHash.users}{$valueHash.users|count}{/if}</td>
+	<td class="text-right">{if $valueHash.orders}{$valueHash.orders}{/if}</td>
+	<td class="text-right">{if $valueHash.revenue}{$gCommerceCurrencies->format($valueHash.revenue)}{/if}</td>
+</tr>
+										{/foreach}
+</tbody>
+</table>
+										</div>
+									</div>
+								</div>
+							</div>
+						{/foreach}
+							{assign var=paramKey value="Everything"}
+							<div class="panel panel-default">
+								<div class="panel-heading" role="tab" id="accordion-{$paramKey}">
+								{if $aggregateStats.$host}
+									<div class="pull-right">
+										{$reg|@count} {booticon iname="icon-user"} {$aggregateStats.$host.orders|default:"0"} {booticon iname="icon-shopping-cart"} {$gCommerceCurrencies->format($aggregateStats.$host.revenue|default:"0.00")}
+									</div>
+								{/if}
+									<h4 class="panel-title">
+										<a class="collapsed" data-toggle="collapse" data-parent="#accordion-{$hostHash}" href="#collapse-{$hostHash}-{$paramKey}" aria-expanded="false" aria-controls="collapse-{$hostHash}-{$paramKey}">{$paramKey}</a>
+									</h4>
+								</div>
+								<div id="collapse-{$hostHash}-{$paramKey}" class="panel-collapse collapse" role="tabpanel" aria-labelledby="heading{$smarty.foreach.agstat.iter}">
+									<div class="panel-body">
+										<div class="panel-group" id="accordion-{$hostHash}-{$paramKey}" role="tablist" aria-multiselectable="true">
+											<table data-toggle="table">
+											<thead>
+											<tr>
+												<th></th>
+												<th data-field="name" data-sortable="true">User</th>
+												<th class="text-center" data-field="order_count" data-sortable="true">{booticon iname="icon-shopping-cart"}</th>
+												<th class="text-center" data-field="revenue" data-sortable="true" data-sorter="priceSorter">Revenue</th>
+											</tr>
+											</thead>
+											<tbody>
+											{foreach from=$reg key=userId item=user}
+												<tr class="{cycle values='odd,even'}">
+													<td class="date">{$user.registration_date|bit_date_format}</td>
+													<td>
+														<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>
+	<td class="text-right">{if $user.revenue.total_orders}{$user.revenue.total_orders}{/if}</td>
+	<td class="text-right">{if $user.revenue.total_revenue}{$gCommerceCurrencies->format($user.revenue.total_revenue)}{/if}</td>
+												</tr>
+											{/foreach}
+											</tbody>
+											</table>
+										</div>
+									</div>
+								</div>
+							</div>
+						</div>
+					</td></tr>
 				</tbody>
+
 			{foreachelse}
 				<tr class="norecords"><td colspan="3">{tr}No records found{/tr}</td></tr>
 			{/foreach}
diff --git a/templates/user_stats.tpl b/templates/user_stats.tpl
index b200ac9..ef88bcf 100644
--- a/templates/user_stats.tpl
+++ b/templates/user_stats.tpl
@@ -23,7 +23,7 @@
 				<tr class="{cycle values="even,odd"}">
 					<td><a href="{$smarty.const.STATS_PKG_URL}users.php?period={$smarty.request.period}&amp;timeframe={$timeframe|urlencode}">{$timeframe}</td>
 					<td>
-						[<a href="{$smarty.const.STATS_PKG_URL}referer_stats.php?period={$smarty.request.period}&amp;timeframe={$timeframe|urlencode}">Referrers</a>]
+						[<a href="{$smarty.const.STATS_PKG_URL}referers.php?period={$smarty.request.period}&amp;timeframe={$timeframe|urlencode}">Referrers</a>]
 					</td>
 					<td><div style="width:{math equation="round( ( r / m ) * 100 )" r=$reg m=$userStats.max}%; background:#ff9;padding:0 0 0 5px;"><a href="{$smarty.const.USERS_PKG_URL}admin/index.php?period={$smarty.request.period}&amp;timeframe={$timeframe|urlencode}">{$reg}</a></div></td>
 				</tr>