物料清单输入时，可以按产品类别建模板，也可以用已经建立清单的相似产品的清单做为模板。
本例通过计算当前产品与原有清单产品的相似度，自动初始化一个产品的新建清单数据。
用到表结构：
物料清单表MLQD字段列表： QDID int 4 清单序号 TextEdit CPCHXH varchar 20 产品货号 TextEdit 00000 CPCHDH varchar 30 产品代号 TextEdit JGDH varchar 30 结构代号 TextEdit JC int 4 级次 TextEdit CHXH varchar 20 货号 ButtonEdit CHDH varchar 30 存货代号 ButtonEdit CHMC nvarchar 100 存货名称 TextRead XH1 nvarchar 100 型号 ButtonEdit XH2 nvarchar 100 规格 TextEdit XH3 nvarchar 400 参数 TextEdit GG1 nvarchar 100 颜色 ComboEdit EDIT GG2 nvarchar 100 材质 TextEdit GG3 nvarchar 100 图号 TextEdit JLDW nvarchar 100 计量单位 TextRead CLLB nvarchar 20 材料类别 TextRead DESL decimal 9 定额数量 TextEdit 0.0000;-0,0000;# DESH decimal 9 定额损耗 TextEdit 0.00;-0,00;# WLDW nvarchar 200 往来单位 ButtonEdit SCBM nvarchar 100 生产部门 TextEdit CKMC nvarchar 100 仓库名称 ListEdit JSJD int 4 计算精度 TextEdit HH int 4 行号 TextEdit RQ date 3 日期 DateEdit yyyy-MM-dd SFSH bit 1 是否审核 CheckEdit SHRY nvarchar 40 审核人员 TextEdit
物料清单模板MLQDMB字段列表： JGDH varchar 30 结构代号 TextEdit SJDH varchar 30 上级代号 TextEdit BJDH varchar 30 本级代号 TextEdit JC int 4 级次 TextEdit CPCHDH varchar 30 产品代号 ButtonEdit CHDH varchar 30 存货代号 ButtonEdit CHMC nvarchar 100 存货名称 TextRead XH1 nvarchar 100 型号 ButtonEdit XH2 nvarchar 100 规格 TextRead XH3 nvarchar 400 参数 ComboEdit EDIT GG1 nvarchar 100 颜色 ComboEdit EDIT GG2 nvarchar 100 材质 TextEdit GG3 nvarchar 100 图号 TextEdit JLDW nvarchar 40 计量单位 TextRead CLLB nvarchar 20 材料类别 TextRead DESL decimal 9 定额数量 TextEdit 0.0000 DESH decimal 9 定额损耗 TextEdit 0.00;0,00;# JSJD int 4 计算精度 TextEdit SFQY bit 1 是否启用 CheckEdit
存储过程代码：
ALTER PROCEDURE [dbo].[X9_GLSRMB] (@DQYH NVARCHAR(10), --用户名称 @FMNAME NVARCHAR(30), --窗口名称 @TBNAME NVARCHAR(30), --数据表名称 @GLXH NVARCHAR(30), --目录主表编码 @TJSTR NVARCHAR(MAX)) --条件字符串 AS BEGIN DECLARE @CPCHDH VARCHAR(30); DECLARE @CPCHXH INT; DECLARE @XH1 NVARCHAR(50),@XH2 NVARCHAR(50),@XH3 NVARCHAR(50); DECLARE @GG1 NVARCHAR(50),@GG2 NVARCHAR(50),@GG3 NVARCHAR(50); DECLARE @XSCPCHXH INT,@MAXXSCD INT; --相似产品存货序号 SELECT @CPCHDH=CHDH,@CPCHXH=CHXH,@XH1=XH1,@XH2=XH2,@XH3=XH3,@GG1=GG1,@GG2=GG2,@GG3=GG3 FROM MLCHXM WHERE CHXH=CONVERT(INT,@GLXH); SET @XSCPCHXH=0; SET @MAXXSCD=0; --相似清单 产品序号，相似程度 --下列WITH语句中的 1 可以改为大小不同的参数，以计算合适的相似程度 WITH XSQD (CPCHXH,XSCD) AS (SELECT CPCHXH, (CASE WHEN XH1=@XH1 AND XH1<>'' THEN 1 ELSE 0 END)+ (CASE WHEN XH2=@XH2 AND XH2<>'' THEN 1 ELSE 0 END)+ (CASE WHEN XH3=@XH3 AND XH3<>'' THEN 1 ELSE 0 END)+ (CASE WHEN GG1=@GG1 AND GG1<>'' THEN 1 ELSE 0 END)+ (CASE WHEN GG2=@GG2 AND GG2<>'' THEN 1 ELSE 0 END)+ (CASE WHEN GG3=@GG3 AND GG3<>'' THEN 1 ELSE 0 END) as XSCD FROM MLQD WHERE CPCHDH =@CPCHDH AND JC=1) SELECT @XSCPCHXH=CPCHXH FROM XSQD WHERE XSCD = (SELECT MAX(XSCD) FROM XSQD); IF @XSCPCHXH >0 BEGIN SELECT JGDH, JC, CPCHDH,CHDH, CHMC, @XH1 AS XH1, @XH2 AS XH2, @XH3 AS XH3, @GG1 AS GG1, @GG2 AS GG2, @GG3 AS GG3, JLDW, CLLB, DESL,DESH, JSJD FROM MLQD WHERE (JC = 1) AND (CPCHXH = @XSCPCHXH) UNION ALL SELECT JGDH, JC, CPCHDH,CHDH, CHMC, XH1, XH2, XH3, GG1, GG2, GG3, JLDW, CLLB, DESL,DESH, JSJD FROM MLQD WHERE (JC > 1) AND (CPCHXH = @XSCPCHXH) ORDER BY JGDH; END ELSE BEGIN DECLARE @JGDH VARCHAR(30),@CHDH VARCHAR(30),@JC VARCHAR(30),@DESL DECIMAL(18,6),@XH INT; SET @XH=1; --循环变量 DELETE MLQDMBLSB WHERE YHMC=@DQYH; INSERT INTO MLQDMBLSB (YHMC,CPCHXH,JGDH,SJDH,BJDH,JC,CPCHDH,CHDH,CHMC, XH1,XH2,XH3,GG1,GG2,GG3,JLDW,CLLB,DESL,DESH,JSJD) SELECT @DQYH AS YHMC,@CPCHXH AS CPCHXH,JGDH,SJDH,BJDH,JC,CPCHDH,CHDH,CHMC, @XH1 AS XH1, @XH2 AS XH2, @XH3 AS XH3, @GG1 AS GG1, @GG2 AS GG2, @GG3 AS GG3, JLDW, CLLB, DESL,DESH, JSJD FROM MLQDMB WHERE (JC = 1) AND CPCHDH =@CPCHDH; INSERT INTO MLQDMBLSB (YHMC,CPCHXH,JGDH,SJDH,BJDH,JC,CPCHDH,CHDH,CHMC, XH1,XH2,XH3,GG1,GG2,GG3,JLDW,CLLB,DESL,DESH,JSJD) SELECT @DQYH AS YHMC,@CPCHXH AS CPCHXH,JGDH,SJDH,BJDH,JC,CPCHDH,CHDH,CHMC, XH1, XH2, XH3, GG1, GG2, GG3, JLDW, CLLB, DESL,DESH, JSJD FROM MLQDMB WHERE (JC > 1) AND CPCHDH =@CPCHDH; WHILE @XH=1 BEGIN IF EXISTS(SELECT * FROM MLQDMBLSB WHERE CLLB='子件') BEGIN DECLARE QDMB_cursor CURSOR FOR SELECT JGDH,CHDH,JC,DESL FROM MLQDMBLSB WHERE CLLB='子件' OPEN QDMB_cursor; FETCH FROM QDMB_cursor INTO @JGDH,@CHDH,@JC,@DESL; WHILE @@FETCH_STATUS=0 BEGIN INSERT INTO MLQDMBLSB (YHMC,CPCHXH,JGDH,SJDH,BJDH,JC,CPCHDH,CHDH,CHMC, XH1,XH2,XH3,GG1,GG2,GG3,JLDW,CLLB,DESL,DESH,JSJD) SELECT @DQYH AS YHMC,@CPCHXH AS CPCHXH,@JGDH+RIGHT(JGDH,LEN(JGDH)-LEN(@CHDH)) AS JGDH, SJDH,BJDH,@JC+JC-1 AS JC,@CPCHDH AS CPCHDH,CHDH,CHMC, XH1, XH2, XH3, GG1, GG2, GG3, JLDW, CLLB, @DESL*DESL,DESH, JSJD FROM MLQDMB WHERE CPCHDH=@CHDH AND JGDH<>@CHDH; UPDATE MLQDMBLSB SET CLLB='自制' WHERE JGDH=@JGDH; FETCH NEXT FROM QDMB_cursor INTO @JGDH,@CHDH,@JC,@DESL; END CLOSE QDMB_cursor; DEALLOCATE QDMB_cursor ; END ELSE BEGIN SET @XH=0; END END SELECT JGDH, JC, CPCHDH, CHDH, CHMC, XH1, XH2, XH3, GG1, GG2, GG3, JLDW, CLLB, DESL, DESH,JSJD FROM MLQDMBLSB WHERE (CPCHXH = @CPCHXH) AND (YHMC = @DQYH) ORDER BY JGDH; END RETURN;
代码中的XH1,XH2,XH3,GG1,GG2,GG3为用户可定义的规格型号类字段。MLQDMBLSB是一个临时表。
代码中还包括了子清单（子件）的合并处理。有些系统子件是分开处理的，合并的好处是降低了需要计算的复杂程度。
