public void Clear(DO.Requisition requisition) { string DBAccountsCustomerImport = GetDbAccountsCustomerImport(); using (var connection = new SqlConnection(strConnection)) { using (var command = new SqlCommand(string.Format("{0}.[SPRK_Clear]", DBAccountsCustomerImport), connection)) { command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@Requisition", requisition.Code); connection.Open(); command.ExecuteNonQuery(); connection.Close(); } } }
public bool ImportCustomer(DO.Requisition requisition) { string DBAccountsCustomerImport = GetDbAccountsCustomerImport(); var dbrequisition = requisition as DO.DBRequisition; using (var connection = new SqlConnection(strConnection)) { using (var command = new SqlCommand(string.Format("{0}.[SPRK_CustomerECImport]", DBAccountsCustomerImport), connection)) { command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@linkedservername", dbrequisition.LinkedServerName); command.Parameters.AddWithValue("@databasename", dbrequisition.DataBaseName); command.Parameters.AddWithValue("@Requisition", requisition.Code); connection.Open(); command.ExecuteNonQuery(); connection.Close(); } } return(true); }
public List <DTO.Account> GetCustomers(DO.Requisition requisition, int skip, int take) { string DBAccountsCustomerImport = GetDbAccountsCustomerImport(); var dicAddresses = GetAddresses(requisition); var accounts = new List <DTO.Account>(); using (var connection = new SqlConnection(strConnection)) { using (var command = new SqlCommand(string.Format("{0}.[SPRK_UserList]", DBAccountsCustomerImport), connection)) { command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@Requisition", requisition.Code); command.Parameters.AddWithValue("@Skip", skip); command.Parameters.AddWithValue("@Take", take); connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { var customer = new DTO.Customer(); var document = string.Empty; if (reader["ConTipo"].AsInt() == 1) { document = reader["ConCPF"].ToString().Trim(); string gender = null; if (!reader["UsuSexo"].AsString().IsNullorEmpty()) { gender = reader["UsuSexo"].AsInt() == 1 ? "M" : "F"; } customer = new DTO.Person { FirstName = reader["ConNome"].ToString().Trim(), LastName = reader["ConSobreNome"].ToString().Trim(), Cpf = reader["ConCPF"].ToString().Trim(), Rg = reader["ConRG"].ToString().Trim(), BirthDate = reader["ConDtNascimento"].AsDateTime(), Gender = gender, Type = CustomerType.Person }; } else { document = reader["ConCNPJ"].ToString().Trim(); customer = new DTO.Company { CompanyName = reader["ConRazaoSocial"].ToString().Trim(), TradeName = reader["ConNomeFantasia"].ToString().Trim(), Cnpj = reader["ConCNPJ"].ToString().Trim(), Ie = reader["ConIE"].ToString().Trim(), Im = reader["ConIM"].ToString().Trim(), Type = CustomerType.Company }; } customer.Status = reader["ConStatus"].AsInt() != 0 || reader["ConStatus"].AsInt() != 2 ? true : false; customer.SaveDate = reader["ConDtInclusao"].AsDateTime(); customer.UpdateDate = reader["ConDtAtualizacao"].AsDateTime(); customer.Addresses = new List <DTO.Address>(); if (dicAddresses.ContainsKey(reader["ConCodigo"].AsInt())) { customer.Addresses.AddRange(dicAddresses[reader["ConCodigo"].AsInt()]); } var account = new DTO.Account() { Email = reader["UsuEmail"].ToString().Trim(), Login = reader["UsuLogin"].ToString().Trim(), Document = document, Password = reader["UsuSenhaBkp"].ToString().Trim(), Customer = customer, Status = customer.Status, Removed = false }; accounts.Add(account); } } } } return(accounts); }
private Dictionary <int, List <DTO.Address> > GetAddresses(DO.Requisition requisition) { string DBAccountsCustomerImport = GetDbAccountsCustomerImport(); var result = new Dictionary <int, List <DTO.Address> >(); using (var connection = new SqlConnection(strConnection)) { using (var command = new SqlCommand(string.Format("{0}.[SPRK_AddressList]", DBAccountsCustomerImport), connection)) { command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@Requisition", requisition.Code); connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { var address = new DTO.Address() { ContactName = reader["EndNome"].ToString().Trim(), Type = GetAddressType(reader["EndTipo"].AsInt()), Purpose = GetAddressPurposeType(reader["EndFinalidade"].AsInt()), Street = string.Format("{0} {1}", (StreetTypeEC)reader["EndTipoLogradouro"].AsInt(), reader["EndLogradouro"].ToString().Trim()), Number = reader["EndNumero"].ToString().Trim(), District = reader["EndBairro"].ToString().Trim(), City = reader["EndCidade"].ToString().Trim(), State = reader["EndEstado"].ToString().Trim(), ZipCode = reader["EndCEP"].ToString().Trim(), Complement = reader["EndComplemento"].ToString().Trim(), Country = reader["EndPais"].ToString().Trim(), Status = true, Removed = false }; address.Phones = new List <DTO.Phone>(); if (!reader["EndDDD1"].ToString().Trim().ClearStrings().IsNullOrWhiteSpace()) { address.Phones.Add(new DTO.Phone() { CodeCountry = address.Country, DDD = reader["EndDDD1"].ToString().Trim(), Number = reader["EndTelefone1"].ToString().Trim().ClearStrings(), Extension = reader["EndRamal1"].ToString().Trim().ClearStrings(), PhoneType = PhoneType.Residencial }); } if (!reader["EndDDD2"].ToString().Trim().ClearStrings().IsNullOrWhiteSpace()) { address.Phones.Add(new DTO.Phone() { CodeCountry = address.Country, DDD = reader["EndDDD2"].ToString().Trim(), Number = reader["EndTelefone2"].ToString().Trim().ClearStrings(), Extension = reader["EndRamal2"].ToString().Trim().ClearStrings(), PhoneType = PhoneType.Comercial }); } if (!reader["EndDDD3"].ToString().Trim().ClearStrings().IsNullOrWhiteSpace()) { address.Phones.Add(new DTO.Phone() { CodeCountry = address.Country, DDD = reader["EndDDD3"].ToString().Trim(), Number = reader["EndTelefone3"].ToString().Trim().ClearStrings(), Extension = reader["EndRamal3"].ToString().Trim().ClearStrings(), PhoneType = PhoneType.Outros }); } if (!reader["EndDDDCelular"].ToString().Trim().ClearStrings().IsNullOrWhiteSpace()) { address.Phones.Add(new DTO.Phone() { CodeCountry = address.Country, DDD = reader["EndDDDCelular"].ToString().Trim(), Number = reader["EndCelular"].ToString().Trim().ClearStrings().PadLeft(9, '9'), PhoneType = PhoneType.Celular }); } if (!reader["EndDDDFax"].ToString().Trim().ClearStrings().IsNullOrWhiteSpace()) { address.Phones.Add(new DTO.Phone() { CodeCountry = address.Country, DDD = reader["EndDDDFax"].ToString().Trim(), Number = reader["EndFax"].ToString().Trim().ClearStrings(), PhoneType = PhoneType.Fax }); } if (result.ContainsKey(reader["ConCodigo"].AsInt())) { result[reader["ConCodigo"].AsInt()].Add(address); } else { result.Add(reader["ConCodigo"].AsInt(), new List <DTO.Address>() { address }); } } } } } return(result); }