/// <summary> /// 按照JobID查找对应Schedule关系数据。不使用TenantCode筛选 /// </summary> /// <param name="jobIDs"></param> /// <returns></returns> public JobScheduleWithJobIDCollection LoadByJobID(params string[] jobIDs) { jobIDs.NullCheck("jobIDs"); InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("JOB_ID"); inBuilder.AppendItem(jobIDs); JobScheduleWithJobIDCollection result = null; if (inBuilder.Count > 0) { //ConnectiveSqlClauseCollection builder = new ConnectiveSqlClauseCollection(inBuilder, // new WhereSqlClauseBuilder().AppendTenantCode("D.TENANT_CODE"), // new WhereSqlClauseBuilder().AppendTenantCode("S.TENANT_CODE")); //string sql = string.Format(QUERY_SCHEDULE_DEF_BY_JOB_ID_SQL_CLAUSE, // builder.ToSqlString(TSqlBuilder.Instance)); string sql = string.Format(QUERY_SCHEDULE_DEF_BY_JOB_ID_SQL_CLAUSE, inBuilder.ToSqlString(TSqlBuilder.Instance)); result = QueryData <JobScheduleWithJobID, JobScheduleWithJobIDCollection>( ORMapping.GetMappingInfo <JobScheduleWithJobID>(), sql); } else { result = new JobScheduleWithJobIDCollection(); } return(result); }
public int Delete(string[] ids) { InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("JOB_ID"); inBuilder.AppendItem(ids); int result = 0; if (inBuilder.Count > 0) { string where = inBuilder.ToSqlString(TSqlBuilder.Instance); StringBuilder sqlString = new StringBuilder(); sqlString.AppendFormat("DELETE FROM WF.JOB_SCHEDULES WHERE {0} ", where); sqlString.Append(TSqlBuilder.Instance.DBStatementSeperator); sqlString.AppendFormat("DELETE FROM WF.JOB_INVOKE_SERVICE WHERE {0} ", where); sqlString.Append(TSqlBuilder.Instance.DBStatementSeperator); sqlString.AppendFormat("DELETE FROM WF.JOB_START_WORKFLOW WHERE {0} ", where); sqlString.Append(TSqlBuilder.Instance.DBStatementSeperator); sqlString.AppendFormat("DELETE WF.JOBS WHERE {0} ", where); result = DbHelper.RunSqlWithTransaction(sqlString.ToString(), this.GetConnectionName()); } return(result); }
public void Delete(WfActivityCollection pendingActivities) { pendingActivities.NullCheck("pendingActivities"); InSqlClauseBuilder builder = new InSqlClauseBuilder("ACTIVITY_ID"); pendingActivities.ForEach(pai => builder.AppendItem(pai.ID)); if (builder.Count > 0) { string sql = string.Format("DELETE FROM WF.PENDING_ACTIVITIES WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); DbHelper.RunSql(sql, this.GetConnectionName()); } }
/// <summary> /// 载入所有下级单元对象 /// </summary> /// <param name="parentIDs"></param> /// <param name="normalOnly"></param> /// <param name="timePoint"></param> /// <returns></returns> public SchemaObjectCollection LoadCurrentSubUnitsDeeply(string[] parentIDs, DateTime timePoint) { parentIDs.NullCheck("parentIDs"); if (parentIDs.Length > 0) { InSqlClauseBuilder inSql = new InSqlClauseBuilder("ParentID").AppendItem(parentIDs); string timeFilterClause = timePoint == DateTime.MinValue ? TSqlBuilder.Instance.DBCurrentTimeFunction : TSqlBuilder.Instance.FormatDateTime(timePoint); string conditionClause = inSql.ToSqlString(TSqlBuilder.Instance); string sql = string.Format(sqlTemplateSubUnits, timeFilterClause, conditionClause); return LoadSchemaObjects(sql); } else return new SchemaObjectCollection(); }
private static string GetJobSchedulesDeleteClause(params string[] jobIDs) { InSqlClauseBuilder builder = new InSqlClauseBuilder("JOB_ID"); builder.AppendItem(jobIDs); string sql = string.Empty; if (builder.Count > 0) { sql = string.Format("{0} {1}", DELETE_JOB_SCHEDULES_SQL_CLAUSE, builder.ToSqlString(TSqlBuilder.Instance)); } return(sql); }
/// <summary> /// 用户或角色直接所属的角色矩阵ID /// </summary> /// <param name="operatorIDs">用户或角色所属的角色矩阵ID集合</param> /// <returns></returns> public List <string> OperatorBelongToRoleIDsDirectly(params string[] operatorIDs) { operatorIDs.NullCheck("userIDs"); List <string> result = new List <string>(); InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("OPERATOR_ID"); inBuilder.AppendItem(operatorIDs); if (inBuilder.IsEmpty == false) { WhereSqlClauseBuilder tenantBuilder = new WhereSqlClauseBuilder(); tenantBuilder.AppendTenantCode(); string tenantCriteria = string.Empty; if (tenantBuilder.IsEmpty == false) { tenantCriteria = " AND " + tenantBuilder.ToSqlString(TSqlBuilder.Instance); } string sql = string.Format("SELECT ROLE_ID FROM WF.ROLE_PROPERTIES_USER_CONTAINERS WHERE {0}{1}", inBuilder.ToSqlString(TSqlBuilder.Instance), tenantCriteria); using (DbContext context = DbContext.GetContext(GetConnectionName())) { Database db = DatabaseFactory.Create(context); DbCommand cmd = db.GetSqlStringCommand(sql); using (var dr = db.ExecuteReader(cmd)) { while (dr.Read()) { result.Add(dr.GetString(0)); } } } } return(result); }
/// <summary> /// 根据角色ID判断哪些角色有了扩展属性定义 /// </summary> /// <param name="roleIDs"></param> /// <returns></returns> public Dictionary <string, bool> AreExist(IEnumerable <string> roleIDs) { roleIDs.NullCheck("roleIDs"); Dictionary <string, bool> result = new Dictionary <string, bool>(); InSqlClauseBuilder builder = new InSqlClauseBuilder(); foreach (string roleID in roleIDs) { if (result.ContainsKey(roleID) == false) { result.Add(roleID, false); builder.AppendItem(roleID); } } if (result.Count > 0) { WhereSqlClauseBuilder tenantBuilder = new WhereSqlClauseBuilder(); tenantBuilder.AppendTenantCode(); string tenantCriteria = string.Empty; if (tenantBuilder.IsEmpty == false) { tenantCriteria = " AND " + tenantBuilder.ToSqlString(TSqlBuilder.Instance); } string sql = string.Format("SELECT ROLE_ID FROM WF.ROLE_PROPERTIES_DEFINITIONS WHERE ROLE_ID IN ({0}){1} GROUP BY ROLE_ID", builder.ToSqlString(TSqlBuilder.Instance), tenantCriteria); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; foreach (DataRow row in table.Rows) { result[row["ROLE_ID"].ToString()] = true; } } return(result); }
public void DeleteByProcesses(IEnumerable <IWfProcess> processes) { processes.NullCheck("processes"); InSqlClauseBuilder builder = new InSqlClauseBuilder("PROCESS_ID"); foreach (IWfProcess process in processes) { builder.AppendItem(process.ID); } if (builder.Count > 0) { string sql = string.Format("DELETE FROM WF.PENDING_ACTIVITIES WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); DbHelper.RunSql(sql, this.GetConnectionName()); } }
public void Update(string processID, WfProcessCurrentActivityCollection pcas) { processID.CheckStringIsNullOrEmpty("processID"); pcas.NullCheck("pcas"); StringBuilder strB = new StringBuilder(); InSqlClauseBuilder deleteActivityIDs = new InSqlClauseBuilder("ACTIVITY_ID"); foreach (WfProcessCurrentActivity pca in pcas) { if (strB.Length > 0) { strB.Append(TSqlBuilder.Instance.DBStatementSeperator); } strB.Append(ORMapping.GetInsertSql(pca, TSqlBuilder.Instance)); deleteActivityIDs.AppendItem(pca.ActivityID); } WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendItem("PROCESS_ID", processID); wBuilder.AppendTenantCodeSqlClause(typeof(WfProcessCurrentActivity)); string sqlDelete = string.Format("DELETE WF.PROCESS_CURRENT_ACTIVITIES WHERE {0}", wBuilder.ToSqlString(TSqlBuilder.Instance)); if (deleteActivityIDs.Count > 0) { sqlDelete += string.Format(" AND {0}", deleteActivityIDs.ToSqlString(TSqlBuilder.Instance)); } string sql = sqlDelete; if (strB.Length > 0) { sql += TSqlBuilder.Instance.DBStatementSeperator + strB.ToString(); } DbHelper.RunSqlWithTransaction(sql, GetConnectionName()); }
public DataView Query(string userID, string where, int startRowIndex, int maximumRows, string orderBy, ref int totalCount) { List<string> operatorIDs = new List<string>(); SchemaObjectCollection userRoles = SCSnapshotAdapter.Instance.QueryUserBelongToRoles(new string[] { "Roles" }, string.Empty, new string[] { userID }, false, DBTimePointActionContext.Current.TimePoint); userRoles.ForEach(r => operatorIDs.Add(r.ID)); operatorIDs.Add(userID); List<string> roleMatrixIDs = SOARolePropertiesAdapter.Instance.OperatorBelongToRoleIDsDirectly(operatorIDs.ToArray()); InSqlClauseBuilder roleInSql = new InSqlClauseBuilder("R.ID"); roleInSql.AppendItem(roleMatrixIDs.ToArray()); DataView view = this.innerSource.Query(startRowIndex, maximumRows, roleInSql.ToSqlString(TSqlBuilder.Instance), orderBy, ref totalCount); this.lastQueryCount = totalCount; return view; }
/// <summary> /// 设置消息类别 /// </summary> /// <param name="categoryID">类别ID,若为"NULL"则为无类别</param> /// <param name="userTaskIDs">TaskID</param> public void SetTaskCategory(string categoryID, params string[] userTaskIDs) { ExceptionHelper.TrueThrow <ArgumentNullException>(null == categoryID, "categoryID"); ExceptionHelper.TrueThrow <ArgumentNullException>(null == userTaskIDs, "userTaskIDs"); if (userTaskIDs.Length > 0) { InSqlClauseBuilder inSQL = new InSqlClauseBuilder(); inSQL.AppendItem(userTaskIDs); //当指定的类别存在或指定NULL时,才对消息进行设置。 //防止设置类别时该类别已被删除,造成脏数据。 string strSql = string.Format( @"IF (((SELECT COUNT(*) FROM WF.USER_TASK_CATEGORY WHERE CATEGORY_GUID = {0}) = 1) OR ({0} IS NULL)) BEGIN UPDATE WF.USER_TASK SET CATEGORY_GUID = {0} WHERE TASK_GUID IN ({1}) END" , TSqlBuilder.Instance.CheckQuotationMark(categoryID, categoryID != "NULL"), inSQL.ToSqlString(TSqlBuilder.Instance)); DbHelper.RunSql(strSql, GetConnectionName()); } }
public int Delete(string[] ids) { StringBuilder strBuilder = new StringBuilder(); InSqlClauseBuilder builder = new InSqlClauseBuilder("JOB_ID"); builder.AppendItem(ids); int result = 0; if (builder.Count > 0) { strBuilder.AppendFormat("{0} {1}", DELETE_JOBS_SQL_CLAUSE, builder.ToSqlString(TSqlBuilder.Instance)); strBuilder.Append(GetJobSchedulesDeleteClause(ids)); result = DbHelper.RunSqlWithTransaction(strBuilder.ToString(), GetConnectionName()); } return(result); }
public DataView Query(string userID, string where, int startRowIndex, int maximumRows, string orderBy, ref int totalCount) { List <string> operatorIDs = new List <string>(); SchemaObjectCollection userRoles = SCSnapshotAdapter.Instance.QueryUserBelongToRoles(new string[] { "Roles" }, string.Empty, new string[] { userID }, false, DBTimePointActionContext.Current.TimePoint); userRoles.ForEach(r => operatorIDs.Add(r.ID)); operatorIDs.Add(userID); List <string> roleMatrixIDs = SOARolePropertiesAdapter.Instance.OperatorBelongToRoleIDsDirectly(operatorIDs.ToArray()); InSqlClauseBuilder roleInSql = new InSqlClauseBuilder("R.ID"); roleInSql.AppendItem(roleMatrixIDs.ToArray()); DataView view = this.innerSource.Query(startRowIndex, maximumRows, roleInSql.ToSqlString(TSqlBuilder.Instance), orderBy, ref totalCount); this.lastQueryCount = totalCount; return(view); }
public int Delete(string[] ids) { InSqlClauseBuilder builder = new InSqlClauseBuilder("JOB_ID"); builder.AppendItem(ids); int result = 0; if (builder.Count > 0) { string sql = string.Format("{0} {1}", DELETE_JOB_START_WORKFLOW_SQL_CLAUSE, builder.ToSqlString(TSqlBuilder.Instance)); using (TransactionScope tran = TransactionScopeFactory.Create()) { JobBaseAdapter.Instance.Delete(ids); result = DbHelper.RunSqlWithTransaction(sql, GetConnectionName()); tran.Complete(); } } return(result); }
public void Update(string processID, WfProcessCurrentActivityCollection pcas) { processID.CheckStringIsNullOrEmpty("processID"); pcas.NullCheck("pcas"); StringBuilder strB = new StringBuilder(); InSqlClauseBuilder deleteActivityIDs = new InSqlClauseBuilder("ACTIVITY_ID"); foreach (WfProcessCurrentActivity pca in pcas) { if (strB.Length > 0) strB.Append(TSqlBuilder.Instance.DBStatementSeperator); strB.Append(ORMapping.GetInsertSql(pca, TSqlBuilder.Instance)); deleteActivityIDs.AppendItem(pca.ActivityID); } WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendItem("PROCESS_ID", processID); wBuilder.AppendTenantCodeSqlClause(typeof(WfProcessCurrentActivity)); string sqlDelete = string.Format("DELETE WF.PROCESS_CURRENT_ACTIVITIES WHERE {0}", wBuilder.ToSqlString(TSqlBuilder.Instance)); if (deleteActivityIDs.Count > 0) sqlDelete += string.Format(" AND {0}", deleteActivityIDs.ToSqlString(TSqlBuilder.Instance)); string sql = sqlDelete; if (strB.Length > 0) sql += TSqlBuilder.Instance.DBStatementSeperator + strB.ToString(); DbHelper.RunSqlWithTransaction(sql, GetConnectionName()); }
/// <summary> /// 取消待办箱置顶 /// </summary> /// <param name="taskID"></param> public void CancelTaskAtTop(params string[] taskID) { ExceptionHelper.TrueThrow <ArgumentNullException>(null == taskID, "taskID"); if (taskID.Length > 0) { InSqlClauseBuilder inSQL = new InSqlClauseBuilder(); inSQL.AppendItem(taskID); string strSQL = string.Format("UPDATE WF.USER_TASK SET TOP_FLAG = 0 WHERE TASK_GUID IN ({0})", inSQL.ToSqlString(TSqlBuilder.Instance)); DbHelper.RunSql(strSQL, GetConnectionName()); } }
/// <summary> /// 根据角色ID判断哪些角色有了扩展属性定义 /// </summary> /// <param name="roleIDs"></param> /// <returns></returns> public Dictionary<string, bool> AreExist(IEnumerable<string> roleIDs) { roleIDs.NullCheck("roleIDs"); Dictionary<string, bool> result = new Dictionary<string, bool>(); InSqlClauseBuilder builder = new InSqlClauseBuilder(); foreach (string roleID in roleIDs) { if (result.ContainsKey(roleID) == false) { result.Add(roleID, false); builder.AppendItem(roleID); } } if (result.Count > 0) { WhereSqlClauseBuilder tenantBuilder = new WhereSqlClauseBuilder(); tenantBuilder.AppendTenantCode(); string tenantCriteria = string.Empty; if (tenantBuilder.IsEmpty == false) tenantCriteria = " AND " + tenantBuilder.ToSqlString(TSqlBuilder.Instance); string sql = string.Format("SELECT ROLE_ID FROM WF.ROLE_PROPERTIES_DEFINITIONS WHERE ROLE_ID IN ({0}){1} GROUP BY ROLE_ID", builder.ToSqlString(TSqlBuilder.Instance), tenantCriteria); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; foreach (DataRow row in table.Rows) result[row["ROLE_ID"].ToString()] = true; } return result; }
/// <summary> /// 取消待办箱置顶 /// </summary> /// <param name="taskID"></param> public void CancelTaskAtTop(params string[] taskID) { ExceptionHelper.TrueThrow<ArgumentNullException>(null == taskID, "taskID"); if (taskID.Length > 0) { InSqlClauseBuilder inSQL = new InSqlClauseBuilder(); inSQL.AppendItem(taskID); string strSQL = string.Format("UPDATE WF.USER_TASK SET TOP_FLAG = 0 WHERE TASK_GUID IN ({0})", inSQL.ToSqlString(TSqlBuilder.Instance)); DbHelper.RunSql(strSQL, GetConnectionName()); } }
/// <summary> /// 用户或角色直接所属的角色矩阵ID /// </summary> /// <param name="operatorIDs">用户或角色所属的角色矩阵ID集合</param> /// <returns></returns> public List<string> OperatorBelongToRoleIDsDirectly(params string[] operatorIDs) { operatorIDs.NullCheck("userIDs"); List<string> result = new List<string>(); InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("OPERATOR_ID"); inBuilder.AppendItem(operatorIDs); if (inBuilder.IsEmpty == false) { WhereSqlClauseBuilder tenantBuilder = new WhereSqlClauseBuilder(); tenantBuilder.AppendTenantCode(); string tenantCriteria = string.Empty; if (tenantBuilder.IsEmpty == false) tenantCriteria = " AND " + tenantBuilder.ToSqlString(TSqlBuilder.Instance); string sql = string.Format("SELECT ROLE_ID FROM WF.ROLE_PROPERTIES_USER_CONTAINERS WHERE {0}{1}", inBuilder.ToSqlString(TSqlBuilder.Instance), tenantCriteria); using (DbContext context = DbContext.GetContext(GetConnectionName())) { Database db = DatabaseFactory.Create(context); DbCommand cmd = db.GetSqlStringCommand(sql); using (var dr = db.ExecuteReader(cmd)) { while (dr.Read()) result.Add(dr.GetString(0)); } } } return result; }
public int BatchDelete(DataTable dataTable) { Database db = DatabaseFactory.Create(GetConnectionName()); InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("SCObjectID"); int result = 0; if (dataTable.Rows.Count > 0) { foreach (DataRow row in dataTable.Rows) { inBuilder.AppendItem(row["SCObjectID"]); } string sqlDelete = string.Format("DELETE FROM [SC].[PermissionCenter_AD_IDMapping] WHERE {0}", inBuilder.ToSqlString(TSqlBuilder.Instance)); result = db.ExecuteNonQuery(CommandType.Text, sqlDelete); } return(result); }
/// <summary> /// 设置消息类别 /// </summary> /// <param name="categoryID">类别ID,若为"NULL"则为无类别</param> /// <param name="userTaskIDs">TaskID</param> public void SetTaskCategory(string categoryID, params string[] userTaskIDs) { ExceptionHelper.TrueThrow<ArgumentNullException>(null == categoryID, "categoryID"); ExceptionHelper.TrueThrow<ArgumentNullException>(null == userTaskIDs, "userTaskIDs"); if (userTaskIDs.Length > 0) { InSqlClauseBuilder inSQL = new InSqlClauseBuilder(); inSQL.AppendItem(userTaskIDs); //当指定的类别存在或指定NULL时,才对消息进行设置。 //防止设置类别时该类别已被删除,造成脏数据。 string strSql = string.Format( @"IF (((SELECT COUNT(*) FROM WF.USER_TASK_CATEGORY WHERE CATEGORY_GUID = {0}) = 1) OR ({0} IS NULL)) BEGIN UPDATE WF.USER_TASK SET CATEGORY_GUID = {0} WHERE TASK_GUID IN ({1}) END", TSqlBuilder.Instance.CheckQuotationMark(categoryID, categoryID != "NULL"), inSQL.ToSqlString(TSqlBuilder.Instance)); DbHelper.RunSql(strSql, GetConnectionName()); } }