[HOW TO] Coupon Usage Report - PHP Script

All,

Here is a little script I created to show me all active coupons, the amount of times they have been used, the total dollar amount of the orders they were used on and the average order size.

<?php
$hostname_dbConnection = "yourshost";
$database_dbConnection = "yourdb";
$username_dbConnection = "youruser";
$password_dbConnection = "yourpw";
$dbConnection = mysql_connect($hostname_dbConnection, $username_dbConnection, $password_dbConnection) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db('yourdb');

$getcoupons = mysql_query("SELECT distinct(coupon_id) FROM `order`;");
while ($outputcoupons = mysql_fetch_array($getcoupons))
{
$coupon_id = $outputcoupons['coupon_id'];
$getcouponinfo = mysql_query("select * from coupon where coupon_id = $coupon_id") or die(mysql_error());
while($outputcouponinfo = mysql_fetch_array($getcouponinfo))
{
$code = $outputcouponinfo['code'];
$discount = $outputcouponinfo['discount'];
$total = $outputcouponinfo['total'];
$getcouponinfo1 = mysql_query("select * from order where coupon_id = $coupon_id") or die(mysql_error());
$uses = mysql_num_rows($getcouponinfo1);
$amountused = $uses * $discount;
$amountpercoupon = $amountused / $uses;
echo "
<span style='text-decoration:underline; font-weight:bold;'>$code</span><br>
Total Uses: $uses<br>
Sum of Total Orders: ";
$totalorderdollar = mysql_query("select sum(total) from order where coupon_id = $coupon_id") or die(mysql_error());
while($ouputdollarmaount = mysql_fetch_array($totalorderdollar))
{
$totalamount = number_format($ouputdollarmaount['sum(total)'], 2);
echo "$totalamount<br>";
}

$average = number_format($totalamount / $uses, 2);
echo "Average Order Amount: $average<hr>";
}

}

?>

Can you rewrite this for 1.5?? what has changed??

This would be a useful little tool to help with knowing whats been used, can this be used for 1.5.1.3 with some changes made to it by some kind hearted person who knows how to do such a thing?

Klimskady wrote:This would be a useful little tool to help with knowing whats been used, can this be used for 1.5.1.3 with some changes made to it by some kind hearted person who knows how to do such a thing?

Any chance this has been done or anyone have a modified version that does work for 1.5.1.3 as I don't get any reports for any coupon usage in my admin!

I am still looking too...

The coupon report has already been implemented in OpenCart v1.5.1.3 under admin - > reports - > coupons.

Excellent, thanks straightlight, just trying to learn as I go....

straightlight wrote:The coupon report has already been implemented in OpenCart v1.5.1.3 under admin - > reports - > coupons.

Hi straightlight , the thing is - this current cupom report shows only the total amount of the cupom usage. If you try to filter the report per a period of time, it willç bring the "sales report screen" where the CUPOM NAME is not displayed!

So i need to see the cupom usage in a period of time, but the current report dont do this despite it has "periodo selection filter" - this is very very wrong.

We do already have this function on Sales Report, so would be better to filter the Cupom Report as is it, not to redirect to Sales Report if the filter is used!

Well, i think i will have to vqmod this.

By default, no redirection policy should be lead to other routes than it's own from url whenever the reports ard used unless JQuery is used from the template. Which Opencart version are you using ?