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