Share
Explore
KA - Cheating detection tool
SGN-LAZADA
Overall
Dịch vụ: SGN-LAZADA
Trạng thái đơn hàng: ASSIGNING
1 - Metabase
Extract data for detection
Viết card tính điểm và phân loại 2 tập Cancel và Unhide
total_pọint
= sender_point + pickup_ward_point + dropoff_ward_point + path_wtow_point + dropoff_district_point+distance_point
COD > 100000 hoặc COD = 0 thì
unhide
Cate = Return và distance >=35 thì
cancel
Cate = MP và distance >= 25 thì
cancel
Total_point >= 5.5 thì
cancel
withstp as (select id , 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_namefrom "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 pointorder 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 FForder by total_point desc)
select * from finwhere true and tracking_number not like '%AHMRT%'[[and action = {{action}}]] order by 1 2 - Google Apps Script
> Files Unhide order
function unhide_lzd() { var url1 = "https://admin.ahamove.com/public/v1/bi/metabase_card?cardid=21544&action=unhide" var response1 = UrlFetchApp.fetch(url1); var dataSet = JSON.parse(response1); Logger.log(dataSet); for (i=0 ; i < dataSet.length; i++) { var id = dataSet[i].id; Logger.log(id);// for (j = 0 ; j < re.length; j++)// { unhide_order(id);// } }
var url2 = "https://admin.ahamove.com/public/v1/bi/metabase_card?cardid=21544&action=cancel" var response2 = UrlFetchApp.fetch(url2); var dataSet2 = JSON.parse(response2); Logger.log(dataSet2); for (i=0 ; i < dataSet2.length; i++) { var id = dataSet2[i].id; Logger.log(id);// for (j = 0 ; j < re.length; j++)// { cancel_cheating(id);// } }
}3 - Setup Triggers
Auto
Function: Unhide_lzd
Notify daily 2PM to 3PM
Action: unhide > trả về unhide_oder ID
Action: cancel > trả về cancel_cheating ID
Manual (bị lỗi không tự động triggers)
Chọn function Unhide_order
Bấm Run để chạy trigger thủ công.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.