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