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