USP_DATAFORMTEMPLATE_EDITSAVE_SPONSORSHIPOPPORTUNITYCHILD

The save procedure used by the edit dataform template "Sponsorship Opportunity Child Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SPONSORSHIPOPPORTUNITYGROUPID	uniqueidentifier	IN	Child group
@SPONSORSHIPLOCATIONID	uniqueidentifier	IN	Location
@FIRSTNAME	nvarchar(50)	IN	First name
@MIDDLENAME	nvarchar(50)	IN	Middle name
@LASTNAME	nvarchar(100)	IN	Last name
@GENDERCODE	int	IN	Gender
@BIRTHDATE	UDT_FUZZYDATE	IN	Birth date
@PICTURE	varbinary	IN	Image
@PICTURETHUMBNAIL	varbinary	IN	Image thumbnail
@PICTURECHANGED	bit	IN	Picture changed
@ISHIVPOSITIVE	bit	IN	HIV positive
@ISORPHANED	bit	IN	Orphaned
@SPROPPCHILDCONDITIONCODEID	uniqueidentifier	IN	Disability/Illness
@VALIDATEBIRTHDATE	bit	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_SPONSORSHIPOPPORTUNITYCHILD
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier,
    @SPONSORSHIPLOCATIONID uniqueidentifier,
    @FIRSTNAME nvarchar(50),
    @MIDDLENAME nvarchar(50),
    @LASTNAME nvarchar(100),
    @GENDERCODE int,
    @BIRTHDATE dbo.UDT_FUZZYDATE,
    --@TERMINATIONREASON nvarchar(100),

    @PICTURE varbinary(max),
    @PICTURETHUMBNAIL varbinary(max),
    @PICTURECHANGED bit,
    @ISHIVPOSITIVE bit,
    @ISORPHANED bit,
    @SPROPPCHILDCONDITIONCODEID uniqueidentifier,
    @VALIDATEBIRTHDATE bit
)
as

set nocount on;

if @ID is null
    set @ID = newid()

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

if dbo.UFN_AGEFROMFUZZYDATE(@BIRTHDATE,getdate()) < 0
begin
    raiserror('BBERR_BIRTHDATEINFUTURE',13,1)
    return 1
end
if @VALIDATEBIRTHDATE = 1
begin
    if dbo.UFN_SPONSORSHIPOPPORTUNITY_AGEVALID(@SPONSORSHIPOPPORTUNITYGROUPID,@BIRTHDATE) = 2
    begin
        raiserror('BBERR_CHILDOPPORTUNITYTOOOLD',13,1)
        return 1
    end
end

declare @AVAILABILITYCODE tinyint

select @AVAILABILITYCODE = case STATUSCODE when 0 then 0 else 2 end
from dbo.SPONSORSHIPLOCATION
where ID = @SPONSORSHIPLOCATIONID

declare @RESERVATIONKEYID uniqueidentifier;

select @RESERVATIONKEYID = RESERVATIONKEYID
from dbo.SPONSORSHIPOPPORTUNITY
where ID = @ID;

declare @CONSTITUENTID uniqueidentifier;
select @CONSTITUENTID = CONSTITUENTID
from dbo.SPONSORSHIPOPPORTUNITYCHILD
where ID = @ID;

--validate location change

declare @OLDSPONSORSHIPLOCATIONID uniqueidentifier;
select @OLDSPONSORSHIPLOCATIONID = SPONSORSHIPLOCATIONID
from dbo.SPONSORSHIPOPPORTUNITY 
where ID = @ID;

if @OLDSPONSORSHIPLOCATIONID <> @SPONSORSHIPLOCATIONID
  begin
    declare @SPONSORSPEROPPORTUNITY int;
    select @SPONSORSPEROPPORTUNITY = dbo.UFN_SPONSORSHIPOPPORTUNITY_SPONSORSPEROPPORTUNITY(@SPONSORSHIPOPPORTUNITYGROUPID, @SPONSORSHIPLOCATIONID);


    if @SPONSORSPEROPPORTUNITY > 0 and dbo.UFN_SPONSORSHIPOPPORTUNITY_ACTIVESPONSORSHIPS(@ID) > @SPONSORSPEROPPORTUNITY
      raiserror('BBERR_INVALIDLOCATIONCHANGE',13,1)
  end

begin try 

  -- handle updating the data

      update
        dbo.SPONSORSHIPOPPORTUNITY
  set 
        SPONSORSHIPOPPORTUNITYGROUPID = @SPONSORSHIPOPPORTUNITYGROUPID,
        SPONSORSHIPLOCATIONID = @SPONSORSHIPLOCATIONID,
        AVAILABILITYCODE = case when @AVAILABILITYCODE = 2 then 2 when AVAILABILITYCODE = 1 then 1 else dbo.UFN_SPONSORSHIPOPPORTUNITY_CALCAVAILABILITY(ID) end,
        RESERVATIONKEYID = case when @AVAILABILITYCODE = 2 then null else RESERVATIONKEYID end,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
  where 
        ID = @ID

    if @RESERVATIONKEYID is not null and @AVAILABILITYCODE = 2
        exec dbo.USP_SPONSORSHIPOPPORTUNITYRESERVE_UPDATESTATUS @RESERVATIONKEYID, @CHANGEAGENTID

  if @PICTURECHANGED = 1
  begin
      update
            dbo.SPONSORSHIPOPPORTUNITYCHILD
      set 
            SPROPPCHILDCONDITIONCODEID = @SPROPPCHILDCONDITIONCODEID,
            FIRSTNAME = @FIRSTNAME,
            MIDDLENAME = @MIDDLENAME,
            LASTNAME = @LASTNAME,
            GENDERCODE = @GENDERCODE,
            BIRTHDATE = @BIRTHDATE,
            PICTURE = @PICTURE,
            PICTURETHUMBNAIL = @PICTURETHUMBNAIL,
            ISHIVPOSITIVE = @ISHIVPOSITIVE,
            ISORPHANED = @ISORPHANED,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
      where
            ID = @ID

      update
            dbo.CONSTITUENT
      set 
            FIRSTNAME = @FIRSTNAME,
            MIDDLENAME = @MIDDLENAME,
            KEYNAME = @LASTNAME,
            GENDERCODE = @GENDERCODE,
            BIRTHDATE = @BIRTHDATE,
            PICTURE = @PICTURE,
            PICTURETHUMBNAIL = @PICTURETHUMBNAIL,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
      where
            ID = @CONSTITUENTID
   end         
   else
   begin
      update
            dbo.SPONSORSHIPOPPORTUNITYCHILD
      set 
            SPROPPCHILDCONDITIONCODEID = @SPROPPCHILDCONDITIONCODEID,
            FIRSTNAME = @FIRSTNAME,
            MIDDLENAME = @MIDDLENAME,
            LASTNAME = @LASTNAME,
            GENDERCODE = @GENDERCODE,
            BIRTHDATE = @BIRTHDATE,
            ISHIVPOSITIVE = @ISHIVPOSITIVE,
            ISORPHANED = @ISORPHANED,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
      where
            ID = @ID

      update
            dbo.CONSTITUENT
      set 
            FIRSTNAME = @FIRSTNAME,
            MIDDLENAME = @MIDDLENAME,
            KEYNAME = @LASTNAME,
            GENDERCODE = @GENDERCODE,
            BIRTHDATE = @BIRTHDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
      where
            ID = @CONSTITUENTID
   end

  exec dbo.USP_SPONSORSHIPOPPORTUNITY_UNLOCK @ID, 0, @CHANGEAGENTID
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0