“To Do List” for Building Maintenance

1. “To Do List” for Building Maintenance Jerry Samples Mobile, coordinated assignments using a PDA CS 8628, Summer 2003

2. Building automation systems (BAS) monitor and control HVAC* and other equipment Network of sensors, embedded controllers, and workstations Can automatically notify personnel of situations needing attention * Heating, Ventilation, and Air-Conditioning A building automation system (BAS) can be programmed to notify personnel if a part of the system enters a predefined state, such as equipment failing to operate properly. Some of these events require building maintenance staff to go to the location of the faulty equipment and troubleshoot the problem. In a large facility, such as a college campus, there may be many events needing attention at any given time. A PDA can improve efficiency by allowing maintenance technicians to download a list of items currently needing attention, including the location of the problem and enough details to allow efficient resolution. With this mobile “to do list”, they can handle several problems in series before returning to the main office. BAS Background

3. A quasi-realistic BAS which uses Oracle 9i and populates a custom table with notifications of (simulated) problems detected. The records describe problems needing human attention somewhere in the facility. Maintenance technicians use the MobileVB application to download these notification records into a PDA, which they carry around for reference. Besides the intended MobileVB aspect of the project, I got excellent experience using my company’s product with Oracle, which I had not previously done. Automated Logic Corp’s “WebCTRL Pro” (pronounced “web control”) BAS software was installed and configured to use Oracle 9i databases and simulate monitoring and control of a small office building. In response to simulated problems, it populates a custom table with problem details. A MobileVB application transfers these records to a PDA, which a technician could carry around the facility for reference as they dealt with each situation. The traditional alternative is to print out a list of issues or log in to a workstation to see the current problem list. Project Description

4. Create VMware virtual machine (VM) capable of running enterprise-class database and BAS software Install Microsoft ActiveSync on VM Install Oracle 9i Personal Edition software Install Automated Logic’s “WebCTRL Pro 2.0” software Port Automated Logic’s sales demo system to Oracle 9i Configure WebCTRL notifications for certain events to insert records into a custom Oracle table Because of the great size of Oracle, WebCTRL, and Visual Basic, I created a VM with a virtual hard disk size of 15 GB. I also increased my computer’s system RAM from 512 MB to 1 GB so that the VM could have more RAM allocated and improve performance. Once Oracle and WebCTRL were installed, I ported the WebCTRL Sales demo software over to have quasi-realistic database files on which to operate. I learned many new things about WebCTRL and Oracle while setting up this environment. Sequence of Tasks (1)

5. Install Visual Basic 6.0 Install AppForge’s MobileVB extensions to VB for creating PDA-specific VB applications Install Sybase SQL Anywhere 8 database system, including Ultralite database for PDAs and Mobilink synchronization server Generate MobileVB app to display records Generate sync. scripts for both directions Several pieces of software work together to maintain (1) the primary database, (2) a reduced but truly relational PDA database, (3) a middleware component for true database synchronization, and (4) a convenient environment for development of the PDA application’s user interface and functionality. Sequence of Tasks (2)

6. WebCTRL displays graphical representations of a building’s floorplans and equipment showing live sensor data. It can also present graphs of collected data samples (trend data) of various sources. The figure here shows colors on the floorplans to indicate areas that are too warm (orange, red) and too cool (blue), On the right is a graph of electrical power usage over time. Data such as the building’s configuration, as well as trend data are stored in databases. An Internet Explorer (5.5 or higher) browser is the primary user interface to a running system, although some data can be accessed via SQL and XML/SOAP. WebCTRL

7. WebCTRL creates numerous tables in databases which are used for a variety of purposes. Often, four separate databases are created (see figure), so that each can have characteristics appropriate for a given purpose. However, a single database is also acceptable in most cases. The figure above shows part of the manual configuration of WebCTRL, where JDBC connection information must be supplied during the one-time engineering phase of a new system. The four databases (wcmain, etc.) were created prior to this step using the Oracle “Database Configuration Assistant”. Port to Oracle 9i

8. HVAC Equipment (air handlers, fans, water chillers, etc.) . . . Is monitored by a . . . Building Automation System, which . . . Generates . . . Problem and Event Notifications, which are . . . Corrected by a . . . Maintenance technician This list identifies the key sequence of operations of a BAS in its role of monitoring a facility and automatically notifying personnel of situations that require human intervention. The cardinalities given here are specific to my project. The states which trigger notification, as well as the specific forms of notification, must be programmed in advance. Notifications do not always indicate a problem. They may also be used as “ticklers” for scheduled maintenance operations, such as replacing motors, fan belts, air filters, etc. E-R Description

9. This diagram represents the sequence given on the previous slide in list form. HVAC equipment may generate multiple notifications, but each one will only be dealt with by one technician. Important information that should be in the notification record includes: timestamp, physical location of the problem source, status code or parameter value, and type (general nature or category) of the event. E-R Diagram

10. In any given system, there is a limited number of event types (categories) defined. Each is identified by a short name, but also has associated with it a verbose description of the problem it represents. These two items are placed in one table which I call EVENT_TEXT. The table that receives the event notifications, ACTION_ITEMS, uses ‘category’ as a short foreign key. This way, the lengthy string is not stored repeatedly for different events of the same type. If and when the verbose description is needed, it is looked up in EVENT_TEXT. Logical Schema

11. The data dictionary for my two tables is shown in the form of the SQL statements I used to create them in Oracle’s SQL*Plus interactive environment. I chose arbitrary field lengths which I thought were sufficiently long for most realistic cases, and certainly long enough for my project examples. Physical Schema (DDL) SQL> create table event_text ( 2 category VARCHAR2(50), 3 description VARCHAR2(300), 4 primary key (category) ); Table created. SQL> create table action_items ( 2 timestp DATE, 3 location VARCHAR2(500), 4 status VARCHAR2(100), 5 category VARCHAR2(50), 6 primary key(timestp), 7 foreign key(category) references event_text ); Table created.

12. SQL> select * from event_text; CATEGORY -------------------------------------------------- DESCRIPTION -------------------------------------------------------------------------------- Filter Dirty Air filter reports dirty. Check or change filter. Damper Stuck Damper appears stuck. Check damper operation. Temp Sensor Temperature sensor fault. Check for proper wiring. For my project, I defined three event categories. Their short names are in the ‘category’ column, and the verbose descriptive text is from the ‘description’ field. All three records are shown in the listing above. This table would not usually be altered once a system is engineered unless a new category was added. In rare cases, the descriptive text might be changed to be more informative. Prepopulated Table

13. This figure gives an idea of the WebCTRL user interface page, within the Internet Explorer browser, where the BAS administrator configures certain states to generate records into the consolidated database. Custom SQL to DB (1)

14. This figure shows detail from the previous slide. At the very bottom of the figure, you can see an SQL statement which is executed any time the specified state is entered. Replaceable parameters are given between dollar signs (e.g. $Generation_Time$). WebCTRL will provide the actual values at the time the SQL statement is executed. The SQL statement can not have a semi-colon at the end, or else an error occurs; the semicolon is handled by WebCTRL. Custom SQL to DB (2)

15. Working name is “WebCTRL Action Item Manager”, or WebAIM for short (did I miss my calling in Marketing or what?) Synchronize with consolidated database and retrieve any action item entries Page through entries to see the problems reported After resolving a problem, delete the record from the PDA’s local database When return to the main office, synchronize again. Records deleted on PDA are deleted from consolidated database; new entries since previous consolidation download to PDA The bullet list above describes the basic steps that would be followed when using this application. The following slides show some screen shots of the actual PDA with real data synchronized from the Oracle database, which had been populated by a running WebCTRL 2.0 system. I manuallyaltered parameters of the running system to simulate error conditions so that the error reporting and database logging would occur. MobileVB App

16. SQL> select table_name from user_tables where table_name like 'ML_%'; TABLE_NAME ------------------------------ ML_CONNECTION_SCRIPT ML_SCRIPT ML_SCRIPTS_MODIFIED ML_SCRIPT_VERSION ML_SUBSCRIPTION ML_TABLE ML_TABLE_SCRIPT ML_USER 8 rows selected. Custom synchronization code: exec ml_add_table_script( 'actionitem', 'action_items', 'upload_delete', - 'DELETE from action_items where timestp = ?' ) / I ran the syncora.sql script provided by Sybase. This created 8 tables named ML_* and several stored procedures. The only additional synchronization SQL code I had to run was to establish the record deletion behavior. It needed to make sure that records marked as deleted on the PDA are removed from the consolidated (Oracle) database during synchronization. In general, the default sync. scripts worked for me. Synchronization Script

17. Private Sub mnuViewNext_Click() ' If Next was enabled, there are >1 items, so we ' need to Enable Prev button/menu frmItems.mnuViewPrev.Enabled = True frmItems.btnPrev.Enabled = True actn_tbl.MoveNext ' Test for end of table and if so, disable Next menu/button actn_tbl.MoveNext If actn_tbl.EOF Then frmItems.btnNext.Enabled = False frmItems.mnuViewNext.Enabled = False End If actn_tbl.MovePrevious Set_Item_Fields End Sub This MobileVB code is called when the user clicks the Next button or chooses View | Next from the menu. If the new row is the last, the Next options are disabled. The technique used to determine this is to two row advances and see if the second one takes us beyond the last row (EOF). If so, then back up one to point at the correct (and final) row. Code Sample # 1

18. Private Sub Set_Item_Fields() Dim m_CatStr As String dataLocx.Caption = actn_tbl.Column("location").StringValue dataStatus.Caption = actn_tbl.Column("status").StringValue dataTimeOccur.Caption = FormatDateTime(actn_tbl.Column("timestp").DatetimeValue, vbGeneralDate) ' Lookup description from event_text table that matches ' the short category string in actn_tbl m_CatStr = actn_tbl.Column("category").StringValue descrip_tbl.FindBegin descrip_tbl.Column("category").StringValue = m_CatStr If descrip_tbl.FindFirst Then dataDescrip.Caption = descrip_tbl.Column("description").StringValue End If End Sub This MobileVB code is called anytime the action item form is displayed or the content changed. This happens on Next and Prev actions. The timestamp, location, and status fields are taken from the ‘action_items’ table. The long descriptive text is found in the ‘event_text’ table by using ‘category’ as a foreign key. Code Sample # 2

19. Entry screen upon launching the application. If any action items are in the PDA’s local Ultralite database, the number will be shown. If there hasn’t been a first synchronization yet, this will show 0. WebAIM UI (1)

20. Typical action item page. Location field describes where the source of the problem is believed to be. Description is supposed to be a helpful hint about what to do. Status describes the state triggering the event. “OFF NORMAL” is an industry term. Occurred is the time at which the notification was generated. WebAIM UI (2)

21. The Help | About popup dialog. WebAIM UI (3)

22. Getting synchronization to work; hard to find what was possible, what tables and stored procedures were created and how used. DATE vs. TIMESTAMP data types in Schema Painter. I thought DATE was always a high-precision timestamp, but Schema Painter truncates this type. Using TIMESTAMP in my .USM allowed me to use the event time as my primary key Not aware that Sybase provided scripts (syncora.sql) to prepare Oracle to work with Mobilink. Happened to stumble across this. Later, also found script to allow CustDB to work with Oracle (custora.sql). Once found, these worked great. By default, clicking ‘X’ in upper right of PocketPC 2002 apps only minimizes app. Doesn’t close it. Can’t deploy updated app until closed. Can add a handler. Commit, commit, commit. During interactive SQL*Plus, if I changed records without a commit, PDA syncs appeared to not work correctly. DOH! If don’t Hide one form before Show another, both are visible and accessible, which could allow improper sequences of commands. Use Me.Hide and Show methods I did not encounter any HUGE roadblocks. Rather, I experienced a continuous series of many, many small and medium-sized obstacles. I knew what pieces needed to work together, but making them do so was a challenge at every turn. Part of the problem was difficulty finding adequate dosumentation explaining what was possible, what options were available, and what the APIs were. I ended up using about three or four different sets of help documentation, as well as the VB ObjectBrowser tool, which was in some ways the most helpful. Difficulties Encountered (1)

23. Had to go to AppForge web site and download 30-day license before could deploy to PDA, Apps created only have a two-day life. Don’t take the PDA on the road for a week of sales demos without a development laptop as well. Mobilink can only connect to one ODBC datasource at a time. This was not clear initially when I tried to run my system and the CustDB simultaneously. Disabling top-level menus (e.g. Edit) doesn’t really disable it on PDA. Simulator behaves as expected. On PDA, however, need to disable all submenu items to keep them from being available. SysParms members were not completely described, so had to guess at some usages I didn’t know for a long time that VB ObjectBrowser was available. It is not launched as part of Help files, so it’s hard to find. Difficulties Encountered (2)

24. I learned a tremendous amount working all the way through to a completed running system Saw how databases from different vendors can interoperate effectively Learned what configurations need to be performed when starting all pieces from scratch Commit! Commit! Commit! when performing interactive SQL sessions during app development to avoid confusion Conclusion