var returnArray = ArrayNew(1); var tabStruct = StructNew(); var formTabQuery = ''; select TabDisplayName,TabSortName,ID from formControlTabs where FormID = order by TabSortName tabStruct = StructNew(); tabStruct.name = TabDisplayName; tabStruct.id = ID; if ( recurse ) { tabStruct.fields = getFieldsFromTabID(ID); } ArrayAppend(returnArray,tabStruct); var returnArray = ArrayNew(1); var formFieldQuery = ''; var fieldStruct = StructNew(); select FormInputControlMap.FieldID,FormInputControlMap.ItemPos,FormInputControl.FieldName from FormInputControlMap inner join FormInputControl ON FormInputControl.ID = FormInputControlMap.FieldID where TabID in () order by ItemPos fieldStruct = StructNew(); fieldStruct.FieldID = FieldID; fieldStruct.FieldName = ReplaceNoCase(FieldName, "FIC_", "", "all"); fieldStruct.defaultValues = getFieldDefaultValueFromID(FieldID); ArrayAppend(returnArray,fieldStruct); var rtnStruct = StructNew(); var params = ""; var formFieldQuery = ""; var defaultValues = StructNew(); var multipleFieldQuery = ""; var fieldQuery = ""; var fieldDefaultValues = ''; select FormID from FormInputControlMap where FieldID = select * from multipleFieldQuery where fieldID = fieldDefaultValues = getElementInfoByPageID(pageid=0,formid=formID); rtnStruct = StructNew(); // 2011-05-04 - MFC - Added check for CS 5 to decode the HTML escaped param WDDX. // Check if in CS 5 or lower to decode the HTML in the wddx if ( application.ADF.csVersion LT 6 ) { fieldQuery.params[1] = server.commonspot.udf.data.fromHTML(fieldQuery.params[1]); } params = server.commonspot.udf.util.wddxdecode(fieldQuery.params[1],1); defaultValues = StructNew(); defaultValues.type = fieldQuery.type[1]; if(structKeyExists(params,"req")) { defaultValues.required = params.req; } defaultValues.fieldName = ReplaceNoCase(fieldQuery.fieldName[1], "FIC_", "", "all"); if(len(fieldDefaultValues.values[defaultValues.fieldName])) { defaultValues.defaultValue = fieldDefaultValues.values[defaultValues.fieldName]; } if(structkeyexists(params,"label")) { defaultValues.label = params.label; } if(structkeyexists(params,"vallist") and params.vallist != "") { defaultValues.OptionListSource = "Value List"; defaultValues.OptionList = params.vallist; } if(structkeyexists(params,"VALSOURCE") and params.VALSOURCE eq "element" and structkeyexists(params,"ELEMENTID") and Len(params.ELEMENTID) ) { //Its an optionlist on a list of elements! And they selected an element. Figure out what element it is! defaultValues.OptionListSource = "Custom Element/Metadata/Simple Form Data"; defaultValues.DynamicData = getCENameByFormID(params.ELEMENTID); } if(structkeyexists(params,"val")) { defaultValues.value = params.val; } if(structkeyexists(params,"height")) { defaultValues.height = params.height; } if(structkeyexists(params,"width")) { defaultValues.width = params.width; } if(structkeyexists(params,"maxlength")) { defaultValues.maxlength = params.maxlength; } if(structkeyexists(params,"size")) { defaultvalues.size = params.size; } if(structkeyexists(params,"cols")) { defaultvalues.size = params.cols; } if(structkeyexists(params,"rows")) { defaultvalues.rows = params.rows; } rtnStruct = defaultValues; var multipleFieldQuery = ''; var formFieldQuery = ''; var fieldQuery = ''; var params = StructNew(); select FormID from FormInputControlMap where FieldID = select * from multipleFieldQuery where fieldID = params = server.commonspot.udf.util.wddxdecode(fieldQuery.params[1],1); // 2011-05-04 - RAK - Added check for CS 5 to decode the HTML escaped param WDDX. // Check if in CS 5 or lower to decode the HTML in the wddx if ( application.ADF.csVersion LT 6 ) params = server.commonspot.udf.data.fromHTML(params); // add in some additional params from the query params.type = fieldQuery.type; params.name = fieldQuery.fieldName; // params.description = fieldQuery.description; var ceDataSerialized = ""; var ceData = variables.getCEData(arguments.ceName); var folder = ExpandPath("#arguments.exportFolder#"); var fileName = "#arguments.ceName#--#DateFormat(now(),'YYYY-MM-DD')#-#TimeFormat(now(),'HH-MM')#.txt"; if( !ArrayLen(ceData) ) { //We have no CE data! return an empty string back return ""; } var rowData = ''; var tempStruct = ''; var dataToImport = ""; var ceData = ""; var i = 1; var currentCE = ""; var populateResults = ""; var scheduleArray = ArrayNew(1); var scheduleStruct = ""; var scheduleParams = ""; var returnStruct = StructNew(); returnStruct.success = false; application.ADF.utils.logAppend(application.ADF.utils.doDump(cfcatch,"cfcatch",false,true),"importCEData-Errors.html"); if( !len(dataToImport) ) { returnStruct.msg = "The file could not be read properly. Please see the logs."; return returnStruct; } // Horray! The file existed and had content ceData = server.Commonspot.UDF.util.deserialize(dataToImport); if( !Find('"',dataToImport) ) { //Wrap everything with quotes. for(i=2;i lte ListLen(dataToImport,chr(10)); i++){ rowData = ListGetAt(dataToImport,i,chr(10)); rowData = '"#Replace(rowData,",",'","',"ALL")#"'; dataToImport = ListSetAt(dataToImport,i,rowData,chr(10)); } //Replace the empty strings with Chr(1) and remove all the quotes we just added. dataToImport = Replace(dataToImport,'""',Chr(1),"ALL"); dataToImport = Replace(dataToImport,'"',"","ALL"); } ceData = variables.data.queryToArrayOfStructures(variables.data.csvToQuery(dataToImport)); for(i=1;i <= ArrayLen(ceData);i++){ tempStruct = StructNew(); tempStruct.values = ceData[i]; tempStruct.formName = arguments.ceName; ceData[i] = tempStruct; } if(!ArrayLen(ceData)) { returnStruct.msg = "There was no data to import"; return returnStruct; } arguments.ceName = ceData[1].formName; if ( LEN(TRIM(arguments.ccapiCEName)) ) arguments.ceName = arguments.ccapiCEName; //We have a valid structure! lets do our clean if requested and continue on. if ( arguments.clean ) { variables.deleteByElementName(ceName); } for(i=1;i<=ArrayLen(ceData);i=i+1){ //Create the params for the populate content call currentCE = StructNew(); currentCE.elementType = "custom"; currentCE.submitChange = true; currentCE.submitChangeComment = "Element imported using CE Data import utility."; if(StructKeyExists(ceData[i],"pageID")) { currentCE.dataPageID = ceData[i].pageID; } structAppend(currentCE,ceData[i].values); //Build the populateContent call for the schedule scheduleStruct = StructNew(); scheduleStruct.bean = "csContent_1_0"; scheduleStruct.method = "populateContent"; scheduleStruct.args.elementName = ceName; scheduleStruct.args.data = currentCE; //Add the item to the schedule ArrayAppend(scheduleArray,scheduleStruct); } //Setup the schedule params scheduleParams = StructNew(); scheduleParams.delay = 1; //minutes till next schedule item scheduleParams.tasksPerBatch = 20; //how many tasks to do per iteration //Schedule it! returnStruct.scheduleID = "import-#ceName#"; application.ADF.scheduler.scheduleProcess(returnStruct.scheduleID,scheduleArray,scheduleParams); returnStruct.msg = "Import scheduled successfully!"; returnStruct.elementName = ceName; returnStruct.success = true; return returnStruct; var rtnData = StructNew(); var elementWDDX = ""; var elementQuery = ""; var elementPageID = ""; var elementFormID = ""; select dw.elementData,ci.controlType from controlInstance ci inner join data_wddx dw on ( dw.pageID = ci.pageID and dw.controlID = ci.controlID ) where ci.pageid = and controlName = elementWDDX = elementQuery.elementData; elementWDDX = server.commonspot.udf.util.wddxdecode(elementWDDX); elementPageID = ListGetAt(elementWDDX.lastRecord,1,"|"); elementFormID = elementQuery.controlType; rtnData = getElementInfoByPageID(elementPageID,elementFormID); 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 commonField = ''; var fieldStruct = structNew(); // 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 formName = getCENameByFormID(formID=arguments.ceDataQuery["formID"][1]); fieldStruct = getCEFieldNameData(ceName=formName); } 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, arguments.ceDataQuery.columnList) ) 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); if( listFindNoCase(arguments.ceDataQuery.columnList, column) ) tmp.values[column] = arguments.ceDataQuery[column][row]; } arrayAppend(ceDataArray, tmp); var viewCreated = false; var formID = getFormIDByCEName(arguments.elementName); var fieldsQuery = ""; var fieldDatatypes = ""; var buildMethod = ""; var useFICPrefix = 0; var logViewSQL = 0; // make sure that we actually have a form ID if (formID == "" || formID <= 0) { server.ADF.objectFactory.getBean("log_1_0").logAppend("ERROR: element '#arguments.elementName#' for view '#arguments.viewName#' does not exist#Chr(10)##repeatString("-", 50)#"); return false; } if (arguments.viewName eq "") arguments.viewName = "ce_#arguments.elementName#View"; arguments.viewName = Replace(arguments.viewName, " ", "_", "all"); // Remove the spaces in the name if (structKeyExists(arguments.options, "useFICPrefix") AND arguments.options.useFICPrefix eq 1) useFICPrefix = 1; if (structKeyExists(arguments.options, "logViewSQL") AND arguments.options.logViewSQL eq 1) logViewSQL = 1; else if (structKeyExists(Request.Params, "adfLogViewSQL") and Request.Params.adfLogViewSQL eq 1) logViewSQL = 1; DROP VIEW #arguments.viewName# fieldsQuery = getFormFieldsQuery(formID); fieldDatatypes = getFormFieldDatatypes(fieldsQuery, arguments.fieldTypes); // aggregate version works for all dbs, but pivot is more efficient, so it's used for SQL Server // Oracle supports pivot too, but requires a somewhat different query arrangement than SQL Server, didn't have to time to work that out // MySQL doesn't support pivot at all switch(Request.Site.SiteDBType) { case "SQLServer": buildMethod = _buildPivotView; break; case 'MySQL': case "Oracle": buildMethod = _buildAggregateView; break; } viewCreated = buildMethod(formID, arguments.viewName, fieldsQuery, fieldDatatypes, useFICPrefix, logViewSQL); return viewCreated; var viewCreated = false; var dbType = Request.Site.SiteDBType; var dbTypeStrs = _getDBTypeStrs(); var intType = ""; var memoValueExpr = ""; var andNotEmptyStr = ""; var orIsEmpty = ""; var fieldType = ""; var dataColsList = "MemoValue,FieldValue"; var dataColSpecs = structNew(); var dataCol = ""; var dataColExpr = ""; var fieldNameNoFIC = ""; var colAlias = ""; var createViewResult = ""; var selectExpr = ""; var logMsg = ""; var thisViewName = ""; var indent = repeatString(chr(9), 5); intType = dbTypeStrs.intType; memoValueExpr = dbTypeStrs.memoValueExpr; andNotEmptyStr = dbTypeStrs.andNotEmptyStr; CREATE VIEW ADF_MemoValueData AS ( SELECT FormID, PageID, ControlID, FieldID, VersionState, #memoValueExpr# AS MemoValue FROM Data_FieldValue ) dataColSpecs[dataCol]["selectSQL"] = ""; dataColSpecs[dataCol]["fieldIDList"] = ""; DROP VIEW #arguments.viewName#_#lCase(left(dataCol, 1))# fieldNameNoFIC = replaceNoCase(FieldName, "FIC_", ""); colAlias = FieldName; if (arguments.useFICPrefix eq 0) colAlias = fieldNameNoFIC; fieldType = arguments.fieldDatatypes[fieldNameNoFIC]; if (fieldType != "omit" && (dataCol == "FieldValue" || fieldType == "longText")) { dataColSpecs[dataCol].selectSQL = "#dataColSpecs[dataCol].selectSQL##indent##_escapeSQLReservedWord(FieldID, 1)# AS #_escapeSQLReservedWord(colAlias)#,#chr(10)#"; dataColSpecs[dataCol].fieldIDList = listAppend(dataColSpecs[dataCol].fieldIDList, _escapeSQLReservedWord(FieldID, 1)); } if (dataColSpecs.MemoValue.selectSQL == "") dataColsList = "FieldValue"; // no MemoValue cols, don't need that pivot view dataColExpr = _pick((dataCol == "FieldValue"), "FieldValue", memoValueExpr); if (dataColSpecs.MemoValue.selectSQL == "") thisViewName = arguments.viewName; // no memo cols, build this pivot as the requested view else thisViewName = "#arguments.viewName#_#lCase(left(dataCol, 1))#"; CREATE VIEW #thisViewName# AS SELECT#chr(10)##dataColSpecs[dataCol].selectSQL##indent#PageID, FormID, ControlID FROM ( SELECT FormID, PageID, ControlID, FieldID, #dataCol# FROM Data_FieldValueADF_MemoValueData WHERE FormID = #arguments.formID# AND VersionState = 2 AND PageID > 0 ) temp PIVOT ( MAX (#dataCol#) FOR FieldID IN (#dataColSpecs[dataCol].fieldIDList#) ) AS Value if (arguments.logViewSQL and StructKeyExists(createViewResult,"sql")) server.ADF.objectFactory.getBean("log_1_0").logAppend("#arguments.viewName#: #thisViewName##Chr(10)##createViewResult.sql##chr(10)##repeatString(chr(9), 3)##repeatString("-", 50)#", "ADFlogViewSQL.log"); CREATE VIEW #arguments.viewName# AS SELECT fieldNameNoFIC = replaceNoCase(FieldName, "FIC_", ""); colAlias = FieldName; if (arguments.useFICPrefix eq 0) colAlias = fieldNameNoFIC; colAlias = _escapeSQLReservedWord(colAlias); orIsEmpty = _pick((dbType == "Oracle"), "", " OR LEN(f.#colAlias#) = 0"); selectExpr = ""; dataType = arguments.fieldDatatypes[fieldNameNoFIC]; switch(dataType) { case "omit": break; case "shortText": selectExpr = "f.#colAlias#"; break; case "integer": selectExpr = "CAST(f.#colAlias# AS #intType#) AS #colAlias#"; break; case "float": selectExpr = "CASE WHEN f.#colAlias# IS NULL#orIsEmpty# THEN 0 ELSE CAST(f.#colAlias# AS DECIMAL(7,2)) END AS #colAlias#"; break; default: selectExpr = "CASE WHEN f.#colAlias# IS NULL#orIsEmpty# THEN m.#colAlias# ELSE f.#colAlias# END AS #colAlias#"; } if (dataType != "omit") writeOutput("#indent##selectExpr#,#chr(10)#"); #indent#f.FormID, f.PageID, f.ControlID FROM #arguments.viewName#_f f JOIN #arguments.viewName#_m m ON m.PageID = f.PageID AND m.ControlID = f.ControlID if (arguments.logViewSQL and StructKeyExists(createViewResult,"sql")) server.ADF.objectFactory.getBean("log_1_0").logAppend("#arguments.viewName##Chr(10)##createViewResult.sql##repeatString("-", 50)#", "ADFlogViewSQL.log"); viewCreated = true; logMsg = "[ceData_1_1._buildPivotView] Error building view: #arguments.viewName##Chr(10)##cfcatch.message# #cfcatch.detail#"; if (structKeyExists(cfcatch, "sql")) logMsg = "#logMsg##chr(10)#QUERY SQL:#chr(10)##cfcatch.sql#"; server.ADF.objectFactory.getBean("log_1_0").logAppend(logMsg); var viewCreated = false; var dbType = Request.Site.SiteDBType; var dbTypeStrs = _getDBTypeStrs(); var intType = ""; var andNotEmptyStr = ""; var fieldNameNoFIC = ""; var colAlias = ""; var dataType = ""; var sql = ""; var defaultValue = ""; var indent = repeatString(chr(9), 5) & " "; var createViewResult = ""; var logMsg = ""; var oper = ''; intType = dbTypeStrs.intType; andNotEmptyStr = dbTypeStrs.andNotEmptyStr; CREATE VIEW #arguments.viewName# AS SELECT fieldNameNoFIC = replaceNoCase(FieldName, "FIC_", ""); dataType = arguments.fieldDatatypes[fieldNameNoFIC]; sql = ""; defaultValue = ""; oper = 'MAX'; switch(dataType) { case "omit": break; case "shortText": sql = "FieldValue"; break; case "longText": sql = dbTypeStrs.memoValueExpr; break; case "integer": sql = "CAST(FieldValue AS #intType#)"; defaultValue = "0"; oper = 'SUM'; break; case "float": sql = "CAST(FieldValue AS DECIMAL(7,2))"; defaultValue = "0.0"; oper = 'SUM'; break; } if (dataType != "omit") { if (dataType eq "longText") sql = "CASE WHEN FieldID <> #FieldID# THEN NULL WHEN FieldValue IS NOT NULL#andNotEmptyStr# THEN FieldValue ELSE #sql#"; else { sql = "CASE WHEN FieldID = #FieldID# THEN #sql#"; if (defaultValue != "") sql = "#sql# ELSE #defaultValue#"; } colAlias = FieldName; if (arguments.useFICPrefix eq 0) colAlias = fieldNameNoFIC; colAlias = _escapeSQLReservedWord(colAlias); sql = "#oper#(#sql# END) AS #colAlias#,"; writeOutput(indent & trim(sql) & chr(10)); } PageID, ControlID, FormID FROM Data_FieldValue WHERE FormID = #formID# AND VersionState = 2 AND PageID > 0 GROUP BY PageID, ControlID, FormID viewCreated = true; if (arguments.logViewSQL && structKeyExists(createViewResult, "sql")) server.ADF.objectFactory.getBean("log_1_0").logAppend("#arguments.viewName##Chr(10)##createViewResult.sql##repeatString("-", 50)#", "ADFlogViewSQL.log"); logMsg = "[ceData_1_1._buildAggregateView] Error building view: #arguments.viewName##Chr(10)##cfcatch.message# #cfcatch.detail#"; if (structKeyExists(cfcatch, "sql")) logMsg = "#logMsg##chr(10)#QUERY SQL:#chr(10)##cfcatch.sql#"; server.ADF.objectFactory.getBean("log_1_0").logAppend(logMsg); SELECT fic.FieldName, fic.Type, fic.Params, ficm.FormID, fic.ID AS FieldID, dv.FieldValue AS FieldDefaultValue, cft.ID AS CustomFieldtypeID FROM FormInputControl fic JOIN FormInputControlMap ficm ON ficm.FieldID = fic.ID LEFT OUTER JOIN Data_FieldValue dv ON dv.FormID = ficm.FormID AND dv.FieldID = fic.ID AND PageID = 0 AND VersionState = 2 LEFT OUTER JOIN CustomFieldTypes cft ON cft.Type = fic.Type WHERE ficm.FormID = ORDER BY fic.ID var dataTypes = structNew(); var fieldLogInfo = structNew(); var fieldTypeIndex = structNew(); var fieldsArray = ""; var colAlias = ""; var dataType = ""; var defaultFieldType = "longText"; var maxFieldValueLen = 425; var fieldMaxLength = 0; var hasLongValueList = false; var i = 0; if (structKeyExists(Request.Constants, "dfvFieldvalueColumnMax")) maxFieldValueLen = Request.Constants.dfvFieldvalueColumnMax; // build struct of types for each field alias out of arguments.fieldTypes, which is a list of columns for each spec'd type for (dataType in arguments.fieldTypes) { fieldsArray = listToArray(arguments.fieldTypes[dataType]); for (i = 1; i lte arrayLen(fieldsArray); i = i + 1) { if (dataType eq "defaultTextType") defaultFieldType = arguments.fieldTypes[dataType]; else fieldTypeIndex[fieldsArray[i]] = dataType; } } colAlias = ReplaceNoCase(FieldName, "FIC_", ""); dataType = Type; fieldMaxLength = 0; if (structKeyExists(fieldTypeIndex, colAlias)) // a specific type was requested for this field dataType = fieldTypeIndex[colAlias]; else if (listFindNoCase("calendar,checkbox,date,email,img,text,select", Type)) // CommonSpot field types we know can't be too long to fit in FieldValue dataType = "shortText"; else if (listFindNoCase("request.formattedTimeStamp,request.user.id,request.user.userid,request.page.id,request.subsite.id,createUUID(),now()", FieldDefaultValue)) // default value expressions implying short text dataType = "shortText"; else if (listFindNoCase("linebreak,label", Type)) // CommonSpot field types we know have no value and can be omitted dataType = "omit"; else if (dataType neq "integer" and dataType neq "float") dataType = ""; // flag to check max length hasLongValueList = (structKeyExists(fieldParams, "valList") && len(fieldParams.valList) > maxFieldValueLen) || (structKeyExists(fieldParams, "valCol") && fieldParams.valCol != ""); if (Type == "select") dataType = _pick((structKeyExists(fieldParams, "mult") && fieldParams.mult == "yes") && hasLongValueList, "longText", "shortText"); else if (Type == "multicheckbox") dataType = _pick(hasLongValueList, "longText", "shortText"); else if (CustomFieldtypeID > 0) // custom element { if (_hasAllKeys(fieldParams, "customElement,displayFieldBuilder")) // Custom Element Select dataType = _pick(structKeyExists(fieldParams, "multipleSelect") && fieldParams.multipleSelect == "1", "longText", "shortText"); else if (_hasAllKeys(fieldParams, "standardizedTimeStr,standardizedTimeType") || structKeyExists(fieldParams, "standardizedDateStr")) // Date or Time Picker dataType = "shortText"; else if (_hasAllKeys(fieldParams, "parentField,rootNodeText,selectionType")) // Custom Element Hierarchy Selector dataType = _pick(fieldParams.selectionType == "single", "shortText", "longText"); else if (_hasAllKeys(fieldParams, "chooserCFCName,maxSelections")) // General Chooser dataType = _pick(fieldParams.maxSelections == 0 || val(fieldParams.maxSelections) > 10, "longText", "shortText"); // assumes stored IDs aren't longer than a UUID else if (_hasAllKeys(fieldParams, "assocCustomElement,childCUSTOMElement,childInstanceIDField,childLinkedField,childUniqueField")) // Data Manager dataType = "omit"; // stores no data if (dataType == "longText" and structKeyExists(request, "dbg")) request.dbg(fieldParams); } else if (structKeyExists(fieldParams, "maxLength")) { fieldMaxLength = val(fieldParams.maxLength); if (fieldMaxLength gt maxFieldValueLen) // too long for FieldValue dataType = "longText"; else if (fieldMaxLength gt 0) // max length spec'd and not too long dataType = "shortText"; } if (dataType == "") dataType = defaultFieldType; dataTypes[colAlias] = dataType; fieldLogInfo[colAlias] = Type & "|" & CustomFieldtypeID & "|" & dataType; if (structKeyExists(request, "dbg")) request.dbg(fieldLogInfo); return dataTypes; var result = arguments.expression; switch(Request.Site.SiteDBType) { case "sqlserver": result = "[#arguments.expression#]"; break; case "mysql": result = "`#arguments.expression#`"; break; case "oracle": /* IMPORTANT NOTE: The escaping method commented out below is correct, and works. HOWEVER, queries on the resulting view MUST enclose all column names in double quotes, and use the correct case. Since that's a non-starter, we're opting instead not to escape column names at all for Oracle. Result is that view creation will fail if any element fields have names that are reserved words for Oracle. This will get logged, only, no error will be visible. One approach if you're writing new code to work with an existing element with this issue is to pass options.useFICPrefix=1. The resulting view column names will all have the prefix 'FIC_', so they won't be reserved words. if (!arguments.fieldIDMode) result = '"#arguments.expression#"';*/ break; } return result; var result = structNew(); var uniCodePrefixMaybe = _pick(siteDBIsUnicode(), "N", ""); var isCS9Plus = (val(ListLast(ListFirst(Server.CommonSpot.ProductVersion, "."), " ")) >= 9); switch (Request.Site.SiteDBType) { case "Oracle": result.intType = "number(12)"; result.memoValueExpr = "CAST(SUBSTR(MemoValue, 1, 1800) AS #uniCodePrefixMaybe#VARCHAR2(4000))"; // truncates - 1800 chars with 2 bytes per char (with slack) - max 4000 bytes //result.memoValueExpr = "CAST(SUBSTR(MemoValue, 1, 4000) AS #uniCodePrefixMaybe#VARCHAR2(4000))"; // truncates result.andNotEmptyStr = " AND LENGTH(FieldValue) <> 0"; break; case 'MySQL': // result.intType = "UNSIGNED"; result.intType = "SIGNED"; result.memoValueExpr = "MemoValue"; result.andNotEmptyStr = " AND LENGTH(FieldValue) <> 0"; break; case "SQLServer": result.intType = "int"; if (isCS9Plus) result.memoValueExpr = "MemoValue"; // VARCHAR(MAX) natively, no CAST needed else result.memoValueExpr = "CAST(MemoValue AS #uniCodePrefixMaybe#VARCHAR(MAX))"; result.andNotEmptyStr = " AND LEN(FieldValue) <> 0"; break; } return result; var aKeys = listToArray(arguments.keysList); var count = arrayLen(aKeys); var i = 0; for (i = 1; i <= count; i++) { if (!structKeyExists(arguments.struct, aKeys[i])) return false; } return true; if (arguments.bool) return arguments.value1; return arguments.value2; var retDataPageID = -1; var getDataWDDX = QueryNew("null"); var elementData = StructNew(); SELECT * FROM Data_WDDX WHERE pageID = AND controlID = AND versionState = 2 // Check that we got records if ( getDataWDDX.RecordCount ) { // Transform the WDDX data elementData = server.commonspot.udf.util.wddxdecode(getDataWDDX.ElementData); // Get the data page ID out retDataPageID = ListFirst(elementData.LASTRECORD,"|"); } return retDataPageID; var returnStruct = StructNew(); var srcElements = ""; var tempStruct = StructNew(); var srcElementStruct = ''; var i = ''; var keysToSync = ''; var syncLen = ''; var currentKey = ''; var currentElement = ''; var isDifferent = ''; var j = ''; var currentKeyValue = ''; var newKeyValue = ''; var commandArray = ArrayNew(1); var deleteList = ''; var dataPageIDList = ''; var scheduleParams = ""; var manualCompare = false; var syncKey = ''; var currSrcElementKey = ""; // Stores the current source element key for building the 'srcElementStruct'. var dupSrcDataPageIDList = ""; // List for DataPageIDs for duplicate recs in source data. var newElement = ''; returnStruct.success = false; returnStruct.msg = "An unknown error occurred."; //*********************************************Begin validation*********************************************// if(!Len(arguments.elementName)) { returnStruct.msg = "Element name must be defined."; return returnStruct; } if(!ArrayLen(arguments.newElements)) { returnStruct.msg = "The list of elements to by sync'd is not defined."; return returnStruct; } if(!StructIsEmpty(arguments.updateOverride)) { //The defined an override if(!StructKeyExists(arguments.updateOverride,"bean") || !StructKeyExists(arguments.updateOverride,"method") || !Len(arguments.updateOverride.method) || !Len(arguments.updateOverride.bean)) { returnStruct.msg = "Invalid structure for updateOverride, it must be a structure with keys bean and method which are string values."; return returnStruct; } } else { arguments.updateOverride.bean = "csContent_1_0"; arguments.updateOverride.method = "populateContent"; } arguments.updateOverride.args.elementName = arguments.elementCCAPIName; arguments.updateOverride.args.data = StructNew(); if( !StructIsEmpty(arguments.deleteOverride) ) { //The defined an override if(!StructKeyExists(arguments.deleteOverride,"bean") || !StructKeyExists(arguments.deleteOverride,"method") || !Len(arguments.deleteOverride.method) || !Len(arguments.deleteOverride.bean)) { returnStruct.msg = "Invalid structure for deleteOverride, it must be a structure with keys bean and method which are string values."; return returnStruct; } } else { arguments.deleteOverride.bean = "ceData_1_0"; arguments.deleteOverride.method = "deleteCE"; } arguments.deleteOverride.args.datapageidList = ""; if(!StructIsEmpty(arguments.newOverride)) { //The defined an override if(!StructKeyExists(arguments.newOverride,"bean") || !StructKeyExists(arguments.newOverride,"method") || !Len(arguments.newOverride.method) || !Len(arguments.newOverride.bean)) { returnStruct.msg = "Invalid structure for newOverride, it must be a structure with keys bean and method which are string values."; return returnStruct; } } else { arguments.newOverride.bean = "csContent_1_0"; arguments.newOverride.method = "populateContent"; } arguments.newOverride.args.elementName = arguments.elementCCAPIName; arguments.newOverride.args.data = StructNew(); //*********************************************End Validation*********************************************// /* Goal: Update the elements that have been changed and don't touch those which have not. 1. Get all the existing records (srcElements) a. first serialize the primary key fields and store them in a lookup struct for detection 2. Loop over newElements (arguments.newElements) 3. If the newElement exists in the srcElements record check to see if it changed. 4. If the subjectID does not exist in struct create a new record 5. Loop over remaining subjectID's and delete them */ //1. Get all the existing records (srcElements) if( StructKeyExists(arguments,"syncSourceContent") ) srcElements = arguments.syncSourceContent; else srcElements = getCEData(arguments.elementName); //1a. first serialize the primary key fields and store them in a lookup struct for detection srcElementStruct = StructNew(); for(i=1;i<=ArrayLen(srcElements);i++){ // 2011-07-08 - MFC // Set the source element key to a variable. // Check if the key already exists, then we have a duplicate record. currSrcElementKey = generateStructKey(srcElements[i],arguments.primaryKeys); if ( NOT StructKeyExists(srcElementStruct, currSrcElementKey) ) StructInsert(srcElementStruct,currSrcElementKey,srcElements[i],true); else dupSrcDataPageIDList = ListAppend(dupSrcDataPageIDList, srcElements[i].pageID); } //2. Loop over newElements (arguments.newElements) /* However first lets get a list of keys that will be checked. 1. get a list of all keys 2. Remove from the list the ignored keys */ keysToSync = StructKeyList(arguments.newElements[1].values); for(i=1;i<=ListLen(arguments.ignoreFields);i++){ currentKey = ListGetAt(arguments.ignoreFields,i); if(ListFindNoCase(keysToSync,currentKey)) keysToSync = ListDeleteAt(keysToSync,ListFindNoCase(keysToSync,currentKey)); } syncLen = ListLen(keysToSync); //If the keys on the input struct dont match the keys on the source then we need to manually compare. if(ArrayLen(srcElements) and ListLen(StructKeyList(arguments.newElements[1].values)) neq ListLen(StructKeyList(srcElements[1].values))) manualCompare = true; for(i=1;i<=ArrayLen(arguments.newElements);i++){ newElement = arguments.newElements[i]; //Figure out the element's lookup key currentKey = generateStructKey(newElement,arguments.primaryKeys); // Clear the variable when iterating currentElement = StructNew(); //3. If the newElement exists in the srcElements record check to see if it changed. if( StructKeyExists(srcElementStruct,currentKey) ) { currentElement = srcElementStruct[currentKey]; /* Check to see if it changes... 1. Loop over comparing each key in the sync list 2. If we notice a discrepancy flag it for update. 3. Remove the element from the srcElementStruct since we found it */ isDifferent = false; if( Len(ignoreFields) || manualCompare ) { //Check each key individually for(j=1;j<=syncLen;j++){ syncKey = ListGetAt(keysToSync,j); currentKeyValue = StructFind(currentElement.values,syncKey); newKeyValue = StructFind(newElement.values,syncKey); if(!currentKeyValue.Equals(newKeyValue)) { isDifferent = true; break; } } } else { //check the entire object. Faster. currentKeyValue = currentElement.values; newKeyValue = newElement.values; if(!currentKeyValue.Equals(newKeyValue)) isDifferent = true; } if ( isDifferent ) { //We have a change on our hands! Do something! arguments.updateOverride = variables.data.duplicateStruct(updateOverride); arguments.updateOverride.args.data = newElement.values; arguments.updateOverride.args.data.dataPageID = currentElement.pageID; ArrayAppend(commandArray,arguments.updateOverride); } else { //This guy is not different. Do nothing for now. } StructDelete(srcElementStruct,currentKey); } else { // A new guy eh... arguments.newOverride = variables.data.duplicateStruct(newOverride); arguments.newOverride.args.data = StructNew(); arguments.newOverride.args.data = newElement.values; ArrayAppend(commandArray,arguments.newOverride); } } //5. Loop over remaining subjectID's and delete them if (arguments.preformDelete and !structIsEmpty(srcElementStruct) ) { deleteList = StructKeyList(srcElementStruct); dataPageIDList = ""; for(i=1;i<=ListLen(deleteList);i++){ currentElement = structFind(srcElementStruct,listGetAt(deleteList,i)); dataPageIDList = ListAppend(dataPageIDList,currentElement.pageID); } // 2011-07-08 - MFC - Added Step 5a. // 5a. Get any duplicate records and add to the delete dataPageIDList if ( ListLen(dupSrcDataPageIDList) ) dataPageIDList = ListAppend(dataPageIDList, dupSrcDataPageIDList); // Add the dataPageIDList to the delete command arguments.deleteOverride.args.datapageidList = dataPageIDList; ArrayAppend(commandArray,arguments.deleteOverride); } returnStruct.msg = "Differential sync scheduled successfully!"; returnStruct.success=true; if ( ArrayLen(commandArray) ) { returnStruct.scheduleID=arguments.elementName&"-differentialSync"; scheduleParams = StructNew(); scheduleParams.delay = 1; scheduleParams.tasksPerBatch = 25; application.ADF.scheduler.scheduleProcess(returnStruct.scheduleID,commandArray,scheduleParams); } return returnStruct; var tempStruct = StructNew(); var pkLength = ListLen(arguments.primaryKeys); var i = ""; var currentKey = ""; var keyValue = ""; for(i=1;i<=pkLength;i++){ //Insert into the struct the value from the other struct and keep its level currentKey = ListGetAt(arguments.primaryKeys,i); if ( currentKey == "_pageID" ) //Reserved pageID vkey { StructInsert(tempStruct,currentKey,ToString(arguments.element.pageID),true); } else { // Set the key value and then check if key has a value. keyValue = StructFind(arguments.element.values,currentKey); if ( LEN(keyValue) ) StructInsert(tempStruct,currentKey,StructFind(arguments.element.values,currentKey),true); } } rtn = SerializeJSON(tempStruct); // Encrypt the key to avoid any problems with JSON string as key. return ENCRYPT(rtn, "diffSync", "CFMX_COMPAT", "Hex"); var tabs = ''; var tab = ''; var field = ''; tabs = getTabsFromFormID(getFormIDByCEName(arguments.ceName),true); var csSecurity = server.ADF.objectFactory.getBean("csSecurity_1_2"); var utils = server.ADF.objectFactory.getBean("utils_1_2"); if ( NOT csSecurity.validateProxy("getCEDataSecurity",arguments.customElementName)) { /*Security failed. Append to the log and return nothing useful.*/ utils.logAppend("Get CEData call to non-whitelisted element: #arguments.customElementName#","getCEDataSecurityException.txt"); return ArrayNew(1); } else { /*Passed security! Pass off to parent.*/ return super.getCEData( arguments.customElementName, arguments.customElementFieldName, arguments.item, arguments.queryType, arguments.searchValues, arguments.searchFields); } ---> var dataStruct = StructNew(); var rtnArray = ArrayNew(1); var i = 1; var key = ""; for(i=1;i<=ArrayLen(array1);i++){ StructInsert(dataStruct,array1[i].pageID,array1[i],true); } for(i=1;i<=ArrayLen(array2);i++){ StructInsert(dataStruct,array2[i].pageID,array2[i],true); } for(key in dataStruct){ ArrayAppend(rtnArray,StructFind(dataStruct,key)); } return rtnArray;