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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`confection_controlSource`(
	vDated DATE,
	vScopeDays INT,
	vMaxAlertLevel INT,
	vWarehouseFk INT
)
BEGIN
/**
 * Obtiene la información para el control de confección,
 * ya sean tickets y/o entradas.
 *
 * @param vDated Fecha a calcular
 * @param vScopeDays Número de días desde hoy en adelante que entran en el cálculo.
 * @param vMaxAlertLevel Id nivel de alerta
 * @param vWarehouseFk Id de almacén
 */
	DECLARE vEndingDate DATETIME DEFAULT util.dayEnd(vDated) + INTERVAL vScopeDays DAY;

		SELECT t.shipped,
			t.id ticketFk,
			s.id saleFk,
			s.quantity,
			s.concept,
			ABS(s.reserved) isReserved,
			i.category,
			it.name itemType,
			t.nickname,
			wh.name warehouse,
			t.warehouseFk warehouseFk,
			a.provinceFk,
			am.agencyFk,
			ct.description,
			stock.visible,
			stock.available
		FROM ticket t
			JOIN agencyMode am ON am.id = t.agencyModeFk
			JOIN warehouse wh ON wh.id = t.warehouseFk
			JOIN sale s ON s.ticketFk = t.id
			JOIN item i ON i.id = s.itemFk
			JOIN itemType it ON it.id = i.typeFk
			JOIN confectionType ct ON ct.id = it.making
			JOIN `address` a on a.id = t.addressFk
			LEFT JOIN ticketState tls on tls.ticketFk = t.id
			LEFT JOIN
				(
					SELECT item_id,
							SUM(visible) visible,
							SUM(available) available
					FROM (
						SELECT a.item_id,
								0 visible,
								a.available
						FROM cache.cache_calc cc
							LEFT JOIN cache.available a ON a.calc_id = cc.id
						WHERE cc.cache_id IN ('visible', 'available')
							AND cc.params = CONCAT(vWarehouseFk, "/", util.VN_CURDATE())
						UNION ALL
						SELECT v.item_id,
								v.visible,
								0
							FROM cache.cache_calc cc
								LEFT JOIN cache.visible v ON v.calc_id = cc.id
							WHERE cc.cacheName IN ('visible', 'available')
								AND cc.params = vWarehouseFk
					) sub
					GROUP BY item_id
				) stock ON stock.item_id = s.itemFk
		WHERE it.making 
			AND tls.alertLevel < vMaxAlertLevel
			AND wh.hasConfectionTeam
			AND t.shipped BETWEEN vDated AND vEndingDate
			AND s.quantity > 0
		UNION ALL
		SELECT tr.shipped, 
				e.id,
				NULL,
				b.quantity,
				i.name,
				NULL,
				i.category,
				NULL,
				whi.name,
				who.name,
				NULL,
				NULL,
				NULL,
				ct.description,
				NULL,
				NULL
			FROM buy b
				JOIN `entry` e ON e.id = b.entryFk
				JOIN travel tr ON tr.id = e.travelFk
				JOIN warehouse whi ON whi.id = tr.warehouseInFk
				JOIN warehouse who ON who.id = tr.warehouseOutFk
				JOIN item i ON i.id = b.itemFk
				JOIN itemType it ON it.id = i.typeFk
				JOIN confectionType ct ON ct.id = it.making
			WHERE who.hasConfectionTeam
				AND it.making
				AND tr.shipped BETWEEN vDated AND vEndingDate;
END$$
DELIMITER ;