SQL语句

USE [FRD]
GO
/****** Object:  StoredProcedure [dbo].[PayMoneySearch]    Script Date: 07/15/2018 10:49:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[PayMoneySearch]
(@starttime datetime,--开始时间
@endtime datetime,--结束时间
@CusName nvarchar(500),--客户
@flag int,--是否显示应收款为0的客户(0不显示,1显示)
@currentpage int,--当前页
@pagesize int,--一页多少行
@allpage int output,--共多少页
@sumcount int output--共多少行
)
as
declare @allcount float 
create table #tb(
  Ispurchase INT ,

  cusId nvarchar(50), --供应商Id
  cusName nvarchar(500), --供应商名称
  InitializationAmount  decimal(18,2),--初始化欠款
  periodMoney decimal(18,2),--期初应收余额
  SumMoney decimal(18,2),--应付总额
  realmoney decimal(18,2),--供应商已付款
  YSBalance decimal(18,2),--应收余额
  YSMoney decimal(18,2),--应付总额
)

begin
insert  #tb 
SELECT MAX(Status) as Ispurchase,  MAX(ID) AS cusid, MAX(SupName) AS CusName, isnull ((select SUM(PayMoney) from Finance_PurchaseInitialMoney where CusID=CRM_SupplierInfo.ID),0) 
as 'InitializationAmount',
isnull(((SELECT SUM(SumMoney)  FROM [FRD].[dbo].[Purchase_CGOrder] where SupID=CRM_SupplierInfo.ID and DDSHStatue=0 and
 Pubdate<@starttime)-(SELECT SUM(PayMoney)  FROM [dbo].[Purchase_CGOrder] where SupID=CRM_SupplierInfo.ID and DDSHStatue=0  and 
 PayDate<@starttime)),0) as periodMoney,
 
 
isnull((SELECT SUM(JSNumber*UnitPrice2)  FROM [dbo].[VPurchase_Receipt]  where SupID=CRM_SupplierInfo.ID and RKStatue=1  and Pubdate>=@starttime and Pubdate<=@endtime),0) as summoney, 
 
isnull(((SELECT SUM(PayMoney)  FROM [dbo].[Purchase_CGOrder] where SupID=CRM_SupplierInfo.ID and DDSHStatue=0  and Pubdate>=@starttime and Pubdate<=@endtime)
+isnull((SELECT SUM(LenderMoney)  FROM [dbo].[FRD_AccountDetail] where SupId=CRM_SupplierInfo.ID  and OperationTime>=@starttime and OperationTime<=@endtime),0)
+isnull((SELECT SUM(Counterbalance)  FROM [dbo].Finance_CurrencyAccountDetail where SupId=CRM_SupplierInfo.ID  and OperationTime>=@starttime and OperationTime<=@endtime and OrderId='JSD'),0)),0)
 as realmoney
 , 0 as YSBalance,0 as YSMoney
FROM       CRM_SupplierInfo 
GROUP BY ID,SupName having SupName like'%'+@CusName+'%'



--修改应收余额与应收总额

update #tb set YSBalance=SumMoney-realmoney,YSMoney=InitializationAmount+periodMoney+(SumMoney-realmoney)
if(@flag=1)
begin

 select top (@pagesize) *
  from #tb where  YSMoney<>0 and cusId not in
 (select top (@pagesize*(@currentpage-1)) cusId from  #tb where YSMoney<>0 order by cusId )
 order by cusId
 --取总行数
select @allcount=COUNT(0) from #tb where YSMoney<>0
print @allcount
end
else
begin
 select top (@pagesize) *
  from #tb where cusId not in
 (select top (@pagesize*(@currentpage-1)) cusId from  #tb  order by cusName )
 order by cusName
 --取总行数
select @allcount=COUNT(0) from #tb 
end
set @sumcount=@allcount
--计算共多少页
set @allpage=ceiling((@allcount/@pagesize))

end