// ADF Path to this CFT
variables.cftPath = "/ADF/extensions/customfields/db_hierarchy_selector";
// Local Site Override
//variables.cftPath = "/customfields/db_hierarchy_selector";
var tableQry = QueryNew("Table_Name");
var tableData = QueryNew("temp");
var dbType = request.Site.SiteDBType;
var sqlFromTable = "INFORMATION_SCHEMA.TABLES"; // SQLServer and MySQL schema table name
var oracleFromTable = "USER_TAB_COLUMNS"; // Oracle schema table name
var excludeTableList = "sysdiagrams,USER_PASSWORDS,USER_TOKEN";
select *
from #sqlFromTable#
where TABLE_TYPE !=
and TABLE_NAME NOT IN (#ListQualify(excludeTableList,"'",",","all")#)
order by TABLE_NAME
SELECT Table_Name
FROM tableData
WHERE TABLE_TYPE !=
var columnQry = QueryNew("COLUMN_NAME");
//var columnData = QueryNew("temp");
var dataQry = QueryNew("Table_Name");
select DISTINCT #arguments.activeField#
from #arguments.tableName#
order by #arguments.activeField#
var dataQry = QueryNew('');
select #arguments.parentField# AS ParentField, #arguments.valueField# AS ValueField, #arguments.displayField# AS DisplayField
from #arguments.tableName#
where #arguments.activeField# #arguments.activeOperator#
order by #arguments.sortCriteriaStr#
order by #arguments.parentField#, #arguments.sortField# asc, #arguments.displayField# asc
order by #arguments.parentField#, #arguments.displayField# asc
if ( !StructKeyExists(Request, 'objectHierarchyCSS') )
{
application.ADF.scripts.loadUnregisteredResource('#variables.cftPath#/db_hierarchy_selector_styles.css', "Stylesheet", "head", "secondary", 0, 0);
request.objectHierarchyCSS = 1;
}
var dataMemArray = ArrayNew(1);
var inputProps = arguments.propertiesStruct;
if ( StructKeyExists(application, 'dbHierarchyCustomField')
AND StructKeyExists(Application.dbHierarchyCustomField, arguments.elementID)
AND StructKeyExists(Application.dbHierarchyCustomField[arguments.elementID], arguments.fieldID)
AND StructKeyExists(Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID], 'cache')
AND ArrayLen( Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].cache ) gt 0
)
{
dataMemArray = Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].cache;
}
//writeDump(var=dataMemArray, expand=false);
return dataMemArray;
var getRecords = '';
var isMemGood = 1;
var cacheLastUpdate = '';
var fieldProperties = arguments.propertiesStruct;
// var objLastUpdate = request.site.availControls[arguments.elementID].lastUpdateSinceRestart;
var eID = fieldProperties.customElement; //TODO: Update to DB info - table
var objLastUpdate = request.site.availControls[eID].lastUpdateSinceRestart;
var cacheData = ArrayNew(1);
//var cfmlFilterCriteria = StructNew();
var filterArray = ArrayNew(1);
var defaultSortColumn = '';
var i = 0;
var memoryCache = StructNew();
var cachedFilterArray = ArrayNew(1);
var currentFilterArray = ArrayNew(1);
var z = 0;
if (StructKeyExists(application,'dbHierarchyCustomField')
AND StructKeyExists(application.dbHierarchyCustomField, eID)
AND StructKeyExists(application.dbHierarchyCustomField[eID], arguments.fieldID))
{
memoryCache = application.dbHierarchyCustomField[eID][arguments.fieldID];
}
if (NOT StructIsEmpty(memoryCache))
{
cacheLastUpdate = memoryCache.lastUpdate;
cacheData = memoryCache.cache;
if ( ArrayLen(cacheData) AND (NOT application.ADF.date.IsRealDate(objLastUpdate) OR DateCompare(cacheLastUpdate, objLastUpdate) eq 1) )
isMemGood = 1;
else
isMemGood = 0;
if (isMemGood)
{
if ( memoryCache.datasource NEQ fieldProperties.datasource
OR memoryCache.tablename NEQ fieldProperties.tablename
OR memoryCache.parentField NEQ fieldProperties.parentField
OR memoryCache.valueField NEQ fieldProperties.valueField
OR memoryCache.activeField NEQ fieldProperties.activeField
OR memoryCache.activeOperator NEQ fieldProperties.activeOperator
OR memoryCache.activeValue NEQ fieldProperties.activeValue
OR memoryCache.sortField NEQ fieldProperties.sortField
OR memoryCache.sortCriteriaStr NEQ fieldProperties.sortCriteriaStr
OR memoryCache.rootValue NEQ fieldProperties.rootValue
OR memoryCache.rootNodeText NEQ fieldProperties.rootNodeText )
isMemGood = 0;
}
if (isMemGood)
{
/*
if (StructKeyExists(fieldProperties, 'filterCriteria') AND IsWDDX(fieldProperties.filterCriteria))
{
cfmlFilterCriteria = Server.CommonSpot.UDF.util.WDDXDecode(fieldProperties.filterCriteria);
filterArray = cfmlFilterCriteria.filter.serSrchArray;
defaultSortColumn = cfmlFilterCriteria.defaultSortColumn;
}
if (NOT ArrayLen(filterArray))
filterArray[1] = '| element_datemodified| element_datemodified| <= | | c,c,c| | ';
// check if filter contains #. If so assume filter has expression and make dynamic
for( z=1; z lte ArrayLen(filterArray); z=z+1 )
{
if( Find( '##', filterArray[z] ) )
{
isMemGood = 0;
break;
}
}
*/
if ( memoryCache.defaultSortColumn NEQ defaultSortColumn )
isMemGood = 0;
else
{
cachedFilterArray = memoryCache.filterArray;
currentFilterArray = filterArray;
if ( (ArrayLen(cachedFilterArray) AND NOT ArrayLen(currentFilterArray))
OR (ArrayLen(currentFilterArray) AND NOT ArrayLen(cachedFilterArray))
OR ArrayLen(currentFilterArray) NEQ ArrayLen(cachedFilterArray) )
isMemGood = 0;
else
{
for (i=1;i LTE ArrayLen(currentFilterArray);i=i+1)
{
if (currentFilterArray[i] NEQ cachedFilterArray[i])
{
isMemGood = 0;
break;
}
}
}
}
}
}
else
{
isMemGood = 0;
}
return isMemGood;
var ceData = QueryNew('');
var inputPropStruct = arguments.propertiesStruct;
var parentFieldName = '';
var displayFieldName = '';
var valueFieldName = '';
var fieldList = '';
var errorMsg = '';
var getFormattedData = QueryNew('');
var dataArray = ArrayNew(1);
var arrayIndex = 1;
var filterArray = ArrayNew(1);
var sortColumn = '';
var sortDir = 'asc';
var addedParents = StructNew();
var defaultSortColumn = '';
var dataSource = '';
var tablename = '';
var activeField = '';
var activeOperator = '=';
var activeValue = '';
var sortCriteriaStr = '';
// Set the defaults
if ( StructKeyExists(inputPropStruct,"datasource") AND LEN(TRIM(inputPropStruct.datasource)) )
dataSource = inputPropStruct.datasource;
if ( StructKeyExists(inputPropStruct,"tablename") AND LEN(TRIM(inputPropStruct.tablename)) )
tablename = inputPropStruct.tablename;
if ( StructKeyExists(inputPropStruct,"parentField") AND LEN(TRIM(inputPropStruct.parentField)) )
parentFieldName = inputPropStruct.parentField;
if ( StructKeyExists(inputPropStruct,"valueField") AND LEN(TRIM(inputPropStruct.valueField)) )
valueFieldName = inputPropStruct.valueField;
if ( StructKeyExists(inputPropStruct,"displayField") AND LEN(TRIM(inputPropStruct.displayField)) )
displayFieldName = inputPropStruct.displayField;
if ( StructKeyExists(inputPropStruct,"activeField") AND LEN(TRIM(inputPropStruct.activeField)) )
activeField = inputPropStruct.activeField;
if ( StructKeyExists(inputPropStruct,"activeOperator") AND LEN(TRIM(inputPropStruct.activeOperator)) )
activeOperator = inputPropStruct.activeOperator;
if ( StructKeyExists(inputPropStruct,"activeValue") AND LEN(TRIM(inputPropStruct.activeValue)) )
activeValue = inputPropStruct.activeValue;
if ( StructKeyExists(inputPropStruct,"sortField") AND LEN(TRIM(inputPropStruct.sortField)) )
defaultSortColumn = inputPropStruct.sortField;
if ( StructKeyExists(inputPropStruct,"sortCriteriaStr") AND LEN(TRIM(inputPropStruct.sortCriteriaStr)) )
sortCriteriaStr = inputPropStruct.sortCriteriaStr;
getFormattedData = application.ADF.dbHierarchySelector.getTreeData(
dataSource=datasource
,tableName=tablename
,parentField=parentFieldName
,valueField=valueFieldName
,displayField=displayFieldName
,activeField=activeField
,activeOperator=activeOperator
,activeValue=activeValue
,sortField=defaultSortColumn
,sortCriteriaStr=sortCriteriaStr
);
//writeDump(var=getFormattedData, expand=false);
if( inputPropStruct.RootNodeText neq '' )
{
arrayIndex = 1;
dataArray[arrayIndex] = StructNew();
dataArray[arrayIndex]['id'] = '#arguments.fieldID#_#inputPropStruct.rootValue#';
dataArray[arrayIndex]['text'] = inputPropStruct.rootNodeText;
dataArray[arrayIndex]['parent'] = '##';
// "state": {"opened" : '' / true, "selected" : '' / true } - ''=false
dataArray[arrayIndex]["state"] = StructNew();
dataArray[arrayIndex]["state"]["opened"] = '';
dataArray[arrayIndex]["state"]["selected"] = '';
}
addedParents[inputPropStruct.rootValue] = StructNew();
// if the parent node has not already been added, add it and store off its offset into the array.
// They must exist before child nodes.
if( NOT StructKeyExists( addedParents, getFormattedData.ParentField ) )
{
arrayIndex = ArrayLen(dataArray) + 1;
dataArray[arrayIndex] = StructNew();
addedParents[getFormattedData.ParentField] = arrayIndex;
}
// If this node was already added into the array (processed a child already), get offset and
// add info to that structure
if( StructKeyExists( addedParents, getFormattedData.ValueField ) )
{
arrayIndex = addedParents[getFormattedData.ValueField];
}
else // otherwise append to the array
{
arrayIndex = ArrayLen(dataArray) + 1;
addedParents[getFormattedData.ValueField] = arrayIndex;
}
dataArray[arrayIndex] = StructNew();
dataArray[arrayIndex]['id'] = '#arguments.fieldID#_#getFormattedData.ValueField#';
dataArray[arrayIndex]['text'] = "#getFormattedData.DisplayField#";
if( getFormattedData.ParentField EQ inputPropStruct.rootValue )
{
if( inputPropStruct.RootNodeText neq '' )
dataArray[arrayIndex]['parent'] = '#arguments.fieldID#_#inputPropStruct.rootValue#';
else
dataArray[arrayIndex]['parent'] = '##';
}
else
dataArray[arrayIndex]['parent'] = '#arguments.fieldID#_#getFormattedData.ParentField#';
// "state": {"opened" : '' / true, "selected" : '' / true } ''=false
dataArray[arrayIndex]["state"] = StructNew();
dataArray[arrayIndex]["state"]["opened"] = '';
dataArray[arrayIndex]["state"]["selected"] = '';
errorMsg = ListRest(valueFieldName,':');
errorMsg = "Error occurred while trying to retrieve data the fields for the element.";
if (NOT StructKeyExists(Application, 'dbHierarchyCustomField'))
Application.dbHierarchyCustomField = StructNew();
if (NOT StructKeyExists(Application.dbHierarchyCustomField, arguments.elementID))
Application.dbHierarchyCustomField[arguments.elementID] = StructNew();
if (NOT StructKeyExists(Application.dbHierarchyCustomField[arguments.elementID], arguments.fieldID))
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID] = StructNew();
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].lastUpdate = Request.FormattedTimestamp;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].cache = dataArray;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].datasource = datasource;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].tablename = tablename;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].parentField = parentFieldName;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].displayField = displayFieldName;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].valueField = valueFieldName;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].activeField = activeField;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].activeOperator = activeOperator;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].activeValue = activeValue;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].sortField = defaultSortColumn;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].sortCriteriaStr = sortCriteriaStr;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].rootValue = propertiesStruct.rootValue;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].rootNodeText = propertiesStruct.rootNodeText;
//Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].filterArray = filterArray;
Application.dbHierarchyCustomField[arguments.elementID][arguments.fieldID].defaultSortColumn = defaultSortColumn;