--V1.5所作升级
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-01-12'
    Return
end
--V1.5 所做升级
if not Exists(Select * from SysColumns where Id=Object_ID('Product') and name='JYPrice' )
begin
  alter table Product add JYPrice money
  exec ('update Product set JYPrice=0 where JYPrice is Null ')
end

if not Exists(Select * from SysColumns where Id=Object_ID('Product') and name='TiCheng' )
begin
  alter table Product add TiCheng money
  exec ('update Product set TiCheng=0 where TiCheng is Null ')
end

if not Exists(Select * from SysColumns where Id=Object_ID('tmpProd') and name='JYPrice' )
begin
  alter table tmpProd add JYPrice money
  exec ('update tmpProd set JYPrice=0 where JYPrice is Null ')
end

if not Exists(Select * from SysColumns where Id=Object_ID('tmpProd') and name='TiCheng' )
begin
  alter table tmpProd add TiCheng money
  exec ('update tmpProd set TiCheng=0 where TiCheng is Null ')
end

if not Exists(Select * from SysColumns where Id=Object_ID('tCustomer') and name='MaxCredit' )
begin
  alter table tCustomer add MaxCredit money
  exec ('update tCustomer set MaxCredit=0 where MaxCredit is Null ')
end

if not Exists(Select * from SysColumns where Id=Object_ID('tSupply') and name='Credit' )
begin
  alter table tSupply add Credit money
  exec ('update tSupply set Credit=0 where Credit is Null ')
end

if not Exists(select * from tProdset where FuncCode='K0302' and CtrName='edtJYPrice' )
begin
  Declare @iOrder1 int
  Select @iOrder1=Max(iOrder)+1 from tProdset where FuncCode='K0302'
  insert into tProdset Values('K0302','edtJYPrice','建议售价',1,@iOrder1)
end
if not Exists(select * from tProdset where FuncCode='K0302' and CtrName='edtTiCheng' )
begin
  Declare @iOrder2 int
  Select @iOrder2=Max(iOrder)+1 from tProdset where FuncCode='K0302'
  insert into tProdset Values('K0302','edtTiCheng','提成比例',1,@iOrder2)
end
if not Exists(select * from tProdset where FuncCode='K0302' and CtrName='chkIsWeight' )
begin
  Declare @iOrder3 int
  Select @iOrder3=Max(iOrder)+1 from tProdset where FuncCode='K0302'
  insert into tProdset Values('K0302','chkIsWeight','称重商品',1,@iOrder3)
end

Delete from QryItem where QryCode='K06023'

Delete from QryItem where QryCode='K06043'

--新增汉字转拼音函数
Go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fun_getPY]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fun_getPY]


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create  function fun_getPY(@str varchar(4000))
returns varchar(4000)
as
begin

declare @word varchar(10),@PY varchar(4000)

declare @i int,@len int
set @PY=''
set @i=1
set @len=len(@str)

while @i<=@len
begin
set @word=substring(@str,@i,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T 
where word>=@word collate Chinese_PRC_CS_AS_KS_WS 
order by PY ASC) else @word end)
set @i=@i+1
end
return @PY
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO