1、打开查询分析器。
2、运行如下语句：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[P_SysSettleAccounts]
@i_OperType int,
@i_MonthName varchar(50),
@i_DateE varchar(10),
@i_Remark varchar(255),
@i_bjdcheck int,
@o_HintInfo varchar(200) output,
@i_DelStopBaseInfo int = 0,
@i_DelFinishedOrder int = 0,
@i_DelStopVip int = 0
AS
declare @DateB varchar(10),
@Period int,
@BNum int,
@LNum int
declare
@SysSonNum int,
@Sysfullid varchar(25),
@Act_FullId varchar(25),
@Act_ID int,
@Act_TotAll numeric(28,10),
@Act_Tot numeric(28,10),
@Act_Ini numeric(28,10),
@Act_Hap numeric(28,10),
@Act_Hap_after numeric(28,10),
@Act_SR_Tot numeric(28,10),
@Act_SR_Hap numeric(28,10),
@Act_SR_Hap_after numeric(28,10),
@Act_ZC_Tot numeric(28,10),
@Act_ZC_Hap numeric(28,10),
@Act_ZC_Hap_after numeric(28,10),
@Acc numeric(28,10),
@Act_0 numeric(28,10)
declare
@Cho_Act_Tot char(18),
@Cho_Act_Happen char(18),
@Cho_Act_Hap_after char(18),
@Sto_Act_RecTotal numeric(28,10),
@Sto_Act_Paytotal numeric(28,10),
@Sql1 varchar(2000),
@Rec_0 numeric(28,10),
@Pay_0 numeric(28,10),
@Rec numeric(28,10),
@Pay numeric(28,10)
declare
@s_ID int,
@s_Syb int,
@StockID int,
@Ss_ID int,
@ProdID int,
@SerNumber varchar(30),
@OutFactoryDate varchar (15),
@Prod_Number1 numeric(20,8),
@Prod_Number2 numeric(20,8),
@CostPrice numeric(20,8),
@CostMoney numeric(20,8),
@Prod_Price numeric(20,8),
@Prod_Money numeric(28,10),
@Prod_Order int
declare
@MaxPeriod int,
@Er int,
@draftID int,
@Bill_ID int,
@outbillno varchar(50),
@outdftdetailid int ,
@outprodid int ,
@outcolorid int,
@outsizeid int,
@billdate varchar(10),
@Result int,
@s_ID_Storage int,
@s_ID_StorageDX int
set nocount on
if exists(select s_FullID from SystemInfo where upper(s_Content)='INIORSTART' and upper(s_Values)='INI')
begin
set @Result=-4
goto Error
end
select @Period=s_Values from SystemInfo where s_FullID='00001'
if @Period<11
begin
set @Cho_Act_Happen='Act_Hap_'+convert(char,convert(int,@Period)+1)
set @Cho_Act_Hap_after='Act_Hap_'+convert(char,convert(int,@Period)+2)
set @Cho_Act_Tot='Act_Tot_'+convert(char,convert(int,@Period)+1)
end
else
begin
set @Cho_Act_Happen='Act_Hap_12'
set @Cho_Act_Hap_after='Act_Hap_12'
set @Cho_Act_Tot='Act_Tot_12'
end
if @i_OperType=0
Begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran P_SysSettleAccountsPrc
if exists (select * from MonthPeriod where [email protected]_MonthName)
begin
set @Result = -36
goto ErrorPrc
end
select @DateB=DateB, @Period=Period from MonthPeriod where MonthName='本期'
if @DateB>@i_DateE
begin
set @Result = -37
goto ErrorPrc
end
if @Period>=13
begin
set @Result = -38
goto ErrorPrc
end
if exists(select AutoID from MasterBill where s_Syb=0 and BillState=2 and BillDate>[email protected] and BillDate<[email protected]_DateE)
or exists(select s_ID from MasterBillDft where s_Syb=0 and BillState=1 and BillDate>[email protected] and BillDate<[email protected]_DateE and (POSGUID is not Null))
begin
set @Result = -40
goto ErrorPrc
end
delete from MasterBillDft where BillDate>[email protected] and BillDate<[email protected]_DateE and BillState = -1
if (@@Error<>0) goto ErrorPrc
select @LNum=0
select @BNum=count(*) from MasterBill
where Period=0 and BillDate>[email protected] and BillDate<[email protected]_DateE
select @LNum=count(*) from c_vw_BillBuy
where Period=0 and BillDate>[email protected] and BillDate<[email protected]_DateE
select @[email protected]+count(*) from c_vw_BillSale
where Period=0 and BillDate>[email protected] and BillDate<[email protected]_DateE
select @[email protected]+count(*) from ListMoney, MasterBill
where MasterBill.Period=0 and MasterBill.BillDate>[email protected] and MasterBill.BillDate<[email protected]_DateE
and ListMoney.Bill_ID = MasterBill.AutoID
select @[email protected]+count(*) from c_vw_BillOther
where Period=0 and BillDate>[email protected] and BillDate<[email protected]_DateE
select @[email protected]+count(*) from ListProdCheck, MasterBill
where MasterBill.Period=0 and MasterBill.BillDate>[email protected] and MasterBill.BillDate<[email protected]_DateE
and ListProdCheck.Bill_ID = MasterBill.AutoID
update MasterBill set [email protected] where Period=0 and BillDate>[email protected] and BillDate<[email protected]_DateE
if @@error<>0 goto ErrorPrc
update ListBuy set [email protected] from ListBuy, MasterBill
where [email protected] and MasterBill.BillDate>[email protected] and MasterBill.BillDate<[email protected]_DateE
and ListBuy.Bill_ID = MasterBill.AutoID
if @@error<>0 goto ErrorPrc
update ListSale set [email protected] from ListSale, MasterBill
where [email protected] and MasterBill.BillDate>[email protected] and MasterBill.BillDate<[email protected]_DateE
and ListSale.Bill_ID = MasterBill.AutoID
if @@error<>0 goto ErrorPrc
update ListOther set [email protected] from ListOther, MasterBill
where [email protected] and MasterBill.BillDate>[email protected] and MasterBill.BillDate<[email protected]_DateE
and ListOther.Bill_ID = MasterBill.AutoID
if @@error<>0 goto ErrorPrc
update ListProdCheck set [email protected] from ListProdCheck, MasterBill
where [email protected] and MasterBill.BillDate>[email protected] and MasterBill.BillDate<[email protected]_DateE
and ListProdCheck.Bill_ID = MasterBill.AutoID
if @@error<>0 goto ErrorPrc
update ListMoney set [email protected] from ListMoney, MasterBill
where [email protected] and MasterBill.BillDate>[email protected] and MasterBill.BillDate<[email protected]_DateE
and ListMoney.Bill_ID = MasterBill.AutoID
if @@error<>0 goto ErrorPrc
delete from MasterBillDft where BillState = -1
if (@@Error<>0) goto ErrorPrc
delete from MasterOrder where BillState = -1
if (@@Error<>0) goto ErrorPrc
delete from MasterSC where BillState = -1
if (@@Error<>0) goto ErrorPrc
if @Period<=12
begin
insert into MonthPeriod (Period, MonthName, DateB, DateE, Remark, BNum, LNum)
values (@Period, @i_MonthName,@DateB,@i_DateE,@i_Remark, @BNum, @LNum)
if (@@Error=0) and (@@rowcount<> 0)
begin
select @[email protected]+1
update MonthPeriod set DateB=convert(varchar(10), dateadd(day,1, convert(datetime,@i_DateE)), 120),
[email protected] where MonthName='本期'
if @@error<>0 goto ErrorPrc
end
else goto ErrorPrc
end else
begin
update MonthPeriod set [email protected]_DateE
if @@error<>0 goto ErrorPrc
end
Declare Cur_GetMoneyAccount Cursor Local Scroll
for
Select s_FullID,Act_Tot,s_id from MoneyAccount where s_SonCount=0 and s_Syb in (0,1)
Open Cur_GetMoneyAccount
Fetch next from Cur_GetMoneyAccount into @Act_FullId,@Act_TotAll,@Act_ID
while @@Fetch_Status=0
begin
set @Act_Ini=0
set @Act_Tot=0
set @Act_Hap=0
set @Act_Hap_after=0
select @Act_Ini=Act_Tot_0 from MoneyAccount where [email protected]_FullID
if @Act_FullId='0000100005'
begin
select @Act_Tot=isnull(YS,0) from
(
select Sum(YS) YS,Sum(YF) YF from
(select YS=case when isnull(YS,0)+isnull(AReceive_0,0)-isnull(APayDue_0,0)>0 and isnull(YS,0)>0 then abs(isnull(YS,0)+isnull(AReceive_0,0)-isnull(APayDue_0,0))
when isnull(YF,0)+isnull(APayDue_0,0)-isnull(AReceive_0,0)<0 and isnull(YF,0)>0 then abs(isnull(YF,0)+isnull(APayDue_0,0)-isnull(AReceive_0,0))
when isnull(AReceive_0,0)-isnull(APayDue_0,0)>0 and isnull(YS,0)=0 and isnull(YF,0)=0 then abs(isnull(AReceive_0,0)-isnull(APayDue_0,0))
end,
YF=case when isnull(YF,0)+isnull(APayDue_0,0)-isnull(AReceive_0,0)>0 and isnull(YF,0)>0 then abs(isnull(YF,0)+isnull(APayDue_0,0)-isnull(AReceive_0,0))
when isnull(YS,0)+isnull(AReceive_0,0)-isnull(APayDue_0,0)<0 and isnull(YS,0)>0 then abs(isnull(YS,0)+isnull(AReceive_0,0)-isnull(APayDue_0,0) )
when isnull(APayDue_0,0)-isnull(AReceive_0,0)>0 and isnull(YS,0)=0 and isnull(YF,0)=0 then abs(isnull(APayDue_0,0)-isnull(AReceive_0,0))
end ,s_FullID
from
( select Units_Fullid, YS=case when sum(isnull(YS,0))-sum(isnull(YF,0))>=0 then sum(isnull(YS,0))-sum(isnull(YF,0)) end,
YF=case when sum(isnull(YS,0))-sum(isnull(YF,0))<0 then abs(sum(isnull(YS,0))-sum(isnull(YF,0))) end from
( select s_FullID Units_Fullid,YS,YF from (select s_FullID from Units where s_Syb=1 and s_SonCount=0) aa
Left join (select Units_Fullid, YS=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000100005' ) and
BillDate<[email protected]_DateE and s_Syb=0
group by Units_Fullid) a
on aa.s_FullID= a.Units_Fullid
left join
(select Units_Fullid, YF=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000200001' ) and
BillDate<[email protected]_DateE and s_Syb=0
group by Units_Fullid ) b
on aa.s_FullID=b.Units_Fullid) a
group by Units_Fullid
)a Right join Units b on a.Units_Fullid=b.s_FullID and b.s_syb<>-1
)aaa
) b
select @Act_Hap=isnull(YS,0) from
(
select Sum(YS) YS,Sum(YF) YF from
(select YS, YF
from
( select Units_Fullid, YS=case when sum(isnull(YS,0))-sum(isnull(YF,0))>=0 then sum(isnull(YS,0))-sum(isnull(YF,0)) end,
YF=case when sum(isnull(YS,0))-sum(isnull(YF,0))<0 then abs(sum(isnull(YS,0))-sum(isnull(YF,0))) end from
( select s_FullID Units_Fullid,YS,YF from (select s_FullID from Units where s_Syb=1 and s_SonCount=0) aa
Left join (select Units_Fullid, YS=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000100005' ) and
BillDate>[email protected] and BillDate<[email protected]_DateE and s_Syb=0
group by Units_Fullid) a
on aa.s_FullID= a.Units_Fullid
left join
(select Units_Fullid, YF=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000200001' ) and
BillDate>[email protected] and BillDate<[email protected]_DateE and s_Syb=0
group by Units_Fullid ) b
on aa.s_FullID=b.Units_Fullid) a
group by Units_Fullid
)a Right join Units b on a.Units_Fullid=b.s_FullID and b.s_syb<>-1
)aaa
) b
select @Act_Hap_after=isnull(YS,0) from
(
select Sum(YS) YS,Sum(YF) YF from
(select YS, YF
from
( select Units_Fullid, YS=case when sum(isnull(YS,0))-sum(isnull(YF,0))>=0 then sum(isnull(YS,0))-sum(isnull(YF,0)) end,
YF=case when sum(isnull(YS,0))-sum(isnull(YF,0))<0 then abs(sum(isnull(YS,0))-sum(isnull(YF,0))) end from
( select s_FullID Units_Fullid,YS,YF from (select s_FullID from Units where s_Syb=1 and s_SonCount=0) aa
Left join (select Units_Fullid, YS=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000100005' ) and
BillDate>@i_DateE and s_Syb=0
group by Units_Fullid) a
on aa.s_FullID= a.Units_Fullid
left join
(select Units_Fullid, YF=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000200001' ) and
BillDate>@i_DateE and s_Syb=0
group by Units_Fullid ) b
on aa.s_FullID=b.Units_Fullid) a
group by Units_Fullid
)a Right join Units b on a.Units_Fullid=b.s_FullID and b.s_syb<>-1
)aaa
) b
end
if @Act_FullId='0000200001'
begin
select @Act_Tot=isnull(YF,0) from
(
select Sum(YS) YS,Sum(YF) YF from
(select YS=case when isnull(YS,0)+isnull(AReceive_0,0)-isnull(APayDue_0,0)>0 and isnull(YS,0)>0 then abs(isnull(YS,0)+isnull(AReceive_0,0)-isnull(APayDue_0,0))
when isnull(YF,0)+isnull(APayDue_0,0)-isnull(AReceive_0,0)<0 and isnull(YF,0)>0 then abs(isnull(YF,0)+isnull(APayDue_0,0)-isnull(AReceive_0,0))
when isnull(AReceive_0,0)-isnull(APayDue_0,0)>0 and isnull(YS,0)=0 and isnull(YF,0)=0 then abs(isnull(AReceive_0,0)-isnull(APayDue_0,0))
end,
YF=case when isnull(YF,0)+isnull(APayDue_0,0)-isnull(AReceive_0,0)>0 and isnull(YF,0)>0 then abs(isnull(YF,0)+isnull(APayDue_0,0)-isnull(AReceive_0,0))
when isnull(YS,0)+isnull(AReceive_0,0)-isnull(APayDue_0,0)<0 and isnull(YS,0)>0 then abs(isnull(YS,0)+isnull(AReceive_0,0)-isnull(APayDue_0,0) )
when isnull(APayDue_0,0)-isnull(AReceive_0,0)>0 and isnull(YS,0)=0 and isnull(YF,0)=0 then abs(isnull(APayDue_0,0)-isnull(AReceive_0,0))
end ,s_FullID
from
( select Units_Fullid, YS=case when sum(isnull(YS,0))-sum(isnull(YF,0))>=0 then sum(isnull(YS,0))-sum(isnull(YF,0)) end,
YF=case when sum(isnull(YS,0))-sum(isnull(YF,0))<0 then abs(sum(isnull(YS,0))-sum(isnull(YF,0))) end from
( select s_FullID Units_Fullid,YS,YF from (select s_FullID from Units where s_Syb=1 and s_SonCount=0) aa
Left join (select Units_Fullid, YS=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000100005' ) and
BillDate<[email protected]_DateE and s_Syb=0
group by Units_Fullid) a
on aa.s_FullID= a.Units_Fullid
left join
(select Units_Fullid, YF=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000200001' ) and
BillDate<[email protected]_DateE and s_Syb=0
group by Units_Fullid ) b
on aa.s_FullID=b.Units_Fullid) a
group by Units_Fullid
)a Right join Units b on a.Units_Fullid=b.s_FullID and b.s_syb<>-1
)aaa
) b
select @Act_Hap=isnull(YF,0) from
(
select Sum(YS) YS,Sum(YF) YF from
(select YS, YF
from
( select Units_Fullid, YS=case when sum(isnull(YS,0))-sum(isnull(YF,0))>=0 then sum(isnull(YS,0))-sum(isnull(YF,0)) end,
YF=case when sum(isnull(YS,0))-sum(isnull(YF,0))<0 then abs(sum(isnull(YS,0))-sum(isnull(YF,0))) end from
( select s_FullID Units_Fullid,YS,YF from (select s_FullID from Units where s_Syb=1 and s_SonCount=0) aa
Left join (select Units_Fullid, YS=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000100005' ) and
BillDate>[email protected] and BillDate<[email protected]_DateE and s_Syb=0
group by Units_Fullid) a
on aa.s_FullID= a.Units_Fullid
left join
(select Units_Fullid, YF=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000200001' ) and
BillDate>[email protected] and BillDate<[email protected]_DateE and s_Syb=0
group by Units_Fullid ) b
on aa.s_FullID=b.Units_Fullid) a
group by Units_Fullid
)a Right join Units b on a.Units_Fullid=b.s_FullID and b.s_syb<>-1
)aaa
) b
select @Act_Hap_after=isnull(YF,0) from
(
select Sum(YS) YS,Sum(YF) YF from
(select YS, YF
from
( select Units_Fullid, YS=case when sum(isnull(YS,0))-sum(isnull(YF,0))>=0 then sum(isnull(YS,0))-sum(isnull(YF,0)) end,
YF=case when sum(isnull(YS,0))-sum(isnull(YF,0))<0 then abs(sum(isnull(YS,0))-sum(isnull(YF,0))) end from
( select s_FullID Units_Fullid,YS,YF from (select s_FullID from Units where s_Syb=1 and s_SonCount=0) aa
Left join (select Units_Fullid, YS=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000100005' ) and
BillDate>@i_DateE and s_Syb=0
group by Units_Fullid) a
on aa.s_FullID= a.Units_Fullid
left join
(select Units_Fullid, YF=isnull(Sum(Act_Money),0)
from a_vw_AllMoneyAccountList
where (Act_Fullid='0000200001' ) and
BillDate>@i_DateE and s_Syb=0
group by Units_Fullid ) b
on aa.s_FullID=b.Units_Fullid) a
group by Units_Fullid
)a Right join Units b on a.Units_Fullid=b.s_FullID and b.s_syb<>-1
)aaa
) b
end
if @Act_FullId<>'0000100005' and @Act_FullId<>'0000200001'
begin
select @Act_Tot=isnull(sum(Act_Money),0) from Moneylist where [email protected]_Id and BillDate<[email protected]_DateE and s_Syb=0
select @Act_Hap=isnull(sum(Act_Money),0) from Moneylist where [email protected]_Id and BillDate>[email protected] and BillDate<[email protected]_DateE and s_Syb=0
select @Act_Hap_after=isnull(sum(Act_Money),0) from Moneylist where [email protected]_Id and BillDate>@i_DateE and s_Syb=0
set @[email protected]_Tot+isnull(@Act_Ini,0)
end
if @Act_Hap_after>=0
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Hap_after+'='+'+'+convert(char,@Act_Hap_after,2)+' where s_FullID='+''''[email protected]_FullID+''''
exec(@Sql1)
if @@error<>0 goto ErrorPrc_cur
end
else
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Hap_after+'='+'-'+convert(char,abs(@Act_Hap_after),2)+' where s_FullID='+''''[email protected]_FullID+''''
exec(@Sql1)
if @@error<>0 goto ErrorPrc_cur
end
if @Act_Hap>=0
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Happen+'='+'+'+convert(char,@Act_Hap,2)+' where s_FullID='+''''[email protected]_FullID+''''
exec(@Sql1)
if @@error<>0 goto ErrorPrc_cur
end
else
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Happen+'='+'-'+convert(char,abs(@Act_Hap),2)+' where s_FullID='+''''[email protected]_FullID+''''
exec(@Sql1)
if @@error<>0 goto ErrorPrc_cur
end
if @Act_Tot>=0
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Tot+'='+'+'+convert(char,@Act_Tot,2)+' where s_FullID='+''''[email protected]_FullID+''''
exec(@Sql1)
if @@error<>0 goto ErrorPrc_cur
end
else
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Tot+'='+'-'+convert(char,abs(@Act_Tot),2)+' where s_FullID='+''''[email protected]_FullID+''''
exec(@Sql1)
if @@error<>0 goto ErrorPrc_cur
end
Fetch next from Cur_GetMoneyAccount into @Act_FullId,@Act_TotAll,@Act_Id
end
Close Cur_GetMoneyAccount
deallocate Cur_GetMoneyAccount
select @Act_SR_Tot=isnull(sum(Act_Money),0) from a_vw_AllMoneyAccountList where Act_Fullid like '00003%' and BillDate<[email protected]_DateE and s_Syb=0
select @Act_SR_Hap=isnull(sum(Act_Money),0) from a_vw_AllMoneyAccountList where Act_Fullid like '00003%' and BillDate>[email protected] and BillDate<[email protected]_DateE and s_Syb=0
select @Act_SR_Hap_after=isnull(sum(Act_Money),0) from a_vw_AllMoneyAccountList where Act_Fullid like '00003%' and BillDate>@i_DateE and s_Syb=0
select @Act_ZC_Tot=isnull(sum(Act_Money),0) from a_vw_AllMoneyAccountList where Act_Fullid like '00004%' and BillDate<[email protected]_DateE and s_Syb=0
select @Act_ZC_Hap=isnull(sum(Act_Money),0) from a_vw_AllMoneyAccountList where Act_Fullid like '00004%' and BillDate>[email protected] and BillDate<[email protected]_DateE and s_Syb=0
select @Act_ZC_Hap_after=isnull(sum(Act_Money),0) from a_vw_AllMoneyAccountList where Act_Fullid like '00004%' and BillDate>@i_DateE and s_Syb=0
if @[email protected]_ZC_Hap_after>=0
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Hap_after+'='+'+'+convert(char,@[email protected]_ZC_Hap_after,2)+' where s_FullID=''0000500002'''
exec(@Sql1)
if @@error<>0 goto ErrorPrc
end
else
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Hap_after+'='+'-'+convert(char,abs(@[email protected]_ZC_Hap_after),2)+' where s_FullID=''0000500002'''
exec(@Sql1)
if @@error<>0 goto ErrorPrc
end
if @[email protected]_ZC_Hap>=0
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Happen+'='+'+'+convert(char,@[email protected]_ZC_Hap,2)+' where s_FullID=''0000500002'''
exec(@Sql1)
if @@error<>0 goto ErrorPrc
end
else
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Happen+'='+'-'+convert(char,abs(@[email protected]_ZC_Hap),2)+' where s_FullID=''0000500002'''
exec(@Sql1)
if @@error<>0 goto ErrorPrc
end
if @[email protected]_ZC_Tot>=0
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Tot+'='+'+'+convert(char,@[email protected]_ZC_Tot,2)+' where s_FullID=''0000500002'''
exec(@Sql1)
if @@error<>0 goto ErrorPrc
end
else
begin
set @Sql1='update MoneyAccount set '[email protected]_Act_Tot+'='+'-'+convert(char,abs(@[email protected]_ZC_Tot),2)+' where s_FullID=''0000500002'''
exec(@Sql1)
if @@error<>0 goto ErrorPrc
end
set @Sql1='update MoneyAccount set '[email protected]_Act_Tot+'=Act_Tot'+' where s_FullID=''0000500001'''
exec(@Sql1)
if @@error<>0 goto ErrorPrc
select @Period=s_Values,@SysSonNum=s_SonCount from SystemInfo where s_FullID='00001'
update SystemInfo set s_Values=convert(char,convert(int,@Period)+1) where s_FullID='00001'
if @@error<>0 goto ErrorPrc
if @SysSonNum>=9
set @SysFullId='00001000'+convert(char,convert(int,@SysSonNum+1))
else
set @SysFullId='000010000'+convert(char,convert(int,@SysSonNum+1))
insert into SystemInfo (s_FullID,s_ParentID,s_Content,s_Values,s_SonCount,s_JCQH)
values (@SysFullId,'00001',@DateB,@i_DateE,0,@i_MonthName)
if @@error<>0 goto ErrorPrc
update SystemInfo set s_SonCount=s_SonCount+1 where s_FullID='00001'
if @@error<>0 goto ErrorPrc
update SystemInfo set s_Values='0' where s_FullID='00007'
if @@error<>0 goto ErrorPrc
commit tran P_SysSettleAccountsPrc
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
return 0
End
if @i_OperType=1
begin
if @Period='' or @Period is Null or @Period ='0'
return 3
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran P_SysSettleAccountsPrc
update MasterBill set Period=0 where [email protected]
if @@error<>0 goto ErrorPrc
update ListBuy set Period=0 where [email protected]
if @@error<>0 goto ErrorPrc
update ListSale set Period=0 where [email protected]
if @@error<>0 goto ErrorPrc
update ListMoney set Period=0 where [email protected]
if @@error<>0 goto ErrorPrc
update ListOther set Period=0 where [email protected]
if @@error<>0 goto ErrorPrc
update ListProdCheck set Period=0 where [email protected]
if @@error<>0 goto ErrorPrc
if @Period=1
begin
select @DateB=DateB,@i_DateE=DateE from MonthPeriod where [email protected]
delete from MonthPeriod where [email protected]
if @@error<>0 goto ErrorPrc
update MonthPeriod set [email protected],
Period=1 where MonthName='本期'
if @@error<>0 goto ErrorPrc
end else
begin
delete from MonthPeriod where [email protected]
if @@error<>0 goto ErrorPrc
select @DateB=DateB,@i_DateE=DateE from MonthPeriod where [email protected]
update MonthPeriod set DateB=convert(varchar(10), dateadd(day,1, convert(datetime,@i_DateE)), 120),
Period=Period-1 where MonthName='本期'
if @@error<>0 goto ErrorPrc
end
select @Period=isnull(s_Values,0) from SystemInfo where s_FullID='00001'
if @Period=12
begin
set @Sql1='update MoneyAccount set Act_Tot_'+convert(char,convert(int,@Period)) +' =0'
exec(@Sql1)
if @@error<>0 goto ErrorPrc
end
else
if @Period=1
begin
set @Sql1='update MoneyAccount set Act_Hap_'+convert(char,convert(int,@Period)) +' =Act_Hap_'+convert(char,convert(int,@Period))+' + Act_Hap_'+convert(char,convert(int,@Period)+1)
exec(@Sql1)
if @@error<>0 goto ErrorPrc
set @Sql1='update MoneyAccount set Act_Tot_'+convert(char,convert(int,@Period)) +' =0'
exec(@Sql1)
if @@error<>0 goto ErrorPrc
set @Sql1='update MoneyAccount set Act_Tot_'+convert(char,convert(int,@Period)+1) +' =0'
exec(@Sql1)
if @@error<>0 goto ErrorPrc
set @Sql1='update MoneyAccount set Act_Hap_'+convert(char,convert(int,@Period)+1) +'=0'
exec(@Sql1)
if @@error<>0 goto ErrorPrc
end
else
begin
set @Sql1='update MoneyAccount set Act_Hap_'+convert(char,convert(int,@Period)) +' =Act_Hap_'+convert(char,convert(int,@Period))+' + Act_Hap_'+convert(char,convert(int,@Period)+1)
exec(@Sql1)
if @@error<>0 goto ErrorPrc
set @Sql1='update MoneyAccount set Act_Tot_'+convert(char,convert(int,@Period)) +' =0'
exec(@Sql1)
if @@error<>0 goto ErrorPrc
set @Sql1='update MoneyAccount set Act_Tot_'+convert(char,convert(int,@Period)+1) +' =0'
exec(@Sql1)
if @@error<>0 goto ErrorPrc
set @Sql1='update MoneyAccount set Act_Hap_'+convert(char,convert(int,@Period)+1) +'=0'
exec(@Sql1)
if @@error<>0 goto ErrorPrc
end
select @Period=s_Values,@SysSonNum=s_SonCount from SystemInfo where s_FullID='00001'
if convert(int,@Period)>0
begin
update SystemInfo set s_Values=convert(char,convert(int,@Period)-1) where s_FullID='00001'
if @@error<>0 goto ErrorPrc
select @SysFullId=MAx(s_FullID) from SystemInfo where s_ParentID='00001'
delete from SystemInfo where [email protected]
if @@error<>0 goto ErrorPrc
update SystemInfo set s_SonCount=s_SonCount-1 where s_FullID='00001'
if @@error<>0 goto ErrorPrc
end
update SystemInfo set s_Values='1' where s_FullID='00007'
if @@error<>0 goto ErrorPrc
commit tran P_SysSettleAccountsPrc
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
return 0
end
if @i_OperType=2
begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran P_SysSettleAccountsPrc
Delete UserIMPower Where UserIMPower.user_id not in (Select s_ID from Users)
if (@@Error<>0) goto ErrorPrc
if exists(select AutoID from MasterBill where s_Syb=0 and BillState=2)
or exists(select s_ID from MasterBillDft where s_Syb=0 and BillState=1 and (POSGUID is not Null))
begin
set @Result = -40
goto ErrorPrc
end
if exists (select * from YearPeriod where [email protected]_MonthName)
begin
set @Result = -36
goto ErrorPrc
end
select @LNum=0
select @BNum=count(*) from MasterBill
select @LNum=count(*) from ListBuy, MasterBill
where ListBuy.Bill_ID = MasterBill.AutoID
select @[email protected]+count(*) from ListSale, MasterBill
where ListSale.Bill_ID = MasterBill.AutoID
select @[email protected]+count(*) from ListOther, MasterBill
where ListOther.Bill_ID = MasterBill.AutoID
select @[email protected]+count(*) from ListMoney, MasterBill
where ListMoney.Bill_ID = MasterBill.AutoID
select @[email protected]+count(*) from ListProdCheck, MasterBill
where ListProdCheck.Bill_ID = MasterBill.AutoID
select @DateB=DateB, @Period=Period from YearPeriod where YearName='本期'
insert into YearPeriod (Period, YearName, DateB, DateE, Remark, BNum, LNum)
values (@Period, @i_MonthName,@DateB,convert(varchar(10), getdate(), 120),@i_Remark, @BNum, @LNum)
if (@@Error <> 0) or (@@rowcount = 0) goto ErrorPrc
select @[email protected]+1
update YearPeriod set DateB=convert(varchar(10), getdate(), 120),
[email protected] where YearName='本期'
if @@error<>0 goto ErrorPrc
create table #employee
(s_ID int,
AReceive numeric(28,12),
APaydue numeric(28,12)
)
insert #employee
select a_vw_Infoemployee.s_id,
case
when sum(isnull(aaa.MoneyBeforeAReceive,0))>=sum(isnull(aaa.MoneyBeforeAPaydue,0))
then sum(isnull(aaa.MoneyBeforeAReceive,0))- sum(isnull(aaa.MoneyBeforeAPaydue,0)) else 0
end AReceive,
case
when sum(isnull(aaa.MoneyBeforeAPaydue,0))>sum(isnull(aaa.MoneyBeforeAReceive,0))
then sum(isnull(aaa.MoneyBeforeAPaydue,0))- sum(isnull(aaa.MoneyBeforeAReceive,0)) else 0
end APaydue
from a_vw_Infoemployee
inner join
(
select a_vw_Infoemployee.s_id,s_fullid deptfullid,
isnull(AReceive_0,0)+isnull(b.MoneyBeforeAReceive,0) MoneyBeforeAReceive,
isnull(APayDue_0,0)+isnull(b.MoneyBeforeAPaydue,0) MoneyBeforeAPaydue
from a_vw_Infoemployee
left join
(select User_ID,
sum(case when act_id=11 then Act_Money else 0 end) MoneyBeforeAReceive,
sum(case when act_id=19 then Act_Money else 0 end) MoneyBeforeAPaydue
from c_vw_AllMoneyList
where
s_Syb<>-100 and Act_ID in (11,19)
group by User_ID
) b on a_vw_Infoemployee.s_id=b.User_ID where a_vw_Infoemployee.s_syb>=0
)aaa
on aaa.deptfullid = a_vw_Infoemployee.s_fullid
where a_vw_Infoemployee.s_Syb>=0 and a_vw_Infoemployee.s_sonCount = 0
group by a_vw_Infoemployee.s_id
if (@@Error<>0) goto ErrorPrc
update employee set AReceive_0=0,APayDue_0=0
if (@@Error<>0) goto ErrorPrc
update employee set AReceive_0=b.AReceive,APayDue_0=b.APaydue from employee a,#employee b where a.s_ID=b.s_ID
if (@@Error<>0) goto ErrorPrc
create table #depart
(s_ID int,
AReceive numeric(28,12),
APaydue numeric(28,12)
)
insert #depart
select a_vw_InfoDeptIni.s_id,
case
when sum(isnull(aaa.MoneyBeforeAReceive,0))>=sum(isnull(aaa.MoneyBeforeAPaydue,0))
then sum(isnull(aaa.MoneyBeforeAReceive,0))- sum(isnull(aaa.MoneyBeforeAPaydue,0)) else 0
end AReceive,
case
when sum(isnull(aaa.MoneyBeforeAPaydue,0))>sum(isnull(aaa.MoneyBeforeAReceive,0))
then sum(isnull(aaa.MoneyBeforeAPaydue,0))- sum(isnull(aaa.MoneyBeforeAReceive,0)) else 0
end APaydue
from a_vw_InfoDeptIni
inner join
(
select a_vw_InfoDeptIni.s_id,s_fullid deptfullid,
isnull(AReceive_0,0)+isnull(b.MoneyBeforeAReceive,0) MoneyBeforeAReceive,
isnull(APayDue_0,0)+isnull(b.MoneyBeforeAPaydue,0) MoneyBeforeAPaydue
from a_vw_InfoDeptIni
left join
(select Dept_ID,
sum(case when act_id=11 then Act_Money else 0 end) MoneyBeforeAReceive,
sum(case when act_id=19 then Act_Money else 0 end) MoneyBeforeAPaydue
from c_vw_AllMoneyList
where
s_Syb<>-100 and Act_ID in (11,19)
group by Dept_ID
) b on a_vw_InfoDeptIni.s_id=b.Dept_ID where a_vw_InfoDeptIni.s_syb>=0
)aaa
on aaa.deptfullid = a_vw_InfoDeptIni.s_fullid
where a_vw_InfoDeptIni.s_Syb>=0 and a_vw_InfoDeptIni.s_sonCount = 0
group by a_vw_InfoDeptIni.s_id
if (@@Error<>0) goto ErrorPrc
update department set AReceive_0=0,APayDue_0=0
if (@@Error<>0) goto ErrorPrc
update department set AReceive_0=b.AReceive,APayDue_0=b.APaydue from department a,#depart b where a.s_ID=b.s_ID
if (@@Error<>0) goto ErrorPrc
update department set AReceive_0=b.AReceive,APayDue_0=b.APaydue from department a,#depart b where a.s_ID=0 and b.s_id=-9999
if (@@Error<>0) goto ErrorPrc
truncate table ListProdCheckDft
if (@@Error<>0) goto ErrorPrc
alter table MasterBillDft
Disable TRIGGER TR_D_MasterBillDft
delete from MasterBillDft where BillType in (59, 66, 67) or BillState = -1
if (@@Error<>0) goto ErrorPrc
alter table MasterBillDft
Enable TRIGGER TR_D_MasterBillDft
delete from MasterOrder where BillState = -1
if (@@Error<>0) goto ErrorPrc
delete from MasterSC where BillState = -1
if (@@Error<>0) goto ErrorPrc
truncate table SysLog
if (@@Error<>0) goto ErrorPrc
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CZInfo_CZInfoMod]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CZInfo] DROP CONSTRAINT FK_CZInfo_CZInfoMod
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ListBuy_MasterBill]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ListBuy] DROP CONSTRAINT FK_ListBuy_MasterBill
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ListMoney_MasterBill]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ListMoney] DROP CONSTRAINT FK_ListMoney_MasterBill
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ListOther_MasterBill]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ListOther] DROP CONSTRAINT FK_ListOther_MasterBill
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ListOtherSC_MasterBill]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ListOtherSC] DROP CONSTRAINT FK_ListOtherSC_MasterBill
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ListProdCheck_MasterBill]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ListProdCheck] DROP CONSTRAINT FK_ListProdCheck_MasterBill
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ListSale_MasterBill]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ListSale] DROP CONSTRAINT FK_ListSale_MasterBill
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ListSaleAcount_MasterBill]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ListSaleAcount] DROP CONSTRAINT FK_ListSaleAcount_MasterBill
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ProductBarCode_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ProductBarCode] DROP CONSTRAINT FK_ProductBarCode_Product
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ProductCode_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ProductCode] DROP CONSTRAINT FK_ProductCode_Product
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ProductPrice_Product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ProductPrice] DROP CONSTRAINT FK_ProductPrice_Product
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_GroupPurview_Group]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RolePower] DROP CONSTRAINT FK_GroupPurview_Group
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SMSClassPerInfo_SMSClassPer]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SMSClassPerInfo] DROP CONSTRAINT FK_SMSClassPerInfo_SMSClassPer
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SMSSendClassPerInfo1_SMSSendClassPer1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SMSSendClassPerInfo1] DROP CONSTRAINT FK_SMSSendClassPerInfo1_SMSSendClassPer1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SMSSendClassPerInfo2_Sys_SMSSendClassPerInfo2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SMSSendClassPerInfo2_Sys] DROP CONSTRAINT FK_SMSSendClassPerInfo2_Sys_SMSSendClassPerInfo2
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SMSSendClassPerInfo3_Bill_SMSSendClassPerInfo3]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SMSSendClassPerInfo3_Bill] DROP CONSTRAINT FK_SMSSendClassPerInfo3_Bill_SMSSendClassPerInfo3
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SMSSendClassPer1_SMSUser]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SMSSendClassPer1] DROP CONSTRAINT FK_SMSSendClassPer1_SMSUser
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SMSSendClassPer2_SMSUser]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SMSSendClassPer2] DROP CONSTRAINT FK_SMSSendClassPer2_SMSUser
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SMSSendClassPer3_SMSUser]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SMSSendClassPer3] DROP CONSTRAINT FK_SMSSendClassPer3_SMSUser
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SMSSendClassPerInfo2_SMSUser]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SMSSendClassPerInfo2] DROP CONSTRAINT FK_SMSSendClassPerInfo2_SMSUser
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SMSSendClassPerInfoR_SMSUser]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SMSSendClassPerInfoR] DROP CONSTRAINT FK_SMSSendClassPerInfoR_SMSUser
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SMSUserLog_SMSUser]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SMSUserLog] DROP CONSTRAINT FK_SMSUserLog_SMSUser
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SmsUserPower_SMSUser]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SmsUserPower] DROP CONSTRAINT FK_SmsUserPower_SMSUser
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PromStorHouse_SalesAdv]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PromStorHouse] DROP CONSTRAINT FK_PromStorHouse_SalesAdv
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PromotionList_Promotion]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SalesAdvList] DROP CONSTRAINT FK_PromotionList_Promotion
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UserGroup_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserGroup] DROP CONSTRAINT FK_UserGroup_Users
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UserImpower_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserImpower] DROP CONSTRAINT FK_UserImpower_Users
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_VIPCardPic_VIPCard]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[VIPCardPic] DROP CONSTRAINT FK_VIPCardPic_VIPCard
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_VIPLogExchangeList_VIPLogExchange]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[VIPLogExchangeList] DROP CONSTRAINT FK_VIPLogExchangeList_VIPLogExchange
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_VIPLogExchangeListP_VIPLogExchange]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[VIPLogExchangeListP] DROP CONSTRAINT FK_VIPLogExchangeListP_VIPLogExchange
truncate table MasterBill
if (@@Error<>0) goto ErrorPrc
truncate table MasterBillT
if (@@Error<>0) goto Error
truncate table ListBuy
if (@@Error<>0) goto ErrorPrc
truncate table ListSale
if (@@Error<>0) goto ErrorPrc
truncate table ListSaleAcount
if (@@Error<>0) goto ErrorPrc
truncate table ListSCFYTZ
if (@@Error<>0) goto Error
truncate table ListSCFYTZDft
if (@@Error<>0) goto Error
truncate table ListOther
if (@@Error<>0) goto ErrorPrc
truncate table ListOtherSC
if (@@Error<>0) goto Error
truncate table ListOtherSCDft
if (@@Error<>0) goto Error
truncate table ListProdCheck
if (@@Error<>0) goto ErrorPrc
truncate table ListMoney
if (@@Error<>0) goto ErrorPrc
truncate table JS_Bill
if (@@Error<>0) goto ErrorPrc
truncate table ProductInOut
if (@@Error<>0) goto ErrorPrc
truncate table ProductInOutXJXC
if (@@Error<>0) goto Error
truncate table MoneyList
if (@@Error<>0) goto ErrorPrc
truncate table BillVIPMulti
if (@@Error<>0) goto Error
truncate table LogBillEdit
if (@@Error<>0) goto Error
declare @IfAllow varchar(2)
select @IfAllow=IfAllow from SetSystemInformation where Content='年结后保存价格跟踪数据'
if @IfAllow='×'
begin
truncate table PricelogUnit
if (@@Error<>0) goto ErrorPrc
truncate table PricelogStor
if (@@Error<>0) goto ErrorPrc
end
ALTER TABLE [dbo].[CZInfo] ADD
CONSTRAINT [FK_CZInfo_CZInfoMod] FOREIGN KEY
(
[Bill_ID]
) REFERENCES [dbo].[CZInfoMod] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[ListBuy] ADD
CONSTRAINT [FK_ListBuy_MasterBill] FOREIGN KEY
(
[Bill_ID]
) REFERENCES [dbo].[MasterBill] (
[AutoID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[ListMoney] ADD
CONSTRAINT [FK_ListMoney_MasterBill] FOREIGN KEY
(
[Bill_ID]
) REFERENCES [dbo].[MasterBill] (
[AutoID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[ListOther] ADD
CONSTRAINT [FK_ListOther_MasterBill] FOREIGN KEY
(
[Bill_ID]
) REFERENCES [dbo].[MasterBill] (
[AutoID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[ListOtherSC] ADD
CONSTRAINT [FK_ListOtherSC_MasterBill] FOREIGN KEY
(
[Bill_id]
) REFERENCES [dbo].[MasterBill] (
[AutoID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[ListProdCheck] ADD
CONSTRAINT [FK_ListProdCheck_MasterBill] FOREIGN KEY
(
[Bill_ID]
) REFERENCES [dbo].[MasterBill] (
[AutoID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[ListSale] ADD
CONSTRAINT [FK_ListSale_MasterBill] FOREIGN KEY
(
[Bill_ID]
) REFERENCES [dbo].[MasterBill] (
[AutoID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[ListSaleAcount] ADD
CONSTRAINT [FK_ListSaleAcount_MasterBill] FOREIGN KEY
(
[Bill_ID]
) REFERENCES [dbo].[MasterBill] (
[AutoID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[ProductBarCode] ADD
CONSTRAINT [FK_ProductBarCode_Product] FOREIGN KEY
(
[Prod_ID]
) REFERENCES [dbo].[Product] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[ProductCode] ADD
CONSTRAINT [FK_ProductCode_Product] FOREIGN KEY
(
[Prod_ID]
) REFERENCES [dbo].[Product] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[ProductPrice] ADD
CONSTRAINT [FK_ProductPrice_Product] FOREIGN KEY
(
[Prod_ID]
) REFERENCES [dbo].[Product] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[PromStorHouse] ADD
CONSTRAINT [FK_PromStorHouse_SalesAdv] FOREIGN KEY
(
[s_PID]
) REFERENCES [dbo].[SalesAdv] (
[s_ID]
) ON DELETE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[RolePower] ADD
CONSTRAINT [FK_GroupPurview_Group] FOREIGN KEY
(
[Group_ID]
) REFERENCES [dbo].[RoleGroup] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SMSClassPerInfo] ADD
CONSTRAINT [FK_SMSClassPerInfo_SMSClassPer] FOREIGN KEY
(
[SmsCr_ID]
) REFERENCES [dbo].[SMSClassPer] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SMSSendClassPer1] ADD
CONSTRAINT [FK_SMSSendClassPer1_SMSUser] FOREIGN KEY
(
[SU_ID]
) REFERENCES [dbo].[SMSUser] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SMSSendClassPer2] ADD
CONSTRAINT [FK_SMSSendClassPer2_SMSUser] FOREIGN KEY
(
[SU_ID]
) REFERENCES [dbo].[SMSUser] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SMSSendClassPer3] ADD
CONSTRAINT [FK_SMSSendClassPer3_SMSUser] FOREIGN KEY
(
[SU_ID]
) REFERENCES [dbo].[SMSUser] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SMSSendClassPerInfo1] ADD
CONSTRAINT [FK_SMSSendClassPerInfo1_SMSSendClassPer1] FOREIGN KEY
(
[SmsCr_ID]
) REFERENCES [dbo].[SMSSendClassPer1] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SMSSendClassPerInfo2] ADD
CONSTRAINT [FK_SMSSendClassPerInfo2_SMSUser] FOREIGN KEY
(
[SU_ID]
) REFERENCES [dbo].[SMSUser] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SMSSendClassPerInfo2_Sys] ADD
CONSTRAINT [FK_SMSSendClassPerInfo2_Sys_SMSSendClassPerInfo2] FOREIGN KEY
(
[scpi2_ID]
) REFERENCES [dbo].[SMSSendClassPerInfo2] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SMSSendClassPerInfo3_Bill] ADD
CONSTRAINT [FK_SMSSendClassPerInfo3_Bill_SMSSendClassPerInfo3] FOREIGN KEY
(
[scpi_ID]
) REFERENCES [dbo].[SMSSendClassPerInfo3] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SMSSendClassPerInfoR] ADD
CONSTRAINT [FK_SMSSendClassPerInfoR_SMSUser] FOREIGN KEY
(
[SU_ID]
) REFERENCES [dbo].[SMSUser] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SMSUserLog] ADD
CONSTRAINT [FK_SMSUserLog_SMSUser] FOREIGN KEY
(
[SU_ID]
) REFERENCES [dbo].[SMSUser] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SalesAdvList] ADD
CONSTRAINT [FK_PromotionList_Promotion] FOREIGN KEY
(
[Prom_ID]
) REFERENCES [dbo].[SalesAdv] (
[s_ID]
) ON DELETE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[SmsUserPower] ADD
CONSTRAINT [FK_SmsUserPower_SMSUser] FOREIGN KEY
(
[SU_ID]
) REFERENCES [dbo].[SMSUser] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[UserGroup] ADD
CONSTRAINT [FK_UserGroup_Users] FOREIGN KEY
(
[User_ID]
) REFERENCES [dbo].[Users] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[UserImpower] ADD
CONSTRAINT [FK_UserImpower_Users] FOREIGN KEY
(
[User_ID]
) REFERENCES [dbo].[Users] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[VIPCardPic] ADD
CONSTRAINT [FK_VIPCardPic_VIPCard] FOREIGN KEY
(
[VIP_ID]
) REFERENCES [dbo].[VIPCard] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[VIPLogExchangeList] ADD
CONSTRAINT [FK_VIPLogExchangeList_VIPLogExchange] FOREIGN KEY
(
[Bill_ID]
) REFERENCES [dbo].[VIPLogExchange] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
ALTER TABLE [dbo].[VIPLogExchangeListP] ADD
CONSTRAINT [FK_VIPLogExchangeListP_VIPLogExchange] FOREIGN KEY
(
[Bill_ID]
) REFERENCES [dbo].[VIPLogExchange] (
[s_ID]
) ON DELETE CASCADE ON UPDATE CASCADE
if (@@Error<>0) goto ErrorPrc
delete from MonthPeriod where AutoID<>1
if (@@Error<>0) goto ErrorPrc
update MonthPeriod set Period = 1, DateB = '1900-01-01' where AutoID=1
if (@@Error<>0) goto ErrorPrc
update BuildID set ClassCurID=0 where upper(Class_Name) in ('AMASTERBILL', 'LISTPRODCHECKDFTP')
if not exists (select 1 from MASTERBILL)
begin
update BuildID set ClassCurID=0 where upper(Class_Name)='MASTERBILL'
end
if not exists (select 1 from LISTBUY)
begin
update BuildID set ClassCurID=0 where upper(Class_Name)='LISTBUY'
end
if not exists (select 1 from LISTSALE)
begin
update BuildID set ClassCurID=0 where upper(Class_Name)='LISTSALE'
end
if not exists (select 1 from LISTOTHER)
begin
update BuildID set ClassCurID=0 where upper(Class_Name)='LISTOTHER'
end
if not exists (select 1 from LISTPRODCHECKDFT)
begin
update BuildID set ClassCurID=0 where upper(Class_Name)='LISTPRODCHECKDFT'
end
if not exists (select 1 from LISTRETAILDFT)
begin
update BuildID set ClassCurID=0 where upper(Class_Name)='LISTRETAILDFT'
end
if (@@Error<>0) goto ErrorPrc
Set @billdate = convert(varchar(10),getdate(),120)
Delete From SalesAdv where (s_Syb = -1) or (Enddate<@i_DateE)
if (@@Error<>0) goto ErrorPrc
Delete SalesAdvList FROM SalesAdv INNER JOIN SalesAdvList ON SalesAdv.s_ID = SalesAdvList.Prom_ID
where (SalesAdv.s_Syb = -1) or (SalesAdv.Enddate<@i_DateE)
if (@@Error<>0) goto ErrorPrc
Delete PromStorHouse FROM SalesAdv INNER JOIN PromStorHouse ON SalesAdv.s_ID = PromStorHouse.s_PID
where (SalesAdv.s_Syb = -1) or (SalesAdv.Enddate<@i_DateE)
if (@@Error<>0) goto ErrorPrc
delete from StorageIni
if (@@Error<>0) goto ErrorPrc
delete from StorageDXIni
if (@@Error<>0) goto ErrorPrc
insert into StorageIni (
[s_ID],[Stor_ID],[Ss_ID],[Prod_ID],[SerNumber],[OutFactoryDate],[Prod_Number1],[Prod_Number2],[CostPrice],
[CostMoney],[Prod_Order],[CostMethod],[EndFullLifeDate],[Prod_Number2II])
select a.s_ID,a.Stor_ID,a.Ss_ID,a.Prod_ID,a.SerNumber,a.OutFactoryDate,a.Prod_Number1,a.Prod_Number2,a.CostPrice,a.CostMoney,
a.Prod_Order,b.CostMethod,EndFullLifeDate ,Prod_Number2II
from Storage a left join Product b on a.Prod_ID=b.s_ID
if (@@Error<>0) goto ErrorPrc
select @s_ID_Storage=isnull(max(s_ID),0)+1 from StorageIni
update BuildID set [email protected]_ID_Storage where Class_Name='STORAGEINI'
if (@@Error<>0) goto ErrorPrc
insert into StorageDXIni (
[s_ID],[s_Syb],[Stor_ID],[Prod_ID],[SerNumber],[OutFactoryDate],[Prod_Number1],[Prod_Number2],[CostPrice],[CostMoney],
[Prod_Price],[Prod_Money],[Prod_Order],[CostMethod],[Prod_Number2II])
select a.s_ID,a.s_Syb,a.Stor_ID,a.Prod_ID,a.SerNumber,a.OutFactoryDate,a.Prod_Number1,a.Prod_Number2,a.CostPrice,a.CostMoney,
Prod_Price,Prod_Money, a.Prod_Order,b.CostMethod ,a.Prod_Number2II
from StorageDX a left join Product b on a.Prod_ID=b.s_ID
if (@@Error<>0) goto ErrorPrc
select @s_ID_StorageDX=isnull(max(s_ID),0)+1 from StorageDXIni
update BuildID set [email protected]_ID_StorageDX where Class_Name='STORAGEDXINI'
if (@@Error<>0) goto ErrorPrc
delete from Storage
if (@@Error<>0) goto ErrorPrc
delete from StorageDX
if (@@Error<>0) goto ErrorPrc
if exists(select 1 from masterbilldft where posguid is not null)
begin
delete from masterbilldft where posguid is not null
end
update VIPCard set VIPCard.IntegralPre = VIPCard.Integral
FROM VIPCard INNER JOIN
VIPCardType ON VIPCard.CardType_ID = VIPCardType.s_ID
WHERE (VIPCardType.Syb_Integral = 1)
if (@@Error<>0) goto ErrorPrc
update MoneyAccount set Act_Tot_0= Act_Tot
if (@@Error<>0) goto ErrorPrc
declare @auto_ID int, @tablename varchar(50), @createtable varchar(2000),@insertSQL varchar(8000)
select @auto_ID = max(autoID) from YearPeriod
select @tablename = 'moneyAccount' + cast(@auto_ID as varchar)
if not exists (select [name] from sysobjects where [name] [email protected] and type = 'U')
begin
select @createtable='create table '+ @tablename+' (autoID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, moneyaccount_s_ID int, act_tot_year numeric(20, 10))'
exec(@createtable)
if (@@Error<>0) goto ErrorPrc
select @insertSQL = 'insert into '+ @tablename+'(moneyaccount_s_ID, act_tot_year) select s_ID, act_tot_0 from MoneyAccount'
exec(@insertSQL)
if (@@Error<>0) goto ErrorPrc
end
else
begin
goto ErrorPrc
end
update MoneyAccount
set Act_Tot=0, Act_Tot_1=0, Act_Hap_1=0, Act_Tot_2=0, Act_Hap_2=0, Act_Tot_3=0,
Act_Hap_3=0, Act_Tot_4=0, Act_Hap_4=0, Act_Tot_5=0, Act_Hap_5=0, Act_Tot_6=0,
Act_Hap_6=0, Act_Tot_7=0, Act_Hap_7=0, Act_Tot_8=0, Act_Hap_8=0, Act_Tot_9=0,
Act_Hap_9=0, Act_Tot_10=0, Act_Hap_10=0, Act_Tot_11=0, Act_Hap_11=0, Act_Tot_12=0, Act_Hap_12=0
if (@@Error<>0) goto ErrorPrc
update MoneyAccount set Act_Tot_0=Act_Tot_0+(select Act_Tot_0 from MoneyAccount where s_FullID='0000500002' and s_Syb<>-1) where s_FullID='0000500001' and s_Syb<>-1
if (@@Error<>0) goto ErrorPrc
update MoneyAccount set Act_Tot_0=0 where s_FullID like '00003%' or s_FullID like '00004%' or s_FullID='0000500002'
if (@@Error<>0) goto ErrorPrc
update Units set AReceive_0=AReceive, APayDue_0=APayDue, AYuS_0=AYus,AYuF_0=AYuF
if (@@Error<>0) goto ErrorPrc
update Units set AReceive=0,APayDue=0,AYus=0,AYuF=0
if (@@Error<>0) goto ErrorPrc
dbcc checkident(Storage, RESEED, 1)
if (@@Error<>0) goto ErrorPrc
dbcc checkident(StorageDX, RESEED, 1)
if (@@Error<>0) goto ErrorPrc
dbcc checkident(MasterBill, RESEED, 1)
if (@@Error<>0) goto ErrorPrc
dbcc checkident(ListBuy, RESEED, 1)
if (@@Error<>0) goto ErrorPrc
dbcc checkident(ListSale, RESEED, 1)
if (@@Error<>0) goto ErrorPrc
dbcc checkident(ListOther, RESEED,1)
if (@@Error<>0) goto ErrorPrc
dbcc checkident(ListMoney, RESEED, 1)
if (@@Error<>0) goto ErrorPrc
dbcc checkident(ListProdCheck, RESEED, 1)
if (@@Error<>0) goto ErrorPrc
delete from dbo.SALESADVLIST where Prom_ID in (select s_id from SALESADV where Enddate < convert(varchar(10),getdate(),120))
if @@Error<>0 goto ErrorPrc
delete from SalesAdvUnits where Prom_ID in (select s_id from SALESADV where Enddate < convert(varchar(10),getdate(),120))
if @@Error<>0 goto ErrorPrc
delete from dbo.SALESADV where Enddate < convert(varchar(10),getdate(),120)
if @@Error<>0 goto ErrorPrc
delete from dbo.advzplistpzp where adv_list_id in (select list_id from dbo.advzplistp
where adv_id in (select s_id from advzplist where Enddate < convert(varchar(10),getdate(),120)))
if @@Error<>0 goto ErrorPrc
delete from dbo.advzplistp where adv_id in (select s_id from advzplist where Enddate < convert(varchar(10),getdate(),120))
if @@Error<>0 goto ErrorPrc
delete from dbo.advzplist where Enddate < convert(varchar(10),getdate(),120)
if @@Error<>0 goto ErrorPrc
delete from dbo.advdzlistpzp where adv_list_id in (select list_id from dbo.advdzlistp
where adv_id in (select s_id from advdzlist where Enddate < convert(varchar(10),getdate(),120)))
if @@Error<>0 goto ErrorPrc
delete from dbo.advdzlistp where adv_id in (select s_id from advdzlist where Enddate < convert(varchar(10),getdate(),120))
if @@Error<>0 goto ErrorPrc
delete from dbo.advdzlist where Enddate < convert(varchar(10),getdate(),120)
if @@Error<>0 goto ErrorPrc
delete from dbo.advrllistpzp where adv_list_id in (select list_id from dbo.advrllistp
where adv_id in (select s_id from advrllist where Enddate < convert(varchar(10),getdate(),120)))
if @@Error<>0 goto ErrorPrc
delete from dbo.advrllistp where adv_id in (select s_id from advrllist where Enddate < convert(varchar(10),getdate(),120))
if @@Error<>0 goto ErrorPrc
delete from dbo.advrllist where Enddate < convert(varchar(10),getdate(),120)
if @@Error<>0 goto ErrorPrc
delete from dbo.advzhlistpzp where adv_list_id in (select list_id from dbo.advzhlistp
where adv_id in (select s_id from advzhlist where Enddate < convert(varchar(10),getdate(),120)))
if @@Error<>0 goto ErrorPrc
delete from dbo.advzhlistp where adv_id in (select s_id from advzhlist where Enddate < convert(varchar(10),getdate(),120))
if @@Error<>0 goto ErrorPrc
delete from dbo.advzhlist where Enddate < convert(varchar(10),getdate(),120)
if @@Error<>0 goto ErrorPrc
if not exists ( select * from MasterBillDft )
begin
dbcc checkident(ListBuyDft, RESEED, 1)
dbcc checkident(ListSaleDft, RESEED, 1)
dbcc checkident(ListOtherDft, RESEED, 1)
dbcc checkident(ListProdCheckDft, RESEED, 1)
end
update VIPLogPay set s_Syb=1
if (@@Error<>0) goto ErrorPrc
update VIPLogConsume set Syb_JC=1
if (@@Error<>0) goto ErrorPrc
update SystemInfo set s_Values='Ini' where s_FullID='00002'
if (@@Error<>0) goto ErrorPrc
update SystemInfo set s_Values='0',s_SonCount=0 where s_FullID='00001'
if (@@Error<>0) goto ErrorPrc
delete from SystemInfo where s_ParentID='00001'
if (@@Error<>0) goto ErrorPrc
update SystemInfo set s_Values=convert(varchar(10), getdate(), 120) where s_FullID='00003'
if (@@Error<>0) goto ErrorPrc
if not exists ( select * from MasterOrder )
begin
truncate table BillSNDay
if (@@Error<>0) goto ErrorPrc
truncate table BillSNMonth
if (@@Error<>0) goto ErrorPrc
truncate table BillSNYear
if (@@Error<>0) goto ErrorPrc
update BuildID set ClassCurID=0 where Class_Name in ('MasterOrder', 'ListORDER' )
if (@@Error<>0) goto ErrorPrc
dbcc checkident(MasterOrder, RESEED, 1)
end else
begin
delete from BillSNDay where BillType not in (5, 6)
if (@@Error<>0) goto ErrorPrc
delete from BillSNMonth where BillType not in (5, 6)
if (@@Error<>0) goto ErrorPrc
delete from BillSNYear where BillType not in (5, 6)
if (@@Error<>0) goto ErrorPrc
if not exists ( select * from MasterOrder where BillType = 5)
begin
delete from BillSNDay where BillType = 5
if (@@Error<>0) goto ErrorPrc
delete from BillSNMonth where BillType = 5
if (@@Error<>0) goto ErrorPrc
delete from BillSNYear where BillType = 5
if (@@Error<>0) goto ErrorPrc
end
if not exists ( select * from MasterOrder where BillType = 6)
begin
delete from BillSNDay where BillType = 6
if (@@Error<>0) goto ErrorPrc
delete from BillSNMonth where BillType = 6
if (@@Error<>0) goto ErrorPrc
delete from BillSNYear where BillType = 6
if (@@Error<>0) goto ErrorPrc
end
end
if not exists ( select * from MasterSC )
begin
truncate table BillSNDay
if (@@Error<>0) goto ErrorPrc
truncate table BillSNMonth
if (@@Error<>0) goto ErrorPrc
truncate table BillSNYear
if (@@Error<>0) goto ErrorPrc
update BuildID set ClassCurID=0 where Class_Name in ('MasterSC', 'ListSC', 'ListSC1' )
if (@@Error<>0) goto ErrorPrc
dbcc checkident(MasterSC, RESEED, 1)
end else
begin
delete from BillSNDay where BillType not in (81, 85)
if (@@Error<>0) goto ErrorPrc
delete from BillSNMonth where BillType not in (81, 85)
if (@@Error<>0) goto ErrorPrc
delete from BillSNYear where BillType not in (81, 85)
if (@@Error<>0) goto ErrorPrc
if not exists ( select * from MasterOrder where BillType = 81)
begin
delete from BillSNDay where BillType = 81
if (@@Error<>0) goto ErrorPrc
delete from BillSNMonth where BillType = 81
if (@@Error<>0) goto ErrorPrc
delete from BillSNYear where BillType = 81
if (@@Error<>0) goto ErrorPrc
end
if not exists ( select * from MasterOrder where BillType = 85)
begin
delete from BillSNDay where BillType = 85
if (@@Error<>0) goto ErrorPrc
delete from BillSNMonth where BillType = 85
if (@@Error<>0) goto ErrorPrc
delete from BillSNYear where BillType = 85
if (@@Error<>0) goto ErrorPrc
end
end
delete List_InOutGroupPrint
where not exists (select q.List_ID from
(select BillState=0,n.BillType,a.Bill_ID,a.List_ID from
(select Bill_ID,List_ID from ListSaleDft
union all
select Bill_ID,List_ID from ListBuyDft
union all
select Bill_ID,List_ID from ListOtherDft) a left join MasterBillDft n on a.Bill_ID=n.s_ID
union all
select BillState=1,n.BillType,a.Bill_ID,a.List_ID from
(select Bill_ID,List_ID from ListSale
union all
select Bill_ID,List_ID from ListBuy
union all
select Bill_ID,List_ID from ListOther) a left join MasterBill n on a.Bill_ID=n.AutoID
union all
select BillState=1,n.BillType,a.Bill_ID,a.List_ID from
ListOrder a left join MasterOrder n on a.Bill_ID=n.s_ID) q where List_InOutGroupPrint.BillState=q.BillState and List_InOutGroupPrint.BillType=q.BillType and List_InOutGroupPrint.Bill_ID=q.Bill_ID and List_InOutGroupPrint.List_ID=q.List_ID)
if @i_bjdcheck =1
Begin
if exists ( SELECT ListOrder.Bill_ID
FROM ListOrder INNER JOIN
MasterOrder ON ListOrder.Bill_ID = MasterOrder.s_ID
WHERE (MasterOrder.BillType = 19) AND (ListOrder.s_Syb >= 0) AND
(MasterOrder.Audit_ID > 0) and enddate<@i_DateE )
delete masterorder from ListOrder INNER JOIN
MasterOrder ON ListOrder.Bill_ID = MasterOrder.s_ID
WHERE (MasterOrder.BillType = 19) AND (ListOrder.s_Syb >= 0) AND
(MasterOrder.Audit_ID > 0) and enddate<@i_DateE
end
if @i_DelStopBaseInfo =1
begin
delete Product where (isnull(ProdStatus, 0) = 1 and s_ID not in (select Prod_ID from ListSaledft
union all select Prod_ID from ListBuydft
union all select Prod_ID from ListOtherdft
union all select Prod_ID from ListOrder
union all select Prod_ID from Storage
union all select Prod_ID from Storagedx))
delete Product where s_Syb = -1
delete Units where (isnull(UnitStatus, 0) = 1 and s_ID not in (select Unit_ID from MasterBilldft where isnull(LoginDate, '') <> '' and BillState >=0
union all select Unit_ID from Masterorder where isnull(LoginDate, '') <> '' and BillState >=0
union all select Stor_ID Unit_ID from Storagedx)) --or s_Syb = -1
delete Units where s_Syb = -1
end
if @i_DelFinishedOrder =1
begin
delete MasterOrder where Performstatus = 2 and BillType in (5, 6)
delete ListOrder where bill_ID not in (select s_ID from MasterOrder)
end
if @i_DelStopVip =1
begin
Delete VipCard where s_Syb = 2
end
truncate table DeductPeriod
if (@@Error<>0) goto ErrorPrc
if not exists(select * from DeductPeriod where s_id=1)
insert DeductPeriod(s_ID,s_Syb,s_Class,u_Name)values (1,1,0,'19')
if (@@Error<>0) goto ErrorPrc
truncate table EmpDeductPeriod
if (@@Error<>0) goto ErrorPrc
truncate table BillTitleCustomItem
if (@@Error<>0) goto ErrorPrc
truncate table BillTitleCustomItems
if (@@Error<>0) goto ErrorPrc
truncate table BillTitleCustomItemsdft
if (@@Error<>0) goto ErrorPrc
commit tran P_SysSettleAccountsPrc
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
return 0
end
Error:
if IsNull(@Result, 0)>=0 set @Result=-1
select @o_HintInfo = dbo.F_GetHintInfo(@Result)
return @Result
ErrorPrc:
rollback tran P_SysSettleAccountsPrc
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
if IsNull(@Result, 0)>=0 set @Result=-1
select @o_HintInfo = dbo.F_GetHintInfo(@Result)
return @Result
ErrorPrc_cur:
Close Cur_GetMoneyAccount
deallocate Cur_GetMoneyAccount
rollback tran P_SysSettleAccountsPrc
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
if IsNull(@Result, 0)>=0 set @Result=-1
select @o_HintInfo = dbo.F_GetHintInfo(@Result)
return @Result
3、重新月结或年结就可以了。
大同大洋软件(大同用友软件) 电话：0352-5388430 13994439175
-
{eyou:pagebreak/}