reports.equipmentbatch - Code Metrics - Inspection of "added Equipment Batch Analysis report API" - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( ad9c71...3af729 )

by Guangyu

created 2021-07-16 10:59 UTC

reports.equipmentbatch B

↳ Parent: Project

Complexity

Total Complexity

Size/Duplication

Total Lines	242
Duplicated Lines	95.45 %

Importance

Changes

Metric	Value
wmc	43
eloc	161
dl	231
loc	242
rs	8.96
c	0
b	0
f	0

3 Methods

Rating	Name	Duplication	Size	Complexity
F	Reporting.on_get()	213	213	41
A	Reporting.__init__()	3	3	1
A	Reporting.on_options()	3	3	1

How to fix Duplicated Code 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
import excelexporters.equipmentbatch


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

        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')
        else:
            space_name = row['name']

        ################################################################################################################
        # 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 equipments in the space tree
        ################################################################################################################
        equipment_dict = dict()
        space_dict = dict()

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

        cursor_system_db.execute(" SELECT e.id, e.name AS equipment_name, s.name AS space_name, "
                                 "        cc.name AS cost_center_name, e.description "
                                 " FROM tbl_spaces s, tbl_spaces_equipments se, "
                                 " tbl_equipments e, tbl_cost_centers cc "
                                 " WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
                                 "       AND se.space_id = s.id AND se.equipment_id = e.id "
                                 "       AND e.cost_center_id = cc.id  ", )
        rows_equipments = cursor_system_db.fetchall()
        if rows_equipments is not None and len(rows_equipments) > 0:
            for row in rows_equipments:
                equipment_dict[row['id']] = {"equipment_name": row['equipment_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_equipment_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 equipment_id in equipment_dict:

            cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
                                     " FROM tbl_equipment_input_category_hourly "
                                     " WHERE equipment_id = %s "
                                     "     AND start_datetime_utc >= %s "
                                     "     AND start_datetime_utc < %s "
                                     " GROUP BY energy_category_id ",
                                     (equipment_id,
                                      reporting_start_datetime_utc,
                                      reporting_end_datetime_utc))
            rows_equipment_energy = cursor_energy_db.fetchall()
            for energy_category in energy_category_list:
                subtotal = Decimal(0.0)
                for rows_equipment_energy in rows_equipment_energy:
                    if energy_category['id'] == rows_equipment_energy[0]:
                        subtotal = rows_equipment_energy[1]
                        break
                equipment_dict[equipment_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
        ################################################################################################################
        equipment_list = list()
        for equipment_id, equipment in equipment_dict.items():
            equipment_list.append({
                "id": equipment_id,
                "equipment_name": equipment['equipment_name'],
                "space_name": equipment['space_name'],
                "cost_center_name": equipment['cost_center_name'],
                "description": equipment['description'],
                "values": equipment['values'],
            })

        result = {'equipments': equipment_list,
                  'energycategories': energy_category_list}

        # export result to Excel file and then encode the file to base64 string
        result['excel_bytes_base64'] = \
            excelexporters.equipmentbatch.export(result,
                                                 space_name,
                                                 reporting_period_start_datetime_local,
                                                 reporting_period_end_datetime_local)
        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		import excelexporters.equipmentbatch
9
10
11	View Code Duplication	class Reporting:
		0 ignored issues – show Duplication introduced 2021-03-02 03:54 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
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: build a space tree
24		# Step 3: query all equipments in the space tree
25		# Step 4: query energy categories
26		# Step 5: query reporting period energy input
27		# Step 6: construct the report
28		####################################################################################################################
29		@staticmethod
30		def on_get(req, resp):
31		print(req.params)
32		space_id = req.params.get('spaceid')
33		reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
34		reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
35
36		################################################################################################################
37		# Step 1: valid parameters
38		################################################################################################################
39		if space_id is None:
40		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
41		else:
42		space_id = str.strip(space_id)
43		if not space_id.isdigit() or int(space_id) <= 0:
44		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
45		else:
46		space_id = int(space_id)
47
48		timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
49		if config.utc_offset[0] == '-':
50		timezone_offset = -timezone_offset
51
52		if reporting_period_start_datetime_local is None:
53		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
54		description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
55		else:
56		reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
57		try:
58		reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
59		'%Y-%m-%dT%H:%M:%S')
60		except ValueError:
61		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
62		description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
63		reporting_start_datetime_utc = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - \
64		timedelta(minutes=timezone_offset)
65
66		if reporting_period_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_period_end_datetime_local = str.strip(reporting_period_end_datetime_local)
71		try:
72		reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local,
73		'%Y-%m-%dT%H:%M:%S')
74		except ValueError:
75		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
76		description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
77		reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
78		timedelta(minutes=timezone_offset)
79
80		if reporting_start_datetime_utc >= reporting_end_datetime_utc:
81		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
82		description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
83
84		cnx_system_db = mysql.connector.connect(**config.myems_system_db)
85		cursor_system_db = cnx_system_db.cursor(dictionary=True)
86
87		cursor_system_db.execute(" SELECT name "
88		" FROM tbl_spaces "
89		" WHERE id = %s ", (space_id,))
90		row = cursor_system_db.fetchone()
91
92		if row is None:
93		if cursor_system_db:
94		cursor_system_db.close()
95		if cnx_system_db:
96		cnx_system_db.disconnect()
97		raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
98		description='API.SPACE_NOT_FOUND')
99		else:
100		space_name = row['name']
101
102		################################################################################################################
103		# Step 2: build a space tree
104		################################################################################################################
105
106		query = (" SELECT id, name, parent_space_id "
107		" FROM tbl_spaces "
108		" ORDER BY id ")
109		cursor_system_db.execute(query)
110		rows_spaces = cursor_system_db.fetchall()
111		node_dict = dict()
112		if rows_spaces is not None and len(rows_spaces) > 0:
113		for row in rows_spaces:
114		parent_node = node_dict[row['parent_space_id']] if row['parent_space_id'] is not None else None
115		node_dict[row['id']] = AnyNode(id=row['id'], parent=parent_node, name=row['name'])
116
117		################################################################################################################
118		# Step 3: query all equipments in the space tree
119		################################################################################################################
120		equipment_dict = dict()
121		space_dict = dict()
122
123		for node in LevelOrderIter(node_dict[space_id]):
124		space_dict[node.id] = node.name
125
126		cursor_system_db.execute(" SELECT e.id, e.name AS equipment_name, s.name AS space_name, "
127		" cc.name AS cost_center_name, e.description "
128		" FROM tbl_spaces s, tbl_spaces_equipments se, "
129		" tbl_equipments e, tbl_cost_centers cc "
130		" WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
131		" AND se.space_id = s.id AND se.equipment_id = e.id "
132		" AND e.cost_center_id = cc.id ", )
133		rows_equipments = cursor_system_db.fetchall()
134		if rows_equipments is not None and len(rows_equipments) > 0:
135		for row in rows_equipments:
136		equipment_dict[row['id']] = {"equipment_name": row['equipment_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_equipment_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 equipment_id in equipment_dict:
189
190		cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
191		" FROM tbl_equipment_input_category_hourly "
192		" WHERE equipment_id = %s "
193		" AND start_datetime_utc >= %s "
194		" AND start_datetime_utc < %s "
195		" GROUP BY energy_category_id ",
196		(equipment_id,
197		reporting_start_datetime_utc,
198		reporting_end_datetime_utc))
199		rows_equipment_energy = cursor_energy_db.fetchall()
200		for energy_category in energy_category_list:
201		subtotal = Decimal(0.0)
202		for rows_equipment_energy in rows_equipment_energy:
203		if energy_category['id'] == rows_equipment_energy[0]:
204		subtotal = rows_equipment_energy[1]
205		break
206		equipment_dict[equipment_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		equipment_list = list()
222		for equipment_id, equipment in equipment_dict.items():
223		equipment_list.append({
224		"id": equipment_id,
225		"equipment_name": equipment['equipment_name'],
226		"space_name": equipment['space_name'],
227		"cost_center_name": equipment['cost_center_name'],
228		"description": equipment['description'],
229		"values": equipment['values'],
230		})
231
232		result = {'equipments': equipment_list,
233		'energycategories': energy_category_list}
234
235		# export result to Excel file and then encode the file to base64 string
236		result['excel_bytes_base64'] = \
237		excelexporters.equipmentbatch.export(result,
238		space_name,
239		reporting_period_start_datetime_local,
240		reporting_period_end_datetime_local)
241		resp.body = json.dumps(result)
242

MyEMS / myems

Push — master ( ad9c71...3af729 )

reports.equipmentbatch B

Complexity

Size/Duplication

Importance

3 Methods

How to fix Duplicated Code Complexity

Duplicated Code

Complexity

Duplication Side-by-Side

Filter issues like