public List<TaskTracker> GetTaskAndSubTask()
{
List<TaskTracker> _lst = new List<TaskTracker>();
using (var db = new SqlHelper(ConfigurationManager.ConnectionStrings["collect2000ConnectionString"].ConnectionString))
{
var q = @"SELECT Task_ID, Task_Title, InternalRadioOption, DayInterval, MonthInterval,WeekNumber,WeekDayName,MonthName, Task_Description, TaskAssignedTo, Task_DueDate, CreatedDate, AppointmentStartTime, AppointmentEndTime, RecurrencePatternType, RecurrencePatternInterval, RecurrencePatternFallOn, RecurrenceRangeStartDate, RecurrenceRangeEndByDate, RecurrenceRangeType, RecurrenceRangeOccurrence, TaskAssignedBy FROM dbo.ERC_Tasks ORDER BY Task_ID DESC;";
using (var dr = db.Query(q))
{
while (dr.Read())
{
TaskTracker info = new TaskTracker();
info.Task_ID = Convert.ToInt32(dr[0].ToString());
info.Title = Convert.ToString(dr["Task_Title"]);
info.Description = Convert.ToString(dr["Task_Description"]);
info.TaskDueDate = Convert.ToString(dr["Task_DueDate"]);
info.CreatedDate = Convert.ToString(dr["CreatedDate"]);
info.AppointmentStartTime = dr["AppointmentStartTime"] as string ?? "";
info.AppointmentEndTime = dr["AppointmentEndTime"] as string ?? "";
info.RecurrencePatternType = dr["RecurrencePatternType"] as string ?? "";
info.RecurrencePatternInterval = dr["RecurrencePatternInterval"] as int? ?? 0;
info.RecurrencePatternFallOn = dr["RecurrencePatternFallOn"] as string ?? "";
info.RecurrenceRangeStartDate = dr["RecurrenceRangeStartDate"] as string ?? "";
info.RecurrenceRangeEndByDate = dr["RecurrenceRangeEndByDate"] as string ?? "";
info.RecurrenceRangeType = dr["RecurrenceRangeType"] as string ?? "";
info.RecurrenceRangeOccurrence = dr["RecurrenceRangeOccurrence"] as int? ?? 0;
info.TaskAssignedBy = Convert.ToString(dr["TaskAssignedBy"]) as string ?? "";
info.TaskAssignedTo = Convert.ToString(dr["TaskAssignedTo"]) as string ?? "";
info.InternalRadioOption = Convert.ToString(dr["InternalRadioOption"]) as string ?? "";
info.DayInterval = Convert.ToString(dr["DayInterval"]) as string ?? "";
info.MonthInterval = Convert.ToString(dr["MonthInterval"]) as string ?? "";
info.WeekNumber = Convert.ToString(dr["WeekNumber"]) as string ?? "";
info.WeekDayName = Convert.ToString(dr["WeekDayName"]) as string ?? "";
info.MonthName = Convert.ToString(dr["MonthName"]) as string ?? "";
info.SubTask = GetSubTaskList(Convert.ToInt32(dr[0].ToString()));
_lst.Add(info);
}
}
}
if (_lst == null)
{
throw new Exception("No match data found ");
}
return _lst;
}
public bool AddAssignment(Assignment _obj)
{
try
{
using (var db = new SqlHelper(ConfigurationManager.ConnectionStrings["collect2000ConnectionString"].ConnectionString))
{
var q = @"EXEC USP_AddAssignment @Task_ID, @TaskAssignedToUser, @TaskComment, @AssignedByUsername";
using (var dr = db.Query(q, new SqlParameter("@Task_ID", _obj.TaskId), new SqlParameter("@TaskAssignedToUser", _obj.AssignedTo), new SqlParameter("@TaskComment", _obj.Comment), new SqlParameter("@AssignedByUsername", _obj.AssignedBy)))
{
}
}
}
catch (Exception ex) { }
return true;
}
public bool AddSubTaskAssignment(Assignment _obj)
{
try
{
using (var db = new SqlHelper(ConfigurationManager.ConnectionStrings["collect2000ConnectionString"].ConnectionString))
{
var q = @"INSERT INTO ERC_TaskAssignment(SubTask_ID, AssignedToUsername, Task_Comment ) VALUES(@Task_ID, @TaskAssignedToUser, @TaskComment)";
using (var dr = db.Query(q, new SqlParameter("@Task_ID", _obj.TaskId), new SqlParameter("@TaskAssignedToUser", _obj.AssignedTo), new SqlParameter("@TaskComment", _obj.Comment)))
{
}
}
}
catch (Exception ex) { }
return true;
}
public bool DeleteSubTask(int ID)
{
using (var db = new SqlHelper(ConfigurationManager.ConnectionStrings["collect2000ConnectionString"].ConnectionString))
{
var q = @"DELETE FROM ERC_SubTasks WHERE SubTask_ID = @ID;";
using (var dr = db.Query(q, new SqlParameter("@ID", ID)))
{
if (dr.Read())
{
}
}
}
return true;
}
public bool AddReccurrence(int Task_ID, string InternalRadioOption, string DayInterval, string MonthInterval, string WeekNumber, string WeekDayName, string MonthName, string AppointmentStartTime, string AppointmentEndTime, string RecurrencePatternType, int RecurrencePatternInterval, string RecurrencePatternFallOn, string RecurrenceRangeStartDate, string RecurrenceRangeEndByDate, string RecurrenceRangeType, int RecurrenceRangeOccurrence)
{
try
{
using (var db = new SqlHelper(ConfigurationManager.ConnectionStrings["collect2000ConnectionString"].ConnectionString))
{
var q = @"UPDATE ERC_Tasks SET InternalRadioOption = @InternalRadioOption, DayInterval = @DayInterval, MonthInterval = @MonthInterval,WeekNumber = @WeekNumber,WeekDayName = @WeekDayName,MonthName = @MonthName, AppointmentStartTime = @AppointmentStartTime, AppointmentEndTime = @AppointmentEndTime, RecurrencePatternType = @RecurrencePatternType, RecurrencePatternInterval = @RecurrencePatternInterval, RecurrencePatternFallOn = @RecurrencePatternFallOn, RecurrenceRangeStartDate = @RecurrenceRangeStartDate, RecurrenceRangeEndByDate = @RecurrenceRangeEndByDate, RecurrenceRangeType = @RecurrenceRangeType, RecurrenceRangeOccurrence = @RecurrenceRangeOccurrence WHERE Task_ID = @Task_ID";
using (var dr = db.Query(q, new SqlParameter("@InternalRadioOption", InternalRadioOption), new SqlParameter("@DayInterval", DayInterval), new SqlParameter("@MonthInterval", MonthInterval), new SqlParameter("@WeekNumber", WeekNumber), new SqlParameter("@WeekDayName", WeekDayName), new SqlParameter("@MonthName", MonthName), new SqlParameter("@AppointmentStartTime", AppointmentStartTime), new SqlParameter("@AppointmentEndTime", AppointmentEndTime), new SqlParameter("@RecurrencePatternType", RecurrencePatternType), new SqlParameter("@RecurrencePatternInterval", RecurrencePatternInterval), new SqlParameter("@RecurrencePatternFallOn", RecurrencePatternFallOn), new SqlParameter("@RecurrenceRangeStartDate", RecurrenceRangeStartDate), new SqlParameter("@RecurrenceRangeEndByDate", RecurrenceRangeEndByDate), new SqlParameter("@RecurrenceRangeType", RecurrenceRangeType), new SqlParameter("@RecurrenceRangeOccurrence", RecurrenceRangeOccurrence), new SqlParameter("@Task_ID", Task_ID)))
{
}
}
}
catch (Exception ex) { return false; }
return true;
}
public bool DeleteTask(int ID)
{
using (var db = new SqlHelper(ConfigurationManager.ConnectionStrings["collect2000ConnectionString"].ConnectionString))
{
var q = @"EXEC USP_DeleteTask @ID;";
using (var dr = db.Query(q, new SqlParameter("@ID", ID)))
{
if (dr.Read())
{
}
}
}
return true;
}
public TaskTracker GetSubTaskByID(int ID)
{
TaskTracker info = new TaskTracker();
using (var db = new SqlHelper(ConfigurationManager.ConnectionStrings["collect2000ConnectionString"].ConnectionString))
{
var q = @"SELECT SubTask_ID, Task_ID, Task_Title, Task_Description, Task_DueDate, CreatedDate, StartDate, Task_Status FROM dbo.ERC_SubTasks WHERE SubTask_ID = @ID;";
using (var dr = db.Query(q, new SqlParameter("@ID", ID)))
{
if (dr.Read())
{
info.SubTask_ID = Convert.ToInt32(dr[0].ToString());
info.Task_ID = Convert.ToInt32(dr["Task_ID"].ToString());
info.Title = Convert.ToString(dr["Task_Title"]);
info.Description = Convert.ToString(dr["Task_Description"]);
info.TaskDueDate = Convert.ToString(dr["Task_DueDate"]);
info.CreatedDate = Convert.ToString(dr["CreatedDate"]);
info.StartDate = Convert.ToString(dr["StartDate"]);
info.Task_Status = Convert.ToString(dr["Task_Status"]);
}
}
}
if (info == null)
{
throw new Exception("No match data found ");
}
return info;
}
public bool AddSubTask(string Task_Title, string Task_Description, DateTime Task_DueDate, string Task_Status, int Task_ID, DateTime StartDate)
{
try
{
using (var db = new SqlHelper(ConfigurationManager.ConnectionStrings["collect2000ConnectionString"].ConnectionString))
{
var q = @"INSERT INTO ERC_SubTasks(Task_Title, Task_Description, Task_DueDate, StartDate, Task_Status, Task_ID) VALUES(@Task_Title, @Task_Description, @Task_DueDate,@StartDate, @Task_Status, @Task_ID)";
using (var dr = db.Query(q, new SqlParameter("@Task_Title", Task_Title), new SqlParameter("@Task_Description", Task_Description),
new SqlParameter("@Task_DueDate", Task_DueDate), new SqlParameter("@StartDate", StartDate), new SqlParameter("@Task_Status", Task_Status), new SqlParameter("@Task_ID", Task_ID)))
{
}
}
}
catch (Exception ex) { return false; }
return true;
}
public bool UpdateSubTask(int Task_ID, string Task_Title, string Task_Description, DateTime Task_DueDate, string Task_Status, DateTime StartDate)
{
try
{
using (var db = new SqlHelper(ConfigurationManager.ConnectionStrings["collect2000ConnectionString"].ConnectionString))
{
var q = @"UPDATE ERC_SubTasks SET Task_Title = @Task_Title, Task_Description = @Task_Description, Task_DueDate = @Task_DueDate, StartDate = @StartDate, Task_Status = @Task_Status WHERE SubTask_ID = @Task_ID";
using (var dr = db.Query(q, new SqlParameter("@Task_Title", Task_Title), new SqlParameter("@Task_Description", Task_Description), new SqlParameter("@Task_DueDate", Task_DueDate), new SqlParameter("@StartDate", StartDate), new SqlParameter("@Task_Status", Task_Status), new SqlParameter("@Task_ID", Task_ID)))
{
}
}
}
catch (Exception ex) { return false; }
return true;
}
private List<TaskTracker> GetSubTaskList(int taskId)
{
List<TaskTracker> _lst = new List<TaskTracker>();
using (var db = new SqlHelper(ConfigurationManager.ConnectionStrings["collect2000ConnectionString"].ConnectionString))
{
var q = @" EXEC USP_GetAllSubTaskByID @ID ";
using (var dr = db.Query(q, new SqlParameter("@ID", taskId)))
{
while (dr.Read())
{
TaskTracker info = new TaskTracker();
user usreinfo = new user();
usreinfo.UserName = Convert.ToString(dr["AssignedTo"]);
info.Task_ID = Convert.ToInt32(dr["Task_ID"].ToString());
info.SubTask_ID = Convert.ToInt32(dr["SubTask_ID"].ToString());
info.Title = Convert.ToString(dr["Task_Title"]);
info.Description = Convert.ToString(dr["Task_Description"]);
info.TaskDueDate = Convert.ToString(dr["TaskDueDate"]);
info.CreatedDate = Convert.ToString(dr["CreatedDate"]);
info.StartDate = Convert.ToString(dr["StartDate"]);
info.Task_Status = Convert.ToString(dr["Task_Status"]);
info.AssignedTo = usreinfo;
_lst.Add(info);
}
}
}
if (_lst == null)
{
throw new Exception("No match data found ");
}
return _lst;
}
