Implementer Guide for EPGL SMS System
Overview
The EPGL SMS System is comprised of two Java servlets. The first handles an SMS message containing information about a about water system and stores the information in a Google Spreadsheet. The second servlet provides a web-based interface to reporting functionality to keep tabs on the recorded information.
Definitions
- Data Type
- A type of statistic to record information for. Examples include inlet pressure and outlet pressure.
- Data Point
- A string concatenation of a short hand prefix for a data type and its corresponding value.
Schema Design
Use Cases
Use Case 1: Water Plant Operator
A water system operator submits via SMS a message containing various data points for the water system. The SMS is routed through the SMS Gateway, where it is packaged into an HTTP request and forwarded to “ http://www.epgldata.com/sms”, with parameters “PhoneNumber” and “Text”, which represent the SMS sender’s phone number and message, respectively. If the user is authorized to send SMSs to the server, the message will be processed. If there are no errors with the message, the data will be stored in the water_system_data Google Spreadsheet. If no row exists yet for the given water system and date, one is created. If, however, a row already exists for the given water system and date, it is simply updated with the data points present in the message. The message must be in the following format:
epgl xxx yz yz ... yz
where “epgl” is the keyword to ensure proper routing through the SMS gateway, “xxx” is a variable-length string representing the water system id, y’s are prefixes for the different data points, and z’s are values for the data points. A few points:
- The user’s mobile phone number must be listed in the authorized_phone Google Spreadsheet.
- The water system must be listed in the water_system Google Spreadsheet.
- All short strings (“epgl”, water system, data points) must be separated by at least one space.
- Not all data points need to be specified in every message, so updating only one column is possible.
If for any reason the user’s request is rejected (unauthorized number, invalid water system, invalid data type, invalid data parameter, internal server error), an appropriate error message is displayed to the user. On success, a success message is displayed.
Use Case 2: EPGL management & maintenance staff
By pointing a browser to http://www.epgldata.com/, the user may log into the management site. The management site provides two types of reports. The first is a daily report, which displays information pertaining to a single day. Days in the past may be selected. The second type of report is a timeline view of all information for a single water system.
Extensibility
The GData backend may be extended in the following ways:
- Add water systems
- This is done by adding another row to the water_system spreadsheet.
- Add/Remove target values
- Target values for a data type for a given water system can be added, edited, and removed from the water_system spreadsheet.
- Add authorized cell phones
- This is done by adding another row to the authorized_phone spreadsheet.
- Add data type
- This is done by adding another row to the data_type spreadsheet, and then adding another column to the water_system_data spreadsheet whose name is the shorthand encoding of the data type. Data type parameters such as max.length or deviation.limit.percentage may also be edited to fit the given needs.
- Add users to management interface
- Log in credentials are stored in the login Google Spreadsheet, and users and their passwords may be added or edited here.
