var ceDataArray = arrayNew(1); var itm = ""; var row = ""; var column = ""; var tmp = ""; var defaultTmp = StructNew(); // Default temp for common fields over each loop var formName = ""; var i = ""; //var commonFieldList = "pageID,formID,dateAdded,dateCreated"; var commonFieldList = "pageID,formID"; var fieldStruct = structNew(); var queryColFieldList = ""; // List to store the column names var queryColPos = ""; // Sort the column name list to be safe var origQueryColFieldList = ListSort(arguments.ceDataQuery.columnList, "textnocase"); // Check that we have a query with values if ( arguments.ceDataQuery.recordCount GTE 1 ){ // Setup the default common fields // get the fields structure for this element fieldStruct = server.ADF.objectFactory.getBean("Forms_1_0").getCEFieldNameData(getCENameByFormID(arguments.ceDataQuery["formID"][1])); } // Check if the query column contains "FIC_" and remove for ( i=1; i LTE ListLen(origQueryColFieldList); i++ ){ currColName = ListGetAt(origQueryColFieldList, i); if ( UCASE(LEFT(currColName, 4)) EQ "FIC_" ) currColName = Replace(currColName, "FIC_", ""); // Add the cleaned name into the list queryColFieldList = ListAppend(queryColFieldList, currColName); } tmp = structNew(); // Set the tmp to the default values from the common fields //tmp = defaultTmp; // add in common fields for( i=1; i lte listLen(commonFieldList); i=i+1 ) { commonField = listGetAt(commonFieldList, i); // handle each of the common fields if( findNoCase(commonField, queryColFieldList) ) tmp[commonField] = arguments.ceDataQuery[commonField][row]; else tmp[commonField] = ""; // do special case work for formID/formName if( commonField eq "formID" ) { if( not len(formName) ) formName = getCENameByFormID(tmp.formID); tmp.formName = formName; } } tmp.values = structNew(); // loop through the field query and build the values structure for( itm=1; itm lte listLen(structKeyList(fieldStruct)); itm=itm+1 ) { column = listGetAt(structKeyList(fieldStruct), itm); // Get the position of the column from the in query queryColPos = listFindNoCase(queryColFieldList, column); // Get the column name from the original column name list if( queryColPos GT 0) tmp.values[column] = arguments.ceDataQuery[ListGetAt(origQueryColFieldList,queryColPos)][row]; else tmp.values[column] = ""; } arrayAppend(ceDataArray, tmp); // Set the view table name from the elementName if a viewName is NOT passed in if ( LEN(TRIM(arguments.viewName)) EQ 0 ) arguments.viewName = getViewTableName(customElementName=arguments.elementName); // Call the SUPER function to build the view table return super.buildRealTypeView(elementName=arguments.elementName, viewName=arguments.viewName); var fieldQuery = ""; var itm = 1; var retQuery = QueryNew("pageid,formid","VarChar,VarChar"); var thisField = ""; if( (len(arguments.ceFormID)) and (arguments.ceFormID GT 0) ) { // get the field query for this element fieldQuery = getElementFieldsByFormID(arguments.ceFormID); // loop through the query and build the default structure for( itm=1; itm LTE fieldQuery.recordCount; itm=itm+1 ) { // replace the FIC_ from the beginning thisField = ReplaceNoCase(fieldQuery.fieldName[itm], "FIC_", "", "all"); // Add the column into the query QueryAddColumn(retQuery, thisField, "VarChar", ArrayNew(1)); } } return retQuery; var i=1; var dataFieldNames = StructKeyList(arguments.elementData); var currFieldName = ""; // Loop over the keys in the "elementData" for ( i=1; i LTE ListLen(dataFieldNames); i++ ){ currFieldName = ListGetAt(dataFieldNames, i); // Check if we need to exclude this field or not if ( ListFindNoCase(excludeFields, currFieldName) LTE 0 ){ // Check if the field contains data, if so then return TRUE if ( LEN(TRIM(arguments.elementData[currFieldName])) GT 0 ) return true; } } return false; // initialize the variables var data_i = 1; var ceFormID = getFormIDByCEName(arguments.customElementName); var ceFieldID = ""; var searchCEFieldName = ""; var searchCEFieldID = ""; var ceFieldName = ""; var pageIDValueQry = QueryNew("temp"); var ceFieldIDNameMap = StructNew(); // sdhardesty fixes 03-27-13 var i = 1; var currFieldName = ''; var newRow = ""; var ceDefaultFieldQry = QueryNew("temp"); var ceFieldQuery = QueryNew("temp"); var getDataPageValueQry = QueryNew("temp"); var ceDataQry = QueryNew("temp"); var distinctPageIDQry = QueryNew("temp"); var currPageIDDataQry = QueryNew("temp"); // end sdhardesty fixes 03-27-13 if (LEN(arguments.customElementFieldName) OR Len(arguments.searchFields)) { // check if queryType is Search if ( arguments.queryType EQ "search" OR arguments.queryType EQ "multi" ) { // get the id's for each item in the list and create a new list of id's for (data_i=1; data_i LTE ListLen(arguments.searchFields); data_i=data_i+1){ searchCEFieldName = "FIC_" & TRIM(ListGetAt(arguments.searchFields,data_i)); searchCEFieldID = ListAppend(searchCEFieldID, getElementFieldID(ceFormID, searchCEFieldName)); } } // convert the CE Field Name Arg to the field ID // check if the field name starts with 'FIC_' if (arguments.customElementFieldName CONTAINS "FIC_") ceFieldID = getElementFieldID(ceFormID, arguments.customElementFieldName); else { ceFieldName = "FIC_" & arguments.customElementFieldName; ceFieldID = getElementFieldID(ceFormID, ceFieldName); } } // special case for versions if ( arguments.queryType eq "versions" ) pageIDValueQry = getPageIDForElement(ceFormID, ceFieldID, arguments.item, "selected", arguments.searchValues, searchCEFieldID); else pageIDValueQry = getPageIDForElement(ceFormID, ceFieldID, arguments.item, arguments.queryType, arguments.searchValues, searchCEFieldID); // Get the default structure for the element fields // Build the query row for the default field values ceDefaultFieldQry = defaultFieldQuery(ceFormID=ceFormID); ceFieldQuery = getElementFieldsByFormID(formID=ceFormID); // Get the mapping of field ID's to Field Names // Example: ceFieldIDNameMap[1011] = "myFieldName" ceFieldIDNameMap = StructNew(); for ( i=1; i LTE ceFieldQuery.recordCount; i++ ){ ceFieldIDNameMap[ceFieldQuery.fieldID[i]] = Replace(ceFieldQuery.fieldName[i], "FIC_", ""); } // Build in the initial query for the CE Data storage ceDataQry = duplicate(ceDefaultFieldQry); getDataPageValueQry = getDataFieldValue(pageID=ValueList(pageIDValueQry.pageID)); SELECT DISTINCT PageID FROM getDataPageValueQry SELECT * FROM getDataPageValueQry WHERE pageid = #distinctPageIDQry.pageid# // Check if we are processing the selected list if ( arguments.queryType EQ "selected" and len(arguments.customElementFieldName) and len(arguments.item) ){ // Order the return data by the order the list was passed in ceDataQry = application.ADF.data.querySort(query=ceDataQry, columnName=arguments.customElementFieldName, columnType="varchar", orderList=arguments.item); } // Flip the query back into the CE Data Array Format return buildCEDataArrayFromQuery(ceDataQuery=ceDataQry); var viewTableName = getViewTableName(customElementName=arguments.customElementName); var ceViewQry = QueryNew("null"); var dataArray = ArrayNew(1); var viewTableExists = false; try { // Verify if the view table exists, create if doesn't exists viewTableExists = verifyViewTableExists(customElementName=arguments.customElementName, viewTableName=viewTableName); // TIMER START //a2 = GetTickCount(); if ( viewTableExists ) { // Switch Case based on the query type switch (arguments.queryType){ case "selected": ceViewQry = getCEDataViewSelected(customElementName=arguments.customElementName, customElementFieldName=arguments.customElementFieldName, item=arguments.item, overrideViewTableName=viewTableName); break; case "notSelected": ceViewQry = getCEDataViewNotSelected(customElementName=arguments.customElementName, customElementFieldName=arguments.customElementFieldName, item=arguments.item, overrideViewTableName=viewTableName); break; case "search": ceViewQry = getCEDataViewSearch(customElementName=arguments.customElementName, searchValues=arguments.searchValues, searchFields=arguments.searchFields, item=arguments.item, customElementFieldName=arguments.customElementFieldName, overrideViewTableName=viewTableName); break; case "searchInList": // To make backwards compatiable, check if the "searchFields" are passed in the "customElementFieldName" arg. if ( LEN(arguments.searchFields) EQ 0 AND LEN(arguments.customElementFieldName) GT 0 ) arguments.searchFields = arguments.customElementFieldName; // To make backwards compatiable, check if the "searchValues" are passed in the "items" arg. if ( LEN(arguments.searchValues) EQ 0 AND LEN(arguments.item) GT 0 ) arguments.searchValues = arguments.item; ceViewQry = getCEDataViewSearchInList(customElementName=arguments.customElementName, searchFields=arguments.searchFields, searchValues=arguments.searchValues, overrideViewTableName=viewTableName); break; case "multi": ceViewQry = getCEDataViewMulti(customElementName=arguments.customElementName, searchFields=arguments.searchFields, searchValues=arguments.searchValues, overrideViewTableName=viewTableName); break; case "list": break; case "numericList": break; case "greaterThan": ceViewQry = getCEDataViewGreaterThan(customElementName=arguments.customElementName, customElementFieldName=arguments.customElementFieldName, item=arguments.item, overrideViewTableName=viewTableName); break; case "between": ceViewQry = getCEDataViewBetween(customElementName=arguments.customElementName, customElementFieldName=arguments.customElementFieldName, item=arguments.item, overrideViewTableName=viewTableName); break; } // TIMER END //b2 = GetTickCount(); //timer2 = "getCEDataView - Query Timer = " & b2-a2; //application.ADF.utils.dodump(ceViewQry, "ceViewQry", false); } else { throw(message="View Table Does Not Exist", detail="View Table Does Not Exist"); } } catch (ANY exception){ application.ADF.utils.dodump(exception, "CFCATCH", false); } if ( ceViewQry.recordCount ) { // Check if we are processing the selected list if ( arguments.queryType EQ "selected" and len(arguments.customElementFieldName) and len(arguments.item) ){ // Order the return data by the order the list was passed in ceViewQry = application.ADF.data.querySort(query=ceViewQry, columnName=arguments.customElementFieldName, columnType="varchar", orderList=arguments.item); } // Flip the query back into the CE Data Array Format dataArray = buildCEDataArrayFromQuery(ceDataQuery=ceViewQry); } return dataArray; var viewTableName = ""; var ceViewQry = QueryNew("null"); // Set the override for the view table name if defined if ( LEN(arguments.overrideViewTableName) ) viewTableName = arguments.overrideViewTableName; else viewTableName = getViewTableName(customElementName=arguments.customElementName); SELECT * FROM #viewTableName# WHERE #arguments.customElementFieldName# > AND #arguments.customElementFieldName# < var viewTableName = ""; var ceViewQry = QueryNew("null"); // Set the override for the view table name if defined if ( LEN(arguments.overrideViewTableName) ) viewTableName = arguments.overrideViewTableName; else viewTableName = getViewTableName(customElementName=arguments.customElementName); SELECT * FROM #viewTableName# WHERE #arguments.customElementFieldName# > var viewTableName = ""; var ceViewQry = QueryNew("null"); var currFieldNum = 1; // Set the override for the view table name if defined if ( LEN(arguments.overrideViewTableName) ) viewTableName = arguments.overrideViewTableName; else viewTableName = getViewTableName(customElementName=arguments.customElementName); SELECT * FROM #viewTableName# WHERE AND ( #ListGetAt(arguments.searchFields, currFieldNum)# = ) var viewTableName = ""; var ceViewQry = QueryNew("null"); // Set the override for the view table name if defined if ( LEN(arguments.overrideViewTableName) ) viewTableName = arguments.overrideViewTableName; else viewTableName = getViewTableName(customElementName=arguments.customElementName); SELECT * FROM #viewTableName# WHERE #arguments.customElementFieldName# NOT IN () WHERE #arguments.customElementFieldName# <> var viewTableName = ""; var ceViewQry = QueryNew("null"); var currFieldNum = 1; var excludeItemQry = QueryNew("null"); var excludePageIDList = ""; // List of Page IDs to exclude from the search results // Set the override for the view table name if defined if ( LEN(arguments.overrideViewTableName) ) viewTableName = arguments.overrideViewTableName; else viewTableName = getViewTableName(customElementName=arguments.customElementName); SELECT * FROM #viewTableName# WHERE OR ( #ListGetAt(arguments.searchFields, currFieldNum)# LIKE ) AND pageid NOT IN () var viewTableName = ""; var ceViewQry = QueryNew("null"); var currFieldNum = 1; // Set the override for the view table name if defined if ( LEN(arguments.overrideViewTableName) ) viewTableName = arguments.overrideViewTableName; else viewTableName = getViewTableName(customElementName=arguments.customElementName); SELECT * FROM #viewTableName# WHERE AND ( #arguments.searchFields# LIKE ) var viewTableName = ""; var ceViewQry = QueryNew("null"); // Set the override for the view table name if defined if ( LEN(arguments.overrideViewTableName) ) viewTableName = arguments.overrideViewTableName; else viewTableName = getViewTableName(customElementName=arguments.customElementName); SELECT * FROM #viewTableName# WHERE #arguments.customElementFieldName# IN () WHERE #arguments.customElementFieldName# = // Initialize the variables var getDataFieldValueQry = queryNew("temp"); SELECT PageID, FormID, FieldID, fieldValue, memoValue FROM Data_FieldValue WHERE PageID IN () WHERE PageID = AND VersionState = 2 AND PageID <> 0 // Initialize the variables var csQry = QueryNew("temp"); SELECT AvailableControls.ID, AvailableControls.ShortDesc AS FormName FROM AvailableControls INNER JOIN FormControlMap ON AvailableControls.ID = FormControlMap.FormID WHERE FormControlMap.ClassID = 1 AND AvailableControls.ElementState = 0 ORDER BY FormName ASC var ceFormID = 0; // Check the arguments that are passed in if ( arguments.customElementFormID GT 0 ) ceFormID = arguments.customElementFormID; else if ( LEN(arguments.customElementName) ) ceFormID = getFormIDByCEName(CEName=arguments.customElementName); // Check that we have a form ID before hand if ( ceFormID GT 0 ) return "ce_view_#ceFormID#"; else return ""; var verifySourceDB = QueryNew("temp"); // Set the view table name if a viewTableName is not passed in if ( LEN(TRIM(arguments.viewTableName)) EQ 0 ) arguments.viewTableName = getViewTableName(customElementName=arguments.customElementName); SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =