Code Duplication    Length = 649-660 lines in 2 locations

reports/combinedequipmentsaving.py 1 location

@@ 10-669 (lines=660) @@
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 saving
26
    # Step 6: query reporting period energy saving
27
    # Step 7: query tariff data
28
    # Step 8: query associated points data
29
    # Step 10: 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_energy_baseline = mysql.connector.connect(**config.myems_energy_baseline_db)
133
        cursor_energy_baseline = cnx_energy_baseline.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 cursor_energy_baseline:
154
                cursor_energy_baseline.close()
155
            if cnx_energy_baseline:
156
                cnx_energy_baseline.disconnect()
157
158
            if cnx_historical:
159
                cnx_historical.close()
160
            if cursor_historical:
161
                cursor_historical.disconnect()
162
            raise falcon.HTTPError(falcon.HTTP_404,
163
                                   title='API.NOT_FOUND',
164
                                   description='API.COMBINED_EQUIPMENT_NOT_FOUND')
165
166
        combined_equipment = dict()
167
        combined_equipment['id'] = row_combined_equipment[0]
168
        combined_equipment['name'] = row_combined_equipment[1]
169
        combined_equipment['cost_center_id'] = row_combined_equipment[2]
170
171
        ################################################################################################################
172
        # Step 3: query energy categories
173
        ################################################################################################################
174
        energy_category_set = set()
175
        # query 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.add(row_energy_category[0])
186
187
        # query 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.add(row_energy_category[0])
198
199
        # query all energy categories in base period and reporting period
200
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
201
                              " FROM tbl_energy_categories "
202
                              " ORDER BY id ", )
203
        rows_energy_categories = cursor_system.fetchall()
204
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
205
            if cursor_system:
206
                cursor_system.close()
207
            if cnx_system:
208
                cnx_system.disconnect()
209
210
            if cursor_energy:
211
                cursor_energy.close()
212
            if cnx_energy:
213
                cnx_energy.disconnect()
214
215
            if cursor_energy_baseline:
216
                cursor_energy_baseline.close()
217
            if cnx_energy_baseline:
218
                cnx_energy_baseline.disconnect()
219
220
            if cnx_historical:
221
                cnx_historical.close()
222
            if cursor_historical:
223
                cursor_historical.disconnect()
224
            raise falcon.HTTPError(falcon.HTTP_404,
225
                                   title='API.NOT_FOUND',
226
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
227
        energy_category_dict = dict()
228
        for row_energy_category in rows_energy_categories:
229
            if row_energy_category[0] in energy_category_set:
230
                energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
231
                                                                "unit_of_measure": row_energy_category[2],
232
                                                                "kgce": row_energy_category[3],
233
                                                                "kgco2e": row_energy_category[4]}
234
235
        ################################################################################################################
236
        # Step 4: query associated points
237
        ################################################################################################################
238
        point_list = list()
239
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
240
                              " FROM tbl_combined_equipments e, tbl_combined_equipments_parameters ep, tbl_points p "
241
                              " WHERE e.id = %s AND e.id = ep.combined_equipment_id AND ep.parameter_type = 'point' "
242
                              "       AND ep.point_id = p.id "
243
                              " ORDER BY p.id ", (combined_equipment['id'],))
244
        rows_points = cursor_system.fetchall()
245
        if rows_points is not None and len(rows_points) > 0:
246
            for row in rows_points:
247
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
248
249
        ################################################################################################################
250
        # Step 5: query base period energy saving
251
        ################################################################################################################
252
        base = dict()
253
        if energy_category_set is not None and len(energy_category_set) > 0:
254
            for energy_category_id in energy_category_set:
255
                kgce = energy_category_dict[energy_category_id]['kgce']
256
                kgco2e = energy_category_dict[energy_category_id]['kgco2e']
257
258
                base[energy_category_id] = dict()
259
                base[energy_category_id]['timestamps'] = list()
260
                base[energy_category_id]['values_baseline'] = list()
261
                base[energy_category_id]['values_actual'] = list()
262
                base[energy_category_id]['values_saving'] = list()
263
                base[energy_category_id]['subtotal_baseline'] = Decimal(0.0)
264
                base[energy_category_id]['subtotal_actual'] = Decimal(0.0)
265
                base[energy_category_id]['subtotal_saving'] = Decimal(0.0)
266
                base[energy_category_id]['subtotal_in_kgce_baseline'] = Decimal(0.0)
267
                base[energy_category_id]['subtotal_in_kgce_actual'] = Decimal(0.0)
268
                base[energy_category_id]['subtotal_in_kgce_saving'] = Decimal(0.0)
269
                base[energy_category_id]['subtotal_in_kgco2e_baseline'] = Decimal(0.0)
270
                base[energy_category_id]['subtotal_in_kgco2e_actual'] = Decimal(0.0)
271
                base[energy_category_id]['subtotal_in_kgco2e_saving'] = Decimal(0.0)
272
                # query base period's energy baseline
273
                cursor_energy_baseline.execute(" SELECT start_datetime_utc, actual_value "
274
                                               " FROM tbl_combined_equipment_input_category_hourly "
275
                                               " WHERE combined_equipment_id = %s "
276
                                               "     AND energy_category_id = %s "
277
                                               "     AND start_datetime_utc >= %s "
278
                                               "     AND start_datetime_utc < %s "
279
                                               " ORDER BY start_datetime_utc ",
280
                                               (combined_equipment['id'],
281
                                                energy_category_id,
282
                                                base_start_datetime_utc,
283
                                                base_end_datetime_utc))
284
                rows_combined_equipment_hourly = cursor_energy_baseline.fetchall()
285
286
                rows_combined_equipment_periodically = \
287
                    utilities.aggregate_hourly_data_by_period(rows_combined_equipment_hourly,
288
                                                              base_start_datetime_utc,
289
                                                              base_end_datetime_utc,
290
                                                              period_type)
291
                for row_combined_equipment_periodically in rows_combined_equipment_periodically:
292
                    current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
293
                                             timedelta(minutes=timezone_offset)
294
                    if period_type == 'hourly':
295
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
296
                    elif period_type == 'daily':
297
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
298
                    elif period_type == 'monthly':
299
                        current_datetime = current_datetime_local.strftime('%Y-%m')
300
                    elif period_type == 'yearly':
301
                        current_datetime = current_datetime_local.strftime('%Y')
302
303
                    baseline_value = Decimal(0.0) if row_combined_equipment_periodically[1] is None \
304
                        else row_combined_equipment_periodically[1]
305
                    base[energy_category_id]['timestamps'].append(current_datetime)
306
                    base[energy_category_id]['values_baseline'].append(baseline_value)
307
                    base[energy_category_id]['subtotal_baseline'] += baseline_value
308
                    base[energy_category_id]['subtotal_in_kgce_baseline'] += baseline_value * kgce
309
                    base[energy_category_id]['subtotal_in_kgco2e_baseline'] += baseline_value * kgco2e
310
311
                # query base period's energy actual
312
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
313
                                      " FROM tbl_combined_equipment_input_category_hourly "
314
                                      " WHERE combined_equipment_id = %s "
315
                                      "     AND energy_category_id = %s "
316
                                      "     AND start_datetime_utc >= %s "
317
                                      "     AND start_datetime_utc < %s "
318
                                      " ORDER BY start_datetime_utc ",
319
                                      (combined_equipment['id'],
320
                                       energy_category_id,
321
                                       base_start_datetime_utc,
322
                                       base_end_datetime_utc))
323
                rows_combined_equipment_hourly = cursor_energy.fetchall()
324
325
                rows_combined_equipment_periodically = \
326
                    utilities.aggregate_hourly_data_by_period(rows_combined_equipment_hourly,
327
                                                              base_start_datetime_utc,
328
                                                              base_end_datetime_utc,
329
                                                              period_type)
330
                for row_combined_equipment_periodically in rows_combined_equipment_periodically:
331
                    current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
332
                                             timedelta(minutes=timezone_offset)
333
                    if period_type == 'hourly':
334
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
335
                    elif period_type == 'daily':
336
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
337
                    elif period_type == 'monthly':
338
                        current_datetime = current_datetime_local.strftime('%Y-%m')
339
                    elif period_type == 'yearly':
340
                        current_datetime = current_datetime_local.strftime('%Y')
341
342
                    actual_value = Decimal(0.0) if row_combined_equipment_periodically[1] is None \
343
                        else row_combined_equipment_periodically[1]
344
                    base[energy_category_id]['values_actual'].append(actual_value)
345
                    base[energy_category_id]['subtotal_actual'] += actual_value
346
                    base[energy_category_id]['subtotal_in_kgce_actual'] += actual_value * kgce
347
                    base[energy_category_id]['subtotal_in_kgco2e_actual'] += actual_value * kgco2e
348
349
                # calculate base period's energy savings
350
                for i in range(len(base[energy_category_id]['values_baseline'])):
351
                    base[energy_category_id]['values_saving'].append(
352
                        base[energy_category_id]['values_baseline'][i] -
353
                        base[energy_category_id]['values_actual'][i])
354
355
                base[energy_category_id]['subtotal_saving'] = \
356
                    base[energy_category_id]['subtotal_baseline'] - \
357
                    base[energy_category_id]['subtotal_actual']
358
                base[energy_category_id]['subtotal_in_kgce_saving'] = \
359
                    base[energy_category_id]['subtotal_in_kgce_baseline'] - \
360
                    base[energy_category_id]['subtotal_in_kgce_actual']
361
                base[energy_category_id]['subtotal_in_kgco2e_saving'] = \
362
                    base[energy_category_id]['subtotal_in_kgco2e_baseline'] - \
363
                    base[energy_category_id]['subtotal_in_kgco2e_actual']
364
        ################################################################################################################
365
        # Step 5: query reporting period energy saving
366
        ################################################################################################################
367
        reporting = dict()
368
        if energy_category_set is not None and len(energy_category_set) > 0:
369
            for energy_category_id in energy_category_set:
370
                kgce = energy_category_dict[energy_category_id]['kgce']
371
                kgco2e = energy_category_dict[energy_category_id]['kgco2e']
372
373
                reporting[energy_category_id] = dict()
374
                reporting[energy_category_id]['timestamps'] = list()
375
                reporting[energy_category_id]['values_baseline'] = list()
376
                reporting[energy_category_id]['values_actual'] = list()
377
                reporting[energy_category_id]['values_saving'] = list()
378
                reporting[energy_category_id]['subtotal_baseline'] = Decimal(0.0)
379
                reporting[energy_category_id]['subtotal_actual'] = Decimal(0.0)
380
                reporting[energy_category_id]['subtotal_saving'] = Decimal(0.0)
381
                reporting[energy_category_id]['subtotal_in_kgce_baseline'] = Decimal(0.0)
382
                reporting[energy_category_id]['subtotal_in_kgce_actual'] = Decimal(0.0)
383
                reporting[energy_category_id]['subtotal_in_kgce_saving'] = Decimal(0.0)
384
                reporting[energy_category_id]['subtotal_in_kgco2e_baseline'] = Decimal(0.0)
385
                reporting[energy_category_id]['subtotal_in_kgco2e_actual'] = Decimal(0.0)
386
                reporting[energy_category_id]['subtotal_in_kgco2e_saving'] = Decimal(0.0)
387
                # query reporting period's energy baseline
388
                cursor_energy_baseline.execute(" SELECT start_datetime_utc, actual_value "
389
                                               " FROM tbl_combined_equipment_input_category_hourly "
390
                                               " WHERE combined_equipment_id = %s "
391
                                               "     AND energy_category_id = %s "
392
                                               "     AND start_datetime_utc >= %s "
393
                                               "     AND start_datetime_utc < %s "
394
                                               " ORDER BY start_datetime_utc ",
395
                                               (combined_equipment['id'],
396
                                                energy_category_id,
397
                                                reporting_start_datetime_utc,
398
                                                reporting_end_datetime_utc))
399
                rows_combined_equipment_hourly = cursor_energy_baseline.fetchall()
400
401
                rows_combined_equipment_periodically = \
402
                    utilities.aggregate_hourly_data_by_period(rows_combined_equipment_hourly,
403
                                                              reporting_start_datetime_utc,
404
                                                              reporting_end_datetime_utc,
405
                                                              period_type)
406
                for row_combined_equipment_periodically in rows_combined_equipment_periodically:
407
                    current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
408
                                             timedelta(minutes=timezone_offset)
409
                    if period_type == 'hourly':
410
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
411
                    elif period_type == 'daily':
412
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
413
                    elif period_type == 'monthly':
414
                        current_datetime = current_datetime_local.strftime('%Y-%m')
415
                    elif period_type == 'yearly':
416
                        current_datetime = current_datetime_local.strftime('%Y')
417
418
                    baseline_value = Decimal(0.0) if row_combined_equipment_periodically[1] is None \
419
                        else row_combined_equipment_periodically[1]
420
                    reporting[energy_category_id]['timestamps'].append(current_datetime)
421
                    reporting[energy_category_id]['values_baseline'].append(baseline_value)
422
                    reporting[energy_category_id]['subtotal_baseline'] += baseline_value
423
                    reporting[energy_category_id]['subtotal_in_kgce_baseline'] += baseline_value * kgce
424
                    reporting[energy_category_id]['subtotal_in_kgco2e_baseline'] += baseline_value * kgco2e
425
426
                # query reporting period's energy actual
427
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
428
                                      " FROM tbl_combined_equipment_input_category_hourly "
429
                                      " WHERE combined_equipment_id = %s "
430
                                      "     AND energy_category_id = %s "
431
                                      "     AND start_datetime_utc >= %s "
432
                                      "     AND start_datetime_utc < %s "
433
                                      " ORDER BY start_datetime_utc ",
434
                                      (combined_equipment['id'],
435
                                       energy_category_id,
436
                                       reporting_start_datetime_utc,
437
                                       reporting_end_datetime_utc))
438
                rows_combined_equipment_hourly = cursor_energy.fetchall()
439
440
                rows_combined_equipment_periodically = \
441
                    utilities.aggregate_hourly_data_by_period(rows_combined_equipment_hourly,
442
                                                              reporting_start_datetime_utc,
443
                                                              reporting_end_datetime_utc,
444
                                                              period_type)
445
                for row_combined_equipment_periodically in rows_combined_equipment_periodically:
446
                    current_datetime_local = row_combined_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
447
                                             timedelta(minutes=timezone_offset)
448
                    if period_type == 'hourly':
449
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
450
                    elif period_type == 'daily':
451
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
452
                    elif period_type == 'monthly':
453
                        current_datetime = current_datetime_local.strftime('%Y-%m')
454
                    elif period_type == 'yearly':
455
                        current_datetime = current_datetime_local.strftime('%Y')
456
457
                    actual_value = Decimal(0.0) if row_combined_equipment_periodically[1] is None \
458
                        else row_combined_equipment_periodically[1]
459
                    reporting[energy_category_id]['values_actual'].append(actual_value)
460
                    reporting[energy_category_id]['subtotal_actual'] += actual_value
461
                    reporting[energy_category_id]['subtotal_in_kgce_actual'] += actual_value * kgce
462
                    reporting[energy_category_id]['subtotal_in_kgco2e_actual'] += actual_value * kgco2e
463
464
                # calculate reporting period's energy savings
465
                for i in range(len(reporting[energy_category_id]['values_baseline'])):
466
                    reporting[energy_category_id]['values_saving'].append(
467
                        reporting[energy_category_id]['values_baseline'][i] -
468
                        reporting[energy_category_id]['values_actual'][i])
469
470
                reporting[energy_category_id]['subtotal_saving'] = \
471
                    reporting[energy_category_id]['subtotal_baseline'] - \
472
                    reporting[energy_category_id]['subtotal_actual']
473
                reporting[energy_category_id]['subtotal_in_kgce_saving'] = \
474
                    reporting[energy_category_id]['subtotal_in_kgce_baseline'] - \
475
                    reporting[energy_category_id]['subtotal_in_kgce_actual']
476
                reporting[energy_category_id]['subtotal_in_kgco2e_saving'] = \
477
                    reporting[energy_category_id]['subtotal_in_kgco2e_baseline'] - \
478
                    reporting[energy_category_id]['subtotal_in_kgco2e_actual']
479
        ################################################################################################################
480
        # Step 6: query tariff data
481
        ################################################################################################################
482
        parameters_data = dict()
483
        parameters_data['names'] = list()
484
        parameters_data['timestamps'] = list()
485
        parameters_data['values'] = list()
486
        if energy_category_set is not None and len(energy_category_set) > 0:
487
            for energy_category_id in energy_category_set:
488
                energy_category_tariff_dict = \
489
                    utilities.get_energy_category_tariffs(combined_equipment['cost_center_id'],
490
                                                          energy_category_id,
491
                                                          reporting_start_datetime_utc,
492
                                                          reporting_end_datetime_utc)
493
                tariff_timestamp_list = list()
494
                tariff_value_list = list()
495
                for k, v in energy_category_tariff_dict.items():
496
                    # convert k from utc to local
497
                    k = k + timedelta(minutes=timezone_offset)
498
                    tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
499
                    tariff_value_list.append(v)
500
501
                parameters_data['names'].append('TARIFF-' + energy_category_dict[energy_category_id]['name'])
502
                parameters_data['timestamps'].append(tariff_timestamp_list)
503
                parameters_data['values'].append(tariff_value_list)
504
505
        ################################################################################################################
506
        # Step 7: query associated points data
507
        ################################################################################################################
508
        for point in point_list:
509
            point_values = []
510
            point_timestamps = []
511
            if point['object_type'] == 'ANALOG_VALUE':
512
                query = (" SELECT utc_date_time, actual_value "
513
                         " FROM tbl_analog_value "
514
                         " WHERE point_id = %s "
515
                         "       AND utc_date_time BETWEEN %s AND %s "
516
                         " ORDER BY utc_date_time ")
517
                cursor_historical.execute(query, (point['id'],
518
                                                  reporting_start_datetime_utc,
519
                                                  reporting_end_datetime_utc))
520
                rows = cursor_historical.fetchall()
521
522
                if rows is not None and len(rows) > 0:
523
                    for row in rows:
524
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
525
                                                 timedelta(minutes=timezone_offset)
526
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
527
                        point_timestamps.append(current_datetime)
528
                        point_values.append(row[1])
529
530
            elif point['object_type'] == 'ENERGY_VALUE':
531
                query = (" SELECT utc_date_time, actual_value "
532
                         " FROM tbl_energy_value "
533
                         " WHERE point_id = %s "
534
                         "       AND utc_date_time BETWEEN %s AND %s "
535
                         " ORDER BY utc_date_time ")
536
                cursor_historical.execute(query, (point['id'],
537
                                                  reporting_start_datetime_utc,
538
                                                  reporting_end_datetime_utc))
539
                rows = cursor_historical.fetchall()
540
541
                if rows is not None and len(rows) > 0:
542
                    for row in rows:
543
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
544
                                                 timedelta(minutes=timezone_offset)
545
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
546
                        point_timestamps.append(current_datetime)
547
                        point_values.append(row[1])
548
            elif point['object_type'] == 'DIGITAL_VALUE':
549
                query = (" SELECT utc_date_time, actual_value "
550
                         " FROM tbl_digital_value "
551
                         " WHERE point_id = %s "
552
                         "       AND utc_date_time BETWEEN %s AND %s ")
553
                cursor_historical.execute(query, (point['id'],
554
                                                  reporting_start_datetime_utc,
555
                                                  reporting_end_datetime_utc))
556
                rows = cursor_historical.fetchall()
557
558
                if rows is not None and len(rows) > 0:
559
                    for row in rows:
560
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
561
                                                 timedelta(minutes=timezone_offset)
562
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
563
                        point_timestamps.append(current_datetime)
564
                        point_values.append(row[1])
565
566
            parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
567
            parameters_data['timestamps'].append(point_timestamps)
568
            parameters_data['values'].append(point_values)
569
570
        ################################################################################################################
571
        # Step 8: construct the report
572
        ################################################################################################################
573
        if cursor_system:
574
            cursor_system.close()
575
        if cnx_system:
576
            cnx_system.disconnect()
577
578
        if cursor_energy:
579
            cursor_energy.close()
580
        if cnx_energy:
581
            cnx_energy.disconnect()
582
583
        if cursor_energy_baseline:
584
            cursor_energy_baseline.close()
585
        if cnx_energy_baseline:
586
            cnx_energy_baseline.disconnect()
587
588
        result = dict()
589
590
        result['combined_equipment'] = dict()
591
        result['combined_equipment']['name'] = combined_equipment['name']
592
593
        result['base_period'] = dict()
594
        result['base_period']['names'] = list()
595
        result['base_period']['units'] = list()
596
        result['base_period']['timestamps'] = list()
597
        result['base_period']['values_saving'] = list()
598
        result['base_period']['subtotals_saving'] = list()
599
        result['base_period']['subtotals_in_kgce_saving'] = list()
600
        result['base_period']['subtotals_in_kgco2e_saving'] = list()
601
        result['base_period']['total_in_kgce_saving'] = Decimal(0.0)
602
        result['base_period']['total_in_kgco2e_saving'] = Decimal(0.0)
603
        if energy_category_set is not None and len(energy_category_set) > 0:
604
            for energy_category_id in energy_category_set:
605
                result['base_period']['names'].append(energy_category_dict[energy_category_id]['name'])
606
                result['base_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
607
                result['base_period']['timestamps'].append(base[energy_category_id]['timestamps'])
608
                result['base_period']['values_saving'].append(base[energy_category_id]['values_saving'])
609
                result['base_period']['subtotals_saving'].append(base[energy_category_id]['subtotal_saving'])
610
                result['base_period']['subtotals_in_kgce_saving'].append(
611
                    base[energy_category_id]['subtotal_in_kgce_saving'])
612
                result['base_period']['subtotals_in_kgco2e_saving'].append(
613
                    base[energy_category_id]['subtotal_in_kgco2e_saving'])
614
                result['base_period']['total_in_kgce_saving'] += base[energy_category_id]['subtotal_in_kgce_saving']
615
                result['base_period']['total_in_kgco2e_saving'] += base[energy_category_id]['subtotal_in_kgco2e_saving']
616
617
        result['reporting_period'] = dict()
618
        result['reporting_period']['names'] = list()
619
        result['reporting_period']['energy_category_ids'] = list()
620
        result['reporting_period']['units'] = list()
621
        result['reporting_period']['timestamps'] = list()
622
        result['reporting_period']['values_saving'] = list()
623
        result['reporting_period']['subtotals_saving'] = list()
624
        result['reporting_period']['subtotals_in_kgce_saving'] = list()
625
        result['reporting_period']['subtotals_in_kgco2e_saving'] = list()
626
        result['reporting_period']['increment_rates_saving'] = list()
627
        result['reporting_period']['total_in_kgce_saving'] = Decimal(0.0)
628
        result['reporting_period']['total_in_kgco2e_saving'] = Decimal(0.0)
629
        result['reporting_period']['increment_rate_in_kgce_saving'] = Decimal(0.0)
630
        result['reporting_period']['increment_rate_in_kgco2e_saving'] = Decimal(0.0)
631
632
        if energy_category_set is not None and len(energy_category_set) > 0:
633
            for energy_category_id in energy_category_set:
634
                result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name'])
635
                result['reporting_period']['energy_category_ids'].append(energy_category_id)
636
                result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
637
                result['reporting_period']['timestamps'].append(reporting[energy_category_id]['timestamps'])
638
                result['reporting_period']['values_saving'].append(reporting[energy_category_id]['values_saving'])
639
                result['reporting_period']['subtotals_saving'].append(reporting[energy_category_id]['subtotal_saving'])
640
                result['reporting_period']['subtotals_in_kgce_saving'].append(
641
                    reporting[energy_category_id]['subtotal_in_kgce_saving'])
642
                result['reporting_period']['subtotals_in_kgco2e_saving'].append(
643
                    reporting[energy_category_id]['subtotal_in_kgco2e_saving'])
644
                result['reporting_period']['increment_rates_saving'].append(
645
                    (reporting[energy_category_id]['subtotal_saving'] - base[energy_category_id]['subtotal_saving']) /
646
                    base[energy_category_id]['subtotal_saving']
647
                    if base[energy_category_id]['subtotal_saving'] > 0.0 else None)
648
                result['reporting_period']['total_in_kgce_saving'] += \
649
                    reporting[energy_category_id]['subtotal_in_kgce_saving']
650
                result['reporting_period']['total_in_kgco2e_saving'] += \
651
                    reporting[energy_category_id]['subtotal_in_kgco2e_saving']
652
653
        result['reporting_period']['increment_rate_in_kgce_saving'] = \
654
            (result['reporting_period']['total_in_kgce_saving'] - result['base_period']['total_in_kgce_saving']) / \
655
            result['base_period']['total_in_kgce_saving'] \
656
            if result['base_period']['total_in_kgce_saving'] > Decimal(0.0) else None
657
658
        result['reporting_period']['increment_rate_in_kgco2e_saving'] = \
659
            (result['reporting_period']['total_in_kgco2e_saving'] - result['base_period']['total_in_kgco2e_saving']) / \
660
            result['base_period']['total_in_kgco2e_saving'] \
661
            if result['base_period']['total_in_kgco2e_saving'] > Decimal(0.0) else None
662
663
        result['parameters'] = {
664
            "names": parameters_data['names'],
665
            "timestamps": parameters_data['timestamps'],
666
            "values": parameters_data['values']
667
        }
668
669
        resp.body = json.dumps(result)
670

reports/equipmentsaving.py 1 location

@@ 10-658 (lines=649) @@
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 saving
26
    # Step 6: query reporting period energy saving
27
    # Step 7: query tariff data
28
    # Step 8: query associated points data
29
    # Step 10: 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_energy_baseline = mysql.connector.connect(**config.myems_energy_baseline_db)
129
        cursor_energy_baseline = cnx_energy_baseline.cursor()
130
131
        cnx_historical = mysql.connector.connect(**config.myems_historical_db)
132
        cursor_historical = cnx_historical.cursor()
133
134
        cursor_system.execute(" SELECT id, name, cost_center_id "
135
                              " FROM tbl_equipments "
136
                              " WHERE id = %s ", (equipment_id,))
137
        row_equipment = cursor_system.fetchone()
138
        if row_equipment is None:
139
            if cursor_system:
140
                cursor_system.close()
141
            if cnx_system:
142
                cnx_system.disconnect()
143
144
            if cursor_energy:
145
                cursor_energy.close()
146
            if cnx_energy:
147
                cnx_energy.disconnect()
148
149
            if cursor_energy_baseline:
150
                cursor_energy_baseline.close()
151
            if cnx_energy_baseline:
152
                cnx_energy_baseline.disconnect()
153
154
            if cnx_historical:
155
                cnx_historical.close()
156
            if cursor_historical:
157
                cursor_historical.disconnect()
158
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.EQUIPMENT_NOT_FOUND')
159
160
        equipment = dict()
161
        equipment['id'] = row_equipment[0]
162
        equipment['name'] = row_equipment[1]
163
        equipment['cost_center_id'] = row_equipment[2]
164
165
        ################################################################################################################
166
        # Step 3: query energy categories
167
        ################################################################################################################
168
        energy_category_set = set()
169
        # query energy categories in base period
170
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
171
                              " FROM tbl_equipment_input_category_hourly "
172
                              " WHERE equipment_id = %s "
173
                              "     AND start_datetime_utc >= %s "
174
                              "     AND start_datetime_utc < %s ",
175
                              (equipment['id'], base_start_datetime_utc, base_end_datetime_utc))
176
        rows_energy_categories = cursor_energy.fetchall()
177
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
178
            for row_energy_category in rows_energy_categories:
179
                energy_category_set.add(row_energy_category[0])
180
181
        # query energy categories in reporting period
182
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
183
                              " FROM tbl_equipment_input_category_hourly "
184
                              " WHERE equipment_id = %s "
185
                              "     AND start_datetime_utc >= %s "
186
                              "     AND start_datetime_utc < %s ",
187
                              (equipment['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
188
        rows_energy_categories = cursor_energy.fetchall()
189
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
190
            for row_energy_category in rows_energy_categories:
191
                energy_category_set.add(row_energy_category[0])
192
193
        # query all energy categories in base period and reporting period
194
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
195
                              " FROM tbl_energy_categories "
196
                              " ORDER BY id ", )
197
        rows_energy_categories = cursor_system.fetchall()
198
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
199
            if cursor_system:
200
                cursor_system.close()
201
            if cnx_system:
202
                cnx_system.disconnect()
203
204
            if cursor_energy:
205
                cursor_energy.close()
206
            if cnx_energy:
207
                cnx_energy.disconnect()
208
209
            if cursor_energy_baseline:
210
                cursor_energy_baseline.close()
211
            if cnx_energy_baseline:
212
                cnx_energy_baseline.disconnect()
213
214
            if cnx_historical:
215
                cnx_historical.close()
216
            if cursor_historical:
217
                cursor_historical.disconnect()
218
            raise falcon.HTTPError(falcon.HTTP_404,
219
                                   title='API.NOT_FOUND',
220
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
221
        energy_category_dict = dict()
222
        for row_energy_category in rows_energy_categories:
223
            if row_energy_category[0] in energy_category_set:
224
                energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
225
                                                                "unit_of_measure": row_energy_category[2],
226
                                                                "kgce": row_energy_category[3],
227
                                                                "kgco2e": row_energy_category[4]}
228
229
        ################################################################################################################
230
        # Step 4: query associated points
231
        ################################################################################################################
232
        point_list = list()
233
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
234
                              " FROM tbl_equipments e, tbl_equipments_parameters ep, tbl_points p "
235
                              " WHERE e.id = %s AND e.id = ep.equipment_id AND ep.parameter_type = 'point' "
236
                              "       AND ep.point_id = p.id "
237
                              " ORDER BY p.id ", (equipment['id'],))
238
        rows_points = cursor_system.fetchall()
239
        if rows_points is not None and len(rows_points) > 0:
240
            for row in rows_points:
241
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
242
243
        ################################################################################################################
244
        # Step 5: query base period energy saving
245
        ################################################################################################################
246
        base = dict()
247
        if energy_category_set is not None and len(energy_category_set) > 0:
248
            for energy_category_id in energy_category_set:
249
                kgce = energy_category_dict[energy_category_id]['kgce']
250
                kgco2e = energy_category_dict[energy_category_id]['kgco2e']
251
252
                base[energy_category_id] = dict()
253
                base[energy_category_id]['timestamps'] = list()
254
                base[energy_category_id]['values_baseline'] = list()
255
                base[energy_category_id]['values_actual'] = list()
256
                base[energy_category_id]['values_saving'] = list()
257
                base[energy_category_id]['subtotal_baseline'] = Decimal(0.0)
258
                base[energy_category_id]['subtotal_actual'] = Decimal(0.0)
259
                base[energy_category_id]['subtotal_saving'] = Decimal(0.0)
260
                base[energy_category_id]['subtotal_in_kgce_baseline'] = Decimal(0.0)
261
                base[energy_category_id]['subtotal_in_kgce_actual'] = Decimal(0.0)
262
                base[energy_category_id]['subtotal_in_kgce_saving'] = Decimal(0.0)
263
                base[energy_category_id]['subtotal_in_kgco2e_baseline'] = Decimal(0.0)
264
                base[energy_category_id]['subtotal_in_kgco2e_actual'] = Decimal(0.0)
265
                base[energy_category_id]['subtotal_in_kgco2e_saving'] = Decimal(0.0)
266
                # query base period's energy baseline
267
                cursor_energy_baseline.execute(" SELECT start_datetime_utc, actual_value "
268
                                               " FROM tbl_equipment_input_category_hourly "
269
                                               " WHERE equipment_id = %s "
270
                                               "     AND energy_category_id = %s "
271
                                               "     AND start_datetime_utc >= %s "
272
                                               "     AND start_datetime_utc < %s "
273
                                               " ORDER BY start_datetime_utc ",
274
                                               (equipment['id'],
275
                                                energy_category_id,
276
                                                base_start_datetime_utc,
277
                                                base_end_datetime_utc))
278
                rows_equipment_hourly = cursor_energy_baseline.fetchall()
279
280
                rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly,
281
                                                                                        base_start_datetime_utc,
282
                                                                                        base_end_datetime_utc,
283
                                                                                        period_type)
284
                for row_equipment_periodically in rows_equipment_periodically:
285
                    current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
286
                                             timedelta(minutes=timezone_offset)
287
                    if period_type == 'hourly':
288
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
289
                    elif period_type == 'daily':
290
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
291
                    elif period_type == 'monthly':
292
                        current_datetime = current_datetime_local.strftime('%Y-%m')
293
                    elif period_type == 'yearly':
294
                        current_datetime = current_datetime_local.strftime('%Y')
295
296
                    baseline_value = Decimal(0.0) if row_equipment_periodically[1] is None \
297
                        else row_equipment_periodically[1]
298
                    base[energy_category_id]['timestamps'].append(current_datetime)
299
                    base[energy_category_id]['values_baseline'].append(baseline_value)
300
                    base[energy_category_id]['subtotal_baseline'] += baseline_value
301
                    base[energy_category_id]['subtotal_in_kgce_baseline'] += baseline_value * kgce
302
                    base[energy_category_id]['subtotal_in_kgco2e_baseline'] += baseline_value * kgco2e
303
304
                # query base period's energy actual
305
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
306
                                      " FROM tbl_equipment_input_category_hourly "
307
                                      " WHERE equipment_id = %s "
308
                                      "     AND energy_category_id = %s "
309
                                      "     AND start_datetime_utc >= %s "
310
                                      "     AND start_datetime_utc < %s "
311
                                      " ORDER BY start_datetime_utc ",
312
                                      (equipment['id'],
313
                                       energy_category_id,
314
                                       base_start_datetime_utc,
315
                                       base_end_datetime_utc))
316
                rows_equipment_hourly = cursor_energy.fetchall()
317
318
                rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly,
319
                                                                                        base_start_datetime_utc,
320
                                                                                        base_end_datetime_utc,
321
                                                                                        period_type)
322
                for row_equipment_periodically in rows_equipment_periodically:
323
                    current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
324
                                             timedelta(minutes=timezone_offset)
325
                    if period_type == 'hourly':
326
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
327
                    elif period_type == 'daily':
328
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
329
                    elif period_type == 'monthly':
330
                        current_datetime = current_datetime_local.strftime('%Y-%m')
331
                    elif period_type == 'yearly':
332
                        current_datetime = current_datetime_local.strftime('%Y')
333
334
                    actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
335
                        else row_equipment_periodically[1]
336
                    base[energy_category_id]['values_actual'].append(actual_value)
337
                    base[energy_category_id]['subtotal_actual'] += actual_value
338
                    base[energy_category_id]['subtotal_in_kgce_actual'] += actual_value * kgce
339
                    base[energy_category_id]['subtotal_in_kgco2e_actual'] += actual_value * kgco2e
340
341
                # calculate base period's energy savings
342
                for i in range(len(base[energy_category_id]['values_baseline'])):
343
                    base[energy_category_id]['values_saving'].append(
344
                        base[energy_category_id]['values_baseline'][i] -
345
                        base[energy_category_id]['values_actual'][i])
346
347
                base[energy_category_id]['subtotal_saving'] = \
348
                    base[energy_category_id]['subtotal_baseline'] - \
349
                    base[energy_category_id]['subtotal_actual']
350
                base[energy_category_id]['subtotal_in_kgce_saving'] = \
351
                    base[energy_category_id]['subtotal_in_kgce_baseline'] - \
352
                    base[energy_category_id]['subtotal_in_kgce_actual']
353
                base[energy_category_id]['subtotal_in_kgco2e_saving'] = \
354
                    base[energy_category_id]['subtotal_in_kgco2e_baseline'] - \
355
                    base[energy_category_id]['subtotal_in_kgco2e_actual']
356
        ################################################################################################################
357
        # Step 5: query reporting period energy saving
358
        ################################################################################################################
359
        reporting = dict()
360
        if energy_category_set is not None and len(energy_category_set) > 0:
361
            for energy_category_id in energy_category_set:
362
                kgce = energy_category_dict[energy_category_id]['kgce']
363
                kgco2e = energy_category_dict[energy_category_id]['kgco2e']
364
365
                reporting[energy_category_id] = dict()
366
                reporting[energy_category_id]['timestamps'] = list()
367
                reporting[energy_category_id]['values_baseline'] = list()
368
                reporting[energy_category_id]['values_actual'] = list()
369
                reporting[energy_category_id]['values_saving'] = list()
370
                reporting[energy_category_id]['subtotal_baseline'] = Decimal(0.0)
371
                reporting[energy_category_id]['subtotal_actual'] = Decimal(0.0)
372
                reporting[energy_category_id]['subtotal_saving'] = Decimal(0.0)
373
                reporting[energy_category_id]['subtotal_in_kgce_baseline'] = Decimal(0.0)
374
                reporting[energy_category_id]['subtotal_in_kgce_actual'] = Decimal(0.0)
375
                reporting[energy_category_id]['subtotal_in_kgce_saving'] = Decimal(0.0)
376
                reporting[energy_category_id]['subtotal_in_kgco2e_baseline'] = Decimal(0.0)
377
                reporting[energy_category_id]['subtotal_in_kgco2e_actual'] = Decimal(0.0)
378
                reporting[energy_category_id]['subtotal_in_kgco2e_saving'] = Decimal(0.0)
379
                # query reporting period's energy baseline
380
                cursor_energy_baseline.execute(" SELECT start_datetime_utc, actual_value "
381
                                               " FROM tbl_equipment_input_category_hourly "
382
                                               " WHERE equipment_id = %s "
383
                                               "     AND energy_category_id = %s "
384
                                               "     AND start_datetime_utc >= %s "
385
                                               "     AND start_datetime_utc < %s "
386
                                               " ORDER BY start_datetime_utc ",
387
                                               (equipment['id'],
388
                                                energy_category_id,
389
                                                reporting_start_datetime_utc,
390
                                                reporting_end_datetime_utc))
391
                rows_equipment_hourly = cursor_energy_baseline.fetchall()
392
393
                rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly,
394
                                                                                        reporting_start_datetime_utc,
395
                                                                                        reporting_end_datetime_utc,
396
                                                                                        period_type)
397
                for row_equipment_periodically in rows_equipment_periodically:
398
                    current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
399
                                             timedelta(minutes=timezone_offset)
400
                    if period_type == 'hourly':
401
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
402
                    elif period_type == 'daily':
403
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
404
                    elif period_type == 'monthly':
405
                        current_datetime = current_datetime_local.strftime('%Y-%m')
406
                    elif period_type == 'yearly':
407
                        current_datetime = current_datetime_local.strftime('%Y')
408
409
                    baseline_value = Decimal(0.0) if row_equipment_periodically[1] is None \
410
                        else row_equipment_periodically[1]
411
                    reporting[energy_category_id]['timestamps'].append(current_datetime)
412
                    reporting[energy_category_id]['values_baseline'].append(baseline_value)
413
                    reporting[energy_category_id]['subtotal_baseline'] += baseline_value
414
                    reporting[energy_category_id]['subtotal_in_kgce_baseline'] += baseline_value * kgce
415
                    reporting[energy_category_id]['subtotal_in_kgco2e_baseline'] += baseline_value * kgco2e
416
417
                # query reporting period's energy actual
418
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
419
                                      " FROM tbl_equipment_input_category_hourly "
420
                                      " WHERE equipment_id = %s "
421
                                      "     AND energy_category_id = %s "
422
                                      "     AND start_datetime_utc >= %s "
423
                                      "     AND start_datetime_utc < %s "
424
                                      " ORDER BY start_datetime_utc ",
425
                                      (equipment['id'],
426
                                       energy_category_id,
427
                                       reporting_start_datetime_utc,
428
                                       reporting_end_datetime_utc))
429
                rows_equipment_hourly = cursor_energy.fetchall()
430
431
                rows_equipment_periodically = utilities.aggregate_hourly_data_by_period(rows_equipment_hourly,
432
                                                                                        reporting_start_datetime_utc,
433
                                                                                        reporting_end_datetime_utc,
434
                                                                                        period_type)
435
                for row_equipment_periodically in rows_equipment_periodically:
436
                    current_datetime_local = row_equipment_periodically[0].replace(tzinfo=timezone.utc) + \
437
                                             timedelta(minutes=timezone_offset)
438
                    if period_type == 'hourly':
439
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
440
                    elif period_type == 'daily':
441
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
442
                    elif period_type == 'monthly':
443
                        current_datetime = current_datetime_local.strftime('%Y-%m')
444
                    elif period_type == 'yearly':
445
                        current_datetime = current_datetime_local.strftime('%Y')
446
447
                    actual_value = Decimal(0.0) if row_equipment_periodically[1] is None \
448
                        else row_equipment_periodically[1]
449
                    reporting[energy_category_id]['values_actual'].append(actual_value)
450
                    reporting[energy_category_id]['subtotal_actual'] += actual_value
451
                    reporting[energy_category_id]['subtotal_in_kgce_actual'] += actual_value * kgce
452
                    reporting[energy_category_id]['subtotal_in_kgco2e_actual'] += actual_value * kgco2e
453
454
                # calculate reporting period's energy savings
455
                for i in range(len(reporting[energy_category_id]['values_baseline'])):
456
                    reporting[energy_category_id]['values_saving'].append(
457
                        reporting[energy_category_id]['values_baseline'][i] -
458
                        reporting[energy_category_id]['values_actual'][i])
459
460
                reporting[energy_category_id]['subtotal_saving'] = \
461
                    reporting[energy_category_id]['subtotal_baseline'] - \
462
                    reporting[energy_category_id]['subtotal_actual']
463
                reporting[energy_category_id]['subtotal_in_kgce_saving'] = \
464
                    reporting[energy_category_id]['subtotal_in_kgce_baseline'] - \
465
                    reporting[energy_category_id]['subtotal_in_kgce_actual']
466
                reporting[energy_category_id]['subtotal_in_kgco2e_saving'] = \
467
                    reporting[energy_category_id]['subtotal_in_kgco2e_baseline'] - \
468
                    reporting[energy_category_id]['subtotal_in_kgco2e_actual']
469
        ################################################################################################################
470
        # Step 6: query tariff data
471
        ################################################################################################################
472
        parameters_data = dict()
473
        parameters_data['names'] = list()
474
        parameters_data['timestamps'] = list()
475
        parameters_data['values'] = list()
476
        if energy_category_set is not None and len(energy_category_set) > 0:
477
            for energy_category_id in energy_category_set:
478
                energy_category_tariff_dict = utilities.get_energy_category_tariffs(equipment['cost_center_id'],
479
                                                                                    energy_category_id,
480
                                                                                    reporting_start_datetime_utc,
481
                                                                                    reporting_end_datetime_utc)
482
                tariff_timestamp_list = list()
483
                tariff_value_list = list()
484
                for k, v in energy_category_tariff_dict.items():
485
                    # convert k from utc to local
486
                    k = k + timedelta(minutes=timezone_offset)
487
                    tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
488
                    tariff_value_list.append(v)
489
490
                parameters_data['names'].append('TARIFF-' + energy_category_dict[energy_category_id]['name'])
491
                parameters_data['timestamps'].append(tariff_timestamp_list)
492
                parameters_data['values'].append(tariff_value_list)
493
494
        ################################################################################################################
495
        # Step 7: query associated points data
496
        ################################################################################################################
497
        for point in point_list:
498
            point_values = []
499
            point_timestamps = []
500
            if point['object_type'] == 'ANALOG_VALUE':
501
                query = (" SELECT utc_date_time, actual_value "
502
                         " FROM tbl_analog_value "
503
                         " WHERE point_id = %s "
504
                         "       AND utc_date_time BETWEEN %s AND %s "
505
                         " ORDER BY utc_date_time ")
506
                cursor_historical.execute(query, (point['id'],
507
                                                  reporting_start_datetime_utc,
508
                                                  reporting_end_datetime_utc))
509
                rows = cursor_historical.fetchall()
510
511
                if rows is not None and len(rows) > 0:
512
                    for row in rows:
513
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
514
                                                 timedelta(minutes=timezone_offset)
515
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
516
                        point_timestamps.append(current_datetime)
517
                        point_values.append(row[1])
518
519
            elif point['object_type'] == 'ENERGY_VALUE':
520
                query = (" SELECT utc_date_time, actual_value "
521
                         " FROM tbl_energy_value "
522
                         " WHERE point_id = %s "
523
                         "       AND utc_date_time BETWEEN %s AND %s "
524
                         " ORDER BY utc_date_time ")
525
                cursor_historical.execute(query, (point['id'],
526
                                                  reporting_start_datetime_utc,
527
                                                  reporting_end_datetime_utc))
528
                rows = cursor_historical.fetchall()
529
530
                if rows is not None and len(rows) > 0:
531
                    for row in rows:
532
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
533
                                                 timedelta(minutes=timezone_offset)
534
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
535
                        point_timestamps.append(current_datetime)
536
                        point_values.append(row[1])
537
            elif point['object_type'] == 'DIGITAL_VALUE':
538
                query = (" SELECT utc_date_time, actual_value "
539
                         " FROM tbl_digital_value "
540
                         " WHERE point_id = %s "
541
                         "       AND utc_date_time BETWEEN %s AND %s ")
542
                cursor_historical.execute(query, (point['id'],
543
                                                  reporting_start_datetime_utc,
544
                                                  reporting_end_datetime_utc))
545
                rows = cursor_historical.fetchall()
546
547
                if rows is not None and len(rows) > 0:
548
                    for row in rows:
549
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
550
                                                 timedelta(minutes=timezone_offset)
551
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
552
                        point_timestamps.append(current_datetime)
553
                        point_values.append(row[1])
554
555
            parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
556
            parameters_data['timestamps'].append(point_timestamps)
557
            parameters_data['values'].append(point_values)
558
559
        ################################################################################################################
560
        # Step 8: construct the report
561
        ################################################################################################################
562
        if cursor_system:
563
            cursor_system.close()
564
        if cnx_system:
565
            cnx_system.disconnect()
566
567
        if cursor_energy:
568
            cursor_energy.close()
569
        if cnx_energy:
570
            cnx_energy.disconnect()
571
572
        if cursor_energy_baseline:
573
            cursor_energy_baseline.close()
574
        if cnx_energy_baseline:
575
            cnx_energy_baseline.disconnect()
576
577
        result = dict()
578
579
        result['equipment'] = dict()
580
        result['equipment']['name'] = equipment['name']
581
582
        result['base_period'] = dict()
583
        result['base_period']['names'] = list()
584
        result['base_period']['units'] = list()
585
        result['base_period']['timestamps'] = list()
586
        result['base_period']['values_saving'] = list()
587
        result['base_period']['subtotals_saving'] = list()
588
        result['base_period']['subtotals_in_kgce_saving'] = list()
589
        result['base_period']['subtotals_in_kgco2e_saving'] = list()
590
        result['base_period']['total_in_kgce_saving'] = Decimal(0.0)
591
        result['base_period']['total_in_kgco2e_saving'] = Decimal(0.0)
592
        if energy_category_set is not None and len(energy_category_set) > 0:
593
            for energy_category_id in energy_category_set:
594
                result['base_period']['names'].append(energy_category_dict[energy_category_id]['name'])
595
                result['base_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
596
                result['base_period']['timestamps'].append(base[energy_category_id]['timestamps'])
597
                result['base_period']['values_saving'].append(base[energy_category_id]['values_saving'])
598
                result['base_period']['subtotals_saving'].append(base[energy_category_id]['subtotal_saving'])
599
                result['base_period']['subtotals_in_kgce_saving'].append(
600
                    base[energy_category_id]['subtotal_in_kgce_saving'])
601
                result['base_period']['subtotals_in_kgco2e_saving'].append(
602
                    base[energy_category_id]['subtotal_in_kgco2e_saving'])
603
                result['base_period']['total_in_kgce_saving'] += base[energy_category_id]['subtotal_in_kgce_saving']
604
                result['base_period']['total_in_kgco2e_saving'] += base[energy_category_id]['subtotal_in_kgco2e_saving']
605
606
        result['reporting_period'] = dict()
607
        result['reporting_period']['names'] = list()
608
        result['reporting_period']['energy_category_ids'] = list()
609
        result['reporting_period']['units'] = list()
610
        result['reporting_period']['timestamps'] = list()
611
        result['reporting_period']['values_saving'] = list()
612
        result['reporting_period']['subtotals_saving'] = list()
613
        result['reporting_period']['subtotals_in_kgce_saving'] = list()
614
        result['reporting_period']['subtotals_in_kgco2e_saving'] = list()
615
        result['reporting_period']['increment_rates_saving'] = list()
616
        result['reporting_period']['total_in_kgce_saving'] = Decimal(0.0)
617
        result['reporting_period']['total_in_kgco2e_saving'] = Decimal(0.0)
618
        result['reporting_period']['increment_rate_in_kgce_saving'] = Decimal(0.0)
619
        result['reporting_period']['increment_rate_in_kgco2e_saving'] = Decimal(0.0)
620
621
        if energy_category_set is not None and len(energy_category_set) > 0:
622
            for energy_category_id in energy_category_set:
623
                result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name'])
624
                result['reporting_period']['energy_category_ids'].append(energy_category_id)
625
                result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
626
                result['reporting_period']['timestamps'].append(reporting[energy_category_id]['timestamps'])
627
                result['reporting_period']['values_saving'].append(reporting[energy_category_id]['values_saving'])
628
                result['reporting_period']['subtotals_saving'].append(reporting[energy_category_id]['subtotal_saving'])
629
                result['reporting_period']['subtotals_in_kgce_saving'].append(
630
                    reporting[energy_category_id]['subtotal_in_kgce_saving'])
631
                result['reporting_period']['subtotals_in_kgco2e_saving'].append(
632
                    reporting[energy_category_id]['subtotal_in_kgco2e_saving'])
633
                result['reporting_period']['increment_rates_saving'].append(
634
                    (reporting[energy_category_id]['subtotal_saving'] - base[energy_category_id]['subtotal_saving']) /
635
                    base[energy_category_id]['subtotal_saving']
636
                    if base[energy_category_id]['subtotal_saving'] > 0.0 else None)
637
                result['reporting_period']['total_in_kgce_saving'] += \
638
                    reporting[energy_category_id]['subtotal_in_kgce_saving']
639
                result['reporting_period']['total_in_kgco2e_saving'] += \
640
                    reporting[energy_category_id]['subtotal_in_kgco2e_saving']
641
642
        result['reporting_period']['increment_rate_in_kgce_saving'] = \
643
            (result['reporting_period']['total_in_kgce_saving'] - result['base_period']['total_in_kgce_saving']) / \
644
            result['base_period']['total_in_kgce_saving'] \
645
            if result['base_period']['total_in_kgce_saving'] > Decimal(0.0) else None
646
647
        result['reporting_period']['increment_rate_in_kgco2e_saving'] = \
648
            (result['reporting_period']['total_in_kgco2e_saving'] - result['base_period']['total_in_kgco2e_saving']) / \
649
            result['base_period']['total_in_kgco2e_saving'] \
650
            if result['base_period']['total_in_kgco2e_saving'] > Decimal(0.0) else None
651
652
        result['parameters'] = {
653
            "names": parameters_data['names'],
654
            "timestamps": parameters_data['timestamps'],
655
            "values": parameters_data['values']
656
        }
657
658
        resp.body = json.dumps(result)
659