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