CREATE OR REPLACE PROCEDURE SP_FZBMPT_PYDLXX(ad_BRID00 number,
                                             ad_DATAID number,
                                             as_XXLX00 varchar2,  --消息类型0配药队列，1余额消息,2取号,3就诊，4检验检查，5取药队列，6取药完成消息推送
                                             ad_YFBMBH number default 0,
                                             ad_FYCKBH number default 0

                                             )
as
  VMBBH00 PT_XXTSB0.MBBH00%type;
  VSFZH00 BM_BRXXB0.BRZJBH%type;
  VJZKH00   IC_YHXX00.KMH000%type;
  VYFMC00   BM_BMBM00.BMMC00%type;
  VFYCKMC   BM_CKBM00.CKMC00%type;
  VYFBMBH   BM_BMBM00.BMBH00%type;
  VFYCKBH   BM_CKBM00.CKBH00%type;
  PYRow     YF_CFDL00%rowtype;
  VYJJYE0    SF_BRZHXX.XJYE00%type;
  VWJZJE0    SF_BRZHXX.XJYE00%type;
  VLXDH00   varchar2(30);
  VYYJC00  XT_YYXX00.YYJC00%type;
  VZHrow   SF_BRZHXX%rowtype;
  VXXNR00  varchar2(500);
  VBRXM00  BM_BRXXB0.BRXM00%type;
  VFBBH00  BM_BRXXB0.FBBH00%type;
  VZT0000  YF_CFDL00.ZT0000%type;
  XT_BMFWXXZL varchar2(10);
  VDLXH00  YF_CFDL00.DLXH00%type;
  VQHRQ00  varchar2(10);
  VGHID00  SF_BRXXB0.GHID00%type;
  VCFLSH0  YF_MZCF00.CFLSH0%type;
  VJGZT00  JY_YW0000.JGZT00%type;
  VSQRQ00  varchar2(20);
  VQHSJ00  varchar2(20);
  VQSSJ00  varchar2(20);
  VBGSJ00  varchar2(20);
  VCOUNT   number(4);
  LS_XXLX00 varchar2(20) ;
  VGHHZCK  sf_hzbr00.ghhzck%type;
  VHZXH00  sf_hzbr00.hzxh00%type;
-- dsm        2017.12.16  create for MZYS-20171216-001
-- dsm        2018.10.24  增加 XT_BMFWXXZL=M的情况
begin
  select nvl(max(trim(VALUE0)),'N') into XT_BMFWXXZL  from XT_XTCS00 where NAME00='XT_BMFWXXZL' and VALUE0 in('Y','M');--便民服务消息整理,Y榕医通，M多码合一
  if XT_BMFWXXZL='N' then
     return;
  end if;
  select KMH000 into VJZKH00  FROM IC_YHXX00 where BRID00=ad_BRID00 and ZT0000='1';
  select BRZJBH,BRXM00,FBBH00 into VSFZH00,VBRXM00,VFBBH00 FROM BM_BRXXB0 where BRID00=ad_BRID00 ;
-- VJZKH00:='111';
--    select '1','1','3' into VSFZH00,VBRXM00,VFBBH00 from BM_BRXXB0 where BRID00=ad_BRID00 ;
  --if VFBBH00<>3 then return;
  --end if;
  LS_XXLX00:=as_XXLX00;
  if as_XXLX00='1' and XT_BMFWXXZL='M' then
      LS_XXLX00:='3';
  end if;
  if as_XXLX00='5' and XT_BMFWXXZL='M' then
      LS_XXLX00:='4';
  end if;
  if XT_BMFWXXZL='Y' then
    if LS_XXLX00='0' then
       VMBBH00:='TM000002916';
       --select YFBMBH,FYCKBH,ZT0000 into VYFBMBH,VFYCKBH,VZT0000 from YF_CFDL00 where BRID00=ad_BRID00 and DLXH00=ad_DATAID and rownum=1;
      -- if VZT0000<>'2' then
       --   return;
      -- end if;
       select BMMC00 into VYFMC00 from BM_BMBM00 where BMBH00=ad_YFBMBH;
       select CKMC00 into VFYCKMC from BM_CKBM00 where CKBH00=ad_FYCKBH;
       VXXNR00:='{'
              ||'"first": "您在'||VYFMC00||'处方已调配完成",'
              ||'"keyword1": "'||VJZKH00||'",'
              ||'"keyword2": "'||VBRXM00||'",'
              ||'"keyword3": {'
              ||'"value": "'||'请持就诊卡到'||VFYCKMC||'取药!",'
              ||'"color": "#FFF000"'
              ||'},'
              ||'"remark": "若已领药，请忽略，请按照医嘱服药，祝您健康！"'
              ||'}' ;
    elsif  LS_XXLX00='1' then
       VMBBH00:='TM000002919';
       select * into VZHrow from SF_BRZHXX where BRID00=ad_BRID00;
       VYJJYE0:=VZHrow.XJYE00;
       select nvl(sum(HJJE00),0) into VWJZJE0 from (
       select sum(ZJE000) HJJE00 from YJ_YW0000 where ZYGHID=ad_DATAID and nvl(SFDJH0,0)=0 and XMZT00 in('0','1')
       union all
       select sum(CFZJE0) HJJE00 from YF_MZCF00 where GHID00=ad_DATAID and nvl(DJH000,0)=0 and CFZT00 in('0')
       );
       if VYJJYE0>VWJZJE0 then
          return;
       end if;
       select YYJC00 into VYYJC00 from XT_YYXX00 where rownum=1;
       VXXNR00:='{'
              ||'"first": "您好，您的账户余额可能不足",'
              ||'"keyword1": "'||VBRXM00||'-'||trim(VYYJC00)||'-预交金'||'",'
              ||'"keyword2": "'||'余额:'||VYJJYE0||'元,未结算金额:'||VWJZJE0||'元'||'",'
              ||'"keyword3": {'
                  ||'"value": "",'
                  ||'"color": "#FFF000"'
                   ||'},'
              ||'"keyword4": "'||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'",'
              ||'"remark": "如有疑问，请拨打'||VLXDH00||'电话，我们将竭诚为您服务！"'
              ||'}'     ;
    end if;
    insert into PT_XXTSB0(ID0000,MBBH00,KMH000,BRZJBH,XXNR00)
    values(SQ_PT_XXTSB0_ID0000.nextval,VMBBH00,VJZKH00,VSFZH00,VXXNR00);
  elsif XT_BMFWXXZL='M' then    --2取号,3就诊，4检验检查，5发药队列，6取药完成消息推送
     if LS_XXLX00='2' then --挂号取号过程用
       VMBBH00:='TM000002930';
       --以下两句注释掉，开发这种写法是有问题的。  20191211 陈雄
       --select CKMC00 into VFYCKMC from BM_CKBM00 where CKBH00=ad_FYCKBH;
       --select XM0000,JZRQ00,JZRQ00||' '||JZSJ00  into VBRXM00,VQHRQ00,VQHSJ00 from SF_BRXXB0 where GHID00=ad_DATAID;
       select a.XM0000,a.JZRQ00,a.JZRQ00||' '||a.JZSJ00,b.GHHZCK,b.hzxh00  into VBRXM00,VQHRQ00,VQHSJ00,VGHHZCK,vhzxh00
       from SF_BRXXB0 a,SF_HZBR00 b  where GHID00=ad_DATAID and a.GHH000=b.GHH000 and rownum=1;
       SELECT CKXXDZ||CKMC00 into VFYCKMC FROM SF_HZBR00 A,SF_YSGHHB B,BM_CKBM00 C,SF_BRXXB0 D
       WHERE A.GHH000=D.GHH000 AND A.YSPBID=B.GHHBID AND B.GHHZCK=C.CKBH00 AND D.GHID00=ad_DATAID;
       select YYJC00 into VYYJC00 from XT_YYXX00 where rownum=1;
       VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VQHSJ00||',在'||VYYJC00||'使用多码融合码取号成功'||'",'
              ||'"keyword1": "'||TRIM(VYYJC00)||'",'
              ||'"keyword2": "'||VQHSJ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用，请到'||VFYCKMC||'候诊！候诊序号为'||VHZXH00||'号！"'
              ||'}' ;
       insert into PT_XXTSB0(ID0000,MBBH00,KMH000,BRZJBH,XXNR00)
       values(SQ_PT_XXTSB0_ID0000.nextval,VMBBH00,VJZKH00,VSFZH00,VXXNR00);

    elsif  LS_XXLX00='3' then  --就诊诊结

       VMBBH00:='TM000002931';
       select XM0000,JZRQ00,JZRQ00||' '||JZSJ00  into VBRXM00,VQHRQ00,VQHSJ00 from SF_BRXXB0 where  GHID00=ad_DATAID;
       select YYJC00 into VYYJC00 from XT_YYXX00 where rownum=1;
       VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VQHSJ00||',在'||VYYJC00||'使用多码融合码就诊成功'||'",'
              ||'"keyword1": "'||TRIM(VYYJC00)||'",'
              ||'"keyword2": "'||VQHSJ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用！"'
              ||'}' ;
       insert into PT_XXTSB0(ID0000,MBBH00,KMH000,BRZJBH,XXNR00)
       values(SQ_PT_XXTSB0_ID0000.nextval,VMBBH00,VJZKH00,VSFZH00,VXXNR00);

    elsif  LS_XXLX00='4' then  --检验检查
       VMBBH00:='TM000002932';
       select BRXM00,JZRQ00  into VBRXM00,VQHRQ00 from SF_JZB000 a,BM_BRXXB0 b  where a.JZDH00=ad_DATAID and a.BRID00=b.BRID00;
       select YYJC00 into VYYJC00 from XT_YYXX00 where rownum=1;
       select MZID00,JZRQ00||' '||JZSJ00 into VGHID00,VQHSJ00 from SF_JZB000 where JZDH00=ad_DATAID;
       for c_jyjc in (select distinct b.BMMC00 from YJ_YW0000 a,BM_BMBM00 b where a.ZYGHID=VGHID00 and a.YJKSBH=b.BMBH00 and nvl(a.SFDJH0,0)>0
          AND A.SL0000>0 AND NOT EXISTS (SELECT 1 FROM YJ_YW0000 H WHERE A.YJDJH0=H.CXDJH0)
         and exists(select 1 from SF_BRFY00 where MZID00=VGHID00 and DJH000=a.SFDJH0 and JZDH00=ad_DATAID )
         )
       loop

       VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VQHSJ00||',在'||VYYJC00||'使用多码融合码结算成功'||'",'
              ||'"keyword1": "'||VYYJC00||'",'
              ||'"keyword2": "'||VQHSJ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用，请到'||c_jyjc.BMMC00||'做相关检查！"'
              ||'}' ;
       insert into PT_XXTSB0(ID0000,MBBH00,KMH000,BRZJBH,XXNR00)
       values(SQ_PT_XXTSB0_ID0000.nextval,VMBBH00,VJZKH00,VSFZH00,VXXNR00);
       end loop;
       for c_qy in(
          select distinct YFBMBH,(select NVL(FYCKBH,0) from YF_CFDL00 where CFLSH0=b.CFLSH0) FYCKBH, (select NVL(FYDLXH,0) from YF_CFDL00 where CFLSH0=b.CFLSH0) FYDLXH,
          (select QYRQ00 from YF_CFDL00 H,YF_JYXXB0 K  where H.CFLSH0=K.CFLSH0 AND H.CFLSH0=b.CFLSH0) qyrq00,
           (select substr(qysj00,1,5) QYSJ00 from YF_CFDL00 H,YF_JYXXB0 K  where H.CFLSH0=K.CFLSH0 AND H.CFLSH0=b.CFLSH0) QYSJ00
              from YF_MZCF00 b,SF_BRFY00 c where c.JZDH00=ad_DATAID and b.DJH000=c.DJH000
              AND B.CFZJE0>0 AND  NOT EXISTS (SELECT 1 FROM YF_MZCF00 H WHERE H.BCCFH0=B.CFLSH0)
       )
       loop
         VMBBH00:='TM000002932';
         select BMMC00 into VYFMC00 from BM_BMBM00 where BMBH00=c_qy.YFBMBH;
         begin
             select CKMC00 into VFYCKMC from BM_CKBM00 where CKBH00=c_qy.FYCKBH;
       EXCEPTION
         WHEN OTHERS THEN
            VFYCKMC:='窗口';
          end;
         if c_qy.FYCKBH='999999' then
         VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VQHSJ00||',在'||VYYJC00||'使用多码融合码结算成功'||'",'
              ||'"keyword1": "'||VYYJC00||'",'
              ||'"keyword2": "'||VQHSJ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用，请到'||VYFMC00||VFYCKMC||'取药，取药号为'||c_qy.FYDLXH||'号,取药时间为'||c_qy.qyrq00||' '||c_qy.QYSJ00||'"'
              ||'}' ;
         ELSE
         VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VQHSJ00||',在'||VYYJC00||'使用多码融合码结算成功'||'",'
              ||'"keyword1": "'||VYYJC00||'",'
              ||'"keyword2": "'||VQHSJ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用，请到'||VYFMC00||VFYCKMC||'取药，取药号为'||c_qy.FYDLXH||'号"'
              ||'}' ;
         END IF;
         insert into PT_XXTSB0(ID0000,MBBH00,KMH000,BRZJBH,XXNR00)
         values(SQ_PT_XXTSB0_ID0000.nextval,VMBBH00,VJZKH00,VSFZH00,VXXNR00);
       end loop;

    elsif  LS_XXLX00='5' then  --取药队列

       VMBBH00:='TM000002933';
       select BMMC00 into VYFMC00 from BM_BMBM00 where BMBH00=ad_YFBMBH;
       select CKMC00 into VFYCKMC from BM_CKBM00 where CKBH00=ad_FYCKBH;
       select FYDLXH,BRXM00,PHRQ00,GHID00,CFLSH0 into VDLXH00,VBRXM00,VQHRQ00,VGHID00,VCFLSH0 from YF_CFDL00 where BRID00=ad_BRID00 and DLXH00=ad_DATAID and rownum=1;
       select JZRQ00||' '||JZSJ00  into  VQHSJ00 from YF_MZCF00 where  CFLSH0=VCFLSH0;
       select YYJC00 into VYYJC00 from XT_YYXX00 where rownum=1;
       VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VQHSJ00||',在'||VYYJC00||'使用多码融合码结算成功'||'",'
              ||'"keyword1": "'||TRIM(VYYJC00)||'",'
              ||'"keyword2": "'||VQHRQ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用，请到'||VYFMC00||VFYCKMC||'取药，取药号为'||VDLXH00||'号"'
              ||'}' ;
       insert into PT_XXTSB0(ID0000,MBBH00,KMH000,BRZJBH,XXNR00)
       values(SQ_PT_XXTSB0_ID0000.nextval,VMBBH00,VJZKH00,VSFZH00,VXXNR00);

    elsif  LS_XXLX00='6' then  --发药完成

       VMBBH00:='TM000002933';
       select FYDLXH,BRXM00,FYRQ00,GHID00,CFLSH0 into VDLXH00,VBRXM00,VQHRQ00,VGHID00,VCFLSH0 from YF_CFDL00 where BRID00=ad_BRID00 and DLXH00=ad_DATAID and rownum=1;
       select FYRQ00||' '||FYSJ00  into  VQHSJ00 from YF_MZCF00 where CFLSH0=VCFLSH0;
       select YYJC00 into VYYJC00 from XT_YYXX00 where rownum=1;
       VQHRQ00:=to_char(sysdate, 'YYYYMMDD');
       VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VQHSJ00||',在'||VYYJC00||'使用多码融合码取药成功,处方号为'||VCFLSH0||'",'
              ||'"keyword1": "'||TRIM(VYYJC00)||'",'
              ||'"keyword2": "'||VQHSJ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用!"'
              ||'}' ;
       insert into PT_XXTSB0(ID0000,MBBH00,KMH000,BRZJBH,XXNR00)
       values(SQ_PT_XXTSB0_ID0000.nextval,VMBBH00,VJZKH00,VSFZH00,VXXNR00);

       elsif substr(LS_XXLX00,1,1)='J' then --增加检验执行扣费和出报告单两个场景的内容写入发送表 by 陈浩南
       VMBBH00:='TM000002932';
       select BRXM00,SQRQ00||' '||SQSJ00,BBSDRQ||' '||BBSDSJ,CBGRQ0||' '||CBGSJ0,JGZT00  into VBRXM00,VSQRQ00,VQSSJ00,VBGSJ00,VJGZT00 from VW_JY_YW0000  where JYYWID=substr(LS_XXLX00,2,12) and rownum=1;

       select YYJC00 into VYYJC00 from XT_YYXX00 where rownum=1;
       if (ad_DATAID='1') then
       VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VSQRQ00||',在'||VYYJC00||'使用多码融合码标本已经签收！ 签收时间",'||VQSSJ00
              ||'"keyword1": "'||VYYJC00||'",'
              ||'"keyword2": "'||VSQRQ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用！"'
              ||'}' ;
       elsif ad_DATAID='2' then
       VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VSQRQ00||',在'||VYYJC00||'使用多码融合码标本已经审核！ 审核时间",'||VBGSJ00
              ||'"keyword1": "'||VYYJC00||'",'
              ||'"keyword2": "'||VSQRQ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用！"'
              ||'}' ;
 /*  elsif substr(LS_XXLX00,1,1)='J' then --增加检验执行扣费和出报告单两个场景的内容写入发送表 by 陈浩南
       VMBBH00:='TM000002932';
       select BRXM00,SQRQ00,JGZT00  into VBRXM00,VSQRQ00,VJGZT00 from VW_JY_YW0000  where TXM000=substr(LS_XXLX00,2,12) and rownum=1;
       select YYJC00 into VYYJC00 from XT_YYXX00 where rownum=1;
       if (VJGZT00='2') or (VJGZT00='1')  then
       VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VSQRQ00||',在'||VYYJC00||'使用多码融合码标本已经签收！'||'",'
              ||'"keyword1": "'||TRIM(VYYJC00)||'",'
              ||'"keyword2": "'||VSQRQ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用！"'
              ||'}' ;
       elsif VJGZT00='6' then
       VXXNR00:='{'
              ||'"first": "尊敬的用户'||VBRXM00||'您在'||VSQRQ00||',在'||VYYJC00||'使用多码融合码标本已经审核！'||'",'
              ||'"keyword1": "'||TRIM(VYYJC00)||'",'
              ||'"keyword2": "'||VSQRQ00||'",'
              ||'"keyword3": "'||VBRXM00||'",'
              ||'"keyword4": "'||VJZKH00||'",'
              ||'"remark": "感谢您的使用！"'
              ||'}' ;
       END IF;*/

              END IF;

       insert into PT_XXTSB0(ID0000,MBBH00,KMH000,BRZJBH,XXNR00)
       values(SQ_PT_XXTSB0_ID0000.nextval,VMBBH00,VJZKH00,VSFZH00,VXXNR00);

    end if;
  end if;
end;