/// <summary> /// 備考リストを更新する /// </summary> /// <param name="remark">選択対象の備考</param> /// <returns></returns> private async Task UpdateRemarkListAsync(string remark = null) { if (this.WVM?.SelectedItemVM?.Id == null) { return; } ObservableCollection <string> remarkVMList = new ObservableCollection <string>() { string.Empty }; string selectedRemark = remark ?? this.WVM.SelectedRemark ?? remarkVMList[0]; using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT remark FROM hst_remark WHERE del_flg = 0 AND item_id = @{0} ORDER BY used_time DESC;", this.WVM.SelectedItemVM.Id); reader.ExecWholeRow((count, record) => { string tmp = record["remark"]; remarkVMList.Add(tmp); return(true); }); } this.WVM.RemarkList = remarkVMList; this.WVM.SelectedRemark = selectedRemark; }
/// <summary> /// カテゴリリストを更新する /// </summary> /// <param name="categoryId">選択対象のカテゴリID</param> /// <returns></returns> private async Task UpdateCategoryListAsync(int?categoryId = null) { ObservableCollection <CategoryViewModel> categoryVMList = new ObservableCollection <CategoryViewModel>() { new CategoryViewModel() { Id = -1, Name = "(指定なし)" } }; int?tmpCategoryId = categoryId ?? this.WVM.SelectedCategoryVM?.Id ?? categoryVMList[0].Id; CategoryViewModel selectedCategoryVM = categoryVMList[0]; using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT category_id, category_name FROM mst_category C WHERE del_flg = 0 AND EXISTS (SELECT * FROM mst_item I WHERE I.category_id = C.category_id AND balance_kind = @{0} AND del_flg = 0 AND EXISTS (SELECT * FROM rel_book_item RBI WHERE book_id = @{1} AND RBI.item_id = I.item_id)) ORDER BY sort_order;", (int)this.WVM.SelectedBalanceKind, this.WVM.SelectedBookVM.Id); reader.ExecWholeRow((count, record) => { CategoryViewModel vm = new CategoryViewModel() { Id = record.ToInt("category_id"), Name = record["category_name"] }; categoryVMList.Add(vm); if (vm.Id == categoryId) { selectedCategoryVM = vm; } return(true); }); } this.WVM.CategoryVMList = categoryVMList; this.WVM.SelectedCategoryVM = selectedCategoryVM; }
/// <summary> /// 店舗リストを更新する /// </summary> /// <param name="shopName">選択対象の店舗名</param> /// <returns></returns> private async Task UpdateShopListAsync(string shopName = null) { if (this.WVM.SelectedItemVM == null) { return; } ObservableCollection <string> shopNameVMList = new ObservableCollection <string>() { string.Empty }; string selectedShopName = shopName ?? this.WVM.SelectedShopName ?? shopNameVMList[0]; using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT shop_name FROM hst_shop WHERE del_flg = 0 AND item_id = @{0} ORDER BY used_time DESC;", this.WVM.SelectedItemVM.Id); reader.ExecWholeRow((count, record) => { string tmp = record["shop_name"]; shopNameVMList.Add(tmp); return(true); }); } this.WVM.ShopNameList = shopNameVMList; this.WVM.SelectedShopName = selectedShopName; }
/// <summary> /// 帳簿リストを更新する /// </summary> /// <param name="bookId">選択対象の帳簿ID</param> /// <returns></returns> private async Task UpdateBookListAsync(int?bookId = null) { // 帳簿を取得する ObservableCollection <BookViewModel> bookVMList = new ObservableCollection <BookViewModel>(); BookViewModel selectedBookVM = null; using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT book_id, book_name FROM mst_book WHERE del_flg = 0 ORDER BY sort_order;"); reader.ExecWholeRow((count, record) => { BookViewModel vm = new BookViewModel() { Id = record.ToInt("book_id"), Name = record["book_name"] }; bookVMList.Add(vm); if (selectedBookVM == null || bookId == vm.Id) { selectedBookVM = vm; } return(true); }); } this.WVM.BookVMList = bookVMList; this.WVM.SelectedBookVM = selectedBookVM; }
public void ExtractData_HasCreate() { var reader = new DaoReader(); var entity = reader.ExtractData(FileText); entity.DataBehaviours.ShouldContain(m => m.Name.Equals("Create")); }
public void ExtractData_EmptyMethodsAreIgnored() { var reader = new DaoReader(); var entity = reader.ExtractData(FileText); entity.DataBehaviours.ShouldNotContain(m => string.IsNullOrEmpty(m.Expression)); }
public void ExtractData_NamespaceMatch() { var reader = new DaoReader(); var entity = reader.ExtractData(FileText); entity.Namespace.ShouldBe("Omnia.Behaviours.T99.External.LocalSys.Daos"); }
public void ExtractData_Successfully() { var reader = new DaoReader(); var entity = reader.ExtractData(FileText); entity.DataBehaviours.ShouldNotBeNull(); entity.DataBehaviours.Count.ShouldBe(5); }
public void ExtractData_SuccessfullyExtractUsings() { var reader = new DaoReader(); var entity = reader.ExtractData(FileText); entity.Usings.ShouldNotBeNull(); entity.Usings.Count.ShouldBe(1); entity.Usings.Single().ShouldBe("MySystem"); }
public void ExtractData_UsesCommentDescription() { var reader = new DaoReader(); var create = reader.ExtractData(FileText) .DataBehaviours .First(m => m.Type == Omnia.CLI.Commands.Model.Apply.Data.Server.DataBehaviourType.Create); create.Description.ShouldBe("Create path.txt file"); }
public void ExtractData_ValidType() { var reader = new DaoReader(); var read = reader.ExtractData(FileText) .DataBehaviours .First(m => m.Name.Equals("Read")); read.Type.ShouldBe(Omnia.CLI.Commands.Model.Apply.Data.Server.DataBehaviourType.Read); }
/// <summary> /// 比較情報を更新する /// </summary> private async Task UpdateComparisonInfoAsync() { // 指定された帳簿内で、日付、金額が一致する帳簿項目を探す using (DaoBase dao = this.builder.Build()) { foreach (var vm in this.WVM.CsvComparisonVMList) { // 前回の結果をクリアする vm.ClearActionInfo(); DaoReader reader = await dao.ExecQueryAsync(@" SELECT A.action_id, I.item_name, A.act_value, A.shop_name, A.remark, A.is_match FROM hst_action A INNER JOIN (SELECT * FROM mst_item WHERE del_flg = 0) I ON I.item_id = A.item_id WHERE to_date(to_char(act_time, 'YYYY-MM-DD'), 'YYYY-MM-DD') = @{0} AND A.act_value = -@{1} AND book_id = @{2} AND A.del_flg = 0;", vm.Record.Date, vm.Record.Value, this.WVM.SelectedBookVM.Id); reader.ExecWholeRow((count, record) => { int actionId = record.ToInt("action_id"); string itemName = record["item_name"]; int outgo = Math.Abs(record.ToInt("act_value")); string shopName = record["shop_name"]; string remark = record["remark"]; bool isMatch = record.ToInt("is_match") == 1; // 帳簿項目IDが使用済なら次のレコードを調べるようにする bool ans = this.WVM.CsvComparisonVMList.Where((tmpVM) => { return(tmpVM.ActionId == actionId); }).Count() != 0; if (!ans) { vm.ActionId = actionId; vm.ItemName = itemName; vm.ShopName = shopName; vm.Remark = remark; vm.IsMatch = isMatch; } return(ans); }); } } List <CsvComparisonViewModel> list = this.WVM.CsvComparisonVMList.ToList(); // 日付と帳簿項目IDでソートする list.Sort((vm1, vm2) => { int rslt = (int)((vm1.Record.Date - vm2.Record.Date).TotalDays); if (rslt == 0) { rslt = (vm1.ActionId ?? 0) - (vm2.ActionId ?? 0); } return(rslt); }); this.WVM.CsvComparisonVMList = new ObservableCollection <CsvComparisonViewModel>(list); }
public void ExtractData_ValidExpression() { var reader = new DaoReader(); var create = reader.ExtractData(FileText) .DataBehaviours .First(m => m.Type == Omnia.CLI.Commands.Model.Apply.Data.Server.DataBehaviourType.Create); create.Expression.ShouldBe(@"using (StreamWriter file = File.CreateText(@""D:\path.txt"")) { var serializer = new JsonSerializer(); serializer.Serialize(file, dto); } return new CustomerDto();"); }
/// <summary> /// 手数料項目リストを更新する /// </summary> /// <param name="itemId">選択対象の項目</param> /// <returns></returns> private async Task UpdateItemListAsync(int?itemId = null) { ObservableCollection <ItemViewModel> itemVMList = new ObservableCollection <ItemViewModel>(); ItemViewModel selectedItemVM = null; using (DaoBase dao = this.builder.Build()) { int bookId = -1; switch (this.WVM.SelectedCommissionKind) { case CommissionKind.FromBook: bookId = this.WVM.SelectedFromBookVM.Id.Value; break; case CommissionKind.ToBook: bookId = this.WVM.SelectedToBookVM.Id.Value; break; } DaoReader reader = await dao.ExecQueryAsync(@" SELECT item_id, item_name FROM mst_item I WHERE del_flg = 0 AND EXISTS (SELECT * FROM rel_book_item RBI WHERE book_id = @{0} AND RBI.item_id = I.item_id AND del_flg = 0) AND EXISTS (SELECT * FROM mst_category C WHERE C.category_id = I.category_id AND balance_kind = @{1} AND del_flg = 0) ORDER BY sort_order;", bookId, (int)BalanceKind.Outgo); reader.ExecWholeRow((count, record) => { ItemViewModel vm = new ItemViewModel() { Id = record.ToInt("item_id"), Name = record["item_name"] }; itemVMList.Add(vm); if (selectedItemVM == null || vm.Id == itemId) { selectedItemVM = vm; } return(true); }); } this.WVM.ItemVMList = itemVMList; this.WVM.SelectedItemVM = selectedItemVM; }
/// <summary> /// 帳簿リストを更新する /// </summary> /// <param name="bookId">選択対象の帳簿ID</param> private async Task UpdateBookListAsync(int?bookId = null) { int?tmpBookId = bookId ?? this.WVM.SelectedBookVM?.Id; ObservableCollection <BookComparisonViewModel> bookCompVMList = new ObservableCollection <BookComparisonViewModel>(); BookComparisonViewModel selectedBookCompVM = null; using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT * FROM mst_book WHERE del_flg = 0 AND book_kind <> @{0} ORDER BY sort_order;", (int)BookKind.Wallet); reader.ExecWholeRow((count, record) => { string jsonCode = record["json_code"]; MstBookJsonObject jsonObj = JsonConvert.DeserializeObject <MstBookJsonObject>(jsonCode); BookComparisonViewModel vm = new BookComparisonViewModel() { Id = record.ToInt("book_id"), Name = record["book_name"], ActDateIndex = jsonObj?.CsvActDateIndex, OutgoIndex = jsonObj?.CsvOutgoIndex, ItemNameIndex = jsonObj?.CsvItemNameIndex }; bookCompVMList.Add(vm); if (vm.Id == tmpBookId) { selectedBookCompVM = vm; } return(true); }); } this.WVM.BookVMList = bookCompVMList; this.WVM.SelectedBookVM = selectedBookCompVM ?? bookCompVMList[0]; }
/// <summary> /// 帳簿リストを更新する /// </summary> /// <param name="fromBookId">移動元帳簿ID</param> /// <param name="toBookId">移動先帳簿ID</param> /// <returns></returns> private async Task UpdateBookListAsync(int?fromBookId = null, int?toBookId = null) { ObservableCollection <BookViewModel> bookVMList = new ObservableCollection <BookViewModel>(); BookViewModel fromBookVM = null; BookViewModel toBookVM = null; int?debitBookId = null; int?payDay = null; using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT book_id, book_name, book_kind, debit_book_id, pay_day FROM mst_book WHERE del_flg = 0 ORDER BY sort_order;"); reader.ExecWholeRow((count, record) => { BookViewModel vm = new BookViewModel() { Id = record.ToInt("book_id"), Name = record["book_name"] }; bookVMList.Add(vm); if (fromBookVM == null || fromBookId == vm.Id) { fromBookVM = vm; switch (this.WVM.RegMode) { case RegistrationMode.Add: { if (record.ToInt("book_kind") == (int)BookKind.CreditCard) { debitBookId = record.ToNullableInt("debit_book_id"); payDay = record.ToNullableInt("pay_day"); } } break; } ; } if (toBookVM == null || toBookId == vm.Id) { toBookVM = vm; } return(true); }); } this.WVM.BookVMList = bookVMList; this.WVM.SelectedFromBookVM = fromBookVM; this.WVM.SelectedToBookVM = toBookVM; switch (this.WVM.RegMode) { case RegistrationMode.Add: { if (debitBookId != null) { this.WVM.SelectedFromBookVM = bookVMList.FirstOrDefault((vm) => { return(vm.Id == debitBookId); }); } this.WVM.FromDate = this.selectedDate ?? ((this.selectedMonth == null || this.selectedMonth?.Month == DateTime.Today.Month) ? DateTime.Today : this.selectedMonth.Value); if (payDay != null) { this.WVM.FromDate = this.WVM.FromDate.GetDateInMonth(payDay.Value); } this.WVM.IsLink = true; this.WVM.SelectedCommissionKind = CommissionKind.FromBook; } break; } ; }
/// <summary> /// DBに登録する /// </summary> /// <returns>登録された帳簿項目ID</returns> private async Task <int?> RegisterToDbAsync() { BalanceKind balanceKind = this.WVM.SelectedBalanceKind; // 収支種別 int bookId = this.WVM.SelectedBookVM.Id.Value; // 帳簿ID int itemId = this.WVM.SelectedItemVM.Id; // 帳簿項目ID DateTime actTime = this.WVM.SelectedDate; // 入力日付 int actValue = (balanceKind == BalanceKind.Income ? 1 : -1) * this.WVM.Value.Value; // 値 string shopName = this.WVM.SelectedShopName; // 店舗名 string remark = this.WVM.SelectedRemark; // 備考 int count = this.WVM.Count; // 繰返し回数 bool isLink = this.WVM.IsLink; int isMatch = this.WVM.IsMatch == true ? 1 : 0; HolidaySettingKind holidaySettingKind = this.WVM.SelectedHolidaySettingKind; int?resActionId = null; // 休日設定を考慮した日付を取得する関数 DateTime getDateTimeWithHolidaySettingKind(DateTime tmpDateTime) { switch (holidaySettingKind) { case HolidaySettingKind.BeforeHoliday: while (tmpDateTime.IsNationalHoliday() || tmpDateTime.DayOfWeek == DayOfWeek.Saturday || tmpDateTime.DayOfWeek == DayOfWeek.Sunday) { tmpDateTime = tmpDateTime.AddDays(-1); } break; case HolidaySettingKind.AfterHoliday: while (tmpDateTime.IsNationalHoliday() || tmpDateTime.DayOfWeek == DayOfWeek.Saturday || tmpDateTime.DayOfWeek == DayOfWeek.Sunday) { tmpDateTime = tmpDateTime.AddDays(1); } break; } return(tmpDateTime); } using (DaoBase dao = this.builder.Build()) { switch (this.WVM.RegMode) { case RegistrationMode.Add: case RegistrationMode.Copy: { #region 帳簿項目を追加する if (count == 1) // 繰返し回数が1回(繰返しなし) { DaoReader reader = await dao.ExecQueryAsync(@" INSERT INTO hst_action (book_id, item_id, act_time, act_value, shop_name, remark, is_match, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, @{2}, @{3}, @{4}, @{5}, 0, 0, 'now', @{6}, 'now', @{7}) RETURNING action_id;", bookId, itemId, actTime, actValue, shopName, remark, Updater, Inserter); reader.ExecARow((record) => { resActionId = record.ToInt("action_id"); }); } else // 繰返し回数が2回以上(繰返しあり) { await dao.ExecTransactionAsync(async() => { int tmpGroupId = -1; // グループIDを取得する DaoReader reader = await dao.ExecQueryAsync(@" INSERT INTO hst_group (group_kind, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, 0, 'now', @{1}, 'now', @{2}) RETURNING group_id;", (int)GroupKind.Repeat, Updater, Inserter); reader.ExecARow((record) => { tmpGroupId = record.ToInt("group_id"); }); DateTime tmpActTime = getDateTimeWithHolidaySettingKind(actTime); // 登録日付 for (int i = 0; i < count; ++i) { reader = await dao.ExecQueryAsync(@" INSERT INTO hst_action (book_id, item_id, act_time, act_value, shop_name, group_id, remark, is_match, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, @{2}, @{3}, @{4}, @{5}, @{6}, 0, 0, 'now', @{7}, 'now', @{8}) RETURNING action_id;", bookId, itemId, tmpActTime, actValue, shopName, tmpGroupId, remark, Updater, Inserter); // 繰り返しの最初の1回を選択するようにする if (i == 0) { reader.ExecARow((record) => { resActionId = record.ToInt("action_id"); }); } tmpActTime = getDateTimeWithHolidaySettingKind(actTime.AddMonths(i + 1)); } }); } #endregion } break; case RegistrationMode.Edit: { #region 帳簿項目を編集する if (count == 1) { #region 繰返し回数が1回 if (this.groupId == null) { #region グループに属していない await dao.ExecNonQueryAsync(@" UPDATE hst_action SET book_id = @{0}, item_id = @{1}, act_time = @{2}, act_value = @{3}, shop_name = @{4}, remark = @{5}, is_match = @{6}, update_time = 'now', updater = @{7} WHERE action_id = @{8};", bookId, itemId, actTime, actValue, shopName, remark, isMatch, Updater, this.selectedActionId); #endregion } else { #region グループに属している await dao.ExecTransactionAsync(async() => { // この帳簿項目以降の繰返し分のレコードを削除する await dao.ExecNonQueryAsync(@" UPDATE hst_action SET del_flg = 1, update_time = 'now', updater = @{0} WHERE del_flg = 0 AND group_id = @{1} AND act_time > (SELECT act_time FROM hst_action WHERE action_id = @{2});", Updater, this.groupId, this.selectedActionId); // グループに属する項目の個数を調べる DaoReader reader = await dao.ExecQueryAsync(@" SELECT action_id FROM hst_action WHERE del_flg = 0 AND group_id = @{0};", this.groupId); if (reader.Count <= 1) { #region グループに属する項目が1項目以下 // この帳簿項目のグループIDをクリアする await dao.ExecNonQueryAsync(@" UPDATE hst_action SET book_id = @{0}, item_id = @{1}, act_time = @{2}, act_value = @{3}, shop_name = @{4}, group_id = null, remark = @{5}, is_match = @{6}, update_time = 'now', updater = @{7} WHERE action_id = @{8};", bookId, itemId, actTime, actValue, shopName, remark, isMatch, Updater, this.selectedActionId); // グループを削除する await dao.ExecNonQueryAsync(@" UPDATE hst_group SET del_flg = 1, update_time = 'now', updater = @{0} WHERE del_flg = 0 AND group_id = @{1};", Updater, this.groupId); #endregion } else { #region グループに属する項目が2項目以上 // この帳簿項目のグループIDをクリアせずに残す await dao.ExecNonQueryAsync(@" UPDATE hst_action SET book_id = @{0}, item_id = @{1}, act_time = @{2}, act_value = @{3}, shop_name = @{4}, remark = @{5}, is_match = @{6}, update_time = 'now', updater = @{7} WHERE action_id = @{8};", bookId, itemId, actTime, actValue, shopName, remark, isMatch, Updater, this.selectedActionId); #endregion } }); #endregion } #endregion } else { #region 繰返し回数が2回以上 await dao.ExecTransactionAsync(async() => { List <int> actionIdList = new List <int>(); DaoReader reader; if (this.groupId == null) { #region グループIDが未割当て // グループIDを取得する reader = await dao.ExecQueryAsync(@" INSERT INTO hst_group (group_kind, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, 0, 'now', @{1}, 'now', @{2}) RETURNING group_id;", (int)GroupKind.Repeat, Updater, Inserter); reader.ExecARow((record) => { this.groupId = record.ToInt("group_id"); }); actionIdList.Add(this.selectedActionId.Value); #endregion } else { #region グループIDが割当て済 // 変更の対象となる帳簿項目を洗い出す reader = await dao.ExecQueryAsync(@" SELECT action_id FROM hst_action WHERE del_flg = 0 AND group_id = @{0} AND act_time >= (SELECT act_time FROM hst_action WHERE action_id = @{1}) ORDER BY act_time ASC;", this.groupId, this.selectedActionId); reader.ExecWholeRow((recCount, record) => { actionIdList.Add(record.ToInt("action_id")); return(true); }); #endregion } DateTime tmpActTime = getDateTimeWithHolidaySettingKind(actTime); // この帳簿項目にだけis_matchを反映する Debug.Assert(actionIdList[0] == this.selectedActionId); await dao.ExecNonQueryAsync(@" UPDATE hst_action SET book_id = @{0}, item_id = @{1}, act_time = @{2}, act_value = @{3}, shop_name = @{4}, group_id = @{5}, remark = @{6}, is_match = @{7}, update_time = 'now', updater = @{8} WHERE action_id = @{9};", bookId, itemId, tmpActTime, actValue, shopName, this.groupId, remark, isMatch, Updater, this.selectedActionId); tmpActTime = getDateTimeWithHolidaySettingKind(actTime.AddMonths(1)); for (int i = 1; i < actionIdList.Count; ++i) { int targetActionId = actionIdList[i]; if (i < count) // 繰返し回数の範囲内のレコードを更新する // 連動して編集時のみ変更する { if (isLink) { await dao.ExecNonQueryAsync(@" UPDATE hst_action SET book_id = @{0}, item_id = @{1}, act_time = @{2}, act_value = @{3}, shop_name = @{4}, group_id = @{5}, remark = @{6}, update_time = 'now', updater = @{7} WHERE action_id = @{8};", bookId, itemId, tmpActTime, actValue, shopName, this.groupId, remark, Updater, targetActionId); } } else // 繰返し回数が帳簿項目数を下回っていた場合に、越えたレコードを削除する { await dao.ExecNonQueryAsync(@" UPDATE hst_action SET del_flg = 1, update_time = 'now', updater = @{0} WHERE action_id = @{1};", Updater, targetActionId); } tmpActTime = getDateTimeWithHolidaySettingKind(actTime.AddMonths(i + 1)); } // 繰返し回数が帳簿項目数を越えていた場合に、新規レコードを追加する for (int i = actionIdList.Count; i < count; ++i) { await dao.ExecNonQueryAsync(@" INSERT INTO hst_action (book_id, item_id, act_time, act_value, shop_name, group_id, remark, is_match, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, @{2}, @{3}, @{4}, @{5}, @{6}, 0, 0, 'now', @{7}, 'now', @{8});", bookId, itemId, tmpActTime, actValue, shopName, this.groupId, remark, Updater, Inserter); tmpActTime = getDateTimeWithHolidaySettingKind(actTime.AddMonths(i + 1)); } }); #endregion } resActionId = this.selectedActionId; #endregion } break; } if (shopName != string.Empty) { #region 店舗を追加する await dao.ExecTransactionAsync(async() => { DaoReader reader = await dao.ExecQueryAsync(@" SELECT shop_name FROM hst_shop WHERE item_id = @{0} AND shop_name = @{1};", itemId, shopName); if (reader.Count == 0) { await dao.ExecNonQueryAsync(@" INSERT INTO hst_shop (item_id, shop_name, used_time, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, @{2}, 0, 'now', @{3}, 'now', @{4});", itemId, shopName, actTime, Updater, Inserter); } else { await dao.ExecNonQueryAsync(@" UPDATE hst_shop SET used_time = @{0}, del_flg = 0, update_time = 'now', updater = @{1} WHERE item_id = @{2} AND shop_name = @{3} AND used_time < @{0};", actTime, Updater, itemId, shopName); } }); #endregion } if (remark != string.Empty) { #region 備考を追加する await dao.ExecTransactionAsync(async() => { DaoReader reader = await dao.ExecQueryAsync(@" SELECT remark FROM hst_remark WHERE item_id = @{0} AND remark = @{1};", itemId, remark); if (reader.Count == 0) { await dao.ExecNonQueryAsync(@" INSERT INTO hst_remark (item_id, remark, remark_kind, used_time, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, 0, @{2}, 0, 'now', @{3}, 'now', @{4});", itemId, remark, actTime, Updater, Inserter); } else { await dao.ExecNonQueryAsync(@" UPDATE hst_remark SET used_time = @{0}, del_flg = 0, update_time = 'now', updater = @{1} WHERE item_id = @{2} AND remark = @{3} AND used_time < @{0};", actTime, Updater, itemId, remark); } }); #endregion } } return(resActionId); }
/// <summary> /// フォーム読込完了時 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private async void MoveRegistrationWindow_Loaded(object sender, RoutedEventArgs e) { int? fromBookId = this.selectedBookId; int? toBookId = this.selectedBookId; int? commissionItemId = null; string commissionRemark = null; // DBから値を読み込む switch (this.WVM.RegMode) { case RegistrationMode.Add: // 追加時の日時、金額は帳簿リスト更新時に取得する break; case RegistrationMode.Edit: case RegistrationMode.Copy: { DateTime fromDate = DateTime.Now; DateTime toDate = DateTime.Now; CommissionKind commissionKind = CommissionKind.FromBook; int moveValue = -1; int commissionValue = 0; using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT A.book_id, A.action_id, A.item_id, A.act_time, A.act_value, A.remark, I.move_flg FROM hst_action A INNER JOIN (SELECT * FROM mst_item WHERE del_flg = 0) I ON I.item_id = A.item_id WHERE A.del_flg = 0 AND A.group_id = @{0} ORDER BY move_flg DESC;", this.groupId); reader.ExecWholeRow((count, record) => { int bookId = record.ToInt("book_id"); DateTime dateTime = record.ToDateTime("act_time"); int actionId = record.ToInt("action_id"); int itemId = record.ToInt("item_id"); int actValue = record.ToInt("act_value"); int moveFlg = record.ToInt("move_flg"); string remark = record["remark"]; if (moveFlg == 1) { if (actValue < 0) { fromBookId = bookId; fromDate = dateTime; this.fromActionId = actionId; } else { toBookId = bookId; toDate = dateTime; this.toActionId = actionId; moveValue = actValue; } } else // 手数料 { if (bookId == fromBookId) // 移動元負担 { commissionKind = CommissionKind.FromBook; } else if (bookId == toBookId) // 移動先負担 { commissionKind = CommissionKind.ToBook; } this.commissionActionId = actionId; commissionItemId = itemId; commissionValue = Math.Abs(actValue); commissionRemark = remark; } return(true); }); } // WVMに値を設定する if (this.WVM.RegMode == RegistrationMode.Edit) { this.WVM.FromId = this.fromActionId; this.WVM.ToId = this.toActionId; this.WVM.GroupId = this.groupId; this.WVM.CommissionId = this.commissionActionId; } this.WVM.IsLink = (fromDate == toDate); this.WVM.FromDate = fromDate; this.WVM.ToDate = toDate; this.WVM.SelectedCommissionKind = commissionKind; this.WVM.Value = moveValue; this.WVM.Commission = commissionValue; } break; } // リストを更新する await this.UpdateBookListAsync(fromBookId, toBookId); await this.UpdateItemListAsync(commissionItemId); await this.UpdateRemarkListAsync(commissionRemark); // イベントハンドラを登録する this.RegisterEventHandlerToWVM(); }
/// <summary> /// フォーム読込完了時 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private async void ActionListRegistrationWindow_Loaded(object sender, RoutedEventArgs e) { int? bookId = null; BalanceKind balanceKind = BalanceKind.Outgo; int? itemId = null; string shopName = null; string remark = null; // DBから値を読み込む switch (this.WVM.RegMode) { case RegistrationMode.Add: { bookId = this.selectedBookId; balanceKind = BalanceKind.Outgo; DateTime actDate = this.selectedDate ?? ((this.selectedMonth == null || this.selectedMonth?.Month == DateTime.Today.Month) ? DateTime.Today : this.selectedMonth.Value); if (this.selectedRecordList == null) { this.WVM.DateValueVMList.Add(new DateValueViewModel() { ActDate = actDate }); } else { foreach (CsvComparisonViewModel.CsvRecord record in this.selectedRecordList) { this.WVM.DateValueVMList.Add(new DateValueViewModel() { ActDate = record.Date, ActValue = record.Value }); } } } break; case RegistrationMode.Edit: case RegistrationMode.Copy: { using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT book_id, item_id, action_id, act_time, act_value, shop_name, remark FROM hst_action WHERE del_flg = 0 AND group_id = @{0};", this.selectedGroupId); reader.ExecWholeRow((count, record) => { int actionId = -1; DateTime actDate = DateTime.Now; int actValue = -1; actionId = record.ToInt("action_id"); actDate = record.ToDateTime("act_time"); actValue = record.ToInt("act_value"); bookId = record.ToInt("book_id"); itemId = record.ToInt("item_id"); shopName = record["shop_name"]; remark = record["remark"]; DateValueViewModel vm = new DateValueViewModel() { ActionId = actionId, ActDate = actDate, ActValue = Math.Abs(actValue) }; balanceKind = Math.Sign(actValue) > 0 ? BalanceKind.Income : BalanceKind.Outgo; // 収入 / 支出 this.groupedActionIdList.Add(vm.ActionId.Value); this.WVM.DateValueVMList.Add(vm); return(true); }); } } break; } // WVMに値を設定する this.WVM.GroupId = this.WVM.RegMode == RegistrationMode.Edit ? this.selectedGroupId : null; this.WVM.SelectedBalanceKind = balanceKind; // リストを更新する await this.UpdateBookListAsync(bookId); await this.UpdateCategoryListAsync(); await this.UpdateItemListAsync(itemId); await this.UpdateShopListAsync(shopName); await this.UpdateRemarkListAsync(remark); // イベントハンドラを登録する this.RegisterEventHandlerToWVM(); }
/// <summary> /// フォーム読込完了時 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private async void ActionRegistrationWindow_Loaded(object sender, RoutedEventArgs e) { ObservableCollection <BookViewModel> bookVMList = new ObservableCollection <BookViewModel>(); int? bookId = null; BookViewModel selectedBookVM = null; DateTime actDate = DateTime.Now; BalanceKind balanceKind = BalanceKind.Outgo; int? itemId = null; int? actValue = null; bool isMatch = false; string shopName = null; string remark = null; switch (this.WVM.RegMode) { case RegistrationMode.Add: { bookId = this.selectedBookId; balanceKind = BalanceKind.Outgo; if (this.selectedRecord == null) { actDate = this.selectedDate ?? ((this.selectedMonth == null || this.selectedMonth?.Month == DateTime.Today.Month) ? DateTime.Today : this.selectedMonth.Value); } else { actDate = this.selectedRecord.Date; actValue = this.selectedRecord.Value; } } break; case RegistrationMode.Edit: case RegistrationMode.Copy: { using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT book_id, item_id, act_time, act_value, group_id, shop_name, remark, is_match FROM hst_action WHERE del_flg = 0 AND action_id = @{0};", this.selectedActionId); reader.ExecARow((record) => { bookId = record.ToInt("book_id"); itemId = record.ToInt("item_id"); actDate = record.ToDateTime("act_time"); actValue = record.ToInt("act_value"); this.groupId = record.ToNullableInt("group_id"); shopName = record["shop_name"]; remark = record["remark"]; isMatch = record.ToInt("is_match") == 1; }); } balanceKind = Math.Sign(actValue.Value) > 0 ? BalanceKind.Income : BalanceKind.Outgo; // 収入 / 支出 // 回数の表示 int count = 1; if (this.groupId != null) { using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT COUNT(action_id) count FROM hst_action WHERE del_flg = 0 AND group_id = @{0} AND act_time >= (SELECT act_time FROM hst_action WHERE action_id = @{1});", this.groupId, this.selectedActionId); reader.ExecARow((record) => { count = record.ToInt("count"); }); } } this.WVM.Count = count; } break; } using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT book_id, book_name FROM mst_book WHERE del_flg = 0 ORDER BY sort_order;"); reader.ExecWholeRow((count, record) => { BookViewModel vm = new BookViewModel() { Id = record.ToInt("book_id"), Name = record["book_name"] }; bookVMList.Add(vm); if (selectedBookVM == null || bookId == vm.Id) { selectedBookVM = vm; } return(true); }); } this.WVM.BookVMList = bookVMList; this.WVM.SelectedBookVM = selectedBookVM; this.WVM.SelectedBalanceKind = balanceKind; this.WVM.SelectedDate = actDate; this.WVM.Value = actValue.HasValue ? Math.Abs(actValue.Value) : (int?)null; this.WVM.IsMatch = isMatch; await this.UpdateCategoryListAsync(); await this.UpdateItemListAsync(itemId); await this.UpdateShopListAsync(shopName); await this.UpdateRemarkListAsync(remark); #region イベントハンドラの設定 this.WVM.BookChanged += async() => { await this.UpdateCategoryListAsync(); await this.UpdateItemListAsync(); await this.UpdateShopListAsync(); await this.UpdateRemarkListAsync(); }; this.WVM.BalanceKindChanged += async() => { await this.UpdateCategoryListAsync(); await this.UpdateItemListAsync(); await this.UpdateShopListAsync(); await this.UpdateRemarkListAsync(); }; this.WVM.CategoryChanged += async() => { await this.UpdateItemListAsync(); await this.UpdateShopListAsync(); await this.UpdateRemarkListAsync(); }; this.WVM.ItemChanged += async() => { await this.UpdateShopListAsync(); await this.UpdateRemarkListAsync(); }; #endregion }
/// <summary> /// フォーム読込完了時 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private async void ActionListRegistrationWindow_Loaded(object sender, RoutedEventArgs e) { ObservableCollection <BookViewModel> bookVMList = new ObservableCollection <BookViewModel>(); int? bookId = null; BookViewModel selectedBookVM = null; BalanceKind balanceKind = BalanceKind.Outgo; int? itemId = null; string shopName = null; string remark = null; switch (this.WVM.RegMode) { case RegistrationMode.Add: { bookId = this.selectedBookId; balanceKind = BalanceKind.Outgo; DateTime actDate = this.selectedDate ?? ((this.selectedMonth == null || this.selectedMonth?.Month == DateTime.Today.Month) ? DateTime.Today : this.selectedMonth.Value); if (this.selectedRecordList == null) { this.WVM.DateValueVMList.Add(new DateValueViewModel() { ActDate = actDate }); } else { foreach (CsvComparisonViewModel.CsvRecord record in this.selectedRecordList) { this.WVM.DateValueVMList.Add(new DateValueViewModel() { ActDate = record.Date, ActValue = record.Value }); } } } break; case RegistrationMode.Edit: case RegistrationMode.Copy: { using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT book_id, item_id, action_id, act_time, act_value, shop_name, remark FROM hst_action WHERE del_flg = 0 AND group_id = @{0};", this.selectedGroupId); reader.ExecWholeRow((count, record) => { int actionId = -1; DateTime actDate = DateTime.Now; int actValue = -1; actionId = record.ToInt("action_id"); actDate = record.ToDateTime("act_time"); actValue = record.ToInt("act_value"); bookId = record.ToInt("book_id"); itemId = record.ToInt("item_id"); shopName = record["shop_name"]; remark = record["remark"]; DateValueViewModel vm = new DateValueViewModel() { ActionId = actionId, ActDate = actDate, ActValue = Math.Abs(actValue) }; balanceKind = Math.Sign(actValue) > 0 ? BalanceKind.Income : BalanceKind.Outgo; // 収入 / 支出 this.groupedActionIdList.Add(vm.ActionId.Value); this.WVM.DateValueVMList.Add(vm); return(true); }); } } break; } using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT book_id, book_name FROM mst_book WHERE del_flg = 0 ORDER BY sort_order;"); reader.ExecWholeRow((count, record) => { BookViewModel vm = new BookViewModel() { Id = record.ToInt("book_id"), Name = record["book_name"] }; bookVMList.Add(vm); if (selectedBookVM == null || bookId == vm.Id) { selectedBookVM = vm; } return(true); }); } this.WVM.BookVMList = bookVMList; this.WVM.SelectedBookVM = selectedBookVM; this.WVM.SelectedBalanceKind = balanceKind; await this.UpdateCategoryListAsync(); await this.UpdateItemListAsync(itemId); await this.UpdateShopListAsync(shopName); await this.UpdateRemarkListAsync(remark); #region イベントハンドラの設定 this.WVM.BookChanged += async() => { await this.UpdateCategoryListAsync(); await this.UpdateItemListAsync(); await this.UpdateShopListAsync(); await this.UpdateRemarkListAsync(); }; this.WVM.BalanceKindChanged += async() => { await this.UpdateCategoryListAsync(); await this.UpdateItemListAsync(); await this.UpdateShopListAsync(); await this.UpdateRemarkListAsync(); }; this.WVM.CategoryChanged += async() => { await this.UpdateItemListAsync(); await this.UpdateShopListAsync(); await this.UpdateRemarkListAsync(); }; this.WVM.ItemChanged += async() => { await this.UpdateShopListAsync(); await this.UpdateRemarkListAsync(); }; #endregion }
/// <summary> /// フォーム読込完了時 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private async void ActionRegistrationWindow_Loaded(object sender, RoutedEventArgs e) { int? bookId = null; DateTime actDate = DateTime.Now; BalanceKind balanceKind = BalanceKind.Outgo; int? itemId = null; int? actValue = null; bool isMatch = false; string shopName = null; string remark = null; // DBから値を読み込む switch (this.WVM.RegMode) { case RegistrationMode.Add: { bookId = this.selectedBookId; balanceKind = BalanceKind.Outgo; if (this.selectedRecord == null) { actDate = this.selectedDate ?? ((this.selectedMonth == null || this.selectedMonth?.Month == DateTime.Today.Month) ? DateTime.Today : this.selectedMonth.Value); } else { actDate = this.selectedRecord.Date; actValue = this.selectedRecord.Value; } } break; case RegistrationMode.Edit: case RegistrationMode.Copy: { using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT book_id, item_id, act_time, act_value, group_id, shop_name, remark, is_match FROM hst_action WHERE del_flg = 0 AND action_id = @{0};", this.selectedActionId); reader.ExecARow((record) => { bookId = record.ToInt("book_id"); itemId = record.ToInt("item_id"); actDate = record.ToDateTime("act_time"); actValue = record.ToInt("act_value"); this.groupId = record.ToNullableInt("group_id"); shopName = record["shop_name"]; remark = record["remark"]; isMatch = record.ToInt("is_match") == 1; }); } balanceKind = Math.Sign(actValue.Value) > 0 ? BalanceKind.Income : BalanceKind.Outgo; // 収入 / 支出 // 回数の表示 int count = 1; if (this.groupId != null) { using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT COUNT(action_id) count FROM hst_action WHERE del_flg = 0 AND group_id = @{0} AND act_time >= (SELECT act_time FROM hst_action WHERE action_id = @{1});", this.groupId, this.selectedActionId); reader.ExecARow((record) => { count = record.ToInt("count"); }); } } this.WVM.Count = count; } break; } // WVMに値を設定する if (this.WVM.RegMode == RegistrationMode.Edit) { this.WVM.ActionId = this.selectedActionId; this.WVM.GroupId = this.groupId; } this.WVM.SelectedBalanceKind = balanceKind; this.WVM.SelectedDate = actDate; this.WVM.Value = actValue.HasValue ? Math.Abs(actValue.Value) : (int?)null; this.WVM.IsMatch = isMatch; // リストを更新する await this.UpdateBookListAsync(bookId); await this.UpdateCategoryListAsync(); await this.UpdateItemListAsync(itemId); await this.UpdateShopListAsync(shopName); await this.UpdateRemarkListAsync(remark); // イベントハンドラを設定する this.RegisterEventHandlerToWVM(); }
/// <summary> /// DBに登録する /// </summary> /// <returns>登録された帳簿項目IDリスト</returns> private async Task <List <int> > RegisterToDbAsync() { List <int> tmpActionIdList = new List <int>(); BalanceKind balanceKind = this.WVM.SelectedBalanceKind; // 収支種別 int bookId = this.WVM.SelectedBookVM.Id.Value; // 帳簿ID int itemId = this.WVM.SelectedItemVM.Id; // 帳簿項目ID string shopName = this.WVM.SelectedShopName; // 店舗名 string remark = this.WVM.SelectedRemark; // 備考 DateTime lastActTime = this.WVM.DateValueVMList.Max((tmp) => tmp.ActDate); using (DaoBase dao = this.builder.Build()) { switch (this.WVM.RegMode) { case RegistrationMode.Add: { #region 帳簿項目を追加する await dao.ExecTransactionAsync(async() => { int tmpGroupId = -1; // グループIDを取得する DaoReader reader = await dao.ExecQueryAsync(@" INSERT INTO hst_group (group_kind, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, 0, 'now', @{1}, 'now', @{2}) RETURNING group_id;", (int)GroupKind.ListReg, Updater, Inserter); reader.ExecARow((record) => { tmpGroupId = record.ToInt("group_id"); }); foreach (DateValueViewModel vm in this.WVM.DateValueVMList) { if (vm.ActValue.HasValue) { DateTime actTime = vm.ActDate; // 日付 int actValue = (balanceKind == BalanceKind.Income ? 1 : -1) * vm.ActValue.Value; // 金額 reader = await dao.ExecQueryAsync(@" INSERT INTO hst_action (book_id, item_id, act_time, act_value, shop_name, group_id, remark, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, @{2}, @{3}, @{4}, @{5}, @{6}, 0, 'now', @{7}, 'now', @{8}) RETURNING action_id;", bookId, itemId, actTime, actValue, shopName, tmpGroupId, remark, Updater, Inserter); reader.ExecARow((record) => { tmpActionIdList.Add(record.ToInt("action_id")); }); } } }); #endregion } break; case RegistrationMode.Edit: { #region 帳簿項目を編集する await dao.ExecTransactionAsync(async() => { foreach (DateValueViewModel vm in this.WVM.DateValueVMList) { if (vm.ActValue.HasValue) { int?actionId = vm.ActionId; DateTime actTime = vm.ActDate; // 日付 int actValue = (balanceKind == BalanceKind.Income ? 1 : -1) * vm.ActValue.Value; // 金額 if (actionId.HasValue) { await dao.ExecNonQueryAsync(@" UPDATE hst_action SET book_id = @{0}, item_id = @{1}, act_time = @{2}, act_value = @{3}, shop_name = @{4}, remark = @{5}, update_time = 'now', updater = @{6} WHERE action_id = @{7} AND NOT (book_id = @{0} AND item_id = @{1} AND act_time = @{2} AND act_value = @{3} AND shop_name = @{4} AND remark = @{5});", bookId, itemId, actTime, actValue, shopName, remark, Updater, actionId.Value); tmpActionIdList.Add(actionId.Value); } else { DaoReader reader = await dao.ExecQueryAsync(@" INSERT INTO hst_action (book_id, item_id, act_time, act_value, shop_name, group_id, remark, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, @{2}, @{3}, @{4}, @{5}, @{6}, 0, 'now', @{7}, 'now', @{8}) RETURNING action_id;", bookId, itemId, actTime, actValue, shopName, this.selectedGroupId, remark, Updater, Inserter); reader.ExecARow((record) => { tmpActionIdList.Add(record.ToInt("action_id")); }); } } } IEnumerable <int> expected = this.groupedActionIdList.Except(tmpActionIdList); foreach (int actionId in expected) { await dao.ExecNonQueryAsync(@" UPDATE hst_action SET del_flg = 1, update_time = 'now', updater = @{1} WHERE action_id = @{0};", actionId, Updater); } }); #endregion } break; } if (shopName != string.Empty) { #region 店舗を追加する await dao.ExecTransactionAsync(async() => { DaoReader reader = await dao.ExecQueryAsync(@" SELECT shop_name FROM hst_shop WHERE item_id = @{0} AND shop_name = @{1};", itemId, shopName); if (reader.Count == 0) { await dao.ExecNonQueryAsync(@" INSERT INTO hst_shop (item_id, shop_name, used_time, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, @{2}, 0, 'now', @{3}, 'now', @{4});", itemId, shopName, lastActTime, Updater, Inserter); } else { await dao.ExecNonQueryAsync(@" UPDATE hst_shop SET used_time = @{0}, del_flg = 0, update_time = 'now', updater = @{1} WHERE item_id = @{2} AND shop_name = @{3} AND used_time < @{0};", lastActTime, Updater, itemId, shopName); } }); #endregion } if (remark != string.Empty) { #region 備考を追加する await dao.ExecTransactionAsync(async() => { DaoReader reader = await dao.ExecQueryAsync(@" SELECT remark FROM hst_remark WHERE item_id = @{0} AND remark = @{1};", itemId, remark); if (reader.Count == 0) { await dao.ExecNonQueryAsync(@" INSERT INTO hst_remark (item_id, remark, remark_kind, used_time, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, 0, @{2}, 0, 'now', @{3}, 'now', @{4});", itemId, remark, lastActTime, Updater, Inserter); } else { await dao.ExecNonQueryAsync(@" UPDATE hst_remark SET used_time = @{0}, del_flg = 0, update_time = 'now', updater = @{1} WHERE item_id = @{2} AND remark = @{3} AND used_time < @{0};", lastActTime, Updater, itemId, remark); } }); #endregion } } return(tmpActionIdList); }
/// <summary> /// DBに登録する /// </summary> /// <returns>登録された帳簿項目ID</returns> private async Task <int?> RegisterToDbAsync() { DateTime fromDate = this.WVM.FromDate; DateTime toDate = this.WVM.ToDate; int fromBookId = this.WVM.SelectedFromBookVM.Id.Value; int toBookId = this.WVM.SelectedToBookVM.Id.Value; int actValue = this.WVM.Value.Value; CommissionKind commissionKind = this.WVM.SelectedCommissionKind; int commissionItemId = this.WVM.SelectedItemVM.Id; int commission = this.WVM.Commission ?? 0; string remark = this.WVM.SelectedRemark; int?resActionId = null; int tmpGroupId = -1; // ローカル用 using (DaoBase dao = this.builder.Build()) { await dao.ExecTransactionAsync(async() => { if (this.groupId == null) // 追加 { #region 帳簿項目を追加する // グループIDを取得する DaoReader reader = await dao.ExecQueryAsync(@" INSERT INTO hst_group (group_kind, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, 0, 'now', @{1}, 'now', @{2}) RETURNING group_id;", (int)GroupKind.Move, Updater, Inserter); reader.ExecARow((record) => { tmpGroupId = record.ToInt("group_id"); }); reader = await dao.ExecQueryAsync(@" -- 移動元 INSERT INTO hst_action (book_id, item_id, act_time, act_value, group_id, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, ( SELECT item_id FROM mst_item I INNER JOIN (SELECT * FROM mst_category WHERE balance_kind = @{6}) C ON C.category_id = I.category_id WHERE move_flg = 1 ), @{1}, @{2}, @{3}, 0, 'now', @{4}, 'now', @{5}) RETURNING action_id;", fromBookId, fromDate, -actValue, tmpGroupId, Updater, Inserter, (int)BalanceKind.Outgo); if (this.selectedBookId == fromBookId) { reader.ExecARow((record) => { resActionId = record.ToInt("action_id"); }); } reader = await dao.ExecQueryAsync(@" -- 移動先 INSERT INTO hst_action (book_id, item_id, act_time, act_value, group_id, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, ( SELECT item_id FROM mst_item I INNER JOIN (SELECT * FROM mst_category WHERE balance_kind = @{6}) C ON C.category_id = I.category_id WHERE move_flg = 1 ), @{1}, @{2}, @{3}, 0, 'now', @{4}, 'now', @{5}) RETURNING action_id;", toBookId, toDate, actValue, tmpGroupId, Updater, Inserter, (int)BalanceKind.Income); if (this.selectedBookId == toBookId) { reader.ExecARow((record) => { resActionId = record.ToInt("action_id"); }); } #endregion } else // 編集 { #region 帳簿項目を編集する tmpGroupId = this.groupId.Value; await dao.ExecNonQueryAsync(@" -- 移動元 UPDATE hst_action SET book_id = @{0}, act_time = @{1}, act_value = @{2}, update_time = 'now', updater = @{3} WHERE action_id = @{4};", fromBookId, fromDate, -actValue, Updater, this.fromActionId); if (this.selectedBookId == fromBookId) { resActionId = this.fromActionId; } await dao.ExecNonQueryAsync(@" -- 移動先 UPDATE hst_action SET book_id = @{0}, act_time = @{1}, act_value = @{2}, update_time = 'now', updater = @{3} WHERE action_id = @{4};", toBookId, toDate, actValue, Updater, this.toActionId); if (this.selectedBookId == toBookId) { resActionId = this.toActionId; } #endregion } if (commission != 0) { #region 手数料あり int bookId = -1; DateTime actTime = DateTime.Now; switch (commissionKind) { case CommissionKind.FromBook: bookId = fromBookId; actTime = fromDate; break; case CommissionKind.ToBook: bookId = toBookId; actTime = toDate; break; } if (this.commissionActionId != null) { await dao.ExecNonQueryAsync(@" UPDATE hst_action SET book_id = @{0}, item_id = @{1}, act_time = @{2}, act_value = @{3}, remark = @{4}, update_time = 'now', updater = @{5} WHERE action_id = @{6};", bookId, commissionItemId, actTime, -commission, remark, Updater, this.commissionActionId); } else { await dao.ExecNonQueryAsync(@" INSERT INTO hst_action (book_id, item_id, act_time, act_value, group_id, remark, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, @{2}, @{3}, @{4}, @{5}, 0, 'now', @{6}, 'now', @{7});", bookId, commissionItemId, actTime, -commission, tmpGroupId, remark, Updater, Inserter); } #endregion } else { #region 手数料なし if (this.commissionActionId != null) { await dao.ExecNonQueryAsync(@" UPDATE hst_action SET del_flg = 1, update_time = 'now', updater = @{0} WHERE action_id = @{1};", Updater, this.commissionActionId); } #endregion } }); if (remark != string.Empty) { #region 備考を追加する DaoReader reader = await dao.ExecQueryAsync(@" SELECT remark FROM hst_remark WHERE item_id = @{0} AND remark = @{1};", commissionItemId, remark); if (reader.Count == 0) { await dao.ExecNonQueryAsync(@" INSERT INTO hst_remark (item_id, remark, remark_kind, used_time, del_flg, update_time, updater, insert_time, inserter) VALUES (@{0}, @{1}, 0, @{2}, 0, 'now', @{3}, 'now', @{4});", commissionItemId, remark, fromDate > toDate?fromDate : toDate, Updater, Inserter); } else { await dao.ExecNonQueryAsync(@" UPDATE hst_remark SET used_time = @{0}, del_flg = 0, update_time = 'now', updater = @{1} WHERE item_id = @{2} AND remark = @{3} AND used_time < @{0};", fromDate > toDate?fromDate : toDate, Updater, commissionItemId, remark); } #endregion } return(resActionId); } }
/// <summary> /// フォーム読込完了時 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private async void MoveRegistrationWindow_Loaded(object sender, RoutedEventArgs e) { ObservableCollection <BookViewModel> bookVMList = new ObservableCollection <BookViewModel>(); int? movedBookId = this.selectedBookId; int? movingBookId = this.selectedBookId; BookViewModel movedBookVM = null; BookViewModel movingBookVM = null; int? commissionItemId = null; string commissionRemark = null; switch (this.WVM.RegMode) { case RegistrationMode.Edit: case RegistrationMode.Copy: { DateTime movedDate = DateTime.Now; DateTime movingDate = DateTime.Now; CommissionKind commissionKind = CommissionKind.FromBook; int moveValue = -1; int commissionValue = 0; using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT A.book_id, A.action_id, A.item_id, A.act_time, A.act_value, A.remark, I.move_flg FROM hst_action A INNER JOIN (SELECT * FROM mst_item WHERE del_flg = 0) I ON I.item_id = A.item_id WHERE A.del_flg = 0 AND A.group_id = @{0} ORDER BY move_flg DESC;", this.groupId); reader.ExecWholeRow((count, record) => { int bookId = record.ToInt("book_id"); DateTime dateTime = record.ToDateTime("act_time"); int actionId = record.ToInt("action_id"); int itemId = record.ToInt("item_id"); int actValue = record.ToInt("act_value"); int moveFlg = record.ToInt("move_flg"); string remark = record["remark"]; if (moveFlg == 1) { if (actValue < 0) { movedBookId = bookId; movedDate = dateTime; this.fromActionId = actionId; } else { movingBookId = bookId; movingDate = dateTime; this.toActionId = actionId; moveValue = actValue; } } else // 手数料 { if (bookId == movedBookId) // 移動元負担 { commissionKind = CommissionKind.FromBook; } else if (bookId == movingBookId) // 移動先負担 { commissionKind = CommissionKind.ToBook; } this.commissionActionId = actionId; commissionItemId = itemId; commissionValue = Math.Abs(actValue); commissionRemark = remark; } return(true); }); } this.WVM.IsLink = (movedDate == movingDate); this.WVM.MovedDate = movedDate; this.WVM.MovingDate = movingDate; this.WVM.SelectedCommissionKind = commissionKind; this.WVM.Value = moveValue; this.WVM.Commission = commissionValue; } break; } int?debitBookId = null; int?payDay = null; using (DaoBase dao = this.builder.Build()) { DaoReader reader = await dao.ExecQueryAsync(@" SELECT book_id, book_name, book_kind, debit_book_id, pay_day FROM mst_book WHERE del_flg = 0 ORDER BY sort_order;"); reader.ExecWholeRow((count, record) => { BookViewModel vm = new BookViewModel() { Id = record.ToInt("book_id"), Name = record["book_name"] }; bookVMList.Add(vm); if (movedBookVM == null || movedBookId == vm.Id) { movedBookVM = vm; switch (this.WVM.RegMode) { case RegistrationMode.Add: { if (record.ToInt("book_kind") == (int)BookKind.CreditCard) { debitBookId = record.ToNullableInt("debit_book_id"); payDay = record.ToNullableInt("pay_day"); } } break; } ; } if (movingBookVM == null || movingBookId == vm.Id) { movingBookVM = vm; } return(true); }); } this.WVM.BookVMList = bookVMList; this.WVM.SelectedMovedBookVM = movedBookVM; this.WVM.SelectedMovingBookVM = movingBookVM; switch (this.WVM.RegMode) { case RegistrationMode.Add: { if (debitBookId != null) { this.WVM.SelectedMovedBookVM = bookVMList.FirstOrDefault((vm) => { return(vm.Id == debitBookId); }); } this.WVM.MovedDate = this.selectedDate ?? ((this.selectedMonth == null || this.selectedMonth?.Month == DateTime.Today.Month) ? DateTime.Today : this.selectedMonth.Value); if (payDay != null) { this.WVM.MovedDate = this.WVM.MovedDate.GetDateInMonth(payDay.Value); } this.WVM.IsLink = true; this.WVM.SelectedCommissionKind = CommissionKind.FromBook; } break; } ; await this.UpdateItemListAsync(commissionItemId); await this.UpdateRemarkListAsync(commissionRemark); #region イベントハンドラの設定 this.WVM.FromBookChanged += async() => { await this.UpdateItemListAsync(); await this.UpdateRemarkListAsync(); }; this.WVM.ToBookChanged += async() => { await this.UpdateItemListAsync(); await this.UpdateRemarkListAsync(); }; this.WVM.CommissionKindChanged += async() => { await this.UpdateItemListAsync(); await this.UpdateRemarkListAsync(); }; this.WVM.ItemChanged += async() => { await this.UpdateRemarkListAsync(); }; #endregion }