public async Task <(IEnumerable <HierarchyFileDataError> errorList, IEnumerable <HierarchyFileData> dataList)> ValidateManagerTab(HierarchyFile hierarchyFile, ExcelWorksheet managerTab)
{
var ListHierarchyFileDataError = new List <HierarchyFileDataError>();
var ListHierarchyFileData = new List <HierarchyFileData>();
for (var row = 2; row <= managerTab.Dimension.End.Row; row++)
{
if (managerTab.Cells[row, 1].Value != null &&
managerTab.Cells[row, 2].Value != null &&
managerTab.Cells[row, 3].Value != null &&
managerTab.Cells[row, 4].Value != null &&
managerTab.Cells[row, 5].Value != null &&
managerTab.Cells[row, 6].Value != null)
{
var hierarchyFileData = new HierarchyFileData
{
Cnpj = (managerTab.Cells[row, 3].Value != null) ? managerTab.Cells[row, 3].Value.ToString().PadLeft(14, '0').Replace("-", "").Replace("/", "").Replace(".", "").Replace(".0", "") : String.Empty,
Cpf = (managerTab.Cells[row, 4].Value != null) ? managerTab.Cells[row, 4].Value.ToString().PadLeft(11, '0').Replace("-", "").Replace(".", "").Replace(".0", "") : String.Empty,
ShopCode = (managerTab.Cells[row, 2].Value != null) ? managerTab.Cells[row, 2].Value.ToString().Replace(".0", "") : String.Empty,
Name = (managerTab.Cells[row, 5].Value != null) ? managerTab.Cells[row, 5].Value.ToString() : String.Empty,
Resale = (managerTab.Cells[row, 1].Value != null) ? managerTab.Cells[row, 1].Value.ToString() : String.Empty,
Spreedsheet = "GERENTES_REGIONAIS",
CreatedAt = DateTime.Now,
HierarchyFile = hierarchyFile,
Off = (managerTab.Cells[row, 6].Value != null) ? managerTab.Cells[row, 6].Value.ToString().Trim() : String.Empty,
};
ListHierarchyFileDataError.AddRange(await ValidateSpreadsheetRow(hierarchyFileData, hierarchyFile, row, 2, hierarchyFileData.Spreedsheet));
ListHierarchyFileData.Add(hierarchyFileData);
}
}
return(ListHierarchyFileDataError, ListHierarchyFileData);
}
public async Task <IEnumerable <HierarchyFileDataError> > ValidateSpreadsheetRow(HierarchyFileData hierarchyFileData, HierarchyFile hierarchyFile, int line, int tab, string spreadsheet)
{
var ListHierarchyFileDataError = new List <HierarchyFileDataError>();
if (String.IsNullOrEmpty(hierarchyFileData.Resale))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = "Revenda em branco.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
if (String.IsNullOrEmpty(hierarchyFileData.ShopCode))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = "Código Loja em branco.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
if (String.IsNullOrEmpty(hierarchyFileData.Cnpj))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = "CNPJ em branco.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
else if (!String.IsNullOrEmpty(hierarchyFileData.Cnpj))
{
if (!Validation.ValidaCnpj(hierarchyFileData.Cnpj))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = $"CNPJ {hierarchyFileData.Cnpj} inválido.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
else
{
var hasCnpj = await _shopRepository.CustomFind(x => x.Cnpj.Equals(hierarchyFileData.Cnpj));
if (!hasCnpj.Any())
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = $"CNPJ {hierarchyFileData.Cnpj} não encontrado.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
}
}
if (String.IsNullOrEmpty(hierarchyFileData.Cpf))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = "CPF em branco.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
else if (!String.IsNullOrEmpty(hierarchyFileData.Cpf))
{
if (!Validation.ValidaCPF(hierarchyFileData.Cpf))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = "CPF inválido.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
}
if (String.IsNullOrEmpty(hierarchyFileData.Name))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = "Nome em branco.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
else if (!hierarchyFileData.Name.ToCharArray().All(t => !char.IsDigit(t)))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = "Nome inválido.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
if (String.IsNullOrEmpty(hierarchyFileData.Off))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = "Desligado em branco.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
else if (hierarchyFileData.Off.ToUpper() != "SIM" && hierarchyFileData.Off.ToUpper() != "NÃO" && hierarchyFileData.Off.ToUpper() != "NAO")
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = "Desligado inválido.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
if (tab != 1)
{
return(ListHierarchyFileDataError);
}
if (String.IsNullOrEmpty(hierarchyFileData.Office))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = "Cargo em branco.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
else
{
if (!Validation.IsOffice(hierarchyFileData.Office.Replace(" ", "")))
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = $"Cargo {hierarchyFileData.Office} não encontrado.",
HierarchyFile = hierarchyFile,
Line = line,
Spreedsheet = spreadsheet
});
}
}
return(ListHierarchyFileDataError);
}
public async Task <(IEnumerable <HierarchyFileDataError> errorList, IEnumerable <HierarchyFileData> dataList)> ValidateSalesmanTab(HierarchyFile hierarchyFile, ExcelWorksheet salesmanTab)
{
var ListHierarchyFileDataError = new List <HierarchyFileDataError>();
var ListHierarchyFileData = new List <HierarchyFileData>();
for (var row = 2; row <= salesmanTab.Dimension.End.Row; row++)
{
if (salesmanTab.Cells[row, 1].Value != null &&
salesmanTab.Cells[row, 2].Value != null &&
salesmanTab.Cells[row, 3].Value != null &&
salesmanTab.Cells[row, 4].Value != null &&
salesmanTab.Cells[row, 5].Value != null &&
salesmanTab.Cells[row, 6].Value != null &&
salesmanTab.Cells[row, 7].Value != null)
{
var hierarchyFileData = new HierarchyFileData
{
Cnpj = (salesmanTab.Cells[row, 3].Value != null) ? salesmanTab.Cells[row, 3].Value.ToString().PadLeft(14, '0').Replace("-", "").Replace("/", "").Replace(".", "").Replace(".0", "") : String.Empty,
Cpf = (salesmanTab.Cells[row, 4].Value != null) ? salesmanTab.Cells[row, 4].Value.ToString().PadLeft(11, '0').Replace("-", "").Replace(".", "").Replace(".0", "") : String.Empty,
ShopCode = (salesmanTab.Cells[row, 2].Value != null) ? salesmanTab.Cells[row, 2].Value.ToString().Replace(".0", "") : String.Empty,
Name = (salesmanTab.Cells[row, 5].Value != null) ? salesmanTab.Cells[row, 5].Value.ToString() : String.Empty,
Off = (salesmanTab.Cells[row, 7].Value != null) ? salesmanTab.Cells[row, 7].Value.ToString() : String.Empty,
Office = Validation.RemoverAcentos((salesmanTab.Cells[row, 6].Value != null) ? (salesmanTab.Cells[row, 6].Value.ToString().ToUpper().Trim() == "SUB GERENTE" || salesmanTab.Cells[row, 6].Value.ToString().ToUpper().Trim() == "SUB-GERENTE") ? "GERENTE" : salesmanTab.Cells[row, 6].Value.ToString() : String.Empty),
Resale = (salesmanTab.Cells[row, 1].Value != null) ? salesmanTab.Cells[row, 1].Value.ToString() : String.Empty,
Spreedsheet = "VENDEDORES_GERENTES",
CreatedAt = DateTime.Now,
HierarchyFile = hierarchyFile,
};
ListHierarchyFileDataError.AddRange(await ValidateSpreadsheetRow(hierarchyFileData, hierarchyFile, row, 1, hierarchyFileData.Spreedsheet));
if (ListHierarchyFileData.Any() && ListHierarchyFileData.Where(x => x.Cpf.Equals(hierarchyFileData.Cpf)).Any())
{
ListHierarchyFileDataError.Add(new HierarchyFileDataError
{
CreatedAt = DateTime.Now,
Description = $"CPF {hierarchyFileData.Cpf} duplicado",
HierarchyFile = hierarchyFile,
Line = row,
Spreedsheet = hierarchyFileData.Spreedsheet
});
}
ListHierarchyFileData.Add(hierarchyFileData);
}
}
return(ListHierarchyFileDataError, ListHierarchyFileData);
}