This application was built for the City of Troy Department of Public Works (DPW) in the Fall of 2022 by a group of RPI students taking a course. The intended purpose of this program was to enable DPW staff to better manage and fulfill bulk pickup requests for large trash/brush items. An additional goal was to maintain visibility with other connected stakeholders like the City Hall billing office and Litter Patrol Officers (LPOs).
The proposed workflow is as follows: a request is sent to DPW by a Troy citizen, the request is received by a DPW dispatcher who enters the request details into the application. Based on zone and DPW staff capacity, the application is used to assign a DPW driver and is used to schedule the pickup date. The dispatcher can export the request details and print out all required forms for a given day. These forms can be given to DPW drivers and LPOs - the drivers can handwrite on these paper forms to fill out missing fields like the actual quantity of items received. Once the pickup has concluded, the driver can deliver the completed form back to the dispatcher who then can update the requests listed in the application with new details like pickup date, items, etc.
Eventually, the hope is that the billing office and others can also directly use the application to collaborate on one single-source-of-truth where knowledge handoffs can be minimized.
Note that this application is only available on the internal City of Troy government network on certain allowed devices.
Production URL: https://dpw.troyny.gov
| Software Requirements |
|---|
| Node 18.12.1 LTS |
| Node MSSQL module (npm install mssql) |
| Node ExpressJS module (npm install express) |
| Node Dotenv module (npm install dotenv) |
| SQL Server 2019 |
| SQL Server Management Studio |
| Internet Information Services (IIS) |
main is the staging branch for all development work
stable is the production branch used by the remote server
- When working on a new feature create a branch off of main.
- When finished developing the feature, submit a pull request.
- Once the pull request is approved, merge the new feature into main.
- Test and ensure the application is working correctly.
- Merge updated main into stable when ready to push to production. (Full steps outlined below).
--
Extensive online guides are available. Look up how to setup a local environment for a GitHub repository for more info.
The application uses Node 18.12.1 LTS as middleware and can be installed from the https://www.nodejs.org website. The required Node modules are used to form connections to the SQL database used to store submitted form data. NPM is used to install the required modules. Node modules can be installed by navigating to the application's folder in a Powershell or command prompt window and running the npm install command. This will install all modules used in the application.
An .env file that contains a valid database username, database password, and server name must be configured in the application's root folder for the mssql module to work. To access to the env file, the Node dotenv module is required. No additional steps are required if the npm install command is used following the installing node modules instructions.
An .env.example is provided as a template.
--
The file containing the NodeJS server-side code for the application is called 'index.js'. To run the application using Powershell or the command prompt, in the same folder location run the node index.js command. In a browser window, navigating to localhost:3000 will open the application.
Node will run continuously until: the Powershell/command prompt window running the node command is closed, the host machine is shut down, or a critical error (e.g. SQL database is not accessible via the mssql module) occurs. Node can be restarted using the 'Running the application' steps for Node.
To restart, quit the running process by using the command ^C. Then start the application again with node index.js.
The application uses SQL Server as the backend for all submitted form data. To access the SQL database, users will need to login to SQL Server Management Studio. Both can be installed from https://www.microsoft.com/en-us/sql-server/sql-server-downloads (SQL Server on premise). For login, the server type is 'database engine'. If the server name is not known, it should be acquired by contacting the Troy BIS department. The authentication type is 'Mixed authentication'. Login info is provided by the Troy BIS department. Once connected to the server, in the object explorer pane, click the databases folder to access the bulkDPW database. This database contains all necessary information for the backend.
name: troydpwservice
Please consult Troy BIS (Bureau of Information Systems) department for further login information.
The SQL database and actual hosting of the production site is provided through the remote webserver. How does this work? Essentially the NodeJS server is mapped to IIS which is configured to host the application on the production URL.
For SQL server admins, additional logins can be configured by right-clicking on the Security folder in the Object Pane
- Fetch and pull any commits on the stable branch.
- Restart the node server (interrupt and start the running process in Powershell / command prompt).
- (optional) Restart the IIS server.
This is a single-page application that uses Javascript (JS), jQuery, Cascading Style Sheets (CSS), HTML, and several additional front-end / back-end packages.
Description of the major files:
index.htmlis the homepage of the applicationindex.jsis the server-side JS (i.e., backend or NodeJS)public/app.jsis the client-side JS (i.e., frontend JS)public/app.cssis the client-side styling (i.e., frontend CSS)
Comments in the code provide more in-depth detail, but the major functions are described below. The functions build on each other in a modular way to minimize rework required when changes need to be made.
Using the tabs at the top of the page, a user can switch to a different 'page' or a different 'mode'. Specific elements are hidden or shown based on a user's selection.
Pages include create, search, and export. Modes include trash and brush.
A user's selection is saved (so when a reload happens, the user sees the same page/mode as before) using cookies / local session storage.
Using the Tabulator (https://tabulator.info/) package, and a JSON data object received from the backend, a consistent table view is displayed on all pages of the site. This table allows for filtering, sorting (by clicking on header columns), pagination, etc. Table events are also used to implement other features of the site.
An incomplete/complete toggle is also present. When clicked, a filter to the table is applied and the table's status message is updated.
Using the form, a user can create a new request. Input validation is present in some fields to ensure better data quality. In some cases, fields will allow null values but will error out when incomplete values are given.
Input masking for the phone number is also present through a package called Inputmask (https://github.com/RobinHerbots/Inputmask). This allows for easier entry (digits are automatically capped and reorganized in '(123) 456-7890' format).
Itemized category list allows one to input a quantity of items that are expected. Once the request has been completed, the actual quantity of items can be input.
Inputted data is sent to the backend where it is checked for SQL injection attacks, and summarily passed to the SQL database as an 'insert'. Once this is complete, a VIEW request is triggered and data reloads into the table on the frontend.
When a table row is clicked (from any page importantly), a Tabulator click event is triggered. This event displays the EDIT page, which draws from the CREATE form. The same fields, format, input validation, etc. are maintained. The only difference is that existing data is populated into the form.
When the edit button is clicked a request similar to CREATE is fired to the backend. Upon completion, a VIEW request is fired.
A button for DELETE is present on the EDIT page. When the delete button is clicked, a DELETE request is fired which deletes the entry from the SQL database. Upon completion, a VIEW request is fired.
When a query is entered and submitted on the SEARCH page, the query is passed to the backend. Using a backend package called ElasticLunr (http://elasticlunr.com/), the query is compared against a pre-built search index. Any matches are passed back to the frontend as a JSON object. This object is parsed, and matching rows of the table are displayed. The status message of the table also updates.
On the EXPORT page, when a zone or a date scheduled are changed, the table filters to show relevant results. These filters stack with each other and with the incomplete/complete toggle. This means adjusting any one of the three filters will yield more relevant results. The status message of the table also updates correspondingly.
Any data in the current table can be converted to a PDF when the EXPORT button is clicked. When clicked, a PDF with a page per request is downloaded. Note: EXPORT can only print what is currently visible - in other words, it will print at a maximum the current 'page size' of the table. This can be adjusted at the bottom row of the table.
The given functionality can be powerful. For example, after a SEARCH is performed, the results of the table can be downloaded by using EXPORT.
This feature is enabled by the package html2pdf.js (https://ekoopmans.github.io/html2pdf.js/).
special mention:
- current SQL table has some historical data - data from Donna's former workstation could be brought over to complete the past data that is available
all:
- adding a link to the CREATE / EDIT forms that links to the zone map
- having the zone autofill based on the chosen address
- displaying the address on a map
- adding cost to the itemized category list
- having the cost update based on number of items
- adding individual items instead of adding them into categories
- additional confirmation could be required before DELETE submits (i.e., are you sure you want to delete?)
- EDIT could only trigger once a field has been changed
- VIEW can be made more performant, is there a way to update the table with new requests rather than replacing all the data?
- EXPORT could be triggerable from a request's EDIT page
- automating the NodeJS start (so that if the remote server ever reboots, the application could start automatically)
- SQL injection detection could be added to all other pertinent functions like EDIT
- increased classifications for violations