/*Purchase In Out Report ([Group Supplier, Item / All), - Date, Supplier Name, Item, qty, gst nd price for in and out) Filter - Date, Group type
*/
#group_type - I -Item and B-Bill

SET @group_type = 'I';
SELECT		por.purchase_date, por.bill_no,  
					CONCAT(s.supplier_name, '(', s.phone, ')') supplier_info, 
					CONCAT(s.contact_person_name, '(', s.contact_person_phone, ')') supplier_contact_info,
case when @group_type = 'I' then  item_name end, 
			sum(pori.net_price) net_purchase_price, sum(pori.cgst) + sum(pori.sgst) total_purchase_gst, 
			sum(po_ret.cgst) + sum(po_ret.sgst) return_gst, sum(po_ret.net_price) return_net_price

FROM		purchase_order_receipts por

INNER JOIN	purchase_order_receipt_items pori on pori.po_receipt_id = por.id
INNER JOIN	suppliers s on s.id = por.supplier_id
INNER JOIN	items it on it.id = pori.item_id
INNER JOIN	item_sub_category isc on isc.id = it.item_sub_category
LEFT JOIN	purchase_order_return_items po_ret on po_ret.po_receipt_item_id = pori.id and po_ret.po_receipt_id = por.id

WHERE	por.purchase_date >= {{start_date}}
AND		por.purchase_date <= {{end_date}}

Group by por.purchase_date, por.bill_no, supplier_info, supplier_contact_info, case when @group_type = 'I' then  item_name end;
