由sql动态生成datawindow

ll_count=tab_1.tabpage_2.dw_5.retrieve(ld_start_date,ld_end_date)
for i=1 to ll_count
ld_date=date(tab_1.tabpage_2.dw_5.object.dates[i])
//1 白班护理患者数
ldt_daytime_start=datetime(ld_date,time("08:00:00"))
ldt_daytime_end=datetime(ld_date,time("16:59:59"))
if i=1 then
if ldt_sysdate>=ldt_daytime_start then
ls_sql1="select count(*) as amouts from pat_visit aa where aa.admission_date_time <=to_date('" +string(ldt_daytime_end,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss')" + &
" and (aa.discharge_date_time is null or aa.discharge_date_time>= to_date('"+string(ldt_daytime_start,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss'))~n~r"
else
ls_sql1="select 0 as amouts from dual~n~r"
end if
else
if i=ll_count then
if ldt_sysdate>=ldt_daytime_start then
ls_sql1=ls_sql1 +&
" union all ~n~r" +&
"select count(*) as amouts from pat_visit aa where aa.admission_date_time <=to_date('" +string(ldt_daytime_end,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss')" + &
" and (aa.discharge_date_time is null or aa.discharge_date_time >= to_date('"+string(ldt_daytime_start,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss')) ~n~r"
else
ls_sql1=ls_sql1 +&
" union all ~n~r" +&
"select 0 as amouts from dual~n~r"
end if
else
ls_sql1=ls_sql1 +&
" union all ~n~r" +&
"select count(*) as amouts from pat_visit aa where aa.admission_date_time <=to_date('" +string(ldt_daytime_end,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss')" + &
" and (aa.discharge_date_time is null or aa.discharge_date_time >= to_date('"+string(ldt_daytime_start,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss')) ~n~r"
end if
end if

//2 夜班护理患者数
ldt_night_start=datetime(ld_date,time("17:00:00"))
ldt_night_end=datetime(RelativeDate(ld_date,1),time("07:59:59"))

if i=1 then
if ldt_sysdate>=ldt_night_start then
ls_sql2="select count(*) as amouts from pat_visit aa where aa.admission_date_time <=to_date('" +string(ldt_night_end,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss')" + &
" and (aa.discharge_date_time is null or aa.discharge_date_time >= to_date('"+string(ldt_night_start,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss'))~n~r"
else
ls_sql2="select 0 as amouts from dual~n~r"
end if
else
if i=ll_count then
if ldt_sysdate>=ldt_night_start then
ls_sql2=ls_sql2 +&
" union all ~n~r" +&
"select count(*) as amouts from pat_visit aa where aa.admission_date_time <=to_date('" +string(ldt_night_end,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss')" + &
" and (aa.discharge_date_time is null or aa.discharge_date_time >= to_date('"+string(ldt_night_start,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss')) ~n~r"
else
ls_sql2=ls_sql2 +&
" union all ~n~r" +&
"select 0 as amouts from dual~n~r"
end if
else
ls_sql2=ls_sql2 +&
" union all ~n~r" +&
"select count(*) as amouts from pat_visit aa where aa.admission_date_time <=to_date('" +string(ldt_night_end,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss')" + &
" and (aa.discharge_date_time is null or aa.discharge_date_time >= to_date('"+string(ldt_night_start,'yyyy-mm-dd hh:mm:ss') + "','yyyy-mm-dd hh24:mi:ss')) ~n~r"
end if
end if
next

ls_sqltring1=" select sum(amouts) from (" +ls_sql1 + ")"
ls_sqltring2=" select sum(amouts) from (" +ls_sql2 + ")"


datastore l_ds
boolean lb_rtn = true
string ls_Syntax ,ls_Error
l_ds = create datastore

ls_Syntax=SQLCA.SyntaxFromSQL(ls_sqltring1 ,'Style(Type=Form)', ls_Error)
if Len(ls_Error) > 0 then
MessageBox("系统提示" ,ls_Error)
else
l_ds.Create(ls_Syntax ,ls_Error)
if Len(ls_Error) > 0 THEN
MessageBox("系统提示" ,ls_Error)
else
l_ds.SetTransObject(SQLCA)
ll_count=l_ds.Retrieve()
if ll_count=1 then
ll_ts=l_ds.getitemnumber(1,1)
if ll_ts>0 then
tab_1.tabpage_2.dw_2.object.indicators_value[1]=string(ll_ts)
else
tab_1.tabpage_2.dw_2.object.indicators_value[1]=""
end if
end if
end if
end if

ls_Syntax=SQLCA.SyntaxFromSQL(ls_sqltring2 ,'Style(Type=Form)', ls_Error)
if Len(ls_Error) > 0 then
MessageBox("系统提示" ,ls_Error)
else
l_ds.Create(ls_Syntax ,ls_Error)
if Len(ls_Error) > 0 THEN
MessageBox("系统提示" ,ls_Error)
else
l_ds.SetTransObject(SQLCA)
ll_count=l_ds.Retrieve()
if ll_count=1 then
ll_ts=l_ds.getitemnumber(1,1)
if ll_ts>0 then
tab_1.tabpage_2.dw_2.object.indicators_value[2]=string(ll_ts)
else
tab_1.tabpage_2.dw_2.object.indicators_value[2]=""
end if
end if
end if
end if