protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.SchemaUserSnapshot_Current O INNER JOIN SC.UserAndContainerSnapshot_Current R ON O.ID = R.UserID" : "SC.SchemaUserSnapshot O INNER JOIN SC.UserAndContainerSnapshot R ON O.ID = R.UserID";
qc.SelectFields = "O.*";
if (string.IsNullOrEmpty(this.orderBy))
qc.OrderByClause = "R.VersionStartTime DESC";
else
qc.OrderByClause = orderBy;
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
var timeCondition1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
builder.AppendItem("R.ContainerID", this.containerId);
InnerBuildWhere(builder);
builder.AppendItem("O.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
var allConditions = new ConnectiveSqlClauseCollection(timeCondition1, timeCondition2, builder);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
private void BindDataGrid()
{
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(this.UserUploadCondition);
if (this.processUsers.ReadOnly)
{
this.ObjectDataSourceUploadLogs.Condition = builder;
}
else
{
ConnectiveSqlClauseCollection collection = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And);
collection.Add(builder);
WhereSqlClauseBuilder userwherebuilder = new WhereSqlClauseBuilder();
userwherebuilder.LogicOperator = LogicOperatorDefine.Or;
foreach (var item in this.processUsers.SelectedOuUserData)
{
userwherebuilder.AppendItem("OPERATOR_ID", item.ID);
}
collection.Add(userwherebuilder);
this.ObjectDataSourceUploadLogs.Condition = collection.ToSqlString(TSqlBuilder.Instance);
}
this.ObjectDataSourceUploadLogs.LastQueryRowCount = -1;
this.DeluxeGridUploadLog.PageIndex = 0;
this.DeluxeGridUploadLog.SelectedKeys.Clear();
this.DeluxeGridUploadLog.DataBind();
}
private void BindDataGrid()
{
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(this.UserUploadCondition);
if (this.processUsers.ReadOnly)
{
this.ObjectDataSourceUploadLogs.Condition = builder;
}
else
{
ConnectiveSqlClauseCollection collection = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And);
collection.Add(builder);
WhereSqlClauseBuilder userwherebuilder = new WhereSqlClauseBuilder();
userwherebuilder.LogicOperator = LogicOperatorDefine.Or;
foreach (var item in this.processUsers.SelectedOuUserData)
{
userwherebuilder.AppendItem("OPERATOR_ID", item.ID);
}
collection.Add(userwherebuilder);
this.ObjectDataSourceUploadLogs.Condition = collection.ToSqlString(TSqlBuilder.Instance);
}
this.ObjectDataSourceUploadLogs.LastQueryRowCount = -1;
this.DeluxeGridUploadLog.PageIndex = 0;
this.DeluxeGridUploadLog.SelectedKeys.Clear();
this.DeluxeGridUploadLog.DataBind();
}
public SchemaObjectCollection QueryApplications(string[] schemaTypes, bool includingDeleted, DateTime timePoint)
{
SchemaObjectCollection result = new SchemaObjectCollection();
ConnectiveSqlClauseCollection timeConditionC = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "SC.");
InSqlClauseBuilder schemaBuilder = new InSqlClauseBuilder("SC.SchemaType");
schemaBuilder.AppendItem(schemaTypes);
WhereSqlClauseBuilder builderC = new WhereSqlClauseBuilder();
if (includingDeleted == false)
{
builderC.AppendItem("SC.Status", (int)SchemaObjectStatus.Normal);
}
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(schemaBuilder, builderC, timeConditionC);
string sql = string.Format("SELECT SC.*" +
"\nFROM SC.SchemaObject SC" +
"\nWHERE {0}", connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
return(result);
}
/// <summary>
/// 根据Container来加载UserAndContainerSnapshot的信息
/// </summary>
/// <param name="containerID"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public int CountAliveUsersByContainer(string containerID, DateTime timePoint)
{
containerID.CheckStringIsNullOrEmpty("containerID");
ConnectiveSqlClauseCollection timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem("ContainerID", containerID);
whereBuilder.AppendItem("Status", (int)SchemaObjectStatus.Normal);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(whereBuilder, timePointBuilder);
string sql = string.Format("SELECT COUNT(ContainerID) FROM {0} WHERE {1}",
this.GetLoadingTableName(timePoint), connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
int result = 0;
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
result = (int)DbHelper.RunSqlReturnScalar(sql, this.GetConnectionName());
}
return(result);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
base.OnBuildQueryCondition(qc);
qc.SelectFields = "S.*";
qc.FromClause = this.FromSqlClause;
if (string.IsNullOrEmpty(qc.OrderByClause))
{
qc.OrderByClause = "S.Name ASC";
}
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("S.");
var timeConditionM = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("M.");
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
var allConditions = new ConnectiveSqlClauseCollection(timeCondition, timeConditionM, where);
where.AppendCondition("M.ContainerID", this.schemaID);
if (normalOnly)
{
where.AppendCondition("S.Status", (int)SchemaObjectStatus.Normal);
where.AppendCondition("M.Status", (int)SchemaObjectStatus.Normal);
}
if (string.IsNullOrEmpty(qc.WhereClause) == false)
{
qc.WhereClause += " AND ";
}
qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance);
}
/// <summary>
/// 获取一个或多个终端节点的父机构
/// </summary>
/// <param name="timePoint">时间点</param>
/// <param name="terminalIds">终结点ID的数组</param>
/// <returns>表示对象ID，和容器ID的关系。</returns>
public IEnumerable <System.Collections.Generic.KeyValuePair <string, string> > GetOwnerships(DateTime timePoint, string[] terminalIds)
{
string sql = @"SELECT R.ObjectID,R.ParentID FROM
SC.SchemaRelationObjectsSnapshot R
WHERE ";
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("R.ObjectID");
inBuilder.AppendItem(terminalIds);
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "R.");
whereBuilder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
whereBuilder.AppendItem("R.ParentSchemaType", "Organizations");
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(inBuilder, whereBuilder, timeCondition);
sql += connectiveBuilder.ToSqlString(TSqlBuilder.Instance) + " ORDER BY R.ObjectID ASC";
var ds = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName());
foreach (System.Data.DataRow row in ds.Tables[0].Rows)
{
yield return(new System.Collections.Generic.KeyValuePair <string, string>(row["ObjectID"] as string, row["ParentID"] as string));
}
}
/// <summary>
///
/// </summary>
/// <param name="whereAction"></param>
/// <param name="timePoint">时间点</param>
/// <param name="mappings"></param>
/// <returns></returns>
public bool Exists(Action <WhereSqlClauseBuilder> whereAction, DateTime timePoint, ORMappingItemCollection mappings = null)
{
whereAction.NullCheck("whereAction");
if (mappings == null)
{
mappings = this.GetQueryMappingInfo();
}
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
whereAction(builder);
builder.AppendTenantCode(typeof(T));
ConnectiveSqlClauseCollection timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
timePointBuilder.Add(builder);
string sql = string.Format("SELECT TOP 1 * FROM {0}", GetQueryTableName(mappings, timePoint));
if (builder.Count > 0)
{
sql = sql + string.Format(" WHERE {0}", timePointBuilder.ToSqlString(TSqlBuilder.Instance));
}
return((int)DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0].Rows.Count > 0);
}
/// <summary>
/// 加载指定的成员
/// </summary>
/// <param name="containerSchemaType"></param>
/// <param name="containerID"></param>
/// <param name="status"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SchemaObjectCollection LoadMembers(string containerSchemaType, string containerID, SchemaObjectStatus status, DateTime timePoint)
{
containerSchemaType.CheckStringIsNullOrEmpty("schemaType");
containerID.CheckStringIsNullOrEmpty("containerID");
WhereSqlClauseBuilder condition = new WhereSqlClauseBuilder();
condition.AppendItem("O.Status", (int)status);
condition.AppendItem("M.ContainerSchemaType", containerSchemaType).AppendItem("M.Status", (int)status);
condition.AppendItem("M.ContainerID", containerID);
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "M.");
ConnectiveSqlClauseCollection conditions = new ConnectiveSqlClauseCollection(condition, timeCondition, timeCondition2);
string sql = string.Format("SELECT O.* FROM SC.SchemaObject O INNER JOIN SC.SchemaMembers M ON O.ID = M.MemberID WHERE {0}",
conditions.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
SchemaObjectCollection result = new SchemaObjectCollection();
result.LoadFromDataView(table.DefaultView);
return(result);
}
public SCAclMemberCollection LoadMembers(IConnectiveSqlClause condition, DateTime timePoint)
{
ConnectiveSqlClauseCollection timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(condition, timePointBuilder);
string sql = string.Format(
"SELECT * FROM {0} WHERE {1} ORDER BY SortID",
this.GetLoadingTableName(timePoint),
connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
SCAclMemberCollection result = new SCAclMemberCollection();
AUCommon.DoDbAction(() =>
{
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName()))
{
ORMapping.DataReaderToCollection(result, reader);
}
}
});
return(result);
}
private string BuildWhereCondition()
{
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(this.Condition,
new AdjustConditionValueDelegate(AdjustQueryConditionValue));
builder.AppendItem("SEND_TO_USER", DeluxeIdentity.CurrentUser.ID);
WhereSqlClauseBuilder whereExpireTime = new WhereSqlClauseBuilder(LogicOperatorDefine.Or);
whereExpireTime.AppendItem("EXPIRE_TIME", "null", "is", true);
whereExpireTime.AppendItem("EXPIRE_TIME", "getdate()", ">", true);
WhereSqlClauseBuilder whereStatus = new WhereSqlClauseBuilder();
whereStatus.AppendItem("STATUS", ((int)TaskStatus.Yue).ToString());
var conditionGroup1 = new ConnectiveSqlClauseCollection(whereExpireTime, whereStatus);
WhereSqlClauseBuilder whereStatus2 = new WhereSqlClauseBuilder();
whereStatus2.AppendItem("STATUS", ((int)TaskStatus.Ban).ToString());
//collection2.Add(collection1);
ConnectiveSqlClauseCollection collection2 = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or, conditionGroup1, whereStatus2);
var allConditions = new ConnectiveSqlClauseCollection(builder, collection2);
return(allConditions.ToSqlString(TSqlBuilder.Instance));
}
public SchemaObjectCollection QueryApplications(string[] schemaTypes, bool includingDeleted, DateTime timePoint)
{
SchemaObjectCollection result = new SchemaObjectCollection();
ConnectiveSqlClauseCollection timeConditionC = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "SC.");
InSqlClauseBuilder schemaBuilder = new InSqlClauseBuilder("SC.SchemaType");
schemaBuilder.AppendItem(schemaTypes);
WhereSqlClauseBuilder builderC = new WhereSqlClauseBuilder();
if (includingDeleted == false)
builderC.AppendItem("SC.Status", (int)SchemaObjectStatus.Normal);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(schemaBuilder, builderC, timeConditionC);
string sql = string.Format("SELECT SC.*" +
"\nFROM SC.SchemaObject SC" +
"\nWHERE {0}", connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
return result;
}
public bool ValidateGroups(DateTime timePoint)
{
InSqlClauseBuilder inSql1 = new InSqlClauseBuilder("ChildSchemaType");
inSql1.AppendItem(SchemaInfo.FilterByCategory("Groups").ToSchemaNames());
InSqlClauseBuilder inSql2 = new InSqlClauseBuilder("ParentSchemaType");
inSql2.AppendItem(SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
var all = new ConnectiveSqlClauseCollection(inSql1, inSql2, VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint));
string sql = string.Format(@"SELECT TOP 1 1 from SC.SchemaRelationObjects WHERE ObjectID IN(
SELECT ObjectID FROM SC.SchemaRelationObjects WHERE {0} AND STATUS =1
GROUP by ObjectID
HAVING COUNT(ParentID) <>1
)", all.ToSqlString(TSqlBuilder.Instance));
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
using (var dr = db.ExecuteReader(CommandType.Text, sql))
{
if (dr.Read())
{
return false;
}
else
{
return true;
}
}
}
}
public bool ValidateGroups(DateTime timePoint)
{
InSqlClauseBuilder inSql1 = new InSqlClauseBuilder("ChildSchemaType");
inSql1.AppendItem(SchemaInfo.FilterByCategory("Groups").ToSchemaNames());
InSqlClauseBuilder inSql2 = new InSqlClauseBuilder("ParentSchemaType");
inSql2.AppendItem(SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
var all = new ConnectiveSqlClauseCollection(inSql1, inSql2, VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint));
string sql = string.Format(@"SELECT TOP 1 1 from SC.SchemaRelationObjects WHERE ObjectID IN(
SELECT ObjectID FROM SC.SchemaRelationObjects WHERE {0} AND STATUS =1
GROUP by ObjectID
HAVING COUNT(ParentID) <>1
)", all.ToSqlString(TSqlBuilder.Instance));
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
Database db = DatabaseFactory.Create(context);
using (var dr = db.ExecuteReader(CommandType.Text, sql))
{
if (dr.Read())
{
return(false);
}
else
{
return(true);
}
}
}
}
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(typeof(JobSchedule)));
string sql = string.Format(QUERY_SCHEDULE_DEF_BY_JOB_ID_SQL_CLAUSE,
builder.ToSqlString(TSqlBuilder.Instance));
result = QueryData <JobScheduleWithJobID, JobScheduleWithJobIDCollection>(
ORMapping.GetMappingInfo <JobScheduleWithJobID>(), sql);
}
else
{
result = new JobScheduleWithJobIDCollection();
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="inCondition"></param>
/// <param name="action"></param>
/// <param name="timePoint"></param>
/// <param name="tableName"></param>
/// <param name="mappings"></param>
public void LoadByInBuilderInContext(InLoadingCondition inCondition, Action <TCollection> action, DateTime timePoint, string tableName = null, ORMappingItemCollection mappings = null)
{
inCondition.NullCheck("inCondition");
inCondition.BuilderAction.NullCheck("BuilderAction");
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder(inCondition.DataField);
inCondition.BuilderAction(inBuilder);
string condition = string.Empty;
if (inBuilder.IsEmpty == false)
{
ConnectiveSqlClauseCollection builder = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And,
inBuilder, new WhereSqlClauseBuilder().AppendTenantCode(typeof(T)));
condition = builder.ToSqlString(TSqlBuilder.Instance);
OrderBySqlClauseBuilder orderByBuilder = null;
if (inCondition.OrderByAction != null)
{
orderByBuilder = new OrderBySqlClauseBuilder();
inCondition.OrderByAction(orderByBuilder);
}
this.RegisterLoadByBuilderInContext(condition, orderByBuilder, action, timePoint, tableName, mappings);
}
}
/// <summary>
/// 按照Builder查询应用所包含的元素（角色和权限）
/// </summary>
/// <param name="builder"></param>
/// <param name="includingDeleted"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SchemaObjectCollection QueryApplicationObjectsByBuilder(IConnectiveSqlClause builder, bool includingDeleted, DateTime timePoint)
{
builder.NullCheck("builder");
SchemaObjectCollection result = new SchemaObjectCollection();
if (builder.IsEmpty == false)
{
IConnectiveSqlClause extraBuilder = CreateStatusAndTimePointBuilder(includingDeleted, timePoint,
"SC.", "A.", "M.");
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(builder, extraBuilder);
string resourcePath = "QueryApplicationObjects_Current.sql";
if (timePoint != DateTime.MinValue && includingDeleted == true)
{
resourcePath = "QueryApplicationObjects.sql";
}
string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(), string.Concat("MCS.Library.SOA.DataObjects.Security.Adapters.Templates.", resourcePath));
string sql = string.Format(sqlTemplate, connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
}
return(result);
}
public int Delete(string[] ids)
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("JOB_ID");
inBuilder.AppendItem(ids);
int result = 0;
if (inBuilder.Count > 0)
{
ConnectiveSqlClauseCollection builder = new ConnectiveSqlClauseCollection(inBuilder,
new WhereSqlClauseBuilder().AppendTenantCode(typeof(InvokeWebServiceJob)));
string where = builder.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());
}
return(result);
}
public WfClientProcessCurrentInfoPageQueryResult QueryProcesses(WfClientProcessQueryCondition condition, int startRowIndex, int maximumRows, string orderBy, int totalCount)
{
condition.NullCheck("condition");
OperationContext.Current.FillContextToOguServiceContext();
WfProcessQueryCondition serverCondition = null;
WfClientProcessQueryConditionConverter.Instance.ClientToServer(condition, ref serverCondition);
if (orderBy.IsNullOrEmpty())
{
orderBy = "START_TIME DESC";
}
ConnectiveSqlClauseCollection connective = serverCondition.ToSqlBuilder();
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("COMMITTED", "1");
builder.AppendTenantCode();
connective.Add(builder);
QueryCondition qc = new QueryCondition(startRowIndex, maximumRows,
ORMapping.GetSelectFieldsNameSql <WfProcessCurrentInfo>(),
ORMapping.GetMappingInfo(typeof(WfProcessCurrentInfo)).TableName,
orderBy);
qc.WhereClause += connective.ToSqlString(TSqlBuilder.Instance);
return(QueryProcessInfo(qc, totalCount));
}
/// <summary>
/// 将缺省组织ID添加到查询结果
/// </summary>
/// <param name="result"></param>
/// <param name="userIdField">源表中用户ID的列名</param>
/// <param name="parentIdField">用于添加到源表中缺省组织的ID的列名</param>
/// <param name="connectionName">连接名</param>
public static void FillUserDefaultParent(DataView result, string userIdField, string parentIdField, string connectionName)
{
if (result.Table.Columns.Contains(parentIdField) == false)
{
result.Table.Columns.Add(parentIdField, typeof(string));
}
var rows = result.Table.Rows;
string[] ids = new string[rows.Count];
for (int i = 0; i < rows.Count; i++)
{
ids[i] = (string)rows[i][userIdField];
}
if (ids.Length > 0)
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("K.ObjectID");
inBuilder.AppendItem(ids);
var defaultParentTimeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("K.");
var defaultParentWhere = new WhereSqlClauseBuilder();
defaultParentWhere.AppendItem("K.Status", (int)SchemaObjectStatus.Normal);
defaultParentWhere.AppendItem("K.IsDefault", 1);
var defaultParentAllConditions = new ConnectiveSqlClauseCollection(inBuilder, defaultParentWhere, defaultParentTimeCondition);
string parentIDSql = TimePointContext.Current.UseCurrentTime ? "SELECT K.ObjectID, K.ParentID FROM SC.SchemaRelationObjectsSnapshot_Current K WHERE " + defaultParentAllConditions.ToSqlString(TSqlBuilder.Instance) : "SELECT K.ObjectID, K.ParentID FROM SC.SchemaRelationObjectsSnapshot K WHERE " + defaultParentAllConditions.ToSqlString(TSqlBuilder.Instance);
DataSet ds = DbHelper.RunSqlReturnDS(parentIDSql, connectionName);
var rows2 = ds.Tables[0].Rows;
rows.ForEach<DataRow>(r =>
{
rows2.ForEach<DataRow>(r2 =>
{
if (r2["ObjectID"].Equals(r[userIdField]))
{
r[parentIdField] = r2["ParentID"];
}
});
});
}
}
public PagedQueryResult <UserTaskModel, UserTaskModelCollection> LoadUserTasks(IPageRequestParams prp, UserTaskQueryCriteria condition, IEnumerable <IOrderByRequestItem> orderByBuilder)
{
ConnectiveSqlClauseCollection sqlCollection = new ConnectiveSqlClauseCollection();
sqlCollection.Add(ConditionMapping.GetConnectiveClauseBuilder(condition));
string sqlBuilder = sqlCollection.ToSqlString(TSqlBuilder.Instance);
var result = Query(prp, sqlBuilder, orderByBuilder);
return(result);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = FromClause;
qc.SelectFields = SelectFields;
if (this.orderBy.IsNullOrEmpty())
{
qc.OrderByClause = this.DefaultOrderBy;
}
else
{
qc.OrderByClause = orderBy;
}
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
builder.AppendItem(ParentIdField, parentId);
builder.AppendItem <int>("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem <int>("O.Status", (int)SchemaObjectStatus.Normal);
InnerBuildWhere(builder);
var conditionMemberCondition = DataSourceUtil.SchemaTypeCondition("R.MemberSchemaType", this.MemberSchemaTypes);
var conditionContainerCondition = DataSourceUtil.SchemaTypeCondition("R.ContainerSchemaType", this.ContainerSchemaTypes);
var allCondition = new ConnectiveSqlClauseCollection(builder, conditionMemberCondition, conditionMemberCondition, timeCondition, timeCondition2);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = allCondition.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = allCondition.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
/// <summary>
/// 判断同一频道下是否有时间交叉的事件
/// </summary>
/// <param name="eventData"></param>
/// <param name="additionalChannelID">额外的频道</param>
/// <returns></returns>
public bool HaveIntersectEvents(AMSEvent eventData, string additionalChannelID = "")
{
eventData.NullCheck("eventData");
DateTime startTime = TimeZoneContext.Current.ConvertTimeToUtc(eventData.StartTime);
DateTime endTime = TimeZoneContext.Current.ConvertTimeToUtc(eventData.EndTime);
WhereSqlClauseBuilder builder1 = new WhereSqlClauseBuilder();
builder1.AppendItem("StartTime", startTime, ">=");
builder1.AppendItem("StartTime", endTime, "<");
WhereSqlClauseBuilder builder2 = new WhereSqlClauseBuilder();
builder2.AppendItem("EndTime", startTime, ">");
builder2.AppendItem("EndTime", endTime, "<");
WhereSqlClauseBuilder builder3 = new WhereSqlClauseBuilder();
builder3.AppendItem("StartTime", startTime, "<");
builder3.AppendItem("EndTime", startTime, ">");
WhereSqlClauseBuilder builder4 = new WhereSqlClauseBuilder();
builder4.AppendItem("StartTime", endTime, "<");
builder4.AppendItem("EndTime", endTime, ">");
ConnectiveSqlClauseCollection connectiveTime = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or,
builder1, builder2, builder3, builder4);
WhereSqlClauseBuilder idBuilder = new WhereSqlClauseBuilder();
idBuilder.AppendItem("ID", eventData.ID, "<>");
AMSChannelCollection channels = this.LoadRelativeChannels(eventData.ID);
InSqlClauseBuilder channelIDBuilder = new InSqlClauseBuilder("EC.ChannelID");
channels.ForEach(c => channelIDBuilder.AppendItem(c.ID));
channelIDBuilder.AppendItem(eventData.ChannelID);
if (additionalChannelID.IsNotEmpty())
{
channelIDBuilder.AppendItem(additionalChannelID);
}
ConnectiveSqlClauseCollection connective = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And, idBuilder, channelIDBuilder, connectiveTime);
string sql = string.Format("SELECT TOP 1 * FROM {0} E INNER JOIN AMS.EventsChannels EC ON E.ID = EC.EventID WHERE {1}",
this.GetTableName(), connective.ToSqlString(TSqlBuilder.Instance));
return(this.QueryData(sql).FirstOrDefault() != null);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = FromClause;
qc.SelectFields = SelectFields;
if (string.IsNullOrEmpty(this.orderBy))
{
qc.OrderByClause = this.DefaultOrderBy;
}
else
{
qc.OrderByClause = orderBy;
}
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
builder.AppendItem(UserIdField, userId);
InnerBuildWhere(builder);
var conditionUserSchemas = DataSourceUtil.SchemaTypeCondition("R.UserSchemaType", this.UserSchemaTypes);
var conditionContainerSchemas = DataSourceUtil.SchemaTypeCondition("R.ContainerSchemaType", this.ContainerSchemaTypes);
var allConditions = new ConnectiveSqlClauseCollection(timeCondition, timeCondition2, conditionUserSchemas, conditionContainerSchemas, builder);
OnConnectConditions(allConditions);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance);
}
else
{
//将builder中的内容与WhereClause合并
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
private SchemaObjectCollection LoadByCodeNameInner(ConnectiveSqlClauseCollection conditions)
{
string sql = "SELECT O.* FROM SC.SchemaObject O INNER JOIN SC.SchemaObjectSnapshot S ON O.ID = S.ID AND S.VersionStartTime = O.VersionStartTime WHERE ";
sql += conditions.ToSqlString(TSqlBuilder.Instance);
var result = new SchemaObjectCollection();
var dt = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(dt.DefaultView);
return(result);
}
public QueryCondition GetQueryConditionByTime(Action <ConnectiveSqlClauseCollection> whereAction, DateTime dateTime)
{
ConnectiveSqlClauseCollection connectiveSqlClauses = new ConnectiveSqlClauseCollection();
ORMappingItemCollection mappings = ORMapping.GetMappingInfo <T>();
connectiveSqlClauses.GetVersionTimeCondion <QueryCondtionEntityBase>(dateTime, mappings.TableName, whereAction);
QueryCondition qc = new QueryCondition
{
FromClause = mappings.TableName,
WhereClause = connectiveSqlClauses.ToSqlString(TSqlBuilder.Instance)
};
return(qc);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = FromClause;
qc.SelectFields = SelectFields;
if (string.IsNullOrEmpty(this.orderBy))
{
qc.OrderByClause = this.DefaultOrderBy;
}
else
{
qc.OrderByClause = orderBy;
}
base.OnBuildQueryCondition(qc);
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
builder.AppendItem(ParentIdField, parentId);
InnerBuildWhere(builder);
var schemaCondition1 = DataSourceUtil.SchemaTypeCondition("R.ChildSchemaType", this.ChildSchemaTypes);
var schemaCondition2 = DataSourceUtil.SchemaTypeCondition("R.ParentSchemaType", this.ParentSchemaTypes);
var allConditions = new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, schemaCondition1, schemaCondition2);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = (TimePointContext.Current.UseCurrentTime ?
"SC.SchemaGroupSnapshot_Current G INNER JOIN SC.SchemaRelationObjectsSnapshot_Current R ON G.ID = R.ObjectID "
: "SC.SchemaGroupSnapshot G INNER JOIN SC.SchemaRelationObjectsSnapshot R ON G.ID = R.ObjectID ");
if (this.fileterRole)
{
qc.FromClause += TimePointContext.Current.UseCurrentTime ?
" LEFT JOIN SC.Acl_Current AC ON AC.ContainerID = R.ParentID INNER JOIN SC.SchemaRoleSnapshot_Current RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot_Current UC ON RS.ID = UC.ContainerID " :
" LEFT JOIN SC.Acl AC ON AC.ContainerID = R.ParentID INNER JOIN SC.SchemaRoleSnapshot RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot UC ON RS.ID = UC.ContainerID ";
}
qc.SelectFields = "G.*,R.ParentID";
if (qc.OrderByClause.IsNullOrEmpty())
{
qc.OrderByClause = "R.InnerSort DESC";
}
qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND ");
var timeConditionS = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("G.");
var timeConditionR = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("G.Status", (int)SchemaObjectStatus.Normal);
var allConditions = new ConnectiveSqlClauseCollection(timeConditionS, timeConditionR, builder);
if (this.fileterRole)
{
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("AC."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("RS."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("UC."));
builder.AppendItem("AC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("RS.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.UserID", this.userID);
InSqlClauseBuilder inSql1 = new InSqlClauseBuilder("AC.ContainerPermission");
inSql1.AppendItem(this.parentPermissions);
allConditions.Add(inSql1);
}
qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.SchemaUserSnapshot_Current O INNER JOIN SC.UserAndContainerSnapshot_Current R ON O.ID = R.UserID" : "SC.SchemaUserSnapshot O INNER JOIN SC.UserAndContainerSnapshot R ON O.ID = R.UserID";
qc.SelectFields = "O.*";
if (string.IsNullOrEmpty(this.orderBy))
{
qc.OrderByClause = "R.VersionStartTime DESC";
}
else
{
qc.OrderByClause = orderBy;
}
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
var timeCondition1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
builder.AppendItem("R.ContainerID", this.containerId);
InnerBuildWhere(builder);
builder.AppendItem("O.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
var allConditions = new ConnectiveSqlClauseCollection(timeCondition1, timeCondition2, builder);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
base.OnBuildQueryCondition(qc);
qc.SelectFields = "S.*";
qc.FromClause = this.FromSqlClause;
if (string.IsNullOrEmpty(qc.OrderByClause))
qc.OrderByClause = "S.Name ASC";
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("S.");
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
var allConditions = new ConnectiveSqlClauseCollection(timeCondition, where);
where.AppendCondition("S.CategoryID", this.categoryID);
if (string.IsNullOrEmpty(qc.WhereClause) == false)
qc.WhereClause += " AND ";
qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance);
}
public JoinBuilder LeftOuterJoin <TLt, TRt>(string lp, string rp, string lals = null, string rals = null, Action <ConnectiveSqlClauseCollection> addCondtion = null)
{
string sql = GetJoinCamal <TLt, TRt>(lp, rp, lals, rals, JoinMode.LeftOutJoin);
var connective = new ConnectiveSqlClauseCollection();
if (addCondtion != null)
{
addCondtion(connective);
}
if (connective.Count > 0)
{
sql = string.Format(" {0} AND {1} ", sql, connective.ToSqlString(TSqlBuilder.Instance));
}
JoinItems.Add(JoinItems.Count + 1, sql);
return(this);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = (TimePointContext.Current.UseCurrentTime ?
"SC.SchemaGroupSnapshot_Current G INNER JOIN SC.SchemaRelationObjectsSnapshot_Current R ON G.ID = R.ObjectID "
: "SC.SchemaGroupSnapshot G INNER JOIN SC.SchemaRelationObjectsSnapshot R ON G.ID = R.ObjectID ");
if (this.fileterRole)
{
qc.FromClause += TimePointContext.Current.UseCurrentTime ?
" LEFT JOIN SC.Acl_Current AC ON AC.ContainerID = R.ParentID INNER JOIN SC.SchemaRoleSnapshot_Current RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot_Current UC ON RS.ID = UC.ContainerID " :
" LEFT JOIN SC.Acl AC ON AC.ContainerID = R.ParentID INNER JOIN SC.SchemaRoleSnapshot RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot UC ON RS.ID = UC.ContainerID ";
}
qc.SelectFields = "G.*,R.ParentID";
if (qc.OrderByClause.IsNullOrEmpty())
qc.OrderByClause = "R.InnerSort DESC";
qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND ");
var timeConditionS = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("G.");
var timeConditionR = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("G.Status", (int)SchemaObjectStatus.Normal);
var allConditions = new ConnectiveSqlClauseCollection(timeConditionS, timeConditionR, builder);
if (this.fileterRole)
{
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("AC."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("RS."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("UC."));
builder.AppendItem("AC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("RS.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.UserID", this.userID);
InSqlClauseBuilder inSql1 = new InSqlClauseBuilder("AC.ContainerPermission");
inSql1.AppendItem(this.parentPermissions);
allConditions.Add(inSql1);
}
qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance);
}
/// <summary>
/// 得到更新当前快照的SQL
/// </summary>
/// <param name="srcTableName"></param>
/// <param name="targetTableName"></param>
/// <param name="builder"></param>
/// <returns></returns>
private string GetInternalUpdateCurrentSnapshotSql(string srcTableName, string targetTableName, IConnectiveSqlClause builder)
{
builder.NullCheck("builder");
StringBuilder strB = new StringBuilder();
if (builder.IsEmpty == false)
{
IConnectiveSqlClause timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(DateTime.MinValue);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(builder, timePointBuilder);
string sql = string.Format("SELECT * FROM {0} WHERE Status = {1} AND {2}",
srcTableName,
(int)SchemaObjectStatus.Normal,
connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
if (table.Rows.Count > 0)
{
UpdateSqlClauseBuilder updateBuilder = new UpdateSqlClauseBuilder();
DataRowToBuilder(table.Rows[0], updateBuilder, "RowUniqueID");
InsertSqlClauseBuilder insertBuilder = new InsertSqlClauseBuilder();
DataRowToBuilder(table.Rows[0], insertBuilder);
strB.AppendFormat("UPDATE {0} SET {1} WHERE {2}",
targetTableName,
updateBuilder.ToSqlString(TSqlBuilder.Instance),
builder.ToSqlString(TSqlBuilder.Instance));
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.Append("IF @@ROWCOUNT = 0");
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.AppendFormat("INSERT INTO {0} {1}",
targetTableName,
insertBuilder.ToSqlString(TSqlBuilder.Instance));
}
else
{
strB.AppendFormat("DELETE {0} WHERE {1}", targetTableName, builder.ToSqlString(TSqlBuilder.Instance));
}
}
return(strB.ToString());
}
/// <summary>
/// 查询用户所属的角色
/// </summary>
/// <param name="schemaTypes"></param>
/// <param name="appCodeName"></param>
/// <param name="userIDs"></param>
/// <param name="includingDeleted"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SchemaObjectCollection QueryUserBelongToRoles(string[] schemaTypes, string appCodeName, string[] userIDs, bool includingDeleted, DateTime timePoint)
{
SchemaObjectCollection result = new SchemaObjectCollection();
InSqlClauseBuilder userBuilder = new InSqlClauseBuilder("UC.UserID");
userBuilder.AppendItem(userIDs);
if (userBuilder.IsEmpty == false)
{
InSqlClauseBuilder schemaBuilder = new InSqlClauseBuilder("MA.MemberSchemaType");
schemaBuilder.AppendItem(schemaTypes);
WhereSqlClauseBuilder appBuilder = new WhereSqlClauseBuilder();
if (appCodeName.IsNotEmpty())
{
appBuilder.AppendItem("A.CodeName", appCodeName);
}
IConnectiveSqlClause extraBuilder = CreateStatusAndTimePointBuilder(includingDeleted, timePoint,
"SC.", "A.", "MA.", "UC.");
string resourcePath = "QueryUserBelongToRoles_Current.sql";
if (timePoint != DateTime.MinValue && includingDeleted == true)
{
resourcePath = "QueryUserBelongToRoles.sql";
}
string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(), string.Concat("MCS.Library.SOA.DataObjects.Security.Adapters.Templates.", resourcePath));
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(userBuilder, schemaBuilder, appBuilder, extraBuilder);
string sql = string.Format(sqlTemplate, connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView, (row, obj) =>
{
obj.Tag = row["AppID"].ToString();
});
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="inCondition"></param>
/// <param name="mappings"></param>
/// <returns></returns>
public TCollection LoadByInBuilder(InLoadingCondition inCondition, ORMappingItemCollection mappings = null)
{
inCondition.NullCheck("inCondition");
inCondition.BuilderAction.NullCheck("BuilderAction");
if (mappings == null)
{
mappings = this.GetQueryMappingInfo();
}
TCollection result = default(TCollection);
PerformanceMonitorHelper.GetDefaultMonitor().WriteExecutionDuration(string.Format("LoadByInBuilder({0})", this.GetType().FullName), () =>
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder(inCondition.DataField);
inCondition.BuilderAction(inBuilder);
string condition = string.Empty;
if (inBuilder.IsEmpty == false)
{
ConnectiveSqlClauseCollection builder = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And,
inBuilder, new WhereSqlClauseBuilder().AppendTenantCode(typeof(T)));
condition = builder.ToSqlString(TSqlBuilder.Instance);
OrderBySqlClauseBuilder orderByBuilder = null;
if (inCondition.OrderByAction != null)
{
orderByBuilder = new OrderBySqlClauseBuilder();
inCondition.OrderByAction(orderByBuilder);
}
result = InnerLoadByBuilder(condition, orderByBuilder, mappings);
}
else
{
result = new TCollection();
}
});
return(result);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
base.OnBuildQueryCondition(qc);
qc.SelectFields = SelectFields;
qc.FromClause = this.FromSqlClause;
if (string.IsNullOrEmpty(qc.OrderByClause))
qc.OrderByClause = DefaultOrderByClause; ;
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("S.");
var timeCondition1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("M.");
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
var allConditions = new ConnectiveSqlClauseCollection(timeCondition, timeCondition1, where);
BuildWhere(where);
if (string.IsNullOrEmpty(qc.WhereClause) == false)
qc.WhereClause += " AND ";
qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance);
}
protected void ObjectDataSourceSelecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
if (e.ExecutingSelectCount == false)
{
e.InputParameters["authType"] = (WfApplicationAuthType)int.Parse(this.rdoList.SelectedValue);
if (views.ActiveViewIndex == 1)
{
if (string.IsNullOrEmpty(this.lastUser.Value))
{
e.Cancel = true;
}
else
{
e.InputParameters["appName"] = null;
e.InputParameters["programName"] = null;
e.InputParameters["authType"] = WfApplicationAuthType.None;
WfApplicationAuthCollection authInfo = WfApplicationAuthAdapter.Instance.GetUserApplicationAuthInfo(new OguUser(lastUser.Value));
string condition = "1=2";
if (authInfo.Count > 0)
{
ConnectiveSqlClauseCollection allMatch = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or);
foreach (var item in authInfo)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("APPLICATION_NAME", item.ApplicationName);
where.AppendItem("PROGRAM_NAME", item.ProgramName);
where.AppendItem("AUTH_TYPE", item.AuthType);
allMatch.Add(where);
}
condition = allMatch.ToSqlString(TSqlBuilder.Instance);
}
else
{
e.Cancel = true;
}
e.InputParameters["where"] = condition;
}
}
}
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.SchemaOrganizationSnapshot_Current O INNER JOIN SC.SchemaRelationObjectsSnapshot_Current R ON O.ID = R.ObjectID" : "SC.SchemaOrganizationSnapshot O INNER JOIN SC.SchemaRelationObjectsSnapshot R ON O.ID = R.ObjectID";
if (this.fileterRole)
{
qc.FromClause += TimePointContext.Current.UseCurrentTime ?
" INNER JOIN SC.Acl_Current AC ON AC.ContainerID = O.ID INNER JOIN SC.SchemaRoleSnapshot_Current RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot_Current UC ON RS.ID = UC.ContainerID "
: " INNER JOIN SC.Acl AC ON AC.ContainerID = O.ID INNER JOIN SC.SchemaRoleSnapshot RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot UC ON RS.ID = UC.ContainerID ";
}
qc.SelectFields = "DISTINCT O.*,R.FullPath";
if (string.IsNullOrEmpty(qc.OrderByClause))
{
qc.OrderByClause = "R.InnerSort ASC";
}
qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND ");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("O.Status", (int)SchemaObjectStatus.Normal);
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
var schemaCondition = DataSourceUtil.SchemaTypeCondition("R.ChildSchemaType", "Organizations");
var allConditions = new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, schemaCondition);
if (this.fileterRole && this.userID != null && this.permissions != null)
{
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("AC."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("RS."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("UC."));
builder.AppendItem("AC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("RS.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.UserID", this.userID);
InSqlClauseBuilder inSql1 = new InSqlClauseBuilder("AC.ContainerPermission");
inSql1.AppendItem(this.permissions);
allConditions.Add(inSql1);
}
qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
base.OnBuildQueryCondition(qc);
qc.SelectFields = "S.*";
qc.FromClause = this.FromSqlClause;
if (string.IsNullOrEmpty(qc.OrderByClause))
qc.OrderByClause = "S.SearchContent ASC";
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("S.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("M.");
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder().NormalFor("S.Status").NormalFor("M.Status");
var allConditions = new ConnectiveSqlClauseCollection(timeCondition, timeCondition2, where);
where.AppendCondition("M.ContainerID", this.scope.ID);
if (string.IsNullOrEmpty(qc.WhereClause) == false)
qc.WhereClause += " AND ";
qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.SchemaOrganizationSnapshot_Current O INNER JOIN SC.SchemaRelationObjectsSnapshot_Current R ON O.ID = R.ObjectID" : "SC.SchemaOrganizationSnapshot O INNER JOIN SC.SchemaRelationObjectsSnapshot R ON O.ID = R.ObjectID";
if (this.fileterRole)
{
qc.FromClause += TimePointContext.Current.UseCurrentTime ?
" INNER JOIN SC.Acl_Current AC ON AC.ContainerID = O.ID INNER JOIN SC.SchemaRoleSnapshot_Current RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot_Current UC ON RS.ID = UC.ContainerID "
: " INNER JOIN SC.Acl AC ON AC.ContainerID = O.ID INNER JOIN SC.SchemaRoleSnapshot RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot UC ON RS.ID = UC.ContainerID ";
}
qc.SelectFields = "DISTINCT O.*,R.FullPath";
if (string.IsNullOrEmpty(qc.OrderByClause))
qc.OrderByClause = "R.InnerSort ASC";
qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND ");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("O.Status", (int)SchemaObjectStatus.Normal);
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
var schemaCondition = DataSourceUtil.SchemaTypeCondition("R.ChildSchemaType", "Organizations");
var allConditions = new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, schemaCondition);
if (this.fileterRole && this.userID != null && this.permissions != null)
{
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("AC."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("RS."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("UC."));
builder.AppendItem("AC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("RS.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.UserID", this.userID);
InSqlClauseBuilder inSql1 = new InSqlClauseBuilder("AC.ContainerPermission");
inSql1.AppendItem(this.permissions);
allConditions.Add(inSql1);
}
qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance);
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
if (qc.OrderByClause.IsNullOrEmpty())
qc.OrderByClause = "VersionStartTime DESC";
qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND ");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder();
builder.AppendItem("Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("SchemaType", "Applications");
var conditions = new ConnectiveSqlClauseCollection(timeCondition, builder);
qc.WhereClause += conditions.ToSqlString(TSqlBuilder.Instance);
}
/// <summary>
/// 获取一个或多个终端节点的父机构
/// </summary>
/// <param name="timePoint">时间点</param>
/// <param name="terminalIds">终结点ID的数组</param>
/// <returns>表示对象ID，和容器ID的关系。</returns>
public IEnumerable<System.Collections.Generic.KeyValuePair<string, string>> GetOwnerships(DateTime timePoint, string[] terminalIds)
{
string sql = @"SELECT R.ObjectID,R.ParentID FROM
SC.SchemaRelationObjectsSnapshot R
WHERE ";
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("R.ObjectID");
inBuilder.AppendItem(terminalIds);
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "R.");
whereBuilder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
whereBuilder.AppendItem("R.ParentSchemaType", "Organizations");
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(inBuilder, whereBuilder, timeCondition);
sql += connectiveBuilder.ToSqlString(TSqlBuilder.Instance) + " ORDER BY R.ObjectID ASC";
var ds = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName());
foreach (System.Data.DataRow row in ds.Tables[0].Rows)
{
yield return new System.Collections.Generic.KeyValuePair<string, string>(row["ObjectID"] as string, row["ParentID"] as string);
}
}
/// <summary>
/// 根据condition所提供的SQL查询条件查询
/// </summary>
/// <param name="condition"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SCConditionCollection Load(IConnectiveSqlClause condition, DateTime timePoint)
{
ConnectiveSqlClauseCollection timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(condition, timePointBuilder);
string sql = string.Format("SELECT * FROM {0} WHERE {1} ORDER BY SortID",
this.GetMappingInfo().TableName,
connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
SCConditionCollection result = new SCConditionCollection();
AUCommon.DoDbAction(() =>
{
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName()))
{
ORMapping.DataReaderToCollection(result, reader);
}
}
});
return result;
}
/// <summary>
/// 查询用户所属的权限
/// </summary>
/// <param name="schemaTypes">角色的SchemaType。权限是从角色推导出来的</param>
/// <param name="appCodeName"></param>
/// <param name="userIDs"></param>
/// <param name="includingDeleted"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SchemaObjectCollection QueryUserBelongToPermissions(string[] schemaTypes, string appCodeName, string[] userIDs, bool includingDeleted, DateTime timePoint)
{
SchemaObjectCollection result = new SchemaObjectCollection();
InSqlClauseBuilder userBuilder = new InSqlClauseBuilder("UC.UserID");
userBuilder.AppendItem(userIDs);
if (userBuilder.IsEmpty == false)
{
InSqlClauseBuilder schemaBuilder = new InSqlClauseBuilder("MA.MemberSchemaType");
schemaBuilder.AppendItem(schemaTypes);
WhereSqlClauseBuilder appBuilder = new WhereSqlClauseBuilder();
appBuilder.AppendItem("A.CodeName", appCodeName);
IConnectiveSqlClause extraBuilder = CreateStatusAndTimePointBuilder(includingDeleted, timePoint,
"SC.", "A.", "MA.", "UC.", "R.", "SR.");
string resourcePath = "QueryUserBelongToPermissions_Current.sql";
if (timePoint != DateTime.MinValue && includingDeleted == true)
resourcePath = "QueryUserBelongToPermissions.sql";
string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(), string.Concat("MCS.Library.SOA.DataObjects.Security.Adapters.Templates.", resourcePath));
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(userBuilder, schemaBuilder, appBuilder, extraBuilder);
string sql = string.Format(sqlTemplate, connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView, (row, obj) =>
{
obj.Tag = row["AppID"].ToString();
});
}
return result;
}
/// <summary>
/// 获取组织和父组织ID
/// </summary>
/// <param name="timePoint">时间点 或 <see cref="DateTime.MinValue"/>表示取当前时间</param>
/// <param name="orgIds">组织的ID</param>
/// <returns></returns>
public IEnumerable<SCWrappedSchemaOwnership> GetDirectParents(DateTime timePoint, params string[] orgIds)
{
string sql = @"SELECT O.*,R.ParentID AS QCHID FROM
SC.SchemaOrganizationSnapshot O
INNER JOIN SC.SchemaRelationObjectsSnapshot R
ON O.ID = R.ObjectID
WHERE ";
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("R.ObjectID");
inBuilder.AppendItem(orgIds);
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "R.");
whereBuilder.AppendItem("O.Status", (int)SchemaObjectStatus.Normal);
whereBuilder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
whereBuilder.AppendItem("R.ParentSchemaType", "Organizations");
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(inBuilder, whereBuilder, timeCondition, timeCondition2);
sql += connectiveBuilder.ToSqlString(TSqlBuilder.Instance) + " ORDER BY R.ParentID ASC";
var ds = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName());
SCSimpleObject lastObj = null;
foreach (System.Data.DataRow row in ds.Tables[0].Rows)
{
SCSimpleObject obj;
if (lastObj != null && lastObj.ID == (string)row["ID"])
{
obj = lastObj;
}
else
{
obj = new SCSimpleObject();
MCS.Library.Data.Mapping.ORMapping.DataRowToObject(row, obj);
lastObj = obj;
}
yield return new SCWrappedSchemaOwnership(obj, (string)row["QCHID"]);
}
}
/// <summary>
/// 根据Container来加载ItemAndContainerSnapshot的信息
/// </summary>
/// <param name="containerID"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public int CountAliveUsersByContainer(string containerID, DateTime timePoint)
{
containerID.CheckStringIsNullOrEmpty("containerID");
ConnectiveSqlClauseCollection timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem("ContainerID", containerID);
whereBuilder.AppendItem("Status", (int)SchemaObjectStatus.Normal);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(whereBuilder, timePointBuilder);
string sql = string.Format("SELECT COUNT(ContainerID) FROM {0} WHERE {1}",
this.GetLoadingTableName(timePoint), connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
int result = 0;
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
result = (int)DbHelper.RunSqlReturnScalar(sql, this.GetConnectionName());
}
return result;
}
private void ExecQuery()
{
// AddPersonQueryStringFromOrganizations();添加组织结构人员
bindingControl.CollectData();
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(QueryCondition,
new AdjustConditionValueDelegate(AdjustQueryConditionValue));
if (QueryCondition.ApplicationName == "全部")
{
QueryCondition.ApplicationName = "";
}
//builder.AppendItem("STATUS", (int)TaskStatus.Ban);
builder.AppendItem("SEND_TO_USER", DeluxeIdentity.CurrentUser.ID);
WhereSqlClauseBuilder wBuilder1 = new WhereSqlClauseBuilder();
wBuilder1.AppendItem("EXPIRE_TIME", "null", "is", true);
WhereSqlClauseBuilder wBuilder2 = new WhereSqlClauseBuilder();
wBuilder2.AppendItem("EXPIRE_TIME", "not null", "is", true);
wBuilder2.AppendItem("EXPIRE_TIME", "getdate()", ">", true);
ConnectiveSqlClauseCollection collection = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or);
collection.Add(wBuilder1);
collection.Add(wBuilder2);
WhereSqlClauseBuilder wBuilder3 = new WhereSqlClauseBuilder();
wBuilder3.AppendItem("STATUS", TaskStatus.Yue.ToString("d"));
ConnectiveSqlClauseCollection collection1 = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And);
collection1.Add(wBuilder3);
collection1.Add(collection);
ConnectiveSqlClauseCollection collection2 = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or);
WhereSqlClauseBuilder wBuilder4 = new WhereSqlClauseBuilder();
wBuilder4.AppendItem("STATUS", TaskStatus.Ban.ToString("d"));
collection2.Add(collection1);
collection2.Add(wBuilder4);
ConnectiveSqlClauseCollection collection3 = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And);
collection3.Add(builder);
collection3.Add(collection2);
whereCondition.Value = collection3.ToSqlString(TSqlBuilder.Instance);
if (QueryCondition.DraftDepartmentName != string.Empty)
{
whereCondition.Value += string.Format(" AND CONTAINS(DRAFT_DEPARTMENT_NAME,'\"*"
+ TSqlBuilder.Instance.CheckQuotationMark(QueryCondition.DraftDepartmentName, false) + "*\"')");
}
if (QueryCondition.TaskTitle != string.Empty)
{
whereCondition.Value += string.Format(" AND CONTAINS(TASK_TITLE,'\"*"
+ TSqlBuilder.Instance.CheckQuotationMark(QueryCondition.TaskTitle, false) + "*\"')");
}
LastQueryRowCount = -1;
this.GridViewTask.SelectedKeys.Clear();
this.GridViewTask.PageIndex = 0;
}
/// <summary>
/// 按照Builder查询应用所包含的元素（角色和权限）
/// </summary>
/// <param name="builder"></param>
/// <param name="includingDeleted"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SchemaObjectCollection QueryApplicationObjectsByBuilder(IConnectiveSqlClause builder, bool includingDeleted, DateTime timePoint)
{
builder.NullCheck("builder");
SchemaObjectCollection result = new SchemaObjectCollection();
if (builder.IsEmpty == false)
{
IConnectiveSqlClause extraBuilder = CreateStatusAndTimePointBuilder(includingDeleted, timePoint,
"SC.", "A.", "M.");
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(builder, extraBuilder);
string resourcePath = "QueryApplicationObjects_Current.sql";
if (timePoint != DateTime.MinValue && includingDeleted == true)
resourcePath = "QueryApplicationObjects.sql";
string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(), string.Concat("MCS.Library.SOA.DataObjects.Security.Adapters.Templates.", resourcePath));
string sql = string.Format(sqlTemplate, connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
}
return result;
}
protected void ObjectDataSourceSelecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
if (e.ExecutingSelectCount == false)
{
e.InputParameters["authType"] = (WfApplicationAuthType)int.Parse(this.rdoList.SelectedValue);
if (views.ActiveViewIndex == 1)
{
if (string.IsNullOrEmpty(this.lastUser.Value))
{
e.Cancel = true;
}
else
{
e.InputParameters["appName"] = null;
e.InputParameters["programName"] = null;
e.InputParameters["authType"] = WfApplicationAuthType.None;
WfApplicationAuthCollection authInfo = WfApplicationAuthAdapter.Instance.GetUserApplicationAuthInfo(new OguUser(lastUser.Value));
string condition = "1=2";
if (authInfo.Count > 0)
{
ConnectiveSqlClauseCollection allMatch = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or);
foreach (var item in authInfo)
{
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("APPLICATION_NAME", item.ApplicationName);
where.AppendItem("PROGRAM_NAME", item.ProgramName);
where.AppendItem("AUTH_TYPE", item.AuthType);
allMatch.Add(where);
}
condition = allMatch.ToSqlString(TSqlBuilder.Instance);
}
else
{
e.Cancel = true;
}
e.InputParameters["where"] = condition;
}
}
}
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = FromClause;
qc.SelectFields = SelectFields;
if (string.IsNullOrEmpty(this.orderBy))
qc.OrderByClause = this.DefaultOrderBy;
else
qc.OrderByClause = orderBy;
base.OnBuildQueryCondition(qc);
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
builder.AppendItem(ParentIdField, parentId);
InnerBuildWhere(builder);
var schemaCondition1 = DataSourceUtil.SchemaTypeCondition("R.ChildSchemaType", this.ChildSchemaTypes);
var schemaCondition2 = DataSourceUtil.SchemaTypeCondition("R.ParentSchemaType", this.ParentSchemaTypes);
var allConditions = new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, schemaCondition1, schemaCondition2);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
StringBuilder sb = new StringBuilder(1024);
var wbP1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("P1.");
var wbM = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("M.");
var wbM1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("M1.");
var wbR2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R2.");
var whereM = new WhereSqlClauseBuilder();
var whereR2 = new WhereSqlClauseBuilder();
var mConditions = new ConnectiveSqlClauseCollection(wbM, whereM);
var r2Conditions = new ConnectiveSqlClauseCollection(wbR2, whereR2);
whereM.AppendItem("M.MemberID", roleId);
whereR2.AppendItem("R2.ParentID", roleId);
string sqlWhere = qc.WhereClause;
if (string.IsNullOrEmpty(sqlWhere))
sqlWhere = wbM1.ToSqlString(TSqlBuilder.Instance);
else
sqlWhere = "(" + sqlWhere + ") AND " + wbM1.ToSqlString(TSqlBuilder.Instance);
sb.AppendFormat((TimePointContext.Current.UseCurrentTime ? @"(
SELECT P1.*,M1.InnerSort,M1.ContainerID as AppID
FROM SC.SchemaMembersSnapshot_Current M1
INNER JOIN SC.SchemaPermissionSnapshot_Current P1 ON M1.MemberID = P1.ID
WHERE
P1.Status = 1 AND
M1.Status = 1
AND {0}
AND {1}
AND M1.ContainerSchemaType = 'Applications' AND M1.MemberSchemaType = 'Permissions'
AND M1.ContainerID =
(SELECT M.ContainerID FROM
SC.SchemaMembersSnapshot_Current M
WHERE
{2}
AND M.ContainerSchemaType ='Applications'
AND M.MemberSchemaType = 'Roles'
AND M.Status = 1
)
) Q1 LEFT JOIN (
--查现有的权限
SELECT *
FROM SC.SchemaRelationObjectsSnapshot_Current R2
WHERE R2.ParentSchemaType = 'Roles'
AND R2.ChildSchemaType = 'Permissions'
AND R2.Status = 1
AND {3}
) Q2 ON Q1.ID = Q2.ObjectID"
: @"(
SELECT P1.*,M1.InnerSort,M1.ContainerID as AppID
FROM SC.SchemaMembersSnapshot M1
INNER JOIN SC.SchemaPermissionSnapshot P1 ON M1.MemberID = P1.ID
WHERE
P1.Status = 1 AND
M1.Status = 1
AND {0}
AND {1}
AND M1.ContainerSchemaType = 'Applications' AND M1.MemberSchemaType = 'Permissions'
AND M1.ContainerID =
(SELECT M.ContainerID FROM
SC.SchemaMembersSnapshot M
WHERE
{2}
AND M.ContainerSchemaType ='Applications'
AND M.MemberSchemaType = 'Roles'
AND M.Status = 1
)
) Q1 LEFT JOIN (
--查现有的权限
SELECT *
FROM SC.SchemaRelationObjectsSnapshot R2
WHERE R2.ParentSchemaType = 'Roles'
AND R2.ChildSchemaType = 'Permissions'
AND R2.Status = 1
AND {3}
) Q2 ON Q1.ID = Q2.ObjectID"), sqlWhere, wbP1.ToSqlString(TSqlBuilder.Instance), mConditions.ToSqlString(TSqlBuilder.Instance), r2Conditions.ToSqlString(TSqlBuilder.Instance));
qc.FromClause = sb.ToString();
qc.WhereClause = "";
if (string.IsNullOrEmpty(qc.OrderByClause))
qc.OrderByClause = "Q1.InnerSort";
qc.SelectFields = "Q1.*,Q2.ObjectID AS ApplyID";
base.OnBuildQueryCondition(qc);
}
private void ExecQuery()
{
InitPageSize();
bindingControl.CollectData();
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(QueryCondition,
new AdjustConditionValueDelegate(AdjustQueryConditionValue));
builder.AppendItem("UT.STATUS", (int)TaskStatus.Ban);
//// LDM 流转中数据的获取
//if (null != Request.QueryString["process_status"] && Request.QueryString["process_status"] == "Running")
// builder.AppendItem("PN.STATUS", "Running");
//// LDM 已办结数据的获取
//if (null != Request.QueryString["process_status"] && Request.QueryString["process_status"] == "Completed")
// builder.AppendItem("ISNULL(PN.STATUS,N'Completed')", "Completed");
if (QueryCondition.ApplicationName == "全部")
{
QueryCondition.ApplicationName = "";
}
string subjectQueryString = string.Empty;
if (string.IsNullOrEmpty(QueryCondition.TaskTitle) == false)
{
StringBuilder subjectSB = new StringBuilder();
//关键词分割符为全角或半角空格
char[] separators = new char[] { ' ', ' ' };
string[] wordsSplitted = QueryCondition.TaskTitle.Split(separators, StringSplitOptions.RemoveEmptyEntries);
//将关键词构造为谓词查询格式
for (int i = 0; i < wordsSplitted.Length; i++)
{
if (i > 0)
{
subjectSB.Append(" AND ");
}
subjectSB.Append("\"");
subjectSB.Append(wordsSplitted[i].Replace("\"", "\"\""));
subjectSB.Append("\"");
}
subjectQueryString = string.Format("CONTAINS(TASK_TITLE,{0})", TSqlBuilder.Instance.CheckQuotationMark(subjectSB.ToString(), true));
}
WhereSqlClauseBuilder processStatusBuilder = new WhereSqlClauseBuilder(LogicOperatorDefine.Or);
switch (WebUtility.GetRequestQueryString("process_status", "Running"))
{
case "Running":
processStatusBuilder.AppendItem("PN.STATUS", "Running");
processStatusBuilder.AppendItem("PN.STATUS", "Maintaining");
break;
case "Completed":
processStatusBuilder.AppendItem("PN.STATUS", "Completed");
processStatusBuilder.AppendItem("PN.STATUS", "Aborted");
break;
}
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection();
connectiveBuilder.Add(builder).Add(processStatusBuilder);
if (connectiveBuilder.IsEmpty)
whereCondition.Value = subjectQueryString;
else if (subjectQueryString == string.Empty)
whereCondition.Value = connectiveBuilder.ToSqlString(TSqlBuilder.Instance);
else
whereCondition.Value = connectiveBuilder.ToSqlString(TSqlBuilder.Instance) + " AND " + subjectQueryString;
if (QueryCondition.DraftDepartmentName != string.Empty)
{
whereCondition.Value += string.Format(" AND CONTAINS(DRAFT_DEPARTMENT_NAME,'\"*"
+ TSqlBuilder.Instance.CheckQuotationMark(QueryCondition.DraftDepartmentName, false) + "*\"')");
}
whereCondition.Value += string.Format(" AND SEND_TO_USER = {0}",
TSqlBuilder.Instance.CheckQuotationMark(DeluxeIdentity.CurrentUser.ID, true));
LastQueryRowCount = -1;
this.GridViewTask.PageIndex = 0;
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.FromClause = FromClause;
qc.SelectFields = SelectFields;
if (this.orderBy.IsNullOrEmpty())
qc.OrderByClause = this.DefaultOrderBy;
else
qc.OrderByClause = orderBy;
base.OnBuildQueryCondition(qc);
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
builder.AppendItem(ParentIdField, parentId);
builder.AppendItem<int>("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem<int>("O.Status", (int)SchemaObjectStatus.Normal);
InnerBuildWhere(builder);
var conditionMemberCondition = DataSourceUtil.SchemaTypeCondition("R.MemberSchemaType", this.MemberSchemaTypes);
var conditionContainerCondition = DataSourceUtil.SchemaTypeCondition("R.ContainerSchemaType", this.ContainerSchemaTypes);
var allCondition = new ConnectiveSqlClauseCollection(builder, conditionMemberCondition, conditionMemberCondition, timeCondition, timeCondition2);
if (string.IsNullOrEmpty(qc.WhereClause))
{
qc.WhereClause = allCondition.ToSqlString(TSqlBuilder.Instance);
}
else
{
qc.WhereClause = allCondition.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")";
}
}
/// <summary>
/// 根据角色ID查询出它的内容
/// </summary>
/// <param name="schemaTypes">角色的SchemaType</param>
/// <param name="roleIDs"></param>
/// <param name="includingDeleted"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SCObjectAndRelationCollection QueryRolesContainsMembers(string[] schemaTypes, string[] roleIDs, bool includingDeleted, DateTime timePoint)
{
SCObjectAndRelationCollection result = new SCObjectAndRelationCollection();
InSqlClauseBuilder roleBuilder = new InSqlClauseBuilder("R.ID");
roleBuilder.AppendItem(roleIDs);
if (roleBuilder.IsEmpty == false)
{
InSqlClauseBuilder schemaBuilder = new InSqlClauseBuilder("MA.MemberSchemaType");
schemaBuilder.AppendItem(schemaTypes);
IConnectiveSqlClause extraBuilder = CreateStatusAndTimePointBuilder(includingDeleted, timePoint,
"A.", "MA.", "R.", "UC.", "SR.", "SC.");
string resourcePath = "QueryRolesContainsMembers_Current.sql";
if (timePoint != DateTime.MinValue && includingDeleted == true)
resourcePath = "QueryRolesContainsMembers.sql";
string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(), string.Concat("MCS.Library.SOA.DataObjects.Security.Adapters.Templates.", resourcePath));
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(roleBuilder, schemaBuilder, extraBuilder);
string sql = string.Format(sqlTemplate, connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
result.LoadFromDataView(table.DefaultView);
}
return result;
}
private string BuildWhereCondition()
{
WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(this.Condition,
new AdjustConditionValueDelegate(AdjustQueryConditionValue));
builder.AppendItem("SEND_TO_USER", DeluxeIdentity.CurrentUser.ID);
WhereSqlClauseBuilder whereExpireTime = new WhereSqlClauseBuilder(LogicOperatorDefine.Or);
whereExpireTime.AppendItem("EXPIRE_TIME", "null", "is", true);
whereExpireTime.AppendItem("EXPIRE_TIME", "getdate()", ">", true);
WhereSqlClauseBuilder whereStatus = new WhereSqlClauseBuilder();
whereStatus.AppendItem("STATUS", ((int)TaskStatus.Yue).ToString());
var conditionGroup1 = new ConnectiveSqlClauseCollection(whereExpireTime, whereStatus);
WhereSqlClauseBuilder whereStatus2 = new WhereSqlClauseBuilder();
whereStatus2.AppendItem("STATUS", ((int)TaskStatus.Ban).ToString());
//collection2.Add(collection1);
ConnectiveSqlClauseCollection collection2 = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or, conditionGroup1, whereStatus2);
var allConditions = new ConnectiveSqlClauseCollection(builder, collection2);
return allConditions.ToSqlString(TSqlBuilder.Instance);
}
private WhereSqlClauseBuilder GetCurrentAssigneesSubQuery()
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("USER_ID");
this.CurrentAssignees.ForEach(u => inBuilder.AppendItem(u.ID));
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
if (this.AssigneesUserName.IsNotEmpty())
wBuilder.AppendItem("USER_NAME", TSqlBuilder.Instance.EscapeLikeString(this.AssigneesUserName) + "%", "LIKE");
ConnectiveSqlClauseCollection subQueryBuilder = new ConnectiveSqlClauseCollection();
subQueryBuilder.Add(inBuilder);
subQueryBuilder.Add(wBuilder);
WhereSqlClauseBuilder resultBuilder = new WhereSqlClauseBuilder();
if (subQueryBuilder.IsEmpty == false)
{
string processCondition = string.Empty;
switch (this.AssigneesSelectType)
{
case WfAssigneesFilterType.CurrentActivity:
processCondition = "CA.ACTIVITY_ID = CURRENT_ACTIVITY_ID";
break;
case WfAssigneesFilterType.AllActivities:
processCondition = "CA.PROCESS_ID = INSTANCE_ID";
break;
}
string subQuery = string.Format("EXISTS(SELECT USER_ID FROM WF.PROCESS_CURRENT_ASSIGNEES CA (NOLOCK) WHERE {0} AND {1})",
processCondition, subQueryBuilder.ToSqlString(TSqlBuilder.Instance));
resultBuilder.AppendItem("CurrentAssignees", subQuery, "=", "${Data}$", true);
}
return resultBuilder;
}
/// <summary>
/// 根据Container来加载ItemAndContainerSnapshot的信息
/// </summary>
/// <param name="containerID"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SameContainerItemAndContainerSnapshotCollection LoadByContainerID(string containerID, DateTime timePoint)
{
containerID.CheckStringIsNullOrEmpty("containerID");
ConnectiveSqlClauseCollection timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem("ContainerID", containerID);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(whereBuilder, timePointBuilder);
string sql = string.Format("SELECT * FROM {0} WHERE {1}",
this.GetLoadingTableName(timePoint), connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
SameContainerItemAndContainerSnapshotCollection result = new SameContainerItemAndContainerSnapshotCollection();
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName()))
{
while (reader.Read())
{
ItemAndContainerSnapshot item = new ItemAndContainerSnapshot();
ORMapping.DataReaderToObject(reader, item);
if (result.ContainsKey(item.ItemID) == false)
result.Add(item);
}
}
}
return result;
}
protected override void OnBuildQueryCondition(QueryCondition qc)
{
qc.SelectFields = "U.*,ParentID='' ";
qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.SchemaUserSnapshot_Current U " : "SC.SchemaUserSnapshot U ";
if (qc.OrderByClause.IsNullOrEmpty())
qc.OrderByClause = "U.VersionStartTime DESC";
if (this.fileterRole && this.parentPermissions != null)
{
var joinClause = TimePointContext.Current.UseCurrentTime ?
@" INNER JOIN SC.SchemaRelationObjectsSnapshot_Current R ON R.ObjectID = U.ID INNER JOIN SC.Acl_Current AC ON AC.ContainerID = R.ParentID INNER JOIN SC.SchemaRoleSnapshot_Current RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot_Current UC ON RS.ID = UC.ContainerID "
:
@" INNER JOIN SC.SchemaRelationObjectsSnapshot R ON R.ObjectID = U.ID INNER JOIN SC.Acl AC ON AC.ContainerID = R.ParentID INNER JOIN SC.SchemaRoleSnapshot RS ON RS.ID = AC.MemberID INNER JOIN SC.UserAndContainerSnapshot UC ON RS.ID = UC.ContainerID ";
qc.FromClause += joinClause;
}
qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND ");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem("U.Status", (int)SchemaObjectStatus.Normal);
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("U.");
var allConditions = new ConnectiveSqlClauseCollection(builder, timeCondition);
if (this.fileterRole && this.logonUserID != null && this.parentPermissions != null)
{
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("AC."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("RS."));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("UC."));
builder.AppendItem("AC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("RS.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("UC.UserID", this.logonUserID);
allConditions.Add(DataSourceUtil.SchemaTypeCondition("R.ParentSchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames()));
allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R."));
if (parentPermissions.Length > 0)
{
InSqlClauseBuilder inSql1 = new InSqlClauseBuilder("AC.ContainerPermission");
inSql1.AppendItem(this.parentPermissions);
allConditions.Add(inSql1);
}
}
if (this.excludeID != null)
{
builder.AppendItem("U.ID", this.excludeID, "<>");
}
if (this.defaultOnly)
{
builder.AppendItem("R.IsDefault", 1);
}
if (this.dissociate)
{
var timeConditionIn = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("RR.");
WhereSqlClauseBuilder innerWhere = new WhereSqlClauseBuilder();
innerWhere.AppendItem("RR.Status", (int)SchemaObjectStatus.Normal);
InSqlClauseBuilder innerInParent = new InSqlClauseBuilder("RR.ParentSchemaType");
innerInParent.AppendItem(SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
allConditions.Add(new ExistExpressionBuilder(true, string.Format(@"SELECT TOP 1 1 FROM SC.SchemaRelationObjects RR WHERE
RR.ObjectID = U.ID AND RR.ChildSchemaType = U.SchemaType AND {0}", new ConnectiveSqlClauseCollection(timeConditionIn, innerWhere, innerInParent).ToSqlString(TSqlBuilder.Instance))));
}
qc.WhereClause += allConditions.ToSqlString(TSqlBuilder.Instance);
}
