//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()
