/**
 * 根据符合条件查询文章数据
 @vType 视图类别,1为大类文章视图vNewsMST,2为专题文章视图vSpecMST
 @SortID 
 @SpecID 
 @GroupsID 
 @NewsTitle 
 @CreateDateBegin 
 @CreateDateEnd 
 */
ALTER PROCEDURE [dbo].[NewsListSelectByCondition]
    (
    @vType int,
    @SortID varchar(12),
    @SpecID varchar(12),
    @GroupsID varchar(12),
    @NewsTitle varchar(120),
    @CreateDateBegin datetime,
    @CreateDateEnd datetime,
    @PageSize int,
    @CurrentPage int output,
    @TotalPage int output,
    @TotalRecord int output,
    @SortBy varchar(100)
    )
AS

--把所有单引号都替换成两个单引号
SET @SortID = REPLACE(LTRIM(RTRIM(@SortID)),'''','''''')
SET @SpecID = REPLACE(LTRIM(RTRIM(@SpecID)),'''','''''')
SET @GroupsID = REPLACE(LTRIM(RTRIM(@GroupsID)),'''','''''')
SET @NewsTitle = REPLACE(LTRIM(RTRIM(@NewsTitle)),'''','''''')
SET @SortBy = REPLACE(LTRIM(RTRIM(@SortBy)),'''','''''')
IF @SortBy=''
BEGIN
    SET @SortBy='NewsID DESC'
END

DECLARE @Sql NVARCHAR(2000)
DECLARE @SqlCount NVARCHAR(2000)
DECLARE @WhereClause NVARCHAR(2000)
--判断是专题文章还是系统文章,1为系统文章,2为专题文章
IF @vType ='1'
BEGIN
SET @Sql = 'SELECT *,ROW_NUMBER() OVER (ORDER BY ' + @SortBy + ') AS RowID FROM [dbo].[vNewsMST] '
SET @SqlCount = 'SELECT @Count=count(*) FROM [dbo].[vNewsMST] '
END
ELSE IF @vType ='2'
BEGIN
SET @Sql = 'SELECT *,ROW_NUMBER() OVER (ORDER BY ' + @SortBy + ') AS RowID FROM [dbo].[vSpecMST] '
SET @SqlCount = 'SELECT @Count=count(*) FROM [dbo].[vSpecMST] '
END

SET @WhereClause = 'WHERE DeleteFlag=0 '
IF RTrim(@SortID)<>''
    SET @WhereClause = @WhereClause + ' AND SortID=''' + @SortID + ''''
    SET @WhereClause = @WhereClause + ' OR SortID LIKE ''' + @SortID + '%'''
IF RTrim(@SpecID)<>''
    SET @WhereClause = @WhereClause + ' AND SpecID=''' + @SpecID + ''''
IF RTrim(@GroupsID)<>''
    SET @WhereClause = @WhereClause + ' AND GroupsID=''' + @GroupsID + ''''
[color=#FF0000]IF RTrim(@NewsTitle)<>''
    SET @WhereClause = @WhereClause + ' AND NewsTitle  LIKE ''%' + @NewsTitle + '%'''[/color]
IF RTrim(@CreateDateBegin)<>Cast('1900-1-1' AS DateTime)
    SET @WhereClause = @WhereClause + ' AND AddDate>=''' + Cast(@CreateDateBegin AS VARCHAR(30)) + ''''
IF RTrim(@CreateDateEnd)<>Cast('1900-1-1' AS DateTime)
    SET @WhereClause = @WhereClause + ' AND AddDate<=''' + Cast(@CreateDateEnd AS VARCHAR(30)) + ''''

SET @SqlCount = @SqlCount + @WhereClause
DECLARE @ParmDefinition nvarchar(100)
SET @ParmDefinition = N'@Count int OUTPUT';
EXECUTE sp_executesql @SqlCount, @ParmDefinition, @Count=@TotalRecord OUTPUT;
SET @TotalPage=CEILING(CAST(@TotalRecord AS DECIMAL)/@PageSize)

IF @CurrentPage > @TotalPage-1
    SET @CurrentPage=@TotalPage-1
IF @CurrentPage < 0
    SET @CurrentPage=0

DECLARE @_Start INT, @_End INT
SET @_Start = ((@CurrentPage * @PageSize) + 1)
SET @_End = (@_Start + @PageSize - 1)

SET @Sql = 'SELECT * FROM (' + @Sql + @WhereClause + ') AS TempTable WHERE (RowID >= ' + CAST(@_Start AS VARCHAR(10)) + ') AND (RowID <= ' + CAST(@_End AS VARCHAR(10)) + ')'
EXEC sp_executesql @Sql

IF RTrim(@SortID)<>''
    SET @WhereClause = @WhereClause + ' AND SortID=''' + @SortID + ''''
    SET @WhereClause = @WhereClause + ' OR SortID LIKE ''' + @SortID + '%'''