DataMiddleware/SourceCode/DataMiddleware/tools/stores/mysql_store.py

from datetime import datetime

from httplib2.auth import params
from pymupdf.utils import insert_text

import tools.utils as utils
from tools.utils.mysql_helper import MySQLHelper
from tools.models.standard_model import StandardModel

class MysqlStore:
    def __init__(self):
        self._db_helper = MySQLHelper()
        self._logger = utils.get_logger()

    def insert_standard(self, data: StandardModel) -> bool:
        try:
            sql = """
                INSERT INTO standard_data (book_number, quota_number, quota_name, work_content, unit, basic_quota, base_price, unit_weight, labor_cost, material_cost, machine_cost, main_material, created_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            with self._db_helper as db:
                db.execute(sql, (
                    data.book_number,
                    data.quota_number,
                    data.quota_name,
                    data.work_content,
                    data.unit,
                    data.basic_quota,
                    data.base_price,
                    data.unit_weight,
                    data.labor_cost,
                    data.material_cost,
                    data.machine_cost,
                    data.main_material,
                    datetime.now()
                ))
            return True
        except Exception as e:
            self._logger.error(f"Error inserting standard: {str(e)}")
            return False
    def insert_standard_batch(self, data_list: list[StandardModel]) -> bool:
        try:
            sql = """
                INSERT INTO standard_data (book_number, quota_number, quota_name, work_content, unit, basic_quota, base_price, unit_weight, labor_cost, material_cost, machine_cost, main_material, created_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            insert_params = [(
                data.book_number,
                data.quota_number,
                data.quota_name,
                data.work_content,
                data.unit,
                data.basic_quota,
                data.base_price,
                data.unit_weight,
                data.labor_cost,
                data.material_cost,
                data.machine_cost,
                data.main_material,
                datetime.now()
            ) for data in data_list]
            self._db_helper.execute_many(sql, insert_params)
        except Exception as e:
            self._logger.error(f"Error inserting standard batch: {str(e)}")
            return False

    def query_standard_group_by_book(self) -> dict:
        sql = """
            SELECT book_number
            FROM standard_data
            GROUP BY book_number
            ORDER BY book_number
        """
        with self._db_helper:
            result = self._db_helper.execute_query(sql)
            if not result:
                return {}

            # 按book_number分组并转换为StandardModel
            grouped_data = {}
            for row in result:
                book_number = row['book_number']
                if book_number not in grouped_data:
                    grouped_data[book_number] = []

                # 由于只按book_number分组，其他字段无法从这里获取，需要重新查询
                standards = self.query_standards_by_book_number(book_number)
                grouped_data[book_number].extend(standards)

            return grouped_data

    def query_standards_by_book_number(self, book_number: str) -> list[StandardModel]:
        sql = """
            SELECT book_number, quota_number, quota_name, work_content, unit, basic_quota, base_price, unit_weight, labor_cost, material_cost, machine_cost, main_material
            FROM standard_data
            WHERE book_number = %s
            ORDER BY created_at
        """
        with self._db_helper:
            result = self._db_helper.execute_query(sql, (book_number,))
            standards = []
            for row in result:
                standard = StandardModel(
                    book_number=row['book_number'],
                    quota_number=row['quota_number'],
                    quota_name=row['quota_name'],
                    work_content=row['work_content'],
                    unit=row['unit'],
                    basic_quota=row['basic_quota'],
                    base_price=row['base_price'],
                    unit_weight=row['unit_weight'],
                    labor_cost=row['labor_cost'],
                    material_cost=row['material_cost'],
                    machine_cost=row['machine_cost'],
                    main_material=row['main_material'],
                )
                standards.append(standard)
            return standards