//private static readonly SolidBrush ActiveForeGround = new SolidBrush(ColorTranslator.FromHtml("#FF0000"));
//private static readonly SolidBrush InactiveForeGround = new SolidBrush(ColorTranslator.FromHtml("#00FF00"));
#endregion
#region Internal Method
/// <summary>
/// テンプレートのヘッダ一覧を取得する
/// </summary>
/// <returns></returns>
internal List <TemplateListItem> Select()
{
var result = new List <TemplateListItem>();
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
database.Open();
var entity = new TemplateEntity(database);
using (var recset = entity.Select()) {
while (recset.Read())
{
result.Add(new TemplateListItem()
{
Id = recset.GetLong(TemplateEntity.Cols.Id),
Name = recset.GetString(TemplateEntity.Cols.Name),
Sun = recset.GetBool(TemplateEntity.Cols.Sun) ? ActiveForeGround : InactiveForeGround,
Mon = recset.GetBool(TemplateEntity.Cols.Mon) ? ActiveForeGround : InactiveForeGround,
Tue = recset.GetBool(TemplateEntity.Cols.Tue) ? ActiveForeGround : InactiveForeGround,
Wed = recset.GetBool(TemplateEntity.Cols.Wed) ? ActiveForeGround : InactiveForeGround,
Thu = recset.GetBool(TemplateEntity.Cols.Thu) ? ActiveForeGround : InactiveForeGround,
Fri = recset.GetBool(TemplateEntity.Cols.Fri) ? ActiveForeGround : InactiveForeGround,
Sat = recset.GetBool(TemplateEntity.Cols.Sat) ? ActiveForeGround : InactiveForeGround
});
}
}
}
return(result);
}
/// <summary>
/// 空のテンプレート情報を作成する
/// </summary>
/// <returns>空のテンプレートデータ</returns>
internal TemplateData CreateEmptyTemplate()
{
var result = new TemplateData()
{
LogList = new ObservableCollection <TemplateDetailData>()
};
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
database.Open();
var categoryEntity = new CategoryEntity(database);
var categories = new Dictionary <long, string>();
using (var recset = categoryEntity.Select()) {
while (recset.Read())
{
if (!recset.GetBool(CategoryEntity.Cols.Visible))
{
continue;
}
result.LogList.Add(new TemplateDetailData()
{
CategoryId = recset.GetLong(CategoryEntity.Cols.Id),
CategoryName = recset.GetString(CategoryEntity.Cols.Name),
IsCategory = true
});
}
}
}
return(result);
}
/// <summary>
/// IDをキーとして並び順・カテゴリIDを更新する
/// </summary>
/// <param name="logList">ログデータ</param>
internal void UpdateTempOrderById(ObservableCollection <TempLogData> logList)
{
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
try {
database.Open();
database.BeginTrans();
var entity = new TempLogEntity(database);
foreach (var data in logList)
{
if (data.IsCategory)
{
continue;
}
entity.ClearParams();
entity.AddParams(LogDetailEntity.Cols.Priority, data.Priority);
entity.UpdateById(data.Id);
}
database.CommitTrans();
} catch (Exception ex) {
database.RollbackTrans();
throw ex;
}
}
}
/// <summary>
/// Create Database File
/// </summary>
/// <param name="file">database file</param>
public void CreateDatabase(string file = "")
{
if (0 == file.Length)
{
file = OsnCsLib.Common.Util.GetAppPath() + @"app.data";
}
if (System.IO.File.Exists(file))
{
this.BackupData(file);
}
else
{
using (var database = new MyLogDatabase(file)) {
try {
database.Open();
database.BeginTrans();
new CategoryEntity(database).Create();
new LogEntity(database).Create();
new LogDetailEntity(database).Create();
new TemplateEntity(database).Create();
new TemplateDetailEntity(database).Create();
database.CommitTrans();
} catch (Exception ex) {
Message.ShowError(null, Message.ErrId.Err002, ex.Message);
}
}
this.DatabaseFile = file;
this.Save();
}
}
/// <summary>
/// テンプログを取得
/// </summary>
/// <returns>取得結果</returns>
internal ObservableCollection <TempLogData> SelectTempLogList()
{
var result = new ObservableCollection <TempLogData>();
result.Add(new TempLogData()
{
IsCategory = true,
CategoryName = "時間帯が未定のtodo"
});
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
database.Open();
var eneity = new TempLogEntity(database);
using (var recset = eneity.Select()) {
while (recset.Read())
{
result.Add(new TempLogData()
{
Id = recset.GetLong(TempLogEntity.Cols.Id),
Priority = recset.GetInt(TempLogEntity.Cols.Priority),
Todo = recset.GetString(TempLogEntity.Cols.Todo),
Memo = recset.GetString(TempLogEntity.Cols.Memo)
});
}
}
}
return(result);
}
/// <summary>
/// IDをキーとしてテンプログを更新する
/// </summary>
/// <param name="id">ID</param>
/// <param name="entity">エンティティ</param>
private void UpdateLogDById(long id, TempLogEntity entity)
{
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
try {
database.Open();
database.BeginTrans();
entity.Database = database;
entity.UpdateById(id);
database.CommitTrans();
} catch (Exception ex) {
database.RollbackTrans();
throw ex;
}
}
}
/// <summary>
/// IDをキーとして削除する
/// </summary>
/// <param name="id">ID</param>
internal void DeleteTempLogById(long id)
{
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
try {
database.Open();
database.BeginTrans();
var entity = new TempLogEntity(database);
entity.DeleteById(id);
database.CommitTrans();
} catch (Exception ex) {
database.RollbackTrans();
throw ex;
}
}
}
/// <summary>
/// テンプレート情報を全件取得する。
/// </summary>
/// <returns>テンプレート情報</returns>
internal ObservableCollection <TemplateData> Select()
{
var result = new ObservableCollection <TemplateData>();
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
database.Open();
// カテゴリ情報を取得
var categoryEntity = new CategoryEntity(database);
var categories = new Dictionary <long, string>();
using (var recset = categoryEntity.SelectVisible()) {
while (recset.Read())
{
categories.Add(recset.GetLong(CategoryEntity.Cols.Id),
recset.GetString(CategoryEntity.Cols.Name));
}
}
var headerEntity = new TemplateEntity(database);
var detailEntity = new TemplateDetailEntity(database);
using (var recset = headerEntity.Select()) {
while (recset.Read())
{
// ヘッダ情報を取得
var templateData = new TemplateData {
Id = recset.GetLong(TemplateEntity.Cols.Id),
Name = recset.GetString(TemplateEntity.Cols.Name),
Sun = recset.GetBool(TemplateEntity.Cols.Sun),
Mon = recset.GetBool(TemplateEntity.Cols.Mon),
Tue = recset.GetBool(TemplateEntity.Cols.Tue),
Wed = recset.GetBool(TemplateEntity.Cols.Wed),
Thu = recset.GetBool(TemplateEntity.Cols.Thu),
Fri = recset.GetBool(TemplateEntity.Cols.Fri),
Sat = recset.GetBool(TemplateEntity.Cols.Sat),
LogList = new ObservableCollection <TemplateDetailData>()
};
// 明細情報を取得
SelectDetailByTemplateId(templateData.LogList, detailEntity, templateData.Id, categories);
result.Add(templateData);
}
}
}
return(result);
}
/// <summary>
/// テンプログの空行を作成する
/// </summary>
/// <param name="order">並び順</param>
/// <returns></returns>
internal TempLogData InsertEmptyTempLogRow(int order)
{
var result = new TempLogData();
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
database.Open();
database.BeginTrans();
var entity = new TempLogEntity(database)
{
Priority = order
};
result.Id = entity.Insert();
result.Priority = order;
database.CommitTrans();
}
return(result);
}
/// <summary>
/// テンプレート情報を更新する
/// </summary>
/// <param name="data">更新情報</param>
/// <param name="isNew">true:新規、false:更新</param>
/// <remarks>新規のケースもあるのでdelete → insertで処理を行う</remarks>
internal void Update(TemplateData data, bool isNew)
{
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
try {
database.Open();
database.BeginTrans();
var headerEntity = new TemplateEntity(database);
var detailEntity = new TemplateDetailEntity(database);
// テンプレート情報(ヘッダ)
headerEntity.Set(data);
long id = data.Id;
if (isNew)
{
id = headerEntity.Insert();
}
else
{
headerEntity.Update();
}
// テンプレート情報(明細)
if (!isNew)
{
detailEntity.DeleteByTemplateId(id);
}
foreach (var detail in data.LogList)
{
if (detail.IsCategory)
{
continue;
}
detailEntity.Set(detail, id);
detailEntity.Insert();
}
database.CommitTrans();
} catch (Exception ex) {
database.RollbackTrans();
throw ex;
}
}
}
/// <summary>
/// テンプレートからログを作成する。
/// </summary>
/// <param name="recordedOn">日付</param>
/// <returns></returns>
internal LogData CreateLogByRecordedOn(string recordedOn)
{
long templateId;
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
database.Open();
var templateEntity = new TemplateEntity(database);
using (var recset = templateEntity.SelectByWeekDay(recordedOn)) {
// テンプレートが存在しない場合は空の情報を作成
if (!recset.Read())
{
return(CreateEmptyLog(recordedOn));
}
templateId = recset.GetLong(TemplateEntity.Cols.Id);
}
}
return(this.CreateLogByTemplateId(templateId, recordedOn));
}
/// <summary>
/// 空のログを作成する
/// </summary>
/// <param name="recordedOn">記録日</param>
/// <returns>空のログデータ</returns>
internal LogData CreateEmptyLog(string recordedOn)
{
var result = new LogData()
{
LogList = new ObservableCollection <LogDetailData>()
};
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
try {
database.Open();
var categoryEntity = new CategoryEntity(database);
var categories = new Dictionary <long, string>();
using (var recset = categoryEntity.Select()) {
while (recset.Read())
{
if (!recset.GetBool(CategoryEntity.Cols.Visible))
{
continue;
}
result.LogList.Add(new LogDetailData()
{
CategoryId = recset.GetLong(CategoryEntity.Cols.Id),
CategoryName = recset.GetString(CategoryEntity.Cols.Name),
IsCategory = true
});
}
}
database.BeginTrans();
var logEntity = new LogEntity(database)
{
RecordedOn = recordedOn
};
result.Id = logEntity.Insert();
database.CommitTrans();
} catch (Exception ex) {
database.RollbackTrans();
throw ex;
}
}
return(result);
}
/// <summary>
/// カテゴリ情報を更新する
/// </summary>
/// <param name="categories">カテゴリ情報</param>
internal void Update(ObservableCollection <CategoryData> categories)
{
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
try {
database.Open();
database.BeginTrans();
var entity = new CategoryEntity(database);
foreach (var(category, index) in categories.Select((cateogry, index) => (cateogry, index)))
{
category.Priority = index;
entity.Set(category);
entity.UpdateById(category.Id);
}
database.CommitTrans();
} catch (Exception ex) {
database.RollbackTrans();
throw ex;
}
}
}
/// <summary>
/// ログ情報(ヘッダ)を作成する
/// </summary>
/// <param name="recordedOn">記録日</param>
/// <returns>ID</returns>
internal long InsertHeader(string recordedOn)
{
long result;
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
try {
database.Open();
database.BeginTrans();
var entity = new LogEntity(database)
{
RecordedOn = recordedOn
};
result = entity.Insert();
database.CommitTrans();
} catch (Exception ex) {
database.RollbackTrans();
throw ex;
}
}
return(result);
}
/// <summary>
/// 空行を作成する
/// </summary>
/// <param name="logId">ログ情報ID</param>
/// <param name="categoryId">カテゴリID</param>
/// <param name="order">並び順</param>
/// <returns></returns>
internal LogDetailData InsertEmptyRow(long logId, long categoryId, int order)
{
var result = new LogDetailData();
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
database.Open();
database.BeginTrans();
var entity = new LogDetailEntity(database)
{
LogId = logId,
CategoryId = categoryId,
Priority = order
};
result.Id = entity.Insert();
result.LogId = logId;
result.CategoryId = categoryId;
result.Priority = order;
database.CommitTrans();
}
return(result);
}
/// <summary>
/// テンプレートからログを作成する。
/// </summary>
/// <param name="templateId">テンプレートID</param>
/// /// <param name="recordedOn">日付</param>
/// <returns></returns>
internal LogData CreateLogByTemplateId(long templateId, string recordedOn)
{
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
database.Open();
try {
database.BeginTrans();
var logEntity = new LogEntity(database)
{
RecordedOn = recordedOn
};
var id = logEntity.Insert();
var templateDetailEntity = new TemplateDetailEntity(database);
templateDetailEntity.InsertToLog(templateId, id);
database.CommitTrans();
} catch (Exception ex) {
database.RollbackTrans();
throw ex;
}
}
return(this.SelectByRecordedOn(recordedOn));
}
/// <summary>
/// カテゴリ情報を取得
/// </summary>
/// <returns>取得結果(該当情報が存在しない場合は空のリストを返却)</returns>
internal ObservableCollection <CategoryData> Select()
{
var result = new ObservableCollection <CategoryData>();
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
database.Open();
var entity = new CategoryEntity(database);
using (var recset = entity.Select()) {
while (recset.Read())
{
var data = new CategoryData()
{
Id = recset.GetLong(CategoryEntity.Cols.Id),
Name = recset.GetString(CategoryEntity.Cols.Name),
Visible = recset.GetBool(CategoryEntity.Cols.Visible),
Priority = recset.GetInt(CategoryEntity.Cols.Priority)
};
result.Add(data);
}
}
}
return(result);
}
/// <summary>
/// 記録日をキーとしてログ情報を取得
/// </summary>
/// <param name="recordedOn">記録日</param>
/// <returns>取得結果(該当情報が存在しない場合はnullを返却)</returns>
internal LogData SelectByRecordedOn(string recordedOn)
{
LogData result = null;
var startIndex = 0;
using (var database = new MyLogDatabase(Constants.DatabaseFile())) {
database.Open();
// ヘッダ情報を取得
var logId = 0L;
var headerEntity = new LogEntity(database);
using (var recset = headerEntity.SelectByRecordedOn(recordedOn)) {
if (!recset.Read())
{
return(result);
}
logId = recset.GetInt(LogEntity.Cols.Id);
}
result = new LogData {
Id = logId,
RecordedOn = recordedOn,
LogList = new ObservableCollection <LogDetailData>()
};
// カテゴリ情報を取得
var categories = new Dictionary <long, string>();
var categoryEntity = new CategoryEntity(database);
using (var recset = categoryEntity.SelectVisible()) {
while (recset.Read())
{
categories.Add(recset.GetLong(CategoryEntity.Cols.Id),
recset.GetString(CategoryEntity.Cols.Name));
}
}
// 明細情報を取得
var detailEntity = new LogDetailEntity(database);
using (var recset = detailEntity.SelectByLogHId(logId)) {
var currentCategory = -1L;
while (recset.Read())
{
var detail = new LogDetailData {
Id = recset.GetLong(LogDetailEntity.Cols.Id),
LogId = recset.GetLong(LogDetailEntity.Cols.LogId),
CategoryId = recset.GetLong(LogDetailEntity.Cols.CategoryId),
Priority = recset.GetInt(LogDetailEntity.Cols.Priority),
Result = (ResultState)recset.GetInt(LogDetailEntity.Cols.Result),
Todo = recset.GetString(LogDetailEntity.Cols.Todo),
PlanStart = recset.GetString(LogDetailEntity.Cols.PlanStart),
PlanEnd = recset.GetString(LogDetailEntity.Cols.PlanEnd),
PlanTime = recset.GetInt(LogDetailEntity.Cols.PlanTime).ToString(),
ActualStart = recset.GetString(LogDetailEntity.Cols.ActualStart),
ActualEnd = recset.GetString(LogDetailEntity.Cols.ActualEnd),
ActualTime = recset.GetInt(LogDetailEntity.Cols.ActualTime).ToString(),
Memo = recset.GetString(LogDetailEntity.Cols.Memo)
};
detail.Priority = recset.GetInt(LogDetailEntity.Cols.Priority);
detail.IsCategory = false;
if (currentCategory != detail.CategoryId)
{
for (var i = startIndex; i < categories.Count; i++)
{
var categoryId = categories.ElementAt(i).Key;
var category = new LogDetailData {
IsCategory = true,
CategoryId = categoryId,
CategoryName = categories[categoryId]
};
result.LogList.Add(category);
if (categoryId == detail.CategoryId)
{
currentCategory = categoryId;
startIndex = i + 1;
break;
}
currentCategory = categoryId;
}
}
result.LogList.Add(detail);
}
startIndex = -1;
for (var i = 0; i < categories.Count; i++)
{
if (currentCategory == categories.ElementAt((int)i).Key)
{
startIndex = i;
break;
}
}
for (var i = startIndex + 1; i < categories.Count; i++)
{
var categoryId = categories.ElementAt(i).Key;
if (currentCategory < categoryId)
{
var category = new LogDetailData {
IsCategory = true,
CategoryId = categoryId,
CategoryName = categories[categoryId]
};
result.LogList.Add(category);
}
}
}
}
return(result);
}
