public override SqlBuilder BuildSql(ICollection <SqlQuerySourceAttribute> attrs, ICollection <SqlQueryCondition> conditions, bool isMain = false) { var builder = new SqlBuilder(); var tableMap = Mapper != null?Mapper.Find(Source.Id) : null; var needToJoinDocument = isMain || (tableMap != null && NeedToJoinDocument(tableMap)); var withOption = WithNoLock ? "WITH(NOLOCK)" : ""; var fromAlias = tableMap != null ? "t" : "d"; var orgAlias = (tableMap != null && !needToJoinDocument) ? "t" : "d"; var createdAlias = (tableMap != null && tableMap.FindIdentField("Created") != null) ? "t" : "d"; var modifiedAlias = (tableMap != null && tableMap.FindIdentField("Last_Modified") != null) ? "t" : "d"; var userAlias = (tableMap != null && tableMap.FindIdentField("User_Id") != null) ? "t" : "d"; var defAlias = (tableMap != null && tableMap.FindIdentField("Def_Id") != null) ? "t" : "d"; var tableMapToDocJoin = tableMap != null && tableMap.IsVirtual ? "LEFT OUTER" : "INNER"; var from = tableMap == null ? "Documents d " + withOption : needToJoinDocument ? String.Format("{0} {1} JOIN Documents d {2} ON t.Id = d.Id", GetTableMapSql(tableMap, "t", withOption), tableMapToDocJoin, withOption) : String.Format("{0}", GetTableMapSql(tableMap, "t", withOption)); // ? String.Format("[{0}] t {1} INNER JOIN Documents d {1} ON t.Id = d.Id", tableMap.TableName, withOption) // : String.Format("[{0}] t {1}", tableMap.TableName, withOption); // var from = "Documents d"; var orgRestriction = ""; // if (WithNoLock) from += " " + withOption; if (UserId != Guid.Empty && !Source.IsPublic) { /*using (*/ //var userRepo = new UserRepository(DataContext);/*)*/ { /* var userInfo = userRepo.GetUserInfo(UserId); * * from += " inner join Organizations o WITH(NOLOCK) on o.Id = d.Organization_Id"; * orgRestriction = String.Format(SourceOrgRestrictCondition, userInfo.OrgUnitTypeId, * userInfo.OrganizationId, withOption);*/ var userOrgs = new List <Guid>(_userRepo.GetUserAccessOrgs(UserId)); if (userOrgs.Count > 0) { foreach (var orgId in userOrgs) { if (orgRestriction.Length > 0) { orgRestriction += ", "; } orgRestriction += String.Format("'{0}'", orgId); } orgRestriction = String.Format( userOrgs.Count == 1 ? "{0}.Organization_Id = {1}" : "{0}.Organization_Id IN ({1})", orgAlias, orgRestriction); } } } builder.SetFrom(from); builder.AddSelect(fromAlias + ".Id"); var usedConditions = new List <SqlQueryCondition>(); var attrIndexDictionary = new Dictionary <SqlQuerySourceAttribute, int>(); var no = 1; if (attrs != null && Attributes != null) { foreach (var attr in attrs) { var i = no; if (!Attributes.Contains(attr)) { continue; } if (attrIndexDictionary.ContainsKey(attr)) { continue; } attrIndexDictionary.Add(attr, i); var attributeConditions = new List <SqlQueryCondition>(GetQuerySourceAttributeConditions(attr, conditions)); var attrConditionStr = SqlQueryCondition.BuildConditions(attributeConditions, "[{0}{1}].[{2}]"); var hasCondition = HasQuerySourceAttributeConditions(attr, conditions); var sb = new ScriptStringBuilder(); var attribute = attr; if (attr.Def != null) { var attrTableField = tableMap != null ? tableMap.FindField(attribute.Def.Id) /*tableMap.Fields.FirstOrDefault( * f => f.AttrDefId == attribute.Def.Id)*/ : null; var attrDef = attr.Def; if (attrDef.Type.Id == (short)CissaDataType.DocList) { if (String.IsNullOrEmpty(attrConditionStr)) { builder.AddJoin( SqlSourceJoinType.LeftOuter, String.Format(SourceInnerJoinDocListAttr2, attrDef.Id, i, withOption, String.Empty)); } else { builder.AddJoin( SqlSourceJoinType.Inner, String.Format(SourceInnerJoinDocListAttr2, attr.Def.Id, i, withOption, String.Format(" and " + attrConditionStr, "dla", i, "Value"))); } sb.AppendFormat("[dla{0}].[Value] as [{1}]", i, attr.AliasName); } else if (attrDef.Type.Id == (short)CissaDataType.Blob) { var tableName = attr.GetAttrDefTableName(); sb.AppendFormat("[a{0}].[File_Name] as [{1}]", i, attr.AliasName); if (String.IsNullOrEmpty(attrConditionStr)) { builder.AddJoin( SqlSourceJoinType.LeftOuter, String.Format( "{0} a{1} {2} on (a{1}.Document_Id = {4}.Id and a{1}.Def_Id = '{3}' and a{1}.Expired = '99991231')", tableName, i, withOption, attr.Def.Id, fromAlias)); } else { var valueFieldName = attr.Def.Type.Id == (short)CissaDataType.Text ? "Upper_" : ""; builder.AddJoin( SqlSourceJoinType.Inner, String.Format( "{0} a{1} {2} on (a{1}.Document_Id = {5}.Id and a{1}.Def_Id = '{3}' and a{1}.Expired = '99991231' and {4})", tableName, i, withOption, attr.Def.Id, String.Format(attrConditionStr, "a", i, valueFieldName + "Value"), fromAlias)); } } else if (attrDef.Type.Id == (short)CissaDataType.OrganizationOfDocument) { if (String.IsNullOrEmpty(attrConditionStr)) { sb.AppendFormat("(select od{0}.Full_Name", i); sb.AppendFormat( " from Organizations o{0} {1} inner join Object_Defs od{0} {1} on od{0}.Id = o{0}.Id\n", i, withOption); sb.AppendFormat(" where o{0}.Id = {2}.Organization_Id) as [{1}]", i, attr.AliasName, orgAlias); } else { sb.AppendFormat("[od{0}].[Full_Name] as [{1}]", i, attr.AliasName); builder.AddJoin( SqlSourceJoinType.Inner, String.Format("Organizations o{0} {1} on o{0}.[Id] = {2}.Organization_Id", i, withOption, orgAlias)); builder.AddJoin( SqlSourceJoinType.Inner, String.Format("Object_Defs od{0} {1} on (od{0}.[Id] = o{0}.[Id] and {2})", i, withOption, String.Format(attrConditionStr, "od", i, "Full_Name"))); } } /* * else if (attrDef.Type.Id == (short)CissaDataType.AuthorOfDocument) * { * sb.AppendFormat("(select top 1 od{0}.Full_Name from Workers w{0} ", i); * sb.AppendFormat("inner join Object_Defs od{0} on od{0}.Id = w{0}.Id ", i); * sb.AppendFormat( * " where o{0}.Id = d.Organization_Id) as [{1}]", * i, attr.AliasName); * } * else if (attrDef.Type.Id == (short)CissaDataType.Organization) * { * var tableName = attr.GetAttrDefTableName(); * sb.AppendFormat("(select top 1 od{0}.Value from {1} oa{0} \n", i, tableName); * // sb.AppendFormat(" inner join Organizations o{0} on o{0}.Id = oa{0}.Value\n", i); * // sb.AppendFormat(" inner join Object_Defs od{0} on od{0}.Id = o{0}.Id\n", i); * sb.AppendFormat( * " where oa{0}.Document_Id = d.Id and oa{0}.Def_Id = '{1}' and oa{0}.Expired = convert(date, '31/12/9999', 103)) as [{2}]", * i, attrDef.Id, attr.AliasName); * } * else if (attrDef.Type.Id == (short)CissaDataType.DocumentState) * { * var tableName = attr.GetAttrDefTableName(); * sb.AppendFormat("(select top 1 od{0}.Full_Name from {1} da{0} \n", i, tableName); * sb.AppendFormat(" inner join Document_State_Types dst{0} on dst{0}.Id = da{0}.Value\n", i); * sb.AppendFormat(" inner join Object_Defs od{0} on od{0}.Id = dst{0}.Id\n", i); * sb.AppendFormat( * " where da{0}.Document_Id = d.Id and da{0}.Def_Id = '{1}' and da{0}.Expired = convert(date, '31/12/9999', 103)) as [{2}]", * i, attrDef.Id, attr.AliasName); * } */ else { var tableName = attr.GetAttrDefTableName(); if (attrTableField != null) { if (attrTableField.Type.HasFlag(AttributeFieldType.View)) { sb.AppendFormat("[{0}].[{1}] as [{2}]", fromAlias, attrTableField.FieldName, attr.AliasName); } else { sb.AppendFormat( "(select [a{0}].[Value] from [{1}] [a{0}] {2}\n" + "where [a{0}].Document_Id = {3}.Id and [a{0}].Def_Id = '{4}' and [a{0}].Expired = '99991231') as [{5}]", i, tableName, withOption, fromAlias, attr.Def.Id, attr.AliasName); } if (!String.IsNullOrEmpty(attrConditionStr)) { if (attrTableField.Type.HasFlag(AttributeFieldType.Search)) { builder.AddCondition(ExpressionOperation.And, String.Format(attrConditionStr, fromAlias, String.Empty, attrTableField.FieldName)); } else { var valueFieldName = attr.Def.Type.Id == (short)CissaDataType.Text ? "Upper_" : ""; builder.AddJoin( SqlSourceJoinType.Inner, String.Format( "{0} a{1} {2} on (a{1}.Document_Id = {5}.Id and a{1}.Def_Id = '{3}' and a{1}.Expired = '99991231' and {4})", tableName, i, withOption, attr.Def.Id, String.Format(attrConditionStr, "a", i, valueFieldName + "Value"), fromAlias)); } } } else { sb.AppendFormat("[a{0}].[Value] as [{1}]", i, attr.AliasName); if (String.IsNullOrEmpty(attrConditionStr)) { builder.AddJoin( SqlSourceJoinType.LeftOuter, String.Format( "{0} a{1} {2} on (a{1}.Document_Id = {4}.Id and a{1}.Def_Id = '{3}' and a{1}.Expired = '99991231')", tableName, i, withOption, attr.Def.Id, fromAlias)); } else { var valueFieldName = attr.Def.Type.Id == (short)CissaDataType.Text ? "Upper_" : ""; builder.AddJoin( SqlSourceJoinType.Inner, String.Format( "{0} a{1} {2} on (a{1}.Document_Id = {5}.Id and a{1}.Def_Id = '{3}' and a{1}.Expired = '99991231' and {4})", tableName, i, withOption, attr.Def.Id, String.Format(attrConditionStr, "a", i, valueFieldName + "Value"), fromAlias)); } } } } else if (attr.Ident == SystemIdent.State || attr.Ident == SystemIdent.InState) { var stateTableField = tableMap != null?tableMap.FindIdentField("State") : null; if (stateTableField != null) { sb.AppendFormat("[{0}].[State] as [{1}]", fromAlias, attr.AliasName); if (!String.IsNullOrEmpty(attrConditionStr)) { builder.AddCondition(ExpressionOperation.And, String.Format(attrConditionStr, fromAlias, String.Empty, stateTableField.FieldName)); } } else { sb.AppendFormat("[ds{0}].[State_Type_Id] as [{1}]", i, attr.AliasName); if (String.IsNullOrEmpty(attrConditionStr)) { //sb.AppendFormat("(select ds{0}.State_Type_Id from Document_States ds{0} {1}\n", i, // withOption); //sb.AppendFormat( // " where ds{0}.Document_Id = {2}.Id and ds{0}.Expired = '99991231') as [{1}]", // i, attr.AliasName, fromAlias); builder.AddJoin( SqlSourceJoinType.LeftOuter, String.Format( "[Document_States] ds{0} {1} on (ds{0}.Document_Id = {2}.Id and ds{0}.Expired = '99991231')", i, withOption, fromAlias)); } else { builder.AddJoin( SqlSourceJoinType.Inner, String.Format( "[Document_States] ds{0} {1} on (ds{0}.Document_Id = {3}.Id and ds{0}.Expired = '99991231' and {2})", i, withOption, String.Format(attrConditionStr, "ds", i, "State_Type_Id"), fromAlias)); } } } else if (attr.Ident == SystemIdent.StateDate) { if (String.IsNullOrEmpty(attrConditionStr)) { sb.AppendFormat("(select ds{0}.Created from Document_States ds{0} {1}\n", i, withOption); sb.AppendFormat( " where ds{0}.Document_Id = {2}.Id and ds{0}.Expired = '99991231') as [{1}]", i, attr.AliasName, fromAlias); } else { sb.AppendFormat("[ds{0}].[Created] as [{1}]", i, attr.AliasName); builder.AddJoin( SqlSourceJoinType.Inner, String.Format( "[Document_States] ds{0} {1} on (ds{0}.Document_Id = {3}.Id and ds{0}.Expired = '99991231' and {2})", i, withOption, String.Format(attrConditionStr, "ds", i, "Created"), fromAlias)); } } else if (attr.Ident == SystemIdent.Id) { sb.AppendFormat("{0}.Id as [{1}]", fromAlias, attr.AliasName); if (!String.IsNullOrEmpty(attrConditionStr)) { builder.AddCondition(ExpressionOperation.And, String.Format(attrConditionStr, fromAlias, String.Empty, "Id")); } } else if (attr.Ident == SystemIdent.OrgId) { sb.AppendFormat("{0}.Organization_Id as [{1}]", orgAlias, attr.AliasName); if (!String.IsNullOrEmpty(attrConditionStr)) { builder.AddCondition(ExpressionOperation.And, String.Format(attrConditionStr, orgAlias, "", "Organization_Id")); } } else if (attr.Ident == SystemIdent.Created) { sb.AppendFormat("{0}.Created as [{1}]", createdAlias, attr.AliasName); if (!String.IsNullOrEmpty(attrConditionStr)) { builder.AddCondition(ExpressionOperation.And, String.Format(attrConditionStr, createdAlias, "", "Created")); } } else if (attr.Ident == SystemIdent.Modified) { sb.AppendFormat("{0}.Last_Modified as [{1}]", modifiedAlias, attr.AliasName); if (!String.IsNullOrEmpty(attrConditionStr)) { builder.AddCondition(ExpressionOperation.And, String.Format(attrConditionStr, modifiedAlias, "", "Last_Modified")); } } else if (attr.Ident == SystemIdent.UserId || attr.Ident == SystemIdent.UserName) { sb.AppendFormat("{0}.UserId as [{1}]", userAlias, attr.AliasName); if (!String.IsNullOrEmpty(attrConditionStr)) { builder.AddCondition(ExpressionOperation.And, String.Format(attrConditionStr, userAlias, "", "UserId")); } } else if (attr.Ident == SystemIdent.DefId) { sb.AppendFormat("{0}.Def_Id as [{1}]", defAlias, attr.AliasName); if (!String.IsNullOrEmpty(attrConditionStr)) { builder.AddCondition(ExpressionOperation.And, String.Format(attrConditionStr, defAlias, "", "Def_Id")); } } /*else if (attr.Ident == SystemIdent.UserName) * { * if (String.IsNullOrEmpty(attrConditionStr)) * { * sb.AppendFormat("(select w{0}.User_Name from Workers w{0} {1}\n", i, withOption); * sb.AppendFormat(" where w{0}.Id = {2}.UserId) as [{1}]", i, attr.AliasName, userAlias); * } * else * { * sb.AppendFormat("[w{0}].[User_Name] as [{1}]", i, attr.AliasName); * builder.AddJoin( * SqlSourceJoinType.Inner, * String.Format("Workers w{0} {1} on (w{0}.Id = {3}.UserId and {2})", i, withOption, * String.Format(attrConditionStr, "w", i, "User_Name"), userAlias)); * } * }*/ else if (attr.Ident == SystemIdent.OrgCode) { if (String.IsNullOrEmpty(attrConditionStr)) { sb.AppendFormat("(select o{0}.Code from Organizations o{0} {1}\n", i, withOption); sb.AppendFormat(" where o{0}.Id = {2}.Organization_Id) as [{1}]", i, attr.AliasName, orgAlias); } else { sb.AppendFormat("[o{0}].[Code] as [{1}]", i, attr.AliasName); builder.AddJoin( SqlSourceJoinType.Inner, String.Format("Organizations o{0} {1} on (o{0}.Id = {3}.Organization_Id and {2})", i, withOption, String.Format(attrConditionStr, "o", i, "Code"), orgAlias)); } } else if (attr.Ident == SystemIdent.OrgName) { if (String.IsNullOrEmpty(attrConditionStr)) { sb.AppendFormat( "(select od{0}.Full_Name\n" + " from Organizations o{0} {1} inner join Object_Defs od{0} {1} on od{0}.Id = o{0}.Id\n", i, withOption); sb.AppendFormat(" where o{0}.Id = {2}.Organization_Id) as [{1}]", i, attr.AliasName, orgAlias); } else { sb.AppendFormat("[od{0}].[Full_Name] as [{1}]", i, attr.AliasName); builder.AddJoin( SqlSourceJoinType.Inner, String.Format("Organizations o{0} {1} on o{0}.[Id] = {2}.Organization_Id", i, withOption, orgAlias)); builder.AddJoin( SqlSourceJoinType.Inner, String.Format("Object_Defs od{0} {1} on od{0}.[Id] = o{0}.[Id] and {2}", i, withOption, String.Format(attrConditionStr, "od", i, "Full_Name"))); } } else { throw new ApplicationException("Не известный атрибут в условии запроса!"); } no++; builder.AddSelect(sb); usedConditions.AddRange(attributeConditions); } } //if (builder.Select == null || builder.Select.Count == 0) builder.AddSelect("d.Id"); if (tableMap == null) { var descendants = new List <Guid>(_docDefRepo.GetDocDefDescendant(Source.Id)); if (descendants.Count < 2) { builder.AddCondition(ExpressionOperation.And, String.Format("{0}.Def_Id = '{1}'", fromAlias, Source.Id)); } else { var s = String.Empty; foreach (var defId in descendants) { if (!String.IsNullOrEmpty(s)) { s += ","; } s += String.Format("'{0}'", defId); } builder.AddCondition(ExpressionOperation.And, String.Format("{0}.Def_Id IN ({1})", fromAlias, s)); } } if (!String.IsNullOrEmpty(orgRestriction)) { builder.AddCondition(ExpressionOperation.And, orgRestriction); } if (isMain || (needToJoinDocument && (tableMap == null || !tableMap.IsVirtual))) { builder.AddCondition(ExpressionOperation.And, "([d].[Deleted] is null OR [d].[Deleted] = 0)"); } BuildConditions(builder, conditions, attrIndexDictionary, tableMap, needToJoinDocument, usedConditions); return(builder); }
internal string BuildConditions() { return(SqlQueryCondition.BuildConditions(Conditions)); }