/// <summary>
/// 服务端->查询消息列表
/// 添加人：周 鹏
/// 添加时间：2014-12-23
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// 2015-02-28 周 鹏 更改用户表关联关系
/// </history>
/// <param name="userId">用户编号</param>
/// <param name="qtype">查询类型</param>
/// <param name="pageIndex">当前索引页</param>
/// <param name="pageSize">每页显示条数</param>
/// <param name="totalRecords">总条数</param>
/// <returns></returns>
public DataTable GetSearchWork(string userId, WorkListType qtype, int pageIndex, int pageSize, out int totalRecords)
{
var strSql = new StringBuilder("");
// strSql.AppendFormat(@"SELECT CMW.Id,CMW.Titles,CONVERT(VARCHAR(10),CMW.StartDate,25) AS StartDate,CU.UserName,FormAddress FROM CrmMessageWork AS CMW WITH(NOLOCK)
//JOIN CrmUser AS CU WITH(NOLOCK) ON CMW.SenderId=CU.Id
//WHERE ActionerID='{0}' ", userId);
strSql.AppendFormat(@"SELECT CMW.Id,CMW.Titles,CONVERT(VARCHAR(10),CMW.StartDate,25) AS StartDate,CU.RealName AS UserName,FormAddress,CMW.ContentTypeID FROM CrmMessageWork AS CMW WITH(NOLOCK)
JOIN CrmUser AS CU WITH(NOLOCK) ON CMW.SenderId=CU.Id
WHERE ActionerID='{0}'", userId);
switch (qtype)
{
case WorkListType.Pending:
strSql.AppendFormat("AND [State]=0 AND CMW.RowStatus=1");
break;
case WorkListType.Read:
strSql.AppendFormat("AND [State]=1 AND CMW.RowStatus=1");
break;
case WorkListType.Processed:
strSql.AppendFormat("AND [State]=2 AND CMW.RowStatus=1");
break;
case WorkListType.Delete:
strSql.AppendFormat("AND CMW.RowStatus=0");
break;
}
return(SqlHelper.QueryPageBySql(SqlHelper.SqlConnStringRead, strSql.ToString(), "*", "", "StartDate", "DESC", pageIndex, pageSize, out totalRecords));
}
/// <summary>
/// 手机端->查询消息列表
/// 添加人：周 鹏
/// 添加时间：2014-12-23
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// 2015-06-15 周 鹏 增加事项类型条件过滤
/// </history>
/// <param name="userId">用户编号</param>
/// <param name="qtype">查询类型</param>
/// <param name="pageIndex">当前索引页</param>
/// <param name="pageSize">每页显示条数</param>
/// <param name="totalRecords">总条数</param>
/// <param name="approveType">事项类型</param>
/// <returns></returns>
public DataTable GetMobileSearchWork(string userId, WorkListType qtype, int pageIndex, int pageSize, out int totalRecords, string approveType)
{
var strSql = new StringBuilder("");
strSql.AppendFormat(@"SELECT CMW.Id AS WorkListId,CMW.FormID,CMW.Titles,CMW.StartDate AS SD,CONVERT(VARCHAR(10),CMW.StartDate,25) AS StartDate,
(CASE WHEN CU.RealName IS NULL THEN '系统' ELSE CU.RealName END) AS UserName,
WFP.ProcessID AS 'FlowId',WP.Name AS 'FlowName',ISNULL(WP.MessageType,CMW.ContentTypeID) AS [Type] FROM CrmMessageWork AS CMW WITH(NOLOCK)
lEFT JOIN CrmUser AS CU WITH(NOLOCK) ON CMW.SenderId=CU.Id
LEFT JOIN WF_ProcessInstance AS WFP WITH(NOLOCK) ON CMW.ProcessInstanceID=WFP.Id AND CMW.FormID=CMW.FormID
LEFT JOIN WF_Process AS WP WITH(NOLOCK) ON WP.Id=WFP.ProcessID
WHERE ActionerID='{0}'", userId);
switch (qtype)
{
case WorkListType.Pending:
case WorkListType.Read:
strSql.AppendFormat("AND [State] IN (0,1) AND CMW.RowStatus=1");
break;
case WorkListType.Processed:
strSql.AppendFormat("AND [State]=2 AND CMW.RowStatus=1");
break;
case WorkListType.Delete:
strSql.AppendFormat("AND CMW.RowStatus=0");
break;
}
if (!string.IsNullOrEmpty(approveType))
{
switch (approveType)
{
case "Case":
strSql.AppendFormat("AND WP.MessageType ='00050001' OR WP.MessageType IS NULL");
break;
case "Leave":
strSql.AppendFormat("AND WP.MessageType ='00050002'");
break;
}
}
return(SqlHelper.QueryPageBySql(SqlHelper.SqlConnStringRead, strSql.ToString(), "*", "", "SD", "DESC", pageIndex, pageSize, out totalRecords));
}
