示例：----------------------------------开始--------------------------------------------------------------------
ALTER TABLE b_user_customer ADD COLUMN customerContact VARCHAR(50) DEFAULT NULL COMMENT '联系人'; 
ALTER TABLE p_channel ADD COLUMN totalChannelProfit  DECIMAL(12,2) DEFAULT 0 COMMENT '渠道总利润';
ALTER TABLE h_page_statistics ADD COLUMN isRead int default 0 COMMENT '是否阅读,0未，1是'; 
ALTER TABLE b_customer_roll_pic ADD COLUMN picCategory ENUM('ip','ic','pa','icbc','common') DEFAULT 'ip';
ALTER TABLE p_customer_info DROP  COLUMN customerContact;
ALTER TABLE biz_receiptvoucher modify COLUMN receiptType ENUM('sk','tk','zz','csrz','ptdsk','ptzz') DEFAULT 'sk';
示例：----------------------------------结束--------------------------------------------------------------------

2019.10.21 ---- zhuangbing
ALTER TABLE biz_icpcustomerinfo ADD COLUMN valueAddedTelecomBusinessApplicationForm VARCHAR(200) DEFAULT NULL COMMENT '增值电信业务申请表'; 
ALTER TABLE biz_icpcustomerinfo ADD COLUMN naturalPersonShareholderIdCard VARCHAR(200) DEFAULT NULL COMMENT '自然人股东身份证'; 
ALTER TABLE biz_icpcustomerinfo ADD COLUMN trusteeshipAgreementAndIDC VARCHAR(200) DEFAULT NULL COMMENT '托管协议及IDC证'; 
ALTER TABLE biz_icpcustomerinfo ADD COLUMN domainNameCertificate VARCHAR(200) DEFAULT NULL COMMENT '域名证书'; 
ALTER TABLE biz_icpcustomerinfo ADD COLUMN paymentAgreementAndLicense VARCHAR(200) DEFAULT NULL COMMENT '支付协议及支付牌照'; 
ALTER TABLE biz_icpcustomerinfo ADD COLUMN capitalFlowDesAndMerchantPresenceTemp VARCHAR(200) DEFAULT NULL COMMENT '资金流向说明及商家入驻模板'; 

ALTER TABLE biz_icpcustomerinfo ADD COLUMN applyAddr VARCHAR(200) DEFAULT NULL COMMENT '申请地址'; 
ALTER TABLE biz_order ADD COLUMN icpcustomerinfo_id int DEFAULT NULL COMMENT 'icp申请人id'; 
CREATE TABLE `biz_icpcustomerinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (50) COMMENT '法人企业或机构名称',
	`code` VARCHAR (50) COMMENT '统一社会信用代码或机构代码',
  `customerType` enum('enterprise','socialorganization','institutional') DEFAULT NULL COMMENT '法人类型',
  `customerTypeName` varchar(255) DEFAULT NULL,
  `customerName` VARCHAR (50) COMMENT '企业法定代表人姓名',
  `credentialsType` enum('idcard','passport','gaidcard','tidcard','officerscard') DEFAULT NULL COMMENT '证件类型',
  `credentialsTypeName` varchar(255) DEFAULT NULL,
  `legalRepresentativeCertificateNo` varchar(255) DEFAULT NULL COMMENT '法定代表人证件号',
  `legalRepresentativeCertificatePic` varchar(255) DEFAULT NULL COMMENT '证件扫描件',
  `businessLicensePic` varchar(255) DEFAULT NULL COMMENT '营业执照图片',
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `company_id` int(11) DEFAULT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `isLastOp` TINYINT(1) DEFAULT '1' COMMENT '是否最后的操作，0否，1是，默认为1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2019.09.03 --zhuangbing
ALTER TABLE py_tmreport ADD COLUMN channel_company_id int DEFAULT NULL COMMENT '渠道公司id'; 
ALTER TABLE py_tmreport ADD COLUMN wxuser_id int DEFAULT NULL COMMENT '微信用户id'; 
ALTER TABLE s_need ADD COLUMN publisherEnt VARCHAR(200) DEFAULT NULL COMMENT '发布者公司'; 
2019.08.28 --- zhuangbing
CREATE TABLE
IF NOT EXISTS `p_weblink` (
	`id` INTEGER NOT NULL auto_increment,
	`name` VARCHAR (50),
	`code` VARCHAR (50),
	`url` VARCHAR (200),
	`description` VARCHAR (255),
	`created_at` DATETIME NOT NULL,
	`updated_at` DATETIME NOT NULL,
	`deleted_at` DATETIME,
	`version` INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (`id`)
) ENGINE = INNODB;
DROP TABLE IF EXISTS `p_companyweblink`;
CREATE TABLE `p_companyweblink` (
  `company_id` int(11) NOT NULL,
  `weblink_id` int(11) NOT NULL,
  `version` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`company_id`,`weblink_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2019.08.20 -- sy
ALTER TABLE biz_order ADD COLUMN accountType ENUM('cash','bank','wx','alipay','other') DEFAULT NULL;
ALTER TABLE biz_order ADD COLUMN accountTypeName VARCHAR(255) DEFAULT NULL COMMENT '帐户类型名称';
ALTER TABLE biz_orderflow ADD COLUMN flowStatus int DEFAULT 0 COMMENT '流程状态值'; 


2019.08.16 -- sy
ALTER TABLE biz_order ADD COLUMN payTime datetime DEFAULT NULL COMMENT '支付时间'; 


2019.08.13 -- sy
ALTER TABLE biz_invoiceapply ADD COLUMN order_id int DEFAULT NULL COMMENT '发票Id'; 
ALTER TABLE cms_loopplay ADD COLUMN loopplayType ENUM('PC','YD') DEFAULT 'PC';
ALTER TABLE cms_loopplay ADD COLUMN loopplayTypeName VARCHAR(50) DEFAULT 'PC' COMMENT '轮波图类型：PC: PC, YD:移动端';
ALTER TABLE cms_loopplay ADD COLUMN skipUrl VARCHAR(500) COMMENT '跳转路径';

2019.08.12 -- zhuangbing
ALTER TABLE biz_order modify COLUMN invoiceApplyStatus ENUM('1','2','3') DEFAULT '1';
2019.08.09 --- sy
ALTER TABLE biz_invoiceapply DROP  COLUMN order_id;
ALTER TABLE biz_order ADD COLUMN invoiceapply_id int DEFAULT NULL COMMENT '发票Id'; 
ALTER TABLE biz_order ADD COLUMN productonetype_id int DEFAULT NULL COMMENT '产品大类Id'; 
ALTER TABLE biz_order DROP  COLUMN productTypeName;
UPDATE biz_order t1,s_serviceitem t2 SET t1.productonetype_id = t2.producttype_pid_id WHERE t1.sveItemCode = t2.code AND t1.`company_id`=t2.`company_id`;


2019.08.05 --- zhuangbing

ALTER TABLE p_user ADD COLUMN bindOpenId VARCHAR(200) COMMENT '绑定用户OpenId';
2019.07.29 -- zhuangbing
ALTER TABLE p_company ADD COLUMN appid VARCHAR(200) COMMENT '绑定公众号appid';
ALTER TABLE wx_user DROP COLUMN company_id;
2019.07.27 ---- zhuangbing
 ALTER TABLE wx_user ADD COLUMN subscribe_status_name VARCHAR(50) COMMENT '订阅状态';
 ALTER TABLE wx_user ADD COLUMN subscribe_status ENUM('subscribe','unsubscribe') COMMENT 'subscribe(订阅)、unsubscribe(取消订阅)';
 
2019.07.26 --- zhuangbing
ALTER TABLE wx_user ADD COLUMN sex VARCHAR(50) DEFAULT '0' COMMENT '用户的性别，值为1时是男性，值为2时是女性，值为0时是未知'; 
ALTER TABLE wx_user ADD COLUMN city VARCHAR(50) COMMENT '用户所在城市'; 
ALTER TABLE wx_user ADD COLUMN country VARCHAR(50) COMMENT '用户所在国家'; 
ALTER TABLE wx_user ADD COLUMN language VARCHAR(50) COMMENT '用户的语言，简体中文为zh_CN'; 
ALTER TABLE wx_user ADD COLUMN unionid VARCHAR(50) COMMENT '只有在用户将公众号绑定到微信开放平台帐号后，才会出现该字段。'; 
ALTER TABLE wx_user ADD COLUMN province VARCHAR(50) COMMENT '用户所在省份'; 

2019.07.24 ----zhuangbing
CREATE TABLE
IF NOT EXISTS `wx_app` (
	`id` INTEGER NOT NULL auto_increment,
	`appid` VARCHAR (255) NOT NULL,
	`authorizer_refresh_token` VARCHAR (255) NOT NULL,
	`authorized_status_name` VARCHAR (50),
	`authorized_status` ENUM (
		'authorized',
		'unauthorized',
		'updateauthorized'
	),
	`nick_name` VARCHAR (50),
	`head_img` VARCHAR (50),
	`service_type_info` VARCHAR (50),
	`verify_type_info` VARCHAR (50),
	`original_id` VARCHAR (50),
	`principal_name` VARCHAR (50),
	`alias` VARCHAR (50),
	`qrcode_url` VARCHAR (50),
	`created_at` DATETIME NOT NULL,
	`updated_at` DATETIME NOT NULL,
	`deleted_at` DATETIME,
	`version` INTEGER NOT NULL DEFAULT 0,
	`company_id` INTEGER,
  `notes` varchar(255) DEFAULT NULL,
  `op_notes` varchar(50) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB;

CREATE TABLE
IF NOT EXISTS `wx_user` (
	`id` INTEGER NOT NULL auto_increment,
	`appid` VARCHAR (255) NOT NULL,
	`openid` VARCHAR (255) NOT NULL,
	`nick_name` VARCHAR (50),
	`head_img` VARCHAR (50),
	`mobile` VARCHAR (50),
	`created_at` DATETIME NOT NULL,
	`updated_at` DATETIME NOT NULL,
	`deleted_at` DATETIME,
	`version` INTEGER NOT NULL DEFAULT 0,
	`company_id` INTEGER,
  `notes` varchar(255) DEFAULT NULL,
  `op_notes` varchar(50) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB;
2019.07.22 ---zhuangbing
ALTER TABLE bi_tmt_special_performance ADD COLUMN backgroundColor VARCHAR(50) DEFAULT '#ffffff' COMMENT '专场背景颜色';
ALTER TABLE bi_tmt_special_performance ADD COLUMN fontColor VARCHAR(50) DEFAULT '#ffffff' COMMENT '专场字体颜色'; 
2019.07.19---zhuangbing
ALTER TABLE s_footerinfo ADD COLUMN icpCard VARCHAR(500) COMMENT '备案证'; 
ALTER TABLE s_footerinfo ADD COLUMN establishmentYear VARCHAR(500) COMMENT '公司成立年份'; 

2019.07.16---sy
ALTER TABLE p_company ADD COLUMN agentPerson VARCHAR(50) DEFAULT '张飞龙' COMMENT '提报key代理公司负责人'; 

2019.07.04---sy
CREATE TABLE `p_customer_files` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `applyAddr` VARCHAR(255) DEFAULT NULL COMMENT '申请地址',
  `applyArea` VARCHAR(255) DEFAULT NULL,
  `customerTypeName` VARCHAR(255) DEFAULT NULL,
  `customerType` ENUM('ent','person') DEFAULT NULL,
  `identityCardPic` VARCHAR(255) DEFAULT NULL COMMENT '身份证图片',
  `businessLicensePic` VARCHAR(255) DEFAULT NULL COMMENT '营业执照图片',
  `isLastOp` TINYINT(1) DEFAULT '1' COMMENT '是否最后的操作，0否，1是，默认为1',
  `name` VARCHAR(1000) DEFAULT NULL,
  `code` VARCHAR(100) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  `deleted_at` DATETIME DEFAULT NULL,
  `version` INT(11) NOT NULL DEFAULT '0',
  `province` VARCHAR(255) DEFAULT NULL COMMENT '省',
  `city` VARCHAR(255) DEFAULT NULL COMMENT '市',
  `identityCardNo` VARCHAR(50) DEFAULT NULL COMMENT '身份证号',
  `notes` VARCHAR(500) DEFAULT NULL COMMENT '备注',
  `ownerCompanyIds` VARCHAR(50) DEFAULT NULL COMMENT '拥有者公司id列表，格式：，1，2，',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `p_customerfiles_contacts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `mobile` VARCHAR(20) DEFAULT NULL,
  `email` VARCHAR(50) DEFAULT NULL,
  `tel` VARCHAR(20) DEFAULT NULL,
  `fax` VARCHAR(50) DEFAULT NULL,
  `zipCode` VARCHAR(20) DEFAULT NULL,
  `name` VARCHAR(1000) DEFAULT NULL,
  `code` VARCHAR(100) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  `deleted_at` DATETIME DEFAULT NULL,
  `version` INT(11) NOT NULL DEFAULT '0',
  `customerfiles_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4;


2019.07.04---zhuangbing
ALTER TABLE bi_tmt_special_performance ADD COLUMN siteThemeName VARCHAR(50) DEFAULT NULL ;
ALTER TABLE bi_tmt_special_performance ADD COLUMN siteTheme ENUM('default','spring','summer','autumn','winter') DEFAULT 'default';
 
2019.06.24---sy
ALTER TABLE `p_company_payparam` ADD COLUMN payType int DEFAULT 1 COMMENT '支付类型1：通联支付、2：中信支付';
ALTER TABLE `p_company_payparam` ADD COLUMN pay_url VARCHAR(255) DEFAULT NULL COMMENT '支付地址';

2019.07.02---zhuangbing
ALTER TABLE bi_trademarktransaction ADD COLUMN trademark_type_name VARCHAR(50) DEFAULT NULL COMMENT 'ordinary:普通商标,specialoffer:特价商标,highquality:精品商标,hot:热门商标,recommended:推荐商标';
ALTER TABLE bi_trademarktransaction ADD COLUMN trademark_type ENUM('ordinary','specialoffer','highquality','hot','recommended') DEFAULT NULL;
 
DROP TABLE IF EXISTS `bi_tmt_special_performance`;
CREATE TABLE `bi_tmt_special_performance` (
  `code` varchar(50) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `logo_img` varchar(200) DEFAULT NULL,
  `banner_img` varchar(200) DEFAULT NULL,
  `content` text,
  `desc` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `createcompany_id` int(11) DEFAULT NULL,
  `createuser_id` int(11) DEFAULT NULL,
  `updateuser_id` int(11) DEFAULT NULL,
  `ncl_one_codes` varchar(255) DEFAULT NULL,
  `orderNo` int(11) DEFAULT NULL,
  `isReleased` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2019.06.24---sy
ALTER TABLE `biz_order` ADD COLUMN wxPayOrderCode VARCHAR(50) DEFAULT NULL COMMENT '业务微信支付订单号';
ALTER TABLE `biz_order` ADD COLUMN aliPayOrderCode VARCHAR(50) DEFAULT NULL COMMENT '业务支付宝支付订单号';

2019.06.19---sy
ALTER TABLE biz_moneyaccount ADD COLUMN bankToPtTransferRate  DECIMAL(12,2) DEFAULT 0 COMMENT '银行收平台费率,千分率，如：值为2.6，计算时除以1000（平台公司收款账户有此值）';
ALTER TABLE `biz_expensevoucher` ADD COLUMN busPayOrderCode VARCHAR(100) DEFAULT NULL COMMENT '业务支付订单号';


2019.06.18---sy
ALTER TABLE `biz_order` ADD COLUMN busPayOrderCode VARCHAR(100) DEFAULT NULL COMMENT '业务支付订单号';
ALTER TABLE `biz_receiptvoucher` ADD COLUMN busPayOrderCode VARCHAR(100) DEFAULT NULL COMMENT '业务支付订单号';
ALTER TABLE `biz_moneyjourney` ADD COLUMN busPayOrderCode VARCHAR(100) DEFAULT NULL COMMENT '业务支付订单号';

ALTER TABLE `biz_receiptvoucher` ADD COLUMN buyerOpenId VARCHAR(255) DEFAULT NULL COMMENT '用户在商户appid下的唯一标识或买家在支付宝的用户id';
ALTER TABLE `biz_receiptvoucher` ADD COLUMN passTradeNo VARCHAR(255) DEFAULT NULL COMMENT '通道的统一订单号';
ALTER TABLE `biz_receiptvoucher` ADD COLUMN buyerAliLogonId VARCHAR(255) DEFAULT NULL COMMENT '买家支付宝账号';
ALTER TABLE `biz_moneyjourney` ADD COLUMN buyerOpenId VARCHAR(255) DEFAULT NULL COMMENT '用户在商户appid下的唯一标识或买家在支付宝的用户id';
ALTER TABLE `biz_moneyjourney` ADD COLUMN passTradeNo VARCHAR(255) DEFAULT NULL COMMENT '通道的统一订单号';
ALTER TABLE `biz_moneyjourney` ADD COLUMN buyerAliLogonId VARCHAR(255) DEFAULT NULL COMMENT '买家支付宝账号';


2019.06.17---sy
ALTER TABLE `s_footerinfo` ADD COLUMN icpNum VARCHAR(255) DEFAULT NULL COMMENT '备案号';
ALTER TABLE `s_producttype` ADD COLUMN oneMoblieIconUrl VARCHAR(500) DEFAULT NULL COMMENT '产品大类移动端图标地址';


2019.06.13---sy
CREATE TABLE `p_company_payparam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pay_appid` varchar(50) DEFAULT NULL,
  `mch_id` varchar(50) DEFAULT NULL,
  `pay_key` varchar(50) DEFAULT NULL,  
  `isEnabled` tinyint(1) DEFAULT '1',
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


2019.06.5---sy
ALTER TABLE s_producttype modify COLUMN itemType VARCHAR(255) DEFAULT NULL COMMENT '项目类型';
ALTER TABLE s_producttype modify COLUMN itemTypeName VARCHAR(255) DEFAULT NULL COMMENT '服务类型名称';

ALTER TABLE s_producttype ADD COLUMN isPubed int DEFAULT 1 COMMENT '是否可以交易，0不发布，1发布，默认为1';

ALTER TABLE s_serviceitem modify COLUMN itemType VARCHAR(255) DEFAULT NULL COMMENT '项目类型';
ALTER TABLE s_serviceitem modify COLUMN itemTypeName VARCHAR(255) DEFAULT NULL COMMENT '服务类型名称';

ALTER TABLE `s_producttype` ADD COLUMN oneIconUrl VARCHAR(500) DEFAULT NULL COMMENT '产品大类图标地址';



2019.05.30---sy
ALTER TABLE biz_moneyaccount ADD COLUMN payeeName VARCHAR(255) DEFAULT NULL COMMENT '收款人姓名';

2019.05.29---sy
ALTER TABLE biz_moneyjourney ADD COLUMN opNotes VARCHAR(255) DEFAULT NULL COMMENT '操作备注';
ALTER TABLE biz_moneyjourney ADD COLUMN payOrderNo VARCHAR(255) DEFAULT NULL COMMENT '支付单号';

2019.05.24---sy
ALTER TABLE biz_receiptvoucher ADD COLUMN createcompany_id int DEFAULT NULL COMMENT '创建收款单的公司'; 

2019.05.23---zhuangbing
CREATE TABLE `s_systemregist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `mobile` varchar(255) DEFAULT NULL,
  `company_addr` varchar(255) DEFAULT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `opNotes` varchar(4000) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2019.05.21---sy
ALTER TABLE bi_trademarktransaction ADD COLUMN excelName VARCHAR(1000) DEFAULT NULL COMMENT '上传时的商标名'; 
2019.05.21 ---zhuangbing
ALTER TABLE bi_trademarktransaction_need ADD COLUMN tm_name VARCHAR(50) DEFAULT NULL COMMENT '商标名称';
ALTER TABLE bi_trademarktransaction_need ADD COLUMN ncl_one_code VARCHAR(200) DEFAULT NULL COMMENT '商标大类';
ALTER TABLE bi_trademarktransaction_need ADD COLUMN tm_code VARCHAR(50) DEFAULT NULL COMMENT '商标号';
ALTER TABLE bi_trademarktransaction_need ADD COLUMN tm_applier VARCHAR(50) DEFAULT NULL COMMENT '商标申请人';
ALTER TABLE bi_trademarktransaction_need ADD COLUMN platform_quoted_price VARCHAR(50) DEFAULT NULL COMMENT '平台售价';
ALTER TABLE bi_trademarktransaction_need ADD COLUMN tmowner_id int DEFAULT NULL COMMENT '商标所属业务员';
ALTER TABLE bi_trademarktransaction ADD UNIQUE KEY (code, ncl_one_code);
2019.05.20 ---zhuangbing
DROP TABLE IF EXISTS `bi_trademarktransaction_need`;
CREATE TABLE `bi_trademarktransaction_need` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `buyer_name` varchar(50) DEFAULT NULL,
  `buyer_mobile` varchar(50) DEFAULT NULL,
  `buyer_quoted_price` varchar(50) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `op_notes` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `company_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `trademarktransaction_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2019.05.18  ---zhuangbing
ALTER TABLE bi_trademarktransaction ADD COLUMN deleted_at datetime DEFAULT NULL COMMENT '删除时间';
2019.05.18 ---商标交易表 ---zhuangbing
CREATE TABLE `bi_trademarktransaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `tm_applier` varchar(50) DEFAULT NULL,
  `ncl_one_code` varchar(50) DEFAULT NULL,
  `business_quoted_price` varchar(50) DEFAULT NULL,
  `platform_quoted_price` varchar(50) DEFAULT NULL,
  `tm_structure_name` varchar(50) DEFAULT NULL,
  `pic_url` varchar(50) DEFAULT NULL,
  `tm_status_name` varchar(50) DEFAULT NULL,
  `publish_status_name` varchar(50) DEFAULT NULL,
  `publish_status` enum('audit','fail','success','uppershelf','lowershelf') DEFAULT 'audit',
  `tm_group` varchar(200) DEFAULT NULL,
  `tm_service_item` varchar(200) DEFAULT NULL,
  `tm_introduction` varchar(500) DEFAULT NULL,
  `tm_start_day` datetime DEFAULT NULL,
  `tm_end_day` datetime DEFAULT NULL,
  `contacts` varchar(50) DEFAULT NULL,
  `mobile` varchar(50) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `op_notes` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `createcompany_id` int(11) DEFAULT NULL,
  `createuser_id` int(11) DEFAULT NULL,
  `updateuser_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2019.05.16---修改表sy
ALTER TABLE s_need ADD COLUMN customer_intention_name VARCHAR(50) DEFAULT NULL COMMENT '客户意向：yxyb: 意向一般, yxhg: 重要意向, wjt: 未接通';
ALTER TABLE s_need ADD COLUMN customer_intention ENUM('yxyb','yxhg','wjt') DEFAULT NULL;
ALTER TABLE s_need ADD COLUMN opNotes VARCHAR(4000) DEFAULT NULL COMMENT '操作备注';
CREATE TABLE `bi_buscompany_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tm_company_type` enum('xz','kz','bh','bysl','yy','bg') DEFAULT NULL,
  `tm_company_type_name` varchar(50) DEFAULT NULL COMMENT '商机类型：xz: 商标续展，kz：商标宽展，bh: 商标驳回复审，bysl: 商标不予受理，yy: 商标异议，bg: 商标变更',  
  `customer_intention` enum('yxyb','yxhg','wjt') DEFAULT NULL,
  `customer_intention_name` varchar(50) DEFAULT NULL COMMENT '客户意向：yxyb: 意向一般, yxhg: 重要意向, wjt: 未接通',
  `notes` varchar(255) DEFAULT NULL,
  `op_notes` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `createuser_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `businesscompany_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


2019.05.13---修改表sy
ALTER TABLE s_producttype ADD COLUMN iconUrl VARCHAR(500) DEFAULT NULL COMMENT '产品图标地址';
ALTER TABLE s_producttype ADD COLUMN itemTypeName VARCHAR(50) DEFAULT '商标服务' COMMENT '项目类型:ip:商标服务,pa:专利服务,ic:版权服务,icbc:工商服务,tax:财税服务';
ALTER TABLE s_producttype ADD COLUMN itemType ENUM('ip','pa','ic','icbc','tax') DEFAULT 'ip';
ALTER TABLE s_producttype ADD COLUMN pid int DEFAULT 0 COMMENT '父id';
ALTER TABLE cms_loopplay ADD COLUMN sitetheme_id int DEFAULT 1 COMMENT '主题id';
ALTER TABLE s_serviceitem ADD COLUMN producttype_pid_id int DEFAULT NULL COMMENT '产品类型父id';


2019.05.11---修改表sy
ALTER TABLE s_serviceitem ADD COLUMN iconUrl VARCHAR(500) DEFAULT NULL COMMENT '产品图标地址';
ALTER TABLE p_company ADD COLUMN siteTheme VARCHAR(255) DEFAULT 'default' COMMENT '站点主题';
ALTER TABLE p_company ADD COLUMN sitetheme_id int DEFAULT '1' COMMENT '站点主题id';

DROP TABLE p_company_theme ;
CREATE TABLE `p_site_theme` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteTheme` enum('default','spring','summer','autumn','winter') DEFAULT NULL,
  `siteThemeName` varchar(255) DEFAULT NULL,
  `isEnabled` tinyint(1) DEFAULT '0',
  `themeAddr` varchar(255) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `createuser_id` int(11) DEFAULT NULL,
  `updateuser_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


2019.05.08---修改表sy
CREATE TABLE `p_company_theme` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteTheme` enum('spring','summer','autumn','winter') DEFAULT NULL,
  `siteThemeName` varchar(255) DEFAULT NULL,
  `isEnabled` tinyint(1) DEFAULT '0',
  `themeAddr` varchar(255) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `company_id` int(11) DEFAULT NULL,
  `createuser_id` int(11) DEFAULT NULL,
  `updateuser_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2019.05.07---修改表sy
ALTER TABLE s_serviceitem ADD COLUMN mobileContentImg1 VARCHAR(500) DEFAULT NULL COMMENT '产品内容图片';
ALTER TABLE s_serviceitem ADD COLUMN mobileContentImg2 VARCHAR(500) DEFAULT NULL COMMENT '产品内容图片';
ALTER TABLE s_serviceitem ADD COLUMN mobileContentImg3 VARCHAR(500) DEFAULT NULL COMMENT '产品内容图片';
ALTER TABLE s_serviceitem ADD COLUMN mobileContentImg4 VARCHAR(500) DEFAULT NULL COMMENT '产品内容图片';
ALTER TABLE s_serviceitem ADD COLUMN mobileContentImg5 VARCHAR(500) DEFAULT NULL COMMENT '产品内容图片';
ALTER TABLE s_serviceitem ADD COLUMN mobileContentImg6 VARCHAR(500) DEFAULT NULL COMMENT '产品内容图片';
ALTER TABLE s_serviceitem ADD COLUMN mobileContentImg7 VARCHAR(500) DEFAULT NULL COMMENT '产品内容图片';
ALTER TABLE s_serviceitem ADD COLUMN mobileContentImg8 VARCHAR(500) DEFAULT NULL COMMENT '产品内容图片';


ALTER TABLE biz_receiptvoucher ADD COLUMN sveItemCode VARCHAR(50) DEFAULT NULL COMMENT '项目操作码';
ALTER TABLE biz_receiptvoucher ADD COLUMN sveItemName VARCHAR(50) DEFAULT NULL COMMENT '项目名称';

UPDATE `biz_receiptvoucher` t1,`biz_order` t2 SET t1.sveItemCode=t2.sveItemCode,t1.sveItemName=t2.sveItemName  WHERE t1.`sourceOrderNo` = t2.`code` AND t1.`sourceOrderNo` IS NOT NULL;

2019.04.29---修改表sy
        ALTER TABLE bi_businessinfo DROP COLUMN `code`;
        ALTER TABLE bi_businessinfo ADD COLUMN sourceOrderCode VARCHAR(50) DEFAULT NULL COMMENT '来源订单号';
        ALTER TABLE biz_order ADD COLUMN orderSourceName VARCHAR(50) DEFAULT NULL COMMENT '订单来源名称gsay: 公司案源, ptsj: 平台商机';
        ALTER TABLE biz_order ADD COLUMN orderSource ENUM('gsay','ptsj') DEFAULT 'gsay';
        update biz_order set orderSourceName='公司案源'
        ALTER TABLE bi_businesscompany ADD COLUMN customer_intention_name VARCHAR(50) DEFAULT NULL COMMENT '客户意向：yxyb: 意向一般, yxhg: 意向很高, wjt: 未接通';
        ALTER TABLE bi_businesscompany ADD COLUMN customer_intention ENUM('yxyb','yxhg','wjt') DEFAULT NULL;        


2019.03.21  修改表sql--sy
        ALTER TABLE biz_moneyaccount ADD COLUMN isOfflinePay tinyint default 0 COMMENT '是否支持线下支付,0否，1是';

        ALTER TABLE biz_receiptvoucher ADD COLUMN certifyFileUrl VARCHAR(500) DEFAULT NULL COMMENT '证明文件url';

        ALTER TABLE biz_customerinfo ADD COLUMN  province VARCHAR(255) DEFAULT NULL COMMENT '省';
        ALTER TABLE biz_customerinfo ADD COLUMN  city VARCHAR(255) DEFAULT NULL COMMENT '市';
        ALTER TABLE biz_customerinfo ADD COLUMN  identityCardNo VARCHAR(50) DEFAULT NULL COMMENT '申请地址';
        ALTER TABLE biz_customerinfo ADD COLUMN  notes VARCHAR(500) DEFAULT NULL COMMENT '备注';

2019.03.22  修改表sql--sy
        ALTER TABLE s_serviceitem ADD COLUMN isSystem tinyint default 0 COMMENT '是否系统数据,0否，1是';
        ALTER TABLE s_producttype ADD COLUMN isSystem tinyint default 0 COMMENT '是否系统数据,0否，1是';
        ALTER TABLE p_user ADD COLUMN isSystem tinyint default 0 COMMENT '是否系统数据,0否，1是';
        ALTER TABLE p_role ADD COLUMN isSystem tinyint default 0 COMMENT '是否系统数据,0否，1是';

2019.03.23  修改表--sy
        // //商标材料引用订单表----去除了引用，把原先的code存储成了来源单号
        // this.db.models.tmstuff.belongsTo(this.db.models.order, { constraints: false, }); 
        ALTER TABLE biz_tmstuff DROP  COLUMN order_id;
        ALTER TABLE biz_trademark DROP  COLUMN caseTypeName;
        ALTER TABLE biz_trademark DROP  COLUMN caseType;
        ALTER TABLE biz_trademark ADD COLUMN producttype_id tinyint default 0 COMMENT '产品小类Id';
        ALTER TABLE biz_trademark ADD COLUMN  productTypeName VARCHAR(255) DEFAULT NULL COMMENT '产品小类名称';
        ALTER TABLE biz_order modify column productTypeName varchar(255) DEFAULT NULL COMMENT '产品小类名称';

2019.03.26 #项目管理--jr
        CREATE TABLE `pm_follow` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `memo` varchar(20) DEFAULT NULL,
          `name` varchar(1000) DEFAULT NULL,
          `code` varchar(100) DEFAULT NULL,
          `created_at` datetime NOT NULL,
          `updated_at` datetime NOT NULL,
          `deleted_at` datetime DEFAULT NULL,
          `version` int(11) NOT NULL DEFAULT '0',
          `company_id` int(11) DEFAULT NULL,
          `pmproject_id` int(11) DEFAULT NULL,
          `follower_id` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
        CREATE TABLE `pm_product` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `memo` varchar(20) DEFAULT NULL,
          `name` varchar(1000) DEFAULT NULL,
          `code` varchar(100) DEFAULT NULL,
          `created_at` datetime NOT NULL,
          `updated_at` datetime NOT NULL,
          `deleted_at` datetime DEFAULT NULL,
          `version` int(11) NOT NULL DEFAULT '0',
          `company_id` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
        CREATE TABLE `pm_project` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `memo` varchar(20) DEFAULT NULL,
          `name` varchar(1000) DEFAULT NULL,
          `code` varchar(100) DEFAULT NULL,
          `created_at` datetime NOT NULL,
          `updated_at` datetime NOT NULL,
          `deleted_at` datetime DEFAULT NULL,
          `version` int(11) NOT NULL DEFAULT '0',
          `company_id` int(11) DEFAULT NULL,
          `pmproduct_id` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
        CREATE TABLE `pm_question` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `solvTypeName` varchar(255) DEFAULT NULL,
          `solvType` enum('waithandle','inmiddle','success','invalid') DEFAULT 'waithandle',
          `urgentStateName` varchar(255) DEFAULT NULL,
          `urgentState` enum('urgent','general') DEFAULT NULL,
          `questionPic` varchar(255) DEFAULT NULL,
          `questionSlvPic` varchar(255) DEFAULT NULL,
          `solvpath` varchar(255) DEFAULT NULL,
          `submemo` varchar(255) DEFAULT NULL,
          `memo` varchar(255) DEFAULT NULL,
          `name` varchar(1000) DEFAULT NULL,
          `code` varchar(100) DEFAULT NULL,
          `created_at` datetime NOT NULL,
          `updated_at` datetime NOT NULL,
          `deleted_at` datetime DEFAULT NULL,
          `version` int(11) NOT NULL DEFAULT '0',
          `company_id` int(11) DEFAULT NULL,
          `pmproject_id` int(11) DEFAULT NULL,
          `subber_id` int(11) DEFAULT NULL,
          `exector_id` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

            

2019.03.23  修改表--sy
        ALTER TABLE biz_order ADD COLUMN isIinvoice TINYINT DEFAULT 0 COMMENT '是否需要发票，0不需要，1需要，默认为1';
        ALTER TABLE biz_receiptvoucher ADD COLUMN accountType ENUM('cash','bank','wx','alipay','other') DEFAULT 'wx';
        ALTER TABLE biz_receiptvoucher ADD COLUMN accountTypeName VARCHAR(255) DEFAULT '微信' COMMENT '帐户类型名称';
        ALTER TABLE biz_receiptvoucher ADD COLUMN payDate DATETIME NULL COMMENT '支付时间';
        ALTER TABLE biz_receiptvoucher ADD COLUMN payFlowType INT DEFAULT 1 COMMENT '支付类型,1为订单支付，2为补单';
        ALTER TABLE p_company ADD COLUMN tel VARCHAR(20) DEFAULT NULL COMMENT '座机';
        ALTER TABLE p_company ADD COLUMN fax VARCHAR(50) DEFAULT NULL COMMENT '传真';
        ALTER TABLE p_company ADD COLUMN zipCode VARCHAR(20) DEFAULT NULL COMMENT '邮政编码';

        ALTER TABLE biz_tmofficial ADD COLUMN recipients VARCHAR(50) DEFAULT NULL COMMENT '收件人';
        ALTER TABLE biz_tmofficial ADD COLUMN mobile VARCHAR(20) DEFAULT NULL COMMENT '手机号';
        ALTER TABLE biz_tmofficial ADD COLUMN email VARCHAR(50) DEFAULT NULL COMMENT '邮箱';
        ALTER TABLE biz_tmofficial ADD COLUMN province VARCHAR(50) DEFAULT NULL COMMENT '省名称';
        ALTER TABLE biz_tmofficial ADD COLUMN city VARCHAR(50) DEFAULT NULL COMMENT '市名称';
        ALTER TABLE biz_tmofficial ADD COLUMN `area` VARCHAR(20) DEFAULT NULL COMMENT '申请区域-存储省市的编码';
        ALTER TABLE biz_tmofficial ADD COLUMN address VARCHAR(255) DEFAULT NULL COMMENT '地址';
        ALTER TABLE biz_tmofficial ADD COLUMN order_id INT DEFAULT NULL COMMENT '订单id';      

        ALTER TABLE biz_tmofficial ADD COLUMN officialMailType ENUM('1','2','3') DEFAULT '1';
        ALTER TABLE biz_tmofficial ADD COLUMN officialMailTypeName VARCHAR(255) DEFAULT '微信' COMMENT '官文邮寄类型名称';

        ALTER TABLE biz_invoiceapply DROP  COLUMN bankAddrNum;
        ALTER TABLE biz_invoiceapply ADD COLUMN bankName VARCHAR(255) DEFAULT NULL COMMENT '开户行名称';
        ALTER TABLE biz_invoiceapply ADD COLUMN bankNum VARCHAR(255) DEFAULT NULL COMMENT '开户行账号';
        ALTER TABLE biz_invoiceapply ADD COLUMN taxesCertifyUrl VARCHAR(500) DEFAULT NULL COMMENT '纳税资格证明';
        ALTER TABLE biz_moneyaccount ADD COLUMN isPlatformAutopay tinyint DEFAULT NULL COMMENT '是否供平台转账，0否，1是，默认为1';

        ALTER TABLE biz_useraddr DROP  COLUMN user_id;
        ALTER TABLE biz_trademark ADD COLUMN order_id INT DEFAULT NULL COMMENT '订单id';      
        ALTER TABLE biz_useraddr ADD COLUMN createuser_id INT DEFAULT NULL COMMENT '创建用户id';     

2019.03.30  修改表--sy
        ALTER TABLE op_log DROP  COLUMN appid;
        ALTER TABLE op_log DROP  COLUMN appname;
        ALTER TABLE op_log DROP  COLUMN userid;
        ALTER TABLE op_log DROP  COLUMN username;
        ALTER TABLE op_log DROP  COLUMN sex;
        ALTER TABLE op_log DROP  COLUMN op;
        ALTER TABLE op_log DROP  COLUMN agent;
        ALTER TABLE op_log DROP  COLUMN flag;
        ALTER TABLE op_log DROP  COLUMN app_code;
        ALTER TABLE op_log DROP  COLUMN content;
        

        ALTER TABLE op_log ADD COLUMN content VARCHAR(4000) DEFAULT NULL COMMENT '操作内容';
        ALTER TABLE op_log ADD COLUMN op VARCHAR(500) DEFAULT NULL COMMENT '操作url';
        ALTER TABLE op_log ADD COLUMN agent VARCHAR(1000) DEFAULT NULL COMMENT '请求头';
        ALTER TABLE op_log ADD COLUMN company_id INT DEFAULT NULL COMMENT '公司id';
        ALTER TABLE op_log ADD COLUMN user_id INT DEFAULT NULL COMMENT '信息user，如创建订单的用户id';
        ALTER TABLE op_log ADD COLUMN createuser_id INT DEFAULT NULL COMMENT '创建此条记录的用户id';
        ALTER TABLE op_log ADD COLUMN sourceOrderNo VARCHAR(255) DEFAULT NULL COMMENT '来源单号';
        ALTER TABLE op_log ADD COLUMN paySourceOrderNo VARCHAR(255) DEFAULT NULL COMMENT '支付来源单号';
        ALTER TABLE op_log ADD COLUMN notes VARCHAR(255) DEFAULT NULL COMMENT '备注';
        ALTER TABLE op_log ADD COLUMN opStatus INT DEFAULT NULL COMMENT '操作状态，0失败，1成功';
        ALTER TABLE op_log ADD COLUMN totalSum DECIMAL(12,2) DEFAULT 0 COMMENT '总额';
        ALTER TABLE op_log ADD COLUMN opType INT DEFAULT 0 COMMENT '操作类型,1其他，2平台转账';

        ALTER TABLE p_company ADD COLUMN transferRate  DECIMAL(12,2) DEFAULT 0 COMMENT '转账费率';
        ALTER TABLE p_company ADD COLUMN moneyaccount_id INT DEFAULT NULL COMMENT '帐户id（用于线上支付转账）';
        ALTER TABLE biz_moneyaccount ADD COLUMN notes VARCHAR(255) DEFAULT NULL COMMENT '备注';
        ALTER TABLE biz_receiptvoucher modify COLUMN receiptType ENUM('sk','tk','zz','csrz','ptdsk','ptzz') DEFAULT 'sk';
        ALTER TABLE biz_moneyjourney ADD COLUMN accountType ENUM('cash','bank','wx','alipay','other') DEFAULT 'wx';
        ALTER TABLE biz_moneyjourney ADD COLUMN accountTypeName VARCHAR(255) DEFAULT '微信' COMMENT '帐户类型名称';

#4.10 蒋勇数据权限表
        CREATE TABLE `p_dataauths` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modelname` varchar(255) DEFAULT NULL,
  `auths` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `company_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;


2019.04.11  修改表--sy
ALTER TABLE biz_moneyaccount ADD COLUMN transferRate  DECIMAL(12,3) DEFAULT 0 COMMENT '转账费率';
ALTER TABLE biz_moneyaccount ADD COLUMN certificateNo VARCHAR(255) DEFAULT NULL COMMENT '证件号';
ALTER TABLE p_company DROP  COLUMN transferRate;
ALTER TABLE p_company DROP  COLUMN moneyaccount_id;
ALTER TABLE p_company ADD COLUMN cpmpanyType int DEFAULT 1 COMMENT '公司类型，self独立运作，full为全委托，part部分委托';

2019.04.12  修改表--sy
ALTER TABLE p_company DROP  COLUMN cpmpanyType;
ALTER TABLE p_company ADD COLUMN cpmpanyTypeName VARCHAR(50) default '独立运作' COMMENT '公司类型，self独立运作，full为全委托，part部分委托'; 
ALTER TABLE p_company ADD COLUMN cpmpanyType ENUM('self','full','part') DEFAULT 'self';
ALTER TABLE p_company ADD COLUMN onlyCode VARCHAR(50) DEFAULT NULL COMMENT '公司唯一编码';

ALTER TABLE p_company DROP  COLUMN cpmpanyType;
ALTER TABLE p_company DROP  COLUMN cpmpanyTypeName;
ALTER TABLE p_company ADD COLUMN companyTypeName VARCHAR(50) default '独立运作' COMMENT '公司类型，self独立运作，full为全委托，part部分委托'; 
ALTER TABLE p_company ADD COLUMN companyType ENUM('self','full','part') DEFAULT 'self';

ALTER TABLE biz_trademark DROP  COLUMN order_id;

2019.04.15  新增表--sy
CREATE TABLE `biz_companyauthcode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `macAddr` varchar(255) DEFAULT NULL COMMENT 'mac地址',
  `resetNum` int(11) DEFAULT NULL COMMENT '重置次数',
  `sortNum` int(11) DEFAULT 0 COMMENT '排序号,默认为0',  
  `isEnable` tinyint(1) DEFAULT '1' COMMENT '是否启用',
  `name` varchar(1000) DEFAULT NULL COMMENT '暂时没有用到',
  `code` varchar(50) DEFAULT NULL COMMENT '授权码',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE biz_order ADD COLUMN subType ENUM('platform','self') DEFAULT 'platform';
ALTER TABLE biz_tmstuff ADD COLUMN order_id INT DEFAULT NULL COMMENT '订单id';    


2019.04.16  修改表--sy
ALTER TABLE biz_trademark ADD COLUMN subErrorMsg VARCHAR(4000) DEFAULT NULL COMMENT '提报错误信息';


2019.04.18  修改表--sy
ALTER TABLE biz_tmofficial ADD COLUMN tmRegistNum VARCHAR(255) DEFAULT NULL COMMENT '注册号';
ALTER TABLE biz_tmofficial modify COLUMN officialType ENUM('1','2','3','4','5','6','7','8','9','10','11','12','13') DEFAULT NULL;
ALTER TABLE biz_trademark modify COLUMN officialType ENUM('1','2','3','4','5','6','7','8','9','10','11','12','13') DEFAULT NULL;

ALTER TABLE biz_order ADD COLUMN pfsettleexpense_id int DEFAULT NULL COMMENT '平台结算费用单id';
ALTER TABLE biz_trademark ADD COLUMN publicexpense_id int DEFAULT NULL COMMENT '官费费用单id';
ALTER TABLE biz_order ADD COLUMN channelProfitSum  DECIMAL(12,2) DEFAULT 0 COMMENT '订单渠道分成毛利润总额(（订单总额-官费总额）*渠道利润分成比率)';
ALTER TABLE biz_order ADD COLUMN channelProfitRate  DECIMAL(12,2) DEFAULT 0 COMMENT '渠道利润分成比率(只分订单中毛利润总额的分成)';
ALTER TABLE s_serviceitem ADD COLUMN channelProfitRate  DECIMAL(12,2) DEFAULT 0 COMMENT '渠道利润分成比率(只分订单中毛利润总额的分成)';
ALTER TABLE biz_trademark ADD COLUMN payPublicExpense int DEFAULT 0 COMMENT '支付官费,0否，1是';


2019.04.19  修改表--sy
ALTER TABLE op_log DROP  COLUMN opStatus;
ALTER TABLE op_log DROP  COLUMN opType;
ALTER TABLE op_log ADD COLUMN opLogType ENUM('other','wxPayCallback','pfTransfer') DEFAULT 'other';
ALTER TABLE op_log ADD COLUMN opLogStatus ENUM('fail','success','waitingProcess') DEFAULT 'success';
ALTER TABLE op_log ADD COLUMN opLogTypeName VARCHAR(255) DEFAULT '其他' COMMENT '操作类型，other: 其他, wxPayCallback: 微信支付回调, pfTransfer: 平台转账, officialDownload: 官文下载';
ALTER TABLE op_log ADD COLUMN opLogStatusName VARCHAR(255) DEFAULT '成功' COMMENT '操作状态，fail: 失败, success: 成功, waitingProcess: 等待处理';
ALTER TABLE biz_order ADD COLUMN pfSettleProfit int DEFAULT 0 COMMENT '平台结算渠道利润,0否，1是';
ALTER TABLE biz_order ADD COLUMN pfProfitSum DECIMAL(12,2) DEFAULT 0 COMMENT '订单平台毛利润总额（订单毛利润总额-订单渠道分成毛利润总额）';

2019.04.20  修改表--sy
ALTER TABLE biz_trademark ADD COLUMN nclPublicExpense DECIMAL(12,2) DEFAULT 0 COMMENT '尼斯官费总额';
ALTER TABLE op_log modify COLUMN opLogType ENUM('other','wxPayCallback','pfTransfer','officialDownload','publicExpense','orderSettlement') DEFAULT 'other';
ALTER TABLE op_log modify COLUMN opLogTypeName VARCHAR(255) DEFAULT '其他' COMMENT '操作类型，other: 其他, wxPayCallback: 微信支付回调, pfTransfer: 平台转账, officialDownload: 官文下载, publicExpense: 官费扣除, orderSettlement: 订单利润结算';

ALTER TABLE biz_expensevoucher modify COLUMN expenseType ENUM('gf','sxf','tax','salary','sale','mag','channelSettleProfit') DEFAULT NULL;
ALTER TABLE biz_expensevoucher modify COLUMN expenseTypeName VARCHAR(255) DEFAULT NULL COMMENT '收款类型，gf: 官费, sxf: 手续费, tax: 税金, salary: 薪酬, sale: 销售费用, mag: 管理费用, channelSettleProfit: 订单渠道分润结算';


ALTER TABLE biz_trademark modify COLUMN sourceType ENUM('order','expensevoucher','receiptvoucher','trademark') DEFAULT NULL;
ALTER TABLE biz_trademark modify COLUMN sourceTypeName VARCHAR(50) DEFAULT NULL COMMENT '来源类型:ENUM=source_type，order: 订单,expensevoucher: 费用单,receiptvoucher: 收款单, trademark: 商标单';
ALTER TABLE biz_receiptvoucher modify COLUMN sourceType ENUM('order','expensevoucher','receiptvoucher','trademark') DEFAULT NULL;
ALTER TABLE biz_receiptvoucher modify COLUMN sourceTypeName VARCHAR(50) DEFAULT NULL COMMENT '来源类型:ENUM=source_type，order: 订单,expensevoucher: 费用单,receiptvoucher: 收款单, trademark: 商标单';
ALTER TABLE biz_order modify COLUMN sourceType ENUM('order','expensevoucher','receiptvoucher','trademark') DEFAULT NULL;
ALTER TABLE biz_order modify COLUMN sourceTypeName VARCHAR(50) DEFAULT NULL COMMENT '来源类型:ENUM=source_type，order: 订单,expensevoucher: 费用单,receiptvoucher: 收款单, trademark: 商标单';
ALTER TABLE biz_invoiceapply modify COLUMN sourceType ENUM('order','expensevoucher','receiptvoucher','trademark') DEFAULT NULL;
ALTER TABLE biz_invoiceapply modify COLUMN sourceTypeName VARCHAR(50) DEFAULT NULL COMMENT '来源类型:ENUM=source_type，order: 订单,expensevoucher: 费用单,receiptvoucher: 收款单, trademark: 商标单';
ALTER TABLE biz_expensevoucher modify COLUMN sourceType ENUM('order','expensevoucher','receiptvoucher','trademark') DEFAULT NULL;
ALTER TABLE biz_expensevoucher modify COLUMN sourceTypeName VARCHAR(50) DEFAULT NULL COMMENT '来源类型:ENUM=source_type，order: 订单,expensevoucher: 费用单,receiptvoucher: 收款单, trademark: 商标单';

2019.04.22  修改表--sy
ALTER TABLE biz_tmstuff DROP  COLUMN order_id;

201904.24 修改表pmquestion
ALTER TABLE pm_question modify COLUMN solvType ENUM('waithandle','inmiddle','waitcheck','success','invalid') DEFAULT 'waithandle';

201904.27 新增表 --- sy
CREATE TABLE `bi_businesscompany` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) DEFAULT NULL COMMENT '唯一码',
  `applicant_cn` VARCHAR(1000) DEFAULT NULL COMMENT '申请人中文',
  `applicant_en` VARCHAR(1000) DEFAULT NULL COMMENT '申请人英文',
  `applicant_cn_addr` VARCHAR(500) DEFAULT NULL COMMENT '申请人中文地址',
  `applicant_en_addr` VARCHAR(1000) DEFAULT NULL COMMENT '申请人英文地址',
  `tm_end_day_min` DATETIME DEFAULT NULL COMMENT '商标截至时间（续展时间）最小，格式2019-03-01',
  `tm_end_day_max` DATETIME DEFAULT NULL COMMENT '商标截至时间（续展时间）最大，格式2019-03-01',
  `reg_property` VARCHAR(255) DEFAULT NULL COMMENT '企业性质',
  `reg_capital` DECIMAL(12,2) DEFAULT NULL COMMENT '注册资本',
  `company_mail` VARCHAR(50) DEFAULT NULL COMMENT '公司邮箱',
  `sve_item_code` VARCHAR(50) DEFAULT NULL COMMENT '项目操作码',
  `sve_item_name` VARCHAR(100) DEFAULT NULL COMMENT '项目名称',
  `tm_company_type_name` VARCHAR(50) DEFAULT NULL COMMENT '商机类型名称',
  `tm_company_type` ENUM('xz','kz','bh','bysl','yy','bg') DEFAULT NULL COMMENT '商机类型：xz: 商标续展，kz：商标宽展，bh: 商标驳回复审，bysl: 商标不予受理，yy: 商标异议，bg: 商标变更',
  `total_amount` INT(11) DEFAULT NULL COMMENT '商机总数',
  `total_worth` INT(11) DEFAULT NULL COMMENT '商机价值',
  `business_status_name` VARCHAR(50) DEFAULT NULL COMMENT '商机状态名称',
  `business_status` ENUM('wff','yff','wrl','yrl','bhz') DEFAULT NULL COMMENT '商机状态：wff未分发、yff已分发、wrl未认领、yrl已认领、bhz保护中',
  `claim_time` DATETIME DEFAULT NULL COMMENT '认领时间',
  `recovery_time` DATETIME DEFAULT NULL COMMENT '收回截止时间',
  `indicative_price` VARCHAR(50) DEFAULT '¥1399(官费:500)' COMMENT '指导价格',
  `invalid_num` INT(11) DEFAULT '0' COMMENT '商机无效数量--存储业务员打商标无效标记的次数',
  `order_num` INT(11) DEFAULT NULL COMMENT '下订单数量',
  `check_mobile` INT(11) DEFAULT '0' COMMENT '验证手机是否有效0无效，1有效',
  `mobile_invalid_num` INT(11) DEFAULT '0' COMMENT '手机无效次数',
  `bus_invalid_num` INT(11) DEFAULT NULL COMMENT '业务无效次数',
  `mobile_list` VARCHAR(4000) DEFAULT NULL COMMENT '手机号列表，格式：["15010929366","15010929388"]',
  `notes` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `op_notes` VARCHAR(50) DEFAULT NULL COMMENT '操作备注',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME DEFAULT NULL,
  `version` INT(11) DEFAULT '0',
  `company_id` INT(11) DEFAULT '1' COMMENT '来源，默认1即平台',
  `createcompany_id` INT(11) DEFAULT NULL COMMENT '认领人公司',
  `createuser_id` INT(11) DEFAULT NULL COMMENT '认领人',
  `updateuser_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `bi_businessinfo` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) DEFAULT NULL COMMENT '唯一码',
  `sourceCode` VARCHAR(255) DEFAULT NULL COMMENT '来源唯一码',
  `tm_type` VARCHAR(50) DEFAULT NULL COMMENT '商标类别：p普通商标、J集体商标、Z证明商标、T特殊商标',
  `tm_name` VARCHAR(1000) DEFAULT NULL COMMENT '商标名称',
  `pic_url` VARCHAR(500) DEFAULT NULL COMMENT '商标大图片',
  `tm_agency` VARCHAR(1000) DEFAULT NULL COMMENT '商标代理机构',
  `apply_day` DATETIME DEFAULT NULL COMMENT '申请时间',
  `tm_regist_num` VARCHAR(100) DEFAULT NULL COMMENT '商标注册号',
  `ncl_one_codes` VARCHAR(50) DEFAULT NULL COMMENT '所属尼斯大类',
  `applicant_cn` VARCHAR(1000) DEFAULT NULL COMMENT '申请人中文',
  `applicant_en` VARCHAR(1000) DEFAULT NULL COMMENT '申请人英文',
  `applicant_cn_addr` VARCHAR(500) DEFAULT NULL COMMENT '申请人中文地址',
  `applicant_en_addr` VARCHAR(1000) DEFAULT NULL COMMENT '申请人英文地址',
  `regist_notice_day` DATETIME DEFAULT NULL COMMENT '注册公告日期',
  `regist_notice_issue` INT(11) DEFAULT NULL COMMENT '注册公告期号',
  `regist_notice_page_num` INT(11) DEFAULT NULL COMMENT '注册公告页码',
  `original_regist_notice_day` DATETIME DEFAULT NULL COMMENT '原注册公告日期',
  `tm_end_day` DATETIME DEFAULT NULL COMMENT '商标截至时间（续展时间）',
  `known` INT(11) DEFAULT NULL COMMENT '是否驰名商标',
  `tm_company_type_name` VARCHAR(50) DEFAULT NULL COMMENT '商机类型名称',
  `tm_company_type` ENUM('xz','kz','bh','bysl','yy','bg') DEFAULT NULL COMMENT '商机类型：xz: 商标续展，kz：商标宽展，bh: 商标驳回复审，bysl: 商标不予受理，yy: 商标异议，bg: 商标变更',
  `legal_status_name` VARCHAR(50) DEFAULT NULL COMMENT '法律状态名称',
  `dispose_status_name` VARCHAR(50) DEFAULT NULL COMMENT '处理状态名称',
  `dispose_status` ENUM('wxd','yxd','wx') DEFAULT NULL COMMENT '处理状态：wxd: 未下单, yxd: 已下单, wx: 无效',
  `notes` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `op_notes` VARCHAR(50) DEFAULT NULL COMMENT '操作备注',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME DEFAULT NULL,
  `version` INT(11) DEFAULT '0',
  `createcompany_id` INT(11) DEFAULT NULL COMMENT '所属人公司',
  `createuser_id` INT(11) DEFAULT NULL COMMENT '所属人',
  `updateuser_id` INT(11) DEFAULT NULL COMMENT '修改用户',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

ALTER TABLE p_user ADD COLUMN freeBusNum int DEFAULT 0 COMMENT '免费商机次数';
ALTER TABLE biz_orderflow ADD COLUMN notes int DEFAULT NULL COMMENT '备注';
