reports.storebatch - Code Metrics - Inspection of "added Store Batch Analysis Report" - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( e26986...50d9b5 )

by Guangyu

created 2021-05-19 07:01 UTC

reports.storebatch B

↳ Parent: Project

Complexity

Total Complexity

Size/Duplication

Total Lines	240
Duplicated Lines	5.83 %

Importance

Changes

Metric	Value
wmc	43
eloc	160
dl	14
loc	240
rs	8.96
c	0
b	0
f	0

3 Methods

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

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.storebatch


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

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

        cursor_system_db.execute(" SELECT store.id, store.name AS store_name, s.name AS space_name, "
                                 "        cc.name AS cost_center_name, store.description "
                                 " FROM tbl_spaces s, tbl_spaces_stores ss, tbl_stores store, tbl_cost_centers cc "
                                 " WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
                                 "       AND ss.space_id = s.id AND ss.store_id = store.id "
                                 "       AND store.cost_center_id = cc.id  ", )
        rows_stores = cursor_system_db.fetchall()
        if rows_stores is not None and len(rows_stores) > 0:
            for row in rows_stores:
                store_dict[row['id']] = {"store_name": row['store_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_store_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 store_id in store_dict:

            cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
                                     " FROM tbl_store_input_category_hourly "
                                     " WHERE store_id = %s "
                                     "     AND start_datetime_utc >= %s "
                                     "     AND start_datetime_utc < %s "
                                     " GROUP BY energy_category_id ",
                                     (store_id,
                                      reporting_start_datetime_utc,
                                      reporting_end_datetime_utc))
            rows_store_energy = cursor_energy_db.fetchall()
            for energy_category in energy_category_list:
                subtotal = Decimal(0.0)
                for row_store_energy in rows_store_energy:
                    if energy_category['id'] == row_store_energy[0]:
                        subtotal = row_store_energy[1]
                        break
                store_dict[store_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
        ################################################################################################################
        store_list = list()
        for store_id, store in store_dict.items():
            store_list.append({
                "id": store_id,
                "store_name": store['store_name'],
                "space_name": store['space_name'],
                "cost_center_name": store['cost_center_name'],
                "description": store['description'],
                "values": store['values'],
            })

        result = {'stores': store_list,
                  'energycategories': energy_category_list}

        # export result to Excel file and then encode the file to base64 string
        result['excel_bytes_base64'] = excelexporters.storebatch.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.storebatch
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: build a space tree
24		# Step 3: query all stores 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 stores in the space tree
119		################################################################################################################
120		store_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 store.id, store.name AS store_name, s.name AS space_name, "
127		" cc.name AS cost_center_name, store.description "
128		" FROM tbl_spaces s, tbl_spaces_stores ss, tbl_stores store, tbl_cost_centers cc "
129		" WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
130		" AND ss.space_id = s.id AND ss.store_id = store.id "
131		" AND store.cost_center_id = cc.id ", )
132		rows_stores = cursor_system_db.fetchall()
133		if rows_stores is not None and len(rows_stores) > 0:
134		for row in rows_stores:
135		store_dict[row['id']] = {"store_name": row['store_name'],
136		"space_name": row['space_name'],
137		"cost_center_name": row['cost_center_name'],
138		"description": row['description'],
139		"values": list()}
140
141		################################################################################################################
142		# Step 4: query energy categories
143		################################################################################################################
144		cnx_energy_db = mysql.connector.connect(**config.myems_energy_db)
145		cursor_energy_db = cnx_energy_db.cursor()
146
147		# query energy categories in reporting period
148		energy_category_set = set()
149		cursor_energy_db.execute(" SELECT DISTINCT(energy_category_id) "
150		" FROM tbl_store_input_category_hourly "
151		" WHERE start_datetime_utc >= %s AND start_datetime_utc < %s ",
152		(reporting_start_datetime_utc, reporting_end_datetime_utc))
153		rows_energy_categories = cursor_energy_db.fetchall()
154		if rows_energy_categories is not None or len(rows_energy_categories) > 0:
155		for row_energy_category in rows_energy_categories:
156		energy_category_set.add(row_energy_category[0])
157
158		# query all energy categories
159		cursor_system_db.execute(" SELECT id, name, unit_of_measure "
160		" FROM tbl_energy_categories "
161		" ORDER BY id ", )
162		rows_energy_categories = cursor_system_db.fetchall()
163	View Code Duplication	if rows_energy_categories is None or len(rows_energy_categories) == 0:
		0 ignored issues – show Duplication introduced 2021-05-19 08:30 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
164		if cursor_system_db:
165		cursor_system_db.close()
166		if cnx_system_db:
167		cnx_system_db.disconnect()
168
169		if cursor_energy_db:
170		cursor_energy_db.close()
171		if cnx_energy_db:
172		cnx_energy_db.disconnect()
173
174		raise falcon.HTTPError(falcon.HTTP_404,
175		title='API.NOT_FOUND',
176		description='API.ENERGY_CATEGORY_NOT_FOUND')
177		energy_category_list = list()
178		for row_energy_category in rows_energy_categories:
179		if row_energy_category['id'] in energy_category_set:
180		energy_category_list.append({"id": row_energy_category['id'],
181		"name": row_energy_category['name'],
182		"unit_of_measure": row_energy_category['unit_of_measure']})
183
184		################################################################################################################
185		# Step 5: query reporting period energy input
186		################################################################################################################
187		for store_id in store_dict:
188
189		cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
190		" FROM tbl_store_input_category_hourly "
191		" WHERE store_id = %s "
192		" AND start_datetime_utc >= %s "
193		" AND start_datetime_utc < %s "
194		" GROUP BY energy_category_id ",
195		(store_id,
196		reporting_start_datetime_utc,
197		reporting_end_datetime_utc))
198		rows_store_energy = cursor_energy_db.fetchall()
199		for energy_category in energy_category_list:
200		subtotal = Decimal(0.0)
201		for row_store_energy in rows_store_energy:
202		if energy_category['id'] == row_store_energy[0]:
203		subtotal = row_store_energy[1]
204		break
205		store_dict[store_id]['values'].append(subtotal)
206
207		if cursor_system_db:
208		cursor_system_db.close()
209		if cnx_system_db:
210		cnx_system_db.disconnect()
211
212		if cursor_energy_db:
213		cursor_energy_db.close()
214		if cnx_energy_db:
215		cnx_energy_db.disconnect()
216
217		################################################################################################################
218		# Step 6: construct the report
219		################################################################################################################
220		store_list = list()
221		for store_id, store in store_dict.items():
222		store_list.append({
223		"id": store_id,
224		"store_name": store['store_name'],
225		"space_name": store['space_name'],
226		"cost_center_name": store['cost_center_name'],
227		"description": store['description'],
228		"values": store['values'],
229		})
230
231		result = {'stores': store_list,
232		'energycategories': energy_category_list}
233
234		# export result to Excel file and then encode the file to base64 string
235		result['excel_bytes_base64'] = excelexporters.storebatch.export(result,
236		space_name,
237		reporting_period_start_datetime_local,
238		reporting_period_end_datetime_local)
239		resp.body = json.dumps(result)
240

MyEMS / myems

Push — master ( e26986...50d9b5 )

reports.storebatch B

Complexity

Size/Duplication

Importance

3 Methods

How to fix Duplicated Code Complexity

Duplicated Code

Complexity

Duplication Side-by-Side

Filter issues like