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


-- Dump della struttura di vista bhc4rg0cloud.v_awbstat
-- Creazione di una tabella temporanea per risolvere gli errori di dipendenza della vista
CREATE TABLE `v_awbstat_all` (
	`ID` INT(11) NULL,
	`IID` INT(11) NULL,
	`I_nudo_vestito` INT(11) NULL,
        `export_retry` INT(11) NULL, 
        `export_ok` INT(11) NULL,
	`awb` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_general_ci',
	`obj_code` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_general_ci',
        `nudo_vestito` INT(11) NULL,
	`tipo_spedizione` INT(11) NULL,
	`countawb` BIGINT(21) NOT NULL,
	`vettore` VARCHAR(3) NOT 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,
	`HPackage` DECIMAL(54,2) NULL,
	`WPackage` DECIMAL(54,2) NULL,
	`LPackage` 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,
	`e_HPackage` BIGINT(21) NULL,
	`e_WPackage` BIGINT(21) NULL,
	`e_LPackage` BIGINT(21) NULL,
	`i_id` INT(11) NULL,
	`i_nobj` INT(11) NULL,
	`i_ncolli` INT(11) NULL,
	`i_NetWeight` DECIMAL(10,2) NULL,
	`i_Volume` DECIMAL(10,2) NULL,
	`i_HPackage` DECIMAL(10,2) NULL,
	`i_WPackage` DECIMAL(10,2) NULL,
	`i_LPackage` 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,
        `i_HSup` BIGINT(21) NULL,
        `i_HInf` BIGINT(21) NULL,
        `i_WSup` BIGINT(21) NULL,
        `i_WInf` BIGINT(21) NULL,
        `i_LSup` BIGINT(21) NULL,
        `i_LInf` BIGINT(21) 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 bhc4rg0cloud.v_awbstat
-- Rimozione temporanea di tabella e creazione della struttura finale della vista
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_awbstat_all` AS select `ae`.`ID` AS `ID`, `ai`.`ID` AS `IID`, `ai`.`nudo_vestito` AS `I_nudo_vestito`, `ae`.`awb` AS `awb`, `ae`.`obj_code` AS `obj_code`, `ae`.`nudo_vestito` AS `nudo_vestito`,`ae`.`tipo_spedizione` AS `tipo_spedizione`,`ae`.`countawb` AS `countawb`,`ae`.`vettore` AS `vettore`,`v`.`id` AS `vettore_id`,`ae`.`macchina_id` AS `macchina_id`,`ae`.`luogo_id` AS `luogo_id`,`ae`.`azienda_id` AS `azienda_id`,`ae`.`nacquisitions` AS `nacquisitions`,`ae`.`nobj` AS `nobj`,`ae`.`ncolli` AS `ncolli`,`ae`.`GrossWeight` AS `GrossWeight`,`ae`.`NetWeight` AS `NetWeight`,`ae`.`Inbound` AS `Inbound`,`ae`.`RealVolume` AS `RealVolume`,`ae`.`InboundVolumeSurface` AS `InboundVolumeSurface`,`ae`.`RealVolumeSurface` AS `RealVolumeSurface`,`ae`.`Volume` AS `Volume`,`ae`.`HPackage` AS `HPackage`,`ae`.`WPackage` AS `WPackage`,`ae`.`LPackage` AS `LPackage`,`ae`.`DateInsNoTime` AS `DateInsNoTime`,`ae`.`DateIns` AS `DateIns`,`ae`.`YearIns` AS `YearIns`,`ae`.`MonthIns` AS `MonthIns`,`ae`.`DayIns` AS `DayIns`,`ae`.`SMonthIns` AS `SMonthIns`,`ae`.`QMonthIns` AS `QMonthIns`,`ae`.`TMonthIns` AS `TMonthIns`,`ae`.`WeekIns` AS `WeekIns`,`ae`.`WeekDayIns` AS `WeekDayIns`,`ae`.`DayYearIns` AS `DayYearIns`,`ae`.`HourIns` AS `HourIns`,cast((`ae`.`GrossWeight` * 1000) as signed) AS `e_GrossWeight`,cast((`ae`.`NetWeight` * 1000) as signed) AS `e_NetWeight`,cast((`ae`.`Volume` * 1000) as signed) AS `e_Volume`,cast((`ae`.`HPackage` * 1000) as signed) AS `e_HPackage`,cast((`ae`.`WPackage` * 1000) as signed) AS `e_WPackage`,cast((`ae`.`LPackage` * 1000) as signed) AS `e_LPackage`,`ai`.`ID` AS `i_id`,/*`ai`.`TotPackages`*/ IF(`ai`.`ID` is not null, 1, null) AS `i_nobj`,`ai`.`ncolli` AS `i_ncolli`,`ai`.`NetWeight` AS `i_NetWeight`,`ai`.`Volume` AS `i_Volume`
,`ai`.`HPackage` AS `i_HPackage`
,`ai`.`WPackage` AS `i_WPackage`
,`ai`.`LPackage` AS `i_LPackage`
,GetLimitSup(`ai`.`GrossWeight`, 'peso.valore.scostamento') AS `i_GrossWeightSup`
,GetLimitInf(`ai`.`GrossWeight`, 'peso.valore.scostamento') AS `i_GrossWeightInf`
,GetLimitSup(`ai`.`NetWeight`, 'peso.valore.scostamento') AS `i_NetWeightSup`
,GetLimitInf(`ai`.`NetWeight`, 'peso.valore.scostamento') AS `i_NetWeightInf`
,GetLimitSup(`ai`.`Volume`, 'volume.valore.scostamento') AS `i_VolumeSup`
,GetLimitInf(`ai`.`Volume`, 'volume.valore.scostamento') AS `i_VolumeInf`
,GetLimitSup(`ai`.`HPackage`, 'h.valore.scostamento') AS `i_HSup`
,GetLimitInf(`ai`.`HPackage`, 'h.valore.scostamento') AS `i_HInf`
,GetLimitSup(`ai`.`WPackage`, 'w.valore.scostamento') AS `i_WSup`
,GetLimitInf(`ai`.`WPackage`, 'w.valore.scostamento') AS `i_WInf`
,GetLimitSup(`ai`.`LPackage`, 'l.valore.scostamento') AS `i_LSup`
,GetLimitInf(`ai`.`LPackage`, 'l.valore.scostamento') AS `i_LInf`
,`ae`.`StatusOnProcessing` AS `StatusOnProcessing`,`ae`.`StatusStandBy` AS `StatusStandBy`,`ae`.`StatusDelivered` AS `StatusDelivered`,`ae`.`StatusError` AS `StatusError`,`ae`.`StatusIncomplete` AS `StatusIncomplete` from ((((select `ae`.`ID` AS `ID`, `ae`.`awb` AS `awb`, `ae`.`obj_code` AS `obj_code`, `ae`.`nudo_vestito` AS `nudo_vestito`,`ae`.`tipo_spedizione` AS `tipo_spedizione`,`ae`.`vettore` AS `vettore`,`ae`.`mid` AS `macchina_id`,`ae`.`lid` AS `luogo_id`,`ae`.`azienda_id` AS `azienda_id`,count(distinct IFNULL(`ae`.`obj_code`, `ae`.id)) AS `countawb`,count(distinct `ae`.`sid`) AS `nacquisitions`,sum(`ae`.`nobj`) AS `nobj`,sum(`ae`.`ncolli`) AS `ncolli`,sum(`ae`.`GrossWeight`) AS `GrossWeight`,sum(`ae`.`NetWeight`) AS `NetWeight`,sum(`ae`.`Inbound`) AS `Inbound`,sum(`ae`.`RealVolume`) AS `RealVolume`,sum(`ae`.`InboundVolumeSurface`) AS `InboundVolumeSurface`,sum(`ae`.`RealVolumeSurface`) AS `RealVolumeSurface`,sum(`ae`.`Volume`) AS `Volume`,sum(`ae`.`HPackage`) AS `HPackage`,sum(`ae`.`WPackage`) AS `WPackage`,sum(`ae`.`LPackage`) AS `LPackage`,min(`ae`.`DateInsNoTime`) AS `DateInsNoTime`,min(`ae`.`DataIns`) AS `DateIns`,year(min(`ae`.`DataIns`)) AS `YearIns`,month(min(`ae`.`DataIns`)) AS `MonthIns`,dayofmonth(min(`ae`.`DataIns`)) AS `DayIns`,(((month(min(`ae`.`DataIns`)) - 1) DIV 6) + 1) AS `SMonthIns`,(((month(min(`ae`.`DataIns`)) - 1) DIV 4) + 1) AS `QMonthIns`,(((month(min(`ae`.`DataIns`)) - 1) DIV 3) + 1) AS `TMonthIns`,week(min(`ae`.`DataIns`),1) AS `WeekIns`,(weekday(min(`ae`.`DataIns`)) + 1) AS `WeekDayIns`,dayofyear(min(`ae`.`DataIns`)) AS `DayYearIns`,lpad(hour(min(`ae`.`DataIns`)),2,'0') AS `HourIns`,sum(`ae`.`StatusOnProcessing`) AS `StatusOnProcessing`,sum(`ae`.`StatusStandBy`) AS `StatusStandBy`,sum(`ae`.`StatusDelivered`) AS `StatusDelivered`,sum(`ae`.`StatusError`) AS `StatusError`,sum(`ae`.`StatusIncomplete`) AS `StatusIncomplete` from (select `awbexport`.`ID` AS `ID`, `awbexport`.`awb` AS `awb`, `awbexport`.`obj_code` AS `obj_code`, `awbexport`.`nudo_vestito` AS `nudo_vestito`,`awbexport`.`tipo_spedizione` AS `tipo_spedizione`,`awbexport`.`sid` AS `sid`,`ma`.`id` AS `mid`,`lu`.`id` AS `lid`,`lu`.`azienda_id` AS `azienda_id`,substr(`awbexport`.`awb`,1,3) AS `vettore`,`awbexport`.`nobj` AS `nobj`,`awbexport`.`ncolli` AS `ncolli`,`awbexport`.`GrossWeight` AS `GrossWeight`,`awbexport`.`NetWeight` AS `NetWeight`,`awbexport`.`Inbound` AS `Inbound`,`awbexport`.`RealVolume` AS `RealVolume`,`awbexport`.`InboundVolumeSurface` AS `InboundVolumeSurface`,`awbexport`.`RealVolumeSurface` AS `RealVolumeSurface`,`awbexport`.`Volume` AS `Volume`,`awbexport`.`HPackage` AS `HPackage`,`awbexport`.`WPackage` AS `WPackage`,`awbexport`.`LPackage` AS `LPackage`,`awbexport`.`DataIns` AS `DataIns`,cast(`awbexport`.`DataIns` as date) AS `DateInsNoTime`,`awbexport`.`StatusOnProcessing` AS `StatusOnProcessing`,`awbexport`.`StatusStandBy` AS `StatusStandBy`,`awbexport`.`StatusDelivered` AS `StatusDelivered`,`awbexport`.`StatusError` AS `StatusError`,`awbexport`.`StatusIncomplete` AS `StatusIncomplete` from ((((((select `awbexport`.`ID` AS `ID`, `awbexport`.`AWB` AS `awb`,`awbexport`.`obj_code` AS `obj_code`,`awbexport`.`nudo_vestito` AS `nudo_vestito`,`awbexport`.`tipo_spedizione` AS `tipo_spedizione`,`awbexport`.`sid` AS `sid`,count(`awbexport`.`ID`) AS `nobj`,sum(if((isnull(`awbexport`.`ncolli`) or (`awbexport`.`ncolli` <= 0)),1,`awbexport`.`ncolli`)) AS `ncolli`,sum(`awbexport`.`GrossWeight`) AS `GrossWeight`,sum(`awbexport`.`NetWeight`) AS `NetWeight`,sum(`awbexport`.`Inbound`) AS `Inbound`,sum(`awbexport`.`RealVolume`) AS `RealVolume`,sum(`awbexport`.`InboundVolumeSurface`) AS `InboundVolumeSurface`,sum(`awbexport`.`RealVolumeSurface`) AS `RealVolumeSurface`,sum(if((isnull(`awbexport`.`VolumeFlag`) or (`awbexport`.`VolumeFlag` <= 1)),`awbexport`.`Inbound`,if((`awbexport`.`VolumeFlag` = 2),`awbexport`.`RealVolume`,if((`awbexport`.`VolumeFlag` = 3),`awbexport`.`InboundVolumeSurface`,if((`awbexport`.`VolumeFlag` = 4),`awbexport`.`RealVolumeSurface`,`awbexport`.`Inbound`))))) AS `Volume`,sum(`awbexport`.`HPackage`) AS `HPackage`,sum(`awbexport`.`WPackage`) AS `WPackage`,sum(`awbexport`.`LPackage`) AS `LPackage`,min(`awbexport`.`DataIns`) AS `DataIns`,sum(if(((`awbexport`.`Stato` = 0) and (`awbexport`.`StandBy` = 0)),1,0)) AS `StatusOnProcessing`,sum(if(((`awbexport`.`Stato` >= 1) and (`awbexport`.`StandBy` = 1)),1,0)) AS `StatusStandBy`,sum(if(((`awbexport`.`Stato` = 1) and (`awbexport`.`StandBy` = 0)),1,0)) AS `StatusDelivered`,sum(if((`awbexport`.`Stato` > 1),1,0)) AS `StatusError`,sum(if((`awbexport`.`Stato` = 3),1,0)) AS `StatusIncomplete` from (`awbexport` join `session` on((`awbexport`.`sid` = `session`.`sid`))) where ((if((isnull(`awbexport`.`Stato`) or isnull(`awbexport`.`StandBy`)),0,if(((`awbexport`.`Stato` = 0) and (`awbexport`.`StandBy` = 0)),1,if(((`awbexport`.`Stato` = 1) and (`awbexport`.`StandBy` = 1)),2,if(((`awbexport`.`Stato` = 1) or (`awbexport`.`StandBy` = 0)),3,0)))) <= 3) and (not(`awbexport`.`ID` in (select `a1`.`parent_id` from `awbexport` `a1` where (`a1`.`parent_id` is not null))))) group by `awbexport`.`ID`, `awbexport`.`AWB`, `awbexport`.`obj_code`, `awbexport`.`nudo_vestito`,`awbexport`.`tipo_spedizione`,`awbexport`.`sid`)) `awbexport` join `session` `se` on((`awbexport`.`sid` = `se`.`sid`))) join `weight` `we` on(((`awbexport`.`sid` = `we`.`sid`) and (`we`.`oid` = 0)))) join `macchine` `ma` on((`se`.`macchina_id` = `ma`.`id`))) join `luoghi` `lu` on((`ma`.`luogo_id` = `lu`.`id`)))) `ae` group by `ae`.`ID`, `ae`.`awb`, `ae`.`obj_code`, `ae`.`nudo_vestito`,`ae`.`tipo_spedizione`,`ae`.`vettore`,`ae`.`azienda_id`,`ae`.`lid`,`ae`.`mid`)) `ae` left join `v_awbimport` `ai` on((`ae`.`obj_code` = `ai`.`obj_code`))) left join `vettori` `v` on((`ae`.`vettore` = `v`.`iata3digit`)));


