SOW Attachment A4.3 Bureau Reporting Sub System AETC.pdf

Information Technology Engineering Support for HRSA Data Systems

Aids Education Training Center (AETC) Web-based Data

Upload System

Detailed Design Document

Attachment A4.3

Health Resources and Services Administration 5600 Fishers Lane

Rockville, MD 20857

AETC Web Application Detailed Design Document

March 5, 2021 i

Revisions

Revision Number

Date Description

1.0 Jan 10, 2008 Initial Draft – outline

2.0 August 3, 2009 Updates for 2009 changes

3.0 August 25, 2010 Updates for 2010 changes

4.0 July 12, 2011 Updates from 2011 changes

5.0 June 14, 2012 Updates from 2012 changes

March 5, 2021 ii

Table of Contents

1.0 Introduction

1.1 Background

1.2 Purpose and Scope

1.3 System Overview

1.4 Document Organization

1.5 Change Control

1.6 Target Audiences

1.7 Assumptions

1.7.1 Environments, Setup and Maintenance

1.8 References

2.0 System Architecture

2.1 Overview

2.2 External Interfaces

2.3 Hardware Architecture

2.4 System Software Architecture

3.0 Software Design

3.1 Web Request Framework

3.1.1 AETCHandler Class

3.1.2 Web Command Factory

3.1.3 Web Command Objects

3.1.4 BizProcess (Business Process) Objects

3.1.5 Utils (Utility) Objects

3.1.6 Renderer Objects

3.1.7 Biz (business) Objects

3.2 AETC Data Entry Forms Request Flow

3.3 AETC Validation

3.4 AETC Dataset Generation

3.5 Middle-Tier Classes

3.5.1 BizProcess Classes

3.6 Code Base

3.6.1 AETCApp Namespaces / Directories

4.0 Database Design

4.1.1 Data Dictionary

4.1.2 Static and Dynamic Data

5.0 External Interfaces

Appendix A: Naming Conventions

Appendix B: Class Association Diagrams

1. Class Diagram – AETCBase and Renderer

2. Class Diagram – Utils

3. Class Diagram – WebCommand Namespace

4. Class Diagram – Parsing Parser

5. Class Diagram – Validation Parser

Appendix C: Sequence Diagrams

1. Sequence Diagram - Home Page

March 5, 2021 iii

List of Figures

Figure 1 - Architecture Overview Figure 2 - External Interfaces Figure 3 - EHB Integration Server Architecture Figure 4 - Access Mode Figure 5 - Environments Figure 6 - Web Request Framework Figure 7 - Component Model Figure 8 - Web.Config - Custom Handler Implementation Figure 9 - Web Request - Data Entry

March 5, 2021 1

1.0 Introduction

The HIV/AIDS Bureau’s (HAB’s) AIDS Education and Training Center’s (AETC) Web Application allows grantees from the regional AETCs to submit data files annually. AETC grantees are required to log in through the Health Resources and Services Administration (HRSA) Electronic Handbooks (EHBs) application to access the AETC Web Application.

This document describes the technical architecture of the AETC Web Application.

1.1 Background

In 2004, the HIV/AIDS Bureau began requiring its AIDS Education and Training Center (AETC) grantees to submit data for each of their centers as collected on five forms: The Event Record (ER), the Technical Assistance (TA) form, the Group Clinical Consultation (GCC) form, the Individual Clinical Consultation (ICC) form, and the Participant Information Form (PIF).

Data collection based on these five forms continued through 2007.

In 2005, HAB and SAIC developed the first Web-based data collection system for the AETC grantees to use to report their data. The goal of the system was to replace paper-based submissions with a more simplified and efficient mechanism allowing AETCs to submit their data files online. The system allows HAB to track the trainings conducted with program funds, to gather participant information, and develop the required statistical reports. Initially, data were collected twice per year. Subsequently, HAB transitioned to annual reporting. In 2007, in accordance with the new OMB-approved grant guidance, the five original data collection forms were condensed to two - the Event Record (ER) and the Participant Information Form (PIF). The ER collects information on each activity, including topics covered, number of people trained, type of training conducted, training modality, length of training, and collaborations with other organizations. The PIF captures information such as profession, primary role, employment setting, etc. from individuals who attend events. In 2008, SAIC released a revised/updated version of the AETC Web-based data collection system reflecting this major modification.

In subsequent years, SAIC has made additional modifications to the AETC Web application as dictated by HAB-specified requirements, and has re-released the application with these modifications and enhancements in July 2009 and July 2010 respectively.

In 2011, SAIC released an updated version of the AETC Web-based data collection system reflecting modifications to the latest OMB approved PIF and ER forms. The latest OMB approved forms went into effect in September, 2010 for AETC grant year 2011.

In 2012, one additional OMB approved data collection form FTCC is collected from the Grantees, which went into effect September, 2011 for AETC grant year 2012. The 2012 AETC Web-based data collection system has been modified to accommodate the addition of FTCC Participant Information Form. To identify the difference between AETC PIF and FTCC PIF, the system has been modified to state AETC PIF wherever reference has been made to PIF and FTCC PIF for references made to the new form. This Detailed Design Document has been

March 5, 2021 2 updated to reflect all major modifications to the AETC Web Application as of the July 15, 2012 release.

The system includes the following features:

• Secure access by Grantees through HRSA’s EHBs. The AETC Web Application is a secure internet application which may be accessed through HRSA’s Electronic Handbooks (EHBs) by grantees throughout the United States.

• Role-Based Controls. The AETC Web Application is a role-based system in which access to the system, data and specific functions is determined based on the users’ roles, as defined in the users’ account profiles. What users can see and do online is determined by the account with which they log into the system.

• Data Validations. The system performs extensive, often complex, data validation checks after the users upload their data. Once the user has finished entering data, the entire AETC is validated. Error and warning messages (as specified in the requirements document and in accordance with the current data manual) are made available to the user in a report. The report provides detail about the nature of the errors and warnings and information to help the user locate the record(s) that triggered the error or warning.

1.2 Purpose and Scope

The purpose of this document is to provide a detailed description of the technical design of the AETC Web Application. Specifically, this document describes:

• system architecture

• software design

• database design

• external interfaces

This document describes SAIC’s approach to implementing the capabilities and functions of the proposed system and satisfying all of the requirements enumerated in the Requirements Definition Document.

March 5, 2021 3

1.3 System Overview

The AETC Web Application allows users to upload AETC data. Users of the AETC Web Application include:

• Grantees receiving funds from HAB

• HAB staff members

• SAIC’s help desk staff

• SAIC’s development team tasked with building and maintaining the system

Users access the AETC Web Application using a standard web browser (Microsoft Internet Explorer Version 6.0 or higher). The system servers (Web Server, .Net Application Server and Database Server) are hosted by HRSA’s Office of Information Technology (OIT) department.

The AETC Web Application requirements are defined in detail in the Requirements Definition Document, however, to summarize, the AETC Web Application allows users to:

• Upload Event Record data (ER), AETC Participant Information Form data (AETC PIF) and FTCC Participant Information Form data (FTCC PIF)

• Validate data uploaded

• Register and manage user accounts

• Get email alerts after the validation is complete

• Access on-line help

• Monitor and track the workflow status

• Un-submit a submitted AETC Report

The AETC Web Application employs the following technologies, as described in more detail elsewhere in this document:

• Microsoft .Net Framework (ASP.Net, C#.Net, Framework Class Library)

• XML and XSLT

• Microsoft SQL Server

• Microsoft IIS

1.4 Document Organization

After the Introduction section, this document is divided into four sections and has several appendices:

1. The System Architecture section provides an overview of the hardware, software, sub-systems, associated systems and external interfaces that comprise the HAB AETC Web Application.

2. The Software Design section provides a detailed description of the custom software built and maintained by the project team.

3. The Database Design section addresses the relational database that stores AETC Web Application data.

March 5, 2021 4

4. The External Interfaces section describes the various ways the AETC Web Application communicates with outside systems and databases.

5. The Appendices (Appendix A through Appendix C) consist mostly of detailed technical diagrams and tables. These are referenced by the body of this document, but are appended, as they can be voluminous.

1.5 Change Control

Throughout each development cycle, the technical details of the HAB AETC Web Application change over time as functionality is added, enhanced, removed or otherwise modified. During the application maintenance phases, the design changes from time to time. As a result, this will remain a “living” document that the development and maintenance teams should strive to keep up to date to the extent possible.

1.6 Target Audiences

• The audience for this document is primarily those with a responsibility for the technical development, enhancement, and maintenance of the AETC system at present and in the future

As a result, the document is detailed and technical. In addition, this document will be reviewed by HRSA and SAIC managers responsible for overseeing the project. To the extent possible, these audiences have also been taken into consideration through clear language and the avoidance of technical jargon.

1.7 Assumptions

This document was written based on the assumptions enumerated in the subsections below.

1.7.1 Environments, Setup and Maintenance

The following assumptions apply to the system environment, setup, and maintenance:

• OIT will install the operating system on the production and test web servers.

• The AETC Web Application development team will be given administrator access on all application and database servers used in this development effort.

• OIT will obtain and install Secure Sockets Layer (SSL) certificates on production and test web servers.

• OIT will do firewall configuration, as necessary, to allow secure traffic from grantees working over the internet, through the firewall to the test and production servers on the default secure port (443).

• OIT will provide sufficient computer hardware and network bandwidth to accommodate peak expected usage on the AETC Web Application web server.

• End-user workstations must be using Microsoft Internet Explorer version 6.0 or higher.

• End-user workstations will be set to a screen resolution of at least 800x600.

March 5, 2021 5

1.8 References

The following references are used in this detailed design document:

• AETC Web Application:

https://performance.hrsa.gov/hab/RegLoginApp/Admin/Login.aspx

• The AETC Web-based Data Entry System Requirements Definition Document, dated April 26, 2012, for AETC Release 7.0; and its predecessor documents.

• RFQ/Project No. 61368, Lifecycle Information Technology Support Services, Statement of Work.

• HRSA AIDS Education and Training Centers Data Collection Manual, HRSA HIV/AIDS Bureau – Dated September 2010.

• PIF FTCC Data Manual, dated April 1, 2011.

• AETC Web Application Release 7.0 Functional Design Document, dated July 13, 2012.

• EHBs Integration Services (EIS) Design Document by REI Systems, September 2006.

2.0 System Architecture

2.1 Overview

The AETC Web Application is deployed as a .Net web application. Figure 1 provides an architecture overview, including servers, clients, subsystems, databases and communication among them.

March 5, 2021 6

WAN

Compaq Server 1 Application Server

Compaq Server 2 Database Server

HAB Staffs

Part A Grantees

SAIC Help Desk

Internet Information Server (IIS)

Web Server SMTP Server

AETC Web Interace .Net Application

Security / Authentication

.Net Application

DMZ

HRSA Network

SQL Server

AETC

Database

AETC Web Interface - Architecture Overview

HAB Staffs

SAIC Dev Team https http smtp

Native SQL Server

Named Pipes

File System Write

.Net State

Server Other .Net Applications

Security / Auth

Database

Other Databases file system / docroot

Figure 1 - Architecture Overview

March 5, 2021 7

AETC Home Page

Login through HAB RegLoginApp

(administration users)

Login through EHBs

(grantee users)

Data Entry

• Grantees

• Data Entry User

Workflow

• All Users

Administration

• HAB Users

• System Administrators

• Data Entry Users

Cover Page

Manage Subsites

List AETCs (non-grantees only)

AETC Submission Status

Non-grantee Users Grantees

Change Password

Edit Registration

Manage Users

AETC Web Application Site Map

The web/application server resides in the Demilitarized Zone (DMZ), between two firewalls.

Users outside of the HRSA network (grantees and HRSA Call Center) access the site through encrypted Hypertext Transfer Protocol (http) Secure Sockets Layer (SSL), or https communication. Users inside the HRSA network access the site through standard http requests.

The .Net Application can be configured to use Microsoft State Server, running in a separate process, to maintain session information, or it can store session information in-process. The application is designed such that either of these methods can be used so that HRSA can easily scale-up the application by adding hardware without redesigning the software. As long as the application is deployed on a single application server, it will be configured to store session information in-process. If it is later determined that the system needs to boost performance or accommodate increased load, HRSA can add a load-balancer and a second application/web server. The AETC Web Application will be able to run unchanged in this distributed

March 5, 2021 8 environment once the .Net Application configuration is changed to use State Server. This is especially important because HRSA is deploying multiple applications on the same server.

The AETC Web Application will allow grantees to upload Excel or MS Access versions of their PIF and ER data through the site. These will be stored as Microsoft Excel (.xls) or Microsoft Access (.mdb) files in the document root.

2.2 External Interfaces

Figure 2 summarizes the application’s external interfaces. External Interfaces are described in more detail in Section 5.0. There are three main external interfaces:

1. The AETC Web Application cover page. Through the cover page, the users can upload their AETC Participant Information Form (AETC PIF), FTCC Participant Information Form (FTCC PIF) and Event Record (ER) data to the Web Application file system. The data is then processed periodically by two applications that:

a. Move data from the uploaded file into the AETC Web Application database.

b. Validate the uploaded PIF and ER data and save validation results in the database.

2. Email alerts are sent out after the validation process is complete.

3. Integration with EHBs Integration Services (EIS) interfaces to update deliverable status in the EHBs.

March 5, 2021 9

AETC Web Interface (.Net Web Application)

Security / Authentication

Database

Registration & Authentication (.Net Web Application)

AETC Database

Microsoft SMTP Server

1. Email alerts file system / docroot

AETC Web Interface External Interfaces

AETC Windows Service

Processes Saving data to database and

Validation Processor

Task

2. Uploaded PIF and ER in .mdb or .xls format

3. Uploaded Files are saved to DB

AETC Web Application

EHB EIS server

4. Update

EHB

deliverable status

Figure 2 - External Interfaces

March 5, 2021 10

SO

AP

/H

TT

PS

AD

O

.N

ET

SO

AP

EHB Integration Server

Integration Services EHB SAC

Database Server (Data Source)

HRSA EHB DataBase

Data Access

AETC Web Interface

XML API

Bureau Specific Systems Bureau Specific Systems

Figure 3 - EHB Integration Server Architecture

March 5, 2021 11

Grantee Data Viewer

Start

Access Mode = Read Only

Stop

Is Grantee Org

ID = AETC

record Org ID?

Access Mode = Denied

No

Yes

Figure 4 - Access Mode

March 5, 2021 12

SQL Server

FixedLengthHandler

SASHandler

AccessHandler

SPSSHandler

ExelHandler

AETC File Upload Process

AETC File Handler

(1) httpRequest/httpResponse

Browswer

(2) Request/Response (3) Request/Reponse Aetc High Level Validation

AetcFilesProcess

AETC

WebCommand Parse

Request

HAB

WebCommand

(4)

Validation Succesful

(5)

(6) Yes

(7) Success Message

(6) No

AETC Logger

(7) Failure Message

(8) Send Response

AETC

DetailValidator

Validation Succesful

(10)

(11) Yes

HAB DataAcces

(11) NoAETC EMailHandler

(12)

(15)

(9)

AETC

DataAccess

AETC

BizProcess

AetcFilesProcess

(14)

AETC

WebCommand

SMTP Server (13)

March 5, 2021 13

2.3 Hardware Architecture

The AETC Web Application is deployed on virtual computer server provided by HRSA OIT.

Each environment (development and production) consists of one application and one database server. The server specifications are as follows:

Database Servers:

• Virtual machine with two Intel Xeon vCPUs

• 4 GB RAM

• 50 GB hard drive for operating system

• 180 GB RAID-5 hard drive for data storage

Application Servers:

• Virtual machine with two Intel Xeon vCPUs

• 4 GB RAM

• 50 GB hard drive for operating system

• 100 GB RAID-5 hard drive for storage

These four servers are deployed across two environments (Dev/Test and Production) as shown in Figure 5.

March 5, 2021 14

WAN

Production

Web/Application Server

CSR

Grantees & Providers

SAIC Help Desk

HRSA Network

HAB Staffs

SAIC Dev Team

DMZ

Dev/Test Database

Server

Production Database Server

Dev/Test Web/Application

Server

To Access Production Application

Environments

Production Environment

Dev and Test Environment

Figure 5 - Environments

HRSA OIT maintains these servers. Table 1 further describes these servers and how they will be configured.

Table 1: Server Specifications

SOFTWARE SPECIFICATIONS

(Software needed to configure the database, web and application servers)

Server Category Database Operating

System Sever

Application Application

Database

SQL Server 2008 (w/sa login privileges)

MS Windows 2008 Server —— ——

March 5, 2021 15

SOFTWARE SPECIFICATIONS

(Software needed to configure the database, web and application servers)

Server Category Database Operating

System Sever

Application Application

Web/Applicatio n ——

MS Windows 2008 R2 Server

MS Internet Information Server (IIS) 7.0

• MS .NET Framework*--minimum requirements for .NET:

• Internet Explorer 6.X (or higher) w/SP 2

• Windows XP SP 3

• MDAC 2.7

• IIS 6.0

• ODBC Drivers (for MS Access)

• AETC Web-Interface application code (provided by developers)

• (Need at least one Administrator Account)

* The Microsoft .NET Framework can be downloaded or execute ‘Dotnefx.exe’ from the Windows component CD. (Note: See below for detailed minimum software and hardware requirements for installing Microsoft .NET.)

HARDWARE SPECIFICATIONS

Sever Type Model CPU Memory Speed Processor

Database Server Virtual Machine 4GB 2 X 2.66GHz vCPU - Intel Xeon X7460 Application Server Virtual Machine 4GB 2 X 2.66GHz vCPU - Intel Xeon X7460

2.4 System Software Architecture

The AETC Web Application consists of the following software components:

• Microsoft Internet Information Server 7.0 o world wide web (www) server o simple mail transport protocol (smtp) server

• Microsoft .Net Framework 4.0

• Microsoft SQL Server 2008 R2

In addition, the development team is using the following Development Tools:

• Microsoft Visual Studio 2010

• Microsoft SQL Server Enterprise Manager

• Microsoft SQL Server Query Analyzer

• XmlSpy 2009

• MS Excel / MS Access

Custom software developed by the Project Team includes:

• AETC Web Application

• HRSA Performance System Login and Registration Application

• Windows service to parse and validate uploaded data

March 5, 2021 16

The AETC Web Application supports Microsoft Internet Explorer, Version 6.0 (and above) using the SSL protocol.

3.0 Software Design

The HAB AETC Web Application consists of various modules and sub-systems. This section addresses the development team’s approach to these various tasks. The heart of the HAB AETC Web Application is its web request framework that receives HTTP requests from the web server and processes them. Regardless of the nature of a request, it is handled using the same approach and the same set of classes. This framework is described in details in the following section.

3.1 Web Request Framework

The Web Request Framework and most business process classes have been separated and abstracted into a separate dll called ‘Hab.core’. This design greatly improved code reuse and promote common architecture across different HAB applications like ADAP, RSR, AETC, MAI.

This framework employs a “three-tiered” design approach wherein the software components are separated into three tiers or layers:

1. Tier 1 or Presentation Layer – code components in this layer are responsible for formatting and displaying information to the user. This layer does not require knowledge about where the data comes from or how to process data or requests; it only needs to know how to display results to the users. First tier components including the Renderer classes are discussed in Appendix B: Class Association Diagrams, as well as a number of XSLT files.

2. Tier 2 or Business Layer – code components in this layer are responsible for processing requests, implementing business rules and otherwise ensuring that requirements are met.

This layer acts as the glue and translator between the data that is stored and what the user sees on screen. Second tier components including the Business Process and Utility classes are discussed in Section 3.3.

3. Tier 3 or Data Layer – this layer consists of the database tables, views and stored procedures. It does not require knowledge about how its data will be displayed to the user or manipulated by the code.

By separating the code into these layers, we reduce interdependencies between them. This allows each area of code to have clear, simple and specific responsibilities on which developers can focus without needing detailed knowledge about all other components of the system. The team is thus able to implement clear divisions of labor and responsibility, and work together on a limited number of interfaces between system components.

The single largest area of functionality in the HAB AETC Web Application is AETC PIF, FTCC PIF and ER data collection. Section 3.2, AETC Data Entry Forms Request Flow Manual, describes data collection in detail. Generally speaking, data collection includes:

March 5, 2021 17

• Uploading the appropriate data to certain location

• Validating data entered and uploaded by the user on the client (Web Browser)

• Parsing and saving data entered and uploaded by the user to the database

• Validating data on the server

The data collection process also relies heavily on a group of “business process classes.” A business process class is typically responsible for handling logic that is specific to HAB’s business needs. They dictate how an AETC file is uploaded and processed, controlling areas such as validation, navigation and workflow. The “WebCommand” classes use “BizProcess” classes to help process their requests. These classes are described in detail in Section 3.3 AETC Validation.

Several other areas of software design are also addressed in this section. Please refer to the diagrams in Appendix B: Class Association Diagrams, Appendix C: Sequence Diagrams, and Section 5.0 - External Interfaces for details and specifics on these areas of the design.

Figure 6 - Web Request Framework illustrates the AETC Web Application Web Request Framework.

March 5, 2021 18

Handler WebCommandFactory

2. createWebCommand(CommandName)

WebCommand

Utils

BizProcess

3.

P as s

Re qu es t t o W eb C om m an d

4a. Use BizProcess Classes

4b. Use Utils Classes

Database

Renderer

5. Send Information for

Output (generally as

XML)

1. Web Request

6.

W eb R es po ns e

XSLT

Web Request Framework

Figure 6 - Web Request Framework

March 5, 2021 19

EHBAuthentication «subsystem»

EHB

AETC Proccess Engine BusinessProcess

DataBase

AETC High Level

Component Model

Web Browser httpRequest() httpResponse()

March 5, 2021 20

EHBAuthentication «subsystem»

EHB

AETCWebCommand

AETC BizProcess

AETC DataAccess

AETC High Level

Component Model

Web Browser

AETC File Handler

Figure 7 - Component Model

3.1.1 AETCHandler Class

The web application is configured such that all requests (GET/POST) are sent to a single ASP.Net page called AETC.aspx, and are processed using the handler class, AETCHandler.cs, which inherits from class RequestHandler.cs. To make this possible, the RequstHandler class was written to implement the following interfaces:

• IHttpHandler

• IRequiresSessionState

IHttpHandler interface consists of a public property, IsReusable and a public method, ProcessRequest. IsReusable indicates whether or not multiple requests can use the same instance of the handler. In the case of the AETC Web Application AETCHandler class, it is set to “true”.

ProcessRequest is a method that takes an HttpContext object as a parameter and contains the custom code for processing a request. ProcessRequest does not return anything. The IRequiresSessionState interface does not have any properties or methods, but must be implemented in order for the application to be able to access the web session object.

Figure 8 - Web.Config - Custom Handler Implementation shows the first few lines of the web application xml configuration file (web.config) where the code enabling the use of the AETC Application custom handler is incorporated.

March 5, 2021 21

Figure 8 - Web.Config - Custom Handler Implementation

The AETCHandler class gets all web application requests. As the first step of processing a request, the AETCHandler class checks to ensure that the user has been authenticated with the system. When users log into the system, the login application writes a session cookie back to the browser. The session cookie is called “Common_UserID” (although this value is configurable in the Web.Config file). The cookie is only set after the user has successfully logged in. The cookie values include the username and login status.

For example, in Appendix C: Sequence Diagrams the diagram describes the use of the Web Request Framework in processing a request for a data entry page on the AETC form. Note that the first step is invoking the method, RequestHandler.handleRequest() with the HttpContext.

3.1.2 Web Command Factory

Once the AETCHandler has verified that the user has been authenticated and is in the midst of an active session, it extracts the name of the web command from the request (either in the querystring or posted as a form variable). In step 2 of Figure 9 - Web Request - Data Entry, the RequestHandler invokes the createWebCommand() method of the WebCommandFactory object, passing the command name. The Web Command Factory is a “singleton” object (meaning that only a single instance is created; that instance is shared and reused throughout the application) that determines the specific type of WebCommand object to create and return based on the command name that was passed in.

In the data entry example (see Appendix C: Sequence Diagrams) the RequestHandler invokes the method createWebCommand(), passing “dataEntry” as the input parameter, to get a reference to the WebCommandDataEntry object that will process the request.

3.1.3 Web Command Objects

The RequestHandler then verifies that a valid WebCommand was returned from the WebCommandFactory, then, using a utility class called “Authenticator,” checks whether the logged in user is authorized to run the requested command. If the user is authorized, the RequestHandler invokes the “execute” method of the WebCommand, passing the HttpContext object (this object contains the standard web objects, such as request, response and session).

<?xml version="1.0" encoding="utf-8" ?> <configuration> <system.web> <httpHandlers> <add verb="*" path="UI/AETC.aspx" type="AETCApp.UI.AETCHandler, AETCApp" /> </httpHandlers>

</system.web> </configuration>

March 5, 2021 22

The WebCommand is responsible for interacting with business process classes, utility classes, the Http Context and the database in order to correctly process the class (whether retrieving information, saving information, getting a report or otherwise manipulating the system). There is a specific web command class for each type of request that the system is able to process.

In the data entry example (Appendix C: Sequence Diagrams) the RequestHandler invokes the method execute() method on the WebCommandDataEntry. Note that this call triggers a flurry of activity by the Web Command class to do what is required to return the data entry form to the user.

3.1.4 BizProcess (Business Process) Objects

The AETC application includes a number of complex business processes that can be grouped into the following categories:

• Parsing Uploaded Data and Converting it into XML for Storage in the Database

• AETC Validation

• XML Generation for Cover Page Display

• Displaying Validation Results and drill down of the detail errors/warnings

As discussed in the previous section, the WebCommand object knows how to process a request.

The WebCommand is able to invoke business processes as necessary to perform these types of services. Separating these complex processes into middle tier classes helps streamline and simplify the code in the Web Command classes. It also makes the code more maintainable and able to adapt to changes in the business rules that govern the system.

3.1.5 Utils (Utility) Objects

The AETC application includes a number of utility objects that provide a wide range of general services to any class that needs them. The functions carried out by utility classes are more generic and generally much less complex than those in the BizProcess namespace. They include:

• Authentication/Access Control

• Logging

• Deliverable

• Others

All classes of objects (WebCommands, BizProcess, Utilities, Renderer, etc.) use the utility objects as they process requests.

3.1.6 Renderer Objects

For the most part, Renderer objects are paired with WebCommands. In the data entry example (Appendix C: Sequence Diagrams) WebCommandDataEntry does the processing while RenderDataEntry is responsible for rendering the resulting display. The WebCommands pass the data that result from their processing to the Renderer classes for display. Generally the Renderer has properties that the WebCommand sets before calling the renderer’s display() method to push the output to the response stream. In most cases the WebCommand passes the renderer an XML document and an indication of which XSLT should be used to transform it to

March 5, 2021 23 its final form as an HTML document that gets sent back to the browser. The renderer objects are also responsible for displaying the header, footer and appropriate menus.

3.1.7 Biz (business) Objects

A number of business objects have been created to model the Entity objects in AETC applications. An AETC Report object represents an AETC yearly report that the user is currently working on.

3.2 AETC Data Entry Forms Request Flow

The core functionality of the AETC Web Application is data entry and uploads. As described in Section 3.1 Web Request Framework, data entry requests are processed using the appropriate Web Request Framework classes. This section provides additional detail on data entry. The scenario described in this section is one in which a user uploads data file and clicks “Save” or “Upload Files” from a data entry page in the AETC. Figure 9 - Web Request - Data Entry illustrates the Web Request Framework as it is used to process this type of request (it is similar to Figure 6 - Web Request Framework but adds some detail that pertains only to Data Entry requests).

March 5, 2021 24

Handler WebCommandFactory

2. createWebCommand(”DataEntry”)

WebCommandDataEntry

Utils

3. Pass Request to W eb Com m and execute(HttpContext)

4a. U se BizP rocess Classe s

4b. Use Utils Classes

Database

Renderer 5. Send Information for

Output:

a) XML Form data from XML Generator

b) XML Validation errors from Validation class

1. Web Request

(form POST to save data)

6. W eb Response

(HTML inclu des e rro rs a nd/or n ew form for e ntry) section#xslt.xslt

Web Request Framework

- Data Entry

BizProcess

2. Validation

1. PostBackParser

4. AetcXmlGenerator

3. Navigation validation.xml questionToXmlMap.xml errorMsg.xslt

Figure 9 - Web Request - Data Entry

The WebCommandDataEntry class gets invoked just as any other type of request would.

However, in this scenario, it will make use of several business process classes while executing the following steps:

1. PostBackParser – this class is responsible for converting form elements into an XML format that is understood by the stored procedure that saves data from any AETC forms/questions. Once this conversion has taken place, the WebCommandDataEntry class calls the stored procedure (sp_AETC_Save_Main), passing the resulting XML.

2. Validation – once the posted data has been saved to the database, this class does Coverpage validation according to the validation rules defined in the file, validation.xml.

Any validation errors or warnings encountered are included in an XML document that is passed to the RenderDataEntry class through the property PageValidationXml.

Finally, the WebCommandDataEntry class calls the display() method of the RenderDataEntry class. RenderDataEntry uses a section-specific XSLT file to transform the SectionXml into an HTML form with form elements that are in the correct format to be processed with the PostBackParser class when the cycle resumes. If there are errors or warnings on that page, the

March 5, 2021 25

PageValidationXml is transformed using the global XSLT for displaying validation error messages.

3.3 AETC Validation

Overall Validation Process

The following steps describe the validation process:

• The user uploads the AETC PIF, FTCC PIF and ER file in the format of MS Excel or MS Access through the AETC Web Application Data Entry Cover Page. When the user clicks the Upload Files or Save option, a validation check is performed on uploaded files.

• The user can click View Status or Work Flow Status to check the status of the files that he or she uploaded. The files’ status is not set to “processed” until the files pass several validation checks.

• The validation report is displayed with Errors/Warnings along with the row number in which the validation error or warning occurred for each file type uploaded. If there are no errors and only warnings, the system allows the user to submit the report to change the workflow status to “submitted.” The user may correct the warnings as well before submission. If there are errors, the system will not allow the user to submit the report.

For some of the validations, the user need to enter a note in the notes section of the cover page to pass the validation.

Explanation of each Validation Process

There are three sets of validation performed on the uploaded files.

• Validation on saving

When the user uploads the file and clicks the Upload Files or Save button, a validation is performed in the web application to check:

o file name o fields inside the file depending on the file type o number of records in the file and the staff responsible for the submission

It also checks whether the file was able to upload and is not corrupt. If there is already a file existing with the same name, the process will change the status to overwritten. If the file is invalid the status will show as invalid file. If the file is uploaded correctly the workflow status changes to working and the file status is in-process.

• Windows service data parser validation

March 5, 2021 26

This is a continuous process where the validity of the data is checked. The date format, the valid value range are checked using xslt files that are passed as input parameter for the stored procedure that performs the validations and stores the errors/warnings into the table AETC_FileProcessLog. The detail parser validation is performed using two parser xslt templates and the detail process of the parser template is discussed in a separate file XSLT Template Diagrams.vsd. After the parser validation check the file status changes to processed.

• Windows service XML validation

This is another continuous validation performed once the files are uploaded and parsed.

When the user clicks the View Status or Work Flow Status commands from the AETC Web Application, he or she can see the results of the validation or the validation request depending on whether the validation is already completed, or in process. The validation first checks whether there were any changes were made to the file after the last validation. If changes have been made since the last validation the process continues. If no changes have been made since the last validation the previous validation report is displayed. This validation checks the required value fields, checks null values and validates the related data element values. For example, the AETC PIF AETC number should match the cover page agency AETC number etc. It checks the values following the defined skip patterns. The detailed validation process is explained below. These error/warning results also go into the table AETC_FileProcessLog.

• Final Validation Report

The validation report is generated for each of the file type displaying the errors/warnings.

A drill down is available to see the exact record where the error was identified. The data is taken from the file AETC_FileProcessLog.

Detail Parser Validation

March 5, 2021 27 template name="ValidateData" Param: testDataType Param: testDataLength Param: testDataRange Param: warnFlag Param: childNodeTrunc template name="TestRequiredField" Param: fieldName Param: isWarningType template name="CreateChildElement" param: childElementName template name="ErrorHandling" param: isWarning param: questionNum param: valueEntered param: errorDesc param: recID

Main Templates used in the Parsing Process

March 5, 2021 28 xsl:call-template name="ValidateData" xsl:call-template name="ErrorHandling"

<xsl:copy-of select="."/><xsl:call-template name="CreateChildElement">

Otherwise

Otherwise

$testDataType

$testDataType = $number and

String-length(normalize-space(text()) > 0 string(number(text())) != NaN

$testDataType = $date string-length($childNodeTrunc) string-length($testDataLength) or string-length($testDataRange) string-length($testDataRange) &gt; 0string-length($testDataLength) &gt; 0

Otherwise warning = 1 Error = 0 Number = 1 Date = 2

Constant Variables

A

B C

ValidateData XSLT Template

ErrorHandling Template Call

Template Call

Choose .. When stmts & Process

Diagram Colors

March 5, 2021 29

<xsl:copy-of select="."/>

Otherwise

$testDataType = $date string-length($dateField) != 0

"substring(edate:difference($startDate, $dateField), 1, 1) != '-' and substring(edate:difference($dateField, $endDate), 1, 1) != '-'"

Otherwise

"string-length($testDataLength) or string-length($testDataRange)" xsl:call-template name="ErrorHandling" warning = 1 Error = 0 Number = 1 Date = 2

Constant Variables xsl:call-template name="ErrorHandling"

Otherwise

A

ErrorHandling Template Call

Template Call

Choose .. When stmts &

March 5, 2021 30 xsl:call-template name="ErrorHandling"

<xsl:copy-of select="."/><xsl:call-template name="CreateChildElement">

Otherwise

Otherwise

$normalizedTestLength string-length($testDataRange) string-length($childNodeTrunc) string-length($testDataLength) &gt; 0

Otherwise warning = 1 Error = 0 Number = 1 Date = 2

Constant Variables B

ErrorHandling Template Call

Template Call

Choose .. When stmts &

March 5, 2021 31 xsl:call-template name="ErrorHandling"

<xsl:copy-of select="."/>

<xsl:call-template name="CreateChildElement">

Otherwise string-length($testDataRange) &gt; 0

$testDataRange string-length($childNodeTrunc)

Otherwise warning = 1 Error = 0 Number = 1 Date = 2

Constant Variables C

ErrorHandling Template Call

Template Call

Choose .. When stmts &

CreateChildElement Template substring($childElementName, string-length($childElementName), 1) = '_'

Otherwise warning = 1 Error = 0 Number = 1 Date = 2

Constant Variables

ErrorHandling Template Call

Template Call

Choose .. When stmts & Process

Diagram Colors

Create an element with attributes

Create an element with attributes

March 5, 2021 32

ErrorHandling Template

Create an element like below with an error information

<ERROR>

<WARNING_FLAG> --- </WARNING_FLAG>

<DATA_ELEMENT> --- </DATA_ELEMENT>

<VALUE> --- </VALUE>

<ERROR_DESCRIPTION> --- </ERROR_DESCRIPTION>

<RECORD_ID> --- </RECORD_ID>

<INTERNAL_ID> --- </INTERNAL_ID>

</ERROR>

warning = 1 Error = 0 Number = 1 Date = 2

Constant Variables

ErrorHandling Template Call

Template Call

Choose .. When stmts &

Detail XML validation

Check 1

Check to see if there are any changes in the current AETC Report after previous validation

Yes Perform Validation Process

Get the status of the AETC Report

No

March 5, 2021 33

Check2

Check to make sure that the file has the correct number of fields as required by the file type either PIF/ER

Fail

Error Message

Check to make sure that there are no all NULL values for any of the data element

Pas s

Fail

Continue Validation Process

Pas s

AETC PIF – 27 data elements PIF_ID, PIFDATE, PIF3, PIF4, PIF5, PIF6a, PIF6b, PIF7, PIF8a, PIF8b, PIF9, PIF10_1, PIF10_2, PIF10_3, PIF10_4, PIF10_5,PIF11, PIF12_1, PIF12_2, PIF13, PIF14, PIF15, PIF16, PIF17, PIF18, PIF19, AETC, LPS, PROG_ID, RWFUND

FTCC PIF – 55 data elements

CRSDATE, STUID, PROFD, FUNR, EMPS, PROGF1, PROGF2, EMPS2, EMPSZ, EMPFB, FUNRW,

FUNFP, FUNCDC, FUNSAM, FUNMAI, AGN, RACET, RACAMI, RACASN, RACAA, RACHL, RACHPI,

SPECP, SPECAD, SPECHI, SPECHM, SPECIP, SPECLI, SPECMSM, SPECMMW, SPECOA, SPECPW,

SPECIRM, SPECSW, SPECSU, SPECT, SPECW, SPOTH, HISPL, PRACAMI, PRACASN, PRACAA,

RACHL, PRACWH, GENDER, CLIENTS/PATIENTS, POPMINS, POPHIV, SERHIV, YRSRHIV,

AVGMOHIV, POPRAM, POPCO, POPAATH, POPWN

ER – 95 data elements ERDATE, ERNAME (not required), LOCZIP (not required), ER4_1, ER4_2, ER4_3, ER4_4, ER4_5, ER4_6, ER4_7, ER4_8, ER4_9, ER4_10, ER4_11, ER4_12, ER4_13, ER4_14, ER4_15, ER4_16, ER4_17, ER4_18, ER4_19, ER4_20, ER4_21, ER4_22, ER4_23, ER4_24, ER4_25, ER4_26, ER4_27, ER4_28, ER4_29, ER4_30, ER4_31, ER4_32, ER4_33, ER4_34, ER4_35, ER4_36, ER4_37, ER4_38, ER4_39, ER4_40, March 5, 2021 34

ER4_41, ER4_42, ER4_43, ER4_44, ER5_0, ER5_1, ER5_2, ER5_3, ER6_0, ER6_1,

ER6_2, ER6_3, ER6_4, ER6_5, ER6_6, ER6_7, ER6_8, ER6_9, ER6_10, ER6_11,

ER6_12, ER6_13, ER6_14, ER6_15, ER6_16, ER6_17, ER6_18, ER6_19, ER6_20,

ER6_21, ER6_22, ER6_23, ER6_24, ER6_25, ER6_26, ER6_27, ER6_28, ER6_29,

ER6_30, ER7, ER8, ER9_1, ER9_2, ER9_3, ER9_4a, ER9_4b, ER9_5, ER10_1, ER10_2, ER10_3, ER10_4, ER10_5, ER10_6, ER10_7, ER10_8, ER10_9,AETC, LPS, PROG_ID

Stored Procedure : sp_AETC_saveNullValidationFromXml

Check 3

Run the XML validations No Error/Warning Generate Report

Call stored procedure SP_AETC_SaveFinalValidationFromXML_2008 to save the Error/Warning record into AETC_FileProcessLog_2008 table by passing the validation XML String

Er ror s/W arn ing s

Stored Procedure SP_AETC_SaveFinalValidationFromXML

• Delete the old records for that AETC record whose log Entry Type is SaveCoverPage, FinalValidation or SaveLPS.

• Write the errors/warning from XMLValidationString into AETC_FileProcessLog table.

• Update AETC_Report table to modify the lastValidatedDate.

Generating XML Validation String

The following steps describe how the XML is generated for validation:

March 5, 2021 35

• Check skip pattern and eliminate validation for those dataelements that are skipped.

• Get all the Error Codes that need to be Validated.

• Validate each of the error codes.

• Each error code can have a unique validation type and compare type.

• Validation type:

Required, Interdependent, Equal or Compare

• Compare type:

Equal, greater, greaterOrEqual, less or lessOrEqual

• Depending on the ValidationType different functions are called by passing in the parsed

SQL statement from the XML validation XML.

• A validation error XML string is generated as the output. A sample XML validation string is shown below:

Sample XML Validation String

<ErrorReport scope='report'> <Sections><section sectionNum='1' /></Sections> <Error checkCode='30' type='compare' compareType='greaterOrEqual' checkType='Error' checkTypeNum='0'> <!--Note- New validation check per defect #97. At least one file must have been uploaded and processed.--> <Question section="1" number="7" /> <overrideErrorMessage> At least one file must be uploaded and processed for this AETC Report.

</overrideErrorMessage> <LogEntryTypeID>1</LogEntryTypeID> </Error> </ErrorReport>

Generate Validation Report

Step 1: Run the query to get the CoverPage and LPS Data errors. This query will retrieve the following in one row as per the comments

1. The AETC Report Status

2. The count of LPS currently existing in the db for this AETC Report.

March 5, 2021 36

3. The count of errors for CoverPage.

4. The count of warnings for CoverPage.

5. The count of errors for LPS Page.

6. The count of warnings for LPS Page.

Step 2: Run the query to get the status info related to the particular AETC Reports files (ER, PIF). This query will retrieve the 2 different file types with the following details as per the comments

1. The file Type (AETC PIF, FTCC PIF, ER)

2. The Status of the most current file of each type (i.e., not overwritten) for the given Report ID.

3. The count of validation ERRORS from aetc_fileProcessLog db table.

4. The count of distinct (errordescription + dataElement) for ERRORS from aetc_fileProcessLog db table.

5. The count of validation WARNINGS from aetc_fileProcessLog db table.

4. The count of distinct (errordescription + dataElement) for WARNINGS from aetc_fileProcessLog db table.

Step 3: WindowsService Validation Check Check for errors, warnings, and unprocessed files for each of the File Types (AETC PIF, FTCC PIF, ER) associated with the report. The user will not be able to submit the report if any of the files have not been processed yet by the back-end windows service, because the service may flag some errors or warnings associated with the file.

3.4 AETC Dataset Generation

There are two utilities involved to generate the AETC Dataset. The following section describes how the AETC dataset gets generated:

• CopyFiles Utility o This utility copies Excel and Access data files that are processed (file status

=”3”), for the specified report year from specified source file location to specified destination location. The files that are now in the destination location are the files that are used to create the AETC combined dataset using the ReadSPSS utility which is discussed below.

March 5, 2021 37

RCK-Server

Local Machine

Copy Excel/Access good files

• ReadSPSS Utility o This utility reads the records from Excel or Access data files into a reader using SQL command. From the SQL reader records are written one by one into CSV file (comma separated file). There are multiple CSV files created for the ER, AETC PIF and FTCC PIF records. At least three CSV files will be created; one for ER, one for AETC PIF and one for FTCC PIF records. Additional CSV files may be created if the ER, AETC PIF or FTCC PIF records exceeded 65535 because Excel has a record limitation of 65535 records per sheet.

o From the CSV files data are then copied into Excel files. The final result of this utility will be three Excel files; one for AETC PIF, one for FTCC PIF and another one for ER. Finally some data columns, such as PIFDATE, ERDATE, and zip code, will be formatted to the corresponding data type.

Multiple PIF/ER Files in Excel/Access

Format

Combined to form one PIF and one ER file in the Excel Format

Dataset Generation Utility

3.5 Middle-Tier Classes

The Middle-Tier classes include Business Process and Utility classes (Appendix B: Class Association Diagrams).

BizProcess:

• AetcNavigation

• AetcValidation

• AetcXmlCoverGenerator

• AetcXmlGenerator

• PrintAETC

Utils:

March 5, 2021 38

• AETCAuthenticator

• AETCDeliverable

• AETCReport

• AETCRequestHandler

3.5.1 BizProcess Classes

The AETCXmlGenerator class is responsible for generating an XML document that contains all elements for display on a given AETC data entry page. When a request comes into the WebCommandDataEntry class to display a page, the web command class creates an AETCXmlGenerator object, passing it the AETC ID, user information and page number through the ReportContext object. The AETCXmlGenerator uses the AETC_ReportID to determine the grantee information to be displayed on the Data Entry page. Then, using the mappings defined in the file QuestionToXmlMap.xml, it executes database queries, converting the results into the correct XML format. The XML configuration file (QuestionToXmlMap.xml) includes an element for every question in the AETC form. Within each question element, the file details the tables, fields and lookups to be queried in each case. It also indicates where within the output XML format to put the results of these queries. This results in an XML format that the various XSLT files are able to read and display to the user.

Based on the XML from the AETCXmlGenerator, the XSLT data entry files create HTML forms with form elements that follow the rules outlined below. After each data entry form is posted back to the server, WebCommandDataEntry object creates a PostBackParser (hab.core) object, which takes the names and values of all form elements (text boxes, checkboxes etc.) that will be saved into the database. The PostBackParser finds the form elements, converts them and passes the resulting XML string to a stored procedure to be saved in the database.

The Validation class (hab.core) is responsible for testing whether the data entered conforms to predefined data validation rules. There are two modes in which the Validation class can be invoked: page-level validation and report-level validation. Page level validation happens when the page is saved to the database. After saving the page data, the WebCommandDataEntry class creates a Validation object, passing it the AETC ID, user information and an indication of whether page or report level validation should be run.