package cn.ksource.web.facade.tj;
|
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.annotation.Resource;
|
import javax.servlet.http.HttpServletRequest;
|
|
import org.springframework.stereotype.Service;
|
|
import cn.ksource.core.dao.BaseDao;
|
import cn.ksource.core.util.ConvertUtil;
|
import cn.ksource.core.util.JsonUtil;
|
import cn.ksource.core.util.StringUtil;
|
import cn.ksource.core.util.TreeUtil;
|
import cn.ksource.core.web.WebUtil;
|
@Service
|
public class CiStatisFacadeImpl implements CiStatisFacade{
|
@Resource
|
private BaseDao baseDao;
|
|
public Map getCiTypeChart(String cusId,String lv1Id,String lv2Id,String queryType){
|
if(StringUtil.isBlank(queryType)){
|
queryType = "CHART";
|
}
|
|
|
String level = "1";
|
String where_c = "";
|
String where_q = "";
|
|
String column = "lv1_id";
|
if(StringUtil.isNotBlank(lv1Id)){
|
if(StringUtil.isBlank(lv2Id)){
|
column = "lv2_id";
|
where_q = " and lv1_id =:lv1Id ";
|
level = "2";
|
where_c = " and PID =:lv1Id ";
|
|
}else{
|
column = "lv3_id";
|
where_q = " and lv2_id =:lv2Id ";
|
level = "3";
|
where_c = " and PID =:lv2Id ";
|
}
|
}
|
|
Map param = new HashMap();
|
param.put("cusId", cusId);
|
param.put("level", level);
|
param.put("lv1Id", lv1Id);
|
param.put("lv2Id", lv2Id);
|
|
StringBuilder sql = new StringBuilder();
|
String realSql;
|
sql.append(" select count(id) num,$column id from cmdb_ci_base ");
|
sql.append(" where state=1 and CUS_ID = :cusId $where ");
|
sql.append(" group by $column ");
|
realSql = sql.toString().replace("$column", column);
|
realSql = realSql.replace("$where", where_q);
|
|
List<Map> queryList = baseDao.queryForList(realSql,param);
|
Map queryMap = new HashMap();
|
if(queryList!=null&&queryList.size()>0){
|
for(Map map:queryList){
|
queryMap.put(map.get("id").toString(), map.get("num"));
|
}
|
}
|
|
sql.setLength(0);
|
sql.append(" select id,name from cmdb_ci_category where type=1 ");
|
sql.append(" and lv=:level $where and state=1 and CATE_TYPE=1 order by SERIAL asc ");
|
realSql = sql.toString().replace("$where", where_c);
|
List<Map> categoryList = baseDao.queryForList(realSql,param);
|
|
|
List<List<String>> series = new ArrayList<List<String>>();
|
List<Map> tableData = new ArrayList<Map>();
|
if(queryType.equals("CHART")){
|
for(Map map:categoryList){
|
List tempList = new ArrayList();
|
tempList.add(map.get("name").toString());
|
if(queryMap.get(map.get("id").toString())!=null){
|
tempList.add(queryMap.get(map.get("id").toString()));
|
}else{
|
tempList.add(0);
|
}
|
series.add(tempList);
|
}
|
}else{
|
for(Map map:categoryList){
|
Map tempMap = new HashMap();
|
tempMap.put("name", map.get("name").toString());
|
if(queryMap.get(map.get("id").toString())!=null){
|
tempMap.put("num", queryMap.get(map.get("id").toString()));
|
}else{
|
tempMap.put("num", "0");
|
}
|
tableData.add(tempMap);
|
}
|
}
|
|
System.out.println("----------"+JsonUtil.list2Json(tableData));
|
Map resMap = new HashMap();
|
resMap.put("series", series);
|
resMap.put("tableData", tableData);
|
return resMap;
|
}
|
|
public Map getCiCountChart(String cusId){
|
Map param = new HashMap();
|
param.put("cusId", cusId);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select total.lv3_id id,total.total_num," +
|
"IFNULL(normal.nor_num,'-') nor_num,IFNULL(abandon.abd_num,'-') abd_num," +
|
"IFNULL(new.new_num,'-') new_num from ");
|
sql.append(" ( ");
|
sql.append(" select count(id) total_num,lv3_id ");
|
sql.append(" from cmdb_ci_base where cus_ID = :cusId ");
|
sql.append(" group by lv3_id ");
|
sql.append(" ) total ");
|
sql.append(" LEFT JOIN ");
|
sql.append(" ( ");
|
sql.append(" select count(id) nor_num,lv3_id ");
|
sql.append(" from cmdb_ci_base where state = 1 and cus_ID = :cusId ");
|
sql.append(" group by lv3_id ");
|
sql.append(" ) normal ");
|
sql.append(" on total.lv3_id = normal.lv3_id ");
|
sql.append(" LEFT JOIN ");
|
sql.append(" ( ");
|
sql.append(" select count(id) abd_num,lv3_id ");
|
sql.append(" from cmdb_ci_base where state = 2 and cus_ID = :cusId ");
|
sql.append(" group by lv3_id ");
|
sql.append(" ) abandon ");
|
sql.append(" on total.lv3_id = abandon.lv3_id ");
|
sql.append(" LEFT JOIN ");
|
sql.append(" ( ");
|
sql.append(" select count(id) new_num,lv3_id ");
|
sql.append(" from cmdb_ci_base where state = 3 and cus_ID = :cusId ");
|
sql.append(" group by lv3_id ");
|
sql.append(" ) new ");
|
sql.append(" on total.lv3_id = new.lv3_id ");
|
List<Map> queryList = baseDao.queryForList(sql.toString(),param);
|
|
|
sql.setLength(0);
|
sql.append(" select id,name text,pid parentId,serial sortId ");
|
sql.append(" from cmdb_ci_category ");
|
sql.append(" where type=1 and state=1 and CATE_TYPE=1 ");
|
List<Map> categoryList = baseDao.queryForList(sql.toString(),param);
|
TreeUtil treeUtil = new TreeUtil();
|
Map rootMap = treeUtil.createTree(categoryList,queryList);
|
|
Map resMap = new HashMap();
|
resMap.put("rootMap", rootMap);
|
return resMap;
|
}
|
|
public List getCiCategoryList(String lv1Id){
|
StringBuilder sql = new StringBuilder();
|
Map param = new HashMap();
|
param.put("lv1Id", lv1Id);
|
|
sql.append(" select id,name from cmdb_ci_category ");
|
sql.append(" where type=1 and CATE_TYPE=1");
|
if(StringUtil.isBlank(lv1Id)){
|
sql.append(" and lv =1 ");
|
}else{
|
sql.append(" and lv =2 and pid = :lv1Id ");
|
}
|
sql.append(" and state=1 order by SERIAL ");
|
return baseDao.queryForList(sql.toString(),param);
|
}
|
|
|
public Map getCiMainufacturerChart(String cusId,String queryType){
|
|
String column;
|
if(queryType.equals("1")){
|
column = "MFT_ID";
|
}else{
|
column = "IGT_ID";
|
}
|
|
Map param = new HashMap();
|
param.put("cusId", cusId);
|
param.put("queryType", queryType);
|
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select id,MANUFACTURERNAME name from CMDB_MAINUFACTURER ");
|
sql.append(" where customer_id = :cusId and state=1 and type=:queryType ");
|
List<Map> lebalList = baseDao.queryForList(sql.toString(),param);
|
|
sql.setLength(0);
|
sql.append(" select id,name text,pid parentId,serial sortId ");
|
sql.append(" from cmdb_ci_category ");
|
sql.append(" where cate_type=1 and state=1 ");
|
List<Map> categoryList = baseDao.queryForList(sql.toString(),param);
|
|
sql.setLength(0);
|
sql.append(" select CONCAT(lv3_id,'_',$column) id,count(ID) num from cmdb_ci_base ");
|
sql.append(" where lv3_id is not null ");
|
sql.append(" and $column is not NULL and $column<>'' ");
|
sql.append(" and cus_ID = :cusId ");
|
sql.append(" group by lv3_id,$column ");
|
String realSql = sql.toString().replace("$column", column);
|
List<Map> queryList = baseDao.queryForList(realSql,param);
|
|
TreeUtil treeUtil = new TreeUtil();
|
Map rootMap = treeUtil.createTree(categoryList,queryList,lebalList);
|
|
Map resMap = new HashMap();
|
resMap.put("rootMap", rootMap);
|
resMap.put("lebalList", lebalList);
|
return resMap;
|
}
|
@SuppressWarnings("unchecked")
|
@Override
|
public List queryCiListData(HttpServletRequest request) {
|
String cusId = request.getParameter("cusId");
|
String subId = request.getParameter("subId");
|
String categoryId = request.getParameter("categoryId");
|
String subChildId = request.getParameter("subChildId");
|
String sjState=request.getParameter("sjState");
|
Map param = new HashMap();
|
param.put("cusId", cusId);
|
param.put("categoryId", categoryId);
|
param.put("subId", subId);
|
param.put("subChildId", subChildId);
|
param.put("sjState", sjState);
|
|
|
//开始查询
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select c.*,CONCAT(lv1.NAME,'->',lv2.name,'->',lv3.NAME) CATENAME,c.ID,c.CINAME,c.POSITION ");
|
sql.append(" from CMDB_CI_BASE c inner JOIN cmdb_ci_category lv3 on c.LV3_ID = lv3.ID ");
|
if (StringUtil.notEmpty(cusId)) {
|
sql.append(" AND c.CUS_ID = :cusId ");
|
}
|
if (StringUtil.notEmpty(subChildId)) {
|
sql.append(" AND c.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<Map> list1=baseDao.queryForList(sqlb, param);
|
if(list1.size()>0){
|
String str="";
|
for(Map map:list1){
|
str+=ConvertUtil.obj2StrBlank(map.get("id"))+",";
|
}
|
str=str+subId;
|
param.put("subId", Arrays.asList(str.split(",")));
|
sql.append(" AND c.SUB_CUS_ID in( :subId) ");
|
}
|
|
}
|
if (StringUtil.isNotBlank(categoryId)) {
|
sql.append(" and c.LV3_ID = :categoryId ");
|
}
|
//审计状态
|
if(StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(param.get("sjState")))){
|
sql.append(" and c.sj_state = :sjState ");
|
}
|
sql.append(" inner JOIN cmdb_ci_category lv2 on c.LV2_ID = lv2.ID ");
|
sql.append(" inner JOIN cmdb_ci_category lv1 on c.LV1_ID = lv1.ID ");
|
sql.append(" where 1 = 1 ");
|
|
return baseDao.queryForList(sql.toString(), param);
|
}
|
}
|
