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

Passed

Push — master ( 0e16a1...5afe6f )

by Guangyu

created 2022-10-24 07:43 UTC

reports.offlinemeterenergy.Reporting.on_get() F

↳ Parent: reports.offlinemeterenergy

Complexity

Conditions

Size

Total Lines	317
Code Lines	242

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	242
dl	0
loc	317
rs	0
c	0
b	0
f	0
cc	52
nop	2

How to fix Long Method Complexity

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

import gettext
import falcon
import mysql.connector
import simplejson as json

import config
import excelexporters.offlinemeterenergy
from core import utilities


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

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

    ####################################################################################################################
    # PROCEDURES
    # Step 1: valid parameters
    # Step 2: query the offline 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)
        offline_meter_id = req.params.get('offlinemeterid')
        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 = req.params.get('reportingperiodstartdatetime')
        reporting_period_end_datetime = req.params.get('reportingperiodenddatetime')
        language = req.params.get('language')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if offline_meter_id is None:
            raise falcon.HTTPError(falcon.HTTP_400,
                                   title='API.BAD_REQUEST',
                                   description='API.INVALID_OFFLINE_METER_ID')
        else:
            offline_meter_id = str.strip(offline_meter_id)
            if not offline_meter_id.isdigit() or int(offline_meter_id) <= 0:
                raise falcon.HTTPError(falcon.HTTP_400,
                                       title='API.BAD_REQUEST',
                                       description='API.INVALID_OFFLINE_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', '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)
            # nomalize the start datetime
            if config.minutes_to_count == 30 and base_start_datetime_utc.minute >= 30:
                base_start_datetime_utc = base_start_datetime_utc.replace(minute=30, second=0, microsecond=0)
            else:
                base_start_datetime_utc = base_start_datetime_utc.replace(minute=0, second=0, microsecond=0)

        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 is None:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
        else:
            reporting_period_start_datetime = str.strip(reporting_period_start_datetime)
            try:
                reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime, '%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 is None:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
        else:
            reporting_period_end_datetime = str.strip(reporting_period_end_datetime)
            try:
                reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime, '%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')

        locale_path = './i18n/'
        if language == 'zh_CN':
            trans = gettext.translation('myems', locale_path, languages=['zh_CN'])
        elif language == 'de':
            trans = gettext.translation('myems', locale_path, languages=['de'])
        elif language == 'en':
            trans = gettext.translation('myems', locale_path, languages=['en'])
        else:
            trans = gettext.translation('myems', locale_path, languages=['en'])
        trans.install()
        _ = trans.gettext

        ################################################################################################################
        # Step 2: query the offline 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_offline_meters m, tbl_energy_categories ec "
                              " WHERE m.id = %s AND m.energy_category_id = ec.id ", (offline_meter_id,))
        row_offline_meter = cursor_system.fetchone()
        if row_offline_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.OFFLINE_METER_NOT_FOUND')

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

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

        rows_offline_meter_periodically = utilities.aggregate_hourly_data_by_period(rows_offline_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_offline_meter_periodically in rows_offline_meter_periodically:
            current_datetime_local = row_offline_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_offline_meter_periodically[1] is None \
                else row_offline_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 * offline_meter['kgce']
            base['total_in_kgco2e'] += actual_value * offline_meter['kgco2e']
        ################################################################################################################
        # Step 4: query reporting period energy consumption
        ################################################################################################################

        query = (" SELECT start_datetime_utc, actual_value "
                 " FROM tbl_offline_meter_hourly "
                 " WHERE offline_meter_id = %s "
                 " AND start_datetime_utc >= %s "
                 " AND start_datetime_utc < %s "
                 " ORDER BY start_datetime_utc ")
        cursor_energy.execute(query, (offline_meter['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
        rows_offline_meter_hourly = cursor_energy.fetchall()

        rows_offline_meter_periodically = utilities.aggregate_hourly_data_by_period(rows_offline_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_offline_meter_periodically in rows_offline_meter_periodically:
            current_datetime_local = row_offline_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_offline_meter_periodically[1] is None \
                else row_offline_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 * offline_meter['kgce']
            reporting['total_in_kgco2e'] += actual_value * offline_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(offline_meter['cost_center_id'],
                                                                offline_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') + '-' + offline_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 = {
            "offline_meter": {
                "cost_center_id": offline_meter['cost_center_id'],
                "energy_category_id": offline_meter['energy_category_id'],
                "energy_category_name": offline_meter['energy_category_name'],
                "unit_of_measure": offline_meter['unit_of_measure'],
                "kgce": offline_meter['kgce'],
                "kgco2e": offline_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.offlinemeterenergy.export(result,
                                                     offline_meter['name'],
                                                     reporting_period_start_datetime,
                                                     reporting_period_end_datetime,
                                                     period_type,
                                                     language)

        resp.text = json.dumps(result)


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

MyEMS / myems

Push — master ( 0e16a1...5afe6f )

reports.offlinemeterenergy.Reporting.on_get() F

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like