optimize

java110

2023-05-30 900829daab40ea7e29440c9de75592cd58a7562a

 service-report/src/main/java/com/java110/report/bmo/customReport/InspectionStaffData.java

@@ -27,44 +27,41 @@

    }

    public String execute(String paramStr, IQueryServiceDAO queryServiceDAOImpl) {
    public String execute(JSONObject params, IQueryServiceDAO queryServiceDAOImpl) {
        JSONObject paramOut = new JSONObject();
        JSONObject params = JSONObject.parseObject(paramStr);

        List sqlParams = new ArrayList();
        String sql = "select \n" +
                "t.staff_name '员工',\n" +
                "ipo.inspection_name '巡检点',\n" +
                "(select count(1) from inspection_task it \n" +
                "INNER JOIN inspection_task_detail itd on it.task_id = itd.task_id and itd.status_cd = '0'\n" +
                "where it.inspection_plan_id = ip.inspection_plan_id\n" +
                "and itd.inspection_id = ipo.inspection_id and it.plan_user_id = t.staff_id\n" +
                "and itd.act_user_id is not null\n" +
                "and it.create_time > ?\n" +
                "and it.create_time < ?\n" +
                ") '已巡检',\n" +
                "(select count(1) from inspection_task it \n" +
                "INNER JOIN inspection_task_detail itd on it.task_id = itd.task_id and itd.status_cd = '0'\n" +
                "where it.inspection_plan_id = ip.inspection_plan_id\n" +
                "and itd.inspection_id = ipo.inspection_id and it.plan_user_id = t.staff_id\n" +
                "and itd.act_user_id is null\n" +
                "and it.create_time > ?\n" +
                "and it.create_time < ?\n" +
                ") '未巡检',\n" +
                "(select itd.description from inspection_task it \n" +
                "INNER JOIN inspection_task_detail itd on it.task_id = itd.task_id and itd.status_cd = '0'\n" +
                "where it.inspection_plan_id = ip.inspection_plan_id\n" +
                "and itd.inspection_id = ipo.inspection_id and it.plan_user_id = t.staff_id\n" +
                "and itd.act_user_id is not null\n" +
                "and it.create_time > ?\n" +
                "and it.create_time < ?\n" +
                "limit 1\n" +
                ") '状态'\n" +
                "from inspection_plan_staff t\n" +
                "left join inspection_plan ip on t.inspection_plan_id = ip.inspection_plan_id and ip.status_cd = '0'\n" +
                "left join inspection_route_point_rel irpr on ip.inspection_route_id = irpr.inspection_route_id and irpr.status_cd= '0'\n" +
                "left join inspection_point ipo on irpr.inspection_id = ipo.inspection_id and ipo.status_cd = '0'\n" +
                "where 1=1 and t.status_cd = '0'\n";
        String sql = "select t.staff_name '员工',\n" +
                " ipo.inspection_name '巡检点',\n" +
                " (select count(1) from inspection_task it\n" +
                " INNER JOIN inspection_task_detail itd on it.task_id = itd.task_id and itd.status_cd = '0'\n" +
                " where it.plan_user_id = t.staff_id\n" +
                " and itd.inspection_id = ipo.inspection_id\n" +
                " and itd.act_user_id is not null\n" +
                " and it.create_time > ?\n" +
                " and it.create_time < ?\n" +
                " ) '已巡检',\n" +
                " (select count(1) from inspection_task it\n" +
                " INNER JOIN inspection_task_detail itd on it.task_id = itd.task_id and itd.status_cd = '0'\n" +
                " where it.plan_user_id = t.staff_id\n" +
                "  and itd.inspection_id = ipo.inspection_id\n" +
                " and itd.act_user_id is null\n" +
                " and it.create_time > ?\n" +
                " and it.create_time < ?\n" +
                " ) '未巡检',\n" +
                " (select itd.description from inspection_task it\n" +
                " INNER JOIN inspection_task_detail itd on it.task_id = itd.task_id and itd.status_cd = '0'\n" +
                " where  it.plan_user_id = t.staff_id\n" +
                "  and itd.inspection_id = ipo.inspection_id\n" +
                " and itd.act_user_id is not null\n" +
                " and it.create_time > ?\n" +
                " and it.create_time < ?\n" +
                " limit 1\n" +
                " ) '状态'\n" +
                " from inspection_plan_staff t\n" +
                " left join inspection_plan ip on t.inspection_plan_id = ip.inspection_plan_id and ip.status_cd = '0'\n" +
                " left join inspection_route_point_rel irpr on ip.inspection_route_id = irpr.inspection_route_id and irpr.status_cd= '0'\n" +
                " left join inspection_point ipo on irpr.inspection_id = ipo.inspection_id and ipo.status_cd = '0'\n";
        if (params.containsKey("startTime") && !StringUtils.isEmpty(params.getString("startTime"))) {
            sqlParams.add(params.get("startTime"));
            sqlParams.add(params.get("endTime"));
@@ -88,13 +85,13 @@
            sqlParams.add(params.get("communityId"));
        }

        sql += "GROUP BY t.staff_name ,ipo.inspection_name";
        sql += "GROUP BY t.staff_name ,ipo.inspection_name,t.staff_id,ipo.inspection_id";


        List datas = queryServiceDAOImpl.executeSql(sql, sqlParams.toArray());

        if (datas == null || datas.size() < 1) {
            paramOut.put("toatl",1);
            paramOut.put("total",1);
            paramOut.put("data",new JSONArray());
            return paramOut.toJSONString();
        }
@@ -106,13 +103,13 @@
            td = hasInTd(tds, dataObj);

            if (td == null) {
                td = new JSONObject();
                td = new JSONObject(true);
                td.put("员工", dataObj.get("员工"));
                tds.add(td);
            }

            td.put(dataObj.get("巡检点").toString(), dataObj.get("已巡检") + "/" + dataObj.get("未巡检"));
            td.put("状态", dataObj.get("状态"));
            td.put(dataObj.get("巡检点").toString()+"状态", dataObj.get("状态"));
        }

        paramOut.put("total",params.get("row"));