待办事项详情
__coTyEAVU2rvXr4ErDl2
__coTyEAVU2rvXr4ErDl2
published
UTF-8
post
/bkt/waitToDoDetail
json
= now()";
examWhereStrDate += " and exam.ITEM_exam_effect_start_time <= now() and exam.ITEM_exam_effect_end_time >= now()";
}else if("expire".equals(dateParams)){
planWhereStrDate += " and plan.ITEM_effect_date_end <= now()";
examWhereStrDate += " and exam.ITEM_exam_effect_end_time <= now()";
}
}
var sql = "SELECT id,type,typeName,effect_date,process,status,effectStatus FROM ( "+
"SELECT "+
"plan.id,"+
"'计划' type,"+
"plan.ITEM_learnplan_name typeName,"+
"concat(DATE_FORMAT(plan.ITEM_effect_date_start,'%Y.%m.%d %H:%i'),'-',DATE_FORMAT(plan.ITEM_effect_date_end,'%Y.%m.%d %H:%i')) effect_date,"+
"ifnull(process.process,0) process,"+
"case process.process when '0' then '未完成' else '进行中' end status,"+
"case when (plan.ITEM_effect_date_start <= now() and plan.ITEM_effect_date_end >= now()) then '有效中' else '已失效' end effectStatus "+
"FROM tms.tlk_learnPlan plan "+
"LEFT JOIN tms.tlk_company_plan p on plan.id = p.item_planid "+
"LEFT JOIN tms.tlk_learnPlan_test_examinee person ON person.ITEM_learnplan_id = plan.id and person.item_isDelete = '0' and person.ITEM_EXAMINEE_ID = '"+employeeId+"' "+
"LEFT JOIN (SELECT ITEM_learnplan_id,ITEM_examinee_id,sum(ITEM_process) / count(1) process FROM tms.tlk_learnPlan_mission_process WHERE ITEM_ISDELETE = '0' GROUP BY ITEM_learnplan_id, ITEM_examinee_id) process ON (process.ITEM_learnplan_id = plan.id AND process.ITEM_examinee_id = person.ITEM_examinee_id)"+
"WHERE plan.item_isDelete = '0' and ifnull(process.process,0) < 1 AND ((person.ITEM_examinee_learnPlan_status = '0' AND person.DOMAINID = '"+domainId+"' AND person.ITEM_examinee_id = '"+employeeId+"') or p.item_companyid = '"+domainId+"')"+planWhereStrDate+
" UNION "+
"SELECT "+
"exam.id,"+
"'考试' type,"+
"exam.ITEM_exam_name typeName,"+
"concat(DATE_FORMAT(exam.ITEM_exam_effect_start_time,'%Y.%m.%d %H:%i'),'-',DATE_FORMAT(exam.ITEM_exam_effect_end_time,'%Y.%m.%d %H:%i')) effect_date,"+
"'0' process,"+
"'未完成' `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 "+
"WHERE person.item_isdelete = '0' AND exam.item_isDelete = '0' AND ITEM_examinee_exam_status = '0' "+
"AND person.DOMAINID = '"+domainId+"' and person.ITEM_examinee_id = '"+employeeId+"'"+examWhereStrDate+
" UNION "+
"SELECT "+
"exam.id,"+
"'考试' type,"+
"exam.ITEM_exam_name typeName,"+
"concat(DATE_FORMAT(exam.ITEM_exam_effect_start_time,'%Y.%m.%d %H:%i'),'-',DATE_FORMAT(exam.ITEM_exam_effect_end_time,'%Y.%m.%d %H:%i')) effect_date,"+
"'0' process,"+
"'未完成' `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) "+
"AND person.id is null OR person.ITEM_examinee_exam_status = '0' "+examWhereStrDate+
") T"+whereStr + limitStr;
var data = queryByDSName("tms",sql);
var iter = data.iterator();
var totalSql = "select count(1) total from ( "+
"SELECT id,type,typeName,effect_date,process,status FROM ( "+
"SELECT "+
"plan.id,"+
"'计划' type,"+
"plan.ITEM_learnplan_name typeName,"+
"concat(DATE_FORMAT(plan.ITEM_effect_date_start,'%Y.%m.%d %H:%i'),'-',DATE_FORMAT(plan.ITEM_effect_date_end,'%Y.%m.%d %H:%i')) effect_date,"+
"person.ITEM_mission_progress process,"+
"case person.ITEM_mission_progress when '0' then '未完成' else '进行中' end status "+
"FROM tms.tlk_learnPlan plan "+
"LEFT JOIN tms.tlk_company_plan p on plan.id = p.item_planid "+
"LEFT JOIN tms.tlk_learnPlan_test_examinee person ON person.ITEM_learnplan_id = plan.id and person.item_isDelete = '0' and person.ITEM_EXAMINEE_ID = '"+employeeId+"' "+
"WHERE plan.item_isDelete = '0' AND ((person.ITEM_examinee_learnPlan_status = '0' AND person.DOMAINID = '"+domainId+"' AND person.ITEM_examinee_id = '"+employeeId+"') or p.item_companyid = '"+domainId+"') "+planWhereStrDate+
" UNION "+
"SELECT "+
"exam.id,"+
"'考试' type,"+
"exam.ITEM_exam_name typeName,"+
"concat(DATE_FORMAT(exam.ITEM_exam_effect_start_time,'%Y.%m.%d %H:%i'),'-',DATE_FORMAT(exam.ITEM_exam_effect_end_time,'%Y.%m.%d %H:%i')) effect_date,"+
"'0' process,"+
"'未完成' status "+
"FROM tms.tlk_enterprise_exam_examinee person "+
"LEFT JOIN tms.tlk_enterprise_exam_info exam ON person.ITEM_exam_info_id = exam.id "+
"WHERE person.item_isdelete = '0' AND exam.item_isDelete = '0' AND ITEM_examinee_exam_status = '0' "+
"AND person.DOMAINID = '"+domainId+"' and person.ITEM_examinee_id = '"+employeeId+"'"+examWhereStrDate+
" UNION "+
"SELECT "+
"exam.id,"+
"'考试' type,"+
"exam.ITEM_exam_name typeName,"+
"concat(DATE_FORMAT(exam.ITEM_exam_effect_start_time,'%Y.%m.%d %H:%i'),'-',DATE_FORMAT(exam.ITEM_exam_effect_end_time,'%Y.%m.%d %H:%i')) effect_date,"+
"'0' process,"+
"'未完成' `status` "+
"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) "+
"AND person.id is null OR person.ITEM_examinee_exam_status = '0' "+examWhereStrDate+
") T"+whereStr+") TA";
var totalData = queryByDSName("tms",totalSql);
var totalIter = totalData.iterator();
var result = new Packages.java.util.HashMap();
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;
})()]]>