var isDifferent = false; var tempStructDataA = duplicateStruct(arguments.structDataA); var tempStructDataB = duplicateStruct(arguments.structDataB); var isEqual = compareStructData(structDataA=tempStructDataA,structDataB=tempStructDataB,excludeKeyList=arguments.excludeKeyList,objectFieldKeyList=arguments.objectFieldKeyList); if ( NOT isEqual ) isDifferent = true; return isDifferent; var isEqual = false; var i=1; var currentKey = ""; var dataAObjectValue = ""; var dataBObjectValue = ""; var dataA = duplicateStruct(arguments.structDataA); var dataB = duplicateStruct(arguments.structDataB); // Remove the exclude Keys before doing compare for ( i=1;i LTE ListLen(arguments.excludeKeyList);i=i+1 ) { currentKey = ListGetAt(arguments.excludeKeyList,i); if ( LEN(TRIM(currentKey)) ) { if ( StructKeyExists(dataA,currentKey) ) StructDelete(dataA,currentKey); if ( StructKeyExists(dataB,currentKey) ) StructDelete(dataB,currentKey); } } // Loop over the object fields to compare them individually for ( i=1;i LTE ListLen(arguments.objectFieldKeyList);i=i+1 ) { currentKey = ListGetAt(arguments.objectFieldKeyList,i); if ( LEN(TRIM(currentKey)) ) { // Compare the object fields if ( isJSON(dataA[currentKey]) AND isJSON(dataB[currentKey]) ) { // Set into variables to run the comparison dataAObjectValue = deserializeJSON(dataA[currentKey]); dataBObjectValue = deserializeJSON(dataB[currentKey]); // If not equal, then end all the remaining comparisons if ( NOT dataAObjectValue.EQUALS(dataBObjectValue) ) return false; } // If equal, then remove from the structs for the final compare if ( StructKeyExists(dataA,currentKey) ) StructDelete(dataA,currentKey); if ( StructKeyExists(dataB,currentKey) ) StructDelete(dataB,currentKey); } } // Check the entire object because it is faster. // [2107-01-03 - GAC] .EQUALS() fails under various CF dataType conditions - use new version in data_2_0 if ( dataA.EQUALS(dataB) ) isEqual = true; return isEqual; var retStr = arguments.list; if ( NOT listFindNoCase(arguments.list,arguments.value,arguments.delimiters) ) retStr = listAppend(arguments.list,arguments.value,arguments.delimiters); return retStr; var findStr = "<[^>]*>"; return REREPLACE(arguments.str,findStr,arguments.replaceStr,'all'); return server.commonspot.udf.html.unescape(arguments.str); var bad_chars="#chr(47)#,#chr(92)#,#chr(42)#,#chr(38)#,#chr(37)#,#chr(36)#,#chr(191)#,#chr(198)#,#chr(199)#,#chr(208)#,#chr(209)#,#chr(221)#,#chr(222)#,#chr(223)#,#chr(230)#,#chr(231)#,#chr(240)#,#chr(241)#,#chr(247)#,#chr(248)#,#chr(253)#,#chr(254)#,#chr(255)#"; var good_chars="#chr(45)#,#chr(45)#,#chr(45)#,#chr(45)#,#chr(45)#,#chr(45)#,#chr(45)#,#chr(65)#,#chr(67)#,#chr(68)#,#chr(78)#,#chr(89)#,#chr(73)#,#chr(66)#,#chr(97)#,#chr(99)#,#chr(111)#,#chr(110)#,#chr(45)#,#chr(111)#,#chr(121)#,#chr(49)#,#chr(121)#"; var scrubbed=""; var b = "0"; // A's for (b = 192; b LTE 197; b++) { bad_chars=listAppend(bad_chars,CHR(b)); good_chars=listAppend(good_chars,"A"); } // a's for (b = 224; b LTE 229; b++) { bad_chars=listAppend(bad_chars,CHR(b)); good_chars=listAppend(good_chars,"a"); } // E's for (b = 200; b LTE 203; b++) { bad_chars=listAppend(bad_chars,CHR(b)); good_chars=listAppend(good_chars,"E"); } // e's for (b = 232; b LTE 235; b++) { bad_chars=listAppend(bad_chars,CHR(b)); good_chars=listAppend(good_chars,"e"); } // I's for (b = 204; b LTE 207; b++) { bad_chars=listAppend(bad_chars,CHR(b)); good_chars=listAppend(good_chars,"I"); } // i's for (b = 236; b LTE 239; b++) { bad_chars=listAppend(bad_chars,CHR(b)); good_chars=listAppend(good_chars,"i"); } // 0's for (b = 210; b LTE 216; b++) { bad_chars=listAppend(bad_chars,CHR(b)); good_chars=listAppend(good_chars,"O"); } // o's for (b = 242; b LTE 246; b++) { bad_chars=listAppend(bad_chars,CHR(b)); good_chars=listAppend(good_chars,"o"); } // U's for (b = 217; b LTE 220; b++) { bad_chars=listAppend(bad_chars,CHR(b)); good_chars=listAppend(good_chars,"U"); } // u's for (b = 249; b LTE 252; b++) { bad_chars=listAppend(bad_chars,CHR(b)); good_chars=listAppend(good_chars,"u"); } if ( arguments.fileName eq "" ) return ""; else { arguments.fileName = ReplaceList(trim(arguments.fileName), bad_chars, good_chars); if ( arguments.replaceSpaces ) arguments.fileName = replace(arguments.fileName, " ",arguments.replaceSpaceChar, "all"); if ( arguments.replaceQuotes ) arguments.fileName = replace(arguments.fileName, "'",arguments.replaceQuoteChar, "all"); return arguments.fileName; } var qResult = queryNew("null"); var qColumnsList = arguments.query.columnList; var newColList = ""; var orderCol = ""; var orderTypeDefaults = "asc,desc"; var orderTypeOption = ""; var logSQL = (structKeyExists(Request.Params,"adfLogQuerySQL") and Request.Params.adfLogQuerySQL eq 1); var logMsg = "[data_1_2.QuerySort]"; var createQueryResult = ""; // Protect against reserved words or columns with spaces query column names // - Wrap the query column names in brackets ([]) // - ONLY brackets are needed since this is a CF query of queries newColList = "[" & Replace(qColumnsList,",","],[","all") & "]"; // Make sure the Column that is used to ORDER BY is one of available columns if ( LEN(TRIM(arguments.columnName)) AND ListFindNoCase(qColumnsList,arguments.columnName) ) orderCol = arguments.columnName; if ( ListFindNoCase(orderTypeDefaults,arguments.orderType) ) orderTypeOption = arguments.orderType; // A saftey catch so there is always a custom sort orderColumnAlias defined if ( LEN(TRIM(arguments.orderColumnAlias)) EQ 0 ) arguments.orderColumnAlias = "recSortCol"; // Also make sure the orderColumnAlias is unique and not one of the query columns if ( ListFindNoCase(qColumnsList,arguments.orderColumnAlias) ) arguments.orderColumnAlias = "xNew" & arguments.orderColumnAlias; SELECT #newColList#,LOWER([#orderCol#]) AS [#arguments.orderColumnAlias#] FROM arguments.query ORDER BY [#arguments.orderColumnAlias#] #orderTypeOption# var qResult = queryNew("null"); var qColumnsList = arguments.query.columnList; var orderItem = ""; var columnTypesAllowed = "varchar,numeric,date"; var columnTypeOverride = ""; var newColList = ""; var criteriaValue = ""; var logSQL = (structKeyExists(Request.Params,"adfLogQuerySQL") and Request.Params.adfLogQuerySQL eq 1); var logMsg = "[data_1_2.QuerySortByOrderedList]"; var createQueryResult = ''; // If a columnType is passed in set it as the override Column Type if ( ListFindNoCase(columnTypesAllowed,arguments.columnType) ) columnTypeOverride = arguments.columnType; // Protect against reserved words or columns with spaces query column names // - Wrap the query column names in brackets ([]) // - ONLY brackets are needed since this is a CF query of queries newColList = "[" & Replace(qColumnsList,",","],[","all") & "]"; // Backwards compatiblity code to handle PARAMETER change if ( StructKeyExists(arguments,"orderColumnName") AND LEN(TRIM(arguments.orderColumnName)) ) arguments.orderColumnAlias = arguments.orderColumnName; // A saftey catch so there is always a custom sort orderColumnAlias defined if ( LEN(TRIM(arguments.orderColumnAlias)) EQ 0 ) arguments.orderColumnAlias = "recSortCol"; // Also make sure the orderColumnAlias is unique and not one of the query columns if ( ListFindNoCase(qColumnsList,arguments.orderColumnAlias) ) arguments.orderColumnAlias = "xNew" & arguments.orderColumnAlias; SELECT #newColList#, #orderItem# AS [#arguments.orderColumnAlias#] FROM arguments.query WHERE [#arguments.columnName#] = WHERE CAST([#arguments.columnName#] AS DATE) = CAST( AS DATE) WHERE LOWER([#arguments.columnName#]) = UNION ORDER BY [#arguments.orderColumnAlias#] // if the right 2 digits are 11, 12, or 13, set number to them. // Otherwise we just want the digit in the one's place. var two=Right(arguments.number,2); var ordinal=""; switch(two) { case "11": case "12": case "13": { arguments.number = two; break; } default: { arguments.number = Right(arguments.number,1); break; } } // 1st, 2nd, 3rd, everything else is "th" switch(arguments.number) { case "1": { ordinal = "st"; break; } case "2": { ordinal = "nd"; break; } case "3": { ordinal = "rd"; break; } default: { ordinal = "th"; break; } } // return the text. return ordinal; return cardinalToOrdinal(NumberAsString(number)); var resultString = ""; // Generated result to return var lastCardinal = ""; // Last word in cardinal number string var TempNum = 0; // temp integer var cardinalSpecialStrings = "One,one,Two,two,Three,three,Four,four,Five,five,Six,six,Eight,eight,Nine,nine,Twelve,twelve"; var ordinalSpecialStrings = "First,first,Second,second,Third,third,Fourth,fourth,Fifth,fifth,Sixth,sixth,Eighth,eighth,Ninth,ninth,Twelfth,twelfth"; arguments.cardinalString = trim(arguments.cardinalString); lastCardinal = listLast(arguments.cardinalString," "); resultString = ListDeleteAt(arguments.cardinalString,ListLen(arguments.cardinalString," ")," "); // Is lastCardinal a special case? TempNum = listFindNoCase(cardinalSpecialStrings,lastCardinal); if (TempNum GT 0) { resultString = ListAppend(resultString,ListGetAt(ordinalSpecialStrings,TempNum)," "); } else { if (ListFindNoCase(Right(lastCardinal,2),"en")) { // Last word ends with "en", add "th" resultString = ListAppend(resultString,lastCardinal & "th"," "); } if (ListFindNoCase(Right(lastCardinal,1),"d")) { // Last word ends with "d", add "th" resultString = ListAppend(resultString,lastCardinal & "th"," "); } if (ListFindNoCase(Right(lastCardinal,1),"y")) { // Last word ends with "y", delete "y", add "ieth" resultString = ListAppend(resultString, Left(lastCardinal,Len(lastCardinal) - 1) & "ieth"," "); } if (ListFindNoCase(Right(lastCardinal,3),"ion")) { // Last word ends with "ion", add "th" resultString = ListAppend(resultString,lastCardinal & "th"," "); } } return resultString; var isDifferent = true; var listDifferences = listDiff(list1=arguments.list1,list2=arguments.list2,delimiters=arguments.delimiters); if ( ListLen(listDifferences, arguments.delimiters) EQ 0 ) isDifferent = false; return isDifferent; var listReturn = ""; var position = 1; var value = ""; //checking list1 for ( position = 1; position LTE ListLen(arguments.list1,arguments.delimiters); position = position + 1 ) { value = ListGetAt(arguments.list1, position , arguments.delimiters); if ( ListFindNoCase(arguments.list2, value , arguments.delimiters) EQ 0 ) listReturn = ListAppend(listReturn, value , arguments.delimiters ); } //checking list2 for ( position = 1; position LTE ListLen(arguments.list2,arguments.delimiters); position = position + 1 ) { value = ListGetAt(arguments.list2, position , arguments.delimiters); if ( ListFindNoCase(arguments.list1, value , arguments.delimiters) EQ 0 ) listReturn = ListAppend(listReturn, value , arguments.delimiters ); } return listReturn; var verifyDB = ''; var verifySourceDB = QueryNew("temp"); var dataSource = arguments.dataSourceName; var dbType = arguments.databaseType; //var selectFromTable = "INFORMATION_SCHEMA.TABLES"; // SQLServer and MySQL schema table // CFM 9+ syntax var selectFromTable = (dbType == "Oracle") ? "USER_TAB_COLUMNS" : "INFORMATION_SCHEMA.TABLES"; // SQLServer and MySQL schema table var utilsLib = server.ADF.objectFactory.getBean("utils_1_2"); // Fix for misspelled parameter: dataSourseName if ( StructKeyExists(arguments,"dataSourseName") ) dataSource = arguments.dataSourseName; // Schema Table for ORACLE if ( dbType EQ "Oracle" ) selectFromTable = "USER_TAB_COLUMNS"; // ORACLE requires uppercase DB objects arguments.tableName = uCase(Trim(arguments.tableName)); SELECT TABLE_NAME FROM #selectFromTable# WHERE TABLE_NAME = AND TABLE_SCHEMA = DATABASE() var listItem = ''; var i = 0; for ( i=1; i LTE ListLen(arguments.list,arguments.delimiter); i=i+1 ) { listItem = ListGetAt(arguments.list,i,arguments.delimiter); if ( !isNumeric(listItem) ) return false; } return true; var retStr = ""; var tagEndsRegEx = '(''/>|''/>|"/>|"/>|"/>|"/>|''>|''>|">|">|">|">)'; var commentsRegEx = '(<!--|)'; ||||||||]*[^/])>|]*[^/])>|]*[^/])>", "", "ALL")> ]*\son\w+=.*[ /]*>|||<[^>]*(javascript:)[^>]*>|<[^>]*(onClick:)[^>]*>|<[^>]*(onDblClick:)[^>]*>|<[^>]*(onMouseDown:)[^>]*>|<[^>]*(onMouseOut:)[^>]*>|<[^>]*(onMouseUp:)[^>]*>|<[^>]*(onMouseOver:)[^>]*>|<[^>]*(onBlur:)[^>]*>|<[^>]*(onFocus:)[^>]*>|<[^>]*(onSelect:)[^>]*>","") > ]*>","","all")> ||||||||]*[^/])>|]*[^/])>|<\w+[^>]*\son\w+=.*[ /]*>|<[^>]*(javascript:)[^>]*>|<[^>]*(onClick:)[^>]*>|<[^>]*(onDblClick:)[^>]*>|<[^>]*(onMouseDown:)[^>]*>|<[^>]*(onMouseOut:)[^>]*>|<[^>]*(onMouseUp:)[^>]*>|<[^>]*(onMouseOver:)[^>]*>|<[^>]*(onBlur:)[^>]*>|<[^>]*(onFocus:)[^>]*>|<[^>]*(onSelect:)[^>]*>",arguments.str)> var retData = StructNew(); var paramsData = StructNew(); var aKey = ""; var pKey = ""; var paramDupList = ""; // Rebuild the paramData struct will only the allowed params // !!! if no allowed params are passed, then allow the whole request.params struct to pass through !!! if ( ListLen(arguments.allowedParamList) EQ 0 ) paramsData = arguments.paramsStruct; else { for ( aKey IN arguments.paramsStruct ) { if ( ListFindNoCase(arguments.allowedParamList,aKey,",") ) paramsData[aKey] = arguments.paramsStruct[aKey]; } } // Loop over the paramData from request.Params to build the retData without the excluded params for ( pKey IN paramsData ) { if ( ListFindNoCase(arguments.excludedParamList,pKey,",") EQ 0 AND ListFindNoCase(paramDupList,pKey,",") EQ 0 ) { retData[pKey] = paramsData[pKey]; // Build a key list so we can check for and prevent dups if ( arguments.preventDups ) paramDupList = ListAppend(paramDupList,pKey,","); } } return retData; /** * Works like the built-in function DollarFormat, but does no rounding. * * @param inNum Number to format. (Required) * @param default_var Value to use if number isn't a proper number. (Optional) * @return Returns a string. * @author Shawn Seley (shawnse@aol.com) * @version 1, September 16, 2002 */ var out_str = ""; var decimal_str = ""; var default_value = arguments.inNum; if(ArrayLen(Arguments) GTE 2) default_value = Arguments[2]; if (not IsNumeric(arguments.inNum)) { return (default_value); } else { arguments.inNum = Trim(arguments.inNum); if (ListLen(arguments.inNum, ".") GT 1) { out_str = Abs(ListFirst(arguments.inNum, ".")); decimal_str = "." & ListLast(arguments.inNum, "."); } else if (Find(".", arguments.inNum) EQ 1) { decimal_str = arguments.inNum; } else { out_str = Abs(arguments.inNum); } if (out_str NEQ "") { // add commas out_str = Reverse(out_str); out_str = REReplace(out_str, "([0-9][0-9][0-9])", "\1,", "ALL"); out_str = REReplace(out_str, ",$", ""); // delete potential leading comma out_str = Reverse(out_str); } // add dollar sign (and parenthesis if negative) if(arguments.inNum LT 0) { return ("($" & out_str & decimal_str & ")"); } else { return ("$" & out_str & decimal_str); } } var badChars = "&nbsp;,&amp;,",&ndash;,&rsquo;,&ldquo;,&rdquo;,#chr(12)#"; var goodChars = " ,&,"",–,’,“,”, "; // Return immediately if blank string if (NOT Len(Trim(arguments.inString))) return arguments.inString; // Do replacing return ReplaceList(arguments.inString, badChars, goodChars); var j = 1; var i = 1; var matches = ""; var word = ""; // loop through keywords for ( i=1; i lte ListLen( arguments.searchTerm, " " ); i=i+1 ) { // get current keyword and escape any special regular expression characters word = ReReplace( ListGetAt( arguments.searchTerm, i, " " ), "\.|\^|\$|\*|\+|\?|\(|\)|\[|\]|\{|\}|\\", "", "ALL" ); // return matches for current keyword from string matches = ReMatchNoCase( word, arguments.str ); // remove duplicate matches (case sensitive) matches = CreateObject( "java", "java.util.HashSet" ).init( matches ).toArray(); // loop through matches for( j=1; j <= ArrayLen( matches ); j=j+1 ) { // where match exists in string highlight it arguments.str = Replace( arguments.str, matches[ j ], arguments.preTermStr & matches[ j ] & arguments.postTermStr, "all" ); } } return arguments.str; var colNames = ArrayNew(1); var theQuery = QueryNew("tmp"); var i = 0; var j = 0; var c = 0; var foo = ""; var count = arrayLen(arguments.theArray); var col_num = 0; var item = ""; var firstArray = ArrayNew(1); var columnCount = 0; var newColName = ""; var newColNames = ArrayNew(1); //if there's nothing in the array, return the empty query if ( count eq 0 ) return theQuery; if ( ArrayLen( arguments.theArray ) ) firstArray = arguments.theArray[1]; columnCount = ArrayLen(firstArray); //get the column names into an array = if ( arguments.useFirstArrayAsColNames ) { colNames = firstArray; ArrayDeleteAt(arguments.theArray,1); count = ArrayLen(arguments.theArray); if ( arguments.forceSimpleColNames ) { newColName = ""; newColNames = ArrayNew(1); for ( c=1; c LTE columnCount; c=c+1 ) { //newColName = REREPLACE(colNames[c],"[\s]","","all"); //newColName = REREPLACE(colNames[c],"[^0-9A-Za-z ]","","all"); newColName = REREPLACE(colNames[c],"[^\w]","","all"); ArrayAppend(newColNames,newColName); } colNames = newColNames; } } else { for ( c=1; c LTE columnCount; c=c+1 ) { ArrayAppend(colNames,"column" & c); } } //colNames = structKeyArray(arguments.theArray[1]); col_num = ArrayLen(colNames); //build the query based on the colNames if ( arguments.forceColsToVarchar ) theQuery = queryNew(arrayToList(colNames), RepeatString("varchar,", col_num)); else theQuery = queryNew(arrayToList(colNames)); //add the right number of rows to the query queryAddRow(theQuery, count); //for each element in the array, loop through the columns, populating the query for ( i=1; i LTE count; i=i+1 ) { item = arguments.theArray[i]; for( j=1; j LTE col_num; j=j+1 ) { itemColumn = colNames[j]; itemValue = TRIM(item[j]); querySetCell(theQuery, itemColumn, itemValue, i); } } return theQuery; /* *************************************************************** */ /* Author: Ja Carter (ja@nuorbit.com) Name: $structCompare Summary: Recursive functions to compare structures and arrays. Fix by Jose Alfonso. @version 2, October 14, 2005 Based on the structCompare() by Ja Carter (ja@nuorbit.com) and Jose Alfonso Returns: Boolean Arguments: Struct - LeftStruct Struct - RightStruct Usage: structCompare(LeftStruct,RightStruct) History: 2017-01-03 - GAC - Added */ public boolean function structCompare(LeftStruct,RightStruct) { var result = true; var LeftStructKeys = ""; var RightStructKeys = ""; var key = ""; //Make sure both params are structures if ( NOT ( isStruct(LeftStruct) AND isStruct(RightStruct) ) ) return false; //Make sure both structures have the same keys LeftStructKeys = ListSort(StructKeyList(LeftStruct),"TextNoCase","ASC"); RightStructKeys = ListSort(StructKeyList(RightStruct),"TextNoCase","ASC"); if ( LeftStructKeys neq RightStructKeys ) return false; // Loop through the keys and compare them one at a time for ( key in LeftStruct ) { //Key is a structure, call structCompare() if ( isStruct(LeftStruct[key]) ) { result = structCompare(LeftStruct[key],RightStruct[key]); if (NOT result) return false; } //Key is an array, call arrayCompare() else if ( isArray(LeftStruct[key]) ) { result = arrayCompare(LeftStruct[key],RightStruct[key]); if ( NOT result ) return false; } // A simple type comparison here else { if ( LeftStruct[key] IS NOT RightStruct[key] ) return false; } } return true; } /* *************************************************************** */ /* Author: Ja Carter (ja@nuorbit.com) Name: $arrayCompare Summary: Recursive functions to compare arrays and nested structures. @version 1, September 23, 2004 Returns: Boolean Arguments: Array - LeftArray Array - RightArray Usage: arrayCompare(LeftArray,RightArray) History: 2017-01-03 - GAC - Added */ public boolean function arrayCompare(LeftArray,RightArray) { var result = true; var i = ""; //Make sure both params are arrays if ( NOT (isArray(LeftArray) AND isArray(RightArray)) ) return false; //Make sure both arrays have the same length if ( NOT arrayLen(LeftArray) EQ arrayLen(RightArray) ) return false; // Loop through the elements and compare them one at a time for (i=1;i lte arrayLen(LeftArray); i = i+1) { //elements is a structure, call structCompare() if ( isStruct(LeftArray[i]) ) { result = structCompare(LeftArray[i],RightArray[i]); if (NOT result) return false; } //elements is an array, call arrayCompare() else if ( isArray(LeftArray[i]) ) { result = arrayCompare(LeftArray[i],RightArray[i]); if (NOT result) return false; } //A simple type comparison here else { if ( LeftArray[i] IS NOT RightArray[i] ) return false; } } return true; }