private static void AddFilterCriteria(ITaskFilterOptions filter, SqlBuilder sqlBuilder)
{
if (!string.IsNullOrWhiteSpace(filter.AssignedToId))
{
sqlBuilder.Where("ta.entity_id = @AssignedToId", new { filter.AssignedToId });
}
if (!string.IsNullOrWhiteSpace(filter.CreatedById))
{
sqlBuilder.Where("t.created_by_id = @CreatedById", new { filter.CreatedById });
}
if (!string.IsNullOrWhiteSpace(filter.Status))
{
sqlBuilder.Where("t.status = @Status", new { filter.Status });
}
}
public async Task <ITaskList> GetTasks(ITaskFilterOptions filter)
{
const string QueryTemplate = @"
select
/**select**/
from task t
/**leftjoin**/
/**where**/
/**orderby**/
";
var sqlBuilder = new SqlBuilder();
sqlBuilder.Select(
@"t.public_id, t.title, t.status, t.type, t.resolved, t.due_date, t.created_date, t.created_by,
ta.id assignment_id, ta.entity_id, ta.entity_type,
tc.id context_id, tc.[key], tc.value");
sqlBuilder.LeftJoin("task_context tc on t.id = tc.task_id");
sqlBuilder.LeftJoin("task_assignment ta on t.id = ta.task_id");
sqlBuilder.Where("t.tenant_id = @TenantId", new { serviceContext.TenantId });
sqlBuilder.OrderBy("t.due_date, t.created_date");
AddFilterCriteria(filter, sqlBuilder);
var template = sqlBuilder.AddTemplate(QueryTemplate);
using (var connection = await OpenNewConnection())
{
var taskLookup = new Dictionary <string, PlatformTaskData>();
var reader = await connection.QueryMultipleAsync(template.RawSql, template.Parameters);
reader.Read <PlatformTaskData, AssignmentData, ContextData, PlatformTaskData>(
(task, assignment, context) => MapTaskSearchResults(task, assignment, context, taskLookup),
"assignment_id, context_id");
return(new TaskSearchList {
Tasks = taskLookup.Values.Cast <ITask>().ToList()
});
}
}
public async Task<TaskSearchResult> Search(ITaskFilterOptions options)
{
var tasks = await repo.GetTasks(options);
return new TaskSearchResult();
}
public async Task <ITaskList> Search(ITaskFilterOptions options)
{
return(await repo.GetTasks(options));
}