-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Versione server:              5.7.24-log - MySQL Community Server (GPL)
-- S.O. server:                  Win64
-- HeidiSQL Versione:            11.0.0.5919
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
;
/*!40101 SET NAMES utf8 */
;
/*!50503 SET NAMES utf8mb4 */
;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
;
-- Dump della struttura di vista c4rg0air.v_sidstat_congruity
-- Creazione di una tabella temporanea per risolvere gli errori di dipendenza della vista
DROP VIEW
IF EXISTS `v_sidstat_congruity`;
CREATE TABLE
        `v_sidstat_congruity`
        (
                `awb`           VARCHAR(100) NULL COLLATE 'utf8mb4_general_ci',
                `sid`           VARCHAR(40) NULL COLLATE 'utf8mb4_general_ci',
                `tipo_spedizione`           VARCHAR(40) NULL COLLATE 'utf8mb4_general_ci',
                `UserIns`       INT(11) NOT NULL                              ,
                `DateIns`       DATETIME NOT 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(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                                           ,
                `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_weight`               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                                     ,
                `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(23,0) 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
        )
        ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dump della struttura di vista c4rg0air.v_sidstat_congruity
-- Rimozione temporanea di tabella e creazione della struttura finale della vista
DROP TABLE
        IF EXISTS `v_sidstat_congruity`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_sidstat_congruity` AS
SELECT
        `v_sidstat`.`awb`                    AS `awb`                 ,
        `v_sidstat`.`sid`                    AS `sid`                 ,
        `v_sidstat`.`tipo_spedizione`                    AS `tipo_spedizione`                 ,
        `v_sidstat`.`UserIns`                AS `UserIns`             ,
        `v_sidstat`.`DateIns`                AS `DateIns`             ,
        `v_sidstat`.`countawb`               AS `countawb`            ,
        `v_sidstat`.`vettore`                AS `vettore`             ,
        `v_sidstat`.`vettore_id`             AS `vettore_id`          ,
        `v_sidstat`.`macchina_id`            AS `macchina_id`         ,
        `v_sidstat`.`luogo_id`               AS `luogo_id`            ,
        `v_sidstat`.`azienda_id`             AS `azienda_id`          ,
        `v_sidstat`.`nacquisitions`          AS `nacquisitions`       ,
        `v_sidstat`.`nobj`                   AS `nobj`                ,
        `v_sidstat`.`ncolli`                 AS `ncolli`              ,
        `v_sidstat`.`GrossWeight`            AS `GrossWeight`         ,
        `v_sidstat`.`NetWeight`              AS `NetWeight`           ,
        `v_sidstat`.`Inbound`                AS `Inbound`             ,
        `v_sidstat`.`RealVolume`             AS `RealVolume`          ,
        `v_sidstat`.`InboundVolumeSurface`   AS `InboundVolumeSurface`,
        `v_sidstat`.`RealVolumeSurface`      AS `RealVolumeSurface`   ,
        `v_sidstat`.`Volume`                 AS `Volume`              ,
        `v_sidstat`.`DateInsNoTime`          AS `DateInsNoTime`       ,
        `v_sidstat`.`YearIns`                AS `YearIns`             ,
        `v_sidstat`.`MonthIns`               AS `MonthIns`            ,
        `v_sidstat`.`DayIns`                 AS `DayIns`              ,
        `v_sidstat`.`SMonthIns`              AS `SMonthIns`           ,
        `v_sidstat`.`QMonthIns`              AS `QMonthIns`           ,
        `v_sidstat`.`TMonthIns`              AS `TMonthIns`           ,
        `v_sidstat`.`WeekIns`                AS `WeekIns`             ,
        `v_sidstat`.`WeekDayIns`             AS `WeekDayIns`          ,
        `v_sidstat`.`DayYearIns`             AS `DayYearIns`          ,
        `v_sidstat`.`HourIns`                AS `HourIns`             ,
        `v_sidstat`.`e_GrossWeight`          AS `e_GrossWeight`       ,
        `v_sidstat`.`e_NetWeight`            AS `e_NetWeight`         ,
        `v_sidstat`.`e_Volume`               AS `e_Volume`            ,
        `v_sidstat`.`i_id`                   AS `i_id`                ,
        `v_sidstat`.`i_nobj`                 AS `i_nobj`              ,
        `v_sidstat`.`i_ncolli`               AS `i_ncolli`            ,
        `v_sidstat`.`i_NetWeight`            AS `i_weight`            ,
        `v_sidstat`.`i_Volume`               AS `i_volume`            ,
        `v_sidstat`.`i_GrossWeightSup`       AS `i_GrossWeightSup`    ,
        `v_sidstat`.`i_GrossWeightInf`       AS `i_GrossWeightInf`    ,
        `v_sidstat`.`i_NetWeightSup`         AS `i_NetWeightSup`      ,
        `v_sidstat`.`i_NetWeightInf`         AS `i_NetWeightInf`      ,
        `v_sidstat`.`i_VolumeSup`            AS `i_VolumeSup`         ,
        `v_sidstat`.`i_VolumeInf`            AS `i_VolumeInf`         ,
        IF(ISNULL(`v_sidstat`.`i_nobj`),1,0) AS `congruity_nobj_null` ,
        IF(((`v_sidstat`.`i_nobj` IS NOT NULL)
AND     (
                `v_sidstat`.`nobj`         <> `v_sidstat`.`i_nobj`)),1,0) AS `congruity_nobj_red`,
        IF(((`v_sidstat`.`i_nobj` IS NOT NULL)
AND     (
                `v_sidstat`.`nobj` = `v_sidstat`.`i_nobj`)),1,0) AS `congruity_nobj_ok`    ,
        IF(ISNULL(`v_sidstat`.`i_ncolli`),1,0)                   AS `congruity_ncolli_null`,
        IF(((`v_sidstat`.`i_ncolli` IS NOT NULL)
AND     (
                `v_sidstat`.`ncolli`         <> `v_sidstat`.`i_ncolli`)),1,0) AS `congruity_ncolli_red`,
        IF(((`v_sidstat`.`i_ncolli` IS NOT NULL)
AND     (
                `v_sidstat`.`ncolli` = `v_sidstat`.`i_ncolli`)),1,0) AS `congruity_ncolli_ok`,
        IF((ISNULL(`v_sidstat`.`i_VolumeSup`)
OR      ISNULL(`v_sidstat`.`i_VolumeInf`)),1,0) AS `congruity_volume_null`,
        IF(((`v_sidstat`.`i_VolumeSup` IS NOT NULL)
AND     (
                `v_sidstat`.`i_VolumeInf` IS NOT NULL)
AND     (
                `v_sidstat`.`e_Volume`           > `v_sidstat`.`i_VolumeSup`)),1,0) AS `congruity_volume_red`,
        IF(((`v_sidstat`.`i_VolumeSup` IS NOT NULL)
AND     (
                `v_sidstat`.`i_VolumeInf` IS NOT NULL)
AND     (
                `v_sidstat`.`e_Volume`           < `v_sidstat`.`i_VolumeInf`)),1,0) AS `congruity_volume_green`,
        IF(((`v_sidstat`.`i_VolumeSup` IS NOT NULL)
AND     (
                `v_sidstat`.`i_VolumeInf` IS NOT NULL)
AND     (
                `v_sidstat`.`e_Volume` >= `v_sidstat`.`i_VolumeInf`)
AND     (
                `v_sidstat`.`e_Volume`          <= `v_sidstat`.`i_VolumeSup`)),1,0) AS `congruity_volume_ok`,
        IF(((`v_sidstat`.`i_VolumeSup` IS NOT NULL)
AND     (
                `v_sidstat`.`i_VolumeInf` IS NOT NULL)
AND     (
                `v_sidstat`.`e_Volume`           > `v_sidstat`.`i_VolumeSup`)),(`v_sidstat`.`Volume` - `v_sidstat`.`i_Volume`),0.0) AS `diff_Volume_red`,
        IF(((`v_sidstat`.`i_VolumeSup` IS NOT NULL)
AND     (
                `v_sidstat`.`i_VolumeInf` IS NOT NULL)
AND     (
                `v_sidstat`.`e_Volume` > `v_sidstat`.`i_VolumeSup`)),1,0) AS `sum_Volume_red`,
        IF((ISNULL(`v_sidstat`.`i_NetWeightSup`)
OR      ISNULL(`v_sidstat`.`i_NetWeightInf`)),1,0) AS `congruity_weight_null`,
        IF(((`v_sidstat`.`i_NetWeightSup` IS NOT NULL)
AND     (
                `v_sidstat`.`i_NetWeightInf` IS NOT NULL)
AND     (
                `v_sidstat`.`e_NetWeight`           > `v_sidstat`.`i_NetWeightSup`)),1,0) AS `congruity_weight_red`,
        IF(((`v_sidstat`.`i_NetWeightSup` IS NOT NULL)
AND     (
                `v_sidstat`.`i_NetWeightInf` IS NOT NULL)
AND     (
                `v_sidstat`.`e_NetWeight`           < `v_sidstat`.`i_NetWeightInf`)),1,0) AS `congruity_weight_green`,
        IF(((`v_sidstat`.`i_NetWeightSup` IS NOT NULL)
AND     (
                `v_sidstat`.`i_NetWeightInf` IS NOT NULL)
AND     (
                `v_sidstat`.`e_NetWeight` >= `v_sidstat`.`i_NetWeightInf`)
AND     (
                `v_sidstat`.`e_NetWeight`          <= `v_sidstat`.`i_NetWeightSup`)),1,0) AS `congruity_weight_ok`,
        IF(((`v_sidstat`.`i_NetWeightSup` IS NOT NULL)
AND     (
                `v_sidstat`.`i_NetWeightInf` IS NOT NULL)
AND     (
                `v_sidstat`.`e_NetWeight`           > `v_sidstat`.`i_NetWeightSup`)),(`v_sidstat`.`NetWeight` - `v_sidstat`.`i_NetWeight`),0.0) AS `diff_NetWeight_red`,
        IF(((`v_sidstat`.`i_NetWeightSup` IS NOT NULL)
AND     (
                `v_sidstat`.`i_NetWeightInf` IS NOT NULL)
AND     (
                `v_sidstat`.`e_NetWeight` > `v_sidstat`.`i_NetWeightSup`)),1,0) AS `sum_NetWeight_red` ,
        `v_sidstat`.`StatusOnProcessing`                                        AS `StatusOnProcessing`,
        `v_sidstat`.`StatusStandBy`                                             AS `StatusStandBy`     ,
        `v_sidstat`.`StatusDelivered`                                           AS `StatusDelivered`  ,
        `v_sidstat`.`StatusError`                                           AS `StatusError`
FROM
        `v_sidstat`;

/*!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 */
;