private async Task ProcessImportToBankAsync()
{
int updates;
Log($"Start ProcessImportToBank() with {CheckMissedTallies()} missed tallies");
DictImportToBank();
using (BankingDbContext db = new BankingDbContext(Options.DbConnection))
{
foreach (var item in ImportToBank)
{
try
{
updates = await db.Database.ExecuteSqlCommandAsync(item.Value);
Log(item.Key, updates);
}
catch (Exception ex)
{
Log($"Error in SQL '{item.Key}' with [{ex}]");
}
}
}
ImportToBank.Clear();
await MainVM.GetAccountSummaryAsync();
MissedTalliesCount = CheckMissedTallies();
Log($"After ProcessImportToBank() has {MissedTalliesCount} missed tallies");
}
private void DictImportToBank()
{
ImportToBank.Add("Import to Bank", @"
INSERT INTO Bank(I.[Account], I.[Date], I.[Mutation], I.[Amount], I.[Name], I.[CounterAccount], I.[Text], I.[RawText])
SELECT I.[Account], I.[Date], I.[Mutation], I.[Amount], I.[Name], I.[CounterAccount], I.[Text], I.[RawText]
FROM Import AS I
LEFT JOIN Bank AS B
ON I.[Account] = B.[Account]
AND I.[Date] = B.[Date]
AND I.[Amount] = B.[Amount]
AND I.[RawText] = B.[RawText]
WHERE B.[Id] IS NULL
ORDER BY I.[Date], I.[Account];
");
ImportToBank.Add("Month", @"
UPDATE Bank
SET [Month] = CAST(YEAR([Date]) AS varchar(4)) + '-' + RIGHT('0' + CAST(MONTH([Date]) AS varchar(2)), 2)
FROM Bank
WHERE [Month] IS NULL
");
}
