huahuibao/sql.sql

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='中央，中控';

-- ssh 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`;


-- 资产相关表中央化 ssh 20220313

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,
`name` varchar(500) not null default '' comment '名称',
`cover` varchar(800) not null default '' comment '封面',
`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',
`workSn` 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 '数量',
`catId` INT NOT NULL DEFAULT 0 COMMENT '分类id',
`catName` varchar(50) not null default '' comment '分类名称',
`goodsId` INT NOT NULL DEFAULT 0 COMMENT '商品id',
`from` TINYINT NOT NULL DEFAULT 0 COMMENT '来源 0门店下单 1小程序下单...',
`remark` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '备注',
`staffId` int not null default 0 comment '负责人',
`staffName` varchar(100) not null default '' comment '负责人名称',
`stockLock` tinyint not null default 0 comment '0库存没有锁定 1库存锁定',
`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 '花材封面',
`price` DECIMAL(9,2) NOT NULL DEFAULT '0.00' 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` INT NOT NULL DEFAULT 0 COMMENT '数量',
`count` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '带小数点的数量',
`ratio` SMALLINT NOT NULL DEFAULT 0 COMMENT '比例',
`bigName` CHAR(10) NOT NULL DEFAULT '' COMMENT '大单位名称',
`smallName` CHAR(10) NOT NULL DEFAULT '' COMMENT '小单位名称',
`unitType` TINYINT NOT NULL DEFAULT 0 COMMENT '当前单位类型 0大单位 1小单位',
`unitName` CHAR(10) NOT NULL DEFAULT '' COMMENT '当前单位名称',
`unitCost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '单位成本',
`cost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '总成本',
`wastNum` INT NOT NULL DEFAULT 0 COMMENT '报损数量',
`wastUnitType` TINYINT NOT NULL DEFAULT 0 COMMENT '报损单位类型 0大单位 1小单位',
`wastUnitId` INT NOT NULL DEFAULT 0 COMMENT '报损单位id',
`wastUnitName` CHAR(10) 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 `xhGoodsItem`;
CREATE TABLE `xhGoodsItem` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`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',
`goodsId` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '成品ID',
`itemId` INT(11) NOT NULL DEFAULT '0' COMMENT '花材id',
`ptItemId` INT(11) NOT NULL DEFAULT '0' COMMENT '平台花材id',
`num` INT NOT NULL DEFAULT 0 COMMENT '数量',
`count` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '带小数点的数量',
`ratio` SMALLINT NOT NULL DEFAULT 0 COMMENT '比例',
`bigName` CHAR(10) NOT NULL DEFAULT '' COMMENT '大单位名称',
`smallName` CHAR(10) NOT NULL DEFAULT '' COMMENT '小单位名称',
`unitType` TINYINT NOT NULL DEFAULT 0 COMMENT '当前单位类型 0大单位 1小单位',
`unitName` CHAR(10) 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`)
) COMMENT='成品的花材';

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='供应商采购退款单号';


-- ssh 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 '商品名称';

-- 批发零售绑定关系 ssh 20220314


-- 花材相关表结构变更start lqh 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



-- 客户的花店 ssh 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`);

-- ssh 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`;

-- 报损中央化 ssh 20220319

ALTER TABLE xhCategory ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `parentId`;
ALTER TABLE xhCategory CHANGE `property` `flower` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0 绿植盆栽花瓶 1鲜花成品';
drop table if exists `xhWorkSn`;
CREATE TABLE `xhWorkSn` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=126520 COMMENT='工单号';

-- 商品分类中央化 ssh 20220319

ALTER TABLE xhWorkItem ADD INDEX `workSn`(`workSn`);
ALTER TABLE xhGoodsSetting ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`;
ALTER TABLE xhGoodsCategory DROP INDEX `merchantId`;
ALTER TABLE xhGoodsCategory ADD INDEX `mainId`(`mainId`,`cId`);
ALTER TABLE xhGoods MODIFY `stock` INT(11) NOT NULL DEFAULT 0 COMMENT '库存';
UPDATE xhGoods SET stock=0;
ALTER TABLE xhFestRise ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER id;

ALTER TABLE xhGhsOrder ADD `book` TINYINT NOT NULL DEFAULT 0 COMMENT '预订单 0不是，普通订单 1预订单' AFTER `purchaseId`;
ALTER TABLE xhGhsOrder ADD `stockLock` TINYINT NOT NULL DEFAULT 1 COMMENT '0库存没有锁定 1库存锁定' AFTER `book`;
ALTER TABLE xhCg ADD `book` TINYINT NOT NULL DEFAULT 0 COMMENT '预订单 0不是，普通订单 1预订单' AFTER `orderSn`;
ALTER TABLE xhCg ADD `stockLock` TINYINT NOT NULL DEFAULT 1 COMMENT '0库存没有锁定 1库存锁定' AFTER `book`;
ALTER TABLE xhStatOut DROP INDEX `shop`;
ALTER TABLE xhStatOutMonth DROP INDEX `shop`;

ALTER TABLE xhGhsOrder MODIFY `tkPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计退款金额';
ALTER TABLE xhGhsOrder MODIFY `refundPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '最后一次的退款金额';
ALTER TABLE xhGhsOrder MODIFY `actPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '实付金额 (原价扣除优惠、退款 prePrice - discountAmount - tkPrice) actPrice realPrice作用现在一样';
ALTER TABLE xhGhsOrder MODIFY `realPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '实付金额 (原价扣除优惠、退款 prePrice - discountAmount - tkPrice) actPrice realPrice作用现在一样';
ALTER TABLE xhCg MODIFY `tkPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计退款金额';
ALTER TABLE xhCg MODIFY `refundPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '最后一次的退款金额';
ALTER TABLE xhCg MODIFY `actPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '实付金额 (原价扣除优惠、退款 prePrice - discountAmount - tkPrice) actPrice realPrice作用现在一样';
ALTER TABLE xhCg MODIFY `realPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '实付金额 (原价扣除优惠、退款 prePrice - discountAmount - tkPrice) actPrice realPrice作用现在一样';

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

ALTER TABLE xhGhsOrderItem ADD `preNum` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '原本要的数量' AFTER `smallNum`;
ALTER TABLE xhGhsOrderItem ADD `preBigNum` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '原本要的大单位数量' AFTER `preNum`;
ALTER TABLE xhGhsOrderItem ADD `preSmallNum` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '原本要的小单位数量' AFTER `preBigNum`;
ALTER TABLE xhCgItem ADD `preItemNum` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '原本要的数量' AFTER `itemNum`;
UPDATE xhGhsOrderItem SET preBigNum=bigNum,preSmallNum=smallNum,preNum=num;
UPDATE xhCgItem SET preItemNum=itemNum;

ALTER TABLE xhShop ADD lsShopId INT NOT NULL DEFAULT 0 COMMENT '批发店关联的零售店' AFTER `ptStyle`;
ALTER TABLE xhShop ADD pfShopId INT NOT NULL DEFAULT 0 COMMENT '零售店关联的批发店' AFTER `ptStyle`;

ALTER TABLE xhStatCg DROP INDEX `shop`;
ALTER TABLE xhStatCgMonth DROP INDEX `shop`;
ALTER TABLE xhStatCgGhs DROP INDEX `ghsId`;
ALTER TABLE xhStatCgGhsMonth DROP INDEX `ghsId`;
ALTER TABLE xhStatKd DROP INDEX `shop`;
ALTER TABLE xhStatKdMonth DROP INDEX `shop`;
ALTER TABLE xhStatSale DROP INDEX `shop`;
ALTER TABLE xhStatSaleMonth DROP INDEX `shop`;

ALTER TABLE xhShop ADD kiloFee DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单每公斤服务费' AFTER `parentShopId`;
ALTER TABLE xhShop ADD miniKilo SMALLINT NOT NULL DEFAULT 0 COMMENT '最低公斤数，不足的按这里设置的公斤数计算' AFTER `kiloFee`;

ALTER TABLE xhGhsOrder ADD itemPrice DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '花材金额，产品金额' AFTER `modPrice`;
ALTER TABLE xhCg ADD `weight` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '花材总重量(公斤)' AFTER `status`;

ALTER TABLE xhGhsOrder ADD kiloFee DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单每公斤服务费' AFTER `weight`;
ALTER TABLE xhCg ADD kiloFee DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单每公斤服务费' AFTER `weight`;
ALTER TABLE xhGhsOrder ADD allKiloFee DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单总服务费' AFTER `kiloFee`;
ALTER TABLE xhCg ADD allKiloFee DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单总服务费' AFTER `kiloFee`;

ALTER TABLE xhGhsOrder ADD debtAmount DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '本单总欠款金额' AFTER `debt`;
ALTER TABLE xhGhsOrder ADD remainDebtAmount DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '本单剩余欠款金额' AFTER `debtAmount`;
ALTER TABLE xhCg ADD debtAmount DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '本单总欠款金额' AFTER `debt`;
ALTER TABLE xhCg ADD remainDebtAmount DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '本单剩余欠款金额' AFTER `debtAmount`;

ALTER TABLE xhGhsOrder ADD orderPrice  DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额 = 原价扣除优惠，不受退款影响' AFTER `tkPrice`;
ALTER TABLE xhCg ADD orderPrice  DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额 = 原价扣除优惠，不受退款影响' AFTER `tkPrice`;

ALTER TABLE xhGhsOrder MODIFY `prePrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '原价=花材金额+运费+人工成本(+预订单服务费)';
ALTER TABLE xhCg MODIFY `prePrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '原价=花材金额+运费+人工成本(+预订单服务费)';

ALTER TABLE xhGhsOrder CHANGE debtAmount debtPrice DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '总欠款金额' AFTER `realPrice`;
ALTER TABLE xhGhsOrder CHANGE remainDebtAmount remainDebtPrice DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '剩余欠款金额' AFTER `debtPrice`;
ALTER TABLE xhCg CHANGE debtAmount debtPrice DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '总欠款金额' AFTER `realPrice`;
ALTER TABLE xhCg CHANGE remainDebtAmount remainDebtPrice DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '剩余欠款金额' AFTER `debtPrice`;

ALTER TABLE xhGhsOrder MODIFY `sendCost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '运费' AFTER `itemPrice`;
ALTER TABLE xhGhsOrder MODIFY `labourCost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '人工和资材费用' AFTER `sendCost`;
ALTER TABLE xhGhsOrder MODIFY `labourCost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '人工和资材费用' AFTER `sendCost`;
ALTER TABLE xhGhsOrder MODIFY `weight` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '花材总重量(公斤)' AFTER `labourCost`;
ALTER TABLE xhGhsOrder MODIFY `kiloFee` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单每公斤服务费' AFTER `weight`;
ALTER TABLE xhGhsOrder MODIFY `allKiloFee` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单总服务费' AFTER `kiloFee`;

ALTER TABLE xhGhsOrder MODIFY `discount` DECIMAL(3,2) NOT NULL DEFAULT '1.00' COMMENT '享有折扣' AFTER `prePrice`;
ALTER TABLE xhGhsOrder MODIFY `discountType` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '优惠类型 1没有优惠 2使用平台优惠。类型放在dict.php里' AFTER `discount`;
ALTER TABLE xhGhsOrder MODIFY `discountAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额' AFTER `discountType`;

ALTER TABLE xhGhsOrder MODIFY `orderPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额 = 原价扣除优惠，不受退款影响' AFTER `discountAmount`;

ALTER TABLE xhCg MODIFY `sendCost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '运费' AFTER `itemPrice`;
ALTER TABLE xhCg MODIFY `labourCost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '人工和资材费用' AFTER `sendCost`;
ALTER TABLE xhCg MODIFY `weight` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '花材总重量(公斤)' AFTER `labourCost`;
ALTER TABLE xhCg MODIFY `kiloFee` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单每公斤服务费' AFTER `weight`;
ALTER TABLE xhCg MODIFY `allKiloFee` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单总服务费' AFTER `kiloFee`;

ALTER TABLE xhCg MODIFY `orderPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额 = 原价扣除优惠，不受退款影响' AFTER `prePrice`;
ALTER TABLE xhCg MODIFY `discount` DECIMAL(3,2) NOT NULL DEFAULT '1.00' COMMENT '享有折扣' AFTER `prePrice`;
ALTER TABLE xhCg MODIFY `discountType` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '优惠类型，在dict.php里' AFTER `discount`;
ALTER TABLE xhCg MODIFY `discountAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额' AFTER `discountType`;

ALTER TABLE xhGhsOrder ADD bookPrice DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单预付金额' AFTER `orderPrice`;
ALTER TABLE xhCg ADD bookPrice DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单预付金额' AFTER `orderPrice`;
ALTER TABLE xhCg ADD miniKilo SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '最少公斤数' AFTER `weight`;
ALTER TABLE xhGhsOrder ADD miniKilo SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '最少公斤数' AFTER `weight`;

ALTER TABLE xhGhsOrder CHANGE `stockLock` `stockChange` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '0库存没有变动 1库存有变动';
ALTER TABLE xhCg CHANGE `stockLock` `stockChange` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '0库存没有变动 1库存有变动';

ALTER TABLE xhGhs ADD ownMainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `ownShopId`;
ALTER TABLE xhGhsCustom ADD ownMainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `ownShopId`;

ALTER TABLE xhGhsOrderItem ADD unitWeight DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '单位重量' AFTER `smallUnitPrice`;
ALTER TABLE xhCgItem ADD unitWeight DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '单位重量' AFTER `variety`;

ALTER TABLE xhRefund ADD book TINYINT NOT NULL DEFAULT 0 COMMENT '0非预订退款，有花材详情 1预订退款，暂无详情页' AFTER `cgRefundId`;
ALTER TABLE xhCgRefund ADD book TINYINT NOT NULL DEFAULT 0 COMMENT '0非预订退款，有花材详情 1预订退款，暂无详情页' AFTER `saleRefundId`;
ALTER TABLE xhRefund MODIFY `status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0 处理中 1已完成';
ALTER TABLE xhCgRefund MODIFY `status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0 处理中 1已完成';
ALTER TABLE xhRefund ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`;
ALTER TABLE xhCgRefund ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`;

DROP TABLE IF EXISTS `xhStatBook`;
CREATE TABLE IF NOT EXISTS `xhStatBook`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`mainId` INT NOT NULL DEFAULT 0 COMMENT '中央id',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '名称',
itemId INT NOT NULL DEFAULT 0 COMMENT '花材id',
ptItemId INT NOT NULL DEFAULT 0 COMMENT '平台花材id',
`time` INT(11) NOT NULL DEFAULT '0' 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 `xhStatKhBook`;
CREATE TABLE IF NOT EXISTS `xhStatKhBook`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`mainId` INT NOT NULL DEFAULT 0 COMMENT '中央id',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '名称',
itemId INT NOT NULL DEFAULT 0 COMMENT '花材id',
ptItemId INT NOT NULL DEFAULT 0 COMMENT '平台花材id',
`time` INT(11) NOT NULL DEFAULT '0' COMMENT '日期',
`khId` INT NOT NULL DEFAULT 0 COMMENT '客户id',
`khName` VARCHAR(100) NOT NULL DEFAULT '' 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='预订单按客户维度统计';

ALTER TABLE xhGhsOrderItem ADD `xhNum` INT(11) NOT NULL DEFAULT '0' COMMENT '当前单位数量' AFTER `variety`;
ALTER TABLE xhGhsOrderItem ADD `xhUnitName` CHAR(10) NOT NULL DEFAULT '' COMMENT '当前单位名称' AFTER `xhNum`;
ALTER TABLE xhGhsOrderItem ADD `xhUnitType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '当前单位类型 0大单位 1小单位' AFTER `xhUnitName`;
ALTER TABLE xhGhsOrderItem ADD `xhUnitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位的单价' AFTER `xhUnitType`;
ALTER TABLE xhGhsOrderItem ADD `xhPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位的总价' AFTER `xhUnitPrice`;
ALTER TABLE xhGhsOrderItem ADD `xhPreNum` INT(11) NOT NULL DEFAULT '0' COMMENT '预订时数量' AFTER `xhPrice`;
ALTER TABLE xhGhsOrderItem ADD `xhPreUnitName` CHAR(10) NOT NULL DEFAULT '' COMMENT '预订时单位名称' AFTER `xhPreNum`;
ALTER TABLE xhGhsOrderItem ADD `xhPreUnitType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '预订时单位类型 0大单位 1小单位' AFTER `xhPreUnitName`;
ALTER TABLE xhGhsOrderItem ADD `xhPreUnitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订时单价' AFTER `xhPreUnitType`;
ALTER TABLE xhGhsOrderItem ADD `xhPrePrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订时总价' AFTER `xhPreUnitPrice`;

ALTER TABLE xhCgItem ADD `xhNum` INT(11) NOT NULL DEFAULT '0' COMMENT '当前单位数量' AFTER `unitWeight`;
ALTER TABLE xhCgItem ADD `xhUnitName` CHAR(10) NOT NULL DEFAULT '' COMMENT '当前单位名称' AFTER `xhNum`;
ALTER TABLE xhCgItem ADD `xhUnitType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '当前单位类型 0大单位 1小单位' AFTER `xhUnitName`;
ALTER TABLE xhCgItem ADD `xhUnitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位的单价' AFTER `xhUnitType`;
ALTER TABLE xhCgItem ADD `xhPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位的总价' AFTER `xhUnitPrice`;
ALTER TABLE xhCgItem ADD `xhPreNum` INT(11) NOT NULL DEFAULT '0' COMMENT '预订时数量' AFTER `xhPrice`;
ALTER TABLE xhCgItem ADD `xhPreUnitName` CHAR(10) NOT NULL DEFAULT '' COMMENT '预订时单位名称' AFTER `xhPreNum`;
ALTER TABLE xhCgItem ADD `xhPreUnitType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '预订时单位类型 0大单位 1小单位' AFTER `xhPreUnitName`;
ALTER TABLE xhCgItem ADD `xhPreUnitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订时单价' AFTER `xhPreUnitType`;
ALTER TABLE xhCgItem ADD `xhPrePrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订时总价' AFTER `xhPreUnitPrice`;

ALTER TABLE xhCgItem ADD `refundNum` INT(11) NOT NULL DEFAULT '0' COMMENT '已退货数量' AFTER `xhPrePrice`;
ALTER TABLE xhGhsOrderItem ADD `refundNum` INT(11) NOT NULL DEFAULT '0' COMMENT '已退货数量' AFTER `xhPrePrice`;

ALTER TABLE xhRefundItem ADD `xhNum` INT(11) NOT NULL DEFAULT '0' COMMENT '当前单位数量' AFTER `newStock`;
ALTER TABLE xhRefundItem ADD `xhUnitName` CHAR(10) NOT NULL DEFAULT '' COMMENT '当前单位名称' AFTER `xhNum`;
ALTER TABLE xhRefundItem ADD `xhUnitType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '当前单位类型 0大单位 1小单位' AFTER `xhUnitName`;
ALTER TABLE xhRefundItem ADD `xhUnitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位单价，售价' AFTER `xhUnitType`;
ALTER TABLE xhRefundItem ADD `xhPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位的总价，售价总和' AFTER `xhUnitPrice`;

ALTER TABLE xhCgRefundItem ADD `xhNum` INT(11) NOT NULL DEFAULT '0' COMMENT '当前单位数量' AFTER `newStock`;
ALTER TABLE xhCgRefundItem ADD `xhUnitName` CHAR(10) NOT NULL DEFAULT '' COMMENT '当前单位名称' AFTER `xhNum`;
ALTER TABLE xhCgRefundItem ADD `xhUnitType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '当前单位类型 0大单位 1小单位' AFTER `xhUnitName`;
ALTER TABLE xhCgRefundItem ADD `xhUnitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位单价，售价' AFTER `xhUnitType`;
ALTER TABLE xhCgRefundItem ADD `xhPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位的总价，售价总和' AFTER `xhUnitPrice`;

-- 预订单 ssh 20220327

ALTER TABLE xhStatItem DROP INDEX `shop`;
ALTER TABLE xhStatItemMonth DROP INDEX `shop`;
ALTER TABLE xhStatItem ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`;
ALTER TABLE xhStatItemMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`;

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

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

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

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

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

ALTER TABLE xhStatCg MODIFY `num` INT(11) NOT NULL DEFAULT '0' COMMENT '扎数，数量';
ALTER TABLE xhStatCgGhs MODIFY `num` INT(11) NOT NULL DEFAULT '0' COMMENT '扎数，数量';
ALTER TABLE xhStatCgGhsMonth MODIFY `num` INT(11) NOT NULL DEFAULT '0' COMMENT '扎数，数量';
ALTER TABLE xhStatCgMonth MODIFY `num` INT(11) NOT NULL DEFAULT '0' COMMENT '扎数，数量';

ALTER TABLE xhStatCg ADD `refundNum` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '退货数' AFTER `num`;
ALTER TABLE xhStatCg ADD `refundAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额' AFTER `refundNum`;
ALTER TABLE xhStatCgMonth ADD `refundNum` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '退货数' AFTER `num`;
ALTER TABLE xhStatCgMonth ADD `refundAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额' AFTER `refundNum`;

ALTER TABLE xhGoods ADD workId INT NOT NULL DEFAULT 0 COMMENT '工单id' AFTER `mainId`;
ALTER TABLE xhGoods ADD workSn CHAR(40) NOT NULL DEFAULT '' COMMENT '工单编号' AFTER `workId`;

ALTER TABLE xhGoodsItem ADD `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '名称' AFTER `mainId`;
ALTER TABLE xhGoodsItem ADD `cover` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '封面' AFTER `name`;

ALTER TABLE xhWork ADD bigNum SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '大单位数量' AFTER `num`;
ALTER TABLE xhWork ADD smallNum SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '大单位数量' AFTER `bigNum`;
ALTER TABLE xhGoods ADD bigNum SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '大单位数量' AFTER `flower`;
ALTER TABLE xhGoods ADD smallNum SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '大单位数量' AFTER `bigNum`;

ALTER TABLE xhGoodsCategory ADD flower TINYINT NOT NULL DEFAULT 1 COMMENT '商品属性 0绿植盆栽资材 1鲜花成品' AFTER `sjId`;

DROP TABLE IF EXISTS `xhGoodsCg`;
CREATE TABLE `xhGoodsCg` (
`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`mainId` INT(11) NOT NULL DEFAULT '0' COMMENT '中央id',
`saleId` INT(11) NOT NULL DEFAULT '0' COMMENT '供货商的销售单id',
`orderSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '订单编号',
`stockChange` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '0库存没有变动 1库存有变动',
`payWay` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '支付方式',
`customId` INT(11) NOT NULL DEFAULT '0' COMMENT '客户id',
`ghsId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '供货商ID',
`ghsName` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '供货商名称',
`ghsAvatar` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '供货商头像',
`ghsMobile` CHAR(15) NOT NULL DEFAULT '' COMMENT '供货商手机号',
`ghsAddress` VARCHAR(800) NOT NULL DEFAULT '' COMMENT '供货商地址',
`sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
`shopId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '门店ID',
`ghsShopAdminId` INT(11) NOT NULL DEFAULT '0',
`ghsShopAdminName` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '员工姓名',
`shopAdminId` INT(11) NOT NULL DEFAULT '0' COMMENT '员工id',
`shopAdminName` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '员工姓名',
`goodsPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '商品总金额',
`sendCost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '运费',
`labourCost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '人工和资材费用',
`prePrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '原价=商品金额+运费+人工成本(+预订单服务费)',
`discount` DECIMAL(3,2) NOT NULL DEFAULT '1.00' COMMENT '享有折扣',
`discountType` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '优惠类型，在dict.php里',
`discountAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额',
`orderPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额 = 原价扣除优惠，不受退款影响',
`bookPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '预订单预付金额',
`refundPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '最后一次的退款金额',
`tkPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计退款金额',
`actPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '实付金额 (原价扣除优惠、退款 prePrice - discountAmount - tkPrice) actPrice realPrice作用现在一样',
`realPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '实付金额 (原价扣除优惠、退款 prePrice - discountAmount - tkPrice) actPrice realPrice作用现在一样',
`debtPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '总欠款金额',
`remainDebtPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '剩余欠款金额',
`refund` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '1正常订单 2有退款，已退款',
`kindNum` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '多少种',
`debt` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '欠款 0未知 1是 2没有',
`deadline` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '有效期',
`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 `xhGoodsCgItem`;
CREATE TABLE `xhGoodsCgItem` (
`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`orderSn` CHAR(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '订单编号',
`goodsId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '商品ID',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品名称',
`cover` VARCHAR(300) NOT NULL DEFAULT '' COMMENT '图片',
`num` INT(11) NOT NULL DEFAULT '0' COMMENT '当前单位数量',
`price` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '单价',
`totalPrice` 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='绿植采购单详情';

DROP TABLE IF EXISTS `xhGoodsCgSn`;
CREATE TABLE `xhGoodsCgSn` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)COMMENT='盆栽采购订单号';

ALTER TABLE xhMain ADD `totalPurchase` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计采购' AFTER `totalExpend`;
ALTER TABLE xhMain ADD `totalPurchasePlant` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计采购绿植盆栽花瓶资材' AFTER `totalPurchase`;

DROP TABLE IF EXISTS `xhStatCgPlant`;
CREATE TABLE `xhStatCgPlant` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `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',
  `time` INT(11) NOT NULL DEFAULT '0' COMMENT '时间戳',
  `amount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '采购金额',
  `num` INT(11) NOT NULL DEFAULT '0' COMMENT '数量',
  `refundNum` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '退货数',
  `refundAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额',
  `totalAmount` 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='每天采购绿植盆栽花瓶资材统计';
DROP TABLE IF EXISTS `xhStatCgPlantMonth`;
CREATE TABLE `xhStatCgPlantMonth` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `time` INT(11) NOT NULL DEFAULT '0' COMMENT '月份',
  `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',
  `year` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '年',
  `month` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '月份',
  `amount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '支出金额',
  `num` INT(11) NOT NULL DEFAULT '0' COMMENT '数量',
  `refundNum` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '退货数',
  `refundAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额',
  `totalAmount` 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='每月采购绿植盆栽花瓶资材统计';

RENAME TABLE `xhGoodsCg` TO `xhPlantCg`;
RENAME TABLE `xhGoodsCgItem` TO `xhPlantCgItem`;
RENAME TABLE `xhGoodsCgSn` TO `xhPlantCgSn`;
ALTER TABLE xhPlantCg ADD `num` INT NOT NULL DEFAULT 0 COMMENT '本次采购的总数量' AFTER `kindNum`;

ALTER TABLE xhGoods MODIFY `content` TEXT COMMENT '正文内容';
ALTER TABLE xhGoodsStockRecord ADD `relateName` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '关联人' AFTER `goodsNum`;

-- 预订单 ssh 20220402

ALTER TABLE xhGhsItem MODIFY `price` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '普通价' AFTER `skPrice`;
ALTER TABLE xhShop MODIFY `totalPdAdd` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '累计盘盈';
ALTER TABLE xhShop MODIFY `totalPdSub` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '累计盘亏';
ALTER TABLE xhShop MODIFY `totalWast` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '累计报损';
ALTER TABLE xhStatPdSub MODIFY `amount` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '金额';
ALTER TABLE xhStatPdSub MODIFY `totalAmount` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '总金额';
ALTER TABLE xhStatPdSubMonth MODIFY `amount` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '金额';
ALTER TABLE xhStatPdSubMonth MODIFY `totalAmount` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '总金额';


ALTER TABLE xhGhsItem DROP INDEX `uni_index_product`;

-- 花材表的mainId添加进去之后，唯一的索引id最好也要加进去 ssh 20220418

ALTER TABLE xhWorkItem ADD unitPrice DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位售价' AFTER `unitName`;

ALTER TABLE xhOrder ADD goodsPrice DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '花材商品金额' AFTER `capitalId`;
ALTER TABLE xhOrder MODIFY `sendCost` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '运费' AFTER `goodsPrice`;
ALTER TABLE xhOrder MODIFY `labourCost` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '人工和资材费用' AFTER `sendCost`;
ALTER TABLE xhOrder MODIFY `prePrice` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '原价=花材金额+运费+人工成本' AFTER `labourCost`;
ALTER TABLE xhOrder MODIFY `discountType` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '优惠类型 1没有优惠 2使用平台优惠。类型放在dict.php里' AFTER `prePrice`;
ALTER TABLE xhOrder MODIFY `discountAmount` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额' AFTER `discountType`;

ALTER TABLE xhOrder ADD `orderPrice` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额 = 原价扣除优惠，不受退款影响' AFTER `discountAmount`;
ALTER TABLE xhOrder ADD `refundPrice` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '最后一次的退款金额' AFTER `orderPrice`;
ALTER TABLE xhOrder ADD `tkPrice` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '累计退款金额' AFTER `refundPrice`;

ALTER TABLE xhOrder MODIFY `actPrice` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '实付金额 (原价扣除优惠、退款 prePrice - discountAmount - tkPrice) actPrice realPrice作用现在一样' AFTER `tkPrice`;
ALTER TABLE xhOrder MODIFY `realPrice` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '实付金额 (原价扣除优惠、退款 prePrice - discountAmount - tkPrice) actPrice realPrice作用现在一样' AFTER `actPrice`;
ALTER TABLE xhOrder ADD `debtPrice` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '总欠款金额' AFTER `realPrice`;
ALTER TABLE xhOrder ADD `remainDebtPrice` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '剩余欠款金额' AFTER `debtPrice`;

ALTER TABLE xhXj ADD mainId INT NOT NULL DEFAULT 0 COMMENT '' AFTER `shopId`;

-- 零售订单优化 ssh 20220422

DROP TABLE IF EXISTS `xhWastageGoods`;
CREATE TABLE IF NOT EXISTS `xhWastageGoods` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `mainId` INT(11) NOT NULL DEFAULT '0' COMMENT '中央id',
  `wastageSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '订单编号',
  `goodsId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '商品id',
  `name` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '名称',
  `cover` VARCHAR(300) NOT NULL DEFAULT '' COMMENT '封面',
  `num` INT NOT NULL DEFAULT 0 COMMENT '报损数量',
  `beforeStock` INT NOT NULL DEFAULT '0' COMMENT '报损前库存',
  `afterStock` INT NOT NULL DEFAULT '0' COMMENT '报损后库存',
  `unitPrice` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '单位售价',
  `unitCost` DECIMAL(15,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 xhWastage ADD `type` TINYINT NOT NULL DEFAULT 0 COMMENT '报损类型 0普通报损 1工单报损' AFTER `orderSn`;
ALTER TABLE xhWastage ADD `targetId` INT NOT NULL DEFAULT 0 COMMENT '报损发起id，如工单id' AFTER `type`;

ALTER TABLE xhWork ADD `flowerNum` TINYINT NOT NULL DEFAULT 0 COMMENT '主花支数' AFTER `smallNum`;
ALTER TABLE xhWork MODIFY `smallNum` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '小单位数量';

ALTER TABLE xhOrder ADD cover VARCHAR(200) NOT NULL DEFAULT '' COMMENT '封面' AFTER `payWay`;

ALTER TABLE xhOrderGoods ADD `refundNum` INT(11) NOT NULL DEFAULT '0' COMMENT '已退货数' AFTER `price`;
ALTER TABLE xhOrderItem ADD `refundNum` INT(11) NOT NULL DEFAULT '0' COMMENT '已退货数' AFTER `price`;

ALTER TABLE xhHdRefund ADD mainId INT NOT NULL DEFAULT 0 COMMENT '' AFTER `shopId`;
ALTER TABLE xhHdRefundGoods ADD mainId INT NOT NULL DEFAULT 0 COMMENT '' AFTER `refundId`;
ALTER TABLE xhHdRefundItem ADD mainId INT NOT NULL DEFAULT 0 COMMENT '' AFTER `refundId`;

DROP TABLE IF EXISTS `xhHdRefundSn`;
CREATE TABLE IF NOT EXISTS `xhHdRefundSn` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=2681235 COMMENT='退款订单号';

ALTER TABLE xhHdRefundItem ADD unitName CHAR(5) NOT NULL DEFAULT '' COMMENT '单位名称' AFTER `num`;
ALTER TABLE xhHdRefundItem ADD unitType TINYINT NOT NULL DEFAULT 0 COMMENT '' AFTER `num`;
ALTER TABLE xhHdRefundItem ADD cover VARCHAR(200) NOT NULL DEFAULT '' COMMENT '封面' AFTER `name`;
ALTER TABLE xhHdRefundGoods ADD cover VARCHAR(300) NOT NULL DEFAULT '' COMMENT '封面' AFTER `name`;

ALTER TABLE xhHdRefundItem ADD ratio TINYINT NOT NULL DEFAULT 20 COMMENT '比例' AFTER `itemId`;
ALTER TABLE xhHdRefundItem ADD bigUnit CHAR(10) NOT NULL DEFAULT '' COMMENT '大单位' AFTER `ratio`;
ALTER TABLE xhHdRefundItem ADD smallUnit CHAR(10) NOT NULL DEFAULT '' COMMENT '小单位' AFTER `ratio`;

ALTER TABLE xhHdRefundItem ADD sjId INT NOT NULL DEFAULT 0 COMMENT '' AFTER `mainId`;
ALTER TABLE xhHdRefundItem ADD shopId INT NOT NULL DEFAULT 0 COMMENT '' AFTER `mainId`;
ALTER TABLE xhHdRefundGoods ADD sjId INT NOT NULL DEFAULT 0 COMMENT '' AFTER `mainId`;
ALTER TABLE xhHdRefundGoods ADD shopId INT NOT NULL DEFAULT 0 COMMENT '' AFTER `mainId`;

ALTER TABLE xhHdRefund ADD `thirdRefundNo` CHAR(50) NOT NULL DEFAULT '' COMMENT '第三方支付id' AFTER `orderId`;

ALTER TABLE xhMain ADD `totalView` INT(11) NOT NULL DEFAULT '0' COMMENT '累计访问次数' AFTER `totalStockOut`;
ALTER TABLE xhMain ADD `totalVisit` INT(11) NOT NULL DEFAULT '0' COMMENT '累计访客数，每个IP一个访客' AFTER `totalStockOut`;
ALTER TABLE xhMain ADD `totalUser` INT(11) NOT NULL DEFAULT '0' COMMENT '总用户；总客户数' AFTER `totalStockOut`;
ALTER TABLE xhMain ADD `totalPurchaseOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '总采购订单' AFTER `totalStockOut`;
ALTER TABLE xhMain ADD `totalPdAdd` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '累计盘盈' AFTER `totalStockOut`;
ALTER TABLE xhMain ADD `totalPdSub` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '累计盘亏' AFTER `totalStockOut`;
ALTER TABLE xhMain ADD `totalWast` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '累计报损' AFTER `totalStockOut`;
ALTER TABLE xhMain ADD `totalRefund` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '累计退款金额' AFTER `totalStockOut`;

ALTER TABLE xhHdRefundItem ADD unitPrice DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '单价' AFTER `unitName`;
ALTER TABLE xhHdRefundItem ADD price DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '总金额' AFTER `unitPrice`;
ALTER TABLE xhHdRefundGoods ADD unitPrice DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '单价' AFTER `num`;
ALTER TABLE xhHdRefundGoods ADD price DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '总金额' AFTER `unitPrice`;

ALTER TABLE xhHdRefundItem MODIFY `num` INT NOT NULL DEFAULT 0 COMMENT '花材数量';

ALTER TABLE xhGoodsCategory ADD flowerNum SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '多少朵' AFTER `flower`;

alter table xhShop drop column openTime;

ALTER TABLE xhAd ADD mainId INT NOT NULL DEFAULT 0 COMMENT '' AFTER `sjId`;

ALTER TABLE xhAdmin ADD `currentHdShopId` INT(11) NOT NULL DEFAULT '0' COMMENT '当前管理的门店' AFTER `openShop`;
ALTER TABLE xhAdmin ADD `openHdShop` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '申请开店 1没有 2有申请，待审核 3有申请，已审核通过' AFTER `currentHdShopId`;
ALTER TABLE xhAdmin ADD `currentGhsShopId` INT(11) NOT NULL DEFAULT '0' COMMENT '当前管理的门店' AFTER `openHdShop`;
ALTER TABLE xhAdmin ADD `openGhsShop` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '申请开店 1没有 2有申请，待审核 3有申请，已审核通过' AFTER `currentGhsShopId`;

ALTER TABLE xhGoodsSetting DROP INDEX `merchantId`;
ALTER TABLE xhAdminRole ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `sjId`;

-- ssh 20220424