Conditions | 145 |
Total Lines | 720 |
Code Lines | 552 |
Lines | 720 |
Ratio | 100 % |
Changes | 0 |
Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.
For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.
Commonly applied refactorings include:
If many parameters/temporary variables are present:
Complex classes like reports.equipmentplan.Reporting.on_get() often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
1 | import re |
||
35 | @staticmethod |
||
36 | def on_get(req, resp): |
||
37 | if 'API-KEY' not in req.headers or \ |
||
38 | not isinstance(req.headers['API-KEY'], str) or \ |
||
39 | len(str.strip(req.headers['API-KEY'])) == 0: |
||
40 | access_control(req) |
||
41 | else: |
||
42 | api_key_control(req) |
||
43 | print(req.params) |
||
44 | equipment_id = req.params.get('equipmentid') |
||
45 | equipment_uuid = req.params.get('equipmentuuid') |
||
46 | period_type = req.params.get('periodtype') |
||
47 | base_period_start_datetime_local = req.params.get('baseperiodstartdatetime') |
||
48 | base_period_end_datetime_local = req.params.get('baseperiodenddatetime') |
||
49 | reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime') |
||
50 | reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime') |
||
51 | language = req.params.get('language') |
||
52 | quick_mode = req.params.get('quickmode') |
||
53 | |||
54 | ################################################################################################################ |
||
55 | # Step 1: valid parameters |
||
56 | ################################################################################################################ |
||
57 | if equipment_id is None and equipment_uuid is None: |
||
58 | raise falcon.HTTPError(status=falcon.HTTP_400, |
||
59 | title='API.BAD_REQUEST', |
||
60 | description='API.INVALID_EQUIPMENT_ID') |
||
61 | |||
62 | if equipment_id is not None: |
||
63 | equipment_id = str.strip(equipment_id) |
||
64 | if not equipment_id.isdigit() or int(equipment_id) <= 0: |
||
65 | raise falcon.HTTPError(status=falcon.HTTP_400, |
||
66 | title='API.BAD_REQUEST', |
||
67 | description='API.INVALID_EQUIPMENT_ID') |
||
68 | |||
69 | if equipment_uuid is not None: |
||
70 | regex = re.compile(r'^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z', re.I) |
||
71 | match = regex.match(str.strip(equipment_uuid)) |
||
72 | if not bool(match): |
||
73 | raise falcon.HTTPError(status=falcon.HTTP_400, |
||
74 | title='API.BAD_REQUEST', |
||
75 | description='API.INVALID_EQUIPMENT_UUID') |
||
76 | |||
77 | if period_type is None: |
||
78 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
79 | description='API.INVALID_PERIOD_TYPE') |
||
80 | else: |
||
81 | period_type = str.strip(period_type) |
||
82 | if period_type not in ['hourly', 'daily', 'weekly', 'monthly', 'yearly']: |
||
83 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
84 | description='API.INVALID_PERIOD_TYPE') |
||
85 | |||
86 | timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6]) |
||
87 | if config.utc_offset[0] == '-': |
||
88 | timezone_offset = -timezone_offset |
||
89 | |||
90 | base_start_datetime_utc = None |
||
91 | if base_period_start_datetime_local is not None and len(str.strip(base_period_start_datetime_local)) > 0: |
||
92 | base_period_start_datetime_local = str.strip(base_period_start_datetime_local) |
||
93 | try: |
||
94 | base_start_datetime_utc = datetime.strptime(base_period_start_datetime_local, '%Y-%m-%dT%H:%M:%S') |
||
95 | except ValueError: |
||
96 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
97 | description="API.INVALID_BASE_PERIOD_START_DATETIME") |
||
98 | base_start_datetime_utc = \ |
||
99 | base_start_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset) |
||
100 | # nomalize the start datetime |
||
101 | if config.minutes_to_count == 30 and base_start_datetime_utc.minute >= 30: |
||
102 | base_start_datetime_utc = base_start_datetime_utc.replace(minute=30, second=0, microsecond=0) |
||
103 | else: |
||
104 | base_start_datetime_utc = base_start_datetime_utc.replace(minute=0, second=0, microsecond=0) |
||
105 | |||
106 | base_end_datetime_utc = None |
||
107 | if base_period_end_datetime_local is not None and len(str.strip(base_period_end_datetime_local)) > 0: |
||
108 | base_period_end_datetime_local = str.strip(base_period_end_datetime_local) |
||
109 | try: |
||
110 | base_end_datetime_utc = datetime.strptime(base_period_end_datetime_local, '%Y-%m-%dT%H:%M:%S') |
||
111 | except ValueError: |
||
112 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
113 | description="API.INVALID_BASE_PERIOD_END_DATETIME") |
||
114 | base_end_datetime_utc = \ |
||
115 | base_end_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset) |
||
116 | |||
117 | if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \ |
||
118 | base_start_datetime_utc >= base_end_datetime_utc: |
||
119 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
120 | description='API.INVALID_BASE_PERIOD_END_DATETIME') |
||
121 | |||
122 | if reporting_period_start_datetime_local is None: |
||
123 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
124 | description="API.INVALID_REPORTING_PERIOD_START_DATETIME") |
||
125 | else: |
||
126 | reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local) |
||
127 | try: |
||
128 | reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local, |
||
129 | '%Y-%m-%dT%H:%M:%S') |
||
130 | except ValueError: |
||
131 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
132 | description="API.INVALID_REPORTING_PERIOD_START_DATETIME") |
||
133 | reporting_start_datetime_utc = \ |
||
134 | reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset) |
||
135 | # nomalize the start datetime |
||
136 | if config.minutes_to_count == 30 and reporting_start_datetime_utc.minute >= 30: |
||
137 | reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=30, second=0, microsecond=0) |
||
138 | else: |
||
139 | reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=0, second=0, microsecond=0) |
||
140 | |||
141 | if reporting_period_end_datetime_local is None: |
||
142 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
143 | description="API.INVALID_REPORTING_PERIOD_END_DATETIME") |
||
144 | else: |
||
145 | reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local) |
||
146 | try: |
||
147 | reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local, |
||
148 | '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \ |
||
149 | timedelta(minutes=timezone_offset) |
||
150 | except ValueError: |
||
151 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
152 | description="API.INVALID_REPORTING_PERIOD_END_DATETIME") |
||
153 | |||
154 | if reporting_start_datetime_utc >= reporting_end_datetime_utc: |
||
155 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
156 | description='API.INVALID_REPORTING_PERIOD_END_DATETIME') |
||
157 | |||
158 | # if turn quick mode on, do not return parameters data and excel file |
||
159 | is_quick_mode = False |
||
160 | if quick_mode is not None and \ |
||
161 | len(str.strip(quick_mode)) > 0 and \ |
||
162 | str.lower(str.strip(quick_mode)) in ('true', 't', 'on', 'yes', 'y'): |
||
163 | is_quick_mode = True |
||
164 | |||
165 | trans = utilities.get_translation(language) |
||
166 | trans.install() |
||
167 | _ = trans.gettext |
||
168 | |||
169 | ################################################################################################################ |
||
170 | # Step 2: query the equipment |
||
171 | ################################################################################################################ |
||
172 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
||
173 | cursor_system = cnx_system.cursor() |
||
174 | |||
175 | cnx_energy = mysql.connector.connect(**config.myems_energy_db) |
||
176 | cursor_energy = cnx_energy.cursor() |
||
177 | |||
178 | cnx_energy_plan = mysql.connector.connect(**config.myems_energy_plan_db) |
||
179 | cursor_energy_plan = cnx_energy_plan.cursor() |
||
180 | |||
181 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
||
182 | cursor_historical = cnx_historical.cursor() |
||
183 | |||
184 | if equipment_id is not None: |
||
185 | cursor_system.execute(" SELECT id, name, cost_center_id " |
||
186 | " FROM tbl_equipments " |
||
187 | " WHERE id = %s ", (equipment_id,)) |
||
188 | row_equipment = cursor_system.fetchone() |
||
189 | elif equipment_uuid is not None: |
||
190 | cursor_system.execute(" SELECT id, name, cost_center_id " |
||
191 | " FROM tbl_equipments " |
||
192 | " WHERE uuid = %s ", (equipment_uuid,)) |
||
193 | row_equipment = cursor_system.fetchone() |
||
194 | |||
195 | if row_equipment is None: |
||
196 | if cursor_system: |
||
197 | cursor_system.close() |
||
198 | if cnx_system: |
||
199 | cnx_system.close() |
||
200 | |||
201 | if cursor_energy: |
||
202 | cursor_energy.close() |
||
203 | if cnx_energy: |
||
204 | cnx_energy.close() |
||
205 | |||
206 | if cursor_energy_plan: |
||
207 | cursor_energy_plan.close() |
||
208 | if cnx_energy_plan: |
||
209 | cnx_energy_plan.close() |
||
210 | |||
211 | if cursor_historical: |
||
212 | cursor_historical.close() |
||
213 | if cnx_historical: |
||
214 | cnx_historical.close() |
||
215 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', description='API.EQUIPMENT_NOT_FOUND') |
||
216 | |||
217 | equipment = dict() |
||
218 | equipment['id'] = row_equipment[0] |
||
219 | equipment['name'] = row_equipment[1] |
||
220 | equipment['cost_center_id'] = row_equipment[2] |
||
221 | |||
222 | ################################################################################################################ |
||
223 | # Step 3: query energy categories |
||
224 | ################################################################################################################ |
||
225 | energy_category_set = set() |
||
226 | # query energy categories in base period |
||
227 | cursor_energy.execute(" SELECT DISTINCT(energy_category_id) " |
||
228 | " FROM tbl_equipment_input_category_hourly " |
||
229 | " WHERE equipment_id = %s " |
||
230 | " AND start_datetime_utc >= %s " |
||
231 | " AND start_datetime_utc < %s ", |
||
232 | (equipment['id'], base_start_datetime_utc, base_end_datetime_utc)) |
||
233 | rows_energy_categories = cursor_energy.fetchall() |
||
234 | if rows_energy_categories is not None and len(rows_energy_categories) > 0: |
||
235 | for row_energy_category in rows_energy_categories: |
||
236 | energy_category_set.add(row_energy_category[0]) |
||
237 | |||
238 | # query energy categories in reporting period |
||
239 | cursor_energy.execute(" SELECT DISTINCT(energy_category_id) " |
||
240 | " FROM tbl_equipment_input_category_hourly " |
||
241 | " WHERE equipment_id = %s " |
||
242 | " AND start_datetime_utc >= %s " |
||
243 | " AND start_datetime_utc < %s ", |
||
244 | (equipment['id'], reporting_start_datetime_utc, reporting_end_datetime_utc)) |
||
245 | rows_energy_categories = cursor_energy.fetchall() |
||
246 | if rows_energy_categories is not None and len(rows_energy_categories) > 0: |
||
247 | for row_energy_category in rows_energy_categories: |
||
248 | energy_category_set.add(row_energy_category[0]) |
||
249 | |||
250 | # query all energy categories in base period and reporting period |
||
251 | cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e " |
||
252 | " FROM tbl_energy_categories " |
||
253 | " ORDER BY id ", ) |
||
254 | rows_energy_categories = cursor_system.fetchall() |
||
255 | if rows_energy_categories is None or len(rows_energy_categories) == 0: |
||
256 | if cursor_system: |
||
257 | cursor_system.close() |
||
258 | if cnx_system: |
||
259 | cnx_system.close() |
||
260 | |||
261 | if cursor_energy: |
||
262 | cursor_energy.close() |
||
263 | if cnx_energy: |
||
264 | cnx_energy.close() |
||
265 | |||
266 | if cursor_energy_plan: |
||
267 | cursor_energy_plan.close() |
||
268 | if cnx_energy_plan: |
||
269 | cnx_energy_plan.close() |
||
270 | |||
271 | if cursor_historical: |
||
272 | cursor_historical.close() |
||
273 | if cnx_historical: |
||
274 | cnx_historical.close() |
||
275 | raise falcon.HTTPError(status=falcon.HTTP_404, |
||
276 | title='API.NOT_FOUND', |
||
277 | description='API.ENERGY_CATEGORY_NOT_FOUND') |
||
278 | energy_category_dict = dict() |
||
279 | for row_energy_category in rows_energy_categories: |
||
280 | if row_energy_category[0] in energy_category_set: |
||
281 | energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1], |
||
282 | "unit_of_measure": row_energy_category[2], |
||
283 | "kgce": row_energy_category[3], |
||
284 | "kgco2e": row_energy_category[4]} |
||
285 | |||
286 | ################################################################################################################ |
||
287 | # Step 4: query associated points |
||
288 | ################################################################################################################ |
||
289 | point_list = list() |
||
290 | cursor_system.execute(" SELECT p.id, ep.name, p.units, p.object_type " |
||
291 | " FROM tbl_equipments e, tbl_equipments_parameters ep, tbl_points p " |
||
292 | " WHERE e.id = %s AND e.id = ep.equipment_id AND ep.parameter_type = 'point' " |
||
293 | " AND ep.point_id = p.id " |
||
294 | " ORDER BY p.id ", (equipment['id'],)) |
||
295 | rows_points = cursor_system.fetchall() |
||
296 | if rows_points is not None and len(rows_points) > 0: |
||
297 | for row in rows_points: |
||
298 | point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]}) |
||
299 | |||
300 | ################################################################################################################ |
||
301 | # Step 5: query base period energy saving |
||
302 | ################################################################################################################ |
||
303 | base = dict() |
||
304 | if energy_category_set is not None and len(energy_category_set) > 0: |
||
305 | for energy_category_id in energy_category_set: |
||
306 | kgce = energy_category_dict[energy_category_id]['kgce'] |
||
307 | kgco2e = energy_category_dict[energy_category_id]['kgco2e'] |
||
308 | |||
309 | base[energy_category_id] = dict() |
||
310 | base[energy_category_id]['timestamps'] = list() |
||
311 | base[energy_category_id]['values_plan'] = list() |
||
312 | base[energy_category_id]['values_actual'] = list() |
||
313 | base[energy_category_id]['values_saving'] = list() |
||
314 | base[energy_category_id]['subtotal_plan'] = Decimal(0.0) |
||
315 | base[energy_category_id]['subtotal_actual'] = Decimal(0.0) |
||
316 | base[energy_category_id]['subtotal_saving'] = Decimal(0.0) |
||
317 | base[energy_category_id]['subtotal_in_kgce_plan'] = Decimal(0.0) |
||
318 | base[energy_category_id]['subtotal_in_kgce_actual'] = Decimal(0.0) |
||
319 | base[energy_category_id]['subtotal_in_kgce_saving'] = Decimal(0.0) |
||
320 | base[energy_category_id]['subtotal_in_kgco2e_plan'] = Decimal(0.0) |
||
321 | base[energy_category_id]['subtotal_in_kgco2e_actual'] = Decimal(0.0) |
||
322 | base[energy_category_id]['subtotal_in_kgco2e_saving'] = Decimal(0.0) |
||
323 | # query base period's energy plan |
||
324 | cursor_energy_plan.execute(" SELECT start_datetime_utc, actual_value " |
||
325 | " FROM tbl_equipment_input_category_hourly " |
||
326 | " WHERE equipment_id = %s " |
||
327 | " AND energy_category_id = %s " |
||
328 | " AND start_datetime_utc >= %s " |
||
329 | " AND start_datetime_utc < %s " |
||
330 | " ORDER BY start_datetime_utc ", |
||
331 | (equipment['id'], |
||
332 | energy_category_id, |
||
333 | base_start_datetime_utc, |
||
334 | base_end_datetime_utc)) |
||
335 | rows_equipment_hourly = cursor_energy_plan.fetchall() |
||
336 | |||
337 | rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly, |
||
338 | base_start_datetime_utc, |
||
339 | base_end_datetime_utc, |
||
340 | period_type) |
||
341 | for row_equipment_periodically in rows_equipment_periodically: |
||
342 | current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \ |
||
343 | timedelta(minutes=timezone_offset) |
||
344 | if period_type == 'hourly': |
||
345 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
346 | elif period_type == 'daily': |
||
347 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
348 | elif period_type == 'weekly': |
||
349 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
350 | elif period_type == 'monthly': |
||
351 | current_datetime = current_datetime_local.strftime('%Y-%m') |
||
352 | elif period_type == 'yearly': |
||
353 | current_datetime = current_datetime_local.strftime('%Y') |
||
354 | |||
355 | plan_value = Decimal(0.0) if row_equipment_periodically[1] is None \ |
||
356 | else row_equipment_periodically[1] |
||
357 | base[energy_category_id]['timestamps'].append(current_datetime) |
||
358 | base[energy_category_id]['values_plan'].append(plan_value) |
||
359 | base[energy_category_id]['subtotal_plan'] += plan_value |
||
360 | base[energy_category_id]['subtotal_in_kgce_plan'] += plan_value * kgce |
||
361 | base[energy_category_id]['subtotal_in_kgco2e_plan'] += plan_value * kgco2e |
||
362 | |||
363 | # query base period's energy actual |
||
364 | cursor_energy.execute(" SELECT start_datetime_utc, actual_value " |
||
365 | " FROM tbl_equipment_input_category_hourly " |
||
366 | " WHERE equipment_id = %s " |
||
367 | " AND energy_category_id = %s " |
||
368 | " AND start_datetime_utc >= %s " |
||
369 | " AND start_datetime_utc < %s " |
||
370 | " ORDER BY start_datetime_utc ", |
||
371 | (equipment['id'], |
||
372 | energy_category_id, |
||
373 | base_start_datetime_utc, |
||
374 | base_end_datetime_utc)) |
||
375 | rows_equipment_hourly = cursor_energy.fetchall() |
||
376 | |||
377 | rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly, |
||
378 | base_start_datetime_utc, |
||
379 | base_end_datetime_utc, |
||
380 | period_type) |
||
381 | for row_equipment_periodically in rows_equipment_periodically: |
||
382 | current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \ |
||
383 | timedelta(minutes=timezone_offset) |
||
384 | if period_type == 'hourly': |
||
385 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
386 | elif period_type == 'daily': |
||
387 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
388 | elif period_type == 'weekly': |
||
389 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
390 | elif period_type == 'monthly': |
||
391 | current_datetime = current_datetime_local.strftime('%Y-%m') |
||
392 | elif period_type == 'yearly': |
||
393 | current_datetime = current_datetime_local.strftime('%Y') |
||
394 | |||
395 | actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \ |
||
396 | else row_equipment_periodically[1] |
||
397 | base[energy_category_id]['values_actual'].append(actual_value) |
||
398 | base[energy_category_id]['subtotal_actual'] += actual_value |
||
399 | base[energy_category_id]['subtotal_in_kgce_actual'] += actual_value * kgce |
||
400 | base[energy_category_id]['subtotal_in_kgco2e_actual'] += actual_value * kgco2e |
||
401 | |||
402 | # calculate base period's energy savings |
||
403 | for i in range(len(base[energy_category_id]['values_plan'])): |
||
404 | base[energy_category_id]['values_saving'].append( |
||
405 | base[energy_category_id]['values_plan'][i] - |
||
406 | base[energy_category_id]['values_actual'][i]) |
||
407 | |||
408 | base[energy_category_id]['subtotal_saving'] = \ |
||
409 | base[energy_category_id]['subtotal_plan'] - \ |
||
410 | base[energy_category_id]['subtotal_actual'] |
||
411 | base[energy_category_id]['subtotal_in_kgce_saving'] = \ |
||
412 | base[energy_category_id]['subtotal_in_kgce_plan'] - \ |
||
413 | base[energy_category_id]['subtotal_in_kgce_actual'] |
||
414 | base[energy_category_id]['subtotal_in_kgco2e_saving'] = \ |
||
415 | base[energy_category_id]['subtotal_in_kgco2e_plan'] - \ |
||
416 | base[energy_category_id]['subtotal_in_kgco2e_actual'] |
||
417 | ################################################################################################################ |
||
418 | # Step 5: query reporting period energy saving |
||
419 | ################################################################################################################ |
||
420 | reporting = dict() |
||
421 | if energy_category_set is not None and len(energy_category_set) > 0: |
||
422 | for energy_category_id in energy_category_set: |
||
423 | kgce = energy_category_dict[energy_category_id]['kgce'] |
||
424 | kgco2e = energy_category_dict[energy_category_id]['kgco2e'] |
||
425 | |||
426 | reporting[energy_category_id] = dict() |
||
427 | reporting[energy_category_id]['timestamps'] = list() |
||
428 | reporting[energy_category_id]['values_plan'] = list() |
||
429 | reporting[energy_category_id]['values_actual'] = list() |
||
430 | reporting[energy_category_id]['values_saving'] = list() |
||
431 | reporting[energy_category_id]['subtotal_plan'] = Decimal(0.0) |
||
432 | reporting[energy_category_id]['subtotal_actual'] = Decimal(0.0) |
||
433 | reporting[energy_category_id]['subtotal_saving'] = Decimal(0.0) |
||
434 | reporting[energy_category_id]['subtotal_in_kgce_plan'] = Decimal(0.0) |
||
435 | reporting[energy_category_id]['subtotal_in_kgce_actual'] = Decimal(0.0) |
||
436 | reporting[energy_category_id]['subtotal_in_kgce_saving'] = Decimal(0.0) |
||
437 | reporting[energy_category_id]['subtotal_in_kgco2e_plan'] = Decimal(0.0) |
||
438 | reporting[energy_category_id]['subtotal_in_kgco2e_actual'] = Decimal(0.0) |
||
439 | reporting[energy_category_id]['subtotal_in_kgco2e_saving'] = Decimal(0.0) |
||
440 | # query reporting period's energy plan |
||
441 | cursor_energy_plan.execute(" SELECT start_datetime_utc, actual_value " |
||
442 | " FROM tbl_equipment_input_category_hourly " |
||
443 | " WHERE equipment_id = %s " |
||
444 | " AND energy_category_id = %s " |
||
445 | " AND start_datetime_utc >= %s " |
||
446 | " AND start_datetime_utc < %s " |
||
447 | " ORDER BY start_datetime_utc ", |
||
448 | (equipment['id'], |
||
449 | energy_category_id, |
||
450 | reporting_start_datetime_utc, |
||
451 | reporting_end_datetime_utc)) |
||
452 | rows_equipment_hourly = cursor_energy_plan.fetchall() |
||
453 | |||
454 | rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly, |
||
455 | reporting_start_datetime_utc, |
||
456 | reporting_end_datetime_utc, |
||
457 | period_type) |
||
458 | for row_equipment_periodically in rows_equipment_periodically: |
||
459 | current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \ |
||
460 | timedelta(minutes=timezone_offset) |
||
461 | if period_type == 'hourly': |
||
462 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
463 | elif period_type == 'daily': |
||
464 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
465 | elif period_type == 'weekly': |
||
466 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
467 | elif period_type == 'monthly': |
||
468 | current_datetime = current_datetime_local.strftime('%Y-%m') |
||
469 | elif period_type == 'yearly': |
||
470 | current_datetime = current_datetime_local.strftime('%Y') |
||
471 | |||
472 | plan_value = Decimal(0.0) if row_equipment_periodically[1] is None \ |
||
473 | else row_equipment_periodically[1] |
||
474 | reporting[energy_category_id]['timestamps'].append(current_datetime) |
||
475 | reporting[energy_category_id]['values_plan'].append(plan_value) |
||
476 | reporting[energy_category_id]['subtotal_plan'] += plan_value |
||
477 | reporting[energy_category_id]['subtotal_in_kgce_plan'] += plan_value * kgce |
||
478 | reporting[energy_category_id]['subtotal_in_kgco2e_plan'] += plan_value * kgco2e |
||
479 | |||
480 | # query reporting period's energy actual |
||
481 | cursor_energy.execute(" SELECT start_datetime_utc, actual_value " |
||
482 | " FROM tbl_equipment_input_category_hourly " |
||
483 | " WHERE equipment_id = %s " |
||
484 | " AND energy_category_id = %s " |
||
485 | " AND start_datetime_utc >= %s " |
||
486 | " AND start_datetime_utc < %s " |
||
487 | " ORDER BY start_datetime_utc ", |
||
488 | (equipment['id'], |
||
489 | energy_category_id, |
||
490 | reporting_start_datetime_utc, |
||
491 | reporting_end_datetime_utc)) |
||
492 | rows_equipment_hourly = cursor_energy.fetchall() |
||
493 | |||
494 | rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly, |
||
495 | reporting_start_datetime_utc, |
||
496 | reporting_end_datetime_utc, |
||
497 | period_type) |
||
498 | for row_equipment_periodically in rows_equipment_periodically: |
||
499 | current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \ |
||
500 | timedelta(minutes=timezone_offset) |
||
501 | if period_type == 'hourly': |
||
502 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
503 | elif period_type == 'daily': |
||
504 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
505 | elif period_type == 'weekly': |
||
506 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
507 | elif period_type == 'monthly': |
||
508 | current_datetime = current_datetime_local.strftime('%Y-%m') |
||
509 | elif period_type == 'yearly': |
||
510 | current_datetime = current_datetime_local.strftime('%Y') |
||
511 | |||
512 | actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \ |
||
513 | else row_equipment_periodically[1] |
||
514 | reporting[energy_category_id]['values_actual'].append(actual_value) |
||
515 | reporting[energy_category_id]['subtotal_actual'] += actual_value |
||
516 | reporting[energy_category_id]['subtotal_in_kgce_actual'] += actual_value * kgce |
||
517 | reporting[energy_category_id]['subtotal_in_kgco2e_actual'] += actual_value * kgco2e |
||
518 | |||
519 | # calculate reporting period's energy savings |
||
520 | for i in range(len(reporting[energy_category_id]['values_plan'])): |
||
521 | reporting[energy_category_id]['values_saving'].append( |
||
522 | reporting[energy_category_id]['values_plan'][i] - |
||
523 | reporting[energy_category_id]['values_actual'][i]) |
||
524 | |||
525 | reporting[energy_category_id]['subtotal_saving'] = \ |
||
526 | reporting[energy_category_id]['subtotal_plan'] - \ |
||
527 | reporting[energy_category_id]['subtotal_actual'] |
||
528 | reporting[energy_category_id]['subtotal_in_kgce_saving'] = \ |
||
529 | reporting[energy_category_id]['subtotal_in_kgce_plan'] - \ |
||
530 | reporting[energy_category_id]['subtotal_in_kgce_actual'] |
||
531 | reporting[energy_category_id]['subtotal_in_kgco2e_saving'] = \ |
||
532 | reporting[energy_category_id]['subtotal_in_kgco2e_plan'] - \ |
||
533 | reporting[energy_category_id]['subtotal_in_kgco2e_actual'] |
||
534 | ################################################################################################################ |
||
535 | # Step 6: query tariff data |
||
536 | ################################################################################################################ |
||
537 | parameters_data = dict() |
||
538 | parameters_data['names'] = list() |
||
539 | parameters_data['timestamps'] = list() |
||
540 | parameters_data['values'] = list() |
||
541 | if not is_quick_mode: |
||
542 | if config.is_tariff_appended and energy_category_set is not None and len(energy_category_set) > 0: |
||
543 | for energy_category_id in energy_category_set: |
||
544 | energy_category_tariff_dict = utilities.get_energy_category_tariffs(equipment['cost_center_id'], |
||
545 | energy_category_id, |
||
546 | reporting_start_datetime_utc, |
||
547 | reporting_end_datetime_utc) |
||
548 | tariff_timestamp_list = list() |
||
549 | tariff_value_list = list() |
||
550 | for k, v in energy_category_tariff_dict.items(): |
||
551 | # convert k from utc to local |
||
552 | k = k + timedelta(minutes=timezone_offset) |
||
553 | tariff_timestamp_list.append(k.isoformat()[0:19][0:19]) |
||
554 | tariff_value_list.append(v) |
||
555 | |||
556 | parameters_data['names'].append(_('Tariff') + '-' |
||
557 | + energy_category_dict[energy_category_id]['name']) |
||
558 | parameters_data['timestamps'].append(tariff_timestamp_list) |
||
559 | parameters_data['values'].append(tariff_value_list) |
||
560 | |||
561 | ################################################################################################################ |
||
562 | # Step 7: query associated points data |
||
563 | ################################################################################################################ |
||
564 | if not is_quick_mode: |
||
565 | for point in point_list: |
||
566 | point_values = [] |
||
567 | point_timestamps = [] |
||
568 | if point['object_type'] == 'ENERGY_VALUE': |
||
569 | query = (" SELECT utc_date_time, actual_value " |
||
570 | " FROM tbl_energy_value " |
||
571 | " WHERE point_id = %s " |
||
572 | " AND utc_date_time BETWEEN %s AND %s " |
||
573 | " ORDER BY utc_date_time ") |
||
574 | cursor_historical.execute(query, (point['id'], |
||
575 | reporting_start_datetime_utc, |
||
576 | reporting_end_datetime_utc)) |
||
577 | rows = cursor_historical.fetchall() |
||
578 | |||
579 | if rows is not None and len(rows) > 0: |
||
580 | for row in rows: |
||
581 | current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \ |
||
582 | timedelta(minutes=timezone_offset) |
||
583 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
584 | point_timestamps.append(current_datetime) |
||
585 | point_values.append(row[1]) |
||
586 | elif point['object_type'] == 'ANALOG_VALUE': |
||
587 | query = (" SELECT utc_date_time, actual_value " |
||
588 | " FROM tbl_analog_value " |
||
589 | " WHERE point_id = %s " |
||
590 | " AND utc_date_time BETWEEN %s AND %s " |
||
591 | " ORDER BY utc_date_time ") |
||
592 | cursor_historical.execute(query, (point['id'], |
||
593 | reporting_start_datetime_utc, |
||
594 | reporting_end_datetime_utc)) |
||
595 | rows = cursor_historical.fetchall() |
||
596 | |||
597 | if rows is not None and len(rows) > 0: |
||
598 | for row in rows: |
||
599 | current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \ |
||
600 | timedelta(minutes=timezone_offset) |
||
601 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
602 | point_timestamps.append(current_datetime) |
||
603 | point_values.append(row[1]) |
||
604 | elif point['object_type'] == 'DIGITAL_VALUE': |
||
605 | query = (" SELECT utc_date_time, actual_value " |
||
606 | " FROM tbl_digital_value " |
||
607 | " WHERE point_id = %s " |
||
608 | " AND utc_date_time BETWEEN %s AND %s " |
||
609 | " ORDER BY utc_date_time ") |
||
610 | cursor_historical.execute(query, (point['id'], |
||
611 | reporting_start_datetime_utc, |
||
612 | reporting_end_datetime_utc)) |
||
613 | rows = cursor_historical.fetchall() |
||
614 | |||
615 | if rows is not None and len(rows) > 0: |
||
616 | for row in rows: |
||
617 | current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \ |
||
618 | timedelta(minutes=timezone_offset) |
||
619 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
620 | point_timestamps.append(current_datetime) |
||
621 | point_values.append(row[1]) |
||
622 | |||
623 | parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')') |
||
624 | parameters_data['timestamps'].append(point_timestamps) |
||
625 | parameters_data['values'].append(point_values) |
||
626 | |||
627 | ################################################################################################################ |
||
628 | # Step 8: construct the report |
||
629 | ################################################################################################################ |
||
630 | if cursor_system: |
||
631 | cursor_system.close() |
||
632 | if cnx_system: |
||
633 | cnx_system.close() |
||
634 | |||
635 | if cursor_energy: |
||
636 | cursor_energy.close() |
||
637 | if cnx_energy: |
||
638 | cnx_energy.close() |
||
639 | |||
640 | if cursor_energy_plan: |
||
641 | cursor_energy_plan.close() |
||
642 | if cnx_energy_plan: |
||
643 | cnx_energy_plan.close() |
||
644 | |||
645 | if cursor_historical: |
||
646 | cursor_historical.close() |
||
647 | if cnx_historical: |
||
648 | cnx_historical.close() |
||
649 | |||
650 | result = dict() |
||
651 | |||
652 | result['equipment'] = dict() |
||
653 | result['equipment']['name'] = equipment['name'] |
||
654 | |||
655 | result['base_period'] = dict() |
||
656 | result['base_period']['names'] = list() |
||
657 | result['base_period']['units'] = list() |
||
658 | result['base_period']['timestamps'] = list() |
||
659 | result['base_period']['values_saving'] = list() |
||
660 | result['base_period']['subtotals_saving'] = list() |
||
661 | result['base_period']['subtotals_in_kgce_saving'] = list() |
||
662 | result['base_period']['subtotals_in_kgco2e_saving'] = list() |
||
663 | result['base_period']['total_in_kgce_saving'] = Decimal(0.0) |
||
664 | result['base_period']['total_in_kgco2e_saving'] = Decimal(0.0) |
||
665 | if energy_category_set is not None and len(energy_category_set) > 0: |
||
666 | for energy_category_id in energy_category_set: |
||
667 | result['base_period']['names'].append(energy_category_dict[energy_category_id]['name']) |
||
668 | result['base_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure']) |
||
669 | result['base_period']['timestamps'].append(base[energy_category_id]['timestamps']) |
||
670 | result['base_period']['values_saving'].append(base[energy_category_id]['values_saving']) |
||
671 | result['base_period']['subtotals_saving'].append(base[energy_category_id]['subtotal_saving']) |
||
672 | result['base_period']['subtotals_in_kgce_saving'].append( |
||
673 | base[energy_category_id]['subtotal_in_kgce_saving']) |
||
674 | result['base_period']['subtotals_in_kgco2e_saving'].append( |
||
675 | base[energy_category_id]['subtotal_in_kgco2e_saving']) |
||
676 | result['base_period']['total_in_kgce_saving'] += base[energy_category_id]['subtotal_in_kgce_saving'] |
||
677 | result['base_period']['total_in_kgco2e_saving'] += base[energy_category_id]['subtotal_in_kgco2e_saving'] |
||
678 | |||
679 | result['reporting_period'] = dict() |
||
680 | result['reporting_period']['names'] = list() |
||
681 | result['reporting_period']['energy_category_ids'] = list() |
||
682 | result['reporting_period']['units'] = list() |
||
683 | result['reporting_period']['timestamps'] = list() |
||
684 | result['reporting_period']['values_saving'] = list() |
||
685 | result['reporting_period']['rates_saving'] = list() |
||
686 | result['reporting_period']['subtotals_saving'] = list() |
||
687 | result['reporting_period']['subtotals_in_kgce_saving'] = list() |
||
688 | result['reporting_period']['subtotals_in_kgco2e_saving'] = list() |
||
689 | result['reporting_period']['increment_rates_saving'] = list() |
||
690 | result['reporting_period']['total_in_kgce_saving'] = Decimal(0.0) |
||
691 | result['reporting_period']['total_in_kgco2e_saving'] = Decimal(0.0) |
||
692 | result['reporting_period']['increment_rate_in_kgce_saving'] = Decimal(0.0) |
||
693 | result['reporting_period']['increment_rate_in_kgco2e_saving'] = Decimal(0.0) |
||
694 | |||
695 | if energy_category_set is not None and len(energy_category_set) > 0: |
||
696 | for energy_category_id in energy_category_set: |
||
697 | result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name']) |
||
698 | result['reporting_period']['energy_category_ids'].append(energy_category_id) |
||
699 | result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure']) |
||
700 | result['reporting_period']['timestamps'].append(reporting[energy_category_id]['timestamps']) |
||
701 | result['reporting_period']['values_saving'].append(reporting[energy_category_id]['values_saving']) |
||
702 | result['reporting_period']['subtotals_saving'].append(reporting[energy_category_id]['subtotal_saving']) |
||
703 | result['reporting_period']['subtotals_in_kgce_saving'].append( |
||
704 | reporting[energy_category_id]['subtotal_in_kgce_saving']) |
||
705 | result['reporting_period']['subtotals_in_kgco2e_saving'].append( |
||
706 | reporting[energy_category_id]['subtotal_in_kgco2e_saving']) |
||
707 | result['reporting_period']['increment_rates_saving'].append( |
||
708 | (reporting[energy_category_id]['subtotal_saving'] - base[energy_category_id]['subtotal_saving']) / |
||
709 | base[energy_category_id]['subtotal_saving'] |
||
710 | if base[energy_category_id]['subtotal_saving'] != Decimal(0.0) else None) |
||
711 | result['reporting_period']['total_in_kgce_saving'] += \ |
||
712 | reporting[energy_category_id]['subtotal_in_kgce_saving'] |
||
713 | result['reporting_period']['total_in_kgco2e_saving'] += \ |
||
714 | reporting[energy_category_id]['subtotal_in_kgco2e_saving'] |
||
715 | |||
716 | rate = list() |
||
717 | for index, value in enumerate(reporting[energy_category_id]['values_saving']): |
||
718 | if index < len(base[energy_category_id]['values_saving']) \ |
||
719 | and base[energy_category_id]['values_saving'][index] != 0 and value != 0: |
||
720 | rate.append((value - base[energy_category_id]['values_saving'][index]) |
||
721 | / base[energy_category_id]['values_saving'][index]) |
||
722 | else: |
||
723 | rate.append(None) |
||
724 | result['reporting_period']['rates_saving'].append(rate) |
||
725 | |||
726 | result['reporting_period']['increment_rate_in_kgce_saving'] = \ |
||
727 | (result['reporting_period']['total_in_kgce_saving'] - result['base_period']['total_in_kgce_saving']) / \ |
||
728 | result['base_period']['total_in_kgce_saving'] \ |
||
729 | if result['base_period']['total_in_kgce_saving'] != Decimal(0.0) else None |
||
730 | |||
731 | result['reporting_period']['increment_rate_in_kgco2e_saving'] = \ |
||
732 | (result['reporting_period']['total_in_kgco2e_saving'] - result['base_period']['total_in_kgco2e_saving']) / \ |
||
733 | result['base_period']['total_in_kgco2e_saving'] \ |
||
734 | if result['base_period']['total_in_kgco2e_saving'] != Decimal(0.0) else None |
||
735 | |||
736 | result['parameters'] = { |
||
737 | "names": parameters_data['names'], |
||
738 | "timestamps": parameters_data['timestamps'], |
||
739 | "values": parameters_data['values'] |
||
740 | } |
||
741 | |||
742 | # export result to Excel file and then encode the file to base64 string |
||
743 | result['excel_bytes_base64'] = None |
||
744 | if not is_quick_mode: |
||
745 | result['excel_bytes_base64'] = excelexporters.equipmentplan.export(result, |
||
746 | equipment['name'], |
||
747 | base_period_start_datetime_local, |
||
748 | base_period_end_datetime_local, |
||
749 | reporting_period_start_datetime_local, |
||
750 | reporting_period_end_datetime_local, |
||
751 | period_type, |
||
752 | language) |
||
753 | |||
754 | resp.text = json.dumps(result) |
||
755 |