|
|
如何实现数字转英文单词功能？
这里是订单为例，增加“合计英文”字段（注：该字段仅在序时簿列表显示）
操作步骤
执行下方的Sql脚本，清除Opcache缓存
-- 1、数字转英文存储过程
DROP FUNCTION IF EXISTS `func_changeTowords`;
delimiter ||
CREATE FUNCTION `func_changeTowords`(n DECIMAL(25,2)) RETURNS MEDIUMTEXT CHARSET UTF8
BEGIN
DECLARE ans VARCHAR(255);
DECLARE changdu,dig1, dig2, dig3, dig4, dig5, dig6,dig7,dig8,dig9 INT;
DECLARE dec1, dec2 INT;
SET ans = '';
SET changdu=LENGTH(floor(n));
IF changdu>9 THEN
RETURN ('The number is too large to convert');
END IF;
SET dig9 = CAST(RIGHT(CAST(floor(n / 100000000) AS CHAR(8)), 1) AS SIGNED);
SET dig8 = CAST(RIGHT(CAST(floor(n / 10000000) AS CHAR(8)), 1) AS SIGNED);
SET dig7 = CAST(RIGHT(CAST(floor(n / 1000000) AS CHAR(8)), 1) AS SIGNED);
SET dig6 = CAST(RIGHT(CAST(floor(n / 100000) AS CHAR(8)), 1) AS SIGNED);
SET dig5 = CAST(RIGHT(CAST(floor(n / 10000) AS CHAR(8)), 1) AS SIGNED);
SET dig4 = CAST(RIGHT(CAST(floor(n / 1000) AS CHAR(8)), 1) AS SIGNED);
SET dig3 = CAST(RIGHT(CAST(floor(n / 100) AS CHAR(8)), 1) AS SIGNED);
SET dig2 = CAST(RIGHT(CAST(floor(n / 10) AS CHAR(8)), 1) AS SIGNED);
SET dig1 = CAST(RIGHT(floor(n), 1) AS SIGNED);
-- 第9位
IF dig9 > 0 THEN
CASE
WHEN dig9=1 THEN SET ans=CONCAT(ans, 'one hundred');
WHEN dig9=2 THEN SET ans=CONCAT(ans, 'two hundred');
WHEN dig9=3 THEN SET ans=CONCAT(ans, 'three hundred');
WHEN dig9=4 THEN SET ans=CONCAT(ans, 'four hundred');
WHEN dig9=5 THEN SET ans=CONCAT(ans, 'five hundred');
WHEN dig9=6 THEN SET ans=CONCAT(ans, 'six hundred');
WHEN dig9=7 THEN SET ans=CONCAT(ans, 'seven hundred');
WHEN dig9=8 THEN SET ans=CONCAT(ans, 'eight hundred');
WHEN dig9=9 THEN SET ans=CONCAT(ans, 'nine hundred');
ELSE SET ans=ans;
END CASE;
ELSE
SET ans=ans;
END IF;
-- 第8位和第7位
IF dig8 = 1 THEN
SET ans=IF(ans='','',CONCAT(ans,' and '));
CASE
WHEN (dig8*10 + dig7) = 10 THEN SET ans=CONCAT(ans, 'ten million');
WHEN (dig8*10 + dig7) = 11 THEN SET ans=CONCAT(ans, 'eleven million');
WHEN (dig8*10 + dig7) = 12 THEN SET ans=CONCAT(ans, 'twelve million');
WHEN (dig8*10 + dig7) = 13 THEN SET ans=CONCAT(ans, 'thirteen million');
WHEN (dig8*10 + dig7) = 14 THEN SET ans=CONCAT(ans, 'fourteen million');
WHEN (dig8*10 + dig7) = 15 THEN SET ans=CONCAT(ans, 'fifteen million');
WHEN (dig8*10 + dig7) = 16 THEN SET ans=CONCAT(ans, 'sixteen million');
WHEN (dig8*10 + dig7) = 17 THEN SET ans=CONCAT(ans, 'seventeen million');
WHEN (dig8*10 + dig7) = 18 THEN SET ans=CONCAT(ans, 'eighteen million');
WHEN (dig8*10 + dig7) = 19 THEN SET ans=CONCAT(ans, 'nineteen million');
END CASE;
ELSEIF dig8 = 0 THEN
IF ans<>'' AND dig7>0 THEN
SET ans=CONCAT(ans,' and ');
ELSE
SET ans=ans;
END IF;
CASE
WHEN dig7=0 AND changdu>7 THEN SET ans=CONCAT(ans, ' million');
WHEN dig7=1 THEN SET ans=CONCAT(ans, 'one million');
WHEN dig7=2 THEN SET ans=CONCAT(ans, 'two million');
WHEN dig7=3 THEN SET ans=CONCAT(ans, 'three million');
WHEN dig7=4 THEN SET ans=CONCAT(ans, 'four million');
WHEN dig7=5 THEN SET ans=CONCAT(ans, 'five million');
WHEN dig7=6 THEN SET ans=CONCAT(ans, 'six million');
WHEN dig7=7 THEN SET ans=CONCAT(ans, 'seven million');
WHEN dig7=8 THEN SET ans=CONCAT(ans, 'eight million');
WHEN dig7=9 THEN SET ans=CONCAT(ans, 'nine million');
ELSE SET ans=ans;
END CASE;
ELSE
IF dig8 > 0 THEN
SET ans=IF(ans='','',CONCAT(ans,' and '));
CASE
WHEN dig8=2 THEN SET ans=CONCAT(ans, 'twenty');
WHEN dig8=3 THEN SET ans=CONCAT(ans, 'thirty');
WHEN dig8=4 THEN SET ans=CONCAT(ans, 'fourty');
WHEN dig8=5 THEN SET ans=CONCAT(ans, 'fifty');
WHEN dig8=6 THEN SET ans=CONCAT(ans, 'sixty');
WHEN dig8=7 THEN SET ans=CONCAT(ans, 'seventy');
WHEN dig8=8 THEN SET ans=CONCAT(ans, 'eighty');
WHEN dig8=9 THEN SET ans=CONCAT(ans, 'ninety');
ELSE SET ans=ans;
END CASE;
ELSE
SET ans=ans;
END IF;
IF dig7 > 0 THEN
CASE
WHEN dig7=1 THEN SET ans=CONCAT(ans, '-one million');
WHEN dig7=2 THEN SET ans=CONCAT(ans, '-two million');
WHEN dig7=3 THEN SET ans=CONCAT(ans, '-three million');
WHEN dig7=4 THEN SET ans=CONCAT(ans, '-four million');
WHEN dig7=5 THEN SET ans=CONCAT(ans, '-five million');
WHEN dig7=6 THEN SET ans=CONCAT(ans, '-six million');
WHEN dig7=7 THEN SET ans=CONCAT(ans, '-seven million');
WHEN dig7=8 THEN SET ans=CONCAT(ans, '-eight million');
WHEN dig7=9 THEN SET ans=CONCAT(ans, '-nine million');
END CASE;
ELSE
SET ans=ansCONCAT(ans,' million');
END IF;
END IF;
-- 第6位
IF dig6 > 0 THEN
CASE
WHEN dig6=1 THEN SET ans=CONCAT(ans, ' one hundred');
WHEN dig6=2 THEN SET ans=CONCAT(ans, ' two hundred');
WHEN dig6=3 THEN SET ans=CONCAT(ans, ' three hundred');
WHEN dig6=4 THEN SET ans=CONCAT(ans, ' four hundred');
WHEN dig6=5 THEN SET ans=CONCAT(ans, ' five hundred');
WHEN dig6=6 THEN SET ans=CONCAT(ans, ' six hundred');
WHEN dig6=7 THEN SET ans=CONCAT(ans, ' seven hundred');
WHEN dig6=8 THEN SET ans=CONCAT(ans, ' eight hundred');
WHEN dig6=9 THEN SET ans=CONCAT(ans, ' nine hundred');
END CASE;
ELSE
SET ans=ans;
END IF;
-- 第5位和第4位
IF dig5=1 THEN
SET ans=IF(ans='','',CONCAT(ans,' '));
CASE
WHEN (dig5*10 + dig4) = 10 THEN SET ans=CONCAT(ans, 'ten thousand');
WHEN (dig5*10 + dig4) = 11 THEN SET ans=CONCAT(ans, 'eleven thousand');
WHEN (dig5*10 + dig4) = 12 THEN SET ans=CONCAT(ans, 'twelve thousand');
WHEN (dig5*10 + dig4) = 13 THEN SET ans=CONCAT(ans, 'thirteen thousand');
WHEN (dig5*10 + dig4) = 14 THEN SET ans=CONCAT(ans, 'fourteen thousand');
WHEN (dig5*10 + dig4) = 15 THEN SET ans=CONCAT(ans, 'fifteen thousand');
WHEN (dig5*10 + dig4) = 16 THEN SET ans=CONCAT(ans, 'sixteen thousand');
WHEN (dig5*10 + dig4) = 17 THEN SET ans=CONCAT(ans, 'seventeen thousand');
WHEN (dig5*10 + dig4) = 18 THEN SET ans=CONCAT(ans, 'eighteen thousand');
WHEN (dig5*10 + dig4) = 19 THEN SET ans=CONCAT(ans, 'nineteen thousand');
-- ELSE SET ans=ans;
END CASE;
ELSEIF dig5=0 THEN
IF ans<>'' AND dig4>0 THEN
SET ans=CONCAT(ans,' ');
ELSE
SET ans=ans;
END IF;
CASE
WHEN dig4=0 AND dig6>0 THEN SET ans=CONCAT(ans, ' thousand');
WHEN dig4=1 THEN SET ans=CONCAT(ans, 'one thousand');
WHEN dig4=2 THEN SET ans=CONCAT(ans, 'two thousand');
WHEN dig4=3 THEN SET ans=CONCAT(ans, 'three thousand');
WHEN dig4=4 THEN SET ans=CONCAT(ans, 'four thousand');
WHEN dig4=5 THEN SET ans=CONCAT(ans, 'five thousand');
WHEN dig4=6 THEN SET ans=CONCAT(ans, 'six thousand');
WHEN dig4=7 THEN SET ans=CONCAT(ans, 'seven thousand');
WHEN dig4=8 THEN SET ans=CONCAT(ans, 'eight thousand');
WHEN dig4=9 THEN SET ans=CONCAT(ans, 'nine thousand');
ELSE SET ans=ans;
END CASE;
ELSE
IF dig5 > 0 THEN
SET ans=IF(ans='',ans,CONCAT(ans,' and '));
CASE
WHEN dig5=2 THEN SET ans=CONCAT(ans, 'twenty');
WHEN dig5=3 THEN SET ans=CONCAT(ans, 'thirty');
WHEN dig5=4 THEN SET ans=CONCAT(ans, 'fourty');
WHEN dig5=5 THEN SET ans=CONCAT(ans, 'fifty');
WHEN dig5=6 THEN SET ans=CONCAT(ans, 'sixty');
WHEN dig5=7 THEN SET ans=CONCAT(ans, 'seventy');
WHEN dig5=8 THEN SET ans=CONCAT(ans, 'eighty');
WHEN dig5=9 THEN SET ans=CONCAT(ans, 'ninety');
ELSE SET ans=ans;
END CASE;
ELSE
SET ans=ans;
END IF;
IF dig4 > 0 THEN
CASE
WHEN dig4=1 THEN SET ans=CONCAT(ans, '-one thousand');
WHEN dig4=2 THEN SET ans=CONCAT(ans, '-two thousand');
WHEN dig4=3 THEN SET ans=CONCAT(ans, '-three thousand');
WHEN dig4=4 THEN SET ans=CONCAT(ans, '-four thousand');
WHEN dig4=5 THEN SET ans=CONCAT(ans, '-five thousand');
WHEN dig4=6 THEN SET ans=CONCAT(ans, '-six thousand');
WHEN dig4=7 THEN SET ans=CONCAT(ans, '-seven thousand');
WHEN dig4=8 THEN SET ans=CONCAT(ans, '-eight thousand');
WHEN dig4=9 THEN SET ans=CONCAT(ans, '-nine thousand');
-- ELSE SET ans=ans;
END CASE;
ELSE
SET ans=CONCAT(ans, ' thousand');
END IF;
END IF;
-- 第3位
IF dig3 > 0 THEN
IF ans<>'' AND (dig4>0 OR dig5>0) AND dig2=0 AND dig1=0 THEN
SET ans=CONCAT(ans,' and ');
ELSEIF ans<>'' AND dig2>0 AND dig1>0 THEN
SET ans=CONCAT(ans,' ');
ELSEIF ans<>'' AND dig4=0 AND dig5=0 THEN
SET ans=CONCAT(ans,' ');
ELSE
SET ans=ans;
END IF;
CASE
WHEN dig3=1 THEN SET ans=CONCAT(ans, ' one hundred');
WHEN dig3=2 THEN SET ans=CONCAT(ans, ' two hundred');
WHEN dig3=3 THEN SET ans=CONCAT(ans, ' three hundred');
WHEN dig3=4 THEN SET ans=CONCAT(ans, ' four hundred');
WHEN dig3=5 THEN SET ans=CONCAT(ans, ' five hundred');
WHEN dig3=6 THEN SET ans=CONCAT(ans, ' six hundred');
WHEN dig3=7 THEN SET ans=CONCAT(ans, ' seven hundred');
WHEN dig3=8 THEN SET ans=CONCAT(ans, ' eight hundred');
WHEN dig3=9 THEN SET ans=CONCAT(ans, ' nine hundred');
-- ELSE SET ans=ans;
END CASE;
ELSEIF dig3 = 0 THEN
IF dig4>0 AND dig2>0 THEN
SET ans=CONCAT(ans,' and ');
ELSE
SET ans=ans;
END IF;
ELSE
SET ans=ans;
END IF;
-- 第2位和第1位
IF dig2 = 1 THEN
SET ans=IF(ans='',ans,CONCAT(ans,' and '));
CASE
WHEN (dig2*10 + dig1) = 10 THEN SET ans=CONCAT(ans, ' ten');
WHEN (dig2*10 + dig1) = 11 THEN SET ans=CONCAT(ans, ' eleven');
WHEN (dig2*10 + dig1) = 12 THEN SET ans=CONCAT(ans, ' twelve');
WHEN (dig2*10 + dig1) = 13 THEN SET ans=CONCAT(ans, ' thirteen');
WHEN (dig2*10 + dig1) = 14 THEN SET ans=CONCAT(ans, ' fourteen');
WHEN (dig2*10 + dig1) = 15 THEN SET ans=CONCAT(ans, ' fifteen');
WHEN (dig2*10 + dig1) = 16 THEN SET ans=CONCAT(ans, ' sixteen');
WHEN (dig2*10 + dig1) = 17 THEN SET ans=CONCAT(ans, ' seventeen');
WHEN (dig2*10 + dig1) = 18 THEN SET ans=CONCAT(ans, ' eighteen');
WHEN (dig2*10 + dig1) = 19 THEN SET ans=CONCAT(ans, ' nineteen');
END CASE;
ELSEIF dig2 = 0 THEN
IF ans<>'' AND dig1>0 THEN
SET ans=CONCAT(ans,' and ');
ELSE
SET ans=ans;
END IF;
CASE
WHEN ans<>'' AND dig1=0 THEN SET ans=CONCAT(ans, ' ');
WHEN dig1=1 THEN SET ans=CONCAT(ans, ' one');
WHEN dig1=2 THEN SET ans=CONCAT(ans, ' two');
WHEN dig1=3 THEN SET ans=CONCAT(ans, ' three');
WHEN dig1=4 THEN SET ans=CONCAT(ans, ' four');
WHEN dig1=5 THEN SET ans=CONCAT(ans, ' five');
WHEN dig1=6 THEN SET ans=CONCAT(ans, ' six');
WHEN dig1=7 THEN SET ans=CONCAT(ans, ' seven');
WHEN dig1=8 THEN SET ans=CONCAT(ans, ' eight');
WHEN dig1=9 THEN SET ans=CONCAT(ans, ' nine');
ELSE SET ans=ans;
END CASE;
ELSE
IF dig2 > 0 THEN
SET ans=IF(ans<>'',CONCAT(ans,' and '),ans);
CASE
WHEN dig2=2 THEN SET ans=CONCAT(ans, ' twenty');
WHEN dig2=3 THEN SET ans=CONCAT(ans, ' thirty');
WHEN dig2=4 THEN SET ans=CONCAT(ans, ' fourty');
WHEN dig2=5 THEN SET ans=CONCAT(ans, ' fifty');
WHEN dig2=6 THEN SET ans=CONCAT(ans, ' sixty');
WHEN dig2=7 THEN SET ans=CONCAT(ans, ' seventy');
WHEN dig2=8 THEN SET ans=CONCAT(ans, ' eighty');
WHEN dig2=9 THEN SET ans=CONCAT(ans, ' ninety');
ELSE SET ans=ans;
END CASE;
ELSE SET ans=ans;
END IF;
IF dig1 > 0 THEN
IF dig2>0 THEN
SET ans=CONCAT(ans,'-');
ELSEIF dig2=0 THEN
SET ans=CONCAT(ans,' and ');
END IF;
CASE
WHEN dig1=1 THEN SET ans=CONCAT(ans, ' one');
WHEN dig1=2 THEN SET ans=CONCAT(ans, ' two');
WHEN dig1=3 THEN SET ans=CONCAT(ans, ' three');
WHEN dig1=4 THEN SET ans=CONCAT(ans, ' four');
WHEN dig1=5 THEN SET ans=CONCAT(ans, ' five');
WHEN dig1=6 THEN SET ans=CONCAT(ans, ' six');
WHEN dig1=7 THEN SET ans=CONCAT(ans, ' seven');
WHEN dig1=8 THEN SET ans=CONCAT(ans, ' eight');
WHEN dig1=9 THEN SET ans=CONCAT(ans, ' nine');
ELSE SET ans=ans;
END CASE;
ELSE SET ans=ans;
END IF;
END IF;
SET dec1=LEFT(RIGHT(n,2),1);
SET dec2=RIGHT(RIGHT(n,2),1);
IF trim(ans)='' THEN
SET ans='zero';
END IF;
IF dec1=0 AND dec2=0 THEN
SET ans=CONCAT(ans,' only');
ELSE
SET ans=CONCAT(ans,' point');
CASE
WHEN dec1=1 THEN SET ans=CONCAT(ans, ' one');
WHEN dec1=2 THEN SET ans=CONCAT(ans, ' two');
WHEN dec1=3 THEN SET ans=CONCAT(ans, ' three');
WHEN dec1=4 THEN SET ans=CONCAT(ans, ' four');
WHEN dec1=5 THEN SET ans=CONCAT(ans, ' five');
WHEN dec1=6 THEN SET ans=CONCAT(ans, ' six');
WHEN dec1=7 THEN SET ans=CONCAT(ans, ' seven');
WHEN dec1=8 THEN SET ans=CONCAT(ans, ' eight');
WHEN dec1=9 THEN SET ans=CONCAT(ans, ' nine');
ELSE SET ans=CONCAT(ans, ' zero');
END CASE;
CASE
WHEN dec2=1 THEN SET ans=CONCAT(ans, ' one');
WHEN dec2=2 THEN SET ans=CONCAT(ans, ' two');
WHEN dec2=3 THEN SET ans=CONCAT(ans, ' three');
WHEN dec2=4 THEN SET ans=CONCAT(ans, ' four');
WHEN dec2=5 THEN SET ans=CONCAT(ans, ' five');
WHEN dec2=6 THEN SET ans=CONCAT(ans, ' six');
WHEN dec2=7 THEN SET ans=CONCAT(ans, ' seven');
WHEN dec2=8 THEN SET ans=CONCAT(ans, ' eight');
WHEN dec2=9 THEN SET ans=CONCAT(ans, ' nine');
ELSE SET ans=CONCAT(ans, ' zero');
END CASE;
END IF;
SET ans=REPLACE(ans,' ',' ');
SET ans=REPLACE(ans,'- ','-');
SET ans=REPLACE(ans,'and and','and');
RETURN (UPPER(trim(ans)));
END ||
delimiter;
-- 2、以订单为例：添加金额英文字段（只在序时簿列表显示，供打印用）
SET @modulename='SalesOrder'; -- 这里模块名需要根据需要修改
SET @tabid=(SELECT tabid FROM ld_tab WHERE `name`=@modulename);
SET @relateid=(SELECT block FROM ld_field WHERE tabid=@tabid AND columnname='smownerid' LIMIT 1);
SET @maxseq=IFNULL((SELECT MAX(sequence) FROM ld_field WHERE tabid=@tabid AND block=@relateid),0);
SET @trantable='ld_field';
SET @tablename=(SELECT tablename FROM ld_field WHERE tabid=@tabid AND columnname='smownerid' LIMIT 1);
SET @columnname='total_towords'; -- 这里 columnname 需要根据需要修改
SET @fieldname='total_towords'; -- 这里 fieldname 需要根据需要修改
SET @zh='合计英文'; -- 这里中文标签根据需要修改
SET @en='合计英文'; -- 这里应文标签根据需要修改
SET @uitype=1;
SET @typeofdata='V~O';
SET @quickcreate=1;
SET @displaytype=3; -- displaytype=3 表示只在序时簿列表界面显示
SET @bigtype='text';
SET @datarang=NULL;
SET @maxrowsshow=0;
SET @allowedit='yes';
SET @maxid=(SELECT MAX(fieldid) FROM ld_field);
INSERT INTO ld_field(tabid,fieldid,columnname,tablename,generatedtype,uitype,fieldname,fieldlabel,readonly,presence,defaultvalue,maximumlength,sequence,block,displaytype,typeofdata,quickcreate,quickcreatesequence,info_type,masseditable,helpinfo,fieldonly,approve_demand,is_checkimportfield,bigtype,datarang,maxrowsshow,allowedit)
SELECT @tabid,@maxid+1,@columnname,@tablename,1,@uitype,@fieldname,@zh,1,2,NULL,100,@maxseq+1,@relateid,@displaytype,@typeofdata,@quickcreate,NULL,NULL,1,NULL,0,2,0,@bigtype,@datarang,@maxrowsshow,@allowedit from dual
WHERE NOT EXISTS (SELECT 1 FROM ld_field WHERE tabid=@tabid AND tablename=@tablename AND columnname=@columnname);
UPDATE ld_field_seq SET id=(SELECT max(fieldid) FROM ld_field);
SET @relateid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname=@columnname LIMIT 1);
INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'zh',@trantable,@tabid,@relateid,@zh FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'zh' AND trantable =@trantable AND tabid = @tabid AND relateid=@relateid);
INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'en',@trantable,@tabid,@relateid,@en FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'en' AND trantable =@trantable AND tabid = @tabid AND relateid=@relateid);
CALl AddColumn(@tablename,@columnname,'VARCHAR','255','');
CALL getallprofile(@modulename);
CALL fieldprofile(@modulename);
-- 3、更新现有的历史数据
UPDATE ld_salesorder SET total_towords=func_changeTowords(total);
-- 4、增加工作流程定制合同订单保存后对英文大写赋值
/*
UPDATE ld_salesorder SET total_towords=func_changeTowords(total) WHERE salesorderid='{Record.ID}'
*/
|
|
楼主
