<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ListView id="__XJ17HrS6vaKAoYkSDLi">
  <name>等级证抽查人员</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>__3a9gjNH5Nlen34qJFh3</searchFormId>
  <autoCompose>false</autoCompose>
  <relatedForm></relatedForm>
  <permissionType>public</permissionType>
  <filterScript><![CDATA[]]></filterScript>
  <sqlFilterScript><![CDATA[(function(){
  var domainId= getDomainid();
  var whereStr=" and 1=1 "
  var z_name = getItemValueAsString("z_name");  
  var z_idCard = getItemValueAsString("z_idCard"); 
  var z_phone = getItemValueAsString("z_phone"); 
  var applyStatus = getItemValueAsString("applyStatus"); 
  var status = getItemValueAsString("status"); 
  var company = getItemValueAsString("company");
  var reviewResult = getItemValueAsString("reviewResult");
  var queryCriteriaSql = "";   
  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_phone like concat('%','"+z_phone+"','%')"

  }   

  if(isNotNull(applyStatus)){                                        
    queryCriteriaSql = queryCriteriaSql + " and b.ITEM_APPLYSTATUS like concat('%','"+applyStatus+"','%')"

  }  
  if(isNotNull(status)){                                        
    queryCriteriaSql = queryCriteriaSql + " and b.ITEM_reviewStatus like concat('%','"+status+"','%')"

  }  
  if(isNotNull(company)){                                        
    queryCriteriaSql = queryCriteriaSql + " and e.ITEM_LEARNNAME like concat('%','"+company+"','%')"

  }  
  if(isNotNull(reviewResult)){            
    queryCriteriaSql += "and (b.ITEM_reviewResult like concat('%','"+reviewResult+"','%'))";           
  }
  

   var sql = "select '"+domainId+"' as domainid,"+
       "b.ITEM_LEARNNAME," + //报名公司
       "b.ITEM_LEARNID," + //报名公司
      //"a.ITEM_LEARNNAME," + //姓名
       "g.ITEM_PROGRESS," + //课程进度
      "a.ITEM_NAME," + //姓名
      "a.ITEM_IDCARDNO," + //身份证
      "a.ITEM_PHONE," + //手机号
      "case b.ITEM_APPLYSTATUS when '0' then '申报中'"+
      " when '1' then '申报通过'"+
      " when '2' then '申报不通过'"+ 
      " when '3' then '申报撤销'"+   
      " when '4' then '审批中'"+      
      " end as ITEM_APPLYSTATUS," + //申报状态
      "case b.ITEM_reviewStatus when '0' then '待审查' " + //审查状态
      " when '1' then '已审查'"+
      " when '2' then '待复审'"+ 
      " when '3' then '已复审'"+   
      " end as ITEM_reviewStatus," +
      "case b.ITEM_reviewResult when '0' then '--' " + //审查结果
      " when '1' then '审查通过'"+
      " when '2' then '审查不通过'"+ 
      " when '3' then '复审通过'"+   
      " when '4' then '复审不通过'"+    
      " end as ITEM_reviewResult," +
      "case when d.item_classId is null then '未安排' else '已安排' end item_examinationStatus,"+//用于判断班级是否开班，为空即未开班"
      "date_format(b.Created,'%Y-%m-%d %H:%i:%s') as item_created ,"+//报名时间
      "a.ITEM_INTERNSHIPCOMPANY as ITEM_COMPANYNAME," + //实习保安公司
      "COUNT(a.ITEM_IDCARDNO) as ITEM_TRAININGDAY,"+
      "SUM(a.ITEM_INTERNSHIPDURATION) AS ITEM_TOTALTIME,"+
      "b.ITEM_PAYDATE," + //缴费时间
      "b.ID,"+
      "b.ID as ITEM_ID"+
      " from  tlk_grade_internship a"+//实习表
      " inner join tlk_grade_registration b on a.ITEM_INTERNID=b.id and a.item_isdelete != '1'"+//等级证报名表
      " LEFT JOIN tlk_level_class_student d on d.item_applyid = b.id"+//班级学生表
       " left join tlk_lesson_auth_base_info g  on g.ITEM_AUTHRECORDID=b.id and g.item_isdelete != '1'  "+
       " where b.ITEM_APPLYSTATUS = '0' and b.ITEM_trainid = '"+domainId+"' and g.item_learnstyle = '2' and b.ITEM_reviewStatus in ('1','2','3')  "+queryCriteriaSql+
      //" where a.ITEM_INTERNSHIPRECORDSTATUS='1'  "+queryCriteriaSql+
      //"and a.ITEM_IDCARDNO not in (SELECT ITEM_IDCARDNO from tlk_level_class_student) "//不需要过滤学生不在这个班级里面
      " GROUP BY a.ITEM_IDCARDNO"+
      " HAVING COUNT(a.ITEM_IDCARDNO)>='"+setParameters.LV_DAY+"' and SUM(a.ITEM_INTERNSHIPDURATION)>='"+setParameters.LV_TIME+"' ORDER BY item_created desc";
  println("sql===================>" + sql);
  //sql=sql+queryCriteriaSql;
  println(sql);
  return sql;
})()]]></sqlFilterScript>
  <procedureFilterScript><![CDATA[]]></procedureFilterScript>
  <filterCondition><![CDATA[[]]]></filterCondition>
  <commonFilterCondition><![CDATA[[{"field":"z_name","isCommonFilter":"true","paramKey":1640940047643}]]]></commonFilterCondition>
  <authorityCondition>[]</authorityCondition>
  <openType>1</openType>
  <editMode>02</editMode>
  <width>0</width>
  <height>0</height>
</ListView>