tdm_server_rust/repository/
reward_repo.rs

1//! 奖励数据访问层 (Reward Repository)
2//!
3//! 封装 `rewardtb`、`rewardrecord`、`rewardticket` 等表的查询。
4
5use crate::entity::reward::{
6    EventBalanceDto, RewardRecordResultDto, RewardTicketResultDto, RewardTicketTransferLog,
7    RewardTicketsDetailDto, RewardWinnerDto, Rewardtb,
8};
9use chrono::NaiveDateTime;
10use sqlx::{MySql, Pool, Row};
11use uuid::Uuid;
12
13/// 奖励仓储
14pub struct RewardRepository {
15    /// 连接池
16    pool: Pool<MySql>,
17}
18
19impl RewardRepository {
20    /// 构造仓储
21    pub fn new(pool: Pool<MySql>) -> Self {
22        Self { pool }
23    }
24
25    /// 查询活动奖品列表
26    #[tracing::instrument(skip_all, level = "debug")]
27    pub async fn list_rewards(&self, event_id: i32) -> crate::error::ApiResult<Vec<Rewardtb>> {
28        let rows = sqlx::query(
29            "SELECT rewardId, name, image, fromEvent, fromEventId, rewardCategory, physicalCategory, \
30             price, stock, totalNumber, deadLine, sponsor, sponsorId \
31             FROM rewardtb WHERE fromEventId = ? ORDER BY rewardId",
32        )
33        .bind(event_id)
34        .fetch_all(&self.pool)
35        .await?;
36        Ok(rows.into_iter().map(row_to_rewardtb).collect())
37    }
38
39    /// 按 ID 查询奖品
40    #[tracing::instrument(skip_all, level = "debug")]
41    pub async fn get_reward_by_id(
42        &self,
43        reward_id: i32,
44    ) -> crate::error::ApiResult<Option<RewardRow>> {
45        let row = sqlx::query(
46            "SELECT rewardId, name, price, rewardCategory, physicalCategory, stock, deadLine, fromEventId \
47             FROM rewardtb WHERE rewardId = ?",
48        )
49        .bind(reward_id)
50        .fetch_optional(&self.pool)
51        .await?;
52        Ok(row.map(|r| row_to_reward(&r)))
53    }
54
55    /// 扣减兑换余额
56    #[tracing::instrument(skip_all, level = "debug")]
57    pub async fn deduct_exchange_balance(
58        &self,
59        member_id: i32,
60        amount: i32,
61    ) -> crate::error::ApiResult<u64> {
62        let r = sqlx::query(
63            "UPDATE rewardticketnumbertb SET exchangeBalance = exchangeBalance - ? \
64             WHERE memberId = ? AND exchangeBalance >= ?",
65        )
66        .bind(amount)
67        .bind(member_id)
68        .bind(amount)
69        .execute(&self.pool)
70        .await?;
71        Ok(r.rows_affected())
72    }
73
74    /// 扣减抽奖余额
75    #[tracing::instrument(skip_all, level = "debug")]
76    pub async fn deduct_lucky_balance(
77        &self,
78        member_id: i32,
79        amount: i32,
80    ) -> crate::error::ApiResult<u64> {
81        let r = sqlx::query(
82            "UPDATE rewardticketnumbertb SET luckyBalance = luckyBalance - ? \
83             WHERE memberId = ? AND luckyBalance >= ?",
84        )
85        .bind(amount)
86        .bind(member_id)
87        .bind(amount)
88        .execute(&self.pool)
89        .await?;
90        Ok(r.rows_affected())
91    }
92
93    /// 查询组员奖券账户
94    #[tracing::instrument(skip_all, level = "debug")]
95    pub async fn get_ticket_number(
96        &self,
97        member_id: i32,
98    ) -> crate::error::ApiResult<Option<TicketNumberRow>> {
99        let row = sqlx::query(
100            "SELECT memberId, exchangeBalance, luckyBalance, total, eventId FROM rewardticketnumbertb WHERE memberId = ?",
101        )
102        .bind(member_id)
103        .fetch_optional(&self.pool)
104        .await?;
105        Ok(row.map(|r| TicketNumberRow {
106            member_id: r.get("memberId"),
107            exchange_balance: r.try_get("exchangeBalance").unwrap_or(0),
108            lucky_balance: r.try_get("luckyBalance").unwrap_or(0),
109            total: r.try_get("total").unwrap_or(0),
110            event_id: r.try_get("eventId").ok(),
111        }))
112    }
113
114    /// 新增兑换记录
115    #[tracing::instrument(skip_all, level = "debug")]
116    pub async fn insert_reward_record(
117        &self,
118        member_id: i32,
119        reward_id: i32,
120        exchange_number: i32,
121    ) -> crate::error::ApiResult<i64> {
122        let r = sqlx::query(
123            "INSERT INTO rewardrecordtb(memberId, rewardId, exchangeNumber, trackingNumber, exchangeTime) \
124             VALUES (?, ?, ?, '想起来了的话会填的喵~', NOW())",
125        )
126        .bind(member_id)
127        .bind(reward_id)
128        .bind(exchange_number)
129        .execute(&self.pool)
130        .await?;
131        Ok(r.last_insert_id() as i64)
132    }
133
134    /// 批量新增抽奖券
135    #[tracing::instrument(skip_all, level = "debug")]
136    pub async fn insert_lucky_tickets(
137        &self,
138        member_id: i32,
139        reward_id: i32,
140        event_id: i32,
141        physical_category: Option<i16>,
142        count: i32,
143    ) -> crate::error::ApiResult<()> {
144        for _ in 0..count {
145            let ticket_id = Uuid::new_v4().to_string();
146            sqlx::query(
147                "INSERT INTO rewardtickettb(memberId, rewardId, fromEventId, rewardTicketId, status, physicalCategory) \
148                 VALUES (?, ?, ?, ?, 1, ?)",
149            )
150            .bind(member_id)
151            .bind(reward_id)
152            .bind(event_id)
153            .bind(ticket_id)
154            .bind(physical_category)
155            .execute(&self.pool)
156            .await?;
157        }
158        Ok(())
159    }
160
161    /// 查询兑换记录
162    #[tracing::instrument(skip_all, level = "debug")]
163    pub async fn select_reward_list(
164        &self,
165        member_id: i32,
166    ) -> crate::error::ApiResult<Vec<RewardRecordResultDto>> {
167        let rows = sqlx::query(
168            "SELECT rr.rewardRecordId, rr.rewardId, rr.exchangeTime, rr.exchangeNumber, \
169             rr.trackingNumber, r.name AS rewardName, r.image AS rewardImage, r.rewardCategory AS getMethod \
170             FROM rewardrecordtb rr LEFT JOIN rewardtb r ON rr.rewardId = r.rewardId \
171             WHERE rr.memberId = ? ORDER BY rr.exchangeTime DESC",
172        )
173        .bind(member_id)
174        .fetch_all(&self.pool)
175        .await?;
176        Ok(rows.iter().map(row_to_reward_record).collect())
177    }
178
179    /// 查询奖券汇总
180    #[tracing::instrument(skip_all, level = "debug")]
181    pub async fn get_reward_tickets(
182        &self,
183        member_id: i32,
184    ) -> crate::error::ApiResult<Vec<RewardTicketResultDto>> {
185        let rows = sqlx::query(
186            "SELECT COUNT(rt.rewardTicketId) AS ticketNumber, rt.memberId, r.rewardId, \
187             MAX(rt.status) AS status, r.name AS rewardName, r.image AS rewardImage, \
188             MAX(rt.gachaTime) AS gachaTime \
189             FROM rewardtickettb rt LEFT JOIN rewardtb r ON rt.rewardId = r.rewardId \
190             WHERE rt.memberId = ? \
191             GROUP BY rt.memberId, r.rewardId, r.name, r.image \
192             ORDER BY MAX(rt.gachaTime) DESC",
193        )
194        .bind(member_id)
195        .fetch_all(&self.pool)
196        .await?;
197        Ok(rows.iter().map(row_to_reward_ticket).collect())
198    }
199
200    /// 随机抽取中奖者
201    #[tracing::instrument(skip_all, level = "debug")]
202    pub async fn select_winner(
203        &self,
204        reward_id: i32,
205        event_id: i32,
206    ) -> crate::error::ApiResult<Option<RewardWinnerDto>> {
207        let row = sqlx::query(
208            "SELECT rt.memberId, m.username, rt.rewardTicketId AS ticketId \
209             FROM rewardtickettb rt LEFT JOIN membertb m ON rt.memberId = m.Id \
210             WHERE rt.rewardId = ? AND rt.fromEventId = ? AND rt.status = 1 \
211             ORDER BY RAND() LIMIT 1",
212        )
213        .bind(reward_id)
214        .bind(event_id)
215        .fetch_optional(&self.pool)
216        .await?;
217        Ok(row.map(|r| RewardWinnerDto {
218            member_id: r.get("memberId"),
219            member_name: r.try_get("username").ok(),
220            ticket_id: r.try_get("ticketId").ok(),
221        }))
222    }
223
224    /// 封锁中奖者同类奖券
225    #[tracing::instrument(skip_all, level = "debug")]
226    pub async fn block_winner(
227        &self,
228        event_id: i32,
229        status: i16,
230        winner_id: i32,
231        physical_category: Option<i16>,
232    ) -> crate::error::ApiResult<()> {
233        sqlx::query(
234            "UPDATE rewardtickettb SET status = ? WHERE memberId = ? AND fromEventId = ? AND physicalCategory = ?",
235        )
236        .bind(status)
237        .bind(winner_id)
238        .bind(event_id)
239        .bind(physical_category)
240        .execute(&self.pool)
241        .await?;
242        Ok(())
243    }
244
245    /// 更新奖券状态
246    #[tracing::instrument(skip_all, level = "debug")]
247    pub async fn update_ticket_status(
248        &self,
249        ticket_id: &str,
250        status: i16,
251        record_id: i64,
252    ) -> crate::error::ApiResult<()> {
253        sqlx::query(
254            "UPDATE rewardtickettb SET status = ?, rewardRecordID = ? WHERE rewardTicketId = ?",
255        )
256        .bind(status)
257        .bind(record_id)
258        .bind(ticket_id)
259        .execute(&self.pool)
260        .await?;
261        Ok(())
262    }
263
264    /// 扣减奖品库存
265    #[tracing::instrument(skip_all, level = "debug")]
266    pub async fn decrement_stock(
267        &self,
268        reward_id: i32,
269        event_id: i32,
270        count: i32,
271    ) -> crate::error::ApiResult<u64> {
272        let r = sqlx::query(
273            "UPDATE rewardtb SET stock = stock - ? WHERE rewardId = ? AND fromEventId = ? AND stock >= ?",
274        )
275        .bind(count)
276        .bind(reward_id)
277        .bind(event_id)
278        .bind(count)
279        .execute(&self.pool)
280        .await?;
281        Ok(r.rows_affected())
282    }
283
284    /// 标记未中奖券
285    #[tracing::instrument(skip_all, level = "debug")]
286    pub async fn mark_non_win_tickets(&self, reward_id: i32) -> crate::error::ApiResult<()> {
287        sqlx::query("UPDATE rewardtickettb SET status = 2 WHERE rewardId = ? AND status = 1")
288            .bind(reward_id)
289            .execute(&self.pool)
290            .await?;
291        Ok(())
292    }
293
294    /// 检查活动是否已经开奖
295    #[tracing::instrument(skip_all, level = "debug")]
296    pub async fn has_opened_rewards(&self, event_id: i32) -> crate::error::ApiResult<bool> {
297        let row = sqlx::query(
298            "SELECT rewardId FROM rewardtb WHERE fromEventId = ? AND stock < totalNumber LIMIT 1",
299        )
300        .bind(event_id)
301        .fetch_optional(&self.pool)
302        .await?;
303        Ok(row.is_some())
304    }
305
306    /// 更新活动下所有奖品截止时间
307    #[tracing::instrument(skip_all, level = "debug")]
308    pub async fn update_event_deadline(
309        &self,
310        event_id: i32,
311        end_time: NaiveDateTime,
312    ) -> crate::error::ApiResult<()> {
313        sqlx::query("UPDATE rewardtb SET deadLine = ? WHERE fromEventId = ?")
314            .bind(end_time)
315            .bind(event_id)
316            .execute(&self.pool)
317            .await?;
318        Ok(())
319    }
320
321    /// 查询活动余额
322    #[tracing::instrument(skip_all, level = "debug")]
323    pub async fn get_reward_balance(
324        &self,
325        member_id: i32,
326        event_id: i32,
327    ) -> crate::error::ApiResult<EventBalanceDto> {
328        let row = sqlx::query(
329            "SELECT exchangeBalance, luckyBalance, total FROM rewardticketnumbertb \
330             WHERE memberId = ? AND eventId = ?",
331        )
332        .bind(member_id)
333        .bind(event_id)
334        .fetch_optional(&self.pool)
335        .await?;
336        Ok(match row {
337            Some(r) => EventBalanceDto {
338                user_id: member_id,
339                event_id,
340                exchange_balance: r.try_get("exchangeBalance").ok(),
341                lucky_balance: r.try_get("luckyBalance").ok(),
342                total_tickets: r.try_get("total").ok(),
343            },
344            None => EventBalanceDto {
345                user_id: member_id,
346                event_id,
347                exchange_balance: Some(0),
348                lucky_balance: Some(0),
349                total_tickets: Some(0),
350            },
351        })
352    }
353
354    /// 查询奖券明细
355    #[tracing::instrument(skip_all, level = "debug")]
356    pub async fn get_ticket_details(
357        &self,
358        member_id: i32,
359    ) -> crate::error::ApiResult<Vec<RewardTicketsDetailDto>> {
360        let rows = sqlx::query(
361            "SELECT rewardTicketId, fromEventId, rewardId, status FROM rewardtickettb WHERE memberId = ?",
362        )
363        .bind(member_id)
364        .fetch_all(&self.pool)
365        .await?;
366        Ok(rows
367            .into_iter()
368            .map(|r| RewardTicketsDetailDto {
369                reward_ticket_id: r.try_get("rewardTicketId").ok(),
370                from_event_id: r.try_get("fromEventId").ok(),
371                reward_id: r.try_get("rewardId").ok(),
372                status: r.try_get("status").ok(),
373            })
374            .collect())
375    }
376
377    /// 检测转账目标账户
378    #[tracing::instrument(skip_all, level = "debug")]
379    pub async fn check_account_exists(&self, member_id: i32) -> crate::error::ApiResult<bool> {
380        let row = sqlx::query("SELECT Id FROM membertb WHERE Id = ?")
381            .bind(member_id)
382            .fetch_optional(&self.pool)
383            .await?;
384        Ok(row.is_some())
385    }
386
387    /// 检测指定活动余额账户是否存在
388    #[tracing::instrument(skip_all, level = "debug")]
389    pub async fn check_balance_account_exists(
390        &self,
391        member_id: i32,
392        event_id: i32,
393    ) -> crate::error::ApiResult<bool> {
394        let row = sqlx::query(
395            "SELECT memberId FROM rewardticketnumbertb WHERE memberId = ? AND eventId = ?",
396        )
397        .bind(member_id)
398        .bind(event_id)
399        .fetch_optional(&self.pool)
400        .await?;
401        Ok(row.is_some())
402    }
403
404    /// 创建余额账户
405    #[tracing::instrument(skip_all, level = "debug")]
406    pub async fn create_balance_account(
407        &self,
408        member_id: i32,
409        event_id: i32,
410    ) -> crate::error::ApiResult<()> {
411        sqlx::query(
412            "INSERT INTO rewardticketnumbertb(memberId, exchangeBalance, luckyBalance, total, eventId) \
413             VALUES (?, 0, 0, 0, ?)",
414        )
415        .bind(member_id)
416        .bind(event_id)
417        .execute(&self.pool)
418        .await?;
419        Ok(())
420    }
421
422    /// 转账扣减余额
423    #[tracing::instrument(skip_all, level = "debug")]
424    pub async fn transfer_decr(
425        &self,
426        from_id: i32,
427        event_id: i32,
428        ticket_type: i32,
429        amount: i32,
430    ) -> crate::error::ApiResult<u64> {
431        let sql = match ticket_type {
432            2 => {
433                "UPDATE rewardticketnumbertb SET exchangeBalance = exchangeBalance - ? \
434                  WHERE memberId = ? AND eventId = ? AND exchangeBalance >= ?"
435            }
436            3 => {
437                "UPDATE rewardticketnumbertb SET luckyBalance = luckyBalance - ? \
438                  WHERE memberId = ? AND eventId = ? AND luckyBalance >= ?"
439            }
440            _ => return Err(crate::error::AppError::business("无效的券类型")),
441        };
442        let r = sqlx::query(sql)
443            .bind(amount)
444            .bind(from_id)
445            .bind(event_id)
446            .bind(amount)
447            .execute(&self.pool)
448            .await?;
449        Ok(r.rows_affected())
450    }
451
452    /// 转账增加余额
453    #[tracing::instrument(skip_all, level = "debug")]
454    pub async fn transfer_incr(
455        &self,
456        to_id: i32,
457        event_id: i32,
458        ticket_type: i32,
459        amount: i32,
460    ) -> crate::error::ApiResult<()> {
461        let sql = match ticket_type {
462            2 => {
463                "UPDATE rewardticketnumbertb SET exchangeBalance = exchangeBalance + ? \
464                  WHERE memberId = ? AND eventId = ?"
465            }
466            3 => {
467                "UPDATE rewardticketnumbertb SET luckyBalance = luckyBalance + ? \
468                  WHERE memberId = ? AND eventId = ?"
469            }
470            _ => return Err(crate::error::AppError::business("无效的券类型")),
471        };
472        sqlx::query(sql)
473            .bind(amount)
474            .bind(to_id)
475            .bind(event_id)
476            .execute(&self.pool)
477            .await?;
478        Ok(())
479    }
480
481    /// 写入转账日志
482    #[tracing::instrument(skip_all, level = "debug")]
483    pub async fn insert_transfer_log(
484        &self,
485        from_id: i32,
486        to_id: i32,
487        event_id: i32,
488        ticket_type: i32,
489        amount: i32,
490    ) -> crate::error::ApiResult<()> {
491        sqlx::query(
492            "INSERT INTO rewardtickettransferlog(from_member_id, to_member_id, event_id, ticket_type, amount, transfer_time) \
493             VALUES (?, ?, ?, ?, ?, NOW())",
494        )
495        .bind(from_id)
496        .bind(to_id)
497        .bind(event_id)
498        .bind(ticket_type)
499        .bind(amount)
500        .execute(&self.pool)
501        .await?;
502        Ok(())
503    }
504
505    /// 查询转账日志
506    #[tracing::instrument(skip_all, level = "debug")]
507    pub async fn get_transfer_logs(
508        &self,
509        member_id: i32,
510    ) -> crate::error::ApiResult<Vec<RewardTicketTransferLog>> {
511        let rows = sqlx::query(
512            "SELECT log_id, from_member_id, to_member_id, event_id, ticket_type, amount, transfer_time \
513             FROM rewardtickettransferlog \
514             WHERE from_member_id = ? OR to_member_id = ? ORDER BY transfer_time DESC",
515        )
516        .bind(member_id)
517        .bind(member_id)
518        .fetch_all(&self.pool)
519        .await?;
520        Ok(rows
521            .into_iter()
522            .map(|r| RewardTicketTransferLog {
523                log_id: r.try_get("log_id").ok(),
524                from_member_id: r.get("from_member_id"),
525                to_member_id: r.get("to_member_id"),
526                event_id: r.get("event_id"),
527                ticket_type: r.get("ticket_type"),
528                amount: r.get("amount"),
529                transfer_time: r.try_get("transfer_time").ok(),
530            })
531            .collect())
532    }
533}
534
535/// 奖品完整行
536#[derive(Debug, Clone)]
537pub struct RewardRow {
538    /// 奖品 ID
539    pub reward_id: i32,
540    /// 名称
541    pub name: Option<String>,
542    /// 价格
543    pub price: Option<i32>,
544    /// 类别
545    pub reward_category: Option<i16>,
546    /// 物理类别
547    pub physical_category: Option<i16>,
548    /// 库存
549    pub stock: Option<i32>,
550    /// 截止时间
551    pub dead_line: Option<NaiveDateTime>,
552    /// 活动 ID
553    pub from_event_id: Option<i32>,
554}
555
556/// 奖券账户行
557#[derive(Debug, Clone)]
558pub struct TicketNumberRow {
559    /// 组员 ID
560    pub member_id: i32,
561    /// 兑换余额
562    pub exchange_balance: i32,
563    /// 抽奖余额
564    pub lucky_balance: i32,
565    /// 总券数
566    pub total: i32,
567    /// 活动 ID
568    pub event_id: Option<i32>,
569}
570
571/// 行映射为兑换记录
572fn row_to_reward_record(row: &sqlx::mysql::MySqlRow) -> RewardRecordResultDto {
573    RewardRecordResultDto {
574        reward_record_id: row.try_get("rewardRecordId").ok(),
575        reward_id: row.try_get("rewardId").ok(),
576        exchange_time: row.try_get("exchangeTime").ok(),
577        exchange_number: row.try_get("exchangeNumber").ok(),
578        tracking_number: row.try_get("trackingNumber").ok(),
579        reward_name: row.try_get("rewardName").ok(),
580        reward_image: row.try_get("rewardImage").ok(),
581        get_method: row.try_get("getMethod").ok(),
582    }
583}
584
585/// 行映射为奖券汇总
586fn row_to_reward_ticket(row: &sqlx::mysql::MySqlRow) -> RewardTicketResultDto {
587    RewardTicketResultDto {
588        ticket_number: row.try_get("ticketNumber").ok(),
589        member_id: row.try_get("memberId").ok(),
590        reward_id: row.try_get("rewardId").ok(),
591        status: row.try_get("status").ok(),
592        reward_name: row.try_get("rewardName").ok(),
593        reward_image: row.try_get("rewardImage").ok(),
594        gacha_time: row.try_get("gachaTime").ok(),
595    }
596}
597
598/// 行映射为奖品列表项（对齐 Java `Rewardtb` JSON）
599fn row_to_rewardtb(row: sqlx::mysql::MySqlRow) -> Rewardtb {
600    Rewardtb {
601        reward_id: row.try_get("rewardId").ok(),
602        name: row.try_get("name").ok(),
603        image: row.try_get("image").ok(),
604        from_event: row.try_get("fromEvent").ok(),
605        from_event_id: row.try_get("fromEventId").ok(),
606        reward_category: row.try_get("rewardCategory").ok(),
607        physical_category: row.try_get("physicalCategory").ok(),
608        price: row.try_get("price").ok(),
609        total_number: row.try_get("totalNumber").ok(),
610        stock: row.try_get("stock").ok(),
611        dead_line: row
612            .try_get::<NaiveDateTime, _>("deadLine")
613            .ok()
614            .map(format_reward_dead_line),
615        sponsor: row.try_get("sponsor").ok(),
616        sponsor_id: row.try_get("sponsorId").ok(),
617    }
618}
619
620/// 格式化为 Java `OffsetDateTime` 东八区字符串
621fn format_reward_dead_line(dt: NaiveDateTime) -> String {
622    dt.and_utc()
623        .with_timezone(&crate::utils::shanghai_time::shanghai_offset())
624        .format("%Y-%m-%dT%H:%M:%S%:z")
625        .to_string()
626}
627
628/// 行映射为奖品
629fn row_to_reward(row: &sqlx::mysql::MySqlRow) -> RewardRow {
630    RewardRow {
631        reward_id: row.get("rewardId"),
632        name: row.try_get("name").ok(),
633        price: row.try_get("price").ok(),
634        reward_category: row.try_get("rewardCategory").ok(),
635        physical_category: row.try_get("physicalCategory").ok(),
636        stock: row.try_get("stock").ok(),
637        dead_line: row.try_get("deadLine").ok(),
638        from_event_id: row.try_get("fromEventId").ok(),
639    }
640}
tdm_server_rust/repository/reward_repo.rs

tdm_server_rust/repository/
reward_repo.rs
