reports.tenantbill - Code Metrics - myems/myems-api - Measure and Improve Code Quality continuously with Scrutinizer

reports.tenantbill F
last analyzed 2021-02-26 06:13 UTC

↳ Parent: Project

Complexity

Total Complexity

Size/Duplication

Total Lines	354
Duplicated Lines	5.37 %

Importance

Changes

Metric	Value
eloc	252
dl	19
loc	354
rs	3.36
c	0
b	0
f	0
wmc	63

3 Methods

Rating	Name	Duplication	Size	Complexity
A	Reporting.on_options()	0	3	1
A	Reporting.__init__()	0	3	1
F	Reporting.on_get()	19	324	61

How to fix Duplicated Code Complexity

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


class Reporting:
    @staticmethod
    def __init__():
        pass

    @staticmethod
    def on_options(req, resp):
        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):
        print(req.params)
        tenant_id = req.params.get('tenantid')
        reporting_start_datetime_local = req.params.get('reportingperiodstartdatetime')
        reporting_end_datetime_local = req.params.get('reportingperiodenddatetime')

        period_type = 'daily'

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

        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_start_datetime_local is None:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
        else:
            reporting_start_datetime_local = str.strip(reporting_start_datetime_local)
            try:
                reporting_start_datetime_utc = datetime.strptime(reporting_start_datetime_local,
                                                                 '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
                    timedelta(minutes=timezone_offset)
            except ValueError:
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")

        if reporting_end_datetime_local is None:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
        else:
            reporting_end_datetime_local = str.strip(reporting_end_datetime_local)
            try:
                reporting_end_datetime_utc = datetime.strptime(reporting_end_datetime_local,
                                                               '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \
                    timedelta(minutes=timezone_offset)
            except ValueError:
                raise falcon.HTTPError(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(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')

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

        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()
        if row_tenant is None:
            if cursor_system:
                cursor_system.close()
            if cnx_system:
                cnx_system.disconnect()

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

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

            raise falcon.HTTPError(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 or 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.disconnect()

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

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

            raise falcon.HTTPError(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.strftime('%Y-%m-%dT%H:%M:%S')
                    elif period_type == 'daily':
                        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_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.strftime('%Y-%m-%dT%H:%M:%S')
                    elif period_type == 'daily':
                        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_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 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][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.disconnect()

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

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

        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_start_datetime_local,
                                                                        reporting_end_datetime_local,
                                                                        period_type)

        resp.body = json.dumps(result)


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

myems / myems-api

reports.tenantbill F last analyzed 2021-02-26 06:13 UTC

Complexity

Size/Duplication

Importance

3 Methods

How to fix Duplicated Code Complexity

Duplicated Code

Complexity

Duplication Side-by-Side

Filter issues like

reports.tenantbill F
last analyzed 2021-02-26 06:13 UTC