ALTER proc [dbo].[GetD_OrderInfoCollectionForPaging]
(
@PageIndex int, ----当前的页码
@PageSize int, ----每页显示多少条
@RecordCount int output,----总条数
@PageCount int output , ----总页数
@TransactionType nvarchar(4000),-------交易类型编号
@BusinessStatus nvarchar(4000),----交易状态
@StrSeach nvarchar(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 @JPByOut varchar(4000)----排序字符串(外层的)
declare @val nvarchar(4000)----定义一个参数用来接收T-SQL语句
declare @sql nvarchar(4000)----定义一个参数用来接收T-SQL语句
-----初次或者没有条件筛选,加载所有信息
IF(@TransactionType='0' and @BusinessStatus='0' and (@StrSeach=''or @StrSeach is null ) and @OrderNum='0' and @OrderMoney='0' and @OrderCreate='0' and @OrderDiff='0')
begin
set @JPWhere='where 1=1 '
set @JPBy=' order by o.CreateTime desc '
set @JPByOut=' order by t.CreateTime desc '
end
-----有条件进行筛选
else
begin
set @JPWhere='Where 1=1 '
set @JPBy='order by o.CreateTime desc '
set @JPByOut=' order by t.CreateTime desc '
if(@TransactionType!='0')--按照类别查询
begin
set @JPWhere= @JPWhere+ ' and o.TransactionType ='+@TransactionType
end
if(@BusinessStatus!='0')--按照状态查询
begin
set @JPWhere=@JPWhere+' and o.BusinessStatus='+@BusinessStatus
end
if(@StrSeach!='')---模糊查询
begin
set @JPWhere=@JPWhere+' and o.OrderCode like '''+ @StrSeach+'%''' +' or p.ProjectName like '''+@StrSeach+'%'''
end
if(@OrderNum='1')---按照数量排序
begin
set @JPBy=' order by im.num asc '
set @JPByOut=' order by t.num asc '
end
if(@OrderNum='2')
begin
set @JPBy=' order by im.num desc '
set @JPByOut=' order by t.num desc '
end
if(@OrderMoney='1')---按照金额排序
begin
set @JPBy=' order by o.BusinessAmount asc '
set @JPByOut=' order by t.BusinessAmount asc '
end
if(@OrderMoney='2')
begin
set @JPBy=' order by o.BusinessAmount desc '
set @JPByOut=' order by t.BusinessAmount desc '
end
if(@OrderCreate='1')---按照时间排序
begin
set @JPBy=' order by o.CreateTime asc '
set @JPByOut=' order by t.CreateTime asc '
end
if(@OrderCreate='2')
begin
set @JPBy=' order by o.CreateTime desc '
set @JPByOut=' order by t.CreateTime desc '
end
if(@OrderDiff='1')---按照剩余时间排序
begin
set @JPBy=' order by DATEDIFF(HH,o.CreateTime,o.FinishDate) asc'
set @JPByOut=' order by t.Diff asc '
end
if(@OrderDiff='2')
begin
set @JPBy=' order by DATEDIFF(HH,o.CreateTime,o.FinishDate) desc'
set @JPByOut=' order by t.Diff desc '
end
end
set @val='select @count=count(1) from( select ROW_NUMBER() over(' +@JPBy+ ') rowNumber, o.OrderCode,p.ProjectName,o.TransactionType,im.num,c.CustomerName sellerName,e.CustomerName buyName,o.BusinessAmount,'
+' o.CreateTime,DATEDIFF(HH,o.CreateTime,o.FinishDate) Diff,o.BusinessStatus'
+' from D_OrderInfo o'
+' left join D_ProjectInfo p on p.ProjectID=o.ProjectID'
+' left join (select i.ProjectID,COUNT(*) num from D_ProjectImageInfo i where i.IsDeleted=0 or i.IsDeleted is null group by i.ProjectID) im on im.ProjectID=o.ProjectID'
+' left join E_CustomerInfo c on c.CustomerID=o.Seller'
+' left join E_CustomerInfo e on e.CustomerID=o.CreatorID '+@JPWhere+' ) t'
-----执行这条拼接的字符串
--exec(@val)
--print (@val)
----执行过以后,会创建出来一张全局表##temp(里面包含所有的条件过滤后的数据)
----查询出来总共的记录数
--select @RecordCount = COUNT(1) from ##tablejp
EXEC sp_executesql @val, N'@count int output',@RecordCount OUTPUT
----查询出来总共的页数
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
----对查询的数据集进行分页
set @sql='select OrderCode,TransactionType,BusinessAmount,
CreateTime,BusinessStatus,Convert(nvarchar,isnull(num,0))+'';''+isnull(sellerName,'''')+'';''+isnull(buyName,'''')+'';''+Convert(nvarchar,isnull(Diff,0))+'';''+isnull(ProjectName,'''') Remark
from (select ROW_NUMBER() over(' +@JPBy+ ') rowNumber, o.OrderCode,p.ProjectName,o.TransactionType,im.num,c.CustomerName sellerName,e.CustomerName buyName,o.BusinessAmount,
o.CreateTime,DATEDIFF(HH,o.CreateTime,o.FinishDate) Diff,o.BusinessStatus
from D_OrderInfo o
left join D_ProjectInfo p on p.ProjectID=o.ProjectID
left join (select i.ProjectID,COUNT(*) num from D_ProjectImageInfo i where i.IsDeleted=0 or i.IsDeleted is null group by i.ProjectID) im on im.ProjectID=o.ProjectID
left join E_CustomerInfo c on c.CustomerID=o.Seller
left join E_CustomerInfo e on e.CustomerID=o.CreatorID '+@JPWhere+') t where rowNumber between ' + STR(@PageIndex * @PageSize + 1) + ' and ('
+ STR(@PageIndex + 1) + ') * ' + STR(@PageSize) + ' '+@JPByOut
print @sql
EXEC sp_executesql @sql
end