superdesk_superservice/sql/wangxz/wangxz.sql

ALTER TABLE `tb_app_user`   
  ADD COLUMN `device_type` INT(1) NULL  COMMENT '1-android 2-ios';
  
ALTER TABLE .`tb_app_user`   
  ADD COLUMN `device_no` VARCHAR(255) NULL  COMMENT '设备号' AFTER `device_type`;
  
ALTER TABLE `tb_app_function`   
  ADD COLUMN `IS_APP` INT(1) DEFAULT 0  NULL  COMMENT '是否app功能(0，否；1，是)' AFTER `ORDER_NUMBER`;
  
ALTER TABLE `tb_app_menu`   
  ADD COLUMN `IS_APP` INT(1) DEFAULT 0  NULL  COMMENT '是否app菜单(0，否；1，是)' AFTER `remark`;
  
ALTER TABLE `tb_app_menu`   
  ADD COLUMN `ICON_URL` VARCHAR(200)  NULL  COMMENT '手机菜单图片链接';
  
ALTER TABLE `tb_app_menu`   
  ADD COLUMN `MENU_CODE` VARCHAR(100) NULL  COMMENT '菜单编码';
  
DROP TABLE IF EXISTS tb_repair_bills;

/*==============================================================*/
/* Table: tb_repair_bills                                       */
/*==============================================================*/
CREATE TABLE tb_repair_bills
(
   id                   BIGINT NOT NULL AUTO_INCREMENT COMMENT 'ID',
   CODE                 VARCHAR(255) COMMENT '维修单编号',
   STATUS               INT(1) COMMENT '维修单状态(1,待审核;2,可抢单;3,进行中;4,挂单中;5,转单中;8,已完成;10,审核未通过)',
   repair_user          VARCHAR(255) COMMENT '报修人',
   contact_way          VARCHAR(255) COMMENT '联系方式',
   address              VARCHAR(500) COMMENT '地址',
   description          VARCHAR(2000) COMMENT '报修描述',
   repair_way           BIGINT COMMENT '报修方式',
   repair_area          BIGINT COMMENT '报修区域',
   repair_type          BIGINT COMMENT '维修类型',
   repair_type_detail   BIGINT COMMENT '维修类型小类',
   pic_url              VARCHAR(2000) COMMENT '上传图片地址多个以;分割',
   bill_from			INT(1) COMMENT '下单源(1，PC；2，微信；3，Android；4，iOS)',
   service_user         BIGINT COMMENT '维修人',
   bill_user_id         BIGINT COMMENT '下单人ID',
   has_comment			INT(1) DEFAULT '1' COMMENT '是否有评论',
   has_return_visit		INT(1) DEFAULT '1' COMMENT '是否有回访记录',
   creator              VARCHAR(100) COMMENT '创建者',
   create_time          DATETIME COMMENT '创建时间',
   MODIFIER             VARCHAR(100) COMMENT '修改者',
   modify_time          DATETIME COMMENT '修改时间',
   PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='维修单';


DROP TABLE IF EXISTS tb_repair_operate_records;

/*==============================================================*/
/* Table: tb_repair_operate_records                             */
/*==============================================================*/
CREATE TABLE tb_repair_operate_records
(
   id                   BIGINT NOT NULL AUTO_INCREMENT COMMENT 'ID',
   user_id              INT(11) COMMENT '操作人ID',
   operate_type         INT(1) COMMENT '操作类型(1.报修；2，抢单；3，转单；4，挂单；5，派单；6，完成；10，审核)',
   repair_bill_id       BIGINT COMMENT '维修单ID',
   DESCRIPTION          VARCHAR(200) COMMENT '操作描述',
   pend_bill_reason	    VARCHAR(200)	COMMENT '挂单原因ID(多个以，分割)',
   expected_time	    VARCHAR(100) COMMENT '挂单预计开始时间',
   detail               VARCHAR(200) COMMENT '操作细节描述',
   creator              VARCHAR(100) COMMENT '创建者',
   create_time          DATETIME COMMENT '创建时间',
   PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='维修单操作记录';


DROP TABLE IF EXISTS tb_user_repair_type;

/*==============================================================*/
/* Table: tb_user_repair_type                                   */
/*==============================================================*/
CREATE TABLE tb_user_repair_type
(
   id                   BIGINT NOT NULL AUTO_INCREMENT COMMENT 'ID',
   user_id              BIGINT COMMENT '用户ID',
   repair_type_id       BIGINT COMMENT '维修类型ID',
   PRIMARY KEY (id)
);

ALTER TABLE tb_user_repair_type COMMENT '人员维修类型关系表';



DROP TABLE IF EXISTS tb_repair_bill_support_users;

/*==============================================================*/
/* Table: tb_repair_bill_support_users                          */
/*==============================================================*/
CREATE TABLE tb_repair_bill_support_users
(
   bill_id              BIGINT COMMENT '维修单ID',
   support_user_id      INT(11) COMMENT '协办人ID'
);

ALTER TABLE tb_repair_bill_support_users COMMENT '维修单协办人信息表';

ALTER TABLE tb_repair_bill_support_users ADD CONSTRAINT FK_Reference_1 FOREIGN KEY (bill_id)
      REFERENCES tb_repair_bills (id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE tb_repair_bill_support_users ADD CONSTRAINT FK_Reference_2 FOREIGN KEY (support_user_id)
      REFERENCES tb_app_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT;
      
 
DROP TABLE IF EXISTS tb_repair_bill_materials;

/*==============================================================*/
/* Table: tb_repair_bill_materials                              */
/*==============================================================*/
CREATE TABLE tb_repair_bill_materials
(
   id                   BIGINT NOT NULL,
   bill_id              BIGINT COMMENT '维修单ID',
   material_name        VARCHAR(20) COMMENT '材料名称',
   amount               INT(10) COMMENT '材料数量',
   PRIMARY KEY (id)
);

ALTER TABLE tb_repair_bill_materials COMMENT '维修单耗材信息表';

ALTER TABLE tb_repair_bill_materials ADD CONSTRAINT FK_Reference_3 FOREIGN KEY (bill_id)
      REFERENCES tb_repair_bills (id) ON DELETE RESTRICT ON UPDATE RESTRICT;


      drop table if exists tb_repair_bill_comments;

/*==============================================================*/
/* Table: tb_repair_bill_comments                               */
/*==============================================================*/
create table tb_repair_bill_comments
(
   id                   bigint not null auto_increment,
   bill_id              bigint comment '维修单ID',
   open_id              varchar(40) comment '评价用户信息',
   star                 int(2) comment '星级',
   serviceImpression    bigint comment '员工印象',
   description          varchar(500) comment '描述',
   create_time          DATETIME COMMENT '创建时间',
   primary key (id)
);

alter table tb_repair_bill_comments comment '维修单评价';

alter table tb_repair_bill_comments add constraint FK_Reference_4 foreign key (bill_id)
      references tb_repair_bills (id) on delete restrict on update restrict;

DROP TABLE IF EXISTS tb_repair_return_visits;

/*==============================================================*/
/* Table: tb_repair_return_visits                               */
/*==============================================================*/
CREATE TABLE tb_repair_return_visits
(
   id                   BIGINT NOT NULL AUTO_INCREMENT,
   bill_id              BIGINT COMMENT '维修单ID',
   comment_id           BIGINT COMMENT '评价ID',
   star                 INT(2) COMMENT '星级',
   description          VARCHAR(500) COMMENT '描述',
   PRIMARY KEY (id)
);

ALTER TABLE tb_repair_return_visits COMMENT '维修单回访记录';
alter table tb_repair_return_visits add constraint FK_Reference_5 foreign key (bill_id)
      references tb_repair_bills (id) on delete restrict on update restrict;
      
CREATE TABLE `tb_weixin_notify_info` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `return_code` VARCHAR(16) DEFAULT NULL,
  `return_msg` VARCHAR(128) DEFAULT NULL,
  `nonce_str` VARCHAR(32) DEFAULT NULL,
  `sign` VARCHAR(32) DEFAULT NULL,
  `sign_type` VARCHAR(32) DEFAULT NULL,
  `result_code` VARCHAR(16) DEFAULT NULL,
  `err_code` VARCHAR(32) DEFAULT NULL,
  `err_code_des` VARCHAR(128) DEFAULT NULL,
  `openid` VARCHAR(128) DEFAULT NULL COMMENT '用户标识',
  `is_subscribe` VARCHAR(1) DEFAULT NULL,
  `trade_type` VARCHAR(16) DEFAULT NULL,
  `bank_type` VARCHAR(16) DEFAULT NULL,
  `total_fee` INT(16) DEFAULT NULL,
  `settlement_total_fee` INT(16) DEFAULT NULL,
  `cash_fee` INT(16) DEFAULT NULL,
  `transaction_id` VARCHAR(32) DEFAULT NULL COMMENT '微信支付订单号',
  `out_trade_no` VARCHAR(32) DEFAULT NULL COMMENT '商户订单号',
  `time_end` VARCHAR(14) DEFAULT NULL,
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '微信支付调用返回信息'


ALTER TABLE `tb_repair_bills`   
  ADD COLUMN `repair_cost` DECIMAL(19,2) NULL  COMMENT '维修费用' AFTER `bill_user_id`;
  
  ALTER TABLE `tb_repair_bills`   
  ADD COLUMN has_paid INT(1) DEFAULT '1' COMMENT '是否支付' AFTER `has_return_visit`;
  
  ALTER TABLE `tb_app_user`   
  CHANGE `PHOTO_URL` `PHOTO_URL` VARCHAR(200)  NULL  COMMENT '员工照片URL地址';
  
  ALTER TABLE `tb_repair_bills`   
  ADD COLUMN `end_time` DATETIME NULL  COMMENT '单完结时间(完成或者审核拒绝)' AFTER `modify_time`;
  
  ALTER TABLE `tb_app_user`   
  ADD COLUMN `is_online` INT(1) DEFAULT 1  NULL  COMMENT '是否已签到' AFTER `device_no`;
  
  ALTER TABLE `tb_repair_bills`   
  ADD COLUMN `version` INT DEFAULT 0  NOT NULL  COMMENT '版本号' AFTER `end_time`;
  
  
  ALTER TABLE `tb_wxpublicno`   
  ADD COLUMN `getAccessTokenUrl` VARCHAR(2000) NULL  COMMENT '获取授权token地址' AFTER `wxApiKey`;
  
  ALTER TABLE `tb_serviceprojectallocation`   
  ADD COLUMN `serviceIcon` VARCHAR(500) NULL  COMMENT '个性化服务图片' AFTER `type`;


  ALTER TABLE `tb_repair_bill_support_users`
  DROP FOREIGN KEY `FK_Reference_2`;

ALTER TABLE `tb_repair_bill_support_users`
  CHANGE `support_user_id` `support_user_id` BIGINT(20) NULL  COMMENT '协办人ID';

ALTER TABLE `tb_app_user`
  CHANGE `ID` `ID` BIGINT(20) NOT NULL AUTO_INCREMENT;

ALTER TABLE tb_repair_bill_support_users ADD CONSTRAINT FK_Reference_2 FOREIGN KEY (support_user_id)
      REFERENCES tb_app_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `tb_app_role`   
  ADD COLUMN `ORG_ID` INT(11) NULL  COMMENT '项目ID' AFTER `FLOOR_REPAIR`;

ALTER TABLE `tb_goods_release`   
  ADD COLUMN `orgId` INT(11) NULL  COMMENT '项目ID' AFTER `company_headuser`;
 
ALTER TABLE `tb_goods_release`   
  ADD COLUMN `virAccountId` BIGINT(20) NULL  COMMENT '企业账户ID' AFTER `orgId`;
  
 -- 项目配置账号类型，企业账号或者个人账号 11.20----
 ALTER TABLE `tb_organization`   
  ADD COLUMN `accountType` INT(1) DEFAULT 0  NULL  COMMENT '账号类型：0企业账号；1个人账号' AFTER `hdProjectName`;

 -- 白名单是否绑定微信用户 11.21----
 ALTER TABLE `tb_user`   
  ADD COLUMN `wxUserId` INT(11) NULL  COMMENT '微信用户ID' AFTER `userCode`;
 ALTER TABLE `tb_user`   
  ADD COLUMN `isBinding` INT(1) DEFAULT 0  NULL  COMMENT '白名单是否绑定微信用户' AFTER `wxUserId`;

 -- 电子放行条白名单使用 11.26----
  ALTER TABLE `tb_goods_release`   
  ADD COLUMN `userId` INT(11) NULL  COMMENT '用户ID' AFTER `virAccountId`;
  
  
 -- 关于我们1221--
CREATE TABLE `tb_about_org`(  
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `org_id` INT(11) DEFAULT NULL COMMENT '项目id',
  `title` VARCHAR(200) COMMENT '标题',
  `content` TEXT COMMENT '内容',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
  `creator` VARCHAR(20) DEFAULT NULL COMMENT '创建人',
  `modifier` VARCHAR(20) DEFAULT NULL COMMENT '修改人',
  `modify_time` DATETIME DEFAULT NULL COMMENT '修改时间',
  `is_enabled` INT(1) DEFAULT 1 COMMENT '是否可用或删除：0 禁用，1 可用',
  PRIMARY KEY (`ID`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;


--- 导航菜单图标 2019.01.09--
ALTER TABLE `tb_app_menu`   
  ADD COLUMN `menu_icon_url` VARCHAR(200) NULL  COMMENT 'WEB导航图标链接' AFTER `MENU_CODE`;

--- 商户信息表2019.1.15 ---
  CREATE TABLE `tb_merchant_detail`(  
  `ID` BIGINT(21) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `org_id` INT(11) COMMENT '项目ID',
  `module_code` VARCHAR(20) COMMENT '模块编码',
  `name` VARCHAR(50) COMMENT '商户名称',
  `app_id` VARCHAR(100) NOT NULL COMMENT '公众号appId',
  `app_secret` VARCHAR(100) NOT NULL COMMENT '公众号appSerect',
  `mch_id` VARCHAR(100) NOT NULL COMMENT '商户ID',
  `wx_apiKey` VARCHAR(100) NOT NULL COMMENT '微信商户API KEY',
  `sslcert_path` VARCHAR(200) COMMENT '证书地址',
  `sslcert_password` VARCHAR(200) COMMENT '证书密码',
  `is_enabled` BIT(1) DEFAULT 1 COMMENT '是否有效',
  `creator` VARCHAR(50) COMMENT '创建人',
  `create_time` DATETIME COMMENT '创建时间',
  `modifier` VARCHAR(50) COMMENT '修改人',
  `modify_time` DATETIME COMMENT '修改时间',
  PRIMARY KEY (`ID`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci
COMMENT='商户信息';


ALTER TABLE `tb_organization`   
  CHANGE `hdProjectId` `hdProjectId` VARCHAR(40) CHARSET utf8 COLLATE utf8_general_ci NULL  COMMENT '航电项目id',
  ADD COLUMN `hdVoiceProjectId` VARCHAR(40) NULL  COMMENT '航电客户声音项目ID' AFTER `hdProjectName`,
  ADD COLUMN `hdVoiceProjectName` VARCHAR(255) NULL  COMMENT '航电客户声音项目名称' AFTER `hdVoiceProjectId`;
  
ALTER TABLE `tb_goods_release`   
  ADD COLUMN `releaseUserName` VARCHAR(200) NULL  COMMENT '放行人姓名' AFTER `company_headuser`;
  
----20190716 报事报修多项目支持 -----
ALTER TABLE `louyu_service_test`.`tb_repair_bills`   
  ADD COLUMN `orgId` INT(11) NULL  COMMENT '项目ID';