Docstoc

ibisph-admin

Document Sample
ibisph-admin Powered By Docstoc
					            Indicator Based Information System for
            Public Health - System Documentation




Created by: Garth Braithwaite
Last saved by: Garth Braithwaite
Last printed: 9/16/2012 1:12:00 AM
Filename: C:\Docstoc\Working\pdf\f3614e95-9e03-45ff-96bd-58822eedf4fc.doc   Total Pages: 51
How to Create a Table of Contents

         To create a Table of Contents for this report, position your cursor on the blank TOC page. From the
         Insert menu choose Index and Tables. Click on the Table of Contents tab. Be sure to use the
         Custom Style format.
       Section


        1
System Overview
Description, Technology Used, Requirements
1.1 Description

       The Indicator Based Information System for Public Health (IBIS-PH) provides a central data
       repository and standardized reporting mechanism for Utah related health indicator data. Users
       include employees of the Utah Department of Health and Utah’s 12 local health districts,
       community planners, private and public health practitioners, and others (such as legislators,
       teachers, researchers, health plans, etc.) interested in public health in Utah.

       This document provides technical information about the system architecture, the IBIS-PH database
       design, the Actuate web reports and printed reports, and the data maintenance web application
       (HTML, Java Script, and back end Java servlet code). The IBIS-PH system consists of the
       following four major sections:

       Database                 The Database is the central data repository that stores all the IBIS-PH
                                indicator information. This section will discuss the entire schema - all the
                                tables, each table’s columns, the triggers, naming conventions, and any
                                special notes.

       Public Site              The Public Site is the web interface that the public uses to view the
                                information stored in the IBIS-PH database. It consists of static web pages
                                that give general IBIS-PH information and provides a link to the Actuate Index
                                report. This Index report is a list of all Indicators defined in the database.
                                Each indicator has one or more graphical views and other associated text
                                reports that can optionally, link to other indicators and other Actuate reports.

       Admin Site               The Admin Site provides a web interface for IBIS-PH users to maintain the
                                IBIS-PH data.

       Printed Reports          The Printed Reports section describes how the various printed books are
                                created and maintained.

       The balance of this section describes the technology used and lists the requirements for each part
       of the IBIS-PH system. Following this section are sections that describe the four major pieces of
       the system listed above.


          This document is not a user manual for the client application, nor does it contain any business rules or process
          information. The intent of this document is to help the existing support staff and/or future developers understand
          and maintain the IBIS-PH system.



                                                                                                                      Page: 2
 1.2 Technology Used

Database:

              Oracle Relational Database Management System (RDBMS) Enterprise Server 8.x, with an
               object type and methods, and custom triggers used to set modified dates and maintain the
               integrity of data values.

Public Site:

              Static HTML web pages.

              Java Script 1.2.

              Actuate e.Report Designer Professional version 5.0 (used to design and maintain the web
               reports).

              Actuate Report Server version 5.0 (used to display the designed web reports).

Admin Site:

              Java Server Pages (JSP) (user interface - presentation).

              Java Script 1.2 (user interface data validation and control).

              Java Servlets and objects (back end business logic and database access).

              Servlet/J2EE compliant application server.

              Site Minder (system security and user validation).

              Information Technology Service’s (ITS) Lightweight Directory Access Protocol (LDAP) user
               information servlet (allows user information to be retrieved from outside the IBIS-PH system).

Printed Reports:

              Actuate e.Report Designer Professional version 5.0 (used to design and run the master
               controlling report).




                                                                                                     Page: 3
 1.3 Requirements

Public Site - Client:

               Internet connection.

               Browser capable of supporting Java Script 1.2+, DHTML, and HTML 4.1+. Tested with
                Microsoft Internet Explorer version 5.5, Netscape Navigator versions 4.77 and 6.1.

Public Site - Server:

               Connectivity to the Internet.

               IBIS-PH HTML web pages, Java Script files, and image files.

               Web server that supports server side includes and interfaces with the Actuate Report server.

               Connectivity to the IBIS-PH data.

               IBIS-PH database schema and data.

               IBIS-PH Actuate reports.

Admin Site - Client:

               Utah State Intranet connection/Inner Web connection (username/password).

               Browser capable of supporting Java Script 1.2+, DHTML, and HTML 4.1+. Tested with
                Microsoft Internet Explorer version 5.5, Netscape Navigator versions 4.x and 6.1.

Admin Site - Server:

               Connectivity to the Intranet.

               IBIS-PH JSP pages, Java Script files, and image files.

               Web server and JSEE compliant application server (or any app server that supports servlets,
                JNDI based database pooling, JSPs with server side includes, and servlet init
                parameters/properties.

               Connectivity to the IBIS-PH data.

               IBIS-PH database schema and data.

               IBIS-PH RequestBroker servlet, controller classes, bean classes, utility classes, and general
                admin classes.


               It is not known at this time how the system can/will perform under a load. The system was never stress tested.




                                                                                                                           Page: 4
1.4 System Architecture Diagram


               Intranet
               Data
               Maintenance
               Site (Admin )                          st
                                                    ue
                                                  eq L)




                                                                     (8 (HT
                                                )R M




                                                                       )R M
                                              (1 (HT




                                                                         es L)
                                                                           po
                                                            iPlanet 6




                                                                             ns
                                                           Application




                                                                                e
                      Request Broker Servlet                Server &
                        (authenticates and                 web server
                      loads/calls appropriate
                            controller)                                          JSP
                                                                             (presentation
                                                                                 only)
                                                              ard
                                 Controller            Fo
                                                         rw
                                                   (6)                          (7) Gets
                                   (2)


                                                                                  Data

                            Controller                                        Java Bean
                           (non obect                                         Component
                                                             (4)
                        specific business                                   (holds data and
                                                           Creates
                           rules & db                                        specific bean
                            access)                                            methods)
                                     Sets




                                                                   )   a
                                                                 (5 Dat
                                        (3)




                                                                   ts
                                           Data




                                                                Ge




                                                               Publish

               Internet                 Admin Data                            Public Data
                                                              Oracle 8 i
               Public                                         DB Server
                                                                                     Gets




               Site
                                                                                      (3)
                                                                                         Data




                        Web Server                                                  Actuate Server




                                                           (2) Actuate
                                                            Request
                    Static Web                                                  Actuate
                       Page                                                     Report



                                    (
                                 (2 1) R
                                   )S e                                         e/
                                      tat que                                 ns L)
                                         ic
                                            Pa st (H                        po TM
                                                                          es H
                                              ge TM                    )R t(
                                                 (H L)               (4 por
                                                    TM                 Re
                                                      L)




                                                                                                     Page: 5
       Section


        2
Database Configuration
Database Overview, Naming Conventions, Security, SQL Scripts, and
Issues

2.1 Database Overview

       The IBIS-PH database is a repository that is managed by the Oracle RDBMS software. The
       database is a generalized database structure designed to be simple and flexible so that all types of
       indicator data are handled by the system in a consistent manner. The main portion of the structure
       is comprised of three main tables: Indicators, Indicator Views, and Indicator View Values. The
       other tables are mostly support and validation tables needed by the system. Database triggers are
       used to automatically catch changes and timestamp records when modified. Triggers are also
       used to keep maintain detail records.

       The IBIS-PH system utilizes the same schema implemented in two owners/accounts. The first
       contains the data that the public views either from the Public Web Site or in the Printed Reports.
       These data have been reviewed and approved prior to their publishing into the production public
       data repository. The second is a staging data repository. It contains data that is being
       modified/worked on by IBIS-PH domain data owners/editors. When these data are ready, they are
       reviewed and when approved, they are pushed into the public site’s production data repository.

       Most of the tables that contain data used by the PUBLIC reporting site contain a MODIFIED_DATE
       column. Triggers automatically date time stamp the record when modified. This mechanism was
       designed to identify which records should be inserted/updated from the ADMIN schema to the
       PUBLIC schema. Another mechanism that is used is the ACTIVE_FLAG column. This controls
       whether records are displayed on the admin site validation lists as well as providing the data
       needed to deactivate records on the PUBLIC site. Without the ACTIVE_FLAG, separate tables
       would be needed to keep track of deleted records. Another benefit of this approach is that records
       can be restored to their active state without having to reenter the information.


          At the time of producing this document, there is not an automated mechanism to push the data from the staging
          admin tables to the public tables. This will have to be done with exports and imports or direct copying.



2.2 Naming Conventions

       The IBIS-PH tables, columns, procedures, functions, methods, types are all UPPER_CASE with
       an underscore separating each significant word. Table and column names are singular and are
       explicitly, fully spelled out for clarity (e.g. no acronyms or short, abbreviated names – except when
       names exceed the 30 character Oracle limit).


                                                                                                                 Page: 6
          Column names are named in a more generic, object oriented fashion to facilitate verbose naming
          and even more importantly for high consistency and reuse.

          Oracle reserve words and supplied packages, procedures etc. are all lower case.


 2.3 Database and Application Security

          Database security is limited/handled by the standard Oracle user account and with ITS’s network.
          The Public Site’s Actuate reports contain the username and password needed to access the data.
          A user account was setup with read only privileges and synonyms to the real data tables. This was
          an extra step that was put in place to keep the owner user name private from the internal IBISPH
          users (so they wouldn’t know the username and password which would allow them to go into the
          database with a program like MS-Access etc.). This step was not needed for the general public
          because they do not have access to the report executable files and the database, when put behind
          a firewall, can only be accessed from a few select internal ITS machines.

          The Admin site relies on the same username/password but is a little bit different. It resides inside
          the ITS intranet and is not viewed by the outside public. Even though the tables have foreign key
          (FK) constraints on all the tables, the admin site application should still be used to maintain the
          data. To guard against intranet users connecting with SQL*Plus or any other data access tool
          (MS-Access with ODBC etc), this database should be put behind a firewall with only a few server
          machines being allowed access. If outside access by other applications is required then the proper
          accounts with privileges can be created.


 2.4 SQL Scripts

          Scripts were used to create and maintain the database tables, objects, and triggers. They can be
          used in a variety of ways such as copy and paste, or ran from inside SQL*Plus via the “@”/run
          command.

Script Naming Convention:

          The common scripts used to create and drop the IBIS-PH database objects are named as:
          OBJECTTYPE_ACTIONTYPE_[SCHEMA].SQL where:

             OBJECTTYPE is an abbreviated 3-4 characters that represents the type of the object.

             ACTIONTYPE is a one letter suffix code describing the type of action that the script will do.
              Create scripts have a _C file suffix. while the drop scripts have a _D suffix.

             SCHEMA is optional and is only used for items that are specific to that owner/schema. If not
              specified, then the script applies to all/both parts of the IBIS-PH schema.

          Examples: the FK_C.SQL script will Create all the Foreign Key (FK) constraints for the IBIS-PH
          system. The TAB_D_ADMIN.SQL script will Drop all the tables associated with only with the
          ADMIN portion of the system.

Scripts File Descriptions:

          Script Name                   Description

          FK_C.SQL                      Creates the Foreign Key constraints for all tables.
          FK_D.SQL                      Drops/deletes the Foreign Key constraints for all tables.
                                                                                                      Page: 7
         IBIS_SETUP.SQL                      Contains the table space creates etc. needed for ITS’s environment.
         PACK_C.SQL                          Contains the Package creation statement. This package contains a
                                             variable that is used to get around the “mutating trigger” issue.
         TAB_C.SQL                           Creates the tables needed by both the admin and public sites.
         TAB_D.SQL                           Drops/deletes the tables needed by both the admin and public sites.
         TAB_C_ADMIN.SQL                     Creates only the additional tables that are only applicable to the
                                             Admin Site.
         TAB_D_ADMIN.SQL                     Drops only the tables that are only applicable to the Admin Site.
         TRIG_C_ADMIN.SQL                    Creates the triggers that are used for the Admin Site.
         TRIG_D_ADMIN.SQL                    Drops the triggers that are used for the Admin Site.


            NOTE: There are no triggers for the Public Site since this data is read only – e.g. not ever modified by a user.




            !!! IMPORTANT !!!: DROP scripts should be used with EXTREME caution. These scripts do not ask for
            confirmation and will cause things to go away without any ability to undo/rollback.



 2.5 Issues

Performance Tuning:

         The system was never stress tested with huge volumes of data and as such performance tuning
         may need to be performed on the system to increase retrieval speed when it becomes more
         heavily populated and used. Currently, most of the tables are accessed via their primary key
         (which by default are indexed automatically). The only main table that is not indexed is the
         INDICATOR_VIEW_VALUE table which could grow to be of moderate size. If performance ever
         becomes an issue, this table should be considered for an index.




                                                                                                                           Page: 8
       Section


        3
Database Structure Descriptions
Table Design Overview, Table & Column Descriptions, and the Database
Entity Relationship Diagram

3.1 Table Design Overview

        The IBIS-PH database is a generalized database structure designed to be simple and flexible.
        With this design you have the ability to store any type of indicator data. While the database is a
        normalized design it is not a typical relational design. This design is based on a model that stores
        generic type data instead of specific data with relationships. With this flexible model, numerous
        indicators can be stored and viewed with minimal reports to write and maintain and with minimal
        coding for the data maintenance application. The down side to this approach is that the each data
        set’s “real” data model is not implemented (which would allow for the true data and it’s relationships
        to be stored).

        The bulk of the design consists of a master indicator table with a detail table that contains view(s).
        Each view record has detail records that contain the actual numerical data to be displayed in a
        graph/tabular report.

        Section 3.9 contains the Entity Relationship Diagram (ERD) for this design. The rest of this section
        describes the Data Definition type objects (or database structures) that exist in the IBIS-PH
        schema. This includes an overview/description of the tables/columns, any applicable triggers, and
        any foreign key relationships.


3.2 Common Table Column Descriptions

        Listed below are the common column/field definitions used throughout the IBIS-PH tables. If the
        table uses a common definition, then just the name will be listed (with optional notes to describe
        anything that is unique/different/special).

        Column Name                    Type          Not Null Description/Notes
        NAME                           varchar2(255)   x      Unique primary key used to reference the
                                                              record. The naming convention for this
                                                              value is to use Mixed Case Names or ALL
                                                              UPPER CASE names for acronyms that
                                                              are consistent with the names that are
                                                              already being used. An underscore “_” is
                                                              typically used when you have an item that
                                                              has a “v.s.”. Example: Utah v.s. US would
                                                              be “Ut_US” or “Utah_US”. Also, if a record
                                                              is a detail record the name is typically:
                                                              MasterName.ChildName. Note the period
                                                              “.” delimiter.   Character names were

                                                                                                      Page: 9
                                                              chosen instead of numerical IDs (that are
                                                              auto generated via triggers and
                                                              sequences) to help when viewing and
                                                              debugging the data. Also, names are
                                                              much easier to use when pushing data
                                                              from one schema to another.
       TITLE                         varchar2(100)     x      Title that is displayed to the admin user
                                                              when selecting the record or that can be
                                                              displayed on a report.
       TEXT                          varchar2(255)     x      Actual textual/data part of the record.
       DESCRIPTION                   varchar2(255)            Optional internal description that can be
                                                              used to describe/define the record.
       SORT_ORDER                    number                   Optional value that is used to control the
                                                              ordering of the values.
       ACTIVE_FLAG                   varchar2(1)              Field that controls if this record is viewable
                                                              in a list of value values on the admin site.
                                                              Also, this field controls which records are
                                                              pushed to the public site. If the flag is not
                                                              set, it will not be copied to the public site.
                                                              If null it is NOT active. If it contains an ‘x’
                                                              (or anything e.g. not null) it is considered
                                                              active. An ‘x’ is used to mimic a check box
                                                              ‘x’ and is also consistent with the admin
                                                              site’s HTML activeFlag checkbox.
       NOTE                          varchar2(4000)           Optional field that contains any internal
                                                              notes that an editor might want to attach to
                                                              the record.
       MODIFIED_DATE                 date                     Field that contains the date that this record
                                                              was last modified. This value is set via an
                                                              insert/update trigger on the table. If a
                                                              value is specified, then the trigger does not
                                                              replace the value. If it doesn’t exist, then
                                                              the trigger gets the system time (from the
                                                              Oracle server) and uses that value for the
                                                              record’s date timestamp. This value may
                                                              also be set via a trigger on an insert/
                                                              update to an associated detail table’s
                                                              record.


3.3 Table and Column Descriptions for the Public and Admin Schema

       Tables and columns are the objects that organize and contain the actual data. This section serves
       as the typical Data Element Dictionary for the tables that are needed/common to both the PUBLIC
       and ADMIN sites. Listed below is a basic description of each table and each column within that
       table used by/required for IBIS-PH.

DATASET_CATEGORY

       Master table used to define valid indicator view graph categories. The DATASET_CATEGORY
       _BIU_TRIG trigger is a before insert and update trigger used to set a date timestamp for the record
       when modified (if the modified date is not specified). The time that is used comes from the Oracle
       server.

       Column Name                   Type          Not Null Description/Notes
       NAME                          varchar2(255)   x      Unique primary key used to reference the
                                                            record.
                                                                                                   Page: 10
       TITLE                         varchar2(100)      x       Title that is displayed to the admin user
                                                                when selecting the record or that can be
                                                                displayed on a report.
       LABEL                         varchar2(100)              Optional category label that can be
                                                                displayed on the graph.
       DESCRIPTION                   varchar2(255)              Optional internal description that can be
                                                                used to describe/define the record.
       START_YEAR                    number(4)                  Optional starting year range value that can
                                                                be used by the admin application to create
                                                                a range of values. Note: this can be used
                                                                by the application to create any category’s
                                                                numerical sequence range of values.
       END_YEAR                      number(4)                  Optional ending year range value.
       SORT_ORDER                    number                     Optional value that is used to control the
                                                                ordering of the records on the admin site.
       ACTIVE_FLAG                   varchar2(1)                Field that controls if this record is displayed
                                                                on the admin site.
       MODIFIED_DATE                 date                       This value is set via an insert/update
                                                                trigger on the table and also from the detail
                                                                DATASET_CATEGORY_VALUE table.

DATASET_CATEGORY_VALUE

       Detail table used to define valid indicator view value graph category values. Associated with the
       master/parent category. Integrity is enforced with a FK constraint to the master DATASET_
       CATEGORY record.

       Column Name        Type          Not Null Description/Notes
       DATASET_CATEGORY_NAME
                          varchar2(255)   x      Foreign key used to reference the master
                                                 dataset category record.
       TITLE              varchar2(100)   x      Title that is displayed to the admin user
                                                 when entering the indicator view values.
                                                 This value is also used on the report for
                                                 the category labels.
       VALUE              varchar2(100)   x      Actual value that the indicator view value is
                                                 associated with and that is used by the
                                                 graph when grouping categories.
       DESCRIPTION        varchar2(255)          Optional internal description that can be
                                                 used to describe/define the record.
       SORT_ORDER         number                 Optional value that is used to control the
                                                 ordering of the records on the admin site.
       ACTIVE_FLAG        varchar2(1)            Field that controls if this record is displayed
                                                 on the admin site.

DATASET_SERIES

       Master table used to define valid indicator view graph series. The DATASET_SERIES_BIU_TRIG
       trigger is a before insert and update trigger used to set a date timestamp for the record when
       modified (if the modified date is not specified). The time that is used comes from the Oracle server.

       Column Name                   Type          Not Null Description/Notes
       NAME                          varchar2(255)   x      Unique primary key used to reference the
                                                            record.
       TITLE                         varchar2(100)   x      Title that is displayed to the admin user
                                                            when selecting the record or that is can be
                                                            displayed on a report.

                                                                                                     Page: 11
       DESCRIPTION                   varchar2(255)             Optional internal description that can be
                                                               used to describe/define the record.
       SORT_ORDER                    number                    Optional value that is used to control the
                                                               ordering of the records on the admin site.
       ACTIVE_FLAG                   varchar2(1)               Field that controls if this record is displayed
                                                               on the admin site.
       MODIFIED_DATE                 date                      This value is set via an insert/update
                                                               trigger on the table and also from a trigger
                                                               on the detail DATASET_SERIES_VALUE
                                                               table.

DATASET_SERIES_VALUE

       Detail table used to define valid indicator view value graph series values. Associated with the
       master/parent dataset series table via the DATASET_SERIES_NAME. . Integrity is enforced with
       a FK constraint to the master DATASET_SERIES record.

       Column Name         Type          Not Null Description/Notes
       DATASET_SERIES_NAME varchar2(255)   x      Foreign key used to reference the master
                                                  series record.
       TITLE               varchar2(100)   x      Title that is displayed to the admin user
                                                  when entering the indicator view values.
                                                  This value is also used on the report for
                                                  the series values.
       VALUE               varchar2(100)   x      Actual value that the indicator view value is
                                                  associated with and that is used by the
                                                  graph when grouping categories.
       DESCRIPTION         varchar2(255)          Optional internal description that can be
                                                  used to describe/define the record.
       SORT_ORDER          number                 Optional value that is used to control the
                                                  ordering of the records on the admin site.
       ACTIVE_FLAG         varchar2(1)            Field that controls if this record is displayed
                                                  on the admin site.

DATA_SOURCE

       Table that contains all the valid data sources. These data sources are then linked to the
       INDICATOR and INDICATOR_VIEW tables via intersection tables. The DATA_SOURCE
       _BIU_TRIG trigger is a before insert and update trigger used to set a date timestamp for the record
       when modified (if the modified date is not specified). The time that is used comes from the Oracle
       server.

       Column Name                   Type          Not Null Description/Notes
       NAME                          varchar2(255)   x      Unique primary key used to reference the
                                                            record.
       TEXT                          varchar2(100)   x      Actual text for the data source. This is the
                                                            information that is presented to the user.
       DESCRIPTION                   varchar2(255)          Optional internal description that can be
                                                            used to describe/define the record.
       SORT_ORDER                    number                 Optional value that is used to control the
                                                            ordering of the records on the admin site.
       ACTIVE_FLAG                   varchar2(1)            Field that controls if this record is displayed
                                                            on the admin site.
       MODIFIED_DATE                 date                   This value is set via an insert/update
                                                            trigger on the table.



                                                                                                    Page: 12
DATA_SOURCE_TO_INDICATOR

       Intersection table that optionally links one or more data sources to an indicator. Both the
       DATA_SOURCE_NAME and INDICATOR_NAME have FK constraints to their respective tables.

       Column Name                    Type          Not Null Description/Notes
       DATA_SOURCE_NAME               varchar2(255)   x      Foreign key used to reference the data
                                                             source record.
       INDICATOR_NAME                 varchar2(255)   x      Foreign key used to reference the indicator
                                                             record.
       SORT_ORDER                                            Optional value that is used when ordering
                                                             the data source records for reports.

DATA_SOURCE_TO_INDICATOR_VIEW

       Intersection table that links a data source to an indicator view.

       Column Name         Type          Not Null Description/Notes
       DATA_SOURCE_NAME    varchar2(255)   x      Foreign key used to reference the data
                                                  source record.
       INDICATOR_VIEW_NAME varchar2(255)   x      Foreign key used to reference the indicator
                                                  view record.
       SORT_ORDER                                 Optional value that is used when ordering
                                                  the data source records for reports.

HEALTHY_PEOPLE_OBJECTIVE

       Table that contains all the valid healthy people objectives. The HEALTHY_PEOPLE_OBJ_BIU
       _TRIG trigger is a before insert and update trigger used to set a date timestamp for the record
       when modified (if the modified date is not specified). The time that is used comes from the Oracle
       server.


       Column Name                    Type           Not Null Description/Notes
       NAME                           varchar2(255)    x      Unique primary key used to reference the
                                                              record.      This value is typically a
                                                              combination of the focus area and
                                                              objective number.
       TITLE                          varchar2(100)    x      Title that is displayed to the admin user
                                                              when selecting the record or that can be
                                                              displayed on a report.
       FOCUS_AREA                     number           x      Number of the focus area (1-28).
       OBJECTIVE_NUMBER               varchar2 (50)    x      Objective number (was made character so
                                                              that it would accommodate the ‘1.2.33c‘
                                                              type of objective number values).
       DESCRIPTION                    varchar2(4000) x        Textual description – not the internal
                                                              description. This could have been named
                                                              TEXT to be consistent but was named
                                                              DESCRIPTION because that’s what the
                                                              users wanted it named.
       US_TARGET                      varchar2(4000) x        Textual description of the US Target for
                                                              this HP Objective.
       STATE_TARGET                   varchar2(4000)          Textual description of the State Target for
                                                              this HP Objective
       SORT_ORDER                     number                  Optional value that is used to control the
                                                              ordering of the records on the admin site.

                                                                                                Page: 13
       ACTIVE_FLAG                    varchar2(1)                Field that controls if this record is displayed
                                                                 on the admin site.
       MODIFIED_DATE                  date                       This value is set via an insert/update
                                                                 trigger on the table.

INDICATOR

       Master table that contains all the main indicator textual data.

       Column Name          Type           Not Null Description/Notes
       NAME                 varchar2(255)    x      Unique primary key used to reference the
                                                    record.
       DESCRIPTION          varchar2(255)           Optional internal description that can be
                                                    used to describe/define the record.
       TITLE                varchar2(100)    x      Title that is displayed to the admin user
                                                    when selecting the record or that can be
                                                    displayed on a report.
       DEFAULT_INDICATOR_VIEW_NAME
                            varchar2 (255)          Default view that is used by the public site
                                                    when an indicator is first selected from the
                                                    main index list. This column is a Non
                                                    optional FK reference to the INDICATOR_
                                                    VIEW table. The reason that this column
                                                    is not null is because the indicator record is
                                                    created first, before a view record and thus
                                                    it can’t be populated with referential
                                                    integrity. A FK does exist for this value.
       DEFINITION           varchar2(4000) x        Text field that contains the definition. This
                                                    field is displayed on the reports.
       NUMERATOR            varchar2(255)    x      Text field that contains the definition of the
                                                    numerator.
       DENOMINATOR          varchar2(255)    x      Text field that contains the definition of the
                                                    denominator.
       DATA_NOTE            varchar2(1000) x        Text field that contains any special data
                                                    notes about the indicator. This field is
                                                    displayed on the reports.
       DATA_ISSUES          varchar2(1000) x        Text field that contains any special data
                                                    issues about the indicator. This field is
                                                    displayed on the reports.
       DATA_SOURCE_DATE     date             x      Date field that contains the date that is
                                                    displayed on the reports showing when
                                                    this data source was last used/referenced.
       WHY_IMPORTANT        varchar2(4000) x        Optional text field that defines why this
                                                    indicator is important.        This field is
                                                    displayed on the reports.
       HEALTHY_PEOPLE_OBJECTIVE_NAME
                            varchar2 (255)          Optional link to an HP objective. This
                                                    column contains a FK reference to:
                                                    HEALTHY_PEOPLE_OBJECTIVE.NAME.
       OTHER_OBJECTIVE_DESCRIPTION
                            varchar2(4000)          Optional text field that defines any other
                                                    objectives that might be of interest for this
                                                    indicator. This field is displayed on the
                                                    reports.
       HOW_DOING            varchar2(4000)          Optional text field that describes how are
                                                    we doing for this indicator. This field is
                                                    displayed on the reports.

                                                                                                      Page: 14
       STATE_VS_US                    varchar2(4000)             Optional text field that describes how the
                                                                 state is doing v.s. the US for this indicator.
                                                                 This field is displayed on the reports.
       WHAT_DOING                     varchar2(4000)             Optional text field that describes what is
                                                                 being done for this indicator. This field is
                                                                 displayed on the reports.
       OTHER_PROGRAM_INFORMATION
                          varchar2(4000)                         Optional text field that defines any other
                                                                 program information that might be of
                                                                 interest for this indicator. This field is
                                                                 displayed on the reports.
       MORE_RESOURCES_TITLE varchar2(30)                         Optional text field that is used as a short,
                                                                 title that is displayed on the public site
                                                                 report’s left navigation menu.
       SERVICES_AVAILABLE_TO_PUBLIC
                            varchar2(4000)                       Optional text field that defines any service
                                                                 type information that might be of interest
                                                                 for this indicator. This field is displayed on
                                                                 the reports.
       RESOURCES_REFERENCES_LINKS
                          varchar2(4000)                         Optional text field that defines any other
                                                                 resources, references or web page links
                                                                 that might be of interest for this indicator.
                                                                 This field is displayed on the reports.
       OWNER_LDAP_ID                  varchar2(255)      x       Contains the LDAP context ID string of the
                                                                 individual that is the owner of this indicator.
                                                                 This string is typically structured like:
                                                                 “novell_ndis_login_name.sub_organization
                                                                 _id.organization_id.world_id”. This controls
                                                                 what certain privileges in the admin site
                                                                 application. If the logged in user’s context
                                                                 id string matches that stored in the table
                                                                 then that user is allowed to maintain the
                                                                 editor list for that indicator.
       STATUS_CODE                    varchar2(1)                Status code for this indicator. This record
                                                                 is a FK to STATUS.CODE. If this value is
                                                                 omitted on record creation, then an after
                                                                 trigger automatically sets it to “O” which is
                                                                 open.
       STATUS_DATE                    date                       This value is set via an insert/update
                                                                 trigger on the table’s STATUS_CODE
                                                                 column if a status date value is not
                                                                 specified.
       NOTE                           varchar2(4000)             Optional field that contains any internal
                                                                 notes that an editor might want to attach to
                                                                 the record.
       SORT_ORDER                     number                     Optional value that is used to control the
                                                                 ordering of the records on the admin site
                                                                 and on the public web index list.
       ACTIVE_FLAG                    varchar2(1)                Field that controls if this record is displayed
                                                                 in validation lists on the admin site.
       MODIFIED_DATE                  date                       This value is set via an insert/update
                                                                 trigger on the table.

INDICATOR_VIEW

       Detail table that contains all the graphical views associated with the main indicator record.

                                                                                                       Page: 15
       Column Name         Type           Not Null Description/Notes
       NAME                varchar2(255)    x      Unique primary key used to reference the
                                                   record.
       INDICATOR_NAME      varchar2(255)    x      Foreign key used to reference the indicator
                                                   record.
       REPORT_NAME         varchar2(255)    x      Foreign key used to reference the
                                                   associated report record.
       TITLE               varchar2(255)    x      Title that is displayed to the admin user
                                                   when selecting the record or that can be
                                                   displayed on a report.
       SUB_TITLE           varchar2(255)    x      Title that is displayed to the admin user
                                                   when selecting the record or that can be
                                                   displayed on a report.
       DATA_NOTE           varchar2(1000)          Text field that contains any special data
                                                   notes about the indicator. This field is
                                                   displayed on the reports.
       DESCRIPTION         varchar2(255)           Optional internal description that can be
                                                   used to describe/define the record.
       DATASET_CATEGORY_NAME
                           varchar2(255)    x      Foreign key used to reference the
                                                   associated dataset category record.
       DATASET_SERIES_NAME varchar2(255)    x      Foreign key used to reference the
                                                   associated dataset series record.
       DATA_ISSUES         varchar2(1000) x        Text field that contains any special data
                                                   issues about the indicator. This field is
                                                   displayed on the reports.
       DATA_SOURCE_DATE    date             x      Date field that contains the date that is
                                                   displayed on the reports showing when
                                                   this data source was last used/referenced.
       NOTE                varchar2(4000)          Optional field that contains any internal
                                                   notes that an editor might want to attach to
                                                   the record.
       SORT_ORDER          number                  Optional value that is used to control the
                                                   ordering of the records on the admin site
                                                   and on the public web index list.
       ACTIVE_FLAG         varchar2(1)             Field that controls if this record is displayed
                                                   in validation lists on the admin site.
       MODIFIED_DATE       date                    This value is set via an insert/update
                                                   trigger on the table.

INDICATOR_VIEW_VALUE

       Detail table that contains all the graphical view’s values. Need to discuss a not firm tie to the DSS
       and DSC records – only to the DSS.V tables. Also discuss the triggers and active flags being turn
       on and off automatically. Discuss the matrix of series and category and values.

       Column Name          Type          Not Null Description/Notes
       INDICATOR_VIEW_NAME varchar2(255)    x      Foreign key used to reference the master
                                                   associated indicator view record.
       DATASET_CATEGORY_VALUE
                            varchar2(255)   x      Partial foreign key used to reference the
                                                   associated dataset category value record.
       DATASET_SERIES_VALUE varchar2(255)   x      Partial foreign key used to reference the
                                                   associated dataset series value record.
       VALUE                number          x      Value that is plotted/displayed in the graph.
       LOWER_CONFIDENCE_VALUE
                            number                 Value of the lower confidence interval.
                                                   Note that the application does NOT
                                                                                                   Page: 16
                                                                   calculate these values. It was beyond the
                                                                   scope of the project. Also, these values
                                                                   are not yet displayed on any of the reports
                                                                   due to the fact that Actuate does not
                                                                   readily support this functionality. This
                                                                   column/value is present simply for a
                                                                   placeholder to centrally store the value for
                                                                   future needs.
       UPPER_CONFIDENCE_VALUE
                           number                                  Value of the upper confidence interval.
       LABEL               varchar2(100)                           Optional text value that can be used as a
                                                                   special label for this value. This field is
                                                                   currently NOT displayed on the reports.
       NOTE                            varchar2(4000)              Optional field that contains any internal
                                                                   notes that an editor might want to attach to
                                                                   the record.
       ACTIVE_FLAG                     varchar2(1)                 Field that controls if this record is displayed
                                                                   in validation lists on the admin site.

RELATION

       Table that contains all the valid relations.

       Column Name                     Type          Not Null Description/Notes
       NAME                            varchar2(100)   x      Unique primary key used to reference the
                                                              record.
       TITLE                           varchar2(255)   x      Title that is displayed to the admin user
                                                              when selecting the record or that can be
                                                              displayed on a report.
       SORT_ORDER                      number                 Optional value that is used to control the
                                                              ordering of the records on the admin site.
       ACTIVE_FLAG                     varchar2(1)            Field that controls if this record is displayed
                                                              on the admin site.
       MODIFIED_DATE                   date                   This value is set via an insert/update
                                                              trigger on the table.

INDICATOR_RELATION

       Table that contains the actual text for a given indicator relation.

       Column Name                     Type          Not Null Description/Notes
       INDICATOR_NAME                  varchar2(255)   x      Foreign key used to reference the indicator
                                                              record.
       RELATION_NAME                   varchar2(100)   x      Foreign key used to reference the relation
                                                              record.
       TEXT                            varchar2(4000) x       Actual text for the specified indicator’s
                                                              relation. This is the information that is
                                                              presented to the user.

INDICATOR_TO_IND_RELATION

       Intersection table that links an indicator to one or more related indicators. The RELATION_NAME,
       INDICATOR_NAME, and RELATED_INDICATOR_NAME all have FK constraints to their
       respective tables.

       Column Name                     Type             Not Null Description/Notes

                                                                                                        Page: 17
         INDICATOR_NAME                varchar2(255)      x      Foreign key used to reference the indicator
                                                                 record.
         RELATION_NAME                 varchar2(100)      x      Foreign key used to reference the relation
                                                                 record.
         RELATED_INDICATOR_NAME
                              varchar2(255)               x      Foreign key used to reference the related
                                                                 indicator record.

REPORT

         Table that, currently, contains all the Actuate reports used on the public site that displays the
         graphical view. This table is also used for the Admin application indicator graph’s report drop down
         list. NOTE: In the future, the Actuate reports could be replaced with any other web report server
         (as long as it is URL based).

         Column Name                   Type          Not Null Description/Notes
         NAME                          varchar2(255)   x      Unique primary key used to reference the
                                                              record.
         TITLE                         varchar2(255)   x      Title that is displayed to the admin user
                                                              when selecting the report to be associated
                                                              with the selected indicator view.
         TYPE                          varchar2(1)     x      Code that defines the type of type report.
                                                              “W” = web report, “P” = printed report.
         URL                           varchar2(255)   x      The URL that is needed to run the report.
         DESCRIPTION                   varchar2(255)          Optional internal description that can be
                                                              used to describe/define the record.
         SORT_ORDER                    number                 Optional value that is used to control the
                                                              ordering of the records on the admin site.
         ACTIVE_FLAG                   varchar2(1)            Field that controls if this record is displayed
                                                              on the admin site.
         MODIFIED_DATE                 date                   This value is set via an insert/update
                                                              trigger on the table.

REPORT_SET

         Table that contains the master record for all associated REPORT_SET_ITEM records. A report
         set is a report that is comprised/contains a series of sub reports. When one of the various printed
         IBISPH printed/hardcopy reports is needed, an Actuate driver report is ran. This driver report is
         passed which report set record to use. The driver report then queries the associated report set
         item records and runs the specified Actuate report with the specified indicator view.

         Column Name                   Type          Not Null Description/Notes
         NAME                          varchar2(100)   x      Unique primary key used to reference the
                                                              record.
         TITLE                         varchar2(255)   x      Currently, this field is not used in any part
                                                              of the Admin application. It is provided for
                                                              the future.
         REPORT_NAME                   varchar2(255)   x      Foreign key used to reference the
                                                              associated report to be used for the set.
         DESCRIPTION                   varchar2(255)          Optional internal description that can be
                                                              used to describe/define the record.
         SORT_ORDER                    number                 Optional value that is used to control the
                                                              ordering of the records on the admin site
                                                              (future - not yet developed).




                                                                                                    Page: 18
REPORT_SET_ITEM

       Detail definition record associated with the REPORT_SET.

       Column Name         Type          Not Null Description/Notes
       REPORT_SET_NAME     varchar2(100)   x      Unique primary key used to reference the
                                                  record.
       TITLE               varchar2(255)   x      Title that could be displayed (future).
       INDICATOR_VIEW_NAME varchar2(255)   x      Foreign key used to reference the
                                                  associated indicator view data to be used
                                                  with the specified report.
       GRAPH_TYPE          varchar2(255)   x      String code that is used by the Actuate
                                                  report to decide which graph is to be used
                                                  to display the associated indicator view.
       SORT_ORDER          number                 Optional value that is used to control the
                                                  ordering of the records.

WEB_INDEX_TYPE

       Oracle object type definition. This is essentially the table description with columns and special
       functions called methods that allow data to be retrieved from other sources while acting almost list
       a column. The reason that this was made into an object table is that to do the hierarchical query
       you can’t do more than one outer join which means that the needed information would not be
       available in the query without having to write more Actuate Basic code.

       Column Name                   Type          Not Null Description/Notes
       NAME                          varchar2(255)   x      Unique primary key used to reference the
                                                            record.
       PARENT_NAME                   varchar2(255)   x      Foreign key back to this tables NAME
                                                            column. This is the hierarchal relationship.
       TITLE                         varchar2(255)   x      Title that is displayed to the admin user
                                                            when selecting the report to be associated
                                                            with the selected indicator view.
       INDICATOR_NAME                varchar2(255)          Optional value that when specified
                                                            designates that this is a leaf node in the
                                                            hierarchy and assigns this web index
                                                            selection to the specified indicator.
       SORT_ORDER                    number                 Optional value that is used to control the
                                                            ordering of the records on the admin site.

       Method Name                   Type            Description/Notes
       INDICATOR_TITLE               varchar2        Returns the associated title of the indicator. This is
                                                     the same as doing a join with the INDICATOR table
                                                     keying on the INDICATOR_NAME column.
       INDICATOR_VIEW_NAME varchar2                  Returns the default view name that is associated
                                                     with the indicator. This is the same as doing a join
                                                     with the INDICATOR table keying on the
                                                     INDICATOR_NAME column and returning the
                                                     DEFAULT_INDICATOR_VIEW_NAME column.
       REPORT_URL                    varchar2        Returns the report URL that is associated with the
                                                     default indicator view. This is the same as doing a
                                                     join with the REPORT table keying on the
                                                     REPORT_NAME           that    is   stored     in  the
                                                     INDICATOR_VIEW table that is associated with the
                                                     INDICATOR_NAME column.



                                                                                                  Page: 19
3.4 Table and Column Descriptions for the Admin Schema Only

       This section contains the table and column descriptions that are needed for the ADMIN sites only.

INDICATOR_EDITOR

       Table that tracks what user(s) can edit what indicator(s).

       Column Name                    Type          Not Null Description/Notes
       INDICATOR_NAME                 varchar2(255)   x      Foreign key that references the indicator.
       USER_LDAP_ID                   varchar2(255)   x      User’s Lightweight Directory Access
                                                             Protocol (LDAP) context string. This is
                                                             typically of the form NDIS_UserName.
                                                             Org.Dept.UT or CN=UserName,OU=Org,
                                                             OU=Dept,O=UT.

NAVIGATION_MENU_ITEM

       Table that contains the information needed to dynamically build the Admin Site’s JSP navigation
       menu.

       Column Name        Type          Not Null Description/Notes
       GROUP_NAME         varchar2(255)          Record parent key/flag. This entry is used
                                                 to “group” certain navigation menu items
                                                 together. If it is null, then the item is
                                                 included with all menus. When a request
                                                 is received by the RequestBroker servlet,
                                                 the navigation menu is build based off of
                                                 the group name associated with the
                                                 request received.
       TEXT_TITLE         varchar2(30)           Optional text field that is used to store text
                                                 navigation menu items. Currently not used
                                                 for the Admin nav.
       IMAGE_URL          varchar2(255)          Optional text field that is used to store the
                                                 URL address for an image/graphical
                                                 navigation menu item.
       MOUSE_OVER_IMAGE_URL
                          varchar2(255)          Optional text field that is used to store the
                                                 URL address for an image/graphical
                                                 navigation menu item that is displayed
                                                 when the user moves the mouse over the
                                                 image’s area.
       SELECTED_IMAGE_URL varchar2(255)          Optional text field that is used to store the
                                                 URL address for an image/graphical
                                                 navigation menu item that is displayed
                                                 when the user is “on” that selection.
       ALT_MESSAGE        varchar2(255)          Optional text field that is used to store the
                                                 HTML mouse over alt text /missing image
                                                 text for the selection.
       PRIVILEGE_CODE     varchar2(1)            Optional field is a FK to the PRIVILEGE.
                                                 CODE table.column. If null then the item
                                                 is open to all. If specified, then the user
                                                 must have that privilege to have the item
                                                 included in their menu.
       REQUEST_NAME       varchar2(255)          Optional text field that contains the desired
                                                 request to be made to the RequestBroker
                                                                                                 Page: 20
                                                                 when the item is selected by the user.
                                                                 This is a FK to REQUEST.NAME.
        LINK_URL                      varchar2(255)              Optional text field that is used to store the
                                                                 a URL that is linked to when the user
                                                                 selects the item.
        SORT_ORDER                    number                     Optional value that is used to control the
                                                                 ordering of the navigation items.
        ACTIVE_FLAG                   varchar2(1)                Field that controls if this navigation item is
                                                                 displayed.

PRIVILEGE

        Table that contains the valid privilege codes needed for the admin site’s application. The
        application does not currently provide any interaction with this table. The table is provided mainly
        as a way to document/describe the code.

        Column Name                   Type          Not Null Description/Notes
        CODE                          varchar2(1)     x      This is the actual privilege code. Code
                                                             was used instead of a numeric privilege
                                                             level. This way, numerous codes can be
                                                             assigned independently of the level. The
                                                             codes can be put together to form a user’s
                                                             privilege ring (string of codes).
        DESCRIPTION                   varchar2(255)   x      Description of the code.
        GRANTABLE_FLAG                varchar2(1)            Optional field that is not used but was
                                                             included in case a privilege screen was
                                                             created.

REQUEST


        Table that contains all the valid requests that can be made within the Admin application. A request
        is associated with a special java class file that knows/is programmed to handle the request. These
        requests can be 1:1 (e.g. 1 controller per request) or can be n:1 (e.g. 1 controller for n requests).
        These requests are submitted to the RequestBroker servlet via the HTML form that is submitted
        (the form contains a hidden field named: request). The servlet calls the associated controller
        based on the request received. The controller is then responsible to handle the request. There are
        two basic types of requests. The first is a SAVE request. This type of request reads the submitted
        HTML form and updates database table(s). The other type of request is for viewing data. When
        this type of request is made, the controller sets up the data bean(s) and then calls the associated
        JSP to display the requested data.

        Column Name         Type          Not Null Description/Notes
        NAME                varchar2(255)   x      Unique primary key used to reference the
                                                   record.
        DESCRIPTION         varchar2(255)          Optional internal description that can be
                                                   used to describe/define the record.
        PRIVILEGE_CODE      varchar2(1)            Optional field that is a FK to the
                                                   PRIVILEGE.CODE table.column. If null
                                                   then the request is available to all users. If
                                                   specified, then the user must have that
                                                   privilege to make the given request.
        JAVA_REQUEST_CONTROLLER_NAME
                            varchar2(255)          Optional field that contains the fully
                                                   qualified java class filename that is to
                                                   handle the associated request. Note: this
                                                   is only used when special processing is

                                                                                                      Page: 21
                                                                   needed. If no beans need to be setup etc.
                                                                   then a controller is not needed – simply
                                                                   use the FORWARD_TO_URL.
         FORWARD_TO_URL                   varchar2(255)            Optional field that contains the fully
                                                                   qualified URL that will be called/forwarded
                                                                   to when the request is received. Most
                                                                   response type requests should have this
                                                                   field set to the associated JSP URL.

SITE_LOG

         Table that tracks what user accessed the Admin application/system when.

         Column Name                      Type          Not Null Description/Notes
         USER_LDAP_ID                     varchar2(255)   x      User’s Lightweight Directory Access
                                                                 Protocol (LDAP) context string.
         LOGON_DATE                       date            x      Date and time stamp that is automatically
                                                                 set to the time of the Oracle RDBMS
                                                                 server. This value is set via a trigger on
                                                                 the table.
         USER_IP_ADDRESS                  varchar2(50)           TCP/IP address of the user’s machine.

STATUS

         Table that contains the valid privilege codes needed for the admin site’s application. The
         application does not currently provide any interaction with this table. The table is provided mainly
         as a way to document/describe the code.

         Column Name                      Type            Not Null Description/Notes
         CODE                             varchar2(1)       x      This is the actual status code.
         DESCRIPTION                      varchar2(50)      x      Description of the code.
         PRIVILEGE_CODE                   varchar2(1)              Optional field that is a FK to the
                                                                   PRIVILEGE.CODE table.column. If null
                                                                   then the status can be set by all users. If
                                                                   specified, then the user must have that
                                                                   privilege in their privilege ring to set the
                                                                   status.
         ASSOCIATED_STATUS_CODES
                             varchar(255)                          String that contains a list of all the
                                                                   associated status codes. These are the
                                                                   codes that are displayed for selection
                                                                   when an indicator is set to the code.
         SORT_ORDER                       number                   Optional value that is used to control the
                                                                   ordering of the navigation items.
         ACTIVE_FLAG                      varchar2(1)              Field that controls if this navigation item is
                                                                   displayed.

TITLE

         Table that contains all the valid titles.

         Column Name                      Type          Not Null Description/Notes
         NAME                             varchar2(255)   x      Unique primary key used to reference the
                                                                 record.
         TEXT                             varchar2(255)   x      Text of the title that is displayed to the
                                                                 admin user when selecting the Y Title to
                                                                 be used with the selected indicator view.
                                                                                                        Page: 22
       TYPE                         varchar2(1)               Code that defines the type of type title.
                                                              Currently, this is not used but could be if
                                                              other types of title were desired to be
                                                              stored.
       SORT_ORDER                   number                    Optional value that is used to control the
                                                              ordering of the title records.
       ACTIVE_FLAG                  varchar2(1)               Field that controls if this title item is
                                                              displayed to the user.
       MODIFIED_DATE                date                      This value is set via an insert/update
                                                              trigger on the table.



3.5 View Descriptions

       Currently, the IBIS-PH system does not have any views.


3.6 Sequence Descriptions

       The IBIS-PH system uses character names as primary keys. It does not have any numerical IDs
       nor does it have any other number sequencing.



3.7 Index Descriptions

       Currently, no special indexes exist for the IBISPH schema. Oracle automatically indexes all
       primary keys on the table and almost all IBISPH tables have a primary key and are access via their
       primary key.



3.8 Synonyms

       All of the IBIS-PH “USER” accounts have synonyms to the “OWNER” account tables. These are
       used by the IBISPH PUBLIC/ADMIN USER account to facilitate a read only view of the sites data.
       These synonyms are maintained by the associated SYN_C_xxxxx.SQL script files.




                                                                                                Page: 23
3.9 Database ERD

                      The IBIS-PH Database Entity Relationship Diagram (ERD) that follows, shows the “physical” data
                      relationships (relationships are the defined database constraints). The diagram does not show
                      logical or the usage relationships between the data.




                                                    STATUS                                INDICATOR
                                                                                                                         INDICATOR_RELATION
                                     PK    CODE                           PK    NAME
                                                                                                                        FK1,U1 INDICATOR_NAME
                                           DESCRIPTION                          DESCRIPTION                             FK2,U1 RELATION_NAME
                                           PRIVILEGE_CODE                 FK1   DEFAULT_INDICATOR_VIEW_NAME
                                           ASSOCIATED_STATUS_CODES              TITLE                                             TEXT
                                           SORT_ORDER                           DEFINITION
                                           ACTIVE_FLAG                          NUMERATOR                                                                          RELATION
                                                                                DENOMINATOR
                                                                                DATA_NOTE                            INDICATOR_TO_IND_RELATION               PK    NAME
                                                                                DATA_ISSUES
                                                                                DATA_SOURCE_DATE                FK1,U1 INDICATOR_NAME                              TITLE
                                      WEB_INDEX : WEB_INDEX_TYPE                WHY_IMPORTANT                   FK3,U1 RELATION_NAME                               SORT_ORDER
                                                                          FK2   HEALTHY_PEOPLE_OBJECTIVE_NAME   FK2,U1 RELATED_INDICATOR_NAME                      ACTIVE_FLAG
                                      PK    NAME                                                                                                                   MODIFIED_DATE
                                                                                OTHER_OBJECTIVE_DESCRIPTION
                                      FK2   PARENT_NAME                         HOW_DOING
                                            TITLE                               STATE_VS_US
                                      FK1   INDICATOR_NAME                      WHAT_DOING
                                            SORT_ORDER                          OTHER_PROGRAM_INFORMATION
        HEALTHY_PEOPLE_OBJECTIVE                                                                                             INDICATOR_EDITOR
                                                                                MORE_RESOURCES_TITLE
                                                                                SERVICES_AVAILABLE_TO_PUBLIC           FK1,U1 INDICATOR_NAME
        PK   NAME
                                                                                RESOURCES_REFERENCES_LINKS             U1     USER_LDAP_ID
                                                                                OWNER_LDAP_ID
             TITLE
                                                                          FK3   STATUS_CODE
             FOCUS_AREA
                                                                                STATUS_DATE
             OBJECTIVE_NUMBER
                                                                                NOTE                                                                          DATA_SOURCE
             DESCRIPTION
                                                                                SORT_ORDER
             US_TARGET                                                                                                                                   PK    NAME
                                                                                ACTIVE_FLAG                           DATA_SOURCE_TO_INDICATOR
             STATE_TARGET
                                                                                MODIFIED_DATE
             SORT_ORDER                                                                                               PK,FK1 DATA_SOURCE_NAME                  TEXT
             ACTIVE_FLAG                                                                                              PK,FK2 INDICATOR_NAME                    SORT_ORDER
             MODIFIED_DATE                                                                                                                                     ACTIVE_FLAG
                                                                                                                                SORT_ORDER                     MODIFIED_DATE



         DATASET_CATEGORY_VALUE               DATASET_CATEGORY                                                        DATA_SOURCE_TO_INDICATOR_VIEW
                                                                                       INDICATOR_VIEW
      FK1,U1 DATASET_CATEGORY_NAME            PK    NAME                                                              PK,FK1 DATA_SOURCE_NAME
      U1     VALUE                                                          PK,FK4 NAME                               PK,FK2 INDICATOR_VIEW_NAME
                                                    TITLE
             TITLE                                  LABEL                   FK3        INDICATOR_NAME                           SORT_ORDER
             DESCRIPTION                            DESCRIPTION                        TITLE
             SORT_ORDER                             START_YEAR                         SUB_TITLE
             ACTIVE_FLAG                            END_YEAR                           Y_TITLE
                                                    SORT_ORDER                         DATA_NOTE
                                                    ACTIVE_FLAG                        DESCRIPTION                              REPORT
                                                    MODIFIED_DATE           FK2        DATASET_SERIES_NAME
                                                                            FK1        DATASET_CATEGORY_NAME            PK    NAME
                                                                                                                                                              REPORT_SET
                                                                                       SORT_ORDER
                                                                                       NOTE                                   TITLE                     PK        NAME
          DATASET_SERIES_VALUE                    DATASET_SERIES                       ACTIVE_FLAG                            TYPE
                                                                                       MODIFIED_DATE                          URL                                 TITLE
       FK1,U1 DATASET_SERIES_NAME             PK    NAME                                                                      DESCRIPTION               FK1       REPORT_NAME
       U1     VALUE                                                                                                           SORT_ORDER                          DESCRIPTION
                                                    TITLE                                                                     ACTIVE_FLAG                         SORT_ORDER
                  TITLE                             DESCRIPTION                                                               MODIFIED_DATE
                  DESCRIPTION                       SORT_ORDER
                  SORT_ORDER                        ACTIVE_FLAG
                  ACTIVE_FLAG                       MODIFIED_DATE

                                                                                                                                                         REPORT_SET_ITEM
                                                                                  INDICATOR_VIEW_VALUE                                                FK1    NAME
                                                                           FK1,U1 INDICATOR_VIEW_NAME
                                                                                                                                                      FK2    REPORT_SET_NAME
                                                                           U1     DATASET_SERIES_VALUE
                                                                                                                                                             TITLE
                                                                           U1     DATASET_CATEGORY_VALUE
                                                                                                                                                             GRAPH_TYPE
                                                                                                                                                             SORT_ORDER
                                                                                    VALUE
                                                                                    LOWER_CONFIDENCE_VALUE
                                                                                    UPPER_CONFIDENCE_VALUE
                                                                                    LABEL                               TITLE
                   REQUEST                        NAVIGATION_MENU_ITEM              NOTE                        PK    NAME
                                                                                    ACTIVE_FLAG                                                  SURVEY_RESPONSE
PK    NAME
                                                                                                                      TYPE
      DESCRIPTION                                  GROUP_NAME                                                         TEXT
FK1   PRIVILEGE_CODE                               TEXT_TITLE                                                         SORT_ORDER                  COURTESY_TITLE
      JAVA_REQUEST_CONTROLLER_NAME                 IMAGE_URL                                                          ACTIVE_FLAG                 FIRST_NAME
      FORWARD_TO_URL                               MOUSE_OVER_IMAGE_URL                                               MODIFIED_DATE               MI
                                                   SELECTED_IMAGE_URL                                                                             LAST_NAME
                                                   ALT_MESSAGE                                                                                    TITLE
                                            FK1    PRIVILEGE_CODE                                                                                 ORG_TYPE
                    PRIVILEGE               FK2    REQUEST_NAME                                                                                   ORG_NAME
                                                                                                                       SITE_LOG
                                                   LINK_URL                                                                                       ROLE
             PK    CODE                            SORT_ORDER                                                                                     PHONE
                                                   ACTIVE_FLAG                                                                                    EMAIL_ADDRESS
                   DESCRIPTION                                                                                   USER_LDAP_ID                     QUESTION
                   GRANTABLE_FLAG                                                                                LOGON_DATE                       NOTE
                                                                                                                 USER_IP_ADDRESS                  ENTRY_DATE




                                                                                                                                                                    Page: 24
        Section


         4
Database Triggers, Packages, Procedures,
Functions, Methods, and Script Files
Overview, PL/SQL Naming Convention, Package Description, Trigger
Descriptions, Procedure Descriptions, Function Descriptions, Method
Description, Script File Descriptions
 4.1 Overview

         This section discusses the backend, server side, Oracle PL*SQL code written for IBIS-PH.
         Triggers are used for logging date/time stamps of changed records and for the auto
         activation/deactivation of records. Methods are used for the WEB_INDEX table. These methods
         allow for access to related data which would otherwise be unavailable due to Oracle’s one table
         outer join limitation.


 4.2 Naming Conventions

PL/SQL Code:

         PL/SQL code follows the standard IBIS-PH Oracle naming conventions with two
         additions/exceptions. Local variables are MixedCase, with the First Letter of each significant word
         being capitalized and do not contain the underscore “_” character between words.

         Example: A local variable in the DATASET_SERIES_VALUE_AIU_TRIG trigger that is used to flag
         when a parent record is being updated is named: ParentUpdateFlag. This allows for the local
         variables to be picked out quickly and easily, and it also makes it unique for use in bind variables.

Trigger Names:

         The first part of the trigger name is the table name that the trigger is associated with. Immediately
         following the table name is an underscore followed by an acronym describing the type of trigger.
         The first letter is either a “B” for before or an “A” for after. The next letters tell which operation the
         trigger will fire on. These letters are: “I” for insert, “U” update, and “D” for delete. A “_TRIG” suffix
         is then added to the end of the name to help identify it as a trigger.

         Example: the INDICATOR_BIU_TRIG trigger is a trigger that fires Before an Insert or an Update
         on the INDICATOR table.




                                                                                                          Page: 25
4.3 Package Description

       IBISPH_PKG is the only package needed for the IBIS-PH system. The PACK_C.SQL script file
       contains the IBIS-PH_PKG package. This package’s only utility is to serve as a way to keep
       variables used by the triggers (It contains no PL/SQL routines). These variables catch and save
       the item of interest before something happens to it. Then after that thing has happened, a follow
       up trigger is fired (by Oracle) that can do the action - logging of changes etc. This pre and post
       event triggering is needed to avoid mutating triggers.


4.4 Trigger Descriptions

       Database triggers are PL/SQL code that fires on defined database events. Triggers are useful in
       helping to enforce business and data validation rules and logging operations. The IBIS-PH system
       mainly uses them for logging modified dates and activation/deactivation of dataset category, series,
       and indicator view values. The TRIG_C.SQL script file contains the actual trigger creation code as
       well as more detailed descriptions and comments. The IBIS-PH triggers are:

       Trigger Name                               Description

       DATASET_CATEGORY_BIU_TRIG                  Sets MODIFIED_DATE to the system date.

       DATASET_CATEGORY_AU_TRIG                   Deactivates all associated dataset category values.

       DATASET_CAT_VALUE_AIU_TRIG                 Sets parent dataset category’s MODIFIED_DATE and
                                                  deactivates any associated indicator view value.

       DATASET_SERIES_BIU_TRIG                    Sets MODIFIED_DATE to the system date.

       DATASET_SERIES_AU_TRIG                     Deactivates all associated dataset series' values.

       DATASET_SERIES_VALUE_AIU_TRIG Sets parent dataset series’ MODIFIED_DATE and
                                     deactivates any associated indicator view values.

       DATA_SOURCE_BIU_TRIG                       Sets MODIFIED_DATE to the system date.

       HEALTHY_PEOPLE_OBJ_BIU_TRIG                Sets MODIFIED_DATE to the system date.

       INDICATOR_BIU_TRIG                         Sets MODIFIED_DATE to the system date, sets the
                                                  status code to “O”pen (if new) and sets the data source
                                                  date to the system date if nothing specified.

       INDICATOR_BD_TRIG                          Sets any WEB_INDEX records that reference this
                                                  indicator to null.

       INDICATOR_VIEW_BIU_TRIG                    Sets MODIFIED_DATE to the system date.

       INDICATOR_VIEW_BD_TRIG                     Sets the associated indicator’s default view to null (if
                                                  associated).

       INDICATOR_VIEW_VALUE_AIU_TRIG              Sets the associated parent indicator                 view’s
                                                  MODIFIED_DATE to the system date.

       DATA_SOURCE_TO_IND_AIU_TRIG                Sets MODIFIED_DATE to the system date.
                                                                                                  Page: 26
         DATA_SOURCE_TO_IND_VW_AIU_TRIG Sets MODIFIED_DATE to the system date.

         INDICATOR_RELATION_AIU_TRIG               Sets MODIFIED_DATE to the system date.

         IND_TO_IND_RELATION_AIU_TRIG              Sets MODIFIED_DATE to the system date.

         REPORT_BIU_TRIG                           Sets MODIFIED_DATE to the system date.

         SITE_LOG_BI_TRIG                          Sets LOGON_DATE to the system date.

         SURVEY_RESPONSE_BIU_TRIG                  Sets ENTRY_DATE to the system date.

         TITLE_BIU_TRIG                            Sets MODIFIED_DATE to the system date.


 4.5 Method Descriptions

         Table methods are PL/SQL code that is associated with a table’s row. These are like special
         functions that look to the user like a column. Below is a list of the methods used in the IBISPH
         system. All of these methods are used for the WEB_INDEX table. The reason that this was made
         into an object table with methods is because Oracle can’t do more than one outer join per query
         which means that the needed information would not be available in the query without having to
         write more Actuate Basic code and/or split the query into several queries.

         Method Name                  Type            Description/Notes
         INDICATOR_TITLE              varchar2        Returns the associated title of the indicator. This is
                                                      INDICATOR_TITLE               varchar2          Returns
                                                      the associated title of the indicator. This is the same
                                                      as doing a join with the INDICATOR table keying on
                                                      the INDICATOR_NAME column.
         INDICATOR_VIEW_NAME varchar2                 Returns the default view name that is associated
                                                      with the indicator. This is the same as doing a join
                                                      with the INDICATOR table keying on the
                                                      INDICATOR_NAME column and returning the
                                                      DEFAULT_INDICATOR_VIEW_NAME column.
         REPORT_URL                   varchar2        Returns the report URL that is associated with the
                                                      default indicator view. This is the same as doing a
                                                      join with the REPORT table keying on the
                                                      REPORT_NAME            that     is   stored     in   the
                                                      INDICATOR_VIEW table that is associated with the
                                                      INDICATOR_NAME column.


 4.6 SQL Script File Descriptions

         Scripts were used to create the database objects, creating and maintaining triggers, constraints,
         account privileges, and synonyms. Special “one time only” scripts were also developed to setup
         the IBIS-PH table spaces.

Script Naming Convention:

         The common scripts used to create and drop the IBIS-PH database objects are named as:
         OBJECTTYPE_ACTIONTYPE[_OWNER].SQL where:

            OBJECTTYPE is an abbreviated 3-4 characters that represents the type of the object.

                                                                                                     Page: 27
             ACTIONTYPE is a one letter suffix code describing the type of action that the script will do.
              Create scripts use “_C”, while the drop scripts use “_D”.

             OWNER is an optional suffix that show which account this script should be used for (ADMIN or
              PUBLIC).

         Examples: the TAB_C.SQL script will Create the TABle objects. The SYN_D_ADMIN.SQL script
         will Drop all the SYNonyms for the ADMIN account.

Common Useful Scripts:

         Script Name              Description
         CON_x.SQL                Contains the foreign key and check constraint scripts.
         IBISPH_C.SQL             Contains accounts and table space creations.
         PACK_C.SQL               Contains the create package script that creates/holds variables.
         PRIV_x_yyyy.SQL          Contains the priv scripts
         SYN_x_yyyyy.SQL          Contains the synonym scripts
         TRIG_x.SQL               Contains the trigger scripts.


             DROP scripts should be used with EXTREME caution. These scripts do not ask for confirmation.


Executing Scripts:

         To execute a script the following steps are needed:

         1) Logon as the table owner (typically HL_IBISPH_ADMIN_OWNER) or an account with
              sufficient privileges.

         2) Execute the run command at the “SQL>” prompt type by entering “run” followed by the script
              filename (including the path). This is of the form:

              SQL>run C:\subdirectory\scrip_name.sql [Enter]
              -or-
              SQL>@C:\subdirectory\scrip_name.sql [Enter]

         Example: To drop all the standard indexes and recreate them the steps would be:

         3) Launch SQL*Plus and log on as the owner of the tables.

         4) Drop the constraints by running the CON_D.SQL file: SQL>@CON_D.SQL [Enter].

         5) Create the constraints by running the CON_C.SQL file: SQL>@CON_C.SQL [Enter].



             The script files contain DDL commands and are permanent - they can not be rolled back (and they don’t need to
             be explicitly committed).




                                                                                                                   Page: 28
       Section


        5
Public Site Static HTML Pages
Overview, Page List and Descriptions, Java Script Descriptions, Images
Used, User Survey, Issues

5.1 Overview

        This section discusses the public site’s static web pages. These static pages include the main,
        opening IBISPH home page, several information overview pages and links to related sites. The
        information could have been presented in Actuate reports but this was decided against due to the
        slow response times, inflexible page design and overall unreliability of the Actuate server.

        The pages all have the same image header as well as a DHTML menu that is located on the left
        hand side of the page. The menus are controlled by java script routines. The first navigational
        menu item on all of the static pages is a link to the main indicator list report. The anchor/link URL
        that is used to access this main index report is stored as a variable in the misc.js Java script file.


5.2 HTML Page List / File Descriptions

        The pages all use the same footer and via a server side include statement. The pages that use the
        server side include are named with the standard .shtml file extension. The static pages used in the
        system are listed below:

        Filename                              Description

        background.shtml                      Background information.

        commun_health.shtml                   Community health information page.

        contents.shtml                        Contents page.

        footer.html                           Standard footer section included in all shtml pages. Includes
                                              the HRSA logo and contact links/information.

        Health_status.shtml                   Health status information page.

        commun_health.shtml                   Community health information page.

        index.shtml                           Main IBISPH home page.

        indicator_def.shtml                   Indicator definition information page.

        local_health.shtml                    Local health information page.

                                                                                                      Page: 29
       commun_health.shtml                   Community health information page.

       organization.shtml                    Organization information page.

       survey.shtml                          End user survey page.    This page submits to the
                                             SubmitSurveyResponse Java servlet which updates the
                                             database table.

       udoh.shtml                            Utah Department of Health overview page.


5.3 Java Script File Descriptions

       List of Java script files which are needed/used in all of the static pages:

       Filename                              Description

       menu.js                               Generic DHTML menu routines that are used to build the
                                             popout submenus.

       misc.js                               Contains the methods to load/build the menus, contains the
                                             URL to the Index Actuate report, and has routines to swap
                                             images upon the mouse over event. As already mentioned,
                                             this file contains the declaration of the indexReportURL
                                             variable which is used for the anchor/link URL in the
                                             navigation menu to access the main Indicator Index Actuate
                                             report.


5.4 Image File Descriptions

       Images files needed for the static pages and the Photoshop design files that were used to create
       the images:

       Filename                              Description

       Public Menus 150x38 blue.psd          Normal, single line left navigation menu images. Blue text,
                                             taupe background, 150 pixels wide by 38 pixels high.

       Public Menus 150x60 blue.psd          Same as above – just for 2 lines.

       Public Menus 150x38 inverted blue.psd Mouse over images for single line left navigation menu
                                          images. Taupe text on a blue background, 150 pixels wide by
                                          38 pixels high.

       Public Menus 150x60 inverted blue.psd Same as above – just for 2 lines.

       Public Menu Bottom.psd                Bottom part of the left hand navigation menu.

       Public Menu Separator 150x20.psd Line separator for the left hand navigation menu.

       Public Static Header.psd              Main header image.

       header.gif                            Main page header.

                                                                                                Page: 30
      HRSALogo.gif                        Reduced HRSA image used in the footer.

      IBISLogo.gif                        Reduced bird image used on all the Actuate reports.

      plus.gif, minus.gif                 Plus and minus images used on the Categorized Indicator
                                          Index Actuate report.

      mn_xxxxxxxxxx.gif                   Normal left navigation image item.

      mn_xxxxxxxxxx_over.gif              Mouse over left navigation image item.

      3lhdmap.gif                         Local health district map.

      btn_reset.gif, btn_save.gif         Reset and save button images for the user survey.

      HRSALogo.gif                        Reduced HRSA image used in the footer.

      IBISLogo.gif                        Reduced bird image used on all the Actuate reports.

      plus.gif, minus.gif                 Plus and minus images used on the Categorized Indicator
                                          Index Actuate report.

      mn_xxxxxxxxxx.gif                   Normal left navigation image item.

      mn_xxxxxxxxxx_over.gif              Mouse over left navigation image item.

      3lhdmap.gif                         Local health district map.

      btn_reset.gif, btn_save.gif         Reset and save button images for the user survey.


5.5 User Survey

      The user survey consists of 2 pieces. The first is a static HTML page that presents the form to be
      filled out. The next is a Java servlet that updates the database table once the form is submitted.
      The SubmitSurveyResponse servlet resides on the ITS iPlanet application server. The action
      statement of the user survey form is hard coded to the ITS iPlanet server servlet.


5.6 Issues

      The static pages, it’s images, and Java script files are stored on and served up from an ITS web
      server that’s dedicated to HTML pages. The Actuate server that serves up the indicator reports is
      a totally different machine as is the Admin Site’s web/application server. ITS has a registered
      name for the public site’s home page (index.shtml). There isn’t a name registered for the Actuate
      report reports and as such the URL for the main Actuate Index report is hard coded as a variable in
      the misc.js Java script file.




                                                                                                Page: 31
       Section


        6
Public Site Actuate Reports
Overview, Naming Convention, Connection, Web Reports, Printed
Reports, Issues

6.1 Overview

       This section discusses the design structure of the IBISPH Actuate Web Reports and Set Reports
       (used for printed/hard copy output). Both types of reports are served from the ITS Actuate
       reporting server in HTML. Both types of reports are generated on the fly from the server using the
       report object executable (ROX) file. The Web Reports are single item reports that link to each
       other dynamically to form an interactive application where the user can view various indicator
       information. The Set Reports are also HTML web reports but differ in that they contain many
       indicator views (hence the name: set of reports) and have no clickable, interactive URL links. The
       Set Reports are the more typical Actuate web report in that they are an end destination item.

       All of the Actuate reports use a mix of property settings and method overrides. Method overrides
       are used mainly for the database queries and when dealing with complex logic that is not easily
       written or maintained by the expression property. Database queries are ALL explicitly defined.
       This is done so that different database username logins can be used. The Actuate Designer, when
       used to build the query, will pull in specific owner account schema meta data. The designer hard
       codes the query for that specific owner into the ROD design file which precludes the report from
       being ran against a different user’s schema of the same design. The IBISPH system contains the
       approved, published data in a PUBLIC owner schema/account and contains the new updated,
       staged data in the ADMIN owner schema/account. The IBISPH users need to view their data in
       the reports before it is published. Currently, ITS only has one Actuate server which means that the
       reports must be ran against both the ADMIN schema and the PUBLIC schema.


6.2 Naming Conventions

       Actuate variable, parameters, and function names use a similar convention as the Java script
       variables and methods. That is, the first word is all lower case with successive words having their
       first letter capitalized. They differ when naming a report object. Report objects start out the same
       as a variable/function but have the actual type of object as it’s suffix. An example is a data source
       for the indicator view data. It would be named: indicatorViewDataSource.

       Object scope was kept to the root level. This is the default behavior of the Actuate Designer. This
       helps to quickly locate items that have been added to inherited objects. It also has the benefit of
       keeping the object scope referencing short. The disadvantage of this approach is that the object
       oriented nature of the composite object is not generic (e.g. you must name objects uniquely
       because they are created/stored at a higher level instead of in the actual container object (as
       shown in the Designer’s hierarchical view).


                                                                                                   Page: 32
 6.3 Database Connection

         The Connection.ROL is an Actuate library that provides the Oracle connection used by all the
         IBISPH Actuate reports. This library provides the parameter names needed to connect to different
         IBISPH schemas. If no connection information is provided it will use the standard connection
         properties defined in the object.


            The connection is dependent on the Oracle SQL*Net being setup with the needed firewall routing etc.

            It is recommended that only the “USER” accounts be used. These “USER” accounts are setup with read only
            privs on the data. This will help keep the actual “OWNER” account information hidden and the data more secure.



 6.4 Web Reports

         The Web Reports were designed around an inherited, object oriented approach (layers). This
         approach keeps everything together, help keep the reports consistent, make changes easier, and
         hopefully makes them a little easier to maintain and keep clean. The problem with building the
         reports based on ROL components is that most of the report objects are NOT independent (e.g.
         they're not black box, stand alone components that can be plugged into another report). Most of
         the ROLs that I was creating had visual dependencies on their container. An example would be a
         navigation ROL. It still had to know the size the container object had in mind and if you needed to
         change the width of the navigation flow and frame you had to open each report that used the ROL
         and modify it as well as modifying the actual ROL. One approach could have been to build a
         registration system, but this is way more work and complexity than is needed. The one downside
         to this approach is that if structure changes are made to a base inherited report object the reports
         that extend those changed components will now have to be put into the new structure and will most
         likely have to be resynced with the container object.

         For the report to be called/used in the system two types of entries need to be made in the
         database. The first entry must include an associated REPORT database table record. This record
         contains the report name and it’s associated URL. The URL is the base report name without any
         of the prefixes or suffixes needed to execute an Actuate ROX on the Actuate Report Server. The
         next entry that is needed is to the INDICATOR_VIEW record. The view needs to have it’s
         REPORT_NAME field set to the desired REPORT record NAME.

Building Blocks for the Web Reports:

         Filename                                   Description

         Connection.ROL                             This ROL doesn't have any visual dependencies so it was
                                                    kept split out as a separate ROL so that it could be reused on
                                                    all reports.

         WebReport.ROL                              This is the base report object for ALL Web Reports. It only
                                                    contains the common basic components and structure for the
                                                    IBISPH web reports.

         WebReport.BAS                              Contains several global functions. The first is named
                                                    getBaseReportURL. It is used to help build a complete
                                                    Actuate ROX executable URL based on the value that is
                                                    passed in. It adds the prefixes, suffixes and db stuff needed
                                                                                                                   Page: 33
                               to be able to launch the report from the Actuate server. The
                               other function is named getImageURL.          It returns the
                               complete URL needed for the web browser to retrieve static
                               images from the server.

         WebInfoReport.ROL     Base information object that inherits the WebReport.ROL and
                               adds the left hand navigation objects. There are several
                               information/text reports that inherit this as it's foundation.
                               Each of these information reports then adds it's own unique
                               report objects.

         WebInfoReport.BAS     Contains/defines the indicatorName global variable and the
                               getReportURL function. The indicatorName global variable is
                               needed for the WebInfoReport’s related Nav items. The
                               getReportURL method is used to get the complete report URL
                               and is based on the getBaseReportURL function (adds the
                               indicator view parameter).

         WebViewReport.ROL     This report object also inherits the WebInfoReport.ROL and
                               adds the objects and structure needed for the all the IBISPH
                               graph view reports. All of the graphical view reports inherit
                               from this report. The nice thing about doing this is that only a
                               few properties were changed for each of the different graph
                               reports. If a query needs to be changed for these view reports
                               then only the ROL is changed and it is set for all the reports
                               that inherit it. It's also really nice when you view the project's
                               structure because it shows only those components that were
                               added and/or changed (keeps things clean).

         WebViewReport.BAS     This file contains three global title type variables needed by
                               the graphs. It also contains a method ZeroToNull. This
                               method was tried for eliminating the zero values in the graph.
                               It didn’t help but was left in for Actuate tech support (if they
                               ever get around to looking at it).

Web Report Files:

         Filename              Description

         AreaGraph.ROD         WebViewReport with the graph being set to be of type
                               Area2D.

         AvailServices.ROD     WebInfoReport with additional text objects needed to display
                               the available services.

         BarGraph.ROD          WebViewReport with the graph being set to be of type 2D Bar.

         BarGraphGrouped.ROD   WebViewReport with the graph being set to be of type 2D Bar.
                               This graph is set with the series and category keys swapped.
                               This produces a bar graph that is grouped by series.

         BarGraphHoriz.ROD     WebViewReport with the graph being set to be of type 2D Bar
                               and the orientation property being set to horizontal.

         ImportantFacts.ROD    WebInfoReport with additional text objects needed to display
                               the important facts fields.
                                                                                        Page: 34
       Index.ROD                                  WebReport with the sections and object needed to display
                                                  both the categorized web index and alphabetical indicator
                                                  listing.

       LineGraph.ROD                              Instance of the WebViewReport.

       OtherResources.ROD                         WebInfoReport with additional text objects needed to display
                                                  the other resources.

       PieGraph.ROD                               WebViewReport with the graph being set to be of type 2D Pie
                                                  and with the GetSeries methods overridden so that the pie
                                                  chart will work correctly.

       Related.ROD                                WebInfoReport with additional text objects needed to display
                                                  the related indicator fields.

       ViewNumbers.ROD                            WebViewReport with the entire graph frame replaced with the
                                                  objects needed to display the numeric values used to produce
                                                  0the associated graph.


          To create a new report: The “base info report.rod” and “base Graph report.rod” design files are provided as base
          report templates. The “base information report.rod” template is used to create new information type reports (e.g.
          no graph). The “base graph report.rod” is used to create graph type reports. To create, simply copy and paste
          the file, rename it to the appropriate name, override the needed properties/methods, add whatever other objects
          are needed, and enter the required information into the REPORT and INDICATOR_VIEW tables.



6.5 Set Reports

       The Set Reports provide a mechanism to easily retrieve the information needed to produce the
       Health Department’s printed reports. Set reports contain a series of indicator views with a graph
       and textual information for each indicator view. These reports are called “Set Reports” because
       when they were first conceptualized, they were going to be a group of or a set of reports. The
       design of these reports is somewhat different when compared to the Web Reports. The Web
       Reports have one report object for each type of report (which is a more modular, component
       design). Since Actuate does not easily provide a way of grouping/combining many reports
       together into one report file a single template looping approach was used (driver reports and
       bursting do not address this). The report contains the basic layout and then sets the needed graph
       properties as needed. The REPORT_SET database table provides the master record for the set.
       When a Set Report is ran, the REPORT_SET.NAME is passed to the report and the report queries
       all the associated detail records to be used to generate the report. These detail records are stored
       in the REPORT_SET_ITEM table. This table contains the indicator view name to use as well as
       the type of Actuate AcSummary graph to be displayed. The report loops through all the detail item
       records (indicator views with the graph type) and builds one large report file. The graph type name
       is coded into the report design and must match exactly. Currently, the naming convention for the
       graph type matches that of the AcSummaryGraph object. There isn’t anything auto magical that
       happens when specifying the graph type in the REPORT_SET_ITEM database table. The report
       MUST be coded to handle the graph type. For example: The AcSummaryGraph can be set to a
       “GraphTape” type graph. The OutcomeMeasures report does not have any code in it’s graph
       section’s Start method to set this graph type so it wouldn’t work. To make it work, Acuate Basic
       code would need to be added to the AcSummaryGraph’s Start method that sets the appropriate
       graph properties.


                                                                                                                    Page: 35
      The IBISPH Set Report files are:

      Filename                            Description

      Connection.ROL                      This ROL doesn't have any visual dependencies so it was
                                          kept split out as a separate ROL so that it could be reused on
                                          all reports.

      HealthServices.ROD                  Community & Family Health Services Division Plan & Report
                                          information (Yellow Book).

      OutcomeMeasures.ROD                 Utah Public Health Outcome Measures Report information
                                          (Blue Book).



      Supported Graph Types:

      Graph Type / Name                   Description

      Graph2Darea                         2D stacked area

      Graph2Dbar                          2D vertical bar/histogram.

      Graph2DBar.Cluster                  2D vertical bar/histogram with the
                                          categories grouped.

      Graph2DBar.Horizontal               2D horizontal bar/histogram.

      GraphLine                           Line graph.

      Graph2Dpie                          2D pie chart.


6.6 Issues

      The dependencies are: Oracle 8.1 connection, db accounts, where the reports are placed in the
      Actuate Encyclopedia (must match the URL used in the static pages), and the URL that the static
      pages use to launch the main indicator index report. If the base structure of any inherited report
      changes then most likely the sub classed object must be “resynced”. If the change was just a
      property change or method change, then the reports that use this base object MUST be rebuilt and
      redeployed.




                                                                                               Page: 36
        Section


         7
Admin Site JSP Pages
Overview, Naming Conventions, General Design, Page List and
Descriptions, Java Script Method Descriptions
 7.1 Overview

         This section discusses the Java Server Pages (JSP) pages used for the Admin Site. The Admin
         site was designed around the Model Viewer Controller architecture with the JSPs serving as the
         “Viewer” portion of the system. The model and controller portions of the system are handled on the
         back end by the main, controlling servlet named RequestBroker and it’s associated support objects
         (the next section documents this portion of the IBISPH system). The two main functions of the
         JSPs are to present the data to the user and to allow the user to edit/update data.

         Most JSPs are tied to a request. The REQUEST database table contains the request name and
         the associated JSP URL that is to be used when that request is made by the user. The data that is
         presented/displayed with the JSP is stored within “Java Bean” type objects which are setup by the
         associated controller and stored in the user’s session.

         The pages were built to be as consistent with each other as possible. They all use the same footer
         include, menu include, style sheet, and Java script files. These pages provide limited data
         validation using Java script and was designed and tested to support Microsoft Internet IE 5.5 and
         Netscape 4.7.


 7.2 Naming Conventions

JSP File Naming Convention:

         The convention for naming of JSPs is to use all lower case and using an underscore (“_”) to
         separate each word. Each word in the name goes from most specific to least specific in
         descriptiveness. List pages are designed to be a record selection screen and are named with the
         word “_list” on the end of them. Detail pages end with “_detail” if the detail page is the only detail
         type page used to maintain that record. The indicator pages use several pages to maintain a
         single record and are named more specifically. The value pages (like dataset_category_value)
         appear to be a second page to maintain a record but the values are saved to their own database
         table. Some detail pages look like a list page but they are used for field value selection and not
         record selection.




                                                                                                      Page: 37
             It was not clear as to the ITS standard for Unix file naming so it was decided to mimic the request name but have it
             lower case – in case ITS had a problem with all upper case filenames. The other naming convention that was
             considered was to keep it consistent with the Java Class filename (Mixed Case).


HTML Naming Conventions:

          HTML tags are UPPER CASE with their attributes being all lower case. Item names use the same
          naming convention as most Java development with the first word being all lower case with the first
          letter of each word after being capitalized (example: <INPUT type=”input” name=”theName”>).

Java Script Naming Conventions:

          Java script variable and method/function names use the same first word lower case, successive
          words having their first letter capitalized.


 7.3 Design

JSP File Naming Convention:

          The convention for naming of JSPs is to use all lower case and using an underscore (“_”) to
          separate each word. Each word in the name goes from most specific to least specific in
          descriptiveness. List pages are designed to be a record selection screen and are named with the
          word “_list” on the end of them. Detail pages end with “_detail” if the detail page is the only detail

Style Sheet (stylesheet.css):

          An externally defined cascading style sheet is used whenever possible to define properties such as
          fonts, font sizes, font colors, background colors, table cell alignment etc. There are some locally
          defined styles within the JSP that are used where a general property needs to be overridden.

Hidden Fields:

          All the pages contain the “request” and “saveRequest” hidden fields. These are used for specifying
          the desired user request and to specify whether there is any data that needs to be saved. Most of
          the pages have additional hidden fields. The more common ones are described below:

          Field Name                                  Description

          request                                     Used to tell the RequestBroker servlet (and eventually the
                                                      controller) what the user has requested – which new page the
                                                      user is wanting to view. This field contains the name of the
                                                      new, desired request. It is populated/set in the submitRequest
                                                      (requestName) function. Typically, the value is passed into
                                                      the submitRequest(requestName) method by the user
                                                      pressing a button or clicking on a navigation tab.


             NOTE: All requests and saveRequests MUST have a corresponding entry in the REQUEST table. The name
             MUST match exactly (case, spaces etc.) and there MUST be a corresponding Java controller or a valid JSP that is
             to be used.

                                                                                                                          Page: 38
           saveRequest                         This field is set to the current page’s request name whenever
                                               something on the page has been modified that needs to be
                                               saved to the database. This field is populated in the in the
                                               setSaveRequest() function which gets called whenever a
                                               data field gets changed (it is called through several different
                                               logic routes). An example of this would be to view the main
                                               indicator text data a “INDICATOR_DETAIL” request would be
                                               made. If the user modified one or more of the fields on the
                                               form then a “INDICATOR_DETAIL_SAVE” saveRequest
                                               would be sent back to the RequestBroker so that the
                                               information could be updated into the database.

           name                                This field is mostly used on list pages when selecting from or
                                               updating many different records. The name is associated with
                                               each record and is not used as an input field – it is used to
                                               provide that record name to the controller. Name is the record
                                               ID that is needed to be able to resolve which record is to be
                                               updated. It is populated as the page is loaded.

           modified                            This field is used in conjunction with the “saveRequest” field.
                                               It identifies an individual record as having been modified. This
                                               allows the controller to only have to update those records that
                                               were actually changed. Modified is populated with a lower
                                               case “x” by the setModified(index) function which is called
                                               when a list page record gets changed.

           activeFlag                          Most of the database tables have an Active Flag field that can
                                               be modified on the screens. This hidden field is used to track
                                               whether they are checked or not on the screen. The reason
                                               this is needed is due to the fact that HTML checkboxes do not
                                               return a value if it is unchecked. ActiveFlag gets populated
                                               with a lower case “x” in the setActiveFlag(index) function
                                               which is called whenever the active flag checkbox is changed
                                               on the page.

           recordCount                         This value is displayed at the bottom of each list page to
                                               indicate the number of records displayed. It is also used in
                                               many of the JavaScript functions to determine whether there
                                               is more that one record in the list. If there are multiple records
                                               in the list the fields are submitted as arrays and must be
                                               handled differently. It is populated in each JSP.

Buttons:

           There are four different buttons used in the system. All four buttons are not always used on every
           page. Only the buttons applicable to the functions of the page and the user’s privileges are
           displayed on the JSP.

           Button                              Description

           Select/Edit                         Select/Edit works in conjunction with the select radio button.
                                               After selecting the record wanted and clicking the Select/Edit
                                               button the user is taken to the next detail page for the selected
                                               record.    This is done be calling the submitRequest(
                                               requestName) function with requestName being the name of

                                                                                                        Page: 39
                                               the desired request. Any changes made to the fields are
                                               updated to the database before moving to the next page.

         Save                                  Save is used to save the data on the current page and remain
                                               on the current page.         This is done be calling the
                                               submitRequest(requestName) function with requestName
                                               being the current request with the word “_SAVE” appended to
                                               it.

         Delete                                Delete is used to delete the selected record. This is done by
                                               calling the submitRequest(requestName) function with
                                               requestName being the current request with “_DELETE”
                                               appended to it. The delete function is only available to admin
                                               type users on list type pages. When the delete button is
                                               pressed an alert box will pop up to verify that the user intends
                                               to delete the record. When the page is returned after deleting
                                               a record, no record is selected in the radio button column. If
                                               the user presses a tab to move to another page without
                                               selecting a record, the system will default to the first record in
                                               the list.

         Reset                                 Reset is used to reset all input field contents back to the value
                                               they had when the page was opened.

Adding a New Record:

         If the user has privileges that allows for the addition of new records, then the needed blank input
         fields are added to the JSP (if a list JSP then the last line of the list). The field names are named
         the same as the other fields with “New” appended to the end of the field name used. These are
         kept separate so that the controller knows that this is a new record and not just the last record in
         the list. After entering the appropriate field values the user can either press the save button which
         will create the new record and remain on the current list page, or they can a click on any other tab
         which will create the new record and move on to the next page. If all the required fields for the file
         are not entered correctly or if a duplicate name field is entered, the user is given an error and is not
         allowed to add the record or navigate to another page. The required field validation is performed in
         the validate(noError) function that is included in each JSP. The duplicate name validation is done
         in the validateDupName(noError) function which is in the common.js file.

System Navigation:

         System navigation is the process of moving from page to page throughout the system. The
         navigation menus are dynamically built in the navigation_menu JSP which is included in each
         page.     The processing of menu tab selections is handled by calling the function
         submitRequest(requestName) which submits the request name to the RequestBroker servlet.
         Requests are name the same as the JSP page but are all capitalized. The RequestBroker servlet
         properly loads the necessary java bean contents and calls for the appropriate java server page to
         be displayed.

         The name of the “select” radio button for all page navigation functions is named “selectedName”.
         And the value of that radio button is the name of corresponding record element on the list page (i.e.
         the name of the radio button on the Data Source List page would be the same name as the data
         source name). This name is used to set the bean with the appropriate data for the page being
         called. It is also used when the user returns to the list page to provide a default radio button
         selection the same as the one the user selected. When the user used the linked names (anchors)


                                                                                                        Page: 40
          on the list page to advance to the detail page, the system makes the radio button selection to
          insure that the selecteName field is loaded properly.

Privileges:

          Admin: Only users with admin privileges can access the Administrator’s Menu, add new
          indicators, delete records, and set indicator owners. This admin privilege is retrieved and set by
          the controller upon first accessing the IBISPH Admin Site for that session. The user must be in the
          NDIS HL_IBISPH_ADMIN group and be logged on to the state network.

          Indicator Status: On the Indicator Main Text page, the user can change the indicator status or
          their indicators (Admin can set any, Owners/Editors can only set those in which they are assigned
          to). Whenever the status is not “open”, all the indicator detail pages are read only.


 7.4 File Descriptions

          Database functions are PL/SQL code that can be executed by a trigger, user, or an application.
          Functions are much like procedures other than they return values. The FUNC_C.SQL script file
          contains the actual function creation code as well as more detailed descriptions and comments.
          The IBIS-PH functions are:

          Filename                            Description

          admin_menu.jsp                      System Administrator’s Menu. The System Administrator’s
                                              Menu is only available to the system administrator and is the
                                              default page when the system is first accessed. All non-
                                              administrator users will get the Indicator Maintenance as their
                                              default page. The sessionUser bean is used in all pages to
                                              provide the user’s name, privileges, and other information.
                                              The System Administrator’s Menu is used to navigate the
                                              follow options (and is the only mechanism to access these
                                              pages): Data Source Maintenance, Dataset Category
                                              Maintenance, Dataset Series Maintenance, Healthy People
                                              Objectives Maintenance, Public Web Site Index Maintenance,
                                              Report Maintenance, Title Maintenance, and the standard
                                              Indicator Maintenance

          data_source_list.jsp                List of all Data Sources. Beans used: dataSource.

          data_source_detail.jsp              Detail of the Selected Data Source .            Beans used:
                                              dataSource.

          dataset_category_list.jsp           List of all Dataset Categorys. Beans used: datasetCategory.

          dataset_category_detail.jsp         Detail of the Selected Dataset Categories.       Beans used:
                                              datasetCategory.

          dataset_category_values.jsp         Values of the Selected Dataset Categories.       Beans used:
                                              datasetCategory.

          dataset_series_list.jsp             List of all Dataset Series. Beans used: datasetSeries.

          dataset_series_detail.jsp           Detail of the Selected Dataset Series.          Beans used:
                                              datasetSeries.
                                                                                                       Page: 41
dataset_series_values.jsp         Values of the Selected Dataset Series.                     Beans used:
                                  datasetSeries.

footer.jsp                        The footer JSP an include file that is included in all of the
                                  Admin Site’s JSPs It is shown at the bottom of each screen.

healthy_people_list.jsp           List of Healthy People            Objectives.          Beans     used:
                                  healthyPeopleObjective.

healthy_people_detail.jsp         Healthy People Objectives                Detail.       Beans     used:
                                  healthyPeopleObjective.

indicator_user.jsp                This is an include JSP. It contains the line that is displayed on
                                  all the Indicator Detail pages just below the navigation tabs
                                  that displays the indicator name, status and modification date.

indicator_list.jsp                Indicator List. Beans: indicator.

indicator_detail.jsp              Indicator Main Text. Beans used: indicator.

indicator_data_sources            Data Sources. Beans used: indicator, dataSources.

indicator_facts.jsp               Important Facts Text. Beans used: indicator.

indicator_healthy_people.jsp      Healthy    People.                Beans            used:      indicator,
                                  healthyPeopleObjective.

indicator_relations.jsp           Related Indicator.       Beans used: indicator, indicatorList,
                                  relation.

indicator_services.jsp            Available Services. Beans used: indicator.

indicator_view_data_sources.jsp   List of Graphs. Beans used: indicator.

indicator_view_detail.jsp         Graph’s Specs.      Beans used: indicator, datasetSeries,
                                  datasetCategory, report, title.

indicator_view_list.jsp           Graph’s Data Sources. Beans used: indicator, dataSource.

indicator_view_values.jsp         Graph’s Values. Beans used: indicator.

indicator_owner_editors.jsp       Owner Editor. Beans used: indicator, user

navigation_menu.jsp               Navigation Menu. This is an include file that is included in all
                                  Admin Site JSPs. The navigation menu is the system of tabs
                                  at the top of each page allowing the user to move throughout
                                  the system. Navigation is described in more detail above.

report_list.jsp                   List of all Reports. Beans used: report.

report_detail.jsp                 Detail of Selected Report. Beans used: report.

title_list.jsp                    List of all Titles. Beans used: title.

title_detail.jsp                  Detail of Selected Title. Beans used: title.

                                                                                                 Page: 42
       web_index_list.jsp                 List of Public Web Indexes. Beans used: indicator, webIndex.

       web_index_node.jsp                 Detail of Selected Web Index Branches.          Beans used:
                                          webIndex.

       web_index_indicator.jsp            Indicator List of the Public Web Index.         Beans used:
                                          webIndex, indicatorList.


7.5 Image File Descriptions

       Images files needed for the static pages and the Photoshop design files that were used to create
       the images:

       Filename                           Description

       Admin Tabs 150x35.psd              Photoshop design file for the normal, double wide navigation
                                          tab images (150 pixels wide by 35 pixels high).

       Admin Tabs 75x35.psd               Photoshop design file for the navigation tab images (75 pixels
                                          wide by 35 pixels high).

       Admin Tabs 150x37 ON.psd           Used to show the current selected page. Inverted color
                                          schema, double wide navigation tab images (150 pixels wide
                                          by 37 pixels high – little higher for the 3d drop shadow).

       Admin Tabs 75x37 ON.psd            Used to show the current selected page. Inverted color
                                          schema, navigation tab images (75 pixels wide by 37 pixels
                                          high – little higher for the 3d drop shadow).

       Admin Tabs 150x35 OVER.psd         Used to show the current item that the mouse point is over.
                                          Same color schema as normal but with a highlighted border
                                          and bolded white text – double wide (150 pixels wide by 35
                                          pixels high).

       Admin Tabs 75x35 OVER.psd          Used to show the current item that the mouse point is over.
                                          Same color schema as normal but with a highlighted border
                                          and bolded white text (75 pixels wide by 35 pixels high).

       arrow_down.gif, arrow_right.gif    Web index selector arrows.

       btn_xxxx.gif                       Save, reset, delete, select buttons.

       hrsa_logo.gif                      HRSA logo used in the footer.

       tab_xxxxx.gif                      Normal navigation menu tabs.

       tab_xxxxx_over.gif                 Mouse over navigation menu tabs.

       tab_xxxxx_on.gif                   Current page, navigation menu tabs.

       hrsa_blue1x1.psd                   1x1 HRSA blue pixel stretched/used to create a blue line.

       biege1x1.psd                       Same as HRSA blue 1x1 except beige/taupe.


                                                                                                Page: 43
       trans1x1.gif                        Transparent 1x1 pixel image.         This is used for explicitly
                                           defining a table cell size.



7.6 Java Script Method Descriptions

       The following list provides a brief description of all the methods that are common to all the admin
       pages. The file that contains these methods is named: common.js.

       Method Name                         Description

       submitRequest(requestName)          Submits the page to the controller to request a new page or
                                           for the current page to be redisplayed. The parameter
                                           “requestName” contains the request name. Before the page
                                           is submitted, it calls the validate(noError) function to check any
                                           field level validation that may need to be performed. If an
                                           error is registered, validate(noError) returns noError as false
                                           and the request is not completed.

       setSaveRequest()                    Loads the hidden field “saveRequest” with the name of the
                                           current request with the word ‘_SAVE’ appended to the end of
                                           it. Having this field loaded tells the controller that the page has
                                           been changed and requires an update.

       setModified(index)                  Loads the “modified” hidden field to indicate that a particular
                                           record has been changed. It then call the setSaveRequest()
                                           function.

       setActiveFlag(index)                Maintains the hidden field, “activeFlag”, for the active flag and
                                           calls the setModified(index) function. A lower case “x” is
                                           placed in the hidden field when the record is active. This java
                                           script is invoked when the user clicks on the Active Flag
                                           checkbox.

       setActiveFlagNew()                  Maintains the hidden field for the active flag for a new record
                                           being added and calls the setSaveRequest() function. The
                                           setModified(index) function is not called when a new record is
                                           being added. A lower case “x” is placed in the hidden field
                                           when the record is active. This java script is invoked when the
                                           user clicks on the Active Flag checkbox on the add new
                                           record line.

       submitLinkRequest(request, index) This is used on list pages where there is an anchor to the next
                                         page. This function sets the “selectedName” radio button
                                         corresponding to the link that has been selected. This java
                                         script is invoked when the user clicks on the name link.

       submitSelectedRequest()             Used in the administrator menu’s select button to loop through
                                           the menu and submit the selected option.

       setImageSrc(imageName, srcName) Used in the navigation menu to dynamically change the
                                     images when mouseovers occur on the navigation tabs.
                                     Note: these images are not cached initially because most
                                     browsers will do this and the state has a fast connection.



                                                                                                     Page: 44
confirmDelete(requestName)                  Whenever a delete button is pressed, this function pops up an
                                            alert box asking the user to confirm that they really intended to
                                            delete the selected record. The requestName parameter is
                                            passed into this function because if the user verifies that the
                                            record is to be deleted then the submitRequest(requestName)
                                            function is called from here.

addNewRecord()                              This function is used on list screens when a new record is
                                            being added. The radio button on the new record line is
                                            enabled and checked and the setSaveRequest() function is
                                            called.

selectOnExit()                              When the user exits a list screen without selecting a record
                                            from the list (i.e. the user moves from a list screen to it’s
                                            related detail screen), this function is invoked and will default
                                            the first entry in the list to be selected . A record needs to be
                                            selected when the detail screen receives focus. Also, if the
                                            user comes back to the list screen it will select (check the
                                            radio button) the record that was selected when the user last
                                            left the list screen.

validateDupName(noError)                    Whenever a new record is being added on a list screen, this
                                            function will check to see that a duplicate name was not
                                            entered. This function is always called from the
                                            validate(noError) function inside the JSP.


   The validate(noError) method/function is the page’s specific validation method. This function must be
   included in every jsp because it is called from the submitRequest(requestName) function which resides in the
   common.js file.. It is not placed in common.js because it’s logic will be different for each page. In some cases the
   function is void of any logic but it is still required.




                                                                                                                Page: 45
         Section


          8
Admin Site Java Servlet and Objects
Overview, Naming Conventions, Class Descriptions, Servlet Properties,
Issues
 8.1 Overview

         This section describes the back end Java portion of the Admin Site. The heart of the system is a
         simple servlet named RequestBroker. This servlet serves as a central, single point of entry for all
         admin type requests/web pages. Upon receiving a request, the servlet directs the Http Request
         received to the Java “controller” object that can handle the request. This request/controller design
         is a modified “command” pattern. The requests and controllers are associated via a database
         table named REQUEST. These request records are loaded into a vector that is owned by the
         RequestBroker. When the RequestBroker receives a request it retrieves the associated controller,
         loads it (if not loaded), and turns control over to it. The controller then sets up the needed Java
         beans and forwards to the appropriate JSP for viewing (the JSP/Html page etc is also specified in
         the database request record). If a controller is not specified and a URL is, then the request is
         forwarded to the specified URL. The system makes extensive use of Session Objects. All
         beans/objects are stored within the user’s session.


 8.2 Naming Conventions

Class Names:

         Class names follow the Java class naming standard of mixed case with the First Letter of Each
         Significant Word being Capitalized. If the name is a shortened or is an acronym, the same rule
         applies – the first letter of each word that is significant is Capitalized.

Static Class Constants:

         Static class constant names follow the Java/c constant naming convention of
         ALL_CAPS_SEPERATED_WITH_AN_UNDERSCORE.

Class Methods and Object Names:

         Class methods and object/variable names follow the Java naming convention of Mixed case with
         the first letter being lower case and all following significant words having their first letter capitalized.




                                                                                                           Page: 46
 8.3 Class File Descriptions

          This section lists the Java packages with each class file and a short description. More detailed
          comments can be found inside the class source code file.

org.ibisph.admin:

          Class Name                         Description

          AppProperties                      Class used to get and retrieve the static properties from either
                                             a property file or a servlet config.
          RequestBroker                      The IBISPH Request Broker serves as the main request
                                             servlet for the admin site/data maint arm of the system. All
                                             data admin webpage requests are/should be funneled through
                                             this servlet. For a more detailed discussion see the
                                             comments in the code.
          Requests                           Contains all the requests that are available to the system.
                                             These are loaded at the start of the user's session. The main
                                             RequestBroker uses this information to know which controller /
                                             JSP should be used to respond to the given request.
org.ibisph.admin.beans:

          Class Name                         Description

          DatabaseBean                       DatabaseBean is an abstract class the provides a foundation
                                             for most of the beans used in the IBISPH Admin system.
          DatasetCategory                    Categories Bean contains a mechanism to get all the valid
                                             categories and/or for getting an individual category record.
          DatasetCategoryValue               Category Values Bean contains a mechanism to get all the
                                             values for an individual category record.
          DatasetSeries                      Series Bean contains a mechanism to get all the valid Series
                                             and/or for getting an individual Series record.
          DatasetSeriesValue                 Series Values Bean contains a mechanism to get all the
                                             values for an individual Series value record.
          DataSource                         Contains a mechanism to get all the valid Data Source
                                             records or an individual data source record.
          HealthyPeopleObjective             Contains a mechanism to get all the valid records or an
                                             individual record.
          Indicator                          Contains a mechanism to get all the Indicators, only active
                                             indicators, and individual indicator records. This bean is
                                             different from the other beans in that it contains dependent
                                             beans. These beans are the view(s), data sources, relations,
                                             and editors. To insure that the dependent data is always up to
                                             date, getter methods are used which select based on the
                                             current indicator.
          IndicatorDataSource                The Indicator Data Source Bean contains a mechanism to get
                                             all the data sources associated with a given indicator. This
                                             bean is meant to be contained in the indicator bean
                                             (dependent).
          IndicatorEditors                   Indicator Editor Bean contains a mechanism to get all the
                                             editors associated with a given indicator.
          IndicatorRelations                 Indicator Relation Bean contains a mechanism to get the text
                                             and a vector/list of related indicator names associated with the
                                             indicator name that was used when constructing the object.


                                                                                                    Page: 47
          IndicatorView                               IBIS-PH Indicator View Bean contains a mechanism to get all
                                                      the views and/or a specific view's info associated with a given
                                                      indicator.
          IndicatorViewDataSources                    Indicator View Data Source Bean contains a mechanism to
                                                      get all the data sources associated with a given indicator view.
          IndicatorViewValue                          Indicator View Value Bean contains a mechanism to get all
                                                      the values associated with a given indicator view. This bean
                                                      is meant to be contained in the indicator view bean which is
                                                      contained in the indicator bean.
          LDAPUserInfo                                IBIS-PH LDAP User Info Bean contains a mechanism to get
                                                      all the information about a given user. It is based on the ITS
                                                      Read User Info servlet when a translated LDAP connection is
                                                      passed in with the load command.
          NavigationMenu                              Contains the navigation menu data for the user based on their
                                                      privs.
          Relation                                    Contains a mechanism to get all the valid records or an
                                                      individual record.
          Report                                      Contains a mechanism to get all the valid records or an
                                                      individual record.
          Status                                      Contains a mechanism to get all the valid records or an
                                                      individual record.
          Title                                       Contains a mechanism to get all the valid records or an
                                                      individual record.
          User                                        Provides a mechanism to get all the valid records or an
                                                      individual user record. The LDAP user id is retrieved from the
                                                      INDICATOR_EDITOR table and unioned with the
                                                      INDICATOR_OWNERs. These LDAP IDs are then used to
                                                      retrieve the user's specific information from the ITS user info
                                                      servlet.
          WebIndex                                    The Web Index Bean contains a mechanism to get either a
                                                      specific web index record or if select all, provides a way to get
                                                      all records based on the parent web index name passed in.
          WebIndexBranch                              Web Index Branch Bean - only gets branches (e.g. no
                                                      indicator is assigned).


              NOTE: The bean is named based on the record usage. If it is singular, then the bean only deals with one record at
              a time. The beans that are named plural deal with a group of records.


org.ibisph.admin.controllers:

          Class Name                                  Description

          DatasetCategory                             Controller handles all the requests that involve viewing
                                                      DatasetCategories and the creating new/updating existing
                                                      DatasetCategory records.
          DatasetSeries                               Controller handles all the requests that involve viewing
                                                      DatasetSeries data and the creating new/updating of existing
                                                      DatasetSeries records.
          DatasetSource                               Controller handles all the requests that involve viewing
                                                      DataSource data and the creating new/updating of existing
                                                      DataSource records.
          HealthyPeople                               Controller handles all the requests that involve viewing
                                                      HealthyPeople data and the creating new/updating of existing
                                                      HealthyPeople records.
          IndicatorSave                               Controller handles all the requests that involve creating
                                                      new/updating of existing Indicator related data.
                                                                                                                        Page: 48
           Logon                                 The Logon Controller handles the LOGON request. This
                                                 includes validating the user, setting associated privileges, and
                                                 setting up the user’s user session bean.
           Report                                Controller handles all the requests that involve viewing Report
                                                 data and the creating new/updating of existing Report records.
           RequestController                     Abstract class that all Request Controllers extend from. This
                                                 class contains routines that pulls data from the Http Request
                                                 (submitted HTML forms) and updates/inserts it into the
                                                 database tables.
           SubmitTest                            The Submit Test Controller simply displays the submitted
                                                 request data received back to the user (used for testing form
                                                 submission).
           Title                                 Controller handles all the requests that involve viewing Title
                                                 data and the creating new/updating of existing Title records.
           WebIndex                              Controller handles all the requests that involve viewing
                                                 WebIndex data and the creating new/updating of existing
                                                 WebIndex records.

org.ibisph.pub:

           SubmitSurveyResponse                  Submit Survey Response servlet that serves simply updates
                                                 the user survey table.

org.ibisph.utils:

           Class Name                            Description

           DAO                                   Class used to execute SQL queries and updates without
                                                 having to do the try/catch blocks, the connection stuff and
                                                 explicitly closing everything. Also gives a better way of
                                                 localizing the sql (and sql exceptions).
           DAOPool                               Data Access Object Pool manager. This is a real simple, very
                                                 UN robust pool manager.
           Logger                                Simple message/exception logger that sends output to
                                                 STDOUT and to a specified log file.
           PooledDAO                             Simple wrapper class to add pooling properties/methods
                                                 needed to the DAO class.
           StrLib                                StrLib is an abstract class with various utility methods for
                                                 string manipulation and other odd ball methods.
           WebLib                                Web Lib is an abstract class with various utility methods used
                                                 for odd ball web/session related stuff.


 8.4 Servlet Properties / Init Parameters

           The following provides a description of all the static system parameters that are currently
           implemented in the application. These values are stored as servlet init parameters (but could also
           be put into a properties file). To set/view these properties open the .WAR file, goto the
           RequestBroker servlet and select the “Init Parameters”. These values are retrieved via the
           AppProperties static class via a “getProperty” method. When calling the AppProperty’s
           “getProperty” method a default value is specified. This value will be used if and only if there is not a
           corresponding value found (a entry is also made into the log file and the system output if an entry is
           not found).

           Property Key/Name                     Description and Default Value


                                                                                                          Page: 49
      adminDefaultRequest                 Default request for the admin user when they first logon/hit
                                          the application. Default: ADMIN_MENU
      adminPrivCode                       Priv code that is associated with an Admin user. Default:
                                          A
      defaultRequiredFieldValue           String value that is inserted into the database column/field
                                          for a required field so that it is not null. Default:
                                          [Required - Please Replace]
      homeDefaultRequest                  Default request for the non admin user when they first
                                          logon/hit the application. Default: INDICATOR_LIST
      jndiDataSourceName                  JNDI jdbc data source name that is used to connect to the
                                          database. Default: jdbc/hl_ibisph_admin
      logFileBaseName                     Output log filename without an extension. Default:
                                          ibisph
      logFileMaxSize                      Output log filename max size in byes. Default: 200000
      logLevel                            Output log level. This is a bitwise ring so that 0 is none,
                                          255 is all. Default: 255
      logNotificationLevel                Output notification log level. Default: 255
      logNumLogFiles                      Output log file versions. Default: 1
      logSysOutLogLevel                   System output log level. Default: 255
      siteminderAdminIDTag                NDIS group name that all admin users must belong to get
                                          the admin priv. Default: HTTP_HL_IBISPH_ADMIN
      userInfoLDAPLookupURL               URL of the user info servlet that is used to retrieve user
                                          information based on their LDAP info. Default:
                                          http://itsweb5.state.ut.us/servlet/ReadUser
                                          InfoServlet


8.5 Issues

      IMPORTANT: If the system is expanded to include the public site the system package and
      deployment should be rethought. If left in it’s current state, the entire system is redeployed when
      ever any change is made to the system (even if only 1 class is modified).

      If things don’t work the following could be the cause: iPlanet servet URL (name registration), Web
      Server and iPlanet, system deployment and registration, servlet init params/properties, JNDI data
      source setup, LDAP user setup, NDIS HL_IBISPH_ADMIN group setup and user entry, network
      security (firewalls and database etc.), ITS LDAP User Info servlet and it’s URL etc.



                                          --- End of Document ---




                                                                                                Page: 50

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:12
posted:9/16/2012
language:Unknown
pages:51