package cn.ksource.web.facade.traffic;
|
|
import java.text.SimpleDateFormat;
|
import java.util.HashMap;
|
import java.util.Map;
|
import java.util.TimeZone;
|
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import cn.ksource.core.dao.BaseDao;
|
import cn.ksource.core.page.PageInfo;
|
import cn.ksource.core.util.ConvertUtil;
|
import cn.ksource.core.util.DateUtil;
|
import cn.ksource.core.util.StringUtil;
|
import cn.ksource.web.Constants;
|
|
@Service
|
public class TrafficFacadeImpl implements TrafficFacade{
|
@Autowired
|
private BaseDao baseDao;
|
@SuppressWarnings({ "rawtypes", "unchecked" })
|
@Override
|
public PageInfo getTrafficData(Map param, PageInfo pageInfo) {
|
String sql="select a.*,e.bid tid,e.order_code,e.BUSINESSTYPE,e.BUSINESS_ID,e.cid from traffic a " +
|
" left join (select b.tra_id,b.id bid,c.order_code,d.BUSINESSTYPE,d.id BUSINESS_ID,c.id cid from sc_workflow_incident c,workflow_base d,traffic_ref b" +
|
" where c.id=d.BUSINESS_ID and b.order_id=c.id ) e on e.tra_id=a.id " +
|
" where 1=1 ";
|
String start_time=ConvertUtil.obj2StrBlank(param.get("start_time"));
|
String end_time=ConvertUtil.obj2StrBlank(param.get("end_time"));
|
String mobile=ConvertUtil.obj2StrBlank(param.get("mobile"));
|
if(StringUtil.notEmpty(start_time)){
|
start_time+="000000";
|
param.put("start_time", start_time);
|
sql+=" and a.create_time>:start_time ";
|
}
|
if(StringUtil.notEmpty(end_time)){
|
end_time+="235959";
|
param.put("end_time", end_time);
|
sql+=" and a.create_time<:end_time ";
|
}
|
if(StringUtil.notEmpty(mobile)){
|
sql+=" and a.CUSTOMER_MOBILE=:mobile ";
|
}
|
String customer_mobile=ConvertUtil.obj2StrBlank(param.get("customer_mobile"));
|
if(StringUtil.notEmpty(customer_mobile)){
|
sql+=" and a.CUSTOMER_MOBILE=:customer_mobile ";
|
}
|
String user_name=ConvertUtil.obj2StrBlank(param.get("user_name"));
|
if(StringUtil.notEmpty(user_name)){
|
param.put("user_name", "%"+user_name+"%");
|
sql+=" and a.user_name like :user_name ";
|
}
|
String tra_state=ConvertUtil.obj2StrBlank(param.get("tra_state"));
|
if(StringUtil.notEmpty(tra_state)){
|
sql+=" and a.tra_state=:tra_state ";
|
}
|
sql+=" order by a.create_time desc ";
|
return baseDao.queryforSplitPageInfo(pageInfo, sql, param);
|
|
}
|
|
@SuppressWarnings({ "rawtypes", "unchecked" })
|
@Override
|
public int getTrafficCount(Map param) {
|
String sql="select count(id) from traffic where 1=1 ";
|
String start_time=ConvertUtil.obj2StrBlank(param.get("start_time"));
|
String end_time=ConvertUtil.obj2StrBlank(param.get("end_time"));
|
String mobile=ConvertUtil.obj2StrBlank(param.get("mobile"));
|
if(StringUtil.notEmpty(start_time)){
|
start_time+="000000";
|
param.put("start_time", start_time);
|
sql+=" and create_time>:start_time ";
|
}
|
if(StringUtil.notEmpty(end_time)){
|
end_time+="235959";
|
param.put("end_time", end_time);
|
sql+=" and create_time<:end_time ";
|
}
|
if(StringUtil.notEmpty(mobile)){
|
sql+=" and CUSTOMER_MOBILE=:mobile ";
|
}
|
String customer_mobile=ConvertUtil.obj2StrBlank(param.get("customer_mobile"));
|
if(StringUtil.notEmpty(customer_mobile)){
|
sql+=" and CUSTOMER_MOBILE=:customer_mobile ";
|
}
|
String user_name=ConvertUtil.obj2StrBlank(param.get("user_name"));
|
if(StringUtil.notEmpty(user_name)){
|
param.put("user_name", "%"+user_name+"%");
|
sql+=" and user_name like :user_name ";
|
}
|
String tra_state=ConvertUtil.obj2StrBlank(param.get("tra_state"));
|
if(StringUtil.notEmpty(tra_state)){
|
sql+=" and tra_state=:tra_state ";
|
}
|
return baseDao.queryForInteger(sql, param);
|
}
|
|
@SuppressWarnings("rawtypes")
|
@Override
|
public Map getTraffic(Map param) {
|
String sql="select * from traffic where id=:id ";
|
return baseDao.queryForMap(sql, param);
|
}
|
|
@SuppressWarnings({ "unchecked", "rawtypes" })
|
@Override
|
public PageInfo getTrafficOrderData(Map param, PageInfo pageInfo) {
|
String sql="select a.*,c.ID BUSINESS_ID,c.BUSINESSTYPE BUSINESSTYPE,c.WFSTATE WFSTATE from sc_workflow_incident a,traffic_ref b,workflow_base c where a.order_code=b.order_code and a.id=c.business_id ";
|
String order_name=ConvertUtil.obj2StrBlank(param.get("order_name"));
|
String order_code=ConvertUtil.obj2StrBlank(param.get("order_code"));
|
String mobile=ConvertUtil.obj2StrBlank(param.get("mobile"));
|
if(StringUtil.notEmpty(order_name)){
|
param.put("order_name", "%"+order_name+"%");
|
sql+=" and a.NAME like :order_name";
|
}
|
if(StringUtil.notEmpty(order_code)){
|
sql+=" and a.order_code=:order_code ";
|
}
|
if(StringUtil.notEmpty(mobile)){
|
sql+=" and b.mobile=:mobile ";
|
}
|
sql+=" order by a.CREATE_TIME DESC";
|
return baseDao.queryforSplitPageInfo(pageInfo, sql, param);
|
}
|
|
@SuppressWarnings({ "unchecked", "rawtypes" })
|
@Override
|
public int getTrafficOrderCount(Map param) {
|
String sql="select count(a.id) from sc_workflow_incident a,traffic_ref b,workflow_base c where a.order_code=b.order_code and a.id=c.business_id ";
|
String order_name=ConvertUtil.obj2StrBlank(param.get("order_name"));
|
String order_code=ConvertUtil.obj2StrBlank(param.get("order_code"));
|
String mobile=ConvertUtil.obj2StrBlank(param.get("mobile"));
|
if(StringUtil.notEmpty(order_name)){
|
param.put("order_name", "%"+order_name+"%");
|
sql+=" and a.NAME like :order_name";
|
}
|
if(StringUtil.notEmpty(order_code)){
|
sql+=" and a.order_code=:order_code ";
|
}
|
if(StringUtil.notEmpty(mobile)){
|
sql+=" and b.mobile=:mobile ";
|
}
|
return baseDao.queryForInteger(sql, param);
|
}
|
|
@SuppressWarnings({ "unchecked", "rawtypes" })
|
@Override
|
public Map getTrafficNum(Map param) {
|
String start_time=ConvertUtil.obj2Str(DateUtil.getCurrentDate8())+"000000";
|
String end_time=ConvertUtil.obj2Str(DateUtil.getCurrentDate8())+"235959";
|
Map result=new HashMap();
|
param.put("start_time", start_time);
|
param.put("end_time", end_time);
|
String sql="select count(id) from traffic where create_time>:start_time and create_time<:end_time and tra_state=1 ";
|
//今日通话数
|
int count=baseDao.queryForInteger(sql, param);
|
result.put("count", count);
|
String sqla="select count(id) from traffic where create_time>:start_time and create_time<:end_time and (tra_state=0 or tra_state is null) ";
|
//今日未通话
|
int count1=baseDao.queryForInteger(sqla, param);
|
result.put("count1", count1);
|
String sqlb="select count(id) from traffic where create_time>:start_time and create_time<:end_time ";
|
//今日总数
|
int countNum=baseDao.queryForInteger(sqlb, param);
|
result.put("countNum", countNum);
|
String sqlc="select count(a.id) from traffic a,traffic_ref b where a.create_time>:start_time and a.create_time<:end_time and a.id=b.tra_id ";
|
//今日生成工单
|
int countOrder=baseDao.queryForInteger(sqlc, param);
|
result.put("countOrder", countOrder);
|
|
|
|
String sqld="select count(id) from traffic where tra_state=1 ";
|
//通话数
|
int num=baseDao.queryForInteger(sqld, param);
|
result.put("num", num);
|
String sqle="select count(id) from traffic where tra_state=0 or tra_state is null ";
|
//今日未通话
|
int num1=baseDao.queryForInteger(sqle, param);
|
result.put("num1", num1);
|
String sqlf="select count(id) from traffic where 1=1 ";
|
//总数
|
int numSum=baseDao.queryForInteger(sqlf, param);
|
result.put("numSum", numSum);
|
|
String sqlg="select count(a.id) from traffic a,traffic_ref b where a.id=b.tra_id ";
|
//今日生成工单
|
int numOrder=baseDao.queryForInteger(sqlg, param);
|
result.put("numOrder", numOrder);
|
return result;
|
}
|
|
@SuppressWarnings("unchecked")
|
@Override
|
public Map saveTraffic(Map param) {
|
String id=StringUtil.getUUID();
|
param.put("id", id);
|
param.put("create_time", DateUtil.getCurrentDate14());
|
String sql="insert into traffic(ID,SERIAL_NUMBER,CUSTOMER_MOBILE,TRA_STATE,USER_ID,USER_NAME," +
|
"ORDER_STATE,CREATE_TIME,START_TIME) values(:id,:sn,:ph,0,:userId,:userName,0,:create_time,:tm)";
|
baseDao.execute(sql, param);
|
Map result=new HashMap();
|
result.put("id", id);
|
result.put("mobile", param.get("ph"));
|
return result;
|
}
|
|
@SuppressWarnings("unchecked")
|
@Override
|
public void updateTraffic(Map param) {
|
String time=ConvertUtil.obj2StrBlank(param.get("time")).replace(":", "");
|
// if(StringUtil.isEmpty(time)){
|
// time=ConvertUtil.obj2StrBlank(DateUtil.getCurrentDate14()).substring(8, 14);
|
// }
|
String now=ConvertUtil.obj2StrBlank(DateUtil.getCurrentDate8())+time;
|
param.put("now", now);
|
String sql="update traffic set TRA_STATE=1,END_TIME=:now where SERIAL_NUMBER=:sn";
|
baseDao.execute(sql, param);
|
String sqla="select * from traffic where SERIAL_NUMBER=:sn ";
|
Map map=baseDao.queryForMap(sqla, param);
|
if(StringUtil.notEmpty(ConvertUtil.obj2StrBlank(map.get("START_TIME")))){
|
if(StringUtil.notEmpty(ConvertUtil.obj2StrBlank(map.get("END_TIME")))){
|
long s=DateUtil.getMillisFormDate2Date(ConvertUtil.obj2Long(map.get("START_TIME")),
|
ConvertUtil.obj2Long(map.get("END_TIME")));
|
SimpleDateFormat format=new SimpleDateFormat("HH:mm:ss");
|
format.setTimeZone(TimeZone.getTimeZone("GMT_00:00"));
|
String hs=format.format(s);
|
param.put("hs", hs);
|
String sqlb="update traffic set LONG_TIME=:hs where SERIAL_NUMBER=:sn";
|
baseDao.execute(sqlb, param);
|
}
|
}
|
}
|
@SuppressWarnings({ "unchecked", "rawtypes" })
|
@Override
|
public PageInfo getTrafficOrderDataTwo(Map param, PageInfo pageInfo) {
|
String sql="select a.*,c.ID BUSINESS_ID,c.BUSINESSTYPE BUSINESSTYPE,c.WFSTATE WFSTATE from sc_workflow_incident a,workflow_base c where a.id=c.business_id ";
|
String order_name=ConvertUtil.obj2StrBlank(param.get("order_name"));
|
String order_code=ConvertUtil.obj2StrBlank(param.get("order_code"));
|
String mobile=ConvertUtil.obj2StrBlank(param.get("mobile"));
|
if(StringUtil.notEmpty(order_name)){
|
param.put("order_name", "%"+order_name+"%");
|
sql+=" and a.NAME like :order_name";
|
}
|
if(StringUtil.notEmpty(order_code)){
|
sql+=" and a.order_code=:order_code ";
|
}
|
if(StringUtil.notEmpty(mobile)){
|
sql+=" and a.CONTACT_PHONE=:mobile ";
|
}
|
sql+=" order by a.CREATE_TIME DESC";
|
return baseDao.queryforSplitPageInfo(pageInfo, sql, param);
|
}
|
@SuppressWarnings({ "unchecked", "rawtypes" })
|
@Override
|
public int getTrafficOrderCountTwo(Map param) {
|
String sql="select count(a.id) from sc_workflow_incident a,workflow_base c where a.id=c.business_id ";
|
String order_name=ConvertUtil.obj2StrBlank(param.get("order_name"));
|
String order_code=ConvertUtil.obj2StrBlank(param.get("order_code"));
|
String mobile=ConvertUtil.obj2StrBlank(param.get("mobile"));
|
if(StringUtil.notEmpty(order_name)){
|
param.put("order_name", "%"+order_name+"%");
|
sql+=" and a.NAME like :order_name";
|
}
|
if(StringUtil.notEmpty(order_code)){
|
sql+=" and a.order_code=:order_code ";
|
}
|
if(StringUtil.notEmpty(mobile)){
|
sql+=" and a.CONTACT_PHONE=:mobile ";
|
}
|
return baseDao.queryForInteger(sql, param);
|
}
|
}
|
