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

Passed

Push — master ( 467911...05a995 )

unknown

created 2025-10-31 14:22 UTC

reports.storecomparison.Reporting.on_options() A

↳ Parent: reports.storecomparison

Complexity

Conditions

Size

Total Lines	4
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

import re
from datetime import datetime, timedelta, timezone
from decimal import Decimal
import falcon
import mysql.connector
import simplejson as json
import config
import excelexporters.storecomparison
from core import utilities
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: query the store and energy category
    # Step 3: query store input category hourly data (pre-aggregated by background service)
    # Step 4: aggregate store energy consumption data by period
    # Step 5: query store associated points data
    # 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)
        # this procedure accepts store id or store uuid to identify a store
        store_id1 = req.params.get("storeid1")
        store_uuid1 = req.params.get("storeuuid1")
        store_id2 = req.params.get("storeid2")
        store_uuid2 = req.params.get("storeuuid2")
        energy_category_id = req.params.get("energycategoryid")
        period_type = req.params.get("periodtype")
        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 store_id1 is None and store_uuid1 is None:
            raise falcon.HTTPError(
                status=falcon.HTTP_400,
                title="API.BAD_REQUEST",
                description="API.INVALID_STORE_ID",
            )

        if store_id1 is not None:
            store_id1 = str.strip(store_id1)
            if not store_id1.isdigit() or int(store_id1) <= 0:
                raise falcon.HTTPError(
                    status=falcon.HTTP_400,
                    title="API.BAD_REQUEST",
                    description="API.INVALID_STORE_ID",
                )

        if store_uuid1 is not None:
            regex = re.compile(
                r"^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z",
                re.I,
            )
            match = regex.match(str.strip(store_uuid1))
            if not bool(match):
                raise falcon.HTTPError(
                    status=falcon.HTTP_400,
                    title="API.BAD_REQUEST",
                    description="API.INVALID_STORE_ID",
                )

        if store_id2 is None and store_uuid2 is None:
            raise falcon.HTTPError(
                status=falcon.HTTP_400,
                title="API.BAD_REQUEST",
                description="API.INVALID_STORE_ID",
            )

        if store_id2 is not None:
            store_id2 = str.strip(store_id2)
            if not store_id2.isdigit() or int(store_id2) <= 0:
                raise falcon.HTTPError(
                    status=falcon.HTTP_400,
                    title="API.BAD_REQUEST",
                    description="API.INVALID_STORE_ID",
                )

        if store_uuid2 is not None:
            regex = re.compile(
                r"^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z",
                re.I,
            )
            match = regex.match(str.strip(store_uuid2))
            if not bool(match):
                raise falcon.HTTPError(
                    status=falcon.HTTP_400,
                    title="API.BAD_REQUEST",
                    description="API.INVALID_STORE_ID",
                )

        if energy_category_id is None:

            raise falcon.HTTPError(
                status=falcon.HTTP_400,
                title="API.BAD_REQUEST",
                description="API.INVALID_ENERGY_CATEGORY_ID",
            )
        else:
            energy_category_id = str.strip(energy_category_id)
            if not energy_category_id.isdigit() or int(energy_category_id) <= 0:
                raise falcon.HTTPError(
                    status=falcon.HTTP_400,
                    title="API.BAD_REQUEST",
                    description="API.INVALID_ENERGY_CATEGORY_ID",
                )

        if period_type is None:

            raise falcon.HTTPError(
                status=falcon.HTTP_400,
                title="API.BAD_REQUEST",
                description="API.INVALID_PERIOD_TYPE",
            )
        else:
            period_type = str.strip(period_type)
            if period_type not in ["hourly", "daily", "weekly", "monthly", "yearly"]:
                raise falcon.HTTPError(
                    status=falcon.HTTP_400,
                    title="API.BAD_REQUEST",
                    description="API.INVALID_PERIOD_TYPE",
                )

        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

        ################################################################################################################
        # Step 2: query the store and energy category
        ################################################################################################################
        cnx_system = mysql.connector.connect(**config.myems_system_db)
        cursor_system = cnx_system.cursor()

        cnx_energy = mysql.connector.connect(**config.myems_energy_db)
        cursor_energy = cnx_energy.cursor()

        cnx_historical = mysql.connector.connect(**config.myems_historical_db)
        cursor_historical = cnx_historical.cursor()

        # Query store 1
        if store_id1 is not None:
            cursor_system.execute(
                " SELECT id, name FROM tbl_stores WHERE id = %s ", (store_id1,)
            )
            row_store1 = cursor_system.fetchone()
        elif store_uuid1 is not None:
            cursor_system.execute(
                " SELECT id, name FROM tbl_stores WHERE uuid = %s ",
                (store_uuid1,),
            )
            row_store1 = cursor_system.fetchone()

        if row_store1 is None:

            if cursor_system:
                cursor_system.close()
            if cnx_system:
                cnx_system.close()

            if cursor_energy:
                cursor_energy.close()
            if cnx_energy:
                cnx_energy.close()

            if cursor_historical:
                cursor_historical.close()
            if cnx_historical:
                cnx_historical.close()
            raise falcon.HTTPError(
                status=falcon.HTTP_404,
                title="API.NOT_FOUND",
                description="API.STORE_NOT_FOUND",
            )

        store1 = dict()
        store1["id"] = row_store1[0]
        store1["name"] = row_store1[1]

        # Query store 2
        if store_id2 is not None:
            cursor_system.execute(
                " SELECT id, name FROM tbl_stores WHERE id = %s ", (store_id2,)
            )
            row_store2 = cursor_system.fetchone()
        elif store_uuid2 is not None:
            cursor_system.execute(
                " SELECT id, name FROM tbl_stores WHERE uuid = %s ",
                (store_uuid2,),
            )
            row_store2 = cursor_system.fetchone()

        if row_store2 is None:

            if cursor_system:
                cursor_system.close()
            if cnx_system:
                cnx_system.close()

            if cursor_energy:
                cursor_energy.close()
            if cnx_energy:
                cnx_energy.close()

            if cursor_historical:
                cursor_historical.close()
            if cnx_historical:
                cnx_historical.close()
            raise falcon.HTTPError(
                status=falcon.HTTP_404,
                title="API.NOT_FOUND",
                description="API.STORE_NOT_FOUND",
            )

        store2 = dict()
        store2["id"] = row_store2[0]
        store2["name"] = row_store2[1]

        # Query energy category
        cursor_system.execute(
            " SELECT id, name, unit_of_measure FROM tbl_energy_categories WHERE id = %s ",
            (energy_category_id,),
        )
        row_energy_category = cursor_system.fetchone()

        if row_energy_category is None:

            if cursor_system:
                cursor_system.close()
            if cnx_system:
                cnx_system.close()

            if cursor_energy:
                cursor_energy.close()
            if cnx_energy:
                cnx_energy.close()

            if cursor_historical:
                cursor_historical.close()
            if cnx_historical:
                cnx_historical.close()
            raise falcon.HTTPError(
                status=falcon.HTTP_404,
                title="API.NOT_FOUND",
                description="API.ENERGY_CATEGORY_NOT_FOUND",
            )

        energy_category = dict()
        energy_category["id"] = row_energy_category[0]
        energy_category["name"] = row_energy_category[1]
        energy_category["unit_of_measure"] = row_energy_category[2]

        ################################################################################################################
        # Step 3: query store input category hourly data (pre-aggregated by background service)
        ################################################################################################################
        # Query store 1 input category hourly data
        cursor_energy.execute(
            " SELECT start_datetime_utc, actual_value "
            " FROM tbl_store_input_category_hourly "
            " WHERE store_id = %s "
            "     AND energy_category_id = %s "
            "     AND start_datetime_utc >= %s "
            "     AND start_datetime_utc < %s "
            " ORDER BY start_datetime_utc ",
            (
                store1["id"],
                energy_category_id,
                reporting_start_datetime_utc,
                reporting_end_datetime_utc,
            ),
        )
        rows_store1_hourly = cursor_energy.fetchall()

        # Query store 2 input category hourly data
        cursor_energy.execute(
            " SELECT start_datetime_utc, actual_value "
            " FROM tbl_store_input_category_hourly "
            " WHERE store_id = %s "
            "     AND energy_category_id = %s "
            "     AND start_datetime_utc >= %s "
            "     AND start_datetime_utc < %s "
            " ORDER BY start_datetime_utc ",
            (
                store2["id"],
                energy_category_id,
                reporting_start_datetime_utc,
                reporting_end_datetime_utc,
            ),
        )
        rows_store2_hourly = cursor_energy.fetchall()

        ################################################################################################################
        # Step 4: aggregate store energy consumption data by period
        ################################################################################################################
        # Aggregate energy consumption for store 1
        store1_energy_data = dict()
        store1_energy_data["timestamps"] = list()
        store1_energy_data["values"] = list()
        store1_energy_data["total_in_category"] = Decimal(0.0)

        # Aggregate store 1 hourly data by period
        rows_store1_periodically = utilities.aggregate_hourly_data_by_period(
            rows_store1_hourly,
            reporting_start_datetime_utc,
            reporting_end_datetime_utc,
            period_type,
        )

        for row_store1_periodically in rows_store1_periodically:
            current_datetime_local = row_store1_periodically[0].replace(
                tzinfo=timezone.utc
            ) + timedelta(minutes=timezone_offset)
            if period_type == "hourly":
                current_datetime = current_datetime_local.isoformat()[0:19]
            elif period_type == "daily":
                current_datetime = current_datetime_local.isoformat()[0:10]
            elif period_type == "weekly":
                current_datetime = current_datetime_local.isoformat()[0:10]
            elif period_type == "monthly":
                current_datetime = current_datetime_local.isoformat()[0:7]
            elif period_type == "yearly":
                current_datetime = current_datetime_local.isoformat()[0:4]

            actual_value = row_store1_periodically[1]

            store1_energy_data["timestamps"].append(current_datetime)

            store1_energy_data["values"].append(actual_value)
            if actual_value is not None:
                store1_energy_data["total_in_category"] += actual_value

        # Aggregate energy consumption for store 2
        store2_energy_data = dict()
        store2_energy_data["timestamps"] = list()
        store2_energy_data["values"] = list()
        store2_energy_data["total_in_category"] = Decimal(0.0)

        # Aggregate store 2 hourly data by period
        rows_store2_periodically = utilities.aggregate_hourly_data_by_period(
            rows_store2_hourly,
            reporting_start_datetime_utc,
            reporting_end_datetime_utc,
            period_type,
        )

        for row_store2_periodically in rows_store2_periodically:
            current_datetime_local = row_store2_periodically[0].replace(
                tzinfo=timezone.utc
            ) + timedelta(minutes=timezone_offset)
            if period_type == "hourly":
                current_datetime = current_datetime_local.isoformat()[0:19]
            elif period_type == "daily":
                current_datetime = current_datetime_local.isoformat()[0:10]
            elif period_type == "weekly":
                current_datetime = current_datetime_local.isoformat()[0:10]
            elif period_type == "monthly":
                current_datetime = current_datetime_local.isoformat()[0:7]
            elif period_type == "yearly":
                current_datetime = current_datetime_local.isoformat()[0:4]

            actual_value = row_store2_periodically[1]

            store2_energy_data["timestamps"].append(current_datetime)
            store2_energy_data["values"].append(actual_value)
            if actual_value is not None:
                store2_energy_data["total_in_category"] += actual_value

        # Calculate difference
        diff = dict()
        diff["values"] = list()
        diff["total_in_category"] = Decimal(0.0)

        # Ensure both stores have the same number of data points
        min_length = min(
            len(store1_energy_data["values"]), len(store2_energy_data["values"])
        )
        for i in range(min_length):
            store1_value = (
                store1_energy_data["values"][i]
                if i < len(store1_energy_data["values"])
                else None
            )
            store2_value = (
                store2_energy_data["values"][i]
                if i < len(store2_energy_data["values"])
                else None
            )
            
            # Calculate difference, handling None values
            if store1_value is None and store2_value is None:
                diff_value = None
            elif store1_value is None:
                diff_value = None  # Cannot calculate difference when one value is missing
            elif store2_value is None:
                diff_value = None  # Cannot calculate difference when one value is missing
            else:
                diff_value = store1_value - store2_value
                diff["total_in_category"] += diff_value
                
            diff["values"].append(diff_value)

        ################################################################################################################
        # Step 5: query store associated points data (for detailed parameters)
        ################################################################################################################
        parameters_data1 = dict()
        parameters_data1["names"] = list()
        parameters_data1["timestamps"] = list()
        parameters_data1["values"] = list()

        parameters_data2 = dict()
        parameters_data2["names"] = list()
        parameters_data2["timestamps"] = list()
        parameters_data2["values"] = list()

        ################################################################################################################
        # Step 6: construct the report
        ################################################################################################################
        if cursor_system:
            cursor_system.close()
        if cnx_system:
            cnx_system.close()

        if cursor_energy:
            cursor_energy.close()
        if cnx_energy:
            cnx_energy.close()

        if cursor_historical:
            cursor_historical.close()
        if cnx_historical:
            cnx_historical.close()

        result = {
            "store1": {
                "id": store1["id"],
                "name": store1["name"],
            },
            "store2": {
                "id": store2["id"],
                "name": store2["name"],
            },
            "energy_category": {
                "id": energy_category["id"],
                "name": energy_category["name"],
                "unit_of_measure": energy_category["unit_of_measure"],
            },
            "reporting_period1": {
                "total_in_category": store1_energy_data["total_in_category"],
                "timestamps": store1_energy_data["timestamps"],
                "values": store1_energy_data["values"],
            },
            "reporting_period2": {
                "total_in_category": store2_energy_data["total_in_category"],
                "timestamps": store2_energy_data["timestamps"],
                "values": store2_energy_data["values"],
            },
            "parameters1": {
                "names": parameters_data1["names"],
                "timestamps": parameters_data1["timestamps"],
                "values": parameters_data1["values"],
            },
            "parameters2": {
                "names": parameters_data2["names"],
                "timestamps": parameters_data2["timestamps"],
                "values": parameters_data2["values"],
            },
            "diff": {
                "values": diff["values"],
                "total_in_category": diff["total_in_category"],
            },
        }

        # export result to Excel file and then encode the file to base64 string
        if not is_quick_mode:
            result["excel_bytes_base64"] = excelexporters.storecomparison.export(
                result,
                store1["name"],
                store2["name"],
                energy_category["name"],
                reporting_period_start_datetime_local,
                reporting_period_end_datetime_local,
                period_type,
                language,
            )

        resp.text = json.dumps(result)


1		import re
2		from datetime import datetime, timedelta, timezone
3		from decimal import Decimal
4		import falcon
5		import mysql.connector
6		import simplejson as json
7		import config
8		import excelexporters.storecomparison
9		from core import utilities
10		from core.useractivity import access_control, api_key_control
11
12
13		class Reporting:
14		def __init__(self):
15		""" "Initializes Reporting"""
16		pass
17
18		@staticmethod
19		def on_options(req, resp):
20		_ = req
21		resp.status = falcon.HTTP_200
22
23		####################################################################################################################
24		# PROCEDURES
25		# Step 1: valid parameters
26		# Step 2: query the store and energy category
27		# Step 3: query store input category hourly data (pre-aggregated by background service)
28		# Step 4: aggregate store energy consumption data by period
29		# Step 5: query store associated points data
30		# Step 6: construct the report
31		####################################################################################################################
32		@staticmethod
33		def on_get(req, resp):
34		if (
35		"API-KEY" not in req.headers
36		or not isinstance(req.headers["API-KEY"], str)
37		or len(str.strip(req.headers["API-KEY"])) == 0
38		):
39		access_control(req)
40		else:
41		api_key_control(req)
42		print(req.params)
43		# this procedure accepts store id or store uuid to identify a store
44		store_id1 = req.params.get("storeid1")
45		store_uuid1 = req.params.get("storeuuid1")
46		store_id2 = req.params.get("storeid2")
47		store_uuid2 = req.params.get("storeuuid2")
48		energy_category_id = req.params.get("energycategoryid")
49		period_type = req.params.get("periodtype")
50		reporting_period_start_datetime_local = req.params.get(
51		"reportingperiodstartdatetime"
52		)
53		reporting_period_end_datetime_local = req.params.get(
54		"reportingperiodenddatetime"
55		)
56		language = req.params.get("language")
57		quick_mode = req.params.get("quickmode")
58
59		################################################################################################################
60		# Step 1: valid parameters
61		################################################################################################################
62		if store_id1 is None and store_uuid1 is None:
63		raise falcon.HTTPError(
64		status=falcon.HTTP_400,
65		title="API.BAD_REQUEST",
66		description="API.INVALID_STORE_ID",
67		)
68
69		if store_id1 is not None:
70		store_id1 = str.strip(store_id1)
71		if not store_id1.isdigit() or int(store_id1) <= 0:
72		raise falcon.HTTPError(
73		status=falcon.HTTP_400,
74		title="API.BAD_REQUEST",
75		description="API.INVALID_STORE_ID",
76		)
77
78		if store_uuid1 is not None:
79		regex = re.compile(
80		r"^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z",
81		re.I,
82		)
83		match = regex.match(str.strip(store_uuid1))
84		if not bool(match):
85		raise falcon.HTTPError(
86		status=falcon.HTTP_400,
87		title="API.BAD_REQUEST",
88		description="API.INVALID_STORE_ID",
89		)
90
91		if store_id2 is None and store_uuid2 is None:
92		raise falcon.HTTPError(
93		status=falcon.HTTP_400,
94		title="API.BAD_REQUEST",
95		description="API.INVALID_STORE_ID",
96		)
97
98		if store_id2 is not None:
99		store_id2 = str.strip(store_id2)
100		if not store_id2.isdigit() or int(store_id2) <= 0:
101		raise falcon.HTTPError(
102		status=falcon.HTTP_400,
103		title="API.BAD_REQUEST",
104		description="API.INVALID_STORE_ID",
105		)
106
107		if store_uuid2 is not None:
108		regex = re.compile(
109		r"^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z",
110		re.I,
111		)
112		match = regex.match(str.strip(store_uuid2))
113		if not bool(match):
114		raise falcon.HTTPError(
115		status=falcon.HTTP_400,
116		title="API.BAD_REQUEST",
117		description="API.INVALID_STORE_ID",
118		)
119
120	View Code Duplication	if energy_category_id is None:
		0 ignored issues – show Duplication introduced 2025-10-31 14:34 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
121		raise falcon.HTTPError(
122		status=falcon.HTTP_400,
123		title="API.BAD_REQUEST",
124		description="API.INVALID_ENERGY_CATEGORY_ID",
125		)
126		else:
127		energy_category_id = str.strip(energy_category_id)
128		if not energy_category_id.isdigit() or int(energy_category_id) <= 0:
129		raise falcon.HTTPError(
130		status=falcon.HTTP_400,
131		title="API.BAD_REQUEST",
132		description="API.INVALID_ENERGY_CATEGORY_ID",
133		)
134
135	View Code Duplication	if period_type is None:
		0 ignored issues – show Duplication introduced 2025-10-31 14:34 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
136		raise falcon.HTTPError(
137		status=falcon.HTTP_400,
138		title="API.BAD_REQUEST",
139		description="API.INVALID_PERIOD_TYPE",
140		)
141		else:
142		period_type = str.strip(period_type)
143		if period_type not in ["hourly", "daily", "weekly", "monthly", "yearly"]:
144		raise falcon.HTTPError(
145		status=falcon.HTTP_400,
146		title="API.BAD_REQUEST",
147		description="API.INVALID_PERIOD_TYPE",
148		)
149
150		timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
151		if config.utc_offset[0] == "-":
152		timezone_offset = -timezone_offset
153
154		if reporting_period_start_datetime_local is None:
155		raise falcon.HTTPError(
156		status=falcon.HTTP_400,
157		title="API.BAD_REQUEST",
158		description="API.INVALID_REPORTING_PERIOD_START_DATETIME",
159		)
160		else:
161		reporting_period_start_datetime_local = str.strip(
162		reporting_period_start_datetime_local
163		)
164		try:
165		reporting_start_datetime_utc = datetime.strptime(
166		reporting_period_start_datetime_local, "%Y-%m-%dT%H:%M:%S"
167		)
168		except ValueError:
169		raise falcon.HTTPError(
170		status=falcon.HTTP_400,
171		title="API.BAD_REQUEST",
172		description="API.INVALID_REPORTING_PERIOD_START_DATETIME",
173		)
174		reporting_start_datetime_utc = reporting_start_datetime_utc.replace(
175		tzinfo=timezone.utc
176		) - timedelta(minutes=timezone_offset)
177		# nomalize the start datetime
178		if (
179		config.minutes_to_count == 30
180		and reporting_start_datetime_utc.minute >= 30
181		):
182		reporting_start_datetime_utc = reporting_start_datetime_utc.replace(
183		minute=30, second=0, microsecond=0
184		)
185		else:
186		reporting_start_datetime_utc = reporting_start_datetime_utc.replace(
187		minute=0, second=0, microsecond=0
188		)
189
190		if reporting_period_end_datetime_local is None:
191		raise falcon.HTTPError(
192		status=falcon.HTTP_400,
193		title="API.BAD_REQUEST",
194		description="API.INVALID_REPORTING_PERIOD_END_DATETIME",
195		)
196		else:
197		reporting_period_end_datetime_local = str.strip(
198		reporting_period_end_datetime_local
199		)
200		try:
201		reporting_end_datetime_utc = datetime.strptime(
202		reporting_period_end_datetime_local, "%Y-%m-%dT%H:%M:%S"
203		)
204		except ValueError:
205		raise falcon.HTTPError(
206		status=falcon.HTTP_400,
207		title="API.BAD_REQUEST",
208		description="API.INVALID_REPORTING_PERIOD_END_DATETIME",
209		)
210		reporting_end_datetime_utc = reporting_end_datetime_utc.replace(
211		tzinfo=timezone.utc
212		) - timedelta(minutes=timezone_offset)
213
214		if reporting_start_datetime_utc >= reporting_end_datetime_utc:
215		raise falcon.HTTPError(
216		status=falcon.HTTP_400,
217		title="API.BAD_REQUEST",
218		description="API.INVALID_REPORTING_PERIOD_END_DATETIME",
219		)
220
221		# if turn quick mode on, do not return parameters data and excel file
222		is_quick_mode = False
223		if (
224		quick_mode is not None
225		and len(str.strip(quick_mode)) > 0
226		and str.lower(str.strip(quick_mode)) in ("true", "t", "on", "yes", "y")
227		):
228		is_quick_mode = True
229
230		################################################################################################################
231		# Step 2: query the store and energy category
232		################################################################################################################
233		cnx_system = mysql.connector.connect(**config.myems_system_db)
234		cursor_system = cnx_system.cursor()
235
236		cnx_energy = mysql.connector.connect(**config.myems_energy_db)
237		cursor_energy = cnx_energy.cursor()
238
239		cnx_historical = mysql.connector.connect(**config.myems_historical_db)
240		cursor_historical = cnx_historical.cursor()
241
242		# Query store 1
243		if store_id1 is not None:
244		cursor_system.execute(
245		" SELECT id, name FROM tbl_stores WHERE id = %s ", (store_id1,)
246		)
247		row_store1 = cursor_system.fetchone()
248		elif store_uuid1 is not None:
249		cursor_system.execute(
250		" SELECT id, name FROM tbl_stores WHERE uuid = %s ",
251		(store_uuid1,),
252		)
253		row_store1 = cursor_system.fetchone()
254
255		if row_store1 is None:
		0 ignored issues – show introduced 2025-10-31 14:34 UTC by Report Bug Copy Issue Report The variable `row_store1` does not seem to be defined for all execution paths. Loading history...
256		if cursor_system:
257		cursor_system.close()
258		if cnx_system:
259		cnx_system.close()
260
261		if cursor_energy:
262		cursor_energy.close()
263		if cnx_energy:
264		cnx_energy.close()
265
266		if cursor_historical:
267		cursor_historical.close()
268		if cnx_historical:
269		cnx_historical.close()
270		raise falcon.HTTPError(
271		status=falcon.HTTP_404,
272		title="API.NOT_FOUND",
273		description="API.STORE_NOT_FOUND",
274		)
275
276		store1 = dict()
277		store1["id"] = row_store1[0]
278		store1["name"] = row_store1[1]
279
280		# Query store 2
281		if store_id2 is not None:
282		cursor_system.execute(
283		" SELECT id, name FROM tbl_stores WHERE id = %s ", (store_id2,)
284		)
285		row_store2 = cursor_system.fetchone()
286		elif store_uuid2 is not None:
287		cursor_system.execute(
288		" SELECT id, name FROM tbl_stores WHERE uuid = %s ",
289		(store_uuid2,),
290		)
291		row_store2 = cursor_system.fetchone()
292
293		if row_store2 is None:
		0 ignored issues – show introduced 2025-10-31 14:34 UTC by Report Bug Copy Issue Report The variable `row_store2` does not seem to be defined for all execution paths. Loading history...
294		if cursor_system:
295		cursor_system.close()
296		if cnx_system:
297		cnx_system.close()
298
299		if cursor_energy:
300		cursor_energy.close()
301		if cnx_energy:
302		cnx_energy.close()
303
304		if cursor_historical:
305		cursor_historical.close()
306		if cnx_historical:
307		cnx_historical.close()
308		raise falcon.HTTPError(
309		status=falcon.HTTP_404,
310		title="API.NOT_FOUND",
311		description="API.STORE_NOT_FOUND",
312		)
313
314		store2 = dict()
315		store2["id"] = row_store2[0]
316		store2["name"] = row_store2[1]
317
318		# Query energy category
319		cursor_system.execute(
320		" SELECT id, name, unit_of_measure FROM tbl_energy_categories WHERE id = %s ",
321		(energy_category_id,),
322		)
323		row_energy_category = cursor_system.fetchone()
324
325	View Code Duplication	if row_energy_category is None:
		0 ignored issues – show Duplication introduced 2025-10-31 14:34 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
326		if cursor_system:
327		cursor_system.close()
328		if cnx_system:
329		cnx_system.close()
330
331		if cursor_energy:
332		cursor_energy.close()
333		if cnx_energy:
334		cnx_energy.close()
335
336		if cursor_historical:
337		cursor_historical.close()
338		if cnx_historical:
339		cnx_historical.close()
340		raise falcon.HTTPError(
341		status=falcon.HTTP_404,
342		title="API.NOT_FOUND",
343		description="API.ENERGY_CATEGORY_NOT_FOUND",
344		)
345
346		energy_category = dict()
347		energy_category["id"] = row_energy_category[0]
348		energy_category["name"] = row_energy_category[1]
349		energy_category["unit_of_measure"] = row_energy_category[2]
350
351		################################################################################################################
352		# Step 3: query store input category hourly data (pre-aggregated by background service)
353		################################################################################################################
354		# Query store 1 input category hourly data
355		cursor_energy.execute(
356		" SELECT start_datetime_utc, actual_value "
357		" FROM tbl_store_input_category_hourly "
358		" WHERE store_id = %s "
359		" AND energy_category_id = %s "
360		" AND start_datetime_utc >= %s "
361		" AND start_datetime_utc < %s "
362		" ORDER BY start_datetime_utc ",
363		(
364		store1["id"],
365		energy_category_id,
366		reporting_start_datetime_utc,
367		reporting_end_datetime_utc,
368		),
369		)
370		rows_store1_hourly = cursor_energy.fetchall()
371
372		# Query store 2 input category hourly data
373		cursor_energy.execute(
374		" SELECT start_datetime_utc, actual_value "
375		" FROM tbl_store_input_category_hourly "
376		" WHERE store_id = %s "
377		" AND energy_category_id = %s "
378		" AND start_datetime_utc >= %s "
379		" AND start_datetime_utc < %s "
380		" ORDER BY start_datetime_utc ",
381		(
382		store2["id"],
383		energy_category_id,
384		reporting_start_datetime_utc,
385		reporting_end_datetime_utc,
386		),
387		)
388		rows_store2_hourly = cursor_energy.fetchall()
389
390		################################################################################################################
391		# Step 4: aggregate store energy consumption data by period
392		################################################################################################################
393		# Aggregate energy consumption for store 1
394		store1_energy_data = dict()
395		store1_energy_data["timestamps"] = list()
396		store1_energy_data["values"] = list()
397		store1_energy_data["total_in_category"] = Decimal(0.0)
398
399		# Aggregate store 1 hourly data by period
400		rows_store1_periodically = utilities.aggregate_hourly_data_by_period(
401		rows_store1_hourly,
402		reporting_start_datetime_utc,
403		reporting_end_datetime_utc,
404		period_type,
405		)
406
407		for row_store1_periodically in rows_store1_periodically:
408		current_datetime_local = row_store1_periodically[0].replace(
409		tzinfo=timezone.utc
410		) + timedelta(minutes=timezone_offset)
411		if period_type == "hourly":
412		current_datetime = current_datetime_local.isoformat()[0:19]
413		elif period_type == "daily":
414		current_datetime = current_datetime_local.isoformat()[0:10]
415		elif period_type == "weekly":
416		current_datetime = current_datetime_local.isoformat()[0:10]
417		elif period_type == "monthly":
418		current_datetime = current_datetime_local.isoformat()[0:7]
419		elif period_type == "yearly":
420		current_datetime = current_datetime_local.isoformat()[0:4]
421
422		actual_value = row_store1_periodically[1]
423
424		store1_energy_data["timestamps"].append(current_datetime)
		0 ignored issues – show introduced 2025-10-31 14:34 UTC by Report Bug Copy Issue Report The variable `current_datetime` does not seem to be defined for all execution paths. Loading history...
425		store1_energy_data["values"].append(actual_value)
426		if actual_value is not None:
427		store1_energy_data["total_in_category"] += actual_value
428
429		# Aggregate energy consumption for store 2
430		store2_energy_data = dict()
431		store2_energy_data["timestamps"] = list()
432		store2_energy_data["values"] = list()
433		store2_energy_data["total_in_category"] = Decimal(0.0)
434
435		# Aggregate store 2 hourly data by period
436		rows_store2_periodically = utilities.aggregate_hourly_data_by_period(
437		rows_store2_hourly,
438		reporting_start_datetime_utc,
439		reporting_end_datetime_utc,
440		period_type,
441		)
442
443		for row_store2_periodically in rows_store2_periodically:
444		current_datetime_local = row_store2_periodically[0].replace(
445		tzinfo=timezone.utc
446		) + timedelta(minutes=timezone_offset)
447		if period_type == "hourly":
448		current_datetime = current_datetime_local.isoformat()[0:19]
449		elif period_type == "daily":
450		current_datetime = current_datetime_local.isoformat()[0:10]
451		elif period_type == "weekly":
452		current_datetime = current_datetime_local.isoformat()[0:10]
453		elif period_type == "monthly":
454		current_datetime = current_datetime_local.isoformat()[0:7]
455		elif period_type == "yearly":
456		current_datetime = current_datetime_local.isoformat()[0:4]
457
458		actual_value = row_store2_periodically[1]
459
460		store2_energy_data["timestamps"].append(current_datetime)
461		store2_energy_data["values"].append(actual_value)
462		if actual_value is not None:
463		store2_energy_data["total_in_category"] += actual_value
464
465		# Calculate difference
466		diff = dict()
467		diff["values"] = list()
468		diff["total_in_category"] = Decimal(0.0)
469
470		# Ensure both stores have the same number of data points
471		min_length = min(
472		len(store1_energy_data["values"]), len(store2_energy_data["values"])
473		)
474		for i in range(min_length):
475		store1_value = (
476		store1_energy_data["values"][i]
477		if i < len(store1_energy_data["values"])
478		else None
479		)
480		store2_value = (
481		store2_energy_data["values"][i]
482		if i < len(store2_energy_data["values"])
483		else None
484		)
485
486		# Calculate difference, handling None values
487		if store1_value is None and store2_value is None:
488		diff_value = None
489		elif store1_value is None:
490		diff_value = None # Cannot calculate difference when one value is missing
491		elif store2_value is None:
492		diff_value = None # Cannot calculate difference when one value is missing
493		else:
494		diff_value = store1_value - store2_value
495		diff["total_in_category"] += diff_value
496
497		diff["values"].append(diff_value)
498
499		################################################################################################################
500		# Step 5: query store associated points data (for detailed parameters)
501		################################################################################################################
502		parameters_data1 = dict()
503		parameters_data1["names"] = list()
504		parameters_data1["timestamps"] = list()
505		parameters_data1["values"] = list()
506
507		parameters_data2 = dict()
508		parameters_data2["names"] = list()
509		parameters_data2["timestamps"] = list()
510		parameters_data2["values"] = list()
511
512		################################################################################################################
513		# Step 6: construct the report
514		################################################################################################################
515		if cursor_system:
516		cursor_system.close()
517		if cnx_system:
518		cnx_system.close()
519
520		if cursor_energy:
521		cursor_energy.close()
522		if cnx_energy:
523		cnx_energy.close()
524
525		if cursor_historical:
526		cursor_historical.close()
527		if cnx_historical:
528		cnx_historical.close()
529
530		result = {
531		"store1": {
532		"id": store1["id"],
533		"name": store1["name"],
534		},
535		"store2": {
536		"id": store2["id"],
537		"name": store2["name"],
538		},
539		"energy_category": {
540		"id": energy_category["id"],
541		"name": energy_category["name"],
542		"unit_of_measure": energy_category["unit_of_measure"],
543		},
544		"reporting_period1": {
545		"total_in_category": store1_energy_data["total_in_category"],
546		"timestamps": store1_energy_data["timestamps"],
547		"values": store1_energy_data["values"],
548		},
549		"reporting_period2": {
550		"total_in_category": store2_energy_data["total_in_category"],
551		"timestamps": store2_energy_data["timestamps"],
552		"values": store2_energy_data["values"],
553		},
554		"parameters1": {
555		"names": parameters_data1["names"],
556		"timestamps": parameters_data1["timestamps"],
557		"values": parameters_data1["values"],
558		},
559		"parameters2": {
560		"names": parameters_data2["names"],
561		"timestamps": parameters_data2["timestamps"],
562		"values": parameters_data2["values"],
563		},
564		"diff": {
565		"values": diff["values"],
566		"total_in_category": diff["total_in_category"],
567		},
568		}
569
570		# export result to Excel file and then encode the file to base64 string
571		if not is_quick_mode:
572		result["excel_bytes_base64"] = excelexporters.storecomparison.export(
573		result,
574		store1["name"],
575		store2["name"],
576		energy_category["name"],
577		reporting_period_start_datetime_local,
578		reporting_period_end_datetime_local,
579		period_type,
580		language,
581		)
582
583		resp.text = json.dumps(result)
584

MyEMS / myems

Push — master ( 467911...05a995 )

reports.storecomparison.Reporting.on_options() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like