package cn.ksource.web.facade.cfg; import cn.ksource.beans.CMDB_CI_CATEGORY; import cn.ksource.beans.FlowRecord; import cn.ksource.core.dao.BaseDao; import cn.ksource.core.dao.SqlParameter; import cn.ksource.core.page.PageInfo; import cn.ksource.core.util.*; import cn.ksource.core.web.WebUtil; import cn.ksource.web.Constants; import cn.ksource.web.facade.cfgcate.CfgcateFacade; import cn.ksource.web.facade.customermanage.CustomerManageFacade; import cn.ksource.web.service.cmdb.SequenceService; import cn.ksource.web.service.flow.FlowRecordService; 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.apache.poi.poifs.filesystem.OfficeXmlFileException; import org.springframework.stereotype.Service; import org.springframework.web.multipart.commons.CommonsMultipartFile; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.*; @Service @SuppressWarnings("rawtypes") public class CfgFacadeImpl implements CfgFacade { @Resource private BaseDao baseDao; @Resource private CfgcateFacade cfgcateFacade; @Resource private FlowRecordService flowRecordService; @Resource private CustomerManageFacade customerFacade; @Resource private SequenceService sequenceService; private Map getCfgcateSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); String gb_state = ConvertUtil.obj2StrBlank(params.get("gb_state")); sql.append(" select a.*,concat(a.lv1_name,'->',a.lv2_name,'->',a.lv3_name) catename,b.state fstate "); sql.append(" from cmdb_ci_category e,cmdb_ci_base a left join cmdb_ci_flow b on a.bus_id = b.id "); sql.append(" where 1=1 and e.id=a.lv3_id and e.state=1 "); // 2 已过保 1 未过保 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("gbFlag")))) { if ("2".equals(params.get("gbFlag"))) { sql.append(" AND TIMESTAMPDIFF(DAY,DATE_FORMAT(now(), '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) <= 0"); } } //创建人 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("createId")))) { sql.append(" and a.create_id=:createId and a.bus_id<>'' "); } //流程通过标识 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("fstate_wc")))) { sql.append(" and (b.state=:fstate_wc or b.id is null) "); } //流程状态 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("fstate")))) { sql.append(" and b.state=:fstate "); } //配置状态 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("state")))) { sql.append(" and a.state=:state "); } //当前处理人 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("dealId")))) { sql.append(" and b.cur_id=:dealId "); } //分类id if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("lv3_id")))) { sql.append(" and a.lv3_id=:lv3_id "); } //搜索码 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("search_code")))) { params.put("search_code", "%" + params.get("search_code") + "%"); sql.append(" and a.searchcode like :search_code "); } //审计状态 if(StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("sjState")))){ sql.append(" and a.sj_state = :sjState "); } //过保查询结束时间 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("overEndDate")))) { sql.append(" AND TIMESTAMPDIFF(DAY,DATE_FORMAT(now(), '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) >0 " + " AND TIMESTAMPDIFF(DAY,DATE_FORMAT(:overEndDate, '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) <= 0"); } String gbState = ConvertUtil.obj2StrBlank(params.get("gb_state")); //过保状态 if (StringUtil.isNotBlank(gbState)) { if (gbState.equals(String.valueOf(Constants.CFG_GB_STATE_YZN))) { sql.append(" AND TIMESTAMPDIFF(DAY,DATE_FORMAT(now(), '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) < 7 " + " AND TIMESTAMPDIFF(DAY,DATE_FORMAT(now(), '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) > 0"); } else if (gbState.equals(String.valueOf(Constants.CFG_GB_STATE_LZN))) { sql.append(" AND TIMESTAMPDIFF(DAY,DATE_FORMAT(now(), '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) < 14 " + " AND TIMESTAMPDIFF(DAY,DATE_FORMAT(now(), '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) > 0"); } else if (gbState.equals(String.valueOf(Constants.CFG_GB_STATE_YGY))) { sql.append(" AND TIMESTAMPDIFF(DAY,DATE_FORMAT(now(), '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) < 30 " + " AND TIMESTAMPDIFF(DAY,DATE_FORMAT(now(), '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) > 0"); } else if (gbState.equals(String.valueOf(Constants.CFG_GB_STATE_SGY))) { sql.append(" AND TIMESTAMPDIFF(DAY,DATE_FORMAT(now(), '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) < 90 " + " AND TIMESTAMPDIFF(DAY,DATE_FORMAT(now(), '%Y%m%d'),DATE_FORMAT(a.GB_TIME, '%Y%m%d')) > 0"); } } //配置分类 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("categoryId")))) { sql.append(" and a.LV3_ID=:categoryId "); } //客户 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("customerId")))) { sql.append(" and a.CUS_ID=:customerId "); } // //子项目 // if(StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("sub_customer_id")))){ // sql.append(" and a.SUB_CUS_ID=:sub_customer_id "); // } //配置名称 if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("name")))) { params.put("name", "%"+ConvertUtil.obj2StrBlank(params.get("name"))+"%"); sql.append(" and a.CINAME like:name "); } String subId = ConvertUtil.obj2StrBlank(params.get("subId")); String subChildId = ConvertUtil.obj2StrBlank(params.get("subChildId")); if (StringUtil.notEmpty(subChildId)) { sql.append(" AND a.SUB_CUS_ID = :subChildId "); }else if(StringUtil.notEmpty(subId)) { String sqlb="select id from SC_PARTNER_CUSTOMER_SUB where p_id=:subId and MY_LEVEL=2 and DEL_FLAG=1"; List list1=baseDao.queryForList(sqlb, params); if(list1.size()>0){ String str=""; for(Map map:list1){ str+=ConvertUtil.obj2StrBlank(map.get("id"))+","; } str=str+subId; params.put("subId", Arrays.asList(str.split(","))); sql.append(" AND a.SUB_CUS_ID in( :subId) "); } } if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("templateKey")))) { sql.append(" and exists ( "); sql.append(" select r.bus_id from ( "); sql.append(" select bus_id from flow_record where deal_id=:dealId and template_key=:templateKey "); sql.append(" group by bus_id ) r where r.bus_id = b.id ) "); } sql.append(" order by a.create_time desc "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public PageInfo getCfgData(PageInfo pageInfo, Map params) { Map support = getCfgcateSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Object getCfgCount(Map params) { Map support = getCfgcateSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } @Override public Map getCfgById(String id) { return baseDao.queryForMap("select * from cmdb_ci_base where id=:id ", new SqlParameter("id", id)); } @Override public Map getCfgByIdTwo(String id,HttpServletRequest request) { Map map=baseDao.queryForMap("select * from cmdb_ci_base where id=:id ", new SqlParameter("id", id)); //生成二维码 TwoDimensionCode handler = new TwoDimensionCode(); String searchCode = ConvertUtil.obj2StrBlank(map.get("SEARCHCODE")); String url = Constants.QRCODE_URL + id; String logoUrl = WebUtil.getRealPath(request, "/static/ui/images/ewmlogo.jpg"); handler.encoderQRCode(url, WebUtil.getRealPath(request, "/upload/qrCode/" + searchCode + ".png"), "png", logoUrl); return map; } @Override public void saveCfg(Map params, HttpServletRequest request) { params.put("state", "1"); StringBuilder sql = new StringBuilder(); if (StringUtil.isNotBlank(params.get("full_cate_id"))) { String[] cateIdArr = params.get("full_cate_id").split("-"); String[] cateNameArr = params.get("full_cate_name").split("-"); params.put("lv1_id", cateIdArr[0]); params.put("lv1_name", cateNameArr[0]); params.put("lv2_id", cateIdArr[1]); params.put("lv2_name", cateNameArr[1]); params.put("lv3_id", cateIdArr[2]); params.put("lv3_name", cateNameArr[2]); } if (StringUtil.isBlank(params.get("buy_time"))) { params.put("buy_time", null); } if (StringUtil.isBlank(params.get("gb_time"))) { params.put("gb_time", null); } if (StringUtil.isBlank(params.get("xb_time"))) { params.put("xb_time", null); } if (StringUtil.isBlank(params.get("xbgb_time"))) { params.put("xbgb_time", null); } if (StringUtil.isBlank(params.get("id"))) { String id = StringUtil.getUUID(); params.put("id", id); String searchcode = "SH" + GenerateUtil.generate16Id(); params.put("searchcode", searchcode); params.put("create_time", DateUtil.getCurrentDate14().toString()); if (StringUtil.isNotBlank(params.get("flow_flag"))) {//需要创建流程 params.put("state", "0"); params.put("bus_id", StringUtil.getUUID()); saveCfgFlow(params); } //生成二维码 TwoDimensionCode handler = new TwoDimensionCode(); String searchCode = searchcode; String url = Constants.QRCODE_URL + id; String logoUrl = WebUtil.getRealPath(request, "/static/ui/images/ewmlogo.jpg"); handler.encoderQRCode(url, WebUtil.getRealPath(request, "/upload/qrCode/" + searchCode + ".png"), "png", logoUrl); sql.append("insert into cmdb_ci_base (id,bus_id,ciname,searchcode,sn_no,zr_name,zr_dept,lv1_id,"); sql.append("lv1_name,lv2_id,lv2_name,lv3_id,lv3_name,mft_id,mft_name,igt_id,igt_name,create_time,"); sql.append("position,note,buy_time,gb_time,xb_time,xbgb_time,state,create_id,create_name,cus_id,cus_name,sub_cus_id,sub_cus_name) values ("); sql.append(":id,:bus_id,:ciname,:searchcode,:sn_no,:zr_name,:zr_dept,:lv1_id,:"); sql.append("lv1_name,:lv2_id,:lv2_name,:lv3_id,:lv3_name,:mft_id,:mft_name,:igt_id,:igt_name,:create_time,:"); sql.append("position,:note,:buy_time,:gb_time,:xb_time,:xbgb_time,:state,:create_id,:create_name,:cus_id,:cus_name,:sub_cus_id,:sub_cus_name)"); } else {//修改 params.put("up_time", DateUtil.getCurrentDate14().toString()); sql.append(" update cmdb_ci_base set ciname=:ciname,sn_no=:sn_no,zr_name=:zr_name, "); sql.append(" zr_dept=:zr_dept, "); sql.append(" mft_id=:mft_id,mft_name=:mft_name,igt_id=:igt_id,igt_name=:igt_name,position=:position,note=:note,buy_time=:buy_time, "); sql.append(" gb_time=:gb_time,xb_time=:xb_time,xbgb_time=:xbgb_time,cus_id=:cus_id,cus_name=:cus_name,sub_cus_id=:sub_cus_id,sub_cus_name=:sub_cus_name,last_up_time=:up_time where id=:id "); } baseDao.execute(sql.toString(), params); params.put("busId", params.get("id")); params.put("linkId", params.get("lv3_id")); saveProp(params); saveDefRcItem(params); saveDefRemindItem(params); saveDefHealthItem(params); } /** * 保存默认日常巡检指标 * * @param params * @author chenlong */ private void saveDefRcItem(Map params) { baseDao.execute("delete from ci_daily_ci_item_re where ci_id=:id ", params); List list = getRcItemList(params); if (list != null && list.size() > 0) { List pList = new ArrayList(); String sql = " insert into ci_daily_ci_item_re (id,cus_item_id,ci_id) " + " values (:id,:cus_item_id,:ci_id) "; for (Map map : list) { SqlParameter p = new SqlParameter(); p.addValue("id", StringUtil.getUUID()) .addValue("cus_item_id", map.get("cus_item_id")) .addValue("ci_id", params.get("id")); pList.add(p); } baseDao.executeBatch(sql, pList); } } /** * 保存默认例行维护指标 * * @param params * @author chenlong */ private void saveDefRemindItem(Map params) { baseDao.execute("delete from ci_remind_cilist where CI_ID=:id ", params); List list = getRemindItemList(params); if (list != null && list.size() > 0) { List pList = new ArrayList(); String sql = " insert into ci_remind_cilist (id,project_item_id,ci_id,state) " + " values (:id,:project_item_id,:ci_id,1) "; for (Map map : list) { SqlParameter p = new SqlParameter(); p.addValue("id", StringUtil.getUUID()) .addValue("project_item_id", map.get("cus_item_id")) .addValue("ci_id", params.get("id")); pList.add(p); } baseDao.executeBatch(sql, pList); } } /** * 保存默认健康检查指标 * * @param params * @author chenlong */ private void saveDefHealthItem(Map params) { baseDao.execute("delete from ci_health_customize_detail where CI_ID=:id ", params); List list = getHealthItemList(params); if (list != null && list.size() > 0) { List pList = new ArrayList(); String sql = " insert into ci_health_customize_detail (id,customer_item_id,ci_id,state) " + " values (:id,:customer_item_id,:ci_id,1) "; for (Map map : list) { SqlParameter p = new SqlParameter(); p.addValue("id", StringUtil.getUUID()) .addValue("customer_item_id", map.get("cus_item_id")) .addValue("ci_id", params.get("id")); pList.add(p); } baseDao.executeBatch(sql, pList); } } /** * 保存流程信息 * * @param params * @author chenlong */ private void saveCfgFlow(Map params) { StringBuilder sql = new StringBuilder(); sql.append(" insert into cmdb_ci_flow (id,cfg_id,create_id,create_name,create_time,state) "); sql.append(" values (:bus_id,:id,:create_id,:create_name,:create_time,1) "); baseDao.execute(sql.toString(), params); } private void saveProp(Map params) { String linkId = ConvertUtil.obj2StrBlank(params.get("linkId")); String busId = ConvertUtil.obj2StrBlank(params.get("busId")); //删除所有的属性配置信息 String delSql = "delete from prop_data where bus_id = :busId "; baseDao.execute(delSql, new SqlParameter("busId", busId)); List glist = cfgcateFacade.getPropTemplate(linkId, null); if (glist != null && glist.size() > 0) { List pList = new ArrayList(); for (Map g : glist) { if (g.get("props") != null) { List plist = (List) g.get("props"); for (Map p : plist) { String key = "p_" + ConvertUtil.obj2StrBlank(p.get("id").toString()); if (params.get(key) != null) { SqlParameter sqlParameter = new SqlParameter(); sqlParameter.addValue("id", StringUtil.getUUID()) .addValue("column_id", p.get("pid")) .addValue("column_code", p.get("columncode")) .addValue("column_name", p.get("columnname")) .addValue("busId", busId) .addValue("column_text", ConvertUtil.obj2StrBlank(params.get(key + "_text"))) .addValue("column_value", ConvertUtil.obj2StrBlank(params.get(key))); pList.add(sqlParameter); } } } } String insertSql = "insert into prop_data(id,bus_id,column_id,column_code,column_name,column_value,column_text) VALUES (:id,:busId,:column_id,:column_code,:column_name,:column_value,:column_text)"; baseDao.executeBatch(insertSql, pList); } } @Override public void doToNext(Map params, FlowRecord flowRecord) { StringBuilder sql = new StringBuilder(); sql.append(" update cmdb_ci_flow set state=:state "); if (StringUtil.isNotBlank(params.get("curId"))) { sql.append(" ,cur_id=:curId,cur_name=:curName "); } if (params.get("state").equals(Constants.CFG_FLOW_DSH.toString())) { sql.append(" ,times=ifnull(times,0)+1 "); } sql.append(" where id=:busId "); baseDao.execute(sql.toString(), params); //更新配置状态 if (params.get("state").equals(Constants.CFG_FLOW_WC.toString())) { sql.setLength(0); sql.append(" update cmdb_ci_base set state=1,last_up_time=:curDate where id=:cfgId "); params.put("curDate", DateUtil.getCurrentDate14().toString()); baseDao.execute(sql.toString(), params); } //记录流程信息 flowRecordService.addRecord(flowRecord); } @Override public PageInfo getCfgLinkOrderData(PageInfo pageInfo, Map params) { Map support = getCfgLinkOrderSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getCfgLinkOrderCount(Map params) { Map support = getCfgLinkOrderSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getCfgLinkOrderSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select a.* "); sql.append(" from workflow_base a,cmdb_ci_ref_order b "); sql.append(" where a.id = b.flow_id and b.ci_id=:id "); sql.append(" order by a.createtime desc "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public PageInfo getCfgLinkKnowData(PageInfo pageInfo, Map params) { Map support = getCfgLinkKnowSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getCfgLinkKnowCount(Map params) { Map support = getCfgLinkKnowSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getCfgLinkKnowSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select a.* "); sql.append(" from km_library a,cmdb_ci_base b "); sql.append(" where a.thirdlevel_id = b.lv3_id and b.id=:id "); sql.append(" order by a.create_time desc "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public PageInfo getCfgDocData(PageInfo pageInfo, Map params) { Map support = getCfgDocSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getCfgDocCount(Map params) { Map support = getCfgDocSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getCfgDocSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select a.* "); sql.append(" from gg_files a "); sql.append(" where a.business_id = :id "); sql.append(" order by a.create_time desc "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public PageInfo getCfgDailyItemData(PageInfo pageInfo, Map params) { Map support = getCfgDailyItemSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Object getCfgDailyItemCount(Map params) { Map support = getCfgDailyItemSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getCfgDailyItemSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select a.ID,a.ITEM_NAME,a.NORMAL_VALUE,a.ORDER_NUM,a.STATE,a.BUSINESS_ID,a.BUSINESS_NAME,a.ITEM_TYPE,a.CUSTOMER_ID,a.P_ID from ci_daily_patrol_item a ,cmdb_ci_base b,ci_daily_patrol_customer_item c,ci_daily_ci_item_re d "); sql.append(" where (b.lv2_id = a.business_id or b.lv3_id = a.business_id) and b.id= :id and d.ci_id = :id and c.item_id = a.id and c.id =d.cus_item_id and c.using_state = 1 and a.state = 1 "); sql.append(" group by a.ID,a.ITEM_NAME,a.NORMAL_VALUE,a.ORDER_NUM,a.STATE,a.BUSINESS_ID,a.BUSINESS_NAME,a.ITEM_TYPE,a.CUSTOMER_ID,a.P_ID "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public PageInfo getCfgRemindItemData(PageInfo pageInfo, Map params) { Map support = getCfgReindItemSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getCfgRemindItemCount(Map params) { Map support = getCfgReindItemSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getCfgReindItemSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select a.* from ci_remind_item a,cmdb_ci_base b,ci_remind_customer_item c,ci_remind_cilist d "); sql.append(" where (b.lv2_id = a.business_id or b.lv3_id = a.business_id) and b.id= :id and d.ci_id = :id and c.item_id = a.id and c.id =d.project_item_id and c.using_state = 1 and a.state = 1 "); sql.append(" group by a.ID,a.ITEM_NAME,a.CYCLE,a.PRIORITY_LEVEL_ID,a.PRIORITY_LEVEL_NAME,a.ITEM_NOTE,a.ORDER_NUM,a.STATE,a.BUSINESS_ID,a.BUSINESS_NAME,a.ITEM_TYPE,a.CUSTOMER_ID,a.P_ID "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public PageInfo getCfgHealthItemData(PageInfo pageInfo, Map params) { Map support = getCfgHealthItemSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getCfgHealthItemCount(Map params) { Map support = getCfgHealthItemSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getCfgHealthItemSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select a.ID,a.ITEM_NAME,a.PRIORITY_LEVEL_ID,a.PRIORITY_LEVEL_NAME,a.ITEM_NOTE,a.EXPECT_VALUE,a.ORDER_NUM,a.STATE,a.BUSINESS_ID,a.BUSINESS_NAME,a.ITEM_TYPE,a.CUSTOMER_ID,a.P_ID from ci_health_item a,cmdb_ci_base b,ci_health_customer_item c,ci_health_customize_detail d "); sql.append(" where (b.lv2_id = a.business_id or b.lv3_id = a.business_id) and b.id= :id and d.ci_id = :id and c.item_id = a.id and c.id =d.customer_item_id and c.using_state = 1 and a.state = 1 "); sql.append(" group by a.ID,a.ITEM_NAME,a.PRIORITY_LEVEL_ID,a.PRIORITY_LEVEL_NAME,a.ITEM_NOTE,a.EXPECT_VALUE,a.ORDER_NUM,a.STATE,a.BUSINESS_ID,a.BUSINESS_NAME,a.ITEM_TYPE,a.CUSTOMER_ID,a.P_ID "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public PageInfo getCfgRelationData(PageInfo pageInfo, Map params) { Map support = getCfgRelationSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getCfgRelationCount(Map params) { Map support = getCfgRelationSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getCfgRelationSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select a.*,b.ciname,b.lv1_name,b.lv2_name,b.lv3_name,b.searchcode from cmdb_ci_relation a, cmdb_ci_base b "); sql.append(" where a.subciid = b.id and a.mainciid = :id "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public List queryRelationLink(Map params) { StringBuffer sql = new StringBuffer(); sql.append(" select a.* from cmdb_ci_base a where a.state =1 and a.id<>:ci_id "); sql.append(" and not exists ( "); sql.append(" select subciid from cmdb_ci_relation b where mainciid =:ci_id "); sql.append(" and b.subciid = a.id "); sql.append(" ) "); if (StringUtil.notEmpty(params.get("searchCode"))) { sql.append(" and a.searchcode like :searchCode "); params.put("searchCode", "%" + params.get("searchCode") + "%"); } if (StringUtil.notEmpty(params.get("ciName"))) { sql.append(" and a.ciname like :ciName "); params.put("ciName", "%" + params.get("ciName") + "%"); } List cilist = baseDao.queryForList(sql.toString(), params); return cilist; } @Override public String saveRelation(Map params) { String relation = params.get("relation"); String ids = params.get("ids"); String ci_id = params.get("ci_id"); String insertSql = "insert into cmdb_ci_relation(id,mainciid,subciid,relation,create_time) values (:id,:mainciid,:subciid,:relation,:create_time)"; long create_time = DateUtil.getCurrentDate14(); List plist = new ArrayList(); String[] linkIds = ids.split(","); List listParamsRecord = new ArrayList(); for (int i = 0; i < linkIds.length; i++) { SqlParameter sqlParameter = new SqlParameter(); sqlParameter.put("id", StringUtil.getUUID()); sqlParameter.put("mainciid", ci_id); sqlParameter.put("subciid", linkIds[i]); sqlParameter.put("relation", relation); sqlParameter.put("create_time", create_time); plist.add(sqlParameter); } baseDao.executeBatch(insertSql, plist); return "1"; } @Override public Map getRelationById(String id) { return baseDao.queryForMap(" select * from cmdb_ci_relation where id =:id ", new SqlParameter("id", id)); } @Override public String updateRelation(Map params) { baseDao.execute("update cmdb_ci_relation set relation = :relation where id=:id ", params); return "1"; } @Override public void delRelationById(String id) { baseDao.execute("delete from cmdb_ci_relation where id=:id ", new SqlParameter("id", id)); } @Override public PageInfo getCfgLinkData(PageInfo pageInfo, Map params) { Map support = getCfgLinkSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getCfgLinkCount(Map params) { Map support = getCfgLinkSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getCfgLinkSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select a.*,b.relation from cmdb_ci_base a,cmdb_ci_relation b where a.id=b.subciid and b.mainciid= :id "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public List getMftList(String cusId, String type) { String sql = "select id,manufacturername name from cmdb_mainufacturer where state=1 and customer_id = :cusId and type = :type"; return baseDao.queryForList(sql, new SqlParameter().addValue("cusId", cusId).addValue("type", type)); } @Override public PageInfo getCfgDailyReportData(PageInfo pageInfo, Map params) { Map support = getCfgDailyReportSupport(params); String sql = support.get("sql").toString(); return baseDao.queryforSplitPageInfo(pageInfo, sql, params); } @Override public Integer getCfgDailyReportCount(Map params) { Map support = getCfgDailyReportSupport(params); String sql = "select count(*) from ( " + support.get("sql").toString() + " ) t "; return baseDao.queryForInteger(sql, params); } private Map getCfgDailyReportSupport(Map params) { Map support = new HashMap(); StringBuilder sql = new StringBuilder(); sql.append(" select b.* from ci_daily_patrol_record a,ci_daily_patrol b where a.patrol_id = b.id "); sql.append(" and a.ci_id = :id "); if (StringUtil.isNotBlank(params.get("cusId"))) { sql.append(" and b.cus_id = :cusId "); } sql.append(" order by b.create_time desc "); support.put("sql", sql.toString()); support.put("param", params); return support; } @Override public List getCfgcateList(Map params) { StringBuilder sql = new StringBuilder(); sql.append(" select a.*,count(a.id) num,b.id bid from cmdb_ci_category a "); if (StringUtil.isBlank(params.get("lv2_id"))) { sql.append(" left join (select c.*,d.state fstate from cmdb_ci_category e,cmdb_ci_category f,cmdb_ci_base c left join cmdb_ci_flow d ON c.bus_id = d.id where (d.state = '4' OR d.id IS NULL) "); if(StringUtil.notEmpty(params.get("cus_id"))){ sql.append(" and c.cus_id=:cus_id "); } sql.append(" and c.state=1 and c.LV2_ID=e.id and c.LV3_ID=f.id and e.STATE=1 and f.STATE=1 ) b on (a.id = b.lv1_id or a.id = b.lv2_id)"); } else { sql.append(" left join (select c.*,d.state fstate from cmdb_ci_category e,cmdb_ci_category f,cmdb_ci_base c left join cmdb_ci_flow d ON c.bus_id = d.id where (d.state = '4' OR d.id IS NULL) "); if(StringUtil.notEmpty(params.get("cus_id"))){ sql.append(" and c.cus_id=:cus_id "); } sql.append(" and c.state=1 and c.LV2_ID=e.id and c.LV3_ID=f.id and e.STATE=1 and f.STATE=1 ) b on (a.id = b.lv3_id or a.id = b.lv2_id) "); } sql.append(" where a.state=1 and cate_type=1 "); if (StringUtil.isBlank(params.get("lv2_id"))) { sql.append(" and (a.lv=1 or a.lv=2) "); } else { sql.append(" and (a.pid=:lv2_id or a.id=:lv2_id) "); } sql.append(" group by a.id order by a.lv asc "); List list = baseDao.queryForList(sql.toString(), params); if (StringUtil.isBlank(params.get("lv2_id"))) { return TreeUtil.createTreeByList(list); } else { return list; } } @Override public List getCatePie(Map params) { StringBuilder sql = new StringBuilder(); sql.append(" select a.name,count(b.id) y from cmdb_ci_category a,cmdb_ci_category e,cmdb_ci_category f,(SELECT c.*, d.state fstate FROM cmdb_ci_base c LEFT JOIN cmdb_ci_flow d ON c.bus_id = d.id WHERE (d.state = '4' OR d.id IS NULL)) b "); sql.append(" where a.id=b.lv1_id and a.lv=1 and e.id=b.lv2_id and f.id=b.lv3_id and e.state=1 and f.state=1 and a.state=1 and e.pid=a.id and f.pid=e.id and b.state=1 "); if (StringUtil.isNotBlank(params.get("cusId"))) { sql.append(" and b.cus_id=:cusId "); } sql.append(" group by a.id "); return baseDao.queryForList(sql.toString(), params); } @Override public List getRcItemList(Map param) { String sql = " select a.id cus_item_id,c.id flag,b.* from " + " ci_daily_patrol_customer_item a " + " inner join ci_daily_patrol_item b on a.item_id = b.id " + " left join ci_daily_ci_item_re c on a.id = c.cus_item_id and c.ci_id = :id " + " where (b.business_id = :lv2_id or b.business_id = :lv3_id) " //+ " and a.customer_id=:cus_id and a.using_state=1 and b.state=1 "; + " and a.customer_id='-1' and a.using_state=1 and b.state=1 "; return baseDao.queryForList(sql, param); } @Override public List getRemindItemList(Map param) { String sql = " select a.id cus_item_id,c.id flag,b.* from " + " ci_remind_customer_item a " + " inner join ci_remind_item b on a.item_id = b.id " + " left join ci_remind_cilist c on a.id = c.project_item_id and c.ci_id = :id " + " where (b.business_id = :lv2_id or b.business_id = :lv3_id) " //+ " and a.customer_id=:cus_id and a.using_state=1 and b.state=1 " ; + " and a.customer_id='-1' and a.using_state=1 and b.state=1 "; return baseDao.queryForList(sql, param); } @Override public List getHealthItemList(Map param) { String sql = " select a.id cus_item_id,c.id flag,b.* from " + " ci_health_customer_item a " + " inner join ci_health_item b on a.item_id = b.id" + " left join ci_health_customize_detail c on a.id = c.customer_item_id and c.ci_id = :id " + " where (b.business_id = :lv2_id or b.business_id = :lv3_id) " //+ " and a.customer_id=:cus_id and a.using_state=1 and b.state=1 "; + " and a.customer_id='-1' and a.using_state=1 and b.state=1 "; return baseDao.queryForList(sql, param); } @Override public void saveItem(Map param) { saveRcItem(param); saveLxItem(param); saveJkItem(param); } @Override public Map queryCommonProps() { String selectSq1 = "select * from prop_column a where 1=1 and a.group_id =-1 and a.state=1"; List commonProps = baseDao.queryForList(selectSq1); Map resultMap = new HashMap(); List commonProp = new ArrayList(); List textareaProp = new ArrayList(); //如果通用属性扩展下为复选或单选，则查询出其属性 if (null != commonProps && commonProps.size() > 0) { commonProps = queryListValue(commonProps); for (Map m : commonProps) { int datatype = ConvertUtil.obj2Int(m.get("DATATYPE")); if (datatype == Constants.CMDB_CI_EXTEND_COLUMN_DATATYPE_TEXT || datatype == Constants.CMDB_CI_EXTEND_COLUMN_DATATYPE_MULTI_SELECT) { textareaProp.add(m); } else { commonProp.add(m); } } } resultMap.put("commonProp", commonProp); resultMap.put("textareaProp", textareaProp); return resultMap; } @Override public List queryGroupProps(String cate) { CMDB_CI_CATEGORY category = new CMDB_CI_CATEGORY(cate).getInstanceById(); String pid = category.getPid(); Map paramMap = new HashMap(); paramMap.put("pid", pid); paramMap.put("cate", cate); StringBuilder builder = new StringBuilder("SELECT D.* FROM ("); //先查询分组信息 String ejGroup = "SELECT A.*,2 AS CATE FROM PROP_GROUP A,CMDB_CI_CATEGORY A1 WHERE A.LINK_ID = A1.ID AND A.LINK_ID = :pid AND A.STATE = 1"; builder.append(ejGroup); builder.append(" UNION "); String sjGroup = "SELECT B.*,3 AS CATE FROM PROP_GROUP B,CMDB_CI_CATEGORY B1 WHERE B.LINK_ID = B1.ID AND B.LINK_ID = :cate AND B.STATE = 1"; builder.append(sjGroup); builder.append(") D ORDER BY D.CATE"); List groups = baseDao.queryForList(builder.toString(), paramMap); System.out.println(JsonUtil.list2Json(groups)); if (null != groups && groups.size() > 0) { String selectPropSql = "SELECT A.* FROM PROP_COLUMN A,PROP_GROUP B WHERE A.GROUP_ID = B.ID AND (B.LINK_ID = :pid OR B.LINK_ID = :cate ) AND A.STATE = 1 AND B.STATE = 1 ORDER BY A.SERIAL"; List props = baseDao.queryForList(selectPropSql, paramMap); Map cacheMap = new HashMap(); Map cacheMap2 = new HashMap(); if (null != props && props.size() > 0) { props = queryListValue(props); for (Map prop : props) { String groupId = ConvertUtil.obj2StrBlank(prop.get("GROUP_ID")); Map m = new HashMap(); int datatype = ConvertUtil.obj2Int(prop.get("DATATYPE")); if (datatype == Constants.CMDB_CI_EXTEND_COLUMN_DATATYPE_TEXT || datatype == Constants.CMDB_CI_EXTEND_COLUMN_DATATYPE_MULTI_SELECT) { m = cacheMap2; } else { m = cacheMap; } if (m.containsKey(groupId)) { List ls = (List) m.get(groupId); ls.add(prop); } else { List ls = new ArrayList(); ls.add(prop); m.put(groupId, ls); } } } for (Map group : groups) { group.put("props", cacheMap.get(group.get("ID"))); group.put("props1", cacheMap2.get(group.get("ID"))); } } return groups; } private List queryListValue(List props) { String selectSql = "SELECT * FROM prop_sel WHERE STATE = 1 ORDER BY SERIAL "; List items = baseDao.queryForList(selectSql); Map cacheMap = new HashMap(); if (null != items && items.size() > 0) { for (Map item : items) { String extendId = ConvertUtil.obj2StrBlank(item.get("COLUMN_ID")); if (cacheMap.containsKey(extendId)) { List list = (List) cacheMap.get(extendId); list.add(item); } else { List list = new ArrayList(); list.add(item); cacheMap.put(extendId, list); } } } for (Map prop : props) { String id = ConvertUtil.obj2StrBlank(prop.get("ID")); if (cacheMap.containsKey(id)) { prop.put("items", cacheMap.get(id)); } } return props; } /** * 保存日常巡检指标 * * @param param * @author chenlong */ private void saveRcItem(Map param) { baseDao.execute("delete from ci_daily_ci_item_re where ci_id=:ci_id", param); String rcItem = param.get("rcItem"); if (StringUtil.isNotBlank(rcItem)) { String ci_id = param.get("ci_id"); String[] arr = rcItem.split(","); String sql = " insert into ci_daily_ci_item_re (id,ci_id,cus_item_id)" + " values (:id,:ci_id,:cus_item_id) "; List pList = new ArrayList(); for (int i = 0; i < arr.length; i++) { SqlParameter p = new SqlParameter(); p.addValue("id", StringUtil.getUUID()) .addValue("ci_id", ci_id) .addValue("cus_item_id", arr[i]); pList.add(p); } baseDao.executeBatch(sql, pList); } } /** * 保存例行维护指标 * * @param param * @author chenlong */ private void saveLxItem(Map param) { baseDao.execute("delete from ci_remind_cilist where ci_id=:ci_id", param); String lxItem = param.get("lxItem"); if (StringUtil.isNotBlank(lxItem)) { String ci_id = param.get("ci_id"); String[] arr = lxItem.split(","); String sql = " insert into ci_remind_cilist (id,ci_id,project_item_id,state)" + " values (:id,:ci_id,:cus_item_id,1) "; List pList = new ArrayList(); for (int i = 0; i < arr.length; i++) { SqlParameter p = new SqlParameter(); p.addValue("id", StringUtil.getUUID()) .addValue("ci_id", ci_id) .addValue("cus_item_id", arr[i]); pList.add(p); } baseDao.executeBatch(sql, pList); } } /** * 保存健康检查指标 * * @param param * @author chenlong */ private void saveJkItem(Map param) { baseDao.execute("delete from ci_health_customize_detail where ci_id=:ci_id", param); String jkItem = param.get("jkItem"); if (StringUtil.isNotBlank(jkItem)) { String ci_id = param.get("ci_id"); String[] arr = jkItem.split(","); String sql = " insert into ci_health_customize_detail (id,ci_id,customer_item_id,state)" + " values (:id,:ci_id,:cus_item_id,1) "; List pList = new ArrayList(); for (int i = 0; i < arr.length; i++) { SqlParameter p = new SqlParameter(); p.addValue("id", StringUtil.getUUID()) .addValue("ci_id", ci_id) .addValue("cus_item_id", arr[i]); pList.add(p); } baseDao.executeBatch(sql, pList); } } @Override public String doUploadBatchCiInfo(CommonsMultipartFile file, HttpServletRequest request) { String flag = null; if (file != null) { String fileName = DateUtil.getCurrentDate14().toString() + ".xls"; String path = request.getSession().getServletContext() .getRealPath("/") + "upload/infofiles/" + fileName; File localFile = new File(path); HSSFWorkbook workbook = null; // 写文件到本地 try { file.transferTo(localFile); // 创建工作簿 workbook = new HSSFWorkbook(new FileInputStream(path)); // 导入excel表数据 flag = importData(request, workbook); } catch (IllegalStateException e) { e.printStackTrace(); flag = "文件格式错误"; } catch (IOException e) { e.printStackTrace(); flag = "文件格式错误"; } catch (OfficeXmlFileException e) { e.printStackTrace(); flag = "该文件不是标准的xls文件"; } catch (Exception e) { e.printStackTrace(); flag = "文件格式错误"; } } return flag; } private String importData(HttpServletRequest request, HSSFWorkbook workbook) { //创建参数List List paramList = new ArrayList(); String cate = request.getParameter("full_cate_id").split("-")[2]; //基础属性 Map props = queryCommonProps(); List textareaProp = (List) props.get("textareaProp"); List commonProps = (List) props.get("commonProp"); //扩展属性 List groupProps = queryGroupProps(cate); int length = 0; for (int i = 0; i < groupProps.size(); i++) { List props_1 = (List) ((Map) groupProps .get(i)).get("props1");// 含有多选 List props_ = (List) ((Map) groupProps .get(i)).get("props");// 含有单选 if (props_1 != null) { length += props_1.size(); } if (props_ != null) { length += props_.size(); } } //获取所有工作表 HSSFSheet sheet = workbook.getSheetAt(0); int rowNum = sheet.getLastRowNum(); if (rowNum < 2) { return "导入数据为空，请检查文件"; } //每一行数据 List> listMap = new ArrayList>(); for (int i = 2; i < rowNum + 1; i++) { HSSFRow row = sheet.getRow(i); if (row != null&&row.getCell(1)!=null) { Map map = new HashMap(); int cellNum = 14 + textareaProp.size() + commonProps.size() + length; for (int j = 1; j <= cellNum; j++) { HSSFCell cell = row.getCell(j); if (cell != null) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); map.put(j, cell.getStringCellValue()); } else { map.put(j, null); } } listMap.add(map); } else { continue; } } //项目名称 List statusList = customerFacade.getCusList(); Map statusMap = getReadyData(statusList); //通过三级编号，查询二级编号和一级编号 String selectSql = "SELECT A.ID AS LV3_ID,A.NAME AS LV3_NAME,C.ID AS LV1_ID,C.NAME AS LV1_NAME,B.ID AS LV2_ID,B.NAME AS LV2_NAME FROM CMDB_CI_CATEGORY A,CMDB_CI_CATEGORY B, CMDB_CI_CATEGORY C WHERE A.PID = B.ID AND B.PID=C.ID AND A.ID =:cate"; Map cateId = baseDao.queryForMap(selectSql, new SqlParameter("cate", cate)); String LV1_ID = ConvertUtil.obj2Str(cateId.get("LV1_ID")); String LV1_NAME = ConvertUtil.obj2Str(cateId.get("LV1_NAME")); String LV2_ID = ConvertUtil.obj2Str(cateId.get("LV2_ID")); String LV2_NAME = ConvertUtil.obj2Str(cateId.get("LV2_NAME")); String LV3_ID = ConvertUtil.obj2Str(cateId.get("LV3_ID")); String LV3_NAME = ConvertUtil.obj2Str(cateId.get("LV3_NAME")); String memberid = WebUtil.getLoginedUserId(request); String memberName = WebUtil.getUserName(request); sequenceService.nextCICategorySequence(cate); long createtime = DateUtil.getCurrentDate14(); StringBuffer baseSql = new StringBuffer(); baseSql.append("INSERT INTO `cmdb_ci_base` (`ID`,`CUS_ID`,`CUS_NAME`, `SUB_CUS_ID`, `SUB_CUS_NAME`, `CINAME`, `SEARCHCODE`, `SN_NO`, `ZR_NAME`,"); baseSql.append("`ZR_DEPT`,`LV1_ID`,`LV1_NAME`,`LV2_ID`,`LV2_NAME`,`LV3_ID`,`LV3_NAME`,`MFT_ID`,"); baseSql.append("`MFT_NAME`,`IGT_ID`,`IGT_NAME`,`CREATE_ID`, `CREATE_NAME`,`CREATE_TIME`,`POSITION`, `NOTE`,`BUY_TIME`, `GB_TIME`,`XB_TIME`,"); baseSql.append("`XBGB_TIME`,`STATE`)"); baseSql.append(" VALUES (:ID,:CUS_ID,:CUS_NAME,:SUB_CUS_ID,:SUB_CUS_NAME,:CINAME,:SEARCHCODE,:SN_NO,:ZR_NAME,"); baseSql.append(":ZR_DEPT,:LV1_ID,:LV1_NAME,:LV2_ID,:LV2_NAME,:LV3_ID,:LV3_NAME,:MFT_ID,"); baseSql.append(":MFT_NAME,:IGT_ID,:IGT_NAME,:CREATE_ID,:CREATE_NAME,:CREATE_TIME,:POSITION,:NOTE,:BUY_TIME,:GB_TIME,:XB_TIME,"); baseSql.append(":XBGB_TIME,:STATE)"); String cmdb_ci_data_storage_sql = "INSERT INTO PROP_DATA(ID,BUS_ID,COLUMN_ID,COLUMN_CODE,COLUMN_NAME,COLUMN_VALUE,COLUMN_TEXT) VALUES (:id,:bus_id,:column_id," + ":column_code,:column_name,:column_value,:column_text)"; List sqlParamterBase = new ArrayList(); List sqlParamterStorage = new ArrayList(); String regexDate = "^\\d{4}[0-1]{1}\\d{1}[0-3]{1}\\d{1}$"; List asset_numberList = new ArrayList(); for (int i = 0; i < listMap.size(); i++) { SqlParameter param = new SqlParameter(); Map map = listMap.get(i); if (map.isEmpty()) { return "请检查文件内容是否完整"; } //id param.put("LV1_ID", LV1_ID); param.put("LV1_NAME", LV1_NAME); param.put("LV2_ID", LV2_ID); param.put("LV2_NAME", LV2_NAME); param.put("LV3_ID", LV3_ID); param.put("LV3_NAME", LV3_NAME); String id = StringUtil.getUUID(); param.put("ID", id); param.put("CREATE_TIME", createtime); param.put("CREATE_ID", memberid); param.put("CREATE_NAME", memberName); String searchCode = "SH" + GenerateUtil.generate16Id(); param.put("SEARCHCODE", searchCode); param.put("STATE", 1); TwoDimensionCode handler = new TwoDimensionCode(); String url = Constants.QRCODE_URL + searchCode; String logoUrl = WebUtil.getRealPath(request, "/static/ui/images/ewmlogo.jpg"); handler.encoderQRCode(url, WebUtil.getRealPath(request, "/upload/qrCode/" + searchCode + ".png"), "png", logoUrl); for (int j = 1; j <= map.size(); j++) { if (j == 1) {//项目名称 String cus_id = customerFacade.queryByName(map.get(j) == null ? " " : map.get(j).toString().trim()); if (map.get(j) != null && StringUtil.notEmpty(cus_id)) { param.put("CUS_NAME", map.get(j)); param.put("CUS_ID", cus_id); } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，输入的项目名称数据库中不存在"; } continue; } if (j == 2) { //子项目 if (map.get(j) != null) { String sub_cus_name = map.get(j).toString().trim(); if (StringUtil.notEmpty(sub_cus_name)) { //子项目名称不等于项目名称 if (!sub_cus_name.equals(map.get(j - 1).toString().trim())) { String sub_cus_id = customerFacade.queryBySubname(sub_cus_name); //判断子项目中数据库中是否存在 如果存在 if (StringUtil.notEmpty(sub_cus_id)) { //判断子项目和项目是否关联关系 Integer flag = customerFacade.checkname(customerFacade.queryByName(map.get(j - 1).toString().trim()), sub_cus_id); //如果有关联关系 if (flag > 0) { param.put("SUB_CUS_NAME", map.get(j)); param.put("SUB_CUS_ID", sub_cus_id); } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，输入的子项目和项目名称不是关联关系"; } //输入的项目名称数据库中不存在 } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，输入的子项目名称数据库中不存在"; } //子项目名称等于项目名称 } else { param.put("SUB_CUS_NAME", map.get(j)); param.put("SUB_CUS_ID", customerFacade.queryByName(map.get(j - 1).toString().trim())); } } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，子项目名称不能为空"; } } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，子项目名称不能为空"; } continue; } if (j == 3) {//配置名称 if (map.get(j) != null) { param.put("CINAME", map.get(j)); } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，配置名称不能为空"; } continue; } if (j == 4) {//SN序列号 if (map.get(j) != null) { param.put("SN_NO", map.get(j)); } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，SN序列号不能为空"; } continue; } if (j == 5) {//负责科室 param.put("ZR_DEPT", map.get(j)); continue; } if (j == 6) {//责任人，负责人 param.put("ZR_NAME", map.get(j)); continue; } if (j == 7) {//厂商 if (map.get(j) != null) { String MFT_ID = queryCsId(map.get(j),param.get("CUS_ID").toString()); if (!StringUtil.notEmpty(MFT_ID)) { return "第" + (i + 3) + "行，第" + (j + 1) + "列，厂商不存在"; } else { param.put("MFT_ID", MFT_ID); param.put("MFT_NAME", map.get(j)); } } else { param.put("MFT_ID", null); param.put("MFT_NAME", null); } continue; } if (j == 8) {//集成商 if (map.get(j) != null) { String IGT_ID = queryCsId(map.get(j),param.get("CUS_ID").toString()); if (!StringUtil.notEmpty(IGT_ID)) { return "第" + (i + 3) + "行，第" + (j + 1) + "列，集成商不存在"; } else { param.put("IGT_ID", IGT_ID); param.put("IGT_NAME", map.get(j)); } } else { param.put("IGT_ID", null); param.put("IGT_NAME", null); } continue; } if (j == 9) {//存放位置 param.put("POSITION", map.get(j)); continue; } if (j == 10) {//起保日期 if (StringUtil.isNotBlank(map.get(j))) { if (map.get(j).matches(regexDate)) { param.put("BUY_TIME", map.get(j)); } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，起保日期格式不正确"; } } else { param.put("BUY_TIME", null); } continue; } if (j == 11) {//过保时间 if (StringUtil.isNotBlank(map.get(j))) { if (map.get(j).matches(regexDate)) { param.put("GB_TIME", map.get(j)); } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，过保日期格式不正确"; } } else { param.put("GB_TIME", null); } continue; } if (j == 12) {//续保时间 if (StringUtil.isNotBlank(map.get(j))) { if (map.get(j).matches(regexDate)) { param.put("XB_TIME", map.get(j)); } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，续保时间格式不正确"; } } else { param.put("XB_TIME", null); } continue; } if (j == 13) {//续保过保时间 if (StringUtil.isNotBlank(map.get(j))) { if (map.get(j).matches(regexDate)) { param.put("XBGB_TIME", map.get(j)); } else { return "第" + (i + 3) + "行，第" + (j + 1) + "列，续保过保时间格式不正确"; } } else { param.put("XBGB_TIME", null); } continue; } if (textareaProp != null) { int size = textareaProp.size(); if (j <= size + 13) { for (int k = 0; k < textareaProp.size(); k++) { String cellValue = map.get(j); String allownull = textareaProp.get(k).get("ALLOWNULL").toString(); String columnName = textareaProp.get(k) .get("COLUMNNAME").toString(); if (StringUtil.isNotBlank(cellValue)) { String dataType = textareaProp.get(k) .get("DATATYPE").toString(); String columnCode = textareaProp.get(k) .get("COLUMNCODE").toString(); String columnID = textareaProp.get(k).get("ID") .toString(); String column_value = ""; if ("6".equals(dataType)) { List items = (List) textareaProp.get(k).get("items"); if(items==null){ items=new ArrayList(); } String[] cellValues = cellValue.replace( "，", ",").split(","); for (int l = 0; l < cellValues.length; l++) { boolean flag = false; for (int t = 0; t < items.size(); t++) { if (items.get(t).get("ITEMVALUE") .equals(cellValues[l])) { column_value += items.get(t) .get("ID"); if (l < cellValues.length - 1) { column_value += ","; } flag = true; } } if (!flag) { return "第" + (i + 3) + "行，第" + (j + 1) + "列，" + columnName + "列数据与系统数据不匹配"; } } } if (StringUtil.isEmpty(column_value)) { column_value = cellValue; } SqlParameter paramStorage = new SqlParameter(); paramStorage.put("id", StringUtil.getUUID()); paramStorage.put("bus_id", id); paramStorage.put("column_id", columnID); paramStorage.put("column_code", columnCode); paramStorage.put("column_name", columnName); paramStorage.put("column_value", column_value); paramStorage.put("column_text", cellValue); sqlParamterStorage.add(paramStorage); } else { if ("2".equals(allownull)) { return "第" + (i + 3) + "行，第" + (j + 1) + "列，" + columnName + "列不能为空"; } } j++; } } } if (commonProps != null) { int size = commonProps.size(); if (j <= size + 13 + textareaProp.size() && j > 13 + textareaProp.size()) { for (int k = 0; k < commonProps.size(); k++) { String cellValue = map.get(j); String allownull = commonProps.get(k).get("ALLOWNULL").toString(); String columnName = commonProps.get(k) .get("COLUMNNAME").toString(); if (StringUtil.isNotBlank(cellValue)) { String dataType = commonProps.get(k) .get("DATATYPE").toString(); String columnCode = commonProps.get(k) .get("COLUMNCODE").toString(); String columnID = commonProps.get(k).get("ID") .toString(); String column_value = ""; if ("5".equals(dataType)) { List items = (List) commonProps .get(k).get("items"); if(items==null){ items=new ArrayList(); } for (int t = 0; t < items.size(); t++) { if (items.get(t).get("ITEMVALUE") .equals(cellValue)) { column_value = items.get(t) .get("ID").toString(); break; } } } if ("4".equals(dataType)) { if (!cellValue.matches(regexDate)) { return "第" + (i + 3) + "行，第" + (j + 1) + "列，" + columnName + "列日期格式不正确"; } } if (StringUtil.isEmpty(column_value)) { column_value = cellValue; } SqlParameter paramStorage = new SqlParameter(); paramStorage .put("id", StringUtil.getUUID()); paramStorage.put("bus_id", id); paramStorage.put("column_id", columnID); paramStorage.put("column_code", columnCode); paramStorage.put("column_name", columnName); paramStorage.put("column_value", column_value); paramStorage.put("column_text", cellValue); sqlParamterStorage.add(paramStorage); } else { if ("2".equals(allownull)) { return "第" + (i + 3) + "行，第" + (j + 1) + "列，" + columnName + "列不能为空"; } } j++; } } } if (j == (commonProps.size() + 13 + textareaProp.size() + 1)) { param.put("NOTE", map.get(j)); continue; } //如果扩展属性不为空 if (groupProps != null) { int size = groupProps.size(); for (int k = 0; k < size; k++) { //多选的扩展属性 List props_1 = (List) ((Map) groupProps.get(k)).get("props1"); //单选的扩展属性 List props_ = (List) ((Map) groupProps.get(k)).get("props"); //如果多选的扩展属性不为空 if (props_1 != null) { //循环多选的扩展属性 for (int x = 0; x < props_1.size(); x++) { String cellValue = map.get(j); //当前多选的扩展属性是否运行为空 String allownull = props_1.get(x).get("ALLOWNULL").toString(); if (cellValue != null) { String dataType = props_1.get(x).get("DATATYPE").toString(); String columnName = props_1.get(x).get("COLUMNNAME").toString(); String columnCode = props_1.get(x).get("COLUMNCODE").toString(); String columnID = props_1.get(x).get("ID").toString(); String column_value = ""; if ("6".equals(dataType)) { List items = (List) (props_1.get(x).get("items")); if(items==null){ items=new ArrayList(); } String[] cellValues = cellValue.replace("，", ",").split(","); for (int l = 0; l < cellValues.length; l++) { boolean flag = false; for (int t = 0; t < items.size(); t++) { if (items.get(t).get("ITEMVALUE").equals(cellValues[l])) { column_value += items.get(t).get("ID"); if (l < cellValues.length - 1) { column_value += ","; } flag = true; } } if (!flag) { return "第" + (i + 3) + "行，第" + (j + 1) + "列，" + columnName + "列数据与系统数据不匹配"; } } } if (StringUtil.isEmpty(column_value)) { column_value = cellValue; } SqlParameter paramStorage = new SqlParameter(); paramStorage.put("id", StringUtil.getUUID()); paramStorage.put("bus_id", id); paramStorage.put("column_id", columnID); paramStorage.put("column_code", columnCode); paramStorage.put("column_name", columnName); paramStorage.put("column_value", column_value); paramStorage.put("column_text", cellValue); sqlParamterStorage.add(paramStorage); } else { if ("2".equals(allownull)) { String columnName = props_1.get(x) .get("COLUMNNAME").toString(); return "第" + (i + 3) + "行，第" + (j + 1) + "列，" + columnName + "列不能为空"; } } j++; } } if (props_ != null) { for (int x = 0; x < props_.size(); x++) { String cellValue = map.get(j); String allownull = props_.get(x) .get("ALLOWNULL").toString(); if (StringUtil.isNotBlank(cellValue)) { String dataType = props_.get(x) .get("DATATYPE").toString(); String columnName = props_.get(x) .get("COLUMNNAME").toString(); String columnCode = props_.get(x) .get("COLUMNCODE").toString(); String columnID = props_.get(x) .get("ID").toString(); String column_value = ""; if ("5".equals(dataType)) { List items = (List) (props_ .get(x).get("items")); for (int t = 0; t < items.size(); t++) { if (items.get(t) .get("ITEMVALUE") .equals(cellValue)) { column_value = items.get(t) .get("ID") .toString(); break; } } } if (StringUtil.isEmpty(column_value)) { column_value = cellValue; } SqlParameter paramStorage = new SqlParameter(); paramStorage.put("id", StringUtil.getUUID()); paramStorage.put("bus_id", id); paramStorage.put("column_id", columnID); paramStorage.put("column_code", columnCode); paramStorage.put("column_name", columnName); paramStorage.put("column_value", column_value); paramStorage.put("column_text", cellValue); sqlParamterStorage.add(paramStorage); } else { if ("2".equals(allownull)) { String columnName = props_.get(x) .get("COLUMNNAME").toString(); return "第" + (i + 3) + "行，第" + (j + 1) + "列，" + columnName + "列不能为空"; } } j++; } } } } } sqlParamterBase.add(param); } baseDao.executeBatch(baseSql.toString(), sqlParamterBase); baseDao.executeBatch(cmdb_ci_data_storage_sql, sqlParamterStorage); return null; } /** * @return */ private Map getReadyData(List list) { Map map = new HashMap(); for (int i = 0; i < list.size(); i++) { String keyName = ((Map) list.get(i)).get("name") .toString(); String valueId = ((Map) list.get(i)).get("ID").toString(); map.put(keyName, valueId); } return map; } /** * 根据厂商/集成商简称和项目id 查找该厂商/集成商的id * @param name customer_id * @return */ private String queryCsId(String name,String customer_id) { return baseDao.queryForString("select id from cmdb_mainufacturer where state=1 and manufacturername=:name and customer_id=:customer_id", new SqlParameter("name", name).addValue("customer_id",customer_id)); } ; @Override public List getMainCiRelationDetail(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("ciId", ciId)); } @Override public List getSubCiRelationDetail(String ciId) { String sql = " SELECT b.CINAME,A.*,A.RELATION AS RELATION_TYPE FROM CMDB_CI_RELATION A, CMDB_CI_BASE B " + " WHERE A.MAINCIID = B.ID AND A.SUBCIID = :ciId "; return baseDao.queryForList(sql, new SqlParameter().addValue("ciId", ciId)); } @Override public void saveSj(Map params) { String sql="update CMDB_CI_BASE set sj_state=:sjState,sj_user_id=:user_id,sj_user_name=:user_name,sj_time=:sj_time where id=:cfgId"; baseDao.execute(sql, params); } }