tdm_server_rust/repository/
task_tracking_repo.rs

1//! 稿件监控数据访问层 (Task Tracking Repository)
2//!
3//! 封装任务看板的多表联查逻辑，聚合组员待做稿件数据。
4
5use crate::db::DbConn;
6use crate::entity::{
7    episode::{
8        EpisodeTask, EpisodeType, MemberTaskCount, TaskTrackingResponse, WorkflowEpisodeItem,
9    },
10    manga::Mangatb,
11};
12use crate::repository::{
13    episode_sort,
14    workflow_filter::{
15        self, apply_workflow_order, build_workflow_filtered_select, build_workflow_page_select,
16        WorkflowEpisodeQuery,
17    },
18};
19use crate::sea_entity::{mangaepisodedetail, mangaepisodetb, mangastationed, mangatb, membertb};
20use chrono::{DateTime, NaiveDateTime, Utc};
21use sea_orm::{
22    sea_query::{
23        Alias, Asterisk, CommonTableExpression, Expr, ExprTrait, JoinType, NullOrdering, Order,
24        PostgresQueryBuilder, Query, QueryStatementWriter, SelectStatement, UnionType, WithClause,
25    },
26    ConnectionTrait, DatabaseConnection, DbBackend, QueryResult, Statement,
27};
28use sqlx::postgres::PgPool;
29use std::collections::HashMap;
30
31/// 话数表别名
32fn alias_me() -> Alias {
33    Alias::new("me")
34}
35
36/// 话数详情表别名
37fn alias_med() -> Alias {
38    Alias::new("med")
39}
40
41/// 漫画表别名
42fn alias_m() -> Alias {
43    Alias::new("m")
44}
45
46/// 组员表别名
47fn alias_member() -> Alias {
48    Alias::new("member")
49}
50
51/// 引用话数表列
52fn col_me(c: mangaepisodetb::Column) -> sea_orm::sea_query::SimpleExpr {
53    Expr::col((alias_me(), c)).into()
54}
55
56/// 引用话数详情表列
57fn col_med(c: mangaepisodedetail::Column) -> sea_orm::sea_query::SimpleExpr {
58    Expr::col((alias_med(), c)).into()
59}
60
61/// 引用漫画表列
62fn col_m(c: mangatb::Column) -> sea_orm::sea_query::SimpleExpr {
63    Expr::col((alias_m(), c)).into()
64}
65
66/// 百合动画分类 ID（与 workflow_filter 一致）
67const YURI_ANIMATION_CATEGORY: i32 = 4;
68
69/// 未发布话数 publishLink 条件（COALESCE 形式，可走 partial index）
70fn unpublished_publish_link_cond() -> sea_orm::sea_query::Condition {
71    sea_orm::sea_query::Condition::all().add(Expr::cust(r#"COALESCE(me."publishLink", '') = ''"#))
72}
73
74/// 话数详情列 IS NULL
75fn med_is_null(c: mangaepisodedetail::Column) -> sea_orm::sea_query::Condition {
76    sea_orm::sea_query::Condition::all().add(col_med(c).is_null())
77}
78
79/// 话数详情列 IS NOT NULL
80fn med_is_not_null(c: mangaepisodedetail::Column) -> sea_orm::sea_query::Condition {
81    sea_orm::sea_query::Condition::all().add(col_med(c).is_not_null())
82}
83
84/// 从 QueryResult 读取 DB 时间并校正为 API UTC
85fn try_dt(row: &QueryResult, col: &str) -> Option<DateTime<Utc>> {
86    let lower = col.to_ascii_lowercase();
87    if let Ok(n) = row.try_get::<NaiveDateTime>("", col) {
88        return Some(n.and_utc());
89    }
90    if let Ok(n) = row.try_get::<NaiveDateTime>("", &lower) {
91        return Some(n.and_utc());
92    }
93    row.try_get::<DateTime<Utc>>("", col)
94        .or_else(|_| row.try_get::<DateTime<Utc>>("", &lower))
95        .ok()
96}
97
98/// 读取 i64 列（兼容 PG 小写别名）
99fn get_i64(row: &QueryResult, col: &str) -> i64 {
100    let lower = col.to_ascii_lowercase();
101    row.try_get::<i64>("", col)
102        .or_else(|_| row.try_get::<i64>("", &lower))
103        .or_else(|_| row.try_get::<i32>("", col).map(i64::from))
104        .or_else(|_| row.try_get::<i32>("", &lower).map(i64::from))
105        .unwrap_or(0)
106}
107
108/// 读取 i32 列
109fn get_i32(row: &QueryResult, col: &str) -> i32 {
110    let lower = col.to_ascii_lowercase();
111    row.try_get::<i32>("", col)
112        .or_else(|_| row.try_get::<i32>("", &lower))
113        .unwrap_or(0)
114}
115
116/// 读取可选 i16 列
117fn try_get_i16(row: &QueryResult, col: &str) -> Option<i16> {
118    let lower = col.to_ascii_lowercase();
119    row.try_get::<i32>("", col)
120        .or_else(|_| row.try_get::<i32>("", &lower))
121        .ok()
122        .map(|v| v as i16)
123}
124
125/// 读取可选字符串列
126fn try_get_str(row: &QueryResult, col: &str) -> Option<String> {
127    let lower = col.to_ascii_lowercase();
128    row.try_get::<String>("", col)
129        .or_else(|_| row.try_get::<String>("", &lower))
130        .ok()
131}
132
133/// 执行 SELECT 并返回全部行
134async fn query_all(
135    db: &DbConn,
136    select: impl QueryStatementWriter,
137) -> crate::error::ApiResult<Vec<QueryResult>> {
138    let (sql, values) = select.build(PostgresQueryBuilder);
139    Ok(db
140        .query_all(Statement::from_sql_and_values(
141            DbBackend::Postgres,
142            &sql,
143            values,
144        ))
145        .await?)
146}
147
148/// 执行 SELECT 并返回首行
149async fn query_one(
150    db: &DbConn,
151    select: impl QueryStatementWriter,
152) -> crate::error::ApiResult<QueryResult> {
153    let (sql, values) = select.build(PostgresQueryBuilder);
154    Ok(db
155        .query_one(Statement::from_sql_and_values(
156            DbBackend::Postgres,
157            &sql,
158            values,
159        ))
160        .await?
161        .ok_or_else(|| crate::error::AppError::business("查询无结果"))?)
162}
163
164/// 待发布话数联表行
165#[derive(Debug, Clone)]
166pub struct PendingEpisodeRow {
167    /// 话数 ID
168    pub episode_id: i32,
169    /// 漫画 ID
170    pub manga_id: i32,
171    /// 话数标签
172    pub manga_episode: Option<String>,
173    /// 话数名
174    pub manga_episode_name: Option<String>,
175    /// 话数分类
176    pub episode_type: EpisodeType,
177    /// 图源 ID
178    pub provider_id: Option<i32>,
179    /// 翻译 ID
180    pub translator_id: Option<i32>,
181    /// 校对 ID
182    pub proofreader_id: Option<i32>,
183    /// 嵌字 ID
184    pub letterer_id: Option<i32>,
185    /// 时轴 ID
186    pub timer_id: Option<i32>,
187    /// 审稿 ID
188    pub reviewer_id: Option<i32>,
189    /// 设立时间
190    pub setup_time: Option<DateTime<Utc>>,
191    /// 更新时间
192    pub update_time: Option<DateTime<Utc>>,
193    /// 翻译文件
194    pub translator_file: Option<String>,
195    /// 校对文件
196    pub proofreader_file: Option<String>,
197    /// 时轴文件
198    pub timer_file: Option<String>,
199    /// 发布链接
200    pub publish_link: Option<String>,
201    /// 图源 OSS ID
202    pub provider_file_oss_id: Option<i32>,
203    /// 翻译稿 OSS ID
204    pub translator_file_oss_id: Option<i32>,
205    /// 校对稿 OSS ID
206    pub proofreader_file_oss_id: Option<i32>,
207    /// 嵌字稿 OSS ID
208    pub letterer_file_oss_id: Option<i32>,
209    /// 时轴 OSS ID
210    pub timer_file_oss_id: Option<i32>,
211    /// 审稿交稿时间
212    pub reviewer_update_time: Option<DateTime<Utc>>,
213    /// 漫画译名
214    pub manga_tran_name: Option<String>,
215    /// 漫画原名
216    pub manga_ori_name: Option<String>,
217    /// 漫画分类
218    pub category: Option<i16>,
219    /// 漫画状态
220    pub manga_status: Option<i16>,
221    /// 封面
222    pub image: Option<String>,
223    /// 开坑时间
224    pub manga_setup_time: Option<DateTime<Utc>>,
225    /// 漫画更新时间
226    pub manga_update_time: Option<DateTime<Utc>>,
227    /// 原著链接
228    pub link: Option<String>,
229    /// 简介
230    pub introduction: Option<String>,
231}
232
233/// 未发布话数详情行（含流程时间）
234#[derive(Debug, Clone)]
235pub struct UnpublishedEpisodeRow {
236    /// 话数 ID
237    pub episode_id: i32,
238    /// 漫画 ID
239    pub manga_id: i32,
240    /// 话数标签
241    pub manga_episode: Option<String>,
242    /// 话数名
243    pub manga_episode_name: Option<String>,
244    /// 译名
245    pub manga_tran_name: Option<String>,
246    /// 漫画分类
247    pub category: Option<i16>,
248    /// 翻译者 ID
249    pub translator_id: Option<i32>,
250    /// 校对者 ID
251    pub proofreader_id: Option<i32>,
252    /// 嵌字者 ID
253    pub letterer_id: Option<i32>,
254    /// 时轴 ID
255    pub timer_id: Option<i32>,
256    /// 审稿者 ID
257    pub reviewer_id: Option<i32>,
258    /// 翻译接稿时间
259    pub translator_setup_time: Option<DateTime<Utc>>,
260    /// 翻译交稿时间
261    pub translator_update_time: Option<DateTime<Utc>>,
262    /// 校对接稿时间
263    pub proofreader_setup_time: Option<DateTime<Utc>>,
264    /// 校对交稿时间
265    pub proofreader_update_time: Option<DateTime<Utc>>,
266    /// 嵌字接稿时间
267    pub letterer_setup_time: Option<DateTime<Utc>>,
268    /// 嵌字交稿时间
269    pub letterer_update_time: Option<DateTime<Utc>>,
270    /// 时轴接稿时间
271    pub timer_setup_time: Option<DateTime<Utc>>,
272    /// 时轴交稿时间
273    pub timer_update_time: Option<DateTime<Utc>>,
274    /// 审稿接稿时间
275    pub reviewer_setup_time: Option<DateTime<Utc>>,
276    /// 审稿交稿时间
277    pub reviewer_update_time: Option<DateTime<Utc>>,
278    /// 话数更新时间
279    pub episode_update_time: Option<DateTime<Utc>>,
280}
281
282/// 组员任务计数行
283#[derive(Debug, Clone)]
284pub struct MemberTaskCountRow {
285    /// 组员 ID
286    pub user_id: i32,
287    /// 用户名
288    pub user_name: Option<String>,
289    /// 职阶
290    pub intern: Option<i32>,
291    /// 邮箱
292    pub email: Option<String>,
293    /// 最后交稿时间
294    pub last_update_time: Option<String>,
295    /// 翻译任务数
296    pub translator_count: i64,
297    /// 校对任务数
298    pub proofreader_count: i64,
299    /// 嵌字任务数
300    pub letterer_count: i64,
301    /// 时轴任务数
302    pub timer_count: i64,
303    /// 审稿任务数
304    pub reviewer_count: i64,
305    /// 常驻数
306    pub stationed_count: i64,
307}
308
309/// 稿件监控仓储
310pub struct TaskTrackingRepository {
311    /// SeaORM 数据库连接
312    db: DbConn,
313}
314
315impl TaskTrackingRepository {
316    /// 从 `PgPool` 构造
317    pub fn new(pool: PgPool) -> Self {
318        Self {
319            db: crate::db::from_sqlx_pool(pool),
320        }
321    }
322
323    /// 从 `DatabaseConnection` 构造
324    pub fn from_db(db: DatabaseConnection) -> Self {
325        Self { db }
326    }
327
328    /// 查询组员任务计数列表
329    #[tracing::instrument(skip_all, level = "debug")]
330    pub async fn list_member_task_counts(
331        &self,
332    ) -> crate::error::ApiResult<Vec<MemberTaskCountRow>> {
333        let rows = query_all(&self.db, build_member_task_counts_select()).await?;
334        Ok(rows
335            .into_iter()
336            .map(|r| row_to_member_task_count(&r))
337            .collect())
338    }
339
340    /// 转为 MemberTaskCount 列表
341    pub fn to_member_task_counts(rows: &[MemberTaskCountRow]) -> Vec<MemberTaskCount> {
342        rows.iter()
343            .map(|r| MemberTaskCount {
344                user_name: r.user_name.clone(),
345                user_id: Some(r.user_id.to_string()),
346                intern: r.intern,
347                email: r.email.clone(),
348                last_update_time: r.last_update_time.clone(),
349                stationed_count: r.stationed_count as i32,
350                translator_count: r.translator_count as i32,
351                proofreader_count: r.proofreader_count as i32,
352                letterer_count: r.letterer_count as i32,
353                timer_count: r.timer_count as i32,
354                reviewer_count: r.reviewer_count as i32,
355            })
356            .collect()
357    }
358
359    /// 查询全部未发布话数及详情（UNION 分桶，可走工序 partial index）
360    #[tracing::instrument(skip_all, level = "debug")]
361    pub async fn list_unpublished_episodes(
362        &self,
363    ) -> crate::error::ApiResult<Vec<UnpublishedEpisodeRow>> {
364        let rows = query_all(&self.db, build_unpublished_episodes_select()).await?;
365        Ok(rows.into_iter().map(|r| row_to_unpublished(&r)).collect())
366    }
367
368    /// 查询未发布话数并直接构建任务看板响应（避免二次映射）
369    #[tracing::instrument(skip_all, level = "debug")]
370    pub async fn list_episode_tasks_response(
371        &self,
372    ) -> crate::error::ApiResult<TaskTrackingResponse> {
373        let rows = query_all(&self.db, build_unpublished_episodes_select()).await?;
374        Ok(build_task_response_from_union(&rows))
375    }
376
377    /// 按工作流规则构建 TaskTrackingResponse（对齐 Java buildWorkflowChain）
378    pub fn build_task_response(episodes: &[UnpublishedEpisodeRow]) -> TaskTrackingResponse {
379        let mut resp = TaskTrackingResponse::empty();
380        for ep in episodes {
381            if ep.reviewer_update_time.is_some() {
382                push_task(
383                    &mut resp,
384                    ep,
385                    "PUBLISHER",
386                    None,
387                    ep.reviewer_setup_time,
388                    ep.reviewer_update_time,
389                    |r, t| r.publish_task_list.push(t),
390                );
391                continue;
392            }
393            if ep.letterer_update_time.is_some() || ep.timer_update_time.is_some() {
394                let prev = ep.letterer_update_time.or(ep.timer_update_time);
395                push_task(
396                    &mut resp,
397                    ep,
398                    "REVIEWER",
399                    ep.reviewer_id,
400                    ep.reviewer_setup_time,
401                    prev,
402                    |r, t| r.reviewer_task_list.push(t),
403                );
404                continue;
405            }
406            if ep.proofreader_update_time.is_some() {
407                let is_animation = ep.category == Some(4);
408                if is_animation {
409                    push_task(
410                        &mut resp,
411                        ep,
412                        "TIMER",
413                        ep.timer_id,
414                        ep.timer_setup_time,
415                        ep.proofreader_update_time,
416                        |r, t| r.timer_task_list.push(t),
417                    );
418                } else {
419                    push_task(
420                        &mut resp,
421                        ep,
422                        "LETTERER",
423                        ep.letterer_id,
424                        ep.letterer_setup_time,
425                        ep.proofreader_update_time,
426                        |r, t| r.letterer_task_list.push(t),
427                    );
428                }
429                continue;
430            }
431            if ep.translator_update_time.is_some() {
432                push_task(
433                    &mut resp,
434                    ep,
435                    "PROOFREADER",
436                    ep.proofreader_id,
437                    ep.proofreader_setup_time,
438                    ep.translator_update_time,
439                    |r, t| r.proofreader_task_list.push(t),
440                );
441                continue;
442            }
443            push_task(
444                &mut resp,
445                ep,
446                "TRANSLATOR",
447                ep.translator_id,
448                ep.translator_setup_time,
449                ep.episode_update_time,
450                |r, t| r.translator_task_list.push(t),
451            );
452        }
453        resp
454    }
455
456    /// 统计嵌字完成且未发布的待处理话数
457    #[tracing::instrument(skip_all, level = "debug")]
458    pub async fn count_pending_publish_episodes(
459        &self,
460        manga_tran_name: Option<&str>,
461    ) -> crate::error::ApiResult<i64> {
462        let select = Query::select()
463            .expr_as(Expr::cust("COUNT(*)"), Alias::new("cnt"))
464            .from_subquery(
465                pending_publish_base_select(manga_tran_name),
466                Alias::new("pending"),
467            )
468            .to_owned();
469        let row = query_one(&self.db, select).await?;
470        Ok(get_i64(&row, "cnt"))
471    }
472
473    /// 查询嵌字完成且未发布的待处理话数（SQL 分页）
474    #[tracing::instrument(skip_all, level = "debug")]
475    pub async fn list_pending_publish_episodes(
476        &self,
477        manga_tran_name: Option<&str>,
478        page: i32,
479        page_size: i32,
480    ) -> crate::error::ApiResult<Vec<PendingEpisodeRow>> {
481        let page = page.max(1);
482        let page_size = page_size.max(1);
483        let offset = ((page - 1) * page_size) as u64;
484        let mut select = pending_publish_base_select(manga_tran_name);
485        select
486            .order_by((alias_m(), mangatb::Column::MangaTranName), Order::Asc)
487            .order_by((alias_me(), mangaepisodetb::Column::MangaId), Order::Asc)
488            .order_by_expr_with_nulls(episode_primary_numeric(), Order::Asc, NullOrdering::Last)
489            .limit(page_size as u64)
490            .offset(offset);
491        let rows = query_all(&self.db, select).await?;
492        Ok(rows
493            .into_iter()
494            .map(|r| row_to_pending_episode(&r))
495            .collect())
496    }
497
498    /// 各漫画最新已发布正篇标签 + 下一个待发布正篇 ID（聚合 SQL，避免拉全量话数）
499    #[tracing::instrument(skip_all, level = "debug")]
500    pub async fn map_publish_episode_context(
501        &self,
502        manga_ids: &[i32],
503    ) -> crate::error::ApiResult<(HashMap<i32, String>, HashMap<i32, i32>)> {
504        if manga_ids.is_empty() {
505            return Ok((HashMap::new(), HashMap::new()));
506        }
507
508        let ep_num = episode_primary_numeric();
509        let latest_select = Query::select()
510            .expr(Expr::cust(r#"DISTINCT ON (me."mangaId") me."mangaId""#))
511            .expr_as(
512                col_me(mangaepisodetb::Column::MangaEpisode),
513                Alias::new("mangaEpisode"),
514            )
515            .from_as(mangaepisodetb::Entity, alias_me())
516            .cond_where(col_me(mangaepisodetb::Column::MangaId).is_in(manga_ids.to_vec()))
517            .cond_where(col_me(mangaepisodetb::Column::EpisodeType).eq(EpisodeType::Main.as_str()))
518            .cond_where(col_me(mangaepisodetb::Column::PublishLink).is_not_null())
519            .cond_where(col_me(mangaepisodetb::Column::PublishLink).ne(""))
520            .order_by((alias_me(), mangaepisodetb::Column::MangaId), Order::Asc)
521            .order_by_expr_with_nulls(ep_num.clone(), Order::Desc, NullOrdering::Last)
522            .to_owned();
523
524        let latest_rows = query_all(&self.db, latest_select).await?;
525        let mut latest_label: HashMap<i32, String> = HashMap::new();
526        for r in latest_rows {
527            let manga_id = get_i32(&r, "mangaId");
528            let label = try_get_str(&r, "mangaEpisode").unwrap_or_default();
529            latest_label.insert(manga_id, label);
530        }
531
532        let latest_pub = Query::select()
533            .expr(Expr::cust(r#"DISTINCT ON (me."mangaId") me."mangaId""#))
534            .expr_as(ep_num.clone(), Alias::new("max_ep"))
535            .from_as(mangaepisodetb::Entity, alias_me())
536            .cond_where(col_me(mangaepisodetb::Column::MangaId).is_in(manga_ids.to_vec()))
537            .cond_where(col_me(mangaepisodetb::Column::EpisodeType).eq(EpisodeType::Main.as_str()))
538            .cond_where(col_me(mangaepisodetb::Column::PublishLink).is_not_null())
539            .cond_where(col_me(mangaepisodetb::Column::PublishLink).ne(""))
540            .order_by((alias_me(), mangaepisodetb::Column::MangaId), Order::Asc)
541            .order_by_expr_with_nulls(ep_num.clone(), Order::Desc, NullOrdering::Last)
542            .to_owned();
543
544        let candidates = Query::select()
545            .expr_as(
546                col_me(mangaepisodetb::Column::MangaId),
547                Alias::new("mangaId"),
548            )
549            .expr_as(col_me(mangaepisodetb::Column::Id), Alias::new("episodeId"))
550            .expr_as(ep_num.clone(), Alias::new("ep_num"))
551            .from_as(mangaepisodetb::Entity, alias_me())
552            .cond_where(col_me(mangaepisodetb::Column::MangaId).is_in(manga_ids.to_vec()))
553            .cond_where(col_me(mangaepisodetb::Column::EpisodeType).eq(EpisodeType::Main.as_str()))
554            .cond_where(unpublished_publish_link_cond())
555            .to_owned();
556
557        let latest_pub_cte = CommonTableExpression::new()
558            .query(latest_pub)
559            .table_name(Alias::new("latest_pub"))
560            .to_owned();
561        let candidates_cte = CommonTableExpression::new()
562            .query(candidates)
563            .table_name(Alias::new("candidates"))
564            .to_owned();
565
566        let next_select = Query::select()
567            .expr(Expr::cust(r#"DISTINCT ON (c."mangaId") c."mangaId""#))
568            .expr_as(
569                Expr::col((Alias::new("c"), Alias::new("episodeId"))),
570                Alias::new("episodeId"),
571            )
572            .from_as(Alias::new("candidates"), Alias::new("c"))
573            .join_as(
574                JoinType::LeftJoin,
575                Alias::new("latest_pub"),
576                Alias::new("lp"),
577                Expr::col((Alias::new("lp"), Alias::new("mangaId")))
578                    .equals((Alias::new("c"), Alias::new("mangaId"))),
579            )
580            .cond_where(
581                Expr::col((Alias::new("c"), Alias::new("ep_num")))
582                    .gt(Expr::cust(r#"COALESCE(lp."max_ep", -1)"#)),
583            )
584            .order_by((Alias::new("c"), Alias::new("mangaId")), Order::Asc)
585            .order_by_expr_with_nulls(
586                Expr::col((Alias::new("c"), Alias::new("ep_num"))).into(),
587                Order::Asc,
588                NullOrdering::Last,
589            )
590            .to_owned()
591            .with(
592                WithClause::new()
593                    .cte(latest_pub_cte)
594                    .cte(candidates_cte)
595                    .to_owned(),
596            );
597
598        let next_rows = query_all(&self.db, next_select).await?;
599        let next_publish: HashMap<i32, i32> = next_rows
600            .into_iter()
601            .map(|r| (get_i32(&r, "mangaId"), get_i32(&r, "episodeId")))
602            .collect();
603
604        Ok((latest_label, next_publish))
605    }
606
607    /// 工序汇总筛选：分页列表（含 total，单次 SQL）
608    #[tracing::instrument(skip_all, level = "debug")]
609    pub async fn page_workflow_episodes(
610        &self,
611        q: &WorkflowEpisodeQuery,
612    ) -> crate::error::ApiResult<(i64, Vec<WorkflowEpisodeItem>)> {
613        let select =
614            build_workflow_page_select(q).map_err(|msg| crate::error::AppError::business(msg))?;
615        let rows = query_all(&self.db, select).await?;
616        Ok(parse_workflow_page_rows(&rows))
617    }
618
619    /// 工序汇总筛选：计数
620    #[tracing::instrument(skip_all, level = "debug")]
621    pub async fn count_workflow_episodes(
622        &self,
623        q: &WorkflowEpisodeQuery,
624    ) -> crate::error::ApiResult<i64> {
625        let (filtered, _) = build_workflow_filtered_select(q)
626            .map_err(|msg| crate::error::AppError::business(msg))?;
627        let select = Query::select()
628            .expr_as(Expr::cust("COUNT(*)"), Alias::new("cnt"))
629            .from_subquery(filtered, Alias::new("counted"))
630            .to_owned();
631        let row = query_one(&self.db, select).await?;
632        Ok(get_i64(&row, "cnt"))
633    }
634
635    /// 工序汇总筛选：分页列表
636    #[tracing::instrument(skip_all, level = "debug")]
637    pub async fn list_workflow_episodes(
638        &self,
639        q: &WorkflowEpisodeQuery,
640    ) -> crate::error::ApiResult<Vec<WorkflowEpisodeItem>> {
641        let (filtered, _) = build_workflow_filtered_select(q)
642            .map_err(|msg| crate::error::AppError::business(msg))?;
643        let page = q.page.max(1);
644        let page_size = q.page_size.max(1);
645        let offset = ((page - 1) * page_size) as u64;
646        let mut select = Query::select()
647            .column(Asterisk)
648            .from_subquery(filtered, workflow_filter::alias_wf())
649            .to_owned();
650        apply_workflow_order(&mut select, q.sort_prop.as_deref(), q.sort_order.as_deref());
651        select.limit(page_size as u64).offset(offset);
652        let rows = query_all(&self.db, select).await?;
653        Ok(rows.iter().map(row_to_workflow_episode).collect())
654    }
655
656    /// PendingEpisodeRow 转 Mangatb
657    pub fn row_mangatb(row: &PendingEpisodeRow) -> Mangatb {
658        Mangatb {
659            id: Some(row.manga_id),
660            manga_tran_name: row.manga_tran_name.clone(),
661            manga_ori_name: row.manga_ori_name.clone(),
662            image: row.image.clone(),
663            category: row.category.map(|v| v as i32),
664            manga_status: row.manga_status.map(|v| v as i32),
665            setup_time: row.manga_setup_time,
666            update_time: row.manga_update_time,
667            link: row.link.clone(),
668            introduction: row.introduction.clone(),
669        }
670    }
671}
672
673/// 话数表 `me."mangaEpisode"` 安全 numeric 排序键
674fn episode_primary_numeric() -> sea_orm::sea_query::SimpleExpr {
675    episode_sort::episode_label_sort_expr(r#"me."mangaEpisode""#)
676}
677
678/// 岗位待办计数 UNION 分支
679fn role_count_branch(
680    member_col: mangaepisodetb::Column,
681    role: &str,
682    extra: sea_orm::sea_query::Condition,
683) -> SelectStatement {
684    Query::select()
685        .expr_as(col_me(member_col), Alias::new("memberId"))
686        .expr_as(Expr::cust("COUNT(*)::bigint"), Alias::new("cnt"))
687        .expr_as(Expr::val(role), Alias::new("role"))
688        .from_as(mangaepisodetb::Entity, alias_me())
689        .join_as(
690            JoinType::InnerJoin,
691            mangaepisodedetail::Entity,
692            alias_med(),
693            Expr::col((alias_med(), mangaepisodedetail::Column::EpisodeId))
694                .equals((alias_me(), mangaepisodetb::Column::Id)),
695        )
696        .cond_where(extra)
697        .cond_where(unpublished_publish_link_cond())
698        .cond_where(col_me(member_col).is_not_null())
699        .group_by_col((alias_me(), member_col))
700        .to_owned()
701}
702
703/// 组员任务计数主查询（CTE + 汇总，parity 测试复用）
704pub fn build_member_task_counts_select() -> sea_orm::sea_query::WithQuery {
705    let mut role_union = role_count_branch(
706        mangaepisodetb::Column::TranslatorId,
707        "translator",
708        sea_orm::sea_query::Condition::all()
709            .add(col_med(mangaepisodedetail::Column::TranslatorUpdateTime).is_null()),
710    );
711    let branches = [
712        (
713            mangaepisodetb::Column::ProofreaderId,
714            "proofreader",
715            sea_orm::sea_query::Condition::all()
716                .add(col_med(mangaepisodedetail::Column::TranslatorUpdateTime).is_not_null())
717                .add(col_med(mangaepisodedetail::Column::ProofreaderUpdateTime).is_null()),
718        ),
719        (
720            mangaepisodetb::Column::LettererId,
721            "letterer",
722            sea_orm::sea_query::Condition::all()
723                .add(col_med(mangaepisodedetail::Column::ProofreaderUpdateTime).is_not_null())
724                .add(col_med(mangaepisodedetail::Column::LettererUpdateTime).is_null()),
725        ),
726        (
727            mangaepisodetb::Column::TimerId,
728            "timer",
729            sea_orm::sea_query::Condition::all()
730                .add(col_med(mangaepisodedetail::Column::ProofreaderUpdateTime).is_not_null())
731                .add(col_med(mangaepisodedetail::Column::TimerUpdateTime).is_null()),
732        ),
733        (
734            mangaepisodetb::Column::ReviewerId,
735            "reviewer",
736            sea_orm::sea_query::Condition::all()
737                .add(col_med(mangaepisodedetail::Column::LettererUpdateTime).is_not_null())
738                .add(col_med(mangaepisodedetail::Column::ReviewerUpdateTime).is_null()),
739        ),
740    ];
741    for (col, role, cond) in branches {
742        role_union = role_union
743            .union(UnionType::All, role_count_branch(col, role, cond))
744            .to_owned();
745    }
746
747    let role_counts = CommonTableExpression::new()
748        .query(role_union)
749        .table_name(Alias::new("role_counts"))
750        .to_owned();
751
752    let pivoted = CommonTableExpression::new()
753        .query(
754            Query::select()
755                .column(Alias::new("memberId"))
756                .expr_as(
757                    Expr::cust("COALESCE(SUM(cnt) FILTER (WHERE role = 'translator'), 0)::bigint"),
758                    Alias::new("translatorCount"),
759                )
760                .expr_as(
761                    Expr::cust("COALESCE(SUM(cnt) FILTER (WHERE role = 'proofreader'), 0)::bigint"),
762                    Alias::new("proofreaderCount"),
763                )
764                .expr_as(
765                    Expr::cust("COALESCE(SUM(cnt) FILTER (WHERE role = 'letterer'), 0)::bigint"),
766                    Alias::new("lettererCount"),
767                )
768                .expr_as(
769                    Expr::cust("COALESCE(SUM(cnt) FILTER (WHERE role = 'timer'), 0)::bigint"),
770                    Alias::new("timerCount"),
771                )
772                .expr_as(
773                    Expr::cust("COALESCE(SUM(cnt) FILTER (WHERE role = 'reviewer'), 0)::bigint"),
774                    Alias::new("reviewerCount"),
775                )
776                .from(Alias::new("role_counts"))
777                .group_by_col(Alias::new("memberId"))
778                .to_owned(),
779        )
780        .table_name(Alias::new("pivoted"))
781        .to_owned();
782
783    let stationed = CommonTableExpression::new()
784        .query(
785            Query::select()
786                .expr_as(
787                    Expr::col((Alias::new("ms"), mangastationed::Column::MemberId)),
788                    Alias::new("memberId"),
789                )
790                .expr_as(Expr::cust("COUNT(*)::bigint"), Alias::new("stationedCount"))
791                .from_as(mangastationed::Entity, Alias::new("ms"))
792                .cond_where(Expr::col((Alias::new("ms"), mangastationed::Column::Status)).eq(1))
793                .group_by_col((Alias::new("ms"), mangastationed::Column::MemberId))
794                .to_owned(),
795        )
796        .table_name(Alias::new("stationed"))
797        .to_owned();
798
799    Query::select()
800        .expr_as(
801            Expr::col((alias_member(), membertb::Column::Id)),
802            Alias::new("userId"),
803        )
804        .expr_as(
805            Expr::col((alias_member(), membertb::Column::Username)),
806            Alias::new("userName"),
807        )
808        .expr_as(
809            Expr::col((alias_member(), membertb::Column::LastSubmitTime)),
810            Alias::new("lastUpdateTime"),
811        )
812        .expr_as(
813            Expr::col((alias_member(), membertb::Column::Intern)),
814            Alias::new("intern"),
815        )
816        .expr_as(
817            Expr::col((alias_member(), membertb::Column::Email)),
818            Alias::new("email"),
819        )
820        .expr_as(
821            Expr::cust(r#"COALESCE(sc."stationedCount", 0)"#),
822            Alias::new("stationedCount"),
823        )
824        .expr_as(
825            Expr::cust(r#"COALESCE(p."translatorCount", 0)"#),
826            Alias::new("translatorCount"),
827        )
828        .expr_as(
829            Expr::cust(r#"COALESCE(p."proofreaderCount", 0)"#),
830            Alias::new("proofreaderCount"),
831        )
832        .expr_as(
833            Expr::cust(r#"COALESCE(p."lettererCount", 0)"#),
834            Alias::new("lettererCount"),
835        )
836        .expr_as(
837            Expr::cust(r#"COALESCE(p."timerCount", 0)"#),
838            Alias::new("timerCount"),
839        )
840        .expr_as(
841            Expr::cust(r#"COALESCE(p."reviewerCount", 0)"#),
842            Alias::new("reviewerCount"),
843        )
844        .from_as(membertb::Entity, alias_member())
845        .join_as(
846            JoinType::LeftJoin,
847            Alias::new("pivoted"),
848            Alias::new("p"),
849            Expr::col((Alias::new("p"), Alias::new("memberId")))
850                .equals((alias_member(), membertb::Column::Id)),
851        )
852        .join_as(
853            JoinType::LeftJoin,
854            Alias::new("stationed"),
855            Alias::new("sc"),
856            Expr::col((Alias::new("sc"), Alias::new("memberId")))
857                .equals((alias_member(), membertb::Column::Id)),
858        )
859        .cond_where(Expr::cust(
860            r#"(COALESCE(p."translatorCount", 0) + COALESCE(p."lettererCount", 0) + COALESCE(p."timerCount", 0) + COALESCE(p."reviewerCount", 0)) > 0"#,
861        ))
862        .order_by((alias_member(), membertb::Column::Id), Order::Desc)
863        .to_owned()
864        .with(
865            WithClause::new()
866                .cte(role_counts)
867                .cte(pivoted)
868                .cte(stationed)
869                .to_owned(),
870        )
871}
872
873/// 未发布话数公共 SELECT 列
874fn append_unpublished_episode_columns(select: &mut sea_orm::sea_query::SelectStatement) {
875    select
876        .expr_as(col_me(mangaepisodetb::Column::Id), Alias::new("episodeId"))
877        .expr_as(
878            col_me(mangaepisodetb::Column::MangaId),
879            Alias::new("mangaId"),
880        )
881        .expr_as(
882            col_me(mangaepisodetb::Column::MangaEpisode),
883            Alias::new("mangaEpisode"),
884        )
885        .expr_as(
886            col_me(mangaepisodetb::Column::MangaEpisodeName),
887            Alias::new("mangaEpisodeName"),
888        )
889        .expr_as(
890            col_me(mangaepisodetb::Column::TranslatorId),
891            Alias::new("translatorId"),
892        )
893        .expr_as(
894            col_me(mangaepisodetb::Column::ProofreaderId),
895            Alias::new("proofreaderId"),
896        )
897        .expr_as(
898            col_me(mangaepisodetb::Column::LettererId),
899            Alias::new("lettererId"),
900        )
901        .expr_as(
902            col_me(mangaepisodetb::Column::TimerId),
903            Alias::new("timerId"),
904        )
905        .expr_as(
906            col_me(mangaepisodetb::Column::ReviewerId),
907            Alias::new("reviewerId"),
908        )
909        .expr_as(
910            col_me(mangaepisodetb::Column::UpdateTime),
911            Alias::new("episodeUpdateTime"),
912        )
913        .expr_as(
914            col_m(mangatb::Column::MangaTranName),
915            Alias::new("mangaTranName"),
916        )
917        .expr_as(col_m(mangatb::Column::Category), Alias::new("category"))
918        .expr_as(
919            col_med(mangaepisodedetail::Column::TranslatorSetupTime),
920            Alias::new("translatorSetupTime"),
921        )
922        .expr_as(
923            col_med(mangaepisodedetail::Column::TranslatorUpdateTime),
924            Alias::new("translatorUpdateTime"),
925        )
926        .expr_as(
927            col_med(mangaepisodedetail::Column::ProofreaderSetupTime),
928            Alias::new("proofreaderSetupTime"),
929        )
930        .expr_as(
931            col_med(mangaepisodedetail::Column::ProofreaderUpdateTime),
932            Alias::new("proofreaderUpdateTime"),
933        )
934        .expr_as(
935            col_med(mangaepisodedetail::Column::LettererSetupTime),
936            Alias::new("lettererSetupTime"),
937        )
938        .expr_as(
939            col_med(mangaepisodedetail::Column::LettererUpdateTime),
940            Alias::new("lettererUpdateTime"),
941        )
942        .expr_as(
943            col_med(mangaepisodedetail::Column::TimerSetupTime),
944            Alias::new("timerSetupTime"),
945        )
946        .expr_as(
947            col_med(mangaepisodedetail::Column::TimerUpdateTime),
948            Alias::new("timerUpdateTime"),
949        )
950        .expr_as(
951            col_med(mangaepisodedetail::Column::ReviewerSetupTime),
952            Alias::new("reviewerSetupTime"),
953        )
954        .expr_as(
955            col_med(mangaepisodedetail::Column::ReviewerUpdateTime),
956            Alias::new("reviewerUpdateTime"),
957        );
958}
959
960/// 未发布话数单分桶 UNION 分支
961fn unpublished_episode_branch(
962    extra_where: sea_orm::sea_query::Condition,
963    post: &str,
964    member_id_expr: sea_orm::sea_query::SimpleExpr,
965    setup_time_expr: sea_orm::sea_query::SimpleExpr,
966    previous_update_expr: sea_orm::sea_query::SimpleExpr,
967) -> SelectStatement {
968    let mut q = Query::select();
969    append_unpublished_episode_columns(&mut q);
970    q.expr_as(Expr::val(post), Alias::new("bucketPost"))
971        .expr_as(member_id_expr, Alias::new("taskMemberId"))
972        .expr_as(setup_time_expr, Alias::new("taskSetupTime"))
973        .expr_as(previous_update_expr, Alias::new("taskPreviousUpdateTime"))
974        .from_as(mangaepisodetb::Entity, alias_me())
975        .join_as(
976            JoinType::LeftJoin,
977            mangaepisodedetail::Entity,
978            alias_med(),
979            Expr::col((alias_med(), mangaepisodedetail::Column::EpisodeId))
980                .equals((alias_me(), mangaepisodetb::Column::Id)),
981        )
982        .join_as(
983            JoinType::LeftJoin,
984            mangatb::Entity,
985            alias_m(),
986            Expr::col((alias_m(), mangatb::Column::Id))
987                .equals((alias_me(), mangaepisodetb::Column::MangaId)),
988        )
989        .cond_where(unpublished_publish_link_cond())
990        .cond_where(extra_where)
991        .to_owned()
992}
993
994/// 未发布话数列表 SELECT（6 路 UNION ALL，对齐 buildWorkflowChain 分桶顺序）
995fn build_unpublished_episodes_select() -> SelectStatement {
996    let mut publisher = unpublished_episode_branch(
997        med_is_not_null(mangaepisodedetail::Column::ReviewerUpdateTime),
998        "PUBLISHER",
999        Expr::val(Option::<i32>::None).into(),
1000        col_med(mangaepisodedetail::Column::ReviewerSetupTime).into(),
1001        col_med(mangaepisodedetail::Column::ReviewerUpdateTime).into(),
1002    );
1003    let reviewer = unpublished_episode_branch(
1004        sea_orm::sea_query::Condition::all()
1005            .add(
1006                sea_orm::sea_query::Condition::any()
1007                    .add(med_is_not_null(
1008                        mangaepisodedetail::Column::LettererUpdateTime,
1009                    ))
1010                    .add(med_is_not_null(mangaepisodedetail::Column::TimerUpdateTime)),
1011            )
1012            .add(med_is_null(mangaepisodedetail::Column::ReviewerUpdateTime)),
1013        "REVIEWER",
1014        col_me(mangaepisodetb::Column::ReviewerId).into(),
1015        col_med(mangaepisodedetail::Column::ReviewerSetupTime).into(),
1016        Expr::cust(r#"COALESCE(med."lettererUpdateTime", med."timerUpdateTime")"#),
1017    );
1018    let letterer = unpublished_episode_branch(
1019        sea_orm::sea_query::Condition::all()
1020            .add(med_is_not_null(
1021                mangaepisodedetail::Column::ProofreaderUpdateTime,
1022            ))
1023            .add(med_is_null(mangaepisodedetail::Column::LettererUpdateTime))
1024            .add(med_is_null(mangaepisodedetail::Column::TimerUpdateTime))
1025            .add(med_is_null(mangaepisodedetail::Column::ReviewerUpdateTime))
1026            .add(
1027                sea_orm::sea_query::Condition::any()
1028                    .add(col_m(mangatb::Column::Category).is_null())
1029                    .add(col_m(mangatb::Column::Category).ne(YURI_ANIMATION_CATEGORY)),
1030            ),
1031        "LETTERER",
1032        col_me(mangaepisodetb::Column::LettererId).into(),
1033        col_med(mangaepisodedetail::Column::LettererSetupTime).into(),
1034        col_med(mangaepisodedetail::Column::ProofreaderUpdateTime).into(),
1035    );
1036    let timer = unpublished_episode_branch(
1037        sea_orm::sea_query::Condition::all()
1038            .add(med_is_not_null(
1039                mangaepisodedetail::Column::ProofreaderUpdateTime,
1040            ))
1041            .add(med_is_null(mangaepisodedetail::Column::LettererUpdateTime))
1042            .add(med_is_null(mangaepisodedetail::Column::TimerUpdateTime))
1043            .add(med_is_null(mangaepisodedetail::Column::ReviewerUpdateTime))
1044            .add(col_m(mangatb::Column::Category).eq(YURI_ANIMATION_CATEGORY)),
1045        "TIMER",
1046        col_me(mangaepisodetb::Column::TimerId).into(),
1047        col_med(mangaepisodedetail::Column::TimerSetupTime).into(),
1048        col_med(mangaepisodedetail::Column::ProofreaderUpdateTime).into(),
1049    );
1050    let proofreader = unpublished_episode_branch(
1051        sea_orm::sea_query::Condition::all()
1052            .add(med_is_not_null(
1053                mangaepisodedetail::Column::TranslatorUpdateTime,
1054            ))
1055            .add(med_is_null(
1056                mangaepisodedetail::Column::ProofreaderUpdateTime,
1057            ))
1058            .add(med_is_null(mangaepisodedetail::Column::LettererUpdateTime))
1059            .add(med_is_null(mangaepisodedetail::Column::TimerUpdateTime))
1060            .add(med_is_null(mangaepisodedetail::Column::ReviewerUpdateTime)),
1061        "PROOFREADER",
1062        col_me(mangaepisodetb::Column::ProofreaderId).into(),
1063        col_med(mangaepisodedetail::Column::ProofreaderSetupTime).into(),
1064        col_med(mangaepisodedetail::Column::TranslatorUpdateTime).into(),
1065    );
1066    let translator = unpublished_episode_branch(
1067        sea_orm::sea_query::Condition::all()
1068            .add(med_is_null(
1069                mangaepisodedetail::Column::TranslatorUpdateTime,
1070            ))
1071            .add(med_is_null(
1072                mangaepisodedetail::Column::ProofreaderUpdateTime,
1073            ))
1074            .add(med_is_null(mangaepisodedetail::Column::LettererUpdateTime))
1075            .add(med_is_null(mangaepisodedetail::Column::TimerUpdateTime))
1076            .add(med_is_null(mangaepisodedetail::Column::ReviewerUpdateTime)),
1077        "TRANSLATOR",
1078        col_me(mangaepisodetb::Column::TranslatorId).into(),
1079        col_med(mangaepisodedetail::Column::TranslatorSetupTime).into(),
1080        col_me(mangaepisodetb::Column::UpdateTime).into(),
1081    );
1082    publisher
1083        .union(UnionType::All, reviewer)
1084        .union(UnionType::All, letterer)
1085        .union(UnionType::All, timer)
1086        .union(UnionType::All, proofreader)
1087        .union(UnionType::All, translator)
1088        .to_owned()
1089}
1090
1091/// 待发布话数基础 SELECT（含联表与筛选）
1092fn pending_publish_base_select(manga_tran_name: Option<&str>) -> SelectStatement {
1093    let mut q = Query::select()
1094        .expr_as(col_me(mangaepisodetb::Column::Id), Alias::new("episodeId"))
1095        .expr_as(
1096            col_me(mangaepisodetb::Column::MangaId),
1097            Alias::new("mangaId"),
1098        )
1099        .expr_as(
1100            col_me(mangaepisodetb::Column::MangaEpisode),
1101            Alias::new("mangaEpisode"),
1102        )
1103        .expr_as(
1104            col_me(mangaepisodetb::Column::MangaEpisodeName),
1105            Alias::new("mangaEpisodeName"),
1106        )
1107        .expr_as(
1108            col_me(mangaepisodetb::Column::EpisodeType),
1109            Alias::new("episodeType"),
1110        )
1111        .expr_as(
1112            col_me(mangaepisodetb::Column::ProviderId),
1113            Alias::new("providerId"),
1114        )
1115        .expr_as(
1116            col_me(mangaepisodetb::Column::TranslatorId),
1117            Alias::new("translatorId"),
1118        )
1119        .expr_as(
1120            col_me(mangaepisodetb::Column::ProofreaderId),
1121            Alias::new("proofreaderId"),
1122        )
1123        .expr_as(
1124            col_me(mangaepisodetb::Column::LettererId),
1125            Alias::new("lettererId"),
1126        )
1127        .expr_as(
1128            col_me(mangaepisodetb::Column::TimerId),
1129            Alias::new("timerId"),
1130        )
1131        .expr_as(
1132            col_me(mangaepisodetb::Column::ReviewerId),
1133            Alias::new("reviewerId"),
1134        )
1135        .expr_as(
1136            col_me(mangaepisodetb::Column::SetupTime),
1137            Alias::new("setupTime"),
1138        )
1139        .expr_as(
1140            col_me(mangaepisodetb::Column::UpdateTime),
1141            Alias::new("updateTime"),
1142        )
1143        .expr_as(
1144            col_me(mangaepisodetb::Column::TranslatorFile),
1145            Alias::new("translatorFile"),
1146        )
1147        .expr_as(
1148            col_me(mangaepisodetb::Column::ProofreaderFile),
1149            Alias::new("proofreaderFile"),
1150        )
1151        .expr_as(
1152            col_me(mangaepisodetb::Column::TimerFile),
1153            Alias::new("timerFile"),
1154        )
1155        .expr_as(
1156            col_me(mangaepisodetb::Column::PublishLink),
1157            Alias::new("publishLink"),
1158        )
1159        .expr_as(
1160            col_me(mangaepisodetb::Column::ProviderFileOssId),
1161            Alias::new("providerFileOssId"),
1162        )
1163        .expr_as(
1164            col_me(mangaepisodetb::Column::TranslatorFileOssId),
1165            Alias::new("translatorFileOssId"),
1166        )
1167        .expr_as(
1168            col_me(mangaepisodetb::Column::ProofreaderFileOssId),
1169            Alias::new("proofreaderFileOssId"),
1170        )
1171        .expr_as(
1172            col_me(mangaepisodetb::Column::LettererFileOssId),
1173            Alias::new("lettererFileOssId"),
1174        )
1175        .expr_as(
1176            col_me(mangaepisodetb::Column::TimerFileOssId),
1177            Alias::new("timerFileOssId"),
1178        )
1179        .expr_as(
1180            col_med(mangaepisodedetail::Column::ReviewerUpdateTime),
1181            Alias::new("reviewerUpdateTime"),
1182        )
1183        .expr_as(col_m(mangatb::Column::Id), Alias::new("mangaDbId"))
1184        .expr_as(
1185            col_m(mangatb::Column::MangaTranName),
1186            Alias::new("mangaTranName"),
1187        )
1188        .expr_as(
1189            col_m(mangatb::Column::MangaOriName),
1190            Alias::new("mangaOriName"),
1191        )
1192        .expr_as(col_m(mangatb::Column::Category), Alias::new("category"))
1193        .expr_as(
1194            col_m(mangatb::Column::MangaStatus),
1195            Alias::new("mangaStatus"),
1196        )
1197        .expr_as(col_m(mangatb::Column::Image), Alias::new("image"))
1198        .expr_as(
1199            col_m(mangatb::Column::SetupTime),
1200            Alias::new("mangaSetupTime"),
1201        )
1202        .expr_as(
1203            col_m(mangatb::Column::UpdateTime),
1204            Alias::new("mangaUpdateTime"),
1205        )
1206        .expr_as(col_m(mangatb::Column::Link), Alias::new("link"))
1207        .expr_as(
1208            col_m(mangatb::Column::Introduction),
1209            Alias::new("introduction"),
1210        )
1211        .from_as(mangaepisodetb::Entity, alias_me())
1212        .join_as(
1213            JoinType::LeftJoin,
1214            mangaepisodedetail::Entity,
1215            alias_med(),
1216            Expr::col((alias_med(), mangaepisodedetail::Column::EpisodeId))
1217                .equals((alias_me(), mangaepisodetb::Column::Id)),
1218        )
1219        .join_as(
1220            JoinType::InnerJoin,
1221            mangatb::Entity,
1222            alias_m(),
1223            Expr::col((alias_m(), mangatb::Column::Id))
1224                .equals((alias_me(), mangaepisodetb::Column::MangaId)),
1225        )
1226        .cond_where(col_me(mangaepisodetb::Column::LettererFileOssId).is_not_null())
1227        .cond_where(unpublished_publish_link_cond())
1228        .to_owned();
1229    if let Some(name) = manga_tran_name {
1230        q = q
1231            .cond_where(col_m(mangatb::Column::MangaTranName).like(format!("%{name}%")))
1232            .to_owned();
1233    }
1234    q
1235}
1236
1237/// 解析工序汇总分页查询结果（空页仍保留 total）
1238fn parse_workflow_page_rows(rows: &[QueryResult]) -> (i64, Vec<WorkflowEpisodeItem>) {
1239    if rows.is_empty() {
1240        return (0, Vec::new());
1241    }
1242    let total = get_i64(rows.first().expect("non-empty"), "_total");
1243    let items = rows
1244        .iter()
1245        .filter(|r| {
1246            r.try_get::<i32>("", "episodeId")
1247                .ok()
1248                .is_some_and(|id| id > 0)
1249        })
1250        .map(row_to_workflow_episode)
1251        .collect();
1252    (total, items)
1253}
1254
1255/// 行映射为工序汇总项
1256fn row_to_workflow_episode(row: &QueryResult) -> WorkflowEpisodeItem {
1257    WorkflowEpisodeItem {
1258        manga_id: get_i32(row, "mangaId"),
1259        episode_id: get_i32(row, "episodeId"),
1260        manga_episode: try_get_str(row, "mangaEpisode"),
1261        episode_name: try_get_str(row, "mangaEpisodeName"),
1262        manga_tran_name: try_get_str(row, "mangaTranName"),
1263        category: try_get_i16(row, "category"),
1264        member_id: row
1265            .try_get::<i32>("", "memberId")
1266            .or_else(|_| row.try_get::<i32>("", "memberid"))
1267            .ok(),
1268        member_name: try_get_str(row, "memberName"),
1269        intern: row.try_get::<i32>("", "intern").ok(),
1270        step_label: try_get_str(row, "stepLabel"),
1271        match_step: try_get_str(row, "matchStep"),
1272        previous_update_time: try_dt(row, "previousUpdateTime"),
1273        setup_time: try_dt(row, "setupTime"),
1274    }
1275}
1276
1277/// 行映射为组员任务计数
1278fn row_to_member_task_count(row: &QueryResult) -> MemberTaskCountRow {
1279    MemberTaskCountRow {
1280        user_id: get_i32(row, "userId"),
1281        user_name: try_get_str(row, "userName"),
1282        intern: row.try_get::<i32>("", "intern").ok(),
1283        email: try_get_str(row, "email"),
1284        last_update_time: try_dt(row, "lastUpdateTime").map(|t| t.to_rfc3339()),
1285        translator_count: get_i64(row, "translatorCount"),
1286        proofreader_count: get_i64(row, "proofreaderCount"),
1287        letterer_count: get_i64(row, "lettererCount"),
1288        timer_count: get_i64(row, "timerCount"),
1289        reviewer_count: get_i64(row, "reviewerCount"),
1290        stationed_count: get_i64(row, "stationedCount"),
1291    }
1292}
1293
1294/// 行映射为未发布话数
1295fn row_to_unpublished(row: &QueryResult) -> UnpublishedEpisodeRow {
1296    UnpublishedEpisodeRow {
1297        episode_id: get_i32(row, "episodeId"),
1298        manga_id: get_i32(row, "mangaId"),
1299        manga_episode: try_get_str(row, "mangaEpisode"),
1300        manga_episode_name: try_get_str(row, "mangaEpisodeName"),
1301        manga_tran_name: try_get_str(row, "mangaTranName"),
1302        category: try_get_i16(row, "category"),
1303        translator_id: row.try_get::<i32>("", "translatorId").ok(),
1304        proofreader_id: row.try_get::<i32>("", "proofreaderId").ok(),
1305        letterer_id: row.try_get::<i32>("", "lettererId").ok(),
1306        timer_id: row.try_get::<i32>("", "timerId").ok(),
1307        reviewer_id: row.try_get::<i32>("", "reviewerId").ok(),
1308        translator_setup_time: try_dt(row, "translatorSetupTime"),
1309        translator_update_time: try_dt(row, "translatorUpdateTime"),
1310        proofreader_setup_time: try_dt(row, "proofreaderSetupTime"),
1311        proofreader_update_time: try_dt(row, "proofreaderUpdateTime"),
1312        letterer_setup_time: try_dt(row, "lettererSetupTime"),
1313        letterer_update_time: try_dt(row, "lettererUpdateTime"),
1314        timer_setup_time: try_dt(row, "timerSetupTime"),
1315        timer_update_time: try_dt(row, "timerUpdateTime"),
1316        reviewer_setup_time: try_dt(row, "reviewerSetupTime"),
1317        reviewer_update_time: try_dt(row, "reviewerUpdateTime"),
1318        episode_update_time: try_dt(row, "episodeUpdateTime"),
1319    }
1320}
1321
1322/// 行映射为待发布话数
1323fn row_to_pending_episode(row: &QueryResult) -> PendingEpisodeRow {
1324    PendingEpisodeRow {
1325        episode_id: get_i32(row, "episodeId"),
1326        manga_id: get_i32(row, "mangaId"),
1327        manga_episode: try_get_str(row, "mangaEpisode"),
1328        manga_episode_name: try_get_str(row, "mangaEpisodeName"),
1329        episode_type: if try_get_str(row, "episodeType").as_deref()
1330            == Some(EpisodeType::Extra.as_str())
1331        {
1332            EpisodeType::Extra
1333        } else {
1334            EpisodeType::Main
1335        },
1336        provider_id: row.try_get::<i32>("", "providerId").ok(),
1337        translator_id: row.try_get::<i32>("", "translatorId").ok(),
1338        proofreader_id: row.try_get::<i32>("", "proofreaderId").ok(),
1339        letterer_id: row.try_get::<i32>("", "lettererId").ok(),
1340        timer_id: row.try_get::<i32>("", "timerId").ok(),
1341        reviewer_id: row.try_get::<i32>("", "reviewerId").ok(),
1342        setup_time: try_dt(row, "setupTime"),
1343        update_time: try_dt(row, "updateTime"),
1344        translator_file: try_get_str(row, "translatorFile"),
1345        proofreader_file: try_get_str(row, "proofreaderFile"),
1346        timer_file: try_get_str(row, "timerFile"),
1347        publish_link: try_get_str(row, "publishLink"),
1348        provider_file_oss_id: row.try_get::<i32>("", "providerFileOssId").ok(),
1349        translator_file_oss_id: row.try_get::<i32>("", "translatorFileOssId").ok(),
1350        proofreader_file_oss_id: row.try_get::<i32>("", "proofreaderFileOssId").ok(),
1351        letterer_file_oss_id: row.try_get::<i32>("", "lettererFileOssId").ok(),
1352        timer_file_oss_id: row.try_get::<i32>("", "timerFileOssId").ok(),
1353        reviewer_update_time: try_dt(row, "reviewerUpdateTime"),
1354        manga_tran_name: try_get_str(row, "mangaTranName"),
1355        manga_ori_name: try_get_str(row, "mangaOriName"),
1356        category: try_get_i16(row, "category"),
1357        manga_status: try_get_i16(row, "mangaStatus"),
1358        image: try_get_str(row, "image"),
1359        manga_setup_time: try_dt(row, "mangaSetupTime"),
1360        manga_update_time: try_dt(row, "mangaUpdateTime"),
1361        link: try_get_str(row, "link"),
1362        introduction: try_get_str(row, "introduction"),
1363    }
1364}
1365
1366fn make_episode_task(
1367    ep: &UnpublishedEpisodeRow,
1368    post: &str,
1369    member_id: Option<i32>,
1370    setup_time: Option<DateTime<Utc>>,
1371    previous_update_time: Option<DateTime<Utc>>,
1372) -> EpisodeTask {
1373    EpisodeTask {
1374        manga_id: Some(ep.manga_id),
1375        episode_id: Some(ep.episode_id),
1376        manga_episode: ep.manga_episode.clone(),
1377        episode_name: ep.manga_episode_name.clone(),
1378        member_id,
1379        setup_time,
1380        previous_update_time,
1381        post: Some(post.to_string()),
1382    }
1383}
1384
1385fn push_task<F>(
1386    resp: &mut TaskTrackingResponse,
1387    ep: &UnpublishedEpisodeRow,
1388    post: &str,
1389    member_id: Option<i32>,
1390    setup_time: Option<DateTime<Utc>>,
1391    previous_update_time: Option<DateTime<Utc>>,
1392    push_list: F,
1393) where
1394    F: FnOnce(&mut TaskTrackingResponse, EpisodeTask),
1395{
1396    let task = make_episode_task(ep, post, member_id, setup_time, previous_update_time);
1397    push_list(resp, task);
1398}
1399
1400/// 从 UNION 分桶查询结果构建任务看板响应
1401fn build_task_response_from_union(rows: &[QueryResult]) -> TaskTrackingResponse {
1402    let mut resp = TaskTrackingResponse::empty();
1403    for row in rows {
1404        let ep = row_to_unpublished(row);
1405        let post = try_get_str(row, "bucketPost").unwrap_or_default();
1406        let member_id = row.try_get::<i32>("", "taskMemberId").ok();
1407        let setup_time = try_dt(row, "taskSetupTime");
1408        let previous_update_time = try_dt(row, "taskPreviousUpdateTime");
1409        let task = make_episode_task(&ep, &post, member_id, setup_time, previous_update_time);
1410        match post.as_str() {
1411            "PUBLISHER" => resp.publish_task_list.push(task),
1412            "REVIEWER" => resp.reviewer_task_list.push(task),
1413            "LETTERER" => resp.letterer_task_list.push(task),
1414            "TIMER" => resp.timer_task_list.push(task),
1415            "PROOFREADER" => resp.proofreader_task_list.push(task),
1416            _ => resp.translator_task_list.push(task),
1417        }
1418    }
1419    resp
1420}
tdm_server_rust/repository/task_tracking_repo.rs

tdm_server_rust/repository/
task_tracking_repo.rs
