--门诊收入
select  'mzfykssr' as ID              ,   --收入ID
       czrq00  as SJ              ,   --时间
       to_char(kdks00)  as KSBH              ,   --科室编码或收费员编码
       bmmc00  as KSName          ,   --科室名称或收费员姓名
       xsxlh0  as Rate_type_code  ,   --项目编码或会计科目
       hsxmmc  as Rate_type_name  ,   --项目名称或科目名称
       hjje00 as JE              ,   --金额
       '2'  as LX              ,   --记录类型      1门诊预缴金收退记录  2门诊科室收入   3住院收费员记录 4住院科室收入
       '0'  as Status          ,   --状态          0默认 1财务已处理
       '' as xmbh,
       '' as xmname,
       '0' as ACCT_No         ,   --结账单号
       ''  as DJID                 --财务生成的报销单号
from (
select  XSXLH0,  HSXMMC,  KDKS00,  ZXKS00, czrq00, bmmc00,sum(HJJE00) as hjje00 from (
SELECT F.XSXLH0,
         F.HSXMMC,
         A.KDKS00,
         A.ZXKS00,
        -- a.czrq00,
         b.bmmc00,
         a.HJJE00,
         a.CZRQ00
    from SF_FYMX00 A, SF_BRFY00 C, BM_BMBM00 B, BM_YYSFXM E, VW_BM_YJHSXM F
   where A.KDKS00 = B.BMBH00
     and A.DJH000 = C.DJH000
     and A.XMBH00 = E.SFXMID
     and E.HSXMID = F.HSXMID
     )
   group by  XSXLH0,  HSXMMC,  KDKS00,  ZXKS00, czrq00, bmmc00 )
   
   ----体检现金支付
   select  'mzfykssr' as ID              ,   --收入ID
       czrq00  as SJ              ,   --时间
       to_char(kdks00)  as KSBH              ,   --科室编码或收费员编码
       bmmc00  as KSName          ,   --科室名称或收费员姓名
       xsxlh0  as Rate_type_code  ,   --项目编码或会计科目
       hsxmmc  as Rate_type_name  ,   --项目名称或科目名称
       hjje00 as JE              ,   --金额
       '2'  as LX              ,   --记录类型      1门诊预缴金收退记录  2门诊科室收入   3住院收费员记录 4住院科室收入
       '0'  as Status          ,   --状态          0默认 1财务已处理
       '' as xmbh,
       '' as xmname,
       '0' as ACCT_No         ,   --结账单号
       ''  as DJID                 --财务生成的报销单号
from (
select  'mzfykssr',a.jfrq00 as czrq00 ,null as kdks00,null as bmmc00 ,null as xsxlh0,null as hsxmmc , sum(a.jfje00) as hjje00,null,null,null,null,null,null
  from sf_brjfb0 a, bm_brxxb0 b
 where a.brid00 = b.brid00
   and b.brblh0 like 'DW%'
   and zffs00 <> 2
   and jfje00 <> 0
   group by a.jfrq00)
   
   -----预交金本期增加
      select  'mzfykssr' as ID              ,   --收入ID
       czrq00  as SJ              ,   --时间
       to_char(kdks00)  as KSBH              ,   --科室编码或收费员编码
       bmmc00  as KSName          ,   --科室名称或收费员姓名
       xsxlh0  as Rate_type_code  ,   --项目编码或会计科目
       hsxmmc  as Rate_type_name  ,   --项目名称或科目名称
       hjje00 as JE              ,   --金额
       '2'  as LX              ,   --记录类型      1门诊预缴金收退记录  2门诊科室收入   3住院收费员记录 4住院科室收入
       '0'  as Status          ,   --状态          0默认 1财务已处理
       '' as xmbh,
       '' as xmname,
       '0' as ACCT_No         ,   --结账单号
       ''  as DJID                 --财务生成的报销单号
from (
   select  'mzfykssr',c.jfrq00 as czrq00 ,null as kdks00,null as bmmc00 ,null as xsxlh0,null as hsxmmc , NVL(sum(C.JFJE00), 0) as hjje00,null,null,null,null,null,null
           from sf_brjfb0 C
          where  jflbid = 1 
		  and c.jfrq00>='20190801'
		  and c.jfrq00<='20190831'
          group by c.jfrq00 )
		  

---银行存款
select zwxm00,
       sum(decode(xmbh00, '02', xmje00, 0)) syjxj0,
       sum(decode(xmbh00, '03', xmje00, 0)) tyjxj0,
       sum(decode(xmbh00, '04', xmje00, 0)) xjjs00,
       sum(decode(xmbh00, '05', xmje00, 0)) yjxj00,
       sum(decode(xmbh00, '09', xmje00, 0)) yhksr0,
       sum(decode(xmbh00, '10', xmje00, 0)) zpsr00,
       sum(decode(xmbh00, '24', xmje00, 0)) wxzf00,
       sum(decode(xmbh00, '25', xmje00, 0)) zfbzf0,
       sum(decode(xmbh00,
                  '05',
                  xmje00,
                  '09',
                  xmje00,
                  '10',
                  xmje00,
                  '24',
                  xmje00,
                  '25',
                  xmje00,
                  0)) yjhjje
  from bm_jkkjmx a, bm_jkb000 b, bm_ygbm00 d
 where a.jkdh00 = b.jkdh00
   and b.sfy000 = d.ygbh00
   and b.jkrq00 between '20190801' and '20190831'
   and mzbz00 = '0'
   and pjbh00 =
       (select min(pjbh00) from bm_jkb000 c where jkdh00 = b.jkdh00)
 group by zwxm00, xkh000

 --门诊收费员缴款汇总表中取数=应缴合计-应缴支票
 --YJHJJE-ZPSR00

 ------记账结算数   门诊日收入报表中取数=记账结算数
    select  A.HSBH00,sum(A.HJJE00),sum(XJJE00),sum(YJJJE0),FBBH00,sum(decode(JMBZ00,'1',JMJE00,0)) JMJE00  from  VM_SF_MZSRHSXM A
                                                  WHERE  CZRQ00 >= '20190801'
                                                  AND  CZRQ00 <= '20190831'
                                                 
                                                  AND  A.FBBH00 not in (1,3)
                                                  and XMLB00='0'  
                                                   and fbbh00='09'    
                                                  GROUP BY  HSBH00,FBBH00; 
	
	-----本期减少
	  select NVL(sum(A.ZFJE00), 0) JE0000
          from sf_brfy00 A
         where A.CZRQ00 >= '20190801'
           and A.CZRQ00 <= '20190831'
           and A.JFLBID = 1

-------门诊日收入报表中取数=体检结算
		      select  A.HSBH00,sum(A.HJJE00),sum(XJJE00),sum(YJJJE0),FBBH00,sum(decode(JMBZ00,'1',JMJE00,0)) JMJE00  from  VM_SF_MZSRHSXM A
                                                  WHERE  CZRQ00 >= '20190801'
                                                  AND  CZRQ00 <= '20190831'
                                                  AND  A.FBBH00 not in (1,3)
                                                  and XMLB00='0'  
                                                 and fbbh00='99'    
                                                  GROUP BY  HSBH00,FBBH00; 

------门诊就诊卡统计表汇总数												  
   select count(*)
                   from ic_ywls00
                  where FSJE00 = 0
                    and YWCZ00 in ('1', 'C', 'D')
                    and YGBH00 = C.YGBH00
                    and CZRQ00 >= '20190801'
                    and CZRQ00 <= '20190831'
                    
-----门诊日收入报表中取数=医保账户+医保统筹
 select nvl(SUM(A.GRZHZF), 0) GRZHZF,
        NVL(SUM(A.TCJJZF), 0) TCJJZF,
        NVL(SUM(A.ZFJE00), 0) ZFJE00,
        nvl(SUM(A.SBGRZH), 0) SBGRZH,
        NVL(sum(A.SBTCJJ), 0) SBTCJJ
   from SF_JZB000 A
  where A.YBDJH0 <> '0'
    and A.JZRQ00 >= '20190801'
    and A.JZRQ00 <= '20190831'
	
---区离休门诊挂账数  门诊日收入报表中取数=离休干部结算+离休普通
select  A.HSBH00,sum(A.HJJE00),sum(XJJE00),sum(YJJJE0),FBBH00,sum(decode(JMBZ00,'1',JMJE00,0)) JMJE00  from  VM_SF_MZSRHSXM A
                                                  WHERE  CZRQ00 >= '20190801'
                                                  AND  CZRQ00 <= '20190831'
                                                  AND  A.FBBH00 not in (1,3)
                                                  and XMLB00='0'  
                                                  and fbbh00 in ('4','6')   
                                                  GROUP BY  HSBH00,FBBH00; 
	
	
   
		  

-----住院收入
select ' 合计' hsxmmc, sum(A.HJJE00) XMJE00
  from ZY_FYMX00 A, ZY_BRFY00 H
 where A.DJH000 = H.DJH000
   and H.CZRQ00 between '20190801' and '20190831'
UNION ALL
SELECT hsxmmc, XMJE00
  FROM (select C.hsxmmc, C.BH0000, sum(A.HJJE00) XMJE00
          from ZY_FYMX00 A, BM_YYSFXM B, bm_yyhsxm C, ZY_BRFY00 H
         where A.XMBH00 = B.SFXMID
           and B.hsxmid = C.hsxmid
           and A.DJH000 = H.DJH000
           and H.CZRQ00 between '20190801' and '20190831'
         group by C.hsxmmc, C.BH0000
         order by C.BH0000)		

-------冲减呆账病人
 select zwxm00,
        sum(decode(xmbh00, '02', xmje00, '20', -1 * xmje00, 0)) pjsr00,
        sum(decode(xmbh00, '29', xmje00, 0)) ybjz00,
        sum(decode(xmbh00, '32', xmje00, 0)) gfjz00,
        sum(decode(xmbh00, '33', xmje00, 0)) cyysk0,
        sum(decode(xmbh00, '17', xmje00, 0)) cyytk0,   -----住院收费员缴款汇总表取数=出院应退款
        sum(decode(xmbh00, '01', xmje00, 0)) yjjsr0,
        sum(decode(xmbh00, '13', xmje00, 0)) yjjhs0,
        sum(decode(xmbh00, '26', xmje00, 0)) yhksr0,
        sum(decode(xmbh00, '54', xmje00, 0)) wxzf00,
        sum(decode(xmbh00, '55', xmje00, 0)) zfbzf0,
        sum(decode(xmbh00,
                   '35',
                   xmje00,
                   '26',
                   xmje00,
                   '25',
                   xmje00,
                   '54',
                   xmje00,
                   '55',
                   xmje00,
                   0)) yjhj00
   from bm_jkkjmx a, bm_jkb000 b, bm_ygbm00 d
  where a.jkdh00 = b.jkdh00
    and b.sfy000 = d.ygbh00
    and b.jkrq00 between '20190801' and '20190831'
    and mzbz00 = '1'
    and pjbh00 =
        (select min(pjbh00) from bm_jkb000 c where jkdh00 = b.jkdh00)
  group by zwxm00, xkh000;		 
		  
--------单病种结算  医院支付
  select sum(CYQFJE) CYQFJE,
         sum(GRZHZF) GRZHZF,
         sum(TCJJZF) TCJJZF,
         sum(SBGRZH) SBGRZH,
         sum(SBTCJJ) SBTCJJ,
         sum(YBXJZF) YBXJZF,
         sum(JZZFJE) JZZFJE,
         sum(YYZFJE) YYZFJE   ------医院支付
    from VW_ZY_JSBBXX b
   where JZRQ00 >= '20190801'
     and JZRQ00 <= '20190831' ;

	
----住院收入汇总表合计数

     select ' 合计' XMMC00, sum(A.HJJE00) XMJE00
       from ZY_FYMX00 A, ZY_BRFY00 H
      where A.DJH000 = H.DJH000
        and H.CZRQ00 between '20190801' and '20190831'	
		
----银行存款  住院收费员缴款汇总表取数=上缴金额  YJHJ00

select zwxm00,
       sum(decode(xmbh00, '02', xmje00, '20', -1 * xmje00, 0)) pjsr00,
       sum(decode(xmbh00, '29', xmje00, 0)) ybjz00,
       sum(decode(xmbh00, '32', xmje00, 0)) gfjz00,
       sum(decode(xmbh00, '33', xmje00, 0)) cyysk0,
       sum(decode(xmbh00, '17', xmje00, 0)) cyytk0,
       sum(decode(xmbh00, '01', xmje00, 0)) yjjsr0,
       sum(decode(xmbh00, '13', xmje00, 0)) yjjhs0,
       sum(decode(xmbh00, '26', xmje00, 0)) yhksr0,
       sum(decode(xmbh00, '54', xmje00, 0)) wxzf00,
       sum(decode(xmbh00, '55', xmje00, 0)) zfbzf0,
       sum(decode(xmbh00,
                  '35',
                  xmje00,
                  '26',
                  xmje00,
                  '25',
                  xmje00,
                  '54',
                  xmje00,
                  '55',
                  xmje00,
                  0)) yjhj00
  from bm_jkkjmx a, bm_jkb000 b, bm_ygbm00 d
 where a.jkdh00 = b.jkdh00
   and b.sfy000 = d.ygbh00
   and b.jkrq00 between '20190801' and '20190831'
   and mzbz00 = '1'
   and pjbh00 =
       (select min(pjbh00) from bm_jkb000 c where jkdh00 = b.jkdh00)
 group by zwxm00, xkh000;
 
 ---记账单位	住院收费员缴款汇总表取数=公费记账  GFJZ00
 select zwxm00,
       sum(decode(xmbh00, '02', xmje00, '20', -1 * xmje00, 0)) pjsr00,
       sum(decode(xmbh00, '29', xmje00, 0)) ybjz00,
       sum(decode(xmbh00, '32', xmje00, 0)) gfjz00,
       sum(decode(xmbh00, '33', xmje00, 0)) cyysk0,
       sum(decode(xmbh00, '17', xmje00, 0)) cyytk0,
       sum(decode(xmbh00, '01', xmje00, 0)) yjjsr0,
       sum(decode(xmbh00, '13', xmje00, 0)) yjjhs0,
       sum(decode(xmbh00, '26', xmje00, 0)) yhksr0,
       sum(decode(xmbh00, '54', xmje00, 0)) wxzf00,
       sum(decode(xmbh00, '55', xmje00, 0)) zfbzf0,
       sum(decode(xmbh00,
                  '35',
                  xmje00,
                  '26',
                  xmje00,
                  '25',
                  xmje00,
                  '54',
                  xmje00,
                  '55',
                  xmje00,
                  0)) yjhj00
  from bm_jkkjmx a, bm_jkb000 b, bm_ygbm00 d
 where a.jkdh00 = b.jkdh00
   and b.sfy000 = d.ygbh00
   and b.jkrq00 between '20190801' and '20190831'
   and mzbz00 = '1'
   and pjbh00 =
       (select min(pjbh00) from bm_jkb000 c where jkdh00 = b.jkdh00)
 group by zwxm00, xkh000;
 
 
 ---------退住院病人预交---住院预交减少数（住院收费员缴款汇总表—预交金收回） YJJHS0
 select zwxm00,
       sum(decode(xmbh00, '02', xmje00, '20', -1 * xmje00, 0)) pjsr00,
       sum(decode(xmbh00, '29', xmje00, 0)) ybjz00,
       sum(decode(xmbh00, '32', xmje00, 0)) gfjz00,
       sum(decode(xmbh00, '33', xmje00, 0)) cyysk0,
       sum(decode(xmbh00, '17', xmje00, 0)) cyytk0,
       sum(decode(xmbh00, '01', xmje00, 0)) yjjsr0,
       sum(decode(xmbh00, '13', xmje00, 0)) yjjhs0,
       sum(decode(xmbh00, '26', xmje00, 0)) yhksr0,
       sum(decode(xmbh00, '54', xmje00, 0)) wxzf00,
       sum(decode(xmbh00, '55', xmje00, 0)) zfbzf0,
       sum(decode(xmbh00,
                  '35',
                  xmje00,
                  '26',
                  xmje00,
                  '25',
                  xmje00,
                  '54',
                  xmje00,
                  '55',
                  xmje00,
                  0)) yjhj00
  from bm_jkkjmx a, bm_jkb000 b, bm_ygbm00 d
 where a.jkdh00 = b.jkdh00
   and b.sfy000 = d.ygbh00
   and b.jkrq00 between '20190801' and '20190831'
   and mzbz00 = '1'
   and pjbh00 =
       (select min(pjbh00) from bm_jkb000 c where jkdh00 = b.jkdh00)
 group by zwxm00, xkh000;
 
 
 -----医保挂账	住院收费员缴款汇总表取数=医保记账  YBJZ00
 select zwxm00,
       sum(decode(xmbh00, '02', xmje00, '20', -1 * xmje00, 0)) pjsr00,
       sum(decode(xmbh00, '29', xmje00, 0)) ybjz00,
       sum(decode(xmbh00, '32', xmje00, 0)) gfjz00,
       sum(decode(xmbh00, '33', xmje00, 0)) cyysk0,
       sum(decode(xmbh00, '17', xmje00, 0)) cyytk0,
       sum(decode(xmbh00, '01', xmje00, 0)) yjjsr0,
       sum(decode(xmbh00, '13', xmje00, 0)) yjjhs0,
       sum(decode(xmbh00, '26', xmje00, 0)) yhksr0,
       sum(decode(xmbh00, '54', xmje00, 0)) wxzf00,
       sum(decode(xmbh00, '55', xmje00, 0)) zfbzf0,
       sum(decode(xmbh00,
                  '35',
                  xmje00,
                  '26',
                  xmje00,
                  '25',
                  xmje00,
                  '54',
                  xmje00,
                  '55',
                  xmje00,
                  0)) yjhj00
  from bm_jkkjmx a, bm_jkb000 b, bm_ygbm00 d
 where a.jkdh00 = b.jkdh00
   and b.sfy000 = d.ygbh00
   and b.jkrq00 between '20190801' and '20190831'
   and mzbz00 = '1'
   and pjbh00 =
       (select min(pjbh00) from bm_jkb000 c where jkdh00 = b.jkdh00)
 group by zwxm00, xkh000;