variables.elementName = getEventDateTimeCEName();
variables.elementIDfield = "uniqueID";
variables.parentIDfield = "calEventID";
variables.CCAPIName = getCCAPIName(variables.elementName);
variables.elementViewName = getCEViewName(variables.elementName);
if ( LEN(TRIM(arguments.uniqueID)) )
return getEventDateTimeByIDfieldName(IDfieldName=variables.ElementIDfield,IDfieldValue=TRIM(arguments.uniqueID));
else
return application.ptCalendar.ceData.getCEData(variables.ElementName);
var retArray = ArrayNew(1);
if ( LEN(TRIM(arguments.IDfieldName)) )
retArray = application.ptCalendar.ceData.getCEData(variables.ElementName,arguments.IDfieldName,arguments.IDfieldValue);
return retArray;
var dataQry = QueryNew("temp");
var filterDataQry = QueryNew("temp");
SELECT *
FROM #variables.elementViewName#
ORDER BY eventDate, AllDay DESC, StartTime
SELECT *
FROM dataQry
WHERE 1 = 1
AND calEventID =
AND #variables.ElementIDfield# =
AND CAST( eventDate AS DATE )
BETWEEN CAST( AS DATE )
AND CAST( AS DATE )
AND CAST( eventDate AS DATE ) >= CAST( AS DATE )
ORDER BY eventDate, AllDay DESC, StartTime
return getDateTimeQuery(eventID=TRIM(arguments.eventID));
var retStruct = {};
var type = 'sqlDataTypes'; // queryDataTypes, sqlDataTypes
var cacheUnit = 'd';
var cacheExpiresValue = 1;
if ( arguments.useQueryDatatypes )
type = 'queryDateTypes';
try
{
lock timeout="10" name="ptCal_app_dateTimeColumnStruct_#type#" type="exclusive" {
if ( NOT structKeyExists(application.ptCalendar, "columnCache") )
application.ptCalendar.columnCache = {};
if ( NOT structKeyExists(application.ptCalendar.columnCache, "eventDateTime") )
application.ptCalendar.columnCache.eventDateTime = {};
if ( NOT structKeyExists(application.ptCalendar.columnCache.eventDateTime, type) OR arguments.rebuildColumnCache )
application.ptCalendar.columnCache.eventDateTime[type] = {};
// eventDateTime Column Struct Caching
if ( isStruct(application.ptCalendar.columnCache.eventDateTime[type])
AND NOT StructIsEmpty(application.ptCalendar.columnCache.eventDateTime[type])
AND structKeyExists(application.ptCalendar.columnCache.eventDateTime[type], 'expires')
AND dateCompare(now(),application.ptCalendar.columnCache.eventDateTime[type].expires) EQ -1)
{
retStruct = application.ptCalendar.columnCache.eventDateTime[type].struct;
}
else
{
retStruct = getDateTimeColumnStructDirect(arguments.useQueryDatatypes);
// Cache for 1 day
application.ptCalendar.columnCache.eventDateTime[type] = {};
application.ptCalendar.columnCache.eventDateTime[type].struct = retStruct;
application.ptCalendar.columnCache.eventDateTime[type].expires = createODBCDateTime(dateAdd(cacheUnit,cacheExpiresValue,now()));
application.ptCalendar.columnCache.eventDateTime[type].recordCount = structCount(retStruct);
}
} // END: cflock
}
catch ( any ex )
{
errorMsg = "Error building dateTime column struct cache";
errorMsg = errorMsg & "#chr(10)##Server.CommonSpot.UDF.util.structToText(arguments)#";
application.ADF.log.addLogEntry(message=errorMsg,CFCatch=ex,useDatePrefix=true,wantAllStacks=false);
}
return retStruct;
var retStruct = {};
var dateTimeFieldStruct = {};
var colNameStruct = {};
var key = "";
var keyVal = "";
var x = 1;
var xFld = "";
var xVal = "";
var extraFields = "recurrenceID";
var extraDataTypes = "nvarchar"; // ntext, nvarchar, varchar
var fieldExceptionList = "pageID,controlID,formID";
dateTimeFieldStruct = application.ptCalendar.getViewColumnStruct(ceName=variables.elementName); // ,useQueryDataTypes=arguments.useQueryDatatypes- argument removed from function
// Make sure we have at least one valid column from the dateTime VIEW
if ( StructKeyExists(dateTimeFieldStruct,"uniqueID") )
{
// Add the dateTimeFieldStruct fields to the colNameStruct
for (key in dateTimeFieldStruct) {
if ( !ListFindNoCase(fieldExceptionList,key) )
{
// if needed convert the values to CF Query DataTypes
if ( arguments.useQueryDatatypes )
keyVal = application.ptCalendar.convertSQLDataTypeToQueryDataType(dateTimeFieldStruct[key]);
else
keyVal = dateTimeFieldStruct[key];
// Convert the uniqueID field to a dateTimeID field
if ( key EQ "uniqueID" )
retStruct["dateTimeID"] = keyVal;
else
retStruct[key] = keyVal;
}
}
// Loop over the extra fields and dataTypes to be used for the Query Columns
if ( ListLen(extraFields) EQ ListLEN(extraDataTypes) )
{
for ( x=1;x LTE ListLen(extraFields);x=x+1 ) {
xFld = ListGetAt(extraFields,x);
// if needed convert the values to CF Query DataTypes
if ( arguments.useQueryDatatypes )
xVal = application.ptCalendar.convertSQLDataTypeToQueryDataType(ListGetAt(extraDataTypes,x));
else
xVal = ListGetAt(extraDataTypes,x);
retStruct[xFld] = xVal;
}
}
}
return retStruct;
var retStruct = StructNew();
var dateTimeFieldStruct = getDateTimeColumnStruct(useQueryDataTypes=true,rebuildColumnCache=arguments.rebuildColumnCache);
var colNameList = "";
var dataTypeList = "";
var key = "";
// Make sure we have populated structure from dateTime VIEW table
if ( NOT StructIsEmpty(dateTimeFieldStruct) )
{
// Create the list of Column Name to be used for query columns
colNameList = StructKeyList(dateTimeFieldStruct);
// Create the list of DataTypes to be used for query columns
for ( key in dateTimeFieldStruct ) {
dataTypeList = ListAppend(dataTypeList,dateTimeFieldStruct[key]);
}
// Build the query column/datatype struct
retStruct.columnNames = colNameList;
retStruct.dataTypes = dataTypeList;
}
return retStruct;
return getDateTimeColumnStruct(useQueryDataTypes=false,rebuildColumnCache=arguments.rebuildColumnCache);
var retStruct = {};
var cacheUnit = 'd';
var cacheExpiresValue = 1;
try
{
lock timeout="30" name="ptCal_app_dateTimeFieldStruct" type="exclusive"
{
if ( NOT structKeyExists(application.ptCalendar, "fieldCache") )
application.ptCalendar.fieldCache = {};
if ( NOT structKeyExists(application.ptCalendar.fieldCache, "eventDateTime") OR arguments.rebuildFieldCache )
application.ptCalendar.fieldCache.eventDateTime = {};
// eventDateTime Column Struct Caching
if ( isStruct(application.ptCalendar.fieldCache.eventDateTime)
AND NOT StructIsEmpty(application.ptCalendar.fieldCache.eventDateTime)
AND structKeyExists(application.ptCalendar.fieldCache.eventDateTime, 'struct')
AND structKeyExists(application.ptCalendar.fieldCache.eventDateTime, 'expires')
AND dateCompare(now(),application.ptCalendar.fieldCache.eventDateTime.expires) EQ -1)
{
retStruct = application.ptCalendar.fieldCache.eventDateTime.struct;
}
else
{
retStruct = getDateTimeFieldStructDirect(overrideDBtype=arguments.overrideDBtype);
// Cache for 1 day
application.ptCalendar.fieldCache.eventDateTime = {};
application.ptCalendar.fieldCache.eventDateTime.struct = retStruct;
application.ptCalendar.fieldCache.eventDateTime.expires = createODBCDateTime(DateAdd(cacheUnit,cacheExpiresValue,now()));
application.ptCalendar.fieldCache.eventDateTime.recordCount = structCount(retStruct);
}
} // END: cflock
}
catch ( any ex )
{
errorMsg = "Error building dateTime field struct cache";
errorMsg = errorMsg & "#chr(10)##Server.CommonSpot.UDF.util.structToText(arguments)#";
application.ADF.log.addLogEntry(message=errorMsg,CFCatch=ex,useDatePrefix=true,wantAllStacks=false);
}
return retStruct;
var retStruct = StructNew();
var fieldStruct = csFieldsDataTypeStructByCEName(ceName=variables.ElementName);
var dbType = getCSDBType();
var validOverrideDBTypes = "Oracle,MySQL,SQLServer";
var key = "";
var keyVal = "";
var x = "";
var xFld = "";
var xType = "";
var fieldExceptionList = "";
var intType = "";
var vcharType = "";
var textType = "";
var bitType = "";
var longTextType = "";
var csTextTypeFieldList = "Custom Text Area Field,large_textarea,formatted_text_block";
var csLongTextTypeFieldList = "";
var extraFields = "recurrenceID";
var extraDataTypes = "text"; // hidden, text, large_textarea, formatted_text_block
/*Custom Types*/
var uuidType = "";
var hashType = "";
var smallTextType = "";
var bitFieldNameList = "";
var intFieldNameList = "allDay";
var UUIDFieldNameList = "uniqueID,recurrenceID,calEventID,datetimeID,parentRecurrenceID";
var hashFieldNameList = "dtHashID";
var smallTextFieldNameList = "eventDate,startTime,endTime";
var textFieldNameList = "";
// Check if an override DBtype was passed in
if ( LEN(TRIM(arguments.overRideDBtype)) AND ListFindNoCase(validOverrideDBTypes,arguments.overRideDBtype) )
dbType = arguments.overRideDBtype;
// Convert the Fields DataTypes for the dbType
switch (dbtype) {
case 'Oracle':
intType = 'number(12)';
vcharType = "varchar2(255)";
textType = "varchar2(2000)";
bitType = "bit";
longTextType = "long"; //clob?
/*Custom Types*/
smallTextType = "varchar2(50)";
uuidType = "varchar2(35)";
hashType = "varchar2(64)";
break;
case 'MySQL':
intType = 'int UNSIGNED';
vcharType = "varchar(255)";
textType = "mediumtext";
bitType = "bit";
longTextType = "longtext";
/*Custom Types*/
smallTextType = "varchar(50)";
uuidType = "varchar(35)";
hashType = "varchar(64)";
break;
case 'SQLServer':
intType = 'int';
vcharType = "varchar(255)";
textType = "varchar(max)"; //nvarchar(4000)
bitType = "bit";
longTextType = "text";
/*Custom Types*/
smallTextType = "varchar(50)";
uuidType = "varchar(35)";
hashType = "varchar(64)";
break;
}
if ( dbtype neq "MySQL" and siteDBIsUnicode() )
{
vcharType = replaceNoCase(vcharType, "varchar", "nvarchar");
textType = replaceNoCase(textType, "varchar", "nvarchar");
/*Custom Types*/
smallTextType = replaceNoCase(smallTextType, "varchar", "nvarchar");
uuidType = replaceNoCase(uuidType, "varchar", "nvarchar");
hashType = replaceNoCase(hashType, "varchar", "nvarchar");
}
// Make sure we have at least one valid field from the element
if ( StructKeyExists(fieldStruct,"uniqueID") )
{
// Add the extra fields to the fieldStruct data structure
if ( ListLen(extraFields) GT 0 AND ListLen(extraFields) EQ ListLEN(extraDataTypes) )
{
for ( x=1;x LTE ListLen(extraFields);x=x+1 ) {
xFld = ListGetAt(extraFields,x);
xType = ListGetAt(extraDataTypes,x);
fieldStruct[xFld] = StructNew();
fieldStruct[xFld].type = xType;
fieldStruct[xFld].label = xFld;
}
}
// Add the dateTimeFieldStruct fields to the colNameStruct
for (key in fieldStruct) {
if ( !ListFindNoCase(fieldExceptionList,key) )
{
/* Custom Types */
if ( ListFindNoCase(bitFieldNameList,key) )
keyVal = lcase(bitType);
else if ( ListFindNoCase(intFieldNameList,key) )
keyVal = lcase(intType);
else if ( ListFindNoCase(UUIDFieldNameList,key) )
keyVal = lcase(uuidType);
else if ( ListFindNoCase(hashFieldNameList,key) )
keyVal = lcase(hashType);
else if ( ListFindNoCase(smallTextFieldNameList,key) )
keyVal = lcase(smallTextType);
else if ( ListFindNoCase(textFieldNameList,key) )
keyVal = lcase(textType);
// convert the field type to a DB Datatype to CF Query DataTypes
else if ( ListFindNoCase(csTextTypeFieldList,fieldStruct[key]["type"]) )
keyVal = lcase(textType);
else if ( ListFindNoCase(csLongTextTypeFieldList,fieldStruct[key]["type"]) )
keyVal = lcase(longTextType);
else
keyVal = lcase(vcharType);
// Convert the uniqueID field to a dateTimeID field
if ( key EQ "uniqueID" )
retStruct["datetimeid"] = keyVal;
else
retStruct[key] = keyVal;
}
}
}
return retStruct;