Building a report that shows the total tax withheld over a time period

I think I can handle this for a simple tax like GST, though I guess there's going to be more complicated tax situations where totals for both VAT and GST need to be calculated.

I'll have a go and see what I can come up with ...

Quick and nasty starting point for anyone desperate:

function commerce_reports_tax_menu() {
  $items = array();
  
  $items['admin/commerce/reports/tax'] = array(
    'title' => 'Tax reporting',
    'description' => 'View reports about your store.',
    'page callback' => 'commerce_reports_tax_page',
    'access arguments' =>  array('access reports'),
  );
  
  return $items;
}


function commerce_reports_tax_page() {

  $orders = entity_load('commerce_order', FALSE);  
  $tax_summary = array();
  $total = 0;

  foreach ($orders as $order) {
    $wrapper = entity_metadata_wrapper('commerce_order', $order);
    $data = $wrapper->commerce_order_total->data->value();
    $components = array();
    foreach ($data['components'] as $value) {
      $components[$value['name']] = $value['price']['amount'];
    }
    
    if (isset($components['tax|gst'])) {
      $tax_summary[] = array('order' => $wrapper->order_id->value(), 'tax' => $components['tax|gst']);
      $total += $components['tax|gst'];
    }
  }
  
  $headers = array('Order ID', 'Total');

  $out = theme('table', array('header' => $headers, 'rows' => $tax_summary));
  $out .= '<p>$'. number_format($total / 100, 2) . '</p>'; // this is really lazy
  
  return $out;
}

I'll add batching once I get a better feeling for what's going to be required, I've added in the EFQ for completed orders:

function commerce_reports_tax_page() {

  // Load all completed orders in the system
  $query = new EntityFieldQuery;
  $result = $query
    ->entityCondition('entity_type', 'commerce_order')
    ->propertyCondition('status', 'completed')
    ->execute();
  $orders = entity_load('commerce_order', array_keys($result['commerce_order']));
  
  // Iterate the orders and break each order down into its components
  $tax_summary = array();
  $headers = array('order_id' => t('Order ID'));
  $total = 0;
  foreach ($orders as $order) {
    $wrapper = entity_metadata_wrapper('commerce_order', $order);
    $data = $wrapper->commerce_order_total->data->value();

    $components = array('order_id' => $wrapper->order_id->value());
    foreach ($data['components'] as $value) {
      $headers[$value['name']] = $value['name'];
      $components[$value['name']] = $value['price']['amount'];
    }
    $tax_summary[] = $components;
  }

  return theme('table', array('header' => $headers, 'rows' => $tax_summary));
}

This generates us something like the attached image which will suffice for my needs, but I'll need to do a little more exploring with other tax types I guess. Any input from people already dealing with VAT / PST / GST?

The module is now working (though the dynamic schema is a bit of a problem, you might need to create a column for each tax type). It's integrated with views allowing you to create your own reports easily, http://drupal.org/project/commerce_reports

I spoke to Paul before creating the project and we decided to go this route, I've added him to the list of admins for commerce_reports so when he gets some free time he's going to merge in his code (sometime this week was the goal). The "Commerce Reports" name is taken by Paul's sandbox project, so hopefully when Paul merges in his code he can remove the sandbox project and I can change the name.