def sendAlertMessage():
# check due alert messages
ts_due = dt.today()
query = "select alert_id,alertmsg from smsalerts where ack = 'none' and ts_set <= '%s'" % (
ts_due.strftime("%Y-%m-%d %H:%M:%S"))
alertmsg = dbio.querydatabase(query, 'SendAlertMessage')
if alertmsg == None:
print 'No alertmsg set for sending'
return
message = 'Alert ID %d:\n%s\n' % (alertmsg[0][0], alertmsg[0][1])
message += 'Text "ACK <alert id> <valid/invalid> <remarks>" to acknowledge'
# send to alert staff
contacts = getAlertStaffNumbers()
for item in contacts:
# for multile contacts
for i in item[1].split(','):
server.WriteOutboxMessageToDb(message, i)
# set alert to 15 mins later
ts_due = ts_due + td(seconds=60 * 15)
query = "update smsalerts set ts_set = '%s' where alert_id = %s" % (
ts_due.strftime("%Y-%m-%d %H:%M:%S"), alertmsg[0][0])
dbio.commitToDb(query, 'checkalertmsg')
def getSensorNumbers():
querys = "SELECT sim_num from site_column_sim_nums"
# print querys
nums = dbio.querydatabase(querys, 'getSensorNumbers', 'LOCAL')
return nums
def count_items(ts_end=None,
ts_start=None,
table=None,
stat_col=None,
stat=None,
pq=False,
write_to_db=False,
timelag=5,
backtrack=5000,
dbinstance='local'):
if table is None:
raise ValueError("No table given")
if ts_end is None:
ts = dt.now()
ts_end = ts - td(minutes=ts.minute % 5,
seconds=ts.second,
microseconds=ts.microsecond)
if ts_start is None:
ts_start = ts_end - td(minutes=5)
ts_start = ts_start.strftime("%Y-%m-%d %H:%M:%S")
ts_end = ts_end.strftime("%Y-%m-%d %H:%M:%S")
print "Count %s items from %s to %s:" % (table, ts_start, ts_end)
query = ("select count(sms_id) from %s "
"where sms_id > (select max(sms_id) - %d from %s) "
"and timestamp > '%s' "
"and timestamp < '%s' ") % (table, backtrack, table, ts_start,
ts_end)
if stat_col is not None:
if stat is None:
raise ValueError('No stat for stat_col (%s) given' % (stat_col))
query += "and %s like '%s%s'" % (stat_col, stat, '%')
else:
stat_col = 'undefined'
stat = "undefined"
if pq:
print "Count query:", query
rs = dbio.querydatabase(query, "ci", "gsm")
try:
item_count = rs[0][0]
print "Count: %d" % (item_count)
except ValueError:
print "Error in resultset conversion. %s", rs
return None
except IndexError, TypeError:
print "Error: Query error (%s)" % (query)
return None
def main():
try:
if sys.argv[1] == 'test': writetodb = False
else: writetodb = True
except:
writetodb = True
c = cfg.config()
dbio.createTable("runtimelog", "runtime")
server.logRuntimeStatus("alert", "checked")
print '>> Checking for alert sms'
alertmsg = server.CheckAlertMessages()
print alertmsg
if alertmsg:
# server.WriteOutboxMessageToDb(alertmsg,c.smsalert.smartnum)
# server.WriteOutboxMessageToDb(alertmsg,c.smsalert.globenum)
query = """select nickname, numbers from dewslcontacts where grouptags like '%alert%'"""
contacts = dbio.querydatabase(query, 'checkalert')
# print contacts
query = "INSERT INTO smsoutbox (timestamp_written,recepients,sms_msg,send_status) VALUES "
tsw = dt.today().strftime("%Y-%m-%d %H:%M:%S")
for item in contacts:
message = 'SENSOR ALERT:\n%s' % (alertmsg)
message = message.replace("ALERT", "AL3RT")
query += "('%s','%s','%s','UNSENT')," % (tsw, item[1], message)
query = query[:-1]
if writetodb: dbio.commitToDb(query, 'checkalertmsg', 'GSM')
else: print query
print 'done'
else:
print '>> No alert msg read.'
def getAlertStaffNumbers():
query = """select nickname, numbers from dewslcontacts where grouptags like '%alert%'"""
contacts = dbio.querydatabase(query, 'checkalert')
return contacts