public ClientFilterViewModel List(ClientFilterViewModel clientFilter)
{
var client = new Client()
{
Classification = new Classification((clientFilter.ClassificationId != null ? clientFilter.ClassificationId.Value : 0)),
Gender = new Gender((clientFilter.GenderId != null ? clientFilter.GenderId.Value : 0)),
Id = clientFilter.Id,
LastPurchase = clientFilter.LastPurchase,
Name = clientFilter.Name,
Phone = clientFilter.Phone,
Region = new Region((clientFilter.RegionId != null ? clientFilter.RegionId.Value : 0), new City((clientFilter.CityId != null ? clientFilter.CityId.Value : 0))),
Seller = new User((clientFilter.GetSellerId() != null ? clientFilter.GetSellerId().Value : 0))
};
var clients = _repository.List(client, clientFilter.LastPurchaseUntil).ToList();
var clientsViewModel = clients.Select(x => new ClientResultViewModel()
{
City = x.Region.City.Description,
Classification = x.Classification.Description,
Gender = x.Gender.Description,
Id = x.Id,
LastPurchase = x.LastPurchase,
Name = x.Name,
Phone = x.Phone,
RegionId = x.Region.Id,
Seller = x.Seller.Email,
Region = x.Region.Description
}).ToList();
clientFilter.Classifications = _classificationRepository.List().ToList().Select(m => new SelectListItem {
Value = m.Id.ToString(), Text = m.Description
}).ToList();
clientFilter.Regions = _regionRepository.ListByCity(clientFilter.CityId).ToList().Select(m => new SelectListItem {
Value = m.Id.ToString(), Text = m.Description
}).ToList();
clientFilter.Sellers = _userRepository.ListSeller().ToList().Select(m => new SelectListItem {
Value = m.Id.ToString(), Text = m.Email
}).ToList();
clientFilter.Genders = _genderRepository.List().ToList().Select(m => new SelectListItem {
Value = m.Id.ToString(), Text = m.Description
}).ToList();
clientFilter.Cities = _cityRepository.List().ToList().Select(m => new SelectListItem {
Value = m.Id.ToString(), Text = m.Description
}).ToList();
clientFilter.SellerId = null;
clientFilter.Clients = clientsViewModel;
return(clientFilter);
}
public IActionResult FilterClients(string name, string phone, int?genderId, int?classificationId, int?sellerId, int?cityId, string region, string lastPurchase, string lastPurchaseUntil, int?regionId)
{
try
{
var user = UserAut;
var clientFilterViewModel = new ClientFilterViewModel(name, phone, genderId, classificationId, sellerId, cityId, region, regionId, user.Id, user.RoleId, lastPurchase, lastPurchaseUntil);
var clientFilter = _clientService.List(clientFilterViewModel);
return(PartialView("_ListClients", clientFilter.Clients));
}
catch (Exception ex)
{
Response.StatusCode = (int)HttpStatusCode.BadGateway;
return(Json(new { errors = ex.Message }));
}
}
public JsonResult Get(ClientFilterViewModel filter)
{
using (var context = new AppDbContext())
{
var query = (filter ?? new ClientFilterViewModel {
orderByColumn = "Name"
}).Apply(context);
var recordsTotal = query.Count();
if (!string.IsNullOrWhiteSpace(filter.orderByColumn))
{
query = query.OrderByPropertyName(filter.orderByColumn, filter.orderByAsc)
.Skip(filter.start)
.Take(filter.length);
}
var clients = query.Select(c => new
{
c.Id,
c.Name,
c.CNPJ,
c.CPF,
c.Telephone,
c.Email,
c.Active
}).ToArray();
if (!string.IsNullOrWhiteSpace(filter.orderByColumn))
{
var data = new
{
recordsTotal,
table = clients
};
return(Json(data, JsonRequestBehavior.AllowGet));
}
return(Json(clients, JsonRequestBehavior.AllowGet));
}
}
public void Excel(ClientFilterViewModel filter)
{
var userId = UserLoggedId;
var username = UserLoggedUserName;
Task.Run(() =>
{
try
{
using (var context = new AppDbContext().WithUsername(username))
{
var query = filter.Apply(context);
var recordsTotal = query.Count();
// esse valor deve variar conforme o desempenho da consulta
// quanto menor o desempenho da consulta menor deve ser o valor
// para evitar timeout de banco de dados
// consultas com poucos registros ou alta performance talvez nem seja necessário
// paginação
var recordsByPage = 100;
var totalPages = (int)Math.Ceiling((double)recordsTotal / (double)recordsByPage);
var clients = new List <dynamic>();
for (int i = 0; i < totalPages; i++)
{
clients.AddRange(query.Skip(i).Take(recordsByPage).Select(c => new
{
c.Id,
c.Name,
c.CNPJ,
c.CPF,
c.Telephone,
c.Email,
c.Active
}).ToArray());
}
// Generate file
// Forma A: Simplesmente exporta o que veio da consulta
var excelGenerator = new ExcelGenerator("Customers", UploadHelper.GetDirectoryTempPathOrCreateIfNotExists(), username);
var fileName = excelGenerator.Generate(clients);
// Forma B: Formata os dados que veio da consulta para exportar
//excelGenerator.Generate(clients, (e) =>
//{
// e.Cells[1, 1].Value = "Id";
// e.Cells[1, 2].Value = "CPF/CNPJ";
// e.Cells[1, 3].Value = "E-mail";
// e.Cells[1, 4].Value = "Telefone";
// e.Cells[1, 5].Value = "Status";
//}, (e, i, data) =>
//{
// e.Cells[i, 1].Value = data.Id;
// e.Cells[i, 2].Value = data.CPF ?? data.CNPJ;
// e.Cells[i, 3].Value = data.Email;
// e.Cells[i, 4].Value = data.Telephone;
// e.Cells[i, 5].Value = data.Active ? "Ativo" : "Inativo";
//});
// Generate notification
var user = new User {
Id = userId
};
context.Users.Attach(user);
user.NotifyFileGenerated(fileName);
context.Configuration.ValidateOnSaveEnabled = false;
context.SaveChanges();
}
}
catch (Exception ex)
{
// TODA ROTINA ASSINCRONA PRECISA TER TRATAMENTO DE ERRO EXPLICITO
var logger = LogManager.GetLogger(GetType());
logger.Error(ex);
}
});
}
