<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ListView id="__SW5t3G0jTCWxjdIC8FM">
  <name>review_management</name>
  <parentId>__PIR6BjNg7pkoWx28hf2</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>__Dg6RRHJ5WFXU8x2Byki</searchFormId>
  <autoCompose>false</autoCompose>
  <relatedForm></relatedForm>
  <permissionType>public</permissionType>
  <filterScript><![CDATA[]]></filterScript>
  <sqlFilterScript><![CDATA[(function(){
 var domainId = getDomainid();
var keyWords = getItemValueAsString("keyWords");//关键字
var materialApproveStatus = getItemValueAsString("materialApproveStatus");//材料审核状态
var learnName = getItemValueAsString("learnName"); //保安公司名称
var progress = getItemValueAsString("progress"); //理论学习进度
var testState = getItemValueAsString("testState"); //考试成绩状态
var applyStatus = getItemValueAsString("applyStatus"); //申报状态
var payStartDate = getItemValueAsDate("payStartDate"); //缴费开始时间
var payEndDate = getItemValueAsDate("payEndDate"); //缴费结束时间
var examStartDate = getItemValueAsDate("examStartDate"); //考试开始时间
var examEndDate = getItemValueAsDate("examEndDate");//考试结束时间
var materialaApprovePerName = getItemValueAsString("materialaApprovePerName"); //核对人
var materialaApproveStartDate = getItemValueAsDate("materialaApproveStartDate"); //核对开始时间
var materialaApproveEndDate = getItemValueAsDate("materialaApproveEndDate"); //核对结束时间


//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(materialApproveStatus)){            
  whereStr += "and a.ITEM_MATERIALAPPROVESTATUS = '"+materialApproveStatus+"' ";            
} 
if(isNotNull(learnName)){            
  whereStr += "and a.ITEM_LEARNNAME like concat('%','"+learnName+"','%') ";           
} 
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)){
  if("-".equals(testState)){
     whereStr += "and (d.ITEM_TESTSTATE = '"+testState+"' or d.ITEM_TESTSTATE is null)"; 
  }else{
     whereStr += "and d.ITEM_TESTSTATE = '"+testState+"' ";  
  }         
}   
if(isNotNull(applyStatus)){            
  whereStr += "and a.ITEM_APPLYSTATUS = '"+applyStatus+"' ";            
} 
if(isNotNull(payStartDate)){            
  whereStr += "and a.ITEM_PAYDATE >= '"+format(payStartDate,"yyyy-MM-dd HH:mm:ss")+"' ";            
}            
if(isNotNull(payEndDate)){            
  whereStr += "and a.ITEM_PAYDATE <= '"+format(payEndDate,"yyyy-MM-dd HH:mm:ss")+"' ";            
} 
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")+"' ";            
} 
if(isNotNull(materialaApprovePerName)){            
  whereStr += "and a.ITEM_MATERIALAPPROVEPERNAME like concat('%','"+materialaApprovePerName+"','%') ";            
}  
if(isNotNull(materialaApproveStartDate)){            
  whereStr += "and a.ITEM_MATERIALAPPROVEDATE >= '"+format(materialaApproveStartDate,"yyyy-MM-dd HH:mm:ss")+"' ";            
}            
if(isNotNull(materialaApproveEndDate)){            
  whereStr += "and a.ITEM_MATERIALAPPROVEDATE <= '"+format(materialaApproveEndDate,"yyyy-MM-dd HH:mm:ss")+"' ";            
} 


var sql = "select '"+domainId+"' as domainid,"+
"a.ITEM_LEARNNAME," + //所属企业
"a.ITEM_LEARNID," + //所属企业id
"a.ITEM_NAME," + //
"a.ITEM_PHONE," + //
"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 '已缴费'"+      
" end as ITEM_PAYSTATE," + //缴费状态"
"a.ITEM_PAYDATE," + //缴费时间
"a.ITEM_TRAINID," + //培训公司id
"a.ITEM_TRAINNAME," + //培训公司
"FORMAT(ifnull( g.ITEM_PROGRESS, '0' ),2) ITEM_PROGRESS,"+//学习进度
"e.ITEM_className,"+//考试名称  
"case when e.ID is null then '未安排' else '已安排' end ITEM_examArrange,"+
"DATE_FORMAT(e.ITEM_graduationTheoryTextStartTime,'%Y-%m-%d') ITEM_examDate,"+//考试时间
"case d.ITEM_GRADUATIONTESTRESULTS when '0' then '不合格' when '1' then '合格' when '-' then '未考试' else '' end ITEM_TESTSTATE,"+//考试结果
"d.ITEM_graduationTheoryScore,"+//笔试成绩
"d.ITEM_graduationOperationScore,"+ //体能成绩
"a.ID,"+
"d.ITEM_IDENTIFICATIONTESTRESULTS,"+//鉴定考试结果
"d.ITEM_IDENTIFICATIONTHEORYSCORE,"+//鉴定理论成绩
"d.ITEM_IDENTIFICATIONOPERATIONSCORE,"+//鉴定实操分数
"a.ID as ITEM_ID"+
" from tlk_grade_registration a "+
     "left JOIN "+
     "tlk_lesson_auth_base_info g on g.ITEM_authRecordId=a.id and g.ITEM_LEARNSTYLE='2' "+
" LEFT JOIN tlk_level_class_student d on d.ITEM_IDCARDNO = a.ITEM_IDCARDNO "+
" LEFT JOIN tlk_grade_card_class e on e.ID = d.ITEM_classId " +whereStr+" order by a.ITEM_PAYDATE desc";

//ITEM_TRAINID = '"+domainId+"'";
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>