USP_DATALIST_SELECTPARTSFORUPDATE

CREATE procedure dbo.USP_DATALIST_SELECTPARTSFORUPDATE(
     @CONTENTTYPEID       int,
     @SEARCHPARTNAME      varchar(515) = '',
     @SEARCHPERSONNAME    varchar(510) = '',
     @EVENTPROGRAMID      UNIQUEIDENTIFIER = null,
     @PAGENUMBER          int = 1,
       @PAGESIZE            int = 50,
     @SORTPARAMETER       varchar(100) ='',
     @USERID              int = 1,
     @SOURCEPARTID        int
)
as
      set nocount on;

    DECLARE @sqlCommand    nvarchar(4000)
      DECLARE @STARTROW      int
      DECLARE @ENDROW        int


    Create TABLE #Queue
    (
       SequenceId          int IDENTITY(1, 1),
       PartName            varchar(515),
       PageName            varchar(510),
       SiteName            varchar(510),
       UpdateDate          datetime,
       UpdateBy            varchar(50),
       PartID              int
    )

    Create TABLE #Queue2
    (
       SequenceId          int IDENTITY(1, 1),
       PartName            varchar(515)  DEFAULT '',
       PageName            nvarchar(max)  DEFAULT '',
       SiteName            nvarchar(max)  DEFAULT '',
       UpdateDate          datetime,
       UpdateBy            varchar(50),
       PartID              int,
       RecordCount         int,
       PageCount           int,
       NumPage             int DEFAULT 0,
       NumSite             int DEFAULT 0
    )

    SET @sqlCommand = '
         Insert into #Queue(PartName, PageName, SiteName, UpdateDate, UpdateBy, PartID)
         Select SC.Title As PartName, SP.PageName As PageName, CS.Name As SiteName,
                 SC.UpdateDate, CU.UserName As UpdateBy, SC.ID As PartID
            from PageContent PC 
                 Inner Join SitePages SP on SP.ID = PC.SitePagesID
                 Inner Join SiteContent SC on PC.SiteContentID = SC.ID
                 Inner Join ClientSites CS on SP.ClientSitesID = CS.ID
                 Inner Join ClientUsers CU on CU.ID = Isnull(SC.ChangedByCMSUserID, SC.OwnerID) '

    DECLARE @IsSupervisor   bit
    Select @IsSupervisor = IsSupervisor from ClientUsers where ID = @USERID
    If @USERID = 1
        Select @IsSupervisor = 1

    --regular user   

    If @IsSupervisor = 0 Begin
           SET @sqlCommand += ' Inner Join dbo.UFN_PART_UPDATEPERMISSION(' + Convert(varchar(10), @USERID) + ') PU on PU.ID = SC.ID '    

           --check site security

           /*
           DECLARE @CONSTITUENTID  UNIQUEIDENTIFIER

           Set @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@USERID)  

           SET @sqlCommand += ' Inner Join (Select Distinct EE.ClientSitesID from EventSite ES Inner Join ConstituentSite CS on CS.SITEID = ES.SITEID
                                             Inner Join EVENTEXTENSION EE on ES.EVENTID = EE.EVENTID
                                  where CS.CONSTITUENTID =  ''' + CAST(@CONSTITUENTID As varchar(50)) + ''' or EE.SITEID is null) SITESECURITY On SITESECURITY.ClientSitesID = CS.ID  '
           */
      End

    if @EVENTPROGRAMID != CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER) Begin
         SET @sqlCommand += ' Inner Join (Select ClientSitesID from EventExtension where FAFProgramid = ''' + CAST(@EVENTPROGRAMID As varchar(50)) + ''') NAP On NAP.ClientSitesID = CS.ID '

      End

    SET @sqlCommand += ' Where SC.ContentTypesID = ' + Convert(varchar(10), @ContentTypeID) + ' and SC.ID != '+ Convert(varchar(10), @SOURCEPARTID) + ''

    if @SEARCHPARTNAME != '' Begin
        SET @sqlCommand += ' and SC.Title like ''' +  @SEARCHPARTNAME + '%' +''''
      End

    if @SEARCHPERSONNAME != '' Begin
        SET @sqlCommand += ' and (CU.FirstName like ''' + @SEARCHPERSONNAME + '%' +''' Or CU.LastName like ''' + @SEARCHPERSONNAME + '%' +''')'
      End


    EXEC sp_executesql @sqlCommand

    -- copy data to #Queue2

  Insert into #Queue2(PartID, PartName, UpdateDate, UpdateBy)
        Select distinct PartID, PartName, UpdateDate, UpdateBy from #Queue


    DECLARE PartCursor CURSOR FOR
    Select PageName, SiteName, PartID
           From #Queue

    DECLARE @PageName      varchar(500)
    DECLARE @SiteName      varchar(500)
    DECLARE @MyPartID      int
    DECLARE @OldSiteName   varchar(500)
    DECLARE @OldPageName   varchar(200)

    OPEN PartCursor
    FETCH NEXT FROM PartCursor
      Into @PageName, @SiteName, @MyPartID

    Set @OldSiteName = ''
    Set @OldPageName = ''

    WHILE @@FETCH_STATUS = 0
     BEGIN

          Update #Queue2 Set 
                         PageName = 
                              CASE 
                                When NumPage = 0 Then @PageName
                                When NumPage <= 2 and @PageName!= @OldPageName Then PageName + ', ' + @PageName 
                                Else PageName + ''
                              End,
                         SiteName = 
                              CASE 
                                When NumSite =0 Then @SiteName
                                When NumSite <=2 and @SiteName!= @OldSiteName Then SiteName + ', ' + @SiteName   
                                Else SiteName + ''
                              End,
                         NumPage =
                              CASE 
                                  When @PageName!= @OldPageName Then NumPage + 1
                                  Else NumPage
                              End,
                         NumSite = 
                              CASE 
                                 When @SiteName!= @OldSiteName Then NumSite + 1
                                 Else NumSite
                              End

          Where PartID = @MyPartID

          Set @OldSiteName = @SiteName
          Set @OldPageName = @PageName

          FETCH NEXT FROM PartCursor
              Into @PageName, @SiteName, @MyPartID

     END 
    CLOSE PartCursor
    DEALLOCATE PartCursor

    Update #Queue2 Set  PageName = 
                              CASE 
                                When NumPage <=3  Then PageName + ''
                                Else PageName + ', and ' + Cast((NumPage -3) As varchar) + ' other pages'
                              End,
                         SiteName = 
                              CASE 
                                 When NumSite <=3  Then SiteName + ''
                                 Else SiteName + ', and ' + Cast((NumSite -3) As varchar) + ' other websites'
                              End


    -- return the final data

    DECLARE @RecordCount int
    DECLARE @PageCount int

    SELECT @RecordCount = COUNT(SequenceId) FROM #Queue2
    SET @PageCount = CEILING(@RecordCount * 1.0 / @PAGESIZE)
    Update #Queue2 Set RecordCount = @RecordCount, PageCount = @PageCount

    -- get paged data

    DECLARE @LB int
    DECLARE @UB int
    DECLARE @SelectSql  nvarchar(4000)

    SET @LB = (@PAGENUMBER - 1) * @PAGESIZE + 1
    SET @UB = @LB + @PAGESIZE - 1

    Set @SelectSql = 'Select PARTNAME,PAGENAME,SITENAME,UPDATEDATE,UPDATEBY,PARTID,RecordCount,PageCount 
       from #Queue2 Where SequenceId between '+ Convert(varchar(10), @LB) +' AND ' + Convert(varchar(10), @UB) + ''

   if @SORTPARAMETER != '' Begin
         SET @SelectSql += ' order by ''' +  @SORTPARAMETER + ''''
       End
    else Begin
           SET @SelectSql += ' order by PartName'
       End   

    EXEC sp_executesql @SelectSql

    Drop table #Queue
    Drop table #Queue2