
SET @from_date= CURRENT_DATE;
SET @to_date= CURRENT_DATE;

SELECT 
	DATE(purchase_order_returns.return_date) as date,
	suppliers.supplier_name,
	NULL as opd_ipd_ot_flag,
	items.item_name,
	item_sub_category.sub_category_name,
	items.indv_item_qty,
	items.pack_size,
	SUM(purchase_order_return_items.quantity) as whole_quantity,
	0 as loose_quantity
	
FROM 
	purchase_order_return_items

INNER JOIN 
	purchase_order_returns 
	ON purchase_order_returns.id=purchase_order_return_items.purchase_order_return_id
	AND purchase_order_returns.`status`='A'
	
INNER JOIN 
	purchase_order_receipts 
	ON  purchase_order_receipts.id=purchase_order_returns.po_receipt_id
	AND purchase_order_receipts.`status`='A'

INNER JOIN 
	items 
	ON items.id=purchase_order_return_items.item_id
	AND items.is_active='Y'

LEFT JOIN 
	item_sub_category 
	ON item_sub_category.id=items.item_sub_category
	
LEFT JOIN 
	suppliers 
	ON suppliers.id=purchase_order_receipts.supplier_id
	
WHERE
	purchase_order_return_items.`status`='Y'
	AND DATE(purchase_order_returns.return_date)>=@from_date
	AND DATE(purchase_order_returns.return_date)<=@to_date
	
GROUP BY
	purchase_order_returns.return_date,
	suppliers.supplier_name,
	items.item_name,
	item_sub_category.sub_category_name,
	items.indv_item_qty,
	items.pack_size
	
	UNION
	
	SELECT 
	DATE(pharmacy_billing_header.bill_date) as date,
	vw_pharmacy_patient.patient_name,
	pharmacy_billing_header.opd_ipd_ot_flag,
	items.item_name,
	item_sub_category.sub_category_name,
	items.indv_item_qty,
	items.pack_size,
	IF(pharmacy_billing_item.is_loose_sold='N', SUM(pharmacy_billing_item.sale_quantity), 0) as whole_quantity,
	IF(pharmacy_billing_item.is_loose_sold='Y', SUM(pharmacy_billing_item.sale_quantity), 0) as loose_quantity
	
FROM 
	pharmacy_billing_item

INNER JOIN 
	pharmacy_billing_header 
	ON pharmacy_billing_header.id=pharmacy_billing_item.pharmacy_billing_header_id
	AND (pharmacy_billing_header.`status`='B' OR pharmacy_billing_header.`status`='P')

INNER JOIN 
	vw_pharmacy_patient 
	ON vw_pharmacy_patient.id=pharmacy_billing_header.patient_id
	AND CASE 
		WHEN pharmacy_billing_header.patient_type='W' THEN vw_pharmacy_patient.patient_type='W'
		WHEN pharmacy_billing_header.patient_type='E' THEN vw_pharmacy_patient.patient_type='E'
	END
	

INNER JOIN 
	items 
	ON items.id=pharmacy_billing_item.item_id
	AND items.is_active='Y'

LEFT JOIN 
	item_sub_category 
	ON item_sub_category.id=items.item_sub_category
	
WHERE 
	pharmacy_billing_item.`status`='A'
	AND DATE(pharmacy_billing_header.bill_date)>=@from_date
	AND DATE(pharmacy_billing_header.bill_date)<=@to_date

GROUP BY
	pharmacy_billing_header.bill_date,
	vw_pharmacy_patient.patient_name,
	pharmacy_billing_header.opd_ipd_ot_flag,
	items.item_name,
	item_sub_category.sub_category_name,
	items.indv_item_qty,
	items.pack_size,
	pharmacy_billing_item.is_loose_sold
	