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