DROP PROCEDURE if EXISTS sp_purchase_billwise_summery;
 /*Proc to generate purchase and return summery*/
CREATE PROCEDURE sp_purchase_billwise_summery(a_POR_ID int)
BEGIN

	SET SESSION sql_mode = "";
	SELECT	por_1.supplier_id as supplier_id,
			sp.supplier_code as supplier_code,
			sp.supplier_name as supplier_name, 
			sp.phone as supplier_phone,
			sp.email as supplier_email,
			sp.address as supplier_address,
			sp.contact_person_name as contact_person_name,
			sp.contact_person_phone as contact_person_phone,
			sp.contact_person_email as contact_person_email,
			por_1.purchase_date as purchase_date,
			por_1.purchase_order_id as purchase_order_id,
			por_1.po_receipt_number as po_receipt_number, 
			por_1.bill_no as bill_no,
			por_1.gross_price as gross_price,
			por_1.discount_percentage as discount_percentage,
			por_1.discount_amount as discount_amount,
			por_1.total_cgst as total_cgst,
			por_1.total_sgst as total_sgst,
			por_1.net_price as net_price,
			por_1.status as status,
			por_1.return_amount as	return_amount,
			por_1.due_amount as	due_amount,
			
			(
				Select JSON_ARRAYAGG(JSON_OBJECT(	
					'item_id', item_id,
					'item_name', item_name,
					'batch_no', batch_no,
					'expire_date', expiry_date,
					'receive_quantity', receive_quantity,
					'pack_size', pack_size,
					'strip_size', indv_item_qty,
					'mrp', mrp,
					'receive_price', receive_price,
					'discount_amount', discount_amount,
					'cgst_amt', cgst,
					'sgst_amt', sgst,
					'net_price', net_price,
					
					'return_quantity', return_quantity, 
					'return_discount_amount', return_discount_amount,
					'return_gross_price', return_gross_price,
					'return_cgst', return_cgst,
					'return_sgst', return_sgst,
					'return_net_price', return_net_price
					)
				)
				From (
						Select	pori.item_id,
										it.item_name,
										pori.batch_no,
										pori.expiry_date,
										receive_quantity,
										pori.pack_size,
										indv_item_qty,
										pori.mrp,
										pori.receive_price,
										pori.discount_amount,
										pori.cgst,
										pori.sgst,
										pori.net_price,

										sum(po_ret.quantity) return_quantity, 
										sum(po_ret.discount_amount) return_discount_amount, 
										sum(po_ret.return_price) return_gross_price,
										sum(po_ret.cgst) return_cgst,
										sum(po_ret.sgst) return_sgst,
										sum(po_ret.net_price) return_net_price
						from 		purchase_order_receipts por
						inner join 	purchase_order_receipt_items pori on por.id = pori.po_receipt_id
						inner join	items it on pori.item_id = it.id

						left join	purchase_order_return_items po_ret on (po_ret.po_receipt_id = por.id and po_ret.po_receipt_item_id = pori.id and por.status = 'A')
						left join	purchase_order_returns po_return on  (po_return.id = po_ret.purchase_order_return_id  AND po_return.status = 'A')

						where	por.id = a_POR_ID
						AND		pori.status in ('A', 'Y')
						group by pori.item_id
				)	purchase_billing
		)	as 	purchase_billing_item
		FROM
		purchase_order_receipts por_1, suppliers sp 
		WHERE	por_1.supplier_id = sp.id
		and por_1.id = a_POR_ID;
END;