SELECT
s.flight_no,
s.car_logo_no,
s.time,
s.is_upload_naga,
s.totalExtractedGoodsCount,
s.alreadyExtractedGoodsCount,
(
CASE
WHEN (s.totalExtractedGoodsCount-s.alreadyExtractedGoodsCount)=0 THEN '1'
WHEN s.actual_arrive_time is not null THEN '2'
WHEN s.arrive_time > NOW() THEN '3'
ELSE '4' END
) taskType,
(
CASE
when (s.totalExtractedGoodsCount - s.alreadyExtractedGoodsCount) > 0
THEN (s.totalExtractedGoodsCount - s.alreadyExtractedGoodsCount)
ELSE 0 END
) unAlreadyExtractedGoodsCount
FROM
(SELECT * FROM (SELECT
a.flight_no,
a.car_logo_no,
(
CASE
WHEN a.actual_arrive_time IS NOT NULL THEN
a.actual_arrive_time
ELSE
a.arrive_time
END
) time,
a.arrive_time,
a.actual_arrive_time,
a.is_transfer,
b.last_extract_time,
b.is_dropped,
b.is_upload_naga,
b.is_back_up_car_logo,
(
SELECT
sum(c.total_waybill_count)
FROM
tdop_bulk_shipping_extract_task c
WHERE
c.car_logo_no = b.car_logo_no
AND c.transit_depot_no = '755X'
AND c.slice_id = '55'
AND c.state = '1'
AND c.is_transfer = (
CASE WHEN EXISTS (
SELECT 1 FROM tdop_bulk_shipping_extract_task d
WHERE d.slice_id = '55'
AND d.car_logo_no = b.car_logo_no
AND d.transit_depot_no = '755X'
AND d.state = '1' AND d.is_transfer = 0
) THEN 0 ELSE 1 END
)
) totalExtractedGoodsCount,
(
SELECT
count(*)
FROM
tdop_extract_goods_waybill w
WHERE
w.car_logo_no = b.car_logo_no
AND w.transit_depot_no = '755X'
AND w.slice_id = '55'
AND w. STATUS = 3
) alreadyExtractedGoodsCount
FROM
tdop_bulk_shipping_extract_task a,
tdop_bulk_shipping_extract_car_logo b
WHERE
a.transit_depot_no = '755X'
AND a.slice_id = '55'
AND b.transit_depot_no = '755X'
AND b.slice_id = '55'
AND a.car_logo_no = b.car_logo_no
AND a.state = '1'
GROUP BY
a.flight_no,
a.car_logo_no) t
WHERE
CASE
WHEN t.is_dropped = 1 THEN t.last_extract_time BETWEEN DATE_SUB(NOW(),INTERVAL 8 HOUR) and NOW()
WHEN t.is_back_up_car_logo = 1 THEN t.time BETWEEN DATE_SUB(NOW(),INTERVAL 12 HOUR) AND NOW()
ELSE t.arrive_time BETWEEN DATE_SUB(NOW(),INTERVAL 6 HOUR) AND DATE_ADD(NOW(),INTERVAL 2 HOUR) END ) s
SELECT
s.flight_no,
s.car_logo_no,
s.time,
s.is_upload_naga,
s.alreadyExtractedGoodsCount,
s.totalExtractedGoodsCount,
(
CASE
WHEN (s.totalExtractedGoodsCount-s.alreadyExtractedGoodsCount)=0 THEN '1'
WHEN s.actual_arrive_time is not null THEN '2'
WHEN s.arrive_time > NOW() THEN '3'
ELSE '4' END
) taskType,
(
CASE
when (s.totalExtractedGoodsCount - s.alreadyExtractedGoodsCount) > 0 THEN (s.totalExtractedGoodsCount - s.alreadyExtractedGoodsCount)
ELSE 0 END
) unAlreadyExtractedGoodsCount
FROM
(SELECT *,(
SELECT
count(*)
FROM
tdop_extract_goods_waybill w
WHERE
w.car_logo_no = t.car_logo_no
AND w.transit_depot_no = '010RA'
AND w.slice_id = '51'
AND w. STATUS = 3
) alreadyExtractedGoodsCount,
(
SELECT
sum(c.total_waybill_count)
FROM
tdop_bulk_shipping_extract_task c
WHERE
c.car_logo_no = t.car_logo_no
AND c.transit_depot_no = '010RA'
AND c.slice_id = '51'
AND c.state = '1'
AND c.is_transfer = (
CASE WHEN EXISTS (
SELECT 1 FROM tdop_bulk_shipping_extract_task d
WHERE d.slice_id = '51'
AND d.car_logo_no = t.car_logo_no
AND d.transit_depot_no = '010RA'
AND d.state = '1' AND d.is_transfer = 0
) THEN 0 ELSE 1 END
)
)totalExtractedGoodsCount
FROM (SELECT
a.flight_no,
a.car_logo_no,
(
CASE
WHEN a.actual_arrive_time IS NULL THEN
a.arrive_time
ELSE
a.actual_arrive_time
END
) time,
a.arrive_time,
a.actual_arrive_time,
a.is_transfer,
b.last_extract_time,
b.is_dropped,
b.is_upload_naga,
b.is_back_up_car_logo
FROM
tdop_bulk_shipping_extract_task a,
tdop_bulk_shipping_extract_car_logo b
WHERE
a.transit_depot_no = '010RA'
AND a.slice_id = '51'
AND b.transit_depot_no = '010RA'
AND b.slice_id = '51'
AND a.car_logo_no = b.car_logo_no
AND a.state = '1'
AND
CASE
WHEN b.is_dropped = 0 AND b.is_back_up_car_logo =0 THEN a.arrive_time BETWEEN DATE_SUB(NOW(),INTERVAL 6 HOUR) AND DATE_ADD(NOW(),INTERVAL 2 HOUR)
WHEN b.is_dropped = 1 THEN b.last_extract_time BETWEEN DATE_SUB(NOW(),INTERVAL 8 HOUR) and NOW()
ELSE a.arrive_time BETWEEN DATE_SUB(NOW(),INTERVAL 12 HOUR) AND NOW() END
GROUP BY
a.flight_no,
a.car_logo_no) t) s
