USP_STUDENT_ADDRELATION

CREATE procedure dbo.USP_STUDENT_ADDRELATION
(
    @CONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null,
    @SKIP_ADDING_SITES bit = 0,
    @SKIP_ADDING_SECURITYGROUPS bit = 0,
    @RELATION_CONSTITUENTID uniqueidentifier = null output,
    @RELATION_LASTNAME nvarchar(100) = '',
    @RELATION_FIRSTNAME nvarchar(50) = '',
    @RELATION_MIDDLENAME nvarchar(50) = '',
    @RELATION_TITLECODEID uniqueidentifier = null,
    @RELATION_TITLE2CODEID uniqueidentifier = null,
    @RELATION_SUFFIXCODEID uniqueidentifier = null,
    @RELATION_SUFFIX2CODEID uniqueidentifier = null,
    @RELATION_NICKNAME nvarchar(50) = '',
    @RELATION_MAIDENNAME nvarchar(50) = '',
    @RELATION_MARITALSTATUSCODEID uniqueidentifier = null,
    @RELATION_GENDERCODE tinyint = 0,
    @RELATION_BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
    @RELATION_PHONE_PHONETYPECODEID uniqueidentifier = null,
    @RELATION_PHONE_NUMBER nvarchar(100) = '',
    @RELATION_PHONE_PHONETYPECODEID_2 uniqueidentifier = null,
    @RELATION_PHONE_NUMBER_2 nvarchar(100) = '',
    @RELATION_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
    @RELATION_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
    @RELATION_RELATIONSHIPTYPECODEID uniqueidentifier,
    @RELATION_RECIPROCALTYPECODEID uniqueidentifier,
    @RELATION_COPYPRIMARYINFORMATION bit = 1,
    @RELATION_ISSPOUSE bit = 0,
    @RELATION_COMMENTS nvarchar(max) = null,
    @RELATION_RELATIONSHIPID uniqueidentifier = null output,
    @RELATION_ADDRESSCOUNTRYID uniqueidentifier = null
)
as
begin            

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

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

    if (@RELATION_CONSTITUENTID is null) and (@RELATION_LASTNAME is not null and len(@RELATION_LASTNAME) > 0)
    begin
        set @RELATION_CONSTITUENTID = newid();

        --Use the education version so ISCONSTITUENT = 0

        --exec dbo.USP_DATAFORMTEMPLATE_INDIVIDUALRECORDSIMPLIFIED_ADD @RELATION_CONSTITUENTID, @CURRENTAPPUSERID, @CHANGEAGENTID, @RELATION_LASTNAME, @RELATION_FIRSTNAME, @RELATION_MIDDLENAME, @RELATION_NICKNAME, @RELATION_TITLECODEID, @RELATION_SUFFIXCODEID, @RELATION_TITLE2CODEID, @RELATION_SUFFIX2CODEID, @RELATION_MAIDENNAME, @RELATION_GENDERCODE, @RELATION_BIRTHDATE, @SKIP_ADDING_SECURITYGROUPS, @SKIP_ADDING_SITES

        exec dbo.USP_DATAFORMTEMPLATE_ADD_EDUCATIONINDIVIDUALRECORD
                        @RELATION_CONSTITUENTID,
                        @CURRENTAPPUSERID,
                        @CHANGEAGENTID,
                        @RELATION_LASTNAME,
                        @RELATION_FIRSTNAME,
                        @RELATION_MIDDLENAME,
                        @RELATION_MAIDENNAME,
                        @RELATION_NICKNAME,
                        @RELATION_TITLECODEID,
                        @RELATION_SUFFIXCODEID,
                        @RELATION_GENDERCODE,
                        @RELATION_BIRTHDATE,
                        @ADDRESS_COUNTRYID = @RELATION_ADDRESSCOUNTRYID,
                        @PHONE_PHONETYPECODEID = @RELATION_PHONE_PHONETYPECODEID,
                        @PHONE_NUMBER = @RELATION_PHONE_NUMBER,
                        @EMAILADDRESS_EMAILADDRESSTYPECODEID = @RELATION_EMAILADDRESS_EMAILADDRESSTYPECODEID,
                        @EMAILADDRESS_EMAILADDRESS = @RELATION_EMAILADDRESS_EMAILADDRESS,                        
                        @MARITALSTATUSCODEID = @RELATION_MARITALSTATUSCODEID,
                        @TITLE2CODEID = @RELATION_TITLE2CODEID,
                        @SUFFIX2CODEID = @RELATION_SUFFIX2CODEID,
                        @SKIP_ADDING_SECURITYGROUPS = @SKIP_ADDING_SECURITYGROUPS,
                        @SKIP_ADDING_SITES = @SKIP_ADDING_SITES,
                        @PHONE2_PHONETYPECODEID = @RELATION_PHONE_PHONETYPECODEID_2,
                        @PHONE2_NUMBER = @RELATION_PHONE_NUMBER_2
    end

    -- throw an error if @Relation_ConstituentID has not been set

    if @RELATION_CONSTITUENTID is null
    begin
        raiserror('ERR_RELATION_NOTSPECIFIED', 13, 1)
        return 0        
    end

    -- Validate the user has permission for the relation

    if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@RELATION_CONSTITUENTID, @CURRENTAPPUSERID) = 0
    begin
        raiserror('ERR_RELATION_NOACCESS', 13, 1)
        return 0
    end

    -- If this is the spousal relationship, we have to check the spouse too

    if @RELATION_ISSPOUSE = 1
    begin
        if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@CONSTITUENTID, @CURRENTAPPUSERID) = 0
        begin
            raiserror('ERR_RELATION_NOACCESS', 13, 1)
            return 0
        end  
    end          

    declare @SETID uniqueidentifier                 

    -- start second member relationship with primary

    -- These are required fields to create a relationship and we don't want to create a new set if the relationship will fail

    if (@CONSTITUENTID is not null) or (@RELATION_CONSTITUENTID is not null) or (@RELATION_RELATIONSHIPTYPECODEID is not null) or (@RELATION_RECIPROCALTYPECODEID is not null)
    begin

        set @SETID = newid();

        insert into dbo.RELATIONSHIPSET
        (
            ID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @SETID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );
    end

    set @RELATION_RELATIONSHIPID = newid();

    insert into dbo.RELATIONSHIP
    (
        ID,
        RELATIONSHIPCONSTITUENTID,
        RECIPROCALCONSTITUENTID,
        RELATIONSHIPTYPECODEID,
        RECIPROCALTYPECODEID,
        ISSPOUSE,
        RELATIONSHIPSETID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    values
    (
        @RELATION_RELATIONSHIPID,
        @CONSTITUENTID,
        @RELATION_CONSTITUENTID,
        @RELATION_RELATIONSHIPTYPECODEID,
        @RELATION_RECIPROCALTYPECODEID,
        @RELATION_ISSPOUSE,                
        @SETID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    );

    --Spouse constituency

    if @RELATION_ISSPOUSE = 1
    begin
        update dbo.CONSTITUENT
        set 
            ISCONSTITUENT = case 
                when (select ISCONSTITUENT from dbo.CONSTITUENT where ID = @CONSTITUENTID) = 1 
                then dbo.UFN_CONSTITUENCYCRITERIASPOUSE_SPOUSEISCONSTITUENT() 
                else 0 end,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from dbo.CONSTITUENT
        where ID = @RELATION_CONSTITUENTID
    end

    --Update comments if it's passed in

    if @RELATION_COMMENTS is not null
    begin
        update dbo.RELATIONSHIP
        set COMMENTS = @RELATION_COMMENTS
        where ID = @RELATION_RELATIONSHIPID 
    end

    exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @CONSTITUENTID, @RELATION_CONSTITUENTID, @RELATION_RELATIONSHIPTYPECODEID, @RELATION_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE                            

    /*Start Relation Copy Primary Contact information*/
    if @RELATION_COPYPRIMARYINFORMATION = 1             
        exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @CONSTITUENTID, @RELATION_CONSTITUENTID

end