<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ListView id="__72xtclEdKWE2j9GbFeb">
  <name>qualify_person_apply</name>
  <parentId>__33O9dRsTvtyXuJr33bv</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>__kJ5yj8mpSm9iwAwMHvY</searchFormId>
  <autoCompose>true</autoCompose>
  <relatedForm></relatedForm>
  <permissionType>public</permissionType>
  <filterScript><![CDATA[]]></filterScript>
  <sqlFilterScript><![CDATA[#include "sysfunction";
(function(){
 var domainId = getDomainid();

var keyWords = getItemValueAsString("keyWords"); 
var applyStatus = getItemValueAsString("applyStatus"); 
var backgroundStatus = getItemValueAsString("backgroundStatus"); 
var trainName = getItemValueAsString("trainName"); 
var payState = getItemValueAsString("payState"); 
var materialApproveStatus = getItemValueAsString("materialApproveStatus"); 
var createStartDate = getItemValueAsDate("createStartDate"); 
var createEndDate = getItemValueAsDate("createEndDate"); 
var progress = getItemValueAsString("progress"); 
var testState = getItemValueAsString("testState"); 
var examStartDate = getItemValueAsDate("examStartDate"); 
var examEndDate = getItemValueAsDate("examEndDate"); 

//var whereStr = " where a.DOMAINID = '"+getDomainid()+"' ";       
var whereStr = " where a.ITEM_TRAINID='"+domainId+"' and a.ITEM_PAYSTATE = '1'";         
if(isNotNull(keyWords)){            
  whereStr += "and (a.ITEM_NAME like concat('%','"+keyWords+"','%')"+
			" or a.ITEM_PHONE like concat('%','"+keyWords+"','%')"+
			" or a.ITEM_IDCARDNO like concat('%','"+keyWords+"','%')) ";           
}   
if(isNotNull(applyStatus)){            
  whereStr += "and a.ITEM_APPLYSTATUS = '"+applyStatus+"' ";            
} 
if(isNotNull(backgroundStatus)){            
  whereStr += "and a.ITEM_BACKGROUNDSTATUS = '"+backgroundStatus+"' ";            
}   
if(isNotNull(trainName)){            
  whereStr += "and a.ITEM_TRAINNAME like concat('%','"+trainName+"','%') ";            
}   
if(isNotNull(payState)){            
  whereStr += "and a.ITEM_PAYSTATE = '"+payState+"' ";            
}   
if(isNotNull(materialApproveStatus)){            
  whereStr += "and a.ITEM_MATERIALAPPROVESTATUS = '"+materialApproveStatus+"' ";            
}   
  
if(isNotNull(createStartDate)){            
  whereStr += "and a.CREATED >= '"+format(createStartDate,"yyyy-MM-dd HH:mm:ss")+"' ";            
}            
if(isNotNull(createEndDate)){            
  whereStr += "and a.CREATED <= '"+format(createEndDate,"yyyy-MM-dd HH:mm:ss")+"' ";            
}     

//if(isNotNull(progress)){     
 // if("1".equals(progress)){
//    whereStr += "and c.ITEM_PROGRESS = 100 ";   
 // }else{
 //   whereStr += "and (c.ITEM_PROGRESS != 100 or c.ITEM_PROGRESS is null)";   
 // }
//}   
if(isNotNull(testState)){            
  whereStr += "and d.ITEM_TESTSTATE = '"+testState+"' ";            
}   

if(isNotNull(examStartDate)){            
  whereStr += "and e.ITEM_COMPUTERSTARTDATE >= '"+format(examStartDate,"yyyy-MM-dd HH:mm:ss")+"' ";            
}            
if(isNotNull(examEndDate)){            
  whereStr += "and e.ITEM_COMPUTERSTARTDATE <= '"+format(examEndDate,"yyyy-MM-dd HH:mm:ss")+"' ";            
} 
var sql = "select '"+domainId+"' as domainid,"+
"a.ITEM_LEARNNAME," + //所属企业
"a.ITEM_LEARNID," + //所属企业id
"a.ITEM_NAME," + //
    "a.formid,"+
"a.applicationid,"+
"a.ITEM_PHONE," + //
"a.ITEM_SECURITYID," + //保安员id
"a.ITEM_IDCARDNO," + //
"a.CREATED,"+  
"case a.ITEM_MATERIALAPPROVESTATUS when '0' then '未审核'"+
" when '1' then '审核通过'"+
" when '2' then '审核不通过'"+ 
" when '3' then '审核中'"+   
" when '4' then '-'"+      
" end as ITEM_MATERIALAPPROVESTATUS," + //材料审核状态
"a.ITEM_MATERIALAPPROVEFAILREASON," + //材料审核不通过原因
"a.ITEM_MATERIALAPPROVEDATE," + //材料审核时间
"a.ITEM_MATERIALAPPROVEPERNAME," + //材料审核人员名称
"a.ITEM_MATERIALAPPROVEPERIDCARDNO," + //材料审核人员身份证号
"case a.ITEM_APPLYSTATUS when '0' then '申报中'"+
" when '1' then '申报通过'"+
" when '2' then '申报不通过'"+ 
" when '3' then '申报撤销'"+   
" when '4' then '审批中'"+      
" end as ITEM_APPLYSTATUS," + //审核状态
"a.ITEM_CHECKSTATUS," + //抽查状态
"case a.ITEM_BACKGROUNDSTATUS when '0' then '审核中'"+
" when '1' then '正常'"+
" when '2' then '关注'"+ 
" when '3' then '申诉通过'"+   
" when '4' then '申诉不通过'"+      
" when '5' then '申诉中'"+        
" end as ITEM_BACKGROUNDSTATUS," + //背景筛查状态
"case a.ITEM_PAYSTATE when '0' then '未缴费'"+
" when '1' then '已缴费'"+
    " when '2' then '已退款'"+ 
" end as ITEM_PAYSTATE," + //缴费状态"
"a.ITEM_PAYDATE," + //缴费时间
"a.ITEM_TRAINID," + //培训公司id
"FORMAT(ifnull( c.ITEM_PROGRESS, '0' ),2) ITEM_PROGRESS,"+//学习进度
"a.ITEM_TRAINNAME," + //培训公司
"e.ITEM_EXAMNAME,"+//考试名称  
"case when e.ID is null then '未安排' else '已安排' end ITEM_examArrange,"+
"DATE_FORMAT(e.ITEM_COMPUTERSTARTDATE,'%Y-%m-%d') ITEM_examDate,"+//考试时间
"case d.ITEM_TESTSTATE when '0' then '不合格' when '1' then '合格' when '-' then '未考试' else '' end ITEM_TESTSTATE,"+//考试结果
"d.ITEM_WRITTENSCORE,"+//笔试成绩
"d.ITEM_OPERATESCORE,"+ //体能成绩
"a.ID,"+
"a.ID as ITEM_ID"+
" from tlk_qualify_person_apply a "+
" LEFT JOIN tlk_system_exam_person d on d.ITEM_APPLYID = a.ID and d.item_isDelete!='1' "+
    " LEFT JOIN tlk_lesson_auth_base_info c on c.ITEM_authRecordId = a.ID and c.item_isDelete!='1'"+
    " left join tlk_certification f on f.ITEM_IDCARDNO=a.ITEM_IDCARDNO and f.ITEM_CERTIFICATETYPE='1'"+
" LEFT JOIN tlk_system_exam_info e on e.ID = d.ITEM_EXAMID and e.ITEM_EXAMSTATE = '0'"+　whereStr;
   
//ITEM_TRAINID = '"+domainId+"'";
  
  sql+=" group by a.id ORDER BY a.ITEM_PAYDATE DESC"
println("sql==>" + sql);
return sql;
})()]]></sqlFilterScript>
  <procedureFilterScript><![CDATA[]]></procedureFilterScript>
  <filterCondition><![CDATA[[]]]></filterCondition>
  <commonFilterCondition><![CDATA[[{"field":"keyWords","isCommonFilter":"true"}]]]></commonFilterCondition>
  <authorityCondition>[]</authorityCondition>
  <openType>1</openType>
  <editMode>02</editMode>
  <width>0</width>
  <height>0</height>
</ListView>