USP_DATAFORMTEMPLATE_EDIT_MKTVENDORCONTACT_2

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTVENDORCONTACT_2]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @CONTACT_LASTNAME nvarchar(100),
  @CONTACT_FIRSTNAME nvarchar(50),
  @CONTACT_MIDDLENAME nvarchar(50),
  @CONTACT_TITLECODEID uniqueidentifier,
  @CONTACT_SUFFIXCODEID uniqueidentifier,
  @CONTACT_NICKNAME nvarchar(50),
  @CONTACT_ADDRESS_TYPECODEID uniqueidentifier,
  @CONTACT_ADDRESS_COUNTRYID uniqueidentifier,
  @CONTACT_ADDRESS_ADDRESSBLOCK nvarchar(150),
  @CONTACT_ADDRESS_CITY nvarchar(50),
  @CONTACT_ADDRESS_STATEID uniqueidentifier,
  @CONTACT_ADDRESS_POSTCODE nvarchar(12),
  @CONTACT_ADDRESS_DONOTMAIL bit,
  @CONTACT_ADDRESS_DONOTMAILREASONCODEID uniqueidentifier,
  @CONTACT_PHONE_PHONETYPECODEID uniqueidentifier,
  @CONTACT_PHONE_NUMBER nvarchar(100),
  @CONTACT_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier,
  @CONTACT_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS,
  @CONTACT_RELATIONSHIPTYPECODEID uniqueidentifier,
  @CONTACT_RECIPROCALTYPECODEID uniqueidentifier,
  @CONTACTTYPECODEID uniqueidentifier,
  @CONTACT_POSITION nvarchar(100),
  @ISPRIMARYCONTACT bit,
  @CONTACT_TITLE2CODEID uniqueidentifier,
  @CONTACT_SUFFIX2CODEID uniqueidentifier
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @VENDORID uniqueidentifier;
  declare @CONTACTID uniqueidentifier;
  declare @ADDRESSID uniqueidentifier;
  declare @PHONEID uniqueidentifier;
  declare @EMAILADDRESSID uniqueidentifier;

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

    set @CURRENTDATE = getdate();

    select
      @VENDORID = [RELATIONSHIP].[RELATIONSHIPCONSTITUENTID],
      @CONTACTID = [RELATIONSHIP].[RECIPROCALCONSTITUENTID]
    from
      dbo.[RELATIONSHIP]
    where
      [ID] = @ID;

    update dbo.[CONSTITUENT] set
      [KEYNAME] = @CONTACT_LASTNAME,
      [FIRSTNAME] = @CONTACT_FIRSTNAME,
      [MIDDLENAME] = @CONTACT_MIDDLENAME,
      [NICKNAME] = @CONTACT_NICKNAME,
      [TITLECODEID] = @CONTACT_TITLECODEID,
      [SUFFIXCODEID] = @CONTACT_SUFFIXCODEID,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE,
      [TITLE2CODEID] = @CONTACT_TITLE2CODEID,
      [SUFFIX2CODEID] = @CONTACT_SUFFIX2CODEID
    where [ID] = @CONTACTID;

    select @ADDRESSID = [ADDRESS].[ID]
    from dbo.[ADDRESS]
    where [CONSTITUENTID] = @CONTACTID and [ISPRIMARY] = 1;

    if @ADDRESSID is not null
      update dbo.[ADDRESS] set
        [ADDRESSTYPECODEID] = @CONTACT_ADDRESS_TYPECODEID,
        [COUNTRYID] = @CONTACT_ADDRESS_COUNTRYID,
        [ADDRESSBLOCK] = @CONTACT_ADDRESS_ADDRESSBLOCK,
        [CITY] = @CONTACT_ADDRESS_CITY,
        [STATEID] = @CONTACT_ADDRESS_STATEID,
        [POSTCODE] = @CONTACT_ADDRESS_POSTCODE,
        [DONOTMAIL] = isnull(@CONTACT_ADDRESS_DONOTMAIL, 0),
        [DONOTMAILREASONCODEID] = @CONTACT_ADDRESS_DONOTMAILREASONCODEID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @ADDRESSID;
    else
      if @CONTACT_ADDRESS_COUNTRYID is not null or
         isnull(@CONTACT_ADDRESS_ADDRESSBLOCK, '') <> '' or
         isnull(@CONTACT_ADDRESS_CITY, '') <> '' or
         @CONTACT_ADDRESS_STATEID is not null or
         isnull(@CONTACT_ADDRESS_POSTCODE, '') <> '' or
         isnull(@CONTACT_ADDRESS_DONOTMAIL, 0) <> 0 or
         @CONTACT_ADDRESS_DONOTMAILREASONCODEID is not null
        insert into dbo.[ADDRESS] (
          [CONSTITUENTID], 
          [ISPRIMARY],
          [ADDRESSTYPECODEID],
          [COUNTRYID],
          [ADDRESSBLOCK],
          [CITY],
          [STATEID],
          [POSTCODE],
          [DONOTMAIL],
          [DONOTMAILREASONCODEID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @CONTACTID,
1,
          @CONTACT_ADDRESS_TYPECODEID,
          @CONTACT_ADDRESS_COUNTRYID,
          @CONTACT_ADDRESS_ADDRESSBLOCK,
          @CONTACT_ADDRESS_CITY,
          @CONTACT_ADDRESS_STATEID,
          @CONTACT_ADDRESS_POSTCODE,
          @CONTACT_ADDRESS_DONOTMAIL,
          @CONTACT_ADDRESS_DONOTMAILREASONCODEID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );

    select @PHONEID = [ID]
    from dbo.[PHONE]
    where [CONSTITUENTID] = @CONTACTID and [ISPRIMARY] = 1;

    if @PHONEID is not null
      update dbo.[PHONE] set
        [PHONETYPECODEID] = @CONTACT_PHONE_PHONETYPECODEID,
        [NUMBER] = @CONTACT_PHONE_NUMBER,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @PHONEID;
    else
      if @CONTACT_PHONE_PHONETYPECODEID is not null or 
         isnull(@CONTACT_PHONE_NUMBER, '') <> ''
        insert into dbo.[PHONE] (
          [CONSTITUENTID],
          [PHONETYPECODEID],
          [NUMBER],
          [ISPRIMARY],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @CONTACTID,
          @CONTACT_PHONE_PHONETYPECODEID,
          @CONTACT_PHONE_NUMBER,
          1,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );

    select @EMAILADDRESSID = [ID]
    from dbo.[EMAILADDRESS]
    where [CONSTITUENTID] = @CONTACTID and [ISPRIMARY] = 1;

    if @EMAILADDRESSID is not null
      update dbo.[EMAILADDRESS] set
        [EMAILADDRESSTYPECODEID] = @CONTACT_EMAILADDRESS_EMAILADDRESSTYPECODEID,
        [EMAILADDRESS] = @CONTACT_EMAILADDRESS_EMAILADDRESS,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @EMAILADDRESSID;
    else
      if @CONTACT_EMAILADDRESS_EMAILADDRESSTYPECODEID is not null or 
         isnull(@CONTACT_EMAILADDRESS_EMAILADDRESS, '') <> ''
        insert into dbo.[EMAILADDRESS] (
          [CONSTITUENTID],
          [EMAILADDRESSTYPECODEID],
          [EMAILADDRESS],
          [ISPRIMARY],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @CONTACTID,
          @CONTACT_EMAILADDRESS_EMAILADDRESSTYPECODEID,
          @CONTACT_EMAILADDRESS_EMAILADDRESS,
          1,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );

    if @ISPRIMARYCONTACT = 1
      update dbo.[RELATIONSHIP]
      set
        [ISPRIMARYCONTACT] = 0,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [RELATIONSHIPCONSTITUENTID] = @VENDORID and [ISCONTACT] = 1;

  declare @RELATIONSHIPSETID uniqueidentifier;

  select @RELATIONSHIPSETID = [RELATIONSHIPSETID]
  from dbo.[RELATIONSHIP] where [ID] = @ID;

  if @RELATIONSHIPSETID is null exec dbo.[USP_RELATIONSHIPSET_RETROCREATE] @RELATIONSHIPSETID output, @CHANGEAGENTID, @ID

  declare @JOBINFOID uniqueidentifier;
  declare @JOBCATEGORYCODEID uniqueidentifier;
  declare @CAREERLEVELCODEID uniqueidentifier;
  declare @JOBDIVISION nvarchar(100);
  declare @JOBDEPARTMENT nvarchar(100);
  declare @JOBSCHEDULECODEID uniqueidentifier;

  select 
    @JOBINFOID = [ID],
    @JOBCATEGORYCODEID = [JOBCATEGORYCODEID],
    @CAREERLEVELCODEID = [CAREERLEVELCODEID],
    @JOBDIVISION = [JOBDIVISION],
    @JOBDEPARTMENT = [JOBDEPARTMENT],
    @JOBSCHEDULECODEID = [JOBSCHEDULECODEID]
  from dbo.[RELATIONSHIPJOBINFO] 
  where [RELATIONSHIPSETID] = @RELATIONSHIPSETID
    and ([RELATIONSHIPJOBINFO].[STARTDATE] <= @CURRENTDATE or [RELATIONSHIPJOBINFO].[STARTDATE] is null)
    and ([RELATIONSHIPJOBINFO].[ENDDATE] >= @CURRENTDATE or [RELATIONSHIPJOBINFO].[ENDDATE] is null)

  if @JOBINFOID is not null
    begin
      --if any of these fields still contains data then we want to update the record

      if @JOBCATEGORYCODEID is not null or 
         @CAREERLEVELCODEID is not null or 
         len(@JOBDIVISION) > 0 or 
         len(@JOBDEPARTMENT) > 0 or 
         @JOBSCHEDULECODEID is not null or 
         len(@CONTACT_POSITION) > 0
        begin
          update dbo.[RELATIONSHIPJOBINFO] set
            [JOBTITLE] = coalesce(@CONTACT_POSITION,''),
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [RELATIONSHIPSETID] = @RELATIONSHIPSETID
            and ([ENDDATE] is null or [ENDDATE] >= @CURRENTDATE)
            and ([STARTDATE] is null or [STARTDATE] < @CURRENTDATE);
        end
      else
        --If all of the relevant job info record data is null the record is no longer needed

        delete from dbo.[RELATIONSHIPJOBINFO] where [ID] = @JOBINFOID
    end
  else 
    if len(@CONTACT_POSITION) > 0 
      --If the record does not yet exist and we have relevant information to populate it with (ie JOBTILE) create it.

      insert into dbo.[RELATIONSHIPJOBINFO]
      (
        [RELATIONSHIPSETID],
        [JOBTITLE],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
      ) values (
        @RELATIONSHIPSETID,
        @CONTACT_POSITION,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      )

    update dbo.[RELATIONSHIP] set
      [RELATIONSHIPTYPECODEID] = @CONTACT_RELATIONSHIPTYPECODEID,
      [RECIPROCALTYPECODEID] = @CONTACT_RECIPROCALTYPECODEID,
      [CONTACTTYPECODEID] = @CONTACTTYPECODEID,
      [ISPRIMARYCONTACT] = @ISPRIMARYCONTACT,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;


  exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE 
    @VENDORID, 
    @CONTACTID, 
    @CONTACT_RELATIONSHIPTYPECODEID, 
    @CONTACT_RECIPROCALTYPECODEID, 
    @CHANGEAGENTID, 
    @CURRENTDATE    

  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;