In the last chapter, "Human Resources: Benefits Maintenance," you created a very simple intranet application that allows employees to modify some of the parameters that define their company benefits. Although a useful application, it was really only a snack. This chapter, however, is an entire four-course meal. In this chapter you will design and build an application that any company would kill for: Conference Room Scheduling.
Most companies these days have more than one conference room. It's when you need one most that they are always full; if you'd only gotten in there five minutes sooner. This application will hopefully let you and your users schedule conference room usage, alleviating any possible violent outbreaks.
This chapter will cover the following topics in regards to the Conference Room Scheduling application:
This four-step format will be used throughout all of the sample application chapters. Hopefully, it will provide you with valuable insight and ideas for creating your own intranet applications.
This application needs to be visually stunning (read cool-looking) and functional at the same time. It should be simple to operate and be configurable. Remember: you want people to use it. Because scheduling a conference room is a two-step process, you need to convey that to the user visually. Therefore, I've chosen to implement this application using a tab panel.
The program has two portions. The first portion allows the user to select the date and to schedule the conference room for that date. The second portion allows the user to select the start and end times for the scheduling. To present this information in a fashion that conveys the idea that the first step must be completed before the second step can be done, we will use the JifTabPanel class.
This serves a couple of useful purposes. The first being that the user cannot mistakenly select incorrect information regarding the date and the room while scheduling the time. Secondly, the first step is on pane one, and the second on pane two. This means the order of completion is visually represented.
This application is not like the last two applications we designed. It is a very modal program. The last maintenance programs were deemed semi-modeless because they did not force the user into an operation mode. This application is more modal because the user must select a date and a conference room before scheduling the time.
Figure 15.1 is the proposed user interface for the Conference Room Scheduling program.
Figure 15.1 : The Conference Room Scheduling user interface.
The first pane is divided into two halves. The first half represents the date with a calendar. The second half lists the conference rooms that are available.
Because the first figure doesn't show the second pane, Figure 15.2 shows the second pane of your application, which you get to by clicking on the "Schedule" tab.
Figure 15.2 : The dark side of conference room scheduling.
The second pane presents the user with two lists of times. The left side is the start time of the scheduling and the right side is the end time of the scheduling. Along the bottom is a space for comments. These can include anything the user wants to include, usually their name.
Only after all the necessary information has been selected (date, room, start and end times), and a comment has been entered, the Save button will become enabled. The user may then save this schedule. The application will inform the user of any conflicts. Otherwise, the records are saved, and the list boxes are reloaded to show their true state. Upon successful saving, the message "Record saved…" is shown in the status bar of the application.
The application will not allow the user to select time slots that are taken. In addition, attempts to select an end time slot that is before the start time slot will be thwarted.
Figure 15.3 shows what the schedule looks like after the user has saved his choices.
Figure 15.3 : The saved schedule is redrawn.
This application requires information from two tables. The first table being the conference room table, the second is the schedule table. This presents you with a parent-child relationship similar to the one presented in Chapter 14, "Human Resources: Benefits Maintenance."
This application will be responsible for manipulating schedule records. These records simply represent a time slot range on a certain day for a particular room. This simplistic design gives you a ton of flexibility in your application design. Each stored row will schedule one room for one period of time. Plus, your SQL to retrieve and update the table is simple. The table you're going to use in this sample application is called the room schedule table.
In addition to this child table, you want to make your application configurable. Administrative users should be able to add new conference rooms to the scheduler program as they need to. To accomplish this, you need a table to hold conference rooms as well.
The design of the schedule table is interesting because no times are actually stored in it. You use the concept of time slots. Not unlike network television programming, you schedule rooms for use during these slots. The usage of the slots is entirely up to the table user. However, your application will have predefined slot numbers. You are going to use 0 through 47 with each representing a one-half-hour slot during a twenty-four hour period.
Table 15.1 shows the columns you'll need to store your conference
room information.
| Description | Column Name | Type | ||
| Room Number | room_nbr | number( 5 ) | ||
| Floor Number | floor_nbr | number( 5 ) | ||
| Description | desc_text | char( 80 ) |
Table 15.2 shows the columns you'll need to store your schedule
information.
| Description | Column Name | Type | ||
| Room Number | room_nbr | number( 5 ) | ||
| Floor Number | floor_nbr | number( 5 ) | ||
| Schedule Date | sched_date | date | ||
| Starting Slot | start_slot | number( 5 ) | ||
| Ending Slot | end_slot | number( 5 ) | ||
| Comments | comment_text | char( 60 ) |
Figure 15.4 shows the entity relationship diagram for your database as it stands in this chapter. As you get deeper into your sample applications, you'll see your entity relationship diagram grow to encompass all the tables.
Figure 15.4 : The entity relationship diagram including the employee tables.
| Note |
Entity relationship diagrams are discussed in Chapter 13, "Employee Files." |
In addition to creating a table, you'll create a database synonym for your table. This will allow everyone to access the table with the same name, and not have to worry about the schema that the table resides in.
Listing 15.1 is the SQL commands to create the conference room table.
Listing 15.1. The SQL used to create the conference room table.
/* Create the table */
create table conf_room_t
(
room_nbr number( 5 ) not null,
floor_nbr number( 5 ) not null,
desc_text char( 80 )
);
/* Create a primary key */
alter table conf_room_t
add
(
primary key
(
room_nbr,
floor_nbr
)
);
/* Grant access for the table to the user role */
grant select,insert,delete,update on conf_room_t to ia_user_r ;
/* Drop any existing public synonym */
drop public synonym conf_room ;
/* Create a public synonym for our table */
create public synonym conf_room for conf_room_t ;
This SQL is extremely similar to earlier table creation SQL presented
in the last two chapters. After the table is created, a primary
key is created. Access rights are granted to your demonstration
user and a public synonym is created.
| Caution |
You must create the conf_room_t (Conference Room) table before you can create the conf_room_sched_t (Conference Room Schedule) table. Otherwise, the conf_room_sched_t SQL will fail! |
However, you do have a child table to go with your parent above. This table is the conference room schedule table, and called conf_room_sched_t in the database. The SQL is shown in Listing 15.2.
Listing 15.2. The SQL used to create the conference room schedule table.
/* Create the table */
create table conf_room_sched_t
(
room_nbr number( 5 ) not null,
floor_nbr number( 5 ) not null,
sched_date date default sysdate not null,
start_slot number( 5 ) not null,
end_slot number( 5 ) not null,
comment_text char( 60 )
);
/* Create a primary key */
alter table conf_room_sched_t
add
(
primary key
(
room_nbr,
floor_nbr,
sched_date,
start_slot
)
);
/* Create a foreign key */
alter table conf_room_sched_t
add
(
foreign key
(
room_nbr,
floor_nbr
)
references conf_room_t
);
/* Grant access for the table to the user role */
grant select,insert,delete,update on conf_room_sched_t to ia_user_r ;
/* Drop any existing public synonym */
drop public synonym conf_room_sched ;
/* Create a public synonym for our table */
create public synonym conf_room_sched for conf_room_sched_t ;
The first SQL clause creates the table conf_room_sched_t. The second clause creates a four column primary key using the room_nbr, floor_nbr, sched_date, and start_slot columns. Making this the primary key ensures that the values in the column are unique across all rows.
The third SQL clause creates our foreign key. The foreign key in this table is the room_nbr and floor_nbr columns. These columns point back to, or reference, the columns in the conf_room_t table.
Lastly, the public synonym conf_room_sched is created for the table conf_room_sched_t.
After you have created this table, you are ready to start building
your application.
| Note |
The SQL presented in this chapter is quite generic; however, it might not work on every database. This particular SQL has been tested with Oracle. |
The rest of this chapter will discuss the implementation of the Conference Room Scheduling program. The first thing we'll discuss is the user interface and how it was created. Secondly, we'll discuss the database access used in the program. Finally, we'll go over some of the programming considerations that came up during our application construction.
The Conference Room Scheduling program is probably the most complex program in this book. It utilizes some of the cooler user interface classes discussed in Chapter 11, "User Interface Classes." It doesn't really operate like the last two applications you developed, though. This application does use the SimpleDBJiflet, SimpleDBUI, and DBRecord classes. However, their use is unconventional compared to the last two applications.
This application requires two separate user interfaces to cooperate. However, because they work more closely together than not, they are managed by the same class. This class, RoomSchedulerUI, extends the SimpleDBUI class as discussed in Chapter 12, "Putting Them All Together."
To construct the user interface for this application, you need to make two separate panes. Each pane will be placed on its own tab in a tab panel. I'll discuss each pane's construction separately.
This pane is the more complex of the two panes. To achieve your interface design, you need to split the screen into four parts, then split those parts. The easiest way to do this is with a BorderLayout and a GridLayout.
The BorderLayout allows you to place five objects in an orientation that mimics a compass. This orientation is illustrated in Figure 15.5.
Figure 15.5 : The BorderLayout.
The five placements you can have are North, South, East, West, and Center. Each placement will grow to the largest size necessary. For example, if you put something in the Center that is 500 pixels high, the East and West sides will stretch to 500 pixels. The North and South placements remain zero pixels high, therefore hidden, until they contain something that has a height. For this pane in the user interface, you will only use the North and Center placements.
To create your first pane, you need to create a standard JifPanel object. This object's layout is set to a new BorderLayout:
// Create a date/room selector...
JifPanel jp = new JifPanel();
jp.setLayout( new BorderLayout() );
The next step is to place some things in this panel. To do this you will create two additional JifPanel objects: one for the North, and one for the Center.
The North one is your label panel. It displays the title for the widgets that sit below it. If you refer back to Figure 15.1, you'll see the panels I'm talking about. These are created and placed into the North placement of your main panel:
// Make a label panel...
JifPanel jp3 = new JifPanel();
jp3.setLayout( new GridLayout( 1, 2, 10, 1 ) );
JifPanel tp = new JifPanel( JifPanel.LOWERED );
tp.setFont( new Font( "Helvetica", Font.BOLD, 12 ) );
tp.setText( "Dates", JifPanel.TEXT_RAISED, JifPanel.CENTER );
jp3.add( tp );
tp = new JifPanel( JifPanel.LOWERED );
tp.setFont( new Font( "Helvetica", Font.BOLD, 12 ) );
tp.setText( "Conference Rooms", JifPanel.TEXT_RAISED, JifPanel.CENTER );
jp3.add( tp );
jp.add( "North", jp3 );
What's interesting about this placement is that you use an embedded GridLayout inside the panel that is placed in the "North" of your main panel. This layout is set at 1 row by 2 columns, with some additional spacing. This guarantees you that your panel will be evenly halved. It allows you to place two new label panels into the layout, and they are automatically sized correctly for you.
Creating the Center panel is quite similar. However, instead of using label panels, you are going to use actual user interface components:
JifPanel p2 = new JifPanel();
p2.setLayout( new GridLayout( 1, 2, 5, 0 ) );
p2.add( new CalendarPanel() );
roomList = new List();
roomList.setFont( new Font( "Helvetica", Font.PLAIN, 14 ) );
roomList.addItem( "No Database Connection" );
p2.add( roomList );
jp.add( "Center", p2 );
You use the same technique, only the spacing is a little different. The calendar is placed in first, forcing it to the left, and the List of rooms is placed in second. This forces the list to be on the right. Finally it is added to the Center of your main panel.
Figure 15.6 illustrates the complete layout of the date and room selection pane.
Figure 15.6 : The date and room pane layout.
You need to populate your list with all the available conference rooms from your conference room table (conf_room_t). To do this, you'll employ the same technique that was used in the EmployeePicklist to load it with data. Below is the source code for the loadRoomList() method.
//****************************************************************************
//* loadRoomList &nbs p; *
//****************************************************************************
public void
loadRoomList()
{
// Use a reference alias...
DBConnector c = getJiflet().getConnector();
// Not connected?
if ( c == null )
return;
String sql = "select * from conf_room";
// Clear out the old rooms...
clearScreen();
try
{
if ( c.getStatement().execute( sql ) )
{
ResultSet rs = c.getStatement().getResultSet();
int row = 0;
while ( rs.next() )
{
ConfRoomRecord crr = new ConfRoomRecord( rs );
getJiflet().setDBRecord( crr );
moveToScreen();
rowMap.insertElementAt( crr, row );
row++;
}
}
}
catch ( SQLException e )
{
getJiflet().errorLog( "Error during loading: " + e.toString() );
}
return;
}
This issues an SQL query that returns all of the conference rooms in your conference room table. Each returned row is stored into a ConfRoomRecord object. The stored row is then placed into a Vector for later use.
You might notice that the room list is really never populated here. That is because you've placed that code in the moveToScreen() method of your user interface class. Only there is data moved to the screen. But before it can move data to your list, you need to tell the base class which DBRecord to use. This is done with the call to setDBRecord().
The moveToScreen() code is quite simple:
//****************************************************************************
//* moveToScreen &nbs p; *
//****************************************************************************
public void
moveToScreen()
{
if ( getJiflet().getDBRecord() == null )
return;
ConfRoomRecord crr = ( ConfRoomRecord )getJiflet().getDBRecord();
roomList.addItem( crr.desc_text );
}
Here you simply retrieve the ConfRoomRecord from your jiflet and add the description to your room list.
This pane is constructed very much like the first pane. However, there are some slight differences.
First, the Center panel is filled with two Lists instead of the one. But for this pane, and to add a little variety to your program, I am using a FlowLayout instead of the grid. It does basically the same thing and is easier to set up.
Secondly, the comment field is placed in the South. This is where the user types in the reason or comment regarding the schedule of the conference room.
Figure 15.7 illustrates the layout of this pane.
Figure 15.7 : The schedule times pane.
The entire user interface construction code for this pane is presented below:
// Create a scheduler panel...
jp = new JifPanel();
jp.setLayout( new BorderLayout() );
// Make a label panel...
jp3 = new JifPanel();
jp3.setLayout( new GridLayout( 1, 2, 10, 1 ) );
tp = new JifPanel( JifPanel.LOWERED );
tp.setFont( new Font( "Helvetica", Font.BOLD, 12 ) );
tp.setText( "Start Time", JifPanel.TEXT_RAISED, JifPanel.CENTER );
jp3.add( tp );
tp = new JifPanel( JifPanel.LOWERED );
tp.setFont( new Font( "Helvetica", Font.BOLD, 12 ) );
tp.setText( "End Time", JifPanel.TEXT_RAISED, JifPanel.CENTER );
jp3.add( tp );
jp.add( "North", jp3 );
// Make the list panel...
JifPanel jp2 = new JifPanel();
jp2.setLayout( new FlowLayout() );
// Create some time selectors...
startList = new List( 12, false );
endList = new List( 12, false );
startList.setFont( new Font( "Helvetica", Font.PLAIN, 14 ) );
endList.setFont( new Font( "Helvetica", Font.PLAIN, 14 ) );
jp2.add( startList );
jp2.add( endList );
jp.add( "Center", jp2 );
jp.add( "South", comment_text );
After you've created your panels, you need to put them into a tab panel so the user can switch between them. Using the tab panel is easy! First you must create it:
// Create a tab panel...
JifTabPanel jtp = new JifTabPanel();
The next step is to add each completed pane to the tab panel. This is done with the addPane() method. It takes two arguments: the name to display on the tab, and the object to display on the pane.
// Add the panes...
jtp.addPane( "Date/Room", jp );
jtp.addPane( "Schedule", jp );
The rest is handled by the tab panel class. There is nothing more for you to worry about.
The last part of the user interface are the buttons along the side of the tab panel. These are two of the buttons that are provided to you from your base class, SimpleDBUI. You're going to use the Save and Delete buttons.
To place these nicely, create yet another JifPanel (YAJP?) object. Set a grid layout of 2 rows by 1 column. However, you set the spacing wide so your buttons will spread out:
// Add the buttons...
JifPanel p = new JifPanel();
p.setLayout( new GridLayout( 2, 1, 5, 20 ) );
p.add( saveButton );
p.add( deleteButton );
The last step in creating your interface is to place your sub-panels into your master panel. Remember that your base class, SimpleDBUI, provides you with a container panel to work with. This panel is already set up with a BorderLayout. You utilize the Center and East placements of this layout for placing your sub-panels:
// Add the tab panel to the center of myself...
add( "Center", jtp );
// Add the button panel…
add( "East", p );
As you can see from your screen shots in Figures 15.1 and 15.2, the layouts work quite well. Figure 15.8 shows this final addition to the master layout.
Figure 15.8 : The master layout.
After you've constructed all the necessary components, you've got this great looking user interface. But how do you command it to do your bidding? Simple! Through the use of the events generated by the components in your interface.
Before you can select a schedule start and end time, you need to choose a date and a room to work with. You want to trap these selections and store them in instance variables for later use. To accomplish this you first need those two instance variables:
java.util.Date
selectedRoom = null;
When the user selects a date on your calendar, or chooses a conference room from the list, an ACTION_EVENT event is generated. To capture these events, all you need to do is override the action() method in your class. The following is that method from your RoomSchedulerUI() class:
//****************************************************************************
//* action &nbs p; *
//****************************************************************************
public boolean
action( Event event, Object arg )
{
// Get the room record from the room list
if ( event.target == roomList )
{
selectedRoom =( ConfRoomRecord )rowMap.elementAt(
roomList.getSelectedIndex() );
showSelection();
return( true );
}
// Get date selection...
if ( event.target instanceof CalendarPanel )
{
// User selected a date...
selectedDate = ( java.util.Date )arg;
showSelection();
return( true );
}
return( super.action( event, arg ) );
}
First you check to see if the event was generated by your room list. If so, you retrieve the cached ConfRoomRecord object from your room list. Secondly, you see if the event was generated by your calendar. If it is, you store the date returned by that object.
After either of these events have fired, you call the showSelection() method. This method is responsible for showing some status information regarding the selections that have been made so far:
//****************************************************************************
//* showSelection &nb sp; *
//****************************************************************************
public void
showSelection()
{
String s = "";
String coolDate = FileDate.toNormalString( selectedDate );
if ( !coolDate.equals( "" ) && selectedRoom != null )
{
s = "Room " + Integer.toString( selectedRoom.room_nbr );
s += ", Floor " + Integer.toString( selectedRoom.floor_nbr );
s += " on " + coolDate;
loadLists();
}
if ( coolDate.equals( "" ) && selectedRoom != null )
{
s = "Room " + Integer.toString( selectedRoom.room_nbr );
s += ", Floor " + Integer.toString( selectedRoom.floor_nbr );
}
if ( !coolDate.equals( "" ) && selectedRoom == null )
{
s = coolDate;
}
if ( s.equals( "" ) )
s = "Select a date and a room...";
dateStatus.setText( s );
}
It is a simple method. It converts the selected date into a normal human readable form. This date is then concatenated to the conference room information that was chosen. This new String is then shown in the status bar of your mainframe.
This application communicates with the database through the use of the DBRecord extension class ConfRoomRecord. This class is used solely to retrieve information about conference rooms that are available to be scheduled.
The actual scheduling database work is done with custom constructed
SQL statements, and not the use of your SQL generator classes
as in the last two chapters. This allows you to explore some more
interesting uses of the framework and SQL.
| Note |
The ConfRoomRecord and other database classes are reused in several other applications. They have been placed in their own package along with other shared code. This package is called jif.common. It contains all the common classes between all the applications. |
Once a date and room are selected for scheduling, it is necessary to retrieve the current schedule for this pair from the database. This is done by stepping through the database. You know what the date and room are, you just need to check each time slot. The following source code illustrates how to step through the database:
//****************************************************************************
//* getSlotStatus &nb sp; *
//****************************************************************************
public String
getSlotStatus( int slot, int room, int floor, java.util.Date date )
{
int count = 0;
String sql = "select count(*) from conf_room_sched " +
" where room_nbr = " + Integer.toString( room ) +
" and floor_nbr = " + Integer.toString( floor ) +
" and " + Integer.toString( slot ) + " >= start_slot " +
" and " + Integer.toString( slot ) + " <= end_slot " +
" and sched_date = to_date('" + FileDate.toOracleString( date ) +
"')";
try
{
ResultSet rs =
getJiflet().getConnector().getStatement().executeQuery( sql );
rs.next();
count = rs.getInt( 1 );
}
catch ( SQLException e )
{
getJiflet().errorLog( "SQL Error:" + e.toString() );
return( "Open" );
}
return( ( count < 1 ) ? "Open" : "Used" );
}
First construct a query based on the arguments passed to you. These arguments specify the slot number, room number, floor number and date of the row to find. The SQL merely returns a count of records that match the given criteria.
You then request that your DBConnector execute the query. The query will always return a single row and column unless there is a database error. Therefore, you can always expect the row count to be there.
Finally, barring any errors, you return a string representing the state of the requested criteria. This is either Open or Used. The determination is based on whether the row count was less than one.
Storing your schedule is quite simple. However, you need to override the default saving mechanism that is built into your framework classes. To do this, you override the saveRecord() method in your main class:
//****************************************************************************
//* saveRecord *
//****************************************************************************
public boolean
saveRecord()
{
RoomSchedulerUI myUI = ( RoomSchedulerUI )getUIPanel();
if ( !myUI.canSave() )
{
MessageBox mb = new MessageBox( this,
"No Way, No How!",
"The information to schedule a room is incomplete.\n" +
"You need to select a date, room, a start time and an end time.",
MessageBox.EXCLAMATION );
mb.show();
return( false );
}
String sql = myUI.getInsertSQL();
try
{
getConnector().getStatement().execute( sql );
// Reload the schedule...
myUI.loadLists();
// Disable UI components...
myUI.saveButton.disable();
getDBRecord().setDataChange( false );
showStatus( "Record saved..." );
}
catch ( SQLException e )
{
errorLog( "Error creating schedule row: " +
e.toString() );
showStatus( "Record not saved..." );
return( false );
}
return( true );
}
This overridden method first checks with the user interface to make sure that the user has selected all the necessary items to actually schedule a room. If not, a message box is displayed informing the user.
Next, you request that the user interface generate some SQL for you to use to update the database. The SQL statement is then executed. If no errors occur, the schedule lists are reloaded with data, and a message is displayed to the user that the save was successful.
To generate the SQL for storing your schedule, you need to bring together all of the selections made by the user:
Once these have all be selected or entered, the SQL generation may take place:
//****************************************************************************
//* getInsertSQL &nbs p; *
//****************************************************************************
public String
getInsertSQL()
{
int rc = -1;
String sql = "insert into conf_room_sched ( room_nbr, floor_nbr, " +
"sched_date, start_slot, end_slot, comment_text ) values ( " +
Integer.toString( selectedRoom.room_nbr ) + ", " +
Integer.toString( selectedRoom.floor_nbr ) + ", " +
"to_date( '" + FileDate.toOracleString( selectedDate ) + "' ), " +
Integer.toString( startList.getSelectedIndex() ) + ", " +
Integer.toString( endList.getSelectedIndex() ) + ", " +
"'" + comment_text.getText() + "' )";
return( sql );
}
This statement simply inserts the rows of the selected data into the database.
This application presented you with quite a challenge. You needed to present an intuitive interface to the user that flowed and showed programmatic direction. This was achieved though the use of the tab panel class, JifTabPanel.
You also enhanced the interface using nested layout managers. You nested a GridLayout within a BorderLayout to evenly space your components. This technique is one you'll use over and over again.
To recap, this application introduced the following Java intranet programming topics:
You used a technique where you placed a FlowLayout and a GridLayout within a BorderLayout to build a cool interface.
You used the JifTabPanel object to present two different sets of data together for the user in an intuitive manner.
You overrode the saveRecord() method of main class to do some special checking and storage for your schedules.
You overrode the SQL generation method in your user interface. This was done because the user interface has all the necessary information to build the SQL. You didn't use components that were capable of generating SQL on their own.
This chapter introduced you to the third sample application in your intranet application suite. This was the Conference Room Scheduling application. This program is responsible for maintaining information about the scheduling of an important company resource: conference rooms. This application should be useful to all employees on your intranet.
In Chapter 16, "Online In/Out Board," you will design and create an application that allows employees to check in and out for lunch, or even vacations.