课题引出
在生产系统中，常常会碰到单位换算问题，例如：
-
购入一批铝型材，仓库论米，生产切割成不同的长度论根，供应商结算论吨。
-
购入一批钢板，仓库论平方，生产切割论块，供应商结算论吨。
-
购入一批塑料膜，仓库论片，供应商结算论kg，并且四舍五入保留1位小数……
课题升级
村长最近在做采购订单时就遇到了换算问题，需要把库存单位转换成结算单位再下采购订单给供应商。
这个动态换算通过一个提数完成(NX/ES均可)，部分换算归纳如下表：
提数公式本身运作良好，但仅仅在采购订单中使用还不够，还要在采购入库，采购发票，采购对账，采购台账等等功能中都要有所体现。
这就涉及到一个更新维护问题，如果哪天有新的换算方式，那不仅要改采购订单的提数，还要改入库，发票，对账等等环节的提数。
如何一劳永逸的解决换算问题?!
方案评比
通常，动态公式换算问题，除了提数写死，有这几类方案：
- 视图换算，这种换算实际就是：数量*常量，其中常量部分通过视图算出，然后匹配本表料号进行计算。
缺点：仅适合不需要考虑本表临时变量（例如长宽高等）的简单换算。
- 使用EXCEL公式，这个是ES唯一原生支持的方案，从公式表提数填充到本表，类似：
=_克重*_数量*_长*_宽/1000000，其中_克重，_数量，_长，_宽是定义的名称，代表某个字段区域。
缺点：本质是客户端计算，需要构建大量辅助字段，将所有需要的变量都提数体现。
- 使用sql函数，这个是NX原生支持的方案，将换算逻辑封装成一个sql函数，提数时无脑代入变量。
create function fGetAuxQty(
@qty decimal(34,3)=0 --数量
,@kz decimal(34,1)=0 --克重
,@gg varchar(128)='' --规格，例如1000*500或800
--长=left(@gg,charindex('*',@gg+'*')-1),宽=right(@gg,len(@gg)-charindex('*',@gg+'*'))
,@dw varchar(128)='' --库存单位
,@hs varchar(128)='' --换算单位
,@hsxs varchar(128)=0 --换算系数
)
returns decimal(36,6)
as
begin
return
case
when @dw='片' and @hs='kg'
then round(@kz*@qty*cast(left(@gg,charindex('*',@gg+'*')-1)as int)*cast(right(@gg,len(@gg)-charindex('*',@gg+'*'))as int)*(case when @kz<20 then 1.4 else 1.36 end)/100000000.0000,1)
when @dw='m' and @hs='吨'
then @kz*@qty*cast(left(@gg,charindex('*',@gg+'*')-1)as int)/1000000000.000000
when @hs='吨' and @kz>0
then @kz*@qty*cast(left(@gg,charindex('*',@gg+'*')-1)as int)*cast(right(@gg,len(@gg)-charindex('*',@gg+'*'))as int)/1000000000000.000000
when @dw='m' and @hs='m2'
then @qty*cast(left(@gg,charindex('*',@gg+'*')-1)as int)/1000000.000000
when @hs='m2'
then @qty*cast(left(@gg,charindex('*',@gg+'*')-1)as int)*cast(right(@gg,len(@gg)-charindex('*',@gg+'*'))as int)/1000000.000000
else
@qty*(case when isnull(@hsxs,1)=0 then 1 else isnull(@hsxs,1) end)
end
end
缺点：不知道会点sql算不算缺点？
方案落地
打开SSMS，将换算函数sql构建代码填入，F5执行。
接下来，在提数中使用。
对比一下老的提数公式，是不是顿时觉得整个世界都清净了^_^
其他模板也是直接提数调用dbo.fGetAuxQty()，无辅助字段，支持本表和其它表字段代入
最重要的是：如有变更，只需要更新fGetAuxQty()即可全局生效，一劳永逸！
最终效果示意
在采购订单中进行换算：
手改规格后（特别提示，是文本型哦!!!），自动重算，爽歪歪：
小结
-
封装sql自定义函数一劳永逸；
-
万能提数中直接调用sql函数，无辅助字段，随叫随到。
-
感谢NX杨工提供的技术支持和的如此灵活的快开平台，并预祝筒子们元旦快乐~！
2019-12-31