---
# System prepended metadata

title: VIP系統規格書

---

# VIP系統規格書

[TOC]

## 需求說明


### 階級方案

#### 靜態階級

數據庫初始時，靜態生成 <span style="color:#808080">**10**</span> 個階級．

- 禮包識別碼規則

    > 組別階級編號 = `id`(自增鍵) % 10 

    以 `vip_gifts_{組別階級編號}` 為禮包唯一識別碼．

- 修改週期數據

    複製當前週期對應的系列階級，修改並於插入數據庫作為下一週期的數據．

- 新增週期數據

    各週期的組別階級數據為常量，不允許新增．
        
#### 動態階級

- 禮包識別碼規則

    > 門檻權重 = 儲值門檻 + 有效投注
    > 階級 = 升冪排序索引(門檻權重&id) 

    以 `vip_gifts_{階級}` 為禮包唯一識別碼．

- 修改週期數據

    複製當前週期對應的系列階級，修改並於插入數據庫作為下一週期的數據．

- 新增週期數據

    複製當前週期對應的系列階級，新增並於插入數據庫作為下一週期的數據．

    <details>
    <summary><b>週期迭代情境說明</b></summary>

    > 第一個週期
    > |id|*階級*|儲值|投注|*權重*|
    > |-|-|-|-|-|
    > |4|1|100|300|400|
    > |2|2|150|350|500|
    > |3|3|200|400|600|
    > |1|4|100|500|600|
    > |5|5|250|450|700|

    假設玩家在第一個週期達到`階級二`，結算時將派發`階級一`與`階級二`對應的禮包內容．


    > 第二個週期（相較第一個週期，在原本`階級一`與`階級二`間插入一個新階級）
    > |id|*階級*|儲值|投注|*權重*|
    > |-|-|-|-|-|
    > |4|1|100|300|400|
    > |6|2|200|250|450|
    > |2|3|150|350|500|
    > |3|4|200|400|600|
    > |1|5|100|500|600|
    > |5|6|250|450|700|

    第二個週期結算時，由於`階級一`與`階級二`對應的禮包內容已在第一週期派發，故無法再次派發，此時若玩家達成`階級三`的門檻時，將會派發`階級三`對應的禮包內容．
    </details>

## API規格

### 檢索階級門檻

> 各階級有效投注門檻、各階級儲值門檻、各階級等級．

### 檢索階級狀態

> 當前階級、週期內有效投注、週期內儲值．

### VIP前端API
[連結](https://hackmd.io/wt94MYY9RJmta2fN_qnXIQ?both)

## 行為流程圖

### 等級提升

### 降級處置

### 週期設置

### 禮包派發

## 數據庫表

### 狀態表(vip_stats)

#### 數據結構

| user_id | deposit | valid_bet | tier_id | tier_id_achieved | created_at         | updated_at         |
|---------|---------|-----------|-------|----------------|--------------------|--------------------|
| 1001    | 650     | 720       | 1     | 2              | 2024-02-26 08:00:00| 2024-02-26 08:00:00|
| 1002    | 420     | 890       | 2     | 2              | 2024-02-25 11:30:00| 2024-02-25 11:30:00|
| 1003    | 880     | 250       | 2     | 3              | 2024-02-24 09:45:00| 2024-02-24 09:45:00|
| 1004    | 210     | 510       | 3     | 3              | 2024-02-23 06:20:00| 2024-02-23 06:20:00|
| 1005    | 790     | 320       | 3     | 4              | 2024-02-22 13:10:00| 2024-02-22 13:10:00|


> 透過 `level_achived`(達成等級) 與 `level`(目前等級) 判斷玩家是否降級過．

- Laravel Migration

    ```PHP
        Schema::create('vip_stats', function (Blueprint $table) {
            $table->string('user_id', 32);
            $table->decimal('deposit', 16, 4)
                ->default(0.0000)
                ->comment('歷史累積儲值');
            $table->decimal('valid_bet', 16, 4)
                ->default(0.0000)
                ->comment('歷史累積效投');
            $table->unsignedtinyInteger('tier_id')->comment('目前階級id');
            $table->unsignedtinyInteger('tier_id_achieved')->comment('達成階級id');
            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

            $table->primary('user_id');
            $table->index('user_id');
            $table->index('updated_at');
        });
    ```

### 週期表(vip_cycles)

會員週期與版主週期統計數據．

#### 數據結構

| id | user_id       | deposit | valid_bet | cycle_begin_date      | cycle_until_date      | created_at          | updated_at          |
|----|---------|---------|-----------|---------------------|---------------------|----------------------|----------------------|
| 1  | 1001    | 500     | 700      | 2024-03-01 | 2024-03-30| 2024-02-28| 2024-02-28|
| 2  | 1002   | 1000    | 1500      | 2024-03-01| 2024-03-30 | 2024-02-29| 2024-02-29|
| 3  | 1003   | 1500    | 2000      | 2024-03-01| 2024-03-30| 2024-03-01| 2024-03-01|
| 4  | 1004  | 2000    | 2500      | 2024-03-01 | 2024-03-30| 2024-03-01| 2024-03-02|
| 5  | 1005   | 2500    | 3000      | 2024-03-01 | 2024-03-30 | 2024-03-01 | 2024-03-03 |
| 6  | 1004    | 2500    | 3000      | 2024-04-01  | 2024-04-30 | 2024-04-03   | 2024-03-03  |
| 7  | 1005    | 2500    | 3000      | 2024-04-01 | 2024-04-30 | 2024-04-03 | 2024-04-03 |


- Laravel Migration

    ```PHP
        Schema::create('vip_cycles', function (Blueprint $table) {
            $table->increments('id');
            $table->string('user_id', 32);
            $table->decimal('deposit', 16, 4)
                ->default(0.0000)
                ->comment('當週累積儲值');
            $table->decimal('valid_bet', 16, 4)
                ->default(0.0000)
                ->comment('當週累積效投');
            $table->date('cycle_begin_date')->comment('週期起始日');
            $table->date('cycle_until_date')->comment('週期結束日');
            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

            $table->index('user_id');
            $table->index(['cycle_begin_at', 'cycle_until_at']);
            $table->index('updated_at');
        });
    ```

### 階級表(vip_tiers)

定義階級所需要的儲值門檻、投注門檻．

#### 數據結構

| id | name | upgrade_deposit | upgrade_valid_bet | keep_deposit | keep_valid_bet | enable  | created_at| updated_at|
|----|--------------------|---------|-----------|---------|-----------|-----------|--------------------|--------------------|
| 1  | 階級1| 300     | 400       | 300     | 400       | ture | 2024-02-20 08:00:00| 2024-02-20 08:00:00|
| 2  | 階級2| 350     | 500       | 350     | 500       | false | 2024-02-20 08:00:00| 2024-02-20 08:00:00|
| 3  | 階級3| 400     | 600       | 400     | 600       | ture | 2024-02-20 08:00:00| 2024-02-20 08:00:00|
| 4  | 階級4| 500     | 600       | 500     | 600       | ture | 2024-02-20 08:00:00| 2024-02-20 08:00:00|
| 5  | 階級5| 450     | 700       | 450     | 700       | ture | 2024-02-20 08:00:00| 2024-02-20 08:00:00|
| 6  | 階級6| 300     | 400       | 300     | 400       | ture | 2024-02-22 13:10:00| 2024-02-22 13:10:00|
| 7  | 階級7| 350     | 500       | 350     | 500       | ture | 2024-02-22 13:10:00| 2024-02-22 13:10:00|
| 8  | 階級8| 400     | 600       | 400     | 600       | false | 2024-02-22 13:10:00| 2024-02-22 13:10:00|
| 9  | 階級9| 500     | 600       | 500     | 600       | ture | 2024-02-22 13:10:00| 2024-02-22 13:10:00|
| 10 | 階級10| 450     | 700      | 450     | 700      | false | 2024-02-22 13:10:00| 2024-02-22 13:10:00|

- Laravel Migration

    ```PHP
        Schema::create('vip_tiers', function (Blueprint $table) {
            $table->string('id', 32)->comment('階級id');
            $table->string('name', 32)->comment('名稱');
            $table->decimal('upgrade_deposit', 16, 4)
                ->default(0.0000)
                ->comment('升級儲值門檻');
            $table->decimal('upgrade_valid_bet', 16, 4)
                ->default(0.0000)
                ->comment('升級效投門檻');
            $table->decimal('keep_deposit', 16, 4)
                ->default(0.0000)
                ->comment('保級儲值門檻');
            $table->decimal('keep_valid_bet', 16, 4)
                ->default(0.0000)
                ->comment('保級效投門檻');
            $table->boolean('enable')->default(false)->comment('開關');
            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

            $table->primary('id');
            $table->unique('upgrade_deposit');
            $table->unique('upgrade_valid_bet');
            $table->index(['id', 'enable']);
            $table->index('updated_at');
        });
    ```


### 日誌表(vip_logs)

1. 異動VIP等級時需要紀錄
2. 派發3種禮包時紀錄
3. VIP門檻升降級
4. 手動調整VIP等級

#### 數據結構
| id | user_id | type | current_tier_id | achieved_tier_ids | log_date | handler_id | remark | notice | created_at| updated_at|
|----|----|----|--------|---------|-------|-------|-------|-------|--------------------|--------------------|
|01hrrxnaeehy6nd58bzbf0r54n|01hcyg20p3v4npn7pc356xvyjk|register|1|[]|2024-03-19|01hcyg20p3v4npn7pc356xvyjl||no|2024-03-19 08:00:00| 2024-03-19 08:00:00|
|01hrrxnaeehy6nd58bzbf0r54k|01hcyg20p3v4npn7pc356xvyjk|system-upgrade|2|[2]|2024-03-19|01hcyg20p3v4npn7pc356xvyjl||no|2024-03-19 12:00:00| 2024-03-19 12:00:00|
|01hrrxnaeehy6nd58bzbf0r54l|01hcyg20p3v4npn7pc356xvyjk|manual-upgrade|4|[3,4]|2024-03-19|01hcyg20p3v4npn7pc356xvyjl||no|2024-03-19 14:00:00| 2024-03-19 14:00:00|
|01hrrxnaeehy6nd58bzbf0r54a|01hcyg20p3v4npn7pc356xvyjk|downgrade|1|[]|2024-03-30|01hcyg20p3v4npn7pc356xvyjl||no|2024-03-30 12:00:00| 2024-03-30 12:00:00|

- Laravel Migration
    ```PHP
        Schema::create('vip_logs', function (Blueprint $table) {
            // [PK] 資料識別碼
            $table->string('id', 32)->comment('資料識別碼');
            // 會員ID
            $table->string('user_id', 32)->comment('會員ID, mapping users.id');
            $table->string('type', 32)->comment('紀錄類型');
            $table->unsignedtinyInteger('current_tier_id')->comment('目前階級id');
            $table->longtext('achieved_tier_ids')
                ->default('[]')
                ->comment('升降級的tier id or 禮包id');
            $table->string('log_date')->comment('紀錄時間');
            $table->string('handler_id', 32)->comment('操作人員');
            // 備註
            $table->text('remark')->default('')->comment('備註');
            // 會員推波通知開關 (yes已通知, no未通知)
            $table->enum('notice', ['yes', 'no'])->default('no')->comment('會員推波通知開關 (yes已通知, no未通知)');

            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

            // === 索引 ===
            // 指定主鍵索引
            $table->primary(['id']);
            $table->index('user_id');
            $table->index('type');
            $table->index('updated_at');
        });
    ```

#### 站台/會員週期相關設置

config

1.起始日期
2.週期天數

#### 業務邏輯

- VIP階級推算

    - SQL 語法

        ```SQL
        SELECT 
            ROW_NUMBER() OVER(
                ORDER BY (deposit + valid_bet) ASC, id ASC
            ) AS level,
            id, 
            deposit, 
            valid_bet, 
            (deposit + valid_bet) AS weight
        FROM your_table_name
        ORDER BY weight ASC, id ASC;
        ```


    - Laravel 語法

        ```PHP=
        $results = YourTableName::select([
                DB::raw("ROW_NUMBER() OVER(
                    ORDER BY (deposit + valid_bet) ASC, id ASC
                ) AS level"),
                'valid_bet',
                'deposit',
                DB::raw('(deposit + valid_bet) AS weight')
            ])
            ->orderBy('weight', 'asc')
            ->orderBy('id', 'asc')
            ->get();
        ```

    - 預期結果

        |level|deposit|valid_bet|weight|
        |-|-|-|-|
        |1|100|300|400|
        |2|150|350|500|
        |3|200|400|600|
        |4|100|500|600|
        |5|250|450|700|
        
### 活動對應表(vip_activity_relation)
VIP與活動關聯的表
#### 數據結構
|id|vip_tiers_id|activity_id|type|point|rebate_table|rebate_mode|threshold_gift_multiple|revenge_limit|revenge_frequency_days|gift_limit|created_at|updated_at|
|-------|----|---------|-----|------|----|------|---------|---|----|----|----------|---------|
|01e4nb76nq2440vpmjf6b4dhva|01e4nb76nq2440vpmjf6b4dhvb|01e4nb76nq2440vpmjf6b4dhvd|upgrade|[]|null|50|1|0|0|0|2024-02-20 08:00:00| 2024-02-20 08:00:00|
|01e66hv9j1e8b8rjde9xtak73a|01e66hv9j1e8b8rjde9xtak73b|01e66hv9j1e8b8rjde9xtak731|revenge|[]|null|60|1|100|20|0|2024-02-20 08:00:00| 2024-02-20 08:00:00|
|01e66mp01m3w9crbbb3aaff0ka|01e66mp01m3w9crbbb3aaff0kb|01e66mp01m3w9crbbb3aaff0kd|rebate|{"sa_gaming":{"bac":{"general":"9"}}}|station|0|1|0|0|1000|2024-02-20 08:00:00| 2024-02-20 08:00:00|
- Laravel Migration

    ```PHP
        Schema::create('vip_activity_relation', function (Blueprint $table) {
            $table->string('id', 32);
            $table->string('vip_tier_id', 32)->comment('VIP階級ID');
            $table->string('activity_id', 32)->comment('活動ID');
            $table->string('type', 16)->comment('類型 (upgrade=升級, revenge=復仇金, rebate=返水)');
            $table->decimal('point', 16, 4)
                ->default(0.0000)
                ->comment('獎金');
            $table->longtext('rebate_table')
                ->default('[]')
                ->comment('遊戲返水表');
            $table->string('rebate_mode', 16)
                ->nullable()
                ->default(null)
                ->comment('返水模式');
            $table->decimal('revenge_limit', 16, 4)
                ->default(0.0000)
                ->comment('復仇金最低額');
            $table->unsignedtinyInteger('revenge_frequency_days')->default(0)->comment('復仇金發送頻率');
            $table->decimal('threshold_gift_multiple', 16, 4)
                ->default(0.0000)
                ->comment('洗碼倍數(禮金)');
            $table->decimal('gift_limit', 16, 4)
                ->default(0.0000)
                ->comment('禮金上限');
            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

            $table->primary('id');
            $table->index(['activity_id', 'vip_tier_id']);
            $table->index('type');
            $table->index('updated_at');
        });
    ```

### VIP活動條件表(vip_gift_condition)

由vip_activity_relation衍生出來的活動條件．
#### 數據結構
|id|is_daily_login|total_valid_bet|station_valid_bet|revenge_limit|revenge_frequency_days|created_at|updated_at|
|---|---|---|---|---|---|---|---|
|01e4nb76nq2440vpmjf6b4dhva|true|null|null|null|null|2024-02-20 08:00:00| 2024-02-20 08:00:00|
|01e4nb76nq2440vpmjf6b4dhvb|null|100|null|null|null|2024-02-20 08:00:00| 2024-02-20 08:00:00|
|01e4nb76nq2440vpmjf6b4dhvc|null|null|1000|null|null|2024-02-20 08:00:00| 2024-02-20 08:00:00|
- Laravel Migration
```php
        Schema::create('vip_gift_condition', function (Blueprint $table) {
            $table->string('id', 32);
            $table->boolean('is_daily_login')->nullable()->comment('每日登入');
            $table->decimal('total_valid_bet', 16, 4)->nullable()->comment('總有效投注');
            $table->longtext('station_valid_bet')->nullable()->comment('特定遊戲館有效投注');
            $table->decimal('revenge_limit', 16, 4)->nullable()->comment('復仇金最低額');
            $table->unsignedtinyInteger('revenge_frequency_days')->nullable()->comment('復仇金發送頻率');
            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

            $table->primary('id');
            $table->index('updated_at');
        });
```
### 活動彩卷表(activity_lottery)

活動彩卷包含所屬活動、週期、票卷類型、關聯獲得獎項．
#### 數據結構
|id|user_id|activity_id|activity_sheet_id|gift_id|gift_model|ticket_type|prize_id|expired_at|used_at|handle_by|created_at|updated_at|
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|01e66mp01m3w9crbbb3aaff0kb|01e66mp01m3w9crbbb3aafsjro|01e66mp01m3w9crbbb3aaff8po|01e66mp01m3w9crbbb3aaffjey|null|null|null|null|2999-01-01 00:00:00|null|01e66mp01m3w9crbbb3aajue8i|2024-02-20 08:00:00| 2024-02-20 08:00:00|

- Laravel Migration
```php
        Schema::create('activity_lottery', function (Blueprint $table) {
            $table->string('id', 32)->comment('獎券唯一值');
            $table->string('user_id', 32)->comment('會員id');
            $table->string('activity_id', 32)->nullable()->comment('對應活動id');
            $table->string('activity_sheet_id', 32)->nullable()->comment('對應活動期數id');
            $table->string('gift_id', 32)->nullable()->comment('對應禮包資訊id');
            $table->string('gift_model', 16)->nullable()->comment('對應禮包的model');
            $table->string('ticket_type', 8)->nullable()->comment('票券類型');
            $table->string('prize_id', 32)->nullable()->comment('對應獎項id');
            $table->datetime('expired_at')->default('2999-01-01 00:00:00')->comment('獎券到期時間');
            $table->datetime('used_at')->nullable()->comment('獎券抽獎時間');
            $table->string('handle_by', 32)->nullable()->comment('派發人員');
            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

            $table->primary('id');
            $table->index('user_id');
            $table->index(['activity_id', 'activity_sheet_id']);

            $table->index('created_at');
            $table->index('updated_at');
        });
```
        
## 開發資訊
### setting 設定參數
> 取得方式
```php= 
config('platform.vip_cycles')
```
> 參數內容
```php=
    'vip_cycles' => [
        'days' => -1,
        'begin_date' => '',
        'type' => '',
    ]
```
### Gitlab分支
> vip_dev
### 開發測試站
> aa 站
### 前端頁面
> [here](https://upspeedt.com/)
### 版主端頁面
> [here](https://ctup.upspeedt.com/zh-Hant)
> 帳密同ab

==關閉自動部署，需進機器自行reset==

## 系統Q&A

1. 異常補發VIP禮包時是否需要以前一個週期的規則？

2. 版主套用會員週期時，由於各用戶週期不同，要如何統計上期人數？
-- 設定成會員週期不用顯示上期人數
3. 每日結算時間是否可以訂於中午12:00？
-- 可設定於中午12:00
4. 週期內異動VIP階級表時，是否會影響當下週期的邏輯判斷？
        
## 測試要點
## 小記錄
1. 開一張附表對應 activities 與 vip
2. sheet 表為排程自動產生
* `activity:manual-checkout->checkoutActivityPeriod`
* `activity:rebate:sheet->createRebateActivitySheetByDateTime`
3. 一個VIP會產生三個禮包：返水、升級、復仇