Recently in Scheduler Application Category

Gee, it's been a long time since I've written a blog! I've had a very busy life the past several months. As for programming and the Scheduler application that I've been developing, I have made some good progress in the last few months.

I worked through a major revision to the security we had implemented for Portal users to access the Scheduler, as well as some internal security for the Scheduler application itself. This seems to work very well.

I've also spent worked through enhancements to make the Scheduler more global in nature. Now, the departments using the application will be more independent of each other, and each will have a greater sense that they are the only unit using the application.

I've also perfected the Student module, which will allow students to schedule their own appointments. I had to call upon Michael Ellis for some assistance when I put this into production for the Learning Center (AALC), but now things seem to be working well for the most part.

This morning, Tamie, Michael Ellis and I had a meeting to discuss the issues to be resolved within the Scheduler application. Tamie gave me a list of about ten issues still outstanding, that I will work through in the next few days. At least some of these, I believe, have been resolved in the test version. As I have worked through the security features of the application once again, I will be ready to put the test version into production sometime during the week of Spring Break. This new version, hopefully, will get us much closer to the final product. A few of the items we discussed in this morning's meeting will actually come later, in our version 2 of the application.

Next week, I will meet with Tamie (AALC area) to discuss their outstanding needs within the Scheduler application, with the goal of having things in place by early Spring Term.

In a previous attempt to put major changes into production a few weeks ago, AALC staff encountered errors very quickly, and I had to go back to the last known stable version of the application.

Since then, I believe I have (finally) resolved the issues with the security needs. Within my test version of the application, I re-wrote a couple of the functions I'm using for security testing, and re-developed the tests I'm using within the application to determine permissions for the user. In my testing the last couple of weeks, things seem to work smoothly. I should be able to quickly make any minor adjustments needed.

Also, within my test version, I have made changes to the student module to implement needed functionality. I hope to have this in place and ready for AALC staff to test very soon.

Once I have the new security functionality and the changes to the student module in place, the only major areas left to complete will be the reports. I also need to develop another user interface for department managers to manage their department properties, including start- and end-times of services for the day, start-day and end-day of services within a term and the cut-off time for allowing next-day appointments to be scheduled.

I have been working on several changes and "fixes" within the Scheduler application, including:

Application Permissions/Security

For the AALC area, I believe I have resolved issues with Peer Advisers not being able to schedule various appointments and navigate throughout the application. Michael Ellis and I have configured both Portal security and application-specific security, which is fairly complex.

Error with "Mini" Calendar View

Browser errors were happening within the page to change an appointment. I'm using a "mini" calendar to allow the user to view Tutors' schedules. For whatever reason, Michael and I had coded the URL for the mini calendar without the port 7777 within the URL. Fixing the URL with this path and the proper DAD seems to have resolved the error.

Student Module

I'm still working on resolving some issues within the form used by students to schedule their own appointments and the 10 pm cutoff time to block appointments from being scheduled the n0ext day. In my original development with this, I had "hard-coded" the 10 pm cutoff time; with the changes I'm making now, the cutoff time will be gathered from the departments (DEPTS) table, allowing this code to be more universal.

I expect to release a new production version of the application early this week.

On Friday of last week, I put a new version of the Scheduler Application into production. This release should tie up a few loose ends with general application security, as well as add one major change -- a cut-off time of 10 pm for next-day appointments -- to the form that students will use to schedule their own appointments. Once again, this area of functionality provided me an opportunity to learn more about Oracle dates, times and time intervals.

I have a new release of the Scheduler Application into production now.

This new version contains changes to security as well as other enhancements. As a result of this latest release, I received a call from an end-user who could not login to the application. I discovered that they had not been added as a staff member within their department. I believe I have the security at a place where most calls for assistance to me will be easily resolved through the web interface of the application; the real test will be when classes resume again in January and the application will be used much more heavily.

I have also worked with the Student appointment scheduling form to add code to not allow next-day appointments to be scheduled after 10 pm. I believe this is working properly now.

I met with Tamie Whitmore earlier this week to give her a demonstration of the test version of the application, which includes many changes. During the meeting, a few errors surfaced within the application, most of which I have since resolved. The big push lately has been to fine-tune the security within the application, to control which staff (peer advisors) and tutors are allowed into various parts of the application. I believe I have most of this in place now, and will finish my testing by early next week. Also included in the latest version is a form to allow students-at-large (once registered within the application) to schedule their own appointments. This is working well so far. I've also spent time debugging several parts of the application; areas that seem to have "broken" for whatever reason. I plan to put my test version into production by the end of next week in order to have it available for Tamie to begin testing by December 17.

I've been working on some major security enhancements within the Scheduler application as a whole, in order to allow department managers, staff and student workers to have appropriate access to the various levels of functionality. I have just about all of the changes to my code in place, and will do some testing next week, in preparation for migrating my test version of the application into production.

I've also made a major improvement to the report that lists appointments by professor. In the last version of the report, the professor was selected using a dropdown box, and all professors were listed, including those for whom there were no appointments scheduled. I modified that query to list only those professors for whom students had scheduled appointments. It also seemed like a good idea (to me, at least) to list these professors with a radio button instead of using a dropdown, and have the resulting report display in a new page.

I have completed a large part of the changes in security that will be implemented within the Scheduler Application. AALC staff need a three-tiered security approach, as follows:

● Administrators. Administrators can do everything within the application. Within most departments, there will be one or two staff members with these privileges. New Administrators will be added by UCS through the Portal. Department Administrators will have the capability to add Staff and Tutors (User Jobs) logins.

● Staff. With this level of security, staff will be able to do most tasks within the application, with some exceptions. Staff will be able to add new User Job (Tutor) logins, as well as Students-at-Large (with the capability to schedule their own appointments, view their appointments and cancel their appointments).

● User Jobs (a.k.a., Tutors). Users with this level of security will be limited to viewing all appointments within the department, as well as adding, changing and canceling their own appointments.

A fourth tier of security, for Students-at-Large, will actually be managed separately from those mentioned above.

I've worked on code to make all of this more efficient, and I expect to have this functionality working throughout the application early next week.

Once I have the security in place, I'll work on the student scheduling module, to allow students-at-large to schedule their own appointments. Michael Ellis will work with me on this part of the application. Once I have this functionality in place, the only major part of the application to be completed will be the various reports.

I've been working through several of the changes requested for this application, including a problem that surfaced with a two-hour limitation on Shift Blockouts. I believe I now have this working properly. Shift Blockouts will be used to effectively "cancel" appointments when Tutors are unable to work on a given day (inclement weather, illness, etc.). Also, Michael Ellis and I had a good meeting this morning to discuss how we will implement significant changes to security and user groups within the application. We expect to have most of the changes in place within the next couple of weeks, including a working interface to allow students-at-large to schedule their own appointments. Once we have these changes in place, the application "should" be "finished" for the most part. At this time, the only major functionality will be the necessary reports, which should be very straightforward.

...or "the customer is always right".

During the planning and development of the Scheduler Application, I was given some fairly strict guidelines or "business rules" that the end users, especially from one department, wanted to have in place. This particular end user wanted to have a great deal of control within the application and limit the scope of department-specific administrative activity of other users within that department.

So, I developed the application to allow for these business rules. Now, the dynamics of that specific department have changed a great deal, as the primary staff member responsible for using the Scheduler Application is no longer here. The staff member now responsible for use of the application has a greater scope of responsibility and has chosen to delegate more duties within the department. As a result, more staff within that department are using and managing the Scheduler Application. Our model of fairly tight control with one major person using the application is no longer effective.

To accommodate the new needs within this department to provide greater flexibility in the use of the Scheduler Application, I'll need to do some reorganization within my code. Most of the new needs will be fairly easy to implement, as most of the code is already in place. A major change in administrative access will involve making some changes to the Portal security. Unfortunately, security within the portal is only designed to accommodate two levels; the Scheduler Application will really have three levels of security once we have everything working properly. Michael Ellis will continue to provide assistance as I work through these modifications.

In the short term, these changes to the application will involve more work. In the long term, however, I believe the Scheduler Application will be more robust, resulting in greater flexibility when we provide access for additional departments.

For AALC, the Scheduler Application is in production now. I have spent much of this week working with them to resolve errors and other issues as they have arisen. Generally, the application is working well.

One of the major areas needing work is the development of the interface to allow students (clients) to schedule their own appointments. Once I have this in place, I'll work more with the Writing Center and ODS areas to put the application into production and work through a similar shake-down strategy as I'm currently doing with AALC.

Along the way this week, I've resolved several errors. I'm gaining confidence in sleuthing out issues within the Oracle tables, examining the actual data instead of automatically assuming that PL/SQL code is directly responsible for certain errors.

I've also had to investigate issues with Portal security as it relates to the Scheduler. Hopefully, in the long term at least, we'll have an interface within the Portal to allow those responsible for certain applications (such as the Scheduler) to coordinate the necessary security. Currently, Portal security is coordinated by only two people within UCS.

Also this week, as I've been working on further improvements within the Scheduler, I've had things in a "broken" state at times, which unfortunately has caused run-time errors for the production version. To prevent further conflicts, I decided to establish a test version of the PL/SQL packages, which will allow me to continue development and keep the production version stable. I'll migrate the test version into production as I make improvements to the code.

I decided to make a slight change in the way I'm handling students who have three or more "no-show" appointments during the current term.

Until now, whenever someone accumulated three or more "no-show" appointments, I simply marked all future appointments for that student during the current term as deleted with the value of SYSDATE.

I decided to include a report of students with three or more "no-show" appointments. In order to accomplish this, I needed a a quick way to query the APPTS table to find the appropriate records. To facilitate this, I modified my UPDATE statement to put the value "12/31/9999" as the deleted value for the future appointments, then query based on this date within the DELETED column within the SQL for the report.

Project Meeting with AALC

This morning I met with Tamie Whitmore of AALC to give her an overview of the Scheduler Application. The meeting went very well. This was the first time that Tamie had seen the application in detail. The application worked very well during the demonstration.

One item that Tamie suggested as an improvement was, within the form used to create an appointment, the possibility of limiting the courses displayed in the dropdown box to only those for which AALC offers tutoring and, within this limitation, to only display those courses for which they actually have tutors during the current term. This idea of restricting the courses that display will actually be a good feature for the other offices as well. I may not have this feature implemented until next term.

Another major outstanding item is the creation of a form to allow students (clients) to schedule their own appointments. I plan to work on this soon, as this is an important feature for the Writing Center.

I'll be arranging similar meetings soon for those in the Writing Center and Office of Disability Services.

Calendar Views

In displaying calendars for a date range other than that for a specific term, calendars did not display accurate dates when a range other than Monday-Friday was selected within the calendar dropdown box. I believe I now have this fixed now.

Email Reminders of Appointments

This capability isn't working at the moment. When I have things working properly, a job will run at 10:30 pm each weeknight that will send reminder notices to all students who have appointments scheduled the next day with the AALC office. Once I get this working properly for AALC, I'll set up an appropriate feature for the other departments as needed.

Academic Advising and Learning Center (AALC) staff would like to have a link on most pages to send email to the Tutoring Center when students are unable to find what they need within the Scheduler Application. I have developed a simple form for this purpose, and the mechanics involved in entering the text and sending the email seem to work well with the testing I've done so far. I still need to put the link on more pages within the application.

I have most of the items on the latest list of features finished, including the following:

Tracking of "No-Shows"

This was a feature requested by the Academic Advising and Learning Center (AALC). Once a student has accumulated three "no-shows" for the current term, future appointments during that term are automatically canceled. For production, we'll probably want to take a less-abrupt action.

Not Allowing Students to Cancel Next-Day Appointments After 10 pm the Night Before

This is another feature requested by AALC. Students are allowed to cancel appointments until 10 pm the night before. As part of the larger picture, I'm not currently allowing students to schedule or otherwise change appointments. This is actually much-needed functionality for the Writing Center, so I'll work on this soon.

Maximum Number of Students Allowed in a Group Appointment

Again, this is functionality requested by AALC. They would like to have a maximum group size of ten people. In thinking of the bigger picture, I have the maximum group size as a variable, so changes will be easy. This feature can be implemented any time for the other departments as well.

We'll need to have a project meeting sometime next week.

I believe I have perfected the code to handle no-show appointments for AALC. In production, students who don't show up for an appointment will be sent a notification email. After three no-shows during the current term, any future appointments scheduled for that student during the term will be canceled in bulk (actually, the records will just be marked for deletion within the APPTS table; nothing will actually be deleted). We may change the production release; the department may not want to take such a drastic measure.

According to dictionary.com:

Date:

(1) sweet edible fruit of the date palm with a single long woody seed
(2) a particular month, day, and year at which some event happened or will happen
(3) the day of the month
(4) an appointment for a particular time

(Yes, this is only a partial listing, and the order has been rearranged.)

This blog entry will focus on definitions (2) through (4) as they relate to dates within Oracle; if you'd like to read more about the sweet edible fruit, click here.

Seriously, the subject of dates within Oracle and PL/SQL shouldn't be taken lightly. As I've developed the Scheduler Application, I've had many excellent opportunities to learn about Oracle dates. With each improvement I make to the various schedules and calendars throughout the application, I'd like to think that I have a better understanding of the important concepts.

Lately, I've been working on some improvements in the scheduling of new appointments and the changing of existing appointments. Specifically, for the Academic Advising and Learning Center, there are several restrictions:

● Tutoring can be scheduled Monday-Friday, 8am-7pm
● Tutoring normally begins week two of the term and ends after week ten
● Appointments are not normally scheduled more than two weeks in advance (the current and next week)
● Length of appointments can range from fifteen minutes to one hour
● Students are allowed a maximum of two hours of tutoring per class per week
● Group tutoring and drop-in sessions are not normally subject to the restrictions above
● For students, the ability to schedule or cancel appointments ends at 10pm the day before
● Emails are sent to students after 10pm to remind them of appointments scheduled the next day

At present, I have most of these restrictions working properly. I had some challenges, however, implementing the limitations of two hours of tutoring per week. As I work on other improvements, I'll have ample opportunity to test this functionality and make any necessary refinements.

Although these restrictions are specific to the Academic Advising and Learning Center, the needs of other departments can be easily implemented globally within the application. Eventually, I'll develop an interface for staff to customize these settings within their respective departments.

I'm still working out some of the details needed within the Scheduler Application. I have made progress, but still have much to finish. I'm running into difficulties with Oracle dates again, and the calculation of hours tutored per student, per class, per week and the determination of when the maximum hours allowed per student, per class, per week have been reached. I hope to schedule some time with Michael Ellis next week to work through some of these glitches.

Project Meeting

Today Michael Ellis and I met with Judy Turner and Tamie Whitmore to discuss the status of the project. I'm working through a list of several medium-to-high priority features for AALC that need to be in place by mid-September, and I wanted to give them an appraisal of the application to-date.

As this is Judy's last week here at WOU, we will be working closely with Tamie to put the application into production for Fall Term. One higher-priority item that we discussed today is the need (by all departments) to give the general student population the capability to schedule their own appointments. We will have this working by the beginning of the term; students currently only have the capability to cancel appointments.

Overall Progress

All-in-all, the project development continues to go smoothly. I've been able to conquer most of the technical details on my own, with some consultation with Michael when I'm not sure how to integrate new functionality into the current system. Of course, I've also sought his help when I find myself facing a brick wall, which happens once in a while.

Down the road, I will develop areas of the user interface to allow managers to configure options within the application. Examples include changing the start- and end-week for services within a given department (the defaults are weeks 1 and 11) and changing the hours of the day during which services will be offered.

As I have the basic functionality of the Scheduler Application working and have released the software to the end-users for testing, I'm now working on some of the more critical details needed within the respective departments.

I now have functionality in place to limit calendars to view specific weeks of a term. For example, if a department only offers services during weeks 2-10 of the term, the calendars will only display this date range. I'm still working on the technical details to get this to work in an IFRAME used to change the date and time of a specific appointment, but I expect to have this in place soon.

I have added code that will allow department to handle different hours of operation (i.e., 8am - 5pm for one department, 8am - 7pm for another).

We can now schedule drop-in appointments.

I'll be working on more details over the next few weeks as well.

Michael Ellis and I met with the AALC staff yesterday to give a demo of the application to-date and to review the progress we made with the items on their list of crucial features. The demo went well, and we have all of the items from the list in place, although I still need to do some fine-tuning in a few areas.

We were also given a list of lower-priority items they would like to have in place by mid-September. Most of these should be fairly straightforward to incorporate within the application.

The bottom line, at this point, is that the Scheduler Application is working! Appointments can be scheduled and updated by staff, calendars can be viewed by staff and students, and the critical security elements are in place and working properly.

Michael wrote a quick report to list appointments by Professor. This report is a higher priority item; I will develop additional reports later. I have also made progress with viewing calendars from previous terms; this ended up being fairly simple to implement, as (per Michael's suggestion) I just developed a quick form to allow users to enter a date range, which I passed back to the calendar view. I have this working for the "all appointments" calendar view; I still need to implement this for the other views.

This week, I've added the following capabilities to the application:

Changing Shifts. We now have the capability to edit and update shifts, moving them to different days and times. As part of this, I had to develop tests to make sure that the changes weren't overwriting existing shifts for the same Tutor/Proctor/Notetaker.

Changing Appointments. We can now move appointments to different days and times, and can schedule appointments for Tutors/Proctors/Notetakers outside of normally-scheduled shifts. This particular feature was requested by the AALC staff. I still need to do some testing with this.

Student Interface. We now have the capability to add students as users (customers) of the application and display their scheduled appointments. Within this interface, students can cancel appointments. We will develop the capability for students to schedule new appointments and change existing appointments at a later time.

Office of Disability Services

Michael and I had a good meeting with Lon Thurston in the Disability Services office in order to get him up-to-speed with the project to-date. Lon is a new employee and will be working with us on the project.

Academic Advising and Learning Center

We've had a couple of meetings to set and clarify priorities for critical functionality to have in place within the next two weeks. Beta testing is proceeding, and we continue to make improvements to the application when needed, both for functionality and bug fixes.

Another Bug Fix

I discovered another major bug within the form used to schedule appointments. When clicking on an available time within a shift, which supplies the start time for the new appointment, the next part of the form allows the user to provide the end time. In our code, we were concatenating data to a large varchar2 variable of 1000 characters. Yesterday PL/SQL began returning an error message, ORA-06502: PL/SQL: numeric or value error, only during certain shifts. Through a Google search of this error, I concluded that the varchar2 of 1000 wasn't large enough, so I decided to increase its length to 2000. This seems to have fixed this bug. This was another instance of code suddenly "breaking" that had worked perfectly for several months.

More Shift Management

I have made more improvements to my procedure to update shifts, similar to the changes I made to the procedure to delete shifts. Again, there had existed the possibility to change the date and/or time of a shift without checking to see if any future appointments had been scheduled. I was able to recycle much of the code I had used to solve this problem with the procedure to delete shifts. In testing thus far, this seems to work fine.

I've made a change to my procedure to delete shifts. In testing, I discovered that I could delete a shift even with future appointments scheduled during that shift. Before updating the Shifts table to "delete" a shift (update the Deleted column with sysdate for the appropriate row), I've included a query of the Appts table to give a count of any future appointments scheduled during the shift to be deleted. This took some work, but I think I have the query working properly now. If the count is greater than zero, the user is given an error message and the shift is not deleted. If the count is zero, the shift is deleted.

Non-Course-Related Appointments

We now have the capability to schedule appointments that are not course-related. Examples could include professional development or assistance while working on an incomplete from a previous term. Within the appointment scheduling form, I've added a choice, "Other -- Non-Course-Related", at the bottom of the course selection dropdown box to designate this type of appointment. I've also added a "Notes" text box where appointment details or needs can be included. The "Notes" box can also be used for appointments associated with a current course.

Cancel Appointments

I've added a button at the bottom of the View Appointment page to "Cancel This Appointment". This removes the appointment from the calendar views by simply marking the record as deleted within the Appointments table.

On Tuesday we had a project meeting where we released the Scheduler Application for beta testing. We wanted to release the application to the Academic Advising and Learning Center and Writing Center for now; we plan to release the application to the Office of Disability Services later in the month. During the meeting, Michael and I gave a detailed demonstration of the current state of the application that also included a brief overview of the WOU Portal.

Highlights:

§ Judy in AALC has reported some unexpected PL/SQL errors and other glitches, mostly related to database queries,
   including data coming from Banner. I will work with Michael Ellis and the Banner Team to resolve these.
§ Additional features (I'm sure this list will grow over the coming weeks):
   ● Scheduling appointments: In addition to the list of classes dropdown box, the users would also like to be able to
      select "other" classes (i.e. professional development, incompletes from prior terms).
   ● Capability to cancel appointments in the event of Tutor, Proctor or Notetaker absence using a "fake" student worker
      to replace existing appointments with "fake" appointments.
   ● On the "Manage Shifts" page, display a column total of the hours worked for the day. Done.
   ● For the Writing Center, create a beta of the student worker scheduling module for Katherine to test.

This morning I discovered a major glitch within the Scheduler Application. Why everything worked flawlessly until today will probably remain a mystery forever. Michael Ellis, with his experience and expertise, was able to help me resolve the problem in about five minutes.

As the very first element of the form used to schedule a new appointment, we require students' identities to be verified using their V-Number. As part of this authentication, the V-Number submitted is verified within the WOU Portal. If a valid V-Number is supplied, the next part of the form is loaded; if the number is determined to be "bad", an error message shows and the user is given another opportunity to key a valid number.

As a result of the glitch that reared its ugly head this morning, any value entered (including a valid V-Number, null and otherwise "bad" numbers) resulted in a failed authentication. (In fact, the "spinner" used to indicate that the authentication attempt is processing keeps going "forever".)

As this process worked very well until today, I began to wonder if something hadn't changed somewhere within the WOU Portal or Banner itself; we hadn't made any recent changes to that part of my PL/SQL code. When Michael and I met to try to resolve things, he discovered that one local instance of the variable used to verify students' identities hadn't been properly initialized and therefore had a value of null. All other local declarations of the variable were properly initialized to call a function within the WOU Portal.

Initializing the null instance of the variable to match the other declarations solved the glitch

At this point, except for the implementation of a fix that we completed today and some general testing, I am finished with the staff interface of the Scheduler Application.

Within the calendar views, instead of displaying appointments for just the staff member's actual department, any appointment, within any and all departments, were showing. Michael helped me to change the loop and table queries to make this more efficient. Everything works great for now, but we may need to modify this using table joins if the calendars views begin to take longer to display as more appointments are scheduled over the coming months.

This fix is currently implemented in the "all appointments" calendar view; I will put this in place for the other views very soon.

We also made a change to the code that handles the association of user jobs and courses. We had this working properly for Tutors within AALC, but we made a global change that will allow these associations to be properly managed within all three departments as well as for departments added to the application in the future.

We also reviewed the process for adding portal users to the application. As of now, I have all project participants added to their respective department.

We expect to present the application to the group within the next week or so. As this is a busy time for everyone, and due to my work scheduling circumstances, we're finding it more difficult to schedule a meeting time that works for everyone. Ideally, we'd like to have at least one representative from each of the three departments in attendance. We'll be turning the application over to the group and will be entering a new phase of the project: Shakedown and Testing.

Once we've given the project to the group for testing over the summer, Michael and I will develop modules for student workers within the departments and for the student body at large. We expect these components to be less complex than the code we've developed so far.

Yesterday, Michael Ellis and I perfected another important part of the course management within the Scheduler Application. We now have the capability to manage the courses for which a Tutor, Proctor or Notetaker is authorized to consult. This involved another query into Banner in order to obtain a listing of currently-offered courses for the current term. We wrote procedures and developed the interface to add and delete courses from a user job. As we quickly developed this interface as part of the Manage Shifts page, I later moved this new functionality to a separate page.

We also finalized our strategy for dealing with the current or future term to display within the pages of the application. "Future term" is actually the next term, in the case of intersessions (such as this week). In finalizing this correction, we tested the major parts of the application and everything seemed to work fine. I will do more testing over the next few days as part of my preparations to release the application to the end users in the next few weeks.

In our meeting Tuesday afternoon, Michael Ellis and I worked on a solution to a bug that surfaced due to the fact that Spring Term has ended. The terms were not displaying properly (the application was "broken", with a PL/SQL error page). I worked on putting this fix into production after our meeting and will do more testing today.

We also began to focus on the push to get things ready for release to the group for beta testing. A key part of this will be to put the application through its paces and see where it breaks. In fact, I was starting to do this testing when I discovered the bug mentioned above.

We also discussed some of the last-minute details that we'll need to consider before release. We hope to meet one or two more times to work on some essential functionality before meeting with the group.

We have had a target date of about July 1 to release the application to the end users and begin the beta testing phase. We expect to meet this deadline.

Within the management of work shifts, I believe I have resolved several small but annoying glitches with the display of the end time. Now, shifts with an end time of 5:00 pm display accurately, and those with earlier end times are accurate (not offset by 15 minutes, which was one of the glitches).

I also have working code in place to properly delete work shifts.

Within the Scheduler Application, I'm working on the management of work shifts.

Working with Michael Ellis, I have code in place to update existing shifts and to add new shifts. Within the update procedure, I'm using dropdown boxes to display the current data and allow the user to modify the values.

This process works great, except for one glitch: If we attempt to update a shift that ends at 5:00 PM, the end time shown in the dropdown box as "8:00 AM" for some reason and not "5:00 PM". I haven't yet figured out how to solve this issue.

I also need to write a procedure to delete shifts. This should be very simple, as I really have this working as part of the update procedure. When we update a shift, we actually mark the old shift as "deleted" by updating the "Deleted" column of that row with SYSDATE, then insert a new shift in its place. This historical data will be used for reporting purposes.

As part of the management of shifts (updating, etc.), I've figured out a more efficient way to display the times-of-day, including the dropdown boxes used to update the shifts. In a quick attempt to display the times-of-day, I used a CASE statement. Today I decided to try creating a new table with a column for the 24-hour time, and another containing the "AM/PM" equivalent. Now, in order to display the time-of-day, I have a FOR loop that contains a query to go through the table and then print the time-of-day, in "AM/PM" format, to be displayed. I will make other improvements to the page and form used to update the shift information.

Within the Scheduler Application, I have made an improvement to the code that handles the deletion of User Jobs. As part of the portal table used to display the respective student employees, the link to delete that person only appears if there are no pending appointments. Previously, I had handled this logic at the time the actual deletion takes place. My current logic is more efficient, as staff will not even be tempted to try to delete a User Job if there are pending appointments.

I believe I now have working code in place to check for future or in-progress appointments before allowing a User Job to be deleted from the database. For example, we only want to be able to delete Tutor Jim Smith when there are no appointments-in-progress and no future appointments scheduled. Over the next few days, I'll test this functionality more thoroughly, but it seems to work for now.

Within the Scheduler application, I have recently completed the code to allow for the management of jobs and user jobs. This includes displaying these elements, as well as adding, updating and deleting each of these from the database. Michael Ellis helped me work through yet another "fix" of "bad" SQL last week, and I know the application is much, much better off as a result; more accurate data is now being displayed.

I still need to implement code that will check to see if there are any appointments scheduled for a particular job or user job before deleting the job or user job. For example, we don't want to delete the Proctor job classification or delete Proctor Jim Smith if there are future appointments scheduled with Proctor Jim Smith and/or if there are future proctoring appointments scheduled with any Proctor.

We also began to work with management of the Shifts data, with the goal of displaying this information in table form. We'll also develop the capability to add, update and delete shifts.

Within the Scheduler Application, I have been working on code to manage the jobs (Tutors, Proctors, Notetakers, etc.). I now have working code in place to add and modify jobs. Next, I'll implement the code to delete a job.

This functionality will be useful in the future when other departments wish to use this application. We will be able to quickly add their unique job titles. If, for example, we needed to add a new job, "Evaluator", this functionality would allow this to be done very easily.

Next, I'll add functionality to manage the user jobs. When a new student is hired within a department, for example, staff within the department will be able to quickly add the student to a job classification (Tutor, Proctor, Notetaker, etc.).

As mentioned in a previous blog, I've been testing the Scheduler Application to eliminate the possibility of double-booking of appointments. This error-trapping seems to work very well, as I have been unable to double-book appointment times for the same student (client).

Along a similar vein, I've also been testing whether or not the same Tutor, Proctor or Notetaker can be double-booked for the same appointment time. Michael Ellis and I even wrote a coding loop to take care of these attempts. Thus far, I have been unable to create these double-bookings, so everything seems to work properly, at least on the surface. Actually, the logic that prevents the double-booking of students seems to be properly handling this latter scenario.

Wow! The title of this blog says it all.

Michael Ellis and I have been focusing the last few days on some nitty-gritty details concerning these data elements. Within the AALC component of the Scheduler Application, we have established that Tutors are assigned to specific courses. Within the Writing Center component, all Tutors work with students from any course, irregardless of discipline, as writing is the main focus.

Michael and I met with Terry Manning on Thursday to discuss the relationships of Proctors and Notetakers to courses within the ODS component. For Proctors, we aren't interested in the person doing the proctoring, but rather the room or location of the proctored test. For this area, we'll create "Proctors" as "locations", and these scheduled "events" will actually be private or internal within the ODS component. When a test is scheduled for proctoring, ODS staff will transfer the schedule details submitted by the public into the actual private events. For Notetakers, which isn't a high priority right now, we'll take a closer look at the AALC component and will consider the use of a similar "Notetaker-to-Course" relationship.

We believe we have also resolved some technical issues within the application concerning "double-booking" of appointments. I still need to do more testing, but we believe that, now, it is not possible for two or more students, viewing the same open timeslot, to double-book appointments for that timeslot. There will also be a "first" student to claim an open timeslot. I will also work on solving a similar problem with double-booking of Tutors, Proctors and Notetakers.

I've been working on the various calendar views to be presented within the Scheduler Application. As of today, I have calendar views working to display all appointments, display by subject, display by course and display by tutor.

Within each calendar view, I have a drop down box to select other time frames, including a rolling two-week view for the current and next week (this is the default time frame for all calendars), the remainder of the term from today forward, and the entire term. Also, within the page/menu where the calendar views are selected, I have drop down boxes in place to allow the user to quickly choose the subject, course or tutor.

We've all experienced times when, all of a sudden something clicks and, "we understand now". This happened to me this morning as I was making design changes to several pages within the Scheduler Application.

I wanted to change the widths of several HTML tables to be 60% of the screen instead of 100%. To make an HTML table an actual "portal table", we call the procedures portal.web.openbox and portal.web.closebox to begin and end the table, respectively. These procedures, I thought, included the needed TABLE and /TABLE tags. With this scheme, however, I could not define a table width of anything less than 100%; this always seemed to be the default.

I decided to study the code that Michael Ellis helped me develop, as we had successfully declared a similar table with a width of only 60% of the page. I noticed that, before calling portal.web.openbox, we had defined yet another table (including TABLE, TR and TD tags), and that we were closing this table (with the respective /TD, /TR and /TABLE tags) after calling portal.web.closebox. This seems to serve as a "super" table; our "portal table" is actually contained within it.

I decided to experiment a little and add a similar "super" table, with a width of 60%, to the code I wanted to change. Success -- problem solved! My newly-acquired understanding of how "portal tables" are defined will be put to use many times as I design more pages within the Scheduler Application.

In our project meeting yesterday, Michael and I gave a demonstration of the current state of the appointment scheduling form. This went fairly well despite a glitch that reared its head when we entered a bad Student ID Number. We'll fix this in the next few days.

We also had several questions for the group, as follows:

(Project group members: Please let me know if I've left anything out or if corrections are needed.)

1) Standard Time Increments. As a group, we need to agree on a
standard for the begin- and end-times for appointments. Our
preference would be to use 15-minute increments throughout
the application.

Discussion: Both 15- and 30-minute time increments are needed. We will add a checkbox to the form to select either.

2) Will student workers input their own schedules for the term, and/or will that be done by staff members? How do you currently handle changes to student workers' schedules?

Discussion: Staff members will input the schedules. For changes to student workers' schedules, AALC staff prefer a week's notice. The other departments are more flexible.

3) How will you deal with students (customers) and student workers who are sick and unable to keep scheduled appointments?

Discussion: Would like to have customers provide their phone number within the form.

4) Are there any staff members (not student workers) who work for more than one department?

Discussion: No.

5) If a student worker has a shift, do they always come in for that shift, or do they only come in when they have appointments scheduled during that shift? Or, do student workers report for a shift and be available for walk-in appointments?

Discussion: Some student workers report for their shifts in order to be available for walk-in appointments and group-study sessions. Within the form itself, we discussed the idea of establishing a "cut-off" at 10 p.m. After this time, new appointments could not be scheduled for the next day. This would be better for student workers who do not report for a shift if there are no appointments scheduled.

6) In terms of the core system, what do you actually need by July?

Discussion: The group will give us a prioritized list.

7) Who will you have available to test the application in July/August?

Discussion: There should be staff and/or student workers available to test the application before September.

I've started working through many of the details needed to integrate the Scheduler Application into the WOU Portal, in terms of security as well as the more cosmetic design of the actual web pages.

The security aspects seem, on the surface at least, to be relatively straightforward; what I'm testing either works or it doesn't. As an example, when entering a student's V-Number, the application should retrieve their first and last names. This works fine if I've entered an "HTTPS://..." URL to launch the application. If I've entered an "HTTP://..." URL, the student's name is never retrieved. In our initial configuration within the WOU Portal, we specified an "HTTP://..." URL, so it should be easy for Michael and I to correct this problem (I don't have the necessary permissions within the WOU Portal to correct this on my own). I expect that most security-related issues will have a similar solution.

The design of the web pages is proving to be a bigger challenge for me. The complexity of the web pages is a direct result of the cascading style sheets (CSS) used within the WOU Portal. I've already learned the basics of how to code a "portal table", which will be used throughout the Scheduler Application. The design of future web pages will become more straightforward for me as time goes on.

In our meeting yesterday, Michael Ellis gave me a good introduction to the WOU Portal, and to the security and access control that he has developed within that application. In about five minutes' time, we established much of the security and access control needed for the Scheduler application. We still need to work out a few details, but we should have everything finished in a few days. Using the WOU Portal to manage security and access control within the Scheduler application will enable us to provide a more general, consistent application that can be used by more departments across campus.

Having this knowledge of how the WOU Portal works will be beneficial to me as I develop future projects. If the decision is made to "portalize" future applications, I can quickly establish the needed security and access control. I originally developed code to handle security and access control within the Scheduler application; the knowledge I gained by doing this has helped me to better understand how this is handled within the portal.

During our last two meetings, we have also made progress with the appointment scheduling form, which is nearly complete at this time. The built-in data validation that we have implemented has allowed us to develop a very simple, clean interface.

During our meeting last Thursday, Michael Ellis and I started to incorporate my application into the WOU Portal. We began with the appointment scheduling form that we've been working on for the past few weeks. For the form itself, we've established the "look-and-feel" of the portal, but we still have much more work to do, including authentication and security. The form itself is very complex, and this is posing challenges as we work with the page design of the portal. Having the appointment scheduling form incorporated into the portal will serve as a good example for me as I "portalize" other parts of the application.

We also met with Bill to give him a demonstration of what we've accomplished so far with the appointment scheduling form. We'll meet with the end users in the next week or so and give them a demonstration of the application as well.

We've also made improvements in the design of several of my Oracle tables, and I'll need to make major changes to much of my code over the next few weeks.

We are getting very close to having the appointment scheduling form in a workable state. At this point, we're shifting our focus a little more toward the visual presentation of the form and the resulting web pages.

At this time, the form works. When we click the final "submit" button, a record is inserted into our Appointments table; we can verify this by viewing the calendar within our form. For one of my "to do" items, I'll develop a page to present the user with a summary/confirmation of the appointment details.

Our use of IFRAMEs to control how parts of the form are displayed, as well as our implementation of DOM at various places within our code, are impressive indeed. Once I develop a thorough understanding of Michael Ellis' code, I'll be able to use this as an example for future programming projects.

Michael Ellis and I continue to meet a couple of times each week to work on the next steps in the redesign of the appointment scheduling form. We always make sure that I have a list of things to work on in order to prepare for our next meeting. For my current list of tasks, I have been modifying the code that he and I have developed so far. I have changed the form so that the "Submit..." button doesn't display by default when the form first loads, and I am working on a change to handle situations and display a message when a student may have a valid V-Number but isn't currently registered for classes.

As I have discussed in previous blogs, I have been working with Michael Ellis to improve the layout and functionality of the form that I had developed to be used to schedule appointments.

In its current state, our form collects the student's V-Number, authenticates them against Banner, then uses this information to display a dropdown box listing their courses for the current term. Once they select a course, a new dropdown box will list the tutors, proctors or notetakers available for the selected course. The next major phase of the form will be the display of a calendar that will allow the actual scheduling of appointments with the tutor, proctor or notetaker selected.

Through our work on this form, I'm gaining an appreciation of the iframe, which is used within a page to process data behind-the-scenes (hidden from the user) or to display the processed information within the current page without requiring the page to refresh. We expect to have the form finished within the next week.

Once we have the scheduling form working properly, we'll focus our attention on incorporating my application into the WOU Portal, as well as changing my page design by using the standardized web templates that Michael has developed. A major change within my application related to this will involve authentication and security for department staff and application administrators. According to Michael, this will be fairly easy to accomplish within the parameters of the WOU Portal.

In our meetings the last few days, Michael Ellis and I have considered how we might make improvements to the Scheduler Application.

As Michael has worked on many large projects and given in-depth training to several programmers within UCS the last few years, he has developed a set of core standards for the design of Oracle tables and coding practices within PL/SQL. We have already started to implement these practices as we've made changes to my table design. Adoption of these standards within my project now will provide more flexibility for greater use of the scheduling application across campus.

We're also working on significant changes to the form that I developed to schedule appointments. Again, considering some of the standard practices that Michael has developed, we can make improvements to the overall look-and-feel of the form, including how the data is validated, in order to provide a user interface (UI) that is more consistent with other campus web-based applications.

In redesigning the form, we'll implement the Document Object Model (DOM) to improve the processing and display of information. We've already used DOM to improve how students' identities are validated when their V-Number is entered. Through the use of an iframe, we've implemented this functionality in only a few lines of code that doesn't require the entire page to refresh.

Another major consideration is the incorporation of the scheduler application within the WOU Portal. Michael developed the WOU Portal and knows it "inside-and-out". We may be able to make use of the WOU Portal for some of the functionality, which would allow us to accomplish great things without "reinventing the wheel". Another advantage of using the WOU Portal is our idea of a more standard application and uniform UI. I'll discuss this in more detail as we make these decisions.

Michael Ellis and I met yesterday and reviewed the table structure that I have in place thus far for the Scheduler project. We will reorganize some of the elements that I have in place to make things work a little more efficiently.

We will replace much of the authentication and security that I have developed by using the WOU Portal that Michael has developed from the ground up. Incorporating my security and authentication needs within the portal Portal will help to make my application more generic. If staff within another department decide that they would like to use my application at a later time, having the authentication and security more centralized will make this much easier. This will also allow student access to my application to be more streamlined and standardized.

The code that I have developed thus far will be useful as we work through the goals mentioned above. Having the opportunity to work through the development of authentication and security has been a good learning experience for me.

Michael Ellis and I had a good meeting this morning to discuss improvements that I can make to the form I have developed to schedule appointments within my application. We will meet next week and work on the "nitty-gritty" changes that will need to be made.

In the meantime, I'll work on a change that will have students enter their V-Number then have their first and last names obtained from Banner. I think I've also found code within wou_util that will be useful.

I also hope to develop a dropdown box where users can select the department and course, rather than typing these values.

For the long term, we'll work on many larger changes that will improve how users input information to schedule appointments.

As I have recently been working on additional calendar views to display an entire term of scheduled events (which seem to work well), it seemed as though my application took a very long time to query the Calendar table and display the results. I decided late last week to take a closer look at the Calendar table and see if I could speed up the queries.

Of course, before diving into this task, I decided to back up my table, just in case of disaster. Let's face it; I didn't want to re-create my test data. Having the backup copy has been good, as I've restored it several times the last couple of days.

First, I experimented with partitioning the table and creating indexes based on these partitions. As I had only worked with partitions once before (one of our Staff Development projects), I used Google to do a little more research.

While partitioning the Calendar table and creating several indexes based on the partitions really didn't hurt anything, I would have liked for the performance gain to be more noticeable. The five seconds it took to query the table without the partitions compared to the 4.5 seconds with the partitions was not impressive.

This morning I decided to take a look at the SIR application that Paul Lambert and I wrote several months ago. Within that application, our raw data table contains well over one million rows and the queries are very fast. Paul, in working with the tables, added several indexes for most of the columns.

Within the Calendar table, I decided to take a similar approach and create several indexes, based on the columns I'm using for the WHERE clauses within my queries. The queries now work faster -- about three seconds to display events for an entire term -- versus five seconds without the use of indexes. It will be interesting to see what happens when I add more data to the Calendar table, and I'll keep the speed of the queries in mind from now on. I'll also try to implement the use of partitions, as I believe this will be beneficial as well.

Calendar Views for a Term

I now have the calendar views for a given academic term working for all three departments. Staff within each department can view appointments within the current two-week period as well as for a specific term. Aside from a few leftover tweaks necessary to establish the correct dates, everything seems to work properly.

The Next Phase

I will begin working with Michael Ellis in the next week or so to see how I can make some needed improvements to the form I developed to enter events into the application. While my form works for the most part, I need to make improvements to how the data is being validated, as well as some changes to the look-and-feel of the form. We will also take a look at some improvements that I can make within my tables. Throughout his many PL/SQL projects, he has developed very efficient code to accomplish similar functionality; I look forward to working with Micheel and learning from his expertise.

This afternoon I've had a major breakthrough, in terms of progress, in displaying the scheduled appointments for an entire eleven-week term. So far, it looks like my plan to recycle and make modifications to some existing code is working the way I originally intended.

I knew I would need to have the user select a term, then have my code generate the views based on the date range of that term. Getting this to work was fairly easy, but I ran into problems because of a "next week" switch that I had used to display my two-week calendar views. For the first week of the eleven-week term, this switch was set to "no" and all scheduled appointments for the first week seemed to display just fine. For weeks 2-11, however, the dates for the appointments were all wrong. I had set "next week" to "yes", thinking that this would be needed. I now believe that the use of this switch was causing the errors for weeks 2-11; commenting-out the code that sets the switch seems to have fixed everything.

I still need to do more testing to make sure everything is working correctly, and I need to get the eleven-week display working for my other calendar views, but I think this will be easier now that I've had this major breakthrough.

Generally speaking, the templates, headers and footers that have been used throughout the campus web pages provide a good standard in uniformity and the presentation of web content. Until very recently, I have followed these standards in the development of pages throughout my Scheduler application.

Within this specific application, however, I feel that most users won't be as interested in the more generic content provided by the campus standard. Also, the header, template and footer use valuable real estate on the screen that I may want to use at at a later time.

I have developed my own header and footer to be used throughout my application, and now have it in place. For now, at least, I have resolved most of glitches that have resulted from such a change, and will continue to fix any problems as they surface. Although my header and footer looks somewhat plain, I believe the new page design will help users to better focus on navigating through the specific pages of the application.

In this blog entry, I continue my discussion of the validation of form data submitted to schedule a tutoring, proctoring or notetaking appointment. Since my last blog, I have made significant improvements to this whole process, as outlined below.

Location Field

With the likelihood that users will enter a location such as "APSC 401" for a tutoring session. I discovered in testing, as similar data was being passed via HTML, that everything to the right of and including the "space" was being deleted. I think I have fixed this by replacing the "space" with a dash ("-") using Oracle's instr and replace functions. I may make some improvements to the validation of this field at a later time.

Types of Tutoring Sessions for the Writing Center

Possible values for this field are "Tutoring" and "Brainstorming". This field is only being used by the Writing Center. As I am using the same form for all departments, I'm doing some testing to find out if the current department is indeed the Writing Center; if it is, then I'm calling the appropriate logic to validate this field.

Proctor and Notetaker Values for Office of Disability Services

This is a similar scenario as discussed above.

Data Entry for All Fields

I am requiring the user to complete all fields of the form. I have PL/SQL code in place to determine if a field has been completed when the user attempts to submit the form. In the future, I hope to improve this process with the use of Javascript.

Options to Submit and Reset the Form

After completing the form, the user will have an opportunity to preview and make changes to the data before submitting the appointment details. The record is added to the Calendar table when the user submits the form. At any time before the user clicks the final Submit button, they can reset the form, resulting in a new, blank form and discarding any previous data that had been entered.

In my last blog entry, I discussed my successful strategy to validate the date field supplied by the user within the form used to schedule a new appointment. I am continuing to develop code to validate the remaining form fields.

I now have working code that will determine whether or not the user entered a date value. If not, they will be taken back to the form and will see a message indicating that nothing was entered for that field. In taking the user back to the form, I wanted to properly display the values that they had actually entered for the other fields. For most of the form data, this was no problem; for the time values (hour and minute), however, the form reset the drop down boxes back to their respective default values of "none".

To solve this minor problem, I added another parameter to the procedure containing the form. I actually call this parameter a "switch". If the value of the switch is "y", I display the drop down boxes with the appropriate variables as "selected". If the switch has no value, I display the drop down boxes with their appropriate default values of "none". So far, this strategy seems to work.

I have more work remaining to complete the validation of the other form fields, but I expect this to go smoothly.

Within my form to schedule a new tutoring/proctoring/notetaking session, I have perfected the validation necessary for the date field. With this now in place, the user cannot enter "bad" dates, such as 11/31/2006 or 2/29/2007. After some trial and error with a couple Javascript examples I gleaned from the web (and making little progress), I sought advise from Michael Ellis. Michael has developed a couple of procedures within the WOU_UTIL schema that display a date box and handles the validation. This was easy to implement, and I had to do very little with the resulting date field to insert the value into the Oracle table. I had to use the "secure" version of the date box procedure, and I have most of the "secure/insecure data" issues resolved at this time.

In doing some "clean-up" of my appointment detail pages, I decided to experiment with the links that I have been placing at the bottom of each page within the application. These are navigational links that allow the user to go back to the previous page, go back to the main menu for that department, or to logout of the application entirely. I was particularly interested in the link to go back to the previous page.

In order to see an appointment detail page, the user clicks on a link within the calendar summary page. As there are actually several summary pages (view all appointments, view appointment by course, by tutor, proctor, etc.), I wanted the link at the bottom of the detail page to go back to the proper summary page.

It should be fairly simple, I thought, to create a dynamic link simply by incorporating a URL as a parameter within another URL. After adding some necessary parameters to several procedures as well as a few more local variables, it all came down to trial and error. Once I had the proper single- and double-quotes and the parameters placed properly within all URLs being passed, everything worked as I expected.

Within the calendar views that display scheduled appointments, I have configured the summary (tutor/protor/notetaker and course) as links to more detailed information about the appointment, including the location of the session/class and the name of the student/client. As of this writing, the detail page just displays the data; I still need to organize the information on the page to be more presentable.

As the same summary information is available through the staff (secure) login to the application as well as the general student (non-secure) page, I only wanted staff to have the capability to view the detailed information. This security check was easy to implement, as I already had the necessary environment variables in place.

The next major component will be to develop the interface to add, change and cancel appointments. It will be nice to have proper code in place for these critical functions within the application. Thus far, I have added all test data to the tables manually.

Also, we had a project meeting this morning. We hadn't had a meeting since mid-September, so I wanted to give everyone a demonstration of the current state of the application. I feel the project is going well so far.

As I said in my last blog post, I had converted some column data from varchar2 to date, and that I still had some things to finalize with my code. Finalizing the little details turned into more of a troubleshooting process than I first expected, but I have things working much better now.

After difficulty getting my calendar views for the Office of Disability Services (ODS) to properly display separate appointments for Proctors and Notetakers, I decided that it would be best to work with a backup copy of my package. This meant that I would need to work through the varchar2-to-date conversions once again for ODS, but I thought this would be easier than troubleshooting other technical issues.

Using the backup package, the varchar2-to-date conversion went well, and I was able to work through the other technical issues with the Proctor and Notetaker views more quickly. I plan to do a little more testing on Monday, but I expect to be finished with this task very early in the day.

Next, I need to do some "clean-up" of my code. I have many troubleshooting htp.print statements that I need to remove, as well as the clean-up of the information shown in the calendar views. My intent is that the calendar views will be summary data that will link to a more detailed view.

In my last blog I described my efforts to convert some varchar2 table columns into their proper date formats. I'm happy to report that this conversion is finished and that I can now display the dates and times appropriately within my calendar views. Having these values in their proper date format will make things easier for the long term of the project, and I'm glad I decided to dive into the mechanics of this change sooner rather than later.

Having the date columns in their proper format will save space within the table, as I now have one column each for the start- and end-dates and times. Previously, I had separate columns for the day-of-the-week, start-date, start-time, end-date and end-time values, all in varchar2 format. Now, to display the desired outputs within the calendar views, I'm using Oracle's trim and to_char functions extensively. The to_char function allows me to extract what I want to display; for example, a day-of-the-week ('Day'), date (I prefer the format 'MM/DD/YY') and time ('HH12:MI AM'). While I realize this represents a very basic (introductory) understanding of Oracle dates and times, I'll have ample opportunity to expand my knowledge later in this project.

I'm still finalizing some coding details with some of my calendar views, especially those for Proctors and Notetakers within the Office of Disability Services. I expect to have these views working properly within the next day or so.

When I first configured my Calendar table for the Scheduler project, I ran into difficulties with the date columns. This was almost entirely due to my lack of experience. In a class I took a couple of years ago and in my reading about Oracle the past few years, I have learned that the subject of dates and times is not to be taken lightly. I am now gaining valuable, practical experience with Oracle dates and times. After first running into difficulties, I decided to just use varchar2 values for these table columns thinking that I would convert these items to dates later and make everything right with the world. I decided that this week would be a good opportunity to take care of this conversion. I took care of the table columns and I think I almost have my PL/SQL package working properly, but I'm still dealing with some run-time errors. My approach, for the moment, is to use Oracle's trim and to_char functions to use the date and time values for comparison and for output through htp.print. I expect to have everything working properly by early next week. I wouldn't have even considered this task before making backup copies of my table and appropriate packages first. When I get things working the way I want, I'll write a blog entry to celebrate!

Improvements Within the Application

Over the last two days, I have made what I consider to be some major improvements within the Scheduler application.

View Scheduled Tutoring Sessions by Course. In addition to viewing scheduled tutoring sessions by subject and by tutor, I now have working code to view sessions by a specific subject and course number. Once the user selects a subject, the next dropdown box to choose the course number will only display valid course numbers for that subject. For example, if our table contains MTH 111, MTH 251, MTH 252, CS 260 and CS 311, when the user selects the subject CS, the only course numbers available will be 260 and 311.

Further Segregation of AALC and WC Data. I have made changes in the tables to further separate AALC and WC data throughout the application. As an example, when logged into the WC area of the application and viewing scheduled tutoring sessions, only WC data will be available. This saparation of data has been a major objective as I have developed code. As a result, the minor changes I have made to the data tables were easy to implement and were the logical "next step" within this objective.

ODS Module

I plan to add more functionality for the Office of Disability Services (ODS) area of the application within the next few days. Having the above improvements in place will help me as I add new capabilities to all areas of the application.

I have written code to view the calendar, with options to list the tutoring sessions by subject and by tutor. Once I had the calendar view working to show all tutoring sessions over the current two-week time period, listing the sessions by subject and tutor was fairly easy.

When selecting the link to view by subject or by tutor, you are prompted to supply the subject area or tutor's name using a dropdown box that involves a query to either the Subjects table or the Tutors table. After gathering this information, my code determines which view you have selected, runs the appropriate query (cursor), then displays the results within the generic calendar/table that I developed sometime back.

I still need to do some clean-up of my code, as the calendar views look a little crude at this point, and I need to fix some broken links within the various pages, which are the result of changes and additions to parameters being passed to several procedures.

I believe I have conquered almost all of the glitches discussed in my earlier blog. This afternoon I developed the code to display a two-week view of the calendar data. I'm passing a one-character varchar2 to the procedure that displays the actual calendar data. If that variable is equal to "y", then I have logic in place to add seven days to the starting date in order to print data for the second week of the two-week view. I've also made significant improvements to another procedure that determines the starting date to display data, so I'm still testing things.

I've been working on the display of schedule data within the various calendars. Querying the calendar table and displaying the results in a linear fashion is simple. As I'm still learning new skills, however, getting the data to display within my calendar template has proven to be a more significant challenge. For now, at least, I think I've conquered most of the technical hurdles. Once I get the data to display properly within one calendar view, I expect the others to be more straightforward.

One particular challenge involved the display of a variable containing the day-of-the-week, a varchar2 which was the result of a to_char operation on an Oracle date field. I could display the contents using htp.print, but couldn't isolate it using if/then/else logic or a case statement.

As it turns out, the real culprit was HTML and how it displayed the actual contents of the variable. For whatevere reason, the day-of-the-week variable contained a trailing space that didn't display in HTML (for example, 'Monday '). As a result, my tests for 'Monday' would fail. Ron was a great help in isolating the glitch. I now have a new appreciation for the "trim" function as well as the use of a delimeter to show the beginning and ending charaters of a variable's contents when printing for testing purposes.

We had a short project meeting on Thursday, September 14, during which I gave a demonstration of the current state of the application. I created Administrative logins for everyone so they can take a closer look at the application on their own. It will be helpful to have everyone test the application and to suggest appropriate changes in the wording and page layout.

I am now working on various cursors to extract data from the calendar. At present, only an empty table is displayed on the various pages. My goal is to pass cursor variables to one procecdure that will display the results as needed throughout the application.

As the Scheduler application will be shared by multiple departments, I am striving to write as much generic code as possible. I am complementing this code with liberal use of parameters among procedures, functions and packages. With this strategy, I will write less code while retaining precise control of the detailed information to be presented throughout the application.

As an example of this goal, students and staff will need to view the appointment calendar and make new appointments, as well as change and cancel appointments. Staff authenticate to the application using their LDAP credentials; students do not. I have only written one copy of the procedures to view, add, change and cancel appointments. Using parameters and making decisions within each procedure based on the values of these parameters, I can use the same procedure for both students and staff, irregardless of how they authenticate to the application.

As another example, I have written only one procedure to actually display the calendar. In order to control the actual data being displayed, I will write specific cursors within the calling procedures instead of the single procedure that displays the calendar.

I have made some major improvements to the authentication and security within the Scheduler application. I expect that, with these changes, it will be very easy for department administrators to control access for staff, tutors, notetakers and proctors as appropriate. Each user is a member of one or more groups; each group has permissions to a specific area within the application. Some groups inherit permissions from other groups; for example, if a login has been granted administrative permissions but not staff permissions, they will inherit staff permissions based on their administrative permissions.

I met with Phil Pownall and Terry Manning in order to discuss their department's needs for the Scheduling application. A major area of interest here is the development of improved procedures for managing the scheduling of services, especially test proctoring. Also, it will be important for students (clients) to have the capability to login to the application and see their specific scheduled services.

I have completed the work of separating the administrative modules for the three departments (Academic Advising and Learning Center, Writing Center and Office of Disability Services). Now, for the administration of application logins, each department has its own independent module; staff, tutors, notetakers and proctors within the respective departments each have logins and permissions based on group memberships.

For the remainder of the project, I will use these group memberships and permissions as a layer of security to keep each department independent of the others throughout the application. This will allow me to consolidate functionality within my code and will result in code that is more efficient and robust.

We held our monthly meeting this morning to discuss the status of the project. Phil Pownall and Terry Manning (from the Office of Disability Services) also joined us, as they are participating in the project as well. I will meet with them, hopefully next week, to discuss their project needs in more detail.

For today's meeting, I prepared a list of data elements -- fields, column names and other items -- that will be used throughout the application. Today's list is by no means complete, and I expect many changes during the development of the application.

In designing the Administrative module thus far, I have kept the tutors' logins and group permissions segregated. For instance, Writing Center tutors will only only be allowed access to the Writing Center module of the application. I have not done this, however, for staff and administrative logins. As it turns out, each department will need to have the permissions of their staff and administrators segregated in the same way. I will have this in place within the next day or so. Stay tuned for more blogs about this topic.

For the last part of the meeting, I gave a demonstration of the current state of the Administrative module. This went well.

Bill and I met last week so that I could give him a demonstration of the Scheduler application and, more specifically, the Administrative module that I had in place at that time.

He suggested some changes with the permissions and security for the application as a whole, which would involve creating groups, then assigning logins to appropriate groups in order to better control access to the various components of the application. Under this scheme, a login would be allowed membership in multiple groups if necessary.

At first, I decided to allow each login to be a member of only one group, but assign additional permissions as necessary. The more I thought about this, however, the less I liked this idea; while everything seemed to work, it also seemed somewhat awkward. At this point, I decided to allow membership in multiple groups and to change the interface used to create and modify logins. I realized that this would involve some work, including the removal of a column from the authentication table which would no longer be needed.

I removed the column and eventually worked through recompiling all of my packages, correcting all of the resulting compile errors. As a result, everything seemed to work much better. Now, during the creation and modification of logins, each group (column within the authentication table) is listed in table format, allowing easy management of group memberships.

As another part of this redesign, I also had to make some changes to the security that I had in place to block against unauthorized access to areas within the application. Now, a security check is present at the entry point for each major area; if a user is not a member of the appropriate group, they are not allowed access. I have also improved the verification of Staff or Administrator group membership. This allows members of the Staff group to access everything except the Administrative module, and members of the Administrator group are allowed access to every area of the application.

I still have some minor changes to make with how some of the pages are displayed, but I believe the Administrative module is now much improved.

I have been finishing up some authentication details with my LRC Scheduler application. As a refinement, when anyone logs in who is not designated within the application as an LRC employee, Writing Center employee or UCS employee, some decisions need to be made within the application.

If the user is designated as an LRC tutor or Writing Center tutor, they are taken to the main menu for the application. From this point, I have logic in place to not allow tutors access to the administrative module.

If the user doesn't fall into any of the above login categories, they are unknown to the application, assumed to be a campus faculty/staff member, and taken to the Faculty/Staff main menu.

Within my logic for Faculty/Staff main menu, if the user is unknown, they do not see links to any other area of the application; if they are a known user (within any of the above login categories), they are allowed to exit from the Faculty/Staff module in order to go back to the application's main menu. The Faculty/Staff main menu is an entry-point only for unknown users; anyone else will access this page from another menu.

Since authentication to the application is achieved via LDAP, anyone from off-campus will not be allowed to authenticate to any part of the application. I will develop a separate interface for the WOU student community.

In addition to LDAP authentication to the application at large, I have the Administrative Module nearly complete. There could very well be some changes needed after I give a demonstration at our next meeting, thus the wording "nearly complete". The Administrative module has the following functionality:

�? Manage employee and tutor logins (add, update, remove)
�? Verifies that new users have an existing LDAP login (if not, don't allow the new login to be created)
�? Checks to make sure that new logins are not duplicates
�? Security checks -- ensures that tutors cannot change staff logins
�? Separate login management for LRC and Writing Center

My focus over the next few days will be to develop the page structure of the major components of the application. Once I have this in place and everyone has come to an agreement about the overall look-and-feel of the application, I'll shift my focus to the details.

I have been assigned a new programming project.

Bill and I met with Karen Sullivan-Vance and Katherine Schmidt to discuss the need for a scheduler and data collection application for the Learning Resource Center and the Writing Center. This application will track students' (customers) sessions as well as the scheduling of tutors. This will really be a management tool for the staff.

I have started working on an administrative module. So far, I have developed the capability to add new staff/administrators to the system, which includes some error checking (don't add the new user if they don't already have an LDAP login; don't add duplicate users).