Chapter 20

Customer Support Maintenance


CONTENTS


Introduction

In , "Product Maintenance," you created a data entry program that allows users to create and maintain data in a product table. It is a useful app in itself; however, the app 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 app of the tutorial, it certainly is not the least. It is the second most complex intranet app that you've created, the first being the Customer Support Maintenance app. This app 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 app:

This four-step format is used throughout all of the sample app chapters. Hopefully, it will provide you with valuable insight and ideas for creating your own intranet apps.

app Design

Remember that your goal is to make intranet users want to use the app. Simply placing an app out on the network is not enough. A program has to be functional, goodlooking, and most of all, easy to use. This app has been designed primarily for ease of use. Figure 20.1 is the proposed user interface for the Customer Support Maintenance program. Screenshot : 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 app. 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. Screenshot shows what the screen program will look like when editing an existing problem. Screenshot : The app editing an existing problem.

Database Design

This app 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 app is called the product problem table. This table is a child table from the product table described in . Table 20.1 shows the columns that you need to store your problem information.

Table 20.1. The product table layout.
Description Column Name Type Can Be Null?
Default
Product ID prod_id number( 5 ) No
None
Problem ID prob_id number( 5 ) No
None
Description desc_text char( 255 ) No
None
Reported By reported_by_text char( 80 ) Yes
None
Problem Resolution resolution_text char( 255 ) Yes
None
Problem Date start_date date Yes
None
Resolution Date end_date date Yes
None

Screenshot shows the entity relationship diagram for your database. This data model diagram represents the entire database developed for this tutorial. Screenshot : The entity relationship diagram with employee tables.

Note
Entity relationship diagrams are discussed in , "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!

Implementation

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 coding considerations that came up during the app construction.

Building the User Interface

The Customer Support Maintenance program is the second most complex program in this tutorial. It utilizes some of the cooler user interface classes discussed in , "User Interface Classes." However, it doesn't operate like any other app in the tutorial. This app does use the SimpleDBJiflet, SimpleDBUI, and DBRecord classes, although their use is unconventional compared to the last apps. This app 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. Screenshot : 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.

Hiding Components

This app 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.

Database Access

This app 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 apps. 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 apps.

Before you can edit problems, you need the user to select a product.

Retrieving the Product and Problem Lists

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.

Product and Problem Selection Changes

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.

Programming Considerations

This app 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 app introduced the following Java Intranet coding topics:

Summary

This chapter introduced you to the final sample app in your intranet app suite: the Customer Support Maintenance app. This program is responsible for tracking customer support information regarding problems with the products that you sell. This app 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.


Java ScreenshotJava ScreenshotJava ScreenshotJava Screenshot



Comments