帆軟引數

帆軟的左下角（西南方向） ==》 過濾 過濾標記-黃色小三角
帆軟的右下角（西北方向） ==》 條件屬性 條件屬性標識-紅色小三角
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' ")}