DROP TRIGGER IF EXISTS InsertAwbexport;

DELIMITER $$

CREATE TRIGGER 
	InsertAwbexport
AFTER INSERT
ON awbexport FOR EACH ROW
BEGIN
	INSERT INTO 
		modifiche_awbexport (
			exp_id, hash, tipo_hash, stato, creator_id, creation_date, modify_id, modify_date, tipo_aggiornamento
			, lpackage, wpackage, hpackage, gross_weight, net_weight, obj_code, status
		) VALUES (
			NEW.id, SHA2(
				CONCAT(
					  IFNULL(NEW.LPackage, 0)
					, IFNULL(NEW.WPackage, 0)
					, IFNULL(NEW.HPackage, 0)
					, IFNULL(NEW.GrossWeight, 0)
					, IFNULL(NEW.NetWeight, 0)
					, IFNULL(NEW.obj_code, '')
				), 512)
			, 'SHA2 512', 0, 1, NOW(), 1, NOW(), 1
			, IFNULL(NEW.LPackage, 0), IFNULL(NEW.WPackage, 0), IFNULL(NEW.HPackage, 0)
			, IFNULL(NEW.GrossWeight, 0), IFNULL(NEW.NetWeight, 0), IFNULL(NEW.obj_code, 0)
			, 8
		)
  	ON DUPLICATE KEY UPDATE 
		hash = SHA2(
				CONCAT(
					  IFNULL(NEW.LPackage, 0)
					, IFNULL(NEW.WPackage, 0)
					, IFNULL(NEW.HPackage, 0)
					, IFNULL(NEW.GrossWeight, 0)
					, IFNULL(NEW.NetWeight, 0)
					, IFNULL(NEW.obj_code, '')
				), 512)
		, tipo_hash = 'sha3'
		, stato = 0
		, modify_id = 1
		, modify_date = NOW()
		, tipo_aggiornamento = 1
		, lpackage = IFNULL(NEW.LPackage, 0)
		, wpackage = IFNULL(NEW.WPackage, 0)
		, hpackage = IFNULL(NEW.HPackage, 0)
		, gross_weight = IFNULL(NEW.GrossWeight, 0)
		, net_weight = IFNULL(NEW.NetWeight, 0)
		, status = 8;
	UPDATE
		configurazione_trasporti AS c
	SET
		  c.LPackage = NEW.LPackage
		, c.WPackage = NEW.WPackage
		, c.HPackage = NEW.HPackage
		, c.GrossWeight = NEW.GrossWeight
		, c.NetWeight = NEW.NetWeight
		, c.PalletID = NEW.PalletID
		, c.PalletHeight = NEW.PalletHeight
		, c.PalletWeight = NEW.PalletWeight
		, c.PalletDescr = NEW.PalletDescr
		, c.PalletCode = NEW.PalletCode
		, c.pallet_op = NEW.pallet_op
		, c.dati_validi = 1
	WHERE
		c.exp_id = NEW.id;
	UPDATE
		configurazione AS c
	SET
		  c.LPackage = NEW.LPackage
		, c.WPackage = NEW.WPackage
		, c.HPackage = NEW.HPackage
		, c.GrossWeight = NEW.GrossWeight
		, c.NetWeight = NEW.NetWeight
		, c.PalletID = NEW.PalletID
		, c.PalletHeight = NEW.PalletHeight
		, c.PalletWeight = NEW.PalletWeight
		, c.PalletDescr = NEW.PalletDescr
		, c.PalletCode = NEW.PalletCode
		, c.pallet_op = NEW.pallet_op
		, c.dati_validi = 1
	WHERE
		c.exp_id = NEW.id;
	UPDATE
		configuratore_do AS c
	SET
		  c.LPackage = NEW.LPackage
		, c.WPackage = NEW.WPackage
		, c.HPackage = NEW.HPackage
		, c.GrossWeight = NEW.GrossWeight
		, c.NetWeight = NEW.NetWeight
		, c.PalletID = NEW.PalletID
		, c.PalletHeight = NEW.PalletHeight
		, c.PalletWeight = NEW.PalletWeight
		, c.PalletDescr = NEW.PalletDescr
		, c.PalletCode = NEW.PalletCode
		, c.pallet_op = NEW.pallet_op
		, c.dati_validi = 1
	WHERE
		c.exp_id = NEW.id;					
	UPDATE
		soluzioni_salvate AS s INNER JOIN configurazione_trasporti AS c ON s.testata_id = c.testata_id AND s.tipo_testata = 'so' AND s.item_id = c.id
	SET
		s.dati_validi = 1
	WHERE
		s.exp_id = NEW.id;							
	UPDATE
		soluzioni_salvate AS s INNER JOIN configurazione AS c ON s.testata_id = c.testata_id AND s.tipo_testata = 'mo' AND s.item_id = c.id
	SET
		s.dati_validi = 1
	WHERE
		s.exp_id = NEW.id;									
	UPDATE
		soluzioni_salvate AS s INNER JOIN configuratore_do AS c ON s.testata_id = c.testata_id AND s.tipo_testata = 'mo' AND s.item_id = c.id
	SET
		s.dati_validi = 1
	WHERE
		s.exp_id = NEW.id;									

END$$    

DELIMITER ;

DROP TRIGGER IF EXISTS UpdateAwbexport;

DELIMITER $$

CREATE TRIGGER 
	UpdateAwbexport
AFTER UPDATE
ON awbexport FOR EACH ROW
BEGIN
	INSERT INTO 
		modifiche_awbexport (
			exp_id, hash, tipo_hash, stato, creator_id, creation_date, modify_id, modify_date, tipo_aggiornamento
			, lpackage, wpackage, hpackage, gross_weight, net_weight, obj_code, status
		) VALUES (
			NEW.id, SHA2(
				CONCAT(
					  IFNULL(NEW.LPackage, 0)
					, IFNULL(NEW.WPackage, 0)
					, IFNULL(NEW.HPackage, 0)
					, IFNULL(NEW.GrossWeight, 0)
					, IFNULL(NEW.NetWeight, 0)
					, IFNULL(NEW.obj_code, '')
				), 512)
			, 'SHA2 512', 0, 1, NOW(), 1, NOW(), 2
			, IFNULL(NEW.LPackage, 0), IFNULL(NEW.WPackage, 0), IFNULL(NEW.HPackage, 0)
			, IFNULL(NEW.GrossWeight, 0), IFNULL(NEW.NetWeight, 0), IFNULL(NEW.obj_code, 0)
			, 8
	)
  	ON DUPLICATE KEY UPDATE 
		hash = SHA2(
				CONCAT(
					  IFNULL(NEW.LPackage, 0)
					, IFNULL(NEW.WPackage, 0)
					, IFNULL(NEW.HPackage, 0)
					, IFNULL(NEW.GrossWeight, 0)
					, IFNULL(NEW.NetWeight, 0)
					, IFNULL(NEW.obj_code, '')
				), 512)
		, tipo_hash = 'sha3'
		, stato = 0
		, modify_id = 1
		, modify_date = NOW()
		, tipo_aggiornamento = 2
		, lpackage = IFNULL(NEW.LPackage, 0)
		, wpackage = IFNULL(NEW.WPackage, 0)
		, hpackage = IFNULL(NEW.HPackage, 0)
		, gross_weight = IFNULL(NEW.GrossWeight, 0)
		, net_weight = IFNULL(NEW.NetWeight, 0)
		, status = 8;
	UPDATE
		configurazione_trasporti AS c
	SET
		  c.LPackage = NEW.LPackage
		, c.WPackage = NEW.WPackage
		, c.HPackage = NEW.HPackage
		, c.GrossWeight = NEW.GrossWeight
		, c.NetWeight = NEW.NetWeight
		, c.PalletID = NEW.PalletID
		, c.PalletHeight = NEW.PalletHeight
		, c.PalletWeight = NEW.PalletWeight
		, c.PalletDescr = NEW.PalletDescr
		, c.PalletCode = NEW.PalletCode
		, c.pallet_op = NEW.pallet_op
		, c.dati_validi = 1
	WHERE
		c.exp_id = NEW.id;
	UPDATE
		configurazione AS c
	SET
		  c.LPackage = NEW.LPackage
		, c.WPackage = NEW.WPackage
		, c.HPackage = NEW.HPackage
		, c.GrossWeight = NEW.GrossWeight
		, c.NetWeight = NEW.NetWeight
		, c.PalletID = NEW.PalletID
		, c.PalletHeight = NEW.PalletHeight
		, c.PalletWeight = NEW.PalletWeight
		, c.PalletDescr = NEW.PalletDescr
		, c.PalletCode = NEW.PalletCode
		, c.pallet_op = NEW.pallet_op
		, c.dati_validi = 1
	WHERE
		c.exp_id = NEW.id;
	UPDATE
		configuratore_do AS c
	SET
		  c.LPackage = NEW.LPackage
		, c.WPackage = NEW.WPackage
		, c.HPackage = NEW.HPackage
		, c.GrossWeight = NEW.GrossWeight
		, c.NetWeight = NEW.NetWeight
		, c.PalletID = NEW.PalletID
		, c.PalletHeight = NEW.PalletHeight
		, c.PalletWeight = NEW.PalletWeight
		, c.PalletDescr = NEW.PalletDescr
		, c.PalletCode = NEW.PalletCode
		, c.pallet_op = NEW.pallet_op
		, c.dati_validi = 1
	WHERE
		c.exp_id = NEW.id;					
	UPDATE
		soluzioni_salvate AS s INNER JOIN configurazione_trasporti AS c ON s.testata_id = c.testata_id AND s.tipo_testata = 'so' AND s.item_id = c.id
	SET
		s.dati_validi = 1
	WHERE
		s.exp_id = NEW.id;							
	UPDATE
		soluzioni_salvate AS s INNER JOIN configurazione AS c ON s.testata_id = c.testata_id AND s.tipo_testata = 'mo' AND s.item_id = c.id
	SET
		s.dati_validi = 1
	WHERE
		s.exp_id = NEW.id;									
	UPDATE
		soluzioni_salvate AS s INNER JOIN configuratore_do AS c ON s.testata_id = c.testata_id AND s.tipo_testata = 'mo' AND s.item_id = c.id
	SET
		s.dati_validi = 1
	WHERE
		s.exp_id = NEW.id;									

END$$    

DELIMITER ;
