//我的流程分页查询 public PartlyCollection <ProcessInstance> LoadListByCreateUserID(int pageIndex, int pageSize, Dictionary <string, string> whereString) { string tableName = ORMapping.GetTableName(typeof(ProcessInstance)); #region Where语句 StringBuilder burWhere = new StringBuilder(); burWhere.Append("1=1 "); if (whereString.ContainsKey("UserID") && !string.IsNullOrEmpty(whereString["UserID"])) { burWhere.AppendFormat(" AND {1}.UserID='{0}'", whereString["UserID"], tableName); } if (whereString.ContainsKey("InstanceName") && !string.IsNullOrEmpty(whereString["InstanceName"]))//标题 { burWhere.AppendFormat(" AND {1}.InstanceName like '%{0}%'", WhereSqlClauseBuilder.EscapeLikeString(whereString["InstanceName"]), tableName); } if (whereString.ContainsKey("CreatedTime") && !string.IsNullOrEmpty(whereString["CreatedTime"]))//创建时间 { //创建时间开始 burWhere.AppendFormat(" AND {0}.CreateTime>= Convert(NVARCHAR(10),'{1}',120) ", tableName, whereString["CreatedTime"]); //创建时间结束 burWhere.AppendFormat(" AND {0}.CreateTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", tableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } if (whereString.ContainsKey("LastUpdatedTime") && !string.IsNullOrEmpty(whereString["LastUpdatedTime"]))//接收时间 { //最后更新时间开始 burWhere.AppendFormat(" AND {0}.LastUpdatedTime>= Convert(NVARCHAR(10),'{1}',120) ", tableName, whereString["LastUpdatedTime"]); //最后更新时间结束 burWhere.AppendFormat(" AND {0}.LastUpdatedTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", tableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } if (whereString.ContainsKey("ProcessCode") && !string.IsNullOrEmpty(whereString["ProcessCode"]))//流程类型 { burWhere.AppendFormat(" AND {1}.ProcessCode='{0}'", whereString["ProcessCode"], tableName); } if (whereString.ContainsKey("Status") && !string.IsNullOrEmpty(whereString["Status"]))//状态 { burWhere.AppendFormat(" AND {1}.Status='{0}'", whereString["Status"], tableName); } burWhere.AppendFormat(" AND {0}", NotDeleted); #endregion PartlyCollection <ProcessInstance> result = GetPageSplitedCollection(pageIndex, pageSize, burWhere.ToString()); return(result); }
//流程分页查询 public PartlyCollection <ProcessInstance> LoadList(int pageIndex, int pageSize, Dictionary <string, string> whereString) { StringBuilder strSql = new StringBuilder(); string ProcessInstanceTableName = ORMapping.GetTableName(typeof(ProcessInstance)); string StakeHolderTableName = ORMapping.GetTableName(typeof(StakeHolder)); string userID = string.Empty; strSql.Append("1=1"); strSql.AppendFormat(" AND {0}.IsDeleted<>1 AND {1}.IsDeleted<>1 ", ProcessInstanceTableName, StakeHolderTableName); if (whereString.ContainsKey("UserID") && !string.IsNullOrEmpty(whereString["UserID"])) { userID = whereString["UserID"]; strSql.AppendFormat(" AND {0}.UserID = '{1}' ", StakeHolderTableName, userID); } strSql.AppendFormat("AND {1}.ProcessInstanceID = {0}.ID", ProcessInstanceTableName, StakeHolderTableName); #region where条件 if (whereString.ContainsKey("InstanceName") && !string.IsNullOrEmpty(whereString["InstanceName"]))//标题 { strSql.AppendFormat(" AND {1}.InstanceName like '%{0}%'", WhereSqlClauseBuilder.EscapeLikeString(whereString["InstanceName"]), ProcessInstanceTableName); } if (whereString.ContainsKey("UserName") && !string.IsNullOrEmpty(whereString["UserName"]))//创建人 { strSql.AppendFormat(" AND ({1}.UserName LIKE '%{0}%')", WhereSqlClauseBuilder.EscapeLikeString(whereString["UserName"]), ProcessInstanceTableName); } if (whereString.ContainsKey("LastUpdatedTime") && !string.IsNullOrEmpty(whereString["LastUpdatedTime"]))//处理时间 { //处理时间开始 strSql.AppendFormat(" AND {0}.LastUpdatedTime>= Convert(NVARCHAR(10),'{1}',120) ", ProcessInstanceTableName, whereString["LastUpdatedTime"]); //处理时间结束 strSql.AppendFormat(" AND {0}.LastUpdatedTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", ProcessInstanceTableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } if (whereString.ContainsKey("CreatedTime") && !string.IsNullOrEmpty(whereString["CreatedTime"]))//接收时间 { //接收时间开始 strSql.AppendFormat(" AND {0}.CreateTime>= Convert(NVARCHAR(10),'{1}',120) ", ProcessInstanceTableName, whereString["CreatedTime"]); //接收时间结束 strSql.AppendFormat(" AND {0}.CreateTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", ProcessInstanceTableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } if (whereString.ContainsKey("Status") && !string.IsNullOrEmpty(whereString["Status"]))//状态 { strSql.AppendFormat(" AND {1}.Status='{0}'", whereString["Status"], ProcessInstanceTableName); } if (whereString.ContainsKey("ProcessCode") && !string.IsNullOrEmpty(whereString["ProcessCode"]))//流程类型 { strSql.AppendFormat(" AND {1}.ProcessCode='{0}'", whereString["ProcessCode"], ProcessInstanceTableName); } #endregion string selectSQL = string.Format("(SELECT DISTINCT {0}.* FROM {0},{1} WHERE {2}) as temp", ProcessInstanceTableName, StakeHolderTableName, strSql.ToString()); string selectFields = "DISTINCT *"; QueryCondition qc = new QueryCondition(pageIndex, pageSize, selectFields, selectSQL, "temp.CreateTime DESC"); PartlyCollection <ProcessInstance> result = GetPageSplitedCollection(qc); return(result); }
public List <ProcessInstance> LoadListByRelatedUserID(string userID, Dictionary <string, string> whereString) { //string sql = string.Format("SELECT {0}.* FROM {0}, {1} WHERE {1}.UserID = {2} AND {1}.ProcessInstanceID = {0}.ProcessInstanceID " + // "AND {0}.ProcessInstanceID NOT IN (SELECT ProcessInstanceID FROM {3} WHERE UserID = {2}) ORDER BY LastUpdatedTime DESC", // ProcessInstance.SourceTable, StakeHolder.SourceTable, userID, TodoWork.SourceTable);//(原始sql) StringBuilder strSql = new StringBuilder(); string ProcessInstanceTableName = ORMapping.GetTableName(typeof(ProcessInstance)); string ApprovalLogTableName = ORMapping.GetTableName(typeof(ApprovalLog)); //strSql.AppendFormat("SELECT {0}.* FROM {0}, {1} WHERE {1}.UserID = {2} AND {1}.ProcessInstanceID = {0}.ProcessInstanceID " + // "AND {0}.ProcessInstanceID NOT IN (SELECT ProcessInstanceID FROM {3} WHERE UserID = {2}) ", // ProcessInstance.SourceTable, StakeHolder.SourceTable, userID, TodoWork.SourceTable); strSql.AppendFormat("SELECT DISTINCT {0}.* FROM {0}, {1} WHERE {1}.UserID = '{2}' AND {1}.ProcessInstanceID = {0}.ID AND {0}.IsDeleted<>1 AND {1}.IsDeleted<>1 ", ProcessInstanceTableName, ApprovalLogTableName, userID); #region where条件 if (whereString.Count > 0) { if (whereString["InstanceName"] != string.Empty)//标题 { strSql.AppendFormat(" AND {1}.InstanceName like '%{0}%'", WhereSqlClauseBuilder.EscapeLikeString(whereString["InstanceName"]), ProcessInstanceTableName); } if (whereString["UserName"] != string.Empty)//创建人 { //strSql.AppendFormat(" AND ({1}.UserName='******' OR {1}.UserCode= '{0}')", whereString["UserName"], // ProcessInstanceTableName); strSql.AppendFormat(" AND ({1}.UserName LIKE '%{0}%')", WhereSqlClauseBuilder.EscapeLikeString(whereString["UserName"]), ProcessInstanceTableName); } if (whereString["LastUpdatedTime"] != string.Empty)//处理时间 { //处理时间开始 strSql.AppendFormat(" AND {0}.LastUpdatedTime>= Convert(NVARCHAR(10),'{1}',120) ", ProcessInstanceTableName, whereString["LastUpdatedTime"]); //处理时间结束 strSql.AppendFormat(" AND {0}.LastUpdatedTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", ProcessInstanceTableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } if (whereString["CreatedTime"] != string.Empty)//接收时间 { //接收时间开始 strSql.AppendFormat(" AND {0}.CreatedTime>= Convert(NVARCHAR(10),'{1}',120) ", ProcessInstanceTableName, whereString["CreatedTime"]); //接收时间结束 strSql.AppendFormat(" AND {0}.CreatedTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", ProcessInstanceTableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } if (whereString["Status"] != string.Empty)//状态 { strSql.AppendFormat(" AND {1}.Status='{0}'", whereString["Status"], ProcessInstanceTableName); } if (whereString["ProcessCode"] != string.Empty)//流程类型 { strSql.AppendFormat(" AND {1}.ProcessCode='{0}'", whereString["ProcessCode"], ProcessInstanceTableName); } } #endregion return(ExecuteQuery(strSql.ToString())); }
public List <ProcessInstance> LoadListByCreateUserID(string userID, Dictionary <string, string> whereString) { WhereSqlClauseBuilder wc = new WhereSqlClauseBuilder(); string ProcessInstanceTableName = ORMapping.GetTableName(typeof(ProcessInstance)); if (!string.IsNullOrEmpty(userID)) { wc.AppendItem("UserID", userID); } string strWhere = wc.ToSqlString(TSqlBuilder.Instance).Trim(); string sqlCommand = ""; if (strWhere == "") { sqlCommand = string.Format("SELECT * FROM {0} WHERE {1} ORDER BY LastUpdatedTime DESC", ProcessInstanceTableName, NotDeleted); } else { #region Where语句 StringBuilder burWhere = new StringBuilder(); if (whereString.Count > 0) { if (whereString["InstanceName"] != string.Empty)//标题 { burWhere.AppendFormat(" AND {1}.InstanceName like '%{0}%'", WhereSqlClauseBuilder.EscapeLikeString(whereString["InstanceName"]), ProcessInstanceTableName); } if (whereString["CreatedTime"] != string.Empty)//创建时间 { //创建时间开始 burWhere.AppendFormat(" AND {0}.CreatedTime>= Convert(NVARCHAR(10),'{1}',120) ", ProcessInstanceTableName, whereString["CreatedTime"]); //创建时间结束 burWhere.AppendFormat(" AND {0}.CreatedTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", ProcessInstanceTableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } if (whereString["LastUpdatedTime"] != string.Empty)//最后更新时间 { //最后更新时间开始 burWhere.AppendFormat(" AND {0}.LastUpdatedTime>= Convert(NVARCHAR(10),'{1}',120) ", ProcessInstanceTableName, whereString["LastUpdatedTime"]); //最后更新时间结束 burWhere.AppendFormat(" AND {0}.LastUpdatedTime< Convert(NVARCHAR(10),dateadd(d,1,'{1}'),120)", ProcessInstanceTableName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } if (whereString["ProcessCode"] != string.Empty)//流程类型 { burWhere.AppendFormat(" AND {1}.ProcessCode='{0}'", whereString["ProcessCode"], ProcessInstanceTableName); } if (whereString["Status"] != string.Empty)//状态 { burWhere.AppendFormat(" AND {1}.Status='{0}'", whereString["Status"], ProcessInstanceTableName); } } #endregion sqlCommand = string.Format("SELECT * FROM {0} WHERE {1} {2} AND {3} ORDER BY LastUpdatedTime DESC", ORMapping.GetTableName(typeof(ProcessInstance)), wc.ToSqlString(TSqlBuilder.Instance), burWhere, NotDeleted); } return(ExecuteQuery(sqlCommand)); }