Ordered Products Monthly Report

yabado · September 25, 2007

I wanted to know what the customers were ordering, along with the totals.

So I changed the Monthly Reports to show me that.

What do you think?


<?php

// Added by yabado 9-24-07
$hostcnt=0;
$hostdomcnt=0;
$domcnt=0;


$months = array('January','February','March','April','May','June','July','August','September','October','November','December');

if ($month=="") {
$month=date("m");
$year=date("Y");
}

$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);  

$reportdata["title"] = "New Orders for ".$months[$month-1]." ".$year;
$reportdata["description"] = "This report shows all new orders for a given month";

$query = "SELECT tblorders.*,tblclients.firstname,tblclients.lastname,tblpaymentgateways.value FROM tblorders INNER JOIN tblclients ON tblclients.id=tblorders.userid INNER JOIN tblpaymentgateways ON tblpaymentgateways.gateway=tblorders.paymentmethod WHERE tblpaymentgateways.setting='name' AND date like '$year-$pmonth%' ORDER BY date ASC";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

$reportdata["headertext"] = "Total New Orders: $num_rows";

// Added Prodyct to headers -yabdabo
$reportdata["tableheadings"] = array("Order ID","Order #","Order Date","Client","Product","Amount","Promotion Code","Payment Method","Status");

while ($data = mysql_fetch_array($result)) {
$id = $data["id"];
$ordernum = $data["ordernum"];
$userid = $data["userid"];
$date = $data["date"];
$amount = $CONFIG["CurrencySymbol"].$data["amount"];
$promo = $data["promocode"];
$paymentmethod = $data["value"];
$status = $data["status"];
$date = fromMySQLDate($date);
$clientname = $data["firstname"]." ".$data["lastname"];
if ($promo=="") {
	$promo="-";


// Add by yabado 9-24-07
$hostingid = $data['hostingid'];
$domainids = $data['domainids'];

// todo: explode domainids to show multiple links if needed.

if($hostingid && !$domainids){	
	$product = "<a href=\"clientshosting.php?userid=$userid&hostingid=$hostingid\" target=\"_blank\" />Hosting Only</a>";
	$hostcnt++;
	$hosttotal += $data['amount'];
}else if($hostingid && $domainids){
	$product = "<a href=\"clientshosting.php?userid=$userid&hostingid=$hostingid\" target=\"_blank\" />Hosting/Domain ( ".$data['domaintype']." )</a>";
	$hostdomcnt++;
	$hostdomtotal += $data['amount'];
}else if(!$hostingid && $domainids){
	$product = "<a href=\"clientsdomains.php?userid=$userid&domainid=$domainids\" target=\"_blank\" />Domain ( ".$data['domaintype']." )</a>";
	$domcnt++;
	$domaintotal += $data['amount'];
}else{
	$product ="unknown";

}

$grandtotal += $data['amount'];

} //<--end of while

// Added $product to results -yabdao
$reportdata["tablevalues"][] = array("$id","$ordernum","$date","$clientname","$product","$amount","$promo","$paymentmethod","$status");
}

// Added by yabado 9-24-07
$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"]."$grandtotal</span>
";
$data['footertext'] .= "[b]Hosting Only:[/b] $hostcnt ( ".$CONFIG["CurrencySymbol"]."$hosttotal ) | ";
$data['footertext'] .= "[b]Hosting/Domain:[/b] $hostdomcnt ( ".$CONFIG["CurrencySymbol"]."$hostdomtotal ) | ";
$data['footertext'] .= "[b]Domain Only:[/b] $domcnt ( ".$CONFIG["CurrencySymbol"]."$domaintotal ) </p>";

$data["footertext"].="<table width=90% align=center><tr><td>";
if ($month=="1") {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=12&year=".($year-1)."\"><< December ".($year-1)."</a>";
} else {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month-1)."&year=".$year."\"><< ".$months[($month-2)]." $year</a>";
}
$data["footertext"].="</td><td align=right>";
if ($month=="12") {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=1&year=".($year+1)."\">January ".($year+1)." >></a>";
} else {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month+1)."&year=".$year."\">".$months[(($month+1)-1)]." $year >></a>";
}
$data["footertext"].="</td></tr></table>";

?>

impactgc · September 25, 2007

I put it up and looks great!.. nice added columns and totals!

Great job!

Adam

disenioweb · September 29, 2007

Very nice, great the only thing i founded is that if the user ordered or you added an addon, it shows as "unknown" in colum "Product"

Thanks

serverx · September 30, 2007

how i can use the script? where i have to upload it?

Thanks

impactgc · September 30, 2007

add the file to /modules/reports -- name it whatever with the php extension.. Then look under reports - it should be there

Thanks,

Adam

Tech Entrance · November 11, 2007

Nicely done - thanks

sgpaul · December 26, 2007

I am not sure if recent updates to WHMCS broke this report, but it didn't work on mine, so I fixed it and added a few more features like currency formatting & product name display.


<?php

// Added by yabado 9-24-07


// Modified by sgpaul 12-26-2007

$hostcnt=0;
$hostdomcnt=0;
$domcnt=0;

$orderis = array();
$months = array('January','February','March','April','May','June','July','August','September','October','November','December');

if ($month=="") {
$month=date("m");
$year=date("Y");
}

$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);

$reportdata["title"] = "New Orders for ".$months[$month-1]." ".$year;
$reportdata["description"] = "This report shows all new orders for a given month";

$query = "SELECT tblorders.*,tblclients.firstname,tblclients.lastname,
tblpaymentgateways.value,
tblhosting.id as hostingid, tbldomains.id as domainids, tbldomains.type as domaintype,
tblproducts.name as productname
FROM tblorders INNER JOIN tblclients ON tblclients.id=tblorders.userid 
INNER JOIN tblpaymentgateways ON tblpaymentgateways.gateway=tblorders.paymentmethod 
left JOIN tblhosting ON tblhosting.orderid=tblorders.id
left JOIN tbldomains ON tbldomains.orderid=tblorders.id
left JOIN tblproducts ON tblproducts.id=tblhosting.packageid

WHERE 
tblpaymentgateways.setting='name' AND
date like '$year-$pmonth%' ORDER BY date ASC";
$result = mysql_query($query);

// Added Prodyct to headers -yabdabo
$reportdata["tableheadings"] = array("Order ID","Order #","Order Date","Client","Product","Amount","Promotion Code","Payment Method","Status");

while ($data = mysql_fetch_array($result)) {

$id = $data["id"];

if ( array_search( $id, $orderis ) === false )
{
	$orderis[] = $id;

	$ordernum = $data["ordernum"];
	$userid = $data["userid"];
	$date = $data["date"];
	$amount = $CONFIG["CurrencySymbol"].$data["amount"];
	$promo = $data["promocode"];
	$paymentmethod = $data["value"];
	$status = $data["status"];
	$productname = $data["productname"];
	$date = fromMySQLDate($date);
	$clientname = $data["firstname"]." ".$data["lastname"];
	if ( $promo=="") {
		$promo="-";
	}

	// Add by yabado 9-24-07
	$hostingid = $data['hostingid'];
	$domainids = $data['domainids'];

	// todo: explode domainids to show multiple links if needed.

	if($hostingid && !$domainids){
		$product = "<a href=\"clientshosting.php?userid=$userid&hostingid=$hostingid\" target=\"_blank\" />$productname</a>";
		$hostcnt++;
		$hosttotal += $data['amount'];
	}else if($hostingid && $domainids){
		$product = "<a href=\"clientshosting.php?userid=$userid&hostingid=$hostingid\" target=\"_blank\" />$productname/Domain ( ".$data['domaintype']." )</a>";
		$hostdomcnt++;
		$hostdomtotal += $data['amount'];
	}else if(!$hostingid && $domainids){
		$product = "<a href=\"clientsdomains.php?userid=$userid&domainid=$domainids\" target=\"_blank\" />Domain ( ".$data['domaintype']." )</a>";
		$domcnt++;
		$domaintotal += $data['amount'];
	}else{
		$product ="unknown";

	}

	$grandtotal += $data['amount'];


	// Added $product to results -yabdao
	$reportdata["tablevalues"][] = array("$id","$ordernum","$date","$clientname","$product","$amount","$promo","$paymentmethod","$status");

}

} //<--end of while
$num_rows = count( $orderis );

$reportdata["headertext"] = "Total New Orders: $num_rows";

// Added by yabado 9-24-07
$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"].number_format( $grandtotal, 2 )."</span>
";
$data['footertext'] .= "Hosting Only: $hostcnt ( ".$CONFIG["CurrencySymbol"].number_format( $hosttotal, 2 )." ) | ";
$data['footertext'] .= "Hosting/Domain: $hostdomcnt ( ".$CONFIG["CurrencySymbol"].number_format( $hostdomtotal, 2 )." ) | ";
$data['footertext'] .= "Domain Only: $domcnt ( ".$CONFIG["CurrencySymbol"].number_format( $domaintotal, 2 )." ) </p>";

$data["footertext"].="<table width=90% align=center><tr><td>";
if ($month=="1") {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=12&year=".($year-1)."\"><< December ".($year-1)."</a>";
} else {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month-1)."&year=".$year."\"><< ".$months[($month-2)]." $year</a>";
}
$data["footertext"].="</td><td align=right>";
if ($month=="12") {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=1&year=".($year+1)."\">January ".($year+1)." >></a>";
} else {
$data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month+1)."&year=".$year."\">".$months[(($month+1)-1)]." $year >></a>";
}
$data["footertext"].="</td></tr></table>";

?>

regards,

Pablo

Tech Entrance · March 4, 2008

I am not sure if recent updates to WHMCS broke this report, but it didn't work on mine, so I fixed it and added a few more features like currency formatting & product name display.


<?php

// Added by yabado 9-24-07


// Modified by sgpaul 12-26-2007

$hostcnt=0;
$hostdomcnt=0;
$domcnt=0;

$orderis = array();
$months = array('January','February','March','April','May','June','July','August','September','October','November','December');

if ($month=="") {
   $month=date("m");
   $year=date("Y");
}

$pmonth = str_pad($month, 2, "0", STR_PAD_LEFT);

$reportdata["title"] = "New Orders for ".$months[$month-1]." ".$year;
$reportdata["description"] = "This report shows all new orders for a given month";

$query = "SELECT tblorders.*,tblclients.firstname,tblclients.lastname,
tblpaymentgateways.value,
tblhosting.id as hostingid, tbldomains.id as domainids, tbldomains.type as domaintype,
tblproducts.name as productname
FROM tblorders INNER JOIN tblclients ON tblclients.id=tblorders.userid 
INNER JOIN tblpaymentgateways ON tblpaymentgateways.gateway=tblorders.paymentmethod 
left JOIN tblhosting ON tblhosting.orderid=tblorders.id
left JOIN tbldomains ON tbldomains.orderid=tblorders.id
left JOIN tblproducts ON tblproducts.id=tblhosting.packageid

WHERE 
tblpaymentgateways.setting='name' AND
date like '$year-$pmonth%' ORDER BY date ASC";
$result = mysql_query($query);

// Added Prodyct to headers -yabdabo
$reportdata["tableheadings"] = array("Order ID","Order #","Order Date","Client","Product","Amount","Promotion Code","Payment Method","Status");

while ($data = mysql_fetch_array($result)) {

   $id = $data["id"];

   if ( array_search( $id, $orderis ) === false )
   {
       $orderis[] = $id;

       $ordernum = $data["ordernum"];
       $userid = $data["userid"];
       $date = $data["date"];
       $amount = $CONFIG["CurrencySymbol"].$data["amount"];
       $promo = $data["promocode"];
       $paymentmethod = $data["value"];
       $status = $data["status"];
       $productname = $data["productname"];
       $date = fromMySQLDate($date);
       $clientname = $data["firstname"]." ".$data["lastname"];
       if ( $promo=="") {
           $promo="-";
       }

       // Add by yabado 9-24-07
       $hostingid = $data['hostingid'];
       $domainids = $data['domainids'];

       // todo: explode domainids to show multiple links if needed.

       if($hostingid && !$domainids){
           $product = "<a href=\"clientshosting.php?userid=$userid&hostingid=$hostingid\" target=\"_blank\" />$productname</a>";
           $hostcnt++;
           $hosttotal += $data['amount'];
       }else if($hostingid && $domainids){
           $product = "<a href=\"clientshosting.php?userid=$userid&hostingid=$hostingid\" target=\"_blank\" />$productname/Domain ( ".$data['domaintype']." )</a>";
           $hostdomcnt++;
           $hostdomtotal += $data['amount'];
       }else if(!$hostingid && $domainids){
           $product = "<a href=\"clientsdomains.php?userid=$userid&domainid=$domainids\" target=\"_blank\" />Domain ( ".$data['domaintype']." )</a>";
           $domcnt++;
           $domaintotal += $data['amount'];
       }else{
           $product ="unknown";

       }

       $grandtotal += $data['amount'];


       // Added $product to results -yabdao
       $reportdata["tablevalues"][] = array("$id","$ordernum","$date","$clientname","$product","$amount","$promo","$paymentmethod","$status");

   }

} //<--end of while
$num_rows = count( $orderis );

$reportdata["headertext"] = "Total New Orders: $num_rows";

// Added by yabado 9-24-07
$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"].number_format( $grandtotal, 2 )."</span>
";
$data['footertext'] .= "Hosting Only: $hostcnt ( ".$CONFIG["CurrencySymbol"].number_format( $hosttotal, 2 )." ) | ";
$data['footertext'] .= "Hosting/Domain: $hostdomcnt ( ".$CONFIG["CurrencySymbol"].number_format( $hostdomtotal, 2 )." ) | ";
$data['footertext'] .= "Domain Only: $domcnt ( ".$CONFIG["CurrencySymbol"].number_format( $domaintotal, 2 )." ) </p>";

$data["footertext"].="<table width=90% align=center><tr><td>";
if ($month=="1") {
   $data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=12&year=".($year-1)."\"><< December ".($year-1)."</a>";
} else {
   $data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month-1)."&year=".$year."\"><< ".$months[($month-2)]." $year</a>";
}
$data["footertext"].="</td><td align=right>";
if ($month=="12") {
   $data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=1&year=".($year+1)."\">January ".($year+1)." >></a>";
} else {
   $data["footertext"].="<a href=\"$PHP_SELF?report=$report&month=".($month+1)."&year=".$year."\">".$months[(($month+1)-1)]." $year >></a>";
}
$data["footertext"].="</td></tr></table>";

?>

regards,

Pablo

I use this version and it looks great but it has a bug where it counted fraud orders in the income totals

Can you exclude that or just make it count active orders ?

othellotech · March 6, 2008

Can you exclude that or just make it count active orders ?

Just change the SELECT to have a WHERE clause

Tech Entrance · March 6, 2008

Just change the SELECT to have a WHERE clause

Can you please write the full query ? I'm not a programmer sorry

Lib-Design · March 6, 2008

add something like

AND tblclients.status='Active'

in the WHERE query

Tech Entrance · March 7, 2008

add something like

in the WHERE query

Thanks. but I think it is tblorders not tblclients

Full query here - worked fine for me

$query = "SELECT tblorders.*,tblclients.firstname,tblclients.lastname,
tblpaymentgateways.value,
tblhosting.id as hostingid, tbldomains.id as domainids, tbldomains.type as domaintype,
tblproducts.name as productname
FROM tblorders INNER JOIN tblclients ON tblclients.id=tblorders.userid 
INNER JOIN tblpaymentgateways ON tblpaymentgateways.gateway=tblorders.paymentmethod 
left JOIN tblhosting ON tblhosting.orderid=tblorders.id
left JOIN tbldomains ON tbldomains.orderid=tblorders.id
left JOIN tblproducts ON tblproducts.id=tblhosting.packageid

WHERE 
tblpaymentgateways.setting='name' AND
date like '$year-$pmonth%' AND tblorders.status='Active' ORDER BY date ASC";
$result = mysql_query($query);

Lib-Design · March 7, 2008

ah, i just had a quick look at the Database setup when i wrote that soz saw an ACtive and inactive enum in there and went that'll be it lol

Tech Entrance · March 7, 2008

ah, i just had a quick look at the Database setup when i wrote that soz saw an ACtive and inactive enum in there and went that'll be it lol

No problem. you really helped me know where to look :-P

Ordered Products Monthly Report

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Recently Browsing 0 members

Important Information