protected void txtTransporter_TextChanged(object sender, EventArgs e)
{
OnSelectingTransporter(this, new SelectingTransporterEventArgs()
{
TransporterName = txtTransporter.Text
});
ProfileManager profileManager;
if (txtTransporter.Text.Contains('|'))
{
profileManager = new ProfileManager(this);
transporterManager = new TransporterManager(this);
string[] identifications = txtTransporter.Text.Split('|');
string identification = identifications[0].ToString().Trim();
LegalEntityProfile legalEntityProfile = profileManager.GetLegalEntityProfile(identification);
if (legalEntityProfile != null)
{
transporter = transporterManager.GetTransporterByLegalEntityProfile(Page.Company.CompanyId, legalEntityProfile.LegalEntityProfileId);
}
ShowTransporter(transporter);
}
}
/// <summary>
/// This method updates a record in the table.
/// Change this method to alter how records are updated.
/// </summary>
/// <param name=original_entity>original_entity</param>
/// <param name=entity>entity</param>
public void SaveLegalEntityProfile(LegalEntityProfile entity)
{
LegalEntityProfile originalEntity = GetLegalEntityProfile(entity.LegalEntityProfileId);
originalEntity.CopyPropertiesFrom(entity);
originalEntity.ModifiedDate = DateTime.Now;
DbContext.SubmitChanges();
}
/// <summary>
/// This method verifies if the legalEntityProfile data be
/// into range of characters supported by database
/// </summary>
/// <param name="legalEntityProfile"></param>
/// <returns></returns>
private bool ValidateLegalEntityProfileData(LegalEntityProfile legalEntityProfile)
{
if (legalEntityProfile.CNPJ.Trim().Length > 18 || legalEntityProfile.CompanyName.Trim().Length > 100 || legalEntityProfile.FantasyName.Trim().Length > 100 ||
legalEntityProfile.Email.Trim().Length > 50 || legalEntityProfile.Phone.Trim().Length > 13 || legalEntityProfile.Fax.Trim().Length > 13)
{
return(false);
}
return(true);
}
/// <summary>
/// This method fills a LegalEntityProfile object with data from excel file
/// </summary>
/// <param name="excelReader"></param>
/// <returns>The LegalEntityProfile object filled from excel file</returns>
private LegalEntityProfile FillLegalEntityProfileData(OleDbDataReader excelReader)
{
var legalEntityProfile = new LegalEntityProfile();
legalEntityProfile.CompanyName = excelReader["RAZAO_SOCIAL"].ToString();
legalEntityProfile.CNPJ = excelReader["CNPJ"].ToString();
legalEntityProfile.PostalCode = excelReader["CEP"].ToString().Replace("-", "");
//
// Fields not required
//
if (ExistsColumm("NOME_FANTASIA"))
{
legalEntityProfile.FantasyName = excelReader["NOME_FANTASIA"].ToString();
}
if (ExistsColumm("INSC_ESTADUAL"))
{
legalEntityProfile.FantasyName = excelReader["INSC_ESTADUAL"].ToString();
}
if (ExistsColumm("INSC_MUNICIPAL"))
{
legalEntityProfile.FantasyName = excelReader["INSC_MUNICIPAL"].ToString();
}
if (ExistsColumm("TELEFONE"))
{
legalEntityProfile.Phone = excelReader["TELEFONE"].ToString();
}
if (ExistsColumm("NUMERO"))
{
legalEntityProfile.AddressNumber = excelReader["NUMERO"].ToString();
}
if (ExistsColumm("COMPLEMENTO"))
{
legalEntityProfile.AddressComp = excelReader["COMPLEMENTO"].ToString();
}
if (ExistsColumm("FAX"))
{
legalEntityProfile.Fax = excelReader["FAX"].ToString();
}
if (ExistsColumm("EMAIL"))
{
legalEntityProfile.Email = excelReader["EMAIL"].ToString();
}
return(legalEntityProfile);
}
private void BindControls(LegalEntityProfile legalEntityProfile)
{
string cnpj = legalEntityProfile.CNPJ.Replace(".", "").Replace("-", "").Replace("_", "").Replace("/", "");
if (!String.IsNullOrEmpty(cnpj))
{
//
// Populate the Control
//
txtCompanyName.Text = legalEntityProfile.CompanyName;
txtFantasyName.Text = legalEntityProfile.FantasyName;
txtMunicipalRegister.Text = legalEntityProfile.MunicipalRegister;
txtCNPJ.Text = legalEntityProfile.CNPJ;
txtIE.Text = legalEntityProfile.IE;
txtPhone.Text = legalEntityProfile.Phone.RemoveMask();
txtFax.Text = legalEntityProfile.Fax.RemoveMask();
//Set Address and Commercialddress
Address.PostalCode = legalEntityProfile.PostalCode;
Address.AddressComp = legalEntityProfile.AddressComp;
Address.AddressNumber = legalEntityProfile.AddressNumber;
RecoveryAddress.PostalCode = legalEntityProfile.RecoveryPostalCode;
RecoveryAddress.AddressComp = legalEntityProfile.RecoveryAddressComp;
RecoveryAddress.AddressNumber = legalEntityProfile.RecoveryAddressNumber;
txtEmail.Text = legalEntityProfile.Email;
Page.ViewState["LegalEntityProfileId"] = legalEntityProfile.LegalEntityProfileId;
Page.ViewState["ModifiedDate"] = legalEntityProfile.ModifiedDate;
//Page.ViewState["CNPJ"] = txtCNPJ.Text;
//
// Hide the search form, cause yet load the LegalEntity
//
entryForm.Visible = true;
searchForm.Visible = false;
}
}
/// <summary>
/// This method inserts a new record in the table.
/// Change this method to alter how records are inserted.
/// </summary>
/// <param name="entity"></param>
public void InsertLegalEntityProfile(LegalEntityProfile entity)
{
entity.ModifiedDate = DateTime.Now;
DbContext.LegalEntityProfiles.InsertOnSubmit(entity);
DbContext.SubmitChanges();
}
/// <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();
}
}
