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

Issues (382)

reports/tenantbill.py (2 issues)

Labels

Duplication 1

Severity

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 Show Similar Issues like this 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 Show Similar Issues like this 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

Issues (382)

reports/tenantbill.py (2 issues)

Labels

Severity

Introduced By

Duplication Side-by-Side

Filter issues like