--V1.7.8所作升级
if exists( Select OptValue from tSysOpt where OptName='DBEdition')
begin
  Declare @DBVer varchar(10)
  Select @DBVer=OptValue from tSysOpt where OptName='DBEdition'
  Set @DBVer=Substring(@DBVer,1,4)+'-'+Substring(@DBVer,6,2)+'-'+Substring(@DBVer,9,2)
  if @DBVer>='2007-11-08'
    Return
end

/*
第一种情况：表中有数据，由于升级失败，字段添加了，内容为空，没有生成主键
第二种情况：表中无数据，字段添加成功(BillNo没有默认值)，没有生成主键
*/
/*
if Exists(Select * from SysColumns where Id=Object_ID('wCustBook') and name='BillNo' )
begin  
 
  if (Select  b.Name from SysColumns a left join SysObjects b on  a.CDefault=b.ID and b.xType='D'  and a.ID=b.parent_Obj
   where a.ID=Object_ID('wCustBook') and a.Name='BillNo') is Null
  begin
   
    Alter table wCustBook drop DF_WCustBook_CPayTotal,DF_WCustBook_CDscTotal,DF_WCustBook_NoPayTotal,DF_WCustBook_PaidTotal
    Alter table wCustBook drop Column BillNo,BillType,CPayTotal,CDscTotal,NoPayTotal,PaidTotal
   
    Alter table wCustBook add [BillNo] [char] (14) not null default '' with values,
	[BillType] [char] (1) not null default '' with values,
	[CPayTotal] [money] null,
 	[CDscTotal] [money] null,
	[NoPayTotal] [money] null, 
	[PaidTotal] [money] null  
    ALTER TABLE [wCustBook] WITH NOCHECK ADD 
	CONSTRAINT [DF_WCustBook_CPayTotal] DEFAULT (0) FOR [CPayTotal],
	CONSTRAINT [DF_WCustBook_CDscTotal] DEFAULT (0) FOR [CDscTotal],
	CONSTRAINT [DF_WCustBook_NoPayTotal] DEFAULT (0) FOR [NoPayTotal],
	CONSTRAINT [DF_WCustBook_PaidTotal] DEFAULT (0) FOR [PaidTotal]
  end
end else begin

  Alter table wCustBook add [BillNo] [char] (14) not null default '' with values,
	[BillType] [char] (1) not null default '' with values,
	[CPayTotal] [money] null,
 	[CDscTotal] [money] null,
	[NoPayTotal] [money] null, 
	[PaidTotal] [money] null  
  ALTER TABLE [wCustBook] WITH NOCHECK ADD 
	CONSTRAINT [DF_WCustBook_CPayTotal] DEFAULT (0) FOR [CPayTotal],
	CONSTRAINT [DF_WCustBook_CDscTotal] DEFAULT (0) FOR [CDscTotal],
	CONSTRAINT [DF_WCustBook_NoPayTotal] DEFAULT (0) FOR [NoPayTotal],
	CONSTRAINT [DF_WCustBook_PaidTotal] DEFAULT (0) FOR [PaidTotal]

end
*/
if (Select OptValue from tSysOpt where OptName='DBEdition')='2007.10.08'
  and (Select Count(*) as iCount from wCustBook)>0 and Exists(Select * from wCustBook where BillNo is Null)
begin

--修改FWJSB(明细结算)
Update FWJSB  Set YTotal=b.YPayTotal
from 
(Select b.FormNo,b.ProdCode,b.FType,YPayTotal=c.YPayTotal-
(Select IsNull(Sum(bb.ProTotal+bb.DscTotal),0)
 from FWJSB bb join FWJSH aa on bb.FormNo=aa.FormNo and aa.CheckType='3'
 where bb.timeMark<b.timeMark and bb.ProdCode=b.ProdCode and bb.FType=b.FType)

,b.ProTotal,b.DscTotal,b.CustCode,b.CustName,a.CheckType
from FWJSB b join FWJSH a  on a.FormNo = b.FormNo and a.CheckType='3' and a.JSType='1'

join wCustPay c on b.ProdCode=c.FormNo and b.FType=c.FType ) as b
where FWJSB.FormNo=b.FormNo and FWJSB.ProdCode=b.ProdCode and FWJSB.FType=b.FType 

--重新排列wCustBook数据            
Select BKDate,CustCode,FormNo,FType,WTotal,THTotal,DscTotal,PayTotal,CTotal,CPayTotal,BillNo,BillType,CDscTotal,NoPayTotal,PaidTotal,timeMark 
 into #Rpt1 from
(
--销货单
Select a.BKDate,a.CustCode,a.FormNo,a.FType, a.WTotal,a.THTotal,a.DscTotal,a.PayTotal,a.CTotal
,CPayTotal=Case b.PayMode when '1' then 0 else a.CTotal end
,BillNo=a.FormNo,BillType='0',CDscTotal=0
,NoPayTotal=Case b.PayMode when '0' then 0 else a.CTotal end 
,PaidTotal=0,a.timeMark,iIndex=0
from wCustBook a join FWXHH b on a.FormNo=b.FormNo and a.FType='1'
union 
--退货单
Select a.BKDate,a.CustCode,a.FormNo,a.FType, a.WTotal,a.THTotal,a.DscTotal,a.PayTotal,a.CTotal
,CPayTotal=Case b.PayMode when '1' then 0 else a.CTotal end
,BillNo=a.FormNo,BillType='1',CDscTotal=0
,NoPayTotal=Case b.PayMode when '0' then 0 else a.CTotal end 
,PaidTotal=0,a.timeMark,iIndex=0
from wCustBook a join FWTHH b on a.FormNo=b.FormNo and a.FType='2'

--费用单
Union
Select a.BKDate,a.CustCode,a.FormNo,a.FType, a.WTotal,a.THTotal,a.DscTotal,a.PayTotal,a.CTotal
,CPayTotal=Case b.FKType when '1' then 0 else a.CTotal end
,BillNo=a.FormNo,BillType='2',CDscTotal=0
,NoPayTotal=Case b.FKType when '0' then 0 else a.CTotal end 
,PaidTotal=0,a.timeMark,iIndex=0
from wCustBook a join FWFeeH b on a.FormNo=b.FormNo and a.FType='5'

--预收款
Union
Select a.BKDate,a.CustCode,a.FormNo,a.FType, a.WTotal,a.THTotal,a.DscTotal,a.PayTotal,a.CTotal
,CPayTotal=0
,BillNo=a.FormNo,BillType='3',CDscTotal=0
,NoPayTotal=CTotal,PaidTotal=0,a.timeMark,iIndex=0
from wCustBook a where a.FType='4'

--结算过的销货、退货、费用单(BillNo字段是NULL 的结算数据)
Union

Select a.BKDate,a.CustCode,a.FormNo,a.FType, a.WTotal,a.THTotal
--减掉同一个结算单中其它单据的折让之合
,DscTotal=a.DscTotal-
(Select IsNull(Sum(DscTotal),0) from FWJSB as bb 
where bb.FormNo=b.FormNo and bb.CustCode=b.CustCode and bb.timeMark>b.timeMark)
--减掉同一个结算单中其它单据的付款之合
,PayTotal=a.PayTotal-
(Select IsNull(Sum(ProTotal),0) from FWJSB as bb 
where bb.FormNo=b.FormNo and bb.CustCode=b.CustCode and bb.timeMark>b.timeMark)

,CTotal=b.ProTotal+b.DscTotal,CPayTotal=b.ProTotal,BillNo=b.ProdCode,BillType=b.FType
,CDscTotal=b.DscTotal,NoPayTotal=b.YTotal-b.ProTotal-b.DscTotal

,PaidTotal=(Select IsNull(Sum(bb.ProTotal+bb.DscTotal),0) 

from FWJSB as bb join FWJSH aa on bb.FormNo=aa.FormNo and aa.CheckType='3'

where bb.ProdCode=b.ProdCode and b.FType=bb.FType and bb.timeMark<b.timeMark),a.timeMark,iIndex=b.timeMark
from 
(--避免数据重复  表wCustBook中 JS200703210008 结算单据数据重复
 Select Distinct BKDate,CustCode,FormNo,FType, WTotal,THTotal,DscTotal,PayTotal,timeMark=Min(timeMark)
 from wCustBook  where FType='3' 
 and BillNo is Null  
 Group by BKDate,CustCode,FormNo,FType, WTotal,THTotal,DscTotal,PayTotal
 ) a join FWJSB b on a.FormNo=b.FormNo and a.CustCode=b.CustCode
--order by a.timeMark,iIndex 

Union ALL --升级失败后新增的结算数据(BillNO 字段 不为空的数据)
Select BKDate,CustCode,FormNo,FType,WTotal,THTotal,DscTotal,PayTotal,CTotal,CPayTotal,BillNo,BillType,CDscTotal,NoPayTotal,PaidTotal,timeMark,iIndex=0
from wCustBook where (BillNo is not Null) and FType='3'
) as a order by timeMark,iIndex

truncate table wCustBook

Exec ('insert into wCustBook(
BKDate,CustCode,FormNo,FType,WTotal,THTotal,DscTotal,PayTotal,CTotal,CPayTotal,BillNo,BillType,CDscTotal,NoPayTotal,PaidTotal)

Select BKDate,CustCode,FormNo,FType,WTotal,THTotal,DscTotal,PayTotal,CTotal,CPayTotal,BillNo,BillType,CDscTotal,NoPayTotal,PaidTotal from #Rpt1 order by timeMark')

drop table #Rpt1

end

if not Exists(Select * from SysObjects where xType='PK' and Parent_Obj=Object_ID('wCustBook') )
begin
--主键 

ALTER TABLE [dbo].[wCustBook] Alter Column FormNo Char(14) not Null
ALTER TABLE [dbo].[wCustBook] Alter Column FType Char(1) not Null
ALTER TABLE [dbo].[wCustBook] Alter Column BillNo Char(14) not Null
ALTER TABLE [dbo].[wCustBook] Alter Column BillType Char(1) not Null
Exec ('
ALTER TABLE [dbo].[wCustBook] WITH NOCHECK 
ADD CONSTRAINT [PK_wCustBook] PRIMARY KEY  CLUSTERED 
	(
		[FormNo],
		[FType],
		[BillNo],
		[BillType]
	)  ON [PRIMARY] ')
end

if not Exists(Select * from tSysFunc where FuncCode='MdfPrice')
begin
  insert into tSysFunc 
  Values('MdfPrice','修改单价','ZZ',1,0,0,0,0,0,0,0,0,0,0,'',0,9,'')
end
--**********1.7.8
--**********************升级wCustBook

Select BKDate,CustCode,FormNo,FType,CTotal,WTotal,THTotal,DscTotal,PayTotal
,BillNo,BillType,CPayTotal,CDscTotal,NoPayTotal,PaidTotal,
SumFeeTotal=IsNull(
 ( Select NoPayTotal=Sum(NoPayTotal) from 
(Select NoPayTotal=Min(Abs(NoPayTotal))* (Case when dd.Total<0 then -1 else 1 end),
BillNo,BillType from wCustBook cc join FWFeeH dd on cc.BillNo=dd.FormNo  where BillType = '2'   and cc.timeMark<=a.timeMark  and cc.CustCode=a.CustCode 
group by BillNo,BillType,dd.Total -- order by timeMark
)as b   ),0.00)
 
,

SumNoPayTotal=IsNull(
(Select sum(NoPayTotal) from 
(Select NoPayTotal=Min(Abs(NoPayTotal))*(Case BillType when '1' then -1 else 1 end),
BillNo,BillType from wCustBook  cc where cc.BillType in ('0','1')  and cc.timeMark<=a.timeMark and cc.CustCode=a.CustCode 
group by BillNo,BillType --having Min(Abs(NoPayTotal))<>0 
)as b  )   
,0.00)
 
,
SumPrePay=IsNull((select Sum(CTotal) from wCustBook aa where aa.BillType='3' and aa.FType='4' and aa.timeMark<=a.timeMark and aa.CustCode=a.CustCode),0.00) 
-IsNull(
(
Select Sum(Prepay) from (
select Distinct aa.FormNo,bb.Prepay
from wCustBook aa join FWJSH bb on aa.FormNo=bb.FormNo and aa.FType='3' and bb.Prepay<>0 and aa.CustCode=a.CustCode 
where aa.timeMark<=a.timeMark ) as a1 )
 ,0.00) ,IDENTITY(int, 1,1) AS ID 

into #wCustBook
from wCustBook a  Order by timeMark

Set Identity_Insert #wCustBook off
Set Identity_Insert #wCustBook on

--结算单使用预收款 
insert into #wCustBook(BKDate,CustCode,FormNo,FType,CTotal,WTotal,THTotal,DscTotal,PayTotal,BillNo,BillType,CPayTotal,CDscTotal
,NoPayTotal,PaidTotal,SumFeeTotal,SumNoPayTotal,SumPrePay,ID)

Select a.BKDate,a.CustCode,a.FormNo,a.FType,CTotal=c.Prepay,a.WTotal,a.THTotal,DscTotal=a.DscTotal-a.CDscTotal,PayTotal=a.PayTotal-a.CPayTotal,BillNo='',BillType='4'
,CPayTotal=c.Prepay,CDscTotal=0.00
,NoPayTotal=SumPrePay
--统计之前结算单使用的预收款
,PaidTotal=
IsNull(
(
Select Sum(Prepay) from (
select Distinct aa.FormNo,bb.Prepay
from #wCustBook aa join FWJSH bb on aa.FormNo=bb.FormNo and aa.FType='3' and bb.Prepay<>0 and aa.CustCode=a.CustCode 
where aa.ID<a.ID ) as a1 )
 ,0.00) 

,SumFeeTotal=SumFeeTotal+(Case when BillType in ('2') then a.CPayTotal+a.CDscTotal else 0.00 end)
,SumNoPayTotal=SumNoPayTotal+(Case when BillType in ('0','1') then a.CPayTotal+a.CDscTotal else 0.00 end)
,SumPrePay,a.ID
 
from #wCustBook a join 
(
Select Min(ID)as id,FormNo from #wCustBook where left(FType,1)='3'  Group by FOrmNo 
) as b on a.ID=b.ID and a.FormNo=b.FormNo
join FWJSH c on a.FormNo=c.FormNo and a.CustCode=c.CustCode where c.Prepay<>0
 
Set Identity_Insert #wCustBook off



truncate table wCustBook

if not Exists(Select * from SysColumns where Id=Object_ID('wCustBook') and name='SumNoPayTotal' )
begin
  alter table wCustBook add 	
	[SumNoPayTotal] [money] not null Default 0.00 With Values,
	[SumFeeTotal] [money] not null Default 0.00 With Values,
	[SumPrePay] [money] not null Default 0.00 With Values 
end

Exec('Insert into wCustBook (BKDate,CustCode,FormNo,FType,CTotal,WTotal,THTotal,DscTotal,PayTotal,BillNo,BillType,CPayTotal,CDscTotal
,NoPayTotal
,PaidTotal
,SumFeeTotal,SumNoPayTotal,SumPrePay )
select BKDate,CustCode,FormNo,FType,CTotal,WTotal,THTotal,DscTotal,PayTotal,BillNo,BillType,CPayTotal,CDscTotal
,NoPayTotal
,PaidTotal
,SumFeeTotal,SumNoPayTotal,SumPrePay 
 
from #wCustBook  
order by ID,BillNo  ')

drop table #wCustBook 
--Select * from wCustBook order by timeMark
/*
1	0  新增销货单
2       1  新增退货单
4       3  新增预收款
5       2  新增费用单
3       0  结算销货
3       1  结算退货
3       2  结算费用
3       4  结算使用预收款
*/


--××××××××××××××××××升级SuppBook

--新增验收单
Select BKDate,SupCode,FormNo,FType,CTotal,JHTotal,THTotal,DscTotal,PayTotal,BillNo,BillType,CPayTotal,CDscTotal,NoPayTotal,PaidTotal,Identity(int,1,1) as RecNo,tag=0
into #SuppBook
  from
(
Select a.BKDate,a.TimeMark,iIndex=0,a.SupCode,a.FormNo,a.FType,a.CTotal,a.JHTotal,a.THTotal,a.DscTotal,a.PayTotal,
BillNo=a.FormNo,BillType='0',CPayTotal=0,CDscTotal=0,NoPayTotal=Cost,PaidTotal=0
 
from SuppBook a join FYSH b on a.FormNo=b.FormNo and a.FType='1' and b.CheckType='3'
Union All
--新增退货单
Select a.BKDate,a.TimeMark,iIndex=0,a.SupCode,a.FormNo,a.FType,a.CTotal,a.JHTotal,a.THTotal,a.DscTotal,a.PayTotal,
BillNo=a.FormNo,BillType='1',CPayTotal=0,CDscTotal=0,NoPayTotal=Cost,PaidTotal=0
from SuppBook a join FTHH b on a.FormNo=b.FormNo and a.FType='2' and b.CheckType='3'
Union All
--新增进价调整单
Select a.BKDate,a.TimeMark,iIndex=0,a.SupCode,a.FormNo,a.FType,a.CTotal,a.JHTotal,a.THTotal,a.DscTotal,a.PayTotal,
BillNo=a.FormNo,BillType='2',CPayTotal=0,CDscTotal=0,NoPayTotal=CostDif,PaidTotal=0
from SuppBook a join FJTJH b on a.FormNo=b.FormNo and a.FType='A' and b.CheckType='3'
Union All
--新增费用单
Select a.BKDate,a.TimeMark,iIndex=0,a.SupCode,a.FormNo,a.FType,a.CTotal,a.JHTotal,a.THTotal,a.DscTotal,a.PayTotal,
BillNo=a.FormNo,BillType='6',CPayTotal=Case when FKType=0 then Total else 0 end,CDscTotal=0.00,
NoPayTotal=Case when FKType=0 then 0 else Total end,PaidTotal=0.00
from SuppBook a join FFeeH b on a.FormNo=b.FormNo and a.FType='6' and b.CheckType='3' 
Union All
--新增预付款单
Select a.BKDate,a.TimeMark,iIndex=0,a.SupCode,a.FormNo,a.FType,a.CTotal,a.JHTotal,a.THTotal,a.DscTotal,a.PayTotal,
BillNo=a.FormNo,BillType='7',CPayTotal=0,CDscTotal=0,NoPayTotal=ProTotal,PaidTotal=0
from SuppBook a join FPrepay b on a.FormNo=b.FormNo and a.FType='7' and b.CheckType='3'
Union All
--结算过的单据
Select a.BKDate,a.TimeMark,b.timeMark as iIndex,a.SupCode,a.FormNo,a.FType,CTotal=b.ProTotal+b.DscTotal,a.JHTotal,a.THTotal,
DscTotal=a.DscTotal-
IsNull((Select Sum(DscTotal) from FJXJSB aa where aa.FormNo=a.FormNo and aa.timeMark>b.timeMark ),0.00),

PayTotal=a.PayTotal-
IsNull((Select Sum(ProTotal) from FJXJSB aa where aa.FormNo=a.FormNo and aa.timeMark>b.timeMark ),0.00),
BillNo=b.ProdCode,BillType=b.FType,CPayTotal=b.ProTotal,CDscTotal=b.DscTotal,
NoPayTotal=b.YTotal-b.ProTotal-b.DscTotal,
PaidTotal=IsNull((Select Sum(aa.ProTotal+aa.DscTotal) from FJXJSB aa join FJXJSH bb on aa.FormNo=bb.FormNo and bb.CheckType='3' 
where aa.timeMark<b.timeMark and aa.ProdCode=b.ProdCode and aa.FType=b.FType),0.00)

from SuppBook a join FJXJSB b on a.FormNo=b.FormNo and a.FType='3' join FJXJSH c on a.FormNo=c.FormNo and c.CheckType='3'
) as Book
order by timeMark,iIndex



Set Identity_insert #SuppBook off 
Set IDENTITY_INSERT #SuppBook on

--插入结算使用预付款流水
insert into #SuppBook
(BKDate,SupCode,FormNo,FType,CTotal,JHTotal,THTotal,DscTotal,PayTotal,BillNo,BillType,CPayTotal,CDscTotal,NoPayTotal,PaidTotal,RecNo,Tag)
Select a.BKDate,a.SupCode,a.FormNo,a.FType,CTotal=c.Prepay,a.JHTotal,a.THTotal,DscTotal=a.DscTotal-a.CDscTotal
,PayTotal=a.PayTotal-a.CPayTotal,BillNo='',BillType='7',
CPayTotal=c.Prepay,CDscTotal=0.00
,NoPayTotal=
--
IsNull( (Select Sum(CTotal) from #SuppBook aa where aa.FType='7' and aa.RecNo<a.RecNo and aa.SupCode=a.SupCode),0.00)
-
IsNull( (Select Sum(Prepay) from FJXJSH aa where aa.CheckType='3' and aa.timeMark<=c.timeMark and aa.Prepay<>0 and aa.CustCode=a.SupCode),0.00)
--
,PaidTotal=
IsNull( (Select Sum(Prepay) from FJXJSH aa where aa.CheckType='3' and aa.timeMark<c.timeMark and aa.Prepay<>0 and aa.CustCode=a.SupCode),0.00) 
,a.RecNo,Tag=-2
from 
#SuppBook a join 
(Select min(RecNo) as RecNo,FormNo from #SuppBook where FType='3' Group by FormNo) b on a.FormNo=b.FormNo and a.RecNo=b.RecNo and a.Tag=0
 join FJXJSH c on a.FormNo=c.FormNo where a.FType='3' and c.Prepay<>0

--插入结算使用费用流水
insert into #SuppBook
(BKDate,SupCode,FormNo,FType,CTotal,JHTotal,THTotal,DscTotal,PayTotal,BillNo,BillType,CPayTotal,CDscTotal,NoPayTotal,PaidTotal,RecNo,Tag)
Select a.BKDate,a.SupCode,a.FormNo,a.FType,CTotal=c.FeeMoney,a.JHTotal,a.THTotal,DscTotal=a.DscTotal-a.CDscTotal
,PayTotal=a.PayTotal-a.CPayTotal,BillNo='',BillType='6',
CPayTotal=c.FeeMoney,CDscTotal=0.00
,NoPayTotal=
--
IsNull( (Select Sum(CTotal) from #SuppBook aa where aa.FType='6' and aa.RecNo<a.RecNo and aa.SupCode=a.SupCode),0.00)
-
IsNull( (Select Sum(FeeMoney) from FJXJSH aa where aa.CheckType='3' and aa.timeMark<=c.timeMark and aa.FeeMoney<>0 and aa.CustCode=a.SupCode),0.00)
--
,PaidTotal=
IsNull( (Select Sum(FeeMoney) from FJXJSH aa where aa.CheckType='3' and aa.timeMark<c.timeMark and aa.FeeMoney<>0 and aa.CustCode=a.SupCode),0.00) 
,a.RecNo,Tag=-1
from 
#SuppBook a join 
(Select min(RecNo) as RecNo,FormNo from #SuppBook where FType='3' Group by FormNo) b on a.FormNo=b.FormNo and a.RecNo=b.RecNo and a.Tag=0
 join FJXJSH c on a.FormNo=c.FormNo where a.FType='3' and c.FeeMoney<>0



Set Identity_insert #SuppBook off

Select BKDate,SupCode,FormNo,FType,CTotal,JHTotal,THTotal,DscTotal,PayTotal,BillNo,BillType,CPayTotal,CDscTotal,NoPayTotal,PaidTotal,Identity(int,1,1) as ID
into #Rpt from #SuppBook order by RecNo,Tag

drop table #SuppBook

truncate table SuppBook
if not Exists(Select * from SysColumns where Id=Object_ID('SuppBook') and name='SumNoPayTotal' )
begin
  alter table SuppBook add 
	[BillNo] [char] (14) not null default '' with values,
	[BillType] [char] (1) not null default '' with values,
	[CPayTotal] [money] not null Default 0 With Values,
 	[CDscTotal] [money] not null Default 0 With Values,
	[NoPayTotal] [money] not null Default 0 With Values, 
	[PaidTotal] [money] not null Default 0 With Values,
	[SumNoPayTotal] [money] not null Default 0 With Values,
	[SumFeeTotal] [money] not null Default 0 With Values,
	[SumPrePay] [money] not null Default 0 With Values 
end

 
Exec('Insert into SuppBook (BKDate,SupCode,FormNo,FType,CTotal,JHTotal,THTotal,DscTotal,PayTotal,BillNo,BillType,CPayTotal,CDscTotal
,NoPayTotal
,PaidTotal
,SumNoPayTotal,SumFeeTotal,SumPrePay )
--查询结果
Select BKDate,SupCode,FormNo,FType,CTotal,JHTotal,THTotal
,DscTotal,PayTotal,BillNo,BillType,CPayTotal,CDscTotal,NoPayTotal,PaidTotal
--当日欠供应商的合计金额
,SumNoPayTotal= (Select IsNull(Sum(NoPayTotal),0.00) from 

( Select IsNull( Min(Abs(NoPayTotal)) * bb.Flag ,0.00) as NoPayTotal,BillNo from #Rpt aa 
join (Select FormNo,Case when YPayTotal>0 then 1 else -1 end as Flag,FType from SuppPay) bb
on aa.BillNO=bb.FormNO and aa.BillType=bb.FType
where   aa.ID<=a.ID and aa.SupCode=a.SupCode
 
and aa.BillType in (''0'',''1'',''2'')
Group by BillNo,bb.Flag
) as tbl  
) 
--当日费用总额
,SumFeeTotal= ( Select IsNull(Sum(NoPayTotal),0.00) from #Rpt aa where aa.ID<=a.ID and aa.FType+aa.BillType=''66'' and aa.SupCode=a.SupCode)
- ( Select IsNull(Sum(CPayTotal),0.00) from #Rpt aa where aa.ID<=a.ID and aa.FType+aa.BillType=''36'' and aa.SupCode=a.SupCode)
--当日预付款总额
,SumPrepay = ( Select IsNull(Sum(NoPayTotal),0.00) from #Rpt aa where aa.ID<=a.ID and aa.FType+aa.BillType=''77'' and aa.SupCode=a.SupCode)
- ( Select IsNull(Sum(CPayTotal),0.00) from #Rpt aa where aa.ID<=a.ID and aa.FType+aa.BillType=''37'' and aa.SupCode=a.SupCode)
 from #Rpt a order by ID ')


drop table #Rpt

--Select * from SuppBook order by timeMark
/*SuppBook
1  商品验收单
2  商品退货单
3  供应商结算单
6  供应商费用单
7  预付款单
A  进价调整单
*/
/* FJXJSB,SuppPay
0  商品验收单
1  商品退货单
2  进价调整单
*/
--添加车辆管理
if not Exists(Select * from SysObjects where Id=Object_ID('Vehicle') and xType='U')
begin
  Create table Vehicle
  (
  VehicleNo Varchar(6) not null,
  VehicleLicense Varchar(20)
  )

  ALTER TABLE [dbo].[Vehicle] WITH NOCHECK ADD 
  	CONSTRAINT [PK_Vehicle] PRIMARY KEY  CLUSTERED 
  (
  	[VehicleNo]
  )  ON [PRIMARY] 

Alter Table FWDHH Add VehicleNo varchar(6) not null Default '' with Values

Alter Table FWDHH_History Add VehicleNo varchar(6) not null Default '' with Values

Alter Table FWXHH Add VehicleNo varchar(6) not null Default '' with Values

Alter Table FWTHH Add VehicleNo varchar(6) not null Default '' with Values
end 



--客户
if not Exists(Select * from SysColumns where Id=Object_ID('tCustomer') and name='FeeTotal' )
begin
  alter table tCustomer add 
	[NoPayTotal] [money] not null default (0.00) with values,
	[FeeTotal] [money]  not null default (0.00) with values
end
 
Exec(' update tCustomer Set NoPayTotal=aa.SumNoPayTotal,FeeTotal=aa.SumFeeTotal
from
(
Select b.* from wCustBook b join
(Select CustCode,timeMark=Max(timeMark) from wCustBook group by CustCode) as a
 on b.CustCode= a.CustCode and b.timeMark=a.timeMark
) as aa
where tCustomer.CustCode=aa.CustCode ')

--供应商
if not Exists(Select * from SysColumns where Id=Object_ID('tSupply') and name='NoPayTotal')
begin
  alter table tSupply add 
	[NoPayTotal] [money] not null default (0.00) with values
end
 
Exec(' update tSupply Set NoPayTotal=aa.SumNoPayTotal
from
(
Select b.* from SuppBook b join
(Select SupCode,timeMark=Max(timeMark) from SuppBook group by SupCode) as a
 on b.SupCode= a.SupCode and b.timeMark=a.timeMark
) as aa
where tSupply.SupCode=aa.SupCode ')
 
 
 --单据明细排列顺序

--use PFDBMis

Declare @i int
Select @i=IsNull(Max(SerialNo),0)+1 from tSysFunc where iNodeCode='02'
insert into tSysFunc 
Values('K0211','车辆管理','02',1,1,1,1,1,1,1,1,0,0,1,'tfrmVehicle',0,@i,'基本资料/K0211')