public Customer SignIn(string User, string Pass)
{
var query = Dbset.AsQueryable();
query = query.Where(x => x.User == User && x.Password == Pass);
return(query.FirstOrDefault());
}
public Customer GetById(string customerId)
{
var query = Dbset.AsQueryable();
query = query.Where(x => x.CustomerId == customerId);
return(query.FirstOrDefault());
}
public Customer getCustomer(string customerId, string ContactName, string CompanyName)
{
var query = Dbset.AsQueryable();
query = query.Where(x => x.CustomerId == customerId && x.ContactName == ContactName && x.CompanyName == CompanyName);
return(query.FirstOrDefault());
}
/// <summary>
/// 按条件删除数据
/// </summary>
/// <param name="where">查询条件</param>
public virtual void Delete(Expression <Func <TEntity, bool> > @where)
{
var objects = Dbset.Where(where);
//foreach (TEntity obj in objects)
Dbset.RemoveRange(objects);
}
public IEnumerable <EV_EVENTO> GetAllEventoInscritos()
{
var query = Dbset.Include("EV_INSCRICAO").OrderBy(x => x.DTCADASTRO);
var _object = CompiledQuery.Compile <ObjectContext, IQueryable <EV_EVENTO> >(ctx => query.Where(X => X.EV_INSCRICAO.Count() > 0));
return(_object.Invoke(((IObjectContextAdapter)Entities).ObjectContext).ToList());
}
public async Task <List <AccountTransferEntity> > GetTransfers(int accountId, TransferType transferType, DateTime from, DateTime to)
{
var records = await Dbset.Where(r => r.AccountId == accountId && r.TransferType == transferType && r.TransferDate > from && r.TransferDate < to)
.OrderBy(r => r.TransferDate).ToListAsync();
return(records);
}
public async Task <List <Category> > GetBySectionIdAsync(int sectionId)
{
var records = await Dbset.Where(r => r.SectionId == sectionId && !r.Deleted)
.OrderBy(h => h.Title).ToListAsync();
return(records);
}
public Employee GetInfor(int id)
{
var query = Dbset.AsQueryable();
query = query.Where(x => x.EmployeeId == id);
return(query.FirstOrDefault());
}
public Employee getEmployee(int EmployeeId, string FirstName, string lastName)
{
var query = Dbset.AsQueryable();
query = query.Where(x => x.EmployeeId == EmployeeId && x.FirstName == FirstName && x.LastName == lastName);
return(query.FirstOrDefault());
}
public List <BasicModel> GetMatchesForSaving(DateTime?date = null)
{
var matches = Dbset.Where(w => w.Participants.Count > 0 && w.TeamTwo != null && w.TeamOne != null).ToList();
if (date.HasValue)
{
matches = matches.Where(w => w.Date.Value.Date == date.Value.Date).ToList();
}
matches = matches.OrderByDescending(o => o.Date).Take(15).ToList();
var result = new List <BasicModel>();
foreach (var match in matches)
{
result.Add(new BasicModel
{
Name = $"{match.Date.Value.ToShortDateString()}: {match.TeamOne.Name} " +
$"{match.Participants.Where(w => w.TeamId == match.TeamOneId).Sum(s => s.Points)} " +
$" : {match.Participants.Where(w => w.TeamId == match.TeamTwoId).Sum(s => s.Points)} " +
$" {match.TeamTwo.Name}",
Id = match.Id
});
}
return(result);
}
public bool Save(User user)
{
try
{
var exists = Dbset.FirstOrDefault(x => x.Id == user.Id);
if (exists != null)
{
exists.Password = exists.Password;
exists.Name = user.Name;
exists.LastName = user.LastName;
exists.Email = user.Email;
exists.CompanyId = user.CompanyId;
exists.Profile = user.Profile;
exists.CompanyId = user.CompanyId;
Update(exists);
}
else
{
var passwordEncoded = StringExtension.RandomString(5, true).Encrypt(StringExtension.PassPhrase);
user.Password = passwordEncoded;
Create(user);
}
return(true);
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
public Order getOrder(int OrderId, string customerId, int?employeeId)
{
var query = Dbset.AsQueryable();
query = query.Where(x => x.CustomerId == customerId && x.OrderId == OrderId && x.EmployeeId == employeeId);
return(query.FirstOrDefault());
}
public override async Task UpdateAsync(MeetingAgenda entityToUpdate)
{
var existsEntity = await Dbset.Include(x => x.MeetingAgendaUsers).FirstOrDefaultAsync(x => x.Id == entityToUpdate.Id);
existsEntity.Name = entityToUpdate.Name;
existsEntity.Number = entityToUpdate.Number;
existsEntity.IsDraft = entityToUpdate.IsDraft;
existsEntity.UpdatedBy = entityToUpdate.UpdatedBy;
existsEntity.UpdatedDate = DateTime.Now;
if (!entityToUpdate.IsRemoved)
{
var existsUsers = existsEntity.MeetingAgendaUsers;
DbContext.MeetingAgendaUsers.RemoveRange(existsUsers);
foreach (var user in entityToUpdate.MeetingAgendaUsers)
{
user.MeetingAgendaId = entityToUpdate.Id;
user.CreatedBy = entityToUpdate.UpdatedBy;
user.CreatedDate = DateTime.Now;
user.UpdatedBy = entityToUpdate.UpdatedBy;
user.UpdatedDate = DateTime.Now;
await DbContext.MeetingAgendaUsers.AddAsync(user);
}
}
}
public virtual IEnumerable <EV_INSCRICAO> GetInscricaoByClienteId(int clientId)
{
var query = Dbset.Include("EV_EVENTO").Include("EV_CLIENTE").Where(x => x.CLIENTEID == clientId).OrderBy(x => x.CRIADO);
var _object = CompiledQuery.Compile <ObjectContext, IQueryable <EV_INSCRICAO> >(ctx => query);
return(_object.Invoke(((IObjectContextAdapter)Entities).ObjectContext).ToList());
}
public List <Cart> Carts(string customerId)
{
var query = Dbset.AsQueryable();
query = query.Include(x => x.Customer).Where(x => x.CustomerId == customerId);
return(query.ToList());
}
public IEnumerable <User> GetAllWithFilters(int currentUserId, string searchName, string searchEmail, string sortColumn, bool sortDirectionIsAscending, short page, byte pageSize)
{
searchName = searchName ?? "";
searchEmail = searchEmail ?? "";
var users = Dbset.Include(x => x.Membership).Where(x => x.Id != currentUserId && x.Name.Contains(searchName) && x.Email.Contains(searchEmail));
switch (sortColumn)
{
case "Email":
users = sortDirectionIsAscending ? users.OrderBy(x => x.Email) : users.OrderByDescending(x => x.Email);
break;
case "Name":
users = sortDirectionIsAscending ? users.OrderBy(x => x.Name) : users.OrderByDescending(x => x.Name);
break;
case "DateRegistered":
users = sortDirectionIsAscending ? users.OrderBy(x => x.DateRegistered) : users.OrderByDescending(x => x.DateRegistered);
break;
case "LastLoginAt":
users = sortDirectionIsAscending ? users.OrderBy(x => x.LastLoginAt) : users.OrderByDescending(x => x.LastLoginAt);
break;
case "IsBanned":
users = sortDirectionIsAscending ? users.OrderBy(x => x.IsBanned) : users.OrderByDescending(x => x.IsBanned);
break;
default:
users = users.OrderByDescending(x => x.DateRegistered);
break;
}
return(users.Skip(pageSize * (page - 1)).Take(pageSize).ToList());
}
public Product getProduct(int productId, string ProductName)
{
var query = Dbset.AsQueryable();
query = query.Where(x => x.ProductId == productId && x.ProductName == ProductName);
return(query.FirstOrDefault());
}
public Shipper getshipper(int id, string companyName, string phone)
{
var query = Dbset.AsQueryable();
query = query.Where(x => x.ShipperId == id && x.CompanyName == companyName && x.Phone == phone);
return(query.FirstOrDefault());
}
public IEnumerable <EV_INSCRICAO> GetInscricaoPaginacao(string search, string order, int offset, int limit)
{
var skip = (offset - 1) * limit < 0 ? 0 : (offset - 1) * limit;
var date = DateTime.TryParse(search, out DateTime dataPagamento);
if (date)
{
var query = Dbset.Include("EV_EVENTO").Include("EV_CLIENTE").Where(x => x.DATAPAGAMENTO.Equals(dataPagamento));
var _object = CompiledQuery.Compile <ObjectContext, IQueryable <EV_INSCRICAO> >(ctx => query.Skip(skip).Take(limit));
return(_object.Invoke(((IObjectContextAdapter)Entities).ObjectContext).ToList());
}
if (order == "asc")
{
var query = Dbset.Include("EV_EVENTO").Include("EV_CLIENTE").Where(x => x.EV_EVENTO.NOME.Contains(search) ||
x.EV_CLIENTE.NOME.Contains(search) || x.PAGO.Contains(search) || x.BOLETOEMITIDO.Contains(search) || x.TURNO.Contains(search));
var _object = CompiledQuery.Compile <ObjectContext, IQueryable <EV_INSCRICAO> >(ctx => query.Skip(skip).Take(limit));
return(_object.Invoke(((IObjectContextAdapter)Entities).ObjectContext).ToList());
}
else
{
var query = Dbset.Include("EV_EVENTO").Include("EV_CLIENTE").Where(x => x.EV_EVENTO.NOME.Contains(search) ||
x.EV_CLIENTE.NOME.Contains(search) || x.PAGO.Contains(search) || x.BOLETOEMITIDO.Contains(search) || x.TURNO.Contains(search));
var _object = CompiledQuery.Compile <ObjectContext, IQueryable <EV_INSCRICAO> >(ctx => query.Skip(skip).Take(limit));
return(_object.Invoke(((IObjectContextAdapter)Entities).ObjectContext).ToList());
}
}
public OrderDetail getOrderDetail(int OrderId, int productId)
{
var query = Dbset.AsQueryable();
query = query.Where(x => x.OrderId == OrderId && x.ProductId == productId);
return(query.FirstOrDefault());
}
public Category getCategory(int categoryId, string categoryName)
{
var query = Dbset.AsQueryable();
query = query.Where(x => x.CategoryId == categoryId && x.CategoryName == categoryName);
return(query.FirstOrDefault());
}
public IEnumerable <EV_CLIENTE> GetClientePaginacao(FilterClientes filter, string order, int offset, int limit)
{
var skip = (offset - 1) * limit < 0 ? 0 : (offset - 1) * limit;
var query = Dbset.Include("AD_ORGAOS").Include("EV_INSCRICAO").Where(x =>
(x.NOME.Contains(filter.Nome) || filter.Nome == null) &&
(x.NOME.StartsWith(filter.Letra) || filter.Letra == null) &&
(x.RG.StartsWith(filter.Rg) || filter.Rg == null) &&
(x.INSTITUICAO.Contains(filter.Instituicao) || filter.Instituicao == null) &&
(x.SEXO.Contains(filter.Sexo) || filter.Sexo == null) &&
(x.TEL_CELULAR.Contains(filter.Telcelular) || filter.Telcelular == null) &&
(x.CPF.StartsWith(filter.Cpf) || filter.Cpf == null) &&
(x.EMAIL.Contains(filter.Email) || filter.Email == null) &&
(x.CIDADE.Contains(filter.Cidade) || filter.Cidade == null) &&
(x.DTNASCIMENTO == filter.Dtaniversario || filter.Dtaniversario == null) &&
(x.EV_INSCRICAO.Any(y => y.EVENTOID == filter.Eventoid) || filter.Eventoid == null || filter.Eventoid == 0));
if (order == "asc")
{
var _object = CompiledQuery.Compile <ObjectContext, IQueryable <EV_CLIENTE> >(ctx => query.OrderBy(x => x.DTCADASTRO).Skip(skip).Take(limit));
return(_object.Invoke(((IObjectContextAdapter)Entities).ObjectContext).ToList());
}
else
{
var _object = CompiledQuery.Compile <ObjectContext, IQueryable <EV_CLIENTE> >(ctx => query.OrderByDescending(x => x.DTCADASTRO).Skip(skip).Take(limit));
return(_object.Invoke(((IObjectContextAdapter)Entities).ObjectContext).ToList());
}
}
/// <summary>
/// Inserts the specified entity.
/// </summary>
/// <param name="entities">The entities.</param>
public virtual void Insert(params TEntity[] entities)
{
foreach (var entity in entities)
{
Dbset.Add(entity);
}
}
/// <summary>
/// Get All Products with Related entities
/// </summary>
/// <returns></returns>
public IEnumerable <Product> GetAllProductsWithInclude()
{
return(Dbset
.Include(x => x.Brand)
.Include(x => x.SubCategory)
.ThenInclude(x => x.RootCategory)
.ToList());
}
public Task <int> DeleteHash(int id)
{
var result = Dbset.Where(x => x.Id == id);
Dbset.Remove(result.FirstOrDefault());
Context.SaveChanges();
return(null);
}
/// <summary>
/// Get Product with Related entities by Id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Product GetProductByIdWithInclude(int id)
{
return(Dbset
.Include(x => x.Brand)
.Include(x => x.SubCategory)
.ThenInclude(x => x.RootCategory)
.FirstOrDefault(x => x.Id == id));
}
public List <CompanyIndicator> Get(string ticker)
{
var records = Dbset
.Where(c => c.Ticker == ticker)
.ToList();
return(records);
}
public void Update(Product product)
{
//var x = Context.Entry(product).State;
Dbset.Attach(product);
//var y = Context.Entry(product).State;
Context.Entry(product).State = EntityState.Modified;
//var z = Context.Entry(product).State;
}
public void Delete(Product product)
{
if (Context.Entry(product).State == EntityState.Detached)
{
Dbset.Attach(product);
}
Dbset.Remove(product);
}
public override async Task UpdateAsync(MeetingNote entityToUpdate)
{
var existsEntity = await Dbset.FindAsync(entityToUpdate.Id);
existsEntity.Note = entityToUpdate.Note;
existsEntity.UpdatedBy = entityToUpdate.UpdatedBy;
existsEntity.UpdatedDate = DateTime.Now;
}
