-- --------------------------------------------------------
-- --------------------------------------------------------
-- 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
DROP VIEW IF EXISTS `v_awbstat_congruity`;
CREATE TABLE `v_awbstat_congruity` (
	`item_id` INT(11) NULL,
	`export_retry` INT(11) NOT NULL,
	`export_ok` INT(11) NOT NULL,
	`last_ok` DATETIME NULL,
	`last_err` DATETIME NULL,
	`sid` VARCHAR(200) NULL,
	`cancellato` INT(11) 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` INT(11) 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` INT(11) NOT NULL,
	`nobj` INT(11) NULL,
	`ncolli` INT(11) NULL,
	`GrossWeight` DOUBLE NULL,
	`NetWeight` DOUBLE NULL,
	`Inbound` DOUBLE NULL,
	`RealVolume` DOUBLE NULL,
	`InboundVolumeSurface` DOUBLE NULL,
	`RealVolumeSurface` DOUBLE NULL,
	`Volume` DOUBLE NULL,
	`CalcNetWeight` DOUBLE 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` INT(11) NULL,
	`e_NetWeight` INT(11) NULL,
	`e_Volume` INT(11) NULL,
	`i_id` INT(11) NULL,
	`i_nobj` INT(11) NULL,
	`i_ncolli` INT(11) NULL,
	`i_weight` DOUBLE NULL,
	`i_volume` DOUBLE NULL,
	`i_HPackage` DOUBLE NULL,
	`i_WPackage` DOUBLE NULL,
	`i_LPackage` DOUBLE NULL,
	`i_NetWeightSup` INT(11) NULL,
	`i_NetWeightInf` INT(11) NULL,
	`i_VolumeSup` INT(11) NULL,
	`i_VolumeInf` INT(11) 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` INT(11) 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` INT(11) NULL,
	`StatusStandBy` INT(11) NULL,
	`StatusDelivered` INT(11) NULL,
	`StatusError` INT(11) NULL,
	`StatusIncomplete` INT(11) 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` 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`.`sid` AS `sid`, `v_awbstat`.`cancellato` AS `cancellato`, `v_awbstat`.`ID` AS `ID`,`v_awbstat`.`IID` AS `IID`,`v_awbstat`.`I_nudo_vestito` AS `I_nudo_vestito`,`v_awbstat`.`awb` AS `awb`,`v_awbstat`.`obj_code` AS `obj_code`,`v_awbstat`.`nudo_vestito` AS `nudo_vestito`,`v_awbstat`.`tipo_spedizione` AS `tipo_spedizione`,`v_awbstat`.`countawb` AS `countawb`,`v_awbstat`.`vettore` AS `vettore`,`v_awbstat`.`vettore_id` AS `vettore_id`,`v_awbstat`.`macchina_id` AS `macchina_id`,`v_awbstat`.`luogo_id` AS `luogo_id`,`v_awbstat`.`azienda_id` AS `azienda_id`,`v_awbstat`.`nacquisitions` AS `nacquisitions`
,`v_awbstat`.`nobj` AS `nobj`
,`v_awbstat`.`ncolli` AS `ncolli`
,`v_awbstat`.`GrossWeight` AS `GrossWeight`
,`v_awbstat`.`NetWeight` AS `NetWeight`
,`v_awbstat`.`Inbound` AS `Inbound`
,`v_awbstat`.`RealVolume` AS `RealVolume`
,`v_awbstat`.`InboundVolumeSurface` AS `InboundVolumeSurface`
,`v_awbstat`.`RealVolumeSurface` AS `RealVolumeSurface`
,`v_awbstat`.`Volume` AS `Volume`
,`v_awbstat`.`CalcNetWeight` AS `CalcNetWeight`
,`v_awbstat`.`HPackage` AS `HPackage`
,`v_awbstat`.`WPackage` AS `WPackage`
,`v_awbstat`.`LPackage` AS `LPackage`
,`v_awbstat`.`DateInsNoTime` AS `DateInsNoTime`
,`v_awbstat`.`DateIns` AS `DateIns`
,`v_awbstat`.`YearIns` AS `YearIns`
,`v_awbstat`.`MonthIns` AS `MonthIns`
,`v_awbstat`.`DayIns` AS `DayIns`
,`v_awbstat`.`SMonthIns` AS `SMonthIns`
,`v_awbstat`.`QMonthIns` AS `QMonthIns`
,`v_awbstat`.`TMonthIns` AS `TMonthIns`
,`v_awbstat`.`WeekIns` AS `WeekIns`
,`v_awbstat`.`WeekDayIns` AS `WeekDayIns`
,`v_awbstat`.`DayYearIns` AS `DayYearIns`
,`v_awbstat`.`HourIns` AS `HourIns`
,`v_awbstat`.`e_GrossWeight` AS `e_GrossWeight`
,`v_awbstat`.`e_NetWeight` AS `e_NetWeight`
,`v_awbstat`.`e_Volume` AS `e_Volume`
,`v_awbstat`.`e_HPackage` AS `e_HPackage`
,`v_awbstat`.`e_WPackage` AS `e_WPackage`
,`v_awbstat`.`e_LPackage` AS `e_LPackage`
,`v_awbstat`.`i_id` AS `i_id`
,`v_awbstat`.`i_nobj` AS `i_nobj`
,`v_awbstat`.`i_ncolli` AS `i_ncolli`
,`v_awbstat`.`i_NetWeight` AS `i_weight`
,`v_awbstat`.`i_Volume` AS `i_volume`
,`v_awbstat`.`i_HPackage` AS `i_HPackage`
,`v_awbstat`.`i_LPackage` AS `i_LPackage`
,`v_awbstat`.`i_WPackage` AS `i_WPackage`
,`v_awbstat`.`i_NetWeightSup` AS `i_NetWeightSup`
,`v_awbstat`.`i_NetWeightInf` AS `i_NetWeightInf`
,`v_awbstat`.`i_VolumeSup` AS `i_VolumeSup`
,`v_awbstat`.`i_VolumeInf` AS `i_VolumeInf`

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

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

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

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

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

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

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





