create PROCEDURE [dbo].[nb_arrearageListByCar]
@pageIndex INT, --第几页
@pageSize INT, --分页大小
@carNumber NVARCHAR(6), --车牌号
@Rstatus INT OUTPUT, --返回标识号 0 成功
@Rcode INT OUTPUT, --返回错误编码
@Rmsg VARCHAR(128) OUTPUT --返回描述状态说明标识
AS
declare @ret int --临时参数
DECLARE @SQL VARCHAR(MAX)
begin
begin try
-->step 1 | 空值检测 |
if(@pageIndex is null or 0 = @pageIndex or @pageSize is null or 0 = @pageSize )
begin
set @Rstatus = 1
set @Rcode = 4000
set @Rmsg = '参数空'
select -1 rn,'' code,'' startDate ,-1 reMoney, -1 lastMoney
return
end
--条件筛选
declare @sql_where nvarchar(max)
declare @start1 nvarchar(16)
declare @end1 nvarchar(16)
set @sql_where = ''
set @start1 = ''
set @end1 = ''
set @start1 = cast((@pageSize * (@pageIndex-1) + 1) as varchar(16))
set @end1 = cast(@pageSize* @pageIndex as varchar(16))
if('' != @carNumber and @carNumber is not null)
begin
set @sql_where = @sql_where + N' and b.code = '''+ @carNumber +''' '
end
-->step 2 | 分页数据 |
declare @count1 int
set @count1 = 0
declare @t_sql nvarchar(max)
set @t_sql = N'select @c = count(1)
from (
select a.parkOrderID,sum(isnull(c.orderMoney,0)) reMoney
from parkOrder a left join parkSpace b on b.spaceID = a.spaceID
left join orderPayBank c on c.Orderid = a.parkOrderID
where a.totalMoney - a.reMoney > 0 and a.spaceStatus = 2 ' + @sql_where + '
group by a.parkOrderID) a
left join parkOrder b on b.parkOrderID = a.parkOrderID
left join parkSpace c on c.spaceID = b.spaceID
where 1 = 1'
exec sp_executesql @t_sql,N'@c int output', @count1 output
set @t_sql = ''
if(0 = @count1)
begin
set @Rstatus = 1
set @Rcode = 0
set @Rmsg = '没有欠款记录'
select -1 rn,'' code,'' startDate ,-1 reMoney, -1 lastMoney
return
end
else
begin
set @Rcode = @count1
set @t_sql = N'select * from (
select ROW_NUMBER() over (order by c.code) rn,
b.parkOrderID,c.code,convert(char(20),b.startDate,120) startDate,convert(char(20),b.endDate,120) endDate,LTRIM(RTRIM(b.carNumber)) carNumber,
DATEDIFF(MINUTE,b.startDate,b.endDate) howlong,a.reMoney,
b.totalMoney - a.reMoney lastMoney
from (
select a.parkOrderID,sum(isnull(c.orderMoney,0)) reMoney
from parkOrder a left join parkSpace b on b.spaceID = a.spaceID
left join orderPayBank c on c.Orderid = a.parkOrderID
where a.totalMoney - a.reMoney > 0 and a.spaceStatus = 2 ' + @sql_where + '
group by a.parkOrderID) a
left join parkOrder b on b.parkOrderID = a.parkOrderID
left join parkSpace c on c.spaceID = b.spaceID
where 1 = 1
) Ch where Ch.rn between ' + @start1 + ' and ' + @end1
exec sp_executesql @t_sql
set @Rstatus = 0
set @Rmsg = '获取成功'
end
end try
begin catch
set @Rstatus = -1
set @Rcode = -1
set @Rmsg = ERROR_MESSAGE()
insert into parkErr(proName,proTime,proErr)values('[nb_arrearageList]',GETDATE(),ERROR_MESSAGE())
end catch
end