AeliaWCEU_VAT_AssistantReportsBase_Sales_Report

Renders the report containing the EU VAT for each country in a specific period.

Defined (1)

The class is defined in the following location(s).

/src/lib/classes/reporting/reports/base/base_sales_report.php  
  1. abstract class Base_Sales_Report extends \Aelia\WC\EU_VAT_Assistant\Reports\Base_Report { 
  2. // @var string Indicates which salkes region should be inclued (EU, non-EU, all) 
  3. protected $sales_region_visible; 
  4. // @var string Indicates which exchange rates should be used (stored with order or ECB). 
  5. protected $exchange_rates_to_use; 
  6.  
  7. /** 
  8. * Indicates if the tax passed as a parameter should be skipped (i.e. excluded 
  9. * from the report). 
  10. * @param array tax_details An array of data describing a tax. 
  11. * @return bool True (tax should be excluded from the report) or false (tax 
  12. * should be displayed on the report). 
  13. */ 
  14. protected function should_skip($sale_data) { 
  15. return false; 
  16.  
  17. // If all taxes should be displayed, just return "false" (i.e. don't skip) 
  18. if(($this->sales_region_visible === Definitions::ALL) && 
  19. ($this->sales_with_vat_visible === Definitions::ALL)) { 
  20. return false; 
  21.  
  22. $eu_vat_evidence = maybe_unserialize($data->_eu_vat_evidence); 
  23.  
  24. // If the tax is a "non-EU" one, it should be skipped when the sales region 
  25. // to display are "EU only" 
  26. if($eu_vat_evidence['location']['is_eu_country'] == false) { 
  27. return ($this->sales_region_visible === Definitions::SALES_EU_ONLY); 
  28. else { 
  29. return ($this->sales_region_visible === Definitions::SALES_NON_EU_ONLY); 
  30.  
  31. /** 
  32. * Indicates if reports should be rendered using the exchange rates associated 
  33. * with the orders. 
  34. * @return bool 
  35. */ 
  36. protected function should_use_orders_exchange_rates() { 
  37. return ($this->exchange_rates_to_use === Definitions::FX_SAVED_WITH_ORDER); 
  38.  
  39. public function __construct() { 
  40. parent::__construct(); 
  41.  
  42. // Store which tax types should be shown 
  43. $this->taxes_to_show = get_value(Definitions::ARG_TAX_TYPE, $_REQUEST, Definitions::TAX_MOSS_ONLY); 
  44. // Store which exchange rates should be used 
  45. $this->exchange_rates_to_use = get_value(Definitions::ARG_EXCHANGE_RATES_TYPE, $_REQUEST, Definitions::FX_SAVED_WITH_ORDER); 
  46.  
  47. /** 
  48. * Returns the meta keys of the order items that should be loaded by the report. 
  49. * For this report, line totals and cost indicate the price of products and 
  50. * the price of shipping, respectively. 
  51. * @return array 
  52. */ 
  53. protected function get_order_items_meta_keys() { 
  54. return array( 
  55. // _line_total: total charged for order items 
  56. '_line_total',  
  57. // cost: total charged for shipping 
  58. 'cost',  
  59. ); 
  60.  
  61. protected function get_order_items_meta() { 
  62. global $wpdb; 
  63.  
  64. $meta_keys = $this->get_order_items_meta_keys(); 
  65.  
  66. $px = $wpdb->prefix; 
  67. $SQL = sprintf(" 
  68. SELECT 
  69. ORDERS.ID AS order_id 
  70. , OI.order_item_id 
  71. , OIM.meta_key 
  72. , OIM.meta_value 
  73. FROM 
  74. {$px}posts AS ORDERS 
  75. INNER JOIN 
  76. {$px}woocommerce_order_items AS OI ON 
  77. (OI.order_id = ORDERS.ID) 
  78. INNER JOIN 
  79. {$px}woocommerce_order_itemmeta AS OIM ON 
  80. (OIM.order_item_id = OI.order_item_id) AND 
  81. (OIM.meta_key in ('%s')) 
  82. WHERE 
  83. (ORDERS.post_type IN ('shop_order')) AND 
  84. (ORDERS.post_status IN ('wc-processing', 'wc-completed')) AND 
  85. (ORDERS.post_date >= '" . date('Y-m-d', $this->start_date) . "') AND 
  86. (ORDERS.post_date < '" . date('Y-m-d', strtotime('+1 DAY', $this->end_date)) . "') 
  87. ", implode("', '", $meta_keys)); 
  88.  
  89. // Debug 
  90. if($this->debug) { 
  91. var_dump($SQL); 
  92. $dataset = $wpdb->get_results($SQL); 
  93.  
  94. // Debug 
  95. //var_dump("ORDER ITEMS META", $dataset); 
  96. return $dataset; 
  97.  
  98. protected function get_orders_meta_keys() { 
  99. return array( 
  100. '_billing_country',  
  101. '_billing_first_name',  
  102. '_billing_last_name',  
  103. '_billing_company',  
  104. '_billing_address_1',  
  105. '_billing_address_2',  
  106. '_billing_city',  
  107. '_billing_state',  
  108. '_billing_postcode',  
  109. '_billing_email',  
  110. '_billing_phone',  
  111. 'vat_number',  
  112. '_eu_vat_data',  
  113. '_eu_vat_evidence',  
  114. ); 
  115.  
  116. protected function get_orders_meta() { 
  117. global $wpdb; 
  118.  
  119. $meta_keys = $this->get_orders_meta_keys(); 
  120.  
  121. $px = $wpdb->prefix; 
  122. $SQL = sprintf(" 
  123. SELECT 
  124. ORDERS.ID AS order_id 
  125. , DATE(ORDERS.post_date) AS order_date 
  126. , OM.meta_key 
  127. , OM.meta_value 
  128. FROM 
  129. {$px}posts AS ORDERS 
  130. LEFT JOIN 
  131. {$px}postmeta AS OM ON 
  132. (OM.post_id = ORDERS.ID) AND 
  133. (OM.meta_key IN ('%s')) 
  134. WHERE 
  135. (ORDERS.post_type IN ('shop_order')) AND 
  136. (ORDERS.post_status IN ('%s')) AND 
  137. (ORDERS.post_date >= '" . date('Y-m-d', $this->start_date) . "') AND 
  138. (ORDERS.post_date < '" . date('Y-m-d', strtotime('+1 DAY', $this->end_date)) . "') 
  139. ",  
  140. implode("', '", $meta_keys),  
  141. implode("', '", $this->order_statuses_to_include(true))); 
  142.  
  143. // Debug 
  144. if($this->debug) { 
  145. var_dump($SQL); 
  146. $dataset = $wpdb->get_results($SQL); 
  147.  
  148. // Debug 
  149. //var_dump("ORDERS META", $dataset); 
  150. return $dataset; 
  151.  
  152. protected function consolidate_items_meta(&$order_items_meta) { 
  153. $result = array(); 
  154.  
  155. foreach($order_items_meta as $entry) { 
  156. /** We add an underscore to force the order id to be a string. This is 
  157. * needed because we will have to merge the result of this method in 
  158. * the final processing step. If the key stays numeric, the merge will 
  159. * append the various arrays, rather than merging them. 
  160. * @link http://php.net/manual/en/function.array-merge-recursive.php 
  161. */ 
  162. $order_id = '_' . $entry->order_id; 
  163.  
  164. if(!isset($result[$order_id])) { 
  165. $result[$order_id] = array( 
  166. 'order_items' => array(),  
  167. ); 
  168.  
  169. $order_item_id = $entry->order_item_id; 
  170. if(!isset($result[$order_id]['order_items'][$order_item_id])) { 
  171. $result[$order_id]['order_items'][$order_item_id] = array(); 
  172.  
  173. $result[$order_id]['order_items'][$order_item_id][$entry->meta_key] = maybe_unserialize($entry->meta_value); 
  174. return $result; 
  175.  
  176. protected function consolidate_orders_meta(&$orders_meta) { 
  177. $result = array(); 
  178.  
  179. foreach($orders_meta as $entry) { 
  180. /** We add an underscore to force the order id to be a string. This is 
  181. * needed because we will have to merge the result of this method in 
  182. * the final processing step. If the key stays numeric, the merge will 
  183. * append the various arrays, rather than merging them. 
  184. * @link http://php.net/manual/en/function.array-merge-recursive.php 
  185. */ 
  186. $order_id = '_' . $entry->order_id; 
  187.  
  188. if(!isset($result[$order_id])) { 
  189. $result[(string)$order_id] = array( 
  190. 'order_meta' => array(),  
  191. ); 
  192. $result[$order_id]['order_meta'][$entry->meta_key] = maybe_unserialize($entry->meta_value); 
  193. return $result; 
  194.  
  195. protected function consolidate_sales_data(&$order_items_meta, &$orders_meta) { 
  196. $result = array_merge_recursive( 
  197. $this->consolidate_items_meta($order_items_meta),  
  198. $this->consolidate_orders_meta($orders_meta) 
  199. ); 
  200. return $result; 
  201.  
  202. /** 
  203. * Returns the tax data for the report. 
  204. * @return array The tax data. 
  205. */ 
  206. protected function get_sales_data() { 
  207. global $wpdb; 
  208. $wpdb->show_errors(); 
  209.  
  210. //$startMemory = memory_get_usage(); 
  211. $order_items_meta = $this->get_order_items_meta(); 
  212. $orders_meta = $this->get_orders_meta(); 
  213.  
  214. $sales_data = $this->consolidate_sales_data($order_items_meta, $orders_meta); 
  215. //var_dump("MEMORY: " . (string)((memory_get_usage() - $startMemory) / 1024) . ' kB'); 
  216.  
  217. foreach($sales_data as $order_id => $order_data) { 
  218. if($this->should_skip($order_data)) { 
  219. unset($sales_data[$order_id]); 
  220.  
  221. // Debug 
  222. //var_dump($sales_data); 
  223.  
  224. return $sales_data; 
  225.  
  226. //$sales_data = array(); 
  227. //foreach($dataset as $data) { 
  228. // $data->_eu_vat_data = maybe_unserialize($data->_eu_vat_data); 
  229. // 
  230. // // Take the order number from the Sequential Order Number plugin, if 
  231. // // available. If such information is not available, use the order id. 
  232. // if(empty($data->order_number)) { 
  233. // $data->order_number = $data->post_id; 
  234. // } 
  235. // 
  236. // // Skip rows that should not appear on the report 
  237. // if($this->should_skip($data)) { 
  238. // continue; 
  239. // } 
  240. // 
  241. // // Select between the the exchange rate associated with each order, or the 
  242. // // ECB rate for the quarter 
  243. // if(!$this->should_use_orders_exchange_rates()) { 
  244. // // Get exchange rates for the last day of the quarter in which the order 
  245. // // was placed 
  246. // $last_day_of_quarter = $this->get_last_day_of_quarter($data->order_date); 
  247. // 
  248. // $vat_currency_exchange_rate = $this->get_vat_currency_exchange_rate($data->order_currency, $last_day_of_quarter); 
  249. // 
  250. // // If the exchange rate is not available, fall back to the one used with 
  251. // // the order 
  252. // if(is_numeric($vat_currency_exchange_rate)) { 
  253. // $data->_eu_vat_data['vat_currency_exchange_rate'] = $vat_currency_exchange_rate; 
  254. // } 
  255. // } 
  256. // 
  257. // $taxes_recorded = get_value('taxes', $data->_eu_vat_data, array()); 
  258. // foreach($taxes_recorded as $rate_id => $tax_details) { 
  259. // // Tag the tax record to indicate if it's part of MOSS or not 
  260. // $tax_details['is_moss'] = ($tax_details['country'] !== $this->base_country()) && 
  261. // (!in_array($tax_details['tax_rate_class'], $this->non_moss_tax_classes)); 
  262. // 
  263. // $data->_eu_vat_data['taxes'][$rate_id] = $tax_details; 
  264. // } 
  265. //} 
  266. // 
  267. ///* NOTES 
  268. // * - Tax rate can be calculated by dividing the line_tax by the tax_total 
  269. // * - Sale totals must be calculated in a second pass. If we add line_total and tax_total to 
  270. // * the query, it will return one line per order product. However, each line will also contain 
  271. // * ALL the taxes for the entire order, which would be processed multiple times. 
  272. // */ 
  273. //return $tax_data; 
  274.  
  275. /** 
  276. * Retrieves the refunds for the specified period and adds them to the tax 
  277. * data. 
  278. * @param array tax_data The tax data to which refund details should be added. 
  279. * @return array The tax data including the refunds applied in the specified 
  280. * period. 
  281. */ 
  282. protected function get_refunds_data() { 
  283. // This method must be implemented by descendant classes 
  284. return array(); 
  285.  
  286. /** 
  287. * Get the data for the report. 
  288. * @return string 
  289. */ 
  290. public function get_main_chart() { 
  291. $sales_data = $this->get_sales_data(); 
  292. // Add the refunds to the sales data 
  293. $refunds_data = $this->get_refunds_data(); 
  294.  
  295. // Store the list of EU countries. It will be used to remove non-EU tax from 
  296. // the report 
  297. $eu_countries = WC_Aelia_EU_VAT_Assistant::instance()->get_eu_vat_countries(); 
  298. // Keep track of the report columns. This information will be used to adjust 
  299. // the "colspan" property 
  300. $report_columns = 13; 
  301. ?> 
  302. <div id="eu_vat_report" class="wc_aelia_eu_vat_assistant report"> 
  303. <table class="widefat"> 
  304. <thead> 
  305. <tr class="report_information"> 
  306. <th colspan="<?php echo $report_columns; ?>"> 
  307. <ul> 
  308. <li> 
  309. <span class="label"><?php 
  310. echo __('Currency for VAT returns:', $this->text_domain); 
  311. ?></span> 
  312. <span><?php echo $this->vat_currency(); ?></span> 
  313. </li> 
  314. <li> 
  315. <span class="label"><?php 
  316. echo __('Exchange rates used:', $this->text_domain); 
  317. ?></span> 
  318. <span><?php 
  319. if($this->should_use_orders_exchange_rates()) { 
  320. echo __('Rates saved with each order', $this->text_domain); 
  321. else { 
  322. echo __('ECB rates for each quarter', $this->text_domain); 
  323. ?></span> 
  324. </li> 
  325. </ul> 
  326. </th> 
  327. </tr> 
  328. <tr> 
  329. <th colspan="3" class="column_group left"></th> 
  330. <th colspan="4" class="column_group header"><?php echo __('Items', $this->text_domain); ?></th> 
  331. <th colspan="4" class="column_group header"><?php echo __('Shipping', $this->text_domain); ?></th> 
  332. <th colspan="2" class="column_group right"> </th> 
  333. </tr> 
  334. <tr class="column_headers"> 
  335. <th class="country_name"><?php echo __('Customer Country', $this->text_domain); ?></th> 
  336. <th class="country_code"><?php echo __('Country Code', $this->text_domain); ?></th> 
  337. <th class="tax_rate"><?php echo __('Tax Rate', $this->text_domain); ?></th> 
  338.  
  339. <!-- Items --> 
  340. <th class="total_row column_group left"><?php echo __('Sales', $this->text_domain); ?></th> 
  341. <th class="total_row column_group "><?php echo __('Refunds', $this->text_domain); ?></th> 
  342. <th class="total_row column_group "><?php echo __('VAT Charged', $this->text_domain); ?></th> 
  343. <th class="total_row column_group right"><?php echo __('VAT Refunded', $this->text_domain); ?></th> 
  344.  
  345. <!-- Shipping --> 
  346. <th class="total_row column_group left"><?php echo __('Shipping charged', $this->text_domain); ?></th> 
  347. <th class="total_row column_group "><?php echo __('Shipping refunded', $this->text_domain); ?></th> 
  348. <th class="total_row column_group "><?php echo __('VAT Charged', $this->text_domain); ?></th> 
  349. <th class="total_row column_group right"><?php echo __('VAT Refunded', $this->text_domain); ?></th> 
  350.  
  351. <!-- Totals --> 
  352. <th class="total_row column_group left"><?php echo __('Total charged', $this->text_domain); ?></th> 
  353. <th class="total_row column_group right"><?php echo __('Final VAT Total', $this->text_domain); ?></th> 
  354. </tr> 
  355. </thead> 
  356. <?php if(empty($taxes_by_country)) : ?> 
  357. <tbody> 
  358. <tr> 
  359. <td colspan="<?php echo $report_columns; ?>"><?php echo __('No VAT has been processed in this period', $this->text_domain); ?></td> 
  360. </tr> 
  361. </tbody> 
  362. <?php else : ?> 
  363. <tbody> 
  364. <?php 
  365. $tax_grand_totals = array( 
  366. 'items_total' => 0,  
  367. 'refunded_items_total' => 0,  
  368. 'shipping_total' => 0,  
  369. 'refunded_shipping_total' => 0,  
  370.  
  371. 'items_tax_amount' => 0,  
  372. 'refunded_items_tax_amount' => 0,  
  373. 'shipping_tax_amount' => 0,  
  374. 'refunded_shipping_tax_amount' => 0,  
  375. ); 
  376.  
  377.  
  378. // First loop - Tax groups (MOSS and non-MOSS) 
  379. foreach($taxes_by_country as $moss_group => $group_taxes) { 
  380. if(empty($group_taxes)) { 
  381. continue; 
  382. // Render a sub-header to make it easier to read the report 
  383. $this->render_group_header($moss_group, $report_columns); 
  384.  
  385. // Second loop - Taxes by country 
  386. foreach($group_taxes as $country_code => $tax_data) { 
  387. // Third loop taxes for a specific country 
  388. foreach($tax_data as $rate_id => $tax_row) { 
  389. $rate = $tax_row->tax_rate_data; 
  390. if(!empty($tax_row->tax_rate_country) && !in_array($rate->tax_rate_country, $eu_countries)) { 
  391. continue; 
  392.  
  393. $tax_payable_to_country = get_value('tax_payable_to_country', $rate, $rate->tax_rate_country) 
  394. ?> 
  395. <tr> 
  396. <th class="country_name" scope="row"><?php echo esc_html(WC()->countries->countries[$rate->tax_rate_country]); ?></th> 
  397. <th class="country_code" scope="row"><?php echo esc_html($rate->tax_rate_country); ?></th> 
  398. <td class="tax_rate"><?php echo number_format(apply_filters('woocommerce_reports_taxes_rate', $rate->tax_rate, $rate_id, $tax_row), 2); ?>%</td> 
  399.  
  400. <!-- Items --> 
  401. <td class="total_row column_group left"><?php echo $this->format_price($tax_row->items_total); ?></td> 
  402. <td class="total_row column_group "><?php echo $this->format_price($tax_row->refunded_items_total); ?></td> 
  403. <td class="total_row column_group "><?php echo $this->format_price($tax_row->items_tax_amount); ?></td> 
  404. <td class="total_row column_group right"><?php echo $this->format_price($tax_row->refunded_items_tax_amount * -1); ?></td> 
  405.  
  406. <!-- Shipping --> 
  407. <td class="total_row column_group left"><?php echo $this->format_price($tax_row->shipping_total); ?></td> 
  408. <td class="total_row column_group "><?php echo $this->format_price($tax_row->refunded_shipping_total); ?></td> 
  409. <td class="total_row column_group "><?php echo $this->format_price($tax_row->shipping_tax_amount); ?></td> 
  410. <td class="total_row column_group right"><?php echo $this->format_price($tax_row->refunded_shipping_tax_amount * -1); ?></td> 
  411.  
  412. <!-- Total --> 
  413. <td class="total_row column_group left"><?php 
  414. echo $this->format_price($tax_row->items_total 
  415. + $tax_row->shipping_total 
  416. - $tax_row->refunded_items_total 
  417. - $tax_row->refunded_shipping_total); 
  418. ?></td> 
  419. <td class="total_row column_group right"><?php 
  420. echo $this->format_price($tax_row->items_tax_amount 
  421. + $tax_row->shipping_tax_amount 
  422. - $tax_row->refunded_items_tax_amount 
  423. - $tax_row->refunded_shipping_tax_amount); 
  424. ?></td> 
  425. </tr> 
  426. <?php 
  427.  
  428. // Calculate grand totals 
  429. $tax_grand_totals['items_tax_amount'] += $tax_row->items_tax_amount; 
  430. $tax_grand_totals['refunded_items_tax_amount'] += $tax_row->refunded_items_tax_amount; 
  431. $tax_grand_totals['shipping_tax_amount'] += $tax_row->shipping_tax_amount; 
  432. $tax_grand_totals['refunded_shipping_tax_amount'] += $tax_row->refunded_shipping_tax_amount; 
  433.  
  434. // Sales data 
  435. $tax_grand_totals['items_total'] += $tax_row->items_total; 
  436. $tax_grand_totals['refunded_items_total'] += $tax_row->refunded_items_total; 
  437. $tax_grand_totals['shipping_total'] += $tax_row->shipping_total; 
  438. $tax_grand_totals['refunded_shipping_total'] += $tax_row->refunded_shipping_total; 
  439. } // Third loop - END 
  440. } // Second loop - END 
  441. } // First loop - END 
  442. ?> 
  443. </tbody> 
  444. <!--- VAT Totals ---> 
  445. <tfoot id="vat-grand-totals"> 
  446. <tr> 
  447. <th class="label" colspan="3"><?php echo __('Totals', $this->text_domain); ?></th> 
  448. <!-- Items --> 
  449. <td class="total_row column_group left"><?php echo $this->format_price($tax_grand_totals['items_total']); ?></td> 
  450. <td class="total_row column_group "><?php echo $this->format_price($tax_grand_totals['refunded_items_total']); ?></td> 
  451. <td class="total_row column_group "><?php echo $this->format_price($tax_grand_totals['items_tax_amount']); ?></td> 
  452. <td class="total_row column_group right"><?php echo $this->format_price($tax_grand_totals['refunded_items_tax_amount'] * -1); ?></td> 
  453.  
  454. <!-- Shipping --> 
  455. <td class="total_row column_group left"><?php echo $this->format_price($tax_grand_totals['shipping_total']); ?></td> 
  456. <td class="total_row column_group "><?php echo $this->format_price($tax_grand_totals['refunded_shipping_total']); ?></td> 
  457. <td class="total_row column_group "><?php echo $this->format_price($tax_grand_totals['shipping_tax_amount']); ?></td> 
  458. <td class="total_row column_group right"><?php echo $this->format_price($tax_grand_totals['refunded_shipping_tax_amount'] * -1); ?></td> 
  459.  
  460. <!-- Totals --> 
  461. <td class="total_row column_group left"><?php 
  462. echo $this->format_price($tax_grand_totals['items_total'] 
  463. + $tax_grand_totals['shipping_total'] 
  464. - $tax_grand_totals['refunded_items_total'] 
  465. - $tax_grand_totals['refunded_shipping_total']); 
  466. ?></td> 
  467. <td class="total_row column_group right"><?php 
  468. echo $this->format_price($tax_grand_totals['items_tax_amount'] 
  469. + $tax_grand_totals['shipping_tax_amount'] 
  470. - $tax_grand_totals['refunded_items_tax_amount'] 
  471. - $tax_grand_totals['refunded_shipping_tax_amount']); 
  472. ?></td> 
  473. </tr> 
  474. </tfoot> 
  475. <?php endif; ?> 
  476. </table> 
  477. </div> 
  478. <?php 
  479.  
  480. protected function render_group_header($moss_group, $report_columns) { 
  481. $group_header_content = array( 
  482. 'moss' => array( 
  483. 'title' => __('MOSS VAT Details', $this->text_domain),  
  484. 'description' => __('This section shows the data to be used to file the VAT MOSS return.', $this->text_domain),  
  485. ),  
  486. 'non-moss' => array( 
  487. 'title' => __('Domestic/non-MOSS VAT Details', $this->text_domain),  
  488. 'description' => __('This section shows the data for the domestic VAT return.', $this->text_domain),  
  489. ),  
  490. ); 
  491.  
  492. $content = get_value($moss_group, $group_header_content); 
  493. if(empty($content)) { 
  494. return; 
  495. ?> 
  496. <tr class="group_header"> 
  497. <th class="" colspan="<?php echo $report_columns; ?>"> 
  498. <div class="title"><?php 
  499. echo $content['title']; 
  500. ?></div> 
  501. <div class="description"><?php 
  502. echo $content['description']; 
  503. ?></div> 
  504. </th> 
  505. </tr> 
  506. <?php 
  507.  
  508. /** 
  509. * Renders a header on top of the standard reporting UI. 
  510. */ 
  511. protected function render_ui_header() { 
  512. include(WC_Aelia_EU_VAT_Assistant::instance()->path('views') . '/admin/reports/sales-report-header.php');