shizhongqi/huahuibao_document @ 429ab7b4282e9dac6c064cf9fc8c231106b6fa9e

type: task project: 花掌柜 app:

hdApp status: doing year: 2026 module: 生日赠礼 tags:
task
生日赠礼 created: 2026-06-09 updated: 2026-06-09 sensitive: false ---

-- hdApp 客户生日赠礼模块 -- 执行前请备份相关表

-- 1. 会员等级扩展：折扣、生日权益、权益说明

ALTER TABLE `xhMemberLevel`
  ADD COLUMN `discount` decimal(4,2) NOT NULL DEFAULT 10.00 COMMENT '折扣，如9.5表示9.5折' AFTER `amount`,
  ADD COLUMN `birthdayBenefit` varchar(50) NOT NULL DEFAULT '' COMMENT '生日权益/礼品名称' AFTER `discount`,
  ADD COLUMN `benefitDesc` varchar(500) NOT NULL DEFAULT '' COMMENT '会员权益说明' AFTER `birthdayBenefit`;

-- 2. 生日赠礼流水（门店+客户+自然年唯一）

CREATE TABLE `xhBirthdayGift` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `mainId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商家主账号',
  `shopId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '门店',
  `customId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '客户',
  `hdId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '花店关系',
  `userId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户',
  `year` smallint(4) unsigned NOT NULL DEFAULT '0' COMMENT '活动年份',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0未填写 1待通知 2待领取 3领取中 4已领取 5超时放弃',
  `member` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '会员等级',
  `memberName` varchar(32) NOT NULL DEFAULT '' COMMENT '等级名称',
  `giftName` varchar(50) NOT NULL DEFAULT '' COMMENT '礼品名称快照',
  `birthdayMonth` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `birthdayDate` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `lunar` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `birthdayDisplay` varchar(32) NOT NULL DEFAULT '' COMMENT '展示用生日',
  `notifyTime` datetime DEFAULT NULL COMMENT '短信通知时间',
  `expireTime` datetime DEFAULT NULL COMMENT '领取截止时间',
  `pickupTime` datetime DEFAULT NULL COMMENT '预约到店时间',
  `collectTime` datetime DEFAULT NULL COMMENT '确认领取时间',
  `abandonTime` datetime DEFAULT NULL COMMENT '超时放弃时间',
  `claimToken` varchar(64) NOT NULL DEFAULT '' COMMENT '领取短链token',
  `shortLink` varchar(255) NOT NULL DEFAULT '' COMMENT '微信短链',
  `orderSn` varchar(32) NOT NULL DEFAULT '' COMMENT '小票订单号',
  `smsSent` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否已发短信',
  `createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_custom_shop_year` (`customId`,`shopId`,`year`),
  UNIQUE KEY `uk_claim_token` (`claimToken`),
  KEY `idx_uid_year` (`userId`,`year`),
  KEY `idx_shop_year_status` (`shopId`,`year`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COMMENT='生日赠礼记录';

2026-07-03 更新

ALTER TABLE `xhBirthdayGift` 
MODIFY COLUMN `smsSent` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '送达状态：0未送达 ，1已送达';

birthday_gift.md 3.1 KB History Raw

birthday_gift.md 3.1 KB

History Raw