后台多条sql查询,json传前台,前台处理多条sql数据实例
前台jsp页面:
<div class="yppp_2" style="margin-left:10px;margin-top:10px;margin-right:10px;">
<table width="130" border="0" cellspacing="0" cellpadding="0" align="center">
<tr style="font-size:21px">
<b>病区工作查询</b>
</tr>
</table>
</div>
<div class="all">
<div class="yppp_3 floatl" style="float:left;display:inline;">
<table width="100%" height="4" border="0" cellspacing="0" cellpadding="0">
<tr height="4">
<td width="4" valign="top"><img src="img/tjysz1.gif" /></td>
<td width="100%" class="yppp_4"></td>
<td width="4" valign="top"><img src="img/tjysz3.gif" /></td>
</tr>
</table>
<div class="yppp_5">
<table width="100%" border="0" cellspacing="0" cellpadding="5" bgcolor="#E5F3F9" height="35" >
<tr align="center">
<td>入、转科</td>
</tr>
</table>
<div class="mar3">
<div id="ruyuan_grids">
<div id="ruyuan_grid" width="100%"></div>
</div>
<!-- <div id="grid_ruyuan_page" style="height:26px;margin-top:6px;"> </div> -->
</div>
</div>
<table width="100%" height="4" border="0" cellspacing="0" cellpadding="0">
<tr height="4">
<td width="4" valign="top"><img src="img/bt1.jpg" /></td>
<td width="100%" class="bgr"></td>
<td width="4" valign="top"><img src="img/bt3.jpg" /></td>
</tr>
</table>
</div>
<div class="yppp_3 yppp_6" style="float:right;display:inline;">
<table width="100%" height="4" border="0" cellspacing="0" cellpadding="0">
<tr height="4">
<td width="4" valign="top"><img src="img/tjysz1.gif" /></td>
<td width="100%" class="yppp_4"></td>
<td width="4" valign="top"><img src="img/tjysz3.gif" /></td>
</tr>
</table>
<div class="yppp_5" >
<div>
<table width="100%" border="0" cellspacing="0" cellpadding="5" bgcolor="#E5F3F9" height="35" >
<tr align="center">
<td>出院</td>
</tr>
</table>
<div class="mar3">
<div id="chuyuan_grids"><div id="chuyuan_grid" width="100%"></div></div>
<!-- <div id="grid_chuyuan_page" style="height:26px;margin-top:6px;"> </div> -->
</div>
</div>
</div>
<table width="100%" height="4" border="0" cellspacing="0" cellpadding="0">
<tr height="4">
<td width="4" valign="top"><img src="img/bt1.jpg" /></td>
<td width="100%" class="bgr"></td>
<td width="4" valign="top"><img src="img/bt3.jpg" /></td>
</tr>
</table>
</div>
<table width="940" border="0" cellspacing="0" cellpadding="0">
<tr>
</tr>
<tr>
<td width="85" align="center">原有病人数:</td>
<td width="100"><span class="font2" id ="yybrs">0</span></td>
<td width="85" align="center">入院病人数:</td>
<td width="100"><span class="font2" id ="rybrs">0</span></td>
<td width="85" align="center">由他科转入:</td>
<td width="100"><span class="font2" id ="rtkzr">0</span></td>
<td width="85" align="center">开放床位数:</td>
<td width="100"><span class="font2" id ="kfcws">0</span></td>
</tr>
<tr>
<td width="85" align="center">现有病人数:</td>
<td><span class="font2" id ="xybrs">0</span></td>
<td width="85" align="center">出院病人数:</td>
<td><span class="font2" id ="cybrs">0</span></td>
<td width="85" align="center">转出到他科:</td>
<td><span class="font2" id ="zcdtk">0</span></td>
<td width="85" align="center">床位利用率:</td>
<td><span class="font2" id ="cwlyl">0.00</span></td>
</tr>
</table>
</div>
js代码:
var avalibleHeight = $(window).height();
$("#ruyuan_grid").css("height",(avalibleHeight-280)); //调整左边grid定高
$("#chuyuan_grid").css("height",(avalibleHeight-280));//调整右边grid定高
var ruyuan_grid;
var chuyuan_grid;
$(function(){
ruyuan_grid = new dhtmlXGridObject('ruyuan_grid');
//ruyuan_grid.setStyle("","","","background-color:blue;color:white;font-weight:bold;");
ruyuan_grid.setImagePath("dhtmlxGrid/codebase/imgs/");
ruyuan_grid.setHeader("类型,床位,姓名,转入科室,转出科室,危,亡");
ruyuan_grid.setInitWidths("50,50,*,130,130,30,30");
ruyuan_grid.setColAlign("center,left,left,center,center,center,center");
ruyuan_grid.setColTypes("ro,ro,ro,ro,ro,ro,ro");
ruyuan_grid.setSkin("dhx_custom");
ruyuan_grid.init();
//ruyuan_grid.setColumnHidden(5,true);//拼音
//ruyuan_grid.setColumnHidden(6,true);//拼音
$.ajax({
type:"post",
url:"clc/stat/ruyuangrid.htm",
cache: false,
error:function(){
// alert("没有该病人记录!");
},
success: function(data){
if(data=="fail"){
alert("查询失败!");
}else{
var json = eval("{" + data + "}"); // 将后台传过来的值转化为json数组
//alert(json);
//alert(json[0]);
//alert('1'+json[0].mylist1);
var json1=json[0].mylist1; //取出mylist1的数据
var json2=json[0].mylist2; //取出mylist2的数据
//alert(json1[0].inpno);
if(json1!=null && json1!=""){
for(var i=0;i<json1.length;i++){
ruyuan_grid.addRow(i,[
'入院',
json1[i].cbedno,
json1[i].patientname,
'/',
'/',
'',
''
]);
}
}
if(json2!=null && json2!=""){
for(var i=0;i<json2.length;i++){
ruyuan_grid.addRow(i,[
'转科',
json2[i].inbed,
json2[i].patientname,
json2[i].indeptname,
json2[i].outdeptname,
'',
''
]);
}
}
// alert(json);
}
}
});
chuyuan_grid = new dhtmlXGridObject('chuyuan_grid');
//chuyuan_grid.setStyle("","","","background-color:blue;color:white;font-weight:bold;");
chuyuan_grid.setImagePath("dhtmlxGrid/codebase/imgs/");
chuyuan_grid.setHeader("类型,床位,姓名,住院天数,转出科室,危,亡");
chuyuan_grid.setInitWidths("50,50,*,130,130,30,30");
chuyuan_grid.setColAlign("center,left,left,center,center,center,center");
chuyuan_grid.setColTypes("ro,ro,ro,ro,ro,ro,ro");
chuyuan_grid.setSkin("dhx_custom");
chuyuan_grid.init();
chuyuan_grid.setColumnHidden(4,true);//拼音
//chuyuan_grid.setColumnHidden(5,true);//拼音
//chuyuan_grid.setColumnHidden(6,true);//拼音
$.ajax({
type:"post",
url:"clc/stat/chuyuangrid.htm",
cache: false,
error:function(){
// alert("没有该病人记录!");
},
success: function(data){
if(data=="fail"){
alert("查询失败!");
}else{
var json = eval("{" + data + "}"); // 将后台传过来的值转化为json数组
for(var i=0;i<json.length;i++){
chuyuan_grid.addRow(i,[
'出院',
json[i].cbedno,
json[i].patientname,
json[i].tianshu,
'/',
'',
''
]);
}
}
}
});
$.ajax({
type:"post",
url:"clc/stat/shuju.htm",
cache: false,
error:function(){
// alert("没有该病人记录!");
},
success: function(data){
if(data=="fail"){
alert("查询失败!");
}else{
var json = eval("{" + data + "}");
$("#yybrs").html(json[0].yybrs); //数据填入span
$("#rybrs").html(json[0].rybrs);
$("#ytkzr").html(json[0].ytkzr);
$("#cybrs").html(json[0].cybrs);
$("#zcdtk").html(json[0].zcdtk);
$("#xybrs").html(json[0].xybrs);
$("#kfcws").html(json[0].kfcws);
$("#cwlyl").html(json[0].cwlyl);
}
}
});
//doSearch();
});
后台java代码:
/**
* 显示病区工作查询页面 */
@RequestMapping(value = "/patWorkQuery", method = RequestMethod.GET)
public ModelAndView patWorkQuery(HttpServletRequest request, HttpServletResponse response,
ModelMap modelMap) throws Exception {
Calendar today = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
modelMap.put("today", sdf.format(today.getTime()));
return new ModelAndView("/presidentInquiry/patWorkQuery", modelMap);
}
@RequestMapping(value="/ruyuangrid",method = RequestMethod.POST)
public void ruyuangrid(HttpServletRequest request,HttpServletResponse response)throws Exception{
response.setContentType("text/html;charset=utf-8");
DBOperator db = new DBOperator();
Bas_dept basDept = (Bas_dept) request.getSession().getAttribute("login_dept");
Bas_dept ward = (Bas_dept)request.getSession().getAttribute("login_ward");
String wardnum = basDept.getDeptcode();
if(ward!=null)
{
wardnum=ward.getDeptcode();
}
PrintWriter pw = null;
Map map=new HashMap();
List<Map> mylist1=new ArrayList();
List<Map> mylist2=new ArrayList();
try
{
pw = response.getWriter();
String sql = "select * from inp_regist r where r.hosnum= ? and r.nodecode=? " +
" and r.out_date is null and r.cward= ? and to_char(r.admiss_date,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') "; //and to_char(r.admiss_date,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
mylist1 = db.find(sql,new Object[]{basDept.getHosnum(),basDept.getNodecode(),wardnum});
map.put("mylist1", mylist1); //第一段数据
sql = "select m.inbed,m.indept,m.outdept,m.indeptname,m.outdeptname,r.patientname " +
" from inp_movercd m left join inp_regist r on m.inpno=r.inpno where m.movetype='转科' " +
" and m.hosnum= ? and m.inward = ? and to_char(m.indate,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') ";//and to_char(m.indate,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
mylist2 = db.find(sql,new Object[]{basDept.getHosnum(),wardnum});
map.put("mylist2", mylist2); //第二段数据
JSONArray json = JSONArray.fromObject(map); //两段数据json到前台
pw.print(json.toString());
// System.out.println(json.toString());
db.commit();
}
catch (RuntimeException e)
{
e.printStackTrace();
db.rollback();
pw.print("fail");
}
finally
{
db.freeCon();
}
pw.flush();
pw.close();
}
@RequestMapping(value="/chuyuangrid",method = RequestMethod.POST)
public void chuyuangrid(HttpServletRequest request,HttpServletResponse response)throws Exception{
response.setContentType("text/html;charset=utf-8");
DBOperator db = new DBOperator();
Bas_dept basDept = (Bas_dept) request.getSession().getAttribute("login_dept");
Bas_dept ward = (Bas_dept)request.getSession().getAttribute("login_ward");
String wardnum = basDept.getDeptcode();
if(ward!=null)
{
wardnum=ward.getDeptcode();
}
PrintWriter pw = null;
List<Map> list=new ArrayList();
try
{
pw = response.getWriter();
String sql = "select r.*,trunc(r.out_date-r.admiss_date, 0) as tianshu from inp_regist r where r.hosnum= ? " +
" and r.nodecode= ? and r.cward= ? and to_char(r.out_date,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') "; //and to_char(r.out_date,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
list = db.find(sql,new Object[]{basDept.getHosnum(),basDept.getNodecode(),wardnum});
JSONArray json = JSONArray.fromObject(list);
pw.print(json.toString());
db.commit();
}
catch (RuntimeException e)
{
e.printStackTrace();
db.rollback();
pw.print("fail");
}
finally
{
db.freeCon();
}
pw.flush();
pw.close();
}
@RequestMapping(value="/shuju",method = RequestMethod.POST)
public void shuju(HttpServletRequest request,HttpServletResponse response)throws Exception{
response.setContentType("text/html;charset=utf-8");
DBOperator db = new DBOperator();
Bas_dept basDept = (Bas_dept) request.getSession().getAttribute("login_dept");
Bas_dept ward = (Bas_dept)request.getSession().getAttribute("login_ward");
String wardnum = basDept.getDeptcode();
if(ward!=null)
{
wardnum=ward.getDeptcode();
}
PrintWriter pw = null;
List<Map> list=new ArrayList();
Map map=new HashMap();
int yybrs,rybrs,ytkzr,kfcws,xybrs,cybrs,zcdtk,bednum;
float cwlyl;
try
{
pw = response.getWriter();
String sql = "select count(*) as yybrs from inp_regist r where to_char(r.admiss_date,'yyyy-mm-dd')<to_char(sysdate,'yyyy-mm-dd') and r.out_date is null and r.hosnum = ? and r.nodecode = ? and r.cward= ? ";
list = db.find(sql,new Object[]{basDept.getHosnum(),basDept.getNodecode(),wardnum});
yybrs = Integer.parseInt(list.get(0).get("yybrs").toString());
//原有病人数
map.put("yybrs", yybrs);
sql = "select count(*) as rybrs from inp_regist r where r.hosnum= ? and r.nodecode=? " +
" and r.out_date is null and r.cward= ? and to_char(r.admiss_date,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') ";
list = db.find(sql,new Object[]{basDept.getHosnum(),basDept.getNodecode(),wardnum});
rybrs = Integer.parseInt(list.get(0).get("rybrs").toString());
//入院病人数
map.put("rybrs", rybrs);
sql = "select count(*) as ytkzr from inp_movercd m where m.inward =? and m.outward !=? and m.movetype='转科' and m.hosnum= ? and to_char(m.indate,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') ";//and to_char(m.indate,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
list = db.find(sql,new Object[]{wardnum,wardnum,basDept.getHosnum()});
ytkzr = Integer.parseInt(list.get(0).get("ytkzr").toString());
//由他科转入
map.put("ytkzr", ytkzr);
sql = "select count(*) as cybrs from inp_regist r where r.hosnum= ? and r.nodecode= ? and r.cward= ? and to_char(r.out_date,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') "; //and to_char(r.out_date,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
list = db.find(sql,new Object[]{basDept.getHosnum(),basDept.getNodecode(),wardnum});
cybrs = Integer.parseInt(list.get(0).get("cybrs").toString());
//出院病人数
map.put("cybrs", cybrs);
sql = "select count(*) as zcdtk from inp_movercd m where m.outward =? and m.inward != ? and m.movetype='转科' and m.hosnum= ? and to_char(m.indate,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') ";//and to_char(m.indate,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
list = db.find(sql,new Object[]{wardnum,wardnum,basDept.getHosnum()});
zcdtk = Integer.parseInt(list.get(0).get("zcdtk").toString());
//转出到他科
map.put("zcdtk", zcdtk);
sql = "select count(*) as bednum from bas_beds b where b.wardno in(select r.wardid from inp_regist r where r.cward= ? and r.hosnum = ? ) and b.hosnum = ? group by b.wardno order by b.wardno ";
list = db.find(sql,new Object[]{wardnum,basDept.getHosnum(),basDept.getHosnum()});
bednum = Integer.parseInt(list.get(0).get("bednum").toString());
xybrs = yybrs +rybrs + ytkzr - cybrs - zcdtk ;
//现有病人数
map.put("xybrs", xybrs);
//kfcws = bednum - xybrs;//剩余床位数
//开放床位数
map.put("kfcws", bednum);
cwlyl = (float)xybrs*100/(float)bednum;
DecimalFormat df = new DecimalFormat("#.00");
//床位利用率
map.put("cwlyl", df.format(cwlyl)+"%");
System.out.println("xybrs----"+xybrs+"bednum-----"+bednum);
JSONArray json = JSONArray.fromObject(map);
pw.print(json.toString());
db.commit();
}
catch (RuntimeException e)
{
e.printStackTrace();
db.rollback();
pw.print("fail");
}
finally
{
db.freeCon();
}
pw.flush();
pw.close();
}
效果显示: