public void CorrectNipsPrawnaMulitiple()
{
Dictionary <string, InputCompany> correctCompanies = CompanyGenerator.GetCorrectCompanies();
var correctC = correctCompanies.Values.ToList();
for (int i = 0; i < correctC.Count(); i++)
{
var c = correctC[i];
InputCompany tempCompany = new InputCompany()
{
RowNumber = c.RowNumber + 20, NIP = c.NIP, LP = c.LP
};
correctCompanies.Add(tempCompany.ID, tempCompany);
tempCompany = new InputCompany()
{
RowNumber = c.RowNumber + 120, NIP = c.NIP, LP = c.LP
};
correctCompanies.Add(tempCompany.ID, tempCompany);
}
Dictionary <string, BiRVerifyResult> verResults = _verifier.AreCompaniesActive(correctCompanies.Values.ToList <InputCompany>());
Assert.AreEqual(correctCompanies.Count, verResults.Count);
Assert.AreEqual(0, correctCompanies.Keys.Except(verResults.Keys).Count());
foreach (var result in verResults)
{
Assert.AreEqual(BiRVerifyStatus.IsActive, result.Value.BiRVerifyStatus);
}
}
public void TestInActiveAndActiveCompaniesAndAccounts()
{
var companiesToCheck = new Dictionary<string, InputCompany>(CompanyGenerator.GetCorrectCompanies());
string inActiveCompanyNIP = CompanyGenerator.GetZakonczoneUpadaloscioweNIP();
string inActiveCompanyID = "u1";
//add inactive company
InputCompany inactiveCompany = new InputCompany() { NIP = inActiveCompanyNIP, LP = inActiveCompanyID, RowNumber = 1 };
companiesToCheck.Add(inActiveCompanyID, inactiveCompany);
Dictionary<string, WhiteListVerResult> verResults = _verifier.VerifyCompanies(companiesToCheck.Values.ToList<InputCompany>(), true, false);
var companyInacticeResultCheck = verResults.FirstOrDefault(vr => vr.Key == inactiveCompany.ID);
Assert.IsNotNull(companyInacticeResultCheck);
Assert.AreEqual(WhiteListVerResultStatus.NotActiveVATPayer, companyInacticeResultCheck.Value.VerificationStatus);
Assert.IsTrue(DTHelper.IsItToday(companyInacticeResultCheck.Value.VerificationDate));
Assert.AreEqual(inActiveCompanyNIP, companyInacticeResultCheck.Value.Nip);
foreach (var companyToCheck in companiesToCheck.Where(c => c.Key != inActiveCompanyID))
{
var companyChecked = verResults.FirstOrDefault(vr => vr.Key == companyToCheck.Key);
Assert.IsNotNull(companyChecked);
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 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;
}
}
}
private static IEnumerable <KeyValuePair <string, InputCompany> > GetDataZakonczeniaDzialanosci()
{
var result = new Dictionary <string, InputCompany>();
InputCompany tempComp;
//Prawna
tempComp = new InputCompany()
{
NIP = "6750001308", LP = "3948", RowNumber = 1
};
result.Add(tempComp.ID, tempComp);
//Fizyczna silos=4
// result.Add("7311680354", new Company() { NIP = "7311680354" });
//Fizyczna silos=4
//result.Add("5252044755", new Company() { NIP = "5252044755" });
//Fizyczna
tempComp = new InputCompany()
{
NIP = "8281014020", LP = "3948", RowNumber = 1
};
result.Add(tempComp.ID, tempComp);
return(result);
}
private void FillInWorksheetWithData(ref Worksheet worksheet, InputCompany inputCompany, WhiteListVerResult whiteListVerResult)
{
if (inputCompany.NoteID.Length < 4) // means that only number is given
{
((Range)worksheet.Cells[3, 1]).Formula = string.Format($"nr {inputCompany.NoteID}/{DateTime.Now.Month}/{DateTime.Now.Year}");
}
else //means that the full number was given in the input file
{
((Range)worksheet.Cells[3, 1]).Formula = string.Format($"nr {inputCompany.NoteID}");
}
if (string.IsNullOrEmpty(inputCompany.NoteDate))
{
((Range)worksheet.Cells[3, 9]).Formula = DateTime.Now.ToString("dd.MM.yyyyr.");
}
else
{
((Range)worksheet.Cells[3, 9]).Formula = string.Format($"{inputCompany.NoteDate}");
}
((Range)worksheet.Cells[4, 9]).Formula = GetPaymentPeriod(inputCompany.NoteTitle);
string fullNameWithAbbr = FormatHelper.AbbreviateFullNameOfCompany(whiteListVerResult.FullName);
fullNameWithAbbr = fullNameWithAbbr.Replace("\"", string.Empty);
int numOfLinesForFullNames = 1 + (fullNameWithAbbr.Length / _maxLengthOfLineInFile);
for (int j = 0; j < numOfLinesForFullNames; j++)
{
string temp = fullNameWithAbbr.Substring(j * _maxLengthOfLineInFile);
temp = temp.Substring(0, Math.Min(_maxLengthOfLineInFile, temp.Length)).Trim();
((Range)worksheet.Cells[8 + j, 8]).Formula = temp;
}
string addressFull = string.IsNullOrEmpty(whiteListVerResult.FullResidenceAddress) ?
whiteListVerResult.FullWorkingAddress : whiteListVerResult.FullResidenceAddress;
addressFull = string.Concat(AddressHelper.GetStreetPrefix(addressFull), _space, addressFull).Trim();
addressFull = addressFull.Replace(_colon, string.Empty);
int addressDivIndex = _postalCodePattern.Match(addressFull).Index;
((Range)worksheet.Cells[8 + numOfLinesForFullNames, 8]).Formula = addressFull.Substring(0, addressDivIndex).Trim();
((Range)worksheet.Cells[9 + numOfLinesForFullNames, 8]).Formula = addressFull.Substring(addressDivIndex).Trim();
((Range)worksheet.Cells[14, 9]).Formula = whiteListVerResult.Nip.Insert(3, _space).Insert(6, _space).Insert(9, _space);
((Range)worksheet.Cells[17, 9]).Formula = FormatHelper.GetAccountNumberInString(whiteListVerResult.GivenAccountNumber);
((Range)worksheet.Cells[21, 9]).Formula = inputCompany.NoteNettoAmount;
((Range)worksheet.Cells[24, 3]).Formula = inputCompany.NoteTitle;
string nettoAmountStr = inputCompany.NoteNettoAmount.Replace(_dot, _colon);
double nettoAmount = double.Parse(nettoAmountStr);
double vatAmout = Math.Round(0.08 * nettoAmount, 2);
((Range)worksheet.Cells[36, 3]).Formula = NumberToWordsConverter.ConvertNumberToAmountPln((nettoAmount + vatAmout).ToString());
}
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 CreateNote(InputCompany inputCompany, WhiteListVerResult whiteListVerResult, string outputPath, bool createPDF)
{
string fileName = string.Empty;
string templPath = string.Format("{0}\\{1}", Environment.CurrentDirectory, "templates\\NotaTemplate.xltx");
_theWorkbook = _app.Workbooks.Add(templPath);
Sheets sheets = _theWorkbook.Worksheets;
Worksheet _worksheet = (Worksheet)sheets.get_Item(1);
FillInWorksheetWithData(ref _worksheet, inputCompany, whiteListVerResult);
fileName = whiteListVerResult.FullName.Replace(_space, _underscore);
fileName = fileName.Replace(_dot, string.Empty);
fileName = _fileNamePattern.IsMatch(fileName) ? _fileNamePattern.Match(fileName).Value : fileName;
fileName = fileName.Length > _lengthOfFullNameInFileNameWithNote?fileName.Substring(0, _lengthOfFullNameInFileNameWithNote) : fileName;
string noteID = _noteIDPattern.IsMatch(inputCompany.NoteID) ? _noteIDPattern.Match(inputCompany.NoteID).Value : string.Empty;
fileName = string.Format("{0}-{1}-{2}", inputCompany.RowNumber, noteID, fileName);
if (createPDF)
{
string pdfFilePath = string.Format("{0}\\{1}\\{2}.pdf", outputPath, _pdfDirName, fileName);
_theWorkbook.ExportAsFixedFormat2(XlFixedFormatType.xlTypePDF, pdfFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false, Type.Missing, Type.Missing);
}
string xlsFilePath = string.Format("{0}\\{1}.xlsx", outputPath, fileName);
_theWorkbook.SaveAs(xlsFilePath, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_theWorkbook.Close(true, Type.Missing, Type.Missing);
}
public void TestCorrectCompaniesWithInCorrectBankAccounts()
{
var companiesToCheck = new Dictionary<string, InputCompany>(CompanyGenerator.GetCorrectCompanies());
//0 - z³y account number, nie na liœcie
InputCompany tempComp = companiesToCheck.ElementAt(0).Value;
companiesToCheck.Remove(companiesToCheck.ElementAt(0).Key);
companiesToCheck.Add(tempComp.ID, new InputCompany() { LP = tempComp.LP, BankAccountNumber = new string(tempComp.BankAccountNumber), NIP = tempComp.NIP, RowNumber= tempComp.RowNumber});
string notOnWhiteListAccountNumberID = tempComp.ID;
//1- z literk¹ account number
tempComp = companiesToCheck.ElementAt(1).Value;
companiesToCheck.Remove(companiesToCheck.ElementAt(1).Key);
companiesToCheck.Add(tempComp.ID, new InputCompany() { LP = tempComp.LP, BankAccountNumber = new string(tempComp.BankAccountNumber).Remove(5, 2).Insert(4, "ab"), NIP = new string(tempComp.NIP), RowNumber = tempComp.RowNumber });
companiesToCheck[notOnWhiteListAccountNumberID].BankAccountNumber = tempComp.BankAccountNumber;
//2 - pusty account number
tempComp = companiesToCheck.ElementAt(2).Value;
companiesToCheck.Remove(companiesToCheck.ElementAt(2).Key);
companiesToCheck.Add(tempComp.ID, new InputCompany() { LP = tempComp.LP, BankAccountNumber = string.Empty, NIP = new string(tempComp.NIP), RowNumber = tempComp.RowNumber });
//3 - null account number
tempComp = companiesToCheck.ElementAt(3).Value;
companiesToCheck.Remove(companiesToCheck.ElementAt(3).Key);
companiesToCheck.Add(tempComp.ID, new InputCompany() { LP = tempComp.LP, BankAccountNumber = null, NIP = new string(tempComp.NIP), RowNumber = tempComp.RowNumber });
Dictionary<string, WhiteListVerResult> verResults = _verifier.VerifyCompanies(companiesToCheck.Values.ToList<InputCompany>(), true, false);
KeyValuePair<string, WhiteListVerResult> companyChecked = verResults.FirstOrDefault(vr => vr.Key == companiesToCheck.ElementAt(0).Key);
Assert.IsNotNull(companyChecked);
Assert.AreEqual(WhiteListVerResultStatus.ActiveVATPayerButGivenAccountNotOnWhiteList, companyChecked.Value.VerificationStatus);
Assert.IsTrue(companyChecked.Value.IsActiveVATPayer);
Assert.AreEqual(companiesToCheck.ElementAt(0).Value.NIP, companyChecked.Value.Nip);
Assert.IsNotNull(companyChecked.Value.AccountNumbers);
Assert.IsTrue(DTHelper.IsItToday(companyChecked.Value.VerificationDate));
Assert.IsFalse(companyChecked.Value.IsGivenAccountNumOnWhiteList);
companyChecked = verResults.FirstOrDefault(vr => vr.Key == companiesToCheck.ElementAt(1).Key);
Assert.IsNotNull(companyChecked);
Assert.AreEqual(WhiteListVerResultStatus.ActiveVATPayerButGivenAccountWrong, companyChecked.Value.VerificationStatus);
Assert.IsTrue(companyChecked.Value.IsActiveVATPayer);
Assert.AreEqual(companiesToCheck.ElementAt(1).Value.NIP, companyChecked.Value.Nip);
Assert.IsNotNull(companyChecked.Value.AccountNumbers);
Assert.IsTrue(DTHelper.IsItToday(companyChecked.Value.VerificationDate));
Assert.IsFalse(companyChecked.Value.IsGivenAccountNumOnWhiteList);
for (int i = 2; i < 4; i++)
{
companyChecked = verResults.FirstOrDefault(vr => vr.Key == companiesToCheck.ElementAt(i).Key);
Assert.IsNotNull(companyChecked);
Assert.AreEqual(WhiteListVerResultStatus.ActiveVATPayerVerScuccessButGivenAccountNotVerified, companyChecked.Value.VerificationStatus);
Assert.IsTrue(companyChecked.Value.IsActiveVATPayer);
Assert.AreEqual(companiesToCheck.ElementAt(i).Value.NIP, companyChecked.Value.Nip);
Assert.IsNotNull(companyChecked.Value.AccountNumbers);
Assert.IsTrue(DTHelper.IsItToday(companyChecked.Value.VerificationDate));
Assert.IsFalse(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 NipInCorrectTest()
{
FileInfo fI = new FileInfo(_filePathNipInCorrect);
if (fI.Exists)
{
_ssr = new SpreadSheetReader(fI.FullName, true, false);
}
else
{
throw new FileLoadException("cannot load the input file");
}
InputCompany firstInputCompanies = _ssr.ReadDataFromFile()[0];
Assert.AreEqual(InputCompanyFormatError.NIPFormatError, firstInputCompanies.FormatErrors[0]);
}
static Dictionary <string, InputCompany> GetDataZakoczeniaPostepowaniaUpadlosiowego()
{
var result = new Dictionary <string, InputCompany>();
InputCompany tempComp;
//Prawna
tempComp = new InputCompany()
{
NIP = "5992973948", LP = "3948", RowNumber = 1
};
result.Add(tempComp.ID, tempComp);
//Fizyczna
tempComp = new InputCompany()
{
NIP = "1132317562", LP = "3948", RowNumber = 1
};
result.Add(tempComp.ID, tempComp);
return(result);
}
static Dictionary <string, InputCompany> GetDataOrzeczeniaOUpadlosci()
{
var result = new Dictionary <string, InputCompany>();
InputCompany tempComp;
//Prawna
tempComp = new InputCompany()
{
NIP = "6861573829", LP = "3948", RowNumber = 1
};
result.Add(tempComp.ID, tempComp);
//Fizyczna
tempComp = new InputCompany()
{
NIP = "7581384594", LP = "3948", RowNumber = 1
};
result.Add(tempComp.ID, tempComp);
return(result);
}
private static IEnumerable <KeyValuePair <string, InputCompany> > GetDataZawieszeniaDzialanosci()
{
var result = new Dictionary <string, InputCompany>();
InputCompany tempComp;
//Prawna
tempComp = new InputCompany()
{
NIP = "8442344628", LP = "3948", RowNumber = 1
};
result.Add(tempComp.ID, tempComp);
//Fizyczna
tempComp = new InputCompany()
{
NIP = "5631802823", LP = "3948", RowNumber = 1
};
result.Add(tempComp.ID, tempComp);
return(result);
}
public void CorrectWorkbookGenertateNoteTest()
{
FileInfo fI = new FileInfo(_filePathCorrect);
if (fI.Exists)
{
_ssr = new SpreadSheetReader(fI.FullName, true, false);
}
else
{
throw new FileLoadException("cannot load the input file");
}
var companies = _ssr.ReadDataFromFile();
Assert.IsTrue(_ssr.ColumnMapping.ContainsKey(ImportColumnName.PaymentDate));
Assert.IsTrue(_ssr.ColumnMapping.ContainsKey(ImportColumnName.NoteTitle));
Assert.IsTrue(_ssr.ColumnMapping.ContainsKey(ImportColumnName.NoteID));
Assert.IsTrue(_ssr.ColumnMapping.ContainsKey(ImportColumnName.NoteDate));
Assert.IsTrue(_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 == 8);
Assert.IsTrue(companies.Count == 5);
InputCompany comp = companies[0];
Assert.AreEqual("46124017501111001068892448", comp.BankAccountNumber);
Assert.AreEqual("5+1-7792369887", comp.ID);
Assert.AreEqual("1", comp.LP);
Assert.AreEqual(5, comp.RowNumber);
Assert.AreEqual("7792369887", comp.NIP);
Assert.AreEqual("12.01.2019r.", comp.NoteDate);
Assert.AreEqual("12", comp.NoteID);
Assert.AreEqual("1000", comp.NoteNettoAmount);
Assert.AreEqual("Q1", comp.NoteTitle);
Assert.AreEqual("15.02.2019r.", comp.PaymentDate);
Assert.AreEqual(5, comp.RowNumber);
}
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 List <InputCompanyFormatError> ValidateCompany(InputCompany tempCompany)
{
List <InputCompanyFormatError> errors = new List <InputCompanyFormatError>();
if (!string.IsNullOrEmpty(tempCompany.BankAccountNumber) && !_bankAccountRegEx.IsMatch(tempCompany.BankAccountNumber))
{
errors.Add(InputCompanyFormatError.BankAccountFormatError);
}
if (string.IsNullOrEmpty(tempCompany.NIP) || !DataFormatHelper.IsNipValid(tempCompany.NIP))
{
errors.Add(InputCompanyFormatError.NIPFormatError);
}
if (string.IsNullOrEmpty(tempCompany.LP))
{
errors.Add(InputCompanyFormatError.LPFormatError);
}
return(errors);
}
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 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 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;
}
private WhiteListVerResult ExtractResultForCompanyFromResponse(bool verifyBankAccount, EntryListResponse content, InputCompany companyToVerify)
{
WhiteListVerResult tempWhiteListVerResult = new WhiteListVerResult()
{
Nip = companyToVerify.NIP,
VerificationDate = DateTime.Now.ToString()
};
if (companyToVerify.BankAccountNumber is null || companyToVerify.FormatErrors.Contains(InputCompanyFormatError.BankAccountFormatError))
{
tempWhiteListVerResult.GivenAccountNumber = string.Empty;
}
internal void StoreToFile(VerificationResult verificationResult, SearchSettings searchSettings)
{
errorCompanies = new StringBuilder();
allResults = new StringBuilder();
if (verificationResult.ErroredWhileReadingInputFileCompanies != null && verificationResult.ErroredWhileReadingInputFileCompanies.Count > 0)
{
errorCompanies.AppendLine("Błędy w formacie danych w pliku wejściowym (firmy nie były lub były częściowo sprawdzane w systemach zewnętrznych");
allResults.AppendLine("Błędy w formacie danych w pliku wejściowym (firmy nie były lub były częściowo sprawdzane w systemach zewnętrznych");
StringBuilder sB = new StringBuilder();
foreach (var erroredCompany in verificationResult.ErroredWhileReadingInputFileCompanies)
{
sB.Append($"Wiersz:{erroredCompany.RowNumber} NIP:{erroredCompany.NIP} - ");
foreach (var error in erroredCompany.FormatErrors)
{
sB.Append($"{error.ToMessage()} ");
}
sB.Append("\n");
}
errorCompanies.AppendLine(sB.ToString());
allResults.AppendLine(sB.ToString());
}
if (searchSettings.VerifyCompaniesInVATSystem)
{
errorCompanies.AppendLine("BŁĘDY w bazie NIP (podatki, czy jest aktywnym płatnikiem VAT):");
allResults.AppendLine("Wyniki sprawdzenia w bazie NIP (aktywny płatnik VAT):");
if (verificationResult.VatSystemVerResultForInvoiceDate != null)
{
foreach (var vatSystemCompVerResult in verificationResult.VatSystemVerResultForInvoiceDate)
{
var verifiedNipResult = InputCompany.GetNIPFromID(vatSystemCompVerResult.Key);
if (vatSystemCompVerResult.Value != VerifyNIPResult.IsActiveVATPayer)
{
errorCompanies.Append($"{InputCompany.GetFormattedStringFromID(vatSystemCompVerResult.Key)} -- {vatSystemCompVerResult.Value.ToMessage()}\n");
}
allResults.Append($"{InputCompany.GetFormattedStringFromID(vatSystemCompVerResult.Key)} -- {vatSystemCompVerResult.Value.ToMessage()}\n");
}
}
else
{
errorCompanies.AppendLine("Wystąpił BŁĄD podczas sprawdzania danych w bazie NIP. Brak wyników. Skontaktuj się z administratorem.");
allResults.AppendLine("Wystąpił BŁĄD podczas sprawdzania danych w bazie NIP. Brak wyników. Skontaktuj się z administratorem.");
}
}
if (searchSettings.VerifyCompaniesInBiRSystem)
{
errorCompanies.AppendLine("\nBŁĘDY w bazie REGON (czy jest aktywnym podmiotem):");
allResults.AppendLine("\nWyniki sprawdzenia w bazie REGON (aktywny podmiot):");
if (verificationResult.BiRSystemVerResult != null)
{
foreach (var birSystemCompResult in verificationResult.BiRSystemVerResult)
{
if (birSystemCompResult.Value.BiRVerifyStatus != BiRVerifyStatus.IsActive)
{
errorCompanies.Append($"{InputCompany.GetFormattedStringFromID(birSystemCompResult.Key)} -- {birSystemCompResult.Value.Message}\n");
}
allResults.Append($"{InputCompany.GetFormattedStringFromID(birSystemCompResult.Key)} -- {birSystemCompResult.Value.Message}\n");
}
}
else
{
errorCompanies.AppendLine("Wystąpił BŁĄD podczas sprawdzania danych w bazie REGON (BiR. Brak wyników. Skontaktuj się z administratorem.");
allResults.AppendLine("Wystąpił BŁĄD podczas sprawdzania danych w bazie REGON (BiR). Brak wyników. Skontaktuj się z administratorem.");
}
}
if (searchSettings.VerifyCompaniesInWhiteListSystem)
{
errorCompanies.AppendLine("\nBŁĘDY w bazie LISTY BIAŁYCH FIRM (czy jest czynnym płatnikiem VAT i ma przypisane dobre konto bankowe):");
allResults.AppendLine("\nWyniki sprawdzenia w bazie LISTY BIAŁYCH FIRM (czy jest czynnym płatnikiem VAT i ma przypisane dobre konto bankowe):");
if (verificationResult.WhiteListCompVerResult != null)
{
foreach (var whiteListCompVerResult in verificationResult.WhiteListCompVerResult)
{
if (whiteListCompVerResult.Value.VerificationStatus != VerifyWhiteListCompany.Lib.WhiteListVerResultStatus.ActiveVATPayerAccountOKVerSuccessfull &&
whiteListCompVerResult.Value.VerificationStatus != VerifyWhiteListCompany.Lib.WhiteListVerResultStatus.ActiveVATPayerVerSuccessfull)
{
errorCompanies.Append($"{InputCompany.GetFormattedStringFromID(whiteListCompVerResult.Key)} -- {whiteListCompVerResult.Value.ToMessage()} Ciąg id zapytania w systemie: {whiteListCompVerResult.Value.ConfirmationResponseString}\n");
}
allResults.Append(string.Format($"{InputCompany.GetFormattedStringFromID(whiteListCompVerResult.Key)} -- {whiteListCompVerResult.Value.ToMessage()} Ciąg id zapytania w systemie: {whiteListCompVerResult.Value.ConfirmationResponseString}\n"));
}
}
else
{
{
errorCompanies.AppendLine("Wystąpił BŁĄD podczas sprawdzania danych w bazie Biała Lista Przedsiębiorców. Brak wyników. Skontaktuj się z administratorem.");
allResults.AppendLine("Wystąpił BŁĄD podczas sprawdzania danych w bazie Biała Lista Przedsiębiorców. Brak wyników. Skontaktuj się z administratorem.");
}
}
}
if (searchSettings.VerifyAlsoForInvoiceDate)
{
errorCompanies.AppendLine("\nBŁĘDY w bazie LISTY BIAŁYCH FIRM na *DATĘ FAKTURY* (czy jest czynnym płatnikiem VAT i ma przypisane dobre konto bankowe):");
allResults.AppendLine("\nWyniki sprawdzenia w bazie LISTY BIAŁYCH FIRM na *DATĘ FAKTURY* (czy jest czynnym płatnikiem VAT i ma przypisane dobre konto bankowe):");
if (verificationResult.WhiteListCompVerResultForInvoiceData != null)
{
foreach (var whiteListCompVerResult in verificationResult.WhiteListCompVerResultForInvoiceData)
{
if (whiteListCompVerResult.Value.VerificationStatus != VerifyWhiteListCompany.Lib.WhiteListVerResultStatus.ActiveVATPayerAccountOKVerSuccessfull &&
whiteListCompVerResult.Value.VerificationStatus != VerifyWhiteListCompany.Lib.WhiteListVerResultStatus.ActiveVATPayerVerSuccessfull)
{
errorCompanies.Append($"{InputCompany.GetFormattedStringFromID(whiteListCompVerResult.Key)} -- {whiteListCompVerResult.Value.ToMessage()} Ciąg id zapytania w systemie: {whiteListCompVerResult.Value.ConfirmationResponseString}\n");
}
allResults.Append(string.Format($"{InputCompany.GetFormattedStringFromID(whiteListCompVerResult.Key)} -- {whiteListCompVerResult.Value.ToMessage()} Ciąg id zapytania w systemie: {whiteListCompVerResult.Value.ConfirmationResponseString}\n"));
}
}
else
{
{
errorCompanies.AppendLine("Wystąpił BŁĄD podczas sprawdzania danych w bazie Biała Lista Przedsiębiorców na *datę faktury*. Brak wyników. Skontaktuj się z administratorem.");
allResults.AppendLine("Wystąpił BŁĄD podczas sprawdzania danych w bazie Biała Lista Przedsiębiorców *datę faktury*. Brak wyników. Skontaktuj się z administratorem.");
}
}
}
SaveToFile("result.txt", searchSettings, errorCompanies, allResults);
string fileNameInSrcDir = string.Format(@"{0}\result-{1}-{2}.txt", searchSettings.InputFileDir, searchSettings.InputFileName, DateTime.Now.ToString("yyyyMMdd-hhmm"));
SaveToFile(fileNameInSrcDir, searchSettings, errorCompanies, allResults);
}
private void AnalyzeWorksheet(Worksheet worksheet)
{
string lastProcessedItem = "Jeszcze nie zaczelismy przetwarzania";
int lastReadColumn = -1;
string tempStr = string.Empty;
CompaniesReadFromFile = new List <InputCompany>();
try
{
lastProcessedItem = "Przed wczytywaniem nagłówka";
HeaderRow = -1;
HeaderRow = SpreadSheetHelper.FindHeaderRow(worksheet, _columnsConfig);
if (HeaderRow == -1)
{
throw new SpreadSheetReaderHeaderException(string.Format("Błąd formatu aruksza. W arkuszu: {0} nie znaleziono nagłówka danych. Sprawdź czy któryś nagłówek zawiera słowo 'nip'", worksheet.Name));
}
DetermineColumns(worksheet);
ValidateColumns();
lastProcessedItem = "Wczytano nagłówek";
InputCompany tempCompany;
for (int i = HeaderRow + 1; !IsEndOfTable(i, 1, worksheet); i++)
{
tempCompany = new InputCompany();
tempCompany.RowNumber = i;
lastProcessedItem = $"Przed wczytaniem NIPu. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NIP];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NIP]]).Formula.ToString();
tempCompany.NIP = GetNip(tempStr);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano NIP. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem konta bankowego. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.AccountNumber];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.AccountNumber]]).Formula.ToString();
tempCompany.BankAccountNumber = GetBankAccountNumber(tempStr);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano Numer Konta. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem LP. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.LP];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.LP]]).Formula.ToString();
tempCompany.LP = GetLP(tempStr);
tempStr = string.Empty;
if (CompaniesReadFromFile.Any(c => c.ID == tempCompany.ID))
{
throw new SpreadSheetReaderException($"Dane zawierają już wpis o takiej samej pozycji i nipie. Aktualna wiersz: {i}, Nip: {tempCompany.NIP}, LP: {tempCompany.LP}");
}
lastProcessedItem = $"Wczytano LP.Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem daty zapłaty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.PaymentDate];
var dateRange = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.PaymentDate]]);
tempStr = dateRange.Formula.ToString();
tempCompany.PaymentDate = GetDate(dateRange);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano datę zapłaty. Aktualna pozycja: wiersz {i}";
if (ColumnMapping.ContainsKey(ImportColumnName.InvoiceDate))
{
lastProcessedItem = $"Przed wczytaniem daty faktury. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.InvoiceDate];
var invoiceDateRange = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.InvoiceDate]]);
tempStr = invoiceDateRange.Formula.ToString();
double tempDateInDouble;
if (double.TryParse(tempStr, out tempDateInDouble))
{
try
{
tempCompany.InvoiceDate = DateTime.FromOADate(tempDateInDouble);
}
catch (ArgumentException)
{ tempCompany.FormatErrors.Add(InputCompanyFormatError.InvoiceDateError); }
}
else
{
tempCompany.FormatErrors.Add(InputCompanyFormatError.InvoiceDateError);
}
tempStr = string.Empty;
lastProcessedItem = $"Wczytano datę faktury. Aktualna pozycja: wiersz {i}";
}
if ((_generateNotes && AreColumnsToGenerateNotesPresent()))
{
lastProcessedItem = $"Przed wczytaniem ID noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteID];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteID]]).Formula.ToString().Trim();
tempCompany.NoteID = tempStr;
tempStr = string.Empty;
lastProcessedItem = $"Wczytano ID Noty. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem tytułu Noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteTitle];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteTitle]]).Formula.ToString().Trim();
tempCompany.NoteTitle = tempStr;
tempStr = string.Empty;
lastProcessedItem = $"Wczytano tytuł Noty. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem netto noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteAmount];
tempStr = ((Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteAmount]]).Formula.ToString().Trim();
tempCompany.NoteNettoAmount = tempStr;
tempStr = string.Empty;
lastProcessedItem = $"Wczytano netto noty. Aktualna pozycja: wiersz {i}";
lastProcessedItem = $"Przed wczytaniem daty noty. Aktualna pozycja: wiersz {i}";
lastReadColumn = ColumnMapping[ImportColumnName.NoteDate];
var dataRange = (Range)worksheet.Cells[i, ColumnMapping[ImportColumnName.NoteDate]];
tempStr = dataRange.Formula.ToString();
tempCompany.NoteDate = GetDate(dataRange);
tempStr = string.Empty;
lastProcessedItem = $"Wczytano datę noty. Aktualna pozycja: wiersz {i}";
}
tempCompany.FormatErrors.AddRange(ValidateCompany(tempCompany));
CompaniesReadFromFile.Add(tempCompany);
}
}
catch (Exception e)
{
if (e is SpreadSheetReaderHeaderException || e is SpreadSheetReaderMissingColumnsException)
{
throw;
}
string errorMsg = string.Format("\nZłapano błąd w rzędzie gdy procedura była na kroku: {0}, w kolumnie: {2}, odczytała wartość: {1}.\n\n", lastProcessedItem, tempStr, lastReadColumn);
throw new SpreadSheetReaderException(errorMsg, e);
}
}