USP_CONSTITUENTUPDATEBATCH_ADJUSTCONSTITUENCYDATERANGE

CREATE procedure USP_CONSTITUENTUPDATEBATCH_ADJUSTCONSTITUENCYDATERANGE
(
    @BATCHCONSTITUENTUPDATEID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier,
    @CONSTITUENCIES xml,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CURRENTDATE datetime = getdate();

        --Defaulting  DATETO for constituencies

    declare @INPUTDATETO table
    (
            ID uniqueidentifier,
        CONSTITUENCYCODEID uniqueidentifier,
        STARTDATE date,
        ISSYSTEM bit
    );

    -- Populate temp table with selected data

    insert into @INPUTDATETO
    select
            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
        T.c.value('(CONSTITUENCYCODEID)[1]','uniqueidentifier') AS 'CONSTITUENCYCODEID',
        T.c.value('(DATEFROM)[1]','date') AS 'DATEFROM',
        T.c.value('(ISSYSTEM)[1]','bit') AS 'ISSYSTEM'
    from @CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') T(c)
    order by T.c.value('(DATEFROM)[1]','date');

    --Defaulting  DATETO for constituencies

    declare @INPUTDATEFROM table
    (
        ID uniqueidentifier,
        CONSTITUENCYCODEID uniqueidentifier,
        ENDDATE date,
        ISSYSTEM bit
    );

    -- Populate temp table with selected data

    insert into @INPUTDATEFROM
    select
            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
        T.c.value('(CONSTITUENCYCODEID)[1]','uniqueidentifier') AS 'CONSTITUENCYCODEID',
        T.c.value('(DATETO)[1]','date') AS 'DATETO',
        T.c.value('(ISSYSTEM)[1]','bit') AS 'ISSYSTEM'
    from @CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') T(c)
    order by T.c.value('(DATETO)[1]','date');

    begin try
        --Defaulting user defined DATETO

        with UPDATEROWS_USERDEFINDED_DATETO as
        (
            select
                ROW_NUMBER() over (partition by BATCHCONSTITUENTUPDATE.PRIMARYRECORDID, BATCHCONSTITUENTUPDATECONSTITUENCIES.CONSTITUENCYCODEID order by BATCHCONSTITUENTUPDATECONSTITUENCIES.DATEFROM desc) as ROWNUMBER,
                BATCHCONSTITUENTUPDATECONSTITUENCIES.ID as BATCHCONSTITUENTUPDATECONSTITUENCIESID,
                INPUT.STARTDATE
            from @INPUTDATETO INPUT
                inner join dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES on INPUT.CONSTITUENCYCODEID = BATCHCONSTITUENTUPDATECONSTITUENCIES.CONSTITUENCYCODEID
                inner join dbo.BATCHCONSTITUENTUPDATE on BATCHCONSTITUENTUPDATE.ID = BATCHCONSTITUENTUPDATECONSTITUENCIES.BATCHCONSTITUENTUPDATEID
            where
                BATCHCONSTITUENTUPDATE.PRIMARYRECORDID = @CONSTITUENTID
                and BATCHCONSTITUENTUPDATECONSTITUENCIES.DATEFROM < INPUT.STARTDATE
                and BATCHCONSTITUENTUPDATECONSTITUENCIES.DATETO is null
                and INPUT.ISSYSTEM = 0 and BATCHCONSTITUENTUPDATE.ID = @BATCHCONSTITUENTUPDATEID
        )
        update dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES
        set
            DATETO = dateadd(day, -1, UPDATEROWS_USERDEFINDED_DATETO.STARTDATE),
            DATECHANGED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID
        from UPDATEROWS_USERDEFINDED_DATETO
        where
            BATCHCONSTITUENTUPDATECONSTITUENCIES.ID = UPDATEROWS_USERDEFINDED_DATETO.BATCHCONSTITUENTUPDATECONSTITUENCIESID
            and UPDATEROWS_USERDEFINDED_DATETO.ROWNUMBER = 1;

            --Defaulting system defined DATETO

        with UPDATEROWS_SYSTEMDEFINDED_DATETO as
        (
            select
                ROW_NUMBER() over (partition by BATCHCONSTITUENTUPDATE.PRIMARYRECORDID, BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.CONSTITUENCYCODEID order by BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.DATEFROM desc) as ROWNUMBER,
                BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.ID as BATCHCONSTITUENTUPDATECONSTITUENCIESID,
                INPUT.STARTDATE
            from @INPUTDATETO INPUT
                inner join dbo.BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM on INPUT.CONSTITUENCYCODEID = BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.CONSTITUENCYCODEID
                inner join dbo.BATCHCONSTITUENTUPDATE on BATCHCONSTITUENTUPDATE.ID = BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.BATCHCONSTITUENTUPDATEID
            where
                BATCHCONSTITUENTUPDATE.PRIMARYRECORDID = @CONSTITUENTID
                and BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.DATEFROM < INPUT.STARTDATE
                and BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.DATETO is null
                and INPUT.ISSYSTEM = 1 and BATCHCONSTITUENTUPDATE.ID = @BATCHCONSTITUENTUPDATEID
        )
        update dbo.BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM
        set
            DATETO = dateadd(day, -1, UPDATEROWS_SYSTEMDEFINDED_DATETO.STARTDATE),
            DATECHANGED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID
        from UPDATEROWS_SYSTEMDEFINDED_DATETO
        where
            BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.ID = UPDATEROWS_SYSTEMDEFINDED_DATETO.BATCHCONSTITUENTUPDATECONSTITUENCIESID
            and UPDATEROWS_SYSTEMDEFINDED_DATETO.ROWNUMBER = 1;

        --Defaulting user defined - DATEFROM

        with UPDATEROWS_USERDEFINED_DATEFROM as
        (
            select
                ROW_NUMBER() over (partition by BATCHCONSTITUENTUPDATE.PRIMARYRECORDID, BATCHCONSTITUENTUPDATECONSTITUENCIES.CONSTITUENCYCODEID order by BATCHCONSTITUENTUPDATECONSTITUENCIES.DATEFROM desc) as ROWNUMBER,
                BATCHCONSTITUENTUPDATECONSTITUENCIES.ID as BATCHCONSTITUENTUPDATECONSTITUENCIESID,
                INPUT.ENDDATE
            from @INPUTDATEFROM INPUT
                inner join dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES on INPUT.CONSTITUENCYCODEID = BATCHCONSTITUENTUPDATECONSTITUENCIES.CONSTITUENCYCODEID
                inner join dbo.BATCHCONSTITUENTUPDATE on BATCHCONSTITUENTUPDATE.ID = BATCHCONSTITUENTUPDATECONSTITUENCIES.BATCHCONSTITUENTUPDATEID
            where
                BATCHCONSTITUENTUPDATE.PRIMARYRECORDID = @CONSTITUENTID
                and BATCHCONSTITUENTUPDATECONSTITUENCIES.DATETO > INPUT.ENDDATE
                and BATCHCONSTITUENTUPDATECONSTITUENCIES.DATEFROM is null
                and INPUT.ISSYSTEM = 0 and BATCHCONSTITUENTUPDATE.ID = @BATCHCONSTITUENTUPDATEID
        )
        update dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES
        set
            DATEFROM = dateadd(day, 1, UPDATEROWS_USERDEFINED_DATEFROM.ENDDATE),
            DATECHANGED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID
        from UPDATEROWS_USERDEFINED_DATEFROM
        where
            BATCHCONSTITUENTUPDATECONSTITUENCIES.ID = UPDATEROWS_USERDEFINED_DATEFROM.BATCHCONSTITUENTUPDATECONSTITUENCIESID
            and UPDATEROWS_USERDEFINED_DATEFROM.ROWNUMBER = 1;

        --Defaulting system defined - DATEFROM

        with UPDATEROWS_SYSTEMDEFINED_DATEFROM as
        (
            select
                ROW_NUMBER() over (partition by BATCHCONSTITUENTUPDATE.PRIMARYRECORDID, BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.CONSTITUENCYCODEID order by BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.DATEFROM desc) as ROWNUMBER,
                BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.ID as BATCHCONSTITUENTUPDATECONSTITUENCIESID,
                INPUT.ENDDATE
            from @INPUTDATEFROM INPUT
                inner join dbo.BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM on INPUT.CONSTITUENCYCODEID = BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.CONSTITUENCYCODEID
                inner join dbo.BATCHCONSTITUENTUPDATE on BATCHCONSTITUENTUPDATE.ID = BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.BATCHCONSTITUENTUPDATEID
            where
                BATCHCONSTITUENTUPDATE.PRIMARYRECORDID = @CONSTITUENTID
                and BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.DATETO > INPUT.ENDDATE
                and BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.DATEFROM is null
                and INPUT.ISSYSTEM = 1 and BATCHCONSTITUENTUPDATE.ID = @BATCHCONSTITUENTUPDATEID
        )
        update dbo.BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM
        set
            DATEFROM = dateadd(day, 1, UPDATEROWS_SYSTEMDEFINED_DATEFROM.ENDDATE),
            DATECHANGED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID
        from UPDATEROWS_SYSTEMDEFINED_DATEFROM
        where
            BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.ID = UPDATEROWS_SYSTEMDEFINED_DATEFROM.BATCHCONSTITUENTUPDATECONSTITUENCIESID
            and UPDATEROWS_SYSTEMDEFINED_DATEFROM.ROWNUMBER = 1;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end