USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYDOCSINTERACTIONSTILE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYDOCSINTERACTIONSTILE
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ISGROUP bit = null output,
    @NOTECOUNT int = null output,
    @MEDIALINKCOUNT int = null output,
    @ATTACHMENTCOUNT int = null output,
    @INTERACTIONCOUNT int = null output
)
as
    set nocount on;

    declare @ISSYSADMIN bit = 0;
    select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

  -- Drop temp table if needed

  if object_id('tempdb..#TEMP_CODETABLEENTRYPERMISSIONS') is not null
    drop table #TEMP_CODETABLEENTRYPERMISSIONS;

  create table #TEMP_CODETABLEENTRYPERMISSIONS
  (
    CODETABLEENTRYID uniqueidentifier
  );

  if @ISSYSADMIN = 0
  begin
    -- Collect all relevant code table entries

    insert into #TEMP_CODETABLEENTRYPERMISSIONS
    select ID from dbo.CONSTITUENTNOTETYPECODE
    union all
    select ID from dbo.CONSTITUENTMEDIALINKTYPECODE
    union all
    select ID from dbo.CONSTITUENTATTACHMENTTYPECODE;

    -- Remove denied entries

    delete #TEMP_CODETABLEENTRYPERMISSIONS
    from #TEMP_CODETABLEENTRYPERMISSIONS T
      inner join dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_CODETABLEENTRY PERMISSIONS
        with (NOEXPAND, INDEX(IX_V_SECURITY_SYSTEMROLEASSIGNMENT_USER_CODETABLEENTRY_CODETABLEENTRYID_APPUSERID))
        on T.CODETABLEENTRYID = PERMISSIONS.CODETABLEENTRYID
    where
      APPUSERID = @CURRENTAPPUSERID and
      ISDENIED = 1;
  end

    select 
        @DATALOADED = 1,
        @ISGROUP = CONSTITUENT.ISGROUP
    from dbo.CONSTITUENT
    where CONSTITUENT.ID = @ID

    --Count up all the documentation on the given constituent


  if @ISSYSADMIN = 1
  begin
      select @NOTECOUNT = count(ID)
      from dbo.CONSTITUENTNOTE
      where CONSTITUENTNOTE.CONSTITUENTID = @ID;
  end
  else
  begin
      select @NOTECOUNT = count(ID)
      from dbo.CONSTITUENTNOTE
      inner join #TEMP_CODETABLEENTRYPERMISSIONS PERMISSIONS on CONSTITUENTNOTE.CONSTITUENTNOTETYPECODEID = PERMISSIONS.CODETABLEENTRYID
      where CONSTITUENTNOTE.CONSTITUENTID = @ID;
  end

  if @ISSYSADMIN = 1
  begin
      select @MEDIALINKCOUNT = count(ID)
      from dbo.CONSTITUENTMEDIALINK
      where CONSTITUENTMEDIALINK.CONSTITUENTID = @ID;
  end
  else
  begin
      select @MEDIALINKCOUNT = count(ID)
      from dbo.CONSTITUENTMEDIALINK
      inner join #TEMP_CODETABLEENTRYPERMISSIONS PERMISSIONS on CONSTITUENTMEDIALINK.CONSTITUENTMEDIALINKTYPECODEID = PERMISSIONS.CODETABLEENTRYID
      where CONSTITUENTMEDIALINK.CONSTITUENTID = @ID;
  end

  if @ISSYSADMIN = 1
  begin
      select @ATTACHMENTCOUNT = count(ID)
      from dbo.CONSTITUENTATTACHMENT
      where CONSTITUENTATTACHMENT.CONSTITUENTID = @ID;
  end
  else
  begin
      select @ATTACHMENTCOUNT = count(ID)
      from dbo.CONSTITUENTATTACHMENT
      inner join #TEMP_CODETABLEENTRYPERMISSIONS PERMISSIONS on CONSTITUENTATTACHMENT.CONSTITUENTATTACHMENTTYPECODEID = PERMISSIONS.CODETABLEENTRYID
      where CONSTITUENTATTACHMENT.CONSTITUENTID = @ID;
  end

  --If the constituent is a group, add on any documentation on its members

    if @ISGROUP = 1
    begin
    if @ISSYSADMIN = 1
    begin
          select @NOTECOUNT = @NOTECOUNT + count(CONSTITUENTNOTE.ID)
          from dbo.CONSTITUENTNOTE
              inner join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENTNOTE.CONSTITUENTID
              left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
          where
        GROUPMEMBER.GROUPID = @ID and
        (
                  (
            GROUPMEMBERDATERANGE.DATEFROM is null and
            (
              GROUPMEMBERDATERANGE.DATETO is null or
              GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTNOTE.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATETO is null and 
            (
              GROUPMEMBERDATERANGE.DATEFROM is null or
              GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTNOTE.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTNOTE.DATEENTERED and
            GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTNOTE.DATEENTERED
                  )
              );
    end
    else
    begin
          select @NOTECOUNT = @NOTECOUNT + count(CONSTITUENTNOTE.ID)
          from dbo.CONSTITUENTNOTE
              inner join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENTNOTE.CONSTITUENTID
        inner join #TEMP_CODETABLEENTRYPERMISSIONS PERMISSIONS on CONSTITUENTNOTE.CONSTITUENTNOTETYPECODEID = PERMISSIONS.CODETABLEENTRYID
              left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
          where
        GROUPMEMBER.GROUPID = @ID and
        (
                  (
            GROUPMEMBERDATERANGE.DATEFROM is null and
            (
              GROUPMEMBERDATERANGE.DATETO is null or
              GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTNOTE.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATETO is null and 
            (
              GROUPMEMBERDATERANGE.DATEFROM is null or
              GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTNOTE.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTNOTE.DATEENTERED and
            GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTNOTE.DATEENTERED
                  )
              );
    end

    if @ISSYSADMIN = 1
    begin
          select @MEDIALINKCOUNT = @MEDIALINKCOUNT + count(CONSTITUENTMEDIALINK.ID)
          from dbo.CONSTITUENTMEDIALINK
              inner join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENTMEDIALINK.CONSTITUENTID
              left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
          where
        GROUPMEMBER.GROUPID = @ID and
        (
                  (
            GROUPMEMBERDATERANGE.DATEFROM is null and
            (
              GROUPMEMBERDATERANGE.DATETO is null or
              GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTMEDIALINK.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATETO is null and
            (
              GROUPMEMBERDATERANGE.DATEFROM is null or
              GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTMEDIALINK.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTMEDIALINK.DATEENTERED and
            GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTMEDIALINK.DATEENTERED
                  )
              );
    end
    else
    begin
          select @MEDIALINKCOUNT = @MEDIALINKCOUNT + count(CONSTITUENTMEDIALINK.ID)
          from dbo.CONSTITUENTMEDIALINK
              inner join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENTMEDIALINK.CONSTITUENTID
        inner join #TEMP_CODETABLEENTRYPERMISSIONS PERMISSIONS on CONSTITUENTMEDIALINK.CONSTITUENTMEDIALINKTYPECODEID = PERMISSIONS.CODETABLEENTRYID
              left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
          where
        GROUPMEMBER.GROUPID = @ID and
        (
                  (
            GROUPMEMBERDATERANGE.DATEFROM is null and
            (
              GROUPMEMBERDATERANGE.DATETO is null or
              GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTMEDIALINK.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATETO is null and
            (
              GROUPMEMBERDATERANGE.DATEFROM is null or
              GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTMEDIALINK.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTMEDIALINK.DATEENTERED and
            GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTMEDIALINK.DATEENTERED
                  )
              );
    end

    if @ISSYSADMIN = 1
    begin
      select @ATTACHMENTCOUNT = @ATTACHMENTCOUNT + count(CONSTITUENTATTACHMENT.ID)
          from dbo.CONSTITUENTATTACHMENT
              inner join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENTATTACHMENT.CONSTITUENTID
              left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
          where
        GROUPMEMBER.GROUPID = @ID and
        (
                  (
            GROUPMEMBERDATERANGE.DATEFROM is null and
            (
              GROUPMEMBERDATERANGE.DATETO is null or
              GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTATTACHMENT.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATETO is null and
            (
              GROUPMEMBERDATERANGE.DATEFROM is null or
              GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTATTACHMENT.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTATTACHMENT.DATEENTERED and
            GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTATTACHMENT.DATEENTERED
                  )
              );
    end
    else
    begin
      select @ATTACHMENTCOUNT = @ATTACHMENTCOUNT + count(CONSTITUENTATTACHMENT.ID)
          from dbo.CONSTITUENTATTACHMENT
              inner join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENTATTACHMENT.CONSTITUENTID
        inner join #TEMP_CODETABLEENTRYPERMISSIONS PERMISSIONS on CONSTITUENTATTACHMENT.CONSTITUENTATTACHMENTTYPECODEID = PERMISSIONS.CODETABLEENTRYID
              left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
          where
        GROUPMEMBER.GROUPID = @ID and
        (
                  (
            GROUPMEMBERDATERANGE.DATEFROM is null and
            (
              GROUPMEMBERDATERANGE.DATETO is null or
              GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTATTACHMENT.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATETO is null and
            (
              GROUPMEMBERDATERANGE.DATEFROM is null or
              GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTATTACHMENT.DATEENTERED
                      )
                  ) or
          (
            GROUPMEMBERDATERANGE.DATEFROM <= CONSTITUENTATTACHMENT.DATEENTERED and
            GROUPMEMBERDATERANGE.DATETO >= CONSTITUENTATTACHMENT.DATEENTERED
                  )
              );
    end

        --Use interaction datalist logic to count up the number of rows that will appear there.

        exec dbo.USP_CONSTITUENTGROUP_INTERACTIONCOUNT
            @ID,
            @CURRENTAPPUSERID,
            'DC27B924-D2EC-4865-B5A6-77B52FDAA957',
            2,
            @INTERACTIONCOUNT output;
    end
    else
    begin
        -- If the constituent is not a group, use the appropriate interaction datalist 

        -- logic to count up the number of rows that will appear there.

        select @INTERACTIONCOUNT = count(ID)
        from dbo.UFN_CONSTITUENT_INTERACTIONS(
            @ID,
            null,
            null,
            null,
            @CURRENTAPPUSERID,
            0,
            null,
            'CBBAC8AF-4F55-4A6E-B94C-628CF44D240D', --Security feature: Constituent Interactions List2

            2, --Security Feature Type: Datalist

            null
        );
    end

  -- Drop temp table

  if object_id('tempdb..#TEMP_CODETABLEENTRYPERMISSIONS') is not null
    drop table #TEMP_CODETABLEENTRYPERMISSIONS;