尽消存

JDP平台模具配件开发案例

需求1

需求说明

模具配件生产企业在生产过程中，作业师傅通常会按照带有图例的作业单照图生产，可是作业单的人工手写标注通常要耗费大量的人力；
为此，在不影响原有业务系统使用习惯的前提下，我们开发了打印报表来替代人工手写的标注功能。

按用户录入的规格自动拆分

  • 先创建表值函数
CREATE FUNCTION [dbo].[SplitString]
(
    @string NVARCHAR(MAX),
    @delimiter NVARCHAR(10)
)
RETURNS @result TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @pos INT
    DECLARE @counter INT
    SET @counter = 1
    WHILE CHARINDEX(@delimiter, @string) > 0
    BEGIN
        SET @pos = CHARINDEX(@delimiter, @string)
        INSERT INTO @result (Value) VALUES (SUBSTRING(@string, 1, @pos - 1))
        SET @string = SUBSTRING(@string, @pos + LEN(@delimiter), LEN(@string))
        SET @counter = @counter + 1
    END
    INSERT INTO @result (Value) VALUES (@string)
    RETURN
END
  • 编写带有表值函数的拆分脚本
UPDATE tblSalesOrderDet
SET Fis = CAST((SELECT Value FROM (SELECT Value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM dbo.SplitString(yearNO, '-') WHERE CHARINDEX('-', yearNO) > 0 AND      CHARINDEX('-', yearNO) <= 6 AND Value <> '' AND Value IS NOT NULL) AS t WHERE rn = 6) AS varchar(200)),
    Ses = CAST((SELECT Value FROM (SELECT Value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM dbo.SplitString(yearNO, '-') WHERE CHARINDEX('-', yearNO) > 0 AND CHARINDEX('-', yearNO) <= 6 AND Value <> '' AND Value IS NOT NULL) AS t WHERE rn = 5) AS varchar(200)),
    Ths = CAST((SELECT Value FROM (SELECT Value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM dbo.SplitString(yearNO, '-') WHERE CHARINDEX('-', yearNO) > 0 AND CHARINDEX('-', yearNO) <= 6 AND Value <> '' AND Value IS NOT NULL) AS t WHERE rn = 4) AS varchar(200)),
    Fos = CAST((SELECT Value FROM (SELECT Value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM dbo.SplitString(yearNO, '-') WHERE CHARINDEX('-', yearNO) > 0 AND CHARINDEX('-', yearNO) <= 6 AND Value <> '' AND Value IS NOT NULL) AS t WHERE rn = 3) AS varchar(200)),
    Fvs = CAST((SELECT Value FROM (SELECT Value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM dbo.SplitString(yearNO, '-') WHERE CHARINDEX('-', yearNO) > 0 AND CHARINDEX('-', yearNO) <= 6 AND Value <> '' AND Value IS NOT NULL) AS t WHERE rn = 2) AS varchar(200)),
    Sis = CAST((SELECT Value FROM (SELECT Value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM dbo.SplitString(yearNO, '-') WHERE CHARINDEX('-', yearNO) > 0 AND CHARINDEX('-', yearNO) <= 6 AND Value <> '' AND Value IS NOT NULL) AS t WHERE rn = 1) AS varchar(200))
where f_ref=?
  • 打印输出报表增加图例匹配
case  
when 名称包含 '顶针' then '顶针的图例.png' 
when 名称包含 '司筒' then '司筒的图例.png'
end