salix/db/routines/srt/procedures/moving_groupOut.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `srt`.`moving_groupOut`()
proc: BEGIN
	
	DECLARE vDayMinute INT;
	DECLARE vRouteFk INT;
	DECLARE vBufferToFk INT;
	DECLARE vBufferFromFk INT;
	DECLARE vBufferGroupFk INT;
	DECLARE vDone INT DEFAULT FALSE;
	DECLARE cur1 CURSOR FOR  
		SELECT DISTINCT b.id
			FROM srt.buffer b
				JOIN srt.bufferState bs ON bs.id = b.stateFk 
				JOIN srt.bufferType bt ON bt.id = b.typeFk 
				JOIN srt.bufferGroup bg ON bg.bufferFk = b.id
				LEFT JOIN srt.moving m ON m.bufferToFk = b.id
			WHERE bs.description = 'FREE'
				AND b.isActive 
				AND bt.typeName = 'STRAPPING';

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;

	IF (SELECT (isAuto OR isRandomMoving) FROM srt.config) THEN 
	
		LEAVE proc;
	
	END IF;

	CASE (SELECT groupingMode FROM srt.config)
	
		WHEN 'TRUCK' THEN

			SELECT min(ev.dayMinute) INTO vDayMinute 
				FROM srt.expeditionVolume ev 
				WHERE ev.isReady
					AND ev.dayMinute
					AND ev.bufferTruck;
			
			IF vDayMinute THEN
			
				SELECT min(ev.bufferTruck) INTO vBufferToFk
					FROM srt.expeditionVolume ev 
					WHERE ev.dayMinute = vDayMinute;
			
				UPDATE srt.buffer 
					SET dayMinute = vDayMinute
					WHERE id = vBufferToFk;
				
				CALL srt.expedition_groupOut(vDayMinute,vBufferToFk);
			
			END IF;
		
		WHEN 'ROUTE' THEN
		
			SELECT min(rv.dayMinute) INTO vDayMinute 
				FROM srt.routeVolume rv 
					LEFT JOIN srt.buffer b ON b.routeFk = rv.routeFk
				WHERE rv.isReady
					AND rv.dayMinute
					AND rv.bufferTruck
					AND ISNULL(b.id);
			
			IF vDayMinute THEN
			
				SELECT min(ev.bufferTruck) INTO vBufferToFk
					FROM srt.expeditionVolume ev 
					WHERE ev.dayMinute = vDayMinute;
			
				SELECT rv.routeFk INTO vRouteFk
					FROM srt.routeVolume rv 
					WHERE rv.dayMinute = vDayMinute
						AND rv.isReady
					ORDER BY dayMinute, cajas DESC
					LIMIT 1;
				
				UPDATE srt.buffer 
					SET routeFk = vRouteFk
					WHERE id = vBufferToFk;
				
				CALL srt.expedition_routeOut(vRouteFk,vBufferToFk);
			
			END IF;
		
		WHEN 'BUFFER' THEN
		
			SELECT rv.dayMinute, rv.routeBuffer 
				INTO vDayMinute, vBufferToFk
				FROM srt.routeVolume rv 
				WHERE rv.isReady
					AND rv.dayMinute
					AND rv.routeBuffer
				ORDER BY rv.dayMinute
				LIMIT 1;
			
			IF vDayMinute THEN
				
				CALL srt.expedition_bufferOut(vBufferToFk);
			
			END IF;
		
		WHEN 'BUFFERGROUP' THEN
		
			-- En el caso de que haya cajas en buffers auxiliares y los principales esten libres, reenvia cajas
		
			OPEN cur1;

			read_loop: LOOP

			FETCH cur1 INTO vBufferToFk;

				IF vDone THEN
					LEAVE read_loop;
				END IF;
		
				SELECT bg.`group` INTO vBufferGroupFk
					FROM srt.bufferGroup bg 
					WHERE bg.bufferFk = vBufferToFk;

				SELECT MAX(e.bufferFk) INTO vBufferFromFk
				FROM srt.expedition e 
					JOIN srt.expeditionState es ON es.id = e.stateFk 
					JOIN srt.bufferGroup bg ON bg.bufferFk = e.bufferFk 
					LEFT JOIN srt.moving m ON m.bufferToFk = e.bufferFk 
					LEFT JOIN srt.moving mo ON mo.bufferFromFk = e.bufferFk
					JOIN srt.buffer b on b.id = e.bufferFk 
					JOIN srt.bufferType bt on bt.id = b.typeFk 
				WHERE bg.`group` = vBufferGroupFk
					AND ISNULL(m.bufferToFk)
					AND ISNULL(mo.id)
					AND bt.typeName = 'ACCUMULATION';
				IF vBufferFromFk THEN
				
					CALL srt.buffer_giveBack(vBufferFromFk);
				
				END IF;
		
		  	END LOOP;

  		CLOSE cur1;
		
	END CASE;
	
END$$
DELIMITER ;