工单统计

dhz

2022-06-22 06856202544f4324e27896e8a7b2fcf1298f5c68

工单统计

 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("调查类型");

 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);