验证中...
Languages: SQL
Categories: 数据库相关
Latest update 2019-11-01 10:40
220B.sql
Raw Copy
GO
/****** Object: StoredProcedure [dbo].[DT_CONSIS_TRANSDATA_220B] Script Date: 10/25/2019 19:15:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[DT_CONSIS_TRANSDATA_220B]
@OPWINID nvarchar(40),
--操作终端窗口号
@OPTYPE nvarchar(40),
--操作代码222A
@OPXML xml,
--传输参数字符串
@OPSTR nvarchar(1000),
--传输参数字符串
@OPIP nvarchar(200),
--终端IP地址
@OPMANNO nvarchar(200),
--终端操作员编号
@OPMANNAME nvarchar(200),
--终端操作员姓名
@RETVAL numeric(10,0) output,
--返回参数
@RETMSG nvarchar(200) output
--错误信息,文本信息
as
/*
<REMARKROOT>
<DESCRIPTION>XML处方220B接口,医嘱信息</DESCRIPTION>
<VERSIONS>
<VERSION>1.0</VERSION>
<MODIFYDATE>2019-10-22</MODIFYDATE>
<MODIFYBY>HOU</MODIFYBY>
<REMARK></REMARK>
</VERSIONS>
</REMARKROOT>
*/
begin
DECLARE @idoc int;
DECLARE @num int ;
DECLARE @packagedate datetime;
DECLARE @packagedateint numeric(10,0);
DECLARE @advicedateint numeric(10,0);
DECLARE @dispensarytype numeric(10,0);
DECLARE @dispensary numeric(10,0);
DECLARE @drugid nvarchar(40) ;
declare @rcpt_remark nvarchar(4000),
@advice_type nvarchar(200),
@printdate datetime,
@advice_code nvarchar(40),
@advice_date datetime,
@sortseqno numeric(10,0),
@dispensarycode nvarchar(40),
@drug_code nvarchar(200),
@drug_spec nvarchar(200),
@drug_name nvarchar(200),
@firm_id nvarchar(200),
@firm_name nvarchar(200),
@quantity numeric(20,6),
@unit nvarchar(40);
--删除临时表
if OBJECT_ID('tempdb..#order_dtldb') is not null begin
drop table #order_dtldb;
end
create table #order_dtldb(
ADVICE_CODE nvarchar(40),
ADVICE_DATE datetime,
SORTSEQNO numeric(10,6),
dispensarycode nvarchar(40),
DRUG_CODE nvarchar(200),
DRUG_SPEC nvarchar(200),
DRUG_NAME nvarchar(200),
FIRM_ID nvarchar(200),
FIRM_NAME nvarchar(200),
QUANTITY numeric(20,6),
UNIT nvarchar(40),
rcpt_remark nvarchar(4000),
ADVICE_TYPE nvarchar(200),
PRINTDATE datetime
)
--默认调用失败
set @RETVAL = 0;
set @RETMSG = '';
--当前日期
select @packagedate = getdate();
--当前日期序号
select @packagedateint=dbo.dc_func_dayint(@packagedate);
EXEC sp_xml_preparedocument @idoc OUTPUT, @OPXML;
insert into #order_dtldb
select
ltrim(rtrim(a.advice_code)),
a.advice_date,
a.sortseqno ,
ltrim(rtrim(a.dispensary)) ,
ltrim(rtrim(a.drug_code)) ,
ltrim(rtrim(a.drug_spec)) ,
ltrim(rtrim(a.drug_name)) ,
'' firm_id ,
ltrim(rtrim(a.firm_name)),
a.quantity,
ltrim(rtrim(a.unit)),
ltrim(rtrim(a.remark)) ,
ltrim(rtrim(a.advice_type)),
a.printdate
FROM OPENXML (@idoc, '/ROOT/CONSIS_ORDER_MSTVW', 2)
WITH (
Advice_Code nvarchar(40),
Advice_Date datetime,
Sortseqno numeric(10,6),
Dispensary nvarchar(40),
Drug_code nvarchar(200),
Drug_spec nvarchar(200),
Drug_name nvarchar(200),
Firm_name nvarchar(200),
Quantity numeric(20,6),
Unit nvarchar(40),
Remark nvarchar(4000),
Advice_Type nvarchar(200),
Printdate datetime
) a
delete from #order_dtldb where exists (select 1 from dt_order_dtl4db db where db.advice_code+db.drug_code =#order_dtldb.advice_code+#order_dtldb.drug_code)
EXEC sp_xml_removedocument @idoc;
declare cur_view cursor for
select advice_code ,
advice_date ,
sortseqno ,
dispensarycode ,
drug_code ,
drug_spec ,
drug_name ,
firm_id ,
firm_name ,
quantity ,
unit ,
rcpt_remark ,
advice_type ,
printdate
from #order_dtldb
open cur_view;
fetch next from cur_view
into @advice_code ,
@advice_date ,
@sortseqno ,
@dispensarycode ,
@drug_code ,
@drug_spec ,
@drug_name ,
@firm_id ,
@firm_name ,
@quantity ,
@unit ,
@rcpt_remark ,
@advice_type ,
@printdate ;
while @@fetch_status = 0
begin
--获取药房编码
if not exists(select dispensarycode from dt_basic_pharmacydb where dispensarycode=@dispensarycode)
begin
set @RETMSG= '药房编码错误'
set @RETVAL = 0
return 0
end
select @dispensary = dbo.DT_FUNC_GET_DISPENSARY(@dispensarycode),
@dispensarytype =dbo.DT_FUNC_GET_DISPENSARYTYPE (@dispensary);
--获取医嘱时间序号
select @advicedateint=dbo.dc_func_dayint(@advice_date);
--取出drugid
select @drugid = drugid from dbo.dt_basic_drugsdb where drug_code = @drug_code
-- 数据导入
begin
insert into dbo.dt_order_dtl4db
(advice_code,advice_date,advicedateint,sortseqno,advice_batch,packageno,packagedate,packagedateint,
order_no,ordersub_no,dispensary,ward_code,ward_name,visit_id,patient_id,patient_name,date_of_birth,sex,
bed_no,bed_name,drugid,
drug_code,drug_spec,drug_name,firm_id,firm_name,quantity,sentqty,
unit,costs,payments,dosage,dosage_units,administration,additionusage,dispensing_name,frequency,
rcpt_remark,advice_type,printdate, --医院新加
textval1,textval2,textval3,textval4,textval5,
quantity1,quantity2,quantity3,quantity4,quantity5,
pivasflg,opflg,opflg1,opflg2,opmanno,opmanname,
flg1,flg2,flg3,flg4,flg5,flg6,status)
values
(@advice_code,@advice_date,@advicedateint,@sortseqno,0,@advice_code,@packagedate,@packagedateint,
'','','01','','','','','','','',
'','',@drugid,
@drug_code,@drug_spec,@drug_name,@firm_id,@firm_name,@quantity,0,
@unit,0,0,'','','','','','',
@rcpt_remark,@advice_type,@printdate,
'','','','','',
'','','','','',
@dispensarytype,0,0,0,'','',
0,0,0,0,0,0,'A');
end
fetch next from cur_view
into
@advice_code ,
@advice_date ,
@sortseqno ,
@dispensarycode ,
@drug_code ,
@drug_spec ,
@drug_name ,
@firm_id ,
@firm_name ,
@quantity ,
@unit ,
@rcpt_remark ,
@advice_type ,
@printdate ;
end
close cur_view;
deallocate cur_view;
select @num=count(1) from #order_dtldb
if @num >=1
begin
SET @RETMSG =convert(nvarchar(8),@num) + '条数据同步成功';
set @RETVAL = 1
end
else if @num<1
begin
set @RETMSG = '暂无新数据'
set @RETVAL = 0
end
return 1
end

Comment list( 0 )

Sign in for post a comment

Help Search