<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ListView id="__QQZxiKk5769DixnWzF7">
  <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>true</readonly>
  <relatedMap><![CDATA[                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ]]></relatedMap>
  <displayType>relatedForm</displayType>
  <defaultShowMode>month</defaultShowMode>
  <collapsibleShowMode>normal</collapsibleShowMode>
  <showWaterMark>false</showWaterMark>
  <showActivityColumnType>0</showActivityColumnType>
  <searchFormId>__ooUMgzOCcHoR6D1jmE8</searchFormId>
  <autoCompose>false</autoCompose>
  <relatedForm>__TguP7KZtUDBVC1gHr6k</relatedForm>
  <permissionType>public</permissionType>
  <filterScript><![CDATA[]]></filterScript>
  <sqlFilterScript><![CDATA[#include "sysfunction";
(function () {
  var domainid = getDomainid();

  var name = "";
  var learnname =  "";
  var phone =  "";
  var idCardNo =  "";
  var bukao =  "";
  var STARTPAYDATE =  "";
  var ENDPAYDATE =  "";
  var paystate =  "";
  var ismarkup =  "";
  var registerPoliceAddress =  "";

  name = getItemValueAsString("name");
  learnname = getItemValueAsString("learnname");
  phone = getItemValueAsString("phone");
  idCardNo = getItemValueAsString("idCardNo");
  bukao = getItemValueAsString("bukao");
  println("bukao-->"+bukao)
  STARTPAYDATE = getItemValueAsDate("STARTPAYDATE");
  ENDPAYDATE = getItemValueAsDate("ENDPAYDATE");
  paystate = getItemValueAsString("paystate");
  ismarkup = getItemValueAsString("ismarkup");//是否补考
  registerPoliceAddress = getItemValueAsString("registerPoliceAddress"); 
  println("sdasdasda"+ismarkup)
  var whereStr = ""
  /*
  //用于第三方培训
  var domainid = getDomainid();
  var areacode = "";
  var sqlp = "SELECT tk.domainid,dl.ITEM_REGIONId FROM organizationauth.tlk_DepartmentLevel dl JOIN baibaodunflow.tlk_SetSupervise tk ON dl.id = tk.item_registerPoliceAddressID WHERE tk.domainid IN ('"+domainid+"')"
  println("sqlp---------------"+sqlp)
  var datasp = queryByDSName("tms",sqlp);
  if(datasp!=null){
    for(var iterator = datasp.iterator();iterator.hasNext();){
      var map1 = iterator.next();//取值
      areacode= map1.get("ITEM_REGIONId");
    }
  }
  if(isNotNull(areacode)){
    areacode = areacode.substr(0,2);
  }

  var checksql="select 1 from tlk_flow_config f join tlk_regional_config c on c.item_config_id=f.id where item_regional_code like '%"+areacode+"%' and item_compulsory_train='1' "
  var datas2 = queryByDSName("organizationAuth",checksql);
  if(datas2.size()>0){
   var whereExam ="a.item_IsAuthorizeCourses='0' and (a.id in (select item_apply_id from tlk_orther_over_record where domainid='"+domainid+"') or a.ITEM_PAYDATE>='2024-09-01') "
  }else{
  var whereExam ="a.item_IsAuthorizeCourses='0'"
  }
*/

  if (isNotNull(name)) {
    whereStr += "and a.ITEM_NAME like concat('%','" + name + "','%') ";
  }

  if (isNotNull(learnname)) {
    whereStr += "and a.ITEM_LEARNNAME like concat('%','" + learnname + "','%') ";
  }

  if(isNotNull(ismarkup)){     
    println("ssssss"+ismarkup)
    whereStr += "and a.ITEM_EXAMNUM like '%"+ismarkup+"%' ";  
  } 
  if(isNotNull(paystate)){     
    whereStr += "and a.ITEM_paystate like '%"+paystate+"%' ";  
  } 
  if(isNotNull(registerPoliceAddress)){            
    whereStr += "and a.ITEM_registerPoliceAddress like concat('%','"+registerPoliceAddress+"','%') ";            
  }
  if (isNotNull(phone)) {
    whereStr += "and a.ITEM_PHONE like concat('%','" + phone + "','%') ";
  }
  if (isNotNull(STARTPAYDATE)) {
    whereStr += "and a.ITEM_PAYDATE >= '"+format(STARTPAYDATE,"yyyy-MM-dd HH:mm:ss")+"' ";    
  }
  if (isNotNull(ENDPAYDATE)) {
    whereStr += "and a.ITEM_PAYDATE <= DATE_FORMAT(DATE_ADD('"+format(ENDPAYDATE,"yyyy-MM-dd HH:mm:ss")+"',INTERVAL 1 DAY),'%Y-%m-%d')";   
  }
  if (isNotNull(idCardNo)) {
    whereStr += "and a.ITEM_IDCARDNO like concat('%','" + idCardNo + "','%') ";
  }


  var rdoc=getRelateDocument()
  var examId = rdoc.getItemValueAsString("examId");
  println("examId==========="+examId); 
  //获取考试点id
  var examSiteId = rdoc.getItemValueAsString("examSiteId");
  println("examSiteId==========="+examSiteId); 
  if(isNotNull(examSiteId)){
    whereStr += "and a.item_exam_site_id = '" + examSiteId + "' ";
    println("examSiteId==========="+examSiteId);
  }
  var domainid = getDomainid();

  if(isNotNull(bukao)){
    if("1".equals(bukao)){
      whereStr += "and ifnull(a.ITEM_WRITTENSCORE,'') != '' and item_examnum = '0.0000000000' ";
    }else if("0".equals(bukao)){

      whereStr += "and (ifnull(a.ITEM_WRITTENSCORE,'') = '' or item_examnum != '0.0000000000') ";
    }
  }else{
    var doc=findDocument(examId);
    var examKind= doc.getItemValueAsString("examKind");
    var practical_fail_up = doc.getItemValueAsString("practical_fail_up");
    if("1".equals(examKind)&&"0".equals(practical_fail_up)){
      whereStr += "and ifnull(a.ITEM_WRITTENSCORE,'') != '' and item_examnum = '0.0000000000' ";
    }else if("0".equals(examKind)&&"0".equals(practical_fail_up)){
      whereStr += "and (ifnull(a.ITEM_WRITTENSCORE,'') = '' or item_examnum != '0.0000000000') ";
    }
  }

  var sql = "select '" + domainid + "' as domainid," +
      "a.ITEM_LEARNNAME," + //所属企业
      "a.ITEM_LEARNID," + //所属企业id
      "a.ITEM_NAME," + //姓名
      "a.ITEM_PHONE," + //手机
      "a.ITEM_IDCARDNO," + //身份证
      "a.ITEM_SECURITYID," + //保安员id
      "a.ITEM_MATERIALAPPROVESTATUS," + //材料审核状态
      "CASE a.ITEM_WRITTENSCORE WHEN '' THEN '否' WHEN NULL THEN '否' ELSE ( CASE a.item_examnum WHEN '0.0000000000' THEN '是' ELSE '否' END ) END AS item_bukao," + //补考
      "a.ITEM_APPLYSTATUS," + //审核状态
      "a.ITEM_REGISTERPOLICEADDRESS," + //办事机构地址
      "a.ITEM_CHECKSTATUS," + //抽查状态
      "a.ITEM_BACKGROUNDSTATUS," + //背景筛查状态
      "case a.ITEM_PAYSTATE when '0' then '未缴费' when '1' then '已缴费' when '2' then '已退款' when '3' then '退款中' else '未缴费' end as item_paystate," + //缴费状态
      "a.ITEM_PAYDATE," + //缴费时间
      "a.ITEM_TRAINID," + //培训公司id
      "a.ITEM_TRAINNAME," + //培训公司
      "case a.ITEM_EXAMNUM when '0.0000000000' then '否' when '1.0000000000' then '是' end as ITEM_EXAMNUM ,"+//补考次数
      "a.ITEM_ORDERID," + //订单id
      "b.ITEM_AUTHLESSONNAME," + //授权课程名
      "b.ITEM_PROGRESS," + //学习进度
      "a.ID," +
      "a.ID as ITEM_ID" +
      " from tlk_qualify_person_apply a  " +
      " left JOIN" +
      " tlk_lesson_auth_base_info b on a.id = b.item_authRecordId and b.item_learnStyle='1' and b.item_isDelete!='1' " +
      " where  (a.ITEM_REVIEWRESULT not in ('2','4') and a.item_reviewStatus != '2') and a.ITEM_organizationId='" + domainid + "' and a.ITEM_MATERIALAPPROVESTATUS = '1' and a.ITEM_APPLYSTATUS = '0'" +
      " and a.ID not IN (select ifnull(a.ITEM_APPLYID,'') from tlk_system_exam_person  a inner join tlk_system_exam_info b on b.id=a.ITEM_examId WHERE a.domainid='"+domainid+"'  and ((a.ITEM_TESTSTATE IN ( '-', '1' ) and b.item_examState='0')  or (a.ITEM_TESTSTATE='0' and b.item_isMakeUpExam!='1') or a.item_examId='" + examId + "')) " + whereStr + " ";


  println("sql==>" + sql);

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