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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`ticket_add`(
	vClientId INT
	,vShipped DATE
	,vWarehouseFk INT
	,vCompanyFk INT
	,vAddressFk INT
	,vAgencyModeFk INT
	,vRouteFk INT
	,vlanded DATE
	,vUserId INT
	,vIsRequiredZone INT
	,OUT vNewTicket INT)
BEGIN
/**
* Crea un ticket,
* ¡¡NO se debe llamar directamente, llamar a salix que hace comprobaciones previas!!
*
* @param vClientId id del cliente
* @param vShipped dia preparacion
* @param vWarehouseFk id del warehouse
* @param vCompanyFk id la empresa
* @param vAddressFk id del consignatario
* @param vAgencyModeFk id de la agencia
* @param vRouteFk id de la ruta | NULL
* @param vlanded dia llegada
* @param vUserId que crea el ticket
* @param vIsRequiredZone Indica si tiene que tener zona valida para ser creado
* @return vNewTicket id del ticket creado
*/
	DECLARE vZoneFk INT;
	DECLARE vPrice DECIMAL(10,2);
	DECLARE vBonus DECIMAL(10,2);
	DECLARE vIsActive BOOL;

	IF vClientId IS NULL THEN
		CALL util.throw ('CLIENT_NOT_ESPECIFIED');
	END IF;

	SELECT isActive INTO vIsActive
		FROM vn.client
		WHERE id = vClientId;

	IF NOT vIsActive THEN
		CALL util.throw ('CLIENT_NOT_ACTIVE');
	END IF;

	IF NOT vAddressFk OR vAddressFk IS NULL THEN
		SELECT id INTO vAddressFk
			FROM address
			WHERE clientFk = vClientId
				AND isDefaultAddress;
	END IF;

	IF vAgencyModeFk IS NOT NULL THEN
		CALL vn.zone_getShipped (vlanded, vAddressFk, vAgencyModeFk, TRUE);

		SELECT zoneFk, price, bonus
			INTO vZoneFk, vPrice, vBonus
			FROM tmp.zoneGetShipped
			WHERE shipped = vShipped
				AND warehouseFk = vWarehouseFk
			LIMIT 1;

		IF (vZoneFk IS NULL OR vZoneFk = 0) AND vIsRequiredZone THEN
			CALL util.throw ('NOT_ZONE_WITH_THIS_PARAMETERS');
		END IF;
		
		DROP TEMPORARY TABLE tmp.zoneGetShipped;
	END IF;

	INSERT INTO ticket (
			clientFk,
			shipped,
			addressFk,
			agencyModeFk,
			nickname,
			warehouseFk,
			routeFk,
			companyFk,
			landed,
			zoneFk,
			zonePrice,
			zoneBonus
		)
		SELECT vClientId,
				vShipped,
				a.id,
				vAgencyModeFk,
				a.nickname,
				vWarehouseFk,
				IF(vRouteFk,vRouteFk,NULL),
				vCompanyFk,
				vlanded,
				vZoneFk,
				vPrice,
				vBonus
			FROM address a
				JOIN agencyMode am ON am.id = a.agencyModeFk
			WHERE a.id = vAddressFk;

	SET vNewTicket = LAST_INSERT_ID();

	INSERT INTO ticketObservation(ticketFk, observationTypeFk, description)
		SELECT vNewTicket, ao.observationTypeFk, ao.description
			FROM addressObservation ao
				JOIN address a ON a.id = ao.addressFk
			WHERE a.id = vAddressFk;

	IF (SELECT COUNT(*)
			FROM bs.clientNewBorn cnb
			WHERE cnb.clientFk = vClientId
				AND NOT cnb.isRookie) = 0 THEN

		CALL vn.ticketObservation_addNewBorn(vNewTicket);
	END IF;

	IF (SELECT ct.isCreatedAsServed FROM vn.clientType ct JOIN vn.client c ON c.typeFk = ct.code WHERE c.id = vClientId ) <> FALSE THEN
		INSERT INTO ticketTracking(stateFk, ticketFk, userFk)
			SELECT id, vNewTicket, account.myUser_getId()
				FROM state
				WHERE `code` = 'DELIVERED';
	END IF;
END$$
DELIMITER ;