CREATE OR REPLACE VIEW vw_bq_jcyysq (
   brid00,
   zyghid,
   yjksbh,
   yjdjh0,
   zyhghh,
   brxm00,
   yexm00,
   brxb00,
   csrq00,
   brnl00,
   zlxmid,
   zlxmjc,
   zlxmjc1,
   kdysxm,
   bmmc00,
   kdksbh,
   kdrq00,
   kdsj00,
   bwmc00,
   jzbz00,
   xmzt00,
   sfdjh0,
   cxdjh0,
   kdbq00,
   sqid00,
   yybz00,
   yyrq00,
   yysd00,
   xsyysd,
   jcsqdh,
   jkyyh0,
   hbdjh0,
   jczid0,
   jczmc0,
   yyzt00,
   dyzt00,
   zysx00,
   bdbz00,
   yjksmc,
   psztmc,
   ch0000,
   mzzybz,
   tx0000,
   scdyxm,
   scdysj,
   djrqsj,
   ddrs00,
   sm0000,
   tsyy00,
   yysj00 )
AS
select
  Y.BRID00, --病人id
  Y.ZYGHID,
  Y.YJKSBH, --医技科室编号
  Y.YJDJH0, --医技单据号
  Y.ZYHGHH, --住院挂号号
  Y.BRXM00, --病人姓名
  Y.YEXM00, --婴儿姓名
  Y.XB0000, --性别
  Y.CSRQ00, --出生日期
  SF_BQ_CSRQTONL(Y.CSRQ00) BRNL00, --病人年龄
  Y.ZLXMID, --诊疗项目id
  substr(SF_YJ_HBDJHMC(Y.YJDJH0),1,250) ZLXMJC , --诊疗项目简称
  Y.ZLXMJC ZLXMJC1,
  Y.KDYSXM, --开单医生姓名
  B.BMMC00, --开单科室名称
  Y.KDKSBH, --开单科室编号
  Y.KDRQ00, --开单日期
  Y.KDSJ00,
  Y.BWMC00, --部位名称
  DECODE(Y.JZBZ00, NULL, '普通', 'Y', '急诊', 'N', '普通', '普通') JZBZ00,
  DECODE(Y.XMZT00,'0','已申请','1','已计价','2','已收费','3','已执行','4','已报告','5','疑退','6','已作废','7','已退费') XMZT00,
  Y.SFDJH0,
  Y.CXDJH0,
  Y.KDBQ00,
  nvl(Y.SQID00,0) SQID00,
  decode(nvl(Y.YYID00,0),0,'','已预约') YYBZ00 ,  --预约标志
  C.YYRQ00,
  C.YYSD00, 
  DECODE((SELECT F.JCZID0 FROM BM_ZLZD00 F WHERE F.ZLXMID=Y.ZLXMID),'20',yysd00||'来等候',
  '21',C.YYSJ00||'提早30分钟到',
  '22',substr(yysd00,1,5)||'来等候','23',C.YYSJ00||'提早30分钟到','24',C.YYSJ00||'来等候',
  '25',C.YYSJ00||'来等候',
  decode((select a.KSPBSJ from YJ_KSPBB0 a,yj_kspbhb b where a.kspbid=b.kspbid and c.pbhbid=b.pbhbid
  and rownum=1),'上午',
  least(to_char(substr(yysd00,1,5)),to_char('10:00')),substr(yysd00,1,5))||'来等候') xsyysd,  --显示时段
  C.JCSQDH,
  C.JKYYH0,
  nvl(Y.HBDJH0,Y.YJDJH0) HBDJH0,
  (select JCZID0 from BM_ZLZD00 where ZLXMID=Y.ZLXMID) JCZID0,
  (select F.YYXMMC from BM_ZLZD00 E,YJ_YYZLZD F where E.ZLXMID=Y.ZLXMID and E.JCZID0=F.XMWHID and XMWHID>0) JCZMC0,
  nvl(c.YYZT00,'-1') YYZT00,
  decode(nvl(C.DYCS00,0),0,'未打印','已打印') DYZT00,  --打印状态
  --(SELECT E.JCZYSX FROM YJ_YYZLZD E,BM_ZLZD00 F WHERE E.XMWHID=F.JCZID0 AND F.ZLXMID=Y.ZLXMID) ZYSX00,
  --(select f.JCQZYSX from VW_BQ_JCQZYSX f where BRID00=Y.BRID00 and  ((nvl(HBDJH0,0)=0 and YJDJH0=Y.YJDJH0) or  (nvl(HBDJH0,0)>0  and HBDJH0=Y.HBDJH0)) and  nvl(JCQZYSX,' ')<>' ' and rownum=1) ZYSX00,
  SF_YJ_GetJCZYSX(Y.YJDJH0) ZYSX00,
  decode(nvl(C.DYCS00,0),0,'','补打')  BDBZ00,
  (select BMMC00 from BM_BMBM00 where BMBH00=Y.YJKSBH) YJKSMC,
  decode(BBZT00,'1','收到影像','2','初步报告','3','确认报告','0','申请','') PSZTMC,
  Y.CH0000,
  DECODE(Y.MZZYBZ,0,'门诊',1,'住院',Y.MZZYBZ) mzzybz,
  decode(y.yjksbh,'531','延时30分钟请重新预约,不便之处,请予谅解!',null),
  (select ZWXM00 from BM_YGBM00 D,YJ_YYDYJL E where E.YJDJH0=Y.YJDJH0 and YGBH00=E.DYRBH0 and E.DYCS00=1 and E.YYID00=C.YYID00) DYRXM0,
  (select to_char(to_date(DYRQ00||DYSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') from YJ_YYDYJL where YJDJH0=Y.YJDJH0 and DYCS00=1 and YYID00=C.YYID00) DYRQSJ,
  to_char(to_date(C.DJRQ00||C.DJSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') DJRQSJ,
  (select count(*) from YJ_YYBRB0 where YYRQ00=C.YYRQ00 and PBHBID=C.PBHBID and YYH000<C.YYH000 and YYZT00='0') DDRS00,
  Y.SM0000,
  --'请到检查科室预约!' TSYY00
  ' ' TSYY00,
  (select e.yysj00 from YJ_YYBRB0 e where c.yyid00 =e.yyid00 and e.yjksbh ='502') YYSJ00
from YJ_YW0000 Y, BM_BMBM00 B,YJ_YYBRB0 C
  where  Y.YJKSBH<>0 AND Y.MZZYBZ='1'  and Y.BRID00=C.BRID00 
    and Y.KDKSBH=B.BMBH00(+)  and Y.YJDJH0=C.YJDJH0 and Y.YYID00=C.YYID00 
    and (nvl(HBDJH0,0)=0 or HBDJH0=Y.YJDJH0)
--以下语句易联众陈总增加，郑医生要求过滤有时间段，有预约，医技科室为B超的病人
    and ( not exists (select 1   from YJ_YYBRB0 e where c.yyid00 =e.yyid00 and e.yjksbh ='502' and yysj00 is not null and e.JKYYH0 is not null) )
    and ( not exists (select 1   from YJ_YYBRB0 e where c.yyid00 =e.yyid00 and e.yjksbh ='499' and yysj00 is not null and e.JKYYH0 is not null) )
union all
select
  Y.BRID00, --病人id
  Y.ZYGHID,
  Y.YJKSBH, --医技科室编号
  Y.YJDJH0, --医技单据号
  Y.ZYHGHH, --住院挂号号
  Y.BRXM00, --病人姓名
  Y.YEXM00, --婴儿姓名
  Y.XB0000, --性别
  Y.CSRQ00, --出生日期
  SF_BQ_CSRQTONL(Y.CSRQ00) BRNL00, --病人年龄
  Y.ZLXMID, --诊疗项目id
  substr(SF_YJ_HBDJHMC(Y.YJDJH0),1,250) ZLXMJC , --诊疗项目简称
  Y.ZLXMJC ZLXMJC1,
  Y.KDYSXM, --开单医生姓名
  (select BMMC00 from BM_BMBM00 where BMBH00=Y.KDKSBH) BMMC00, --开单科室名称
  Y.KDKSBH, --开单科室编号
  Y.KDRQ00, --开单日期
  Y.KDSJ00,
  Y.BWMC00, --部位名称
  DECODE(Y.JZBZ00, NULL, '普通', 'Y', '急诊', 'N', '普通', '普通') JZBZ00,
  DECODE(Y.XMZT00,'0','已申请','1','已计价','2','已收费','3','已执行','4','已报告','5','疑退','6','已作废','7','已退费') XMZT00,
  Y.SFDJH0,
  Y.CXDJH0,
  Y.KDBQ00,
  nvl(Y.SQID00,0) SQID00,
  decode(nvl(Y.YYID00,0),0,'','已预约') YYBZ00 ,  --预约标志
  '' YYRQ00,
  '' YYSD00, 
  '' XSYYSD, 
  '' JCSQDH,
  '' JKYYH0,
  nvl(Y.HBDJH0,Y.YJDJH0) HBDJH0,
  (select JCZID0 from BM_ZLZD00 where ZLXMID=Y.ZLXMID) JCZID0,
  (select F.YYXMMC from BM_ZLZD00 E,YJ_YYZLZD F where E.ZLXMID=Y.ZLXMID and E.JCZID0=F.XMWHID and XMWHID>0) JCZMC0,
  decode(nvl(Y.SM0000,' '),' ','-1','3') YYZT00,
  decode(nvl(Y.YJDJDY,0),0,'未打印','已打印')  DYZT00,  --打印状态
  --(SELECT E.JCZYSX FROM YJ_YYZLZD E,BM_ZLZD00 F WHERE E.XMWHID=F.JCZID0 AND F.ZLXMID=Y.ZLXMID) ZYSX00,
  SF_YJ_GetJCZYSX(Y.YJDJH0) ZYSX00,
  ' ' BDBZ00,
  (select BMMC00 from BM_BMBM00 where BMBH00=Y.YJKSBH) YJKSMC,
  decode(BBZT00,'1','收到影像','2','初步报告','3','确认报告','0','申请','') PSZTMC,
  Y.CH0000,
  DECODE(Y.MZZYBZ,0,'门诊',1,'住院',Y.MZZYBZ) mzzybz,
  null tx0000,
  (select ZWXM00 from BM_YGBM00 D,YJ_YYDYJL E where E.YJDJH0=Y.YJDJH0 and YGBH00=E.DYRBH0 and E.DYCS00=1 and rownum=1) DYRXM0,
  (select to_char(to_date(DYRQ00||DYSJ00,'YYYYMMDDHH24:MI:SS'),'YYYY.MM.DD HH24:MI:SS') from YJ_YYDYJL where YJDJH0=Y.YJDJH0 and DYCS00=1 and rownum=1) DYRQSJ,
  ' ' DJRQSJ,
  0 DDRS00,
  Y.SM0000,  
  (case when yjksbh ='503' then  '请到肺功能室预约!' 
  when yjksbh ='505' then '请持本条码至内镜室预约!'else '请到检查科室预约!' end) TSYY00,
  '' YYSJ00
from YJ_YW0000 Y 
  where  Y.YJKSBH<>0 AND Y.MZZYBZ='1' 
      and nvl(Y.YYID00,0)=0 and exists(select 1 from BM_ZLZD00 where ZLXMID=Y.ZLXMID and nvl(JCZID0,0)>0)