From 70ddec6cf736c0174bafaef0a9bfe4c9412af133 Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期六, 25 三月 2023 16:37:32 +0800
Subject: [PATCH] 1
---
src/main/java/com/lf/server/mapper/sys/AttachMapper.java | 16 ++++
src/main/java/com/lf/server/service/sys/AttachService.java | 11 ++
data/db_fn.sql | 89 ++++++++++-----------
data/db_tab.sql | 6 +
src/main/java/com/lf/server/service/data/UploadService.java | 9 -
src/main/resources/mapper/sys/AttachMapper.xml | 10 ++
src/main/java/com/lf/server/entity/data/FmeLogEntity.java | 21 +++++
data/db_cx.sql | 35 +-------
8 files changed, 112 insertions(+), 85 deletions(-)
diff --git a/data/db_cx.sql b/data/db_cx.sql
index 521d5f3..1207dfd 100644
--- a/data/db_cx.sql
+++ b/data/db_cx.sql
@@ -4,41 +4,16 @@
select * from lf.sys_layer where url is not null and serve_type='WMS' and data_type in ('宸ョ▼椤圭洰','宸ョ▼椤圭洰-鍦扮伨绫�','宸ョ▼椤圭洰-娴嬮噺绫�','宸ョ▼椤圭洰-娲炲簱绫�','宸ョ▼椤圭洰-鍕樺療绫�') order by id;
insert into lf.sys_layer (pid,cn_name,en_name,url,type,level,order_num,is_show,create_user,serve_type,data_type,elev) values
(423, '鍦伴潰灞�', 'fushun_site', 'tileset/o/39HBFG/tileset.json', 2, 3, 5, 0, 1, 'Tileset', '宸ョ▼娴嬮噺妯″瀷', 1200);
-update lf.sys_layer set elev=118,cn_name='鍦伴潰灞�' where id=427;
-
select concat(gb, '_', name) from bd.dlg_agnp limit 10;
-select a.*, fn_uname(create_user) createName, fn_uname(update_user) updateName from lf.sys_dict a limit 10
-select * from lf.sys_download where position('绠￠亾鍩虹澶ф暟鎹钩鍙�' in descr)>0 order by id desc;
select ST_AsText(geom) "geom", a.*, fn_get_fullname(a.depid, 1) depName, fn_get_fullname(a.dirid, 2) dirName, fn_uname(create_user) createName, fn_uname(update_user) updateName from lf.sys_publish a
--- 娣诲姞鑺傜偣
-alter table lf.sys_layer add column pubid integer;
-insert into lf.sys_layer (pid,cn_name,en_name,type,level,order_num,is_show,create_user,data_type) values (0,'褰卞儚','img',1,1,3,0,1,'褰卞儚鏁版嵁');
-insert into lf.sys_layer (pid,cn_name,en_name,type,level,order_num,is_show,create_user,data_type) values (430,'鑷姩鍙戝竷妯″瀷','auto_model',1,2,25,0,1,'鑷姩鍙戝竷妯″瀷');
-select * from lf.sys_layer order by id desc limit 20;
-
-select * from lf.sys_attach where tab = 'bs.s_explorationpoint' and tab_guid in (select eventid from bs.s_explorationpoint where exppointid = '001')
-select * from lf.sys_meta where id in (1523,1525)
-
--- update lf.sys_meta set path = '1\' || guid || '.' || type where id in (1523, 1525);
-select * from lf.sys_publish; -- delete from lf.sys_publish
-select * from lf.sys_meta_pub; -- delete from lf.sys_meta_pub
-select * from lf.sys_layer order by id desc limit 20; -- delete from lf.sys_layer where id > 441;
-
-select coalesce(max(order_num), 0) + 1 from lf.sys_layer where pid = (select id from lf.sys_layer where cn_name = '鑷姩鍙戝竷妯″瀷' limit 1);
-select * from lf.sys_meta where id in (1523,1529,1525);
-
-select * from lf.sys_publish order by id desc
-select * from lf.sys_meta_pub order by id desc
-select * from lf.sys_layer order by id desc limit 10
-select * from lf.sys_meta where name='鍕樻帰鐐硅〃.xlsx'
+select * from lf.sys_fme_log order by id desc limit 10; -- 236
+select * from lf.sys_meta order by id desc limit 10; -- 1686 > 1696
select * from lf.sys_fme_log
---where tcdm='u_sectionline'
-order by id desc;
-
-
-
+where count > 0 and update_time is null and
+position(tcdm in 'bd.b_pac_hydrogeology,bd.b_pac_frozensoil,bd.b_pac_geologic_hazard,bd.b_pac_marine_meteorological,bd.b_pac_meteorological,bs.m_equipment_nameplate,bs.m_hydraulic_protection,bs.m_marker,bs.s_explorationpoint,bs.u_sectionline') > 0
+ --and create_time > now()::timestamp + '-1 min'
diff --git a/data/db_fn.sql b/data/db_fn.sql
index 628101f..27afa70 100644
--- a/data/db_fn.sql
+++ b/data/db_fn.sql
@@ -320,14 +320,11 @@
begin
update lf.sys_meta set tab = new.pg_ns || '.' || new.tcdm, layer = new.tcmc, rows = new.count where eventid = new.parentid;
- if new.count > 0 and position(new.tcdm in 'b_pac_hydrogeology,b_pac_frozensoil,b_pac_geologic_hazard,b_pac_marine_meteorological,b_pac_meteorological,m_equipment_nameplate,m_hydraulic_protection,m_marker,s_explorationpoint,u_sectionline') > 0 then
- select fn_auto_attach(new.id);
- end if;
-
return new;
end;
$$ language plpgsql;
+-- select id, pg_ns || '.' || tcdm, parentid from lf.sys_fme_log;
----------------------------------------------------------------------------------------------------- 15.鍗曚綅琛ㄨЕ鍙戝櫒鍑芥暟
-- drop function fn_dep_insert();
create or replace function fn_dep_insert() returns trigger as $$
@@ -359,49 +356,6 @@
return new;
end;
$$ language plpgsql;
------------------------------------------------------------------------------------------------------ 18.鍚屾闄勪欢鍑芥暟
--- drop function fn_auto_attach(integer);
-create or replace function fn_auto_attach(sid integer) returns void as $$
- declare
- rec record;
- str varchar;
- tab varchar;
- pid varchar;
- field varchar := 'materiname';
- begin
- select pg_ns || '.' || tcdm, parentid into tab, pid from lf.sys_fme_log a where a.id = sid limit 1;
-
- if position('bs.' in tab) > 0 then field := 'photono'; end if;
-
- execute format('select count(*) from %s where parentid = ''%s'' and %s is not null', tab, pid, field) into sid;
- raise notice 'tab = %, pid = %, field = %, rows = %', tab, pid, field, sid;
-
- if sid = 0 then return; end if;
-
- for rec in execute format('select eventid "eid",%s "ns" from %s where parentid=''%s'' and %s is not null', field, tab, pid, field) loop
- foreach str in array (select string_to_array(replace(replace(replace(rec.ns, '锛�', ','), '锛�', ','), ';', ','), ',')) loop
- raise notice 'eventid = %, photono = %', rec.eid, str;
-
- execute format('insert into lf.sys_attach (name, guid, path, sizes, create_user, tab, tab_guid) ' ||
- 'select name, guid, path, sizes, create_user, ''%s'', ''%s'' from lf.sys_meta ' ||
- 'where name = ''%s'' and create_time > now()::timestamp + ''-1 hour'' order by id desc limit 1', tab, rec.eid, str);
- end loop;
- end loop;
- end;
-$$ language plpgsql;
-
-select fn_auto_attach(142); select fn_auto_attach(143);
-select * from lf.sys_attach where tab = 'bs.m_marker';
-
-select position('s_pac_hydrogeology' in 'b_pac_hydrogeology,bd.b_pac_frozensoil,bd.b_pac_geologic_hazard')
-select parentid, pg_ns || '.' || tcdm from lf.sys_fme_log where id = 143;
-select * from lf.sys_meta where eventid = '1caa28e2-b911-40e1-bfc5-4959a927cdd1';
-select * from bs.m_marker where parentid = '1caa28e2-b911-40e1-bfc5-4959a927cdd1';
-select * from lf.sys_fme_log
-select * from lf.sys_meta a inner join lf.sys_fme_log b on a.eventid= b.parentid
-select * from lf.sys_attach where tab='bs.m_marker';
-
-select * from lf.sys_meta where name in ('璀︾ず鐗�10002.jpg','璀︾ず鐗�10004.jpg') order by id desc
----------------------------------------------------------------------------------------------------- 19.璺緞鍒嗘瀽
-- 鍒犻櫎宸插瓨鍦ㄧ殑鍑芥暟
-- drop function pgr_fromAtoB(varchar, double precision, double precision, double precision, double precision);
@@ -693,4 +647,45 @@
SELECT SUBSTR(S, 2), (POWER(62, LENGTH(S)::NUMERIC - 1) * (strpos('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', SUBSTR(S, 1, 1)) - 1) + N) FROM T WHERE LENGTH(S) > 0
)
SELECT N::NUMERIC(30, 0) FROM T WHERE LENGTH(S) < 1;
+----------------------------------------------------------------------------------------------------- e.FME鏃ュ織琛ㄨЕ鍙戝櫒鍑芥暟 *
+-- drop function fn_meta_insert();
+create or replace function fn_meta_insert() returns trigger as $$
+ begin
+ update lf.sys_meta set tab = new.pg_ns || '.' || new.tcdm, layer = new.tcmc, rows = new.count where eventid = new.parentid;
+
+ --if new.count > 0 and position(new.tcdm in 'b_pac_hydrogeology,b_pac_frozensoil,b_pac_geologic_hazard,b_pac_marine_meteorological,b_pac_meteorological,m_equipment_nameplate,m_hydraulic_protection,m_marker,s_explorationpoint,u_sectionline') > 0 then
+ -- select fn_auto_attach(new.id, new.pg_ns || '.' || new.tcdm, new.parentid);
+ --end if;
+
+ return new;
+ end;
+$$ language plpgsql;
+----------------------------------------------------------------------------------------------------- f.鍚屾闄勪欢鍑芥暟 *
+-- drop function fn_auto_attach(integer, varchar, varchar);
+create or replace function fn_auto_attach(sid integer, tab varchar, pid varchar) returns void as $$
+ declare
+ rec record;
+ str varchar;
+ field varchar := 'materiname';
+ begin
+ if position('bs.' in tab) > 0 then field := 'photono'; end if;
+
+ execute format('select count(*) from %s where parentid = ''%s'' and %s is not null', tab, pid, field) into sid;
+ --raise notice 'tab = %, pid = %, field = %, rows = %', tab, pid, field, sid;
+
+ if sid = 0 then return; end if;
+
+ for rec in execute format('select eventid "eid",%s "ns" from %s where parentid=''%s'' and %s is not null', field, tab, pid, field) loop
+ foreach str in array (select string_to_array(replace(replace(replace(rec.ns, '锛�', ','), '锛�', ','), ';', ','), ',')) loop
+ --raise notice 'eventid = %, photono = %', rec.eid, str;
+
+ execute format('insert into lf.sys_attach (name, guid, path, sizes, create_user, tab, tab_guid) ' ||
+ 'select name, guid, path, sizes, create_user, ''%s'', ''%s'' from lf.sys_meta ' ||
+ 'where name = ''%s'' and create_time > now()::timestamp + ''-1 hour'' order by id desc limit 1', tab, rec.eid, str);
+ end loop;
+ end loop;
+ end;
+$$ language plpgsql;
+
+-- select fn_auto_attach(142); select fn_auto_attach(143);
-----------------------------------------------------------------------------------------------------
diff --git a/data/db_tab.sql b/data/db_tab.sql
index 7ba6609..1b3d5fe 100644
--- a/data/db_tab.sql
+++ b/data/db_tab.sql
@@ -1072,7 +1072,8 @@
tcmc varchar(150),
tcdm varchar(50),
count integer default 0,
- create_time timestamp(6) without time zone default now()
+ create_time timestamp(6) without time zone default now(),
+ update_time timestamp(6) without time zone
);
comment on table lf.sys_fme_log is 'FME鏃ュ織琛�';
comment on column lf.sys_fme_log.id is '涓婚敭ID';
@@ -1083,8 +1084,9 @@
comment on column lf.sys_fme_log.tcdm is '琛ㄥ悕';
comment on column lf.sys_fme_log.count is '琛屾暟';
comment on column lf.sys_fme_log.create_time is '鍒涘缓鏃堕棿';
+comment on column lf.sys_fme_log.update_time is '鏇存柊鏃堕棿';
-select * from lf.sys_fme_log; -- delete from lf.sys_fme_log;
+select * from lf.sys_fme_log order by id desc; -- delete from lf.sys_fme_log;
----------------------------------------------------------------------------------------------------- 32.鍥惧眰琛�
-- DROP TABLE IF EXISTS lf.sys_layer;
create table lf.sys_layer(
diff --git a/src/main/java/com/lf/server/entity/data/FmeLogEntity.java b/src/main/java/com/lf/server/entity/data/FmeLogEntity.java
index 1a708e5..5d87ed0 100644
--- a/src/main/java/com/lf/server/entity/data/FmeLogEntity.java
+++ b/src/main/java/com/lf/server/entity/data/FmeLogEntity.java
@@ -1,6 +1,7 @@
package com.lf.server.entity.data;
import java.io.Serializable;
+import java.sql.Timestamp;
/**
* FME鏃ュ織琛�
@@ -25,6 +26,10 @@
private String tcdm;
private Integer count;
+
+ private Timestamp createTime;
+
+ private Timestamp updateTime;
public Integer getId() {
return id;
@@ -81,4 +86,20 @@
public void setCount(Integer count) {
this.count = count;
}
+
+ public Timestamp getCreateTime() {
+ return createTime;
+ }
+
+ public void setCreateTime(Timestamp createTime) {
+ this.createTime = createTime;
+ }
+
+ public Timestamp getUpdateTime() {
+ return updateTime;
+ }
+
+ public void setUpdateTime(Timestamp updateTime) {
+ this.updateTime = updateTime;
+ }
}
diff --git a/src/main/java/com/lf/server/mapper/sys/AttachMapper.java b/src/main/java/com/lf/server/mapper/sys/AttachMapper.java
index c8598fb..2a0563a 100644
--- a/src/main/java/com/lf/server/mapper/sys/AttachMapper.java
+++ b/src/main/java/com/lf/server/mapper/sys/AttachMapper.java
@@ -1,5 +1,6 @@
package com.lf.server.mapper.sys;
+import com.lf.server.entity.data.FmeLogEntity;
import com.lf.server.entity.sys.AttachEntity;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@@ -137,4 +138,19 @@
* @return
*/
public Integer updates(List<AttachEntity> list);
+
+ /**
+ * 鏌ヨFME鏃ュ織
+ *
+ * @return
+ */
+ public List<FmeLogEntity> selectFmeLogs();
+
+ /**
+ * 鏇存柊FME鏃ュ織
+ *
+ * @param id ID
+ * @return
+ */
+ public Integer updateFmeLog(Integer id);
}
diff --git a/src/main/java/com/lf/server/service/data/UploadService.java b/src/main/java/com/lf/server/service/data/UploadService.java
index 5c05c6b..27829af 100644
--- a/src/main/java/com/lf/server/service/data/UploadService.java
+++ b/src/main/java/com/lf/server/service/data/UploadService.java
@@ -101,21 +101,18 @@
/**
* Excel鍏ュ簱
*/
- private String excelLoader(List<MetaFileEntity> list, HttpServletRequest req) {
+ private void excelLoader(List<MetaFileEntity> list, HttpServletRequest req) {
List<MetaFileEntity> xlsList = getExcelFiles(list);
if (xlsList.isEmpty()) {
- return "";
+ return;
}
- String guid = null;
try {
MetaFileEntity meta = getExcelMeta(xlsList);
- guid = fmeService.excelLoader(meta, req);
+ String guid = fmeService.excelLoader(meta, req);
} catch (Exception ex) {
log.error(ex.getMessage(), ex);
}
-
- return guid;
}
/**
diff --git a/src/main/java/com/lf/server/service/sys/AttachService.java b/src/main/java/com/lf/server/service/sys/AttachService.java
index 5d08250..205fd6f 100644
--- a/src/main/java/com/lf/server/service/sys/AttachService.java
+++ b/src/main/java/com/lf/server/service/sys/AttachService.java
@@ -1,5 +1,6 @@
package com.lf.server.service.sys;
+import com.lf.server.entity.data.FmeLogEntity;
import com.lf.server.entity.sys.AttachEntity;
import com.lf.server.helper.StringHelper;
import com.lf.server.mapper.sys.AttachMapper;
@@ -95,4 +96,14 @@
public Integer updates(List<AttachEntity> list) {
return attachMapper.updates(list);
}
+
+ @Override
+ public List<FmeLogEntity> selectFmeLogs() {
+ return attachMapper.selectFmeLogs();
+ }
+
+ @Override
+ public Integer updateFmeLog(Integer id) {
+ return attachMapper.updateFmeLog(id);
+ }
}
diff --git a/src/main/resources/mapper/sys/AttachMapper.xml b/src/main/resources/mapper/sys/AttachMapper.xml
index d38fb6d..3f3c070 100644
--- a/src/main/resources/mapper/sys/AttachMapper.xml
+++ b/src/main/resources/mapper/sys/AttachMapper.xml
@@ -103,4 +103,14 @@
where id = #{item.id}
</foreach>
</update>
+
+ <select id="selectFmeLogs" resultType="com.lf.server.entity.data.FmeLogEntity">
+ select * from lf.sys_fme_log
+ where count > 0 and update_time is null and create_time > now()::timestamp + '-1 min'
+ position(tcdm in 'bd.b_pac_hydrogeology,bd.b_pac_frozensoil,bd.b_pac_geologic_hazard,bd.b_pac_marine_meteorological,bd.b_pac_meteorological,bs.m_equipment_nameplate,bs.m_hydraulic_protection,bs.m_marker,bs.s_explorationpoint,bs.u_sectionline') > 0;
+ </select>
+
+ <update id="updateFmeLog">
+ update lf.sys_fme_log set update_time = now() where id = #{id};
+ </update>
</mapper>
\ No newline at end of file
--
Gitblit v1.9.3