//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.");
}
public static void InsertOutflows(ExcelWorksheet destSheet, ExcelWorksheet srcSheet, string accountBank)
{
int colKey = 5;
int colTitle = 2;
int colGross = 6;
int colVAT = 7;
int colCurrency = 8;
int colSplit = 13;
int colOutflowInDest = 6;
int colOpeningBalanceInDest = 5;
for (int row = 1; row <= srcSheet.Dimension.End.Row; row++)
{
if (srcSheet.Cells[row, colKey].Value != null)
{
if (CompanyNamesMapping.K2AsKey.ContainsKey(srcSheet.Cells[row, colKey].Value.ToString().ToUpper()))
{
string lineKey = MasterData.GetCfLineKey(accountBank,
srcSheet.Cells[row, colCurrency].GetNotNullString(),
CompanyNamesMapping.K2AsKey[srcSheet.Cells[row, colKey].GetNotNullString().ToUpper()].CFName);
if (MasterData.cfReportLines.ContainsKey(lineKey))
{
float nonSplitValueToInsert;
float splitValueToInsert;
float splitAccumulated;
int rowToInsert = MasterData.cfReportLines[lineKey].RowInCfReport;
int rowToInsertSplit = rowToInsert + 1;
while (destSheet.Cells[rowToInsertSplit, colTitle].GetNotNullString().ToUpper() != "PLN SPLIT")
{
rowToInsertSplit++;
}
if (IsOutflowSplit(srcSheet.Cells[row, colSplit].GetNotNullString(),
srcSheet.Cells[row, colGross].GetNotNullFloat(),
srcSheet.Cells[row, colVAT].GetNotNullFloat(),
srcSheet.Cells[row, colTitle].GetNotNullString()))
{
splitAccumulated = -1 * destSheet.Cells[rowToInsertSplit, colOutflowInDest].GetNotNullFloat();
splitValueToInsert = CalculateSplitOutflow(srcSheet.Cells[row, colVAT].GetNotNullFloat(),
splitAccumulated,
destSheet.Cells[rowToInsertSplit, colOpeningBalanceInDest].GetNotNullFloat());
nonSplitValueToInsert = srcSheet.Cells[row, colGross].GetNotNullFloat() - splitValueToInsert;
}
else
{
splitAccumulated = 0f;
splitValueToInsert = 0f;
nonSplitValueToInsert = srcSheet.Cells[row, colGross].GetNotNullFloat();
}
if (splitValueToInsert != 0)
{
destSheet.Cells[rowToInsertSplit, colOutflowInDest].Value = -1 * splitValueToInsert
+ destSheet.Cells[rowToInsertSplit, colOutflowInDest].GetNotNullFloat();
}
if (nonSplitValueToInsert != 0)
{
destSheet.Cells[rowToInsert, colOutflowInDest].Value = -1 * nonSplitValueToInsert
+ destSheet.Cells[rowToInsert, colOutflowInDest].GetNotNullFloat();
}
/*if (ReportFile.SplitTest && rowToInsert==19) LogsDisplay.SplitLogsInsert(rowToInsert,
* srcSheet.Cells[row, colTitle].GetNotNullString(),
* srcSheet.Cells[row, colSplit].GetNotNullString(),
* srcSheet.Cells[row, colGross].GetNotNullFloat(),
* srcSheet.Cells[row, colVAT].GetNotNullFloat(),
* destSheet.Cells[rowToInsertSplit, colOpeningBalanceInDest].GetNotNullFloat(),
* splitAccumulated,
* splitValueToInsert,
* nonSplitValueToInsert
* ); */
}
//TODO V2 Obsłużyć inne waluty niż EUR i PLN (zamiana na PLN)
else
{
//TODO V3 Info do usera, że nie ma mapowania
}
}
}
}
}
