package cn.ksource.web.facade.ciadd; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.util.*; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import cn.ksource.beans.CMDB_CI_BASE; import cn.ksource.beans.CMDB_CI_CATEGORY; import cn.ksource.beans.CMDB_MAINUFACTURER_CONTACTS; import cn.ksource.beans.GG_USER; import cn.ksource.beans.SC_WORKFLOW_CI_ADD; import cn.ksource.beans.WORKFLOW_BASE; import cn.ksource.beans.WORKFLOW_NODE; import cn.ksource.core.dao.BaseDao; import cn.ksource.core.dao.SqlParameter; import cn.ksource.core.util.ConvertUtil; import cn.ksource.core.util.DateUtil; import cn.ksource.core.util.JsonUtil; import cn.ksource.core.util.PinYinUtil; import cn.ksource.core.util.StringUtil; import cn.ksource.core.web.SysInfoMsg; import cn.ksource.core.web.TreeNode; import cn.ksource.core.web.WebUtil; import cn.ksource.core.workflow.NodeAnswerEntity; import cn.ksource.core.workflow.NodeDealEntity; import cn.ksource.core.workflow.NodeFinishEntity; import cn.ksource.core.workflow.WorkflowBusinessService; import cn.ksource.core.workflow.WorkflowCoreService; import cn.ksource.core.workflow.WorkflowDutyerEntity; import cn.ksource.web.Constants; import cn.ksource.web.entity.WorkFlowSupportEntity; import cn.ksource.web.facade.change.ChangeFacade; import cn.ksource.web.facade.dsl.DSLFacade; import cn.ksource.web.facade.incident.IncidentFacade; import cn.ksource.web.facade.servicelist.SlaProjectFacade; import cn.ksource.web.service.WorkOrderCodeService; import cn.ksource.web.service.cmdb.SequenceService; import cn.ksource.web.service.file.FileService; import cn.ksource.web.service.workFlowSupport.WorkFlowSupportService; @SuppressWarnings("unchecked") @Service("ciAddFacade") public class CiAddFacadeImpl implements CiAddFacade { @Autowired private BaseDao baseDao; @Autowired private DSLFacade dslFacade; @Resource private ChangeFacade changeFacade; @Autowired private IncidentFacade incidentFacade; @Resource private WorkFlowSupportService workFlowSupportService; @Resource private SequenceService sequenceService; @Autowired private WorkflowBusinessService workflowBusinessService; @Autowired private WorkflowCoreService workflowCoreService; @Autowired private WorkOrderCodeService workOrderCodeService; @Autowired private SlaProjectFacade slaProjectFacade; @Autowired private FileService fileService; @Override public Map queryCiAddBaseMsg(String partnerId,String orderId) { String selectSql = "SELECT A.*,N.ANSWER_TIME AS XY_TIME,B.WFSTATE FROM SC_WORKFLOW_CI_ADD A LEFT JOIN WORKFLOW_BASE B ON A.ID = B.BUSINESS_ID LEFT JOIN WORKFLOW_NODE N ON B.CURRENT_NODE_ID = N.ID WHERE A.ID = :orderId AND B.PARTNER_ID = :partnerId "; Map baseMap = baseDao.queryForMap(selectSql,new SqlParameter("orderId",orderId).addValue("partnerId", partnerId)); if(null!=baseMap && baseMap.size()>0) { //查询附件 List files = fileService.getFileList(orderId); baseMap.put("files", files); } return baseMap; } public void doFinish(HttpServletRequest request){ String orderId = request.getParameter("orderId"); String nodeId = request.getParameter("nodeId"); String note = request.getParameter("flowNote"); Map user = WebUtil.getLoginUser(request).getLoginUser(); String userName = user.get("ZSXM").toString(); String userId = user.get("Id").toString(); SC_WORKFLOW_CI_ADD ciAddOrder = new SC_WORKFLOW_CI_ADD(orderId).getInstanceById(); String flowId = ciAddOrder.getFlow_id(); WorkFlowSupportEntity support = new WorkFlowSupportEntity(); support.setFlowId(flowId); support.setNodeId(nodeId); support.setUserName(userName); support.setUserId(userId); support.setNote(note); support.setFlowEndflag(true); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_ADD); workFlowSupportService.doOrderFlow(support); //更新设备状态为正常 updateCMDB(ciAddOrder.getFlow_id()); //保存dsl操作记录 dslFacade.saveConfigDslDealRecord(flowId); //更新dsl关系 dslFacade.updateConfigDslWhenFinish(flowId); //配置关系保存到记录表 changeFacade.saveRelationWhenClose(flowId); //保存配置关系-结束工单 changeFacade.updateRelationWhenFinish(flowId); //更新日常巡检设备 /*updateDayPatrol(ciAddOrder.getFlow_id()); //更新例行巡检项 updateDutyPatrol(ciAddOrder.getFlow_id()); //更新健康检查 updateHealthPatrol(ciAddOrder.getFlow_id());*/ } @Override public Map getCiAddDetail(String orderid, String nodeid, String model, HttpServletRequest request) { Map add = baseDao.queryForMap("select * from SC_WORKFLOW_CI_ADD where id=:id", new SqlParameter("id", orderid)); Map workflow = baseDao.queryForMap("select * from WORKFLOW_BASE where id=:id", new SqlParameter("id", add.get("FLOW_ID"))); String sql = "SELECT WORKFLOW_TEMPLATE_NODE.DEALFORMURL\n" + " FROM WORKFLOW_NODE,WORKFLOW_TEMPLATE_NODE\n" + " WHERE WORKFLOW_NODE.NODE_TEMPLATE_ID = WORKFLOW_TEMPLATE_NODE.ID\n" + " AND WORKFLOW_NODE.ID = :nodeid"; Map node = baseDao.queryForMap(sql, new SqlParameter("nodeid", workflow.get("CURRENT_NODE_ID"))); if ("edit".equalsIgnoreCase(model)) { Map customerMap = baseDao.queryForMap("select * from CUSTOMER_INFO where id=:id", new SqlParameter("id", add.get("CUSTOMER_ID"))); request.getSession().setAttribute("MEMBERR_INFO", customerMap); } add.put("NODEID", nodeid); add.put("WORKFLOW", workflow); add.put("NODE", node); add.put("URGENT_DEGREE", Constants.getmapCOMMON_URGENT_DEGREE_Label(ConvertUtil.obj2Integer(add.get("URGENT_DEGREE")))); return add; } @Override public Map getFlowInfo(String wfInstanceId) { Map resultMap = new HashMap(); //节点详情 StringBuilder sql = new StringBuilder(); sql.append("select n.NODENAME,n.CURRENT_DEALER_NAME, "); sql.append("n.DEALTIME,n.DEAL_NOTE,n.DEAL_RESULT "); sql.append("from workflow_node n "); sql.append("where n.DEALTIME is not null "); sql.append("and n.FLOWID=:id "); sql.append("order by n.DEALTIME "); List list = baseDao.queryForList(sql.toString(), new SqlParameter().addValue("id", wfInstanceId)); if (list != null && list.size() > 0) { for (Map map : list) { int dealResult = map.get("DEAL_RESULT") == null ? 0 : ConvertUtil.obj2Integer(map.get("DEAL_RESULT")); map.put("DEAL_RESULT", Constants.getmapWORKFLOW_NODE_DEAL_RESULT_Label(dealResult)); map.put("DEALTIME", DateUtil.format("yyyy-MM-dd HH:mm:ss", map.get("DEALTIME"))); map.put("DEAL_NOTE", StringUtil.str2Html(ConvertUtil.obj2Str(map.get("DEAL_NOTE")))); } } resultMap.put("nodeList", list); //提交详情 String sql2 = "select CUSTOMER_NAME,APPLY_USER_NAME,APPLY_TIME,APPLY_REASON,CI_NOTE from sc_workflow_ci_add where FLOW_ID=:flow_id "; Map map = baseDao.queryForMap(sql2, new SqlParameter().addValue("flow_id", wfInstanceId)); map.put("APPLY_TIME", DateUtil.format("yyyy-MM-dd HH:mm:ss", map.get("APPLY_TIME"))); resultMap.put("start", map); return resultMap; } @Override public void saveCiAddOrder(SC_WORKFLOW_CI_ADD info, HttpServletRequest request) { //保存工单信息 info.setCustomer_name(request.getParameter("customer_id_TEXT")); info = info.setFlow_name(info.getCustomer_name() + "的设备采集申请") .setOrder_code(workOrderCodeService.getCiAddOrderCode()) .setApply_time(DateUtil.getCurrentDate14()) .setApply_user_id(WebUtil.getLoginedUserId(request)) .setApply_user_name(ConvertUtil.obj2Str(WebUtil.getLoginUser(request).getLoginUser().get("ZSXM"))) .insert(); //保存设备概况 String[] ciNames = request.getParameterValues("ci_name"); String[] ciTypes = request.getParameterValues("ci_type"); String[] ciNums = request.getParameterValues("ci_num"); String[] ciUses = request.getParameterValues("ci_use"); if (ciNames != null && ciNames.length > 0) { String sql = "insert into sc_workflow_ci_add_general(ID,BUSINESS_ID,CI_NAME,CI_TYPE,CI_NUM,CI_USE) values(:id,:business_id,:ci_name,:ci_type,:ci_num,:ci_use) "; List paramList = new ArrayList(); for (int i = 0; i < ciNames.length; i++) { SqlParameter param = new SqlParameter(); param.addValue("id", StringUtil.getUUID()); param.addValue("business_id", info.getId()); param.addValue("ci_name", ciNames[i]); param.addValue("ci_type", ciTypes[i]); param.addValue("ci_num", ciNums[i]); param.addValue("ci_use", ciUses[i]); //过滤空行 if (!StringUtil.isEmpty(StringUtil.trim(ciNames[i] + ciTypes[i] + ciNums[i] + ciUses[i]))) { paramList.add(param); } } baseDao.executeBatch(sql, paramList); } //启动采集流程 workflowBusinessService.startCIAddWorkflow(info.getId()); } @Override public void ciManagerSubmit(HttpServletRequest request) { String orderId = request.getParameter("order_id"); String dealNote = request.getParameter("deal_note"); String flowId = request.getParameter("flow_id"); String nodeId = request.getParameter("node_id"); int dealResult = ConvertUtil.obj2Int(request.getParameter("deal_result")); String backNodeTemplateId = request.getParameter("back_node"); String userId = WebUtil.getLoginedUserId(request); //提交流程 if (dealResult - Constants.WORKFLOW_NODE_DEAL_RESULT_BACK == 0) { workflowBusinessService.toBack(flowId, nodeId, userId, dealResult, dealNote, backNodeTemplateId); } else { workflowBusinessService.toNext(flowId, nodeId, userId, dealResult, dealNote); } //更新审批信息 new SC_WORKFLOW_CI_ADD().setId(orderId) .setApprove_note(dealNote) .setApprove_time(DateUtil.getCurrentDate14()) .setApprove_user_id(userId) .setApprove_user_name(ConvertUtil.obj2Str(WebUtil.getLoginUser(request).getLoginUser().get("ZSXM"))) .update(); } @Override public void ciDispatcherSubmit(HttpServletRequest request) { String orderId = request.getParameter("order_id"); String dealNote = request.getParameter("deal_note"); String flowId = request.getParameter("flow_id"); String nodeId = request.getParameter("node_id"); String[] dutyer_ids = request.getParameter("dutyer_id").split(","); String admins = request.getParameter("admins"); String urgentDegree = request.getParameter("urgent_degree"); long dispatcherRequireDate = ConvertUtil.obj2Long(request.getParameter("dispatcher_require_date")); List dutyers = new ArrayList(); GG_USER user = new GG_USER(admins).getInstanceById(); String userName = user != null ? user.getZsxm() : ""; String dutys = userName + "(主要负责人)，"; for (String dutyerid : dutyer_ids) { WorkflowDutyerEntity entity = new WorkflowDutyerEntity(Constants.WORKFLOW_TEMPLATE_NODE_DEALER_TYPE_USER,dutyerid,"", admins.equalsIgnoreCase(dutyerid) ? 1 : 0); dutyers.add(entity); user = new GG_USER(dutyerid).getInstanceById(); String name = user != null ? user.getZsxm() : ""; if (!userName.equals(name)) { dutys += name + "，"; } } //更新紧急程度及要求处理时间 if (!StringUtil.isEmpty(urgentDegree)) { new SC_WORKFLOW_CI_ADD(orderId) .setUrgent_degree(ConvertUtil.obj2Integer(urgentDegree)) .setDispatcher_require_date(dispatcherRequireDate) .update(); } String note = "安排 " + dutys +"处理，紧急状态为" + Constants.getmapCOMMON_URGENT_DEGREE_Label(ConvertUtil.obj2Integer(urgentDegree)) + "\r" + dealNote; workflowBusinessService.toNext(flowId, nodeId, WebUtil.getLoginedUserId(request), Constants.WORKFLOW_NODE_DEAL_RESULT_PASS, note, dutyers); } @Override public void ciConfigerSubmit(HttpServletRequest request) { String dealNote = request.getParameter("deal_note"); String flowId = request.getParameter("flow_id"); String nodeId = request.getParameter("node_id"); String userId = WebUtil.getLoginedUserId(request); //提交流程 workflowBusinessService.toNext(flowId, nodeId, userId, Constants.WORKFLOW_NODE_DEAL_RESULT_PASS, dealNote); //更新设备状态，新增->正常 String sql = "update cmdb_ci_base set USINGSTATE=:usingstate where FLOW_ID=:flow_id "; SqlParameter param = new SqlParameter(); param.addValue("usingstate", Constants.CMDB_CI_BASE_USINGSTATE_NORMAL); param.addValue("flow_id", flowId); baseDao.execute(sql, param); } @Override public void ciManagerConfirmSubmit(HttpServletRequest request) { String orderId = request.getParameter("order_id"); String dealNote = request.getParameter("deal_note"); String flowId = request.getParameter("flow_id"); String nodeId = request.getParameter("node_id"); int dealResult = ConvertUtil.obj2Int(request.getParameter("deal_result")); String backNodeTemplateId = request.getParameter("back_node"); String userId = WebUtil.getLoginedUserId(request); //提交流程 if (dealResult - Constants.WORKFLOW_NODE_DEAL_RESULT_BACK == 0) { workflowBusinessService.toBack(flowId, nodeId, userId, dealResult, dealNote, backNodeTemplateId); //回退时，废弃采集的设备信息 this.doScrapCi(orderId, flowId); } else { workflowBusinessService.toNext(flowId, nodeId, userId, dealResult, dealNote); } } /** * 废弃新增的设备信息 * @param orderId * @param flowId */ private void doScrapCi(String orderId, String flowId) { baseDao.execute("update cmdb_ci_base set USINGSTATE=2 where FLOW_ID=:flow_id ", new SqlParameter("flow_id", flowId)); baseDao.execute("update sc_workflow_ci_add_result set STATE=2 where BUSINESS_ID=:business_id ", new SqlParameter("business_id", orderId)); } @Override public Map getNodeConfigInfo(String nodeId) { List goBackList = workflowBusinessService.getBackTemplateNodeList(nodeId); Map map = new HashMap(); map.put("IS_BACK", goBackList.size() == 0 ? "0":"1"); map.put("BACK_NODE_LIST", goBackList); return map; } @Override public void ciAddSpotApproveSubmit(HttpServletRequest request) { String orderId = request.getParameter("order_id"); String dealNote = request.getParameter("deal_note"); String flowId = request.getParameter("flow_id"); String nodeId = request.getParameter("node_id"); String userId = WebUtil.getLoginedUserId(request); String[] dutyer_ids = request.getParameter("dutyer_id").split(","); String admins = request.getParameter("admins"); List dutyers = new ArrayList(); GG_USER user = new GG_USER(admins).getInstanceById(); String userName = user != null ? user.getZsxm() : ""; String dutys = userName + "(主要负责人)，"; for (String dutyerid : dutyer_ids) { WorkflowDutyerEntity entity = new WorkflowDutyerEntity(Constants.WORKFLOW_TEMPLATE_NODE_DEALER_TYPE_USER,dutyerid,"", admins.equalsIgnoreCase(dutyerid) ? 1 : 0); dutyers.add(entity); user = new GG_USER(dutyerid).getInstanceById(); String name = user != null ? user.getZsxm() : ""; if (!userName.equals(name)) { dutys += name + "，"; } } String note = "请 " + dutys +" 制定维保方案" + "\r" + dealNote; workflowBusinessService.toNext(flowId, nodeId, userId, Constants.WORKFLOW_NODE_DEAL_RESULT_PASS, note, dutyers); //写入设备信息到流程中 this.insertCiResult(orderId, flowId); } @Override public Map getciAddGeneralInfo(String orderId) { //申请信息 Map add = baseDao.queryForMap("select * from SC_WORKFLOW_CI_ADD where id=:id", new SqlParameter("id", orderId)); //设备概况 String sql = "select CI_NAME,CI_TYPE,CI_NUM,CI_USE from sc_workflow_ci_add_general where BUSINESS_ID=:business_id "; List ciList = baseDao.queryForList(sql, new SqlParameter().addValue("business_id", orderId)); //服务评价信息 String evaluationSql = "select A.QUALITY_EVALUE_NAME,A.ATTITUDE_EVALUE_NAME,A.SPECIALTY_EVALUE_NAME," +"A.SUGGESTION,A.INVEST_TIME,A.SCORE,A.CUSTOMER_CONTACT_NAME from customer_satis_info A,workflow_base B where A.BUSINESS_ID=b.ID and B.BUSINESS_ID = :order_id"; Map evalue = baseDao.queryForMap(evaluationSql, new SqlParameter("order_id",orderId)); evalue.put("INVEST_TIME", DateUtil.format("yyyy-MM-dd HH:mm:ss", evalue.get("INVEST_TIME"))); add.put("evalue", evalue); add.put("ciList", ciList); add.put("count", ciList.size()); return add; } @Override public void ciAddHealthSubmit(HttpServletRequest request) { String dealNote = request.getParameter("deal_note"); String flowId = request.getParameter("flow_id"); String nodeId = request.getParameter("node_id"); String userId = WebUtil.getLoginedUserId(request); //提交流程 workflowBusinessService.toNext(flowId, nodeId, userId, Constants.WORKFLOW_NODE_DEAL_RESULT_PASS, dealNote); } @Override public Map getciAddResultInfo(String orderId) { //申请信息 Map map = baseDao.queryForMap("select * from SC_WORKFLOW_CI_ADD where id=:id", new SqlParameter("id", orderId)); //采集信息 String sql = "select * from sc_workflow_ci_add_result where BUSINESS_ID=:business_id and STATE=3 "; List ciList = baseDao.queryForList(sql, new SqlParameter().addValue("business_id", orderId)); //采集信息统计 String sql2 = "select THIRD_LEVEL_ID,THIRD_LEVEL_NAME,count(1) as COUNT from sc_workflow_ci_add_result where BUSINESS_ID=:business_id and STATE=3 group by THIRD_LEVEL_ID,THIRD_LEVEL_NAME "; List ciLevel = baseDao.queryForList(sql2, new SqlParameter().addValue("business_id", orderId)); //采集人处理结果，这里用节点名称判断，会有问题 String flowId = ConvertUtil.obj2Str(map.get("FLOW_ID")); String sql3 = "select CURRENT_DEALER_NAME,DEALTIME,DEAL_NOTE from workflow_node where NODENAME like '%采集%' and FLOWID=:flowid order by DEALTIME"; List geterList = baseDao.queryForList(sql3, new SqlParameter().addValue("flowid", flowId)); map.put("ciList", ciList); map.put("ciLevel", ciLevel); map.put("geterList", geterList); return map; } public List getCIAddList(String flowid){ //查询设备 String sql = "SELECT b.*,c.NAME as category_name,c.TABLE_NAME FROM cmdb_ci_base b,cmdb_ci_category c " + "WHERE b.LEIX=1 and b.THELEVELID=c.ID and b.ID in (select CI_ID from workflow_user_cmdb where WF_ID=:wf_id) " + "ORDER BY c.ID,b.LASTUPDATETIME "; List list = baseDao.queryForList(sql,new SqlParameter("wf_id",flowid)); return list; } public TreeNode getCiCategoryList(String pid){ String sql = "select * from CMDB_CI_CATEGORY where STATE=1 and cate_type=1 "; if (StringUtils.isBlank(pid) || pid.equalsIgnoreCase("1")) { sql += " and JB=1 "; } else { sql+= " and PID=:PID "; } sql += " order by SERIAL asc"; List list = baseDao.queryForList(sql,new SqlParameter("PID",pid)); TreeNode root = new TreeNode("1","配置分类树"); for (Map map : list) { TreeNode node = new TreeNode(map.get("ID").toString(),map.get("NAME").toString()); if (!map.get("JB").toString().equalsIgnoreCase("3")) { node.setIsOpen(false); } node.setAttributes(map); root.addChild(node); } return root; } /** * 向流程中插入设备信息 * @param orderId * @param flowId */ private void insertCiResult(String orderId, String flowId) { StringBuilder sql = new StringBuilder(); sql.append("select cb.Id,cb.CINAME,cb.SEACHCODE,cb.MEMBERCODE,cb.THIRDLEVELID,cb.POSITION,cc.NAME,cb.USINGSTATE "); sql.append("from cmdb_ci_base cb "); sql.append("left join cmdb_ci_category cc on cc.ID=cb.THELEVELID "); sql.append("where cb.FLOW_ID=:flow_id "); sql.append("and cb.USINGSTATE=3 "); sql.append("and cb.LEIX=1 "); List ciList = baseDao.queryForList(sql.toString(), new SqlParameter("flow_id", flowId)); if (ciList != null && ciList.size() > 0) { //删除之前的设备记录 baseDao.execute("delete from SC_WORKFLOW_CI_ADD_RESULT where BUSINESS_ID=:business_id ", new SqlParameter("business_id", orderId)); List paramList = new ArrayList(); for (Map map : ciList) { SqlParameter param = new SqlParameter(); param.addValue("id", StringUtil.getUUID()); param.addValue("business_id", orderId); param.addValue("ci_id", map.get("Id")); param.addValue("ci_search_code", map.get("SEACHCODE")); param.addValue("ci_name", map.get("CINAME")); param.addValue("ci_customer_code", map.get("MEMBERCODE")); param.addValue("ci_position", map.get("POSITION")); param.addValue("third_level_id", map.get("THIRDLEVELID")); param.addValue("third_level_name", map.get("NAME")); param.addValue("state", map.get("USINGSTATE")); paramList.add(param); } StringBuilder isql = new StringBuilder(); isql.append("insert into SC_WORKFLOW_CI_ADD_RESULT (ID,BUSINESS_ID,CI_ID,CI_SEARCH_CODE,CI_NAME, "); isql.append(" CI_CUSTOMER_CODE,CI_POSITION,THIRD_LEVEL_ID,THIRD_LEVEL_NAME,STATE) "); isql.append(" values (:id,:business_id,:ci_id,:ci_search_code,:ci_name, "); isql.append(" :ci_customer_code,:ci_position,:third_level_id,:third_level_name,:state) "); baseDao.executeBatch(isql.toString(), paramList); } } @Override public List getCiList(String orderId) { StringBuilder sql = new StringBuilder(); sql.append("select distinct r.CI_ID,r.CI_NAME,r.THIRD_LEVEL_ID,r.THIRD_LEVEL_NAME,r.CI_SEARCH_CODE,r.CI_POSITION, "); sql.append("p.ci_general_id as PATROL_CI_ID,h.CI_ID as HEALTH_CI_ID,rc.CI_ID as REMIND_CI_ID "); sql.append("from sc_workflow_ci_add_result r "); sql.append("left join ci_daily_patrol_customize_detail p on p.ci_general_id=r.CI_ID "); sql.append("left join ci_health_customize_detail h on h.CI_ID=r.CI_ID "); sql.append("left join ci_remind_customize_detail rc on rc.CI_ID=r.CI_ID "); sql.append("where r.BUSINESS_ID=:business_id "); List ciList = baseDao.queryForList(sql.toString(), new SqlParameter().addValue("business_id", orderId)); for (Map map : ciList) { String patrol = ConvertUtil.obj2Str(map.get("PATROL_CI_ID")); String health = ConvertUtil.obj2Str(map.get("HEALTH_CI_ID")); String remind = ConvertUtil.obj2Str(map.get("REMIND_CI_ID")); //判断是否已制定维保方案 if (StringUtil.isEmpty(patrol) && StringUtil.isEmpty(health) && StringUtil.isEmpty(remind) ) { map.put("WB_STATE", "未制定"); } else { map.put("WB_STATE", "已制定"); } } return ciList; } @Override public Map getCiWbDetail(String ciId) { //驻场巡检 StringBuilder sql = new StringBuilder(); sql.append("select i.* "); sql.append("from ci_daily_patrol_customize_detail d , "); sql.append(" ci_daily_patrol_item i "); sql.append("where d.CI_ID=:ci_id and i.ID=d.ITEM_ID and i.STATE =1 "); sql.append("order by i.ORDER_NUM "); List patrolList = baseDao.queryForList(sql.toString(), new SqlParameter("ci_id", ciId)); //智能提醒 sql = new StringBuilder(); sql.append("select i.* "); sql.append("from ci_remind_customize_detail d, "); sql.append(" ci_remind_item i "); sql.append("where d.CI_ID=:ci_id and i.ID=d.ITEM_ID and i.STATE =1 "); sql.append("order by i.ORDER_NUM "); List remindList = baseDao.queryForList(sql.toString(), new SqlParameter("ci_id", ciId)); //健康检查 sql = new StringBuilder(); sql.append("select i.* "); sql.append("from ci_health_customize_detail d, "); sql.append(" ci_health_item i "); sql.append("where d.CI_ID=:ci_id and i.ID=d.ITEM_ID and i.STATE =1 "); sql.append("order by i.ORDER_NUM "); List healthList = baseDao.queryForList(sql.toString(), new SqlParameter("ci_id", ciId)); Map map = baseDao.queryForMap("select * from cmdb_ci_base where ID=:id", new SqlParameter("id", ciId)); map.put("patrolList", patrolList); map.put("remindList", remindList); map.put("healthList", healthList); return map; } @Override public Map getCiAddHealthDetail(String orderId) { //驻场巡检 StringBuilder sql = new StringBuilder(); sql.append("select i.*,d.CI_ID as CIID "); sql.append("from ci_daily_patrol_customize_detail d "); sql.append("left join ci_daily_patrol_item i on i.ID=d.ITEM_ID "); sql.append("order by i.ORDER_NUM "); List patrolList = baseDao.queryForList(sql.toString()); //驻场巡检数量 sql = new StringBuilder(); sql.append("select r.CI_ID,r.CI_SEARCH_CODE,r.CI_NAME,r.CI_CUSTOMER_CODE,r.CI_POSITION,count(d.CI_ID) as COUNT "); sql.append("from ci_daily_patrol_customize_detail d "); sql.append("left join sc_workflow_ci_add_result r on r.CI_ID=d.CI_ID "); sql.append("where r.BUSINESS_ID=:business_id "); sql.append("group by r.CI_ID,r.CI_SEARCH_CODE,r.CI_NAME,r.CI_SEARCH_CODE,r.CI_POSITION "); List patrolCountList = baseDao.queryForList(sql.toString(), new SqlParameter("business_id", orderId)); //智能提醒 sql = new StringBuilder(); sql.append("select i.*,d.CI_ID as CIID "); sql.append("from ci_remind_customize_detail d "); sql.append("left join ci_remind_item i on i.ID=d.ITEM_ID "); sql.append("order by i.ORDER_NUM "); List remindList = baseDao.queryForList(sql.toString()); //智能提醒数量 sql = new StringBuilder(); sql.append("select r.CI_ID,r.CI_SEARCH_CODE,r.CI_NAME,r.CI_CUSTOMER_CODE,r.CI_POSITION,count(d.CI_ID) as COUNT "); sql.append("from ci_remind_customize_detail d "); sql.append("left join sc_workflow_ci_add_result r on r.CI_ID=d.CI_ID "); sql.append("where r.BUSINESS_ID=:business_id "); sql.append("group by r.CI_ID,r.CI_SEARCH_CODE,r.CI_NAME,r.CI_SEARCH_CODE,r.CI_POSITION "); List remindCountList = baseDao.queryForList(sql.toString(), new SqlParameter("business_id", orderId)); //健康检查 sql = new StringBuilder(); sql.append("select i.*,d.CI_ID as CIID "); sql.append("from ci_health_customize_detail d "); sql.append("left join ci_health_item i on i.ID=d.ITEM_ID "); sql.append("order by i.ORDER_NUM "); List healthList = baseDao.queryForList(sql.toString()); //健康检查数量 sql = new StringBuilder(); sql.append("select r.CI_ID,r.CI_SEARCH_CODE,r.CI_NAME,r.CI_CUSTOMER_CODE,r.CI_POSITION,count(d.CI_ID) as COUNT "); sql.append("from ci_health_customize_detail d "); sql.append("left join sc_workflow_ci_add_result r on r.CI_ID=d.CI_ID "); sql.append("where r.BUSINESS_ID=:business_id "); sql.append("group by r.CI_ID,r.CI_SEARCH_CODE,r.CI_NAME,r.CI_SEARCH_CODE,r.CI_POSITION "); List healthCountList = baseDao.queryForList(sql.toString(), new SqlParameter("business_id", orderId)); Map map = new HashMap(); map.put("patrolList", patrolList); map.put("remindList", remindList); map.put("healthList", healthList); map.put("patrolCountList", patrolCountList); map.put("remindCountList", remindCountList); map.put("healthCountList", healthCountList); return map; } @Override public void ciAddSpotSubmit(HttpServletRequest request) { String orderId = request.getParameter("order_id"); String dealNote = request.getParameter("deal_note"); String flowId = request.getParameter("flow_id"); String nodeId = request.getParameter("node_id"); String userId = WebUtil.getLoginedUserId(request); workflowBusinessService.toNext(flowId, nodeId, userId, Constants.WORKFLOW_NODE_DEAL_RESULT_PASS, dealNote); //写入设备信息到流程中 this.insertCiResult(orderId, flowId); } @Override public List getCiCategoryTree(String pid,String partnerId) { String sql = " select id,pid,name,lv jb from CMDB_CI_CATEGORY where state=1 and cate_type=1 "; SqlParameter param = new SqlParameter(); param.addValue("partnerId", partnerId); if(StringUtil.isNotBlank(pid)){ sql += " and PID=:pid "; param.addValue("pid", pid); }else{ sql += " and lv=1 "; } sql += " AND TYPE = 1 order by STATE,lv,SERIAL "; List list = baseDao.queryForList(sql,param); for(Map map:list){ //if(hasCiCategoryChildren(map.get("id").toString())) map.put("isParent", true); } return list; } public SysInfoMsg doApply(HttpServletRequest request,SC_WORKFLOW_CI_ADD info){ Map user = WebUtil.getLoginUser(request).getLoginUser(); String userName = user.get("ZSXM").toString(); String userId = user.get("Id").toString(); List generalList = new ArrayList(); String generalJson = request.getParameter("generalJson"); generalList = JsonUtil.json2List(generalJson); String nodeId = request.getParameter("nodeId"); String orderId = request.getParameter("orderId"); if(StringUtil.isNotBlank(orderId)){ info.setId(orderId); }else{ info.setApply_user_id(ConvertUtil.obj2StrBlank(user.get("ID"))); info.setApply_user_name(ConvertUtil.obj2StrBlank(user.get("ZSXM"))); info.setApply_time(DateUtil.getCurrentDate14()); String orderCode = workOrderCodeService.getCiAddOrderCode(); info.setOrder_code(orderCode); } //保存采集工单数据 info.insertOrUpdate(); //保存采集设备信息 if(generalList!=null&&generalList.size()>0){ saveCiGeneralInfo(generalList,info.getId()); } //发送到审批环节 WorkFlowSupportEntity support = new WorkFlowSupportEntity(); if(StringUtil.isNotBlank(orderId)){ String flowId = new SC_WORKFLOW_CI_ADD(orderId).getInstanceById().getFlow_id(); support.setFlowId(flowId); } support.setOrderId(info.getId()); support.setNodeId(nodeId); support.setNextNodeTemplateId(Constants.CIADD_APPROVE); support.setUserName(userName); support.setUserId(userId); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_ADD); //获取项目经理 //List userList = getProjectManager(info.getProject_id()); List userList = new ArrayList(); Map nodeUser = new HashMap(); nodeUser.put("IS_ADMIN", "1"); nodeUser.put("USER_ID", request.getParameter("userId")); nodeUser.put("USER_NAME", request.getParameter("userName")); userList.add(nodeUser); support.setNextUserList(userList); SysInfoMsg resMessage = workFlowSupportService.doOrderFlow(support); return resMessage; } //保存设备概况 private void saveCiGeneralInfo(List generalList,String orderId){ //保存采集设备信息 SqlParameter param = new SqlParameter(); param.addValue("business_id", orderId); baseDao.execute("delete from SC_WORKFLOW_CI_ADD_GENERAL where BUSINESS_ID=:business_id",param); List list = new ArrayList(); StringBuilder sql = new StringBuilder(); sql.append("insert into SC_WORKFLOW_CI_ADD_GENERAL (ID,BUSINESS_ID,THIRD_LEVEL_ID,THIRD_LEVEL_NAME,NOTE,CI_NUM) "); sql.append("values (:id,:business_id,:third_level_id,:third_level_name,:note,:ci_num)"); for(Map general:generalList){ SqlParameter generalParam = new SqlParameter(); general.put("id",StringUtil.getUUID()); general.put("business_id", orderId); generalParam.setSource(general); list.add(generalParam); } baseDao.executeBatch(sql.toString(), list); } @Override public SysInfoMsg doApprove (HttpServletRequest request){ String fzr_id = request.getParameter("fzr_id"); String requireDate = request.getParameter("dispatcher_require_date"); String nodeId = request.getParameter("nodeId"); String note = request.getParameter("note"); String orderId = request.getParameter("orderId"); String flowId = request.getParameter("flowId"); Map user = WebUtil.getLoginUser(request).getLoginUser(); String userName = user.get("ZSXM").toString(); String userId = user.get("Id").toString(); //设定其他执行人 List userList = getExecutor(orderId); if(userList.size()>0){ for(Map userMap:userList){ if(userMap.get("USER_ID").toString().equals(fzr_id)){ userMap.put("IS_ADMIN", "1"); } } } String sql = " UPDATE SC_WORKFLOW_CI_ADD SET DISPATCHER_REQUIRE_DATE=:requireDate WHERE ID=:orderId "; baseDao.execute(sql, new SqlParameter().addValue("requireDate", requireDate) .addValue("orderId", orderId)); //发送到审批环节 WorkFlowSupportEntity support = new WorkFlowSupportEntity(); support.setFlowId(flowId); support.setNodeId(nodeId); support.setNote(note); support.setUserName(userName); support.setUserId(userId); support.setNextNodeTemplateId(Constants.CIADD_COLLECTION); support.setNextUserList(userList); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_ADD); SysInfoMsg msg = workFlowSupportService.doOrderFlow(support); return msg; } /* * 获取执行人列表 */ public List getExecutor(String orderId){ String sql = "select DISTINCT u.ID USER_ID,u.ZSXM USER_NAME,0 as ISADMIN from sc_workflow_ci_add_general g " + "INNER JOIN SC_COLLECTION_PERSON p on g.ID=p.CI_GENERAL_ID " + "inner join gg_user u ON u.ID = p.USER_ID " + "where g.BUSINESS_ID = :orderId "; SqlParameter parameter = new SqlParameter(); parameter.addValue("orderId", orderId); return baseDao.queryForList(sql,parameter); } //设备采集 public SysInfoMsg doCollection(HttpServletRequest request){ String orderId = ConvertUtil.obj2StrBlank(request.getParameter("orderId")); Map user = WebUtil.getLoginUser(request).getLoginUser(); String userName = user.get("ZSXM").toString(); String userId = user.get("Id").toString(); String dealId = request.getParameter("dealId"); String dealName = request.getParameter("dealName"); String nodeId = ConvertUtil.obj2StrBlank(request.getParameter("nodeId")); String note = ConvertUtil.obj2StrBlank(request.getParameter("bz")); SC_WORKFLOW_CI_ADD ciAddOrder = new SC_WORKFLOW_CI_ADD(orderId).getInstanceById(); String flowId = ciAddOrder.getFlow_id(); //获取项目经理 WorkFlowSupportEntity support = new WorkFlowSupportEntity(); support.setFlowId(flowId); support.setNote(note); support.setNodeId(nodeId); support.setUserName(userName); support.setUserId(userId); //List userList = getProjectManager(ciAddOrder.getProject_id()); List userList = new ArrayList(); Map dealer = new HashMap(); dealer.put("USER_ID",dealId); dealer.put("USER_NAME",dealName); dealer.put("IS_ADMIN","1"); userList.add(dealer); support.setNextUserList(userList); support.setNextNodeTemplateId(request.getParameter("nextTemplateId")); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_ADD); SysInfoMsg msg = workFlowSupportService.doOrderFlow(support); return msg; } //获取审批人 public Map getApproveUser(String nodeId){ String sql = "select CURRENT_DEALER_ID,CURRENT_DEALER_NAME from WORKFLOW_NODE where FLOWID = ("+ "select FLOWID from WORKFLOW_NODE where id = :nodeId"+ ") and NODE_TEMPLATE_ID='" + Constants.CIADD_APPROVE +"'"; SqlParameter parameter = new SqlParameter(); parameter.addValue("nodeId", nodeId); return baseDao.queryForMap(sql,parameter); } @Override public Map getCiAddInfo(Map paramMap) { String orderId = paramMap.get("orderId").toString(); SC_WORKFLOW_CI_ADD info = new SC_WORKFLOW_CI_ADD(orderId).getInstanceById(); String applyReason = StringUtil.str2Html(ConvertUtil.obj2StrBlank(info.getApply_reason())); info.setApply_reason(applyReason); List ciList = getGeneralList(orderId); Map ciParamMap = new HashMap(); for(Map generalMap:ciList){ ciParamMap.clear(); ciParamMap.put("folwId", ConvertUtil.obj2StrBlank(info.getFlow_id())); ciParamMap.put("thirdlevelId", ConvertUtil.obj2StrBlank(generalMap.get("THIRD_LEVEL_ID").toString())); List ciInfoList = getCiInfoByFlowId(ciParamMap); if(ciInfoList!=null){ generalMap.put("ciInfoList", ciInfoList); } } Map map = new HashMap(); map.put("info", info); map.put("ciList", ciList); return map; } public List getCiInfoByFlowId(Map paramMap) { String folwId = paramMap.get("folwId").toString(); String thirdlevelId = paramMap.get("thirdlevelId").toString(); if(StringUtil.isNotBlank(folwId)){ StringBuilder sql = new StringBuilder(); sql.append("select * from CMDB_CI_BASE where FLOW_ID=:folwId and THIRDLEVELID=:thirdlevelId ORDER BY (MEMBERCODE+0) "); SqlParameter param = new SqlParameter(); param.addValue("folwId", folwId); param.addValue("thirdlevelId", thirdlevelId); List ciList = baseDao.queryForList(sql.toString(),param); return ciList; }else{ return null; } } @Override public List getUsers(String projectId) { SqlParameter param = new SqlParameter(); param.put("projectId", projectId); StringBuilder sql = new StringBuilder(); List cateList = new ArrayList(); List userList = new ArrayList(); sql.setLength(0); sql.append(" select g.GROUP_TYPE,g.GROUP_NAME from "); sql.append(" ( "); sql.append(" select GROUP_ID,USER_ID,USER_NAME from SC_SERVICER_GROUP_USER "); sql.append(" where state=1 group by USER_ID "); sql.append(" ) u inner join SC_SERVICER_GROUP g "); sql.append(" on g.ID = u.GROUP_ID "); sql.append(" where g.state=1 and g.PROJECT_ID =:projectId "); sql.append(" group by group_type order by group_type "); cateList = baseDao.queryForList(sql.toString(),param); sql.setLength(0); sql.append(" select g.GROUP_TYPE,u.USER_ID,u.USER_NAME from "); sql.append(" ( "); sql.append(" select GROUP_ID,USER_ID,USER_NAME from SC_SERVICER_GROUP_USER "); sql.append(" where state=1 group by USER_ID "); sql.append(" ) u inner join SC_SERVICER_GROUP g "); sql.append(" on g.ID = u.GROUP_ID "); sql.append(" where g.state=1 and g.PROJECT_ID =:projectId "); userList = baseDao.queryForList(sql.toString(),param); List resList = new ArrayList(); for(Map cateMap:cateList){ List tempList = new ArrayList(); Map tempMap = new HashMap(); tempMap.put("name", cateMap.get("GROUP_NAME")); for(Map userMap:userList){ if(cateMap.get("GROUP_TYPE").toString().equals(userMap.get("GROUP_TYPE").toString())){ tempList.add(userMap); } } tempMap.put("dataList", tempList); resList.add(tempMap); } //System.out.println("resList --------------- "+ JsonUtil.list2Json(resList)); return resList; } @Override public List getUserTree(String pid) { String sql; if(StringUtil.isBlank(pid)){ sql = " select id,rolename name from ac_role"; }else{ sql = " select u.ID id,u.ZSXM name,0 as pid from gg_user u inner join ac_user_ref_role ref on u.ID = ref.YHBH "; sql += " where ref.JSBH=:roleId"; } SqlParameter param = new SqlParameter(); param.addValue("roleId", pid); List list = baseDao.queryForList(sql,param); for(Map map:list){ if(StringUtil.isBlank(pid)){ map.put("isParent", true); }else{ map.put("isParent", false); } } return list; } @Override public void saveCollectionPerson(Map map){ String generalId = ConvertUtil.obj2StrBlank(map.get("generalId")); String executorIds = ConvertUtil.obj2StrBlank(map.get("executorIds")); StringBuilder sql = new StringBuilder(); sql.append("delete from SC_COLLECTION_PERSON where CI_GENERAL_ID=:generalId"); SqlParameter parameter = new SqlParameter(); parameter.addValue("generalId", generalId); baseDao.execute(sql.toString(), parameter); if(StringUtil.isNotBlank(executorIds)){ List list = new ArrayList(); sql.setLength(0); sql.append("insert into SC_COLLECTION_PERSON (id,ci_general_id,user_id) values (:id,:ci_general_id,:user_id)"); String[] executorAry = executorIds.split(","); for(String user_id:executorAry){ SqlParameter param = new SqlParameter(); param.addValue("id", StringUtil.getUUID()); param.addValue("ci_general_id", generalId); param.addValue("user_id", user_id); list.add(param); } baseDao.executeBatch(sql.toString(), list); } } @Override public List getUserByIds(String ids){ StringBuilder sql = new StringBuilder(); if(StringUtil.isNotBlank(ids)){ ids = ids.replace(",", "','"); ids = "'" + ids + "'"; sql.append("select * from gg_user where id in ("+ids+")"); SqlParameter parameter = new SqlParameter(); //parameter.addValue("ids", idArr); List userList = baseDao.queryForList(sql.toString(), parameter); return userList; }else{ return null; } } @Override public void saveCiInfo(CMDB_CI_BASE ciInfo){ ciInfo.setState(1); ciInfo.insertOrUpdate(); } @Override public Map getCustomerById(String id){ StringBuilder sql = new StringBuilder(); if(StringUtil.isNotBlank(id)){ sql.append("select * from SC_PARTNER_CUSTOMER_INFO where id = :id"); SqlParameter parameter = new SqlParameter(); parameter.addValue("id", id); Map customer = baseDao.queryForMap(sql.toString(), parameter); return customer; }else{ return null; } } @Override public void deleteCiInfo(String ciId) { new CMDB_CI_BASE(ciId).deleteById(); } @Override public List getmanufacturerList(Map queryMap) { String partnerId = ConvertUtil.obj2StrBlank(queryMap.get("partnerId")); String type = ConvertUtil.obj2StrBlank(queryMap.get("type")); String projectId = ConvertUtil.obj2StrBlank(queryMap.get("projectId")); StringBuilder sql = new StringBuilder(); SqlParameter parameter = new SqlParameter(); parameter.addValue("partnerId", partnerId); parameter.addValue("type", type); parameter.addValue("projectId", projectId); //查询厂商 sql.append(" select ID,MANUFACTURERNAME from CMDB_MAINUFACTURER "); sql.append(" where state=1 and PARTNER_ID = :partnerId and type=:type "); List typeList = baseDao.queryForList(sql.toString(),parameter); //查询联系人 sql.setLength(0); sql.append(" select c.MAINUFACTUREERID,c.CONTACTS,c.TELPHONE,c.MOBILE,c.JOB from CMDB_MAINUFACTURER_CONTACTS c "); sql.append(" inner join CMDB_MAINUFACTURER m on c.MAINUFACTUREERID = m.ID "); sql.append(" where m.state=1 and m.PARTNER_ID = :partnerId and m.type=:type and c.PROJECT_ID = :projectId "); List contactList = baseDao.queryForList(sql.toString(),parameter); if(typeList!=null&&typeList.size()>0){ for(Map typeMap:typeList){ List tempList = new ArrayList(); String typeId = typeMap.get("Id").toString(); if(contactList!=null&&contactList.size()>0){ for(Map contactMap:contactList){ if(typeId.equals(contactMap.get("MAINUFACTUREERID").toString())){ tempList.add(contactMap); } } } typeMap.put("contactList", tempList); } } return typeList; } /** * 查询设备采集总数量 */ @Override public int queryCiAddOrderCount(HttpServletRequest request) { Map supportMap = getAllCiAddSql(request); String sql = "select count(*) from ( " + supportMap.get("sql").toString() + " ) t"; return baseDao.queryForInteger(sql,(Map)supportMap.get("param")); } /** * 查询设备采集列表 */ @Override public List queryCiAddOrderList(HttpServletRequest request) { Map supportMap = getAllCiAddSql(request); List list = baseDao.queryforSplitPage(request, supportMap.get("sql").toString(), (Map)supportMap.get("param")); return list; } /** * 服务台工单列表 * @param request * @return */ private Map getAllCiAddSql(HttpServletRequest request){ String userId = WebUtil.getLoginedUserId(request); //String partnerId = WebUtil.getLoginedPartnerId(request); String customerId = request.getParameter("customerId"); String nodeTemplateId = request.getParameter("nodeTemplateId"); String subCustomerId = request.getParameter("subCustomerId"); String startDate = request.getParameter("startDate"); String endtDate = request.getParameter("endtDate"); String orderName = request.getParameter("orderName"); String orderCode = request.getParameter("orderCode"); String userCode = request.getParameter("userCode"); String collectionType = request.getParameter("collectionType"); String flowState = request.getParameter("flowState"); String nodeState = request.getParameter("nodeState"); Map paramMap = new HashMap(); paramMap.put("userId", userId); //paramMap.put("partnerId", partnerId); paramMap.put("nodeTemplateId", nodeTemplateId); paramMap.put("nodeState", nodeState); paramMap.put("flowState", flowState); paramMap.put("businessType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_ADD); paramMap.put("customerId", customerId); paramMap.put("subCustomerId", subCustomerId); paramMap.put("orderCode", "%"+orderCode+"%"); paramMap.put("userCode", "%"+userCode+"%"); paramMap.put("orderName", "%"+orderName+"%"); /*List projects = new ArrayList(); projects = WebUtil.getProjects(request);*/ StringBuilder builder = new StringBuilder(); builder.append(" select b.PROJECT_NAME,b.ID FLOWID,b.WFNAME,b.CREATERNAME,b.CREATETIME,b.CURRENT_NODE_NAME,b.BUSINESSTYPE, "); builder.append(" r.ORDER_CODE,r.ID ORDERID,r.APPLY_REASON,b.CUSTOMER_NAME,b.SUB_CUSTOMER_NAME,B.WFSTATE,n.CURRENT_DEALER_NAME,n.FLOWSTATE NODESTATE "); builder.append(" from SC_WORKFLOW_CI_ADD r "); builder.append(" inner join WORKFLOW_BASE b on r.FLOW_ID = b.ID "); /*if(StringUtil.isNotBlank(nodeTemplateId)){*/ builder.append(" inner join WORKFLOW_NODE N on b.CURRENT_NODE_ID = n.ID "); builder.append(" inner join gg_user g on b.CREATERID = g.ID "); /*}*/ builder.append(" where 1=1 "); //工单类型 builder.append(" AND b.BUSINESSTYPE = :businessType"); if(StringUtil.isNotBlank(nodeTemplateId)){ builder.append(" and n.NODE_TEMPLATE_ID IS NOT NULL and n.NODE_TEMPLATE_ID =:nodeTemplateId and b.WFSTATE = 1 "); } //客户 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(startDate)) { builder.append(" AND b.CREATETIME >= :startDate "); paramMap.put("startDate", startDate+"000000"); } //结束时间 if(StringUtil.isNotBlank(endtDate)) { builder.append(" AND b.CREATETIME <= :endtDate "); paramMap.put("endtDate", endtDate+"666666"); } //工单名称 if(StringUtil.notEmpty(orderName)) { builder.append(" AND b.WFNAME LIKE :orderName"); } //工单编号 if(StringUtil.notEmpty(orderCode)) { builder.append(" AND r.ORDER_CODE LIKE :orderCode"); } //用户 if(StringUtil.notEmpty(userCode)) { builder.append(" AND g.employee_id LIKE :userCode"); } //流程状态 if(StringUtil.notEmpty(flowState)) { builder.append(" AND b.WFSTATE = :flowState"); } //节点状态 if(StringUtil.notEmpty(nodeState)) { builder.append(" AND n.FLOWSTATE = :nodeState"); } //采集类型 if(StringUtil.notEmpty(collectionType)) { String[] types = collectionType.split(","); if(types.length==1) { builder.append(" AND r.COLLECTION_TYPE_ID = :type_id"); paramMap.put("type_id", types[0]); } else { builder.append(" AND r.COLLECTION_TYPE_ID IN ("); for(int i=0; i list = baseDao.queryforSplitPage(request, supportMap.get("sql").toString(), (Map)supportMap.get("param")); return list; } @Override public int queryMyCiaddCount(HttpServletRequest request) { Map supportMap = getMyCiAddListSql(request); String sql = "select count(*) from ( " + supportMap.get("sql").toString() + " ) t"; return baseDao.queryForInteger(sql,(Map)supportMap.get("param")); } public SysInfoMsg 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 = new SC_WORKFLOW_CI_ADD(orderId).getInstanceById().getFlow_id(); 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_ADD); SysInfoMsg msg = workFlowSupportService.doOrderSendback(support); return msg; } /** * 获取上一节点主负责人 * @param lastNodeTemplateId * @return */ public Map getLastExecutor(String lastNodeTemplateId){ String sql = "select * from WORKFLOW_NODE where NODE_TEMPLATE_ID = :nodeTemplateId and IS_ADMIN=1 order by CREATETIME desc LIMIT 1"; SqlParameter parameter = new SqlParameter(); parameter.addValue("nodeTemplateId", lastNodeTemplateId); return baseDao.queryForMap(sql,parameter); } @Override public List getGeneralList(String orderId) { StringBuilder sql = new StringBuilder(); sql.append("select g.*,t.EXECUTORS,t.EXECUTORIDS from SC_WORKFLOW_CI_ADD_GENERAL g "); sql.append("left join ( "); sql.append("select p.CI_GENERAL_ID,group_concat(u.zsxm) as EXECUTORS,group_concat(u.id) as EXECUTORIDS "); sql.append("from SC_COLLECTION_PERSON p inner join gg_user u "); sql.append("on p.USER_ID=u.ID group by CI_GENERAL_ID "); sql.append(") t on g.ID=t.CI_GENERAL_ID "); sql.append("where BUSINESS_ID=:orderId "); //String sql = "select * from SC_WORKFLOW_CI_ADD_GENERAL where BUSINESS_ID=:orderId "; SqlParameter parameter = new SqlParameter(); parameter.addValue("orderId", orderId); return baseDao.queryForList(sql.toString(),parameter); } /** * 更新设备状态 * @param flowId */ public void updateCMDB(String flowId){ String sql = "update CMDB_CI_BASE set USINGSTATE=1 where FLOW_ID=:flowId "; SqlParameter parameter = new SqlParameter(); parameter.addValue("flowId", flowId); baseDao.execute(sql,parameter); //插入关联工单 sql = " select ID CIID from CMDB_CI_BASE where FLOW_ID = :flowId and USINGSTATE = 1 "; List ciList = baseDao.queryForList(sql,parameter); if(ciList!=null&&ciList.size()>0){ sql = "insert into CMDB_CI_REF_ORDER (ID,FLOW_ID,CI_ID) values (:ID,:FLOWID,:CIID)"; List paramList = new ArrayList(); for(Map map : ciList){ SqlParameter tempParam = new SqlParameter(); tempParam.put("ID", StringUtil.getUUID()); tempParam.put("CIID", map.get("CIID")); tempParam.put("FLOWID", flowId); paramList.add(tempParam); } baseDao.executeBatch(sql, paramList); } } /** * 关闭流程 */ public void closeWorkFlowSubmit(HttpServletRequest request){ String orderId = request.getParameter("orderId"); String closeNote = request.getParameter("closeNote"); String nodeId = request.getParameter("nodeId"); String userName = WebUtil.getLoginUser(request).getLoginUser().get("ZSXM").toString(); SC_WORKFLOW_CI_ADD ciAddOrder = new SC_WORKFLOW_CI_ADD(orderId).getInstanceById(); WORKFLOW_BASE base = new WORKFLOW_BASE(ciAddOrder.getFlow_id()).getInstanceById(); NodeFinishEntity finishEntity = new NodeFinishEntity(); finishEntity.setDeal_result(1); finishEntity.setDeal_note_title(userName+"关闭了工单"); finishEntity.setDeal_note(closeNote); WORKFLOW_NODE node = new WORKFLOW_NODE(nodeId).getInstanceById(); workflowCoreService.finishNode(base, node, finishEntity); workflowCoreService.closeWorkFlow(base); dslFacade.updateConfigDslWhenClose(ciAddOrder.getFlow_id()); String flowId = base.getId(); //保存dsl操作记录 dslFacade.saveConfigDslDealRecord(flowId); //更新dsl关系 dslFacade.updateConfigDslWhenClose(flowId); //配置关系保存到记录表 changeFacade.saveRelationWhenClose(flowId); //删除变更中的配置关系 changeFacade.delRelationWhenClose(flowId); } @Override public Map getLastNodeInfo(String nodeId){ String sql = "select * from workflow_node where id = ( " + " select SOURCE_NODE_INSTANCE_ID from workflow_node where id =:nodeId " + ")"; SqlParameter parameter = new SqlParameter(); parameter.addValue("nodeId", nodeId); Map map = baseDao.queryForMap(sql,parameter); String dealTime = DateUtil.format("yyyy-MM-dd HH:mm:ss", ConvertUtil.obj2StrBlank(map.get("DEALTIME"))); map.put("DEALTIME", dealTime); return map; } @Override public List queryFiles(String orderId) { String selectSql = "SELECT * FROM GG_FILES WHERE DEL_FLAG = 1 AND (BUSINESS_ID = :orderId OR EXTEND1 = :orderId) ORDER BY ORDERNUM "; List files = baseDao.queryForList(selectSql,new SqlParameter("orderId",orderId)); if(null!=files && files.size()>0) { for(Map file : files) { file.put("FILE_SIZE", StringUtil.getFileSize(ConvertUtil.obj2Double(file.get("FILE_SIZE")))); } } return files; } @Override public Map selectCiInfoById(String ciId) { String sql = "select * from CMDB_CI_BASE where id=:ciId "; SqlParameter parameter = new SqlParameter(); parameter.addValue("ciId", ciId); return baseDao.queryForMap(sql,parameter); } @Override public boolean answerSubmit(HttpServletRequest request){ String orderId = request.getParameter("orderId"); SC_WORKFLOW_CI_ADD ciAddOrder = new SC_WORKFLOW_CI_ADD(orderId).getInstanceById(); String nodeId = request.getParameter("nodeId"); WORKFLOW_BASE base = new WORKFLOW_BASE(ciAddOrder.getFlow_id()).getInstanceById(); WORKFLOW_NODE node = new WORKFLOW_NODE(nodeId).getInstanceById(); NodeAnswerEntity entity = new NodeAnswerEntity(); Map user = WebUtil.getLoginUser(request).getLoginUser(); entity.setUser_id(user.get("ID").toString()); entity.setUser_name(user.get("ZSXM").toString()); boolean flag = workflowCoreService.answerNode(base, node, entity); return flag; } /** * 获取审批人列表 * @return */ public Map getApproveUser(){ String sql = "select u.* from " + "workflow_template_node n inner join WORKFLOW_TEMPLATE_NODE_PARTNER p " + "on n.ID = p.NODE_ID " + "inner join WORKFLOW_TEMPLATE_NODE_USER u " + "on p.ID = u.PARTNER_NODE_ID " + "where n.ID='"+Constants.CIADD_APPROVE+"' LIMIT 1 "; return baseDao.queryForMap(sql); } /** * 更新例行巡检 * @param flowId */ public void updateDutyPatrol(String flowId){ String selectSql = "SELECT A.* FROM CI_REMIND_CILIST A WHERE A.STATE = 2 AND EXISTS (SELECT B.ID FROM CMDB_CI_BASE B WHERE A.CI_ID = B.ID AND B.FLOW_ID = :flowId)"; List cis = baseDao.queryForList(selectSql,new SqlParameter("flowId",flowId)); if(null!=cis && cis.size()>0) { List paramList = new ArrayList(); for(Map ci : cis) { SqlParameter sqlParameter = new SqlParameter(); sqlParameter.put("id", ci.get("ID")); paramList.add(sqlParameter); } String updateSql = "UPDATE CI_REMIND_CILIST SET STATE = 1 WHERE ID = :id "; baseDao.executeBatch(updateSql,paramList); } } /** * 更新健康检查 */ void updateHealthPatrol(String flowId){ String sql = " update CI_HEALTH_CUSTOMIZE_DETAIL h " + " inner join cmdb_ci_base b on b.ID = h.CI_ID " + " set h.STATE=1 where b.FLOW_ID = :flowId "; baseDao.execute(sql, new SqlParameter("flowId",flowId)); } public List getProjectManager(String projectId){ String sql = "SELECT A.PROJECT_MANAGER_ID USER_ID,A.PROJECT_MANAGER_NAME USER_NAME,1 as IS_ADMIN FROM SC_PARTNER_CUSTOMER_PROJECT A WHERE A.PROJECT_STATE != 2 " + " and id=:projectId "; SqlParameter parameter = new SqlParameter(); parameter.addValue("projectId", projectId); return baseDao.queryForList(sql,parameter); } @Override public void delFiles(String fileId) { } @Override public SysInfoMsg doAdminConfirm(HttpServletRequest request) { String orderId = request.getParameter("orderId"); String nodeId = request.getParameter("nodeId"); String note = request.getParameter("flowNote"); String nextUserId = request.getParameter("nextUserId"); String nextUserName = request.getParameter("nextUserName"); Map user = WebUtil.getLoginUser(request).getLoginUser(); String userName = user.get("ZSXM").toString(); String userId = user.get("Id").toString(); SC_WORKFLOW_CI_ADD ciAddOrder = new SC_WORKFLOW_CI_ADD(orderId).getInstanceById(); String flowId = ciAddOrder.getFlow_id(); WorkFlowSupportEntity support = new WorkFlowSupportEntity(); List userList = new ArrayList(); if(nextUserId.indexOf("user_")>=0){ Map userMap = new HashMap(); userMap.put("USER_ID", nextUserId.substring(5)); userMap.put("USER_NAME", nextUserName); userMap.put("IS_ADMIN", 1); userList.add(userMap); }else{ support.setDealer_type(String.valueOf(NodeDealEntity.Dealer_Type_ROLE)); support.setRoleId(nextUserId.substring(6)); support.setRoleName(nextUserName); } support.setFlowId(flowId); support.setNodeId(nodeId); support.setUserName(userName); support.setUserId(userId); support.setNote(note); support.setNextUserList(userList); support.setNextNodeTemplateId(Constants.CIADD_ADMIN); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_ADD); SysInfoMsg msg = workFlowSupportService.doOrderFlow(support); return msg; } @Override public List getCiAdmin(String projectId){ SqlParameter param = new SqlParameter(); param.put("projectId", projectId); StringBuilder sql = new StringBuilder(); List cateList = new ArrayList(); List userList = new ArrayList(); //查询组及用户 sql.setLength(0); sql.append(" select g.ID GROUP_ID,g.GROUP_NAME,u.USER_ID,u.USER_NAME from "); sql.append(" ( "); sql.append(" select GROUP_ID,USER_ID,USER_NAME from SC_SERVICER_GROUP_USER "); sql.append(" where state=1 "); sql.append(" ) u inner join SC_SERVICER_GROUP g "); sql.append(" on g.ID = u.GROUP_ID "); sql.append(" INNER JOIN AC_USER_REF_ROLE R ON U.USER_ID=R.YHBH "); sql.append(" where g.state=1 and g.PROJECT_ID =:projectId and r.jsbh=:roleId group by u.USER_ID "); param.put("roleId", Constants.CFG_ADMIN_ID); userList = baseDao.queryForList(sql.toString(),param); //获取分类 Map groupMap = new HashMap(); List resList = new ArrayList(); if(userList!=null&&userList.size()>0){ for(Map map:userList){ if(!groupMap.containsKey(map.get("GROUP_ID").toString())) groupMap.put(map.get("GROUP_ID").toString(), map.get("GROUP_NAME").toString()); } for(Map.Entry entry:groupMap.entrySet()){ List tempList = new ArrayList(); Map tempMap = new HashMap(); tempMap.put("groupId", entry.getKey()); tempMap.put("groupName", entry.getValue()); for(Map map:userList){ if(map.get("GROUP_ID").toString().equals(entry.getKey())){ tempList.add(map); } } tempMap.put("userList", tempList); resList.add(tempMap); } } //System.out.println("--------------"+JsonUtil.list2Json(resList)); return resList; } public Map getReport(String orderId,String flowId){ Map resMap = new HashMap(); Map baseInfo = new HashMap(); //查询参数 Map param = new HashMap(); param.put("flowId", flowId); param.put("orderId", orderId); param.put("appNodeId", Constants.CIADD_APPROVE); param.put("colNodeId", Constants.CIADD_COLLECTION); param.put("admNodeId", Constants.CIADD_ADMIN); //查询基本信息 StringBuilder sql = new StringBuilder(); sql.append(" select c.CUSTOMER_NAME,c.ORDER_CODE,c.PROJECT_NAME,c.COLLECTION_TYPE_NAME,b.CREATETIME,b.ENDTIME,c.CUSTOMER_CONTACTS,c.CUSTOMER_CONTACT_INFO,c.SUB_CUSTOMER_NAME, "); sql.append(" c.APPLY_REASON,c.APPLY_USER_NAME,c.APPLY_TIME, "); sql.append(" approve.*,col.*,adm.* "); sql.append(" from sc_workflow_ci_add c "); sql.append(" inner join workflow_base b on c.FLOW_ID = b.ID "); sql.append(" left join "); sql.append(" ( "); sql.append(" select DEAL_NOTE APPROVENOTE,CURRENT_DEALER_NAME APPROVENAME,DEALTIME APPROVETIME,FLOWID from workflow_node "); sql.append(" where NODE_TEMPLATE_ID = :appNodeId and IS_ADMIN=1 and FLOWID = :flowId "); sql.append(" order by dealtime desc LIMIT 1 "); sql.append(" ) approve "); sql.append(" on c.FLOW_ID = approve.FLOWID "); sql.append(" left join "); sql.append(" ( "); sql.append(" select DEAL_NOTE COLNOTE,CURRENT_DEALER_NAME COLNAME,DEALTIME COLTIME,FLOWID from workflow_node "); sql.append(" where NODE_TEMPLATE_ID = :colNodeId and IS_ADMIN=1 and FLOWID = :flowId "); sql.append(" order by dealtime desc LIMIT 1 "); sql.append(" ) col "); sql.append(" on c.FLOW_ID = col.FLOWID "); sql.append(" left join "); sql.append(" ( "); sql.append(" select DEAL_NOTE ADMNOTE,CURRENT_DEALER_NAME ADMNAME,DEALTIME ADMTIME,FLOWID from workflow_node "); sql.append(" where NODE_TEMPLATE_ID = :admNodeId and IS_ADMIN=1 and FLOWID = :flowId "); sql.append(" order by dealtime desc LIMIT 1 "); sql.append(" ) adm "); sql.append(" on c.FLOW_ID = adm.FLOWID "); sql.append(" where c.FLOW_ID = :flowId "); resMap.put("baseInfo", baseDao.queryForMap(sql.toString(),param)); //查询关联配置 sql.setLength(0); sql.append(" select c.REALNUM,g.*,t.EXECUTORS,t.EXECUTORIDS from SC_WORKFLOW_CI_ADD_GENERAL g "); sql.append(" left join ( "); sql.append(" select p.CI_GENERAL_ID,group_concat(u.zsxm) as EXECUTORS,group_concat(u.id) as EXECUTORIDS "); sql.append(" from SC_COLLECTION_PERSON p inner join gg_user u "); sql.append(" on p.USER_ID=u.ID group by CI_GENERAL_ID "); sql.append(" ) t on g.ID=t.CI_GENERAL_ID "); sql.append(" left join ( "); sql.append(" select THIRDLEVELID,COUNT(*) realNum from CMDB_CI_BASE b where FLOW_ID = :flowId "); sql.append(" group by b.THIRDLEVELID "); sql.append(" ) c on g.THIRD_LEVEL_ID=c.THIRDLEVELID "); sql.append(" where BUSINESS_ID=:orderId "); resMap.put("relation", baseDao.queryForList(sql.toString(),param)); resMap.put("fileList", incidentFacade.queryFiles(flowId)); return resMap; } @Override public List getReportDetail(String flowId) { List resList = new ArrayList(); Map param = new HashMap(); param.put("flowId", flowId); String sql =" select a.ORDER_CODE,a.PROJECT_NAME,a.CUSTOMER_NAME,b.ID,b.THIRDLEVELID,c.NAME,b.SEACHCODE,b.CINAME,b.POSITION,b.MAINUFACTURERNAME,b.SYSTEMINTEGRATIONNAME " + " from CMDB_CI_BASE b inner join cmdb_ci_category c " + " on b.THIRDLEVELID = c.ID " + " inner join sc_workflow_ci_add a on b.FLOW_ID = a.FLOW_ID " + " where c.LEIX =1 and a.flow_id = :flowId " + " order by b.THIRDLEVELID asc " ; List queryList = baseDao.queryForList(sql,param); if(queryList!=null&&queryList.size()>0){ Map groupMap = new HashMap(); Map lebalMap = new HashMap(); for(Map map:queryList){ if(!groupMap.containsKey(map.get("THIRDLEVELID").toString())){ groupMap.put(map.get("THIRDLEVELID").toString(),map.get("NAME").toString()); } } for(Map.Entry entry : groupMap.entrySet()){ Map tempMap = new HashMap(); tempMap.put("name", entry.getValue()); List tempList = new ArrayList(); for(Map map:queryList){ if(entry.getKey().equals(map.get("THIRDLEVELID").toString())){ tempList.add(map); } } tempMap.put("dataList",tempList); resList.add(tempMap); } } return resList; } public String saveContact(HttpServletRequest request,CMDB_MAINUFACTURER_CONTACTS contact) { long createTime = DateUtil.getCurrentDate14(); Map user = WebUtil.getLoginUser(request).getLoginUser(); String userId = ConvertUtil.obj2StrBlank(user.get("ID")); String userName = ConvertUtil.obj2StrBlank(user.get("ZSXM")); String flowId = contact.getFlow_id(); WORKFLOW_BASE base = new WORKFLOW_BASE(flowId).getInstanceById(); contact.setName_pinyin(PinYinUtil.getStringPinYin(contact.getContacts(), false)).setCreate_time(createTime).setUser_id(userId).setUser_name(userName).setDel_flag(1).setCustomer_id(base.getCustomer_id()).insert(); return "1"; } public Map getOrderFlowInfo(String flowId){ Map param = new HashMap(); param.put("flowId", flowId); String sql = " select n.CURRENT_DEAL_ROLENAME roleName,n.deal_type dealType,g.SJHM telphone,n.FLOWSTATE nodeState,b.WFSTATE flowState,b.CURRENT_NODE_NAME nodeName,b.CURRENT_DEALER_NAME userName "+ " from workflow_base b inner join workflow_node n on "+ " b.CURRENT_NODE_ID = n.id "+ " left join gg_user g on g.ID = b.CURRENT_DEALER_ID "+ " where FLOWID = :flowId "; return baseDao.queryForMap(sql,param); } /** * 更新日常巡检设备信息 * @param flowId */ public void updateDayPatrol(String flowId){ Map param = new HashMap(); param.put("flowId", flowId); StringBuilder sql = new StringBuilder(); sql.append(" select c.ID,c.PROJECT_ID,c.SUB_CUSTOMER_ID from cmdb_ci_base c "); sql.append(" inner join "); sql.append(" ( "); sql.append(" select CI_ID from CI_DAILY_PATROL_CUSTOMIZE_DETAIL group by CI_ID "); sql.append(" ) t "); sql.append(" on c.ID = t.CI_ID "); sql.append(" where c.FLOW_ID = :flowId and c.USINGSTATE=1 "); List queryList = baseDao.queryForList(sql.toString(),param); List paramList = new ArrayList(); if(queryList!=null&&queryList.size()>0){ for(Map map:queryList){ SqlParameter tempParam = new SqlParameter(); tempParam.addValue("ciId",map.get("ID")) .addValue("projectId",map.get("PROJECT_ID")) .addValue("subCustomerId",map.get("SUB_CUSTOMER_ID")) .addValue("id", StringUtil.getUUID()); paramList.add(tempParam); } sql.setLength(0); sql.append(" insert into CI_DAILY_PATROL_CI (ID,PROJECT_ID,CI_ID,SUB_CUSTOMER_ID) values (:id,:projectId,:ciId,:subCustomerId) "); baseDao.executeBatch(sql.toString(), paramList); } } /** * 获取我的设备采集sql * @param request * @return */ private Map getMyCiAddListSql(HttpServletRequest request){ String userId = WebUtil.getLoginedUserId(request); //String partnerId = WebUtil.getLoginedPartnerId(request); String customerId = request.getParameter("customerId"); String subCustomerId = request.getParameter("subCustomerId"); String startDate = request.getParameter("startDate"); String endtDate = request.getParameter("endtDate"); String orderName = request.getParameter("orderName"); String orderCode = request.getParameter("orderCode"); String userCode = request.getParameter("userCode"); String collectionType = request.getParameter("collectionType"); String flowState = request.getParameter("flowState"); String nodeState = request.getParameter("nodeState"); Map paramMap = new HashMap(); paramMap.put("userId", userId); //paramMap.put("partnerId", partnerId); paramMap.put("nodeState", nodeState); paramMap.put("flowState", flowState); paramMap.put("businessType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_ADD); paramMap.put("customerId", customerId); paramMap.put("subCustomerId", subCustomerId); paramMap.put("orderCode", "%"+orderCode+"%"); paramMap.put("userCode", "%"+userCode+"%"); paramMap.put("orderName", "%"+orderName+"%"); 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,b.PROJECT_NAME, "); builder.append(" r.ORDER_CODE,r.ID ORDERID,r.APPLY_REASON "); builder.append(" from SC_WORKFLOW_CI_ADD r "); builder.append(" inner join WORKFLOW_BASE B on r.FLOW_ID = b.ID "); builder.append(" inner join "); builder.append(" ( "); builder.append(" select * from ( "); builder.append(" select CREATETIME NODETIME,ID NODEID,FLOWSTATE NODESTATE,FLOWID,CURRENT_DEALER_NAME 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(" inner join gg_user g on b.createrid = g.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 "); } //开始时间 if(StringUtil.isNotBlank(startDate)) { builder.append(" AND b.CREATETIME >= :startDate "); paramMap.put("startDate", startDate+"000000"); } //结束时间 if(StringUtil.isNotBlank(endtDate)) { builder.append(" AND b.CREATETIME <= :endtDate "); paramMap.put("endtDate", endtDate+"666666"); } //工单名称 if(StringUtil.notEmpty(orderName)) { builder.append(" AND b.WFNAME LIKE :orderName"); } //工单编号 if(StringUtil.notEmpty(orderCode)) { builder.append(" AND r.ORDER_CODE LIKE :orderCode"); } //用户 if(StringUtil.notEmpty(userCode)) { builder.append(" AND g.employee_id LIKE :userCode"); } //采集类型 if(StringUtil.notEmpty(collectionType)) { String[] types = collectionType.split(","); if(types.length==1) { builder.append(" AND r.COLLECTION_TYPE_ID = :type_id"); paramMap.put("type_id", types[0]); } else { builder.append(" AND r.COLLECTION_TYPE_ID IN ("); for(int i=0; i projects) { if(null != projects && projects.size()>0) { buffer.append(" AND b.PROJECT_ID IN ( "); for(Map project : projects) { String projectId = ConvertUtil.obj2StrBlank(project.get("PROJECT_ID")); buffer.append("'"); buffer.append(projectId); buffer.append("',"); } buffer.deleteCharAt(buffer.length()-1); buffer.append(")"); } else { buffer.append(" AND b.PROJECT_ID = '' "); } return buffer; } @Override public Map getCiCollectionManageSurvey(HttpServletRequest request) { Map result = new HashMap(); //String partnerId = WebUtil.getLoginedPartnerId(request); SqlParameter param = new SqlParameter(); List projectIds = new ArrayList(); /*List projects = WebUtil.getProjects(request); for (Map project : projects) { projectIds.add(ConvertUtil.obj2StrBlank(project.get("PROJECT_ID"))); }*/ param.addValue("businessType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_ADD) .addValue("projectIds", projectIds); StringBuilder builder = new StringBuilder(); builder.append(" select COUNT(b.ID) num,b.wfstate "); builder.append(" from WORKFLOW_BASE b,WORKFLOW_NODE n "); builder.append(" where b.CURRENT_NODE_ID = n.ID AND b.BUSINESSTYPE = :businessType "); builder.append(" and b.PROJECT_ID in (:projectIds) "); //工单类型 builder.append(" GROUP BY b.WFSTATE "); List queryList = baseDao.queryForList(builder.toString(),param); result.put("totalNum", 0); result.put("dealNum", 0); result.put("finishNum", 0); result.put("closeNum", 0); if(queryList!=null&&queryList.size()>0){ Integer totalNum = 0; for(Map map:queryList){ if(ConvertUtil.obj2StrBlank(map.get("wfstate")).equals(Constants.WORKFLOW_BASE_WFSTATE_DOING.toString())){//进行中 result.put("dealNum", map.get("num")); }else if(ConvertUtil.obj2StrBlank(map.get("wfstate")).equals(Constants.WORKFLOW_BASE_WFSTATE_FINISH.toString())){//已完成 result.put("finishNum", map.get("num")); }else if(ConvertUtil.obj2StrBlank(map.get("wfstate")).equals(Constants.WORKFLOW_BASE_WFSTATE_CLOSE.toString())){//已取消 result.put("closeNum", map.get("num")); } totalNum = totalNum + ConvertUtil.obj2Integer(map.get("num")); } result.put("totalNum",totalNum); } return result; } @Override public boolean checkHasConfig(String flowId) { String sql = " select count(id) from cmdb_ci_base where flow_id=:flowId "; int count = baseDao.queryForInteger(sql,new SqlParameter().addValue("flowId", flowId)); if(count==0){ return false; }else{ return true; } } @Override public List getXlsList(String configId, String fileName) { List list = new ArrayList(); // 创建对Excel工作簿文件的引用 HSSFWorkbook wookbook; try { wookbook = new HSSFWorkbook(new FileInputStream(fileName)); // 在Excel文档中，第一张工作表的缺省索引是0 HSSFSheet sheet = wookbook.getSheet("Sheet1"); //获取到Excel文件中的所有行数 int rows = sheet.getPhysicalNumberOfRows(); //遍历行 for (int i = 1; i < rows; i++) { // 读取左上端单元格 HSSFRow row = sheet.getRow(i); // 行不为空 if (row != null) { //获取到Excel文件中的所有的列 int cells = row.getPhysicalNumberOfCells(); String value = ""; //获取到列的值­ HSSFCell cell = row.getCell(0); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_NUMERIC: value = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; default: value = ""; break; } list.add(value); } } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return list; } @Override public void doBathAdd(String configId, List serialNos,HttpServletRequest request) { /*CMDB_CI_BASE base = new CMDB_CI_BASE(configId).getInstanceById(); String selSql = " SELECT * FROM CMDB_CI_DATA_STORAGE WHERE CI_ID = :configId "; List Storages = baseDao.queryForList(selSql,new SqlParameter().addValue("configId", configId)); if(serialNos!=null&&serialNos.size()>0){ List params = new ArrayList(); List extend_params = new ArrayList(); StringBuilder sql = new StringBuilder(); sql.append(" INSERT INTO CMDB_CI_BASE ( "); sql.append(" ID,CINAME,SEACHCODE,MEMBERCODE,MEMBERID,MEMBERNAME,LEVELNUMBER,THELEVELID,FIRSTLEVELID,SECONDLEVELID,THIRDLEVELID, "); sql.append(" MAINUFACTURERID,MAINUFACTURERNAME,SYSTEMINTEGRATIONID,SYSTEMINTEGRATIONNAME,USINGSTATE,CREATETIME, "); sql.append(" CIADMIN,POSITION,LASTUPDATETIME,NOTE,VERSION,FLOW_ID,DISK_ID,LEIX,BUY_TIME,GB_TIME, "); sql.append(" SERVICE_PROVIDERS_ID,SERVICE_PROVIDERS_NAME,XB_TIME,XBGB_TIME,PCI_ID,DEPT_ID,DEPT_NAME, "); sql.append(" SN,PROJECT_ID,PROJECT_NAME,SUB_CUSTOMER_ID,SUB_CUSTOMER_NAME,MO_LINK_ID "); sql.append(" ) VALUES ( "); sql.append(" :ID,:CINAME,:SEACHCODE,:MEMBERCODE,:MEMBERID,:MEMBERNAME,:LEVELNUMBER,:THELEVELID,:FIRSTLEVELID,:SECONDLEVELID,:THIRDLEVELID, "); sql.append(" :MAINUFACTURERID,:MAINUFACTURERNAME,:SYSTEMINTEGRATIONID,:SYSTEMINTEGRATIONNAME,:USINGSTATE,:CREATETIME, "); sql.append(" :CIADMIN,:POSITION,:LASTUPDATETIME,:NOTE,:VERSION,:FLOW_ID,:DISK_ID,:LEIX,:BUY_TIME,:GB_TIME, "); sql.append(" :SERVICE_PROVIDERS_ID,:SERVICE_PROVIDERS_NAME,:XB_TIME,:XBGB_TIME,:PCI_ID,:DEPT_ID,:DEPT_NAME, "); sql.append(" :SN,:PROJECT_ID,:PROJECT_NAME,:SUB_CUSTOMER_ID,:SUB_CUSTOMER_NAME,:MO_LINK_ID "); sql.append(" ) "); int i = 0; for(String serialNo: serialNos){ i++; String searchcode = sequenceService.getCICategorySequence(base.getThirdlevelid()); sequenceService.nextCICategorySequence(base.getThirdlevelid()); String membercode = ConvertUtil.obj2StrBlank(sequenceService.getMemberSequence(base.getMemberid())); sequenceService.nextMemberSequence(base.getMemberid()); //生成二维码 TwoDimensionCode handler = new TwoDimensionCode(); String searchCode = searchcode; String url = Constants.QRCODE_URL + searchCode; String logoUrl = WebUtil.getRealPath(request, "/static/logo.jpg"); handler.encoderQRCode(url, WebUtil.getRealPath(request, "/upload/qrCode/"+searchCode+".png"), "png",logoUrl); String newConfigId = StringUtil.getUUID(); SqlParameter param = new SqlParameter(); param.addValue("ID", newConfigId) .addValue("CINAME", base.getCiname().toString()+i) .addValue("SEACHCODE",searchcode) .addValue("MEMBERCODE", membercode) .addValue("MEMBERID", base.getMemberid()) .addValue("MEMBERNAME", base.getMembername()) .addValue("LEVELNUMBER", base.getLevelnumber()) .addValue("THELEVELID", base.getThelevelid()) .addValue("FIRSTLEVELID", base.getFirstlevelid()) .addValue("SECONDLEVELID", base.getSecondlevelid()) .addValue("THIRDLEVELID", base.getThirdlevelid()) .addValue("MAINUFACTURERID", base.getMainufacturerid()) .addValue("MAINUFACTURERNAME", base.getMainufacturername()) .addValue("SYSTEMINTEGRATIONID", base.getSystemintegrationid()) .addValue("SYSTEMINTEGRATIONNAME", base.getSystemintegrationname()) .addValue("USINGSTATE", 3) .addValue("CREATETIME", DateUtil.getCurrentDate14()) .addValue("CIADMIN", base.getCiadmin()) .addValue("POSITION", base.getPosition()) .addValue("LASTUPDATETIME", DateUtil.getCurrentDate14()) .addValue("NOTE", base.getNote()) .addValue("VERSION", 1) .addValue("FLOW_ID", base.getFlow_id()) .addValue("DISK_ID", base.getDisk_id()) .addValue("LEIX", 1) .addValue("BUY_TIME", base.getBuy_time()) .addValue("GB_TIME", base.getGb_time()) .addValue("SERVICE_PROVIDERS_ID", base.getService_providers_id()) .addValue("SERVICE_PROVIDERS_NAME", base.getService_providers_name()) .addValue("XB_TIME", base.getXb_time()) .addValue("XBGB_TIME", base.getXbgb_time()) .addValue("PCI_ID", base.getPci_id()) .addValue("DEPT_ID", base.getDept_id()) .addValue("DEPT_NAME", base.getDept_name()) .addValue("SN", serialNo) .addValue("PROJECT_ID", base.getProject_id()) .addValue("PROJECT_NAME", base.getProject_name()) .addValue("SUB_CUSTOMER_ID", base.getSub_customer_id()) .addValue("SUB_CUSTOMER_NAME", base.getSub_customer_id()) .addValue("MO_LINK_ID", base.getMo_link_id()) .addValue("CI_ID", newConfigId); params.add(param); for(Map Storage:Storages){ SqlParameter extend_param = new SqlParameter(); extend_param.addValue("COLUMN_ID", Storage.get("COLUMN_ID")) .addValue("COLUMN_CODE", Storage.get("COLUMN_CODE")) .addValue("COLUMN_NAME", Storage.get("COLUMN_NAME")) .addValue("COLUMN_VALUE", Storage.get("COLUMN_VALUE")) .addValue("COLUMN_TEXT", Storage.get("COLUMN_TEXT")) .addValue("STORAGE_ID", StringUtil.getUUID()) .addValue("CI_ID", newConfigId) .addValue("SEACHCODE", searchcode); extend_params.add(extend_param); } } baseDao.executeBatch(sql.toString(), params); String insertSql = "INSERT INTO CMDB_CI_DATA_STORAGE(ID,CI_ID,SEACHCODE,COLUMN_ID,COLUMN_CODE,COLUMN_NAME,COLUMN_VALUE,COLUMN_TEXT) VALUES (:STORAGE_ID,:CI_ID,:SEACHCODE,:COLUMN_ID,:COLUMN_CODE,:COLUMN_NAME,:COLUMN_VALUE,:COLUMN_TEXT)"; baseDao.executeBatch(insertSql,extend_params); }*/ } @Override public Map getNextNode(Map params) { String flowId = params.get("flowId"); Map nextNode = new HashMap(); nextNode.put("userId", ""); nextNode.put("userName", ""); String templateId = Constants.CIADD_APPLY; if (flowId==null||StringUtil.isBlank(flowId)) { nextNode.put("nodeId", Constants.CIADD_APPROVE); nextNode.put("nodeName", Constants.getCiAddNode(Constants.CIADD_APPROVE)); }else{ StringBuilder sql = new StringBuilder("select b.node_template_id from workflow_base a,workflow_node b where a.current_node_id = b.id and a.id = :flowId "); templateId = baseDao.queryForString(sql.toString(), new SqlParameter("flowId", flowId)); if(templateId.equals(Constants.CIADD_COLLECTION)){ nextNode.put("nodeId", Constants.CIADD_CONFIRM); nextNode.put("nodeName", Constants.getCiAddNode(Constants.CIADD_CONFIRM)); }else if(templateId.equals(Constants.CIADD_CONFIRM)){ nextNode.put("nodeId", Constants.CIADD_ADMIN); nextNode.put("nodeName", Constants.getCiAddNode(Constants.CIADD_ADMIN)); }else if(templateId.equals(Constants.CIADD_APPLY)){ nextNode.put("nodeId", Constants.CIADD_APPROVE); nextNode.put("nodeName", Constants.getCiAddNode(Constants.CIADD_APPROVE)); } } nextNode.put("curTemplateId", templateId); return nextNode; } public Map queryUserByTemplateId(String flowId,String templateId){ String sql = " select n.current_dealer_id,n.current_dealer_name from workflow_base b,workflow_node n where b.id = n.flowid" + " and b.id=:flowId and n.node_template_id = :templateId and is_admin=1 order by n.createtime limit 1 "; SqlParameter sqlParameter = new SqlParameter(); sqlParameter.addValue("templateId", templateId) .addValue("flowId", flowId); return baseDao.queryForMap(sql, sqlParameter); } @Override public List getMainCiRelation(String flowId, String ciId) { String sql = " SELECT b.CINAME,A.*, A.AFTER_RELATION_TYPE AS RELATION FROM CMDB_CI_RELATION_ING A, CMDB_CI_BASE B " + " WHERE A.SUBCIID = B.ID AND A.BUSINESS_ID = :flowId AND A.MAINID = :ciId AND ( A.DEAL_TYPE IS NULL OR A.DEAL_TYPE = 1 OR A.DEAL_TYPE = 2 ) "; return baseDao.queryForList(sql,new SqlParameter().addValue("flowId",flowId).addValue("ciId",ciId)); } @Override public List getSubCiRelation(String flowId, String ciId) { String sql = " SELECT b.CINAME,A.*, A.AFTER_RELATION_TYPE AS RELATION FROM CMDB_CI_RELATION_ING A, CMDB_CI_BASE B " + " WHERE A.SUBCIID = B.ID AND A.BUSINESS_ID = :flowId AND A.SUBCIID = :ciId AND ( A.DEAL_TYPE IS NULL OR A.DEAL_TYPE = 1 OR A.DEAL_TYPE = 2 ) "; return baseDao.queryForList(sql,new SqlParameter().addValue("flowId",flowId).addValue("ciId",ciId)); } @Override public List getMainCiRelationDetail(String flowId, String ciId) { String sql = " SELECT b.CINAME,A.*,A.RELATION AS RELATION_TYPE FROM CMDB_CI_RELATION A, CMDB_CI_BASE B " + " WHERE A.SUBCIID = B.ID AND A.MAINCIID = :ciId "; return baseDao.queryForList(sql,new SqlParameter().addValue("flowId",flowId).addValue("ciId",ciId)); } @Override public List getSubCiRelationDetail(String flowId, String ciId) { String sql = " SELECT b.CINAME,A.*,A.RELATION AS RELATION_TYPE FROM CMDB_CI_RELATION A, CMDB_CI_BASE B " + " WHERE A.SUBCIID = B.ID AND A.SUBCIID = :ciId "; return baseDao.queryForList(sql,new SqlParameter().addValue("flowId",flowId).addValue("ciId",ciId)); } @Override public SysInfoMsg doPartnerDeal(HttpServletRequest request){ String orderId = ConvertUtil.obj2StrBlank(request.getParameter("orderId")); Map user = WebUtil.getLoginUser(request).getLoginUser(); String userName = user.get("ZSXM").toString(); String userId = user.get("Id").toString(); String nodeId = ConvertUtil.obj2StrBlank(request.getParameter("nodeId")); WORKFLOW_NODE node=new WORKFLOW_NODE(nodeId).getInstanceById(); WORKFLOW_BASE base=new WORKFLOW_BASE(node.getFlowid()).getInstanceById(); base.setEndtime(DateUtil.getCurrentDate14()).setWfstate(2).update(); String note = ConvertUtil.obj2StrBlank(request.getParameter("flowNote")); SC_WORKFLOW_CI_ADD ciAddOrder = new SC_WORKFLOW_CI_ADD(orderId).getInstanceById(); String flowId = ciAddOrder.getFlow_id(); //baseDao.execute("update CMDB_CI_BASE set usingstate=1 where FLOW_ID=:flowId", new SqlParameter("flowId", flowId)); doFinish(request); WorkFlowSupportEntity support = new WorkFlowSupportEntity(); support.setFlowId(flowId); support.setNote(note); support.setNodeId(nodeId); support.setUserName(userName); support.setUserId(userId); support.setWorkFlowType(Constants.WORKFLOW_BASE_BUSINESS_TYPE_CI_ADD); SysInfoMsg msg = workFlowSupportService.doOrderFlow(support); return msg; } @Override public int queryCiAddOrderCountTwo(Map paramMap,HttpServletRequest request) { String userId = WebUtil.getLoginedUserId(request); //String partnerId = WebUtil.getLoginedPartnerId(request); String flowState = ConvertUtil.obj2StrBlank(paramMap.get("flowState")); paramMap.put("userId", userId); //paramMap.put("partnerId", partnerId); List projects = new ArrayList(); //projects = WebUtil.getProjects(request); StringBuilder builder = new StringBuilder(); builder.append(" select b.PROJECT_NAME,b.ID FLOWID,b.WFNAME,b.CREATERNAME,b.CREATETIME,b.CURRENT_NODE_NAME,b.BUSINESSTYPE, "); builder.append(" r.ORDER_CODE,r.ID ORDERID,r.APPLY_REASON,b.CUSTOMER_NAME,b.SUB_CUSTOMER_NAME,B.WFSTATE,n.CURRENT_DEALER_NAME,n.FLOWSTATE NODESTATE "); builder.append(" from SC_WORKFLOW_CI_ADD r "); builder.append(" inner join WORKFLOW_BASE b on r.FLOW_ID = b.ID "); /*if(StringUtil.isNotBlank(nodeTemplateId)){*/ builder.append(" inner join WORKFLOW_NODE N on b.CURRENT_NODE_ID = n.ID "); builder.append(" inner join gg_user g on b.CREATERID = g.ID "); /*}*/ builder.append(" where 1=1 "); //流程状态 if(StringUtil.notEmpty(flowState)) { builder.append(" AND b.WFSTATE = :flowState"); } builder.append(" order by b.CREATETIME desc "); Map supportMap = new HashMap(); supportMap.put("sql",builder); supportMap.put("param", paramMap); String sql = "select count(*) from ( " + supportMap.get("sql").toString() + " ) t"; return baseDao.queryForInteger(sql,(Map)supportMap.get("param")); } }