<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ListView id="__0oVqiyXqJYRtmnJ7tg4">
  <name>考试信息</name>
  <parentId>__xmA8P0JniFiisvoPiWM</parentId>
  <applicationid>__LgZds5MVHW3o08T7UcX</applicationid>
  <description>考试信息</description>
  <pagination>true</pagination>
  <pageLines>10</pageLines>
  <showTotalRow>true</showTotalRow>
  <refresh>false</refresh>
  <refreshAll>false</refreshAll>
  <newPage>false</newPage>
  <readonly>false</readonly>
  <displayType>relatedForm</displayType>
  <defaultShowMode>month</defaultShowMode>
  <collapsibleShowMode>normal</collapsibleShowMode>
  <showWaterMark>false</showWaterMark>
  <showActivityColumnType>0</showActivityColumnType>
  <searchFormId>__QURHXh602nSz6KBgx09</searchFormId>
  <autoCompose>false</autoCompose>
  <relatedForm>__TguP7KZtUDBVC1gHr6k</relatedForm>
  <permissionType>public</permissionType>
  <filterScript><![CDATA[]]></filterScript>
  <sqlFilterScript><![CDATA[(function(){

  var name = getItemValueAsString("name"); 
  var s_name = getItemValueAsString("s_name"); 
  var person = getItemValueAsString("person"); 
  var createStartDate = getItemValueAsDate("startTime"); 
  var createEndDate = getItemValueAsDate("endTime"); 
  var examType = getItemValueAsString("examType"); 
  var examState = getItemValueAsString("examState"); 
  var examine = getItemValueAsString("examine"); 
  var areacode = getItemValueAsString("areacode"); 
  var COMPUTERSTARTDATE1 = getItemValueAsDate("COMPUTERSTARTDATE1"); 
  var COMPUTERSTARTDATE2 = getItemValueAsDate("COMPUTERSTARTDATE2"); 

  var isstamina = getItemValueAsString("isstamina"); //是否实操考试
  var where=" where 1=1  "

  if(isNotNull(name)){            
    where += "and a.ITEM_EXAMNAME like '%"+name+"%' ";  
  }  
  if(isNotNull(areacode)){            
    where += "and a.ITEM_areacode like '%"+areacode+"%' ";  
  }  
  if(isNotNull(isstamina)){            
    where += "and a.ITEM_isstamina like '%"+isstamina+"%' ";  
  } 
  if(isNotNull(examine)){            
    where += "and a.ITEM_EXAMINE like '%"+examine+"%' ";  
  } 
  if(isNotNull(examState)){            
    where += "and a.ITEM_EXAMSTATE like '%"+examState+"%' ";  
  }
  if(isNotNull(s_name)){            
    where += "and a.ITEM_PAPERID like concat('%','"+s_name+"','%') ";            
  } 
  if(isNotNull(person)){            
    where += "and a.ITEM_person like concat('%','"+person+"','%') ";            
  }   
  if(isNotNull(COMPUTERSTARTDATE1)){            
    where += "and a.ITEM_COMPUTERSTARTDATE >= '"+format(COMPUTERSTARTDATE1,"yyyy-MM-dd")+"' ";            
  }  
  if(isNotNull(COMPUTERSTARTDATE2)){            
    where += "and a.ITEM_COMPUTERSTARTDATE <= DATE_FORMAT(DATE_ADD('"+format(COMPUTERSTARTDATE2,"yyyy-MM-dd")+"',INTERVAL 1 DAY),'%Y-%m-%d')";             
  }   
  if(isNotNull(createStartDate)){            
    where += "and a.ITEM_CREATEDTIME >= '"+format(createStartDate,"yyyy-MM-dd")+"' ";            
  }  
  if(isNotNull(createEndDate)){            
    where += "and a.ITEM_CREATEDTIME <= DATE_FORMAT(DATE_ADD('"+format(createEndDate,"yyyy-MM-dd")+"',INTERVAL 1 DAY),'%Y-%m-%d')";             
  }  
  if(isNotNull(examType)){            
    where += "and a.ITEM_EXAMTYPE like concat('%','"+examType+"','%') ";            
  } 
  if(isNotNull(createStartDate)){            
    where += "and a.CREATED >= '"+format(createStartDate,"yyyy-MM-dd")+"' ";            
  }            
  if(isNotNull(createEndDate)){            
    where += "and a.CREATED <= DATE_FORMAT(DATE_ADD('"+format(createEndDate,"yyyy-MM-dd")+"',INTERVAL 1 DAY),'%Y-%m-%d')";             
  } 
  if(isNotNull(createEndDate)){            
    where += "and a.CREATED <= DATE_FORMAT(DATE_ADD('"+format(createEndDate,"yyyy-MM-dd")+"',INTERVAL 1 DAY),'%Y-%m-%d')";             
  }
  var domainid = getDomainid();
  var areacode = "";
  var areacode2 = "";

  var sqlp = "SELECT supervise_region_code FROM obpm2.tenant_organizations where id = '"+domainid+"' "
  var datasp = queryByDSName("tms",sqlp);
  if(datasp!=null){
    for(var iterator = datasp.iterator();iterator.hasNext();){
      var map1 = iterator.next();//取值
      areacode= map1.get("supervise_region_code");
    }
  }

  if(isNotNull(areacode)){
    areacode2 = areacode.substr(0,2);
    //println("areacode2------------------"+areacode)
  }


  var region = getItemValueAsString("region");
  var exam_site_name = getItemValueAsString("exam_site_name");
  var exam_room_name = getItemValueAsString("exam_room_name");
  if(isNotNull(region)){                                        
    where += " and a.item_position_id = '"+region+"' "  //所属地区
  }
  if(isNotNull(exam_site_name)){                                        
    where += " and a.item_exam_site_name like '%"+exam_site_name+"%' "   //考试点
  }
  if(isNotNull(exam_room_name)){                                        
    where += " and a.ITEM_placeName like  '%"+exam_room_name+"%' "   //考场
  }


  if("64".equals(areacode2) && !"505303218683641856--__AOkTvsah9VLicJhQ1FO".equals(domainid)){
    where += " and (a.domainid = '"+domainid+"' or a.item_areacode = '"+areacode+"')"
    var sql = "select '"+domainid+"' as domainid,"+
        //"a.formid," + //表单ID
        "a.applicationid," + //应用ID
        "a.ITEM_AREACODE," + //办事区域
        "a.ITEM_SUBJECTID," + //考试科目
        "a.ITEM_EXAMTYPE," + //考试方式
        "CASE "+
        " 		a.ITEM_EXAMKIND "+
        "  		WHEN '1' THEN"+
        "  '补考' "+
        "		  WHEN '0' THEN"+
        "		  '日常考试' "+
        "  END ITEM_EXAMKIND," + //考试类型
        "a.ITEM_PAPERID," + //试卷名称
        "a.ITEM_EXAMSTATE," + //考试状态
        "a.ITEM_EXAMNAME," + //考试名称
        "date_format(a.ITEM_CREATEDTIME,'%Y-%m-%d %H:%i:%S') as ITEM_CREATEDTIME,"+
        "a.ITEM_person," + //考点联系人
        "a.ITEM_personname," + //考点联系人
        "a.ITEM_placeName," + //考场名称
        "a.ITEM_personNumber," + //考场人数
        //  "a.ITEM_ATTENTION," + //注意事项
        "CASE "+
        " 		a.ITEM_ISSTAMINA "+
        "  		WHEN '1' THEN"+
        "  '是' "+
        "		  WHEN '0' THEN"+
        "		  '否' "+
        "  END ITEM_ISSTAMINA," + //是否实操考试
        // "a.ITEM_STAMINAADDRESS," + //体能考试地址
        "date_format(a.ITEM_STAMINASTARTDATE,'%Y-%m-%d %H:%i:%S') as ITEM_STAMINASTARTDATE,"+ //体能测试开始时间
        // "a.ITEM_STAMINAENDDATE," + //体能测试结束时间
        //// "a.ITEM_EXAMRULES," + //成绩考核规则
        // "a.ITEM_THEORETICALADDRESS," + //理论考试地址
        "date_format(a.ITEM_COMPUTERSTARTDATE,'%Y-%m-%d %H:%i:%S') as ITEM_COMPUTERSTARTDATE," + //理论考试开始时间
        "a.ITEM_COMPUTERENDDATE," + //理论考试结束时间
        // "a.ITEM_WRITTENRATIO," + //理论分数占比
        //  "a.ITEM_OPERATERATIO," + //体能分数占比
        //  "a.ITEM_EXAMSTANDARD," + //成绩合格分数
        //   "a.ITEM_WRITTENPASSSCORE," + //理论考试合格分数
        // "a.ITEM_OPERATEPASSSCORE," + //体能考试合格分数
        // "a.ITEM_ISCOMPUTEREXAM," + //是否理论考试
        //   "a.ITEM_ISMAKEUPEXAM," + //是否有补考机会
        "a.ITEM_CANCELREASON," + //取消考试原因
        "CASE "+
        " 		a.ITEM_EXAMINE "+
        "  		WHEN '1' THEN"+
        "  '审核通过' "+
        "		  WHEN '2' THEN"+
        "		  '审核不通过' "+
        "		  WHEN '3' THEN"+
        "		  '审核中' ELSE '待提交' "+
        "  END ITEM_EXAMINE," + //审核
        "a.ITEM_AUDITREASON," + //不通过原因
        "count(b.ID) ITEM_COUNT,"+
        "a.ID,"+ 
        "a.ID as ITEM_ID"+
        " from tlk_system_exam_info a "+
        " left JOIN"+
        " tlk_system_exam_person b on a.ID = b.ITEM_EXAMID "+where+"  GROUP BY a.ID order by a.ITEM_COMPUTERSTARTDATE desc  ";
  }else{
    var sql = "select a.domainid,"+
        //"a.formid," + //表单ID
        "a.applicationid," + //应用ID
        "a.ITEM_SUBJECTID," + //考试科目
        "a.ITEM_AREACODE," + //办事区域
        "a.ITEM_EXAMTYPE," + //考试方式
        "CASE "+
        " 		a.ITEM_EXAMKIND "+
        "  		WHEN '1' THEN"+
        "  '补考' "+
        "		  WHEN '0' THEN"+
        "		  '日常考试' "+
        "  END ITEM_EXAMKIND," + //考试类型
        "a.ITEM_PAPERID," + //试卷名称
        "a.ITEM_EXAMSTATE," + //考试状态
        "a.ITEM_EXAMNAME," + //考试名称
        "date_format(a.ITEM_CREATEDTIME,'%Y-%m-%d %H:%i:%S') as ITEM_CREATEDTIME,"+
        "a.ITEM_person," + //考点联系人
        "a.ITEM_personname," + //考点联系人
        "a.ITEM_placeName," + //考场名称
        "a.ITEM_personNumber," + //考场人数
        //  "a.ITEM_ATTENTION," + //注意事项
        "CASE "+
        " 		a.ITEM_ISSTAMINA "+
        "  		WHEN '1' THEN"+
        "  '是' "+
        "		  WHEN '0' THEN"+
        "		  '否' "+
        "  END ITEM_ISSTAMINA," + //是否实操考试
        // "a.ITEM_STAMINAADDRESS," + //体能考试地址
        "date_format(a.ITEM_STAMINASTARTDATE,'%Y-%m-%d %H:%i:%S') as ITEM_STAMINASTARTDATE,"+ //体能测试开始时间
        // "a.ITEM_STAMINAENDDATE," + //体能测试结束时间
        //// "a.ITEM_EXAMRULES," + //成绩考核规则
        // "a.ITEM_THEORETICALADDRESS," + //理论考试地址
        "date_format(a.ITEM_COMPUTERSTARTDATE,'%Y-%m-%d %H:%i:%S') as ITEM_COMPUTERSTARTDATE," + //理论考试开始时间
        "a.ITEM_COMPUTERENDDATE," + //理论考试结束时间
        // "a.ITEM_WRITTENRATIO," + //理论分数占比
        //  "a.ITEM_OPERATERATIO," + //体能分数占比
        //  "a.ITEM_EXAMSTANDARD," + //成绩合格分数
        //   "a.ITEM_WRITTENPASSSCORE," + //理论考试合格分数
        // "a.ITEM_OPERATEPASSSCORE," + //体能考试合格分数
        // "a.ITEM_ISCOMPUTEREXAM," + //是否理论考试
        //   "a.ITEM_ISMAKEUPEXAM," + //是否有补考机会
        "a.ITEM_CANCELREASON," + //取消考试原因
        "CASE "+
        " 		a.ITEM_EXAMINE "+
        "  		WHEN '1' THEN"+
        "  '审核通过' "+
        "		  WHEN '2' THEN"+
        "		  '审核不通过' "+
        "		  WHEN '3' THEN"+
        "		  '审核中' ELSE '待提交' "+
        "  END ITEM_EXAMINE," + //审核
        "a.ITEM_AUDITREASON," + //不通过原因
        "count(b.ID) ITEM_COUNT,"+
        "a.ID,"+ 
        "a.ID as ITEM_ID,"+
        "a.item_position,"+  
        "a.item_exam_site_id,"+ //考试点id
        "a.item_exam_site_name "+ //考试点名称
        " from tlk_system_exam_info a "+
        " left JOIN"+
        " tlk_system_exam_person b on a.ID = b.ITEM_EXAMID "+where+"  GROUP BY a.ID order by a.ITEM_COMPUTERSTARTDATE desc  ";
  }


  println("sql==>" + sql);
  return sql;
})()]]></sqlFilterScript>
  <procedureFilterScript><![CDATA[]]></procedureFilterScript>
  <filterCondition><![CDATA[[]]]></filterCondition>
  <commonFilterCondition><![CDATA[[{"field":"name","isCommonFilter":"true","paramKey":1637651295198}]]]></commonFilterCondition>
  <authorityCondition>[]</authorityCondition>
  <openType>1</openType>
  <editMode>02</editMode>
  <width>0</width>
  <height>0</height>
</ListView>