Databases
StackOverflow2013
Postsdbo
POCreating Annual Sales Tax Reports using MySQL based on a Magento 1.4.1.0 store
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCreating Annual Sales Tax Reports using MySQL based on a Magento 1.4.1.0 store
    primarykey
    data
    text
    <p>Our client has a Magento-powered store and needs the ability to create tax reports based on a specified date-range with a break down of how many much was refunded via credits. </p> <p>Essentially they want a table that <a href="http://www.widgetsandburritos.com/test/numbers.png" rel="nofollow">looks like this</a>.</p> <p>To save time, and since they only need these reports twice a year, I really don't want to build it within Magento. Instead, I'm opting to just create a script to search through the MySQL tables based on the date range. I constructed what I thought should be a query to gather the necessary numbers from the database. Since they only charge sales tax in the state of Texas, I grouped the taxed and non-taxed pieces together using the boolean expression: fi.tax_amount > 0. I'm referencing the following tables in my query:</p> <ol> <li>mag_sales_flat_order: used to indicate order date ranges (identified as fo in MySQL query)</li> <li>mag_sales_flat_invoice: table containing all the actual invoice data (identified as fi in MySQL query)</li> <li>mag_sales_flat_creditmemo: table containing all the credit memo data (identified as fc in MySQL query)</li> </ol> <p>Most of the fields seem to be okay, but I keep getting numbers showing that the credits for shipping tax exceed the shipping tax for the gross. Perhaps I'm using the wrong fields in my query or doing something else incorrectly. Here is the query I'm using:</p> <pre><code>SELECT fi.tax_amount &gt; 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_order` fo ON fo.entity_id = fi.order_id LEFT JOIN `mag_sales_flat_creditmemo` fc ON fc.order_id = fi.order_id WHERE fo.created_at &lt;= '2010-12-31 00:00:00' AND fo.created_at &gt;= '2010-07-01 00:00:00' GROUP BY fi.tax_amount &gt; 0 </code></pre> <p>Does anybody have any clue as to what I'm doing wrong? Let me know if you need any more information.</p> <p>Thanks!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBen Brocka
    1. USDavid Stinemetze
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COtry to visualize the report for us so we could suggest a proper way to handle it
      singulars
      1. POCreating Annual Sales Tax Reports using MySQL based on a Magento 1.4.1.0 store
      1. USAnton S
    2. COWell I did when I referenced this: http://www.widgetsandburritos.com/test/numbers.png, but that said, I figured out my problem. There were a few issues with the query, but the main problem was I had mixed up the order on how I was doing a calculation after I had the number. Thanks for the help though.
      singulars
      1. POCreating Annual Sales Tax Reports using MySQL based on a Magento 1.4.1.0 store
      1. USDavid Stinemetze
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload