In Chapter 19, "Product Maintenance," you created a data entry program that allows users to create and maintain data in a product table. It is a useful application in itself; however, the application that you are going to create for this chapter relies on the data entered by that program. This is the Customer Support Maintenance program.
Although this is the last sample application of the book, it certainly is not the least. It is the second most complex intranet application that you've created, the first being the Customer Support Maintenance application. This application allows customer service representatives on your intranet to track problem reports generated by your customers.
This chapter follows the same topic format as the last chapters and covers the following topics in regards to the Customer Support Maintenance application:
This four-step format is used throughout all of the sample application chapters. Hopefully, it will provide you with valuable insight and ideas for creating your own intranet applications.
Remember that your goal is to make intranet users want to use the application. Simply placing an application out on the network is not enough. A program has to be functional, goodlooking, and most of all, easy to use.
This application has been designed primarily for ease of use. Figure 20.1 is the proposed user interface for the Customer Support Maintenance program.
Figure 20.1 : The Customer Support Maintenance user interface.
At the top are two lists. The list on the left contains all the available products. The list on the right contains all of the problems for a given product.
The product list on the left frees you from the need to have a product pick list in this application. At startup, all of the products are displayed.
The problem list on the right is not active until a product is selected on the left. After the user selects a product, any problems associated with that product are retrieved from the database and displayed in that list.
When choosing a problem from the list, the user might add a resolution or, perhaps, augment the current problem. Maintenance is fairly free-form. Changes are saved with the Save button.
By selecting New, a new problem can be entered for the chosen product. This new problem can also be saved with the Save button.
Figure 20.2 shows what the screen program will look like when editing an existing problem.
Figure 20.2 : The application editing an existing problem.
This application will be responsible for manipulating problem records. These records represent a problem that a customer has or had with a product. Each stored row represents a single problem. Your SQL to retrieve and update the table will be simple because each problem has a unique identifying number. The table you're going to use in this sample application is called the product problem table.
This table is a child table from the product table described in Chapter 19.
Table 20.1 shows the columns that you need to store your problem
information.
| Description | Column Name | Type | ||
| Product ID | prod_id | number( 5 ) | ||
| Problem ID | prob_id | number( 5 ) | ||
| Description | desc_text | char( 255 ) | ||
| Reported By | reported_by_text | char( 80 ) | ||
| Problem Resolution | resolution_text | char( 255 ) | ||
| Problem Date | start_date | date | ||
| Resolution Date | end_date | date |
Figure 20.3 shows the entity relationship diagram for your database. This data model diagram represents the entire database developed for this book.
Figure 20.3 : The entity relationship diagram with employee tables.
| Note |
Entity relationship diagrams are discussed in Chapter 13, "Employee Files." |
In addition to creating a table, you create a database synonym for your table. This allows everyone to access the table with the same name, without having to worry about the schema that the table resides in.
Listing 20.1 shows the SQL commands to create the product problem table.
Listing 20.1. SQL commands.
/* Create the table */
create table prod_prob_t
(
prod_id number( 5 ) not null,
prob_id number( 5 ) not null,
desc_text char( 255 ) not null,
reported_by_text char( 80 ),
resolution_text char( 255 ),
start_date date default sysdate,
end_date date
);
/* Create a primary key */
alter table prod_prob_t
add
(
primary key
(
prod_id,
prob_id
)
);
/* Create a foreign key */
alter table prod_prob_t
add
(
foreign key
(
prod_id
)
references prod_t
);
/* Grant access for the table to the user role */
grant select,insert,delete,update on prod_prob_t to ia_user_r ;
/* Drop any existing public synonym */
drop public synonym prod_prob ;
/* Create a public synonym for our table */
create public synonym prod_prob for prod_prob_t ;
This SQL is similar to table creation presented in the previous
chapters. After the table is created, a primary key is created.
Then a foreign key is created to reference the product table.
Access rights are granted to your demonstration user, and a public
synonym is created.
| Caution |
You must create the prod_t (Product) table before you can create the prod_prob_t (Product Problem) table. Otherwise, the prod_prob_t SQL will fail! |
The rest of this chapter covers the implementation of the Customer Support Maintenance program. First, I'll discuss the user interface and how it was created. Secondly, I'll discuss the database access used in the program. Finally, I'll go over some of the programming considerations that came up during the application construction.
The Customer Support Maintenance program is the second most complex program in this book. It utilizes some of the cooler user interface classes discussed in Chapter 11, "User Interface Classes." However, it doesn't operate like any other application in the book. This application does use the SimpleDBJiflet, SimpleDBUI, and DBRecord classes, although their use is unconventional compared to the last applications.
This application consists of two separate sets of components on one screen. This is done by creating two subpanels and inserting them into a BorderLayout. Figure 20.4 illustrates your user interface.
Figure 20.4 : The Customer Support Maintenance layout.
Listing 20.2 shows the source code for the construction of the user interface.
Listing 20.2. The Product Maintenance interface construction code.
//****************************************************************************
//* Members &nb sp; *
//****************************************************************************
List prodList = new List();
List probList = new List();
JifTextField prod_id = new JifTextField( "", "prod_id" );
JifTextField prob_id = new JifTextField( "", "prob_id" );
JifTextArea prob_desc_text =
new JifTextArea( "", "desc_text" );
JifTextField prob_rep_by_text =
new JifTextField( "", "reported_by_text" );
JifTextArea resolution_text =
new JifTextArea( "", "resolution_text" );
JifTextField start_date =
new JifTextField( "", "start_date" );
JifTextField end_date = new JifTextField( "", "end_date" );
Vector rowMap = new Vector( 5 );
Vector prodMap = new Vector( 5 );
//****************************************************************************
//* Constructor ; *
//****************************************************************************
public
ProblemTrackerUI( SimpleDBJiflet jiflet )
{
super( jiflet );
setLayout( new BorderLayout() );
// Build a big panel...
JifPanel p2 = new JifPanel();
p2.setLayout( new BorderLayout() );
// Build a label panel...
JifPanel p1 = new JifPanel();
p1.setFont( new Font( "Helvetica", Font.PLAIN, 14 ) );
p1.setLayout( new GridLayout( 1, 2, 5, 5 ) );
JifPanel tp = new JifPanel( JifPanel.LOWERED );
tp.setText( "Products", JifPanel.TEXT_RAISED, JifPanel.CENTER );
p1.add( tp );
tp = new JifPanel( JifPanel.LOWERED );
tp.setText( "Problems", JifPanel.TEXT_RAISED, JifPanel.CENTER );
p1.add( tp );
p2.add( "North", p1 );
// Build a listbox panel...
p1 = new JifPanel();
p1.setFont( new Font( "Helvetica", Font.PLAIN, 14 ) );
prodList.setFont( new Font( "Helvetica", Font.PLAIN, 14 ) );
probList.setFont( new Font( "Helvetica", Font.PLAIN, 14 ) );
p1.setLayout( new GridLayout( 1, 2, 5, 5 ) );
p1.add( prodList );
p1.add( probList );
p2.add( "Center", p1 );
// Build a UI panel...
p1 = new JifPanel();
GridBagLayout gbl = new GridBagLayout();
int cw[] = { 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10 };
double rc14_0[] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
gbl.columnWidths = new int[ 13 ];
gbl.rowHeights = new int[ 10 ];
gbl.columnWeights = new double[ 13 ];
gbl.rowWeights = new double[ 10 ];
System.arraycopy( cw, 0, gbl.columnWidths, 0, 13 );
System.arraycopy( cw, 0, gbl.rowHeights, 0, 10 );
System.arraycopy( rc14_0, 0, gbl.columnWeights, 0, 13 );
System.arraycopy( rc14_0, 0, gbl.rowWeights, 0, 10 );
p1.setLayout( gbl );
p1.addWithConstraints( new Label( "Problem:", Label.RIGHT ),
"anchor=east;x=0;y=0" );
p1.addWithConstraints( prob_desc_text,
"x=1;y=0;width=9;height=3;fill=both" );
p1.addWithConstraints( new Label( "Resolution:", Label.RIGHT ),
"anchor=east;x=0;y=4" );
p1.addWithConstraints( resolution_text,
"x=1;y=4;width=9;height=3;fill=both" );
p1.addWithConstraints( new Label( "Reported By:", Label.RIGHT ),
"anchor=east;x=0;y=7" );
p1.addWithConstraints( prob_rep_by_text,
"x=1;y=7;width=9;fill=horizontal" );
p1.addWithConstraints( new Label( "Problem Date:", Label.RIGHT ),
"anchor=east;x=0;y=8" );
p1.addWithConstraints( start_date, "x=1;y=8;width=9;fill=both" );
p1.addWithConstraints( new Label( "Resolution Date:", Label.RIGHT ),
"anchor=east;x=0;y=9" );
p1.addWithConstraints( end_date, "x=1;y=9;width=9;fill=both" );
/*
* Note: The prod_id and prob_id fields need to get on the panel.
* If they don't, the SQL is not generated for them, and you cannot
* save anything. I trick them onto the panel below. I don't want
* the user to see them, so I hide them after adding them behind
* another component.
*
* Try commenting out the hide() calls below and see what happens.
* It is pretty cool! ;)
*/
// Hide this behind the new button...
p1.addWithConstraints( prod_id, "x=11;y=0;width=2;fill=horizontal" );
p1.addWithConstraints( newButton, "x=11;y=0;width=2;fill=horizontal" );
// Hide this behind the save button...
p1.addWithConstraints( prob_id, "x=11;y=2;width=2" );
p1.addWithConstraints( saveButton, "x=11;y=2;width=2;fill=horizontal" );
// Hide the two fields...
prod_id.hide();
prob_id.hide();
// Make the sauce...
add( "North", p2 );
add( "Center", p1 );
// Disable buttons...
newButton.disable();
saveButton.disable();
// Tell which are numeric...
prob_id.setNumeric( true );
prod_id.setNumeric( true );
prob_id.setPrimaryKey( true );
prod_id.setPrimaryKey( true );
start_date.setDate( true );
end_date.setDate( true );
clearScreen();
}
First, build a panel that contains two labels and two list boxes. These make up the top half of the interface. Then create a panel that holds the GridBagLayout of text fields and areas. These are where the user will type data.
The two panels are then placed into a master BorderLayout.
This application uses a cool trick. There are two columns, prod_id and prob_id, that the user should never see. However, they are required to be part of the layout because you need the values they hold for proper SQL generation by your SQLFactory classes.
The solution is to add them to the layout, but make them invisible from the user. You accomplish this by placing them behind the two buttons and then hiding them. The following is the source code:
// Hide this behind the new button...
p1.addWithConstraints( prod_id, "x=11;y=0;width=2;fill=horizontal" );
p1.addWithConstraints( newButton, "x=11;y=0;width=2;fill=horizontal" );
// Hide this behind the save button...
p1.addWithConstraints( prob_id, "x=11;y=2;width=2" );
p1.addWithConstraints( saveButton, "x=11;y=2;width=2;fill=horizontal" );
// Hide the two fields...
prod_id.hide();
prob_id.hide();
Without hiding the components, you get a weird double-component look that is not natural. But when they are hidden, only the container itself knows that they are there.
Being there, they can store values and generate SQL code. It's a pretty cool trick.
This application communicates with the database through the use
of the DBRecord extension
class ProductProblemRecord.
This class is used solely to retrieve product problem table rows
from the database.
| Note |
The ProductProblemRecord 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. |
Before you can edit problems, you need the user to select a product.
Before the user can edit product problems, you must populate the list of products. This is done the same way the product pick list was done in the previous chapter:
//****************************************************************************
//* loadLists & nbsp; *
//****************************************************************************
public void
loadLists()
{
newButton.disable();
getJiflet().showStatus( "Loading lists..." );
getJiflet().startWait();
prodList.clear();
prodMap.removeAllElements();
int rows = 0;
probList.clear();
try
{
String sql = "select * from prod order by desc_text";
ResultSet rs =
getJiflet().getConnector().getStatement().executeQuery( sql );
while ( rs.next() )
{
ProductRecord pr = new ProductRecord( rs );
prodList.addItem( pr.desc_text );
prodMap.insertElementAt( pr, rows );
rows++;
}
if ( rows > 0 )
{
prodList.select( 0 );
loadProblemList();
}
}
catch( SQLException e )
{
getJiflet().getConnector().errorLog( e.toString() );
}
getJiflet().endWait();
getJiflet().showStatus( "Products Loaded!" );
}
Retrieve all the products in sorted order. Each one is stored in your product cache and then placed into the list. After all of the products have been loaded, select the first one in the list and load any problems associated with that product:
//****************************************************************************
//* loadProblemList & nbsp; *
//****************************************************************************
/**
* Load the problem lists...
*/
public void
loadProblemList()
{
getJiflet().showStatus( "Loading problems..." );
// Set the current product id...
ProductRecord pr =
( ProductRecord )prodMap.elementAt( prodList.getSelectedIndex() );
prod_id.setText( Integer.toString( pr.prod_id ) );
probList.clear();
rowMap.removeAllElements();
// Clear out any records...
getJiflet().getDBRecord().clear();
clearScreen();
int rows = 0;
try
{
String sql = "select * from prod_prob " +
"where prod_id = " + Integer.toString( pr.prod_id ) + " " +
" order by desc_text";
ResultSet rs =
getJiflet().getConnector().getStatement().executeQuery( sql );
while ( rs.next() )
{
ProductProblemRecord ppr =
( ProductProblemRecord )getJiflet().getDBRecord();
ppr.parseResultSet( rs );
String s = Integer.toString( ppr.prob_id );
s += " ";
s += ( new FileDate( ppr.start_date ) ).toNormalString();
s += " ";
s += ( new FileDate( ppr.end_date ) ).toNormalString();
s += " ";
s += ppr.desc_text;
probList.addItem( s );
// Make a row map...
rowMap.insertElementAt( ppr, rows );
rows++;
}
if ( rows > 0 )
{
probList.select( 0 );
showProblemDetail();
}
}
catch( SQLException e )
{
getJiflet().getConnector().errorLog( e.toString() );
}
getJiflet().showStatus( "Problems Loaded!" );
newButton.enable();
}
This routine relies on the fact that a product has been selected. It retrieves the selected index from the product List and uses that to get the product ID to use for its lookup.
After the problems for that particular product ID have been retrieved, they are loaded into the problem List. After all of these have been loaded, the first one is chosen by default.
When it is chosen, the problem detail is displayed:
//****************************************************************************
//* showProblemDetail ; *
//****************************************************************************
public void
showProblemDetail()
{
// Get my record...
ProductProblemRecord ppr =
( ProductProblemRecord )rowMap.elementAt(
probList.getSelectedIndex() );
// Fill in the fields...
getJiflet().setDBRecord( ppr );
moveToScreen();
}
Here, you utilize the display mechanism built into your SimpleDBUI class. This enables you to use the setDBRecord() method and call the moveToScreen() method. This method formats the data and moves it to the screen.
When the user changes products, you need to reload the problem list. To do this, trap some of the events for your lists. When a list item is selected, an Event.LIST_SELECT is generated. Simply capture this event and reload accordingly:
//****************************************************************************
//* handleEvent ; *
//****************************************************************************
public boolean
handleEvent( Event event )
{
if ( event.target == prodList )
{
if ( event.id == Event.LIST_SELECT )
{
loadProblemList();
return( true );
}
}
if ( event.target == probList )
{
if ( event.id == Event.LIST_SELECT )
{
showProblemDetail();
return( true );
}
}
return( super.handleEvent( event ) );
}
When the product list has a new selection, call the loadProblemList() method. When the problem list changes, show the problem detail with the showProblemDetail() method.
This application presented you with an interesting user interface challenge, not to mention two table database lookups. You needed to present an intuitive interface to the user that was easy to use.
You also enhanced the interface using nested layout managers. You nested a GridLayout within a BorderLayout to space your components evenly. You also nested a GridBagLayout within another BorderLayout.
To recap, this application introduced the following Java Intranet programming topics:
This chapter introduced you to the final sample application in your intranet application suite: the Customer Support Maintenance application. This program is responsible for tracking customer support information regarding problems with the products that you sell. This application should be useful to customer service as well as technical support employees on your intranet.
In the next chapter, "Extending the Java Intranet Framework," I'll talk about the future of the Java Intranet Framework (JIF) and your intranet.