Opdatering af jar_hent_opdater_matrikel_status_sp - beregning af matriklens statusdato

[sync-by-unito[bot]]

Med reference til JAR-993, så har vi på JAR workshop den 27-09-2017 besluttet at oprette et særskilt issue til implementering af en opdateret version af sp, som Region Sjælland allerede har testet og implementeret.

Se vedhæftede script:

SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; SET ANSI_PADDING ON; SET NOCOUNT ON;
GO

DECLARE @svn_revision   varchar(15) = '$Rev: 6138 $'
DECLARE @db_version     varchar(15) = '2.6.5'
DECLARE @pakke_version  varchar(15) = '-'
DECLARE @scriptnavn     varchar(60) = '21 Rettet version af jar_hent_opdater_matrikel_status_sp.sql'
DECLARE @beskrivelse    varchar(250)= 'Retter jar_hent_opdater_matrikel_status_sp'

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[script_log_gm]') AND type in (N'U'))
BEGIN
    INSERT INTO script_log_gm (db_version,pakke_version,dato,scriptnavn,beskrivelse,svn_revision)
    VALUES  (@db_version,@pakke_version,getdate(),@scriptnavn,@beskrivelse,@svn_revision)
END
go
-----------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jar_hent_opdater_matrikel_status_sp]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[jar_hent_opdater_matrikel_status_sp]
GO
-- ==========================================================================
-- Author                 :        JAR
-- Updated by             :        Rune Andersen, Sweco
-- Create date            :        -
-- Updated date           :        2017-01-18 RUA
-- Description            :        Proceduren beregner og evt. opdaterer matrikelstatus og statusdato
--                                 
-- Latest change          :        Rettet den dato fra sagsforløb, der benyttes til at sortere og finde det ældste.
--                                 Hvis matrikelstatus er "V1+V2", benyttes nu også sagstrin fra lokaliteter med status V1 og med status V2
--                                 Tidligere blev udelukende benyttet data fra lokaliteter med status "V1+V2"
--                                 
--
-- Release number          :       Rettelse efter ver 2.6.5. Lavet if. konvertering for Reg.Sjælland
-- Patch number           :        -
-- ==========================================================================

CREATE PROCEDURE [dbo].[jar_hent_opdater_matrikel_status_sp]
    @matrikel_id UNIQUEIDENTIFIER='00000000-0000-0000-0000-000000000000', 
    @kunGokend INT, 
    @doUpdate INT, 
    @eksklusivlokalitet_id UNIQUEIDENTIFIER='00000000-0000-0000-0000-000000000000', 
    @StatusId VARCHAR (36) OUTPUT
AS

BEGIN

    declare @MatrikelStatusId uniqueidentifier
    declare @MatrikelStatusKode varchar(10)
    declare @kortlaegnings_dato datetime
    declare @OldMatrikelStatusId uniqueidentifier --, @afgoerelses_dato datetime,@dato datetime

    set @MatrikelStatusId = '00000000-0000-0000-0000-000000000000'
    set @MatrikelStatusKode = '';

    -------------------------------------------------------------------
    -- opbyg temp tabel med lokaliseringsflader for matriklen
    select * into #temtable 
    from jar_matrikel_fladetyper_all_view 
    where matrikel_id = @matrikel_id

    if(@kunGokend=1)
    begin
        delete from #temtable where version=1 -- kladder
    end 

    if(@kunGokend=0)
    begin
        select * into #temtable2 from jar_matrikel_fladetyper_all_view where matrikel_id = @matrikel_id

        -- slet godkendte, såfremt der er en kladde
        delete from #temtable  
        where version=0 -- godkendt
        and forurening_id in (
            select t2.forurening_id 
            from #temtable2 t2
            where t2.[version]=1 -- kladder
            and #temtable.forurening_id=t2.forurening_id 
        )
    end 

    -- fjern rækker for en bestemt lokalitet    
    if(@eksklusivlokalitet_id <> '00000000-0000-0000-0000-000000000000' )
    begin
        delete from #temtable where  #temtable.forurening_id=@eksklusivlokalitet_id
    end 

    -------------------------------------------------------------------
    -- bestem ny matrikelstatus ud fra lokaliseringsflader for matriklen

    if ('0001.002' in (select kode from #temtable where matrikel_id = @matrikel_id)) and    -- V1
       ( exists (select 1 from #temtable where matrikel_id = @matrikel_id and kode in ('0001.003', '0001.006', '0001.007', '0001.008')))        -- V2
    begin
        select @MatrikelStatusId = pkid, @MatrikelStatusKode=kode from e_liste where kode_type = 'lokalitetsstatus' and kode = '0999.500'   -- V1 + v2
    end
    else
    begin
        if (exists (select 1 from #temtable where matrikel_id = @matrikel_id and kode in ('0001.003', '0001.006', '0001.007', '0001.008'))) -- V2
        begin
            select @MatrikelStatusId = pkid, @MatrikelStatusKode=kode from e_liste where kode_type = 'lokalitetsstatus' and kode = '0999.200'   -- V2
        end
        else
        begin
            if ('0001.002' in (select kode from #temtable where matrikel_id = @matrikel_id))    -- V1
            begin
                select @MatrikelStatusId = pkid, @MatrikelStatusKode=kode  from e_liste where kode_type = 'lokalitetsstatus' and kode = '0999.100'  -- V1
            end
            else
            begin
                if ('0001.001' in (select kode from #temtable where matrikel_id = @matrikel_id))    -- V0
                begin
                    select @MatrikelStatusId = pkid, @MatrikelStatusKode=kode  from e_liste where kode_type = 'lokalitetsstatus' and kode = '0999.000'  -- V0
                end
                else
                begin
                    if ('0001.004' in (select kode from #temtable where matrikel_id = @matrikel_id))    -- Udgået efter kortlægning
                    begin
                        select @MatrikelStatusId = pkid, @MatrikelStatusKode=kode  from e_liste where kode_type = 'lokalitetsstatus' and kode = '0999.400'  -- Udgået efter kortlægning
                    end
                    else
                    begin
                        if ('0001.005' in (select kode from #temtable where matrikel_id = @matrikel_id))    -- Udgået før kortlægning
                        begin
                            select @MatrikelStatusId = pkid, @MatrikelStatusKode=kode  from e_liste where kode_type = 'lokalitetsstatus' and kode = '0999.300'  -- Udgået før kortlægning
                        end

                    end
                end
            end
        end
    end

    if(@MatrikelStatusId <> '00000000-0000-0000-0000-000000000000' and @doUpdate=1)
    begin
        -------------------------------------------------------------------
        -- Bestem kortlægningsdato for matriklen

        select   top 1 
            @kortlaegnings_dato = isnull(es.afgoerelses_dato,es.dato) 
        from e_sagsforloeb es
        inner join r_sagsforloeb_liste rsl on es.pkid = rsl.sagsforloeb_id
        inner join e_liste el on el.pkid = rsl.kode_id and el.kode_type = 'Afgoerelses_Type' and el.kode in ('0001.000','0010.000') -- Vurdering af kortlægningsstatus, Kortlægningsnuancering

        inner join r_sagsforloeb_liste rsl2 on es.pkid = rsl2.sagsforloeb_id
        inner join e_liste el2 on el2.pkid = rsl2.kode_id  and el2.kode_type = 'Sagsforlob_og_status' 
        -- RUA: NB det ser ikke ud til at el2 benyttes.
        -- den bør muligvis nok benyttes til at filtrere de sagstrin fra, der ikke er relevante .. Her er der plads til rafinementer, hvis ønsket. :-)

        where 
            es.forurening_id in (
                    select es2.forurening_id
                    from e_sagsforloeb  es2
                    inner join r_forurening_matrikel rml on  es2.forurening_id = rml.forurening_id
                    inner join r_forurening_liste r on r.forurening_id = rml.forurening_id
                    inner join e_liste el3 on el3.pkid=r.kode_id
                    where  
                    (
                        (r.kode_id=@MatrikelStatusId) 
                        OR  (    @MatrikelStatusKode='0999.500' -- v1+v2
                            AND el3.kode in ('0999.100','0999.200','0999.500') -- v1, v2, v1+v2
                        )
                    )
                    and rml.matrikel_id=@matrikel_id)
        and es.flade_id in (
                    select gis_ref_nr 
                    from e_gis where 
                    kode collate Danish_Norwegian_CI_AS in (
                        select fladekode from [dbo].[FUNC_HentFladetyperFraLokalitetsStatusId](@MatrikelStatusId) 
                        )
                    )
        order by isnull(es.afgoerelses_dato,es.dato);

        -------------------------------------------------------------------
        -- bestem old-matrikelStatusId 
        select @OldMatrikelStatusId = null 
        select @OldMatrikelStatusId =  r.kode_id 
        from r_matrikel_liste r 
        inner join e_liste e on e.pkid=r.kode_id and e.kode_type='LokalitetsStatus'
        where matrikel_id = @matrikel_id  

        print 'Statusdato: ' + ISNULL(CONVERT(varchar(20),@kortlaegnings_dato,105),'NULL')+' Matrikelstatus: '+ ISNULL(@MatrikelStatusKode,'NULL')

        if(@kortlaegnings_dato is not null)
        begin
            if (@OldMatrikelStatusId is not null )
            begin
                print 'Update'
                update r_matrikel_liste 
                set kode_id = @MatrikelStatusId,
                    kortlaegnings_dato =@kortlaegnings_dato
                from r_matrikel_liste r
                inner join e_liste e on e.pkid=r.kode_id and e.kode_type='LokalitetsStatus'
                where matrikel_id = @matrikel_id 
            end
            else
            begin
                print 'Add new'
                insert into r_matrikel_liste (matrikel_id, kode_id, kortlaegnings_dato)
                values (@matrikel_id, @MatrikelStatusId, @kortlaegnings_dato)
            end
        end
    end

    set  @StatusId = @MatrikelStatusId
END

GO

Udfordringen med beregning af matriklen statusdato startede i sin tid i Region Sjælland regi. Jeg har derfor vedhæftede indholdet i dette issue (JARRSJ-190.doc)

Det er Rune fra Sweco der har opdateret sp og essens i opdateringen er:

Rettet den dato fra sagsforløb, der benyttes til at sortere og finde det ældste. Hvis matrikelstatus er "V1+V2", benyttes nu også sagstrin fra lokaliteter med status V1 og med status V2 Tidligere blev udelukende benyttet data fra lokaliteter med status "V1+V2.

┆Attachments: JARRSJ-190.doc

[sync-by-unito[bot]]

➤ Filip Bruman commented:

Vi har behov for at kunne genskabe et forløb hvor vi kan sammenligne effekten af de to versioner af SP jar_hent_opdater_matrikel_status_sp. Vi beder derfor om en beskrivelse af et forløb hvor matrikelstatusdato beregnes forkert.

[sync-by-unito[bot]]

➤ Peter Riis commented:

Lone, vil du gå i ind i Master JAR og oprette en lokalitet der illustrer problemstillingen med beregning af matrikelstatus? Her skal beskrives hvad den forventede dato er og hvad datoen så rent faktisk er i Master JAR. Ved opdatering af sp så vil vi se at datoen bliver som forventet.

Du må meget gerne gøre det i dag.

[sync-by-unito[bot]]

➤ Lone Oster commented:

Filip, jeg har forsøgt at lave et forløb, som skulle vise en forkert beregnet matrikelstatusdato, men jeg har ikke haft det store held, da jeg ikke kan se en forskel i Master JAR , og i vores driftsmiljø.

Jeg må derfor sætte dette issue på standby, til jeg har fået opklaret, hvorfor vi ikke får de ønskede matrikelstatusdatoer i vores driftsmiljø. /Lone

[sync-by-unito[bot]]

➤ Peter Riis commented:

Ifm. JAR-1216 er Stored Procedure jar_hent_opdater_matrikel_status_sp blevet opdateret. Cowi har dog ikke forholdt sig specifikt til JAR 1088, men det vil være fint at teste den nye version af jar_hent_opdater_matrikel_status_sp.

Lone Oster vil du teste i applikationen og komme med input til issuet?

[sync-by-unito[bot]]

➤ Peter Riis commented:

Lone Oster , vil du undersøge om dette issue kan lukkes?