public DataTable GetQS_IM_Result(QueryQS_IM_Result query, string order, int currentPage, int pageSize, out int totalCount)
{
string where = GetWhere(query);
if (string.IsNullOrEmpty(order))
{
order = "a.CreateTime desc";
}
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.NVarChar, 40000),
new SqlParameter("@order", SqlDbType.NVarChar, 200),
new SqlParameter("@pagesize", SqlDbType.Int, 4),
new SqlParameter("@indexpage", SqlDbType.Int, 4),
new SqlParameter("@totalRecorder", SqlDbType.Int, 4)
};
parameters[0].Value = where;
parameters[1].Value = order;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_QS_IM_Result_SELECT, parameters);
totalCount = (int)(parameters[4].Value);
return(ds.Tables[0]);
}
private void BindData()
{
//解析参数
string errMsg = string.Empty;
QueryQS_IM_Result query = new QueryQS_IM_Result();
BLL.ConverToEntitie <QueryQS_IM_Result> conver = new BLL.ConverToEntitie <QueryQS_IM_Result>(query);
errMsg = conver.Conver(JsonStr);
if (errMsg != "")
{
return;
}
//参数校验处理（必须）
query.LoginUerID = BLL.Util.GetLoginUserID();
query.InitCheck();
switch (query.BeginCount)
{
case "-1":
query.BeginCount = "1";
query.EndCount = "";
break;
case "1":
query.BeginCount = "1";
query.EndCount = "30";
break;
case "2":
query.BeginCount = "31";
query.EndCount = "59";
break;
case "3":
query.BeginCount = "60";
query.EndCount = "99";
break;
case "4":
query.BeginCount = "100";
query.EndCount = "";
break;
default: break;
}
//查询数据
DataTable dt = BLL.QS_IM_Result.Instance.GetQS_IM_Result(query, "", BLL.PageCommon.Instance.PageIndex, PageSize, out RecordCount);
repeaterTableList.DataSource = dt;
repeaterTableList.DataBind();
AjaxPager.PageSize = 20;
AjaxPager.InitPager(RecordCount);
}
public DataRow GetQS_IM_ResultForCSID(string csid)
{
QueryQS_IM_Result query = new QueryQS_IM_Result();
query.CSID = csid;
int count = 0;
DataTable dt = GetQS_IM_Result(query, "", 1, 100, out count);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0]);
}
else
{
return(null);
}
}
/// 获取where条件
/// <summary>
/// 获取where条件
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private string GetWhere(QueryQS_IM_Result query)
{
string where = "";
//CSID
if (!string.IsNullOrEmpty(query.CSID))
{
where += " AND a.CSID='" + StringHelper.SqlFilter(query.CSID) + "' ";
}
//对话起始时间
if (!string.IsNullOrEmpty(query.BeginTime))
{
where += " AND a.CreateTime>='" + StringHelper.SqlFilter(query.BeginTime) + "' ";
}
//对话结束时间
if (!string.IsNullOrEmpty(query.EndTime))
{
where += " AND a.CreateTime<='" + StringHelper.SqlFilter(query.EndTime) + "' ";
}
//开始次数
if (!string.IsNullOrEmpty(query.BeginCount))
{
where += " AND a.SumDailog>=" + StringHelper.SqlFilter(query.BeginCount);
}
//结束次数
if (!string.IsNullOrEmpty(query.EndCount))
{
where += " AND a.SumDailog<=" + StringHelper.SqlFilter(query.EndCount);
}
//坐席id
if (!string.IsNullOrEmpty(query.AgentUserID))
{
where += " AND a.UserID='" + StringHelper.SqlFilter(query.AgentUserID) + "' ";
}
//评分日期-开始
if (!string.IsNullOrEmpty(query.ScoreBeginTime))
{
where += " AND b.CreateTime>='" + StringHelper.SqlFilter(query.ScoreBeginTime) + " 00:00:00' ";
}
//评分日期-结束
if (!string.IsNullOrEmpty(query.ScoreEndTime))
{
where += " AND b.CreateTime<='" + StringHelper.SqlFilter(query.ScoreEndTime) + " 23:59:59' ";
}
//评分表
if (!string.IsNullOrEmpty(query.ScoreTable) && query.ScoreTable != "-1")
{
//待评分 取范围表字段；已评分 取结果表字段
where += " AND ((c.QS_IM_RTID='" + StringHelper.SqlFilter(query.ScoreTable) + "' AND ISNULL(b.STATUS, 20001)=20001) OR b.QS_RTID='" + StringHelper.SqlFilter(query.ScoreTable) + "') ";
}
//坐席分组
if (!string.IsNullOrEmpty(query.BGID) && query.BGID != "-1")
{
where += " AND a.BGID='" + StringHelper.SqlFilter(query.BGID) + "' ";
}
//业务线
if (!string.IsNullOrEmpty(query.BusinessLine) && query.BusinessLine != "-1")
{
//暂时不做
where += "";
}
//申诉时间
where += GetQS_ApprovalHistoryWhere(query.AppealBeginTime, query.AppealEndTime);
//评分人
if (!string.IsNullOrEmpty(query.ScoreCreater) && query.ScoreCreater != "-1")
{
where += " AND b.CreateUserID='" + StringHelper.SqlFilter(query.ScoreCreater) + "' ";
}
////成绩
//if (!string.IsNullOrEmpty(query.Qualified))
//{
// where += " AND b.IsQualified IN (" + Dal.Util.SqlFilterByInCondition(query.Qualified) + ") ";
//}
//评分状态
if (!string.IsNullOrEmpty(query.QSResultStatus) && query.QSResultStatus != "-1")
{
where += " AND ISNULL(b.STATUS, 20001)='" + StringHelper.SqlFilter(query.QSResultStatus) + "' ";
//申诉结果 (已申诉)
if (!string.IsNullOrEmpty(query.QSStateResult) && query.QSResultStatus == "20006")
{
where += " AND b.StateResult IN (" + Dal.Util.SqlFilterByInCondition(query.QSStateResult) + ") ";
}
}
//是否评价 0 已评价 1未评价
if (!string.IsNullOrEmpty(query.QSResultScore) && query.QSResultScore != "-1")
{
if (query.QSResultScore == "0")
{
string subwhere = "";
//服务评价
if (!string.IsNullOrEmpty(query.PerQSResultScore) && query.PerQSResultScore != "-1")
{
subwhere += " a.PerSatisfaction='" + StringHelper.SqlFilter(query.PerQSResultScore) + "' AND ";
}
else
{
//有评价
subwhere += " a.PerSatisfaction IS NOT NULL AND ";
}
//产品评价
if (!string.IsNullOrEmpty(query.ProQSResultScore) && query.ProQSResultScore != "-1")
{
subwhere += " a.ProSatisfaction='" + StringHelper.SqlFilter(query.ProQSResultScore) + "' AND ";
}
else
{
//有评价
subwhere += " a.ProSatisfaction IS NOT NULL AND ";
}
if (subwhere.Length > 4)
{
subwhere = subwhere.Substring(0, subwhere.Length - 4);
}
where += " AND (" + subwhere + ") ";
}
else if (query.QSResultScore == "1")
{
//没有评价
where += " AND a.PerSatisfaction IS NULL AND a.ProSatisfaction IS NULL ";
}
}
#region 数据权限判断
if (query.LoginUerID > 0)
{
string whereDataRight = "";
whereDataRight = Dal.UserGroupDataRigth.Instance.GetSqlRightstr("a", "BGID", "UserID", query.LoginUerID);
where += whereDataRight;
}
#endregion
return(where);
}
public DataTable GetQS_IM_Result(QueryQS_IM_Result query, string order, int currentPage, int pageSize, out int totalCount)
{
return(Dal.QS_IM_Result.Instance.GetQS_IM_Result(query, order, currentPage, pageSize, out totalCount));
}
