public WorkingTimeForTimelineDto[] SelectByYmd(string ymd)
{
#region SQL
const string sql = @"
SELECT
time.id as WorkingTimeId
, time.taskid as WorkTaskId
, time.starttime as starttime
, time.endtime as endtime
, task.title as title
, task.taskcategory as taskcategory
, processes.title as workprocessname
FROM
workingtimes time
INNER JOIN
worktasks task ON task.id = time.taskid
INNER JOIN
processes ON processes.id = task.processid
WHERE
time.ymd = @ymd
";
#endregion
var list = new List <WorkingTimeForTimelineDto>();
RepositoryAction.Query(c =>
{
var listRow = c.Query <WorkingTimeForTimelineTableRow>(sql, new { ymd });
list.AddRange(listRow.Select(r => r.ConvertToDto()));
});
return(list.OrderBy(i => i.TimePeriod.StartDateTime).ToArray());
}
public WorkProcess[] SelectAll()
{
var list = new List <WorkProcess>();
RepositoryAction.Query(c =>
{
var rows = new WorkProcessDao(c, null).SelectAll();
list.AddRange(rows.Select(r => r.ToDomainObject()));
});
return(list.ToArray());
}
public Product[] SelectAll()
{
var list = new List <Product>();
RepositoryAction.Query(c =>
{
var rows = new ProductDao(c, null).SelectAll();
list.AddRange(rows.Select(r => r.ToDomainObject()));
});
return(list.ToArray());
}
public WorkTaskWithTimesDto[] SelectByYmd(YmdString ymd, bool containsCompleted)
{
var list = new List <WorkTaskWithTimesDto>();
RepositoryAction.Query(c =>
{
var workTaskDao = new WorkTaskDao(c, null);
var workingTimeDao = new WorkingTimeDao(c, null);
var processes = new WorkProcessDao(c, null).SelectAll();
var products = new ProductDao(c, null).SelectAll();
var clients = new ClientDao(c, null).SelectAll();
var completedDao = new WorkTaskCompletedDao(c, null);
var tasks = workTaskDao.SelectPlaned(ymd, containsCompleted);
var times = workingTimeDao.SelectByTaskIds(tasks.Select(t => t.Id).Distinct().ToArray());
var completed = completedDao.SelectCompleted(tasks.Select(t => t.Id).Distinct().ToArray());
foreach (var task in tasks)
{
var dto = new WorkTaskWithTimesDto
{
TaskId = new Identity <Domain.Domain.Tasks.WorkTask>(task.Id),
ClientName = clients.FirstOrDefault(c => c.Id == task.ClientId)?.Name ?? "",
ProcessName = processes.FirstOrDefault(p => p.Id == task.ProcessId)?.Title ?? "",
ProductName = products.FirstOrDefault(p => p.Id == task.ProductId)?.Name ?? "",
TaskCategory = task.TaskCategory,
Title = task.Title,
IsCompleted = completed.Any(c => c == task.Id),
IsScheduled = task.TaskSource == Domain.Domain.Tasks.TaskSource.Schedule,
};
dto.WorkingTimes = times.Where(t => t.TaskId == task.Id)
.Select(t => t.ToDomainObject())
.OrderBy(t => t.TimePeriod.StartDateTime)
.ThenBy(t => t.Id)
.ToArray();
list.Add(dto);
}
});
try
{
return(list.OrderBy(i => i.WorkingTimes.Any(t => t.IsDoing) ? 0 : 1)
.ThenByDescending(i => i.WorkingTimes.Where(t => t.TimePeriod.IsFuture == false).Any() ? i.WorkingTimes.Where(t => t.TimePeriod.IsFuture == false).Max(t => t.TimePeriod.StartDateTime) : DateTime.MinValue)
.ThenBy(i => i.WorkingTimes.Where(t => t.TimePeriod.IsFuture).Any() ? i.WorkingTimes.Where(t => t.TimePeriod.IsFuture).Min(t => t.TimePeriod.StartDateTime) : DateTime.MaxValue)
.ThenBy(i => i.TaskId.Value).ToArray());
}
catch (Exception)
{
return(list.ToArray());
}
}
public ImportedTask[] SelectByImportKeys(string[] importKeys)
{
ImportedTask[] results = null;
RepositoryAction.Query(c =>
{
var dao = new ImportedTaskDao(c, null);
results = dao.SelectByImportKeys(importKeys).Select(d => d.ConvertToDomainObject()).ToArray();
});
return(results);
}
public WorkingTimeRange SelectById(Identity <WorkingTimeRange> id)
{
WorkingTimeRange result = null;
RepositoryAction.Query(c =>
{
var dao = new WorkingTimeDao(c, null);
result = dao.SelectId(id.Value)?.ToDomainObject();
});
return(result);
}
public ConfigurationItem[] SelectAll()
{
ConfigurationItem[] results = null;
RepositoryAction.Query(c =>
{
var dao = new ConfigDao(c, null);
results = dao.SelectAll().Select(i => i.ConvertToDomainObject()).ToArray();
});
return(results);
}
public WorkTask SelectById(Identity <WorkTask> identity)
{
WorkTask results = null;
RepositoryAction.Query(c =>
{
var dao = new WorkTaskDao(c, null);
results = dao.SelectById(identity.Value)?.ConvertToDomainObject();
});
return(results);
}
public Client[] SelectAll()
{
var list = new List <Client>();
RepositoryAction.Query(c =>
{
var listRow = new ClientDao(c, null).SelectAll();
list.AddRange(listRow.Select(r => r.ToDomainObject()));
});
return(list.OrderBy(i => i.KanaName)
.ThenBy(i => i.Id.Value)
.ToArray());
}
public WorkTask SelectById(Identity <WorkTask> identity)
{
WorkTask results = null;
RepositoryAction.Query(c =>
{
var dao = new WorkTaskDao(c, null);
var compDao = new WorkTaskCompletedDao(c, null);
var exist = compDao.IsCompleted(identity.Value);
results = WorkTaskFactory.Create(dao.SelectById(identity.Value), exist);
});
return(results);
}
public WorkingTimeRange[] SelectByTaskId(Identity <WorkTask> taskId)
{
WorkingTimeRange[] result = null;
RepositoryAction.Query(c =>
{
var dao = new WorkingTimeDao(c, null);
result = dao.SelectByTaskIds(new int[] { taskId.Value })
.Select(t => t.ToDomainObject())
.ToArray();
});
return(result);
}
public WorkingTimeRange[] SelectByYmd(string ymd)
{
WorkingTimeRange[] results = null;
RepositoryAction.Query(c =>
{
var dao = new WorkingTimeDao(c, null);
results = dao.SelectYmd(ymd)
.Select(r => r.ToDomainObject())
.OrderBy(r => r.TimePeriod.StartDateTime)
.ToArray();
});
return(results);
}
public string GetSystemVersion()
{
#region SQL
const string sql = @"
SELECT
MAX(version) version
FROM
systemversionlog
";
#endregion
var version = "";
RepositoryAction.Query(c =>
{
var result = c.Query(sql);
version = result.FirstOrDefault()?.version ?? "";
});
return(version);
}
private bool ExistVersionTable()
{
#region SQL
const string sql = @"
SELECT
COUNT(1) as cnt
FROM
sqlite_master
WHERE
TYPE = 'table'
AND name = 'systemversionlog'
";
#endregion
var existVersionTable = false;
RepositoryAction.Query(c =>
{
var result = c.Query(sql);
existVersionTable = result.FirstOrDefault()?.cnt > 0;
});
return(existVersionTable);
}