/// <summary>
/// 读取用户对于各应用的管理权限，不通过缓存
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public WfApplicationAuthCollection LoadUserApplicationAuthInfo(IUser user)
{
user.NullCheck("user");
List <IRole> roles = user.Roles.GetAllRoles();
List <string> roleIDs = new List <string>();
foreach (IRole role in roles)
{
roleIDs.Add(role.ID);
}
string sql = "SELECT * FROM WF.UNION_APP_AUTH_WITH_ALIAS WHERE {0}";
WfApplicationAuthCollection result = new WfApplicationAuthCollection();
if (roleIDs.Count > 0)
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("ROLE_ID");
inBuilder.AppendItem(roleIDs.ToArray());
ConnectiveSqlClauseCollection builder = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And,
inBuilder, new WhereSqlClauseBuilder().AppendTenantCode(typeof(WfApplicationAuth)));
sql = string.Format(sql, inBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataView view = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0].DefaultView;
ORMapping.DataViewToCollection(result, view);
}
return(result);
}
/// <summary>
/// 加载流程运行时的信息
/// </summary>
/// <param name="processIDs"></param>
/// <returns></returns>
public WfProcessCurrentInfoCollection Load(bool fillAssignees, params string[] processIDs)
{
processIDs.NullCheck("processIDs");
WfProcessCurrentInfoCollection result = new WfProcessCurrentInfoCollection();
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(processIDs);
if (builder.Count > 0)
{
string fieldNames = ORMapping.GetSelectFieldsNameSql<WfProcessInstanceData>("Data");
string sql = string.Format("SELECT {0} FROM WF.PROCESS_INSTANCES WHERE INSTANCE_ID {1}",
fieldNames,
builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
if (fillAssignees)
FillAssignees(result);
}
return result;
}
/// <summary>
/// 加载流程运行时的信息
/// </summary>
/// <param name="processIDs"></param>
/// <returns></returns>
public WfProcessCurrentInfoCollection Load(bool fillAssignees, params string[] processIDs)
{
processIDs.NullCheck("processIDs");
WfProcessCurrentInfoCollection result = new WfProcessCurrentInfoCollection();
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(processIDs);
if (builder.Count > 0)
{
string fieldNames = ORMapping.GetSelectFieldsNameSql <WfProcessInstanceData>("Data");
string sql = string.Format("SELECT {0} FROM WF.PROCESS_INSTANCES WHERE INSTANCE_ID {1}",
fieldNames,
builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
if (fillAssignees)
{
FillAssignees(result);
}
}
return(result);
}
public UserTaskCollection LoadUserTasksByActivity(string activityID, Action<InSqlClauseBuilder> action)
{
action.NullCheck("action");
InSqlClauseBuilder builder = new InSqlClauseBuilder();
action(builder);
string sql = string.Format("SELECT * FROM WF.USER_TASK WHERE ACTIVITY_ID = {0}",
TSqlBuilder.Instance.CheckUnicodeQuotationMark(activityID));
if (builder.Count > 0)
sql += " AND " + builder.ToSqlStringWithInOperator(TSqlBuilder.Instance);
UserTaskCollection result = new UserTaskCollection();
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
UserTask task = new UserTask();
ORMapping.DataRowToObject(row, task);
result.Add(task);
}
return result;
}
private static void FillAssignees(WfProcessCurrentInfoCollection result)
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder();
result.ForEach(currentInfo => inBuilder.AppendItem(currentInfo.CurrentActivityID));
if (inBuilder.Count > 0)
{
string sql = string.Format("SELECT * FROM WF.PROCESS_CURRENT_ASSIGNEES WHERE ACTIVITY_ID {0} ORDER BY ID",
inBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
DataView assigneeView = new DataView(table);
assigneeView.Sort = "ACTIVITY_ID";
foreach (var currentInfo in result)
{
DataRowView[] rows = assigneeView.FindRows(currentInfo.CurrentActivityID);
Array.ForEach(rows, drv =>
{
currentInfo.Assignees.Add(DataRowToAssignee(drv.Row));
});
}
}
}
public UserTaskCollection LoadUserTasksByActivity(string activityID, Action <InSqlClauseBuilder> action)
{
action.NullCheck("action");
InSqlClauseBuilder builder = new InSqlClauseBuilder();
action(builder);
string sql = string.Format("SELECT * FROM WF.USER_TASK WHERE ACTIVITY_ID = {0}",
TSqlBuilder.Instance.CheckUnicodeQuotationMark(activityID));
if (builder.Count > 0)
{
sql += " AND " + builder.ToSqlStringWithInOperator(TSqlBuilder.Instance);
}
UserTaskCollection result = new UserTaskCollection();
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
UserTask task = new UserTask();
ORMapping.DataRowToObject(row, task);
result.Add(task);
}
return(result);
}
/// <summary>
/// 删除某个事件下的频道（UI上用于频道列表）
/// </summary>
/// <param name="eventID"></param>
/// <param name="channelIDs"></param>
/// <param name="includeDefault">是否包含默认频道</param>
public int DeleteChannels(string eventID, IEnumerable <string> channelIDs, bool includeDefault = false)
{
eventID.CheckStringIsNullOrEmpty("eventID");
channelIDs.NullCheck("channelIDs");
InSqlClauseBuilder builder = new InSqlClauseBuilder("ChannelID");
channelIDs.ForEach(channelID => builder.AppendItem(channelID));
ORMappingItemCollection mappings = ORMapping.GetMappingInfo(typeof(AMSEventChannel));
string sql = string.Format("DELETE {0} WHERE {1}", mappings.TableName, builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
int result = 0;
if (builder.IsEmpty == false)
{
if (includeDefault == false)
{
sql += " AND IsDefault <> 1";
}
result = DbHelper.RunSql(sql, this.GetConnectionName());
}
return(result);
}
/// <summary>
/// 按照In条件加载对象
/// </summary>
/// <param name="inAction"></param>
/// <param name="orderByAction"></param>
/// <returns></returns>
public TCollection LoadByInBuilder(Action <InSqlClauseBuilder> inAction, Action <OrderBySqlClauseBuilder> orderByAction)
{
inAction.NullCheck("whereAction");
InSqlClauseBuilder builder = new InSqlClauseBuilder();
inAction(builder);
string condition = string.Empty;
if (builder.Count > 0)
{
condition = builder.ToSqlStringWithInOperator(TSqlBuilder.Instance);
}
OrderBySqlClauseBuilder orderByBuilder = null;
if (orderByAction != null)
{
orderByBuilder = new OrderBySqlClauseBuilder();
orderByAction(orderByBuilder);
}
return(InnerLoadByBuilder(condition, orderByBuilder));
}
/// <summary>
/// 获取AD组内的人员在安全中心中的人员信息
/// </summary>
/// <param name="groupEntry"></param>
/// <returns></returns>
private static IEnumerable <DataRow> CollectGroupUserRows(DirectoryEntry groupEntry)
{
InSqlClauseBuilder builder = new InSqlClauseBuilder();
ADHelper helper = ADHelper.GetInstance();
helper.EnumGroupMembers(groupEntry, sr =>
{
builder.AppendItem(helper.GetSearchResultPropertyStrValue("samAccountName", sr));
});
List <DataRow> result = new List <DataRow>(256);
if (builder.Count > 0)
{
string sql = string.Format("SELECT OU_USERS.* FROM OU_USERS INNER JOIN USERS ON USER_GUID = GUID WHERE SIDELINE = 0 AND LOGON_NAME {0} ORDER BY GLOBAL_SORT",
builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = null;
DbHelper.ExecSql(db => table = db.ExecuteDataSet(CommandType.Text, sql).Tables[0]);
foreach (DataRow row in table.Rows)
{
result.Add(row);
}
}
return(result);
}
private void FillAllChildrenRelationsRecursively(SCSimpleRelationObjectCollection relations, InSqlClauseBuilder builder, LoadingRelationsRecursivelyHandler handler, object context)
{
if (builder.Count > 0)
{
string sql = string.Format("SELECT ParentID, ObjectID FROM {0} WHERE {1} AND VersionStartTime <= GETDATE() AND VersionEndTime > GETDATE() AND STATUS = 1",
GetMappingInfo().TableName, builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
SCSimpleRelationObjectCollection tempRelations = new SCSimpleRelationObjectCollection();
ORMapping.DataViewToCollection(tempRelations, table.DefaultView);
InSqlClauseBuilder subBuilder = new InSqlClauseBuilder("ParentID");
foreach (SCSimpleRelationObject r in tempRelations)
{
subBuilder.AppendItem(r.ID);
relations.Add(r);
}
if (handler != null)
{
handler(tempRelations, context);
}
FillAllChildrenRelationsRecursively(relations, subBuilder, handler, context);
}
}
private static DataSet GetRolesByIDs(string[] roleIDs)
{
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(roleIDs);
string sql = string.Format("SELECT R.ID, R.APP_ID, R.NAME, R.CODE_NAME, R.DESCRIPTION, R.CLASSIFY, R.SORT_ID, R.ALLOW_DELEGATE, R.INHERITED FROM ROLES R WHERE {0} ORDER BY R.CLASSIFY DESC, R.SORT_ID",
builder.Count > 0 ? "ID " + builder.ToSqlStringWithInOperator(TSqlBuilder.Instance) : "1 = 0");
return(OGUCommonDefine.ExecuteDataset(sql));
}
private static void eventContainer_SetUserTasksAccomplished(UserTaskCollection tasks, Dictionary <object, object> context)
{
ExceptionHelper.FalseThrow <ArgumentNullException>(tasks != null, "tasks");
//需要删除的待办的ID
InSqlClauseBuilder deleteTaskIDs = new InSqlClauseBuilder();
StringBuilder strB = new StringBuilder();
ORMappingItemCollection mapping = ORMapping.GetMappingInfo <UserTask>().Clone();
string userTaskTableName = mapping.TableName;
mapping.TableName = "WF.USER_ACCOMPLISHED_TASK";
foreach (UserTask task in tasks)
{
UserTask clonedUserTask = GetClonedAccomplishedUserTask(task);
if (strB.Length > 0)
{
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
}
WhereSqlClauseBuilder builder = GetDeleteAccomplishedUserTaskWhereBuilder(task);
//删除已办
strB.AppendFormat("DELETE {0} WHERE {1}",
mapping.TableName,
builder.ToSqlString(TSqlBuilder.Instance));
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.Append(GetUserTaskInsertSql(clonedUserTask, mapping, "Category", "TopFlag"));
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
deleteTaskIDs.AppendItem(task.TaskID);
}
if (strB.Length > 0 && deleteTaskIDs.IsEmpty == false)
{
//删除待办
strB.AppendFormat("DELETE {0} WHERE TASK_GUID {1}",
userTaskTableName,
deleteTaskIDs.ToSqlStringWithInOperator(TSqlBuilder.Instance));
}
if (strB.Length > 0)
{
DbHelper.RunSqlWithTransaction(strB.ToString(), GetConnectionName());
}
}
private void ExecuteQuery()
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
string appName = string.IsNullOrEmpty(txtApplicationName.Text) == true ? "全部" : txtApplicationName.Text;
if (appName.IsNotEmpty())
{
if ("全部" == appName)
{
if (!RolesDefineConfig.GetConfig().IsCurrentUserInRoles("ProcessAdmin"))
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder();
inBuilder.AppendItem(AllApplicationNames());
builder.AppendItem("APPLICATION_NAME", inBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance), "", true);
}
}
else
{
if ("其它" == appName)
{
builder.AppendItem("APPLICATION_NAME", " ");
}
else
{
builder.AppendItem("APPLICATION_NAME", appName);
}
}
}
if (!string.IsNullOrEmpty(txtProgramName.Text))
{
builder.AppendItem("PROGRAM_NAME", "%" + TSqlBuilder.Instance.CheckQuotationMark(txtProgramName.Text, false) + "%", "like");
}
if (!string.IsNullOrEmpty(txtProcessKey.Text))
{
builder.AppendItem("PROCESS_KEY", "%" + TSqlBuilder.Instance.CheckQuotationMark(txtProcessKey.Text, false) + "%", "like");
}
if (!string.IsNullOrEmpty(txtProcessName.Text))
{
builder.AppendItem("PROCESS_NAME", "%" + TSqlBuilder.Instance.CheckQuotationMark(txtProcessName.Text, false) + "%", "like");
}
if (!string.IsNullOrEmpty(ddlEnabled.SelectedValue))
{
builder.AppendItem("ENABLED", TSqlBuilder.Instance.CheckQuotationMark(ddlEnabled.SelectedValue, false));
}
builder.AppendTenantCode();
this.BindGrid(builder);
}
/// <summary>
/// 根据ID和时间点载入所有父级关系
/// </summary>
/// <param name="ids">子级ID</param>
/// <param name="includingSelf">结果中是否包含自己</param>
/// <param name="timePoint"></param>
/// <returns></returns>
public Dictionary <string, SCSimpleObjectCollection> LoadAllParentsInfo(string[] ids, bool includingSelf, DateTime timePoint)
{
ids.NullCheck("ids");
Dictionary <string, SCSimpleObjectCollection> result = new Dictionary <string, SCSimpleObjectCollection>(ids.Length, StringComparer.OrdinalIgnoreCase);
if (ids.Length > 0)
{
string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(),
"MCS.Library.SOA.DataObjects.Security.Adapters.Templates.LoadAllParentInfo.sql");
string timeString = TSqlBuilder.Instance.DBCurrentTimeFunction;
if (timePoint != DateTime.MinValue)
{
timeString = TSqlBuilder.Instance.FormatDateTime(timePoint);
}
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("ID");
inBuilder.AppendItem(ids);
string sql = string.Format(sqlTemplate, timeString, inBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
DataView view = new DataView(table);
view.Sort = "ID";
foreach (string id in ids)
{
if (result.ContainsKey(id) == false)
{
SCSimpleObjectCollection tempParents = new SCSimpleObjectCollection();
FillAllParents(id, view, includingSelf, tempParents);
SCSimpleObjectCollection parents = new SCSimpleObjectCollection();
//转换次序
for (int i = tempParents.Count - 1; i >= 0; i--)
{
parents.Add(tempParents[i]);
}
result.Add(id, parents);
}
}
}
return(result);
}
/// <summary>
/// 删除流程运行时活动节点的Assignees
/// </summary>
/// <param name="activityID"></param>
public void DeleteProcessCurrentAssignees(WfProcessCurrentInfoCollection processesInfo)
{
InSqlClauseBuilder builder = new InSqlClauseBuilder("ACTIVITY_ID");
processesInfo.ForEach(p => builder.AppendItem(p.CurrentActivityID));
if (builder.Count > 0)
{
string sql = string.Format("DELETE WF.PROCESS_CURRENT_ASSIGNEES WHERE {0}",
builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
}
}
private static void eventContainer_SetUserTasksAccomplished(UserTaskCollection tasks, Dictionary<object, object> context)
{
ExceptionHelper.FalseThrow<ArgumentNullException>(tasks != null, "tasks");
//需要删除的待办的ID
InSqlClauseBuilder deleteTaskIDs = new InSqlClauseBuilder();
StringBuilder strB = new StringBuilder();
ORMappingItemCollection mapping = ORMapping.GetMappingInfo<UserTask>().Clone();
string userTaskTableName = mapping.TableName;
mapping.TableName = "WF.USER_ACCOMPLISHED_TASK";
foreach (UserTask task in tasks)
{
UserTask clonedUserTask = GetClonedAccomplishedUserTask(task);
if (strB.Length > 0)
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
WhereSqlClauseBuilder builder = GetDeleteAccomplishedUserTaskWhereBuilder(task);
//删除已办
strB.AppendFormat("DELETE {0} WHERE {1}",
mapping.TableName,
builder.ToSqlString(TSqlBuilder.Instance));
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.Append(GetUserTaskInsertSql(clonedUserTask, mapping, "Category", "TopFlag"));
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
deleteTaskIDs.AppendItem(task.TaskID);
}
if (strB.Length > 0 && deleteTaskIDs.IsEmpty == false)
{
//删除待办
strB.AppendFormat("DELETE {0} WHERE TASK_GUID {1}",
userTaskTableName,
deleteTaskIDs.ToSqlStringWithInOperator(TSqlBuilder.Instance));
}
if (strB.Length > 0)
DbHelper.RunSqlWithTransaction(strB.ToString(), GetConnectionName());
}
/// <summary>
/// 根据ID和时间点载入所有父级关系
/// </summary>
/// <param name="ids">子级ID</param>
/// <param name="includingSelf">结果中是否包含自己</param>
/// <param name="timePoint"></param>
/// <returns></returns>
public Dictionary<string, SCSimpleObjectCollection> LoadAllParentsInfo(string[] ids, bool includingSelf, DateTime timePoint)
{
ids.NullCheck("ids");
Dictionary<string, SCSimpleObjectCollection> result = new Dictionary<string, SCSimpleObjectCollection>(ids.Length, StringComparer.OrdinalIgnoreCase);
if (ids.Length > 0)
{
string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(),
"MCS.Library.SOA.DataObjects.Security.Adapters.Templates.LoadAllParentInfo.sql");
string timeString = TSqlBuilder.Instance.DBCurrentTimeFunction;
if (timePoint != DateTime.MinValue)
timeString = TSqlBuilder.Instance.FormatDateTime(timePoint);
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("ID");
inBuilder.AppendItem(ids);
string sql = string.Format(sqlTemplate, timeString, inBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
DataView view = new DataView(table);
view.Sort = "ID";
foreach (string id in ids)
{
if (result.ContainsKey(id) == false)
{
SCSimpleObjectCollection tempParents = new SCSimpleObjectCollection();
FillAllParents(id, view, includingSelf, tempParents);
SCSimpleObjectCollection parents = new SCSimpleObjectCollection();
//转换次序
for (int i = tempParents.Count - 1; i >= 0; i--)
parents.Add(tempParents[i]);
result.Add(id, parents);
}
}
}
return result;
}
public void DeletePersistQueue(WfProcessCurrentInfoCollection processesInfo)
{
processesInfo.NullCheck("processesInfo");
InSqlClauseBuilder builder = new InSqlClauseBuilder("PROCESS_ID");
processesInfo.ForEach(pi => builder.AppendItem(pi.InstanceID));
if (builder.IsEmpty == false)
{
ORMappingItemCollection mappingInfo = ORMapping.GetMappingInfo(typeof(WfPersistQueue));
string sql = string.Format("DELETE {0} WHERE {0}",
mappingInfo.TableName, builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
}
}
public UserTaskCollection GetUserAccomplishedTasks(UserTaskIDType idType, UserTaskFieldDefine returnFields, bool nolock, params string[] ids)
{
ExceptionHelper.FalseThrow <ArgumentNullException>(ids != null, "ids");
UserTaskCollection utc = new UserTaskCollection();
if (ids.Length > 0)
{
ORMappingItem keyItem = GetMappingItemFromIDType(idType);
InSqlClauseBuilder iBuilder = new InSqlClauseBuilder();
iBuilder.AppendItem(ids);
string sql = string.Format("SELECT {0} FROM WF.USER_ACCOMPLISHED_TASK{1} WHERE {2} {3}",
GetUserTaskSelectFields(returnFields),
nolock ? "(NOLOCK)" : string.Empty,
GetMappingItemFromIDType(idType).DataFieldName,
iBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
Database db = DatabaseFactory.Create(dbi);
using (IDataReader dr = db.ExecuteReader(CommandType.Text, sql))
{
while (dr.Read())
{
UserTask ut = new UserTask();
ORMapping.DataReaderToObject(dr, ut);
utc.Add(ut);
}
}
}
}
return(utc);
}
/// <summary>
/// 读取需要停止的频道
/// </summary>
/// <param name="leadTime">多长时间之内没有节目</param>
/// <returns></returns>
public AMSChannelCollection LoadNeedStopChannels(TimeSpan leadTime)
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("OuterC.State");
inBuilder.AppendItem(
AMSChannelState.Running.ToString(),
AMSChannelState.Starting.ToString(),
AMSChannelState.Stopping.ToString());
//得到从现在开始到leadTime时间段内，需要启动的节目
WhereSqlClauseBuilder startTimeBuilder = new WhereSqlClauseBuilder();
startTimeBuilder.AppendItem("E.StartTime", "GETUTCDATE()", ">=", true);
startTimeBuilder.AppendItem("E.StartTime",
string.Format("DATEADD(second, {0}, GETUTCDATE())", (int)leadTime.TotalSeconds),
"<", true);
//得到还没有结束的节目
WhereSqlClauseBuilder endTimeBuilder = new WhereSqlClauseBuilder();
endTimeBuilder.AppendItem("E.EndTime", "GETUTCDATE()", ">", true);
endTimeBuilder.AppendItem("E.EndTime",
string.Format("DATEADD(second, {0}, GETUTCDATE())", (int)leadTime.TotalSeconds),
"<=", true);
ConnectiveSqlClauseCollection connective = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or,
startTimeBuilder, endTimeBuilder);
//子查询，表示在未来一段时间内需要播放的节目
string subSql = string.Format("SELECT C.ID FROM AMS.Channels C INNER JOIN AMS.EventsChannels EC ON C.ID = EC.ChannelID INNER JOIN AMS.Events E ON EC.EventID = E.ID WHERE {0}",
connective.ToSqlString(TSqlBuilder.Instance));
string sql = string.Format("SELECT * FROM AMS.Channels OuterC WHERE {0} AND OuterC.ID NOT IN ({1})",
inBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance),
subSql);
return(this.QueryData(sql));
}
public UserReportInfoCollection LoadUsersReportInfo(IEnumerable <IUser> users)
{
ExceptionHelper.FalseThrow(users != null, "用户集合不能为null");
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(ConvertUsersToUserIDs(users));
UserReportInfoCollection result = new UserReportInfoCollection();
if (builder.Count > 0)
{
string sql = string.Format("SELECT * FROM USERS_REPORT_LINE WHERE USER_ID {0}",
builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = null;
DbHelper.RunSql(db => table = db.ExecuteDataSet(CommandType.Text, sql).Tables[0], ConnectionDefine.UserRelativeInfoConnectionName);
result.LoadFromDataView(table.DefaultView);
}
return(result);
}
public UserInfoExtendCollection GetUserInfoExtendInfoCollectionByUsers(params string[] userIDS)
{
ExceptionHelper.FalseThrow(userIDS != null, "用户列表不能为null");
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(userIDS);
UserInfoExtendCollection result = new UserInfoExtendCollection();
if (builder.Count > 0)
{
string sql = string.Format("SELECT * FROM USERS_INFO_EXTEND WHERE ID {0}",
builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = null;
DbHelper.RunSql(db => table = db.ExecuteDataSet(CommandType.Text, sql).Tables[0], ConnectionDefine.UserRelativeInfoConnectionName);
result.LoadFromDataView(table.DefaultView);
}
return(result);
}
/// <summary>
/// 根据TaskGuid得到UserAccomplishedTask
/// </summary>
/// <param name="strTaskGuid"></param>
/// <returns></returns>
public UserTaskCollection GetUserAccomplishedTasks(params string[] strTaskGuid)
{
UserTaskCollection utc = new UserTaskCollection();
if (strTaskGuid.Length > 0)
{
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(strTaskGuid);
string strSql = "SELECT * FROM WF.USER_ACCOMPLISHED_TASK WHERE (TASK_GUID " + builder.ToSqlStringWithInOperator(TSqlBuilder.Instance) + ")";
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
Database db = DatabaseFactory.Create(dbi);
using (IDataReader dr = db.ExecuteReader(CommandType.Text, strSql))
{
while (dr.Read())
{
UserTask ut = new UserTask();
ORMapping.DataReaderToObject(dr, ut);
utc.Add(ut);
}
}
}
}
return(utc);
}
private static void eventContainer_DeleteUserAccomplishedTasks(UserTaskCollection tasks, Dictionary<object, object> context)
{
ExceptionHelper.FalseThrow<ArgumentNullException>(tasks != null, "tasks");
InSqlClauseBuilder builder = new InSqlClauseBuilder();
tasks.ForEach(u => builder.AppendItem(u.TaskID));
if (builder.Count > 0)
{
string sql = string.Format("DELETE WF.USER_ACCOMPLISHED_TASK WHERE TASK_GUID {0}", builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
}
}
private static DataSet GetRolesByIDs(string[] roleIDs)
{
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(roleIDs);
string sql = string.Format("SELECT R.ID, R.APP_ID, R.NAME, R.CODE_NAME, R.DESCRIPTION, R.CLASSIFY, R.SORT_ID, R.ALLOW_DELEGATE, R.INHERITED FROM ROLES R WHERE {0} ORDER BY R.CLASSIFY DESC, R.SORT_ID",
builder.Count > 0 ? "ID " + builder.ToSqlStringWithInOperator(TSqlBuilder.Instance) : "1 = 0");
return OGUCommonDefine.ExecuteDataset(sql);
}
private void ExecuteQuery()
{
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
string appName = string.IsNullOrEmpty(txtApplicationName.Text) == true ? "全部" : txtApplicationName.Text;
if (appName.IsNotEmpty())
{
if ("全部" == appName)
{
if (!RolesDefineConfig.GetConfig().IsCurrentUserInRoles("ProcessAdmin"))
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder();
inBuilder.AppendItem(AllApplicationNames());
builder.AppendItem("APPLICATION_NAME", inBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance), "", true);
}
}
else
{
if ("其它" == appName)
{
builder.AppendItem("APPLICATION_NAME", " ");
}
else
{
builder.AppendItem("APPLICATION_NAME", appName);
}
}
}
if (!string.IsNullOrEmpty(txtProgramName.Text))
builder.AppendItem("PROGRAM_NAME", "%" + TSqlBuilder.Instance.CheckQuotationMark(txtProgramName.Text, false) + "%", "like");
if (!string.IsNullOrEmpty(txtProcessKey.Text))
builder.AppendItem("PROCESS_KEY", "%" + TSqlBuilder.Instance.CheckQuotationMark(txtProcessKey.Text, false) + "%", "like");
if (!string.IsNullOrEmpty(txtProcessName.Text))
builder.AppendItem("PROCESS_NAME", "%" + TSqlBuilder.Instance.CheckQuotationMark(txtProcessName.Text, false) + "%", "like");
if (!string.IsNullOrEmpty(ddlEnabled.SelectedValue))
builder.AppendItem("ENABLED", TSqlBuilder.Instance.CheckQuotationMark(ddlEnabled.SelectedValue, false));
builder.AppendTenantCode();
this.BindGrid(builder);
}
public IEnumerable <AMSQueueItem> GetMessages(string category, int count = 1)
{
using (TransactionScope scope = TransactionScopeFactory.Create())
{
AMSQueueItemCollection result = QueryData(GetQuerySql(category, count));
InSqlClauseBuilder builder = new InSqlClauseBuilder("ID");
result.ForEach(item => builder.AppendItem(item.ID));
if (builder.IsEmpty == false)
{
string sql = string.Format("DELETE {0} WHERE {1}", GetTableName(), builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
}
scope.Complete();
return(result);
}
}
private static void eventContainer_DeleteUserTasks(UserTaskCollection tasks, Dictionary <object, object> context)
{
ExceptionHelper.FalseThrow <ArgumentNullException>(tasks != null, "tasks");
InSqlClauseBuilder builder = new InSqlClauseBuilder();
tasks.ForEach(u => builder.AppendItem(u.TaskID));
if (builder.Count > 0)
{
string sql = string.Format("DELETE WF.USER_TASK WHERE TASK_GUID {0}", builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
}
}
public void DeletePersistQueue(WfProcessCurrentInfoCollection processesInfo)
{
processesInfo.NullCheck("processesInfo");
InSqlClauseBuilder builder = new InSqlClauseBuilder("PROCESS_ID");
processesInfo.ForEach(pi => builder.AppendItem(pi.InstanceID));
if (builder.IsEmpty == false)
{
ORMappingItemCollection mappingInfo = ORMapping.GetMappingInfo(typeof(WfPersistQueue));
string sql = string.Format("DELETE {0} WHERE {0}",
mappingInfo.TableName, builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
}
}
/// <summary>
/// 获取AD组内的人员在安全中心中的人员信息
/// </summary>
/// <param name="groupEntry"></param>
/// <returns></returns>
private static IEnumerable<DataRow> CollectGroupUserRows(DirectoryEntry groupEntry)
{
InSqlClauseBuilder builder = new InSqlClauseBuilder();
ADHelper helper = ADHelper.GetInstance();
helper.EnumGroupMembers(groupEntry, sr =>
{
builder.AppendItem(helper.GetSearchResultPropertyStrValue("samAccountName", sr));
});
List<DataRow> result = new List<DataRow>(256);
if (builder.Count > 0)
{
string sql = string.Format("SELECT OU_USERS.* FROM OU_USERS INNER JOIN USERS ON USER_GUID = GUID WHERE SIDELINE = 0 AND LOGON_NAME {0} ORDER BY GLOBAL_SORT",
builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = null;
DbHelper.ExecSql(db => table = db.ExecuteDataSet(CommandType.Text, sql).Tables[0]);
foreach (DataRow row in table.Rows)
result.Add(row);
}
return result;
}
private static void FillAssignees(WfProcessCurrentInfoCollection result)
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder();
result.ForEach(currentInfo => inBuilder.AppendItem(currentInfo.CurrentActivityID));
if (inBuilder.Count > 0)
{
string sql = string.Format("SELECT * FROM WF.PROCESS_CURRENT_ASSIGNEES WHERE ACTIVITY_ID {0} ORDER BY ID",
inBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0];
DataView assigneeView = new DataView(table);
assigneeView.Sort = "ACTIVITY_ID";
foreach (var currentInfo in result)
{
DataRowView[] rows = assigneeView.FindRows(currentInfo.CurrentActivityID);
Array.ForEach(rows, drv =>
{
currentInfo.Assignees.Add(DataRowToAssignee(drv.Row));
});
}
}
}
/// <summary>
/// 删除流程运行时活动节点的Assignees
/// </summary>
/// <param name="activityID"></param>
public void DeleteProcessCurrentAssignees(WfProcessCurrentInfoCollection processesInfo)
{
InSqlClauseBuilder builder = new InSqlClauseBuilder("ACTIVITY_ID");
processesInfo.ForEach(p => builder.AppendItem(p.CurrentActivityID));
if (builder.Count > 0)
{
string sql = string.Format("DELETE WF.PROCESS_CURRENT_ASSIGNEES WHERE {0}",
builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
}
}
public void DeletePostUsers(WfPost post, params string[] userIDs)
{
post.NullCheck("post");
userIDs.NullCheck("userIDs");
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(userIDs);
if (builder.Count > 0)
{
string sql = string.Format("DELETE WF.POST_USERS WHERE POST_ID = {0} AND USER_ID {1}",
TSqlBuilder.Instance.CheckQuotationMark(post.PostID, true), builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
}
}
public UserTaskCollection GetUserAccomplishedTasks(UserTaskIDType idType, UserTaskFieldDefine returnFields, bool nolock, params string[] ids)
{
ExceptionHelper.FalseThrow<ArgumentNullException>(ids != null, "ids");
UserTaskCollection utc = new UserTaskCollection();
if (ids.Length > 0)
{
ORMappingItem keyItem = GetMappingItemFromIDType(idType);
InSqlClauseBuilder iBuilder = new InSqlClauseBuilder();
iBuilder.AppendItem(ids);
string sql = string.Format("SELECT {0} FROM WF.USER_ACCOMPLISHED_TASK{1} WHERE {2} {3}",
GetUserTaskSelectFields(returnFields),
nolock ? "(NOLOCK)" : string.Empty,
GetMappingItemFromIDType(idType).DataFieldName,
iBuilder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
Database db = DatabaseFactory.Create(dbi);
using (IDataReader dr = db.ExecuteReader(CommandType.Text, sql))
{
while (dr.Read())
{
UserTask ut = new UserTask();
ORMapping.DataReaderToObject(dr, ut);
utc.Add(ut);
}
}
}
}
return utc;
}
/// <summary>
/// 调用本方法，批量删除组内用户
/// </summary>
/// <param name="group"></param>
/// <param name="userIDs"></param>
public void DeleteGroupUsers(WfGroup group, params string[] userIDs)
{
group.NullCheck("group");
userIDs.NullCheck("userIDs");
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(userIDs);
if (builder.Count > 0)
{
string sql = string.Format("DELETE WF.GROUP_USERS WHERE GROUP_ID = {0} AND USER_ID {1}",
TSqlBuilder.Instance.CheckQuotationMark(group.GroupID, true), builder.ToSqlStringWithInOperator(TSqlBuilder.Instance));
DbHelper.RunSql(sql, GetConnectionName());
}
}
/// <summary>
/// 根据TaskGuid得到UserAccomplishedTask
/// </summary>
/// <param name="strTaskGuid"></param>
/// <returns></returns>
public UserTaskCollection GetUserAccomplishedTasks(params string[] strTaskGuid)
{
UserTaskCollection utc = new UserTaskCollection();
if (strTaskGuid.Length > 0)
{
InSqlClauseBuilder builder = new InSqlClauseBuilder();
builder.AppendItem(strTaskGuid);
string strSql = "SELECT * FROM WF.USER_ACCOMPLISHED_TASK WHERE (TASK_GUID " + builder.ToSqlStringWithInOperator(TSqlBuilder.Instance) + ")";
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
Database db = DatabaseFactory.Create(dbi);
using (IDataReader dr = db.ExecuteReader(CommandType.Text, strSql))
{
while (dr.Read())
{
UserTask ut = new UserTask();
ORMapping.DataReaderToObject(dr, ut);
utc.Add(ut);
}
}
}
}
return utc;
}
