USE [UshareDB]
GO
/****** Object: StoredProcedure [dbo].[GetD_ProjectInfoCollectionForPagingJSSS] Script Date: 07/16/2014 18:19:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[GetD_ProjectInfoCollectionForPagingJSSS]
(
@PageIndex int, ----当前的页码
@PageSize int, ----每页显示多少条
@RecordCount int output,----总条数
@PageCount int output , ----总页数
@FileTypeID varchar(4000),-------文件类型编号
@ProjectState varchar(4000),----交易状态
@StrSeach varchar(4000),------搜索条件
----------排序标示: 0 无排序,1 升序 ,2 降序
@OrderNum char(1),--------按照数量排序的标示
@OrderMoney char(1),------按照金额排序的标示
@OrderCreate char(1),-----按照交易时间排序的标示
@OrderDiff char(1)----按照剩余时间排序的标示
)
as
begin
declare @JPWhere varchar(4000)----条件字符串
declare @JPBy varchar(4000) ----排序字符串
declare @val varchar(5000)----定义一个参数用来接收T-SQL语句
declare @Num char(4)------记录按照数量排序的标示
declare @Money char(4)----记录按照交易金额排序的标示
declare @Create char(4)---记录按照时间排序的标示
declare @Diff char(4)---记录按照剩余时间排序的标示
set @Create='desc' ---默认情况下是按照时间降序的
-----初次或者没有条件筛选,加载所有信息
IF(@FileTypeID='0' and @ProjectState='0' and (@StrSeach=''or @StrSeach is null ) and @OrderNum='0' and @OrderMoney='0' and @OrderCreate='0' and @OrderDiff='0')
begin
--set @WhereBy=' order by p.CreateTime desc '
set @JPWhere='and 1=1 '
set @JPBy=' order by p.CreateTime desc '
--print @WhereBy
end
-----有条件进行筛选
else
begin
set @JPWhere=''
set @JPBy=' order by p.CreateTime desc '
if(@FileTypeID!='0')--按照类别查询
begin
set @JPWhere=' and ProjectType ='+@FileTypeID
end
if(@ProjectState!='0')--按照状态查询
begin
set @JPWhere=@JPWhere+' and BusinessStatus='+@ProjectState
end
if(@StrSeach!='')---模糊查询
begin
set @JPWhere=@JPWhere+' and ProjectCode like '''+ @StrSeach+'%''' +' or ProjectName like'''+@StrSeach+'%'''
end
--set @WhereBy=@WhereBy+' order by '
if(@OrderNum='1')---按照数量排序
begin
set @JPBy=' order by im.ImageCount asc '
--set @Num='asc'
end
if(@OrderNum='2')
begin
set @JPBy=' order by im.ImageCount desc '
--set @Num='desc'
end
if(@OrderMoney='1')---按照金额排序
begin
set @JPBy=' order by BusinessAmount asc '
--set @Money='asc'
end
if(@OrderMoney='2')
begin
set @JPBy=' order by BusinessAmount desc '
--set @Money='desc'
end
if(@OrderCreate='1')---按照时间排序
begin
set @JPBy=' order by p.CreateTime asc'
--set @Create='asc'
end
if(@OrderCreate='2')
begin
set @JPBy=' order by p.CreateTime desc'
-- set @Create='desc'
end
if(@OrderDiff='1')---按照剩余时间排序
begin
set @JPBy=' order by DATEDIFF(HH,p.CreateTime,FinishDate) asc'
--set @Diff='asc'
end
if(@OrderDiff='2')
begin
set @JPBy=' order by DATEDIFF(HH,p.CreateTime,FinishDate) desc'
--set @Diff='desc'
end
--if(SUBSTRING(@WhereBy,LEN(@WhereBy)-1,2)='by')---没有排序,按照时间排序
--begin
--set @WhereBy =@WhereBy+' p.CreateTime desc '
--end
--if(SUBSTRING(@WhereBy,LEN(@WhereBy),1)=',')---最后一位是,去除,
--begin
--set @WhereBy=SUBSTRING(@WhereBy,1,len(@WhereBy)-1)
--end
end
----要执行的T-SQL语句,赋值给val,并把查询的数据集赋值给一个全局临时表##jptemp
--order by im.ImageCount ' +@Num+ ', BusinessAmount '+@Money+ ',p.CreateTime ' +@Create+ ' ,DATEDIFF(HH,p.CreateTime,FinishDate) ' +@Diff+ ')
set @val= 'select ROW_NUMBER() over(' +@JPBy+ ') rowNumber, ProjectCode,ProjectName,ProjectType,im.ImageCount,custor.CustomerName SellName,cust.CustomerName BuyName,BusinessAmount,p.CreateTime,DATEDIFF(HH,p.CreateTime,FinishDate) '
+' Diff,BusinessStatus,IsDeleted into ##jptemp from D_ProjectInfo p '
+' left join (select ProjectID,COUNT(*) ImageCount from D_ProjectImageInfo where IsDeleted=0 or IsDeleted is null group by ProjectID) im'
+' on p.ProjectID=im.ProjectID left join E_CustomerInfo custor on p.Seller=custor.CustomerID '
+' left join E_CustomerInfo cust on p.CreatorID=cust.CustomerID '
+' where (p.IsDeleted=0 or p.IsDeleted is null) ' + @JPWhere+@JPBy
-----执行这条拼接的字符串
exec(@val)
print (@val)
----执行过以后,会创建出来一张全局表##temp(里面包含所有的条件过滤后的数据)
----查询出来总共的记录数
select @RecordCount = COUNT(1) from ##jptemp
----查询出来总共的页数
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
----对查询的数据集进行分页
select jp.ProjectCode,jp.ProjectName,jp.BusinessAmount,jp.CreateTime,jp.BusinessStatus,jp.ProjectType,
CONVERT(nvarchar,isnull(jp.ImageCount,0))+';'+isnull(jp.SellName,'')+';'+isnull(jp.BuyName,0)+';'+Convert(nvarchar,isnull(jp.Diff,0)) Remark
from ##jptemp jp where jp.rowNumber between @PageIndex*@PageSize+1 and (@PageIndex+1)*@PageSize
----删除这张表
drop table ##jptemp
end