1 / 17

Kentucky Transportation Cabinet: Materials Testing Web Application

Kentucky Transportation Cabinet: Materials Testing Web Application. 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.

nasnan
Download Presentation

Kentucky Transportation Cabinet: Materials Testing Web Application

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Kentucky Transportation Cabinet:Materials 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. Technologies • The application uses a variety of technologies including: • Server Side • ASP.NET • C# • Client Side • JavaScript • Excel

  4. 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 thediscipline. • 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.

  5. 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 utilizedSiteManager table and it’sfield were included in the schema design. This allows the one schema to work for all workbooks.

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

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

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

  9. Application Execution (cont.) • The page’s 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 user’s choices prevent loading of non-Excel files, or adding duplicate files to the process list.

  10. 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 client’s 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.

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

  12. 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 server’s temporary directory. • Since each table from the discipline now exists in XML format, Excel is no longer needed.

  13. 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 ID’s).

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

  15. Questions ???

More Related