huahuibao/sql.txt

DROP TABLE IF EXISTS `xhMain`;
CREATE TABLE IF NOT EXISTS `xhMain`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`balance` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '余额',
  `freezeBalance` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '提现时被冻结的余额',
  `txBalance` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '可提现余额',
  `fixBalance` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '只能向推荐人消费的余额，指定余额',
    `totalRecharge` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计充值',
  `totalIncome` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计收入',
  `totalExpend` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '总支出',
  `totalItem` INT(11) NOT NULL DEFAULT '0' COMMENT '总花材数',
  `totalSale` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计销售额',
  `totalStockIn` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计入库',
  `totalStockOut` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计出库',
  `cashName` varchar(100) NOT NULL DEFAULT '' COMMENT '收款人姓名',
  `cashAccount` varchar(50) NOT NULL DEFAULT '' COMMENT '银行卡号，银行帐号',
  `cashBank` varchar(50) NOT NULL DEFAULT '' COMMENT '开户行',
  `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)COMMENT='中央，中控';

============ shish 20220311

ALTER TABLE xhStatSale ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatSaleMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatUser ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatUserMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatOut ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatOutMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhPtYeChange ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhShopYeChange ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhShopCapital ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhCash ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhRecharge ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStaff ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhGhsStatYj ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhGhsStatYjMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatVisit ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatVisitMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;

ALTER TABLE xhGhsStatOrderCount ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatOrder ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatOrderCount ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatOrderMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;

ALTER TABLE xhStatIncome ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatIncomeMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;


============ 资产相关表中央化 shish 20220313


执行供货商资产中央化 ./yii init/ghs-center 【库存也要中央化处理，要加入进来】!!!!

ALTER TABLE xhStaff COMMENT '员工';
ALTER TABLE xhGhsItem DROP COLUMN `oneRatio`;
ALTER TABLE xhGhsItem ADD smallRatio SMALLINT(6) NOT NULL DEFAULT '1' COMMENT '小单位价格系数，一支花价格系数，一支花=(skPrice/ratio)*smallRatio' AFTER `rank`;
ALTER TABLE xhCategory ADD property TINYINT NOT NULL DEFAULT 0 COMMENT '属性 0鲜花类 1盆栽绿植花瓶类' AFTER `sjId`;
ALTER TABLE xhGoods ADD property TINYINT NOT NULL DEFAULT 0 COMMENT '属性 0鲜花类 1盆栽绿植花瓶类' AFTER `sjId`;

DROP TABLE IF EXISTS `xhWork`;
CREATE TABLE IF NOT EXISTS `xhWork`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
`shopId` INT(11) NOT NULL DEFAULT '0' COMMENT '门店id',
`mainId` INT(11) NOT NULL DEFAULT '0' COMMENT '中央id',
`makeSn` CHAR(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '工单编号',
`orderSn` CHAR(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '关联订单编号',
`orderId` INT NOT NULL DEFAULT 0 COMMENT '关联订单id',
`num` SMALLINT NOT NULL DEFAULT 0 COMMENT '数量',
`categoryId` INT NOT NULL DEFAULT 0 COMMENT '分类id',
`goodsId` INT NOT NULL DEFAULT 0 COMMENT '商品id',
`from` TINYINT NOT NULL DEFAULT 0 COMMENT '来源 0门店下单 1小程序下单...',
`remark` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '备注',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0待完成 1已完成 2已取消',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
)COMMENT='工单';
DROP TABLE IF EXISTS `xhWorkItem`;
CREATE TABLE IF NOT EXISTS `xhWorkItem`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称',
`cover` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '花材封面',
`workSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '工单编号',
`itemId` INT(11) NOT NULL DEFAULT '0' COMMENT '花材id',
`ptItemId` INT(11) NOT NULL DEFAULT '0' COMMENT '平台花材id',
`sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
`shopId` INT(11) NOT NULL DEFAULT '0' COMMENT '门店id',
`mainId` INT(11) NOT NULL DEFAULT '0' COMMENT '中央id',
`num` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '数量，包括大小单位总合',
`cost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '花材单位成本',
`bigPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '大单位价格',
`smallPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '小单位价格',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
)COMMENT='工单花材明细';


ALTER TABLE xhGoodsItem DROP COLUMN `productId`;
ALTER TABLE xhGoodsItem DROP COLUMN `itemId`;
ALTER TABLE xhGoodsItem ADD itemId INT NOT NULL DEFAULT 0 COMMENT '花材id' AFTER `goodsId`;
ALTER TABLE xhGoodsItem ADD ptItemId INT NOT NULL DEFAULT 0 COMMENT '平台花材id' AFTER `itemId`;

DROP TABLE IF EXISTS `xhHdRefund`;
CREATE TABLE IF NOT EXISTS `xhHdRefund` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
  `shopId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '门店ID',
  `customId` INT(11) NOT NULL DEFAULT '0' COMMENT '客户id',
  `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款订单编号',
  `orderSn` CHAR(30) NOT NULL DEFAULT '' COMMENT '关联订单编号',
  `orderId` INT(11) NOT NULL DEFAULT '0' COMMENT '关联订单id',
  `status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单状态： 0处理中 1已完成',
  `shopAdminId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '员工id',
  `shopAdminName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '员工名称',
  `refundPrice` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额',
  `refundType` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '退款类型： 1退货并退款，2仅退款',
  `remark` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '备注信息',
  `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间'
)COMMENT='零售退款单';
DROP TABLE IF EXISTS `xhHdRefundItem`;
CREATE TABLE IF NOT EXISTS `xhHdRefundItem`(
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款单号',
`refundId` INT NOT NULL DEFAULT 0 COMMENT '退款单id',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称',
`itemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '花材ID',
`ptItemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '平台花材ID',
`num` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '花材数量',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
)COMMENT='退款花材';
DROP TABLE IF EXISTS `xhHdRefundGoods`;
CREATE TABLE IF NOT EXISTS `xhHdRefundGoods`(
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款单号',
`refundId` INT NOT NULL DEFAULT 0 COMMENT '退款单id',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称',
`goodsId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '商品ID',
`num` INT NOT NULL DEFAULT '0.00' COMMENT '数量',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
)COMMENT='退款商品';

DROP TABLE IF EXISTS `xhMallRefund`;
CREATE TABLE IF NOT EXISTS `xhMallRefund` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
  `shopId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '门店ID',
  `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款订单编号',
  `orderSn` CHAR(30) NOT NULL DEFAULT '' COMMENT '关联订单编号',
  `orderId` INT(11) NOT NULL DEFAULT '0' COMMENT '关联订单id',
  `status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单状态： 0处理中 1已完成',
  `refundPrice` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额',
  `refundType` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '退款类型： 1退货并退款，2仅退款',
  `remark` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '备注信息',
  `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间'
)COMMENT='商城退款单';
DROP TABLE IF EXISTS `xhMallRefundItem`;
CREATE TABLE IF NOT EXISTS `xhMallRefundItem`(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款单号',
`refundId` INT NOT NULL DEFAULT 0 COMMENT '退款单id',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称',
`itemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '花材ID',
`ptItemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '平台花材ID',
`num` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '花材数量',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
)COMMENT='退款花材';
DROP TABLE IF EXISTS `xhMallRefundGoods`;
CREATE TABLE IF NOT EXISTS `xhMallRefundGoods`(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款单号',
`refundId` INT NOT NULL DEFAULT 0 COMMENT '退款单id',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称',
`goodsId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '商品ID',
`num` INT NOT NULL DEFAULT '0.00' COMMENT '数量',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
)COMMENT='退款商品';

DROP TABLE IF EXISTS `xhGhsCgRefund`;
CREATE TABLE `xhGhsCgRefund` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
  `shopId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '门店ID',
  `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款订单编号',
  `thirdRefundNo` CHAR(50) NOT NULL DEFAULT '' COMMENT '第三方退款id',
  `status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单状态 0处理中 1已完成',
  `shopAdminId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '员工id',
  `shopAdminName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '员工名称',
  `num` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '数量',
  `refundPrice` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额',
  `refundType` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '退款类型： 1退货并退款，2仅退款',
  `remark` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '备注信息',
  `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_orderNum` (`refundSn`)
) COMMENT='供应商采购退款单';
DROP TABLE IF EXISTS `xhGhsCgRefundItem`;
CREATE TABLE `xhGhsCgRefundItem` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款单编号',
  `name` VARCHAR(300) NOT NULL DEFAULT '' COMMENT '名称',
  `cover` VARCHAR(600) NOT NULL DEFAULT '' COMMENT '封面',
  `itemId` INT(11) NOT NULL DEFAULT '0' COMMENT '花材ID',
  `ptItemId` INT(11) NOT NULL DEFAULT '0' COMMENT '平台花材ID',
  `num` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '花材数量',
  PRIMARY KEY (`id`),
  KEY `idx_orderNum` (`refundSn`) USING BTREE,
  KEY `idx_goodsItemId` (`itemId`) USING BTREE
) COMMENT='供应商采购退款详情';
DROP TABLE IF EXISTS `xhGhsCgRefundSn`;
CREATE TABLE `xhGhsCgRefundSn` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) COMMENT='供应商采购退款单号';


============ shish 20220314


DROP TABLE IF EXISTS `xhMyLs`;
CREATE TABLE IF NOT EXISTS `xhMyLs`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
bindId int not null default 0 comment '绑定id',
sjId INT NOT NULL DEFAULT 0 COMMENT '商家id',
shopId INT NOT NULL DEFAULT 0 COMMENT '门店id',
ownerSjId INT NOT NULL DEFAULT 0 COMMENT '零售店所有者的商家id',
ownerShopId INT NOT NULL DEFAULT 0 COMMENT '零售店所有者的门店id',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
)COMMENT='我的零售店';
DROP TABLE IF EXISTS `xhMyPf`;
CREATE TABLE IF NOT EXISTS `xhMyPf`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
bindId int not null default 0 comment '绑定id',
sjId INT NOT NULL DEFAULT 0 COMMENT '商家id',
shopId INT NOT NULL DEFAULT 0 COMMENT '门店id',
ownerSjId INT NOT NULL DEFAULT 0 COMMENT '零售店所有者的商家id',
ownerShopId INT NOT NULL DEFAULT 0 COMMENT '零售店所有者的门店id',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
)COMMENT='我的批发店';
DROP TABLE IF EXISTS `xhMyPfLsBind`;
CREATE TABLE IF NOT EXISTS `xhPfLsBind`(
pfSjId INT NOT NULL DEFAULT 0 COMMENT '商家id',
pfShopId INT NOT NULL DEFAULT 0 COMMENT '门店id',
lsSjId INT NOT NULL DEFAULT 0 COMMENT '商家id',
lsShopId INT NOT NULL DEFAULT 0 COMMENT '门店id',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待确认 1已同意 2已取消，不同意',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
)COMMENT='批发零售店绑定';


ALTER TABLE xhShop ADD `open` TINYINT NOT NULL DEFAULT 1 COMMENT '营业状态 0休息 1正常营业' AFTER `telephone`;
ALTER TABLE xhShop ADD `openStartTime` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '营业开始时间，空表示24小时，3:30' AFTER `open`;
ALTER TABLE xhShop ADD `openEndTime` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '营业结束时间 5:00' AFTER `openStartTime`;

DROP TABLE IF EXISTS `xhGroup`;
CREATE TABLE IF NOT EXISTS `xhGroup`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`mainId` int not null default 0 comment '中央id',
`sjId` int not null default 0 comment '商家id',
`shopId` int not null default 0 comment '门店id',
`name` VARCHAR(80) NOT NULL DEFAULT '' COMMENT '组合名称',
`cover` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '封面图片',
`intro` VARCHAR(900) NOT NULL DEFAULT '' COMMENT '简介',
`price` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '价格',
`num` INT NOT NULL DEFAULT 0 COMMENT '开单次数',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
)COMMENT='商品花材组合';
DROP TABLE IF EXISTS `xhGroupItem`;
CREATE TABLE `xhGroupItem` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `groupId` INT(11) NOT NULL DEFAULT '0' COMMENT '组合id',
  `name` VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '花材名称',
  `itemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '花材ID',
  `ptItemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '平台花材ID',
  `num` INT NOT NULL DEFAULT 0 COMMENT '当前数量',
  `unitType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '当前单位类型 0大单位 1小单位',
  `unitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位价格，单价',
  `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) COMMENT='常用组合的花材';
DROP TABLE IF EXISTS `xhGroupGoods`;
CREATE TABLE IF NOT EXISTS `xhGroupGoods`(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`groupId` INT NOT NULL DEFAULT 0 COMMENT '组合id',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称',
`goodsId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '商品ID',
`num` INT NOT NULL DEFAULT '0.00' COMMENT '数量',
`unitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位价格，单价',
`addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
)COMMENT='组合商品';


ALTER TABLE `xhGroup` ADD INDEX idx_shopId (`shopId`,`addTime`);
ALTER TABLE xhGoods ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`;
ALTER TABLE xhGoodsCategory ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`;
ALTER TABLE xhGoods ADD cover VARCHAR(800) NOT NULL DEFAULT '' COMMENT '封面' AFTER `goodsName`;

ALTER TABLE xhGoods MODIFY `property` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '属性 0鲜花成品 1盆栽绿植花瓶 2花材';
ALTER TABLE xhGhsItem ADD `property` TINYINT(4) NOT NULL DEFAULT '2' COMMENT '属性 0鲜花成品 1盆栽绿植花瓶 2花材' AFTER `cover`;
ALTER TABLE xhGoods MODIFY `property` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '属性 0成品 1花材，前端开发区分使用';
ALTER TABLE xhGhsItem MODIFY `property` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '属性 0成品 1花材，前端开发区分使用';
ALTER TABLE xhGoods ADD flower TINYINT NOT NULL DEFAULT 0 COMMENT '0非鲜花成品，绿植盆栽花瓶 1鲜花成品' AFTER `property`;

ALTER TABLE xhStatCg ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatCgGhs ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatCgGhsMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatCgMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;

ALTER TABLE xhStatKd ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;
ALTER TABLE xhStatKdMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`;

ALTER TABLE xhGoods CHANGE `goodsName` `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品名称';

============ 批发零售绑定关系 shish 20220314


========================花材相关表结构变更start=======linqh 20220315=================
ALTER TABLE `xhGhsItem`
ADD COLUMN `mainId`  int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`,
ADD INDEX `idx_mainId` (`mainId`) ;

ALTER TABLE `xhGhsItemClass`
ADD COLUMN `mainId`  int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '中央ID' AFTER `sjId`,
ADD INDEX `idx_mainId` (`mainId`) ;

ALTER TABLE `xhGhsItemRank`
ADD COLUMN `mainId`  int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '中央ID' AFTER `sjId`,
ADD INDEX `idx_mainId` (`mainId`) ;

ALTER TABLE `xhGhsStockRecord`
ADD COLUMN `mainId`  int(11) NOT NULL AFTER `shopId`,
ADD INDEX `idx_mainId` (`mainId`) ;

ALTER TABLE `xhGhsStockRecord`
MODIFY COLUMN `mainId`  int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '中央ID' AFTER `shopId`;


CREATE TABLE `xhGoodsStockRecord` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sjId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商家ID',
  `shopId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '门店ID',
  `mainId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '中央ID',
  `recordType` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '记录类型 1制作入口（采购），2出库，3入库，4盘点 5销售（开单）',
  `orderSn` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '订单号',
  `goodsId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '成品ID',
  `goodsNum` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '成品数量',
  `oldStock` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '原库存数量',
  `newStock` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '新库存数量',
  `addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `extend` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '扩展字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='成品库存记录表';



===============花材相关表结构变更end========



==========客户的花店 shish 20220316

DROP TABLE IF EXISTS `xhHd`;
CREATE TABLE `xhHd` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `userId` INT NOT NULL DEFAULT 0 COMMENT '用户id',
  `sjId` INT NOT NULL DEFAULT 0 COMMENT '商家id',
  `shopId` INT NOT NULL DEFAULT 0 COMMENT '门店id',
  `avatar` VARCHAR(800) NOT NULL DEFAULT '' COMMENT '头像',
  `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '供货商名称',
  `py` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '名称拼音首字母',
  `mobile` CHAR(15) NOT NULL DEFAULT '' COMMENT '手机号',
  `province` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '省',
  `city` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '市',
  `dist` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '区县',
  `address` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '街道地址',
  `floor` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '楼号门牌号',
  `fullAddress` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '完整地址',
  `showAddress` VARCHAR(900) NOT NULL DEFAULT '' COMMENT '地图接口显示的完整地址',
  `lat` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '纬度',
  `long` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '经度',
  `debt` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '1没有欠款 2有欠款',
  `debtNum` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '欠款订单数，欠款笔数',
  `debtAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '欠款金额',
  `giveLevel` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '客户等级',
  `giveDiscount` DECIMAL(3,2) NOT NULL DEFAULT '1.00' COMMENT '提供的折扣',
  `expendAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '消费金额',
  `expendNum` INT(11) NOT NULL DEFAULT '0' COMMENT '消费次数',
  `black` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '列入黑名单 1没有 2是',
  `inTurn` INT(10) NOT NULL DEFAULT '100' COMMENT '排序',
  `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
) COMMENT='客户的花店';

ALTER TABLE xhOrder ADD `thirdNo` CHAR(80) NOT NULL DEFAULT '' COMMENT '支付回调编号，微信支付回调的transaction_id' AFTER `orderType`;
ALTER TABLE xhOrder ADD `labourCost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '人工和资材和包装费' AFTER `sendCost`;

DROP TABLE IF EXISTS `xhOrderItem`;
CREATE TABLE `xhOrderItem` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '名称',
  `cover` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '封面',
  `ratio` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '大小单位比例',
  `unitType` TINYINT NOT NULL DEFAULT 0 COMMENT '当前单位类型 0大单位 1小单位',
  `unitName` CHAR(10) NOT NULL DEFAULT '' COMMENT '当前单位名称',
  `unitId` INT NOT NULL DEFAULT 0 COMMENT '当前单位id',
  `unitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位价格，单价',
  `num` INT NOT NULL DEFAULT 0 COMMENT '当前单位数量',
  `orderSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '订单编号',
  `itemId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家花材id',
  `ptItemId` INT NOT NULL DEFAULT 0 COMMENT '平台花材id',
  `count` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '数量，转换后带小数点的数量',
  `price` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '总价',
  `customId` INT(11) NOT NULL DEFAULT '0' COMMENT '客户id',
  `hdId` INT NOT NULL DEFAULT 0 COMMENT '花店id',
  `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商户id',
  `shopId` INT NOT NULL DEFAULT 0 COMMENT '门店id',
  `mainId` INT NOT NULL DEFAULT 0 COMMENT '中央id',
  `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
)COMMENT='零售订单花材详情';
DROP TABLE IF EXISTS `xhOrderGoods`;
CREATE TABLE `xhOrderGoods` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `orderSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '订单编号',
  `goodsId` INT(11) NOT NULL DEFAULT '0' COMMENT '商品id，备用字段',
  `customId` INT(11) NOT NULL DEFAULT '0' COMMENT '客户id',
  `hdId` INT NOT NULL DEFAULT 0 COMMENT '花店id',
  `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商户id',
  `shopId` INT NOT NULL DEFAULT 0 COMMENT '门店id',
  `mainId` INT NOT NULL DEFAULT 0 COMMENT '中央id',
  `name` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '名称',
  `cover` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '封面',
  `unitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '单价',
  `num` INT(11) NOT NULL DEFAULT '0' COMMENT '数量',
  `price` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '总价',
  `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) COMMENT='零售订单商品明细';

ALTER TABLE xhCustom ADD hdId INT NOT NULL DEFAULT 0 COMMENT '花店id' AFTER `id`;
ALTER TABLE xhHd ADD customId INT NOT NULL DEFAULT 0 COMMENT '客户id' AFTER `id`;
ALTER TABLE xhOrder ADD hdId INT NOT NULL DEFAULT 0 COMMENT '花店id' AFTER `discountType`;
ALTER TABLE xhOrder ADD hdName VARCHAR(50) NOT NULL DEFAULT '' COMMENT '花店名称' AFTER `hdId`;
ALTER TABLE xhOrder ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`;

ALTER TABLE xhStatIncomeMonth DROP INDEX `shop`;
ALTER TABLE xhStatIncome DROP INDEX `shop`;
ALTER TABLE xhStatIncome ADD INDEX idx_mainId(`mainId`,`time`);
ALTER TABLE xhStatIncomeMonth ADD INDEX idx_mainId(`mainId`,`time`);

UPDATE xhGoods SET stock=0 WHERE stock=-100000;
ALTER TABLE xhGhsStockRecord MODIFY `itemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '平台花材ID';

ALTER TABLE xhStatOrder DROP INDEX `shop`;
ALTER TABLE xhStatOrder ADD INDEX `mainId`(`mainId`,`time`);
ALTER TABLE xhStatOrderCount DROP INDEX `shop`;
ALTER TABLE xhStatOrderCount ADD INDEX `mainId`(`mainId`,`time`);
ALTER TABLE xhStatOrderMonth DROP INDEX `shop`;
ALTER TABLE xhStatOrderMonth ADD INDEX `mainId`(`mainId`,`time`);

====shish 20220319

ALTER TABLE xhWastage ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`;
ALTER TABLE xhWastageItem ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`;

====报损中央化 shish 20220319