巡逻态势 - 上勤次数统计
__xDl55yOVZI8fuZnYzig
__xDl55yOVZI8fuZnYzig
published
UTF-8
post
/duty/rank/onduty/times/stat
json
t3.ITEM_ORG_TIMES_RIGHT,1,0)) as greenNum, " +
"sum(if(t2.item_empCnt <= t3.ITEM_RED_TIMES,1,0)) as redNum,"+
"sum(if(t2.item_empCnt > t3.ITEM_RED_TIMES and t2.item_empCnt <= t3.ITEM_ORG_TIMES_RIGHT,1,0)) as orangeNum " +
" from (" +
"select t.ITEM_CMPID,sum(item_empCnt) AS item_empCnt " +
" from (" +
"select r.ITEM_CMPID, count(e.tenant_employee_id) as item_empCnt from duty.tlk_security_plan_company_relations r join duty.tlk_security_zone_plan p on r.ITEM_PLANID = p.id left join duty.tlk_company_selected_zone z on r.ITEM_PLANID = z.ITEM_PLANID and r.ITEM_CMPID = z.DOMAINID LEFT JOIN duty.tlk_company_zone_employee_arrange a ON z.id = a.ITEM_SELECTEDPLANZONEID left join dispatch.tenant_employee_attendance_records e on a.ITEM_EMPLOYEEID = e.tenant_employee_id and e.STATUS='0' and date(e.created_time) >= date(p.ITEM_STARTDATE) and date(e.created_time) <= date(p.ITEM_ENDDATE) ";
if(isNotNull(s_startDate)){
sql += " and date(e.created_time) >= '" + s_startDate + "' ";
}
if(isNotNull(s_endDate)){
sql += " and date(e.created_time) <= '" + s_endDate + "' ";
}
sql += " where p.ITEM_SUPERVISEDEPID in" + subGroup + " and p.item_onoff = '1' and date(p.item_startDate)<= CURRENT_DATE and date(p.item_endDate)>= CURRENT_DATE GROUP BY r.ITEM_CMPID" +
" UNION ALL" +
" select r.ITEM_CMPID, count(e.tenant_employee_id) as item_empCnt from duty.tlk_security_action_company_relations r join duty.tlk_security_action_plan p on r.item_planid = p.id left join duty.tlk_security_action_station_relations s on s.ITEM_PLANID = p.ID and s.ITEM_DOMAINID = r.ITEM_CMPID left JOIN duty.tlk_company_action_employee_arrange a on a.DOMAINID = r.ITEM_CMPID and a.ITEM_PLANID = r.ITEM_PLANID left join dispatch.tenant_employee_attendance_records e on a.ITEM_EMPLOYEEID = e.tenant_employee_id and s.ITEM_STATIONID = e.reference_number and e.STATUS='0' and date(e.created_time) >= date(p.ITEM_STARTDATE) and date(e.created_time) <= date(p.ITEM_ENDDATE)";
if(isNotNull(s_startDate)){
sql += " and date(e.created_time) >= '" + s_startDate + "' ";
}
if(isNotNull(s_endDate)){
sql += " and date(e.created_time) <= '" + s_endDate + "' ";
}
sql += " where p.ITEM_SUPERVISEDEPID in " + subGroup + " and p.item_onoff = '1' and date(p.item_startDate)<= CURRENT_DATE and date(p.item_endDate)>= CURRENT_DATE GROUP BY r.ITEM_CMPID" +
") t GROUP BY t.ITEM_CMPID" +
") t2 " +
" left join (select ITEM_RED_TIMES, ITEM_ORG_TIMES_RIGHT from duty.tlk_patrol_situation_setting l where l.ITEM_ONOFF = '1' and lastmodified = (select max(ll.lastmodified) from duty.tlk_patrol_situation_setting ll where ll.ITEM_ONOFF = '1')) t3 " +
" on 1 = 1 ";
var datas = queryByDSName("duty",sql);
var result = new Packages.java.util.HashMap();
//result.put("sql", sql);
result.put("data",datas);
return result;
})()]]>