Скрипт

Скрипт

stas

declare

-- Данные для теста функции

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;

  

Report Page