@Override
public Grid getData(Parameter parameter) {
String sql =
"select DATEPART(HOUR,e.受理时刻) span,COUNT(*) times,SUM(case when pc.转归编码=1 then 1 else 0 end) takeBacks, "
+ "isnull(AVG(DATEDIFF(Second,e.受理时刻,t.到达现场时刻)),0) averageResponseTime,isnull(sum(datediff(Second,t.出车时刻,t.到达医院时刻)),0) outCallTotal, isnull(avg(datediff(Second,t.出车时刻,t.到达医院时刻)),0) averageTime "
+ "from AuSp120.tb_TaskV t left outer join AuSp120.tb_EventV e on e.事件编码=t.事件编码 "
+ "left outer join AuSp120.tb_PatientCase pc on t.任务编码=pc.任务编码 and t.任务序号=pc.任务序号 "
+ "where e.事件性质编码=1 and e.受理时刻 between :startTime and :endTime "
+ "group by DATEPART(HOUR,e.受理时刻) order by DATEPART(HOUR,e.受理时刻) ";
Map<String, String> paramMap = new HashMap<String, String>();
paramMap.put("startTime", parameter.getStartTime());
paramMap.put("endTime", parameter.getEndTime());
List<OutCallSpan> results =
this.npJdbcTemplate.query(
sql,
paramMap,
new RowMapper<OutCallSpan>() {
@Override
public OutCallSpan mapRow(ResultSet rs, int index) throws SQLException {
return new OutCallSpan(
rs.getString("span"),
rs.getString("times"),
rs.getString("takeBacks"),
rs.getString("averageResponseTime"),
rs.getString("outCallTotal"),
rs.getString("averageTime"));
}
});
logger.info("一共有" + results.size() + "条数据");
for (OutCallSpan result : results) {
result.setAverageResponseTime(CommonUtil.formatSecond(result.getAverageResponseTime()));
result.setAverageTime(CommonUtil.formatSecond(result.getAverageTime()));
result.setOutCallTotal(CommonUtil.formatSecond(result.getOutCallTotal()));
}
Grid grid = new Grid();
if ((int) parameter.getPage() > 0) {
int page = (int) parameter.getPage();
int rows = (int) parameter.getRows();
int fromIndex = (page - 1) * rows;
int toIndex =
(results.size() <= page * rows && results.size() >= (page - 1) * rows)
? results.size()
: page * rows;
grid.setRows(results.subList(fromIndex, toIndex));
grid.setTotal(results.size());
} else {
grid.setRows(results);
}
return grid;
}
@Override
public Grid getData(Parameter parameter) {
String sql =
"select m.姓名 dispatcher,convert(varchar(20),tr.振铃时刻,120) ringTime,convert(varchar(20),tr.通话开始时刻,120) callTime,datediff(Second,tr.振铃时刻,tr.通话开始时刻) ringDuration,tr.座席号 acceptCode,tr.备注 acceptRemark "
+ "from AuSp120.tb_TeleRecord tr left outer join AuSp120.tb_MrUser m on tr.调度员编码=m.工号 "
+ "where m.人员类型=0 and tr.振铃时刻 between :startTime and :endTime and datediff(Second,tr.振铃时刻,tr.通话开始时刻)> :overtimes ";
if (!CommonUtil.isNullOrEmpty(parameter.getDispatcher())) {
sql = sql + " and tr.调度员编码=:dispatcher ";
}
sql = sql + "order by tr.调度员编码";
Map<String, String> paramMap = new HashMap<String, String>();
paramMap.put("overtimes", parameter.getOvertimes());
paramMap.put("dispatcher", parameter.getDispatcher());
paramMap.put("startTime", parameter.getStartTime());
paramMap.put("endTime", parameter.getEndTime());
List<RingToAccept> results =
this.npJdbcTemplate.query(
sql,
paramMap,
new RowMapper<RingToAccept>() {
@Override
public RingToAccept mapRow(ResultSet rs, int index) throws SQLException {
return new RingToAccept(
rs.getString("dispatcher"),
rs.getString("ringTime"),
rs.getString("callTime"),
rs.getString("ringDuration"),
rs.getString("acceptCode"),
rs.getString("acceptRemark"));
}
});
logger.info("一共有" + results.size() + "条数据");
Grid grid = new Grid();
if ((int) parameter.getPage() > 0) {
int page = (int) parameter.getPage();
int rows = (int) parameter.getRows();
int fromIndex = (page - 1) * rows;
int toIndex =
(results.size() <= page * rows && results.size() >= (page - 1) * rows)
? results.size()
: page * rows;
grid.setRows(results.subList(fromIndex, toIndex));
grid.setTotal(results.size());
} else {
grid.setRows(results);
}
return grid;
}