From 425675051e544cf29b2132615cfbf7a93dc5e51f Mon Sep 17 00:00:00 2001
From: cy <1664593601@qq.com>
Date: 星期三, 22 六月 2022 15:50:59 +0800
Subject: [PATCH] fix(dm): MySQL=》DMSQL
---
src/cn/ksource/web/service/knowledge/KmServiceImpl.java | 171 +++++++++++++++++++++++++++-----------------------------
1 files changed, 82 insertions(+), 89 deletions(-)
diff --git a/src/cn/ksource/web/service/knowledge/KmServiceImpl.java b/src/cn/ksource/web/service/knowledge/KmServiceImpl.java
index e38a94b..7f01b32 100644
--- a/src/cn/ksource/web/service/knowledge/KmServiceImpl.java
+++ b/src/cn/ksource/web/service/knowledge/KmServiceImpl.java
@@ -1,18 +1,6 @@
package cn.ksource.web.service.knowledge;
-import java.util.ArrayList;
-import java.util.HashMap;
-import java.util.LinkedList;
-import java.util.List;
-import java.util.Map;
-
-import javax.servlet.http.HttpServletRequest;
-
-import org.springframework.beans.factory.annotation.Autowired;
-import org.springframework.stereotype.Service;
-
-import cn.ksource.beans.KM_LIBRARY;
import cn.ksource.beans.KM_LIBRARY_FAVORITE;
import cn.ksource.beans.KM_LIBRARY_TEMP;
import cn.ksource.core.dao.BaseDao;
@@ -23,13 +11,18 @@
import cn.ksource.core.web.WebUtil;
import cn.ksource.web.Constants;
import cn.ksource.web.service.file.FileService;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import javax.servlet.http.HttpServletRequest;
+import java.util.*;
@Service
public class KmServiceImpl implements KmService {
-
+
@Autowired
private BaseDao baseDao;
-
+
@Autowired
private FileService fileService;
@@ -38,10 +31,10 @@
StringBuffer sql=new StringBuffer(" SELECT A.*,COUNT(B.ID) AS NUM FROM KM_LIBRARY_CATEGORY A LEFT JOIN KM_LIBRARY B ON A.ID = B.CATEGORY_ID and B.STATE = 1 WHERE A.STATE=1 ");
sql.append(" GROUP BY A.ID ORDER BY A.LEVEL,A.SERIAL,A.TITLE");
List<Map> categoryList = baseDao.queryForList(sql.toString());
-
-
+
+
List<Map> resultList = new LinkedList<Map>();
-
+
Map<String, Map> yjgnCache = new HashMap<String, Map>();
Map<String, Map> ejgnCache = new HashMap<String, Map>();
Map<String, Map> sjgnCache = new HashMap<String, Map>();
@@ -67,9 +60,9 @@
}
}
}
-
+
for (Map map : categoryList) {
-
+
//涓�绾ф爲
if (map.get("LEVEL").toString().equalsIgnoreCase("1")) {
yjgnCache.put(map.get("ID").toString(), map);
@@ -84,7 +77,7 @@
List<Map> list = (List<Map>)yjgnMap.get("ejTree");
map.put("sjTree", new LinkedList<Map>());
list.add(map);
-
+
ejgnCache.put(map.get("ID").toString(), map);
continue;
}
@@ -125,21 +118,21 @@
}else{
sql.append(" (SELECT C.* FROM KM_LIBRARY C,KM_LIBRARY_KNOWLEDGE_ACCESS D WHERE C.ID = D.KNOWLEDGE_ID AND D.CUSTOMER_ID = :cusId) B ON A.ID = B.CATEGORY_ID and B.STATE = 1");
}
- sql.append(" WHERE A.STATE=1 GROUP BY A.ID ORDER BY A.LEVEL,A.SERIAL,A.TITLE");
-
+ sql.append(" WHERE A.STATE=1 GROUP BY A.ID,A.TITLE, A.TAG , A.LEVEL,A.SERIAL ORDER BY A.LEVEL,A.SERIAL,A.TITLE");
+
List<Map> categoryList = baseDao.queryForList(sql.toString(),new SqlParameter("cusId",cusId));
-
+
List<Map> resultList = new LinkedList<Map>();
-
+
String firstCate = "";
String secondCate = "";
-
+
Map<String, Map> yjgnCache = new HashMap<String, Map>();
Map<String, Map> ejgnCache = new HashMap<String, Map>();
Map<String, Map> sjgnCache = new HashMap<String, Map>();
-
+
for (Map map : categoryList) {
-
+
//涓�绾ф爲
if (map.get("LEVEL").toString().equalsIgnoreCase("1")) {
yjgnCache.put(map.get("ID").toString(), map);
@@ -154,7 +147,7 @@
List<Map> list = (List<Map>)yjgnMap.get("ejTree");
map.put("sjTree", new LinkedList<Map>());
list.add(map);
-
+
ejgnCache.put(map.get("ID").toString(), map);
continue;
}
@@ -168,14 +161,14 @@
List<Map> list = (List<Map>)ejgnMap.get("sjTree");
list.add(map);
}
-
-
+
+
}
}
-
+
return resultList;
}
-
+
@Override
public List<Map> getmyFavourite(String userId) {
StringBuilder sql = new StringBuilder();
@@ -184,7 +177,7 @@
sql.append("where k.CREATE_USER_ID=:create_user_id ");
SqlParameter param = new SqlParameter();
param.addValue("create_user_id", userId);
-
+
return baseDao.queryForList(sql.toString(), param);
}
@@ -203,13 +196,13 @@
StringBuilder sql = new StringBuilder();
sql.append("select t.ID,t.TITLE,t.TAG,t.CATEGORY_ID,t.SUMMARY,t.CREATE_TIME,t.CREATE_USER_NAME,t.IS_EDITOR,t.STATE from ( ");
sql.append("select DISTINCT k.ID,k.TITLE,k.TAG,k.SUMMARY,k.CREATE_TIME,k.CREATE_USER_NAME,k.CATEGORY_ID,k.IS_EDITOR,p.STATE ");
-
+
if(StringUtil.isEmpty(cusId)){
sql.append("from km_library k ");
}else{
sql.append("from (SELECT A.* FROM KM_LIBRARY A,KM_LIBRARY_KNOWLEDGE_ACCESS B WHERE A.ID = B.KNOWLEDGE_ID AND B.CUSTOMER_ID = :cusId ) k ");
}
-
+
sql.append(" LEFT JOIN KM_LIBRARY_TEMP p on p.ORIGINAL_ID = k.ID where k.STATE=1 ");
sql.append("order by k.CREATE_TIME desc) t ");
sql.append("limit :limit1,:limit2 ");
@@ -220,7 +213,7 @@
param.addValue("cusId", cusId);
param.addValue("limit1", (begin-1)*size);
param.addValue("limit2", size);
-
+
List<Map> list = baseDao.queryForList(sql.toString(), param);
List<Map> mylist = getmyFavourite(userId);
for (Map map : list) {
@@ -251,7 +244,7 @@
}
sql.append("where k.STATE=1 ");
sql.append("order by k.CREATE_TIME desc) t ");
-
+
return baseDao.queryForInteger(sql.toString());
}
@@ -277,12 +270,12 @@
sql.append("and k.THIRD_CATEGORY_ID=:category_id ");
}
sql.append("order by k.CREATE_TIME desc) t ");
-
+
SqlParameter param = new SqlParameter();
param.addValue("category_id", categoryId);
param.addValue("userId", userId);
param.addValue("cusId", cusId);
-
+
return baseDao.queryForInteger(sql.toString(), param);
}
@@ -303,7 +296,7 @@
StringBuilder sql = new StringBuilder();
sql.append("select t.ID,t.TITLE,t.TAG,t.SUMMARY,t.CREATE_TIME,t.CREATE_USER_NAME,t.IS_EDITOR,t.STATE from ( ");
sql.append("select DISTINCT k.ID,k.TITLE,k.TAG,k.SUMMARY,k.CREATE_TIME,k.CREATE_USER_NAME,k.IS_EDITOR,p.STATE ");
-
+
if(StringUtil.isEmpty(cusId)){
sql.append("from km_library k ");
}else{
@@ -327,7 +320,7 @@
param.addValue("category_id", categoryId);
param.addValue("userId", userId);
param.addValue("cusId", cusId);
-
+
List<Map> list = baseDao.queryForList(sql.toString(), param);
List<Map> mylist = getmyFavourite(userId);
for (Map map : list) {
@@ -363,7 +356,7 @@
sql.append(" WHERE k.ID = :id ");
SqlParameter param = new SqlParameter();
param.addValue("id", libraryId);
-
+
Map map = baseDao.queryForMap(sql.toString(), param);
List<Map> mylist = getmyFavourite(userId);
List<Map> myrecord = getmyknowRecord(userId);
@@ -393,18 +386,18 @@
}
}
map.put("tag_list", tagList);
-
+
}
-
+
//闄勪欢
map.put("file_list", fileService.getFileList(ConvertUtil.obj2StrBlank(map.get("FILE_ID"))));
-
-
+
+
//鐭ヨ瘑鍏宠仈
String linksql = "select B.ID,B.TITLE from KM_LIBRARY_KNOWLEDGE A,KM_LIBRARY B where A.KNOWLEDGE_ID = B.ID AND A.LIBRARY_ID=:library_id ";
List<Map> linkList = baseDao.queryForList(linksql, new SqlParameter("library_id", libraryId));
map.put("link_list", linkList);
-
+
//鍘嗗彶鐗堟湰鍒楄〃
String historySql = "select ID,VERSION,CREATE_USER_NAME,CREATE_TIME from KM_LIBRARY_HISTORY "
+"where ORIGINAL_ID=:original_id and STATE=1 order by VERSION ";
@@ -413,10 +406,10 @@
//鐐瑰嚮鎺掑悕
Map rankMap = this.getLibraryRankForHits();
map.put("RANK", rankMap.get(libraryId));
-
+
return map;
}
-
+
@Override
public Map getLibraryZjbById(String libraryId, HttpServletRequest request) {
String userId = WebUtil.getLoginedUserId(request);
@@ -426,7 +419,7 @@
sql.append(" WHERE k.ID = :id ");
SqlParameter param = new SqlParameter();
param.addValue("id", libraryId);
-
+
Map map = baseDao.queryForMap(sql.toString(), param);
List<Map> mylist = getmyFavourite(userId);
for(Map mymap :mylist){
@@ -445,13 +438,13 @@
}
}
map.put("tag_list", tagList);
-
+
}
-
+
//闄勪欢
map.put("file_list", fileService.getFileList(ConvertUtil.obj2StrBlank(map.get("ID"))));
-
-
+
+
//鐭ヨ瘑鍏宠仈
String linksql = "select B.ID,B.TITLE from KM_LIBRARY_KNOWLEDGE A,KM_LIBRARY B where A.KNOWLEDGE_ID = B.ID AND A.LIBRARY_ID=:library_id ";
List<Map> linkList = baseDao.queryForList(linksql, new SqlParameter("library_id", new KM_LIBRARY_TEMP(libraryId).getInstanceById().getOriginal_id()));
@@ -459,7 +452,7 @@
linkList = baseDao.queryForList(linksql, new SqlParameter("library_id", libraryId));
}
map.put("link_list", linkList);
-
+
/*//鍘嗗彶鐗堟湰鍒楄〃
String historySql = "select ID,VERSION from KM_LIBRARY_HISTORY "
+"where ORIGINAL_ID=:original_id and STATE=1 order by VERSION ";
@@ -468,10 +461,10 @@
//鐐瑰嚮鎺掑悕
Map rankMap = this.getLibraryRankForHits();
map.put("RANK", rankMap.get(libraryId));*/
-
+
return map;
}
-
+
/**
* 鑾峰彇鏂囧簱鐐瑰嚮鎺掑悕
* @param partnerId
@@ -479,16 +472,16 @@
*/
private Map getLibraryRankForHits() {
Map resultMap = new HashMap();
-
+
String sql = "select ID,HITS from km_library where STATE=1 order by HITS desc ";
-
+
List<Map> list = baseDao.queryForList(sql);
if (list != null && list.size() > 0) {
for (int i=0; i<list.size(); i++) {
resultMap.put(list.get(i).get("ID"), i+1);
}
}
-
+
return resultMap;
}
@@ -496,7 +489,7 @@
public boolean addMyLibraryFavourite(String libraryId, String userId) {
KM_LIBRARY_FAVORITE fav = new KM_LIBRARY_FAVORITE().setLibrary_id(libraryId)
.setCreate_user_id(userId).queryForBean();
-
+
if (fav != null && fav.getCreate_time() > 0) {
return false;//涓嶉噸澶嶆敹钘�
} else {
@@ -513,12 +506,12 @@
//鍩烘湰淇℃伅
StringBuilder sql = new StringBuilder("select ID,TITLE,TAG,SUMMARY,CONTENT,CATEGORY_ID,CATEGORY_NAME,CREATE_USER_ID,CREATE_USER_NAME,CREATE_TIME,VERSION,HITS,STATE,FILE_ID ");
sql.append("from km_library_history where ID=:id ");
-
+
SqlParameter param = new SqlParameter();
param.addValue("id", historyId);
-
+
Map map = baseDao.queryForMap(sql.toString(), param);
-
+
//鏍囩
String tag = ConvertUtil.obj2Str(map.get("TAG"));
if (tag != null) {
@@ -531,17 +524,17 @@
}
map.put("tag_list", tagList);
}
-
+
//闄勪欢
map.put("file_list", fileService.getFileList(ConvertUtil.obj2StrBlank(map.get("FILE_ID"))));
-
-
+
+
//鐭ヨ瘑鍏宠仈
String linksql = "select B.ID,B.TITLE from KM_LIBRARY_KNOWLEDGE_TEMP A,KM_LIBRARY B where A.KNOWLEDGE_ID = B.ID AND A.LIBRARY_ID=:library_id ";
List<Map> linkList = baseDao.queryForList(linksql, new SqlParameter("library_id", historyId));
map.put("link_list", linkList);
-
-
+
+
return map;
}
@@ -549,26 +542,26 @@
public Map thirdlevelListTree(HttpServletRequest request) {
String category_id = request.getParameter("category_id");
String cusId = request.getParameter("cusId");
- StringBuilder sql=new StringBuilder("SELECT A.* ,COUNT(B.ID) AS NUM FROM CMDB_CI_CATEGORY A LEFT JOIN ");
-
+ StringBuilder sql=new StringBuilder("SELECT A.ID,A.PID,A.TYPE,A.NAME,A.CODE,A.LV,A.SERIAL,A.STATE,A.LV1_ID,A.LV1_NAME,A.LV2_ID,A.LV2_NAME,A.LV3_ID,A.LV3_NAME,A.FULL_ID,A.FULL_NAME,A.CATE_TYPE,COUNT(B.ID) AS NUM FROM CMDB_CI_CATEGORY A LEFT JOIN ");
+
if(StringUtil.isEmpty(cusId)){
sql.append(" KM_LIBRARY B ON A.ID = B.THIRDLEVEL_ID AND B.STATE = 1 ");
}else{
sql.append(" (SELECT C.* FROM KM_LIBRARY C,KM_LIBRARY_KNOWLEDGE_ACCESS D WHERE C.ID = D.KNOWLEDGE_ID AND D.CUSTOMER_ID = :cusId) B ON A.ID = B.THIRDLEVEL_ID and B.STATE = 1");
}
- sql.append(" WHERE A.STATE = 1 and a.cate_type=1 GROUP BY A.ID ORDER BY A.LV,A.SERIAL,A.NAME");
+ sql.append(" WHERE A.STATE = 1 and a.cate_type=1 GROUP BY A.ID,A.PID,A.TYPE,A.NAME,A.CODE,A.LV,A.SERIAL,A.STATE,A.LV1_ID,A.LV1_NAME,A.LV2_ID,A.LV2_NAME,A.LV3_ID,A.LV3_NAME,A.FULL_ID,A.FULL_NAME,A.CATE_TYPE ORDER BY A.LV,A.SERIAL,A.NAME");
List<Map> categoryList = baseDao.queryForList(sql.toString(),new SqlParameter("cusId",cusId));
-
+
Map result = new HashMap();
-
+
List<Map> resultList = new LinkedList<Map>();
-
+
Map<String, Map> yjgnCache = new HashMap<String, Map>();
Map<String, Map> ejgnCache = new HashMap<String, Map>();
Map<String, Map> sjgnCache = new HashMap<String, Map>();
-
+
for (Map map : categoryList) {
-
+
//涓�绾ф爲
if (map.get("LV").toString().equalsIgnoreCase("1")) {
yjgnCache.put(map.get("ID").toString(), map);
@@ -583,7 +576,7 @@
List<Map> list = (List<Map>)yjgnMap.get("ejTree");
map.put("sjTree", new LinkedList<Map>());
list.add(map);
-
+
ejgnCache.put(map.get("ID").toString(), map);
continue;
}
@@ -593,7 +586,7 @@
map.put("select", 1);
}
if(null!=map.get("PID")){
-
+
Map ejgnMap = ejgnCache.get(map.get("PID").toString());
List<Map> list = (List<Map>)ejgnMap.get("sjTree");
list.add(map);
@@ -610,29 +603,29 @@
String category_id = request.getParameter("category_id");
String cusId = request.getParameter("cusId");
String type = request.getParameter("type");
- StringBuffer sql = new StringBuffer("SELECT D.*,COUNT(B.ID) AS NUM FROM (");
+ StringBuffer sql = new StringBuffer("SELECT D.ID,D.CATEGORY_CODE,D.CATEGORY_NAME,D.LEVEL,D.P_ID,D.STATE,D.SERIAL,D.TYPE, D.PHOTOPATH,COUNT(B.ID) AS NUM FROM (");
String sqlpart="SELECT * FROM SC_SERVCE_CATEGORY WHERE STATE = 1 AND TYPE = 2 ORDER BY LEVEL,SERIAL ";
sql.append(sqlpart);
sql.append(" ) D LEFT JOIN ");
-
+
if(StringUtil.isEmpty(cusId)){
sql.append(" KM_LIBRARY B ON D.ID = B.THIRD_CATEGORY_ID and B.STATE = 1 ");
}else{
sql.append(" (SELECT C.* FROM KM_LIBRARY C,KM_LIBRARY_KNOWLEDGE_ACCESS D WHERE C.ID = D.KNOWLEDGE_ID AND D.CUSTOMER_ID = :cusId) B ON D.ID = B.THIRD_CATEGORY_ID and B.STATE = 1");
}
- sql.append(" GROUP BY D.ID ORDER BY D.LEVEL,D.SERIAL,D.CATEGORY_NAME");
+ sql.append(" GROUP BY D.ID,D.CATEGORY_CODE,D.CATEGORY_NAME,D.LEVEL,D.P_ID,D.STATE,D.SERIAL,D.TYPE, D.PHOTOPATH ORDER BY D.LEVEL,D.SERIAL,D.CATEGORY_NAME");
List<Map> categoryList = baseDao.queryForList(sql.toString(),new SqlParameter("cusId",cusId));
-
+
Map result = new HashMap();
-
+
List<Map> resultList = new LinkedList<Map>();
-
+
Map<String, Map> yjgnCache = new HashMap<String, Map>();
Map<String, Map> ejgnCache = new HashMap<String, Map>();
Map<String, Map> sjgnCache = new HashMap<String, Map>();
-
+
for (Map map : categoryList) {
-
+
//涓�绾ф爲
if (map.get("LEVEL").toString().equalsIgnoreCase("1")) {
yjgnCache.put(map.get("ID").toString(), map);
@@ -647,7 +640,7 @@
List<Map> list = (List<Map>)yjgnMap.get("ejTree");
map.put("sjTree", new LinkedList<Map>());
list.add(map);
-
+
ejgnCache.put(map.get("ID").toString(), map);
continue;
}
@@ -657,7 +650,7 @@
map.put("select", 1);
}
if(null!=map.get("P_ID")){
-
+
Map ejgnMap = ejgnCache.get(map.get("P_ID").toString());
List<Map> list = (List<Map>)ejgnMap.get("sjTree");
list.add(map);
--
Gitblit v1.9.1