package cn.ksource.web.facade.tj;
|
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.annotation.Resource;
|
import javax.servlet.http.HttpServletRequest;
|
|
import org.springframework.core.annotation.Order;
|
import org.springframework.stereotype.Service;
|
|
import cn.ksource.beans.CMDB_CI_CATEGORY;
|
import cn.ksource.core.dao.BaseDao;
|
import cn.ksource.core.dao.SqlParameter;
|
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;
|
import cn.ksource.web.Constants;
|
import cn.ksource.web.util.ChartUtil;
|
@Service
|
public class SparePartStatisFacadeImpl implements SparePartStatisFacade{
|
@Resource
|
private BaseDao baseDao;
|
|
public List<Map> getCiTypeChart(String lv1Id,String lv2Id,String queryType){
|
if(StringUtil.isBlank(queryType)){
|
queryType = "CHART";
|
}
|
|
|
String level = "1";
|
String where_c = "";
|
String where_q = "";
|
|
String column = "cmdb.lv1_id id,cmdb.lv1_name name";
|
String group = " cmdb.lv1_id ";
|
if(StringUtil.isNotBlank(lv1Id)){
|
if(StringUtil.isBlank(lv2Id)){
|
column = "cmdb.lv2_id id,cmdb.lv2_name name,cmdb.lv1_id lv_id";
|
where_q = " and cmdb.LV1_ID =:lv1Id";
|
level = "2";
|
where_c = " and PID =:lv1Id ";
|
group=" cmdb.lv2_id";
|
}else{
|
column = "cmdb.lv3_id id,cmdb.lv3_name name, cmdb.lv1_id lv1_id,\n" +
|
" cmdb.lv2_id lv2_id ";
|
where_q = " and cmdb.LV2_ID =:lv2Id ";
|
level = "3";
|
where_c = " and PID =:lv2Id ";
|
group=" cmdb.lv3_id";
|
}
|
}
|
if(StringUtil.isNotBlank(lv2Id)){
|
column = "cmdb.lv3_id id,cmdb.lv3_name name, cmdb.lv1_id lv_id,\n" +
|
" cmdb.lv2_id lv2_id ";
|
where_q = " and cmdb.LV2_ID =:lv2Id ";
|
level = "3";
|
where_c = " and PID =:lv2Id ";
|
group=" cmdb.lv3_id";
|
}
|
Map param = new HashMap();
|
param.put("level", level);
|
param.put("lv1Id", lv1Id);
|
param.put("lv2Id", lv2Id);
|
|
StringBuilder sql = new StringBuilder();
|
String realSql;
|
sql.append(" select count(SPARE_PART.id) num,b.lv, $column from SPARE_PART,CMDB_CI_CATEGORY cmdb,CMDB_CI_CATEGORY b");
|
sql.append(" where SPARE_PART.state=1 and SPARE_PART.CATE_ID = cmdb.ID $where and cmdb.state=1 and b.id=$group and b.state=1");
|
sql.append(" group by $group ");
|
realSql = sql.toString().replace("$column", column);
|
realSql = realSql.replace("$where", where_q);
|
realSql = realSql.replace("$group", group);
|
List<Map> queryList = baseDao.queryForList(realSql,param);
|
List<Map> result = new ArrayList<Map>();
|
Integer sum_qt=0;
|
Integer a=queryList.size();
|
if(queryList!=null&&queryList.size()>0) {
|
for (Map map : queryList) {
|
if(result.size()>=9 && queryList.size()!=10){
|
sum_qt+=ConvertUtil.obj2Int(map.get("num"));
|
}else {
|
Map series_map = new HashMap();
|
series_map.put("id", ConvertUtil.obj2StrBlank(map.get("id")));
|
series_map.put("name", ConvertUtil.obj2StrBlank(map.get("name")));
|
series_map.put("y", ConvertUtil.obj2Int(map.get("num")));
|
series_map.put("lv", ConvertUtil.obj2Int(map.get("lv")));
|
if (ConvertUtil.obj2Int(map.get("lv")) == 2) {
|
series_map.put("lv1_id", ConvertUtil.obj2StrBlank(map.get("lv_id")));
|
}
|
result.add(series_map);
|
}
|
}
|
}
|
if(sum_qt>0){
|
Map series_map = new HashMap();
|
series_map.put("name","其它");
|
series_map.put("y", sum_qt);
|
result.add(series_map);
|
}
|
System.out.println("----------"+result);
|
return result;
|
}
|
|
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 ");
|
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 getSpareCategoryList(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 CATE_TYPE=2 ");
|
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);
|
}
|
/**
|
* 备品备件数量统计
|
* @param beginTime
|
* @param endTime
|
* @param cate_id
|
* @return
|
*/
|
public List<Map> getSpareTableTypeChart(String cate_id,String beginTime,String endTime,String type){
|
StringBuilder sql = new StringBuilder();
|
Map param = new HashMap();
|
List<Map> list=new ArrayList();
|
if(StringUtil.isBlank(cate_id) && type.equals("bjcrktj")){
|
String rksl_string="SELECT\n" +
|
" count( distinct SPARE_PART.id) rksl\n" +
|
" FROM\n" +
|
" SPARE_PART,\n" +
|
" WORKFLOW_BASE,\n" +
|
" SPARE_PART_STORAGE\n" +
|
" WHERE\n" +
|
" SPARE_PART.LINK_STORAGE_ID = SPARE_PART_STORAGE.ID\n" +
|
" AND WORKFLOW_BASE.id = SPARE_PART_STORAGE.FLOW_ID\n" +
|
" AND SPARE_PART_STORAGE.STATE = 3 ";
|
if(StringUtil.isNotBlank(beginTime)){
|
rksl_string+=" and WORKFLOW_BASE.ENDTIME>=:beginTime ";
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
rksl_string+=" and WORKFLOW_BASE.ENDTIME<=:endTime ";
|
param.put("endTime", endTime+"235959");
|
}
|
Integer rksl=baseDao.queryForInteger(rksl_string, param);
|
Map map=new HashMap();
|
map.put("rksl", rksl);
|
|
String cksl_string="SELECT\n" +
|
" count(distinct SPARE_PART.id)\n" +
|
" FROM\n" +
|
" SPARE_PART,\n" +
|
" SPARE_PART_DELIVERY,\n" +
|
" SPARE_PART_APPLY_ENTITY,\n" +
|
" WORKFLOW_BASE\n" +
|
" WHERE\n" +
|
" SPARE_PART_APPLY_ENTITY.ENTITY_ID = SPARE_PART.ID\n" +
|
" AND SPARE_PART_APPLY_ENTITY.DELIVERY_ORDER_ID=SPARE_PART_DELIVERY.ID\n" +
|
" AND SPARE_PART.STATE = 3\n" +
|
" AND WORKFLOW_BASE.id =SPARE_PART_DELIVERY.FLOW_ID \n" +
|
" AND SPARE_PART_DELIVERY.state = 3 ";
|
if(StringUtil.isNotBlank(beginTime)){
|
cksl_string+=" and WORKFLOW_BASE.ENDTIME>=:beginTime ";
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
cksl_string+=" and WORKFLOW_BASE.ENDTIME<=:endTime ";
|
param.put("endTime", endTime+"235959");
|
}
|
Integer cksl=baseDao.queryForInteger(cksl_string, param);
|
map.put("cksl", cksl);
|
|
|
String slsl_string=" SELECT\n" +
|
" count(distinct SPARE_PART.id)\n" +
|
" FROM\n" +
|
" SPARE_PART_APPLY,\n" +
|
" SPARE_PART_APPLY_CATE,\n" +
|
" SPARE_PART_APPLY_ENTITY,\n" +
|
" WORKFLOW_BASE,\n" +
|
" SPARE_PART\n" +
|
" WHERE\n" +
|
" SPARE_PART_APPLY_CATE.APPLY_ORDER_ID = SPARE_PART_APPLY.id\n" +
|
" AND SPARE_PART.ID = SPARE_PART_APPLY_ENTITY.ENTITY_ID\n" +
|
" AND SPARE_PART_APPLY_ENTITY.LINK_ID = SPARE_PART_APPLY_CATE.ID\n" +
|
" AND SPARE_PART_APPLY.STATE IN (3, 4, 5)\n" +
|
" AND WORKFLOW_BASE.id =SPARE_PART_APPLY.FLOW_ID \n" +
|
" AND SPARE_PART.state = '3'\n" +
|
" AND SPARE_PART_APPLY_ENTITY.CI_ID IS NOT NULL";
|
if(StringUtil.isNotBlank(beginTime)){
|
slsl_string+=" and SPARE_PART_APPLY_ENTITY.GMT_USAGE>=:beginTime ";
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
slsl_string+=" and SPARE_PART_APPLY_ENTITY.GMT_USAGE<=:endTime ";
|
param.put("endTime", endTime+"235959");
|
}
|
Integer slsl=baseDao.queryForInteger(slsl_string, param);
|
map.put("slsl", slsl);
|
list.add(map);
|
}else{
|
sql.append("SELECT\n" +
|
" cmdb.LV1_NAME,\n" +
|
" cmdb.LV2_NAME,\n" +
|
" cmdb.LV3_NAME,\n" +
|
" (\n" +
|
" SELECT\n" +
|
" count(SPARE_PART.id)\n" +
|
" FROM\n" +
|
" SPARE_PART,\n" +
|
" WORKFLOW_BASE,\n" +
|
" SPARE_PART_STORAGE\n" +
|
" WHERE\n" +
|
" SPARE_PART.LINK_STORAGE_ID = SPARE_PART_STORAGE.ID\n" +
|
" AND WORKFLOW_BASE.id = SPARE_PART_STORAGE.FLOW_ID\n" +
|
" AND SPARE_PART_STORAGE.STATE = 3");
|
|
|
if(StringUtil.isNotBlank(beginTime)){
|
sql.append(" and WORKFLOW_BASE.ENDTIME>=:beginTime ");
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
sql.append(" and WORKFLOW_BASE.ENDTIME<=:endTime ");
|
param.put("endTime", endTime+"235959");
|
}
|
|
sql.append(" AND SPARE_PART.CATE_ID = spare.CATE_ID\n" +
|
" ) rksl,\n" +
|
" (\n" +
|
" SELECT\n" +
|
" count(distinct SPARE_PART.id)\n" +
|
" FROM\n" +
|
" SPARE_PART,\n" +
|
" SPARE_PART_DELIVERY,\n" +
|
" SPARE_PART_APPLY_ENTITY,\n" +
|
" WORKFLOW_BASE\n" +
|
" WHERE\n" +
|
" SPARE_PART_APPLY_ENTITY.ENTITY_ID = SPARE_PART.ID\n" +
|
" AND SPARE_PART.STATE = 3\n" +
|
" AND SPARE_PART_APPLY_ENTITY.DELIVERY_ORDER_ID=SPARE_PART_DELIVERY.ID\n" +
|
" AND WORKFLOW_BASE.id =SPARE_PART_DELIVERY.FLOW_ID \n");
|
|
if(StringUtil.isNotBlank(beginTime)){
|
sql.append(" and WORKFLOW_BASE.ENDTIME>=:beginTime ");
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
sql.append(" and WORKFLOW_BASE.ENDTIME<=:endTime ");
|
param.put("endTime", endTime+"235959");
|
}
|
|
|
|
sql.append(" AND SPARE_PART.CATE_ID = spare.CATE_ID\n" +
|
" ) cksl,\n" +
|
" ifnull(\n" +
|
" (\n" +
|
" SELECT\n" +
|
" count(distinct SPARE_PART.id)\n" +
|
" FROM\n" +
|
" SPARE_PART_APPLY,\n" +
|
" SPARE_PART_APPLY_ENTITY,\n" +
|
" WORKFLOW_BASE,\n" +
|
" SPARE_PART\n" +
|
" WHERE\n" +
|
" SPARE_PART.ID = SPARE_PART_APPLY_ENTITY.ENTITY_ID\n" +
|
" AND SPARE_PART_APPLY.STATE IN (3, 4, 5)\n" +
|
" AND WORKFLOW_BASE.id =SPARE_PART_APPLY.FLOW_ID \n");
|
|
|
if(StringUtil.isNotBlank(beginTime)){
|
sql.append(" and SPARE_PART_APPLY_ENTITY.GMT_USAGE>=:beginTime ");
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
sql.append(" and SPARE_PART_APPLY_ENTITY.GMT_USAGE<=:endTime ");
|
param.put("endTime", endTime+"235959");
|
}
|
sql.append(" AND SPARE_PART.CATE_ID = spare.CATE_ID\n" +
|
" AND SPARE_PART.state = '3'\n" +
|
" AND SPARE_PART_APPLY_ENTITY.CI_ID IS NOT NULL\n" +
|
" ),\n" +
|
" 0\n" +
|
" ) slsl \n" +
|
" FROM\n" +
|
" SPARE_PART spare,\n" +
|
" CMDB_CI_CATEGORY cmdb\n" +
|
"WHERE\n" +
|
" spare.CATE_ID = cmdb.id\n");
|
//"AND spare.state = 1");
|
|
|
if(StringUtil.isNotBlank(cate_id)){
|
sql.append(" and spare.CATE_ID=:cate_id ");
|
param.put("cate_id", cate_id);
|
}
|
sql.append(" GROUP BY CATE_ID order by rksl desc");
|
list=baseDao.queryForList(sql.toString(),param);
|
}
|
|
return list;
|
}
|
/**
|
* 项目备件申领统计数据
|
*/
|
|
public List getProjectSpareTableTypeChart(String customer_id,String beginTime,String endTime){
|
StringBuilder sql = new StringBuilder();
|
Map param = new HashMap();
|
sql.append("SELECT\n" +
|
" count(spare.id) zs,\n" +
|
" apply.PROJECT_NAME,\n" +
|
" sum(spare.SALE_PRICE) je\n" +
|
"FROM\n" +
|
" SPARE_PART_APPLY apply,\n" +
|
" SPARE_PART_APPLY_ENTITY entity,\n" +
|
" SPARE_PART spare,\n" +
|
" SC_PARTNER_CUSTOMER_INFO\n" +
|
"WHERE\n" +
|
" entity.ORDER_ID = apply.ID\n" +
|
"AND entity.CI_ID IS NOT NULL\n" +
|
"AND entity.ENTITY_ID = spare.id\n" +
|
"AND apply.PROJECT_ID = SC_PARTNER_CUSTOMER_INFO.id\n" +
|
"AND SC_PARTNER_CUSTOMER_INFO.CUSTOMER_STATE = 1");
|
|
if(StringUtil.isNotBlank(customer_id)){
|
sql.append(" and apply.PROJECT_ID=:customer_id ");
|
param.put("customer_id", customer_id);
|
}
|
if(StringUtil.isNotBlank(beginTime)){
|
sql.append(" and entity.gmt_usage>=:beginTime ");
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
sql.append(" and entity.gmt_usage<=:endTime ");
|
param.put("endTime", endTime+"235959");
|
}
|
|
sql.append(" GROUP BY apply.PROJECT_ID order by zs desc");
|
return baseDao.queryForList(sql.toString(),param);
|
}
|
|
/**
|
*供应商统计领统计数据
|
*/
|
|
public List getSupplierSpareTableTypeChart(String cate_id,String beginTime,String endTime,String pxtj,String pxlx){
|
StringBuilder sql = new StringBuilder();
|
Map param = new HashMap();
|
sql.append("SELECT\n" +
|
" spare.SUPPLIER_name,\n" +
|
" (\n" +
|
" SELECT\n" +
|
" count(SPARE_PART.id)\n" +
|
" FROM\n" +
|
" SPARE_PART,\n" +
|
" SPARE_PART_STORAGE,\n" +
|
" WORKFLOW_BASE\n" +
|
" WHERE\n" +
|
" SPARE_PART.LINK_STORAGE_ID = SPARE_PART_STORAGE.ID\n" +
|
" AND WORKFLOW_BASE.id = SPARE_PART_STORAGE.FLOW_ID\n" +
|
" AND SPARE_PART_STORAGE.STATE = 3");
|
|
if(StringUtil.isNotBlank(cate_id)){
|
sql.append(" and SPARE_PART.CATE_ID=:cate_id ");
|
param.put("cate_id", cate_id);
|
}
|
if(StringUtil.isNotBlank(beginTime)){
|
sql.append(" and WORKFLOW_BASE.ENDTIME>=:beginTime ");
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
sql.append(" and WORKFLOW_BASE.ENDTIME<=:endTime ");
|
param.put("endTime", endTime+"235959");
|
}
|
|
|
|
sql.append("AND SPARE_PART.SUPPLIER_id = spare.SUPPLIER_id\n" +
|
" ) rksl,\n" +
|
" (\n" +
|
" SELECT\n" +
|
" count(SPARE_PART.id)\n" +
|
" FROM\n" +
|
" SPARE_PART,\n" +
|
" SPARE_PART_DELIVERY,\n" +
|
" SPARE_PART_APPLY_CATE,\n" +
|
" SPARE_PART_APPLY_ENTITY,\n" +
|
" WORKFLOW_BASE\n" +
|
" WHERE\n" +
|
" SPARE_PART_DELIVERY.id = SPARE_PART_APPLY_CATE.DELIVERY_ORDER_ID\n" +
|
" AND SPARE_PART_APPLY_ENTITY.LINK_ID = SPARE_PART_APPLY_CATE.ID\n" +
|
" AND SPARE_PART_APPLY_ENTITY.ENTITY_ID = SPARE_PART.ID\n" +
|
" AND SPARE_PART.STATE = 3\n" +
|
" AND WORKFLOW_BASE.id =SPARE_PART_DELIVERY.FLOW_ID \n" +
|
" AND SPARE_PART_APPLY_ENTITY.STATE = 2");
|
|
|
if(StringUtil.isNotBlank(cate_id)){
|
sql.append(" and SPARE_PART.CATE_ID=:cate_id ");
|
param.put("cate_id", cate_id);
|
}
|
if(StringUtil.isNotBlank(beginTime)){
|
sql.append(" and WORKFLOW_BASE.ENDTIME>=:beginTime ");
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
sql.append(" and WORKFLOW_BASE.ENDTIME<=:endTime ");
|
param.put("endTime", endTime+"235959");
|
}
|
sql.append("AND SPARE_PART.SUPPLIER_id = spare.SUPPLIER_id\n" +
|
" AND SPARE_PART_DELIVERY.state = 3\n" +
|
/* " AND (\n" +
|
" (\n" +
|
" SPARE_PART_DELIVERY.APPLY_ORDER_ID IS NOT NULL\n" +
|
" AND SPARE_PART_APPLY_ENTITY.CI_ID IS NOT NULL\n" +
|
" )\n" +
|
" OR SPARE_PART_DELIVERY.APPLY_ORDER_ID IS NULL\n" +
|
" )\n" +*/
|
" ) cksl,\n" +
|
" ifnull(\n" +
|
" (\n" +
|
" SELECT\n" +
|
" count(SPARE_PART.id)\n" +
|
" FROM\n" +
|
" SPARE_PART_APPLY,\n" +
|
" SPARE_PART_APPLY_CATE,\n" +
|
" SPARE_PART_APPLY_ENTITY,\n" +
|
" WORKFLOW_BASE,\n" +
|
" SPARE_PART\n" +
|
" WHERE\n" +
|
" SPARE_PART_APPLY_CATE.APPLY_ORDER_ID = SPARE_PART_APPLY.id\n" +
|
" AND SPARE_PART.ID = SPARE_PART_APPLY_ENTITY.ENTITY_ID\n" +
|
" AND SPARE_PART_APPLY_ENTITY.LINK_ID = SPARE_PART_APPLY_CATE.ID\n" +
|
" AND SPARE_PART_APPLY.STATE IN (3, 4, 5)\n" +
|
" AND WORKFLOW_BASE.id =SPARE_PART_APPLY.FLOW_ID \n" +
|
" AND SPARE_PART_APPLY_ENTITY.STATE = 2");
|
|
|
|
if(StringUtil.isNotBlank(cate_id)){
|
sql.append(" and SPARE_PART.CATE_ID=:cate_id ");
|
param.put("cate_id", cate_id);
|
}
|
if(StringUtil.isNotBlank(beginTime)){
|
sql.append(" and SPARE_PART_APPLY_ENTITY.GMT_USAGE>=:beginTime ");
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
sql.append(" and SPARE_PART_APPLY_ENTITY.GMT_USAGE<=:endTime ");
|
param.put("endTime", endTime+"235959");
|
}
|
|
|
|
|
sql.append("AND SPARE_PART.SUPPLIER_id = spare.SUPPLIER_id\n" +
|
" AND SPARE_PART.state = '3'\n" +
|
" AND SPARE_PART_APPLY_ENTITY.CI_ID IS NOT NULL\n" +
|
" ),\n" +
|
" 0\n" +
|
" ) slsl,\n" +
|
" (\n" +
|
" SELECT\n" +
|
" count(SPARE_PART.id)\n" +
|
" FROM\n" +
|
" OVERAGE_LOSS,\n" +
|
" OVERAGE_LOSS_DETAIL,\n" +
|
" WORKFLOW_BASE,\n" +
|
" SPARE_PART\n" +
|
" WHERE\n" +
|
" OVERAGE_LOSS.id = OVERAGE_LOSS_DETAIL.ORDER_ID\n" +
|
" AND WORKFLOW_BASE.id = OVERAGE_LOSS.FLOW_ID\n" +
|
" AND SPARE_PART.id = OVERAGE_LOSS_DETAIL.ENTITY_ID");
|
|
|
|
if(StringUtil.isNotBlank(cate_id)){
|
sql.append(" and SPARE_PART.CATE_ID=:cate_id ");
|
param.put("cate_id", cate_id);
|
}
|
if(StringUtil.isNotBlank(beginTime)){
|
sql.append(" and WORKFLOW_BASE.ENDTIME>=:beginTime ");
|
param.put("beginTime", beginTime+"000000");
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
sql.append(" and WORKFLOW_BASE.ENDTIME<=:endTime ");
|
param.put("endTime", endTime+"235959");
|
}
|
|
sql.append("AND OVERAGE_LOSS.BUS_TYPE = 1\n" +
|
" AND SPARE_PART.state = 2\n" +
|
" AND SPARE_PART.SUPPLIER_id = spare.SUPPLIER_id\n" +
|
" ) bssl\n" +
|
"FROM\n" +
|
" SPARE_PART spare ,SUPPLIER_INFO where spare.SUPPLIER_id=SUPPLIER_INFO.id and SUPPLIER_INFO.STATE=1\n" +
|
"GROUP BY\n" +
|
" spare.SUPPLIER_id ");
|
sql.append(" order BY "+" "+ pxtj+" "+pxlx+"");
|
return baseDao.queryForList(sql.toString(),param);
|
}
|
public List getSpareMainufacturerChart(HttpServletRequest request){
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
String cate_id = request.getParameter("cate_id");
|
String entity_no = request.getParameter("entity_no");
|
String name = request.getParameter("name");
|
String asset_no = request.getParameter("asset_no");
|
String supplier_id = request.getParameter("supplier_id");
|
|
Map param = new HashMap();
|
param.put("beginTime", beginTime+"000000");
|
param.put("endTime", endTime+"235959" );
|
param.put("cate_id", cate_id);
|
param.put("entity_no", "%" + entity_no + "%");
|
param.put("name", "%" + name + "%");
|
param.put("asset_no", "%" + asset_no + "%");
|
param.put("supplier_id", supplier_id );
|
// var param = {'beginTime':beginTime,'endTime':endTime,'cate_id':cate_id,'entity_no':entity_no,'name':name,'asset_no':asset_no,'supplier_id':supplier_id};
|
//开始查询
|
StringBuilder sql = new StringBuilder();
|
sql.append("SELECT\n" +
|
" SPARE_PART.*, CONCAT(\n" +
|
" LV1_NAME,\n" +
|
" '-',\n" +
|
" LV2_NAME,\n" +
|
" '-',\n" +
|
" LV3_NAME\n" +
|
" ) CATENAME\n" +
|
"FROM\n" +
|
" SPARE_PART,\n" +
|
" cmdb_ci_category\n" +
|
"WHERE\n" +
|
" SPARE_PART.CATE_ID = cmdb_ci_category.id\n" +
|
"AND SPARE_PART.state = 1");
|
if (StringUtil.notEmpty(cate_id)) {
|
sql.append(" AND SPARE_PART.CATE_ID =:cate_id");
|
}
|
if (StringUtil.isNotBlank(beginTime)) {
|
sql.append(" and SPARE_PART.GMT_CREATE>=:beginTime");
|
}
|
if (StringUtil.isNotBlank(endTime)) {
|
sql.append(" and SPARE_PART.GMT_CREATE<= :endTime");
|
}
|
if (StringUtil.isNotBlank(entity_no)) {
|
sql.append(" and SPARE_PART.entity_no like :entity_no ");
|
}
|
if (StringUtil.isNotBlank(name)) {
|
sql.append(" and SPARE_PART.name like :name ");
|
}
|
if (StringUtil.isNotBlank(asset_no)) {
|
sql.append(" and SPARE_PART.asset_no like :asset_no ");
|
}
|
if (StringUtil.isNotBlank(supplier_id)) {
|
sql.append(" and SPARE_PART.supplier_id =:supplier_id ");
|
}
|
sql.append(" order by SPARE_PART.GMT_CREATE desc ");
|
return baseDao.queryForList(sql.toString(), param);
|
}
|
@Override
|
public Map queryStorageData(String ciId) {
|
Map data = new HashMap();
|
Map label = new HashMap();
|
String selectSql = "SELECT * FROM PROP_DATA WHERE BUS_ID =:ciId";
|
List<Map> datas = baseDao.queryForList(selectSql,new SqlParameter("ciId",ciId));
|
|
if(null!=datas && datas.size()>0) {
|
for(Map d : datas) {
|
String column_id = ConvertUtil.obj2StrBlank(d.get("COLUMN_ID"));
|
String column_value = ConvertUtil.obj2StrBlank(d.get("COLUMN_VALUE"));
|
String column_text = ConvertUtil.obj2StrBlank(d.get("COLUMN_TEXT"));
|
|
data.put(column_id, column_value);
|
label.put(column_id, column_text);
|
}
|
}
|
Map resultMap = new HashMap();
|
resultMap.put("data", data);
|
resultMap.put("label", label);
|
System.out.print("data11111111111111111111111111"+data);
|
System.out.print("label11111111111111111111111111"+label);
|
|
return resultMap;
|
}
|
|
|
@Override
|
public List queryGroupPropsTwo(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,D.SERIAL ");
|
|
List<Map> 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<Map> 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 ) {
|
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")));
|
System.out.print("cacheMap21111111111111111111111111111"+cacheMap2);
|
}
|
}
|
return groups;
|
}
|
|
private List<Map> queryListValue(List<Map> props) {
|
String selectSql = "SELECT * FROM PROP_SEL WHERE STATE = 1 ORDER BY SERIAL ";
|
List<Map> 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;
|
}
|
|
@Override
|
public Map queryCommonProps(String ciId) {
|
String selectSq1 ="SELECT\n" +
|
" p.*\n" +
|
"FROM\n" +
|
" (\n" +
|
" SELECT\n" +
|
" g.id,\n" +
|
" g.group_name,\n" +
|
" g.serial\n" +
|
" FROM\n" +
|
" prop_group g,\n" +
|
" cmdb_ci_category c\n" +
|
" WHERE\n" +
|
" g.link_id = c.id\n" +
|
" AND c.state = 1\n" +
|
" AND (\n" +
|
" c.id ='"+ciId+"'\n" +
|
/*" OR c.id =c.LV2_ID \n" +*/
|
" )\n" +
|
" ) g,\n" +
|
" prop_column p\n" +
|
"WHERE\n" +
|
" g.id = p.group_id\n" +
|
"AND p.state = 1\n" +
|
"ORDER BY SERIAL\n";
|
|
List<Map> 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) {
|
textareaProp.add(m);
|
} else {
|
commonProp.add(m);
|
}
|
}
|
}
|
|
resultMap.put("commonProp", commonProp);
|
resultMap.put("textareaProp", textareaProp);
|
|
return resultMap;
|
}
|
|
@Override
|
public Map getCiCountChart(HttpServletRequest request) {
|
// TODO Auto-generated method stub
|
return null;
|
}
|
|
@Override
|
public Map getCiMainufacturerChart(String cusId, String queryType) {
|
// TODO Auto-generated method stub
|
return null;
|
}
|
@Override
|
public Map incidentInfluenceCol(HttpServletRequest request) {
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", request.getParameter("cusId"))
|
.addValue("beginTime", request.getParameter("beginTime")+"000000")
|
.addValue("endTime", request.getParameter("endTime")+"235959")
|
.addValue("lv1Id", request.getParameter("lv1Id"))
|
.addValue("lv2Id", request.getParameter("lv2Id"));
|
String sqlString="SELECT\n" +
|
" count(SPARE_PART.id) sum,";
|
String lv1Id=request.getParameter("lv1Id");
|
String lv2Id=request.getParameter("lv2Id");
|
String cusId=request.getParameter("cusId");
|
String beginTime=request.getParameter("beginTime");
|
String endTime=request.getParameter("endTime");
|
/*if(StringUtil.isNotBlank(lv1Id)){
|
sqlString+=" and CATE_ID in (select id from CMDB_CI_CATEGORY where lv1_id=:lv1Id and lv=3)";
|
}else{
|
|
}*/
|
if(StringUtil.isBlank(lv1Id)){
|
sqlString+=" b.lv1_name NAME";
|
}else if(StringUtil.isNotBlank(lv1Id) && StringUtil.isBlank(lv2Id)){
|
sqlString+=" b.lv2_name NAME";
|
}else if(StringUtil.isNotBlank(lv1Id) && StringUtil.isNotBlank(lv2Id) ){
|
sqlString+=" b.lv3_name NAME";
|
}
|
sqlString+=" FROM\n" +
|
" SPARE_PART,\n" +
|
" SPARE_PART_APPLY,\n" +
|
" SPARE_PART_APPLY_ENTITY,\n" +
|
" CMDB_CI_CATEGORY a,\n" +
|
" CMDB_CI_CATEGORY b\n" +
|
"WHERE\n" +
|
" SPARE_PART_APPLY.id = SPARE_PART_APPLY_ENTITY.ORDER_ID\n" +
|
"AND a.id = SPARE_PART.CATE_ID\n" +
|
"AND a.id = b.lv3_id\n" +
|
"AND SPARE_PART_APPLY_ENTITY.ENTITY_ID = SPARE_PART.id\n" +
|
"AND SPARE_PART_APPLY_ENTITY.STATE = 2\n" +
|
"AND SPARE_PART_APPLY_ENTITY.CI_ID IS NOT NULL";
|
if(StringUtil.isNotBlank(cusId)){
|
sqlString+=" and SPARE_PART_APPLY.PROJECT_ID=:cusId";
|
}
|
|
if(StringUtil.isNotBlank(beginTime)){
|
sqlString+=" and SPARE_PART_APPLY_ENTITY.GMT_USAGE>=:beginTime";
|
}
|
if(StringUtil.isNotBlank(endTime)){
|
sqlString+=" and SPARE_PART_APPLY_ENTITY.GMT_USAGE<=:endTime";
|
}
|
if(StringUtil.isBlank(lv1Id)){
|
sqlString+=" group by b.lv1_id";
|
}else if(StringUtil.isNotBlank(lv1Id) && StringUtil.isBlank(lv2Id)){
|
sqlString+=" and b.lv1_id=:lv1Id group by b.lv2_id";
|
}else if(StringUtil.isNotBlank(lv1Id) && StringUtil.isNotBlank(lv2Id) ){
|
sqlString+=" and b.lv1_id=:lv1Id and b.lv2_id=:lv2Id group by b.lv3_id ";
|
}
|
sqlString+=" order by sum desc LIMIT 0,10";
|
List<Map> datas = baseDao.queryForList(sqlString,param);
|
Map seriesMap = new HashMap();
|
List<Map> series = new ArrayList<Map>();
|
List seriesData = new ArrayList();
|
HashMap chartMap = new HashMap();
|
List<String> categories = new ArrayList<String>();
|
if(datas!=null && datas.size()>0) {
|
for (Map<String, String> category : datas) {
|
seriesMap.put("name", "数量");
|
Integer aInteger = ConvertUtil.obj2Integer((category.get("sum")));
|
seriesData.add(aInteger);
|
seriesMap.put("data", seriesData);
|
categories.add(ConvertUtil.obj2StrBlank(category.get("name")));
|
}
|
series.add(seriesMap);
|
chartMap.put("series", series);
|
chartMap.put("categories", categories);
|
}
|
|
System.out.print(chartMap);
|
|
return chartMap;
|
}
|
|
}
|
