AeliaWCEU_VAT_AssistantReportsBase_VIES_Report

Base class for the VIES report.

Defined (1)

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

/src/lib/classes/reporting/reports/base/base_vies_report.php  
  1. abstract class Base_VIES_Report extends \Aelia\WC\EU_VAT_Assistant\Reports\Base_Sales_Report { 
  2. /** 
  3. * Indicates if the tax passed as a parameter should be skipped (i.e. excluded 
  4. * from the report). 
  5. * @param array tax_details An array of data describing a tax. 
  6. * @return bool True (tax should be excluded from the report) or false (tax 
  7. * should be displayed on the report). 
  8. */ 
  9. protected function should_skip($order_data) { 
  10. $eu_vat_evidence = maybe_unserialize(get_value('eu_vat_evidence', $order_data)); 
  11. // VIES reports must only include sales to the EU, excluding shop base 
  12. // country 
  13. if(empty($eu_vat_evidence['location']['is_eu_country']) || 
  14. ($eu_vat_evidence['location']['is_eu_country'] == false) || 
  15. ($eu_vat_evidence['location']['billing_country'] == $this->base_country())) { 
  16. return true; 
  17.  
  18. // VIES reports must only include sales to customers who entered a valid EU 
  19. // VAT number 
  20. if(empty($eu_vat_evidence['exemption']['vat_number']) || 
  21. empty($eu_vat_evidence['exemption']['vat_number_validated']) || 
  22. $eu_vat_evidence['exemption']['vat_number_validated'] !== Definitions::VAT_NUMBER_VALIDATION_VALID) { 
  23. return true; 
  24. return false; 
  25.  
  26. /** 
  27. * Creates the temporary table that will be used to generate the VIES report. 
  28. * @return string|bool The name of the created table, or false on failure. 
  29. * @since 1.3.20.150330 
  30. */ 
  31. protected function create_temp_vies_table() { 
  32. global $wpdb; 
  33.  
  34. $table_name = $wpdb->prefix . 'aelia_euva_vies_report'; 
  35. $sql = " 
  36. CREATE TEMPORARY TABLE IF NOT EXISTS `$table_name` ( 
  37. `row_id` INT NOT NULL AUTO_INCREMENT,  
  38. `order_id` INT NOT NULL,  
  39. `product_id` INT NOT NULL,  
  40. `billing_country` VARCHAR(5) NOT NULL,  
  41. `vat_number` VARCHAR(50) NOT NULL,  
  42. `line_total` DECIMAL(18, 6) NOT NULL,  
  43. `is_service` SMALLINT NOT NULL,  
  44. `is_triangulation` SMALLINT NOT NULL,  
  45. `exchange_rate` DECIMAL(18, 6) NOT NULL,  
  46. PRIMARY KEY (`row_id`),  
  47. INDEX `IX_ORDER_ID` (`order_id` ASC),  
  48. INDEX `IX_PRODUCT_ID` (`product_id` ASC) 
  49. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 
  50. "; 
  51.  
  52. return $this->create_temporary_table($table_name, $sql); 
  53.  
  54. /** 
  55. * Stores a row in the temporary table used to produce the VIES report. 
  56. * @since 1.3.20.150330 
  57. */ 
  58. protected function store_temp_vies_row(array $fields) { 
  59. global $wpdb; 
  60.  
  61. $table_name = $wpdb->prefix . 'aelia_euva_vies_report'; 
  62. $SQL = " 
  63. INSERT INTO `$table_name` ( 
  64. `order_id`,  
  65. `product_id`,  
  66. `billing_country`,  
  67. `vat_number`,  
  68. `line_total`,  
  69. `is_service`,  
  70. `is_triangulation`,  
  71. `exchange_rate` 
  72. VALUES ( 
  73. %d, -- Order ID 
  74. %d, -- Product ID 
  75. %s, -- Billing country 
  76. %s, -- VAT Number 
  77. %f, -- Line total 
  78. %d, -- 'Is service' flag 
  79. %d, -- 'Triangulation' flag 
  80. %f -- Exchange rate 
  81. "; 
  82.  
  83. $query = $wpdb->prepare( 
  84. $SQL,  
  85. $fields['order_id'],  
  86. $fields['product_id'],  
  87. $fields['billing_country'],  
  88. $fields['vat_number'],  
  89. $fields['line_total'],  
  90. $fields['is_service'],  
  91. $fields['is_triangulation'],  
  92. $fields['exchange_rate'] 
  93. ); 
  94.  
  95. // Debug 
  96. //var_dump($fields, $query);die(); 
  97.  
  98. // Save to database the IP data for the country 
  99. $rows_affected = $wpdb->query($query); 
  100.  
  101. $result = $rows_affected; 
  102. if($result == false) { 
  103. $error_message = sprintf(__('Could not store row in table "%s" rates. ' . 
  104. 'Fields (JSON): "%s".', $this->text_domain),  
  105. $table_name,  
  106. $fields); 
  107. $this->log($error_message, false); 
  108. trigger_error(E_USER_WARNING, $error_message); 
  109. return $result; 
  110.  
  111. protected function get_order_items_meta_keys() { 
  112. return array( 
  113. // _line_total: total charged for order items 
  114. '_line_total',  
  115. // cost: total charged for shipping 
  116. 'cost',  
  117. ); 
  118.  
  119. /** 
  120. * Stores in a temporary table the data required to produce the VIES report. 
  121. * @param array dataset An array containing the data for the report. 
  122. * @return bool True if the data was stored correctly, false otherwise. 
  123. * @since 1.3.20.150402 
  124. */ 
  125. protected function store_report_data($dataset) { 
  126. foreach($dataset as $index => $entry) { 
  127. $entry->eu_vat_data = maybe_unserialize($entry->eu_vat_data); 
  128. $entry->eu_vat_evidence = maybe_unserialize($entry->eu_vat_evidence); 
  129.  
  130. if(!$this->should_skip($entry)) { 
  131. $vat_currency_exchange_rate = (float)get_value('vat_currency_exchange_rate', $entry->eu_vat_data); 
  132. if(!is_numeric($vat_currency_exchange_rate) || ($vat_currency_exchange_rate <= 0)) { 
  133. $this->log(sprintf(__('VAT currency exchange rate not found for order id "%s". ' . 
  134. 'Fetching exchange rate from FX provider.', $this->text_domain),  
  135. $entry->order_id)); 
  136. $vat_currency_exchange_rate = $this->get_vat_currency_exchange_rate($entry->order_currency,  
  137. $entry->order_date); 
  138.  
  139. $fields = array( 
  140. 'order_id' => $entry->order_id,  
  141. 'product_id' => $entry->product_id,  
  142. 'billing_country' => $entry->eu_vat_evidence['location']['billing_country'],  
  143. 'vat_number' => $entry->eu_vat_evidence['exemption']['vat_number'],  
  144. 'line_total' => $entry->line_total,  
  145. 'is_service' => $entry->is_service,  
  146. 'is_triangulation' => $entry->is_triangulation,  
  147. 'exchange_rate' => $vat_currency_exchange_rate,  
  148. ); 
  149.  
  150. // Debug 
  151. //var_dump($fields);die(); 
  152.  
  153. if(!$this->store_temp_vies_row($fields)) { 
  154. return false; 
  155. return true; 
  156.  
  157. /** 
  158. * Returns the sales data that will be included in the report. This method must 
  159. * be implemented by descendant classes. 
  160. * @return array 
  161. */ 
  162. protected function get_sales_data() { 
  163. return array(); 
  164.  
  165. /** 
  166. * Returns the refunds data that will be included in the report. This method 
  167. * is empty for compatibility with WooCommerce 2.1, which doesn't handle 
  168. * refunds. Classes designed for WooCommerce 2.2 and later will take care of 
  169. * fetching the refunds. 
  170. * @return array 
  171. * @since 1.3.20.150330 
  172. */ 
  173. protected function get_refunds_data() { 
  174. return array(); 
  175.  
  176. /** 
  177. * Consolidates the sales data with the refunds data and returns it. 
  178. * @return array An array containing the consolidated sales and return data. 
  179. * @since 1.3.20.150330 
  180. */ 
  181. protected function get_vies_report_data() { 
  182. global $wpdb; 
  183.  
  184. $px = $wpdb->prefix; 
  185. $SQL = " 
  186. SELECT 
  187. VR.order_id 
  188. , VR.billing_country 
  189. , VR.vat_number 
  190. , VR.is_service 
  191. , VR.is_triangulation 
  192. , SUM(VR.line_total * VR.exchange_rate) AS line_total 
  193. FROM 
  194. {$px}aelia_euva_vies_report VR 
  195. GROUP BY 
  196. VR.vat_number 
  197. , VR.is_service 
  198. , VR.is_triangulation 
  199. HAVING 
  200. -- Discard rows with zero, they don't need to be added to the report. 
  201. -- We can't just use 'greater than zero' as a criteria, because rows 
  202. -- with negative values must be included 
  203. line_total <> 0 
  204. "; 
  205.  
  206. // Debug 
  207. //var_dump($SQL); 
  208. $dataset = $wpdb->get_results($SQL); 
  209.  
  210. // Debug 
  211. //var_dump("REFUNDS RESULT", $dataset); 
  212. return $dataset; 
  213.  
  214. /** 
  215. * Loads and returns the report data. 
  216. * @return array An array with the report data. 
  217. * @since 1.3.20.150402 
  218. */ 
  219. protected function get_report_data() { 
  220. if($result = $this->create_temp_vies_table()) { 
  221. // Retrieve and store sales data 
  222. $result = $this->store_report_data($this->get_sales_data()); 
  223.  
  224. // Retrieve and store refunds data 
  225. if($result) { 
  226. $result = $this->store_report_data($this->get_refunds_data()); 
  227.  
  228. if($result) { 
  229. // Prepare a summary for the VIES report and return it 
  230. $result = $this->get_vies_report_data(); 
  231. return $result; 
  232.  
  233. if(!$result) { 
  234. trigger_error(E_USER_WARNING, __('Could not prepare temporary table for the report. ' . 
  235. 'Please enable debug mode and tru again. If the issue ' . 
  236. 'persists, contact support and forward them the debug ' . 
  237. 'log produced by the plugin. For more information, please ' . 
  238. 'go to WooCommerce > EU VAT Assistant > Support.',  
  239. $this->text_domain)); 
  240.  
  241. /** 
  242. * Get the data for the report. 
  243. * @return string 
  244. */ 
  245. public function get_main_chart() { 
  246. $vies_report_data = $this->get_report_data(); 
  247.  
  248. // Keep track of the report columns. This information will be used to adjust 
  249. // the "colspan" property 
  250. $report_columns = 6; 
  251. ?> 
  252. <div id="vies_report" class="wc_aelia_eu_vat_assistant report"> 
  253. <table class="widefat"> 
  254. <thead> 
  255. <tr class="report_information"> 
  256. <th colspan="<?php echo $report_columns; ?>"> 
  257. <ul> 
  258. <li> 
  259. <span class="label"><?php 
  260. echo __('Currency for VAT returns:', $this->text_domain); 
  261. ?></span> 
  262. <span><?php echo $this->vat_currency(); ?></span> 
  263. </li> 
  264. <li> 
  265. <span class="label"><?php 
  266. echo __('Exchange rates used:', $this->text_domain); 
  267. ?></span> 
  268. <span><?php 
  269. if($this->should_use_orders_exchange_rates()) { 
  270. echo __('Rates saved with each order', $this->text_domain); 
  271. else { 
  272. echo __('ECB rates for each quarter', $this->text_domain); 
  273. ?></span> 
  274. </li> 
  275. </ul> 
  276. </th> 
  277. </tr> 
  278. <tr class="column_headers"> 
  279. <th class="order_id"><?php echo __('Order ID', $this->text_domain); ?></th> 
  280. <th class="billing_country"><?php echo __('Customer country', $this->text_domain); ?></th> 
  281. <th class="vat_number"><?php echo __('Customer VAT Number', $this->text_domain); ?></th> 
  282. <th class="total_sales"><?php echo __('Sales', $this->text_domain); ?></th> 
  283. <th class="is_service"><?php echo __('Services', $this->text_domain); ?></th> 
  284. <th class="is_triangulation"><?php echo __('Triangulation', $this->text_domain); ?></th> 
  285. </tr> 
  286. </thead> 
  287. <?php if(empty($vies_report_data)) : ?> 
  288. <tbody> 
  289. <tr> 
  290. <td colspan="<?php echo $report_columns; ?>"><?php echo __('No sales falling under VIES scheme have been found.', $this->text_domain); ?></td> 
  291. </tr> 
  292. </tbody> 
  293. <?php else : ?> 
  294. <tbody> 
  295. <?php 
  296.  
  297. $sales_total = 0; 
  298.  
  299. // First loop - Tax groups (MOSS and non-MOSS) 
  300. foreach($vies_report_data as $entry_id => $entry) { 
  301. $sales_total += $entry->line_total; 
  302. ?> 
  303. <tr> 
  304. <th class="order_id"><?php echo $entry->order_id; ?></th> 
  305. <th class="billing_country"><?php echo $entry->billing_country; ?></th> 
  306. <th class="vat_number"><?php echo $entry->vat_number; ?></th> 
  307. <th class="total_sales"><?php echo $this->format_price($entry->line_total); ?></th> 
  308. <th class="is_service"><?php echo (int)$entry->is_service; ?></th> 
  309. <th class="is_triangulation"><?php echo (int)$entry->is_triangulation; ?></th> 
  310. </tr> 
  311. <?php 
  312. } // First loop - END 
  313. ?> 
  314. </tbody> 
  315. <tfoot> 
  316. <tr> 
  317. <th class="label"><?php echo __('Total', $this->text_domain); ?></th> 
  318. <th class="total"><?php echo $this->format_price($sales_total); ?></th> 
  319. <th colspan="4"></th> 
  320. </tr> 
  321. </tfoot> 
  322. <?php endif; ?> 
  323. </table> 
  324. </div> 
  325. <?php 
  326.  
  327. /** 
  328. * Renders a header on top of the standard reporting UI. 
  329. */ 
  330. protected function render_ui_header() { 
  331. // The VIES report does not require a header