reports.virtualmeterenergy.Reporting.__init__() - 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.init() A

↳ Parent: reports.virtualmeterenergy

Complexity

Conditions

Size

Total Lines	4
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	3
dl	0
loc	4
rs	10
c	0
b	0
f	0
cc	1
nop	0

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.__init__() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

reports.virtualmeterenergy.Reporting.init() A