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

Issues (1577)

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

Labels

Duplication 1

Severity

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


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

    @staticmethod
    def on_options(req, resp):
        _ = req
        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):
        if 'API-KEY' not in req.headers or \
                not isinstance(req.headers['API-KEY'], str) or \
                len(str.strip(req.headers['API-KEY'])) == 0:
            access_control(req)
        else:
            api_key_control(req)
        print(req.params)
        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')
        quick_mode = req.params.get('quickmode')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if offline_meter_id is None:
            raise falcon.HTTPError(status=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(status=falcon.HTTP_400,
                                       title='API.BAD_REQUEST',
                                       description='API.INVALID_OFFLINE_METER_ID')

        if period_type is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_PERIOD_TYPE')
        else:
            period_type = str.strip(period_type)
            if period_type not in ['hourly', 'daily', 'weekly', 'monthly', 'yearly']:
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description='API.INVALID_PERIOD_TYPE')

        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
        if config.utc_offset[0] == '-':
            timezone_offset = -timezone_offset

        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(status=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(status=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(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_BASE_PERIOD_END_DATETIME')

        if reporting_period_start_datetime is None:

            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
        else:
            reporting_period_start_datetime = 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(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
            reporting_start_datetime_utc = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - \
                timedelta(minutes=timezone_offset)

        if reporting_period_end_datetime is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
        else:
            reporting_period_end_datetime = 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(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
            reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
                timedelta(minutes=timezone_offset)

        if reporting_start_datetime_utc >= reporting_end_datetime_utc:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')

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

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

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

            actual_value = Decimal(0.0) if row_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']

        for index, value in enumerate(reporting['values']):
            if index < len(base['values']) and base['values'][index] != 0 and value != 0:
                reporting['rates'].append((value - base['values'][index]) / base['values'][index])
            else:
                reporting['rates'].append(None)

        ################################################################################################################
        # 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 and not is_quick_mode:
            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'] != Decimal(0.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'],
            "rates": reporting['rates'],
        }, "parameters": {
            "names": parameters_data['names'],
            "timestamps": parameters_data['timestamps'],
            "values": parameters_data['values']
        }, 'excel_bytes_base64': None}

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

MyEMS / myems

Issues (1577)

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

Labels

Severity

Introduced By

Duplication Side-by-Side

Filter issues like