server/ses-manage/sql/2019-06/2019-06-28.sql

-- Create table
create table STD_ENROL_LOG
(
  id           NUMBER(10) not null,
  std_reg_id   NUMBER(10),
  year_code    NUMBER(6),
  std_enrol_id NUMBER(10),
  enrol_type   NUMBER(2),
  content      VARCHAR2(1000),
  operator     VARCHAR2(100),
  oper_time    DATE,
  oper_type    NUMBER(10)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table STD_ENROL_LOG
  is '考生报考日志表';
-- Add comments to the columns 
comment on column STD_ENROL_LOG.id
  is '编号';
comment on column STD_ENROL_LOG.std_reg_id
  is '考生ID 和考生表关联';
comment on column STD_ENROL_LOG.year_code
  is '批次码 6位批次码，如：201910';
comment on column STD_ENROL_LOG.std_enrol_id
  is '报考ID 和std_enrol表关联';
comment on column STD_ENROL_LOG.enrol_type
  is '报考类型 1：个人报考；2：集体报考';
comment on column STD_ENROL_LOG.content
  is '操作内容';
comment on column STD_ENROL_LOG.operator
  is '操作人 登录名+姓名';
comment on column STD_ENROL_LOG.oper_time
  is '操作时间 精确到时分秒';
comment on column STD_ENROL_LOG.oper_type
  is '操作类型 0：新增：1：修改：2：删除';
-- Create/Recreate primary, unique and foreign key constraints 
alter table STD_ENROL_LOG
  add constraint PK_STD_ENROL_LOG_ID primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create sequence SEQ_std_enrol_log;


alter table PL_MAJOR_APPLY modify file_path VARCHAR2(256);
alter table PL_MAJOR_APPLY modify file_name VARCHAR2(128);

-- Add/modify columns 
alter table PL_MAJOR_APPLY_JOIN_SCHOOL add major_id NUMBER(10);
-- Add comments to the columns 
comment on column PL_MAJOR_APPLY_JOIN_SCHOOL.major_id
  is '专业ID';
-- Add/modify columns 
alter table CF_ORGANIZATION add status NUMBER(1);
-- Add comments to the columns 
comment on column CF_ORGANIZATION.status
  is '0-禁用,1-启用';
  
 alter table pl_major_record add apply_form NUMBER(1);
-- Add comments to the columns 
comment on column pl_major_record.apply_form
  is '申报形式'; 

drop table BY_TIME_CONFIG;
drop sequence SEQ_BY_TIME_CONFIG;
create sequence SEQ_BY_TIME_CONFIG;
  -- Create table
create table BY_TIME_CONFIG
(
  id                 NUMBER(10) not null,
  exam_time_id       NUMBER(10),
  by_date            DATE,
  apply_start_time   DATE,
  apply_end_time     DATE,
  confirm_start_time DATE,
  confirm_end_time   DATE,
  sch_chk_start_time DATE,
  sch_chk_end_time   DATE,
  student_type       NUMBER(1),
  status             NUMBER(1),
  remark             VARCHAR2(150)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table BY_TIME_CONFIG
  is '毕业时间配置表';
-- Add comments to the columns 
comment on column BY_TIME_CONFIG.id
  is '主键';
comment on column BY_TIME_CONFIG.exam_time_id
  is '时间配置ID';
comment on column BY_TIME_CONFIG.by_date
  is '毕业日期';
comment on column BY_TIME_CONFIG.apply_start_time
  is '申请开始时间';
comment on column BY_TIME_CONFIG.apply_end_time
  is '申请结束时间';
comment on column BY_TIME_CONFIG.confirm_start_time
  is '确认开始时间';
comment on column BY_TIME_CONFIG.confirm_end_time
  is '确认结束时间';
comment on column BY_TIME_CONFIG.sch_chk_start_time
  is '主考学校审核开始时间';
comment on column BY_TIME_CONFIG.sch_chk_end_time
  is '主考学校审核结束时间';
comment on column BY_TIME_CONFIG.student_type
  is '考生类型';
comment on column BY_TIME_CONFIG.status
  is '状态';
comment on column BY_TIME_CONFIG.remark
  is '备注';
-- Create/Recreate primary, unique and foreign key constraints 
alter table BY_TIME_CONFIG
  add constraint PK_BY_TIME_CONFIG_ID primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

  
  alter table pl_major_apply add IS_AUTH_BACK NUMBER(1);
-- Add comments to the columns 
comment on column pl_major_apply.IS_AUTH_BACK
  is '是否退回';
  
  
  alter table pl_major_apply_detail add major_id NUMBER(10);
-- Add comments to the columns 
comment on column pl_major_apply_detail.major_id
  is '专业ID';
  
  
drop table BY_BLACK_LIST;
drop sequense SEQ_BY_BLACK_LIST;
create sequense SEQ_BY_BLACK_LIST;
-- Create table
create table BY_BLACK_LIST
(
  id         NUMBER(10) not null,
  std_id     NUMBER(10),
  std_name   VARCHAR2(30),
  ticket_no  VARCHAR2(15),
  cert_no    VARCHAR2(18),
  start_date DATE,
  end_date   DATE,
  course_id  NUMBER(10),
  exam_date  DATE,
  remark     VARCHAR2(150),
  status     NUMBER(10)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the table 
comment on table BY_BLACK_LIST
  is '毕业黑名单';
-- Add comments to the columns 
comment on column BY_BLACK_LIST.id
  is '主键';
comment on column BY_BLACK_LIST.std_id
  is '学生ID';
comment on column BY_BLACK_LIST.std_name
  is '学生姓名';
comment on column BY_BLACK_LIST.ticket_no
  is '准考证号码';
comment on column BY_BLACK_LIST.cert_no
  is '身份证号';
comment on column BY_BLACK_LIST.start_date
  is '限制开始日期';
comment on column BY_BLACK_LIST.end_date
  is '限制结束日期';
comment on column BY_BLACK_LIST.course_id
  is '问题课程ID';
comment on column BY_BLACK_LIST.exam_date
  is '考试日期';
comment on column BY_BLACK_LIST.remark
  is '备注';
comment on column BY_BLACK_LIST.status
  is '状态 状态,0-停办、1-解禁';
-- Create/Recreate primary, unique and foreign key constraints 
alter table BY_BLACK_LIST
  add constraint PK_BY_BLACK_LIST_ID primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;
  
drop table BY_PAPER;
drop sequense SEQ_BY_PAPER;
create sequense SEQ_BY_PAPER;
-- Create table
create table BY_PAPER
(
  id           NUMBER(10) not null,
  major_id     NUMBER(10),
  std_reg_id   NUMBER(10),
  paper_title  VARCHAR2(100),
  paper_score  NUMBER(2),
  apply_school NUMBER(10),
  auth_time    DATE,
  submit_time  DATE,
  auth_status  NUMBER(2)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table BY_PAPER
  is '毕业论文';
-- Add comments to the columns 
comment on column BY_PAPER.id
  is '主键';
comment on column BY_PAPER.major_id
  is '专业ID';
comment on column BY_PAPER.std_reg_id
  is '考生ID';
comment on column BY_PAPER.paper_title
  is '论文标题';
comment on column BY_PAPER.paper_score
  is '论文成绩 0-不及格,1-及格,2-中等,3-良好,4-优秀';
comment on column BY_PAPER.apply_school
  is '上报学校';
comment on column BY_PAPER.auth_time
  is '审核时间';
comment on column BY_PAPER.submit_time
  is '提交审核时间';
comment on column BY_PAPER.auth_status
  is '审核状态 0-待审核,1-审核通过';
-- Create/Recreate primary, unique and foreign key constraints 
alter table BY_PAPER
  add constraint PK_BY_PAPER_ID primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
  
  
  drop table BY_APPLY_AUDIT;
drop sequense SEQ_BY_APPLY_AUDIT;
create sequense SEQ_BY_APPLY_AUDIT;
-- Create table
create table BY_APPLY_AUDIT
(
  id          NUMBER(10) not null,
  apply_id    NUMBER(10),
  user_id     NUMBER(10),
  auth_time   DATE,
  auth_remark VARCHAR2(150),
  auth_type   NUMBER(1),
  auth_role   NUMBER(1)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table BY_APPLY_AUDIT
  is '毕业审核表';
-- Add comments to the columns 
comment on column BY_APPLY_AUDIT.id
  is '主键';
comment on column BY_APPLY_AUDIT.apply_id
  is '毕业申请ID';
comment on column BY_APPLY_AUDIT.user_id
  is '审核用户ID';
comment on column BY_APPLY_AUDIT.auth_time
  is '审核时间';
comment on column BY_APPLY_AUDIT.auth_remark
  is '审核备注';
comment on column BY_APPLY_AUDIT.auth_type
  is '审核类型 审核类型,0-审核通过,1-审核驳回';
comment on column BY_APPLY_AUDIT.auth_role
  is '0-省考试院,1-主考学校,2-市考试院';
-- Create/Recreate primary, unique and foreign key constraints 
alter table BY_APPLY_AUDIT
  add constraint PK_BY_APPLY_AUDIT_ID primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

  drop table BY_DIAPLMA;
drop sequense SEQ_BY_DIAPLMA;
create sequense SEQ_BY_DIAPLMA;
-- Create table
create table BY_DIAPLMA
(
  id                NUMBER(10) not null,
  apply_id          NUMBER(10),
  by_time_config_id NUMBER(10),
  diaplma_no        VARCHAR2(30),
  std_id            NUMBER(10),
  ticket_no         VARCHAR2(15),
  is_dist           NUMBER(1)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table BY_DIAPLMA
  is '毕业证书表';
-- Add comments to the columns 
comment on column BY_DIAPLMA.id
  is '主键';
comment on column BY_DIAPLMA.apply_id
  is '毕业ID';
comment on column BY_DIAPLMA.by_time_config_id
  is '毕业时间ID';
comment on column BY_DIAPLMA.diaplma_no
  is '毕业证书号';
comment on column BY_DIAPLMA.std_id
  is '学生ID';
comment on column BY_DIAPLMA.ticket_no
  is '准考证号';
comment on column BY_DIAPLMA.is_dist
  is '是否发放';
-- Create/Recreate primary, unique and foreign key constraints 
alter table BY_DIAPLMA
  add constraint PK_BY_DIAPLMA_ID primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

alter table kw_layout add UNIFIED_FLAG NUMBER(1);
-- Add comments to the columns 
comment on column kw_layout.UNIFIED_FLAG
  is '是否统考编排';
  
  
-- Add/modify columns 
alter table KW_LAYOUT_ROOM add exam_addr varchar2(50);
-- Add comments to the table 
comment on table KW_LAYOUT_ROOM
  is '编排考场';
comment on column KW_LAYOUT_ROOM.EXAM_ADDR
  is '考场编排地址';