def dashboardUpdate(mysql,dashboard_data,site_id):
"""
Parameters:
mysql: mysql connection to EMS DB
dashboard_data:
list type
First element of dashboard_data is site id,
Second element of dashboard_data is 'lastTime' field data
Return values:
"""
print 'begin dashboard data update:',dashboard_data,len(dashboard_data)
if dashboard_data and len(dashboard_data)>1:
"""update 'lasttime' field here"""
lasttimedata=dashboard_data.pop(0)
field_name=lasttimedata[0]
field_value=lasttimedata[1]
dashboard_update_sql="UPDATE `electricity_dashboard` SET `date_time` = '%s' WHERE `electricity_dashboard`.`field` = '%s' and site_id = '%s';"%(field_value,field_name,site_id)
insertDBdata.update_data(mysql, dashboard_update_sql)
"""update other field data"""
for field_data in dashboard_data:
print 'update field:',field_data
field_name=field_data[0]
field_value=field_data[1]
dashboard_update_sql="UPDATE electricity_dashboard SET value=%s WHERE electricity_dashboard.field = '%s' and site_id = '%s';"%(field_value,field_name,site_id)
#sql_update_field="UPDATE `electricity_dashboard` SET `value` = '%s' WHERE `electricity_dashboard`.`field` = '%s';"%(field[1],field[0])
print dashboard_update_sql
insertDBdata.update_data(mysql, dashboard_update_sql)
return
def updateDashboardAlarm(mysql, site_id):
alarm_color = [[], [], [], []] # Flag color for the breakdown alarm logs
alarm_details = [[], [], [], []] # Alarm text for the breakdown alarm logs
status_flag = [0, 0, 0, 0] # For the four lights on alarm panel, 0:green, 1:yellow, 2:red, 3:grey
eventgroupname = ['City Power', 'UPS System', 'DC System', 'Cooling Device'] # Event group names on alarm panel
eventgroup = ['City Power', 'UPS', 'DC', 'Cooling'] # Event group names in database
device_ids = device_id_list(mysql, site_id)
for i in range(len(eventgroup)):
status_flag[i], alarm_color[i], alarm_details[i] = alarm(mysql,eventgroup[i], eventgroupname[i], device_ids)
alarm_logs = alarmLogs(mysql, device_ids)
status_flag = json.dumps(status_flag)
alarm_details = json.dumps(alarm_details)
alarm_details = json.dumps(alarm_details)
alarm_logs = json.dumps(alarm_logs)
alarm_logs = json.dumps(alarm_logs)
alarm_color = json.dumps(alarm_color)
site_id_info=' and site_id=%s;' % site_id
sql = 'update electricity_dashboard set value="'+status_flag+'" where field="status_flag"' + site_id_info
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value='+alarm_details+' where field="alarm_details"' + site_id_info
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value='+alarm_logs+' where field="alarm_logs"' + site_id_info
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value="'+alarm_color+'" where field="alarm_color"' + site_id_info
insertDBdata.update_data(mysql, sql)
return
def dashboardUpdate0(mysql,field_list):
"""Not use after multi-site"""
print 'begin dashboard data update:',field_list
if field_list:
lasttimedata=field_list.pop(0)
for field in field_list:
print 'update field:',field
dashboard_update_sql="UPDATE electricity_dashboard SET value=%s WHERE electricity_dashboard.field = '%s';"%(field[1],field[0])
#sql_update_field="UPDATE `electricity_dashboard` SET `value` = '%s' WHERE `electricity_dashboard`.`field` = '%s';"%(field[1],field[0])
print dashboard_update_sql
insertDBdata.update_data(mysql, dashboard_update_sql)
dashboard_update_sql="UPDATE `electricity_dashboard` SET `date_time` = '%s' WHERE `electricity_dashboard`.`field` = '%s';"%(lasttimedata[1],lasttimedata[0])
insertDBdata.update_data(mysql, dashboard_update_sql)
return
def emsDataUpdate(mysql,dbdata):
"""
Parameters:
mysql: mysql connection to EMS DB
electricity_data: json type, data from agent/client
Return values:
"""
ems_data = json.loads(dbdata)
if ems_data :
data_head=ems_data.pop(0)
if ems_data and len(data_head)>=3:
data_type=data_head[0]
site_id=data_head[1]
data_length=data_head[2]
if data_length!=len(ems_data):
print 'Data integrity error'
return
if data_type==ALARM_HEAD:
#receive data is alarm data
#ems_data.pop(0)
#print 'istdata.pop(0)',listdata
print 'start event log insert', datetime.datetime.now()
eventInsert(mysql,ems_data)
print 'eventInsert===========================',datetime.datetime.now()
elif data_type==DASHBOARD_HEAD:
#receive data is alarm data
#print 'istdata.pop(0)',listdata
print 'update dashboard fields',datetime.datetime.now()
dashboardUpdate(mysql,ems_data,site_id)
print 'dashboardUpdate===========================',datetime.datetime.now()
updateDashboardAlarm(mysql, site_id)
print 'updateDashboardAlarm===========================',datetime.datetime.now()
elif data_type==ELECTRICITY_HEAD:
print 'start electricity insert, including virtual data'
print 'electricity_data=',ems_data
electricityInsert(mysql,ems_data) #insert data to electricity
last_measurement_data=ems_data[-1]
latest_insert_db_time=last_measurement_data[0]
field_name='lastTime'
dashboard_update_sql="UPDATE `electricity_dashboard` SET `date_time` = '%s' WHERE `electricity_dashboard`.`field` = '%s' and site_id = '%s';"%(latest_insert_db_time,field_name,site_id)
insertDBdata.update_data(mysql, dashboard_update_sql)
else:
pass
else:
#empty data
pass
return
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
def dashboardReset():
alarm_color = [[], [], [], []] # Flag color for the breakdown alarm logs
alarm_details = [[], [], [], []] # Alarm text for the breakdown alarm logs
status_flag = [3, 3, 3, 3] # For the four lights on alarm panel, 0:green, 1:yellow, 2:red, 3:grey
alarm_logs = []
PUETable = []
efficiencyTable = []
loadTable = []
realTimePUE = 0.0
realTimeCO2 = 0.0
realTimeEfficiency = 0.0
realTimeLoad = 0.0
siteAbsoluteLoad = 0.0
PUETable = json.dumps(PUETable)
efficiencyTable = json.dumps(efficiencyTable)
loadTable = json.dumps(loadTable)
sql = 'update electricity_dashboard set value="'+PUETable+'" where field="PUETable"'
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value="'+efficiencyTable+'" where field="efficiencyTable"'
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value="'+loadTable+'" where field="loadTable"'
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value='+str(realTimePUE)+' where field="realTimePUE"'
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value='+str(realTimeCO2)+' where field="realTimeCO2"'
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value='+str(realTimeEfficiency)+' where field="realTimeEfficiency"'
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value='+str(realTimeLoad)+' where field="realTimeLoad"'
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value='+str(siteAbsoluteLoad)+' where field="siteAbsoluteLoad"'
insertDBdata.update_data(mysql, sql)
status_flag = json.dumps(status_flag)
alarm_details = json.dumps(alarm_details)
alarm_logs = json.dumps(alarm_logs)
alarm_color = json.dumps(alarm_color)
sql = 'update electricity_dashboard set value="'+status_flag+'" where field="status_flag";'
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value="'+alarm_details+'" where field="alarm_details";'
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value="'+alarm_logs+'" where field="alarm_logs";'
insertDBdata.update_data(mysql, sql)
sql = 'update electricity_dashboard set value="'+alarm_color+'" where field="alarm_color"'
insertDBdata.update_data(mysql, sql)