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);
}
