package cn.ksource.web.facade.health; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import org.springframework.stereotype.Service; import cn.ksource.beans.SC_PARTNER_CUSTOMER_INFO; import cn.ksource.beans.SC_WORKFLOW_CI_HEALTH; import cn.ksource.beans.WORKFLOW_NODE; import cn.ksource.core.dao.BaseDao; import cn.ksource.core.dao.SqlParameter; import cn.ksource.core.page.PageInfo; import cn.ksource.core.util.ConvertUtil; import cn.ksource.core.util.DateUtil; import cn.ksource.core.util.JsonUtil; import cn.ksource.core.util.StringUtil; import cn.ksource.core.util.TreeUtil; import cn.ksource.core.web.ClientUtil; import cn.ksource.core.web.SysInfoMsg; import cn.ksource.core.web.WebUtil; import cn.ksource.web.Constants; import cn.ksource.web.entity.WorkFlowSupportEntity; import cn.ksource.web.facade.incident.IncidentFacade; import cn.ksource.web.service.file.FileService; import cn.ksource.web.service.workFlowSupport.WorkFlowSupportService; import cn.ksource.web.util.DateParepareUtil; @Service @SuppressWarnings("unchecked") public class HealthFacadeImpl implements HealthFacade { @Resource private BaseDao baseDao; @Resource private WorkFlowSupportService workFlowSupportService; @Resource private IncidentFacade incidentFacade; @Resource private FileService fileService; @Override public PageInfo getHealthPlanData(PageInfo pageInfo, Map params) { Map support = getHealthPlanSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getHealthPlanCount(Map params) { Map support = getHealthPlanSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getHealthPlanSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select a.* "); sql.append(" from ci_health_plan a "); sql.append(" where 1=1 "); if (StringUtil.isNotBlank(params.get("state"))) { sql.append(" and a.state = :state "); } if (StringUtil.isNotBlank(params.get("subCusId"))) { sql.append(" and a.sub_cus_id = :subCusId "); } if (StringUtil.isNotBlank(params.get("cusId"))) { sql.append(" and a.cus_id = :cusId "); } sql.append(" order by a.create_date desc "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public Map getHealthPlanById(String id) { return baseDao.queryForMap("select * from ci_health_plan where id=:id ", new SqlParameter("id", id)); } @Override public void saveHealthPlan(Map params) { StringBuilder sql = new StringBuilder(); if (StringUtil.isBlank(params.get("id"))) {//插入 params.put("id", StringUtil.getUUID()); sql.append(" insert into ci_health_plan ( "); sql.append(" id,plan_name,sub_cus_id,sub_cus_name,cus_id,state,note "); sql.append(" ) values ( "); sql.append(" :id,:plan_name,:sub_cus_id,:sub_cus_name,:cus_id,1,:note ) "); } else {//编辑 sql.append(" update ci_health_plan set plan_name=:plan_name,sub_cus_id=:sub_cus_id,sub_cus_name=:sub_cus_name, "); sql.append(" note=:note where id=:id "); } baseDao.execute(sql.toString(), params); } @Override public PageInfo getHealthCfgData(PageInfo pageInfo, Map params) { Map support = getHealthCfgSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getHealthCfgCount(Map params) { Map support = getHealthCfgSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getHealthCfgSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select a.*,b.ciname,b.position,b.id ciId,b.lv2_id,b.lv3_id "); sql.append(" from ci_health_plan_cilist a,cmdb_ci_base b "); sql.append(" where a.ci_id=b.id and a.plan_id=:planId "); if (StringUtil.isNotBlank(params.get("state"))) { sql.append(" and a.state = :state "); } if (StringUtil.isNotBlank(params.get("subCusId"))) { sql.append(" and a.sub_cus_id = :subCusId "); } sql.append(" order by a.id desc "); support.put("sql", sql.toString()); support.put("param", params); return support; } /** * 指标分类列表数据 * * @param param * @return */ @Override public List getCommonsList(Map param) { String planId = param.get("planId"); String sql = "SELECT * FROM HEALTH_CUSTOMER_COMMONS_CATEGORY WHERE PLAN_ID=:planId ORDER BY ORDERNUM ASC"; param.put("planId", planId); List categorys = baseDao.queryForList(sql, param); if (null != categorys && categorys.size() > 0) { Map cacheMap = new HashMap(); for (Map category : categorys) { String categoryId = ConvertUtil.obj2StrBlank(category.get("ID")); List list = new ArrayList(); category.put("items", list); cacheMap.put(categoryId, category); } //查询分类下的指标项 String selectSql = "SELECT B.* FROM HEALTH_CUSTOMER_COMMONS_CATEGORY A,HEALTH_CUSTOMER_COMMONS_ITEM B WHERE A.ID = B.CATEGORY_ID AND A.PLAN_ID = :planId ORDER BY B.ORDERNUM ASC "; List items = baseDao.queryForList(selectSql, new SqlParameter("planId", planId)); if (null != items && items.size() > 0) { for (Map item : items) { String categoryId = ConvertUtil.obj2StrBlank(item.get("CATEGORY_ID")); if (cacheMap.containsKey(categoryId)) { Map category = (Map) cacheMap.get(categoryId); List zbs = (List) category.get("items"); zbs.add(item); } } } } return categorys; } /** * 添加(修改)指标分类 * * @param param */ @Override public void saveCommons(Map param) { String planId = param.get("planId"); String orderNum = param.get("orderNum"); String commonsName = param.get("category_name"); String id = StringUtil.getUUID(); StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO HEALTH_CUSTOMER_COMMONS_CATEGORY(ID,CATEGORY_NAME"); sql.append(",ORDERNUM,USING_STATE,PLAN_ID)"); sql.append(" VALUES(:id,:commonsName,:orderNum,1,:planId)"); param.put("id", id); param.put("planId", planId); param.put("orderNum", orderNum); param.put("commonsName", commonsName); baseDao.execute(sql.toString(), param); } /** * 查询修改指标分类信息 */ @Override public Map getCommons(Map param) { String id = param.get("id"); String sql = "SELECT * FROM HEALTH_CUSTOMER_COMMONS_CATEGORY WHERE ID=:id"; param.put("id", id); return baseDao.queryForMap(sql, param); } /** * 添加(修改)通用指标 * * @param param */ @Override public void saveCommonItem(Map param) { String itemName = param.get("item_name"); String ordernum = param.get("ordernum"); String categoryId = param.get("categoryId"); String note = param.get("note"); String id = StringUtil.getUUID(); StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO HEALTH_CUSTOMER_COMMONS_ITEM(ID,ITEM_NAME,CATEGORY_ID,ORDERNUM,USING_STATE,NOTE)"); sql.append(" VALUES(:id,:itemName,:categoryId,:ordernum,1,:note)"); //SqlParameter paramMap = new SqlParameter(); param.put("itemName", itemName); param.put("ordernum", ordernum); param.put("categoryId", categoryId); param.put("note", note); param.put("id", id); ; baseDao.execute(sql.toString(), param); } /** * 查询修改通用指标信息 * * @param param * @return */ @Override public Map getCommonItem(Map param) { String id = param.get("id"); String sql = "SELECT * FROM HEALTH_CUSTOMER_COMMONS_ITEM WHERE ID=:id ORDER BY ORDERNUM ASC"; param.put("id", id); return baseDao.queryForMap(sql, param); } @Override public void deleteCommons(Map param) { String id = param.get("id"); String type = param.get("type"); StringBuilder sql = new StringBuilder(); sql.append("UPDATE HEALTH_CUSTOMER_COMMONS_CATEGORY SET USING_STATE=:type "); sql.append("WHERE ID=:id"); param.put("id", id); param.put("type", type); baseDao.execute(sql.toString(), param); } @Override public void deleteCommonItem(Map param) { String id = param.get("id"); String type = param.get("type"); String sql = "UPDATE HEALTH_CUSTOMER_COMMONS_ITEM SET USING_STATE=:type WHERE ID=:id"; param.put("id", id); param.put("type", type); baseDao.execute(sql, param); } @Override public List querySelCfgList(Map params) { String sql = "SELECT * FROM CMDB_CI_BASE A WHERE A.SUB_CUS_ID=:subId AND A.ID NOT IN" + "(SELECT CI_ID FROM CI_HEALTH_PLAN_CILIST B WHERE B.PLAN_ID=:planId ) AND A.STATE=1"; return baseDao.queryForList(sql, params); } @Override public void doAddDevice(Map params) { String devices = params.get("devices"); String lv3_ids = params.get("lv3_ids"); String lv3_names = params.get("lv3_names"); String planId = params.get("planId"); String device[] = devices.split(","); String lv3_id[] = lv3_ids.split(","); String lv3_name[] = lv3_names.split(","); List paramList = new ArrayList(); for (int i = 0; i < device.length; i++) { SqlParameter parameter = new SqlParameter(); parameter.put("id", StringUtil.getUUID()); parameter.put("plan_id", planId); parameter.put("state", 1); parameter.put("ci_id", device[i]); parameter.put("lv3_cate_id", lv3_id[i]); parameter.put("lv3_cate_name", lv3_name[i]); paramList.add(parameter); } String sqlString = "INSERT INTO CI_HEALTH_PLAN_CILIST (ID,CI_ID,STATE,LV3_CATE_ID,LV3_CATE_NAME,PLAN_ID) VALUES(:id,:ci_id,:state,:lv3_cate_id,:lv3_cate_name,:plan_id)"; baseDao.executeBatch(sqlString, paramList); } @Override public Map getHealthItemList(Map params) { //设备信息 String sqlString = "SELECT * FROM CMDB_CI_BASE WHERE ID=:id"; Map deviceMap = baseDao.queryForMap(sqlString, params); //客户的指标项 String sql = "SELECT A.ID AS CI_ITEM_ID,B.*,C.STATE,C.ID AS CI_DETAIL_ID FROM CI_HEALTH_CUSTOMER_ITEM A LEFT JOIN CI_HEALTH_ITEM B ON A.ITEM_ID =B .ID " + "LEFT JOIN CI_HEALTH_CUSTOMIZE_DETAIL C ON C.CUSTOMER_ITEM_ID=A.ID AND C.CI_PATROL_ID=:ci_plan_id" + " WHERE A.CUSTOMER_ID =:cusId AND A.USING_STATE=1 " + " AND (B.BUSINESS_ID=:lv2_id OR B.BUSINESS_ID=:lv3_id)"; List itemList = baseDao.queryForList(sql, params); Map map = new HashMap(); map.put("deviceMap", deviceMap); map.put("itemList", itemList); return map; } @Override public void doeditDeviceItem(Map params) { String type = params.get("type"); String ci_detail_id = params.get("ci_detail_id"); String sql = ""; if ("1".equals(type)) { sql = "INSERT INTO CI_HEALTH_CUSTOMIZE_DETAIL (ID,CUSTOMER_ITEM_ID,CI_PATROL_ID,STATE) VALUES (:id,:ci_item_id,:ci_plan_id,1)"; params.put("id", StringUtil.getUUID()); } else { sql = "DELETE FROM CI_HEALTH_CUSTOMIZE_DETAIL WHERE CUSTOMER_ITEM_ID=:ci_item_id AND CI_PATROL_ID=:ci_plan_id"; } baseDao.execute(sql, params); } @Override public Map getHealthPlan(HttpServletRequest request) { String id = request.getParameter("planId"); String sql = "SELECT * FROM CI_HEALTH_PLAN WHERE ID=:id"; return baseDao.queryForMap(sql, new SqlParameter().addValue("id", id)); } @Override public List getPlanTimer(String cusId, String planId) { List result = new ArrayList(); if (StringUtil.notEmpty(cusId)) { String sqlString = "SELECT * FROM SC_PARTNER_CUSTOMER_INFO WHERE ID=:customer_id"; Map customerMap = baseDao.queryForMap(sqlString, new SqlParameter("customer_id", cusId)); long beginTime = Long.parseLong(customerMap.get("VALID_BEGIN_DATE") + ""); long endTime = Long.parseLong(customerMap.get("VALID_END_DATE") + ""); Map paramMap = new HashMap(); //查询所有已经生成的计划 String selectSql = "SELECT * FROM CI_HEALTH_PLAN_TIMER WHERE PLAN_ID = :planId "; paramMap.put("planId", planId); //selectSql += " ORDER BY PLAN_EXECUTION_DATE "; List plans = baseDao.queryForList(selectSql, paramMap); Map cacheMap = new HashMap(); if (null != plans && plans.size() > 0) { for (Map plan : plans) { String day = ConvertUtil.obj2StrBlank(plan.get("PLAN_EXE_DATE")); cacheMap.put(day, plan); } } //求出所有月份的集合 List months = DateUtil.getBetweenTwoDateCycleList(ConvertUtil.obj2StrBlank(beginTime), ConvertUtil.obj2StrBlank(endTime), 1, 1, "yyyyMMdd", "yyyyMM"); if (null != months && months.size() > 0) { for (int i = 0; i < months.size(); i++) { String month = months.get(i); String beginDay = month + "01"; //得到当月的最后一天 String lastDay = DateParepareUtil.lastDayMonth(month, "yyyyMM", "yyyyMMdd"); List days = DateUtil.getBetweenTwoDateCycleList(beginDay, lastDay, 2, 1, "yyyyMMdd", "yyyyMMdd"); List newList = new ArrayList(); for (String str : days) { Map map = new HashMap(); if (cacheMap.containsKey(str)) { map = cacheMap.get(str); map.put("day", str); map.put("check", 1); } else { map.put("day", str); map.put("check", 2); } //是否显示复选框 long date = ConvertUtil.obj2Long(str); if (date >= beginTime && date <= endTime) { map.put("showbox", 1); } else { map.put("showbox", 2); } //今天之前的复选框不可用 long today = DateUtil.getCurrentDate8(); if (date > today) { map.put("isUse", 1); } else { map.put("isUse", 2); } newList.add(map); } Map map = new HashMap(); map.put("month", month); map.put("days", newList); result.add(map); } } } return result; } @Override public void updatePlanLeix(HttpServletRequest request) { String id = request.getParameter("id"); String date = request.getParameter("beginExecDate"); if (StringUtil.isEmpty(date)) { date = null; } String zhouqi = request.getParameter("zhouqi"); String leix = request.getParameter("leix"); StringBuilder sql = new StringBuilder(); sql.append("UPDATE CI_HEALTH_PLAN SET CYCLE=:zhouqi,TYPE=:leix,NEXT_EXE_DATE=:date"); sql.append(" WHERE ID=:id"); SqlParameter param = new SqlParameter(); param.addValue("id", id) .addValue("date", date) .addValue("zhouqi", zhouqi) .addValue("leix", leix); baseDao.execute(sql.toString(), param); //清空未执行计划 deletePlanTimer(id); //自动添加计划 addPlanTimer(id); } @Override public void deletePlanTimer(String planId) { String deleteSql = "DELETE FROM CI_HEALTH_PLAN_TIMER WHERE PLAN_EXE_DATE > :today AND STATE = :state AND PLAN_ID=:planId"; Map paramMap = new HashMap(); paramMap.put("planId", planId); paramMap.put("today", DateUtil.getCurrentDate8()); paramMap.put("state", Constants.CI_HEALTH_PLAN_TIMER_STATE_WJX); baseDao.execute(deleteSql, paramMap); } @Override public void addPlanTimer(String planId) { String selectSql = "SELECT A.*,C.VALID_END_DATE FROM CI_HEALTH_PLAN A,SC_PARTNER_CUSTOMER_INFO C WHERE A.CUS_ID = C.ID AND A.ID = :planId AND C.VALID_END_DATE >= :today AND C.VALID_BEGIN_DATE <=:today "; Map item = baseDao.queryForMap(selectSql, new SqlParameter("planId", planId).addValue("today", DateUtil.getCurrentDate8())); if (null != item && item.size() > 0) { String leix = ConvertUtil.obj2StrBlank(item.get("TYPE")); String zhouqi = ConvertUtil.obj2StrBlank(item.get("CYCLE")); String endTime = ConvertUtil.obj2StrBlank(item.get("VALID_END_DATE")); String today = DateUtil.getToday("yyyyMMdd"); Map paramMap = new HashMap(); paramMap.put("planId", planId); paramMap.put("state", Constants.CI_HEALTH_PLAN_TIMER_STATE_YWC); //查询已经执行过的日期，生成新的提醒表时，去掉已经执行过的日期 String zxSql = "SELECT * FROM CI_HEALTH_PLAN_TIMER WHERE PLAN_ID = :planId AND STATE = :state "; List zxList = baseDao.queryForList(zxSql, paramMap); Map zxCache = new HashMap(); if (null != zxList && zxList.size() > 0) { for (Map zx : zxList) { String day = ConvertUtil.obj2StrBlank(zx.get("PLAN_EXECUTION_DATE")); zxCache.put(day, zx); } } //生成智能提醒时间表 String insertSql = "INSERT INTO CI_HEALTH_PLAN_TIMER(ID,PLAN_ID,STATE,PLAN_EXE_DATE) VALUES (:id,:planId,1,:plan_execution_date)"; if (leix.equals("1")) { String beginExecDate = ConvertUtil.obj2StrBlank(item.get("NEXT_EXE_DATE")); List paramList = new ArrayList(); List dates = DateUtil.getBetweenTwoDateCycleList(beginExecDate, endTime, 2, ConvertUtil.obj2Int(zhouqi), "yyyyMMdd", "yyyyMMdd"); if (null != dates && dates.size() > 0) { Map param = new HashMap(); param.put("planId", planId); for (String str : dates) { if (!zxCache.containsKey(str)) { SqlParameter sqlParameter = new SqlParameter(); sqlParameter.addValue("id", StringUtil.getUUID()); sqlParameter.addValue("plan_execution_date", str); sqlParameter.putAll(param); paramList.add(sqlParameter); } } baseDao.executeBatch(insertSql, paramList); } } } } @Override public void addPlanTimer(HttpServletRequest request) { String planId = request.getParameter("planId"); String date = request.getParameter("executionDate"); StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO CI_HEALTH_PLAN_TIMER(ID,PLAN_EXE_DATE,STATE,PLAN_ID)"); sql.append(" VALUES(:id,:date,1,:planId)"); SqlParameter param = new SqlParameter(); param.addValue("id", StringUtil.getUUID()) .addValue("planId", planId) .addValue("date", date); baseDao.execute(sql.toString(), param); updateHealthPlan(planId); } @Override public void deletePlanTimer(HttpServletRequest request) { String planId = request.getParameter("planId"); String date = request.getParameter("executionDate"); String sql = "DELETE FROM CI_HEALTH_PLAN_TIMER WHERE PLAN_ID=:planId AND PLAN_EXE_DATE=:date"; SqlParameter param = new SqlParameter(); param.addValue("planId", planId) .addValue("date", date); baseDao.execute(sql.toString(), param); updateHealthPlan(planId); } @Override public void deleteCi(HttpServletRequest request) { String ciId = request.getParameter("ciId"); String planId = request.getParameter("planId"); String type = request.getParameter("type"); StringBuilder sql = new StringBuilder(); sql.append("UPDATE CI_HEALTH_PLAN_CILIST SET STATE=:type WHERE ID=:ciId "); baseDao.execute(sql.toString(), new SqlParameter().addValue("ciId", ciId).addValue("type", type)); } @Override public void startHealthWorkFlow(SC_WORKFLOW_CI_HEALTH health) { //启动流程 WorkFlowSupportEntity support = new WorkFlowSupportEntity(); support.setOrderId(health.getId()); support.setNextNodeTemplateId(Constants.HEALTH_DISPATCH); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_HEALTH); String sql = "SELECT YWJL_ID USER_ID,YWJL_NAME USER_NAME,1 AS IS_ADMIN FROM SC_PARTNER_CUSTOMER_INFO WHERE ID=:customerId "; Map map = baseDao.queryForMap(sql, new SqlParameter("customerId", health.getCustomer_id())); List list = new ArrayList(); list.add(map); support.setNextUserList(list); SysInfoMsg msg = workFlowSupportService.doOrderFlow(support); } @Override public Map queryOrderCountByCate(HttpServletRequest request, String type) { String userId = WebUtil.getLoginedUserId(request); Map groupMap = WebUtil.getLoginUser(request).getGroupMap(); Map paramMap = new HashMap(); StringBuilder builder = new StringBuilder(); paramMap.put("ysc", Constants.WORKFLOW_BASE_WFSTATE_DELETE); paramMap.put("userId", userId); builder.append(" SELECT DISTINCT D.* FROM ( "); //查询处理人是我的 builder.append(" SELECT B.ID,A.FLOWSTATE,B.WFSTATE FROM WORKFLOW_NODE A,WORKFLOW_BASE B WHERE A.FLOWID = B.ID AND A.CURRENT_DEALER_ID = :userId "); if (StringUtil.notEmpty(type)) { builder.append(" AND B.BUSINESSTYPE = :type "); paramMap.put("type", type); } builder.append(")D"); builder.append(" WHERE D.WFSTATE != :ysc "); StringBuilder newBuild = new StringBuilder(); newBuild.append("SELECT COUNT(*) AS NUM,E.FLOWSTATE FROM ( "); newBuild.append(builder.toString()); newBuild.append(") E GROUP BY E.FLOWSTATE ORDER BY E.FLOWSTATE"); List list = baseDao.queryForList(newBuild.toString(), paramMap); Map resultMap = new HashMap(); for (Map m : list) { resultMap.put("state" + m.get("FLOWSTATE"), m.get("NUM")); } int totalNum = queryAllOrderCount(request, type); int orderPoolCount = myorderpoolCount(request, type); resultMap.put("totalOrder", totalNum); resultMap.put("orderPool", orderPoolCount); return resultMap; } @Override public int queryAllOrderCount(HttpServletRequest request, String type) { String userId = WebUtil.getLoginedUserId(request); //Map jsMap = WebUtil.getLoginUser(request).getRoleMap(); Map groupMap = WebUtil.getLoginUser(request).getGroupMap(); Map paramMap = new HashMap(); StringBuilder builder = new StringBuilder(); paramMap.put("ysc", Constants.WORKFLOW_BASE_WFSTATE_DELETE); paramMap.put("userId", userId); builder.append(" SELECT DISTINCT D.* FROM ( "); //查询处理人是我的 builder.append(" SELECT B.ID,B.WFSTATE FROM WORKFLOW_NODE A,WORKFLOW_BASE B WHERE A.FLOWID = B.ID AND A.CURRENT_DEALER_ID = :userId "); if (StringUtil.notEmpty(type)) { builder.append(" AND B.BUSINESSTYPE = :type "); paramMap.put("type", type); } builder.append(")D"); builder.append(" WHERE D.WFSTATE != :ysc "); StringBuilder newBuild = new StringBuilder(); newBuild.append("SELECT COUNT(*) AS NUM FROM ( "); newBuild.append(builder.toString()); newBuild.append(") E"); int count = baseDao.queryForInteger(newBuild.toString(), paramMap); System.out.println(count); return count; } /** * 查询我的工单池数量 */ @Override public int myorderpoolCount(HttpServletRequest request, String types) { /** * 查询我的工单池规则 * 如果是一二三线或者驻场工程师桌面,则直接查询工单表中 所属分组是当前工程师所在组的 * 如果是项目服务台，则直接查询工单表中所属分组是当前工程师所在组的 （同上） * 如果是客户服务台，查询我的工单池 则查询工单表中分组是 （查询该客户下所有的项目的服务台分组） * 如果是总服务台，则查询所有客户下（加盟商下所有项目的服务台分组） */ String userId = WebUtil.getLoginedUserId(request); String sql = new String(); Map paramMap = new HashMap(); paramMap.put("wfstate", Constants.WORKFLOW_BASE_WFSTATE_DELETE); sql = "SELECT COUNT(D.ID) FROM WORKFLOW_BASE D,(SELECT B.ID,B.ROLENAME FROM AC_USER_REF_ROLE A,AC_ROLE B WHERE A.JSBH = B.ID AND A.YHBH = :userId) C WHERE D.CURRENT_DEAL_ROLEIDA = C.ID AND D.WFSTATE != :wfstate AND (D.CURRENT_DEALER_ID IS NULL OR D.CURRENT_DEALER_ID = '' ) "; paramMap.put("userId", userId); if (StringUtil.notEmpty(sql)) { StringBuilder builder = new StringBuilder(sql); String status = request.getParameter("status"); if (StringUtil.notEmpty(status)) { builder.append(" AND D.WFSTATE = :status "); paramMap.put("status", status); } String orderName = request.getParameter("orderName"); if (StringUtil.notEmpty(orderName)) { builder.append(" AND D.WFNAME LIKE :orderName "); paramMap.put("orderName", "%" + orderName + "%"); } String orderCode = request.getParameter("orderCode"); if (StringUtil.notEmpty(orderCode)) { builder.append(" AND D.ORDER_CODE LIKE :orderCode"); paramMap.put("orderCode", "%" + orderCode + "%"); } String customerName = request.getParameter("customerName"); if (StringUtil.notEmpty(customerName)) { builder.append(" AND D.CUSTOMER_NAME LIKE :customerName "); paramMap.put("customerName", "%" + customerName + "%"); } if (StringUtil.notEmpty(types)) { String[] type = types.split(","); if (type.length == 1) { builder.append(" AND D.BUSINESSTYPE = :businessType"); paramMap.put("businessType", type[0]); } else { builder.append(" AND D.BUSINESSTYPE IN ("); for (int i = 0; i < type.length; i++) { builder.append(" :type" + i); builder.append(","); paramMap.put("type" + i, type[i]); } builder.deleteCharAt(builder.lastIndexOf(",")); builder.append(")"); } } int count = baseDao.queryForInteger(builder.toString(), paramMap); return count; } return 0; } @Override public int getMyHealthListCount(HttpServletRequest request) { Map supportMap = getMyHealthListSql(request); String sql = "select count(*) from ( " + supportMap.get("sql").toString() + " ) t"; return baseDao.queryForInteger(sql, (Map) supportMap.get("param")); } @Override public List getMyHealthListData(HttpServletRequest request) { Map supportMap = getMyHealthListSql(request); List result = baseDao.queryforSplitPage(request, supportMap.get("sql").toString(), (Map) supportMap.get("param")); return result; } /** * 获取我的工单 * * @param request * @return */ private Map getMyHealthListSql(HttpServletRequest request) { String userId = WebUtil.getLoginedUserId(request); String flowState = request.getParameter("flowState"); String nodeState = request.getParameter("nodeState"); String customerId = request.getParameter("customerId"); String projectId = request.getParameter("projectId"); String subCustomerId = request.getParameter("subCustomerId"); String orderCode = request.getParameter("orderCode"); Map paramMap = new HashMap(); paramMap.put("userId", userId); paramMap.put("nodeState", nodeState); paramMap.put("flowState", flowState); paramMap.put("businessType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_HEALTH); paramMap.put("orderCode", "%" + orderCode + "%"); paramMap.put("customerId", customerId); paramMap.put("projectId", projectId); paramMap.put("subCustomerId", subCustomerId); StringBuilder builder = new StringBuilder(); builder.append(" select n.*,b.WFNAME,b.CREATERNAME,b.CREATETIME,b.CURRENT_NODE_NAME, "); builder.append(" b.BUSINESSTYPE,b.CUSTOMER_NAME,b.SUB_CUSTOMER_NAME,B.WFSTATE, "); builder.append(" t.PLAN_EXE_DATE,r.PLAN_TIME,r.ORDER_CODE,r.ID ORDERID,r.CI_RUN_NOTE "); builder.append(" from SC_WORKFLOW_CI_HEALTH r "); builder.append(" inner join WORKFLOW_BASE B on r.FLOW_ID = b.ID "); builder.append(" inner join CI_HEALTH_PLAN_TIMER t on r.ID = t.FLOW_ID "); builder.append(" inner join "); builder.append(" ( "); builder.append(" select * from ( "); builder.append(" select CREATETIME NODETIME,ID NODEID,FLOWSTATE NODESTATE,FLOWID from WORKFLOW_NODE "); builder.append(" where CURRENT_DEALER_ID = :userId "); //环节状态 if (StringUtil.isNotBlank(nodeState)) { builder.append("and FLOWSTATE =:nodeState "); } builder.append(" order by FLOWSTATE asc "); builder.append(" ) t group by FLOWID "); builder.append(" ) n "); builder.append(" on b.ID = n.FLOWID "); //工单类型 builder.append(" WHERE b.BUSINESSTYPE = :businessType"); //工单状态 if (StringUtil.notEmpty(flowState)) { builder.append(" AND b.WFSTATE =:flowState "); } //客户 if (StringUtil.isNotBlank(customerId)) { builder.append(" AND b.CUSTOMER_ID = :customerId "); } //下属单位 if (StringUtil.isNotBlank(subCustomerId)) { builder.append(" AND b.SUB_CUSTOMER_ID = :subCustomerId "); } //项目 if (StringUtil.isNotBlank(projectId)) { builder.append(" AND b.PROJECT_ID = :projectId "); } //工单编号 if (StringUtil.notEmpty(orderCode)) { builder.append(" AND r.ORDER_CODE LIKE :orderCode"); } builder.append(" order by b.CREATETIME desc,t.PLAN_EXE_DATE desc "); Map supportMap = new HashMap(); supportMap.put("sql", builder.toString()); supportMap.put("param", paramMap); return supportMap; } @Override public SysInfoMsg doDispatch(SC_WORKFLOW_CI_HEALTH health, HttpServletRequest request) { String orderId = request.getParameter("orderId"); String flowId = request.getParameter("flowId"); String nodeId = request.getParameter("nodeId"); String flowNote = request.getParameter("flowNote"); String executorIds = request.getParameter("executorIds"); String executorNames = request.getParameter("executorNames"); String main_executorId = request.getParameter("main_executorId"); Map user = WebUtil.getLoginUser(request).getLoginUser(); String[] ciIdAry = request.getParameterValues("ciId"); String items = request.getParameter("items"); List itemlist = JsonUtil.json2List(items); //更新基本信息 health.setId(orderId).update(); //添加巡检设备和指标（edit by peikezhan 2016.07.14） addPatrolCi(orderId, ciIdAry, itemlist); //设定执行人 List userList = new ArrayList(); String[] executorIdAry = executorIds.split(","); String[] executorNameAry = executorNames.split(","); for (int i = 0; i < executorIdAry.length; i++) { Map tempMap = new HashMap(); tempMap.put("USER_ID", executorIdAry[i]); tempMap.put("USER_NAME", executorNameAry[i]); if (executorIdAry[i].equals(main_executorId)) { tempMap.put("IS_ADMIN", "1"); } userList.add(tempMap); } //环节流转 WorkFlowSupportEntity support = new WorkFlowSupportEntity(); support.setFlowId(flowId); support.setNodeId(nodeId); support.setNote(flowNote); support.setUserName(user.get("ZSXM").toString()); support.setUserId(user.get("ID").toString()); support.setNextNodeTemplateId(Constants.HEALTH_PATROL); support.setNextUserList(userList); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_HEALTH); SysInfoMsg msg = workFlowSupportService.doOrderFlow(support); return msg; } /** * 添加巡检设备 * * @param orderId * @param ciIdAry(现在应该为CI_HEALTH_PLAN_CILIST表的ID ADD BY peikezhan2016.7.13) */ private void addPatrolCi(String orderId, String[] ciIdAry, List itemList) { //删除巡检设备和巡检项！！！！！！！！！！！！！！！！！ baseDao.execute("delete from SC_WORKFLOW_CI_HEALTH_CIDETAIL where order_id = :orderId", new SqlParameter("orderId", orderId)); baseDao.execute("delete from SC_WORKFLOW_ITEM_HEALTH_CIDETAIL where order_id = :orderId", new SqlParameter("orderId", orderId)); if (ciIdAry != null && ciIdAry.length > 0) { ArrayList paramList = new ArrayList(); ArrayList paramList_item = new ArrayList(); for (int i = 0; i < ciIdAry.length; i++) { SqlParameter param = new SqlParameter(); String orderCiId = StringUtil.getUUID(); param.addValue("id", orderCiId) .addValue("ciId", ciIdAry[i]) .addValue("orderId", orderId); paramList.add(param); //查询设备对应巡检项 /*StringBuilder itemSql = new StringBuilder(); itemSql.append(" select i.ID ITEM_ID,i.ITEM_NAME from CI_HEALTH_CUSTOMIZE_DETAIL d "); itemSql.append(" inner join CI_HEALTH_CUSTOMER_ITEM r "); itemSql.append(" on d.CUSTOMER_ITEM_ID = r.ID "); itemSql.append(" inner join CI_HEALTH_ITEM i "); itemSql.append(" on r.ITEM_ID = i.ID "); itemSql.append(" where d.CI_PATROL_ID = :ciId "); List itemList = baseDao.queryForList(itemSql.toString(),new SqlParameter().addValue("ciId", ciIdAry[i]));*/ if (itemList != null && itemList.size() > 0) { Map map = (Map) itemList.get(i); String itemString = map.get("itemId"); if (!StringUtil.isEmpty(itemString)) { String items[] = itemString.split("-"); for (int k = 0; k < items.length; k++) { SqlParameter param_item = new SqlParameter(); param_item.addValue("id", StringUtil.getUUID()) .addValue("orderId", orderId) .addValue("orderCiId", orderCiId) .addValue("ciId", ciIdAry[i]) .addValue("itemId", items[k]); paramList_item.add(param_item); } } } } //插入设备巡检表 String sql = " insert into SC_WORKFLOW_CI_HEALTH_CIDETAIL (ID,CI_ID,ORDER_ID,DEAL_STATE) " + " values (:id,:ciId,:orderId,3)"; baseDao.executeBatch(sql, paramList); //插入设备指标项巡检表 String sql_item = " insert into SC_WORKFLOW_ITEM_HEALTH_CIDETAIL " + " (ID,ORDER_CI_ID,ORDER_ID,CI_ID,ITEM_ID,DEAL_STATE) " + " values " + " (:id,:orderCiId,:orderId,:ciId,:itemId,3) "; baseDao.executeBatch(sql_item, paramList_item); } } @Override public Map getHealthInfo(String orderId) { String sql = " select b.WFSTATE,h.*,t.PLAN_EXE_DATE from SC_WORKFLOW_CI_HEALTH h ,CI_HEALTH_PLAN_TIMER t,WORKFLOW_BASE b " + " where h.ID = t.FLOW_ID and h.FLOW_ID=b.ID and h.id = :orderId "; return baseDao.queryForMap(sql, new SqlParameter("orderId", orderId)); } @Override public List getHealthCiList(HttpServletRequest request) { String ciIds = request.getParameter("ciIds"); String[] arr = ciIds.split(","); List ciList = Arrays.asList(arr); String customerId = request.getParameter("customerId"); String subCustomerId = ConvertUtil.obj2Str(request.getParameter("subCustomerId")); String sql = " select b.*,c.NAME CATENAME from cmdb_ci_base b inner join cmdb_ci_category c on b.LV3_ID = c.ID " + " where b.STATE =1 and b.CUS_ID = :customerId and b.SUB_CUS_ID=:subCustomerId "; if (StringUtil.isNotBlank(ciIds)) { sql = sql + " and b.id not in (:ciList)"; } SqlParameter param = new SqlParameter(); param.addValue("customerId", customerId) .addValue("subCustomerId", subCustomerId) .addValue("ciList", ciList) .addValue("ciIds", ciIds); return baseDao.queryForList(sql, param); } @Override public List getPatrolCiList(String orderId) { StringBuilder sql = new StringBuilder(); sql.append(" select r.id,r.DEAL_STATE,b.ID CIID,c.NAME CATENAME,b.CINAME,b.SEARCHCODE,b.POSITION from SC_WORKFLOW_CI_HEALTH_CIDETAIL r "); sql.append(" inner join cmdb_ci_base b "); sql.append(" on r.CI_ID = b.ID "); sql.append(" inner join cmdb_ci_category c "); sql.append(" on b.LV3_ID = c.ID "); sql.append(" where r.ORDER_ID = :orderId "); return baseDao.queryForList(sql.toString(), new SqlParameter("orderId", orderId)); } @Override public List getCiPatrolItem(String orderId) { StringBuilder sql = new StringBuilder(); SqlParameter param = new SqlParameter("orderId", orderId); sql.append(" select b.LV3_ID CATEID,b.id CIID,d.id,b.CINAME text from SC_WORKFLOW_CI_HEALTH_CIDETAIL d "); sql.append(" inner join cmdb_ci_base b on d.CI_ID = b.ID "); sql.append(" where d.CI_ID = b.ID and d.ORDER_ID = :orderId "); List categoryList = baseDao.queryForList(sql.toString(), param); sql.setLength(0); sql.append(" select d.ORDER_CI_ID cateId,d.id,d.DEAL_STATE,d.USER_NAME,d.ITEM_RESULT,i.ITEM_NAME from SC_WORKFLOW_ITEM_HEALTH_CIDETAIL d "); sql.append(" inner join CI_HEALTH_ITEM i "); sql.append(" on d.ITEM_ID = i.ID "); sql.append(" where ORDER_ID = :orderId "); List dataList = baseDao.queryForList(sql.toString(), param); TreeUtil treeUtil = new TreeUtil(); List list = treeUtil.createTreeByCate(categoryList, dataList); return list; } @Override public List getPatrolIteamsList(String orderId) { StringBuilder sql = new StringBuilder(); SqlParameter param = new SqlParameter("orderId", orderId); sql.append(" select d.item_id,d.ORDER_CI_ID cateId,d.id,d.DEAL_STATE,d.USER_NAME,d.ITEM_RESULT,i.ITEM_NAME from SC_WORKFLOW_ITEM_HEALTH_CIDETAIL d "); sql.append(" inner join CI_HEALTH_ITEM i "); sql.append(" on d.ITEM_ID = i.ID "); sql.append(" where ORDER_ID = :orderId "); List list = baseDao.queryForList(sql.toString(), param); return list; } @Override public List getCusItemList(HttpServletRequest request) { String customer_id = request.getParameter("customerId"); String sqlString = "SELECT A.ITEM_ID,CASE WHEN B.P_ID IS NULL THEN B.BUSINESS_ID ELSE B.P_ID END AS cateId FROM CI_HEALTH_CUSTOMER_ITEM A LEFT JOIN CI_HEALTH_ITEM B ON A.ITEM_ID = B.ID " + "WHERE A.CUSTOMER_ID =:customer_id AND A.USING_STATE=1 "; String sql1 = "SELECT A.ITEM_ID FROM "; SqlParameter param = new SqlParameter(); param.put("customer_id", customer_id); return baseDao.queryForList(sqlString, param); } @Override public List getAllItemOfCus(HttpServletRequest request) { String subCustomerId = request.getParameter("subCustomerId"); String customer_id = request.getParameter("customerId"); String sqlString = "SELECT * FROM (SELECT C.ID,A.ITEM_ID,B.BUSINESS_ID,B.ITEM_NAME FROM CI_HEALTH_CUSTOMER_ITEM A,CI_HEALTH_ITEM B ,CMDB_CI_BASE C " + "WHERE C.sub_CUS_ID =:subCustomerId AND A.USING_STATE = 1 AND B.BUSINESS_ID=C.LV3_ID AND A.ITEM_ID = B.ID AND A.CUSTOMER_ID=:customer_id " + "UNION SELECT C.ID,A.ITEM_ID,B.BUSINESS_ID,B.ITEM_NAME FROM CI_HEALTH_CUSTOMER_ITEM A,CI_HEALTH_ITEM B ,CMDB_CI_BASE C " + "WHERE C.sub_CUS_ID =:subCustomerId AND A.USING_STATE = 1 AND B.BUSINESS_ID=C.LV2_ID AND A.ITEM_ID = B.ID AND A.CUSTOMER_ID=:customer_id " + ")D ORDER BY D.ID,D.ITEM_ID"; SqlParameter param = new SqlParameter(); param.put("subCustomerId", subCustomerId); param.put("customer_id", customer_id); return baseDao.queryForList(sqlString, param); } @Override public Map getAllItemOfDevice(Map params) { //设备信息 String sqlString = "SELECT * FROM CMDB_CI_BASE WHERE ID=:id"; Map deviceMap = baseDao.queryForMap(sqlString, params); //客户的指标项 String sql = "SELECT A.ID AS CI_ITEM_ID,B.* FROM CI_HEALTH_CUSTOMER_ITEM A LEFT JOIN CI_HEALTH_ITEM B ON A.ITEM_ID =B .ID " + //" WHERE A.CUSTOMER_ID =:cusId AND A.USING_STATE=1 " + " WHERE A.CUSTOMER_ID ='-1' AND A.USING_STATE=1 " + " AND (B.BUSINESS_ID=:lv2_id OR B.BUSINESS_ID=:lv3_id)"; params.put("lv2_id", deviceMap.get("LV2_ID") + ""); params.put("lv3_id", deviceMap.get("LV3_ID") + ""); List itemList = baseDao.queryForList(sql, params); Map map = new HashMap(); map.put("deviceMap", deviceMap); map.put("itemList", itemList); return map; } @Override public List getCommonPatrolItem(String orderId) { StringBuilder sql = new StringBuilder(); SqlParameter param = new SqlParameter(); param.addValue("orderId", orderId); //获取通用指标分类 sql.append(" select CATEGORY_ID,CATEGORY_NAME "); sql.append(" from CI_HEALTH_PATROL_COMMON_RECORD "); sql.append(" where ORDER_ID = :orderId "); sql.append(" group by CATEGORY_ID "); List categoryList = baseDao.queryForList(sql.toString(), param); //获取通用指标 sql.setLength(0); sql.append(" select ID,CATEGORY_ID,ITEM_ID,ITEM_NAME,RESULT_TYPE,NOTE "); sql.append(" from CI_HEALTH_PATROL_COMMON_RECORD "); sql.append(" where ORDER_ID = :orderId "); List dataList = baseDao.queryForList(sql.toString(), param); Map categoryMap = new HashMap(); if (categoryList != null && categoryList.size() > 0) { for (Map category : categoryList) { ArrayList children = new ArrayList(); category.put("children", children); categoryMap.put(category.get("CATEGORY_ID").toString(), category); } } //组装树 if (dataList != null && dataList.size() > 0) { for (Map data : dataList) { if (categoryMap.get(data.get("CATEGORY_ID").toString()) != null) { ((List) ((Map) categoryMap.get(data.get("CATEGORY_ID").toString())).get("children")).add(data); } } } return categoryList; } @Override public Map getPatrolItemInfo(String patrolItemId) { String sql = " select * from SC_WORKFLOW_ITEM_HEALTH_CIDETAIL where id = :patrolItemId "; return baseDao.queryForMap(sql, new SqlParameter("patrolItemId", patrolItemId)); } @Override public SysInfoMsg doSendToManager(HttpServletRequest request) { String orderId = request.getParameter("orderId"); String flowId = request.getParameter("flowId"); String nodeId = request.getParameter("nodeId"); String flowNote = request.getParameter("flowNote"); String ciRunNote = request.getParameter("ciRunNote"); String ciErrorNote = request.getParameter("ciErrorNote"); String commonJsonList = request.getParameter("commonJsonList"); Map user = WebUtil.getLoginUser(request).getLoginUser(); String updateSql = " update SC_WORKFLOW_CI_HEALTH set CI_RUN_NOTE=:ciRunNote, CI_ERROR_NOTE=:ciErrorNote " + " where ID =:orderId "; SqlParameter param = new SqlParameter().addValue("orderId", orderId) .addValue("ciRunNote", ciRunNote) .addValue("ciErrorNote", ciErrorNote); baseDao.execute(updateSql, param); //通用指标信息 if (StringUtil.isNotBlank(commonJsonList)) { saveCommontItem(commonJsonList); } //环节流转 WorkFlowSupportEntity support = new WorkFlowSupportEntity(); support.setFlowId(flowId); support.setNodeId(nodeId); support.setNote(flowNote); support.setUserName(user.get("ZSXM").toString()); support.setUserId(user.get("ID").toString()); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_HEALTH); Integer is_admin = new WORKFLOW_NODE(nodeId).getInstanceById().getIs_admin(); if (is_admin != null && is_admin == 1) { support.setNextNodeTemplateId(Constants.HEALTH_APPROVE); support.setEverDealflag(true); support.setEverNodeTemplateId(Constants.HEALTH_DISPATCH); } SysInfoMsg msg = workFlowSupportService.doOrderFlow(support); return msg; } private void saveCommontItem(String commonJsonList) { List commonList = JsonUtil.json2List(commonJsonList); List paramList = new ArrayList(); if (commonList != null && commonList.size() > 0) { for (Map common : commonList) { SqlParameter param = new SqlParameter(); param.addValue("result", common.get("result")) .addValue("commonId", common.get("commonId")) .addValue("note", common.get("note")); paramList.add(param); } } String sql = " update CI_HEALTH_PATROL_COMMON_RECORD set RESULT_TYPE=:result,NOTE=:note " + " where ID=:commonId "; baseDao.executeBatch(sql, paramList); } @Override public SysInfoMsg doEndHealthFlow(HttpServletRequest request) { String orderId = ConvertUtil.obj2StrBlank(request.getParameter("orderId")); String flowId = ConvertUtil.obj2StrBlank(request.getParameter("flowId")); String nodeId = ConvertUtil.obj2StrBlank(request.getParameter("nodeId")); String note = ConvertUtil.obj2StrBlank(request.getParameter("flowNote")); Map user = WebUtil.getLoginUser(request).getLoginUser(); String userName = user.get("ZSXM").toString(); String userId = user.get("Id").toString(); //保存概况 StringBuilder sql = getStatisSql(); List list = baseDao.queryForList(sql.toString(), new SqlParameter("orderId", orderId)); if (list != null && list.size() > 0) { List paramList = new ArrayList(); for (Map map : list) { SqlParameter param = new SqlParameter(); param.addValue("id", StringUtil.getUUID()) .addValue("orderId", orderId) .addValue("thirdid", map.get("thirdid").toString()) .addValue("totalnum", map.get("totalnum").toString()) .addValue("realnum", map.get("realnum").toString()) .addValue("normalnum", map.get("normalnum").toString()) .addValue("warnnum", map.get("warnnum").toString()) .addValue("errornum", map.get("errornum").toString()); paramList.add(param); } sql.setLength(0); sql.append(" insert into SC_WORKFLOW_CI_HEALTH_CATEGORY "); sql.append(" (ID,BUS_ID,LV3_CATE_ID,TOTAL_NUM,TRUE_NUM,NORMAL_NUM,ALERT_NUM,ERROR_NUM) "); sql.append(" values "); sql.append(" (:id,:orderId,:thirdid,:totalnum,:realnum,:normalnum,:warnnum,:errornum) "); baseDao.executeBatch(sql.toString(), paramList); } //环节流转 WorkFlowSupportEntity support = new WorkFlowSupportEntity(); support.setFlowId(flowId); support.setNodeId(nodeId); support.setNote(note); support.setUserName(userName); support.setUserId(userId); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_HEALTH); support.setFlowEndflag(true); SysInfoMsg msg = workFlowSupportService.doOrderFlow(support); return msg; } /** * 获取统计sql * * @return */ private StringBuilder getStatisSql() { StringBuilder builder = new StringBuilder(); builder.append(" "); builder.append(" select m_total.LV3_ID thirdid,IFNULL(m_total.totalnum,0) totalnum, "); builder.append(" IFNULL(m_real.realnum,0) realnum, "); builder.append(" IFNULL(m_normal.normalnum,0) normalnum, "); builder.append(" IFNULL(m_warn.warnnum,0) warnnum, "); builder.append(" IFNULL(m_error.errornum,0) errornum "); builder.append(" from "); builder.append(" ( "); builder.append(" select count(*) totalnum,b.LV3_ID from SC_WORKFLOW_CI_HEALTH_CIDETAIL d "); builder.append(" inner join cmdb_ci_base b "); builder.append(" on d.CI_ID = b.ID "); builder.append(" where d.ORDER_ID = :orderId "); builder.append(" group by b.LV3_ID "); builder.append(" ) m_total left join "); builder.append(" ( "); builder.append(" select count(*) realnum,b.LV3_ID from SC_WORKFLOW_CI_HEALTH_CIDETAIL d "); builder.append(" inner join cmdb_ci_base b "); builder.append(" on d.CI_ID = b.ID "); builder.append(" where d.ORDER_ID = :orderId "); builder.append(" and d.DEAL_STATE=1 "); builder.append(" group by b.LV3_ID "); builder.append(" ) m_real on m_total.LV3_ID = m_real.LV3_ID "); builder.append(" left join "); builder.append(" ( "); builder.append(" select count(*) normalnum,b.LV3_ID from SC_WORKFLOW_CI_HEALTH_CIDETAIL d "); builder.append(" inner join cmdb_ci_base b "); builder.append(" on d.CI_ID = b.ID "); builder.append(" where d.ORDER_ID = :orderId "); builder.append(" and d.CI_STATE = 1 "); builder.append(" group by b.LV3_ID "); builder.append(" ) m_normal on m_total.LV3_ID = m_normal.LV3_ID "); builder.append(" left JOIN "); builder.append(" ( "); builder.append(" select count(*) warnnum,b.LV3_ID from SC_WORKFLOW_CI_HEALTH_CIDETAIL d "); builder.append(" inner join cmdb_ci_base b "); builder.append(" on d.CI_ID = b.ID "); builder.append(" where d.ORDER_ID = :orderId "); builder.append(" and d.CI_STATE=2 "); builder.append(" group by b.LV3_ID "); builder.append(" ) m_warn on m_total.LV3_ID = m_warn.LV3_ID "); builder.append(" left JOIN "); builder.append(" ( "); builder.append(" select count(*) errornum,b.LV3_ID from SC_WORKFLOW_CI_HEALTH_CIDETAIL d "); builder.append(" inner join cmdb_ci_base b "); builder.append(" on d.CI_ID = b.ID "); builder.append(" where d.ORDER_ID = :orderId "); builder.append(" and d.CI_STATE=3 "); builder.append(" group by b.LV3_ID "); builder.append(" ) m_error on m_total.LV3_ID = m_error.LV3_ID "); return builder; } @Override public void doSendback(HttpServletRequest request) { String orderId = request.getParameter("orderId"); String nodeId = request.getParameter("nodeId"); String lastNodeTemplateId = request.getParameter("lastNodeTemplateId"); String note = request.getParameter("backNote"); Map user = WebUtil.getLoginUser(request).getLoginUser(); String userName = ConvertUtil.obj2StrBlank(user.get("ZSXM")); String userId = ConvertUtil.obj2StrBlank(user.get("ID")); String flowId = request.getParameter("flowId"); WorkFlowSupportEntity support = new WorkFlowSupportEntity(); support.setFlowId(flowId); support.setNodeId(nodeId); support.setUserName(userName); support.setUserId(userId); support.setNote(note); support.setEverNodeTemplateId(lastNodeTemplateId); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_HEALTH); SysInfoMsg msg = workFlowSupportService.doOrderSendback(support); } @Override public Map queryHealthNodeCount(HttpServletRequest request) { StringBuilder buffer = new StringBuilder("SELECT n.NODE_TEMPLATE_ID,COUNT(n.NODE_TEMPLATE_ID) AS num FROM WORKFLOW_NODE n, WORKFLOW_BASE b WHERE b.CURRENT_NODE_ID = n.ID and b.BUSINESSTYPE = :businessType AND b.WFSTATE = :wfstate "); buffer.append(" GROUP BY n.NODE_TEMPLATE_ID "); List list = baseDao.queryForList(buffer.toString(), new SqlParameter("businessType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_HEALTH).addValue("wfstate", Constants.WORKFLOW_BASE_WFSTATE_DOING)); Map resultMap = new HashMap(); if (null != list && list.size() > 0) { for (Map map : list) { resultMap.put(map.get("NODE_TEMPLATE_ID"), map.get("num")); } } return resultMap; } @Override public int queryJxzHealthOrderCount(HttpServletRequest request) { StringBuilder buffer = new StringBuilder("SELECT COUNT(b.ID) FROM WORKFLOW_BASE b WHERE b.BUSINESSTYPE = :businessType AND b.WFSTATE = :wfstate "); return baseDao.queryForInteger(buffer.toString(), new SqlParameter("businessType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_HEALTH).addValue("wfstate", Constants.WORKFLOW_BASE_WFSTATE_DOING)); } @Override public int queryHealthOrderCount(HttpServletRequest request) { StringBuilder builder = new StringBuilder(); String flowState = request.getParameter("flowState"); String customerId = request.getParameter("customerId"); String subCustomerId = request.getParameter("subCustomerId"); String orderCode = request.getParameter("orderCode"); Map paramMap = new HashMap(); paramMap.put("flowState", flowState); paramMap.put("customerId", customerId); paramMap.put("subCustomerId", subCustomerId); paramMap.put("orderCode", "%" + orderCode + "%"); paramMap.put("businessType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_HEALTH); String nodeTempId = request.getParameter("nodeTempId"); paramMap.put("nodeTempId", nodeTempId); builder = getAllHealthSql(request); String sql = "select count(*) from ( " + builder.toString() + " ) t"; return baseDao.queryForInteger(sql.toString(), paramMap); } @Override public List queryHealthOrderList(HttpServletRequest request) { StringBuilder builder = new StringBuilder(); String flowState = request.getParameter("flowState"); String customerId = request.getParameter("customerId"); String subCustomerId = request.getParameter("subCustomerId"); String orderCode = request.getParameter("orderCode"); Map paramMap = new HashMap(); paramMap.put("flowState", flowState); paramMap.put("customerId", customerId); paramMap.put("subCustomerId", subCustomerId); paramMap.put("orderCode", "%" + orderCode + "%"); paramMap.put("businessType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_HEALTH); String nodeTempId = request.getParameter("nodeTempId"); paramMap.put("nodeTempId", nodeTempId); builder = getAllHealthSql(request); return baseDao.queryforSplitPage(request, builder.toString(), paramMap); } /** * 服务台工单列表 * * @param request * @return */ private StringBuilder getAllHealthSql(HttpServletRequest request) { String userId = WebUtil.getLoginedUserId(request); String flowState = request.getParameter("flowState"); String nodeState = request.getParameter("nodeState"); String customerId = request.getParameter("customerId"); String subCustomerId = request.getParameter("subCustomerId"); String nodeTempId = request.getParameter("nodeTempId"); StringBuilder builder = new StringBuilder(); builder.append(" select b.ID FLOWID,b.WFNAME,b.CREATERNAME,b.CREATETIME,b.CURRENT_NODE_NAME,b.BUSINESSTYPE, "); builder.append(" t.PLAN_EXE_DATE,r.ORDER_CODE,r.ID ORDERID,r.CI_RUN_NOTE,b.CUSTOMER_NAME,b.SUB_CUSTOMER_NAME,B.WFSTATE "); builder.append(" from SC_WORKFLOW_CI_HEALTH r "); builder.append(" inner join WORKFLOW_BASE b on r.FLOW_ID = b.ID "); builder.append(" inner join CI_HEALTH_PLAN_TIMER t on r.ID = t.FLOW_ID "); builder.append(" where 1=1 "); //工单类型 builder.append(" AND b.BUSINESSTYPE = :businessType"); //工单状态 if (StringUtil.notEmpty(flowState)) { builder.append(" AND b.WFSTATE =:flowState "); } //客户 if (StringUtil.isNotBlank(customerId)) { builder.append(" AND b.CUSTOMER_ID = :customerId "); } //下属单位 if (StringUtil.isNotBlank(subCustomerId)) { builder.append(" AND b.SUB_CUSTOMER_ID = :subCustomerId "); } //工单编号 String orderCode = request.getParameter("orderCode"); if (StringUtil.notEmpty(orderCode)) { builder.append(" AND r.ORDER_CODE LIKE :orderCode"); } if (StringUtil.notEmpty(nodeTempId)) { builder.append(" AND EXISTS (SELECT n.ID FROM WORKFLOW_NODE n WHERE b.CURRENT_NODE_ID = n.ID AND n.NODE_TEMPLATE_ID = :nodeTempId ) "); } builder.append(" order by b.CREATETIME desc,t.PLAN_EXE_DATE desc "); return builder; } @Override public Map queryCusHealthPlanReport(String customerId, String subCustomerId, String mon) { String year = DateUtil.format("yyyy", mon); String mm = DateUtil.format("MM", mon); String[] time = DateParepareUtil.queryFirstLastDate(year, mm); String beginDay = time[0].replaceAll("-", ""); //得到当月的最后一天 String lastDay = time[1].replaceAll("-", ""); //查询该月份的所有指标 String sql = "SELECT DISTINCT B.ID,B.PLAN_NAME FROM CI_HEALTH_PLAN_TIMER A,CI_HEALTH_PLAN B WHERE A.PLAN_ID = B.ID AND B.STATE = 1 AND A.PLAN_EXE_DATE >= :beginTime AND A.PLAN_EXE_DATE <= :endTime AND B.SUB_CUS_ID = :subCustomerId AND B.CUS_ID = :customerId "; Map paramMap = new HashMap(); paramMap.put("customerId", customerId); paramMap.put("subCustomerId", subCustomerId); paramMap.put("beginTime", beginDay); paramMap.put("endTime", lastDay); List subList = baseDao.queryForList(sql, paramMap); List days = DateUtil.getBetweenTwoDateCycleList(beginDay, lastDay, 2, 1, "yyyyMMdd", "yyyyMMdd"); Map resultMap = new HashMap(); resultMap.put("month", mon); resultMap.put("days", days); List result = new ArrayList(); //查询该项目下所有已经生成的计划 String selectSql = "SELECT D.*, C.FLOW_ID AS FLOWID FROM(SELECT A.* FROM CI_HEALTH_PLAN_TIMER A,CI_HEALTH_PLAN B WHERE A.PLAN_ID = B.ID AND B.STATE = 1 AND A.PLAN_EXE_DATE >= :beginTime AND A.PLAN_EXE_DATE <= :endTime AND B.SUB_CUS_ID = :subCustomerId AND B.CUS_ID = :customerId )D LEFT JOIN SC_WORKFLOW_CI_HEALTH C ON C.ID=D.FLOW_ID"; List plans = baseDao.queryForList(selectSql, paramMap); Map cacheMap = new HashMap(); if (null != plans && plans.size() > 0) { for (Map plan : plans) { String plan_id = ConvertUtil.obj2StrBlank(plan.get("PLAN_ID")); String plan_execution_date = ConvertUtil.obj2StrBlank(plan.get("PLAN_EXE_DATE")); cacheMap.put(plan_id + "-" + plan_execution_date, plan); } } if (null != subList && subList.size() > 0) { for (Map plan : subList) { String planId = ConvertUtil.obj2StrBlank(plan.get("ID")); String planName = ConvertUtil.obj2StrBlank(plan.get("PLAN_NAME")); List newList = new ArrayList(); for (String str : days) { Map map = new HashMap(); String unitKey = planId + "-" + str; if (cacheMap.containsKey(unitKey)) { map = cacheMap.get(unitKey); } newList.add(map); } Map map = new HashMap(); map.put("planName", planName); map.put("msgs", newList); result.add(map); } } resultMap.put("rs", result); return resultMap; } @Override public Map queryHealthPlanData(String customerId, String month) { //查询该客户下的所有下属单位 String sql = "SELECT * FROM SC_PARTNER_CUSTOMER_SUB WHERE CUSTOMER_ID=:customerId AND DEL_FLAG=1"; List subList = baseDao.queryForList(sql, new SqlParameter().addValue("customerId", customerId)); Map currentCustomer = new HashMap(); currentCustomer.put("ID", customerId); currentCustomer.put("CUSTOMER_NAME", new SC_PARTNER_CUSTOMER_INFO(customerId).getInstanceById().getCustomer_name()); subList.add(0, currentCustomer); if (!StringUtil.notEmpty(month)) { month = DateUtil.getCurrentDate6().toString(); } String monthBeginDay = month + "01"; //查询当月的所有天数 String monthLastDay = DateParepareUtil.lastDayMonth(month, "yyyyMM", "yyyyMMdd"); List days = DateUtil.getBetweenTwoDateCycleList(monthBeginDay, monthLastDay, 2, 1, "yyyyMMdd", "yyyyMMdd"); if (subList != null && subList.size() > 0) { for (Map subMap : subList) { //查询执行计划 String selectSql = "SELECT A.* FROM CI_HEALTH_PLAN_TIMER A,CI_HEALTH_PLAN B WHERE A.PLAN_ID = B.ID AND B.STATE = 1 AND A.PLAN_EXE_DATE >= :beginTime AND A.PLAN_EXE_DATE <= :endTime AND B.SUB_CUS_ID = :subCustomerId AND B.CUS_ID = :customerId"; Map paramMap = new HashMap(); paramMap.put("customerId", customerId); paramMap.put("subCustomerId", subMap.get("ID")); paramMap.put("beginTime", monthBeginDay); paramMap.put("endTime", monthLastDay); List taskList = baseDao.queryForList(selectSql, paramMap); List isMarkList = new ArrayList(); for (String day : days) { Map markMap = new HashMap(); markMap.put("day", day); int zjh = 0; int yzx = 0; if (taskList != null && taskList.size() > 0) { List plans = new ArrayList(); for (Map map : taskList) { Map plan = new HashMap(); Integer task = 2; Integer mark = 0; String orderId = new String(); String plan_execution_date = ConvertUtil.obj2StrBlank(map.get("PLAN_EXE_DATE")); if (plan_execution_date.equals(day)) { task = 1; String state = ConvertUtil.obj2StrBlank(map.get("STATE")); if (state.equals("1")) { mark = 1; } else if (state.equals("2") || state.equals("3")) { yzx++; mark = 2; orderId = ConvertUtil.obj2StrBlank(map.get("FLOW_ID")); } zjh++; } plan.put("task", String.valueOf(task)); plan.put("mark", String.valueOf(mark)); plan.put("orderId", orderId); plans.add(plan); } markMap.put("plans", plans); } markMap.put("zjh", zjh); markMap.put("yzx", yzx); isMarkList.add(markMap); } subMap.put("isMarkList", isMarkList); } } System.out.println(subList); Map map = new HashMap(); map.put("subList", subList); map.put("days", days); return map; } @Override public List queryCustomers(HttpServletRequest request) { String customerId = request.getParameter("customerId"); //查询该客户下的所有下属单位 String sql = "SELECT * FROM SC_PARTNER_CUSTOMER_SUB WHERE CUSTOMER_ID=:customerId AND DEL_FLAG=1"; List subList = baseDao.queryForList(sql, new SqlParameter().addValue("customerId", customerId)); Map currentCustomer = new HashMap(); SC_PARTNER_CUSTOMER_INFO info = new SC_PARTNER_CUSTOMER_INFO(customerId).getInstanceById(); currentCustomer.put("ID", customerId); currentCustomer.put("CUSTOMER_NAME", info.getCustomer_name()); currentCustomer.put("CONTACT", info.getContact()); currentCustomer.put("PHONE", info.getPhone()); subList.add(0, currentCustomer); return subList; } @Override public Map getHealthReportInfo(String orderId, String flowId) { Map healthInfo = new HashMap(); SqlParameter param = new SqlParameter(); param.addValue("orderId", orderId) .addValue("flowId", flowId) .addValue("managerNodeId", Constants.HEALTH_APPROVE) .addValue("engineerNodeId", Constants.HEALTH_PATROL); StringBuilder sql = new StringBuilder(); //查询基本信息 sql.append(" select * from SC_WORKFLOW_CI_HEALTH where id=:orderId "); Map baseInfo = baseDao.queryForMap(sql.toString(), param); //查询设备信息 sql.setLength(0); sql.append(" select c.id,c.name from ( "); sql.append(" select c.PID from SC_WORKFLOW_CI_HEALTH_CATEGORY h inner join cmdb_ci_category c "); sql.append(" on h.LV3_CATE_ID = c.ID "); sql.append(" where h.BUS_ID =:orderId "); sql.append(" group by c.PID "); sql.append(" ) t inner join cmdb_ci_category c on t.pid = c.id "); List categoryList = baseDao.queryForList(sql.toString(), param); if (categoryList != null && categoryList.size() > 0) { sql.setLength(0); sql.append(" select c.PID cateId,c.name,h.TOTAL_NUM totalnum,h.TRUE_NUM realnum,(h.ALERT_NUM+h.ERROR_NUM) warnnum,truncate(((h.ALERT_NUM+h.ERROR_NUM)*100/h.TRUE_NUM),2) warnrate "); sql.append(" from SC_WORKFLOW_CI_HEALTH_CATEGORY h inner join cmdb_ci_category c "); sql.append(" on h.LV3_CATE_ID = c.ID where h.BUS_ID =:orderId "); List dataList = baseDao.queryForList(sql.toString(), param); TreeUtil treeUtil = new TreeUtil(); List cateList = treeUtil.createTreeByCate(categoryList, dataList); healthInfo.put("cateList", cateList); } List commonList = this.getCommonPatrolItem(orderId); //查询告警异常设备 sql.setLength(0); sql.append(" select b.SEARCHCODE,b.CINAME,b.POSITION,h.ORDER_NOTE from SC_WORKFLOW_CI_HEALTH_CIDETAIL h "); sql.append(" inner join cmdb_ci_base b on h.CI_ID = b.ID "); sql.append(" where (h.CI_STATE=2 or h.CI_STATE=3) and h.ORDER_ID=:orderId "); List ciList = baseDao.queryForList(sql.toString(), param); //项目经理环节信息 sql.setLength(0); sql.append(" select DEAL_NOTE,DEALTIME,CURRENT_DEALER_NAME from workflow_node "); sql.append(" where NODE_TEMPLATE_ID=:managerNodeId and FLOWID =:flowId order by CREATETIME desc LIMIT 1 "); Map approveNodeInfo = baseDao.queryForMap(sql.toString(), param); //工程师执行环节信息 sql.setLength(0); sql.append(" select b.ENDTIME,n.CREATETIME,g.SJHM,n.DEALTIME,n.CURRENT_DEALER_NAME from workflow_node n "); sql.append(" inner join gg_user g on n.CURRENT_DEALER_ID = g.ID "); sql.append(" inner join workflow_base b on b.id = n.FLOWID "); sql.append(" where n.IS_ADMIN=1 and n.NODE_TEMPLATE_ID=:engineerNodeId and n.FLOWID =:flowId order by CREATETIME desc LIMIT 1 "); Map engineerNodeInfo = baseDao.queryForMap(sql.toString(), param); //查询关联工单信息 List linkOrders = incidentFacade.queryOrders(flowId); //查询附件信息 List fileList = fileService.getFileList(flowId, 1); healthInfo.put("baseInfo", baseInfo); healthInfo.put("commonList", commonList); healthInfo.put("ciList", ciList); healthInfo.put("linkOrders", linkOrders); healthInfo.put("fileList", fileList); healthInfo.put("approveNodeInfo", approveNodeInfo); healthInfo.put("engineerNodeInfo", engineerNodeInfo); return healthInfo; } @Override public String queryNodeidByFlowId(String flowId, String userId) { String sqlString = "SELECT id FROM workflow_node WHERE FLOWID=:flowId AND CURRENT_DEALER_ID=:userId AND (FLOWSTATE=1 OR FLOWSTATE=2)"; SqlParameter parameter = new SqlParameter(); parameter.put("flowId", flowId); parameter.put("userId", userId); return baseDao.queryForString(sqlString, parameter); } @Override public List querySubCustomerHealthPlanForDay(HttpServletRequest request) { String customerId = request.getParameter("customerId"); String subCustomerId = request.getParameter("subCustomerId"); String day = request.getParameter("day"); String projectId = request.getParameter("projectId"); String selectSql = "SELECT D.*,C.FLOW_ID AS FLOWID FROM(SELECT A.*,B.PLAN_NAME,B.NOTE FROM CI_HEALTH_PLAN_TIMER A,CI_HEALTH_PLAN B WHERE A.PLAN_ID = B.ID AND B.STATE = 1 AND B.CUS_ID = :customerId AND B.SUB_CUS_ID = :subCustomerId AND A.PLAN_EXE_DATE = :day ORDER BY A.PLAN_EXE_DATE DESC) D LEFT JOIN SC_WORKFLOW_CI_HEALTH C ON C.ID= D.FLOW_ID "; Map paramMap = new HashMap(); paramMap.put("customerId", customerId); paramMap.put("subCustomerId", subCustomerId); paramMap.put("day", day); paramMap.put("projectId", projectId); List plans = baseDao.queryForList(selectSql, paramMap); return plans; } @Override public PageInfo getCiHealthOrderData(PageInfo pageInfo, Map params) { Map support = geCiHealthOrderSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getCiHealthOrderCount(Map params) { Map support = geCiHealthOrderSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map geCiHealthOrderSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" SELECT A.*,B.ORDER_CODE,B.NAME,B.STATE,B.CUSTOMER_NAME,B.CREATE_TIME,B.ID AS BUSINESS_ID "); sql.append(" FROM SC_WORKFLOW_CI_HEALTH_CIDETAIL A,SC_WORKFLOW_CI_HEALTH B WHERE A.ORDER_ID = B.ID "); sql.append(" AND A.CI_ID = :id ORDER BY START_TIME DESC "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public Map queryHealthDate(Map paramMap) { StringBuilder builder = new StringBuilder(); String queryType = ConvertUtil.obj2StrBlank(paramMap.get("queryType")); if (queryType.equals("1")) { workFlowSupportService.getMyOrderDateSql(builder, paramMap); } else { workFlowSupportService.getAllOrderDateSql(builder, paramMap); } List orderDatas = baseDao.queryForList(builder.toString(), paramMap); Map resultMap = new HashMap(); //例行维护计划 if (StringUtil.isBlank(queryType)) { builder.setLength(0); builder.append(" SELECT DATE_FORMAT(t.PLAN_EXE_DATE,'%Y-%m-%d') m_date from CI_HEALTH_PLAN_TIMER T,CI_HEALTH_PLAN I "); builder.append(" WHERE t.PLAN_ID = i.ID AND T.STATE=1 AND DATE_FORMAT(t.PLAN_EXE_DATE,'%Y%m') = :selMonth "); List planDatas = baseDao.queryForList(builder.toString(), paramMap); resultMap.put("planDatas", planDatas); } resultMap.put("orderDatas", orderDatas); resultMap.put(ClientUtil.SUCCESS, true); resultMap.put(ClientUtil.MSG, "成功"); return resultMap; } @Override public int healthPlanCount(String ciId) { String selectSql = "SELECT COUNT(C.ID) FROM CI_HEALTH_PLAN_CILIST A,CI_HEALTH_PLAN B,CI_HEALTH_PLAN_TIMER C WHERE A.PLAN_ID = B.ID AND B.ID = C.PLAN_ID AND A.CI_ID = :ciId AND A.STATE = 1 AND B.STATE = 1 ORDER BY C.PLAN_EXE_DATE DESC "; int count = baseDao.queryForInteger(selectSql, new SqlParameter("ciId", ciId)); return count; } @Override public List healthPlanData(HttpServletRequest request, String ciId) { String selectSql = "SELECT C.* FROM CI_HEALTH_PLAN_CILIST A,CI_HEALTH_PLAN B,CI_HEALTH_PLAN_TIMER C WHERE A.PLAN_ID = B.ID AND B.ID = C.PLAN_ID AND A.CI_ID = :ciId AND A.STATE = 1 AND B.STATE = 1 ORDER BY C.PLAN_EXE_DATE DESC "; List plans = baseDao.queryforSplitPage(request, selectSql, new SqlParameter("ciId", ciId)); return plans; } @Override public void doCloseWorkFlow(HttpServletRequest request) { workFlowSupportService.doCloseWrokFlow(request); } @Override public void delHealthPlan(Map param) { String sql = new String("UPDATE CI_HEALTH_PLAN SET STATE = :state WHERE ID = :id"); baseDao.execute(sql, param); } private void updateHealthPlan(String planId) { SqlParameter param = new SqlParameter("nowDate", ConvertUtil.obj2Str(DateUtil.getCurrentDate8())); param.put("planId", planId); String next_exe_date = baseDao.queryForString("select min(plan_exe_date) from ci_health_plan_timer where plan_id=:planId and plan_exe_date >:nowDate", param); if (StringUtil.notEmpty(next_exe_date)) { param.addValue("next_exe_date", next_exe_date); baseDao.execute("update ci_health_plan set next_exe_date=:next_exe_date where id=:planId", param); } } }