, to_char(to_timestamp(o.create_time),'YYYY-MM-DD HH24:MI:SS') as create_time
, path->0->>'name' as sender_name
, right(path->0->>'mobile',9) as sender_mobile
, (path->1->>'weight')::numeric as weight
, (path->1->>'tracking_number') as tracking_number
, o.distance
, (o.path->0->>'address') as pickup
, (path->1->>'address') as dropoff
, (path->0->>'lat') as pickup_lat
, (path->0->>'lng') as pickup_lng
, (path->1->>'lat') as dropoff_lat
, (path->1->>'lng') as dropoff_lng
, total_fee
, coalesce(cast((path->1->>'cod') as numeric),0) as cod
, supplier_id, supplier_name
from "order" o
where 1=1
and order_date >= current_date - interval '0 day'
and service_id = 'SGN-LAZADA'
and status = 'ASSIGNING'
and (Case when
path->1->>'tracking_number' not like '%AHMCR%' then distance >= 14
when
path->1->>'tracking_number' like '%AHMCR%' then distance >= 25 end )
)
, stp_location as (
select
g.id, case when tracking_number like'%AHMCR%'then'Return' else 'MP' end as cate
, create_time
, tracking_number
, distance
, sender_name
, sender_mobile
, pickup
, dropoff
, c2.name_vn as pickup_district
, c1.name_vn as dropoff_district
, c4.name_vn||' '||c2.name_vn as pickup_ward
, c3.name_vn||' '||c1.name_vn as dropoff_ward
, c4.name_vn||' '||c2.name_vn||'-'||c3.name_vn||' '||c1.name_vn as path_wtow
, total_fee
, cod
from stp g
left join admin_areas as c1
on ST_Contains(c1.the_geom, ST_SetSRID(ST_POINT(g.dropoff_lng ::float, g.dropoff_lat ::float),4326))
left join admin_areas as c2
on ST_Contains(c2.the_geom, ST_SetSRID(ST_POINT(g.pickup_lng ::float, g.pickup_lat ::float),4326))
left join admin_areas as c3
on ST_Contains(c3.the_geom, ST_SetSRID(ST_POINT(g.dropoff_lng ::float, g.dropoff_lat ::float),4326))
left join admin_areas as c4
on ST_Contains(c4.the_geom, ST_SetSRID(ST_POINT(g.pickup_lng ::float, g.pickup_lat ::float),4326))
where 1=1
and c1.level = '3'
and c2.level = '3'
and c3.level = '4'
and c4.level = '4'
)
, point as ( select *,
case when sender_mobile in (select seller_right from custom.lzd_blacklist) then 2 else 0 end as sender_point,
case when pickup_ward in (select pickup_ward from custom.lzd_blacklist) then 1 else 0 end as pickup_ward_point,
case when dropoff_ward in (select dropoff_ward from custom.lzd_blacklist) then 3 else 0 end as dropoff_ward_point,
case when path_wtow in (select path_wtow from custom.lzd_blacklist) then 5 else 0 end as path_wtow_point,
case when dropoff_district in (select dropoff_district from custom.lzd_blacklist) then 1 else 0 end as dropoff_district_point,
case when sender_mobile in (select seller_right from custom.lzd_blacklist) and cod between 1000 and 150000 then 1 else 0 end as cod_point,
(distance/15) as distance_point
from stp_location )
, total_point as (select sender_point+pickup_ward_point+dropoff_ward_point+path_wtow_point+dropoff_district_point+distance_point as total_point, * from point
order by 1 desc)
, fin as (select case
when cod > 100000 or cod = 0 then'unhide'
when cate = 'Return' and distance >=35 then'cancel'
when cate = 'MP'and distance >= 25 then'cancel'
when total_point >= 5.5 then'cancel'
else 'unhide' end as action, *
from total_point
where right(sender_mobile,9) not in (select right(mobile,9) from custom.freshfood) -- loại list user FF
KA - Cheating detection tool
