public async Task <List <RequestStat> > GetStatsByDate(DateTime from, DateTime to)
{
List <RequestStat> stats = new List <RequestStat>();
string query = @"SELECT COALESCE(q.id_request_type, 'ADDPOST') as Type, dic.code as Status, COALESCE(q.count, 0) as Count, q.avg as Avg
FROM he.dic_request_status dic
LEFT JOIN (
SELECT rq.id_status, rq.id_request_type, count(rq.id_status), avg(process_end_date - process_start_date)
FROM he.requests_queue rq
WHERE rq.id_request_type = 'ADDPOST' AND rq.add_date >= @from AND rq.add_date <= @to
GROUP BY rq.id_status, rq.id_request_type) q ON q.id_status = dic.code
UNION
SELECT COALESCE(q.id_request_type, 'GETINFO'), dic.code, COALESCE(q.count, 0), q.avg
FROM he.dic_request_status dic
LEFT JOIN (
SELECT rq.id_status, rq.id_request_type, count(rq.id_status), avg(process_end_date - process_start_date)
FROM he.requests_queue rq
WHERE rq.id_request_type = 'GETINFO' AND rq.add_date >= @from AND rq.add_date <= @to
GROUP BY rq.id_status, rq.id_request_type) q ON q.id_status = dic.code
ORDER BY 2, 1";
using (DbConnection conn = _appDbContext.Database.GetDbConnection())
{
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
cmd.Parameters.Add(new NpgsqlParameter("from", from));
cmd.Parameters.Add(new NpgsqlParameter("to", to));
conn.Open();
DbDataReader dr = await cmd.ExecuteReaderAsync();
while (await dr.ReadAsync())
{
RequestStat rs = new RequestStat();
rs.Type = dr.GetString(0);
rs.Status = dr.GetString(1);
rs.Count = dr.GetInt64(2);
rs.Avg = dr[3] != DBNull.Value ? (TimeSpan?)dr[3] : null;
stats.Add(rs);
}
}
}
return(stats);
}
public TrackRequest()
{
captured = DateTime.Now;
status = RequestStat.OnOurWarehouse;
}
