小程序·刮刮卡抽奖简单设计

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

CREATE TABLE `mini_user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '微信小程序用户表id',
  `unionid` varchar(50) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '用户在开放平台的唯一标识',
  `openid` varchar(50) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '用户在某个小程序的唯一标识',
  `session_key` varchar(50) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '小程序会话密钥',
  `nickname` varchar(30) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '用户昵称',
  `avatar_url` varchar(255) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '用户头像',
  `handset` varchar(30) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '手机号（国外手机号会有区号）',
  `country_code` varchar(10) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '手机区号,',
  `last_login_at` datetime DEFAULT NULL COMMENT '最后登录时间',
  `last_login_ip` varchar(50) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '最后登录IP',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `invite_staff_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '最新邀请员工ID',
  `invite_at` datetime DEFAULT NULL COMMENT '最新邀请时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_unionid` (`unionid`),
  KEY `index_created_at` (`created_at`),
  KEY `i_openid` (`openid`),
  KEY `i_handset` (`handset`),
  KEY `idx_mini_user_invite_staff_id` (`invite_staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

CREATE TABLE `mini_callback` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '微信小程序回调记录表id',
  `msg_type` varchar(20) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '',
  `event` varchar(50) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '',
  `change_type` varchar(50) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '',
  `data` text COLLATE utf8mb4_0900_as_cs COMMENT 'query body',
  `query_string` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs COMMENT 'URL query string',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `i_me` (`msg_type`,`event`),
  KEY `i_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

CREATE TABLE `mini_lottery_activity` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '活动表id',
  `name` varchar(256) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '活动名称',
  `start_at` datetime DEFAULT NULL COMMENT '开始时间，null则是没时间限制',
  `end_at` datetime DEFAULT NULL COMMENT '结束时间，null则是没时间限制',
  `rule` text COLLATE utf8mb4_0900_as_cs NOT NULL COMMENT '活动规则',
  `staff_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '操作员工ID',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

CREATE TABLE `mini_lottery_prize` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '奖品表ID',
  `activity_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '归属活动ID',
  `name` varchar(100) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '奖品名称',
  `image` json DEFAULT NULL COMMENT '奖品图片',
  `probability` bigint NOT NULL DEFAULT '0' COMMENT '中奖概率（百分号前面的数字*100）',
  `is_default` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否作为兜底奖品，0-否，1-是',
  `total` bigint NOT NULL DEFAULT '0' COMMENT '奖品总数',
  `surplus` bigint NOT NULL DEFAULT '0' COMMENT '剩余奖品数量',
  `is_enable` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否上架',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mini_lottery_prize_activity_id` (`activity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

CREATE TABLE `mini_lucky_code_all` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '全量抽奖码表id，方便表mini_lucky_code生成不连串的抽奖码',
  `code` char(6) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '抽奖码，如AA1234',
  `is_enable` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否可用，0-已被用过，1-可用',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

CREATE TABLE `mini_lucky_code` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '抽奖码表id',
  `activity_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '归属活动ID',
  `code` char(6) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '抽奖码',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `batch` varchar(30) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '批次',
  `is_enable` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否可用，0-已被用过，1-可用',
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_code` (`code`),
  KEY `i_batch` (`batch`),
  KEY `i_ac` (`activity_id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

CREATE TABLE `mini_lottery_record` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '用户抽奖记录表id',
  `activity_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '归属活动ID',
  `user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT 'mini_user表ID',
  `prize_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '中奖奖品ID',
  `lucky_code` varchar(256) COLLATE utf8mb4_0900_as_cs NOT NULL DEFAULT '' COMMENT '抽奖码',
  `status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '抽奖状态，0-未抽奖，1-未中奖，2-待兑奖，3-已兑奖，4-已过期',
  `draw_at` datetime DEFAULT NULL COMMENT '抽奖时间',
  `remark` varchar(200) COLLATE utf8mb4_0900_as_cs DEFAULT '' COMMENT '备注',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '获取抽奖机会时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `invite_staff_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '邀请员工ID',
  PRIMARY KEY (`id`),
  KEY `idx_mini_lottery_record_user_id` (`user_id`),
  KEY `idx_mini_lottery_record_lucky_code` (`lucky_code`),
  KEY `idx_mini_lottery_record_out_id` (`out_id`),
  KEY `i_au` (`activity_id`,`user_id`),
  KEY `idx_mini_lottery_record_invite_staff_id` (`invite_staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

CREATE TABLE `mini_lottery_user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '抽奖用户统计表id',
  `activity_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '归属活动ID',
  `user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT 'mini_user表ID',
  `surplus_num` bigint NOT NULL DEFAULT '0' COMMENT '剩余抽奖次数(总抽奖机会=抽奖次数+剩余抽奖次数)',
  `draw_num` bigint NOT NULL DEFAULT '0' COMMENT '抽奖次数',
  `win_num` bigint NOT NULL DEFAULT '0' COMMENT '中奖次数',
  `award_num` bigint NOT NULL DEFAULT '0' COMMENT '兑奖次数',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_au` (`activity_id`,`user_id`),
  KEY `idx_mini_lottery_user_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

CREATE TABLE `mini_lottery_report` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '抽奖数据统计报表ID',
  `activity_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '活动ID',
  `report_at` date DEFAULT NULL COMMENT '统计日期',
  `player_num` bigint NOT NULL DEFAULT '0' COMMENT '参与人数',
  `draw_num` bigint NOT NULL DEFAULT '0' COMMENT '抽奖次数',
  `winner_num` bigint NOT NULL DEFAULT '0' COMMENT '中奖人数',
  `win_num` bigint NOT NULL DEFAULT '0' COMMENT '中奖次数',
  `awarder_num` bigint NOT NULL DEFAULT '0' COMMENT '兑奖人数',
  `award_num` bigint NOT NULL DEFAULT '0' COMMENT '兑奖次数',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_ar` (`activity_id`,`report_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

var activityId uint64 = 1 // 抽奖活动ID，支持多个抽奖活动的代码复用

// 抽奖参与状态
type LotteryStatus uint8
const (
    LotteryStatusDefault LotteryStatus = iota // 未抽奖
    LotteryStatusLose                         // 未中奖
    LotteryStatusWait                         // 已中奖-待兑奖
    LotteryStatusSuccess                      // 已中奖-已兑奖
    LotteryStatusExpired                      // 已中奖-已过期
)

// 最大概率值，百分号前面的数字*100，兼容2位小数，如30.22%）
const MaxPrizeProbability = 10000

// 未中奖设置
const (
    LosePrizeId       uint64 = 0                                  // 未中奖奖品ID
    LosePrizeName            = "未中奖"                              // 名称
    LosePrizeImageUrl        = "https://xxx/weizhongjiangtmp.jpg" // 未中奖图片
)

// 奖品概率范围
type PrizeProbabilityRange struct {
    Min int
    Max int
}

// 获取抽奖结果
func Result() (uint64, error) {
    // todo 此处先加上抽奖用户个人的分布式互斥锁，避免该用户同时触发多次抽奖

    // 奖品判断
    var (
        currentProbabilityTotal  int                                      // 当前奖品总概率
        originalProbabilityTotal int                                      // 初始奖品总概率
        isDefault                bool                                     // 是否有兜底奖品
        defaultPrizeId           = LosePrizeId                            // 兜底奖品ID或未中奖ID
        surplusTotal             int64                                    // 总剩余奖品数量
        prizeMap                 = make(map[uint64]PrizeProbabilityRange) // 奖品概率范围
        winPrizeId               uint64                                   // 中奖奖品ID
    )
    prizes := dao.MiniLotteryPrizeDao.GetList(activityId) // 获取活动的所有上架奖品列表
    if len(prizes) == 0 {
        return winPrizeId, errors.New("尚未配置奖池")
    }
    for _, v := range prizes {
        // 兜底奖品设置的 奖品概率 和 奖品数量 直接忽略，当做未中奖项处理
        if v.IsDefault {
            isDefault = true
            defaultPrizeId = v.Id
            continue
        }

        originalProbabilityTotal += v.Probability

        // 数量为零的奖品不参与抽奖
        if v.Surplus <= 0 {
            continue
        }

        surplusTotal += v.Surplus
        min := currentProbabilityTotal
        currentProbabilityTotal += v.Probability
        max := currentProbabilityTotal - 1
        prizeMap[v.Id] = PrizeProbabilityRange{
            Min: min,
            Max: max,
        }
    }
    if currentProbabilityTotal > MaxPrizeProbability {
        return winPrizeId, errors.New("奖池设置异常，暂无法抽奖")
    }
    if originalProbabilityTotal == MaxPrizeProbability && currentProbabilityTotal != originalProbabilityTotal {
        // 如果设置了多个奖品的总中奖概率为100%，则必须设置其中一个奖品为兜底奖品，否则其中一个奖品数量变为0时，无法继续抽奖，这种属于运营操作错误
        return winPrizeId, errors.New("奖池设置异常，暂无法抽奖哟")
    }
    if currentProbabilityTotal < MaxPrizeProbability {
        prizeMap[defaultPrizeId] = PrizeProbabilityRange{
            Min: currentProbabilityTotal,
            Max: MaxPrizeProbability - 1,
        }
    }
    if !isDefault && surplusTotal <= 0 {
        return winPrizeId, errors.New("来的太晚啦，奖品已经派完")
    }

    // 抽奖
    rand.Seed(time.Now().UnixNano())
    random := rand.Intn(MaxPrizeProbability)
    for prizeId, v := range prizeMap {
        if v.Min <= random && v.Max >= random {
            winPrizeId = prizeId
            break
        }
    }

    // todo 此处先加上全局的分布式互斥锁，避免高并发时奖品库存超额

    // 再次确认中奖奖品是否有库存，没有库存则设置为兜底奖品或未中奖
    if winPrizeId > 0 && winPrizeId != defaultPrizeId {
        prize := dao.MiniLotteryPrizeDao.GetById(winPrizeId)
        if prize.Surplus <= 0 || !prize.IsEnable {
            winPrizeId = defaultPrizeId
        }
    }
    return winPrizeId, nil
}

// 获取所有奖品
type (
    MiniLotteryPrizeImage struct {
        Win  string `json:"win"`  // 获奖展示图片
        Word string `json:"word"` // 带文字的图片
    }

    MiniLotteryActivityPrize struct {
        Id    uint64                 `json:"id"`
        Name  string                 `json:"name"`
        Image *MiniLotteryPrizeImage `json:"image"`
    }
)
func GetPrizes(activityId uint64) (MiniLotteryActivityPrize, error) {
    var (
        isDefault        bool // 是否有兜底奖品
        probabilityTotal int // 奖品中奖总概率
        resp = make([]MiniLotteryActivityPrize, 0)
    )
    prizes := dao.MiniLotteryPrizeDao.GetList(activityId)
    if len(prizes) == 0 {
        return resp, errors.New("尚未配置奖池")
    }
    for _, v := range prizes {
        probabilityTotal += v.Probability
        if v.IsDefault {
            isDefault = true
        }
        resp = append(resp, MiniLotteryActivityPrize{
            Id:    v.Id,
            Name:  v.Name,
            Image: v.Image,
        })
    }
    // 判断是否有未中奖的情况，有则追加上未中奖数据
    if !isDefault && probabilityTotal < MaxPrizeProbability {
        resp = append(resp, MiniLotteryActivityPrize{
            Id:   LosePrizeId,
            Name: LosePrizeName,
            Image: &MiniLotteryPrizeImage{
                Win:  LosePrizeImageUrl,
                Word: LosePrizeImageUrl,
            },
        })
    }
    return resp, nil
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

type MiniLuckyCodeAll struct {
    Id        uint64          `gorm:"comment:全量抽奖码表id;" json:"id"`
    Code      string          `gorm:"type:char(6);not null;default:'';comment:抽奖码;uniqueIndex:u_code;" json:"code"`
    IsEnable  bool            `gorm:"not null;default:1;comment:是否可用，0-已被用过，1-可用" json:"is_use"`
}

// 脚本一次性生成所需抽奖码入库
func GenerateLotteryLuckyCode() {
    var (
        prefix        = "AA" // 抽奖码总共6位，预先设定前两位数
        str           = "ABCDEFGHIJKLMNPQRSTUVWXYZ123456789" // 去掉容易混淆的字母O和数字0
        bytes         = []byte(str)
        luckCodeSlice = make([]MiniLuckyCodeAll, 0)
        startAt       = time.Now()
    )
    for _, v1 := range bytes {
        for _, v2 := range bytes {
            for _, v3 := range bytes {
                for _, v4 := range bytes {
                    var code = []byte{v1, v2, v3, v4}
                    luckCodeSlice = append(luckCodeSlice, MiniLuckyCodeAll{
                        Code:     prefix + string(code),
                        IsEnable: true,
                    })
                }
            }

            // 一次性入库一千多个抽奖码
            if err := dao.MiniLuckyCodeDao.BatchCreateLuckyCodeAll(luckCodeSlice); err != nil {
                logger.Sugar.Error(err)
                break
            }

            luckCodeSlice = luckCodeSlice[:0]
        }
    }
    logger.Sugar.Infof("开始时间：%s", startAt.Format("2006-01-02 15:04:05"))
    logger.Sugar.Infof("结束时间：%s", time.Now().Format("2006-01-02 15:04:05"))
}

1
2
3
4

SELECT *
FROM `mini_lucky_code_all`
WHERE is_enable = 1
ORDER BY RAND() LIMIT 10000