//Case 30293: This is not intended to provide the most rubust SQL Param massage possible; //rather, it handles only a few edge cases. This should be refactored as more robust SQL mechanics are implemented. private static string _getParamVal(string Param) { string Ret = ""; Int32 IntVal = CswConvert.ToInt32(Param); if (Int32.MinValue != IntVal) { Ret = IntVal.ToString(); } else { Ret = CswTools.SafeSqlParam(Param); } return(Ret); }
} // getViews() public CswNbtViewId getViewIdByName(string ViewName, CswEnumNbtViewVisibility Visibility, CswPrimaryKey VisibilityRoleId, CswPrimaryKey VisibilityUserId) { CswTableSelect ViewsTable = _CswNbtResources.makeCswTableSelect("CswNbtViewSelect_viewExists_select", "node_views"); string WhereClause = "where viewname = '" + CswTools.SafeSqlParam(ViewName) + "'"; if (Visibility == CswEnumNbtViewVisibility.Role) { WhereClause += " and visibility = 'Role' and roleid = " + VisibilityRoleId.PrimaryKey.ToString(); } else if (Visibility == CswEnumNbtViewVisibility.User) { WhereClause += " and visibility = 'User' and userid = " + VisibilityUserId.PrimaryKey.ToString(); } else { WhereClause += " and visibility = '" + Visibility.ToString() + "'"; } return(new CswNbtViewId(Convert.ToInt32(ViewsTable.getTable(new CswCommaDelimitedString("nodeviewid"), WhereClause).Rows[0]["nodeviewid"]))); }
/// <summary> /// Get a DataTable with a single view, by name and visibility /// </summary> public DataTable getView(string ViewName, CswEnumNbtViewVisibility Visibility, CswPrimaryKey VisibilityRoleId, CswPrimaryKey VisibilityUserId) { CswTableSelect ViewsTable = _CswNbtResources.makeCswTableSelect("CswNbtViewSelect_viewExists_select", "node_views"); string WhereClause = "where viewname = '" + CswTools.SafeSqlParam(ViewName) + "'"; if (Visibility == CswEnumNbtViewVisibility.Role) { WhereClause += " and visibility = 'Role' and roleid = " + VisibilityRoleId.PrimaryKey.ToString(); } else if (Visibility == CswEnumNbtViewVisibility.User) { WhereClause += " and visibility = 'User' and userid = " + VisibilityUserId.PrimaryKey.ToString(); } else { WhereClause += " and visibility = '" + Visibility.ToString() + "'"; } return(ViewsTable.getTable(WhereClause)); } // getViews()
public CswNbtMetaDataObjectClassProp getObjectClassProp( Int32 ObjectClassId, string ObjectClassPropName, CswDateTime Date = null ) { return (CswNbtMetaDataObjectClassProp) _CollImpl.getWhereFirst( "where objectclassid = " + ObjectClassId.ToString() + " and lower(propname) = '" + CswTools.SafeSqlParam( ObjectClassPropName.ToLower() ) + "'", Date ); }
public CswNbtMetaDataNodeType getNodeTypeLatestVersion(string NodeTypeName) { // Get any nodetype matching by name // (which is guaranteed to have the same version history as any other nodetype matching by name) // Then fetch the latest version of that nodetype return((CswNbtMetaDataNodeType)_CollImpl.getWhereFirst(@"where nodetypeid = (select max(nodetypeid) maxntid from nodetypes where firstversionid = (select firstversionid from nodetypes where lower(nodetypename) = '" + CswTools.SafeSqlParam(NodeTypeName.ToLower()) + "'))")); }
/// <summary> /// Get the first nodetype matching by name (which is guaranteed to have the same version history as any other nodetype matching by name). /// </summary> /// <param name="NodeTypeName">The name of the NodeType</param> /// <returns></returns> public CswNbtMetaDataNodeType getNodeTypeFirstVersion(string NodeTypeName) { return((CswNbtMetaDataNodeType)_CollImpl.getWhereFirst(@"where nodetypeid = (select firstversionid from nodetypes where lower(nodetypename) = '" + CswTools.SafeSqlParam(NodeTypeName.ToLower()) + "')")); }
private string _makeNodeSql() { string CurrentUserIdClause = string.Empty; if (null != _CswNbtResources.CurrentNbtUser && null != _CswNbtResources.CurrentNbtUser.UserId) { CurrentUserIdClause = " and f.userid = " + _CswNbtResources.CurrentNbtUser.UserId.PrimaryKey; } IEnumerable <string> SafeLikeClauses = _makeSafeLikeClauses(); string Query = string.Empty; if (SafeLikeClauses.Any()) { Query += @" with props as ( select p.nodetypeid, p.objectclasspropid, p.nodetypepropid, p.propname, f.fieldtype, nl.nodetypelayoutid, nl.display_row, op.propname as objectclasspropname from nodetype_props p join field_types f on p.fieldtypeid = f.fieldtypeid left outer join nodetype_layout nl on (nl.nodetypepropid = p.nodetypepropid and nl.layouttype = 'Table') left outer join object_class_props op on p.objectclasspropid = op.objectclasspropid where ( nl.nodetypelayoutid is not null or f.fieldtype in ('Image', 'MOL') or ( f.searchable = '1' and p.nodetypepropid in (select nodetypepropid from jct_nodes_props j "; Query += " where ( "; bool first = true; foreach (string SafeLikeClause in SafeLikeClauses) { if (false == first) { Query += " or "; } Query += " j.gestaltsearch " + SafeLikeClause + " "; first = false; } Query += @" ) ) ) ) ), jctnd as ( select jnp.nodeid, jnp.gestaltsearch from jct_nodes_props jnp join nodetype_props p on (jnp.nodetypepropid = p.nodetypepropid) join nodetypes t on (p.nodetypeid = t.nodetypeid) join field_types f on (p.fieldtypeid = f.fieldtypeid) where f.searchable = '1'"; if (false == _SingleNodetype) { Query += @" and t.searchdeferpropid is null"; Query += @" and t.searchable = '1'"; } Query += @" UNION select rn.nodeid, jnp.gestaltsearch from nodes n join jct_nodes_props jnp on jnp.nodeid = n.nodeid join nodetype_props p on (jnp.nodetypepropid = p.nodetypepropid) join field_types f on (p.fieldtypeid = f.fieldtypeid) join nodetypes t on t.nodetypeid = p.nodetypeid join nodetype_props r on t.searchdeferpropid = r.nodetypepropid join jct_nodes_props rj on (r.nodetypepropid = rj.nodetypepropid and rj.nodeid = n.nodeid) join nodes rn on rj.field1_fk = rn.nodeid where f.searchable = '1' ), pval as (select j.nodeid, op.propname, j.field1_fk from object_class_props op join nodetype_props p on op.objectclasspropid = p.objectclasspropid join jct_nodes_props j on j.nodetypepropid = p.nodetypepropid where op.propname in ('Location', 'Inventory Group', 'Report Group', 'Mail Report Group') ), permgrp as ( " + _makePermissionGroupSQL() + @" ), obsoleteprops as ( " + _makeMaterialObsoleteFilterSQL() + @" ), srch as ( select n.nodeid, n.relationalid, n.relationaltable, n.nodename, n.locked, nvl(n.iconfilename, t.iconfilename) iconfilename, t.nodetypename, t.nametemplate, t.nodetypeid, o.objectclass, o.objectclassid, lower(n.nodename) mssqlorder, props.nodetypepropid, props.objectclasspropid, props.propname, props.objectclasspropname, props.fieldtype, props.display_row, propval.jctnodepropid, propval.gestaltsearch as gestalt, propval.field1, propval.field2, propval.field1_fk, propval.field1_numeric, propval.hidden, propval.field1_big, i.permissiongroupid, f.userid, oprops.obsolete from nodes n join nodetypes t on (n.nodetypeid = t.nodetypeid) join object_class o on (t.objectclassid = o.objectclassid) left outer join favorites f on n.nodeid = f.itemid " + CurrentUserIdClause + @" left outer join permgrp i on (n.nodeid = i.nodeid) left outer join props on (props.nodetypeid = t.nodetypeid) left outer join obsoleteprops oprops on (oprops.nodeid = n.nodeid) left outer join jct_nodes_props propvaljoin on (props.nodetypepropid = propvaljoin.nodetypepropid and propvaljoin.nodeid = n.nodeid) left outer join jct_nodes_props propval on (propval.jctnodepropid = propvaljoin.jctnodepropid) where n.istemp = '0' "; //If we have access to disabled module MetaData, we should have access to their Nodes as well if (_CswNbtResources.MetaData.ExcludeDisabledModules) { Query += " and t.enabled = '1' "; } // case 31056 if (_OnlyMergeableNodeTypes) { Query += " and t.mergeable = '1' "; } // BZ 6008 if (!_IncludeSystemNodes) { Query += " and n.issystem = '0' "; } //case 27862 if (false == _IncludeHiddenNodes) { Query += " and n.hidden = '0' "; } Query += " and ( "; first = true; foreach (string SafeLikeClause in SafeLikeClauses) { if (false == first) { Query += " and "; } Query += " n.nodeid in (select nodeid from jctnd where gestaltsearch " + SafeLikeClause; if (CswTools.IsInteger(_SearchTerm)) { Query += @" union select " + _SearchTerm + " from dual"; } Query += " ) "; first = false; } Query += @" ) "; if (false == _SingleNodetype) { Query += @" and t.searchable = '1'"; Query += @" and t.searchdeferpropid is null"; } Query += @" and ( n.searchable = '1' or ( props.fieldtype = 'Barcode' and propval.field1 = '" + CswTools.SafeSqlParam(_SearchTerm) + @"' ) )"; // CIS-52280 Query += @" and ( oprops.obsolete is null or oprops.obsolete = '0' or oprops.obsolete = 'N')"; Query += _ExtraWhereClause; // Case 31351: Exclude specific nodes if (_ExcludeNodeIds.Count > 0) { Query += " and n.nodeid not in (" + _ExcludeNodeIds.ToString() + ") "; } Query += @" ) select * from srch"; // Handle result limits by looking at unique nodeids in the results Query += @" where srch.nodeid in ( select nodeid from ( select nodeid, rownum as rnum from (select distinct nodeid from srch)) where rnum <= " + _CswNbtResources.TreeViewResultLimit + @") order by srch.userid, lower(srch.nodename), srch.nodeid, lower(srch.propname), srch.display_row "; } return(Query); } //_makeNodeSql()
public void threadCallBack(ICswResources CswResources) { _LogicRunStatus = CswEnumScheduleLogicRunStatus.Running; if (CswEnumScheduleLogicRunStatus.Stopping != _LogicRunStatus) { CswNbtResources _CswNbtResources = (CswNbtResources)CswResources; try { const Int32 MaxRowsToProcessPerThread = 1000; //This number must not exceed the amount of importing we expect to be able to do in _CswScheduleLogicDetail.MaxRunTimeMs const Int32 MinNumberToProcess = 100; //CIS-53123 - we should process at least this many rows at a time (because committing is expensive) - this number must not exceed 1000 (because we're using an IN clause) Int32 NumberToProcess = CswConvert.ToInt32(_CswNbtResources.ConfigVbls.getConfigVariableValue(CswEnumConfigurationVariableNames.NodesProcessedPerCycle)); NumberToProcess = NumberToProcess < MinNumberToProcess ? MinNumberToProcess : NumberToProcess; Int32 NumberOfCommits = MaxRowsToProcessPerThread / NumberToProcess;//CIS-53123 - process MaxRowsToProcessPerThread rows per rule iteration, committing every NumberToProcess rows const string QueueTableName = "nbtimportqueue"; const string QueuePkName = "nbtimportqueueid"; for (int c = 0; c < NumberOfCommits; c++) { string Sql = "select nbtimportqueueid, state, itempk, pkcolumnname, sheetname, priority, importorder, tablename, coalesce(viewname, tablename) as sourcename, nodetypename from " + QueueTableName + "@" + CAFDbLink + " iq" + " join " + CswNbtImportTables.ImportDefOrder.TableName + " io on ( coalesce(viewname, tablename) = iq.sheetname )" //TODO - remove ignoring Delete rows when we support Importing deleted nodes + " where state != '" + State.E + "' and state != '" + State.D //Much faster than (I or U) + "' order by decode (state, '" + State.I + "', 1, '" + State.U + "', 2) asc, priority desc, importorder asc, nbtimportqueueid asc"; CswArbitrarySelect QueueSelect = _CswNbtResources.makeCswArbitrarySelect("cafimport_queue_select", Sql); DataTable QueueTable = QueueSelect.getTable(0, NumberToProcess, false); if (QueueTable.Rows.Count > 0) { Collection <String> ImportQueuePKs = new Collection <string>(); CswCommaDelimitedString ItemPKs = new CswCommaDelimitedString(); string ImportOrder = QueueTable.Rows[0]["importorder"].ToString(); DataRow QueueRowDef = QueueTable.Rows[0]; CswNbtImporter Importer = new CswNbtImporter(_CswNbtResources.AccessId, CswEnumSetupMode.NbtExe); foreach (DataRow QueueRow in QueueTable.Rows) { string CurrentTblNamePkCol = CswConvert.ToString(QueueRow["pkcolumnname"]); if (string.IsNullOrEmpty(CurrentTblNamePkCol)) { throw new Exception("Could not find pkcolumn in data_dictionary for table " + QueueRow["tablename"]); } if (QueueRow["importorder"].ToString() != ImportOrder) { break;//We've changed NodeTypes - we'll pick them up next time around } ImportQueuePKs.Add(QueueRow[QueuePkName].ToString()); ItemPKs.Add("'" + QueueRow["itempk"] + "'"); } string ItemSql = "select * from " + QueueRowDef["sourcename"] + "@" + CAFDbLink + " where " + QueueRowDef["pkcolumnname"] + " in(" + ItemPKs + ")"; CswArbitrarySelect ItemSelect = _CswNbtResources.makeCswArbitrarySelect("cafimport_queue_select", ItemSql); DataTable ItemTable = ItemSelect.getTable(); for (int i = 0; i < ItemTable.Rows.Count; i++) { DataRow ItemRow = ItemTable.Rows[i]; string NodetypeName = QueueRowDef["nodetypename"].ToString(); bool Overwrite = QueueRowDef["state"].ToString().Equals("U"); string Error = Importer.ImportRow(ItemRow, DefinitionName, NodetypeName, Overwrite); if (string.IsNullOrEmpty(Error)) { // record success - delete the record _CswNbtResources.execArbitraryPlatformNeutralSql("delete from " + QueueTableName + "@" + CAFDbLink + " where " + QueuePkName + " = " + ImportQueuePKs[i]); } else { // truncate error to 2000 chars string SafeError = CswTools.SafeSqlParam(Error); if (SafeError.Length > 2000) { SafeError = SafeError.Substring(0, 2000); } // record failure - record the error on nbtimportqueue _CswNbtResources.execArbitraryPlatformNeutralSql("update " + QueueTableName + "@" + CAFDbLink + " set state = '" + State.E + "', " + " errorlog = '" + SafeError + "' " + " where " + QueuePkName + " = " + ImportQueuePKs[i]); } _CswScheduleLogicDetail.LoadCount = _CswScheduleLogicDetail.LoadCount - 1; } Importer.Finish(); //CIS-53123 - Commit every NumberToProcess rows (performes better than spawning a new thread every NumberToProcess rows) _CswNbtResources.finalize(); } else { c = NumberOfCommits;//We're done importing! } } _CswScheduleLogicDetail.StatusMessage = "Completed without error"; _LogicRunStatus = CswEnumScheduleLogicRunStatus.Succeeded; //last line }//try catch (Exception Exception) { _CswScheduleLogicDetail.StatusMessage = "CswScheduleLogicNbtCAFImport::ImportItems() exception: " + Exception.Message + "; " + Exception.StackTrace; _CswNbtResources.logError(new CswDniException(_CswScheduleLogicDetail.StatusMessage)); _LogicRunStatus = CswEnumScheduleLogicRunStatus.Failed; } //catch } //if we're not shutting down } //threadCallBack()
} // loadRelationshipRecursive() private CswArbitrarySelect _makeNodeSql(CswNbtViewRelationship Relationship, IEnumerable <CswPrimaryKey> ParentNodeIds = null) { string CurrentUserIdClause = string.Empty; if (null != _CswNbtResources.CurrentNbtUser && null != _CswNbtResources.CurrentNbtUser.UserId) { CurrentUserIdClause = " and f.userid = " + _CswNbtResources.CurrentNbtUser.UserId.PrimaryKey; } CswCommaDelimitedString With = new CswCommaDelimitedString(); string Select = @"select n.nodeid, n.relationalid, n.relationaltable, n.nodename, n.locked, nvl(n.iconfilename, t.iconfilename) iconfilename, t.nodetypename, t.nametemplate, t.nodetypeid, o.objectclass, o.objectclassid, f.userid"; string From = @"from nodes n left join favorites f on n.nodeid = f.itemid " + CurrentUserIdClause + @" join nodetypes t on (n.nodetypeid = t.nodetypeid) join object_class o on (t.objectclassid = o.objectclassid) "; string OrderBy = string.Empty; // Nodetype/Object Class filter string Where; if (Relationship.SecondType == CswEnumNbtViewRelatedIdType.NodeTypeId) { Where = " where (t.firstversionid = " + Relationship.SecondId + ") "; } else if (Relationship.SecondType == CswEnumNbtViewRelatedIdType.ObjectClassId) { Where = " where (o.objectclassid = " + Relationship.SecondId + ") "; } else if (Relationship.SecondType == CswEnumNbtViewRelatedIdType.PropertySetId) { From += @" join jct_propertyset_objectclass jpo on (o.objectclassid = jpo.objectclassid) join property_set ps on (jpo.propertysetid = ps.propertysetid) "; Where = " where (ps.propertysetid = " + Relationship.SecondId + ") "; } else { throw new CswDniException(CswEnumErrorType.Error, "Invalid View", "CswNbtTreeLoaderFromXmlViewByLevel got a relationship with an unrecognized SecondType: " + Relationship.SecondType.ToString()); } //If we have access to disabled module MetaData, we should have access to their Nodes as well if (_CswNbtResources.MetaData.ExcludeDisabledModules) { // case 26029 Where += " and t.enabled = '1' "; } // Parent Node if (Relationship.PropId != Int32.MinValue && null != ParentNodeIds) { bool first = true; string parentsWith = "parents as ("; foreach (Int32 ParentNodeId in ParentNodeIds.Select(key => key.PrimaryKey)) { if (first) { first = false; } else { parentsWith += " union "; } parentsWith += "select " + ParentNodeId + " nodeid from dual "; } parentsWith += ")"; With.Add(parentsWith); Select += ",parent.parentnodeid "; if (Relationship.PropOwner == CswEnumNbtViewPropOwnerType.First) { From += @" join (select jnp.nodeid parentnodeid, jnp.field1_fk thisnodeid from jct_nodes_props jnp join nodetype_props p on (jnp.nodetypepropid = p.nodetypepropid) "; if (Relationship.PropType == CswEnumNbtViewPropIdType.NodeTypePropId) { From += @" where p.firstpropversionid = " + Relationship.PropId; } else { From += @" join object_class_props op on (p.objectclasspropid = op.objectclasspropid) where op.objectclasspropid = " + Relationship.PropId; } From += @" ) parent on (parent.thisnodeid = n.nodeid)"; } else { From += @" join (select jnp.nodeid thisnodeid, jnp.field1_fk parentnodeid from jct_nodes_props jnp join nodetype_props p on (jnp.nodetypepropid = p.nodetypepropid) "; if (Relationship.PropType == CswEnumNbtViewPropIdType.NodeTypePropId) { From += @" where p.firstpropversionid = " + Relationship.PropId; } else { From += @" join object_class_props op on (p.objectclasspropid = op.objectclasspropid) where op.objectclasspropid = " + Relationship.PropId; } From += @" ) parent on (parent.thisnodeid = n.nodeid)"; } From += " join parents on (parent.parentnodeid = parents.nodeid) "; } // if( Relationship.PropId != Int32.MinValue ) CswCommaDelimitedString OrderByProps = new CswCommaDelimitedString(); OrderByProps.Add("f.userid"); // Grouping if (Relationship.GroupByPropId != Int32.MinValue) { CswNbtSubField GroupBySubField = _getDefaultSubFieldForProperty(Relationship.GroupByPropType, Relationship.GroupByPropId); Select += " ,g." + GroupBySubField.Column + " groupname"; OrderByProps.Add("g." + GroupBySubField.Column); if (Relationship.GroupByPropType == CswEnumNbtViewPropIdType.ObjectClassPropId) { From += @" left outer join (select j.nodeid, " + GroupBySubField.Column + @" from jct_nodes_props j join nodetype_props p on j.nodetypepropid = p.nodetypepropid where p.objectclasspropid = " + Relationship.GroupByPropId.ToString() + @") g on (g.nodeid = n.nodeid)"; } else { From += @" left outer join (select j.nodeid, " + GroupBySubField.Column + @" from jct_nodes_props j where j.nodetypepropid = " + Relationship.GroupByPropId.ToString() + @") g on (g.nodeid = n.nodeid)"; } } // if( Relationship.GroupByPropId != Int32.MinValue ) // Handle sort order Int32 sortAlias = 0; String OrderByString = String.Empty; foreach (CswNbtViewProperty Prop in Relationship.Properties) { if (Prop.SortBy) { // Case 10530 sortAlias++; if (null != Prop.MetaDataProp) { CswEnumNbtPropColumn SubFieldColumn = Prop.MetaDataProp.getFieldTypeRule().SubFields.Default.Column; string aliasName = "mssqlorder" + sortAlias; Select += ",(select "; if (SubFieldColumn == CswEnumNbtPropColumn.Field1_Numeric || SubFieldColumn == CswEnumNbtPropColumn.Field1_Date || SubFieldColumn == CswEnumNbtPropColumn.Field2_Numeric || SubFieldColumn == CswEnumNbtPropColumn.Field2_Date) { Select += SubFieldColumn.ToString(); } else { Select += "lower(" + SubFieldColumn.ToString() + ")"; } Select += " from jct_nodes_props where nodeid = n.nodeid and "; if (Prop.Type == CswEnumNbtViewPropType.NodeTypePropId) { Select += "nodetypepropid = " + Prop.NodeTypePropId; } else { Select += "nodetypepropid in (select nodetypepropid from nodetype_props where objectclasspropid = " + Prop.ObjectClassPropId + ")"; } Select += ") as " + aliasName; // Case 10533 if (SubFieldColumn == CswEnumNbtPropColumn.Gestalt || SubFieldColumn == CswEnumNbtPropColumn.ClobData) { OrderByString = "lower(to_char(" + aliasName + "))"; } else if (SubFieldColumn == CswEnumNbtPropColumn.Field1_Numeric || SubFieldColumn == CswEnumNbtPropColumn.Field1_Date || SubFieldColumn == CswEnumNbtPropColumn.Field2_Numeric || SubFieldColumn == CswEnumNbtPropColumn.Field2_Date) { OrderByString = aliasName; } else { OrderByString = "lower(" + aliasName + ")"; } if (Prop.SortMethod == CswEnumNbtViewPropertySortMethod.Descending) { OrderByString += " desc"; } Int32 OrderByOrder = Prop.Order; if (OrderByOrder != 0 && (OrderByProps.Count <= OrderByOrder || OrderByOrder < 0)) { if (OrderByProps.Count == 0) { OrderByOrder = 0; } else { OrderByOrder = OrderByProps.Count - 1; } } OrderByProps.Insert(OrderByOrder, OrderByString); } } // if( Prop.SortBy ) } // foreach( CswNbtViewProperty Prop in Relationship.Properties ) // case 29193: always fall back on name sort sortAlias++; Select += ",lower(n.nodename) mssqlorder" + sortAlias; OrderByProps.Add("lower(n.nodename)"); OrderBy = " order by " + OrderByProps.ToString() + " "; // for property multiplexing OrderBy += ",n.nodeid"; if (Relationship.PropId != Int32.MinValue && null != ParentNodeIds) { OrderBy += ",parent.parentnodeid "; } // Properties for Select if (Relationship.Properties.Count > 0) { CswCommaDelimitedString NTPropsInClause = new CswCommaDelimitedString(0, "'"); CswCommaDelimitedString OCPropsInClause = new CswCommaDelimitedString(0, "'"); foreach (CswNbtViewProperty Prop in Relationship.Properties) { if (Prop.Type == CswEnumNbtViewPropType.NodeTypePropId && Prop.NodeTypePropId != Int32.MinValue) { NTPropsInClause.Add(Prop.NodeTypePropId.ToString()); } else if (Prop.ObjectClassPropId != Int32.MinValue) { OCPropsInClause.Add(Prop.ObjectClassPropId.ToString()); } } // This will multiplex the results by the number of properties! if (NTPropsInClause.Count > 0 || OCPropsInClause.Count > 0) { // Properties // We match on propname because that's how the view editor works. Select += @" ,props.nodetypepropid, props.objectclasspropid, props.propname, props.objectclasspropname, props.fieldtype "; string propsWith = "props as ("; if (NTPropsInClause.Count > 0) { propsWith += @" select p2.nodetypeid, p2.nodetypepropid, p2.objectclasspropid, p2.propname, f.fieldtype, ocp2.propname as objectclasspropname from nodetype_props p1 join nodetype_props p2 on (p2.firstpropversionid = p1.firstpropversionid or p1.propname = p2.propname) left outer join object_class_props ocp2 on p2.objectclasspropid = ocp2.objectclasspropid join field_types f on f.fieldtypeid = p2.fieldtypeid where p1.nodetypepropid in (" + NTPropsInClause.ToString() + @")"; if (OCPropsInClause.Count > 0) { propsWith += @" UNION ALL "; } } if (OCPropsInClause.Count > 0) { propsWith += @" select ntp.nodetypeid, ntp.nodetypepropid, ntp.objectclasspropid, ntp.propname, f.fieldtype, op.propname as objectclasspropname from object_class_props op join nodetype_props ntp on (ntp.objectclasspropid = op.objectclasspropid or ntp.propname = op.propname) join field_types f on f.fieldtypeid = ntp.fieldtypeid where op.objectclasspropid in (" + OCPropsInClause.ToString() + @")"; } propsWith += @" )"; With.Add(propsWith); From += " left outer join props on (props.nodetypeid = t.nodetypeid)"; // intentional multiplexing // Property Values Select += @" ,propval.jctnodepropid, propval.gestalt, propval.field1, propval.field2, propval.field1_fk, propval.field1_numeric, propval.hidden, propval.field1_big "; From += @" left outer join jct_nodes_props propvaljoin on (props.nodetypepropid = propvaljoin.nodetypepropid and propvaljoin.nodeid = n.nodeid) "; // better performance from indexes if we do this first From += @" left outer join jct_nodes_props propval on (propval.jctnodepropid = propvaljoin.jctnodepropid) "; } // if( NTPropsInClause.Count > 0 || OCPropsInClause.Count > 0 ) } // if(Relationship.Properties.Count > 0) // Property Filters Int32 FilterCount = 0; string FilterWhere = string.Empty; Dictionary <string, string> FilterParameters = new Dictionary <string, string>(); foreach (CswNbtViewProperty Prop in Relationship.Properties) { foreach (CswNbtViewPropertyFilter Filter in Prop.Filters) { if (Filter.FilterMode == CswEnumNbtFilterMode.Null || Filter.FilterMode == CswEnumNbtFilterMode.NotNull || Filter.Value != string.Empty) { FilterCount += 1; ICswNbtFieldTypeRule FilterFieldTypeRule = _CswNbtResources.MetaData.getFieldTypeRule(Prop.FieldType); string FilterValue = string.Empty; if (null != FilterFieldTypeRule) { FilterValue = FilterFieldTypeRule.renderViewPropFilter(_RunAsUser, Filter, FilterParameters, FilterCount); } if (false == string.IsNullOrEmpty(FilterValue)) { CswNbtSubField FilterSubField = FilterFieldTypeRule.SubFields[Filter.SubfieldName]; //if( FilterSubField.RelationalTable == string.Empty ) //{ string FilterClause = string.Empty; // @"select z.nodeid, '1' as included from nodes z where "; if (Filter.FilterMode == CswEnumNbtFilterMode.Null || Filter.FilterMode == CswEnumNbtFilterMode.NotEquals || Filter.FilterMode == CswEnumNbtFilterMode.NotContains) { FilterClause += @"select z.nodeid, '1' as included from nodes z "; if (Relationship.SecondType == CswEnumNbtViewRelatedIdType.NodeTypeId) { FilterClause += @" join nodetypes t on z.nodetypeid = t.nodetypeid where (t.firstversionid = :filt" + FilterCount + "relid) "; } else if (Relationship.SecondType == CswEnumNbtViewRelatedIdType.ObjectClassId) { FilterClause += @" join nodetypes t on z.nodetypeid = t.nodetypeid join object_class o on t.objectclassid = o.objectclassid where (o.objectclassid = :filt" + FilterCount + "relid) "; } else if (Relationship.SecondType == CswEnumNbtViewRelatedIdType.PropertySetId) { FilterClause += @" join nodetypes t on z.nodetypeid = t.nodetypeid join object_class o on t.objectclassid = o.objectclassid join jct_propertyset_objectclass jpo on (o.objectclassid = jpo.objectclassid) join property_set ps on (jpo.propertysetid = ps.propertysetid) where (ps.propertysetid = :filt" + FilterCount + "relid) "; } FilterClause += @" and (z.nodeid not in ( select jnp.nodeid from jct_nodes_props jnp join nodetype_props p on (jnp.nodetypepropid = p.nodetypepropid) "; if (Prop.Type == CswEnumNbtViewPropType.NodeTypePropId) { FilterClause += @" where (lower(p.propname) = :filt" + FilterCount + @"ntpname)) "; } else { FilterClause += @" join object_class_props op on (p.objectclasspropid = op.objectclasspropid) where op.objectclasspropid = :filt" + FilterCount + @"ocpid)"; } FilterClause += @" or z.nodeid in (select n.nodeid from nodes n "; } else { FilterClause += @"select n.nodeid, '1' as included from nodes n "; } if (Prop.Type == CswEnumNbtViewPropType.NodeTypePropId) { FilterClause += @" join nodetype_props p on (lower(p.propname) = :filt" + FilterCount + @"ntpname) "; FilterParameters.Add("filt" + FilterCount + "ntpname", CswTools.SafeSqlParam(Prop.NodeTypeProp.PropName.ToLower())); } else { FilterClause += @" join object_class_props op on (op.objectclasspropid = :filt" + FilterCount + @"ocpid) join nodetype_props p on (p.objectclasspropid = op.objectclasspropid) "; FilterParameters.Add("filt" + FilterCount + "ocpid", Prop.ObjectClassPropId.ToString()); } FilterClause += @" join jct_nodes_props jnp on (jnp.nodeid = n.nodeid and jnp.nodetypepropid = p.nodetypepropid) "; FilterParameters.Add("filt" + FilterCount + "relid", Relationship.SecondId.ToString()); if (Relationship.SecondType == CswEnumNbtViewRelatedIdType.NodeTypeId) { FilterClause += @" join nodetypes t on n.nodetypeid = t.nodetypeid where (t.firstversionid = :filt" + FilterCount + "relid) "; } else if (Relationship.SecondType == CswEnumNbtViewRelatedIdType.ObjectClassId) { FilterClause += @" join nodetypes t on n.nodetypeid = t.nodetypeid join object_class o on t.objectclassid = o.objectclassid where (o.objectclassid = :filt" + FilterCount + "relid) "; } else if (Relationship.SecondType == CswEnumNbtViewRelatedIdType.PropertySetId) { FilterClause += @" join nodetypes t on n.nodetypeid = t.nodetypeid join object_class o on t.objectclassid = o.objectclassid join jct_propertyset_objectclass jpo on (o.objectclassid = jpo.objectclassid) join property_set ps on (jpo.propertysetid = ps.propertysetid) where (ps.propertysetid = :filt" + FilterCount + "relid) "; } FilterClause += @" and " + FilterValue + @""; if (Filter.FilterMode == CswEnumNbtFilterMode.Null || Filter.FilterMode == CswEnumNbtFilterMode.NotEquals || Filter.FilterMode == CswEnumNbtFilterMode.NotContains) { FilterClause += "))"; } With.Add("filt" + FilterCount.ToString() + " as (" + FilterClause + ")"); From += "left outer join filt" + FilterCount.ToString() + " f" + FilterCount.ToString() + " on (f" + FilterCount.ToString() + ".nodeid = n.nodeid)"; if (Filter.ResultMode == CswEnumNbtFilterResultMode.Disabled) { Select += ",f" + FilterCount.ToString() + ".included as included" + Filter.Conjunction.ToString() + FilterCount.ToString(); } if (Filter.ResultMode == CswEnumNbtFilterResultMode.Hide) { if (FilterWhere != string.Empty) { FilterWhere += Filter.Conjunction.ToString().ToLower(); } FilterWhere += " f" + FilterCount.ToString() + ".included = '1' "; } //} // if( FilterSubField.RelationalTable == string.empty ) //else if( false == string.IsNullOrEmpty( FilterValue ) ) //{ // FilterWhere += Filter.Conjunction.ToString().ToLower() + " " + FilterValue; //} } // if we really have a filter } // if we have a filter } // foreach( CswNbtViewPropertyFilter Filter in Prop.Filters ) } // foreach( CswNbtViewProperty Prop in Relationship.Properties ) if (FilterWhere != string.Empty) { Where += "and (" + FilterWhere + ")"; } if (Relationship.NodeIdsToFilterOut.Count > 0) { string inclause = ""; bool first = true; foreach (CswPrimaryKey NodeId in Relationship.NodeIdsToFilterOut) { if (NodeId != null) { if (first) { first = false; } else { inclause += ","; } inclause += NodeId.PrimaryKey.ToString(); } } if (inclause != string.Empty) { Where += " and n.nodeid not in ( " + inclause + " ) "; } } if (Relationship.NodeIdsToFilterIn.Count > 0) { string inclause = ""; bool first = true; foreach (CswPrimaryKey NodeId in Relationship.NodeIdsToFilterIn) { if (NodeId != null) { if (first) { first = false; } else { inclause += ","; } inclause += NodeId.PrimaryKey.ToString(); } } if (inclause != string.Empty) { Where += " and n.nodeid in ( " + inclause + " ) "; } } // BZ 6008 if (false == _IncludeSystemNodes) { Where += " and n.issystem = '0' "; } if (false == _IncludeHiddenNodes) { Where += " and n.hidden = '0' "; } if (false == _IncludeTempNodes) { Where += " and n.istemp= '0' "; } string Sql = string.Empty; if (With.Count > 0) { Sql = "with " + With.ToString(false); } Sql += " " + Select + " " + From + " " + Where + " " + OrderBy; CswArbitrarySelect Ret = _CswNbtResources.makeCswArbitrarySelect("TreeLoader_select", Sql); foreach (string Parameter in FilterParameters.Keys) { Ret.addParameter(Parameter, FilterParameters[Parameter]); } return(Ret); } //_makeNodeSql()
public CswNbtMetaDataNodeTypeTab getNodeTypeTab( Int32 NodeTypeId, string NodeTypeTabName, bool BypassModuleCheck = false ) { return (CswNbtMetaDataNodeTypeTab) _CollImpl.getWhereFirst( "where nodetypeid = " + NodeTypeId.ToString() + " and lower(tabname) = '" + CswTools.SafeSqlParam( NodeTypeTabName.ToLower() ) + "'", null, BypassModuleCheck ); }
public Int32 getNodeTypePropIdByObjectClassProp( Int32 NodeTypeId, string ObjectClassPropName ) { return _CollImpl.getPksFirst( "where nodetypeid = " + NodeTypeId.ToString() + " and objectclasspropid in (select objectclasspropid from object_class_props where lower(propname) = '" + CswTools.SafeSqlParam( ObjectClassPropName.ToLower() ) + "')" ); }
public Int32 getNodeTypePropId( Int32 NodeTypeId, string PropName ) { return _CollImpl.getPksFirst( "where nodetypeid = " + NodeTypeId.ToString() + " and lower(propname) = '" + CswTools.SafeSqlParam( PropName.ToLower() ) + "'" ); }
public CswNbtMetaDataNodeTypeProp getNodeTypeProp( Int32 NodeTypeId, string NodeTypePropName, CswDateTime Date = null ) { return (CswNbtMetaDataNodeTypeProp) _CollImpl.getWhereFirst( "where nodetypeid = " + NodeTypeId.ToString() + " and lower(propname) = '" + CswTools.SafeSqlParam( NodeTypePropName.ToLower() ) + "'", Date ); }
/// <summary> /// Compares this prop's Text value with all existing values that share the same NodeTypePropId and changes it until it becomes unqiue. /// </summary> public void makeUnique() { bool isUnique = false; CswTableSelect JctNodePropSelect = _CswNbtResources.makeCswTableSelect(NodeTypePropId + "_matching select", "jct_nodes_props"); int PropNum = 2; string NewText = Text; while (false == isUnique) { DataTable MatchingPropsTable = JctNodePropSelect.getTable("where nodetypepropid = " + NodeTypePropId + " " + " and nodeid <> " + this.NodeId.PrimaryKey + " and lower(" + _TextSubField.Column + ") = '" + CswTools.SafeSqlParam(NewText).ToLower() + "'"); if (MatchingPropsTable.Rows.Count > 0) { NewText = Text + " " + PropNum; PropNum++; } else { isUnique = true; } } Text = NewText; } // makeUnique()