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_1").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 ) { // Set the commonField to work with commonField = listGetAt(commonFieldList, i); // handle each of the common fields if( findNoCase(commonField, queryColFieldList) and StructKeyExists(arguments.ceDataQuery,commonField) ) tmp[commonField] = arguments.ceDataQuery[commonField][row]; else tmp[commonField] = ""; // do special case work for formID/formName if ( commonField eq "formID" ) { // Get the FormName from the FormID if( not len(formName) and StructKeyExists(tmp,commonField) and IsNumeric(tmp[commonField]) ) formName = getCENameByFormID(tmp[commonField]); // Set the Value for the formName in the tmp Struct 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 arguments.viewName = trim(arguments.viewName); if (len(arguments.viewName) eq 0) arguments.viewName = getViewTableName(customElementName=arguments.elementName); // Call the SUPER function to build the view table return super.buildRealTypeView(argumentCollection=arguments); var buildNow = arguments.forceRebuild or (structKeyExists(Request.Params, "adfRebuildSQLViews") and Request.Params.adfRebuildSQLViews eq 1); arguments.viewName = trim(arguments.viewName); if (arguments.viewName eq "" ) arguments.viewName = getViewTableName(customElementName=arguments.ceName); // have to calc default name here, to check if view exists; should use same algorithm as buildRealTypeView buildNow = buildNow or not server.ADF.objectFactory.getBean("data_1_2").verifyTableExists(tableName=arguments.viewName); if ( buildNow ) return buildRealTypeView ( elementName=arguments.ceName, viewName=arguments.viewName, fieldTypes=arguments.fieldTypes ); return true; var charLimit = 30; // Oracle VIEW table name character limitation var vNamePrefix = "vCE_"; var vNameSuffix = ""; var dbType = Request.Site.SiteDBType; // Convert space in element to underscores arguments.ceName = reReplace(arguments.ceName, "[\s]", "_", "all"); if ( dbType EQ "Oracle" ) { // Set the Oracle character limit by subtracking the VIEW prefix length charLimit = charLimit - LEN(vNamePrefix); // Set the Oracle character limit by subtracking the VIEW suffix length charLimit = charLimit - LEN(vNameSuffix); if ( len(arguments.ceName) gt charLimit ) throw("[ceData.getCEViewName] Custom element name is too long to create a view name that's valid on Oracle."); // TODO: is this really what we should do here } return vNamePrefix & arguments.ceName & vNameSuffix; 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 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(); var sFldsLen = 0; var prevPageID = 0; var getDataPageValueQrySORTED = ""; // 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 sFldsLen = ListLen(arguments.searchFields); for (data_i=1; data_i LTE sFldsLen; 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, arguments.itemListDelimiter); else pageIDValueQry = getPageIDForElement(ceFormID, ceFieldID, arguments.item, arguments.queryType, arguments.searchValues, searchCEFieldID, arguments.itemListDelimiter); // 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),formid=ceFormID); SELECT * FROM getDataPageValueQry ORDER BY 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 // --IMPORTANT: We CAN NOT use the local 'variables.data.QuerySortByOrderedList' since this LIB is extended by the general_chooser.cfc ceDataQry = server.ADF.objectFactory.getBean("data_1_2").QuerySortByOrderedList(query=ceDataQry, columnName=arguments.customElementFieldName, columnType="varchar", orderList=arguments.item, orderListDelimiter = arguments.ItemListDelimiter); } // Flip the query back into the CE Data Array Format return buildCEDataArrayFromQuery(ceDataQuery=ceDataQry); var getDataPageValueQrySORTED = arguments.qry; var ceDataQry = arguments.qry2; var newrow = ''; var currPageIDDataQry = ''; var prevPageID = 0; 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 = buildView(ceName=arguments.customElementName, viewName=viewTableName, forceRebuild=arguments.forceRebuild); // 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, itemListDelimiter=arguments.itemListDelimiter); break; case "notSelected": ceViewQry = getCEDataViewNotSelected(customElementName=arguments.customElementName, customElementFieldName=arguments.customElementFieldName, item=arguments.item, overrideViewTableName=viewTableName, itemListDelimiter=arguments.itemListDelimiter); 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(TRIM(arguments.searchFields)) EQ 0 AND LEN(arguments.customElementFieldName) ) arguments.searchFields = arguments.customElementFieldName; // To make backwards compatiable, check if the "searchValues" are passed in the "items" arg. if ( LEN(TRIM(arguments.searchValues)) EQ 0 AND LEN(arguments.item) ) arguments.searchValues = arguments.item; ceViewQry = getCEDataViewSearchInList(customElementName=arguments.customElementName, searchFields=arguments.searchFields, searchValues=arguments.searchValues, overrideViewTableName=viewTableName, searchValuesDelimiter=arguments.itemListDelimiter); break; case "multi": ceViewQry = getCEDataViewMulti(customElementName=arguments.customElementName, searchFields=arguments.searchFields, searchValues=arguments.searchValues, overrideViewTableName=viewTableName); break; case "list": ceViewQry = getCEDataViewList(customElementName=arguments.customElementName, customElementFieldName=arguments.customElementFieldName, item=arguments.item, overrideViewTableName=viewTableName, itemListDelimiter=arguments.itemListDelimiter); break; case "numericList": ceViewQry = getCEDataViewNumericList(customElementName=arguments.customElementName, customElementFieldName=arguments.customElementFieldName, item=arguments.item, overrideViewTableName=viewTableName, itemListDelimiter=arguments.itemListDelimiter); 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, itemListDelimiter=arguments.itemListDelimiter); 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 // --IMPORTANT: We CAN NOT use the local 'variables.data.QuerySortByOrderedList' since this LIB is extended by the general_chooser.cfc ceViewQry = server.ADF.objectFactory.getBean("data_1_2").QuerySortByOrderedList(query=ceViewQry, columnName=arguments.customElementFieldName, columnType="varchar", orderList=arguments.item, orderListDelimiter=arguments.itemListDelimiter); //application.ADF.utils.dodump(ceViewQry, "ceViewQry", false); } // Flip the query back into the CE Data Array Format dataArray = buildCEDataArrayFromQuery(ceDataQuery=ceViewQry); } return dataArray; var viewTableName = ""; var ceViewQry = QueryNew("null"); var dbType = Request.Site.SiteDBType; // Set the escape characters for reserverd words switch (dbType) { case 'Oracle': rwPre = '"'; rwPost = '"'; break; case 'MySQL': rwPre = '`'; rwPost = '`'; break; case 'SQLServer': rwPre = '['; rwPost = ']'; break; } // 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 #rwPre##arguments.customElementFieldName##rwPost# >= AND #rwPre##arguments.customElementFieldName##rwPost# <= var viewTableName = ""; var ceViewQry = QueryNew("null"); var dbType = Request.Site.SiteDBType; // Set the escape characters for reserverd words switch (dbType) { case 'Oracle': rwPre = '"'; rwPost = '"'; break; case 'MySQL': rwPre = '`'; rwPost = '`'; break; case 'SQLServer': rwPre = '['; rwPost = ']'; break; } // 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 #rwPre##arguments.customElementFieldName##rwPost# > 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"); var dbType = Request.Site.SiteDBType; // Set the escape characters for reserverd words switch (dbType) { case 'Oracle': rwPre = '"'; rwPost = '"'; break; case 'MySQL': rwPre = '`'; rwPost = '`'; break; case 'SQLServer': rwPre = '['; rwPost = ']'; break; } // 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 WHERE #rwPre##arguments.customElementFieldName##rwPost# <> 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 var viewTableName = ""; var ceViewQry = QueryNew("null"); var currFieldNum = 1; var theItem = ""; var rwPre = ""; var rwPost = ""; var dbType = Request.Site.SiteDBType; // Set the escape characters for reserverd words switch (dbType) { case 'Oracle': rwPre = '"'; rwPost = '"'; break; case 'MySQL': rwPre = '`'; rwPost = '`'; break; case 'SQLServer': rwPre = '['; rwPost = ']'; break; } // 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 LOWER(#rwPre##arguments.searchFields##rwPost#) = OR LOWER(#rwPre##arguments.searchFields##rwPost#) LIKE OR LOWER(#rwPre##arguments.searchFields##rwPost#) LIKE OR LOWER(#rwPre##arguments.searchFields##rwPost#) LIKE WHERE LOWER(#rwPre##arguments.searchFields##rwPost#) IS OR LOWER(#rwPre##arguments.searchFields##rwPost#) = var viewTableName = ""; var ceViewQry = QueryNew("null"); var dbType = Request.Site.SiteDBType; // Set the escape characters for reserverd words switch (dbType) { case 'Oracle': rwPre = '"'; rwPost = '"'; break; case 'MySQL': rwPre = '`'; rwPost = '`'; break; case 'SQLServer': rwPre = '['; rwPost = ']'; break; } // 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 WHERE #rwPre##arguments.customElementFieldName##rwPost# = 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# dvt WHERE PageID IN ( SELECT DISTINCT PageID FROM Data_FieldValue dfv WHERE dfv.listID IN ( SELECT DISTINCT listID FROM Data_ListItems WHERE pageID = dfv.PageID AND AND StrItemValue = ) AND FormID = dvt.FormID AND VersionState = 2 AND PageID <> 0 ) 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# dvt WHERE PageID IN ( SELECT DISTINCT PageID FROM Data_FieldValue dfv WHERE dfv.listID IN ( SELECT DISTINCT listID FROM Data_ListItems WHERE pageID = dfv.PageID AND ) AND FormID = dvt.FormID AND VersionState = 2 AND PageID <> 0 ) // Initialize the variables var getDataFieldValueQry = queryNew("temp"); SELECT PageID, FormID, FieldID, fieldValue, memoValue FROM Data_FieldValue WHERE WHERE PageID = AND FormID = 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 return super.arrayOfCEDataToQuery(theArray=arguments.theArray, excludeFICfields=arguments.excludeFICfields, excludeTopLevelFieldList=arguments.excludeTopLevelFieldList); return buildView(ceName=arguments.customElementName, viewName=arguments.viewTableName, fieldTypes=arguments.fieldTypes, forceRebuild=arguments.forceRebuild);