public static async Task <IEnumerable <e.target_route> > GetRoutes(e.trace_param param) { using (var db = d.ConnectionFactory()) { return(await db.QueryAsync <e.target_route>( @"SELECT lat, lng FROM people_history WHERE mhash=@mhash AND timestamp BETWEEN @start_date AND @end_date ORDER BY timestamp" , param)); } }
public static async Task <e.trace_result> GetTraceData(e.trace_param param) { using (var db = d.ConnectionFactory()) { var rotues = await GetRoutes(param); var contactPeople = await db.QueryAsync <e.contact_person>( $@"WITH tbl AS ( SELECT * FROM people_history WHERE timestamp BETWEEN @start_date AND @end_date ) SELECT mhash, count, timestamp AS last_contact, lat, lng FROM ( SELECT *, MAX(r) OVER (PARTITION BY mhash) AS count FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY mhash ORDER BY timestamp DESC) AS r FROM ( SELECT t2.mhash, t2.lat, t2.lng, t1.timestamp, dbo.GetDistance(t1.lat, t1.lng, t2.lat, t2.lng) AS distance FROM ( SELECT mhash, lat, lng, timestamp, DATEADD(MI, -{v.ContactTime}, timestamp) AS start_time, DATEADD(MI, {v.ContactTime}, timestamp) AS end_time FROM tbl WHERE mhash=@mhash ) t1, ( SELECT mhash, lat, lng, timestamp FROM tbl WHERE mhash<>@mhash ) t2 WHERE t2.timestamp BETWEEN t1.start_time AND t1.end_time ) t3 WHERE distance < 3 ) t4 ) t5 WHERE r=1" , param); return(new e.trace_result { target_routes = rotues, contact_people = contactPeople }); } }