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

Issues (1588)

myems-api/reports/combinedequipmentbatch.py (1 issue)

Labels

Duplication 1

Severity

Informational 1

from datetime import datetime, timedelta, timezone
from decimal import Decimal
import falcon
import mysql.connector
import simplejson as json
from anytree import AnyNode, LevelOrderIter
import config
import excelexporters.combinedequipmentbatch
from core.useractivity import access_control, api_key_control


class Reporting:

    def __init__(self):
        """"Initializes Reporting"""
        pass

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

    ####################################################################################################################
    # PROCEDURES
    # Step 1: valid parameters
    # Step 2: build a space tree
    # Step 3: query all combined 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):
        if 'API-KEY' not in req.headers or \
                not isinstance(req.headers['API-KEY'], str) or \
                len(str.strip(req.headers['API-KEY'])) == 0:
            access_control(req)
        else:
            api_key_control(req)
        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')
        language = req.params.get('language')
        quick_mode = req.params.get('quickmode')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if space_id is None:
            raise falcon.HTTPError(status=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(status=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(status=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(status=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)
            # nomalize the start datetime
            if config.minutes_to_count == 30 and reporting_start_datetime_utc.minute >= 30:
                reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=30, second=0, microsecond=0)
            else:
                reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=0, second=0, microsecond=0)

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

        # if turn quick mode on, do not return parameters data and excel file
        is_quick_mode = False
        if quick_mode is not None and \
                len(str.strip(quick_mode)) > 0 and \
                str.lower(str.strip(quick_mode)) in ('true', 't', 'on', 'yes', 'y'):
            is_quick_mode = True
        ################################################################################################################
        # 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 combined equipments in the space tree
        ################################################################################################################
        combined_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 ce.id, ce.name AS combined_equipment_name, "
                                 "        ce.uuid AS combined_equipment_uuid, s.name AS space_name, "
                                 "        cc.name AS cost_center_name, ce.description "
                                 " FROM tbl_spaces s, tbl_spaces_combined_equipments sce, "
                                 "      tbl_combined_equipments ce, tbl_cost_centers cc "
                                 " WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
                                 "       AND sce.space_id = s.id AND sce.combined_equipment_id = ce.id "
                                 "       AND ce.cost_center_id = cc.id  ", )
        rows_combined_equipments = cursor_system_db.fetchall()
        if rows_combined_equipments is not None and len(rows_combined_equipments) > 0:
            for row in rows_combined_equipments:
                combined_equipment_dict[row[0]] = {"combined_equipment_name": row[1],
                                                   "combined_equipment_uuid": row[2],
                                                   "space_name": row[3],
                                                   "cost_center_name": row[4],
                                                   "description": row[5],
                                                   "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_combined_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 and 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.close()

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

            raise falcon.HTTPError(status=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 combined_equipment_id in combined_equipment_dict:

            cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
                                     " FROM tbl_combined_equipment_input_category_hourly "
                                     " WHERE combined_equipment_id = %s "
                                     "     AND start_datetime_utc >= %s "
                                     "     AND start_datetime_utc < %s "
                                     " GROUP BY energy_category_id ",
                                     (combined_equipment_id,
                                      reporting_start_datetime_utc,
                                      reporting_end_datetime_utc))
            rows_combined_equipment_energy = cursor_energy_db.fetchall()
            for energy_category in energy_category_list:
                subtotal = Decimal(0.0)
                for row_combined_equipment_energy in rows_combined_equipment_energy:
                    if energy_category['id'] == row_combined_equipment_energy[0]:
                        subtotal = row_combined_equipment_energy[1]
                        break
                combined_equipment_dict[combined_equipment_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
        ################################################################################################################
        combined_equipment_list = list()
        for combined_equipment_id, combined_equipment in combined_equipment_dict.items():
            combined_equipment_list.append({
                "id": combined_equipment_id,
                "combined_equipment_name": combined_equipment['combined_equipment_name'],
                "combined_equipment_uuid": combined_equipment['combined_equipment_uuid'],
                "space_name": combined_equipment['space_name'],
                "cost_center_name": combined_equipment['cost_center_name'],
                "description": combined_equipment['description'],
                "values": combined_equipment['values'],
            })

        result = {'combined_equipments': combined_equipment_list, 'energycategories': energy_category_list,
                  'excel_bytes_base64': None}

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


1		from datetime import datetime, timedelta, timezone
2		from decimal import Decimal
3		import falcon
4		import mysql.connector
5		import simplejson as json
6		from anytree import AnyNode, LevelOrderIter
7		import config
8		import excelexporters.combinedequipmentbatch
9		from core.useractivity import access_control, api_key_control
10
11
12	View Code Duplication	class Reporting:
		0 ignored issues – show Duplication introduced 2024-03-15 10:26 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated in your project. Loading history...
13		def __init__(self):
14		""""Initializes Reporting"""
15		pass
16
17		@staticmethod
18		def on_options(req, resp):
19		_ = req
20		resp.status = falcon.HTTP_200
21
22		####################################################################################################################
23		# PROCEDURES
24		# Step 1: valid parameters
25		# Step 2: build a space tree
26		# Step 3: query all combined equipments in the space tree
27		# Step 4: query energy categories
28		# Step 5: query reporting period energy input
29		# Step 6: construct the report
30		####################################################################################################################
31		@staticmethod
32		def on_get(req, resp):
33		if 'API-KEY' not in req.headers or \
34		not isinstance(req.headers['API-KEY'], str) or \
35		len(str.strip(req.headers['API-KEY'])) == 0:
36		access_control(req)
37		else:
38		api_key_control(req)
39		print(req.params)
40		space_id = req.params.get('spaceid')
41		reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
42		reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
43		language = req.params.get('language')
44		quick_mode = req.params.get('quickmode')
45
46		################################################################################################################
47		# Step 1: valid parameters
48		################################################################################################################
49		if space_id is None:
50		raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
51		else:
52		space_id = str.strip(space_id)
53		if not space_id.isdigit() or int(space_id) <= 0:
54		raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
55		description='API.INVALID_SPACE_ID')
56		else:
57		space_id = int(space_id)
58
59		timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
60		if config.utc_offset[0] == '-':
61		timezone_offset = -timezone_offset
62
63		if reporting_period_start_datetime_local is None:
64		raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
65		description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
66		else:
67		reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
68		try:
69		reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
70		'%Y-%m-%dT%H:%M:%S')
71		except ValueError:
72		raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
73		description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
74		reporting_start_datetime_utc = \
75		reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset)
76		# nomalize the start datetime
77		if config.minutes_to_count == 30 and reporting_start_datetime_utc.minute >= 30:
78		reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=30, second=0, microsecond=0)
79		else:
80		reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=0, second=0, microsecond=0)
81
82		if reporting_period_end_datetime_local is None:
83		raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
84		description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
85		else:
86		reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local)
87		try:
88		reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local,
89		'%Y-%m-%dT%H:%M:%S')
90		except ValueError:
91		raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
92		description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
93		reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
94		timedelta(minutes=timezone_offset)
95
96		if reporting_start_datetime_utc >= reporting_end_datetime_utc:
97		raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
98		description='API.INVALID_REPORTING_PERIOD_END_DATETIME')
99
100		cnx_system_db = mysql.connector.connect(**config.myems_system_db)
101		cursor_system_db = cnx_system_db.cursor()
102
103		cursor_system_db.execute(" SELECT name "
104		" FROM tbl_spaces "
105		" WHERE id = %s ", (space_id,))
106		row = cursor_system_db.fetchone()
107
108		if row is None:
109		if cursor_system_db:
110		cursor_system_db.close()
111		if cnx_system_db:
112		cnx_system_db.close()
113		raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND',
114		description='API.SPACE_NOT_FOUND')
115		else:
116		space_name = row[0]
117
118		# if turn quick mode on, do not return parameters data and excel file
119		is_quick_mode = False
120		if quick_mode is not None and \
121		len(str.strip(quick_mode)) > 0 and \
122		str.lower(str.strip(quick_mode)) in ('true', 't', 'on', 'yes', 'y'):
123		is_quick_mode = True
124		################################################################################################################
125		# Step 2: build a space tree
126		################################################################################################################
127
128		query = (" SELECT id, name, parent_space_id "
129		" FROM tbl_spaces "
130		" ORDER BY id ")
131		cursor_system_db.execute(query)
132		rows_spaces = cursor_system_db.fetchall()
133		node_dict = dict()
134		if rows_spaces is not None and len(rows_spaces) > 0:
135		for row in rows_spaces:
136		parent_node = node_dict[row[2]] if row[2] is not None else None
137		node_dict[row[0]] = AnyNode(id=row[0], parent=parent_node, name=row[1])
138
139		################################################################################################################
140		# Step 3: query all combined equipments in the space tree
141		################################################################################################################
142		combined_equipment_dict = dict()
143		space_dict = dict()
144
145		for node in LevelOrderIter(node_dict[space_id]):
146		space_dict[node.id] = node.name
147
148		cursor_system_db.execute(" SELECT ce.id, ce.name AS combined_equipment_name, "
149		" ce.uuid AS combined_equipment_uuid, s.name AS space_name, "
150		" cc.name AS cost_center_name, ce.description "
151		" FROM tbl_spaces s, tbl_spaces_combined_equipments sce, "
152		" tbl_combined_equipments ce, tbl_cost_centers cc "
153		" WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
154		" AND sce.space_id = s.id AND sce.combined_equipment_id = ce.id "
155		" AND ce.cost_center_id = cc.id ", )
156		rows_combined_equipments = cursor_system_db.fetchall()
157		if rows_combined_equipments is not None and len(rows_combined_equipments) > 0:
158		for row in rows_combined_equipments:
159		combined_equipment_dict[row[0]] = {"combined_equipment_name": row[1],
160		"combined_equipment_uuid": row[2],
161		"space_name": row[3],
162		"cost_center_name": row[4],
163		"description": row[5],
164		"values": list()}
165
166		################################################################################################################
167		# Step 4: query energy categories
168		################################################################################################################
169		cnx_energy_db = mysql.connector.connect(**config.myems_energy_db)
170		cursor_energy_db = cnx_energy_db.cursor()
171
172		# query energy categories in reporting period
173		energy_category_set = set()
174		cursor_energy_db.execute(" SELECT DISTINCT(energy_category_id) "
175		" FROM tbl_combined_equipment_input_category_hourly "
176		" WHERE start_datetime_utc >= %s AND start_datetime_utc < %s ",
177		(reporting_start_datetime_utc, reporting_end_datetime_utc))
178		rows_energy_categories = cursor_energy_db.fetchall()
179		if rows_energy_categories is not None and len(rows_energy_categories) > 0:
180		for row_energy_category in rows_energy_categories:
181		energy_category_set.add(row_energy_category[0])
182
183		# query all energy categories
184		cursor_system_db.execute(" SELECT id, name, unit_of_measure "
185		" FROM tbl_energy_categories "
186		" ORDER BY id ", )
187		rows_energy_categories = cursor_system_db.fetchall()
188		if rows_energy_categories is None or len(rows_energy_categories) == 0:
189		if cursor_system_db:
190		cursor_system_db.close()
191		if cnx_system_db:
192		cnx_system_db.close()
193
194		if cursor_energy_db:
195		cursor_energy_db.close()
196		if cnx_energy_db:
197		cnx_energy_db.close()
198
199		raise falcon.HTTPError(status=falcon.HTTP_404,
200		title='API.NOT_FOUND',
201		description='API.ENERGY_CATEGORY_NOT_FOUND')
202		energy_category_list = list()
203		for row_energy_category in rows_energy_categories:
204		if row_energy_category[0] in energy_category_set:
205		energy_category_list.append({"id": row_energy_category[0],
206		"name": row_energy_category[1],
207		"unit_of_measure": row_energy_category[2]})
208
209		################################################################################################################
210		# Step 5: query reporting period energy input
211		################################################################################################################
212		for combined_equipment_id in combined_equipment_dict:
213
214		cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
215		" FROM tbl_combined_equipment_input_category_hourly "
216		" WHERE combined_equipment_id = %s "
217		" AND start_datetime_utc >= %s "
218		" AND start_datetime_utc < %s "
219		" GROUP BY energy_category_id ",
220		(combined_equipment_id,
221		reporting_start_datetime_utc,
222		reporting_end_datetime_utc))
223		rows_combined_equipment_energy = cursor_energy_db.fetchall()
224		for energy_category in energy_category_list:
225		subtotal = Decimal(0.0)
226		for row_combined_equipment_energy in rows_combined_equipment_energy:
227		if energy_category['id'] == row_combined_equipment_energy[0]:
228		subtotal = row_combined_equipment_energy[1]
229		break
230		combined_equipment_dict[combined_equipment_id]['values'].append(subtotal)
231
232		if cursor_system_db:
233		cursor_system_db.close()
234		if cnx_system_db:
235		cnx_system_db.close()
236
237		if cursor_energy_db:
238		cursor_energy_db.close()
239		if cnx_energy_db:
240		cnx_energy_db.close()
241
242		################################################################################################################
243		# Step 6: construct the report
244		################################################################################################################
245		combined_equipment_list = list()
246		for combined_equipment_id, combined_equipment in combined_equipment_dict.items():
247		combined_equipment_list.append({
248		"id": combined_equipment_id,
249		"combined_equipment_name": combined_equipment['combined_equipment_name'],
250		"combined_equipment_uuid": combined_equipment['combined_equipment_uuid'],
251		"space_name": combined_equipment['space_name'],
252		"cost_center_name": combined_equipment['cost_center_name'],
253		"description": combined_equipment['description'],
254		"values": combined_equipment['values'],
255		})
256
257		result = {'combined_equipments': combined_equipment_list, 'energycategories': energy_category_list,
258		'excel_bytes_base64': None}
259
260		# export result to Excel file and then encode the file to base64 string
261		if not is_quick_mode:
262		result['excel_bytes_base64'] = \
263		excelexporters.combinedequipmentbatch.export(result,
264		space_name,
265		reporting_period_start_datetime_local,
266		reporting_period_end_datetime_local,
267		language)
268		resp.text = json.dumps(result)
269

MyEMS / myems

Issues (1588)

myems-api/reports/combinedequipmentbatch.py (1 issue)

Labels

Severity

Introduced By

Duplication Side-by-Side

Filter issues like