tdm_server_rust/repository/
questionnaire_repo.rs

1//! 接稿问卷数据访问层 (Questionnaire Repository)
2//!
3//! 封装 `questionnaire` 表的提交与多维筛选查询。
4
5use crate::entity::questionnaire::Questionnaire;
6use chrono::{DateTime, Utc};
7use sqlx::{MySql, Pool, Row};
8
9/// 问卷列表/详情查询字段
10const SELECT_COLS: &str = "id, member_id, willingness, acceptable_lengths, update_frequency, \
11    preferred_genres, avoid_topics, note, remark, last_assigned_time, created_at, updated_at";
12
13/// 问卷仓储
14pub struct QuestionnaireRepository {
15    /// 连接池
16    pool: Pool<MySql>,
17}
18
19impl QuestionnaireRepository {
20    /// 构造仓储
21    pub fn new(pool: Pool<MySql>) -> Self {
22        Self { pool }
23    }
24
25    /// 按组员 ID 查询问卷
26    #[tracing::instrument(skip_all, level = "debug")]
27    pub async fn get_by_member_id(&self, member_id: i32) -> crate::error::ApiResult<Option<Questionnaire>> {
28        let sql = format!("SELECT {SELECT_COLS} FROM questionnaire WHERE member_id = ?");
29        let row = sqlx::query(&sql).bind(member_id).fetch_optional(&self.pool).await?;
30        Ok(row.map(|r| row_to_questionnaire(&r)))
31    }
32
33    /// 条件列表查询（不含岗位筛选）
34    #[tracing::instrument(skip_all, level = "debug")]
35    pub async fn list_all(
36        &self,
37        willingness: Option<i16>,
38        length: Option<&str>,
39        frequency: Option<i16>,
40        member_id: Option<i32>,
41    ) -> crate::error::ApiResult<Vec<Questionnaire>> {
42        let mut sql = format!("SELECT {SELECT_COLS} FROM questionnaire WHERE 1=1");
43        if willingness.is_some() {
44            sql.push_str(" AND willingness = ?");
45        }
46        if length.is_some() {
47            sql.push_str(" AND acceptable_lengths = ?");
48        }
49        if frequency.is_some() {
50            sql.push_str(" AND update_frequency = ?");
51        }
52        if member_id.is_some() {
53            sql.push_str(" AND member_id = ?");
54        }
55        let mut q = sqlx::query(&sql);
56        if let Some(v) = willingness {
57            q = q.bind(v);
58        }
59        if let Some(v) = length {
60            q = q.bind(v);
61        }
62        if let Some(v) = frequency {
63            q = q.bind(v);
64        }
65        if let Some(v) = member_id {
66            q = q.bind(v);
67        }
68        let rows = q.fetch_all(&self.pool).await?;
69        Ok(rows.into_iter().map(|r| row_to_questionnaire(&r)).collect())
70    }
71
72    /// 按岗位筛选问卷列表
73    #[tracing::instrument(skip_all, level = "debug")]
74    pub async fn list_by_post(
75        &self,
76        post_id: i32,
77        willingness: Option<i16>,
78        length: Option<&str>,
79        frequency: Option<i16>,
80        member_id: Option<i32>,
81    ) -> crate::error::ApiResult<Vec<Questionnaire>> {
82        let mut sql = format!(
83            "SELECT q.id, q.member_id, q.willingness, q.acceptable_lengths, q.update_frequency, \
84             q.preferred_genres, q.avoid_topics, q.note, q.remark, q.last_assigned_time, \
85             q.created_at, q.updated_at \
86             FROM questionnaire q \
87             INNER JOIN memberpost mp ON q.member_id = mp.memberId \
88             WHERE mp.postId = ?"
89        );
90        if willingness.is_some() {
91            sql.push_str(" AND q.willingness = ?");
92        }
93        if length.is_some() {
94            sql.push_str(" AND q.acceptable_lengths = ?");
95        }
96        if frequency.is_some() {
97            sql.push_str(" AND q.update_frequency = ?");
98        }
99        if member_id.is_some() {
100            sql.push_str(" AND q.member_id = ?");
101        }
102        let mut q = sqlx::query(&sql).bind(post_id);
103        if let Some(v) = willingness {
104            q = q.bind(v);
105        }
106        if let Some(v) = length {
107            q = q.bind(v);
108        }
109        if let Some(v) = frequency {
110            q = q.bind(v);
111        }
112        if let Some(v) = member_id {
113            q = q.bind(v);
114        }
115        let rows = q.fetch_all(&self.pool).await?;
116        Ok(rows.into_iter().map(|r| row_to_questionnaire(&r)).collect())
117    }
118
119    /// 新增问卷
120    #[tracing::instrument(skip_all, level = "debug")]
121    pub async fn insert(&self, q: &Questionnaire) -> crate::error::ApiResult<i64> {
122        let r = sqlx::query(
123            "INSERT INTO questionnaire(member_id, willingness, acceptable_lengths, update_frequency, \
124             preferred_genres, avoid_topics, note, remark, created_at, updated_at) \
125             VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())",
126        )
127        .bind(q.member_id.unwrap_or(0))
128        .bind(q.willingness)
129        .bind(&q.acceptable_lengths)
130        .bind(q.update_frequency)
131        .bind(&q.preferred_genres)
132        .bind(&q.avoid_topics)
133        .bind(&q.note)
134        .bind(&q.remark)
135        .execute(&self.pool)
136        .await?;
137        Ok(r.last_insert_id() as i64)
138    }
139
140    /// 更新问卷
141    #[tracing::instrument(skip_all, level = "debug")]
142    pub async fn update(&self, q: &Questionnaire) -> crate::error::ApiResult<()> {
143        sqlx::query(
144            "UPDATE questionnaire SET willingness = ?, acceptable_lengths = ?, update_frequency = ?, \
145             preferred_genres = ?, avoid_topics = ?, note = ?, remark = ?, updated_at = NOW() \
146             WHERE id = ?",
147        )
148        .bind(q.willingness)
149        .bind(&q.acceptable_lengths)
150        .bind(q.update_frequency)
151        .bind(&q.preferred_genres)
152        .bind(&q.avoid_topics)
153        .bind(&q.note)
154        .bind(&q.remark)
155        .bind(q.id)
156        .execute(&self.pool)
157        .await?;
158        Ok(())
159    }
160
161    /// 查询翻译岗最近接稿时间
162    #[tracing::instrument(skip_all, level = "debug")]
163    pub async fn select_last_assigned_time(
164        &self,
165        member_id: i32,
166    ) -> crate::error::ApiResult<Option<DateTime<Utc>>> {
167        let row = sqlx::query(
168            "SELECT MAX(med.translatorSetupTime) AS lastTime FROM mangaepisodedetail med \
169             JOIN mangaepisodetb me ON med.episodeId = me.Id \
170             WHERE me.translatorId = ? AND med.translatorSetupTime IS NOT NULL",
171        )
172        .bind(member_id)
173        .fetch_one(&self.pool)
174        .await?;
175        Ok(row.try_get("lastTime").ok())
176    }
177}
178
179/// 行映射为问卷
180fn row_to_questionnaire(row: &sqlx::mysql::MySqlRow) -> Questionnaire {
181    Questionnaire {
182        id: row.try_get("id").ok(),
183        member_id: row.try_get("member_id").ok(),
184        willingness: row.try_get("willingness").ok(),
185        acceptable_lengths: row.try_get("acceptable_lengths").ok(),
186        update_frequency: row.try_get("update_frequency").ok(),
187        preferred_genres: row.try_get("preferred_genres").ok(),
188        avoid_topics: row.try_get("avoid_topics").ok(),
189        note: row.try_get("note").ok(),
190        remark: row.try_get("remark").ok(),
191        last_assigned_time: row.try_get("last_assigned_time").ok(),
192        created_at: row.try_get("created_at").ok(),
193        updated_at: row.try_get("updated_at").ok(),
194    }
195}
tdm_server_rust/repository/questionnaire_repo.rs

tdm_server_rust/repository/
questionnaire_repo.rs
