Скрипт
stasdeclare
-- Данные для теста функции
in_targetrouteid uuid := 'd2ed598a-5200-47a9-890b-fab59e42d94f';
in_baserouteid uuid := '566cf3d7-d055-42a8-9577-d2c14b920627';
in_tenantid uuid := 'c36a0157-fc27-411c-b0ca-20c3088ac270';
in_userid uuid := '4d8b9a38-ac60-4489-a2fc-5463b9e46c97';
in_alterdesc character varying := 'Тест из БД';
in_datetime timestamp without time zone := cast(now() as timestamp without time zone);
in_type bigint := 'bd7caf5d-f1b9-1a06-e040-007f020048f2';
----------------------------------------------
v_auditID uuid := uuid_generate_v4();
v_date date := date_trunc('day', in_dateTime);
v_newrouteid uuid := uuid_generate_v4();
f record;
begin
/*in_type - тип копирования:
1 Копируем:
-маршрут
-нулевые трассы
-точки маршрута
-направления следования
2 Копируем:
-маршрут
*/
create temporary table if not exists tmp_copyIDs(oldID uuid, newID uuid, className text);
delete from tmp_copyIDs;
--===========================================================================
--Устанавливаем для маршрута базовую версию
for f in (select R.routename, R.routecode, R.routeTypeID, R.vehicleTypeID, R.startDt, R.streets, R.traffickindID, R.landingOrderID, R.routeSpec, R.trackSpec, R.verID,
R.verStatus, R.version, R.length, R.displayOnWeb, R.controlSchedule, R.baserouteid, R.alterdesc, R.alternumb
from vcRoutes R
where R.ID = in_targetrouteid)
loop
insert into vcRoutes (id, routename, routecode, routeTypeID, vehicleTypeID, startDt, streets, traffickindID, landingOrderID, routeSpec, trackSpec, verID, verStatus, version, length, displayOnWeb, controlSchedule, baserouteid, alterdesc, alternumb)
select v_newrouteid, f.routename, f.routecode, f.routeTypeID, f.vehicleTypeID, f.startDt, f.streets, f.traffickindID, f.landingOrderID, f.routeSpec, f.trackSpec, f.verID, f.verStatus, f.version, f.length, f.displayOnWeb, f.controlSchedule, in_baserouteid, in_alterdesc,
(select max(RR.alternumb) + 1 from vcRoutes RR where RR.baserouteid = in_baserouteid);
insert into sscAttributes (id, creationDate, collectionID, userID, tenantID)
select v_newrouteid, now(), 'feb1b1ec-48b9-46d4-b59e-93d9ff3f1f09', in_userID, in_tenantID;
--Пишем аудит по маршруту
v_auditID := uuid_generate_v4();
insert into sscobjectsaudit (creationdate, collectionid, objectid, action, userid, tenantid, id)
select now(), 'feb1b1ec-48b9-46d4-b59e-93d9ff3f1f09', v_newrouteid, 'CREATE', in_userID, in_tenantID, v_auditID;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, 'e3241d19-460b-4506-9c54-5a489cfabf85', v_newrouteid;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, 'f6bdb839-ee26-4727-acd7-ff1c990db9e8', f.routename;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '08dc4314-1755-4006-aebc-e88e719f29f6', f.routecode;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '04724578-7a29-44d1-99b8-803d908a345d', f.routeTypeID;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '87617586-f56c-45ec-93b7-5c89f9c4ac62', f.vehicleTypeID;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '56c09c46-0ba6-4680-8a20-6d946c07aa71', f.startDt;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '86a924e6-87ef-4d75-b72d-d2d90383db70', f.streets;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '8c33376d-1dfc-42ed-a2b9-b85efbe77200', f.traffickindID;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '5fac854e-deda-46cd-abb0-d075f30c1e32', f.landingOrderID;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '73b7788c-8e9e-45db-96e2-30ed53835eb6', f.routeSpec;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '25e98f64-d098-478f-ae8d-8be45f160d7c', f.trackSpec;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '56c63ee2-e7a1-4a7f-aacc-6d1abe8a8951', f.verID;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '2e1f97e9-50bc-43ce-b7bb-0aa1acb2722d', f.verStatus;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '4b9e0fd3-be49-4b8a-9bea-080840970401', f.version;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, 'bb395ea8-306c-4536-9f64-133e82014bc7', f.length;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '51f8d2e6-7a92-4a04-92c2-ca86dfcf8736', f.displayOnWeb;
insert into sscobjectsauditdata (id, auditID, fieldID, newValue)
select uuid_generate_v4(), v_auditID, '8cad8aba-2cdb-41e9-ac48-ec13a175d38a', f.controlSchedule;
end loop;
--===========================================================================
--Получаем идентификаторы доступных объектов для копирования
--===========================================================================
--===========================================================================
--Получение данных маршрута
if (in_type = 1)
then
insert into tmp_copyIDs (oldID, newID, className)
--Получаем точки маршрута
select P.ID, uuid_generate_v4(), 'vcRoutePoints'
from vcRoutePoints P
where P.route = in_targetrouteid
union
--Получаем направления следования
select P.ID, uuid_generate_v4(), 'vcRouteDirection'
from vcRouteDirection P
where P.routeID = in_targetrouteid
and v_date between P.startDt and coalesce(P.endDt, v_date)
union
--Получаем нулевые трассы
select P.ID, uuid_generate_v4(), 'vcZeroTracks'
from vcZeroTracks P
where P.routeID = in_targetrouteid;
--===========================================================================
--Копирование данных
--===========================================================================
--Копируем Нулевые трассы
insert into vcZeroTracks (id, startDt, name, routeID)
select I.newID, v_date, name, v_newrouteid
from vcZeroTracks R, tmp_copyIDs I
where R.ID = I.oldID
and I.className = 'vcZeroTracks';
insert into sscAttributes (id, creationDate, collectionID, userID, tenantID)
select I.newID, now(), '191f1d08-5740-4249-a5af-8c3360c76dbd', in_userID, in_tenantID
from tmp_copyIDs I
where I.className = 'vcZeroTracks';
--Копируем точки маршрута
insert into vcRoutePoints (id, route, lat, lon, type, pointNumber, location, Name, radius, isEnding, Distance, objectID, zero, zeroid, brdStrategy, limitSpeed, checkPoint)
select I.newID, v_newrouteid, lat, lon, R.type, pointNumber, location, Name, radius, isEnding, Distance, objectID, zero,
(select G.newID from tmp_copyIDs G where G.className = 'vcZeroTracks' and G.oldID = R.zeroid),
brdStrategy, limitSpeed, checkPoint
from vcRoutePoints R, tmp_copyIDs I
where R.ID = I.oldID
and I.className = 'vcRoutePoints';
insert into sscAttributes (id, creationDate, collectionID, userID, tenantID)
select I.newID, now(), 'fee7df77-4686-4f69-94af-7a22cfa95cca', in_userID, in_tenantID
from tmp_copyIDs I
where I.className = 'vcRoutePoints';
--Апдейтим точки входа/выхода на нулевую трассу
update vcZeroTracks
set enterID = (select G.newID from tmp_copyIDs G where G.className = 'vcRoutePoints' and G.oldID = R.enterID),
exitID = (select G.newID from tmp_copyIDs G where G.className = 'vcRoutePoints' and G.oldID = R.exitID)
from vcZeroTracks R, tmp_copyIDs I
where exists (select 1 from tmp_copyIDs I
where R.ID = I.oldID
and I.className = 'vcZeroTracks');
--Копируем направления следования
insert into vcRouteDirection (id, routeID, directionTypeID, startDt)
select I.newID, v_newrouteid, directionTypeID, v_date
from vcRouteDirection R, tmp_copyIDs I
where R.ID = I.oldID
and I.className = 'vcRouteDirection';
insert into sscAttributes (id, creationDate, collectionID, userID, tenantID)
select I.newID, now(), 'a02ace98-7dd5-4db9-b5a2-6af1b5eea83a', in_userID, in_tenantID
from tmp_copyIDs I
where I.className = 'vcRouteDirection';
--Копируем объекты трассы
insert into vcrouteobjects (id, "object", route)
select I.newID, r."object", v_newrouteid
from vcrouteobjects r, tmp_copyIDs I
where R.ID = I.oldID
and I.className = 'vcRouteObjects';
insert into sscAttributes (id, creationDate, collectionID, userID, tenantID)
select I.newID, now(), 'd25aa829-e3ca-45a9-b6ee-640bcf0f1a58', in_userID, in_tenantID
from tmp_copyIDs I
where I.className = 'vcRouteObjects';
end if;
drop table if exists tmp_copyIDs;
return query select v_newrouteid::character varying newVersionID;/*
exception when others then
return false;*/
end;