Mercurial > HIS > BLT-XML
changeset 2:a409f2a3ce0a default tip
Fix:修改文件名为英文名(Hgweb不支持中文路径)
| author | Schmit <liny.jii@nnsui.com> |
|---|---|
| date | Sun, 08 Sep 2024 17:15:31 +0800 |
| parents | be9328b4ea2d |
| children | |
| files | ehis.pool.XML ehis.ͳ.Ӫҵ.Ӫ.XML |
| diffstat | 2 files changed, 359 insertions(+), 359 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/ehis.pool.XML Sun Sep 08 17:15:31 2024 +0800 @@ -0,0 +1,359 @@ +<?xml version="1.0" encoding="UTF-8"?> +<report report-id="ehis.数据统计.营业收入.运营情况汇总" title="运营情况汇总" seq="0" is-detail="true"> +<description>运营情况汇总</description> +<attrs> + <attr key="datasource" value="default"/> + <attr key="leftFixedColCount" value="0"/> + <attr key="rightFixedColCount" value="0"/> + <attr key="showTotalRow" value="false"/> + <attr key="showRowIndex" value="true"/> + <attr key="showColIndex" value="false"/> + <attr key="showColLine" value="true"/> + <attr key="showRowLine" value="true"/> + <attr key="showHeader" value="false"/> + <attr key="showTools" value="false"/> + <attr key="trackMouseOver" value="true"/> + <attr key="defaultColWidth" value="100"/> + <attr key="rowNoColWidth" value="40"/> + <attr key="pageType" value="A4"/> + <attr key="pageDirect" value="V"/> + <attr key="pageFontSize" value="11"/> + <attr key="pageSize" value="297,210"/> + <attr key="pageMargin" value="10,10,10,10"/> + <attr key="pageMaxRowCount" value="50"/> + <attr key="autoSizeCol" value="0" /> +</attrs> + +<params> + <param id="jgxs"> + <sql> + <![CDATA[ + select count(1) col into #t from + ( + select 机构ID value,名称 text from bas_机构 where 机构ID=${user.companyPartyId} + union all + select 机构ID value,名称 text from bas_机构 where isnull(管理机构ID,'')=${user.companyPartyId} + ) a + ; + select case when col>1 then 'false' else 'true' end aaa from #t + ]]> + </sql> + </param> + <param id="mrjg"> + <sql> + <![CDATA[ + select ${user.companyPartyId} id + ]]> + </sql> + </param> +</params> + +<enums> + <enum id="tjjg"> + <sql> + <![CDATA[ + select 机构ID value,名称 text from bas_机构 where 机构ID=${user.companyPartyId} + union all + select 机构ID value,名称 text from bas_机构 where isnull(管理机构ID,'')=${user.companyPartyId} + ]]> + </sql> + </enum> +</enums> + +<header-items> + <header-item id="统计机构" type="enum" enum-id="tjjg" title="统计机构" default="${mrjg}" hidden="${jgxs}" value-col="value" use-like='false' text-col="text" width="250" trigger-query="false"/> + <header-item id="时间" type="datetimerange" title="统计时间" default="今天" width="190" trigger-query="false"/> + <header-item id="查找" type="text" width="150" use-like="false" title="查找" hidden="true" empty-text="请输入查找信息" trigger-query="false"/> +</header-items> + +<select> + <field id="分类" title="分类"><![CDATA[分类]]></field> + <field id="列1" title="列1"><![CDATA[列1]]></field> + <field id="列1值" title="列1值"><![CDATA[列1值]]></field> + <field id="列2" title="列2"><![CDATA[列2]]></field> + <field id="列2值" title="列2值"><![CDATA[列2值]]></field> + <field id="列3" title="列3"> <![CDATA[列3]]></field> + <field id="列3值" title="列3值"> <![CDATA[列3值]]></field> + <field id="列4" title="列4"> <![CDATA[列4]]></field> + <field id="列4值" title="列4值"> <![CDATA[列4值]]></field> +</select> + +<from> + <![CDATA[ + ( + select + '门诊情况' 分类,'挂号人数' 列1,gh.挂号人数 列1值,'挂号金额' 列2,cast(gh.挂号金额 as nvarchar(10)) 列2值, + '首诊人数' 列3,gh.首诊人数 列3值,'复诊人数' 列4,gh.复诊人数 列4值 + from #jg jg + left join #gh gh on jg.机构ID=gh.机构ID + union all + select + '门诊情况' 分类,'门诊人次' 列1,mzjs.门诊人次 列1值,'总金额' 列2,cast(mzjs.总金额 as nvarchar(10)) 列2值, + '医保金额' 列3,mzjs.报销金额 列3值,'现金金额' 列4,mzzf.现金支付 列4值 + from #jg jg + left join #mzjs mzjs on jg.机构ID=mzjs.机构ID + left join #mzzf mzzf on jg.机构ID=mzzf.机构ID + union all + select + '门诊情况' 分类,'处方总数' 列1,mzcf.处方总数 列1值,'药品金额' 列2,cast(mzcf.药品金额 as nvarchar(10)) 列2值, + '诊疗金额' 列3,mzcf.诊疗金额 列3值,'处方均额' 列4,case when mzcf.处方总数<>0 then cast(mzjs.总金额/mzcf.处方总数 as decimal(10,2)) else null end 列4值 + from #jg jg + left join #mzcf mzcf on jg.机构ID=mzcf.机构ID + left join #mzjs mzjs on jg.机构ID=mzjs.机构ID + union all + select + '门诊情况' 分类,'自费人次' 列1,mzjs.自费人次 列1值,'医保人次' 列2,cast(mzjs.医保人次 as nvarchar(10)) 列2值, + '普通门诊数' 列3,mzjs.普通门诊人次 列3值,'特病人次' 列4,mzjs.特病人次 列4值 + from #jg jg + left join #mzjs mzjs on jg.机构ID=mzjs.机构ID + union all + select + '住院情况' 分类,'入院总人数' 列1,ry.入院总人数 列1值,'当前在院总人数' 列2,cast(zy.在院总人数 as nvarchar(10)) 列2值, + '出院总人数' 列3,cy.出院总人数 列3值,'结算总人数' 列4,zyjs.结算总人数 列4值 + from #jg jg + left join #ry ry on jg.机构ID=ry.机构ID + left join #cy cy on jg.机构ID=cy.机构ID + left join #zyjs zyjs on jg.机构ID=zyjs.机构ID + left join #zy zy on jg.机构ID=zy.机构ID + union all + select + '住院情况' 分类,'结算金额' 列1,zyjs.结算金额 列1值,'职工金额' 列2,cast(zyjs.职工金额 as nvarchar(10)) 列2值, + '居民金额' 列3,zyjs.居民金额 列3值,'自费金额' 列4,zyjs.自费金额 列4值 + from #jg jg + left join #zyjs zyjs on jg.机构ID=zyjs.机构ID + union all + select + '住院情况' 分类,'预交收入' 列1,zyyj.预交收入 列1值,'药品收入' 列2,cast(zycf.药品收入 as nvarchar(10)) 列2值, + '诊疗收入' 列3,zycf.诊疗收入 列3值,'医院承担' 列4,zyjs.医院承担 列4值 + from #jg jg + left join #zyjs zyjs on jg.机构ID=zyjs.机构ID + left join #zyyj zyyj on jg.机构ID=zyyj.机构ID + left join #zycf zycf on jg.机构ID=zycf.机构ID + union all + select null,'',null,'工作量情况(医院实际工','作量情况,与结算','数据无关)',null,null,null + union all + select + null,'总费用',t.总金额,'药品金额',cast(t.药品金额 as nvarchar(10)) ,'诊疗金额',t.诊疗金额,null,null + from #gzl t + union all + select null,'',null,'各项收入项目费用情况','(结算数据)','',null,null,null + union all + select + null,a.收入项目,a.金额,b.收入项目,cast(b.金额 as nvarchar(10)) ,c.收入项目,c.金额,d.收入项目,d.金额 + from #a a + left join #b b on a.px+1=b.px + left join #c c on a.px+2=c.px + left join #d d on a.px+3=d.px + union all + select null, '合计' ,sum(t.金额),null,null,null,null,null,null + from #srxm t + ) t + ]]> +</from> + +<with> + <![CDATA[ + + with t as ( + select + gh.登记机构ID 机构ID, + count(distinct gh.档案ID) 挂号人数, + count(distinct case when isnull(jzjl.初诊,'是')='是' then jzjl.档案ID else null end) 首诊人数, + count(distinct case when isnull(jzjl.初诊,'是')='否' then jzjl.档案ID else null end) 复诊人数, + sum(js.金额) 挂号金额 + from reg_挂号 gh + inner join opd_记录 jzjl on gh.挂号ID=jzjl.挂号ID + inner join opc_记录_结算 js on gh.结算ID=js.结算ID + where gh.就诊日期 between ${时间_START} and ${时间_END} and gh.登记机构ID=${统计机构} + group by gh.登记机构ID + ) + select * into #gh from t; + select + js.结算机构ID 机构ID, + count(distinct jl.档案ID+'-'+jl.开单医生ID+'-'+convert(nvarchar(10),jl.登记时间,120)) 门诊人次, + sum(js.金额) 总金额, + sum(js.报销金额) 报销金额, + COUNT(DISTINCT (case when ybjs.结算ID IS NULL THEN jl.档案ID+'-'+jl.开单医生ID+'-'+convert(nvarchar(10),jl.登记时间,120) ELSE NULL END)) 自费人次, + COUNT(DISTINCT (case when ybjs.结算ID IS NOT NULL THEN jl.档案ID+'-'+jl.开单医生ID+'-'+convert(nvarchar(10),jl.登记时间,120) ELSE NULL END)) 医保人次, + count(distinct (case when jzlb.名称 in ('门诊慢特病') then jl.档案ID+'-'+jl.开单医生ID+'-'+convert(nvarchar(10),jl.登记时间,120) else null end)) 特病人次, + count(distinct (case when jzlb.名称 not in ('门诊慢特病') then jl.档案ID+'-'+jl.开单医生ID+'-'+convert(nvarchar(10),jl.登记时间,120) else null end)) 普通门诊人次 + into #mzjs + from opc_记录 jl + inner join opc_记录_结算 js on jl.记录ID=js.记录ID + left join yb_就诊结算记录 ybjs on js.结算ID=ybjs.结算ID + left join bas_就诊类别 jzlb on jl.医疗类别ID=jzlb.医疗类别ID AND jl.就诊类别ID=jzlb.就诊类别ID + where js.结算时间 between ${时间_START} and ${时间_END} and js.结算机构ID=${统计机构} + and jl.处方来源<>'门诊挂号' + group by js.结算机构ID + + + select + js.结算机构ID 机构ID, + sum(case when zffs.名称='现金' then zf.支付金额 else null end) 现金支付, + sum(case when zffs.名称!='现金' then zf.支付金额 else null end) 其他支付 + into #mzzf + from opc_记录_结算 js + inner join opc_记录 jl on js.记录ID=jl.记录ID + inner join opc_记录_结算_支付 zf on js.结算ID=zf.结算ID + inner join bas_支付方式 zffs on zf.支付方式ID=zffs.支付方式ID + where js.结算时间 between ${时间_START} and ${时间_END} and js.结算机构ID=${统计机构} + and jl.处方来源<>'门诊挂号' + group by js.结算机构ID + + + --cf + select + js.结算机构ID 机构ID, + count(distinct fymx.处方ID) 处方总数, + sum(case when xm.类别ID in ('5','6','7','8') then mx.金额 else null end) 药品金额, + sum(case when xm.类别ID not in ('5','6','7','8') then mx.金额 else null end) 诊疗金额 + into #mzcf + from opc_记录_明细 mx + inner join opc_记录_结算 js on mx.结算ID=js.结算ID + inner join bas_收费项目 xm on mx.项目ID=xm.项目ID + left join opd_处方_医嘱_费用 fymx on mx.医嘱明细ID=fymx.明细ID + where js.结算时间 between ${时间_START} and ${时间_END} AND js.结算机构ID=${统计机构} + group by js.结算机构ID + + --住院 + select jl.登记机构ID 机构ID, count(1) 入院总人数 into #ry + from ihm_记录 jl + where jl.入院时间 between ${时间_START} and ${时间_END} AND 登记机构ID=${统计机构} + group by jl.登记机构ID + + select + jl.登记机构ID 机构ID, + count(1) 在院总人数 + into #zy + from ihm_记录 jl + where jl.状态 in ('在床','待分') + group by jl.登记机构ID + + + + select + jl.登记机构ID 机构ID, count(1) 出院总人数 into #cy + from ihm_记录 jl + where jl.出院时间 between ${时间_START} and ${时间_END} + group by jl.登记机构ID + + select js.结算机构ID 机构ID,count(distinct 记录ID) 结算总人数, + sum(js.金额) 结算金额, + sum(case when ybjs.险种类型='310' then js.金额 else null end) 职工金额, + sum(case when ybjs.险种类型='390' then js.金额 else null end) 居民金额, + sum(case when ybjs.结算ID is null then js.金额 else null end) 自费金额, + sum(js.医院承担) 医院承担 + into #zyjs + from ihm_记录_结算 js + left join yb_就诊结算记录 ybjs on js.结算ID=ybjs.结算ID AND js.结算机构ID=${统计机构} + where js.结算时间 between ${时间_START} and ${时间_END} + group by js.结算机构ID + + select + yj.登记机构ID 机构ID, + sum(yj.金额) 预交收入 + into #zyyj + from ihm_记录_预交 yj + where yj.收费时间 between ${时间_START} and ${时间_END} and yj.登记机构ID=${统计机构} + group by yj.登记机构ID + + select js.结算机构ID 机构ID, + sum(case when xm.类别ID in ('5','6','7','8') then case when js.状态='作废' then -mx.金额 else mx.金额 end else null end) 药品收入, + sum(case when xm.类别ID not in ('5','6','7','8') then case when js.状态='作废' then -mx.金额 else mx.金额 end else null end) 诊疗收入 + into #zycf + from ihm_记录_结算 js + inner join ihm_记录_结算_明细 jsmx on js.结算ID=jsmx.结算ID + inner join ihm_记录_明细 mx on jsmx.明细ID=mx.明细ID + inner join bas_收费项目 xm on mx.项目ID=xm.项目ID + where js.结算时间 between ${时间_START} and ${时间_END} and js.结算机构ID=${统计机构} + group by js.结算机构ID + + + select + sum(case when xm.类别ID in ('5','6','7','8') then mx.金额 else null end) 药品金额, + sum(case when xm.类别ID not in ('5','6','7','8') then mx.金额 else null end) 诊疗金额, + sum(mx.金额) 总金额 into #mx + from opc_记录_明细 mx + inner join bas_收费项目 xm on mx.项目ID=xm.项目ID + where mx.登记时间 between ${时间_START} and ${时间_END} and mx.登记机构ID=${统计机构} + union all + select + sum(case when xm.类别ID in ('5','6','7','8') then mx.金额 else null end) 药品金额, + sum(case when xm.类别ID not in ('5','6','7','8') then mx.金额 else null end) 诊疗金额, + sum(mx.金额) 总金额 + from ihm_记录_明细 mx + inner join bas_收费项目 xm on mx.项目ID=xm.项目ID + where mx.登记时间 between ${时间_START} and ${时间_END} and mx.登记机构ID=${统计机构} + + select + sum(t.总金额) 总金额, + sum(t.药品金额) 药品金额, + sum(t.诊疗金额) 诊疗金额 + into #gzl + from #mx t + + + select + js.结算机构ID, + xm.收入项目, + sum(mx.金额) 金额 into #srxmmx + from opc_记录_结算 js + inner join opc_记录_明细 mx on js.结算ID=mx.结算ID + inner join bas_收费项目 xm on mx.项目ID=xm.项目ID + where js.结算时间 between ${时间_START} and ${时间_END} and js.结算机构ID=${统计机构} + group by js.结算机构ID,xm.收入项目 + union all + select + js.结算机构ID, + xm.收入项目, + sum(case when js.状态='作废' then -mx.金额 else mx.金额 end) 金额 + from ihm_记录_结算 js + inner join ihm_记录_结算_明细 jsmx on js.结算ID=jsmx.结算ID + inner join ihm_记录_明细 mx on jsmx.明细ID=mx.明细ID + inner join bas_收费项目 xm on mx.项目ID=xm.项目ID + where js.结算时间 between ${时间_START} and ${时间_END} and js.结算机构ID=${统计机构} + group by js.结算机构ID,xm.收入项目 + + + + select + t.结算机构ID,t.收入项目,sum(t.金额) 金额, + ROW_NUMBER()over(order by t.收入项目) px into #srxm + from #srxmmx t + group by t.结算机构ID,t.收入项目 + + select 收入项目,金额,px into #a from #srxm t where t.px%4=1 + select 收入项目,金额,px into #b from #srxm t where t.px%4=2 + select 收入项目,金额,px into #c from #srxm t where t.px%4=3 + select 收入项目,金额,px into #d from #srxm t where t.px%4=0 + + + select jg.机构ID into #jg + from bas_机构 jg + where jg.机构ID=${统计机构} + + ]]> +</with> + + +<where> + <![CDATA[ + + ]]> +</where> + +<groups> + <group> + <group-col id="分类" field-id="分类" width="120" span="true" span-dep-col-id="分类"/> + <group-col id="列1" field-id="列1" width="120" /> + <group-col id="列1值" field-id="列1值" width="80" /> + <group-col id="列2" field-id="列2" width="170"/> + <group-col id="列2值" field-id="列2值" width="125"/> + <group-col id="列3" field-id="列3" width="120"/> + <group-col id="列3值" field-id="列3值" width="80"/> + <group-col id="列4" field-id="列4" width="120"/> + <group-col id="列4值" field-id="列4值" width="80" /> + </group> +</groups> + +</report> \ No newline at end of file
--- a/ehis.ͳ.Ӫҵ.Ӫ.XML Sun Sep 08 17:07:59 2024 +0800 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,359 +0,0 @@ -<?xml version="1.0" encoding="UTF-8"?> -<report report-id="ehis.数据统计.营业收入.运营情况汇总" title="运营情况汇总" seq="0" is-detail="true"> -<description>运营情况汇总</description> -<attrs> - <attr key="datasource" value="default"/> - <attr key="leftFixedColCount" value="0"/> - <attr key="rightFixedColCount" value="0"/> - <attr key="showTotalRow" value="false"/> - <attr key="showRowIndex" value="true"/> - <attr key="showColIndex" value="false"/> - <attr key="showColLine" value="true"/> - <attr key="showRowLine" value="true"/> - <attr key="showHeader" value="false"/> - <attr key="showTools" value="false"/> - <attr key="trackMouseOver" value="true"/> - <attr key="defaultColWidth" value="100"/> - <attr key="rowNoColWidth" value="40"/> - <attr key="pageType" value="A4"/> - <attr key="pageDirect" value="V"/> - <attr key="pageFontSize" value="11"/> - <attr key="pageSize" value="297,210"/> - <attr key="pageMargin" value="10,10,10,10"/> - <attr key="pageMaxRowCount" value="50"/> - <attr key="autoSizeCol" value="0" /> -</attrs> - -<params> - <param id="jgxs"> - <sql> - <![CDATA[ - select count(1) col into #t from - ( - select 机构ID value,名称 text from bas_机构 where 机构ID=${user.companyPartyId} - union all - select 机构ID value,名称 text from bas_机构 where isnull(管理机构ID,'')=${user.companyPartyId} - ) a - ; - select case when col>1 then 'false' else 'true' end aaa from #t - ]]> - </sql> - </param> - <param id="mrjg"> - <sql> - <![CDATA[ - select ${user.companyPartyId} id - ]]> - </sql> - </param> -</params> - -<enums> - <enum id="tjjg"> - <sql> - <![CDATA[ - select 机构ID value,名称 text from bas_机构 where 机构ID=${user.companyPartyId} - union all - select 机构ID value,名称 text from bas_机构 where isnull(管理机构ID,'')=${user.companyPartyId} - ]]> - </sql> - </enum> -</enums> - -<header-items> - <header-item id="统计机构" type="enum" enum-id="tjjg" title="统计机构" default="${mrjg}" hidden="${jgxs}" value-col="value" use-like='false' text-col="text" width="250" trigger-query="false"/> - <header-item id="时间" type="datetimerange" title="统计时间" default="今天" width="190" trigger-query="false"/> - <header-item id="查找" type="text" width="150" use-like="false" title="查找" hidden="true" empty-text="请输入查找信息" trigger-query="false"/> -</header-items> - -<select> - <field id="分类" title="分类"><![CDATA[分类]]></field> - <field id="列1" title="列1"><![CDATA[列1]]></field> - <field id="列1值" title="列1值"><![CDATA[列1值]]></field> - <field id="列2" title="列2"><![CDATA[列2]]></field> - <field id="列2值" title="列2值"><![CDATA[列2值]]></field> - <field id="列3" title="列3"> <![CDATA[列3]]></field> - <field id="列3值" title="列3值"> <![CDATA[列3值]]></field> - <field id="列4" title="列4"> <![CDATA[列4]]></field> - <field id="列4值" title="列4值"> <![CDATA[列4值]]></field> -</select> - -<from> - <![CDATA[ - ( - select - '门诊情况' 分类,'挂号人数' 列1,gh.挂号人数 列1值,'挂号金额' 列2,cast(gh.挂号金额 as nvarchar(10)) 列2值, - '首诊人数' 列3,gh.首诊人数 列3值,'复诊人数' 列4,gh.复诊人数 列4值 - from #jg jg - left join #gh gh on jg.机构ID=gh.机构ID - union all - select - '门诊情况' 分类,'门诊人次' 列1,mzjs.门诊人次 列1值,'总金额' 列2,cast(mzjs.总金额 as nvarchar(10)) 列2值, - '医保金额' 列3,mzjs.报销金额 列3值,'现金金额' 列4,mzzf.现金支付 列4值 - from #jg jg - left join #mzjs mzjs on jg.机构ID=mzjs.机构ID - left join #mzzf mzzf on jg.机构ID=mzzf.机构ID - union all - select - '门诊情况' 分类,'处方总数' 列1,mzcf.处方总数 列1值,'药品金额' 列2,cast(mzcf.药品金额 as nvarchar(10)) 列2值, - '诊疗金额' 列3,mzcf.诊疗金额 列3值,'处方均额' 列4,case when mzcf.处方总数<>0 then cast(mzjs.总金额/mzcf.处方总数 as decimal(10,2)) else null end 列4值 - from #jg jg - left join #mzcf mzcf on jg.机构ID=mzcf.机构ID - left join #mzjs mzjs on jg.机构ID=mzjs.机构ID - union all - select - '门诊情况' 分类,'自费人次' 列1,mzjs.自费人次 列1值,'医保人次' 列2,cast(mzjs.医保人次 as nvarchar(10)) 列2值, - '普通门诊数' 列3,mzjs.普通门诊人次 列3值,'特病人次' 列4,mzjs.特病人次 列4值 - from #jg jg - left join #mzjs mzjs on jg.机构ID=mzjs.机构ID - union all - select - '住院情况' 分类,'入院总人数' 列1,ry.入院总人数 列1值,'当前在院总人数' 列2,cast(zy.在院总人数 as nvarchar(10)) 列2值, - '出院总人数' 列3,cy.出院总人数 列3值,'结算总人数' 列4,zyjs.结算总人数 列4值 - from #jg jg - left join #ry ry on jg.机构ID=ry.机构ID - left join #cy cy on jg.机构ID=cy.机构ID - left join #zyjs zyjs on jg.机构ID=zyjs.机构ID - left join #zy zy on jg.机构ID=zy.机构ID - union all - select - '住院情况' 分类,'结算金额' 列1,zyjs.结算金额 列1值,'职工金额' 列2,cast(zyjs.职工金额 as nvarchar(10)) 列2值, - '居民金额' 列3,zyjs.居民金额 列3值,'自费金额' 列4,zyjs.自费金额 列4值 - from #jg jg - left join #zyjs zyjs on jg.机构ID=zyjs.机构ID - union all - select - '住院情况' 分类,'预交收入' 列1,zyyj.预交收入 列1值,'药品收入' 列2,cast(zycf.药品收入 as nvarchar(10)) 列2值, - '诊疗收入' 列3,zycf.诊疗收入 列3值,'医院承担' 列4,zyjs.医院承担 列4值 - from #jg jg - left join #zyjs zyjs on jg.机构ID=zyjs.机构ID - left join #zyyj zyyj on jg.机构ID=zyyj.机构ID - left join #zycf zycf on jg.机构ID=zycf.机构ID - union all - select null,'',null,'工作量情况(医院实际工','作量情况,与结算','数据无关)',null,null,null - union all - select - null,'总费用',t.总金额,'药品金额',cast(t.药品金额 as nvarchar(10)) ,'诊疗金额',t.诊疗金额,null,null - from #gzl t - union all - select null,'',null,'各项收入项目费用情况','(结算数据)','',null,null,null - union all - select - null,a.收入项目,a.金额,b.收入项目,cast(b.金额 as nvarchar(10)) ,c.收入项目,c.金额,d.收入项目,d.金额 - from #a a - left join #b b on a.px+1=b.px - left join #c c on a.px+2=c.px - left join #d d on a.px+3=d.px - union all - select null, '合计' ,sum(t.金额),null,null,null,null,null,null - from #srxm t - ) t - ]]> -</from> - -<with> - <![CDATA[ - - with t as ( - select - gh.登记机构ID 机构ID, - count(distinct gh.档案ID) 挂号人数, - count(distinct case when isnull(jzjl.初诊,'是')='是' then jzjl.档案ID else null end) 首诊人数, - count(distinct case when isnull(jzjl.初诊,'是')='否' then jzjl.档案ID else null end) 复诊人数, - sum(js.金额) 挂号金额 - from reg_挂号 gh - inner join opd_记录 jzjl on gh.挂号ID=jzjl.挂号ID - inner join opc_记录_结算 js on gh.结算ID=js.结算ID - where gh.就诊日期 between ${时间_START} and ${时间_END} and gh.登记机构ID=${统计机构} - group by gh.登记机构ID - ) - select * into #gh from t; - select - js.结算机构ID 机构ID, - count(distinct jl.档案ID+'-'+jl.开单医生ID+'-'+convert(nvarchar(10),jl.登记时间,120)) 门诊人次, - sum(js.金额) 总金额, - sum(js.报销金额) 报销金额, - COUNT(DISTINCT (case when ybjs.结算ID IS NULL THEN jl.档案ID+'-'+jl.开单医生ID+'-'+convert(nvarchar(10),jl.登记时间,120) ELSE NULL END)) 自费人次, - COUNT(DISTINCT (case when ybjs.结算ID IS NOT NULL THEN jl.档案ID+'-'+jl.开单医生ID+'-'+convert(nvarchar(10),jl.登记时间,120) ELSE NULL END)) 医保人次, - count(distinct (case when jzlb.名称 in ('门诊慢特病') then jl.档案ID+'-'+jl.开单医生ID+'-'+convert(nvarchar(10),jl.登记时间,120) else null end)) 特病人次, - count(distinct (case when jzlb.名称 not in ('门诊慢特病') then jl.档案ID+'-'+jl.开单医生ID+'-'+convert(nvarchar(10),jl.登记时间,120) else null end)) 普通门诊人次 - into #mzjs - from opc_记录 jl - inner join opc_记录_结算 js on jl.记录ID=js.记录ID - left join yb_就诊结算记录 ybjs on js.结算ID=ybjs.结算ID - left join bas_就诊类别 jzlb on jl.医疗类别ID=jzlb.医疗类别ID AND jl.就诊类别ID=jzlb.就诊类别ID - where js.结算时间 between ${时间_START} and ${时间_END} and js.结算机构ID=${统计机构} - and jl.处方来源<>'门诊挂号' - group by js.结算机构ID - - - select - js.结算机构ID 机构ID, - sum(case when zffs.名称='现金' then zf.支付金额 else null end) 现金支付, - sum(case when zffs.名称!='现金' then zf.支付金额 else null end) 其他支付 - into #mzzf - from opc_记录_结算 js - inner join opc_记录 jl on js.记录ID=jl.记录ID - inner join opc_记录_结算_支付 zf on js.结算ID=zf.结算ID - inner join bas_支付方式 zffs on zf.支付方式ID=zffs.支付方式ID - where js.结算时间 between ${时间_START} and ${时间_END} and js.结算机构ID=${统计机构} - and jl.处方来源<>'门诊挂号' - group by js.结算机构ID - - - --cf - select - js.结算机构ID 机构ID, - count(distinct fymx.处方ID) 处方总数, - sum(case when xm.类别ID in ('5','6','7','8') then mx.金额 else null end) 药品金额, - sum(case when xm.类别ID not in ('5','6','7','8') then mx.金额 else null end) 诊疗金额 - into #mzcf - from opc_记录_明细 mx - inner join opc_记录_结算 js on mx.结算ID=js.结算ID - inner join bas_收费项目 xm on mx.项目ID=xm.项目ID - left join opd_处方_医嘱_费用 fymx on mx.医嘱明细ID=fymx.明细ID - where js.结算时间 between ${时间_START} and ${时间_END} AND js.结算机构ID=${统计机构} - group by js.结算机构ID - - --住院 - select jl.登记机构ID 机构ID, count(1) 入院总人数 into #ry - from ihm_记录 jl - where jl.入院时间 between ${时间_START} and ${时间_END} AND 登记机构ID=${统计机构} - group by jl.登记机构ID - - select - jl.登记机构ID 机构ID, - count(1) 在院总人数 - into #zy - from ihm_记录 jl - where jl.状态 in ('在床','待分') - group by jl.登记机构ID - - - - select - jl.登记机构ID 机构ID, count(1) 出院总人数 into #cy - from ihm_记录 jl - where jl.出院时间 between ${时间_START} and ${时间_END} - group by jl.登记机构ID - - select js.结算机构ID 机构ID,count(distinct 记录ID) 结算总人数, - sum(js.金额) 结算金额, - sum(case when ybjs.险种类型='310' then js.金额 else null end) 职工金额, - sum(case when ybjs.险种类型='390' then js.金额 else null end) 居民金额, - sum(case when ybjs.结算ID is null then js.金额 else null end) 自费金额, - sum(js.医院承担) 医院承担 - into #zyjs - from ihm_记录_结算 js - left join yb_就诊结算记录 ybjs on js.结算ID=ybjs.结算ID AND js.结算机构ID=${统计机构} - where js.结算时间 between ${时间_START} and ${时间_END} - group by js.结算机构ID - - select - yj.登记机构ID 机构ID, - sum(yj.金额) 预交收入 - into #zyyj - from ihm_记录_预交 yj - where yj.收费时间 between ${时间_START} and ${时间_END} and yj.登记机构ID=${统计机构} - group by yj.登记机构ID - - select js.结算机构ID 机构ID, - sum(case when xm.类别ID in ('5','6','7','8') then case when js.状态='作废' then -mx.金额 else mx.金额 end else null end) 药品收入, - sum(case when xm.类别ID not in ('5','6','7','8') then case when js.状态='作废' then -mx.金额 else mx.金额 end else null end) 诊疗收入 - into #zycf - from ihm_记录_结算 js - inner join ihm_记录_结算_明细 jsmx on js.结算ID=jsmx.结算ID - inner join ihm_记录_明细 mx on jsmx.明细ID=mx.明细ID - inner join bas_收费项目 xm on mx.项目ID=xm.项目ID - where js.结算时间 between ${时间_START} and ${时间_END} and js.结算机构ID=${统计机构} - group by js.结算机构ID - - - select - sum(case when xm.类别ID in ('5','6','7','8') then mx.金额 else null end) 药品金额, - sum(case when xm.类别ID not in ('5','6','7','8') then mx.金额 else null end) 诊疗金额, - sum(mx.金额) 总金额 into #mx - from opc_记录_明细 mx - inner join bas_收费项目 xm on mx.项目ID=xm.项目ID - where mx.登记时间 between ${时间_START} and ${时间_END} and mx.登记机构ID=${统计机构} - union all - select - sum(case when xm.类别ID in ('5','6','7','8') then mx.金额 else null end) 药品金额, - sum(case when xm.类别ID not in ('5','6','7','8') then mx.金额 else null end) 诊疗金额, - sum(mx.金额) 总金额 - from ihm_记录_明细 mx - inner join bas_收费项目 xm on mx.项目ID=xm.项目ID - where mx.登记时间 between ${时间_START} and ${时间_END} and mx.登记机构ID=${统计机构} - - select - sum(t.总金额) 总金额, - sum(t.药品金额) 药品金额, - sum(t.诊疗金额) 诊疗金额 - into #gzl - from #mx t - - - select - js.结算机构ID, - xm.收入项目, - sum(mx.金额) 金额 into #srxmmx - from opc_记录_结算 js - inner join opc_记录_明细 mx on js.结算ID=mx.结算ID - inner join bas_收费项目 xm on mx.项目ID=xm.项目ID - where js.结算时间 between ${时间_START} and ${时间_END} and js.结算机构ID=${统计机构} - group by js.结算机构ID,xm.收入项目 - union all - select - js.结算机构ID, - xm.收入项目, - sum(case when js.状态='作废' then -mx.金额 else mx.金额 end) 金额 - from ihm_记录_结算 js - inner join ihm_记录_结算_明细 jsmx on js.结算ID=jsmx.结算ID - inner join ihm_记录_明细 mx on jsmx.明细ID=mx.明细ID - inner join bas_收费项目 xm on mx.项目ID=xm.项目ID - where js.结算时间 between ${时间_START} and ${时间_END} and js.结算机构ID=${统计机构} - group by js.结算机构ID,xm.收入项目 - - - - select - t.结算机构ID,t.收入项目,sum(t.金额) 金额, - ROW_NUMBER()over(order by t.收入项目) px into #srxm - from #srxmmx t - group by t.结算机构ID,t.收入项目 - - select 收入项目,金额,px into #a from #srxm t where t.px%4=1 - select 收入项目,金额,px into #b from #srxm t where t.px%4=2 - select 收入项目,金额,px into #c from #srxm t where t.px%4=3 - select 收入项目,金额,px into #d from #srxm t where t.px%4=0 - - - select jg.机构ID into #jg - from bas_机构 jg - where jg.机构ID=${统计机构} - - ]]> -</with> - - -<where> - <![CDATA[ - - ]]> -</where> - -<groups> - <group> - <group-col id="分类" field-id="分类" width="120" span="true" span-dep-col-id="分类"/> - <group-col id="列1" field-id="列1" width="120" /> - <group-col id="列1值" field-id="列1值" width="80" /> - <group-col id="列2" field-id="列2" width="170"/> - <group-col id="列2值" field-id="列2值" width="125"/> - <group-col id="列3" field-id="列3" width="120"/> - <group-col id="列3值" field-id="列3值" width="80"/> - <group-col id="列4" field-id="列4" width="120"/> - <group-col id="列4值" field-id="列4值" width="80" /> - </group> -</groups> - -</report> \ No newline at end of file
