Kentucky Transportation Cabinet: Materials Testing Web Application

1
Kentucky Transportation CabinetMaterials
Testing Web Application
2
Purpose

• Extract materials test data from Excel workbooks
  for mix designs and QC/QA processes.
• Mix Designs and QC/QA Workbooks are submitted by
  Contractors.
• Application allows for capturing of this data
  without hand entering in SiteManager.
• Validate the data.
• Transfer validated data into the SiteManager
  Database for analysis and payment adjustments.

3
(No Transcript)
4
Technologies

• The application uses a variety of technologies
  including
• Server Side
• ASP.NET
• C
• Client Side
• JavaScript
• Excel

5
Business Process

• KYTC is currently utilizing 8 separate
  disciplines
• 6 QC/QA Workbooks
• 2 Mix Design Workbooks
• Test data is collected on a variety of materials,
  which are categorized within the discipline.
• One or more test methods are utilized for each
  discipline.
• When complete, the workbook is reviewed by the
  designated authority and subsequently presented
  to the application for validation and data
  transfer.

6
Excel Workbook Design

• Test data is extracted from the Excel workbooks
  utilizing an internal XML schema map.
• To implement this feature, the XML schema map
  must be designed externally and attached to each
  workbook.
• The XML schema is based on SiteManager database
  structure. For simplicity, every utilized
  SiteManager table and its field were included in
  the schema design. This allows the one schema to
  work for all workbooks.

7
Excel Workbook Design (cont.)

• Workbooks are designed to match the XML schema
• One worksheet is added to the workbook for each
  SiteManager table needed.
• Data from the workbook is mapped to these
  table-oriented worksheets.
• Then, the XML schema is attached to the workbook,
  and its elements are mapped to the table-oriented
  worksheets.
• Concrete Pavement QC/QA Workbook

8
SiteManager Tables

• 10 Tables are used by the application
• t_smpl
• t_smpl_tst
• t_smpl_tstr
• t_cont_smpl
• t_tst_rslt_hdr
• t_tst_rstl_dtl
• t_rmrks_dtl
• t_pcc
• t_superpave
• t_bit_conc_mixblnd

9
Application Execution

• Upon receipt of a completed workbook, the web
  application is launched.

10
(No Transcript)
11
Application Execution (cont.)

• The pages JavaScript allows the user to
• Browse for the workbook(s) they want to load into
  SiteManager.
• Add or remove from list of files to process.
• Validation of the users choices prevent loading
  of non-Excel files, or adding duplicate files to
  the process list.

12
Application Execution (cont.)

• The user chooses the Load option when they are
  satisfied that the process list is correct.
• In the background, and invisible to the user,
  JavaScript launches the clients Excel
  application and briefly opens the workbook to be
  processed.
• Based on the already attached schema map, a
  method is called to extract data, and then save
  it to an XML file.

13
Application Execution (cont.)

• The resulting XML file is saved to a temporary
  processing folder on the Web Server.
• Next, the workbook being processed is saved to
  the same temporary directory.
• Then, Excel closes without interrupting or
  affecting any Excel processes the client may
  already be running.

14
Application Execution (cont.)

• From here, the server side of the application
  begins its work.
• First a series of validations are run against the
  XML file that Excel extracted to the web servers
  temporary directory.
• Since each table from the discipline now exists
  in XML format, Excel is no longer needed.

15
Application Execution (cont.)

• If a validation fails, none of the data is loaded
  into SiteManager, and the errors are displayed to
  the user.
• The user must correct all errors before data can
  be loaded into SiteManager.
• After the initial load has completed, a Reload
  option is available if the user finds a
  non-validated error in data, (i.e., a complete
  record delete/insert based on original Sample and
  Mix Design IDs).

16
Application Execution (cont.)

• Upon successful validation, the data is loaded
  into SiteManager, and a message indicating this
  is displayed to the user.
• Also, upon successful validation, a copy of the
  workbook is archived from the temporary
  processing area on the web server to a central
  repository on a data server, and is named
  according to the root Sample ID.
• The XML file which was used for validation is
  then deleted.

17
Questions

• ???