CREATE PROCEDURE [dbo].[GetMailReportData]
    -- Add the parameters for the stored procedure here
    @P_CampusId bigint,
    @P_MailTypeID int=null,
    @P_PageNo int,
    @P_PageRows int,
    @P_SortColum varchar(100)=null,
    @P_SortOrder varchar(10)=null,
    @P_SentByID  int=null,
    @P_From Datetime=null,
    @P_To Datetime=null,
    @P_Status varchar(50)=null,
    @P_Condition varchar(50)=null


AS
BEGIN
BEGIN TRAN

    if(@P_CampusId!=0)
    begin

    if(@P_Condition !='REPORT DATA')
    BEGIN
        select distinct TemplateId,TemplateName from Mail_Templates where CampusId=@P_CampusId
        select distinct case when SentStatus is null  then 'Pending' else SentStatus end as Status, isnull(Status,0) as StatusID from SendMail where CampusId=@P_CampusId
        select distinct US.UserId as SentByID,US.FirstName+' '+US.LastName as SentBy from SendMail SM inner join Users US on SM.CreatedBy=US.UserId where SM.CampusId=@P_CampusId
    END
    ELSE
    BEGIN

        Declare @RowsFrom int,
            @RowsTo int,
            @varQuery NVARCHAR(max)='',
            @varQuery1 NVARCHAR(max)='',
            @varQueryCount NVARCHAR(max)=''; --Use this variable for find out the total rows of searching record.

    If (@P_PageNo is not null and @P_PageRows is not null)
    Begin
        Set @RowsTo     = ((@P_PageNo) * @P_PageRows);
        Set @RowsFrom   = (((@P_PageNo) * @P_PageRows)+1) - @P_PageRows;
    End;

    Set @varQueryCount = 'SELECT Count(*) as TotalRowsCount From SendMail where CampusID='+cast(@P_CampusId AS varchar);


    Set @varQuery = 'Select * from  (select Row_Number() over (order by Logid Asc) as RowIndex ,LogId,TemplateName as MailType,MT.TemplateId as MailTypeID,Subject,Body,SM.Status as StatusID,case when SentStatus is null then ''Pending'' else SentStatus end as Status,SM.CreatedOn,SM.MailSentOn as SentOn,SM.CreatedBy as SentByID,US.FirstName+'' ''+US.LastName as SentBy from SendMail SM
    inner join Mail_Templates MT on MT.TemplateId=SM.TemplateId
    inner join Users US on US.UserId=SM.CreatedBy
    where SM.CampusId='+cast(@P_CampusId AS varchar);





    If (@P_MailTypeID is not null and @P_MailTypeID <> 0 and LEN(@P_MailTypeID) > 0)
    Begin
        Set @varQuery = @varQuery +' and MT.TemplateId='+ cast( @P_MailTypeID as varchar) ;
    End;

    If (@P_SentByID is not null and @P_SentByID <> 0 and LEN(@P_SentByID) > 0)
    Begin
        Set @varQuery = @varQuery +' and SM.CreatedBy = '+ cast (@P_SentByID as varchar);
    End;


    If (@P_From is not null and @P_From <> '')
    Begin
        Set @varQuery = @varQuery +' AND CONVERT(date,MailSentOn) >= '''+ CONVERT(varchar(10),@P_From,101) +''' ';
    End;


    If (@P_To is not null and @P_To <> '')
    Begin
        Set @varQuery = @varQuery +' AND CONVERT(date,MailSentOn) <= '''+ CONVERT(varchar(10),@P_To,101) +''' ';
    End;

    If (@P_Status is not null and @P_Status='Success' )
    Begin
        Set @varQuery = @varQuery +' AND SentStatus like ''%'+ cast( @P_Status as varchar)+'%''';
    End;
        If (@P_Status is not null and @P_Status='Pending')
    Begin
        Set @varQuery = @varQuery +' AND SentStatus is null';
    End;
        Set @varQuery = ''+ @varQuery +' '+ @varQuery1 +' ) as Sub WHERE Sub.RowIndex >= '+ cast(@RowsFrom as varchar) +'  and Sub.RowIndex <= '+ cast(@RowsTo as varchar) +'';



    --Set @varQueryCount = ''+ @varQueryCount +' '+ @varQuery;



    If (@P_SortColum is not null and @P_SortColum <> '')
    Begin
        Set @varQuery = ''+ @varQuery +' '+ ' Order by Sub.'+ @P_SortColum +' '+ @P_SortOrder + '';
    End;
    ELSE
    Begin
        Set @varQuery = ''+ @varQuery +' '+ ' Order by Sub.logid Desc ';
    End;

        truncate table temp
    insert into temp values(@varQueryCount)
    Exec ( @varQueryCount ); --find the record count
    Exec ( @varQuery); --display the record

    --select @varQuery;
    --truncate table temp
    insert into temp values(@varQuery)
    end
    END


    IF(@@ERROR = 0)
        COMMIT TRAN
    ELSE
        ROLLBACK TRAN
        RETURN (@@ERROR)
    END;