DROP PROCEDURE sp_stock_item_consumption;

CREATE PROCEDURE sp_stock_item_consumption(a_departmentwise_consumptions_id int)
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE	v_item_id INT;
	DECLARE	v_bal_date DATE;
	DECLARE	v_quantity_issue DECIMAL(12, 2);
	DECLARE	v_prev_quantity_issue DECIMAL(12, 2);
	DECLARE	v_prev_quantity_req	DECIMAL(12, 2);
	DECLARE	v_prev_quantity_balance	DECIMAL(12, 2);
	DECLARE	v_issue_qty	DECIMAL(12, 2);

	DECLARE cur_departmentwise_consumption CURSOR FOR 
		SELECT	item_id, consumption_quantity
		FROM	departmentwise_consumptions dc, departmentwise_consumption_items dci
		WHERE	dc.id = dci.departmentwise_consumption_id
		AND		dc.id = a_departmentwise_consumptions_id
		AND		status = 'U'
		AND		is_deleted = 'N';

	/*DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  */
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	OPEN cur_departmentwise_consumption; 
	read_loop: LOOP
		FETCH	cur_departmentwise_consumption	INTO	v_item_id,	v_quantity_issue;
		IF done = 1 THEN 
			LEAVE read_loop;
		END IF;

		SELECT 	Max(stock_date)
		INTO	v_bal_date
		FROM	stock_item_requisition
		WHERE	item_id = v_item_id;

		IF v_bal_date IS NULL THEN
			SET	v_prev_quantity_balance = 0.00;
			SET v_prev_quantity_req	= 0.00;
			SET v_prev_quantity_issue = 0.00;
		ELSE
			SELECT	tot_quantity, req_qty, issue_qty
			INTO	v_prev_quantity_balance, v_prev_quantity_req, v_prev_quantity_issue
			FROM	stock_item_requisition
			WHERE	item_id = v_item_id
			AND		stock_date = v_bal_date;
		END IF;

		IF CURRENT_DATE = v_bal_date THEN
			UPDATE 	stock_item_requisition
			SET		issue_qty = issue_qty + v_quantity_issue,
					tot_quantity = tot_quantity - v_quantity_issue
			WHERE	item_id = v_item_id
			AND		stock_date = v_bal_date;
		ELSE
			INSERT INTO	stock_item_requisition
			(stock_date, item_id, req_qty, 
			issue_qty, tot_quantity)
			SELECT	CURRENT_DATE, v_item_id, v_prev_quantity_req, 
			v_prev_quantity_issue + v_quantity_issue, v_prev_quantity_balance - v_quantity_issue;
		END IF;
	END LOOP read_loop;

	CLOSE cur_departmentwise_consumption; 
END;