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

/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Dump della struttura di vista 20210222bhc4rg0cloud.v_awbstat_congruity_stati
-- Creazione di una tabella temporanea per risolvere gli errori di dipendenza della vista
DROP VIEW IF EXISTS `v_awbstat_congruity_stati`;
CREATE TABLE `v_awbstat_congruity_stati` (
	`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_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` DOUBLE 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, 
	`status` INT(11) NOT NULL
) ENGINE=MyISAM;

-- Dump della struttura di vista 20210222bhc4rg0cloud.v_awbstat_congruity_stati
-- Rimozione temporanea di tabella e creazione della struttura finale della vista
DROP TABLE IF EXISTS `v_awbstat_congruity_stati`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_awbstat_congruity_stati` AS select 
s.*
, IF (s.cancellato is not null and s.cancellato > 0, IF(s.cancellato = 1, 15, 14), IF(StatusStandBy > 0
        , IF(s.obj_code IS NULL or s.NetWeight IS NULL
            , 2
            , IF(s.IID IS NULL, -1, 1)
        )
        , IF(s.IID IS NOT NULL AND s.ID = s.IID
            , IF(s.export_ok = 0
                , IF(s.export_retry IS NULL OR s.export_retry = 0
                    , 3
                    , IF(s.export_retry <= 3
                        , IF(s.obj_code IS NULL or s.NetWeight IS NULL, 6, 4)
                        , IF(s.obj_code IS NULL or s.NetWeight IS NULL, 6, 5)
                      )
                  )
                , 8
              )
            , IF (s.IID IS NOT NULL, 
                IF(StatusDelivered > 0
                    , IF(s.I_nudo_vestito <> s.nudo_vestito
                        , 9
                        , IF(
                            s.congruity_nobj_ok > 0 AND s.congruity_ncolli_ok > 0
                            AND s.congruity_volume_ok > 0 AND s.congruity_weight_ok > 0
                            AND s.congruity_h_ok > 0 AND s.congruity_l_ok > 0 AND s.congruity_w_ok > 0
                            , 10
                            , 11
                        )
                      )
                    , 7
                ), IF(StatusDelivered > 0, 12, 13)
            )
        )
      )) AS status
from `v_awbstat_congruity` AS s;





/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
