药易通药检报告图片导入时空智友V11

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