AeliaWCEU_VAT_AssistantReportsWC22Sales_Summary_Report

Renders the report containing the details of all sales for each country in a specific period.

Defined (1)

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

/src/lib/classes/reporting/reports/WC22/sales_summary_report.php  
  1. class Sales_Summary_Report extends \Aelia\WC\EU_VAT_Assistant\Reports\Base_Sales_Summary_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. , ORDERS.post_type AS post_type 
  16. , DATE(ORDERS.post_date) AS order_date 
  17. , ORDER_META1.meta_value AS eu_vat_evidence 
  18. , ORDER_META3.meta_value AS eu_vat_data 
  19. , ORDER_META4.meta_value AS order_currency 
  20. -- Debug information 
  21. , ORDER_META2.meta_value AS vat_number_validated 
  22. , OI.order_item_id AS order_item_id 
  23. , OIM.meta_key AS line_item_key 
  24. , OIM.meta_value AS line_total 
  25. , OIM2.meta_value as line_tax 
  26. , IF((OIM.meta_value > 0) AND (OIM2.meta_key <> 'taxes'), ROUND(OIM2.meta_value / OIM.meta_value * 100, 2), -1) AS tax_rate 
  27. FROM 
  28. {$px}posts AS ORDERS 
  29. INNER JOIN 
  30. {$px}woocommerce_order_items AS OI ON 
  31. (OI.order_id = ORDERS.ID) 
  32. INNER JOIN 
  33. {$px}woocommerce_order_itemmeta AS OIM ON 
  34. (OIM.order_item_id = OI.order_item_id) AND 
  35. (OIM.meta_key in ('%s')) 
  36. LEFT JOIN 
  37. {$px}woocommerce_order_itemmeta AS OIM2 ON 
  38. (OIM2.order_item_id = OI.order_item_id) AND 
  39. (OIM2.meta_key IN ('_line_tax', 'taxes')) 
  40. -- Fetch orders meta 
  41. LEFT JOIN 
  42. {$px}postmeta AS ORDER_META1 ON 
  43. (ORDER_META1.post_id = ORDERS.ID) AND 
  44. (ORDER_META1.meta_key = '_eu_vat_evidence') 
  45. LEFT JOIN 
  46. {$px}postmeta AS ORDER_META2 ON 
  47. (ORDER_META2.post_id = ORDERS.ID) AND 
  48. (ORDER_META2.meta_key = '_vat_number_validated') 
  49. INNER JOIN 
  50. {$px}postmeta AS ORDER_META3 ON 
  51. (ORDER_META3.post_id = ORDERS.ID) AND 
  52. (ORDER_META3.meta_key = '_eu_vat_data') 
  53. INNER JOIN 
  54. {$px}postmeta AS ORDER_META4 ON 
  55. (ORDER_META4.post_id = ORDERS.ID) AND 
  56. (ORDER_META4.meta_key = '_order_currency') 
  57. WHERE 
  58. (ORDERS.post_type = 'shop_order') AND 
  59. (ORDERS.post_status IN ('%s')) AND 
  60. (ORDERS.post_date >= '" . date('Y-m-d', $this->start_date) . "') AND 
  61. (ORDERS.post_date < '" . date('Y-m-d', strtotime('+1 DAY', $this->end_date)) . "') 
  62. ",  
  63. implode("', '", $meta_keys),  
  64. implode("', '", $this->order_statuses_to_include(true))); 
  65.  
  66. // Debug 
  67. //var_dump($SQL); 
  68. $dataset = $wpdb->get_results($SQL); 
  69.  
  70. // Debug 
  71. //var_dump("SALES DATA", $dataset);die(); 
  72. return $dataset; 
  73.  
  74. /** 
  75. * Returns the refunds data that will be included in the report. 
  76. * @return array 
  77. * @since 1.3.20.150330 
  78. */ 
  79. protected function get_refunds_data() { 
  80. global $wpdb; 
  81.  
  82. $meta_keys = $this->get_order_items_meta_keys(); 
  83.  
  84. $px = $wpdb->prefix; 
  85. $SQL = sprintf(" 
  86. SELECT 
  87. REFUNDS.ID AS refund_id 
  88. , REFUNDS.post_type AS post_type 
  89. , DATE(REFUNDS.post_date) AS refund_date 
  90. , REFUNDS.post_parent AS order_id 
  91. , ORDER_META1.meta_value AS eu_vat_evidence 
  92. , ORDER_META3.meta_value AS eu_vat_data 
  93. , ORDER_META4.meta_value AS order_currency 
  94. -- Debug information 
  95. , ORDER_META2.meta_value AS vat_number_validated 
  96. , RI.order_item_id AS order_item_id 
  97. , RIM1.meta_key AS line_item_key 
  98. , RIM1.meta_value AS product_id 
  99. , RIM1.meta_value AS line_total 
  100. , RIM2.meta_value AS line_tax 
  101. , RIM3.meta_value AS refunded_order_item_id 
  102. , SALES.tax_rate AS tax_rate 
  103. FROM 
  104. {$px}posts AS REFUNDS 
  105. JOIN 
  106. {$px}posts AS ORDERS ON 
  107. (ORDERS.ID = REFUNDS.post_parent) 
  108. LEFT JOIN 
  109. -- Order Meta 
  110. {$px}postmeta AS ORDER_META1 ON 
  111. (ORDER_META1.post_id = REFUNDS.post_parent) AND 
  112. (ORDER_META1.meta_key = '_eu_vat_evidence') 
  113. -- Only include refunds related to orders with a valid EU VAT number 
  114. LEFT JOIN 
  115. {$px}postmeta AS ORDER_META2 ON 
  116. (ORDER_META2.post_id = REFUNDS.post_parent) AND 
  117. (ORDER_META2.meta_key = '_vat_number_validated') 
  118. INNER JOIN 
  119. {$px}postmeta AS ORDER_META3 ON 
  120. (ORDER_META3.post_id = REFUNDS.post_parent) AND 
  121. (ORDER_META3.meta_key = '_eu_vat_data') 
  122. INNER JOIN 
  123. {$px}postmeta AS ORDER_META4 ON 
  124. (ORDER_META4.post_id = REFUNDS.post_parent) AND 
  125. (ORDER_META4.meta_key = '_order_currency') 
  126. -- Refund items 
  127. JOIN 
  128. {$px}woocommerce_order_items RI ON 
  129. (RI.order_id = REFUNDS.ID) AND 
  130. (RI.order_item_type IN ('line_item', 'shipping')) 
  131. JOIN 
  132. -- Refund items meta - Find item/shipping refund amounts 
  133. {$px}woocommerce_order_itemmeta RIM1 ON 
  134. (RIM1.order_item_id = RI.order_item_id) AND 
  135. (RIM1.meta_key IN ('%s')) 
  136. LEFT JOIN 
  137. {$px}woocommerce_order_itemmeta AS RIM2 ON 
  138. (RIM2.order_item_id = RI.order_item_id) AND 
  139. (RIM2.meta_key IN ('_line_tax', 'taxes')) 
  140. LEFT JOIN 
  141. {$px}woocommerce_order_itemmeta AS RIM3 ON 
  142. (RIM3.order_item_id = RI.order_item_id) AND 
  143. (RIM3.meta_key = '_refunded_item_id') 
  144. LEFT JOIN 
  145. {$px}" . self::SALES_SUMMARY_REPORT_TEMP_TABLE . " AS SALES ON 
  146. (SALES.order_item_id = RIM3.meta_value) 
  147. WHERE 
  148. (REFUNDS.post_type IN ('shop_order_refund')) AND 
  149. -- The statuses to include always refer to the original orders. Refunds 
  150. -- are always in status 'wc-completed' 
  151. (ORDERS.post_status IN ('%s')) AND 
  152. (REFUNDS.post_date >= '" . date('Y-m-d', $this->start_date) . "') AND 
  153. (REFUNDS.post_date < '" . date('Y-m-d', strtotime('+1 DAY', $this->end_date)) . "') 
  154. ",  
  155. implode("', '", $meta_keys),  
  156. implode("', '", $this->order_statuses_to_include(true))); 
  157.  
  158. // Debug 
  159. //var_dump($SQL);die(); 
  160. $dataset = $wpdb->get_results($SQL); 
  161.  
  162. // Debug 
  163. //var_dump("REFUNDS RESULT", $dataset); 
  164. return $dataset;