package com.jd.sns.chat.common.manager.empdailyreport.impl;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.springframework.stereotype.Component;
import com.jd.sns.chat.common.dao.empdailyreport.JdEmpDailyReportDao;
import com.jd.sns.chat.common.domain.empdailyreport.EmpDailyReport;
import com.jd.sns.chat.common.domain.empdailyreport.OrgProfile;
import com.jd.sns.chat.common.domain.empdailyreport.ReportProfile;
import com.jd.sns.chat.common.domain.empdailyreport.ReportResult;
import com.jd.sns.chat.common.domain.empdailyreport.ServerProfile;
import com.jd.sns.chat.common.manager.base.BaseManager;
import com.jd.sns.chat.common.manager.empdailyreport.JdEmpDailyReportManager;
@Component("jdEmpDailyReportManager")
public class JdEmpDailyReportManagerImpl extends BaseManager implements JdEmpDailyReportManager {
private JdEmpDailyReportDao jdEmpDailyReportDao;
private static final String JD_EMP_DAILY_REPORT_CACHE_KEY="jd_emp_daily_report_cache_key";
public JdEmpDailyReportDao getJdEmpDailyReportDao() {
return jdEmpDailyReportDao;
}
public void setJdEmpDailyReportDao(JdEmpDailyReportDao jdEmpDailyReportDao) {
this.jdEmpDailyReportDao = jdEmpDailyReportDao;
}
@Override
public List<EmpDailyReport> getDataFromDb(String dateString){
List<EmpDailyReport> datas = new ArrayList<EmpDailyReport>();
EmpDailyReport emp = null;
//获取报表数据
ReportProfile reportProfile = new ReportProfile();
reportProfile.setStartTime(dateString);
reportProfile.setEndTime(this.getSpecifiedDayAfter(dateString));
List<ServerProfile> serverPins = jdEmpDailyReportDao.findAllServerPin();
List<String> workNos = jdEmpDailyReportDao.findAllWorkNo();
List<ReportResult> receptionNo = jdEmpDailyReportDao.findReceptionNo(reportProfile);
List<ReportResult> transferOutNo = jdEmpDailyReportDao.findTransferOutNo(reportProfile);
List<ReportResult> transferInNo = jdEmpDailyReportDao.findTransferInNo(reportProfile);
List<ReportResult> evaluationNo = jdEmpDailyReportDao.findEvaluationNo(reportProfile);
reportProfile.setDegree(100);
List<ReportResult> degree100 = jdEmpDailyReportDao.findDegreesNoByDegree(reportProfile);
reportProfile.setDegree(75);
List<ReportResult> degree75 = jdEmpDailyReportDao.findDegreesNoByDegree(reportProfile);
reportProfile.setDegree(50);
List<ReportResult> degree50= jdEmpDailyReportDao.findDegreesNoByDegree(reportProfile);
reportProfile.setDegree(25);
List<ReportResult> degree25= jdEmpDailyReportDao.findDegreesNoByDegree(reportProfile);
reportProfile.setDegree(0);
List<ReportResult> degree0 = jdEmpDailyReportDao.findDegreesNoByDegree(reportProfile);
List<ReportResult> consultingNo = jdEmpDailyReportDao.findConsultingNo(reportProfile);
List<OrgProfile> orgs = jdEmpDailyReportDao.findSkillGroupProfile();
for (int i=0;i<serverPins.size();i++){
String serverPin = serverPins.get(i).getPin();
emp = new EmpDailyReport();
this.getOrgGroup(serverPins, orgs, i, emp); //确定技能组
emp.setPin(serverPin); //员工pin
emp.setWorkNo(workNos.get(i)); //工号
int consulting=0;
int evaluation=0;
int score100=0;
int score75=0;
int score25=0;
int score0=0;
for(int j=0;j<receptionNo.size();j++){
if (serverPin.equals(receptionNo.get(j).getPin())){
emp.setReception(receptionNo.get(j).getCount());
break;
}
} //接待量
for(int j=0;j<transferOutNo.size();j++){
if (serverPin.equals(transferOutNo.get(j).getPin())){
emp.setTransferOut(transferOutNo.get(j).getCount());
break;
}
} //转出量
for(int j=0;j<transferInNo.size();j++){
if (serverPin.equals(transferInNo.get(j).getPin())){
emp.setTransferIn(transferInNo.get(j).getCount());
break;
}
} //转入量
for(int j=0;j<evaluationNo.size();j++){
if (serverPin.equals(evaluationNo.get(j).getPin())){
emp.setEvaluation(evaluationNo.get(j).getCount());
evaluation = emp.getEvaluation();
break;
}
} //评价总数
for(int j=0;j<consultingNo.size();j++){
if (serverPin.equals(consultingNo.get(j).getPin())){
emp.setConsulting(consultingNo.get(j).getCount());
consulting = emp.getConsulting();
break;
}
} //咨询量
for(int j=0;j<degree100.size();j++){
if (serverPin.equals(degree100.get(j).getPin())){
emp.setDegree100(degree100.get(j).getCount());
score100 = emp.getDegree100();
break;
}
} //非常满意
for(int j=0;j<degree75.size();j++){
if (serverPin.equals(degree75.get(j).getPin())){
emp.setDegree75(degree75.get(j).getCount());
score75 = emp.getDegree75();
break;
}
} //满意
for(int j=0;j<degree50.size();j++){
if (serverPin.equals(degree50.get(j).getPin())){
emp.setDegree50(degree50.get(j).getCount());
break;
}
} //一般
for(int j=0;j<degree25.size();j++){
if (serverPin.equals(degree25.get(j).getPin())){
emp.setDegree25(degree25.get(j).getCount());
score25 = emp.getDegree25();
break;
}
} //不满意
for(int j=0;j<degree0.size();j++){
if (serverPin.equals(degree0.get(j).getPin())){
emp.setDegree0(degree0.get(j).getCount());
score0 = emp.getDegree0();
break;
}
} //非常不满意
DecimalFormat df = new DecimalFormat("0.00");
String evaluationRate = df.format((double)((0==consulting) ? 0 : (double)evaluation/consulting));
emp.setEvaluationRate(evaluationRate); //评价率
int satisfiedSum = score100 + score75;
String satisfiedRate = df.format((0==evaluation) ? 0 : (double)satisfiedSum/evaluation);
emp.setSatisfactionRate(satisfiedRate); //满意度
int notSatisfiedSum = score25 + score0;
String notSatisfiedRate = df.format((0==evaluation) ? 0 : (double)notSatisfiedSum/evaluation);
emp.setNotSatisfactionRate(notSatisfiedRate); //不满意度
datas.add(emp);
}
return datas;
}
@Override
public String getCacheKey(String dateString) {
return JD_EMP_DAILY_REPORT_CACHE_KEY+"_"+dateString;
}
@Override @SuppressWarnings("unchecked")
public List<EmpDailyReport> getData(String dateString) {
List<EmpDailyReport> list = null;
String key = this.getCacheKey(dateString);
Object obj = null;
try {
obj = cacheUtils.get(key);
} catch (Exception e) {
log.error(e.getMessage());
}
if (obj != null){
list = (List<EmpDailyReport>) obj;
}else {
try {
list = this.getDataFromDb(dateString);
cacheUtils.set(key,10, list);
} catch (Exception e) {
log.error(e.getMessage());
}
}
return list;
}
@Override
public String getSpecifiedDayAfter(String dateString) {
Calendar c = Calendar.getInstance();
Date date2 = null;
try {
date2 = new SimpleDateFormat("yy-MM-dd").parse(dateString);
} catch (ParseException e) {
e.printStackTrace();
}
c.setTime(date2);
int day = c.get(Calendar.DATE);
c.set(Calendar.DATE, day + 1);
String dayAfter = new SimpleDateFormat("yyyy-MM-dd")
.format(c.getTime());
return dayAfter;
}
public void getOrgGroup(List<ServerProfile> serverPins,List<OrgProfile> orgs,int i,EmpDailyReport emp){
int orgId=0;
for (int j=0;j<orgs.size();j++){
if(serverPins.get(i).getId()==orgs.get(j).getOrgId()){
if(orgs.get(j).getFid()==0){
orgId = orgs.get(j).getOrgId();
}else{
orgId = orgs.get(j).getFid();
}
}
for (int k=0;k<headOrgList.size();k++){
orgMark.add("");
}
for(int k=0;k<headOrgList.size();k++){
if (orgId == headOrgList.get(k).getOrgId()){
orgMark.set(k, "Y");
}
}
emp.setOrgName(orgMark);
}
@Override
public List<OrgProfile> getOrgs(){
for (OrgProfile org : list){
if(org.getFid()==0){
orgsList.add(org);
}
}
return orgsList;
}
}
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.springframework.stereotype.Component;
import com.jd.sns.chat.common.dao.empdailyreport.JdEmpDailyReportDao;
import com.jd.sns.chat.common.domain.empdailyreport.EmpDailyReport;
import com.jd.sns.chat.common.domain.empdailyreport.OrgProfile;
import com.jd.sns.chat.common.domain.empdailyreport.ReportProfile;
import com.jd.sns.chat.common.domain.empdailyreport.ReportResult;
import com.jd.sns.chat.common.domain.empdailyreport.ServerProfile;
import com.jd.sns.chat.common.manager.base.BaseManager;
import com.jd.sns.chat.common.manager.empdailyreport.JdEmpDailyReportManager;
@Component("jdEmpDailyReportManager")
public class JdEmpDailyReportManagerImpl extends BaseManager implements JdEmpDailyReportManager {
private JdEmpDailyReportDao jdEmpDailyReportDao;
private static final String JD_EMP_DAILY_REPORT_CACHE_KEY="jd_emp_daily_report_cache_key";
public JdEmpDailyReportDao getJdEmpDailyReportDao() {
return jdEmpDailyReportDao;
}
public void setJdEmpDailyReportDao(JdEmpDailyReportDao jdEmpDailyReportDao) {
this.jdEmpDailyReportDao = jdEmpDailyReportDao;
}
@Override
public List<EmpDailyReport> getDataFromDb(String dateString){
List<EmpDailyReport> datas = new ArrayList<EmpDailyReport>();
EmpDailyReport emp = null;
//获取报表数据
ReportProfile reportProfile = new ReportProfile();
reportProfile.setStartTime(dateString);
reportProfile.setEndTime(this.getSpecifiedDayAfter(dateString));
List<ServerProfile> serverPins = jdEmpDailyReportDao.findAllServerPin();
List<String> workNos = jdEmpDailyReportDao.findAllWorkNo();
List<ReportResult> receptionNo = jdEmpDailyReportDao.findReceptionNo(reportProfile);
List<ReportResult> transferOutNo = jdEmpDailyReportDao.findTransferOutNo(reportProfile);
List<ReportResult> transferInNo = jdEmpDailyReportDao.findTransferInNo(reportProfile);
List<ReportResult> evaluationNo = jdEmpDailyReportDao.findEvaluationNo(reportProfile);
reportProfile.setDegree(100);
List<ReportResult> degree100 = jdEmpDailyReportDao.findDegreesNoByDegree(reportProfile);
reportProfile.setDegree(75);
List<ReportResult> degree75 = jdEmpDailyReportDao.findDegreesNoByDegree(reportProfile);
reportProfile.setDegree(50);
List<ReportResult> degree50= jdEmpDailyReportDao.findDegreesNoByDegree(reportProfile);
reportProfile.setDegree(25);
List<ReportResult> degree25= jdEmpDailyReportDao.findDegreesNoByDegree(reportProfile);
reportProfile.setDegree(0);
List<ReportResult> degree0 = jdEmpDailyReportDao.findDegreesNoByDegree(reportProfile);
List<ReportResult> consultingNo = jdEmpDailyReportDao.findConsultingNo(reportProfile);
List<OrgProfile> orgs = jdEmpDailyReportDao.findSkillGroupProfile();
for (int i=0;i<serverPins.size();i++){
String serverPin = serverPins.get(i).getPin();
emp = new EmpDailyReport();
this.getOrgGroup(serverPins, orgs, i, emp); //确定技能组
emp.setPin(serverPin); //员工pin
emp.setWorkNo(workNos.get(i)); //工号
int consulting=0;
int evaluation=0;
int score100=0;
int score75=0;
int score25=0;
int score0=0;
for(int j=0;j<receptionNo.size();j++){
if (serverPin.equals(receptionNo.get(j).getPin())){
emp.setReception(receptionNo.get(j).getCount());
break;
}
} //接待量
for(int j=0;j<transferOutNo.size();j++){
if (serverPin.equals(transferOutNo.get(j).getPin())){
emp.setTransferOut(transferOutNo.get(j).getCount());
break;
}
} //转出量
for(int j=0;j<transferInNo.size();j++){
if (serverPin.equals(transferInNo.get(j).getPin())){
emp.setTransferIn(transferInNo.get(j).getCount());
break;
}
} //转入量
for(int j=0;j<evaluationNo.size();j++){
if (serverPin.equals(evaluationNo.get(j).getPin())){
emp.setEvaluation(evaluationNo.get(j).getCount());
evaluation = emp.getEvaluation();
break;
}
} //评价总数
for(int j=0;j<consultingNo.size();j++){
if (serverPin.equals(consultingNo.get(j).getPin())){
emp.setConsulting(consultingNo.get(j).getCount());
consulting = emp.getConsulting();
break;
}
} //咨询量
for(int j=0;j<degree100.size();j++){
if (serverPin.equals(degree100.get(j).getPin())){
emp.setDegree100(degree100.get(j).getCount());
score100 = emp.getDegree100();
break;
}
} //非常满意
for(int j=0;j<degree75.size();j++){
if (serverPin.equals(degree75.get(j).getPin())){
emp.setDegree75(degree75.get(j).getCount());
score75 = emp.getDegree75();
break;
}
} //满意
for(int j=0;j<degree50.size();j++){
if (serverPin.equals(degree50.get(j).getPin())){
emp.setDegree50(degree50.get(j).getCount());
break;
}
} //一般
for(int j=0;j<degree25.size();j++){
if (serverPin.equals(degree25.get(j).getPin())){
emp.setDegree25(degree25.get(j).getCount());
score25 = emp.getDegree25();
break;
}
} //不满意
for(int j=0;j<degree0.size();j++){
if (serverPin.equals(degree0.get(j).getPin())){
emp.setDegree0(degree0.get(j).getCount());
score0 = emp.getDegree0();
break;
}
} //非常不满意
DecimalFormat df = new DecimalFormat("0.00");
String evaluationRate = df.format((double)((0==consulting) ? 0 : (double)evaluation/consulting));
emp.setEvaluationRate(evaluationRate); //评价率
int satisfiedSum = score100 + score75;
String satisfiedRate = df.format((0==evaluation) ? 0 : (double)satisfiedSum/evaluation);
emp.setSatisfactionRate(satisfiedRate); //满意度
int notSatisfiedSum = score25 + score0;
String notSatisfiedRate = df.format((0==evaluation) ? 0 : (double)notSatisfiedSum/evaluation);
emp.setNotSatisfactionRate(notSatisfiedRate); //不满意度
datas.add(emp);
}
return datas;
}
@Override
public String getCacheKey(String dateString) {
return JD_EMP_DAILY_REPORT_CACHE_KEY+"_"+dateString;
}
@Override @SuppressWarnings("unchecked")
public List<EmpDailyReport> getData(String dateString) {
List<EmpDailyReport> list = null;
String key = this.getCacheKey(dateString);
Object obj = null;
try {
obj = cacheUtils.get(key);
} catch (Exception e) {
log.error(e.getMessage());
}
if (obj != null){
list = (List<EmpDailyReport>) obj;
}else {
try {
list = this.getDataFromDb(dateString);
cacheUtils.set(key,10, list);
} catch (Exception e) {
log.error(e.getMessage());
}
}
return list;
}
@Override
public String getSpecifiedDayAfter(String dateString) {
Calendar c = Calendar.getInstance();
Date date2 = null;
try {
date2 = new SimpleDateFormat("yy-MM-dd").parse(dateString);
} catch (ParseException e) {
e.printStackTrace();
}
c.setTime(date2);
int day = c.get(Calendar.DATE);
c.set(Calendar.DATE, day + 1);
String dayAfter = new SimpleDateFormat("yyyy-MM-dd")
.format(c.getTime());
return dayAfter;
}
public void getOrgGroup(List<ServerProfile> serverPins,List<OrgProfile> orgs,int i,EmpDailyReport emp){
int orgId=0;
for (int j=0;j<orgs.size();j++){
if(serverPins.get(i).getId()==orgs.get(j).getOrgId()){
if(orgs.get(j).getFid()==0){
orgId = orgs.get(j).getOrgId();
}else{
orgId = orgs.get(j).getFid();
}
}
}
List<OrgProfile> headOrgList = this.getOrgs();
for (int k=0;k<headOrgList.size();k++){
orgMark.add("");
}
for(int k=0;k<headOrgList.size();k++){
if (orgId == headOrgList.get(k).getOrgId()){
orgMark.set(k, "Y");
}
}
emp.setOrgName(orgMark);
}
@Override
public List<OrgProfile> getOrgs(){
//此语句执行sql查询
List<OrgProfile> list = jdEmpDailyReportDao.findSkillGroupProfile();
for (OrgProfile org : list){
if(org.getFid()==0){
orgsList.add(org);
}
}
return orgsList;
}
}
上面是开发报表时的manager层源代码,项目运行时发现报表页面显示每次需要等待20秒才能显示统计的数据(报表数据大概是2000行,每行25列数据,报表数据按列查询)
开始以为是代码中双层for循环次数过多引起的(manager层循环次数估计在4000万次左右),但是对查找进行算法优化后还是需要20多秒,因此断定原因不是这个造成的
后来单步调试的时候尝试在for循环外循环处打断点测试,无意中发现控制台console一直在重复执行一条sql语句,就是代码中的findSkillGroupProfile方法!!
<select id="findSkillGroupProfile" resultMap="OrgProfile">
SELECT id,fid,name
FROM server_jd_org
ORDER BY fid
</select>
然后ctrl+alt+h查询该方法被调用的位置,显示刚好位于for循环第一层!!!
然后,恍然大悟,哎,由于sql查询被调用层次太多,不容易发现原因,庆幸这次调用位置是第一层循环,要是在内层循环中,岂不是。。。。。后果不堪设想。。。
亲身经历,谨以此分享
附:解决方案
将sql操作语句转移至循环外执行,其他类似情况类似处理