I am trying to create a calendar with data from sql query. I don't know how to use defn.filter or how to add arguments to filter the data. (the syntax)


function getEvents as c (e as p)
dim month as c
dim year as c
dim startDate as d
dim endDate as d
startDate = date_value(val(e._year),val(e._month),1)

dim jsonData as c
dim def as p

'The a5_getCalendarEventDataJSON() function retrieves the events for a particular month in a json format.
'The function takes a dot variable that must have these properties:

'defn.type = "sql" or "dbf"
'defn.connectionstring
'defn.tableName - name of the table that has the event data. can also be a sql select statement when defn.type is sql
'defn.startDate - character value - start date of query. date must be in form yyyy-mm-dd
'defn.endDate - character value - end date of query. date must be in form yyyy-mm-dd
'defn.filter - an optional filter to apply to the query

dim defn as p
defn.type = "sql"
defn.connectionString = "::Name::conn"
defn.tableName = <<%sql%
SELECT DISTINCT ID as OrderId, eventName, Data as orderDate, eventdescription FROM dbo.Calendar
%sql%

defn.startDate = year(startDate) + "-" + month(startDate) + "-" + day(startDate)
defn.endDate = year(endDate) + "-" + month(endDate) + "-" + day(endDate)
defn.eventDate = "orderDate"
defn.eventId = "orderId"
defn.eventName = "eventName"
defn.eventDescription = "eventdescription"

jsonData = a5_GetCalendarEventDataJSON(defn)

dim js as c = ""

'get a pointer to the calendar object
'then put the json data in the _data property of the object
'finally, call the calendar object's .refresh() method.
js = js + "var _cal = {dialog.object}.getControl('DT1');" + crlf()
js = js + "_cal._data = " + jsonData + ";" + crlf()
js = js + "_cal.refresh();"
getEvents = js
end function