40 lines
1014 B
MySQL
40 lines
1014 B
MySQL
|
CREATE OR REPLACE DEFINER=`root`@`localhost`
|
||
|
|
SQL SECURITY DEFINER
|
||
|
|
VIEW `srt`.`bufferFreeLength`
|
||
|
|
AS SELECT cast(`b`.`id` AS decimal(10, 0)) AS `bufferFk`,
|
||
|
|
`b`.`code` AS `bufferName`,
|
||
|
|
cast(
|
||
|
|
`b`.`length` - sum(IFNULL(`e`.`length`, 900) + 500) AS decimal(10, 0)
|
||
|
|
) AS `freeLength`,
|
||
|
|
cast(
|
||
|
|
sum(IFNULL(`e`.`length`, 900) + 500) AS decimal(10, 0)
|
||
|
|
) AS `saturacion`,
|
||
|
|
`bs`.`description` AS `state`,
|
||
|
|
`b`.`stateFk` AS `stateFk`,
|
||
|
|
`b`.`typeFk` AS `typeFk`,
|
||
|
|
`bt`.`typeName` AS `typeName`,
|
||
|
|
COUNT(`e`.`id`) AS `stock`,
|
||
|
|
cast(
|
||
|
|
`b`.`length` - sum(
|
||
|
|
IF(
|
||
|
|
`es`.`description` = 'STORED',
|
||
|
|
`e`.`length` + 500,
|
||
|
|
0
|
||
|
|
)
|
||
|
|
) AS decimal(10, 0)
|
||
|
|
) AS `RealFreeLength`
|
||
|
|
FROM (
|
||
|
|
(
|
||
|
|
(
|
||
|
|
(
|
||
|
|
`srt`.`buffer` `b`
|
||
|
|
LEFT JOIN `srt`.`expedition` `e` ON(`e`.`bufferFk` = `b`.`id`)
|
||
|
|
)
|
||
|
|
LEFT JOIN `srt`.`expeditionState` `es` ON(`es`.`id` = `e`.`stateFk`)
|
||
|
|
)
|
||
|
|
JOIN `srt`.`bufferType` `bt` ON(`bt`.`id` = `b`.`typeFk`)
|
||
|
|
)
|
||
|
|
JOIN `srt`.`bufferState` `bs` ON(`bs`.`id` = `b`.`stateFk`)
|
||
|
|
)
|
||
|
|
GROUP BY `b`.`id`
|