salix/db/routines/vn/procedures/collection_get.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`collection_get`(vWorkerFk INT)
BEGIN
/**
 * Obtiene colección del sacador si tiene líneas pendientes.
 *
 * @param vWorkerFk id del worker.
 * @table Devuelve tabla temporal con las colecciones pendientes
 */
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		RESIGNAL;
	END;

	CREATE OR REPLACE TEMPORARY TABLE tmp.collection
		ENGINE = MEMORY
		SELECT c.id collectionFk,
				DATE(c.created) created,
				COUNT(DISTINCT tc.ticketFk) ticketTotalCount
			FROM collection c 
					JOIN ticketCollection tc ON tc.collectionFk = c.id 
					JOIN sale s ON s.ticketFk = tc.ticketFk 
					JOIN ticketState ts ON ts.ticketFk = tc.ticketFk
					JOIN state s2 ON s2.id = ts.stateFk
					JOIN productionConfig pc
					JOIN vn.state ss ON ss.code = 'PREPARED'
					LEFT JOIN vn.saleTracking st ON st.saleFk = s.id
						AND st.stateFk = ss.id
			WHERE c.workerFk = vWorkerFk 
					AND TIMESTAMPDIFF(HOUR, c.created , util.VN_NOW()) < pc.pendingCollectionsAge
					AND s.quantity
					AND s2.order < pc.pendingCollectionsOrder 
			GROUP BY c.id
			HAVING COUNT(*) > COUNT(DISTINCT st.id);

	SELECT * FROM tmp.collection;
END$$
DELIMITER ;