45 lines
1.3 KiB
SQL
45 lines
1.3 KiB
SQL
CREATE OR REPLACE DEFINER=`vn`@`localhost`
|
|
SQL SECURITY DEFINER
|
|
VIEW `vn`.`zoneEstimatedDelivery`
|
|
AS SELECT `t`.`zoneFk` AS `zoneFk`,
|
|
`zc`.`hour` AS `zoneClosureHour`,
|
|
`z`.`hour` AS `zoneHour`,
|
|
`sv`.`volume` AS `volume`,
|
|
`al`.`hasToRecalcPrice` AS `hasToRecalcPrice`,
|
|
`lhp`.`m3` AS `m3`,
|
|
`dl`.`minSpeed` AS `minSpeed`
|
|
FROM (
|
|
(
|
|
(
|
|
(
|
|
(
|
|
(
|
|
(
|
|
(
|
|
(
|
|
`vn`.`ticket` `t`
|
|
JOIN `vn`.`ticketStateToday` `tst` ON(`tst`.`ticketFk` = `t`.`id`)
|
|
)
|
|
JOIN `vn`.`state` `s` ON(`s`.`id` = `tst`.`state`)
|
|
)
|
|
JOIN `vn`.`saleVolume` `sv` ON(`sv`.`ticketFk` = `t`.`id`)
|
|
)
|
|
LEFT JOIN `vn`.`lastHourProduction` `lhp` ON(`lhp`.`warehouseFk` = `t`.`warehouseFk`)
|
|
)
|
|
JOIN `vn`.`warehouse` `w` ON(`w`.`id` = `t`.`warehouseFk`)
|
|
)
|
|
) STRAIGHT_JOIN `vn`.`zone` `z` ON(`z`.`id` = `t`.`zoneFk`)
|
|
)
|
|
LEFT JOIN `vn`.`zoneClosure` `zc` ON(
|
|
`zc`.`zoneFk` = `t`.`zoneFk`
|
|
AND `zc`.`dated` = `util`.`VN_CURDATE`()
|
|
)
|
|
)
|
|
LEFT JOIN `cache`.`departure_limit` `dl` ON(
|
|
`dl`.`warehouse_id` = `t`.`warehouseFk`
|
|
AND `dl`.`fecha` = `util`.`VN_CURDATE`()
|
|
)
|
|
)
|
|
JOIN `vn`.`alertLevel` `al` ON(`al`.`id` = `s`.`alertLevel`)
|
|
WHERE `w`.`hasProduction` <> 0
|
|
AND cast(`t`.`shipped` AS date) = `util`.`VN_CURDATE`() |