--V1.61所作升级
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-03-20'
    Return
end

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin

CREATE TABLE [dbo].[CustType] (
	[TypeCode] [varchar] (4) NOT NULL ,
	[TypeName] [varchar] (60) NULL ,
	[Remark] [varchar] (60) NULL 
) ON [PRIMARY]

ALTER TABLE [dbo].[CustType] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[TypeCode]
	)  ON [PRIMARY] 
	
end

--添加TypeCode字段
if not Exists(Select * from SysColumns where Id=Object_ID('tCustomer') and name='TypeCode' )
begin
  alter table tCustomer add TypeCode Varchar(4) Default '' with Values
end

--添加费用字段
if not Exists(Select * from SysColumns where Id=Object_ID('FWXHH') and name='expenses' )
begin
  alter table FWXHH add expenses money Default 0 with Values
end

--添加CustType字段
if not Exists(Select * from SysColumns where Id=Object_ID('FWJSH') and name='CustType' )
begin
  alter table FWJSH add CustType char(1) Default '0' with Values
end

--添加CustCode,CustName字段
if not Exists(Select * from SysColumns where Id=Object_ID('FWJSB') and name='CustCode' )
begin
  alter table FWJSB add CustCode Varchar(10) Default '' with Values
  alter table FWJSB add CustName Varchar(40) Default '' with Values
  Exec ('Update FWJSB set CustCode=FWJSH.CustCode from FWJSH where FWJSB.FormNo=FWJSH.FormNo and FWJSH.FType=''0'' ')
  Exec ('Update FWJSB set CustName=a.CustName from tCustomer a where a.CustCode=FWJSB.CustCode ')

end

if not Exists(Select * from SysColumns where Id=Object_ID('FWJSBDetail') and name='CustCode' )
begin
  alter table FWJSBDetail add CustCode Varchar(10) Default '' with Values
  alter table FWJSBDetail add CustName Varchar(40) Default '' with Values
  Exec ('Update FWJSBDetail set CustCode=a.CustCode from FWJSH a where FWJSBDetail.JSFormNo=a.FormNo and a.FType=''0'' ')
  Exec ('Update FWJSBDetail set CustName=a.CustName from tCustomer a where a.CustCode=FWJSBDetail.CustCode ')

end