/// <summary>
/// Returns import data table names, in import order
/// </summary>
/// <param name="IncludeCompleted">If true, also include table names for already completed imports</param>
public static StringCollection getImportDataTableNames(CswNbtResources CswNbtResources, bool IncludeCompleted = false)
{
StringCollection ret = new StringCollection();
string Sql = @"select m." + CswNbtImportTables.ImportDataMap.datatablename +
" from " + CswNbtImportTables.ImportDataMap.TableName + " m " +
" join " + CswNbtImportTables.ImportDataJob.TableName + " j on m." + CswNbtImportTables.ImportDataMap.importdatajobid + " = j." + CswNbtImportTables.ImportDataJob.importdatajobid +
" join " + CswNbtImportTables.ImportDef.TableName + " d on m." + CswNbtImportTables.ImportDataMap.importdefid + " = d." + CswNbtImportTables.ImportDef.importdefid;
if (false == IncludeCompleted)
{
Sql += " where " + CswNbtImportTables.ImportDataJob.dateended + " is null";
Sql += " and m." + CswNbtImportTables.ImportDataMap.completed + " = '" + CswConvert.ToDbVal(false) + "'";
}
Sql += @" order by j." + CswNbtImportTables.ImportDataJob.datestarted + ", d." + CswNbtImportTables.ImportDef.sheetorder + ", m." + CswNbtImportTables.ImportDataMap.PkColumnName;
CswArbitrarySelect ImportDataSelect = CswNbtResources.makeCswArbitrarySelect("getImportDataTableNames_Select", Sql);
DataTable ImportDataTable = ImportDataSelect.getTable();
foreach (DataRow Row in ImportDataTable.Rows)
{
ret.Add(CswConvert.ToString(Row[CswNbtImportTables.ImportDataMap.datatablename]));
}
return(ret);
} // getImportDataTableNames()
} // getByPk()
public ICswNbtMetaDataObject getByPk(Int32 Pk, CswDateTime Date, bool BypassModuleCheck = false)
{
ICswNbtMetaDataObject ret = null;
if (false == CswTools.IsDate(Date))
{
ret = getByPk(Pk, BypassModuleCheck);
}
else
{
string Sql = "select * from " + CswNbtAuditTableAbbreviation.getAuditTableSql(_CswNbtMetaDataResources.CswNbtResources, _TableSelect.TableName, Date) + " " + _TableSelect.TableName;
string Where = " where " + _PkColumnName + " = " + Pk.ToString();
if (false == BypassModuleCheck)
{
addModuleWhereClause(ref Where);
}
Sql += Where;
CswArbitrarySelect AuditSelect = _CswNbtMetaDataResources.CswNbtResources.makeCswArbitrarySelect("MetaDataCollectionImpl_getbypk_audit_select", Sql);
DataTable Table = AuditSelect.getTable();
if (Table.Rows.Count > 0)
{
ret = _makeObj(Table.Rows[0], Date, useCache: false);
}
}
return(ret);
}
private void _init()
{
if (Int32.MinValue != _fkDefId)
{
CswTableSelect FkeyDefsSelect = _CswNbtResources.makeCswTableSelect("fetch_fkey_def_sql", "fkey_definitions");
DataTable FkeyDefsTable = FkeyDefsSelect.getTable("fkeydefid", _fkDefId);
string Sql = FkeyDefsTable.Rows[0]["sql"].ToString();
CswArbitrarySelect ListOptsSelect = _CswNbtResources.makeCswArbitrarySelect("list_options_query", Sql);
DataTable ListOptsTable = ListOptsSelect.getTable();
_Options = new Collection <CswNbtNodeTypePropListOption>();
_Options.Add(new CswNbtNodeTypePropListOption("", ""));
foreach (DataRow CurrentRow in ListOptsTable.Rows)
{
_Options.Add(new CswNbtNodeTypePropListOption(CurrentRow[FkeyDefsTable.Rows[0]["ref_column"].ToString()].ToString(),
CurrentRow[FkeyDefsTable.Rows[0]["pk_column"].ToString()].ToString()));
}//iterate listopts rows
}
else
{
CswCommaDelimitedString Opts = new CswCommaDelimitedString();
Opts.FromString(_ListOptions);
Override(Opts);
}
}
}//SessionListWhere
public Int32 getLoadCount(ICswResources CswResources)
{
Int32 ReturnVal = 0;
_MasterSchemaResources = _getMasterSchemaResources((CswNbtResources)CswResources);
if (null != _MasterSchemaResources)
{
CswArbitrarySelect CswArbitrarySelectSessionList = _MasterSchemaResources.makeCswArbitrarySelect("expired_session_list_query",
"select count(*) as cnt from sessionlist" + _SessionListWhere(CswResources.AccessId));
DataTable SessionListTable = CswArbitrarySelectSessionList.getTable();
Int32 ExpiredSessionRecordCount = CswConvert.ToInt32(SessionListTable.Rows[0]["cnt"]);
Int32 OrhpanSessionDataCount = _getOrphanRowCount((CswNbtResources)CswResources);
CswArbitrarySelect ArbitrarySelectOrphanedNodes = _MasterSchemaResources.makeCswArbitrarySelect("orphaned_temp_nodes",
"select count(*) as cnt from nodes n left outer join sessionlist s on n.sessionid=s.sessionid where istemp=1 and s.sessionid is null");
Int32 OrphanTempNodeCount = CswConvert.ToInt32(ArbitrarySelectOrphanedNodes.getTable().Rows[0]["cnt"]);
ReturnVal = ExpiredSessionRecordCount + OrhpanSessionDataCount + OrphanTempNodeCount;
_StaleDataExists = (ExpiredSessionRecordCount > 0 || OrhpanSessionDataCount > 0 || OrphanTempNodeCount > 0);
}
else
{
CswResources.CswLogger.reportError(new CswDniException("Unable to get load count of sessionlist records: The master schema resource object is null"));
}
return(ReturnVal);
}//getLoadCount()
private static Collection <CswNbtImportWcf.DltExistingNodesReturn.DltExistingNodesReturnData.DoomedNode> _retriveDoomedNodes(CswNbtResources NbtResources)
{
Collection <CswNbtImportWcf.DltExistingNodesReturn.DltExistingNodesReturnData.DoomedNode> DoomedNodes = new Collection <CswNbtImportWcf.DltExistingNodesReturn.DltExistingNodesReturnData.DoomedNode>();
CswArbitrarySelect NodeTblSelect = NbtResources.makeCswArbitrarySelect("getDoomedNodes_nbtImporter",
@"select t.nodeid, t.nodename, t.nodetypeid, nt.nodetypename
from nodes t
join nodetypes nt on (nt.nodetypeid = t.nodetypeid)
where nodename not in ('chemsw_admin', 'chemsw_admin_role', 'Each', 'Days', 'Ci', 'kg',
'Liters', 'lb', 'gal', 'cu.ft.', 'CISPro_Admin',
'CISPro_General', 'Default Jurisdiction')
and t.nodetypeid not in
(1130, 1212, 1329, 1330, 1369, 33, 114, 659, 1052, 1053, 1290, 1291, 1292, 1293, 1211)
and nodetypename not like 'Design%'");
DataTable NodesTbl = NodeTblSelect.getTable();
foreach (DataRow Row in NodesTbl.Rows)
{
DoomedNodes.Add(new CswNbtImportWcf.DltExistingNodesReturn.DltExistingNodesReturnData.DoomedNode
{
NodeId = CswConvert.ToInt32(Row["nodeid"]),
NodeName = CswConvert.ToString(Row["nodename"]),
NodeType = CswConvert.ToString(Row["nodetypename"])
});
}
return(DoomedNodes);
}//_retriveDoomedNodes()
public override void update()
{
string SQL = @"select jctnodepropid
from (select j1.nodeid, j1.nodetypepropid, max(j1.jctnodepropid) jctnodepropid
from jct_nodes_props j1
join jct_nodes_props j2 on (j1.nodetypepropid = j2.nodetypepropid
and j1.nodeid = j2.nodeid
and j1.jctnodepropid <> j2.jctnodepropid)
group by j1.nodeid, j1.nodetypepropid)";
CswArbitrarySelect DupeSelect = _CswNbtSchemaModTrnsctn.makeCswArbitrarySelect( "29797_select", SQL );
DataTable DupeTable = DupeSelect.getTable();
if( DupeTable.Rows.Count > 0 )
{
CswCommaDelimitedString JctIds = new CswCommaDelimitedString();
foreach( DataRow DupeRow in DupeTable.Rows )
{
JctIds.Add( DupeRow["jctnodepropid"].ToString() );
}
CswTableUpdate JctUpdate = _CswNbtSchemaModTrnsctn.makeCswTableUpdate( "29797_update", "jct_nodes_props" );
DataTable JctTable = JctUpdate.getTable( "where jctnodepropid in (" + JctIds.ToString() + ")" );
foreach( DataRow JctRow in JctTable.Rows )
{
JctRow.Delete();
}
JctUpdate.update( JctTable );
}
} // update()
/// <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()
public CswNbtViewRelationship getLocationRelationship(string LocationSql, CswNbtView LocationsView, CswPrimaryKey StartLocationId)
{
CswNbtViewRelationship LocationRel = null;
CswArbitrarySelect LocationSelect = _CswNbtResources.makeCswArbitrarySelect("populateLocations_select", LocationSql);
LocationSelect.addParameter("startlocationid", StartLocationId.PrimaryKey.ToString());
DataTable LocationTable = null;
try
{
LocationTable = LocationSelect.getTable();
Collection <CswPrimaryKey> LocationPks = new Collection <CswPrimaryKey>();
LocationPks.Add(StartLocationId);
CswNbtMetaDataObjectClass LocationOc = _CswNbtResources.MetaData.getObjectClass(CswEnumNbtObjectClass.LocationClass);
LocationRel = LocationsView.AddViewRelationship(LocationOc, false);
if (LocationTable.Rows.Count > 0)
{
foreach (DataRow Row in LocationTable.Rows)
{
Int32 LocationNodeId = CswConvert.ToInt32(Row["nodeid"]);
CswPrimaryKey LocationPk = new CswPrimaryKey("nodes", LocationNodeId);
LocationPks.Add(LocationPk);
}
}
LocationRel.NodeIdsToFilterIn = LocationPks;
}
catch (Exception ex)
{
throw new CswDniException(CswEnumErrorType.Error, "Invalid Query", "_getContainerRelationship() attempted to run invalid SQL: " + LocationSql, ex);
}
return(LocationRel);
}
private bool _doesTblIdxExist(string IdxName)
{
CswArbitrarySelect idxSelect = _CswNbtResources.makeCswArbitrarySelect("ModuleRuleDirectStructureSearch.doesIdxExist", "select * from user_indexes ui where lower(ui.index_name) = '" + IdxName + "'");
DataTable indexes = idxSelect.getTable();
return(indexes.Rows.Count > 0);
}
private void _setLoad(ICswResources CswResources)
{
CswNbtResources NbtResources = (CswNbtResources)CswResources;
if (NbtResources.Modules.IsModuleEnabled(CswEnumNbtModuleName.Containers))
{
#region SQL
String SQLSelect = @"with
RemainingQuantity as (
select jnp.nodeid, jnp.field1_numeric as RemainingQuantity, jnp.field1_fk as RemainingQuantityUnit
from jct_nodes_props jnp
inner join nodetype_props ntp on ntp.nodetypepropid = jnp.nodetypepropid
inner join object_class_props ocp on ocp.objectclasspropid = ntp.objectclasspropid
where ocp.propname = 'Remaining Source Container Quantity'
),
SourceContainer as (
select jnp.nodeid, jnp.field1_fk as containerid
from jct_nodes_props jnp
inner join nodetype_props ntp on ntp.nodetypepropid = jnp.nodetypepropid
inner join object_class_props ocp on ocp.objectclasspropid = ntp.objectclasspropid
where ( ocp.propname = 'Source Container' or ocp.propname = 'Destination Container' )
),
containers as (
select n.nodeid
from nodes n
inner join nodetypes nt on n.nodetypeid = nt.nodetypeid
inner join object_class oc on nt.objectclassid = oc.objectclassid
where oc.objectclass = 'ContainerClass'
),
dispenses as (
select
c.nodeid containerid,
n.nodeid dispensetransactionid,
rq.RemainingQuantity RemainingQuantity
from nodes n
left join RemainingQuantity rq on n.nodeid = rq.nodeid
left join SourceContainer sc on n.nodeid = sc.nodeid
left join containers c on sc.containerid = c.nodeid
inner join nodetypes nt on n.nodetypeid = nt.nodetypeid
inner join object_class oc on nt.objectclassid = oc.objectclassid
where oc.objectclass = 'ContainerDispenseTransactionClass'
and sc.containerid is not null
)
select c.nodeid, ct.dispenses
from containers c
left join (select c.nodeid containerid, count(d.dispensetransactionid) dispenses
from containers c
left join dispenses d on d.containerid = c.nodeid
group by c.nodeid) ct on c.nodeid = ct.containerid
where c.nodeid not in (select containerid from dispenses where RemainingQuantity is null)";
#endregion SQL
CswArbitrarySelect TransactionlessContainers = new CswArbitrarySelect(NbtResources.CswResources, "TransactionlessContainers", SQLSelect);
DataTable ContainersTable = TransactionlessContainers.getTable();
foreach (DataRow ContainerRow in ContainersTable.Rows)
{
_ContainerIdsWithoutReceiveTransactions.Add(new CswPrimaryKey("nodes", CswConvert.ToInt32(ContainerRow["nodeid"].ToString())));
_ContainerHasOtherTransactions.Add(CswConvert.ToInt32(ContainerRow["dispenses"].ToString()) > 0);
}
}
}
public static string generateImportQueueTableSQL(ICswResources CswResources)
{
string Ret = string.Empty;
CswArbitrarySelect ImportDefSelect = CswResources.makeCswArbitrarySelect("importdef_get_caf_rows", "select distinct pkcolumnname, coalesce(viewname, tablename) as sourcename " +
"from import_def_order io, import_def id " +
"where id.importdefid = io.importdefid " +
"and id.definitionname = '" + DefinitionName + "'");
DataTable ImportDefTable = ImportDefSelect.getTable();
bool FirstRow = true;
foreach (DataRow DefRow in ImportDefTable.Rows)
{
string CurrentDefRowSql = @"insert into nbtimportqueue(nbtimportqueueid, state, itempk, sheetname, priority, errorlog) "
+ " select seq_nbtimportqueueid.nextval, '"
+ State.I + "', "
+ CswConvert.ToString(DefRow[CswNbtImportTables.ImportDefOrder.pkcolumnname]) + ", "
+ "'" + CswConvert.ToString(DefRow["sourcename"]) + "', "
+ "0, "
+ "'' from " + CswConvert.ToString(DefRow["sourcename"]) + " where deleted = '0';";
CurrentDefRowSql = CurrentDefRowSql + "\ncommit;";
if (FirstRow)
{
Ret = CurrentDefRowSql + Environment.NewLine;
FirstRow = false;
}
else
{
Ret = Ret + Environment.NewLine + CurrentDefRowSql + Environment.NewLine;
}
}
return(Ret);
}
} // getLayout()
/// <summary>
/// Returns a layout for a property on a tab.
/// If edit, be sure to supply a valid TabId.
/// </summary>
public NodeTypeLayout getLayout(CswEnumNbtLayoutType LayoutType, Int32 NodeTypeId, Int32 PropId, Int32 TabId, CswDateTime Date)
{
NodeTypeLayout ret = null;
if (false == CswTools.IsDate(Date))
{
_CacheLayout(NodeTypeId);
ret = _Cache[NodeTypeId].FirstOrDefault(Layout => Layout.LayoutType == LayoutType &&
Layout.PropId == PropId &&
(LayoutType != CswEnumNbtLayoutType.Edit || Layout.TabId == TabId));
}
else
{
string Sql = @"select * from " + CswNbtAuditTableAbbreviation.getAuditTableSql(_CswNbtMetaDataResources.CswNbtResources, "nodetype_layout", Date) +
" where nodetypeid = " + NodeTypeId + " " +
" and layouttype = '" + LayoutType.ToString() + "' " +
" and nodetypepropid = " + PropId + " ";
if (LayoutType == CswEnumNbtLayoutType.Edit)
{
Sql += " and nodetypetabsetid = " + TabId;
}
CswArbitrarySelect LayoutSelect = _CswNbtMetaDataResources.CswNbtResources.makeCswArbitrarySelect("getLayout_Audit_Select", Sql);
DataTable LayoutTable = LayoutSelect.getTable();
if (LayoutTable.Rows.Count > 0)
{
ret = new NodeTypeLayout(LayoutTable.Rows[0]);
}
}
return(ret);
} // getLayout()
/// <summary>
/// Returns a dictionary of layout by tab
/// </summary>
public Dictionary <Int32, NodeTypeLayout> getLayout(CswEnumNbtLayoutType LayoutType, Int32 NodeTypeId, Int32 PropId, CswDateTime Date)
{
Dictionary <Int32, NodeTypeLayout> LayoutByTab = new Dictionary <Int32, NodeTypeLayout>();
if (false == CswTools.IsDate(Date))
{
_CacheLayout(NodeTypeId);
foreach (NodeTypeLayout Layout in _Cache[NodeTypeId].Where(Layout => Layout.LayoutType == LayoutType &&
Layout.PropId == PropId))
{
LayoutByTab[Layout.TabId] = Layout;
}
}
else
{
string Sql = @"select * from " + CswNbtAuditTableAbbreviation.getAuditTableSql(_CswNbtMetaDataResources.CswNbtResources, "nodetype_layout", Date) +
" where nodetypeid = " + NodeTypeId + " " +
" and layouttype = '" + LayoutType.ToString() + "' " +
" and nodetypepropid = " + PropId;
CswArbitrarySelect LayoutSelect = _CswNbtMetaDataResources.CswNbtResources.makeCswArbitrarySelect("getLayout_Audit_Select", Sql);
DataTable LayoutTable = LayoutSelect.getTable();
foreach (DataRow LayoutRow in LayoutTable.Rows)
{
NodeTypeLayout Layout = new NodeTypeLayout(LayoutRow);
LayoutByTab.Add(Layout.TabId, Layout);
}
}
return(LayoutByTab);
} // getLayout()
private Int32 _getNextAvailableRowForItem(Int32 RoleId, string ActionId)
{
String SqlText = "select max(display_row) maxrow from landingpage where display_col = 1 ";
String ActionClause = " and (for_actionid = :actionid )";
String RoleClause = " and (for_roleid = :roleid )";
if (false == String.IsNullOrEmpty(ActionId))
{
SqlText += ActionClause;
}
else//TODO - if and when Action Landing Pages are Role-specific, remove this else clause
{
SqlText += RoleClause;
}
CswArbitrarySelect LandingPageSelect = _CswNbtResources.makeCswArbitrarySelect("LandingPageForRole", SqlText);
LandingPageSelect.addParameter("actionid", ActionId);
LandingPageSelect.addParameter("roleid", RoleId.ToString());
DataTable LandingPageSelectTable = LandingPageSelect.getTable();
Int32 MaxRow = 0;
if (LandingPageSelectTable.Rows.Count > 0)
{
MaxRow = CswConvert.ToInt32(LandingPageSelectTable.Rows[0]["maxrow"]);
if (MaxRow < 0)
{
MaxRow = 0;
}
}
return(MaxRow + 1);
}
public void threadCallBack(ICswResources CswResources)
{
_LogicRunStatus = CswEnumScheduleLogicRunStatus.Running;
CswNbtResources CswNbtResources = (CswNbtResources)CswResources;
CswNbtResources.AuditContext = "Scheduler Task: " + RuleName;
if (CswEnumScheduleLogicRunStatus.Stopping != _LogicRunStatus)
{
try
{
// Find which nodes are out of date
CswArbitrarySelect OutOfDateNodesQuerySelect = getValuesToUpdate(CswNbtResources);
DataTable OutOfDateNodes = null;
Int32 NodesPerCycle = CswConvert.ToInt32(CswNbtResources.ConfigVbls.getConfigVariableValue(CswEnumConfigurationVariableNames.NodesProcessedPerCycle));
if (NodesPerCycle <= 0)
{
NodesPerCycle = 25;
}
OutOfDateNodes = OutOfDateNodesQuerySelect.getTable(0, NodesPerCycle, false);
NodesPerCycle = OutOfDateNodes.Rows.Count; //in case we didn't actually retrieve that amount
Int32 ErroneousNodeCount = 0;
string ErroneousNodes = "The following Nodes failed to update:\n";
for (Int32 idx = 0; (idx < NodesPerCycle); idx++)
{
CswPrimaryKey nodeid = new CswPrimaryKey("nodes", CswConvert.ToInt32(OutOfDateNodes.Rows[idx]["nodeid"].ToString()));
try//Case 29526 - if updating the node fails for whatever reason, log it and move on
{
CswNbtNode Node = CswNbtResources.Nodes[nodeid];
CswNbtActUpdatePropertyValue CswNbtActUpdatePropertyValue = new CswNbtActUpdatePropertyValue(CswNbtResources);
CswNbtActUpdatePropertyValue.UpdateNode(Node, false);
// Case 28997:
Node.postChanges(ForceUpdate: true);
}
catch (Exception ex)
{
if (false == ErroneousNodes.Contains(CswConvert.ToString(nodeid)))
{
ErroneousNodeCount++;
ErroneousNodes += nodeid + " - " + ex.Message + ex.StackTrace + "\n\n";
}
}
}//if we have nodes to process
_CswScheduleLogicDetail.StatusMessage = 0 == ErroneousNodeCount ? "Completed without error" : ErroneousNodes;
_LogicRunStatus = CswEnumScheduleLogicRunStatus.Succeeded; //last line
}
catch (Exception Exception)
{
CswNbtResources.logError(Exception);
_CswScheduleLogicDetail.StatusMessage = "CswScheduleLogicNbtUpdtPropVals exception: " + Exception.Message + "; " + Exception.StackTrace;
_LogicRunStatus = CswEnumScheduleLogicRunStatus.Failed;//last line
}
}//if we're not shutting down
}//threadCallBack()
//Determine the number of Nodes that need to be updated and return that value
public Int32 getLoadCount(ICswResources CswResources)
{
CswArbitrarySelect PendingEventsNodesSelect = getValuesToUpdate(CswResources);
DataTable NodesRequiringUpdateEvents = PendingEventsNodesSelect.getTable();
Int32 LoadCount = NodesRequiringUpdateEvents.Rows.Count;
return(LoadCount);
}
public Int32 getLoadCount(ICswResources CswResources)
{
string Sql = "select count(*) cnt from nbtimportqueue@" + CAFDbLink + " where state = '" + State.I + "' or state = '" + State.U + "'";
CswArbitrarySelect QueueCountSelect = CswResources.makeCswArbitrarySelect("cafimport_queue_count", Sql);
DataTable QueueCountTable = QueueCountSelect.getTable();
return(CswConvert.ToInt32(QueueCountTable.Rows[0]["cnt"]));
}
public override void update()
{
CswNbtMetaDataFieldType BarcodeFT = _CswNbtSchemaModTrnsctn.MetaData.getFieldType( CswEnumNbtFieldType.Barcode );
string SelectText = "select nodetypepropid from nodetype_props where fieldtypeid = " + BarcodeFT.FieldTypeId;
CswArbitrarySelect GetBarcodePropsSelect = _CswNbtSchemaModTrnsctn.makeCswArbitrarySelect( "Get Barcode Props Select", SelectText );
DataTable BarcodePropsTable = GetBarcodePropsSelect.getTable();
foreach( DataRow BarcodePropsRow in BarcodePropsTable.Rows )
{
CswNbtMetaDataNodeTypeProp BarcodeProp = _CswNbtSchemaModTrnsctn.MetaData.getNodeTypeProp( CswConvert.ToInt32( BarcodePropsRow["nodetypepropid"] ) );
BarcodeProp.ReadOnly = true;
}
} // update()
//Determine the number of props that need to be updated and return that value
public Int32 getLoadCount(ICswResources CswResources)
{
Int32 OldLoadCount = _CswScheduleLogicDetail.LoadCount;
CswArbitrarySelect OutOfDateNodesQuerySelect = getValuesToUpdate(CswResources);
DataTable OutOfDateNodes = OutOfDateNodesQuerySelect.getTable();
Int32 LoadCount = OutOfDateNodes.Rows.Count;
if (LoadCount == OldLoadCount)
{
LoadCount = 0;//Case 31213 - If LoadCount is stuck above 0 and hasn't changed, skip running this rule
}
return(LoadCount);
}
} // removeSessionData(CswNbtSessionDataId)
public void removeAllSessionData( string SessionId )
{
if( SessionId != string.Empty )
{
if( _CswNbtResources.IsInitializedForDbAccess )
{
CswTableUpdate SessionDataUpdate = _CswNbtResources.makeCswTableUpdate( "removeSessionData_update", SessionDataTableName );
DataTable SessionDataTable = SessionDataUpdate.getTable( "where " + SessionDataColumn_SessionId + " = '" + SessionId + "'" );
if( SessionDataTable.Rows.Count > 0 )
{
Collection<DataRow> DoomedRows = new Collection<DataRow>();
foreach( DataRow Row in SessionDataTable.Rows )
DoomedRows.Add( Row );
foreach( DataRow Row in DoomedRows )
Row.Delete();
SessionDataUpdate.update( SessionDataTable );
}//there are session records
CswArbitrarySelect SessionNodeSelect = _CswNbtResources.makeCswArbitrarySelect( "removeSessionData_update_nodes",
"select nodeid from nodes where istemp = 1 and sessionid = :sessionid " );
SessionNodeSelect.addParameter( "sessionid", SessionId );
DataTable NodesTable = SessionNodeSelect.getTable();
if( NodesTable.Rows.Count > 0 )
{
Collection<CswNbtNode> DoomedNodes = new Collection<CswNbtNode>();
foreach( DataRow Row in NodesTable.Rows )
{
CswPrimaryKey NodeId = new CswPrimaryKey( "nodes", CswConvert.ToInt32( Row["nodeid"] ) );
if( CswTools.IsPrimaryKey( NodeId ) )
{
CswNbtNode TempNode = _CswNbtResources.Nodes[NodeId];
if( null != TempNode )
{
DoomedNodes.Add( TempNode );
}
}
}
foreach( CswNbtNode DoomedNode in DoomedNodes )
{
DoomedNode.delete( DeleteAllRequiredRelatedNodes : true, OverridePermissions : true );
}
}//there are nodes rows
}//Db resources are initialzied
}//SessionId is not empty
} // removeAllSessionData()
public void processUpdateEvents(CswNbtResources CswNbtResources)
{
CswArbitrarySelect PendingEventsNodesSelect = getValuesToUpdate(CswNbtResources);
Int32 NodesPerCycle = _getNodesPerCycle(CswNbtResources);
DataTable NodesRequiringUpdate = PendingEventsNodesSelect.getTable(0, NodesPerCycle, false);
NodesPerCycle = NodesRequiringUpdate.Rows.Count;
for (Int32 idx = 0; (idx < NodesPerCycle); idx++)
{
CswPrimaryKey NodeId = new CswPrimaryKey("nodes", CswConvert.ToInt32(NodesRequiringUpdate.Rows[idx]["nodeid"].ToString()));
executeNodeUpdateEvents(CswNbtResources, NodeId);
}
}
/// <summary>
/// Returns the set of available Import Definition Names
/// </summary>
public static CswCommaDelimitedString getDefinitionNames(CswNbtResources CswNbtResources)
{
CswCommaDelimitedString ret = new CswCommaDelimitedString();
//CswTableSelect DefSelect = _CswNbtResources.makeCswTableSelect( "loadBindings_def_select1", CswNbtImportTables.ImportDef.TableName );
string Sql = @"select distinct(" + CswNbtImportTables.ImportDef.definitionname + ") from " + CswNbtImportTables.ImportDef.TableName + "";
CswArbitrarySelect DefSelect = CswNbtResources.makeCswArbitrarySelect("loadBindings_def_select1", Sql);
DataTable DefDataTable = DefSelect.getTable();
foreach (DataRow defrow in DefDataTable.Rows)
{
ret.Add(defrow[CswNbtImportTables.ImportDef.definitionname].ToString(), false, true);
}
return(ret);
}
public DataTable GetDataAsTable(string NameColumn, string KeyColumn)
{
DataTable Data = new CswDataTable("GetDataAsTable_DataTable", "");
Data.Columns.Add(NameColumn);
Data.Columns.Add(KeyColumn);
foreach (string XValue in XValues)
{
Data.Columns.Add(XValue, typeof(bool));
}
//if( _CswNbtMetaDataNodeTypeProp.IsFK && _CswNbtMetaDataNodeTypeProp.FKType == "fkeydefid" )
if (CswConvert.ToBoolean(_CswNbtNodePropData[CswNbtFieldTypeRuleLogicalSet.AttributeName.IsFK]) &&
_CswNbtNodePropData[CswNbtFieldTypeRuleLogicalSet.AttributeName.FKType] == "fkeydefid")
{
CswTableSelect FkeyDefsSelect = _CswNbtResources.makeCswTableSelect("YValues_fkeydef_select", "fkey_definitions");
//DataTable FkeyDefsTable = FkeyDefsSelect.getTable( "fkeydefid", _CswNbtMetaDataNodeTypeProp.FKValue );
DataTable FkeyDefsTable = FkeyDefsSelect.getTable("fkeydefid", CswConvert.ToInt32(_CswNbtNodePropData[CswNbtFieldTypeRuleLogicalSet.AttributeName.FKValue]));
string Sql = FkeyDefsTable.Rows[0]["sql"].ToString();
CswArbitrarySelect YValuesSelect = _CswNbtResources.makeCswArbitrarySelect("YValues_select", Sql);
DataTable YValuesTable = YValuesSelect.getTable();
foreach (DataRow CurrentYValueRow in YValuesTable.Rows)
{
DataRow Row = Data.NewRow();
Row[NameColumn] = CurrentYValueRow[FkeyDefsTable.Rows[0]["ref_column"].ToString()].ToString();
Row[KeyColumn] = CurrentYValueRow[FkeyDefsTable.Rows[0]["pk_column"].ToString()].ToString();
foreach (string XValue in XValues)
{
Row[XValue] = CheckValue(XValue, Row[KeyColumn].ToString());
}
Data.Rows.Add(Row);
}
}
else
{
foreach (string YValue in YValues)
{
DataRow Row = Data.NewRow();
Row[NameColumn] = YValue;
Row[KeyColumn] = YValue;
foreach (string XValue in XValues)
{
Row[XValue] = CheckValue(XValue, YValue);
}
Data.Rows.Add(Row);
}
}
return(Data);
}
public static void CreateCafProps(CswNbtResources NbtResources, List <string> NodeTypes, string PropsValsTblName, string PropsValsPKName, CswEnumSetupMode SetupMode)
{
CswNbtSchemaUpdateImportMgr ImpMgr = new CswNbtSchemaUpdateImportMgr(new CswNbtSchemaModTrnsctn(NbtResources), "CAF", ImporterSetUpMode: SetupMode);
string sql = GetCAFPropertiesSQL(PropsValsTblName, NodeTypes);
CswArbitrarySelect cafChemPropAS = NbtResources.makeCswArbitrarySelect("cafProps_" + PropsValsPKName, sql);
DataTable cafChemPropsDT = cafChemPropAS.getTable();
foreach (DataRow row in cafChemPropsDT.Rows)
{
foreach (string nt in NodeTypes)
{
CswNbtMetaDataNodeType NodeType = NbtResources.MetaData.getNodeType(nt);
string PropName = row["propertyname"].ToString();
int PropId = CswConvert.ToInt32(row["propertyid"]);
PropName = GetUniquePropName(NodeType, PropName); //keep appending numbers until we have a unique prop name
CswEnumNbtFieldType propFT = GetFieldTypeFromCAFPropTypeCode(row["propertytype"].ToString());
CswNbtMetaDataNodeTypeProp newProp = NbtResources.MetaData.makeNewProp(new CswNbtWcfMetaDataModel.NodeTypeProp(NodeType, NbtResources.MetaData.getFieldType(propFT), PropName)
{
IsRequired = CswConvert.ToBoolean(row["required"]),
ReadOnly = CswConvert.ToBoolean(row["readonly"]),
ListOptions = CswConvert.ToString(row["listopts"])
});
newProp.removeFromAllLayouts();
string cafColPropName = "prop" + row["propertyid"];
string cafSourceCol = "propvaltext";
if (CswEnumNbtFieldType.DateTime == propFT)
{
cafSourceCol = "propvaldate";
}
else if (CswEnumNbtFieldType.Number == propFT)
{
cafSourceCol = "propvalnumber";
}
ImpMgr.importBinding(cafSourceCol, PropName, "", "CAF", NodeType.NodeTypeName,
ClobTableName: PropsValsTblName,
LobDataPkColOverride: cafColPropName,
LobDataPkColName: PropsValsPKName,
LegacyPropId: PropId);
}
}
NbtResources.commitTransaction();
ImpMgr.finalize();
}
private CswCommaDelimitedString _getMolsWithNoCTab(CswNbtResources NbtResources)
{
CswCommaDelimitedString nonCTabedMols = new CswCommaDelimitedString();
string sql = @"select nodeid from mol_data where ctab is null";
CswArbitrarySelect arbSelect = NbtResources.makeCswArbitrarySelect("getNonCTabedMols", sql);
DataTable nodesToUpdate = arbSelect.getTable();
foreach (DataRow row in nodesToUpdate.Rows)
{
nonCTabedMols.Add(row["nodeid"].ToString());
}
return(nonCTabedMols);
}
private static int _getNodeTypePropIdFromJctNodePropId(CswNbtResources NbtResources, int JctNodePropId)
{
int ret = Int32.MinValue;
string sql = "select nodetypepropid from jct_nodes_props where jctnodepropid = :jctnodepropid";
CswArbitrarySelect arbSelect = NbtResources.makeCswArbitrarySelect("BinaryData.getNodeTypePropIdFromJctNodePropId", sql);
arbSelect.addParameter("jctnodepropid", JctNodePropId.ToString());
DataTable tbl = arbSelect.getTable();
if (tbl.Rows.Count > 0)
{
ret = CswConvert.ToInt32(tbl.Rows[0]["nodetypepropid"]);
}
return(ret);
}
//public void makeArbitraryNode()
//{
// if( null == TestNode )
// {
// if( null == TestNodeType )
// {
// TestNodeType = _CswTestCaseRsrc.makeTestNodeType( TestNodeTypeNamesFake.TestNodeType01 );
// BuiltInProp = TestNodeType.getNodeTypeProp( "Name" );
// AddedProp = _CswNbtSchemaModTrnsctn.MetaData.makeNewProp( TestNodeType, CswEnumNbtFieldType.Text, "AddedProp", 0 );
// }
// CswNbtMetaDataNodeType LatestVersionNT = _CswNbtSchemaModTrnsctn.MetaData.getNodeTypeLatestVersion( TestNodeType.NodeTypeId );
// TestNode = _CswNbtSchemaModTrnsctn.Nodes.makeNodeFromNodeTypeId( LatestVersionNT.NodeTypeId, CswEnumNbtMakeNodeOperation.DoNothing );
// }//if we haven't already made a node
//}//makeArbitraryNode()
public DataTable getJctAuditRecords()
{
DataTable ReturnVal = null;
CswCommaDelimitedString CswCommaDelimitedString = new CswCommaDelimitedString();
CswCommaDelimitedString.Add(JctNodePropIdOfBuiltInProp.ToString());
CswCommaDelimitedString.Add(JctNodePropIdOfAddedProp.ToString());
CswArbitrarySelect CswArbitrarySelect = _CswNbtSchemaModTrnsctn.makeCswArbitrarySelect(Purpose, "select * from jct_nodes_props_audit where jctnodepropid in (" + CswCommaDelimitedString.ToString() + ")");
ReturnVal = CswArbitrarySelect.getTable();
return(ReturnVal);
}
/// <summary>
/// Removes all rows flagged isdemo=1
/// </summary>
public void RemoveDemoData()
{
if ("1" == _CswNbtResources.ConfigVbls.getConfigVariableValue("is_demo"))
{
String AllDemoTablesSQL = " select distinct tablename from data_dictionary where columnname='isdemo' and tablename <> 'nodes' and tablename <> 'statistics' order by tablename ";
CswArbitrarySelect AllDemoTables = _CswNbtResources.makeCswArbitrarySelect("Fetch Tables With Demo Data", AllDemoTablesSQL);
DataTable DemosDataTable = AllDemoTables.getTable();
CswCommaDelimitedString TablesToPrune = new CswCommaDelimitedString();
for (Int32 i = 0; i < DemosDataTable.Rows.Count; i++)
{
TablesToPrune.Add(DemosDataTable.Rows[i]["tablename"].ToString());
}
TablesToPrune.Sort();
//As of 01H-17, executing this in alphabetical order (minus nodes/statistics) will work
foreach (String TableName in TablesToPrune)
{
String NukeDemoDataSQL = "delete from " + TableName + " where isdemo = '" + CswConvert.ToDbVal(true) + "'";
try
{
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(NukeDemoDataSQL);
_CswNbtSchemaModTrnsctn.commitTransaction();
}
catch (Exception ex)
{
throw new CswDniException(CswEnumErrorType.Error, "Before records from the " + TableName + " table can be deleted, child records must be deleted first.", "Oracle threw an " + ex + " exception.");
}
}
// We just happen to know that these are the only 2 tables which have constraints and need to be dealt with separately
try
{
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql("delete from nodes where isdemo= '" + CswConvert.ToDbVal(true) + "'");
_CswNbtSchemaModTrnsctn.commitTransaction();
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql("delete from statistics where isdemo= '" + CswConvert.ToDbVal(true) + "'");
_CswNbtSchemaModTrnsctn.commitTransaction();
}
catch (Exception ex)
{
throw new CswDniException(CswEnumErrorType.Error, "Before records from the nodes/statistics table(s) can be deleted, child records must be deleted first.", "Oracle threw an " + ex + " exception.");
}
_CswNbtResources.ConfigVbls.setConfigVariableValue("is_demo", "0");
}//if( "1" == _CswNbtResources.ConfigVbls.getConfigVariableValue( "is_demo" ) )
}
public override void update()
{
CswTableUpdate LayoutTabUpdate = _CswNbtSchemaModTrnsctn.makeCswTableUpdate( "layout_tab_update", "layout_tab" );
DataTable LayoutTabTable = LayoutTabUpdate.getEmptyTable();
CswArbitrarySelect OldLayoutTabsSelect = _CswNbtSchemaModTrnsctn.makeCswArbitrarySelect( "layout_tab_update",
@"select
l.layouttype,
t.tabname,
t.taborder,
l.nodetypeid,
n.nodeid design_nodetype_nodeid,
l.nodetypetabsetid,
n3.nodeid design_nodetypetab_nodeid
from nodetype_layout l
left join nodetype_tabset t on l.nodetypetabsetid = t.nodetypetabsetid
left join nodes n on n.relationalid = l.nodetypeid and n.relationaltable = 'nodetypes'
left join nodes n3 on n3.relationalid = l.nodetypetabsetid and n3.relationaltable = 'nodetype_tabset'
group by l.layouttype, t.tabname, t.taborder, l.nodetypeid, l.nodetypetabsetid, n.nodeid, n3.nodeid
order by l.nodetypeid, l.layouttype" );
DataTable OldLayoutTabsTable = OldLayoutTabsSelect.getTable();
foreach( DataRow OldLayoutRow in OldLayoutTabsTable.Rows )
{
DataRow LayoutTabRow = LayoutTabTable.NewRow();
LayoutTabRow["layout_type"] = OldLayoutRow["layouttype"].ToString().ToLower();
if( LayoutTabRow["layout_type"].ToString() == "table" )
{
LayoutTabRow["layout_type"] = "search";
}
LayoutTabRow["tab_type"] = "main";
if( OldLayoutRow["tabname"].ToString() == "Identity" )
{
LayoutTabRow["tab_type"] = "identity";
}
LayoutTabRow["tab_name"] = OldLayoutRow["tabname"];
LayoutTabRow["tab_order"] = OldLayoutRow["taborder"];
if( LayoutTabRow["tab_order"].ToString() == "" )
{
LayoutTabRow["tab_order"] = 1;
}
LayoutTabRow["metadata_nodetypeid"] = OldLayoutRow["nodetypeid"];
LayoutTabRow["design_nodetype_nodeid"] = OldLayoutRow["design_nodetype_nodeid"];
LayoutTabRow["design_nodetypetab_nodeid"] = OldLayoutRow["design_nodetypetab_nodeid"];
LayoutTabTable.Rows.Add( LayoutTabRow );
}
LayoutTabUpdate.update( LayoutTabTable );
} // update()
} // update()
private void _getCounts( out Dictionary<Int32, Int32> NodeCountsForNodeType, out Dictionary<Int32, Int32> NodeCountsForObjectClass )
{
NodeCountsForNodeType = new Dictionary<Int32, Int32>();
NodeCountsForObjectClass = new Dictionary<Int32, Int32>();
// Look up the object class of all nodes (deleted or no)
string SqlSelect = @"select count(distinct nodeid) cnt, firstversionid, objectclassid
from (select n.nodeid, t.firstversionid, o.objectclassid, n.istemp
from nodes n
left outer join nodetypes t on n.nodetypeid = t.nodetypeid
left outer join object_class o on t.objectclassid = o.objectclassid
UNION
select n.nodeid, ta.firstversionid, o.objectclassid, n.istemp
from nodes_audit n
left outer join nodetypes_audit ta on n.nodetypeid = ta.nodetypeid
left outer join object_class o on ta.objectclassid = o.objectclassid)";
SqlSelect += "where istemp = 0";
SqlSelect += "group by objectclassid, firstversionid";
CswArbitrarySelect NodeCountSelect = _CswNbtSchemaModTrnsctn.makeCswArbitrarySelect( "CswNbtActQuotas_historicalNodeCount", SqlSelect );
DataTable NodeCountTable = NodeCountSelect.getTable();
foreach( DataRow NodeCountRow in NodeCountTable.Rows )
{
Int32 ThisObjectClassId = CswConvert.ToInt32( NodeCountRow["objectclassid"] );
Int32 ThisNodeTypeId = CswConvert.ToInt32( NodeCountRow["firstversionid"] );
Int32 NodeCount = CswConvert.ToInt32( NodeCountRow["cnt"] );
if( NodeCountsForNodeType.ContainsKey( ThisNodeTypeId ) )
{
NodeCountsForNodeType[ThisNodeTypeId] += NodeCount;
}
else
{
NodeCountsForNodeType.Add( ThisNodeTypeId, NodeCount );
}
if( NodeCountsForObjectClass.ContainsKey( ThisObjectClassId ) )
{
NodeCountsForObjectClass[ThisObjectClassId] += NodeCount;
}
else
{
NodeCountsForObjectClass.Add( ThisObjectClassId, NodeCount );
}
}
}
