USP_DATAFORMTEMPLATE_VIEW_COMMUNITYMEMBERMOSTFREQUENTPAGES

The load procedure used by the view dataform template "Community Member Pages Most Frequently Visited View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NETCOMMUNITYCLIENTUSERID	uniqueidentifier	INOUT	NETCOMMUNITYCLIENTUSERID
@TOPPAGES	xml	INOUT	Top pages

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_COMMUNITYMEMBERMOSTFREQUENTPAGES
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NETCOMMUNITYCLIENTUSERID uniqueidentifier = null output,
    @TOPPAGES xml = null output 
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    declare @pagetable table (PAGENAME nvarchar(50), URL nvarchar(2047), VISITCOUNT int);
    declare @pagetemptable table (PAGENAME nvarchar(50), URL nvarchar(2047), VISITCOUNT int);
    create table #NETCOMMUNITYCLIENTUSERIDSTABLE(ROWINDEX int identity(1,1),ID uniqueidentifier);
      declare @ROWINDEX int = 1,@ROWCOUNT int = 0;
    -- SP to populate data in temp table and determine the row count

    exec dbo.USP_BBNC_GETNETCOMMUNITYCLIENTUSERIDS @ID,@ROWCOUNT output;

      while(@ROWINDEX <= @ROWCOUNT)
      begin
          select @NETCOMMUNITYCLIENTUSERID = ID from #NETCOMMUNITYCLIENTUSERIDSTABLE where ROWINDEX = @ROWINDEX;

           -- default VIEWFILTER = 2 (month), top 5 pages

          insert into @pagetemptable
              exec dbo.USP_DATALIST_COMMUNITYMEMBERMOSTFREQUENTPAGES @NETCOMMUNITYCLIENTUSERID, 2, 5;

          set @ROWINDEX = @ROWINDEX +1;
      end

      insert into @pagetable(PAGENAME,URL,VISITCOUNT)
              select PAGENAME,URL,sum(VISITCOUNT) from @pagetemptable group by PAGENAME,URL;

    set @TOPPAGES = 
        (select
            PAGENAME,
            URL,
            VISITCOUNT
        from @pagetable
        for xml raw('ITEM'),type,elements,root('TOPPAGES'),BINARY BASE64)

    set @DATALOADED = 1;
    drop table #NETCOMMUNITYCLIENTUSERIDSTABLE;
    return 0;