--V1.7.7所作升级
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-10-08'
    Return
end
 

if not Exists(Select * from SysColumns where Id=Object_ID('wCustBook') and name='PaidTotal' )
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]
 -- ALTER TABLE [dbo].[wCustBook] Alter Column BillNo Char(14) not Null
 -- ALTER TABLE [dbo].[wCustBook] Alter Column BillType Char(1) not Null
end

--修改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 
            
Select BKDate,CustCode,FormNo,FType,WTotal,THTotal,DscTotal,PayTotal,CTotal,CPayTotal,BillNo,BillType,CDscTotal,NoPayTotal,PaidTotal,timeMark 
 into #Rpt 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'

--结算过的销货、退货、费用单
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'   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 
) 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 #Rpt order by timeMark')

drop table #Rpt

--主键 

ALTER TABLE [dbo].[wCustBook] Alter Column FormNo Char(14) not Null
ALTER TABLE [dbo].[wCustBook] Alter Column FType Char(1) not Null
Exec ('
ALTER TABLE [dbo].[wCustBook] WITH NOCHECK 
ADD CONSTRAINT [PK_wCustBook] PRIMARY KEY  CLUSTERED 
	(
		[FormNo],
		[FType],
		[BillNo],
		[BillType]
	)  ON [PRIMARY] ')
	
--前台修改单价
insert into tSysFunc 
Values('MdfPrice','修改单价','ZZ',1,0,0,0,0,0,0,0,0,0,0,'',0,9,'')