分类统计数据，按类别统计小计，最后再合计
前台jsp：
<table cellspacing="0" cellpadding="0" border="0" width="99%" align="center">
<tr>
<td colspan="3">
<table width="100%">
<tr>
<td width="5%" >日期：</td>
<td width="7%%" style="text-align: center;"><div style="width: 90px;height: 20px;float: left;"><input name="input12" type="text" id="s_date1" style="width: 75px;" readonly/></div>
<td width="1%" >至</td>
<td width="7%" style="text-align: center;"><div style="width: 90px;height: 20px;float: left;"><input name="input12" type="text" id="s_date2" style="width: 75px;" readonly/></div>
<td >
<div style="width: 300">
<ul class="ant floatr lingyao_5">
<li style="cursor:pointer;" onclick="doSearch();">检索</li>
<li style="cursor:pointer;" onclick="expTable();">导出</li>
<li style="cursor:pointer;display:none" onclick="doPrint();" >打印</li>
</ul>
</div>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td width="10" ><img src="img/new_yuan1.jpg" /></td>
<td background="img/new_yuan2.jpg" ></td>
<td width="10" ><img src="img/new_yuan3.jpg" /></td>
</tr>
<tr>
<td background="img/new_yuan4.jpg" > </td>
<td height="32" width="100%" >
<div id="out_div" class="dszl_10">
<div id="in_div" class="dszl_11">
<div id="headtitle" align="center"
style="margin-top: 25px; font-size: 21px; font-family: 微软雅黑">
【${hospitalname}】
</div>
<div id="headtitle" align="center"
style="margin-top: 25px; font-size: 21px; font-family: 微软雅黑">
住院病人预交金明细表
</div>
<div id="headtitle2" align="center"
style="margin-top: 5px; font-size: 19px; font-family: 微软雅黑">
</div>
<table style="margin-top: 10px;" id="table" width="95%;" border="0"
cellspacing="1" cellpadding="3" bgcolor="#000000" align="center" class="table">
</table>
<div id="tip" align="right" style="width:91%;margin-top: 25px;"><span style="float:left">制表人：${login_user.name} 制表日期：<span id="create_time"></span></span> 统计日期：<span id="check_time"></span></div>
</div>
</td>
<td background="img/new_yuan5.jpg" > </td>
</tr>
<tr>
<td><img src="img/new_yuan6.jpg" /></td>
<td background="img/new_yuan7.jpg" ></td>
<td><img src="img/new_yuan8.jpg" /></td>
</tr>
</table>
<form id="dataForm" action="query/expTable2.htm" method="post" style="display:none">
<input id="f_data" name="f_data" type="text"/>
<input id="f_title" name="f_title" type="text"/>
<input id="f_head" name="f_head" type="text"/>
</form>
js代码：
var comboTimeout;//combo定时器
var oldDeptCode;//记录原先的科室
var startDate; //开始时间
var endDate; //结束时间
var deptCode; //部门
var doctorId; //医生
var colNum; //每行的列数
var logintype;
var type;
$(function(){
//自适应高宽---start
$("#out_div").css("height",document.documentElement.clientHeight-140);
$("#in_div").css("height",document.documentElement.clientHeight-145);
//自适应高宽---end
//初始化下拉图片
var imgs=[];
for(var i=0;i<10;i++){
var z = document.createElement("img");
z.className="dhx_combo_img";
z.src ="imgs/combo_select_dhx_blue.gif";
imgs.push(z);
}
//设置查询条件---end
//初始化时间控件---start
myCalendar = new dhtmlXCalendarObject(["s_date1","s_date2"]);
myCalendar.loadUserLanguage("cn");
var myDate = new Date();
$("#create_time").text(myDate.toLocaleDateString());
//初始化时间控件---end
setAllTime();
});
/**
* 设置日期
*/
function setAllTime(){
date = new Date();
year = date.getFullYear();
month = date.getMonth()+1;
day = date.getDate();
if(month.toString().length<2){
month = "0"+month;
}
if(day.toString().length<2){
day = "0"+day;
}
$("#s_date1").val(year+"-"+month+"-"+day);
$("#s_date2").val(year+"-"+month+"-"+day);
$("#check_time").text($("#s_date1").val().toString().substring(2)+"至"+$("#s_date2").val().toString().substring(2));
}
function doTempSearch(){
showMsg2("数据获取中...");
window.setTimeout(function(){
doSearch()
},100);
}
/**
* 检索
*/
function doSearch(){
//showMsg2("数据获取中...");
startDate = $("#s_date1").val();
endDate = $("#s_date2").val();
//deptCode = $("#deptCode").val(); //部门
//doctorId = $("#doctorId").val(); //医生
//type = $("#type").val();
//logintype = $("#logintype").val();
if(endDate.replace(/-/g,"")-startDate.replace(/-/g,"")<0){
alert("结束时间不能早于开始时间");
$.unblockUI();
}
$.ajax({
async:false,
cache:false,
url:"query/getInpPrePayData.htm",
type:"post",
data:{"startDate":startDate,"endDate":endDate},
error:function(){
alert("加载数据失败!");
window.setTimeout(function(){
$.unblockUI();
},500);
},
success:function(reply){
if(reply!="false"){
jsons = eval("("+reply+")");
//alert(jsons);
createTable(jsons);
window.setTimeout(function(){
$.unblockUI();
},500);
// alert(2222);
}else{
alert("加载数据失败!");
window.setTimeout(function(){
$.unblockUI();
},500);
}
}
});
$("#check_time").text($("#s_date1").val().toString().substring(2)+"至"+$("#s_date2").val().toString().substring(2));
}
//创建数据表
function createTable(jsons){
var xjhj = 0;
var xjxj = 0;
var xjyhk = 0;
var heji = 0;
var hejixj = 0;
var hejiyhk = 0;
var operator = '';
$("#table tr").remove();
//$("#table").append("<tr id='title' bgcolor='#FFFFFF'><td id='0' align='center' width = '50px;'>开单科室</td><td id='1' align='center' width='50px;' toleft=''>开单医生</td><td id='2' align='center' style='width:50px;display:none;'>药品类型</td><td id='3' align='center' style='min-width:60px;'>药品名称、规格</td><td id='4' align='center' style='min-width:50px;' toleft=''>产地名称</td><td id='5' align='center' style='width:50px;' toleft=''>数量</td><td id='6' align='center' style='width:50px;'>进价</td><td id='7' align='center' style='width:50px;' toleft=''>进价金额</td><td id='8' align='center' width='50px'>零售价</td><td id='9' align='center' style='width:50px;'>零售金额</td><td id='10' align='center' style='width:50px;' toleft=''>利润金额</td></tr>");
$("#table").append("<tr id='title' rowspan='2' bgcolor='#FFFFFF'><td id='0' rowspan='2' align='center' width = '12%'>收费员</td><td id='1' rowspan='2' align='center' width='16%' toleft=''>收款时间</td><td id='3' rowspan='2' align='center' width = '12%'>病案号</td><td id='4' rowspan='2' align='center' width = '12%' toleft=''>姓名</td><td id='7' align='center' colspan='3' width = '36%'toleft=''>支付方式</td><td id='8' rowspan='2' align='center' width = '12%'>类型</td><td id='2' align='center' style='width:50px;display:none;'>药品类型</td><td id='2' align='center' style='width:50px;display:none;'>药品类型</td></tr>");
$("#table").append("<tr id='title' bgcolor='#FFFFFF'><td id='5' align='center' width = '12%' toleft=''>合计</td><td id='6' align='center' width = '12%'>现金</td><td id='6' align='center' width = '12%'>银行卡</td></tr>");
for(var i=0;i<jsons.length;i++){
if(jsons[i].operator!=operator&&i!=0){
$("#table").append("<tr bgcolor='#FFFFFF'><td id='"+i+"_0' align='center' width = '12%' >小计</td><td id='"+i+"_1' align='center' width = '12%' toleft=''></td><td id='"+i+"_2' align='center' width = '12%' ></td><td id='"+i+"_3' align='center' width = '12%' ></td><td id='"+i+"_4' align='right' width = '12%' toleft=''>"+xjhj+"</td><td id='"+i+"_5' align='right' width = '12%' toleft=''>"+xjxj+"</td><td id='"+i+"_6' align='right' width = '12%' >"+xjyhk+"</td><td id='"+i+"_7' align='center' width = '12%' toleft=''></td></tr>");
xjhj = 0;
xjxj = 0;
xjyhk = 0;
}
xjhj += (jsons[i].xjamt+jsons[i].yhkamt);
xjxj += jsons[i].xjamt;
xjyhk += jsons[i].yhkamt;
heji += (jsons[i].xjamt+jsons[i].yhkamt);
hejixj += jsons[i].xjamt;
hejiyhk += jsons[i].yhkamt;
operator = jsons[i].operator;
//$("#table").append("<tr bgcolor='#FFFFFF'><td id='"+i+"_0' align='center' width = '50px;'>"+jsons[i].deptname+"</td><td id='"+i+"_1' align='center' width='50px;' toleft=''>"+jsons[i].doctorname+"</td><td id='"+i+"_2' align='center' style='width:50px;display:none;'>"+jsons[i].ordertype+"</td><td id='"+i+"_3' align='left' style='min-width:60px;'>"+jsons[i].medname+"</td><td id='"+i+"_4' align='center' style='min-width:50px;' toleft=''>"+jsons[i].mafname+"</td><td id='"+i+"_5' align='right' style='width:50px;' toleft=''>"+jsons[i].qty+"</td><td id='"+i+"_6' align='right' style='width:50px;'>"+jsons[i].pprice+"</td><td id='"+i+"_7' align='right' style='width:50px;' toleft=''>"+jsons[i].sumpprice+"</td><td id='"+i+"_8' align='right' width='50px'>"+jsons[i].sprice+"</td><td id='"+i+"_9' align='right' style='min-width:50px;'>"+jsons[i].sumsprice+"</td><td id='"+i+"_10' align='right' style='min-width:50px;' toleft=''>"+(jsons[i].sumsprice-jsons[i].sumpprice).toFixed(2)+"</td></tr>");
//$("#table").append("<tr bgcolor='#FFFFFF'><td id='"+i+"_0' align='center' width = '50px;'>"+jsons[i].deptname+"</td><td id='"+i+"_1' align='center' width='50px;' toleft=''>"+jsons[i].doctorname+"</td><td id='"+i+"_2' align='center' style='width:50px;display:none;'>"+jsons[i].ordertype+"</td><td id='"+i+"_3' align='left' style='min-width:60px;'>"+jsons[i].medname+"</td><td id='"+i+"_4' align='center' style='min-width:50px;' toleft=''>"+jsons[i].mafname+"</td><td id='"+i+"_5' align='right' style='width:50px;' toleft=''>"+jsons[i].qty+"</td><td id='"+i+"_6' align='right' style='width:50px;'>"+jsons[i].pprice+"</td><td id='"+i+"_7' align='right' style='width:50px;' toleft=''>"+jsons[i].sumpprice+"</td><td id='"+i+"_8' align='right' width='50px'>"+jsons[i].sprice+"</td></tr>");
$("#table").append("<tr bgcolor='#FFFFFF'><td id='"+i+"_0' align='center' width = '12%' >"+jsons[i].operator+"</td><td id='"+i+"_1' align='center' width = '12%' toleft=''>"+jsons[i].prepaydate+"</td><td id='"+i+"_2' align='center' width = '12%' >"+jsons[i].medrcdno+"</td><td id='"+i+"_3' align='center' width = '12%' >"+jsons[i].patientname+"</td><td id='"+i+"_4' align='right' width = '12%' toleft=''>"+(jsons[i].xjamt+jsons[i].yhkamt)+"</td><td id='"+i+"_5' align='right' width = '12%' toleft=''>"+jsons[i].xjamt+"</td><td id='"+i+"_6' align='right' width = '12%' >"+jsons[i].yhkamt+"</td><td id='"+i+"_7' align='center' width = '12%' toleft=''>"+jsons[i].type+"</td></tr>");
if(i==jsons.length-1){
$("#table").append("<tr bgcolor='#FFFFFF'><td id='"+i+"_0' align='center' width = '12%' >小计</td><td id='"+i+"_1' align='center' width = '12%' toleft=''></td><td id='"+i+"_2' align='center' width = '12%' ></td><td id='"+i+"_3' align='center' width = '12%' ></td><td id='"+i+"_4' align='right' width = '12%' toleft=''>"+xjhj+"</td><td id='"+i+"_5' align='right' width = '12%' toleft=''>"+xjxj+"</td><td id='"+i+"_6' align='right' width = '12%' >"+xjyhk+"</td><td id='"+i+"_7' align='center' width = '12%' toleft=''></td></tr>");
xjhj = 0;
xjxj = 0;
xjyhk = 0;
}
}
//$("#table").append("<tr bgcolor='#FFFFFF'><td id='all_0' align='center' colspan='2' width = '50px;'>合计</td><td id='all_3' align='center' style='min-width:60px;'> </td><td id='all_4' align='center' style='min-width:50px;' toleft=''> </td><td id='all_5' align='right' style='width:50px;' toleft=''>"+qtyall+"</td><td id='all_6' align='right' style='width:50px;'>"+ppriceall.toFixed(2)+"</td><td id='all_7' align='right' style='width:50px;' toleft=''>"+sumppriceall.toFixed(2)+"</td><td id='all_8' align='right' width='50px'>"+spriceall.toFixed(2)+"</td><td id='all_9' align='right' style='min-width:50px;'>"+sumspriceall.toFixed(2)+"</td><td id='all_10' align='right' style='min-width:50px;' toleft=''>"+(sumspriceall-sumppriceall).toFixed(2)+"</td></tr>");
$("#table").append("<tr bgcolor='#FFFFFF'><td id='"+i+"_0' align='center' width = '12%' >合计</td><td id='"+i+"_1' align='center' width = '12%' toleft=''></td><td id='"+i+"_2' align='center' width = '12%' ></td><td id='"+i+"_3' align='center' width = '12%' ></td><td id='"+i+"_4' align='right' width = '12%' toleft=''>"+heji+"</td><td id='"+i+"_5' align='right' width = '12%' toleft=''>"+hejixj+"</td><td id='"+i+"_6' align='right' width = '12%' >"+hejiyhk+"</td><td id='"+i+"_7' align='center' width = '12%' toleft=''></td></tr>");
//添加序号列
/*$("#table tr td[id='0'],[id*='_0']").each(function(ind,ele){
if(ind==0){
$(this).before("<td align='center' width = '20px;'>序号</td>");
}else{
$(this).before("<td align='center' width = '20px;'>"+ind+"</td>");
}
}); */
}
/**
* 导出表
*/
function expTable(){
colNum = 0; //重置colNum
var excelTitle = "";
var excelData = "";
$("#title td").each(function(ind,ele){
colNum = colNum+1;
});
$("#title td").each(function(){
excelTitle+=$(this).text()+"~";
});
$("#table tr:not(:first,:eq(1)) td").each(function(ind,ele){//jquery操作表格
if((ind+1)%colNum!=0){
excelData+=($(this).text()==""?" ":$(this).text())+"~"
}else{
excelData+=($(this).text()==""?" ":$(this).text())+"@"
}
});
$("#f_title").val(excelTitle);
$("#f_data").val(excelData);
$("#f_head").val($("#headtitle").text());
$("#dataForm")[0].submit();
}
后台java代码：
//住院病人预交金明细
@RequestMapping(value = "/InpPrePay", method = RequestMethod.GET)
public String InpPrePay(HttpServletRequest request,
HttpServletResponse response) {
response.setContentType("text/html;charset=utf-8");
Bas_hospitals bas_hospital = (Bas_hospitals)request.getSession().getAttribute("login_hospital");
request.setAttribute("hospitalname", bas_hospital.getHosname());
request.setAttribute("logintype", request.getParameter("logintype"));
return "query/InpPrePay";
}
//查询预交金数据
@RequestMapping(value = "/getInpPrePayData", method = RequestMethod.POST)
private void getInpPrePayData(HttpServletRequest request,
HttpServletResponse response,String logintype) {
response.setContentType("text/html;charset=utf-8");
String startDate = request.getParameter("startDate");
String endDate = request.getParameter("endDate");
// String deptCode = request.getParameter("deptCode");
// String doctorId = request.getParameter("doctorId");
// String type = request.getParameter("type");
Bas_hospitals bas_hospitals = (Bas_hospitals)request.getSession().getAttribute("login_hospital");
Bas_dept bas_dept = (Bas_dept)request.getSession().getAttribute("login_dept");
Bas_user bas_user = (Bas_user)request.getSession().getAttribute("login_user");
DBOperator db = null;
String sql = "";
// String sql1 = "";
// String sql2 = "";
PrintWriter pw = null;
String result = "";
boolean flag = false;
List<Map> list = new ArrayList();
try {
pw = response.getWriter();
db = new DBOperator();
sql = "select p.operator operator,to_char(p.prepaydate,'yyyy-mm-dd') prepaydate,i.medrcdno medrcdno,i.patientname patientname, " +
" case when p.prepaytype='现金' then p.amt else 0 end as xjamt, " +
" case when p.prepaytype='银行卡' then p.amt else 0 end as yhkamt, " +
" '收押金' as type from pat_prepay p left join inp_regist i on p.inpno=i.inpno " +
" where p.hosnum = ? ";
if(startDate!=null&&!"".equals(startDate)){
sql += " and to_char(p.prepaydate,'yyyy-mm-dd')>='" + startDate +"'";
}
if(endDate!=null&&!"".equals(endDate)){
sql += " and to_char(p.prepaydate,'yyyy-mm-dd')<='" + endDate +"'";
}
sql += " order by p.operator,p.prepaydate,i.medrcdno,i.patientname ";
// list = db.find(sql);
list = db.find(sql, new Object[] {bas_dept.getHosnum()});
JSONArray jsons = JSONArray.fromObject(list);
pw.print(jsons.toString());
// System.out.println(jsons.toString());
} catch (Exception e) {
pw.print("false");
db.rollback();
e.printStackTrace();
} finally {
pw.flush();
pw.close();
db.freeCon();
}
}
/**
* 导出Excel
*
* @param request
* @param response
* @param expList
*/
@RequestMapping(value = "/expTable2", method = RequestMethod.POST)
private void expTable2(HttpServletRequest request,
HttpServletResponse response) {
String title = request.getParameter("f_title");
String data = request.getParameter("f_data");
String head = request.getParameter("f_head");
try {
String[] titleAry = title.split("~");
String[] rowAry = data.split("@");
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for (int i = 0; i < rowAry.length; i++) {
String[] dataAry = rowAry[i].split("~");
Map<String, Object> map = new LinkedHashMap<String, Object>();
for (int j = 0; j < dataAry.length; j++) {
map.put("a" + Integer.toString(j), dataAry[j]);
}
list.add(map);
}
response.setContentType("application/x-msdownload;charset=gbk");
response.setCharacterEncoding("UTF-8");
String dateStr = DateUtil.dateToString(new Date(), "yyyyMMdd");
String fileName = head.substring(0,head.length()-1) + "-" + dateStr + ".xls";
String fileNameTemp = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-Disposition", "attachment; filename="
+ new String(fileNameTemp.getBytes("utf-8"), "gbk"));
OutputStream os = response.getOutputStream();
ExcelUtils eu = new ExcelUtils();
eu.export(os, head, new String[]{"收费员","收款时间","病案号","姓名","现金银行卡合计","现金","银行卡","类型"}, new int[] { 12, 18, 12, 12, 12, 12, 12,
12 }, DbUtils.ListMapToListObject(list));
// eu.export(os, head, titleAry, new int[] { 10, 15, 15, 10, 10, 15, 35,
// 15, 50, 15, 20, 25 }, DbUtils.ListMapToListObject(list));
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
效果显示：
