<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ListView id="__Xq3r3eKoddb3a49Mrrg">
  <name>五级开班信息</name>
  <parentId>__AT2erAITyWrFQyQEh3m</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>false</readonly>
  <relatedMap><![CDATA[                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ]]></relatedMap>
  <displayType>relatedForm</displayType>
  <defaultShowMode>month</defaultShowMode>
  <collapsibleShowMode>normal</collapsibleShowMode>
  <showWaterMark>false</showWaterMark>
  <showActivityColumnType>0</showActivityColumnType>
  <searchFormId>__6ozyqRz1h3evbEOTmR0</searchFormId>
  <autoCompose>false</autoCompose>
  <relatedForm>__m9P0IztvBTahDSR5qP0</relatedForm>
  <permissionType>public</permissionType>
  <filterScript><![CDATA[]]></filterScript>
  <sqlFilterScript><![CDATA[(function(){

  var domainId = getDomainid();

  var className = getItemValueAsString("className");//班级名称
  var ClassStatus = getItemValueAsString("ClassStatus");//班级状态
  var classStartTime = getItemValueAsDate("classStartTime"); //开班开始时间
  var classEndTime = getItemValueAsDate("classEndTime"); //开班结束时间
  var trainingStartTime = getItemValueAsDate("trainingStartTime"); //实操培训开始时间
  var trainingEndTime = getItemValueAsDate("trainingEndTime"); //实操培训结束时间
  var graduationTheoryTextStartTime = getItemValueAsDate("graduationTheoryTextStartTime"); //结业理论考试开始时间
  var graduationTheoryTextEndTime = getItemValueAsDate("graduationTheoryTextEndTime"); //结业理论考试结束时间
  var graduationOperationStartTime = getItemValueAsDate("graduationOperationStartTime"); //结业实操考试开始时间
  var graduationOperationEndTime = getItemValueAsDate("graduationOperationEndTime");//结业实操考试结束时间
  var IDENTIFICATIONTHEORYSTARTTIME = getItemValueAsDate("IDENTIFICATIONTHEORYSTARTTIME"); //鉴定理论考试开始时间
  var IDENTIFICATIONTHEORYENDTIME = getItemValueAsDate("IDENTIFICATIONTHEORYENDTIME"); //鉴定理论考试结束时间
  var IDENTIFICATIONOSTARTTIME = getItemValueAsDate("IDENTIFICATIONOSTARTTIME"); //鉴定实操考试开始时间
  var IDENTIFICATIONOENDTIME = getItemValueAsDate("IDENTIFICATIONOENDTIME");//鉴定实操考试结束时间

  //println("------------------------"+trainingStartTime);

  //var whereStr = " where a.DOMAINID = '"+getDomainid()+"' ";       
  var whereStr = " where 1=1 ";         
  if(isNotNull(className)){            
    whereStr += "and (a.ITEM_CLASSNAME like concat('%','"+className+"','%'))";
  }		
  if(isNotNull(ClassStatus)){            
    whereStr += "and (a.ITEM_CLASSSTATUS like concat('%','"+ClassStatus+"','%'))";            
  } 

  if(isNotNull(trainingStartTime)){       
    whereStr += "and a.ITEM_trainingStartTime >= '"+format(trainingStartTime,"yyyy-MM-dd HH:mm:ss")+"' "; 
    //whereStr += "and a.ITEM_TRAININGSTARTTIME >= '"+trainingStartTime+"' ";       
  } 
  if(isNotNull(trainingEndTime)){      
    whereStr += "and a.ITEM_trainingEndTime <= DATE_FORMAT(DATE_ADD('"+format(trainingEndTime,"yyyy-MM-dd HH:mm:ss")+"',INTERVAL 1 DAY),'%Y-%m-%d')";
    // whereStr += "and a.ITEM_TRAININGENDTIME <= '"+trainingEndTime+"' ";            
  }
  if(isNotNull(graduationTheoryTextStartTime)){            
    //whereStr += "and a.ITEM_GRADUATIONTHEORYTEXTSTARTTIME >= '"+graduationTheoryTextStartTime+"' ";
    whereStr += "and a.ITEM_GRADUATIONTHEORYTEXTSTARTTIME >= '"+format(graduationTheoryTextStartTime,"yyyy-MM-dd HH:mm:ss")+"' ";   
  } 
  if(isNotNull(graduationTheoryTextEndTime)){            
    whereStr += "and a.ITEM_GRADUATIONTHEORYTEXTENDTIME <= DATE_FORMAT(DATE_ADD('"+format(graduationTheoryTextEndTime,"yyyy-MM-dd HH:mm:ss")+"',INTERVAL 1 DAY),'%Y-%m-%d')";
    // whereStr += "and a.ITEM_GRADUATIONTHEORYTEXTENDTIME <= '"+graduationTheoryTextEndTime+"' ";            
  }
  if(isNotNull(graduationOperationStartTime)){           
    whereStr += "and a.ITEM_GRADUATIONOPERATIONSTARTTIME >= '"+format(graduationOperationStartTime,"yyyy-MM-dd HH:mm:ss")+"' ";  
    //whereStr += "and a.ITEM_GRADUATIONOPERATIONSTARTTIME >= '"+graduationOperationStartTime+"' ";       
  } 
  if(isNotNull(graduationOperationEndTime)){    
    whereStr += "and a.ITEM_GRADUATIONOPERATIONENDTIME <= DATE_FORMAT(DATE_ADD('"+format(graduationOperationEndTime,"yyyy-MM-dd HH:mm:ss")+"',INTERVAL 1 DAY),'%Y-%m-%d')";
    // whereStr += "and a.ITEM_GRADUATIONOPERATIONENDTIME <= '"+graduationOperationEndTime+"' ";            
  }
  if(isNotNull(IDENTIFICATIONTHEORYSTARTTIME)){     
    whereStr += "and a.ITEM_IDENTIFICATIONTHEORYSTARTTIME >= '"+format(IDENTIFICATIONTHEORYSTARTTIME,"yyyy-MM-dd HH:mm:ss")+"' ";  
    //whereStr += "and a.ITEM_IDENTIFICATIONTHEORYSTARTTIME >= '"+IDENTIFICATIONTHEORYSTARTTIME+"' ";       
  } 
  if(isNotNull(IDENTIFICATIONTHEORYENDTIME)){   
    whereStr += "and a.ITEM_IDENTIFICATIONTHEORYENDTIME <= DATE_FORMAT(DATE_ADD('"+format(IDENTIFICATIONTHEORYENDTIME,"yyyy-MM-dd HH:mm:ss")+"',INTERVAL 1 DAY),'%Y-%m-%d')";
    // whereStr += "and a.ITEM_IDENTIFICATIONTHEORYENDTIME <= '"+IDENTIFICATIONTHEORYENDTIME+"' ";            
  }
  if(isNotNull(IDENTIFICATIONOSTARTTIME)){           
    whereStr += "and a.ITEM_IDENTIFICATIONOSTARTTIME >= '"+format(IDENTIFICATIONOSTARTTIME,"yyyy-MM-dd HH:mm:ss")+"' ";  
    //whereStr += "and a.ITEM_IDENTIFICATIONOSTARTTIME >= '"+IDENTIFICATIONOSTARTTIME+"' ";       
  } 
  if(isNotNull(IDENTIFICATIONOENDTIME)){            
    whereStr += "and a.ITEM_IDENTIFICATIONOENDTIME <= DATE_FORMAT(DATE_ADD('"+format(IDENTIFICATIONOENDTIME,"yyyy-MM-dd HH:mm:ss")+"',INTERVAL 1 DAY),'%Y-%m-%d')";
    // whereStr += "and a.ITEM_IDENTIFICATIONOENDTIME <= '"+IDENTIFICATIONOENDTIME+"' ";            
  }
  if(isNotNull(classStartTime)){            
    whereStr += "and a.item_applyTime >= '"+format(classStartTime,"yyyy-MM-dd HH:mm:ss")+"' ";      
  } 
  if(isNotNull(classEndTime)){            
    //whereStr += "and a.CREATED <= '"+classEndTime+"' ";  
    //whereStr += "and a.CREATED < DATE_FORMAT(DATE_ADD('"+format(classEndTime,"yyyy-MM-dd")+"',INTERVAL 1 DAY),'%Y-%m-%d')";   
    whereStr += "and a.item_applyTime < DATE_FORMAT(DATE_ADD('"+format(classEndTime,"yyyy-MM-dd HH:mm:ss")+"',INTERVAL 1 DAY),'%Y-%m-%d')";  

  }

  var sql = "select a.domainid,"+
      "a.applicationid,"+
      //"a.formid,"+
      "a.ITEM_CLASSNAME," + //班级名称
      "DATE_FORMAT(a.ITEM_applyTime,'%Y-%m-%d %H:%i:%s') as ITEM_applyTime," + //申请时间
      "a.ITEM_CLASSSTATUS," + //班级状态
      "a.ITEM_TRAININGSTARTTIME," + //开班开始结束
      "a.ITEM_TRAININGLOCATION," + //培训地点
      "a.created," + 
      "a.ITEM_GRADUATIONTHEORYTEXTSTARTTIME," + //结业理论考试开始时间
      "a.ITEM_GRADUATIONTHEORYTEXTPLACE," + //结业理论考试地点
      "a.ITEM_IDENTIFICATIONTHEORYSTARTTIME," + //鉴定理论考试开始时间
      "a.ITEM_IDENTIFICATIONTHEORYPLACE," + //鉴定理论考试地点
      "a.ITEM_TESTRESULTCONFIRMATIONSTATUS," + //考试成绩确认状态
      "COUNT(b.ITEM_NAME) as ITEM_CLASSNUM,"+//班级人员
      "CONCAT(a.ITEM_TRAININGSTARTTIME,'~',a.ITEM_TRAININGENDTIME) as ITEM_TRAININGTIME," +//实操考试时间
      "CONCAT(a.ITEM_GRADUATIONTHEORYTEXTSTARTTIME,'~',a.ITEM_GRADUATIONTHEORYTEXTENDTIME) as ITEM_GRADUATIONTHEORYTEXTTIME," +//结业-理论考试时间
      "CONCAT(a.ITEM_GRADUATIONOPERATIONSTARTTIME,'~',a.ITEM_GRADUATIONOPERATIONENDTIME) as ITEM_GRADUATIONOPERATIONTIME," +//结业-实操考试时间
      "CONCAT(a.ITEM_IDENTIFICATIONTHEORYSTARTTIME,'~',a.ITEM_IDENTIFICATIONTHEORYENDTIME) as ITEM_IDENTIFICATIONTHEORYTIME," +//鉴定-理论考试时间
      "CONCAT(a.ITEM_IDENTIFICATIONOSTARTTIME,'~',a.ITEM_IDENTIFICATIONOENDTIME) as ITEM_IDENTIFICATIONOTIME," +//鉴定-实操考试时间
      "a.ID,"+
      "a.ID as ITEM_ID"+
      " from tlk_grade_card_class a LEFT JOIN tms.tlk_level_class_student b on a.ID=b.ITEM_CLASSID " + whereStr +
      " GROUP BY a.ID order by a.created  DESC";

  println("sql==========================>" + sql);
  return sql;
})()]]></sqlFilterScript>
  <procedureFilterScript><![CDATA[]]></procedureFilterScript>
  <filterCondition><![CDATA[[]]]></filterCondition>
  <commonFilterCondition><![CDATA[[{"field":"className","isCommonFilter":"true"}]]]></commonFilterCondition>
  <authorityCondition>[]</authorityCondition>
  <openType>1</openType>
  <editMode>02</editMode>
  <width>0</width>
  <height>0</height>
</ListView>