我的考试
__coTyEAVU2rvXr4ErDl2
__coTyEAVU2rvXr4ErDl2
published
UTF-8
post
/bkt/myEnterpriseExam
json
now() and (person.ITEM_examinee_exam_score is null OR person.ITEM_examinee_exam_score = '')";
}else if("2".equals(status)){
whereStr += " and exam.item_exam_release_status = '1' and (exam.ITEM_exam_effect_start_time <= now() and exam.ITEM_exam_effect_end_time >= now()) and (person.ITEM_examinee_exam_score is null OR person.ITEM_examinee_exam_score = '')";
}else if("3".equals(status)){
whereStr += " and exam.item_exam_release_status = '1' and person.ITEM_examinee_pass_status = '0' and (person.ITEM_examinee_exam_score is not null OR person.ITEM_examinee_exam_score <> '')";
}else if("4".equals(status)){
whereStr += " and exam.item_exam_release_status = '1' and person.ITEM_examinee_pass_status = '1' and (person.ITEM_examinee_exam_score is not null OR person.ITEM_examinee_exam_score <> '')";
}else if("5".equals(status)){
whereStr += " and exam.item_exam_release_status = '2'";
}
}
if(judgePlatform()){
sql = "SELECT "+
"exam.id,"+
"case when person.ITEM_examinee_exam_num > 0 then concat(exam.ITEM_EXAM_NAME,'（补考）') else exam.ITEM_EXAM_NAME end ITEM_EXAM_NAME,"+
"DATE_FORMAT(exam.ITEM_exam_effect_start_time,'%Y-%m-%d %H:%i') ITEM_exam_effect_start_time,"+
"DATE_FORMAT(exam.ITEM_exam_effect_end_time,'%Y-%m-%d %H:%i') ITEM_exam_effect_end_time,"+
"case when exam.item_exam_release_status = '2' then '已取消' else "+
"case when (person.ITEM_examinee_exam_score is null OR person.ITEM_examinee_exam_score = '') then "+
"case when (exam.ITEM_exam_effect_start_time <= now() and exam.ITEM_exam_effect_end_time >= now()) then '已开始' else "+
"case when exam.ITEM_exam_effect_start_time > now() then '未开始' else '已结束' end "+
"end "+
"else "+
"case person.ITEM_examinee_pass_status when '0' then '未通过' when '1' then '已通过' end "+
"end "+
"end `status`,"+
"case when (exam.ITEM_exam_effect_start_time <= now() and exam.ITEM_exam_effect_end_time >= now()) then '有效中' else '已失效' end effectStatus "+
"FROM tms.tlk_enterprise_exam_info exam "+
"LEFT JOIN tms.tlk_enterprise_exam_examinee person ON (person.item_exam_info_id = exam.id and person.ITEM_ISDELETE = '0' and person.ITEM_examinee_id = '"+employeeId+"') "+
"WHERE exam.ITEM_ISDELETE = '0' "+
"and FIND_IN_SET((SELECT organization_id FROM obpm2.tenant_employees WHERE id = '"+employeeId+"' and `status` = '0'),exam.item_companyId)" + whereStr+limitStr;
totalSql = "SELECT "+
"count(1) total "+
"FROM tms.tlk_enterprise_exam_info exam "+
"WHERE exam.ITEM_ISDELETE = '0' "+
"and FIND_IN_SET((SELECT organization_id FROM obpm2.tenant_employees WHERE id = '"+employeeId+"' and `status` = '0'),exam.item_companyId)" + whereStr;
}else{
sql = "SELECT "+
"exam.id,"+
"case num.num when '1' then exam.ITEM_EXAM_NAME else concat(exam.ITEM_EXAM_NAME,'(补考)') end ITEM_EXAM_NAME,"+
"DATE_FORMAT(exam.ITEM_exam_effect_start_time,'%Y-%m-%d %H:%i') ITEM_exam_effect_start_time,"+
"DATE_FORMAT(exam.ITEM_exam_effect_end_time,'%Y-%m-%d %H:%i') ITEM_exam_effect_end_time,"+
"case when exam.item_exam_release_status = '2' then '已取消' else "+
"case when (person.ITEM_examinee_exam_score is null OR person.ITEM_examinee_exam_score = '') then "+
"case when (exam.ITEM_exam_effect_start_time <= now() and exam.ITEM_exam_effect_end_time >= now()) then '已开始' else "+
"case when exam.ITEM_exam_effect_start_time > now() then '未开始' else '已结束' end "+
"end "+
"else "+
"case person.ITEM_examinee_pass_status when '0' then '未通过' when '1' then '已通过' end "+
"end "+
"end `status`,"+
"case when (exam.ITEM_exam_effect_start_time <= now() and exam.ITEM_exam_effect_end_time >= now()) then '有效中' else '已失效' end effectStatus "+
"FROM tms.tlk_enterprise_exam_examinee person "+
"LEFT JOIN tms.tlk_enterprise_exam_info exam ON person.item_exam_info_id = exam.id "+
"LEFT JOIN (select ITEM_examinee_id,item_exam_info_id,count(1) num from tms.tlk_enterprise_exam_examinee group by item_exam_info_id,ITEM_examinee_id) num "+
"ON (person.item_exam_info_id = num.item_exam_info_id AND person.ITEM_examinee_id = num.ITEM_examinee_id) "+
"WHERE exam.ITEM_ISDELETE = '0' and person.item_isDelete = '0' and exam.item_exam_release_status <> '0' and person.ITEM_examinee_id = '"+employeeId+"'" + whereStr+limitStr;
totalSql = "SELECT "+
"count(1) total "+
"FROM tms.tlk_enterprise_exam_examinee person "+
"LEFT JOIN tms.tlk_enterprise_exam_info exam ON person.item_exam_info_id = exam.id "+
"LEFT JOIN (select ITEM_examinee_id,item_exam_info_id,count(1) num from tms.tlk_enterprise_exam_examinee group by item_exam_info_id,ITEM_examinee_id) num "+
"ON (person.item_exam_info_id = num.item_exam_info_id AND person.ITEM_examinee_id = num.ITEM_examinee_id) "+
"WHERE exam.ITEM_ISDELETE = '0' and person.item_isDelete = '0' " + whereStr;
}
var data = queryByDSName("tms",sql);
var iter = data.iterator();
var totalData = queryByDSName("tms",totalSql);
var totalIter = totalData.iterator();
if(iter.hasNext()){
result.put("msg","success");
result.put("code","1");
result.put("data",data);
result.put("pageNo",parseInt(pageIndex));
result.put("pageSize",parseInt(pageSize));
result.put("total",totalIter.next().get("total"));
}else{
result.put("msg","我的考试不存在");
result.put("code","-1");
}
return result;
})()]]>