public static Dictionary <string, CompanyName> GetMappings(int keyColumn)
{
var namesMapping = new Dictionary <string, CompanyName>();
using (var mappingWorkbook = ReportFile.GetWorkbook(ReportFile.CompanyMapping))
{
var mappingSheet = mappingWorkbook.Workbook.Worksheets[0];
int row = 2;
int colKey = keyColumn;
int colCF = 1;
int colKorab2 = 2;
int colDailyFlow = 3;
int colNetting = 4;
int colInflows = 5;
while (mappingSheet.Cells[row, colKey].Value != null)
{
if (!namesMapping.ContainsKey(mappingSheet.Cells[row, colKey].GetNotNullString().ToUpper()))
{
namesMapping.Add(mappingSheet.Cells[row, colKey].GetNotNullString().ToUpper(),
new CompanyName
{
CFName = mappingSheet.Cells[row, colCF].GetNotNullString().ToUpper(),
Korab2Name = mappingSheet.Cells[row, colKorab2].GetNotNullString().ToUpper(),
DailyFlowName = mappingSheet.Cells[row, colDailyFlow].GetNotNullString().ToUpper(),
NettingName = mappingSheet.Cells[row, colNetting].GetNotNullString().ToUpper(),
InflowsName = mappingSheet.Cells[row, colInflows].GetNotNullString().ToUpper()
});
}
row++;
}
}
return(namesMapping);
}
public static void InsertDataSEB(ExcelWorksheet destSheet, string nameBank)
{
ExcelWorksheet srcSEBSheet = ReportFile.GetWorkbookCsv(ReportFile.OpeningBalanceSEB, MasterData.reportDate, nameBank);
// blokuję, bo wykorzystuję do testów
KK_TestDict.DisplayMappingsInSheet(MasterData.reportWorkbook);
int colAccount = 2;
int colCurrency = 3;
int colDate = 5;
int colValueToInsert = 9;
int colDestination = 5;
//foreach (KeyValuePair<string, CompanyBankAccount> kvp in MasterData.cfReportLines)
foreach (var kvp in MasterData.cfReportLines)
{
if (kvp.Value.Bank.Contains(nameBank))
{
for (int row = srcSEBSheet.Dimension.Start.Row + 1; row <= srcSEBSheet.Dimension.End.Row; row++)
{
if (srcSEBSheet.Cells[row, colAccount].Value != null)
{
var _bookingDate = DateTime.Parse(srcSEBSheet.Cells[row, colDate].Value.ToString());
if (srcSEBSheet.Cells[row, colAccount].GetNotNullString().Replace(" ", "") == kvp.Value.AccountNumber.Replace(" ", "") &&
srcSEBSheet.Cells[row, colCurrency].GetNotNullString() == kvp.Value.Currency &&
_bookingDate.ToString("dd.MM.yyyy") == MasterData.previousReportDate.ToString("dd.MM.yyyy")
)
{
double _amount;
var _tmpAmount = srcSEBSheet.Cells[row, colValueToInsert].Value;
if (_tmpAmount != null)
{
Double.TryParse(_tmpAmount.ToString(), out _amount);
destSheet.Cells[kvp.Value.RowInCfReport, colDestination].Value = _amount;
}
else
{
destSheet.Cells[kvp.Value.RowInCfReport, colDestination].Value = 0.0;
}
break;
}
}
}
}
}
}
//TODO V2 Konieczny refaktor
public static void RunCfRobot()
{
bool processStageStatus;
string processMessage = string.Empty;
var robotWatch = Stopwatch.StartNew();
Console.WriteLine($"Starting process for CashFlow report\n");
// 2019-08-05 KK trzeba tu przenieść MasterData.reportDate, bo jest wykorzystywany jako część nazwy pliku .csv
// 2019-08-07 sprawdziłem, że nie ma znaczenia wielkość liter przy rozszerzeniu pliku .CSV czy .csv
MasterData.reportDate = "09.08.2019";
MasterData.previousReportDate = Utils.PreviousDate(DateTime.Parse(MasterData.reportDate));
//KK pozyskanie włściwych nazw plików .csv
Utils.FilesFromCsv();
Console.WriteLine($"Step 1 - Checking the required files ....");
var processWatch = Stopwatch.StartNew();
processStageStatus = ReportFile.CheckIfCFFilesReady(out processMessage);
if (processStageStatus)
{
MasterData.reportWorkbook = ReportFile.GetWorkbook(ReportFile.CFReport);
}
processWatch.Stop();
Console.WriteLine($"\tElapsed: {TimeSpan.FromMilliseconds(processWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine($"\tResult: {processMessage}\n");
if (!processStageStatus)
{
return;
}
Console.WriteLine($"Step 2 - Configuring report worksheet....");
processWatch = Stopwatch.StartNew();
processStageStatus = CanCreateReportSheet();
if (processStageStatus)
{
ReportFile.GenerateLogSheets();
MasterData.reportSheet = MasterData.CreateFromTemplate();
// KK dodałem wyświetlenie, jaki arkusz został utworzony
processMessage = $"Success! Report worksheet {MasterData.reportDate} created.";
}
else
{
processMessage = "Aborted by user!";
}
processWatch.Stop();
Console.WriteLine($"\tElapsed: {TimeSpan.FromMilliseconds(processWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine($"\tResult: {processMessage}\n");
if (!processStageStatus)
{
return;
}
Console.WriteLine($"Step 3 - Initializing mappings....");
processWatch = Stopwatch.StartNew();
MasterData.InitializeMasterdata();
processMessage = "Success! Mappings ready.";
processWatch.Stop();
Console.WriteLine($"\tElapsed: {TimeSpan.FromMilliseconds(processWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine($"\tResult: {processMessage}\n");
Console.WriteLine($"Step 4 - Reading Opening balances ING PL....");
processWatch = Stopwatch.StartNew();
var obSheet = ReportFile.GetWorkbook(ReportFile.OpeningBalanceINGPL).Workbook.Worksheets[0];
OpeningBalance.InsertOpeningBalanceING(MasterData.reportSheet, obSheet, "ING CP");
obSheet = ReportFile.GetWorkbook(ReportFile.OpeningBalanceTFI).Workbook.Worksheets[0];
OpeningBalance.InsertOpeningBalanceTFI(MasterData.reportSheet, obSheet, "ING CP");
obSheet = ReportFile.GetWorkbook(ReportFile.OpeningBalanceFizan).Workbook.Worksheets[0];
OpeningBalance.InsertOpeningBalanceFizan(MasterData.reportSheet, obSheet, "ING CP");
processWatch.Stop();
processMessage = "Success! Opening balances loaded.";
Console.WriteLine($"\tElapsed: {TimeSpan.FromMilliseconds(processWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine($"\tResult: {processMessage}\n");
Console.WriteLine($"Step 5 - Reading Split Payment....");
processWatch = Stopwatch.StartNew();
var splitSheet = ReportFile.GetWorkbook(ReportFile.SplitPaymentINGPL).Workbook.Worksheets[0];
OpeningBalaceSplitING.InsertOpeningBalanceSplitING(MasterData.reportSheet, splitSheet, "SPLIT");
OpeningBalaceSplitING.InsertOpeningBalanceSplitINGEscrow(MasterData.reportSheet, splitSheet, "Escrow SPLIT");
processWatch.Stop();
processMessage = "Success! Split payment opening balances loaded.";
Console.WriteLine($"\tElapsed: {TimeSpan.FromMilliseconds(processWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine($"\tResult: {processMessage}\n");
Console.WriteLine($"Step 6 - Reading Outflows / Inflows....");
processWatch = Stopwatch.StartNew();
var outflowInflowSheet = ReportFile.GetWorkbook(ReportFile.OutflowsInflowsINGSPP).Workbook.Worksheets[0];
Outflow.InsertOutflows(MasterData.reportSheet, outflowInflowSheet, "ING CP");
Inflow.InsertInflowsING(MasterData.reportSheet, outflowInflowSheet, "ING CP");
outflowInflowSheet = ReportFile.GetWorkbook(ReportFile.OutflowsInflowsINGCDE).Workbook.Worksheets[0];
Outflow.InsertOutflows(MasterData.reportSheet, outflowInflowSheet, "ING CP");
Inflow.InsertInflowsING(MasterData.reportSheet, outflowInflowSheet, "ING CP");
outflowInflowSheet = ReportFile.GetWorkbook(ReportFile.OutflowsInflowsINGTFI).Workbook.Worksheets[0];
Outflow.InsertOutflows(MasterData.reportSheet, outflowInflowSheet, "ING N");
//TODO V1 Do przemyślenia kodowanie bankaccount bo opieranie sie na jednym (ING N / ING CP) nie starcza.
Inflow.InsertInflowsING(MasterData.reportSheet, outflowInflowSheet, "ING CP");
processWatch.Stop();
processMessage = "Success! Outflows and inflows loaded.";
Console.WriteLine($"\tElapsed: {TimeSpan.FromMilliseconds(processWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine($"\tResult: {processMessage}\n");
Console.WriteLine($"Step 7 - Reading daily limits....");
processWatch = Stopwatch.StartNew();
var dailyLimitsSheet = ReportFile.GetWorkbook(ReportFile.DailyLimitINGPLEUR).Workbook.Worksheets[0];
DailyLimit.InsertDailyLimitING(MasterData.reportSheet, dailyLimitsSheet, "ING CP");
dailyLimitsSheet = ReportFile.GetWorkbook(ReportFile.DailyLimitINGPLPLN).Workbook.Worksheets[0];
DailyLimit.InsertDailyLimitING(MasterData.reportSheet, dailyLimitsSheet, "ING CP");
processWatch.Stop();
processMessage = "Success! Daily limits loaded.";
Console.WriteLine($"\tElapsed: {TimeSpan.FromMilliseconds(processWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine($"\tResult: {processMessage}\n");
Console.WriteLine($"Step 8 - Reading SEB data....");
processWatch = Stopwatch.StartNew();
OpeningBalanceSEB.InsertDataSEB(MasterData.reportSheet, "SEB");
// TODO wykonać jeszcze procedurę dla SEB, gdzie jeżeli w kolumnie N są kwoty,
// to należy je wpisać do OutFlow i InFlow do arkusza z dnia poprzedniego
processWatch.Stop();
processMessage = "Success! Data from SEB loaded.";
Console.WriteLine($"\tElapsed: {TimeSpan.FromMilliseconds(processWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine($"\tResult: {processMessage}\n");
Console.WriteLine($"Step 9 - Reading Santander data....");
processWatch = Stopwatch.StartNew();
OpeningBalanceSantander.InsertDataSantander(MasterData.reportSheet, "Santander");
// TODO wykonać jeszcze procedurę dla Santander, gdzie jeżeli w kolumnie H są kwoty,
// to należy je wpisać do OutFlow i InFlow do arkusza z dnia poprzedniego
processWatch.Stop();
processMessage = "Success! Data from Santander loaded.";
Console.WriteLine($"\tElapsed: {TimeSpan.FromMilliseconds(processWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine($"\tResult: {processMessage}\n");
Console.WriteLine($"Step 10 - Reading ING BV data....");
processWatch = Stopwatch.StartNew();
OpeningBalanceINGBV.InsertDataINGBV(MasterData.reportSheet, "ING BV");
// TODO wykonać jeszcze procedurę dla Santander, gdzie jeżeli w kolumnie H są kwoty,
// to należy je wpisać do OutFlow i InFlow do arkusza z dnia poprzedniego
processWatch.Stop();
processMessage = "Success! Data from ING BV loaded.";
Console.WriteLine($"\tElapsed: {TimeSpan.FromMilliseconds(processWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine($"\tResult: {processMessage}\n");
MasterData.reportWorkbook.Save();
Console.WriteLine($"\tTotal ROBOT execution time: {TimeSpan.FromMilliseconds(robotWatch.ElapsedMilliseconds).TotalSeconds} sek.");
Console.WriteLine("Press any key to finish.");
}