DROP TABLE IF EXISTS `xhMain`; CREATE TABLE IF NOT EXISTS `xhMain`( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `balance` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '余额', `freezeBalance` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '提现时被冻结的余额', `txBalance` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '可提现余额', `fixBalance` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '只能向推荐人消费的余额，指定余额', `totalRecharge` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计充值', `totalIncome` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计收入', `totalExpend` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '总支出', `totalItem` INT(11) NOT NULL DEFAULT '0' COMMENT '总花材数', `totalSale` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计销售额', `totalStockIn` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计入库', `totalStockOut` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计出库', `cashName` varchar(100) NOT NULL DEFAULT '' COMMENT '收款人姓名', `cashAccount` varchar(50) NOT NULL DEFAULT '' COMMENT '银行卡号，银行帐号', `cashBank` varchar(50) NOT NULL DEFAULT '' COMMENT '开户行', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )COMMENT='中央，中控'; ============ shish 20220311 ALTER TABLE xhStatSale ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatSaleMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatUser ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatUserMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatOut ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatOutMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhPtYeChange ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhShopYeChange ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhShopCapital ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhCash ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhRecharge ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStaff ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhGhsStatYj ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhGhsStatYjMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatVisit ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatVisitMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhGhsStatOrderCount ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatOrder ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatOrderCount ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatOrderMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatIncome ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatIncomeMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ============ 资产相关表中央化 shish 20220313 执行供货商资产中央化 ./yii init/ghs-center 【库存也要中央化处理，要加入进来】!!!! ALTER TABLE xhStaff COMMENT '员工'; ALTER TABLE xhGhsItem DROP COLUMN `oneRatio`; ALTER TABLE xhGhsItem ADD smallRatio SMALLINT(6) NOT NULL DEFAULT '1' COMMENT '小单位价格系数，一支花价格系数，一支花=(skPrice/ratio)*smallRatio' AFTER `rank`; ALTER TABLE xhCategory ADD property TINYINT NOT NULL DEFAULT 0 COMMENT '属性 0鲜花类 1盆栽绿植花瓶类' AFTER `sjId`; ALTER TABLE xhGoods ADD property TINYINT NOT NULL DEFAULT 0 COMMENT '属性 0鲜花类 1盆栽绿植花瓶类' AFTER `sjId`; DROP TABLE IF EXISTS `xhWork`; CREATE TABLE IF NOT EXISTS `xhWork`( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `makeSn` CHAR(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '工单编号', `orderSn` CHAR(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '关联订单编号', `orderId` INT NOT NULL DEFAULT 0 COMMENT '关联订单id', `num` SMALLINT NOT NULL DEFAULT 0 COMMENT '数量', `categoryId` INT NOT NULL DEFAULT 0 COMMENT '分类id', `goodsId` INT NOT NULL DEFAULT 0 COMMENT '商品id', `from` TINYINT NOT NULL DEFAULT 0 COMMENT '来源 0门店下单 1小程序下单...', `remark` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '备注', `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0待制作 1已完成 2已取消', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' )COMMENT='工单'; DROP TABLE IF EXISTS `xhWorkItem`; CREATE TABLE IF NOT EXISTS `xhWorkItem`( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称', `cover` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '花材封面', `workSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '工单编号', `itemId` INT(11) NOT NULL DEFAULT '0' COMMENT '花材id', `ptItemId` INT(11) NOT NULL DEFAULT '0' COMMENT '平台花材id', `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id', `shopId` INT(11) NOT NULL DEFAULT '0' COMMENT '门店id', `mainId` INT(11) NOT NULL DEFAULT '0' COMMENT '中央id', `num` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '数量，包括大小单位总合', `cost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '花材单位成本', `bigPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '大单位价格', `smallPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '小单位价格', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' )COMMENT='工单花材明细'; ALTER TABLE xhGoodsItem DROP COLUMN `productId`; ALTER TABLE xhGoodsItem DROP COLUMN `itemId`; ALTER TABLE xhGoodsItem ADD itemId INT NOT NULL DEFAULT 0 COMMENT '花材id' AFTER `goodsId`; ALTER TABLE xhGoodsItem ADD ptItemId INT NOT NULL DEFAULT 0 COMMENT '平台花材id' AFTER `itemId`; DROP TABLE IF EXISTS `xhHdRefund`; CREATE TABLE IF NOT EXISTS `xhHdRefund` ( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id', `shopId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '门店ID', `customId` INT(11) NOT NULL DEFAULT '0' COMMENT '客户id', `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款订单编号', `orderSn` CHAR(30) NOT NULL DEFAULT '' COMMENT '关联订单编号', `orderId` INT(11) NOT NULL DEFAULT '0' COMMENT '关联订单id', `status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单状态： 0处理中 1已完成', `shopAdminId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '员工id', `shopAdminName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '员工名称', `refundPrice` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额', `refundType` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '退款类型： 1退货并退款，2仅退款', `remark` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '备注信息', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间' )COMMENT='零售退款单'; DROP TABLE IF EXISTS `xhHdRefundItem`; CREATE TABLE IF NOT EXISTS `xhHdRefundItem`( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款单号', `refundId` INT NOT NULL DEFAULT 0 COMMENT '退款单id', `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称', `itemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '花材ID', `ptItemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '平台花材ID', `num` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '花材数量', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' )COMMENT='退款花材'; DROP TABLE IF EXISTS `xhHdRefundGoods`; CREATE TABLE IF NOT EXISTS `xhHdRefundGoods`( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款单号', `refundId` INT NOT NULL DEFAULT 0 COMMENT '退款单id', `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称', `goodsId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '商品ID', `num` INT NOT NULL DEFAULT '0.00' COMMENT '数量', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' )COMMENT='退款商品'; DROP TABLE IF EXISTS `xhMallRefund`; CREATE TABLE IF NOT EXISTS `xhMallRefund` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id', `shopId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '门店ID', `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款订单编号', `orderSn` CHAR(30) NOT NULL DEFAULT '' COMMENT '关联订单编号', `orderId` INT(11) NOT NULL DEFAULT '0' COMMENT '关联订单id', `status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单状态： 0处理中 1已完成', `refundPrice` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额', `refundType` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '退款类型： 1退货并退款，2仅退款', `remark` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '备注信息', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间' )COMMENT='商城退款单'; DROP TABLE IF EXISTS `xhMallRefundItem`; CREATE TABLE IF NOT EXISTS `xhMallRefundItem`( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款单号', `refundId` INT NOT NULL DEFAULT 0 COMMENT '退款单id', `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称', `itemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '花材ID', `ptItemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '平台花材ID', `num` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '花材数量', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' )COMMENT='退款花材'; DROP TABLE IF EXISTS `xhMallRefundGoods`; CREATE TABLE IF NOT EXISTS `xhMallRefundGoods`( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款单号', `refundId` INT NOT NULL DEFAULT 0 COMMENT '退款单id', `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称', `goodsId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '商品ID', `num` INT NOT NULL DEFAULT '0.00' COMMENT '数量', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' )COMMENT='退款商品'; DROP TABLE IF EXISTS `xhGhsCgRefund`; CREATE TABLE `xhGhsCgRefund` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id', `shopId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '门店ID', `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款订单编号', `thirdRefundNo` CHAR(50) NOT NULL DEFAULT '' COMMENT '第三方退款id', `status` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单状态 0处理中 1已完成', `shopAdminId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '员工id', `shopAdminName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '员工名称', `num` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '数量', `refundPrice` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额', `refundType` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '退款类型： 1退货并退款，2仅退款', `remark` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '备注信息', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间', PRIMARY KEY (`id`), UNIQUE KEY `uni_orderNum` (`refundSn`) ) COMMENT='供应商采购退款单'; DROP TABLE IF EXISTS `xhGhsCgRefundItem`; CREATE TABLE `xhGhsCgRefundItem` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `refundSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '退款单编号', `name` VARCHAR(300) NOT NULL DEFAULT '' COMMENT '名称', `cover` VARCHAR(600) NOT NULL DEFAULT '' COMMENT '封面', `itemId` INT(11) NOT NULL DEFAULT '0' COMMENT '花材ID', `ptItemId` INT(11) NOT NULL DEFAULT '0' COMMENT '平台花材ID', `num` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '花材数量', PRIMARY KEY (`id`), KEY `idx_orderNum` (`refundSn`) USING BTREE, KEY `idx_goodsItemId` (`itemId`) USING BTREE ) COMMENT='供应商采购退款详情'; DROP TABLE IF EXISTS `xhGhsCgRefundSn`; CREATE TABLE `xhGhsCgRefundSn` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) COMMENT='供应商采购退款单号'; ============ shish 20220314 DROP TABLE IF EXISTS `xhMyLs`; CREATE TABLE IF NOT EXISTS `xhMyLs`( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bindId int not null default 0 comment '绑定id', sjId INT NOT NULL DEFAULT 0 COMMENT '商家id', shopId INT NOT NULL DEFAULT 0 COMMENT '门店id', ownerSjId INT NOT NULL DEFAULT 0 COMMENT '零售店所有者的商家id', ownerShopId INT NOT NULL DEFAULT 0 COMMENT '零售店所有者的门店id', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' )COMMENT='我的零售店'; DROP TABLE IF EXISTS `xhMyPf`; CREATE TABLE IF NOT EXISTS `xhMyPf`( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bindId int not null default 0 comment '绑定id', sjId INT NOT NULL DEFAULT 0 COMMENT '商家id', shopId INT NOT NULL DEFAULT 0 COMMENT '门店id', ownerSjId INT NOT NULL DEFAULT 0 COMMENT '零售店所有者的商家id', ownerShopId INT NOT NULL DEFAULT 0 COMMENT '零售店所有者的门店id', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' )COMMENT='我的批发店'; DROP TABLE IF EXISTS `xhMyPfLsBind`; CREATE TABLE IF NOT EXISTS `xhPfLsBind`( pfSjId INT NOT NULL DEFAULT 0 COMMENT '商家id', pfShopId INT NOT NULL DEFAULT 0 COMMENT '门店id', lsSjId INT NOT NULL DEFAULT 0 COMMENT '商家id', lsShopId INT NOT NULL DEFAULT 0 COMMENT '门店id', `status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待确认 1已同意 2已取消，不同意', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' )COMMENT='批发零售店绑定'; ALTER TABLE xhShop ADD `open` TINYINT NOT NULL DEFAULT 1 COMMENT '营业状态 0休息 1正常营业' AFTER `telephone`; ALTER TABLE xhShop ADD `openStartTime` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '营业开始时间，空表示24小时，3:30' AFTER `open`; ALTER TABLE xhShop ADD `openEndTime` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '营业结束时间 5:00' AFTER `openStartTime`; DROP TABLE IF EXISTS `xhGroup`; CREATE TABLE IF NOT EXISTS `xhGroup`( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `mainId` int not null default 0 comment '中央id', `sjId` int not null default 0 comment '商家id', `shopId` int not null default 0 comment '门店id', `name` VARCHAR(80) NOT NULL DEFAULT '' COMMENT '组合名称', `cover` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '封面图片', `intro` VARCHAR(900) NOT NULL DEFAULT '' COMMENT '简介', `price` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '价格', `num` INT NOT NULL DEFAULT 0 COMMENT '开单次数', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' )COMMENT='商品花材组合'; DROP TABLE IF EXISTS `xhGroupItem`; CREATE TABLE `xhGroupItem` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `groupId` INT(11) NOT NULL DEFAULT '0' COMMENT '组合id', `name` VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '花材名称', `itemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '花材ID', `ptItemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '平台花材ID', `num` INT NOT NULL DEFAULT 0 COMMENT '当前数量', `unitType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '当前单位类型 0大单位 1小单位', `unitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位价格，单价', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`) ) COMMENT='常用组合的花材'; DROP TABLE IF EXISTS `xhGroupGoods`; CREATE TABLE IF NOT EXISTS `xhGroupGoods`( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `groupId` INT NOT NULL DEFAULT 0 COMMENT '组合id', `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '花材名称', `goodsId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '商品ID', `num` INT NOT NULL DEFAULT '0.00' COMMENT '数量', `unitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位价格，单价', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' )COMMENT='组合商品'; ALTER TABLE `xhGroup` ADD INDEX idx_shopId (`shopId`,`addTime`); ALTER TABLE xhGoods ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`; ALTER TABLE xhGoodsCategory ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`; ALTER TABLE xhGoods ADD cover VARCHAR(800) NOT NULL DEFAULT '' COMMENT '封面' AFTER `goodsName`; ALTER TABLE xhGoods MODIFY `property` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '属性 0鲜花成品 1盆栽绿植花瓶 2花材'; ALTER TABLE xhGhsItem ADD `property` TINYINT(4) NOT NULL DEFAULT '2' COMMENT '属性 0鲜花成品 1盆栽绿植花瓶 2花材' AFTER `cover`; ALTER TABLE xhGoods MODIFY `property` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '属性 0成品 1花材，前端开发区分使用'; ALTER TABLE xhGhsItem MODIFY `property` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '属性 0成品 1花材，前端开发区分使用'; ALTER TABLE xhGoods ADD flower TINYINT NOT NULL DEFAULT 0 COMMENT '0非鲜花成品，绿植盆栽花瓶 1鲜花成品' AFTER `property`; ALTER TABLE xhStatCg ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatCgGhs ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatCgGhsMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatCgMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatKd ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhStatKdMonth ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`; ALTER TABLE xhGoods CHANGE `goodsName` `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品名称'; ============ 批发零售绑定关系 shish 20220314 ========================花材相关表结构变更start=======linqh 20220315================= ALTER TABLE `xhGhsItem` ADD COLUMN `mainId` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `shopId`, ADD INDEX `idx_mainId` (`mainId`) ; ALTER TABLE `xhGhsItemClass` ADD COLUMN `mainId` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '中央ID' AFTER `sjId`, ADD INDEX `idx_mainId` (`mainId`) ; ALTER TABLE `xhGhsItemRank` ADD COLUMN `mainId` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '中央ID' AFTER `sjId`, ADD INDEX `idx_mainId` (`mainId`) ; ALTER TABLE `xhGhsStockRecord` ADD COLUMN `mainId` int(11) NOT NULL AFTER `shopId`, ADD INDEX `idx_mainId` (`mainId`) ; ALTER TABLE `xhGhsStockRecord` MODIFY COLUMN `mainId` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '中央ID' AFTER `shopId`; CREATE TABLE `xhGoodsStockRecord` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `sjId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '商家ID', `shopId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '门店ID', `mainId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '中央ID', `recordType` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '记录类型 1制作入口（采购），2出库，3入库，4盘点 5销售（开单）', `orderSn` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '订单号', `goodsId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '成品ID', `goodsNum` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '成品数量', `oldStock` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '原库存数量', `newStock` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '新库存数量', `addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', `extend` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '扩展字段', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='成品库存记录表'; ===============花材相关表结构变更end======== ==========客户的花店 shish 20220316 DROP TABLE IF EXISTS `xhHd`; CREATE TABLE `xhHd` ( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `userId` INT NOT NULL DEFAULT 0 COMMENT '用户id', `sjId` INT NOT NULL DEFAULT 0 COMMENT '商家id', `shopId` INT NOT NULL DEFAULT 0 COMMENT '门店id', `avatar` VARCHAR(800) NOT NULL DEFAULT '' COMMENT '头像', `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '供货商名称', `py` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '名称拼音首字母', `mobile` CHAR(15) NOT NULL DEFAULT '' COMMENT '手机号', `province` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '省', `city` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '市', `dist` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '区县', `address` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '街道地址', `floor` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '楼号门牌号', `fullAddress` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '完整地址', `showAddress` VARCHAR(900) NOT NULL DEFAULT '' COMMENT '地图接口显示的完整地址', `lat` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '纬度', `long` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '经度', `debt` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '1没有欠款 2有欠款', `debtNum` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '欠款订单数，欠款笔数', `debtAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '欠款金额', `giveLevel` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '客户等级', `giveDiscount` DECIMAL(3,2) NOT NULL DEFAULT '1.00' COMMENT '提供的折扣', `expendAmount` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '消费金额', `expendNum` INT(11) NOT NULL DEFAULT '0' COMMENT '消费次数', `black` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '列入黑名单 1没有 2是', `inTurn` INT(10) NOT NULL DEFAULT '100' COMMENT '排序', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' ) COMMENT='客户的花店'; ALTER TABLE xhOrder ADD `thirdNo` CHAR(80) NOT NULL DEFAULT '' COMMENT '支付回调编号，微信支付回调的transaction_id' AFTER `orderType`; ALTER TABLE xhOrder ADD `labourCost` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '人工和资材和包装费' AFTER `sendCost`; DROP TABLE IF EXISTS `xhOrderItem`; CREATE TABLE `xhOrderItem` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '名称', `cover` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '封面', `ratio` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '大小单位比例', `unitType` TINYINT NOT NULL DEFAULT 0 COMMENT '当前单位类型 0大单位 1小单位', `unitName` CHAR(10) NOT NULL DEFAULT '' COMMENT '当前单位名称', `unitId` INT NOT NULL DEFAULT 0 COMMENT '当前单位id', `unitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '当前单位价格，单价', `num` INT NOT NULL DEFAULT 0 COMMENT '当前单位数量', `orderSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '订单编号', `itemId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家花材id', `ptItemId` INT NOT NULL DEFAULT 0 COMMENT '平台花材id', `count` DECIMAL(10,2) NOT NULL DEFAULT '0.00' COMMENT '数量，转换后带小数点的数量', `price` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '总价', `customId` INT(11) NOT NULL DEFAULT '0' COMMENT '客户id', `hdId` INT NOT NULL DEFAULT 0 COMMENT '花店id', `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商户id', `shopId` INT NOT NULL DEFAULT 0 COMMENT '门店id', `mainId` INT NOT NULL DEFAULT 0 COMMENT '中央id', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', PRIMARY KEY (`id`) )COMMENT='零售订单花材详情'; DROP TABLE IF EXISTS `xhOrderGoods`; CREATE TABLE `xhOrderGoods` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单id', `orderSn` CHAR(20) NOT NULL DEFAULT '' COMMENT '订单编号', `goodsId` INT(11) NOT NULL DEFAULT '0' COMMENT '商品id，备用字段', `customId` INT(11) NOT NULL DEFAULT '0' COMMENT '客户id', `hdId` INT NOT NULL DEFAULT 0 COMMENT '花店id', `sjId` INT(11) NOT NULL DEFAULT '0' COMMENT '商户id', `shopId` INT NOT NULL DEFAULT 0 COMMENT '门店id', `mainId` INT NOT NULL DEFAULT 0 COMMENT '中央id', `name` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '名称', `cover` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '封面', `unitPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '单价', `num` INT(11) NOT NULL DEFAULT '0' COMMENT '数量', `price` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '总价', `addTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `updateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', PRIMARY KEY (`id`) ) COMMENT='零售订单商品明细'; ALTER TABLE xhCustom ADD hdId INT NOT NULL DEFAULT 0 COMMENT '花店id' AFTER `id`; ALTER TABLE xhHd ADD customId INT NOT NULL DEFAULT 0 COMMENT '客户id' AFTER `id`; ALTER TABLE xhOrder ADD hdId INT NOT NULL DEFAULT 0 COMMENT '花店id' AFTER `discountType`; ALTER TABLE xhOrder ADD hdName VARCHAR(50) NOT NULL DEFAULT '' COMMENT '花店名称' AFTER `hdId`; ALTER TABLE xhOrder ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`; ALTER TABLE xhStatIncomeMonth DROP INDEX `shop`; ALTER TABLE xhStatIncome DROP INDEX `shop`; ALTER TABLE xhStatIncome ADD INDEX idx_mainId(`mainId`,`time`); ALTER TABLE xhStatIncomeMonth ADD INDEX idx_mainId(`mainId`,`time`); UPDATE xhGoods SET stock=0 WHERE stock=-100000; ALTER TABLE xhGhsStockRecord MODIFY `itemId` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '平台花材ID'; ALTER TABLE xhStatOrder DROP INDEX `shop`; ALTER TABLE xhStatOrder ADD INDEX `mainId`(`mainId`,`time`); ALTER TABLE xhStatOrderCount DROP INDEX `shop`; ALTER TABLE xhStatOrderCount ADD INDEX `mainId`(`mainId`,`time`); ALTER TABLE xhStatOrderMonth DROP INDEX `shop`; ALTER TABLE xhStatOrderMonth ADD INDEX `mainId`(`mainId`,`time`); ====shish 20220319 ALTER TABLE xhWastage ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`; ALTER TABLE xhWastageItem ADD mainId INT NOT NULL DEFAULT 0 COMMENT '中央id' AFTER `id`; ====报损中央化 shish 20220319