------------------------------------------------------
-- Export file for user SD_HOSPITAL                 --
-- Created by Administrator on 2017-07-31, 14:54:19 --
------------------------------------------------------

spool SP_SST_JYPDQH_YKT.log

prompt
prompt Creating procedure SP_SST_JYPDQH_YKT
prompt ====================================
prompt
CREATE OR REPLACE PROCEDURE SD_HOSPITAL.SP_SST_JYPDQH_YKT
(
 P_ZDBH00    in   varchar2,--入参: 终端编号
 P_YYJGDM    in   varchar2,--入参:医院机构代码
 P_CARDNO    in   char,--  入参: 卡号
 P_CARDTYPE  in   varchar2, --  入参: 卡类型
 P_ERRMSG   out  varchar2 --  出参：错误信息，空表示正确
)
as
    V_brid00    ic_yhxx00.brid00%type;
    V_xmyxts    number(5);
    V_counter   number(5);
    V_brxm00    bm_brxxb0.brxm00%type;
    V_brxb00    bm_brxxb0.brxb00%type;
    V_brnl00    varchar2(20);
    V_yjdjh0    varchar2(2000);
    V_brqfxx    varchar2(2000);
    V_fbbh00    bm_brxxb0.fbbh00%type;
    V_ybsfjs    varchar2(200);
    V_hmscfs    varchar2(200);
    V_dllxid    JY_DLLX00.dllxid%type;
    V_jzms00    varchar2(200);
    V_hmyxjb    varchar2(200);
    V_ickh00    ic_yhxx00.ickh00%type;
    V_ddrs00    number(5);
    V_phh000    varchar2(200);
  V_SYSMSG  varchar2(2000);
  ls_error  varchar2(2000);
  V_pdhm00 varchar2(10);
    cursor cur_yjdjh is
        select YJDJH0
          from jy_yw0000
         where sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD')
           and sfbz00 in ('0','1') and brid00=V_brid00 ;
begin
ls_error:='1';
--占用标志释放
    update xt_xtcs00 set value0='0' where name00='JY_DLCRBZQK';
--判断卡号
    if P_CARDNO='' then
       P_ERRMSG:='非法卡号！';
       return;
    end if;
ls_error:=ls_error||'2';
--判断卡类别
    select SF_SST_GETICKH00(P_CARDNO) into V_ickh00 from dual;
--判断卡号有效状态
    begin
        select brid00 into V_brid00 from ic_yhxx00 where ickh00=V_ickh00 and zt0000='1';
    exception
    when no_data_found then
       P_ERRMSG:=P_CARDNO||'属于无效卡或未开卡！';
       return;
    end;
ls_error:=ls_error||'3';
--取参数值：项目有效天数JY_JYQHXMYXTS
    begin
        select to_number(value0) into V_xmyxts from xt_xtcs00 where name00='JY_JYQHXMYXTS';
    exception
    when others then
        V_xmyxts:=10;
    end;
ls_error:=ls_error||'4';
--获取病人基本信息
    begin
        select brxm00,brxb00,sf_bq_csrqtonl(brcsrq),fbbh00 into V_brxm00,V_brxb00,V_brnl00,V_fbbh00 from bm_brxxb0 where brid00=V_brid00;
    exception
    when others then
        P_ERRMSG:=P_CARDNO||'获取病人基本信息发生错误！';
        return;
    end;
ls_error:=ls_error||'5';
--判断是否存在未取号的项目信息
    select count(*) into V_counter
      from jy_yw0000
     where mzzybz='0' and sqrq00 between to_char(sysdate-V_xmyxts,'YYYYMMDD') and to_char(sysdate,'YYYYMMDD')
       and BRID00=V_brid00 and zlxmid not in (1076) AND JGZT00 IN ('0');
    if V_counter=0 then
        P_ERRMSG:=V_brxm00||'您没有检验项目,无法打印号码....！';
        return;
    end if;
ls_error:=ls_error||'6';
--获取项目信息
    V_yjdjh0:='0';
    for yw in cur_yjdjh loop
        V_yjdjh0:=V_yjdjh0||','||yw.yjdjh0;
    end loop;
    if V_yjdjh0<>'' then
       V_yjdjh0:=substr(V_yjdjh0,1,length(V_yjdjh0)-1) ;
    end if;
ls_error:=ls_error||'7';
--判断欠费信息
    begin
        select SF_JY_HQQFXX(V_brid00,'',V_yjdjh0,'',0,0,0) into V_brqfxx from dual;
    exception
    when others then
        P_ERRMSG:=V_brxm00||'获取账户信息失败！';
        return;
    end;
    V_brqfxx:=trim(substr(V_brqfxx,instr(V_brqfxx,'|',1,2)+1,length(V_brqfxx)));
    if V_brqfxx<>'0' then
        P_ERRMSG:= V_brxm00||'病人金额不够，请先到收费处补交：' || V_brqfxx|| ' 元后再操作！';
        return;
    end if;
ls_error:=ls_error||'8';
--判断医保病人是否结算
    select SF_JY_PDYBBRSFJS(V_brid00,V_yjdjh0) into V_ybsfjs from dual;
    if V_ybsfjs='N' then
        P_ERRMSG:= V_brxm00||'您还没有结算，请先到收费处结算后再取号！';
        return;
    end if;
ls_error:=ls_error||'9';
--判断是否已取过号，且号还是有效状态
    select count(*) into V_counter
      from jy_pdjhb0
     where brid00=V_brid00
       and jhbz00 in ('0','1','3') --and sfdy00='Y'
     and PHRQ00=to_char(sysdate,'YYYYMMDD');
    if V_counter>0 then
      select max(DLH000) into V_pdhm00   from jy_pdjhb0  where brid00=V_brid00 and jhbz00 in ('0','1','3') --and sfdy00='Y'
       and PHRQ00=to_char(sysdate,'YYYYMMDD');
        P_ERRMSG:= V_brxm00||'您已经取过号,并且你的号还有效,不需要再取！'||'您的号码'||V_pdhm00||'号';
        return;
    end if;
ls_error:=ls_error||'a';
--取参数值：判断取号方式JY_JYQHFMSCFS
    begin
        select trim(value0) into V_hmscfs from xt_xtcs00 where name00='JY_JYQHFMSCFS';
    exception
    when others then
        V_hmscfs:='0';
    end;

/*
--获取急诊模式参数
        begin
            select trim(value0) into V_jzms00 from xt_xtcs00 where name00='JY_JYQHJZMS';
        exception
        when others then
            V_jzms00:='0';
        end;
*/
 ls_error:=ls_error||'b';
    if V_hmscfs='0' then  --南平市医院 只取一个号
--获取队列类型
 ls_error:=ls_error||'b.1'||','||V_xmyxts||','||V_brid00;
        V_dllxid:=sf_jyqh_getdllx('0',V_xmyxts,V_brid00);
--判断优先级别，即是否急诊
 ls_error:=ls_error||'b.2'||','||V_xmyxts||','||V_brid00||','||V_dllxid;
        V_hmyxjb:=sf_jyqh_getjzbz('0',V_xmyxts,V_brid00,V_dllxid);
--生成队列号表
 ls_error:=ls_error||'b.3'||','||V_brid00||','||V_dllxid||','||V_hmyxjb||','||V_ddrs00||','||V_phh000;
        sp_jyqh_scdlhb(V_brid00,V_dllxid,V_hmyxjb,V_ddrs00,V_phh000);

    elsif V_hmscfs='1' then
 ls_error:=ls_error||'b.4'||','||V_xmyxts||','||V_brid00;
--获取队列类型
        V_dllxid:=sf_jyqh_getdllx('1',V_xmyxts,V_brid00);
 ls_error:=ls_error||'b.5'||','||V_xmyxts||','||V_brid00||','||V_dllxid;
--判断优先级别，即是否急诊
        V_hmyxjb:=sf_jyqh_getjzbz('1',V_xmyxts,V_brid00,V_dllxid);
--生成队列号表
 ls_error:=ls_error||'b.6'||','||V_brid00||','||V_dllxid||','||V_hmyxjb||','||V_ddrs00||','||V_phh000;
        sp_jyqh_scdlhb(V_brid00,V_dllxid,V_hmyxjb,V_ddrs00,V_phh000);

    end if;
ls_error:=ls_error||'c';
--记录jy_pdjhb0
    insert into jy_PDJHB0(DLID00,DLH000,BRID00,brxm00,CYCKBH,CKMC00,PHRQ00,JHCS00,JHBZ00,DLLXID,DLHMYX,QHSJ00)
    values
    (SQ_JY_PDJHB0_DLID00.nextval,V_phh000,V_brid00,V_brxm00,null,null,to_char(SYSdate,'YYYYMMDD'),0,'0',V_dllxid,V_hmyxjb,to_char(sysdate,'HH24:MI:SS'));
--占用标志释放
    update xt_xtcs00 set value0='0' where name00='JY_DLCRBZQK';

--写入检验排队取号信息中间表
    insert into SST_PDJYQH(Zdbh00,Yyjgdm,Cardno,Cardtype,XMING0,DDRS00,DYSJ00,PDH000,TSXX00)
    values
    (P_ZDBH00,P_YYJGDM,P_CARDNO,P_CARDTYPE,V_brxm00,V_ddrs00,to_char(SYSdate,'YYYY-MM-DD HH24:mi:ss'),V_phh000,'');
--修改号表打印标志
--    update jy_PDJHB0 set sfdy00='Y' where brid00=V_brid00 and DLLXID=V_dllxid;
ls_error:=ls_error||'d';
exception
  when others then
    P_ERRMSG:=ls_error||substr(SQLERRM||'执行过程SP_SST_JYPDQH_YKT',1,150);
    raise_application_error(-20011,substrb(P_ERRMSG,1,220));
end;
/


spool off