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>='2006-08-18'
    Return
end

--2006-05-31
if not Exists(Select * from SysColumns where Id=Object_ID('tUser') and name='MaxDscRate' )
  alter table tUser add MaxDscRate money null default 0
--2006-06-01
if (Select Caption from UserRptParam where QryNo=46 and ParamName='CustCode')='业务员' 
  update UserRptParam Set Caption='批发客户' where QryNo=46 and ParamName='CustCode'
  
--2006-08-15

  --FWJSH添加JSType字段
  if not Exists(Select * from SysColumns where name='JSType' and id=Object_ID('FWJSH'))
  begin
  Alter table FWJSH add [JSType] [char] (1)
  ALTER TABLE [FWJSH] WITH NOCHECK ADD 
  	CONSTRAINT [DF_FWJSH_JSType] DEFAULT ('0') FOR [JSType]
  Exec ('update FWJSH  Set JSType=''0'' where JSType is null')
  end
  --
  
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FWJSBDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[FWJSBDetail] (
	[JSFormNo] [char] (14) NOT NULL ,
	[FormNo] [char] (14) NOT NULL ,
	[FType] [char] (1) NOT NULL ,
	[FormDate] [char] (10) NULL ,
	[PayDate] [char] (10) NULL ,
	[ProdCode] [char] (18) NOT NULL ,
	[BarCode] [char] (18) NOT NULL ,
	[ProdName] [char] (40) NOT NULL ,
	[YPayCountN] [money] NULL ,
	[PayCountN] [money] NULL ,
	[ProPrice] [money] NULL ,
	[YPayTotal] [money] NULL ,
	[PayTotal] [money] NULL ,
	[DscTotal] [money] NULL ,
	[ProMemo] [varchar] (2000) NULL ,
	[timeMark] [int] IDENTITY (1, 1) NOT NULL ,
	[ProdType] [char] (1) NULL 
) ON [PRIMARY]

ALTER TABLE [dbo].[FWJSBDetail] WITH NOCHECK ADD 
	CONSTRAINT [DF_FWJSBDetail_YPayCountN] DEFAULT (0) FOR [YPayCountN],
	CONSTRAINT [DF_FWJSBDetail_PayCountN] DEFAULT (0) FOR [PayCountN],
	CONSTRAINT [DF_FWJSBDetail_ProPrice] DEFAULT (0) FOR [ProPrice],
	CONSTRAINT [DF_FWJSBDetail_YPayTotal] DEFAULT (0) FOR [YPayTotal],
	CONSTRAINT [DF_FWJSBDetail_PayTotal] DEFAULT (0) FOR [PayTotal],
	CONSTRAINT [DF_FWJSBDetail_DscTotal] DEFAULT (0) FOR [DscTotal],
	CONSTRAINT [PK_FWJSBDetail] PRIMARY KEY  CLUSTERED 
	(
		[JSFormNo],
		[FormNo],
		[FType],
		[ProdCode],
		[BarCode]
	)  ON [PRIMARY] 
end


if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WCustProdBindPay]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[WCustProdBindPay] (
	[FormNo] [char] (14) NOT NULL ,
	[FType] [char] (1) NOT NULL ,
	[BindCode] [char] (18) NOT NULL ,
	[ProdCode] [char] (18) NOT NULL ,
	[BarCode] [char] (18) NOT NULL ,
	[ProdName] [char] (40) NOT NULL ,
	[YPayCountN] [money] NULL ,
	[PayCountN] [money] NULL ,
	[YPayTotal] [money] NULL ,
	[PayTotal] [money] NULL ,
	[DscTotal] [money] NULL ,
	[OPrice] [money] NULL ,
	[OutOPrice] [money] NULL ,
	[Price] [money] NULL ,
	[Cost] [money] NULL ,
	[OutCost] [money] NULL ,
	[Total] [money] NULL 
) ON [PRIMARY]

ALTER TABLE [dbo].[WCustProdBindPay] WITH NOCHECK ADD 
	CONSTRAINT [DF_WCustProdBindPay_YPayCountN] DEFAULT (0) FOR [YPayCountN],
	CONSTRAINT [DF_WCustProdBindPay_PayCountN] DEFAULT (0) FOR [PayCountN],
	CONSTRAINT [DF_WCustProdBindPay_YPayTotal] DEFAULT (0) FOR [YPayTotal],
	CONSTRAINT [DF_WCustProdBindPay_PayTotal] DEFAULT (0) FOR [PayTotal],
	CONSTRAINT [DF_WCustProdBindPay_DscTotal] DEFAULT (0) FOR [DscTotal],
	CONSTRAINT [PK_WCustProdBindPay] PRIMARY KEY  CLUSTERED 
	(
		[FormNo],
		[FType],
		[BindCode],
		[ProdCode],
		[BarCode]
	)  ON [PRIMARY] 
end


if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WCustProdPay]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[WCustProdPay] (
	[FormNo] [char] (14) NOT NULL ,
	[FType] [char] (1) NOT NULL ,
	[CustCode] [char] (10) NULL ,
	[FormDate] [char] (10) NULL ,
	[PayDate] [char] (10) NULL ,
	[ProdCode] [char] (18) NOT NULL ,
	[BarCode] [char] (18) NOT NULL ,
	[ProdName] [char] (40) NOT NULL ,
	[DepCode] [char] (12) NULL ,
	[YPayCountN] [money] NULL ,
	[PayCountN] [money] NULL ,
	[ProPrice] [money] NULL ,
	[YPayTotal] [money] NULL ,
	[PayTotal] [money] NULL ,
	[DscTotal] [money] NULL ,
	[ProdType] [char] (1) NULL ,
	[OPrice] [money] NULL ,
	[OutOPrice] [money] NULL ,
	[Price] [money] NULL ,
	[Cost] [money] NULL ,
	[OutCost] [money] NULL ,
	[Total] [money] NULL 
) ON [PRIMARY]

ALTER TABLE [dbo].[WCustProdPay] WITH NOCHECK ADD 
	CONSTRAINT [DF_WCustProdPay_YPayCountN] DEFAULT (0) FOR [YPayCountN],
	CONSTRAINT [DF_WCustProdPay_PayCountN] DEFAULT (0) FOR [PayCountN],
	CONSTRAINT [DF_WCustProdPay_ProPrice] DEFAULT (0) FOR [ProPrice],
	CONSTRAINT [DF_WCustProdPay_YPayTotal] DEFAULT (0) FOR [YPayTotal],
	CONSTRAINT [DF_WCustProdPay_PayTotal] DEFAULT (0) FOR [PayTotal],
	CONSTRAINT [DF_WCustProdPay_DscTotal] DEFAULT (0) FOR [DscTotal],
	CONSTRAINT [PK_WCustProdPay] PRIMARY KEY  CLUSTERED 
	(
		[FormNo],
		[FType],
		[ProdCode],
		[BarCode]
	)  ON [PRIMARY] 
end

--升级备注字段的长度
Declare @tblName varchar(100),@FldName Varchar(20),@TypeName varchar(50),@TypeLen int
,@Str Varchar(1000),@ifNull Varchar(10)

Declare Fld_Cur Cursor for

Select O.Name,C.Name,TypeName=t.Name,c.Length
,ifNull=(Case c.IsNullable when 1 then ' null ' else ' not Null ' end)
from SysColumns C join SysObjects O
on c.id=o.ID and O.xType='U'
join systypes t on C.xType=t.XType
where C.Name in ('ProMemo','ProdMemo','ProdMemo1','ProdMemo2') and C.Length<256
order by O.Name

open Fld_Cur

Fetch next from Fld_Cur into @tblName,@FldName,@TypeName,@TypeLen,@ifNull

WHILE @@FETCH_STATUS = 0
begin
set @Str='Alter table '+@tblName+' alter column '+@FldName+' '+@TypeName+'(255)'+@ifNull
Exec (@Str)
Fetch next from Fld_Cur into @tblName,@FldName,@TypeName,@TypeLen,@ifNull
end
Close Fld_Cur
Deallocate Fld_Cur

--2006-08-18 批发销货、退货添加销项税率字段
if not Exists(Select * from SysColumns where name='STax' and id=Object_ID('FWXHB'))
begin
Alter table FWXHB add  [STax] [tinyint] NULL 
Exec ('update FWXHB  Set STax=(Select STax from Product A where FWXHB.ProdCode=A.ProdCode) ')
end

if not Exists(Select * from SysColumns where name='STax' and id=Object_ID('FWTHB'))
begin
Alter table FWTHB add [STax] [tinyint] NULL 
Exec ('update FWTHB  Set STax=(Select STax from Product A where FWTHB.ProdCode=A.ProdCode) ')
end

--2006-08-18 增加批发订货单
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FWDHB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[FWDHB] (
	[FormNo] [char] (14) NOT NULL ,
	[ProdCode] [char] (18) NOT NULL ,
	[ProdName] [char] (40) NULL ,
	[BarCode] [char] (18) NOT NULL ,
	[DepCode] [char] (12) NULL ,
	[Spec] [char] (20) NULL ,
	[ProdAdr] [char] (10) NULL ,
	[PUnit] [char] (20) NULL ,
	[PUnitAmt] [money] NULL ,
	[RUnit] [char] (20) NULL ,
	[CountP] [money] NULL ,
	[Unit] [char] (20) NULL ,
	[CountM] [money] NULL ,
	[CountN] [money] NULL ,
	[OPrice] [decimal](19, 10) NULL ,
	[OutOPrice] [decimal](19, 10) NULL ,
	[Price] [money] NULL ,
	[Cost] [money] NULL ,
	[OutCost] [money] NULL ,
	[Total] [money] NULL ,	
	[ProPrice] [money] NOT NULL ,
	[ProTotal] [money] NULL ,
	[ProMemo] [char] (10) NULL ,
	[timeMark] [int] IDENTITY (1, 1) NOT NULL ,
	[WBoxPrice] [money] NULL ,
	[DscTotal] [money] NULL ,
	[ProdType] [char] (1) NULL ,	
	[IsPresent] [char] (10) NOT NULL ,
	[LinkCode] [char] (30) NULL,
	[STax] [tinyint] NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[FWDHB] WITH NOCHECK ADD 
	CONSTRAINT [DF_FWDHB_PUnitAmt] DEFAULT (0) FOR [PUnitAmt],
	CONSTRAINT [DF_FWDHB_CountP] DEFAULT (0) FOR [CountP],
	CONSTRAINT [DF_FWDHB_CountM] DEFAULT (0) FOR [CountM],
	CONSTRAINT [DF_FWDHB_CountN] DEFAULT (0) FOR [CountN],
	CONSTRAINT [DF_FWDHB_OPrice] DEFAULT (0) FOR [OPrice],
	CONSTRAINT [DF_FWDHB_OutOPrice] DEFAULT (0) FOR [OutOPrice],
	CONSTRAINT [DF_FWDHB_Price] DEFAULT (0) FOR [Price],
	CONSTRAINT [DF_FWDHB_Cost] DEFAULT (0) FOR [Cost],
	CONSTRAINT [DF_FWDHB_OutCost] DEFAULT (0) FOR [OutCost],
	CONSTRAINT [DF_FWDHB_Total] DEFAULT (0) FOR [Total],	
	CONSTRAINT [DF_FWDHB_ProPrice] DEFAULT (0) FOR [ProPrice],
	CONSTRAINT [DF_FWDHB_ProTotal] DEFAULT (0) FOR [ProTotal],
	CONSTRAINT [DF_FWDHB_DscTotal] DEFAULT (0) FOR [DscTotal],	
	CONSTRAINT [DF_FWDHB_IsPresent] DEFAULT ('0') FOR [IsPresent],
	CONSTRAINT [PK_FWDHB] PRIMARY KEY  CLUSTERED 
	(
		[FormNo],
		[ProdCode],
		[BarCode],
		[IsPresent]
	)  ON [PRIMARY] 

end


if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FWDHH]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[FWDHH] (
	[FormNo] [char] (14) NOT NULL ,
	[FormDate] [char] (10) NULL ,
	[ReferNo] [char] (14) NULL ,
	[WriteDate] [char] (10) NULL ,
	[OverDate] [char] (10) NULL ,
	[YWCode] [char] (6) NULL ,
	[FormMaker] [char] (10) NULL ,
	[UserCode] [char] (6) NULL ,
	[UserName] [char] (10) NULL ,
	[CheckCode] [char] (6) NULL ,
	[CheckName] [char] (10) NULL ,
	[CheckType] [char] (1) NULL ,
	[DepCode] [char] (12) NULL ,
	[CustCode] [char] (10) NULL ,
	[PayMode] [char] (1) NULL ,
	[PayDate] [char] (10) NULL ,
	[VldDate] [char] (10) NULL ,
	[CountN] [money] NULL ,
	[ProTotal] [money] NULL ,
	[Cost] [money] NULL ,
	[OutCost] [money] NULL ,
	[Total] [money] NULL ,
	[ProMemo] [varchar] (50) NULL ,
	[Tag] [char] (1) NULL ,
	[SchemeCode] [char] (2) NULL ,
	[PrnTimes] [int] NULL ,
	[RZUser] [char] (6) NULL ,
	[RZUserName] [char] (10) NULL ,
	[timemark] [timestamp] NULL ,
	[DscTotal] [money] NULL ,
	[Subscription] [money] null,
	[StationName] [char] (30) NULL 
) ON [PRIMARY]

ALTER TABLE [dbo].[FWDHH] WITH NOCHECK ADD 
	CONSTRAINT [DF_FWDHH_CheckType] DEFAULT (0) FOR [CheckType],
	CONSTRAINT [DF_FWDHH_PayMode] DEFAULT (0) FOR [PayMode],
	CONSTRAINT [DF_FWDHH_CountN] DEFAULT (0) FOR [CountN],
	CONSTRAINT [DF_FWDHH_ProTotal] DEFAULT (0) FOR [ProTotal],
	CONSTRAINT [DF_FWDHH_Cost] DEFAULT (0) FOR [Cost],
	CONSTRAINT [DF_FWDHH_OutCost] DEFAULT (0) FOR [OutCost],
	CONSTRAINT [DF_FWDHH_Total] DEFAULT (0) FOR [Total],
	CONSTRAINT [DF_FWDHh_PrnTimes] DEFAULT (0) FOR [PrnTimes],
	CONSTRAINT [DF_FWDHH_DscTotal] DEFAULT (0) FOR [DscTotal],
	CONSTRAINT [DF_FWDHH_StationName] DEFAULT ('') FOR [StationName],
	CONSTRAINT [PK_FWDHH] PRIMARY KEY  CLUSTERED 
	(
		[FormNo]
	)  ON [PRIMARY] 

end
--2006-08-19 
  --FWXHH增加WDHFormNo字段
if not Exists(Select * from SysColumns where name='WDHFormNo' and id=Object_ID('FWXHH'))
begin
Alter table FWXHH add [WDHFormNo] char(14) NULL 
Exec ('update FWXHH  Set WDHFormNo='''' where WDHFormNo is null')
end
  --预付订金
if not Exists(Select * from SysColumns where name='subscription' and id=Object_ID('FWXHH'))
begin
Alter table FWXHH add [subscription] money 
ALTER TABLE [FWXHH] WITH NOCHECK ADD 
	CONSTRAINT [DF_FWXHH_subscription] DEFAULT (0) FOR [subscription]
Exec ('update FWXHH  Set subscription=0 where subscription is null')
end
  --预付款
if not Exists(Select * from SysColumns where name='Prepay' and id=Object_ID('FWJSH'))
begin
Alter table FWJSH add [Prepay] money 
ALTER TABLE [dbo].[FWJSH] WITH NOCHECK ADD 
	CONSTRAINT [DF_FWJSH_Prepay] DEFAULT (0) FOR [Prepay]
Exec ('update FWJSH  Set Prepay=0 where Prepay is null')
end
/****** Object:  Table [dbo].[FWPrePay]    Script Date: 2006-8-23 13:17:28 ******/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FWPrePay]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[FWPrePay] (
	[FormNo] [char] (14) NOT NULL ,
	[FormDate] [char] (10) NULL ,
	[WriteDate] [char] (10) NULL ,
	[FormMaker] [char] (10) NULL ,
	[UserCode] [char] (6) NULL ,
	[UserName] [char] (10) NULL ,
	[CheckCode] [char] (6) NULL ,
	[CheckName] [char] (10) NULL ,
	[CheckType] [char] (1) NULL ,
	[DepCode] [char] (10) NULL ,
	[CustCode] [char] (10) NULL ,
	[ProTotal] [money] NULL ,
	[ProMemo] [varchar] (255) NULL ,
	[Tag] [char] (1) NULL ,
	[PrnTimes] [int] NULL ,
	[RZUser] [char] (6) NULL ,
	[RZUserName] [char] (10) NULL ,
	[timemark] [timestamp] NULL 
) ON [PRIMARY] 

ALTER TABLE [dbo].[FWPrePay] WITH NOCHECK ADD 
	CONSTRAINT [DF_FWPrePay_ProTotal] DEFAULT (0) FOR [ProTotal],
	CONSTRAINT [PK_FWPrePay] PRIMARY KEY  CLUSTERED 
	(
		[FormNo]
	)  ON [PRIMARY] 
end
--tCustomer增加字段Prepay  2006-08-23 
if not Exists(Select * from SysColumns where name='Prepay' and id=Object_ID('tCustomer'))
begin
Alter table tCustomer add [Prepay] money 
ALTER TABLE [dbo].[tCustomer] WITH NOCHECK ADD 
	CONSTRAINT [DF_tCustomer_Prepay] DEFAULT (0) FOR [Prepay]
Exec ('update tCustomer  Set Prepay=0 where Prepay is null')
end
