MyEMS /
myems
| @@ 9-203 (lines=195) @@ | ||
| 6 | import config |
|
| 7 | ||
| 8 | ||
| 9 | class Reporting: |
|
| 10 | def __init__(self): |
|
| 11 | """Initializes Class""" |
|
| 12 | pass |
|
| 13 | ||
| 14 | @staticmethod |
|
| 15 | def on_options(req, resp, id_): |
|
| 16 | _ = req |
|
| 17 | resp.status = falcon.HTTP_200 |
|
| 18 | _ = id_ |
|
| 19 | ||
| 20 | #################################################################################################################### |
|
| 21 | # PROCEDURES |
|
| 22 | # Step 1: valid parameters |
|
| 23 | # Step 2: query the energy storage power station |
|
| 24 | # Step 3: query associated containers |
|
| 25 | # Step 4: query analog points latest values |
|
| 26 | # Step 5: query energy points latest values |
|
| 27 | # Step 6: query digital points latest values |
|
| 28 | # Step 7: query the points of meters |
|
| 29 | # Step 8: construct the report |
|
| 30 | #################################################################################################################### |
|
| 31 | @staticmethod |
|
| 32 | def on_get(req, resp, id_): |
|
| 33 | if 'API-KEY' not in req.headers or \ |
|
| 34 | not isinstance(req.headers['API-KEY'], str) or \ |
|
| 35 | len(str.strip(req.headers['API-KEY'])) == 0: |
|
| 36 | access_control(req) |
|
| 37 | else: |
|
| 38 | api_key_control(req) |
|
| 39 | ||
| 40 | ################################################################################################################ |
|
| 41 | # Step 1: valid parameters |
|
| 42 | ################################################################################################################ |
|
| 43 | if not id_.isdigit() or int(id_) <= 0: |
|
| 44 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
|
| 45 | description='API.INVALID_ENERGY_STORAGE_POWER_STATION_ID') |
|
| 46 | energy_storage_power_station_id = id_ |
|
| 47 | ################################################################################################################ |
|
| 48 | # Step 2: query the energy storage power station |
|
| 49 | ################################################################################################################ |
|
| 50 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
|
| 51 | cursor_system = cnx_system.cursor() |
|
| 52 | ||
| 53 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
|
| 54 | cursor_historical = cnx_historical.cursor() |
|
| 55 | ||
| 56 | if energy_storage_power_station_id is not None: |
|
| 57 | query = (" SELECT id, name, uuid " |
|
| 58 | " FROM tbl_energy_storage_power_stations " |
|
| 59 | " WHERE id = %s ") |
|
| 60 | cursor_system.execute(query, (energy_storage_power_station_id,)) |
|
| 61 | row = cursor_system.fetchone() |
|
| 62 | if row is None: |
|
| 63 | cursor_system.close() |
|
| 64 | cnx_system.close() |
|
| 65 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', |
|
| 66 | description='API.ENERGY_STORAGE_POWER_STATION_NOT_FOUND') |
|
| 67 | ||
| 68 | # query all points |
|
| 69 | query = (" SELECT id, name, units, description " |
|
| 70 | " FROM tbl_points ") |
|
| 71 | cursor_system.execute(query) |
|
| 72 | rows = cursor_system.fetchall() |
|
| 73 | ||
| 74 | points_dict = dict() |
|
| 75 | if rows is not None and len(rows) > 0: |
|
| 76 | for row in rows: |
|
| 77 | points_dict[row[0]] = [row[1], row[2], row[3]] |
|
| 78 | ################################################################################################################ |
|
| 79 | # Step 3: query associated containers |
|
| 80 | ################################################################################################################ |
|
| 81 | container_list = list() |
|
| 82 | cursor_system.execute(" SELECT c.id, c.name, c.uuid " |
|
| 83 | " FROM tbl_energy_storage_power_stations_containers espsc, " |
|
| 84 | " tbl_energy_storage_containers c " |
|
| 85 | " WHERE espsc.energy_storage_power_station_id = %s " |
|
| 86 | " AND espsc.energy_storage_container_id = c.id ", |
|
| 87 | (energy_storage_power_station_id,)) |
|
| 88 | rows_containers = cursor_system.fetchall() |
|
| 89 | if rows_containers is not None and len(rows_containers) > 0: |
|
| 90 | for row_container in rows_containers: |
|
| 91 | container_list.append({"id": row_container[0], |
|
| 92 | "name": row_container[1], |
|
| 93 | "uuid": row_container[2]}) |
|
| 94 | print('container_list:' + str(container_list)) |
|
| 95 | ||
| 96 | ################################################################################################################ |
|
| 97 | # Step 4: query analog points latest values |
|
| 98 | ################################################################################################################ |
|
| 99 | latest_value_dict = dict() |
|
| 100 | query = (" SELECT point_id, actual_value " |
|
| 101 | " FROM tbl_analog_value_latest " |
|
| 102 | " WHERE utc_date_time > %s ") |
|
| 103 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 104 | rows = cursor_historical.fetchall() |
|
| 105 | if rows is not None and len(rows) > 0: |
|
| 106 | for row in rows: |
|
| 107 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 108 | points_dict[row[0]][1], |
|
| 109 | points_dict[row[0]][2], |
|
| 110 | row[1]] |
|
| 111 | ||
| 112 | ################################################################################################################ |
|
| 113 | # Step 5: query energy points latest values |
|
| 114 | ################################################################################################################ |
|
| 115 | query = (" SELECT point_id, actual_value " |
|
| 116 | " FROM tbl_energy_value_latest " |
|
| 117 | " WHERE utc_date_time > %s ") |
|
| 118 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 119 | rows = cursor_historical.fetchall() |
|
| 120 | if rows is not None and len(rows) > 0: |
|
| 121 | for row in rows: |
|
| 122 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 123 | points_dict[row[0]][1], |
|
| 124 | points_dict[row[0]][2], |
|
| 125 | row[1]] |
|
| 126 | ||
| 127 | ################################################################################################################ |
|
| 128 | # Step 6: query digital points latest values |
|
| 129 | ################################################################################################################ |
|
| 130 | query = (" SELECT point_id, actual_value " |
|
| 131 | " FROM tbl_digital_value_latest " |
|
| 132 | " WHERE utc_date_time > %s ") |
|
| 133 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 134 | rows = cursor_historical.fetchall() |
|
| 135 | if rows is not None and len(rows) > 0: |
|
| 136 | for row in rows: |
|
| 137 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 138 | points_dict[row[0]][1], |
|
| 139 | points_dict[row[0]][2], |
|
| 140 | row[1]] |
|
| 141 | ||
| 142 | ################################################################################################################ |
|
| 143 | # Step 7: query the points of meters |
|
| 144 | ################################################################################################################ |
|
| 145 | # query all points with units |
|
| 146 | query = (" SELECT id, units " |
|
| 147 | " FROM tbl_points ") |
|
| 148 | cursor_system.execute(query) |
|
| 149 | rows = cursor_system.fetchall() |
|
| 150 | ||
| 151 | units_dict = dict() |
|
| 152 | if rows is not None and len(rows) > 0: |
|
| 153 | for row in rows: |
|
| 154 | units_dict[row[0]] = row[1] |
|
| 155 | ||
| 156 | # query meter parameters |
|
| 157 | meter_list = list() |
|
| 158 | for container in container_list: |
|
| 159 | cursor_system.execute(" SELECT charge_meter_id, discharge_meter_id " |
|
| 160 | " FROM tbl_energy_storage_containers_batteries " |
|
| 161 | " WHERE energy_storage_container_id = %s " |
|
| 162 | " ORDER BY id ", |
|
| 163 | (container['id'],)) |
|
| 164 | rows_meters = cursor_system.fetchall() |
|
| 165 | if rows_meters is not None and len(rows_meters) > 0: |
|
| 166 | for row in rows_meters: |
|
| 167 | charge_meter = dict() |
|
| 168 | charge_meter['id'] = row[0] |
|
| 169 | charge_meter['points'] = list() |
|
| 170 | meter_list.append(charge_meter) |
|
| 171 | discharge_meter = dict() |
|
| 172 | discharge_meter['id'] = row[1] |
|
| 173 | discharge_meter['points'] = list() |
|
| 174 | meter_list.append(discharge_meter) |
|
| 175 | ||
| 176 | for index, meter in enumerate(meter_list): |
|
| 177 | cursor_system.execute(" SELECT p.id " |
|
| 178 | " FROM tbl_meters_points mp, tbl_points p " |
|
| 179 | " WHERE mp.meter_id = %s AND mp.point_id = p.id " |
|
| 180 | " ORDER BY mp.id ", |
|
| 181 | (meter['id'],)) |
|
| 182 | rows_points = cursor_system.fetchall() |
|
| 183 | if rows_points is not None and len(rows_points) > 0: |
|
| 184 | point_list = list() |
|
| 185 | for row in rows_points: |
|
| 186 | point = latest_value_dict.get(row[0], None) |
|
| 187 | if point is not None: |
|
| 188 | point_list.append(point) |
|
| 189 | meter_list[index]['points'] = point_list |
|
| 190 | ||
| 191 | if cursor_system: |
|
| 192 | cursor_system.close() |
|
| 193 | if cnx_system: |
|
| 194 | cnx_system.close() |
|
| 195 | ||
| 196 | if cursor_historical: |
|
| 197 | cursor_historical.close() |
|
| 198 | if cnx_historical: |
|
| 199 | cnx_historical.close() |
|
| 200 | ################################################################################################################ |
|
| 201 | # Step 8: construct the report |
|
| 202 | ################################################################################################################ |
|
| 203 | resp.text = json.dumps(meter_list) |
|
| 204 | ||
| @@ 9-202 (lines=194) @@ | ||
| 6 | import config |
|
| 7 | ||
| 8 | ||
| 9 | class Reporting: |
|
| 10 | def __init__(self): |
|
| 11 | """Initializes Class""" |
|
| 12 | pass |
|
| 13 | ||
| 14 | @staticmethod |
|
| 15 | def on_options(req, resp, id_): |
|
| 16 | _ = req |
|
| 17 | resp.status = falcon.HTTP_200 |
|
| 18 | _ = id_ |
|
| 19 | ||
| 20 | #################################################################################################################### |
|
| 21 | # PROCEDURES |
|
| 22 | # Step 1: valid parameters |
|
| 23 | # Step 2: query the energy storage power station |
|
| 24 | # Step 3: query associated containers |
|
| 25 | # Step 4: query analog points latest values |
|
| 26 | # Step 5: query energy points latest values |
|
| 27 | # Step 6: query digital points latest values |
|
| 28 | # Step 7: query the points of BMSes |
|
| 29 | # Step 8: construct the report |
|
| 30 | #################################################################################################################### |
|
| 31 | @staticmethod |
|
| 32 | def on_get(req, resp, id_): |
|
| 33 | if 'API-KEY' not in req.headers or \ |
|
| 34 | not isinstance(req.headers['API-KEY'], str) or \ |
|
| 35 | len(str.strip(req.headers['API-KEY'])) == 0: |
|
| 36 | access_control(req) |
|
| 37 | else: |
|
| 38 | api_key_control(req) |
|
| 39 | ||
| 40 | ################################################################################################################ |
|
| 41 | # Step 1: valid parameters |
|
| 42 | ################################################################################################################ |
|
| 43 | if not id_.isdigit() or int(id_) <= 0: |
|
| 44 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
|
| 45 | description='API.INVALID_ENERGY_STORAGE_POWER_STATION_ID') |
|
| 46 | energy_storage_power_station_id = id_ |
|
| 47 | ################################################################################################################ |
|
| 48 | # Step 2: query the energy storage power station |
|
| 49 | ################################################################################################################ |
|
| 50 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
|
| 51 | cursor_system = cnx_system.cursor() |
|
| 52 | ||
| 53 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
|
| 54 | cursor_historical = cnx_historical.cursor() |
|
| 55 | ||
| 56 | if energy_storage_power_station_id is not None: |
|
| 57 | query = (" SELECT id, name, uuid " |
|
| 58 | " FROM tbl_energy_storage_power_stations " |
|
| 59 | " WHERE id = %s ") |
|
| 60 | cursor_system.execute(query, (energy_storage_power_station_id,)) |
|
| 61 | row = cursor_system.fetchone() |
|
| 62 | if row is None: |
|
| 63 | cursor_system.close() |
|
| 64 | cnx_system.close() |
|
| 65 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', |
|
| 66 | description='API.ENERGY_STORAGE_POWER_STATION_NOT_FOUND') |
|
| 67 | ||
| 68 | # query all points |
|
| 69 | query = (" SELECT id, name, units, description " |
|
| 70 | " FROM tbl_points ") |
|
| 71 | cursor_system.execute(query) |
|
| 72 | rows = cursor_system.fetchall() |
|
| 73 | ||
| 74 | points_dict = dict() |
|
| 75 | if rows is not None and len(rows) > 0: |
|
| 76 | for row in rows: |
|
| 77 | points_dict[row[0]] = [row[1], row[2], row[3]] |
|
| 78 | ################################################################################################################ |
|
| 79 | # Step 3: query associated containers |
|
| 80 | ################################################################################################################ |
|
| 81 | container_list = list() |
|
| 82 | cursor_system.execute(" SELECT c.id, c.name, c.uuid " |
|
| 83 | " FROM tbl_energy_storage_power_stations_containers espsc, " |
|
| 84 | " tbl_energy_storage_containers c " |
|
| 85 | " WHERE espsc.energy_storage_power_station_id = %s " |
|
| 86 | " AND espsc.energy_storage_container_id = c.id ", |
|
| 87 | (energy_storage_power_station_id,)) |
|
| 88 | rows_containers = cursor_system.fetchall() |
|
| 89 | if rows_containers is not None and len(rows_containers) > 0: |
|
| 90 | for row_container in rows_containers: |
|
| 91 | container_list.append({"id": row_container[0], |
|
| 92 | "name": row_container[1], |
|
| 93 | "uuid": row_container[2]}) |
|
| 94 | print('container_list:' + str(container_list)) |
|
| 95 | ||
| 96 | ################################################################################################################ |
|
| 97 | # Step 4: query analog points latest values |
|
| 98 | ################################################################################################################ |
|
| 99 | latest_value_dict = dict() |
|
| 100 | query = (" SELECT point_id, actual_value " |
|
| 101 | " FROM tbl_analog_value_latest " |
|
| 102 | " WHERE utc_date_time > %s ") |
|
| 103 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 104 | rows = cursor_historical.fetchall() |
|
| 105 | if rows is not None and len(rows) > 0: |
|
| 106 | for row in rows: |
|
| 107 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 108 | points_dict[row[0]][1], |
|
| 109 | points_dict[row[0]][2], |
|
| 110 | row[1]] |
|
| 111 | ||
| 112 | ################################################################################################################ |
|
| 113 | # Step 5: query energy points latest values |
|
| 114 | ################################################################################################################ |
|
| 115 | query = (" SELECT point_id, actual_value " |
|
| 116 | " FROM tbl_energy_value_latest " |
|
| 117 | " WHERE utc_date_time > %s ") |
|
| 118 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 119 | rows = cursor_historical.fetchall() |
|
| 120 | if rows is not None and len(rows) > 0: |
|
| 121 | for row in rows: |
|
| 122 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 123 | points_dict[row[0]][1], |
|
| 124 | points_dict[row[0]][2], |
|
| 125 | row[1]] |
|
| 126 | ||
| 127 | ################################################################################################################ |
|
| 128 | # Step 6: query digital points latest values |
|
| 129 | ################################################################################################################ |
|
| 130 | query = (" SELECT point_id, actual_value " |
|
| 131 | " FROM tbl_digital_value_latest " |
|
| 132 | " WHERE utc_date_time > %s ") |
|
| 133 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 134 | rows = cursor_historical.fetchall() |
|
| 135 | if rows is not None and len(rows) > 0: |
|
| 136 | for row in rows: |
|
| 137 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 138 | points_dict[row[0]][1], |
|
| 139 | points_dict[row[0]][2], |
|
| 140 | row[1]] |
|
| 141 | ||
| 142 | ################################################################################################################ |
|
| 143 | # Step 7: query the points of BMSes |
|
| 144 | ################################################################################################################ |
|
| 145 | # query all points with units |
|
| 146 | query = (" SELECT id, units " |
|
| 147 | " FROM tbl_points ") |
|
| 148 | cursor_system.execute(query) |
|
| 149 | rows = cursor_system.fetchall() |
|
| 150 | ||
| 151 | units_dict = dict() |
|
| 152 | if rows is not None and len(rows) > 0: |
|
| 153 | for row in rows: |
|
| 154 | units_dict[row[0]] = row[1] |
|
| 155 | ||
| 156 | # query bms parameters |
|
| 157 | bms_list = list() |
|
| 158 | ||
| 159 | for container in container_list: |
|
| 160 | cursor_system.execute(" SELECT id, name, uuid " |
|
| 161 | " FROM tbl_energy_storage_containers_batteries " |
|
| 162 | " WHERE energy_storage_container_id = %s " |
|
| 163 | " ORDER BY id ", |
|
| 164 | (container['id'],)) |
|
| 165 | rows_bmses = cursor_system.fetchall() |
|
| 166 | if rows_bmses is not None and len(rows_bmses) > 0: |
|
| 167 | for row in rows_bmses: |
|
| 168 | current_bms = dict() |
|
| 169 | current_bms['id'] = row[0] |
|
| 170 | current_bms['name'] = row[1] |
|
| 171 | current_bms['uuid'] = row[2] |
|
| 172 | current_bms['points'] = list() |
|
| 173 | bms_list.append(current_bms) |
|
| 174 | ||
| 175 | for index, bms in enumerate(bms_list): |
|
| 176 | cursor_system.execute(" SELECT p.id " |
|
| 177 | " FROM tbl_energy_storage_containers_bmses_points bp, tbl_points p " |
|
| 178 | " WHERE bp.bms_id = %s AND bp.point_id = p.id " |
|
| 179 | " ORDER BY bp.id ", |
|
| 180 | (bms['id'],)) |
|
| 181 | rows_points = cursor_system.fetchall() |
|
| 182 | if rows_points is not None and len(rows_points) > 0: |
|
| 183 | point_list = list() |
|
| 184 | for row in rows_points: |
|
| 185 | point = latest_value_dict.get(row[0], None) |
|
| 186 | if point is not None: |
|
| 187 | point_list.append(point) |
|
| 188 | bms_list[index]['points'] = point_list |
|
| 189 | ||
| 190 | if cursor_system: |
|
| 191 | cursor_system.close() |
|
| 192 | if cnx_system: |
|
| 193 | cnx_system.close() |
|
| 194 | ||
| 195 | if cursor_historical: |
|
| 196 | cursor_historical.close() |
|
| 197 | if cnx_historical: |
|
| 198 | cnx_historical.close() |
|
| 199 | ################################################################################################################ |
|
| 200 | # Step 8: construct the report |
|
| 201 | ################################################################################################################ |
|
| 202 | resp.text = json.dumps(bms_list) |
|
| 203 | ||
| @@ 9-201 (lines=193) @@ | ||
| 6 | import config |
|
| 7 | ||
| 8 | ||
| 9 | class Reporting: |
|
| 10 | def __init__(self): |
|
| 11 | """Initializes Class""" |
|
| 12 | pass |
|
| 13 | ||
| 14 | @staticmethod |
|
| 15 | def on_options(req, resp, id_): |
|
| 16 | _ = req |
|
| 17 | resp.status = falcon.HTTP_200 |
|
| 18 | _ = id_ |
|
| 19 | ||
| 20 | #################################################################################################################### |
|
| 21 | # PROCEDURES |
|
| 22 | # Step 1: valid parameters |
|
| 23 | # Step 2: query the energy storage power station |
|
| 24 | # Step 3: query associated containers |
|
| 25 | # Step 4: query analog points latest values |
|
| 26 | # Step 5: query energy points latest values |
|
| 27 | # Step 6: query digital points latest values |
|
| 28 | # Step 7: query the points of grids |
|
| 29 | # Step 8: construct the report |
|
| 30 | #################################################################################################################### |
|
| 31 | @staticmethod |
|
| 32 | def on_get(req, resp, id_): |
|
| 33 | if 'API-KEY' not in req.headers or \ |
|
| 34 | not isinstance(req.headers['API-KEY'], str) or \ |
|
| 35 | len(str.strip(req.headers['API-KEY'])) == 0: |
|
| 36 | access_control(req) |
|
| 37 | else: |
|
| 38 | api_key_control(req) |
|
| 39 | ||
| 40 | ################################################################################################################ |
|
| 41 | # Step 1: valid parameters |
|
| 42 | ################################################################################################################ |
|
| 43 | if not id_.isdigit() or int(id_) <= 0: |
|
| 44 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
|
| 45 | description='API.INVALID_ENERGY_STORAGE_POWER_STATION_ID') |
|
| 46 | energy_storage_power_station_id = id_ |
|
| 47 | ################################################################################################################ |
|
| 48 | # Step 2: query the energy storage power station |
|
| 49 | ################################################################################################################ |
|
| 50 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
|
| 51 | cursor_system = cnx_system.cursor() |
|
| 52 | ||
| 53 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
|
| 54 | cursor_historical = cnx_historical.cursor() |
|
| 55 | ||
| 56 | if energy_storage_power_station_id is not None: |
|
| 57 | query = (" SELECT id, name, uuid " |
|
| 58 | " FROM tbl_energy_storage_power_stations " |
|
| 59 | " WHERE id = %s ") |
|
| 60 | cursor_system.execute(query, (energy_storage_power_station_id,)) |
|
| 61 | row = cursor_system.fetchone() |
|
| 62 | if row is None: |
|
| 63 | cursor_system.close() |
|
| 64 | cnx_system.close() |
|
| 65 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', |
|
| 66 | description='API.ENERGY_STORAGE_POWER_STATION_NOT_FOUND') |
|
| 67 | ||
| 68 | # query all points |
|
| 69 | query = (" SELECT id, name, units, description " |
|
| 70 | " FROM tbl_points ") |
|
| 71 | cursor_system.execute(query) |
|
| 72 | rows = cursor_system.fetchall() |
|
| 73 | ||
| 74 | points_dict = dict() |
|
| 75 | if rows is not None and len(rows) > 0: |
|
| 76 | for row in rows: |
|
| 77 | points_dict[row[0]] = [row[1], row[2], row[3]] |
|
| 78 | ################################################################################################################ |
|
| 79 | # Step 3: query associated containers |
|
| 80 | ################################################################################################################ |
|
| 81 | container_list = list() |
|
| 82 | cursor_system.execute(" SELECT c.id, c.name, c.uuid " |
|
| 83 | " FROM tbl_energy_storage_power_stations_containers espsc, " |
|
| 84 | " tbl_energy_storage_containers c " |
|
| 85 | " WHERE espsc.energy_storage_power_station_id = %s " |
|
| 86 | " AND espsc.energy_storage_container_id = c.id ", |
|
| 87 | (energy_storage_power_station_id,)) |
|
| 88 | rows_containers = cursor_system.fetchall() |
|
| 89 | if rows_containers is not None and len(rows_containers) > 0: |
|
| 90 | for row_container in rows_containers: |
|
| 91 | container_list.append({"id": row_container[0], |
|
| 92 | "name": row_container[1], |
|
| 93 | "uuid": row_container[2]}) |
|
| 94 | print('container_list:' + str(container_list)) |
|
| 95 | ||
| 96 | ################################################################################################################ |
|
| 97 | # Step 4: query analog points latest values |
|
| 98 | ################################################################################################################ |
|
| 99 | latest_value_dict = dict() |
|
| 100 | query = (" SELECT point_id, actual_value " |
|
| 101 | " FROM tbl_analog_value_latest " |
|
| 102 | " WHERE utc_date_time > %s ") |
|
| 103 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 104 | rows = cursor_historical.fetchall() |
|
| 105 | if rows is not None and len(rows) > 0: |
|
| 106 | for row in rows: |
|
| 107 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 108 | points_dict[row[0]][1], |
|
| 109 | points_dict[row[0]][2], |
|
| 110 | row[1]] |
|
| 111 | ||
| 112 | ################################################################################################################ |
|
| 113 | # Step 5: query energy points latest values |
|
| 114 | ################################################################################################################ |
|
| 115 | query = (" SELECT point_id, actual_value " |
|
| 116 | " FROM tbl_energy_value_latest " |
|
| 117 | " WHERE utc_date_time > %s ") |
|
| 118 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 119 | rows = cursor_historical.fetchall() |
|
| 120 | if rows is not None and len(rows) > 0: |
|
| 121 | for row in rows: |
|
| 122 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 123 | points_dict[row[0]][1], |
|
| 124 | points_dict[row[0]][2], |
|
| 125 | row[1]] |
|
| 126 | ||
| 127 | ################################################################################################################ |
|
| 128 | # Step 6: query digital points latest values |
|
| 129 | ################################################################################################################ |
|
| 130 | query = (" SELECT point_id, actual_value " |
|
| 131 | " FROM tbl_digital_value_latest " |
|
| 132 | " WHERE utc_date_time > %s ") |
|
| 133 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 134 | rows = cursor_historical.fetchall() |
|
| 135 | if rows is not None and len(rows) > 0: |
|
| 136 | for row in rows: |
|
| 137 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 138 | points_dict[row[0]][1], |
|
| 139 | points_dict[row[0]][2], |
|
| 140 | row[1]] |
|
| 141 | ||
| 142 | ################################################################################################################ |
|
| 143 | # Step 7: query the points of grids |
|
| 144 | ################################################################################################################ |
|
| 145 | # query all points with units |
|
| 146 | query = (" SELECT id, units " |
|
| 147 | " FROM tbl_points ") |
|
| 148 | cursor_system.execute(query) |
|
| 149 | rows = cursor_system.fetchall() |
|
| 150 | ||
| 151 | units_dict = dict() |
|
| 152 | if rows is not None and len(rows) > 0: |
|
| 153 | for row in rows: |
|
| 154 | units_dict[row[0]] = row[1] |
|
| 155 | ||
| 156 | # query grid parameters |
|
| 157 | grid_list = list() |
|
| 158 | for container in container_list: |
|
| 159 | cursor_system.execute(" SELECT id, name, uuid " |
|
| 160 | " FROM tbl_energy_storage_containers_grids " |
|
| 161 | " WHERE energy_storage_container_id = %s " |
|
| 162 | " ORDER BY id ", |
|
| 163 | (container['id'],)) |
|
| 164 | rows_grids = cursor_system.fetchall() |
|
| 165 | if rows_grids is not None and len(rows_grids) > 0: |
|
| 166 | for row in rows_grids: |
|
| 167 | current_grid = dict() |
|
| 168 | current_grid['id'] = row[0] |
|
| 169 | current_grid['name'] = row[1] |
|
| 170 | current_grid['uuid'] = row[2] |
|
| 171 | current_grid['points'] = list() |
|
| 172 | grid_list.append(current_grid) |
|
| 173 | ||
| 174 | for index, grid in enumerate(grid_list): |
|
| 175 | cursor_system.execute(" SELECT p.id " |
|
| 176 | " FROM tbl_energy_storage_containers_grids_points bp, tbl_points p " |
|
| 177 | " WHERE bp.grid_id = %s AND bp.point_id = p.id " |
|
| 178 | " ORDER BY bp.id ", |
|
| 179 | (grid['id'],)) |
|
| 180 | rows_points = cursor_system.fetchall() |
|
| 181 | if rows_points is not None and len(rows_points) > 0: |
|
| 182 | point_list = list() |
|
| 183 | for row in rows_points: |
|
| 184 | point = latest_value_dict.get(row[0], None) |
|
| 185 | if point is not None: |
|
| 186 | point_list.append(point) |
|
| 187 | grid_list[index]['points'] = point_list |
|
| 188 | ||
| 189 | if cursor_system: |
|
| 190 | cursor_system.close() |
|
| 191 | if cnx_system: |
|
| 192 | cnx_system.close() |
|
| 193 | ||
| 194 | if cursor_historical: |
|
| 195 | cursor_historical.close() |
|
| 196 | if cnx_historical: |
|
| 197 | cnx_historical.close() |
|
| 198 | ################################################################################################################ |
|
| 199 | # Step 8: construct the report |
|
| 200 | ################################################################################################################ |
|
| 201 | resp.text = json.dumps(grid_list) |
|
| 202 | ||
| @@ 9-201 (lines=193) @@ | ||
| 6 | import config |
|
| 7 | ||
| 8 | ||
| 9 | class Reporting: |
|
| 10 | def __init__(self): |
|
| 11 | """Initializes Class""" |
|
| 12 | pass |
|
| 13 | ||
| 14 | @staticmethod |
|
| 15 | def on_options(req, resp, id_): |
|
| 16 | _ = req |
|
| 17 | resp.status = falcon.HTTP_200 |
|
| 18 | _ = id_ |
|
| 19 | ||
| 20 | #################################################################################################################### |
|
| 21 | # PROCEDURES |
|
| 22 | # Step 1: valid parameters |
|
| 23 | # Step 2: query the energy storage power station |
|
| 24 | # Step 3: query associated containers |
|
| 25 | # Step 4: query analog points latest values |
|
| 26 | # Step 5: query energy points latest values |
|
| 27 | # Step 6: query digital points latest values |
|
| 28 | # Step 7: query the points of power conversion systems |
|
| 29 | # Step 8: construct the report |
|
| 30 | #################################################################################################################### |
|
| 31 | @staticmethod |
|
| 32 | def on_get(req, resp, id_): |
|
| 33 | if 'API-KEY' not in req.headers or \ |
|
| 34 | not isinstance(req.headers['API-KEY'], str) or \ |
|
| 35 | len(str.strip(req.headers['API-KEY'])) == 0: |
|
| 36 | access_control(req) |
|
| 37 | else: |
|
| 38 | api_key_control(req) |
|
| 39 | ||
| 40 | ################################################################################################################ |
|
| 41 | # Step 1: valid parameters |
|
| 42 | ################################################################################################################ |
|
| 43 | if not id_.isdigit() or int(id_) <= 0: |
|
| 44 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
|
| 45 | description='API.INVALID_ENERGY_STORAGE_POWER_STATION_ID') |
|
| 46 | energy_storage_power_station_id = id_ |
|
| 47 | ################################################################################################################ |
|
| 48 | # Step 2: query the energy storage power station |
|
| 49 | ################################################################################################################ |
|
| 50 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
|
| 51 | cursor_system = cnx_system.cursor() |
|
| 52 | ||
| 53 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
|
| 54 | cursor_historical = cnx_historical.cursor() |
|
| 55 | ||
| 56 | if energy_storage_power_station_id is not None: |
|
| 57 | query = (" SELECT id, name, uuid " |
|
| 58 | " FROM tbl_energy_storage_power_stations " |
|
| 59 | " WHERE id = %s ") |
|
| 60 | cursor_system.execute(query, (energy_storage_power_station_id,)) |
|
| 61 | row = cursor_system.fetchone() |
|
| 62 | if row is None: |
|
| 63 | cursor_system.close() |
|
| 64 | cnx_system.close() |
|
| 65 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', |
|
| 66 | description='API.ENERGY_STORAGE_POWER_STATION_NOT_FOUND') |
|
| 67 | ||
| 68 | # query all points |
|
| 69 | query = (" SELECT id, name, units, description " |
|
| 70 | " FROM tbl_points ") |
|
| 71 | cursor_system.execute(query) |
|
| 72 | rows = cursor_system.fetchall() |
|
| 73 | ||
| 74 | points_dict = dict() |
|
| 75 | if rows is not None and len(rows) > 0: |
|
| 76 | for row in rows: |
|
| 77 | points_dict[row[0]] = [row[1], row[2], row[3]] |
|
| 78 | ################################################################################################################ |
|
| 79 | # Step 3: query associated containers |
|
| 80 | ################################################################################################################ |
|
| 81 | container_list = list() |
|
| 82 | cursor_system.execute(" SELECT c.id, c.name, c.uuid " |
|
| 83 | " FROM tbl_energy_storage_power_stations_containers espsc, " |
|
| 84 | " tbl_energy_storage_containers c " |
|
| 85 | " WHERE espsc.energy_storage_power_station_id = %s " |
|
| 86 | " AND espsc.energy_storage_container_id = c.id ", |
|
| 87 | (energy_storage_power_station_id,)) |
|
| 88 | rows_containers = cursor_system.fetchall() |
|
| 89 | if rows_containers is not None and len(rows_containers) > 0: |
|
| 90 | for row_container in rows_containers: |
|
| 91 | container_list.append({"id": row_container[0], |
|
| 92 | "name": row_container[1], |
|
| 93 | "uuid": row_container[2]}) |
|
| 94 | print('container_list:' + str(container_list)) |
|
| 95 | ||
| 96 | ################################################################################################################ |
|
| 97 | # Step 4: query analog points latest values |
|
| 98 | ################################################################################################################ |
|
| 99 | latest_value_dict = dict() |
|
| 100 | query = (" SELECT point_id, actual_value " |
|
| 101 | " FROM tbl_analog_value_latest " |
|
| 102 | " WHERE utc_date_time > %s ") |
|
| 103 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 104 | rows = cursor_historical.fetchall() |
|
| 105 | if rows is not None and len(rows) > 0: |
|
| 106 | for row in rows: |
|
| 107 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 108 | points_dict[row[0]][1], |
|
| 109 | points_dict[row[0]][2], |
|
| 110 | row[1]] |
|
| 111 | ||
| 112 | ################################################################################################################ |
|
| 113 | # Step 5: query energy points latest values |
|
| 114 | ################################################################################################################ |
|
| 115 | query = (" SELECT point_id, actual_value " |
|
| 116 | " FROM tbl_energy_value_latest " |
|
| 117 | " WHERE utc_date_time > %s ") |
|
| 118 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 119 | rows = cursor_historical.fetchall() |
|
| 120 | if rows is not None and len(rows) > 0: |
|
| 121 | for row in rows: |
|
| 122 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 123 | points_dict[row[0]][1], |
|
| 124 | points_dict[row[0]][2], |
|
| 125 | row[1]] |
|
| 126 | ||
| 127 | ################################################################################################################ |
|
| 128 | # Step 6: query digital points latest values |
|
| 129 | ################################################################################################################ |
|
| 130 | query = (" SELECT point_id, actual_value " |
|
| 131 | " FROM tbl_digital_value_latest " |
|
| 132 | " WHERE utc_date_time > %s ") |
|
| 133 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 134 | rows = cursor_historical.fetchall() |
|
| 135 | if rows is not None and len(rows) > 0: |
|
| 136 | for row in rows: |
|
| 137 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 138 | points_dict[row[0]][1], |
|
| 139 | points_dict[row[0]][2], |
|
| 140 | row[1]] |
|
| 141 | ||
| 142 | ################################################################################################################ |
|
| 143 | # Step 7: query the points of power conversion systems |
|
| 144 | ################################################################################################################ |
|
| 145 | # query all points with units |
|
| 146 | query = (" SELECT id, units " |
|
| 147 | " FROM tbl_points ") |
|
| 148 | cursor_system.execute(query) |
|
| 149 | rows = cursor_system.fetchall() |
|
| 150 | ||
| 151 | units_dict = dict() |
|
| 152 | if rows is not None and len(rows) > 0: |
|
| 153 | for row in rows: |
|
| 154 | units_dict[row[0]] = row[1] |
|
| 155 | ||
| 156 | # query pcs parameters |
|
| 157 | pcs_list = list() |
|
| 158 | for container in container_list: |
|
| 159 | cursor_system.execute(" SELECT id, name, uuid " |
|
| 160 | " FROM tbl_energy_storage_containers_power_conversion_systems " |
|
| 161 | " WHERE energy_storage_container_id = %s " |
|
| 162 | " ORDER BY id ", |
|
| 163 | (container['id'],)) |
|
| 164 | rows_pcses = cursor_system.fetchall() |
|
| 165 | if rows_pcses is not None and len(rows_pcses) > 0: |
|
| 166 | for row in rows_pcses: |
|
| 167 | current_pcs = dict() |
|
| 168 | current_pcs['id'] = row[0] |
|
| 169 | current_pcs['name'] = row[1] |
|
| 170 | current_pcs['uuid'] = row[2] |
|
| 171 | current_pcs['points'] = list() |
|
| 172 | pcs_list.append(current_pcs) |
|
| 173 | ||
| 174 | for index, pcs in enumerate(pcs_list): |
|
| 175 | cursor_system.execute(" SELECT p.id " |
|
| 176 | " FROM tbl_energy_storage_containers_pcses_points bp, tbl_points p " |
|
| 177 | " WHERE bp.pcs_id = %s AND bp.point_id = p.id " |
|
| 178 | " ORDER BY bp.id ", |
|
| 179 | (pcs['id'],)) |
|
| 180 | rows_points = cursor_system.fetchall() |
|
| 181 | if rows_points is not None and len(rows_points) > 0: |
|
| 182 | point_list = list() |
|
| 183 | for row in rows_points: |
|
| 184 | point = latest_value_dict.get(row[0], None) |
|
| 185 | if point is not None: |
|
| 186 | point_list.append(point) |
|
| 187 | pcs_list[index]['points'] = point_list |
|
| 188 | ||
| 189 | if cursor_system: |
|
| 190 | cursor_system.close() |
|
| 191 | if cnx_system: |
|
| 192 | cnx_system.close() |
|
| 193 | ||
| 194 | if cursor_historical: |
|
| 195 | cursor_historical.close() |
|
| 196 | if cnx_historical: |
|
| 197 | cnx_historical.close() |
|
| 198 | ################################################################################################################ |
|
| 199 | # Step 8: construct the report |
|
| 200 | ################################################################################################################ |
|
| 201 | resp.text = json.dumps(pcs_list) |
|
| 202 | ||
| @@ 9-201 (lines=193) @@ | ||
| 6 | import config |
|
| 7 | ||
| 8 | ||
| 9 | class Reporting: |
|
| 10 | def __init__(self): |
|
| 11 | """Initializes Class""" |
|
| 12 | pass |
|
| 13 | ||
| 14 | @staticmethod |
|
| 15 | def on_options(req, resp, id_): |
|
| 16 | _ = req |
|
| 17 | resp.status = falcon.HTTP_200 |
|
| 18 | _ = id_ |
|
| 19 | ||
| 20 | #################################################################################################################### |
|
| 21 | # PROCEDURES |
|
| 22 | # Step 1: valid parameters |
|
| 23 | # Step 2: query the energy storage power station |
|
| 24 | # Step 3: query associated containers |
|
| 25 | # Step 4: query analog points latest values |
|
| 26 | # Step 5: query energy points latest values |
|
| 27 | # Step 6: query digital points latest values |
|
| 28 | # Step 7: query the points of loads |
|
| 29 | # Step 8: construct the report |
|
| 30 | #################################################################################################################### |
|
| 31 | @staticmethod |
|
| 32 | def on_get(req, resp, id_): |
|
| 33 | if 'API-KEY' not in req.headers or \ |
|
| 34 | not isinstance(req.headers['API-KEY'], str) or \ |
|
| 35 | len(str.strip(req.headers['API-KEY'])) == 0: |
|
| 36 | access_control(req) |
|
| 37 | else: |
|
| 38 | api_key_control(req) |
|
| 39 | ||
| 40 | ################################################################################################################ |
|
| 41 | # Step 1: valid parameters |
|
| 42 | ################################################################################################################ |
|
| 43 | if not id_.isdigit() or int(id_) <= 0: |
|
| 44 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
|
| 45 | description='API.INVALID_ENERGY_STORAGE_POWER_STATION_ID') |
|
| 46 | energy_storage_power_station_id = id_ |
|
| 47 | ################################################################################################################ |
|
| 48 | # Step 2: query the energy storage power station |
|
| 49 | ################################################################################################################ |
|
| 50 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
|
| 51 | cursor_system = cnx_system.cursor() |
|
| 52 | ||
| 53 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
|
| 54 | cursor_historical = cnx_historical.cursor() |
|
| 55 | ||
| 56 | if energy_storage_power_station_id is not None: |
|
| 57 | query = (" SELECT id, name, uuid " |
|
| 58 | " FROM tbl_energy_storage_power_stations " |
|
| 59 | " WHERE id = %s ") |
|
| 60 | cursor_system.execute(query, (energy_storage_power_station_id,)) |
|
| 61 | row = cursor_system.fetchone() |
|
| 62 | if row is None: |
|
| 63 | cursor_system.close() |
|
| 64 | cnx_system.close() |
|
| 65 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', |
|
| 66 | description='API.ENERGY_STORAGE_POWER_STATION_NOT_FOUND') |
|
| 67 | ||
| 68 | # query all points |
|
| 69 | query = (" SELECT id, name, units, description " |
|
| 70 | " FROM tbl_points ") |
|
| 71 | cursor_system.execute(query) |
|
| 72 | rows = cursor_system.fetchall() |
|
| 73 | ||
| 74 | points_dict = dict() |
|
| 75 | if rows is not None and len(rows) > 0: |
|
| 76 | for row in rows: |
|
| 77 | points_dict[row[0]] = [row[1], row[2], row[3]] |
|
| 78 | ################################################################################################################ |
|
| 79 | # Step 3: query associated containers |
|
| 80 | ################################################################################################################ |
|
| 81 | container_list = list() |
|
| 82 | cursor_system.execute(" SELECT c.id, c.name, c.uuid " |
|
| 83 | " FROM tbl_energy_storage_power_stations_containers espsc, " |
|
| 84 | " tbl_energy_storage_containers c " |
|
| 85 | " WHERE espsc.energy_storage_power_station_id = %s " |
|
| 86 | " AND espsc.energy_storage_container_id = c.id ", |
|
| 87 | (energy_storage_power_station_id,)) |
|
| 88 | rows_containers = cursor_system.fetchall() |
|
| 89 | if rows_containers is not None and len(rows_containers) > 0: |
|
| 90 | for row_container in rows_containers: |
|
| 91 | container_list.append({"id": row_container[0], |
|
| 92 | "name": row_container[1], |
|
| 93 | "uuid": row_container[2]}) |
|
| 94 | print('container_list:' + str(container_list)) |
|
| 95 | ||
| 96 | ################################################################################################################ |
|
| 97 | # Step 4: query analog points latest values |
|
| 98 | ################################################################################################################ |
|
| 99 | latest_value_dict = dict() |
|
| 100 | query = (" SELECT point_id, actual_value " |
|
| 101 | " FROM tbl_analog_value_latest " |
|
| 102 | " WHERE utc_date_time > %s ") |
|
| 103 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 104 | rows = cursor_historical.fetchall() |
|
| 105 | if rows is not None and len(rows) > 0: |
|
| 106 | for row in rows: |
|
| 107 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 108 | points_dict[row[0]][1], |
|
| 109 | points_dict[row[0]][2], |
|
| 110 | row[1]] |
|
| 111 | ||
| 112 | ################################################################################################################ |
|
| 113 | # Step 5: query energy points latest values |
|
| 114 | ################################################################################################################ |
|
| 115 | query = (" SELECT point_id, actual_value " |
|
| 116 | " FROM tbl_energy_value_latest " |
|
| 117 | " WHERE utc_date_time > %s ") |
|
| 118 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 119 | rows = cursor_historical.fetchall() |
|
| 120 | if rows is not None and len(rows) > 0: |
|
| 121 | for row in rows: |
|
| 122 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 123 | points_dict[row[0]][1], |
|
| 124 | points_dict[row[0]][2], |
|
| 125 | row[1]] |
|
| 126 | ||
| 127 | ################################################################################################################ |
|
| 128 | # Step 6: query digital points latest values |
|
| 129 | ################################################################################################################ |
|
| 130 | query = (" SELECT point_id, actual_value " |
|
| 131 | " FROM tbl_digital_value_latest " |
|
| 132 | " WHERE utc_date_time > %s ") |
|
| 133 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 134 | rows = cursor_historical.fetchall() |
|
| 135 | if rows is not None and len(rows) > 0: |
|
| 136 | for row in rows: |
|
| 137 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 138 | points_dict[row[0]][1], |
|
| 139 | points_dict[row[0]][2], |
|
| 140 | row[1]] |
|
| 141 | ||
| 142 | ################################################################################################################ |
|
| 143 | # Step 7: query the points of loads |
|
| 144 | ################################################################################################################ |
|
| 145 | # query all points with units |
|
| 146 | query = (" SELECT id, units " |
|
| 147 | " FROM tbl_points ") |
|
| 148 | cursor_system.execute(query) |
|
| 149 | rows = cursor_system.fetchall() |
|
| 150 | ||
| 151 | units_dict = dict() |
|
| 152 | if rows is not None and len(rows) > 0: |
|
| 153 | for row in rows: |
|
| 154 | units_dict[row[0]] = row[1] |
|
| 155 | ||
| 156 | # query load parameters |
|
| 157 | load_list = list() |
|
| 158 | for container in container_list: |
|
| 159 | cursor_system.execute(" SELECT id, name, uuid " |
|
| 160 | " FROM tbl_energy_storage_containers_loads " |
|
| 161 | " WHERE energy_storage_container_id = %s " |
|
| 162 | " ORDER BY id ", |
|
| 163 | (container['id'],)) |
|
| 164 | rows_loads = cursor_system.fetchall() |
|
| 165 | if rows_loads is not None and len(rows_loads) > 0: |
|
| 166 | for row in rows_loads: |
|
| 167 | current_load = dict() |
|
| 168 | current_load['id'] = row[0] |
|
| 169 | current_load['name'] = row[1] |
|
| 170 | current_load['uuid'] = row[2] |
|
| 171 | current_load['points'] = list() |
|
| 172 | load_list.append(current_load) |
|
| 173 | ||
| 174 | for index, load in enumerate(load_list): |
|
| 175 | cursor_system.execute(" SELECT p.id " |
|
| 176 | " FROM tbl_energy_storage_containers_loads_points bp, tbl_points p " |
|
| 177 | " WHERE bp.load_id = %s AND bp.point_id = p.id " |
|
| 178 | " ORDER BY bp.id ", |
|
| 179 | (load['id'],)) |
|
| 180 | rows_points = cursor_system.fetchall() |
|
| 181 | if rows_points is not None and len(rows_points) > 0: |
|
| 182 | point_list = list() |
|
| 183 | for row in rows_points: |
|
| 184 | point = latest_value_dict.get(row[0], None) |
|
| 185 | if point is not None: |
|
| 186 | point_list.append(point) |
|
| 187 | load_list[index]['points'] = point_list |
|
| 188 | ||
| 189 | if cursor_system: |
|
| 190 | cursor_system.close() |
|
| 191 | if cnx_system: |
|
| 192 | cnx_system.close() |
|
| 193 | ||
| 194 | if cursor_historical: |
|
| 195 | cursor_historical.close() |
|
| 196 | if cnx_historical: |
|
| 197 | cnx_historical.close() |
|
| 198 | ################################################################################################################ |
|
| 199 | # Step 8: construct the report |
|
| 200 | ################################################################################################################ |
|
| 201 | resp.text = json.dumps(load_list) |
|
| 202 | ||
| @@ 9-200 (lines=192) @@ | ||
| 6 | import config |
|
| 7 | ||
| 8 | ||
| 9 | class Reporting: |
|
| 10 | def __init__(self): |
|
| 11 | """Initializes Class""" |
|
| 12 | pass |
|
| 13 | ||
| 14 | @staticmethod |
|
| 15 | def on_options(req, resp, id_): |
|
| 16 | _ = req |
|
| 17 | resp.status = falcon.HTTP_200 |
|
| 18 | _ = id_ |
|
| 19 | ||
| 20 | #################################################################################################################### |
|
| 21 | # PROCEDURES |
|
| 22 | # Step 1: valid parameters |
|
| 23 | # Step 2: query the energy storage power station |
|
| 24 | # Step 3: query associated containers |
|
| 25 | # Step 4: query analog points latest values |
|
| 26 | # Step 5: query energy points latest values |
|
| 27 | # Step 6: query digital points latest values |
|
| 28 | # Step 7: query the points of HVACs |
|
| 29 | # Step 8: construct the report |
|
| 30 | #################################################################################################################### |
|
| 31 | @staticmethod |
|
| 32 | def on_get(req, resp, id_): |
|
| 33 | if 'API-KEY' not in req.headers or \ |
|
| 34 | not isinstance(req.headers['API-KEY'], str) or \ |
|
| 35 | len(str.strip(req.headers['API-KEY'])) == 0: |
|
| 36 | access_control(req) |
|
| 37 | else: |
|
| 38 | api_key_control(req) |
|
| 39 | ||
| 40 | ################################################################################################################ |
|
| 41 | # Step 1: valid parameters |
|
| 42 | ################################################################################################################ |
|
| 43 | if not id_.isdigit() or int(id_) <= 0: |
|
| 44 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
|
| 45 | description='API.INVALID_ENERGY_STORAGE_POWER_STATION_ID') |
|
| 46 | energy_storage_power_station_id = id_ |
|
| 47 | ################################################################################################################ |
|
| 48 | # Step 2: query the energy storage power station |
|
| 49 | ################################################################################################################ |
|
| 50 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
|
| 51 | cursor_system = cnx_system.cursor() |
|
| 52 | ||
| 53 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
|
| 54 | cursor_historical = cnx_historical.cursor() |
|
| 55 | ||
| 56 | if energy_storage_power_station_id is not None: |
|
| 57 | query = (" SELECT id, name, uuid " |
|
| 58 | " FROM tbl_energy_storage_power_stations " |
|
| 59 | " WHERE id = %s ") |
|
| 60 | cursor_system.execute(query, (energy_storage_power_station_id,)) |
|
| 61 | row = cursor_system.fetchone() |
|
| 62 | if row is None: |
|
| 63 | cursor_system.close() |
|
| 64 | cnx_system.close() |
|
| 65 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', |
|
| 66 | description='API.ENERGY_STORAGE_POWER_STATION_NOT_FOUND') |
|
| 67 | ||
| 68 | # query all points |
|
| 69 | query = (" SELECT id, name, units, description " |
|
| 70 | " FROM tbl_points ") |
|
| 71 | cursor_system.execute(query) |
|
| 72 | rows = cursor_system.fetchall() |
|
| 73 | ||
| 74 | points_dict = dict() |
|
| 75 | if rows is not None and len(rows) > 0: |
|
| 76 | for row in rows: |
|
| 77 | points_dict[row[0]] = [row[1], row[2], row[3]] |
|
| 78 | ################################################################################################################ |
|
| 79 | # Step 3: query associated containers |
|
| 80 | ################################################################################################################ |
|
| 81 | container_list = list() |
|
| 82 | cursor_system.execute(" SELECT c.id, c.name, c.uuid " |
|
| 83 | " FROM tbl_energy_storage_power_stations_containers espsc, " |
|
| 84 | " tbl_energy_storage_containers c " |
|
| 85 | " WHERE espsc.energy_storage_power_station_id = %s " |
|
| 86 | " AND espsc.energy_storage_container_id = c.id ", |
|
| 87 | (energy_storage_power_station_id,)) |
|
| 88 | rows_containers = cursor_system.fetchall() |
|
| 89 | if rows_containers is not None and len(rows_containers) > 0: |
|
| 90 | for row_container in rows_containers: |
|
| 91 | container_list.append({"id": row_container[0], |
|
| 92 | "name": row_container[1], |
|
| 93 | "uuid": row_container[2]}) |
|
| 94 | print('container_list:' + str(container_list)) |
|
| 95 | ||
| 96 | ################################################################################################################ |
|
| 97 | # Step 4: query analog points latest values |
|
| 98 | ################################################################################################################ |
|
| 99 | latest_value_dict = dict() |
|
| 100 | query = (" SELECT point_id, actual_value " |
|
| 101 | " FROM tbl_analog_value_latest " |
|
| 102 | " WHERE utc_date_time > %s ") |
|
| 103 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 104 | rows = cursor_historical.fetchall() |
|
| 105 | if rows is not None and len(rows) > 0: |
|
| 106 | for row in rows: |
|
| 107 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 108 | points_dict[row[0]][1], |
|
| 109 | points_dict[row[0]][2], |
|
| 110 | row[1]] |
|
| 111 | ||
| 112 | ################################################################################################################ |
|
| 113 | # Step 5: query energy points latest values |
|
| 114 | ################################################################################################################ |
|
| 115 | query = (" SELECT point_id, actual_value " |
|
| 116 | " FROM tbl_energy_value_latest " |
|
| 117 | " WHERE utc_date_time > %s ") |
|
| 118 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 119 | rows = cursor_historical.fetchall() |
|
| 120 | if rows is not None and len(rows) > 0: |
|
| 121 | for row in rows: |
|
| 122 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 123 | points_dict[row[0]][1], |
|
| 124 | points_dict[row[0]][2], |
|
| 125 | row[1]] |
|
| 126 | ||
| 127 | ################################################################################################################ |
|
| 128 | # Step 6: query digital points latest values |
|
| 129 | ################################################################################################################ |
|
| 130 | query = (" SELECT point_id, actual_value " |
|
| 131 | " FROM tbl_digital_value_latest " |
|
| 132 | " WHERE utc_date_time > %s ") |
|
| 133 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 134 | rows = cursor_historical.fetchall() |
|
| 135 | if rows is not None and len(rows) > 0: |
|
| 136 | for row in rows: |
|
| 137 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 138 | points_dict[row[0]][1], |
|
| 139 | points_dict[row[0]][2], |
|
| 140 | row[1]] |
|
| 141 | ||
| 142 | ################################################################################################################ |
|
| 143 | # Step 7: query the points of HVACs |
|
| 144 | ################################################################################################################ |
|
| 145 | # query all points with units |
|
| 146 | query = (" SELECT id, units " |
|
| 147 | " FROM tbl_points ") |
|
| 148 | cursor_system.execute(query) |
|
| 149 | rows = cursor_system.fetchall() |
|
| 150 | ||
| 151 | units_dict = dict() |
|
| 152 | if rows is not None and len(rows) > 0: |
|
| 153 | for row in rows: |
|
| 154 | units_dict[row[0]] = row[1] |
|
| 155 | # query pcs parameters |
|
| 156 | hvac_list = list() |
|
| 157 | for container in container_list: |
|
| 158 | cursor_system.execute(" SELECT id, name, uuid " |
|
| 159 | " FROM tbl_energy_storage_containers_hvacs " |
|
| 160 | " WHERE energy_storage_container_id = %s " |
|
| 161 | " ORDER BY id ", |
|
| 162 | (container['id'],)) |
|
| 163 | rows_hvacs = cursor_system.fetchall() |
|
| 164 | if rows_hvacs is not None and len(rows_hvacs) > 0: |
|
| 165 | for row in rows_hvacs: |
|
| 166 | current_hvac = dict() |
|
| 167 | current_hvac['id'] = row[0] |
|
| 168 | current_hvac['name'] = row[1] |
|
| 169 | current_hvac['uuid'] = row[2] |
|
| 170 | current_hvac['points'] = list() |
|
| 171 | hvac_list.append(current_hvac) |
|
| 172 | ||
| 173 | for index, hvac in enumerate(hvac_list): |
|
| 174 | cursor_system.execute(" SELECT p.id " |
|
| 175 | " FROM tbl_energy_storage_containers_hvacs_points bp, tbl_points p " |
|
| 176 | " WHERE bp.hvac_id = %s AND bp.point_id = p.id " |
|
| 177 | " ORDER BY bp.id ", |
|
| 178 | (hvac['id'],)) |
|
| 179 | rows_points = cursor_system.fetchall() |
|
| 180 | if rows_points is not None and len(rows_points) > 0: |
|
| 181 | point_list = list() |
|
| 182 | for row in rows_points: |
|
| 183 | point = latest_value_dict.get(row[0], None) |
|
| 184 | if point is not None: |
|
| 185 | point_list.append(point) |
|
| 186 | hvac_list[index]['points'] = point_list |
|
| 187 | ||
| 188 | if cursor_system: |
|
| 189 | cursor_system.close() |
|
| 190 | if cnx_system: |
|
| 191 | cnx_system.close() |
|
| 192 | ||
| 193 | if cursor_historical: |
|
| 194 | cursor_historical.close() |
|
| 195 | if cnx_historical: |
|
| 196 | cnx_historical.close() |
|
| 197 | ################################################################################################################ |
|
| 198 | # Step 8: construct the report |
|
| 199 | ################################################################################################################ |
|
| 200 | resp.text = json.dumps(hvac_list) |
|
| 201 | ||
| @@ 9-200 (lines=192) @@ | ||
| 6 | import config |
|
| 7 | ||
| 8 | ||
| 9 | class Reporting: |
|
| 10 | def __init__(self): |
|
| 11 | """Initializes Class""" |
|
| 12 | pass |
|
| 13 | ||
| 14 | @staticmethod |
|
| 15 | def on_options(req, resp, id_): |
|
| 16 | _ = req |
|
| 17 | resp.status = falcon.HTTP_200 |
|
| 18 | _ = id_ |
|
| 19 | ||
| 20 | #################################################################################################################### |
|
| 21 | # PROCEDURES |
|
| 22 | # Step 1: valid parameters |
|
| 23 | # Step 2: query the energy storage power station |
|
| 24 | # Step 3: query associated containers |
|
| 25 | # Step 4: query analog points latest values |
|
| 26 | # Step 5: query energy points latest values |
|
| 27 | # Step 6: query digital points latest values |
|
| 28 | # Step 7: query the points of dcdcs |
|
| 29 | # Step 8: construct the report |
|
| 30 | #################################################################################################################### |
|
| 31 | @staticmethod |
|
| 32 | def on_get(req, resp, id_): |
|
| 33 | if 'API-KEY' not in req.headers or \ |
|
| 34 | not isinstance(req.headers['API-KEY'], str) or \ |
|
| 35 | len(str.strip(req.headers['API-KEY'])) == 0: |
|
| 36 | access_control(req) |
|
| 37 | else: |
|
| 38 | api_key_control(req) |
|
| 39 | ||
| 40 | ################################################################################################################ |
|
| 41 | # Step 1: valid parameters |
|
| 42 | ################################################################################################################ |
|
| 43 | if not id_.isdigit() or int(id_) <= 0: |
|
| 44 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
|
| 45 | description='API.INVALID_ENERGY_STORAGE_POWER_STATION_ID') |
|
| 46 | energy_storage_power_station_id = id_ |
|
| 47 | ################################################################################################################ |
|
| 48 | # Step 2: query the energy storage power station |
|
| 49 | ################################################################################################################ |
|
| 50 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
|
| 51 | cursor_system = cnx_system.cursor() |
|
| 52 | ||
| 53 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
|
| 54 | cursor_historical = cnx_historical.cursor() |
|
| 55 | ||
| 56 | if energy_storage_power_station_id is not None: |
|
| 57 | query = (" SELECT id, name, uuid " |
|
| 58 | " FROM tbl_energy_storage_power_stations " |
|
| 59 | " WHERE id = %s ") |
|
| 60 | cursor_system.execute(query, (energy_storage_power_station_id,)) |
|
| 61 | row = cursor_system.fetchone() |
|
| 62 | if row is None: |
|
| 63 | cursor_system.close() |
|
| 64 | cnx_system.close() |
|
| 65 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', |
|
| 66 | description='API.ENERGY_STORAGE_POWER_STATION_NOT_FOUND') |
|
| 67 | ||
| 68 | # query all points |
|
| 69 | query = (" SELECT id, name, units, description " |
|
| 70 | " FROM tbl_points ") |
|
| 71 | cursor_system.execute(query) |
|
| 72 | rows = cursor_system.fetchall() |
|
| 73 | ||
| 74 | points_dict = dict() |
|
| 75 | if rows is not None and len(rows) > 0: |
|
| 76 | for row in rows: |
|
| 77 | points_dict[row[0]] = [row[1], row[2], row[3]] |
|
| 78 | ################################################################################################################ |
|
| 79 | # Step 3: query associated containers |
|
| 80 | ################################################################################################################ |
|
| 81 | container_list = list() |
|
| 82 | cursor_system.execute(" SELECT c.id, c.name, c.uuid " |
|
| 83 | " FROM tbl_energy_storage_power_stations_containers espsc, " |
|
| 84 | " tbl_energy_storage_containers c " |
|
| 85 | " WHERE espsc.energy_storage_power_station_id = %s " |
|
| 86 | " AND espsc.energy_storage_container_id = c.id ", |
|
| 87 | (energy_storage_power_station_id,)) |
|
| 88 | rows_containers = cursor_system.fetchall() |
|
| 89 | if rows_containers is not None and len(rows_containers) > 0: |
|
| 90 | for row_container in rows_containers: |
|
| 91 | container_list.append({"id": row_container[0], |
|
| 92 | "name": row_container[1], |
|
| 93 | "uuid": row_container[2]}) |
|
| 94 | print('container_list:' + str(container_list)) |
|
| 95 | ||
| 96 | ################################################################################################################ |
|
| 97 | # Step 4: query analog points latest values |
|
| 98 | ################################################################################################################ |
|
| 99 | latest_value_dict = dict() |
|
| 100 | query = (" SELECT point_id, actual_value " |
|
| 101 | " FROM tbl_analog_value_latest " |
|
| 102 | " WHERE utc_date_time > %s ") |
|
| 103 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 104 | rows = cursor_historical.fetchall() |
|
| 105 | if rows is not None and len(rows) > 0: |
|
| 106 | for row in rows: |
|
| 107 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 108 | points_dict[row[0]][1], |
|
| 109 | points_dict[row[0]][2], |
|
| 110 | row[1]] |
|
| 111 | ||
| 112 | ################################################################################################################ |
|
| 113 | # Step 5: query energy points latest values |
|
| 114 | ################################################################################################################ |
|
| 115 | query = (" SELECT point_id, actual_value " |
|
| 116 | " FROM tbl_energy_value_latest " |
|
| 117 | " WHERE utc_date_time > %s ") |
|
| 118 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 119 | rows = cursor_historical.fetchall() |
|
| 120 | if rows is not None and len(rows) > 0: |
|
| 121 | for row in rows: |
|
| 122 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 123 | points_dict[row[0]][1], |
|
| 124 | points_dict[row[0]][2], |
|
| 125 | row[1]] |
|
| 126 | ||
| 127 | ################################################################################################################ |
|
| 128 | # Step 6: query digital points latest values |
|
| 129 | ################################################################################################################ |
|
| 130 | query = (" SELECT point_id, actual_value " |
|
| 131 | " FROM tbl_digital_value_latest " |
|
| 132 | " WHERE utc_date_time > %s ") |
|
| 133 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 134 | rows = cursor_historical.fetchall() |
|
| 135 | if rows is not None and len(rows) > 0: |
|
| 136 | for row in rows: |
|
| 137 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 138 | points_dict[row[0]][1], |
|
| 139 | points_dict[row[0]][2], |
|
| 140 | row[1]] |
|
| 141 | ||
| 142 | ################################################################################################################ |
|
| 143 | # Step 7: query the points of dcdcs |
|
| 144 | ################################################################################################################ |
|
| 145 | # query all points with units |
|
| 146 | query = (" SELECT id, units " |
|
| 147 | " FROM tbl_points ") |
|
| 148 | cursor_system.execute(query) |
|
| 149 | rows = cursor_system.fetchall() |
|
| 150 | ||
| 151 | units_dict = dict() |
|
| 152 | if rows is not None and len(rows) > 0: |
|
| 153 | for row in rows: |
|
| 154 | units_dict[row[0]] = row[1] |
|
| 155 | # query dcdc parameters |
|
| 156 | dcdc_list = list() |
|
| 157 | for container in container_list: |
|
| 158 | cursor_system.execute(" SELECT id, name, uuid " |
|
| 159 | " FROM tbl_energy_storage_containers_dcdcs " |
|
| 160 | " WHERE energy_storage_container_id = %s " |
|
| 161 | " ORDER BY id ", |
|
| 162 | (container['id'],)) |
|
| 163 | rows_dcdcs = cursor_system.fetchall() |
|
| 164 | if rows_dcdcs is not None and len(rows_dcdcs) > 0: |
|
| 165 | for row in rows_dcdcs: |
|
| 166 | current_dcdc = dict() |
|
| 167 | current_dcdc['id'] = row[0] |
|
| 168 | current_dcdc['name'] = row[1] |
|
| 169 | current_dcdc['uuid'] = row[2] |
|
| 170 | current_dcdc['points'] = list() |
|
| 171 | dcdc_list.append(current_dcdc) |
|
| 172 | ||
| 173 | for index, dcdc in enumerate(dcdc_list): |
|
| 174 | cursor_system.execute(" SELECT p.id " |
|
| 175 | " FROM tbl_energy_storage_containers_dcdcs_points bp, tbl_points p " |
|
| 176 | " WHERE bp.dcdc_id = %s AND bp.point_id = p.id " |
|
| 177 | " ORDER BY bp.id ", |
|
| 178 | (dcdc['id'],)) |
|
| 179 | rows_points = cursor_system.fetchall() |
|
| 180 | if rows_points is not None and len(rows_points) > 0: |
|
| 181 | point_list = list() |
|
| 182 | for row in rows_points: |
|
| 183 | point = latest_value_dict.get(row[0], None) |
|
| 184 | if point is not None: |
|
| 185 | point_list.append(point) |
|
| 186 | dcdc_list[index]['points'] = point_list |
|
| 187 | ||
| 188 | if cursor_system: |
|
| 189 | cursor_system.close() |
|
| 190 | if cnx_system: |
|
| 191 | cnx_system.close() |
|
| 192 | ||
| 193 | if cursor_historical: |
|
| 194 | cursor_historical.close() |
|
| 195 | if cnx_historical: |
|
| 196 | cnx_historical.close() |
|
| 197 | ################################################################################################################ |
|
| 198 | # Step 8: construct the report |
|
| 199 | ################################################################################################################ |
|
| 200 | resp.text = json.dumps(dcdc_list) |
|
| 201 | ||
| @@ 9-200 (lines=192) @@ | ||
| 6 | import config |
|
| 7 | ||
| 8 | ||
| 9 | class Reporting: |
|
| 10 | def __init__(self): |
|
| 11 | """Initializes Class""" |
|
| 12 | pass |
|
| 13 | ||
| 14 | @staticmethod |
|
| 15 | def on_options(req, resp, id_): |
|
| 16 | _ = req |
|
| 17 | resp.status = falcon.HTTP_200 |
|
| 18 | _ = id_ |
|
| 19 | ||
| 20 | #################################################################################################################### |
|
| 21 | # PROCEDURES |
|
| 22 | # Step 1: valid parameters |
|
| 23 | # Step 2: query the energy storage power station |
|
| 24 | # Step 3: query associated containers |
|
| 25 | # Step 4: query analog points latest values |
|
| 26 | # Step 5: query energy points latest values |
|
| 27 | # Step 6: query digital points latest values |
|
| 28 | # Step 7: query the points of firecontrols |
|
| 29 | # Step 8: construct the report |
|
| 30 | #################################################################################################################### |
|
| 31 | @staticmethod |
|
| 32 | def on_get(req, resp, id_): |
|
| 33 | if 'API-KEY' not in req.headers or \ |
|
| 34 | not isinstance(req.headers['API-KEY'], str) or \ |
|
| 35 | len(str.strip(req.headers['API-KEY'])) == 0: |
|
| 36 | access_control(req) |
|
| 37 | else: |
|
| 38 | api_key_control(req) |
|
| 39 | ||
| 40 | ################################################################################################################ |
|
| 41 | # Step 1: valid parameters |
|
| 42 | ################################################################################################################ |
|
| 43 | if not id_.isdigit() or int(id_) <= 0: |
|
| 44 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
|
| 45 | description='API.INVALID_ENERGY_STORAGE_POWER_STATION_ID') |
|
| 46 | energy_storage_power_station_id = id_ |
|
| 47 | ################################################################################################################ |
|
| 48 | # Step 2: query the energy storage power station |
|
| 49 | ################################################################################################################ |
|
| 50 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
|
| 51 | cursor_system = cnx_system.cursor() |
|
| 52 | ||
| 53 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
|
| 54 | cursor_historical = cnx_historical.cursor() |
|
| 55 | ||
| 56 | if energy_storage_power_station_id is not None: |
|
| 57 | query = (" SELECT id, name, uuid " |
|
| 58 | " FROM tbl_energy_storage_power_stations " |
|
| 59 | " WHERE id = %s ") |
|
| 60 | cursor_system.execute(query, (energy_storage_power_station_id,)) |
|
| 61 | row = cursor_system.fetchone() |
|
| 62 | if row is None: |
|
| 63 | cursor_system.close() |
|
| 64 | cnx_system.close() |
|
| 65 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', |
|
| 66 | description='API.ENERGY_STORAGE_POWER_STATION_NOT_FOUND') |
|
| 67 | ||
| 68 | # query all points |
|
| 69 | query = (" SELECT id, name, units, description " |
|
| 70 | " FROM tbl_points ") |
|
| 71 | cursor_system.execute(query) |
|
| 72 | rows = cursor_system.fetchall() |
|
| 73 | ||
| 74 | points_dict = dict() |
|
| 75 | if rows is not None and len(rows) > 0: |
|
| 76 | for row in rows: |
|
| 77 | points_dict[row[0]] = [row[1], row[2], row[3]] |
|
| 78 | ################################################################################################################ |
|
| 79 | # Step 3: query associated containers |
|
| 80 | ################################################################################################################ |
|
| 81 | container_list = list() |
|
| 82 | cursor_system.execute(" SELECT c.id, c.name, c.uuid " |
|
| 83 | " FROM tbl_energy_storage_power_stations_containers espsc, " |
|
| 84 | " tbl_energy_storage_containers c " |
|
| 85 | " WHERE espsc.energy_storage_power_station_id = %s " |
|
| 86 | " AND espsc.energy_storage_container_id = c.id ", |
|
| 87 | (energy_storage_power_station_id,)) |
|
| 88 | rows_containers = cursor_system.fetchall() |
|
| 89 | if rows_containers is not None and len(rows_containers) > 0: |
|
| 90 | for row_container in rows_containers: |
|
| 91 | container_list.append({"id": row_container[0], |
|
| 92 | "name": row_container[1], |
|
| 93 | "uuid": row_container[2]}) |
|
| 94 | print('container_list:' + str(container_list)) |
|
| 95 | ||
| 96 | ################################################################################################################ |
|
| 97 | # Step 4: query analog points latest values |
|
| 98 | ################################################################################################################ |
|
| 99 | latest_value_dict = dict() |
|
| 100 | query = (" SELECT point_id, actual_value " |
|
| 101 | " FROM tbl_analog_value_latest " |
|
| 102 | " WHERE utc_date_time > %s ") |
|
| 103 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 104 | rows = cursor_historical.fetchall() |
|
| 105 | if rows is not None and len(rows) > 0: |
|
| 106 | for row in rows: |
|
| 107 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 108 | points_dict[row[0]][1], |
|
| 109 | points_dict[row[0]][2], |
|
| 110 | row[1]] |
|
| 111 | ||
| 112 | ################################################################################################################ |
|
| 113 | # Step 5: query energy points latest values |
|
| 114 | ################################################################################################################ |
|
| 115 | query = (" SELECT point_id, actual_value " |
|
| 116 | " FROM tbl_energy_value_latest " |
|
| 117 | " WHERE utc_date_time > %s ") |
|
| 118 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 119 | rows = cursor_historical.fetchall() |
|
| 120 | if rows is not None and len(rows) > 0: |
|
| 121 | for row in rows: |
|
| 122 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 123 | points_dict[row[0]][1], |
|
| 124 | points_dict[row[0]][2], |
|
| 125 | row[1]] |
|
| 126 | ||
| 127 | ################################################################################################################ |
|
| 128 | # Step 6: query digital points latest values |
|
| 129 | ################################################################################################################ |
|
| 130 | query = (" SELECT point_id, actual_value " |
|
| 131 | " FROM tbl_digital_value_latest " |
|
| 132 | " WHERE utc_date_time > %s ") |
|
| 133 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 134 | rows = cursor_historical.fetchall() |
|
| 135 | if rows is not None and len(rows) > 0: |
|
| 136 | for row in rows: |
|
| 137 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 138 | points_dict[row[0]][1], |
|
| 139 | points_dict[row[0]][2], |
|
| 140 | row[1]] |
|
| 141 | ||
| 142 | ################################################################################################################ |
|
| 143 | # Step 7: query the points of firecontrols |
|
| 144 | ################################################################################################################ |
|
| 145 | # query all points with units |
|
| 146 | query = (" SELECT id, units " |
|
| 147 | " FROM tbl_points ") |
|
| 148 | cursor_system.execute(query) |
|
| 149 | rows = cursor_system.fetchall() |
|
| 150 | ||
| 151 | units_dict = dict() |
|
| 152 | if rows is not None and len(rows) > 0: |
|
| 153 | for row in rows: |
|
| 154 | units_dict[row[0]] = row[1] |
|
| 155 | # query firecontrol parameters |
|
| 156 | firecontrol_list = list() |
|
| 157 | for container in container_list: |
|
| 158 | cursor_system.execute(" SELECT id, name, uuid " |
|
| 159 | " FROM tbl_energy_storage_containers_firecontrols " |
|
| 160 | " WHERE energy_storage_container_id = %s " |
|
| 161 | " ORDER BY id ", |
|
| 162 | (container['id'],)) |
|
| 163 | rows_firecontrols = cursor_system.fetchall() |
|
| 164 | if rows_firecontrols is not None and len(rows_firecontrols) > 0: |
|
| 165 | for row in rows_firecontrols: |
|
| 166 | current_firecontrol = dict() |
|
| 167 | current_firecontrol['id'] = row[0] |
|
| 168 | current_firecontrol['name'] = row[1] |
|
| 169 | current_firecontrol['uuid'] = row[2] |
|
| 170 | current_firecontrol['points'] = list() |
|
| 171 | firecontrol_list.append(current_firecontrol) |
|
| 172 | ||
| 173 | for index, firecontrol in enumerate(firecontrol_list): |
|
| 174 | cursor_system.execute(" SELECT p.id " |
|
| 175 | " FROM tbl_energy_storage_containers_firecontrols_points bp, tbl_points p " |
|
| 176 | " WHERE bp.firecontrol_id = %s AND bp.point_id = p.id " |
|
| 177 | " ORDER BY bp.id ", |
|
| 178 | (firecontrol['id'],)) |
|
| 179 | rows_points = cursor_system.fetchall() |
|
| 180 | if rows_points is not None and len(rows_points) > 0: |
|
| 181 | point_list = list() |
|
| 182 | for row in rows_points: |
|
| 183 | point = latest_value_dict.get(row[0], None) |
|
| 184 | if point is not None: |
|
| 185 | point_list.append(point) |
|
| 186 | firecontrol_list[index]['points'] = point_list |
|
| 187 | ||
| 188 | if cursor_system: |
|
| 189 | cursor_system.close() |
|
| 190 | if cnx_system: |
|
| 191 | cnx_system.close() |
|
| 192 | ||
| 193 | if cursor_historical: |
|
| 194 | cursor_historical.close() |
|
| 195 | if cnx_historical: |
|
| 196 | cnx_historical.close() |
|
| 197 | ################################################################################################################ |
|
| 198 | # Step 8: construct the report |
|
| 199 | ################################################################################################################ |
|
| 200 | resp.text = json.dumps(firecontrol_list) |
|
| 201 | ||
| @@ 9-200 (lines=192) @@ | ||
| 6 | import config |
|
| 7 | ||
| 8 | ||
| 9 | class Reporting: |
|
| 10 | def __init__(self): |
|
| 11 | """Initializes Class""" |
|
| 12 | pass |
|
| 13 | ||
| 14 | @staticmethod |
|
| 15 | def on_options(req, resp, id_): |
|
| 16 | _ = req |
|
| 17 | resp.status = falcon.HTTP_200 |
|
| 18 | _ = id_ |
|
| 19 | ||
| 20 | #################################################################################################################### |
|
| 21 | # PROCEDURES |
|
| 22 | # Step 1: valid parameters |
|
| 23 | # Step 2: query the energy storage power station |
|
| 24 | # Step 3: query associated containers |
|
| 25 | # Step 4: query analog points latest values |
|
| 26 | # Step 5: query energy points latest values |
|
| 27 | # Step 6: query digital points latest values |
|
| 28 | # Step 7: query the points of stses |
|
| 29 | # Step 8: construct the report |
|
| 30 | #################################################################################################################### |
|
| 31 | @staticmethod |
|
| 32 | def on_get(req, resp, id_): |
|
| 33 | if 'API-KEY' not in req.headers or \ |
|
| 34 | not isinstance(req.headers['API-KEY'], str) or \ |
|
| 35 | len(str.strip(req.headers['API-KEY'])) == 0: |
|
| 36 | access_control(req) |
|
| 37 | else: |
|
| 38 | api_key_control(req) |
|
| 39 | ||
| 40 | ################################################################################################################ |
|
| 41 | # Step 1: valid parameters |
|
| 42 | ################################################################################################################ |
|
| 43 | if not id_.isdigit() or int(id_) <= 0: |
|
| 44 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
|
| 45 | description='API.INVALID_ENERGY_STORAGE_POWER_STATION_ID') |
|
| 46 | energy_storage_power_station_id = id_ |
|
| 47 | ################################################################################################################ |
|
| 48 | # Step 2: query the energy storage power station |
|
| 49 | ################################################################################################################ |
|
| 50 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
|
| 51 | cursor_system = cnx_system.cursor() |
|
| 52 | ||
| 53 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
|
| 54 | cursor_historical = cnx_historical.cursor() |
|
| 55 | ||
| 56 | if energy_storage_power_station_id is not None: |
|
| 57 | query = (" SELECT id, name, uuid " |
|
| 58 | " FROM tbl_energy_storage_power_stations " |
|
| 59 | " WHERE id = %s ") |
|
| 60 | cursor_system.execute(query, (energy_storage_power_station_id,)) |
|
| 61 | row = cursor_system.fetchone() |
|
| 62 | if row is None: |
|
| 63 | cursor_system.close() |
|
| 64 | cnx_system.close() |
|
| 65 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', |
|
| 66 | description='API.ENERGY_STORAGE_POWER_STATION_NOT_FOUND') |
|
| 67 | ||
| 68 | # query all points |
|
| 69 | query = (" SELECT id, name, units, description " |
|
| 70 | " FROM tbl_points ") |
|
| 71 | cursor_system.execute(query) |
|
| 72 | rows = cursor_system.fetchall() |
|
| 73 | ||
| 74 | points_dict = dict() |
|
| 75 | if rows is not None and len(rows) > 0: |
|
| 76 | for row in rows: |
|
| 77 | points_dict[row[0]] = [row[1], row[2], row[3]] |
|
| 78 | ################################################################################################################ |
|
| 79 | # Step 3: query associated containers |
|
| 80 | ################################################################################################################ |
|
| 81 | container_list = list() |
|
| 82 | cursor_system.execute(" SELECT c.id, c.name, c.uuid " |
|
| 83 | " FROM tbl_energy_storage_power_stations_containers espsc, " |
|
| 84 | " tbl_energy_storage_containers c " |
|
| 85 | " WHERE espsc.energy_storage_power_station_id = %s " |
|
| 86 | " AND espsc.energy_storage_container_id = c.id ", |
|
| 87 | (energy_storage_power_station_id,)) |
|
| 88 | rows_containers = cursor_system.fetchall() |
|
| 89 | if rows_containers is not None and len(rows_containers) > 0: |
|
| 90 | for row_container in rows_containers: |
|
| 91 | container_list.append({"id": row_container[0], |
|
| 92 | "name": row_container[1], |
|
| 93 | "uuid": row_container[2]}) |
|
| 94 | print('container_list:' + str(container_list)) |
|
| 95 | ||
| 96 | ################################################################################################################ |
|
| 97 | # Step 4: query analog points latest values |
|
| 98 | ################################################################################################################ |
|
| 99 | latest_value_dict = dict() |
|
| 100 | query = (" SELECT point_id, actual_value " |
|
| 101 | " FROM tbl_analog_value_latest " |
|
| 102 | " WHERE utc_date_time > %s ") |
|
| 103 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 104 | rows = cursor_historical.fetchall() |
|
| 105 | if rows is not None and len(rows) > 0: |
|
| 106 | for row in rows: |
|
| 107 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 108 | points_dict[row[0]][1], |
|
| 109 | points_dict[row[0]][2], |
|
| 110 | row[1]] |
|
| 111 | ||
| 112 | ################################################################################################################ |
|
| 113 | # Step 5: query energy points latest values |
|
| 114 | ################################################################################################################ |
|
| 115 | query = (" SELECT point_id, actual_value " |
|
| 116 | " FROM tbl_energy_value_latest " |
|
| 117 | " WHERE utc_date_time > %s ") |
|
| 118 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 119 | rows = cursor_historical.fetchall() |
|
| 120 | if rows is not None and len(rows) > 0: |
|
| 121 | for row in rows: |
|
| 122 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 123 | points_dict[row[0]][1], |
|
| 124 | points_dict[row[0]][2], |
|
| 125 | row[1]] |
|
| 126 | ||
| 127 | ################################################################################################################ |
|
| 128 | # Step 6: query digital points latest values |
|
| 129 | ################################################################################################################ |
|
| 130 | query = (" SELECT point_id, actual_value " |
|
| 131 | " FROM tbl_digital_value_latest " |
|
| 132 | " WHERE utc_date_time > %s ") |
|
| 133 | cursor_historical.execute(query, (datetime.utcnow() - timedelta(minutes=60),)) |
|
| 134 | rows = cursor_historical.fetchall() |
|
| 135 | if rows is not None and len(rows) > 0: |
|
| 136 | for row in rows: |
|
| 137 | latest_value_dict[row[0]] = [points_dict[row[0]][0], |
|
| 138 | points_dict[row[0]][1], |
|
| 139 | points_dict[row[0]][2], |
|
| 140 | row[1]] |
|
| 141 | ||
| 142 | ################################################################################################################ |
|
| 143 | # Step 7: query the points of stses |
|
| 144 | ################################################################################################################ |
|
| 145 | # query all points with units |
|
| 146 | query = (" SELECT id, units " |
|
| 147 | " FROM tbl_points ") |
|
| 148 | cursor_system.execute(query) |
|
| 149 | rows = cursor_system.fetchall() |
|
| 150 | ||
| 151 | units_dict = dict() |
|
| 152 | if rows is not None and len(rows) > 0: |
|
| 153 | for row in rows: |
|
| 154 | units_dict[row[0]] = row[1] |
|
| 155 | # query sts parameters |
|
| 156 | sts_list = list() |
|
| 157 | for container in container_list: |
|
| 158 | cursor_system.execute(" SELECT id, name, uuid " |
|
| 159 | " FROM tbl_energy_storage_containers_stses " |
|
| 160 | " WHERE energy_storage_container_id = %s " |
|
| 161 | " ORDER BY id ", |
|
| 162 | (container['id'],)) |
|
| 163 | rows_stses = cursor_system.fetchall() |
|
| 164 | if rows_stses is not None and len(rows_stses) > 0: |
|
| 165 | for row in rows_stses: |
|
| 166 | current_sts = dict() |
|
| 167 | current_sts['id'] = row[0] |
|
| 168 | current_sts['name'] = row[1] |
|
| 169 | current_sts['uuid'] = row[2] |
|
| 170 | current_sts['points'] = list() |
|
| 171 | sts_list.append(current_sts) |
|
| 172 | ||
| 173 | for index, sts in enumerate(sts_list): |
|
| 174 | cursor_system.execute(" SELECT p.id " |
|
| 175 | " FROM tbl_energy_storage_containers_stses_points bp, tbl_points p " |
|
| 176 | " WHERE bp.sts_id = %s AND bp.point_id = p.id " |
|
| 177 | " ORDER BY bp.id ", |
|
| 178 | (sts['id'],)) |
|
| 179 | rows_points = cursor_system.fetchall() |
|
| 180 | if rows_points is not None and len(rows_points) > 0: |
|
| 181 | point_list = list() |
|
| 182 | for row in rows_points: |
|
| 183 | point = latest_value_dict.get(row[0], None) |
|
| 184 | if point is not None: |
|
| 185 | point_list.append(point) |
|
| 186 | sts_list[index]['points'] = point_list |
|
| 187 | ||
| 188 | if cursor_system: |
|
| 189 | cursor_system.close() |
|
| 190 | if cnx_system: |
|
| 191 | cnx_system.close() |
|
| 192 | ||
| 193 | if cursor_historical: |
|
| 194 | cursor_historical.close() |
|
| 195 | if cnx_historical: |
|
| 196 | cnx_historical.close() |
|
| 197 | ################################################################################################################ |
|
| 198 | # Step 8: construct the report |
|
| 199 | ################################################################################################################ |
|
| 200 | resp.text = json.dumps(sts_list) |
|
| 201 | ||
