/// <summary> /// Resets next sequence value based on newest entry and existing values in the database. /// </summary> public void reSync(CswEnumNbtPropColumn Column, Int32 NewSeqVal) { string SelectText = @"with seqntpids as (select nodetypepropid from nodetype_props where sequenceid in (select sequenceid from sequences where sequenceid = :sequenceid) ) select max(" + Column + @") as seqval from jct_nodes_props where nodetypepropid in (select nodetypepropid from seqntpids)"; CswArbitrarySelect SeqValueSelect = _CswNbtResources.makeCswArbitrarySelect("syncSequence_maxvalue_select", SelectText); SeqValueSelect.addParameter("sequenceid", SequenceId.ToString()); DataTable SeqValueTable = SeqValueSelect.getTable(); Int32 MaxSeqVal = NewSeqVal; if (SeqValueTable.Rows.Count > 0) { Int32 ThisSeqVal = CswConvert.ToInt32(SeqValueTable.Rows[0]["seqval"]); if (ThisSeqVal > MaxSeqVal) { MaxSeqVal = ThisSeqVal; } } _CswNbtResources.resetUniqueSequenceValForProperty(getDbName(), MaxSeqVal + 1); } // reSync()
//mark any property references to this property on other nodes as pending update private void _markExternalPropRefsDirty(CswNbtNodePropWrapper CurrentProp) { if (CswTools.IsPrimaryKey(CurrentProp.NodeId)) { CswNbtFieldTypeRulePropertyReference PropRefFTR = (CswNbtFieldTypeRulePropertyReference)_CswNbtResources.MetaData.getFieldTypeRule(CswEnumNbtFieldType.PropertyReference); CswEnumNbtPropColumn PropRefColumn = PropRefFTR.CachedValueSubField.Column; string SQL = @"update jct_nodes_props set pendingupdate = '" + CswConvert.ToDbVal(true) + @"', " + PropRefColumn.ToString() + @" = '' where jctnodepropid in (select j.jctnodepropid from jct_nodes_props j join nodes n on n.nodeid = j.nodeid join nodetype_props p on p.nodetypepropid = j.nodetypepropid join field_types f on p.fieldtypeid = f.fieldtypeid left outer join jct_nodes_props jntp on (jntp.nodetypepropid = p.fkvalue and jntp.nodeid = n.nodeid and jntp.field1_fk = " + CurrentProp.NodeId.PrimaryKey.ToString() + @") left outer join (select jx.jctnodepropid, ox.objectclasspropid, jx.nodeid from jct_nodes_props jx join nodetype_props px on jx.nodetypepropid = px.nodetypepropid join object_class_props ox on px.objectclasspropid = ox.objectclasspropid where jx.field1_fk = " + CurrentProp.NodeId.PrimaryKey.ToString() + @") jocp on (jocp.objectclasspropid = p.fkvalue and jocp.nodeid = n.nodeid) where f.fieldtype = 'PropertyReference' and ((lower(p.fktype) = 'nodetypepropid' and jntp.jctnodepropid is not null) or (lower(p.fktype) = 'objectclasspropid' and jocp.jctnodepropid is not null)) and ((lower(p.valueproptype) = 'nodetypepropid' and p.valuepropid = " + CurrentProp.NodeTypePropId.ToString() + @") or (lower(p.valueproptype) = 'objectclasspropid' and p.valuepropid = " + CurrentProp.ObjectClassPropId + @")))"; // We're not doing this in a CswTableUpdate because it might be a large operation, // and we don't care about auditing for this change. _CswNbtResources.execArbitraryPlatformNeutralSql(SQL); } }
public string this[CswEnumNbtPropColumn column] { get { string ret = string.Empty; if (CswEnumNbtPropColumn.Field1 == column) { ret = this.Field1; } else if (CswEnumNbtPropColumn.Field1_FK == column) { ret = this.Field1_Fk.ToString(); } else if (CswEnumNbtPropColumn.Field1_Numeric == column) { ret = this.Field1_Numeric.ToString(); } else if (CswEnumNbtPropColumn.Field2 == column) { ret = this.Field2; } else if (CswEnumNbtPropColumn.Gestalt == column) { ret = this.Gestalt; } else if (CswEnumNbtPropColumn.Field1_Big == column) { ret = this.Field1_Big; } return(ret); } } // this[]
/// <summary> /// Returns the original value of the provided subfield for this property /// </summary> public string GetOriginalPropRowValue(CswEnumNbtSubFieldName SubfieldName) { string ret = string.Empty; ICswNbtFieldTypeRule FieldTypeRule = _CswNbtMetaDataNodeTypeProp.getFieldTypeRule(); if (FieldTypeRule != null) { CswEnumNbtPropColumn Column = FieldTypeRule.SubFields[SubfieldName].Column; ret = GetOriginalPropRowValue(Column); } return(ret); }
private string _getRowStringVal(CswEnumNbtPropColumn ColumnName) { string ReturnVal = ""; if (_PropRow != null) { if (_PropsTable.Columns.Contains(ColumnName.ToString()) && !_PropRow.IsNull(ColumnName.ToString())) { ReturnVal = _PropRow[ColumnName.ToString()].ToString(); } } return(ReturnVal); }
private bool _getRowBoolVal(CswEnumNbtPropColumn ColumnName) { bool ReturnVal = false; if (_PropRow != null) { if (_PropsTable.Columns.Contains(ColumnName.ToString()) && !_PropRow.IsNull(ColumnName.ToString())) { ReturnVal = CswConvert.ToBoolean(_PropRow[ColumnName.ToString()]); } } return(ReturnVal); }
private DateTime _getRowDateVal(CswEnumNbtPropColumn ColumnName) { DateTime ReturnVal = DateTime.MinValue; if (_PropRow != null) { if (_PropsTable.Columns.Contains(ColumnName.ToString()) && !_PropRow.IsNull(ColumnName.ToString())) { ReturnVal = CswConvert.ToDateTime(_PropRow[ColumnName.ToString()]); } } return(ReturnVal); }
private Int32 _getRowIntVal(CswEnumNbtPropColumn ColumnName) { Int32 ReturnVal = Int32.MinValue; if (_PropRow != null) { if (_PropsTable.Columns.Contains(ColumnName.ToString()) && !_PropRow.IsNull(ColumnName.ToString())) { ReturnVal = CswConvert.ToInt32(_PropRow[ColumnName.ToString()]); } } return(ReturnVal); }
private Double _getRowDoubleVal(CswEnumNbtPropColumn ColumnName) { Double ReturnVal = Double.NaN; if (_PropRow != null) { if (_PropsTable.Columns.Contains(ColumnName.ToString()) && !_PropRow.IsNull(ColumnName.ToString())) { ReturnVal = CswConvert.ToDouble(_PropRow[ColumnName.ToString()]); } } return(ReturnVal); }
/// <summary> /// Sets the value of a column for a property /// value should be in native format -- this function will convert to db format /// </summary> /// <param name="column">Target column</param> /// <param name="value">New value</param> /// <returns>True if any changes were made</returns> public bool SetPropRowValue(CswEnumNbtSubFieldName SubFieldName, CswEnumNbtPropColumn column, object value, bool IsNonModifying = false) { bool ret = false; object dbval; if (value is string) { dbval = CswConvert.ToDbVal(CswConvert.ToString(value).Trim()); } else { dbval = CswConvert.ToDbVal(value); } if (_PropRow == null && dbval != DBNull.Value) { makePropRow(); ret = true; } if (_PropRow != null) { if (_NodeId != null) { _PropRow["nodeid"] = CswConvert.ToDbVal(_NodeId.PrimaryKey); _PropRow["nodeidtablename"] = _NodeId.TableName; } if (null != _NodeTypeProp) { _PropRow["nodetypepropid"] = CswConvert.ToDbVal(_NodeTypeProp.PropId); } _PropRow["objectclasspropid"] = CswConvert.ToDbVal(_ObjectClassPropId); if (false == (CswConvert.ToDbVal(_PropRow[column.ToString()]).Equals(dbval))) { _PropRow[column.ToString()] = CswConvert.ToDbVal(value); if (false == IsNonModifying && SubFieldName != CswEnumNbtSubFieldName.Unknown) { setSubFieldModified(SubFieldName); } ret = true; } } // Don't just return WasModified, or else changes to one subfield // will look like changes to another subfield return(ret); }
public string GetOriginalPropRowValue(CswEnumNbtPropColumn Column) { // see case 22613 string ret = string.Empty; if (null != _PropRow) { try { ret = _PropRow[Column.ToString(), DataRowVersion.Original].ToString(); } catch (System.Data.VersionNotFoundException) { ret = _PropRow[Column.ToString()].ToString(); } } return(ret); }
public DateTime GetPropRowValueDate(CswEnumNbtPropColumn Column) { DateTime ret = DateTime.MinValue; if (Column == CswEnumNbtPropColumn.Field1_Date) { ret = Field1_Date; } else if (Column == CswEnumNbtPropColumn.Field2_Date) { ret = Field2_Date; } else { throw new CswDniException(CswEnumErrorType.Error, "Invalid PropColumn", "CswNbtNodePropData.GetPropRowValueDate() found an unhandled PropColumn: " + Column.ToString()); } return(ret); } // GetPropRowValueDate()
public string GetPropRowValue(CswEnumNbtPropColumn Column) { string ret = string.Empty; if (Column == CswEnumNbtPropColumn.Field1) { ret = Field1; } else if (Column == CswEnumNbtPropColumn.Field1_FK) { ret = CswConvert.ToString(Field1_Fk); } else if (Column == CswEnumNbtPropColumn.Field1_Numeric) { ret = CswConvert.ToString(Field1_Numeric); } else if (Column == CswEnumNbtPropColumn.Field1_Date) { ret = CswConvert.ToString(Field1_Date); } else if (Column == CswEnumNbtPropColumn.Field2) { ret = Field2; } else if (Column == CswEnumNbtPropColumn.Field2_Date) { ret = CswConvert.ToString(Field2_Date); } else if (Column == CswEnumNbtPropColumn.Field2_Numeric) { ret = CswConvert.ToString(Field2_Numeric); } else if (Column == CswEnumNbtPropColumn.Field3_Numeric) { ret = CswConvert.ToString(Field3_Numeric); } else if (Column == CswEnumNbtPropColumn.Field3) { ret = Field3; } else if (Column == CswEnumNbtPropColumn.Field4) { ret = Field4; } else if (Column == CswEnumNbtPropColumn.Field5) { ret = Field5; } else if (Column == CswEnumNbtPropColumn.Gestalt) { ret = Gestalt; } else if (Column == CswEnumNbtPropColumn.ClobData) { ret = ClobData; } else if (Column == CswEnumNbtPropColumn.Field1_Big) { ret = Field1_Big; } else { throw new CswDniException(CswEnumErrorType.Error, "Invalid PropColumn", "CswNbtNodePropData.GetPropRowValue() found an unhandled PropColumn: " + Column.ToString()); } return(ret); } // GetPropRowValue()
/// <summary> /// Resets next sequence value based on maximum existing value in the database. /// </summary> public void reSync(CswEnumNbtPropColumn Column) { reSync(Column, Int32.MinValue); }
/// <summary> /// Get the Prior state of the Property's value using a specific subfield /// </summary> public string GetOriginalPropRowValue(CswEnumNbtPropColumn Column) { return(_CswNbtNodePropData.GetOriginalPropRowValue(Column)); }
public override void beforeWriteNode(bool IsCopy, bool OverrideUniqueValidation) { List <CswNbtNodePropWrapper> CompoundUniqueProps = new List <CswNbtNodePropWrapper>(); foreach (CswNbtNodePropWrapper CurrentProp in _CswNbtNode.Properties) { if (CurrentProp.WasModified) { // When a property changes, we need to: // 1. recalculate composite property values which include changed properties on this node foreach (CswNbtNodePropWrapper CompositeProp in _CswNbtNode.Properties[(CswEnumNbtFieldType)CswEnumNbtFieldType.Composite]) { if ( CompositeProp.AsComposite.TemplateValue.Contains( CswNbtMetaData.MakeTemplateEntry(CurrentProp.NodeTypePropId.ToString()))) { CompositeProp.AsComposite.RecalculateCompositeValue(); } } // 2. recalculate property references attached to relationships whose values changed if (CurrentProp.getFieldTypeValue() == CswEnumNbtFieldType.Relationship) { foreach (CswNbtNodePropWrapper PropRefPropWrapper in _CswNbtNode.Properties[(CswEnumNbtFieldType)CswEnumNbtFieldType.PropertyReference]) { CswNbtNodePropPropertyReference PropRefProp = PropRefPropWrapper.AsPropertyReference; if ((PropRefProp.RelationshipType == CswEnumNbtViewPropIdType.NodeTypePropId && PropRefProp.RelationshipId == CurrentProp.NodeTypePropId) || (PropRefProp.RelationshipType == CswEnumNbtViewPropIdType.ObjectClassPropId && PropRefProp.RelationshipId == CurrentProp.ObjectClassPropId)) { PropRefProp.RecalculateReferenceValue(); } } } // 3. mark any property references to this property on other nodes as pending update if (CswTools.IsPrimaryKey(CurrentProp.NodeId)) { //BZ 10239 - Fetch the cached value field name. CswNbtFieldTypeRulePropertyReference PropRefFTR = (CswNbtFieldTypeRulePropertyReference)_CswNbtResources.MetaData.getFieldTypeRule(CswEnumNbtFieldType.PropertyReference); CswEnumNbtPropColumn PropRefColumn = PropRefFTR.CachedValueSubField.Column; string SQL = @"update jct_nodes_props set pendingupdate = '" + CswConvert.ToDbVal(true) + @"', " + PropRefColumn.ToString() + @" = '' where jctnodepropid in (select j.jctnodepropid from jct_nodes_props j join nodes n on n.nodeid = j.nodeid join nodetype_props p on p.nodetypepropid = j.nodetypepropid join field_types f on p.fieldtypeid = f.fieldtypeid left outer join jct_nodes_props jntp on (jntp.nodetypepropid = p.fkvalue and jntp.nodeid = n.nodeid and jntp.field1_fk = " + CurrentProp.NodeId.PrimaryKey.ToString() + @") left outer join (select jx.jctnodepropid, ox.objectclasspropid, jx.nodeid from jct_nodes_props jx join nodetype_props px on jx.nodetypepropid = px.nodetypepropid join object_class_props ox on px.objectclasspropid = ox.objectclasspropid where jx.field1_fk = " + CurrentProp.NodeId.PrimaryKey.ToString() + @") jocp on (jocp.objectclasspropid = p.fkvalue and jocp.nodeid = n.nodeid) where f.fieldtype = 'PropertyReference' and ((lower(p.fktype) = 'nodetypepropid' and jntp.jctnodepropid is not null) or (lower(p.fktype) = 'objectclasspropid' and jocp.jctnodepropid is not null)) and ((lower(p.valueproptype) = 'nodetypepropid' and p.valuepropid = " + CurrentProp.NodeTypePropId.ToString() + @") or (lower(p.valueproptype) = 'objectclasspropid' and p.valuepropid = " + CurrentProp.ObjectClassPropId + @")))"; // We're not doing this in a CswTableUpdate because it might be a large operation, // and we don't care about auditing for this change. _CswNbtResources.execArbitraryPlatformNeutralSql(SQL); } // 4. For locations, if this node's location changed, we need to update the pathname on the children if (CurrentProp.getFieldTypeValue() == CswEnumNbtFieldType.Location && CswTools.IsPrimaryKey(_CswNbtNode.NodeId)) { _CswNbtResources.CswNbtNodeFactory.CswNbtNodeWriter.updateRelationsToThisNode(_CswNbtNode); } // 5. Prepare for compound unique validation if (CswConvert.ToBoolean(CurrentProp[CswEnumNbtPropertyAttributeName.CompoundUnique])) { CompoundUniqueProps.Add(CurrentProp); } } // if(CurrentProp.WasModified) } // foreach (CswNbtNodePropWrapper CurrentProp in _CswNbtNode.Properties) if (CompoundUniqueProps.Count > 0 && NodeId != null) { if (false == IsCopy && false == OverrideUniqueValidation) { //check for other compound unique props that were _not_ modified foreach (CswNbtNodePropWrapper CurrentProp in _CswNbtNode.Properties) { if (CswConvert.ToBoolean(CurrentProp[CswEnumNbtPropertyAttributeName.CompoundUnique]) && (false == CompoundUniqueProps.Contains(CurrentProp))) { CompoundUniqueProps.Add(CurrentProp); } } CswNbtView CswNbtView = this.NodeType.CreateDefaultView(); CswNbtView.ViewName = "For compound unique"; CswNbtViewRelationship ViewRelationship = CswNbtView.Root.ChildRelationships[0]; if (CswTools.IsPrimaryKey(NodeId)) { ViewRelationship.NodeIdsToFilterOut.Add(NodeId); } foreach (CswNbtNodePropWrapper CurrentCompoundUniqueProp in CompoundUniqueProps) { //case 27670 - in order to reserve the right for compound unique props to be empty, it has to be explicitly stated when creating the ForCompundUnique view CswNbtViewProperty CswNbtViewProperty = CswNbtView.AddViewProperty(ViewRelationship, CurrentCompoundUniqueProp.NodeTypeProp); ICswNbtFieldTypeRule ftRule = CurrentCompoundUniqueProp.NodeTypeProp.getFieldTypeRule(); ftRule.AddUniqueFilterToView(CswNbtView, CswNbtViewProperty, CurrentCompoundUniqueProp, true); } ICswNbtTree NodeTree = _CswNbtResources.Trees.getTreeFromView(_CswNbtResources.CurrentNbtUser, CswNbtView, true, false, false); if (NodeTree.getChildNodeCount() > 0) { NodeTree.goToNthChild(0); CswNbtNode DuplicateValueNode = NodeTree.getNodeForCurrentPosition(); CswCommaDelimitedString CompoundUniquePropNames = new CswCommaDelimitedString(); CswCommaDelimitedString CompoundUniquePropValues = new CswCommaDelimitedString(); foreach (CswNbtNodePropWrapper CurrentUniqueProp in CompoundUniqueProps) { CompoundUniquePropNames.Add(CurrentUniqueProp.PropName); CompoundUniquePropValues.Add(CurrentUniqueProp.Gestalt); } string ExotericMessage = "The following properties must have unique values: " + CompoundUniquePropNames.ToString(); string EsotericMessage = "The " + CompoundUniquePropNames.ToString() + " of node " + NodeId.ToString() + " are the same as for node " + DuplicateValueNode.NodeId.ToString() + ": " + CompoundUniquePropValues.ToString(); if (false == _CswNbtNode.IsTemp && false == DuplicateValueNode.IsTemp) //only throw an error if we're comparing two REAL nodes { throw (new CswDniException(CswEnumErrorType.Warning, ExotericMessage, EsotericMessage)); } }//we have a duplicate value situation } else { foreach (CswNbtNodePropWrapper CurrentPropWrapper in CompoundUniqueProps) { CurrentPropWrapper.ClearValue(); CurrentPropWrapper.clearModifiedFlag(); } } //if-else we're not a copy and not overridding } //if we have at leaste one modified compound unique prop //_synchNodeName(); // can't do this here, because we miss some of the onBeforeUpdateNodePropRow events // we do it in writer now instead } // beforeWriteNode()
protected bool SetPropRowValue(CswEnumNbtSubFieldName SubFieldName, CswEnumNbtPropColumn column, object value, bool IsNonModifying = false) { return(_CswNbtNodePropData.SetPropRowValue(SubFieldName, column, value, IsNonModifying)); }
public static string renderViewPropFilter(ICswNbtUser RunAsUser, CswNbtFieldResources CswNbtFieldResources, CswNbtViewPropertyFilter CswNbtViewPropertyFilterIn, CswEnumNbtPropColumn Column) { string ValueColumn = "jnp." + Column.ToString(); string ReturnVal = string.Empty; bool IncludesTime = false; DateTime FilterValue = DateTime.MinValue; string Value = CswNbtViewPropertyFilterIn.Value.ToLower().Trim(); if (Value.StartsWith("today")) { Int32 PlusDays = 0; if (Value.Length > "today".Length) { string Operator = Value.Substring("today".Length, 1); string Operand = Value.Substring("today".Length + 1); if (CswTools.IsInteger(Operand)) { PlusDays = CswConvert.ToInt32(Operand); if (Operator == "-") { PlusDays = PlusDays * -1; } } } FilterValue = DateTime.Now.AddDays(PlusDays).Date; } else { FilterValue = CswConvert.ToDateTime(CswNbtViewPropertyFilterIn.Value); if (FilterValue.TimeOfDay != TimeSpan.Zero) // midnight { IncludesTime = true; } } if (CswNbtViewPropertyFilterIn.FilterMode == CswEnumNbtFilterMode.NotNull) { ReturnVal = ValueColumn + " is not null"; } else if (CswNbtViewPropertyFilterIn.FilterMode == CswEnumNbtFilterMode.Null) { ReturnVal = ValueColumn + " is null"; } else if (FilterValue != DateTime.MinValue) { // case 26844 // If no time was specified in our filter value, then // we need to ignore the time part of values in our comparisons string ThisDayString = CswNbtFieldResources.CswNbtResources.getDbNativeDate(FilterValue); string NextDayString = CswNbtFieldResources.CswNbtResources.getDbNativeDate(FilterValue.AddDays(1)); if (CswNbtViewPropertyFilterIn.FilterMode == CswEnumNbtFilterMode.Equals) { if (IncludesTime) { ReturnVal = ValueColumn + " = " + ThisDayString; } else { ReturnVal = ValueColumn + " >= " + ThisDayString; ReturnVal += " and " + ValueColumn + " < " + NextDayString; } } else if (CswNbtViewPropertyFilterIn.FilterMode == CswEnumNbtFilterMode.GreaterThan) { if (IncludesTime) { ReturnVal = ValueColumn + " > " + ThisDayString; } else { ReturnVal = ValueColumn + " >= " + NextDayString; } } else if (CswNbtViewPropertyFilterIn.FilterMode == CswEnumNbtFilterMode.GreaterThanOrEquals) { ReturnVal = ValueColumn + " >= " + ThisDayString; } else if (CswNbtViewPropertyFilterIn.FilterMode == CswEnumNbtFilterMode.LessThan) { ReturnVal = ValueColumn + " < " + ThisDayString; } else if (CswNbtViewPropertyFilterIn.FilterMode == CswEnumNbtFilterMode.LessThanOrEquals) { if (IncludesTime) { ReturnVal = ValueColumn + " <= " + ThisDayString; } else { ReturnVal = ValueColumn + " < " + NextDayString; // not <=, see case 28620 } } else if (CswNbtViewPropertyFilterIn.FilterMode == CswEnumNbtFilterMode.NotEquals) { if (IncludesTime) { ReturnVal = ValueColumn + " <> " + ThisDayString; } else { ReturnVal = "(" + ValueColumn + " < " + ThisDayString; ReturnVal += " or " + ValueColumn + " >= " + NextDayString + ")"; } } else { throw new CswDniException(CswEnumErrorType.Error, "Invalid filter", "An invalid FilterMode was encountered in CswNbtNodeProp.GetFilter()) { " + CswNbtViewPropertyFilterIn.FilterMode.ToString()); } // switch( CswNbtViewPropertyFilterIn.FilterMode ) } // if( FilterValue != DateTime.MinValue ) return(ReturnVal); }//renderViewPropFilter()
} // 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()
} // beforeWriteNode() //Updates related composite, propertyreference, relationship, and location private void _updateExternalRelatedProps(CswNbtNodePropWrapper CurrentProp) { // When a property changes, we need to: // 1. recalculate composite property values which include changed properties on this node foreach (CswNbtNodePropWrapper CompositeProp in _CswNbtNode.Properties[(CswEnumNbtFieldType)CswEnumNbtFieldType.Composite]) { if (CompositeProp.AsComposite.TemplateValue.Contains(CswNbtMetaData.MakeTemplateEntry(CurrentProp.NodeTypePropId.ToString()))) { CompositeProp.AsComposite.RecalculateCompositeValue(); } } // 2. recalculate property references attached to relationships whose values changed if (CurrentProp.getFieldTypeValue() == CswEnumNbtFieldType.Relationship) { foreach (CswNbtNodePropWrapper PropRefPropWrapper in _CswNbtNode.Properties[(CswEnumNbtFieldType)CswEnumNbtFieldType.PropertyReference]) { CswNbtNodePropPropertyReference PropRefProp = PropRefPropWrapper.AsPropertyReference; if ((PropRefProp.RelationshipType == CswEnumNbtViewPropIdType.NodeTypePropId && PropRefProp.RelationshipId == CurrentProp.NodeTypePropId) || (PropRefProp.RelationshipType == CswEnumNbtViewPropIdType.ObjectClassPropId && PropRefProp.RelationshipId == CurrentProp.ObjectClassPropId)) { PropRefProp.RecalculateReferenceValue(); } } } // 3. mark any property references to this property on other nodes as pending update if (CswTools.IsPrimaryKey(CurrentProp.NodeId)) { //BZ 10239 - Fetch the cached value field name. CswNbtFieldTypeRulePropertyReference PropRefFTR = (CswNbtFieldTypeRulePropertyReference)_CswNbtResources.MetaData.getFieldTypeRule(CswEnumNbtFieldType.PropertyReference); CswEnumNbtPropColumn PropRefColumn = PropRefFTR.CachedValueSubField.Column; string SQL = @"update jct_nodes_props set pendingupdate = '" + CswConvert.ToDbVal(true) + @"', " + PropRefColumn.ToString() + @" = '' where jctnodepropid in (select j.jctnodepropid from jct_nodes_props j join nodes n on n.nodeid = j.nodeid join nodetype_props p on p.nodetypepropid = j.nodetypepropid join field_types f on p.fieldtypeid = f.fieldtypeid left outer join jct_nodes_props jntp on (jntp.nodetypepropid = p.fkvalue and jntp.nodeid = n.nodeid and jntp.field1_fk = " + CurrentProp.NodeId.PrimaryKey.ToString() + @") left outer join (select jx.jctnodepropid, ox.objectclasspropid, jx.nodeid from jct_nodes_props jx join nodetype_props px on jx.nodetypepropid = px.nodetypepropid join object_class_props ox on px.objectclasspropid = ox.objectclasspropid where jx.field1_fk = " + CurrentProp.NodeId.PrimaryKey.ToString() + @") jocp on (jocp.objectclasspropid = p.fkvalue and jocp.nodeid = n.nodeid) where f.fieldtype = 'PropertyReference' and ((lower(p.fktype) = 'nodetypepropid' and jntp.jctnodepropid is not null) or (lower(p.fktype) = 'objectclasspropid' and jocp.jctnodepropid is not null)) and ((lower(p.valueproptype) = 'nodetypepropid' and p.valuepropid = " + CurrentProp.NodeTypePropId.ToString() + @") or (lower(p.valueproptype) = 'objectclasspropid' and p.valuepropid = " + CurrentProp.ObjectClassPropId + @")))"; // We're not doing this in a CswTableUpdate because it might be a large operation, // and we don't care about auditing for this change. _CswNbtResources.execArbitraryPlatformNeutralSql(SQL); } // 4. For locations, if this node's location changed, we need to update the pathname on the children if (CurrentProp.getFieldTypeValue() == CswEnumNbtFieldType.Location && CswTools.IsPrimaryKey(_CswNbtNode.NodeId)) { _CswNbtNode.updateRelationsToThisNode(); } }