student_management_system/dao/employment_dao.py

# path: dao/employment_dao.py
# time：2026年9月12日10:52
# title：就业信息数据库操作
# author：周兴
# info：负责就业信息的增、查、改、逻辑删除，DAO是Data Access Object 数据访问对象

from sqlalchemy.orm import Session

from model import Student
from model.employment_model import Employment
from schema.employment_schema import EmploymentCreate, EmploymentUpdate


def validate_employment_times(start, offer):
    """允许未确定日期；两项均填写时，Offer 时间必须不早于开放时间。"""
    if start is not None and offer is not None:
        if offer < start:
            raise ValueError("Offer 下发时间不能早于就业开放时间，请调整后再保存。")

# 新增就业信息
def create_employment(
    db: Session,
    employment_data: EmploymentCreate
):
    validate_employment_times(employment_data.employment_start_time, employment_data.offer_time)
    # 创建 Employment ORM 对象
    new_employment = Employment(
        student_id=employment_data.student_id,
        employment_start_time=employment_data.employment_start_time,
        offer_time=employment_data.offer_time,
        company_name=employment_data.company_name,
        salary=employment_data.salary
    )


    # 添加到数据库会话
    db.add(new_employment)
    # 提交事务
    db.commit()
    # 刷新对象，获取数据库生成的id信息
    db.refresh(new_employment)

    return new_employment

# 查询就业信息（多条件查询）
def search_employments(
    db: Session,
    student_id: int | None = None,
    company_name: str | None = None,
    min_salary: float | None = None,
    max_salary: float | None = None
):
    # 先查询所有没有被逻辑删除的数据
    query = db.query(Employment).filter(
        Employment.flag == 1
    )
    # 按学生ID查询
    if student_id is not None:
        query = query.filter(
            Employment.student_id == student_id
        )
    # 按公司名称查询
    if company_name is not None:
        query = query.filter(
            Employment.company_name == company_name
        )
    # 最低工资
    if min_salary is not None:
        query = query.filter(
            Employment.salary >= min_salary
        )
    # 最高工资
    if max_salary is not None:
        query = query.filter(
            Employment.salary <= max_salary
        )
    return query.all()

# 修改就业信息（根据 student_id 进行修改）
def update_employment(
    db: Session,
    student_id: int,
    employment_data: EmploymentUpdate,
):
    # 根据student_id查就业信息
    employment = db.query(Employment).filter(
        Employment.student_id == student_id,
        Employment.flag == 1
    ).first()

    if employment is None:
        return None

    # 先校验合并后的时间，再修改对象；省略或 null 保持原值。
    start = employment_data.employment_start_time if employment_data.employment_start_time is not None else employment.employment_start_time
    offer = employment_data.offer_time if employment_data.offer_time is not None else employment.offer_time
    validate_employment_times(start, offer)

    if employment_data.employment_start_time is not None:
        employment.employment_start_time = employment_data.employment_start_time

    if employment_data.offer_time is not None:
        employment.offer_time = employment_data.offer_time

    if employment_data.company_name is not None:
        employment.company_name = employment_data.company_name

    if employment_data.salary is not None:
        employment.salary = employment_data.salary

    db.commit()
    db.refresh(employment)

    return employment


# 逻辑删除就业信息 （根据 student_id 逻辑删除）
def delete_employment(
        db: Session,
        student_id: int
):
    employment = db.query(Employment).filter(
        Employment.student_id == student_id,
        Employment.flag == 1
    ).first()

    if employment is None:
        return None

    # 逻辑删除
    employment.flag = 0

    db.commit()
    db.refresh(employment)

    return employment

# 判断学生是否存在
# 如果student表里面student_id已经存在，就返回Student ORM对象
# 如果student表里面student_id不存在，返回None
def check_student_exists(
        db: Session,
        student_id: int
):
    student = db.query(Student).filter(
        Student.sid == student_id,
        Student.flag == 1
    ).first()

    return student

# 判断学生是否已经存在就业信息
# 避免给已经有就业信息的学生再次插入就业信息时抛出500异常
def check_employment_exists(
        db: Session,
        student_id: int
):
    employment = db.query(Employment).filter(
        Employment.student_id == student_id,
        Employment.flag == 1
    ).first()

    return employment