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