验证中...
Languages: SQL
Categories: 数据库相关
Latest update 2019-11-08 17:38
2222xiugai.sql
Raw Copy
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994
USE [HPSGdsJmszxyydb]
GO
/****** Object: StoredProcedure [dbo].[DT_CONSIS_TRANSDATA_2222] Script Date: 2019/11/8 17:21:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DT_CONSIS_TRANSDATA_2222]
@OPWINID NVARCHAR(40),
--操作终端窗口号(1:启用退费抵扣)
@OPTYPE NVARCHAR(40),
--操作代码
@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处方222接口,按流程处理摆药记录,生成溶媒和主药数据</DESCRIPTION>
<VERSIONS>
<VERSION>1.0</VERSION>
<MODIFYDATE>2015-04-01</MODIFYDATE>
<MODIFYBY>Liu</MODIFYBY>
<REMARK></REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.0</VERSION>
<MODIFYDATE>2015-06-25</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>表完成,数据验证中</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.0</VERSION>
<MODIFYDATE>2015-07-02</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>checkflg=0 从机器发药</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.1</VERSION>
<MODIFYDATE>2015-07-08</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>多药</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.2</VERSION>
<MODIFYDATE>2015-07-21</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>根据容量 计算小车数量</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.3</VERSION>
<MODIFYDATE>2015-09-21</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>非统领</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.5</VERSION>
<MODIFYDATE>2015-10-26</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>多药改用溶媒找主药</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.6</VERSION>
<MODIFYDATE>2015-10-28</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>主药车自动绑定工作台</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.7</VERSION>
<MODIFYDATE>2015-11-11</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>主药车根据格子容量分拆多行</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.8</VERSION>
<MODIFYDATE>2015-11-12</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>order_dtl2拆分,导致溶媒重复修正</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.9</VERSION>
<MODIFYDATE>2015-11-17</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>修正格子分拆方式,结合容量和实际用药数量</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.91</VERSION>
<MODIFYDATE>2015-11-27</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>格子循环次数错误</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>2.92</VERSION>
<MODIFYDATE>2015-12-2</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>增加QD,REMARK1</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>3.0</VERSION>
<MODIFYDATE>2015-12-3</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>一批次多次摆药,溶媒翻倍,增加packageno条件</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>3.0</VERSION>
<MODIFYDATE>2015-12-9</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>单药主药maindrugno重复,截取前六位</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>3.2</VERSION>
<MODIFYDATE>2015-12-11</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>新算法,计算多药的主药排车,每车使用理牌模式,最后更新成药品相邻</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>3.3</VERSION>
<MODIFYDATE>2015-12-15</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>多药排车更新</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>3.4</VERSION>
<MODIFYDATE>2015-12-16</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>多药格子相邻更新</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>3.5</VERSION>
<MODIFYDATE>2015-12-18</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>多药dtl2,maindrugdtl2</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>3.6</VERSION>
<MODIFYDATE>2016-02-18</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>多批次处理</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>3.7</VERSION>
<MODIFYDATE>2016-02-23</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>dt_pivas_maindrug_batch_listdb</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>3.8</VERSION>
<MODIFYDATE>2016-04-11</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>单药品种不跨车</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>3.9</VERSION>
<MODIFYDATE>2016-04-13</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>更正多要数量问题</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>4.0</VERSION>
<MODIFYDATE>2016-04-21</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>单药品种不跨车</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>4.2</VERSION>
<MODIFYDATE>2016-05-05</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>屏蔽多药</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>4.3</VERSION>
<MODIFYDATE>2016-05-09</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>分舱</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>4.4</VERSION>
<MODIFYDATE>2016-05-21</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>溶媒算法合并</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>4.5</VERSION>
<MODIFYDATE>2016-05-30</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>加入住院频次条件</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>4.6</VERSION>
<MODIFYDATE>2016-06-29</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>drug_code改drug_id</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>4.7</VERSION>
<MODIFYDATE>2016-08-11</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>drug_code改drug_id</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>4.8</VERSION>
<MODIFYDATE>2016-08-26</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>非统领溶媒</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>4.9</VERSION>
<MODIFYDATE>2016-10-13</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>退药抵扣</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>5.0</VERSION>
<MODIFYDATE>2017-01-12</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>opwind=1,启用退费抵扣</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>5.1</VERSION>
<MODIFYDATE>2017-01-19</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>opflg3参加退药抵扣</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>5.2</VERSION>
<MODIFYDATE>2017-05-02</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>@opstr临时长期</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>5.3</VERSION>
<MODIFYDATE>2017-05-12</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>222-1合并</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>5.4</VERSION>
<MODIFYDATE>2017-05-18</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>drugtype=3</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>5.5</VERSION>
<MODIFYDATE>2017-06-05</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>非统领</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>5.6</VERSION>
<MODIFYDATE>2017-06-06</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>增加营养舱小车,增加非统领嘱托,修正非统领溶媒bug</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>5.7</VERSION>
<MODIFYDATE>2017-06-13</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>化疗药归入散单</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>5.8</VERSION>
<MODIFYDATE>2017-06-19</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>嘱托表重复bug</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>5.9</VERSION>
<MODIFYDATE>2017-06-22</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>多药全部归入散单</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>6.0</VERSION>
<MODIFYDATE>2017-07-20</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>非统领consistype=1</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>6.1</VERSION>
<MODIFYDATE>2017-08-12</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>#</REMARK>
</VERSIONS>
</VERSIONS>
<VERSIONS>
<VERSION>6.2</VERSION>
<MODIFYDATE>2017-08-17</MODIFYDATE>
<MODIFYBY>FU</MODIFYBY>
<REMARK>order_type:2打包,3营养液</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>6.3</VERSION>
<MODIFYDATE>2017-08-22</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>status</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>6.4</VERSION>
<MODIFYDATE>2017-08-31</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>ordertype=1</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>6.5</VERSION>
<MODIFYDATE>2017-09-07</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>ordertype</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>6.6</VERSION>
<MODIFYDATE>2017-09-08</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>packageno</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>6.7</VERSION>
<MODIFYDATE>2017-09-13</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>4条单药辅药</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>6.8</VERSION>
<MODIFYDATE>2017-09-15</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>bolong_cnt</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>6.9</VERSION>
<MODIFYDATE>2017-10-18</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>bolong_cnt2,count=4</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>6.91</VERSION>
<MODIFYDATE>2017-10-25</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>advicedatint</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>7.0</VERSION>
<MODIFYDATE>2017-11-22</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>[DT_PIVAS_LIMIT_DRUGSVW]</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>7.1</VERSION>
<MODIFYDATE>2017-11-24</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>ward_code</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>7.2</VERSION>
<MODIFYDATE>2017-12-04</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>@indexno=1</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>7.3</VERSION>
<MODIFYDATE>2017-12-25</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>@additionuseage</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>7.4</VERSION>
<MODIFYDATE>2018-01-08</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>advice_batch</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>7.5</VERSION>
<MODIFYDATE>2018-01-08</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>checkflg=2</REMARK>
</VERSIONS>
<VERSIONS>
<VERSION>7.6</VERSION>
<MODIFYDATE>2018-01-24</MODIFYDATE>
<MODIFYBY>fu</MODIFYBY>
<REMARK>HLY</REMARK>
</VERSIONS>
</REMARKROOT>
*/
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
set LOCK_TIMEOUT 100000
--IF EXISTS( SELECT 1 FROM dbo.dt_pivas_menstruum_mstdb WHERE printflg=0 AND packagedateint= dbo.DC_FUNC_DAYINT(GETDATE()))
--BEGIN
-- SET @RETVAL = 0;
-- SET @RETMSG='今日还有未打印的瓶签,停止摆药'
-- RETURN 1
--END
DECLARE @idoc int;
DECLARE @return_value int;
declare @packageno as nvarchar(40)
DECLARE @packageno_1 nvarchar(40)
DECLARE @ORDER_NO_XML nvarchar(40)
DECLARE @orderclass nvarchar(40)
DECLARE @order_cnt INT;
DECLARE @order_cnt1 INT
DECLARE @order_cnt2 int
DECLARE @bolong_cnt INT;
DECLARE @kl_cnt INT;
DECLARE
@advice_date1 datetime,
@advice_date2 datetime,
@advice_batch nvarchar(40),
@dispensary nvarchar(40),
@new_advice_batch numeric(10,0),
@cur_advice_batch numeric(10,0),
@dispensarytype numeric(10,0),
@benchgrade1 nvarchar(40),
@benchgrade2 nvarchar(40),
@benchgrade3 nvarchar(40),
@benchgrade4 nvarchar(40),
@benchgrade5 nvarchar(40),
@benchgrade6 nvarchar(40);
DECLARE @PivasMainDrugCount as numeric(10,0)
if exists ( select advice_batch from [dt_pivas_menstruum_mstdb] where packagedateint=dbo.DC_FUNC_DAYINT(getdate()))
begin
select @new_advice_batch=isnull(max(advice_batch),0)+1 from [dt_pivas_menstruum_mstdb] where packagedateint=dbo.DC_FUNC_DAYINT(getdate())
end
ELSE
BEGIN
SET @new_advice_batch=1
END
--合并模式:1--药品合并;2--病区合并;3--住院号合并
declare
@mergemode1 char(1), --长期医嘱合并模式
@mergemode2 char(1), --临时医嘱合并模式
@mergemode3 char(1), --紧急医嘱合并模式
@mergemode4 char(1), --出院带药合并模式
@MERGEMODE char(1);
declare @icnt numeric(10,0);
--默认调用失败
set @RETVAL = 0;
set @RETMSG = '';
set LOCK_TIMEOUT 1000000
--删除临时表
IF OBJECT_ID('tempdb..#dt_order_dtldb') is not null begin
drop table #dt_order_dtldb;
END;
if OBJECT_ID('tempdb..#order_dtldb') is not null begin
drop table #order_dtldb;
END;
if OBJECT_ID('tempdb..#order_retdtldb') is not null begin
drop table #order_retdtldb;
END;
if OBJECT_ID('tempdb..#order_warddb') is not null begin
drop table #order_warddb;
END;
if OBJECT_ID('tempdb..#order_maindrugdb') is not null begin
drop table #order_maindrugdb;
END;
if OBJECT_ID('tempdb..#order_celldb') is not null begin
drop table #order_celldb;
END;
if OBJECT_ID('tempdb..#order_celldtldb') is not null begin
drop table #order_celldtldb;
END;
if OBJECT_ID('tempdb..#order_multi_maindrugdb') is not null begin
drop table #order_multi_maindrugdb;
END;
if OBJECT_ID('tempdb..#order_maindrug_dtldb') is not null begin
drop table #order_maindrug_dtldb;
END;
if OBJECT_ID('tempdb..#maindrug_checkconsisseqno') is not null begin
drop table #maindrug_checkconsisseqno;
END;
CREATE TABLE #dt_order_dtldb(
[billno] [NVARCHAR](40) NOT NULL,
[seqno] [NUMERIC](10, 0) NOT NULL,
[packageno] [NVARCHAR](40) NOT NULL,
[packagedate] [DATETIME] NOT NULL,
[packagedateint] [NUMERIC](10, 0) NOT NULL,
[mergemode] [CHAR](1) NOT NULL,
[orderclass] [CHAR](1) NOT NULL,
[ordertype] [CHAR](1) NOT NULL,
[order_no] [NVARCHAR](40) NOT NULL,
[ordersub_no] [NVARCHAR](40) NOT NULL,
[advice_code] [NVARCHAR](40) NOT NULL,
[advice_date] [DATETIME] NOT NULL,
[advicedateint] [NUMERIC](10, 0) NOT NULL,
[advicetimeint] [NUMERIC](10, 0) NOT NULL,
[advice_batch] [NUMERIC](10, 0) NOT NULL,
[billdate] [DATETIME] NOT NULL,
[billdateint] [NUMERIC](10, 0) NOT NULL,
[dispensary] [NVARCHAR](40) NULL,
[ward_code] [NVARCHAR](40) NULL,
[ward_name] [NVARCHAR](40) NULL,
[visit_id] [NVARCHAR](200) NULL,
[drugid] [NVARCHAR](40) NOT NULL,
[drug_code] [NVARCHAR](200) NULL,
[drug_spec] [NVARCHAR](200) NULL,
[drug_name] [NVARCHAR](200) NULL,
[firm_id] [NVARCHAR](200) NULL,
[firm_name] [NVARCHAR](200) NULL,
[drug_type] [CHAR](1) NOT NULL,
[quantity] [NUMERIC](20, 6) NOT NULL,
[unit] [NVARCHAR](40) NULL,
[costs] [NUMERIC](20, 6) NOT NULL,
[payments] [NUMERIC](20, 6) NOT NULL,
[dosage] [NVARCHAR](40) NULL,
[dosage_units] [NVARCHAR](40) NULL,
[administration] [NVARCHAR](200) NULL,
[additionusage] [NVARCHAR](200) NULL,
[dispensing_name] [NVARCHAR](40) NULL,
[frequency] [NVARCHAR](200) NULL,
[rcpt_remark] [NVARCHAR](4000) NULL,
[textval1] [NVARCHAR](1000) NULL,
[textval2] [NVARCHAR](1000) NULL,
[textval3] [NVARCHAR](1000) NULL,
[textval4] [NVARCHAR](1000) NULL,
[textval5] [NVARCHAR](1000) NULL,
[quantity1] [NUMERIC](20, 6) NOT NULL,
[quantity2] [NUMERIC](20, 6) NOT NULL,
[quantity3] [NUMERIC](20, 6) NOT NULL,
[quantity4] [NUMERIC](20, 6) NOT NULL,
[quantity5] [NUMERIC](20, 6) NOT NULL,
[pivasflg] [CHAR](1) NOT NULL,
[opflg] [CHAR](1) NOT NULL,
[opflg1] [CHAR](1) NOT NULL,
[opflg2] [CHAR](1) NOT NULL,
[opmanno] [NVARCHAR](40) NULL,
[opmanname] [NVARCHAR](40) NULL,
[allowind] [CHAR](1) NOT NULL,
[lockflg] [CHAR](1) NOT NULL,
[lockposno] [CHAR](6) NOT NULL,
[cellseqno] [NUMERIC](10, 0) NOT NULL,
[flg1] [CHAR](1) NOT NULL,
[flg2] [CHAR](1) NOT NULL,
[flg3] [CHAR](1) NOT NULL,
[flg4] [CHAR](1) NOT NULL,
[flg5] [CHAR](1) NOT NULL,
[flg6] [CHAR](1) NOT NULL,
[status] [CHAR](1) NOT NULL)
create table #order_dtldb(
billno nvarchar(40),
billseqno numeric(10,0),
advice_batch nvarchar(40),
drugid nvarchar(40),
ward_code nvarchar(40),
visit_id nvarchar(200),
quantity numeric(20,6),
packageno nvarchar(40),
orderclass char(1),
mergemode char(1),
drug_type char(1),
status char(1),
ordertype NVARCHAR(40)
);
create table #order_dtl2db(
billno nvarchar(40),
billseqno numeric(10,0),
advice_batch nvarchar(40),
advice_code nvarchar(40),
drugid nvarchar(40),
ward_code nvarchar(40),
visit_id nvarchar(200),
quantity numeric(20,6),
packageno nvarchar(40),
orderclass char(1),
mergemode char(1),
drug_type char(1),
consistype numeric(10,0),
status char(1),
ordertype NVARCHAR(40)
);
create table #order_retdtldb(
billno nvarchar(40),
billseqno numeric(10,0),
drugid nvarchar(40),
ward_code nvarchar(40),
visit_id nvarchar(200),
quantity numeric(20,6),
packageno nvarchar(40),
orderclass char(1),
mergemode char(1),
drug_type char(1),
consistype numeric(10,0),
ordertype NVARCHAR(40)
);
create table #order_warddb(
ward_code nvarchar(40),
ORDER_NO nvarchar(40));
create TABLE #maindrug_checkconsisseqno
(old_checkconsisseqno numeric(10,0),
new_checkconsisseqno numeric(10,0),
maindrugno nvarchar(40));
EXEC sp_xml_preparedocument @idoc OUTPUT, @OPXML;
select @advice_date1=ISNULL(max(a.advice_date1),GETDATE()),@advice_date2=ISNULL(max(a.advice_date2),GETDATE()),
@orderclass = ISNULL(max(a.orderclass),1),
@dispensary=ISNULL(max(a.dispensary),''),
@dispensarytype=ISNULL(max(b.dispensarytype),0),
@mergemode1=ISNULL(max(b.mergemode1),'1'),@mergemode2=ISNULL(max(b.mergemode2),'1'),
@mergemode3=ISNULL(max(b.mergemode3),'1'),@mergemode4=ISNULL(max(b.mergemode4),'1')
FROM OPENXML (@idoc, '/ROOT/CONSIS_ORDER_MSTVW', 2)
WITH (
ADVICE_DATE1 datetime,
ADVICE_DATE2 datetime,
ORDERCLASS nvarchar(40),
DISPENSARY nvarchar(40)
) a,dt_basic_pharmacydb b
where (a.DISPENSARY = b.dispensary);
insert into #order_warddb
SELECT distinct
ward_code,ORDER_NO
FROM OPENXML (@idoc, '/ROOT/CONSIS_ORDER_MSTVW/CONSIS_ORDER_DTLVW', 3)
WITH (
WARD_CODE nvarchar(40),
ORDER_NO nvarchar(40)
) --WHERE ORDER_NO=@OPIP ;
EXEC sp_xml_removedocument @idoc;
--判断9:00-11:00是否勾选第五批次
declare @datehour nvarchar(40);
select @datehour=datepart(hour,getdate())
if(@datehour not in ( '9','10'))and( '5' in (select f1 from SPLIT(@advice_batch,',')))
begin
SET @RETMSG='现在不能导入第五批次!'
return 1;
end
select @icnt = count(1) from #order_warddb;
print @icnt;
print @dispensarytype;
--住院数据导入
if (@dispensarytype=1) begin
if (@icnt > 0) BEGIN
--长期
print @orderclass
print @advice_batch
print @dispensary
IF EXISTS (SELECT f1 from dbo.split(@orderclass,',') WHERE f1='1')
begin
update dt_order_dtldb set lockflg = 'Y' where (opflg= '0')
AND (dispensary = @dispensary)
AND (ward_code in (select ward_code from #order_warddb))
and (order_no in (select order_no from #order_warddb))
--and order_no='OPIP'
AND (advicedateint >= dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY_MINDAYS(ward_code))
AND (advicedateint <= dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY_MAXDAYS(ward_code))
AND dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY(advice_date,dispensary,ward_code,drugid) = 'Y' and
(dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY2(dispensary,ward_code,drugid) = 'Y')
AND orderclass='1'
--AND opflg2='1'
end
IF EXISTS (SELECT f1 from dbo.split(@orderclass,',') WHERE f1<>'1')
begin
--临时
update dt_order_dtldb set lockflg = 'Y' where (opflg= '0')
AND (dispensary = @dispensary)
AND (ward_code in (select ward_code from #order_warddb))
and (order_no in (select order_no from #order_warddb))
AND advice_date>=@advice_date1
and advice_date<=@advice_date2
AND orderclass IN(SELECT f1 from dbo.split(@orderclass,',')WHERE f1<>'1')
--AND opflg2='1'
end
--长期
IF EXISTS (SELECT f1 from dbo.split(@orderclass,',') WHERE f1='1')
begin
print 'dtl'
insert into #order_dtldb
select b.billno,
b.seqno as billseqno,
'' as advice_batch,
b.drugid,
b.ward_code,
b.visit_id,
b.quantity,
order_no as packageno,
b.orderclass,
'0' as mergemode,
b.drug_type,
b.status,
CASE WHEN b.status='N' THEN 2 ELSE CASE WHEN b.textval2='YYY' THEN 3 ELSE CASE WHEN b.textval2='RRR' THEN 4
ELSE CASE WHEN b.textval2='VVV' THEN 5 ELSE 1 END END END END AS [ordertype]
from dt_order_dtldb b with(nolock)
where (b.dispensary = @dispensary) and (b.opflg= '0') and (b.lockflg = 'Y')
AND (b.ward_code in (select ward_code from #order_warddb))
and (order_no in (select order_no from #order_warddb))
AND (advicedateint >= dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY_MINDAYS(ward_code))
AND (advicedateint <= dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY_MAXDAYS(ward_code))
AND dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY(advice_date,dispensary,ward_code,drugid) = 'Y' And
(dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY2(dispensary,ward_code,drugid) = 'Y')
AND b.orderclass='1'
--AND opflg2='1'
end
--临时
IF EXISTS (SELECT f1 FROM dbo.split(@orderclass,',') WHERE f1<>'1')
BEGIN
INSERT INTO #order_dtldb
SELECT b.billno,b.seqno AS billseqno,'' as advice_batch,b.drugid,b.ward_code,b.visit_id,b.quantity,
order_no AS packageno,b.orderclass,'0' AS mergemode,b.drug_type,b.status,CASE WHEN b.status='N' THEN 2 ELSE CASE WHEN b.textval2='YYY' THEN 3 ELSE CASE WHEN b.textval2='RRR' THEN 4
ELSE CASE WHEN b.textval2='VVV' THEN 5 ELSE 1 END END END END AS [ordertype]
FROM dt_order_dtldb b WITH(NOLOCK)
WHERE (b.dispensary = @dispensary) AND (b.opflg= '0') AND (b.lockflg = 'Y')
AND (b.ward_code IN (SELECT ward_code FROM #order_warddb))
and (order_no in (select order_no from #order_warddb))
AND advice_date>=@advice_date1
AND advice_date<=@advice_date2
AND orderclass IN(SELECT f1 FROM dbo.split(@orderclass,',') WHERE f1<>'1')
--AND opflg2='1'
END
END
ELSE BEGIN
--长期
IF EXISTS (SELECT f1 from dbo.split(@orderclass,',') WHERE f1='1')
begin
update dt_order_dtldb set lockflg = 'Y'
where (advicedateint >= dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY_MINDAYS(ward_code))
AND (advicedateint <= dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY_MAXDAYS(ward_code))
AND dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY(advice_date,dispensary,ward_code,drugid) = 'Y' And
(dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY2(dispensary,ward_code,drugid) = 'Y')
and (opflg= '0')
and (dispensary = @dispensary)
AND orderclass='1'
--AND opflg2='1'
end
--临时
IF EXISTS (SELECT f1 from dbo.split(@orderclass,',') WHERE f1<>'1')
begin
update dt_order_dtldb set lockflg = 'Y'
where
(opflg= '0')
and (dispensary = @dispensary)
AND advice_date>=@advice_date1
and advice_date<=@advice_date2
AND orderclass in (SELECT f1 from dbo.split(@orderclass,',') WHERE f1<>'1')
--AND opflg2='1'
end
--长期
IF EXISTS (SELECT f1 from dbo.split(@orderclass,',') WHERE f1='1')
begin
insert into #order_dtldb
select b.billno,b.seqno as billseqno,'' as advice_batch,b.drugid,b.ward_code,b.visit_id,b.quantity,
order_no as packageno,b.orderclass,'0' as mergemode,b.drug_type,b.status,CASE WHEN b.status='N' THEN 2 ELSE CASE WHEN b.textval2='YYY' THEN 3 ELSE CASE WHEN b.textval2='RRR' THEN 4
ELSE CASE WHEN b.textval2='VVV' THEN 5 ELSE 1 END END END END AS [ordertype]
from dt_order_dtldb b with(nolock)
where (advicedateint >= dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY_MINDAYS(ward_code))
AND (advicedateint <= dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY_MAXDAYS(ward_code))
AND dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY(advice_date,dispensary,ward_code,drugid) = 'Y' And
(dbo.DT_FUNC_ORDER_GET_WARD_FREQUENCY2(dispensary,ward_code,drugid) = 'Y')
AND b.orderclass='1'
and (b.dispensary = @dispensary) and (b.opflg= '0') and (b.lockflg = 'Y')
--AND opflg2='1'
end
--临时
IF EXISTS (SELECT f1 FROM dbo.split(@orderclass,',') WHERE f1<>'1')
BEGIN
INSERT INTO #order_dtldb
SELECT b.billno,b.seqno AS billseqno,'' as advice_batch,b.drugid,b.ward_code,b.visit_id,b.quantity,
order_no AS packageno,b.orderclass,'0' AS mergemode,b.drug_type,b.status,CASE WHEN b.status='N' THEN 2 ELSE CASE WHEN b.textval2='YYY' THEN 3 ELSE CASE WHEN b.textval2='RRR' THEN 4
ELSE CASE WHEN b.textval2='VVV' THEN 5 ELSE 1 END END END END AS [ordertype]
FROM dt_order_dtldb b WITH(NOLOCK)
WHERE advice_date>=@advice_date1
AND advice_date<=@advice_date2
AND orderclass IN (SELECT f1 FROM dbo.split(@orderclass,',') WHERE f1<>'1')
AND (b.dispensary = @dispensary) AND (b.opflg= '0') AND (b.lockflg = 'Y')
--AND opflg2='1'
END
END;
END
SELECT @icnt=COUNT(1) FROM #order_dtldb
if (@icnt = 0)
BEGIN
SET @RETMSG='选择的条件中没有医嘱数据,请检查是否已到摆药时间'
set @RETVAL = 0;
RETURN 0
END
--住院药房处理
if (@dispensarytype = 1) BEGIN
--是否启用退费
IF @OPWINID<>'0'
begin
update dt_order_retdtldb set lockflg = 'Y' WHERE (billdate>= dbo.DC_FUNC_DAY(dbo.DC_FUNC_DAYINT(GETDATE())-1)) and
(billdate<=(GETDATE())) and (opflg IN('0','3')) and lockflg<>'Y' AND
(dispensary = @dispensary) and
(ward_code in (select ward_code from #order_warddb));
insert into #order_retdtldb
select b.billno,b.seqno as billseqno,b.drugid,b.ward_code,b.visit_id,b.quantity,
order_no as packageno,b.orderclass,'3' as mergemode,b.drug_type,1,b.ordertype
from dt_order_retdtldb b with(nolock)
where (b.billdate>= dbo.DC_FUNC_DAY(dbo.DC_FUNC_DAYINT(GETDATE())-1)) and
(b.billdate<=(GETDATE()))
and (b.dispensary = @dispensary) and (b.opflg IN('0','3')) and (b.lockflg = 'Y') and
(b.ward_code in (select ward_code from #order_warddb));
END
SET @mergemode1 = '3';
SET @mergemode2 = '3';
SET @mergemode3 = '3';
--删除包编号列表
declare @rdno varchar(100)
declare order_dtl_ORDER_NO cursor for select ORDER_NO from #order_warddb group by ORDER_NO
open order_dtl_ORDER_NO
fetch next from order_dtl_ORDER_NO
into @rdno;
while @@fetch_status = 0
begin
delete from dt_order_package_listdb where (dispensary = @dispensary) and (clientno = @rdno);
--医嘱
EXEC @return_value = [dbo].[DT_CONSIS_TRANSDATA_222_1]
@DISPENSARY = @dispensary,
@ORDERCLASS = @orderclass,
@MERGEMODE = @mergemode1,
@ADVICE_BATCH = 1,
@OPIP = @rdno,
@OPMANNO = @OPMANNO,
@OPMANNAME = @OPMANNAME,
@RETVAL = @RETVAL OUTPUT,
@RETMSG = @RETMSG OUTPUT
set @rdno=''
fetch next from order_dtl_ORDER_NO
into @rdno;
end;
close order_dtl_ORDER_NO;
deallocate order_dtl_ORDER_NO;
----临时医嘱
--EXEC @return_value = [dbo].[DT_CONSIS_TRANSDATA_222_1]
--@DISPENSARY = @dispensary,
--@ORDERCLASS = '2',
--@MERGEMODE = @mergemode2,
--@ADVICE_BATCH = 1,
--@OPIP = @OPIP,
--@OPMANNO = @OPMANNO,
--@OPMANNAME = @OPMANNAME,
--@RETVAL = @RETVAL OUTPUT,
--@RETMSG = @RETMSG OUTPUT
----紧急医嘱
--EXEC @return_value = [dbo].[DT_CONSIS_TRANSDATA_222_1]
--@DISPENSARY = @dispensary,
--@ORDERCLASS = '3',
--@MERGEMODE = @mergemode3,
--@ADVICE_BATCH = 1,
--@OPIP = @OPIP,
--@OPMANNO = @OPMANNO,
--@OPMANNAME = @OPMANNAME,
--@RETVAL = @RETVAL OUTPUT,
--@RETMSG = @RETMSG OUTPUT
--update dt_order_retdtldb SET opflg = '1'
--WHERE (billdate>= dbo.DC_FUNC_DAY(dbo.DC_FUNC_DAYINT(GETDATE())-1)) and
-- (billdate<=(GETDATE()))
-- and (dispensary = @dispensary) and (opflg= '0') and (lockflg = 'Y') and
-- (ward_code in (select ward_code from #order_warddb));
update dt_order_dtldb set packageno = a.packageno,packagedate = GETDATE(),packagedateint = dbo.DC_FUNC_DAYINT(GETDATE()),
mergemode=a.mergemode,pivasflg='1',opflg = '1',textval3=@OPIP
from #order_dtldb a
where (dt_order_dtldb.billno = a.billno) and (dt_order_dtldb.seqno = a.billseqno)
update dt_order_retdtldb set packageno = a.packageno,packagedate = GETDATE(),packagedateint = dbo.DC_FUNC_DAYINT(GETDATE()),
mergemode=a.mergemode,pivasflg='1',opflg = '1'
from #order_retdtldb a
where (dt_order_retdtldb.billno = a.billno) and (dt_order_retdtldb.seqno = a.billseqno)
UPDATE dt_order_retdtldb SET dt_order_retdtldb.opflg1='1'
FROM #order_retdtldb a INNER JOIN #order_dtldb b ON a.ward_code=b.ward_code AND a.drugid=b.drugid
WHERE (dt_order_retdtldb.billno = a.billno) AND (dt_order_retdtldb.seqno = a.billseqno)
--更新发药后退药的记录
--是否启用退费
--IF @OPWINID<>'0'
--begin
-- SELECT @packageno_1= ISNULL(MAX(packageno),'000000') FROM #order_dtldb WHERE (orderclass=@OPSTR) AND (orderclass<>'4');
-- update dt_order_retdtldb set packageno=@packageno_1,packagedate = GETDATE(),packagedateint = dbo.DC_FUNC_DAYINT(GETDATE())
-- where (packageno='000000') AND (opflg='3') AND lockflg = 'Y' AND (orderclass=@OPSTR)
--end;
END;
;
--删除临时表
IF OBJECT_ID('tempdb..#dt_order_dtldb') IS NOT NULL BEGIN
DROP TABLE #dt_order_dtldb;
END;
IF OBJECT_ID('tempdb..#order_dtldb') IS NOT NULL BEGIN
DROP TABLE #order_dtldb;
END;
IF OBJECT_ID('tempdb..#order_retdtldb') IS NOT NULL BEGIN
DROP TABLE #order_retdtldb;
END;
IF OBJECT_ID('tempdb..#order_warddb') IS NOT NULL BEGIN
DROP TABLE #order_warddb;
END;
IF OBJECT_ID('tempdb..#checkcartype1') IS NOT NULL BEGIN
DROP TABLE #checkcartype1;
END;
IF OBJECT_ID('tempdb..#checkcartype2') IS NOT NULL BEGIN
DROP TABLE #checkcartype2;
END;
IF OBJECT_ID('tempdb..#maindrug_checkconsisseqno') IS NOT NULL BEGIN
DROP TABLE #maindrug_checkconsisseqno;
END;
SET @RETVAL = 1;
--SET @RETMSG = '摆药确认完成!';
RETURN 1;
END;

Comment list( 0 )

Sign in for post a comment

Help Search