/******1.创建本地视图，否则每一次访问都要写好长的SQL语句******/

create view TJ_HIS_PRODUCT AS (select * from openquery(TJ_HIS_TEST,

‘SELECT * FROM HR.V_BJP_INTERFACE_MEDICINEINFO‘))

/******2.存储过程细节******/

USE [库]

GO

/******  Object:  StoredProcedure  

[dbo].[SysUpdateProductnew]    Script  Date: 12/02/2016 10:00:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SysUpdateProductnew]

AS

/*

第一步插入新数据

*/

DECLARE @DRUG_CODE varchar(20),@goodsid varchar(20),@maker_id varchar(50),@spec varchar(50),@DRUG_NAME varchar(150),@unit varchar(10),

@styleid int,@pieceratio VARCHAR(20),@makername varchar(150),@Mnemonic varchar(30),@rtprice numeric(18, 4),@wsprice numeric(18, 4),@TOXI_PROPERTY VARCHAR(20),@min_spec varchar(20),@v_sub_package int,@mini_unit varchar(30),@ExtFileds5 varchar(5)

declare Sys_product_cur scroll cursor for 

select distinct DRUG_CODE,

              DRUG_NAME,

              INPUT_CODE,

              DRUG_SPEC,

              DRUG_UNITS,

              TRADE_PRICE,

              RETAIL_PRICE,

              FIRM_ID,

              FIRM_NAME,

              TOXI_PROPERTY,

              MIN_SPEC,

              AMOUNT_PER_PACKAGE,

              Min_units,

              Drug_INDICATOR

       FROM TJ_HIS_PRODUCT THP

       WHERE NOT EXISTS(SELECT 1

              FROM  PRODUCT

               WHERE PRODUCT.drug_id = THP.DRUG_CODE

               AND PRODUCT.SPEC = THP.DRUG_SPEC

               AND PRODUCT.MAKERID = THP.FIRM_ID

               AND PRODUCT.UNIT = THP.DRUG_UNITS)

open Sys_product_cur

fetch first from Sys_product_cur  into @DRUG_CODE,@DRUG_NAME,@Mnemonic,@spec,@unit,

@wsprice ,@rtprice ,@maker_id,@makername ,@TOXI_PROPERTY ,@min_spec,@v_sub_package,@mini_unit,@ExtFileds5

while @@fetch_status=0

BEGIN

--10位商品编码

select @goodsid=right(‘0000000000‘+cast(isnull(max(goodsid),0)+1 as varchar(10)),10) from product

INSERT into product(warehouseid,goodsid,Description,spec,unit,styleid,ControlId,

              PieceRatio,MidRatio,AbcCode,MakerId,MakerName,Mnemonic,CaseScale,

               RtPrice,WsPrice,CreateDate,CreateMan,ModifiedDate,drug_id,status,orgid,ExtFields1,ExtFileds2,ExtFileds3,MiniUnit,ExtFileds5)

values   (‘Y30‘,@goodsid,@DRUG_NAME,@spec,@unit,@styleid,0,

              @pieceratio,0,‘C‘,@maker_id,@makername,@Mnemonic,0,

               @rtprice,@wsprice,getdate(),‘SA‘,getdate(),@DRUG_CODE,0,‘102‘,@TOXI_PROPERTY,@min_spec,@v_sub_package,@mini_unit,@ExtFileds5)

  fetch next from  Sys_product_cur  into @DRUG_CODE,@DRUG_NAME,@Mnemonic,@spec,@unit,

@wsprice ,@rtprice ,@maker_id,@makername ,@TOXI_PROPERTY ,@min_spec,@v_sub_package,@mini_unit,@ExtFileds5

END

close Sys_product_cur

deallocate Sys_product_cur

--第二步，修改已存商品的信息

BEGIN

update product

   SET PRODUCT.PieceRatio = ‘1‘,

       PRODUCT.RTPRICE = TJ_HIS_PRODUCT.RETAIL_PRICE,

       PRODUCT.WsPrice = TJ_HIS_PRODUCT.TRADE_PRICE,

       PRODUCT.BuyPrice = TJ_HIS_PRODUCT.TRADE_PRICE,

       PRODUCT.MiniUnit = TJ_HIS_PRODUCT.MIN_UNITS,--最小单位

       PRODUCT.ExtFileds3 = TJ_HIS_PRODUCT.AMOUNT_PER_PACKAGE,

       PRODUCT.Description = TJ_HIS_PRODUCT.DRUG_NAME,

       PRODUCT.ExtFileds5 = TJ_HIS_PRODUCT.DRUG_INDICATOR,--中，西药，中成药标识

       product.Mnemonic = TJ_HIS_PRODUCT.INPUT_CODE

from TJ_HIS_PRODUCT,product

where TJ_HIS_PRODUCT.DRUG_CODE = product.drug_id

  AND TJ_HIS_PRODUCT.DRUG_SPEC = product.Spec

  AND TJ_HIS_PRODUCT.DRUG_UNITS = product.Unit

  AND TJ_HIS_PRODUCT.FIRM_ID = product.MakerId

END

--3.过滤停价药品

BEGIN

UPDATE Product SET status = ‘9‘ FROM Product WHERE status<>‘9‘ AND NOT EXISTS(

SELECT * FROM TJ_HIS_PRODUCT  V WHERE Product.drug_id = V.DRUG_CODE AND PRODUCT.Spec = V.DRUG_SPEC AND PRODUCT.Unit = V.DRUG_UNITS AND PRODUCT.MakerId = V.FIRM_ID)

END