Code Duplication    Length = 703-717 lines in 2 locations

reports/combinedequipmentefficiency.py 1 location

@@ 10-726 (lines=717) @@
7
from decimal import Decimal
8
9
10
class Reporting:
11
    @staticmethod
12
    def __init__():
13
        pass
14
15
    @staticmethod
16
    def on_options(req, resp):
17
        resp.status = falcon.HTTP_200
18
19
    ####################################################################################################################
20
    # PROCEDURES
21
    # Step 1: valid parameters
22
    # Step 2: query the combined equipment
23
    # Step 3: query associated equipments
24
    # Step 4: query energy categories
25
    # Step 5: query associated points
26
    # Step 6: query base period energy input
27
    # Step 7: query base period energy output
28
    # Step 8: query reporting period energy input
29
    # Step 9: query reporting period energy output
30
    # Step 10: query tariff data
31
    # Step 11: query associated points data
32
    # Step 12: construct the report
33
    ####################################################################################################################
34
    @staticmethod
35
    def on_get(req, resp):
36
        print(req.params)
37
        combined_equipment_id = req.params.get('combinedequipmentid')
38
        period_type = req.params.get('periodtype')
39
        base_start_datetime_local = req.params.get('baseperiodstartdatetime')
40
        base_end_datetime_local = req.params.get('baseperiodenddatetime')
41
        reporting_start_datetime_local = req.params.get('reportingperiodstartdatetime')
42
        reporting_end_datetime_local = req.params.get('reportingperiodenddatetime')
43
44
        ################################################################################################################
45
        # Step 1: valid parameters
46
        ################################################################################################################
47
        if combined_equipment_id is None:
48
            raise falcon.HTTPError(falcon.HTTP_400,
49
                                   title='API.BAD_REQUEST',
50
                                   description='API.INVALID_COMBINED_EQUIPMENT_ID')
51
        else:
52
            combined_equipment_id = str.strip(combined_equipment_id)
53
            if not combined_equipment_id.isdigit() or int(combined_equipment_id) <= 0:
54
                raise falcon.HTTPError(falcon.HTTP_400,
55
                                       title='API.BAD_REQUEST',
56
                                       description='API.INVALID_COMBINED_EQUIPMENT_ID')
57
58
        if period_type is None:
59
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
60
        else:
61
            period_type = str.strip(period_type)
62
            if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
63
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
64
65
        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
66
        if config.utc_offset[0] == '-':
67
            timezone_offset = -timezone_offset
68
69
        base_start_datetime_utc = None
70
        if base_start_datetime_local is not None and len(str.strip(base_start_datetime_local)) > 0:
71
            base_start_datetime_local = str.strip(base_start_datetime_local)
72
            try:
73
                base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
74
                                                            '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
75
                    timedelta(minutes=timezone_offset)
76
            except ValueError:
77
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
78
                                       description="API.INVALID_BASE_PERIOD_START_DATETIME")
79
80
        base_end_datetime_utc = None
81
        if base_end_datetime_local is not None and len(str.strip(base_end_datetime_local)) > 0:
82
            base_end_datetime_local = str.strip(base_end_datetime_local)
83
            try:
84
                base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
85
                                                          '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
86
                    timedelta(minutes=timezone_offset)
87
            except ValueError:
88
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
89
                                       description="API.INVALID_BASE_PERIOD_END_DATETIME")
90
91
        if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
92
                base_start_datetime_utc >= base_end_datetime_utc:
93
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
94
                                   description='API.INVALID_BASE_PERIOD_END_DATETIME')
95
96
        if reporting_start_datetime_local is None:
97
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
98
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
99
        else:
100
            reporting_start_datetime_local = str.strip(reporting_start_datetime_local)
101
            try:
102
                reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
103
                                                                 '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
104
                    timedelta(minutes=timezone_offset)
105
            except ValueError:
106
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
107
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
108
109
        if reporting_end_datetime_local is None:
110
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
111
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
112
        else:
113
            reporting_end_datetime_local = str.strip(reporting_end_datetime_local)
114
            try:
115
                reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
116
                                                               '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
117
                    timedelta(minutes=timezone_offset)
118
            except ValueError:
119
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
120
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
121
122
        if reporting_start_datetime_utc >= reporting_end_datetime_utc:
123
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
124
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
125
126
        ################################################################################################################
127
        # Step 2: query the combined equipment
128
        ################################################################################################################
129
        cnx_system = mysql.connector.connect(**config.myems_system_db)
130
        cursor_system = cnx_system.cursor()
131
132
        cnx_energy = mysql.connector.connect(**config.myems_energy_db)
133
        cursor_energy = cnx_energy.cursor()
134
135
        cnx_historical = mysql.connector.connect(**config.myems_historical_db)
136
        cursor_historical = cnx_historical.cursor()
137
138
        cursor_system.execute(" SELECT id, name, cost_center_id "
139
                              " FROM tbl_combined_equipments "
140
                              " WHERE id = %s ", (combined_equipment_id,))
141
        row_combined_equipment = cursor_system.fetchone()
142
        if row_combined_equipment is None:
143
            if cursor_system:
144
                cursor_system.close()
145
            if cnx_system:
146
                cnx_system.disconnect()
147
148
            if cursor_energy:
149
                cursor_energy.close()
150
            if cnx_energy:
151
                cnx_energy.disconnect()
152
153
            if cnx_historical:
154
                cnx_historical.close()
155
            if cursor_historical:
156
                cursor_historical.disconnect()
157
            raise falcon.HTTPError(falcon.HTTP_404,
158
                                   title='API.NOT_FOUND',
159
                                   description='API.COMBINED_EQUIPMENT_NOT_FOUND')
160
161
        combined_equipment = dict()
162
        combined_equipment['id'] = row_combined_equipment[0]
163
        combined_equipment['name'] = row_combined_equipment[1]
164
        combined_equipment['cost_center_id'] = row_combined_equipment[2]
165
        ################################################################################################################
166
        # Step 3: query associated equipments
167
        ################################################################################################################
168
        # todo
169
170
        ################################################################################################################
171
        # Step 4: query input energy categories and output energy categories
172
        ################################################################################################################
173
        energy_category_set_input = set()
174
        energy_category_set_output = set()
175
        # query input energy categories in base period
176
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
177
                              " FROM tbl_combined_equipment_input_category_hourly "
178
                              " WHERE combined_equipment_id = %s "
179
                              "     AND start_datetime_utc >= %s "
180
                              "     AND start_datetime_utc < %s ",
181
                              (combined_equipment['id'], base_start_datetime_utc, base_end_datetime_utc))
182
        rows_energy_categories = cursor_energy.fetchall()
183
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
184
            for row_energy_category in rows_energy_categories:
185
                energy_category_set_input.add(row_energy_category[0])
186
187
        # query input energy categories in reporting period
188
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
189
                              " FROM tbl_combined_equipment_input_category_hourly "
190
                              " WHERE combined_equipment_id = %s "
191
                              "     AND start_datetime_utc >= %s "
192
                              "     AND start_datetime_utc < %s ",
193
                              (combined_equipment['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
194
        rows_energy_categories = cursor_energy.fetchall()
195
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
196
            for row_energy_category in rows_energy_categories:
197
                energy_category_set_input.add(row_energy_category[0])
198
199
        # query output energy categories in base period
200
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
201
                              " FROM tbl_combined_equipment_output_category_hourly "
202
                              " WHERE combined_equipment_id = %s "
203
                              "     AND start_datetime_utc >= %s "
204
                              "     AND start_datetime_utc < %s ",
205
                              (combined_equipment['id'], base_start_datetime_utc, base_end_datetime_utc))
206
        rows_energy_categories = cursor_energy.fetchall()
207
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
208
            for row_energy_category in rows_energy_categories:
209
                energy_category_set_output.add(row_energy_category[0])
210
211
        # query output energy categories in reporting period
212
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
213
                              " FROM tbl_combined_equipment_output_category_hourly "
214
                              " WHERE combined_equipment_id = %s "
215
                              "     AND start_datetime_utc >= %s "
216
                              "     AND start_datetime_utc < %s ",
217
                              (combined_equipment['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
218
        rows_energy_categories = cursor_energy.fetchall()
219
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
220
            for row_energy_category in rows_energy_categories:
221
                energy_category_set_output.add(row_energy_category[0])
222
223
        # query properties of all energy categories above
224
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
225
                              " FROM tbl_energy_categories "
226
                              " ORDER BY id ", )
227
        rows_energy_categories = cursor_system.fetchall()
228
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
229
            if cursor_system:
230
                cursor_system.close()
231
            if cnx_system:
232
                cnx_system.disconnect()
233
234
            if cursor_energy:
235
                cursor_energy.close()
236
            if cnx_energy:
237
                cnx_energy.disconnect()
238
239
            if cnx_historical:
240
                cnx_historical.close()
241
            if cursor_historical:
242
                cursor_historical.disconnect()
243
            raise falcon.HTTPError(falcon.HTTP_404,
244
                                   title='API.NOT_FOUND',
245
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
246
        energy_category_dict = dict()
247
        for row_energy_category in rows_energy_categories:
248
            if row_energy_category[0] in energy_category_set_input or \
249
                    row_energy_category[0] in energy_category_set_output:
250
                energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
251
                                                                "unit_of_measure": row_energy_category[2],
252
                                                                "kgce": row_energy_category[3],
253
                                                                "kgco2e": row_energy_category[4]}
254
255
        ################################################################################################################
256
        # Step 5: query associated points
257
        ################################################################################################################
258
        point_list = list()
259
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
260
                              " FROM tbl_combined_equipments e, tbl_combined_equipments_parameters ep, tbl_points p "
261
                              " WHERE e.id = %s AND e.id = ep.combined_equipment_id AND ep.parameter_type = 'point' "
262
                              "       AND ep.point_id = p.id "
263
                              " ORDER BY p.id ", (combined_equipment['id'],))
264
        rows_points = cursor_system.fetchall()
265
        if rows_points is not None and len(rows_points) > 0:
266
            for row in rows_points:
267
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
268
269
        ################################################################################################################
270
        # Step 6: query base period energy input
271
        ################################################################################################################
272
        base_input = dict()
273
        if energy_category_set_input is not None and len(energy_category_set_input) > 0:
274
            for energy_category_id in energy_category_set_input:
275
                base_input[energy_category_id] = dict()
276
                base_input[energy_category_id]['timestamps'] = list()
277
                base_input[energy_category_id]['values'] = list()
278
                base_input[energy_category_id]['subtotal'] = Decimal(0.0)
279
280
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
281
                                      " FROM tbl_combined_equipment_input_category_hourly "
282
                                      " WHERE combined_equipment_id = %s "
283
                                      "     AND energy_category_id = %s "
284
                                      "     AND start_datetime_utc >= %s "
285
                                      "     AND start_datetime_utc < %s "
286
                                      " ORDER BY start_datetime_utc ",
287
                                      (combined_equipment['id'],
288
                                       energy_category_id,
289
                                       base_start_datetime_utc,
290
                                       base_end_datetime_utc))
291
                rows_combined_equipment_hourly = cursor_energy.fetchall()
292
293
                rows_combined_equipment_periodically = \
294
                    utilities.aggregate_hourly_data_by_period(rows_combined_equipment_hourly,
295
                                                              base_start_datetime_utc,
296
                                                              base_end_datetime_utc,
297
                                                              period_type)
298
                for row_combined_equipment_periodically in rows_combined_equipment_periodically:
299
                    current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
300
                                             timedelta(minutes=timezone_offset)
301
                    if period_type == 'hourly':
302
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
303
                    elif period_type == 'daily':
304
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
305
                    elif period_type == 'monthly':
306
                        current_datetime = current_datetime_local.strftime('%Y-%m')
307
                    elif period_type == 'yearly':
308
                        current_datetime = current_datetime_local.strftime('%Y')
309
310
                    actual_value = Decimal(0.0) if row_combined_equipment_periodically[1] is None \
311
                        else row_combined_equipment_periodically[1]
312
                    base_input[energy_category_id]['timestamps'].append(current_datetime)
313
                    base_input[energy_category_id]['values'].append(actual_value)
314
                    base_input[energy_category_id]['subtotal'] += actual_value
315
316
        ################################################################################################################
317
        # Step 7: query base period energy output
318
        ################################################################################################################
319
        base_output = dict()
320
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
321
            for energy_category_id in energy_category_set_output:
322
                base_output[energy_category_id] = dict()
323
                base_output[energy_category_id]['timestamps'] = list()
324
                base_output[energy_category_id]['values'] = list()
325
                base_output[energy_category_id]['subtotal'] = Decimal(0.0)
326
327
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
328
                                      " FROM tbl_combined_equipment_output_category_hourly "
329
                                      " WHERE combined_equipment_id = %s "
330
                                      "     AND energy_category_id = %s "
331
                                      "     AND start_datetime_utc >= %s "
332
                                      "     AND start_datetime_utc < %s "
333
                                      " ORDER BY start_datetime_utc ",
334
                                      (combined_equipment['id'],
335
                                       energy_category_id,
336
                                       base_start_datetime_utc,
337
                                       base_end_datetime_utc))
338
                rows_combined_equipment_hourly = cursor_energy.fetchall()
339
340
                rows_combined_equipment_periodically = \
341
                    utilities.aggregate_hourly_data_by_period(rows_combined_equipment_hourly,
342
                                                              base_start_datetime_utc,
343
                                                              base_end_datetime_utc,
344
                                                              period_type)
345
                for row_combined_equipment_periodically in rows_combined_equipment_periodically:
346
                    current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
347
                                             timedelta(minutes=timezone_offset)
348
                    if period_type == 'hourly':
349
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
350
                    elif period_type == 'daily':
351
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
352
                    elif period_type == 'monthly':
353
                        current_datetime = current_datetime_local.strftime('%Y-%m')
354
                    elif period_type == 'yearly':
355
                        current_datetime = current_datetime_local.strftime('%Y')
356
357
                    actual_value = Decimal(0.0) if row_combined_equipment_periodically[1] is None \
358
                        else row_combined_equipment_periodically[1]
359
                    base_output[energy_category_id]['timestamps'].append(current_datetime)
360
                    base_output[energy_category_id]['values'].append(actual_value)
361
                    base_output[energy_category_id]['subtotal'] += actual_value
362
        ################################################################################################################
363
        # Step 8: query reporting period energy input
364
        ################################################################################################################
365
        reporting_input = dict()
366
        if energy_category_set_input is not None and len(energy_category_set_input) > 0:
367
            for energy_category_id in energy_category_set_input:
368
369
                reporting_input[energy_category_id] = dict()
370
                reporting_input[energy_category_id]['timestamps'] = list()
371
                reporting_input[energy_category_id]['values'] = list()
372
                reporting_input[energy_category_id]['subtotal'] = Decimal(0.0)
373
374
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
375
                                      " FROM tbl_combined_equipment_input_category_hourly "
376
                                      " WHERE combined_equipment_id = %s "
377
                                      "     AND energy_category_id = %s "
378
                                      "     AND start_datetime_utc >= %s "
379
                                      "     AND start_datetime_utc < %s "
380
                                      " ORDER BY start_datetime_utc ",
381
                                      (combined_equipment['id'],
382
                                       energy_category_id,
383
                                       reporting_start_datetime_utc,
384
                                       reporting_end_datetime_utc))
385
                rows_combined_equipment_hourly = cursor_energy.fetchall()
386
387
                rows_combined_equipment_periodically = \
388
                    utilities.aggregate_hourly_data_by_period(rows_combined_equipment_hourly,
389
                                                              reporting_start_datetime_utc,
390
                                                              reporting_end_datetime_utc,
391
                                                              period_type)
392
                for row_combined_equipment_periodically in rows_combined_equipment_periodically:
393
                    current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
394
                                             timedelta(minutes=timezone_offset)
395
                    if period_type == 'hourly':
396
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
397
                    elif period_type == 'daily':
398
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
399
                    elif period_type == 'monthly':
400
                        current_datetime = current_datetime_local.strftime('%Y-%m')
401
                    elif period_type == 'yearly':
402
                        current_datetime = current_datetime_local.strftime('%Y')
403
404
                    actual_value = Decimal(0.0) if row_combined_equipment_periodically[1] is None \
405
                        else row_combined_equipment_periodically[1]
406
                    reporting_input[energy_category_id]['timestamps'].append(current_datetime)
407
                    reporting_input[energy_category_id]['values'].append(actual_value)
408
                    reporting_input[energy_category_id]['subtotal'] += actual_value
409
410
        ################################################################################################################
411
        # Step 9: query reporting period energy output
412
        ################################################################################################################
413
        reporting_output = dict()
414
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
415
            for energy_category_id in energy_category_set_output:
416
417
                reporting_output[energy_category_id] = dict()
418
                reporting_output[energy_category_id]['timestamps'] = list()
419
                reporting_output[energy_category_id]['values'] = list()
420
                reporting_output[energy_category_id]['subtotal'] = Decimal(0.0)
421
422
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
423
                                      " FROM tbl_combined_equipment_output_category_hourly "
424
                                      " WHERE combined_equipment_id = %s "
425
                                      "     AND energy_category_id = %s "
426
                                      "     AND start_datetime_utc >= %s "
427
                                      "     AND start_datetime_utc < %s "
428
                                      " ORDER BY start_datetime_utc ",
429
                                      (combined_equipment['id'],
430
                                       energy_category_id,
431
                                       reporting_start_datetime_utc,
432
                                       reporting_end_datetime_utc))
433
                rows_combined_equipment_hourly = cursor_energy.fetchall()
434
435
                rows_combined_equipment_periodically = \
436
                    utilities.aggregate_hourly_data_by_period(rows_combined_equipment_hourly,
437
                                                              reporting_start_datetime_utc,
438
                                                              reporting_end_datetime_utc,
439
                                                              period_type)
440
                for row_combined_equipment_periodically in rows_combined_equipment_periodically:
441
                    current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
442
                                             timedelta(minutes=timezone_offset)
443
                    if period_type == 'hourly':
444
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
445
                    elif period_type == 'daily':
446
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
447
                    elif period_type == 'monthly':
448
                        current_datetime = current_datetime_local.strftime('%Y-%m')
449
                    elif period_type == 'yearly':
450
                        current_datetime = current_datetime_local.strftime('%Y')
451
452
                    actual_value = Decimal(0.0) if row_combined_equipment_periodically[1] is None \
453
                        else row_combined_equipment_periodically[1]
454
                    reporting_output[energy_category_id]['timestamps'].append(current_datetime)
455
                    reporting_output[energy_category_id]['values'].append(actual_value)
456
                    reporting_output[energy_category_id]['subtotal'] += actual_value
457
458
        ################################################################################################################
459
        # Step 10: query tariff data
460
        ################################################################################################################
461
        parameters_data = dict()
462
        parameters_data['names'] = list()
463
        parameters_data['timestamps'] = list()
464
        parameters_data['values'] = list()
465
        if energy_category_set_input is not None and len(energy_category_set_input) > 0:
466
            for energy_category_id in energy_category_set_input:
467
                energy_category_tariff_dict = utilities.get_energy_category_tariffs(
468
                    combined_equipment['cost_center_id'],
469
                    energy_category_id,
470
                    reporting_start_datetime_utc,
471
                    reporting_end_datetime_utc)
472
473
                tariff_timestamp_list = list()
474
                tariff_value_list = list()
475
                for k, v in energy_category_tariff_dict.items():
476
                    # convert k from utc to local
477
                    k = k + timedelta(minutes=timezone_offset)
478
                    tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
479
                    tariff_value_list.append(v)
480
481
                parameters_data['names'].append('TARIFF-' + energy_category_dict[energy_category_id]['name'])
482
                parameters_data['timestamps'].append(tariff_timestamp_list)
483
                parameters_data['values'].append(tariff_value_list)
484
485
        ################################################################################################################
486
        # Step 11: query associated points data
487
        ################################################################################################################
488
        for point in point_list:
489
            point_values = []
490
            point_timestamps = []
491
            if point['object_type'] == 'ANALOG_VALUE':
492
                query = (" SELECT utc_date_time, actual_value "
493
                         " FROM tbl_analog_value "
494
                         " WHERE point_id = %s "
495
                         "       AND utc_date_time BETWEEN %s AND %s "
496
                         " ORDER BY utc_date_time ")
497
                cursor_historical.execute(query, (point['id'],
498
                                                  reporting_start_datetime_utc,
499
                                                  reporting_end_datetime_utc))
500
                rows = cursor_historical.fetchall()
501
502
                if rows is not None and len(rows) > 0:
503
                    for row in rows:
504
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
505
                                                 timedelta(minutes=timezone_offset)
506
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
507
                        point_timestamps.append(current_datetime)
508
                        point_values.append(row[1])
509
510
            elif point['object_type'] == 'ENERGY_VALUE':
511
                query = (" SELECT utc_date_time, actual_value "
512
                         " FROM tbl_energy_value "
513
                         " WHERE point_id = %s "
514
                         "       AND utc_date_time BETWEEN %s AND %s "
515
                         " ORDER BY utc_date_time ")
516
                cursor_historical.execute(query, (point['id'],
517
                                                  reporting_start_datetime_utc,
518
                                                  reporting_end_datetime_utc))
519
                rows = cursor_historical.fetchall()
520
521
                if rows is not None and len(rows) > 0:
522
                    for row in rows:
523
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
524
                                                 timedelta(minutes=timezone_offset)
525
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
526
                        point_timestamps.append(current_datetime)
527
                        point_values.append(row[1])
528
            elif point['object_type'] == 'DIGITAL_VALUE':
529
                query = (" SELECT utc_date_time, actual_value "
530
                         " FROM tbl_digital_value "
531
                         " WHERE point_id = %s "
532
                         "       AND utc_date_time BETWEEN %s AND %s ")
533
                cursor_historical.execute(query, (point['id'],
534
                                                  reporting_start_datetime_utc,
535
                                                  reporting_end_datetime_utc))
536
                rows = cursor_historical.fetchall()
537
538
                if rows is not None and len(rows) > 0:
539
                    for row in rows:
540
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
541
                                                 timedelta(minutes=timezone_offset)
542
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
543
                        point_timestamps.append(current_datetime)
544
                        point_values.append(row[1])
545
546
            parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
547
            parameters_data['timestamps'].append(point_timestamps)
548
            parameters_data['values'].append(point_values)
549
550
        ################################################################################################################
551
        # Step 12: construct the report
552
        ################################################################################################################
553
        if cursor_system:
554
            cursor_system.close()
555
        if cnx_system:
556
            cnx_system.disconnect()
557
558
        if cursor_energy:
559
            cursor_energy.close()
560
        if cnx_energy:
561
            cnx_energy.disconnect()
562
563
        result = dict()
564
565
        result['combined_equipment'] = dict()
566
        result['combined_equipment']['name'] = combined_equipment['name']
567
568
        result['base_period_input'] = dict()
569
        result['base_period_input']['names'] = list()
570
        result['base_period_input']['units'] = list()
571
        result['base_period_input']['timestamps'] = list()
572
        result['base_period_input']['values'] = list()
573
        result['base_period_input']['subtotals'] = list()
574
        if energy_category_set_input is not None and len(energy_category_set_input) > 0:
575
            for energy_category_id in energy_category_set_input:
576
                result['base_period_input']['names'].append(energy_category_dict[energy_category_id]['name'])
577
                result['base_period_input']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
578
                result['base_period_input']['timestamps'].append(base_input[energy_category_id]['timestamps'])
579
                result['base_period_input']['values'].append(base_input[energy_category_id]['values'])
580
                result['base_period_input']['subtotals'].append(base_input[energy_category_id]['subtotal'])
581
582
        result['base_period_output'] = dict()
583
        result['base_period_output']['names'] = list()
584
        result['base_period_output']['units'] = list()
585
        result['base_period_output']['timestamps'] = list()
586
        result['base_period_output']['values'] = list()
587
        result['base_period_output']['subtotals'] = list()
588
589
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
590
            for energy_category_id in energy_category_set_output:
591
                result['base_period_output']['names'].append(energy_category_dict[energy_category_id]['name'])
592
                result['base_period_output']['units'].append(
593
                    energy_category_dict[energy_category_id]['unit_of_measure'])
594
                result['base_period_output']['timestamps'].append(base_output[energy_category_id]['timestamps'])
595
                result['base_period_output']['values'].append(base_output[energy_category_id]['values'])
596
                result['base_period_output']['subtotals'].append(base_output[energy_category_id]['subtotal'])
597
598
        result['base_period_efficiency'] = dict()
599
        result['base_period_efficiency']['names'] = list()
600
        result['base_period_efficiency']['units'] = list()
601
        result['base_period_efficiency']['timestamps'] = list()
602
        result['base_period_efficiency']['values'] = list()
603
        result['base_period_efficiency']['cumulations'] = list()
604
605
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
606
            for energy_category_id_output in energy_category_set_output:
607
                for energy_category_id_input in energy_category_set_input:
608
                    result['base_period_efficiency']['names'].append(
609
                        energy_category_dict[energy_category_id_output]['name'] + '/' +
610
                        energy_category_dict[energy_category_id_input]['name'])
611
                    result['base_period_efficiency']['units'].append(
612
                        energy_category_dict[energy_category_id_output]['unit_of_measure'] + '/' +
613
                        energy_category_dict[energy_category_id_input]['unit_of_measure'])
614
                    result['base_period_efficiency']['timestamps'].append(
615
                        base_output[energy_category_id_output]['timestamps'])
616
                    efficiency_values = list()
617
                    for i in range(len(base_output[energy_category_id_output]['timestamps'])):
618
                        efficiency_values.append((base_output[energy_category_id_output]['values'][i] /
619
                                                  base_input[energy_category_id_input]['values'][i])
620
                                                 if base_input[energy_category_id_input]['values'][i] > Decimal(0.0)
621
                                                 else None)
622
                    result['base_period_efficiency']['values'].append(efficiency_values)
623
624
                    base_cumulation = (base_output[energy_category_id_output]['subtotal'] /
625
                                       base_input[energy_category_id_input]['subtotal']) if \
626
                        base_input[energy_category_id_input]['subtotal'] > Decimal(0.0) else None
627
                    result['base_period_efficiency']['cumulations'].append(base_cumulation)
628
629
        result['reporting_period_input'] = dict()
630
        result['reporting_period_input']['names'] = list()
631
        result['reporting_period_input']['energy_category_ids'] = list()
632
        result['reporting_period_input']['units'] = list()
633
        result['reporting_period_input']['timestamps'] = list()
634
        result['reporting_period_input']['values'] = list()
635
        result['reporting_period_input']['subtotals'] = list()
636
        result['reporting_period_input']['increment_rates'] = list()
637
638
        if energy_category_set_input is not None and len(energy_category_set_input) > 0:
639
            for energy_category_id in energy_category_set_input:
640
                result['reporting_period_input']['names'].append(energy_category_dict[energy_category_id]['name'])
641
                result['reporting_period_input']['energy_category_ids'].append(energy_category_id)
642
                result['reporting_period_input']['units'].append(
643
                    energy_category_dict[energy_category_id]['unit_of_measure'])
644
                result['reporting_period_input']['timestamps'].append(
645
                    reporting_input[energy_category_id]['timestamps'])
646
                result['reporting_period_input']['values'].append(
647
                    reporting_input[energy_category_id]['values'])
648
                result['reporting_period_input']['subtotals'].append(
649
                    reporting_input[energy_category_id]['subtotal'])
650
                result['reporting_period_input']['increment_rates'].append(
651
                    (reporting_input[energy_category_id]['subtotal'] -
652
                     base_input[energy_category_id]['subtotal']) /
653
                    base_input[energy_category_id]['subtotal']
654
                    if base_input[energy_category_id]['subtotal'] > 0.0 else None)
655
656
        result['reporting_period_output'] = dict()
657
        result['reporting_period_output']['names'] = list()
658
        result['reporting_period_output']['energy_category_ids'] = list()
659
        result['reporting_period_output']['units'] = list()
660
        result['reporting_period_output']['timestamps'] = list()
661
        result['reporting_period_output']['values'] = list()
662
        result['reporting_period_output']['subtotals'] = list()
663
        result['reporting_period_output']['increment_rates'] = list()
664
665
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
666
            for energy_category_id in energy_category_set_output:
667
                result['reporting_period_output']['names'].append(energy_category_dict[energy_category_id]['name'])
668
                result['reporting_period_output']['energy_category_ids'].append(energy_category_id)
669
                result['reporting_period_output']['units'].append(
670
                    energy_category_dict[energy_category_id]['unit_of_measure'])
671
                result['reporting_period_output']['timestamps'].append(
672
                    reporting_output[energy_category_id]['timestamps'])
673
                result['reporting_period_output']['values'].append(reporting_output[energy_category_id]['values'])
674
                result['reporting_period_output']['subtotals'].append(reporting_output[energy_category_id]['subtotal'])
675
                result['reporting_period_output']['increment_rates'].append(
676
                    (reporting_output[energy_category_id]['subtotal'] -
677
                     base_output[energy_category_id]['subtotal']) /
678
                    base_output[energy_category_id]['subtotal']
679
                    if base_output[energy_category_id]['subtotal'] > 0.0 else None)
680
681
        result['reporting_period_efficiency'] = dict()
682
        result['reporting_period_efficiency']['names'] = list()
683
        result['reporting_period_efficiency']['units'] = list()
684
        result['reporting_period_efficiency']['timestamps'] = list()
685
        result['reporting_period_efficiency']['values'] = list()
686
        result['reporting_period_efficiency']['cumulations'] = list()
687
        result['reporting_period_efficiency']['increment_rates'] = list()
688
689
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
690
            for energy_category_id_output in energy_category_set_output:
691
                for energy_category_id_input in energy_category_set_input:
692
                    result['reporting_period_efficiency']['names'].append(
693
                        energy_category_dict[energy_category_id_output]['name'] + '/' +
694
                        energy_category_dict[energy_category_id_input]['name'])
695
                    result['reporting_period_efficiency']['units'].append(
696
                        energy_category_dict[energy_category_id_output]['unit_of_measure'] + '/' +
697
                        energy_category_dict[energy_category_id_input]['unit_of_measure'])
698
                    result['reporting_period_efficiency']['timestamps'].append(
699
                        reporting_output[energy_category_id_output]['timestamps'])
700
                    efficiency_values = list()
701
                    for i in range(len(reporting_output[energy_category_id_output]['timestamps'])):
702
                        efficiency_values.append((reporting_output[energy_category_id_output]['values'][i] /
703
                                                 reporting_input[energy_category_id_input]['values'][i])
704
                                                 if reporting_input[energy_category_id_input]['values'][i] >
705
                                                 Decimal(0.0) else None)
706
                    result['reporting_period_efficiency']['values'].append(efficiency_values)
707
708
                    base_cumulation = (base_output[energy_category_id_output]['subtotal'] /
709
                                       base_input[energy_category_id_input]['subtotal']) if \
710
                        base_input[energy_category_id_input]['subtotal'] > Decimal(0.0) else None
711
712
                    reporting_cumulation = (reporting_output[energy_category_id_output]['subtotal'] /
713
                                            reporting_input[energy_category_id_input]['subtotal']) if \
714
                        reporting_input[energy_category_id_input]['subtotal'] > Decimal(0.0) else None
715
716
                    result['reporting_period_efficiency']['cumulations'].append(reporting_cumulation)
717
                    result['reporting_period_efficiency']['increment_rates'].append(
718
                        ((reporting_cumulation - base_cumulation) / base_cumulation if (base_cumulation > Decimal(0.0))
719
                         else None)
720
                    )
721
722
        result['parameters'] = {
723
            "names": parameters_data['names'],
724
            "timestamps": parameters_data['timestamps'],
725
            "values": parameters_data['values']
726
        }
727
728
        resp.body = json.dumps(result)
729

reports/equipmentefficiency.py 1 location

@@ 10-712 (lines=703) @@
7
from decimal import Decimal
8
9
10
class Reporting:
11
    @staticmethod
12
    def __init__():
13
        pass
14
15
    @staticmethod
16
    def on_options(req, resp):
17
        resp.status = falcon.HTTP_200
18
19
    ####################################################################################################################
20
    # PROCEDURES
21
    # Step 1: valid parameters
22
    # Step 2: query the equipment
23
    # Step 3: query energy categories
24
    # Step 4: query associated constants
25
    # Step 4: query associated points
26
    # Step 5: query associated fractions
27
    # Step 5: query base period energy input
28
    # Step 6: query base period energy output
29
    # Step 7: query reporting period energy input
30
    # Step 8: query reporting period energy output
31
    # Step 9: query tariff data
32
    # Step 10: query associated points data
33
    # Step 11: construct the report
34
    ####################################################################################################################
35
    @staticmethod
36
    def on_get(req, resp):
37
        print(req.params)
38
        equipment_id = req.params.get('equipmentid')
39
        period_type = req.params.get('periodtype')
40
        base_start_datetime_local = req.params.get('baseperiodstartdatetime')
41
        base_end_datetime_local = req.params.get('baseperiodenddatetime')
42
        reporting_start_datetime_local = req.params.get('reportingperiodstartdatetime')
43
        reporting_end_datetime_local = req.params.get('reportingperiodenddatetime')
44
45
        ################################################################################################################
46
        # Step 1: valid parameters
47
        ################################################################################################################
48
        if equipment_id is None:
49
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_EQUIPMENT_ID')
50
        else:
51
            equipment_id = str.strip(equipment_id)
52
            if not equipment_id.isdigit() or int(equipment_id) <= 0:
53
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_EQUIPMENT_ID')
54
55
        if period_type is None:
56
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
57
        else:
58
            period_type = str.strip(period_type)
59
            if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
60
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
61
62
        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
63
        if config.utc_offset[0] == '-':
64
            timezone_offset = -timezone_offset
65
66
        base_start_datetime_utc = None
67
        if base_start_datetime_local is not None and len(str.strip(base_start_datetime_local)) > 0:
68
            base_start_datetime_local = str.strip(base_start_datetime_local)
69
            try:
70
                base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
71
                                                            '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
72
                    timedelta(minutes=timezone_offset)
73
            except ValueError:
74
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
75
                                       description="API.INVALID_BASE_PERIOD_START_DATETIME")
76
77
        base_end_datetime_utc = None
78
        if base_end_datetime_local is not None and len(str.strip(base_end_datetime_local)) > 0:
79
            base_end_datetime_local = str.strip(base_end_datetime_local)
80
            try:
81
                base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
82
                                                          '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
83
                    timedelta(minutes=timezone_offset)
84
            except ValueError:
85
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
86
                                       description="API.INVALID_BASE_PERIOD_END_DATETIME")
87
88
        if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
89
                base_start_datetime_utc >= base_end_datetime_utc:
90
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
91
                                   description='API.INVALID_BASE_PERIOD_END_DATETIME')
92
93
        if reporting_start_datetime_local is None:
94
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
95
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
96
        else:
97
            reporting_start_datetime_local = str.strip(reporting_start_datetime_local)
98
            try:
99
                reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
100
                                                                 '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
101
                    timedelta(minutes=timezone_offset)
102
            except ValueError:
103
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
104
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
105
106
        if reporting_end_datetime_local is None:
107
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
108
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
109
        else:
110
            reporting_end_datetime_local = str.strip(reporting_end_datetime_local)
111
            try:
112
                reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
113
                                                               '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
114
                    timedelta(minutes=timezone_offset)
115
            except ValueError:
116
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
117
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
118
119
        if reporting_start_datetime_utc >= reporting_end_datetime_utc:
120
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
121
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
122
123
        ################################################################################################################
124
        # Step 2: query the equipment
125
        ################################################################################################################
126
        cnx_system = mysql.connector.connect(**config.myems_system_db)
127
        cursor_system = cnx_system.cursor()
128
129
        cnx_energy = mysql.connector.connect(**config.myems_energy_db)
130
        cursor_energy = cnx_energy.cursor()
131
132
        cnx_historical = mysql.connector.connect(**config.myems_historical_db)
133
        cursor_historical = cnx_historical.cursor()
134
135
        cursor_system.execute(" SELECT id, name, cost_center_id "
136
                              " FROM tbl_equipments "
137
                              " WHERE id = %s ", (equipment_id,))
138
        row_equipment = cursor_system.fetchone()
139
        if row_equipment is None:
140
            if cursor_system:
141
                cursor_system.close()
142
            if cnx_system:
143
                cnx_system.disconnect()
144
145
            if cursor_energy:
146
                cursor_energy.close()
147
            if cnx_energy:
148
                cnx_energy.disconnect()
149
150
            if cnx_historical:
151
                cnx_historical.close()
152
            if cursor_historical:
153
                cursor_historical.disconnect()
154
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.EQUIPMENT_NOT_FOUND')
155
156
        equipment = dict()
157
        equipment['id'] = row_equipment[0]
158
        equipment['name'] = row_equipment[1]
159
        equipment['cost_center_id'] = row_equipment[2]
160
161
        ################################################################################################################
162
        # Step 3: query input energy categories and output energy categories
163
        ################################################################################################################
164
        energy_category_set_input = set()
165
        energy_category_set_output = set()
166
        # query input energy categories in base period
167
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
168
                              " FROM tbl_equipment_input_category_hourly "
169
                              " WHERE equipment_id = %s "
170
                              "     AND start_datetime_utc >= %s "
171
                              "     AND start_datetime_utc < %s ",
172
                              (equipment['id'], base_start_datetime_utc, base_end_datetime_utc))
173
        rows_energy_categories = cursor_energy.fetchall()
174
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
175
            for row_energy_category in rows_energy_categories:
176
                energy_category_set_input.add(row_energy_category[0])
177
178
        # query input energy categories in reporting period
179
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
180
                              " FROM tbl_equipment_input_category_hourly "
181
                              " WHERE equipment_id = %s "
182
                              "     AND start_datetime_utc >= %s "
183
                              "     AND start_datetime_utc < %s ",
184
                              (equipment['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
185
        rows_energy_categories = cursor_energy.fetchall()
186
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
187
            for row_energy_category in rows_energy_categories:
188
                energy_category_set_input.add(row_energy_category[0])
189
190
        # query output energy categories in base period
191
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
192
                              " FROM tbl_equipment_output_category_hourly "
193
                              " WHERE equipment_id = %s "
194
                              "     AND start_datetime_utc >= %s "
195
                              "     AND start_datetime_utc < %s ",
196
                              (equipment['id'], base_start_datetime_utc, base_end_datetime_utc))
197
        rows_energy_categories = cursor_energy.fetchall()
198
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
199
            for row_energy_category in rows_energy_categories:
200
                energy_category_set_output.add(row_energy_category[0])
201
202
        # query output energy categories in reporting period
203
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
204
                              " FROM tbl_equipment_output_category_hourly "
205
                              " WHERE equipment_id = %s "
206
                              "     AND start_datetime_utc >= %s "
207
                              "     AND start_datetime_utc < %s ",
208
                              (equipment['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
209
        rows_energy_categories = cursor_energy.fetchall()
210
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
211
            for row_energy_category in rows_energy_categories:
212
                energy_category_set_output.add(row_energy_category[0])
213
214
        # query properties of all energy categories above
215
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
216
                              " FROM tbl_energy_categories "
217
                              " ORDER BY id ", )
218
        rows_energy_categories = cursor_system.fetchall()
219
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
220
            if cursor_system:
221
                cursor_system.close()
222
            if cnx_system:
223
                cnx_system.disconnect()
224
225
            if cursor_energy:
226
                cursor_energy.close()
227
            if cnx_energy:
228
                cnx_energy.disconnect()
229
230
            if cnx_historical:
231
                cnx_historical.close()
232
            if cursor_historical:
233
                cursor_historical.disconnect()
234
            raise falcon.HTTPError(falcon.HTTP_404,
235
                                   title='API.NOT_FOUND',
236
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
237
        energy_category_dict = dict()
238
        for row_energy_category in rows_energy_categories:
239
            if row_energy_category[0] in energy_category_set_input or \
240
                    row_energy_category[0] in energy_category_set_output:
241
                energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
242
                                                                "unit_of_measure": row_energy_category[2],
243
                                                                "kgce": row_energy_category[3],
244
                                                                "kgco2e": row_energy_category[4]}
245
246
        ################################################################################################################
247
        # Step 4: query associated points
248
        ################################################################################################################
249
        point_list = list()
250
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
251
                              " FROM tbl_equipments e, tbl_equipments_parameters ep, tbl_points p "
252
                              " WHERE e.id = %s AND e.id = ep.equipment_id AND ep.parameter_type = 'point' "
253
                              "       AND ep.point_id = p.id "
254
                              " ORDER BY p.id ", (equipment['id'],))
255
        rows_points = cursor_system.fetchall()
256
        if rows_points is not None and len(rows_points) > 0:
257
            for row in rows_points:
258
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
259
260
        ################################################################################################################
261
        # Step 5: query base period energy input
262
        ################################################################################################################
263
        base_input = dict()
264
        if energy_category_set_input is not None and len(energy_category_set_input) > 0:
265
            for energy_category_id in energy_category_set_input:
266
                base_input[energy_category_id] = dict()
267
                base_input[energy_category_id]['timestamps'] = list()
268
                base_input[energy_category_id]['values'] = list()
269
                base_input[energy_category_id]['subtotal'] = Decimal(0.0)
270
271
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
272
                                      " FROM tbl_equipment_input_category_hourly "
273
                                      " WHERE equipment_id = %s "
274
                                      "     AND energy_category_id = %s "
275
                                      "     AND start_datetime_utc >= %s "
276
                                      "     AND start_datetime_utc < %s "
277
                                      " ORDER BY start_datetime_utc ",
278
                                      (equipment['id'],
279
                                       energy_category_id,
280
                                       base_start_datetime_utc,
281
                                       base_end_datetime_utc))
282
                rows_equipment_hourly = cursor_energy.fetchall()
283
284
                rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly,
285
                                                                                        base_start_datetime_utc,
286
                                                                                        base_end_datetime_utc,
287
                                                                                        period_type)
288
                for row_equipment_periodically in rows_equipment_periodically:
289
                    current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
290
                                             timedelta(minutes=timezone_offset)
291
                    if period_type == 'hourly':
292
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
293
                    elif period_type == 'daily':
294
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
295
                    elif period_type == 'monthly':
296
                        current_datetime = current_datetime_local.strftime('%Y-%m')
297
                    elif period_type == 'yearly':
298
                        current_datetime = current_datetime_local.strftime('%Y')
299
300
                    actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
301
                        else row_equipment_periodically[1]
302
                    base_input[energy_category_id]['timestamps'].append(current_datetime)
303
                    base_input[energy_category_id]['values'].append(actual_value)
304
                    base_input[energy_category_id]['subtotal'] += actual_value
305
306
        ################################################################################################################
307
        # Step 6: query base period energy output
308
        ################################################################################################################
309
        base_output = dict()
310
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
311
            for energy_category_id in energy_category_set_output:
312
                base_output[energy_category_id] = dict()
313
                base_output[energy_category_id]['timestamps'] = list()
314
                base_output[energy_category_id]['values'] = list()
315
                base_output[energy_category_id]['subtotal'] = Decimal(0.0)
316
317
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
318
                                      " FROM tbl_equipment_output_category_hourly "
319
                                      " WHERE equipment_id = %s "
320
                                      "     AND energy_category_id = %s "
321
                                      "     AND start_datetime_utc >= %s "
322
                                      "     AND start_datetime_utc < %s "
323
                                      " ORDER BY start_datetime_utc ",
324
                                      (equipment['id'],
325
                                       energy_category_id,
326
                                       base_start_datetime_utc,
327
                                       base_end_datetime_utc))
328
                rows_equipment_hourly = cursor_energy.fetchall()
329
330
                rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly,
331
                                                                                        base_start_datetime_utc,
332
                                                                                        base_end_datetime_utc,
333
                                                                                        period_type)
334
                for row_equipment_periodically in rows_equipment_periodically:
335
                    current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
336
                                             timedelta(minutes=timezone_offset)
337
                    if period_type == 'hourly':
338
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
339
                    elif period_type == 'daily':
340
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
341
                    elif period_type == 'monthly':
342
                        current_datetime = current_datetime_local.strftime('%Y-%m')
343
                    elif period_type == 'yearly':
344
                        current_datetime = current_datetime_local.strftime('%Y')
345
346
                    actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
347
                        else row_equipment_periodically[1]
348
                    base_output[energy_category_id]['timestamps'].append(current_datetime)
349
                    base_output[energy_category_id]['values'].append(actual_value)
350
                    base_output[energy_category_id]['subtotal'] += actual_value
351
        ################################################################################################################
352
        # Step 7: query reporting period energy input
353
        ################################################################################################################
354
        reporting_input = dict()
355
        if energy_category_set_input is not None and len(energy_category_set_input) > 0:
356
            for energy_category_id in energy_category_set_input:
357
358
                reporting_input[energy_category_id] = dict()
359
                reporting_input[energy_category_id]['timestamps'] = list()
360
                reporting_input[energy_category_id]['values'] = list()
361
                reporting_input[energy_category_id]['subtotal'] = Decimal(0.0)
362
363
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
364
                                      " FROM tbl_equipment_input_category_hourly "
365
                                      " WHERE equipment_id = %s "
366
                                      "     AND energy_category_id = %s "
367
                                      "     AND start_datetime_utc >= %s "
368
                                      "     AND start_datetime_utc < %s "
369
                                      " ORDER BY start_datetime_utc ",
370
                                      (equipment['id'],
371
                                       energy_category_id,
372
                                       reporting_start_datetime_utc,
373
                                       reporting_end_datetime_utc))
374
                rows_equipment_hourly = cursor_energy.fetchall()
375
376
                rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly,
377
                                                                                        reporting_start_datetime_utc,
378
                                                                                        reporting_end_datetime_utc,
379
                                                                                        period_type)
380
                for row_equipment_periodically in rows_equipment_periodically:
381
                    current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
382
                                             timedelta(minutes=timezone_offset)
383
                    if period_type == 'hourly':
384
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
385
                    elif period_type == 'daily':
386
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
387
                    elif period_type == 'monthly':
388
                        current_datetime = current_datetime_local.strftime('%Y-%m')
389
                    elif period_type == 'yearly':
390
                        current_datetime = current_datetime_local.strftime('%Y')
391
392
                    actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
393
                        else row_equipment_periodically[1]
394
                    reporting_input[energy_category_id]['timestamps'].append(current_datetime)
395
                    reporting_input[energy_category_id]['values'].append(actual_value)
396
                    reporting_input[energy_category_id]['subtotal'] += actual_value
397
398
        ################################################################################################################
399
        # Step 8: query reporting period energy output
400
        ################################################################################################################
401
        reporting_output = dict()
402
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
403
            for energy_category_id in energy_category_set_output:
404
405
                reporting_output[energy_category_id] = dict()
406
                reporting_output[energy_category_id]['timestamps'] = list()
407
                reporting_output[energy_category_id]['values'] = list()
408
                reporting_output[energy_category_id]['subtotal'] = Decimal(0.0)
409
410
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
411
                                      " FROM tbl_equipment_output_category_hourly "
412
                                      " WHERE equipment_id = %s "
413
                                      "     AND energy_category_id = %s "
414
                                      "     AND start_datetime_utc >= %s "
415
                                      "     AND start_datetime_utc < %s "
416
                                      " ORDER BY start_datetime_utc ",
417
                                      (equipment['id'],
418
                                       energy_category_id,
419
                                       reporting_start_datetime_utc,
420
                                       reporting_end_datetime_utc))
421
                rows_equipment_hourly = cursor_energy.fetchall()
422
423
                rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly,
424
                                                                                        reporting_start_datetime_utc,
425
                                                                                        reporting_end_datetime_utc,
426
                                                                                        period_type)
427
                for row_equipment_periodically in rows_equipment_periodically:
428
                    current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
429
                                             timedelta(minutes=timezone_offset)
430
                    if period_type == 'hourly':
431
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
432
                    elif period_type == 'daily':
433
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
434
                    elif period_type == 'monthly':
435
                        current_datetime = current_datetime_local.strftime('%Y-%m')
436
                    elif period_type == 'yearly':
437
                        current_datetime = current_datetime_local.strftime('%Y')
438
439
                    actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
440
                        else row_equipment_periodically[1]
441
                    reporting_output[energy_category_id]['timestamps'].append(current_datetime)
442
                    reporting_output[energy_category_id]['values'].append(actual_value)
443
                    reporting_output[energy_category_id]['subtotal'] += actual_value
444
445
        ################################################################################################################
446
        # Step 9: query tariff data
447
        ################################################################################################################
448
        parameters_data = dict()
449
        parameters_data['names'] = list()
450
        parameters_data['timestamps'] = list()
451
        parameters_data['values'] = list()
452
        if energy_category_set_input is not None and len(energy_category_set_input) > 0:
453
            for energy_category_id in energy_category_set_input:
454
                energy_category_tariff_dict = utilities.get_energy_category_tariffs(equipment['cost_center_id'],
455
                                                                                    energy_category_id,
456
                                                                                    reporting_start_datetime_utc,
457
                                                                                    reporting_end_datetime_utc)
458
                tariff_timestamp_list = list()
459
                tariff_value_list = list()
460
                for k, v in energy_category_tariff_dict.items():
461
                    # convert k from utc to local
462
                    k = k + timedelta(minutes=timezone_offset)
463
                    tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
464
                    tariff_value_list.append(v)
465
466
                parameters_data['names'].append('TARIFF-' + energy_category_dict[energy_category_id]['name'])
467
                parameters_data['timestamps'].append(tariff_timestamp_list)
468
                parameters_data['values'].append(tariff_value_list)
469
470
        ################################################################################################################
471
        # Step 10: query associated sensors and points data
472
        ################################################################################################################
473
        for point in point_list:
474
            point_values = []
475
            point_timestamps = []
476
            if point['object_type'] == 'ANALOG_VALUE':
477
                query = (" SELECT utc_date_time, actual_value "
478
                         " FROM tbl_analog_value "
479
                         " WHERE point_id = %s "
480
                         "       AND utc_date_time BETWEEN %s AND %s "
481
                         " ORDER BY utc_date_time ")
482
                cursor_historical.execute(query, (point['id'],
483
                                                  reporting_start_datetime_utc,
484
                                                  reporting_end_datetime_utc))
485
                rows = cursor_historical.fetchall()
486
487
                if rows is not None and len(rows) > 0:
488
                    for row in rows:
489
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
490
                                                 timedelta(minutes=timezone_offset)
491
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
492
                        point_timestamps.append(current_datetime)
493
                        point_values.append(row[1])
494
495
            elif point['object_type'] == 'ENERGY_VALUE':
496
                query = (" SELECT utc_date_time, actual_value "
497
                         " FROM tbl_energy_value "
498
                         " WHERE point_id = %s "
499
                         "       AND utc_date_time BETWEEN %s AND %s "
500
                         " ORDER BY utc_date_time ")
501
                cursor_historical.execute(query, (point['id'],
502
                                                  reporting_start_datetime_utc,
503
                                                  reporting_end_datetime_utc))
504
                rows = cursor_historical.fetchall()
505
506
                if rows is not None and len(rows) > 0:
507
                    for row in rows:
508
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
509
                                                 timedelta(minutes=timezone_offset)
510
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
511
                        point_timestamps.append(current_datetime)
512
                        point_values.append(row[1])
513
            elif point['object_type'] == 'DIGITAL_VALUE':
514
                query = (" SELECT utc_date_time, actual_value "
515
                         " FROM tbl_digital_value "
516
                         " WHERE point_id = %s "
517
                         "       AND utc_date_time BETWEEN %s AND %s ")
518
                cursor_historical.execute(query, (point['id'],
519
                                                  reporting_start_datetime_utc,
520
                                                  reporting_end_datetime_utc))
521
                rows = cursor_historical.fetchall()
522
523
                if rows is not None and len(rows) > 0:
524
                    for row in rows:
525
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
526
                                                 timedelta(minutes=timezone_offset)
527
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
528
                        point_timestamps.append(current_datetime)
529
                        point_values.append(row[1])
530
531
            parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
532
            parameters_data['timestamps'].append(point_timestamps)
533
            parameters_data['values'].append(point_values)
534
535
        ################################################################################################################
536
        # Step 11: construct the report
537
        ################################################################################################################
538
        if cursor_system:
539
            cursor_system.close()
540
        if cnx_system:
541
            cnx_system.disconnect()
542
543
        if cursor_energy:
544
            cursor_energy.close()
545
        if cnx_energy:
546
            cnx_energy.disconnect()
547
548
        result = dict()
549
550
        result['equipment'] = dict()
551
        result['equipment']['name'] = equipment['name']
552
553
        result['base_period_input'] = dict()
554
        result['base_period_input']['names'] = list()
555
        result['base_period_input']['units'] = list()
556
        result['base_period_input']['timestamps'] = list()
557
        result['base_period_input']['values'] = list()
558
        result['base_period_input']['subtotals'] = list()
559
        if energy_category_set_input is not None and len(energy_category_set_input) > 0:
560
            for energy_category_id in energy_category_set_input:
561
                result['base_period_input']['names'].append(energy_category_dict[energy_category_id]['name'])
562
                result['base_period_input']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
563
                result['base_period_input']['timestamps'].append(base_input[energy_category_id]['timestamps'])
564
                result['base_period_input']['values'].append(base_input[energy_category_id]['values'])
565
                result['base_period_input']['subtotals'].append(base_input[energy_category_id]['subtotal'])
566
567
        result['base_period_output'] = dict()
568
        result['base_period_output']['names'] = list()
569
        result['base_period_output']['units'] = list()
570
        result['base_period_output']['timestamps'] = list()
571
        result['base_period_output']['values'] = list()
572
        result['base_period_output']['subtotals'] = list()
573
574
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
575
            for energy_category_id in energy_category_set_output:
576
                result['base_period_output']['names'].append(energy_category_dict[energy_category_id]['name'])
577
                result['base_period_output']['units'].append(
578
                    energy_category_dict[energy_category_id]['unit_of_measure'])
579
                result['base_period_output']['timestamps'].append(base_output[energy_category_id]['timestamps'])
580
                result['base_period_output']['values'].append(base_output[energy_category_id]['values'])
581
                result['base_period_output']['subtotals'].append(base_output[energy_category_id]['subtotal'])
582
583
        result['base_period_efficiency'] = dict()
584
        result['base_period_efficiency']['names'] = list()
585
        result['base_period_efficiency']['units'] = list()
586
        result['base_period_efficiency']['timestamps'] = list()
587
        result['base_period_efficiency']['values'] = list()
588
        result['base_period_efficiency']['cumulations'] = list()
589
590
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
591
            for energy_category_id_output in energy_category_set_output:
592
                for energy_category_id_input in energy_category_set_input:
593
                    result['base_period_efficiency']['names'].append(
594
                        energy_category_dict[energy_category_id_output]['name'] + '/' +
595
                        energy_category_dict[energy_category_id_input]['name'])
596
                    result['base_period_efficiency']['units'].append(
597
                        energy_category_dict[energy_category_id_output]['unit_of_measure'] + '/' +
598
                        energy_category_dict[energy_category_id_input]['unit_of_measure'])
599
                    result['base_period_efficiency']['timestamps'].append(
600
                        base_output[energy_category_id_output]['timestamps'])
601
                    efficiency_values = list()
602
                    for i in range(len(base_output[energy_category_id_output]['timestamps'])):
603
                        efficiency_values.append((base_output[energy_category_id_output]['values'][i] /
604
                                                  base_input[energy_category_id_input]['values'][i])
605
                                                 if base_input[energy_category_id_input]['values'][i] > Decimal(0.0)
606
                                                 else None)
607
                    result['base_period_efficiency']['values'].append(efficiency_values)
608
609
                    base_cumulation = (base_output[energy_category_id_output]['subtotal'] /
610
                                       base_input[energy_category_id_input]['subtotal']) if \
611
                        base_input[energy_category_id_input]['subtotal'] > Decimal(0.0) else None
612
                    result['base_period_efficiency']['cumulations'].append(base_cumulation)
613
614
        result['reporting_period_input'] = dict()
615
        result['reporting_period_input']['names'] = list()
616
        result['reporting_period_input']['energy_category_ids'] = list()
617
        result['reporting_period_input']['units'] = list()
618
        result['reporting_period_input']['timestamps'] = list()
619
        result['reporting_period_input']['values'] = list()
620
        result['reporting_period_input']['subtotals'] = list()
621
        result['reporting_period_input']['increment_rates'] = list()
622
623
        if energy_category_set_input is not None and len(energy_category_set_input) > 0:
624
            for energy_category_id in energy_category_set_input:
625
                result['reporting_period_input']['names'].append(energy_category_dict[energy_category_id]['name'])
626
                result['reporting_period_input']['energy_category_ids'].append(energy_category_id)
627
                result['reporting_period_input']['units'].append(
628
                    energy_category_dict[energy_category_id]['unit_of_measure'])
629
                result['reporting_period_input']['timestamps'].append(
630
                    reporting_input[energy_category_id]['timestamps'])
631
                result['reporting_period_input']['values'].append(
632
                    reporting_input[energy_category_id]['values'])
633
                result['reporting_period_input']['subtotals'].append(
634
                    reporting_input[energy_category_id]['subtotal'])
635
                result['reporting_period_input']['increment_rates'].append(
636
                    (reporting_input[energy_category_id]['subtotal'] -
637
                     base_input[energy_category_id]['subtotal']) /
638
                    base_input[energy_category_id]['subtotal']
639
                    if base_input[energy_category_id]['subtotal'] > 0.0 else None)
640
641
        result['reporting_period_output'] = dict()
642
        result['reporting_period_output']['names'] = list()
643
        result['reporting_period_output']['energy_category_ids'] = list()
644
        result['reporting_period_output']['units'] = list()
645
        result['reporting_period_output']['timestamps'] = list()
646
        result['reporting_period_output']['values'] = list()
647
        result['reporting_period_output']['subtotals'] = list()
648
        result['reporting_period_output']['increment_rates'] = list()
649
650
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
651
            for energy_category_id in energy_category_set_output:
652
                result['reporting_period_output']['names'].append(energy_category_dict[energy_category_id]['name'])
653
                result['reporting_period_output']['energy_category_ids'].append(energy_category_id)
654
                result['reporting_period_output']['units'].append(
655
                    energy_category_dict[energy_category_id]['unit_of_measure'])
656
                result['reporting_period_output']['timestamps'].append(
657
                    reporting_output[energy_category_id]['timestamps'])
658
                result['reporting_period_output']['values'].append(reporting_output[energy_category_id]['values'])
659
                result['reporting_period_output']['subtotals'].append(reporting_output[energy_category_id]['subtotal'])
660
                result['reporting_period_output']['increment_rates'].append(
661
                    (reporting_output[energy_category_id]['subtotal'] -
662
                     base_output[energy_category_id]['subtotal']) /
663
                    base_output[energy_category_id]['subtotal']
664
                    if base_output[energy_category_id]['subtotal'] > 0.0 else None)
665
666
        result['reporting_period_efficiency'] = dict()
667
        result['reporting_period_efficiency']['names'] = list()
668
        result['reporting_period_efficiency']['units'] = list()
669
        result['reporting_period_efficiency']['timestamps'] = list()
670
        result['reporting_period_efficiency']['values'] = list()
671
        result['reporting_period_efficiency']['cumulations'] = list()
672
        result['reporting_period_efficiency']['increment_rates'] = list()
673
674
        if energy_category_set_output is not None and len(energy_category_set_output) > 0:
675
            for energy_category_id_output in energy_category_set_output:
676
                for energy_category_id_input in energy_category_set_input:
677
                    result['reporting_period_efficiency']['names'].append(
678
                        energy_category_dict[energy_category_id_output]['name'] + '/' +
679
                        energy_category_dict[energy_category_id_input]['name'])
680
                    result['reporting_period_efficiency']['units'].append(
681
                        energy_category_dict[energy_category_id_output]['unit_of_measure'] + '/' +
682
                        energy_category_dict[energy_category_id_input]['unit_of_measure'])
683
                    result['reporting_period_efficiency']['timestamps'].append(
684
                        reporting_output[energy_category_id_output]['timestamps'])
685
                    efficiency_values = list()
686
                    for i in range(len(reporting_output[energy_category_id_output]['timestamps'])):
687
                        efficiency_values.append((reporting_output[energy_category_id_output]['values'][i] /
688
                                                 reporting_input[energy_category_id_input]['values'][i])
689
                                                 if reporting_input[energy_category_id_input]['values'][i] >
690
                                                 Decimal(0.0) else None)
691
                    result['reporting_period_efficiency']['values'].append(efficiency_values)
692
693
                    base_cumulation = (base_output[energy_category_id_output]['subtotal'] /
694
                                       base_input[energy_category_id_input]['subtotal']) if \
695
                        base_input[energy_category_id_input]['subtotal'] > Decimal(0.0) else None
696
697
                    reporting_cumulation = (reporting_output[energy_category_id_output]['subtotal'] /
698
                                            reporting_input[energy_category_id_input]['subtotal']) if \
699
                        reporting_input[energy_category_id_input]['subtotal'] > Decimal(0.0) else None
700
701
                    result['reporting_period_efficiency']['cumulations'].append(reporting_cumulation)
702
                    result['reporting_period_efficiency']['increment_rates'].append(
703
                        ((reporting_cumulation - base_cumulation) / base_cumulation if (base_cumulation is not None and
704
                                                                                        base_cumulation > Decimal(0.0))
705
                         else None)
706
                    )
707
708
        result['parameters'] = {
709
            "names": parameters_data['names'],
710
            "timestamps": parameters_data['timestamps'],
711
            "values": parameters_data['values']
712
        }
713
714
        resp.body = json.dumps(result)
715