reports.virtualmeterenergy.Reporting.on_get() - Code Metrics - Inspection of "Merge branch 'develop'" - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 70a01f...fc67ad )

by Guangyu

created 2022-09-27 07:55 UTC

reports.virtualmeterenergy.Reporting.on_get() F

↳ Parent: reports.virtualmeterenergy

Complexity

Conditions

Size

Total Lines	313
Code Lines	241

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	241
dl	0
loc	313
rs	0
c	0
b	0
f	0
cc	51
nop	2

How to fix Long Method Complexity

import re
from datetime import datetime, timedelta, timezone
from decimal import Decimal

import config
import excelexporters.virtualmeterenergy
import falcon
import mysql.connector
import simplejson as json
from core import utilities


class Reporting:
    @staticmethod
    def __init__():
        """"Initializes Reporting"""
        pass

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

    ####################################################################################################################
    # PROCEDURES
    # Step 1: valid parameters
    # Step 2: query the virtual meter and energy category
    # Step 3: query base period energy consumption
    # Step 4: query reporting period energy consumption
    # Step 5: query tariff data
    # Step 6: construct the report
    ####################################################################################################################
    @staticmethod
    def on_get(req, resp):
        print(req.params)
        virtual_meter_id = req.params.get('virtualmeterid')
        virtual_meter_uuid = req.params.get('virtualmeteruuid')
        period_type = req.params.get('periodtype')
        base_period_start_datetime_local = req.params.get('baseperiodstartdatetime')
        base_period_end_datetime_local = req.params.get('baseperiodenddatetime')
        reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
        reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
        language = req.params.get('language')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if virtual_meter_id is None and virtual_meter_uuid is None:
            raise falcon.HTTPError(falcon.HTTP_400,
                                   title='API.BAD_REQUEST',
                                   description='API.INVALID_VIRTUAL_METER_ID')

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

        if virtual_meter_uuid is not None:
            regex = re.compile('^[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(virtual_meter_uuid))
            if not bool(match):
                raise falcon.HTTPError(falcon.HTTP_400,
                                       title='API.BAD_REQUEST',
                                       description='API.INVALID_VIRTUAL_METER_UUID')

        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', 'weekly', '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_local is not None and len(str.strip(base_period_start_datetime_local)) > 0:
            base_period_start_datetime_local = str.strip(base_period_start_datetime_local)
            try:
                base_start_datetime_utc = datetime.strptime(base_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_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_local is not None and len(str.strip(base_period_end_datetime_local)) > 0:
            base_period_end_datetime_local = str.strip(base_period_end_datetime_local)
            try:
                base_end_datetime_utc = datetime.strptime(base_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_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 virtual 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()

        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_virtual_meters m, tbl_energy_categories ec "
                              " WHERE m.id = %s AND m.energy_category_id = ec.id ", (virtual_meter_id,))
        row_virtual_meter = cursor_system.fetchone()
        if row_virtual_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(falcon.HTTP_404, title='API.NOT_FOUND', description='API.VIRTUAL_METER_NOT_FOUND')

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

        ################################################################################################################
        # Step 3: query base period energy consumption
        ################################################################################################################
        query = (" SELECT start_datetime_utc, actual_value "
                 " FROM tbl_virtual_meter_hourly "
                 " WHERE virtual_meter_id = %s "
                 " AND start_datetime_utc >= %s "
                 " AND start_datetime_utc < %s "
                 " ORDER BY start_datetime_utc ")
        cursor_energy.execute(query, (virtual_meter['id'], base_start_datetime_utc, base_end_datetime_utc))
        rows_virtual_meter_hourly = cursor_energy.fetchall()

        rows_virtual_meter_periodically = \
            utilities.aggregate_hourly_data_by_period(rows_virtual_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_virtual_meter_periodically in rows_virtual_meter_periodically:
            current_datetime_local = row_virtual_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 == 'weekly':
                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_virtual_meter_periodically[1] is None \
                else row_virtual_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 * virtual_meter['kgce']
            base['total_in_kgco2e'] += actual_value * virtual_meter['kgco2e']

        ################################################################################################################
        # Step 3: query reporting period energy consumption
        ################################################################################################################
        query = (" SELECT start_datetime_utc, actual_value "
                 " FROM tbl_virtual_meter_hourly "
                 " WHERE virtual_meter_id = %s "
                 " AND start_datetime_utc >= %s "
                 " AND start_datetime_utc < %s "
                 " ORDER BY start_datetime_utc ")
        cursor_energy.execute(query, (virtual_meter['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
        rows_virtual_meter_hourly = cursor_energy.fetchall()

        rows_virtual_meter_periodically = utilities.aggregate_hourly_data_by_period(rows_virtual_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_virtual_meter_periodically in rows_virtual_meter_periodically:
            current_datetime_local = row_virtual_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 == 'weekly':
                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_virtual_meter_periodically[1] is None \
                else row_virtual_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 * virtual_meter['kgce']
            reporting['total_in_kgco2e'] += actual_value * virtual_meter['kgco2e']

        ################################################################################################################
        # Step 5: query tariff data
        ################################################################################################################
        parameters_data = dict()
        parameters_data['names'] = list()
        parameters_data['timestamps'] = list()
        parameters_data['values'] = list()
        if config.is_tariff_appended:
            tariff_dict = utilities.get_energy_category_tariffs(virtual_meter['cost_center_id'],
                                                                virtual_meter['energy_category_id'],
                                                                reporting_start_datetime_utc,
                                                                reporting_end_datetime_utc)
            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-' + virtual_meter['energy_category_name'])
            parameters_data['timestamps'].append(tariff_timestamp_list)
            parameters_data['values'].append(tariff_value_list)

        ################################################################################################################
        # Step 6: 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 = {
            "virtual_meter": {
                "cost_center_id": virtual_meter['cost_center_id'],
                "energy_category_id": virtual_meter['energy_category_id'],
                "energy_category_name": virtual_meter['energy_category_name'],
                "unit_of_measure": virtual_meter['unit_of_measure'],
                "kgce": virtual_meter['kgce'],
                "kgco2e": virtual_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.virtualmeterenergy.export(result,
                                                     virtual_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
5			import config
6			import excelexporters.virtualmeterenergy
7			import falcon
8			import mysql.connector
9			import simplejson as json
10			from core import utilities
11
12
13			class Reporting:
14			@staticmethod
15			def __init__():
16			""""Initializes Reporting"""
17			pass
18
19			@staticmethod
20			def on_options(req, resp):
21			resp.status = falcon.HTTP_200
22
23			####################################################################################################################
24			# PROCEDURES
25			# Step 1: valid parameters
26			# Step 2: query the virtual meter and energy category
27			# Step 3: query base period energy consumption
28			# Step 4: query reporting period energy consumption
29			# Step 5: query tariff data
30			# Step 6: construct the report
31			####################################################################################################################
32			@staticmethod
33			def on_get(req, resp):
34			print(req.params)
35			virtual_meter_id = req.params.get('virtualmeterid')
36			virtual_meter_uuid = req.params.get('virtualmeteruuid')
37			period_type = req.params.get('periodtype')
38			base_period_start_datetime_local = req.params.get('baseperiodstartdatetime')
39			base_period_end_datetime_local = req.params.get('baseperiodenddatetime')
40			reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
41			reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
42			language = req.params.get('language')
43
44			################################################################################################################
45			# Step 1: valid parameters
46			################################################################################################################
47			if virtual_meter_id is None and virtual_meter_uuid is None:
48			raise falcon.HTTPError(falcon.HTTP_400,
49			title='API.BAD_REQUEST',
50			description='API.INVALID_VIRTUAL_METER_ID')
51
52			if virtual_meter_id is not None:
53			virtual_meter_id = str.strip(virtual_meter_id)
54			if not virtual_meter_id.isdigit() or int(virtual_meter_id) <= 0:
55			raise falcon.HTTPError(falcon.HTTP_400,
56			title='API.BAD_REQUEST',
57			description='API.INVALID_VIRTUAL_METER_ID')
58
59			if virtual_meter_uuid is not None:
60			regex = re.compile('^[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)
61			match = regex.match(str.strip(virtual_meter_uuid))
62			if not bool(match):
63			raise falcon.HTTPError(falcon.HTTP_400,
64			title='API.BAD_REQUEST',
65			description='API.INVALID_VIRTUAL_METER_UUID')
66
67			if period_type is None:
68			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
69			else:
70			period_type = str.strip(period_type)
71			if period_type not in ['hourly', 'daily', 'weekly', 'monthly', 'yearly']:
72			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_PERIOD_TYPE')
73
74			timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
75			if config.utc_offset[0] == '-':
76			timezone_offset = -timezone_offset
77
78			base_start_datetime_utc = None
79			if base_period_start_datetime_local is not None and len(str.strip(base_period_start_datetime_local)) > 0:
80			base_period_start_datetime_local = str.strip(base_period_start_datetime_local)
81			try:
82			base_start_datetime_utc = datetime.strptime(base_period_start_datetime_local, '%Y-%m-%dT%H:%M:%S')
83			except ValueError:
84			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
85			description="API.INVALID_BASE_PERIOD_START_DATETIME")
86			base_start_datetime_utc = base_start_datetime_utc.replace(tzinfo=timezone.utc) - \
87			timedelta(minutes=timezone_offset)
88
89			base_end_datetime_utc = None
90			if base_period_end_datetime_local is not None and len(str.strip(base_period_end_datetime_local)) > 0:
91			base_period_end_datetime_local = str.strip(base_period_end_datetime_local)
92			try:
93			base_end_datetime_utc = datetime.strptime(base_period_end_datetime_local, '%Y-%m-%dT%H:%M:%S')
94			except ValueError:
95			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
96			description="API.INVALID_BASE_PERIOD_END_DATETIME")
97			base_end_datetime_utc = base_end_datetime_utc.replace(tzinfo=timezone.utc) - \
98			timedelta(minutes=timezone_offset)
99
100			if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \
101			base_start_datetime_utc >= base_end_datetime_utc:
102			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
103			description='API.INVALID_BASE_PERIOD_END_DATETIME')
104
105			if reporting_period_start_datetime_local is None:
106			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
107			description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
108			else:
109			reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
110			try:
111			reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
112			'%Y-%m-%dT%H:%M:%S')
113			except ValueError:
114			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
115			description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
116			reporting_start_datetime_utc = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - \
117			timedelta(minutes=timezone_offset)
118
119			if reporting_period_end_datetime_local is None:
120			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
121			description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
122			else:
123			reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local)
124			try:
125			reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local,
126			'%Y-%m-%dT%H:%M:%S')
127			except ValueError:
128			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
129			description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
130			reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
131			timedelta(minutes=timezone_offset)
132
133			if reporting_start_datetime_utc >= reporting_end_datetime_utc:
134			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
135			description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
136
137			################################################################################################################
138			# Step 2: query the virtual meter and energy category
139			################################################################################################################
140			cnx_system = mysql.connector.connect(**config.myems_system_db)
141			cursor_system = cnx_system.cursor()
142
143			cnx_energy = mysql.connector.connect(**config.myems_energy_db)
144			cursor_energy = cnx_energy.cursor()
145
146			cursor_system.execute(" SELECT m.id, m.name, m.cost_center_id, m.energy_category_id, "
147			" ec.name, ec.unit_of_measure, ec.kgce, ec.kgco2e "
148			" FROM tbl_virtual_meters m, tbl_energy_categories ec "
149			" WHERE m.id = %s AND m.energy_category_id = ec.id ", (virtual_meter_id,))
150			row_virtual_meter = cursor_system.fetchone()
151			if row_virtual_meter is None:
152			if cursor_system:
153			cursor_system.close()
154			if cnx_system:
155			cnx_system.close()
156
157			if cursor_energy:
158			cursor_energy.close()
159			if cnx_energy:
160			cnx_energy.close()
161			raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND', description='API.VIRTUAL_METER_NOT_FOUND')
162
163			virtual_meter = dict()
164			virtual_meter['id'] = row_virtual_meter[0]
165			virtual_meter['name'] = row_virtual_meter[1]
166			virtual_meter['cost_center_id'] = row_virtual_meter[2]
167			virtual_meter['energy_category_id'] = row_virtual_meter[3]
168			virtual_meter['energy_category_name'] = row_virtual_meter[4]
169			virtual_meter['unit_of_measure'] = row_virtual_meter[5]
170			virtual_meter['kgce'] = row_virtual_meter[6]
171			virtual_meter['kgco2e'] = row_virtual_meter[7]
172
173			################################################################################################################
174			# Step 3: query base period energy consumption
175			################################################################################################################
176			query = (" SELECT start_datetime_utc, actual_value "
177			" FROM tbl_virtual_meter_hourly "
178			" WHERE virtual_meter_id = %s "
179			" AND start_datetime_utc >= %s "
180			" AND start_datetime_utc < %s "
181			" ORDER BY start_datetime_utc ")
182			cursor_energy.execute(query, (virtual_meter['id'], base_start_datetime_utc, base_end_datetime_utc))
183			rows_virtual_meter_hourly = cursor_energy.fetchall()
184
185			rows_virtual_meter_periodically = \
186			utilities.aggregate_hourly_data_by_period(rows_virtual_meter_hourly,
187			base_start_datetime_utc,
188			base_end_datetime_utc,
189			period_type)
190			base = dict()
191			base['timestamps'] = list()
192			base['values'] = list()
193			base['total_in_category'] = Decimal(0.0)
194			base['total_in_kgce'] = Decimal(0.0)
195			base['total_in_kgco2e'] = Decimal(0.0)
196
197			for row_virtual_meter_periodically in rows_virtual_meter_periodically:
198			current_datetime_local = row_virtual_meter_periodically[0].replace(tzinfo=timezone.utc) + \
199			timedelta(minutes=timezone_offset)
200			if period_type == 'hourly':
201			current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
202			elif period_type == 'daily':
203			current_datetime = current_datetime_local.strftime('%Y-%m-%d')
204			elif period_type == 'weekly':
205			current_datetime = current_datetime_local.strftime('%Y-%m-%d')
206			elif period_type == 'monthly':
207			current_datetime = current_datetime_local.strftime('%Y-%m')
208			elif period_type == 'yearly':
209			current_datetime = current_datetime_local.strftime('%Y')
210
211			actual_value = Decimal(0.0) if row_virtual_meter_periodically[1] is None \
212			else row_virtual_meter_periodically[1]
213			base['timestamps'].append(current_datetime)
			0 ignored issues – show introduced 2021-03-02 03:54 UTC by Report Bug Copy Issue Report The variable `current_datetime` does not seem to be defined for all execution paths. Loading history...
214			base['values'].append(actual_value)
215			base['total_in_category'] += actual_value
216			base['total_in_kgce'] += actual_value * virtual_meter['kgce']
217			base['total_in_kgco2e'] += actual_value * virtual_meter['kgco2e']
218
219			################################################################################################################
220			# Step 3: query reporting period energy consumption
221			################################################################################################################
222			query = (" SELECT start_datetime_utc, actual_value "
223			" FROM tbl_virtual_meter_hourly "
224			" WHERE virtual_meter_id = %s "
225			" AND start_datetime_utc >= %s "
226			" AND start_datetime_utc < %s "
227			" ORDER BY start_datetime_utc ")
228			cursor_energy.execute(query, (virtual_meter['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
229			rows_virtual_meter_hourly = cursor_energy.fetchall()
230
231			rows_virtual_meter_periodically = utilities.aggregate_hourly_data_by_period(rows_virtual_meter_hourly,
232			reporting_start_datetime_utc,
233			reporting_end_datetime_utc,
234			period_type)
235			reporting = dict()
236			reporting['timestamps'] = list()
237			reporting['values'] = list()
238			reporting['total_in_category'] = Decimal(0.0)
239			reporting['total_in_kgce'] = Decimal(0.0)
240			reporting['total_in_kgco2e'] = Decimal(0.0)
241
242			for row_virtual_meter_periodically in rows_virtual_meter_periodically:
243			current_datetime_local = row_virtual_meter_periodically[0].replace(tzinfo=timezone.utc) + \
244			timedelta(minutes=timezone_offset)
245			if period_type == 'hourly':
246			current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S')
247			elif period_type == 'daily':
248			current_datetime = current_datetime_local.strftime('%Y-%m-%d')
249			elif period_type == 'weekly':
250			current_datetime = current_datetime_local.strftime('%Y-%m-%d')
251			elif period_type == 'monthly':
252			current_datetime = current_datetime_local.strftime('%Y-%m')
253			elif period_type == 'yearly':
254			current_datetime = current_datetime_local.strftime('%Y')
255
256			actual_value = Decimal(0.0) if row_virtual_meter_periodically[1] is None \
257			else row_virtual_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 * virtual_meter['kgce']
263			reporting['total_in_kgco2e'] += actual_value * virtual_meter['kgco2e']
264
265			################################################################################################################
266			# Step 5: query tariff data
267			################################################################################################################
268			parameters_data = dict()
269			parameters_data['names'] = list()
270			parameters_data['timestamps'] = list()
271			parameters_data['values'] = list()
272			if config.is_tariff_appended:
273			tariff_dict = utilities.get_energy_category_tariffs(virtual_meter['cost_center_id'],
274			virtual_meter['energy_category_id'],
275			reporting_start_datetime_utc,
276			reporting_end_datetime_utc)
277			tariff_timestamp_list = list()
278			tariff_value_list = list()
279			for k, v in tariff_dict.items():
280			# convert k from utc to local
281			k = k + timedelta(minutes=timezone_offset)
282			tariff_timestamp_list.append(k.isoformat()[0:19])
283			tariff_value_list.append(v)
284
285			parameters_data['names'].append('TARIFF-' + virtual_meter['energy_category_name'])
286			parameters_data['timestamps'].append(tariff_timestamp_list)
287			parameters_data['values'].append(tariff_value_list)
288
289			################################################################################################################
290			# Step 6: construct the report
291			################################################################################################################
292			if cursor_system:
293			cursor_system.close()
294			if cnx_system:
295			cnx_system.close()
296
297			if cursor_energy:
298			cursor_energy.close()
299			if cnx_energy:
300			cnx_energy.close()
301
302			result = {
303			"virtual_meter": {
304			"cost_center_id": virtual_meter['cost_center_id'],
305			"energy_category_id": virtual_meter['energy_category_id'],
306			"energy_category_name": virtual_meter['energy_category_name'],
307			"unit_of_measure": virtual_meter['unit_of_measure'],
308			"kgce": virtual_meter['kgce'],
309			"kgco2e": virtual_meter['kgco2e'],
310			},
311			"base_period": {
312			"total_in_category": base['total_in_category'],
313			"total_in_kgce": base['total_in_kgce'],
314			"total_in_kgco2e": base['total_in_kgco2e'],
315			"timestamps": base['timestamps'],
316			"values": base['values'],
317			},
318			"reporting_period": {
319			"increment_rate":
320			(reporting['total_in_category'] - base['total_in_category']) / base['total_in_category']
321			if base['total_in_category'] > 0 else None,
322			"total_in_category": reporting['total_in_category'],
323			"total_in_kgce": reporting['total_in_kgce'],
324			"total_in_kgco2e": reporting['total_in_kgco2e'],
325			"timestamps": reporting['timestamps'],
326			"values": reporting['values'],
327			},
328			"parameters": {
329			"names": parameters_data['names'],
330			"timestamps": parameters_data['timestamps'],
331			"values": parameters_data['values']
332			},
333			}
334
335			# export result to Excel file and then encode the file to base64 string
336			result['excel_bytes_base64'] = \
337			excelexporters.virtualmeterenergy.export(result,
338			virtual_meter['name'],
339			reporting_period_start_datetime_local,
340			reporting_period_end_datetime_local,
341			period_type,
342			language)
343
344			resp.text = json.dumps(result)
345

MyEMS / myems

Push — master ( 70a01f...fc67ad )

reports.virtualmeterenergy.Reporting.on_get() F

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like