--V1.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-04-16'
    Return
end

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

CREATE TABLE [dbo].[RepFonts] (
	[PrnCode] [varchar] (8) NOT NULL ,
	[Type] [char] (1) NOT NULL ,
	[FontName] [varchar] (50) NULL ,
	[FontHeight] [int] NULL 
) ON [PRIMARY]


ALTER TABLE [dbo].[RepFonts] WITH NOCHECK ADD 
	CONSTRAINT [PK_RepFonts] PRIMARY KEY  CLUSTERED 
	(
		[PrnCode],
		[Type]
	)  ON [PRIMARY] 

end

--添加TiCheng字段
if not Exists(Select * from SysColumns where Id=Object_ID('ProdBindWSDetail') and name='TiCheng' )
begin
  alter table ProdBindWSDetail add TiCheng Money Default 0 with Values 
  Exec ('Update ProdBindWSDetail  set ProdBindWSDetail.TiCheng=a.TiCheng from Product a where a.ProdCode=ProdBindWSDetail.ProdCode  ')
end

if not Exists(Select * from SysColumns where Id=Object_ID('ProdBindWSDetail') and name='PUnitAmt' )
begin
  alter table ProdBindWSDetail add PUnitAmt Money Default 0 with Values 
  Exec ('Update ProdBindWSDetail  set ProdBindWSDetail.PUnitAmt=a.PUnitAmt from Product a where a.ProdCode=ProdBindWSDetail.ProdCode  ')
end
--重新建立PosKeyDef
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PosKeyDef]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PosKeyDef]

CREATE TABLE [dbo].[PosKeyDef] (
	[PosNO] [char] (30) NOT NULL ,
	[KeyCode] [int] NOT NULL ,
	[FuncCode] [int] NULL ,
	[UserCode] [char] (20) NULL 
) ON [PRIMARY]

ALTER TABLE [dbo].[PosKeyDef] WITH NOCHECK ADD 
	CONSTRAINT [DF_PosKeyDef_FuncCode] DEFAULT (0) FOR [FuncCode],
	CONSTRAINT [DF_PosKeyDef_UserCode] DEFAULT ('') FOR [UserCode],
	CONSTRAINT [PK_PosKeyDef] PRIMARY KEY  CLUSTERED 
	(
		[PosNO],
		[KeyCode]
	)  ON [PRIMARY] 

--FWXHB添加ShortName字段
if not Exists(Select * from SysColumns where Id=Object_ID('FWXHB') and name='ShortName' )
begin
  alter table FWXHB add ShortName char(20) 
  Exec ('Update FWXHB  set FWXHB.ShortName=a.ShortName from Product a where a.ProdCode=FWXHB.ProdCode  ')
end

--FWTHB添加ShortName字段
if not Exists(Select * from SysColumns where Id=Object_ID('FWTHB') and name='ShortName' )
begin
  alter table FWTHB add ShortName char(20) Default '' with Values 
  Exec ('Update FWTHB  set FWTHB.ShortName=a.ShortName from Product a where a.ProdCode=FWTHB.ProdCode  ')
end

--销货单中增加结算金额、未结算金额、最后结算日期
if not Exists(Select * from SysColumns where Id=Object_ID('FWXHH') and name='JSTotal' )
begin
  alter table FWXHH add JSTotal Money  
  Exec('Update FWXHH Set JSTotal=0 ') 
  Exec ('update FWXHH set JSTotal=a.PayTotal+a.DscTotal from wCustPay a where a.FormNo=FWXHH.FormNo and a.FType=''0''  ')
  
  alter table FWXHH add LastJSDate char(10)

  Exec ('Update FWXHH set LastJSDate=tmp.LastJSDate from 
  (Select  Distinct LastJSDate=Max(a.WriteDate),FormNo=b.ProdCode from FWJSH a join FWJSB b on a.FormNo=b.FormNo 
  and a.CheckType=''3'' and FType=''0''  Group by b.ProdCode ) as tmp
  where tmp.FormNo=FWXHH.FormNo')
end
--退货单中增加结算金额、未结算金额、最后结算日期
if not Exists(Select * from SysColumns where Id=Object_ID('FWTHH') and name='JSTotal' )
begin
  alter table FWTHH add JSTotal Money  
  Exec('Update FWTHH Set JSTotal=0 ') 
  Exec ('update FWTHH set JSTotal=a.PayTotal+a.DscTotal from wCustPay a where a.FormNo=FWTHH.FormNo and a.FType=''1''  ')
  
  alter table FWTHH add LastJSDate char(10)

  Exec ('Update FWTHH set LastJSDate=tmp.LastJSDate from 
  (Select  Distinct LastJSDate=Max(a.WriteDate),FormNo=b.ProdCode from FWJSH a join FWJSB b on a.FormNo=b.FormNo 
  and a.CheckType=''3'' and FType=''1''  Group by b.ProdCode ) as tmp
  where tmp.FormNo=FWTHH.FormNo')
end
--验收单
if not Exists(Select * from SysColumns where Id=Object_ID('FYSH') and name='JSTotal' )
begin
  alter table FYSH add JSTotal Money  
  Exec('Update FYSH Set JSTotal=0 ') 
  Exec ('update FYSH set JSTotal=a.PayTotal+a.DscTotal from SuppPay a where a.FormNo=FYSH.FormNo and a.FType=''0''  ')
  
  alter table FYSH add LastJSDate char(10)

  Exec ('Update FYSH set LastJSDate=tmp.LastJSDate from 
  (Select  Distinct LastJSDate=Max(a.WriteDate),FormNo=b.ProdCode from FjxJSH a join FjxJSB b on a.FormNo=b.FormNo 
  and a.CheckType=''3'' and FType=''0''  Group by b.ProdCode ) as tmp
  where tmp.FormNo=FYSH.FormNo')
end

--退货单
if not Exists(Select * from SysColumns where Id=Object_ID('FTHH') and name='JSTotal' )
begin
  alter table FTHH add JSTotal Money  
  Exec('Update FTHH Set JSTotal=0 ') 
  Exec ('update FTHH set JSTotal=a.PayTotal+a.DscTotal from SuppPay a where a.FormNo=FTHH.FormNo and a.FType=''1''  ')
  
  alter table FTHH add LastJSDate char(10)

  Exec ('Update FTHH set LastJSDate=tmp.LastJSDate from 
  (Select  Distinct LastJSDate=Max(a.WriteDate),FormNo=b.ProdCode from FjxJSH a join FjxJSB b on a.FormNo=b.FormNo 
  and a.CheckType=''3'' and FType=''1''  Group by b.ProdCode ) as tmp
  where tmp.FormNo=FTHH.FormNo')
end

--进价单
if not Exists(Select * from SysColumns where Id=Object_ID('FJTJH') and name='JSTotal' )
begin
  alter table FJTJH add JSTotal Money 
  Exec('Update FJTJH Set JSTotal=0 ') 
  Exec ('update FJTJH set JSTotal=a.PayTotal+a.DscTotal from SuppPay a where a.FormNo=FJTJH.FormNo and a.FType=''2''  ')
  
  alter table FJTJH add LastJSDate char(10)

  Exec ('Update FJTJH set LastJSDate=tmp.LastJSDate from 
  (Select  Distinct LastJSDate=Max(a.WriteDate),FormNo=b.ProdCode from FjxJSH a join FjxJSB b on a.FormNo=b.FormNo 
  and a.CheckType=''3'' and FType=''2''  Group by b.ProdCode ) as tmp
  where tmp.FormNo=FJTJH.FormNo')
end

Update tGrpRight Set FuncCode='RptSet' where FuncCode='F2'
Update tGrpRight Set FuncCode='Present' where FuncCode='F8'
Update tGrpRight Set FuncCode='pfth' where FuncCode='F10'
Update tGrpRight Set FuncCode='itemdsc' where FuncCode='F11F12A'
Update tGrpRight Set FuncCode='TRANDSC' where FuncCode='F11F12B'

Update tSysFunc Set FuncCode='RptSet' where FuncCode='F2'
Update tSysFunc Set FuncCode='Present' where FuncCode='F8'
Update tSysFunc Set FuncCode='pfth' where FuncCode='F10'
Update tSysFunc Set FuncCode='itemdsc' where FuncCode='F11F12A'
Update tSysFunc Set FuncCode='TRANDSC' where FuncCode='F11F12B'
insert into tSysFunc 
Values('DelRow','行删','ZZ',1,0,0,0,0,0,0,0,0,0,0,'',0,6,'')
insert into tSysFunc 
Values('TranVoid','交易取消','ZZ',1,0,0,0,0,0,0,0,0,0,0,'',0,7,'')
insert into tSysFunc 
Values('CashBox','开钱箱','ZZ',1,0,0,0,0,0,0,0,0,0,0,'',0,8,'')

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


