monthly-production.sql

SELECT
YEAR(stock_status_histories.created_at) as year,
MONTH(stock_status_histories.created_at) as month,

-- Total IGOR Amount Dresses
CAST(SUM(
	CASE
	WHEN manufacturers.id = 1 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN IFNULL(stock_payment_items.amount / 100, 0)
	ELSE 0
	END
) as SIGNED) AS igor_amount_dresses,

-- Count IGOR Dresses

SUM(
 CASE 
 	WHEN manufacturers.id = 1 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN 1
 	ELSE 0
 END
) AS igor_count_dresses,

-- Total TLV Amount Dresses
CAST(SUM(
	CASE
	WHEN manufacturers.id = 2 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN IFNULL(stock_payment_items.amount / 100, 0)
	ELSE 0
	END
) as SIGNED) AS tlv_amount_dresses,

-- Count TLV Dresses

SUM(
 CASE 
 	WHEN manufacturers.id = 2 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN 1
 	ELSE 0
 END
) AS tlv_count_dresses,

-- Total JERUSALEM Amount Dresses
CAST(SUM(
	CASE
	WHEN manufacturers.id = 3 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN IFNULL(stock_payment_items.amount / 100, 0)
	ELSE 0
	END
) as SIGNED) AS jerusalem_amount_dresses,

-- Count JERUSALEM Dresses

SUM(
 CASE 
 	WHEN manufacturers.id = 3 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN 1
 	ELSE 0
 END
) AS jerusalem_count_dresses,

-- Total NIKOLAI Amount Dresses
CAST(SUM(
	CASE
	WHEN manufacturers.id = 5 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN IFNULL(stock_payment_items.amount / 100, 0)
	ELSE 0
	END
) as SIGNED) AS nikolai_amount_dresses,

-- Count NIKOLAI Dresses

SUM(
 CASE 
 	WHEN manufacturers.id = 5 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN 1
 	ELSE 0
 END
) AS nikolai_count_dresses,

-- Total SASHA Amount Dresses
CAST(SUM(
	CASE
	WHEN manufacturers.id = 6 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN IFNULL(stock_payment_items.amount / 100, 0)
	ELSE 0
	END
) as SIGNED) AS sasha_amount_dresses,

-- Count SASHA Dresses

SUM(
 CASE 
 	WHEN manufacturers.id = 6 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN 1
 	ELSE 0
 END
) AS sasha_count_dresses,

-- Total SALE Amount
CAST(SUM(
	CASE
	WHEN orders.type = 0 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN IFNULL(stock_payment_items.amount / 100, 0)
	ELSE 0
	END
) as SIGNED) AS sale_amount,

-- Count SALE

SUM(
 CASE 
 	WHEN orders.type = 0 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN 1
 	ELSE 0
 END
) AS sale_count,

-- Total SAMPLE Amount
CAST(SUM(
	CASE
	WHEN orders.type = 5 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN IFNULL(stock_payment_items.amount / 100, 0)
	ELSE 0
	END
) as SIGNED) AS sample_amount,

-- Count SAMPLE

SUM(
 CASE 
 	WHEN orders.type = 5 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN 1
 	ELSE 0
 END
) AS sample_count,

-- Total GL SAMPLE Amount
CAST(SUM(
	CASE
	WHEN orders.type = 2 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN IFNULL(stock_payment_items.amount / 100, 0)
	ELSE 0
	END
) as SIGNED) AS gl_sample_amount,

-- Count GL SAMPLE

SUM(
 CASE 
 	WHEN orders.type = 2 AND products.type = 'wedding_dress' OR products.type = 'evening_dress' THEN 1
 	ELSE 0
 END
) AS gl_sample_count

FROM stocks
LEFT JOIN stock_status_histories on stocks.id = stock_status_histories.stock_id
LEFT JOIN order_products on order_products.id = stocks.order_product_id
LEFT JOIN products on products.id = stocks.product_id
LEFT JOIN orders on orders.id = order_products.order_id
LEFT JOIN manufacturers on manufacturers.id = stocks.manufacturer_id
LEFT JOIN stock_payments on stock_payments.manufacturer_id = manufacturers.id
LEFT JOIN stock_payment_items on stock_payment_items.stock_payment_id = stock_payments.id
WHERE stock_status_histories.stock_status_id in (3,5)
-- AND (DATE(stock_status_histories.created_at) BETWEEN DATE('2018-04-01') AND ('2018-05-01'))
AND stock_status_histories.id = (SELECT MIN(stock_status_histories.id) from stock_status_histories WHERE stock_id = stocks.id AND stock_status_histories.stock_status_id in (3,5))
GROUP BY YEAR(stocks.created_at), MONTH(stocks.created_at) order by `stocks`.`created_at` desc