From da30b02214ab3a35a3b2ac163afce87e70a39ff4 Mon Sep 17 00:00:00 2001
From: cy <1664593601@qq.com>
Date: 星期四, 23 六月 2022 10:30:10 +0800
Subject: [PATCH] fix : MySQL=》DMSQL
---
src/cn/ksource/web/facade/rcxj/RcxjFacadeImpl.java | 62 +++++++--------
src/cn/ksource/web/facade/supplier/SupplierFacadeImpl.java | 37 +++------
src/cn/ksource/web/facade/customerconfig/slacustomer/SlaCustomerFacadeImpl.java | 86 ++++++++++-----------
3 files changed, 84 insertions(+), 101 deletions(-)
diff --git a/src/cn/ksource/web/facade/customerconfig/slacustomer/SlaCustomerFacadeImpl.java b/src/cn/ksource/web/facade/customerconfig/slacustomer/SlaCustomerFacadeImpl.java
index d521c39..aeafa3c 100644
--- a/src/cn/ksource/web/facade/customerconfig/slacustomer/SlaCustomerFacadeImpl.java
+++ b/src/cn/ksource/web/facade/customerconfig/slacustomer/SlaCustomerFacadeImpl.java
@@ -1,15 +1,5 @@
package cn.ksource.web.facade.customerconfig.slacustomer;
-import java.util.ArrayList;
-import java.util.HashMap;
-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.CI_DAILY_PATROL_ITEM;
import cn.ksource.beans.CMDB_CI_CATEGORY;
import cn.ksource.core.dao.BaseDao;
@@ -18,7 +8,13 @@
import cn.ksource.core.util.ConvertUtil;
import cn.ksource.core.util.JsonUtil;
import cn.ksource.core.util.StringUtil;
-import cn.ksource.core.web.WebUtil;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
@Service("slaCustomerFacade")
public class SlaCustomerFacadeImpl implements SlaCustomerFacade{
@Autowired
@@ -52,8 +48,8 @@
param.put("itemName", "%"+itemName+"%");
param.put("itemType", itemType);
param.put("state", state);
-
-
+
+
return baseDao.queryforSplitPageInfo(pageInfo, selectSql.toString(), param);
}
/**
@@ -155,10 +151,10 @@
String thirdCateId = param.get("business_id");
String thirdCateName = param.get("business_name");
String p_id = param.get("p_id");
-
+
item.setId(id).setState(1).setItem_type(2).setBusiness_id(thirdCateId).setBusiness_name(thirdCateName)
.setP_id(p_id).setCustomer_id(customerId).insert();
-
+
String insertSql = "INSERT INTO CI_DAILY_PATROL_CUSTOMER_ITEM(ID,CUSTOMER_ID,ITEM_ID,USING_STATE) VALUES (:id,:customerId,:itemId,1)";
param.put("id", StringUtil.getUUID());
param.put("itemId", id);
@@ -181,7 +177,7 @@
baseDao.execute(updateSql, param);
return true;
}
-
+
/**
* 鏌ヨ椤圭洰鍙互澧炲姞鐨勬寚鏍囬」
*/
@@ -238,7 +234,7 @@
param.put("itemName", "%"+itemName+"%");
return baseDao.queryForInteger(selectSql.toString(),param);
}
-
+
/**
* 淇濆瓨閫夋嫨鐨勬寚鏍囬」
*/
@@ -278,30 +274,30 @@
builder.append(" AND A.SUB_CUSTOMER_ID = :subCustomerId ");
paramMap.put("subCustomerId", subCustomerId);
}
-
+
if(StringUtil.notEmpty(categoryId)) {
builder.append(" AND B.LV3_ID = :categoryId ");
paramMap.put("categoryId", categoryId);
}
-
+
if(StringUtil.notEmpty(ciName)) {
builder.append(" AND B.CINAME LIKE :ciName ");
paramMap.put("ciName", "%"+ciName+"%");
}
-
+
builder.append(" ORDER BY B.CREATE_TIME DESC ");
-
+
PageInfo info = baseDao.queryforSplitPageInfo(pageInfo, builder.toString(), paramMap);
return info;
}
-
+
/**
* 鏃ュ父宸℃璁惧鏁伴噺
*/
@Override
public int queryDailyDeviceCount(String customerId,String subCustomerId,String categoryId,String ciName) {
StringBuilder builder = new StringBuilder("SELECT COUNT(A.ID) FROM CI_DAILY_PATROL_CI A,CMDB_CI_BASE B WHERE A.CI_ID = B.ID AND B.STATE = 1 AND A.CUSTOMER_ID = :customerId");
-
+
Map paramMap = new HashMap();
paramMap.put("customerId", customerId);
if(StringUtil.notEmpty(subCustomerId)) {
@@ -312,16 +308,16 @@
builder.append(" AND B.LV3_ID = :categoryId ");
paramMap.put("categoryId", categoryId);
}
-
+
if(StringUtil.notEmpty(ciName)) {
builder.append(" AND B.CINAME LIKE :ciName ");
paramMap.put("ciName", "%"+ciName+"%");
}
-
+
int count = baseDao.queryForInteger(builder.toString(),paramMap);
return count;
}
-
+
@Override
public List queryAddDailyDevice(String customerId,String subCustomerId,String ciName,String cateId,String sub_customer_id) {
StringBuilder builder = new StringBuilder("SELECT A.ID,A.SEARCHCODE,A.CINAME,A.LV3_NAME,A.POSITION,A.CUS_ID,A.SUB_CUS_ID,A.SUB_CUS_NAME " +
@@ -344,12 +340,12 @@
builder.append(" AND A.LV3_ID = :cateId ");
paramMap.put("cateId", cateId);
}
-
+
builder.append(" AND NOT EXISTS (SELECT B.ID FROM CI_DAILY_PATROL_CI B WHERE A.ID = B.CI_ID AND B.CUSTOMER_ID = :customerId ) ORDER BY A.CREATE_TIME DESC ");
List devices = baseDao.queryForList(builder.toString(),paramMap);
return devices;
}
-
+
@Override
public Integer queryAddDailyDeviceCount(String customerId,
String subCustomerId, String ciName, String cateId,
@@ -374,11 +370,11 @@
builder.append(" AND A.LV3_ID = :cateId ");
paramMap.put("cateId", cateId);
}
-
+
builder.append(" AND NOT EXISTS (SELECT B.ID FROM CI_DAILY_PATROL_CI B WHERE A.ID = B.CI_ID AND B.CUSTOMER_ID = :customerId ) ORDER BY A.CREATE_TIME DESC ");
Integer i = baseDao.queryForInteger(builder.toString(),paramMap);
return i;
- }
+ }
/**
* 娣诲姞椤圭洰闇�宸℃璁惧
* @param request
@@ -415,8 +411,8 @@
baseDao.execute(deleteSql, new SqlParameter("id",id));
return true;
}
-
-
+
+
/**
* 鏌ヨ鍋ュ悍妫�鏌ユ寚鏍囬」閰嶇疆
* @param request
@@ -592,7 +588,7 @@
String steps=param.get("steps");
String expect_value=param.get("expect_value");
String order_num = param.get("order_num");
-
+
String[] cateIds = param.get("full_cate_id").split("-");
String[] cateNames = param.get("full_cate_name").split("-");
if (cateIds.length == 3) {
@@ -604,9 +600,9 @@
param.put("business_name", cateNames[1]);
param.put("p_id", cateIds[0]);
}
-
+
String id = StringUtil.getUUID();
-
+
String insertSql = "INSERT INTO CI_HEALTH_ITEM(ID,ITEM_NAME,PRIORITY_LEVEL_ID,PRIORITY_LEVEL_NAME,ITEM_NOTE,ORDER_NUM,STATE,BUSINESS_ID,BUSINESS_NAME,ITEM_TYPE,CUSTOMER_ID,STEPS,EXPECT_VALUE,P_ID,FULL_CATE_ID,FULL_CATE_NAME) VALUES " +
"(:id,:item_name,:priority_level_id,:priority_level_name,:item_note,:order_num,1,:business_id,:business_name,2,:customerId,:steps,:expect_value,:pId,:full_cate_id,:full_cate_name)";
param.put("id", id);
@@ -621,9 +617,9 @@
param.put("steps", steps);
param.put("expect_value", expect_value);
param.put("pId", param.get("p_id"));
-
+
baseDao.execute(insertSql, param);
-
+
String saveProjectItemSql = "INSERT INTO CI_HEALTH_CUSTOMER_ITEM(ID,CUSTOMER_ID,ITEM_ID,USING_STATE) VALUES (:id,:customerId,:item_id,1)";
Map params = new HashMap();
String projectItemId = StringUtil.getUUID();
@@ -659,7 +655,7 @@
baseDao.execute(updateSql, param);
return true;
}
-
+
/**
* 鏌ヨ閫氱敤宸℃椤瑰垎绫�
*/
@@ -765,7 +761,7 @@
baseDao.execute(updateSql, param);
return true;
}
-
+
/**
* 鍚敤鎴栬�呯鐢ㄩ�氱敤宸℃椤瑰垎绫�
*/
@@ -779,7 +775,7 @@
baseDao.execute(updateSql, param);
return true;
}
-
+
/**
* 鍚敤鎴栬�呯鐢ㄩ�氱敤宸℃椤�
*/
@@ -793,13 +789,13 @@
baseDao.execute(updateSql, param);
return true;
}
-
+
/**
* 鑾峰彇璁惧鍒嗙被鏍�
*/
@Override
public List<Map> getCiCategoryTree(String pid,String type) {
- String sql = " SELECT ID as id,PID as pid,NAME as name,LV as jb,STATE,SERIAL FROM CMDB_CI_CATEGORY WHERE STATE=1 ";
+ String sql = " SELECT ID as id,PID as pid,NAME as \"name\",LV as jb,STATE,SERIAL FROM CMDB_CI_CATEGORY WHERE STATE=1 ";
SqlParameter param = new SqlParameter();
if(StringUtil.notEmpty(type)){
sql+=" and cate_type=:type";
@@ -817,7 +813,7 @@
sql="";
sql="select DISTINCT LV1_ID AS id,\n" +
" '0' AS pid ,\n" +
- " LV1_name AS name,\n" +
+ " LV1_name AS \"name\",\n" +
" '1' AS lv,STATE,SERIAL from CMDB_CI_CATEGORY where STATE=1 and lv=3 and CATE_TYPE=2 ";
}else if(StringUtil.isNotBlank(pid) && type.equals("2")){
CMDB_CI_CATEGORY cmdb_ci_category=new CMDB_CI_CATEGORY(pid).getInstanceById();
@@ -826,11 +822,11 @@
sql="";
sql="select DISTINCT LV1_ID AS id,\n" +
" '0' AS pid ,\n" +
- " LV1_name AS name,\n" +
+ " LV1_name AS \"name\",\n" +
" '1' AS lv,STATE,SERIAL from CMDB_CI_CATEGORY where STATE=1 and lv=3 and CATE_TYPE=2 and LV1_ID=:pid ";
param.addValue("pid", pid);
}
-
+
}
sql += " ORDER BY STATE,SERIAL ";
List<Map> list = baseDao.queryForList(sql,param);
diff --git a/src/cn/ksource/web/facade/rcxj/RcxjFacadeImpl.java b/src/cn/ksource/web/facade/rcxj/RcxjFacadeImpl.java
index 0773506..4415e55 100644
--- a/src/cn/ksource/web/facade/rcxj/RcxjFacadeImpl.java
+++ b/src/cn/ksource/web/facade/rcxj/RcxjFacadeImpl.java
@@ -1,14 +1,5 @@
package cn.ksource.web.facade.rcxj;
-import java.util.ArrayList;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
-
-import javax.annotation.Resource;
-
-import org.springframework.stereotype.Service;
-
import cn.ksource.beans.CI_DAILY_PATROL;
import cn.ksource.beans.FlowRecord;
import cn.ksource.core.dao.BaseDao;
@@ -20,6 +11,13 @@
import cn.ksource.core.util.StringUtil;
import cn.ksource.web.Constants;
import cn.ksource.web.service.flow.FlowRecordService;
+import org.springframework.stereotype.Service;
+
+import javax.annotation.Resource;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
@Service
@SuppressWarnings({"rawtypes"})
@@ -29,8 +27,8 @@
private BaseDao baseDao;
@Resource
private FlowRecordService flowRecordService;
-
-
+
+
@Override
public PageInfo getRcxjData(PageInfo pageInfo, Map<String, String> params) {
Map support = getRcxjSupport(params);
@@ -69,18 +67,18 @@
if(StringUtil.isNotBlank(params.get("curId"))){
sql.append(" and a.cur_id = :curId ");
}
-
+
if(StringUtil.isNotBlank(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 = a.id ) ");
}
-
+
if(StringUtil.isNotBlank(params.get("fstate"))){
sql.append(" and a.state = :fstate ");
}
-
+
if(StringUtil.isNotBlank(params.get("state"))){
if(params.get("state").equals("3")){
sql.append(" and a.state > :state ");
@@ -91,7 +89,7 @@
sql.append(" and a.state = :state ");
}
}
-
+
sql.append(" order by a.create_time desc ");
support.put("sql", sql.toString());
support.put("param", params);
@@ -105,7 +103,7 @@
.addValue("orderId", orderId);
//鏌ヨ鍒嗙被
StringBuilder sql = new StringBuilder();
-
+
sql.append(" select ID,CATEGORY_NAME from CI_DAILY_PATROL_CUSTOMER_COMMONS_CATEGORY ");
sql.append(" where customer_id = :cusId and USING_state=1 order by ordernum asc ");
List<Map> categoryList = baseDao.queryForList(sql.toString(),param);
@@ -122,7 +120,7 @@
sql.append(" ) t left join (select * from CI_DAILY_PATROL_COMMON_RECORD where PATROL_ID = :orderId ) r on t.item_id = r.item_id ");
}
List<Map> datalist = baseDao.queryForList(sql.toString(),param);
-
+
//鎷艰鏁版嵁
List<Map> resList = new ArrayList<Map>();
if(categoryList!=null&&categoryList.size()>0){
@@ -157,7 +155,7 @@
sql.append(" inner join cmdb_ci_base c on p.ci_id = c.id ");
sql.append(" inner join ci_daily_ci_item_re r on r.ci_id = p.ci_id ");
sql.append(" where c.state =1 and p.customer_id =:cusId ");
- if(StringUtil.isNotBlank(params.get("subCusId"))){
+ if(StringUtil.isNotBlank(params.get("subCusId"))){
sql.append(" and p.sub_customer_id =:subCusId ");
}
if(StringUtil.isNotBlank(params.get("errflag"))){//鍙煡宸℃璇㈠紓甯搁厤缃�
@@ -165,9 +163,9 @@
sql.append(" select ci_id from (select ci_id from ci_daily_patrol_record where result<>1 and patrol_id=:orderId group by ci_id ) t where t.ci_id = c.id ");
sql.append(" ) ");
}
- sql.append(" group by c.id ");
+ sql.append(" group by c.id,c.lv3_id, c.ciname, c.searchcode,c.position ");
List<Map> ciList = baseDao.queryForList(sql.toString(),params);
-
+
//鏌ヨ鎸囨爣
sql.setLength(0);
sql.append(" select b.ci_id,b.id,d.item_name,e.result,e.note from ");
@@ -181,7 +179,7 @@
}
sql.append(" where a.customer_id = :cusId ");
List<Map> itemList = baseDao.queryForList(sql.toString(),params);
-
+
//缁勮鏁版嵁
if(ciList!=null&&ciList.size()>0){
Map<String, Map> ciSet = new HashMap<String, Map>();
@@ -215,7 +213,7 @@
//鎸囨爣鏁伴噺
int nitem_num = 0;
int aitem_num = 0;
-
+
Map<String, String> ciMap = new HashMap<String, String>();
Map<String, String> aciMap = new HashMap<String, String>();
if(itemlist!=null&&itemlist.size()>0){
@@ -232,19 +230,19 @@
ciMap.put(c_ci_id, "1");
}
}
-
+
report.setNci_num(ciMap.size()-aciMap.size())
.setAci_num(aciMap.size())
.setNitem_num(nitem_num)
.setAitem_num(aitem_num);
-
+
if (StringUtil.isBlank(params.get("orderId"))) {//鏂板
report = report.setCreate_time(DateUtil.getCurrentDate14())
.setUser_id(params.get("user_id")).insert();
} else {
report = report.setId(params.get("orderId")).update();
}
-
+
//閫氱敤宸℃璁板綍
saveCommonPatrolRecord(report.getId(),commonJsonListStr);
//璁惧宸℃璁板綍
@@ -253,7 +251,7 @@
saveLinkOrder(report.getId(),linkOrderId);
return report;
}
-
+
/**
* 淇濆瓨鍏宠仈宸ュ崟淇℃伅
* @param orderId
@@ -278,7 +276,7 @@
}
}
}
-
+
private void saveCommonPatrolRecord(String orderId, String commonJsonListStr){
List<Map> list = JsonUtil.json2List(commonJsonListStr);
baseDao.execute("delete from ci_daily_patrol_common_record where patrol_id = :orderId ",
@@ -310,7 +308,7 @@
baseDao.executeBatch(sql, paramList);
}
}
-
+
private void saveCiPatrolRecord(String orderId, Map<String, String> params){
String ciJsonListStr = params.get("ciJsonList");
List<Map> list = JsonUtil.json2List(ciJsonListStr);
@@ -374,7 +372,7 @@
patrolDate = DateUtil.getCurrentDate6().toString();
}
StringBuilder sql = new StringBuilder();
- sql.append(" select concat(',',GROUP_CONCAT(DISTINCT right(PATROL_DATE,2)),',') ");
+ sql.append(" select concat(',',GROUP_CONCAT(DISTINCT right(PATROL_DATE,2)),',') ");
sql.append(" from CI_DAILY_PATROL p ");
sql.append(" where ACI_NUM>0 and PATROL_DATE like :patrolDate ");
if(StringUtil.isNotBlank(params.get("userId"))){
@@ -395,7 +393,7 @@
patrolDate = DateUtil.getCurrentDate6().toString();
}
StringBuilder sql = new StringBuilder();
- sql.append(" select CONVERT(concat(',',GROUP_CONCAT(DISTINCT right(PATROL_DATE,2)),',') USING utf8) ");
+ sql.append(" select CONVERT(concat(',',GROUP_CONCAT(DISTINCT right(PATROL_DATE,2)),',') USING utf8) ");
sql.append(" from CI_DAILY_PATROL p ");
sql.append(" where PATROL_DATE like :patrolDate ");
if(StringUtil.isNotBlank(params.get("userId"))){
@@ -450,7 +448,7 @@
String sql = " select * from flow_record where template_key = :key and bus_id = :orderId order by deal_time desc limit 1 ";
return baseDao.queryForMap(sql,param);
}
-
+
@Override
public List<Map> getLinkOrderList(String orderId) {
String sql = " select r.RELATE_FLOW_ID FLOWID,b.* from ORDER_REF_ORDER r " +
@@ -458,5 +456,5 @@
" where r.BUSINESS_ID = :orderId ";
return baseDao.queryForList(sql, new SqlParameter("orderId",orderId));
}
-
+
}
diff --git a/src/cn/ksource/web/facade/supplier/SupplierFacadeImpl.java b/src/cn/ksource/web/facade/supplier/SupplierFacadeImpl.java
index bfab8d7..23dec29 100644
--- a/src/cn/ksource/web/facade/supplier/SupplierFacadeImpl.java
+++ b/src/cn/ksource/web/facade/supplier/SupplierFacadeImpl.java
@@ -7,21 +7,10 @@
import cn.ksource.core.util.ConvertUtil;
import cn.ksource.core.util.DateUtil;
import cn.ksource.core.util.StringUtil;
-import cn.ksource.web.Constants;
-
-import com.lowagie.text.pdf.PRAcroForm;
-
-import org.apache.commons.lang.StringEscapeUtils;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
-
-import java.sql.Struct;
-import java.util.ArrayList;
-import java.util.Arrays;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
+import java.util.*;
/**
* Created by chenlong
@@ -75,7 +64,7 @@
if(StringUtil.isBlank(params.get("gmt_register"))){
params.put("gmt_register", null);
}
-
+
StringBuilder sql = new StringBuilder();
if(StringUtil.isBlank(id)){//鏂板
id = StringUtil.getUUID();
@@ -87,7 +76,7 @@
"email=:email,contacts=:contacts,contacts_tel=:contacts_tel,province=:province,province_id=:province_id,city=:city,city_id=:city_id,county=:county,county_id=:county_id," +
"address=:address,note=:note,gmt_modified=:gmt_modified where id=:id ");
}
-
+
//淇濆瓨鍩烘湰淇℃伅
baseDao.execute(sql.toString(), params);
SUPPLIER_MODIFY_RECORD supplier_modify_record=new SUPPLIER_MODIFY_RECORD();
@@ -157,7 +146,7 @@
@Override
public String delSupplier(String id,String state) {
-
+
String sqlString="select count(*) from SPARE_PART where SUPPLIER_ID=:supplier_id and state in(1,2,3,4,5)";
Map paramMap = new HashMap();
paramMap.put("supplier_id", id);
@@ -391,7 +380,7 @@
" from spare_part a,spare_part_apply_entity b,cmdb_ci_base c ,spare_part_apply d,cmdb_ci_category e " +
" where a.id = b.entity_id and b.ci_id = c.id and b.order_id=d.id and b.state=2 and a.cate_id =e.id ");
-
+
//椤圭洰
if(StringUtil.isNotBlank(params.get("cus_id"))){
sql.append(" and c.cus_id = :cus_id ");
@@ -430,10 +419,10 @@
String sql = "select count(*) from ( "+this.getCateGorySpareList(params)+ " ) t";
return baseDao.queryForInteger(sql, params);
}
-
+
// 鑾峰緱澶囦欢鍒嗙被鍒楄〃鏌ヨ鏉′欢
private String getCateGorySpareList(Map<String,String> params){
-
+
StringBuilder sql = new StringBuilder("SELECT b.id,b.LV1_NAME,b.LV2_NAME,b.LV3_NAME,b.CODE,(select code from cmdb_ci_category where id=b.LV1_id) code1,(select code from cmdb_ci_category where id=b.LV2_id) code2 ,count(*) SL FROM "
+" cmdb_ci_category b left join spare_part a on b.ID = a.CATE_ID"
+" WHERE a.state = 1 and b.state = 1 and b.cate_type = 2 ");
@@ -456,19 +445,19 @@
sql.append(")");
}
}
-
+
if (StringUtil.isNotBlank(cate_id)) {
sql.append(" and b.ID =:bjfl ");
params.put("bjfl",cate_id );
}
-
+
if (StringUtil.isNotBlank(code)) {
sql.append(" and b.code like :flbm ");
params.put("flbm","%"+code+"%" );
}
- sql.append(" group by b.id");
+ sql.append(" group by b.id,b.LV1_id,b.LV2_id,b.LV1_NAME, b.LV2_NAME, b.LV3_NAME, b.CODE");
return sql.toString();
-
+
}
@Override
@@ -492,7 +481,7 @@
return info;
}
-
+
@Override
public List<Map> getSparePartOverageSearchListData(
Map<String, String> params) {
@@ -502,7 +491,7 @@
params.put("id", params.get("id"));
return baseDao.queryForList(sql.toString(), params);
}
-
+
/**
* 渚涘簲鍟嗗鐞嗚褰�
* @param id
--
Gitblit v1.9.1