-- --------------------------------------------------------
-- --------------------------------------------------------
-- Versione server:              5.7.24-log - MySQL Community Server (GPL)
-- S.O. server:                  Win64
-- HeidiSQL Versione:            11.2.0.6213
-- --------------------------------------------------------


-- Dump della struttura di vista 20210222bhc4rg0cloud.v_awbstat_congruity
-- Creazione di una tabella temporanea per risolvere gli errori di dipendenza della vista
CREATE TABLE `v_awbstat_congruity_del` (
	`item_id` INT(11) NULL,
	`export_retry` BIGINT(21) NOT NULL,
	`export_ok` BIGINT(20) NOT NULL,
	`last_ok` DATETIME NULL,
	`last_err` DATETIME NULL,
	`ID` INT(11) NOT NULL,
	`IID` INT(11) NULL,
	`I_nudo_vestito` INT(11) NULL,
	`awb` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_general_ci',
	`obj_code` VARCHAR(255) NULL COLLATE 'utf8mb4_general_ci',
        `nudo_vestito` INT(11) NULL,
	`tipo_spedizione` INT(11) NULL,
	`countawb` BIGINT(21) NOT NULL,
	`vettore` VARCHAR(3) NULL COLLATE 'utf8mb4_general_ci',
	`vettore_id` INT(11) NULL,
	`macchina_id` INT(11) NOT NULL,
	`luogo_id` INT(11) NOT NULL,
	`azienda_id` INT(11) NOT NULL,
	`nacquisitions` BIGINT(21) NOT NULL,
	`nobj` DECIMAL(42,0) NULL,
	`ncolli` DECIMAL(54,0) NULL,
	`GrossWeight` DOUBLE NULL,
	`NetWeight` DOUBLE NULL,
	`Inbound` DECIMAL(54,2) NULL,
	`RealVolume` DECIMAL(54,2) NULL,
	`InboundVolumeSurface` DECIMAL(54,2) NULL,
	`RealVolumeSurface` DECIMAL(54,2) NULL,
	`Volume` DECIMAL(54,2) NULL,
	`DateInsNoTime` DATE NULL,
	`DateIns` DATETIME NULL,
	`YearIns` INT(4) NULL,
	`MonthIns` INT(2) NULL,
	`DayIns` INT(2) NULL,
	`SMonthIns` INT(4) NULL,
	`QMonthIns` INT(4) NULL,
	`TMonthIns` INT(4) NULL,
	`WeekIns` INT(2) NULL,
	`WeekDayIns` INT(3) NULL,
	`DayYearIns` INT(3) NULL,
	`HourIns` VARCHAR(2) NULL COLLATE 'utf8mb4_general_ci',
	`e_GrossWeight` BIGINT(21) NULL,
	`e_NetWeight` BIGINT(21) NULL,
	`e_Volume` BIGINT(21) NULL,
	`i_id` INT(11) NULL,
	`i_nobj` INT(11) NULL,
	`i_ncolli` INT(11) NULL,
	`i_weight` DECIMAL(10,2) NULL,
	`i_volume` DECIMAL(10,2) NULL,
	`i_GrossWeightSup` BIGINT(21) NULL,
	`i_GrossWeightInf` BIGINT(21) NULL,
	`i_NetWeightSup` BIGINT(21) NULL,
	`i_NetWeightInf` BIGINT(21) NULL,
	`i_VolumeSup` BIGINT(21) NULL,
	`i_VolumeInf` BIGINT(21) NULL,
	`congruity_nobj_null` INT(1) NOT NULL,
	`congruity_nobj_red` INT(1) NOT NULL,
	`congruity_nobj_ok` INT(1) NOT NULL,
	`congruity_ncolli_null` INT(1) NOT NULL,
	`congruity_ncolli_red` INT(1) NOT NULL,
	`congruity_ncolli_ok` INT(1) NOT NULL,
	`congruity_volume_null` INT(1) NOT NULL,
	`congruity_volume_red` INT(1) NOT NULL,
	`congruity_volume_green` INT(1) NOT NULL,
	`congruity_volume_ok` INT(1) NOT NULL,
	`diff_Volume_red` DECIMAL(55,2) NULL,
	`sum_Volume_red` INT(1) NOT NULL,
	`congruity_weight_null` INT(1) NOT NULL,
	`congruity_weight_red` INT(1) NOT NULL,
	`congruity_weight_green` INT(1) NOT NULL,
	`congruity_weight_ok` INT(1) NOT NULL,
	`diff_NetWeight_red` DOUBLE NULL,
	`sum_NetWeight_red` INT(1) NOT NULL,
	`StatusOnProcessing` DECIMAL(45,0) NULL,
	`StatusStandBy` DECIMAL(45,0) NULL,
	`StatusDelivered` DECIMAL(45,0) NULL,
	`StatusError` DECIMAL(45,0) NULL,
	`StatusIncomplete` DECIMAL(45,0) NULL
) ENGINE=MyISAM;

-- Dump della struttura di vista 20210222bhc4rg0cloud.v_awbstat_congruity
-- Rimozione temporanea di tabella e creazione della struttura finale della vista
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_awbstat_congruity_del` AS select `v_export_retry`.`item_id` AS `item_id`,`v_export_retry`.`retry` AS `export_retry`,`v_export_retry`.`last_ok` AS `last_ok`,IF(`v_export_retry`.`last_ok` IS NOT NULL, 1, 0) AS `export_ok`,`v_export_retry`.`last_err` AS `last_err`,`v_awbstat_del`.`ID` AS `ID`,`v_awbstat_del`.`IID` AS `IID`,`v_awbstat_del`.`I_nudo_vestito` AS `I_nudo_vestito`,`v_awbstat_del`.`awb` AS `awb`,`v_awbstat_del`.`obj_code` AS `obj_code`,`v_awbstat_del`.`nudo_vestito` AS `nudo_vestito`,`v_awbstat_del`.`tipo_spedizione` AS `tipo_spedizione`,`v_awbstat_del`.`countawb` AS `countawb`,`v_awbstat_del`.`vettore` AS `vettore`,`v_awbstat_del`.`vettore_id` AS `vettore_id`,`v_awbstat_del`.`macchina_id` AS `macchina_id`,`v_awbstat_del`.`luogo_id` AS `luogo_id`,`v_awbstat_del`.`azienda_id` AS `azienda_id`,`v_awbstat_del`.`nacquisitions` AS `nacquisitions`
,`v_awbstat_del`.`nobj` AS `nobj`
,`v_awbstat_del`.`ncolli` AS `ncolli`
,`v_awbstat_del`.`GrossWeight` AS `GrossWeight`
,`v_awbstat_del`.`NetWeight` AS `NetWeight`
,`v_awbstat_del`.`Inbound` AS `Inbound`
,`v_awbstat_del`.`RealVolume` AS `RealVolume`
,`v_awbstat_del`.`InboundVolumeSurface` AS `InboundVolumeSurface`
,`v_awbstat_del`.`RealVolumeSurface` AS `RealVolumeSurface`
,`v_awbstat_del`.`Volume` AS `Volume`
,`v_awbstat_del`.`HPackage` AS `HPackage`
,`v_awbstat_del`.`WPackage` AS `WPackage`
,`v_awbstat_del`.`LPackage` AS `LPackage`
,`v_awbstat_del`.`DateInsNoTime` AS `DateInsNoTime`
,`v_awbstat_del`.`DateIns` AS `DateIns`
,`v_awbstat_del`.`YearIns` AS `YearIns`
,`v_awbstat_del`.`MonthIns` AS `MonthIns`
,`v_awbstat_del`.`DayIns` AS `DayIns`
,`v_awbstat_del`.`SMonthIns` AS `SMonthIns`
,`v_awbstat_del`.`QMonthIns` AS `QMonthIns`
,`v_awbstat_del`.`TMonthIns` AS `TMonthIns`
,`v_awbstat_del`.`WeekIns` AS `WeekIns`
,`v_awbstat_del`.`WeekDayIns` AS `WeekDayIns`
,`v_awbstat_del`.`DayYearIns` AS `DayYearIns`
,`v_awbstat_del`.`HourIns` AS `HourIns`
,`v_awbstat_del`.`e_GrossWeight` AS `e_GrossWeight`
,`v_awbstat_del`.`e_NetWeight` AS `e_NetWeight`
,`v_awbstat_del`.`e_Volume` AS `e_Volume`
,`v_awbstat_del`.`e_HPackage` AS `e_HPackage`
,`v_awbstat_del`.`e_WPackage` AS `e_WPackage`
,`v_awbstat_del`.`e_LPackage` AS `e_LPackage`
,`v_awbstat_del`.`i_id` AS `i_id`
,`v_awbstat_del`.`i_nobj` AS `i_nobj`
,`v_awbstat_del`.`i_ncolli` AS `i_ncolli`
,`v_awbstat_del`.`i_NetWeight` AS `i_weight`
,`v_awbstat_del`.`i_Volume` AS `i_volume`
,`v_awbstat_del`.`i_GrossWeightSup` AS `i_GrossWeightSup`
,`v_awbstat_del`.`i_GrossWeightInf` AS `i_GrossWeightInf`
,`v_awbstat_del`.`i_NetWeightSup` AS `i_NetWeightSup`
,`v_awbstat_del`.`i_NetWeightInf` AS `i_NetWeightInf`
,`v_awbstat_del`.`i_VolumeSup` AS `i_VolumeSup`
,`v_awbstat_del`.`i_VolumeInf` AS `i_VolumeInf`

,if(`v_awbstat_del`.`id` = `v_awbstat_del`.`iid` OR isnull(`v_awbstat_del`.`i_nobj`),1,0) AS `congruity_nobj_null`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_nobj` is not null) AND (`v_awbstat_del`.`nobj` <> `v_awbstat_del`.`i_nobj`),1,0) AS `congruity_nobj_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_nobj` is not null) AND (`v_awbstat_del`.`nobj` = `v_awbstat_del`.`i_nobj`),1,0) AS `congruity_nobj_ok`

,if(`v_awbstat_del`.`id` = `v_awbstat_del`.`iid` OR isnull(`v_awbstat_del`.`i_ncolli`),1,0) AS `congruity_ncolli_null`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_ncolli` is not null) AND (`v_awbstat_del`.`ncolli` <> `v_awbstat_del`.`i_ncolli`),1,0) AS `congruity_ncolli_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_ncolli` is not null) AND (`v_awbstat_del`.`ncolli` = `v_awbstat_del`.`i_ncolli`),1,0) AS `congruity_ncolli_ok`

,if((`v_awbstat_del`.`id` = `v_awbstat_del`.`iid` OR isnull(`v_awbstat_del`.`i_VolumeSup`) or isnull(`v_awbstat_del`.`i_VolumeInf`)),1,0) AS `congruity_volume_null`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_VolumeSup` is not null) AND (`v_awbstat_del`.`i_VolumeInf` is not null) AND (`v_awbstat_del`.`e_Volume` > `v_awbstat_del`.`i_VolumeSup`),1,0) AS `congruity_volume_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_VolumeSup` is not null) AND (`v_awbstat_del`.`i_VolumeInf` is not null) AND (`v_awbstat_del`.`e_Volume` < `v_awbstat_del`.`i_VolumeInf`),1,0) AS `congruity_volume_green`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_VolumeSup` is not null) AND (`v_awbstat_del`.`i_VolumeInf` is not null) and (`v_awbstat_del`.`e_Volume` >= `v_awbstat_del`.`i_VolumeInf`) and (`v_awbstat_del`.`e_Volume` <= `v_awbstat_del`.`i_VolumeSup`),1,0) AS `congruity_volume_ok`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND ((`v_awbstat_del`.`i_VolumeSup` is not null) and (`v_awbstat_del`.`i_VolumeInf` is not null) and (`v_awbstat_del`.`e_Volume` > `v_awbstat_del`.`i_VolumeSup` OR `v_awbstat_del`.`e_Volume` < `v_awbstat_del`.`i_VolumeInf`)),(`v_awbstat_del`.`Volume` - `v_awbstat_del`.`i_Volume`),0.0) AS `diff_Volume_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND ((`v_awbstat_del`.`i_VolumeSup` is not null) and (`v_awbstat_del`.`i_VolumeInf` is not null) and (`v_awbstat_del`.`e_Volume` > `v_awbstat_del`.`i_VolumeSup`)),1,0) AS `sum_Volume_red`

,if((`v_awbstat_del`.`id` = `v_awbstat_del`.`iid` OR isnull(`v_awbstat_del`.`i_HSup`) or isnull(`v_awbstat_del`.`i_HInf`)),1,0) AS `congruity_h_null`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_HSup` is not null) and (`v_awbstat_del`.`i_HInf` is not null) and (`v_awbstat_del`.`e_HPackage` > `v_awbstat_del`.`i_HSup`),1,0) AS `congruity_h_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_HSup` is not null) and (`v_awbstat_del`.`i_HInf` is not null) and (`v_awbstat_del`.`e_HPackage` < `v_awbstat_del`.`i_HInf`),1,0) AS `congruity_h_green`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_HSup` is not null) and (`v_awbstat_del`.`i_HInf` is not null) and (`v_awbstat_del`.`e_HPackage` >= `v_awbstat_del`.`i_HInf`) and (`v_awbstat_del`.`e_HPackage` <= `v_awbstat_del`.`i_HSup`),1,0) AS `congruity_h_ok`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND ((`v_awbstat_del`.`i_HSup` is not null) and (`v_awbstat_del`.`i_HInf` is not null) and (`v_awbstat_del`.`e_HPackage` > `v_awbstat_del`.`i_HSup` OR `v_awbstat_del`.`e_HPackage` < `v_awbstat_del`.`i_HInf`)),(`v_awbstat_del`.`HPackage` - `v_awbstat_del`.`i_HPackage`),0.0) AS `diff_h_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND ((`v_awbstat_del`.`i_HSup` is not null) and (`v_awbstat_del`.`i_HInf` is not null) and (`v_awbstat_del`.`e_HPackage` > `v_awbstat_del`.`i_HSup`)),1,0) AS `sum_h_red`

,if((`v_awbstat_del`.`id` = `v_awbstat_del`.`iid` OR isnull(`v_awbstat_del`.`i_WSup`) or isnull(`v_awbstat_del`.`i_WInf`)),1,0) AS `congruity_w_null`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_WSup` is not null) and (`v_awbstat_del`.`i_WInf` is not null) and (`v_awbstat_del`.`e_WPackage` > `v_awbstat_del`.`i_WSup`),1,0) AS `congruity_w_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_WSup` is not null) and (`v_awbstat_del`.`i_WInf` is not null) and (`v_awbstat_del`.`e_WPackage` < `v_awbstat_del`.`i_WInf`),1,0) AS `congruity_w_green`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_WSup` is not null) and (`v_awbstat_del`.`i_WInf` is not null) and (`v_awbstat_del`.`e_WPackage` >= `v_awbstat_del`.`i_WInf`) and (`v_awbstat_del`.`e_WPackage` <= `v_awbstat_del`.`i_WSup`),1,0) AS `congruity_w_ok`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND ((`v_awbstat_del`.`i_WSup` is not null) and (`v_awbstat_del`.`i_WInf` is not null) and (`v_awbstat_del`.`e_WPackage` > `v_awbstat_del`.`i_WSup` OR `v_awbstat_del`.`e_WPackage` < `v_awbstat_del`.`i_WInf`)),(`v_awbstat_del`.`WPackage` - `v_awbstat_del`.`i_WPackage`),0.0) AS `diff_w_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND ((`v_awbstat_del`.`i_WSup` is not null) and (`v_awbstat_del`.`i_WInf` is not null) and (`v_awbstat_del`.`e_WPackage` > `v_awbstat_del`.`i_WSup`)),1,0) AS `sum_w_red`

,if((`v_awbstat_del`.`id` = `v_awbstat_del`.`iid` OR isnull(`v_awbstat_del`.`i_LSup`) or isnull(`v_awbstat_del`.`i_LInf`)),1,0) AS `congruity_l_null`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_LSup` is not null) and (`v_awbstat_del`.`i_LInf` is not null) and (`v_awbstat_del`.`e_LPackage` > `v_awbstat_del`.`i_LSup`),1,0) AS `congruity_l_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_LSup` is not null) and (`v_awbstat_del`.`i_LInf` is not null) and (`v_awbstat_del`.`e_LPackage` < `v_awbstat_del`.`i_LInf`),1,0) AS `congruity_l_green`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_LSup` is not null) and (`v_awbstat_del`.`i_LInf` is not null) and (`v_awbstat_del`.`e_LPackage` >= `v_awbstat_del`.`i_LInf`) and (`v_awbstat_del`.`e_LPackage` <= `v_awbstat_del`.`i_LSup`),1,0) AS `congruity_l_ok`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND ((`v_awbstat_del`.`i_LSup` is not null) and (`v_awbstat_del`.`i_LInf` is not null) and (`v_awbstat_del`.`e_LPackage` > `v_awbstat_del`.`i_LSup` OR `v_awbstat_del`.`e_LPackage` < `v_awbstat_del`.`i_LInf`)),(`v_awbstat_del`.`LPackage` - `v_awbstat_del`.`i_LPackage`),0.0) AS `diff_l_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND ((`v_awbstat_del`.`i_LSup` is not null) and (`v_awbstat_del`.`i_LInf` is not null) and (`v_awbstat_del`.`e_LPackage` > `v_awbstat_del`.`i_LSup`)),1,0) AS `sum_l_red`

,if((`v_awbstat_del`.`id` = `v_awbstat_del`.`iid` OR isnull(`v_awbstat_del`.`i_NetWeightSup`) or isnull(`v_awbstat_del`.`i_NetWeightInf`)),1,0) AS `congruity_weight_null`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_NetWeightSup` is not null) and (`v_awbstat_del`.`i_NetWeightInf` is not null) and (`v_awbstat_del`.`e_NetWeight` > `v_awbstat_del`.`i_NetWeightSup`),1,0) AS `congruity_weight_red`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_NetWeightSup` is not null) and (`v_awbstat_del`.`i_NetWeightInf` is not null) and (`v_awbstat_del`.`e_NetWeight` < `v_awbstat_del`.`i_NetWeightInf`),1,0) AS `congruity_weight_green`
,if((`v_awbstat_del`.`id` <> `v_awbstat_del`.`iid`) AND (`v_awbstat_del`.`i_NetWeightSup` is not null) and (`v_awbstat_del`.`i_NetWeightInf` is not null) and (`v_awbstat_del`.`e_NetWeight` >= `v_awbstat_del`.`i_NetWeightInf`) and (`v_awbstat_del`.`e_NetWeight` <= `v_awbstat_del`.`i_NetWeightSup`),1,0) AS `congruity_weight_ok`
,if(((`v_awbstat_del`.`i_NetWeightSup` is not null) and (`v_awbstat_del`.`i_NetWeightInf` is not null) and (`v_awbstat_del`.`e_NetWeight` > `v_awbstat_del`.`i_NetWeightSup` OR `v_awbstat_del`.`e_NetWeight` < `v_awbstat_del`.`i_NetWeightSup`)),(`v_awbstat_del`.`NetWeight` - `v_awbstat_del`.`i_NetWeight`),0.0) AS `diff_NetWeight_red`
,if(((`v_awbstat_del`.`i_NetWeightSup` is not null) and (`v_awbstat_del`.`i_NetWeightInf` is not null) and (`v_awbstat_del`.`e_NetWeight` > `v_awbstat_del`.`i_NetWeightSup`)),1,0) AS `sum_NetWeight_red`,`v_awbstat_del`.`StatusOnProcessing` AS `StatusOnProcessing`,`v_awbstat_del`.`StatusStandBy` AS `StatusStandBy`,`v_awbstat_del`.`StatusDelivered` AS `StatusDelivered`,`v_awbstat_del`.`StatusError` AS `StatusError`,`v_awbstat_del`.`StatusIncomplete` AS `StatusIncomplete` from (`v_awbstat_del` left join `v_export_retry` on((`v_awbstat_del`.`IID` = `v_export_retry`.`item_id`)));





