public IEnumerable <VoipCall> GetCalls(VoipCallFilter filter) { using (var db = GetDb()) { var query = GetCallsQuery(filter); if (filter.SortByColumn != null) { query.OrderBy(filter.SortByColumn, filter.SortOrder); } query.SetFirstResult((int)filter.Offset); query.SetMaxResults((int)filter.Max * 3); var calls = db.ExecuteList(query).ConvertAll(ToCall); calls = calls.GroupJoin(calls, call => call.Id, h => h.ParentID, (call, h) => { call.ChildCalls.AddRange(h); return(call); }).Where(r => string.IsNullOrEmpty(r.ParentID)).ToList(); return(calls); } }
public IEnumerable <VoipCall> GetCalls(VoipCallFilter filter) { using (var db = GetDb()) { var query = GetCallsQuery(filter); if (filter.SortByColumn != null) { query.OrderBy(filter.SortByColumn, filter.SortOrder); } query.SetFirstResult((int)filter.Offset); query.SetMaxResults((int)filter.Max); var calls = db.ExecuteList(query).ConvertAll(ToCall); var history = GetCallHistory(calls.Select(r => r.Id)); calls = calls.GroupJoin(history, call => call.Id, h => h.ParentID, (call, h) => { call.History.AddRange(h); return(call); }).ToList(); return(calls); } }
public int GetCallsCount(VoipCallFilter filter) { using var db = GetDb(); var query = GetCallsQuery(filter).Where("ca.parent_call_id", ""); var queryCount = new SqlQuery().SelectCount().From(query, "t1"); return(db.ExecuteScalar <int>(queryCount)); }
public int GetCallsCount(VoipCallFilter filter) { using (var db = GetDb()) { var query = GetCallsQuery(filter); var queryCount = new SqlQuery().SelectCount().From(query, "t1"); return(db.ExecuteScalar <int>(queryCount)); } }
private IQueryable <CallContact> GetCallsQuery(VoipCallFilter filter) { var q = VoipDbContext.VoipCalls .Where(r => r.TenantId == TenantID); if (!string.IsNullOrEmpty(filter.Id)) { q = q.Where(r => r.Id == filter.Id || r.ParentCallId == filter.Id); } if (filter.ContactID.HasValue) { q = q.Where(r => r.ContactId == filter.ContactID.Value); } if (!string.IsNullOrWhiteSpace(filter.SearchText)) { q = q.Where(r => r.Id.StartsWith(filter.SearchText)); } if (filter.TypeStatus.HasValue) { q = q.Where(r => r.Status == filter.TypeStatus.Value); } if (filter.FromDate.HasValue) { q = q.Where(r => r.DialDate >= filter.FromDate.Value); } if (filter.ToDate.HasValue) { q = q.Where(r => r.DialDate <= filter.ToDate.Value); } if (filter.Agent.HasValue) { q = q.Where(r => r.AnsweredBy == filter.Agent.Value); } return(q .GroupBy(r => r.Id, r => r) .Join( VoipDbContext.CrmContact.DefaultIfEmpty(), r => r.FirstOrDefault().ContactId, c => c.Id, (call, contact) => new CallContact { DbVoipCall = call.FirstOrDefault(), CrmContact = contact }) ); }
private SqlQuery GetCallsQuery(VoipCallFilter filter) { var query = Query("crm_voip_calls ca") .Select("ca.id", "ca.parent_call_id", "ca.number_from", "ca.number_to", "ca.answered_by", "ca.dial_date", "ca.dial_duration", "ca.price", "ca.status") .Select("ca.record_sid", "ca.record_url", "ca.record_duration", "ca.record_price") .Select("ca.contact_id", "co.is_company", "co.company_name", "co.first_name", "co.last_name") .LeftOuterJoin("crm_contact co", Exp.EqColumns("ca.contact_id", "co.id")) .GroupBy("ca.id"); if (!string.IsNullOrEmpty(filter.Id)) { query.Where(Exp.Eq("ca.id", filter.Id) | Exp.Eq("ca.parent_call_id", filter.Id)); } if (filter.ContactID.HasValue) { query.Where(Exp.Eq("ca.contact_id", filter.ContactID.Value)); } if (!string.IsNullOrWhiteSpace(filter.SearchText)) { query.Where(Exp.Like("ca.id", filter.SearchText, SqlLike.StartWith)); } if (filter.TypeStatus.HasValue) { query.Where("ca.status", filter.TypeStatus.Value); } if (filter.FromDate.HasValue) { query.Where(Exp.Ge("ca.dial_date", filter.FromDate.Value)); } if (filter.ToDate.HasValue) { query.Where(Exp.Le("ca.dial_date", filter.ToDate.Value)); } if (filter.Agent.HasValue) { query.Where("ca.answered_by", filter.Agent.Value); } return(query); }
private SqlQuery GetCallsQuery(VoipCallFilter filter) { var query = Query(callsTable + " ca") .Select("ca.id", "ca.number_from", "ca.number_to", "ca.answered_by", "ca.dial_date", "ca.price") .Select("ca.status", "ca.contact_id") .LeftOuterJoin(contactsTable + " co", Exp.EqColumns("ca.contact_id", "co.id")) .Select("co.is_company", "co.company_name", "co.first_name", "co.last_name"); if (filter.Ids != null && filter.Ids.Any()) { query.Where(Exp.In("ca.id", filter.Ids)); } if (!string.IsNullOrWhiteSpace(filter.SearchText)) { query.Where(Exp.Like("ca.id", filter.SearchText, SqlLike.StartWith)); } if (filter.TypeStatus.HasValue) { query.Where("ca.status", filter.TypeStatus.Value); } if (filter.FromDate.HasValue) { query.Where(Exp.Ge("ca.dial_date", filter.FromDate.Value)); } if (filter.ToDate.HasValue) { query.Where(Exp.Le("ca.dial_date", filter.ToDate.Value)); } if (filter.Agent.HasValue) { query.Where("ca.answered_by", filter.Agent.Value); } return(query); }
public IEnumerable <VoipCall> GetCalls(VoipCallFilter filter) { var query = GetCallsQuery(filter); if (filter.SortByColumn != null) { query.OrderBy(filter.SortByColumn, filter.SortOrder); } query = query.Skip((int)filter.Offset); query = query.Take((int)filter.Max * 3); var calls = query.ToList().ConvertAll(ToCall); calls = calls.GroupJoin(calls, call => call.Id, h => h.ParentID, (call, h) => { call.ChildCalls.AddRange(h); return(call); }).Where(r => string.IsNullOrEmpty(r.ParentID)).ToList(); return(calls); }
public int GetCallsCount(VoipCallFilter filter) { return(GetCallsQuery(filter).Where(r => r.DbVoipCall.ParentCallId == "").Count()); }