public IEnumerable<CswNbtMetaDataNodeTypeProp> getLayoutProps( Int32 NodeTypeId, Int32 TabId, CswEnumNbtLayoutType LayoutType, CswDateTime Date, bool PropsInLayout = true )
{
string NodeTypeIdStr = NodeTypeId.ToString();
string WhereClause = "where nodetypeid = '" + NodeTypeIdStr + "' ";
WhereClause += " and nodetypepropid ";
if( PropsInLayout )
{
WhereClause += " in ";
}
else
{
WhereClause += " not in ";
}
WhereClause += " (select nodetypepropid ";
if( null == Date || Date.ToDateTime() == DateTime.MinValue )
{
WhereClause += " from nodetype_layout ";
}
else
{
WhereClause += " from " + CswNbtAuditTableAbbreviation.getAuditTableSql( _CswNbtMetaDataResources.CswNbtResources, "nodetype_layout", Date );
}
WhereClause += " where layouttype = '" + LayoutType.ToString() + "'" +
" and nodetypeid = " + NodeTypeIdStr + @" ";
if( LayoutType == CswEnumNbtLayoutType.Edit && TabId != Int32.MinValue )
{
WhereClause += "and nodetypetabsetid = " + TabId.ToString();
}
WhereClause += ")";
return _CollImpl.getWhere( WhereClause, Date ).Cast<CswNbtMetaDataNodeTypeProp>();
} // getPropsInLayout()
} // 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);
}
} // 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()
public Dictionary <Int32, string> getPkDict(string Where = "", CswDateTime Date = null)
{
if (_PkDictsWhere == null)
{
_PkDictsWhere = new Dictionary <PkDictKey, Dictionary <int, string> >();
}
PkDictKey Key = new PkDictKey()
{
Date = Date, Where = Where
};
if (false == _PkDictsWhere.ContainsKey(Key))
{
DataTable Table;
if (null == Date)
{
CswCommaDelimitedString Select = new CswCommaDelimitedString();
Select.Add(_PkColumnName);
Select.Add(_NameColumnName);
string WhereClause = Where;
addModuleWhereClause(ref WhereClause);
Table = _TableSelect.getTable(Select, string.Empty, Int32.MinValue, WhereClause, false);
}
else
{
string Sql = "select " + _PkColumnName + "," + _NameColumnName +
" from " + CswNbtAuditTableAbbreviation.getAuditTableSql(_CswNbtMetaDataResources.CswNbtResources, _TableSelect.TableName, Date) + " " + _TableSelect.TableName + " ";
addModuleWhereClause(ref Where);
Sql += Where;
CswArbitrarySelect AuditSelect = _CswNbtMetaDataResources.CswNbtResources.makeCswArbitrarySelect("MetaDataCollectionImpl_getpkdict_audit_select", Sql);
Table = AuditSelect.getTable();
}
Dictionary <Int32, string> Coll = new Dictionary <Int32, string>();
foreach (DataRow Row in Table.Rows)
{
Coll.Add(CswConvert.ToInt32(Row[_PkColumnName]), CswConvert.ToString(Row[_NameColumnName]));
}
_PkDictsWhere[Key] = Coll;
}
return(_PkDictsWhere[Key]);
} // _PkDictsWhere(Where)
}//delete()
public void fill()
{
if (NodeSpecies == CswEnumNbtNodeSpecies.Plain)
{
//bool NodeInfoFetched = false;
if (CswTools.IsPrimaryKey(NodeId) && (NodeTypeId <= 0 || NodeName == String.Empty))
{
DataTable NodesTable = null;
if (CswTools.IsDate(_Date))
{
string NodesSql = "select * from " + CswNbtAuditTableAbbreviation.getAuditTableSql(_CswNbtResources, "nodes", _Date, NodeId.PrimaryKey);
CswArbitrarySelect NodesTableSelect = _CswNbtResources.makeCswArbitrarySelect("fetchNodeInfo_Select", NodesSql);
NodesTable = NodesTableSelect.getTable();
}
else
{
CswTableSelect NodesTableSelect = _CswNbtResources.makeCswTableSelect("CswNbtNode.fill_nodes", "nodes");
NodesTable = NodesTableSelect.getTable("nodeid", NodeId.PrimaryKey);
}
if (NodesTable.Rows.Count > 0)
{
read(NodesTable.Rows[0]);
RelationalId = new CswPrimaryKey(NodesTable.Rows[0]["relationaltable"].ToString(), CswConvert.ToInt32(NodesTable.Rows[0]["relationalid"]));
}
CswTimer Timer = new CswTimer();
if (getNodeType() != null)
{
Properties.fill(false);
}
_CswNbtResources.logTimerResult("Filled in node property data for node (" + NodeId.ToString() + "): " + NodeName, Timer.ElapsedDurationInSecondsAsString);
if (CswTools.IsDate(_Date))
{
setReadOnly(value: true, SaveToDb: false);
}
}
}
_NodeModificationState = CswEnumNbtNodeModificationState.Unchanged;
}//fill()
} // getWhere(Where)
public Collection <ICswNbtMetaDataObject> getWhere(string Where, CswDateTime Date, bool BypassModuleCheck = false)
{
Collection <ICswNbtMetaDataObject> ret = null;
if (false == CswTools.IsDate(Date))
{
ret = getWhere(Where, BypassModuleCheck);
}
else
{
string Sql = "select * from " + CswNbtAuditTableAbbreviation.getAuditTableSql(_CswNbtMetaDataResources.CswNbtResources, _TableSelect.TableName, Date) + " " + _TableSelect.TableName + " " + Where;
if (false == BypassModuleCheck)
{
addModuleWhereClause(ref Sql);
}
CswArbitrarySelect AuditSelect = _CswNbtMetaDataResources.CswNbtResources.makeCswArbitrarySelect("MetaDataCollectionImpl_getWhere_audit_select", Sql);
DataTable Table = AuditSelect.getTable();
ret = _makeObjs(Table, Date, useCache: false);
}
return(ret);
} // getWhere(Where,Date)
} // getPks(Where)
public Collection <Int32> getPks(string Where, CswDateTime Date)
{
Collection <Int32> ret = null;
if (false == CswTools.IsDate(Date))
{
ret = getPks(Where);
}
else
{
string Sql = "select " + _PkColumnName + " from " + CswNbtAuditTableAbbreviation.getAuditTableSql(_CswNbtMetaDataResources.CswNbtResources, _TableSelect.TableName, Date) + " " + _TableSelect.TableName + " ";
addModuleWhereClause(ref Where);
Sql += Where;
CswArbitrarySelect AuditSelect = _CswNbtMetaDataResources.CswNbtResources.makeCswArbitrarySelect("MetaDataCollectionImpl_getpks_audit_select", Sql);
DataTable Table = AuditSelect.getTable();
ret = new Collection <Int32>();
foreach (DataRow Row in Table.Rows)
{
ret.Add(CswConvert.ToInt32(Row[_PkColumnName]));
}
}
return(ret);
} // getPks(Where)
public JObject getAuditHistoryGrid(CswNbtNode Node, bool JustDateColumn)
{
JObject ret = new JObject();
if (Node != null)
{
string SQL = @"select ja.recordcreated as ChangeDate ";
if (!JustDateColumn)
{
SQL += @", x.transactionusername as Username,
x.auditeventname as Context,
np.propname as Propname,
ja.gestaltsearch as Value,
x.audittransactionid as AuditId,
ft.fieldtype as FieldType ";
}
SQL += @" from jct_nodes_props_audit ja
join audit_transactions x on ja.audittransactionid = x.audittransactionid
join TABLE(" + CswNbtAuditTableAbbreviation.getAuditLookupFunctionNameForRealTable("nodetype_props") +
@"(ja.recordcreated)) np on (np.nodetypepropid = ja.nodetypepropid)
join field_types ft on ft.fieldtypeid = np.fieldtypeid
where ja.nodeid = :nodeid
and x.transactionusername not in (:sysusernames)
order by AuditId desc";
CswCommaDelimitedString sysUserNames = new CswCommaDelimitedString(0, "'");
foreach (CswEnumSystemUserNames sysUserName in CswEnumSystemUserNames.getValues())
{
sysUserNames.Add(sysUserName.ToString());
}
CswArbitrarySelect HistorySelect = _CswNbtResources.makeCswArbitrarySelect("CswNbtWebServiceAuditing_getAuditHistory_select", SQL);
HistorySelect.addParameter("nodeid", Node.NodeId.PrimaryKey.ToString());
HistorySelect.addParameter("sysusernames", sysUserNames.ToString());
DataTable HistoryTable = HistorySelect.getTable();
//for the audit grid we want to group by audittransactionid, but show the changedate
//also, we mask the password value column and not show the encrypted password
string mutatingRowsAuditId = "";
string changeToDate = "";
foreach (DataRow row in HistoryTable.Rows)
{
string currentAuditId = row["AuditId"].ToString();
if (currentAuditId != mutatingRowsAuditId) //we're onto a new group
{
mutatingRowsAuditId = currentAuditId;
changeToDate = row["ChangeDate"].ToString();
}
row["ChangeDate"] = changeToDate;
if (row["FieldType"].ToString().Equals("Password"))
{
row["Value"] = "[password changed]";
}
}
HistoryTable.Columns.Remove("FieldType");
HistoryTable.Columns.Remove("AuditId");
CswNbtGrid g = new CswNbtGrid(_CswNbtResources);
ret = g.DataTableToJSON(HistoryTable, GroupByCol: "ChangeDate", GroupByColType: CswEnumExtJsXType.datecolumn);
}
return(ret);
} // _getAuditHistoryGrid()
public override void update()
{
foreach (string Abbrev in CswNbtAuditTableAbbreviation.Abbreviations)
{
ICswDataDictionaryReader DataDictionary = _CswNbtSchemaModTrnsctn.CswDataDictionary;
CswAuditMetaData CswAuditMetaData = new CswAuditMetaData();
string AuditTable = CswNbtAuditTableAbbreviation.getAuditTableName(Abbrev);
string RealTable = CswAuditMetaData.makeNameOfAuditedTable(AuditTable);
string AuditTablePk = DataDictionary.getPrimeKeyColumn(AuditTable);
string RealTablePk = DataDictionary.getPrimeKeyColumn(RealTable);
string ObjectType = "CSW_" + Abbrev + @"_OBJ_TYPE";
string TableType = "CSW_" + Abbrev + @"_TABLE_TYPE";
string FuncName = CswNbtAuditTableAbbreviation.getAuditLookupFunctionName(AuditTable);
bool requiresNodeId = CswNbtAuditTableAbbreviation.requiresNodeId(RealTable);
CswCommaDelimitedString Columns = new CswCommaDelimitedString();
CswCommaDelimitedString ObjectTypeColumns = new CswCommaDelimitedString();
foreach (string ColumnName in _CswNbtSchemaModTrnsctn.CswDataDictionary.getColumnNames(RealTable))
{
if (ColumnName != "auditlevel")
{
DataDictionary.setCurrentColumn(RealTable, ColumnName);
string OracleType = CswDbVendorOpsOracle._getOracleDataTypeFromPortableDataType(DataDictionary.PortableDataType, DataDictionary.DataTypeSize);
Columns.Add(ColumnName);
ObjectTypeColumns.Add(ColumnName + " " + OracleType);
}
}
//Columns.Add( "recordcreated" );
ObjectTypeColumns.Add("recordcreated date");
string ObjectTypeSql = @"CREATE OR REPLACE TYPE " + ObjectType + " IS OBJECT (" + ObjectTypeColumns.ToString(false) + ");";
string TableTypeSql = @"CREATE OR REPLACE TYPE " + TableType + " AS TABLE OF " + ObjectType + ";";
string FuncSql = @"create or replace function " + FuncName + @" (AsOfDate in Date";
if (requiresNodeId)
{
FuncSql += ", aNodeId in number";
}
FuncSql += @" ) return " + TableType + @" is
ResultTable " + TableType + @";
begin
with audit1 as (select " + AuditTablePk + @", " + RealTablePk + @", auditeventtype
from " + AuditTable + @" a
where a." + AuditTablePk + @" = (select max(" + AuditTablePk + @")
from " + AuditTable + @" a2
where a2.recordcreated <= AsOfDate
and a2." + RealTablePk + @" = a." + RealTablePk + @"))
select " + ObjectType + @"(" + Columns.ToString(false) + @",recordcreated) BULK COLLECT into ResultTable
from (select " + Columns.ToString(false) + @", recordcreated
from " + AuditTable + @"
where " + AuditTablePk + @" in (select " + AuditTablePk + @" from audit1)
and auditeventtype <> 'PhysicalDelete'
union all
select " + Columns.ToString(false) + @", sysdate as recordcreated
from " + RealTable + @"
where " + RealTablePk + @" not in (select " + RealTablePk + @" from audit1))";
if (requiresNodeId)
{
FuncSql += " where nodeid = aNodeId ";
}
FuncSql += @";
RETURN ResultTable;
EXCEPTION
WHEN OTHERS THEN
ResultTable.DELETE;
RETURN ResultTable;
end " + FuncName + @";";
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(
@"declare
object_not_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(object_not_exists, -04043);
begin
execute immediate 'drop type " + TableType + @" force';
exception
when object_not_exists then null;
end;"
);
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(
@"declare
object_not_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(object_not_exists, -04043);
begin
execute immediate 'drop type " + ObjectType + @" force';
exception
when object_not_exists then null;
end;"
);
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(ObjectTypeSql);
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(TableTypeSql);
_CswNbtSchemaModTrnsctn.execArbitraryPlatformNeutralSql(FuncSql);
} // foreach
} //update()
