USP_GLOBALCHANGE_ADDUSERDEFINEDCONSTITUENCY

CREATE procedure dbo.USP_GLOBALCHANGE_ADDUSERDEFINEDCONSTITUENCY
(
  @IDSETREGISTERID uniqueidentifier = null,
  @CONSTITUENCYCODEID uniqueidentifier,
  @DATEFROM datetime = null,
  @DATETO datetime = null,
  @CHANGEAGENTID uniqueidentifier = null,
  @ASOF as datetime = null,
  @OVERWRITEEXISTINGVALUE bit,
  @REMOVEUNQUALIFIED bit,
  @NUMBERADDED int = 0 output,
  @NUMBEREDITED int = 0 output,
  @NUMBERDELETED int = 0 output,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime = getdate();

  set @NUMBERADDED = 0;
  set @NUMBEREDITED = 0;
  set @NUMBERDELETED = 0; 

  declare @BPID uniqueidentifier = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';
  declare @BYPASSSECURITY bit;
  declare @BYPASSSITESECURITY bit;

  exec dbo.USP_SECURITY_APPUSER_BYPASSSECURITYFORBUSINESSPROCESS @CURRENTAPPUSERID, @BPID, @BYPASSSECURITY output, @BYPASSSITESECURITY output;                

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

  declare @SELECTION table (ID uniqueidentifier not null primary key);

  -- retrieve records that the user can add a constituency to

  insert into @SELECTION(ID)
  select ID from dbo.UFN_CONSTITUENT_GETRECORDSINSELECTION_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @IDSETREGISTERID, @BPID, @BYPASSSECURITY, @BYPASSSITESECURITY);

  begin try
    if @REMOVEUNQUALIFIED = 1 and @IDSETREGISTERID is not null
    begin
      declare @CONTEXTCACHE varbinary(128);

      /* Cache current context information@ */
      set @CONTEXTCACHE = CONTEXT_INFO();

      /* Set CONTEXT_INFO to @CHANGEAGENTID */
      set CONTEXT_INFO @CHANGEAGENTID;

      /* delete records */
      delete
        dbo.CONSTITUENCY
      from
        dbo.CONSTITUENCY
      left join
        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @BPID) as CONSTIT_RACS on CONSTITUENCY.CONSTITUENTID = CONSTIT_RACS.ID
      where
        CONSTITUENCY.CONSTITUENCYCODEID = @CONSTITUENCYCODEID and
        CONSTITUENCY.CONSTITUENTID not in (select ID from @SELECTION) and
        (
          @BYPASSSECURITY = 1 or
          CONSTIT_RACS.ID is not null
        )
        and 
        (
          @BYPASSSITESECURITY = 1 or 
          exists
          (
            select 1 
            from dbo.CONSTITUENTSITE
            where CONSTITUENTSITE.CONSTITUENTID = CONSTITUENCY.CONSTITUENTID 
            and dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, CONSTITUENTSITE.SITEID) = 1
          )
        );

      set @NUMBERDELETED = @@ROWCOUNT;

      /* Reset CONTEXT_INFO to previous value */
      if not @CONTEXTCACHE is null
        set CONTEXT_INFO @CONTEXTCACHE;

      delete dbo.CONSTITUENCYDATERANGE
      from
        dbo.CONSTITUENCYDATERANGE
        left join dbo.CONSTITUENCY
          on CONSTITUENCY.ID = CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER
      where
        CONSTITUENCY.ID is null
        and CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @CONSTITUENCYCODEID;
    end

    if @OVERWRITEEXISTINGVALUE = 1
    begin
      update
        dbo.CONSTITUENCY
      set
        DATEFROM = @DATEFROM,
        DATETO = @DATETO,
        CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
      where
        CONSTITUENTID in(select ID from @SELECTION) and
        CONSTITUENCYCODEID = @CONSTITUENCYCODEID and
        (
          DATEFROM is null and @DATEFROM is not null or
          DATEFROM is not null and @DATEFROM is null or
          DATEFROM <> @DATEFROM or
          DATETO is null and @DATETO is not null or
          DATETO is not null and @DATETO is null or
          DATETO <> @DATETO
        );

      set @NUMBEREDITED = @@ROWCOUNT;

      update dbo.CONSTITUENCYDATERANGE
      set
        CONSTITUENCYDATERANGE.DATEFROM = @DATEFROM,
        CONSTITUENCYDATERANGE.DATETO = @DATETO,
        CONSTITUENCYDATERANGE.DATECHANGED = @CURRENTDATE,
        CONSTITUENCYDATERANGE.CHANGEDBYAPPLICATIONNAME = CHANGEDBY.APPLICATIONNAME,
        CONSTITUENCYDATERANGE.CHANGEDBYUSERNAME = CHANGEDBY.USERNAME,
        CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE,
        CONSTITUENCYDATERANGE.CONSTITUENCYTS = CONSTITUENCY.TS
      from
        dbo.CONSTITUENCYDATERANGE
        inner join dbo.CONSTITUENCY
          on CONSTITUENCY.ID = CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER
        inner join dbo.CHANGEAGENT as CHANGEDBY
          on CHANGEDBY.ID = CONSTITUENCY.CHANGEDBYID
      where
        CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = @CONSTITUENCYCODEID
        and CONSTITUENCYDATERANGE.CONSTITUENCYTS <> CONSTITUENCY.TS;
    end

    insert into dbo.CONSTITUENCY
    (
      ID,
      CONSTITUENTID,
      CONSTITUENCYCODEID,
      DATEFROM,
      DATETO,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    select
      newid(),
      SELECTION.ID,
      @CONSTITUENCYCODEID,
      @DATEFROM,
      @DATETO,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from
      @SELECTION as SELECTION
    where
      SELECTION.ID not in (select CONSTITUENTID from dbo.CONSTITUENCY where CONSTITUENCY.CONSTITUENCYCODEID = @CONSTITUENCYCODEID);

    set @NUMBERADDED = @@ROWCOUNT;

    insert into dbo.CONSTITUENCYDATERANGE
    (
      ID,
      CONSTITUENTID,
      CONSTITUENCYDEFINITIONID,
      DATEFROM,
      DATETO,
      CONSTITUENCYRECORDIDENTIFIER,
      REFRESHDATE,
      DATEADDED,
      DATECHANGED,
      ADDEDBYAPPLICATIONNAME,
      ADDEDBYUSERNAME,
      CHANGEDBYAPPLICATIONNAME,
      CHANGEDBYUSERNAME,
      CONSTITUENCYTS
    )
    select
      newid(),
      CONSTITUENCY.CONSTITUENTID,
      @CONSTITUENCYCODEID,
      @DATEFROM,
      @DATETO,
      CONSTITUENCY.ID,
      @CURRENTDATE,
      @CURRENTDATE,
      @CURRENTDATE,
      ADDEDBY.APPLICATIONNAME,
      ADDEDBY.USERNAME,
      CHANGEDBY.APPLICATIONNAME,
      CHANGEDBY.USERNAME,
      CONSTITUENCY.TS
    from
      dbo.CONSTITUENCY
      left join dbo.CONSTITUENCYDATERANGE
        on CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER = CONSTITUENCY.ID
      inner join dbo.CHANGEAGENT as ADDEDBY
        on ADDEDBY.ID = CONSTITUENCY.ADDEDBYID
      inner join dbo.CHANGEAGENT as CHANGEDBY
        on CHANGEDBY.ID = CONSTITUENCY.CHANGEDBYID
    where
      CONSTITUENCYDATERANGE.ID is null
      and CONSTITUENCY.CONSTITUENCYCODEID = @CONSTITUENCYCODEID;
  end try

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