AeliaWCEU_VAT_AssistantReportsBase_Sales_Summary_Report

Base class for the sales summary report.

Defined (1)

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

/src/lib/classes/reporting/reports/base/base_sales_summary_report.php  
  1. abstract class Base_Sales_Summary_Report extends \Aelia\WC\EU_VAT_Assistant\Reports\Base_Sales_Report { 
  2. const SALES_SUMMARY_REPORT_TEMP_TABLE = 'aelia_euva_sales_summary_report'; 
  3.  
  4. /** 
  5. * Indicates if the tax passed as a parameter should be skipped (i.e. excluded 
  6. * from the report). 
  7. * @param array tax_details An array of data describing a tax. 
  8. * @return bool True (tax should be excluded from the report) or false (tax 
  9. * should be displayed on the report). 
  10. */ 
  11. protected function should_skip($order_data) { 
  12. return false; 
  13.  
  14. /** 
  15. * Creates the temporary table that will be used to generate the VIES report. 
  16. * @return string|bool The name of the created table, or false on failure. 
  17. * @since 1.3.20.150330 
  18. */ 
  19. protected function create_temp_report_table() { 
  20. global $wpdb; 
  21.  
  22. $table_name = $wpdb->prefix . self::SALES_SUMMARY_REPORT_TEMP_TABLE; 
  23. $sql = " 
  24. CREATE TEMPORARY TABLE IF NOT EXISTS `$table_name` ( 
  25. `row_id` INT NOT NULL AUTO_INCREMENT,  
  26. `order_id` INT NOT NULL,  
  27. `post_type` VARCHAR(50) NOT NULL,  
  28. `is_eu_country` VARCHAR(10) NOT NULL,  
  29. `billing_country` VARCHAR(10) NOT NULL,  
  30. `vat_number` VARCHAR(50) NOT NULL,  
  31. `vat_number_validated` VARCHAR(50) NOT NULL,  
  32. `order_item_id` INT NOT NULL,  
  33. `line_total` DECIMAL(18, 6) NOT NULL,  
  34. `line_tax` DECIMAL(18, 6) NOT NULL,  
  35. `tax_rate` DECIMAL(18, 2) NOT NULL,  
  36. `exchange_rate` DECIMAL(18, 6) NOT NULL,  
  37. PRIMARY KEY (`row_id`) 
  38. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 
  39. "; 
  40.  
  41. return $this->create_temporary_table($table_name, $sql); 
  42.  
  43. /** 
  44. * Stores a row in the temporary table used to produce the VIES report. 
  45. * @since 1.3.20.150330 
  46. */ 
  47. protected function store_temp_data(array $fields) { 
  48. global $wpdb; 
  49.  
  50. // Debug 
  51. //var_dump("STORING TEMP. TAX DATA", $fields); 
  52.  
  53. $table_name = $wpdb->prefix . self::SALES_SUMMARY_REPORT_TEMP_TABLE; 
  54. $SQL = " 
  55. INSERT INTO `$table_name` ( 
  56. `order_id`,  
  57. `post_type`,  
  58. `is_eu_country`,  
  59. `billing_country`,  
  60. `vat_number`,  
  61. `vat_number_validated`,  
  62. `order_item_id`,  
  63. `line_total`,  
  64. `line_tax`,  
  65. `tax_rate`,  
  66. `exchange_rate` 
  67. VALUES ( 
  68. %d, -- Order ID 
  69. %s, -- Post type (for debugging purposes) 
  70. %s, -- Is EU country (flag) 
  71. %s, -- Billing country 
  72. %d, -- Order item ID 
  73. %s, -- VAT Number 
  74. %s, -- VAT Number validated (flag) 
  75. %f, -- Line total 
  76. %f, -- Line tax 
  77. %f, -- Tax rate 
  78. %f -- Exchange rate 
  79. "; 
  80.  
  81. $query = $wpdb->prepare( 
  82. $SQL,  
  83. $fields['order_id'],  
  84. $fields['post_type'],  
  85. $fields['is_eu_country'],  
  86. $fields['billing_country'],  
  87. $fields['vat_number'],  
  88. $fields['vat_number_validated'],  
  89. $fields['order_item_id'],  
  90. $fields['line_total'],  
  91. $fields['line_tax'],  
  92. $fields['tax_rate'],  
  93. $fields['exchange_rate'] 
  94. ); 
  95.  
  96. // Debug 
  97. //var_dump($fields, $query);die(); 
  98.  
  99. // Save to database the IP data for the country 
  100. $rows_affected = $wpdb->query($query); 
  101.  
  102. $result = $rows_affected; 
  103. if($result == false) { 
  104. $error_message = sprintf(__('Could not store row in table "%s" rates. ' . 
  105. 'Fields (JSON): "%s".', $this->text_domain),  
  106. $table_name,  
  107. $fields); 
  108. $this->log($error_message, false); 
  109. trigger_error(E_USER_WARNING, $error_message); 
  110. return $result; 
  111.  
  112. /** 
  113. * Returns the meta keys of the order items that should be loaded by the report. 
  114. * For this report, line totals and cost indicate the price of products and 
  115. * the price of shipping, respectively. 
  116. * @return array 
  117. */ 
  118. protected function get_order_items_meta_keys() { 
  119. return array( 
  120. // _line_total: total charged for order items 
  121. '_line_total',  
  122. // cost: total charged for shipping 
  123. 'cost',  
  124. ); 
  125.  
  126. /** 
  127. * Stores in a temporary table the data required to produce the VIES report. 
  128. * @param array dataset An array containing the data for the report. 
  129. * @return bool True if the data was stored correctly, false otherwise. 
  130. * @since 1.3.20.150402 
  131. */ 
  132. protected function store_report_data($dataset) { 
  133. foreach($dataset as $index => $entry) { 
  134. $entry->eu_vat_data = maybe_unserialize($entry->eu_vat_data); 
  135. $entry->eu_vat_evidence = maybe_unserialize($entry->eu_vat_evidence); 
  136.  
  137. //var_dump($entry->eu_vat_data); 
  138.  
  139. if(!$this->should_skip($entry)) { 
  140. $vat_currency_exchange_rate = (float)get_value('vat_currency_exchange_rate', $entry->eu_vat_data); 
  141. if(!is_numeric($vat_currency_exchange_rate) || ($vat_currency_exchange_rate <= 0)) { 
  142. $this->log(sprintf(__('VAT currency exchange rate not found for order id "%s". ' . 
  143. 'Fetching exchange rate from FX provider.', $this->text_domain),  
  144. $entry->order_id)); 
  145. $vat_currency_exchange_rate = $this->get_vat_currency_exchange_rate($entry->order_currency,  
  146. $entry->order_date); 
  147.  
  148. $fields = array( 
  149. 'order_id' => $entry->order_id,  
  150. 'post_type' => $entry->post_type,  
  151. 'is_eu_country' => $this->is_eu_country($entry->eu_vat_evidence['location']['billing_country']) ? 'eu' : 'non-eu',  
  152. 'billing_country' => $entry->eu_vat_evidence['location']['billing_country'],  
  153. 'vat_number' => $entry->eu_vat_evidence['exemption']['vat_number'],  
  154. 'vat_number_validated' => $entry->vat_number_validated,  
  155. 'order_item_id' => $entry->order_item_id,  
  156. 'exchange_rate' => $vat_currency_exchange_rate,  
  157. ); 
  158.  
  159. /** Calculate the line tax 
  160. * This operation is necessary because shipping taxes are stored as an 
  161. * array. The amounts have to be unserialised and summed. 
  162. */ 
  163. // TODO This logic doesn't support compounding tax rates and should be reviewed. 
  164. $line_tax = maybe_unserialize($entry->line_tax); 
  165. if(is_array($line_tax)) { 
  166. $line_tax = array_sum($line_tax); 
  167.  
  168. /** Calculate the tax rate 
  169. * A tax rate lower than zero means that the actual rate could not be 
  170. * calculated via SQL. This is often the case when the item is a shipping 
  171. * cost, as its tax is stored as an array, insteaf of a number (see 
  172. * above). 
  173. */ 
  174. if($entry->tax_rate < 0) { 
  175. $entry->tax_rate = 0; 
  176. if($entry->line_total > 0) { 
  177. $entry->tax_rate = round($line_tax / $entry->line_total * 100, 2); 
  178.  
  179. // Add the tax information to the data 
  180. $fields = array_merge($fields, array( 
  181. 'line_total' => $entry->line_total,  
  182. 'line_tax' => $line_tax,  
  183. 'tax_rate' => $entry->tax_rate,  
  184. )); 
  185.  
  186. if(!$this->store_temp_data($fields)) { 
  187. return false; 
  188. return true; 
  189.  
  190. /** 
  191. * Returns the sales data that will be included in the report. This method must 
  192. * be implemented by descendant classes. 
  193. * @return array 
  194. */ 
  195. protected function get_sales_data() { 
  196. return array(); 
  197.  
  198. /** 
  199. * Returns the refunds data that will be included in the report. This method 
  200. * is empty for compatibility with WooCommerce 2.1, which doesn't handle 
  201. * refunds. Classes designed for WooCommerce 2.2 and later will take care of 
  202. * fetching the refunds. 
  203. * @return array 
  204. * @since 1.3.20.150330 
  205. */ 
  206. protected function get_refunds_data() { 
  207. return array(); 
  208.  
  209. /** 
  210. * Consolidates the sales data with the refunds data and returns it. 
  211. * @return array An array containing the consolidated sales and return data. 
  212. * @since 1.3.20.150330 
  213. */ 
  214. protected function get_sales_summary_report_data() { 
  215. global $wpdb; 
  216.  
  217. $px = $wpdb->prefix; 
  218. $SQL = " 
  219. SELECT 
  220. SSR.is_eu_country 
  221. , SSR.billing_country 
  222. , SSR.tax_rate 
  223. , SUM(SSR.line_total * SSR.exchange_rate) AS sales_total 
  224. , SUM(SSR.line_tax * SSR.exchange_rate) AS tax_total 
  225. FROM 
  226. {$px}" . self::SALES_SUMMARY_REPORT_TEMP_TABLE . " SSR 
  227. GROUP BY 
  228. SSR.is_eu_country 
  229. , SSR.billing_country 
  230. , SSR.tax_rate 
  231. HAVING 
  232. -- Discard rows with zero, they don't need to be added to the report. 
  233. -- We can't just use 'greater than zero' as a criteria, because rows 
  234. -- with negative values must be included 
  235. (sales_total <> 0) 
  236. ORDER BY 
  237. SSR.is_eu_country 
  238. , SSR.tax_rate 
  239. , SSR.billing_country 
  240. "; 
  241.  
  242. // Debug 
  243. //var_dump($SQL);die(); 
  244. $dataset = $wpdb->get_results($SQL); 
  245.  
  246. // Debug 
  247. //var_dump("REFUNDS RESULT", $dataset); 
  248. return $dataset; 
  249.  
  250. /** 
  251. * Loads and returns the report data. 
  252. * @return array An array with the report data. 
  253. * @since 1.3.20.150402 
  254. */ 
  255. protected function get_report_data() { 
  256. if($result = $this->create_temp_report_table()) { 
  257. // Retrieve and store sales data 
  258. $result = $this->store_report_data($this->get_sales_data()); 
  259.  
  260. // Retrieve and store refunds data 
  261. if($result) { 
  262. $result = $this->store_report_data($this->get_refunds_data()); 
  263.  
  264. if($result) { 
  265. // Prepare a summary for the VIES report and return it 
  266. $result = $this->get_sales_summary_report_data(); 
  267. return $result; 
  268.  
  269. if(!$result) { 
  270. trigger_error(E_USER_WARNING, __('Could not prepare temporary table for the report. ' . 
  271. 'Please enable debug mode and tru again. If the issue ' . 
  272. 'persists, contact support and forward them the debug ' . 
  273. 'log produced by the plugin. For more information, please ' . 
  274. 'go to WooCommerce > EU VAT Assistant > Support.',  
  275. $this->text_domain)); 
  276.  
  277. /** 
  278. * Get the data for the report. 
  279. * @return string 
  280. */ 
  281. public function get_main_chart() { 
  282. $sales_summary_report_data = $this->get_report_data(); 
  283.  
  284. // Keep track of the report columns. This information will be used to adjust 
  285. // the "colspan" property 
  286. $report_columns = 6; 
  287. $debug_columns_class = $this->debug ? '' : ' hidden '; 
  288. ?> 
  289. <div id="sales_summary_report" class="wc_aelia_eu_vat_assistant report"> 
  290. <table class="widefat"> 
  291. <thead> 
  292. <tr class="report_information"> 
  293. <th colspan="<?php echo $report_columns; ?>"> 
  294. <ul> 
  295. <li> 
  296. <span class="label"><?php 
  297. echo __('Currency for VAT returns:', $this->text_domain); 
  298. ?></span> 
  299. <span><?php echo $this->vat_currency(); ?></span> 
  300. </li> 
  301. <li> 
  302. <span class="label"><?php 
  303. echo __('Exchange rates used:', $this->text_domain); 
  304. ?></span> 
  305. <span><?php 
  306. if($this->should_use_orders_exchange_rates()) { 
  307. echo __('Rates saved with each order', $this->text_domain); 
  308. else { 
  309. echo __('ECB rates for each quarter', $this->text_domain); 
  310. ?></span> 
  311. </li> 
  312. </ul> 
  313. </th> 
  314. </tr> 
  315. <tr class="column_headers"> 
  316. <th class="is_eu <?php echo $debug_columns_class; ?>"><?php echo __('EU', $this->text_domain); ?></th> 
  317. <th class="billing_country"><?php echo __('Customer country', $this->text_domain); ?></th> 
  318. <th class="tax_rate total_row column_group left right"><?php echo __('Tax rate', $this->text_domain); ?></th> 
  319. <th class="total_sales total_row "><?php echo __('Total Sales (ex. tax)', $this->text_domain); ?></th> 
  320. <th class="total_tax total_row "><?php echo __('Total Tax', $this->text_domain); ?></th> 
  321. <th class="total_tax total_row inc_tax column_group left"><?php echo __('Total Sales (inc. tax)', $this->text_domain); ?></th> 
  322. </tr> 
  323. </thead> 
  324. <?php if(empty($sales_summary_report_data)) : ?> 
  325. <tbody> 
  326. <tr> 
  327. <td colspan="<?php echo $report_columns; ?>"><?php echo __('No sales have been found for the selected period.', $this->text_domain); ?></td> 
  328. </tr> 
  329. </tbody> 
  330. <?php else : ?> 
  331. <tbody> 
  332. <?php 
  333.  
  334. $sales_total = 0; 
  335. $taxes_total = 0; 
  336. $render_group = null; 
  337. foreach($sales_summary_report_data as $entry_id => $entry) { 
  338. if($render_group != $entry->is_eu_country) { 
  339. $this->render_group_header($entry->is_eu_country, $report_columns); 
  340. $render_group = $entry->is_eu_country; 
  341.  
  342. $sales_total += $entry->sales_total; 
  343. $taxes_total += $entry->tax_total; 
  344. ?> 
  345. <tr> 
  346. <th class="is_eu <?php echo $debug_columns_class; ?>"><?php echo $entry->is_eu_country; ?></th> 
  347. <th class="billing_country"><?php echo $entry->billing_country; ?></th> 
  348. <th class="tax_rate total_row column_group left right"><?php echo $entry->tax_rate; ?></th> 
  349. <th class="total_sales total_row "><?php echo $this->format_price($entry->sales_total); ?></th> 
  350. <th class="total_tax total_row "><?php echo $this->format_price($entry->tax_total); ?></th> 
  351. <th class="total_sales total_row inc_tax column_group left"><?php echo $this->format_price($entry->sales_total + $entry->tax_total); ?></th> 
  352. </tr> 
  353. <?php 
  354. } // First loop - END 
  355. ?> 
  356. </tbody> 
  357. <tfoot> 
  358. <tr> 
  359. <th class="label column_group right" colspan="2"><?php echo __('Totals', $this->text_domain); ?></th> 
  360. <th class="total total_row"><?php echo $this->format_price($sales_total); ?></th> 
  361. <th class="total total_row"><?php echo $this->format_price($taxes_total); ?></th> 
  362. <th class="total total_row column_group left"><?php echo $this->format_price($sales_total + $taxes_total); ?></th> 
  363. </tr> 
  364. </tfoot> 
  365. <?php endif; ?> 
  366. </table> 
  367. </div> 
  368. <?php 
  369.  
  370. /** 
  371. * Renders a header on top of the standard reporting UI. 
  372. */ 
  373. protected function render_ui_header() { 
  374. include(WC_Aelia_EU_VAT_Assistant::instance()->path('views') . '/admin/reports/sales-summary-report-header.php'); 
  375.  
  376. /** 
  377. * Renders a group header, to organise the data displayed in the report. 
  378. * @param string group_id The group ID. Each group ID will show a different 
  379. * text. 
  380. * @param int report_columns The number of columns in the report. Used to 
  381. * determine the "colspan" of the group header. 
  382. */ 
  383. protected function render_group_header($group_id, $report_columns) { 
  384. $group_header_content = array( 
  385. 'eu' => array( 
  386. 'title' => __('EU Sales', $this->text_domain),  
  387. 'description' => __('This section shows sales made to EU countries.', $this->text_domain),  
  388. ),  
  389. 'non-eu' => array( 
  390. 'title' => __('Non-EU Sales', $this->text_domain),  
  391. 'description' => __('This section shows sales made to countries outside the EU.', $this->text_domain),  
  392. ),  
  393. ); 
  394.  
  395. $content = get_value($group_id, $group_header_content); 
  396. if(empty($content)) { 
  397. return; 
  398. ?> 
  399. <tr class="group_header"> 
  400. <th class="" colspan="<?php echo $report_columns; ?>"> 
  401. <div class="title"><?php 
  402. echo $content['title']; 
  403. ?></div> 
  404. <div class="description"><?php 
  405. echo $content['description']; 
  406. ?></div> 
  407. </th> 
  408. </tr> 
  409. <?php