Issues in meterenergy.py (master) - Issues in master - myems/myems-api - Measure and Improve Code Quality continuously with Scrutinizer

Issues (382)

reports/meterenergy.py (1 issue)

Severity

Unknown 1

import falcon
import simplejson as json
import mysql.connector
import config
from datetime import datetime, timedelta, timezone
from core import utilities
from decimal import Decimal
import excelexporters.meterenergy


class Reporting:
    @staticmethod
    def __init__():
        pass

    @staticmethod
    def on_options(req, resp):
        resp.status = falcon.HTTP_200

    ####################################################################################################################
    # PROCEDURES
    # Step 1: valid parameters
    # Step 2: query the meter and energy category
    # Step 3: query associated points
    # Step 4: query base period energy consumption
    # Step 5: query reporting period energy consumption
    # Step 6: query tariff data
    # Step 7: query associated points data
    # Step 8: construct the report
    ####################################################################################################################
    @staticmethod
    def on_get(req, resp):
        print(req.params)
        meter_id = req.params.get('meterid')
        period_type = req.params.get('periodtype')
        base_period_start_datetime = req.params.get('baseperiodstartdatetime')
        base_period_end_datetime = req.params.get('baseperiodenddatetime')
        reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
        reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if meter_id is None:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_METER_ID')
        else:
            meter_id = str.strip(meter_id)
            if not meter_id.isdigit() or int(meter_id) <= 0:
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_METER_ID')

        if period_type is None:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
        else:
            period_type = str.strip(period_type)
            if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')

        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
        if config.utc_offset[0] == '-':
            timezone_offset = -timezone_offset

        base_start_datetime_utc = None
        if base_period_start_datetime is not None and len(str.strip(base_period_start_datetime)) > 0:
            base_period_start_datetime = str.strip(base_period_start_datetime)
            try:
                base_start_datetime_utc = datetime.strptime(base_period_start_datetime, '%Y-%m-%dT%H:%M:%S')
            except ValueError:
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_BASE_PERIOD_START_DATETIME")
            base_start_datetime_utc = base_start_datetime_utc.replace(tzinfo=timezone.utc) - \
                timedelta(minutes=timezone_offset)

        base_end_datetime_utc = None
        if base_period_end_datetime is not None and len(str.strip(base_period_end_datetime)) > 0:
            base_period_end_datetime = str.strip(base_period_end_datetime)
            try:
                base_end_datetime_utc = datetime.strptime(base_period_end_datetime, '%Y-%m-%dT%H:%M:%S')
            except ValueError:
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_BASE_PERIOD_END_DATETIME")
            base_end_datetime_utc = base_end_datetime_utc.replace(tzinfo=timezone.utc) - \
                timedelta(minutes=timezone_offset)

        if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
                base_start_datetime_utc >= base_end_datetime_utc:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_BASE_PERIOD_END_DATETIME')

        if reporting_period_start_datetime_local is None:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
        else:
            reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
            try:
                reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
                                                                 '%Y-%m-%dT%H:%M:%S')
            except ValueError:
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
            reporting_start_datetime_utc = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - \
                timedelta(minutes=timezone_offset)

        if reporting_period_end_datetime_local is None:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
        else:
            reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local)
            try:
                reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local,
                                                               '%Y-%m-%dT%H:%M:%S')
            except ValueError:
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
            reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
                timedelta(minutes=timezone_offset)

        if reporting_start_datetime_utc >= reporting_end_datetime_utc:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')

        ################################################################################################################
        # Step 2: query the meter and energy category
        ################################################################################################################
        cnx_system = mysql.connector.connect(**config.myems_system_db)
        cursor_system = cnx_system.cursor()

        cnx_energy = mysql.connector.connect(**config.myems_energy_db)
        cursor_energy = cnx_energy.cursor()

        cnx_historical = mysql.connector.connect(**config.myems_historical_db)
        cursor_historical = cnx_historical.cursor()

        cursor_system.execute(" SELECT m.id, m.name, m.cost_center_id, m.energy_category_id, "
                              "        ec.name, ec.unit_of_measure, ec.kgce, ec.kgco2e "
                              " FROM tbl_meters m, tbl_energy_categories ec "
                              " WHERE m.id = %s AND m.energy_category_id = ec.id ", (meter_id,))
        row_meter = cursor_system.fetchone()
        if row_meter is None:
            if cursor_system:
                cursor_system.close()
            if cnx_system:
                cnx_system.disconnect()

            if cursor_energy:
                cursor_energy.close()
            if cnx_energy:
                cnx_energy.disconnect()

            if cursor_historical:
                cursor_historical.close()
            if cnx_historical:
                cnx_historical.disconnect()
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.METER_NOT_FOUND')

        meter = dict()
        meter['id'] = row_meter[0]
        meter['name'] = row_meter[1]
        meter['cost_center_id'] = row_meter[2]
        meter['energy_category_id'] = row_meter[3]
        meter['energy_category_name'] = row_meter[4]
        meter['unit_of_measure'] = row_meter[5]
        meter['kgce'] = row_meter[6]
        meter['kgco2e'] = row_meter[7]

        ################################################################################################################
        # Step 3: query associated points
        ################################################################################################################
        point_list = list()
        cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type  "
                              " FROM tbl_meters m, tbl_meters_points mp, tbl_points p "
                              " WHERE m.id = %s AND m.id = mp.meter_id AND mp.point_id = p.id "
                              " ORDER BY p.id ", (meter['id'],))
        rows_points = cursor_system.fetchall()
        if rows_points is not None and len(rows_points) > 0:
            for row in rows_points:
                point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})

        ################################################################################################################
        # Step 4: query base period energy consumption
        ################################################################################################################
        cnx_energy = mysql.connector.connect(**config.myems_energy_db)
        cursor_energy = cnx_energy.cursor()
        query = (" SELECT start_datetime_utc, actual_value "
                 " FROM tbl_meter_hourly "
                 " WHERE meter_id = %s "
                 " AND start_datetime_utc >= %s "
                 " AND start_datetime_utc < %s "
                 " ORDER BY start_datetime_utc ")
        cursor_energy.execute(query, (meter['id'], base_start_datetime_utc, base_end_datetime_utc))
        rows_meter_hourly = cursor_energy.fetchall()

        rows_meter_periodically = utilities.aggregate_hourly_data_by_period(rows_meter_hourly,
                                                                            base_start_datetime_utc,
                                                                            base_end_datetime_utc,
                                                                            period_type)
        base = dict()
        base['timestamps'] = list()
        base['values'] = list()
        base['total_in_category'] = Decimal(0.0)
        base['total_in_kgce'] = Decimal(0.0)
        base['total_in_kgco2e'] = Decimal(0.0)

        for row_meter_periodically in rows_meter_periodically:
            current_datetime_local = row_meter_periodically[0].replace(tzinfo=timezone.utc) + \
                                     timedelta(minutes=timezone_offset)
            if period_type == 'hourly':
                current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
            elif period_type == 'daily':
                current_datetime = current_datetime_local.strftime('%Y-%m-%d')
            elif period_type == 'monthly':
                current_datetime = current_datetime_local.strftime('%Y-%m')
            elif period_type == 'yearly':
                current_datetime = current_datetime_local.strftime('%Y')

            actual_value = Decimal(0.0) if row_meter_periodically[1] is None else row_meter_periodically[1]
            base['timestamps'].append(current_datetime)

            base['values'].append(actual_value)
            base['total_in_category'] += actual_value
            base['total_in_kgce'] += actual_value * meter['kgce']
            base['total_in_kgco2e'] += actual_value * meter['kgco2e']

        ################################################################################################################
        # Step 5: query reporting period energy consumption
        ################################################################################################################
        query = (" SELECT start_datetime_utc, actual_value "
                 " FROM tbl_meter_hourly "
                 " WHERE meter_id = %s "
                 " AND start_datetime_utc >= %s "
                 " AND start_datetime_utc < %s "
                 " ORDER BY start_datetime_utc ")
        cursor_energy.execute(query, (meter['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
        rows_meter_hourly = cursor_energy.fetchall()

        rows_meter_periodically = utilities.aggregate_hourly_data_by_period(rows_meter_hourly,
                                                                            reporting_start_datetime_utc,
                                                                            reporting_end_datetime_utc,
                                                                            period_type)
        reporting = dict()
        reporting['timestamps'] = list()
        reporting['values'] = list()
        reporting['total_in_category'] = Decimal(0.0)
        reporting['total_in_kgce'] = Decimal(0.0)
        reporting['total_in_kgco2e'] = Decimal(0.0)

        for row_meter_periodically in rows_meter_periodically:
            current_datetime_local = row_meter_periodically[0].replace(tzinfo=timezone.utc) + \
                timedelta(minutes=timezone_offset)
            if period_type == 'hourly':
                current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
            elif period_type == 'daily':
                current_datetime = current_datetime_local.strftime('%Y-%m-%d')
            elif period_type == 'monthly':
                current_datetime = current_datetime_local.strftime('%Y-%m')
            elif period_type == 'yearly':
                current_datetime = current_datetime_local.strftime('%Y')

            actual_value = Decimal(0.0) if row_meter_periodically[1] is None else row_meter_periodically[1]

            reporting['timestamps'].append(current_datetime)
            reporting['values'].append(actual_value)
            reporting['total_in_category'] += actual_value
            reporting['total_in_kgce'] += actual_value * meter['kgce']
            reporting['total_in_kgco2e'] += actual_value * meter['kgco2e']

        ################################################################################################################
        # Step 6: query tariff data
        ################################################################################################################
        parameters_data = dict()
        parameters_data['names'] = list()
        parameters_data['timestamps'] = list()
        parameters_data['values'] = list()

        tariff_dict = utilities.get_energy_category_tariffs(meter['cost_center_id'],
                                                            meter['energy_category_id'],
                                                            reporting_start_datetime_utc,
                                                            reporting_end_datetime_utc)
        print(tariff_dict)
        tariff_timestamp_list = list()
        tariff_value_list = list()
        for k, v in tariff_dict.items():
            # convert k from utc to local
            k = k + timedelta(minutes=timezone_offset)
            tariff_timestamp_list.append(k.isoformat()[0:19])
            tariff_value_list.append(v)

        parameters_data['names'].append('TARIFF-' + meter['energy_category_name'])
        parameters_data['timestamps'].append(tariff_timestamp_list)
        parameters_data['values'].append(tariff_value_list)

        ################################################################################################################
        # Step 7: query associated points data
        ################################################################################################################
        for point in point_list:
            point_values = []
            point_timestamps = []
            if point['object_type'] == 'ANALOG_VALUE':
                query = (" SELECT utc_date_time, actual_value "
                         " FROM tbl_analog_value "
                         " WHERE point_id = %s "
                         "       AND utc_date_time BETWEEN %s AND %s "
                         " ORDER BY utc_date_time ")
                cursor_historical.execute(query, (point['id'],
                                                  reporting_start_datetime_utc,
                                                  reporting_end_datetime_utc))
                rows = cursor_historical.fetchall()

                if rows is not None and len(rows) > 0:
                    for row in rows:
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
                                                 timedelta(minutes=timezone_offset)
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
                        point_timestamps.append(current_datetime)
                        point_values.append(row[1])

            elif point['object_type'] == 'ENERGY_VALUE':
                query = (" SELECT utc_date_time, actual_value "
                         " FROM tbl_energy_value "
                         " WHERE point_id = %s "
                         "       AND utc_date_time BETWEEN %s AND %s "
                         " ORDER BY utc_date_time ")
                cursor_historical.execute(query, (point['id'],
                                                  reporting_start_datetime_utc,
                                                  reporting_end_datetime_utc))
                rows = cursor_historical.fetchall()

                if rows is not None and len(rows) > 0:
                    for row in rows:
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
                                                 timedelta(minutes=timezone_offset)
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
                        point_timestamps.append(current_datetime)
                        point_values.append(row[1])
            elif point['object_type'] == 'DIGITAL_VALUE':
                query = (" SELECT utc_date_time, actual_value "
                         " FROM tbl_digital_value "
                         " WHERE point_id = %s "
                         "       AND utc_date_time BETWEEN %s AND %s ")
                cursor_historical.execute(query, (point['id'],
                                                  reporting_start_datetime_utc,
                                                  reporting_end_datetime_utc))
                rows = cursor_historical.fetchall()

                if rows is not None and len(rows) > 0:
                    for row in rows:
                        current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
                                                 timedelta(minutes=timezone_offset)
                        current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
                        point_timestamps.append(current_datetime)
                        point_values.append(row[1])

            parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
            parameters_data['timestamps'].append(point_timestamps)
            parameters_data['values'].append(point_values)

        ################################################################################################################
        # Step 8: construct the report
        ################################################################################################################
        if cursor_system:
            cursor_system.close()
        if cnx_system:
            cnx_system.disconnect()

        if cursor_energy:
            cursor_energy.close()
        if cnx_energy:
            cnx_energy.disconnect()

        if cursor_historical:
            cursor_historical.close()
        if cnx_historical:
            cnx_historical.disconnect()
        result = {
            "meter": {
                "cost_center_id": meter['cost_center_id'],
                "energy_category_id": meter['energy_category_id'],
                "energy_category_name": meter['energy_category_name'],
                "unit_of_measure": meter['unit_of_measure'],
                "kgce": meter['kgce'],
                "kgco2e": meter['kgco2e'],
            },
            "base_period": {
                "total_in_category": base['total_in_category'],
                "total_in_kgce": base['total_in_kgce'],
                "total_in_kgco2e": base['total_in_kgco2e'],
                "timestamps": base['timestamps'],
                "values": base['values']
            },
            "reporting_period": {
                "increment_rate":
                    (reporting['total_in_category'] - base['total_in_category'])/base['total_in_category']
                    if base['total_in_category'] > 0 else None,
                "total_in_category": reporting['total_in_category'],
                "total_in_kgce": reporting['total_in_kgce'],
                "total_in_kgco2e": reporting['total_in_kgco2e'],
                "timestamps": reporting['timestamps'],
                "values": reporting['values'],
            },
            "parameters": {
                "names": parameters_data['names'],
                "timestamps": parameters_data['timestamps'],
                "values": parameters_data['values']
            },
        }
        # export result to Excel file and then encode the file to base64 string
        result['excel_bytes_base64'] = \
            excelexporters.meterenergy.export(result,
                                              meter['name'],
                                              reporting_period_start_datetime_local,
                                              reporting_period_end_datetime_local,
                                              period_type)

        resp.body = json.dumps(result)


1			import falcon
2			import simplejson as json
3			import mysql.connector
4			import config
5			from datetime import datetime, timedelta, timezone
6			from core import utilities
7			from decimal import Decimal
8			import excelexporters.meterenergy
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 meter and energy category
24			# Step 3: query associated points
25			# Step 4: query base period energy consumption
26			# Step 5: query reporting period energy consumption
27			# Step 6: query tariff data
28			# Step 7: query associated points data
29			# Step 8: construct the report
30			####################################################################################################################
31			@staticmethod
32			def on_get(req, resp):
33			print(req.params)
34			meter_id = req.params.get('meterid')
35			period_type = req.params.get('periodtype')
36			base_period_start_datetime = req.params.get('baseperiodstartdatetime')
37			base_period_end_datetime = req.params.get('baseperiodenddatetime')
38			reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
39			reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
40
41			################################################################################################################
42			# Step 1: valid parameters
43			################################################################################################################
44			if meter_id is None:
45			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_METER_ID')
46			else:
47			meter_id = str.strip(meter_id)
48			if not meter_id.isdigit() or int(meter_id) <= 0:
49			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_METER_ID')
50
51			if period_type is None:
52			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
53			else:
54			period_type = str.strip(period_type)
55			if period_type not in ['hourly', 'daily', 'monthly', 'yearly']:
56			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
57
58			timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
59			if config.utc_offset[0] == '-':
60			timezone_offset = -timezone_offset
61
62			base_start_datetime_utc = None
63			if base_period_start_datetime is not None and len(str.strip(base_period_start_datetime)) > 0:
64			base_period_start_datetime = str.strip(base_period_start_datetime)
65			try:
66			base_start_datetime_utc = datetime.strptime(base_period_start_datetime, '%Y-%m-%dT%H:%M:%S')
67			except ValueError:
68			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
69			description="API.INVALID_BASE_PERIOD_START_DATETIME")
70			base_start_datetime_utc = base_start_datetime_utc.replace(tzinfo=timezone.utc) - \
71			timedelta(minutes=timezone_offset)
72
73			base_end_datetime_utc = None
74			if base_period_end_datetime is not None and len(str.strip(base_period_end_datetime)) > 0:
75			base_period_end_datetime = str.strip(base_period_end_datetime)
76			try:
77			base_end_datetime_utc = datetime.strptime(base_period_end_datetime, '%Y-%m-%dT%H:%M:%S')
78			except ValueError:
79			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
80			description="API.INVALID_BASE_PERIOD_END_DATETIME")
81			base_end_datetime_utc = base_end_datetime_utc.replace(tzinfo=timezone.utc) - \
82			timedelta(minutes=timezone_offset)
83
84			if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
85			base_start_datetime_utc >= base_end_datetime_utc:
86			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
87			description='API.INVALID_BASE_PERIOD_END_DATETIME')
88
89			if reporting_period_start_datetime_local is None:
90			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
91			description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
92			else:
93			reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
94			try:
95			reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
96			'%Y-%m-%dT%H:%M:%S')
97			except ValueError:
98			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
99			description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
100			reporting_start_datetime_utc = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - \
101			timedelta(minutes=timezone_offset)
102
103			if reporting_period_end_datetime_local is None:
104			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
105			description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
106			else:
107			reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local)
108			try:
109			reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local,
110			'%Y-%m-%dT%H:%M:%S')
111			except ValueError:
112			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
113			description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
114			reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
115			timedelta(minutes=timezone_offset)
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 meter and energy category
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 m.id, m.name, m.cost_center_id, m.energy_category_id, "
134			" ec.name, ec.unit_of_measure, ec.kgce, ec.kgco2e "
135			" FROM tbl_meters m, tbl_energy_categories ec "
136			" WHERE m.id = %s AND m.energy_category_id = ec.id ", (meter_id,))
137			row_meter = cursor_system.fetchone()
138			if row_meter is None:
139			if cursor_system:
140			cursor_system.close()
141			if cnx_system:
142			cnx_system.disconnect()
143
144			if cursor_energy:
145			cursor_energy.close()
146			if cnx_energy:
147			cnx_energy.disconnect()
148
149			if cursor_historical:
150			cursor_historical.close()
151			if cnx_historical:
152			cnx_historical.disconnect()
153			raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.METER_NOT_FOUND')
154
155			meter = dict()
156			meter['id'] = row_meter[0]
157			meter['name'] = row_meter[1]
158			meter['cost_center_id'] = row_meter[2]
159			meter['energy_category_id'] = row_meter[3]
160			meter['energy_category_name'] = row_meter[4]
161			meter['unit_of_measure'] = row_meter[5]
162			meter['kgce'] = row_meter[6]
163			meter['kgco2e'] = row_meter[7]
164
165			################################################################################################################
166			# Step 3: query associated points
167			################################################################################################################
168			point_list = list()
169			cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type "
170			" FROM tbl_meters m, tbl_meters_points mp, tbl_points p "
171			" WHERE m.id = %s AND m.id = mp.meter_id AND mp.point_id = p.id "
172			" ORDER BY p.id ", (meter['id'],))
173			rows_points = cursor_system.fetchall()
174			if rows_points is not None and len(rows_points) > 0:
175			for row in rows_points:
176			point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]})
177
178			################################################################################################################
179			# Step 4: query base period energy consumption
180			################################################################################################################
181			cnx_energy = mysql.connector.connect(**config.myems_energy_db)
182			cursor_energy = cnx_energy.cursor()
183			query = (" SELECT start_datetime_utc, actual_value "
184			" FROM tbl_meter_hourly "
185			" WHERE meter_id = %s "
186			" AND start_datetime_utc >= %s "
187			" AND start_datetime_utc < %s "
188			" ORDER BY start_datetime_utc ")
189			cursor_energy.execute(query, (meter['id'], base_start_datetime_utc, base_end_datetime_utc))
190			rows_meter_hourly = cursor_energy.fetchall()
191
192			rows_meter_periodically = utilities.aggregate_hourly_data_by_period(rows_meter_hourly,
193			base_start_datetime_utc,
194			base_end_datetime_utc,
195			period_type)
196			base = dict()
197			base['timestamps'] = list()
198			base['values'] = list()
199			base['total_in_category'] = Decimal(0.0)
200			base['total_in_kgce'] = Decimal(0.0)
201			base['total_in_kgco2e'] = Decimal(0.0)
202
203			for row_meter_periodically in rows_meter_periodically:
204			current_datetime_local = row_meter_periodically[0].replace(tzinfo=timezone.utc) + \
205			timedelta(minutes=timezone_offset)
206			if period_type == 'hourly':
207			current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
208			elif period_type == 'daily':
209			current_datetime = current_datetime_local.strftime('%Y-%m-%d')
210			elif period_type == 'monthly':
211			current_datetime = current_datetime_local.strftime('%Y-%m')
212			elif period_type == 'yearly':
213			current_datetime = current_datetime_local.strftime('%Y')
214
215			actual_value = Decimal(0.0) if row_meter_periodically[1] is None else row_meter_periodically[1]
216			base['timestamps'].append(current_datetime)
			0 ignored issues – show introduced 2020-11-24 10:14 UTC by Report Bug Copy Issue Report Show Similar Issues like this The variable `current_datetime` does not seem to be defined for all execution paths. Loading history...
217			base['values'].append(actual_value)
218			base['total_in_category'] += actual_value
219			base['total_in_kgce'] += actual_value * meter['kgce']
220			base['total_in_kgco2e'] += actual_value * meter['kgco2e']
221
222			################################################################################################################
223			# Step 5: query reporting period energy consumption
224			################################################################################################################
225			query = (" SELECT start_datetime_utc, actual_value "
226			" FROM tbl_meter_hourly "
227			" WHERE meter_id = %s "
228			" AND start_datetime_utc >= %s "
229			" AND start_datetime_utc < %s "
230			" ORDER BY start_datetime_utc ")
231			cursor_energy.execute(query, (meter['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
232			rows_meter_hourly = cursor_energy.fetchall()
233
234			rows_meter_periodically = utilities.aggregate_hourly_data_by_period(rows_meter_hourly,
235			reporting_start_datetime_utc,
236			reporting_end_datetime_utc,
237			period_type)
238			reporting = dict()
239			reporting['timestamps'] = list()
240			reporting['values'] = list()
241			reporting['total_in_category'] = Decimal(0.0)
242			reporting['total_in_kgce'] = Decimal(0.0)
243			reporting['total_in_kgco2e'] = Decimal(0.0)
244
245			for row_meter_periodically in rows_meter_periodically:
246			current_datetime_local = row_meter_periodically[0].replace(tzinfo=timezone.utc) + \
247			timedelta(minutes=timezone_offset)
248			if period_type == 'hourly':
249			current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
250			elif period_type == 'daily':
251			current_datetime = current_datetime_local.strftime('%Y-%m-%d')
252			elif period_type == 'monthly':
253			current_datetime = current_datetime_local.strftime('%Y-%m')
254			elif period_type == 'yearly':
255			current_datetime = current_datetime_local.strftime('%Y')
256
257			actual_value = Decimal(0.0) if row_meter_periodically[1] is None else row_meter_periodically[1]
258
259			reporting['timestamps'].append(current_datetime)
260			reporting['values'].append(actual_value)
261			reporting['total_in_category'] += actual_value
262			reporting['total_in_kgce'] += actual_value * meter['kgce']
263			reporting['total_in_kgco2e'] += actual_value * meter['kgco2e']
264
265			################################################################################################################
266			# Step 6: query tariff data
267			################################################################################################################
268			parameters_data = dict()
269			parameters_data['names'] = list()
270			parameters_data['timestamps'] = list()
271			parameters_data['values'] = list()
272
273			tariff_dict = utilities.get_energy_category_tariffs(meter['cost_center_id'],
274			meter['energy_category_id'],
275			reporting_start_datetime_utc,
276			reporting_end_datetime_utc)
277			print(tariff_dict)
278			tariff_timestamp_list = list()
279			tariff_value_list = list()
280			for k, v in tariff_dict.items():
281			# convert k from utc to local
282			k = k + timedelta(minutes=timezone_offset)
283			tariff_timestamp_list.append(k.isoformat()[0:19])
284			tariff_value_list.append(v)
285
286			parameters_data['names'].append('TARIFF-' + meter['energy_category_name'])
287			parameters_data['timestamps'].append(tariff_timestamp_list)
288			parameters_data['values'].append(tariff_value_list)
289
290			################################################################################################################
291			# Step 7: query associated points data
292			################################################################################################################
293			for point in point_list:
294			point_values = []
295			point_timestamps = []
296			if point['object_type'] == 'ANALOG_VALUE':
297			query = (" SELECT utc_date_time, actual_value "
298			" FROM tbl_analog_value "
299			" WHERE point_id = %s "
300			" AND utc_date_time BETWEEN %s AND %s "
301			" ORDER BY utc_date_time ")
302			cursor_historical.execute(query, (point['id'],
303			reporting_start_datetime_utc,
304			reporting_end_datetime_utc))
305			rows = cursor_historical.fetchall()
306
307			if rows is not None and len(rows) > 0:
308			for row in rows:
309			current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
310			timedelta(minutes=timezone_offset)
311			current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
312			point_timestamps.append(current_datetime)
313			point_values.append(row[1])
314
315			elif point['object_type'] == 'ENERGY_VALUE':
316			query = (" SELECT utc_date_time, actual_value "
317			" FROM tbl_energy_value "
318			" WHERE point_id = %s "
319			" AND utc_date_time BETWEEN %s AND %s "
320			" ORDER BY utc_date_time ")
321			cursor_historical.execute(query, (point['id'],
322			reporting_start_datetime_utc,
323			reporting_end_datetime_utc))
324			rows = cursor_historical.fetchall()
325
326			if rows is not None and len(rows) > 0:
327			for row in rows:
328			current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
329			timedelta(minutes=timezone_offset)
330			current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
331			point_timestamps.append(current_datetime)
332			point_values.append(row[1])
333			elif point['object_type'] == 'DIGITAL_VALUE':
334			query = (" SELECT utc_date_time, actual_value "
335			" FROM tbl_digital_value "
336			" WHERE point_id = %s "
337			" AND utc_date_time BETWEEN %s AND %s ")
338			cursor_historical.execute(query, (point['id'],
339			reporting_start_datetime_utc,
340			reporting_end_datetime_utc))
341			rows = cursor_historical.fetchall()
342
343			if rows is not None and len(rows) > 0:
344			for row in rows:
345			current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \
346			timedelta(minutes=timezone_offset)
347			current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
348			point_timestamps.append(current_datetime)
349			point_values.append(row[1])
350
351			parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
352			parameters_data['timestamps'].append(point_timestamps)
353			parameters_data['values'].append(point_values)
354
355			################################################################################################################
356			# Step 8: construct the report
357			################################################################################################################
358			if cursor_system:
359			cursor_system.close()
360			if cnx_system:
361			cnx_system.disconnect()
362
363			if cursor_energy:
364			cursor_energy.close()
365			if cnx_energy:
366			cnx_energy.disconnect()
367
368			if cursor_historical:
369			cursor_historical.close()
370			if cnx_historical:
371			cnx_historical.disconnect()
372			result = {
373			"meter": {
374			"cost_center_id": meter['cost_center_id'],
375			"energy_category_id": meter['energy_category_id'],
376			"energy_category_name": meter['energy_category_name'],
377			"unit_of_measure": meter['unit_of_measure'],
378			"kgce": meter['kgce'],
379			"kgco2e": meter['kgco2e'],
380			},
381			"base_period": {
382			"total_in_category": base['total_in_category'],
383			"total_in_kgce": base['total_in_kgce'],
384			"total_in_kgco2e": base['total_in_kgco2e'],
385			"timestamps": base['timestamps'],
386			"values": base['values']
387			},
388			"reporting_period": {
389			"increment_rate":
390			(reporting['total_in_category'] - base['total_in_category'])/base['total_in_category']
391			if base['total_in_category'] > 0 else None,
392			"total_in_category": reporting['total_in_category'],
393			"total_in_kgce": reporting['total_in_kgce'],
394			"total_in_kgco2e": reporting['total_in_kgco2e'],
395			"timestamps": reporting['timestamps'],
396			"values": reporting['values'],
397			},
398			"parameters": {
399			"names": parameters_data['names'],
400			"timestamps": parameters_data['timestamps'],
401			"values": parameters_data['values']
402			},
403			}
404			# export result to Excel file and then encode the file to base64 string
405			result['excel_bytes_base64'] = \
406			excelexporters.meterenergy.export(result,
407			meter['name'],
408			reporting_period_start_datetime_local,
409			reporting_period_end_datetime_local,
410			period_type)
411
412			resp.body = json.dumps(result)
413

myems / myems-api

Issues (382)

reports/meterenergy.py (1 issue)

Severity

Introduced By

Duplication Side-by-Side

Filter issues like