private static string GetSnapshotTableQuery(QueryCondition qc)
{
StringBuilder strB = new StringBuilder(256);
string whereClause = qc.WhereClause;
bool usingCurrentTime = TimePointContext.Current.UseCurrentTime;
strB.AppendLine("SELECT R.ContainerID, O.ID, O.Name, O.DisplayName, O.CodeName, O.SchemaType, O.Status, O.VersionStartTime, O.VersionEndTime,O.CreatorID,O.CreatorName,O.CreateDate, R.InnerSort");
strB.AppendFormat("FROM SC.SchemaObjectSnapshot{0} O INNER JOIN SC.SchemaMembersSnapshot{1} R ON O.ID = R.MemberID AND O.SchemaType = R.MemberSchemaType\n", usingCurrentTime ? "_Current" : "", usingCurrentTime ? "_Current" : "");
strB.Append("WHERE 1 = 1 ");
//WhereSqlClauseBuilder buildCommon = new WhereSqlClauseBuilder(LogicOperatorDefine.And);
//buildCommon.AppendItem("R.ContainerSchemaType", "Roles");
//buildCommon.AppendItem("R.MemberSchemaType", schema.Category);
//strB.Append(" AND " + buildCommon.ToSqlString(TSqlBuilder.Instance));
strB.Append(" AND " + DataSourceUtil.SchemaTypeCondition("R.ContainerSchemaType", "Roles").ToSqlString(TSqlBuilder.Instance));
//strB.Append(" AND " + DataSourceUtil.SchemaTypeCondition("R.MemberSchemaType", schema.Name).ToSqlString(TSqlBuilder.Instance));
var builderObj = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
strB.Append(" AND " + builderObj.ToSqlString(TSqlBuilder.Instance));
var builderRelation = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
strB.Append(" AND " + builderRelation.ToSqlString(TSqlBuilder.Instance));
if (whereClause.IsNotEmpty())
{
strB.Append(" AND " + whereClause);
}
return(strB.ToString());
}
public System.Collections.Generic.Stack <SchemaNavPathNode> Query(Guid id, DateTime timePoint)
{
System.Collections.Generic.Stack <SchemaNavPathNode> stack = new Stack <SchemaNavPathNode>();
StringBuilder sb = new StringBuilder();
var db = DbHelper.GetDBDatabase(SCConnectionDefine.DBConnectionName);
SchemaNavPathNode node;
do
{
node = null;
sb.Clear();
sb.Append(TimePointContext.Current.UseCurrentTime ? @"
SELECT R.ObjectID, R.ParentID, O.ID, O.Name, O.DisplayName,O.SchemaType
FROM SC.SchemaOrganizationSnapshot_Current AS O INNER JOIN SC.SchemaRelationObjects_Current AS R ON O.ID = R.ObjectID
WHERE " : @"
SELECT R.ObjectID, R.ParentID, O.ID, O.Name, O.DisplayName,O.SchemaType
FROM SC.SchemaOrganizationSnapshot AS O INNER JOIN SC.SchemaRelationObjects AS R ON O.ID = R.ObjectID
WHERE ");
WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder();
builder.AppendItem <int>("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem <string>("R.ParentSchemaType", "Organizations");
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O.");
builder.AppendItem <int>("O.Status", (int)SchemaObjectStatus.Normal);
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "R.");
builder.AppendItem <int>("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem <string>("R.ObjectID", id.ToString());
var schemaConditon1 = DataSourceUtil.SchemaTypeCondition("R.ParentSchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
var schemaConditon2 = DataSourceUtil.SchemaTypeCondition("O.SchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
var schemaConditon3 = DataSourceUtil.SchemaTypeCondition("O.ChildSchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
sb.Append(new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, schemaConditon1, schemaConditon2, schemaConditon3).ToSqlString(TSqlBuilder.Instance));
using (var dr = db.ExecuteReader(System.Data.CommandType.Text, sb.ToString()))
{
if (dr.Read())
{
node = new SchemaNavPathNode()
{
ID = dr.GetString(0),
ParentId = dr.GetString(1),
Name = dr.GetString(2),
Description = dr.GetString(3),
NodeType = dr.GetString(4)
};
stack.Push(node);
id = Guid.Parse(node.ParentId);
}
}
} while (node != null && node.ID != SCOrganization.RootOrganizationID);
return(stack);
}
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)
{
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 + ")";
}
}
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 + ")";
}
}
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";
qc.SelectFields = "O.*";
if (string.IsNullOrEmpty(qc.OrderByClause))
{
qc.OrderByClause = "R.InnerSort DESC";
}
qc.WhereClause.IsNotEmpty((s) => qc.WhereClause += " AND ");
var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R.");
var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.");
var builder = new WhereSqlClauseBuilder();
builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal);
builder.AppendItem("R.ParentID", SCOrganization.RootOrganizationID);
IConnectiveSqlClause clause = DataSourceUtil.SchemaTypeCondition("R.ChildSchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames());
qc.WhereClause += new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, clause).ToSqlString(TSqlBuilder.Instance);
}
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 OnAfterQuery(DataView result)
{
base.OnAfterQuery(result);
DataSourceUtil.FillUserDefaultParent(result, "ID", "ParentID", this.GetConnectionName());
}
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);
}
