Attachment_2_-_Specifications.pdf

Bureau of Indian Education (BIE) United States Department of the Interior

(DOI)

System Requirements Specification

WEBET_SRS_V1.00_2023-01-23

Web Education Transportation (WebET) Solution

Requirements Document: WebET Transportation Software

01/23/2023 Bureau of Indian Education WebET Reverse Engineering 2 of 139

REVISION HISTORY

Version Date Author Change Description

0.01 11/30/2022 Navancio, LLC. Beta Draft

0.02 1/19/2023 BIE Reviewers Updated from BIE document reviewers incorporated

1.00 1/23/2023 BIE Reviewers & Navancio, LLC. Final initial version

1.01 1/28/2023 BIE Signatories & Navancio, LLC.

Grammatical errors, punctuation, and minor reformatting. No content changes.

01/23/2023 Bureau of Indian Education WebET Reverse Engineering 3 of 139

AUTHORIZING SIGNATURES

System Requirements Specification Review

Contract Officer Representative (COR) Name: Jake Coury

Signature:

Date:

Project Manager (PM) Name: Kristen Benedetto

Signature:

Date:

Contract Officer (CO) Name: Darren Nutter

Deputy Associate Chief Information Officer (DACIO) Indian Education Name: Stuart Ott

Deputy Director, School Operations-BIE Name: Sharon Pinto

JAKE COURY

Digitally signed by JAKE COURY Date: 2023.01.31 09:47:42 -05'00'

KRISTEN BENEDETTO Digitally signed by KRISTEN BENEDETTO Date: 2023.01.31 15:58:38 -07'00'

DARREN NUTTER

Digitally signed by DARREN

NUTTER

Date: 2023.02.02 12:24:28 -06'00'

STUART OTT Digitally signed by STUART OTT Date: 2023.02.03 07:57:58 -05'00'

02/07/2023

01/11/2023 Bureau of Indian Education WebET Reverse Engineering 4 of 139

Table of Contents

1. Introduction

1.1 Purpose

1.2 System Overview

1.2.1 WebET Overview

1.2.2 Excel Tool Overview

1.3 Stakeholders

2. Assumptions and Constraints

2.1 Assumptions

2.2 Constraints

3. Context Diagram

3.1 WebET Flow Diagrams

3.2 Excel Flow Diagrams

4. Functional Requirements

4.1 WebET Functional Requirements

4.1.1 WebET Use Case Overview

4.1.2 WebET Use Case Survey

4.1.3 WebET Use Cases

4.2 Excel Spreadsheet Functional Requirements

4.2.1 Excel Spreadsheet Use Case Overview

4.2.2 Excel Spreadsheet Use Case Survey

4.2.3 Excel Spreadsheet Use Cases

5. Technical Requirements

5.1 Hardware Requirements

5.2 Software Requirements

5.2.1 Hosting ……………………………………………………………………………………..19

5.2.2 Database

5.2.3 Development

5.2.4 Excel Tool

5.3 Performance Requirements

5.4 Security Requirements

5.4.1 Operating System Security

5.4.2 User Level Security

5.5 Availability Requirements

Appendix A – Acronyms, Abbreviations and Definitions

Appendix B – Source Material

Appendix C – WebET Use Cases C.1 Review/Edit/Certify School Data

C.1.1 Edit Transportation Data C.1.2 View/Print Transportation Certification Report C.1.3 Certify/Decertify Transportation Report C.1.4 Enter/Edit Transportation Cost Detail Data

WebET Reverse Engineering 5 of 139

C.2 Edit Code Tables and School Profiles C.2.1 Modify School Profiles C.2.2 Modify Line Offices

C.3 Security Maintenance C.4 Bureau-Wide Reports

C.4.1 Transportation Summary Report C.4.2 Transportation Cost Summary Report

C.5 Create Interface Files C.6 Logoff C.7 Select a School Year C.8 Select a School

Appendix D – Excel Document Use Cases D.1 Update Current and Previous Year Data

D.1.1. Update Appropriated Transportation Funding D.1.2. Update Previous Year Funding D.1.3. Update Reimbursable Data D.1.4. Update Mileage Data

D. 2. Produce Allocation Distribution Document D. 3. Maintain Metadata, Mileage Formulas and Bus Routes

D.3.1 Maintain Metadata D.3.2 Maintain Mileage Formulas

D. 4. Maintain The Green Book D.4.1 Mileage Data Tab D.4.2 Bus Route Totals Tab D.4.3 Mileage Calculations Tab D.4.4 School Payments Tab D.4.5 Summary Tab D.4.6 Greenbook Appendix Tab D.4.7 Reimbursables Data Tab D.4.8 Reimbursables Tab D.4.9 Mail Merge Tab D.4.10 Tables Tab D.4.11 Policies Tab

Appendix E – Data Entities E.1 Busses Entity E.2 Busses_Supplemental Entity E.3 Bus_Routes Entity E.4 DB_SysInfo Entity E.5 LineOffices Entity E.6 Schools Entity E.7 SchoolTypes Entity E.8 Transportation_Status Entity E.9 Trans_Charters Entity E.10 Trans_Commercial Entity E.11 Trans_Drivers Entity E.12 Trans_Fixed Entity E.13 Trans_Lodging_Staff Entity

WebET Reverse Engineering 6 of 139

E.14 Trans_Lodging_Students Entity E.15 Trans_Maintenance Entity E.16 Trans_Other Entity E.17 Trans_Variable Entity E.18 Users Entity

01/28/2023 Bureau of Indian Education WebET Reverse Engineering 7 of 139

1. Introduction The Web Education Transportation (WebET) application is a component of the Web Indian Student Equalization Program (ISEP) application which has been in production since 2005. The WebET application is used yearly by the 53 Bureau of Indian Education (BIE) Federal Operated Schools and the 130 Tribally Controlled Schools under 25 CFR § 39.700. The WebET application is used yearly starting September 1 and ending December 1 of each year. The WebET application calculates grant funding allocations using 25 CFR § 39.710. The yearly reporting is a requirement under 25 CFR § 39.700 as yearly grant money for education transportation costs is dispersed based on the individual school reporting.

The Excel Student Transportation Funding Tool (Excel Tool) is an 11 sheet Microsoft Excel workbook designed to calculate funding distributions based on the Student Transportation Funding Formula. Input into this workbook comes from WebET and is augmented with data from the financial system and occasional updates to school meta data, reference tables, and a link to policy data.

1.1 Purpose

The purpose of the System Requirements Specifications (SRS) document is to provide business requirements to be used to support the re-engineering of a technical solution with matching functionality of the existing solution. The SRS records the formal business requirements for the WebET system. The SRS defines what the potential technical solution must do to support the business owner and system owner needs. The specific objectives for this SRS are to:

Identify the business requirements for individuals who use WebET (Business Owners) and the Excel Tool

Identify the existing functions of the current WebET solution through reverse engineering

Identify the existing functions of the current Excel Tool solution through reverse engineering

Identify system data exchanges If appropriate, identify where new business processes are needed and / or if existing business process may require modifications Identify what business / system results are needed

The framework for the effort is the project scope and objectives, business requirements documents, and information gathered from the business owner and other stakeholders regarding desired system functionality and performance. Functional requirements answer the following questions:

How are inputs transformed into outputs?

Who initiates and who receives specific information?

What information must be available for each function to be performed?

WebET Reverse Engineering 8 of 139

1.2 System Overview

The solution provided is a combination of the WebET system and the Excel tool. Both are required to provide the full functionality needed by the users and the program.

1.2.1 WebET Overview

The WebET system is an Active Server Pages (ASP) 2.0 web application leveraging the power of an ASP based architecture utilizing the .NET framework and SQL Server. The application is presented to the user using BIE approved browsers (i.e., Internet Explorer, Chrome, and Edge) and utilizes a Microsoft SQL Server 2016 database back-end. There are two separate instances of the WebET webservers. The first instance is the Federal end user's internal server. The second WebET instance is the external server. There is a long-term solution project to replace the WebET application which will be based on the current WebET application.

This application resides on approved and authorized BIE technology platforms that exist in the Indian Affairs (IA) Data Center located in Albuquerque, New Mexico. The public WebET hosting server is running Internet Information Server (IIS) version 10, with ASP 2.0, operating on a Windows 2019 server. The backend database server is SQL Server 2016 running on a Windows 2019 server.

The trust side WebET hosting server is running Internet Information Server (IIS) version 8.5, with ASP 2.0, operating on a Windows 2012R2 server. The backend database server is SQL Server 2016 running on a Windows 2016 server. The WebET application collects education transportation data from Federal Operated Schools and from Tribally Controlled Schools. The WebET external transportation data is currently collected once a year during the last full week in September. The application uses a manual download process and is sent via BIE Exchange server to the Internal Federal WebET server for merging into consolidated reports which feeds into an internal BIE business process where the WebET reports drive the individual school grants.

Access controls are provided by the network, the application, and the SQL Server database.

1.2.2 Excel Tool Overview

The Excel tool focuses calculations needed to generate the allocation distribution amounts for each school.

There are five (5) primary outputs:

WebET Reverse Engineering 9 of 139

1. Reimbursables are calculated

2. School payments are prepared for use in mail merge and distribution of funds for each school

3. Performing the mail merge

4. Allocation distribution document

5. Financial System excel upload file

6. Provide information for maintenance of “The Green Book”

Inputs providing data come from the following:

1. WebET: generation/export of the Milage Data Report

2. Reimbursable Form provides the reimbursable data via email

3. Previous year’s funding data is provided from the financial system

4. Occasional updates to school meta data, reference tables, and/or the link to policy data

5. Office of Budget and Performance Management supplies the appropriated

Student Transportation funding value

1.3 Stakeholders

The stakeholders (points of contact) involved in the project, including the Business Unit Program Manager, Project Manager, and other key project personnel are:

Stakeholder Name Organization Project Role

Jake Coury BIE COR

Darren Nutter BIE CO

Stuart Ott BIE Deputy Associate Chief Information Officer (DACIO)-Indian Education

Sharon Pinto BIE Deputy Director, School Operations

Kristen Benedetto BIE Project Manager

Albert Rice BIA Information Systems Security Officer

Dick Davis BIA Software Engineer: SME – WebET

Mark Patterson BIA Contractor: SME – WebET

Dominic Aguilar BIE Budget Analyst: SME – Excel Tool

Huberta Lewis BIE Financial Analyst: SME – Excel Tool

Student Transportation Staff BIE Stakeholders

WebET IT support staff BIE Stakeholders

WebET Business Owners BIE Stakeholders

WebET System Administrators

BIE Stakeholders

WebET Reverse Engineering 10 of 139

WebET System Developers

BIE Stakeholders

2. Assumptions and Constraints

2.1 Assumptions

This document represents reverse engineered requirements from the existing state of

WebET and the associated Excel spreadsheet. WebET and the Excel spreadsheet are currently in use at BIE. It is assumed that the current infrastructure and the systems with which WebET interfaces continue to persist in their current state.

Users will have a reliable internet connection.

Users will have sufficient knowledge and skills to use the web application and spreadsheet.

2.2 Constraints

The scope of this project is limited to the WebET subsystem of the larger OIEP

MultiWeb application.

As described in the “Performance Work Statement WebET Student Transportation

System” document from May 2022, this SRS document represents the discoveries made from a reverse-engineering effort. Therefore, new feature requests and future requirements are not considered herein. However, where consistent and clear future functionality was described, we have included it.

BIE personnel were instrumental in the creation of this document as they carry with them institutional knowledge about the systems described here. While every attempt was made to extract and inscribe that knowledge herein, our collaboration and review processes may inadvertently omit some areas of functionality.

The application must be compatible with the Federal Government’s enterprise architecture, use approved technologies and meet the necessary regulatory requirements.

WebET Reverse Engineering 11 of 139

3. Context Diagram System Context Diagrams are used to represent the more important external factors interacting with the system at hand. The below figure shows how the WebET System, SysAdmin System, Excel Spreadsheet, and the User interact with each other and with other external systems of the OIEP MultiWeb Intranet. Arrows between systems represent generic data flow and are intentionally represented with either one or two arrowheads to represent one-way or bi-directional data flow. Please note that systems represented with dotted outlines are not within the scope of this SRS.

Figure 1: System Overview Context Diagram

3.1 WebET Flow Diagrams

The WebET application flow is shown in the following diagrams (Figures 2 -5). Decision points (i.e. – Menu options) are indicated with a diamond. A horizontal bar represents a parallel process dependent on the type of user authenticated with WebET. Variables passed between pages via URL query string are represented with double chevrons (e.g. -

<< VARIABLENAME >>).

Some flows are not represented in the diagrams below in order to simplify them. Most pages in the application have a link to logout.asp which is removed for visual clarity.

Many other pages have a link from the submenu to the parent menu.

WebET Reverse Engineering 12 of 139

Figure 2: WebET Flow - Initial Menus

WebET Reverse Engineering 13 of 139

Figure 3: WebET Flow - Bureau User Menus

WebET Reverse Engineering 14 of 139

Figure 4: WebET Flow - Cost Detail Menu

WebET Reverse Engineering 15 of 139

Figure 5: WebET Flow - BUS and Rate Menus

3.2 Excel Flow Diagrams

The Excel Tool process flow is shown in the following diagram (Figure 6). The diagram depicts the process flow of each use case and their associated external deliverables and actor interactions during the process. The three primary external deliverables manually produced via the Excel Tool outputs are:

1. Financial System’s excel template: manually produced from the Mail Merge export generated from the Excel Tool, this excel document it is used to upload the current transportation funding data in financial system.

WebET Reverse Engineering 16 of 139

2. Allocation Distribution Word template: manually produced from the Mail Merge export generated from the Excel Tool, this Word document is the formal document produced and distributed for each school’s yearly transportation allocation distribution.

3. The Green Book: manually updated from the ‘Green Book Appendix’ tab generated by the Excel Tool, the Green Book excel document is used externally by the Budget Formulation Team.

Figure 6 : Excel Tool

4. Functional Requirements

4.1 WebET Functional Requirements

4.1.1 WebET Use Case Overview

A use case is a written description of how users will perform tasks. It outlines, WebET Reverse Engineering 17 of 139 from a user's point of view, a system's behavior as it responds to a request. Each use case is represented as a sequence of simple steps, beginning with a user's goal and ending when that goal is fulfilled.

4.1.2 WebET Use Case Survey

Use Case

School User

ELO

User

Bureau User

1.1. Edit Transportation Data ✔

1.1.1. Add a Bus to the School Year ✔

1.1.2. Remove a Bus from the School Year ✔

1.1.3. Edit Bus Details ✔

1.1.4. Add or Edit Day Student Miles ✔

1.1.5. Add or Edit Residential Mileage ✔

1.1.6. Print Blank Day Student Mileage ✔

1.1.7. Print Blank Residential Student Mileage ✔

1.2. View/Print Transportation Certification Report ✔ ✔ ✔

1.3. Certify/Decertify Transportation Certification Report ✔ ✔ ✔

1.3.1. Certify Transportation Report ✔ ✔ ✔

1.3.2. Decertify a School ✔ ✔

1.3.3. Decertify a Line Office ✔

1.3.4. Decertify a Central Office ✔

1.4. Enter/Edit Transportation Cost Detail Data ✔ ✔ ✔

1.4.1. Modify GSA Vehicle Rates ✔ ✔ ✔

1.4.1.1. Edit Regular Vehicle Rates ✔ ✔ ✔

1.4.1.2. Edit Supplemental Fleet Rates ✔ ✔ ✔

1.4.1.3. Add Supplemental Fleet Vehicle ✔ ✔ ✔

1.4.1.4. Delete Supplemental Fleet Vehicle ✔ ✔ ✔

1.4.2. Modify Charter Transportation Costs ✔ ✔ ✔

1.4.3. Modify Non-GSA (Fixed) Vehicle Rates ✔ ✔ ✔

1.4.4. Modify Variable Vehicle Costs ✔ ✔ ✔

1.4.5. Modify Commercial Transportation Costs ✔ ✔ ✔

1.4.6. Modify Vehicle Maintenance, Service and Fuel Costs ✔ ✔ ✔

1.4.7. Modify Driver Costs ✔ ✔ ✔

1.4.8. Modify Student Meals and Lodging ✔ ✔ ✔

1.4.9. Modify Staff Per Diem and Lodging ✔ ✔ ✔

1.4.10. Modify Other Costs ✔ ✔ ✔

1.4.11. Print Transportation Cost Detail Report ✔ ✔ ✔

2. Edit Code Tables and School Profiles ✔

2.1. Modify School Profiles ✔

2.2. Modify Line Offices ✔

3. Security Maintenance ✔

4. Bureau-Wide Reports ✔

4.1. Transportation Summary Report ✔

4.2. Transportation Cost Summary Report ✔

5. Create Interface Files ✔

6. Logoff ✔ ✔ ✔

WebET Reverse Engineering 18 of 139

7. Select a School Year ✔ ✔ ✔

8. Select a School ✔ ✔ ✔

4.1.3 WebET Use Cases

WebET use cases are detailed in Appendix C – WebET Use Cases.

4.2 Excel Spreadsheet Functional Requirements

4.2.1 Excel Spreadsheet Use Case Overview

A use case is a written description of how users will perform tasks. It outlines, from a user's point of view, a system's behavior as it responds to a request. Each use case is represented as a sequence of simple steps, beginning with a user's goal and ending when that goal is fulfilled.

4.2.2 Excel Spreadsheet Use Case Survey

Use Case WebET Financial System

Budget Formulation

Team eCFR

Office of Budget and

Performance Management

Reimbursable

Form

1.1. Update Appropriated

Transportation Funding

1.2. Update Previous

Year Funding ✔

1.3. Update

Reimbursable Data ✔

1.4. Update Mileage Data ✔

2. Produce Allocation Distribution Document ✔

3.1. Maintain Metadata

3.2. Maintain Mileage

Formulas ✔

4. Maintain The Green Book ✔

4.2.3 Excel Spreadsheet Use Cases

Excel Spreadsheet use cases are detailed in Appendix D – Excel Document Use Cases.

5. Technical Requirements

5.1 Hardware Requirements

VMware NSXi version 6.7 for hosting of the front-end application and the back-end SQL server.

5.2 Software Requirements

WebET Reverse Engineering 19 of 139

5.2.1 Hosting

WebET requires Internet Information Server (IIS), with ASP 2.0. The WebET application also requires CRUD (Create/Read/Update/Delete) access to the file system in the folder in which it is hosted and executing in.

5.2.2 Database

WebET requires SQL Server 2016. The data entities are described in Appendix E

– Data Entities.

5.2.3 Development

Software developers desiring to build and debug the existing codebase will need development tools including Visual Studio, and a tool to inspect and/or modify database tables that is functionally equivalent to SQL Server Management Studio.

5.2.4 Excel Tool

Microsoft Office suite of products (including Word and Excel Macro Enabled Workbook).

5.3 Performance Requirements

The number of users is estimated at between 100 and 450 non-privileged users. The WebET application automates the collection of education transportation data from 53 Bureau Operated Schools and130 Tribally Controlled Schools. The WebET application collects the education transportation data which is then manually transferred to a BIE internal Excel spreadsheet. The spreadsheet does the calculations for the BIE Education Transportation Grant Funds allocations to the individual 183 schools.

5.4 Security Requirements

WebET Reverse Engineering 20 of 139

The WebET web server architecture is split between an Internal WebET server located at the ADC and External WebET server located at the EROS/SDC. The Internal WebET server is available for BIE federal and contractor end users. The WebET external server is utilized by Tribal Controlled Schools end users. The Tribal end users are vetted through BIE Personnel Security and must have a current background investigation, completed the security awareness training and testing, and IIS vetting.

The Internal ADC hosted WebET server, for federal end use, requires the user to be authenticated by the BIE AD Personal Identity Verification (PIV) card for access. This use of BIE AD and PIV card meet the requirements for Homeland Security Presidential Directive 12 (HSPD-12). The mandatory use of BIE AD and PIV card usage decreases the security risk level.

The External SDC BIE CPZ hosted WebET server, for Tribal Controlled Schools end users, will use first.last name with a DOI required minimum 12 character, upper, lower, case, number, and special character complex password for access to the application.

The end users' activity logs are reviewed weekly in accordance with NIST SP 800-53 Rev 5 AU-6.

5.4.1 Operating System Security

The WebET application must comply with DOI and Bureau security policies for Security Technical Implementation Guide (STIG) baselining, patching, security documentations, vulnerability scanning and remediation, and auditing.

The WebET Windows 2016 hosts are configured by default with the current STIG baseline via BIE AD group policies. The Microsoft back-end Windows 2016 servers are configured by default with the current STIG baseline via BIE AD group policies. Microsoft SQL 2016 database servers will be secured with the default OIMT STIG baseline as mandated by policy.

5.4.2 User Level Security

The following functionality is referred to as the “Security Subsystem” in Appendix C. The ability to view records, as well as perform various functions, will be dependent on roles assigned to the user.

Roles (also known as UserType):

School User = 0

ELO/ERC User = 1

Bureau Wide User = 2 Each page in the WebET application also contains:

A PageType value of 0, 1 or 2.

A PageExclusive value of True or False.

WebET Reverse Engineering 21 of 139

If PageExclusive = True, then the logged in user can only access the page if the UserType and PageType are an exact match. Otherwise, UserType must be equal to or greater than the PageType.

In the case of a School User, any pages that collect a Location Code parameter (SID) must match the School Location Code assigned to the User.

In the case of an ELO User, any pages that collect a Location Code parameter (SID) must be part of the same Line Office to which that User is assigned.

Finally, the Bureau User has the option (via bureausysadminmenu.asp) to set a minimum User access level for WebET and other, out of scope, OIEP MultiWeb systems. This will independently show or hide certain menu options depending on the UserType.

5.5 Availability Requirements

The system should be available 24/7, 365 except for identified maintenance windows.

WebET Reverse Engineering 22 of 139

Appendix A – Acronyms, Abbreviations and Definitions

Term Acronym or Abbreviation Description

Central Office User / Bureau User

BUREAUUSER Hierarchically one level above ELOUSER.

Central Office CO Code CD “Code” query string Count Week The week used to count mileage data. The last full week of September.

Definition: Transportation mileage count week from 25 CFR § 39.701 | LII / Legal Information Institute (cornell.edu)

Display Location Code DLC Education Budget Projections WebBP Early Childhood WebEC Education Line Office ELO / ERC An organizational unit that contains one or more Schools.

According to new regulations, ELO is now referred to as Education Resource Center

(ERC)

25 CFR Subpart G - Student Transportation | CFR | US Law | LII / Legal Information Institute (cornell.edu)

Education Line Office User / ELO User

ELOUSER Hierarchically one position above

SCHOOLUSER.

Education Transportation WebET Excel Tool Excel Student Transportation Funding Tool Indian School Equalization Program

WebISEP

Instructional I Instructional (Day Student Transportation) Location Code SID The code used for a School. Sometimes called a School ID.

Office of Indian Education Programs

OIEP

OIEP MultiWeb OIEP MultiWeb is the larger, out of scope context in which WebET exists. See the Context Diagram in Section 3 for more details.

Residential R Residential (Boarding/Dormitory Student Transportation)

Route Types Instructional (I) or Residential (R)

Security Subsystem Used to indicate the validation subroutine described in the Security Requirements section of the main document.

School User SCHOOLUSER School User (SCHOOLUSER) – Lowest level WebET system user.

School Year SYR School Year (SYR) – Budget year selected for data entry.

System Menu SysMenu System Administration SysAdmin User Used to generically indicate the personnel

WebET Reverse Engineering 23 of 139

(People) listed in Part A of the Actors section of the Use Case description. This can be one of the following: Bureau User, ELO User or School User.

Vehicle Identification Number VIN Unique identifier for a bus.

WebET Reverse Engineering 24 of 139

Appendix B – Source Material

The following documents and material were used as reference sources to create this SRS.

Name Filename Provider Usage WebISEP App Source Code

N/A BIE The source code was inspected and executed in a development environment to ensure coverage for all business requirements.

WebET Generated Excel File

N/A BIE The formulas and functionality of the Excel file were inspected to ensure coverage for all business requirements.

WebET demonstration WebET Demonstration- 20221026_130601-Meeting Recording

BIE Provided interactive Question & Answer session with recording for reference on application usage.

WebET Student Transportation Excel Tool Demonstration

WebET Student Transportation Excel Tool Demo-20221028_140522- Meeting Recording

BIE Provided interactive Question & Answer session with recording for reference on application usage.

Student Transportation Excel Tool – Empty Version

BIE ISEP Transportation Mileage Funding Tool Clean

BIE Provides a clean version for basis of reverse engineering.

Sample Excel tool Example_BIE ISEP Transportation Mileage Funding Tool

BIE Provides an example of real life data in excel tool for final version of reverse engineering

ISEP database diagram

ISEP_Data_Diagram BIE Diagram of full ISEP database of which WebET is a part.

ISEP database Diagram

ISEP_Data_Diagram_HighLev el

BIE Diagram of full ISEP database of which WebET is a part.

Mail Merge source data

ADD_ISEP_Trans_First_Merg e_File

BIE List of recipients for mail merge process in Excel Tool

Mail Merge template Document

FDD Student Trans Initial Distribution Template

BIE Document template for Mail Merge

Example Mail Merge FDD Student Trans Second Distribution Template

BIE Sample document produced by mail merge

Implementation and Test Plan for the Bureau of Indian Education (BIE) Web Education Transportation (WebET) Application v1.0

BIE WebET ITP v2.2 BIE Test plan for version 1.0 of WebET

WebET Reverse Engineering 25 of 139

Appendix C – WebET Use Cases

The WebET subsystem of OIAP WebISEP has the following areas of functionality:

1. Review/Edit/Certify School Data This is the primary area of data entry and activity for the WebET application.

2. Edit Code Tables and School Profiles

Allows the Bureau User to update the system data related to the WebET application.

3. Security Maintenance

Allows the Bureau User to manage WebET application Users.

4. Bureau-Wide Reports

Allows the Bureau User to produce multi-use reports from the transportation data.

5. Create Interface Files Allows the Bureau User to create and export system data files.

6. Logoff

Clear session variables and logout.

C.1 Review/Edit/Certify School Data

The Review/Edit/Certify process contains a short summary report of the certification status of the selected School and School Year in addition to menu options for the following areas of functionality:

C.1.1 Edit Transportation Data

Use Case Use Case ID 1.1 Use Case Edit Transportation Data (Bus Menu) Description Display summary information for the selected School, and School Year. Provide links to view reports and modify related data.

Actors A) People: School User

B) Other system(s): WebET

WebET Reverse Engineering 26 of 139

Pre-condition The Security Subsystem has validated the User.

A School has been selected.

A School Year has been selected.

Post-condition N/A Source file schoolbusmenu.asp

Use Case Diagram

Primary Use Case Flow of Events Step # Description

1 WebET checks mileage certification records in the Transportation_Status entity. If any of the following conditions are true, the user is redirected to the School Main Menu.

UserType = SCHOOLUSER and Cert_Status = "Certified" UserType = ELOUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Certified") UserType = BUREAUUSER and (Cert_Status = "Not Certified" Or ELO_Status = "Not Certified")

2 WebET checks mileage certification records in the Transportation_Status entity to display a certification status summary. WebET also displays a list of busses for the selected School and School Year, and their associated mileage entries, if any.

3 The submenu options for section 1.1, and the data compiled in the previous step are displayed to the User.

C.1.1.1 Add a Bus to the School Year

Use Case ID 1.1.1 Use Case Add a Bus to the School Year Description The User enters new vehicle information for the selected School and School Year, including VIN, Name and Capacity.

Actors A) People: School User

WebET Reverse Engineering 27 of 139

A School Year has been selected.

The School has not been certified for the selected School Year.

Post-condition A new bus has been added to WebET for the School and School Year.

Source file schoolbusadd.asp

1 WebET checks mileage certification records in the Transportation_Status entity. If any of the following conditions are true, the user is redirected to the School Main Menu.

UserType = SCHOOLUSER and Cert_Status = "Certified" UserType = ELOUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Certified") UserType = BUREAUUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Not Certified") 2 The User enters all data fields that are not system-generated or derived.

Derived fields:

LocationCode (captured from SID session variable) SchoolYear (captured from SYR session variable)

User-entered fields:

WebET Reverse Engineering 28 of 139

VIN

Bus Name (Busname) Capacity

3 WebET validates each data element in accordance with the following business rules.

VIN: Required Busname: Required VIN: VIN + LocationCode + SchoolYear must be unique.

4 WebET stores the new bus data in the Busses entity.

C.1.1.2 Remove a Bus from School Year

Use Case Use Case ID 1.1.2 Use Case Remove a Bus from School Year Description The User deletes a vehicle for the selected School and School Year.

Actors A) People: School User

B) Other system(s): WebET Pre-condition The Security Subsystem has validated the User.

A School has been selected.

A School Year has been selected.

The School has not been certified for the selected School Year.

Post-condition A bus has been deleted from WebET for the School and School Year.

Source file schoolbusdel.asp

1 WebET checks mileage certification records in the Transportation_Status entity. If any of the following conditions are true, the user is redirected to the School Main Menu.

WebET Reverse Engineering 29 of 139

UserType = SCHOOLUSER and Cert_Status = "Certified" UserType = ELOUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Certified") UserType = BUREAUUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Not Certified") 2 The vehicle information is presented to the User. The User confirms the displayed vehicle should be deleted.

3 WebET deletes the vehicle from the Busses entity.

C.1.1.3 Edit Bus Details

Use Case Use Case ID 1.1.3 Use Case Edit Bus Details Description The User edits bus information for the selected School and School Year.

Actors A) People: School User

B) Other system(s): WebET Pre-condition The Security Subsystem has validated the User.

A School has been selected.

A School Year has been selected.

The School has not been certified for the selected School Year.

Post-condition Bus information has been edited in WebET for the School and School Year.

Source file schoolbusedit.asp

WebET Reverse Engineering 30 of 139

1 WebET checks mileage certification records in the Transportation_Status entity. If any of the following conditions are true, the user is redirected to the School Main Menu.

UserType = SCHOOLUSER and Cert_Status = "Certified" UserType = ELOUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Certified") UserType = BUREAUUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Not Certified") 2 The User edits any of the following data fields:

VIN

Busname Capacity

3 WebET validates each data element in accordance with the following business rules.

VIN: Required VIN: VIN + LocationCode + SchoolYear must be unique.

Busname: Required Capacity

4 WebET stores the updated bus data in the Busses entity.

C.1.1.4 Add or Edit Day Student Mileage Record

Use Case Use Case ID 1.1.4 Use Case Add or Edit Day Student Mileage Record Description Add or edit a Day Student mileage record for a selected bus for a selected School, School Year, vehicle, and day of the Count Week.

Actors A) People: School User

B) Other system(s): WebET Pre-condition The Security Subsystem has validated the User.

A School has been selected.

A School Year has been selected.

The School has not been certified for the selected School Year.

Post-condition Day Student Bus route mileage information has been added or edited in WebET for the School, School Year, vehicle, and day of the week selected by the User.

Source file schoolbusmenu.asp & schoolbusweek.asp & schoolbusday.asp

WebET Reverse Engineering 31 of 139

1 WebET checks mileage certification records in the Transportation_Status entity. If any of the following conditions are true, the user is redirected to the School Main Menu.

UserType = SCHOOLUSER and Cert_Status = "Certified" UserType = ELOUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Certified") UserType = BUREAUUSER and (Cert_Status = "Not Certified" Or ELO_Status = "Not Certified")

2 The User selects the desired vehicle using the “Day Student Milage Recorded” column value (via schoolbusmenu.asp).

3 The User selects the desired day of the week (via schoolbusweek.asp).

WebET Reverse Engineering 32 of 139

Options are:

Monday (DAY = 2) Tuesday (DAY = 3) Wednesday (DAY = 4) Thursday (DAY = 5) Friday (DAY = 6)

4 The User enters all data fields that are not system-generated or derived. There are six entries available for morning (AM) routes and six entries available for afternoon (PM) routes.

Derived fields:

LocationCode (captured from SID session variable) SchoolYear (captured from SYR session variable) VIN (captured from VIN session variable) RouteDate (calculated via countweekdate function from DAY session variable) RouteType (hardcoded “I”) AMPM (“AM” for the first 6 routes or “PM” for the last 6 routes on the form) RouteNum (sequential 1 through 6 based on row numbers in each AM/PM section)

Route Name (RouteName) Odometer Start (OdometerStart) Odometer Stop (OdometerStop) Unimproved Miles (Unimproved_Miles)

5 WebET validates each data element in accordance with the following business rules.

Odometer Stop must be greater than Odometer Start Odometer Start must be greater than the Odometer Stop value for the previous route.

Unimproved Miles must not exceed total mileage for a route.

Unimproved Miles may contain up to 2 decimal positions.

If Odometer Start or Odometer Stop contain a decimal, WebET will round the number up if greater or equal to 0.5; down otherwise.

6 WebET stores the updated bus data in the Bus_Routes entity.

Future System Enhancements The Count Week is predefined as specified by Federal regulation as the last full week in September. BIE Staff desires the ability to define an alternate count week.

C.1.1.5 Add or Edit Residential Mileage Record

Use Case Use Case ID 1.1.5 Use Case Add or Edit Residential Mileage Record Description Add or edit a Residential mileage record for a selected bus for a selected School, School Year, vehicle, and day of the Count Week.

Actors A) People: School User

B) Other system(s): WebET Pre-condition The Security Subsystem has validated the User.

A School has been selected.

A School Year has been selected.

The School has not been certified for the selected School Year.

WebET Reverse Engineering 33 of 139

Post-condition Residential Bus route mileage information has been added or edited in WebET for the School, School Year, vehicle, and date selected by the User.

Source file schoolbusmenu.asp & schoolbusdayres.asp

1 WebET checks mileage certification records in the Transportation_Status entity. If any of the following conditions are true, the user is redirected to the School Main Menu.

UserType = SCHOOLUSER and Cert_Status = "Certified" UserType = ELOUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Certified") UserType = BUREAUUSER and (Cert_Status = "Not Certified" Or ELO_Status = "Not Certified")

2 The User selects the desired vehicle using the “Residential Student Milage Recorded” column value (via schoolbusmenu.asp).

3 The User enters all data fields that are not system-generated or derived. There are six entries available for morning (AM) routes and six entries available for afternoon (PM) routes.

WebET Reverse Engineering 34 of 139

SchoolYear (captured from SYR session variable) VIN (captured from VIN session variable) RouteType (hardcoded “R”) AMPM (“AM” for the first 6 routes or “PM” for the last 6 routes on the form) RouteNum (sequential 1 through 6 based on row numbers in each AM/PM section)

Residential Service Date (RouteDate) Route Name (RouteName) Odometer Start (OdometerStart) Odometer Stop (OdometerStop) Unimproved Miles (Unimproved_Miles)

4 WebET validates each data element in accordance with the following business rules.

Odometer Stop must be greater than Odometer Start Odometer Start must be greater than the Odometer Stop value for the previous route.

Unimproved Miles must not exceed total mileage for a route.

Unimproved Miles may contain up to 2 decimal positions.

If Odometer Start or Odometer Stop contain a decimal, WebET will round the number up if greater or equal to 0.5; down otherwise.

RouteDate must be a valid date.

RouteDate must occur in the currently selected School Year or the year prior to the currently selected School Year.

5 WebET stores the updated bus data in the Bus_Routes entity.

C.1.1.6 Print Blank Day Student Mileage Form

Use Case Use Case ID 1.1.6 Use Case Print Blank Day Student Mileage Form Description This form is used for manual data entry by bus drivers to capture Day Student

Mileage information. It provides fields to enter Route and Odometer information for each day of the Count Week.

Actors A) People: School User B) Other system(s): WebET

Pre-condition The Security Subsystem has validated the User.

A School has been selected.

A School Year has been selected.

The School has not been certified for the selected School Year.

Post-condition The report is printed and used by drivers in the field to manually enter Day Student Mileage data.

Source file schoolbusblankform.asp

WebET Reverse Engineering 35 of 139

1 WebET checks mileage certification records in the Transportation_Status entity. If any of the following conditions are true, the user is redirected to the School Main Menu.

UserType = SCHOOLUSER and Cert_Status = "Certified" UserType = ELOUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Certified") UserType = BUREAUUSER and (Cert_Status = "Not Certified" Or ELO_Status = "Not Certified")

2 WebET calculates the dates to use for the Count Week and populates them.

3 WebET displays the report to the User for printing.

WebET Reverse Engineering 36 of 139

Sample Report

C.1.1.7 Print Blank Residential Mileage Form

Use Case ID 1.1.7 Use Case Print Blank Residential Mileage Form Description This form is used for manual data entry by bus drivers to capture Residential

Mileage information. It provides fields to enter Route and Odometer information for a single day.

Actors A) People: School User B) Other system(s): WebET

Pre-condition The Security Subsystem has validated the User.

A School has been selected.

A School Year has been selected.

The School has not been certified for the selected School Year.

Post-condition The report is printed and used by drivers in the field to manually enter Residential Mileage data.

Source file schoolbusblankformres.asp

WebET Reverse Engineering 37 of 139

1 WebET checks mileage certification records in the Transportation_Status entity. If any of the following conditions are true, the user is redirected to the School Main Menu.

UserType = SCHOOLUSER and Cert_Status = "Certified" UserType = ELOUSER and (Cert_Status = "Not Certified" Or ELO_Status =

"Certified") UserType = BUREAUUSER and (Cert_Status = "Not Certified" Or ELO_Status = "Not Certified")

2 WebET displays the report to the User for printing.

C.1.2 View/Print Transportation Certification Report

Use Case ID 1.2 Use Case View/Print Transportation Certification Report Description This report lists the recorded Day Student Mileage, Residential Mileage, and

Transportation Cost entries for the selected School and School Year. It also shows mileage data entry errors.

Actors A) People: School User, ELO User and Bureau User

WebET Reverse Engineering 38 of 139

A School Year has been selected.

Transportation Data has been entered.

Post-condition The report is used to validate the accuracy of the data entry.

Source file schoolbusrptcertification.asp

1 WebET compiles the necessary data from the following entities:

Schools Busses Bus_Routes Transportation_Status

WebET validates the data.

2 WebET displays the report to the User including data entry errors.

WebET Reverse Engineering 39 of 139

Figure 7: Sample Certification Report without Errors

Figure 8: Sample Certification Report with Errors

C.1.3 Certify/Decertify Transportation Report

For a School User, the Certify/Decertify option is only available if the Transportation_Status entity has Cert_Status = NULL & ELO_Status = NULL & CO_Status = NULL.

For an ELO User, the Certify/Decertify option is only available if the Transportation_Status entity has Cert_Status = “C” & ELO_Status = NULL & CO_Status = NULL.

For a Bureau User, the Certify/Decertify option is only available if the Transportation_Status entity has Cert_Status = “C” & ELO_Status = “C”.

C.1.3.1 Certify Transportation Report

WebET Reverse Engineering 40 of 139

Use Case ID 1.3.1 Use Case Certify Transportation Report Description The User views a short summary of the certification status by School, ELO and CO.

The User may certify the selected School for the selected School Year.

Actors A) People: School User, ELO User, Bureau User

B) Other system(s): WebET Pre-condition The Security Subsystem has validated the User.

The active User has UserLevel = 1 (Data Entry and Certification) in the Users entity.

A School has been selected.

A School Year has been selected.

Post-condition WebET has recorded the User level certification for the selected School and School Year.

Source file schoolbuscertify.asp & elobuscertify.asp & bureaubuscertify.asp

1 WebET compiles the certification status information from the Transportation_Status entity.

2 WebET displays a summary of the certification status by User type including the following.

School User Certification Date (Cert_Date) ELO User Certification Date (ELO_Date) Bureau User Certification Date (CO_Date)

3 If the User selected “Certify” WebET stores the following information in the Transportation_Status entity for the selected School and School Year, depending on the active User type.

School User o Cert_Date = The current date.

o Cert_Status = “C”

ELO User

WebET Reverse Engineering 41 of 139 o ELO_Date = The current date.

o ELO_Status = “C”

Bureau User o CO_Date = The current date.

o CO_Status = “C”

Future System Enhancements A report with errors can still be certified. Disallow certification when errors exist on the certification report.

The name of the school is not shown on the certification report which could lead to incorrect certification. Show the school name on all certification reports.

C.1.3.2 Decertify a School

Use Case Use Case ID 1.3.2 Use Case Decertify a School Description The User views a short summary of the certification status by School, ELO and CO.

The User may decertify the School, ELO and CO certifications for the selected School and School Year.

Actors A) People: ELO User, Bureau User B) Other system(s): WebET

Pre-condition The Security Subsystem has validated the User.

The active User has UserLevel = 1 (Data Entry and Certification) in the

Users entity.

A School has been selected.

A School Year has been selected.

Post-condition WebET has modified the User level certification for the selected School and School Year.

Source file elobuscertify.asp & bureaubuscertify.asp

WebET Reverse Engineering 42 of 139

1 WebET compiles the certification status information from the Transportation_Status entity.

2 WebET displays a summary of the certification status by User type including the following.

School User Certification Date (Cert_Date) ELO User Certification Date (ELO_Date) Bureau User Certification Date (CO_Date)

3 If the User selected “Decertify” WebET stores the following information in the Transportation_Status entity for the selected School and School Year.

Cert_Date = NULL Cert_Status = NULL ELO_Date = NULL ELO_Status = NULL CO_Date = NULL CO_Status = NULL

C.1.3.3 Decertify a Line Office

Use Case ID 1.3.3 Use Case Decertify a Line Office Description The User views a short summary of the certification status by School, ELO and CO.

The User may decertify the ELO and CO certifications for the selected School and School Year. The School certification remains.

Actors A) People: Bureau User B) Other system(s): WebET

Pre-condition The Security Subsystem has validated the User.

The active User has UserLevel = 1 (Data Entry and Certification) in the

Users entity.

A School has been selected.

A School Year has been selected.

Post-condition WebET has modified the User level certification for the selected School and School Year.

Source file bureaubuscertify.asp

WebET Reverse Engineering 43 of 139

1 WebET compiles the certification status information from the Transportation_Status entity.

2 WebET displays a summary of the certification status by User type including the following.

School User Certification Date (Cert_Date) ELO User Certification Date (ELO_Date) Bureau User Certification Date (CO_Date)

3 If the User selected “Decertify” WebET stores the following information in the Transportation_Status entity for the selected School and School Year.

ELO_Date = NULL ELO_Status = NULL

C.1.3.4 Decertify a Central Office

Use Case ID 1.3.4 Use Case Decertify a Central Office Description The User views a short summary of the certification status by School, ELO and CO.

The User may decertify the CO certification for the selected School and School Year. The School and ELO certifications remain.

Actors A) People: Bureau User B) Other system(s): WebET

Pre-condition The Security Subsystem has validated the User.

The active User has UserLevel = 1 (Data Entry and Certification) in the

Users entity.

WebET Reverse Engineering 44 of 139

Post-condition WebET has modified the User level certification for the selected School and School Year.

Source file bureaubuscertify.asp

1 WebET compiles the certification status information from the Transportation_Status entity.

2 WebET displays a summary of the certification status by User type including the following.

School User Certification Date (Cert_Date) ELO User Certification Date (ELO_Date) Bureau User Certification Date (CO_Date)

3 If the User selected “Decertify” WebET stores the following information in the Transportation_Status entity for the selected School and School Year.

C.1.4 Enter/Edit Transportation Cost Detail Data The Enter/Edit Transportation Cost Detail Data process contains menu options for the following areas of functionality:

C.1.4.1. Modify GSA Vehicle Rates The Modify GSA Vehicle Rates process shows a summary of Regular and Supplemental Vehicle

WebET Reverse Engineering 45 of 139 details and contains menu options for the following areas of functionality:

C.1.4.1.1. Edit Regular Vehicle Rates

Use Case ID 1.4.1.1 Use Case Edit Regular Vehicle Rates Description The User edits vehicle rate information for the selected School, School Year, and

VIN.

Actors A) People: School User, ELO User, Bureau User

B) Other system(s): WebET Pre-condition The Security Subsystem has validated the User.

A School has been selected.

A School Year has been selected.

Post-condition Vehicle rates have been edited in WebET for the selected School, School Year, and VIN.

Source file schooltransrateregedit.asp

1 The User enters all data fields that are not system-generated or derived.

WebET Reverse Engineering 46 of 139

VIN (captured from VIN session variable)

Vehicle Rate per Month (Rate_per_Vehicle) Vehicle Rate per Mile (Rate_per_Mile) Vehicle Surcharge per Month (Vehicle_Surcharge) Vehicle per Mile Surcharge (Mileage_Surcharge) Total Mileage (Total_Mileage) Total Cost (Total_Cost)

2 WebET validates each data element in accordance with the following business rules.

VIN: Required Busname: Required VIN: VIN + LocationCode + SchoolYear must be unique.

Rate_per_Vehicle must be < 9999999.99.

Rate_per_Mile must be < 9999999.99.

Vehicle_Surcharge must be < 9999999.99.

Mileage_Surcharge must be < 9999999.99.

Total_Mileage must be < 9999999.99.

Total_Cost must be < 9999999.99.

3 WebET stores vehicle rate updates in the Busses entity.

C.1.4.1.2. Edit Supplemental Fleet Rates Use Case Use Case ID 1.4.1.2 Use Case Edit Supplemental Fleet Rates Description The User edits supplemental vehicle rate information for the selected School, School Year, and VIN.

Actors A) People: School User, ELO User, Bureau User

B) Other system(s): WebET Pre-condition The Security Subsystem has validated the User.

A School has been selected.

A School Year has been selected.

Post-condition Supplemental vehicle rates have been edited in WebET for the selected School, School Year, and VIN.

Source file schooltransratesupedit.asp

WebET Reverse Engineering 47 of 139

1 The User enters all data fields that are not system-generated or derived.

Derived fields:

LocationCode (captured from SID session variable)

VIN (captured from VIN session variable)

Vehicle Rate per Mile (Rate_per_Mile) Vehicle Surcharge per Month (Vehicle_Surcharge) Vehicle per Mile Surcharge (Mileage_Surcharge) Total Mileage (Total_Mileage) Total Cost (Total_Cost)

2 WebET validates each data element in accordance with…