USP_SPONSOR_CHECKANDMAKEINACTIVE

CREATE procedure dbo.USP_SPONSOR_CHECKANDMAKEINACTIVE
(
    @CONSTITUENTID uniqueidentifier,
    @ENDDATE date,
    @SPONSORTYPECODE tinyint,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null
)
as
begin
    set nocount on;

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @ACTIVECOUNT int;

    if @SPONSORTYPECODE in(0,1)
        select @ACTIVECOUNT =
          count(case @SPONSORTYPECODE
                  when 0 then case when SPONSORSHIP.CONSTITUENTID = REVENUE.CONSTITUENTID then 'x' end
                  when 1 then case when SPONSORSHIP.CONSTITUENTID <> REVENUE.CONSTITUENTID then 'x' end
                end)
        from dbo.SPONSORSHIP
        left join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
        left join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
        where SPONSORSHIP.CONSTITUENTID = @CONSTITUENTID
        and SPONSORSHIP.STATUSCODE = 1;
    else
        select @ACTIVECOUNT = count('x')
        from dbo.SPONSORSHIP
        inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
        inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
        where REVENUE.CONSTITUENTID = @CONSTITUENTID
        and SPONSORSHIP.CONSTITUENTID <> REVENUE.CONSTITUENTID
        and SPONSORSHIP.STATUSCODE = 1;


    if @ACTIVECOUNT = 0
        update dbo.SPONSORDATERANGE
        set DATETO = @ENDDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where CONSTITUENTID = @CONSTITUENTID
        and SPONSORTYPECODE = @SPONSORTYPECODE
        and DATETO is null;
end