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

Code Duplication Length = 255-257 lines in 4 locations

myems-api/reports/equipmentbatch.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 equipments in the space tree
    # Step 4: query energy categories
    # Step 5: query reporting period energy input
    # Step 6: construct the report
    ####################################################################################################################
    @staticmethod
    def on_get(req, resp):
        if 'API-KEY' not in req.headers or \
                not isinstance(req.headers['API-KEY'], str) or \
                len(str.strip(req.headers['API-KEY'])) == 0:
            access_control(req)
        else:
            api_key_control(req)
        print(req.params)
        space_id = req.params.get('spaceid')
        reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
        reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
        language = req.params.get('language')
        quick_mode = req.params.get('quickmode')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if space_id is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
        else:
            space_id = str.strip(space_id)
            if not space_id.isdigit() or int(space_id) <= 0:
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description='API.INVALID_SPACE_ID')
            else:
                space_id = int(space_id)

        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
        if config.utc_offset[0] == '-':
            timezone_offset = -timezone_offset

        if reporting_period_start_datetime_local is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
        else:
            reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
            try:
                reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
                                                                 '%Y-%m-%dT%H:%M:%S')
            except ValueError:
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
            reporting_start_datetime_utc = \
                reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset)
            # nomalize the start datetime
            if config.minutes_to_count == 30 and reporting_start_datetime_utc.minute >= 30:
                reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=30, second=0, microsecond=0)
            else:
                reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=0, second=0, microsecond=0)

        if reporting_period_end_datetime_local is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
        else:
            reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local)
            try:
                reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local,
                                                               '%Y-%m-%dT%H:%M:%S')
            except ValueError:
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
            reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
                timedelta(minutes=timezone_offset)

        if reporting_start_datetime_utc >= reporting_end_datetime_utc:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')

        # 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 equipments in the space tree
        ################################################################################################################
        equipment_dict = dict()
        space_dict = dict()

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

        cursor_system_db.execute(" SELECT e.id, e.name AS equipment_name, "
                                 "        e.uuid AS equipment_uuid, s.name AS space_name, "
                                 "        cc.name AS cost_center_name, e.description "
                                 " FROM tbl_spaces s, tbl_spaces_equipments se, "
                                 "      tbl_equipments e, tbl_cost_centers cc "
                                 " WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
                                 "       AND se.space_id = s.id AND se.equipment_id = e.id "
                                 "       AND e.cost_center_id = cc.id  ", )
        rows_equipments = cursor_system_db.fetchall()
        if rows_equipments is not None and len(rows_equipments) > 0:
            for row in rows_equipments:
                equipment_dict[row[0]] = {"equipment_name": row[1],
                                          "equipment_uuid": row[2],
                                          "space_name": row[3],
                                          "cost_center_name": row[4],
                                          "description": row[5],
                                          "values": list()}

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

        # query energy categories in reporting period
        energy_category_set = set()
        cursor_energy_db.execute(" SELECT DISTINCT(energy_category_id) "
                                 " FROM tbl_equipment_input_category_hourly "
                                 " WHERE start_datetime_utc >= %s AND start_datetime_utc < %s ",
                                 (reporting_start_datetime_utc, reporting_end_datetime_utc))
        rows_energy_categories = cursor_energy_db.fetchall()
        if rows_energy_categories is not None and len(rows_energy_categories) > 0:
            for row_energy_category in rows_energy_categories:
                energy_category_set.add(row_energy_category[0])

        # query all energy categories
        cursor_system_db.execute(" SELECT id, name, unit_of_measure "
                                 " FROM tbl_energy_categories "
                                 " ORDER BY id ", )
        rows_energy_categories = cursor_system_db.fetchall()
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.close()

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

            raise falcon.HTTPError(status=falcon.HTTP_404,
                                   title='API.NOT_FOUND',
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
        energy_category_list = list()
        for row_energy_category in rows_energy_categories:
            if row_energy_category[0] in energy_category_set:
                energy_category_list.append({"id": row_energy_category[0],
                                             "name": row_energy_category[1],
                                             "unit_of_measure": row_energy_category[2]})

        ################################################################################################################
        # Step 5: query reporting period energy input
        ################################################################################################################
        for equipment_id in equipment_dict:

            cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
                                     " FROM tbl_equipment_input_category_hourly "
                                     " WHERE equipment_id = %s "
                                     "     AND start_datetime_utc >= %s "
                                     "     AND start_datetime_utc < %s "
                                     " GROUP BY energy_category_id ",
                                     (equipment_id,
                                      reporting_start_datetime_utc,
                                      reporting_end_datetime_utc))
            rows_equipment_energy = cursor_energy_db.fetchall()
            for energy_category in energy_category_list:
                subtotal = Decimal(0.0)
                for row_equipment_energy in rows_equipment_energy:
                    if energy_category['id'] == row_equipment_energy[0]:
                        subtotal = row_equipment_energy[1]
                        break
                equipment_dict[equipment_id]['values'].append(subtotal)

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

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

        ################################################################################################################
        # Step 6: construct the report
        ################################################################################################################
        equipment_list = list()
        for equipment_id, equipment in equipment_dict.items():
            equipment_list.append({
                "id": equipment_id,
                "equipment_name": equipment['equipment_name'],
                "equipment_uuid": equipment['equipment_uuid'],
                "space_name": equipment['space_name'],
                "cost_center_name": equipment['cost_center_name'],
                "description": equipment['description'],
                "values": equipment['values'],
            })

        result = {'equipments': equipment_list, 'energycategories': energy_category_list, 'excel_bytes_base64': None}

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


myems-api/reports/combinedequipmentbatch.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 combined equipments in the space tree
    # Step 4: query energy categories
    # Step 5: query reporting period energy input
    # Step 6: construct the report
    ####################################################################################################################
    @staticmethod
    def on_get(req, resp):
        if 'API-KEY' not in req.headers or \
                not isinstance(req.headers['API-KEY'], str) or \
                len(str.strip(req.headers['API-KEY'])) == 0:
            access_control(req)
        else:
            api_key_control(req)
        print(req.params)
        space_id = req.params.get('spaceid')
        reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime')
        reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime')
        language = req.params.get('language')
        quick_mode = req.params.get('quickmode')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if space_id is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_SPACE_ID')
        else:
            space_id = str.strip(space_id)
            if not space_id.isdigit() or int(space_id) <= 0:
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description='API.INVALID_SPACE_ID')
            else:
                space_id = int(space_id)

        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
        if config.utc_offset[0] == '-':
            timezone_offset = -timezone_offset

        if reporting_period_start_datetime_local is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
        else:
            reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local)
            try:
                reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local,
                                                                 '%Y-%m-%dT%H:%M:%S')
            except ValueError:
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_START_DATETIME")
            reporting_start_datetime_utc = \
                reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset)
            # nomalize the start datetime
            if config.minutes_to_count == 30 and reporting_start_datetime_utc.minute >= 30:
                reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=30, second=0, microsecond=0)
            else:
                reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=0, second=0, microsecond=0)

        if reporting_period_end_datetime_local is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
        else:
            reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local)
            try:
                reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local,
                                                               '%Y-%m-%dT%H:%M:%S')
            except ValueError:
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description="API.INVALID_REPORTING_PERIOD_END_DATETIME")
            reporting_end_datetime_utc = reporting_end_datetime_utc.replace(tzinfo=timezone.utc) - \
                timedelta(minutes=timezone_offset)

        if reporting_start_datetime_utc >= reporting_end_datetime_utc:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_REPORTING_PERIOD_END_DATETIME')

        cnx_system_db = mysql.connector.connect(**config.myems_system_db)
        cursor_system_db = cnx_system_db.cursor()

        cursor_system_db.execute(" SELECT name "
                                 " FROM tbl_spaces "
                                 " WHERE id = %s ", (space_id,))
        row = cursor_system_db.fetchone()

        if row is None:
            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.close()
            raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND',
                                   description='API.SPACE_NOT_FOUND')
        else:
            space_name = row[0]

        # if turn quick mode on, do not return parameters data and excel file
        is_quick_mode = False
        if quick_mode is not None and \
                len(str.strip(quick_mode)) > 0 and \
                str.lower(str.strip(quick_mode)) in ('true', 't', 'on', 'yes', 'y'):
            is_quick_mode = True
        ################################################################################################################
        # Step 2: build a space tree
        ################################################################################################################

        query = (" SELECT id, name, parent_space_id "
                 " FROM tbl_spaces "
                 " ORDER BY id ")
        cursor_system_db.execute(query)
        rows_spaces = cursor_system_db.fetchall()
        node_dict = dict()
        if rows_spaces is not None and len(rows_spaces) > 0:
            for row in rows_spaces:
                parent_node = node_dict[row[2]] if row[2] is not None else None
                node_dict[row[0]] = AnyNode(id=row[0], parent=parent_node, name=row[1])

        ################################################################################################################
        # Step 3: query all combined equipments in the space tree
        ################################################################################################################
        combined_equipment_dict = dict()
        space_dict = dict()

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

        cursor_system_db.execute(" SELECT ce.id, ce.name AS combined_equipment_name, "
                                 "        ce.uuid AS combined_equipment_uuid, s.name AS space_name, "
                                 "        cc.name AS cost_center_name, ce.description "
                                 " FROM tbl_spaces s, tbl_spaces_combined_equipments sce, "
                                 "      tbl_combined_equipments ce, tbl_cost_centers cc "
                                 " WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
                                 "       AND sce.space_id = s.id AND sce.combined_equipment_id = ce.id "
                                 "       AND ce.cost_center_id = cc.id  ", )
        rows_combined_equipments = cursor_system_db.fetchall()
        if rows_combined_equipments is not None and len(rows_combined_equipments) > 0:
            for row in rows_combined_equipments:
                combined_equipment_dict[row[0]] = {"combined_equipment_name": row[1],
                                                   "combined_equipment_uuid": row[2],
                                                   "space_name": row[3],
                                                   "cost_center_name": row[4],
                                                   "description": row[5],
                                                   "values": list()}

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

        # query energy categories in reporting period
        energy_category_set = set()
        cursor_energy_db.execute(" SELECT DISTINCT(energy_category_id) "
                                 " FROM tbl_combined_equipment_input_category_hourly "
                                 " WHERE start_datetime_utc >= %s AND start_datetime_utc < %s ",
                                 (reporting_start_datetime_utc, reporting_end_datetime_utc))
        rows_energy_categories = cursor_energy_db.fetchall()
        if rows_energy_categories is not None and len(rows_energy_categories) > 0:
            for row_energy_category in rows_energy_categories:
                energy_category_set.add(row_energy_category[0])

        # query all energy categories
        cursor_system_db.execute(" SELECT id, name, unit_of_measure "
                                 " FROM tbl_energy_categories "
                                 " ORDER BY id ", )
        rows_energy_categories = cursor_system_db.fetchall()
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.close()

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

            raise falcon.HTTPError(status=falcon.HTTP_404,
                                   title='API.NOT_FOUND',
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
        energy_category_list = list()
        for row_energy_category in rows_energy_categories:
            if row_energy_category[0] in energy_category_set:
                energy_category_list.append({"id": row_energy_category[0],
                                             "name": row_energy_category[1],
                                             "unit_of_measure": row_energy_category[2]})

        ################################################################################################################
        # Step 5: query reporting period energy input
        ################################################################################################################
        for combined_equipment_id in combined_equipment_dict:

            cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
                                     " FROM tbl_combined_equipment_input_category_hourly "
                                     " WHERE combined_equipment_id = %s "
                                     "     AND start_datetime_utc >= %s "
                                     "     AND start_datetime_utc < %s "
                                     " GROUP BY energy_category_id ",
                                     (combined_equipment_id,
                                      reporting_start_datetime_utc,
                                      reporting_end_datetime_utc))
            rows_combined_equipment_energy = cursor_energy_db.fetchall()
            for energy_category in energy_category_list:
                subtotal = Decimal(0.0)
                for row_combined_equipment_energy in rows_combined_equipment_energy:
                    if energy_category['id'] == row_combined_equipment_energy[0]:
                        subtotal = row_combined_equipment_energy[1]
                        break
                combined_equipment_dict[combined_equipment_id]['values'].append(subtotal)

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

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

        ################################################################################################################
        # Step 6: construct the report
        ################################################################################################################
        combined_equipment_list = list()
        for combined_equipment_id, combined_equipment in combined_equipment_dict.items():
            combined_equipment_list.append({
                "id": combined_equipment_id,
                "combined_equipment_name": combined_equipment['combined_equipment_name'],
                "combined_equipment_uuid": combined_equipment['combined_equipment_uuid'],
                "space_name": combined_equipment['space_name'],
                "cost_center_name": combined_equipment['cost_center_name'],
                "description": combined_equipment['description'],
                "values": combined_equipment['values'],
            })

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

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


myems-api/reports/shopfloorbatch.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 shopfloors 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 shopfloors in the space tree
        ################################################################################################################
        shopfloor_dict = dict()
        space_dict = dict()

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

        cursor_system_db.execute(" SELECT shopfloor.id, shopfloor.name AS shopfloor_name, "
                                 "        shopfloor.uuid AS shopfloor_uuid, s.name AS space_name, "
                                 "        cc.name AS cost_center_name, shopfloor.description "
                                 " FROM tbl_spaces s, tbl_spaces_shopfloors ss,"
                                 " tbl_shopfloors shopfloor, tbl_cost_centers cc "
                                 " WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
                                 "       AND ss.space_id = s.id AND ss.shopfloor_id = shopfloor.id "
                                 "       AND shopfloor.cost_center_id = cc.id  ", )
        rows_shopfloors = cursor_system_db.fetchall()
        if rows_shopfloors is not None and len(rows_shopfloors) > 0:
            for row in rows_shopfloors:
                shopfloor_dict[row[0]] = {"shopfloor_name": row[1],
                                          "shopfloor_uuid": row[2],
                                          "space_name": row[3],
                                          "cost_center_name": row[4],
                                          "description": row[5],
                                          "values": list()}

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

        # query energy categories in reporting period
        energy_category_set = set()
        cursor_energy_db.execute(" SELECT DISTINCT(energy_category_id) "
                                 " FROM tbl_shopfloor_input_category_hourly "
                                 " WHERE start_datetime_utc >= %s AND start_datetime_utc < %s ",
                                 (reporting_start_datetime_utc, reporting_end_datetime_utc))
        rows_energy_categories = cursor_energy_db.fetchall()
        if rows_energy_categories is not None and len(rows_energy_categories) > 0:
            for row_energy_category in rows_energy_categories:
                energy_category_set.add(row_energy_category[0])

        # query all energy categories
        cursor_system_db.execute(" SELECT id, name, unit_of_measure "
                                 " FROM tbl_energy_categories "
                                 " ORDER BY id ", )
        rows_energy_categories = cursor_system_db.fetchall()
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.close()

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

            raise falcon.HTTPError(status=falcon.HTTP_404,
                                   title='API.NOT_FOUND',
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
        energy_category_list = list()
        for row_energy_category in rows_energy_categories:
            if row_energy_category[0] in energy_category_set:
                energy_category_list.append({"id": row_energy_category[0],
                                             "name": row_energy_category[1],
                                             "unit_of_measure": row_energy_category[2]})

        ################################################################################################################
        # Step 5: query reporting period energy input
        ################################################################################################################
        for shopfloor_id in shopfloor_dict:

            cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
                                     " FROM tbl_shopfloor_input_category_hourly "
                                     " WHERE shopfloor_id = %s "
                                     "     AND start_datetime_utc >= %s "
                                     "     AND start_datetime_utc < %s "
                                     " GROUP BY energy_category_id ",
                                     (shopfloor_id,
                                      reporting_start_datetime_utc,
                                      reporting_end_datetime_utc))
            rows_shopfloor_energy = cursor_energy_db.fetchall()
            for energy_category in energy_category_list:
                subtotal = Decimal(0.0)
                for row_shopfloor_energy in rows_shopfloor_energy:
                    if energy_category['id'] == row_shopfloor_energy[0]:
                        subtotal = row_shopfloor_energy[1]
                        break
                shopfloor_dict[shopfloor_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
        ################################################################################################################
        shopfloor_list = list()
        for shopfloor_id, shopfloor in shopfloor_dict.items():
            shopfloor_list.append({
                "id": shopfloor_id,
                "shopfloor_name": shopfloor['shopfloor_name'],
                "shopfloor_uuid": shopfloor['shopfloor_uuid'],
                "space_name": shopfloor['space_name'],
                "cost_center_name": shopfloor['cost_center_name'],
                "description": shopfloor['description'],
                "values": shopfloor['values'],
            })

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


myems-api/reports/storebatch.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 stores 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 stores in the space tree
        ################################################################################################################
        store_dict = dict()
        space_dict = dict()

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

        cursor_system_db.execute(" SELECT store.id, store.name AS store_name, store.uuid AS store_uuid, "
                                 " s.name AS space_name, cc.name AS cost_center_name, store.description "
                                 " FROM tbl_spaces s, tbl_spaces_stores ss, tbl_stores store, tbl_cost_centers cc "
                                 " WHERE s.id IN ( " + ', '.join(map(str, space_dict.keys())) + ") "
                                 "       AND ss.space_id = s.id AND ss.store_id = store.id "
                                 "       AND store.cost_center_id = cc.id  ", )
        rows_stores = cursor_system_db.fetchall()
        if rows_stores is not None and len(rows_stores) > 0:
            for row in rows_stores:
                store_dict[row[0]] = {"store_name": row[1],
                                      "store_uuid": row[2],
                                      "space_name": row[3],
                                      "cost_center_name": row[4],
                                      "description": row[5],
                                      "values": list()}

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

        # query energy categories in reporting period
        energy_category_set = set()
        cursor_energy_db.execute(" SELECT DISTINCT(energy_category_id) "
                                 " FROM tbl_store_input_category_hourly "
                                 " WHERE start_datetime_utc >= %s AND start_datetime_utc < %s ",
                                 (reporting_start_datetime_utc, reporting_end_datetime_utc))
        rows_energy_categories = cursor_energy_db.fetchall()
        if rows_energy_categories is not None and len(rows_energy_categories) > 0:
            for row_energy_category in rows_energy_categories:
                energy_category_set.add(row_energy_category[0])

        # query all energy categories
        cursor_system_db.execute(" SELECT id, name, unit_of_measure "
                                 " FROM tbl_energy_categories "
                                 " ORDER BY id ", )
        rows_energy_categories = cursor_system_db.fetchall()
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.close()

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

            raise falcon.HTTPError(status=falcon.HTTP_404,
                                   title='API.NOT_FOUND',
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
        energy_category_list = list()
        for row_energy_category in rows_energy_categories:
            if row_energy_category[0] in energy_category_set:
                energy_category_list.append({"id": row_energy_category[0],
                                             "name": row_energy_category[1],
                                             "unit_of_measure": row_energy_category[2]})

        ################################################################################################################
        # Step 5: query reporting period energy input
        ################################################################################################################
        for store_id in store_dict:

            cursor_energy_db.execute(" SELECT energy_category_id, SUM(actual_value) "
                                     " FROM tbl_store_input_category_hourly "
                                     " WHERE store_id = %s "
                                     "     AND start_datetime_utc >= %s "
                                     "     AND start_datetime_utc < %s "
                                     " GROUP BY energy_category_id ",
                                     (store_id,
                                      reporting_start_datetime_utc,
                                      reporting_end_datetime_utc))
            rows_store_energy = cursor_energy_db.fetchall()
            for energy_category in energy_category_list:
                subtotal = Decimal(0.0)
                for row_store_energy in rows_store_energy:
                    if energy_category['id'] == row_store_energy[0]:
                        subtotal = row_store_energy[1]
                        break
                store_dict[store_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
        ################################################################################################################
        store_list = list()
        for store_id, store in store_dict.items():
            store_list.append({
                "id": store_id,
                "store_name": store['store_name'],
                "store_uuid": store['store_uuid'],
                "space_name": store['space_name'],
                "cost_center_name": store['cost_center_name'],
                "description": store['description'],
                "values": store['values'],
            })

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


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

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

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

MyEMS / myems

Code Duplication Length = 255-257 lines in 4 locations

myems-api/reports/equipmentbatch.py 1 location

myems-api/reports/combinedequipmentbatch.py 1 location

myems-api/reports/shopfloorbatch.py 1 location

myems-api/reports/storebatch.py 1 location