Code Duplication - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

Code Duplication Length = 250-250 lines in 2 locations

myems-api/reports/offlinemeterbatch.py 1 location


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 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):
        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()

        # 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

        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]

        ################################################################################################################
        # 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()
        energy_category_set = set()

        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(),
                                              "subtotal": None}
                energy_category_set.add(row[2])

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

        # 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 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]
                        offline_meter_dict[offline_meter_id]['subtotal'] = subtotal
                        break
                # append subtotal
                # append None if energy category is not applicable
                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'],
                "subtotal": offline_meter['subtotal'],
            })

        result = {'offline_meters': offline_meter_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.offlinemeterbatch.export(result,
                                                        space_name,
                                                        reporting_period_start_datetime_local,
                                                        reporting_period_end_datetime_local,
                                                        language)
        resp.text = json.dumps(result)


myems-api/reports/virtualmeterbatch.py 1 location


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 virtual 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):
        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')

        # 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

        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]

        ################################################################################################################
        # 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 meters in the space tree
        ################################################################################################################
        virtual_meter_dict = dict()
        space_dict = dict()
        energy_category_set = set()

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

        cursor_system_db.execute(" SELECT vm.id, vm.name AS virtual_meter_name, vm.energy_category_id, "
                                 "        s.name AS space_name, "
                                 "        cc.name AS cost_center_name"
                                 " FROM tbl_spaces s, tbl_spaces_virtual_meters svm, "
                                 "      tbl_virtual_meters vm, tbl_cost_centers cc "
                                 " WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
                                 " AND svm.space_id = s.id AND svm.virtual_meter_id = vm.id "
                                 " AND vm.cost_center_id = cc.id  ", )
        rows_meters = cursor_system_db.fetchall()
        if rows_meters is not None and len(rows_meters) > 0:
            for row in rows_meters:
                virtual_meter_dict[row[0]] = {"virtual_meter_name": row[1],
                                              "energy_category_id": row[2],
                                              "space_name": row[3],
                                              "cost_center_name": row[4],
                                              "values": list(),
                                              "subtotal": None}
                energy_category_set.add(row[2])

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

        # 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 virtual_meter_id in virtual_meter_dict:

            cursor_energy_db.execute(" SELECT SUM(actual_value) "
                                     " FROM tbl_virtual_meter_hourly "
                                     " WHERE virtual_meter_id = %s "
                                     "     AND start_datetime_utc >= %s "
                                     "     AND start_datetime_utc < %s ",
                                     (virtual_meter_id,
                                      reporting_start_datetime_utc,
                                      reporting_end_datetime_utc))
            rows_meter_energy = cursor_energy_db.fetchall()
            for energy_category in energy_category_list:
                subtotal = None
                for row_meter_energy in rows_meter_energy:
                    if energy_category['id'] == virtual_meter_dict[virtual_meter_id]['energy_category_id']:
                        subtotal = row_meter_energy[0]
                        virtual_meter_dict[virtual_meter_id]['subtotal'] = subtotal
                        break
                # append subtotal
                # append None if energy category is not applicable
                virtual_meter_dict[virtual_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
        ################################################################################################################
        virtual_meter_list = list()
        for virtual_meter_id, virtual_meter in virtual_meter_dict.items():
            virtual_meter_list.append({
                "id": virtual_meter_id,
                "virtual_meter_name": virtual_meter['virtual_meter_name'],
                "space_name": virtual_meter['space_name'],
                "cost_center_name": virtual_meter['cost_center_name'],
                "values": virtual_meter['values'],
                "subtotal": virtual_meter['subtotal'],
            })

        result = {'virtual_meters': virtual_meter_list,
                  'energycategories': energy_category_list}

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


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

MyEMS / myems

Code Duplication Length = 250-250 lines in 2 locations

myems-api/reports/offlinemeterbatch.py 1 location

myems-api/reports/virtualmeterbatch.py 1 location