set option ansi_update_constraints = Off

GO


if exists(select * from systable where table_name = 'FADbStatus') then 
    if exists (select * from "ADMIN"."FADbStatus" where major1 = 7 and major2 = 17 and minor1=1 and minor2=7) then
        RAISERROR 23000 'This FormAnalyzer database patch has allready been applied!'
    end if ;
end if;

-------------------------------------------------
--   Procedure
-------------------------------------------------

CREATE PROCEDURE "ADMIN"."FASplitBox"( in @srcBoxId integer, in @newBoxName char(64), in @splitPosition integer, out @newBoxId integer )
BEGIN
    // REMEMBER -> set option ansi_update_constraints = Off
    declare err_notfound exception for sqlstate value '02000';
    declare @boxid integer;
    declare @parent integer;
    declare @cfgid integer;
    declare @doctempl char(64);
    declare @status integer;
    declare @volid integer;
    declare @hold integer;
    declare @creator char(128);
    declare @created timestamp;
    declare @changesIntroduced integer;
    declare @docsCount integer;
    declare @newobid integer;
    
    select obid,parent,cfgid,doctempl,status,volid,hold,creator,created
        into @boxid,@parent,@cfgid,@doctempl,@status,@volid,@hold,@creator,@created
    from ADMIN.Box as B,ADMIN.Object as O
    where B.obid = @srcBoxId and O.id = @srcBoxId;

    if @boxid is null then
        RAISERROR 23000 'ERROR - Given box does not exist!' 
    end if;

    // check if there are any documents left for the split box to be created
    select count(*) into @docsCount from "ADMIN"."Document" where boxid = @boxid and number >= @splitPosition ;
    if @docsCount = 0 then
        RAISERROR 23001 'ERROR - The resulting split box will be empty!' 
    end if ;

    // create a new box
    insert into "ADMIN"."Object" (id,cfgid,status,error,doctempl) values( default,@cfgid,@status,0,@doctempl) ;
    set @newobid = @@identity;
    insert into "ADMIN"."Box" (obid,volid,parent,name,hold) values (@newobid,@volid,@parent,@newBoxName,0) ;
    
    // move documents to a new box
    update "ADMIN"."Document" set boxid = @newobid, number = number - @splitPosition + 1 where boxid = @boxid and number >= @splitPosition order by number;
    
    // return new box id
    set @newBoxId = @newobid;
END;

GO

GRANT EXECUTE ON "ADMIN"."FASplitBox" TO "ADMIN"
go

-------------------------------------------------
--   Create tables
-------------------------------------------------

CREATE TABLE "ADMIN"."FADbStatus" (
    "id"                             integer NOT NULL DEFAULT autoincrement
   ,"major1"                         integer NOT NULL
   ,"major2"                         integer NOT NULL
   ,"minor1"                         integer NOT NULL
   ,"minor2"                         integer NOT NULL
   ,"version"                        char(32) NOT NULL
   ,"note"                           char(255) NULL
   ,"creator"                        char(128) NOT NULL DEFAULT current user
   ,"created"                        timestamp NOT NULL DEFAULT current timestamp
   ,PRIMARY KEY ("id") 
) IN "CONFIG"
go

GRANT SELECT ON "ADMIN"."FADbStatus" TO "PUBLIC" 
go

GRANT SELECT,INSERT ON "ADMIN"."FADbStatus" TO "ADMIN" 
go

INSERT INTO "ADMIN"."FADbStatus"(id, major1, major2, minor1, minor2, "version", note) VALUES(default,7,17,1,7,'7.17.1.7', 'Split Box feature added to box review')
go

commit work
go
