USP_DATAFORMTEMPLATE_EDIT_PROSPECTRESEARCHREQUESTCONSTITUENTCOMPLETE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PROSPECTRESEARCHREQUESTCONSTITUENTCOMPLETE (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @TIMESPENT decimal(18, 2),
    @SOURCES xml,
    @CURRENTAPPUSERID uniqueidentifier
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try

        declare @CONSTITUENTID uniqueidentifier
        select @CONSTITUENTID = CONSTITUENTID from dbo.APPUSER where ID = @CURRENTAPPUSERID

        if coalesce((select ASSIGNEDTOID from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT where ID = @ID), '00000000-0000-0000-0000-000000000000') <> @CONSTITUENTID begin
            raiserror('BBERR_PROSPECTRESEARCHREQUESTCONSTITUENT_NOTASSIGNEE', 13, 1);
            return 1;
        end
        if (select STATUSCODE from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT where ID = @ID) <> 3 begin
            raiserror('BBERR_PROSPECTRESEARCHREQUESTCONSTITUENT_BADCOMPLETESTATE', 13, 1);
            return 1;
        end

        -- handle updating the data

        update 
            dbo.PROSPECTRESEARCHREQUESTCONSTITUENT 
        set
            TIMESPENT = @TIMESPENT,
            STATUSCODE = 4,
            DATECOMPLETED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID

        update PRRS
        set
            PRRS.TIME = coalesce(PRRCS.TIME, -1),
            PRRS.USES = coalesce(PRRCS.USES, -1),
            PRRS.COST = coalesce(PRRCS.COST, -1),
            PRRS.CHANGEDBYID = @CHANGEAGENTID,
            PRRS.DATECHANGED = @CURRENTDATE
        from
            dbo.UFN_PROSPECTRESEARCHREQUESTCONSTITUENT_GETSOURCES_FROMITEMLISTXML(@SOURCES) PRRCS
        join
            dbo.PROSPECTRESEARCHREQUESTSOURCE PRRS on PRRS.PROSPECTRESEARCHREQUESTCONSTITUENTID = @ID and PRRS.PROSPECTRESEARCHREQUESTSOURCECODEID = PRRCS.PROSPECTRESEARCHREQUESTSOURCECODEID

        insert into dbo.PROSPECTRESEARCHREQUESTSOURCE
        (
            PROSPECTRESEARCHREQUESTCONSTITUENTID,
            PROSPECTRESEARCHREQUESTSOURCECODEID,
            TIME, USES, COST,
            CHANGEDBYID, ADDEDBYID, DATECHANGED, DATEADDED
        )
        select 
            @ID,
            PRRCS.PROSPECTRESEARCHREQUESTSOURCECODEID,
            coalesce(PRRCS.TIME, -1),
            coalesce(PRRCS.USES, -1),
            coalesce(PRRCS.COST, -1),
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from 
            dbo.UFN_PROSPECTRESEARCHREQUESTCONSTITUENT_GETSOURCES_FROMITEMLISTXML(@SOURCES) PRRCS
        where
            not exists(select ID from PROSPECTRESEARCHREQUESTSOURCE where PROSPECTRESEARCHREQUESTSOURCECODEID = PRRCS.PROSPECTRESEARCHREQUESTSOURCECODEID and PROSPECTRESEARCHREQUESTCONSTITUENTID = @ID)

        declare @PROSPECTRESEARCHREQUESTID uniqueidentifier
        select @PROSPECTRESEARCHREQUESTID = PROSPECTRESEARCHREQUESTID from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT where ID = @ID

        --set parent request to complete if this is the last child completed

        update
            dbo.PROSPECTRESEARCHREQUEST
        set
            STATUSCODE = 4,
            ASSIGNEDTOID = coalesce(ASSIGNEDTOID, @CONSTITUENTID), --JamesWill WI135116 2011-05-31 If the newly closed request doesn't have a researcher, set the researcher to the closer

            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @PROSPECTRESEARCHREQUESTID
            and not exists(select 1 from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT where PROSPECTRESEARCHREQUESTID = @PROSPECTRESEARCHREQUESTID and STATUSCODE in (0, 1, 2, 3, 5))

        exec dbo.USP_PROSPECTRESEARCHREQUESTCONSTITUENT_COMPLETEDALERT_SEND @CURRENTAPPUSERID, @ID;

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

return 0;