--V1.7.3所作升级
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-06-27'
    Return
end
--删除订货单历史数据
if not Exists(Select * from DelHistory where spName='Up_DelHist_FWDH')
begin
  insert into DelHistory Values('批发订货单','Up_DelHist_FWDH','21','0')
end

if (Select Count(*) from tSysOpt where OptName='Version' and OptValue='1.7.2')>0
and exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RelativeRpt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
  Drop table RelativeRpt
  Drop table RelativeRptParam
end

if not (exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RelativeRpt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) )
Create table RelativeRpt
(
QryNo int,
RptNo int,
RelativeRptNo int,
Caption varchar(30),
iOrder int
)
if not (exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RelativeRptParam]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) )
Create table RelativeRptParam
(
QryNo int,
RptNo int,
RelativeRptNo int,
ParamName varchar(20),
ParamType varchar(1),
iOrder int
)

if not Exists(Select * from SysColumns where Id=Object_ID('FTJB') and name='BoxPrice' )
begin
  alter table FTJB add BoxPrice money null   
  alter table FTJB add NewBoxPrice money null
  alter table FTJB add PUnitAmt money null
  ALTER TABLE [dbo].[FTJB] WITH NOCHECK ADD 	
	CONSTRAINT [DF_FTJB_BoxPrice] DEFAULT (0) FOR [BoxPrice],
	CONSTRAINT [DF_FTJB_NewBoxPrice] DEFAULT (0) FOR [NewBoxPrice],
	CONSTRAINT [DF_FTJB_PUnitAmt] DEFAULT (0) FOR [PUnitAmt]
	Exec ('update FTJB set PUnitAmt=(Select IsNull(PUnitAmt,1) from Product where ProdCode=FTJB.ProdCode')
	Exec ('update FTJB set BoxPrice=(Select IsNull(BoxPrice,1) from Product where ProdCode=FTJB.ProdCode)')
  Exec ('update FTJB set NewBoxPrice=(Select IsNull(BoxPrice,1) from Product where ProdCode=FTJB.ProdCode)')
end

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

CREATE TABLE [dbo].[tPosLog] (
	[PosNo] [char] (30) NOT NULL ,
        [FormNo] [char] (14) NULL ,
	[sDateTime] [char] (19) NOT NULL ,
	[UserCode] [char] (6) NULL ,
	[Operation] [char] (20) NULL ,
	[ProdCode] [char] (18) NULL ,
	[ProdName] [char] (40) NULL ,
	[CountN] [money] NULL ,
	[ProTotal] [money] NULL ,
        [DscTotal] [money] NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[tPosLog] WITH NOCHECK ADD 
	CONSTRAINT [DF_tPosLog_CountN] DEFAULT (0) FOR [CountN],
	CONSTRAINT [DF_tPosLog_ProTotal] DEFAULT (0) FOR [ProTotal],
	CONSTRAINT [DF_tPosLog_DscTotal] DEFAULT (0) FOR [DscTotal]

end
