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

SELECT
	purchase_order_receipts.purchase_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_receipt_items.receive_quantity) as whole_quantity,
	0 as loose_quantity
	

FROM 
	purchase_order_receipt_items

INNER JOIN 
	purchase_order_receipts 
	ON  purchase_order_receipt_items.po_receipt_id=purchase_order_receipts.id
	AND purchase_order_receipts.status='A'

INNER JOIN 
	items 
	ON items.id=purchase_order_receipt_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_receipt_items.`status`='Y'
	AND DATE(purchase_order_receipts.purchase_date)>=@form_date
	AND DATE(purchase_order_receipts.purchase_date)<=@to_date
	
GROUP BY
	purchase_order_receipts.purchase_date,
	suppliers.supplier_name,
	items.item_name,
	item_sub_category.sub_category_name,
	items.indv_item_qty,
	items.pack_size

UNION

SELECT 
	DATE(pharmacy_return_header.return_date) as date,
	vw_pharmacy_patient.patient_name,
	pharmacy_return_header.opd_ipd_ot_flag,
	items.item_name,
	item_sub_category.sub_category_name,
	items.indv_item_qty,
	items.pack_size,
	IF(pharmacy_return_items.is_loose_sold='N', SUM(pharmacy_return_items.return_quantity), 0) as whole_quantity,
	IF(pharmacy_return_items.is_loose_sold='Y', SUM(pharmacy_return_items.return_quantity), 0) as loose_quantity

FROM 
	pharmacy_return_items

INNER JOIN 
	pharmacy_return_header 
	ON pharmacy_return_header.id=pharmacy_return_items.pharmacy_return_header_id
	AND pharmacy_return_header.status='A'

INNER JOIN 
	vw_pharmacy_patient 
	ON vw_pharmacy_patient.id=pharmacy_return_header.patient_id
	AND CASE 
		WHEN pharmacy_return_header.opd_ipd_ot_flag='W' THEN vw_pharmacy_patient.patient_type='W'
		WHEN pharmacy_return_header.opd_ipd_ot_flag='O' THEN vw_pharmacy_patient.patient_type='E'
		WHEN pharmacy_return_header.opd_ipd_ot_flag='I' THEN vw_pharmacy_patient.patient_type='E'
		WHEN pharmacy_return_header.opd_ipd_ot_flag='T' THEN vw_pharmacy_patient.patient_type='E'
		END
	

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

LEFT JOIN 
	item_sub_category 
	ON item_sub_category.id=items.item_sub_category
	
WHERE 
	pharmacy_return_items.`status`='Y'
	AND DATE(pharmacy_return_header.return_date)>=@from_date
	AND DATE(pharmacy_return_header.return_date)<=@to_date

GROUP BY
	pharmacy_return_header.return_date,
	vw_pharmacy_patient.patient_name,
	pharmacy_return_header.opd_ipd_ot_flag,
	items.item_name,
	item_sub_category.sub_category_name,
	items.indv_item_qty,
	items.pack_size,
	pharmacy_return_items.is_loose_sold
	


			


