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

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

Revisions

Table of Contents

11.0 Introduction

11.1 Background

21.2 Purpose and Scope

31.3 System Overview

31.4 Document Organization

41.5 Change Control

41.6 Target Audiences

41.7 Assumptions

41.7.1 Environments, Setup and Maintenance

51.8 References

52.0 System Architecture

52.1 Overview

82.2 External Interfaces

132.3 Hardware Architecture

152.4 System Software Architecture

163.0 Software Design

163.1 Web Request Framework

203.1.1 AETCHandler Class

213.1.2 Web Command Factory

213.1.3 Web Command Objects

223.1.4 BizProcess (Business Process) Objects

223.1.5 Utils (Utility) Objects

223.1.6 Renderer Objects

233.1.7 Biz (business) Objects

233.2 AETC Data Entry Forms Request Flow

253.3 AETC Validation

363.4 AETC Dataset Generation

373.5 Middle-Tier Classes

383.5.1 BizProcess Classes

393.6 Code Base

403.6.1 AETCApp Namespaces / Directories

414.0 Database Design

414.1.1 Data Dictionary

414.1.2 Static and Dynamic Data

415.0

42Appendix A: Naming Conventions

43Appendix B: Class Association Diagrams

431.

Class Diagram – AETCBase and Renderer

452.

Class Diagram – Utils

463.

Class Diagram – WebCommand Namespace

474.

Class Diagram – Parsing Parser

485.

Class Diagram – Validation Parser

49Appendix C: Sequence Diagrams

491.

Sequence Diagram - Home Page

List of Figures

6Figure 1 - Architecture Overview

9Figure 2 - External Interfaces

10Figure 3 - EHB Integration Server Architecture

11Figure 4 - Access Mode

14Figure 5 - Environments

18Figure 6 - Web Request Framework

20Figure 7 - Component Model

21Figure 8 - Web.Config - Custom Handler Implementation

24Figure 9 - Web Request - Data Entry

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

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.

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.

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.

Figure 1 - Architecture Overview

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

Figure 2 - External Interfaces

Figure 3 - EHB Integration Server Architecture

Figure 4 - Access Mode

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.

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)

• 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

2.4 System Software Architecture

The AETC Web Application consists of the following software components:

· Microsoft Internet Information Server 7.0

· world wide web (www) server

· 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 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:

· 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.

Figure 6 - Web Request Framework

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.

SHAPE \* MERGEFORMAT

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).

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 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).

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 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:

· file name

· fields inside the file depending on the file type

· 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

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

Detail XML validation

Check 1

Check2

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, 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

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:

· 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.

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

· 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.

· ReadSPSS Utility

· 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.

· 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.

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:

· 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. Report level validation queries validation rule definition file to get all validation checks, rather than narrowing it down like in page-level validation. Then, using the checks defined in the file Validation.xml, it executes database queries, to check whether the data is valid. The XML configuration file (Validation.xml) includes an element for every validation check the system performs. Within each validation check element, the file details the relevant questions, database queries and error text for each case. This result is an XML document that lists all validation errors found. Only validation errors (not warning) will be displayed during page-level validation. The system will display both validation errors and warnings when it performs report-level validation.

The AETCWorkflow class tracks the status of an AETC as it is submitted, rejected, accepted, etc.

Figure 8: Workflow Flowchart

3.6 Code Base

The AETC Web Application is a .Net application. The code includes:

· C# classes

· ASP.Net forms (only as a conduit with all the code for generating output in the C# ASP.Net codebehind classes)

· xml files

· xslt files

· javascript (.js) files

· html files

· help files

· SQL Server Database Code:

· Stored procedures

· Views

· User Functions

· Triggers

The code base is comprised of five projects (applications):

· AETCApp – this is the main web application

· RegLoginApp – this web application has the code for registering users to the site and logging into the AETCApp application

· Hab.core – this is a separate dll which most of the common reusable modules have been abstracted into

· AETCParserFiles – Parses the uploaded data and saves into the database

· AETCValidation – Validates the uploaded data

3.6.1 AETCApp Namespaces / Directories

The AETCApp Namespace contains the following classes:

· BizProcess

· classes used by the WebCommand to perform business processes:

· validation

· navigation

· workflow

· xml generation

· from database to form display xml

· from form elements to database storage xml

· BizObject

· Classes model entity object in AETC applications like AETC Report

· AETCBase

· base classes

· Configs

· Web.config and other files that need to be deployed to the various environments

· Help

· AETCHelp.chm

· various html files used for help

· Images

· Images for use throughout the site

· js

· javascript files

· Logs

· Log files are written here by the application

· Renderer

· renderer classes

· Test

· Any classes, forms, etc. that developers create for testing purposes can go here

· UI

· aspx files

· AETCHandler.cs

· Utils:

· General utilities

· WebCommands

· web command classes

· XML

· xml files

· XSL

· xslt files

· css files

4.0 Database Design

The database design is shown in detail in the following document.

4.1.1 Data Dictionary

A data dictionary is included and attached as a separate document. The file is called, AETC2012DataDictionary.docx 4.1.2 Static and Dynamic Data

All the file names in the Data Dictionary ending with Lkup and Rel stores static data and rest of the tables store dynamic data.

5.0 External Interfaces

A document describing the design of the EHB Integration Service (EIS) is included and attached as a separate document. The file is called EIS Design.doc Appendix A: Naming Conventions

Method Param iMyInput oMyOutput ioMyBoth

Private member to be exposed

Get/set method or use property

Var scope

No rules

Var type

No rules folder name first intial capital

Appendix B: Class Association Diagrams

1. Class Diagram – AETCBase and Renderer

Class Diagram – BizObject and BizProcess

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

<?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>

PAGE

_1344250255.vsd

Data�

Handler�

3. Pass Request to Web Command�

1. Web Request�

4a. Use BizProcess Classes�

WebCommandFactory�

2. createWebCommand(CommandName)�

WebCommand�

4b. Use Utils Classes�

BizProcess�

Utils�

Database�

Renderer�

5. Send Information for Output (generally as

XML)�

6. Web Response�

XSLT�

Web Request Framework�

_1344250263.vsd xsl:call-template name="ErrorHandling"

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

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

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

$testDataRange string-length($childNodeTrunc)

ErrorHandling Template Call

Template Call

Choose .. When stmts & Process

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

Constant Variables

C

_1344250267.vsd 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

Pass

Fail

Continue Validation

Pass

_1344250269.vsd

_1344250271.vsd Yes

No

Deadline Passed?

AETC Record

Status: final Locked by: None

AETC Data Submission Workflow

Status: working Locked by: Grantee

1. AETC Grantee Creates Record

2. AETC Grantee Modifies Main (Cover Page) Record

3. AETC Grantee Uploads (or re-uploads) Data Files

5. AETC Grantee Submits Data to HAB

Validation Errors?

Yes -user cannot submit

Status: submitted Locked by: None

Validation Errors in File?

3B. Reject File

3A. Accept File

3C. Windows service Process Parses Uploaded File

4. Windows service Validates Submission

_1344250272.vsd Sequence

Sequence

WebCommandFactory Singleton : AETCBase::WebCommandFactor�

Browser Request aetcApp : UI::AetcHandle�

WebCommand - Home Page : WebCommands::WebCommandHomePag e

Renderer Home Page : Renderer::RenderHomePage

Request Data Entry Page: handleRequest(ctx)

HTTP Context handleRequest verifyLoggedIn createWebCommand execute

UserFullName

Username

UserRoles display displayHeader displayBody

Response.Write() displayFooter

Simple Request - Home Page

HTTP Response

_1344250270.vsd Multiple PIF/ER Files in Excel/Access Format

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

Dataset Generation Utility

_1344250268.vsd 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

Errors/Warnings

_1344250265.vsd 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>

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

Perform Validation Process

Get the status of the AETC Report

_1344250264.vsd CreateChildElement Template

Create an element with attributes

Create an element with attributes substring($childElementName, string-length($childElementName), 1) = '_'

_1344250259.vsd 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

_1344250261.vsd xsl:call-template name="ValidateData"

A

"string-length($testDataLength) or string-length($testDataRange)"

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

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

_1344250262.vsd

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

B

_1344250260.vsd

Error = 0 string(number(text())) != NaN

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

$testDataType = $number and String-length(normalize-space(text()) > 0

$testDataType

A

B

C

ValidateData XSLT Template

_1344250257.vsd AETC File Handler

AETC High Level Component Model

Web Browser

AETCWebCommand

«subsystem»

EHB

EHBAuthentication

AETC DataAccess

AETC BizProcess

_1344250258.vsd Data

Handler

WebCommandFactory

2. createWebCommand(”DataEntry”)

4. AetcXmlGenerator

3. Navigation

WebCommandDataEntry

BizProcess

Utils validation.xml

2. Validation

1. PostBackParser questionToXmlMap.xml

3. Pass Request to Web Command execute(HttpContext)

4a. Use BizProcess Classes

4b.…