public static Common.Models.Tasks.Task Create(
Common.Models.Tasks.Task model,
Common.Models.Account.Users creator,
IDbConnection conn = null,
bool closeConnection = true)
{
model.CreatedBy = model.ModifiedBy = creator;
model.Created = model.Modified = DateTime.UtcNow;
DBOs.Tasks.Task dbo = Mapper.Map <DBOs.Tasks.Task>(model);
conn = DataHelper.OpenIfNeeded(conn);
if (conn.Execute("INSERT INTO \"task\" (\"title\", \"description\", \"projected_start\", \"due_date\", \"projected_end\", " +
"\"actual_end\", \"parent_id\", \"is_grouping_task\", \"sequential_predecessor_id\", \"active\", \"utc_created\", " +
"\"utc_modified\", \"created_by_user_pid\", \"modified_by_user_pid\") " +
"VALUES (@Title, @Description, @ProjectedStart, @DueDate, @ProjectedEnd, @ActualEnd, @ParentId, @IsGroupingTask, " +
"@SequentialPredecessorId, @Active, @UtcCreated, @UtcModified, @CreatedByUserPId, @ModifiedByUserPId)",
dbo) > 0)
{
model.Id = conn.Query <DBOs.Tasks.Task>("SELECT currval(pg_get_serial_sequence('task', 'id')) AS \"id\"").Single().Id;
}
DataHelper.Close(conn, closeConnection);
return(model);
}
public static List<Common.Models.Tasks.Task> ListChildren(
long? parentId,
List<Tuple<string, string>> filter = null,
IDbConnection conn = null,
bool closeConnection = true)
{
List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>();
IEnumerable<DBOs.Tasks.Task> ie = null;
//filter = new List<Tuple<string, string>>();
//filter.Add(new Tuple<string, string>("status", "pending"));
if (filter != null)
{
string filterStr = null;
List<string> cats = new List<string>();
List<string> tags = new List<string>();
List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>();
filter.ForEach(x =>
{
if (!string.IsNullOrWhiteSpace(x.Item1))
cats.Add(x.Item1.ToLower());
if (!string.IsNullOrWhiteSpace(x.Item2))
tags.Add(x.Item2.ToLower());
});
filterStr = "SELECT * FROM \"task\" WHERE \"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " +
"IN (SELECT \"id\" FROM \"tag_category\" WHERE LOWER(\"name\") IN (";
cats.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
filterStr += ":" + parmName + ",";
});
filterStr = filterStr.TrimEnd(',');
filterStr += ")) AND LOWER(\"tag\") IN (";
tags.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
filterStr += ":" + parmName + ",";
});
filterStr = filterStr.TrimEnd(',');
filterStr += ")) AND \"parent_id\"";
if (parentId.HasValue && parentId.Value > 0)
{
filterStr += "=:parentid ";
parms.Add(new Npgsql.NpgsqlParameter("parentid", DbType.Int64) { Value = parentId.Value.ToString() });
}
else
filterStr += " is null ";
filterStr += "AND \"utc_disabled\" is null";
conn = DataHelper.OpenIfNeeded(conn);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(filterStr, (Npgsql.NpgsqlConnection)conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue<long>("id", reader);
dbo.Title = Database.GetDbColumnValue<string>("title", reader);
dbo.Description = Database.GetDbColumnValue<string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
}
}
}
else
{
conn = DataHelper.OpenIfNeeded(conn);
if (parentId.HasValue)
ie = conn.Query<DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\"=@ParentId AND \"utc_disabled\" is null",
new { ParentId = parentId.Value });
else
ie = conn.Query<DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\" is null AND \"utc_disabled\" is null");
foreach (DBOs.Tasks.Task dbo in ie)
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
DataHelper.Close(conn, closeConnection);
return list;
}
public static List<Common.Models.Tasks.Task> GetTodoListForAll(
DateTime? start = null,
DateTime? stop = null,
IDbConnection conn = null,
bool closeConnection = true)
{
string sql;
List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>();
List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>();
sql = "SELECT * FROM \"task\" WHERE \"active\"=true AND \"utc_disabled\" is null ";
if (start.HasValue)
{
parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime) { Value = start.Value });
if (stop.HasValue)
{
sql += "AND \"due_date\" BETWEEN @Start AND @Stop ";
parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime) { Value = stop.Value });
}
else
{
sql += "AND \"due_date\">=@Start ";
}
}
sql += "ORDER BY \"due_date\" ASC";
conn = DataHelper.OpenIfNeeded(conn);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, (Npgsql.NpgsqlConnection)conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue<long>("id", reader);
dbo.Title = Database.GetDbColumnValue<string>("title", reader);
dbo.Description = Database.GetDbColumnValue<string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
}
}
DataHelper.Close(conn, closeConnection);
return list;
}
private static void UpdateGroupingTaskProperties(Common.Models.Tasks.Task groupingTask)
{
bool groupingTaskChanged = false;
using (IDbConnection conn = Database.Instance.GetConnection())
{
// Projected Start
DBOs.Tasks.Task temp = conn.Query <DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\"=@ParentId AND \"utc_disabled\" is null ORDER BY \"projected_start\" DESC limit 1",
new { ParentId = groupingTask.Id.Value }).SingleOrDefault();
// If temp.ProjectedStart has a value then we know that there are no rows
// with null value and so, we may update the grouping task to be the
// earliest projected start value. However, if null, then we need to
// set the grouping task's projected start value to null.
if (temp.ProjectedStart.HasValue)
{
temp = conn.Query <DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\"=@ParentId AND \"utc_disabled\" is null ORDER BY \"projected_start\" ASC limit 1",
new { ParentId = groupingTask.Id.Value }).SingleOrDefault();
if (groupingTask.ProjectedStart != temp.ProjectedStart)
{
groupingTask.ProjectedStart = temp.ProjectedStart;
groupingTaskChanged = true;
}
}
else
{
if (groupingTask.ProjectedStart.HasValue)
{
groupingTask.ProjectedStart = null;
groupingTaskChanged = true;
}
}
// Due Date
temp = conn.Query <DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\"=@ParentId AND \"utc_disabled\" is null ORDER BY \"due_date\" DESC limit 1",
new { ParentId = groupingTask.Id.Value }).SingleOrDefault();
if (temp.DueDate != groupingTask.DueDate)
{
groupingTask.DueDate = temp.DueDate;
groupingTaskChanged = true;
}
// Projected End
temp = conn.Query <DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\"=@ParentId AND \"utc_disabled\" is null ORDER BY \"projected_end\" DESC limit 1",
new { ParentId = groupingTask.Id.Value }).SingleOrDefault();
if (temp.ProjectedEnd != groupingTask.ProjectedEnd)
{
groupingTask.ProjectedEnd = temp.ProjectedEnd;
groupingTaskChanged = true;
}
// Actual End
temp = conn.Query <DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\"=@ParentId AND \"utc_disabled\" is null ORDER BY \"actual_end\" DESC limit 1",
new { ParentId = groupingTask.Id.Value }).SingleOrDefault();
if (temp.ActualEnd != groupingTask.ActualEnd)
{
groupingTask.ActualEnd = temp.ActualEnd;
groupingTaskChanged = true;
}
// Update grouping task if needed
if (groupingTaskChanged)
{
DBOs.Tasks.Task task = Mapper.Map <DBOs.Tasks.Task>(groupingTask);
conn.Execute("UPDATE \"task\" SET \"projected_start\"=@ProjectedStart, \"due_date\"=@DueDate, \"projected_end\"=@ProjectedEnd, " +
"\"actual_end\"=@ActualEnd WHERE \"id\"=@Id",
task);
if (groupingTask.Parent != null && groupingTask.Parent.Id.HasValue)
{
UpdateGroupingTaskProperties(OpenLawOffice.Data.Tasks.Task.Get(groupingTask.Parent.Id.Value));
}
}
}
}
public static Common.Models.Tasks.Task Edit(Common.Models.Tasks.Task model,
Common.Models.Account.Users modifier)
{
/*
* We need to consider how to handle the relationship modifications
*
* First, basic assumptions:
* 1) If a task has a sequential predecessor then it cannot independently specify its parent
*
*
* Parent - if the parent is modified
*
* Sequential Predecessor
* 1) If changed, need to cascade changes to all subsequent sequence members
* 2) If removed from sequence, need to defer to user's parent selection
* 3) If added to sequence, need to override user's parent selection
*
*
* UI should be like:
*
* If sequence member:
* [Remove from Sequence]
*
*
* If NOT sequence member:
* This task is not currently part of a task sequence. If you would like to make this
* task part of a task sequence, click here.
* -- OnClick -->
* Please select the task you wish t
*
*/
if (model.Parent != null && model.Parent.Id.HasValue)
{
// There is a proposed parent, we need to check and make sure it is not trying
// to set itself as its parent
if (model.Parent.Id.Value == model.Id)
{
throw new ArgumentException("Task cannot have itself as its parent.");
}
}
model.ModifiedBy = modifier;
model.Modified = DateTime.UtcNow;
DBOs.Tasks.Task dbo = Mapper.Map <DBOs.Tasks.Task>(model);
using (IDbConnection conn = Database.Instance.GetConnection())
{
conn.Execute("UPDATE \"task\" SET " +
"\"title\"=@Title, \"description\"=@Description, \"projected_start\"=@ProjectedStart, " +
"\"due_date\"=@DueDate, \"projected_end\"=@ProjectedEnd, \"actual_end\"=@ActualEnd, \"parent_id\"=@ParentId, " +
"\"active\"=@Active, \"utc_modified\"=@UtcModified, \"modified_by_user_pid\"=@ModifiedByUserPId " +
"WHERE \"id\"=@Id", dbo);
}
if (model.Parent != null && model.Parent.Id.HasValue)
{
UpdateGroupingTaskProperties(OpenLawOffice.Data.Tasks.Task.Get(model.Parent.Id.Value));
}
return(model);
}
public static List <Common.Models.Tasks.Task> ListChildren(long?parentId, List <Tuple <string, string> > filter = null)
{
List <Common.Models.Tasks.Task> list = new List <Common.Models.Tasks.Task>();
IEnumerable <DBOs.Tasks.Task> ie = null;
//filter = new List<Tuple<string, string>>();
//filter.Add(new Tuple<string, string>("status", "pending"));
if (filter != null)
{
string filterStr = null;
List <string> cats = new List <string>();
List <string> tags = new List <string>();
List <Npgsql.NpgsqlParameter> parms = new List <Npgsql.NpgsqlParameter>();
filter.ForEach(x =>
{
if (!string.IsNullOrWhiteSpace(x.Item1))
{
cats.Add(x.Item1.ToLower());
}
if (!string.IsNullOrWhiteSpace(x.Item2))
{
tags.Add(x.Item2.ToLower());
}
});
filterStr = "SELECT * FROM \"task\" WHERE \"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " +
"IN (SELECT \"id\" FROM \"tag_category\" WHERE LOWER(\"name\") IN (";
cats.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text)
{
Value = x
});
filterStr += ":" + parmName + ",";
});
filterStr = filterStr.TrimEnd(',');
filterStr += ")) AND LOWER(\"tag\") IN (";
tags.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text)
{
Value = x
});
filterStr += ":" + parmName + ",";
});
filterStr = filterStr.TrimEnd(',');
filterStr += ")) AND \"parent_id\"";
if (parentId.HasValue && parentId.Value > 0)
{
filterStr += "=:parentid ";
parms.Add(new Npgsql.NpgsqlParameter("parentid", DbType.Int64)
{
Value = parentId.Value.ToString()
});
}
else
{
filterStr += " is null ";
}
filterStr += "AND \"utc_disabled\" is null";
using (Npgsql.NpgsqlConnection conn = (Npgsql.NpgsqlConnection)Database.Instance.GetConnection())
{
conn.Open();
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(filterStr, conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue <long>("id", reader);
dbo.Title = Database.GetDbColumnValue <string>("title", reader);
dbo.Description = Database.GetDbColumnValue <string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue <DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue <DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue <DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue <DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue <long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue <bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue <long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map <Common.Models.Tasks.Task>(dbo));
}
}
}
}
}
else
{
using (IDbConnection conn = Database.Instance.GetConnection())
{
if (parentId.HasValue)
{
ie = conn.Query <DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\"=@ParentId AND \"utc_disabled\" is null",
new { ParentId = parentId.Value });
}
else
{
ie = conn.Query <DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\" is null AND \"utc_disabled\" is null");
}
}
foreach (DBOs.Tasks.Task dbo in ie)
{
list.Add(Mapper.Map <Common.Models.Tasks.Task>(dbo));
}
}
return(list);
}
public static List <Common.Models.Tasks.Task> GetTodoListForAll(List <Common.Models.Settings.TagFilter> tagFilter, DateTime?start = null, DateTime?stop = null)
{
string sql;
List <string> cats = new List <string>();
List <string> tags = new List <string>();
List <Npgsql.NpgsqlParameter> parms = new List <Npgsql.NpgsqlParameter>();
List <Common.Models.Tasks.Task> list = new List <Common.Models.Tasks.Task>();
tagFilter.ForEach(x =>
{
if (!string.IsNullOrWhiteSpace(x.Category))
{
cats.Add(x.Category.ToLower());
}
if (!string.IsNullOrWhiteSpace(x.Tag))
{
tags.Add(x.Tag.ToLower());
}
});
sql = "SELECT * FROM \"task\" WHERE \"active\"=true AND " +
"\"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " +
"IN (SELECT \"id\" FROM \"tag_category\" WHERE LOWER(\"name\") IN (";
cats.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text)
{
Value = x
});
sql += ":" + parmName + ",";
});
sql = sql.TrimEnd(',');
sql += ")) AND LOWER(\"tag\") IN (";
tags.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text)
{
Value = x
});
sql += ":" + parmName + ",";
});
sql = sql.TrimEnd(',');
sql += ")) AND \"utc_disabled\" is null ";
if (start.HasValue)
{
parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime)
{
Value = start.Value
});
if (stop.HasValue)
{
sql += "AND \"due_date\" BETWEEN @Start AND @Stop ";
parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime)
{
Value = stop.Value
});
}
else
{
sql += "AND \"due_date\">=@Start ";
}
}
sql += "ORDER BY \"due_date\" ASC";
using (Npgsql.NpgsqlConnection conn = (Npgsql.NpgsqlConnection)Database.Instance.GetConnection())
{
conn.Open();
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue <long>("id", reader);
dbo.Title = Database.GetDbColumnValue <string>("title", reader);
dbo.Description = Database.GetDbColumnValue <string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue <DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue <DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue <DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue <DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue <long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue <bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue <long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map <Common.Models.Tasks.Task>(dbo));
}
}
}
}
return(list);
}
public static List<Common.Models.Tasks.Task> GetTodoListForAll(List<Common.Models.Settings.TagFilter> tagFilter, DateTime? start = null, DateTime? stop = null)
{
string sql;
List<string> cats = new List<string>();
List<string> tags = new List<string>();
List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>();
List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>();
tagFilter.ForEach(x =>
{
if (!string.IsNullOrWhiteSpace(x.Category))
cats.Add(x.Category.ToLower());
if (!string.IsNullOrWhiteSpace(x.Tag))
tags.Add(x.Tag.ToLower());
});
sql = "SELECT * FROM \"task\" WHERE \"active\"=true AND " +
"\"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " +
"IN (SELECT \"id\" FROM \"tag_category\" WHERE LOWER(\"name\") IN (";
cats.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
sql += ":" + parmName + ",";
});
sql = sql.TrimEnd(',');
sql += ")) AND LOWER(\"tag\") IN (";
tags.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
sql += ":" + parmName + ",";
});
sql = sql.TrimEnd(',');
sql += ")) AND \"utc_disabled\" is null ";
if (start.HasValue)
{
parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime) { Value = start.Value });
if (stop.HasValue)
{
sql += "AND \"due_date\" BETWEEN @Start AND @Stop ";
parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime) { Value = stop.Value });
}
else
{
sql += "AND \"due_date\">=@Start ";
}
}
sql += "ORDER BY \"due_date\" ASC";
using (Npgsql.NpgsqlConnection conn = (Npgsql.NpgsqlConnection)Database.Instance.GetConnection())
{
conn.Open();
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue<long>("id", reader);
dbo.Title = Database.GetDbColumnValue<string>("title", reader);
dbo.Description = Database.GetDbColumnValue<string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
}
}
}
return list;
}
public static List <Common.Models.Tasks.Task> GetTodoListForAll(
DateTime?start = null,
DateTime?stop = null,
IDbConnection conn = null,
bool closeConnection = true)
{
string sql;
List <Npgsql.NpgsqlParameter> parms = new List <Npgsql.NpgsqlParameter>();
List <Common.Models.Tasks.Task> list = new List <Common.Models.Tasks.Task>();
sql = "SELECT * FROM \"task\" WHERE \"active\"=true AND \"utc_disabled\" is null ";
if (start.HasValue)
{
parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime)
{
Value = start.Value
});
if (stop.HasValue)
{
sql += "AND \"due_date\" BETWEEN @Start AND @Stop ";
parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime)
{
Value = stop.Value
});
}
else
{
sql += "AND \"due_date\">=@Start ";
}
}
sql += "ORDER BY \"due_date\" ASC";
conn = DataHelper.OpenIfNeeded(conn);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, (Npgsql.NpgsqlConnection)conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue <long>("id", reader);
dbo.Title = Database.GetDbColumnValue <string>("title", reader);
dbo.Description = Database.GetDbColumnValue <string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue <DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue <DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue <DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue <DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue <long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue <bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue <long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map <Common.Models.Tasks.Task>(dbo));
}
}
}
DataHelper.Close(conn, closeConnection);
return(list);
}
