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

Issues (1577)

myems-api/reports/metersubmetersbalance.py (2 issues)

Severity

Unknown 2

import re
from datetime import datetime, timedelta, timezone
from decimal import Decimal
import falcon
import mysql.connector
import simplejson as json
import config
import excelexporters.metersubmetersbalance
from core import utilities
from core.useractivity import access_control, api_key_control


class Reporting:
    def __init__(self):
        """"Initializes Reporting"""
        pass

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

    ####################################################################################################################
    # PROCEDURES
    # Step 1: valid parameters
    # Step 2: query the master meter and energy category
    # Step 3: query associated submeters
    # Step 4: query reporting period master meter energy consumption
    # Step 5: query reporting period submeters energy consumption
    # Step 6: calculate reporting period difference between master meter and submeters
    # Step 7: query submeter values as parameter data
    # Step 8: construct the report
    ####################################################################################################################
    @staticmethod
    def on_get(req, resp):
        if 'API-KEY' not in req.headers or \
                not isinstance(req.headers['API-KEY'], str) or \
                len(str.strip(req.headers['API-KEY'])) == 0:
            access_control(req)
        else:
            api_key_control(req)
        print(req.params)
        meter_id = req.params.get('meterid')
        meter_uuid = req.params.get('meteruuid')
        period_type = req.params.get('periodtype')
        reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
        reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
        language = req.params.get('language')
        quick_mode = req.params.get('quickmode')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if meter_id is None and meter_uuid is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_METER_ID')

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

        if meter_uuid is not None:
            regex = re.compile(r'^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z', re.I)
            match = regex.match(str.strip(meter_uuid))
            if not bool(match):
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description='API.INVALID_METER_UUID')

        if period_type is None:
            raise falcon.HTTPError(status=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', 'weekly', 'monthly', 'yearly']:
                raise falcon.HTTPError(status=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

        if reporting_period_start_datetime_local is None:
            raise falcon.HTTPError(status=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(status=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)
            # nomalize the start datetime
            if config.minutes_to_count == 30 and reporting_start_datetime_utc.minute >= 30:
                reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=30, second=0, microsecond=0)
            else:
                reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=0, second=0, microsecond=0)

        if reporting_period_end_datetime_local is None:
            raise falcon.HTTPError(status=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(status=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(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')

        # if turn quick mode on, do not return parameters data and excel file
        is_quick_mode = False
        if quick_mode is not None and \
            len(str.strip(quick_mode)) > 0 and \
                str.lower(str.strip(quick_mode)) in ('true', 't', 'on', 'yes', 'y'):
            is_quick_mode = True

        ################################################################################################################
        # 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()

        if meter_id is not None:
            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()
        elif meter_uuid is not None:
            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.uuid = %s AND m.energy_category_id = ec.id ", (meter_uuid,))
            row_meter = cursor_system.fetchone()

        if row_meter is None:

            if cursor_system:
                cursor_system.close()
            if cnx_system:
                cnx_system.close()

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

            raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', description='API.METER_NOT_FOUND')

        master_meter = dict()
        master_meter['id'] = row_meter[0]
        master_meter['name'] = row_meter[1]
        master_meter['cost_center_id'] = row_meter[2]
        master_meter['energy_category_id'] = row_meter[3]
        master_meter['energy_category_name'] = row_meter[4]
        master_meter['unit_of_measure'] = row_meter[5]

        ################################################################################################################
        # Step 3: query associated submeters
        ################################################################################################################
        submeter_list = list()
        submeter_id_set = set()

        cursor_system.execute(" SELECT id, name, energy_category_id "
                              " FROM tbl_meters "
                              " WHERE master_meter_id = %s ",
                              (master_meter['id'],))
        rows_meters = cursor_system.fetchall()

        if rows_meters is not None and len(rows_meters) > 0:
            for row in rows_meters:
                submeter_list.append({"id": row[0],
                                      "name": row[1],
                                      "energy_category_id": row[2]})
                submeter_id_set.add(row[0])

        ################################################################################################################
        # Step 4: query reporting period master meter energy consumption
        ################################################################################################################
        reporting = dict()
        reporting['master_meter_total_in_category'] = Decimal(0.0)
        reporting['submeters_total_in_category'] = Decimal(0.0)
        reporting['total_difference_in_category'] = Decimal(0.0)
        reporting['percentage_difference'] = Decimal(0.0)
        reporting['timestamps'] = list()
        reporting['master_meter_values'] = list()
        reporting['submeters_values'] = list()
        reporting['difference_values'] = list()

        parameters_data = dict()
        parameters_data['names'] = list()
        parameters_data['timestamps'] = list()
        parameters_data['values'] = list()

        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, (master_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)

        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.isoformat()[0:19]
            elif period_type == 'daily':
                current_datetime = current_datetime_local.isoformat()[0:10]
            elif period_type == 'weekly':
                current_datetime = current_datetime_local.isoformat()[0:10]
            elif period_type == 'monthly':
                current_datetime = current_datetime_local.isoformat()[0:7]
            elif period_type == 'yearly':
                current_datetime = current_datetime_local.isoformat()[0:4]

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

            reporting['timestamps'].append(current_datetime)

            reporting['master_meter_values'].append(actual_value)
            reporting['master_meter_total_in_category'] += actual_value

        # add master meter values to parameter data
        parameters_data['names'].append(master_meter['name'])
        parameters_data['timestamps'].append(reporting['timestamps'])
        parameters_data['values'].append(reporting['master_meter_values'])

        ################################################################################################################
        # Step 5: query reporting period submeters energy consumption
        ################################################################################################################
        if len(submeter_list) > 0:
            query = (" SELECT start_datetime_utc, SUM(actual_value) "
                     " FROM tbl_meter_hourly "
                     " WHERE meter_id IN ( " + ', '.join(map(str, submeter_id_set)) + ") "
                     " AND start_datetime_utc >= %s "
                     " AND start_datetime_utc < %s "
                     " GROUP BY start_datetime_utc "
                     " ORDER BY start_datetime_utc ")
            cursor_energy.execute(query, (reporting_start_datetime_utc, reporting_end_datetime_utc))
            rows_submeters_hourly = cursor_energy.fetchall()

            rows_submeters_periodically = utilities.aggregate_hourly_data_by_period(rows_submeters_hourly,
                                                                                    reporting_start_datetime_utc,
                                                                                    reporting_end_datetime_utc,
                                                                                    period_type)

            for row_submeters_periodically in rows_submeters_periodically:
                current_datetime_local = row_submeters_periodically[0].replace(tzinfo=timezone.utc) + \
                                         timedelta(minutes=timezone_offset)
                if period_type == 'hourly':
                    current_datetime = current_datetime_local.isoformat()[0:19]
                elif period_type == 'daily':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'weekly':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'monthly':
                    current_datetime = current_datetime_local.isoformat()[0:7]
                elif period_type == 'yearly':
                    current_datetime = current_datetime_local.isoformat()[0:4]

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

                reporting['submeters_values'].append(actual_value)
                reporting['submeters_total_in_category'] += actual_value

        ################################################################################################################
        # Step 6: calculate reporting period difference between master meter and submeters
        ################################################################################################################
        if len(submeter_list) > 0:
            for i in range(len(reporting['timestamps'])):
                reporting['difference_values'].append(reporting['master_meter_values'][i] -
                                                      reporting['submeters_values'][i])
        else:
            for i in range(len(reporting['timestamps'])):
                reporting['difference_values'].append(reporting['master_meter_values'][i])

        reporting['total_difference_in_category'] = \
            reporting['master_meter_total_in_category'] - reporting['submeters_total_in_category']

        if abs(reporting['master_meter_total_in_category']) > Decimal(0.0):
            reporting['percentage_difference'] = \
                reporting['total_difference_in_category'] / reporting['master_meter_total_in_category']
        elif abs(reporting['master_meter_total_in_category']) == Decimal(0.0) and \
                abs(reporting['submeters_total_in_category']) > Decimal(0.0):
            reporting['percentage_difference'] = Decimal(-1.0)

        ################################################################################################################
        # Step 7: query submeter values as parameter data
        ################################################################################################################
        for submeter in submeter_list:
            submeter_timestamps = list()
            submeter_values = list()

            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, (submeter['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)

            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.isoformat()[0:19]
                elif period_type == 'daily':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'weekly':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'monthly':
                    current_datetime = current_datetime_local.isoformat()[0:7]
                elif period_type == 'yearly':
                    current_datetime = current_datetime_local.isoformat()[0:4]

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

                submeter_timestamps.append(current_datetime)
                submeter_values.append(actual_value)

            parameters_data['names'].append(submeter['name'])
            parameters_data['timestamps'].append(submeter_timestamps)
            parameters_data['values'].append(submeter_values)

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

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

        result = {
            "meter": {
                "cost_center_id": master_meter['cost_center_id'],
                "energy_category_id": master_meter['energy_category_id'],
                "energy_category_name": master_meter['energy_category_name'],
                "unit_of_measure": master_meter['unit_of_measure'],
            },
            "reporting_period": {
                "master_meter_consumption_in_category": reporting['master_meter_total_in_category'],
                "submeters_consumption_in_category": reporting['submeters_total_in_category'],
                "difference_in_category": reporting['total_difference_in_category'],
                "percentage_difference": reporting['percentage_difference'],
                "timestamps": reporting['timestamps'],
                "difference_values": reporting['difference_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
        if not is_quick_mode:
            result['excel_bytes_base64'] = \
                excelexporters.metersubmetersbalance.export(result,
                                                            master_meter['name'],
                                                            reporting_period_start_datetime_local,
                                                            reporting_period_end_datetime_local,
                                                            period_type,
                                                            language)

        resp.text = json.dumps(result)


1			import re
2			from datetime import datetime, timedelta, timezone
3			from decimal import Decimal
4			import falcon
5			import mysql.connector
6			import simplejson as json
7			import config
8			import excelexporters.metersubmetersbalance
9			from core import utilities
10			from core.useractivity import access_control, api_key_control
11
12
13			class Reporting:
14			def __init__(self):
15			""""Initializes Reporting"""
16			pass
17
18			@staticmethod
19			def on_options(req, resp):
20			_ = req
21			resp.status = falcon.HTTP_200
22
23			####################################################################################################################
24			# PROCEDURES
25			# Step 1: valid parameters
26			# Step 2: query the master meter and energy category
27			# Step 3: query associated submeters
28			# Step 4: query reporting period master meter energy consumption
29			# Step 5: query reporting period submeters energy consumption
30			# Step 6: calculate reporting period difference between master meter and submeters
31			# Step 7: query submeter values as parameter data
32			# Step 8: construct the report
33			####################################################################################################################
34			@staticmethod
35			def on_get(req, resp):
36			if 'API-KEY' not in req.headers or \
37			not isinstance(req.headers['API-KEY'], str) or \
38			len(str.strip(req.headers['API-KEY'])) == 0:
39			access_control(req)
40			else:
41			api_key_control(req)
42			print(req.params)
43			meter_id = req.params.get('meterid')
44			meter_uuid = req.params.get('meteruuid')
45			period_type = req.params.get('periodtype')
46			reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
47			reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
48			language = req.params.get('language')
49			quick_mode = req.params.get('quickmode')
50
51			################################################################################################################
52			# Step 1: valid parameters
53			################################################################################################################
54			if meter_id is None and meter_uuid is None:
55			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_METER_ID')
56
57			if meter_id is not None:
58			meter_id = str.strip(meter_id)
59			if not meter_id.isdigit() or int(meter_id) <= 0:
60			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
61			description='API.INVALID_METER_ID')
62
63			if meter_uuid is not None:
64			regex = re.compile(r'^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z', re.I)
65			match = regex.match(str.strip(meter_uuid))
66			if not bool(match):
67			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
68			description='API.INVALID_METER_UUID')
69
70			if period_type is None:
71			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
72			description='API.INVALID_PERIOD_TYPE')
73			else:
74			period_type = str.strip(period_type)
75			if period_type not in ['hourly', 'daily', 'weekly', 'monthly', 'yearly']:
76			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
77			description='API.INVALID_PERIOD_TYPE')
78
79			timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
80			if config.utc_offset[0] == '-':
81			timezone_offset = -timezone_offset
82
83			if reporting_period_start_datetime_local is None:
84			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
85			description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
86			else:
87			reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
88			try:
89			reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
90			'%Y-%m-%dT%H:%M:%S')
91			except ValueError:
92			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
93			description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
94			reporting_start_datetime_utc = \
95			reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset)
96			# nomalize the start datetime
97			if config.minutes_to_count == 30 and reporting_start_datetime_utc.minute >= 30:
98			reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=30, second=0, microsecond=0)
99			else:
100			reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=0, second=0, microsecond=0)
101
102			if reporting_period_end_datetime_local is None:
103			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
104			description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
105			else:
106			reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local)
107			try:
108			reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local,
109			'%Y-%m-%dT%H:%M:%S')
110			except ValueError:
111			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
112			description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
113			reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
114			timedelta(minutes=timezone_offset)
115
116			if reporting_start_datetime_utc >= reporting_end_datetime_utc:
117			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
118			description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
119
120			# if turn quick mode on, do not return parameters data and excel file
121			is_quick_mode = False
122			if quick_mode is not None and \
123			len(str.strip(quick_mode)) > 0 and \
124			str.lower(str.strip(quick_mode)) in ('true', 't', 'on', 'yes', 'y'):
125			is_quick_mode = True
126
127			################################################################################################################
128			# Step 2: query the meter and energy category
129			################################################################################################################
130			cnx_system = mysql.connector.connect(**config.myems_system_db)
131			cursor_system = cnx_system.cursor()
132
133			cnx_energy = mysql.connector.connect(**config.myems_energy_db)
134			cursor_energy = cnx_energy.cursor()
135
136			if meter_id is not None:
137			cursor_system.execute(" SELECT m.id, m.name, m.cost_center_id, m.energy_category_id, "
138			" ec.name, ec.unit_of_measure, ec.kgce, ec.kgco2e "
139			" FROM tbl_meters m, tbl_energy_categories ec "
140			" WHERE m.id = %s AND m.energy_category_id = ec.id ", (meter_id,))
141			row_meter = cursor_system.fetchone()
142			elif meter_uuid is not None:
143			cursor_system.execute(" SELECT m.id, m.name, m.cost_center_id, m.energy_category_id, "
144			" ec.name, ec.unit_of_measure, ec.kgce, ec.kgco2e "
145			" FROM tbl_meters m, tbl_energy_categories ec "
146			" WHERE m.uuid = %s AND m.energy_category_id = ec.id ", (meter_uuid,))
147			row_meter = cursor_system.fetchone()
148
149			if row_meter is None:
			0 ignored issues – show introduced 2022-01-18 14:12 UTC by Report Bug Copy Issue Report Show Similar Issues like this The variable `row_meter` does not seem to be defined for all execution paths. Loading history...
150			if cursor_system:
151			cursor_system.close()
152			if cnx_system:
153			cnx_system.close()
154
155			if cursor_energy:
156			cursor_energy.close()
157			if cnx_energy:
158			cnx_energy.close()
159
160			raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', description='API.METER_NOT_FOUND')
161
162			master_meter = dict()
163			master_meter['id'] = row_meter[0]
164			master_meter['name'] = row_meter[1]
165			master_meter['cost_center_id'] = row_meter[2]
166			master_meter['energy_category_id'] = row_meter[3]
167			master_meter['energy_category_name'] = row_meter[4]
168			master_meter['unit_of_measure'] = row_meter[5]
169
170			################################################################################################################
171			# Step 3: query associated submeters
172			################################################################################################################
173			submeter_list = list()
174			submeter_id_set = set()
175
176			cursor_system.execute(" SELECT id, name, energy_category_id "
177			" FROM tbl_meters "
178			" WHERE master_meter_id = %s ",
179			(master_meter['id'],))
180			rows_meters = cursor_system.fetchall()
181
182			if rows_meters is not None and len(rows_meters) > 0:
183			for row in rows_meters:
184			submeter_list.append({"id": row[0],
185			"name": row[1],
186			"energy_category_id": row[2]})
187			submeter_id_set.add(row[0])
188
189			################################################################################################################
190			# Step 4: query reporting period master meter energy consumption
191			################################################################################################################
192			reporting = dict()
193			reporting['master_meter_total_in_category'] = Decimal(0.0)
194			reporting['submeters_total_in_category'] = Decimal(0.0)
195			reporting['total_difference_in_category'] = Decimal(0.0)
196			reporting['percentage_difference'] = Decimal(0.0)
197			reporting['timestamps'] = list()
198			reporting['master_meter_values'] = list()
199			reporting['submeters_values'] = list()
200			reporting['difference_values'] = list()
201
202			parameters_data = dict()
203			parameters_data['names'] = list()
204			parameters_data['timestamps'] = list()
205			parameters_data['values'] = list()
206
207			query = (" SELECT start_datetime_utc, actual_value "
208			" FROM tbl_meter_hourly "
209			" WHERE meter_id = %s "
210			" AND start_datetime_utc >= %s "
211			" AND start_datetime_utc < %s "
212			" ORDER BY start_datetime_utc ")
213			cursor_energy.execute(query, (master_meter['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
214			rows_meter_hourly = cursor_energy.fetchall()
215
216			rows_meter_periodically = utilities.aggregate_hourly_data_by_period(rows_meter_hourly,
217			reporting_start_datetime_utc,
218			reporting_end_datetime_utc,
219			period_type)
220
221			for row_meter_periodically in rows_meter_periodically:
222			current_datetime_local = row_meter_periodically[0].replace(tzinfo=timezone.utc) + \
223			timedelta(minutes=timezone_offset)
224			if period_type == 'hourly':
225			current_datetime = current_datetime_local.isoformat()[0:19]
226			elif period_type == 'daily':
227			current_datetime = current_datetime_local.isoformat()[0:10]
228			elif period_type == 'weekly':
229			current_datetime = current_datetime_local.isoformat()[0:10]
230			elif period_type == 'monthly':
231			current_datetime = current_datetime_local.isoformat()[0:7]
232			elif period_type == 'yearly':
233			current_datetime = current_datetime_local.isoformat()[0:4]
234
235			actual_value = Decimal(0.0) if row_meter_periodically[1] is None else row_meter_periodically[1]
236
237			reporting['timestamps'].append(current_datetime)
			0 ignored issues – show introduced 2021-03-02 03:54 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...
238			reporting['master_meter_values'].append(actual_value)
239			reporting['master_meter_total_in_category'] += actual_value
240
241			# add master meter values to parameter data
242			parameters_data['names'].append(master_meter['name'])
243			parameters_data['timestamps'].append(reporting['timestamps'])
244			parameters_data['values'].append(reporting['master_meter_values'])
245
246			################################################################################################################
247			# Step 5: query reporting period submeters energy consumption
248			################################################################################################################
249			if len(submeter_list) > 0:
250			query = (" SELECT start_datetime_utc, SUM(actual_value) "
251			" FROM tbl_meter_hourly "
252			" WHERE meter_id IN ( " + ', '.join(map(str, submeter_id_set)) + ") "
253			" AND start_datetime_utc >= %s "
254			" AND start_datetime_utc < %s "
255			" GROUP BY start_datetime_utc "
256			" ORDER BY start_datetime_utc ")
257			cursor_energy.execute(query, (reporting_start_datetime_utc, reporting_end_datetime_utc))
258			rows_submeters_hourly = cursor_energy.fetchall()
259
260			rows_submeters_periodically = utilities.aggregate_hourly_data_by_period(rows_submeters_hourly,
261			reporting_start_datetime_utc,
262			reporting_end_datetime_utc,
263			period_type)
264
265			for row_submeters_periodically in rows_submeters_periodically:
266			current_datetime_local = row_submeters_periodically[0].replace(tzinfo=timezone.utc) + \
267			timedelta(minutes=timezone_offset)
268			if period_type == 'hourly':
269			current_datetime = current_datetime_local.isoformat()[0:19]
270			elif period_type == 'daily':
271			current_datetime = current_datetime_local.isoformat()[0:10]
272			elif period_type == 'weekly':
273			current_datetime = current_datetime_local.isoformat()[0:10]
274			elif period_type == 'monthly':
275			current_datetime = current_datetime_local.isoformat()[0:7]
276			elif period_type == 'yearly':
277			current_datetime = current_datetime_local.isoformat()[0:4]
278
279			actual_value = Decimal(0.0) if row_submeters_periodically[1] is None else row_submeters_periodically[1]
280
281			reporting['submeters_values'].append(actual_value)
282			reporting['submeters_total_in_category'] += actual_value
283
284			################################################################################################################
285			# Step 6: calculate reporting period difference between master meter and submeters
286			################################################################################################################
287			if len(submeter_list) > 0:
288			for i in range(len(reporting['timestamps'])):
289			reporting['difference_values'].append(reporting['master_meter_values'][i] -
290			reporting['submeters_values'][i])
291			else:
292			for i in range(len(reporting['timestamps'])):
293			reporting['difference_values'].append(reporting['master_meter_values'][i])
294
295			reporting['total_difference_in_category'] = \
296			reporting['master_meter_total_in_category'] - reporting['submeters_total_in_category']
297
298			if abs(reporting['master_meter_total_in_category']) > Decimal(0.0):
299			reporting['percentage_difference'] = \
300			reporting['total_difference_in_category'] / reporting['master_meter_total_in_category']
301			elif abs(reporting['master_meter_total_in_category']) == Decimal(0.0) and \
302			abs(reporting['submeters_total_in_category']) > Decimal(0.0):
303			reporting['percentage_difference'] = Decimal(-1.0)
304
305			################################################################################################################
306			# Step 7: query submeter values as parameter data
307			################################################################################################################
308			for submeter in submeter_list:
309			submeter_timestamps = list()
310			submeter_values = list()
311
312			query = (" SELECT start_datetime_utc, actual_value "
313			" FROM tbl_meter_hourly "
314			" WHERE meter_id = %s "
315			" AND start_datetime_utc >= %s "
316			" AND start_datetime_utc < %s "
317			" ORDER BY start_datetime_utc ")
318			cursor_energy.execute(query, (submeter['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
319			rows_meter_hourly = cursor_energy.fetchall()
320
321			rows_meter_periodically = utilities.aggregate_hourly_data_by_period(rows_meter_hourly,
322			reporting_start_datetime_utc,
323			reporting_end_datetime_utc,
324			period_type)
325
326			for row_meter_periodically in rows_meter_periodically:
327			current_datetime_local = row_meter_periodically[0].replace(tzinfo=timezone.utc) + \
328			timedelta(minutes=timezone_offset)
329			if period_type == 'hourly':
330			current_datetime = current_datetime_local.isoformat()[0:19]
331			elif period_type == 'daily':
332			current_datetime = current_datetime_local.isoformat()[0:10]
333			elif period_type == 'weekly':
334			current_datetime = current_datetime_local.isoformat()[0:10]
335			elif period_type == 'monthly':
336			current_datetime = current_datetime_local.isoformat()[0:7]
337			elif period_type == 'yearly':
338			current_datetime = current_datetime_local.isoformat()[0:4]
339
340			actual_value = Decimal(0.0) if row_meter_periodically[1] is None else row_meter_periodically[1]
341
342			submeter_timestamps.append(current_datetime)
343			submeter_values.append(actual_value)
344
345			parameters_data['names'].append(submeter['name'])
346			parameters_data['timestamps'].append(submeter_timestamps)
347			parameters_data['values'].append(submeter_values)
348
349			################################################################################################################
350			# Step 8: construct the report
351			################################################################################################################
352			if cursor_system:
353			cursor_system.close()
354			if cnx_system:
355			cnx_system.close()
356
357			if cursor_energy:
358			cursor_energy.close()
359			if cnx_energy:
360			cnx_energy.close()
361
362			result = {
363			"meter": {
364			"cost_center_id": master_meter['cost_center_id'],
365			"energy_category_id": master_meter['energy_category_id'],
366			"energy_category_name": master_meter['energy_category_name'],
367			"unit_of_measure": master_meter['unit_of_measure'],
368			},
369			"reporting_period": {
370			"master_meter_consumption_in_category": reporting['master_meter_total_in_category'],
371			"submeters_consumption_in_category": reporting['submeters_total_in_category'],
372			"difference_in_category": reporting['total_difference_in_category'],
373			"percentage_difference": reporting['percentage_difference'],
374			"timestamps": reporting['timestamps'],
375			"difference_values": reporting['difference_values'],
376			},
377			"parameters": {
378			"names": parameters_data['names'],
379			"timestamps": parameters_data['timestamps'],
380			"values": parameters_data['values']
381			},
382			}
383
384			# export result to Excel file and then encode the file to base64 string
385			if not is_quick_mode:
386			result['excel_bytes_base64'] = \
387			excelexporters.metersubmetersbalance.export(result,
388			master_meter['name'],
389			reporting_period_start_datetime_local,
390			reporting_period_end_datetime_local,
391			period_type,
392			language)
393
394			resp.text = json.dumps(result)
395

MyEMS / myems

Issues (1577)

myems-api/reports/metersubmetersbalance.py (2 issues)

Severity

Introduced By

Duplication Side-by-Side

Filter issues like