/// <summary>
/// This method inserts a customer and relate it with a existing legalEntityProfile
/// </summary>
/// <param name="companyId"></param>
/// <param name="userId"></param>
/// <param name="excelReader"></param>
private void AttachCustomerToProfile(Int32 companyId, Int32 userId, OleDbDataReader excelReader)
{
var customer = new Customer();
var profileManager = new ProfileManager(this);
customer.CompanyId = companyId;
customer.ModifiedDate = DateTime.Now;
customer.ProfileId = profileManager.GetProfile(excelReader["CPF"].ToString()).ProfileId;
CustomerManager.Insert(customer);
if (ExistsColumm("CONTATO"))
{
if (!String.IsNullOrEmpty(excelReader["CONTATO"].ToString()))
{
SaveCustomerContact(customer, userId, excelReader["CONTATO"].ToString());
}
}
}
/// <summary>
/// Method to add a new COMPANY in a CUSTOMER of the HOST COMPANY
/// </summary>
/// <param name="newCompany"></param>
private Int32 AddCompanyAsCustomer(Company newCompany)
{
var customerManager = new CustomerManager(this);
var customer = new Customer();
customer.CompanyId = GetHostCompany().CompanyId;
customer.LegalEntityProfileId = newCompany.LegalEntityProfileId;
if (customerManager.ExistCustomer(customer))
customer = customerManager.GetCustomerByLegalEntityProfile(customer.CompanyId,
Convert.ToInt32(customer.LegalEntityProfileId));
else
customerManager.Insert(customer);
return customer.CustomerId;
}
/// <summary>
/// This method retrieves customers data from a excel file and stores in db
/// </summary>
/// <param name="companyId"></param>
/// <param name="userId"></param>
/// <param name="message"></param>
public void ImportDataFromExcelFile(Int32 companyId, Int32 userId, string fileName, out string message)
{
ProfileManager profileManager;
profileManager = new ProfileManager(this);
using (var excelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + @"; Extended Properties=Excel 5.0"))
{
var excelCommand = new OleDbCommand("Select * from [CLIENTES$]", excelConnection);
excelConnection.Open();
OleDbDataReader excelReader;
try
{
excelReader = excelCommand.ExecuteReader();
}
catch (OleDbException)
{
message = "Nome da planilha incorreto! Certifique-se que o nome da planilha no seu arquivo é CLIENTES";
return;
}
RetrieveExistentColunms(excelReader);
//
// Verifies if exist the necessary columms from excel file
//
if (!ExistRequiredInformationsToProfile(excelReader) && !ExistRequiredColumnsToLegalEntityProfile(excelReader) || !GetColumm("CEP", excelReader))
{
message = "Coluna obrigatória faltando no arquivo!";
return;
}
var addressManager = new AddressManager(this);
while (excelReader.Read())
{
totalRegisters++;
if (!String.IsNullOrEmpty(excelReader["CEP"].ToString()))
{
if (addressManager.GetAddress(excelReader["CEP"].ToString().Replace("-", "")) == null)
{
if (IsValidAddress(excelReader))
{
InsertNewAddress(excelReader);
}
else
{
errors++;
continue;
}
}
}
//
// Retrieve the cpnj's e cpf's from db for to compare with
// the cpnj's e cpf's retrieved from excel file
//
listCPF.AddRange(profileManager.GetCPFnumbers(companyId));
listCNPJ.AddRange(profileManager.GetCNPJnumbers(companyId));
var customer = new Customer();
var profile = new Profile();
var legalEntityProfile = new LegalEntityProfile();
var contact = new Contact();
if (ExistRequiredInformationsToProfile(excelReader) && !String.IsNullOrEmpty(excelReader["CPF"].ToString()) && !String.IsNullOrEmpty(excelReader["NOME"].ToString()))
{
//
// profile informations
//
if (ExistCpf(companyId, excelReader))
{
// verifies if the cpf is related with some customer
if (CustomerManager.GetCustomer(companyId, excelReader["CPF"].ToString()) == null)
{
AttachCustomerToProfile(companyId, userId, excelReader);
continue;
}
else
{
errors++;
}
continue;
}
profile = FillProfileData(excelReader);
if (!ValidateProfileData(profile) || !ValidateAddressData(profile.PostalCode ?? String.Empty, profile.AddressNumber ?? String.Empty, profile.AddressComp ?? String.Empty))
{
errors++;
continue;
}
//save the customer with profile
customer.CompanyId = companyId;
customer.ModifiedDate = DateTime.Now;
customer.Profile = profile;
CustomerManager.Insert(customer);
//
// insert a contact and related it with customer
//
if (ExistsColumm("CONTATO"))
{
if (!String.IsNullOrEmpty(excelReader["CONTATO"].ToString()))
{
SaveCustomerContact(customer, userId, excelReader["CONTATO"].ToString());
}
}
listCPF.Add(excelReader["CPF"].ToString());
continue;
}
//
// LegalEntityProfile informations
//
if (ExistRequiredColumnsToLegalEntityProfile(excelReader) && !String.IsNullOrEmpty(excelReader["CNPJ"].ToString()) && !String.IsNullOrEmpty(excelReader["RAZAO_SOCIAL"].ToString()))
{
if (ExistCnpj(companyId, excelReader))
{
// verifies if the cnpj is related with some customer
if (CustomerManager.GetCustomer(companyId, excelReader["CNPJ"].ToString()) == null)
{
AttachCustomerToLegalEntityProfile(companyId, userId, excelReader);
continue;
}
else
{
errors++;
}
continue;
}
legalEntityProfile = FillLegalEntityProfileData(excelReader);
if (!ValidateLegalEntityProfileData(legalEntityProfile) ||
!ValidateAddressData(profile.PostalCode ?? String.Empty, profile.AddressNumber ?? String.Empty, profile.AddressComp ?? String.Empty))
{
errors++;
continue;
}
//
// insert a customer
//
customer.CompanyId = companyId;
customer.ModifiedDate = DateTime.Now;
customer.LegalEntityProfile = legalEntityProfile;
CustomerManager.Insert(customer);
//
// insert a contact and related it with customer
//
if (ExistsColumm("CONTATO"))
{
if (!String.IsNullOrEmpty(excelReader["CONTATO"].ToString()))
{
SaveCustomerContact(customer, userId, excelReader["CONTATO"].ToString());
}
}
listCNPJ.Add(excelReader["CNPJ"].ToString());
}
}
message = "Total de Registros: " + totalRegisters + "; Registros importados com sucesso: " + Convert.ToInt32(totalRegisters - errors) +
"; Registros não importados por incompatibilidade de dados: " + errors;
excelConnection.Close();
}
}