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 ++++++++++++++++++++++-----------
1 files changed, 22 insertions(+), 11 deletions(-)
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