//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); }