Annual Tax Reports based on a Magento 1.4.1.0+ store.
// January 3rd, 2011 // Programming, Web Development
I had a client request for a more detailed annual tax report in Magento 1.4.1.0. They wanted to break down all their income, between taxable and non-taxable regions, with appropriate annotations of how many adjustments were made based on refunds/credits.
Because the client was only using this a few times a year, I didn’t want to waste too much time writing it as an actual Magento extension, as that can become time consuming. Instead I just wrote a separate PHP script to traverse the MySQL database and perform the necessary calculations.
Since this specific client only charged sales tax to 1 state, I cheated a bit by just splitting it into 2 groups: taxed and non-taxed. If you charge sales-tax in multiple states, then you may need to alter this solution to fit your needs.
-
<?php
-
-
// set MySQL connection info
-
-
// quick reference array
-
1 => ‘January’,
-
2 => ‘February’,
-
3 => ‘March’,
-
4 => ‘April’,
-
5 => ‘May’,
-
6 => ‘June’,
-
7 => ‘July’,
-
8 => ‘August’,
-
9 => ‘September’,
-
10 => ‘October’,
-
11 => ‘November’,
-
12 => ‘December’
-
);
-
-
// determine today’s date and split it into tokens
-
-
// if a date has been posted, then use that value, otherwise, use the default value.
-
// note: using intval as a quick way to prevent sql injection. if i force the input to be an integer, then nobody can insert pesky symbols in there to screw with my db
-
-
-
// begin HTML portion
-
?>
-
<html>
-
<head>
-
<title>Magento: Online Sales Tax Report</title>
-
<style type="text/css">
-
table { margin-top: 25px; border: 1px solid #000; }
-
td { padding: 3px 20px; }
-
th { padding: 5px 20px; border-bottom: 1px solid #000; background: #e8e8e8; }
-
</style>
-
</head>
-
<body>
-
<h2>Magento: Online Sales Tax Report</h2>
-
-
<form method="post" action="sales_tax_report.php">
-
<b>Start Date:</b>
-
<select name="start_month">
-
<?php for ($i=1; $i<=12; $i++) {?>
-
<?php } ?>
-
</select>
-
-
<select name="start_day">
-
<?php for ($i=1; $i<=31; $i++) { ?>
-
<?php } ?>
-
</select>
-
-
<select name="start_year">
-
<?php }?>
-
</select>
-
-
-
-
-
<b>End Date:</b>
-
<select name="end_month">
-
<?php for ($i=1; $i<=12; $i++) {?>
-
<?php } ?>
-
</select>
-
-
<select name="end_day">
-
<?php for ($i=1; $i<=31; $i++) { ?>
-
<?php } ?>
-
</select>
-
-
<select name="end_year">
-
<?php }?>
-
</select>
-
-
-
<input type="submit" value="Generate Report" />
-
</form>
-
-
<?php
-
// if we’ve come here through a post, then run the query
-
// specify our date ranges
-
-
$query = "SELECT count(*) as order_total, fi.tax_amount > 0 as `taxed`, sum(fi.grand_total) as `sales_total`, sum(fi.tax_amount) as `sales_tax`, sum(fi.shipping_tax_amount) as `shipping_tax`, sum(fc.grand_total) as `credit_total`, sum(fc.tax_amount) as `credit_sales_tax`, sum(fc.shipping_tax_amount) as `credit_shipping_tax` FROM `mag_sales_flat_invoice` fi LEFT JOIN `mag_sales_flat_creditmemo` fc ON fc.order_id = fi.order_id LEFT JOIN `mag_sales_flat_order` fo ON fo.entity_id = fi.order_id WHERE fo.created_at <= ‘$end’ AND fo.created_at >= ‘$start’ GROUP BY fi.tax_amount > 0";
-
-
-
// output query results
-
print ‘<table cellspacing="0" cellpadding="0">’;
-
print "<tr><th>Region</th><th> </th><th>Sales</th><th>Shipping</th><th>State Tax</th><th>Total</th></tr>\n";
-
print ‘<tr>’;
-
print ‘<td>Gross</td>’;
-
print ‘</tr>’;
-
print ‘<tr>’;
-
print ‘</tr>’;
-
print ‘<td> </td><td><b>Total</b></td>’;
-
print ‘<td><b>$’.number_format($row[‘shipping_tax’]+$row[‘sales_tax’]-$row[‘credit_shipping_tax’]-$row[‘credit_sales_tax’],2).‘</b></td>’;
-
print ‘</tr>’;
-
}
-
print ‘</table>’;
-
-
}
-
?>
-
-
</body>
-
</html>
-
This is by no means a perfect script by any stretch of the imagination. But for something that’s run a couple times a year, it will do what you need.
