/*
01.选择医院 参数名称:[strYYID00s] 0:全部 默认第一个,可多选
02.统计日期 KSRQ00 和 JSRQ00 必填不可为空 格式:YYYYMMDD 例子:20250630
03.出库类型 参数名称:[strCKLX00s] 值取BM_YFCKLX.CKLXBH,可多选  (与[按照记账日期统计]互斥)
04.药品状态 参数名称:[YPZT00] 0:全部 1:停用 2:正常
05.特殊属性 参数名称:[YPTSSX] 国谈和集采等标记 BM_TYZD00.ZDMC00 = '药品属性' 对应药品特殊属性表[BM_YDTSSX]
06.药品类别 参数名称:[strLBBH00s] 0西药 1成药 2中药 3器械 9材料  ,可多选
07.剂型编号 参数名称:[JXBH00]
08.药理编号 参数名称:[YLBH00]
09.是否显示全部编码 参数名称:[isShowAllCode] 0:否 1:是  union同等条件的BM_YD0000表
10.按照药品编码汇总 参数名称:[groupByYPBM00] 0:否 1:是  汇总是group中不包含[YYQC00和BMMC00]字段
11.按照记账日期统计 参数名称:[groupByJZRQ00] 0:否 1:是  [YF_YPMXZ0]改使用视图[VW_YK_JZRQTJ_HIS13]  (与出院日期和出库类型互斥) 
12.按照出院日期统计 参数名称:[groupByCYRQ00] 0:否 1:是  (与[按照记账日期统计]互斥)
*/
select gg.* ,
decode(gg.LBBH00_01,'0','西药','1','成药','2','中药','其他') LBMC00,
(SELECT SCCJZW FROM BM_YPSCCJ  WHERE SCCJBH=gg.SCCJBH_01) SCCJZW,
(SELECT GHDWMC FROM BM_YPGHDW where GHDWNM=gg.GHDWNM_01) GHDWMC,
(SELECT JXMC00 FROM BM_YPJX00 WHERE LBBH00=gg.LBBH00_01 AND JXBH00=gg.JXBH00_01) JXMC00,
(select MC0000 from BM_TYZD00 where ZDMC00='药典维护-药品性质' and BH0000=gg.YPXZ00_01 and rownum=1) YPXZMC,
(SELECT YLMC00 FROM BM_YPYL00 WHERE LBBH00=gg.LBBH00_01 AND YLBH00=gg.YLBH00_01) YLMC00 
from(
  select 
  B.LBBH00 as LBBH00_01,B.JXBH00 as JXBH00_01,B.YLBH00 as YLBH00_01,
  <#if groupByYPBM00?default("")?trim == "1" >
    '' as YYQC00,'' as YFMC00,
  <#else>
    D.YYQC00,C.BMMC00 YFMC00,
  </#if>
  B.YPNM00,B.YPBM00,B.YPMC00,B.YPTYM0,B.YPGG00,
  B.SCCJBH as SCCJBH_01,B.GHDWNM as GHDWNM_01,B.YPMLBZ,B.KCDW00,
  A.FCDJ00,
  A.GJDJ00 GJJ000,
  ROUND(SUM(A.FCSL00),4) FCSL00,
  ROUND(SUM(A.FCJE00),4) LSJE00,
  ROUND(SUM(A.GJFCJE),4) GJJE00,
  B.KSSJB0,B.YPPZBH,B.YPXZ00 as YPXZ00_01
  --(case when A.FCSL00 = 0 then A.FCDJ00 else ROUND(A.FCJE00/A.FCSL00,4) end )as FCDJ09
  from 
  --按照记账日期统计
  <#if groupByJZRQ00?default("")?trim == "1" >
    VW_YK_JZRQTJ_HIS13 A ,
  <#else>
    YF_YPMXZ0 A ,
  </#if>
  BM_YD0000 B , BM_BMBM00 C , XT_YYXX00 D 
  where 1=1
  and A.YPNM00 = B.YPNM00
  and A.YFBMBH = C.BMBH00
  and C.YYID00 = D.YYID00
  <#if groupByJZRQ00?default("")?trim == "0" >
    and A.JZXZ00 = 'C'--记账性质:''R'',入库,''C'',出库
    and A.RCLXBH not in ('04','06','07')

    --出库类型
    <#if strCKLX00s?default("")?trim?length gt 0 && strCKLX00s?default("")?trim != "0" >
      and A.RCLXBH in (${strCKLX00s?split(",")?map(CKLX -> "'" + CKLX?trim + "'")?join(", ")})
    </#if>

  </#if>
  and A.JZRQ00 >= to_char(sysdate-730,'YYYYMMDD') --记账日期
  
  --剂型编号
  <#if JXBH00?default("")?trim?length gt 0 >
    and B.JXBH00 = '${JXBH00?trim}'
  </#if>

  --药理编号
  <#if YLBH00?default("")?trim?length gt 0 >
    and B.YLBH00 = '${YLBH00?trim}'
  </#if>

  --医院编码 
  <#if strYYID00s?default("")?trim?length gt 0 && strYYID00s?default("")?trim != "0" >
    and C.YYID00 in (${strYYID00s?split(",")?map(YYID -> "'" + YYID?trim + "'")?join(", ")})
  </#if>
  --统计日期
  <#if KSRQ00?default("")?trim?length gt 0 && JSRQ00?default("")?trim?length gt 0 >
    <#if groupByCYRQ00?default("")?trim == "1" >
      and exists(select 1 from ZY_BRXXB0 Z,YF_YZYPSQ Y where Z.ZYID00=Y.ZYID00 and Y.CKDH00 = A.DJH000 and Z.SJCYRQ between '${KSRQ00?trim}' and '${JSRQ00?trim}')
    <#else>
      and A.JZRQ00 between '${KSRQ00?trim}' and '${JSRQ00?trim}'
    </#if>
  </#if>

  --药品状态
  <#if YPZT00?default("")?trim?length gt 0 && YPZT00?default("")?trim != "0" >
    <#if YPZT00?default("")?trim == "1" >
      and B.TZRQ00 is not null
    <#elseif YPZT00?default("")?trim == "2" >
      and B.TZRQ00 is null
    </#if>
  </#if>
  --特殊属性
  <#if YPTSSX?default("")?trim?length gt 0 && YPTSSX?default("")?trim != "0" >
    and exists(select 1 from BM_YDTSSX SX where SX.YPNM00 = B.YPNM00 and SX.SXBH00 = '${YPTSSX?trim}')
  </#if>
  --药品类别
  <#if strLBBH00s?default("")?trim?length gt 0 >
    and B.LBBH00+0 in (${strLBBH00s?split(",")?map(LBBH -> "'" + LBBH?trim + "'")?join(", ")})
  </#if>
  group by B.LBBH00,B.JXBH00,B.YLBH00,
  <#if groupByYPBM00?default("")?trim == "1" >
    '','',
  <#else>
    D.YYQC00,C.BMMC00,
  </#if>
  B.YPNM00,B.YPBM00,B.YPMC00,B.YPTYM0,B.YPGG00,
  B.SCCJBH,B.GHDWNM,B.YPMLBZ,B.KCDW00,
  A.FCDJ00 ,
  A.GJDJ00 ,
  B.KSSJB0,B.YPPZBH,B.YPXZ00--,(case when A.FCSL00 = 0 then A.FCDJ00 else ROUND(A.FCJE00/A.FCSL00,4) end )


  <#if isShowAllCode?default("")?trim == "1" >
      union all
      select 
      YD.LBBH00 , YD.JXBH00    , YD.YLBH00    , '' as YYQC00  , '' as BMMC00 ,
      YD.YPNM00 , YD.YPBM00    , YD.YPMC00    , YD.YPTYM0     , YD.YPGG00,
      YD.SCCJBH , YD.GHDWNM    , YD.YPMLBZ    , YD.KCDW00     , YD.LSJ000,
      YD.GJJ000 , 0 as FCSL00  , 0 as LSJE00  , 0 as GJJE00   , YD.KSSJB0,
      YD.YPPZBH , YD.YPXZ00    , YD.LSJ000 
      --,substr(SF_YK_GetLJYPFL(YD.YPNM00),1,120) LJYPFL,substr(SF_YK_GetYBYLMC(substr(SF_YK_GetYBYLID(YD.YPNM00),1,100)),1,120) YBYLMC 
      from BM_YD0000 YD
      where 1=1
      and YD.YPNM00 in (
          select YPNM00 from BM_YD0000 where TZRQ00 is null and LBBH00+0 in('0') 
          minus 
          select distinct A.YPNM00
          from 
          --按照记账日期统计
          <#if groupByJZRQ00?default("")?trim == "1" >
              VW_YK_JZRQTJ_HIS13 A ,
          <#else>
              YF_YPMXZ0 A ,
          </#if>
          BM_YD0000 B , BM_BMBM00 C , XT_YYXX00 D 
          where 1=1
          and A.YPNM00 = B.YPNM00
          and A.YFBMBH = C.BMBH00
          and C.YYID00 = D.YYID00

          and A.JZRQ00 >= to_char(sysdate-730,'YYYYMMDD') --记账日期
          <#if groupByJZRQ00?default("")?trim == "0" >
            and A.JZXZ00 = 'C'--记账性质:''R'',入库,''C'',出库
            and A.RCLXBH not in ('04','06','07')

            --出库类型
            <#if strCKLX00s?default("")?trim?length gt 0 && strCKLX00s?default("")?trim != "0" >
              and A.RCLXBH in (${strCKLX00s?split(",")?map(CKLX -> "'" + CKLX?trim + "'")?join(", ")})
            </#if>

          </#if>
          --剂型编号
          <#if JXBH00?default("")?trim?length gt 0 >
          and B.JXBH00 = '${JXBH00?trim}'
          </#if>

          --药理编号
          <#if YLBH00?default("")?trim?length gt 0 >
          and B.YLBH00 = '${YLBH00?trim}'
          </#if>

          --医院编码 
          <#if strYYID00s?default("")?trim?length gt 0 && strYYID00s?default("")?trim != "0" >
            and C.YYID00 in (${strYYID00s?split(",")?map(YYID -> "'" + YYID?trim + "'")?join(", ")})
          </#if>
          --统计日期
          <#if KSRQ00?default("")?trim?length gt 0 && JSRQ00?default("")?trim?length gt 0 >
          <#if groupByCYRQ00?default("")?trim == "1" >
              and exists(select 1 from ZY_BRXXB0 Z,YF_YZYPSQ Y where Z.ZYID00=Y.ZYID00 and Y.CKDH00 = A.DJH000 and Z.SJCYRQ between '${KSRQ00?trim}' and '${JSRQ00?trim}')
          <#else>
              and A.JZRQ00 between '${KSRQ00?trim}' and '${JSRQ00?trim}'
          </#if>
          </#if>


          --药品状态
          <#if YPZT00?default("")?trim?length gt 0 && YPZT00?default("")?trim != "0" >
            <#if YPZT00?default("")?trim == "1" >
                and B.TZRQ00 is not null
            <#elseif YPZT00?default("")?trim == "2" >
                and B.TZRQ00 is null
            </#if>
          </#if>
          --特殊属性
          <#if YPTSSX?default("")?trim?length gt 0 && YPTSSX?default("")?trim != "0" >
          and exists(select 1 from BM_YDTSSX SX where SX.YPNM00 = B.YPNM00 and SX.SXBH00 = '${YPTSSX?trim}')
          </#if>
          --药品类别
          <#if strLBBH00s?default("")?trim?length gt 0 >
          and B.LBBH00+0 in (${strLBBH00s?split(",")?map(LBBH -> "'" + LBBH?trim + "'")?join(", ")})
          </#if>
      )


  </#if>

)gg
where 1=1
order by gg.YPBM00