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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`collection_getTickets`(vParamFk INT)
BEGIN
/**
 * Selecciona los tickets de una colección/ticket/sectorCollection
 * @param vParamFk ticketFk/collectionFk/sectorCollection
 * @return Retorna ticketFk, level, agencyName, warehouseFk, salesPersonFk, observation
 */	
	DECLARE vItemPackingTypeFk VARCHAR(1);
	DECLARE vYesterday DATE;

	-- Si los sacadores son los de pruebas, pinta los colores
	SELECT itemPackingTypeFk
			INTO vItemPackingTypeFk
		FROM collection 
		WHERE id = vParamFk;
	
	SET vYesterday = util.yesterday();

	WITH observation AS (
		SELECT tob.ticketFk, tob.description 
			FROM vn.ticketObservation tob 
				JOIN vn.ticketCollection tc ON tc.ticketFk = tob.ticketFk
				JOIN vn.observationType ot ON ot.id = tob.observationTypeFk AND ot.`code` = 'itemPicker' 
			WHERE tc.collectionFk = vParamFk OR tc.ticketFk = vParamFk
	)
	SELECT t.id ticketFk, 
			IF(!(vItemPackingTypeFk <=> 'V'), cc.code, CONCAT(SUBSTRING('ABCDEFGH', tc.wagon, 1), '-', tc.`level`)) `level`,
			am.name agencyName,
			t.warehouseFk,
			w.id salesPersonFk,
			IFNULL(ob.description,'') observaciones,
			cc.rgb,
			p.code parkingCode,
			IF (ps.ticketFk, TRUE, FALSE) isAdvanced
		FROM vn.ticket t
			LEFT JOIN vn.ticketCollection tc ON t.id = tc.ticketFk
			LEFT JOIN vn.collection c2 ON c2.id = tc.collectionFk
			LEFT JOIN vn.collectionColors cc 
				ON cc.wagon = tc.wagon 
				AND cc.shelve = tc.`level` 
				AND cc.trainFk = c2.trainFk
			LEFT JOIN vn.zone z ON z.id = t.zoneFk 
			LEFT JOIN vn.agencyMode am ON am.id = z.agencyModeFk
			LEFT JOIN vn.client c ON c.id = t.clientFk
			LEFT JOIN vn.worker w ON w.id = c.salesPersonFk
			LEFT JOIN observation ob ON ob.ticketFk = t.id
			LEFT JOIN vn.ticketParking tp ON tp.ticketFk = t.id
			LEFT JOIN vn.parking p ON tp.parkingFk = p.id
			LEFT JOIN vn.packingSiteAdvanced ps ON ps.ticketFk = t.id
		WHERE t.id = vParamFk 
			AND t.shipped >= vYesterday
	UNION
	SELECT t.id ticketFk, 
			IF(NOT(vItemPackingTypeFk <=> 'V'), cc.code, CONCAT(SUBSTRING('ABCDEFGH', tc.wagon, 1), '-', tc.`level`)) `level`,
			am.name agencyName,
			t.warehouseFk,
			w.id salesPersonFk,
			ob.description,
			IF(NOT (vItemPackingTypeFk <=> 'V'), cc.rgb, NULL) `rgb`,
			p.code parkingCode,
			IF (ps.ticketFk, TRUE, FALSE) isAdvanced
		FROM vn.ticket t
			JOIN vn.ticketCollection tc ON t.id = tc.ticketFk
			LEFT JOIN vn.collection c2 ON c2.id = tc.collectionFk 	
			LEFT JOIN vn.collectionColors cc 
				ON cc.wagon = tc.wagon 
					AND cc.shelve = tc.`level` 
					AND cc.trainFk = c2.trainFk
			LEFT JOIN vn.zone z ON z.id = t.zoneFk 
			LEFT JOIN vn.agencyMode am ON am.id = z.agencyModeFk
			LEFT JOIN vn.client c ON c.id = t.clientFk
			LEFT JOIN vn.worker w ON w.id = c.salesPersonFk
			LEFT JOIN observation ob ON ob.ticketFk = t.id
			LEFT JOIN vn.ticketParking tp ON tp.ticketFk = t.id
			LEFT JOIN vn.parking p ON tp.parkingFk = p.id
			LEFT JOIN vn.packingSiteAdvanced ps ON ps.ticketFk = t.id
		WHERE tc.collectionFk = vParamFk
	UNION
	SELECT sg.ticketFk,
			NULL `level`,
			am.name agencyName,
			t.warehouseFk,
			c.salesPersonFk,
			ob.description,
			NULL `rgb`,
			p.code parkingCode,
			IF (ps.ticketFk, TRUE, FALSE) isAdvanced
		FROM vn.sectorCollection sc
			JOIN vn.sectorCollectionSaleGroup ss ON ss.sectorCollectionFk = sc.id 
			JOIN vn.saleGroup sg ON sg.id = ss.saleGroupFk
			JOIN vn.ticket t ON t.id = sg.ticketFk 
			LEFT JOIN vn.zone z ON z.id = t.zoneFk 
			LEFT JOIN vn.agencyMode am ON am.id = z.agencyModeFk 
			LEFT JOIN observation ob ON ob.ticketFk = t.id 
			LEFT JOIN vn.client c ON c.id = t.clientFk
			LEFT JOIN vn.ticketParking tp ON tp.ticketFk = t.id
			LEFT JOIN vn.parking p ON tp.parkingFk = p.id
			LEFT JOIN vn.packingSiteAdvanced ps ON ps.ticketFk = t.id
		WHERE sc.id = vParamFk
			AND t.shipped >= vYesterday
			GROUP BY ticketFk;
END$$
DELIMITER ;