A sales report based on users

metinex · #1 08-01-2006, 07:44 PM

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:

Code:

Sales Report

Finally login to admin area and click on "Sales Report" or browse admin/sales_report.php

http://minaw.com/files/sales_report.jpg

Audiolines · #2 08-02-2006, 08:28 AM

how do we run this file?

Audiolines · #3 08-02-2006, 08:31 AM

when i manually run it i get a blank screen

metinex · #4 08-02-2006, 11:12 AM

You need to create mow folder under skin1 along with 3 .tpl files.

Create your .php file under admin folder along with included .php file under include folder.

You also edit the admin menu to include a link. That's it.

Audiolines · #5 08-02-2006, 11:23 AM

a link to which .php file?

Audiolines · #6 08-02-2006, 11:28 AM

just get a blank page.

copied yoru code exactly

metinex · #7 08-02-2006, 11:33 AM

http://yourwebsite.com/admin/sales_report.php after you login to admin area.

Audiolines · #8 08-02-2006, 11:38 AM

hmmm, totally confused. still just blank pages

im not sure where the problem is coming up, as i am not that sql savvy.

any ideas?

metinex · #9 08-06-2006, 04:40 AM

I'm sorry, but I think I forgot to mention one of the important things:

Open skin1/common_tenplates.tpl and add the following condition somwhere in the if condition:

{elseif $main eq "sales_report"}
{include file="mow/sales_report.tpl"}

banzai · #10 08-07-2006, 05:01 AM

Hi,

I get the following error when executing the script:

Fatal error: Call to undefined function: create_array() in /mysite/include/sales_report.php on line 59

the script stops at this line: $smarty->assign("data", create_array($query));

any ideas what might be causing this?

thank you
Olaf