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