-- Start of DDL script for VW_YF_MZZYDY_LS
-- Generated 27-七月-11  2:55:38 pm
-- from fw2-SD_HOSPITAL:1

-- View VW_YF_MZZYDY_LS

CREATE OR REPLACE VIEW vw_yf_mzzydy_ls (
   cflsh0,
   cfid00,
   cfxh00,
   cfmx00,
   btxx00,
   je0000,
   xmyfmc,
   ypmc00 )
AS
select CFLSH0,-1,3,decode(a.CFDGS0,'0','麻、精一','1','精 二','2','儿 科','3','儿科急诊','4','急 诊','5','普',' '),'',0,'',''
 FROM YF_MZCF00 a

union all/*医院名称*/
 SELECT CFLSH0,-2,3,sf_xt_yymc00(YFBMBH),'',0,'','' FROM YF_MZCF00

union all /*处方性质全称*/
 SELECT CFLSH0,-3,3,decode(a.CFDGS0,'0','麻醉和第一类精神药品','1','第二类精神药品',''),'',0,'',''
 FROM YF_MZCF00 a

union all/*病历号 输入日期*/
 select CFLSH0,-4,3,rpad('病历号(门诊):'||(select Trim(c.BRBLH0) from SF_BRXXB0 c
 where a.GHh000=c.GHH000),40,' ')||'        '||substr(a.srrq00,1,4)||'年'||substr(a.srrq00,5,2)||'月'||substr(a.SRRQ00,7,2)||'日',
 '',0,'',''
from YF_MZCF00 a

union all/*姓名 性别 年龄*/
 select a.CFLSH0,-5,3,rpad('姓名:'||Trim(a.BRXM00),25,' ')||rpad('性别:'||Trim(a.XB0000),23,' ')||
       rpad('年龄:'||decode(a.CFDGS0,'2',Trim(SF_YF_CSRQTONYR(a.CSRQ00,a.SRRQ00)),'3',Trim(SF_YF_CSRQTONYR(a.CSRQ00,a.SRRQ00)),
            Trim(SF_YF_CSRQTONL(CSRQ00,SRRQ00))),20,' '),
       '',0,'',''
 from YF_MZCF00 a

union all /*科别  费别*/
 select a.CFLSH0,-6,3,rpad('科别:'||(select trim(b.BMMC00) from bm_bmbm00 b where bmbh00=a.ghks00),24,' ')||
 lpad('费别:'||(select Trim(d.YBMC00) from IC_YBBRLB d where  F.YBLB00=d.YBLB00 AND F.FBBH00=d.FBBH00),16,' '),
 '',0,'',''
 from YF_MZCF00 a,bm_brxxb0 f where a.brid00=f.brid00
union all /* 病人身份证号*/
 select a.CFLSH0,-7,3,rpad('身份证号:'||Trim(b.BRZJBH),30,' '),
 '',0,'',''
 from YF_MZCF00 a,bm_brxxb0 b where a.brid00=b.brid00
union all/*代办人姓名 性别 联系电话*/
 select a.CFLSH0,-8,3,rpad('代办人姓名:'||Trim(b.BRLXXM)||'        '||'性别:'||Trim(b.LXRXB0)||'        '||
 '联系电话'||Trim(b.BRLXDH),60,' '),
 '',0,'',''
 from YF_MZCF00 a,bm_brxxb0 b where a.brid00=b.brid00
union all/*代办人身份证号*/
 select a.CFLSH0,-9,3,rpad('身份证号:'||Trim(b.LXRSFZ),40,' '),
 '',0,'',''
 from YF_MZCF00 a,bm_brxxb0 b where a.brid00=b.brid00

union all /*联系人地址*/
  select a.CFLSH0,-10,3,rpad('地址:'||Trim(b.BRLXDZ),40,' '),
  '',0,'',''
  from YF_MZCF00 a,bm_brxxb0 b where a.brid00=b.brid00

union all /*临床诊断*/
select a.CFLSH0,-11,3,rpad('临床诊断及证型:'||nvl(trim(DECODE(TRIM(SF_YF_GETXYZYZD(GHH000,'0')),'','','(中)'||TRIM(SF_YF_GETXYZYZD(GHH000,'0')))||DECODE(trim(SF_YF_GETXYZYZD(GHH000,'1')),'','','(西)'||TRIM(SF_YF_GETXYZYZD(GHH000,'1')))),'待查'),50,' '),'',0,'',''
from YF_MZCF00 a

union all /*Rp*/
 select a.CFLSH0,-12,3,'  Rp','',0,'',''
 FROM YF_MZCF00 a

union all /*字段标题*/
select a.CFLSH0,-13,3,'药名     用量    用法    药名     用量   用法     药名     用量   用法',
'',0,'',''
FROM YF_MZCF00 a



/*-14 到-18 处方尾*/

union all /*总贴数 整剂用法 代煎数 医师*/
select a.CFLSH0,-14,3,rpad('总剂数:'||to_char(a.zyts00),9,' ')||','||
  DECODE(A.YFBMBH,'171','每日'||to_char(a.ZYMRJS)||'剂'||','||(SELECT trim(YPYFMC) FROM YF_MZCFMX e WHERE e.CFLSH0=a.CFLSH0 AND ROWNUM=1)||',',
  '759','每日'||to_char(a.ZYMRJS)||'剂'||','||(SELECT trim(YPYFMC) FROM YF_MZCFMX e WHERE e.CFLSH0=a.CFLSH0 AND ROWNUM=1)||',',('代煎数:'||to_char(a.zydjs0)||','
||'每日'||to_char(a.ZYMRJS)||'剂'||','||'水煎:'||to_char(a.ZYNJJL)||'ml'||','))
 ||decode(a.ZYMRCS,2,'分(早晚)2次',3,'分(早中晚)3次','分'||to_char(a.ZYMRCS)||'次')||','||
 DECODE((SELECT trim(YPYFMC) FROM YF_MZCFMX e WHERE e.CFLSH0=a.CFLSH0 AND ROWNUM=1),'煎服',
 (SELECT e.YFSMMC FROM BM_YPYFSM e WHERE e.YFSMID=a.ZJZFID AND ROWNUM=1),
 '冲服',(SELECT e.YFSMMC FROM BM_YPYFSM e WHERE e.YFSMID=a.ZJZFID AND ROWNUM=1),
 (SELECT YPYFMC FROM YF_MZCFMX e WHERE e.CFLSH0=a.CFLSH0 AND ROWNUM=1))
 ,'',0,'',''
 from YF_MZCF00 a

union all /*医师 药  费*/
 select a.CFLSH0,-15,3,rpad('医 师:'||Trim(a.YSXM00),37,' ')||
 rpad('药  费:'||Trim(to_char(ROUND(a.CFZJE0,2),'99990.00'))||'元',15,' '),
 '',0,'',''
 from YF_MZCF00 a

union all /*审核、调配*/
select a.CFLSH0,-16,3,rpad('审 核:'||(select Trim(D.ZWXM00) from BM_YGBM00 D where a.FYR000=D.YGBH00),19,' ')
||rpad('调 配:'||(select Trim(D.ZWXM00) from BM_YGBM00 D where a.FYR000=D.YGBH00),18,' '),
 '',0,'',''
 FROM YF_MZCF00 a

union all/*核对、发药、收  费  员*/
SELECT a.CFLSH0,-17,3,rpad('核 对:'||(select Trim(D.ZWXM00) from BM_YGBM00 D where a.FYR000=D.YGBH00),19,' ')
||rpad('发 药:',18,' ')
||rpad('收  费  员:',19,' '),
      '',0,'',''
FROM YF_MZCF00 a

union all /*发药窗口，排队序号*/
select a.CFLSH0,-18,3,'发药窗口：'||(SELECT TRIM(CKMC00) FROM yf_CFDL00 E,BM_CKBM00 F WHERE E.CFLSH0=A.CFLSH0 AND E.fyckbh=F.ckbh00)||' 排队序号：'||((SELECT FYDLXH FROM yf_CFDL00 WHERE CFLSH0=A.CFLSH0)),'',0,'',''
from YF_MZCF00 a

union all /*超期理由*/
select a.CFLSH0,-19,3,RPAD('超期理由:'||SF_YF_GETCQLY00(a.CFLSH0),50,' '),'',0,'',''
from YF_MZCF00 a
/

-- End of DDL script for VW_YF_MZZYDY_LS