Knowledgebase: > MySQL
Emergency Text Alert Notification Configuration and Workflow
Posted by Blair Benjamin, Last modified by Sali Kaceli on 10/29/2019 2:29 PM

The process for maintaining our database of text alert numbers is explained below

1- Students and Employees are responsible for adding and maintaining their contact record, which can be done at https://cairn.edu/emergency-contact-information/.  There are links to this URL on the Student/Employee Hub (https://cairn.edu/hub/).  This process authenticates/verifies the individual using their PeopleID.  

  • The script uses TWO MySQL tables on the remote cairn.edu website/server - EmergencyContact and CurrentCairnPeople.   The script will first query the EmergencyContact table to see if there is an existing record, in which case the process will update it with any newly added/changed information.  If there is not an existing record, it will query the CurrentCairnPeople table, which contains all current students and employees.  It will then pre-populate the form with basic info derived ultimately from PowerCampus.  Some of these fields (name, cairn email) cannot be edited.  The rest can.
  • The CurrentCairnPeople table syncs daily via a one-way sync FROM the ITDEPT table on Betty called dbo.CurrentCairnPeople which is generated by the Job "Current Cairn People Update" which runs daily.  The sync from the local MSSQL DB on Betty to the remote MySQL DB on the cairn.edu website takes place using a utility called DBSync for MS SQL & MySQL which runs locally on Blair's workstation as a daily scheduled task.
  • The EmergencyContact table on the remote MySQL DB syncs daily using the same utility to do a bi-directional sync with ITDEPT table dbo.EmergencyContact.

2- The Emergency Contact information harvested from the database/process on the website is then processed locally by the job on Betty called CIRT Text Msg DistributionList Updates.  This job generates three separate CSV files in a format suitable for upload to TellMyCell (our system/vendor for sending the text notifications).  These three files (employees.csv, residents.csv and students.csv) are saved to j:\Crisis Response\Notification\Distribution Lists each time the job is run.  These correspond with three separate groups in TellMyCell, respectively.   The job verifies that the records outputted for upload to TellMyCell include only current Students and Employees  (for example, the actual EmergenctContact table contains many more records than we actually upload to TellMyCell).   This prevents us from sending notifications to former students/employees.

3- The files generated in the step above are manually uploaded to TellMyCell periodically.  Typically, at the beginning of each semester, about once a month thereafter, and when a re-fresh is warranted due to a suspected impending incident (snow forecast, etc.).   The existing groups are deleted and then a new import is created with the same name.

Notes:

  • If a student claims to have not received a notification, check to see if they are in the TellMyCell database.  If they are not, check to see if they are in the EmergencyContact table locally and/or remotely.  If they're in the remote table but not the local one, it suggests a syncing issue.
  • If they are in the TellMyCell database, they may need to "Opt In" with their carrier, which they can do by texting cairn to 88202.

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments: