salix/db/routines/dipole/procedures/expedition_add.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `dipole`.`expedition_add`(vExpeditionFk INT, vPrinterFk INT, vIsPrinted BOOLEAN)
BEGIN
/**	Insert records to print agency stickers and to inform sorter with new box
 * 
 * 	param vExpeditionFk Id for vn.expedition
 *  param vPrinterFk Id for dipole.printer
 * 	param vIsPrinted Value for field dipole.expedition_PrintOut.isPrinted
 */
	INSERT INTO dipole.expedition_PrintOut(expeditionFk,
				ticketFk,
				addressFk,
				street,
				postalCode,
				city,
				shopName,
				printerFk,
				routeFk,
				parkingCode,
				truckName,
				clientFk,
				phone,
				province,
				agency,
				workerCode,
				isPrinted)
			SELECT e.id,
					e.ticketFk,
					t.addressFk ,
					LEFT(a.street, 100),
					a.postalCode ,
					LEFT (CONCAT(IFNULL(rm.beachFk,''),' ',a.city), 13),
					a.nickname,
					vPrinterFk,
					IFNULL(right(`t`.`routeFk`, 3),0),
					if (@vVolume := vn.ticketTotalVolume(t.id) > 1.5, @vVolume, IFNULL( rm.beachFk, 0)),
					LEFT(IFNULL(rs.description ,replace(`z`.`name`, 'ZONA ', 'Z')),14) truckName,
					t.clientFk ,
					ifnull(c.mobile, ifnull(a.mobile, ifnull(c.phone, a.phone))),
					LEFT(p.name, 20),
					NULL,
					w.code,
					vIsPrinted
				FROM vn.expedition e
					JOIN vn.ticket t ON t.id = e.ticketFk
					JOIN vn.address a ON a.id = t.addressFk
					JOIN vn.province p ON p.id = a.provinceFk
					LEFT JOIN vn.routesMonitor rm ON rm.routeFk = t.routeFk
					LEFT JOIN vn.roadmapStop rs ON rs.id = rm.roadmapStopFk
					LEFT JOIN vn.beach b ON b.code = rm.beachFk
					LEFT JOIN vn.`zone`z ON z.id = t.zoneFk
					JOIN vn.agencyMode am ON t.agencyModeFk = am.id
					JOIN vn.client c ON t.clientFk = c.id
					JOIN vn.agency ag ON ag.id = am.agencyFk
					JOIN vn.printer pr ON pr.id = vPrinterFk
					JOIN vn.worker w ON w.id =  e.workerFk
				WHERE e.id = vExpeditionFk;

	INSERT INTO srt.expedition (id,
			width,
			`length`,
			height,
			stateFk)
		SELECT e.id,
				p.width * 10,
				p.`depth` * 10,
				p.height * 10,
				es.id
			FROM vn.expedition e
				LEFT JOIN vn.packaging p ON p.id = e.packagingFk 
				JOIN srt.expeditionState es ON es.description = 'PRINTED'
			WHERE e.id = vExpeditionFk; -- En esa tabla las medidas van en milímetros
END$$
DELIMITER ;