帆软参数
帆软的左下角（西南方向） ==》 过滤 过滤标记-黄色小三角
帆软的右下角（西北方向） ==》 条件属性 条件属性标识-红色小三角
https://help.fanruan.com/finereport/doc-view-231.html -- 单元格标识说明
本地目录 D:\帆软\FineReport_10.0\webapps\webroot\WEB-INF\reportlets
&op=write
&yq=${orgAreaCode}
&orgCode=${orgCode}
&empCode=${empCode}
--自定义参数
&Current_Nursing_Unit=${orgCode}
http://172.30.130.65:8080/webroot/decision/view/report?viewlet=his/dms/dms_cgjh_pgd.cpt&op=write&yq=${orgAreaCode}&orgCode=${orgCode}&empCode=${empCode}
http://172.30.130.65:8080/webroot/decision/view/report?viewlet=his/dms/dms_cgjh_pgd.cpt&op=write&yq=H0002&orgCode=300040102&empCode=FHY0009
=========生产报表查询==========================
select
c.name as 工作台 ,
a.RESOURCE_NAME 菜单 ,
b.RESOURCE_CODE as 报表编码 ,
b.RESOURCE_NAME as 报表名称 ,
b.SORT_NO 序号 ,b.PROPERTY2 as 源文件地址
from PMS.PMS_WORKBENCH_RESOURCE a
left join PMS.PMS_WORKBENCH_RESOURCE b on a.id =b.PARENT_ID
left join PMS.PMS_WORKBENCH c on b.workbench_id =c.id
where
b.PROPERTY1='net.carefx.framework.client.mvc.page.HostedPageEntryPoint'
and b.PROPERTY2 like '%172.30.130.6%'
--and b.resource_name like '%销售统计%'
order by NAME , a.RESOURCE_NAME ,b.sort_no asc
=====================================================================
驱动: oracle.jdbc.driver.OracleDriver
jdbc连接: jdbc:oracle:thin:@172.30.120.20:1521/hisdb
用户名 thdba
密码： thzk211！
生产库报表的名称： THBI-DG
=======测试环境============
172.30.99.130/orcl
select * from V$version
select INSTANCE_NAME from v$instance;
报表目录：
/opt/earth/jboss/standalone/deployments/webroot.war/WEB-INF/reportlets/his
admin
Thzk211!
select * from uum.UUM_SYSTEM_FUNCTION a WHERE A.FUNCTION_NAME LIKE '%门急诊人次%';
select * from uum.UUM_SYSTEM A WHERE A.ID='2027';
MONTHDELTA(TODAY()-1,-1)
--上月的开始时间 上月的结束时间
DATEINMONTH(today()-day(today()),1) DATEINMONTH(today()-day(today()),-1)+" 23:59:59"
======================================================================
报表参数 科室编码 ${orgCode} 院区编码 ${orgAreaCode} 工号 ${empCode}
http://172.30.130.65:8080/webroot/decision/view/report?viewlet=his/workload/ksgzltj_new.cpt&op=write&orgCode=${orgCode}&yq=${orgAreaCode}&empCode=${empCode}
http://172.30.130.65:8080/webroot/decision/view/report?viewlet=his/cw/cw_mzsfy_rb.cpt&yq=${orgAreaCode}&Operator=${empCode}
and visit.CURRENT_WARD = '${orgCode}'
=====================================================================
VALUE("ds0_AreaCurrent", "AREA_NAME", 1)
制表人参数
===========================================================================
"制表人："+sql("THBI-DG","select t.people_name from uum.uum_user t where t.user_name='"+$empCode+"'",1,1)
===========================================================================
-- 根据下拉框选中的院区，查询当前院区编码及名称
=====================================================================
ds0_AreaList
-- 院区编码及名称
select *
from THBI.V_RPT_AREA_LIST t
ds0_AreaCurrent
select *
from THBI.V_RPT_AREA_LIST t
where t.area_code = '${Area_Code}'
院区参数
${
switch(LEN(Area_Code),
4,
"and A.Area like '" + Area_Code + "_" + "' ",
5,
"and A.Area = '" + Area_Code + "'")
}
=====================================================================
CONCATENATE(" 统计日期：", $StDate_from, IF($StDate_from = $StDate_to, "", CONCATENATE(" 到 ", $StDate_to)), " 制表时间：", NOW())
CONCATENATE(VALUE("ds0_AreaCurrent", "AREA_NAME", 1) , "病区护士交班日报表")
CONCATENATE(VALUE("ds0_AreaCurrent", "AREA_NAME_SHORT", 1), "收费项目套餐查询")
===================月参数=月初 月末=================================================
DATEINMONTH(today()-day(today()),1) --2023-05-01 00:00:00
DATEINMONTH(today()-day(today()),-1)+" 23:59:59" --2023-05-31 23:59:59
=====================================================================
ds0_AreaList
-- 院区编码及名称
select *
from THBI.V_RPT_AREA_LIST t
${if(isnull(areaCode), "", " where t.area_code = '" + areaCode + "' or t.parent_area_code = '" + areaCode + "'")}
=====================================================================
制表人
"制表人：" + sql("THBI-DG", "select t.people_name from uum.uum_user t where t.user_name='" + $empCode + "'", 1, 1)
H0001
参数
====================
>= to_date('${StDate_from}', 'yyyy-mm-dd hh24:mi:ss')
<= to_date('${StDate_to}', 'yyyy-mm-dd hh24:mi:ss')
===按住院时间 出院时间查询
${
switch(category,
'1' ," visit.new_in_dept_date >= to_date('"+StDate_from+"','yyyy-mm-dd hh24:mi:ss') and visit.new_in_dept_date <= to_date('"+StDate_to+"', 'yyyy-mm-dd hh24:mi:ss') ",
'2' ," visit.discharge_dept_date >= to_date('"+StDate_from+"','yyyy-mm-dd hh24:mi:ss') and visit.discharge_dept_date <= to_date('"+StDate_to+"', 'yyyy-mm-dd hh24:mi:ss') "
)}
${switch(pd,
"","",
"2","and A1.code='"+ks+"'",
"7","and A2.code='"+ks+"'")}
${switch(pd1,
"","",
"1","and A.Patient_Id='"+name+"'",
"2","and A.Bed_Code='"+name+"'",
"3","and A.Patient_Name like '%"+name+"%'")}
--sql脚本中可以带switch表达式
left join bds.bds_organization A1 on A1.Code=${switch(pd,"1","A.Current_Ward","2","A.Current_Nursing_Unit")}
====================
=StDate_from
=StDate_to
CONCATENATE(" 统计日期：", $StDate_from, IF($StDate_from = $StDate_to, "", CONCATENATE(" 到 ", $StDate_to)), " 制表时间：", NOW())
"统计日期：" + $StDate_from + IF($StDate_from = $StDate_to, "", CONCATENATE(" 到 ", $StDate_to))
"第"+$$page_number+"页,共"+$$totalPage_number+"页"
"制表时间："+ NOW()
公式 - 时间型
today()+" 00:00:00"
today()+" 23:59:59"
2023-04-25 00:00:00
2023-04-28 23:59:59
--下拉多选框
select * from 订单
where 1=1
${
if(len(diqu)==0,"","and 货主地区 in ('" + diqu +"')")
}
${
if(len(shengfen)==0,""," and 货主省份 in ('" + shengfen +"')")
}
${
if(len(chengshi)==0,""," and 货主城市 in ('" + chengshi +"')")
}
--单选按钮控件参数:
select *from 订单
where 1=1
${
IF(Query_type="DAY","AND 订购日期 >= '"+ YYYYMMDD_S+"' AND 订购日期< '"+YYYYMMDD_E+"'" ,
IF(Query_type="WEEK","AND 订购日期>='"+YYYYWW_S+"' AND 订购日期<'"+YYYYWW_E+"'","AND 订购日期>='"+YYYYMM_S+"' AND 订购日期<'"+YYYYMM_E+"'"))
}
${
if(len(患者编号)==0,""," and 患者编号 = '"+患者编号+"'")
}
swith函数
============================================
${
switch(pd,
"","",
"2","and A1.code='"+ks+"'",
"7","and A2.code='"+ks+"'")
}
${switch(pd1,
"","",
"1","and A.Patient_Id='"+name+"'",
"2","and A.Bed_Code='"+name+"'",
"3","and A.Patient_Name like '%"+name+"%'")
}
${switch(Person_Type,
"","",
"390","and a3.insur_type='390'",
"310","and a3.insur_type='310'"
)}
========多院区=================================================
select * from (
select 'H0001' as yuanqu , 10 as 人次 from dual
union all
select 'H0002' as yuanqu ,5 as 人次 from dual
) a
============
方法一
============
${switch(len(Area_Code),
4,
"and a.yuanqu like '" + Area_Code +'_' + "' ",
5,
"and a.yuanqu = '" + Area_Code + "'") }
============
方法二
============
${
switch(Area_Code,
'H0001' ," and a.yuanqu='"+Area_Code+"' ",
'H0002' ," and a.yuanqu='"+Area_Code+"' ",
'H000' ," and a.yuanqu in (select area_code from THBI.V_RPT_AREA_LIST where parent_area_code ='"+Area_Code+"' ) " ,
'',""
)}
标题使用switch
switch(pd,"1","住院抗菌药物使用率（按科室）","2","住院抗菌药物使用率（按病区）")
--swith转化
${
switch(category,
'1' ," and visit.in_register_date >= to_date('"+StDate_from+"','yyyy-mm-dd hh24:mi:ss') and visit.in_register_date <= to_date('"+StDate_to+"', 'yyyy-mm-dd hh24:mi:ss') ",
'2' ," and visit.pre_transfer_out_date >= to_date('"+StDate_from+"','yyyy-mm-dd hh24:mi:ss') and visit.pre_transfer_out_date <= to_date('"+StDate_to+"', 'yyyy-mm-dd hh24:mi:ss') "
)}
--发药时间勾了 取发药时间 否则取 创建时间
${if(fysj = TRUE ,
" AND psp.DISPENSING_DATETIME BETWEEN to_date('" + startdate + "', 'yyyy-mm-dd,hh24:mi:ss' )
AND to_date('"+ enddate + "', 'yyyy-mm-dd,hh24:mi:ss') ",
" AND ps.create_date BETWEEN to_date('"+ startdate +"', 'yyyy-mm-dd,hh24:mi:ss')
AND to_date('"+ enddate +"', 'yyyy-mm-dd,hh24:mi:ss') " )}
${switch( category, "","", '1'," and bi.item_class ='M' ",'2'," and bi.item_class !='M' ")}
帆软的右下角（西北方向） ==》 条件属性 条件属性标识-红色小三角
https://help.fanruan.com/finereport/doc-view-231.html -- 单元格标识说明
本地目录 D:\帆软\FineReport_10.0\webapps\webroot\WEB-INF\reportlets
&op=write
&yq=${orgAreaCode}
&orgCode=${orgCode}
&empCode=${empCode}
--自定义参数
&Current_Nursing_Unit=${orgCode}
http://172.30.130.65:8080/webroot/decision/view/report?viewlet=his/dms/dms_cgjh_pgd.cpt&op=write&yq=${orgAreaCode}&orgCode=${orgCode}&empCode=${empCode}
http://172.30.130.65:8080/webroot/decision/view/report?viewlet=his/dms/dms_cgjh_pgd.cpt&op=write&yq=H0002&orgCode=300040102&empCode=FHY0009
=========生产报表查询==========================
select
c.name as 工作台 ,
a.RESOURCE_NAME 菜单 ,
b.RESOURCE_CODE as 报表编码 ,
b.RESOURCE_NAME as 报表名称 ,
b.SORT_NO 序号 ,b.PROPERTY2 as 源文件地址
from PMS.PMS_WORKBENCH_RESOURCE a
left join PMS.PMS_WORKBENCH_RESOURCE b on a.id =b.PARENT_ID
left join PMS.PMS_WORKBENCH c on b.workbench_id =c.id
where
b.PROPERTY1='net.carefx.framework.client.mvc.page.HostedPageEntryPoint'
and b.PROPERTY2 like '%172.30.130.6%'
--and b.resource_name like '%销售统计%'
order by NAME , a.RESOURCE_NAME ,b.sort_no asc
=====================================================================
驱动: oracle.jdbc.driver.OracleDriver
jdbc连接: jdbc:oracle:thin:@172.30.120.20:1521/hisdb
用户名 thdba
密码： thzk211！
生产库报表的名称： THBI-DG
=======测试环境============
172.30.99.130/orcl
select * from V$version
select INSTANCE_NAME from v$instance;
报表目录：
/opt/earth/jboss/standalone/deployments/webroot.war/WEB-INF/reportlets/his
admin
Thzk211!
select * from uum.UUM_SYSTEM_FUNCTION a WHERE A.FUNCTION_NAME LIKE '%门急诊人次%';
select * from uum.UUM_SYSTEM A WHERE A.ID='2027';
MONTHDELTA(TODAY()-1,-1)
--上月的开始时间 上月的结束时间
DATEINMONTH(today()-day(today()),1) DATEINMONTH(today()-day(today()),-1)+" 23:59:59"
======================================================================
报表参数 科室编码 ${orgCode} 院区编码 ${orgAreaCode} 工号 ${empCode}
http://172.30.130.65:8080/webroot/decision/view/report?viewlet=his/workload/ksgzltj_new.cpt&op=write&orgCode=${orgCode}&yq=${orgAreaCode}&empCode=${empCode}
http://172.30.130.65:8080/webroot/decision/view/report?viewlet=his/cw/cw_mzsfy_rb.cpt&yq=${orgAreaCode}&Operator=${empCode}
and visit.CURRENT_WARD = '${orgCode}'
=====================================================================
VALUE("ds0_AreaCurrent", "AREA_NAME", 1)
制表人参数
===========================================================================
"制表人："+sql("THBI-DG","select t.people_name from uum.uum_user t where t.user_name='"+$empCode+"'",1,1)
===========================================================================
-- 根据下拉框选中的院区，查询当前院区编码及名称
=====================================================================
ds0_AreaList
-- 院区编码及名称
select *
from THBI.V_RPT_AREA_LIST t
ds0_AreaCurrent
select *
from THBI.V_RPT_AREA_LIST t
where t.area_code = '${Area_Code}'
院区参数
${
switch(LEN(Area_Code),
4,
"and A.Area like '" + Area_Code + "_" + "' ",
5,
"and A.Area = '" + Area_Code + "'")
}
=====================================================================
CONCATENATE(" 统计日期：", $StDate_from, IF($StDate_from = $StDate_to, "", CONCATENATE(" 到 ", $StDate_to)), " 制表时间：", NOW())
CONCATENATE(VALUE("ds0_AreaCurrent", "AREA_NAME", 1) , "病区护士交班日报表")
CONCATENATE(VALUE("ds0_AreaCurrent", "AREA_NAME_SHORT", 1), "收费项目套餐查询")
===================月参数=月初 月末=================================================
DATEINMONTH(today()-day(today()),1) --2023-05-01 00:00:00
DATEINMONTH(today()-day(today()),-1)+" 23:59:59" --2023-05-31 23:59:59
=====================================================================
ds0_AreaList
-- 院区编码及名称
select *
from THBI.V_RPT_AREA_LIST t
${if(isnull(areaCode), "", " where t.area_code = '" + areaCode + "' or t.parent_area_code = '" + areaCode + "'")}
=====================================================================
制表人
"制表人：" + sql("THBI-DG", "select t.people_name from uum.uum_user t where t.user_name='" + $empCode + "'", 1, 1)
H0001
参数
====================
>= to_date('${StDate_from}', 'yyyy-mm-dd hh24:mi:ss')
<= to_date('${StDate_to}', 'yyyy-mm-dd hh24:mi:ss')
===按住院时间 出院时间查询
${
switch(category,
'1' ," visit.new_in_dept_date >= to_date('"+StDate_from+"','yyyy-mm-dd hh24:mi:ss') and visit.new_in_dept_date <= to_date('"+StDate_to+"', 'yyyy-mm-dd hh24:mi:ss') ",
'2' ," visit.discharge_dept_date >= to_date('"+StDate_from+"','yyyy-mm-dd hh24:mi:ss') and visit.discharge_dept_date <= to_date('"+StDate_to+"', 'yyyy-mm-dd hh24:mi:ss') "
)}
${switch(pd,
"","",
"2","and A1.code='"+ks+"'",
"7","and A2.code='"+ks+"'")}
${switch(pd1,
"","",
"1","and A.Patient_Id='"+name+"'",
"2","and A.Bed_Code='"+name+"'",
"3","and A.Patient_Name like '%"+name+"%'")}
--sql脚本中可以带switch表达式
left join bds.bds_organization A1 on A1.Code=${switch(pd,"1","A.Current_Ward","2","A.Current_Nursing_Unit")}
====================
=StDate_from
=StDate_to
CONCATENATE(" 统计日期：", $StDate_from, IF($StDate_from = $StDate_to, "", CONCATENATE(" 到 ", $StDate_to)), " 制表时间：", NOW())
"统计日期：" + $StDate_from + IF($StDate_from = $StDate_to, "", CONCATENATE(" 到 ", $StDate_to))
"第"+$$page_number+"页,共"+$$totalPage_number+"页"
"制表时间："+ NOW()
公式 - 时间型
today()+" 00:00:00"
today()+" 23:59:59"
2023-04-25 00:00:00
2023-04-28 23:59:59
--下拉多选框
select * from 订单
where 1=1
${
if(len(diqu)==0,"","and 货主地区 in ('" + diqu +"')")
}
${
if(len(shengfen)==0,""," and 货主省份 in ('" + shengfen +"')")
}
${
if(len(chengshi)==0,""," and 货主城市 in ('" + chengshi +"')")
}
--单选按钮控件参数:
select *from 订单
where 1=1
${
IF(Query_type="DAY","AND 订购日期 >= '"+ YYYYMMDD_S+"' AND 订购日期< '"+YYYYMMDD_E+"'" ,
IF(Query_type="WEEK","AND 订购日期>='"+YYYYWW_S+"' AND 订购日期<'"+YYYYWW_E+"'","AND 订购日期>='"+YYYYMM_S+"' AND 订购日期<'"+YYYYMM_E+"'"))
}
${
if(len(患者编号)==0,""," and 患者编号 = '"+患者编号+"'")
}
swith函数
============================================
${
switch(pd,
"","",
"2","and A1.code='"+ks+"'",
"7","and A2.code='"+ks+"'")
}
${switch(pd1,
"","",
"1","and A.Patient_Id='"+name+"'",
"2","and A.Bed_Code='"+name+"'",
"3","and A.Patient_Name like '%"+name+"%'")
}
${switch(Person_Type,
"","",
"390","and a3.insur_type='390'",
"310","and a3.insur_type='310'"
)}
========多院区=================================================
select * from (
select 'H0001' as yuanqu , 10 as 人次 from dual
union all
select 'H0002' as yuanqu ,5 as 人次 from dual
) a
============
方法一
============
${switch(len(Area_Code),
4,
"and a.yuanqu like '" + Area_Code +'_' + "' ",
5,
"and a.yuanqu = '" + Area_Code + "'") }
============
方法二
============
${
switch(Area_Code,
'H0001' ," and a.yuanqu='"+Area_Code+"' ",
'H0002' ," and a.yuanqu='"+Area_Code+"' ",
'H000' ," and a.yuanqu in (select area_code from THBI.V_RPT_AREA_LIST where parent_area_code ='"+Area_Code+"' ) " ,
'',""
)}
标题使用switch
switch(pd,"1","住院抗菌药物使用率（按科室）","2","住院抗菌药物使用率（按病区）")
--swith转化
${
switch(category,
'1' ," and visit.in_register_date >= to_date('"+StDate_from+"','yyyy-mm-dd hh24:mi:ss') and visit.in_register_date <= to_date('"+StDate_to+"', 'yyyy-mm-dd hh24:mi:ss') ",
'2' ," and visit.pre_transfer_out_date >= to_date('"+StDate_from+"','yyyy-mm-dd hh24:mi:ss') and visit.pre_transfer_out_date <= to_date('"+StDate_to+"', 'yyyy-mm-dd hh24:mi:ss') "
)}
--发药时间勾了 取发药时间 否则取 创建时间
${if(fysj = TRUE ,
" AND psp.DISPENSING_DATETIME BETWEEN to_date('" + startdate + "', 'yyyy-mm-dd,hh24:mi:ss' )
AND to_date('"+ enddate + "', 'yyyy-mm-dd,hh24:mi:ss') ",
" AND ps.create_date BETWEEN to_date('"+ startdate +"', 'yyyy-mm-dd,hh24:mi:ss')
AND to_date('"+ enddate +"', 'yyyy-mm-dd,hh24:mi:ss') " )}
${switch( category, "","", '1'," and bi.item_class ='M' ",'2'," and bi.item_class !='M' ")}
浙公网安备 33010602011771号