reports.tenantbatch - Code Metrics - Inspection of "added tenant batch report" - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 97c75f...a1f49c )

by Guangyu

created 2021-05-13 00:42 UTC

reports.tenantbatch B

↳ Parent: Project

Complexity

Total Complexity

Size/Duplication

Total Lines	236
Duplicated Lines	0 %

Importance

Changes

Metric	Value
wmc	44
eloc	157
dl	0
loc	236
rs	8.8798
c	0
b	0
f	0

3 Methods

Rating	Name	Size	Complexity
A	Reporting.__init__()	3	1
F	Reporting.on_get()	208	42
A	Reporting.on_options()	3	1

How to fix Complexity

import falcon
import simplejson as json
import mysql.connector
import config
from anytree import Node, AnyNode, LevelOrderIter
from datetime import datetime, timedelta, timezone
from decimal import Decimal


class Reporting:
    @staticmethod
    def __init__():
        pass

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

    ####################################################################################################################
    # PROCEDURES
    # Step 1: valid parameters
    # Step 2: build a space tree
    # Step 3: query all tenants in the space tree
    # Step 4: query energy categories
    # Step 5: query reporting period energy input
    # Step 6: construct the report
    ####################################################################################################################
    @staticmethod
    def on_get(req, resp):
        print(req.params)
        space_id = req.params.get('spaceid')
        reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
        reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if space_id is None:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
        else:
            space_id = str.strip(space_id)
            if not space_id.isdigit() or int(space_id) <= 0:
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
            else:
                space_id = int(space_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_period_start_datetime_local is None:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
        else:
            reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
            try:
                reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
                                                                 '%Y-%m-%dT%H:%M:%S')
            except ValueError:
                raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
            reporting_start_datetime_utc = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - \
                timedelta(minutes=timezone_offset)

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

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

        if reporting_start_datetime_utc + timedelta(minutes=15) >= reporting_end_datetime_utc:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.THE_REPORTING_PERIOD_MUST_BE_LONGER_THAN_15_MINUTES')

        cnx_system_db = mysql.connector.connect(**config.myems_system_db)
        cursor_system_db = cnx_system_db.cursor(dictionary=True)

        cursor_system_db.execute(" SELECT name "
                                 " FROM tbl_spaces "
                                 " WHERE id = %s ", (space_id,))
        row = cursor_system_db.fetchone()

        if row is None:
            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.disconnect()
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
                                   description='API.SPACE_NOT_FOUND')

        ################################################################################################################
        # Step 2: build a space tree
        ################################################################################################################

        query = (" SELECT id, name, parent_space_id "
                 " FROM tbl_spaces "
                 " ORDER BY id ")
        cursor_system_db.execute(query)
        rows_spaces = cursor_system_db.fetchall()
        node_dict = dict()
        if rows_spaces is not None and len(rows_spaces) > 0:
            for row in rows_spaces:
                parent_node = node_dict[row['parent_space_id']] if row['parent_space_id'] is not None else None
                node_dict[row['id']] = AnyNode(id=row['id'], parent=parent_node, name=row['name'])

        ################################################################################################################
        # Step 3: query all tenants in the space tree
        ################################################################################################################
        tenant_dict = dict()
        space_dict = dict()

        for node in LevelOrderIter(node_dict[space_id]):
            space_dict[node.id] = node.name

        cursor_system_db.execute(" SELECT t.id, t.name AS tenant_name, s.name AS space_name, "
                                 "        cc.name AS cost_center_name, t.description "
                                 " FROM tbl_spaces s, tbl_spaces_tenants st, tbl_tenants t, tbl_cost_centers cc "
                                 " WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
                                 "       AND st.space_id = s.id AND st.tenant_id = t.id "
                                 "       AND t.cost_center_id = cc.id  ", )
        rows_tenants = cursor_system_db.fetchall()
        if rows_tenants is not None and len(rows_tenants) > 0:
            for row in rows_tenants:
                tenant_dict[row['id']] = {"tenant_name": row['tenant_name'],
                                          "space_name": row['space_name'],
                                          "cost_center_name": row['cost_center_name'],
                                          "description": row['description'],
                                          "values": list()}

        ################################################################################################################
        # Step 4: query energy categories
        ################################################################################################################
        cnx_energy_db = mysql.connector.connect(**config.myems_energy_db)
        cursor_energy_db = cnx_energy_db.cursor()

        # query energy categories in reporting period
        energy_category_set = set()
        cursor_energy_db.execute(" SELECT DISTINCT(energy_category_id) "
                                 " FROM tbl_tenant_input_category_hourly "
                                 " WHERE start_datetime_utc >= %s AND start_datetime_utc < %s ",
                                 (reporting_start_datetime_utc, reporting_end_datetime_utc))
        rows_energy_categories = cursor_energy_db.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_db.execute(" SELECT id, name, unit_of_measure "
                                 " FROM tbl_energy_categories "
                                 " ORDER BY id ", )
        rows_energy_categories = cursor_system_db.fetchall()
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.disconnect()

            if cursor_energy_db:
                cursor_energy_db.close()
            if cnx_energy_db:
                cnx_energy_db.disconnect()

            raise falcon.HTTPError(falcon.HTTP_404,
                                   title='API.NOT_FOUND',
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
        energy_category_list = list()
        for row_energy_category in rows_energy_categories:
            if row_energy_category['id'] in energy_category_set:
                energy_category_list.append({"id": row_energy_category['id'],
                                             "name": row_energy_category['name'],
                                             "unit_of_measure": row_energy_category['unit_of_measure']})

        ################################################################################################################
        # Step 5: query reporting period energy input
        ################################################################################################################
        for tenant_id in tenant_dict:

            cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
                                     " FROM tbl_tenant_input_category_hourly "
                                     " WHERE tenant_id = %s "
                                     "     AND start_datetime_utc >= %s "
                                     "     AND start_datetime_utc < %s "
                                     " GROUP BY energy_category_id ",
                                     (tenant_id,
                                      reporting_start_datetime_utc,
                                      reporting_end_datetime_utc))
            rows_tenant_energy = cursor_energy_db.fetchall()
            for energy_category in energy_category_list:
                subtotal = Decimal(0.0)
                for row_tenant_energy in rows_tenant_energy:
                    if energy_category['id'] == row_tenant_energy[0]:
                        subtotal = row_tenant_energy[1]
                        break
                tenant_dict[tenant_id]['values'].append(subtotal)

        if cursor_system_db:
            cursor_system_db.close()
        if cnx_system_db:
            cnx_system_db.disconnect()

        if cursor_energy_db:
            cursor_energy_db.close()
        if cnx_energy_db:
            cnx_energy_db.disconnect()

        ################################################################################################################
        # Step 6: construct the report
        ################################################################################################################
        tenant_list = list()
        for tenant_id, tenant in tenant_dict.items():
            tenant_list.append({
                "id": tenant_id,
                "tenant_name": tenant['tenant_name'],
                "space_name": tenant['space_name'],
                "cost_center_name": tenant['cost_center_name'],
                "description": tenant['description'],
                "values": tenant['values'],
            })

        result = {'tenants': tenant_list,
                  'energycategories': energy_category_list}

        resp.body = json.dumps(result)


1			import falcon
2			import simplejson as json
3			import mysql.connector
4			import config
5			from anytree import Node, AnyNode, LevelOrderIter
6			from datetime import datetime, timedelta, timezone
7			from decimal import Decimal
8
9
10			class Reporting:
11			@staticmethod
12			def __init__():
13			pass
14
15			@staticmethod
16			def on_options(req, resp):
17			resp.status = falcon.HTTP_200
18
19			####################################################################################################################
20			# PROCEDURES
21			# Step 1: valid parameters
22			# Step 2: build a space tree
23			# Step 3: query all tenants in the space tree
24			# Step 4: query energy categories
25			# Step 5: query reporting period energy input
26			# Step 6: construct the report
27			####################################################################################################################
28			@staticmethod
29			def on_get(req, resp):
30			print(req.params)
31			space_id = req.params.get('spaceid')
32			reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
33			reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
34
35			################################################################################################################
36			# Step 1: valid parameters
37			################################################################################################################
38			if space_id is None:
39			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
40			else:
41			space_id = str.strip(space_id)
42			if not space_id.isdigit() or int(space_id) <= 0:
43			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
44			else:
45			space_id = int(space_id)
46
47			timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
48			if config.utc_offset[0] == '-':
49			timezone_offset = -timezone_offset
50
51			if reporting_period_start_datetime_local is None:
52			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
53			description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
54			else:
55			reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
56			try:
57			reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
58			'%Y-%m-%dT%H:%M:%S')
59			except ValueError:
60			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
61			description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
62			reporting_start_datetime_utc = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - \
63			timedelta(minutes=timezone_offset)
64
65			if reporting_period_end_datetime_local is None:
66			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
67			description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
68			else:
69			reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local)
70			try:
71			reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local,
72			'%Y-%m-%dT%H:%M:%S')
73			except ValueError:
74			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
75			description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
76			reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
77			timedelta(minutes=timezone_offset)
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			if reporting_start_datetime_utc + timedelta(minutes=15) >= reporting_end_datetime_utc:
84			raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
85			description='API.THE_REPORTING_PERIOD_MUST_BE_LONGER_THAN_15_MINUTES')
86
87			cnx_system_db = mysql.connector.connect(**config.myems_system_db)
88			cursor_system_db = cnx_system_db.cursor(dictionary=True)
89
90			cursor_system_db.execute(" SELECT name "
91			" FROM tbl_spaces "
92			" WHERE id = %s ", (space_id,))
93			row = cursor_system_db.fetchone()
94
95			if row is None:
96			if cursor_system_db:
97			cursor_system_db.close()
98			if cnx_system_db:
99			cnx_system_db.disconnect()
100			raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
101			description='API.SPACE_NOT_FOUND')
102
103			################################################################################################################
104			# Step 2: build a space tree
105			################################################################################################################
106
107			query = (" SELECT id, name, parent_space_id "
108			" FROM tbl_spaces "
109			" ORDER BY id ")
110			cursor_system_db.execute(query)
111			rows_spaces = cursor_system_db.fetchall()
112			node_dict = dict()
113			if rows_spaces is not None and len(rows_spaces) > 0:
114			for row in rows_spaces:
115			parent_node = node_dict[row['parent_space_id']] if row['parent_space_id'] is not None else None
116			node_dict[row['id']] = AnyNode(id=row['id'], parent=parent_node, name=row['name'])
117
118			################################################################################################################
119			# Step 3: query all tenants in the space tree
120			################################################################################################################
121			tenant_dict = dict()
122			space_dict = dict()
123
124			for node in LevelOrderIter(node_dict[space_id]):
125			space_dict[node.id] = node.name
126
127			cursor_system_db.execute(" SELECT t.id, t.name AS tenant_name, s.name AS space_name, "
128			" cc.name AS cost_center_name, t.description "
129			" FROM tbl_spaces s, tbl_spaces_tenants st, tbl_tenants t, tbl_cost_centers cc "
130			" WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
131			" AND st.space_id = s.id AND st.tenant_id = t.id "
132			" AND t.cost_center_id = cc.id ", )
133			rows_tenants = cursor_system_db.fetchall()
134			if rows_tenants is not None and len(rows_tenants) > 0:
135			for row in rows_tenants:
136			tenant_dict[row['id']] = {"tenant_name": row['tenant_name'],
137			"space_name": row['space_name'],
138			"cost_center_name": row['cost_center_name'],
139			"description": row['description'],
140			"values": list()}
141
142			################################################################################################################
143			# Step 4: query energy categories
144			################################################################################################################
145			cnx_energy_db = mysql.connector.connect(**config.myems_energy_db)
146			cursor_energy_db = cnx_energy_db.cursor()
147
148			# query energy categories in reporting period
149			energy_category_set = set()
150			cursor_energy_db.execute(" SELECT DISTINCT(energy_category_id) "
151			" FROM tbl_tenant_input_category_hourly "
152			" WHERE start_datetime_utc >= %s AND start_datetime_utc < %s ",
153			(reporting_start_datetime_utc, reporting_end_datetime_utc))
154			rows_energy_categories = cursor_energy_db.fetchall()
155			if rows_energy_categories is not None or len(rows_energy_categories) > 0:
156			for row_energy_category in rows_energy_categories:
157			energy_category_set.add(row_energy_category[0])
158
159			# query all energy categories
160			cursor_system_db.execute(" SELECT id, name, unit_of_measure "
161			" FROM tbl_energy_categories "
162			" ORDER BY id ", )
163			rows_energy_categories = cursor_system_db.fetchall()
164			if rows_energy_categories is None or len(rows_energy_categories) == 0:
165			if cursor_system_db:
166			cursor_system_db.close()
167			if cnx_system_db:
168			cnx_system_db.disconnect()
169
170			if cursor_energy_db:
171			cursor_energy_db.close()
172			if cnx_energy_db:
173			cnx_energy_db.disconnect()
174
175			raise falcon.HTTPError(falcon.HTTP_404,
176			title='API.NOT_FOUND',
177			description='API.ENERGY_CATEGORY_NOT_FOUND')
178			energy_category_list = list()
179			for row_energy_category in rows_energy_categories:
180			if row_energy_category['id'] in energy_category_set:
181			energy_category_list.append({"id": row_energy_category['id'],
182			"name": row_energy_category['name'],
183			"unit_of_measure": row_energy_category['unit_of_measure']})
184
185			################################################################################################################
186			# Step 5: query reporting period energy input
187			################################################################################################################
188			for tenant_id in tenant_dict:
189
190			cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
191			" FROM tbl_tenant_input_category_hourly "
192			" WHERE tenant_id = %s "
193			" AND start_datetime_utc >= %s "
194			" AND start_datetime_utc < %s "
195			" GROUP BY energy_category_id ",
196			(tenant_id,
197			reporting_start_datetime_utc,
198			reporting_end_datetime_utc))
199			rows_tenant_energy = cursor_energy_db.fetchall()
200			for energy_category in energy_category_list:
201			subtotal = Decimal(0.0)
202			for row_tenant_energy in rows_tenant_energy:
203			if energy_category['id'] == row_tenant_energy[0]:
204			subtotal = row_tenant_energy[1]
205			break
206			tenant_dict[tenant_id]['values'].append(subtotal)
207
208			if cursor_system_db:
209			cursor_system_db.close()
210			if cnx_system_db:
211			cnx_system_db.disconnect()
212
213			if cursor_energy_db:
214			cursor_energy_db.close()
215			if cnx_energy_db:
216			cnx_energy_db.disconnect()
217
218			################################################################################################################
219			# Step 6: construct the report
220			################################################################################################################
221			tenant_list = list()
222			for tenant_id, tenant in tenant_dict.items():
223			tenant_list.append({
224			"id": tenant_id,
225			"tenant_name": tenant['tenant_name'],
226			"space_name": tenant['space_name'],
227			"cost_center_name": tenant['cost_center_name'],
228			"description": tenant['description'],
229			"values": tenant['values'],
230			})
231
232			result = {'tenants': tenant_list,
233			'energycategories': energy_category_list}
234
235			resp.body = json.dumps(result)
236

MyEMS / myems

Push — master ( 97c75f...a1f49c )

reports.tenantbatch B

Complexity

Size/Duplication

Importance

3 Methods

How to fix Complexity

Complexity

Duplication Side-by-Side

Filter issues like