带有分页的存储过程

-- =============================================

-- =============================================
CREATE PROCEDURE [control].[mp_GetSellerTerminalList_Temp]
@Svalidtime DATETIME, /*验证开始时间*/
@Evalidtime DATETIME, /*验证结束时间*/
@CurrentPage int, /*当前页数*/
@pageSize int, /*每页记录数*/
@UseStatus int,

@ProvinceNo VARCHAR(100), /**/
@CityNo varchar(30),
@DistributorName nvarchar(100),
@OrderType int,
@MobileNo nvarchar(100),

@SellerNO nvarchar(100),
@OrderNo nvarchar(100),
@StrSrcBatchNo nvarchar(100), /*交易流水*/
@GoodsName nvarchar(100),
@DeviceID nvarchar(100),

@EnterpriseName nvarchar(100),
@VoucherId nvarchar(100),
--加外部订单号
@ExOrderNo nvarchar(100)
AS
BEGIN
DECLARE @Where VARCHAR(MAX)
SET @Where=' DistributorName<>'''' '

--过滤主表中的数据
DECLARE @sqlC VARCHAR(MAX)
DECLARE @whereC VARCHAR(MAX)
SET @whereC = ' 1=1 '
IF @Svalidtime IS NOT NULL
BEGIN
SET @whereC =@whereC+ ' and convert(varchar(10),ValidTime,120)>='''+convert(varchar(10),@Svalidtime,120)+''''
END
IF @Evalidtime IS NOT NULL
BEGIN
SET @whereC =@whereC+ ' and convert(varchar(10),ValidTime,120)<='''+convert(varchar(10),@Evalidtime,120)+''''
END
IF @GoodsName IS NOT NULL AND @GoodsName != ''
BEGIN
SET @whereC =@whereC+' and GoodsName like ''%'+@GoodsName+'%'''
END
IF @DeviceID IS NOT NULL AND @DeviceID != ''
BEGIN
SET @whereC =@whereC+' and DeviceID like ''%'+@DeviceID+'%'''
END
IF @EnterpriseName IS NOT NULL AND @EnterpriseName != ''
BEGIN
SET @whereC =@whereC+' and EnterpriseName like ''%'+@EnterpriseName+'%'''
END
IF @VoucherId IS NOT NULL AND @VoucherId != ''
BEGIN
SET @whereC =@whereC+' and VoucherId = '''+@VoucherId+''''
END
IF @SellerNO IS NOT NULL AND @SellerNO !=''
BEGIN
SET @whereC =@whereC+' and SellerNo='''+@SellerNO+''''
END
IF @OrderType IS NOT NULL AND @OrderType !=-1
BEGIN
SET @whereC =@whereC+' and OrderType='''+cast(@OrderType AS VARCHAR)+''''
END
IF @OrderNo IS NOT NULL AND @OrderNo !=''
BEGIN
SET @whereC =@whereC+' and OrderNo='''+@OrderNo+''''
END
IF @StrSrcBatchNo IS NOT NULL AND @StrSrcBatchNo!=''
BEGIN
SET @whereC =@whereC+' and BatchNo+TradeNo = '''+@StrSrcBatchNo+''''
END
PRINT @whereC

--过滤出主表数据到临时表中

IF object_id('tempdb..#tbVoucherValidLog') is NOT NULL
BEGIN
DROP TABLE #tbVoucherValidLog
END

CREATE TABLE #tbVoucherValidLog
(
LogID BIGINT,
VoucherType TINYINT,
BatchNo VARCHAR(10),
TradeNo VARCHAR(10),
SrcBatchNo VARCHAR(10),
SrcTradeNo VARCHAR(10),
ConsumeAmount DECIMAL(8,2),
GoodsName VARCHAR(50),
SettlementPrice MONEY,
OrderNo VARCHAR(50),
VoucherId VARCHAR(50),
SellerNo VARCHAR(20),
GoodsID BIGINT,
GoodsAmount INT,
StoreName VARCHAR(50),
ValidTime DATETIME,
DeviceID VARCHAR(50),
VoucherNo VARCHAR(50),
ValidateType TINYINT,
SettlementAllPrice MONEY,
OrderType TINYINT,
EnterpriseNo VARCHAR(50),
EnterpriseName VARCHAR(50),
IsReSet BIT
)


EXEC('insert into #tbVoucherValidLog
select LogID,VoucherType,BatchNo,TradeNo,SrcBatchNo,SrcTradeNo,ConsumeAmount,GoodsName,SettlementPrice,OrderNo,VoucherId,SellerNo,GoodsID,GoodsAmount,StoreName,
ValidTime,DeviceID,VoucherNo,ValidateType,SettlementAllPrice,OrderType,EnterpriseNo,EnterpriseName,IsReSet
from control.tb_VoucherValidLog where '+@whereC)

PRINT '1.Insert tbVoucherValidLog '+cast(GETDATE() AS VARCHAR)

--control.tb_Voucher
DECLARE @whereB VARCHAR(MAX)
SET @whereB = ' UseStatus<>5 and exists (select VoucherId from #tbVoucherValidLog b where b.VoucherId = a.VoucherId) '
PRINT @whereB
IF @UseStatus IS NOT NULL AND @UseStatus !=-1
BEGIN
SET @whereB =@whereB+' and UseStatus='+CAST(@UseStatus AS VARCHAR)
PRINT @whereB
SET @Where = @Where+' and UseStatus is not null'
END
IF @MobileNo IS NOT NULL AND @MobileNo !=''
BEGIN
SET @whereB =@whereB+' and MobileNo='''+@MobileNo+''''
SET @Where = @Where+' and MobileNo is not null'
END
PRINT @whereB
IF object_id('tempdb..#tbVoucher') is NOT NULL
BEGIN
DROP TABLE #tbVoucher
END

CREATE TABLE #tbVoucher
(
OrderType TINYINT,
CustomerName VARCHAR(50),
VoucherId VARCHAR(50),
VoucherType TINYINT,
MobileNo VARCHAR(20),
AllPointAmout DECIMAL(8,2),
UseStatus TINYINT,
ID BIGINT,
RePrintTimes INT,
VoucherNo VARCHAR(50)
)

EXEC('insert into #tbVoucher
select OrderType,CustomerName,VoucherId,VoucherType,MobileNo,AllPointAmout,UseStatus,ID,RePrintTimes,VoucherNo
from control.tb_Voucher a where '+@whereB)
PRINT '2.Insert tbVoucher '+cast(GETDATE() AS VARCHAR)

--control.tb_VoucherDetails
DECLARE @whereA VARCHAR(MAX)
SET @whereA = ' exists (select VoucherNo from #tbVoucherValidLog b where b.VoucherNo = a.VoucherNo) '

IF @DistributorName IS NOT NULL AND @DistributorName !=''
BEGIN
SET @DistributorName =@DistributorName+' and DistributorName='''+@DistributorName+''''
END
PRINT @whereA
IF object_id('tempdb..#tbVoucherDetails') is NOT NULL
BEGIN
DROP TABLE #tbVoucherDetails
END

CREATE TABLE #tbVoucherDetails
(
DistributorName VARCHAR(50),
SinglePrice MONEY,
VoucherNo VARCHAR(50),
GoodsID BIGINT,
OrderNo VARCHAR(50)
)

EXEC('insert into #tbVoucherDetails
select distinct DistributorName,SinglePrice,VoucherNo,GoodsID,OrderNo
from control.tb_VoucherDetails a where '+@whereA)
PRINT '3.Insert tbVoucherDetails '+cast(GETDATE() AS VARCHAR)
--control.tb_Order
DECLARE @whereO VARCHAR(MAX)
SET @whereO = ' exists (select OrderNo from #tbVoucherValidLog b where b.OrderNo = a.OrderNo)'
IF object_id('tempdb..#tbOrder') is NOT NULL
BEGIN
DROP TABLE #tbOrder
END

CREATE TABLE #tbOrder
(
OrderNo VARCHAR(50),
ExOrderNo VARCHAR(50)
)

EXEC('insert into #tbOrder select OrderNo,ExOrderNo from control.tb_Order a where '+@whereO)
PRINT '4.Insert tbOrder '+cast(GETDATE() AS VARCHAR)
--control.tb_MovieCard
-- DECLARE @whereM VARCHAR(MAX)
-- SET @whereM= ' exists (select VoucherNo from #tbVoucherValidLog b where b.VoucherNo = a.CardNo)'
-- IF object_id('tempdb..##tbMovieCard') is NOT NULL
--BEGIN
--DROP TABLE ##tbMovieCard
--END
--EXEC('select * into ##tbMovieCard from control.tb_MovieCard a where '+@whereM)
--PRINT '5.Insert tbMovieCard '+cast(GETDATE() AS VARCHAR)
--control.tb_CustomerOrder
DECLARE @whereCo VARCHAR(MAX)
SET @whereCo =' exists (select OrderNo from #tbVoucherValidLog b where b.OrderNo = a.OrderNo)'
IF object_id('tempdb..#tbCustomerOrder') is NOT NULL
BEGIN
DROP TABLE #tbCustomerOrder
END

CREATE TABLE #tbCustomerOrder
(
OrderNo VARCHAR(50),
CustomerName VARCHAR(50),
SinglePrice MONEY
)

EXEC('insert into #tbCustomerOrder
select OrderNo,CustomerName,SinglePrice
from control.tb_CustomerOrder a where '+@whereCo)
PRINT '6.Insert tbCustomerOrder '+cast(GETDATE() AS VARCHAR)

--
IF @CityNo IS NOT NULL AND @CityNo!=''
BEGIN
SET @Where = @Where + ' and CityNo='''+@CityNo+''''
END
IF @ExOrderNo IS NOT NULL AND @ExOrderNo !=''
BEGIN
SET @Where = @Where + ' and ExOrderNo='''+@ExOrderNo+''''
END

----------------------------------------------------------------------------------------------
CREATE TABLE #tb_GetSellerTerminalListTable
(
LogID INT,
DistributorName NVARCHAR(100),
OrderType1 NVARCHAR(20),
VoucherType1 NVARCHAR(20),
VoucherId1 NVARCHAR(30),
MobileNo NVARCHAR(30),
StrSrcBatchNo NVARCHAR(30),
StrSrcTradeNo NVARCHAR(30),
SinglePrice NVARCHAR(50),
TolPrice NVARCHAR(50),
GoodsName NVARCHAR(50),
SettlementPrice NVARCHAR(50),
OrderNo NVARCHAR(50),
UseStatus NVARCHAR(20),
VoucherId NVARCHAR(50),
ExOrderNo NVARCHAR(50),
SellerNo NVARCHAR(30),
GoodsAmount NVARCHAR(20),
StoreName NVARCHAR(40),
ValidTime DATETIME,
DeviceID NVARCHAR(30),
VoucherNo NVARCHAR(30),
SettlementAllPrice NVARCHAR(30),
GoodsNum1 NVARCHAR(30),
OrderType NVARCHAR(20),
EnterpriseName NVARCHAR(40),
VoucherType NVARCHAR(20),
ValidateType1 NVARCHAR(20),
ValidateType NVARCHAR(20),
ID NVARCHAR(30),
ValidNo NVARCHAR(20),
RePrintCount NVARCHAR(20),
GoodsNum NVARCHAR(30),
TradeNo NVARCHAR(20),
ProvinceNo NVARCHAR(20),
CityNo NVARCHAR(20),
AreaNo NVARCHAR(20),
AreaName NVARCHAR(40),
IsReset NVARCHAR(10)
)
CREATE INDEX index_validTime ON #tb_GetSellerTerminalListTable(OrderNo);


INSERT INTO #tb_GetSellerTerminalListTable
SELECT C.LogID,
CASE WHEN B.OrderType IN(4,5) AND C.VoucherType<>61 THEN B.CustomerName
WHEN C.VoucherType=61 THEN K.CustomerName
ELSE A.DistributorName
END AS DistrbutorName,
D.[name] as OrderType1,
F.[name] as VoucherType1,
CASE WHEN LEN(C.VoucherId)>0 THEN replace(C.VoucherId,substring(C.VoucherId,4,len(C.VoucherId)-7),'*********')
ELSE '' END as VoucherId1,
(case when C.VoucherType=53 then replace(B.MobileNo,substring(B.MobileNo,4,len(B.MobileNo)-7),'*********')
WHEN C.VoucherType =61 THEN '13888888888'
else B.MobileNo end) as MobileNo,
C.BatchNo+C.TradeNo AS StrSrcBatchNo,
C.SrcBatchNo+C.SrcTradeNo AS StrSrcTradeNo,
Convert(decimal(18,2),
(CASE
WHEN B.VoucherType = 54 OR B.VoucherType = 60 THEN CASE
WHEN B.AllPointAmout
= 0 THEN
0
ELSE A.SinglePrice
/ B.AllPointAmout
END
WHEN C.VoucherType=61 THEN K.SinglePrice
ELSE A.SinglePrice
END)) AS SinglePrice,

Convert(decimal(18,2),
(case when C.ValidateType=2 then -(
(CASE
WHEN B.VoucherType = 54 OR B.VoucherType = 60 THEN CASE
WHEN B.AllPointAmout
= 0 THEN
0
ELSE C.ConsumeAmount
* (A.SinglePrice / B.AllPointAmout)
END
ELSE C.ConsumeAmount * A.SinglePrice END))
ELSE
(CASE
WHEN B.VoucherType = 54 OR B.VoucherType = 60 THEN CASE
WHEN B.AllPointAmout
= 0 THEN
0
ELSE C.ConsumeAmount
* (A.SinglePrice / B.AllPointAmout)
END
WHEN C.VoucherType = 61 THEN C.ConsumeAmount* K.SinglePrice
ELSE C.ConsumeAmount * A.SinglePrice
END)END)) AS TolPrice,

C.GoodsName,C.SettlementPrice,
C.OrderNo,
CASE WHEN C.VoucherType=61 THEN 3 ELSE B.UseStatus END UseStatus,
C.VoucherId AS VoucherId,M.ExOrderNo,C.SellerNo,C.GoodsAmount,C.StoreName,
C.ValidTime,C.DeviceID,C.VoucherNo,
(
CASE
WHEN C.ValidateType = 2 THEN -(C.SettlementAllPrice)
ELSE C.SettlementAllPrice
END
) AS SettlementAllPrice,
(
CASE
WHEN B.VoucherType = 55 THEN CONVERT(NVARCHAR(30), C.GoodsAmount)
WHEN (
B.VoucherType = 53
OR B.VoucherType = 54
OR B.VoucherType = 60
OR C.VoucherType = 61
) THEN CONVERT(NVARCHAR(30), C.ConsumeAmount)
WHEN (B.VoucherType = 51 OR B.VoucherType = 52) THEN CONVERT(NVARCHAR(30), C.GoodsAmount)
END
) AS GoodsNum1,
C.OrderType,C.EnterpriseName,C.VoucherType,E.[name] AS ValidateType1,C.ValidateType,
CASE WHEN C.VoucherType=61 THEN 1 ELSE B.ID END ID,C.TradeNo as ValidNo,
CASE WHEN C.VoucherType=61 THEN 0 ELSE B.RePrintTimes END AS RePrintCount,
(case when B.VoucherType=55 then convert(nvarchar(30),C.GoodsAmount)+' 份' when (B.VoucherType=53 or B.VoucherType=54 or B.VoucherType=60)
then convert(nvarchar(30),C.ConsumeAmount)+' 点' when (B.VoucherType=51 or B.VoucherType=52)
then convert(nvarchar(30),C.GoodsAmount)+' 张' when C.VoucherType=61 then convert(nvarchar(30),C.GoodsAmount)+' 次' end) as GoodsNum ,C.TradeNo,
G.ProvinceNo,G.CityNo,G.AreaNo,I.AreaName,C.IsReSet
FROM
#tbVoucherValidLog AS C
LEFT JOIN
#tbVoucher AS B ON C.VoucherId = B.VoucherId AND C.VoucherNo = B.VoucherNo
LEFT JOIN
#tbVoucherDetails AS A ON C.VoucherNo = A.VoucherNo AND C.GoodsID = A.GoodsID
--LEFT JOIN
--##tbMovieCard AS J ON C.VoucherNo = J.CardNo
LEFT JOIN
#tbCustomerOrder AS K ON C.OrderNo = K.OrderNo
LEFT JOIN base.tb_DictionaryData AS D ON D.dicId = 1086 AND d.[value] = C.OrderType
LEFT JOIN base.tb_DictionaryData AS E ON E.dicId = 1185 AND E.[value] = C.ValidateType
LEFT JOIN base.tb_DictionaryData AS F ON F.dicId = 1119 AND F.[value] = C.VoucherType
LEFT JOIN control.tb_Cinema AS H ON H.CinemaNo = C.EnterpriseNo
LEFT JOIN control.tb_Organization AS G ON G.OrganizationNo = H.OrganizationNo
LEFT JOIN #tbOrder AS M ON M.OrderNo = A.OrderNo
LEFT JOIN base.tb_Area AS I ON G.CityNo = I.AreaNo

PRINT @Where

---实现分页返回数据

DECLARE @sqlText VARCHAR(MAX)
declare @Sp nvarchar(100);
declare @Ep nvarchar(100);
set @Sp=((@CurrentPage-1)*@PageSize)+1;
set @Ep=@CurrentPage*@PageSize;

set @sqlText='select * from (select *, ROW_NUMBER() OVER(Order by ValidTime DESC) AS RowNumber from #tb_GetSellerTerminalListTable as a where '+@Where+') as b where ';
set @sqlText=@sqlText+' RowNumber BETWEEN '+@Sp+' and '+@Ep;

EXEC(@SqlText)

--统计总金额总是
declare @TongJISum nvarchar(300);

set @TongJISum='select''合计'' as total, sum(Convert(decimal(18,2),SettlementAllPrice)) as AllSettlementAllPrice from #tb_GetSellerTerminalListTable where '+@Where;

EXEC(@TongJISum);

declare @conutT nvarchar(500);
set @conutT='select count(*) as PageCount from #tb_GetSellerTerminalListTable where '+@Where;
EXEC(@conutT)


DROP TABLE #tbVoucherValidLog
DROP TABLE #tbVoucher
DROP TABLE #tbVoucherDetails
DROP TABLE #tbCustomerOrder
DROP TABLE #tbOrder
END