<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ListView id="__n2gccPTHRcyrnzgeyUr">
  <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>__Y4GQFG58VzCYtxMLc5o</searchFormId>
  <autoCompose>false</autoCompose>
  <relatedForm>__TguP7KZtUDBVC1gHr6k</relatedForm>
  <permissionType>public</permissionType>
  <filterScript><![CDATA[]]></filterScript>
  <sqlFilterScript><![CDATA[//在表单控件值脚本中获取当前文档对象
(function(){
  var type = getParameter("type");
  println("type-------------------"+type);
  var z_name = getItemValueAsString("z_name");  
  var z_idCard = getItemValueAsString("z_idCard"); 
  var z_phone = getItemValueAsString("z_phone"); 
  var company = getItemValueAsString("company"); 
  var testname = getItemValueAsString("testname"); 
  var idCardNo = getItemValueAsString("idCardNo"); 
  var EXAMID = getItemValueAsString("EXAMID"); 

  var exam_site_name = getItemValueAsString("exam_site_name"); 
  var exam_room_name = getItemValueAsString("exam_room_name"); 
  var region = getItemValueAsString("region"); 
  var COMPUTERSTARTDATEmin= getItemValueAsString("COMPUTERSTARTDATEmin");//理论考试开始时间
  var COMPUTERSTARTDATEmax= getItemValueAsString("COMPUTERSTARTDATEmax");//理论考试开始时间
  var queryCriteriaSql = ""; 
  if(isNotNull(COMPUTERSTARTDATEmin)){                                        
    queryCriteriaSql = queryCriteriaSql + " and b.item_COMPUTERSTARTDATE >= '"+COMPUTERSTARTDATEmin+"' ";
  }
  if(isNotNull(COMPUTERSTARTDATEmax)){                                        
    queryCriteriaSql = queryCriteriaSql + " and b.item_COMPUTERSTARTDATE <= DATE_FORMAT(DATE_ADD('"+COMPUTERSTARTDATEmax+"',INTERVAL 1 DAY),'%Y-%m-%d')";
  }
  if(isNotNull(exam_site_name)){                                        
    queryCriteriaSql = queryCriteriaSql + " and b.item_exam_site_name like concat('%','"+exam_site_name+"','%')"
  }
  if(isNotNull(exam_room_name)){                                        
    queryCriteriaSql = queryCriteriaSql + " and b.item_placeName like concat('%','"+exam_room_name+"','%')"

  }
  if(isNotNull(region)){                                        
    queryCriteriaSql = queryCriteriaSql + " and b.item_position_id = '"+region+"'";

  }

  if(isNotNull(z_name)){                                        
    queryCriteriaSql = queryCriteriaSql + " and a.ITEM_NAME like concat('%','"+z_name+"','%')"

  }
  if(isNotNull(z_idCard)){                                        
    queryCriteriaSql = queryCriteriaSql + " and a.ITEM_IDCARDNO like concat('%','"+z_idCard+"','%')"
  }
  if(isNotNull(z_phone)){                                        
    queryCriteriaSql = queryCriteriaSql + " and a.ITEM_MOBILE like concat('%','"+z_phone+"','%')"
  }  
  if(isNotNull(company)){                                        
    queryCriteriaSql = queryCriteriaSql + " and a.ITEM_COMPANY like concat('%','"+company+"','%')"
  }  
  if(isNotNull(testname)){                                        
    queryCriteriaSql = queryCriteriaSql + " and b.ITEM_EXAMNAME like concat('%','"+testname+"','%')"
  }  

  var domainid = getDomainid();
  println("domainid---------------------"+domainid);
  var sql = "select a.domainid,"+
      "case b.ITEM_SUBJECTID when '1' then '保安员资格证'" + 
      " when '2' then '保安员培训结业考试'"+
      " end as ITEM_SUBJECTID," + //是否理论考试
      //"b.ITEM_EXAMTYPE," + //考试类型
      //"b.ITEM_PAPERID," + //试卷名称
      //"b.ITEM_EXAMSTATE," + //考试状态
       "CASE "+
        " 		b.ITEM_EXAMKIND "+
        "  		WHEN '1' THEN"+
        "  '补考' "+
        "		  WHEN '0' THEN"+
        "		  '日常考试' "+
        "  END ITEM_EXAMKIND," + //考试类型
      "b.ITEM_EXAMNAME," + //考试名称
      "b.ITEM_countQuestions," + //总题数
      "b.ITEM_person," + //考点联系人
      "b.ITEM_personName," + //考点联系人
      //"c.name as ITEM_person," + //考点联系人
      //"b.ITEM_ATTENTION," + //注意事项
      "case b.ITEM_ISSTAMINA when '0' then '否'" + 
      " when '1' then '是'"+
      " end as ITEM_ISSTAMINA," +//是否体能考试
      "b.ITEM_STAMINAADDRESS," + //体能考试地址
      "date_format(b.ITEM_STAMINASTARTDATE,'%Y-%m-%d %H:%i:%s' ) as ITEM_STAMINASTARTDATE," + //体能测试开始时间
      "date_format(b.ITEM_STAMINAENDDATE,'%Y-%m-%d %H:%i:%s' ) as ITEM_STAMINAENDDATE," + //体能测试结束时间
      "case a.ITEM_operateStatus when '0' then '不合格'" + 
      " when '1' then '合格'"+
      " when '-' then '未考试'"+
      " end as ITEM_operateStatus," + //是否合格
      "a.ITEM_operateReason," + //不合格理由
      //"b.ITEM_EXAMRULES," + //成绩考核规则
      "case b.ITEM_ISCOMPUTEREXAM when '0' then '否'" + 
      " when '1' then '是'"+
      " end as ITEM_ISCOMPUTEREXAM," + //是否理论考试
      "b.ITEM_THEORETICALADDRESS," + //理论考试地址
      "date_format(b.ITEM_COMPUTERSTARTDATE,'%Y-%m-%d %H:%i:%s' ) ITEM_COMPUTERSTARTDATE ," + //理论考试开始时间//
      "date_format(b.ITEM_COMPUTERENDDATE,'%Y-%m-%d %H:%i:%s' )  ITEM_COMPUTERENDDATE," + //理论考试结束时间
      //"b.ITEM_WRITTENRATIO," + //理论分数占比
      //"b.ITEM_OPERATERATIO," + //体能分数占比
      "b.ITEM_EXAMSTANDARD," + //成绩合格分数
      //"b.ITEM_WRITTENPASSSCORE," + //理论考试合格分数
      //"b.ITEM_OPERATEPASSSCORE," + //
      //"a.ITEM_SECURITYID," + //保安员id
      "a.ITEM_IDCARDNO," + //身份证
      "a.ITEM_NAME," + //姓名
      "a.ITEM_COMPANY," + //所属公司
      "a.ITEM_MOBILE," + //手机号
      //"a.ITEM_WRITTENRECORDMORE," +
      "a.ITEM_SUMSCORE," + //总成绩
      "case a.ITEM_TESTSTATE when '0' then '不合格'" + 
      " when '1' then '合格'"+
      " when '-' then '未考试'"+
      " end as ITEM_TESTSTATE," + //考试成绩状态
      "a.ITEM_APPLYID," + //报名id
      "b.ITEM_PLACENAME,"+//考场
      "a.created,"+
      "b.item_exam_site_name,"+//考试点
      "a.ITEM_sign_operator," + //签到老师
      "date_format(a.ITEM_sign_date,'%Y-%m-%d %H:%i:%s')  as ITEM_sign_date,"+
      "b.ITEM_POSITION,"+//所属地区
      "a.ITEM_WRITTENSTATE," + //笔试状态
      "a.ITEM_WRITTENRECORD," + //笔试做题记录
      "case a.ITEM_SIGNIN when '0' then '否'" + 
      " when '1' then '是'"+
      " end as ITEM_SIGNIN" + //是否签到
      /*/"a.ITEM_EXAMID," + //考试id
      "b.ID,"+
      "b.formid,"+
      "b.applicationid,"+
      "b.ID as ITEM_ID,"+
      "a.ID as ITEM_ID1"+*/
      " from tlk_system_exam_person a"+
      " INNER JOIN"+
      " tlk_system_exam_info b on a.ITEM_EXAMID = b.ID"+
      /*" LEFT JOIN"+
      " obpm2.tenant_employees d on d.ID like CONCAT('%',b.ITEM_person,'%')"+
      " LEFT JOIN"+
      " obpm2.tenant_users c on c.ID like CONCAT('%',d.tenant_user_id,'%')"+*/
      " where b.ITEM_EXAMSTATE = '0'  ";


  if(isNotNull(idCardNo)){
    sql += " and a.ITEM_IDCARDNO = '"+idCardNo+"' ";
  }
  if(!isNotNull(type)){
    type = 0;
  }
  if(isNotNull(type)){
    sql += " and b.item_type = '"+type+"' ";
  }
  if(isNotNull(EXAMID)){
    sql += " and a.ITEM_EXAMID = '"+EXAMID+"' ";
  }

  sql=sql+queryCriteriaSql
  sql+=" order by a.created desc"
  println("sql==>" + sql);
  
  return sql;



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