某财务报表
USE [PB_AHTC]
GO
/****** Object: StoredProcedure [dbo].[JSPRO] Script Date: 12/10/2013 11:54:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[JSPRO]
AS
BEGIN
DECLARE @B_CODE INT --商户代码
DECLARE @B_JSTIME INT --商户结算时间
DECLARE @B_LastJSTIME DATETIME --商户结算时间
DECLARE @B_JSTIME2 INT --商户结算时间
DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT B_CODE, B_JSTIME,B_LastJSTIME FROM dbo.Z_BUSINESS) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
fetch next from My_Cursor into @B_CODE,@B_JSTIME,@B_LastJSTIME --填充变量
WHILE @@FETCH_STATUS = 0
BEGIN
set @B_JSTIME2= DATEDIFF(DAY,@B_LastJSTIME,GETDATE())
IF @B_JSTIME2>=@B_JSTIME --是否到结算期（当前时间-最后结算时间〉= XX天一结）
BEGIN
begin transaction --事物
declare @errorSum int --定义局部变量
set @errorSum=0 --初始化临时变量
--查询要结算的面值卡 插入到结算主表
Insert into Z_SHJZ(Z_B_CODE,Z_B_NAME,KHXfJE,KFJE,XJZFJE,khyh,JLZFSH,SHZFJL,jlyl, KHJF,ICBC,LASTJSTIME,JSTIME,CARDTYPE)
SELECT B.B_CODE ,B.B_NAME , SUM(l_je22)KHXFJE ,SUM(l_je4)KFJE,SUM(l_je3)XJZFJE,null khyh,SUM(l_je22*(1-l_hl1))JLZF,0.00 SJZF ,SUM(l_je44)jlyl,0.00 KHJF ,''ICBC,
B.B_LASTJSTIME ,GETDATE() AS JSTIME, 2 AS CARDTYPE
FROM dbo.Z_LS A,Z_BUSINESS B WHERE A.B_CODE =@B_CODE and A.B_CODE=b.B_CODE AND M_TIME<=GETDATE() AND L_JS!=1 AND ((B_JUD = 2) OR (B_JUD = 3))and len(C_KH)=9
GROUP BY B.B_CODE,B.B_NAME,B.B_LASTJSTIME
set @errorSum=@errorSum+@@error --累计是否有错误
--跟新流水表面值卡标记为已结算
UPDATE Z_LS SET L_JS=1 WHERE B_CODE =@B_CODE AND M_TIME<=GETDATE() AND L_JS!=1 AND ((B_JUD = 2) OR (B_JUD = 3))and len(C_KH)=9
set @errorSum=@errorSum+@@error --累计是否有错误
--查询要结算的VIP卡 插入到结算主表
Insert into Z_SHJZ(Z_B_CODE,Z_B_NAME,KHXfJE,KFJE,XJZFJE,khyh,JLZFSH,SHZFJL,jlyl,khjf,ICBC,LASTJSTIME,JSTIME,CARDTYPE)
select b.B_CODE,b.B_NAME, SUM(l_je22)KHXFJE ,SUM(l_je4)KFJE, SUM(l_je3)XJZFJE ,SUM(L_JE22* L_HL2)khyh,
SUM( CASE when [L_JE4]- [L_JE4] *(B_HL3-B_HL2) >=0 then [L_JE4]- [L_JE4] *(B_HL3-B_HL2) else 0 end) JLZF ,
SUM( CASE when [L_JE4]- [L_JE4] *(B_HL3-B_HL2) <0 then [L_JE4] *(B_HL3-B_HL2)-[L_JE4] else 0 end )SJZF,
SUM( [L_JE22] *(B_HL3-B_HL2-B_HL4) )jlyl,
SUM([L_JE22]*B_HL4)khjf,''ICBC,B.B_LASTJSTIME ,GETDATE() AS JSTIME, 1 AS CARDTYPE
from [Z_LS] a, Z_BUSINESS b WHERE A.B_CODE =@B_CODE and A.B_CODE=b.B_CODE AND M_TIME<=GETDATE() and ((B_JUD = 4) OR (B_JUD = 3))and len(C_KH)=8
GROUP BY B.B_CODE,B.B_NAME,B.B_LASTJSTIME
set @errorSum=@errorSum+@@error --累计是否有错误
--跟新流水表面值卡标记为已结算
UPDATE Z_LS SET L_JS=1 WHERE B_CODE =@B_CODE AND M_TIME<=GETDATE() AND L_JS!=1 AND ((B_JUD = 4) OR (B_JUD = 3))and len(C_KH)=8
set @errorSum=@errorSum+@@error --累计是否有错误
--跟新最后一次结算时间
UPDATE Z_BUSINESS SET B_LASTJSTIME=GETDATE() WHERE B_CODE=@B_CODE
set @errorSum=@errorSum+@@error --累计是否有错误
if @errorSum<>0 --如果有错误
begin
rollback transaction --回滚
end
else
begin
commit transaction --提交
end
END
fetch next from My_Cursor into @B_CODE,@B_JSTIME,@B_LastJSTIME
END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
END