DROP PROCEDURE sp_billwise_medicine;

CREATE PROCEDURE sp_billwise_medicine(a_bill_no VARCHAR(50))
BEGIN
	SET SESSION sql_mode = "";
	SELECT	pat.id as patient_id,
			pat.patient_unique_id as patient_unique_id,
			pat.patient_name as patient_name,
			pat.age as age,
			pat.gender as gender,
			pat.marital_status as marital_status,
			pat.blood_group as blood_group,
			pbh.id as pharmacy_billing_header_id,
			pbh.bill_no as bill_no,
			pbh.doctor_id as doctor_id,
			pbh.doctor_name as doctor_name,
			/*
			OPD, IPD and OT Details
			*/
			total_gross_amt as total_gross_amt,
			total_sgst_amount as total_sgst_amount,
			total_cgst_amount as total_cgst_amount,
			discount_amt as discount_amt,
			total_net_amount as total_net_amount,
			ifnull(return_amount, 0.00)  as return_amount,
			ifnull(refund_amount, 0.00) as refund_amount,
			ifnull(due_amount, 0.00) as due_amount,
			ifnull(paid_amount, 0.00) as paid_amount,	
			ifnull(pbh.total_round_amount, 0.00) as sell_round_amount,
			(
			Select JSON_ARRAYAGG(JSON_OBJECT(
				'pharmacy_billing_item_id', pharmacy_billing_item_id,
				'item_id', item_id,
				'tax_group_id', tax_group_id, 
				'tot_tax_perc', tot_tax_perc,
				'item_name', item_name,
				'batch_no', batch_no,
				'expire_date', expire_date,
				'mrp', mrp,
				'item_loose_sale_allowed', is_loose_sale_allowed, 
				'is_loose_sold', is_loose_sold,
				'strip_qty', strip_qty,
				'return_strip_qty', return_strip_qty,
				'return_loose_quantity', return_loose_quantity,
				'available_quantity', available_quantity,
				'available_loose_quantity', available_loose_quantity,
				'sale_price', sale_price,
				'sell_quantity', sale_quantity,
				'gross_sale_price', gross_sale_price,
				'unit_price', unit_price,
				'cgst_amt', cgst_amt,
				'sgst_amt', sgst_amt,
				'net_sale_price', net_sale_price,
				'discount_value', discount_value,
				'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,
				'return_round_amount', return_round_amount
				)
					)
			From (
					Select			pbi.id as pharmacy_billing_item_id,
									pbi.item_id,
									pbi.tax_group_id, 
									it.item_name,
									pi.is_loose_sale_allowed,
									pbi.batch_no,
									pbi.expire_date,
									pbi.mrp,
									pbi.is_loose_sold,
									pbi.strip_qty,
									pbi.sale_price,
									pbi.sale_quantity,	
									gross_sale_price,
									pbi.unit_price,
									pbi.cgst_amt,
									pbi.sgst_amt,
									pbi.net_sale_price,
									pbi.discount_value,
									tax.tot_tax_perc,
									/*
									max(pri.strip_qty) return_strip_qty,
									sum(pri.return_quantity) return_quantity, 
									sum(ifnull(pri.return_loose_quantity, 0)) return_loose_quantity, 
									sum(pri.available_quantity) available_quantity, 
									sum(ifnull(pri.available_loose_quantity, 0)) available_loose_quantity, 
									sum(ifnull(prh.total_round_amount, 0)) return_round_amount,
									sum(pri.discount_amount) return_discount_amount, 
									sum(pri.gross_return_price) return_gross_price,
									sum(pri.cgst_amt) return_cgst,
									sum(pri.sgst_amt) return_sgst,
									sum(pri.net_return_price) return_net_price
									*/
									return_strip_qty,
									return_quantity,
									return_loose_quantity,
									available_quantity,
									available_loose_quantity,
									return_round_amount,
									return_discount_amount,
									return_gross_price,
									return_cgst,
									return_sgst,
									return_net_price

					from 		pharmacy_billing_header pbhj
					inner join 	pharmacy_billing_item pbi on pbhj.id = pbi.pharmacy_billing_header_id
					inner join	items it on pbi.item_id = it.id
					inner join 	pharmacy_items pi on pi.item_id = it.id
					inner join 	(Select tax_group_id, 
										GROUP_CONCAT(CONCAT(tax_group_category, '-',tax_gpercentage)) tot_tax_perc
								FROM		tax_group_detail group by tax_group_id) tax on tax.tax_group_id = pbi.tax_group_id

					left join 	vw_pharmacy_return_details	prd on (prd.pharmacy_billing_header_id = pbi.pharmacy_billing_header_id AND prd.pharmacy_billing_item_id = pbi.id)

					/*
					left join	pharmacy_return_header prh	on prh.pharmacy_billing_header_id = pbhj.id AND prh.status = 'A'
					left join pharmacy_return_items pri on (pri.pharmacy_billing_header_id = pbi.pharmacy_billing_header_id
										and	pri.pharmacy_billing_item_id = pbi.id and pri.status IN ('A', 'Y'))
					*/

					where	pbhj.id = pbh.id
					group by pbi.id, pbi.item_id, it.item_name, pbi.tax_group_id, pi.is_loose_sale_allowed
			) pharmacy_billing
		) as billing_items
		FROM	(
				select 'E' patient_type, id, patient_unique_id, patient_name, age, gender, marital_status, blood_group
				from patients
				union
				select 'W', id, '', patient_name, 0, '', '', ''
				from pharmacy_walkin_patient
				) pat, pharmacy_billing_header pbh
	WHERE	pat.id = pbh.patient_id
	AND		pat.patient_type = pbh.patient_type
	AND		pbh.bill_no = a_bill_no;
END;