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)