Exemplo n.º 1
0
        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));
            }
        }
Exemplo n.º 2
0
        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
                });
            }
        }