Code Duplication    Length = 609-612 lines in 4 locations

reports/shopfloorstatistics.py 1 location

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

reports/storestatistics.py 1 location

@@ 12-620 (lines=609) @@
9
from decimal import Decimal
10
11
12
class Reporting:
13
    @staticmethod
14
    def __init__():
15
        pass
16
17
    @staticmethod
18
    def on_options(req, resp):
19
        resp.status = falcon.HTTP_200
20
21
    ####################################################################################################################
22
    # PROCEDURES
23
    # Step 1: valid parameters
24
    # Step 2: query the store
25
    # Step 3: query energy categories
26
    # Step 4: query associated sensors
27
    # Step 5: query associated points
28
    # Step 6: query base period energy input
29
    # Step 7: query reporting period energy input
30
    # Step 8: query tariff data
31
    # Step 9: query associated sensors and points data
32
    # Step 10: construct the report
33
    ####################################################################################################################
34
    @staticmethod
35
    def on_get(req, resp):
36
        print(req.params)
37
        store_id = req.params.get('storeid')
38
        period_type = req.params.get('periodtype')
39
        base_start_datetime_local = req.params.get('baseperiodstartdatetime')
40
        base_end_datetime_local = req.params.get('baseperiodenddatetime')
41
        reporting_start_datetime_local = req.params.get('reportingperiodstartdatetime')
42
        reporting_end_datetime_local = req.params.get('reportingperiodenddatetime')
43
44
        ################################################################################################################
45
        # Step 1: valid parameters
46
        ################################################################################################################
47
        if store_id is None:
48
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_STORE_ID')
49
        else:
50
            store_id = str.strip(store_id)
51
            if not store_id.isdigit() or int(store_id) <= 0:
52
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_STORE_ID')
53
54
        if period_type is None:
55
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
56
        else:
57
            period_type = str.strip(period_type)
58
            if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
59
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
60
61
        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
62
        if config.utc_offset[0] == '-':
63
            timezone_offset = -timezone_offset
64
65
        base_start_datetime_utc = None
66
        if base_start_datetime_local is not None and len(str.strip(base_start_datetime_local)) > 0:
67
            base_start_datetime_local = str.strip(base_start_datetime_local)
68
            try:
69
                base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
70
                                                            '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
71
                                          timedelta(minutes=timezone_offset)
72
            except ValueError:
73
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
74
                                       description="API.INVALID_BASE_PERIOD_START_DATETIME")
75
76
        base_end_datetime_utc = None
77
        if base_end_datetime_local is not None and len(str.strip(base_end_datetime_local)) > 0:
78
            base_end_datetime_local = str.strip(base_end_datetime_local)
79
            try:
80
                base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
81
                                                          '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
82
                                        timedelta(minutes=timezone_offset)
83
            except ValueError:
84
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
85
                                       description="API.INVALID_BASE_PERIOD_END_DATETIME")
86
87
        if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
88
                base_start_datetime_utc >= base_end_datetime_utc:
89
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
90
                                   description='API.INVALID_BASE_PERIOD_END_DATETIME')
91
92
        if reporting_start_datetime_local is None:
93
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
94
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
95
        else:
96
            reporting_start_datetime_local = str.strip(reporting_start_datetime_local)
97
            try:
98
                reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
99
                                                                 '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
100
                                               timedelta(minutes=timezone_offset)
101
            except ValueError:
102
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
103
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
104
105
        if reporting_end_datetime_local is None:
106
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
107
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
108
        else:
109
            reporting_end_datetime_local = str.strip(reporting_end_datetime_local)
110
            try:
111
                reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
112
                                                               '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
113
                                             timedelta(minutes=timezone_offset)
114
            except ValueError:
115
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
116
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
117
118
        if reporting_start_datetime_utc >= reporting_end_datetime_utc:
119
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
120
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
121
122
        ################################################################################################################
123
        # Step 2: query the store
124
        ################################################################################################################
125
        cnx_system = mysql.connector.connect(**config.myems_system_db)
126
        cursor_system = cnx_system.cursor()
127
128
        cnx_energy = mysql.connector.connect(**config.myems_energy_db)
129
        cursor_energy = cnx_energy.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, area, cost_center_id "
135
                              " FROM tbl_stores "
136
                              " WHERE id = %s ", (store_id,))
137
        row_store = cursor_system.fetchone()
138
        if row_store 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 cnx_historical:
150
                cnx_historical.close()
151
            if cursor_historical:
152
                cursor_historical.disconnect()
153
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.STORE_NOT_FOUND')
154
155
        store = dict()
156
        store['id'] = row_store[0]
157
        store['name'] = row_store[1]
158
        store['area'] = row_store[2]
159
        store['cost_center_id'] = row_store[3]
160
161
        ################################################################################################################
162
        # Step 3: query energy categories
163
        ################################################################################################################
164
        energy_category_set = set()
165
        # query energy categories in base period
166
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
167
                              " FROM tbl_store_input_category_hourly "
168
                              " WHERE store_id = %s "
169
                              "     AND start_datetime_utc >= %s "
170
                              "     AND start_datetime_utc < %s ",
171
                              (store['id'], base_start_datetime_utc, base_end_datetime_utc))
172
        rows_energy_categories = cursor_energy.fetchall()
173
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
174
            for row_energy_category in rows_energy_categories:
175
                energy_category_set.add(row_energy_category[0])
176
177
        # query energy categories in reporting period
178
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
179
                              " FROM tbl_store_input_category_hourly "
180
                              " WHERE store_id = %s "
181
                              "     AND start_datetime_utc >= %s "
182
                              "     AND start_datetime_utc < %s ",
183
                              (store['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
184
        rows_energy_categories = cursor_energy.fetchall()
185
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
186
            for row_energy_category in rows_energy_categories:
187
                energy_category_set.add(row_energy_category[0])
188
189
        # query all energy categories in base period and reporting period
190
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
191
                              " FROM tbl_energy_categories "
192
                              " ORDER BY id ", )
193
        rows_energy_categories = cursor_system.fetchall()
194
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
195
            if cursor_system:
196
                cursor_system.close()
197
            if cnx_system:
198
                cnx_system.disconnect()
199
200
            if cursor_energy:
201
                cursor_energy.close()
202
            if cnx_energy:
203
                cnx_energy.disconnect()
204
205
            if cnx_historical:
206
                cnx_historical.close()
207
            if cursor_historical:
208
                cursor_historical.disconnect()
209
            raise falcon.HTTPError(falcon.HTTP_404,
210
                                   title='API.NOT_FOUND',
211
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
212
        energy_category_dict = dict()
213
        for row_energy_category in rows_energy_categories:
214
            if row_energy_category[0] in energy_category_set:
215
                energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
216
                                                                "unit_of_measure": row_energy_category[2],
217
                                                                "kgce": row_energy_category[3],
218
                                                                "kgco2e": row_energy_category[4]}
219
220
        ################################################################################################################
221
        # Step 4: query associated sensors
222
        ################################################################################################################
223
        point_list = list()
224
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
225
                              " FROM tbl_stores st, tbl_sensors se, tbl_stores_sensors ss, "
226
                              "      tbl_points p, tbl_sensors_points sp "
227
                              " WHERE st.id = %s AND st.id = ss.store_id AND ss.sensor_id = se.id "
228
                              "       AND se.id = sp.sensor_id AND sp.point_id = p.id "
229
                              " ORDER BY p.id ", (store['id'],))
230
        rows_points = cursor_system.fetchall()
231
        if rows_points is not None and len(rows_points) > 0:
232
            for row in rows_points:
233
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
234
235
        ################################################################################################################
236
        # Step 5: query associated points
237
        ################################################################################################################
238
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
239
                              " FROM tbl_stores s, tbl_stores_points sp, tbl_points p "
240
                              " WHERE s.id = %s AND s.id = sp.store_id AND sp.point_id = p.id "
241
                              " ORDER BY p.id ", (store['id'],))
242
        rows_points = cursor_system.fetchall()
243
        if rows_points is not None and len(rows_points) > 0:
244
            for row in rows_points:
245
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
246
247
        ################################################################################################################
248
        # Step 6: query base period energy input
249
        ################################################################################################################
250
        base = dict()
251
        if energy_category_set is not None and len(energy_category_set) > 0:
252
            for energy_category_id in energy_category_set:
253
                base[energy_category_id] = dict()
254
                base[energy_category_id]['timestamps'] = list()
255
                base[energy_category_id]['values'] = list()
256
                base[energy_category_id]['subtotal'] = Decimal(0.0)
257
                base[energy_category_id]['mean'] = None
258
                base[energy_category_id]['median'] = None
259
                base[energy_category_id]['minimum'] = None
260
                base[energy_category_id]['maximum'] = None
261
                base[energy_category_id]['stdev'] = None
262
                base[energy_category_id]['variance'] = None
263
264
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
265
                                      " FROM tbl_store_input_category_hourly "
266
                                      " WHERE store_id = %s "
267
                                      "     AND energy_category_id = %s "
268
                                      "     AND start_datetime_utc >= %s "
269
                                      "     AND start_datetime_utc < %s "
270
                                      " ORDER BY start_datetime_utc ",
271
                                      (store['id'],
272
                                       energy_category_id,
273
                                       base_start_datetime_utc,
274
                                       base_end_datetime_utc))
275
                rows_store_hourly = cursor_energy.fetchall()
276
277
                rows_store_periodically, \
278
                    base[energy_category_id]['mean'], \
279
                    base[energy_category_id]['median'], \
280
                    base[energy_category_id]['minimum'], \
281
                    base[energy_category_id]['maximum'], \
282
                    base[energy_category_id]['stdev'], \
283
                    base[energy_category_id]['variance'] = \
284
                    utilities.statistics_hourly_data_by_period(rows_store_hourly,
285
                                                               base_start_datetime_utc,
286
                                                               base_end_datetime_utc,
287
                                                               period_type)
288
289
                for row_store_periodically in rows_store_periodically:
290
                    current_datetime_local = row_store_periodically[0].replace(tzinfo=timezone.utc) + \
291
                                             timedelta(minutes=timezone_offset)
292
                    if period_type == 'hourly':
293
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
294
                    elif period_type == 'daily':
295
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
296
                    elif period_type == 'monthly':
297
                        current_datetime = current_datetime_local.strftime('%Y-%m')
298
                    elif period_type == 'yearly':
299
                        current_datetime = current_datetime_local.strftime('%Y')
300
301
                    actual_value = Decimal(0.0) if row_store_periodically[1] is None else row_store_periodically[1]
302
                    base[energy_category_id]['timestamps'].append(current_datetime)
303
                    base[energy_category_id]['values'].append(actual_value)
304
                    base[energy_category_id]['subtotal'] += actual_value
305
306
        ################################################################################################################
307
        # Step 7: query reporting period energy input
308
        ################################################################################################################
309
        reporting = dict()
310
        if energy_category_set is not None and len(energy_category_set) > 0:
311
            for energy_category_id in energy_category_set:
312
                reporting[energy_category_id] = dict()
313
                reporting[energy_category_id]['timestamps'] = list()
314
                reporting[energy_category_id]['values'] = list()
315
                reporting[energy_category_id]['subtotal'] = Decimal(0.0)
316
                reporting[energy_category_id]['mean'] = None
317
                reporting[energy_category_id]['median'] = None
318
                reporting[energy_category_id]['minimum'] = None
319
                reporting[energy_category_id]['maximum'] = None
320
                reporting[energy_category_id]['stdev'] = None
321
                reporting[energy_category_id]['variance'] = None
322
323
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
324
                                      " FROM tbl_store_input_category_hourly "
325
                                      " WHERE store_id = %s "
326
                                      "     AND energy_category_id = %s "
327
                                      "     AND start_datetime_utc >= %s "
328
                                      "     AND start_datetime_utc < %s "
329
                                      " ORDER BY start_datetime_utc ",
330
                                      (store['id'],
331
                                       energy_category_id,
332
                                       reporting_start_datetime_utc,
333
                                       reporting_end_datetime_utc))
334
                rows_store_hourly = cursor_energy.fetchall()
335
336
                rows_store_periodically, \
337
                    reporting[energy_category_id]['mean'], \
338
                    reporting[energy_category_id]['median'], \
339
                    reporting[energy_category_id]['minimum'], \
340
                    reporting[energy_category_id]['maximum'], \
341
                    reporting[energy_category_id]['stdev'], \
342
                    reporting[energy_category_id]['variance'] = \
343
                    utilities.statistics_hourly_data_by_period(rows_store_hourly,
344
                                                               reporting_start_datetime_utc,
345
                                                               reporting_end_datetime_utc,
346
                                                               period_type)
347
348
                for row_store_periodically in rows_store_periodically:
349
                    current_datetime_local = row_store_periodically[0].replace(tzinfo=timezone.utc) + \
350
                                             timedelta(minutes=timezone_offset)
351
                    if period_type == 'hourly':
352
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
353
                    elif period_type == 'daily':
354
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
355
                    elif period_type == 'monthly':
356
                        current_datetime = current_datetime_local.strftime('%Y-%m')
357
                    elif period_type == 'yearly':
358
                        current_datetime = current_datetime_local.strftime('%Y')
359
360
                    actual_value = Decimal(0.0) if row_store_periodically[1] is None else row_store_periodically[1]
361
                    reporting[energy_category_id]['timestamps'].append(current_datetime)
362
                    reporting[energy_category_id]['values'].append(actual_value)
363
                    reporting[energy_category_id]['subtotal'] += actual_value
364
365
        ################################################################################################################
366
        # Step 8: query tariff data
367
        ################################################################################################################
368
        parameters_data = dict()
369
        parameters_data['names'] = list()
370
        parameters_data['timestamps'] = list()
371
        parameters_data['values'] = list()
372
        if energy_category_set is not None and len(energy_category_set) > 0:
373
            for energy_category_id in energy_category_set:
374
                energy_category_tariff_dict = utilities.get_energy_category_tariffs(store['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 9: query associated sensors and 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 10: 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['store'] = dict()
471
        result['store']['name'] = store['name']
472
        result['store']['area'] = store['area']
473
474
        result['base_period'] = dict()
475
        result['base_period']['names'] = list()
476
        result['base_period']['units'] = list()
477
        result['base_period']['timestamps'] = list()
478
        result['base_period']['values'] = list()
479
        result['base_period']['subtotals'] = list()
480
        result['base_period']['means'] = list()
481
        result['base_period']['medians'] = list()
482
        result['base_period']['minimums'] = list()
483
        result['base_period']['maximums'] = list()
484
        result['base_period']['stdevs'] = list()
485
        result['base_period']['variances'] = list()
486
487
        if energy_category_set is not None and len(energy_category_set) > 0:
488
            for energy_category_id in energy_category_set:
489
                result['base_period']['names'].append(energy_category_dict[energy_category_id]['name'])
490
                result['base_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
491
                result['base_period']['timestamps'].append(base[energy_category_id]['timestamps'])
492
                result['base_period']['values'].append(base[energy_category_id]['values'])
493
                result['base_period']['subtotals'].append(base[energy_category_id]['subtotal'])
494
                result['base_period']['means'].append(base[energy_category_id]['mean'])
495
                result['base_period']['medians'].append(base[energy_category_id]['median'])
496
                result['base_period']['minimums'].append(base[energy_category_id]['minimum'])
497
                result['base_period']['maximums'].append(base[energy_category_id]['maximum'])
498
                result['base_period']['stdevs'].append(base[energy_category_id]['stdev'])
499
                result['base_period']['variances'].append(base[energy_category_id]['variance'])
500
501
        result['reporting_period'] = dict()
502
        result['reporting_period']['names'] = list()
503
        result['reporting_period']['energy_category_ids'] = list()
504
        result['reporting_period']['units'] = list()
505
        result['reporting_period']['timestamps'] = list()
506
        result['reporting_period']['values'] = list()
507
        result['reporting_period']['subtotals'] = list()
508
        result['reporting_period']['means'] = list()
509
        result['reporting_period']['means_per_unit_area'] = list()
510
        result['reporting_period']['means_increment_rate'] = list()
511
        result['reporting_period']['medians'] = list()
512
        result['reporting_period']['medians_per_unit_area'] = list()
513
        result['reporting_period']['medians_increment_rate'] = list()
514
        result['reporting_period']['minimums'] = list()
515
        result['reporting_period']['minimums_per_unit_area'] = list()
516
        result['reporting_period']['minimums_increment_rate'] = list()
517
        result['reporting_period']['maximums'] = list()
518
        result['reporting_period']['maximums_per_unit_area'] = list()
519
        result['reporting_period']['maximums_increment_rate'] = list()
520
        result['reporting_period']['stdevs'] = list()
521
        result['reporting_period']['stdevs_per_unit_area'] = list()
522
        result['reporting_period']['stdevs_increment_rate'] = list()
523
        result['reporting_period']['variances'] = list()
524
        result['reporting_period']['variances_per_unit_area'] = list()
525
        result['reporting_period']['variances_increment_rate'] = list()
526
527
        if energy_category_set is not None and len(energy_category_set) > 0:
528
            for energy_category_id in energy_category_set:
529
                result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name'])
530
                result['reporting_period']['energy_category_ids'].append(energy_category_id)
531
                result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
532
                result['reporting_period']['timestamps'].append(reporting[energy_category_id]['timestamps'])
533
                result['reporting_period']['values'].append(reporting[energy_category_id]['values'])
534
                result['reporting_period']['subtotals'].append(reporting[energy_category_id]['subtotal'])
535
                result['reporting_period']['means'].append(reporting[energy_category_id]['mean'])
536
                result['reporting_period']['means_per_unit_area'].append(
537
                    reporting[energy_category_id]['mean'] / store['area']
538
                    if reporting[energy_category_id]['mean'] is not None and
539
                    store['area'] is not None and
540
                    store['area'] > Decimal(0.0)
541
                    else None)
542
                result['reporting_period']['means_increment_rate'].append(
543
                    (reporting[energy_category_id]['mean'] - base[energy_category_id]['mean']) /
544
                    base[energy_category_id]['mean'] if (base[energy_category_id]['mean'] is not None and
545
                                                         base[energy_category_id]['mean'] > Decimal(0.0))
546
                    else None)
547
                result['reporting_period']['medians'].append(reporting[energy_category_id]['median'])
548
                result['reporting_period']['medians_per_unit_area'].append(
549
                    reporting[energy_category_id]['median'] / store['area']
550
                    if reporting[energy_category_id]['median'] is not None and
551
                    store['area'] is not None and
552
                    store['area'] > Decimal(0.0)
553
                    else None)
554
                result['reporting_period']['medians_increment_rate'].append(
555
                    (reporting[energy_category_id]['median'] - base[energy_category_id]['median']) /
556
                    base[energy_category_id]['median'] if (base[energy_category_id]['median'] is not None and
557
                                                           base[energy_category_id]['median'] > Decimal(0.0))
558
                    else None)
559
                result['reporting_period']['minimums'].append(reporting[energy_category_id]['minimum'])
560
                result['reporting_period']['minimums_per_unit_area'].append(
561
                    reporting[energy_category_id]['minimum'] / store['area']
562
                    if reporting[energy_category_id]['minimum'] is not None and
563
                    store['area'] is not None and
564
                    store['area'] > Decimal(0.0)
565
                    else None)
566
                result['reporting_period']['minimums_increment_rate'].append(
567
                    (reporting[energy_category_id]['minimum'] - base[energy_category_id]['minimum']) /
568
                    base[energy_category_id]['minimum'] if (base[energy_category_id]['minimum'] is not None and
569
                                                            base[energy_category_id]['minimum'] > Decimal(0.0))
570
                    else None)
571
                result['reporting_period']['maximums'].append(reporting[energy_category_id]['maximum'])
572
                result['reporting_period']['maximums_per_unit_area'].append(
573
                    reporting[energy_category_id]['maximum'] / store['area']
574
                    if reporting[energy_category_id]['maximum'] is not None and
575
                    store['area'] is not None and
576
                    store['area'] > Decimal(0.0)
577
                    else None)
578
                result['reporting_period']['maximums_increment_rate'].append(
579
                    (reporting[energy_category_id]['maximum'] - base[energy_category_id]['maximum']) /
580
                    base[energy_category_id]['maximum'] if (base[energy_category_id]['maximum'] is not None and
581
                                                            base[energy_category_id]['maximum'] > Decimal(0.0))
582
                    else None)
583
                result['reporting_period']['stdevs'].append(reporting[energy_category_id]['stdev'])
584
                result['reporting_period']['stdevs_per_unit_area'].append(
585
                    reporting[energy_category_id]['stdev'] / store['area']
586
                    if reporting[energy_category_id]['stdev'] is not None and
587
                    store['area'] is not None and
588
                    store['area'] > Decimal(0.0)
589
                    else None)
590
                result['reporting_period']['stdevs_increment_rate'].append(
591
                    (reporting[energy_category_id]['stdev'] - base[energy_category_id]['stdev']) /
592
                    base[energy_category_id]['stdev'] if (base[energy_category_id]['stdev'] is not None and
593
                                                          base[energy_category_id]['stdev'] > Decimal(0.0))
594
                    else None)
595
                result['reporting_period']['variances'].append(reporting[energy_category_id]['variance'])
596
                result['reporting_period']['variances_per_unit_area'].append(
597
                    reporting[energy_category_id]['variance'] / store['area']
598
                    if reporting[energy_category_id]['variance'] is not None and
599
                    store['area'] is not None and
600
                    store['area'] > Decimal(0.0)
601
                    else None)
602
                result['reporting_period']['variances_increment_rate'].append(
603
                    (reporting[energy_category_id]['variance'] - base[energy_category_id]['variance']) /
604
                    base[energy_category_id]['variance'] if (base[energy_category_id]['variance'] is not None and
605
                                                             base[energy_category_id]['variance'] > Decimal(0.0))
606
                    else None)
607
608
        result['parameters'] = {
609
            "names": parameters_data['names'],
610
            "timestamps": parameters_data['timestamps'],
611
            "values": parameters_data['values']
612
        }
613
614
        # export result to Excel file and then encode the file to base64 string
615
        result['excel_bytes_base64'] = excelexporters.storestatistics.export(result,
616
                                                                             store['name'],
617
                                                                             reporting_start_datetime_local,
618
                                                                             reporting_end_datetime_local,
619
                                                                             period_type)
620
        resp.body = json.dumps(result)
621

reports/tenantstatistics.py 1 location

@@ 11-619 (lines=609) @@
8
import excelexporters.tenantstatistics
9
10
11
class Reporting:
12
    @staticmethod
13
    def __init__():
14
        pass
15
16
    @staticmethod
17
    def on_options(req, resp):
18
        resp.status = falcon.HTTP_200
19
20
    ####################################################################################################################
21
    # PROCEDURES
22
    # Step 1: valid parameters
23
    # Step 2: query the tenant
24
    # Step 3: query energy categories
25
    # Step 4: query associated sensors
26
    # Step 5: query associated points
27
    # Step 6: query base period energy input
28
    # Step 7: query reporting period energy input
29
    # Step 8: query tariff data
30
    # Step 9: query associated sensors and points data
31
    # Step 10: construct the report
32
    ####################################################################################################################
33
    @staticmethod
34
    def on_get(req, resp):
35
        print(req.params)
36
        tenant_id = req.params.get('tenantid')
37
        period_type = req.params.get('periodtype')
38
        base_start_datetime_local = req.params.get('baseperiodstartdatetime')
39
        base_end_datetime_local = req.params.get('baseperiodenddatetime')
40
        reporting_start_datetime_local = req.params.get('reportingperiodstartdatetime')
41
        reporting_end_datetime_local = req.params.get('reportingperiodenddatetime')
42
43
        ################################################################################################################
44
        # Step 1: valid parameters
45
        ################################################################################################################
46
        if tenant_id is None:
47
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_TENANT_ID')
48
        else:
49
            tenant_id = str.strip(tenant_id)
50
            if not tenant_id.isdigit() or int(tenant_id) <= 0:
51
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_TENANT_ID')
52
53
        if period_type is None:
54
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
55
        else:
56
            period_type = str.strip(period_type)
57
            if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
58
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
59
60
        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
61
        if config.utc_offset[0] == '-':
62
            timezone_offset = -timezone_offset
63
64
        base_start_datetime_utc = None
65
        if base_start_datetime_local is not None and len(str.strip(base_start_datetime_local)) > 0:
66
            base_start_datetime_local = str.strip(base_start_datetime_local)
67
            try:
68
                base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
69
                                                            '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
70
                                          timedelta(minutes=timezone_offset)
71
            except ValueError:
72
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
73
                                       description="API.INVALID_BASE_PERIOD_START_DATETIME")
74
75
        base_end_datetime_utc = None
76
        if base_end_datetime_local is not None and len(str.strip(base_end_datetime_local)) > 0:
77
            base_end_datetime_local = str.strip(base_end_datetime_local)
78
            try:
79
                base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
80
                                                          '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
81
                                        timedelta(minutes=timezone_offset)
82
            except ValueError:
83
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
84
                                       description="API.INVALID_BASE_PERIOD_END_DATETIME")
85
86
        if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
87
                base_start_datetime_utc >= base_end_datetime_utc:
88
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
89
                                   description='API.INVALID_BASE_PERIOD_END_DATETIME')
90
91
        if reporting_start_datetime_local is None:
92
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
93
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
94
        else:
95
            reporting_start_datetime_local = str.strip(reporting_start_datetime_local)
96
            try:
97
                reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
98
                                                                 '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
99
                                               timedelta(minutes=timezone_offset)
100
            except ValueError:
101
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
102
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
103
104
        if reporting_end_datetime_local is None:
105
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
106
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
107
        else:
108
            reporting_end_datetime_local = str.strip(reporting_end_datetime_local)
109
            try:
110
                reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
111
                                                               '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
112
                                             timedelta(minutes=timezone_offset)
113
            except ValueError:
114
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
115
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
116
117
        if reporting_start_datetime_utc >= reporting_end_datetime_utc:
118
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
119
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
120
121
        ################################################################################################################
122
        # Step 2: query the tenant
123
        ################################################################################################################
124
        cnx_system = mysql.connector.connect(**config.myems_system_db)
125
        cursor_system = cnx_system.cursor()
126
127
        cnx_energy = mysql.connector.connect(**config.myems_energy_db)
128
        cursor_energy = cnx_energy.cursor()
129
130
        cnx_historical = mysql.connector.connect(**config.myems_historical_db)
131
        cursor_historical = cnx_historical.cursor()
132
133
        cursor_system.execute(" SELECT id, name, area, cost_center_id "
134
                              " FROM tbl_tenants "
135
                              " WHERE id = %s ", (tenant_id,))
136
        row_tenant = cursor_system.fetchone()
137
        if row_tenant is None:
138
            if cursor_system:
139
                cursor_system.close()
140
            if cnx_system:
141
                cnx_system.disconnect()
142
143
            if cursor_energy:
144
                cursor_energy.close()
145
            if cnx_energy:
146
                cnx_energy.disconnect()
147
148
            if cnx_historical:
149
                cnx_historical.close()
150
            if cursor_historical:
151
                cursor_historical.disconnect()
152
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.TENANT_NOT_FOUND')
153
154
        tenant = dict()
155
        tenant['id'] = row_tenant[0]
156
        tenant['name'] = row_tenant[1]
157
        tenant['area'] = row_tenant[2]
158
        tenant['cost_center_id'] = row_tenant[3]
159
160
        ################################################################################################################
161
        # Step 3: query energy categories
162
        ################################################################################################################
163
        energy_category_set = set()
164
        # query energy categories in base period
165
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
166
                              " FROM tbl_tenant_input_category_hourly "
167
                              " WHERE tenant_id = %s "
168
                              "     AND start_datetime_utc >= %s "
169
                              "     AND start_datetime_utc < %s ",
170
                              (tenant['id'], base_start_datetime_utc, base_end_datetime_utc))
171
        rows_energy_categories = cursor_energy.fetchall()
172
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
173
            for row_energy_category in rows_energy_categories:
174
                energy_category_set.add(row_energy_category[0])
175
176
        # query energy categories in reporting period
177
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
178
                              " FROM tbl_tenant_input_category_hourly "
179
                              " WHERE tenant_id = %s "
180
                              "     AND start_datetime_utc >= %s "
181
                              "     AND start_datetime_utc < %s ",
182
                              (tenant['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
183
        rows_energy_categories = cursor_energy.fetchall()
184
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
185
            for row_energy_category in rows_energy_categories:
186
                energy_category_set.add(row_energy_category[0])
187
188
        # query all energy categories in base period and reporting period
189
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
190
                              " FROM tbl_energy_categories "
191
                              " ORDER BY id ", )
192
        rows_energy_categories = cursor_system.fetchall()
193
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
194
            if cursor_system:
195
                cursor_system.close()
196
            if cnx_system:
197
                cnx_system.disconnect()
198
199
            if cursor_energy:
200
                cursor_energy.close()
201
            if cnx_energy:
202
                cnx_energy.disconnect()
203
204
            if cnx_historical:
205
                cnx_historical.close()
206
            if cursor_historical:
207
                cursor_historical.disconnect()
208
            raise falcon.HTTPError(falcon.HTTP_404,
209
                                   title='API.NOT_FOUND',
210
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
211
        energy_category_dict = dict()
212
        for row_energy_category in rows_energy_categories:
213
            if row_energy_category[0] in energy_category_set:
214
                energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
215
                                                                "unit_of_measure": row_energy_category[2],
216
                                                                "kgce": row_energy_category[3],
217
                                                                "kgco2e": row_energy_category[4]}
218
219
        ################################################################################################################
220
        # Step 4: query associated sensors
221
        ################################################################################################################
222
        point_list = list()
223
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
224
                              " FROM tbl_tenants t, tbl_sensors s, tbl_tenants_sensors ts, "
225
                              "      tbl_points p, tbl_sensors_points sp "
226
                              " WHERE t.id = %s AND t.id = ts.tenant_id AND ts.sensor_id = s.id "
227
                              "       AND s.id = sp.sensor_id AND sp.point_id = p.id "
228
                              " ORDER BY p.id ", (tenant['id'],))
229
        rows_points = cursor_system.fetchall()
230
        if rows_points is not None and len(rows_points) > 0:
231
            for row in rows_points:
232
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
233
234
        ################################################################################################################
235
        # Step 5: query associated points
236
        ################################################################################################################
237
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
238
                              " FROM tbl_tenants t, tbl_tenants_points tp, tbl_points p "
239
                              " WHERE t.id = %s AND t.id = tp.tenant_id AND tp.point_id = p.id "
240
                              " ORDER BY p.id ", (tenant['id'],))
241
        rows_points = cursor_system.fetchall()
242
        if rows_points is not None and len(rows_points) > 0:
243
            for row in rows_points:
244
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
245
246
        ################################################################################################################
247
        # Step 6: query base period energy input
248
        ################################################################################################################
249
        base = dict()
250
        if energy_category_set is not None and len(energy_category_set) > 0:
251
            for energy_category_id in energy_category_set:
252
                base[energy_category_id] = dict()
253
                base[energy_category_id]['timestamps'] = list()
254
                base[energy_category_id]['values'] = list()
255
                base[energy_category_id]['subtotal'] = Decimal(0.0)
256
                base[energy_category_id]['mean'] = None
257
                base[energy_category_id]['median'] = None
258
                base[energy_category_id]['minimum'] = None
259
                base[energy_category_id]['maximum'] = None
260
                base[energy_category_id]['stdev'] = None
261
                base[energy_category_id]['variance'] = None
262
263
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
264
                                      " FROM tbl_tenant_input_category_hourly "
265
                                      " WHERE tenant_id = %s "
266
                                      "     AND energy_category_id = %s "
267
                                      "     AND start_datetime_utc >= %s "
268
                                      "     AND start_datetime_utc < %s "
269
                                      " ORDER BY start_datetime_utc ",
270
                                      (tenant['id'],
271
                                       energy_category_id,
272
                                       base_start_datetime_utc,
273
                                       base_end_datetime_utc))
274
                rows_tenant_hourly = cursor_energy.fetchall()
275
276
                rows_tenant_periodically, \
277
                    base[energy_category_id]['mean'], \
278
                    base[energy_category_id]['median'], \
279
                    base[energy_category_id]['minimum'], \
280
                    base[energy_category_id]['maximum'], \
281
                    base[energy_category_id]['stdev'], \
282
                    base[energy_category_id]['variance'] = \
283
                    utilities.statistics_hourly_data_by_period(rows_tenant_hourly,
284
                                                               base_start_datetime_utc,
285
                                                               base_end_datetime_utc,
286
                                                               period_type)
287
288
                for row_tenant_periodically in rows_tenant_periodically:
289
                    current_datetime_local = row_tenant_periodically[0].replace(tzinfo=timezone.utc) + \
290
                                             timedelta(minutes=timezone_offset)
291
                    if period_type == 'hourly':
292
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
293
                    elif period_type == 'daily':
294
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
295
                    elif period_type == 'monthly':
296
                        current_datetime = current_datetime_local.strftime('%Y-%m')
297
                    elif period_type == 'yearly':
298
                        current_datetime = current_datetime_local.strftime('%Y')
299
300
                    actual_value = Decimal(0.0) if row_tenant_periodically[1] is None else row_tenant_periodically[1]
301
                    base[energy_category_id]['timestamps'].append(current_datetime)
302
                    base[energy_category_id]['values'].append(actual_value)
303
                    base[energy_category_id]['subtotal'] += actual_value
304
305
        ################################################################################################################
306
        # Step 7: query reporting period energy input
307
        ################################################################################################################
308
        reporting = dict()
309
        if energy_category_set is not None and len(energy_category_set) > 0:
310
            for energy_category_id in energy_category_set:
311
                reporting[energy_category_id] = dict()
312
                reporting[energy_category_id]['timestamps'] = list()
313
                reporting[energy_category_id]['values'] = list()
314
                reporting[energy_category_id]['subtotal'] = Decimal(0.0)
315
                reporting[energy_category_id]['mean'] = None
316
                reporting[energy_category_id]['median'] = None
317
                reporting[energy_category_id]['minimum'] = None
318
                reporting[energy_category_id]['maximum'] = None
319
                reporting[energy_category_id]['stdev'] = None
320
                reporting[energy_category_id]['variance'] = None
321
322
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
323
                                      " FROM tbl_tenant_input_category_hourly "
324
                                      " WHERE tenant_id = %s "
325
                                      "     AND energy_category_id = %s "
326
                                      "     AND start_datetime_utc >= %s "
327
                                      "     AND start_datetime_utc < %s "
328
                                      " ORDER BY start_datetime_utc ",
329
                                      (tenant['id'],
330
                                       energy_category_id,
331
                                       reporting_start_datetime_utc,
332
                                       reporting_end_datetime_utc))
333
                rows_tenant_hourly = cursor_energy.fetchall()
334
335
                rows_tenant_periodically, \
336
                    reporting[energy_category_id]['mean'], \
337
                    reporting[energy_category_id]['median'], \
338
                    reporting[energy_category_id]['minimum'], \
339
                    reporting[energy_category_id]['maximum'], \
340
                    reporting[energy_category_id]['stdev'], \
341
                    reporting[energy_category_id]['variance'] = \
342
                    utilities.statistics_hourly_data_by_period(rows_tenant_hourly,
343
                                                               reporting_start_datetime_utc,
344
                                                               reporting_end_datetime_utc,
345
                                                               period_type)
346
347
                for row_tenant_periodically in rows_tenant_periodically:
348
                    current_datetime_local = row_tenant_periodically[0].replace(tzinfo=timezone.utc) + \
349
                                             timedelta(minutes=timezone_offset)
350
                    if period_type == 'hourly':
351
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
352
                    elif period_type == 'daily':
353
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
354
                    elif period_type == 'monthly':
355
                        current_datetime = current_datetime_local.strftime('%Y-%m')
356
                    elif period_type == 'yearly':
357
                        current_datetime = current_datetime_local.strftime('%Y')
358
359
                    actual_value = Decimal(0.0) if row_tenant_periodically[1] is None else row_tenant_periodically[1]
360
                    reporting[energy_category_id]['timestamps'].append(current_datetime)
361
                    reporting[energy_category_id]['values'].append(actual_value)
362
                    reporting[energy_category_id]['subtotal'] += actual_value
363
364
        ################################################################################################################
365
        # Step 8: 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 = utilities.get_energy_category_tariffs(tenant['cost_center_id'],
374
                                                                                    energy_category_id,
375
                                                                                    reporting_start_datetime_utc,
376
                                                                                    reporting_end_datetime_utc)
377
                tariff_timestamp_list = list()
378
                tariff_value_list = list()
379
                for k, v in energy_category_tariff_dict.items():
380
                    # convert k from utc to local
381
                    k = k + timedelta(minutes=timezone_offset)
382
                    tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
383
                    tariff_value_list.append(v)
384
385
                parameters_data['names'].append('TARIFF-' + energy_category_dict[energy_category_id]['name'])
386
                parameters_data['timestamps'].append(tariff_timestamp_list)
387
                parameters_data['values'].append(tariff_value_list)
388
389
        ################################################################################################################
390
        # Step 9: query associated sensors and points data
391
        ################################################################################################################
392
        for point in point_list:
393
            point_values = []
394
            point_timestamps = []
395
            if point['object_type'] == 'ANALOG_VALUE':
396
                query = (" SELECT utc_date_time, actual_value "
397
                         " FROM tbl_analog_value "
398
                         " WHERE point_id = %s "
399
                         "       AND utc_date_time BETWEEN %s AND %s "
400
                         " ORDER BY utc_date_time ")
401
                cursor_historical.execute(query, (point['id'],
402
                                                  reporting_start_datetime_utc,
403
                                                  reporting_end_datetime_utc))
404
                rows = cursor_historical.fetchall()
405
406
                if rows is not None and len(rows) > 0:
407
                    for row in rows:
408
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
409
                                                 timedelta(minutes=timezone_offset)
410
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
411
                        point_timestamps.append(current_datetime)
412
                        point_values.append(row[1])
413
414
            elif point['object_type'] == 'ENERGY_VALUE':
415
                query = (" SELECT utc_date_time, actual_value "
416
                         " FROM tbl_energy_value "
417
                         " WHERE point_id = %s "
418
                         "       AND utc_date_time BETWEEN %s AND %s "
419
                         " ORDER BY utc_date_time ")
420
                cursor_historical.execute(query, (point['id'],
421
                                                  reporting_start_datetime_utc,
422
                                                  reporting_end_datetime_utc))
423
                rows = cursor_historical.fetchall()
424
425
                if rows is not None and len(rows) > 0:
426
                    for row in rows:
427
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
428
                                                 timedelta(minutes=timezone_offset)
429
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
430
                        point_timestamps.append(current_datetime)
431
                        point_values.append(row[1])
432
            elif point['object_type'] == 'DIGITAL_VALUE':
433
                query = (" SELECT utc_date_time, actual_value "
434
                         " FROM tbl_digital_value "
435
                         " WHERE point_id = %s "
436
                         "       AND utc_date_time BETWEEN %s AND %s ")
437
                cursor_historical.execute(query, (point['id'],
438
                                                  reporting_start_datetime_utc,
439
                                                  reporting_end_datetime_utc))
440
                rows = cursor_historical.fetchall()
441
442
                if rows is not None and len(rows) > 0:
443
                    for row in rows:
444
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
445
                                                 timedelta(minutes=timezone_offset)
446
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
447
                        point_timestamps.append(current_datetime)
448
                        point_values.append(row[1])
449
450
            parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
451
            parameters_data['timestamps'].append(point_timestamps)
452
            parameters_data['values'].append(point_values)
453
454
        ################################################################################################################
455
        # Step 10: construct the report
456
        ################################################################################################################
457
        if cursor_system:
458
            cursor_system.close()
459
        if cnx_system:
460
            cnx_system.disconnect()
461
462
        if cursor_energy:
463
            cursor_energy.close()
464
        if cnx_energy:
465
            cnx_energy.disconnect()
466
467
        result = dict()
468
469
        result['tenant'] = dict()
470
        result['tenant']['name'] = tenant['name']
471
        result['tenant']['area'] = tenant['area']
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']['means'] = list()
480
        result['base_period']['medians'] = list()
481
        result['base_period']['minimums'] = list()
482
        result['base_period']['maximums'] = list()
483
        result['base_period']['stdevs'] = list()
484
        result['base_period']['variances'] = list()
485
486
        if energy_category_set is not None and len(energy_category_set) > 0:
487
            for energy_category_id in energy_category_set:
488
                result['base_period']['names'].append(energy_category_dict[energy_category_id]['name'])
489
                result['base_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
490
                result['base_period']['timestamps'].append(base[energy_category_id]['timestamps'])
491
                result['base_period']['values'].append(base[energy_category_id]['values'])
492
                result['base_period']['subtotals'].append(base[energy_category_id]['subtotal'])
493
                result['base_period']['means'].append(base[energy_category_id]['mean'])
494
                result['base_period']['medians'].append(base[energy_category_id]['median'])
495
                result['base_period']['minimums'].append(base[energy_category_id]['minimum'])
496
                result['base_period']['maximums'].append(base[energy_category_id]['maximum'])
497
                result['base_period']['stdevs'].append(base[energy_category_id]['stdev'])
498
                result['base_period']['variances'].append(base[energy_category_id]['variance'])
499
500
        result['reporting_period'] = dict()
501
        result['reporting_period']['names'] = list()
502
        result['reporting_period']['energy_category_ids'] = list()
503
        result['reporting_period']['units'] = list()
504
        result['reporting_period']['timestamps'] = list()
505
        result['reporting_period']['values'] = list()
506
        result['reporting_period']['subtotals'] = list()
507
        result['reporting_period']['means'] = list()
508
        result['reporting_period']['means_per_unit_area'] = list()
509
        result['reporting_period']['means_increment_rate'] = list()
510
        result['reporting_period']['medians'] = list()
511
        result['reporting_period']['medians_per_unit_area'] = list()
512
        result['reporting_period']['medians_increment_rate'] = list()
513
        result['reporting_period']['minimums'] = list()
514
        result['reporting_period']['minimums_per_unit_area'] = list()
515
        result['reporting_period']['minimums_increment_rate'] = list()
516
        result['reporting_period']['maximums'] = list()
517
        result['reporting_period']['maximums_per_unit_area'] = list()
518
        result['reporting_period']['maximums_increment_rate'] = list()
519
        result['reporting_period']['stdevs'] = list()
520
        result['reporting_period']['stdevs_per_unit_area'] = list()
521
        result['reporting_period']['stdevs_increment_rate'] = list()
522
        result['reporting_period']['variances'] = list()
523
        result['reporting_period']['variances_per_unit_area'] = list()
524
        result['reporting_period']['variances_increment_rate'] = list()
525
526
        if energy_category_set is not None and len(energy_category_set) > 0:
527
            for energy_category_id in energy_category_set:
528
                result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name'])
529
                result['reporting_period']['energy_category_ids'].append(energy_category_id)
530
                result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
531
                result['reporting_period']['timestamps'].append(reporting[energy_category_id]['timestamps'])
532
                result['reporting_period']['values'].append(reporting[energy_category_id]['values'])
533
                result['reporting_period']['subtotals'].append(reporting[energy_category_id]['subtotal'])
534
                result['reporting_period']['means'].append(reporting[energy_category_id]['mean'])
535
                result['reporting_period']['means_per_unit_area'].append(
536
                    reporting[energy_category_id]['mean'] / tenant['area']
537
                    if reporting[energy_category_id]['mean'] is not None and
538
                    tenant['area'] is not None and
539
                    tenant['area'] > Decimal(0.0)
540
                    else None)
541
                result['reporting_period']['means_increment_rate'].append(
542
                    (reporting[energy_category_id]['mean'] - base[energy_category_id]['mean']) /
543
                    base[energy_category_id]['mean'] if (base[energy_category_id]['mean'] is not None and
544
                                                         base[energy_category_id]['mean'] > Decimal(0.0))
545
                    else None)
546
                result['reporting_period']['medians'].append(reporting[energy_category_id]['median'])
547
                result['reporting_period']['medians_per_unit_area'].append(
548
                    reporting[energy_category_id]['median'] / tenant['area']
549
                    if reporting[energy_category_id]['median'] is not None and
550
                    tenant['area'] is not None and
551
                    tenant['area'] > Decimal(0.0)
552
                    else None)
553
                result['reporting_period']['medians_increment_rate'].append(
554
                    (reporting[energy_category_id]['median'] - base[energy_category_id]['median']) /
555
                    base[energy_category_id]['median'] if (base[energy_category_id]['median'] is not None and
556
                                                           base[energy_category_id]['median'] > Decimal(0.0))
557
                    else None)
558
                result['reporting_period']['minimums'].append(reporting[energy_category_id]['minimum'])
559
                result['reporting_period']['minimums_per_unit_area'].append(
560
                    reporting[energy_category_id]['minimum'] / tenant['area']
561
                    if reporting[energy_category_id]['minimum'] is not None and
562
                    tenant['area'] is not None and
563
                    tenant['area'] > Decimal(0.0)
564
                    else None)
565
                result['reporting_period']['minimums_increment_rate'].append(
566
                    (reporting[energy_category_id]['minimum'] - base[energy_category_id]['minimum']) /
567
                    base[energy_category_id]['minimum'] if (base[energy_category_id]['minimum'] is not None and
568
                                                            base[energy_category_id]['minimum'] > Decimal(0.0))
569
                    else None)
570
                result['reporting_period']['maximums'].append(reporting[energy_category_id]['maximum'])
571
                result['reporting_period']['maximums_per_unit_area'].append(
572
                    reporting[energy_category_id]['maximum'] / tenant['area']
573
                    if reporting[energy_category_id]['maximum'] is not None and
574
                    tenant['area'] is not None and
575
                    tenant['area'] > Decimal(0.0)
576
                    else None)
577
                result['reporting_period']['maximums_increment_rate'].append(
578
                    (reporting[energy_category_id]['maximum'] - base[energy_category_id]['maximum']) /
579
                    base[energy_category_id]['maximum'] if (base[energy_category_id]['maximum'] is not None and
580
                                                            base[energy_category_id]['maximum'] > Decimal(0.0))
581
                    else None)
582
                result['reporting_period']['stdevs'].append(reporting[energy_category_id]['stdev'])
583
                result['reporting_period']['stdevs_per_unit_area'].append(
584
                    reporting[energy_category_id]['stdev'] / tenant['area']
585
                    if reporting[energy_category_id]['stdev'] is not None and
586
                    tenant['area'] is not None and
587
                    tenant['area'] > Decimal(0.0)
588
                    else None)
589
                result['reporting_period']['stdevs_increment_rate'].append(
590
                    (reporting[energy_category_id]['stdev'] - base[energy_category_id]['stdev']) /
591
                    base[energy_category_id]['stdev'] if (base[energy_category_id]['stdev'] is not None and
592
                                                          base[energy_category_id]['stdev'] > Decimal(0.0))
593
                    else None)
594
                result['reporting_period']['variances'].append(reporting[energy_category_id]['variance'])
595
                result['reporting_period']['variances_per_unit_area'].append(
596
                    reporting[energy_category_id]['variance'] / tenant['area']
597
                    if reporting[energy_category_id]['variance'] is not None and
598
                    tenant['area'] is not None and
599
                    tenant['area'] > Decimal(0.0)
600
                    else None)
601
                result['reporting_period']['variances_increment_rate'].append(
602
                    (reporting[energy_category_id]['variance'] - base[energy_category_id]['variance']) /
603
                    base[energy_category_id]['variance'] if (base[energy_category_id]['variance'] is not None and
604
                                                             base[energy_category_id]['variance'] > Decimal(0.0))
605
                    else None)
606
607
        result['parameters'] = {
608
            "names": parameters_data['names'],
609
            "timestamps": parameters_data['timestamps'],
610
            "values": parameters_data['values']
611
        }
612
        # export result to Excel file and then encode the file to base64 string
613
        result['excel_bytes_base64'] = excelexporters.tenantstatistics.export(result,
614
                                                                              tenant['name'],
615
                                                                              reporting_start_datetime_local,
616
                                                                              reporting_end_datetime_local,
617
                                                                              period_type)
618
619
        resp.body = json.dumps(result)
620

reports/spacestatistics.py 1 location

@@ 11-619 (lines=609) @@
8
import excelexporters.spacestatistics
9
10
11
class Reporting:
12
    @staticmethod
13
    def __init__():
14
        pass
15
16
    @staticmethod
17
    def on_options(req, resp):
18
        resp.status = falcon.HTTP_200
19
20
    ####################################################################################################################
21
    # PROCEDURES
22
    # Step 1: valid parameters
23
    # Step 2: query the space
24
    # Step 3: query energy categories
25
    # Step 4: query associated sensors
26
    # Step 5: query associated points
27
    # Step 6: query base period energy input
28
    # Step 7: query reporting period energy input
29
    # Step 8: query tariff data
30
    # Step 9: query associated sensors and points data
31
    # Step 10: construct the report
32
    ####################################################################################################################
33
    @staticmethod
34
    def on_get(req, resp):
35
        print(req.params)
36
        space_id = req.params.get('spaceid')
37
        period_type = req.params.get('periodtype')
38
        base_start_datetime_local = req.params.get('baseperiodstartdatetime')
39
        base_end_datetime_local = req.params.get('baseperiodenddatetime')
40
        reporting_start_datetime_local = req.params.get('reportingperiodstartdatetime')
41
        reporting_end_datetime_local = req.params.get('reportingperiodenddatetime')
42
43
        ################################################################################################################
44
        # Step 1: valid parameters
45
        ################################################################################################################
46
        if space_id is None:
47
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
48
        else:
49
            space_id = str.strip(space_id)
50
            if not space_id.isdigit() or int(space_id) <= 0:
51
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
52
53
        if period_type is None:
54
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
55
        else:
56
            period_type = str.strip(period_type)
57
            if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
58
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
59
60
        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
61
        if config.utc_offset[0] == '-':
62
            timezone_offset = -timezone_offset
63
64
        base_start_datetime_utc = None
65
        if base_start_datetime_local is not None and len(str.strip(base_start_datetime_local)) > 0:
66
            base_start_datetime_local = str.strip(base_start_datetime_local)
67
            try:
68
                base_start_datetime_utc = datetime.strptime(base_start_datetime_local,
69
                                                            '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
70
                    timedelta(minutes=timezone_offset)
71
            except ValueError:
72
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
73
                                       description="API.INVALID_BASE_PERIOD_START_DATETIME")
74
75
        base_end_datetime_utc = None
76
        if base_end_datetime_local is not None and len(str.strip(base_end_datetime_local)) > 0:
77
            base_end_datetime_local = str.strip(base_end_datetime_local)
78
            try:
79
                base_end_datetime_utc = datetime.strptime(base_end_datetime_local,
80
                                                          '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
81
                    timedelta(minutes=timezone_offset)
82
            except ValueError:
83
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
84
                                       description="API.INVALID_BASE_PERIOD_END_DATETIME")
85
86
        if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
87
                base_start_datetime_utc >= base_end_datetime_utc:
88
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
89
                                   description='API.INVALID_BASE_PERIOD_END_DATETIME')
90
91
        if reporting_start_datetime_local is None:
92
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
93
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
94
        else:
95
            reporting_start_datetime_local = str.strip(reporting_start_datetime_local)
96
            try:
97
                reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
98
                                                                 '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
99
                    timedelta(minutes=timezone_offset)
100
            except ValueError:
101
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
102
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
103
104
        if reporting_end_datetime_local is None:
105
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
106
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
107
        else:
108
            reporting_end_datetime_local = str.strip(reporting_end_datetime_local)
109
            try:
110
                reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
111
                                                               '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
112
                    timedelta(minutes=timezone_offset)
113
            except ValueError:
114
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
115
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
116
117
        if reporting_start_datetime_utc >= reporting_end_datetime_utc:
118
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
119
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
120
121
        ################################################################################################################
122
        # Step 2: query the space
123
        ################################################################################################################
124
        cnx_system = mysql.connector.connect(**config.myems_system_db)
125
        cursor_system = cnx_system.cursor()
126
127
        cnx_energy = mysql.connector.connect(**config.myems_energy_db)
128
        cursor_energy = cnx_energy.cursor()
129
130
        cnx_historical = mysql.connector.connect(**config.myems_historical_db)
131
        cursor_historical = cnx_historical.cursor()
132
133
        cursor_system.execute(" SELECT id, name, area, cost_center_id "
134
                              " FROM tbl_spaces "
135
                              " WHERE id = %s ", (space_id,))
136
        row_space = cursor_system.fetchone()
137
        if row_space is None:
138
            if cursor_system:
139
                cursor_system.close()
140
            if cnx_system:
141
                cnx_system.disconnect()
142
143
            if cursor_energy:
144
                cursor_energy.close()
145
            if cnx_energy:
146
                cnx_energy.disconnect()
147
148
            if cnx_historical:
149
                cnx_historical.close()
150
            if cursor_historical:
151
                cursor_historical.disconnect()
152
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.SPACE_NOT_FOUND')
153
154
        space = dict()
155
        space['id'] = row_space[0]
156
        space['name'] = row_space[1]
157
        space['area'] = row_space[2]
158
        space['cost_center_id'] = row_space[3]
159
160
        ################################################################################################################
161
        # Step 3: query energy categories
162
        ################################################################################################################
163
        energy_category_set = set()
164
        # query energy categories in base period
165
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
166
                              " FROM tbl_space_input_category_hourly "
167
                              " WHERE space_id = %s "
168
                              "     AND start_datetime_utc >= %s "
169
                              "     AND start_datetime_utc < %s ",
170
                              (space['id'], base_start_datetime_utc, base_end_datetime_utc))
171
        rows_energy_categories = cursor_energy.fetchall()
172
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
173
            for row_energy_category in rows_energy_categories:
174
                energy_category_set.add(row_energy_category[0])
175
176
        # query energy categories in reporting period
177
        cursor_energy.execute(" SELECT DISTINCT(energy_category_id) "
178
                              " FROM tbl_space_input_category_hourly "
179
                              " WHERE space_id = %s "
180
                              "     AND start_datetime_utc >= %s "
181
                              "     AND start_datetime_utc < %s ",
182
                              (space['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
183
        rows_energy_categories = cursor_energy.fetchall()
184
        if rows_energy_categories is not None or len(rows_energy_categories) > 0:
185
            for row_energy_category in rows_energy_categories:
186
                energy_category_set.add(row_energy_category[0])
187
188
        # query all energy categories in base period and reporting period
189
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
190
                              " FROM tbl_energy_categories "
191
                              " ORDER BY id ", )
192
        rows_energy_categories = cursor_system.fetchall()
193
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
194
            if cursor_system:
195
                cursor_system.close()
196
            if cnx_system:
197
                cnx_system.disconnect()
198
199
            if cursor_energy:
200
                cursor_energy.close()
201
            if cnx_energy:
202
                cnx_energy.disconnect()
203
204
            if cnx_historical:
205
                cnx_historical.close()
206
            if cursor_historical:
207
                cursor_historical.disconnect()
208
            raise falcon.HTTPError(falcon.HTTP_404,
209
                                   title='API.NOT_FOUND',
210
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
211
        energy_category_dict = dict()
212
        for row_energy_category in rows_energy_categories:
213
            if row_energy_category[0] in energy_category_set:
214
                energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
215
                                                                "unit_of_measure": row_energy_category[2],
216
                                                                "kgce": row_energy_category[3],
217
                                                                "kgco2e": row_energy_category[4]}
218
219
        ################################################################################################################
220
        # Step 4: query associated sensors
221
        ################################################################################################################
222
        point_list = list()
223
        cursor_system.execute(" SELECT po.id, po.name, po.units, po.object_type  "
224
                              " FROM tbl_spaces sp, tbl_sensors se, tbl_spaces_sensors spse, "
225
                              "      tbl_points po, tbl_sensors_points sepo "
226
                              " WHERE sp.id = %s AND sp.id = spse.space_id AND spse.sensor_id = se.id "
227
                              "       AND se.id = sepo.sensor_id AND sepo.point_id = po.id "
228
                              " ORDER BY po.id ", (space['id'], ))
229
        rows_points = cursor_system.fetchall()
230
        if rows_points is not None and len(rows_points) > 0:
231
            for row in rows_points:
232
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
233
234
        ################################################################################################################
235
        # Step 5: query associated points
236
        ################################################################################################################
237
        cursor_system.execute(" SELECT po.id, po.name, po.units, po.object_type  "
238
                              " FROM tbl_spaces sp, tbl_spaces_points sppo, tbl_points po "
239
                              " WHERE sp.id = %s AND sp.id = sppo.space_id AND sppo.point_id = po.id "
240
                              " ORDER BY po.id ", (space['id'], ))
241
        rows_points = cursor_system.fetchall()
242
        if rows_points is not None and len(rows_points) > 0:
243
            for row in rows_points:
244
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
245
246
        ################################################################################################################
247
        # Step 6: query base period energy input
248
        ################################################################################################################
249
        base = dict()
250
        if energy_category_set is not None and len(energy_category_set) > 0:
251
            for energy_category_id in energy_category_set:
252
                base[energy_category_id] = dict()
253
                base[energy_category_id]['timestamps'] = list()
254
                base[energy_category_id]['values'] = list()
255
                base[energy_category_id]['subtotal'] = Decimal(0.0)
256
                base[energy_category_id]['mean'] = None
257
                base[energy_category_id]['median'] = None
258
                base[energy_category_id]['minimum'] = None
259
                base[energy_category_id]['maximum'] = None
260
                base[energy_category_id]['stdev'] = None
261
                base[energy_category_id]['variance'] = None
262
263
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
264
                                      " FROM tbl_space_input_category_hourly "
265
                                      " WHERE space_id = %s "
266
                                      "     AND energy_category_id = %s "
267
                                      "     AND start_datetime_utc >= %s "
268
                                      "     AND start_datetime_utc < %s "
269
                                      " ORDER BY start_datetime_utc ",
270
                                      (space['id'],
271
                                       energy_category_id,
272
                                       base_start_datetime_utc,
273
                                       base_end_datetime_utc))
274
                rows_space_hourly = cursor_energy.fetchall()
275
276
                rows_space_periodically, \
277
                    base[energy_category_id]['mean'], \
278
                    base[energy_category_id]['median'], \
279
                    base[energy_category_id]['minimum'], \
280
                    base[energy_category_id]['maximum'], \
281
                    base[energy_category_id]['stdev'], \
282
                    base[energy_category_id]['variance'] = \
283
                    utilities.statistics_hourly_data_by_period(rows_space_hourly,
284
                                                               base_start_datetime_utc,
285
                                                               base_end_datetime_utc,
286
                                                               period_type)
287
288
                for row_space_periodically in rows_space_periodically:
289
                    current_datetime_local = row_space_periodically[0].replace(tzinfo=timezone.utc) + \
290
                                             timedelta(minutes=timezone_offset)
291
                    if period_type == 'hourly':
292
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
293
                    elif period_type == 'daily':
294
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
295
                    elif period_type == 'monthly':
296
                        current_datetime = current_datetime_local.strftime('%Y-%m')
297
                    elif period_type == 'yearly':
298
                        current_datetime = current_datetime_local.strftime('%Y')
299
300
                    actual_value = Decimal(0.0) if row_space_periodically[1] is None else row_space_periodically[1]
301
                    base[energy_category_id]['timestamps'].append(current_datetime)
302
                    base[energy_category_id]['values'].append(actual_value)
303
                    base[energy_category_id]['subtotal'] += actual_value
304
305
        ################################################################################################################
306
        # Step 7: query reporting period energy input
307
        ################################################################################################################
308
        reporting = dict()
309
        if energy_category_set is not None and len(energy_category_set) > 0:
310
            for energy_category_id in energy_category_set:
311
                reporting[energy_category_id] = dict()
312
                reporting[energy_category_id]['timestamps'] = list()
313
                reporting[energy_category_id]['values'] = list()
314
                reporting[energy_category_id]['subtotal'] = Decimal(0.0)
315
                reporting[energy_category_id]['mean'] = None
316
                reporting[energy_category_id]['median'] = None
317
                reporting[energy_category_id]['minimum'] = None
318
                reporting[energy_category_id]['maximum'] = None
319
                reporting[energy_category_id]['stdev'] = None
320
                reporting[energy_category_id]['variance'] = None
321
322
                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
323
                                      " FROM tbl_space_input_category_hourly "
324
                                      " WHERE space_id = %s "
325
                                      "     AND energy_category_id = %s "
326
                                      "     AND start_datetime_utc >= %s "
327
                                      "     AND start_datetime_utc < %s "
328
                                      " ORDER BY start_datetime_utc ",
329
                                      (space['id'],
330
                                       energy_category_id,
331
                                       reporting_start_datetime_utc,
332
                                       reporting_end_datetime_utc))
333
                rows_space_hourly = cursor_energy.fetchall()
334
335
                rows_space_periodically, \
336
                    reporting[energy_category_id]['mean'], \
337
                    reporting[energy_category_id]['median'], \
338
                    reporting[energy_category_id]['minimum'], \
339
                    reporting[energy_category_id]['maximum'], \
340
                    reporting[energy_category_id]['stdev'], \
341
                    reporting[energy_category_id]['variance'] = \
342
                    utilities.statistics_hourly_data_by_period(rows_space_hourly,
343
                                                               reporting_start_datetime_utc,
344
                                                               reporting_end_datetime_utc,
345
                                                               period_type)
346
347
                for row_space_periodically in rows_space_periodically:
348
                    current_datetime_local = row_space_periodically[0].replace(tzinfo=timezone.utc) + \
349
                                             timedelta(minutes=timezone_offset)
350
                    if period_type == 'hourly':
351
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
352
                    elif period_type == 'daily':
353
                        current_datetime = current_datetime_local.strftime('%Y-%m-%d')
354
                    elif period_type == 'monthly':
355
                        current_datetime = current_datetime_local.strftime('%Y-%m')
356
                    elif period_type == 'yearly':
357
                        current_datetime = current_datetime_local.strftime('%Y')
358
359
                    actual_value = Decimal(0.0) if row_space_periodically[1] is None else row_space_periodically[1]
360
                    reporting[energy_category_id]['timestamps'].append(current_datetime)
361
                    reporting[energy_category_id]['values'].append(actual_value)
362
                    reporting[energy_category_id]['subtotal'] += actual_value
363
364
        ################################################################################################################
365
        # Step 8: 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 = utilities.get_energy_category_tariffs(space['cost_center_id'],
374
                                                                                    energy_category_id,
375
                                                                                    reporting_start_datetime_utc,
376
                                                                                    reporting_end_datetime_utc)
377
                tariff_timestamp_list = list()
378
                tariff_value_list = list()
379
                for k, v in energy_category_tariff_dict.items():
380
                    # convert k from utc to local
381
                    k = k + timedelta(minutes=timezone_offset)
382
                    tariff_timestamp_list.append(k.isoformat()[0:19][0:19])
383
                    tariff_value_list.append(v)
384
385
                parameters_data['names'].append('TARIFF-' + energy_category_dict[energy_category_id]['name'])
386
                parameters_data['timestamps'].append(tariff_timestamp_list)
387
                parameters_data['values'].append(tariff_value_list)
388
389
        ################################################################################################################
390
        # Step 9: query associated sensors and points data
391
        ################################################################################################################
392
        for point in point_list:
393
            point_values = []
394
            point_timestamps = []
395
            if point['object_type'] == 'ANALOG_VALUE':
396
                query = (" SELECT utc_date_time, actual_value "
397
                         " FROM tbl_analog_value "
398
                         " WHERE point_id = %s "
399
                         "       AND utc_date_time BETWEEN %s AND %s "
400
                         " ORDER BY utc_date_time ")
401
                cursor_historical.execute(query, (point['id'],
402
                                                  reporting_start_datetime_utc,
403
                                                  reporting_end_datetime_utc))
404
                rows = cursor_historical.fetchall()
405
406
                if rows is not None and len(rows) > 0:
407
                    for row in rows:
408
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
409
                                                 timedelta(minutes=timezone_offset)
410
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
411
                        point_timestamps.append(current_datetime)
412
                        point_values.append(row[1])
413
414
            elif point['object_type'] == 'ENERGY_VALUE':
415
                query = (" SELECT utc_date_time, actual_value "
416
                         " FROM tbl_energy_value "
417
                         " WHERE point_id = %s "
418
                         "       AND utc_date_time BETWEEN %s AND %s "
419
                         " ORDER BY utc_date_time ")
420
                cursor_historical.execute(query, (point['id'],
421
                                                  reporting_start_datetime_utc,
422
                                                  reporting_end_datetime_utc))
423
                rows = cursor_historical.fetchall()
424
425
                if rows is not None and len(rows) > 0:
426
                    for row in rows:
427
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
428
                                                 timedelta(minutes=timezone_offset)
429
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
430
                        point_timestamps.append(current_datetime)
431
                        point_values.append(row[1])
432
            elif point['object_type'] == 'DIGITAL_VALUE':
433
                query = (" SELECT utc_date_time, actual_value "
434
                         " FROM tbl_digital_value "
435
                         " WHERE point_id = %s "
436
                         "       AND utc_date_time BETWEEN %s AND %s ")
437
                cursor_historical.execute(query, (point['id'],
438
                                                  reporting_start_datetime_utc,
439
                                                  reporting_end_datetime_utc))
440
                rows = cursor_historical.fetchall()
441
442
                if rows is not None and len(rows) > 0:
443
                    for row in rows:
444
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
445
                                                 timedelta(minutes=timezone_offset)
446
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
447
                        point_timestamps.append(current_datetime)
448
                        point_values.append(row[1])
449
450
            parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
451
            parameters_data['timestamps'].append(point_timestamps)
452
            parameters_data['values'].append(point_values)
453
454
        ################################################################################################################
455
        # Step 10: construct the report
456
        ################################################################################################################
457
        if cursor_system:
458
            cursor_system.close()
459
        if cnx_system:
460
            cnx_system.disconnect()
461
462
        if cursor_energy:
463
            cursor_energy.close()
464
        if cnx_energy:
465
            cnx_energy.disconnect()
466
467
        result = dict()
468
469
        result['space'] = dict()
470
        result['space']['name'] = space['name']
471
        result['space']['area'] = space['area']
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']['means'] = list()
480
        result['base_period']['medians'] = list()
481
        result['base_period']['minimums'] = list()
482
        result['base_period']['maximums'] = list()
483
        result['base_period']['stdevs'] = list()
484
        result['base_period']['variances'] = list()
485
486
        if energy_category_set is not None and len(energy_category_set) > 0:
487
            for energy_category_id in energy_category_set:
488
                result['base_period']['names'].append(energy_category_dict[energy_category_id]['name'])
489
                result['base_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
490
                result['base_period']['timestamps'].append(base[energy_category_id]['timestamps'])
491
                result['base_period']['values'].append(base[energy_category_id]['values'])
492
                result['base_period']['subtotals'].append(base[energy_category_id]['subtotal'])
493
                result['base_period']['means'].append(base[energy_category_id]['mean'])
494
                result['base_period']['medians'].append(base[energy_category_id]['median'])
495
                result['base_period']['minimums'].append(base[energy_category_id]['minimum'])
496
                result['base_period']['maximums'].append(base[energy_category_id]['maximum'])
497
                result['base_period']['stdevs'].append(base[energy_category_id]['stdev'])
498
                result['base_period']['variances'].append(base[energy_category_id]['variance'])
499
500
        result['reporting_period'] = dict()
501
        result['reporting_period']['names'] = list()
502
        result['reporting_period']['energy_category_ids'] = list()
503
        result['reporting_period']['units'] = list()
504
        result['reporting_period']['timestamps'] = list()
505
        result['reporting_period']['values'] = list()
506
        result['reporting_period']['subtotals'] = list()
507
        result['reporting_period']['means'] = list()
508
        result['reporting_period']['means_per_unit_area'] = list()
509
        result['reporting_period']['means_increment_rate'] = list()
510
        result['reporting_period']['medians'] = list()
511
        result['reporting_period']['medians_per_unit_area'] = list()
512
        result['reporting_period']['medians_increment_rate'] = list()
513
        result['reporting_period']['minimums'] = list()
514
        result['reporting_period']['minimums_per_unit_area'] = list()
515
        result['reporting_period']['minimums_increment_rate'] = list()
516
        result['reporting_period']['maximums'] = list()
517
        result['reporting_period']['maximums_per_unit_area'] = list()
518
        result['reporting_period']['maximums_increment_rate'] = list()
519
        result['reporting_period']['stdevs'] = list()
520
        result['reporting_period']['stdevs_per_unit_area'] = list()
521
        result['reporting_period']['stdevs_increment_rate'] = list()
522
        result['reporting_period']['variances'] = list()
523
        result['reporting_period']['variances_per_unit_area'] = list()
524
        result['reporting_period']['variances_increment_rate'] = list()
525
526
        if energy_category_set is not None and len(energy_category_set) > 0:
527
            for energy_category_id in energy_category_set:
528
                result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name'])
529
                result['reporting_period']['energy_category_ids'].append(energy_category_id)
530
                result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
531
                result['reporting_period']['timestamps'].append(reporting[energy_category_id]['timestamps'])
532
                result['reporting_period']['values'].append(reporting[energy_category_id]['values'])
533
                result['reporting_period']['subtotals'].append(reporting[energy_category_id]['subtotal'])
534
                result['reporting_period']['means'].append(reporting[energy_category_id]['mean'])
535
                result['reporting_period']['means_per_unit_area'].append(
536
                    reporting[energy_category_id]['mean'] / space['area']
537
                    if reporting[energy_category_id]['mean'] is not None and
538
                    space['area'] is not None and
539
                    space['area'] > Decimal(0.0)
540
                    else None)
541
                result['reporting_period']['means_increment_rate'].append(
542
                    (reporting[energy_category_id]['mean'] - base[energy_category_id]['mean']) /
543
                    base[energy_category_id]['mean'] if (base[energy_category_id]['mean'] is not None and
544
                                                         base[energy_category_id]['mean'] > Decimal(0.0))
545
                    else None)
546
                result['reporting_period']['medians'].append(reporting[energy_category_id]['median'])
547
                result['reporting_period']['medians_per_unit_area'].append(
548
                    reporting[energy_category_id]['median'] / space['area']
549
                    if reporting[energy_category_id]['median'] is not None and
550
                    space['area'] is not None and
551
                    space['area'] > Decimal(0.0)
552
                    else None)
553
                result['reporting_period']['medians_increment_rate'].append(
554
                    (reporting[energy_category_id]['median'] - base[energy_category_id]['median']) /
555
                    base[energy_category_id]['median'] if (base[energy_category_id]['median'] is not None and
556
                                                           base[energy_category_id]['median'] > Decimal(0.0))
557
                    else None)
558
                result['reporting_period']['minimums'].append(reporting[energy_category_id]['minimum'])
559
                result['reporting_period']['minimums_per_unit_area'].append(
560
                    reporting[energy_category_id]['minimum'] / space['area']
561
                    if reporting[energy_category_id]['minimum'] is not None and
562
                    space['area'] is not None and space['area'] > Decimal(0.0)
563
                    else None)
564
                result['reporting_period']['minimums_increment_rate'].append(
565
                    (reporting[energy_category_id]['minimum'] - base[energy_category_id]['minimum']) /
566
                    base[energy_category_id]['minimum'] if (base[energy_category_id]['minimum'] is not None and
567
                                                            base[energy_category_id]['minimum'] > Decimal(0.0))
568
                    else None)
569
                result['reporting_period']['maximums'].append(reporting[energy_category_id]['maximum'])
570
                result['reporting_period']['maximums_per_unit_area'].append(
571
                    reporting[energy_category_id]['maximum'] / space['area']
572
                    if reporting[energy_category_id]['maximum'] is not None and
573
                    space['area'] is not None and
574
                    space['area'] > Decimal(0.0)
575
                    else None)
576
                result['reporting_period']['maximums_increment_rate'].append(
577
                    (reporting[energy_category_id]['maximum'] - base[energy_category_id]['maximum']) /
578
                    base[energy_category_id]['maximum']
579
                    if (base[energy_category_id]['maximum'] is not None and
580
                        base[energy_category_id]['maximum'] > Decimal(0.0))
581
                    else None)
582
                result['reporting_period']['stdevs'].append(reporting[energy_category_id]['stdev'])
583
                result['reporting_period']['stdevs_per_unit_area'].append(
584
                    reporting[energy_category_id]['stdev'] / space['area']
585
                    if reporting[energy_category_id]['stdev'] is not None and
586
                    space['area'] is not None and
587
                    space['area'] > Decimal(0.0)
588
                    else None)
589
                result['reporting_period']['stdevs_increment_rate'].append(
590
                    (reporting[energy_category_id]['stdev'] - base[energy_category_id]['stdev']) /
591
                    base[energy_category_id]['stdev'] if (base[energy_category_id]['stdev'] is not None and
592
                                                          base[energy_category_id]['stdev'] > Decimal(0.0))
593
                    else None)
594
                result['reporting_period']['variances'].append(reporting[energy_category_id]['variance'])
595
                result['reporting_period']['variances_per_unit_area'].append(
596
                    reporting[energy_category_id]['variance'] / space['area']
597
                    if reporting[energy_category_id]['variance'] is not None and
598
                    space['area'] is not None and
599
                    space['area'] > Decimal(0.0)
600
                    else None)
601
                result['reporting_period']['variances_increment_rate'].append(
602
                    (reporting[energy_category_id]['variance'] - base[energy_category_id]['variance']) /
603
                    base[energy_category_id]['variance'] if (base[energy_category_id]['variance'] is not None and
604
                                                             base[energy_category_id]['variance'] > Decimal(0.0))
605
                    else None)
606
607
        result['parameters'] = {
608
            "names": parameters_data['names'],
609
            "timestamps": parameters_data['timestamps'],
610
            "values": parameters_data['values']
611
        }
612
        # export result to Excel file and then encode the file to base64 string
613
        result['excel_bytes_base64'] = excelexporters.spacestatistics.export(result,
614
                                                                             space['name'],
615
                                                                             reporting_start_datetime_local,
616
                                                                             reporting_end_datetime_local,
617
                                                                             period_type)
618
619
        resp.body = json.dumps(result)
620