public static List<TaskItem> QueryTasks(TaskQueryFilter filter)
{
var result = new List<TaskItem>();
var conStr = ConfigurationManager.ConnectionStrings["ConnBrainExtension"].ConnectionString;
using (SqlConnection conn = new SqlConnection(conStr))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
string queryString = "";
if (filter != null && filter.StartDateMin.HasValue)
{
queryString = queryString + " AND StartDate >= @StartDateMin";
cmd.Parameters.AddWithValue("StartDateMin", filter.StartDateMin.Value);
}
if (filter != null && filter.StartDateMax.HasValue)
{
queryString = queryString + " AND StartDate < @StartDateMax";
cmd.Parameters.AddWithValue("StartDateMax", filter.StartDateMax.Value.AddDays(1));
}
if (filter != null && filter.StatusList.Count > 0)
{
queryString = queryString + " AND [Status] IN (" + string.Join(",", filter.StatusList.Select(d => string.Format("'{0}'", d)).ToArray()) + ")";
}
queryString = string.Format(@"
SELECT [Id]
,[Name]
,[Description]
,[StartDate]
,[EndDate]
,[Status]
,[IsDelete]
,[CreateBy]
,[CreateDate]
,[UpdateBy]
,[UpdateDate]
FROM [dbo].[Task]
WHERE IsDelete = 0
{0}
ORDER BY [StartDate]
", queryString);
cmd.CommandText = queryString;
using (var rd = cmd.ExecuteReader())
{
if (rd != null && rd.HasRows)
{
while (rd.Read())
{
var startDateTime = new DateTime(((DateTime)rd["StartDate"]).Ticks, DateTimeKind.Utc);
var endDateTime = new DateTime(((DateTime)rd["EndDate"]).Ticks, DateTimeKind.Utc);
var minDate = (new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc));
result.Add(new TaskItem()
{
Id=rd["Id"].ToString(),
Name = rd["Name"].ToString(),
Description = rd["Description"].ToString(),
StartDate = startDateTime,
StartTime = minDate + startDateTime.TimeOfDay,
EndDate = endDateTime,
EndTime = minDate + endDateTime.TimeOfDay,
Status = rd["Status"].ToString(),
IsDelete = (bool)rd["IsDelete"],
CreateBy = rd["CreateBy"].ToString(),
CreateDate = (DateTime)rd["CreateDate"],
UpdateBy = rd["UpdateBy"].ToString(),
UpdateDate = (DateTime)rd["UpdateDate"]
});
}
}
}
}
return result;
}
public ActionResult QueryTasks(TaskQueryFilter filter)
{
var taskList = TaskManager.QueryTasks(filter);
return Json(taskList);
}
public static List<TaskItem> QueryTasks(TaskQueryFilter filter)
{
return TaskProvider.QueryTasks(filter);
}
