public ERoles[] GetById(int userId)
{
List <ERoles> roles = new List <ERoles>();
var dbContext = new SQLDbContext();
string SQL = @"SELECT * FROM VW_Usuarios_Perfils WHERE IdUsuario = @IdUsuario";
var parametros = new SqlParameter[] {
new SqlParameter("@IdUsuario", userId)
};
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
foreach (DataRow dataRow in dtResult.Rows)
{
int perfilId = Convert.ToInt32(dataRow["IdPerfil"]);
ERoles role = EnumHelper.PerfilIdParaPerfil(perfilId);
roles.Add(role);
}
return(roles.ToArray());
}
public IList <City> GetCities(int stateId)
{
var cities = new List <City>();
var dbContext = new SQLDbContext();
string SQL = @"SELECT * FROM Cidades WHERE IdEstado = @IdEstado";
var parametros = new SqlParameter[] {
new SqlParameter("@IdEstado", stateId)
};
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
foreach (DataRow dataRow in dtResult.Rows)
{
var city = new City(
Convert.ToInt32(dataRow["Id"]),
Convert.ToString(dataRow["Cidade"]),
Convert.ToInt32(dataRow["IdEstado"])
);
cities.Add(city);
}
return(cities);
}
public User GetById(int id)
{
User user = null;
var dbContext = new SQLDbContext();
string SQL = @"SELECT * FROM VW_Usuarios WHERE Id = @Id";
var parametros = new SqlParameter[] {
new SqlParameter("@Id", id)
};
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
if (dtResult.Rows.Count > 0)
{
DataRow dataRow = dtResult.Rows[0];
user = new User(
Convert.ToInt32(dataRow["Id"]),
dataRow["Nome"].ToString(),
dataRow["Login"].ToString(),
dataRow["Senha"].ToString(),
Convert.ToDateTime(dataRow["DataCadastro"])
);
var roles = _perfilRepository.GetById(user.Id);
user.Roles = roles;
}
return(user);
}
public IList <State> GetStates(int countryId)
{
var states = new List <State>();
var dbContext = new SQLDbContext();
string SQL = @"SELECT * FROM Estados WHERE IdPais = @IdPais";
var parametros = new SqlParameter[] {
new SqlParameter("@IdPais", countryId)
};
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
foreach (DataRow dataRow in dtResult.Rows)
{
var state = new State(
Convert.ToInt32(dataRow["Id"]),
Convert.ToString(dataRow["Estado"]),
Convert.ToInt32(dataRow["IdPais"])
);
states.Add(state);
}
return(states);
}
public IList <Fluxo> ListarFluxo(DateTime startDate, DateTime endDate, int statusId = 0, string cpf = "", string name = "")
{
var fluxos = new List <Fluxo>();
var dbContext = new SQLDbContext();
SqlParameter[] parametros = new SqlParameter[] {
new SqlParameter("@StartDate", startDate.ToShortDateString()),
new SqlParameter("@EndDate", endDate.AddDays(1).ToShortDateString()),
new SqlParameter("@IdStatus", statusId),
new SqlParameter("@CPF", cpf ?? ""),
new SqlParameter("@Name", name ?? ""),
};
string SQL = "SELECT * FROM VW_Fluxo_Aprovacao WHERE DataCriacao >= @StartDate AND DataCriacao <= @EndDate";
if (statusId > 0)
{
SQL += " AND IdStatus = @IdStatus";
}
if (!string.IsNullOrEmpty(cpf))
{
SQL += " AND CPF = @CPF";
}
if (!string.IsNullOrEmpty(name))
{
SQL += " AND Cliente LIKE '%@Name%'";
}
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
foreach (DataRow dataRow in dtResult.Rows)
{
var userId = Convert.ToInt32(dataRow["IdUsuario"]);
var user = _userRepository.GetById(userId);
var clientId = Convert.ToInt32(dataRow["IdCliente"]);
var client = _clientRepository.GetById(clientId);
var fluxo = new Fluxo(
client,
user,
status: EnumHelper.StatusIdParaStatus(dataRow["IdStatus"].ToString()),
createDate: Convert.ToDateTime(dataRow["DataCriacao"])
);
fluxos.Add(fluxo);
}
return(fluxos);
}
public IList <int> ListarIdsClientesIndisponiveis(int userId)
{
var ids = new List <int>();
var dbContext = new SQLDbContext();
SqlParameter[] parametros = new SqlParameter[] {
new SqlParameter("@IdUsuario", userId)
};
string SQL = @"SELECT * FROM FC_Listar_IdsClientes_Indisponiveis(@IdUsuario)";
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
foreach (DataRow dataRow in dtResult.Rows)
{
ids.Add(Convert.ToInt32(dataRow["IdCliente"]));
}
return(ids);
}
public IList <User> GetAll()
{
var users = new List <User>();
var dbContext = new SQLDbContext();
var SQL = @$ "SELECT * FROM VW_Usuarios ORDER BY Id DESC";
DataTable dtresult = dbContext.ExecutarConsulta(SQL);
foreach (DataRow dataRow in dtresult.Rows)
{
var user = new User(
Convert.ToInt32(dataRow["Id"]),
dataRow["Nome"].ToString(),
dataRow["Login"].ToString(),
dataRow["Senha"].ToString(),
Convert.ToDateTime(dataRow["DataCadastro"])
);
users.Add(user);
}
return(users);
}
public IList <Country> GetCountries()
{
var countries = new List <Country>();
var dbContext = new SQLDbContext();
string SQL = @"SELECT * FROM Paises";
DataTable dtResult = dbContext.ExecutarConsulta(SQL);
foreach (DataRow dataRow in dtResult.Rows)
{
var country = new Country(
Convert.ToInt32(dataRow["Id"]),
Convert.ToString(dataRow["Pais"]),
Convert.ToString(dataRow["Sigla"])
);
countries.Add(country);
}
return(countries);
}
