tdm_server_rust/repository/
episode_statistics_assemble.rs

1//! 话数完成统计组装（单 SQL FILTER 批量聚合 + 并行组员统计）
2
3use crate::entity::episode::MemberStatistics;
4use crate::sea_entity::membertb;
5use chrono::{DateTime, NaiveDateTime, Utc};
6use sea_orm::{
7    ColumnTrait, ConnectionTrait, DatabaseConnection, DbBackend, EntityTrait, QueryFilter,
8    Statement, Value,
9};
10use std::collections::{HashMap, HashSet};
11
12/// 岗位完成统计
13#[derive(Debug, Clone)]
14pub struct PostCompletionStats {
15    /// 翻译完成数
16    pub translator_count: i64,
17    /// 校对完成数
18    pub proofreader_count: i64,
19    /// 嵌字完成数
20    pub letterer_count: i64,
21    /// 审稿完成数
22    pub reviewer_count: i64,
23}
24
25/// 各岗位 detail 时间列（PostgreSQL 引号列名）
26const ROLE_TIME_COLS: [(&str, &str); 4] = [
27    ("translatorUpdateTime", "translator"),
28    ("proofreaderUpdateTime", "proofreader"),
29    ("lettererUpdateTime", "letterer"),
30    ("reviewerUpdateTime", "reviewer"),
31];
32
33/// 统计单时间段内各岗位完成数（`[start, end]` 含边界，单条 SQL 四列 FILTER）
34pub async fn statistic_count_in_range(
35    db: &DatabaseConnection,
36    start: DateTime<Utc>,
37    end: DateTime<Utc>,
38) -> crate::error::ApiResult<PostCompletionStats> {
39    let rows = statistic_counts_in_ranges(db, &[(start.naive_utc(), end.naive_utc())]).await?;
40    rows.into_iter()
41        .next()
42        .ok_or_else(|| crate::error::AppError::business("统计失败喵"))
43}
44
45/// 批量统计多时间段（一条 SQL，每段 4 列 FILTER，共 1 次 round-trip）
46pub async fn statistic_counts_in_ranges(
47    db: &DatabaseConnection,
48    ranges: &[(NaiveDateTime, NaiveDateTime)],
49) -> crate::error::ApiResult<Vec<PostCompletionStats>> {
50    if ranges.is_empty() {
51        return Ok(vec![]);
52    }
53    let mut select_parts: Vec<String> = Vec::new();
54    let mut values: Vec<Value> = Vec::new();
55    let mut param_idx = 1i32;
56    for (ri, (start, end)) in ranges.iter().enumerate() {
57        let p_start = param_idx;
58        param_idx += 1;
59        let p_end = param_idx;
60        param_idx += 1;
61        for (col, alias) in ROLE_TIME_COLS {
62            select_parts.push(format!(
63                r#"COUNT(*) FILTER (WHERE "{col}" >= ${p_start} AND "{col}" <= ${p_end})::bigint AS "r{ri}_{alias}""#
64            ));
65        }
66        values.push(Value::from(*start));
67        values.push(Value::from(*end));
68    }
69    let sql = format!("SELECT {} FROM mangaepisodedetail", select_parts.join(", "));
70    let row = db
71        .query_one(Statement::from_sql_and_values(
72            DbBackend::Postgres,
73            &sql,
74            values,
75        ))
76        .await?
77        .ok_or_else(|| crate::error::AppError::business("统计失败喵"))?;
78
79    let mut out = Vec::with_capacity(ranges.len());
80    for ri in 0..ranges.len() {
81        out.push(PostCompletionStats {
82            translator_count: row.try_get("", &format!("r{ri}_translator"))?,
83            proofreader_count: row.try_get("", &format!("r{ri}_proofreader"))?,
84            letterer_count: row.try_get("", &format!("r{ri}_letterer"))?,
85            reviewer_count: row.try_get("", &format!("r{ri}_reviewer"))?,
86        });
87    }
88    Ok(out)
89}
90
91/// 查询组员完成统计（左开右闭区间 `[start, end)`，五岗位 SQL GROUP BY 并行）
92pub async fn member_statistics_in_range(
93    db: &DatabaseConnection,
94    start: DateTime<Utc>,
95    end: DateTime<Utc>,
96    member_id: Option<i32>,
97) -> crate::error::ApiResult<Vec<MemberStatistics>> {
98    let start = start.naive_utc();
99    let end = end.naive_utc();
100    let (translator, proofreader, letterer, timer, reviewer) = tokio::try_join!(
101        role_member_counts_sql(
102            db,
103            "translatorId",
104            "translatorUpdateTime",
105            start,
106            end,
107            member_id,
108        ),
109        role_member_counts_sql(
110            db,
111            "proofreaderId",
112            "proofreaderUpdateTime",
113            start,
114            end,
115            member_id,
116        ),
117        role_member_counts_sql(
118            db,
119            "lettererId",
120            "lettererUpdateTime",
121            start,
122            end,
123            member_id,
124        ),
125        role_member_counts_sql(db, "timerId", "timerUpdateTime", start, end, member_id),
126        role_member_counts_sql(
127            db,
128            "reviewerId",
129            "reviewerUpdateTime",
130            start,
131            end,
132            member_id,
133        ),
134    )?;
135
136    let mut member_ids: HashSet<i32> = HashSet::new();
137    for map in [&translator, &proofreader, &letterer, &timer, &reviewer] {
138        member_ids.extend(map.keys().copied());
139    }
140    let mut active: Vec<i32> = member_ids
141        .into_iter()
142        .filter(|mid| {
143            translator.get(mid).copied().unwrap_or(0)
144                + proofreader.get(mid).copied().unwrap_or(0)
145                + letterer.get(mid).copied().unwrap_or(0)
146                + timer.get(mid).copied().unwrap_or(0)
147                + reviewer.get(mid).copied().unwrap_or(0)
148                > 0
149        })
150        .collect();
151    if active.is_empty() {
152        return Ok(vec![]);
153    }
154    active.sort_unstable();
155
156    let members: HashMap<i32, membertb::Model> = membertb::Entity::find()
157        .filter(membertb::Column::Id.is_in(active.clone()))
158        .all(db)
159        .await?
160        .into_iter()
161        .map(|m| (m.id, m))
162        .collect();
163
164    Ok(active
165        .into_iter()
166        .filter_map(|mid| {
167            let m = members.get(&mid)?;
168            Some(MemberStatistics {
169                user_name: Some(m.username.clone()),
170                user_id: Some(mid.to_string()),
171                translator_count: translator.get(&mid).copied().unwrap_or(0),
172                proofreader_count: proofreader.get(&mid).copied().unwrap_or(0),
173                letterer_count: letterer.get(&mid).copied().unwrap_or(0),
174                timer_count: timer.get(&mid).copied().unwrap_or(0),
175                reviewer_count: reviewer.get(&mid).copied().unwrap_or(0),
176            })
177        })
178        .collect())
179}
180
181/// SQL GROUP BY 统计岗位组员完成数（`[start, end)`）
182async fn role_member_counts_sql(
183    db: &DatabaseConnection,
184    member_col: &str,
185    time_col: &str,
186    start: NaiveDateTime,
187    end: NaiveDateTime,
188    member_id: Option<i32>,
189) -> crate::error::ApiResult<HashMap<i32, i32>> {
190    let sql = if member_id.is_some() {
191        format!(
192            r#"SELECT e."{member_col}" AS member_id, COUNT(*)::int AS cnt
193            FROM mangaepisodedetail d
194            INNER JOIN mangaepisodetb e ON e."Id" = d."episodeId"
195            WHERE d."{time_col}" >= $1 AND d."{time_col}" < $2
196              AND e."{member_col}" = $3
197            GROUP BY e."{member_col}"
198            "#
199        )
200    } else {
201        format!(
202            r#"SELECT e."{member_col}" AS member_id, COUNT(*)::int AS cnt
203            FROM mangaepisodedetail d
204            INNER JOIN mangaepisodetb e ON e."Id" = d."episodeId"
205            WHERE d."{time_col}" >= $1 AND d."{time_col}" < $2
206              AND e."{member_col}" IS NOT NULL
207            GROUP BY e."{member_col}"
208            "#
209        )
210    };
211    let values: Vec<Value> = if let Some(id) = member_id {
212        vec![Value::from(start), Value::from(end), Value::from(id)]
213    } else {
214        vec![Value::from(start), Value::from(end)]
215    };
216    let rows = db
217        .query_all(Statement::from_sql_and_values(
218            DbBackend::Postgres,
219            &sql,
220            values,
221        ))
222        .await?;
223    let mut counts = HashMap::new();
224    for row in rows {
225        let mid: i32 = row.try_get("", "member_id")?;
226        let cnt: i32 = row.try_get("", "cnt")?;
227        counts.insert(mid, cnt);
228    }
229    Ok(counts)
230}
tdm_server_rust/repository/episode_statistics_assemble.rs

tdm_server_rust/repository/
episode_statistics_assemble.rs
