Design Document: Work Statistics System

Overview

工作统计系统采用前后端分离架构，后端使用Python Flask框架提供RESTful API，前端使用React构建单页应用，数据存储使用PostgreSQL（测试环境使用SQLite）。系统支持人员管理、物品管理、工作记录管理，以及Excel报表导出功能。

Architecture

graph TB
    subgraph Frontend
        React[React SPA]
    end
    
    subgraph Backend
        API[Flask API Server]
        Services[Business Services]
        Models[SQLAlchemy Models]
        Export[Excel Export Service]
    end
    
    subgraph Database
        PG[(PostgreSQL/SQLite)]
    end
    
    React -->|HTTP GET/POST| API
    API --> Services
    Services --> Models
    Services --> Export
    Models --> PG

Technology Stack

Backend: Python 3.11+, Flask, SQLAlchemy, openpyxl, Flask-RESTX (Swagger文档)
Frontend: React 18, Axios, React Router, Ant Design (简洁美观的UI组件库)
Database: PostgreSQL (production), SQLite (testing)
Testing: pytest, hypothesis (property-based testing)
API Documentation: Swagger UI (通过 Flask-RESTX 自动生成)

Components and Interfaces

Backend Structure

backend/
├── app/
│   ├── __init__.py          # Flask app factory
│   ├── config.py             # Configuration
│   ├── models/
│   │   ├── __init__.py
│   │   ├── person.py         # Person model
│   │   ├── item.py           # Item model
│   │   └── work_record.py    # WorkRecord model
│   ├── routes/
│   │   ├── __init__.py
│   │   ├── person.py         # Person API routes
│   │   ├── item.py           # Item API routes
│   │   ├── work_record.py    # WorkRecord API routes
│   │   └── export.py         # Export API routes
│   ├── services/
│   │   ├── __init__.py
│   │   ├── person_service.py
│   │   ├── item_service.py
│   │   ├── work_record_service.py
│   │   └── export_service.py
│   └── utils/
│       ├── __init__.py
│       └── validators.py
├── tests/
│   ├── __init__.py
│   ├── conftest.py
│   ├── test_person.py
│   ├── test_item.py
│   ├── test_work_record.py
│   └── test_export.py
├── requirements.txt
└── run.py

API Documentation (Swagger)

系统使用 Flask-RESTX 自动生成 Swagger API 文档：

访问地址: http://localhost:5000/api/docs

功能:

自动生成所有API接口文档
支持在线测试API
显示请求/响应模型

支持导出 OpenAPI 3.0 规范

# Flask-RESTX 配置示例
from flask_restx import Api, Resource, fields

api = Api(
title='Work Statistics API',
version='1.0',
description='工作统计系统 API 文档',
doc='/api/docs'
)

# 定义数据模型
person_model = api.model('Person', {
'id': fields.Integer(description='人员ID'),
'name': fields.String(required=True, description='人员姓名')
})

Frontend Structure

frontend/
├── src/
│   ├── components/
│   │   ├── Layout.jsx          # 主布局（侧边栏+内容区）
│   │   ├── PersonList.jsx      # 人员列表（Table组件）
│   │   ├── PersonForm.jsx      # 人员表单（Modal+Form）
│   │   ├── ItemList.jsx        # 物品列表
│   │   ├── ItemForm.jsx        # 物品表单
│   │   ├── WorkRecordList.jsx  # 工作记录列表（带筛选）
│   │   ├── WorkRecordForm.jsx  # 工作记录表单
│   │   └── Dashboard.jsx       # 仪表盘（统计卡片+图表）
│   ├── services/
│   │   └── api.js              # API调用封装
│   ├── App.jsx
│   └── index.jsx
├── package.json
└── vite.config.js

UI Design Principles

使用 Ant Design 组件库，遵循以下设计原则：

简洁清晰 - 使用 Ant Design 的 Table、Form、Modal 等组件，保持界面整洁
响应式布局 - 使用 Layout 组件实现侧边栏导航 + 内容区布局
一致性 - 统一的按钮样式、表单验证、消息提示
易用性 - 表格支持排序、筛选；表单有清晰的验证提示

UI Components

页面	主要组件	功能
人员管理	Table + Modal + Form	列表展示、新增/编辑弹窗
物品管理	Table + Modal + Form	列表展示、新增/编辑弹窗
工作记录	Table + DatePicker + Select	列表、日期筛选、人员筛选
仪表盘	Card + Statistic	今日统计、快捷操作
导出	Button + DatePicker	选择月份/年份导出Excel

API Endpoints

Person API

Method	Endpoint	Description
GET	`/api/persons`	List all persons
GET	`/api/persons/<id>`	Get person by ID
POST	`/api/persons/create`	Create new person
POST	`/api/persons/update`	Update person
POST	`/api/persons/delete`	Delete person

Item API

Method	Endpoint	Description
GET	`/api/items`	List all items
GET	`/api/items/<id>`	Get item by ID
POST	`/api/items/create`	Create new item
POST	`/api/items/update`	Update item
POST	`/api/items/delete`	Delete item

Work Record API

Method	Endpoint	Description
GET	`/api/work-records`	List work records (with filters)
GET	`/api/work-records/<id>`	Get work record by ID
POST	`/api/work-records/create`	Create new work record
POST	`/api/work-records/update`	Update work record
POST	`/api/work-records/delete`	Delete work record
GET	`/api/work-records/daily-summary`	Get daily summary

Export API

Method	Endpoint	Description
GET	`/api/export/monthly?year=&month=`	Export monthly Excel
GET	`/api/export/yearly?year=`	Export yearly Excel

Response Format

All API responses follow this structure:

{
  "success": true,
  "data": { ... },
  "message": "Operation successful"
}

Error response:

{
  "success": false,
  "error": "Error message",
  "code": "VALIDATION_ERROR"
}

Data Models

Person Model

class Person:
    id: int (primary key, auto-increment)
    name: str (required, non-empty)
    created_at: datetime
    updated_at: datetime

Item Model

class Item:
    id: int (primary key, auto-increment)
    name: str (required, non-empty)
    unit_price: float (required, positive, supports decimal values like 10.50)
    created_at: datetime
    updated_at: datetime

Note: unit_price 使用浮点数类型，支持小数点（如 10.50、25.75 等）。在数据库中使用 NUMERIC(10, 2) 或 FLOAT 类型存储以保证精度。

WorkRecord Model

class WorkRecord:
    id: int (primary key, auto-increment)
    person_id: int (foreign key -> Person.id)
    item_id: int (foreign key -> Item.id)
    work_date: date (required)
    quantity: int (required, positive)
    created_at: datetime
    updated_at: datetime
    
    # Computed property
    @property
    def total_price(self) -> float:
        return self.item.unit_price * self.quantity  # Returns float, e.g., 10.50 * 5 = 52.50

Note: total_price 是计算属性，返回浮点数，等于 unit_price * quantity。

Entity Relationship Diagram

erDiagram
    Person ||--o{ WorkRecord : has
    Item ||--o{ WorkRecord : has
    
    Person {
        int id PK
        string name
        datetime created_at
        datetime updated_at
    }
    
    Item {
        int id PK
        string name
        decimal unit_price
        datetime created_at
        datetime updated_at
    }
    
    WorkRecord {
        int id PK
        int person_id FK
        int item_id FK
        date work_date
        int quantity
        datetime created_at
        datetime updated_at
    }

Excel Export Format

Detail Sheet (明细表)

人员	日期	物品	单价	数量	总价
张三	2024-01-01	物品A	10.50	5	52.50
张三	2024-01-01	物品B	20.75	3	62.25

Monthly Summary Sheet (月度汇总)

人员	总金额
张三	1500.00
李四	2000.00
合计	3500.00

Yearly Summary Sheet (年度汇总 - 按月)

人员	1月	2月	...	12月	年度合计
张三	1500	1800	...	2000	20000
李四	2000	2200	...	2500	25000
合计	3500	4000	...	4500	45000

Correctness Properties

A property is a characteristic or behavior that should hold true across all valid executions of a system—essentially, a formal statement about what the system should do. Properties serve as the bridge between human-readable specifications and machine-verifiable correctness guarantees.

Property 1: Person CRUD Round-Trip

For any valid person name, creating a person, then retrieving it by ID, should return the same name. Updating the person's name and retrieving again should return the new name. Deleting the person should make it no longer retrievable.

Validates: Requirements 1.1, 1.2, 1.3, 1.4

Property 2: Person Name Validation

For any string composed entirely of whitespace (including empty string), attempting to create or update a person with that name should be rejected with a validation error.

Validates: Requirements 1.5

Property 3: Item CRUD Round-Trip

For any valid item (non-empty name, positive unit_price), creating an item, then retrieving it by ID, should return the same name and unit_price. Updating the item and retrieving again should return the updated values. Deleting the item should make it no longer retrievable.

Validates: Requirements 2.1, 2.2, 2.3, 2.4

Property 4: Item Validation

For any item with empty name or non-positive unit_price, attempting to create or update should be rejected with a validation error.

Validates: Requirements 2.5

Property 5: Work Record Total Price Calculation

For any work record with a valid person, item, and positive quantity, the total_price should equal item.unit_price * quantity.

Validates: Requirements 3.1, 3.3

Property 6: Work Record Filter Consistency

For any set of work records and a filter (person_id or date_range), the filtered results should contain only records matching the filter criteria.

Validates: Requirements 3.2

Property 7: Work Record Quantity Validation

For any quantity that is zero or negative, attempting to create or update a work record should be rejected with a validation error.

Validates: Requirements 3.5

Property 8: Work Record Reference Validation

For any non-existent person_id or item_id, attempting to create a work record should be rejected with a reference error.

Validates: Requirements 3.6

Property 9: Daily Summary Consistency

For any date with work records, the daily summary total for each person should equal the sum of their individual work record total_prices for that date.

Validates: Requirements 4.1, 4.2

Property 10: Monthly Export Completeness

For any year and month with work records, the exported Excel should contain exactly all work records for that month, and the summary sheet totals should equal the sum of detail records.

Validates: Requirements 5.1, 5.3

Property 11: Yearly Export Completeness

For any year with work records, the exported Excel should contain exactly all work records for that year, and the monthly breakdown totals should sum to the yearly total.

Validates: Requirements 6.1, 6.3

Property 12: API Response Consistency

For any API request, the response should contain either {success: true, data: ...} or {success: false, error: ...} structure.

Validates: Requirements 8.3

Property 13: Referential Integrity

For any person or item with associated work records, deleting that person or item should either fail with an error or cascade delete the associated work records (based on configuration).

Validates: Requirements 7.4

Error Handling

Validation Errors (HTTP 400)

Empty or whitespace-only person name
Empty item name
Non-positive item unit_price
Non-positive work record quantity
Invalid date format
Missing required fields

Reference Errors (HTTP 404)

Person not found by ID
Item not found by ID
Work record not found by ID
Non-existent person_id or item_id in work record

Server Errors (HTTP 500)

Database connection failure
Excel generation failure
Unexpected exceptions

Error Response Format

{
    "success": False,
    "error": "Human-readable error message",
    "code": "ERROR_CODE",  # e.g., VALIDATION_ERROR, NOT_FOUND, REFERENCE_ERROR
    "details": {}  # Optional additional details
}

Testing Strategy

Testing Framework

Unit Tests: pytest
Property-Based Tests: hypothesis (Python PBT library)
Frontend Tests: Jest + React Testing Library

Unit Tests

Unit tests focus on specific examples and edge cases:

Test each API endpoint with valid inputs
Test validation error cases (empty names, invalid prices)
Test reference error cases (non-existent IDs)
Test Excel export format and content

Property-Based Tests

Property-based tests verify universal properties across many generated inputs:

Minimum 100 iterations per property test
Each test references its design document property
Tag format: Feature: work-statistics-system, Property N: [property description]

Test Configuration

# conftest.py
import pytest
from hypothesis import settings

# Configure hypothesis for minimum 100 examples
settings.register_profile("ci", max_examples=100)
settings.load_profile("ci")

Test Coverage Goals

All CRUD operations for Person, Item, WorkRecord
All validation rules
Total price calculation
Filter functionality
Daily summary aggregation
Excel export content and format

design.md 14 KB Historik Rå