tdm_server_rust/repository/
episode_repo.rs

1//! 话数数据访问层 (Episode Repository)
2//!
3//! 封装 `mangaepisodetb` 表及 workflow JOIN 查询。
4//! 行映射逻辑见 `episode_mapper` 模块。
5
6use crate::{
7    entity::episode::{
8        EpisodeDetailVo, EpisodeEditDto, EpisodeListVo, EpisodeSimpleListVo, MemberStatistics,
9        NewestEpisodeVo, PublishLinkRequest, Statistics, UploadPageVo,
10    },
11    repository::episode_mapper::{
12        row_to_episode_detail, row_to_episode_list, row_to_episode_simple, row_to_newest,
13        row_to_upload_page, EPISODE_WORKFLOW_FROM, EPISODE_WORKFLOW_SELECT,
14    },
15};
16use chrono::{DateTime, NaiveDateTime, Utc};
17use sqlx::{MySql, Pool, Row};
18use tracing::Instrument;
19
20/// 读取 MySQL SUM 聚合结果
21fn read_mysql_sum(row: &sqlx::mysql::MySqlRow, col: &str) -> i64 {
22    row.try_get::<i64, _>(col)
23        .or_else(|_| row.try_get::<i32, _>(col).map(i64::from))
24        .unwrap_or(0)
25}
26
27/// 岗位完成统计
28#[derive(Debug, Clone)]
29pub struct PostCompletionStats {
30    /// 翻译完成数
31    pub translator_count: i64,
32    /// 校对完成数
33    pub proofreader_count: i64,
34    /// 嵌字完成数
35    pub letterer_count: i64,
36    /// 审稿完成数
37    pub reviewer_count: i64,
38}
39
40/// 话数仓储
41pub struct EpisodeRepository {
42    /// 连接池
43    pool: Pool<MySql>,
44}
45
46impl EpisodeRepository {
47    /// 构造仓储
48    pub fn new(pool: Pool<MySql>) -> Self {
49        Self { pool }
50    }
51
52    /// 按漫画 ID 查询话数简单列表（含文件信息）。
53    ///
54    /// # 返回值
55    ///
56    /// 返回漫画下所有话数，按 `manga_episode` 数字降序排列。
57    #[tracing::instrument(skip_all, level = "debug")]
58    pub async fn list_by_manga_id(&self, manga_id: i32) -> crate::error::ApiResult<Vec<EpisodeSimpleListVo>> {
59        let sql = format!(
60            "SELECT {EPISODE_WORKFLOW_SELECT} {EPISODE_WORKFLOW_FROM} \
61             WHERE me.mangaId = ? ORDER BY CAST(me.mangaEpisode AS DECIMAL(10, 2)) DESC"
62        );
63        let rows = sqlx::query(&sql).bind(manga_id).fetch_all(&self.pool).await?;
64        Ok(rows.iter().map(row_to_episode_simple).collect())
65    }
66
67    /// 按漫画 ID 查询话数完整列表
68    #[tracing::instrument(skip_all, level = "debug")]
69    pub async fn list_full_by_manga_id(&self, manga_id: i32) -> crate::error::ApiResult<Vec<EpisodeListVo>> {
70        let sql = format!(
71            "SELECT {EPISODE_WORKFLOW_SELECT} {EPISODE_WORKFLOW_FROM} \
72             WHERE me.mangaId = ? ORDER BY CAST(me.mangaEpisode AS DECIMAL(10, 2)) DESC"
73        );
74        let rows = sqlx::query(&sql).bind(manga_id).fetch_all(&self.pool).await?;
75        Ok(rows.iter().map(row_to_episode_list).collect())
76    }
77
78    /// 按 ID 查询话数详情。
79    ///
80    /// # 返回值
81    ///
82    /// - `Ok(Some(detail))` — 找到话数
83    /// - `Ok(None)` — 话数不存在
84    #[tracing::instrument(skip_all, level = "debug")]
85    pub async fn get_by_id(&self, id: i32) -> crate::error::ApiResult<Option<EpisodeDetailVo>> {
86        let sql = format!(
87            "SELECT {EPISODE_WORKFLOW_SELECT} {EPISODE_WORKFLOW_FROM} WHERE me.Id = ?"
88        );
89        let row = sqlx::query(&sql).bind(id).fetch_optional(&self.pool).await?;
90        Ok(row.as_ref().map(row_to_episode_detail))
91    }
92
93    /// 查询话数本地稿件路径（translatorFile / proofreaderFile / timerFile）
94    #[tracing::instrument(skip_all, level = "debug")]
95    pub async fn get_legacy_file_path(
96        &self,
97        id: i32,
98        post_name: &str,
99    ) -> crate::error::ApiResult<Option<String>> {
100        let row = sqlx::query(
101            "SELECT translatorFile, proofreaderFile, timerFile FROM mangaepisodetb WHERE Id = ?",
102        )
103        .bind(id)
104        .fetch_optional(&self.pool)
105        .await?;
106        let Some(row) = row else {
107            return Ok(None);
108        };
109        let col = match post_name.to_lowercase().as_str() {
110            "translator" => "translatorFile",
111            "proofreader" => "proofreaderFile",
112            "timer" => "timerFile",
113            _ => {
114                return Err(crate::error::AppError::business(format!(
115                    "当前岗位文件不存在喵：{post_name}"
116                )));
117            }
118        };
119        Ok(row.try_get(col).ok())
120    }
121
122    /// 更新话数本地稿件路径
123    #[tracing::instrument(skip_all, level = "debug")]
124    pub async fn set_legacy_file_path(
125        &self,
126        id: i32,
127        post_name: &str,
128        path: &str,
129    ) -> crate::error::ApiResult<()> {
130        let sql = match post_name.to_lowercase().as_str() {
131            "translator" => "UPDATE mangaepisodetb SET translatorFile = ?, updateTime = NOW() WHERE Id = ?",
132            "proofreader" => {
133                "UPDATE mangaepisodetb SET proofreaderFile = ?, updateTime = NOW() WHERE Id = ?"
134            }
135            "timer" => "UPDATE mangaepisodetb SET timerFile = ?, updateTime = NOW() WHERE Id = ?",
136            _ => {
137                return Err(crate::error::AppError::business(format!(
138                    "当前岗位文件不存在喵：{post_name}"
139                )));
140            }
141        };
142        sqlx::query(sql).bind(path).bind(id).execute(&self.pool).await?;
143        Ok(())
144    }
145
146    /// 查询漫画最新话
147    #[tracing::instrument(skip_all, level = "debug")]
148    pub async fn get_newest_by_manga_id(&self, manga_id: i32) -> crate::error::ApiResult<Option<NewestEpisodeVo>> {
149        let row = sqlx::query(
150            "SELECT me.Id FROM mangaepisodetb me WHERE me.mangaId = ? ORDER BY me.Id DESC LIMIT 1",
151        )
152        .bind(manga_id)
153        .fetch_optional(&self.pool)
154        .await?;
155        let Some(ep_row) = row else {
156            return Ok(None);
157        };
158        let ep_id: i32 = ep_row.get("Id");
159        let sql = format!(
160            "SELECT {EPISODE_WORKFLOW_SELECT} {EPISODE_WORKFLOW_FROM} WHERE me.Id = ?"
161        );
162        let row = sqlx::query(&sql).bind(ep_id).fetch_optional(&self.pool).await?;
163        Ok(row.as_ref().map(row_to_newest))
164    }
165
166    /// 分页查询已上传稿件（SQL LIMIT/OFFSET，对齐 Java PageHelper）
167    #[tracing::instrument(skip_all, level = "debug")]
168    pub async fn page_uploaded_submit(
169        &self,
170        page: i32,
171        page_size: i32,
172        manga_tran_name: Option<&str>,
173        username: Option<&str>,
174    ) -> crate::error::ApiResult<(i64, Vec<UploadPageVo>)> {
175        let from_sql = " FROM mangaepisodetb me \
176             LEFT JOIN membertb m ON m.Id = me.translatorId \
177             LEFT JOIN membertb m2 ON m2.Id = me.proofreaderId \
178             LEFT JOIN mangatb m3 ON m3.Id = me.mangaId \
179             WHERE (me.translatorFile IS NOT NULL OR me.proofreaderFile IS NOT NULL OR me.timerFile IS NOT NULL \
180             OR me.translator_file_oss_id IS NOT NULL OR me.proofreader_file_oss_id IS NOT NULL OR me.timer_file_oss_id IS NOT NULL)";
181        let mut where_extra = String::new();
182        if manga_tran_name.is_some() {
183            where_extra.push_str(" AND m3.mangaTranName = ?");
184        }
185        if username.is_some() {
186            where_extra.push_str(" AND (m2.username = ? OR m.username = ?)");
187        }
188
189        let count_sql = format!("SELECT COUNT(*) AS cnt{from_sql}{where_extra}");
190        let mut count_q = sqlx::query(&count_sql);
191        count_q = bind_uploaded_submit_filters(count_q, manga_tran_name, username);
192        let total: i64 = count_q.fetch_one(&self.pool).await?.get("cnt");
193
194        let page = page.max(1);
195        let page_size = page_size.max(1);
196        let offset = (page - 1) * page_size;
197        let data_sql = format!(
198            "SELECT me.*, m.username AS translatorName, m.email, m2.username AS proofreaderName, \
199             m2.email AS emailNow, letterer.username AS lettererName, timer.username AS timerName, \
200             reviewer.username AS reviewerName, m3.mangaTranName AS mangaName, \
201             providerFile.filename AS providerFilename, translatorFile.filename AS translatorFilename, \
202             proofreaderFile.filename AS proofreaderFilename, lettererFile.filename AS lettererFilename, \
203             timerFile.filename AS timerFilename \
204             FROM mangaepisodetb me \
205             LEFT JOIN membertb m ON m.Id = me.translatorId \
206             LEFT JOIN membertb m2 ON m2.Id = me.proofreaderId \
207             LEFT JOIN membertb letterer ON letterer.Id = me.lettererId \
208             LEFT JOIN membertb reviewer ON reviewer.Id = me.reviewerId \
209             LEFT JOIN membertb timer ON timer.Id = me.timerId \
210             LEFT JOIN mangatb m3 ON m3.Id = me.mangaId \
211             LEFT JOIN oss providerFile ON providerFile.id = me.provider_file_oss_id \
212             LEFT JOIN oss translatorFile ON translatorFile.id = me.translator_file_oss_id \
213             LEFT JOIN oss proofreaderFile ON proofreaderFile.id = me.proofreader_file_oss_id \
214             LEFT JOIN oss lettererFile ON lettererFile.id = me.letterer_file_oss_id \
215             LEFT JOIN oss timerFile ON timerFile.id = me.timer_file_oss_id \
216             WHERE (me.translatorFile IS NOT NULL OR me.proofreaderFile IS NOT NULL OR me.timerFile IS NOT NULL \
217             OR me.translator_file_oss_id IS NOT NULL OR me.proofreader_file_oss_id IS NOT NULL OR me.timer_file_oss_id IS NOT NULL){where_extra} \
218             ORDER BY me.updateTime DESC LIMIT ? OFFSET ?"
219        );
220        let mut data_q = sqlx::query(&data_sql);
221        data_q = bind_uploaded_submit_filters(data_q, manga_tran_name, username);
222        data_q = data_q.bind(page_size).bind(offset);
223        let rows = async {
224            data_q.fetch_all(&self.pool).await
225        }
226        .instrument(tracing::debug_span!("list_uploaded_submit_sql"))
227        .await?;
228        let result = {
229            let _span = tracing::debug_span!("list_uploaded_submit_map").entered();
230            rows.iter().map(row_to_upload_page).collect()
231        };
232        Ok((total, result))
233    }
234
235    /// 统计同漫画下相同话数标签数量
236    #[tracing::instrument(skip_all, level = "debug")]
237    pub async fn count_episode_by_number(
238        &self,
239        manga_id: i32,
240        manga_episode: &str,
241    ) -> crate::error::ApiResult<i64> {
242        let row = sqlx::query(
243            "SELECT COUNT(*) AS cnt FROM mangaepisodetb WHERE mangaId = ? AND mangaEpisode = ?",
244        )
245        .bind(manga_id)
246        .bind(manga_episode)
247        .fetch_one(&self.pool)
248        .await?;
249        Ok(row.get("cnt"))
250    }
251
252    /// 刷新漫画更新时间
253    #[tracing::instrument(skip_all, level = "debug")]
254    pub async fn touch_manga_update_time(&self, manga_id: i32) -> crate::error::ApiResult<()> {
255        sqlx::query("UPDATE mangatb SET updateTime = NOW() WHERE Id = ?")
256            .bind(manga_id)
257            .execute(&self.pool)
258            .await?;
259        Ok(())
260    }
261
262    /// 新增话数（对齐 Java insertEpisode）
263    #[tracing::instrument(skip_all, level = "debug")]
264    pub async fn insert(&self, dto: &EpisodeEditDto) -> crate::error::ApiResult<i32> {
265        let manga_id = dto.manga_id.ok_or_else(|| {
266            crate::error::AppError::business("缺少漫画 ID")
267        })?;
268        let r = sqlx::query(
269            "INSERT INTO mangaepisodetb(mangaId, mangaEpisode, mangaEpisodeName, \
270             providerId, translatorId, proofreaderId, lettererId, timerId, reviewerId, \
271             setupTime, updateTime, publishLink) \
272             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), ?)",
273        )
274        .bind(manga_id)
275        .bind(&dto.manga_episode)
276        .bind(&dto.manga_episode_name)
277        .bind(dto.provider_id)
278        .bind(dto.translator_id)
279        .bind(dto.proofreader_id)
280        .bind(dto.letterer_id)
281        .bind(dto.timer_id)
282        .bind(dto.reviewer_id)
283        .bind(&dto.publish_link)
284        .execute(&self.pool)
285        .await?;
286        let id = r.last_insert_id() as i32;
287        // #region agent log
288        crate::utils::agent_debug::log(
289            "E1",
290            "episode_repo.rs:insert",
291            "episode inserted",
292            serde_json::json!({
293                "episodeId": id,
294                "mangaId": manga_id,
295                "providerId": dto.provider_id,
296                "translatorId": dto.translator_id
297            }),
298        );
299        // #endregion
300        Ok(id)
301    }
302
303    /// 新增话数详情及岗位接稿时间（对齐 Java insertEpisodeDetail）
304    #[tracing::instrument(skip_all, level = "debug")]
305    pub async fn insert_detail(&self, episode_id: i32, dto: &EpisodeEditDto) -> crate::error::ApiResult<()> {
306        sqlx::query(
307            "INSERT INTO mangaepisodedetail(episodeId, translatorSetupTime, proofreaderSetupTime, \
308             lettererSetupTime, timerSetupTime, reviewerSetupTime) \
309             VALUES (?, \
310             IF(? IS NULL, NULL, NOW()), \
311             IF(? IS NULL, NULL, NOW()), \
312             IF(? IS NULL, NULL, NOW()), \
313             IF(? IS NULL, NULL, NOW()), \
314             IF(? IS NULL, NULL, NOW()))",
315        )
316        .bind(episode_id)
317        .bind(dto.translator_id)
318        .bind(dto.proofreader_id)
319        .bind(dto.letterer_id)
320        .bind(dto.timer_id)
321        .bind(dto.reviewer_id)
322        .execute(&self.pool)
323        .await?;
324        Ok(())
325    }
326
327    /// 仅更新发布链接
328    #[tracing::instrument(skip_all, level = "debug")]
329    pub async fn update_publish_link_only(
330        &self,
331        id: i32,
332        publish_link: Option<String>,
333    ) -> crate::error::ApiResult<()> {
334        sqlx::query(
335            "UPDATE mangaepisodetb SET publishLink = ?, updateTime = NOW() WHERE Id = ?",
336        )
337        .bind(publish_link)
338        .bind(id)
339        .execute(&self.pool)
340        .await?;
341        Ok(())
342    }
343
344    /// 更新话数（对齐 Java updateMangaEpisode）
345    #[tracing::instrument(skip_all, level = "debug")]
346    pub async fn update(&self, dto: &EpisodeEditDto) -> crate::error::ApiResult<()> {
347        let id = dto.id.ok_or_else(|| crate::error::AppError::business("缺少话数 ID"))?;
348        let manga_episode_name = dto.manga_episode_name.as_deref().map(|s| {
349            if s.is_empty() || s == "null" {
350                None
351            } else {
352                Some(s)
353            }
354        }).flatten();
355        sqlx::query(
356            "UPDATE mangaepisodetb SET \
357             mangaEpisode = COALESCE(?, mangaEpisode), \
358             mangaEpisodeName = ?, \
359             providerId = ?, translatorId = ?, proofreaderId = ?, \
360             lettererId = ?, timerId = ?, reviewerId = ?, \
361             publishLink = ?, updateTime = NOW() \
362             WHERE Id = ?",
363        )
364        .bind(&dto.manga_episode)
365        .bind(manga_episode_name)
366        .bind(dto.provider_id)
367        .bind(dto.translator_id)
368        .bind(dto.proofreader_id)
369        .bind(dto.letterer_id)
370        .bind(dto.timer_id)
371        .bind(dto.reviewer_id)
372        .bind(&dto.publish_link)
373        .bind(id)
374        .execute(&self.pool)
375        .await?;
376        self.sync_assignment_setup_times(id, dto).await?;
377        if let Some(manga_id) = dto.manga_id {
378            self.touch_manga_update_time(manga_id).await?;
379        }
380        Ok(())
381    }
382
383    /// 同步单话编辑里的岗位指派时间。
384    async fn sync_assignment_setup_times(
385        &self,
386        episode_id: i32,
387        dto: &EpisodeEditDto,
388    ) -> crate::error::ApiResult<()> {
389        for (setup_col, member_id) in assignment_setup_columns(dto) {
390            let sql = if member_id.is_some() {
391                format!(
392                    "UPDATE mangaepisodedetail SET {setup_col} = COALESCE({setup_col}, NOW()) WHERE episodeId = ?"
393                )
394            } else {
395                format!("UPDATE mangaepisodedetail SET {setup_col} = NULL WHERE episodeId = ?")
396            };
397            sqlx::query(&sql)
398                .bind(episode_id)
399                .execute(&self.pool)
400                .await?;
401        }
402        Ok(())
403    }
404
405    /// 删除话数及详情
406    #[tracing::instrument(skip_all, level = "debug")]
407    pub async fn delete_by_id(&self, id: i32) -> crate::error::ApiResult<()> {
408        sqlx::query("DELETE FROM mangaepisodedetail WHERE episodeId = ?")
409            .bind(id)
410            .execute(&self.pool)
411            .await?;
412        sqlx::query("DELETE FROM mangaepisodetb WHERE Id = ?")
413            .bind(id)
414            .execute(&self.pool)
415            .await?;
416        Ok(())
417    }
418
419    /// 批量更新发布链接
420    #[tracing::instrument(skip_all, level = "debug")]
421    pub async fn update_publish_links_batch(
422        &self,
423        requests: &[PublishLinkRequest],
424    ) -> crate::error::ApiResult<()> {
425        if requests.is_empty() {
426            return Ok(());
427        }
428        let mut case_sql = String::from("publishLink = CASE ");
429        let mut ids = Vec::new();
430        for req in requests {
431            case_sql.push_str("WHEN Id = ? THEN ? ");
432            ids.push(req.id);
433        }
434        case_sql.push_str("ELSE publishLink END, updateTime = NOW()");
435        let placeholders = ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
436        let sql = format!(
437            "UPDATE mangaepisodetb SET {case_sql} WHERE Id IN ({placeholders})"
438        );
439        let mut q = sqlx::query(&sql);
440        for req in requests {
441            q = q.bind(req.id).bind(&req.publish_link);
442        }
443        for id in &ids {
444            q = q.bind(id);
445        }
446        q.execute(&self.pool).await?;
447        Ok(())
448    }
449
450    /// 检测发布链接是否重复
451    #[tracing::instrument(skip_all, level = "debug")]
452    pub async fn count_publish_link(&self, publish_link: &str, exclude_id: Option<i32>) -> crate::error::ApiResult<i64> {
453        let row = if let Some(id) = exclude_id {
454            sqlx::query("SELECT COUNT(*) AS cnt FROM mangaepisodetb WHERE publishLink = ? AND Id != ?")
455                .bind(publish_link)
456                .bind(id)
457                .fetch_one(&self.pool)
458                .await?
459        } else {
460            sqlx::query("SELECT COUNT(*) AS cnt FROM mangaepisodetb WHERE publishLink = ?")
461                .bind(publish_link)
462                .fetch_one(&self.pool)
463                .await?
464        };
465        Ok(row.get::<i64, _>("cnt"))
466    }
467
468    /// 统计时间段内各岗位完成数
469    #[tracing::instrument(skip_all, level = "debug")]
470    pub async fn get_statistic_count(
471        &self,
472        start: NaiveDateTime,
473        end: NaiveDateTime,
474    ) -> crate::error::ApiResult<PostCompletionStats> {
475        let row = sqlx::query(
476            "SELECT \
477             CAST(COALESCE(SUM(IF(med.translatorUpdateTime >= ? AND med.translatorUpdateTime <= ?, 1, 0)), 0) AS SIGNED) AS translatorCount, \
478             CAST(COALESCE(SUM(IF(med.proofreaderUpdateTime >= ? AND med.proofreaderUpdateTime <= ?, 1, 0)), 0) AS SIGNED) AS proofreaderCount, \
479             CAST(COALESCE(SUM(IF(med.lettererUpdateTime >= ? AND med.lettererUpdateTime <= ?, 1, 0)), 0) AS SIGNED) AS lettererCount, \
480             CAST(COALESCE(SUM(IF(med.reviewerUpdateTime >= ? AND med.reviewerUpdateTime <= ?, 1, 0)), 0) AS SIGNED) AS reviewerCount \
481             FROM mangaepisodedetail med",
482        )
483        .bind(start)
484        .bind(end)
485        .bind(start)
486        .bind(end)
487        .bind(start)
488        .bind(end)
489        .bind(start)
490        .bind(end)
491        .fetch_one(&self.pool)
492        .await?;
493        Ok(PostCompletionStats {
494            translator_count: read_mysql_sum(&row, "translatorCount"),
495            proofreader_count: read_mysql_sum(&row, "proofreaderCount"),
496            letterer_count: read_mysql_sum(&row, "lettererCount"),
497            reviewer_count: read_mysql_sum(&row, "reviewerCount"),
498        })
499    }
500
501    /// 转为 Statistics（对齐 Java getStatisticCount）
502    pub fn stats_from_post(stats: &PostCompletionStats) -> Statistics {
503        Statistics {
504            translator_count: stats.translator_count as i32,
505            proofreader_count: stats.proofreader_count as i32,
506            letterer_count: stats.letterer_count as i32,
507            reviewer_count: stats.reviewer_count as i32,
508        }
509    }
510
511    /// 查询组员完成统计（对齐 Java MembertbDao.selectMemberStatistics）
512    #[tracing::instrument(skip_all, level = "debug")]
513    pub async fn get_member_statistics(
514        &self,
515        start: DateTime<Utc>,
516        end: DateTime<Utc>,
517        member_id: Option<i32>,
518    ) -> crate::error::ApiResult<Vec<MemberStatistics>> {
519        let mut sql = String::from(
520            "SELECT m.Id AS userId, m.username AS userName, \
521             COALESCE(t.translatorCount, 0) AS translatorCount, \
522             COALESCE(p.proofreaderCount, 0) AS proofreaderCount, \
523             COALESCE(l.lettererCount, 0) AS lettererCount, \
524             COALESCE(ti.timerCount, 0) AS timerCount, \
525             COALESCE(r.reviewerCount, 0) AS reviewerCount \
526             FROM membertb m \
527             LEFT JOIN ( \
528               SELECT me.translatorId AS memberId, COUNT(DISTINCT med.Id) AS translatorCount \
529               FROM mangaepisodetb me JOIN mangaepisodedetail med ON med.episodeId = me.Id \
530               WHERE med.translatorUpdateTime >= ? AND med.translatorUpdateTime < ? \
531               GROUP BY me.translatorId \
532             ) t ON t.memberId = m.Id \
533             LEFT JOIN ( \
534               SELECT me.proofreaderId AS memberId, COUNT(DISTINCT med.Id) AS proofreaderCount \
535               FROM mangaepisodetb me JOIN mangaepisodedetail med ON med.episodeId = me.Id \
536               WHERE med.proofreaderUpdateTime >= ? AND med.proofreaderUpdateTime < ? \
537               GROUP BY me.proofreaderId \
538             ) p ON p.memberId = m.Id \
539             LEFT JOIN ( \
540               SELECT me.lettererId AS memberId, COUNT(DISTINCT med.Id) AS lettererCount \
541               FROM mangaepisodetb me JOIN mangaepisodedetail med ON med.episodeId = me.Id \
542               WHERE med.lettererUpdateTime >= ? AND med.lettererUpdateTime < ? \
543               GROUP BY me.lettererId \
544             ) l ON l.memberId = m.Id \
545             LEFT JOIN ( \
546               SELECT me.timerId AS memberId, COUNT(DISTINCT med.Id) AS timerCount \
547               FROM mangaepisodetb me JOIN mangaepisodedetail med ON med.episodeId = me.Id \
548               WHERE med.timerUpdateTime >= ? AND med.timerUpdateTime < ? \
549               GROUP BY me.timerId \
550             ) ti ON ti.memberId = m.Id \
551             LEFT JOIN ( \
552               SELECT me.reviewerId AS memberId, COUNT(DISTINCT med.Id) AS reviewerCount \
553               FROM mangaepisodetb me JOIN mangaepisodedetail med ON med.episodeId = me.Id \
554               WHERE med.reviewerUpdateTime >= ? AND med.reviewerUpdateTime < ? \
555               GROUP BY me.reviewerId \
556             ) r ON r.memberId = m.Id \
557             WHERE (COALESCE(t.translatorCount,0)+COALESCE(p.proofreaderCount,0)+COALESCE(l.lettererCount,0)+COALESCE(ti.timerCount,0)+COALESCE(r.reviewerCount,0)) > 0",
558        );
559        if member_id.is_some() {
560            sql.push_str(" AND m.Id = ?");
561        }
562        let mut q = sqlx::query(&sql);
563        for _ in 0..5 {
564            q = q.bind(start).bind(end);
565        }
566        if let Some(id) = member_id {
567            q = q.bind(id);
568        }
569        let rows = q.fetch_all(&self.pool).await?;
570        Ok(rows
571            .into_iter()
572            .map(|r| MemberStatistics {
573                user_name: r.try_get("userName").ok(),
574                user_id: r.try_get::<i32, _>("userId").ok().map(|v| v.to_string()),
575                translator_count: r.try_get("translatorCount").unwrap_or(0),
576                proofreader_count: r.try_get("proofreaderCount").unwrap_or(0),
577                letterer_count: r.try_get("lettererCount").unwrap_or(0),
578                timer_count: r.try_get("timerCount").unwrap_or(0),
579                reviewer_count: r.try_get("reviewerCount").unwrap_or(0),
580            })
581            .collect())
582    }
583
584    /// 回退指定岗位流程
585    #[tracing::instrument(skip_all, level = "debug")]
586    pub async fn rollback_episode(&self, episode_id: i32, workflow_type: &str) -> crate::error::ApiResult<Option<i32>> {
587        let post = workflow_type.to_lowercase();
588        let (episode_sql, detail_sql, oss_col) = match post.as_str() {
589            "provider" => (
590                "UPDATE mangaepisodetb SET provider_file_oss_id = NULL, updateTime = NOW() WHERE Id = ?",
591                None,
592                Some("provider_file_oss_id"),
593            ),
594            "translator" => (
595                "UPDATE mangaepisodetb SET translatorFile = NULL, translator_file_oss_id = NULL, updateTime = NOW() WHERE Id = ?",
596                Some("UPDATE mangaepisodedetail SET translatorUpdateTime = NULL WHERE episodeId = ?"),
597                Some("translator_file_oss_id"),
598            ),
599            "proofreader" => (
600                "UPDATE mangaepisodetb SET proofreaderFile = NULL, proofreader_file_oss_id = NULL, updateTime = NOW() WHERE Id = ?",
601                Some("UPDATE mangaepisodedetail SET proofreaderUpdateTime = NULL WHERE episodeId = ?"),
602                Some("proofreader_file_oss_id"),
603            ),
604            "letterer" => (
605                "UPDATE mangaepisodetb SET letterer_file_oss_id = NULL, updateTime = NOW() WHERE Id = ?",
606                Some("UPDATE mangaepisodedetail SET lettererUpdateTime = NULL WHERE episodeId = ?"),
607                Some("letterer_file_oss_id"),
608            ),
609            "timer" => (
610                "UPDATE mangaepisodetb SET timerFile = NULL, timer_file_oss_id = NULL, updateTime = NOW() WHERE Id = ?",
611                Some("UPDATE mangaepisodedetail SET timerUpdateTime = NULL WHERE episodeId = ?"),
612                Some("timer_file_oss_id"),
613            ),
614            "reviewer" => (
615                "",
616                Some("UPDATE mangaepisodedetail SET reviewerUpdateTime = NULL WHERE episodeId = ?"),
617                None,
618            ),
619            _ => {
620                return Err(crate::error::AppError::business("不支持的回退流程类型喵！"));
621            }
622        };
623
624        let oss_id = if let Some(col) = oss_col {
625            let row = sqlx::query(&format!("SELECT {col} AS ossId FROM mangaepisodetb WHERE Id = ?"))
626                .bind(episode_id)
627                .fetch_optional(&self.pool)
628                .await?;
629            row.and_then(|r| r.try_get("ossId").ok())
630        } else {
631            None
632        };
633
634        if !episode_sql.is_empty() {
635            sqlx::query(episode_sql)
636                .bind(episode_id)
637                .execute(&self.pool)
638                .await?;
639        }
640        if let Some(sql) = detail_sql {
641            sqlx::query(sql)
642                .bind(episode_id)
643                .execute(&self.pool)
644                .await?;
645        }
646        Ok(oss_id)
647    }
648
649    /// 查询未发布话数（任务追踪用）
650    #[tracing::instrument(skip_all, level = "debug")]
651    pub async fn list_unpublished_with_detail(&self) -> crate::error::ApiResult<Vec<EpisodeListVo>> {
652        let rows = sqlx::query(
653            "SELECT me.Id, me.mangaId, me.mangaEpisode \
654             FROM mangaepisodetb me \
655             WHERE me.publishLink IS NULL OR me.publishLink = '' \
656             ORDER BY me.Id DESC",
657        )
658        .fetch_all(&self.pool)
659        .await?;
660        Ok(rows
661            .into_iter()
662            .map(|r| EpisodeListVo {
663                id: r.get("Id"),
664                manga_id: r.get("mangaId"),
665                manga_episode: r.try_get("mangaEpisode").ok(),
666                ..Default::default()
667            })
668            .collect())
669    }
670}
671
672/// 绑定藏宝处列表筛选参数
673fn bind_uploaded_submit_filters<'q>(
674    mut q: sqlx::query::Query<'q, sqlx::MySql, sqlx::mysql::MySqlArguments>,
675    manga_tran_name: Option<&'q str>,
676    username: Option<&'q str>,
677) -> sqlx::query::Query<'q, sqlx::MySql, sqlx::mysql::MySqlArguments> {
678    if let Some(v) = manga_tran_name {
679        q = q.bind(v);
680    }
681    if let Some(v) = username {
682        q = q.bind(v).bind(v);
683    }
684    q
685}
686
687/// 获取岗位 ID 对应的接稿时间字段。
688fn assignment_setup_columns(dto: &EpisodeEditDto) -> [(&'static str, Option<i32>); 5] {
689    [
690        ("translatorSetupTime", dto.translator_id),
691        ("proofreaderSetupTime", dto.proofreader_id),
692        ("lettererSetupTime", dto.letterer_id),
693        ("timerSetupTime", dto.timer_id),
694        ("reviewerSetupTime", dto.reviewer_id),
695    ]
696}
697
698#[cfg(test)]
699mod tests {
700    use super::*;
701
702    /// 创建用于岗位接稿时间测试的默认话数编辑数据。
703    fn dto_with_assignments() -> EpisodeEditDto {
704        EpisodeEditDto {
705            id: Some(1),
706            manga_id: Some(2),
707            manga_episode: Some("11.1".to_string()),
708            manga_episode_end: None,
709            manga_episode_name: Some("测试话".to_string()),
710            provider_id: Some(97),
711            translator_id: Some(10),
712            proofreader_id: Some(20),
713            letterer_id: Some(30),
714            timer_id: None,
715            reviewer_id: Some(40),
716            publish_link: None,
717        }
718    }
719
720    #[test]
721    fn assignment_setup_columns_maps_member_ids_to_setup_columns() {
722        let columns = assignment_setup_columns(&dto_with_assignments());
723
724        assert_eq!(
725            columns,
726            [
727                ("translatorSetupTime", Some(10)),
728                ("proofreaderSetupTime", Some(20)),
729                ("lettererSetupTime", Some(30)),
730                ("timerSetupTime", None),
731                ("reviewerSetupTime", Some(40)),
732            ]
733        );
734    }
735
736    #[test]
737    fn assignment_setup_columns_keeps_empty_assignment_as_none() {
738        let mut dto = dto_with_assignments();
739        dto.proofreader_id = None;
740        dto.letterer_id = None;
741
742        let columns = assignment_setup_columns(&dto);
743
744        assert_eq!(columns[1], ("proofreaderSetupTime", None));
745        assert_eq!(columns[2], ("lettererSetupTime", None));
746    }
747}
tdm_server_rust/repository/episode_repo.rs

tdm_server_rust/repository/
episode_repo.rs
