reports.shopfloorcomparison.Reporting.__init__() - Code Metrics - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

reports.shopfloorcomparison.Reporting.init() A
last analyzed 2025-11-11 06:26 UTC

↳ Parent: reports.shopfloorcomparison

Complexity

Conditions

Size

Total Lines	3
Code Lines	2

Duplication

Lines	3
Ratio	100 %

Importance

Changes

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

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.shopfloorcomparison
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 shopfloor and energy category
    # Step 3: query shopfloor input category hourly data (pre-aggregated by background service)
    # Step 4: aggregate shopfloor energy consumption data by period
    # Step 5: 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 shopfloor id or shopfloor uuid to identify a shopfloor
        shopfloor_id1 = req.params.get("shopfloorid1")
        shopfloor_uuid1 = req.params.get("shopflooruuid1")
        shopfloor_id2 = req.params.get("shopfloorid2")
        shopfloor_uuid2 = req.params.get("shopflooruuid2")
        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 shopfloor_id1 is None and shopfloor_uuid1 is None:
            raise falcon.HTTPError(
                status=falcon.HTTP_400,
                title="API.BAD_REQUEST",
                description="API.INVALID_EQUIPMENT_ID",
            )

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

        if shopfloor_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(shopfloor_uuid1))
            if not bool(match):
                raise falcon.HTTPError(
                    status=falcon.HTTP_400,
                    title="API.BAD_REQUEST",
                    description="API.INVALID_EQUIPMENT_UUID",
                )

        if shopfloor_id2 is None and shopfloor_uuid2 is None:
            raise falcon.HTTPError(
                status=falcon.HTTP_400,
                title="API.BAD_REQUEST",
                description="API.INVALID_EQUIPMENT_ID",
            )

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

        if shopfloor_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(shopfloor_uuid2))
            if not bool(match):
                raise falcon.HTTPError(
                    status=falcon.HTTP_400,
                    title="API.BAD_REQUEST",
                    description="API.INVALID_EQUIPMENT_UUID",
                )

        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 shopfloor 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 shopfloor 1
        if shopfloor_id1 is not None:
            cursor_system.execute(
                " SELECT id, name FROM tbl_shopfloors WHERE id = %s ", (shopfloor_id1,)
            )
            row_shopfloor1 = cursor_system.fetchone()
        elif shopfloor_uuid1 is not None:
            cursor_system.execute(
                " SELECT id, name FROM tbl_shopfloors WHERE uuid = %s ",
                (shopfloor_uuid1,),
            )
            row_shopfloor1 = cursor_system.fetchone()

        if row_shopfloor1 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.EQUIPMENT_NOT_FOUND",
            )

        shopfloor1 = dict()
        shopfloor1["id"] = row_shopfloor1[0]
        shopfloor1["name"] = row_shopfloor1[1]

        # Query shopfloor 2
        if shopfloor_id2 is not None:
            cursor_system.execute(
                " SELECT id, name FROM tbl_shopfloors WHERE id = %s ", (shopfloor_id2,)
            )
            row_shopfloor2 = cursor_system.fetchone()
        elif shopfloor_uuid2 is not None:
            cursor_system.execute(
                " SELECT id, name FROM tbl_shopfloors WHERE uuid = %s ",
                (shopfloor_uuid2,),
            )
            row_shopfloor2 = cursor_system.fetchone()

        if row_shopfloor2 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.EQUIPMENT_NOT_FOUND",
            )

        shopfloor2 = dict()
        shopfloor2["id"] = row_shopfloor2[0]
        shopfloor2["name"] = row_shopfloor2[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 shopfloor input category hourly data (pre-aggregated by background service)
        ################################################################################################################
        # Query shopfloor 1 input category hourly data
        cursor_energy.execute(
            " SELECT start_datetime_utc, actual_value "
            " FROM tbl_shopfloor_input_category_hourly "
            " WHERE shopfloor_id = %s "
            "     AND energy_category_id = %s "
            "     AND start_datetime_utc >= %s "
            "     AND start_datetime_utc < %s "
            " ORDER BY start_datetime_utc ",
            (
                shopfloor1["id"],
                energy_category_id,
                reporting_start_datetime_utc,
                reporting_end_datetime_utc,
            ),
        )
        rows_shopfloor1_hourly = cursor_energy.fetchall()

        # Query shopfloor 2 input category hourly data
        cursor_energy.execute(
            " SELECT start_datetime_utc, actual_value "
            " FROM tbl_shopfloor_input_category_hourly "
            " WHERE shopfloor_id = %s "
            "     AND energy_category_id = %s "
            "     AND start_datetime_utc >= %s "
            "     AND start_datetime_utc < %s "
            " ORDER BY start_datetime_utc ",
            (
                shopfloor2["id"],
                energy_category_id,
                reporting_start_datetime_utc,
                reporting_end_datetime_utc,
            ),
        )
        rows_shopfloor2_hourly = cursor_energy.fetchall()

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

        # Aggregate shopfloor 1 hourly data by period
        rows_shopfloor1_periodically = utilities.aggregate_hourly_data_by_period(
            rows_shopfloor1_hourly,
            reporting_start_datetime_utc,
            reporting_end_datetime_utc,
            period_type,
        )

        for row_shopfloor1_periodically in rows_shopfloor1_periodically:
            current_datetime_local = row_shopfloor1_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_shopfloor1_periodically[1]

            shopfloor1_energy_data["timestamps"].append(current_datetime)

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

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

        # Aggregate shopfloor 2 hourly data by period
        rows_shopfloor2_periodically = utilities.aggregate_hourly_data_by_period(
            rows_shopfloor2_hourly,
            reporting_start_datetime_utc,
            reporting_end_datetime_utc,
            period_type,
        )

        for row_shopfloor2_periodically in rows_shopfloor2_periodically:
            current_datetime_local = row_shopfloor2_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_shopfloor2_periodically[1]

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

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

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

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

MyEMS / myems

reports.shopfloorcomparison.Reporting.__init__() A last analyzed 2025-11-11 06:26 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

reports.shopfloorcomparison.Reporting.init() A
last analyzed 2025-11-11 06:26 UTC