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

Issues (1577)

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

Severity

Unknown 2

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


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

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

    ####################################################################################################################
    # PROCEDURES
    # Step 1: valid parameters
    # Step 2: query the tenant
    # Step 3: query energy categories
    # Step 4: query reporting period energy input
    # Step 5: query reporting period energy cost
    # Step 6: query tariff data
    # Step 7: 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)
        tenant_id = req.params.get('tenantid')
        tenant_uuid = req.params.get('tenantuuid')
        reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
        reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
        language = req.params.get('language')
        # This value is intentionally left daily
        period_type = 'daily'

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if tenant_id is None and tenant_uuid is None:
            raise falcon.HTTPError(status=falcon.HTTP_400,
                                   title='API.BAD_REQUEST',
                                   description='API.INVALID_TENANT_ID')

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

        if tenant_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(tenant_uuid))
            if not bool(match):
                raise falcon.HTTPError(status=falcon.HTTP_400,
                                       title='API.BAD_REQUEST',
                                       description='API.INVALID_TENANT_UUID')

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

        if reporting_period_start_datetime_local 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_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(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)
            # 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(status=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').replace(tzinfo=timezone.utc) - \
                    timedelta(minutes=timezone_offset)
            except ValueError:
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")

        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')

        trans = utilities.get_translation(language)
        trans.install()
        _ = trans.gettext

        ################################################################################################################
        # Step 2: query the tenant
        ################################################################################################################
        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()

        cnx_billing = mysql.connector.connect(**config.myems_billing_db)
        cursor_billing = cnx_billing.cursor()

        if tenant_id is not None:
            cursor_system.execute(" SELECT t.id, t.name, t.buildings, t.floors, t.rooms, t.lease_number, "
                                  "        c.email, c.phone, cost_center_id "
                                  " FROM tbl_tenants t, tbl_contacts c "
                                  " WHERE t.id = %s AND t.contact_id = c.id ", (tenant_id,))
            row_tenant = cursor_system.fetchone()
        elif tenant_uuid is not None:
            cursor_system.execute(" SELECT t.id, t.name, t.buildings, t.floors, t.rooms, t.lease_number, "
                                  "        c.email, c.phone, cost_center_id "
                                  " FROM tbl_tenants t, tbl_contacts c "
                                  " WHERE t.uuid = %s AND t.contact_id = c.id ", (tenant_uuid,))
            row_tenant = cursor_system.fetchone()

        if row_tenant 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()

            if cursor_billing:
                cursor_billing.close()
            if cnx_billing:
                cnx_billing.close()

            raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', description='API.TENANT_NOT_FOUND')

        tenant = dict()
        tenant['id'] = row_tenant[0]
        tenant['name'] = row_tenant[1]
        tenant['buildings'] = row_tenant[2]
        tenant['floors'] = row_tenant[3]
        tenant['rooms'] = row_tenant[4]
        tenant['lease_number'] = row_tenant[5]
        tenant['email'] = row_tenant[6]
        tenant['phone'] = row_tenant[7]
        tenant['cost_center_id'] = row_tenant[8]

        ################################################################################################################
        # Step 3: query energy categories
        ################################################################################################################
        energy_category_set = set()

        # query energy categories in reporting period
        cursor_billing.execute(" SELECT DISTINCT(energy_category_id) "
                               " FROM tbl_tenant_input_category_hourly "
                               " WHERE tenant_id = %s "
                               "     AND start_datetime_utc >= %s "
                               "     AND start_datetime_utc < %s ",
                               (tenant['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
        rows_energy_categories = cursor_billing.fetchall()
        if rows_energy_categories is not None and len(rows_energy_categories) > 0:
            for row_energy_category in rows_energy_categories:
                energy_category_set.add(row_energy_category[0])

        # query all energy categories
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
                              " FROM tbl_energy_categories "
                              " ORDER BY id ", )
        rows_energy_categories = cursor_system.fetchall()
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
            if cursor_system:
                cursor_system.close()
            if cnx_system:
                cnx_system.close()

            if cursor_energy:
                cursor_energy.close()
            if cnx_energy:
                cnx_energy.close()

            if cursor_billing:
                cursor_billing.close()
            if cnx_billing:
                cnx_billing.close()

            raise falcon.HTTPError(status=falcon.HTTP_404,
                                   title='API.NOT_FOUND',
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
        energy_category_dict = dict()
        for row_energy_category in rows_energy_categories:
            if row_energy_category[0] in energy_category_set:
                energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
                                                                "unit_of_measure": row_energy_category[2],
                                                                "kgce": row_energy_category[3],
                                                                "kgco2e": row_energy_category[4]}

        ################################################################################################################
        # Step 4: query reporting period energy input
        ################################################################################################################
        reporting_input = dict()
        if energy_category_set is not None and len(energy_category_set) > 0:
            for energy_category_id in energy_category_set:
                reporting_input[energy_category_id] = dict()
                reporting_input[energy_category_id]['timestamps'] = list()
                reporting_input[energy_category_id]['values'] = list()
                reporting_input[energy_category_id]['subtotal'] = Decimal(0.0)

                cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
                                      " FROM tbl_tenant_input_category_hourly "
                                      " WHERE tenant_id = %s "
                                      "     AND energy_category_id = %s "
                                      "     AND start_datetime_utc >= %s "
                                      "     AND start_datetime_utc < %s "
                                      " ORDER BY start_datetime_utc ",
                                      (tenant['id'],
                                       energy_category_id,
                                       reporting_start_datetime_utc,
                                       reporting_end_datetime_utc))
                rows_tenant_hourly = cursor_energy.fetchall()

                rows_tenant_periodically = utilities.aggregate_hourly_data_by_period(rows_tenant_hourly,
                                                                                     reporting_start_datetime_utc,
                                                                                     reporting_end_datetime_utc,
                                                                                     period_type)
                for row_tenant_periodically in rows_tenant_periodically:
                    current_datetime_local = row_tenant_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_tenant_periodically[1] is None else row_tenant_periodically[1]
                    reporting_input[energy_category_id]['timestamps'].append(current_datetime)

                    reporting_input[energy_category_id]['values'].append(actual_value)
                    reporting_input[energy_category_id]['subtotal'] += actual_value

        ################################################################################################################
        # Step 5: query reporting period energy cost
        ################################################################################################################
        reporting_cost = dict()
        if energy_category_set is not None and len(energy_category_set) > 0:
            for energy_category_id in energy_category_set:
                reporting_cost[energy_category_id] = dict()
                reporting_cost[energy_category_id]['timestamps'] = list()
                reporting_cost[energy_category_id]['values'] = list()
                reporting_cost[energy_category_id]['subtotal'] = Decimal(0.0)

                cursor_billing.execute(" SELECT start_datetime_utc, actual_value "
                                       " FROM tbl_tenant_input_category_hourly "
                                       " WHERE tenant_id = %s "
                                       "     AND energy_category_id = %s "
                                       "     AND start_datetime_utc >= %s "
                                       "     AND start_datetime_utc < %s "
                                       " ORDER BY start_datetime_utc ",
                                       (tenant['id'],
                                        energy_category_id,
                                        reporting_start_datetime_utc,
                                        reporting_end_datetime_utc))
                rows_tenant_hourly = cursor_billing.fetchall()

                rows_tenant_periodically = utilities.aggregate_hourly_data_by_period(rows_tenant_hourly,
                                                                                     reporting_start_datetime_utc,
                                                                                     reporting_end_datetime_utc,
                                                                                     period_type)
                for row_tenant_periodically in rows_tenant_periodically:
                    current_datetime_local = row_tenant_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_tenant_periodically[1] is None else row_tenant_periodically[1]
                    reporting_cost[energy_category_id]['timestamps'].append(current_datetime)
                    reporting_cost[energy_category_id]['values'].append(actual_value)
                    reporting_cost[energy_category_id]['subtotal'] += actual_value

        ################################################################################################################
        # Step 6: query tariff data
        ################################################################################################################
        parameters_data = dict()
        parameters_data['names'] = list()
        parameters_data['timestamps'] = list()
        parameters_data['values'] = list()
        if config.is_tariff_appended and energy_category_set is not None and len(energy_category_set) > 0:
            for energy_category_id in energy_category_set:
                energy_category_tariff_dict = utilities.get_energy_category_tariffs(tenant['cost_center_id'],
                                                                                    energy_category_id,
                                                                                    reporting_start_datetime_utc,
                                                                                    reporting_end_datetime_utc)
                tariff_timestamp_list = list()
                tariff_value_list = list()
                for k, v in energy_category_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') + '-' + energy_category_dict[energy_category_id]['name'])
                parameters_data['timestamps'].append(tariff_timestamp_list)
                parameters_data['values'].append(tariff_value_list)

        ################################################################################################################
        # Step 7: 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()

        if cursor_billing:
            cursor_billing.close()
        if cnx_billing:
            cnx_billing.close()

        result = dict()

        result['tenant'] = dict()
        result['tenant']['name'] = tenant['name']
        result['tenant']['buildings'] = tenant['buildings']
        result['tenant']['floors'] = tenant['floors']
        result['tenant']['rooms'] = tenant['rooms']
        result['tenant']['lease_number'] = tenant['lease_number']
        result['tenant']['email'] = tenant['email']
        result['tenant']['phone'] = tenant['phone']

        result['reporting_period'] = dict()
        result['reporting_period']['names'] = list()
        result['reporting_period']['energy_category_ids'] = list()
        result['reporting_period']['units'] = list()
        result['reporting_period']['subtotals_input'] = list()
        result['reporting_period']['subtotals_cost'] = list()
        result['reporting_period']['total_cost'] = Decimal(0.0)
        result['reporting_period']['currency_unit'] = config.currency_unit

        if energy_category_set is not None and len(energy_category_set) > 0:
            for energy_category_id in energy_category_set:
                result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name'])
                result['reporting_period']['energy_category_ids'].append(energy_category_id)
                result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
                result['reporting_period']['subtotals_input'].append(reporting_input[energy_category_id]['subtotal'])
                result['reporting_period']['subtotals_cost'].append(reporting_cost[energy_category_id]['subtotal'])
                result['reporting_period']['total_cost'] += reporting_cost[energy_category_id]['subtotal']

        result['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.tenantbill.export(result,
                                                                        tenant['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			import falcon
5			import mysql.connector
6			import simplejson as json
7			import config
8			import excelexporters.tenantbill
9			from core import utilities
10			from core.useractivity import access_control, api_key_control
11
12
13			class Reporting:
14			def __init__(self):
15			""""Initializes Reporting"""
16			pass
17
18			@staticmethod
19			def on_options(req, resp):
20			_ = req
21			resp.status = falcon.HTTP_200
22
23			####################################################################################################################
24			# PROCEDURES
25			# Step 1: valid parameters
26			# Step 2: query the tenant
27			# Step 3: query energy categories
28			# Step 4: query reporting period energy input
29			# Step 5: query reporting period energy cost
30			# Step 6: query tariff data
31			# Step 7: construct the report
32			####################################################################################################################
33			@staticmethod
34			def on_get(req, resp):
35			if 'API-KEY' not in req.headers or \
36			not isinstance(req.headers['API-KEY'], str) or \
37			len(str.strip(req.headers['API-KEY'])) == 0:
38			access_control(req)
39			else:
40			api_key_control(req)
41			print(req.params)
42			tenant_id = req.params.get('tenantid')
43			tenant_uuid = req.params.get('tenantuuid')
44			reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
45			reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
46			language = req.params.get('language')
47			# This value is intentionally left daily
48			period_type = 'daily'
49
50			################################################################################################################
51			# Step 1: valid parameters
52			################################################################################################################
53			if tenant_id is None and tenant_uuid is None:
54			raise falcon.HTTPError(status=falcon.HTTP_400,
55			title='API.BAD_REQUEST',
56			description='API.INVALID_TENANT_ID')
57
58			if tenant_id is not None:
59			tenant_id = str.strip(tenant_id)
60			if not tenant_id.isdigit() or int(tenant_id) <= 0:
61			raise falcon.HTTPError(status=falcon.HTTP_400,
62			title='API.BAD_REQUEST',
63			description='API.INVALID_TENANT_ID')
64
65			if tenant_uuid is not None:
66			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)
67			match = regex.match(str.strip(tenant_uuid))
68			if not bool(match):
69			raise falcon.HTTPError(status=falcon.HTTP_400,
70			title='API.BAD_REQUEST',
71			description='API.INVALID_TENANT_UUID')
72
73			timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
74			if config.utc_offset[0] == '-':
75			timezone_offset = -timezone_offset
76
77			if reporting_period_start_datetime_local is None:
78			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
79			description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
80			else:
81			reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
82			try:
83			reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
84			'%Y-%m-%dT%H:%M:%S')
85			except ValueError:
86			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
87			description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
88			reporting_start_datetime_utc = \
89			reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset)
90			# nomalize the start datetime
91			if config.minutes_to_count == 30 and reporting_start_datetime_utc.minute >= 30:
92			reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=30, second=0, microsecond=0)
93			else:
94			reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=0, second=0, microsecond=0)
95
96			if reporting_period_end_datetime_local is None:
97			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
98			description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
99			else:
100			reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local)
101			try:
102			reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local,
103			'%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
104			timedelta(minutes=timezone_offset)
105			except ValueError:
106			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
107			description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
108
109			if reporting_start_datetime_utc >= reporting_end_datetime_utc:
110			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
111			description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
112
113			trans = utilities.get_translation(language)
114			trans.install()
115			_ = trans.gettext
116
117			################################################################################################################
118			# Step 2: query the tenant
119			################################################################################################################
120			cnx_system = mysql.connector.connect(**config.myems_system_db)
121			cursor_system = cnx_system.cursor()
122
123			cnx_energy = mysql.connector.connect(**config.myems_energy_db)
124			cursor_energy = cnx_energy.cursor()
125
126			cnx_billing = mysql.connector.connect(**config.myems_billing_db)
127			cursor_billing = cnx_billing.cursor()
128
129			if tenant_id is not None:
130			cursor_system.execute(" SELECT t.id, t.name, t.buildings, t.floors, t.rooms, t.lease_number, "
131			" c.email, c.phone, cost_center_id "
132			" FROM tbl_tenants t, tbl_contacts c "
133			" WHERE t.id = %s AND t.contact_id = c.id ", (tenant_id,))
134			row_tenant = cursor_system.fetchone()
135			elif tenant_uuid is not None:
136			cursor_system.execute(" SELECT t.id, t.name, t.buildings, t.floors, t.rooms, t.lease_number, "
137			" c.email, c.phone, cost_center_id "
138			" FROM tbl_tenants t, tbl_contacts c "
139			" WHERE t.uuid = %s AND t.contact_id = c.id ", (tenant_uuid,))
140			row_tenant = cursor_system.fetchone()
141
142			if row_tenant is None:
			0 ignored issues – show introduced 2022-01-21 11:19 UTC by Report Bug Copy Issue Report Show Similar Issues like this The variable `row_tenant` does not seem to be defined for all execution paths. Loading history...
143			if cursor_system:
144			cursor_system.close()
145			if cnx_system:
146			cnx_system.close()
147
148			if cursor_energy:
149			cursor_energy.close()
150			if cnx_energy:
151			cnx_energy.close()
152
153			if cursor_billing:
154			cursor_billing.close()
155			if cnx_billing:
156			cnx_billing.close()
157
158			raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', description='API.TENANT_NOT_FOUND')
159
160			tenant = dict()
161			tenant['id'] = row_tenant[0]
162			tenant['name'] = row_tenant[1]
163			tenant['buildings'] = row_tenant[2]
164			tenant['floors'] = row_tenant[3]
165			tenant['rooms'] = row_tenant[4]
166			tenant['lease_number'] = row_tenant[5]
167			tenant['email'] = row_tenant[6]
168			tenant['phone'] = row_tenant[7]
169			tenant['cost_center_id'] = row_tenant[8]
170
171			################################################################################################################
172			# Step 3: query energy categories
173			################################################################################################################
174			energy_category_set = set()
175
176			# query energy categories in reporting period
177			cursor_billing.execute(" SELECT DISTINCT(energy_category_id) "
178			" FROM tbl_tenant_input_category_hourly "
179			" WHERE tenant_id = %s "
180			" AND start_datetime_utc >= %s "
181			" AND start_datetime_utc < %s ",
182			(tenant['id'], reporting_start_datetime_utc, reporting_end_datetime_utc))
183			rows_energy_categories = cursor_billing.fetchall()
184			if rows_energy_categories is not None and len(rows_energy_categories) > 0:
185			for row_energy_category in rows_energy_categories:
186			energy_category_set.add(row_energy_category[0])
187
188			# query all energy categories
189			cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
190			" FROM tbl_energy_categories "
191			" ORDER BY id ", )
192			rows_energy_categories = cursor_system.fetchall()
193			if rows_energy_categories is None or len(rows_energy_categories) == 0:
194			if cursor_system:
195			cursor_system.close()
196			if cnx_system:
197			cnx_system.close()
198
199			if cursor_energy:
200			cursor_energy.close()
201			if cnx_energy:
202			cnx_energy.close()
203
204			if cursor_billing:
205			cursor_billing.close()
206			if cnx_billing:
207			cnx_billing.close()
208
209			raise falcon.HTTPError(status=falcon.HTTP_404,
210			title='API.NOT_FOUND',
211			description='API.ENERGY_CATEGORY_NOT_FOUND')
212			energy_category_dict = dict()
213			for row_energy_category in rows_energy_categories:
214			if row_energy_category[0] in energy_category_set:
215			energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
216			"unit_of_measure": row_energy_category[2],
217			"kgce": row_energy_category[3],
218			"kgco2e": row_energy_category[4]}
219
220			################################################################################################################
221			# Step 4: query reporting period energy input
222			################################################################################################################
223			reporting_input = dict()
224			if energy_category_set is not None and len(energy_category_set) > 0:
225			for energy_category_id in energy_category_set:
226			reporting_input[energy_category_id] = dict()
227			reporting_input[energy_category_id]['timestamps'] = list()
228			reporting_input[energy_category_id]['values'] = list()
229			reporting_input[energy_category_id]['subtotal'] = Decimal(0.0)
230
231			cursor_energy.execute(" SELECT start_datetime_utc, actual_value "
232			" FROM tbl_tenant_input_category_hourly "
233			" WHERE tenant_id = %s "
234			" AND energy_category_id = %s "
235			" AND start_datetime_utc >= %s "
236			" AND start_datetime_utc < %s "
237			" ORDER BY start_datetime_utc ",
238			(tenant['id'],
239			energy_category_id,
240			reporting_start_datetime_utc,
241			reporting_end_datetime_utc))
242			rows_tenant_hourly = cursor_energy.fetchall()
243
244			rows_tenant_periodically = utilities.aggregate_hourly_data_by_period(rows_tenant_hourly,
245			reporting_start_datetime_utc,
246			reporting_end_datetime_utc,
247			period_type)
248			for row_tenant_periodically in rows_tenant_periodically:
249			current_datetime_local = row_tenant_periodically[0].replace(tzinfo=timezone.utc) + \
250			timedelta(minutes=timezone_offset)
251			if period_type == 'hourly':
252			current_datetime = current_datetime_local.isoformat()[0:19]
253			elif period_type == 'daily':
254			current_datetime = current_datetime_local.isoformat()[0:10]
255			elif period_type == 'weekly':
256			current_datetime = current_datetime_local.isoformat()[0:10]
257			elif period_type == 'monthly':
258			current_datetime = current_datetime_local.isoformat()[0:7]
259			elif period_type == 'yearly':
260			current_datetime = current_datetime_local.isoformat()[0:4]
261
262			actual_value = Decimal(0.0) if row_tenant_periodically[1] is None else row_tenant_periodically[1]
263			reporting_input[energy_category_id]['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...
264			reporting_input[energy_category_id]['values'].append(actual_value)
265			reporting_input[energy_category_id]['subtotal'] += actual_value
266
267			################################################################################################################
268			# Step 5: query reporting period energy cost
269			################################################################################################################
270			reporting_cost = dict()
271			if energy_category_set is not None and len(energy_category_set) > 0:
272			for energy_category_id in energy_category_set:
273			reporting_cost[energy_category_id] = dict()
274			reporting_cost[energy_category_id]['timestamps'] = list()
275			reporting_cost[energy_category_id]['values'] = list()
276			reporting_cost[energy_category_id]['subtotal'] = Decimal(0.0)
277
278			cursor_billing.execute(" SELECT start_datetime_utc, actual_value "
279			" FROM tbl_tenant_input_category_hourly "
280			" WHERE tenant_id = %s "
281			" AND energy_category_id = %s "
282			" AND start_datetime_utc >= %s "
283			" AND start_datetime_utc < %s "
284			" ORDER BY start_datetime_utc ",
285			(tenant['id'],
286			energy_category_id,
287			reporting_start_datetime_utc,
288			reporting_end_datetime_utc))
289			rows_tenant_hourly = cursor_billing.fetchall()
290
291			rows_tenant_periodically = utilities.aggregate_hourly_data_by_period(rows_tenant_hourly,
292			reporting_start_datetime_utc,
293			reporting_end_datetime_utc,
294			period_type)
295			for row_tenant_periodically in rows_tenant_periodically:
296			current_datetime_local = row_tenant_periodically[0].replace(tzinfo=timezone.utc) + \
297			timedelta(minutes=timezone_offset)
298			if period_type == 'hourly':
299			current_datetime = current_datetime_local.isoformat()[0:19]
300			elif period_type == 'daily':
301			current_datetime = current_datetime_local.isoformat()[0:10]
302			elif period_type == 'weekly':
303			current_datetime = current_datetime_local.isoformat()[0:10]
304			elif period_type == 'monthly':
305			current_datetime = current_datetime_local.isoformat()[0:7]
306			elif period_type == 'yearly':
307			current_datetime = current_datetime_local.isoformat()[0:4]
308
309			actual_value = Decimal(0.0) if row_tenant_periodically[1] is None else row_tenant_periodically[1]
310			reporting_cost[energy_category_id]['timestamps'].append(current_datetime)
311			reporting_cost[energy_category_id]['values'].append(actual_value)
312			reporting_cost[energy_category_id]['subtotal'] += actual_value
313
314			################################################################################################################
315			# Step 6: query tariff data
316			################################################################################################################
317			parameters_data = dict()
318			parameters_data['names'] = list()
319			parameters_data['timestamps'] = list()
320			parameters_data['values'] = list()
321			if config.is_tariff_appended and energy_category_set is not None and len(energy_category_set) > 0:
322			for energy_category_id in energy_category_set:
323			energy_category_tariff_dict = utilities.get_energy_category_tariffs(tenant['cost_center_id'],
324			energy_category_id,
325			reporting_start_datetime_utc,
326			reporting_end_datetime_utc)
327			tariff_timestamp_list = list()
328			tariff_value_list = list()
329			for k, v in energy_category_tariff_dict.items():
330			# convert k from utc to local
331			k = k + timedelta(minutes=timezone_offset)
332			tariff_timestamp_list.append(k.isoformat()[0:19])
333			tariff_value_list.append(v)
334
335			parameters_data['names'].append(_('Tariff') + '-' + energy_category_dict[energy_category_id]['name'])
336			parameters_data['timestamps'].append(tariff_timestamp_list)
337			parameters_data['values'].append(tariff_value_list)
338
339			################################################################################################################
340			# Step 7: construct the report
341			################################################################################################################
342			if cursor_system:
343			cursor_system.close()
344			if cnx_system:
345			cnx_system.close()
346
347			if cursor_energy:
348			cursor_energy.close()
349			if cnx_energy:
350			cnx_energy.close()
351
352			if cursor_billing:
353			cursor_billing.close()
354			if cnx_billing:
355			cnx_billing.close()
356
357			result = dict()
358
359			result['tenant'] = dict()
360			result['tenant']['name'] = tenant['name']
361			result['tenant']['buildings'] = tenant['buildings']
362			result['tenant']['floors'] = tenant['floors']
363			result['tenant']['rooms'] = tenant['rooms']
364			result['tenant']['lease_number'] = tenant['lease_number']
365			result['tenant']['email'] = tenant['email']
366			result['tenant']['phone'] = tenant['phone']
367
368			result['reporting_period'] = dict()
369			result['reporting_period']['names'] = list()
370			result['reporting_period']['energy_category_ids'] = list()
371			result['reporting_period']['units'] = list()
372			result['reporting_period']['subtotals_input'] = list()
373			result['reporting_period']['subtotals_cost'] = list()
374			result['reporting_period']['total_cost'] = Decimal(0.0)
375			result['reporting_period']['currency_unit'] = config.currency_unit
376
377			if energy_category_set is not None and len(energy_category_set) > 0:
378			for energy_category_id in energy_category_set:
379			result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name'])
380			result['reporting_period']['energy_category_ids'].append(energy_category_id)
381			result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure'])
382			result['reporting_period']['subtotals_input'].append(reporting_input[energy_category_id]['subtotal'])
383			result['reporting_period']['subtotals_cost'].append(reporting_cost[energy_category_id]['subtotal'])
384			result['reporting_period']['total_cost'] += reporting_cost[energy_category_id]['subtotal']
385
386			result['parameters'] = {
387			"names": parameters_data['names'],
388			"timestamps": parameters_data['timestamps'],
389			"values": parameters_data['values']
390			}
391
392			# export result to Excel file and then encode the file to base64 string
393			result['excel_bytes_base64'] = excelexporters.tenantbill.export(result,
394			tenant['name'],
395			reporting_period_start_datetime_local,
396			reporting_period_end_datetime_local,
397			period_type,
398			language)
399
400			resp.text = json.dumps(result)
401

MyEMS / myems

Issues (1577)

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

Severity

Introduced By

Duplication Side-by-Side

Filter issues like