-- ----------------------------
-- Table structure for KW_CHECK_ITEM
-- ----------------------------
--DROP TABLE "KW_CHECK_ITEM";
CREATE TABLE "KW_CHECK_ITEM" (
  "ID" NUMBER NOT NULL,
  "TYPE" VARCHAR2(50 BYTE),
  "ITEM" VARCHAR2(255 BYTE),
  "FIELDS" VARCHAR2(255 BYTE),
  "ALIAS" VARCHAR2(255 BYTE),
  "SQL" CLOB
)

-- ----------------------------
-- Records of KW_CHECK_ITEM
-- ----------------------------
INSERT INTO "KW_CHECK_ITEM" VALUES ('8', '编排', '考场试卷数大于等于编排考生数', '批次,考区,考点,考场号', 'year_code,org_name,unit_name,room_code', 'select e.year_code,
       o.org_code || ''-'' || o.org_name org_name,
       eu.unit_code || ''-'' || eu.unit_name unit_name,
       r.room_code
  from kw_layout               l,
       kw_layout_volume_course v,
       kw_layout_room          r,
       pl_exam_time            e,
       kw_exam_point           p,
       kw_exam_unit            eu,
       cf_organization         o
 where l.id = r.layout_id
   and v.room_id = r.id
   and r.exam_time_id = e.id
   and p.id = l.exam_point_id
   and eu.id = l.exam_unit_id
   and o.id = p.org_id
   and e.year_code = ?
   and v.bag_capacity < v.stu_num
 order by o.org_code, p.exam_code, eu.unit_code, r.room_code
');
INSERT INTO "KW_CHECK_ITEM" VALUES ('10', '编排', '考场存在相同座位号', '批次,考区,考点,考场号,座位号,准考证,姓名', 'year_code,org_name,unit_name,room_code,seat_no,ticket_no,std_name', 'select ? year_code,o.org_name,eu.unit_name,r.room_code,d.seat_no,d.ticket_no,d.std_name 
from kw_layout_detail d,kw_layout k,kw_layout_room r,kw_exam_point p,cf_organization o,kw_exam_unit eu
where k.id=r.layout_id and r.id=d.room_id 
and p.id=k.exam_point_id
and eu.id=k.exam_unit_id
and o.id=p.org_id
and k.exam_time_id=(select pe.id from pl_exam_time pe where pe.year_code=?)
and exists(
select * from kw_layout_detail d1,kw_layout k1,kw_layout_room r1 where k1.id=r1.layout_id and r1.id=d1.room_id and k1.exam_time_id=(select pe.id from pl_exam_time pe where pe.year_code=?)
and k.exam_times=k1.exam_times and r1.exam_point_id=r.exam_point_id and r1.exam_unit_id=r.exam_unit_id and d1.room_id=d.room_id and d1.seat_no=d.seat_no
and d1.id<>d.id
)
order by o.org_code,eu.unit_code,r.room_code,d.seat_no
');
INSERT INTO "KW_CHECK_ITEM" VALUES ('11', '编排', '同时段存在同一考生', '批次,考区,考点,考场号,座位号,准考证,姓名', 'year_code,org_name,unit_name,room_code,seat_no,ticket_no,std_name', 'select ? year_code,o.org_name,eu.unit_name,r.room_code,d.seat_no,d.ticket_no,d.std_name 
from kw_layout_detail d,kw_layout k,kw_layout_room r,kw_exam_point p,cf_organization o,kw_exam_unit eu
where k.id=r.layout_id and r.id=d.room_id 
and p.id=k.exam_point_id
and eu.id=k.exam_unit_id
and o.id=p.org_id
and k.exam_time_id=(select pe.id from pl_exam_time pe where pe.year_code=?)
and exists(
select * from kw_layout_detail d1,kw_layout k1,kw_layout_room r1 where k1.id=r1.layout_id and r1.id=d1.room_id and k1.exam_time_id=(select pe.id from pl_exam_time pe where pe.year_code=?)
and k.exam_times=k1.exam_times 
and d1.reg_id=d.reg_id
and d1.id<>d.id
)
order by o.org_code,eu.unit_code,r.room_code,d.seat_no
');
INSERT INTO "KW_CHECK_ITEM" VALUES ('12', '编排', '同一考生相同时间在不同考场', '准考证,姓名,场次', 'ticket_no,std_name,exam_times', 'select d.ticket_no,d.std_name,k.exam_times
from kw_layout_detail d,kw_layout k,kw_layout_room r where k.id=r.layout_id and r.id=d.room_id and k.exam_time_id=(select pe.id from pl_exam_time pe where pe.year_code=?)
group by k.exam_times,d.ticket_no,d.std_name
having count(1)>1
order by d.ticket_no
');
INSERT INTO "KW_CHECK_ITEM" VALUES ('13', '编排', '编排课程与报考课程一致性', '报考课程数,编排课程数', 'enrolcount,layoutcount', 'select enrolcount,layoutcount from (
select 
(select count(distinct a.course_id)
from std_enrol a where a.fee_status=1 and a.year_code=? ) enrolcount,
(select count(distinct d.course_id) from kw_layout_detail d,kw_layout k,kw_layout_room r where k.id=r.layout_id and r.id=d.room_id and k.exam_time_id=(select pe.id from pl_exam_time pe where pe.year_code=?)
) layoutcount
from dual
) where enrolcount<>layoutcount
');
INSERT INTO "KW_CHECK_ITEM" VALUES ('9', '编排', '报考科次小于编排科目试卷数', '批次,考区,课程', 'year_code,org_name,course_name', 'select * from (
select a.year_code,a.enrol_agent_id org_id,o.org_code||o.org_name  org_name, a.course_id,o.org_code,
pc.course_code||pc.course_name course_name,count(1) total
from std_enrol a 
left join cf_organization o on o.id=a.enrol_agent_id
left join pl_course pc on pc.id=a.course_id
where  a.fee_status=1 and a.year_code=?
group by a.year_code,a.enrol_agent_id,a.course_id,o.org_code,o.org_name,pc.course_code,pc.course_name
) c where exists (
select * from (
select   p.org_id,                                                                   
        vc.course_id,   
         sum(bag5)*5+sum(bag15)*15+sum(bag30)*30 total                    
    from (                                                                      
        select                                                                  
            v.id,v.room_id,v.course_id,v.security_code,                         
            case when v.stu_num <=5 and v.stu_num>0 then 1 else 0 end bag5,     
            case when v.stu_num <=15 and v.stu_num>5 then 1 else 0 end bag15,   
            case when v.stu_num <=30 and v.stu_num>15 then 1 else 0 end bag30      
        from kw_layout_volume_course v                                          
     ) vc                                                                       
     left join kw_layout_room ro on ro.id=vc.room_id                           
     left join kw_layout ly on ly.id=ro.layout_id                              
     left join kw_exam_point p                            
          on (p.exam_time_id=ly.exam_time_id and p.id=ly.exam_point_id)        
    where ly.exam_time_id=(select pe.id from pl_exam_time pe where pe.year_code=?)                                   
     group by p.org_id,vc.course_id      
 
     ) d where d.course_id=c.course_id and d.org_id=c.org_id and d.total<c.total
     )
     order by c.org_code,c.course_name');
INSERT INTO "KW_CHECK_ITEM" VALUES ('1', '报考', '同一天跨市报考', '批次,准考证,姓名,电话,课程,报考考区,考试场次,报考类型,报考时间,收费标准,报考状态', 'year_code,ticket_no,std_name,sdt_dh,course_code,org_name,exam_batch,enrol_type_name,create_time,fee_price,status_name', 'select distinct c.* from (
select a.*,o.org_name,o.city_code,m.major_name,pc.course_name,r.std_dh,et.dict_text enrol_type_name,st.dict_text status_name
from std_enrol a ,cf_organization o,pl_major m,pl_course pc,std_reg_info r ,frame_dict et ,frame_dict st
where a.enrol_agent_id=o.id and r.id=a.std_reg_id 
and et.dict_name=''EnrolType'' and et.dict_value=a.enrol_type
and st.dict_name=''enrol_status'' and st.dict_value=a.status
and a.major_id=m.id
and a.course_id=pc.id
and a.fee_status=1 
and a.status>=4 
and a.year_code=201910
) c where  exists
(
select 1
from std_enrol b , cf_organization p
where b.enrol_agent_id=p.id
and b.fee_status=1 
and b.status>=4 
and b.year_code=c.year_code
and (
(b.std_reg_id=c.std_reg_id and b.exam_batch in(''A'',''B'') and  c.exam_batch in(''A'',''B'') and p.city_code <> c.city_code) or 
(b.std_reg_id=c.std_reg_id and b.exam_batch in(''C'',''D'') and  c.exam_batch in(''C'',''D'') and p.city_code <> c.city_code)
)
)
order by c.ticket_no

');
INSERT INTO "KW_CHECK_ITEM" VALUES ('6', '编排', '编排与报考一致性', '批次,准考证,姓名,电话,课程,报考考区,考试场次,报考类型,报考时间,收费标准,报考状态', 'year_code,ticket_no,std_name,sdt_dh,course_code,org_name,exam_batch,enrol_type_name,create_time,fee_price,status_name', 'select a.*,o.org_name,r.std_dh,et.dict_text enrol_type_name,st.dict_text status_name
from std_enrol a ,cf_organization o,std_reg_info r ,frame_dict et ,frame_dict st
where o.id=a.enrol_agent_id and r.id=a.std_reg_id 
and et.dict_name=''EnrolType'' and et.dict_value=a.enrol_type
and st.dict_name=''enrol_status'' and st.dict_value=a.status
and o.org_type=4
and a.fee_status=1 
and a.status>=4 
and a.year_code=201910
and not exists (select 1 from kw_layout_detail d where d.course_id=a.course_id and d.reg_id=a.std_reg_id and d.exam_time_id=a.exam_time_id)
order by a.ticket_no
');
INSERT INTO "KW_CHECK_ITEM" VALUES ('2', '报考', '报考场次与课程计划场次不同', '批次,准考证,姓名,电话,课程,报考考区,考试场次,报考类型,报考时间,收费标准,报考状态', 'year_code,ticket_no,std_name,sdt_dh,course_code,org_name,exam_batch,enrol_type_name,create_time,fee_price,status_name', 'select a.*,o.org_name,r.std_dh,et.dict_text enrol_type_name,st.dict_text status_name
from std_enrol a ,cf_organization o,std_reg_info r ,frame_dict et ,frame_dict st
where o.id=a.enrol_agent_id and r.id=a.std_reg_id 
and et.dict_name=''EnrolType'' and et.dict_value=a.enrol_type
and st.dict_name=''enrol_status'' and st.dict_value=a.status
and a.fee_status=1 
and a.status>=4 
and a.year_code=?
and not exists (
select * from pl_merge_publish_plan p  where  p.year_code=a.year_code and a.course_id=p.course_id and a.exam_batch=p.exam_time
)
order by a.ticket_no
');
INSERT INTO "KW_CHECK_ITEM" VALUES ('3', '报考', '课程指定', '批次,准考证,姓名,电话,课程,报考考区,考试场次,报考类型,报考时间,收费标准,报考状态', 'year_code,ticket_no,std_name,sdt_dh,course_code,org_name,exam_batch,enrol_type_name,create_time,fee_price,status_name', 'select a.*,o.org_name,r.std_dh,et.dict_text enrol_type_name,st.dict_text status_name
from std_enrol a ,cf_organization o,std_reg_info r ,frame_dict et ,frame_dict st
where o.id=a.enrol_agent_id and r.id=a.std_reg_id 
and et.dict_name=''EnrolType'' and et.dict_value=a.enrol_type
and st.dict_name=''enrol_status'' and st.dict_value=a.status
and o.org_type=4
and a.fee_status=1 
and a.status>=4 
and a.year_code=?
and exists (
select * from pl_exam_area_course e where  e.exam_time_id=a.exam_time_id and e.course_id=a.course_id
)
and not exists (
select * from pl_exam_area_course e where e.exam_time_id=a.exam_time_id  and e.course_id=a.course_id and e.org_id=a.enrol_agent_id
)
order by a.ticket_no
');
INSERT INTO "KW_CHECK_ITEM" VALUES ('4', '报考', '课程限制', '批次,准考证,姓名,电话,课程,报考考区,考试场次,报考类型,报考时间,收费标准,报考状态', 'year_code,ticket_no,std_name,sdt_dh,course_code,org_name,exam_batch,enrol_type_name,create_time,fee_price,status_name', 'select a.*,o.org_name,r.std_dh,et.dict_text enrol_type_name,st.dict_text status_name
from std_enrol a ,cf_organization o,std_reg_info r ,frame_dict et ,frame_dict st
where o.id=a.enrol_agent_id and r.id=a.std_reg_id 
and et.dict_name=''EnrolType'' and et.dict_value=a.enrol_type
and st.dict_name=''enrol_status'' and st.dict_value=a.status
and o.org_type=4
and a.fee_status=1 
and a.status>=4 
and a.year_code=?
and  exists (
select * from pl_exam_area_course_limit e where e.exam_time_id=a.exam_time_id  and e.course_id=a.course_id and e.org_id=a.enrol_agent_id
)
order by a.ticket_no
');
INSERT INTO "KW_CHECK_ITEM" VALUES ('5', '报考', '报考不存在支付成功订单', '批次,准考证,姓名,电话,课程,报考考区,考试场次,报考类型,报考时间,收费标准,报考状态', 'year_code,ticket_no,std_name,sdt_dh,course_code,org_name,exam_batch,enrol_type_name,create_time,fee_price,status_name', 'select a.*,o.org_name,r.std_dh,et.dict_text enrol_type_name,st.dict_text status_name
from std_enrol a ,cf_organization o,std_reg_info r ,frame_dict et ,frame_dict st
where o.id=a.enrol_agent_id and r.id=a.std_reg_id 
and et.dict_name=''EnrolType'' and et.dict_value=a.enrol_type
and st.dict_name=''enrol_status'' and st.dict_value=a.status
and o.org_type=4
and a.fee_status=1 
and a.status>=4 
and a.year_code=?
and not exists (
select * from pay_bill_union b where b.business_id=a.id and b.bill_status=''success''
)
order by a.ticket_no
');
INSERT INTO "KW_CHECK_ITEM" VALUES ('7', '编排', '编排场次与报考场次一致性', '批次,准考证,姓名,电话,课程,报考考区,考试场次,报考类型,报考时间,收费标准,报考状态', 'year_code,ticket_no,std_name,sdt_dh,course_code,org_name,exam_batch,enrol_type_name,create_time,fee_price,status_name', 'select a.*,o.org_name,r.std_dh,et.dict_text enrol_type_name,st.dict_text status_name
from std_enrol a ,cf_organization o,std_reg_info r ,frame_dict et ,frame_dict st
where o.id=a.enrol_agent_id and r.id=a.std_reg_id 
and et.dict_name=''EnrolType'' and et.dict_value=a.enrol_type
and st.dict_name=''enrol_status'' and st.dict_value=a.status
and o.org_type=4
and a.fee_status=1 
and a.status>=4 
and a.year_code=?
and not exists (
select * from kw_layout_detail d ,kw_layout_room r,kw_layout l
where d.room_id=r.id and r.layout_id=l.id
and d.exam_time_id=a.exam_time_id
and a.id=d.enrol_id
and a.exam_batch=l.exam_times
)
order by a.ticket_no
');
COMMIT;
COMMIT;

-- ----------------------------
-- Primary Key structure for table KW_CHECK_ITEM
-- ----------------------------
ALTER TABLE "KW_CHECK_ITEM" ADD CONSTRAINT "SYS_C007498" PRIMARY KEY ("ID");

-- ----------------------------
-- Checks structure for table KW_CHECK_ITEM
-- ----------------------------
ALTER TABLE "KW_CHECK_ITEM" ADD CONSTRAINT "SYS_C007497" CHECK ("ID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;



INSERT INTO "FRAME_RES"("RES_ID", "RES_NAME", "RES_ALIAS", "RES_URL", "RES_PID", "RES_LEVEL", "RES_TYPE", "RES_CSS", "RES_STATUS", "RES_ORDER", "RES_DESC") VALUES ('478', '数据校验', NULL, '/hmsoft/kw/checkitem', '10000', '2', 'Menu', NULL, 'Active', '10', NULL);