reports.virtualmeterenergy - 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.virtualmeterenergy F

↳ Parent: Project

Complexity

Total Complexity

Size/Duplication

Total Lines	369
Duplicated Lines	0 %

Importance

Changes

Metric	Value
wmc	60
eloc	276
dl	0
loc	369
rs	3.6
c	0
b	0
f	0

3 Methods

Rating	Name	Size	Complexity
A	Reporting.__init__()	4	1
A	Reporting.on_options()	3	1
F	Reporting.on_get()	335	58

How to fix Complexity

import re
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.virtualmeterenergy
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(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(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)
            # 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_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)
            # 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(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')

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

MyEMS / myems

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

reports.virtualmeterenergy F

Complexity

Size/Duplication

Importance

3 Methods

How to fix Complexity

Complexity

Duplication Side-by-Side

Filter issues like