public static void InsertInflowsING(ExcelWorksheet destSheet, ExcelWorksheet srcSheet, string accountBank)
{
int colKey = 12;
int colGross = 6;
int colCurrency = 8;
int colInflowInDest = 7;
for (int row = 1; row <= srcSheet.Dimension.End.Row; row++)
{
if (srcSheet.Cells[row, colKey].Value != null)
{
if (CompanyNamesMapping.InflowsAsKey.ContainsKey(srcSheet.Cells[row, colKey].Value.ToString().ToUpper()))
{
string lineKey = MasterData.GetCfLineKey(accountBank,
srcSheet.Cells[row, colCurrency].GetNotNullString(),
CompanyNamesMapping.InflowsAsKey[srcSheet.Cells[row, colKey].GetNotNullString().ToUpper()].CFName);
if (MasterData.cfReportLines.ContainsKey(lineKey))
{
float inflowToInsert = srcSheet.Cells[row, colGross].GetNotNullFloat();
int rowToInsert = MasterData.cfReportLines[lineKey].RowInCfReport;
if (inflowToInsert != 0)
{
destSheet.Cells[rowToInsert, colInflowInDest].Value = inflowToInsert + destSheet.Cells[rowToInsert, colInflowInDest].GetNotNullFloat();
}
}
}
}
}
}
public static void InsertOpeningBalanceING(ExcelWorksheet destSheet, ExcelWorksheet srcSheet, string accountBank)
{
int colKey = 2;
//int colDate = 4;
int colDescription = 6;
int colValueToInsert = 3;
int colCurrency = 4;
int colDestination = 5;
for (int row = 1; row <= srcSheet.Dimension.End.Row; row++)
{
if (srcSheet.Cells[row, colKey].Value != null &&
CompanyNamesMapping.DailyAsKey.ContainsKey(srcSheet.Cells[row, colKey].Value.ToString().ToUpper())
//&& Utils.CompareStringDates(srcSheet.Cells[row, colDate].Value.ToString(), MasterData.reportDate) //Plik jest robiony na datę, nie potrzebujemy tego warunku.
&& srcSheet.Cells[row, colDescription].Value.ToString().ToUpper().Contains("TRANSFER ODWROTNY")
)
{
string lineKey = MasterData.GetCfLineKey(accountBank, srcSheet.Cells[row, colCurrency].Value.ToString(), CompanyNamesMapping.DailyAsKey[srcSheet.Cells[row, colKey].Value.ToString()].CFName);
if (MasterData.cfReportLines.ContainsKey(lineKey))
{
destSheet.Cells[MasterData.cfReportLines[lineKey].RowInCfReport, colDestination].Value = srcSheet.Cells[row, colValueToInsert].Value;
//TODO V3 Obsłużyć błędne wartości w parse
//TODO V2 Dodać metodę wstawiającą dla wszystkich plików (przekazujemy arkusz, kolumnę i dane)
}
else
{
//TODO V3 Info do usera, że nie ma mapowania
}
}
}
// KK moja wersja wypełnienie z OpeningBalanceINGPL kolumna E raportu
// wykonanie tej wersji trwa dłużej, gdy chodzimy po arkuszu destSheet
//OpeningBalanceINGDataProcessor.INGProcessData(MasterData.reportDate, destSheet, srcSheet);
}
public static void InsertOpeningBalanceSplitING(ExcelWorksheet destSheet, ExcelWorksheet srcSheet, string accountBank)
{
for (int row = 1; row <= srcSheet.Dimension.End.Row; row++)
{
if (srcSheet.Cells[row, 2].Value != null &&
srcSheet.Cells[row, 8].GetNotNullString() == "Rachunek VAT" &&
CompanyNamesMapping.DailyAsKey.ContainsKey(srcSheet.Cells[row, 2].Value.ToString().ToUpper())
//&& Utils.CompareStringDates(srcSheet.Cells[row, 1].Value.ToString(), MasterData.reportDate)
)
{
string lineKey = MasterData.GetCfLineKey(accountBank,
srcSheet.Cells[row, 9].GetNotNullString(),
CompanyNamesMapping.DailyAsKey[srcSheet.Cells[row, 2].GetNotNullString()].CFName);
if (MasterData.cfReportLines.ContainsKey(lineKey))
{
destSheet.Cells[MasterData.cfReportLines[lineKey].RowInCfReport, 5].Value = srcSheet.Cells[row, 6].GetNotNullFloat();
//TODO V2 Dodać metodę wstawiającą dla wszystkich plików (przekazujemy arkusz, kolumnę i dane)
}
else
{
//TODO V3 Info do usera, że nie ma mapowania
}
}
}
for (int row = 1; row <= destSheet.Dimension.End.Row; row++)
{
if (destSheet.Cells[row, 2].GetNotNullString().ToUpper() == "PLN SPLIT" &&
destSheet.Cells[row, 5].GetNotNullFloat() == 0)
{
destSheet.Cells[row, 5].Value = 0;
}
}
}
public static void InsertDailyLimitING(ExcelWorksheet destSheet, ExcelWorksheet srcSheet, string accountBank)
{
int colKey = 4;
int colDate = 1;
int colValueToInsert = 2;
int colCurrency = 3;
int colDestination = 10;
for (int row = 1; row <= srcSheet.Dimension.End.Row; row++)
{
if (srcSheet.Cells[row, colKey].Value != null &&
CompanyNamesMapping.DailyAsKey.ContainsKey(srcSheet.Cells[row, colKey].Value.ToString().ToUpper()) &&
Utils.CompareStringDates(srcSheet.Cells[row, colDate].GetNotNullString(), MasterData.reportDate))
{
string lineKey = MasterData.GetCfLineKey(accountBank, srcSheet.Cells[row, colCurrency].Value.ToString(),
CompanyNamesMapping.DailyAsKey[srcSheet.Cells[row, colKey].Value.ToString().ToUpper()].CFName);
if (MasterData.cfReportLines.ContainsKey(lineKey))
{
destSheet.Cells[MasterData.cfReportLines[lineKey].RowInCfReport, colDestination].Value = srcSheet.Cells[row, colValueToInsert].Value;
}
else
{
//TODO V3 Info do usera, że nie ma mapowania
}
}
}
}
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
}
}
}
}
}