USP_CONSTITUENTUPDATEBATCH_ADDCONSTITUENCIES

CREATE procedure dbo.USP_CONSTITUENTUPDATEBATCH_ADDCONSTITUENCIES(
  @CONSTITUENCYTABLE dbo.UDT_CONSTITUENTUPDATEBATCH_CONSTITUENCY readonly, 
  @ID uniqueidentifier, 
  @PROSPECTMANAGERFUNDRAISERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
  )
as
begin
  declare @CURRENTDATE datetime;
      set @CURRENTDATE = getdate();

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

  --Update new constituencies into the CONSTITUENCY table

  declare @USERDEFINEDCONSTITUENCY xml;
  set @USERDEFINEDCONSTITUENCY = 
      (
          select
              CONSTITUENCY.ORIGINALCONSTITUENCYID as ID,
              CONSTITUENCY.DATEFROM,
              CONSTITUENCY.DATETO,
              CONSTITUENCY.CONSTITUENCYCODEID
          from
              @CONSTITUENCYTABLE CONSTITUENCY
              left join dbo.CONSTITUENCYSYSTEMNAME on CONSTITUENCY.CONSTITUENCYCODEID = CONSTITUENCYSYSTEMNAME.ID
          where
              CONSTITUENCYSYSTEMNAME.ID is null
          for xml raw('ITEM'),type,elements,root('CONSTITUENCY'),BINARY BASE64
      );
  exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_UPDATEFROMXML @ID, @USERDEFINEDCONSTITUENCY, @CHANGEAGENTID;

  declare @SYSTEMDEFINEDCONSTITUENCY xml;
  set @SYSTEMDEFINEDCONSTITUENCY =
      (
          select
              CONSTITUENCY.ORIGINALCONSTITUENCYID as ID,
              CONSTITUENCY.DATEFROM,
              CONSTITUENCY.DATETO,
              CONSTITUENCY.CONSTITUENCYCODEID
          from
              @CONSTITUENCYTABLE CONSTITUENCY
          where
              CONSTITUENCY.CONSTITUENCYCODEID = 'F828E957-5F5E-479A-8F23-2FFD6C7C68FF'
          for xml raw('ITEM'),type,elements,root('CONSTITUENCY'),BINARY BASE64
      );
  exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_BOARDMEMBER_UPDATEFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;

  set @SYSTEMDEFINEDCONSTITUENCY =
      (
          select
              CONSTITUENCY.ORIGINALCONSTITUENCYID as ID,
              CONSTITUENCY.DATEFROM,
              CONSTITUENCY.DATETO,
              CONSTITUENCY.CONSTITUENCYCODEID
          from
              @CONSTITUENCYTABLE CONSTITUENCY
          where
              CONSTITUENCY.CONSTITUENCYCODEID = '6093915E-ADE9-42BE-88AE-304731754467'
          for xml raw('ITEM'),type,elements,root('CONSTITUENCY'),BINARY BASE64
      );
  exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_STAFF_UPDATEFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;

  set @SYSTEMDEFINEDCONSTITUENCY =
      (
          select
              CONSTITUENCY.ORIGINALCONSTITUENCYID as ID,
              CONSTITUENCY.DATEFROM,
              CONSTITUENCY.DATETO,
              CONSTITUENCY.CONSTITUENCYCODEID
          from
              @CONSTITUENCYTABLE CONSTITUENCY
          where
              CONSTITUENCY.CONSTITUENCYCODEID = 'D2DCA06A-BE6E-40B3-B95D-59A926181923'
          for xml raw('ITEM'),type,elements,root('CONSTITUENCY'),BINARY BASE64
      );
  exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_FUNDRAISER_UPDATEFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;

                  declare @EXISTINGPROSPECT bit = 0;

                  if exists(select top 1 1 from dbo.PROSPECT where ID = @ID)
                    set @EXISTINGPROSPECT = 1;

  set @SYSTEMDEFINEDCONSTITUENCY =
      (
          select
              CONSTITUENCY.ORIGINALCONSTITUENCYID as ID,
              CONSTITUENCY.DATEFROM,
              CONSTITUENCY.DATETO,
              CONSTITUENCY.CONSTITUENCYCODEID
          from
              @CONSTITUENCYTABLE CONSTITUENCY
          where
              CONSTITUENCY.CONSTITUENCYCODEID = '00E748FB-940D-4A7D-A133-C148B29410A8'
          for xml raw('ITEM'),type,elements,root('CONSTITUENCY'),BINARY BASE64
      );
  exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_PROSPECT_UPDATEFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;

                  if @SYSTEMDEFINEDCONSTITUENCY is not null
                  begin
                    if not exists(select 1 from dbo.PROSPECT where ID = @ID)
                    begin
                      insert into dbo.PROSPECT 
              (
                  ID,
                  PROSPECTMANAGERFUNDRAISERID,
                  ADDEDBYID, CHANGEDBYID,
                          DATEADDED, DATECHANGED
              ) 
              values 
              (
                  @ID,
                  @PROSPECTMANAGERFUNDRAISERID,
                  @CHANGEAGENTID, @CHANGEAGENTID,
                          @CURRENTDATE, @CURRENTDATE
              );
                    end
                    else if @PROSPECTMANAGERFUNDRAISERID is not null
                    begin
                      update dbo.PROSPECT set
                        PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      where
                        ID = @ID;

                      update dbo.INTERACTION set
                        FUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      from 
                        dbo.INTERACTION I
                        inner join dbo.PROSPECTPLAN PP on PP.ID = I.PROSPECTPLANID
                        left outer join dbo.PLANOUTLINESTEP SI on SI.ID=I.PLANOUTLINESTEPID
                      where
                        I.FUNDRAISERID is null
                        and SI.FUNDRAISERROLECODE = 0
                        and PP.PROSPECTID = @ID
                        and @PROSPECTMANAGERFUNDRAISERID not in (
                          select IAF.FUNDRAISERID from dbo.INTERACTIONADDITIONALFUNDRAISER as IAF
                          where IAF.INTERACTIONID = I.ID
                        );
                    end

                  end
                  else if @EXISTINGPROSPECT = 0 and @PROSPECTMANAGERFUNDRAISERID is not null
                  begin
                    insert into dbo.PROSPECT 
          (
              ID,
              PROSPECTMANAGERFUNDRAISERID,
              ADDEDBYID, CHANGEDBYID,
                        DATEADDED, DATECHANGED
          ) 
          values 
          (
              @ID,
              @PROSPECTMANAGERFUNDRAISERID,
              @CHANGEAGENTID, @CHANGEAGENTID,
                        @CURRENTDATE, @CURRENTDATE
          );

                    insert into dbo.PROSPECTDATERANGE
          (
              CONSTITUENTID,
              ADDEDBYID, CHANGEDBYID,
              DATEADDED, DATECHANGED
          )
          values
          (
              @ID,
              @CHANGEAGENTID, @CHANGEAGENTID,
              @CURRENTDATE, @CURRENTDATE
          );
                  end
                  else if @EXISTINGPROSPECT = 1 and @PROSPECTMANAGERFUNDRAISERID is not null
                  begin
                    update dbo.PROSPECT set
                      PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID,
                      CHANGEDBYID = @CHANGEAGENTID,
                      DATECHANGED = @CURRENTDATE
                    where
                      ID = @ID;

                    update dbo.INTERACTION set
                      FUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID,
                      CHANGEDBYID = @CHANGEAGENTID,
                      DATECHANGED = @CURRENTDATE
                    from 
                      dbo.INTERACTION I
                      inner join dbo.PROSPECTPLAN PP on PP.ID = I.PROSPECTPLANID
                      left outer join dbo.PLANOUTLINESTEP SI on SI.ID=I.PLANOUTLINESTEPID
                    where
                      I.FUNDRAISERID is null
                      and SI.FUNDRAISERROLECODE = 0
                      and PP.PROSPECTID = @ID
                      and @PROSPECTMANAGERFUNDRAISERID not in (
                        select IAF.FUNDRAISERID from dbo.INTERACTIONADDITIONALFUNDRAISER as IAF
                        where IAF.INTERACTIONID = I.ID
                      );
                  end

                  set @SYSTEMDEFINEDCONSTITUENCY =
      (
          select
              CONSTITUENCY.ORIGINALCONSTITUENCYID as ID,
              CONSTITUENCY.DATEFROM,
              CONSTITUENCY.DATETO,
              CONSTITUENCY.CONSTITUENCYCODEID
          from
              @CONSTITUENCYTABLE CONSTITUENCY
          where
              CONSTITUENCY.CONSTITUENCYCODEID = 'E7489703-3D63-4017-A2BC-88C092563C5D'
          for xml raw('ITEM'),type,elements,root('CONSTITUENCY'),BINARY BASE64
      );
  exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_VOLUNTEER_UPDATEFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
  if @SYSTEMDEFINEDCONSTITUENCY.exist('/CONSTITUENCY/ITEM/CONSTITUENCYCODEID/text()[string-length() > 0]') = 1
  begin
      if not exists (select 1 from dbo.VOLUNTEER where ID=@ID)
          insert into dbo.VOLUNTEER
          (
              ID,
              ADDEDBYID,
              CHANGEDBYID,
              DATEADDED,
              DATECHANGED
          )
          values
          (
              @ID,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
          );
  end
  else
      exec dbo.USP_VOLUNTEER_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

  --Committee

  set @SYSTEMDEFINEDCONSTITUENCY =
      (
          select
              CONSTITUENCY.ORIGINALCONSTITUENCYID as ID,
              CONSTITUENCY.DATEFROM,
              CONSTITUENCY.DATETO,
              CONSTITUENCY.CONSTITUENCYCODEID
          from
              @CONSTITUENCYTABLE CONSTITUENCY
          where
              CONSTITUENCY.CONSTITUENCYCODEID = 'AC9DB5A4-14E0-416A-9FB2-04038AC66799'
          for xml raw('ITEM'),type,elements,root('CONSTITUENCY'),BINARY BASE64
      );
  exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_COMMITTEE_UPDATEFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
  if @SYSTEMDEFINEDCONSTITUENCY.exist('/CONSTITUENCY/ITEM/CONSTITUENCYCODEID/text()[string-length() > 0]') = 1
  begin
      if not exists (select 1 from dbo.COMMITTEE where ID=@ID)
          insert into dbo.COMMITTEE
          (
              ID,
              ADDEDBYID,
              CHANGEDBYID,
              DATEADDED,
              DATECHANGED
          )
          values
          (
              @ID,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
          );
  end
  else
      exec dbo.USP_COMMITTEE_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

end