reports.offlinemeterbatch.Reporting.on_options() - Code Metrics - Inspection of "Merge branch 'develop'" - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( e39e9b...029c30 )

by Guangyu

created 2022-06-10 06:27 UTC

reports.offlinemeterbatch.Reporting.on_options() A

↳ Parent: reports.offlinemeterbatch

Complexity

Conditions

Size

Total Lines	3
Code Lines	3

Duplication

Lines	3
Ratio	100 %

Importance

Changes

Metric	Value
eloc	3
dl	3
loc	3
rs	10
c	0
b	0
f	0
cc	1
nop	2

import falcon
import simplejson as json
import mysql.connector
import config
from anytree import AnyNode, LevelOrderIter
from datetime import datetime, timedelta, timezone
import excelexporters.offlinemeterbatch


class Reporting:

    @staticmethod
    def __init__():
        """"Initializes Reporting"""
        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 offline meters 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()

        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.close()
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
                                   description='API.SPACE_NOT_FOUND')
        else:
            space_name = row[0]

        ################################################################################################################
        # 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[2]] if row[2] is not None else None
                node_dict[row[0]] = AnyNode(id=row[0], parent=parent_node, name=row[1])

        ################################################################################################################
        # Step 3: query all offline meters in the space tree
        ################################################################################################################
        offline_meter_dict = dict()
        space_dict = dict()

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

        cursor_system_db.execute(" SELECT om.id, om.name AS offline_meter_name, om.energy_category_id, "
                                 "        s.name AS space_name, "
                                 "        cc.name AS cost_center_name"
                                 " FROM tbl_spaces s, tbl_spaces_offline_meters som, "
                                 "      tbl_offline_meters om, tbl_cost_centers cc "
                                 " WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
                                 " AND som.space_id = s.id AND som.offline_meter_id = om.id "
                                 " AND om.cost_center_id = cc.id  ", )
        rows_offline_meters = cursor_system_db.fetchall()
        if rows_offline_meters is not None and len(rows_offline_meters) > 0:
            for row in rows_offline_meters:
                offline_meter_dict[row[0]] = {"offline_meter_name": row[1],
                                              "energy_category_id": row[2],
                                              "space_name": row[3],
                                              "cost_center_name": row[4],
                                              "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()

        if rows_offline_meters is not None and len(rows_offline_meters) > 0:
            for row_offline_meter in rows_offline_meters:
                energy_category_set.add(row_offline_meter[2])

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

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

            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[0] in energy_category_set:
                energy_category_list.append({"id": row_energy_category[0],
                                             "name": row_energy_category[1],
                                             "unit_of_measure": row_energy_category[2]})

        ################################################################################################################
        # Step 5: query reporting period energy input
        ################################################################################################################
        for offline_meter_id in offline_meter_dict:

            cursor_energy_db.execute(" SELECT SUM(actual_value) "
                                     " FROM tbl_offline_meter_hourly "
                                     " WHERE offline_meter_id = %s "
                                     "     AND start_datetime_utc >= %s "
                                     "     AND start_datetime_utc < %s ",
                                     (offline_meter_id,
                                      reporting_start_datetime_utc,
                                      reporting_end_datetime_utc))
            rows_offline_meter_energy = cursor_energy_db.fetchall()
            for energy_category in energy_category_list:
                subtotal = None
                for row_offline_meter_energy in rows_offline_meter_energy:
                    if energy_category['id'] == offline_meter_dict[offline_meter_id]['energy_category_id']:
                        subtotal = row_offline_meter_energy[0]
                        break
                offline_meter_dict[offline_meter_id]['values'].append(subtotal)

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

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

        ################################################################################################################
        # Step 6: construct the report
        ################################################################################################################
        offline_meter_list = list()
        for offline_meter_id, offline_meter in offline_meter_dict.items():
            offline_meter_list.append({
                "id": offline_meter_id,
                "offline_meter_name": offline_meter['offline_meter_name'],
                "space_name": offline_meter['space_name'],
                "cost_center_name": offline_meter['cost_center_name'],
                "values": offline_meter['values'],
            })

        result = {'offline_meters': offline_meter_list,
                  'energycategories': energy_category_list}

        # export result to Excel file and then encode the file to base64 string
        result['excel_bytes_base64'] = \
            excelexporters.offlinemeterbatch.export(result,
                                                    space_name,
                                                    reporting_period_start_datetime_local,
                                                    reporting_period_end_datetime_local)
        resp.text = json.dumps(result)


1		import falcon
2		import simplejson as json
3		import mysql.connector
4		import config
5		from anytree import AnyNode, LevelOrderIter
6		from datetime import datetime, timedelta, timezone
7		import excelexporters.offlinemeterbatch
8
9
10	View Code Duplication	class Reporting:
		0 ignored issues – show Duplication introduced 2022-06-10 06:35 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
11		@staticmethod
12		def __init__():
13		""""Initializes Reporting"""
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 offline meters 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()
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.close()
97		raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
98		description='API.SPACE_NOT_FOUND')
99		else:
100		space_name = row[0]
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[2]] if row[2] is not None else None
115		node_dict[row[0]] = AnyNode(id=row[0], parent=parent_node, name=row[1])
116
117		################################################################################################################
118		# Step 3: query all offline meters in the space tree
119		################################################################################################################
120		offline_meter_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 om.id, om.name AS offline_meter_name, om.energy_category_id, "
127		" s.name AS space_name, "
128		" cc.name AS cost_center_name"
129		" FROM tbl_spaces s, tbl_spaces_offline_meters som, "
130		" tbl_offline_meters om, tbl_cost_centers cc "
131		" WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
132		" AND som.space_id = s.id AND som.offline_meter_id = om.id "
133		" AND om.cost_center_id = cc.id ", )
134		rows_offline_meters = cursor_system_db.fetchall()
135		if rows_offline_meters is not None and len(rows_offline_meters) > 0:
136		for row in rows_offline_meters:
137		offline_meter_dict[row[0]] = {"offline_meter_name": row[1],
138		"energy_category_id": row[2],
139		"space_name": row[3],
140		"cost_center_name": row[4],
141		"values": list()}
142
143		################################################################################################################
144		# Step 4: query energy categories
145		################################################################################################################
146		cnx_energy_db = mysql.connector.connect(**config.myems_energy_db)
147		cursor_energy_db = cnx_energy_db.cursor()
148
149		# query energy categories in reporting period
150		energy_category_set = set()
151
152		if rows_offline_meters is not None and len(rows_offline_meters) > 0:
153		for row_offline_meter in rows_offline_meters:
154		energy_category_set.add(row_offline_meter[2])
155
156		# query all energy categories
157		cursor_system_db.execute(" SELECT id, name, unit_of_measure "
158		" FROM tbl_energy_categories "
159		" ORDER BY id ", )
160		rows_energy_categories = cursor_system_db.fetchall()
161		if rows_energy_categories is None or len(rows_energy_categories) == 0:
162		if cursor_system_db:
163		cursor_system_db.close()
164		if cnx_system_db:
165		cnx_system_db.close()
166
167		if cursor_energy_db:
168		cursor_energy_db.close()
169		if cnx_energy_db:
170		cnx_energy_db.close()
171
172		raise falcon.HTTPError(falcon.HTTP_404,
173		title='API.NOT_FOUND',
174		description='API.ENERGY_CATEGORY_NOT_FOUND')
175		energy_category_list = list()
176		for row_energy_category in rows_energy_categories:
177		if row_energy_category[0] in energy_category_set:
178		energy_category_list.append({"id": row_energy_category[0],
179		"name": row_energy_category[1],
180		"unit_of_measure": row_energy_category[2]})
181
182		################################################################################################################
183		# Step 5: query reporting period energy input
184		################################################################################################################
185		for offline_meter_id in offline_meter_dict:
186
187		cursor_energy_db.execute(" SELECT SUM(actual_value) "
188		" FROM tbl_offline_meter_hourly "
189		" WHERE offline_meter_id = %s "
190		" AND start_datetime_utc >= %s "
191		" AND start_datetime_utc < %s ",
192		(offline_meter_id,
193		reporting_start_datetime_utc,
194		reporting_end_datetime_utc))
195		rows_offline_meter_energy = cursor_energy_db.fetchall()
196		for energy_category in energy_category_list:
197		subtotal = None
198		for row_offline_meter_energy in rows_offline_meter_energy:
199		if energy_category['id'] == offline_meter_dict[offline_meter_id]['energy_category_id']:
200		subtotal = row_offline_meter_energy[0]
201		break
202		offline_meter_dict[offline_meter_id]['values'].append(subtotal)
203
204		if cursor_system_db:
205		cursor_system_db.close()
206		if cnx_system_db:
207		cnx_system_db.close()
208
209		if cursor_energy_db:
210		cursor_energy_db.close()
211		if cnx_energy_db:
212		cnx_energy_db.close()
213
214		################################################################################################################
215		# Step 6: construct the report
216		################################################################################################################
217		offline_meter_list = list()
218		for offline_meter_id, offline_meter in offline_meter_dict.items():
219		offline_meter_list.append({
220		"id": offline_meter_id,
221		"offline_meter_name": offline_meter['offline_meter_name'],
222		"space_name": offline_meter['space_name'],
223		"cost_center_name": offline_meter['cost_center_name'],
224		"values": offline_meter['values'],
225		})
226
227		result = {'offline_meters': offline_meter_list,
228		'energycategories': energy_category_list}
229
230		# export result to Excel file and then encode the file to base64 string
231		result['excel_bytes_base64'] = \
232		excelexporters.offlinemeterbatch.export(result,
233		space_name,
234		reporting_period_start_datetime_local,
235		reporting_period_end_datetime_local)
236		resp.text = json.dumps(result)
237

MyEMS / myems

Push — master ( e39e9b...029c30 )

reports.offlinemeterbatch.Reporting.on_options() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like