public JsonResult QueryRecords(QueryCallRecordsRquest request)
{
var totalNumber = 0;
var dt = GetRecords(request, ref totalNumber);
var data = BuildRecordingModel(dt);
//获取咨询类型
var qtypes = GetQuestionTypes(data.Select(p => p.UniqueId).ToList());
data.ForEach(p =>
{
var types = qtypes.Where(qtype => qtype.ParentId == p.ObjectId).ToList();
if (types == null && !types.Any())
{
return;
}
p.QuestionType = string.Join(",", types.Select(t => t.QType2Name));
p.QTpye1Ids = types.GroupBy(t => t.QType1Id).Select(t => t.Key).ToList();
p.QTpye2Ids = types.GroupBy(t => t.QType2Id).Select(t => t.Key).ToList();
});
if (!string.IsNullOrEmpty(request.QuestionType))
{
data = data.Where(p => p.QuestionType.Contains(request.QuestionType)).ToList();
}
GridViewModel <QueryCallRecordsResponse> result = new GridViewModel <QueryCallRecordsResponse>(totalNumber, data);
return(Json(result, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 录音记录页面
/// 录音记录查询 SQL
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public DataTable GetRecords(QueryCallRecordsRquest request, ref int totalNumber)
{
var countSql = @"select count(1) from c_callrecords ";
string sql = @"select * from c_callrecords ";
var whereSql = " where CALLTYPE=" + request.CallType + "";
var parentSql = @"select * from C_CALLPOWER where usercode='{0}' and state=1";
DataTable parentDt = AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(string.Format(parentSql, request.CallerCode));
var codeWhereSql = "'" + request.CallerCode + "',";
if (CommonFunction.hasData(parentDt))
{
var parentId = parentDt.Rows[0]["parentid"] + string.Empty;
if (string.IsNullOrEmpty(parentId))
{
var parentObjectId = parentDt.Rows[0]["objectid"] + string.Empty;
DataTable chilrdDt = AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(string.Format("select * from C_CALLPOWER where parentid='{0}' and state=1", parentObjectId));
if (CommonFunction.hasData(chilrdDt))
{
foreach (DataRow row in chilrdDt.Rows)
{
codeWhereSql += "'" + row["usercode"] + string.Empty + "',";
}
}
}
}
codeWhereSql = codeWhereSql.Substring(0, codeWhereSql.Length - 1);
whereSql += string.Format(" and CALLERCODE in ({0})", codeWhereSql);
if (!string.IsNullOrEmpty(request.CalledName))
{
whereSql += " and CALLEDPARTYNAME='" + request.CalledName + "'"; //用户姓名
}
if (!string.IsNullOrEmpty(request.CalledType))
{
whereSql += " and CALLEDPARTYTYPE='" + request.CalledType + "'"; //角色类型
}
if (!string.IsNullOrEmpty(request.CalledIdType))
{
whereSql += " and CALLEDPARTYIDTYPE='" + request.CalledIdType + "'"; //证件类型
}
if (!string.IsNullOrEmpty(request.CalledIdNumber))
{
whereSql += " and CALLEDPARTYIDNUMBER='" + request.CalledIdNumber + "'"; //证件号码
}
if (!string.IsNullOrEmpty(request.ContractNo))
{
whereSql += " and CONTRACTNO='" + request.ContractNo + "'"; //合同号
}
if (!string.IsNullOrEmpty(request.CalledNumber))
{
whereSql += " and CALLEDPARTYNUMBER='" + request.CalledNumber + "'"; //被叫人电话
}
if (!string.IsNullOrEmpty(request.CalledNumberType))
{
whereSql += " and CALLEDPARTYNUMBERTYPE='" + request.CalledNumberType + "'"; //电话类型
}
//if (!string.IsNullOrEmpty(request.QuestionType))
//{
// whereSql += " and QUESTIONTYPE='" + request.QuestionType + "'"; //咨询类型
//}
if (!string.IsNullOrEmpty(request.CallerName))
{
whereSql += " and CALLERNAME='" + request.CallerName + "'"; //主叫人
}
if (!string.IsNullOrEmpty(request.CallerPosition))
{
whereSql += " and CALLERPOSITION='" + request.CallerPosition + "'"; //职务
}
if (!string.IsNullOrEmpty(request.StartDate))
{
whereSql += " and RECORDCREATEDTIME >= to_date('" + request.StartDate + "','yyyy-MM-dd')"; //
}
if (!string.IsNullOrEmpty(request.EndDate))
{
whereSql += " and RECORDCREATEDTIME <= to_date('" + request.EndDate + "','yyyy-MM-dd')"; //
}
sql = string.Format("{0} {1} {2}", sql, whereSql, " order by createdtime desc");
countSql = string.Format("{0} {1}", countSql, whereSql);
if (request.StartIndex > 0 && request.EndIndex > 0)
{
//sql += " where c.rn >={1} and c.rn<={2}";
var pageSql = @" SELECT rownum,c.* FROM (SELECT ROWNUM r, a.* FROM (select * from c_callrecords {0} order by createdtime desc) a WHERE ROWNUM <= {1} ) c WHERE r >= {2} ";
sql = string.Format(pageSql, whereSql, request.EndIndex, request.StartIndex);
}
DataTable dt = AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql);
var num = AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteScalar(countSql);
totalNumber = Convert.ToInt32(num);
return(dt);
}