public static SellerModel BuscaPorEmailSeller(string email) { var ret = new SellerModel(); using (var comando = new SqlCommand()) { comando.Connection = Conexao.connection; comando.CommandText = string.Format( "select * from UserSys where email = '{0}'", email); Conexao.Conectar(); var reader = comando.ExecuteReader(); while (reader.Read()) { ret.Id = (int)reader["Id"]; ret.Name = (string)reader["Login"]; ret.UserRoleId = (int)reader["UserRoleId"]; } Conexao.Desconectar(); } return(ret); }
public static List <RegionModel> BuscaRegion(int?CityId) { var ret = new List <RegionModel>(); using (var comando = new SqlCommand()) { comando.Connection = Conexao.connection; if (CityId > 0) { comando.CommandText = string.Format( "select * from Region " + "INNER JOIN City on City.RegionId=Region.Id " + "where City.id = '{0}' ", CityId); } else { comando.CommandText = string.Format( "select * from Region "); } Conexao.Conectar(); var reader = comando.ExecuteReader(); while (reader.Read()) { ret.Add(new RegionModel { Id = (int)reader["Id"], Name = (string)reader["Name"] }); } Conexao.Desconectar(); } return(ret); }
public static List <SellerModel> BuscaSeller(string email) { var ret = new List <SellerModel>(); SellerModel Seller = BuscaPorEmailSeller(email); using (var comando = new SqlCommand()) { comando.Connection = Conexao.connection; if (Seller.UserRoleId == 1) { comando.CommandText = string.Format( "select * from UserSys "); } else { comando.CommandText = string.Format( "select * from UserSys where id = '{0}'", Seller.Id); } Conexao.Conectar(); var reader = comando.ExecuteReader(); while (reader.Read()) { ret.Add(new SellerModel { Id = (int)reader["Id"], Name = (string)reader["Login"] }); } Conexao.Desconectar(); } return(ret); }
public static List <CityModel> BuscaCitys() { var ret = new List <CityModel>(); /* * conexao.ConnectionString = "Data Source=MATHEUS-PC;Initial Catalog=GerenciadorDeContatos;Integrated Security=True"; * conexao.Open(); */ using (var comando = new SqlCommand()) { comando.Connection = Conexao.connection; /* * comando.CommandText = string.Format( * "select * from Customer where Name='{0}' and GenderId='{1}' and CityId='{2}' " + * "and RegionId='{3}' and LastPurchase>='{4}' and LastPurchase <='{5}' and ClassificationId='{6}'", Name, GenderId, CityId, RegionId, LastPurchaseIni, LastPurchaseFim, ClassificationId); */ comando.CommandText = string.Format( "select * from City "); Conexao.Conectar(); var reader = comando.ExecuteReader(); while (reader.Read()) { ret.Add(new CityModel { Id = (int)reader["Id"], Name = (string)reader["Name"], RegionId = (int)reader["RegionId"] }); } Conexao.Desconectar(); } return(ret); }
public static List <CustomerListModel> BuscaContatos(string Name, int?GenderId, int?CityId, int?RegionId, DateTime?LastPurchaseIni, DateTime?LastPurchaseFim, int?ClassificationId, int?SellerId, string login) { var ret = new List <CustomerListModel>(); String Filtro = ""; using (var comando = new SqlCommand()) { comando.Connection = Conexao.connection; /* * comando.CommandText = string.Format( * "select * from Customer where Name='{0}' and GenderId='{1}' and CityId='{2}' " + * "and RegionId='{3}' and LastPurchase>='{4}' and LastPurchase <='{5}' and ClassificationId='{6}'", Name, GenderId, CityId, RegionId, LastPurchaseIni, LastPurchaseFim, ClassificationId); */ comando.CommandType = CommandType.Text; comando.CommandText = String.Format("select Customer.Id,Customer.Name,Customer.GenderId,Customer.CityId,Customer.RegionId,Customer.LastPurchase,Customer.ClassificationId,Customer.Phone,Customer.UserId,City.Name as Cidade,Classification.Name as Class,Region.Name as Regiao,Gender.Name as Sexo,UserSys.Login as Usuario " + "from Customer " + "INNER JOIN City ON Customer.CityId=City.Id " + "INNER JOIN Classification ON Customer.ClassificationId=Classification.Id " + "INNER JOIN Gender ON Customer.GenderId=Gender.Id " + "INNER JOIN Region ON Customer.RegionId=Region.Id " + "INNER JOIN UserSys ON Customer.UserId=UserSys.Id " + "INNER JOIN UserRole ON UserSys.UserRoleId=UserRole.Id "); if (!String.IsNullOrEmpty(Name)) { if (String.IsNullOrEmpty(Filtro)) { Filtro += String.Format("where Customer.Name LIKE '%{0}%'", Name); } else { Filtro += String.Format(" and Customer.Name LIKE '%{0}%'", Name); } } if (GenderId.HasValue) { if (String.IsNullOrEmpty(Filtro)) { Filtro += String.Format("where Customer.GenderId = '{0}'", GenderId); } else { Filtro += String.Format(" and Customer.GenderId = '{0}'", GenderId); } } if (CityId.HasValue) { if (String.IsNullOrEmpty(Filtro)) { Filtro += String.Format("where Customer.CityId = '{0}'", CityId); } else { Filtro += String.Format(" and Customer.CityId = '{0}'", CityId); } } if (RegionId.HasValue) { if (String.IsNullOrEmpty(Filtro)) { Filtro += String.Format("where Customer.RegionId = '{0}'", RegionId); } else { Filtro += String.Format(" and Customer.RegionId = '{0}'", RegionId); } } if (LastPurchaseIni.HasValue) { if (String.IsNullOrEmpty(Filtro)) { Filtro += String.Format("where Customer.LastPurchase >= '{0}'", LastPurchaseIni); } else { Filtro += String.Format(" and Customer.LastPurchase >= '{0}'", LastPurchaseIni); } } if (LastPurchaseFim.HasValue) { if (String.IsNullOrEmpty(Filtro)) { Filtro += String.Format("where Customer.LastPurchase <= '{0}'", LastPurchaseFim); } else { Filtro += String.Format(" and Customer.LastPurchase <= '{0}'", LastPurchaseFim); } } if (ClassificationId.HasValue) { if (String.IsNullOrEmpty(Filtro)) { Filtro += String.Format("where Customer.ClassificationId = '{0}'", ClassificationId); } else { Filtro += String.Format(" and Customer.ClassificationId = '{0}'", ClassificationId); } } if (SellerId.HasValue && SellerId > 1) { if (String.IsNullOrEmpty(Filtro)) { Filtro += String.Format("where Customer.UserId = '{0}'", SellerId); } else { Filtro += String.Format(" and Customer.UserId = '{0}'", SellerId); } } if (!String.IsNullOrEmpty(login)) { UsuarioModel Usuario = UsuarioModel.BuscaUsuario(login); if (Usuario != null && Usuario.UserRoleId > 1) { Filtro += String.Format(" and Customer.UserId = '{0}'", Usuario.UserRoleId); } } comando.CommandText += Filtro; Conexao.Conectar(); var reader = comando.ExecuteReader(); while (reader.Read()) { ret.Add(new CustomerListModel { Id = (int)reader["Id"], Name = (string)reader["Name"], GenderId = (int)reader["GenderId"], CityId = (int)reader["CityId"], RegionId = (int)reader["RegionId"], LastPurchase = (DateTime)reader["LastPurchase"], ClassificationId = (int)reader["ClassificationId"], Phone = (string)reader["Phone"], UserId = (int)reader["UserId"], CityName = (string)reader["Cidade"], ClassificationName = (string)reader["Class"], RegionName = (string)reader["Regiao"], GenderName = (string)reader["Sexo"], SellerName = (string)reader["Usuario"] }); } Conexao.Desconectar(); } return(ret); }
public static UsuarioModel BuscaUsuario(string usuario) { var ret = new UsuarioModel(); using (var comando = new SqlCommand()) { comando.Connection = Conexao.connection; comando.CommandText = string.Format( "select * from UserSys where Login = '******' or Email = '{1}'", usuario, usuario); Conexao.Desconectar(); Conexao.Desconectar(); Conexao.Conectar(); var reader = comando.ExecuteReader(); while (reader.Read()) { ret.Id = (int)reader["Id"]; ret.Login = (string)reader["Login"]; ret.Email = (string)reader["Email"]; ret.UserRoleId = (int)reader["UserRoleId"]; } Conexao.Desconectar(); } return(ret); }