Report: Improved Daily Performance v0.1

[othellotech]

Never one to like anything in the exact format it arrives

 

Have started making some modifications to the reports - 1st job on the DP Report is to add Totals ...

 

Add the following *before* the for ($day =1 line

 

// Zero Totals
$total_neworders        = 0;
$total_newaccounts      = 0;
$total_newinvoices      = 0;
$total_paidinvoices     = 0;
$total_cancellations    = 0;
$total_newtickets       = 0;

 

Add the following *after* the reportdata["tablevalues"][] line

 

       // Increment Totals
       $total_neworders        += $neworders;
       $total_newaccounts      += $newaccounts;
       $total_newinvoices      += $newinvoices;
       $total_paidinvoices     += $paidinvoices;
       $total_cancellations    += $cancellations;
       $total_newtickets       += $newtickets;

 

add the following before the while ($data line ...

$reportdata["tablevalues"][] = 
array("Monthly Total","$total_neworders","$total_newaccounts","$total_newinvoices",
"$total_paidinvoices","$total_newtickets","$total_cancellations");

 

 

Coming in stage 2 ...

 

removal of the (suspected) superfluous (while $data) loop

 

And for stage 3 ....

 

totals of coupons redeemed

[othellotech]

Ok, for stage 2 you can just delete the entire while block as completely irrelevant (probably accidentally copied from another report at some earlier time)

 

for stage 3 you ...

change the report heading

$reportdata["tableheadings"] = array("Date","New Orders","New Accounts","New Invoices","Paid Invoices","New Tickets","Cancellations","Promotions");

 

another total - $total_promocodes to the zero and addition sections

$total_promocodes       = 0;

and

       $total_promocodes       += $promocodes;

 

and the following query after the cancellations query ...

       $query = "SELECT COUNT(*) FROM tblorders WHERE `date` LIKE '$date%' AND `promocode` NOT IN ('')";
       $result = mysql_query($query);
       $data = mysql_fetch_array($result);
       $promocodes = $data[0];

 

c'est voila an extra column and a total row - what more could you possibly require in life !

[Santo]

Hi,

 

Once again, thank you!

 

I think you missed a change on this line:

 

	$reportdata["tablevalues"][] = array(fromMySQLDate($date),"$neworders","$newaccounts","$newinvoices","$paidinvoices","$newtickets","$cancellations");

 

Should read:

 

	$reportdata["tablevalues"][] = array(fromMySQLDate($date),"$neworders","$newaccounts","$newinvoices","$paidinvoices","$newtickets","$cancellations","$promocodes");

 

and also this line:

 

$reportdata["tablevalues"][] =
array("Monthly Total","$total_neworders","$total_newaccounts","$total_newinvoices",
"$total_paidinvoices","$total_newtickets","$total_cancellations"); 

 

should read:

 

$reportdata["tablevalues"][] =
array("Monthly Total","$total_neworders","$total_newaccounts","$total_newinvoices",
"$total_paidinvoices","$total_newtickets","$total_cancellations","$promocodes"); 

 

Cheers!

[Joweb]

I have tried this my Monthly totals are all 0 does not seem to add them up.

[DavidJ]

Same, all zeros now. Oops!

[othellotech]

if you're seeing zero's/missing totals then you've made a typo

 

here's our working file in full, compare it to yours....

 

<?php

$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"] = "Daily Performance for ".$months[$month-1]." ".$year;
$reportdata["description"] = "This report shows a daily activity summary for a given month.";

$reportdata["tableheadings"] = array("Date","New Orders","New Accounts","New Invoices","Paid Invoices","New Tickets","Cancellations","Promotions");

// Zero Totals
$total_neworders 	= 0;
$total_newaccounts 	= 0;
$total_newinvoices 	= 0;
$total_paidinvoices 	= 0;
$total_cancellations	= 0;
$total_newtickets 	= 0;
$total_promocodes	= 0;

for ( $day = 1; $day <= 31; $day += 1) 
{
$date = $year."-".str_pad($month,2,"0",STR_PAD_LEFT)."-".str_pad($day,2,"0",STR_PAD_LEFT);

$query = "SELECT COUNT(*) FROM tblorders WHERE `date` LIKE '$date%'";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$neworders = $data[0];

$query = "SELECT COUNT(*) FROM tblhosting WHERE `regdate`='$date'";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$newaccounts = $data[0];

$query = "SELECT COUNT(*) FROM tbldomains WHERE `registrationdate`='$date'";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$newaccounts += $data[0];

$query = "SELECT COUNT(*) FROM tblinvoices WHERE `date`='$date'";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$newinvoices = $data[0];

$query = "SELECT COUNT(*) FROM tblinvoices WHERE `datepaid` LIKE '$date%'";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$paidinvoices = $data[0];

$query = "SELECT COUNT(*) FROM tbltickets WHERE `date` LIKE '$date%'";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$newtickets = $data[0];

$query = "SELECT COUNT(*) FROM tblcancelrequests WHERE `date` LIKE '$date%'";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$cancellations = $data[0];

$query = "SELECT COUNT(*) FROM tblorders WHERE `date` LIKE '$date%' AND `promocode` NOT IN ('')";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$promocodes = $data[0];

$reportdata["tablevalues"][] = array(fromMySQLDate($date),"$neworders","$newaccounts","$newinvoices","$paidinvoices","$newtickets","$cancellations","$promocodes");

// Increment Totals
$total_neworders 	+= $neworders;
$total_newaccounts 	+= $newaccounts;
$total_newinvoices 	+= $newinvoices;
$total_paidinvoices 	+= $paidinvoices;
$total_cancellations	+= $cancellations;
$total_newtickets 	+= $newtickets;
$total_promocodes	+= $promocodes;

}

$reportdata["tablevalues"][] = array("Monthly Total","$total_neworders","$total_newaccounts","$total_newinvoices","$total_paidinvoices","$total_newtickets","$total_cancellations","$total_promocodes");

//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="-";
//	}
//	$reportdata["tablevalues"][] = array("$id","$ordernum","$date","$clientname","$amount","$promo","$paymentmethod","$status");
//}

$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>";

?>

[Joweb]

Thanks That did it.

[DavidJ]

Ah, flawless now.

I'm curious -- I'm trying to take the monthly_orders.php page & get a 'total' amount from it.

 

I'm not having any luck, I'm sure it's something you could figure out in 2 seconds. I'm no good with code!

 

Edit: Nevermind, I stole the information from the monthly transaction's "balance" page. <3

[Joweb]

Same here trying to set up totals on new admin template to show this

Monthly: $0.00 (0)

Quarterly: $0.00 (0)

Semi-Annually: $0.00 (0)

Annually: $0000.00 (0)

Biennially: $0.00 (0)

Est. Annual Income: $0000.00

(Excluding Domains)

 

but not having any luck any Idea

[othellotech]

Same here trying to set up totals on new admin template to show this

Monthly: $0.00 (0)

Quarterly: $0.00 (0)

Semi-Annually: $0.00 (0)

Annually: $0000.00 (0)

Biennially: $0.00 (0)

Est. Annual Income: $0000.00

(Excluding Domains)

 

You can get example code to do all of that from the reports

[Joweb]

Not sure how to pull from reports correctly can you give an example?

[othellotech]

Not sure how to pull from reports correctly can you give an example?

 

Just look at the files in modules/reports - all the sql/php code you need is in there.

[Joweb]

I am not sure that my syntax is correct I get this () but no amounts Not very good with queries

[G Swanepoel]

Hi There,

 

I am asking for some help please. I looked at the reporting and I am trying to make a custom report, but i do not know php and as a result i am not getting amywhere.

 

All I need is a report for my VAT (Value Added Tax) that is due every second month.

 

I would need a report that looks something like this:

 

 

I would have to be able to select the month I want the report for.

 

Can anyone please help me????

 

Regards,

 

Gert