/// <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>
/// 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>
/// 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>
/// フォーム読込完了時
/// </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>
/// フォーム読込完了時
/// </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
}