----------------------------------------------------------------------- 数据检查
|
select count(*) from bd.dlg_25w_resa; -- 157217
|
select count(*) from bd.dlg_25w_boul; -- 7453
|
select count(*) from bd.dlg_agnp; -- 3248
|
|
select count(*) from bd.dlg_25w_hyda; -- 297884
|
select count(*) from bd.dlg_25w_hydl; -- 2212590
|
select count(*) from bd.dlg_25w_lrdl; -- 4690386
|
select count(*) from bd.dlg_25w_lrrl; -- 51338
|
select count(*) from bs.m_pipeline; -- 26
|
|
select * from bd.dlg_25w_resa order by gid limit 10;
|
select * from bd.dlg_25w_boul order by gid limit 10;
|
select * from bd.dlg_agnp order by gid limit 10;
|
----------------------------------------------------------------------- 设置数据单位
|
select * from lf.sys_dep order by id;
|
|
-- pid = 15, 生产业务部门
|
update bd.dlg_25w_resa set depid = 20 where gid % 7 = 0; -- 线路室
|
update bd.dlg_25w_resa set depid = 21 where gid % 7 = 1; -- 穿跨越室
|
--update bd.dlg_25w_resa set depid = 22 where gid % 7 = 2; -- 土建室
|
|
-- pid = 38, 勘察事业部
|
update bd.dlg_25w_resa set depid = 55 where gid % 7 = 3; -- 勘察室
|
--update bd.dlg_25w_resa set depid = 56 where gid % 7 = 4; -- 岩土设计室
|
--update bd.dlg_25w_resa set depid = 54 where gid % 7 = 5; -- 测绘室
|
|
update bd.dlg_25w_boul set depid = 20 where gid % 7 = 0; -- 线路室
|
update bd.dlg_25w_boul set depid = 21 where gid % 7 = 1; -- 穿跨越室
|
update bd.dlg_25w_boul set depid = 55 where gid % 7 = 3; -- 勘察室
|
|
update bd.dlg_agnp set depid = 20 where gid % 7 = 0; -- 线路室
|
update bd.dlg_agnp set depid = 21 where gid % 7 = 1; -- 穿跨越室
|
update bd.dlg_agnp set depid = 55 where gid % 7 = 3; -- 勘察室
|
|
update bd.dlg_25w_resa set depid = 22 where depid is null or depid = 0 or depid = 1; -- 土建室
|
update bd.dlg_25w_boul set depid = 22 where depid is null or depid = 0 or depid = 1; -- 土建室
|
update bd.dlg_agnp set depid = 22 where depid is null or depid = 0 or depid = 1; -- 土建室
|
|
update bd.dlg_agnp set depid = null where gid = 3247;
|
update bd.dlg_agnp set depid = 0 where gid = 3246;
|
|
select depid from bd.dlg_25w_resa group by depid order by depid;
|
select depid from bd.dlg_25w_boul group by depid order by depid;
|
select depid from bd.dlg_agnp group by depid order by depid;
|
----------------------------------------------------------------------- 设置用户
|
select * from lf.sys_user order by id;
|
|
update lf.sys_user set depid = 38 where uname = '管理员'; -- id = 1
|
update lf.sys_user set depid = 20 where uname = '线路室'; -- id = 2
|
update lf.sys_user set depid = 21 where uname = '穿跨越室'; -- id = 3
|
update lf.sys_user set depid = 55 where uname = '勘察室'; -- id = 5
|
----------------------------------------------------------------------- 设置角色
|
select * from lf.sys_role order by id;
|
|
delete from lf.sys_role where id between 2 and 11;
|
insert into lf.sys_role (depid,name,descr,is_admin,create_user) values (20,'线路室-数据管理员', '数据管理员',2,1);
|
insert into lf.sys_role (depid,name,descr,is_admin,create_user) values (21,'穿跨越室-数据管理员','数据管理员',2,1);
|
insert into lf.sys_role (depid,name,descr,is_admin,create_user) values (55,'勘察室-数据管理员', '数据管理员',2,1);
|
insert into lf.sys_role (depid,name,descr,is_admin,create_user) values (22,'土建室-数据管理员', '数据管理员',2,1);
|
|
----------------------------------------------------------------------- 设置用户角色
|
select * from lf.sys_role_user order by id;
|
|
-- delete from lf.sys_role_user where id > 1;
|
insert into lf.sys_role_user (id, roleid, userid, create_user) values (2, 12, 2, 1);
|
insert into lf.sys_role_user (id, roleid, userid, create_user) values (3, 13, 3, 1);
|
insert into lf.sys_role_user (id, roleid, userid, create_user) values (4, 14, 5, 1);
|
insert into lf.sys_role_user (id, roleid, userid, create_user) values (5, 15, 4, 1);
|
|
alter sequence lf.sys_apply_id_seq restart with 3;
|
alter sequence lf.sys_flow_id_seq restart with 7;
|
-----------------------------------------------------------------------
|
select depid from bd.dlg_25w_boul where depid > 0 and depid != ALL(fn_rec_array(38, 'dep'))
|
and ST_Intersects(geom, ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4490))
|
group by depid;
|
|
select c.* from lf.sys_role a inner join lf.sys_role_user b on a.id = b.roleid inner join lf.sys_user c on b.userid = c.id
|
where a.is_admin = 2 and a.depid = 55 order by c.id limit 1
|
|
select * from lf.sys_apply order by id;
|
select * from lf.sys_flow order by id;
|
|
update lf.sys_flow set status = 1 where id = 3;
|
update lf.sys_apply a set status = (select count(*) from lf.sys_flow b where b.status = 1 and b.applyid = a.id) where id = 1;
|
update lf.sys_apply set status = 10 where status = count and id = 1;
|
|
select * from lf.sys_user
|
select * from lf.sys_role_user
|
select * from lf.sys_role
|
|
select c.*, fn_rec_query(c.depid, 'dep') depName,fn_uname(c.create_user) createName,fn_uname(c.update_user) updateName
|
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid inner join lf.sys_role c on b.roleid = c.id
|
where a.id = 1;
|
|
select a.*, fn_rec_query(a.depid, 'dep') depName,fn_uname(a.create_user) createName,fn_uname(a.update_user) updateName
|
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid inner join lf.sys_role c on b.roleid = c.id
|
where c.id = 15
|
|
select *, st_astext(geom) as wkt from bs.m_pipesegment;
|
select * from bs.m_pipesegment;
|
select gid, pipename, segname from bs.m_pipesegment order by pipename, segname;
|
|
select * from bd.dlg_25w_hydl limit 10;
|
select * from bd.dlg_25w_lrdl limit 10;
|
select * from bd.dlg_25w_lrrl limit 10;
|
select * from bd.dlg_25w_hyda limit 10;
|
select * from bs.m_pipesegment limit 10;
|
|
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_hydl limit 1;
|
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_lrdl limit 1;
|
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_lrrl limit 1;
|
select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_hyda limit 1;
|
select ST_SRID(geom), ST_GeometryType(geom) from bs.m_pipesegment limit 1;
|
|
SELECT a.name as acrossName,
|
b.segname as segName,
|
b.remarks,
|
b.pipename as pipeName,
|
st_length( st_geographyfromtext( st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) AS acrossLength,
|
st_astext(b.geom) as wkt
|
FROM bd.dlg_25w_hyda AS a,
|
(SELECT *
|
FROM bs.m_pipesegment
|
WHERE segname = 'DD' ) AS b
|
WHERE ST_Intersects(a.geom, b.geom)
|
|
select row_number() over() as gid, a.name as acrossName, b.segname as segName, b.remarks, b.pipename as pipeName,
|
cast( st_length( st_geographyfromtext( st_astext( st_intersection(ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) as decimal(12, 3) ) AS acrossLength,
|
st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) as wkt
|
from bd.dlg_25w_hyda as a, (select segname, remarks, pipename, geom from bs.m_pipesegment where gid = 24) as b
|
where ST_Intersects(a.geom, b.geom)
|
|
select * from lf.sys_download where type = 5;
|
select * from bs.m_pipesegment;
|
select tab from lf.sys_dict where ns='md' group by tab order by tab;
|
|
select * from lf.sys_meta where id > 150;
|
select * from lf.sys_meta_file where metaid > 150;
|
select path from lf.sys_meta_file where guid = '8466c363eb269ed5b6664e66913aee71' limit 1
|
|
delete from lf.sys_meta where id > 150;
|
delete from lf.sys_meta_file where metaid > 150;
|
|
select * from lf.sys_dir;
|
select * from lf.sys_meta_new;
|
insert into lf.sys_meta_new (name,dirid) values ('项目WBS导出.xlsx',57);
|
insert into lf.sys_meta_new (name,dirid) values ('00116BT02-CR001#ESV-DW-0101 封面.docx',62);
|
insert into lf.sys_meta_new (name,dirid) values ('00116BT02-CR001#ESV-DW-0101（大河沿河穿越地形图）0.dwg',62);
|
insert into lf.sys_meta_new (name,dirid) values ('00116BT02-CR001#ESV-DW-0101（大河沿河穿越地形图）0.dwl',62);
|
insert into lf.sys_meta_new (name,dirid) values ('00116BT02-CR001#ESV-DW-0101（大河沿河穿越地形图）0.dwl2',62);
|
insert into lf.sys_meta_new (name,dirid) values ('00116BT02-CR001#ESV-DW-0101（大河沿河穿越纵断面图）0.dwg',62);
|
insert into lf.sys_meta_new (name,dirid) values ('00116BT02-CR001#ESV-IN-0101（资料图纸目录）.doc',62);
|
insert into lf.sys_meta_new (name,dirid) values ('1类.gdb.zip',74);
|
insert into lf.sys_meta_new (name,dirid) values ('2类.gdb.zip',74);
|
insert into lf.sys_meta_new (name,dirid) values ('易燃易爆.gdb.zip',74);
|
insert into lf.sys_meta_new (name,dirid) values ('地面线.xlsx',64);
|
insert into lf.sys_meta_new (name,dirid) values ('控制点.xlsx',64);
|
insert into lf.sys_meta_new (name,dirid) values ('离散点.xlsx',64);
|
insert into lf.sys_meta_new (name,dirid) values ('平面图索引文件.xlsx',64);
|
insert into lf.sys_meta_new (name,dirid) values ('中线成果表.xlsx',64);
|
|
update lf.sys_meta_new set depid=1,type='file',path='2\'||name,sizes=1;
|
|
with rs as (select id from lf.sys_dir where name='西气东输四线天然气管道工程（吐鲁番-中卫）（00116BT02）')
|
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'));
|
