-- --------------------------------------------------------
-- Versione server:              5.7.24-log - MySQL Community Server (GPL)
-- S.O. server:                  Win64
-- HeidiSQL Versione:            11.0.0.5919
-- --------------------------------------------------------
;
;
;
;
;
-- Dump della struttura di vista c4rg0air.v_awbstat
DROP VIEW
IF EXISTS `v_awbstat`;
    -- Creazione di una tabella temporanea per risolvere gli errori di dipendenza della vista
    CREATE TABLE `v_awbstat`
                 (
                              `awb`                 VARCHAR(100) NULL COLLATE 'utf8mb4_general_ci'
                            ,  `tipo_spedizione` VARCHAR(100) NULL COLLATE 'utf8mb4_general_ci'
                            , `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`          BIGINT(21) NOT NULL
                            , `ncolli`        DECIMAL(54,0) NULL
                            , `GrossWeight` DOUBLE(23,0) NULL
                            , `NetWeight` DOUBLE(23,0) 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(1) 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_NetWeight`          DECIMAL(8,2) NULL
                            , `i_Volume`             DECIMAL(8,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
                            , `StatusOnProcessing`   DECIMAL(45,0) NULL
                            , `StatusStandBy`        DECIMAL(45,0) NULL
                            , `StatusDelivered`      DECIMAL(45,0) NULL
                            , `StatusError`          DECIMAL(45,0) NULL
                 )
                 ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    ;
    
    -- Dump della struttura di vista c4rg0air.v_awbstat
    DROP VIEW
    IF EXISTS `v_awbstat`;
        -- Rimozione temporanea di tabella e creazione della struttura finale della vista
        DROP TABLE IF EXISTS `v_awbstat`
        ;
        
        CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_awbstat` AS
        select
                  `ae`.`awb`                                  AS `awb`
                ,`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`.`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`
                ,`ai`.`ID`                                    AS `i_id`
                ,`ai`.`TotPackages`                           AS `i_nobj`
                ,`ai`.`ncolli`                                AS `i_ncolli`
                ,`ai`.`NetWeight`                             AS `i_NetWeight`
                ,`ai`.`Volume`                                AS `i_Volume`
                , cast(((`ai`.`GrossWeight` *
                  (
                         select
                                (1.0 + (`decode`.`decvalue` / 100000.0)) AS `value`
                         from
                                `decode`
                         where
                                (
                                       (
                                              `decode`.`decclass` = 'CONGRUITA'
                                       )
                                       and
                                       (
                                              `decode`.`deckey` = 'peso.valORe.scostamento.sup'
                                       )
                                )
                                limit 1
                  )
                  ) * 1000) as signed) AS `i_GrossWeightSup`
                , cast(((`ai`.`GrossWeight` *
                  (
                         select
                                (1.0 - (`decode`.`decvalue` / 100000.0)) AS `value`
                         from
                                `decode`
                         where
                                (
                                       (
                                              `decode`.`decclass` = 'CONGRUITA'
                                       )
                                       and
                                       (
                                              `decode`.`deckey` = 'peso.valORe.scostamento.inf'
                                       )
                                )
                                limit 1
                  )
                  ) * 1000) as signed) AS `i_GrossWeightInf`
                , cast(((`ai`.`NetWeight` *
                  (
                         select
                                (1.0 + (`decode`.`decvalue` / 100000.0)) AS `value`
                         from
                                `decode`
                         where
                                (
                                       (
                                              `decode`.`decclass` = 'CONGRUITA'
                                       )
                                       and
                                       (
                                              `decode`.`deckey` = 'peso.valORe.scostamento.sup'
                                       )
                                )
                                limit 1
                  )
                  ) * 1000) as signed) AS `i_NetWeightSup`
                , cast(((`ai`.`NetWeight` *
                  (
                         select
                                (1.0 - (`decode`.`decvalue` / 100000.0)) AS `value`
                         from
                                `decode`
                         where
                                (
                                       (
                                              `decode`.`decclass` = 'CONGRUITA'
                                       )
                                       and
                                       (
                                              `decode`.`deckey` = 'peso.valORe.scostamento.inf'
                                       )
                                )
                                limit 1
                  )
                  ) * 1000) as signed) AS `i_NetWeightInf`
                , cast(((`ai`.`Volume` *
                  (
                         select
                                (1.0 + (`decode`.`decvalue` / 100000.0)) AS `value`
                         from
                                `decode`
                         where
                                (
                                       (
                                              `decode`.`decclass` = 'CONGRUITA'
                                       )
                                       and
                                       (
                                              `decode`.`deckey` = 'volume.valORe.scostamento.sup'
                                       )
                                )
                                limit 1
                  )
                  ) * 1000) as signed) AS `i_VolumeSup`
                , cast(((`ai`.`Volume` *
                  (
                         select
                                (1.0 - (`decode`.`decvalue` / 100000.0)) AS `value`
                         from
                                `decode`
                         where
                                (
                                       (
                                              `decode`.`decclass` = 'CONGRUITA'
                                       )
                                       and
                                       (
                                              `decode`.`deckey` = 'volume.valORe.scostamento.inf'
                                       )
                                )
                                limit 1
                  )
                  ) * 1000) as signed)     AS `i_VolumeInf`
                ,`ae`.`StatusOnProcessing` AS `StatusOnProcessing`
                ,`ae`.`StatusStandBy`      AS `StatusStandBy`
                ,`ae`.`StatusDelivered`    AS `StatusDelivered`
                ,`ae`.`StatusError`        AS `StatusError`
                ,`ae`.`StatusIncomplete`        AS `StatusIncomplete`

        from
                  (((
                  (
                           select
                                    `ae`.`awb`                                    AS `awb`
                                  ,`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 `ae`.`awb`)                    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`
                                  , 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`.`awb`             AS `awb`
                                                ,`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`
                                                ,`nobj`
                                                ,`ncolli`
                                                ,`we`.`gross`                         AS `GrossWeight`
                                                ,`we`.`net`                           AS `NetWeight`
                                                ,`awbexport`.`Inbound`                AS `Inbound`
                                                ,`awbexport`.`RealVolume`             AS `RealVolume`
                                                ,`awbexport`.`InboundVolumeSurface`   AS `InboundVolumeSurface`
                                                ,`awbexport`.`RealVolumeSurface`      AS `RealVolumeSurface`
                                                ,`awbexport`.`Volume`                 AS `Volume`
                                                ,`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`.`AWB`       AS `awb`
                                                                     ,`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`.`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`
                                                                  , 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` INNER 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
                                                                             (
                                                                                      isnull(`awbexport`.`cancellato`)
                                                                                      or
                                                                                      (
                                                                                               `awbexport`.`cancellato` <> 1
                                                                                      )
                                                                             )
                                                                             and
                                                                             (
                                                                                      isnull(`session`.`cancellato`)
                                                                                      or
                                                                                      (
                                                                                               `session`.`cancellato` <> 1
                                                                                      )
                                                                             )
                                                                            and `awbexport`.id NOT IN (SELECT a1.parent_id FROM `awbexport` AS a1 WHERE a1.parent_id IS NOT NULL)
                                                                    )
                                                           group by
                                                                    `awbexport`.`AWB`
                                                                  ,`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`.`awb`
                                  ,`ae`.`tipo_spedizione`
                                  ,`ae`.`vettore`
                                  ,`ae`.`azienda_id`
                                  ,`ae`.`lid`
                                  ,`ae`.`mid`
                  )
                  ) `ae`
                  left join
                            `awbimport` `ai`
                            on
                                      (
                                                (
                                                          `ae`.`awb` = `ai`.`AWB`
                                                )
                                      )
                  )
                  left join
                            `vettori` `v`
                            on
                                      (
                                                (
                                                          `ae`.`vettore` = `v`.`iata3digit`
                                                )
                                      )
                  )
        ;
        
        /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */
        ;
        /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */
        ;
        /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */
        ;