wordpress/woocommerce: bill data for artists. example december 2016 && exclude service email

bill_data.sql

CREATE TEMPORARY TABLE ALL_ORDERS_IN_GIVEN_TIMEFRAME (


		SELECT ALL_ORDERS_IN_GIVEN_TIMEFRAME_PLAIN.order_item_id, ALL_ORDERS_IN_GIVEN_TIMEFRAME_PLAIN.product_name, ALL_ORDERS_IN_GIVEN_TIMEFRAME_PLAIN.post_date, meta_value AS email  FROM (SELECT ITEMS.order_item_id, ITEMS.order_item_name AS product_name, ITEMS.order_id, MAIN_POSTS.post_date FROM wp_mokkamerch_posts AS MAIN_POSTS
			JOIN wp_PREFIX_woocommerce_order_items AS ITEMS ON MAIN_POSTS.ID = ITEMS.order_id 
			WHERE DATE(MAIN_POSTS.post_date) BETWEEN "2016-12-01" AND "2017-01-01" AND (MAIN_POSTS.post_status = "wc-completed" OR MAIN_POSTS.post_status = "wc-production") AND ITEMS.order_item_type = "line_item") AS ALL_ORDERS_IN_GIVEN_TIMEFRAME_PLAIN
		  JOIN wp_PREFIX_postmeta AS MAIN_POSTMETA ON ALL_ORDERS_IN_GIVEN_TIMEFRAME_PLAIN.order_id = MAIN_POSTMETA.post_id AND MAIN_POSTMETA.meta_key = "_billing_email" AND MAIN_POSTMETA.meta_value <> 'EXCLUDE@SERVICE-EMAIL.com'
);

CREATE TEMPORARY TABLE ITEMMETA_SUMMARY (
SELECT DISTINCT MAIN_ITEMMETA.order_item_id, PRODUCT_ITEMMETA.meta_value AS product_id, VARIATION_ITEMMETA.meta_value as variation_id, QTY_ITEMMETA.meta_value AS qty, (LINE_TOTAL.meta_value + LINE_TAX.meta_value) AS price 
	FROM `wp_PREFIX_woocommerce_order_itemmeta` AS MAIN_ITEMMETA 
	JOIN wp_PREFIX_woocommerce_order_itemmeta AS VARIATION_ITEMMETA ON MAIN_ITEMMETA.order_item_id = VARIATION_ITEMMETA.order_item_id AND VARIATION_ITEMMETA.meta_key = "_variation_id" 
	JOIN wp_PREFIX_woocommerce_order_itemmeta AS QTY_ITEMMETA ON MAIN_ITEMMETA.order_item_id = QTY_ITEMMETA.order_item_id AND QTY_ITEMMETA.meta_key = "_qty"
	JOIN wp_PREFIX_woocommerce_order_itemmeta AS LINE_TOTAL ON MAIN_ITEMMETA.order_item_id = LINE_TOTAL.order_item_id AND LINE_TOTAL.meta_key = "_line_total"
	JOIN wp_PREFIX_woocommerce_order_itemmeta AS LINE_TAX ON MAIN_ITEMMETA.order_item_id = LINE_TAX.order_item_id AND LINE_TAX.meta_key = "_line_tax"			
	JOIN wp_PREFIX_woocommerce_order_itemmeta AS PRODUCT_ITEMMETA ON MAIN_ITEMMETA.order_item_id = PRODUCT_ITEMMETA.order_item_id AND PRODUCT_ITEMMETA.meta_key = "_product_id"
);

-- artist data
CREATE TEMPORARY TABLE ARTIST_MEMORY_DATA (
	SELECT * FROM ITEMMETA_SUMMARY
		JOIN wp_PREFIX_term_relationships AS TERM_REL
			ON ITEMMETA_SUMMARY.product_id = TERM_REL.object_id
);

CREATE TEMPORARY TABLE ARTIST_SUMMARY (
	SELECT * FROM ARTIST_MEMORY_DATA
		JOIN wp_PREFIX_terms AS TERMS 
			ON ARTIST_MEMORY_DATA.term_taxonomy_id = TERMS.term_id
);
-- ALL artist data without human fails
SELECT * FROM ARTIST_SUMMARY;

-- FETCH ALL TERM_IDs for artist slugs
-- all artist data with potential human fails
CREATE TEMPORARY TABLE TERM_ID_ARTISTS (
	SELECT * FROM (SELECT ARTISTS.artist_name, TERMS.term_id, TERMS.name FROM wp_PREFIX_artists AS ARTISTS
		JOIN wp_PREFIX_terms AS TERMS
			ON ARTISTS.slug = TERMS.slug) AS SUB_TABLE
	JOIN wp_PREFIX_term_relationships AS TERM_REL
		ON SUB_TABLE.term_id = TERM_REL.term_taxonomy_id
);

SELECT * FROM TERM_ID_ARTISTS;

CREATE TEMPORARY TABLE ITEMMETA_SUMMARY_PLUS_ORDER_ID (
	SELECT ITEMMETA_SUMMARY.*, ORDER_ITEMS.order_id FROM ITEMMETA_SUMMARY 
		JOIN wp_PREFIX_woocommerce_order_items AS ORDER_ITEMS
		ON ITEMMETA_SUMMARY.order_item_id = ORDER_ITEMS.order_item_id
);

CREATE TEMPORARY TABLE ORDER_SPECIFIC_DATA (
	SELECT ALL_ORDERS_IN_GIVEN_TIMEFRAME.product_name, ALL_ORDERS_IN_GIVEN_TIMEFRAME.post_date, ALL_ORDERS_IN_GIVEN_TIMEFRAME.email, ITEMMETA_SUMMARY_PLUS_ORDER_ID.* FROM ALL_ORDERS_IN_GIVEN_TIMEFRAME 
		JOIN ITEMMETA_SUMMARY_PLUS_ORDER_ID 
		ON ALL_ORDERS_IN_GIVEN_TIMEFRAME.order_item_id = ITEMMETA_SUMMARY_PLUS_ORDER_ID.order_item_id
);
-- #83
SELECT * FROM ORDER_SPECIFIC_DATA;
SELECT * FROM ORDER_SPECIFIC_DATA WHERE variation_id = 0;

CREATE TEMPORARY TABLE POSTMETA_ROHARTIKEL_v2 (
	SELECT DISTINCT MAIN_POSTMETA.post_id AS variation_id, POSTMETA_ROHARTIKEL.meta_value AS rohartikel FROM wp_PREFIX_postmeta AS MAIN_POSTMETA
		JOIN wp_PREFIX_postmeta AS POSTMETA_ROHARTIKEL
			ON MAIN_POSTMETA.post_id = POSTMETA_ROHARTIKEL.post_id AND POSTMETA_ROHARTIKEL.meta_key = "_rohartikel_default"
);

SELECT * FROM POSTMETA_ROHARTIKEL_v2;

CREATE TEMPORARY TABLE ORDER_DATA_WITH_POSTMETA (
	SELECT ORDER_SPECIFIC_DATA.*, POSTMETA_ROHARTIKEL_v2.rohartikel FROM ORDER_SPECIFIC_DATA
	JOIN POSTMETA_ROHARTIKEL_v2
		ON ORDER_SPECIFIC_DATA.variation_id = POSTMETA_ROHARTIKEL_v2.variation_id
);

SELECT * FROM ORDER_DATA_WITH_POSTMETA;

CREATE TEMPORARY TABLE BILL_DATA (
	SELECT * FROM ORDER_DATA_WITH_POSTMETA
		JOIN wp_PREFIX_rohartikel AS ROHARTIKEL
		ON ORDER_DATA_WITH_POSTMETA.rohartikel = ROHARTIKEL.rohartikel_nr_extern
);

#81
SELECT * FROM BILL_DATA;

#72 = human fails
SELECT * FROM BILL_DATA
	JOIN TERM_ID_ARTISTS 
		ON BILL_DATA.product_id = TERM_ID_ARTISTS.object_id;