huahuibao/sql.txt

2019.12.17 shish 商家流水相关表修改 ！！！
UPDATE `xhMerchantCapital` SET `relateId`=0;！！！！！！这条要使用php来转换
ALTER TABLE `xhMerchantCapital` MODIFY `relateId` INT NOT NULL DEFAULT 0 COMMENT '关联id；订单id';
UPDATE `xhUserGrowth` SET `relateId`=0;！！！！！！这条要使用php来转换
ALTER TABLE `xhUserGrowth` MODIFY `relateId` int NOT NULL DEFAULT 0 COMMENT '关联id';
UPDATE `xhUserCapital` SET `relateId`=0;！！！！！！这条要使用php来转换
ALTER TABLE `xhUserCapital` MODIFY `relateId` int NOT NULL DEFAULT 0 COMMENT '关联id';
ALTER TABLE xhUsage ADD `inTurn` INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER `usageName`;

2019.12.17 shish 用途相关的统计
drop table if exists `xhStatIncomeUsage`;
CREATE TABLE if not exists `xhStatIncomeUsage` (
  `id` int(11) NOT NULL auto_increment primary key,
  `merchantId` int(11) NOT NULL DEFAULT '0' COMMENT '商家id',
  `shopId` int not null default 0 comment '门店id,0表示商家所有门店',
  `time` int(11) NOT NULL DEFAULT '0' COMMENT '时间',
  `usageId` tinyint(4) NOT NULL DEFAULT '0' COMMENT '用途id；UsageRelation id',
  `amount` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '收入金额',
  `weixin` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '微信收入',
  `alipay` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '支付宝收入',
  `balancePay` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '余额付款收入',
  `cash` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '现金收入',
  `totalAmount` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '总收入金额',
  `addTime` int(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
) COMMENT='按用途统计每天收入';
drop table if exists `xhStatIncomeUsageMonth`;
CREATE TABLE if not exists `xhStatIncomeUsageMonth` (
  `id` int(11) NOT NULL auto_increment primary key,
  `year` smallint(6) NOT NULL DEFAULT '0' COMMENT '年',
  `month` tinyint(4) NOT NULL DEFAULT '1' COMMENT '月份',
  `usageId` tinyint(4) NOT NULL DEFAULT '0' COMMENT '用途id',
  `amount` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '收入金额',
  `weixin` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '微信收入',
  `alipay` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '支付宝收入',
  `balancePay` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '余额付款收入',
  `cash` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '现金收入',
  `merchantId` int(11) NOT NULL DEFAULT '0' COMMENT '商家id',
`shopId` int not null default 0 comment '门店id,0表示商家所有门店',
  `addTime` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '添加时间'
) COMMENT='按用途统计每月收入';
ALTER TABLE `xhMerchantCapital` MODIFY shopId INT NOT NULL DEFAULT 0 COMMENT '0表示商家所有门店';
ALTER TABLE `xhOrder` ADD `classify` TINYINT NOT NULL DEFAULT 0 COMMENT '是否归类 0未归类 1已归类' AFTER `usageId`;

2019.12.15 16:30 shish 数据要重新更新过去！！！！！
ALTER TABLE xhOrder ADD `reachDate` DATE DEFAULT NULL COMMENT '客户要求送到的日期' AFTER `sendDistance`;
ALTER TABLE xhOrder DROP COLUMN `reachTime`;
ALTER TABLE xhOrder MODIFY `reachPeriod` TINYINT NOT NULL DEFAULT 0 COMMENT '客户要求送到的时段：0上午1下午2晚上';
ALTER TABLE xhOrder ADD sendTip DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '运费里增加的小费' AFTER `sendCost`;
ALTER TABLE xhOrder MODIFY currentFlow TINYINT NOT NULL DEFAULT 0 COMMENT '完成流程数；当前流程数';

2019.12.15 11:57 刮刮卡表名变更
RENAME TABLE xhGuaGua TO xhGgk;
RENAME TABLE xhGuaGuaAttend TO xhGgkAttend;
RENAME TABLE xhGuaGuaPrize TO xhGgkPrize;
RENAME TABLE xhGuaGuaWin TO xhGgkWin;
ALTER TABLE xhOrder CHANGE `modPriceStatus` `modPrice` TINYINT NOT NULL DEFAULT 1 COMMENT '0不能修改价格 1可以修改';
ALTER TABLE xhOrder DROP COLUMN `getCouponQrCode`;
alter table xhOrder AUTO_INCREMENT=263810;
ALTER TABLE xhOrder MODIFY `orderSn` CHAR(40) NOT NULL DEFAULT '' COMMENT '订单编号';
ALTER TABLE xhOrder MODIFY `receiveAddress` VARCHAR(300) NOT NULL DEFAULT '' COMMENT '收货地址 详细街道地址';
ALTER TABLE xhOrder MODIFY `receiveFullAddress` VARCHAR(400) NOT NULL DEFAULT '' COMMENT '完整收货地址';
ALTER TABLE xhOrder MODIFY `receiveFloor` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '收货地址 楼号门牌号' AFTER `receiveAddress`;
ALTER TABLE xhOrder MODIFY `sendType` TINYINT NOT NULL DEFAULT 0 COMMENT '配送方式 0免配送 1自己送 2别人送，快递送';
ALTER TABLE xhOrder ADD `sendSide` TINYINT NOT NULL DEFAULT 0 COMMENT '配送方 0没有 1达达' AFTER `sendType`;
ALTER TABLE xhOrder MODIFY `sendStatus` TINYINT NOT NULL DEFAULT 0 COMMENT '配送状态：0未发货，待接单 1已发货，已接单 2已送达' AFTER `sendSide`;

2019.12.15 shish 优惠券
ALTER TABLE xhCoupon MODIFY `orderId` INT NOT NULL DEFAULT 0 COMMENT '优惠券使用的订单id';
ALTER TABLE xhCoupon CHANGE `getType` `source` TINYINT NOT NULL DEFAULT 0 COMMENT '来源 0新人领券 1邀请有礼领取';
ALTER TABLE xhCoupon ADD sourceId INT NOT NULL DEFAULT 0 COMMENT '来源目标id' AFTER `source`;
ALTER TABLE xhInvite ADD introMobile VARCHAR(15) NOT NULL DEFAULT '' COMMENT '介绍人手机；邀请人手机' AFTER `introUserId`;
ALTER TABLE `xhCoupon` DROP COLUMN `introUserId`;
ALTER TABLE xhInvite ADD introUserName VARCHAR(30) NOT NULL DEFAULT '' COMMENT '邀请人昵称' AFTER `userId`;

2019.12.14 shish
DELETE FROM `xhOrderSend`;
ALTER TABLE `xhOrderSend` MODIFY `orderId` INT NOT NULL DEFAULT 0 COMMENT '订单id';

2019.12.14 shish 订单表的优化 ！！！要写php更新 xhOrder xhOrderGoods
ALTER TABLE `xhOrder` DROP PRIMARY KEY;
ALTER TABLE `xhOrder` DROP  INDEX `id`;
ALTER TABLE `xhOrder` CHANGE `id` `orderSn` CHAR(32) NOT NULL DEFAULT '' COMMENT '订单编号';
ALTER TABLE `xhOrder` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自增id' FIRST;

2019.12.13 shish 流程数增加
ALTER TABLE xhOrder ADD totalFlow TINYINT NOT NULL DEFAULT 5 COMMENT '总流程数' AFTER `evaluate`;
ALTER TABLE xhOrder ADD currentFlow TINYINT NOT NULL DEFAULT 0 COMMENT '当前流程数' AFTER `totalFlow`;
UPDATE xhOrder SET totalFlow=5,currentFlow=5;

2019.12.13 23:02 shish
DROP TABLE IF EXISTS `xhOrderSend`;
CREATE TABLE IF NOT EXISTS `xhOrderSend`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`orderId` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '订单号',
`actionId` TINYINT NOT NULL DEFAULT 0 COMMENT '动作id',
`option` TINYINT NOT NULL DEFAULT 0 COMMENT '动作的选项',
finishTime INT NOT NULL DEFAULT 0 COMMENT '完成时间',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='发货管理';
ALTER TABLE xhOrderSend ADD inTurn INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER `finishTime`;
ALTER TABLE xhOrderSend MODIFY `orderId` CHAR(32) NOT NULL DEFAULT '' COMMENT '订单号';
ALTER TABLE xhOrderGoods MODIFY `orderId` CHAR(32) NOT NULL DEFAULT '' COMMENT '订单号';
ALTER TABLE xhOrder MODIFY `id` CHAR(32) NOT NULL DEFAULT '' COMMENT '订单号';

2019.12.13 shish
ALTER TABLE xhWxScene MODIFY `type` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '场景类型 0刮刮卡 1邀请有礼领券';
ALTER TABLE xhShop ADD `openTime` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '营业时间' AFTER `telephone`;
ALTER TABLE `xhAd` DROP INDEX `merchantId`;
ALTER TABLE `xhAd` ADD INDEX `merchantId`(`merchantId`,`type`);
ALTER TABLE `xhAd` ADD INDEX `multiple`(`merchantId`,`type`,`status`,`startTime`,`endTime`);
DELETE FROM `xhAd`;
ALTER TABLE `xhAd` MODIFY `startTime` INT NOT NULL DEFAULT 0 COMMENT '开始时间';
ALTER TABLE `xhAd` MODIFY `endTime` INT NOT NULL DEFAULT 0 COMMENT '结束时间；方便查询，永不过期情况，使用 开始值0 结束值4102416000即2100-01-01 00:00:00';
ALTER TABLE `xhAd` DROP COLUMN `forever`;
ALTER TABLE xhShop MODIFY shopAddress VARCHAR(200) NOT NULL DEFAULT '' COMMENT '街道地址';
ALTER TABLE xhShop ADD shopFullAddress VARCHAR(500) NOT NULL DEFAULT '' COMMENT '完整地址' AFTER shopAddress;
ALTER TABLE xhShop DROP COLUMN `status`;

2019.12.12 shish 我的代理与申请试用
DROP TABLE IF EXISTS `xhAgentLevel`;
CREATE TABLE IF NOT EXISTS xhAgentLevel(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '代理名称',
`commission` INT NOT NULL DEFAULT 0 COMMENT '佣金',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='代理等级';
DROP TABLE IF EXISTS `xhApply`;
CREATE TABLE IF NOT EXISTS `xhApply`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '花店名称',
`mobile` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
`userId` INT NOT NULL DEFAULT 0 COMMENT '花卉宝用户id',
`oldId` INT NOT NULL DEFAULT 0 COMMENT '老花店id；介绍人',
`fee` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '付费金额',
`income` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '介绍费',
`newId` INT NOT NULL DEFAULT 0 COMMENT '新花店id，0表示还没有审核通过',
`longitude` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '花店经度',
`latitude` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '花店纬度',
`province` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '省',
`city` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '市',
`dist` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '区县',
`address` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '详细街道地址',
`floor` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '楼号门牌号',
`fullAddress` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '地图中获取完整地址',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0审核中 1试用中 2已付费 3已失效',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='申请试用';

2019.12.12 shish 更新所有的会员等级
update xhMerchantExtend set gradeList='1_2000_0.9I2_5000_0.88I3_10000_0.85I4_20000_0.8';

2019.12.12 shish 订单评价
ALTER TABLE xhOrder ADD `grade` TINYINT NOT NULL DEFAULT 0 COMMENT '整体评分' AFTER `printStatus`;
ALTER TABLE xhOrder ADD `gradeService` TINYINT NOT NULL DEFAULT 0 COMMENT '服务态度评分' AFTER `grade`;
ALTER TABLE xhOrder ADD `gradeQuality` TINYINT NOT NULL DEFAULT 0 COMMENT '鲜花品质评分' AFTER `gradeService`;
ALTER TABLE xhOrder ADD `gradeSend` TINYINT NOT NULL DEFAULT 0 COMMENT '配送服务评分' AFTER `gradeQuality`;
ALTER TABLE xhOrder ADD `evaluate` VARCHAR(800) NOT NULL DEFAULT '' COMMENT '评语' AFTER `gradeSend`;

2019.12.11 shish 邀请有礼表结构确定
DROP TABLE IF EXISTS `xhInvite`;
CREATE TABLE IF NOT EXISTS `xhInvite`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
`userName` varchar(50) NOT NULL DEFAULT '' COMMENT '邀请人的昵称',
mobile VARCHAR(20) NOT NULL DEFAULT '' COMMENT '邀请人手机号',
userId INT NOT NULL DEFAULT 0 COMMENT '受邀人',
introUserId INT NOT NULL DEFAULT 0 COMMENT '介绍人',
couponId INT NOT NULL DEFAULT 0 COMMENT '优惠券id',
prize VARCHAR(50) NOT NULL DEFAULT '' COMMENT '介绍人的奖励',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待下单 1待兑奖 2已完成',
orderTime INT NOT NULL DEFAULT 0 COMMENT '下单时间',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='邀请有礼记录';

DROP TABLE IF EXISTS xhInviteAsset;
CREATE TABLE IF NOT EXISTS `xhInviteAsset`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
switch TINYINT NOT NULL DEFAULT 1 COMMENT '是否开启邀请有礼 0关闭 1开启。默认开启',
amount INT NOT NULL DEFAULT 0 COMMENT '新人领券金额',
miniCost INT NOT NULL DEFAULT 0 COMMENT '最低消费',
duration SMALLINT NOT NULL DEFAULT 0 COMMENT '有效时长，天数',
prize VARCHAR(50) NOT NULL DEFAULT '' COMMENT '介绍人的奖励',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='商家邀请有礼设置';

DROP TABLE IF EXISTS `xhInviteStat`;
CREATE TABLE IF NOT EXISTS `xhInviteStat`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
userId INT NOT NULL DEFAULT 0 COMMENT '',
inviteNum INT NOT NULL DEFAULT 0 COMMENT '邀请数',
orderNum INT NOT NULL DEFAULT 0 COMMENT '下单数',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='邀请统计';

2019.12.11 shish 领券
ALTER TABLE xhCoupon MODIFY getType TINYINT NOT NULL DEFAULT 0 COMMENT '获取方式，0新人领券 1邀请领券';
DROP TABLE IF EXISTS `xhCouponAsset`;
CREATE TABLE IF NOT EXISTS `xhCouponAsset`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
totalNum INT NOT NULL DEFAULT 0 COMMENT '优惠券总数',
unUseNum INT NOT NULL DEFAULT 0 COMMENT '未使用数',
useNum INT NOT NULL DEFAULT 0 COMMENT '已使用数',
invalidNum INT NOT NULL DEFAULT 0 COMMENT '失效数',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='优惠券资产与设置';

2019.12.10 shish 角色相关
ALTER TABLE xhAdminRole ADD `num` INT NOT NULL DEFAULT 0 COMMENT '成员数' AFTER `merchantId`;
CREATE TABLE xhAdminLog(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`ip` BIGINT NOT NULL DEFAULT 0 COMMENT 'ip地址',
`merchantId` INT NOT NULL DEFAULT 0 COMMENT '商家id',
`content` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '操作内容',
adminId INT NOT NULL DEFAULT 0 COMMENT '员工id；管理员id',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='员工操作日志';

2019.12.9 shish 员工相关
ALTER TABLE xhAdmin DROP COLUMN `openId`;
ALTER TABLE xhAdmin DROP COLUMN `unionId`;
ALTER TABLE xhAdmin DROP COLUMN `subscribe`;
ALTER TABLE xhAdmin DROP COLUMN `wxQrCode`;
ALTER TABLE xhAdmin MODIFY `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态 0停用 1正常';
ALTER TABLE xhAdmin ADD merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id' AFTER `roleId`;
DROP TABLE IF EXISTS `xhAdminToMerchant`;
ALTER TABLE xhAdmin ADD userId INT NOT NULL DEFAULT 0 COMMENT '员工关联的userId' AFTER merchantId;
ALTER TABLE xhAdmin ADD `addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间' AFTER `status`;
ALTER TABLE xhAdmin DROP COLUMN `createTime`;
ALTER TABLE xhAdmin DROP COLUMN `updateTime`;
ALTER TABLE xhAdmin DROP COLUMN `province`;
ALTER TABLE xhAdmin DROP COLUMN `city`;
ALTER TABLE xhAdmin DROP COLUMN `dist`;
ALTER TABLE xhAdmin DROP COLUMN `address`;
ALTER TABLE xhAdmin DROP COLUMN `addLong`;
ALTER TABLE xhAdmin DROP COLUMN `addLat`;
ALTER TABLE xhAdmin DROP COLUMN `addPrecision`;
ALTER TABLE xhAdmin ADD incomeNotice TINYINT NOT NULL DEFAULT 0 COMMENT '收入通知 0不需要 1需要' AFTER `currentShopId`;
ALTER TABLE xhAdminRole ADD `addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间' AFTER `merchantId`;
ALTER TABLE xhAdminRole DROP COLUMN `createTime`;
ALTER TABLE xhAdminRole DROP COLUMN `updateTime`;
ALTER TABLE xhAdmin DROP COLUMN `avatar`;
ALTER TABLE xhAdmin ADD loginTime INT NOT NULL DEFAULT 0 COMMENT '最后登陆时间' AFTER `status`;
ALTER TABLE xhAdmin ADD `super` TINYINT NOT NULL DEFAULT 0 COMMENT '是否超级管理员 0不是 1是，超级员工不可删除' AFTER `sex`;

2019.12.8 23：06 shish 商品设置
ALTER TABLE xhGoodsSetting DROP COLUMN `startRiseTime`;
ALTER TABLE xhGoodsSetting DROP COLUMN `endRiseTime`;
ALTER TABLE xhGoodsSetting CHANGE `goodSIntroduce` `goodsIntroduce` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '商品说明';
ALTER TABLE xhGoodsSetting DROP COLUMN `createTime`;
ALTER TABLE xhGoodsSetting DROP COLUMN `updateTime`;
ALTER TABLE xhMerchantExtend DROP COLUMN `riseType`;
ALTER TABLE xhMerchantExtend DROP COLUMN `riseAmount`;
ALTER TABLE xhMerchantExtend DROP COLUMN `createTime`;
ALTER TABLE xhMerchantExtend DROP COLUMN `updateTime`;
ALTER TABLE xhGoodsSetting ADD riseSwitch TINYINT NOT NULL DEFAULT 1 COMMENT '自动涨价开关 1开启 0关闭' AFTER `merchantId`;

2019.12.8 shish 图片
DROP TABLE IF EXISTS xhPic;
CREATE TABLE IF NOT EXISTS xhPic(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
img VARCHAR(500) NOT NULL DEFAULT '' COMMENT '图片地址',
price DECIMAL(9,2) NOT NULL DEFAULT 0.00 COMMENT '价格',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品名称',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='图库；商品图库';
DROP TABLE IF EXISTS xhPicCategory;
CREATE TABLE IF NOT EXISTS xhPicCategory(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
pictureId INT NOT NULL DEFAULT 0 COMMENT '图片id',
categoryId INT NOT NULL DEFAULT 0 COMMENT '分类id'
)COMMENT='商品图与分类关系';
ALTER TABLE xhPic ADD inTurn INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER `name`;
ALTER TABLE xhPicCategory ADD inTurn INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER categoryId;
ALTER TABLE xhPicCategory CHANGE pictureId picId INT NOT NULL DEFAULT 0 COMMENT '图片id';
ALTER TABLE xhGoodsCategory ADD `status` TINYINT NOT NULL DEFAULT 1 COMMENT '上下架状态 0下架 1正常' AFTER `merchantId`;
ALTER TABLE xhCategory ADD goodsNum INT NOT NULL DEFAULT 0 COMMENT '商品数' AFTER `cover`;
ALTER TABLE xhCategory ADD sold INT NOT NULL DEFAULT 0 COMMENT '销量' AFTER `goodsNum`;
ALTER TABLE xhCategory CHANGE cover `img` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '分类图片';

2019.12.7 shizhongqi 广告管理
ALTER TABLE xhAd ADD `status` TINYINT NOT NULL DEFAULT 0 COMMENT '启用状态 0 未启用 1 启用' AFTER `adName`;
ALTER TABLE xhAd ADD `inTurn` INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER `status`;
ALTER TABLE xhAd ADD `viewTimes` INT NOT NULL DEFAULT 0 COMMENT '点击次数' AFTER `adImg`;
ALTER TABLE `xhAd` CHANGE `startTime` `startTime` DATETIME NULL COMMENT '开始时间', CHANGE `endTime` `endTime` DATETIME NULL COMMENT '结束时间';
ALTER TABLE xhAd ADD `forever` TINYINT NOT NULL DEFAULT 0 COMMENT '永久有效：0 未启用 1 永久有效' AFTER `goodsId`;

2019.12.6 shish
增加xhRegion表结构和数据！！
ALTER TABLE xhOrder MODIFY `payStatus` TINYINT NOT NULL DEFAULT 0 COMMENT '0待付款 1已付款';
RENAME TABLE xhUserIntegral TO xhUserGrowth;
ALTER TABLE xhUserGrowth CHANGE `integral` `growth` INT NOT NULL DEFAULT 0 COMMENT '当前成长值';
ALTER TABLE xhUserGrowth MODIFY `num` INT NOT NULL DEFAULT 0 COMMENT '增加或减少的成长值';
ALTER TABLE xhOrder DROP COLUMN `payStyle`;
ALTER TABLE xhGoods MODIFY `status` TINYINT NOT NULL DEFAULT 1 COMMENT '商品状态 0下架 1正常';
ALTER TABLE xhGoods DROP COLUMN `delStatus`;
ALTER TABLE xhGoodsCategory ADD INDEX `gId`(`gId`);
ALTER TABLE xhGoods ADD autoRise TINYINT NOT NULL DEFAULT 1 COMMENT '自动涨价 0不需要 1需要' AFTER `goodsStyle`;
ALTER TABLE `xhGoodsStyle` CHANGE `title` `styleName` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '款式名称';

2019.12.5 shish
ALTER TABLE xhOrderGoods CHANGE `multipleTitle` `goodsStyleName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '款式名称';
ALTER TABLE xhOrderGoods CHANGE `multiPriceId` `goodsStyleId` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '款式id';
ALTER TABLE xhCoupon MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0未使用 1使用 2过期';
ALTER TABLE xhOrder DROP COLUMN `luckyNum`;
ALTER TABLE xhOrder DROP COLUMN `uniCode`;
ALTER TABLE xhOrder DROP COLUMN `useType`;

2019.12.4 11:44 shish
ALTER TABLE xhShop CHANGE `businessName` `shopName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '门店名称';
ALTER TABLE xhShop CHANGE `branchName` `merchantName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商家名称';
ALTER TABLE xhShop DROP COLUMN `poiId`;
ALTER TABLE xhMerchantExtend ADD `freight` CHAR(10) NOT NULL DEFAULT '5_2' COMMENT '运费计算方式 5公里内免运费，超过每增加一公里+2' AFTER `alipayAccountName`;
ALTER TABLE xhMerchantExtend DROP COLUMN `giveCoupon`;
ALTER TABLE xhMerchantExtend DROP COLUMN `giveCouponPrice`;
ALTER TABLE xhMerchantExtend DROP COLUMN `couponMeetPrice`;
ALTER TABLE xhMerchantExtend DROP COLUMN `couponValidity`;
ALTER TABLE xhMerchantExtend ADD newCouponSwitch TINYINT NOT NULL DEFAULT 1 COMMENT '新人送优惠券 0未开启 1开启' AFTER `freight`;
ALTER TABLE xhMerchantExtend ADD newCouponAmount DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '新人优惠券金额' AFTER `newCouponSwitch`;
ALTER TABLE xhMerchantExtend ADD newCouponMiniCost DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '新人优惠券最低消费金额' AFTER `newCouponAmount`;
ALTER TABLE xhMerchantExtend ADD newCouponValidTime INT NOT NULL DEFAULT 0 COMMENT '新人优惠券有效期，时长为秒，0永不过期' AFTER `newCouponMiniCost`;
ALTER TABLE xhGoods DROP COLUMN `isCharge`;
ALTER TABLE xhGoods DROP COLUMN `sendArea`;
ALTER TABLE xhGoods CHANGE `sendType` needSend TINYINT NOT NULL DEFAULT 0 COMMENT '是否要配送 0免配送 1需要配送';
ALTER TABLE xhOrder CHANGE `needSend` `sendType` TINYINT NOT NULL DEFAULT 0 COMMENT '配送方式 0免配送 1自己送 2别人送';
ALTER TABLE xhOrder MODIFY reachPeriod TINYINT NOT NULL DEFAULT 0 COMMENT '0上午1下午2晚上';
ALTER TABLE xhOrder DROP COLUMN `blessing`;
ALTER TABLE xhOrder ADD cardInfo VARCHAR(500) NOT NULL DEFAULT '' COMMENT '贺卡内容' AFTER `needCard`;
ALTER TABLE xhOrder MODIFY `payWay` TINYINT NOT NULL DEFAULT 0 COMMENT '支付方式 0微信 1支付宝 2余额';

2019.12.4 shish
!!执行下面的sql，全部的会员折扣要纠正
UPDATE xhUserAsset SET `discount`=1;
ALTER TABLE xhUserAsset MODIFY discount FLOAT(3,2) NOT NULL DEFAULT 1.00 COMMENT '折扣';
ALTER TABLE xhCoupon CHANGE `meetAmount` `miniCost` INT NOT NULL DEFAULT 0 COMMENT '最低消费金额，0表示任何情况下可用';

！！国兰的商品款式表要给对应上，注意xhGoods表里的goodsStyle也要改
ALTER TABLE xhGoods CHANGE `style` `goodsStyle` TINYINT NOT NULL DEFAULT 0 COMMENT '商品款式 0单款式 1多款式';
ALTER TABLE xhGoodsStyle CHANGE `order` `inTurn` INT NOT NULL DEFAULT 0 COMMENT '排序';
ALTER TABLE xhGoodsStyle DROP COLUMN `softDel`;

2019.12.3 shish
ALTER TABLE xhGoods ADD sendType TINYINT NOT NULL DEFAULT 0 COMMENT '配送方式 0需要配送 1到店自提' AFTER sendArea;
ALTER TABLE xhGoods ADD freightType TINYINT NOT NULL DEFAULT 0 COMMENT '运费计算方式，0按距离计算 1免运费' AFTER sendType;
DROP TABLE IF EXISTS `xhCouponModel`;
DROP TABLE IF EXISTS `xhCouponReward`;
DROP TABLE IF EXISTS `xhCouponRight`;
ALTER TABLE xhCoupon DROP COLUMN `type`;
ALTER TABLE xhCoupon DROP COLUMN `property`;
ALTER TABLE xhCoupon DROP COLUMN `fitType`;
ALTER TABLE xhCoupon DROP COLUMN `rightId`;
ALTER TABLE xhCoupon DROP COLUMN `modelId`;
ALTER TABLE xhCoupon DROP COLUMN `reward`;
ALTER TABLE xhCoupon DROP COLUMN `urlQrCode`;
ALTER TABLE xhCoupon CHANGE howToGet getType TINYINT NOT NULL DEFAULT 0 COMMENT '获得优惠券的方式';
ALTER TABLE xhCoupon DROP COLUMN `targetId`;
ALTER TABLE xhCoupon MODIFY deadline INT NOT NULL DEFAULT 0 COMMENT '过期时间；值4102416000表示永不过期，即2100-01-01 00:00:00';
ALTER TABLE xhCoupon ADD INDEX userId(userId,`status`);
ALTER TABLE xhCoupon ADD INDEX merchantId(merchantId,`status`);

2019.12.2 19:18
DROP TABLE IF EXISTS `xhCategoryRelate`;
ALTER TABLE xhCategory ADD indexShow TINYINT NOT NULL DEFAULT 0 COMMENT '首页上显示 0不显示 1显示' AFTER categoryName;
ALTER TABLE xhCategory ADD menuShow TINYINT NOT NULL DEFAULT 0 COMMENT '菜单显示 0不显示 1显示' AFTER `indexShow`;
ALTER TABLE xhCategory ADD INDEX indexShow(merchantId,indexShow);
ALTER TABLE xhCategory ADD INDEX menuShow(merchantId,menuShow);
ALTER TABLE xhAd ADD adImg VARCHAR(800) NOT NULL DEFAULT '' COMMENT '广告图' AFTER adName;
ALTER TABLE xhAd MODIFY endTime INT NOT NULL DEFAULT 0 COMMENT '结束时间；方便查询，永不过期情况，使用 开始值0 结束值4102416000即2100-01-01 00:00:00';
ALTER TABLE xhGoodsCategory MODIFY `cId` INT NOT NULL DEFAULT 0 COMMENT '分类id';
ALTER TABLE xhGoods DROP COLUMN `categoryId`;
ALTER TABLE xhGoodsCategory ADD INDEX merchantId(merchantId);
ALTER TABLE xhCategory DROP COLUMN `indexShow`;
ALTER TABLE xhCategory DROP COLUMN `menuShow`;
ALTER TABLE xhCategory ADD `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0停用1启用';
ALTER TABLE xhCategory DROP INDEX `indexShow`;
ALTER TABLE xhCategory DROP INDEX `menuShow`;
ALTER TABLE xhCategory ADD INDEX merchantId(merchantId,`status`);
ALTER TABLE xhGoodsCategory DROP INDEX merchantId;
ALTER TABLE xhGoodsCategory ADD INDEX merchantId(merchantId,cId);
ALTER TABLE xhGoods DROP COLUMN `cover`;

2019.12.2 13:00 shish 广告管理
DROP TABLE IF EXISTS xhAd;
CREATE TABLE IF NOT EXISTS xhAd(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`adName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '广告名称',
`merchantId` INT NOT NULL DEFAULT 0 COMMENT '商家id',
`type` TINYINT NOT NULL DEFAULT 0 COMMENT '广告类型 0商城首页轮播图 1门店轮播图',
`style` TINYINT NOT NULL DEFAULT 0 COMMENT '0 url 1商品id',
`url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '链接',
`goodsId` INT NOT NULL DEFAULT 0 COMMENT '商品id',
`startTime` INT NOT NULL DEFAULT 0 COMMENT '开始时间',
`endTime` INT NOT NULL DEFAULT 0 COMMENT '结束时间，方便查询，永不过期 使用值4102416000即2100-01-01 00:00:00',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='广告管理';
ALTER TABLE xhAd ADD INDEX `merchantId`(`merchantId`,`endTime`);

2019.12.2 shish
DROP TABLE IF EXISTS xhUsage;
CREATE TABLE `xhUsage` (
  `id` INT(11) NOT NULL,
  `usageName` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用途名称',
  `addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
) COMMENT='商品用途';
INSERT INTO `xhUsage` (`id`, `usageName`, `addTime`) VALUES
(1, '送爱人/恋人', 1567955291),
(2, '开业', 1567955291),
(3, '婚庆', 1567955291),
(4, '家养', 1567955291),
(5, '生日', 1568038612),
(6, '会议', 1568038612),
(7, '送母亲', 1568038612),
(8, '送老师', 1568038612),
(9, '其它', 1568038612);

2019.12.1
ALTER TABLE `xhUser` ADD INDEX mobile(merchantId,`mobile`);
ALTER TABLE xhUserAsset DROP COLUMN `point`;
ALTER TABLE xhUserAsset MODIFY `integral` INT NOT NULL DEFAULT 0 COMMENT '积分';
ALTER TABLE xhUser MODIFY birthday DATE NOT NULL DEFAULT '0000-00-00' COMMENT '生日';
ALTER TABLE xhUserAsset DROP COLUMN `uniqueQrCode`;
ALTER TABLE xhUserAsset DROP COLUMN `uniqueCode`;
ALTER TABLE xhMerchantExtend ADD gradeSwitch TINYINT NOT NULL DEFAULT 1 COMMENT '会员等级修改开关 0不可再修改 1可修改' AFTER gradeList;
DROP TABLE IF EXISTS `xhUserOldData`;
!!!!!!老客户没给国兰导进来的要导进来客户表里

shish 2019.11.30
ALTER TABLE xhGoods DROP COLUMN `multiPrice`;
RENAME TABLE xhGoodsPrice TO xhGoodsStyle;
ALTER TABLE xhGoodsStyle COMMENT '商品款式';
ALTER TABLE xhGoodsStyle ADD goodsId INT NOT NULL DEFAULT 0 COMMENT '商品id' AFTER `id`;
DROP TABLE IF EXISTS `xhGoodsUsage`;
ALTER TABLE xhOrder ADD INDEX merchant(merchantId,`status`);
ALTER TABLE xhOrder ADD INDEX userId(userId,`status`);
ALTER TABLE xhUserAsset ADD inviteNum INT NOT NULL DEFAULT 0 COMMENT '邀请新人数量' AFTER `discount`;
ALTER TABLE xhAdmin MODIFY `password` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '密码';
ALTER TABLE xhAdmin MODIFY `confirmPassword` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '确认密码';

shish 2019.11.27
ALTER TABLE xhOrder ADD refund TINYINT NOT NULL DEFAULT 0 COMMENT '1已退款 0正常订单，没有退款' AFTER luckyNum;
ALTER TABLE xhGoods MODIFY multiPrice VARCHAR(255) NOT NULL DEFAULT '' COMMENT '多款式商品，有多种价格，例(1,2,3,4)';

shish 2019.11.27
!!注意第一家全部订单已经完成，并将全部订单置为已完成
ALTER TABLE xhOrder MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态 0待付款 1待配送 2配送中 3待通知 4已取消 5已完成';
!!注意第一家店分类与用途迁移的问题
DROP TABLE IF EXISTS xhCategoryRelate;
ALTER TABLE xhCategory ADD merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id' AFTER parentId;
DROP TABLE IF EXISTS xhGoodsUsage;
DROP TABLE IF EXISTS xhUsage;
DROP TABLE IF EXISTS xhUsageRelation;
DROP TABLE IF EXISTS xhStatIncomeUsage;
DROP TABLE IF EXISTS xhStatIncomeUsageMonth;

shish 2019.11.26
ALTER TABLE xhAdminRole DROP COLUMN auth;
ALTER TABLE xhAdminRole CHANGE authority auth VARCHAR(500) NOT NULL DEFAULT '' COMMENT '权限id列表，存在*表示有所有权限';

shish 2019.11.25
ALTER TABLE xhAuth CHANGE front_url frontUrl VARCHAR(30) NOT NULL DEFAULT '' COMMENT '前端路由';
ALTER TABLE xhAuth CHANGE back_url backUrl VARCHAR(30) NOT NULL DEFAULT '' COMMENT '后端路由';

shish 2019.11.24
ALTER TABLE xhAdminRoleAuth ADD endId TINYINT NOT NULL DEFAULT 0 COMMENT '业务端id' AFTER roleId;
ALTER TABLE xhMerchant ADD setDeadline INT NOT NULL DEFAULT 0 COMMENT '套餐到期时间' AFTER `setId`;
ALTER TABLE xhMerchant ADD setId TINYINT NOT NULL DEFAULT 0 COMMENT '套餐id' AFTER `wxRefreshToken`;

shish 2019.11.23
DROP TABLE IF EXISTS xhAuth;
CREATE TABLE xhAuth(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
endId TINYINT NOT NULL DEFAULT 0 COMMENT '业务端，零售1',
parentId INT NOT NULL DEFAULT 0 COMMENT '权限父id',
authName VARCHAR(30) NOT NULL DEFAULT '' COMMENT '权限名称',
front_url VARCHAR(30) NOT NULL DEFAULT '' COMMENT '前端路由',
back_url VARCHAR(30) NOT NULL DEFAULT '' COMMENT '后端路由',
`addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
)COMMENT='各业务端的权限';
DROP TABLE IF EXISTS xhSetAuth;
CREATE TABLE xhSetAuth(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
setId TINYINT NOT NULL DEFAULT 0 COMMENT '套餐id',
endId TINYINT NOT NULL DEFAULT 0 COMMENT '业务端id',
auth VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '权限',
`addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
)COMMENT='套餐在各业务端的权限';
CREATE TABLE xhAdminRoleAuth(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
roleId INT NOT NULL DEFAULT 0 COMMENT '角色id',
auth VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '权限',
`addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
)COMMENT='员工角色权限'

shish 2019.11.23
!!jwt的过期时间要设置得短点

shish 2019.11.22
!!注意要将成积分移到成长值里

shish 2019.11.22
ALTER TABLE xhUserAsset ADD growth INT NOT NULL DEFAULT 0 COMMENT '成长值' AFTER `point`;