Code Duplication    Length = 534-544 lines in 2 locations

reports/combinedequipmentenergycategory.py 1 location

@@ 10-553 (lines=544) @@
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 energy categories
24
    # Step 4: query associated points
25
    # Step 5: query base period energy input
26
    # Step 6: query reporting period energy input
27
    # Step 7: query tariff data
28
    # Step 8: query associated points data
29
    # Step 9: construct the report
30
    ####################################################################################################################
31
    @staticmethod
32
    def on_get(req, resp):
33
        print(req.params)
34
        combined_equipment_id = req.params.get('combinedequipmentid')
35
        period_type = req.params.get('periodtype')
36
        base_start_datetime_local = req.params.get('baseperiodstartdatetime')
37
        base_end_datetime_local = req.params.get('baseperiodenddatetime')
38
        reporting_start_datetime_local = req.params.get('reportingperiodstartdatetime')
39
        reporting_end_datetime_local = req.params.get('reportingperiodenddatetime')
40
41
        ################################################################################################################
42
        # Step 1: valid parameters
43
        ################################################################################################################
44
        if combined_equipment_id is None:
45
            raise falcon.HTTPError(falcon.HTTP_400,
46
                                   title='API.BAD_REQUEST',
47
                                   description='API.INVALID_COMBINED_EQUIPMENT_ID')
48
        else:
49
            combined_equipment_id = str.strip(combined_equipment_id)
50
            if not combined_equipment_id.isdigit() or int(combined_equipment_id) <= 0:
51
                raise falcon.HTTPError(falcon.HTTP_400,
52
                                       title='API.BAD_REQUEST',
53
                                       description='API.INVALID_COMBINED_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 combined 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_combined_equipments "
137
                              " WHERE id = %s ", (combined_equipment_id,))
138
        row_combined_equipment = cursor_system.fetchone()
139
        if row_combined_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,
155
                                   title='API.NOT_FOUND',
156
                                   description='API.COMBINED_EQUIPMENT_NOT_FOUND')
157
158
        combined_equipment = dict()
159
        combined_equipment['id'] = row_combined_equipment[0]
160
        combined_equipment['name'] = row_combined_equipment[1]
161
        combined_equipment['cost_center_id'] = row_combined_equipment[2]
162
163
        ################################################################################################################
164
        # Step 3: query energy categories
165
        ################################################################################################################
166
        energy_category_set = set()
167
        # query energy categories in base period
168
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
169
                              " FROM tbl_combined_equipment_input_category_hourly "
170
                              " WHERE combined_equipment_id = %s "
171
                              "     AND start_datetime_utc >= %s "
172
                              "     AND start_datetime_utc < %s ",
173
                              (combined_equipment['id'], base_start_datetime_utc, base_end_datetime_utc))
174
        rows_energy_categories = cursor_energy.fetchall()
175
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
176
            for row_energy_category in rows_energy_categories:
177
                energy_category_set.add(row_energy_category[0])
178
179
        # query energy categories in reporting period
180
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
181
                              " FROM tbl_combined_equipment_input_category_hourly "
182
                              " WHERE combined_equipment_id = %s "
183
                              "     AND start_datetime_utc >= %s "
184
                              "     AND start_datetime_utc < %s ",
185
                              (combined_equipment['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
186
        rows_energy_categories = cursor_energy.fetchall()
187
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
188
            for row_energy_category in rows_energy_categories:
189
                energy_category_set.add(row_energy_category[0])
190
191
        # query all energy categories in base period and reporting period
192
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
193
                              " FROM tbl_energy_categories "
194
                              " ORDER BY id ", )
195
        rows_energy_categories = cursor_system.fetchall()
196
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
197
            if cursor_system:
198
                cursor_system.close()
199
            if cnx_system:
200
                cnx_system.disconnect()
201
202
            if cursor_energy:
203
                cursor_energy.close()
204
            if cnx_energy:
205
                cnx_energy.disconnect()
206
207
            if cnx_historical:
208
                cnx_historical.close()
209
            if cursor_historical:
210
                cursor_historical.disconnect()
211
            raise falcon.HTTPError(falcon.HTTP_404,
212
                                   title='API.NOT_FOUND',
213
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
214
        energy_category_dict = dict()
215
        for row_energy_category in rows_energy_categories:
216
            if row_energy_category[0] in energy_category_set:
217
                energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
218
                                                                "unit_of_measure": row_energy_category[2],
219
                                                                "kgce": row_energy_category[3],
220
                                                                "kgco2e": row_energy_category[4]}
221
222
        ################################################################################################################
223
        # Step 4: query associated points
224
        ################################################################################################################
225
        point_list = list()
226
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
227
                              " FROM tbl_combined_equipments e, tbl_combined_equipments_parameters ep, tbl_points p "
228
                              " WHERE e.id = %s AND e.id = ep.combined_equipment_id AND ep.parameter_type = 'point' "
229
                              "       AND ep.point_id = p.id "
230
                              " ORDER BY p.id ", (combined_equipment['id'],))
231
        rows_points = cursor_system.fetchall()
232
        if rows_points is not None and len(rows_points) > 0:
233
            for row in rows_points:
234
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
235
236
        ################################################################################################################
237
        # Step 7: query base period energy input
238
        ################################################################################################################
239
        base = dict()
240
        if energy_category_set is not None and len(energy_category_set) > 0:
241
            for energy_category_id in energy_category_set:
242
                kgce = energy_category_dict[energy_category_id]['kgce']
243
                kgco2e = energy_category_dict[energy_category_id]['kgco2e']
244
245
                base[energy_category_id] = dict()
246
                base[energy_category_id]['timestamps'] = list()
247
                base[energy_category_id]['values'] = list()
248
                base[energy_category_id]['subtotal'] = Decimal(0.0)
249
                base[energy_category_id]['subtotal_in_kgce'] = Decimal(0.0)
250
                base[energy_category_id]['subtotal_in_kgco2e'] = Decimal(0.0)
251
252
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
253
                                      " FROM tbl_combined_equipment_input_category_hourly "
254
                                      " WHERE combined_equipment_id = %s "
255
                                      "     AND energy_category_id = %s "
256
                                      "     AND start_datetime_utc >= %s "
257
                                      "     AND start_datetime_utc < %s "
258
                                      " ORDER BY start_datetime_utc ",
259
                                      (combined_equipment['id'],
260
                                       energy_category_id,
261
                                       base_start_datetime_utc,
262
                                       base_end_datetime_utc))
263
                rows_combined_equipment_hourly = cursor_energy.fetchall()
264
265
                rows_combined_equipment_periodically = \
266
                    utilities.aggregate_hourly_data_by_period(rows_combined_equipment_hourly,
267
                                                              base_start_datetime_utc,
268
                                                              base_end_datetime_utc,
269
                                                              period_type)
270
                for row_combined_equipment_periodically in rows_combined_equipment_periodically:
271
                    current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
272
                                             timedelta(minutes=timezone_offset)
273
                    if period_type == 'hourly':
274
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
275
                    elif period_type == 'daily':
276
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
277
                    elif period_type == 'monthly':
278
                        current_datetime = current_datetime_local.strftime('%Y-%m')
279
                    elif period_type == 'yearly':
280
                        current_datetime = current_datetime_local.strftime('%Y')
281
282
                    actual_value = Decimal(0.0) if row_combined_equipment_periodically[1] is None \
283
                        else row_combined_equipment_periodically[1]
284
                    base[energy_category_id]['timestamps'].append(current_datetime)
285
                    base[energy_category_id]['values'].append(actual_value)
286
                    base[energy_category_id]['subtotal'] += actual_value
287
                    base[energy_category_id]['subtotal_in_kgce'] += actual_value * kgce
288
                    base[energy_category_id]['subtotal_in_kgco2e'] += actual_value * kgco2e
289
290
        ################################################################################################################
291
        # Step 5: query reporting period energy input
292
        ################################################################################################################
293
        reporting = dict()
294
        if energy_category_set is not None and len(energy_category_set) > 0:
295
            for energy_category_id in energy_category_set:
296
                kgce = energy_category_dict[energy_category_id]['kgce']
297
                kgco2e = energy_category_dict[energy_category_id]['kgco2e']
298
299
                reporting[energy_category_id] = dict()
300
                reporting[energy_category_id]['timestamps'] = list()
301
                reporting[energy_category_id]['values'] = list()
302
                reporting[energy_category_id]['subtotal'] = Decimal(0.0)
303
                reporting[energy_category_id]['subtotal_in_kgce'] = Decimal(0.0)
304
                reporting[energy_category_id]['subtotal_in_kgco2e'] = Decimal(0.0)
305
                reporting[energy_category_id]['toppeak'] = Decimal(0.0)
306
                reporting[energy_category_id]['onpeak'] = Decimal(0.0)
307
                reporting[energy_category_id]['midpeak'] = Decimal(0.0)
308
                reporting[energy_category_id]['offpeak'] = Decimal(0.0)
309
310
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
311
                                      " FROM tbl_combined_equipment_input_category_hourly "
312
                                      " WHERE combined_equipment_id = %s "
313
                                      "     AND energy_category_id = %s "
314
                                      "     AND start_datetime_utc >= %s "
315
                                      "     AND start_datetime_utc < %s "
316
                                      " ORDER BY start_datetime_utc ",
317
                                      (combined_equipment['id'],
318
                                       energy_category_id,
319
                                       reporting_start_datetime_utc,
320
                                       reporting_end_datetime_utc))
321
                rows_combined_equipment_hourly = cursor_energy.fetchall()
322
323
                rows_combined_equipment_periodically = \
324
                    utilities.aggregate_hourly_data_by_period(rows_combined_equipment_hourly,
325
                                                              reporting_start_datetime_utc,
326
                                                              reporting_end_datetime_utc,
327
                                                              period_type)
328
                for row_combined_equipment_periodically in rows_combined_equipment_periodically:
329
                    current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
330
                                             timedelta(minutes=timezone_offset)
331
                    if period_type == 'hourly':
332
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
333
                    elif period_type == 'daily':
334
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
335
                    elif period_type == 'monthly':
336
                        current_datetime = current_datetime_local.strftime('%Y-%m')
337
                    elif period_type == 'yearly':
338
                        current_datetime = current_datetime_local.strftime('%Y')
339
340
                    actual_value = Decimal(0.0) if row_combined_equipment_periodically[1] is None \
341
                        else row_combined_equipment_periodically[1]
342
                    reporting[energy_category_id]['timestamps'].append(current_datetime)
343
                    reporting[energy_category_id]['values'].append(actual_value)
344
                    reporting[energy_category_id]['subtotal'] += actual_value
345
                    reporting[energy_category_id]['subtotal_in_kgce'] += actual_value * kgce
346
                    reporting[energy_category_id]['subtotal_in_kgco2e'] += actual_value * kgco2e
347
348
                energy_category_tariff_dict = \
349
                    utilities.get_energy_category_peak_types(combined_equipment['cost_center_id'],
350
                                                             energy_category_id,
351
                                                             reporting_start_datetime_utc,
352
                                                             reporting_end_datetime_utc)
353
                for row in rows_combined_equipment_hourly:
354
                    peak_type = energy_category_tariff_dict.get(row[0], None)
355
                    if peak_type == 'toppeak':
356
                        reporting[energy_category_id]['toppeak'] += row[1]
357
                    elif peak_type == 'onpeak':
358
                        reporting[energy_category_id]['onpeak'] += row[1]
359
                    elif peak_type == 'midpeak':
360
                        reporting[energy_category_id]['midpeak'] += row[1]
361
                    elif peak_type == 'offpeak':
362
                        reporting[energy_category_id]['offpeak'] += row[1]
363
364
        ################################################################################################################
365
        # Step 6: query tariff data
366
        ################################################################################################################
367
        parameters_data = dict()
368
        parameters_data['names'] = list()
369
        parameters_data['timestamps'] = list()
370
        parameters_data['values'] = list()
371
        if energy_category_set is not None and len(energy_category_set) > 0:
372
            for energy_category_id in energy_category_set:
373
                energy_category_tariff_dict = \
374
                    utilities.get_energy_category_tariffs(combined_equipment['cost_center_id'],
375
                                                          energy_category_id,
376
                                                          reporting_start_datetime_utc,
377
                                                          reporting_end_datetime_utc)
378
                tariff_timestamp_list = list()
379
                tariff_value_list = list()
380
                for k, v in energy_category_tariff_dict.items():
381
                    # convert k from utc to local
382
                    k = k + timedelta(minutes=timezone_offset)
383
                    tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
384
                    tariff_value_list.append(v)
385
386
                parameters_data['names'].append('TARIFF-' + energy_category_dict[energy_category_id]['name'])
387
                parameters_data['timestamps'].append(tariff_timestamp_list)
388
                parameters_data['values'].append(tariff_value_list)
389
390
        ################################################################################################################
391
        # Step 7: query associated points data
392
        ################################################################################################################
393
        for point in point_list:
394
            point_values = []
395
            point_timestamps = []
396
            if point['object_type'] == 'ANALOG_VALUE':
397
                query = (" SELECT utc_date_time, actual_value "
398
                         " FROM tbl_analog_value "
399
                         " WHERE point_id = %s "
400
                         "       AND utc_date_time BETWEEN %s AND %s "
401
                         " ORDER BY utc_date_time ")
402
                cursor_historical.execute(query, (point['id'],
403
                                                  reporting_start_datetime_utc,
404
                                                  reporting_end_datetime_utc))
405
                rows = cursor_historical.fetchall()
406
407
                if rows is not None and len(rows) > 0:
408
                    for row in rows:
409
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
410
                                                 timedelta(minutes=timezone_offset)
411
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
412
                        point_timestamps.append(current_datetime)
413
                        point_values.append(row[1])
414
415
            elif point['object_type'] == 'ENERGY_VALUE':
416
                query = (" SELECT utc_date_time, actual_value "
417
                         " FROM tbl_energy_value "
418
                         " WHERE point_id = %s "
419
                         "       AND utc_date_time BETWEEN %s AND %s "
420
                         " ORDER BY utc_date_time ")
421
                cursor_historical.execute(query, (point['id'],
422
                                                  reporting_start_datetime_utc,
423
                                                  reporting_end_datetime_utc))
424
                rows = cursor_historical.fetchall()
425
426
                if rows is not None and len(rows) > 0:
427
                    for row in rows:
428
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
429
                                                 timedelta(minutes=timezone_offset)
430
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
431
                        point_timestamps.append(current_datetime)
432
                        point_values.append(row[1])
433
            elif point['object_type'] == 'DIGITAL_VALUE':
434
                query = (" SELECT utc_date_time, actual_value "
435
                         " FROM tbl_digital_value "
436
                         " WHERE point_id = %s "
437
                         "       AND utc_date_time BETWEEN %s AND %s ")
438
                cursor_historical.execute(query, (point['id'],
439
                                                  reporting_start_datetime_utc,
440
                                                  reporting_end_datetime_utc))
441
                rows = cursor_historical.fetchall()
442
443
                if rows is not None and len(rows) > 0:
444
                    for row in rows:
445
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
446
                                                 timedelta(minutes=timezone_offset)
447
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
448
                        point_timestamps.append(current_datetime)
449
                        point_values.append(row[1])
450
451
            parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
452
            parameters_data['timestamps'].append(point_timestamps)
453
            parameters_data['values'].append(point_values)
454
455
        ################################################################################################################
456
        # Step 8: construct the report
457
        ################################################################################################################
458
        if cursor_system:
459
            cursor_system.close()
460
        if cnx_system:
461
            cnx_system.disconnect()
462
463
        if cursor_energy:
464
            cursor_energy.close()
465
        if cnx_energy:
466
            cnx_energy.disconnect()
467
468
        result = dict()
469
470
        result['combined_equipment'] = dict()
471
        result['combined_equipment']['name'] = combined_equipment['name']
472
473
        result['base_period'] = dict()
474
        result['base_period']['names'] = list()
475
        result['base_period']['units'] = list()
476
        result['base_period']['timestamps'] = list()
477
        result['base_period']['values'] = list()
478
        result['base_period']['subtotals'] = list()
479
        result['base_period']['subtotals_in_kgce'] = list()
480
        result['base_period']['subtotals_in_kgco2e'] = list()
481
        result['base_period']['total_in_kgce'] = Decimal(0.0)
482
        result['base_period']['total_in_kgco2e'] = Decimal(0.0)
483
        if energy_category_set is not None and len(energy_category_set) > 0:
484
            for energy_category_id in energy_category_set:
485
                result['base_period']['names'].append(energy_category_dict[energy_category_id]['name'])
486
                result['base_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
487
                result['base_period']['timestamps'].append(base[energy_category_id]['timestamps'])
488
                result['base_period']['values'].append(base[energy_category_id]['values'])
489
                result['base_period']['subtotals'].append(base[energy_category_id]['subtotal'])
490
                result['base_period']['subtotals_in_kgce'].append(base[energy_category_id]['subtotal_in_kgce'])
491
                result['base_period']['subtotals_in_kgco2e'].append(base[energy_category_id]['subtotal_in_kgco2e'])
492
                result['base_period']['total_in_kgce'] += base[energy_category_id]['subtotal_in_kgce']
493
                result['base_period']['total_in_kgco2e'] += base[energy_category_id]['subtotal_in_kgco2e']
494
495
        result['reporting_period'] = dict()
496
        result['reporting_period']['names'] = list()
497
        result['reporting_period']['energy_category_ids'] = list()
498
        result['reporting_period']['units'] = list()
499
        result['reporting_period']['timestamps'] = list()
500
        result['reporting_period']['values'] = list()
501
        result['reporting_period']['subtotals'] = list()
502
        result['reporting_period']['subtotals_in_kgce'] = list()
503
        result['reporting_period']['subtotals_in_kgco2e'] = list()
504
        result['reporting_period']['toppeaks'] = list()
505
        result['reporting_period']['onpeaks'] = list()
506
        result['reporting_period']['midpeaks'] = list()
507
        result['reporting_period']['offpeaks'] = list()
508
        result['reporting_period']['increment_rates'] = list()
509
        result['reporting_period']['total_in_kgce'] = Decimal(0.0)
510
        result['reporting_period']['total_in_kgco2e'] = Decimal(0.0)
511
        result['reporting_period']['increment_rate_in_kgce'] = Decimal(0.0)
512
        result['reporting_period']['increment_rate_in_kgco2e'] = Decimal(0.0)
513
514
        if energy_category_set is not None and len(energy_category_set) > 0:
515
            for energy_category_id in energy_category_set:
516
                result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name'])
517
                result['reporting_period']['energy_category_ids'].append(energy_category_id)
518
                result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
519
                result['reporting_period']['timestamps'].append(reporting[energy_category_id]['timestamps'])
520
                result['reporting_period']['values'].append(reporting[energy_category_id]['values'])
521
                result['reporting_period']['subtotals'].append(reporting[energy_category_id]['subtotal'])
522
                result['reporting_period']['subtotals_in_kgce'].append(
523
                    reporting[energy_category_id]['subtotal_in_kgce'])
524
                result['reporting_period']['subtotals_in_kgco2e'].append(
525
                    reporting[energy_category_id]['subtotal_in_kgco2e'])
526
                result['reporting_period']['toppeaks'].append(reporting[energy_category_id]['toppeak'])
527
                result['reporting_period']['onpeaks'].append(reporting[energy_category_id]['onpeak'])
528
                result['reporting_period']['midpeaks'].append(reporting[energy_category_id]['midpeak'])
529
                result['reporting_period']['offpeaks'].append(reporting[energy_category_id]['offpeak'])
530
                result['reporting_period']['increment_rates'].append(
531
                    (reporting[energy_category_id]['subtotal'] - base[energy_category_id]['subtotal']) /
532
                    base[energy_category_id]['subtotal']
533
                    if base[energy_category_id]['subtotal'] > 0.0 else None)
534
                result['reporting_period']['total_in_kgce'] += reporting[energy_category_id]['subtotal_in_kgce']
535
                result['reporting_period']['total_in_kgco2e'] += reporting[energy_category_id]['subtotal_in_kgco2e']
536
537
        result['reporting_period']['increment_rate_in_kgce'] = \
538
            (result['reporting_period']['total_in_kgce'] - result['base_period']['total_in_kgce']) / \
539
            result['base_period']['total_in_kgce'] \
540
            if result['base_period']['total_in_kgce'] > Decimal(0.0) else None
541
542
        result['reporting_period']['increment_rate_in_kgco2e'] = \
543
            (result['reporting_period']['total_in_kgco2e'] - result['base_period']['total_in_kgco2e']) / \
544
            result['base_period']['total_in_kgco2e'] \
545
            if result['base_period']['total_in_kgco2e'] > Decimal(0.0) else None
546
547
        result['parameters'] = {
548
            "names": parameters_data['names'],
549
            "timestamps": parameters_data['timestamps'],
550
            "values": parameters_data['values']
551
        }
552
553
        resp.body = json.dumps(result)
554

reports/equipmentenergycategory.py 1 location

@@ 10-543 (lines=534) @@
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 points
25
    # Step 5: query base period energy input
26
    # Step 6: query reporting period energy input
27
    # Step 7: query tariff data
28
    # Step 8: query associated points data
29
    # Step 9: construct the report
30
    ####################################################################################################################
31
    @staticmethod
32
    def on_get(req, resp):
33
        print(req.params)
34
        equipment_id = req.params.get('equipmentid')
35
        period_type = req.params.get('periodtype')
36
        base_start_datetime_local = req.params.get('baseperiodstartdatetime')
37
        base_end_datetime_local = req.params.get('baseperiodenddatetime')
38
        reporting_start_datetime_local = req.params.get('reportingperiodstartdatetime')
39
        reporting_end_datetime_local = req.params.get('reportingperiodenddatetime')
40
41
        ################################################################################################################
42
        # Step 1: valid parameters
43
        ################################################################################################################
44
        if equipment_id is None:
45
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_EQUIPMENT_ID')
46
        else:
47
            equipment_id = str.strip(equipment_id)
48
            if not equipment_id.isdigit() or int(equipment_id) <= 0:
49
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_EQUIPMENT_ID')
50
51
        if period_type is None:
52
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
53
        else:
54
            period_type = str.strip(period_type)
55
            if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
56
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
57
58
        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
59
        if config.utc_offset[0] == '-':
60
            timezone_offset = -timezone_offset
61
62
        base_start_datetime_utc = None
63
        if base_start_datetime_local is not None and len(str.strip(base_start_datetime_local)) > 0:
64
            base_start_datetime_local = str.strip(base_start_datetime_local)
65
            try:
66
                base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
67
                                                            '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
68
                    timedelta(minutes=timezone_offset)
69
            except ValueError:
70
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
71
                                       description="API.INVALID_BASE_PERIOD_START_DATETIME")
72
73
        base_end_datetime_utc = None
74
        if base_end_datetime_local is not None and len(str.strip(base_end_datetime_local)) > 0:
75
            base_end_datetime_local = str.strip(base_end_datetime_local)
76
            try:
77
                base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
78
                                                          '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
79
                    timedelta(minutes=timezone_offset)
80
            except ValueError:
81
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
82
                                       description="API.INVALID_BASE_PERIOD_END_DATETIME")
83
84
        if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
85
                base_start_datetime_utc >= base_end_datetime_utc:
86
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
87
                                   description='API.INVALID_BASE_PERIOD_END_DATETIME')
88
89
        if reporting_start_datetime_local is None:
90
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
91
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
92
        else:
93
            reporting_start_datetime_local = str.strip(reporting_start_datetime_local)
94
            try:
95
                reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
96
                                                                 '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
97
                    timedelta(minutes=timezone_offset)
98
            except ValueError:
99
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
100
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
101
102
        if reporting_end_datetime_local is None:
103
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
104
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
105
        else:
106
            reporting_end_datetime_local = str.strip(reporting_end_datetime_local)
107
            try:
108
                reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
109
                                                               '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
110
                    timedelta(minutes=timezone_offset)
111
            except ValueError:
112
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
113
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
114
115
        if reporting_start_datetime_utc >= reporting_end_datetime_utc:
116
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
117
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
118
119
        ################################################################################################################
120
        # Step 2: query the equipment
121
        ################################################################################################################
122
        cnx_system = mysql.connector.connect(**config.myems_system_db)
123
        cursor_system = cnx_system.cursor()
124
125
        cnx_energy = mysql.connector.connect(**config.myems_energy_db)
126
        cursor_energy = cnx_energy.cursor()
127
128
        cnx_historical = mysql.connector.connect(**config.myems_historical_db)
129
        cursor_historical = cnx_historical.cursor()
130
131
        cursor_system.execute(" SELECT id, name, cost_center_id "
132
                              " FROM tbl_equipments "
133
                              " WHERE id = %s ", (equipment_id,))
134
        row_equipment = cursor_system.fetchone()
135
        if row_equipment is None:
136
            if cursor_system:
137
                cursor_system.close()
138
            if cnx_system:
139
                cnx_system.disconnect()
140
141
            if cursor_energy:
142
                cursor_energy.close()
143
            if cnx_energy:
144
                cnx_energy.disconnect()
145
146
            if cnx_historical:
147
                cnx_historical.close()
148
            if cursor_historical:
149
                cursor_historical.disconnect()
150
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.EQUIPMENT_NOT_FOUND')
151
152
        equipment = dict()
153
        equipment['id'] = row_equipment[0]
154
        equipment['name'] = row_equipment[1]
155
        equipment['cost_center_id'] = row_equipment[2]
156
157
        ################################################################################################################
158
        # Step 3: query energy categories
159
        ################################################################################################################
160
        energy_category_set = set()
161
        # query energy categories in base period
162
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
163
                              " FROM tbl_equipment_input_category_hourly "
164
                              " WHERE equipment_id = %s "
165
                              "     AND start_datetime_utc >= %s "
166
                              "     AND start_datetime_utc < %s ",
167
                              (equipment['id'], base_start_datetime_utc, base_end_datetime_utc))
168
        rows_energy_categories = cursor_energy.fetchall()
169
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
170
            for row_energy_category in rows_energy_categories:
171
                energy_category_set.add(row_energy_category[0])
172
173
        # query energy categories in reporting period
174
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
175
                              " FROM tbl_equipment_input_category_hourly "
176
                              " WHERE equipment_id = %s "
177
                              "     AND start_datetime_utc >= %s "
178
                              "     AND start_datetime_utc < %s ",
179
                              (equipment['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
180
        rows_energy_categories = cursor_energy.fetchall()
181
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
182
            for row_energy_category in rows_energy_categories:
183
                energy_category_set.add(row_energy_category[0])
184
185
        # query all energy categories in base period and reporting period
186
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
187
                              " FROM tbl_energy_categories "
188
                              " ORDER BY id ", )
189
        rows_energy_categories = cursor_system.fetchall()
190
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
191
            if cursor_system:
192
                cursor_system.close()
193
            if cnx_system:
194
                cnx_system.disconnect()
195
196
            if cursor_energy:
197
                cursor_energy.close()
198
            if cnx_energy:
199
                cnx_energy.disconnect()
200
201
            if cnx_historical:
202
                cnx_historical.close()
203
            if cursor_historical:
204
                cursor_historical.disconnect()
205
            raise falcon.HTTPError(falcon.HTTP_404,
206
                                   title='API.NOT_FOUND',
207
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
208
        energy_category_dict = dict()
209
        for row_energy_category in rows_energy_categories:
210
            if row_energy_category[0] in energy_category_set:
211
                energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
212
                                                                "unit_of_measure": row_energy_category[2],
213
                                                                "kgce": row_energy_category[3],
214
                                                                "kgco2e": row_energy_category[4]}
215
216
        ################################################################################################################
217
        # Step 4: query associated points
218
        ################################################################################################################
219
        point_list = list()
220
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
221
                              " FROM tbl_equipments e, tbl_equipments_parameters ep, tbl_points p "
222
                              " WHERE e.id = %s AND e.id = ep.equipment_id AND ep.parameter_type = 'point' "
223
                              "       AND ep.point_id = p.id "
224
                              " ORDER BY p.id ", (equipment['id'],))
225
        rows_points = cursor_system.fetchall()
226
        if rows_points is not None and len(rows_points) > 0:
227
            for row in rows_points:
228
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
229
230
        ################################################################################################################
231
        # Step 7: query base period energy input
232
        ################################################################################################################
233
        base = dict()
234
        if energy_category_set is not None and len(energy_category_set) > 0:
235
            for energy_category_id in energy_category_set:
236
                kgce = energy_category_dict[energy_category_id]['kgce']
237
                kgco2e = energy_category_dict[energy_category_id]['kgco2e']
238
239
                base[energy_category_id] = dict()
240
                base[energy_category_id]['timestamps'] = list()
241
                base[energy_category_id]['values'] = list()
242
                base[energy_category_id]['subtotal'] = Decimal(0.0)
243
                base[energy_category_id]['subtotal_in_kgce'] = Decimal(0.0)
244
                base[energy_category_id]['subtotal_in_kgco2e'] = Decimal(0.0)
245
246
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
247
                                      " FROM tbl_equipment_input_category_hourly "
248
                                      " WHERE equipment_id = %s "
249
                                      "     AND energy_category_id = %s "
250
                                      "     AND start_datetime_utc >= %s "
251
                                      "     AND start_datetime_utc < %s "
252
                                      " ORDER BY start_datetime_utc ",
253
                                      (equipment['id'],
254
                                       energy_category_id,
255
                                       base_start_datetime_utc,
256
                                       base_end_datetime_utc))
257
                rows_equipment_hourly = cursor_energy.fetchall()
258
259
                rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly,
260
                                                                                        base_start_datetime_utc,
261
                                                                                        base_end_datetime_utc,
262
                                                                                        period_type)
263
                for row_equipment_periodically in rows_equipment_periodically:
264
                    current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
265
                                             timedelta(minutes=timezone_offset)
266
                    if period_type == 'hourly':
267
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
268
                    elif period_type == 'daily':
269
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
270
                    elif period_type == 'monthly':
271
                        current_datetime = current_datetime_local.strftime('%Y-%m')
272
                    elif period_type == 'yearly':
273
                        current_datetime = current_datetime_local.strftime('%Y')
274
275
                    actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
276
                        else row_equipment_periodically[1]
277
                    base[energy_category_id]['timestamps'].append(current_datetime)
278
                    base[energy_category_id]['values'].append(actual_value)
279
                    base[energy_category_id]['subtotal'] += actual_value
280
                    base[energy_category_id]['subtotal_in_kgce'] += actual_value * kgce
281
                    base[energy_category_id]['subtotal_in_kgco2e'] += actual_value * kgco2e
282
283
        ################################################################################################################
284
        # Step 5: query reporting period energy input
285
        ################################################################################################################
286
        reporting = dict()
287
        if energy_category_set is not None and len(energy_category_set) > 0:
288
            for energy_category_id in energy_category_set:
289
                kgce = energy_category_dict[energy_category_id]['kgce']
290
                kgco2e = energy_category_dict[energy_category_id]['kgco2e']
291
292
                reporting[energy_category_id] = dict()
293
                reporting[energy_category_id]['timestamps'] = list()
294
                reporting[energy_category_id]['values'] = list()
295
                reporting[energy_category_id]['subtotal'] = Decimal(0.0)
296
                reporting[energy_category_id]['subtotal_in_kgce'] = Decimal(0.0)
297
                reporting[energy_category_id]['subtotal_in_kgco2e'] = Decimal(0.0)
298
                reporting[energy_category_id]['toppeak'] = Decimal(0.0)
299
                reporting[energy_category_id]['onpeak'] = Decimal(0.0)
300
                reporting[energy_category_id]['midpeak'] = Decimal(0.0)
301
                reporting[energy_category_id]['offpeak'] = Decimal(0.0)
302
303
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
304
                                      " FROM tbl_equipment_input_category_hourly "
305
                                      " WHERE equipment_id = %s "
306
                                      "     AND energy_category_id = %s "
307
                                      "     AND start_datetime_utc >= %s "
308
                                      "     AND start_datetime_utc < %s "
309
                                      " ORDER BY start_datetime_utc ",
310
                                      (equipment['id'],
311
                                       energy_category_id,
312
                                       reporting_start_datetime_utc,
313
                                       reporting_end_datetime_utc))
314
                rows_equipment_hourly = cursor_energy.fetchall()
315
316
                rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly,
317
                                                                                        reporting_start_datetime_utc,
318
                                                                                        reporting_end_datetime_utc,
319
                                                                                        period_type)
320
                for row_equipment_periodically in rows_equipment_periodically:
321
                    current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
322
                                             timedelta(minutes=timezone_offset)
323
                    if period_type == 'hourly':
324
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
325
                    elif period_type == 'daily':
326
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
327
                    elif period_type == 'monthly':
328
                        current_datetime = current_datetime_local.strftime('%Y-%m')
329
                    elif period_type == 'yearly':
330
                        current_datetime = current_datetime_local.strftime('%Y')
331
332
                    actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
333
                        else row_equipment_periodically[1]
334
                    reporting[energy_category_id]['timestamps'].append(current_datetime)
335
                    reporting[energy_category_id]['values'].append(actual_value)
336
                    reporting[energy_category_id]['subtotal'] += actual_value
337
                    reporting[energy_category_id]['subtotal_in_kgce'] += actual_value * kgce
338
                    reporting[energy_category_id]['subtotal_in_kgco2e'] += actual_value * kgco2e
339
340
                energy_category_tariff_dict = utilities.get_energy_category_peak_types(equipment['cost_center_id'],
341
                                                                                       energy_category_id,
342
                                                                                       reporting_start_datetime_utc,
343
                                                                                       reporting_end_datetime_utc)
344
                for row in rows_equipment_hourly:
345
                    peak_type = energy_category_tariff_dict.get(row[0], None)
346
                    if peak_type == 'toppeak':
347
                        reporting[energy_category_id]['toppeak'] += row[1]
348
                    elif peak_type == 'onpeak':
349
                        reporting[energy_category_id]['onpeak'] += row[1]
350
                    elif peak_type == 'midpeak':
351
                        reporting[energy_category_id]['midpeak'] += row[1]
352
                    elif peak_type == 'offpeak':
353
                        reporting[energy_category_id]['offpeak'] += row[1]
354
355
        ################################################################################################################
356
        # Step 6: query tariff data
357
        ################################################################################################################
358
        parameters_data = dict()
359
        parameters_data['names'] = list()
360
        parameters_data['timestamps'] = list()
361
        parameters_data['values'] = list()
362
        if energy_category_set is not None and len(energy_category_set) > 0:
363
            for energy_category_id in energy_category_set:
364
                energy_category_tariff_dict = utilities.get_energy_category_tariffs(equipment['cost_center_id'],
365
                                                                                    energy_category_id,
366
                                                                                    reporting_start_datetime_utc,
367
                                                                                    reporting_end_datetime_utc)
368
                tariff_timestamp_list = list()
369
                tariff_value_list = list()
370
                for k, v in energy_category_tariff_dict.items():
371
                    # convert k from utc to local
372
                    k = k + timedelta(minutes=timezone_offset)
373
                    tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
374
                    tariff_value_list.append(v)
375
376
                parameters_data['names'].append('TARIFF-' + energy_category_dict[energy_category_id]['name'])
377
                parameters_data['timestamps'].append(tariff_timestamp_list)
378
                parameters_data['values'].append(tariff_value_list)
379
380
        ################################################################################################################
381
        # Step 7: query associated points data
382
        ################################################################################################################
383
        for point in point_list:
384
            point_values = []
385
            point_timestamps = []
386
            if point['object_type'] == 'ANALOG_VALUE':
387
                query = (" SELECT utc_date_time, actual_value "
388
                         " FROM tbl_analog_value "
389
                         " WHERE point_id = %s "
390
                         "       AND utc_date_time BETWEEN %s AND %s "
391
                         " ORDER BY utc_date_time ")
392
                cursor_historical.execute(query, (point['id'],
393
                                                  reporting_start_datetime_utc,
394
                                                  reporting_end_datetime_utc))
395
                rows = cursor_historical.fetchall()
396
397
                if rows is not None and len(rows) > 0:
398
                    for row in rows:
399
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
400
                                                 timedelta(minutes=timezone_offset)
401
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
402
                        point_timestamps.append(current_datetime)
403
                        point_values.append(row[1])
404
405
            elif point['object_type'] == 'ENERGY_VALUE':
406
                query = (" SELECT utc_date_time, actual_value "
407
                         " FROM tbl_energy_value "
408
                         " WHERE point_id = %s "
409
                         "       AND utc_date_time BETWEEN %s AND %s "
410
                         " ORDER BY utc_date_time ")
411
                cursor_historical.execute(query, (point['id'],
412
                                                  reporting_start_datetime_utc,
413
                                                  reporting_end_datetime_utc))
414
                rows = cursor_historical.fetchall()
415
416
                if rows is not None and len(rows) > 0:
417
                    for row in rows:
418
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
419
                                                 timedelta(minutes=timezone_offset)
420
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
421
                        point_timestamps.append(current_datetime)
422
                        point_values.append(row[1])
423
            elif point['object_type'] == 'DIGITAL_VALUE':
424
                query = (" SELECT utc_date_time, actual_value "
425
                         " FROM tbl_digital_value "
426
                         " WHERE point_id = %s "
427
                         "       AND utc_date_time BETWEEN %s AND %s ")
428
                cursor_historical.execute(query, (point['id'],
429
                                                  reporting_start_datetime_utc,
430
                                                  reporting_end_datetime_utc))
431
                rows = cursor_historical.fetchall()
432
433
                if rows is not None and len(rows) > 0:
434
                    for row in rows:
435
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
436
                                                 timedelta(minutes=timezone_offset)
437
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
438
                        point_timestamps.append(current_datetime)
439
                        point_values.append(row[1])
440
441
            parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
442
            parameters_data['timestamps'].append(point_timestamps)
443
            parameters_data['values'].append(point_values)
444
445
        ################################################################################################################
446
        # Step 8: construct the report
447
        ################################################################################################################
448
        if cursor_system:
449
            cursor_system.close()
450
        if cnx_system:
451
            cnx_system.disconnect()
452
453
        if cursor_energy:
454
            cursor_energy.close()
455
        if cnx_energy:
456
            cnx_energy.disconnect()
457
458
        result = dict()
459
460
        result['equipment'] = dict()
461
        result['equipment']['name'] = equipment['name']
462
463
        result['base_period'] = dict()
464
        result['base_period']['names'] = list()
465
        result['base_period']['units'] = list()
466
        result['base_period']['timestamps'] = list()
467
        result['base_period']['values'] = list()
468
        result['base_period']['subtotals'] = list()
469
        result['base_period']['subtotals_in_kgce'] = list()
470
        result['base_period']['subtotals_in_kgco2e'] = list()
471
        result['base_period']['total_in_kgce'] = Decimal(0.0)
472
        result['base_period']['total_in_kgco2e'] = Decimal(0.0)
473
        if energy_category_set is not None and len(energy_category_set) > 0:
474
            for energy_category_id in energy_category_set:
475
                result['base_period']['names'].append(energy_category_dict[energy_category_id]['name'])
476
                result['base_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
477
                result['base_period']['timestamps'].append(base[energy_category_id]['timestamps'])
478
                result['base_period']['values'].append(base[energy_category_id]['values'])
479
                result['base_period']['subtotals'].append(base[energy_category_id]['subtotal'])
480
                result['base_period']['subtotals_in_kgce'].append(base[energy_category_id]['subtotal_in_kgce'])
481
                result['base_period']['subtotals_in_kgco2e'].append(base[energy_category_id]['subtotal_in_kgco2e'])
482
                result['base_period']['total_in_kgce'] += base[energy_category_id]['subtotal_in_kgce']
483
                result['base_period']['total_in_kgco2e'] += base[energy_category_id]['subtotal_in_kgco2e']
484
485
        result['reporting_period'] = dict()
486
        result['reporting_period']['names'] = list()
487
        result['reporting_period']['energy_category_ids'] = list()
488
        result['reporting_period']['units'] = list()
489
        result['reporting_period']['timestamps'] = list()
490
        result['reporting_period']['values'] = list()
491
        result['reporting_period']['subtotals'] = list()
492
        result['reporting_period']['subtotals_in_kgce'] = list()
493
        result['reporting_period']['subtotals_in_kgco2e'] = list()
494
        result['reporting_period']['toppeaks'] = list()
495
        result['reporting_period']['onpeaks'] = list()
496
        result['reporting_period']['midpeaks'] = list()
497
        result['reporting_period']['offpeaks'] = list()
498
        result['reporting_period']['increment_rates'] = list()
499
        result['reporting_period']['total_in_kgce'] = Decimal(0.0)
500
        result['reporting_period']['total_in_kgco2e'] = Decimal(0.0)
501
        result['reporting_period']['increment_rate_in_kgce'] = Decimal(0.0)
502
        result['reporting_period']['increment_rate_in_kgco2e'] = Decimal(0.0)
503
504
        if energy_category_set is not None and len(energy_category_set) > 0:
505
            for energy_category_id in energy_category_set:
506
                result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name'])
507
                result['reporting_period']['energy_category_ids'].append(energy_category_id)
508
                result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
509
                result['reporting_period']['timestamps'].append(reporting[energy_category_id]['timestamps'])
510
                result['reporting_period']['values'].append(reporting[energy_category_id]['values'])
511
                result['reporting_period']['subtotals'].append(reporting[energy_category_id]['subtotal'])
512
                result['reporting_period']['subtotals_in_kgce'].append(
513
                    reporting[energy_category_id]['subtotal_in_kgce'])
514
                result['reporting_period']['subtotals_in_kgco2e'].append(
515
                    reporting[energy_category_id]['subtotal_in_kgco2e'])
516
                result['reporting_period']['toppeaks'].append(reporting[energy_category_id]['toppeak'])
517
                result['reporting_period']['onpeaks'].append(reporting[energy_category_id]['onpeak'])
518
                result['reporting_period']['midpeaks'].append(reporting[energy_category_id]['midpeak'])
519
                result['reporting_period']['offpeaks'].append(reporting[energy_category_id]['offpeak'])
520
                result['reporting_period']['increment_rates'].append(
521
                    (reporting[energy_category_id]['subtotal'] - base[energy_category_id]['subtotal']) /
522
                    base[energy_category_id]['subtotal']
523
                    if base[energy_category_id]['subtotal'] > 0.0 else None)
524
                result['reporting_period']['total_in_kgce'] += reporting[energy_category_id]['subtotal_in_kgce']
525
                result['reporting_period']['total_in_kgco2e'] += reporting[energy_category_id]['subtotal_in_kgco2e']
526
527
        result['reporting_period']['increment_rate_in_kgce'] = \
528
            (result['reporting_period']['total_in_kgce'] - result['base_period']['total_in_kgce']) / \
529
            result['base_period']['total_in_kgce'] \
530
            if result['base_period']['total_in_kgce'] > Decimal(0.0) else None
531
532
        result['reporting_period']['increment_rate_in_kgco2e'] = \
533
            (result['reporting_period']['total_in_kgco2e'] - result['base_period']['total_in_kgco2e']) / \
534
            result['base_period']['total_in_kgco2e'] \
535
            if result['base_period']['total_in_kgco2e'] > Decimal(0.0) else None
536
537
        result['parameters'] = {
538
            "names": parameters_data['names'],
539
            "timestamps": parameters_data['timestamps'],
540
            "values": parameters_data['values']
541
        }
542
543
        resp.body = json.dumps(result)
544