CREATE OR REPLACE FUNCTION SF_PY_PDJHJK_JKSTR0_XWJSON
(
  PFYCKBH number,                  --发药窗口编号
  PCZYXKH varchar2,                --操作员胸卡号
  PCZYXM0 varchar2,                --操作员姓名
  PIP0000 varchar2,                --操作员电脑IP
  PKSBH00 varchar2,                --科室编号
  PKSMC00 varchar2,                --科室名称
  PKSPHRQ varchar2,                --排号开始日期
  PJSPHRQ varchar2                 --排号结束日期
)
-- Person      Date         Comments
-- linshu      2022.01.19   create by  星网排队叫号接口json报文; for YF9-20220110-002
return clob is
  LS_JSON00  clob;
  LS_DOCTOR  clob;
  LS_DDRY00  clob;
  LS_DDLIST  clob;
  LS_JHRY00  CLOB;
  LS_JHLIST  CLOB;
  LS_DDRS00  number(5);
  LS_SID000  varchar2(20);
begin
  select count(1) into LS_DDRS00 from YF_CFDL00 A where  A.ZT0000='1' and A.THBZ00<>'1' and A.FYCKBH = PFYCKBH and  A.PHRQ00 between PKSPHRQ and PJSPHRQ ;
  --1:待配药 2:待发药 3:已发药

  --操作员信息
  LS_DOCTOR := '"callerip": "'||PIP0000||'",'
             ||'"doctorname": "'||PCZYXM0||'",'
             ||'"doctorno": "'||PCZYXKH||'",'
             ||'"departmentname": "'||PKSMC00||'",'
             ||'"departmentno": "'||PKSBH00||'",'
             ||'"category": "'||'yf'||'",'
             ||'"roomname": "'||PFYCKBH||'",'
             ||'"waitcount": "'||to_char(LS_DDRS00)||'"';
             --||'"remaincount": "'||'0'||'"';
  LS_DOCTOR := '"doctor": { '||LS_DOCTOR||' }';
  --叫号人员
  for cur_JHLIST in ( select * from (select A.FYDLXH,A.BRXM00,C.ICKH00,A.GHH000,B.GHRQ00||' '||B.GHSJ00 GHRQSJ from YF_CFDL00 A,SF_BRXXB0 B,IC_YHXX00 C
                       where A.GHID00=B.GHID00 and B.BRID00 = C.BRID00  and  A.PHRQ00 between PKSPHRQ and PJSPHRQ
                         and A.ZT0000='2' and A.THBZ00<>'1' and A.FYCKBH = PFYCKBH and c.ZT0000 = '1'  order by a.pysj00 desc  ) where rownum =1
                     ) loop
    LS_JHRY00 :=  '"ticketno": "'||to_char(cur_JHLIST.FYDLXH)||'",'
                ||'"name": "'||cur_JHLIST.BRXM00||'",'
                ||'"cardno": "'||cur_JHLIST.ICKH00||'",'
                ||'"guahaoid": "'||cur_JHLIST.GHH000||'",'
                ||'"patienttype": "'||' '||'",'
                ||'"regtime": "'||cur_JHLIST.GHRQSJ||'"';
    LS_JHRY00 := '{'||LS_JHRY00||'}';
    if LS_JHLIST is null then
      LS_JHLIST := LS_JHRY00;
    else
      LS_JHLIST := LS_JHLIST||','||LS_JHRY00;
    end if;
  end loop;
  LS_JHLIST := '"doingpatient":'||LS_JHLIST||'';

  --等待人员
  for cur_DDLIST in ( select A.FYDLXH,A.BRXM00,C.ICKH00,A.GHH000,B.GHRQ00||' '||B.GHSJ00 GHRQSJ from YF_CFDL00 A,SF_BRXXB0 B,IC_YHXX00 C
                       where A.GHID00=B.GHID00 and B.BRID00 = C.BRID00  and  A.PHRQ00 between PKSPHRQ and PJSPHRQ
                         and A.ZT0000='1' and A.THBZ00<>'1' and A.FYCKBH = PFYCKBH and c.ZT0000 = '1' order by a.fydlxh
                     ) loop
    LS_DDRY00 :=  '"ticketno": "'||to_char(cur_DDLIST.FYDLXH)||'",'
                ||'"name": "'||cur_DDLIST.BRXM00||'",'
                ||'"cardno": "'||cur_DDLIST.ICKH00||'",'
                ||'"guahaoid": "'||cur_DDLIST.GHH000||'",'
                ||'"patienttype": "'||' '||'",'
                ||'"regtime": "'||cur_DDLIST.GHRQSJ||'"';
    LS_DDRY00 := '{'||LS_DDRY00||'}';
    if LS_DDLIST is null then
      LS_DDLIST := LS_DDRY00;
    else
      LS_DDLIST := LS_DDLIST||','||LS_DDRY00;
    end if;
  end loop;
 LS_DDLIST := '"waitpatientlist": [ '||LS_DDLIST||']';



  --时间戳
  select to_char((sysdate - to_date('2022-1-19', 'YYYY-MM-DD HH24')) * 86400000 + to_number(to_char(SYSTIMESTAMP(3), 'FF'))) into LS_SID000 FROM DUAL;

  LS_JSON00 := '{"sid": "'||to_char(LS_SID000)||'","plugin": "fjlnyy","type": "his",'||LS_DOCTOR||','||LS_JHLIST||','||LS_DDLIST||'}';
  return LS_JSON00;
end;