用友U8+中,客戶檔案和供應商檔案可以設定自動生成助記碼,但軟體隻能自動根據客戶全稱/供應商全稱生成助記碼,而無法選擇按簡稱生成助記碼,這顯然十分不友善,可以通過如下方式解決:
修改步驟
1、往資料庫中插入一個自定義函數,實作自動根據漢字取拼音首字母。
2、對customer(客戶檔案)及Vendor(供應商檔案)分别增加一個觸發器,儲存檔案後自動update。
新增函數
來源于https://blog.csdn.net/huryer/article/details/78547331
create function [dbo].[fun_getPY](@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nvarchar(1),@PY nvarchar(4000)
set @PY=\'\'
set @str = ltrim(rtrim(@str))
while len(@str)>0
begin
set @word=left(@str,1)
set @PY=@PY+ltrim((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)
when unicode(@word) between 8544 and 8552 --希臘字母1-9
then nchar(unicode(@word)-8495)
when unicode(@word) = 8553 --希臘字母10
then \'0\'
when unicode(@word) between 48 and 57 --數字0-9
then @word
when unicode(@word) between 65296 and 65305 --全角數字0-9
then nchar(unicode(@word)-65248)
when unicode(upper(@word)) between 65 and 90 --字母a-z和A-Z
then upper(@word)
when unicode(upper(@word)) between 65313 and 65338 --全角字母a-z和A-Z
then nchar(unicode(upper(@word))-65248)
else \'\' end)) --如果非漢字字元或非字母、數字、希臘字母、全角字母、全角數字,傳回空字元
--set @str=right(@str,len(@str)-1)
set @str = substring(@str,2,len(@str)-1)
end
return @PY
end
函數調用方法
select dbo.fun_getPY(‘張三’) as result --上述語句執行後傳回結果ZS
插入觸發器
客戶檔案
CREATE TRIGGER [dbo].[MnemCode]
ON [dbo].[Customer] AFTER INSERT
AS
update customer
set cCusMnemCode=dbo.fun_getPY(cCusAbbName) where cCusMnemCode is null
供應商檔案
CREATE TRIGGER [dbo].[Vendor_MnemCode]
ON [dbo].[Vendor] AFTER INSERT
AS
update vendor
set cVenMnemCode=dbo.fun_getPY(cVenAbbName) where cVenMnemCode is null