<?php

namespace console\controllers;

use biz\goods\services\GoodsCategoryService;
use biz\goods\services\GoodsService;
use biz\goods\services\GoodsStyleService;
use biz\merchant\services\MerchantCapitalService;
use biz\merchant\services\MerchantExtendService;
use biz\order\services\OrderGoodsService;
use biz\order\services\OrderService;
use biz\stat\services\StatIncomeMonthService;
use biz\stat\services\StatIncomeSourceMonthService;
use biz\stat\services\StatOrderMonthService;
use biz\stat\services\StatOrderService;
use biz\user\services\UserAssetService;
use biz\user\services\UserCapitalService;
use biz\user\services\UserGrowthService;
use biz\user\services\UserService;
use common\components\dirUtil;
use common\components\util;
use common\services\xhMerchantService;
use common\services\xhWxMenuService;
use Yii;
use yii\console\Controller;

class UpgradeController extends Controller
{

	//执行脚本方式
	// ./yii upgrade/init
	public function actionInit()
	{
		if (getenv('YII_ENV') == 'dev') {
			util::stop('开发环境不允许初始化');
		}

		//更新积分 shish 2019.11.22
		$sql = "ALTER TABLE xhUserAsset ADD growth INT NOT NULL DEFAULT 0 COMMENT '成长值' AFTER `point`;";
		$sql .= "UPDATE `xhUserAsset` SET `growth`=`integral`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		//shish 2019.11.23
		$sql = "DROP TABLE IF EXISTS xhAuth;
CREATE TABLE xhAuth(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
endId TINYINT NOT NULL DEFAULT 0 COMMENT '业务端，零售1',
parentId INT NOT NULL DEFAULT 0 COMMENT '权限父id',
authName VARCHAR(30) NOT NULL DEFAULT '' COMMENT '权限名称',
front_url VARCHAR(30) NOT NULL DEFAULT '' COMMENT '前端路由',
back_url VARCHAR(30) NOT NULL DEFAULT '' COMMENT '后端路由',
`addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
)COMMENT='各业务端的权限';
DROP TABLE IF EXISTS xhSetAuth;
CREATE TABLE xhSetAuth(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
setId TINYINT NOT NULL DEFAULT 0 COMMENT '套餐id',
endId TINYINT NOT NULL DEFAULT 0 COMMENT '业务端id',
auth VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '权限',
`addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
)COMMENT='套餐在各业务端的权限';
CREATE TABLE xhAdminRoleAuth(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
roleId INT NOT NULL DEFAULT 0 COMMENT '角色id',
auth VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '权限',
`addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
)COMMENT='员工角色权限';";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE xhAdminRoleAuth ADD endId TINYINT NOT NULL DEFAULT 0 COMMENT '业务端id' AFTER roleId;
ALTER TABLE xhMerchant ADD setId TINYINT NOT NULL DEFAULT 0 COMMENT '套餐id' AFTER `wxRefreshToken`;
ALTER TABLE xhMerchant ADD setDeadline INT NOT NULL DEFAULT 0 COMMENT '套餐到期时间' AFTER `setId`;
ALTER TABLE xhAuth CHANGE front_url frontUrl VARCHAR(30) NOT NULL DEFAULT '' COMMENT '前端路由';
ALTER TABLE xhAuth CHANGE back_url backUrl VARCHAR(30) NOT NULL DEFAULT '' COMMENT '后端路由';
ALTER TABLE xhAdminRole CHANGE authority auth VARCHAR(500) NOT NULL DEFAULT '' COMMENT '权限id列表，存在*表示有所有权限';";
		Yii::$app->db->createCommand($sql)->execute();
		
		//注意国兰花尚的全部订单已经完成，并将全部订单置为已完成
		$sql = "ALTER TABLE xhOrder MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态 0待付款 1待配送 2配送中 3待通知 4已取消 5已完成';";
		$sql .= "update xhOrder set `status`=5;";
		$sql .= "DROP TABLE IF EXISTS xhCategoryRelate;
ALTER TABLE xhCategory ADD merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id' AFTER parentId;
DROP TABLE IF EXISTS xhGoodsUsage;
DROP TABLE IF EXISTS xhUsage;
DROP TABLE IF EXISTS xhUsageRelation;
DROP TABLE IF EXISTS xhStatIncomeUsage;
DROP TABLE IF EXISTS xhStatIncomeUsageMonth;
ALTER TABLE xhOrder ADD refund TINYINT NOT NULL DEFAULT 0 COMMENT '1已退款 0正常订单，没有退款' AFTER luckyNum;
RENAME TABLE xhGoodsPrice TO xhGoodsStyle;
ALTER TABLE xhGoodsStyle COMMENT '商品款式';
ALTER TABLE xhGoodsStyle ADD goodsId INT NOT NULL DEFAULT 0 COMMENT '商品id' AFTER `id`;
DROP TABLE IF EXISTS `xhGoodsUsage`;
ALTER TABLE xhOrder ADD INDEX merchant(merchantId,`status`);
ALTER TABLE xhOrder ADD INDEX userId(userId,`status`);
ALTER TABLE xhUserAsset ADD inviteNum INT NOT NULL DEFAULT 0 COMMENT '邀请新人数量' AFTER `discount`;
ALTER TABLE xhAdmin MODIFY `password` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '密码';
ALTER TABLE xhAdmin MODIFY `confirmPassword` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '确认密码';
ALTER TABLE `xhUser` ADD INDEX mobile(merchantId,`mobile`);
ALTER TABLE xhUserAsset DROP COLUMN `point`;
ALTER TABLE xhUserAsset MODIFY `integral` INT NOT NULL DEFAULT 0 COMMENT '积分';
ALTER TABLE xhUser MODIFY birthday DATE NOT NULL DEFAULT '0000-00-00' COMMENT '生日';
ALTER TABLE xhUserAsset DROP COLUMN `uniqueQrCode`;
ALTER TABLE xhUserAsset DROP COLUMN `uniqueCode`;
ALTER TABLE xhMerchantExtend ADD gradeSwitch TINYINT NOT NULL DEFAULT 1 COMMENT '会员等级修改开关 0不可再修改 1可修改' AFTER gradeList;
DROP TABLE IF EXISTS xhUsage;
CREATE TABLE `xhUsage` (
  `id` INT(11) NOT NULL,
  `usageName` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用途名称',
  `addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
) COMMENT='商品用途';
INSERT INTO `xhUsage` (`id`, `usageName`, `addTime`) VALUES
(1, '送爱人/恋人', 1567955291),
(2, '开业', 1567955291),
(3, '婚庆', 1567955291),
(4, '家养', 1567955291),
(5, '生日', 1568038612),
(6, '会议', 1568038612),
(7, '送母亲', 1568038612),
(8, '送老师', 1568038612),
(9, '其它', 1568038612);
DROP TABLE IF EXISTS xhAd;
CREATE TABLE IF NOT EXISTS xhAd(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`adName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '广告名称',
`merchantId` INT NOT NULL DEFAULT 0 COMMENT '商家id',
`type` TINYINT NOT NULL DEFAULT 0 COMMENT '广告类型 0商城首页轮播图 1门店轮播图',
`style` TINYINT NOT NULL DEFAULT 0 COMMENT '0 url 1商品id',
`url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '链接',
`goodsId` INT NOT NULL DEFAULT 0 COMMENT '商品id',
`startTime` INT NOT NULL DEFAULT 0 COMMENT '开始时间',
`endTime` INT NOT NULL DEFAULT 0 COMMENT '结束时间，方便查询，永不过期 使用值4102416000即2100-01-01 00:00:00',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='广告管理';
ALTER TABLE xhAd ADD INDEX `merchantId`(`merchantId`,`endTime`);
DROP TABLE IF EXISTS `xhCategoryRelate`;
ALTER TABLE xhCategory ADD indexShow TINYINT NOT NULL DEFAULT 0 COMMENT '首页上显示 0不显示 1显示' AFTER categoryName;
ALTER TABLE xhCategory ADD menuShow TINYINT NOT NULL DEFAULT 0 COMMENT '菜单显示 0不显示 1显示' AFTER `indexShow`;
ALTER TABLE xhCategory ADD INDEX indexShow(merchantId,indexShow);
ALTER TABLE xhCategory ADD INDEX menuShow(merchantId,menuShow);
ALTER TABLE xhAd ADD adImg VARCHAR(800) NOT NULL DEFAULT '' COMMENT '广告图' AFTER adName;
ALTER TABLE xhAd MODIFY endTime INT NOT NULL DEFAULT 0 COMMENT '结束时间；方便查询，永不过期情况，使用 开始值0 结束值4102416000即2100-01-01 00:00:00';
ALTER TABLE xhGoodsCategory MODIFY `cId` INT NOT NULL DEFAULT 0 COMMENT '分类id';
ALTER TABLE xhGoods DROP COLUMN `categoryId`;
ALTER TABLE xhGoodsCategory ADD INDEX merchantId(merchantId);
ALTER TABLE xhCategory DROP COLUMN `indexShow`;
ALTER TABLE xhCategory DROP COLUMN `menuShow`;
ALTER TABLE xhCategory ADD `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0停用1启用';
ALTER TABLE xhCategory DROP INDEX `indexShow`;
ALTER TABLE xhCategory DROP INDEX `menuShow`;
ALTER TABLE xhCategory ADD INDEX merchantId(merchantId,`status`);
ALTER TABLE xhGoodsCategory DROP INDEX merchantId;
ALTER TABLE xhGoodsCategory ADD INDEX merchantId(merchantId,cId);
ALTER TABLE xhGoods DROP COLUMN `cover`;
ALTER TABLE xhGoods ADD sendType TINYINT NOT NULL DEFAULT 0 COMMENT '配送方式 0需要配送 1到店自提' AFTER sendArea;
ALTER TABLE xhGoods ADD freightType TINYINT NOT NULL DEFAULT 0 COMMENT '运费计算方式，0按距离计算 1免运费' AFTER sendType;
DROP TABLE IF EXISTS `xhCouponModel`;
DROP TABLE IF EXISTS `xhCouponReward`;
DROP TABLE IF EXISTS `xhCouponRight`;
ALTER TABLE xhCoupon DROP COLUMN `type`;
ALTER TABLE xhCoupon DROP COLUMN `property`;
ALTER TABLE xhCoupon DROP COLUMN `fitType`;
ALTER TABLE xhCoupon DROP COLUMN `rightId`;
ALTER TABLE xhCoupon DROP COLUMN `modelId`;
ALTER TABLE xhCoupon DROP COLUMN `reward`;
ALTER TABLE xhCoupon DROP COLUMN `urlQrCode`;
ALTER TABLE xhCoupon CHANGE howToGet getType TINYINT NOT NULL DEFAULT 0 COMMENT '获得优惠券的方式';
ALTER TABLE xhCoupon DROP COLUMN `targetId`;
ALTER TABLE xhCoupon MODIFY deadline INT NOT NULL DEFAULT 0 COMMENT '过期时间；值4102416000表示永不过期，即2100-01-01 00:00:00';
ALTER TABLE xhCoupon ADD INDEX userId(userId,`status`);
ALTER TABLE xhCoupon ADD INDEX merchantId(merchantId,`status`);";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE xhGoods CHANGE `style` `goodsStyle` TINYINT NOT NULL DEFAULT 0 COMMENT '商品款式 0单款式 1多款式';
ALTER TABLE xhGoodsStyle CHANGE `order` `inTurn` INT NOT NULL DEFAULT 0 COMMENT '排序';
ALTER TABLE xhGoodsStyle DROP COLUMN `softDel`;
UPDATE xhUserAsset SET `discount`=1;
ALTER TABLE xhUserAsset MODIFY discount FLOAT(3,2) NOT NULL DEFAULT 1.00 COMMENT '折扣';
ALTER TABLE xhCoupon CHANGE `meetAmount` `miniCost` INT NOT NULL DEFAULT 0 COMMENT '最低消费金额，0表示任何情况下可用';
ALTER TABLE xhShop CHANGE `businessName` `shopName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '门店名称';
ALTER TABLE xhShop CHANGE `branchName` `merchantName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商家名称';
ALTER TABLE xhShop DROP COLUMN `poiId`;
ALTER TABLE xhMerchantExtend ADD `freight` CHAR(10) NOT NULL DEFAULT '5_2' COMMENT '运费计算方式 5公里内免运费，超过每增加一公里+2' AFTER `alipayAccountName`;
ALTER TABLE xhMerchantExtend DROP COLUMN `giveCoupon`;
ALTER TABLE xhMerchantExtend DROP COLUMN `giveCouponPrice`;
ALTER TABLE xhMerchantExtend DROP COLUMN `couponMeetPrice`;
ALTER TABLE xhMerchantExtend DROP COLUMN `couponValidity`;
ALTER TABLE xhMerchantExtend ADD newCouponSwitch TINYINT NOT NULL DEFAULT 1 COMMENT '新人送优惠券 0未开启 1开启' AFTER `freight`;
ALTER TABLE xhMerchantExtend ADD newCouponAmount DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '新人优惠券金额' AFTER `newCouponSwitch`;
ALTER TABLE xhMerchantExtend ADD newCouponMiniCost DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '新人优惠券最低消费金额' AFTER `newCouponAmount`;
ALTER TABLE xhMerchantExtend ADD newCouponValidTime INT NOT NULL DEFAULT 0 COMMENT '新人优惠券有效期，时长为秒，0永不过期' AFTER `newCouponMiniCost`;
ALTER TABLE xhGoods DROP COLUMN `isCharge`;
ALTER TABLE xhGoods DROP COLUMN `sendArea`;
ALTER TABLE xhGoods CHANGE `sendType` needSend TINYINT NOT NULL DEFAULT 0 COMMENT '是否要配送 0免配送 1需要配送';
ALTER TABLE xhOrder CHANGE `needSend` `sendType` TINYINT NOT NULL DEFAULT 0 COMMENT '配送方式 0免配送 1自己送 2别人送';
ALTER TABLE xhOrder MODIFY reachPeriod TINYINT NOT NULL DEFAULT 0 COMMENT '0上午1下午2晚上';
ALTER TABLE xhOrder DROP COLUMN `blessing`;
ALTER TABLE xhOrder ADD cardInfo VARCHAR(500) NOT NULL DEFAULT '' COMMENT '贺卡内容' AFTER `needCard`;
ALTER TABLE xhOrder MODIFY `payWay` TINYINT NOT NULL DEFAULT 0 COMMENT '支付方式 0微信 1支付宝 2余额';
ALTER TABLE xhOrderGoods CHANGE `multipleTitle` `goodsStyleName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '款式名称';
ALTER TABLE xhOrderGoods CHANGE `multiPriceId` `goodsStyleId` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '款式id';
ALTER TABLE xhCoupon MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0未使用 1使用 2过期';
ALTER TABLE xhOrder DROP COLUMN `luckyNum`;
ALTER TABLE xhOrder DROP COLUMN `uniCode`;
ALTER TABLE xhOrder DROP COLUMN `useType`;
ALTER TABLE xhOrder MODIFY `payStatus` TINYINT NOT NULL DEFAULT 0 COMMENT '0待付款 1已付款';
RENAME TABLE xhUserIntegral TO xhUserGrowth;
ALTER TABLE xhUserGrowth CHANGE `integral` `growth` INT NOT NULL DEFAULT 0 COMMENT '当前成长值';
ALTER TABLE xhUserGrowth MODIFY `num` INT NOT NULL DEFAULT 0 COMMENT '增加或减少的成长值';
ALTER TABLE xhOrder DROP COLUMN `payStyle`;
ALTER TABLE xhGoods MODIFY `status` TINYINT NOT NULL DEFAULT 1 COMMENT '商品状态 0下架 1正常';
ALTER TABLE xhGoods DROP COLUMN `delStatus`;
ALTER TABLE xhGoodsCategory ADD INDEX `gId`(`gId`);
ALTER TABLE xhGoods ADD autoRise TINYINT NOT NULL DEFAULT 1 COMMENT '自动涨价 0不需要 1需要' AFTER `goodsStyle`;
ALTER TABLE `xhGoodsStyle` CHANGE `title` `styleName` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '款式名称';
ALTER TABLE xhAd ADD `status` TINYINT NOT NULL DEFAULT 0 COMMENT '启用状态 0 未启用 1 启用' AFTER `adName`;
ALTER TABLE xhAd ADD `inTurn` INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER `status`;
ALTER TABLE xhAd ADD `viewTimes` INT NOT NULL DEFAULT 0 COMMENT '点击次数' AFTER `adImg`;
ALTER TABLE `xhAd` CHANGE `startTime` `startTime` DATETIME NULL COMMENT '开始时间', CHANGE `endTime` `endTime` DATETIME NULL COMMENT '结束时间';
ALTER TABLE xhAd ADD `forever` TINYINT NOT NULL DEFAULT 0 COMMENT '永久有效：0 未启用 1 永久有效' AFTER `goodsId`;
DROP TABLE IF EXISTS xhPic;
CREATE TABLE IF NOT EXISTS xhPic(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
img VARCHAR(500) NOT NULL DEFAULT '' COMMENT '图片地址',
price DECIMAL(9,2) NOT NULL DEFAULT 0.00 COMMENT '价格',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品名称',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='图库；商品图库';
DROP TABLE IF EXISTS xhPicCategory;
CREATE TABLE IF NOT EXISTS xhPicCategory(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
pictureId INT NOT NULL DEFAULT 0 COMMENT '图片id',
categoryId INT NOT NULL DEFAULT 0 COMMENT '分类id'
)COMMENT='商品图与分类关系';
ALTER TABLE xhPic ADD inTurn INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER `name`;
ALTER TABLE xhPicCategory ADD inTurn INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER categoryId;
ALTER TABLE xhPicCategory CHANGE pictureId picId INT NOT NULL DEFAULT 0 COMMENT '图片id';
ALTER TABLE xhGoodsCategory ADD `status` TINYINT NOT NULL DEFAULT 1 COMMENT '上下架状态 0下架 1正常' AFTER `merchantId`;
ALTER TABLE xhCategory ADD goodsNum INT NOT NULL DEFAULT 0 COMMENT '商品数' AFTER `cover`;
ALTER TABLE xhCategory ADD sold INT NOT NULL DEFAULT 0 COMMENT '销量' AFTER `goodsNum`;
ALTER TABLE xhCategory CHANGE cover `img` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '分类图片';";
		Yii::$app->db->createCommand($sql)->execute();
		
		//商品设置 员工 角色
		$sql = "ALTER TABLE xhGoodsSetting DROP COLUMN `startRiseTime`;
ALTER TABLE xhGoodsSetting DROP COLUMN `endRiseTime`;
ALTER TABLE xhGoodsSetting CHANGE `goodSIntroduce` `goodsIntroduce` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '商品说明';
ALTER TABLE xhGoodsSetting DROP COLUMN `createTime`;
ALTER TABLE xhGoodsSetting DROP COLUMN `updateTime`;
ALTER TABLE xhMerchantExtend DROP COLUMN `createTime`;
ALTER TABLE xhMerchantExtend DROP COLUMN `updateTime`;
ALTER TABLE xhGoodsSetting ADD riseSwitch TINYINT NOT NULL DEFAULT 1 COMMENT '自动涨价开关 1开启 0关闭' AFTER `merchantId`;
ALTER TABLE xhAdmin DROP COLUMN `openId`;
ALTER TABLE xhAdmin DROP COLUMN `unionId`;
ALTER TABLE xhAdmin DROP COLUMN `subscribe`;
ALTER TABLE xhAdmin DROP COLUMN `wxQrCode`;
ALTER TABLE xhAdmin MODIFY `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态 0停用 1正常';
ALTER TABLE xhAdmin ADD merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id' AFTER `roleId`;
DROP TABLE IF EXISTS `xhAdminToMerchant`;
ALTER TABLE xhAdmin ADD userId INT NOT NULL DEFAULT 0 COMMENT '员工关联的userId' AFTER merchantId;
ALTER TABLE xhAdmin ADD `addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间' AFTER `status`;
ALTER TABLE xhAdmin DROP COLUMN `createTime`;
ALTER TABLE xhAdmin DROP COLUMN `updateTime`;
ALTER TABLE xhAdmin DROP COLUMN `province`;
ALTER TABLE xhAdmin DROP COLUMN `city`;
ALTER TABLE xhAdmin DROP COLUMN `dist`;
ALTER TABLE xhAdmin DROP COLUMN `address`;
ALTER TABLE xhAdmin DROP COLUMN `addLong`;
ALTER TABLE xhAdmin DROP COLUMN `addLat`;
ALTER TABLE xhAdmin DROP COLUMN `addPrecision`;
ALTER TABLE xhAdmin ADD incomeNotice TINYINT NOT NULL DEFAULT 0 COMMENT '收入通知 0不需要 1需要' AFTER `currentShopId`;
ALTER TABLE xhAdminRole ADD `addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间' AFTER `merchantId`;
ALTER TABLE xhAdminRole DROP COLUMN `createTime`;
ALTER TABLE xhAdminRole DROP COLUMN `updateTime`;
ALTER TABLE xhAdmin DROP COLUMN `avatar`;
ALTER TABLE xhAdmin ADD loginTime INT NOT NULL DEFAULT 0 COMMENT '最后登陆时间' AFTER `status`;
ALTER TABLE xhAdmin ADD `super` TINYINT NOT NULL DEFAULT 0 COMMENT '是否超级管理员 0不是 1是，超级员工不可删除' AFTER `sex`;
ALTER TABLE xhAdminRole ADD `num` INT NOT NULL DEFAULT 0 COMMENT '成员数' AFTER `merchantId`;
CREATE TABLE xhAdminLog(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`ip` BIGINT NOT NULL DEFAULT 0 COMMENT 'ip地址',
`merchantId` INT NOT NULL DEFAULT 0 COMMENT '商家id',
`content` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '操作内容',
adminId INT NOT NULL DEFAULT 0 COMMENT '员工id；管理员id',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='员工操作日志';";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE xhCoupon MODIFY getType TINYINT NOT NULL DEFAULT 0 COMMENT '获取方式，0新人领券 1邀请领券';
DROP TABLE IF EXISTS `xhCouponAsset`;
CREATE TABLE IF NOT EXISTS `xhCouponAsset`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
totalNum INT NOT NULL DEFAULT 0 COMMENT '优惠券总数',
unUseNum INT NOT NULL DEFAULT 0 COMMENT '未使用数',
useNum INT NOT NULL DEFAULT 0 COMMENT '已使用数',
invalidNum INT NOT NULL DEFAULT 0 COMMENT '失效数',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='优惠券资产与设置';
DROP TABLE IF EXISTS `xhInvite`;
CREATE TABLE IF NOT EXISTS `xhInvite`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
`userName` varchar(50) NOT NULL DEFAULT '' COMMENT '邀请人的昵称',
mobile VARCHAR(20) NOT NULL DEFAULT '' COMMENT '邀请人手机号',
userId INT NOT NULL DEFAULT 0 COMMENT '受邀人',
introUserId INT NOT NULL DEFAULT 0 COMMENT '介绍人',
couponId INT NOT NULL DEFAULT 0 COMMENT '优惠券id',
prize VARCHAR(50) NOT NULL DEFAULT '' COMMENT '介绍人的奖励',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待下单 1待兑奖 2已完成',
orderTime INT NOT NULL DEFAULT 0 COMMENT '下单时间',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='邀请有礼记录';
DROP TABLE IF EXISTS xhInviteAsset;
CREATE TABLE IF NOT EXISTS `xhInviteAsset`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
switch TINYINT NOT NULL DEFAULT 1 COMMENT '是否开启邀请有礼 0关闭 1开启。默认开启',
amount INT NOT NULL DEFAULT 0 COMMENT '新人领券金额',
miniCost INT NOT NULL DEFAULT 0 COMMENT '最低消费',
duration SMALLINT NOT NULL DEFAULT 0 COMMENT '有效时长，天数',
prize VARCHAR(50) NOT NULL DEFAULT '' COMMENT '介绍人的奖励',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='商家邀请有礼设置';
DROP TABLE IF EXISTS `xhInviteStat`;
CREATE TABLE IF NOT EXISTS `xhInviteStat`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
userId INT NOT NULL DEFAULT 0 COMMENT '',
inviteNum INT NOT NULL DEFAULT 0 COMMENT '邀请数',
orderNum INT NOT NULL DEFAULT 0 COMMENT '下单数',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='邀请统计';
ALTER TABLE xhOrder ADD `grade` TINYINT NOT NULL DEFAULT 0 COMMENT '整体评分' AFTER `printStatus`;
ALTER TABLE xhOrder ADD `gradeService` TINYINT NOT NULL DEFAULT 0 COMMENT '服务态度评分' AFTER `grade`;
ALTER TABLE xhOrder ADD `gradeQuality` TINYINT NOT NULL DEFAULT 0 COMMENT '鲜花品质评分' AFTER `gradeService`;
ALTER TABLE xhOrder ADD `gradeSend` TINYINT NOT NULL DEFAULT 0 COMMENT '配送服务评分' AFTER `gradeQuality`;
ALTER TABLE xhOrder ADD `evaluate` VARCHAR(800) NOT NULL DEFAULT '' COMMENT '评语' AFTER `gradeSend`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "update xhMerchantExtend set gradeList='1_2000_0.9I2_5000_0.88I3_10000_0.85I4_20000_0.8';
DROP TABLE IF EXISTS `xhAgentLevel`;
CREATE TABLE IF NOT EXISTS xhAgentLevel(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '代理名称',
`commission` INT NOT NULL DEFAULT 0 COMMENT '佣金',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='代理等级';
DROP TABLE IF EXISTS `xhApply`;
CREATE TABLE IF NOT EXISTS `xhApply`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '花店名称',
`mobile` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
`userId` INT NOT NULL DEFAULT 0 COMMENT '花卉宝用户id',
`oldId` INT NOT NULL DEFAULT 0 COMMENT '老花店id；介绍人',
`fee` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '付费金额',
`income` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '介绍费',
`newId` INT NOT NULL DEFAULT 0 COMMENT '新花店id，0表示还没有审核通过',
`longitude` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '花店经度',
`latitude` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '花店纬度',
`province` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '省',
`city` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '市',
`dist` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '区县',
`address` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '详细街道地址',
`floor` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '楼号门牌号',
`fullAddress` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '地图中获取完整地址',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0审核中 1试用中 2已付费 3已失效',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='申请试用';
ALTER TABLE xhWxScene MODIFY `type` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '场景类型 0刮刮卡 1邀请有礼领券';
ALTER TABLE xhShop ADD `openTime` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '营业时间' AFTER `telephone`;
ALTER TABLE `xhAd` DROP INDEX `merchantId`;
ALTER TABLE `xhAd` ADD INDEX `merchantId`(`merchantId`,`type`);
ALTER TABLE `xhAd` ADD INDEX `multiple`(`merchantId`,`type`,`status`,`startTime`,`endTime`);
DELETE FROM `xhAd`;
ALTER TABLE `xhAd` MODIFY `startTime` INT NOT NULL DEFAULT 0 COMMENT '开始时间';
ALTER TABLE `xhAd` MODIFY `endTime` INT NOT NULL DEFAULT 0 COMMENT '结束时间；方便查询，永不过期情况，使用 开始值0 结束值4102416000即2100-01-01 00:00:00';
ALTER TABLE `xhAd` DROP COLUMN `forever`;
ALTER TABLE xhShop MODIFY shopAddress VARCHAR(200) NOT NULL DEFAULT '' COMMENT '街道地址';
ALTER TABLE xhShop ADD shopFullAddress VARCHAR(500) NOT NULL DEFAULT '' COMMENT '完整地址' AFTER shopAddress;
ALTER TABLE xhShop DROP COLUMN `status`;
DROP TABLE IF EXISTS `xhOrderSend`;
CREATE TABLE IF NOT EXISTS `xhOrderSend`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`orderId` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '订单号',
`actionId` TINYINT NOT NULL DEFAULT 0 COMMENT '动作id',
`option` TINYINT NOT NULL DEFAULT 0 COMMENT '动作的选项',
finishTime INT NOT NULL DEFAULT 0 COMMENT '完成时间',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='发货管理';
ALTER TABLE xhOrderSend ADD inTurn INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER `finishTime`;
ALTER TABLE xhOrderSend MODIFY `orderId` CHAR(32) NOT NULL DEFAULT '' COMMENT '订单号';
ALTER TABLE xhOrderGoods MODIFY `orderId` CHAR(32) NOT NULL DEFAULT '' COMMENT '订单号';
ALTER TABLE xhOrder MODIFY `id` CHAR(32) NOT NULL DEFAULT '' COMMENT '订单号';";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE xhOrder ADD totalFlow TINYINT NOT NULL DEFAULT 5 COMMENT '总流程数' AFTER `evaluate`;
ALTER TABLE xhOrder ADD currentFlow TINYINT NOT NULL DEFAULT 0 COMMENT '当前流程数' AFTER `totalFlow`;
UPDATE xhOrder SET totalFlow=5,currentFlow=5;
ALTER TABLE `xhOrder` DROP PRIMARY KEY;
ALTER TABLE `xhOrder` DROP  INDEX `id`;
ALTER TABLE `xhOrder` CHANGE `id` `orderSn` CHAR(32) NOT NULL DEFAULT '' COMMENT '订单编号';
ALTER TABLE `xhOrder` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自增id' FIRST;
ALTER TABLE `xhOrder` AUTO_INCREMENT=213800;
UPDATE `xhOrder` SET id=id+213800;
DELETE FROM `xhOrderSend`;
ALTER TABLE `xhOrderSend` MODIFY `orderId` INT NOT NULL DEFAULT 0 COMMENT '订单id';
ALTER TABLE xhCoupon MODIFY `orderId` INT NOT NULL DEFAULT 0 COMMENT '优惠券使用的订单id';
ALTER TABLE xhCoupon CHANGE `getType` `source` TINYINT NOT NULL DEFAULT 0 COMMENT '来源 0新人领券 1邀请有礼领取';
ALTER TABLE xhCoupon ADD sourceId INT NOT NULL DEFAULT 0 COMMENT '来源目标id' AFTER `source`;
ALTER TABLE xhInvite ADD introMobile VARCHAR(15) NOT NULL DEFAULT '' COMMENT '介绍人手机；邀请人手机' AFTER `introUserId`;
ALTER TABLE `xhCoupon` DROP COLUMN `introUserId`;
ALTER TABLE xhInvite ADD introUserName VARCHAR(30) NOT NULL DEFAULT '' COMMENT '邀请人昵称' AFTER `userId`;
RENAME TABLE xhGuaGua TO xhGgk;
RENAME TABLE xhGuaGuaAttend TO xhGgkAttend;
RENAME TABLE xhGuaGuaPrize TO xhGgkPrize;
RENAME TABLE xhGuaGuaWin TO xhGgkWin;
ALTER TABLE xhOrder CHANGE `modPriceStatus` `modPrice` TINYINT NOT NULL DEFAULT 1 COMMENT '0不能修改价格 1可以修改';
ALTER TABLE xhOrder DROP COLUMN `getCouponQrCode`;
ALTER TABLE xhOrder MODIFY `orderSn` CHAR(40) NOT NULL DEFAULT '' COMMENT '订单编号';
ALTER TABLE xhOrder MODIFY `receiveAddress` VARCHAR(300) NOT NULL DEFAULT '' COMMENT '收货地址 详细街道地址';
ALTER TABLE xhOrder MODIFY `receiveFullAddress` VARCHAR(400) NOT NULL DEFAULT '' COMMENT '完整收货地址';
ALTER TABLE xhOrder MODIFY `receiveFloor` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '收货地址 楼号门牌号' AFTER `receiveAddress`;
ALTER TABLE xhOrder MODIFY `sendType` TINYINT NOT NULL DEFAULT 0 COMMENT '配送方式 0免配送 1自己送 2别人送，快递送';
ALTER TABLE xhOrder ADD `sendSide` TINYINT NOT NULL DEFAULT 0 COMMENT '配送方 0没有 1达达' AFTER `sendType`;
ALTER TABLE xhOrder MODIFY `sendStatus` TINYINT NOT NULL DEFAULT 0 COMMENT '配送状态：0未发货，待接单 1已发货，已接单 2已送达' AFTER `sendSide`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE xhOrder ADD `reachDate` DATE DEFAULT NULL COMMENT '客户要求送到的日期' AFTER `sendDistance`;
ALTER TABLE xhOrder DROP COLUMN `reachTime`;
ALTER TABLE xhOrder MODIFY `reachPeriod` TINYINT NOT NULL DEFAULT 0 COMMENT '客户要求送到的时段：0上午1下午2晚上';
ALTER TABLE xhOrder ADD sendTip DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '运费里增加的小费' AFTER `sendCost`;
ALTER TABLE xhOrder MODIFY currentFlow TINYINT NOT NULL DEFAULT 0 COMMENT '完成流程数；当前流程数';
drop table if exists `xhStatIncomeUsage`;
CREATE TABLE if not exists `xhStatIncomeUsage` (
  `id` int(11) NOT NULL auto_increment primary key,
  `merchantId` int(11) NOT NULL DEFAULT '0' COMMENT '商家id',
  `shopId` int not null default 0 comment '门店id,0表示商家所有门店',
  `time` int(11) NOT NULL DEFAULT '0' COMMENT '时间',
  `usageId` tinyint(4) NOT NULL DEFAULT '0' COMMENT '用途id；UsageRelation id',
  `amount` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '收入金额',
  `weixin` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '微信收入',
  `alipay` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '支付宝收入',
  `balancePay` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '余额付款收入',
  `cash` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '现金收入',
  `totalAmount` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '总收入金额',
  `addTime` int(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
) COMMENT='按用途统计每天收入';
drop table if exists `xhStatIncomeUsageMonth`;
CREATE TABLE if not exists `xhStatIncomeUsageMonth` (
  `id` int(11) NOT NULL auto_increment primary key,
  `year` smallint(6) NOT NULL DEFAULT '0' COMMENT '年',
  `month` tinyint(4) NOT NULL DEFAULT '1' COMMENT '月份',
  `usageId` tinyint(4) NOT NULL DEFAULT '0' COMMENT '用途id',
  `amount` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '收入金额',
  `weixin` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '微信收入',
  `alipay` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '支付宝收入',
  `balancePay` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '余额付款收入',
  `cash` decimal(9,2) NOT NULL DEFAULT '0.00' COMMENT '现金收入',
  `merchantId` int(11) NOT NULL DEFAULT '0' COMMENT '商家id',
`shopId` int not null default 0 comment '门店id,0表示商家所有门店',
  `addTime` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '添加时间'
) COMMENT='按用途统计每月收入';
ALTER TABLE `xhMerchantCapital` MODIFY shopId INT NOT NULL DEFAULT 0 COMMENT '0表示商家所有门店';
ALTER TABLE `xhOrder` ADD `classify` TINYINT NOT NULL DEFAULT 0 COMMENT '是否归类 0未归类 1已归类' AFTER `usageId`;
ALTER TABLE xhUsage ADD `inTurn` INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER `usageName`;
ALTER TABLE `xhInvite` ADD amount DECIMAL(9,2) NOT NULL DEFAULT 0.00 COMMENT '下单金额' AFTER `couponId`;
ALTER TABLE `xhInvite` ADD giveTime INT NOT NULL DEFAULT 0 COMMENT '兑奖时间' AFTER `orderTime`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "DROP TABLE IF EXISTS `xhCouponAsset`;
CREATE TABLE IF NOT EXISTS `xhCouponAsset`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
`switch` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '是否开启邀请有礼 0关闭 1开启。默认开启',
  `amount` INT(11) NOT NULL DEFAULT '0' COMMENT '新人领券金额',
  `miniCost` INT(11) NOT NULL DEFAULT '0' COMMENT '最低消费',
  `duration` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '有效时长，天数',
  `addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
)COMMENT='新人优惠券资产';
ALTER TABLE `xhWxMenu` MODIFY `replyType` TINYINT NOT NULL DEFAULT 0 COMMENT '回复给粉丝的类型 0跳转链接 1文字 2单图文';
ALTER TABLE `xhWxMenu` DROP COLUMN `targetId`;
ALTER TABLE `xhWxMenu` ADD targetId INT NOT NULL DEFAULT 0 COMMENT '常用链接的目标id，活动目标id' AFTER `hasSonMenu`;
CREATE TABLE `xhUserRemark`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
userId INT NOT NULL DEFAULT 0 COMMENT '用户id',
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
remark VARCHAR(200) NOT NULL DEFAULT '' COMMENT '备注',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='用户备注';
ALTER TABLE `xhUserRemark` ADD INDEX `userId`(`userId`);
RENAME TABLE xhUserRemark TO xhMerchantRemark;
RENAME TABLE `xhStatOrderByDay` TO `xhStatOrder`;
RENAME TABLE `xhStatOrderSourceByDay` TO `xhStatOrderSource`;
RENAME TABLE `xhStatUserByDay` TO `xhStatUser`;
RENAME TABLE `xhStatViewByDay` TO `xhStatView`;
RENAME TABLE `xhStatViewSourceByDay` TO `xhStatViewSource`;
RENAME TABLE `xhStatVisitByDay` TO `xhStatVisit`;
RENAME TABLE `xhStatOutDay` TO `xhStatOut`;
RENAME TABLE `xhStatInDay` TO `xhStatIn`;
RENAME TABLE `xhStatDealByDay` TO `xhStatDeal`;
DROP TABLE IF EXISTS `xhStatDeal`;
DROP TABLE IF EXISTS `xhStatView`;
DROP TABLE IF EXISTS `xhStatViewSource`;
DROP TABLE IF EXISTS `xhStatVisit`;
RENAME TABLE `xhVisitByDay` TO `xhStatVisit`;
RENAME TABLE `xhVisitByMonth` TO `xhStatVisitMonth`;
DROP TABLE IF EXISTS `xhMerchantOutCapital`;
DROP TABLE IF EXISTS `xhMerchantInCapital`;
DROP TABLE IF EXISTS `xhStatOrder`;
RENAME TABLE `xhOrderDayNum` TO `xhStatOrder`;
RENAME TABLE `xhOrderMonthNum` TO `xhStatOrderMonth`;
RENAME TABLE `xhLuckyDrawActivity` TO `xhDraw`;
RENAME TABLE `xhLuckyDrawAward` TO `xhDrawPrize`;
RENAME TABLE `xhLuckyDrawAwardCustomer` TO `xhDrawWin`;
RENAME TABLE `xhLuckyDrawAwardParticipant` TO `xhDrawRecord`;
RENAME TABLE `xhDrawCash` TO `xhWithdraw`;
RENAME TABLE `xhActive` TO `xhAct`;
RENAME TABLE `xhActiveOrder` TO `xhActOrder`;
RENAME TABLE `xhActiveTicketClass` TO `xhActTicket`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhMerchantAsset` MODIFY `totalOrder` INT NOT NULL DEFAULT 0 COMMENT '订单数；已付款订单数';
ALTER TABLE `xhMerchantAsset` ADD cancelOrder INT NOT NULL DEFAULT 0 COMMENT '已取消订单数' AFTER `totalOrder`;
ALTER TABLE `xhMerchantAsset` ADD unNoticeOrder INT NOT NULL DEFAULT 0 COMMENT '待通知订单数' AFTER `totalOrder`;
ALTER TABLE `xhMerchantAsset` ADD sendingOrder INT NOT NULL DEFAULT 0 COMMENT '配送中订单数' AFTER `totalOrder`;
ALTER TABLE `xhMerchantAsset` ADD unSendOrder INT NOT NULL DEFAULT 0 COMMENT '待配送订单数' AFTER `totalOrder`;
ALTER TABLE `xhMerchantAsset` ADD unPayOrder INT NOT NULL DEFAULT 0 COMMENT '待付款订单数' AFTER `totalOrder`;
ALTER TABLE `xhMerchantAsset` ADD finishOrder INT NOT NULL DEFAULT 0 COMMENT '完成订单数' AFTER `totalOrder`;
ALTER TABLE `xhMerchantAsset` CHANGE `remainMsgNum` `remainSmsNum` INT NOT NULL DEFAULT 0 COMMENT '剩余短信数';
ALTER TABLE `xhMerchantAsset` MODIFY `totalUser` INT NOT NULL DEFAULT 0 COMMENT '总用户；总客户数';
ALTER TABLE `xhMerchantAsset` ADD unClassifyOrder INT NOT NULL DEFAULT 0 COMMENT '待归类订单' AFTER `totalOrder`;
ALTER TABLE `xhMerchantAsset` MODIFY `totalOrder` INT NOT NULL DEFAULT 0 COMMENT '订单总数，包括所有的';
ALTER TABLE `xhMerchantAsset` ADD `payOrder` INT NOT NULL DEFAULT 0 COMMENT '付款订单数' AFTER `totalOrder`;
ALTER TABLE `xhMerchant` ADD `addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间' AFTER `status`;
ALTER TABLE `xhAdmin` ADD `avatar` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '头像' AFTER `adminName`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhMerchant` MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '商家状态 0待审 1审核通过，取消冻结，正常 2审核未通过 3冻结 4过期';
ALTER TABLE `xhMerchantExtend` ADD miniVersion INT NOT NULL DEFAULT 0 COMMENT '小程序当前版本' AFTER `miniRefreshToken`;
DROP TABLE IF EXISTS `xhMiniUpgrade`;
CREATE TABLE `xhMiniUpgrade`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
`version` INT NOT NULL DEFAULT 0 COMMENT '提交版本号',
`submitTime` INT NOT NULL DEFAULT 0 COMMENT '提交时间',
`checkTime` INT NOT NULL DEFAULT 0 COMMENT '审核时间',
`checkReturn` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '审核结果返回的信息',
`remark` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待审核 1审核通过 2审核未通过',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='小程序升级记录';
DROP TABLE IF EXISTS `xhApplyOrder`;
RENAME TABLE `xhOpenRenewOrder` TO `xhRenewAccount`;
ALTER TABLE `xhApply` ADD remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '审核时添加的备注' AFTER `fullAddress`;
ALTER TABLE `xhApply` MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '0审核中 1审核通过、试用中 2审核未通过 3已付费 4试用到期失效';
ALTER TABLE `xhMiniUpgrade` MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待审核 1审核通过，待上线 2审核未通过 3已上线';
ALTER TABLE `xhMiniUpgrade` ADD `onTime` INT NOT NULL DEFAULT 0 COMMENT '上线时间' AFTER `version`;
ALTER TABLE `xhMiniUpgrade` MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待审核 1待上线 2已上线 3审核未通过';
RENAME TABLE `xhRenewAccount` TO `xhRenew`;
ALTER TABLE `xhRenew` DROP COLUMN `dealStatus`;
ALTER TABLE `xhRenew` MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '0 待付款 1已付款';
ALTER TABLE `xhRenew` DROP COLUMN `orderStyle`;
ALTER TABLE `xhRenew` ADD setId SMALLINT NOT NULL DEFAULT 0 COMMENT '套餐id' AFTER `remark`;
ALTER TABLE xhMerchantAsset DROP `totalWeixinFans`;
ALTER TABLE xhMerchantAsset CHANGE `unClassifyOrder` `unClassOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '待归类订单';
ALTER TABLE xhMerchantAsset CHANGE `totalUseRecharge` `usedRecharge` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '余额消费数；充值金额消耗数';
DROP TABLE IF EXISTS `xhRetailAsset`;
CREATE TABLE IF NOT EXISTS `xhRetailAsset` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `totalVisit` INT(11) NOT NULL DEFAULT '0' COMMENT '总访问量',
  `totalUser` INT(11) NOT NULL DEFAULT '0' COMMENT '总用户；总客户数',
  `totalMember` INT(11) NOT NULL DEFAULT '0' COMMENT '总会员',
  `totalFans` INT(11) NOT NULL DEFAULT '0' COMMENT '总粉丝',
  `totalIntegral` INT(11) NOT NULL DEFAULT '0' COMMENT '总积分',
  `totalGoods` INT(11) NOT NULL DEFAULT '0' COMMENT '总计商品数量',
  `totalRecharge` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计给用户充值金额',
  `usedRecharge` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '余额消费数；充值金额消耗数',
  `totalOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '订单总数，包括所有的',
  `payOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '付款订单数',
  `unClassOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '待归类订单',
  `finishOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '完成订单数',
  `unPayOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '待付款订单数',
  `unSendOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '待配送订单数',
  `sendingOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '配送中订单数',
  `unNoticeOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '待通知订单数',
  `cancelOrder` INT(11) NOT NULL DEFAULT '0' COMMENT '已取消订单数',
  `totalIncome` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计收入',
  `totalExpend` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计消费',
  `addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
) COMMENT='平台零售业资产';";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhRetailAsset` ADD onGoods INT NOT NULL DEFAULT 0 COMMENT '上架商品数' AFTER `totalGoods`;
ALTER TABLE `xhRetailAsset` ADD offGoods INT NOT NULL DEFAULT 0 COMMENT '下架商品数' AFTER `totalGoods`;
ALTER TABLE `xhMerchantAsset` ADD onGoods INT NOT NULL DEFAULT 0 COMMENT '上架商品数' AFTER `totalGoods`;
ALTER TABLE `xhMerchantAsset` ADD offGoods INT NOT NULL DEFAULT 0 COMMENT '下架商品数' AFTER `totalGoods`;
drop table if exists `xhFest`;
CREATE TABLE if not exists `xhFest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '节日名称',
  `month` tinyint(4) NOT NULL DEFAULT '1' COMMENT '月份',
  `day` tinyint(4) NOT NULL DEFAULT '1' COMMENT '日期',
  `inTurn` int(11) NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`id`)
) COMMENT='节日';
drop table if exists `xhFestRise`;
CREATE TABLE `xhFestRise` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchantId` int(11) NOT NULL DEFAULT '0' COMMENT '商家id',
  `festId` int(11) NOT NULL DEFAULT '0' COMMENT '节日',
  PRIMARY KEY (`id`)
) COMMENT='涨价节日';
ALTER TABLE `xhSetMeal` ADD num INT NOT NULL DEFAULT 0 COMMENT '花店数量' AFTER `price`;
ALTER TABLE `xhSetMeal` DROP COLUMN `manageStyle`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhApply` ADD UNIQUE INDEX `mobile`(`mobile`);
ALTER TABLE `xhRenew` ADD `orderSn` CHAR(30) NOT NULL DEFAULT '' COMMENT '订单编号' AFTER `id`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `roleId`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `province`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `city`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `dist`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `address`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `latitude`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `longitude`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `precision`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `openId`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `merchantId`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `userId`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `wxOpenId`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `unionId`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `subscribe`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `createTime`;
ALTER TABLE `xhWxOpenAdmin` DROP COLUMN `updateTime`;
ALTER TABLE `xhWxOpenAdmin` ADD `addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间' AFTER `status`;
ALTER TABLE `xhWxOpenAdmin` MODIFY `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态 0冻结 1正常';
ALTER TABLE `xhRenew` DROP COLUMN `payStyle`;
ALTER TABLE `xhRenew` MODIFY `merchantId` INT NOT NULL DEFAULT 0 COMMENT '续费的商家';
ALTER TABLE xhRenew MODIFY `payWay` TINYINT NOT NULL DEFAULT 0 COMMENT '支付方式 0微信 1支付宝 2余额';
DROP TABLE IF EXISTS `xhRenew`;
CREATE TABLE IF NOT EXISTS `xhRenew` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自增id',
  `orderSn` CHAR(30) NOT NULL DEFAULT '' COMMENT '订单编号',
  `payWay` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '支付方式 0微信 1支付宝 2余额',
  `payCode` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '支付返回号',
  `prePrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '应付款，原价，未修改价格，打折前',
  `actPrice` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '实际付款，实价，修改后价格，打折后',
  `userId` INT(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `alipayId` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '支付宝用户id',
  `merchantId` INT(11) NOT NULL DEFAULT '0' COMMENT '续费的商家',
  `remark` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '备注',
  `setId` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '套餐id',
  `beforeDeadline` INT(11) NOT NULL DEFAULT '0' COMMENT '之前到期时间',
  `deadline` INT(11) NOT NULL DEFAULT '0' COMMENT '续费后到期时间',
  `status` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0 待付款 1已付款',
  `addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间，时间戳格式'
) COMMENT='开通续费订单';
ALTER TABLE `xhCouponAsset` ADD `totalNum` INT NOT NULL DEFAULT 0 COMMENT '全部优惠券数' AFTER `duration`;
ALTER TABLE `xhCouponAsset` ADD `unUseNum` INT NOT NULL DEFAULT 0 COMMENT '待使用数' AFTER `totalNum`;
ALTER TABLE `xhCouponAsset` ADD `useNum` INT NOT NULL DEFAULT 0 COMMENT '已使用数' AFTER `unUseNum`;
ALTER TABLE `xhCouponAsset` ADD `invalidNum` INT NOT NULL DEFAULT 0 COMMENT '已失效数' AFTER `useNum`;
ALTER TABLE `xhOrder` MODIFY `sourceType` TINYINT NOT NULL DEFAULT 0 COMMENT '订单来源，0商城 1门店 2微信朋友圈 3美团';";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhAdmin` ADD platUserId INT NOT NULL DEFAULT 0 COMMENT '管理员对应的花卉宝id' AFTER `userId`;
ALTER TABLE `xhAdmin` ADD openId VARCHAR(100) NOT NULL DEFAULT '' COMMENT '管理员对应商家公众号openId' AFTER `userId`;
ALTER TABLE `xhAdmin` DROP COLUMN `openId`;
ALTER TABLE `xhAdmin` ADD `delStatus` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识 0未删除 1已删除' AFTER `super`;
ALTER TABLE `xhAdmin` CHANGE `incomeNotice` `remind` TINYINT NOT NULL DEFAULT 0 COMMENT '动态提醒 0不需要 1需要';
ALTER TABLE `xhAdmin` ADD remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '员工备注' AFTER `remind`;
ALTER TABLE `xhApply` MODIFY `userId` INT NOT NULL DEFAULT 0 COMMENT '邀请花店的用户id，没有邀请花店，刚是平台花卉宝的用户';
DROP TABLE IF EXISTS `xhAgentAsset`;
CREATE TABLE IF NOT EXISTS `xhAgentAsset`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
inviteNum INT NOT NULL DEFAULT 0 COMMENT '邀请花店数',
payNum INT NOT NULL DEFAULT 0 COMMENT '付费花店数',
income DECIMAL(9,2) NOT NULL DEFAULT 0.00 COMMENT '累计收益',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='我的代理资产';
ALTER TABLE `xhRecharge` DROP PRIMARY KEY;
ALTER TABLE `xhRecharge` CHANGE `id` `sn` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '编号';
ALTER TABLE `xhRecharge` ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE `xhRecharge` AUTO_INCREMENT=135736;
UPDATE `xhRecharge` SET id=id+135736;
ALTER TABLE `xhRecharge` DROP COLUMN `createTime`;
ALTER TABLE `xhRecharge` DROP COLUMN `updateTime`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhUserAsset` MODIFY `memberLevel` SMALLINT NOT NULL DEFAULT -1 COMMENT '会员等级 -1普通用户 0普通会员，登记了手机号 1 一级会员';
ALTER TABLE `xhUserAsset` CHANGE `memberLevel` `member` SMALLINT NOT NULL DEFAULT -1 COMMENT '会员等级 -1普通用户 0普通会员，登记了手机号 1 一级会员';
ALTER TABLE `xhUserAsset` DROP COLUMN `isMember`;
ALTER TABLE `xhUser` DROP COLUMN `isMember`;
ALTER TABLE `xhUser` ADD `member` SMALLINT NOT NULL DEFAULT -1 COMMENT '会员等级 -1普通用户 0普通会员，登记了手机号 1 一级会员' AFTER `source`;
ALTER TABLE `xhGoods` ADD delStatus TINYINT NOT NULL DEFAULT 0 COMMENT '删除标识 0未删除 1删除' AFTER `riseAmount`;
ALTER TABLE `xhGoodsCategory` ADD delStatus TINYINT NOT NULL DEFAULT 0 COMMENT '删除标识 0未删除 1已删除' AFTER `merchantId`;
DROP TABLE IF EXISTS `xhStatIn`;
DROP TABLE IF EXISTS `xhStatInMonth`;
ALTER TABLE `xhStatIncomeCategoryMonth` ADD `time` INT NOT NULL DEFAULT 0 COMMENT '月份' AFTER `id`;
ALTER TABLE `xhStatIncomeMonth` ADD `time` INT NOT NULL DEFAULT 0 COMMENT '月份' AFTER `id`;
ALTER TABLE `xhStatIncomeSourceMonth` ADD `time` INT NOT NULL DEFAULT 0 COMMENT '月份' AFTER `id`;
ALTER TABLE `xhStatIncomeUsageMonth` ADD `time` INT NOT NULL DEFAULT 0 COMMENT '月份' AFTER `id`;
ALTER TABLE `xhStatOrderMonth` ADD `time` INT NOT NULL DEFAULT 0 COMMENT '月份' AFTER `id`;
ALTER TABLE `xhStatOutMonth` ADD `time` INT NOT NULL DEFAULT 0 COMMENT '月份' AFTER `id`;
ALTER TABLE `xhStatVisitMonth` ADD `time` INT NOT NULL DEFAULT 0 COMMENT '月份' AFTER `id`;
DROP TABLE IF EXISTS `xhStatOrderSource`;
CREATE TABLE `xhStatUserMonth` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `time` INT(11) NOT NULL DEFAULT '0' COMMENT '月份',
  `riseFansNum` INT(11) NOT NULL DEFAULT '0' COMMENT '增减粉丝数，负数表示减',
  `totalFansNum` INT(11) NOT NULL DEFAULT '0' COMMENT '当前粉丝总数',
  `riseMemberNum` INT(11) NOT NULL DEFAULT '0' COMMENT '增加会员数',
  `totalMemberNum` INT(11) NOT NULL DEFAULT '0' COMMENT '当前会员总数',
  `riseUserNum` INT(11) NOT NULL DEFAULT '0' COMMENT '访客数',
  `totalUserNum` INT(11) NOT NULL DEFAULT '0' COMMENT '当前访客总数',
  `merchantId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
  `addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间',
  PRIMARY KEY (`id`)
) COMMENT='客户月度统计';
RENAME TABLE `xhWxOpenAdmin` TO `xhStaff`;
ALTER TABLE `xhMerchantCapital` MODIFY `capitalType` TINYINT NOT NULL DEFAULT 0 COMMENT '流水类型';
ALTER TABLE `xhMerchantCapital` MODIFY `payWay` TINYINT NOT NULL DEFAULT 0 COMMENT '付款方式；支付方式';
ALTER TABLE `xhMerchantCapital` MODIFY `sourceType` TINYINT NOT NULL DEFAULT 0 COMMENT '来源';
ALTER TABLE `xhMerchantCapital` MODIFY `io` TINYINT NOT NULL DEFAULT 0 COMMENT '收支类型 0支出 1收入';";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhMerchant` ADD `deadline` INT NOT NULL DEFAULT 0 COMMENT '帐号过期时间' AFTER `status`;UPDATE `xhMerchant` SET `deadline`=1607589778;";
		Yii::$app->db->createCommand($sql)->execute();
		
		
		//导入省市区
		$root = dirUtil::getRootDir();
		$sql = file_get_contents($root . '/region.sql');
		Yii::$app->db->createCommand($sql)->execute();
		
		
		//将订单商品表的订单id换成自增id
		$orderGoods = OrderGoodsService::getAllByCondition([], null, '*');
		if (!empty($orderGoods)) {
			$ids = array_column($orderGoods, 'orderId');
			$orderList = OrderService::getAllByCondition(['orderSn' => ['in', $ids]], null, '*', 'orderSn');
			foreach ($orderList as $orderSn => $val) {
				$id = $val['id'];
				OrderGoodsService::updateByCondition(['orderId' => $orderSn], ['orderId' => $id]);
			}
		}
		$sql = "alter table `xhOrderGoods` modify orderId int not null default 0 comment '订单id';";
		Yii::$app->db->createCommand($sql)->execute();
		
		//商品多款式同步
		$goodsList = GoodsService::getAllByCondition([], null, '*');
		if (!empty($goodsList)) {
			foreach ($goodsList as $goods) {
				$multiPrice = $goods['multiPrice'];
				if (!empty($multiPrice)) {
					$currentIds = explode(',', trim($multiPrice));
					if (!empty($currentIds)) {
						$currentIds = array_filter(array_unique($currentIds));
						$goodsId = $goods['id'];
						foreach ($currentIds as $currentId) {
							GoodsStyleService::updateByCondition(['id' => $currentId], ['goodsId' => $goodsId]);
						}
					}
				}
			}
		}
		$sql = "ALTER TABLE xhGoods DROP COLUMN `multiPrice`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		//商家流水
		$mCapital = MerchantCapitalService::getAllByCondition([], null, '*');
		if (!empty($mCapital)) {
			foreach ($mCapital as $capital) {
				$relateId = $capital['relateId'];
				$id = $capital['id'];
				if (!empty($relateId)) {
					$order = OrderService::getByCondition(['orderSn' => $relateId]);
					if (!empty($order)) {
						$orderId = $order['id'];
						MerchantCapitalService::updateById($id, ['relateId' => $orderId]);
					} else {
						MerchantCapitalService::updateById($id, ['relateId' => 0]);
					}
				} else {
					MerchantCapitalService::updateById($id, ['relateId' => 0]);
				}
			}
		}
		$sql = "ALTER TABLE `xhMerchantCapital` MODIFY `relateId` INT NOT NULL DEFAULT 0 COMMENT '关联id；订单id';";
		Yii::$app->db->createCommand($sql)->execute();
		
		//客户流水
		$uCapital = UserCapitalService::getAllByCondition([], null, '*');
		if (!empty($uCapital)) {
			foreach ($uCapital as $capital) {
				$relateId = $capital['relateId'];
				$id = $capital['id'];
				if (!empty($relateId)) {
					$order = OrderService::getByCondition(['orderSn' => $relateId]);
					if (!empty($order)) {
						$orderId = $order['id'];
						UserCapitalService::updateById($id, ['relateId' => $orderId]);
					} else {
						UserCapitalService::updateById($id, ['relateId' => 0]);
					}
				} else {
					UserCapitalService::updateById($id, ['relateId' => 0]);
				}
			}
		}
		$sql = "ALTER TABLE `xhUserCapital` MODIFY `relateId` INT NOT NULL DEFAULT 0 COMMENT '关联id；订单id';";
		Yii::$app->db->createCommand($sql)->execute();
		
		//客户成长值
		$userGrowth = UserGrowthService::getAllByCondition([], null, '*');
		if (!empty($userGrowth)) {
			foreach ($userGrowth as $relateId => $growth) {
				$relateId = $growth['relateId'];
				$id = $growth['id'];
				if (!empty($relateId)) {
					$order = OrderService::getByCondition(['orderSn' => $relateId]);
					if (!empty($order)) {
						$orderId = $order['id'];
						UserGrowthService::updateById($id, ['relateId' => $orderId]);
					} else {
						UserGrowthService::updateById($id, ['relateId' => 0]);
					}
				} else {
					UserGrowthService::updateById($id, ['relateId' => 0]);
				}
			}
		}
		$sql = "ALTER TABLE `xhUserGrowth` MODIFY `relateId` INT NOT NULL DEFAULT 0 COMMENT '关联id';ALTER TABLE `xhAdminRoleAuth` ADD merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id' AFTER `endId`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "DROP TABLE IF EXISTS `xhInformAdmin`;
CREATE TABLE `xhInformAdmin` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` INT(11) NOT NULL DEFAULT '0' COMMENT '管理员userId',
  `adminId` INT(11) NOT NULL DEFAULT '0' COMMENT '管理员Id',
  `merchantId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
  `noticeType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '类型',
  `content` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '内容',
  `noticeWay` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '通知方式 0微信1短信',
  `status` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '状态 0没有成功 1成功',
  `sourceType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '来源类型',
  `sourceId` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '来源id',
  `addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间',
  PRIMARY KEY (`id`)
) COMMENT='店员消息通知记录';
DROP TABLE IF EXISTS `xhInformUser`;
CREATE TABLE `xhInformUser` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` INT(11) NOT NULL DEFAULT '0' COMMENT '客户id',
  `merchantId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
  `noticeType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '类型',
  `content` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '内容',
  `noticeWay` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '通知方式 0微信1短信',
  `status` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '状态 0没有成功 1成功',
  `sourceType` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '来源类型',
  `sourceId` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '来源id',
  `addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间',
  PRIMARY KEY (`id`)
) COMMENT='客户消息通知记录';";
		Yii::$app->db->createCommand($sql)->execute();
		
		//省市区表字段变更
		$sql = "ALTER TABLE `xhDistrict` CHANGE `shortName` `name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '简称';";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "DROP TABLE IF EXISTS `xhStatView`;
CREATE TABLE `xhStatView` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `time` INT(11) NOT NULL DEFAULT '0' COMMENT '时间戳',
  `riseNum` INT(11) NOT NULL DEFAULT '0' COMMENT '增加数量',
  `totalNum` INT(11) NOT NULL DEFAULT '0' COMMENT '总数量',
  `merchantId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
  `addTime` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '添加时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `num` (`merchantId`,`time`)
) COMMENT='每天增加访问次数，非访问ip数';
DROP TABLE IF EXISTS `xhStatViewMonth`;
CREATE TABLE `xhStatViewMonth` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `time` INT(11) NOT NULL DEFAULT '0' COMMENT '月份',
  `riseNum` INT(11) NOT NULL DEFAULT '0' COMMENT '增加数量',
  `totalNum` INT(11) NOT NULL DEFAULT '0' COMMENT '总数量',
  `merchantId` INT(11) NOT NULL DEFAULT '0' COMMENT '商家id',
`addTime` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '添加时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `num` (`merchantId`,`time`)
) COMMENT='每月增加访问次数，非访问ip数';";
		Yii::$app->db->createCommand($sql)->execute();
		
		//统计相关
		//xhStatIncomeMonth
		$list = StatIncomeMonthService::getAllByCondition([], null, '*');
		if (!empty($list)) {
			foreach ($list as $val) {
				$year = $val['year'];
				$month = $val['month'];
				$date = date("Ym", strtotime($year . '-' . $month . '-' . '1'));
				$id = $val['id'];
				StatIncomeMonthService::updateById($id, ['time' => $date]);
			}
		}
		//xhStatIncomeSourceMonth
		$list = StatIncomeSourceMonthService::getAllByCondition([], null, '*');
		if (!empty($list)) {
			foreach ($list as $val) {
				$year = $val['year'];
				$month = $val['month'];
				$date = date("Ym", strtotime($year . '-' . $month . '-' . '1'));
				$id = $val['id'];
				StatIncomeSourceMonthService::updateById($id, ['time' => $date]);
			}
		}
		//xhStatOrder
		$list = StatOrderService::getAllByCondition([], null, '*');
		if (!empty($list)) {
			foreach ($list as $val) {
				$id = $val['id'];
				$date = date("Ymd", $val['time']);
				StatOrderService::updateById($id, ['time' => $date]);
			}
		}
		//xhStatOrderMonth
		$list = StatOrderMonthService::getAllByCondition([], null, '*');
		if (!empty($list)) {
			foreach ($list as $val) {
				$year = $val['year'];
				$month = $val['month'];
				$date = date("Ym", strtotime($year . '-' . $month . '-' . '1'));
				$id = $val['id'];
				StatOrderMonthService::updateById($id, ['time' => $date]);
			}
		}
		
		$sql = "ALTER TABLE `xhMerchantAsset` ADD totalView INT NOT NULL DEFAULT 0 COMMENT '累计访问次数' AFTER `totalVisit`;ALTER TABLE `xhMerchantAsset` MODIFY `totalVisit` INT NOT NULL DEFAULT 0 COMMENT '累计访客数';";
		Yii::$app->db->createCommand($sql)->execute();
		
		//删除收入来源数据统计的键名
		$sql = "ALTER TABLE `xhStatIncomeSourceMonth` DROP INDEX `income`;";
		Yii::$app->db->createCommand($sql)->execute();

		$sql = "ALTER TABLE xhGoods ADD `briefContent` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '简单内容介绍，移动端添加商品' AFTER `content`;";
		Yii::$app->db->createCommand($sql)->execute();


		//各个环境开放平台的更新
		$env = getenv("YII_ENV");

		$sql = "ALTER TABLE `xhAdmin` AUTO_INCREMENT=18532;
UPDATE `xhAdmin` SET id=18531 WHERE id=5;
UPDATE `xhAdmin` SET id=18530 WHERE id=4;
UPDATE `xhAdmin` SET id=18529 WHERE id=3;
UPDATE `xhAdmin` SET id=18528 WHERE id=1;";
		Yii::$app->db->createCommand($sql)->execute();

		$password = password_hash('glhs610000', PASSWORD_BCRYPT);
		$confirmPassword = password_hash('454588', PASSWORD_BCRYPT);
		$sql = "update `xhAdmin` set password='{$password}',confirmPassword='{$confirmPassword}' where id=18528";
		Yii::$app->db->createCommand($sql)->execute();
		$password = password_hash('123456', PASSWORD_BCRYPT);
		$confirmPassword = password_hash('123456', PASSWORD_BCRYPT);
		$sql = "update `xhAdmin` set password='{$password}',confirmPassword='{$confirmPassword}' where id=18529";
		Yii::$app->db->createCommand($sql)->execute();

		//花美灵
		if ($env == 'dev') {
			$sql = "update `xhWxOpen` set appId='wxe4428cfe08fc74d6',openName='花美灵',appSecret='cdbcb1ea6bf2249b269c542c870bd96d',merchantId=12359,adminId=18529,token='XH11464773308',verifyTicket='ticket@@@mUTfkCa6p-W5fIB_YvWDiSzUvsCVJNN6fELjsHnZ07Nvz5hfRMyfHzmjn3ZPK9Gi5aqY2_H7ruxnX2cdYdjRIg',verifyTicketTime='2020-01-09 21:04:50',componentAccessToken='29_RQZoasQKtSPVa754Inhu3aKiAfou3KYGJT5SGtqWy74cHoL3aEfPt0xTR6JMuULyDN4-25fBDTE-VS_jtalbq6WTbb5d7B-K4dUSS3zDbKykNtctzFnBBnDzkbGRXcw2b8OS8JY_HHblIlFZTVLcAGAGIY',componentAccessTokenTime='2020-01-09 21:36:26',aesKey='Gm2BIikJaExAbiyafGoJGFbEsECb4mDEnyEYgl9WXW5',preAuthCode='preauthcode@@@IJXez4FxmgNRLow1rmxhAxM2eaKQYY6g1ocsK8Koe4v9mx7mr07arTNZTLxe2Sf2',preAuthCodeTime='2019-12-19 16:13:58',createTime='2019-04-17 14:49:19',updateTime='2020-03-05 15:00:00' where id=1;";
			Yii::$app->db->createCommand($sql)->execute();
		}
		//花掌柜助手
		if ($env == 'test') {
			$sql = "update `xhWxOpen` set appId='wx364800c320a0cf53',openName='花掌柜助手',appSecret='6a83bfa98f3ff5e66b1a85a19c54ebdb',merchantId=12359,adminId=18529,token='wt11464773309',verifyTicket='ticket@@@mUTfkCa6p-W5fIB_YvWDiSzUvsCVJNN6fELjsHnZ07Nvz5hfRMyfHzmjn3ZPK9Gi5aqY2_H7ruxnX2cdYdjRIg',verifyTicketTime='2020-01-09 21:04:50',componentAccessToken='29_RQZoasQKtSPVa754Inhu3aKiAfou3KYGJT5SGtqWy74cHoL3aEfPt0xTR6JMuULyDN4-25fBDTE-VS_jtalbq6WTbb5d7B-K4dUSS3zDbKykNtctzFnBBnDzkbGRXcw2b8OS8JY_HHblIlFZTVLcAGAGIY',componentAccessTokenTime='2020-01-09 21:36:26',aesKey='Gm2BIikJaExAbiyafGoJGFbEsECb4mDEnyEYgl9WXW5',preAuthCode='preauthcode@@@IJXez4FxmgNRLow1rmxhAxM2eaKQYY6g1ocsK8Koe4v9mx7mr07arTNZTLxe2Sf2',preAuthCodeTime='2019-12-19 16:13:58',createTime='2019-04-17 14:49:19',updateTime='2020-03-05 15:00:00' where id=1;";
			Yii::$app->db->createCommand($sql)->execute();
		}

		//客户的省市区延长字段，有外国英文名称的比较长
		$sql = "ALTER TABLE `xhUser` MODIFY `province` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '省';
ALTER TABLE `xhUser` MODIFY `city` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '市';
ALTER TABLE `xhUser` MODIFY `dist` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '区';
ALTER TABLE `xhUser` MODIFY `address` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '详细地址';
ALTER TABLE `xhTMessage` MODIFY `createTime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间';
ALTER TABLE `xhTMessage` ADD `addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间' AFTER `templateId`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		//申请信息修改
		$sql = "ALTER TABLE `xhApply` MODIFY `userId` INT NOT NULL DEFAULT 0 COMMENT '平台userId';
ALTER TABLE `xhApply` CHANGE `oldId` introMerchantId INT NOT NULL DEFAULT 0 COMMENT '花店介绍人';
ALTER TABLE `xhApply` CHANGE `newId` merchantId INT NOT NULL DEFAULT 0 COMMENT '新花店id；0表示还没审核通过';";
		Yii::$app->db->createCommand($sql)->execute();
		
		//门店省市区地址字段变更
		$sql = "ALTER TABLE `xhShop` CHANGE `shopLat` `lat` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '纬度';
ALTER TABLE `xhShop` CHANGE `shopLong` `long` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '经度';
ALTER TABLE `xhShop` CHANGE `shopProvince` `province` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '省';
ALTER TABLE `xhShop` CHANGE `shopCity` `city` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '市';
ALTER TABLE `xhShop` CHANGE `shopDist` `dist` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '区县';
ALTER TABLE `xhShop` CHANGE `shopAddress` `address` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '街道地址';
ALTER TABLE `xhShop` ADD `floor` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '楼号门牌号' AFTER `address`;
ALTER TABLE `xhShop` CHANGE `shopFullAddress` `fullAddress` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '完整地址';
ALTER TABLE `xhApply` CHANGE `longitude` `long` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '经度';
ALTER TABLE `xhApply` CHANGE `latitude` `lat` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '纬度';";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhMerchant` DROP INDEX `merchantName`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhAd` MODIFY `endTime` BIGINT NOT NULL DEFAULT 0 COMMENT '结束时间；值4102416000(2100-01-01 00:00:00)表示永不过期情况';
ALTER TABLE `xhAd` MODIFY `startTime` BIGINT NOT NULL DEFAULT 0 COMMENT '开始时间';";
		Yii::$app->db->createCommand($sql)->execute();
		
		//支付相关配置修改
		$sql = "ALTER TABLE `xhMerchantExtend` ADD wxPayInit TINYINT NOT NULL DEFAULT 0 COMMENT '0微信支付未配置 1配置' AFTER `miniVersion`;
ALTER TABLE `xhMerchantExtend` ADD alipayInit TINYINT NOT NULL DEFAULT 0 COMMENT '0支付宝支付未配置 1配置' AFTER `wxPayKey`;
ALTER TABLE `xhMerchantExtend` MODIFY `alipayKey` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '商户密钥';
ALTER TABLE `xhMerchantExtend` ADD alipayPublicKey VARCHAR(500) NOT NULL DEFAULT '' COMMENT '支付宝公钥' AFTER `alipayKey`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		//收款二维码和申请会员小程序码
		$sql = "ALTER TABLE `xhMerchantExtend` ADD `applyMemberCode` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '申请会员小程序码' AFTER `alipayAccountName`;
ALTER TABLE `xhShop` ADD `gatheringCode` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '收款的二维码' AFTER `introduction`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		//门店与来源
		$sql = "ALTER TABLE `xhOrder` MODIFY `sourceType` TINYINT NOT NULL DEFAULT 0 COMMENT '来源 0微信 1支付宝 2小程序 3朋友圈 4美团';
ALTER TABLE `xhOrder` ADD `store` TINYINT NOT NULL DEFAULT 0 COMMENT '门店订单 0不是 1是' AFTER `sourceType`;
ALTER TABLE `xhUser` CHANGE `source` `sourceType` TINYINT NOT NULL DEFAULT 0 COMMENT '来源 0微信 1支付宝 2小程序 3朋友圈 4美团';
ALTER TABLE `xhUser` ADD `store` TINYINT NOT NULL DEFAULT 0 COMMENT '门店订单 0不是 1是' AFTER `sourceType`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		//商品图片变长
		$sql = "ALTER TABLE `xhGoodsStyle` MODIFY `picture` VARCHAR(800) NOT NULL DEFAULT '' COMMENT '图片相对路径';";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhWxOpen` ADD smsNum INT NOT NULL DEFAULT 0 COMMENT '短信剩余总条数' AFTER `preAuthCodeTime`;UPDATE `xhWxOpen` SET smsNum=18500;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhShop` ADD `applyMemberCode` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '注册会员码' AFTER `gatheringCode`;
ALTER TABLE `xhShop` ADD `totalIncome` DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '累计收入' AFTER `applyMemberCode`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		//门店相关修改
		$sql = "ALTER TABLE `xhShop` ADD `avatar` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '门店照片；门店头像' AFTER `id`;
ALTER TABLE `xhShop` ADD `status` TINYINT NOT NULL DEFAULT 1 COMMENT '门店状态 0停用 1正常' AFTER `totalIncome`;
ALTER TABLE `xhShop` ADD `delStatus` TINYINT NOT NULL DEFAULT 0 COMMENT '删除状态 0没有删除 1已删除' AFTER `totalIncome`;
ALTER TABLE `xhShop` ADD `default` TINYINT NOT NULL DEFAULT 0 COMMENT '默认门店 0不是 1是，默认门店不能删除' AFTER `totalIncome`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhWxOpen` ADD smsRemindTime INT NOT NULL DEFAULT 0 COMMENT '平台短信不足提醒时间' AFTER `smsNum`;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "UPDATE `xhUser` SET mobile='' WHERE mobile=0;";
		Yii::$app->db->createCommand($sql)->execute();
		
		$sql = "ALTER TABLE `xhCategory` ADD `delStatus` TINYINT NOT NULL DEFAULT 0 COMMENT '删除状态 0未删除 1已删除' AFTER `inTurn`;
ALTER TABLE `xhCategory` DROP INDEX `merchantId`;
ALTER TABLE `xhCategory` ADD INDEX `merchant`(`merchantId`,`delStatus`);";
		Yii::$app->db->createCommand($sql)->execute();
		
	}

	//数据迁移 shish 2020.1.12
	public function actionSync()
	{
		$password = password_hash('glhs610000', PASSWORD_BCRYPT);
		$confirmPassword = password_hash('454588', PASSWORD_BCRYPT);
		$sql = "update `xhAdmin` set password='{$password}',confirmPassowrd='{$confirmPassword}' where id=1";
		Yii::$app->db->createCommand($sql)->execute();

		//国兰老客户导入
		$oldUserList = Yii::$app->db2->createCommand('select * from xhUserOldData where `status`=0')->queryAll();
		if (!empty($oldUserList)) {
			foreach ($oldUserList as $user) {
				$mobile = $user['mobile'];
				$exists = UserService::getByCondition(['mobile' => $mobile, 'merchantId' => 12358]);
				if (!empty($exists)) {
					echo "老客户：'.$mobile.'已经导入过了。\n";
					continue;
				}
				$user['realName'] = $user['userName'];
				$user['growth'] = $user['integral'];
				$insertUser = UserService::addUser($user);
				$id = $user['id'];
				Yii::$app->db2->createCommand("update xhUserOldData set `status`=1 where id={$id}")->execute();
				echo "老客户导入生成userId:" . $insertUser['id'] . "\n";
			}
		}
		$sql = "DROP TABLE IF EXISTS `xhUserOldData`;";
		Yii::$app->db2->createCommand($sql)->execute();

		//更新国兰的菜单分类
		$sql = "truncate `xhCategory`;insert  into `xhCategory`(`id`,`parentId`,`merchantId`,`categoryName`,`description`,`img`,`goodsNum`,`sold`,`inTurn`,`status`) values (1,0,12358,'花束','唯美花束','',0,0,15,1),(2,0,12358,'花盒','时尚花盒','',0,0,14,1),(3,0,12358,'开业花篮','开业花篮','',0,0,0,1),(4,0,12358,'婚礼用花','婚礼用花','',0,0,0,1),(5,0,12358,'会议用花','会议用花','',0,0,0,1),(9,0,12358,'蝴蝶兰专区','蝴蝶兰','',0,0,4,1),(87,0,12358,'花篮','时尚礼篮','',0,0,4,1),(88,0,12358,'植物-微景观','植物-微景观','',0,0,4,1);";
		Yii::$app->db->createCommand($sql)->execute();

		//更新国兰分类与商品对应关系
		Yii::$app->db->createCommand("truncate `xhGoodsCategory`;")->execute();
		$goodsCategory = Yii::$app->db2->createCommand('select * from `xhGoodsCategory` where `merchantId`=12358')->queryAll();
		if (!empty($goodsCategory)) {
			foreach ($goodsCategory as $val) {
				$cId = $val['cId'];
				$gId = $val['gId'];
				$inTurn = $val['inTurn'];
				$merchantId = $val['merchantId'];
				GoodsCategoryService::add(['gId' => $gId, 'cId' => $cId, 'inTurn' => $inTurn, 'status' => 1, 'merchantId' => $merchantId]);
				echo $cId . ' ' . $gId . "\n";
				
			}
		}
		$goodsUsage = Yii::$app->db2->createCommand('select * from `xhGoodsUsage` where `merchantId`=12358')->queryAll();
		if (!empty($goodsUsage)) {
			foreach ($goodsUsage as $val) {
				$usageId = $val['usageId'];
				$goodsId = $val['goodsId'];
				$inTurn = $val['inTurn'];
				$merchantId = $val['merchantId'];
				GoodsCategoryService::add(['gId' => $goodsId, 'cId' => $usageId, 'inTurn' => $inTurn, 'status' => 1, 'merchantId' => $merchantId]);
				echo $usageId . ' ' . $goodsId . "\n";
			}
		}
		
		//会员打折和会员等级要同步，要先导老客户 shish 2020.1.12
		$merchantList = MerchantExtendService::getAllByCondition([], null, '*', 'merchantId');
		$userAssetList = UserAssetService::getAllByCondition([], null, '*');
		if (!empty($userAssetList)) {
			foreach ($userAssetList as $asset) {
				$id = $asset['userId'];
				$merchantId = $asset['merchantId'];
				$growth = $asset['growth'];
				$currentMember = $asset['member'];
				UserService::updateById($id, ['member' => $currentMember]);
				if (!empty($merchantId) && isset($merchantList[$merchantId])) {
					$extend = $merchantList[$merchantId];
					if (!empty($extend)) {
						$gradeList = MerchantExtendService::getGradeList($extend);
						$member = null;
						foreach ($gradeList as $grade) {
							if ($grade['growth'] < $growth) {
								$member = $grade['level'];
								continue;
							}
						}
						if (!empty($member)) {
							UserAssetService::updateByUserId($id, ['member' => $member]);
							UserService::updateById($id, ['member' => $member]);
							echo '客户id:' . $id . '会员等级：' . $member . "已更新\n";
						}
					}
				}
			}
		}

		//更新头像
		UserService::syncAvatar(12358);

	}

	//微信菜单初始化或重置，可以重复执行 shish 2020.3.5
	// ./yii upgrade/menu-init 12358
	public function actionMenuInit($id)
	{
		$merchant = xhMerchantService::getById($id);
		xhWxMenuService::applyInit($merchant);
	}

}