AeliaWCEU_VAT_AssistantReportsWC21VIES_Report

Renders the VIES Report.

Defined (1)

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

/src/lib/classes/reporting/reports/WC21/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.4.1.150407 
  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}term_relationships AS rel ON 
  37. (rel.object_ID = orders.ID) 
  38. INNER JOIN 
  39. {$px}term_taxonomy AS taxonomy ON 
  40. (taxonomy.term_taxonomy_id = rel.term_taxonomy_id) AND 
  41. (taxonomy.taxonomy = 'shop_order_status') 
  42. INNER JOIN 
  43. {$px}terms AS term ON 
  44. (term.term_id = taxonomy.term_id) AND 
  45. (term.slug IN ('%s')) 
  46. INNER JOIN 
  47. {$px}woocommerce_order_items AS OI ON 
  48. (OI.order_id = ORDERS.ID) 
  49. INNER JOIN 
  50. {$px}woocommerce_order_itemmeta AS OIM ON 
  51. (OIM.order_item_id = OI.order_item_id) AND 
  52. (OIM.meta_key in ('%s')) 
  53. -- Get product data for simple (non variable) products 
  54. LEFT JOIN 
  55. {$px}woocommerce_order_itemmeta AS OIM2 ON 
  56. (OIM2.order_item_id = OI.order_item_id) AND 
  57. (OIM2.meta_key = '_product_id') 
  58. LEFT JOIN 
  59. {$px}postmeta AS PROD_META ON 
  60. (PROD_META.post_id = OIM2.meta_value) AND 
  61. (PROD_META.meta_key = '". Definitions::FIELD_VIES_PRODUCT_IS_SERVICE . "') 
  62. -- Get product data for variable products 
  63. LEFT JOIN 
  64. {$px}woocommerce_order_itemmeta AS OIM3 ON 
  65. (OIM3.order_item_id = OI.order_item_id) AND 
  66. (OIM3.meta_key = '_variation_id') 
  67. LEFT JOIN 
  68. {$px}postmeta AS VARIATION_META ON 
  69. (VARIATION_META.post_id = OIM3.meta_value) AND 
  70. (VARIATION_META.meta_key = '". Definitions::FIELD_VIES_PRODUCT_IS_SERVICE . "') 
  71. -- Fetch orders meta 
  72. INNER JOIN 
  73. {$px}postmeta AS ORDER_META1 ON 
  74. (ORDER_META1.post_id = ORDERS.ID) AND 
  75. (ORDER_META1.meta_key = '_eu_vat_evidence') 
  76. INNER JOIN 
  77. {$px}postmeta AS ORDER_META2 ON 
  78. (ORDER_META2.post_id = ORDERS.ID) AND 
  79. (ORDER_META2.meta_key = '_vat_number_validated') AND 
  80. (ORDER_META2.meta_value = '%s') 
  81. INNER JOIN 
  82. {$px}postmeta AS ORDER_META3 ON 
  83. (ORDER_META3.post_id = ORDERS.ID) AND 
  84. (ORDER_META3.meta_key = '_eu_vat_data') 
  85. INNER JOIN 
  86. {$px}postmeta AS ORDER_META4 ON 
  87. (ORDER_META4.post_id = ORDERS.ID) AND 
  88. (ORDER_META4.meta_key = '_order_currency') 
  89. WHERE 
  90. (ORDERS.post_type = 'shop_order') AND 
  91. (ORDERS.post_status = 'publish') AND 
  92. (ORDERS.post_date >= '" . date('Y-m-d', $this->start_date) . "') AND 
  93. (ORDERS.post_date < '" . date('Y-m-d', strtotime('+1 DAY', $this->end_date)) . "') 
  94. ",  
  95. implode("', '", $this->order_statuses_to_include(false)),  
  96. implode("', '", $meta_keys),  
  97. Definitions::VAT_NUMBER_VALIDATION_VALID 
  98. ); 
  99.  
  100. // Debug 
  101. //var_dump($SQL); 
  102. $dataset = $wpdb->get_results($SQL); 
  103.  
  104. // Debug 
  105. //var_dump("SALES DATA", $dataset);die(); 
  106. return $dataset;