CREATE 
    EVENT evn_stock_close
		ON SCHEDULE 
		EVERY 1 DAY
    DO
		update 	good_receipt_details INNER JOIN
				(
				Select	batch_number, item_id, item_name, sum(CASE 
													WHEN inout_status In ('R', 'T') THEN
														IFNULL(item_qty, 0)
													ELSE
														0
												END ) has,
												sum(CASE 
													WHEN inout_status In ('E', 'S') THEN
														IFNULL(item_qty, 0)
													ELSE
														0
												END ) gone
				FROM		good_receipt_details, items 
				WHERE		good_receipt_details.item_id = items.id
				AND		inout_status In ('R', 'T', 'E', 'S')
				AND		stock_close_date is NULL
				GROUP BY batch_number, item_id
				) batch_medicine
				ON 	good_receipt_details.batch_number = batch_medicine.batch_number AND 
						good_receipt_details.item_id = batch_medicine.item_id
SET			good_receipt_details.stock_close_date = CURRENT_TIMESTAMP
WHERE 		batch_medicine.has - batch_medicine.gone <= 0;