my apologies for this question but I'm extremely limited in the coding languages I can use - thus, you are my last bastion of support. Please see below
I want to integrate full calendar.io into my companies local intranet to show holidays for my store managers (around 800) so that area managers can gain visibility of whats happening and when.
I have a resource management system that lets them, the store managers, book holidays and is dumped onto my local server daily with a little help from Python (thank you selenium)
now, my idea was to either setup a simple connection using Python with full calendar to read events from my dB - static only.
However, my companies local webserver doesn't have Python installed and the company sees it as a risk to install anything on it as the webserver is very old.
Okay, cool, step 2 lets use the php file included to read the json and just dump a json file in there daily, it wouldn't be so difficult with python and pandas to setup a workflow daily.
Oh, no php either, the only thing we can use is asp. not asp.net, asp-classic!
so I've been toying around building basic html pages with asp-classic from my ms-sql server. and I've been able to create dynamic pages that pull from my dB (this is on a secured intranet)
please be aware that I work for a fairly large company, any changes take an extremely long time and are bogged down in politics so I won't be getting Python/php or anything installed on the webserver.
I have an ms-sql table like so:
ID : Varchar(255)
Event Start : datetime
Event End : datetime
Area : int
I imagine using something like the following would allow me to generate my events on a html page :
Set gobjConn = Server.CreateObject("ADODB.Connection")
Set grs = Server.CreateObject("ADODB.Recordset")
gsConnect = "Driver={SQL Server};Server=Server;Database=mydb;uid=uid,pw=pw
gobjConn.Open gsConnect
gsSQL = "SELECT ID,[Event Start], [Event End] FROM Events WHERE Area = '" & Area& "'"
Set grs = gobjConn.Execute(gsSQL)
the Area number is a query string I would declare further up within my code.
from here I have no idea how to integrate this into the Jquery Full Calender.
$(document).ready(function() {
$('#calendar').fullCalendar({
header: {
left: 'prev,next today',
center: 'title',
right: 'month,agendaWeek,agendaDay,listWeek'
},
defaultDate: '2019-01-12',
editable: true,
navLinks: true, // can click day/week names to navigate views
eventLimit: true, // allow "more" link when too many events
events: {
url: 'read_my_sql_dB_here',
error: function() {
$('#script-warning').show();
}
},
loading: function(bool) {
$('#loading').toggle(bool);
}
});
});
sorry for the long post, I would imagine its better to be explicit rather than implicit! also please be gentle, until yesterday evening I didn't have a clue what asp-classic was (or is) and I'm a novice coder.
Sounds like you need to generate JSON code using classic asp. There are some JSON classes available for classic ASP (see here), but the JSON code needed for FullCalender.io looks pretty simple and it would be easier to just response.write it rather than generate it using a class.
Try something like this...
events.asp:
<%
Response.ContentType = "application/json"
Dim gobjConn, grs, gsConnect, gsSQL, theData, r, Area
' if you're getting the area int from the querystring it's wise to
' check it's actually an int before inserting it into your SQL
Area = request.QueryString("Area")
if NOT isNumeric(Area) then
Area = 1 ' set a default
'response.End() ' or just use response.End() to stop the script
else
Area = int(Area)
end if
Set gobjConn = Server.CreateObject("ADODB.Connection")
Set grs = Server.CreateObject("ADODB.Recordset")
gsConnect = "Driver={SQL Server};Server=Server;Database=mydb;uid=uid,pw=pw"
gobjConn.Open gsConnect
gsSQL = "SELECT ID,[Event Start], [Event End] FROM Events WHERE Area = " & Area
Set grs = gobjConn.Execute(gsSQL)
if NOT grs.EOF then
' Use GetRows() to convert the recordset to to a 2D array
theData = grs.getRows()
' start to build the JSON
response.write "[" & VBcrlf
for r = 0 to uBound(theData,2)
' loop through the events
response.write " {" & VBcrlf
response.write " ""id"": """ & theData(0,r) & """," & VBcrlf
' If you want to include a title you would need to escape the text:
' response.write " ""title"": """ & JSONEncode(theData(3,r)) & """," & VBcrlf
response.write " ""start"": """ & theData(1,r) & """," & VBcrlf
response.write " ""end"": """ & theData(2,r) & """" & VBcrlf
if r = uBound(theData,2) then
' end of events, no comma
response.write " }" & VBcrlf
else
response.write " }," & VBcrlf
end if
next
response.write "]"
else
' no events
end if
grs.close() : set grs = nothing ' close the recordset
gobjConn.close() : set gobjConn = nothing ' close the connection
' Use this function to escape JSON text
Function JSONEncode(ByVal val)
val = Replace(val, "\", "\\")
val = Replace(val, """", "\""")
val = Replace(val, Chr(8), "\b")
val = Replace(val, Chr(12), "\f")
val = Replace(val, Chr(10), "\n")
val = Replace(val, Chr(13), "\r")
val = Replace(val, Chr(9), "\t")
JSONEncode = Trim(val)
End Function
%>
Using the JSON class linked by @lankymart:
<!--#include file = "jsonObject.class.asp" -->
<%
Response.ContentType = "application/json"
Dim gobjConn, grs, gsConnect, gsSQL, Area
' if you're getting the area int from the querystring it's wise to
' check it's actually an int before inserting it into your SQL
Area = request.QueryString("Area")
if NOT isNumeric(Area) then
Area = 0 ' set a default
'response.End() ' or just use response.End() to stop the script
else
Area = int(Area)
end if
Set gobjConn = Server.CreateObject("ADODB.Connection")
Set grs = Server.CreateObject("ADODB.Recordset")
gsConnect = "Driver={SQL Server};Server=Server;Database=mydb;uid=uid,pw=pw"
gobjConn.Open gsConnect
gsSQL = "SELECT ID,[Event Start], [Event End] FROM Events WHERE Area = " & Area
Set grs = gobjConn.Execute(gsSQL)
set JSON = New JSONarray
JSON.LoadRecordset grs
JSON.Write()
set JSON = nothing
grs.close() : set grs = nothing ' close the recordset
gobjConn.close() : set gobjConn = nothing ' close the connection
%>
In your jQuery:
events: {
url: 'events.asp',
error: function() {
$('#script-warning').show();
}
},