alter table supplier_order
    change ORG_NAME COMPANY_NAME varchar(120) null;

alter table supplier_order
    add COMPANY_CODE VARCHAR(50) null;

alter table supplier_order
    add COSTCENTER VARCHAR(50) null;

alter table supplier_order
    add ORG_CODE VARCHAR(50) null;

alter table supplier_order
    add ITEM_NAME VARCHAR(255) null;

alter table supplier_order
    add TAX_RATE VARCHAR(10) null;

alter table supplier_order
    add OWNER VARCHAR(100) null;

alter table supplier_order
    add DESCRIPTION VARCHAR(100) null;

alter table supplier_order
    add CREATION_TIME DATETIME null;

alter table supplier_order
    add UPDATE_TIME DATETIME null;

alter table supplier_order
    drop column V_STATUS_NAME;

create table sys_user_org
(
    USER_ID        int          null,
    AUTHORIZED_ORG VARCHAR(255) null comment '授权组织id集合',
    OWNED_ORG      varchar(255) null comment '所属的组织'
)
    comment '用户组织关联表';

create unique index sys_user_org_USER_ID_uindex
    on sys_user_org (USER_ID);

alter table sys_user_org
    add constraint sys_user_org_pk
        primary key (USER_ID);

alter table corporate_invoice
    add OWNER int null comment '所属用户ID';

alter table sys_user_org
    drop column OWNED_ORG;

alter table sys_user_org
    add DATA_AUTH_TYPE TINYINT(1) default 0 null comment '数据权限 0 All,1 by org,2 by owner';


alter table supplier_order_item_matching
    modify PRICE VARCHAR(50) default 0 null comment '价格';

alter table supplier_order_item_matching
    modify TOTAL VARCHAR(50) default 0 null comment '金额';

alter table supplier_order_item_matching
    modify TAX VARCHAR(50) default 0 null comment '税额';

alter table supplier_order_item_matching
    add RECEIVE_NO VARCHAR(50) null comment '收货号码';

alter table supplier_order_item_matching
    add DEDUCTION_AMOUNT varchar(50) default 0 null comment '抵扣金额';

alter table supplier_order_item_matching
    add DEDUCTION_TAX varchar(50) default 0 null comment '抵扣税额';

alter table supplier_order_item_matching
    add DEDUCTION_TAX_RATE varchar(10) null comment '抵扣税率';
alter table supplier_material
    add SPECIFICATION varchar(100) null;

alter table corporate_invoice
    add IS_DEL TINYINT(1) default 0 null;

alter table corporate_invoice_items
    add IS_DEL TINYINT(1) default 0 null;

alter table supplier_order_item_matching
    add INVOICE_ID VARCHAR(100) null after ID;

alter table supplier_order_item_matching
    add ORDER_NO VARCHAR(100) null;

alter table supplier_order
    modify GOODS_NUM decimal(8) null;
alter table supplier_order
    modify RECEIVED_NUM decimal(8) null;

alter table supplier_order
    modify UNIT_PRICE decimal(20, 5) null;
alter table supplier_order
    modify AMOUNT decimal(20, 5) null;

alter table supplier_order
    modify GOODS_NUM int(8) null;

alter table supplier_order
    modify RECEIVED_NUM int(8) null;

alter table supplier_order_item_matching
    add RECEIVED_TOTAL decimal(20, 5) default 0 null;

alter table supplier_order
    change ITEM_NAME ITEM_NO varchar(100) null;


##### been updated

alter table file
    add DESCRIPTION VARCHAR(255) null;

alter table file
    add UPLOAD_USER int null;

alter table file
    add UPLOAD_TIME DATETIME null;

#### been updated

alter table invoice
    add LOGIN_ID VARCHAR(255) null;

alter table supplier_order_item_matching
    add ITEM_NO VARCHAR(100) null comment '物料号';


#### 2021/11/15

alter table corporate_invoice
    add USE_STATUS TINYINT(1) default 0 null comment '使用状态 0 未使用 1 已使用 2 后票';

##### 2021/11/16
alter table file
    modify TYPE VARCHAR(100) null comment '附件类型';
create index emp_id on t_10_employee (emp_id);

create table expense_type_ticket
(
    ID               int comment '主键ID',
    EXPENSE_TYPE_ID  int          not null comment '费用类型ID',
    TICKET_TYPE_CODE VARCHAR(20)  not null comment '票种对应CODE码',
    KIND             VARCHAR(20)  null comment '消费类型，多个可以英文逗号隔开',
    CONTENT          VARCHAR(100) null comment '消费内容，可多个以英文逗号隔开',
    ACTIVE           BOOLEAN      null comment '是否可用'
)
    comment '费用类型票种关系表-用于特定规则';

create unique index EXPENSE_TYPE_TICKET_ID_uindex
    on EXPENSE_TYPE_TICKET (ID);

alter table EXPENSE_TYPE_TICKET
    add constraint EXPENSE_TYPE_TICKET_pk
        primary key (ID);

alter table EXPENSE_TYPE_TICKET
    modify ID int auto_increment comment '主键ID';


alter table corporate_invoice_type
    drop column PATH;

alter table corporate_invoice_type
    add REVIEW BOOLEAN default FALSE not null comment '是否需要审核 默认不需要';

alter table corporate_invoice
    modify INVOICE_TYPE int null comment '发票类型';

create table invoice_item_mileage
(
    ID        BIGINT comment '主键ID',
    ITEM_DATE DATE                     null comment '行程日期',
    `FROM`    VARCHAR(100)             null comment '出发地',
    `TO`      VARCHAR(100)             null comment '到达地',
    MILEAGE   DECIMAL(10, 2) default 0 null comment '里程数',
    REASON    VARCHAR(200)             null comment '事由',
    CUSTOM1   VARCHAR(100)             null,
    CUSTOM2   VARCHAR(100)             null,
    CUSTOM3   VARCHAR(100)             null,
    CUSTOM4   VARCHAR(100)             null,
    CUSTOM5   VARCHAR(100)             null
)
    comment '里程发票明细';

create unique index invoice_item_mileage_ID_uindex
    on invoice_item_mileage (ID);

alter table invoice_item_mileage
    add constraint invoice_item_mileage_pk
        primary key (ID);

alter table invoice_item_mileage
    modify ID BIGINT auto_increment comment '主键ID';

alter table invoice_item_mileage
    add INVOICE_ID VARCHAR(50) not null comment '发票ID' after ID;


#### 1118 been updated

alter table customer
    modify ORG_UNIT_WORD varchar(20) not null;

alter table customer
    add COMPANY_WORD VARCHAR(20) not null after ORG_UNIT_WORD;

alter table files
    add MQ_ID VARCHAR(50) null comment 'MQ返回标识字符';

create table band_config
(
    ID      int,
    BAND    VARCHAR(50)  null,
    MILEAGE VARCHAR(20)  null,
    EXT01   VARCHAR(100) null,
    EXT02   VARCHAR(100) null,
    EXT03   VARCHAR(100) null,
    EXT04   VARCHAR(100) null,
    EXT05   VARCHAR(100) null,
    EXT06   VARCHAR(100) null,
    EXT07   VARCHAR(100) null,
    EXT08   VARCHAR(100) null,
    EXT09   VARCHAR(100) null,
    EXT10   VARCHAR(100) null
);

create unique index band_config_ID_uindex
    on band_config (ID);

alter table band_config
    add constraint band_config_pk
        primary key (ID);

alter table band_config
    modify ID int auto_increment;

create table band_area_config
(
    ID    INT,
    BAND  VARCHAR(50)  null,
    AREA  VARCHAR(100) null,
    HOTEL VARCHAR(100) null,
    EXT01 VARCHAR(100) null,
    EXT02 VARCHAR(100) null,
    EXT03 VARCHAR(100) null,
    EXT04 VARCHAR(100) null,
    EXT05 VARCHAR(100) null
);

create unique index band_area_config_ID_uindex
    on band_area_config (ID);

alter table band_area_config
    add constraint band_area_config_pk
        primary key (ID);

alter table band_area_config
    modify ID INT auto_increment;


#### been updated 1122
alter table corporate_invoice_type
    add SEND_TYPE TINYINT(1) default 0 null;
#### been updated 1123


alter table supplier_order
    add EXT01 VARCHAR(50) null;
alter table supplier_order
    add EXT02 VARCHAR(50) null;
alter table supplier_order
    add EXT03 VARCHAR(50) null;
alter table supplier_order
    add EXT04 VARCHAR(50) null;
alter table supplier_order
    add EXT05 VARCHAR(50) null;

alter table expense_type_ticket
    add TYPE TINYINT(1) default 0 null comment '0允许报销 1不允许报销';


alter table sys_org
    add ORG_NAME VARCHAR(100) null;
##### updated 1201


alter table invoice_attendees
    add FILES_ID int null;

alter table invoice_item_mileage
    add FILES_ID int null;

alter table invoice_items
    add FILES_ID INT null;

alter table expense_reports
    add OWNER_ID VARCHAR(150) null;

update expense_reports e
set e.OWNER_ID = (select u.EMAIL from user u where u.USER_ID = e.USER_ID)
where USER_ID is not null;


CREATE TABLE `t_10_employee`
(
    `id`                          varchar(64) NOT NULL COMMENT 'UUID',
    `emp_id`                      varchar(50)  DEFAULT NULL COMMENT '员工Id',
    `user_email`                  varchar(255) DEFAULT NULL COMMENT '邮箱 ',
    `login_id`                    varchar(255) DEFAULT NULL COMMENT '登录Id',
    `first_name`                  varchar(50)  DEFAULT NULL COMMENT '姓名-名',
    `last_name`                   varchar(50)  DEFAULT NULL COMMENT '姓名-姓',
    `org_unit1`                   varchar(200) DEFAULT NULL COMMENT '组织1',
    `org_unit2`                   varchar(200) DEFAULT NULL COMMENT '组织2',
    `org_unit3`                   varchar(200) DEFAULT NULL COMMENT '组织3',
    `org_unit4`                   varchar(200) DEFAULT NULL COMMENT '组织4',
    `org_unit5`                   varchar(200) DEFAULT NULL,
    `org_unit6`                   varchar(200) DEFAULT NULL,
    `attributer1`                 varchar(100) DEFAULT NULL COMMENT '属性1',
    `attributer2`                 varchar(100) DEFAULT NULL COMMENT '属性2',
    `attributer3`                 varchar(100) DEFAULT NULL COMMENT '属性3',
    `attributer4`                 varchar(100) DEFAULT NULL COMMENT '属性4',
    `attributer5`                 varchar(100) DEFAULT NULL COMMENT '属性5',
    `attributer6`                 varchar(100) DEFAULT NULL COMMENT '属性6',
    `attributer7`                 varchar(100) DEFAULT NULL COMMENT '属性7',
    `attributer8`                 varchar(100) DEFAULT NULL COMMENT '属性8',
    `attributer9`                 varchar(100) DEFAULT NULL COMMENT '属性9',
    `attributer10`                varchar(100) DEFAULT NULL COMMENT '属性10',
    `create_by`                   varchar(36)  DEFAULT NULL COMMENT '创建者  ',
    `create_date`                 datetime     DEFAULT NULL COMMENT '创建时间',
    `update_by`                   varchar(36)  DEFAULT NULL COMMENT '更新者',
    `update_date`                 datetime     DEFAULT NULL COMMENT '更新时间',
    `remarks`                     varchar(200) DEFAULT NULL COMMENT '备注信息',
    `expense_approver`            varchar(10)  DEFAULT NULL,
    `expense_approver_employeeID` varchar(50)  DEFAULT NULL,
    `ext11`                       varchar(100) DEFAULT NULL COMMENT '扩展11',
    `ext12`                       varchar(100) DEFAULT NULL COMMENT '扩展11',
    `ext13`                       varchar(100) DEFAULT NULL COMMENT '扩展11',
    `ext14`                       varchar(100) DEFAULT NULL COMMENT '扩展11',
    `ext15`                       varchar(100) DEFAULT NULL COMMENT '扩展11',
    `ext16`                       varchar(100) DEFAULT NULL COMMENT '扩展11',
    `ext17`                       varchar(100) DEFAULT NULL COMMENT '扩展11',
    `ext18`                       varchar(100) DEFAULT NULL COMMENT '扩展11',
    `ext19`                       varchar(100) DEFAULT NULL COMMENT '扩展11',
    `ext20`                       varchar(100) DEFAULT NULL COMMENT '扩展11',
    `active`                      varchar(10)  DEFAULT NULL COMMENT '是否激活',
    `cash_advance_account_code`   varchar(100) DEFAULT NULL COMMENT '科目',
    `crn_key`                     varchar(100) DEFAULT NULL COMMENT 'crnKey',
    `ctry_code`                   varchar(100) DEFAULT NULL COMMENT 'ctryCode',
    `ctry_sub_code`               varchar(100) DEFAULT NULL COMMENT 'subCode',
    `custom1`                     varchar(100) DEFAULT NULL COMMENT '自定义1',
    `custom2`                     varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom3`                     varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom4`                     varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom5`                     varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom6`                     varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom7`                     varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom8`                     varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom9`                     varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom10`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom11`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom12`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom13`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom14`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom15`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom16`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom17`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom18`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom19`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom20`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `custom21`                    varchar(100) DEFAULT NULL COMMENT '自定义',
    `invoice_approver`            varchar(100) DEFAULT NULL COMMENT '是否发票审批',
    `invoice_user`                varchar(100) DEFAULT NULL COMMENT '发票审批人',
    `is_test_emp`                 varchar(100) DEFAULT NULL COMMENT '是否测试',
    `ledger_key`                  varchar(100) DEFAULT NULL COMMENT 'ledgerkey',
    `ledger_name`                 varchar(100) DEFAULT NULL COMMENT 'ledgerName',
    `locale_name`                 varchar(100) DEFAULT NULL COMMENT '本地名',
    `trip_user`                   varchar(100) DEFAULT NULL COMMENT '是否临时用户',
    `mi`                          varchar(100) DEFAULT NULL COMMENT 'mi',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

alter table verified_user
    add BAND VARCHAR(50) null;

alter table verified_user
    add EXT01 VARCHAR(50) null;

alter table verified_user
    add EXT02 VARCHAR(50) null;

alter table verified_user
    add EXT03 VARCHAR(50) null;

alter table verified_user
    add EXT04 VARCHAR(50) null;

alter table verified_user
    add EXT05 VARCHAR(50) null;

alter table user
    modify TEL varchar(255) null comment '用户电话';

alter table verified_user
    modify TEL varchar(255) not null;

alter table t_10_employee
    modify login_id varchar(255) null comment '登录Id';
alter table user
    modify EMAIL varchar(255) null comment '用户邮箱';

alter table verified_user
    modify LOGIN_ID varchar(255) not null;


####updated 1210
create table user_guide
(
    ID          int,
    TITLE       VARCHAR(255)      not null comment '标题',
    CONTENT     TEXT              null comment '内容',
    UPDATE_TIME DATETIME          null comment '更新时间',
    SORT        int(5)  default 0 null comment '排序',
    IS_DEL      BOOLEAN default 0 null comment '删除标记'
)
    comment '用户向导';

create unique index user_guide_ID_uindex
    on user_guide (ID);

alter table user_guide
    add constraint user_guide_pk
        primary key (ID);

alter table user_guide
    modify ID int auto_increment;

create table sys_notification
(
    ID          int,
    TITLE       VARCHAR(255)         null comment '标题',
    CONTENT     TEXT                 null comment '内容',
    LEVEL       TINYINT(1) default 0 null comment '级别',
    UPDATE_TIME DATETIME             null comment '更新时间',
    SORT        INT(5)               null comment '排序',
    IS_DEL      BOOLEAN    default 0 null comment '删除标记'
)
    comment '系统通知';

create unique index sys_notification_ID_uindex
    on sys_notification (ID);

alter table sys_notification
    add constraint sys_notification_pk
        primary key (ID);

alter table sys_notification
    modify ID int auto_increment;

alter table user_guide
    add TITLE_EN VARCHAR(255) null;

alter table user_guide
    add CONTENT_EN TEXT null;


#### updated 1214

create table invoice_kind_matching
(
    ID      int comment '主键',
    CONTENT VARCHAR(255)         null comment '内容关键字',
    KIND    VARCHAR(10)          null comment '类型',
    ACTIVE  BOOLEAN default TRUE null comment '是否可用'
)
    comment '发票消费类型匹配表';

create unique index invoice_kind_matching_ID_uindex
    on invoice_kind_matching (ID);

alter table invoice_kind_matching
    add constraint invoice_kind_matching_pk
        primary key (ID);

alter table invoice_kind_matching
    modify ID int auto_increment comment '主键';


create index t_10_employee_login_id_index
    on t_10_employee (login_id);

create index invoice_image_REPORT_ID_index
    on invoice_image (REPORT_ID);

alter table expense_reports
    add SOURCE varchar(10) default 'APP' null;


###### UPDATED 1220
alter table supplier_order
    add SUPPLIER_NAME VARCHAR(100) null;

alter table invoice_image
    modify CREATE_DATE DATETIME null;



#####been updated
alter table corporate_invoice modify ORG_ID int(11) unsigned default 0 null;