From 06492d128df5d7d0905539dd04faa9d475e9de59 Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期一, 13 二月 2023 15:12:59 +0800
Subject: [PATCH] 1
---
data/ts.sql | 41 ++-----
src/main/java/com/lf/server/controller/data/MetaController.java | 2
data/db_fn.sql | 147 ++++++++++++++++++++++++-----
data/db_tab.sql | 12 +
src/main/java/com/lf/server/entity/data/DirEntity.java | 41 ++++++--
data/update.sql | 32 ++++-
6 files changed, 196 insertions(+), 79 deletions(-)
diff --git a/data/db_fn.sql b/data/db_fn.sql
index afd9235..1aa9547 100644
--- a/data/db_fn.sql
+++ b/data/db_fn.sql
@@ -158,23 +158,7 @@
$$ language plpgsql;
select fn_get_entity('dlg_25w_boua_s');
------------------------------------------------------------------------------------------------------ 08.FME鏃ュ織瑙﹀彂鍣�
-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;
-
- return new;
- end;
-$$ language plpgsql;
-
-create or replace trigger fme_log_trigger after insert on lf.sys_fme_log
-for each row execute procedure fn_meta_insert();
-
-select * from lf.sys_meta;
-/*insert into lf.sys_fme_log (parentid,dirpath,pg_ns,tcmc,tcdm,count) values
- ('8fb3c3dd-6a12-488d-80ae-a93cc7e8b2e3','','bs','涓嚎鎴愭灉琛�','m_pipelinepoint',0);
-select * from bs.m_pipelinepoint where parentid='8fb3c3dd-6a12-488d-80ae-a93cc7e8b2e3';*/
------------------------------------------------------------------------------------------------------ 09.鏌ヨ瀛楀吀琛ㄥ苟缁熻璁板綍
+----------------------------------------------------------------------------------------------------- 08.鏌ヨ瀛楀吀琛ㄥ苟缁熻璁板綍
-- drop function fn_tab_count(varchar, varchar, varchar, integer);
create or replace function fn_tab_count(ns varchar, tab varchar, dirs varchar, depid integer)
returns integer as $$
@@ -208,9 +192,9 @@
select fn_rec_array(1, 'dep')
select count(*) from lf.sys_meta where depid = ANY(fn_rec_array(1, 'dep'));
select count(*) from bd.dlg_25w_lrdl where depid = ANY(fn_rec_array(1, 'dep'));
------------------------------------------------------------------------------------------------------ 10.10杩涘埗杞�62杩涘埗
--- drop function fn_10to62(numeric);
-create or replace function fn_10to62(num numeric(30, 0))
+----------------------------------------------------------------------------------------------------- 09.10杩涘埗杞�62杩涘埗
+-- drop function fn_10_to_62(numeric);
+create or replace function fn_10_to_62(num numeric(30, 0))
returns varchar as $$
declare
rs varchar;
@@ -230,10 +214,10 @@
end;
$$ language plpgsql;
-select fn_10to62(3843); select fn_10to62(0); select fn_10to62(3844);
------------------------------------------------------------------------------------------------------ 11.62杩涘埗杞�10杩涘埗
--- drop function fn_62to10(numeric);
-create or replace function fn_62to10(ch varchar)
+select fn_10_to_62(3843); select fn_10_to_62(0); select fn_10_to_62(3844);
+----------------------------------------------------------------------------------------------------- 10.62杩涘埗杞�10杩涘埗
+-- drop function fn_62_to_10(varchar);
+create or replace function fn_62_to_10(ch varchar)
returns numeric as $$
declare
rs numeric(30, 0);
@@ -249,8 +233,119 @@
end;
$$ language plpgsql;
-select fn_62to10('zz'); select fn_62to10('0'); select fn_62to10('100');
------------------------------------------------------------------------------------------------------ 12.璺緞鍒嗘瀽
+select fn_62_to_10('zz'); select fn_62_to_10('0'); select fn_62_to_10('100');
+----------------------------------------------------------------------------------------------------- 11.鑾峰彇涓嬩竴涓紪鐮�
+-- drop function fn_get_next_code(integer, integer);
+create or replace function fn_get_next_code(pid integer, genre integer)
+returns varchar as $$
+ declare
+ cc integer;
+ code varchar;
+ pcode varchar;
+ tab varchar := 'dep';
+ begin
+ if (pid is null or genre is null) then
+ return '00';
+ end if;
+ if genre = 2 then
+ tab := 'dir';
+ end if;
+
+ execute 'select code from lf.sys_' || tab || ' where id = ' || pid into pcode;
+ if pcode is null then
+ pcode := '';
+ end if;
+
+ for ii in 0..3843 loop
+ code := fn_10_to_62(ii);
+ if length(code) = 1 then
+ code := '0' || code;
+ end if;
+
+ execute 'select count(*) from lf.sys_' || tab || ' where pid = ' || pid || ' and code = ''' || pcode || code || '''' into cc;
+ if cc = 0 then
+ return pcode || code;
+ end if;
+ end loop;
+
+ return pcode || '00';
+ end;
+$$ language plpgsql;
+
+select fn_get_next_code(0, 1); select fn_get_next_code(0, 2);
+----------------------------------------------------------------------------------------------------- 12.璁剧疆琛ㄧ殑缂栫爜锛�1-鍗曚綅锛�2-鐩綍
+-- drop function fn_set_tab_codes(integer, integer);
+create or replace function fn_set_tab_codes(pid integer, genre integer)
+returns integer as $$
+ declare
+ cc integer;
+ rid integer;
+ code varchar;
+ tab varchar := 'dep';
+ begin
+ if (pid is null or genre is null) then
+ return 0;
+ end if;
+ if genre = 2 then
+ tab := 'dir';
+ end if;
+
+ execute 'select count(*) from lf.sys_' || tab || ' where pid = ' || pid into cc;
+ if cc = 0 then
+ return 0;
+ end if;
+
+ for rid in execute 'select id from lf.sys_' || tab || ' where code is null and pid = ' || pid || ' order by order_num' loop
+ code := fn_get_next_code(pid, genre);
+ execute 'update lf.sys_' || tab || ' set code = ''' || code || ''' where id = ' || rid;
+
+ cc := fn_set_tab_codes(rid, genre);
+ end loop;
+
+ return 1;
+ end;
+$$ language plpgsql;
+
+select fn_set_tab_codes(1, 1); select fn_set_tab_codes(0, 2);
+----------------------------------------------------------------------------------------------------- 13.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;
+
+ return new;
+ end;
+$$ language plpgsql;
+
+-- drop trigger fme_log_trigger on lf.sys_fme_log;
+create or replace trigger fn_fme_log_trigger after insert on lf.sys_fme_log
+for each row execute procedure fn_meta_insert();
+
+select * from lf.sys_meta;
+/*insert into lf.sys_fme_log (parentid,dirpath,pg_ns,tcmc,tcdm,count) values
+ ('8fb3c3dd-6a12-488d-80ae-a93cc7e8b2e3','','bs','涓嚎鎴愭灉琛�','m_pipelinepoint',0);
+select * from bs.m_pipelinepoint where parentid='8fb3c3dd-6a12-488d-80ae-a93cc7e8b2e3';*/
+----------------------------------------------------------------------------------------------------- 14.鍗曚綅琛ㄨЕ鍙戝櫒
+-- drop function fn_dep_insert();
+create or replace function fn_dep_insert() returns trigger as $$
+ begin
+ update lf.sys_dep set code = fn_get_next_code(new.pid, 1) where id = new.id;
+
+ return new;
+ end;
+$$ language plpgsql;
+
+-- drop trigger fn_dep_trigger on lf.sys_dep;
+create or replace trigger fn_dep_trigger after insert on lf.sys_dep
+for each row execute procedure fn_dep_insert();
+
+select * from lf.sys_dep where pid = 0 order by id;
+/*insert into lf.sys_dep (pid,name,level,order_num) values (0,'鏂板崟浣�',1,2);
+delete from lf.sys_dep where pid = 0 and id > 1;*/
+----------------------------------------------------------------------------------------------------- 15.鐩綍琛ㄨЕ鍙戝櫒
+
+
+----------------------------------------------------------------------------------------------------- 16.璺緞鍒嗘瀽
-- 鍒犻櫎宸插瓨鍦ㄧ殑鍑芥暟
drop function pgr_fromAtoB(varchar, double precision, double precision, double precision, double precision);
diff --git a/data/db_tab.sql b/data/db_tab.sql
index ba162b5..145a649 100644
--- a/data/db_tab.sql
+++ b/data/db_tab.sql
@@ -50,7 +50,7 @@
pid integer,
name varchar(150),
sname varchar(50),
- code varchar(50),
+ code varchar(24),
uncode varchar(50),
addr varchar(300),
contact varchar(50),
@@ -97,9 +97,11 @@
id serial primary key,
pid integer,
name varchar(150),
- code varchar(50),
+ exts varchar(200),
descr varchar(1024),
level integer,
+ checks varchar(100),
+ code varchar(24),
order_num integer,
create_user integer,
create_time timestamp(6) without time zone default now(),
@@ -114,6 +116,8 @@
comment on column lf.sys_dir.code is '鐩綍缂栫爜';
comment on column lf.sys_dir.descr is '鐩綍璇存槑';
comment on column lf.sys_dir.level is '灞傜骇锛�0-鏍硅妭鐐�';
+comment on column lf.sys_dir.checks is '妫�鏌ラ」';
+comment on column lf.sys_dir.code is '缂栫爜';
comment on column lf.sys_dir.order_num is '鎺掑簭鍙�';
comment on column lf.sys_dir.create_user is '鍒涘缓浜篒D';
comment on column lf.sys_dir.create_time is '鍒涘缓鏃堕棿';
@@ -540,7 +544,7 @@
--insert into lf.sys_role_menu_auth (roleid,menu_auth_id) values (1,1);
select * from lf.sys_role_menu_auth;
------------------------------------------------------------------------------------------------------ 16.鍏冩暟鎹〃
+----------------------------------------------------------------------------------------------------- 16.婧愭暟鎹〃
-- DROP TABLE IF EXISTS lf.sys_meta;
create table lf.sys_meta (
id serial primary key,
@@ -566,7 +570,7 @@
);
-- drop index index_sys_meta_geom;
create index index_sys_meta_geom on lf.sys_meta using GIST (geom);
-comment on table lf.sys_meta is '鍏冩暟鎹〃';
+comment on table lf.sys_meta is '婧愭暟鎹〃';
comment on column lf.sys_meta.id is '涓婚敭ID';
comment on column lf.sys_meta.eventid is 'GUID';
comment on column lf.sys_meta.metaid is '鐖跺厓鏁版嵁ID锛�0-娌℃湁';
diff --git a/data/ts.sql b/data/ts.sql
index b52d793..7f2221e 100644
--- a/data/ts.sql
+++ b/data/ts.sql
@@ -106,33 +106,8 @@
-- 瀵嗙爜锛歵est@12345_lf
select * from lf.sys_user where id in (47,48,49);
select * from lf.sys_download;
------------------------------------------------------------------------
+----------------------------------------------------------------------- 缁熻鎶ュ憡
-- alter sequence lf.sys_dict_id_seq restart with 11118;
-select * from bs.m_marker where parentid is not null; -- delete from bs.m_marker; createtime
-select * from bs.m_hydraulic_protection;
-
-insert into lf.sys_fme_log (pg_ns,tcdm,create_time) values ('bs','m_marker',20230206132933); delete lf.sys_fme_log where id = 99;
-select * from lf.sys_meta where type in ('xls', 'xlsx') and eventid is not null and tab is not null and rows > 0 and id in (1209,1180,1387);
-select * from lf.sys_attach order by id desc;
-select * from lf.sys_dir where id = 74;
-
-select * from lf.sys_dict where field='gid';
-select field,type,count(*) from lf.sys_dict where field='gid' group by field,type;
-update lf.sys_dict set type='integer' where field='updateuser';
-select * from lf.sys_layer;
-alter sequence lf.sys_layer_id_seq restart with 1;
-
-select * from lf.sys_menu where position('http:' in url)>0;
-select * from bd.b_hydrogeology_attach limit 1;
-select * from bd.b_pac_hydrogeology limit 1;
-
-select * from lf.sys_serve_log;
-select * from lf.sys_meta;
-select * from lf.sys_operate order by id desc limit 20;
-select * from lf.sys_report order by id;
-select * from lf.sys_attach order by id;
-select * from lf.sys_download order by id desc;
-
select fn_rec_query(depid, 'dep') "m1", count(sizes) "sizes"
from lf.sys_meta
group by depid
@@ -147,10 +122,18 @@
from lf.sys_operate
group by modular1,modular2
order by modular1 desc,modular2;
+-----------------------------------------------------------------------
+select * from lf.sys_menu where position('http:' in url)>0;
+select * from bs.m_marker where parentid is not null; -- delete from bs.m_marker; createtime
+select * from bs.m_hydraulic_protection;
-select * from lf.sys_operate where modular2 = '' or modular2 = '2' or modular2 is null;
-delete from lf.sys_operate where modular2 = '' or modular2 = '2' or modular2 is null;
-select * from lf.sys_report
+select * from lf.sys_layer where url is not null;
+
+
+
+
+
+
diff --git a/data/update.sql b/data/update.sql
index 4be2ac2..b6d2369 100644
--- a/data/update.sql
+++ b/data/update.sql
@@ -98,16 +98,32 @@
select fn_rec_query(a.dirid,'dir'),a.* from lf.sys_meta_new a where dirid = ANY(fn_rec_array((select id from rs), 'dir'));
select * from lf.sys_meta where substr(path,1,2) = '2\';
+--------------------------------------------------------- alter sequence lf.sys_meta_id_seq restart with 1;
+update lf.sys_operate set modular2='婧愭暟鎹鐞�' where modular2='鍏冩暟鎹�';
+
+delete from lf.sys_dep where id = 59;
+update lf.sys_dep set bak = null where bak = ' ';
+update lf.sys_dep set code = null;
+update lf.sys_dep set code = '00' where pid = 0;
+update lf.sys_dep set level = level + 1;
+alter table lf.sys_dep alter column code type varchar(24);
+
+select id,pid,name,code,level,order_num from lf.sys_dep where code is not null order by code;
---------------------------------------------------------
-select * from lf.sys_fme_log;
+select * from lf.sys_dir order by id;
+
+alter table lf.sys_dir alter column code type varchar(200);
+alter table lf.sys_dir rename column code to exts;
+alter table lf.sys_dir alter column bak type varchar(100);
+alter table lf.sys_dir rename column bak to checks;
+alter table lf.sys_dir add column bak varchar(1024);
+update lf.sys_dir set bak=checks where checks is not null and position('check' in checks)=0;
+update lf.sys_dir set checks=null where position('check' in checks)=0;
+alter table lf.sys_dir add column code varchar(24);
+
+select id,pid,name,code,level,order_num from lf.sys_dir where code is not null order by code;
+---------------------------------------------------------
select * from lf.sys_meta;
--- delete from lf.sys_meta where id> 31;
--- delete from lf.sys_meta;
--- alter sequence lf.sys_meta_id_seq restart with 1;
-
-
-
-
diff --git a/src/main/java/com/lf/server/controller/data/MetaController.java b/src/main/java/com/lf/server/controller/data/MetaController.java
index 56dab7e..0811560 100644
--- a/src/main/java/com/lf/server/controller/data/MetaController.java
+++ b/src/main/java/com/lf/server/controller/data/MetaController.java
@@ -41,7 +41,7 @@
* 鍏冩暟鎹�
* @author WWW
*/
-@Api(tags = "鏁版嵁绠＄悊\\鍏冩暟鎹�")
+@Api(tags = "鏁版嵁绠＄悊\\婧愭暟鎹鐞�")
@RestController
@RequestMapping("/meta")
public class MetaController extends BaseController {
diff --git a/src/main/java/com/lf/server/entity/data/DirEntity.java b/src/main/java/com/lf/server/entity/data/DirEntity.java
index 9d658ec..dae9fb7 100644
--- a/src/main/java/com/lf/server/entity/data/DirEntity.java
+++ b/src/main/java/com/lf/server/entity/data/DirEntity.java
@@ -2,20 +2,16 @@
import lombok.AllArgsConstructor;
import lombok.Data;
-import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.sql.Timestamp;
/**
- * sys_dir
- * @author sws
- * @date 2022-09-24
+ * 鐩綍
+ * @author WWW
*/
-
@Data
@AllArgsConstructor
-@NoArgsConstructor
public class DirEntity implements Serializable {
private static final long serialVersionUID = -2184993363389504088L;
@@ -25,7 +21,7 @@
private String name;
- private String code;
+ private String exts;
private String descr;
@@ -41,9 +37,16 @@
private Timestamp updateTime;
+ private String checks;
+
private String bak;
+ private String code;
+
private String fullName;
+
+ public DirEntity() {
+ }
public int getId() {
return id;
@@ -69,12 +72,12 @@
this.name = name;
}
- public String getCode() {
- return code;
+ public String getExts() {
+ return exts;
}
- public void setCode(String code) {
- this.code = code;
+ public void setExts(String exts) {
+ this.exts = exts;
}
public String getDescr() {
@@ -133,6 +136,14 @@
this.updateTime = updateTime;
}
+ public String getChecks() {
+ return checks;
+ }
+
+ public void setChecks(String checks) {
+ this.checks = checks;
+ }
+
public String getBak() {
return bak;
}
@@ -141,6 +152,14 @@
this.bak = bak;
}
+ public String getCode() {
+ return code;
+ }
+
+ public void setCode(String code) {
+ this.code = code;
+ }
+
public String getFullName() {
return fullName;
}
--
Gitblit v1.9.3