| 解决方案： |
执行脚本：
if (not exists(select top 1 cCuscode from customer)) and (not exists (select top 1 cCCCode from customerclass))
Begin
if (exists(select top 1 * from accInformation where cid='40' and csysid='AA' and cvalue='false' )) and (not exists (select top 1 * from customer)) begin delete from customerclass where cCCCode='00' End
if (not exists(select top 1 cCuscode from customer)) and (not exists (select top 1 cCCCode from customerclass))
Begin
update accInformation set cvalue=(select top 1 cValue from AccInformation where cid='41' and cSysId='AA') where cid='40' and csysid='AA'
update gradedef set CodingRule=(select top 1 codingRule from gradedef where keyword='vendorclass') where keyword='customerclass'
update gradedef set CodingRule=(select top 1 codingRule from gradedef where keyword='vendorAuth') where keyword='customerAuth'
insert into CustomerClass(cCCCode,cCCName,iCCGrade,bCCEnd) select cVCCode,cVCName,iVCGrade,bVCEnd from vendorClass
insert into customer(cCusCode,cCusName,cCusAbbName,cCCCode,cDCCode,cCusDepart,cCusPPerson,dCusDevDate,cCusPhone,cCusHand,cCusFax,cCusBP,cCusPerson,cCusOAddress,cCusEmail,cCusHeadCode) select cVenCode,cVenName,cVenAbbName,cVCCode,cDCCode,cVenDepart,cVenPPerson,dVenDevDate,cVenPhone,cVenHand,cVenFax,cVenBP,cVenPerson,cVenIAddress,cVenEmail,cVenHeadCode from vendor
if (not exists(select top 1 id from aa_authclass where cbusobid='customer'))
Begin
if (exists(select top 1 * from aa_busobject where cbusobid = 'vendor' and bauthcontrol = 1 and iauthtype = 255)) update aa_busobject set bauthcontrol = 1 where cbusobid = 'customer' and iauthtype = 0
if (exists(select top 1 * from aa_busobject where cbusobid = 'vendor' and bauthcontrol = 1 and iauthtype = 1)) update aa_busobject set bauthcontrol = 1 where cbusobid = 'customer' and iauthtype = 1
update aa_authclass set cbusobid='customer' where cbusobid='vendor'
update aa_holdauth set cbusobid='customerclass' where cbusobid='vendorclass'
update aa_holdauth set cbusobid='customer' where cbusobid='vendor'
End
update code set bcus=1 where bsup=1
update code set bsup=0
update gl_accvouch set ccus_id=csup_id where ccus_id is null and csup_id is not null
update gl_accvouch set csup_id=null
update gl_accass set ccus_id=csup_id where ccus_id is null and csup_id is not null
update gl_accass set csup_id=null
End
End
Else
Begin
--判断以前有没有转换过
Declare @cVenAbbName Varchar(100)
Set @cVenAbbName = (Select Top 1 cVenAbbName + 'G' From Vendor)
if (Not exists(select top 1 cCuscode from customer Where cCusAbbName = @cVenAbbName))
Begin
--得到供应商分类的第一级
Declare @cMaxCusClassCode Varchar(12)--最大第一级客户分类编码
Declare @cMinVenClassCodeOld Varchar(12)--由客户分类编码计算出来的第一个一级供应商分类编码
Declare @cMinVenClassCode Varchar(12)--由客户分类编码计算出来的第一个一级供应商分类编码
Set @cMinVenClassCodeOld = (Select Max(cVCCode) From VendorClass Where iVCGrade = 1)
Set @cMaxCusClassCode = (Select Max(cCCCode) From CustomerClass Where iCCGrade = 1)
If (CharIndex(Right(@cMaxCusClassCode,1), '123456789') > 0)--如果是数字类型的客户编码
Begin
If (Right(@cMaxCusClassCode,1)+1 <= 9)
Set @cMinVenClassCode = Left(@cMaxCusClassCode,Len(@cMaxCusClassCode)-1) + Right(@cMaxCusClassCode,1)+1
Else
Set @cMinVenClassCode = Left(@cMaxCusClassCode,Len(@cMaxCusClassCode)-1) + 'A'
End
Else--如果是字符类型的客户编码
Begin
Set @cMinVenClassCode = Left(@cMaxCusClassCode,Len(@cMaxCusClassCode)-1) + Char(Ascii(Right(@cMaxCusClassCode,1)) + 1)
End
Set @cMaxCusClassCode = (Select Max(cCCCode) From CustomerClass Where iCCGrade = 1)
Declare @iVenClassCount int--供应商一级分类个数
Declare @iVenClassLoop int --供应商分类循环变量
Declare @cVenClassCodeOld varchar(12) --旧的供应商一级编码
--取得供应商一级分类个数
set @iVenClassCount = (Select Count(*) From VendorClass Where iVCGrade = 1)
Set @iVenClassLoop = 0
While (@iVenClassLoop < @iVenClassCount)
Begin
Set @iVenClassLoop = @iVenClassLoop +1
If (@iVenClassLoop = 1)
Set @cVenClassCodeOld = (Select Min(cVCCode) From VendorClass)
Else
Set @cVenClassCodeOld = (Select Min(cVCCode) From VendorClass Where cVCCode > @cVenClassCodeOld And Len(cVCCode) = Len(@cVenClassCodeOld))
-- Select @cVenClassCodeOld
--将新的供应商分类和供应商信息插入到客户信息中
If (@cMaxCusClassCode <> '00' Or @cMinVenClassCodeOld <> '00')
Begin
--select Replace(cVCCode,@cVenClassCodeOld,@cMinVenClassCode) From vendor
insert into CustomerClass(cCCCode,cCCName,iCCGrade,bCCEnd) select Replace(cVCCode,@cVenClassCodeOld,@cMinVenClassCode),cVCName,iVCGrade,bVCEnd from vendorClass Where Left(cVCCode,Len(@cVenClassCodeOld)) = @cVenClassCodeOld
insert into Customer(cCusCode,cCusName,cCusAbbName,cCCCode,cDCCode,cCusDepart,cCusPPerson,dCusDevDate,cCusPhone,cCusHand,cCusFax,cCusBP,cCusPerson,cCusOAddress,cCusEmail,cCusHeadCode) select cVenCode + 'G',cVenName,cVenAbbName + 'G',Replace(cVCCode,@cVenClassCodeOld,@cMinVenClassCode),cDCCode,cVenDepart,cVenPPerson,dVenDevDate,cVenPhone,cVenHand,cVenFax,cVenBP,cVenPerson,cVenIAddress,cVenEmail,cVenHeadCode from vendor Where Left(cVCCode,Len(@cVenClassCodeOld)) = @cVenClassCodeOld
If Char(Ascii(Right(@cMinVenClassCode,1)) + 1) = '9'
Set @cMinVenClassCode = Left(@cMinVenClassCode, Len(@cMinVenClassCode) - 1) + 'A'
Else
Set @cMinVenClassCode = Left(@cMinVenClassCode, Len(@cMinVenClassCode) - 1) + Char(Ascii(Right(@cMinVenClassCode,1)) + 1)
End
Else
Begin
insert into Customer(cCusCode,cCusName,cCusAbbName,cCCCode,cDCCode,cCusDepart,cCusPPerson,dCusDevDate,cCusPhone,cCusHand,cCusFax,cCusBP,cCusPerson,cCusOAddress,cCusEmail,cCusHeadCode) select cVenCode + 'G',cVenName,cVenAbbName + 'G','00',cDCCode,cVenDepart,cVenPPerson,dVenDevDate,cVenPhone,cVenHand,cVenFax,cVenBP,cVenPerson,cVenIAddress,cVenEmail,cVenHeadCode from vendor Where cVCCode = '00'
End
End
update code set bcus=1 where bsup=1
update code set bsup=0
update gl_accvouch set ccus_id = csup_id + 'G' where csup_id <> '' And csup_id Is Not NULL
update gl_accvouch set csup_id = null
update gl_accass set ccus_id= csup_id + 'G' where csup_id <> '' And csup_id Is Not NULL
update gl_accass set csup_id=null
End
End
|