<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ListView id="__Ju1TnBPxdlhbAVDZp1o">
  <name>level_student_select</name>
  <parentId>__AT2erAITyWrFQyQEh3m</parentId>
  <applicationid>__LgZds5MVHW3o08T7UcX</applicationid>
  <description>班级人员选择</description>
  <pagination>true</pagination>
  <pageLines>05</pageLines>
  <showTotalRow>false</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>__1cT9KQMjPIPe5uSsO7f</searchFormId>
  <autoCompose>false</autoCompose>
  <relatedForm>__TTLL6anXeEmwkn6UvAO</relatedForm>
  <permissionType>public</permissionType>
  <filterScript><![CDATA[]]></filterScript>
  <sqlFilterScript><![CDATA[#include "setParameters";
(function(){
  var domainId = getDomainid();
  var name = getItemValueAsString("name");
  var idCardNo = getItemValueAsString("idCardNo");
  var keyWord = getItemValueAsString("keyWord");
  var learnname = getItemValueAsString("learnname");
  var payStartDate = getItemValueAsString("payStartDate");
  var payEndDate = getItemValueAsString("payEndDate");
  var phone = getItemValueAsString("phone");
  var STARTPAYDATE = getItemValueAsDate("STARTPAYDATE");
  var ENDPAYDATE = getItemValueAsDate("ENDPAYDATE");
  var whereStr="";
  if (isNotNull(name)) {
    whereStr += "and b.ITEM_NAME like concat('%','" + name + "','%') ";
  }
  if (isNotNull(idCardNo)) {
    whereStr += "and b.ITEM_IDCARDNO like concat('%','" + idCardNo + "','%') ";
  }
  if(isNotNull(keyWord)){            
    whereStr += "and (b.ITEM_NAME like concat('%','"+keyWord+"','%')"+
      " or b.ITEM_PHONE like concat('%','"+keyWord+"','%')"+
      " or b.ITEM_IDCARDNO like concat('%','"+keyWord+"','%')) ";           
  }
  if(isNotNull(learnname)){            
    whereStr += "and b.ITEM_learnname like concat('%','"+learnname+"','%') ";            
  } 
  if(isNotNull(phone)){            
    whereStr += "and b.ITEM_phone like concat('%','"+phone+"','%') ";            
  } 
  if(isNotNull(payStartDate)){            
    whereStr += "and b.ITEM_PAYDATE >= '"+format(payStartDate,"yyyy-MM-dd HH:mm:ss")+"' ";            
  }            
  if(isNotNull(payEndDate)){            
    whereStr += "and b.ITEM_PAYDATE <= '"+format(payEndDate,"yyyy-MM-dd HH:mm:ss")+"' ";            
  } 
   if (isNotNull(STARTPAYDATE)) {
    whereStr += "and b.ITEM_PAYDATE >= '"+format(STARTPAYDATE,"yyyy-MM-dd HH:mm:ss")+"' ";    
  }
   if (isNotNull(ENDPAYDATE)) {
     whereStr += "and b.ITEM_PAYDATE <= DATE_FORMAT(DATE_ADD('"+format(ENDPAYDATE,"yyyy-MM-dd HH:mm:ss")+"',INTERVAL 1 DAY),'%Y-%m-%d')";   
  }

  /*var sql = "select '"+domainId+"' as domainid,"+
      "a.ITEM_NAME," + //姓名
      "a.ITEM_IDCARDNO," + //身份证
      "a.ITEM_PHONE," + //手机号
      "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 (select * from tlk_apply_person_refund group by item_applyid order by created desc) c on c.item_applyid=b.id "+
      " where  b.item_organizationId='"+domainId+"' and (b.ITEM_REVIEWRESULT in ('0','1','3') and b.item_reviewStatus != '2') and a.ITEM_INTERNSHIPRECORDSTATUS='1' and "+
      "a.ITEM_IDCARDNO not in (SELECT ITEM_IDCARDNO from tlk_level_class_student  where item_graduationTestResults!='2' and  ITEM_IDCARDNO is not null ) and b.id not in (SELECT item_applyid FROM tms.tlk_level_class_student WHERE item_applyid IS NOT NULL) "+whereStr+
      " GROUP BY a.ITEM_IDCARDNO"+
      " HAVING COUNT(a.ITEM_IDCARDNO)>='"+setParameters.LV_DAY+"' and SUM(a.ITEM_INTERNSHIPDURATION)>='"+setParameters.LV_TIME+"'";*/
  var sql = "select '"+domainId+"' as domainid,"+
      "b.ITEM_NAME," + //姓名
      "b.ITEM_IDCARDNO," + //身份证
      "b.ITEM_PHONE," + //手机号
      "b.ITEM_LEARNNAME as ITEM_COMPANYNAME," + //实习保安公司
      "COUNT(a.ITEM_IDCARDNO) as ITEM_TRAININGDAY,"+
      "SUM(a.ITEM_INTERNSHIPDURATION) AS ITEM_TOTALTIME,"+
      "b.ITEM_PAYDATE," + //缴费时间
      "b.item_IsAuthorizeCourses," + 
      "b.ITEM_materialApproveStatus," + 
      "b.ID,"+
      "b.ID as ITEM_ID"+
      " from  tlk_grade_registration b"+
      " left join tlk_grade_internship a on a.item_internid=b.id and a.item_isdelete != '1'"+
      //" left join (select * from tlk_apply_person_refund group by item_applyid order by created desc) c on c.item_applyid=b.id "+
      " where  b.item_organizationId='"+domainId+"' and b.ITEM_materialApproveStatus = '1' and ((b.ITEM_REVIEWRESULT in ('0','1','3') and ifnull(b.item_reviewStatus,'') != '2') OR b.item_IsAuthorizeCourses = '0') and (a.ITEM_INTERNSHIPRECORDSTATUS='1' OR b.item_IsAuthorizeCourses = '0') and "+
      "b.ITEM_IDCARDNO not in (SELECT ITEM_IDCARDNO from tlk_level_class_student  where ifnull(item_graduationTestResults,'')!='2' and  ITEM_IDCARDNO is not null ) and b.id not in (SELECT item_applyid FROM tms.tlk_level_class_student WHERE item_applyid IS NOT NULL) "+whereStr+
      " GROUP BY b.ITEM_IDCARDNO"+
      " HAVING COUNT(b.ITEM_IDCARDNO)>='"+setParameters.LV_DAY+"' and (SUM(a.ITEM_INTERNSHIPDURATION)>='"+setParameters.LV_TIME+"' OR b.item_IsAuthorizeCourses = '0')";
  sql+= " order by b.ITEM_PAYDATE desc";
  println("sql===================>" + sql);
  //println("sql===================>" + setParameters.LV_DAY);
  return sql;
})()]]></sqlFilterScript>
  <procedureFilterScript><![CDATA[]]></procedureFilterScript>
  <filterCondition><![CDATA[[]]]></filterCondition>
  <commonFilterCondition><![CDATA[[{"field":"name","isCommonFilter":"true"}]]]></commonFilterCondition>
  <authorityCondition>[]</authorityCondition>
  <openType>1</openType>
  <editMode>02</editMode>
  <width>0</width>
  <height>0</height>
</ListView>