From 06856202544f4324e27896e8a7b2fcf1298f5c68 Mon Sep 17 00:00:00 2001
From: dhz <duhuizhe>
Date: 星期三, 22 六月 2022 16:43:50 +0800
Subject: [PATCH] 工单统计
---
src/cn/ksource/web/facade/tj/SparePartStatisFacadeImpl.java | 33 ++++++++++++++++++++++-----------
src/cn/ksource/web/facade/tj/SatisfactionStatisFacadeImpl.java | 8 ++++----
2 files changed, 26 insertions(+), 15 deletions(-)
diff --git a/src/cn/ksource/web/facade/tj/SatisfactionStatisFacadeImpl.java b/src/cn/ksource/web/facade/tj/SatisfactionStatisFacadeImpl.java
index e318652..901b895 100644
--- a/src/cn/ksource/web/facade/tj/SatisfactionStatisFacadeImpl.java
+++ b/src/cn/ksource/web/facade/tj/SatisfactionStatisFacadeImpl.java
@@ -54,7 +54,7 @@
//鏌ヨ姣忎釜鏃ユ湡涓嬪搴旂殑鍚勪笁绾ф弧鎰忓害璋冩煡椤圭殑婊℃剰搴︽暟閲�
sql.setLength(0);
//涓夌骇婊℃剰搴﹁皟鏌ラ」涓庢椂闂村杩炴帴
- sql.append(" select t.$COLUMN,GROUP_CONCAT(IFNULL(i.num,'-') order by ordernum1,ordernum2,ordernum3) total_num from ( ");
+ sql.append(" select t.$COLUMN,WM_CONCAT(IFNULL(i.num, '-')) total_num from ( ");
sql.append(" select t2.$COLUMN,t1.resultid,ordernum1,ordernum2,ordernum3 from ");
sql.append(" ( ");
sql.append(" select v3.id resultid,v1.ORDERNUM ordernum1,v2.ORDERNUM ordernum2,v3.ordernum ordernum3 from ");
@@ -100,7 +100,7 @@
}
sql.append(" group by i.$COLUMN,d.RESULTID ");
sql.append(" ) i on t.resultid = i.RESULTID and t.$COLUMN = i.$COLUMN ");
- sql.append(" group by $COLUMN ");
+ sql.append(" group by t.$COLUMN ");
Map resMap = new HashMap();
String finalSql = sql.toString();
@@ -464,13 +464,13 @@
//鏌ヨ鏁版嵁
StringBuilder builder = new StringBuilder();
builder.append(" select d.SATIS_TYPE,d.REQUEST_FG_RATE,d.SATIS_QZ,d.REQUEST_SATIS_SCORE,sum(d.ALL_COUNT) total_num,SUM(d.SATIS_COUNT) do_num,sum(d.ALL_COUNT-SATIS_COUNT) undo_num, ");
- builder.append(" truncate(avg(d.FG_RATE),2) cover_rate,truncate(avg(d.SATIS_SCORE),2) score ");
+ builder.append(" round(avg(d.FG_RATE),2) cover_rate,round(avg(d.SATIS_SCORE),2) score ");
builder.append(" from TOTLE_SATIS_INFO i ,TOTLE_SATIS_DETAIL d ");
builder.append(" where i.ID = d.BUS_ID and i.MONTH >= DATE_FORMAT(:beginDay,'%Y%m') and i.MONTH<=DATE_FORMAT(:endDay,'%Y%m') ");
if(StringUtil.notEmpty(cusId)){
builder.append(" and i.CUSTOMER_ID = :cusId ");
}
- builder.append(" group by d.SATIS_TYPE order by d.SATIS_TYPE ");
+ builder.append(" group by d.SATIS_TYPE,d.REQUEST_FG_RATE,d.SATIS_QZ,d.REQUEST_SATIS_SCORE order by d.SATIS_TYPE ");
List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
//缁勭粐琛ㄥご
lebalList.add("璋冩煡绫诲瀷");
diff --git a/src/cn/ksource/web/facade/tj/SparePartStatisFacadeImpl.java b/src/cn/ksource/web/facade/tj/SparePartStatisFacadeImpl.java
index 55dd5da..e9cddde 100644
--- a/src/cn/ksource/web/facade/tj/SparePartStatisFacadeImpl.java
+++ b/src/cn/ksource/web/facade/tj/SparePartStatisFacadeImpl.java
@@ -37,10 +37,12 @@
String where_q = "";
String column = "cmdb.lv1_id id,cmdb.lv1_name name";
+ String column1 = "cmdb.lv1_id ,cmdb.lv1_name ";
String group = " cmdb.lv1_id ";
if(StringUtil.isNotBlank(lv1Id)){
if(StringUtil.isBlank(lv2Id)){
column = "cmdb.lv2_id id,cmdb.lv2_name name,cmdb.lv1_id lv_id";
+ column1 = "cmdb.lv2_id ,cmdb.lv2_name ,cmdb.lv1_id ";
where_q = " and cmdb.LV1_ID =:lv1Id";
level = "2";
where_c = " and PID =:lv1Id ";
@@ -48,6 +50,8 @@
}else{
column = "cmdb.lv3_id id,cmdb.lv3_name name, cmdb.lv1_id lv1_id,\n" +
" cmdb.lv2_id lv2_id ";
+ column1 = "cmdb.lv3_id ,cmdb.lv3_name , cmdb.lv1_id ,\n" +
+ " cmdb.lv2_id ";
where_q = " and cmdb.LV2_ID =:lv2Id ";
level = "3";
where_c = " and PID =:lv2Id ";
@@ -57,6 +61,8 @@
if(StringUtil.isNotBlank(lv2Id)){
column = "cmdb.lv3_id id,cmdb.lv3_name name, cmdb.lv1_id lv_id,\n" +
" cmdb.lv2_id lv2_id ";
+ column1 = "cmdb.lv3_id ,cmdb.lv3_name , cmdb.lv1_id ,\n" +
+ " cmdb.lv2_id ";
where_q = " and cmdb.LV2_ID =:lv2Id ";
level = "3";
where_c = " and PID =:lv2Id ";
@@ -71,10 +77,11 @@
String realSql;
sql.append(" select count(SPARE_PART.id) num,b.lv, $column from SPARE_PART,CMDB_CI_CATEGORY cmdb,CMDB_CI_CATEGORY b");
sql.append(" where SPARE_PART.state=1 and SPARE_PART.CATE_ID = cmdb.ID $where and cmdb.state=1 and b.id=$group and b.state=1");
- sql.append(" group by $group ");
+ sql.append(" group by $group,b.lv,$groCol ");
realSql = sql.toString().replace("$column", column);
realSql = realSql.replace("$where", where_q);
realSql = realSql.replace("$group", group);
+ realSql = realSql.replace("$groCol",column1);
List<Map> queryList = baseDao.queryForList(realSql,param);
List<Map> result = new ArrayList<Map>();
Integer sum_qt=0;
@@ -258,10 +265,12 @@
map.put("slsl", slsl);
list.add(map);
}else{
- sql.append("SELECT\n" +
+ sql.append("select t.id,t.lv1_name,t.lv2_name,t.lv3_name,t.rksl,t.cksl,t.slsl from (" +
+ "SELECT\n" +
" cmdb.LV1_NAME,\n" +
" cmdb.LV2_NAME,\n" +
" cmdb.LV3_NAME,\n" +
+ " cmdb.id,\n" +
" (\n" +
" SELECT\n" +
" count(SPARE_PART.id)\n" +
@@ -354,7 +363,7 @@
sql.append(" and spare.CATE_ID=:cate_id ");
param.put("cate_id", cate_id);
}
- sql.append(" GROUP BY CATE_ID order by rksl desc");
+ sql.append(")t GROUP BY t.id,t.lv1_name,t.lv2_name,t.lv3_name,t.rksl,t.cksl,t.slsl order by t.rksl desc");
list=baseDao.queryForList(sql.toString(),param);
}
@@ -396,7 +405,7 @@
param.put("endTime", endTime+"235959");
}
- sql.append(" GROUP BY apply.PROJECT_ID order by zs desc");
+ sql.append(" GROUP BY apply.PROJECT_ID ,apply.PROJECT_NAME order by zs desc");
return baseDao.queryForList(sql.toString(),param);
}
@@ -407,8 +416,10 @@
public List getSupplierSpareTableTypeChart(String cate_id,String beginTime,String endTime,String pxtj,String pxlx){
StringBuilder sql = new StringBuilder();
Map param = new HashMap();
- sql.append("SELECT\n" +
+ sql.append("select t.SUPPLIER_id,t.SUPPLIER_name,t.rksl,t.cksl,t.slsl,t.bssl from (" +
+ "SELECT\n" +
" spare.SUPPLIER_name,\n" +
+ " spare.supplier_id,\n" +
" (\n" +
" SELECT\n" +
" count(SPARE_PART.id)\n" +
@@ -553,10 +564,10 @@
" AND SPARE_PART.SUPPLIER_id = spare.SUPPLIER_id\n" +
" ) bssl\n" +
"FROM\n" +
- " SPARE_PART spare ,SUPPLIER_INFO where spare.SUPPLIER_id=SUPPLIER_INFO.id and SUPPLIER_INFO.STATE=1\n" +
+ " SPARE_PART spare ,SUPPLIER_INFO where spare.SUPPLIER_id=SUPPLIER_INFO.id and SUPPLIER_INFO.STATE=1\n)t " +
"GROUP BY\n" +
- " spare.SUPPLIER_id ");
- sql.append(" order BY "+" "+ pxtj+" "+pxlx+"");
+ " t.SUPPLIER_id,t.SUPPLIER_name,t.rksl,t.cksl,t.slsl,t.bssl ");
+ sql.append(" order BY "+" t."+ pxtj+" "+pxlx+"");
return baseDao.queryForList(sql.toString(),param);
}
public List getSpareMainufacturerChart(HttpServletRequest request){
@@ -849,11 +860,11 @@
sqlString+=" and SPARE_PART_APPLY_ENTITY.GMT_USAGE<=:endTime";
}
if(StringUtil.isBlank(lv1Id)){
- sqlString+=" group by b.lv1_id";
+ sqlString+=" group by b.lv1_name";
}else if(StringUtil.isNotBlank(lv1Id) && StringUtil.isBlank(lv2Id)){
- sqlString+=" and b.lv1_id=:lv1Id group by b.lv2_id";
+ sqlString+=" and b.lv1_id=:lv1Id group by b.lv2_name";
}else if(StringUtil.isNotBlank(lv1Id) && StringUtil.isNotBlank(lv2Id) ){
- sqlString+=" and b.lv1_id=:lv1Id and b.lv2_id=:lv2Id group by b.lv3_id ";
+ sqlString+=" and b.lv1_id=:lv1Id and b.lv2_id=:lv2Id group by b.lv3_name ";
}
sqlString+=" order by sum desc LIMIT 0,10";
List<Map> datas = baseDao.queryForList(sqlString,param);
--
Gitblit v1.9.1