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 `xhMake`;
CREATE TABLE IF NOT EXISTS `xhMake`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`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 `xhMakeItem`;
CREATE TABLE IF NOT EXISTS `xhMakeItem`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称',
`cover` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '花材封面',
`makeSn` 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',
  `orderSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款订单编号',
  `cgId` INT(11) NOT NULL DEFAULT '0' COMMENT '采购单id',
  `saleRefundId` INT(11) NOT NULL DEFAULT '0' COMMENT '供应商退款单id',
  `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` (`orderSn`)
) COMMENT='供应商采购退款单';
DROP TABLE IF EXISTS `xhGhsCgRefundItem`;
CREATE TABLE `xhGhsCgRefundItem` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `orderSn` 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',
  `itemNum` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '花材数量',
  `itemStock` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '当时的库存',
  `itemPrice` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '当时的售卖价格',
  `totalPrice` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '总售卖价',
  `itemCost` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '当时的成本价格',
  `newStock` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '变更后的库存',
  PRIMARY KEY (`id`),
  KEY `idx_orderNum` (`orderSn`) 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