private void AddErroredWhileReadingInputCompanies(Worksheet worksheet, int headerRow, int lastColumn, int nipColumn, int lpColumn, List <InputCompany> verifiedCompanies)
{
int inputErrorColumn = lastColumn + InputErrorColumnDelta;
((Range)worksheet.Cells[headerRow, inputErrorColumn]).Formula = _exportColumnsConfig.First(c => c.ID == ExportColumnName.ImportFileError.ToString()).HeaderText;
foreach (var company in verifiedCompanies)
{
string nipFromCell = ((Range)worksheet.Cells[company.RowNumber, nipColumn]).Formula.ToString().Trim();
string lpFromCell = ((Range)worksheet.Cells[company.RowNumber, lpColumn]).Formula.ToString().Trim();
if (company.ID != InputCompany.GetID(company.RowNumber, lpFromCell, nipFromCell))
{
throw new ArgumentException($"Nip wczytany z pliku różny od nipu z pliku wyjściowego. Błąd przy zapisie wyniku importu danych z pliku. Z pliku wejściowego: NIP={company.NIP}, LP={company.LP}; z pliku wyjściowego: NIP={nipFromCell}, LP={lpFromCell}.");
}
if (company.FormatErrors != null && company.FormatErrors.Count > 0)
{
StringBuilder sB = new StringBuilder();
foreach (var error in company.FormatErrors)
{
sB.Append($"{error.ToMessage()} ");
}
((Range)worksheet.Cells[company.RowNumber, inputErrorColumn]).Formula = sB.ToString().Trim();
((Range)worksheet.Cells[company.RowNumber, inputErrorColumn]).Font.Color = XlRgbColor.rgbWhite;
((Range)worksheet.Cells[company.RowNumber, inputErrorColumn]).Interior.Color = XlRgbColor.rgbRed;
_overallVerificationResult[company.ID] = OverallResult.Error;
}
}
}
public void TestEmptyNIPCompanyWithCorrectCompanies()
{
var companiesToCheck = new Dictionary<string, InputCompany>(VerifyCompany.Common.Test.Lib.CompanyGenerator.GetCorrectCompanies());
//add incorrect company
const string emptyNIPCompanyID = "emptyNip";
InputCompany emptyCompany = new InputCompany() { LP = emptyNIPCompanyID, NIP = string.Empty, RowNumber = 1 };
companiesToCheck.Add(emptyCompany.ID, emptyCompany);
Dictionary<string, WhiteListVerResult> verResults = _verifier.VerifyCompanies(companiesToCheck.Values.ToList<InputCompany>(), true, false);
KeyValuePair<string, WhiteListVerResult> companyIncorrectNipChecked = verResults.FirstOrDefault(vr => vr.Key == InputCompany.GetID(emptyCompany.RowNumber, emptyCompany.LP, emptyCompany.NIP));
Assert.IsNotNull(companyIncorrectNipChecked);
Assert.AreEqual(WhiteListVerResultStatus.ErrorNIPEmpty, companyIncorrectNipChecked.Value.VerificationStatus);
Assert.IsTrue(DTHelper.IsItToday(companyIncorrectNipChecked.Value.VerificationDate));
foreach (var companyToCheck in companiesToCheck.Where(c => c.Key != InputCompany.GetID(emptyCompany.RowNumber, emptyCompany.LP, emptyCompany.NIP)))
{
var companyChecked = verResults.FirstOrDefault(vr => vr.Key == companyToCheck.Key);
Assert.IsNotNull(companyChecked);
Logger.Info("ID: {0}, Status: {1}, Nip: {2}, Account nr: {3}", companyChecked.Key, companyChecked.Value.VerificationStatus, companyChecked.Value.Nip, companyChecked.Value.GivenAccountNumber);
Assert.AreEqual(WhiteListVerResultStatus.ActiveVATPayerAccountOKVerSuccessfull, companyChecked.Value.VerificationStatus);
Assert.IsTrue(companyChecked.Value.IsActiveVATPayer);
Assert.AreEqual(companyToCheck.Value.NIP, companyChecked.Value.Nip);
Assert.IsNotNull(companyChecked.Value.AccountNumbers);
Assert.IsTrue(DTHelper.IsItToday(companyChecked.Value.VerificationDate));
Assert.IsTrue(companyChecked.Value.IsGivenAccountNumOnWhiteList);
}
}
private void AddWhiteListVerificationForInvoiceDate(Worksheet worksheet, int headerRow, int lastColumn, int nipColumn, int lpColumn, List <InputCompany> companiesReadFromFile, Dictionary <string, WhiteListVerResult> verifiedCompaniesForInvoiceDate)
{
int whiteListVerColumnForInvoiceDate = lastColumn + WhiteListVerificationForInvoiceDateStatusColumnDelta;
int whiteListVerColumnForInvoiceDateConfirmString = lastColumn + WhiteListVerificationForInvoiceDateConfirmAndDateColumnDelta;
((Range)worksheet.Cells[headerRow, whiteListVerColumnForInvoiceDate]).Formula = _exportColumnsConfig.First(c => c.ID == ExportColumnName.WhiteListVerificationForInvoiceDateStatusHeader.ToString()).HeaderText;
((Range)worksheet.Cells[headerRow, whiteListVerColumnForInvoiceDateConfirmString]).Formula = _exportColumnsConfig.First(c => c.ID == ExportColumnName.WhiteListVerificationForInvoiceDateConfirmAndDateHeader.ToString()).HeaderText;
if (verifiedCompaniesForInvoiceDate != null && verifiedCompaniesForInvoiceDate.Count > 0)
{
foreach (var company in companiesReadFromFile)
{
string nipFromCell = ((Range)worksheet.Cells[company.RowNumber, nipColumn]).Formula.ToString().Trim();
string lpFromCell = ((Range)worksheet.Cells[company.RowNumber, lpColumn]).Formula.ToString().Trim();
if (company.ID != InputCompany.GetID(company.RowNumber, lpFromCell, nipFromCell))
{
throw new ArgumentException($"Nip z odpowiedzi serwera różny od nipu z pliku. Błąd przy zapisie wyniku białej listy do pliku na dzień faktury. Z serwera: NIP={company.NIP}, LP={company.LP}; z pliku: NIP={nipFromCell}, LP={lpFromCell}.");
}
if (verifiedCompaniesForInvoiceDate != null)
{
var verificationResult = verifiedCompaniesForInvoiceDate.FirstOrDefault(vN => vN.Key == company.ID);
if (!verificationResult.Equals(default(KeyValuePair <string, WhiteListVerResult>)))
{
var result = verificationResult.Value.ToMessage();
((Range)worksheet.Cells[company.RowNumber, whiteListVerColumnForInvoiceDate]).Formula = result;
((Range)worksheet.Cells[company.RowNumber, whiteListVerColumnForInvoiceDateConfirmString]).Formula = $"{verificationResult.Value.VerificationDate} - {verificationResult.Value.ConfirmationResponseString}";
if (verificationResult.Value.VerificationStatus == WhiteListVerResultStatus.ActiveVATPayerVerScuccessButGivenAccountNotVerified &&
_overallVerificationResult[verificationResult.Key] != OverallResult.Error)
{
_overallVerificationResult[verificationResult.Key] = OverallResult.Warning;
}
else if (verificationResult.Value.VerificationStatus != WhiteListVerResultStatus.ActiveVATPayerVerSuccessfull && verificationResult.Value.VerificationStatus != WhiteListVerResultStatus.ActiveVATPayerAccountOKVerSuccessfull && verificationResult.Value.VerificationStatus != WhiteListVerResultStatus.ActiveVATPayerButGivenAccountWrong && verificationResult.Value.VerificationStatus != WhiteListVerResultStatus.ActiveVATPayerButHasNoAccounts)
{
_overallVerificationResult[verificationResult.Key] = OverallResult.Error;
}
}
}
else
{
((Range)worksheet.Cells[company.RowNumber, whiteListVerColumnForInvoiceDate]).Formula = _notChecked;
}
}
}
}
public void CorrectWorkbookDoNotGenertateNoteTest()
{
FileInfo fI = new FileInfo(_filePathCorrect);
if (fI.Exists)
{
_ssr = new SpreadSheetReader(fI.FullName, false, false);
}
else
{
throw new FileLoadException("cannot load the input file");
}
var companies = _ssr.ReadDataFromFile();
Assert.IsTrue(_ssr.ColumnMapping.ContainsKey(ImportColumnName.PaymentDate));
Assert.IsFalse(_ssr.ColumnMapping.ContainsKey(ImportColumnName.NoteTitle));
Assert.IsFalse(_ssr.ColumnMapping.ContainsKey(ImportColumnName.NoteID));
Assert.IsFalse(_ssr.ColumnMapping.ContainsKey(ImportColumnName.NoteDate));
Assert.IsFalse(_ssr.ColumnMapping.ContainsKey(ImportColumnName.NoteAmount));
Assert.IsTrue(_ssr.ColumnMapping.ContainsKey(ImportColumnName.NIP));
Assert.IsTrue(_ssr.ColumnMapping.ContainsKey(ImportColumnName.LP));
Assert.IsTrue(_ssr.ColumnMapping.ContainsKey(ImportColumnName.AccountNumber));
Assert.IsTrue(_ssr.ColumnMapping.Count == 4);
Assert.IsTrue(companies.Count == 5);
InputCompany comp = companies[3];
Assert.AreEqual("03124061751111001095041381", comp.BankAccountNumber);
Assert.AreEqual(InputCompany.GetID(8, "4", "7811767696"), comp.ID);
Assert.AreEqual("4", comp.LP);
Assert.AreEqual(8, comp.RowNumber);
Assert.AreEqual("7811767696", comp.NIP);
Assert.IsNull(comp.NoteDate);
Assert.IsNull(comp.NoteID);
Assert.IsNull(comp.NoteNettoAmount);
Assert.IsNull(comp.NoteTitle);
Assert.AreEqual("15.02.2019r.", comp.PaymentDate);
Assert.AreEqual(8, comp.RowNumber);
}
private static void AddCompanyData(Worksheet worksheet, int nipColumn, int lpColumn, List <InputCompany> companiesReadFromFile, Dictionary <string, WhiteListVerResult> verifiedCompanies, bool addAccountsToSeparateColumns, int accountsColumn, int fullNameColumn, int dataColumn, int stringConfIDColumn, int accountSepColStartingColumn, int fullResidenceAddressColumn, int fullWorkingAddressColumn)
{
foreach (var company in companiesReadFromFile)
{
string nipFromCell = ((Range)worksheet.Cells[company.RowNumber, nipColumn]).Formula.ToString().Trim();
string lPFromCell = ((Range)worksheet.Cells[company.RowNumber, lpColumn]).Formula.ToString().Trim();
if (company.ID != InputCompany.GetID(company.RowNumber, lPFromCell, nipFromCell))
{
throw new SpreadSheetWriterExcpetion($"Nip z odpowiedzi serwera różny od nipu z pliku. Błąd przy zapisie do pliku. Z serwera: NIP={company.NIP}, LP={company.LP}; z pliku: NIP={nipFromCell}, LP={lPFromCell}.");
}
var verifiedCompResult = verifiedCompanies[company.ID];
if (verifiedCompResult != null)
{
var accounts = verifiedCompResult.AccountNumbers;
((Range)worksheet.Cells[company.RowNumber, accountsColumn]).Formula = DataFormatHelper.GetAccountsInString(accounts);
var fullName = verifiedCompResult.FullName;
((Range)worksheet.Cells[company.RowNumber, fullNameColumn]).Formula = fullName;
((Range)worksheet.Cells[company.RowNumber, dataColumn]).Formula = verifiedCompResult.VerificationDate;
((Range)worksheet.Cells[company.RowNumber, stringConfIDColumn]).Formula = verifiedCompResult.ConfirmationResponseString;
((Range)worksheet.Cells[company.RowNumber, fullResidenceAddressColumn]).Formula = verifiedCompResult.FullResidenceAddress;
((Range)worksheet.Cells[company.RowNumber, fullWorkingAddressColumn]).Formula = verifiedCompResult.FullWorkingAddress;
if (addAccountsToSeparateColumns && verifiedCompResult.AccountNumbers != null)
{
for (int j = 0; j < verifiedCompResult.AccountNumbers.Count; j++)
{
((Range)worksheet.Cells[company.RowNumber, accountSepColStartingColumn + j]).Formula = DataFormatHelper.GetAccountInString(verifiedCompResult.AccountNumbers[j]);
}
}
}
}
}
private void AddNIPVerification(Worksheet worksheet, int headerRow, int lastColumn, int nipColumn, int lpColumn, List <InputCompany> inputCompanies, Dictionary <string, VerifyNIPResult> verifiedNips)
{
int nipVerColumn = lastColumn + NIPVerficationStatusColumnDelta;
((Range)worksheet.Cells[headerRow, nipVerColumn]).Formula = _exportColumnsConfig.First(c => c.ID == ExportColumnName.NIPVerificationStatusHeader.ToString()).HeaderText;
foreach (var company in inputCompanies)
{
string nipFromCell = ((Range)worksheet.Cells[company.RowNumber, nipColumn]).Formula.ToString().Trim();
string lpFromCell = ((Range)worksheet.Cells[company.RowNumber, lpColumn]).Formula.ToString().Trim();
if (company.ID != InputCompany.GetID(company.RowNumber, lpFromCell, nipFromCell))
{
throw new SpreadSheetWriterExcpetion($"Nip z odpowiedzi serwera różny od nipu z pliku. Błąd przy zapisywaniu wyniku zapytania NIP verification do pliku xls. Z serwera: NIP={company.NIP}, LP={company.LP}; z pliku: NIP={nipFromCell}, LP={lpFromCell}.");
}
if (verifiedNips != null)
{
var nipVerifyStatus = verifiedNips.FirstOrDefault(vN => vN.Key == company.ID);
if (!nipVerifyStatus.Equals(default(KeyValuePair <string, VerifyNIPResult>)))
{
var result = nipVerifyStatus.Value.ToMessage();
((Range)worksheet.Cells[company.RowNumber, nipVerColumn]).Formula = result;
if (nipVerifyStatus.Value != VerifyNIPResult.IsActiveVATPayer)
{
_overallVerificationResult[nipVerifyStatus.Key] = OverallResult.Error;
}
}
}
else
{
((Range)worksheet.Cells[company.RowNumber, nipVerColumn]).Formula = _notChecked;
}
}
}
private void AddREGONVerification(Worksheet worksheet, int headerRow, int lastColumn, int nipColumn, int lpColumn, List <InputCompany> companiesReadFromFile, Dictionary <string, BiRVerifyResult> areCompaniesActive)
{
int regonVerColumn = lastColumn + REGONVerificationStatusColumnDelta;
((Range)worksheet.Cells[headerRow, regonVerColumn]).Formula = _exportColumnsConfig.First(c => c.ID == ExportColumnName.REGONVerificationStatusHeader.ToString()).HeaderText;
foreach (var company in companiesReadFromFile)
{
string nipFromCell = ((Range)worksheet.Cells[company.RowNumber, nipColumn]).Formula.ToString().Trim();
string lpFromCell = ((Range)worksheet.Cells[company.RowNumber, lpColumn]).Formula.ToString().Trim();
if (company.ID != InputCompany.GetID(company.RowNumber, lpFromCell, nipFromCell))
{
throw new ArgumentException($"Nip z odpowiedzi serwera różny od nipu z pliku. Błąd przy zapisie wyniku regon (BiR) do pliku. Z serwera: NIP={company.NIP}, LP={company.LP}; z pliku: NIP={nipFromCell}, LP={lpFromCell}.");
}
if (areCompaniesActive != null)
{
var isCompanyActiveStatus = areCompaniesActive.FirstOrDefault(vN => vN.Key == company.ID);
if (!isCompanyActiveStatus.Equals(default(KeyValuePair <string, BiRVerifyStatus>)))
{
var result = isCompanyActiveStatus.Value.Message;
((Range)worksheet.Cells[company.RowNumber, regonVerColumn]).Formula = result;
if (isCompanyActiveStatus.Value.BiRVerifyStatus != BiRVerifyStatus.IsActive)
{
_overallVerificationResult[isCompanyActiveStatus.Key] = OverallResult.Error;
}
}
}
else
{
((Range)worksheet.Cells[company.RowNumber, regonVerColumn]).Formula = _notChecked;
}
}
}
private void AddOverallResultsToFile(Worksheet worksheet, int headerRow, int lastColumn, int nipColumn, int lpColumn, List <InputCompany> companiesReadFromFile, List <InputCompany> erroredWhileReadingInputFileCompanies)
{
int overallVerColumn = lastColumn + AllVerificationStatusColumnDelta;
((Range)worksheet.Cells[headerRow, overallVerColumn]).Formula = _exportColumnsConfig.First(c => c.ID == ExportColumnName.ALLVerificationStatusHeader.ToString()).HeaderText;
var allCompaniesRead = companiesReadFromFile.Concat(erroredWhileReadingInputFileCompanies);
foreach (var company in allCompaniesRead)
{
string nipFromCell = ((Range)worksheet.Cells[company.RowNumber, nipColumn]).Formula.ToString().Trim();
string lpFromCell = ((Range)worksheet.Cells[company.RowNumber, lpColumn]).Formula.ToString().Trim();
if (company.ID != InputCompany.GetID(company.RowNumber, lpFromCell, nipFromCell))
{
throw new ArgumentException("Nip z odpowiedzi serwera różny od nipu z pliku. Błąd przy zapisie overall result do pliku.");
}
var result = _overallVerificationResult[company.ID];
if (result == OverallResult.Error)
{
((Range)worksheet.Cells[company.RowNumber, overallVerColumn]).Formula = VerFailedMsg;
((Range)worksheet.Cells[company.RowNumber, overallVerColumn]).Font.Color = XlRgbColor.rgbWhite;
((Range)worksheet.Cells[company.RowNumber, overallVerColumn]).Interior.Color = XlRgbColor.rgbRed;
}
else if (result == OverallResult.Warning)
{
((Range)worksheet.Cells[company.RowNumber, overallVerColumn]).Formula = VerWarningMsg;
((Range)worksheet.Cells[company.RowNumber, overallVerColumn]).Font.Color = XlRgbColor.rgbWhite;
((Range)worksheet.Cells[company.RowNumber, overallVerColumn]).Interior.Color = XlRgbColor.rgbOrange;
}
else
{
((Range)worksheet.Cells[company.RowNumber, overallVerColumn]).Formula = VerOKMsg;
}
}
}
private static void SetUp()
{
_random = new Random((int)DateTime.Now.Ticks);
_correctNIPs = new List <string>()
{
_correctNIP1, _correctNIP2, _correctNIP3, _correctNIP4, _correctNIP5, _correctNIP6, _correctNIP7, _correctNIP8, _correctNIP9, _correctNIP10, _correctNIP11
};
_incorrectNIPs = new List <string>()
{
_incorrectNIP1, _incorrectNIP2, _incorrectNIP3, _incorrectNIP4, _incorrectNIP5
};
_correctCompanies = new List <InputCompany>()
{
new InputCompany()
{
NIP = _correctNIP1, LP = _correctID1, RowNumber = 1, BankAccountNumber = "03124061751111001095041381"
},
new InputCompany()
{
NIP = _correctNIP2, LP = _correctID2, RowNumber = 2, BankAccountNumber = "24114011240000582620001003"
},
new InputCompany()
{
NIP = _correctNIP3, LP = _correctID3, RowNumber = 3, BankAccountNumber = "68160010841844744520000009"
},
new InputCompany()
{
NIP = _correctNIP4, LP = _correctID4, RowNumber = 4, BankAccountNumber = "27116022020000000109081086"
},
new InputCompany()
{
NIP = _correctNIP5, LP = _correctID5, RowNumber = 5, BankAccountNumber = "11114011240000584072001027"
},
new InputCompany()
{
NIP = _correctNIP6, LP = _correctID6, RowNumber = 6, BankAccountNumber = "64114020040000300278432279"
},
new InputCompany()
{
NIP = _correctNIP7, LP = _correctID7, RowNumber = 7, BankAccountNumber = "17124060031111000049409738"
},
new InputCompany()
{
NIP = _correctNIP8, LP = _correctID8, RowNumber = 8, BankAccountNumber = "67160011270003012765973021"
},
new InputCompany()
{
NIP = _correctNIP9, LP = _correctID9, RowNumber = 9, BankAccountNumber = "51124013301111001028688812"
},
new InputCompany()
{
NIP = _correctNIP10, LP = _correctID10, RowNumber = 10, BankAccountNumber = "31103015080000000500217065"
},
new InputCompany()
{
NIP = _correctNIP11, LP = _correctID11, RowNumber = 11, BankAccountNumber = "77160010130002001222057198"
}
};
_incorrectCompanies = new List <InputCompany>()
{
new InputCompany()
{
NIP = _incorrectNIP1, LP = _incorrectID1, RowNumber = 1
},
new InputCompany()
{
NIP = _incorrectNIP2, LP = _incorrectID2, RowNumber = 2
},
new InputCompany()
{
NIP = _incorrectNIP3, LP = _incorrectID3, RowNumber = 3
},
new InputCompany()
{
NIP = _incorrectNIP4, LP = _incorrectID4, RowNumber = 4
},
new InputCompany()
{
NIP = _incorrectNIP5, LP = _incorrectID5, RowNumber = 5
}
};
CompaniesNIPIDDic = new Dictionary <string, string>
{
{ _correctNIP1, InputCompany.GetID(1, _correctID1, _correctNIP1) },
{ _correctNIP2, InputCompany.GetID(2, _correctID2, _correctNIP2) },
{ _correctNIP3, InputCompany.GetID(3, _correctID3, _correctNIP3) },
{ _correctNIP4, InputCompany.GetID(4, _correctID4, _correctNIP4) },
{ _correctNIP5, InputCompany.GetID(5, _correctID5, _correctNIP5) },
{ _correctNIP6, InputCompany.GetID(6, _correctID6, _correctNIP6) },
{ _correctNIP7, InputCompany.GetID(7, _correctID7, _correctNIP7) },
{ _correctNIP8, InputCompany.GetID(8, _correctID8, _correctNIP8) },
{ _correctNIP9, InputCompany.GetID(9, _correctID9, _correctNIP9) },
{ _correctNIP10, InputCompany.GetID(10, _correctID10, _correctNIP10) },
{ _correctNIP11, InputCompany.GetID(11, _correctID11, _correctNIP11) },
{ _incorrectNIP1, InputCompany.GetID(1, _incorrectID1, _incorrectNIP1) },
{ _incorrectNIP2, InputCompany.GetID(2, _incorrectID2, _incorrectNIP2) },
{ _incorrectNIP3, InputCompany.GetID(3, _incorrectID3, _incorrectNIP3) },
{ _incorrectNIP4, InputCompany.GetID(4, _incorrectID4, _incorrectNIP4) },
{ _incorrectNIP5, InputCompany.GetID(5, _incorrectID5, _incorrectNIP5) }
};
_correctCompaniesDic = new Dictionary <string, InputCompany>()
{
{ _correctCompanies[0].ID, _correctCompanies[0] },
{ _correctCompanies[1].ID, _correctCompanies[1] },
{ _correctCompanies[2].ID, _correctCompanies[2] },
{ _correctCompanies[3].ID, _correctCompanies[3] },
{ _correctCompanies[4].ID, _correctCompanies[4] },
{ _correctCompanies[5].ID, _correctCompanies[5] },
{ _correctCompanies[6].ID, _correctCompanies[6] },
{ _correctCompanies[7].ID, _correctCompanies[7] },
{ _correctCompanies[8].ID, _correctCompanies[8] },
{ _correctCompanies[9].ID, _correctCompanies[9] },
{ _correctCompanies[10].ID, _correctCompanies[10] },
};
_incorrectNipCompaniesDic = new Dictionary <string, InputCompany>()
{
{ _incorrectCompanies[0].ID, _incorrectCompanies[0] },
{ _incorrectCompanies[1].ID, _incorrectCompanies[1] },
{ _incorrectCompanies[2].ID, _incorrectCompanies[2] },
{ _incorrectCompanies[3].ID, _incorrectCompanies[3] },
{ _incorrectCompanies[4].ID, _incorrectCompanies[4] },
};
var phisicalC = new InputCompany()
{
NIP = _correctPhisicalCompanyNIP1, LP = "1", RowNumber = 1
};
_correctPhisicalCompanyDic = new Dictionary <string, InputCompany>()
{
{ phisicalC.ID, phisicalC }
};
_isSetUp = true;
}