def alarmDetails(mysql,eventgroup, device_ids):
device_ids = tuple(device_ids)
sql = "select * from event where device_id in %s and event_group='%s'" % (device_ids, eventgroup)
print 'alarmDetails_sql=',sql
eventdata = insertDBdata.get_data(mysql, sql)
status_flag = 0
alarm_info = []
alarm_time = []
alarm_description = []
alarm_color = []
if eventdata:
for i in range(len(eventdata)):
sql = 'select * from event_log where event_id='+str(eventdata[i][0])+' ORDER BY `date_time` DESC LIMIT 1'
alarmdata = insertDBdata.get_data(mysql, sql)
if len(alarmdata) != 0:
alarm_info.append(str(alarmdata[0][3]))
alarm_time.append(str(alarmdata[0][1]))
alarm_description.append(str(eventdata[i][3]))
if alarm_info[-1] == 'recover':
alarm_color.append(0)
else:
alarm_color.append(int(eventdata[i][1]))
if 2 in alarm_color:
status_flag = 2
else:
if 1 in alarm_color:
status_flag = 1
return status_flag, alarm_color, alarm_time, alarm_description
def alarmLogs(mysql, device_ids):
event_id_data = []
event_id = []
for device_id in device_ids:
sql = 'SELECT id FROM `event` where device_id=%s' % device_id
event_id_data = insertDBdata.get_data(mysql, sql)
for row in event_id_data:
event_id.append(int(row[0]))
event_id = tuple(event_id)
sql = 'SELECT * FROM `event_log` where event_id in %s ORDER BY `id` DESC LIMIT 10' % str(event_id)
print sql
logs = insertDBdata.get_data(mysql, sql) # Get the last 10 records
alarm_info = []
alarm_time = []
alarm_description = []
alarm_logs = []
alarm_type = []
alarm_device = []
alarm_status = []
if logs:
for log in logs:
alarm_info.append(log[3])
alarm_time.append(str(log[1]))
sql = 'SELECT * FROM `event` where id="'+str(log[2])+'"'
eventdata = insertDBdata.get_data(mysql, sql)
alarm_description.append(eventdata[0][3])
sql = 'SELECT * FROM `event_type` where id="'+str(eventdata[0][1])+'"'
alarm_type.append(insertDBdata.get_data(mysql, sql)[0][1])
sql = 'SELECT * FROM `device` where id="'+str(eventdata[0][2])+'"'
alarm_device.append(insertDBdata.get_data(mysql, sql)[0][2])
count = len(logs)
for i in range(count):
if alarm_info[i] == 'activate':
#alarm_status.append('<img src="../../static/images/icons/alarm_yellow.png" width="20">')
#logStr = alarm_status[i] + " " + alarm_time[i] + " <b><font color=red>" + alarm_type[i] +" </font></b> '<b>" + alarm_description[i] + "</b>' <b><font color=red>activated!</font></b>"
if alarm_type[i] == 'alert':
alarm_status.append('<img src="../../static/images/icons/alarm_red.png" width="20">')
logStr = alarm_status[i] + " " + alarm_time[i] + " <b><font color=red>" + alarm_type[i] +" </font></b> '<b>" + alarm_description[i] + "</b>' <b><font color=red>activated!</font></b> System sent out alert mails and SMS."
else:
alarm_status.append('<img src="../../static/images/icons/alarm_yellow.png" width="20">')
logStr = alarm_status[i] + " " + alarm_time[i] + " <b><font color=orange>" + alarm_type[i] +" </font></b> '<b>" + alarm_description[i] + "</b>' <b><font color=orange>activated!</font></b> System sent out alarm mails."
else:
alarm_status.append('<img src="../../static/images/icons/alarm_green.png" width="20">')
logStr = alarm_status[i] + " " + alarm_time[i] + " <b><font color=green>" + alarm_type[i] +" </font></b> '<b>" +alarm_description[i] + "</b>' <b><font color=green>cleared!</font></b>"
alarm_logs.append(logStr)
return alarm_logs
def init_electricity_dashboard(mysql):
"""
Parameters:
mysql: mysql connection to EMS DB
"""
""""to initialize the electricity_dashboard table for each site"""
field_list=['alarm_color','alarm_details','alarm_logs','deltaMinutes','efficiencyTable','lastTime','loadTable','PUETable','realTimeCO2','realTimeEfficiency','realTimeLoad','realTimePUE','siteAbsoluteLoad','status_flag']
site_id_sql="SELECT id FROM emsdb.site;"
site_id_datas=insertDBdata.get_data(mysql, site_id_sql)
if site_id_datas:
site_id_list=[4,10]
for site_id_data in site_id_datas:
#site_id=site_id_data[0]
site_id_list.append(site_id_data[0])
site_id_list=[4,10]
site_dashboard_data_list=[]
print 'site_id_list=',site_id_list
site_id_list=[]
for site_id in site_id_list:
for field in field_list:
site_dashboard_data_list.append([site_id, field])
sql_insert_dashbord_field="insert ignore into emsdb.electricity_dashboard (site_id,field) values(%s,'%s');"
insertDBdata.insert_data(mysql, sql_insert_dashbord_field, site_dashboard_data_list)
else:
pass
return
def device_id_list(mysql, site_id):
sql = 'SELECT id FROM `device` where site_id=%d' % site_id
device_id_data = insertDBdata.get_data(mysql, sql)
device_id = []
for row in device_id_data:
device_id.append(int(row[0]))
return device_id
def get_latest_db_time(self,db_time=None):
if db_time==None:
beforeTime_str = fileOperation.Readfile()
if beforeTime_str != None:
pass
else:
nowTime = datetime.datetime.now()
beforeTime = nowTime - datetime.timedelta(minutes=5)
beforeTime_str = str(beforeTime)
beforeTime_str = beforeTime_str[:17] + '00'
fileOperation.Writefile(beforeTime_str)
latest_sql="select max(electricity.date_time) \
from electricity, \
(select measurement.id as measurement_id \
from measurement, device, site \
where measurement.device_id=device.id \
and device.site_id=site.id \
and site.name='%s' \
) as A \
where electricity.measurement_id=A.measurement_id \
and electricity.date_time>'%s';" % (self.sitename, beforeTime_str)
latesttime_data = insertDBdata.get_data(self.emysql, latest_sql)
if latesttime_data:
db_time = latesttime_data[0][0]
else:
db_time = datetime.datetime.strptime(beforeTime_str,ISOTIMEFORMAT)
# not choose datetime.datetime.now() time as db_time, in case bms does not have data of now() time
#db_time = datetime.datetime.now()
#self.dbLatestTime=db_time
return db_time
def getLastMeasurement(self,measurement_id,nowtime_str):
sql_lastest="SELECT CAST(date_time AS CHAR),measurement_id,value \
FROM electricity \
where measurement_id=%s and date_time<'%s' \
order by date_time desc limit 1;" % (measurement_id,nowtime_str)
lastest_tuple=insertDBdata.get_data(self.emysql, sql_lastest)
latest_list=[]
if lastest_tuple:
latest_list=[lastest_tuple[0][0],lastest_tuple[0][1],lastest_tuple[0][2]]
#latest_list=lastest_tuple[0]
if self.DEBUG_ENABLE: print 'getLastMeasurement:',latest_list
return latest_list
def getDemoOption(self,demo_type='cooling_demo'):
demo_sql="SELECT * FROM `demon` WHERE `demon`.`field`='%s';"%demo_type
demo_data=insertDBdata.get_data(self.emysql, demo_sql)
demo_option=0
print '===================getDemoOption'
if demo_data:
demo_option=demo_data[0][1]
#print demo_option
#if demo_option==1:
print '===================%s option is ENABLE.' % demo_type
else:
pass
return demo_option
def _get_actual_ms_tag_map(self):
sql_actual_measurement="SELECT measurement_id,local_db_tag FROM v_measurement where validity='yes' and virtuality=0 and site_name='%s';" % self.sitename
actual_measurement_data=insertDBdata.get_data(self.emysql, sql_actual_measurement)
#print '_get_actual_ms_tag_map():',actual_measurement_data
ms_tag={}
ms_list=[]
if actual_measurement_data:
for tup in actual_measurement_data:
key=tup[0]
tag=tup[1]
ms_list.append(key)
ms_tag[key]=tag
ms_tag['ms_list']=ms_list
return ms_tag
def getDemoMeasurement(self,demo_option,outtage_type='crac_1_3_outage'):
demo_measurement_list=[]
if demo_option==1:
map_sql="SELECT * FROM v_event_criteria where event_description='%s' and site_name='%s';" % (outtage_type, self.sitename)
measure_map_data=insertDBdata.get_data(self.emysql, map_sql)
if measure_map_data:
for measure_map in measure_map_data:
outage_measurement_id=measure_map[1]
event_description=measure_map[9]
demo_measurement_list.append(outage_measurement_id)
else:
pass
else:
pass
return demo_measurement_list
def _get_virtual_energy_ms_map(self):
sql_energy_from_power="SELECT measurement_id,virtuality FROM v_measurement where validity='yes' and local_db_tag='virtual' and measurement_category='kWh_UPS_output' and site_name='%s';" % self.sitename
energy_from_power_data=insertDBdata.get_data(self.emysql, sql_energy_from_power)
ms_tag={}
ms_list=[]
if energy_from_power_data:
for tup in energy_from_power_data:
key=tup[0]
related_key=tup[1]
#tag=self.actual_ms_tag_map[related_key]
ms_tag[key]=related_key
ms_list.append(key)
ms_tag['ms_list']=ms_list
return ms_tag
def heartbeatAlert():
sql = 'select phone from v_subscription where alert_subscription="yes"'
datalist = insertDBdata.get_data(mysql, sql)
smslist = []
for row in datalist:
smslist.append(row[0]+'@sms.ericsson.com')
sql = 'select email from v_subscription where alert_subscription="yes"'
datalist = insertDBdata.get_data(mysql, sql)
emaillist = []
for row in datalist:
emaillist.append(row[0])
subjectSMS = '[EMS Communication Loss Alert]'
subjectMail = 'EMS Communication Loss Alert'
smscontent = ' There is no new data updated for more than 5 minutes in EMS database. Please take action! [@Energy Management System]'
mailcontent = 'Dear User,\n \n' + 'EMS communication is lost, and there is no new data updated for more than 5 minutes in EMS database. Please take action! \n \n' + 'Energy Management System \n' + 'http://ems.eld.gz.cn.ao.ericsson.se'
msg = MIMEText(smscontent)
msg['Subject'] = subjectSMS
smtp = smtplib.SMTP()
smtp.connect('142.133.1.1')
smtp.sendmail('*****@*****.**', smslist, msg.as_string())
msg = MIMEText(mailcontent)
msg['Subject'] = subjectMail
smtp.sendmail('*****@*****.**', emaillist, msg.as_string())
smtp.quit()
def get_all_actual_measurement(self,nowtime_str,tag_list=None):
#self.actual_ms_tag_map=self._get_actual_ms_tag_map()
dbname_str=nowtime_str.replace('-','')
dbname='data'+dbname_str[:6]
#print 'get_all_actual_measurement'
if tag_list==None:
#print self.actual_ms_tag_map
ms_list=self.actual_ms_tag_map['ms_list']
tag_list=[]
list_str='('
for ms in ms_list:
tag=self.actual_ms_tag_map[ms]
list_str=list_str+'\''+'%s'%tag+'\''+','
tag_list.append(tag)
list_str=list_str[:-1]+')'
sql = "SELECT CAST(save_time AS CHAR),tag_no,tag_value FROM %s WHERE tag_no in %s and save_time='%s';" %(dbname, list_str,nowtime_str)
#print 'all sql: ',sql
#print sql
tupledata = insertDBdata.get_data(self.mysql, sql)
#format dataset as [time, measurement_id,value] bellow
dataset = self.checkData(tupledata)
#dataset = json.dumps(con_data)
return dataset
def heartbeat():
global host, port, user, passwd, db, alert_count
print alert_count
print 'Calculate delta minutes between DB and local.'
localtime = datetime.datetime.now()
sql = 'SELECT date_time FROM electricity ORDER BY date_time DESC LIMIT 1'
datalist = insertDBdata.get_data(mysql, sql)
dbtime = datalist[0][0]
sql = 'update electricity_dashboard set date_time="'+str(dbtime)+'" where field="lastTime"'
insertDBdata.update_data(mysql, sql)
deltaseconds = (localtime - dbtime).total_seconds()
deltaminutes = deltaseconds // 60
sql = 'update electricity_dashboard set value='+str(deltaminutes)+' where field="deltaMinutes"'
insertDBdata.update_data(mysql, sql)
print 'Delta minutes between DB and local is ', deltaminutes
if deltaminutes >= 5:
dashboardReset()
print 'Reset all the values to 0 on dashboard.'
if alert_count == 0:
heartbeatAlert()
alert_count = 1
print 'Sent SMS/Email for communication lost alert.'
else:
alert_count = 0