'admin/store/reports/tax', 'title' => t('Sales tax report'), 'description' => t('View report on sales tax'), 'callback' => 'uc_tax_report_report_page', 'access' => user_access('view reports'), 'type' => MENU_NORMAL_ITEM, ); return $items; } // Displays the sales tax report form and table. function uc_tax_report_report_page($start_date = NULL, $end_date = NULL, $status = NULL) { $timezone = _uc_reports_timezone_offset(); $timezone_offset = time() + $timezone; $format = variable_get('uc_date_format_default', 'm/d/Y'); // Use default report parameters if we don't detect values in the URL. if ($start_date == '') { $args = array( 'start_date' => gmmktime(0, 0, 0, gmdate('n', $timezone_offset), 1, gmdate('Y', $timezone_offset) - 1), 'end_date' => time(), 'status' => FALSE, ); } else { $args = array( 'start_date' => $start_date, 'end_date' => $end_date, 'status' => explode(',', urldecode($status)), ); } // Pull the order statuses into a SQL friendly array. if ($args['status'] === FALSE) { $order_statuses = _uc_reports_order_statuses(); } else { $order_statuses = "('". implode("', '", $args['status']) ."')"; } // Build the header for the report table. $header = array(t('Tax Name'), t('Jurisdiction'), t('Tax rate'), t('Total taxable amount'), t('Total tax collected')); $rows = array(); $csv_rows = array(); $csv_rows[] = $header; // Query to get the tax line items in this date range $result = db_query("SELECT ucoli.amount, ucoli.title, ucoli.data FROM {uc_orders} ucord LEFT JOIN {uc_order_statuses} ON order_status_id = order_status LEFT JOIN {uc_order_line_items} ucoli ON ucord.order_id = ucoli.order_id WHERE %d <= created AND created <= %d AND order_status IN $order_statuses AND ucoli.type = 'tax'", $args['start_date'], $args['end_date']); // add up the amounts by jurisdiction $totals = array(); $no_meta_totals = array(); while($item = db_fetch_object($result)) { $name = trim($item->title); $amount = floatval($item->amount); // get the meta-data out of the serialized array $data = unserialize($item->data); $jurisdiction = trim($data['tax_jurisdiction']); $taxable_amount = floatval($data['taxable_amount']); $rate = floatval($data['tax_rate']); // make a line item in the report for each name/jurisdiction/rate $key = strtolower($name) . strtolower($jurisdiction) . number_format($rate, 5); if(!empty($jurisdiction) && $amount && $taxable_amount) { // we have meta-data if(empty($totals[$key])) { $totals[$key] = array( 'name' => $name, 'jurisdiction' => $jurisdiction, 'rate' => $rate, 'taxable_amount' => $taxable_amount, 'amount' => $amount, ); } else { $totals[$key]['taxable_amount'] += $taxable_amount; $totals[$key]['amount'] += $amount; } } elseif ($amount) { // Old data: no meta-data was stored. Just report the amount collected. if(empty($no_meta_totals[$key])) { $no_meta_totals[$key] = array( 'name' => $name, 'amount' => $amount, ); } else { $no_meta_totals[$key]['amount'] += $amount; } } } // sort and make this into a report ksort($totals); ksort($no_meta_totals); $taxable_amount = 0; $amount = 0; $star_legend = ''; foreach($totals as $line) { $row = array( $line['name'], $line['jurisdiction'], number_format($line['rate'] * 100, 3) . '%', uc_currency_format($line['taxable_amount']), uc_currency_format($line['amount']), ); $rows[] = $row; $csv_rows[] = $row; $taxable_amount += $line['taxable_amount']; $amount += $line['amount']; } foreach($no_meta_totals as $line) { $row = array( $line['name'], '*', '*', '*', uc_currency_format($line['amount']), ); $rows[] = $row; $csv_rows[] = $row; $amount += $line['amount']; // We have at least one no-meta-data line. Explain why. $star_legend = t('* No information on jurisdiction, tax rate, or taxable amount is available for this line.'); } // Add a totals line. $row = array( t('Total'), '', '', uc_currency_format($taxable_amount), uc_currency_format($amount), ); $rows[] = $row; $csv_rows[] = $row; // Cache the CSV export. $csv_data = uc_reports_store_csv('uc_tax_report', $csv_rows); // Build the page output holding the form, table, and CSV export link. $output = drupal_get_form('uc_tax_report_params_form', $args, $args['status']); $output .= theme('table', $header, $rows, array('width' => '100%', 'class' => 'uc-sales-table')); if ($star_legend) { $output .= '
' . $star_legend . '