salix/db/routines/floranet/procedures/order_confirm.sql

DELIMITER $$
$$

CREATE OR REPLACE DEFINER=`root`@`localhost`PROCEDURE floranet.order_confirm(vCatalogueFk INT)
READS SQL DATA

proc:BEGIN
/** Update order.isPaid field, and makes the ticket
 *  
 *  @param vCatalogueFk floranet.catalogue.id
 * 
 *  @returns floranet.order.isPaid
 */
	DECLARE vNewTicketFk INT;
	DECLARE vCustomerEmail VARCHAR(255);
	DECLARE vFloranetEmail VARCHAR(255);
	DECLARE vSubjectEmail VARCHAR(100);
	DECLARE vBodyEmail TEXT;
	DECLARE vZoneFk INT;

	DECLARE exit handler FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;

		GET DIAGNOSTICS CONDITION 2 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;

		SELECT CONCAT('ERROR ', IFNULL(@errno,0), ': ', ifnull(@text,'texto')) AS `SQLEXCEPTION`;

		CALL vn.mail_insert(
			'floranet@verdnatura.es,pako@verdnatura.es',
			'noreply@verdnatura.es',
			'Floranet.order_confirm failure',
			CONCAT('CatalogueFk: ', vCatalogueFk, '\n','ERROR ', IFNULL(@errno, 0), ': ', ifnull(@text, 'texto'))
		);
	END;

	IF (SELECT isPaid FROM `order` WHERE catalogueFk = vCatalogueFk) THEN
		SELECT CONCAT('CatalogueFk: ', vCatalogueFk, ' Esta orden ya está confirmada') AS `ERROR`;
		LEAVE proc;
	END IF;

	START TRANSACTION;

	UPDATE `order`
		SET isPaid = TRUE,
			payed = NOW()
		WHERE catalogueFk = vCatalogueFk;

	SELECT zoneFk 
		INTO vZoneFk
		FROM (
			SELECT zoneFk, COUNT(*) totalCount
				FROM vn.ticket t
					JOIN catalogue c ON c.id = vCatalogueFk
				WHERE t.shipped > util.VN_CURDATE() - INTERVAL 1 YEAR
					AND t.addressFk = c.addressFk
				GROUP BY zoneFk
				ORDER BY totalCount DESC 
				LIMIT 10000000000000000000
		) sub
		LIMIT 1;
	
	INSERT INTO vn.ticket (
			clientFk,
			shipped,
			addressFk,
			agencyModeFk,
			nickname,
			warehouseFk,
			routeFk,
			companyFk,
			landed,
			zoneFk
		)
		SELECT 	a.clientFk,
				c.dated - INTERVAL 1 DAY,
				c.addressFk,
				a.agencyModeFk,
				a.nickname,
				ag.warehouseFk,
				NULL,
				co.id,
				c.dated,
				vZoneFk
			FROM vn.address a
				JOIN vn.agencyMode am ON am.id = a.agencyModeFk
				JOIN vn.agency ag ON ag.id = am.agencyFk
				JOIN catalogue c ON c.addressFk = a.id
				JOIN vn.company co ON co.code = 'VNL'
			WHERE c.id = vCatalogueFk;

	SET vNewTicketFk = LAST_INSERT_ID();

	INSERT INTO vn.sale(
		ticketFk,
		itemFk,
		concept,
		price,
		quantity)
		SELECT 
			vNewTicketFk,
			c.itemFk,
			CONCAT('Entrega: ',c.name),
			- apc.deliveryCost,
			1
			FROM catalogue c
				JOIN addressPostCode apc 
					ON apc.addressFk = c.addressFk
					AND apc.dayOfWeek = dayOfWeek(c.dated)
			WHERE c.id = vCatalogueFk;

	INSERT INTO vn.sale(
		ticketFk,
		itemFk,
		concept,
		price,
		quantity)
		SELECT 
			vNewTicketFk,
			r.elementFk,
			i.longName,
			0,
			r.quantity
			FROM catalogue c
				JOIN recipe r ON r.itemFk = c.itemFk
				JOIN vn.item i ON i.id = r.elementFk
			WHERE c.id = vCatalogueFk;

	SELECT cl.email,
		cf.email,
		CONCAT('Nuevo pedido FLORANET para entrega el ',c.dated),
		CONCAT_WS('\n',
				CONCAT('Producto: ', c.name),
				CONCAT('Fecha de entrega: ',c.dated),
				CONCAT('Destinatario: ', o.deliveryName),
				CONCAT('Dirección: ', o.address),
				CONCAT('CP: ', c.postalCode),
				CONCAT('Foto: ', c.image),
				CONCAT('Mensaje: ', IFNULL(o.message,"Ninguno.")),
				CONCAT('Teléfono: ',IFNULL(o.deliveryPhone,"--")),
				CONCAT('Observaciones: ', IFNULL(o.observations,"No hay."))
		)
		INTO vCustomerEmail,
			vFloranetEmail,
			vSubjectEmail,
			vBodyEmail
		FROM vn.client cl
			JOIN vn.address a ON a.clientFk = cl.id
			JOIN catalogue c ON c.addressFk = a.id
			JOIN `order` o ON o.catalogueFk = c.id
			JOIN config cf
		WHERE c.id = vCatalogueFk;

	CALL vn.mail_insert(
		vCustomerEmail,
		vFloranetEmail,
		vSubjectEmail,
		vBodyEmail);

	SELECT isPaid, vNewTicketFk
		FROM `order`
		WHERE catalogueFk = vCatalogueFk;

	COMMIT;

END$$
DELIMITER ;