UNIVERSITY OF CALIFORNIA AT SANTA BARBARA
CS 193
Design and Development of a Three-Tiered Corporate Extranet System
Patrick Rayes
1/12/99
Introduction
During the midpoint of my education at
UCSB I decided to pursue a part-time position at a local company that would involve me in
the management of information systems and technologies. My efforts for locating such a
position entailed sending resumes via E-mail to numerous local company's listed on our
Computer Science Department's Job
Listing Web site. In late October of 1998 I was contacted by the Chief Executive
Officer (CEO) of a somewhat young local company named Event
Electronics. Over a brief telephone conversation
he had mentioned that he was looking for someone to establish and manage the company's
Management Information Systems (MIS) department. Surprisingly, the resume which I had sent
them only a few days ago fit the requirements hand-in-hand - this great opportunity was
undoubtedly what I had been looking for and I was determined to take advantage of it
immediately.
A few days later I met with the CEO and
Chief Financial Officer (CFO) of the company, and before I could even express my enormous
interest in the position I found myself signing the necessary documents to employ me at
the company as a part-time employee. School was still a priority in my life, which was
well understood by the people hiring me, so I was required to work part-time during
regular school quarters with the option of working full-time during school breaks.
Consequently, another individual was hired as a full-time Network/Systems Administrator to
aid me in the organization of the company's already established Local Area Network (LAN).
My career had just taken a positive turn and I was eager to excel my work experience and
essential business skills. The next twelve months involved the coordination of various
exciting projects to aid the company in its efforts to extend its business processes with
cutting-edge information systems and technologies.
The first few months at the company were
extremely demanding, and required a clear and open mind to absorb the various issues at
hand and how they should be best dealt with. Since a new department was being established
at the company, I took the responsibility of typing up a mission statement to be handed
out to each and every employee at the company. This document acted as a primer for the
evolution of the department - it entailed a brief introduction of what the MIS department
stood for and the value-added services it offered, along with outlooks for various
projects, budgeting, organization, key initial goals, infrastructure and business
integration. The results of this document were incredibly positive, since it allowed
everyone at the company to understand the importance behind information systems and where
the company will be taking itself through the use of this valuable resource.
During the midpoint of my career I was involved with a sizeable project
that required the interaction and business process alignment of all departments in the
company. The project involved the implementation of a $60,000.00 corporate-level financial
and Enterprise Resource Planning (ERP) centric database system named Navision Financials. An outside contractor, key department heads
and myself headed this four-month long system implementation and training project. Close
to the end of the implementation phase, the companys CEO had asked me to design and
develop an Extranet system directly integrated with Navision Financials' Sales and
Receivables database. This system was to offer various information (refer to orders.asp and ordersbyproduct.asp
for detailed information) on past and current orders for use by the companys
dealers, distributors and sales representatives across the nation. Through this
information they would be able to keep track of their orders in real-time over the
Internet while better outlining their projected supplies and demands for each product
ordered. In addition, orders would be traceable through the Web sites of common shipping
carriers for immediate location and order delivery dates.
This project required skills which I had
not yet completely developed and resources that were not yet available - at this point I
was facing something which I had initially thought was beyond my reach. As a primary
objective I began researching the implications of designing and developing an Extranet
system, and how to gear my programming expertise towards a new array of development tools.
Obviously, my single resource for the information that I acquired was the Internet -
specifically discussion groups and developer Web sites focused towards Extranet systems.
After one week of researching I began outlining a Requirements Analysis (RA) and Domain
Analysis (DA) of the project, along with information regarding the available development
tools for developing and deploying a Rapid Application Development (RAD) type system.
Additionally, this one week long research period helped align the tasks and costs required
while clarifying the base of development tools and server systems to be applied. Due to a
limited budget and timeline, I was required to select low-cost and rapid solutions for
this project. Therefore, I chose to build the project upon Microsoft BackOffice server
components and Microsoft
Visual Studio development tools. These software systems were geared towards quickly
developing and deploying scalable database-integrated Web sites within corporate LAN's,
and were clearly a solution to the project's architecture. In addition, my fluency in the
use of these tools and server components, due to their not-too-recent availability on my
home computer, aided the acceleration of the project.
My role in the project was
"singular" - I designed, developed and managed the project from conception to
creation. The skills I applied were somewhat already known to me, however most of the
project required me to develop new skills quickly in order to keep up with the pace of the
project and meet the project's deadline. I was determined to complete the project, and
through this determination and accelerated learning I was able to deliver it on time and
fully complete.
System and Project Outline
The system was named Event Order System
(EOS) and was composed of the following base system functions:
- Provide dealers, distributors and sales representatives with various customizable and
unique sales and order reports.
- Provide dealers, distributors and sales representatives with order tracking through
United Parcel Service, FedEx and DHL.
- Provide dealers, distributors and sales representatives with basic on-line account
management tools.
- Offer the above services over an authenticated[1] and secured[2] medium.
- Build the system with footage for future scalability.
- Prepare the system for integration with the next versions of Navision Financials.
- The next versions of Navision Financials are based on Microsofts SQL Server, and
since EOSs business logic is Simple Query Language (SQL) based immediate integration
with SQL Server is undemanding.
The project entailed the deployment of a $5,000.00 Hewlett Packard Web-application server
running Microsoft Windows NT
Server 4.0 and Microsoft
Internet Information Server 4.0. In addition, the following development timeline and
project management issues had been agreed upon:
- Once a week project reports and progression meetings.
- One week purchase and deploy necessary server hardware and software.
- One week test and verify installed server hardware and software components.
- One week compose rudimentary requirements and domain analysis documents.
- Two weeks development time (coding).
- One week deploy application and begin testing various system functions.
- One week compose final design specifications and system maintenance notes.
In addition, an Intranet project management Web site was setup to
provide the following key information on EOS and Navision Financials to all employees
within the company:
- Constantly updated project timeline.
- Dynamic responsibilities for each key department head.
- Discussion section for posting questions, comments and/or suggestions.
- Rotating lists of "action items" for each department based on each
items/departments priority.
- Key dates for system maintenance, project meetings, outside contractor work schedules
etc.
- Links to Internet based information on Navision Financials, Microsoft and other project
management tools/information.
Back-End
Web-Application Server
The preparation of a back-end for a
three-tiered system generally involves verifying the integrity of the back-end database
system(s), deploying a Web-application server, and establishing a dedicated communication
channel between the database and Web-application server. The following installations were
performed as part of the deployment of the Web-application server:
- Install the physical server in the companys server room and connect/verify all
network connections.
- Install Microsoft Windows NT Server 4.0
- The server was setup as a stand-alone server running the following services and
protocols:
- Services: Computer Browser, NetBIOS Interface, Network Monitor Agent, RPC Configuration
and Server.
- Protocols: NetBEUI, TCP/IP.
- The network configuration of the server involved two 10 Base-T Ethernet adapters:
- Adapter 1: connected to the corporate LAN hub with an unregistered private Class C IP[3] issued by the Primary Domain Controller (PDC) on our LAN.
- Adapter 2: connected to the corporate Wide Area Network (WAN) hub with a registered
Class C IP issued by WestNet[4].
- Install Microsoft Windows NT Server Service Pack 3.0.
- Install 128-bit Microsoft Socket Channel for 128-bit Secure Socket Layer (SSL) data
encryption.
- Install VeriSigns 128-bit Security Key.
- Install the latest version of Microsofts Data Access Component and Open Database
Connectivity (ODBC) drivers for Microsoft Access 97.
- Install Navision Financials Client application.
- Install Navision Financials C/ODBC drivers.
- Install Microsoft Access 97.
- Install Microsoft Office 97 Service Pack 1.0 and 2.0.
- Install Microsoft
FrontPage 98 client and Web server extensions.
- Install Microsoft FrontPage 98 Service Pack 1.0.
- Verify Operating System (OS) installation and networking services, protocols and
adapters.
- Secure the server using Microsofts C2 Manager.
The diagram below illustrates the implemented back-end system:
Back-End Databases
The Navision Financials and Extranet
Web-application Server User databases comprise of the following table/field structures
(only tables/fields which are used in this system are listed):
Navision Financials Database
- Sales Header (non-invoiced sales) main header of
order
- Your Reference purchase order number.
- No internal order number.
- Order Date date order was posted.
- Shipment Date date order was shipped.
- Package Tracking No tracking number for shipment carrier.
- Shipping Agent Code code of shipment carrier.
- Sell-to Customer Name sales representative sold to.
- Sell-to Customer No sales representative sold to (identification number).
- Document Type type of document (Order).
- Sales Line (non-invoiced sales) actual line items
- No internal line item product code.
- Description description of line item.
- Quantity Shipped number of items shipped for particular line item.
- Quantity actual quantity order for particular line item.
- Amount amount in dollars for particular line item.
- Type type of line item (Item).
- Sales Invoice Header (invoiced sales) main header of sale
- Your Reference purchase order number.
- No internal sale number.
- Order Date date sale was posted.
- Shipment Date date sale was shipped.
- Package Tracking No tracking number for shipment carrier.
- Shipping Agent Code code of shipment carrier.
- Order No internal order number.
- Sell-to Customer Name sales representative sold to.
- Sell-to Customer No sales representative sold to (identification number).
- Sales Line (invoiced sales) actual line items
- No internal line item product code.
- Description description of line item.
- Quantity number of items shipped for particular line item.
- Amount amount in dollars for particular line item.
- Type type of line item (Item).
Extranet User Database
- tblAccessLogs logs of account accesses
- UserName unique user name.
- TimeIn time of log on.
- DateIn date of log on.
- LogonStatus status of log on (Fail or Success).
- tblAccounts list of unique user accounts
- CreationTime time user account was created.
- CreationDate date user account was created.
- Group group user account belongs to (representative, distributor or dealer).
- DealerCode Navision dealer code (only for dealers).
- RepresentativeCode Navision sales representative code (only for reps).
- FirstName first name of user.
- LastName last name of user.
- Company company that user is affiliated with.
- UserName unique user name.
- Password user password.
- Email users E-mail address.
- Active boolean field for whether account is active.
Front-End
The front-end of this system comprises
of the second and third tiers of the entire system, which includes the business logic (SQL
queries against Navision Financials and the Extranet user database) and the Active Server
Pages (ASP).
All ASP pages use JavaScript to validate proper data entry in form data
fields, which places data verification to the clients side for faster processing. In
addition, all ASP pages check to see if the AccountVerified session variable[5]
is set to true if it is false (meaning that the user never logged on) the system
redirects the user to default.asp, otherwise the system continues through every ASP page
normally.
The following is a list of all the ASP pages[6]
including the implemented SQL queries (business logic) within them for each type of user
(some pages are not unique to the user type):
Account.asp
- Purpose
- To display information pertaining to the users account with options to change
password and E-mail
- Name, Company, User Name, E-mail, Last Log On Date and Time.
- SQL Query(s)
- None, simply uses already established session variables from authenticate.asp.
- Form(s)
- E-mail Change Form
- Allows the user to change E-mail.
- Redirects to email.asp for processing.
- Password Change Form
- Allows the user to change password.
- Includes two fields (extra one for verification).
- Redirects to password.asp for processing.
Authenticate.asp
- Purpose
- To verify the users user name and password against the Extranet database and
update the Extranet databases account access logs.
- SQL Query(s)
- Compares user name and password entered from default.asp to tblAccounts for validity and
returns record count.
- If record count is 1, then do the following
- Capture the following database fields as session variables: UserName, FirstName,
LastName, Company, Group, RepresentativeCode, DealerCode, Email, LastLogonDate and
LastLogonTime.
- Set the AccountVerified session variable to true.
- Update tblAccessLogs with successful log on.
- Redirect to success.asp.
- Else
- Set the AccountVerified session variable to false.
- Update tblAcceessLogs with failed log on.
- Redirect to fail.asp.
- Form(s)
Default.asp
- Purpose
- To capture the users user name and password and send them authenticate.asp for
verification.
- SQL Query(s)
- Form(s)
- Log On Form
- Take users user name and password and transmit them to authenticate.asp for
verification.
Email.asp
- Purpose
- To update tblAccounts with the new users E-mail entered from account.asp.
- SQL Query(s)
- Update tblAccounts with new E-mail address.
- Update E-mail session variable with new E-mail address.
- Form(s)
Fail.asp
- Purpose
- Notify the user that log on failed and offer user to log on again.
- SQL Query(s)
- Form(s)
Help.asp
- Purpose
- Provide the user with a list of Frequently Asked Questions (FAQs) based on using
the system efficiently and troubleshooting it.
- SQL Query(s)
- Form(s)
Home.asp
- Purpose
- Welcome the user after having successfully logged on.
- SQL Query(s)
- Form(s)
Navigate.asp
- Purpose
- Provide the user with the following list of "linked" buttons
- Home redirect to home.asp.
- Reports redirect to reports.asp.
- Account redirect to account.asp.
- Help redirect to help.asp.
- Quit redirect to quit.asp.
- SQL Query(s)
- Form(s)
Orders.asp
- Purpose
- Provide dealers/distributors with a data table containing the following data columns of
each ordered line item:
- Purchase Order Number.
- Internal Order Number.
- Invoice Number.
- Order Date.
- Description.
- Quantity Ordered.
- Quantity Shipped.
- Expected Date of Shipment.
- Tracking Number hyperlink to appropriate shipping carriers Web site that
automatically pulls up the packages tracking information.
- Provide sales representatives with a data table containing the following data columns of
each ordered line item:
- Dealer Ordered For.
- Purchase Order Number
- Internal Order Number.
- Invoice Number.
- Order Date.
- Description.
- Quantity Ordered.
- Quantity Shipped.
- Expected Date of Shipment.
- Tracking Number hyperlink to appropriate shipping carriers Web site that
automatically pulls up the packages tracking information.
- SQL Query(s)
- Dealers/Distributors:
- Two sets of queries query the Navision database for orders (unshipped orders) and
invoices (shipped orders). Each query filters data for dealers/distributors only and based
upon the user-specified filter criteria on reports.asp.
- Dealers/Distributors:
- Two sets of queries query the Navision database for orders (unshipped orders) and
invoices (shipped orders). Each query filters data for sales representatives only and
based upon the user-specified filter criteria on reports.asp.
- Form(s)
- NOTE
- Each output table and SQL query run is transacted according to the user logged on. This
ensures that dealers/distributors will not see data pertaining to sales representatives
and vice versa.
Ordersbyproduct.asp
- Purpose
- Dealers/Distributors
- This report is identical to orders.asp, however the Description column is the first
column to the left in the table, and all data rows are grouped within each Description
set. This allows the user logged on to navigate through each product description set while
analyzing the automatically calculated totals[7] for each set.
- Sales Representatives
- This report is identical to orders.asp, however the Description column is the first
column to the left in the table, and all data rows are grouped within each Description
set. In addition, the Dealer Ordered For column is the second column to the right, and all
data rows are grouped within each Dealer Ordered For set. This allows the user logged on
to navigate through each product description set and dealer set while analyzing the
automatically calculated totals[7] for each set.
- SQL Query(s)
- SQL queries are identical to the ones used in orders.asp, however they were slightly
modified to accommodate for the Description and Dealer Ordered For data groupings.
- Form(s)
- NOTE
- Each output table and SQL query run is transacted according to the user logged on. This
ensures that dealers/distributors will not see data pertaining to sales representatives
and vice versa.
Password.asp
- Purpose
- To update tblAccounts with the new users password entered from account.asp.
- SQL Query(s)
- Update tblAccounts with new password.
- Form(s)
Quit.asp
- Purpose
- Close all session variables and log user off.
- SQL Query(s)
- Form(s)
Reports.asp
- Purpose
- Dealers/Distributors
- Provide data filtering options start/end dates, dealer ordered for, and product.
Redirect to orders.asp or ordersbyproduct.asp based on data filtering options.
- Sales Representatives
- Provide data filtering options start/end dates, and product. Redirect to orders.asp or
ordersbyproduct.asp based on data filtering options.
- SQL Query(s)
- Get most recent list of products and dealers from Navision database to display on forms
below.
- Form(s)
- Dealers/Distributors
- Data filtering pull-down menus:
- Start Date month and year.
- End Date month and year.
- Product Description.
- Sales Representatives
- Start Date month and year.
- End Date month and year.
- Product Description.
- Dealer Ordered For.
Success.asp
- Purpose
- This is a frame page the places navigate.asp at the top (10% spacing) and home.asp at
the bottom (90% spacing).
- SQL Query(s)
- Form(s)
Footnotes
- Authentication was implemented by storing
and retrieving user names and passwords from a separate back-end database.
- Security was implemented by use of a
128-bit Secure Socket Layer key issued from Verisign (http://www.verisign.com/).
- An IP address that is not registered on
any Domain Name Server and usually used within LANs behind firewalls.
- The companys Internet Service
Provider for Frame Relay service (http://www.west.net/).
- A session variable is a unique per-user
variable that resides in memory and is accessible on any ASP page.
- Source code is available upon request.
- Quantities, cost and net total per
grouping.