22,210
社区成员
发帖
与我相关
我的任务
分享
IF EXISTS(SELECT * FROM sys.objects WHERE parent_object_id=OBJECT_ID('dbo.[OA_LeaseContract_Finance]') AND name='OA_LeaseContract_Finance_LFID')
ALTER TABLE [dbo].[OA_LeaseContract_Finance] Drop [OA_LeaseContract_Finance_LFID]
USE CuService
GO
alter Proc dbo.OA_LeaseContract_Finance_Proce_Init
@LTID int,
@LogName varchar(30)
as
Begin
DECLARE @run_flag int
SET @run_flag=0
IF EXISTS(Select 1 From cRunFlagTbl where run_date=getdate() and run_flag=1)
SET @run_flag=1
IF @run_flag=0 --今日未执行
BEGIN
IF EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
Begin
declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
ALTER TABLE [dbo].[OA_LeaseContract_Finance] Drop [OA_LeaseContract_Finance_LFID]
select @sysFinanceNumber=a.Sys_FinanceNumber
,@d1=a.BeginDate,@d2=a.EndDate
,@Term=a.Pay_Cyc
,@d3=a.Ahead_Date
,@PriceMonth=a.Price
from OA_LeaseContract a
where LTID=@LTID;
declare @TB table
(
[id] [int] IDENTITY(1,1) NOT NULL,
HF_Date datetime,
BeginDate datetime,
EndDate datetime,
Pay_Price numeric(18,2),
Pay_Price2 numeric(18,2),
Get_Price2 numeric(18,2),
updatee datetime
)
declare @dtmp datetime
set @dtmp=@d1
while @dtmp<=@d2
Begin
insert into @TB(BeginDate,EndDate,HF_Date)
values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3)
set @dtmp=dateadd(m,@Term,@dtmp)
--insert into @TB(Awakeday)
--values(@dtmp-@d3)
--Select case when Advanceday > 0
--then Select id,DATEADD(day,-Advanceday,BeginDate) end from @TB
End
update @TB set Pay_Price=@PriceMonth*@Term
update @TB set EndDate=@d2 where EndDate>@d2
update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2
update @TB set Pay_Price2 = @PriceMonth*2 where id = 1
update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2
--update @TB set HF_Date = null where id = 1
update @TB set Get_Price2 = -Pay_Price2
insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate,Pay_Price,Pay_Price2,Get_Price2,CreateUser)
select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate,Pay_Price,Pay_Price2,Get_Price2,@LogName
from @TB a
order by a.id
--run 完后加入标记
Insert into cRunFlagTbl values (GETDATE(),1)
END
End
GO
USE CuService
GO
alter Proc dbo.OA_LeaseContract_Finance_Proce_Init
@LTID int,
@LogName varchar(30)
as
Begin
IF EXISTS (SELECT * FROM dbo.OA_LeaseContract_Finance WHERE @LTID=LTID )
Begin
declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint
ALTER TABLE [dbo].[OA_LeaseContract_Finance] Drop [OA_LeaseContract_Finance_LFID]
select @sysFinanceNumber=a.Sys_FinanceNumber
,@d1=a.BeginDate,@d2=a.EndDate
,@Term=a.Pay_Cyc
,@d3=a.Ahead_Date
,@PriceMonth=a.Price
from OA_LeaseContract a
where LTID=@LTID;
declare @TB table
(
[id] [int] IDENTITY(1,1) NOT NULL,
HF_Date datetime,
BeginDate datetime,
EndDate datetime,
Pay_Price numeric(18,2),
Pay_Price2 numeric(18,2),
Get_Price2 numeric(18,2),
updatee datetime
)
declare @dtmp datetime
set @dtmp=@d1
while @dtmp<=@d2
Begin
insert into @TB(BeginDate,EndDate,HF_Date)
values(@dtmp,dateadd(d,-1,DATEADD(m,@Term,@dtmp)),@dtmp-@d3)
set @dtmp=dateadd(m,@Term,@dtmp)
--insert into @TB(Awakeday)
--values(@dtmp-@d3)
--Select case when Advanceday > 0
--then Select id,DATEADD(day,-Advanceday,BeginDate) end from @TB
End
update @TB set Pay_Price=@PriceMonth*@Term
update @TB set EndDate=@d2 where EndDate>@d2
update @TB set Pay_Price2 = -@PriceMonth*2 where EndDate>@d2 or EndDate=@d2
update @TB set Pay_Price2 = @PriceMonth*2 where id = 1
update @TB set Pay_Price2 = 0 where BeginDate>@d1 and EndDate<@d2
--update @TB set HF_Date = null where id = 1
update @TB set Get_Price2 = -Pay_Price2
insert into OA_LeaseContract_Finance(Sys_FinanceNumber,LTID,HF_Date,BeginDate,EndDate,Pay_Price,Pay_Price2,Get_Price2,CreateUser)
select @sysFinanceNumber,Ltid=@LTID,HF_Date,BeginDate,EndDate,Pay_Price,Pay_Price2,Get_Price2,@LogName
from @TB a
order by a.id
End
GO