| metinex |
08-01-2006 07:44 PM |
A sales report based on users
With this mod you will get a sales report based on users with their referer and brief order information. It will also list new registered users with no order for sales department to follow up.
Create include/sales_report.php and place the code below:
Code:
<?php
/*****************************************************************************\
OLJAI
\*****************************************************************************/
#
# $Id: users.php,v 1.44.2.3 2005/10/26 05:31:40 max Exp $
#
require "./auth.php";
require $xcart_dir."/include/security.php";
$smarty->assign("main","sales_report");
$end_date = time() + $config["General"]["timezone_offset"];
$lastday=(($_POST['lastday']=="0" || $_POST['lastday']) ?$_POST['lastday'] : "0");
$start_date = mktime(date("G",$end_date),date("j",$end_date),date(" ",$end_date),date("n",$end_date),date("j",$end_date),date("Y",$end_date));
$start_date=mktime(0, 0, 0, date("m"), date("d")-$lastday, date("Y"));
$search_condition .= " o.date>='".($start_date)."'";
$search_condition .= " AND o.date<='".($end_date)."'";
$search_condition .= "AND o.status<>'F' AND o.status<>'I'";
$referer_name=" CASE
WHEN c.referer LIKE '%location=google%' THEN 'google'
WHEN c.referer LIKE '%your-domain-name%' THEN 'your-domain-name'
WHEN c.referer LIKE '%msn%' THEN 'msn'
WHEN c.referer LIKE '%yahoo%' THEN 'yahoo'
WHEN c.referer='' THEN ''
ELSE 'other' END AS referer_name";
if($_GET["login"]){
$login=html_entity_decode($_GET["login"]);
$query="select orderid,firstname,lastname,total,FROM_UNIXTIME(date,'%b %d, %Y %h:%i %p') as date from xcart_orders o where login='$login' AND " .$search_condition;
$smarty->assign("data", create_array($query));
$result=func_query_first("SELECT sum(total) as total,count(*) as cnt FROM xcart_orders o WHERE login='$login' AND $search_condition");
$orders_total=$result['total'];
$orders_count=$result['cnt'];
}else{
//(select max({$sql_tbl[orders].date} from {$sql_tbl[orders]} where $search_condition) as last_order,
$query="SELECT o.login,sum(o.total) as total,@total:=ifnull(@total,0)+sum(o.total) as orders_total,count(*)as cnt,
o.orderid,
FROM_UNIXTIME((select max(o2.date)
from xcart_orders o2 where $search_condition AND o2.login=o.login group by o2.login),'%b %d %h:%i %p') as date,
o.b_firstname,o.b_lastname,c.s_city,c.s_state,
o.status,
$referer_name,
c.referer
FROM xcart_orders o
INNER JOIN xcart_customers c USING(login)
WHERE $search_condition
GROUP BY o.login
ORDER BY o.orderid DESC";//cnt desc,total DESC";
$result=func_query_first("SELECT sum(total) as total,count(*) as cnt FROM xcart_orders o WHERE $search_condition");
$orders_total=$result['total'];
$orders_count=$result['cnt'];
$smarty->assign("data", create_array($query));
$search_condition = " c.first_login>='".($start_date)."'";
$search_condition .= " AND c.first_login<='".($end_date)."'";
$query2="SELECT null,c.firstname as b_firstname,c.lastname as b_lastname,c.s_city,c.s_state,c.login,FROM_UNIXTIME(first_login,'%b %d %h:%i %p') as date,$referer_name,c.referer FROM xcart_customers c LEFT JOIN xcart_orders USING(login) WHERE $search_condition AND xcart_orders.orderid IS NULL ORDER BY c.first_login DESC";
$smarty->assign("users", create_array($query2));
}
$smarty->assign("orders_total",$orders_total);
$smarty->assign("orders_count",$orders_count);
@include $xcart_dir."/modules/gold_display.php";
//$smarty->assign("test", $query);
func_display("admin/home.tpl",$smarty);
?>
Create admin/sales_report.php with the code below:
Code:
<?php
/*****************************************************************************\
OLJAI
\*****************************************************************************/
include "../include/sales_report.php";
?>
Open include/func.php and add the following code somewhere:
Code:
function create_array($query)
{
$result= mysql_query($query);
$array_final = array();
while ($query_result= mysql_fetch_array($result))
array_push($array_final, $query_result);
return ($array_final);
}
Create skin1/mow/sales_report.tpl
Code:
{literal}
<style>
.submitLink {
color: #00f;
background-color: transparent;
text-decoration: underline;
border: none;
cursor: pointer;
cursor: hand;
}
</style>
{/literal}
<form name="report" method="post" action="{$smarty.server.REQUEST_URI}">
<input type="radio" name="lastday" value="0" {if $smarty.post.lastday=="0" || $smarty.post.lastday==""}checked{/if}> Today
<input type="radio" name="lastday" value="1" {if $smarty.post.lastday==1}checked{/if}> Last 1 day
<input type="radio" name="lastday" value="7" {if $smarty.post.lastday==7}checked{/if}> Last 7 day
<input type="radio" name="lastday" value="30" {if $smarty.post.lastday==30}checked{/if}> Last 30 day
<input type="radio" name="lastday" value="365" {if $smarty.post.lastday==365}checked{/if}> Last 365 day
<input type="submit" style="margin-top:5px">
<div style="line-height:10px"></div>
{if $smarty.get.login}
{include file="mow/sales_reports_user_orders.tpl"}
{else}
{if $orders_total>0}
There are {$orders_count} orders.
{/if}
<table>
{if $orders_total>0}
{include file="mow/sales_report_table.tpl" data=$data nodata="There are no orders."}
<tr><td>TOTAL</td><td></td><td align="right" >${$orders_total}</td>
<td></td><td></td></tr>
</table>
{else}There are no orders for the date range you selected.
{/if}
<h2>NEW USERS WITH NO ORDER</h2>
<table>
{include file="mow/sales_report_table.tpl" data=$users nodata="No new users that haven't ordered."}
</table>
{/if}
</form>
Create skin1/mow/sales_report_table.tpl
Code:
{section name="id" loop=$data}
{if $smarty.section.id.first}
<tr>
<th align="left" width="160px">Login</th>
<th style="padding-right:15px">Name</th>
<th>Total</th>
<th>Count</th>
<th>Last Order</th>
<th>Last
Order #</th>
<th>Referer</th>
<th>Status</th>
</tr>
{/if}
<tr>
<td>
<input style="padding-left:0px; padding-right:10px" onClick="document.report.action='mow_report.php?login={$data[id].login|htmlentities}'" title="{$data[id].login}" type="submit" class="submitLink" value="{$data[id].login}">
<input name='orderid' type='hidden' value='{$data[id].login}' />
</td>
<td>{$data[id].b_firstname} {$data[id].b_lastname}</td>
<td align="right">{if $data[id].total}${/if}{$data[id].total}</td>
<td align="center">{$data[id].cnt}</td>
<td width="105">{$data[id].date}</td>
<td align="center">{$data[id].orderid}</td> <td>{$data[id].referer_name}</td>
<td align="center">{$data[id].status}</td>
</tr>
{sectionelse}
<tr>
<td>{$nodata}</td>
</tr>
{/section}
Create skin1/mow/sales_reports_user_orders.tpl
Code:
<table>
{section name="id" loop=$data}
{if $smarty.section.id.first}
<tr><td colspan="3"> Profile: {$data[id].firstname} {$data[id].lastname}
<div style="line-height:10px"></div>
</td></tr>
<tr>
<th align="right" width="45">Order #</th><th width="65" align="center">Total</th><th>Order Date</th></tr>
{/if}
<tr>
<td align="right">{$data[id].orderid}</td>
<td align="right" style="padding-right:15px" width="90">${$data[id].total}</td>
<td align="right">{$data[id].date}</td>
</tr>
{if $smarty.section.id.last}
<tr>
<td>TOTAL</td><td align="right" style="padding-right:15px">${$orders_total}</td>
<td></td></tr>
{/if}
{sectionelse}
<tr><td colspan="3"> Profile: {$smarty.get.login}
<div style="line-height:10px"></div>
</td></tr>
<tr><td colspan="3">There are no orders yet for this user.</td></tr>
{/section}
</table>
Open skin1/common_tenplates.tpl and add the following condition somewhere in the if condition:
Code:
{elseif $main eq "sales_report"}
{include file="mow/sales_report.tpl"}
Open skin1/admin/menu.tpl and add the following code before {/capture} tag:
Finally login to admin area and click on "Sales Report" or browse admin/sales_report.php
http://minaw.com/files/sales_report.jpg |
