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 = StructNew();
var dateTimeFieldStruct = application.ptCalendar.getViewColumnStruct(ceName=variables.elementName,useQueryDataTypes=arguments.useQueryDatatypes);
var colNameStruct = StructNew();
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";
// 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);
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);
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 = "allDay";
var intFieldNameList = "";
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;