AeliaWCEU_VAT_AssistantReportsWC22VIES_Report

Renders the VIES Report.

Defined (1)

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

/src/lib/classes/reporting/reports/WC22/vies_report.php  
  1. class VIES_Report extends \Aelia\WC\EU_VAT_Assistant\Reports\Base_VIES_Report { 
  2. /** 
  3. * Returns the sales data that will be included in the report. 
  4. * @return array 
  5. * @since 1.3.20.150402 
  6. */ 
  7. protected function get_sales_data() { 
  8. global $wpdb; 
  9.  
  10. $meta_keys = $this->get_order_items_meta_keys(); 
  11. $px = $wpdb->prefix; 
  12. $SQL = sprintf(" 
  13. SELECT 
  14. ORDERS.ID AS order_id 
  15. , DATE(ORDERS.post_date) AS order_date 
  16. , ORDER_META1.meta_value AS eu_vat_evidence 
  17. , ORDER_META3.meta_value AS eu_vat_data 
  18. , ORDER_META4.meta_value AS order_currency 
  19. -- Debug information 
  20. -- , ORDER_META2.meta_value AS vat_number_validated 
  21. , OI.order_item_id 
  22. , OIM.meta_key AS line_item_key 
  23. , OIM2.meta_value as product_id 
  24. , OIM.meta_value AS line_total 
  25. , CASE 
  26. -- Shipping should always be considered a service 
  27. WHEN OI.order_item_type = 'shipping' THEN 1 
  28. -- For products, check if they have been set up to be a 
  29. -- service. If not, use a default value 
  30. ELSE IF(COALESCE(VARIATION_META.meta_value, PROD_META.meta_value, 'no') = 'yes', 1, 0) 
  31. END AS is_service 
  32. , 0 AS is_triangulation 
  33. FROM 
  34. {$px}posts AS ORDERS 
  35. INNER JOIN 
  36. {$px}woocommerce_order_items AS OI ON 
  37. (OI.order_id = ORDERS.ID) 
  38. INNER JOIN 
  39. {$px}woocommerce_order_itemmeta AS OIM ON 
  40. (OIM.order_item_id = OI.order_item_id) AND 
  41. (OIM.meta_key in ('%s')) 
  42. -- Get product data for simple (non variable) products 
  43. LEFT JOIN 
  44. {$px}woocommerce_order_itemmeta AS OIM2 ON 
  45. (OIM2.order_item_id = OI.order_item_id) AND 
  46. (OIM2.meta_key = '_product_id') 
  47. LEFT JOIN 
  48. {$px}postmeta AS PROD_META ON 
  49. (PROD_META.post_id = OIM2.meta_value) AND 
  50. (PROD_META.meta_key = '". Definitions::FIELD_VIES_PRODUCT_IS_SERVICE . "') 
  51. -- Get product data for variable products 
  52. LEFT JOIN 
  53. {$px}woocommerce_order_itemmeta AS OIM3 ON 
  54. (OIM3.order_item_id = OI.order_item_id) AND 
  55. (OIM3.meta_key = '_variation_id') 
  56. LEFT JOIN 
  57. {$px}postmeta AS VARIATION_META ON 
  58. (VARIATION_META.post_id = OIM3.meta_value) AND 
  59. (VARIATION_META.meta_key = '". Definitions::FIELD_VIES_PRODUCT_IS_SERVICE . "') 
  60. -- Fetch orders meta 
  61. INNER JOIN 
  62. {$px}postmeta AS ORDER_META1 ON 
  63. (ORDER_META1.post_id = ORDERS.ID) AND 
  64. (ORDER_META1.meta_key = '_eu_vat_evidence') 
  65. INNER JOIN 
  66. {$px}postmeta AS ORDER_META2 ON 
  67. (ORDER_META2.post_id = ORDERS.ID) AND 
  68. (ORDER_META2.meta_key = '_vat_number_validated') AND 
  69. (ORDER_META2.meta_value = '%s') 
  70. INNER JOIN 
  71. {$px}postmeta AS ORDER_META3 ON 
  72. (ORDER_META3.post_id = ORDERS.ID) AND 
  73. (ORDER_META3.meta_key = '_eu_vat_data') 
  74. INNER JOIN 
  75. {$px}postmeta AS ORDER_META4 ON 
  76. (ORDER_META4.post_id = ORDERS.ID) AND 
  77. (ORDER_META4.meta_key = '_order_currency') 
  78. WHERE 
  79. (ORDERS.post_type = 'shop_order') AND 
  80. (ORDERS.post_status IN ('%s')) AND 
  81. (ORDERS.post_date >= '" . date('Y-m-d', $this->start_date) . "') AND 
  82. (ORDERS.post_date < '" . date('Y-m-d', strtotime('+1 DAY', $this->end_date)) . "') 
  83. ",  
  84. implode("', '", $meta_keys),  
  85. Definitions::VAT_NUMBER_VALIDATION_VALID,  
  86. implode("', '", $this->order_statuses_to_include(true))); 
  87.  
  88. // Debug 
  89. //var_dump($SQL); 
  90. $dataset = $wpdb->get_results($SQL); 
  91.  
  92. // Debug 
  93. //var_dump("SALES DATA", $dataset);die(); 
  94. return $dataset; 
  95.  
  96. /** 
  97. * Returns the refunds data that will be included in the report. 
  98. * @return array 
  99. * @since 1.3.20.150330 
  100. */ 
  101. protected function get_refunds_data() { 
  102. global $wpdb; 
  103.  
  104. $meta_keys = $this->get_order_items_meta_keys(); 
  105.  
  106. $px = $wpdb->prefix; 
  107. $SQL = sprintf(" 
  108. SELECT 
  109. REFUNDS.ID AS refund_id 
  110. , DATE(REFUNDS.post_date) AS refund_date 
  111. , REFUNDS.post_parent AS order_id 
  112. , ORDER_META1.meta_value AS eu_vat_evidence 
  113. , ORDER_META3.meta_value AS eu_vat_data 
  114. , ORDER_META4.meta_value AS order_currency 
  115. , RI.order_item_id 
  116. , RIM1.meta_key AS line_item_key 
  117. , RIM1.meta_value AS product_id 
  118. , RIM2.meta_value AS line_total 
  119. , CASE 
  120. -- Shipping should always be considered a service 
  121. WHEN RI.order_item_type = 'shipping' THEN 1 
  122. -- For products, check if they have been set up to be a 
  123. -- service. If not, use a default value 
  124. ELSE IF(COALESCE(VARIATION_META.meta_value, PROD_META.meta_value, 'no') = 'yes', 1, 0) 
  125. END AS is_service 
  126. , 0 AS is_triangulation 
  127. FROM 
  128. {$px}posts AS REFUNDS 
  129. JOIN 
  130. {$px}posts AS ORDERS ON 
  131. (ORDERS.ID = REFUNDS.post_parent) 
  132. JOIN 
  133. -- Order Meta 
  134. {$px}postmeta AS ORDER_META1 ON 
  135. (ORDER_META1.post_id = REFUNDS.post_parent) AND 
  136. (ORDER_META1.meta_key = '_eu_vat_evidence') 
  137. -- Only include refunds related to orders with a valid EU VAT number 
  138. INNER JOIN 
  139. {$px}postmeta AS ORDER_META2 ON 
  140. (ORDER_META2.post_id = REFUNDS.post_parent) AND 
  141. (ORDER_META2.meta_key = '_vat_number_validated') AND 
  142. (ORDER_META2.meta_value = '%s') 
  143. INNER JOIN 
  144. {$px}postmeta AS ORDER_META3 ON 
  145. (ORDER_META3.post_id = REFUNDS.post_parent) AND 
  146. (ORDER_META3.meta_key = '_eu_vat_data') 
  147. INNER JOIN 
  148. {$px}postmeta AS ORDER_META4 ON 
  149. (ORDER_META4.post_id = REFUNDS.post_parent) AND 
  150. (ORDER_META4.meta_key = '_order_currency') 
  151.  
  152. -- Refund items 
  153. JOIN 
  154. {$px}woocommerce_order_items RI ON 
  155. (RI.order_id = REFUNDS.ID) AND 
  156. (RI.order_item_type IN ('line_item', 'shipping')) 
  157. JOIN 
  158. -- Get product data for simple (non-variable) products 
  159. {$px}woocommerce_order_itemmeta RIM1 ON 
  160. (RIM1.order_item_id = RI.order_item_id) AND 
  161. (RIM1.meta_key = '_product_id') 
  162. LEFT JOIN 
  163. -- Determine if the product is to be considered a service for VIES purposes 
  164. {$px}postmeta AS PROD_META ON 
  165. (PROD_META.post_id = RIM1.meta_value) AND 
  166. (PROD_META.meta_key = '". Definitions::FIELD_VIES_PRODUCT_IS_SERVICE . "') 
  167. -- Get product data for variable products 
  168. LEFT JOIN 
  169. {$px}woocommerce_order_itemmeta AS RIM3 ON 
  170. (RIM3.order_item_id = RI.order_item_id) AND 
  171. (RIM3.meta_key = '_variation_id') 
  172. LEFT JOIN 
  173. -- Determine if the variation is to be considered a service for VIES purposes 
  174. {$px}postmeta AS VARIATION_META ON 
  175. (VARIATION_META.post_id = RIM3.meta_value) AND 
  176. (VARIATION_META.meta_key = '". Definitions::FIELD_VIES_PRODUCT_IS_SERVICE . "') 
  177. JOIN 
  178. -- Refund items meta - Find item/shipping refund amounts 
  179. {$px}woocommerce_order_itemmeta RIM2 ON 
  180. (RIM2.order_item_id = RI.order_item_id) AND 
  181. (RIM2.meta_key IN ('%s')) 
  182. WHERE 
  183. (REFUNDS.post_type IN ('shop_order_refund')) AND 
  184. -- The statuses to include always refer to the original orders. Refunds 
  185. -- are always in status 'wc-completed' 
  186. (ORDERS.post_status IN ('%s')) AND 
  187. (REFUNDS.post_date >= '" . date('Y-m-d', $this->start_date) . "') AND 
  188. (REFUNDS.post_date < '" . date('Y-m-d', strtotime('+1 DAY', $this->end_date)) . "') 
  189. ",  
  190. Definitions::VAT_NUMBER_VALIDATION_VALID,  
  191. implode("', '", $meta_keys),  
  192. implode("', '", $this->order_statuses_to_include(true))); 
  193.  
  194. // Debug 
  195. //var_dump($SQL); 
  196. $dataset = $wpdb->get_results($SQL); 
  197.  
  198. // Debug 
  199. //var_dump("REFUNDS RESULT", $dataset); 
  200. return $dataset;