--
-- Host: localhost    Database: c4rg0airv2
-- ------------------------------------------------------


--
-- Temporary table structure for view `v_awbstat`
--

/*!50001 DROP VIEW IF EXISTS `v_awbstat`*/;
/*!50001 CREATE VIEW `v_awbstat` AS SELECT 
 1 AS `awb`,
 1 AS `tipo_spedizione`,
 1 AS `countawb`,
 1 AS `vettore`,
 1 AS `vettore_id`,
 1 AS `macchina_id`,
 1 AS `luogo_id`,
 1 AS `azienda_id`,
 1 AS `nacquisitions`,
 1 AS `nobj`,
 1 AS `ncolli`,
 1 AS `GrossWeight`,
 1 AS `NetWeight`,
 1 AS `Inbound`,
 1 AS `RealVolume`,
 1 AS `InboundVolumeSurface`,
 1 AS `RealVolumeSurface`,
 1 AS `Volume`,
 1 AS `DateInsNoTime`,
 1 AS `DateIns`,
 1 AS `YearIns`,
 1 AS `MonthIns`,
 1 AS `DayIns`,
 1 AS `SMonthIns`,
 1 AS `QMonthIns`,
 1 AS `TMonthIns`,
 1 AS `WeekIns`,
 1 AS `WeekDayIns`,
 1 AS `DayYearIns`,
 1 AS `HourIns`,
 1 AS `e_GrossWeight`,
 1 AS `e_NetWeight`,
 1 AS `e_Volume`,
 1 AS `i_id`,
 1 AS `i_nobj`,
 1 AS `i_ncolli`,
 1 AS `i_NetWeight`,
 1 AS `i_Volume`,
 1 AS `i_GrossWeightSup`,
 1 AS `i_GrossWeightInf`,
 1 AS `i_NetWeightSup`,
 1 AS `i_NetWeightInf`,
 1 AS `i_VolumeSup`,
 1 AS `i_VolumeInf`,
 1 AS `StatusOnProcessing`,
 1 AS `StatusStandBy`,
 1 AS `StatusDelivered`*/;

--
-- Final view structure for view `v_awbstat`
--

/*!50001 DROP VIEW IF EXISTS `v_awbstat`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`c4rg0`@`localhost` SQL SECURITY DEFINER */
/*!50001 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 + (`c4rg0airv2`.`decode`.`decvalue` / 100000.0)) AS `value` from `c4rg0airv2`.`decode` where ((`c4rg0airv2`.`decode`.`decclass` = 'CONGRUITA') and (`c4rg0airv2`.`decode`.`deckey` = 'peso.valORe.scostamento.sup')) limit 1)) * 1000) as signed) AS `i_GrossWeightSup`,cast(((`ai`.`GrossWeight` * (select (1.0 - (`c4rg0airv2`.`decode`.`decvalue` / 100000.0)) AS `value` from `c4rg0airv2`.`decode` where ((`c4rg0airv2`.`decode`.`decclass` = 'CONGRUITA') and (`c4rg0airv2`.`decode`.`deckey` = 'peso.valORe.scostamento.inf')) limit 1)) * 1000) as signed) AS `i_GrossWeightInf`,cast(((`ai`.`NetWeight` * (select (1.0 + (`c4rg0airv2`.`decode`.`decvalue` / 100000.0)) AS `value` from `c4rg0airv2`.`decode` where ((`c4rg0airv2`.`decode`.`decclass` = 'CONGRUITA') and (`c4rg0airv2`.`decode`.`deckey` = 'peso.valORe.scostamento.sup')) limit 1)) * 1000) as signed) AS `i_NetWeightSup`,cast(((`ai`.`NetWeight` * (select (1.0 - (`c4rg0airv2`.`decode`.`decvalue` / 100000.0)) AS `value` from `c4rg0airv2`.`decode` where ((`c4rg0airv2`.`decode`.`decclass` = 'CONGRUITA') and (`c4rg0airv2`.`decode`.`deckey` = 'peso.valORe.scostamento.inf')) limit 1)) * 1000) as signed) AS `i_NetWeightInf`,cast(((`ai`.`Volume` * (select (1.0 + (`c4rg0airv2`.`decode`.`decvalue` / 100000.0)) AS `value` from `c4rg0airv2`.`decode` where ((`c4rg0airv2`.`decode`.`decclass` = 'CONGRUITA') and (`c4rg0airv2`.`decode`.`deckey` = 'volume.valORe.scostamento.sup')) limit 1)) * 1000) as signed) AS `i_VolumeSup`,cast(((`ai`.`Volume` * (select (1.0 - (`c4rg0airv2`.`decode`.`decvalue` / 100000.0)) AS `value` from `c4rg0airv2`.`decode` where ((`c4rg0airv2`.`decode`.`decclass` = 'CONGRUITA') and (`c4rg0airv2`.`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` 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` 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`,`awbexport`.`nobj` AS `nobj`,`awbexport`.`ncolli` AS `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` from ((((((select `c4rg0airv2`.`awbexport`.`AWB` AS `awb`,`c4rg0airv2`.`awbexport`.`tipo_spedizione` AS `tipo_spedizione`,`c4rg0airv2`.`awbexport`.`sid` AS `sid`,count(`c4rg0airv2`.`awbexport`.`ID`) AS `nobj`,sum(if((isnull(`c4rg0airv2`.`awbexport`.`ncolli`) or (`c4rg0airv2`.`awbexport`.`ncolli` <= 0)),1,`c4rg0airv2`.`awbexport`.`ncolli`)) AS `ncolli`,sum(`c4rg0airv2`.`awbexport`.`Inbound`) AS `Inbound`,sum(`c4rg0airv2`.`awbexport`.`RealVolume`) AS `RealVolume`,sum(`c4rg0airv2`.`awbexport`.`InboundVolumeSurface`) AS `InboundVolumeSurface`,sum(`c4rg0airv2`.`awbexport`.`RealVolumeSurface`) AS `RealVolumeSurface`,sum(if((isnull(`c4rg0airv2`.`awbexport`.`VolumeFlag`) or (`c4rg0airv2`.`awbexport`.`VolumeFlag` <= 1)),`c4rg0airv2`.`awbexport`.`Inbound`,if((`c4rg0airv2`.`awbexport`.`VolumeFlag` = 2),`c4rg0airv2`.`awbexport`.`RealVolume`,if((`c4rg0airv2`.`awbexport`.`VolumeFlag` = 3),`c4rg0airv2`.`awbexport`.`InboundVolumeSurface`,if((`c4rg0airv2`.`awbexport`.`VolumeFlag` = 4),`c4rg0airv2`.`awbexport`.`RealVolumeSurface`,`c4rg0airv2`.`awbexport`.`Inbound`))))) AS `Volume`,min(`c4rg0airv2`.`awbexport`.`DataIns`) AS `DataIns`,sum(if(((`c4rg0airv2`.`awbexport`.`Stato` = 0) and (`c4rg0airv2`.`awbexport`.`StandBy` = 0)),1,0)) AS `StatusOnProcessing`,sum(if(((`c4rg0airv2`.`awbexport`.`Stato` = 1) and (`c4rg0airv2`.`awbexport`.`StandBy` = 1)),1,0)) AS `StatusStandBy`,sum(if(((`c4rg0airv2`.`awbexport`.`Stato` = 1) and (`c4rg0airv2`.`awbexport`.`StandBy` = 0)),1,0)) AS `StatusDelivered` from (`c4rg0airv2`.`awbexport` join `c4rg0airv2`.`session` on((`c4rg0airv2`.`awbexport`.`sid` = `c4rg0airv2`.`session`.`sid`))) where ((if((isnull(`c4rg0airv2`.`awbexport`.`Stato`) or isnull(`c4rg0airv2`.`awbexport`.`StandBy`)),0,if(((`c4rg0airv2`.`awbexport`.`Stato` = 0) and (`c4rg0airv2`.`awbexport`.`StandBy` = 0)),1,if(((`c4rg0airv2`.`awbexport`.`Stato` = 1) and (`c4rg0airv2`.`awbexport`.`StandBy` = 1)),2,if(((`c4rg0airv2`.`awbexport`.`Stato` = 1) or (`c4rg0airv2`.`awbexport`.`StandBy` = 0)),3,0)))) <= 3) and (isnull(`c4rg0airv2`.`awbexport`.`cancellato`) or (`c4rg0airv2`.`awbexport`.`cancellato` <> 1)) and (isnull(`c4rg0airv2`.`session`.`cancellato`) or (`c4rg0airv2`.`session`.`cancellato` <> 1)) and (not(`c4rg0airv2`.`awbexport`.`ID` in (select `a1`.`parent_id` from `c4rg0airv2`.`awbexport` `a1` where (`a1`.`parent_id` is not null))))) group by `c4rg0airv2`.`awbexport`.`AWB`,`c4rg0airv2`.`awbexport`.`tipo_spedizione`,`c4rg0airv2`.`awbexport`.`sid`)) `awbexport` join `c4rg0airv2`.`session` `se` on((`awbexport`.`sid` = `se`.`sid`))) join `c4rg0airv2`.`weight` `we` on(((`awbexport`.`sid` = `we`.`sid`) and (`we`.`oid` = 0)))) join `c4rg0airv2`.`macchine` `ma` on((`se`.`macchina_id` = `ma`.`id`))) join `c4rg0airv2`.`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 `c4rg0airv2`.`awbimport` `ai` on((`ae`.`awb` = `ai`.`AWB`))) left join `c4rg0airv2`.`vettori` `v` on((`ae`.`vettore` = `v`.`iata3digit`))) */;


