Web Based Calendar Example

[rleunis]

Re: Web Based Calendar Example

RE 1:

a) You can set the table width f.i. to 1200 (if your screen supports this number) , by
<table width="1246" border="0" cellpadding="0" cellspacing="0" align="center" style="width: 1246px; height: 94px ">

b) And set the column width by 150 (when using 8 columns)
but the actual content will force a cell bigger if it does not fits into it anyway.

?"<td vAlign=top width=150 height=60 " + iif(dd=date(),"bgcolor='#fafa80'",iif(between(dd-date()+dow(date()),1,7),"bgcolor='#fafad2'","")) + "><font size=4 style='font-weight:bold;' >&nbsp;" + day(dd)+"</font><br>"+crlf()

Re 2:

I guess you will have to count the number of entries within a cell within the <for next loop> filling them.
If greater than limit (say 5), then display a more link and exit the for next loop.
I think this would be in the loop: for each appt in appts


Re 3:

I have a version now which connects to a Oracle DB. I used a view defintion where in the select statement, I only select the entries >= sysdate. This will give me the future entries only. The underlying table has more than 50.000 records. The selected entries are average 150 spread over a month. loads in 3-4 seconds.
regards, Ron

[drgarytraub]

Re: Web Based Calendar Example

Re3:

I am not sure I understand what is accounting for the increased speed? The use of Oracle as opposed to alpha's native dbf's, or the filter.

And I am not sure I see where the filter is speeding anything up because there is already a filter that selects appointments just for a particular user and month.

Could you please explain a bit more?

Gary

[rleunis]

Re: Web Based Calendar Example

Hi Gary,

Yes it seems up to a number of appointments all goes reasonably fast, but when it gets more than a certain limit it goes slower and slower. I tried with more than 250 appts and it then it certainly takes more than 10+ seconds.

I think it is because the appts are filled within a couple of for next loops and the whole lot is loaded each time for each day within the weeks of a month..And only then the filter is applied. I wonder if it make more sense to load the appts like the catlist above in the page and filter on that in the loops, if possible. I am going to dig into this more...
regards, Ron

[drgarytraub]

Re: Web Based Calendar Example

Hi Ron,

Thanks fo getting back to me. I agree. And it seems to me that the calendar as it is, is perfect for those where there they expect only a few appointments in any particular day (cell). However, once we start talking about busy medical/attorney/etc type offices, where there can be 20 to 30 or more appointments per day, then the load time is prohibitive. I just tested on my computer and it takes approx 17 seconds to load a calendar with the average day having 12 to 20 appointments.

I am thinking that it may be better in these cases to have the cell display one simple link that says something like: "14 Appointments" - which is then a clickable link to the popup. But this time the popup will show a tabular grid of all the appointments for that cell. This way, there would be a maximum of 31 entries to load, and it would always be very quick, and the user would then just have to click the link to see all the appointments. Not ideal perhaps, but I cannot think of another way to speed this up to where it would be useful in a busy practice.

What do you think?

Gary

[rleunis]

Re: Web Based Calendar Example

Gary,

attached a revised first attempt of trying to improve on the loading time. I moved the table.external_record_content_get above the for next loops.

And added a test in the for each appt in appts loop:
if left(word(appt,1),10) = left(dtoc(dd),10). Which only displays the content into the cell when the date of the appointment is of the current cell. This works for me and speed is very much better.

Because I am not at all experienced in XBASIC (Just starting to understand XBASIC), I haven't yet got the other tests in. Also the formatting in the cells needs to be improved.

I am giving this already so you can test whether this improves loading time for you. If it does , maybe you can help me with the things I did not get right yet. regards, Ron

[drgarytraub]

Re: Web Based Calendar Example

Hi Ron,

I had trouble looking at your download because I have downloaded several before and do not remember which is which, plus I had changed it to suit my own situation.

However, you really got me thinking with the idea of putting the table.external etc statement before the for loops.

I have rewritten the routine without using the table.external etc statement, and instead using a query, but as you suggest, prior to the for loops.

I now have decreased the load time from approx 17 seconds or more to just 1 second! I am tweaking the code and should be able to post it in a little bit ...

[drgarytraub]

Re: Web Based Calendar Example

Here is my revised code:

session.currMonth = cmonth(ctod(session.showdate))
session.currYear=year(ctod(session.showdate))

dim tdate1 as d
dim tdate2 as d
tdate1=month_start(ctod(session.showdate))
tdate2=month_end(ctod(session.showdate))

month=month(ctod(session.showdate))

start_day=tdate1

dim dd as d
dim nw as n
nw = ceiling((day(month_end(start_day)) + dow(start_day) - 1) / 7)

if dow(start_day)>1 then
start_day=start_day-dow(start_day)+1
end if

order="cdate(Date)+str(Timesort,19,5)"
filter="between(date,tdate1,tdate2)"
filter=filter+".and. prov='"+session.showprov+"'"
dim tbl as p
tbl=table.open("[PathAlias.adb_path]\appointments")
query.filter=filter
query.order=order
query.options=""
tbl.query_create()

for nweek=1 to nw 'rows of calendar
? "<tr>"
for nday=0 to 6 'columns (days of week)
dd=start_day+nday+((nweek-1)*7)
if month(dd)=month then
?"<td vAlign=top width=500 height=60 " + iif(dd=date(),"bgcolor='#fafa80'",iif(between(dd-date()+dow(date()),1,7),"bgcolor='#fafad2'","")) + "><font size=2 style='font-weight:900;' >&nbsp;" + day(dd)+"</font><br>"+crlf()
tbl.fetch_first()
while .not. tbl.fetch_eof()
if day(tbl.date)=day(dd)
url = "calevent.a5w?calevent_filter=chargeno="+tbl.chargeno
display=tbl.time+" "+left(tbl.fullname,9)
?"<font size=2><a href=\"javascript: void(0)\" onclick=\"window.open('"+url+"','windowname1','scrollbars=1, width=330, height=430'); return false;\">"+display+"</font></a>"+"<br>"
end if
tbl.fetch_next()
end while
? "</td>"+crlf()
else
?"<td vAlign=top><font size=2>"+day(dd)+"</font></td>"+crlf()
end if
next nday
? "</tr>"+crlf()
next nweek

tbl.close()


Please note that my implementation of the form is a bit different than what was posted previously. I have no dropdowns. In my case, the user is in my main scheduler, on a particular day and doctor. When the user clicks a link to show the month view, session.showdate and session.showprov are used to form the basis of filters in the month view calendar. This works beatutifully for me. It loads in less than a second for months that have just a few appointments, and in a second or so in months with MANY appointments.

Gary

[rleunis]

Re: Web Based Calendar Example

Hi Gary,

Yes there was certainly a flaw in the original setup: it was reading all the appointments for each occurance in the cell over and over. No wonder it took a lot of time when number of occurances grow.

I like your approach with reading first and then next until eof. Brings back memories on how I used to approach reading records with cobol / rpg400 some decades ago...

But I certainly need to dig more into this xbasic. Just baffled with all the type conversions and " and ' one has to use to get it all working...But I am pleased with the speed increase as compared to using standard grids. Just wonder why your version shows such an increase in speed since you also fetch all the records within one day, testing whether it is in the day..

Once i get my version working with the original filters and dropdowns I will post again here. regards, Ron

[drgarytraub]

Re: Web Based Calendar Example

"Just wonder why your version shows such an increase in speed since you also fetch all the records within one day, testing whether it is in the day.."

Because the big slow down came from opening the table and retrieving all the records from the server each day - My approach opens the table only once and retrieves those records once - I did think about now that the table had been opened before the for loops, then fetching thru the list sequentially by date such that nov 4 would stop before nov 5, then nov5 would start with the first nov5 and stop before nov 6 and so on, but my approach was so fast that I did not see the need to tweak any further.

Gary

[drgarytraub]

Re: Web Based Calendar Example

Ah, what the heck. I revised the code slightly to do as I described above. Now, instead of approx one second, it works a little faster. Notice the 3 changes:

session.currMonth = cmonth(ctod(session.showdate))
session.currYear=year(ctod(session.showdate))

dim tdate1 as d
dim tdate2 as d
tdate1=month_start(ctod(session.showdate))
tdate2=month_end(ctod(session.showdate))

month=month(ctod(session.showdate))

start_day=tdate1

dim dd as d
dim nw as n
nw = ceiling((day(month_end(start_day)) + dow(start_day) - 1) / 7)

if dow(start_day)>1 then
start_day=start_day-dow(start_day)+1
end if

order="cdate(Date)+str(Timesort,19,5)"
filter="between(date,tdate1,tdate2) .and. pracid='"+session.pracid+"'"
filter=filter+".and. prov='"+session.showprov+"'"
dim tbl as p
tbl=table.open("[PathAlias.adb_path]\appointments")
query.filter=filter
query.order=order
query.options=""
tbl.query_create()

for nweek=1 to nw 'rows of calendar
? "<tr>"
for nday=0 to 6 'columns (days of week)
dd=start_day+nday+((nweek-1)*7)
if month(dd)=month then
?"<td vAlign=top width=500 height=60 " + iif(dd=date(),"bgcolor='#fafa80'",iif(between(dd-date()+dow(date()),1,7),"bgcolor='#fafad2'","")) + "><font size=2 style='font-weight:900;' >&nbsp;" + day(dd)+"</font><br>"+crlf()
'tbl.fetch_first() ' added this for Ron
while .not. tbl.fetch_eof()
if day(tbl.date)=day(dd)
url = "calevent.a5w?calevent_filter=chargeno="+tbl.chargeno
display=tbl.time+" "+left(tbl.fullname,9)
?"<font size=2><a href=\"javascript: void(0)\" onclick=\"window.open('"+url+"','windowname1','scrollbars=1, width=330, height=430'); return false;\">"+display+"</font></a>"+"<br>"
else ' added this for Ron
exit while ' added this for Ron
end if
tbl.fetch_next()
end while
? "</td>"+crlf()
else
?"<td vAlign=top><font size=2>"+day(dd)+"</font></td>"+crlf()
end if
next nday
? "</tr>"+crlf()
next nweek

tbl.close()


Gary

[rleunis]

Re: Web Based Calendar Example

Great ! Thanks Gary,

Now there are 2 possible approaches:
1) create a one time list using get_external etc outside the main loop and work with that to select what needs to be displayed within the loops
2) Open the table outside the loop and fetch records and display when criteria are met within the loop

Both methods are a big improvement ! Ron

[drgarytraub]

Re: Web Based Calendar Example

No problem, and a big thank you to you too!!!

Gary

[jkletrovets]

Re: Web Based Calendar Example

Thanks Dr. Gary and Ron! I have now incorporated the changes you made for speeding up retrieval of the records into my app. I had not noticed the slow down yet since there are not many records yet to display but I used your code to eliminate future problems. Thanks!

Stephen - I incorporated many of the enhancements you made as well so thank you too!

Do any of you know how we could incorporate a forward and reverse button to cycle ahead and back one month at a time if the user pressed those buttons?

Regards,

Jeff

[drgarytraub]

Re: Web Based Calendar Example

Jeff,

Use 2 links, one called Prev Month and the other called Next Month.

Each of these then goes to their own a5w page.

Each of those pages has just a simple Xbasic script, and then reloads the Calendar page

The script for Prev_Month.a5w would be

dim tdate as d
tdate=ctod(session.showdate)
tdate=addmonths(tdate,-1)
session.showdate=dtoc(tdate)
response.redirect("Calendar")


The script for Next_Month.a5w would be:

dim tdate as d
tdate=ctod(session.showdate)
tdate=addmonths(tdate,1)
session.showdate=dtoc(tdate)
response.redirect("Calendar")

[rleunis]

Re: Web Based Calendar Example

Gary, is it possible to put this a5w code into buttons directly? How? Ron