# 轉盤活動可加入存款自動發放序號
[TOC]

## 需求規格書
- 需求規格 (https://tinyurl.com/2p8m7s5z)
- Trello (https://trello.com/c/JHoKo6Dg)

## DataBase
### LuckyWheelMaster
在這個主表中，新增一個欄位 `DepositRewardType`，用來判定存款自動發放序號的行為

USE [CasinoCash.KD001]

ALTER TABLE LuckyWheelMaster
ADD DepositRewardType nvarchar(20) NULL; 

### LuckyWheelDepositRewardInfo
這個表儲存存款贈送序號的每一級距內容

USE [CasinoCash.KD001]
GO

/****** Object:  Table [dbo].[LuckyWheelDepositRewardInfo]    Script Date: 2022/9/12 下午 03:34:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LuckyWheelDepositRewardInfo](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [EventId] [int] NOT NULL,
    [SerialNumber] [nvarchar](16) NOT NULL,
    [DepositAmount] [decimal](18, 2) NOT NULL,
    [RewardTimes] [int] NOT NULL,
    [CreatedTime] [DateTime] NOT NULL,
 CONSTRAINT [PK_dbo.LuckyWheelDepositRewardInfo] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LuckyWheelDepositRewardInfo]  WITH CHECK ADD  CONSTRAINT [FK_dbo.LuckyWheelDepositRewardInfo_dbo.LuckyWheelMaster_EventId] FOREIGN KEY([EventId])
REFERENCES [dbo].[LuckyWheelMaster] ([Id])
GO

ALTER TABLE [dbo].[LuckyWheelDepositRewardInfo] CHECK CONSTRAINT [FK_dbo.LuckyWheelDepositRewardInfo_dbo.LuckyWheelMaster_EventId]
GO

### LuckyWheelDepositGroupAccountApply
這個 table 紀錄是因為哪張存款單，給了哪個序號
並且拿來 check 不要重複發放序號

USE [CasinoCash.KD001]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LuckyWheelDepositGroupAccountApply](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [GroupAccountApplyId] [int] NOT NULL,
    [SerialNumber] [nvarchar](16) NOT NULL,
    [DepositAmount] [decimal](18, 2) NOT NULL,
    [RewardTimes] [int] NOT NULL,
    [CreatedTime] [DateTime] NOT NULL,
 CONSTRAINT [PK_dbo.LuckyWheelDepositGroupAccountApply] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

## 流程
### 建立序號
- 儲存轉盤活動時，根據贈送次數條件進行動作
- 如果為存款贈送，依據級距建立 n 組序號 (無限次領取)，並建立在 `LuckyWheelDepositRewardInfo`
- 如果為不贈送，就把 `LuckyWheelDepositRewardInfo` 裡面 `未過期` 的序號，更改為過期

### 贈送序號
- 每 3 分鐘在 `Master - report server` 去掃 `一小時` 內完成的存款單，且這些存款單不存在 `LuckyWheelDepositGroupAccountApply` table 內，為待發放序號清單
- 這些存款自動發放的序號，發放時判斷活動能否允許序號重複 (單一會員)
- 每發放成功一筆，就把單號寫入 `LuckyWheelDepositGroupAccountApply`