public static List<RequestInfo> GetRequestInfo(string type, string date, string order = "", bool desc = true, int page = 1, int size = 30)
{
String orderBy = "";
String where = "";
if (!string.IsNullOrWhiteSpace(order))
{
if (order.Equals("Id", StringComparison.OrdinalIgnoreCase))
{
orderBy = " Order by Id " + (desc ? "DESC" : "ASC");
}
else if (order.Equals("CurrentDate", StringComparison.OrdinalIgnoreCase))
{
orderBy = " Order by CurrentDate" + (desc ? "DESC" : "ASC");
}
}
if (!string.IsNullOrWhiteSpace(type))
{
where += "WHERE Type=@Type ";
}
if (!string.IsNullOrWhiteSpace(date))
{
if (!string.IsNullOrWhiteSpace(where))
{
where += " AND CurrentDate=@CurrentDate ";
}
else
{
where += "WHERE CurrentDate=@CurrentDate ";
}
}
List<RequestInfo> requestInfos = new List<RequestInfo>();
string sqlSaveChannel = "SELECT * FROM RequestInfo " + where + orderBy + " LIMIT " + size + " OFFSET " + (page - 1) * size;
using (var sql_con = new SQLiteConnection(ReportConnectString))
{
sql_con.Open();
using (var sql_cmd = sql_con.CreateCommand())
{
sql_cmd.CommandText = sqlSaveChannel;
var myParameters = sql_cmd.Parameters;
if (!string.IsNullOrWhiteSpace(type))
{
myParameters.AddWithValue("@Type", type);
}
if (!string.IsNullOrWhiteSpace(date))
{
myParameters.AddWithValue("@CurrentDate", date);
}
using (var reader = sql_cmd.ExecuteReader())
{
while (reader.Read())
{
var requestInfo = new RequestInfo();
requestInfo.Id = Int32.Parse((reader["Id"] ?? "").ToString());
requestInfo.CurrentDate = (reader["CurrentDate"] ?? "").ToString();
requestInfo.Type = (reader["Type"] ?? "").ToString();
requestInfo.IsFailed = (reader["IsFailed"] ?? "").ToString();
requestInfo.RequestLink = (reader["RequestLink"] ?? "").ToString();
requestInfos.Add(requestInfo);
}
}
}
sql_con.Close();
}
return requestInfos;
}
public static List<RequestInfo> GetRequestInfoStatistic(string type, string fromDate, string toDate)
{
String where = "";
if (!string.IsNullOrWhiteSpace(type))
{
where += "WHERE Type=@Type ";
}
if (!string.IsNullOrWhiteSpace(fromDate))
{
if (!string.IsNullOrWhiteSpace(where))
{
where += " AND CurrentDate>=@FromDate ";
}
else
{
where += "WHERE CurrentDate>=@FromDate ";
}
}
if (!string.IsNullOrWhiteSpace(toDate))
{
if (!string.IsNullOrWhiteSpace(where))
{
where += " AND CurrentDate<=@ToDate ";
}
else
{
where += "WHERE CurrentDate<=@ToDate ";
}
}
List<RequestInfo> requestInfos = new List<RequestInfo>();
string sqlSaveChannel = "SELECT * FROM RequestInfo " + where;
using (var sql_con = new SQLiteConnection(ReportConnectString))
{
sql_con.Open();
using (var sql_cmd = sql_con.CreateCommand())
{
sql_cmd.CommandText = sqlSaveChannel;
var myParameters = sql_cmd.Parameters;
if (!string.IsNullOrWhiteSpace(type))
{
myParameters.AddWithValue("@Type", type);
}
if (!string.IsNullOrWhiteSpace(fromDate))
{
myParameters.AddWithValue("@FromDate", fromDate);
}
if (!string.IsNullOrWhiteSpace(toDate))
{
myParameters.AddWithValue("@ToDate", toDate);
}
using (var reader = sql_cmd.ExecuteReader())
{
while (reader.Read())
{
var requestInfo = new RequestInfo();
requestInfo.Id = Int32.Parse((reader["Id"] ?? "").ToString());
requestInfo.CurrentDate = (reader["CurrentDate"] ?? "").ToString();
requestInfo.Type = (reader["Type"] ?? "").ToString();
requestInfo.IsFailed = (reader["IsFailed"] ?? "").ToString();
requestInfo.RequestLink = (reader["RequestLink"] ?? "").ToString();
requestInfos.Add(requestInfo);
}
}
}
sql_con.Close();
}
return requestInfos;
}