编辑代码

/**
账务管理系统
*/
--1.基础账户表
CREATE TABLE accounts (
    account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_number VARCHAR(30) UNIQUE NOT NULL,
    account_name VARCHAR(100) NOT NULL,
    account_type VARCHAR(20) CHECK (account_type IN ('资产','负债','权益','收入','费用')),
    balance NUMERIC(18,4) DEFAULT 0.0000,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
--2.交易流水表
CREATE TABLE transactions (
    transaction_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id UUID REFERENCES accounts(account_id),
    transaction_date DATE NOT NULL,
    amount NUMERIC(18,4) NOT NULL CHECK (amount != 0),
    direction VARCHAR(10) CHECK (direction IN ('DEBIT','CREDIT')),
    reference VARCHAR(50),
    status VARCHAR(20) DEFAULT 'PENDING'::varchar,
    CONSTRAINT valid_amount CHECK (amount > 0)
);
--3.审计日志表
CREATE TABLE audit_logs (
    log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name VARCHAR(50) NOT NULL,
    record_id UUID NOT NULL,
    changed_fields JSONB,
    old_values JSONB,
    new_values JSONB,
    changed_by VARCHAR(50),
    changed_at TIMESTAMPTZ DEFAULT NOW()
);
--4.核心业务逻辑代码
class AccountingSystem:
    def __init__(self, db_conn):
        self.conn = db_conn
    
    def create_account(self, account_data):
        """创建新账户"""
        with self.conn.cursor() as cur:
            cur.execute("""
                INSERT INTO accounts (account_number, account_name, account_type)
                VALUES (%s, %s, %s)
                RETURNING account_id
            """, (account_data['number'], account_data['name'], account_data['type']))
            return cur.fetchone()[0]
    
    def record_transaction(self, tx_data):
        """记录交易流水"""
        with self.conn.cursor() as cur:
            # 开始事务
            cur.execute("BEGIN;")
            
            # 更新账户余额
            balance_sql = "UPDATE accounts SET balance = balance + %s WHERE account_id = %s"
            
            # 插入交易记录
            tx_sql = """
                INSERT INTO transactions (account_id, transaction_date, amount, direction)
                VALUES (%s, %s, %s, %s)
            """
            
            # 执行操作
            try:
                cur.execute(balance_sql, (tx_data['amount'], tx_data['account']))
                cur.execute(tx_sql, (
                    tx_data['account'],
                    tx_data['date'],
                    tx_data['amount'],
                    'DEBIT' if tx_data['amount'] > 0 else 'CREDIT'
                ))
                self.conn.commit()
                return True
            except Exception as e:
                self.conn.rollback()
                raise e
    
    def generate_report(self, start_date, end_date):
        """生成财务报表"""
        with self.conn.cursor() as cur:
            cur.execute("""
                SELECT a.account_type, SUM(t.amount) 
                FROM transactions t
                JOIN accounts a ON t.account_id = a.account_id
                WHERE t.transaction_date BETWEEN %s AND %s
                GROUP BY a.account_type
            """, (start_date, end_date))
            return cur.fetchall()；
--5.资金转移存储过程
DELIMITER $$
CREATE PROCEDURE sp_transfer_funds(
    IN src_account BIGINT,
    IN dest_account BIGINT,
    IN transfer_amount DECIMAL(15,2)
)
BEGIN
    START TRANSACTION;
    UPDATE accounts 
    SET balance = balance - transfer_amount 
    WHERE account_id = src_account AND balance >= transfer_amount;
    
    UPDATE accounts 
    SET balance = balance + transfer_amount 
    WHERE account_id = dest_account;
    
    IF ROW_COUNT() = 2 THEN
        COMMIT;
    ELSE
        ROLLBACK;
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Transfer failed - insufficient funds';
    END IF;
END$$
DELIMITER ;