AeliaWCEU_VAT_AssistantReportsBase_INTRASTAT_Report

Renders the INTRASTAT report.

Defined (1)

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

/src/lib/classes/reporting/reports/base/base_intrastat_report.php  
  1. class Base_INTRASTAT_Report extends \Aelia\WC\EU_VAT_Assistant\Reports\Base_Sales_Report { 
  2. const INTRASTAT_REPORT_TEMP_TABLE = 'aelia_euva_intrastat_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. $eu_vat_evidence = maybe_unserialize(get_value('eu_vat_evidence', $order_data)); 
  13. // INTRASTAT reports must only include sales to the EU, excluding shop base 
  14. // country 
  15. if(empty($eu_vat_evidence['location']['is_eu_country']) || 
  16. ($eu_vat_evidence['location']['is_eu_country'] == false) || 
  17. ($eu_vat_evidence['location']['billing_country'] == $this->base_country())) { 
  18. return true; 
  19. return false; 
  20.  
  21. /** 
  22. * Creates the temporary table that will be used to generate the INTRASTAT report. 
  23. * @return string|bool The name of the created table, or false on failure. 
  24. */ 
  25. protected function create_temp_intrastat_table() { 
  26. global $wpdb; 
  27.  
  28. $table_name = $wpdb->prefix . self::INTRASTAT_REPORT_TEMP_TABLE; 
  29. $sql = " 
  30. CREATE TEMPORARY TABLE IF NOT EXISTS `$table_name` ( 
  31. `row_id` INT NOT NULL AUTO_INCREMENT,  
  32. `order_id` INT NOT NULL,  
  33. `order_date` DATETIME NOT NULL,  
  34. `post_type` VARCHAR(50) NOT NULL,  
  35. `is_eu_country` VARCHAR(10) NOT NULL,  
  36. `billing_country` VARCHAR(10) NOT NULL,  
  37. `vat_number` VARCHAR(50) NOT NULL,  
  38. `vat_number_validated` VARCHAR(50) NOT NULL,  
  39. `order_item_id` INT NOT NULL,  
  40. `line_total` DECIMAL(18, 6) NOT NULL,  
  41. `line_tax` DECIMAL(18, 6) NOT NULL,  
  42. `tax_rate` DECIMAL(18, 2) NOT NULL,  
  43. `exchange_rate` DECIMAL(18, 6) NOT NULL,  
  44. PRIMARY KEY (`row_id`) 
  45. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 
  46. "; 
  47.  
  48. return $this->create_temporary_table($table_name, $sql); 
  49.  
  50. /** 
  51. * Stores a row in the temporary table used to produce the INTRASTAT report. 
  52. */ 
  53. protected function store_temp_intrastat_row(array $fields) { 
  54. global $wpdb; 
  55.  
  56. // Debug 
  57. //var_dump("STORING TEMP. INTRASTAT DATA", $fields); 
  58.  
  59. $table_name = $wpdb->prefix . self::INTRASTAT_REPORT_TEMP_TABLE; 
  60. $SQL = " 
  61. INSERT INTO `$table_name` ( 
  62. `order_id`,  
  63. `order_date`,  
  64. `post_type`,  
  65. `is_eu_country`,  
  66. `billing_country`,  
  67. `vat_number`,  
  68. `vat_number_validated`,  
  69. `order_item_id`,  
  70. `line_total`,  
  71. `line_tax`,  
  72. `tax_rate`,  
  73. `exchange_rate` 
  74. VALUES ( 
  75. %d, -- Order ID 
  76. %s, -- Order date (date/time) 
  77. %s, -- Post type (for debugging purposes) 
  78. %s, -- Is EU country (flag) 
  79. %s, -- Billing country 
  80. %d, -- Order item ID 
  81. %s, -- VAT Number 
  82. %s, -- VAT Number validated (flag) 
  83. %f, -- Line total 
  84. %f, -- Line tax 
  85. %f, -- Tax rate 
  86. %f -- Exchange rate 
  87. "; 
  88.  
  89. $query = $wpdb->prepare( 
  90. $SQL,  
  91. $fields['order_id'],  
  92. $fields['order_date'],  
  93. $fields['post_type'],  
  94. $fields['is_eu_country'],  
  95. $fields['billing_country'],  
  96. $fields['vat_number'],  
  97. $fields['vat_number_validated'],  
  98. $fields['order_item_id'],  
  99. $fields['line_total'],  
  100. $fields['line_tax'],  
  101. $fields['tax_rate'],  
  102. $fields['exchange_rate'] 
  103. ); 
  104.  
  105. // Debug 
  106. //var_dump($fields, $query);die(); 
  107.  
  108. // Save to database the IP data for the country 
  109. $rows_affected = $wpdb->query($query); 
  110.  
  111. $result = $rows_affected; 
  112. if($result == false) { 
  113. $error_message = sprintf(__('Could not store row in table "%s" rates. ' . 
  114. 'Fields (JSON): "%s".', $this->text_domain),  
  115. $table_name,  
  116. $fields); 
  117. $this->log($error_message, false); 
  118. trigger_error(E_USER_WARNING, $error_message); 
  119. return $result; 
  120.  
  121. /** 
  122. * Stores in a temporary table the data required to produce the INTRASTAT report. 
  123. * @param array dataset An array containing the data for the report. 
  124. * @return bool True if the data was stored correctly, false otherwise. 
  125. * @since 1.3.20.150402 
  126. */ 
  127. protected function store_report_data($dataset) { 
  128. foreach($dataset as $index => $entry) { 
  129. $entry->eu_vat_data = maybe_unserialize($entry->eu_vat_data); 
  130. $entry->eu_vat_evidence = maybe_unserialize($entry->eu_vat_evidence); 
  131.  
  132. if(!$this->should_skip($entry)) { 
  133. $vat_currency_exchange_rate = (float)get_value('vat_currency_exchange_rate', $entry->eu_vat_data); 
  134. if(!is_numeric($vat_currency_exchange_rate) || ($vat_currency_exchange_rate <= 0)) { 
  135. $this->log(sprintf(__('VAT currency exchange rate not found for order id "%s". ' . 
  136. 'Fetching exchange rate from FX provider.', $this->text_domain),  
  137. $entry->order_id)); 
  138. $vat_currency_exchange_rate = $this->get_vat_currency_exchange_rate($entry->order_currency,  
  139. $entry->order_date); 
  140.  
  141. $fields = array( 
  142. 'order_id' => $entry->order_id,  
  143. 'order_date' => $entry->order_date,  
  144. 'post_type' => $entry->post_type,  
  145. 'is_eu_country' => $this->is_eu_country($entry->eu_vat_evidence['location']['billing_country']) ? 'eu' : 'non-eu',  
  146. 'billing_country' => $entry->eu_vat_evidence['location']['billing_country'],  
  147. 'vat_number' => $entry->eu_vat_evidence['exemption']['vat_number'],  
  148. 'vat_number_validated' => $entry->vat_number_validated,  
  149. 'order_item_id' => $entry->order_item_id,  
  150. 'exchange_rate' => $vat_currency_exchange_rate,  
  151. ); 
  152.  
  153. /** Calculate the line tax 
  154. * This operation is necessary because shipping taxes are stored as an 
  155. * array. The amounts have to be unserialised and summed. 
  156. */ 
  157. // TODO This logic doesn't support compounding tax rates and should be reviewed. 
  158. $line_tax = maybe_unserialize($entry->line_tax); 
  159. if(is_array($line_tax)) { 
  160. $line_tax = array_sum($line_tax); 
  161.  
  162. /** Calculate the tax rate 
  163. * A tax rate lower than zero means that the actual rate could not be 
  164. * calculated via SQL. This is often the case when the item is a shipping 
  165. * cost, as its tax is stored as an array, insteaf of a number (see 
  166. * above). 
  167. */ 
  168. if($entry->tax_rate < 0) { 
  169. $entry->tax_rate = 0; 
  170. if($entry->line_total > 0) { 
  171. $entry->tax_rate = round($line_tax / $entry->line_total * 100, 2); 
  172.  
  173. $fields = array_merge($fields, array( 
  174. 'line_total' => $entry->line_total,  
  175. 'line_tax' => $line_tax,  
  176. 'tax_rate' => $entry->tax_rate,  
  177. )); 
  178.  
  179. if(!$this->store_temp_intrastat_row($fields)) { 
  180. return false; 
  181. return true; 
  182.  
  183. /** 
  184. * Returns the sales data that will be included in the report. This method must 
  185. * be implemented by descendant classes. 
  186. * @return array 
  187. */ 
  188. protected function get_sales_data() { 
  189.  
  190. /** 
  191. * Returns the refunds data that will be included in the report. This method 
  192. * is empty for compatibility with WooCommerce 2.1, which doesn't handle 
  193. * refunds. Classes designed for WooCommerce 2.2 and later will take care of 
  194. * fetching the refunds. 
  195. * @return array 
  196. */ 
  197. protected function get_refunds_data() { 
  198. return array(); 
  199.  
  200. /** 
  201. * Consolidates the sales data with the refunds data and returns it. 
  202. * @return array An array containing the consolidated sales and return data. 
  203. */ 
  204. protected function get_intrastat_report_data() { 
  205. global $wpdb; 
  206.  
  207. $px = $wpdb->prefix; 
  208. $SQL = " 
  209. SELECT 
  210. YEAR(VR.order_date) AS year 
  211. , MONTH(VR.order_date) AS month 
  212. , SUM(VR.line_total * VR.exchange_rate) AS period_total 
  213. FROM 
  214. {$px}" . self::INTRASTAT_REPORT_TEMP_TABLE . " VR 
  215. GROUP BY 
  216. YEAR(VR.order_date) 
  217. , MONTH(VR.order_date) 
  218. "; 
  219.  
  220. // Debug 
  221. //var_dump($SQL); 
  222. $dataset = $wpdb->get_results($SQL); 
  223.  
  224. // Debug 
  225. //var_dump("REFUNDS RESULT", $dataset); 
  226. return $dataset; 
  227.  
  228. /** 
  229. * Loads and returns the report data. 
  230. * @return array An array with the report data. 
  231. */ 
  232. protected function get_report_data() { 
  233. if($result = $this->create_temp_intrastat_table()) { 
  234. // Retrieve and store sales data 
  235. $result = $this->store_report_data($this->get_sales_data()); 
  236.  
  237. // Retrieve and store refunds data 
  238. if($result) { 
  239. $result = $this->store_report_data($this->get_refunds_data()); 
  240.  
  241. if($result) { 
  242. // Prepare a summary for the INTRASTAT report and return it 
  243. $result = $this->get_intrastat_report_data(); 
  244. return $result; 
  245.  
  246. if(!$result) { 
  247. trigger_error(E_USER_WARNING, __('Could not prepare temporary table for the report. ' . 
  248. 'Please enable debug mode and tru again. If the issue ' . 
  249. 'persists, contact support and forward them the debug ' . 
  250. 'log produced by the plugin. For more information, please ' . 
  251. 'go to WooCommerce > EU VAT Assistant > Support.',  
  252. $this->text_domain)); 
  253.  
  254. /** 
  255. * Get the data for the report. 
  256. * @return string 
  257. */ 
  258. public function get_main_chart() { 
  259. $intrastat_report_data = $this->get_report_data(); 
  260.  
  261. // Debug 
  262. //var_dump($intrastat_report_data); 
  263.  
  264. // Keep track of the report columns. This information will be used to adjust 
  265. // the "colspan" property 
  266. $report_columns = 3; 
  267. ?> 
  268. <div id="intrastat_report" class="wc_aelia_eu_vat_assistant report"> 
  269. <table class="widefat"> 
  270. <thead> 
  271. <tr class="report_information"> 
  272. <th colspan="<?php echo $report_columns; ?>"> 
  273. <ul> 
  274. <li> 
  275. <span class="label"><?php 
  276. echo __('Currency for VAT returns:', $this->text_domain); 
  277. ?></span> 
  278. <span><?php echo $this->vat_currency(); ?></span> 
  279. </li> 
  280. <!-- 
  281. <li> 
  282. <span class="label"><?php 
  283. echo __('Exchange rates used:', $this->text_domain); 
  284. ?></span> 
  285. <span><?php 
  286. if($this->should_use_orders_exchange_rates()) { 
  287. echo __('Rates saved with each order', $this->text_domain); 
  288. else { 
  289. echo __('ECB rates for each quarter', $this->text_domain); 
  290. ?></span> 
  291. </li> 
  292. --> 
  293. </ul> 
  294. </th> 
  295. </tr> 
  296. <tr class="column_headers"> 
  297. <th class="year"><?php echo __('Year', $this->text_domain); ?></th> 
  298. <th class="month"><?php echo __('Month', $this->text_domain); ?></th> 
  299. <th class="total"><?php echo __('Sales', $this->text_domain); ?></th> 
  300. </tr> 
  301. </thead> 
  302. <?php if(empty($intrastat_report_data)) : ?> 
  303. <tbody> 
  304. <tr> 
  305. <td colspan="<?php echo $report_columns; ?>"><?php echo __('No sales falling under INTRASTAT scheme have been found.', $this->text_domain); ?></td> 
  306. </tr> 
  307. </tbody> 
  308. <?php else : ?> 
  309. <tbody> 
  310. <?php 
  311. $sales_total = 0; 
  312. foreach($intrastat_report_data as $entry_id => $entry) { 
  313. $sales_total += $entry->period_total; 
  314.  
  315. $date_obj = DateTime::createFromFormat('!m', $entry->month); 
  316. $month_name = $date_obj->format('F'); 
  317. ?> 
  318. <tr> 
  319. <td class="year"><?php echo $entry->year; ?></td> 
  320. <td class="month"><?php echo $month_name; ?></td> 
  321. <td class="total"><?php echo $this->format_price($entry->period_total); ?></td> 
  322. </tr> 
  323. <?php 
  324. ?> 
  325. </tbody> 
  326. <tfoot> 
  327. <tr> 
  328. <th colspan="2" class="label"><?php echo __('Total', $this->text_domain); ?></th> 
  329. <td class="total"><?php echo $this->format_price($sales_total); ?></td> 
  330. </tr> 
  331. </tfoot> 
  332. <?php endif; ?> 
  333. </table> 
  334. </div> 
  335. <?php 
  336.  
  337. /** 
  338. * Renders a header on top of the standard reporting UI. 
  339. */ 
  340. protected function render_ui_header() { 
  341. // The INTRASTAT report does not require a header 
  342.  
  343. /** 
  344. * Returns the label to be used to indicate a bi-monthly period. 
  345. * @param int period The period for which the label has to be generated. 
  346. * @param int year The year to which the period refers. 
  347. * @return string The label for the period. 
  348. * @since 1.4.4.150421 
  349. */ 
  350. protected function get_period_label($period, $year) { 
  351. $month2 = $period * 2; 
  352.  
  353. $month_name_1 = DateTime::createFromFormat('!m', $month2 - 1)->format('M'); 
  354. $month_name_2 = DateTime::createFromFormat('!m', $month2)->format('M'); 
  355.  
  356. return sprintf(__('%s-%s %d', $this->text_domain), $month_name_1, $month_name_2, $year); 
  357.  
  358. /** 
  359. * Returns an array of ranges that are used to produce the reports. 
  360. * @return array 
  361. */ 
  362. protected function get_report_ranges() { 
  363. $ranges = array(); 
  364.  
  365. $current_time = current_time('timestamp'); 
  366.  
  367. // Current bi-monthly period 
  368. $period = ceil(date('m', $current_time) / 2); 
  369. $year = date('Y'); 
  370. $ranges['current_period'] = $this->get_period_label($period, $year); 
  371.  
  372. // Quarter before this one 
  373. $month = date('m', strtotime('-2 MONTH', $current_time)); 
  374. $year = date('Y', strtotime('-2 MONTH', $current_time)); 
  375. $period = ceil($month / 2); 
  376. $ranges['previous_period'] = $this->get_period_label($period, $year); 
  377.  
  378. // Two quarters ago 
  379. $month = date('m', strtotime('-4 MONTH', $current_time)); 
  380. $year = date('Y', strtotime('-4 MONTH', $current_time)); 
  381. $period = ceil($month / 2); 
  382. $ranges['before_previous_period'] = $this->get_period_label($period, $year); 
  383.  
  384. return array_reverse($ranges); 
  385.  
  386. /** 
  387. * Output the report. 
  388. * @since 1.4.4.150421 
  389. */ 
  390. public function output_report() { 
  391. $ranges = $this->get_report_ranges(); 
  392. $current_range = !empty($_GET['range']) ? sanitize_text_field($_GET['range']) : 'current_period'; 
  393.  
  394. if(!in_array($current_range, array_merge(array_keys($ranges), array('custom')))) { 
  395. $current_range = 'current_period'; 
  396. $this->calculate_current_range($current_range); 
  397.  
  398. $hide_sidebar = true; 
  399.  
  400. // Render a header on top of the standard reporting UI 
  401. $this->render_ui_header(); 
  402.  
  403. include(WC()->plugin_path() . '/includes/admin/views/html-report-by-date.php'); 
  404.  
  405. /** 
  406. * Get the current range and calculate the start and end dates of the 
  407. * corresponding bi-monthly period. 
  408. * @param string $current_range The range to be used for the calculation. 
  409. * @since 1.4.4.150421 
  410. */ 
  411. public function calculate_current_range($current_range) { 
  412. $this->chart_groupby = 'month'; 
  413. switch ($current_range) { 
  414. case 'before_previous_period': 
  415. $month = date('m', strtotime('-4 MONTH', current_time('timestamp'))); 
  416. $year = date('Y', strtotime('-4 MONTH', current_time('timestamp'))); 
  417. break; 
  418. case 'previous_period': 
  419. $month = date('m', strtotime('-2 MONTH', current_time('timestamp'))); 
  420. $year = date('Y', strtotime('-2 MONTH', current_time('timestamp'))); 
  421. break; 
  422. case 'current_period': 
  423. $month = date('m', current_time('timestamp')); 
  424. $year = date('Y', current_time('timestamp')); 
  425. break; 
  426. default: 
  427. parent::calculate_current_range($current_range); 
  428. return; 
  429. break; 
  430.  
  431. if($month <= 2) { 
  432. $this->start_date = strtotime($year . '-01-01'); 
  433. $this->end_date = strtotime(date('Y-m-t', strtotime($year . '-02-01'))); 
  434. elseif($month > 2 && $month <= 4) { 
  435. $this->start_date = strtotime($year . '-03-01'); 
  436. $this->end_date = strtotime(date('Y-m-t', strtotime($year . '-04-01'))); 
  437. elseif($month > 4 && $month <= 6) { 
  438. $this->start_date = strtotime($year . '-05-01'); 
  439. $this->end_date = strtotime(date('Y-m-t', strtotime($year . '-06-01'))); 
  440. elseif($month > 6 && $month <= 8) { 
  441. $this->start_date = strtotime($year . '-07-01'); 
  442. $this->end_date = strtotime(date('Y-m-t', strtotime($year . '-08-01'))); 
  443. elseif($month > 8 && $month <= 10) { 
  444. $this->start_date = strtotime($year . '-09-01'); 
  445. $this->end_date = strtotime(date('Y-m-t', strtotime($year . '-10-01'))); 
  446. elseif($month > 10) { 
  447. $this->start_date = strtotime($year . '-11-01'); 
  448. $this->end_date = strtotime(date('Y-m-t', strtotime($year . '-12-01')));