CREATE TABLE piclink2(refid VARCHAR(40),attachid NVARCHAR(191),aid INT IDENTITY(1,1),pictypeid CHAR(11),picname NVARCHAR(128),entid CHAR(11))
INSERT INTO PIClink2
SELECT c.GoodsId+e.AngleId AS refid,'','PICIQRSS6Y2' AS pictypeid,rtrim(ltrim(substring(a.pathname,33,len(a.pathname)))),'E3NQODPWQJT' endtid FROM hycg.dbo.medReport a JOIN hycg.dbo.storehouse b ON a.p_id=b.p_id
AND a.batchno=b.batchno
JOIN goodsdoc c ON a.p_id=c.OldCode
JOIN goodsattr d ON c.GoodsId=d.GoodsId JOIN batchcode e ON b.batchno=e.batchcode AND c.GoodsId=e.GoodsId JOIN anglebalance f ON e.angleid=f.angleid AND e.goodsid=f.goodsid
WHERE a.pathname LIKE '%检验报告%'
UPDATE piclink3 SET picname=rtrim(ltrim(substring(picname,7,len(picname))))
注意这点:智友文件名取的ATTACHID的名字
应该把GOODSID+ANGLEID改为商品ID+批号,可以避免重复批号药检上传
ANNEXID的最大编号
SELECT max(ANNEXID) FROM KMATTACHMENT
UPDATE dbo.SYSCODE
SET RECNUM = 1873
WHERE PREFIX = 'A'
GO
PLACEID与KMFOLDER里一致。
注意文件名前缀序号和KMATTACHMENT的ID一致
最好改成GOODSID+批号。
图片文件拷贝采用EXCEL生成COPY命令,然后粘贴为BAT文件
copy G:\PicData\PicData\PicData\2020-01\33242_2446_5_191017_培坤丸_191017.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy G:\PicData\PicData\PicData\2020-01\33279_3637_2651_180701_鸡骨草180701.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy G:\PicData\PicData\PicData\2019-08\30829_16292_2597_190401-01_南寒水石190401-01.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy G:\PicData\PicData\PicData\2019-10\31624_16402_2052_190901_黑芝麻190901.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy G:\PicData\PicData\PicData\2019-09\31111_16374_2261_20161002_龙血竭20161002.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy E:\PicData\2015-12\3621_2189_150402_金果揽150402.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy G:\PicData\PicData\2017-04\17247_175_2189_161201_丁香161201.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy G:\PicData\PicData\2017-04\17247_175_2189_161201_丁香161201.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy E:\PicData\2015-04\216_2189_140501_鸦胆子140501.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy G:\PicData\PicData\PicData\2019-09\31291_329_2561_180801-X_香加皮180801-X.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy E:\PicData\2016-01\6628_1498_20150501_马钱子.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01
copy E:\PicData\2016-08\10741_2053_20160801_杜仲20160801.jpg E:\检验报告\PicData\skpic\E3NQODPWQJT\2022-05-01