// Primeiramente ficou um método muito grande. // Observação: Tive um problemas para descobrir que alguns GUIDs estavam em lowercase ou uppercase. // Ou seja, quando comparava-se dois GUIDs não se obtia nenhum resultados. private IEnumerable<Client> ResultFromClient(ClientFilter filter) { ICollection<Client> clients = null; // Connection with the database. (Not the right way. Better to use DBContext). Tive problemas com o Entity Framework. using (var cn = new SqlConnection( @"Data Source=(LocalDB)\v11.0; AttachDbFilename='C:\Cursos\Testes\MVCWebApp\App_Data\Database.mdf'; Integrated Security=True")) { // Bring all data. Again it is not right. "Work around :)" string sql = string.Concat(@"SELECT c.ClientId, c.Name, c.Phone, c.Gender, c.LastPurchase, ", "seller.Id, seller.UserName, seller.Discriminator, ", "region.RegionId, region.RegionName, ", "city.CityId, city.CityName, ", "class.ClassificationId, class.ClassificationName ", "from Client c ", "inner join AspNetUsers seller on c.SellerId = seller.Id ", "inner join Region region on c.RegionId = region.RegionId ", "inner join City city on region.CityId = city.CityId ", "inner join Classification class on c.ClassificationId = class.ClassificationId "); var cmd = new SqlCommand(sql, cn); cn.Open(); SqlDataReader reader = cmd.ExecuteReader(); // If data was found, load client list to show in the Customer List grid. if (reader.HasRows) { clients = new List<Client>(); while (reader.Read()) { // Cria o objeto Client e joga na lista Clients Client client = new Client() { Id = (Guid)reader.GetValue(0), Name = reader.GetValue(1).ToString(), Phone = string.Format("{0: ## ####-##-##}", reader.GetValue(2).ToString()), Gender = reader.GetValue(3).ToString(), LastPurchase = (DateTime)reader.GetValue(4), Seller = new AspNetUsers () { Id = reader.GetValue(5).ToString(), UserName = reader.GetValue(6).ToString(), Discriminator = reader.GetValue(7).ToString(), }, Region = new Region() { Id = (Guid)reader.GetValue(8), Name = reader.GetValue(9).ToString(), City = new City() { Id = (Guid)reader.GetValue(10), Name = reader.GetValue(11).ToString(), } }, Classification = new Classification() { Id = (Guid)reader.GetValue(12), Name = reader.GetValue(13).ToString(), } }; clients.Add(client); } reader.Dispose(); cmd.Dispose(); } else { reader.Dispose(); cmd.Dispose(); } } // Achei feio isto aqui, mas foi a maneira que achei para buscar os dados. Devem existir outras, mas foi esta que me veio à cabeça. // Name if (!string.IsNullOrEmpty(filter.Name)) clients = clients.Where(s => s.Name.ToUpper().Contains(filter.Name.ToUpper())).ToList(); // Gender (M/F) if (!string.IsNullOrEmpty(filter.Gender)) clients = clients.Where(s => s.Gender.ToUpper().Equals(filter.Gender.ToUpper())).ToList(); // Guid Region if (!string.IsNullOrEmpty(filter.GuidRegion)) clients = clients.Where(s => s.Region.Id.ToString().ToLower().Equals(filter.GuidRegion.ToLower())).ToList(); // Guid Cidade if (!string.IsNullOrEmpty(filter.GuidCity)) clients = clients.Where(s => s.Region.City.Id.ToString().ToLower().Equals(filter.GuidCity.ToLower())).ToList(); // Guid Classification if (!string.IsNullOrEmpty(filter.GuidClassification)) clients = clients.Where(s => s.Classification.Id.ToString().ToLower().Equals(filter.GuidClassification.ToLower())).ToList(); // Guid Seler if (!string.IsNullOrEmpty(filter.GuidSeller)) clients = clients.Where(s => s.Seller.Id.ToLower().Equals(filter.GuidSeller.ToLower()) ).ToList(); // Quando tiver data de início e não tiver data de fim. Considera-se o dia corrente como data final. // No entanto se existir data final e não existir inicial, pega-se a data mínima. if (filter.StartDate.HasValue && !filter.EndDate.HasValue) filter.EndDate = System.DateTime.Now.Date; else if (!filter.StartDate.HasValue && !filter.EndDate.HasValue) { filter.EndDate = System.DateTime.Now.Date; filter.StartDate = System.DateTime.MinValue; } else filter.StartDate = System.DateTime.MinValue; // Verifica o intervalo de datas; clients = clients.Where(c => c.LastPurchase >= filter.StartDate.Value && c.LastPurchase <= filter.EndDate.Value).ToList(); // Ser não existir ordem, retorna ordenado pelo nome do cliente. if (string.IsNullOrEmpty(filter.SortOrder)) return clients.OrderBy(s => s.Name).ToList(); // Ordena as colunas da grid. switch (filter.SortOrder) { case "name_desc": clients = clients.OrderByDescending(s => s.Name).ToList(); break; case "City": clients = clients.OrderBy(s => s.Region.City.Name).ToList(); break; case "city_desc": clients = clients.OrderByDescending(s => s.Region.City.Name).ToList(); break; case "Region": clients = clients.OrderBy(s => s.Region.Name).ToList(); break; case "region_desc": clients = clients.OrderByDescending(s => s.Region.Name).ToList(); break; case "Gender": clients = clients.OrderBy(s => s.Gender).ToList(); break; case "gender_desc": clients = clients.OrderByDescending(s => s.Gender).ToList(); break; case "Classification": clients = clients.OrderBy(s => s.Classification.Name).ToList(); break; case "classification_desc": clients = clients.OrderByDescending(s => s.Classification.Name).ToList(); break; case "Last Purchase": clients = clients.OrderBy(s => s.LastPurchase).ToList(); break; case "lastpurchase_desc": clients = clients.OrderByDescending(s => s.LastPurchase).ToList(); break; case "Seller": clients = clients.OrderBy(s => s.Seller.UserName).ToList(); break; case "seller_desc": clients = clients.OrderByDescending(s => s.Seller.UserName).ToList(); break; } return clients; }
// Get/Post public ViewResult Index(string sortOrder, string currentFilter, string searchName, string searchStartDate, string searchEndDate, string searchGender, string searchClassification, string searchRegion, string searchCity, string searchSeller, int? page) { // Se for Adminstrador pode consultar tudo. Se tiver ID é porque é um usuário comum. string userId = string.Empty; if (TempData["userId"] != null) { userId = TempData["userId"].ToString(); TempData["userId"] = userId; searchSeller = userId; } // DropDownListClassification if (string.IsNullOrEmpty(searchSeller) || searchSeller.Contains(rubish)) { ViewBag.SellerCombo = BuildComboSeller(string.Empty); searchSeller = string.Empty; } else ViewBag.SellerCombo = BuildComboSeller(searchSeller); // DropDownListGender if (string.IsNullOrEmpty(searchGender) || searchGender.Contains(rubish)) { ViewBag.GenderCombo = CombosHelper.BuildComboGender(string.Empty); searchGender = string.Empty; } else ViewBag.GenderCombo = CombosHelper.BuildComboGender(searchGender); // DropDownListCity if (string.IsNullOrEmpty(searchCity) || searchCity.Contains(rubish)) { ViewBag.CityCombo = BuildComboCity(string.Empty); searchCity = string.Empty; } else ViewBag.CityCombo = BuildComboCity(searchCity); // DropDownListRegion if (string.IsNullOrEmpty(searchRegion) || searchRegion.Contains(rubish)) { ViewBag.RegionCombo = BuildComboRegion(string.Empty); searchRegion = string.Empty; } else ViewBag.RegionCombo = BuildComboRegion(searchRegion); // DropDownListClassification if (string.IsNullOrEmpty(searchClassification) || searchClassification.Contains(rubish)) { ViewBag.ClassificationCombo = BuildComboClassification(string.Empty); searchClassification = string.Empty; } else ViewBag.ClassificationCombo = BuildComboClassification(searchClassification); // Check dates DateTime dateToCheckStart; DateTime dateToCheckEnd; DateTime? end = null; DateTime? start = null; // Tentar fazer o parse das datas, caso seja uma data inválida. bool convertDateStart = DateTime.TryParse(searchStartDate, out dateToCheckStart); bool convertDateEnd = DateTime.TryParse(searchEndDate, out dateToCheckEnd); if (convertDateEnd) end = dateToCheckEnd; if (convertDateStart) start = dateToCheckStart; // Cria-se um objeto para usar na consulta dos dados. ClientFilter filter = new ClientFilter() { Name = searchName == null ? string.Empty : searchName, Gender = searchGender, StartDate = start, EndDate = end, GuidRegion = !string.IsNullOrEmpty(searchRegion) ? searchRegion : string.Empty, GuidCity = !string.IsNullOrEmpty(searchCity) ? searchCity : string.Empty, GuidClassification = !string.IsNullOrEmpty(searchClassification) ? searchClassification : string.Empty, GuidSeller = !string.IsNullOrEmpty(searchSeller) ? searchSeller : string.Empty, SortOrder = sortOrder }; // Controll header sorting. HeaderController(sortOrder); // Grid paging. if (!string.IsNullOrEmpty(searchName)) page = 1; else searchName = currentFilter; ViewBag.FilterName = searchName; // Search for Client's data. Important method. IEnumerable<Client> clients = ResultFromClient(filter); // Number of registries to show for each page. int pageSize = 5; int pageNumber = (page ?? 1); return View(clients.ToPagedList(pageNumber, pageSize)); }