[Article] How to create a weekly planner using html tags

by Riccardo Albieri

One of the hardest tasks a database solution developer can be asked to realize is to build an efficient appointment management system.

In fact, this kind of projects usually includes the chance to present appointments in various formats (the most common are daily, weekly and monthly views).

Amongst the numerous problems the developer must deal with, the construction of the weekly view seems to evidence the most insidious ones: indeed, if the developer decides to give the user the opportunity to distinguish the length of an appointment at first glance, it's necessary to find a way of representing appointments different lengths on screen.

This purpose can be achieved in various ways: from the notorious related records grid (each of them representing the chosen time unit: e.g. from 9:00 to 9:30, in the case of appointments with minimum length of 30 minutes) to a calc fields cluster (in which the calculations show smaller or bigger image files depending on appo's duration). And so on.

Unfortunately, the major part of these approaches share one (or more than one, in worst case scenarios) of the following drawbacks:

• difficult implementation
• poor performances, especially over the network
• insufficient versatility

Just to speak only of the two mentioned examples, the related records grid requires a lot of relationships and usually is not very fast while the cluster doesn't allow to include buttons or interactive elements able to launch scripts inside the appo's graphical representation (those elements are essential in order to let the user modify, edit or delete the appointment, for instance).

Projecting the planner

When I had the need to realize the above described functionality with Servoy, I tried to forget all the strategies I used to adopt in similar circumstances: after all, in Servoy I had a brand new set of tools I wasn't used to, that could probably prove to be very useful (tabpanels, html management, multi-operators relationships etc.)

So I begun to build the system delegating to a series of tabpanels, each of them related to appointments table via a “day + operator” relationship (see picture), the task of showing selected day's appos

No problems in doing that, but I encountered two obstacles from the beginning: every tabpanel, to work properly, needed a dedicated form and, above all, there wasn't a clear distinction, evident at first sight, between a thirty minutes appointment and a three hours one: acceptable for a quick weekly report, unacceptable for a weekly planner.

While I proceeded with my test, Servoy's dev team kept on adding new functionalities, enriching the set of functions related to html management: after having tested some of the new functions in a method built to format a SQL query-generated report, I asked myself if I couldn't use this new set of commands for my purposes.

The advantages, from my point of view, were evident: to get data using SQL is a matter of tenths of second and, once the html has been rendered, viewing and scrolling it on screen   is very fast, since there won't be related fields to update in the UI: I thought that this solution was worth of a try.

At first, I didn't encounter big problems: I created 7 global text fields in agenda table, in order to store the html generated for each day (if you prefer, you can use global fields, instead) and I quickly wrote the method for creating appointments. In this case, there's one important thing to remember: when creating the appointment, be sure to remove all the time part from the field (usually, SQL database use DateTime field, instead of Date field, not completely standard); if you don't do this, you could have problems with the SQL query.

To avoid this kind of problems, I added these lines to the method:

var appo_date = globals.day1
appo_date.setHours(0)
appo_date.setMinutes(00)
appo_date.setSeconds(00)
appo_date.setMilliseconds(000)
agsett1.dateappo = appo_date

The query entitled to retrieve day's appointments belonging to selected operator is quite simple:

query =    "SELECT " +
        "  a.starthour, a.endhour, n.name, n.lastname, i.coloreesa, a.duration, a.appoid, a.noteappo" +
        " from appointments a, operators o, contacts n, categories i" +
        " where a.dataappo = " + "'" + selected_day + "'" +
        " and a.operatoreid = " + operator +
        " and a.operatoreid = o.operatoreid" +
        " and a.anagid = n.anagid" +
        " and i.interventiid = a.tipologid" +
        " order by 1"

I added to the basic set of tables (appointments, operators, contacts) a categories table, storing the colours (in hexadecimal format) linked to the various kinds of appointment: those colours will be used in the html rendering.

Building a function

In order to format the html with data present in the retrieved dataset, I wrote a method and transformed it in a function: this way I could easily re-use it for every day of the week, with no extra code or duplicated methods that would have decreased solution performance and made maintenance more difficult.

This function incorporates a series of methods inside the html code, to let the user interact with the single appointment even if it's stored inside a single html field; furthermore, taking advantage of Servoy's ability to pass parameters directly into the code, it modifies the attributes of every single cell, changing the background colour depending of the category and varying the length of the cell itself depending of appo's duration.

Here's the function's code (I called it agenda_redraw). I put some comment besides the most important steps .

//arg0 = selected date
//arg1 = operator id
//arg2 = field name
var selDate = arguments[0];
var operator = arguments[1];
var htmlfield = arguments[2];
var output = '';

//agenda1_html = ""
var giornata = selDate.getFullYear() + "-" + (selDate.getMonth() +1) + "-" + selDate.getDate()

//create HTML table
var HTML='<html>'+
'<head>'+
'<style type="text/css">'+
'.normal {'+
'    font-family: Arial, Helvetica, Courier, sans-serif;'+
'    font-size: 11pt;'+
'}'+
'.headerUnderlined {'+
'    font-family: Arial, Helvetica, Courier, sans-serif;'+
'    font-size: 12pt;'+
'    font-weight: bold;'+
'    text-decoration: underline;'+
'}'+
'.normalItalic {'+
'    font-family: Arial, Helvetica, Courier, sans-serif;'+
'    font-size: 11pt;'+
'    font-style: italic;'+
'}'+
'</style>'+
'</head>'+
'<table border="0" cellpadding="2" cellspacing="0" width="120" bgcolor="#ffffff">'
        
var query = ""    
    query = "SELECT "+
    "  a.orainizio, a.oratermine, n.nome, n.cognome, i.coloreesa, a.durata, a.appoid, a.noteappo"+
    " from appuntamenti a, operatori o, anagrafica n, interventi i"+
    " where a.dataappo = " + "'" + giornata + "'" + //test also LIKE + "%" after the date: this way the query ignores the time part of the field
    " and a.operatoreid = " + operator +
    " and a.operatoreid = o.operatoreid" +
    " and a.anagid = n.anagid" +
    " and i.interventiid = a.tipologid" +
    " UNION " + //needed to include appos without a contact selected (new contacts, for instance)
    "SELECT "+
    " a.orainizio, a.oratermine,NULL, NULL, i.coloreesa, a.durata, a.appoid, a.noteappo" +
    " from appuntamenti a, operatori o, interventi i" +
    " where a.dataappo = " + "'" + giornata + "'" +
    " and a.operatoreid = " + operator +
    " and a.operatoreid = o.operatoreid" +
    " and a.anagid = 0" +
    " and i.interventiid = a.tipologid" +
    " order by 1"

var dataSet = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 100000);
for(var i=0 ; i<= dataSet.getMaxRowIndex() ; i++)
{
    dataSet.rowIndex= i
    
    //replace null with blank
    var column1 = dataSet[1]
    if(column1 == null) column1 = ""
    else
        if (column1.getMinutes() == 0)
            {
        column1 = column1.getHours() + ":0" + column1.getMinutes()
            }
        else
            {
        column1 = column1.getHours() + ":" + column1.getMinutes()
            }
    var column2 = dataSet[2]
    if(column2 == null) column2 = ""
    else
        if (column2.getMinutes() == 0)
            {
        column2 = column2.getHours() + ":0" + column2.getMinutes()
            }
        else
            {
        column2 = column2.getHours() + ":" + column2.getMinutes()
            }      
    var column3 = dataSet[3]
    if(column3 == null) column3 = ""
        var column4 = dataSet[4]
    if(column4 == null) column4 = ""
        var column5 = dataSet[5]
    if(column5 == null) column5 = ""
        var column6 = (dataSet[6]*15)//per dare la dimensione degli orari
    if(column6 == null) column6 = "0"
        var column7 = dataSet[7]
    if(column7 == null) column7 = "0"
        var column8 = utils.stringLeft(dataSet[8], column6)//needed if you don't want that short appos grow too big if they contain very long notes
    if(column8 == null) column8 = ""
if ( column5 != "" )
{
HTML = HTML + '<table width="120" cellspacing="0" cellpadding="2" bgcolor="'+ column5 +'">140" height=" '+column6 +'"><font size="3" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular,Courier"><b><a href="javascript:globals.Dettagli_appo('+"'"+dataSet[7]+"'"+')">' + column1 +' '+ column2+'</b><br><font size="2" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular,Courier">'+column3+' ' + column4 +'<br><font size="-2"  color="#0000CD">' + column8 + '</td></tr></table>'
}
}

HTML = HTML + '<tr class="normal" bgcolor="#babafe"> <td colspan="3"></tr>'
//end of the table
HTML = HTML+'</table>'+'</html>';
output = HTML
return output;

This way is possible to recall the function from other methods simply passing to it the required parameters.

So, if you want to obtain all the appointments of the first day of the week (for the selected operator), you will only need a very short method like this:

var selected_day = globals.weekbeginning
var agenda_operator = globals.goperator
var HTML = agenda_redraw(selected_day,  agenda_operator, 'agenda1_html');
// these are the requested parameters: selected date, operator and field name
agenda1_html = HTML
controller.show()

Using appropriate navigation techniques and taking care of the initial settings of the agenda (you have to be sure that the agenda opens displaying the correct first day of the week and you have to control the navigation to let the user be free to move back and forward the weekly view, redrawing the html accordingly), can allow you to catch up a good result (see picture).

In my case, this approach solved all the problems related to rendering speed (the fields are populated in a breeze, either changing the operator or moving on a different week), sparing me the creation of a bunch of relationships (in this case, I have only seven of them: one per day of the week. In theory, since everything is managed by methods, it could be possible to build the entire system with no relation at all, letting to SQL the task to retrieve the related records; for this project, I preferred to maintain these relations because I find them helpful in the creation of related records.

The bottom line

This technique seems to give very good performances, without having to renounce to versatility, required in situations like this.

However, my competence on html is quite superficial, so I'm confident this technique can be made more efficient or complete from somebody mastering html better than I do: please, be welcome and don't forget to share the result of your efforts with the rest of us (if your boss agrees, of course). :-)