if exists ( select * from sys.objects where object_id = object_id(N'dbo.a_NumTxt') and type in (N'FN', N'if', N'TF', N'FS', N'FT') ) drop function dbo.a_NumTxt go
create function dbo.a_NumTxt ( @Txt varchar(max) ) returns varchar(max) as begin --------------------------------------------------------------------------------------------------------------------------------------------- while patIndex( '%[^-.+,0-9]%',@Txt ) > 0 -- 负号要放在最前面，否则会被视为【连续符】如【0-9】=【0 至 9】 begin set @Txt = stuff( @Txt ,patIndex( '%[^-.+,0-9]%' ,@Txt ) ,1 ,'' ) end --------------------------------------------------------------------------------------------------------------------------------------------- return @Txt end
if exists ( select * from sys.objects where object_id = object_id(N'dbo.a_EnTxt') and type in (N'FN', N'if', N'TF', N'FS', N'FT') ) drop function dbo.a_EnTxt go
create function dbo.a_EnTxt ( @Txt varchar(max) ) returns varchar(max) as begin --------------------------------------------------------------------------------------------------------------------------------------------- while patIndex( '%[^A-Za-z]%',@Txt ) > 0 begin set @Txt = stuff( @Txt ,patIndex( '%[^A-Za-z]%' ,@Txt ) ,1 ,'' ) end --------------------------------------------------------------------------------------------------------------------------------------------- return convert(varchar(max),@Txt) end
if exists (select * from sys.objects where object_id = OBJECT_ID(N'dbo.a_SplitTxtNew') AND type in (N'FN', N'if', N'TF', N'FS', N'FT')) drop function dbo.a_SplitTxtNew go
create function dbo.a_SplitTxtNew ( @Txt varchar(8000) -- 原文本 set @Txt = 'Fdate 制单日期 ,FCustIDName 购货单位 ,FSaleStylename 销售方式 ,FInvoiceName 发票抬头' ,@Find varchar(200) -- 分隔符 set @Find = ' ,'
,@Txt1 varchar(200) -- 开始文本 ,@Txt2 varchar(200) -- 中间文本 ,@Txt3 varchar(200) -- 结束文本 ) returns varchar(max) as begin ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ if len(replace(@Txt,' ',''))=0 begin -- 没有文本，返回空 ★ return '' end
------------------------------------------------------------------------------------------------------------------- declare @Start int set @Start = 1 declare @end int set @end = 1
declare @Result varchar(max) set @Result = '' declare @TxtNew varchar(max) set @TxtNew = ''
while 1 = 1 begin ------------------------------------------------------------------------------------------------------- set @end = CHARINDEX( @Find ,@Txt ,@Start ); -- 找到【分隔符】的位置 ★
----------------------------------------------------------------------------- if @end = 0 begin
set @end = len(@Txt) + 1; -- 找不到【分隔符】 ★ end
----------------------------------------------------------------------------- if @Start >= @end begin
set @TxtNew = @TxtNew + @Txt3; -- 文本结束 ★ break; end
----------------------------------------------------------------------------- set @Result = SUBSTRING(@Txt,@Start,@end-@Start) -- 截取文字 ★
----------------------------------------------------------------------------- if @Start = 1 begin
set @TxtNew = @Txt1 + @Result -- 文本开始 ★ end else begin
set @TxtNew = @TxtNew + @Txt2 + @Result -- 文本中间 ★ end
----------------------------------------------------------------------------- set @Start = @end + DATAlenGTH(@Find) -- 更新 起始位置
------------------------------------------------------------------------------------------------------- end
return @TxtNew -- 输出结果
-------------------------------------------------- end
ALTER TABLE ICTransactionType ADD FChatTypeID int DEFAULT 0; -- ICChatBillTitle.FTypeID ALTER TABLE ICTransactionType ADD FSchemeTypeID int DEFAULT 0; -- ICSchemeProfile.FTranType go
update ICTransactionType set FChatTypeID = 0; update ICTransactionType set FSchemeTypeID = 0; go
select * from a_ICTemplateTxt where FTabIndex < 100 and FID = 'B01' order BY FTabIndex -- 金蝶老单 表头布局：输入顺序 select * from a_ICTemplateTxt where FTabIndex < 100 and FID = 'B01' order BY FTop,FLeft -- 金蝶老单 表头布局：更改位置
from ( --------------------------------------------------------------------------------------------------------------------------------------------- select FID,FBillName,FFieldName
from aICField where FID=a1.FID and FFieldName=a1.FFieldName ---------------------------------------------------------------------------------------------------------------------- for xml path('') ) ,1,1,'') FCaption
,stuff( ( ---------------------------------------------------------------------------------------------------------------------- select ','+ CAST( FSort as varchar(10))
from aICField where FID=a1.FID and FFieldName=a1.FFieldName order by FSort ---------------------------------------------------------------------------------------------------------------------- for xml path('') ) ,1,1,'') FSort
from aICField a1
group by FID,FBillName,FFieldName --------------------------------------------------------------------------------------------------------------------------------------------- ) b1
if exists ( select * from sys.objects where object_id = object_id(N'dbo.a_ChangeCaption') and type in (N'P', N'PC')) drop procedure dbo.a_ChangeCaption go
if exists( select 1 from ICChatBillTitle where FTypeID = @FChatTypeID and FColName = @FFieldName ) update ICChatBillTitle set FColCaption = @FCaption +'$' where FTypeID = @FChatTypeID and FColName = @FFieldName
if exists( select 1 from GLNoteCitation where FTemplateID = @FTemplateID and FCode = @FFieldName and FInEntry = @FInEntry and FRelationID IN ( 1,2 ) ) update GLNoteCitation set FCitationName = @FCaption +'$' where FTemplateID = @FTemplateID and FCode = @FFieldName and FInEntry = @FInEntry and FRelationID IN ( 1,2 )
if exists( select 1 from GLNoteCitation where FTemplateID = @FTemplateID and FCode = @FFieldName and FInEntry = 0 and FRelationID IN ( 3 ) ) update GLNoteCitation set FCitationName = '汇总' + @FCaption +'$' where FTemplateID = @FTemplateID and FCode = @FFieldName and FInEntry = 0 and FRelationID IN ( 3 )
if exists( select 1 from GLNoteCitation where FTemplateID = @FTemplateID and FCode = @FFieldName and FInEntry = 0 and FRelationID IN ( 20 ) ) update GLNoteCitation set FCitationName = '本页小计—'+ @FCaption +'$' where FTemplateID = @FTemplateID and FCode = @FFieldName and FInEntry = 0 and FRelationID IN ( 20 ) end