1、今日完成任务:
(1)完成查询统计-结算明细模块的代码书写及测试工作
(2)完成查询统计-车补贴查询模块的代码书写及测试工作
(3)完成查询统计-单车查询模块的代码书写及测试工作
2、核心源码:
@RequestMapping("/queryAllSubsidy")
@ResponseBody
public List<Map<String,Object>> queryAllSubsidy(Integer driverid,String startdate,String enddate){
return ds.querySubsidy(driverid,startdate,enddate);
}
@RequestMapping("/exportSubsidy")
@ResponseBody
public void exportSubsidy(Integer driverid,String startdate,String enddate,HttpServletResponse resp){
try {
List<Map<String, Object>> list = ds.querySubsidy(driverid,startdate,enddate);
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表
HSSFSheet sheet = workbook.createSheet("车补贴统计");
// 在索引0的位置创建行(最顶端的行)
HSSFRow row = sheet.createRow(0);
//表头 创建单元格并输入内容
row.createCell(0).setCellValue("驾驶员");
row.createCell(1).setCellValue("开始日期");
row.createCell(2).setCellValue("截止日期");
row.createCell(3).setCellValue("车补贴");
//表格内容
for (Map<String, Object> map : list) {
HSSFRow row1 = sheet.createRow(sheet.getLastRowNum()+1);
row1.createCell(0).setCellValue((String)map.get("drivername"));
row1.createCell(1).setCellValue(startdate);
row1.createCell(2).setCellValue(enddate);
row1.createCell(3).setCellValue((Double)map.get("subsidys"));
}
//设置响应类型及头信息
resp.setContentType("application/vnd.ms-excel");
String filename = URLEncoder.encode("车补贴统计.xls", "UTF-8");
if(!StringUtils.isEmpty(driverid)){
filename = URLEncoder.encode("驾驶员"+list.get(0).get("drivername")+URLDecoder.decode(filename, "UTF-8"), "UTF-8");
}
if(!StringUtils.isEmpty(startdate)&&!StringUtils.isEmpty(enddate)){
filename = URLEncoder.encode(startdate+"到"+enddate+URLDecoder.decode(filename, "UTF-8"), "UTF-8");
}
resp.setHeader("Content-Disposition", "attachment;filename="+filename);
//导出
workbook.write(resp.getOutputStream());
//关闭
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//查询单车所有派车单与月结算单
@RequestMapping("/queryOnecar")
@ResponseBody
public List<Dispatch> queryOnecar(Integer carid,String carnumber){
try {
List<Dispatch> list = ds.queryOne(carid,carnumber);
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
@RequestMapping("/exportOnecar")
@ResponseBody
public void exportOnecar(Integer carid,String carnumber,HttpServletResponse resp){
try {
List<Dispatch> list = ds.queryOne(carid,carnumber);
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表
HSSFSheet sheet = workbook.createSheet("单车所有派车单与结算明细");
// 在索引0的位置创建行(最顶端的行)
HSSFRow row = sheet.createRow(0);
//表头 创建单元格并输入内容
row.createCell(0).setCellValue("派车单号");
row.createCell(1).setCellValue("车牌号码");
row.createCell(2).setCellValue("出车日期");
row.createCell(3).setCellValue("用车单位");
row.createCell(4).setCellValue("业务员");
row.createCell(5).setCellValue("驾驶员");
row.createCell(6).setCellValue("租车费");
row.createCell(7).setCellValue("折扣金额");
row.createCell(8).setCellValue("实际金额");
row.createCell(9).setCellValue("结算年份");
row.createCell(10).setCellValue("结算月份");
row.createCell(11).setCellValue("工资");
row.createCell(12).setCellValue("GPS费用");
row.createCell(13).setCellValue("保险费");
row.createCell(14).setCellValue("还贷");
row.createCell(15).setCellValue("分红");
row.createCell(16).setCellValue("总共油费");
row.createCell(17).setCellValue("总过路费");
row.createCell(18).setCellValue("总停车费");
row.createCell(19).setCellValue("总修理费");
row.createCell(20).setCellValue("总轮胎费");
row.createCell(21).setCellValue("总车补贴");
row.createCell(22).setCellValue("总行驶公里数");
row.createCell(23).setCellValue("总营业额");
row.createCell(24).setCellValue("费用总计");
row.createCell(25).setCellValue("毛利润");
//表格内容
for (Dispatch dispatch : list) {
HSSFRow row1 = sheet.createRow(sheet.getLastRowNum()+1);
row1.createCell(0).setCellValue(dispatch.getId());
row1.createCell(1).setCellValue(dispatch.getCar().getCarnumber());
row1.createCell(2).setCellValue(dispatch.getSenddate());
row1.createCell(3).setCellValue(dispatch.getCustomer().getCompany());
row1.createCell(4).setCellValue(dispatch.getClerk().getName());
row1.createCell(5).setCellValue(dispatch.getDriver().getName());
row1.createCell(6).setCellValue(dispatch.getRentmoney());
row1.createCell(7).setCellValue(dispatch.getDiscount());
row1.createCell(8).setCellValue(dispatch.getRealmoney());
row1.createCell(9).setCellValue(dispatch.getCount().getCountyear());
row1.createCell(10).setCellValue(dispatch.getCount().getCountmonth());
row1.createCell(11).setCellValue(dispatch.getCount().getSalary());
row1.createCell(12).setCellValue(dispatch.getCount().getGpsmoney());
row1.createCell(13).setCellValue(dispatch.getCount().getPremium());
row1.createCell(14).setCellValue(dispatch.getCount().getLoan());
row1.createCell(15).setCellValue(dispatch.getCount().getDividend());
row1.createCell(16).setCellValue(dispatch.getCount().getTotalgas());
row1.createCell(17).setCellValue(dispatch.getCount().getTotaltoll());
row1.createCell(18).setCellValue(dispatch.getCount().getTotalparking());
row1.createCell(19).setCellValue(dispatch.getCount().getTotalrepair());
row1.createCell(20).setCellValue(dispatch.getCount().getTotaltire());
row1.createCell(21).setCellValue(dispatch.getCount().getTotalsubsidy());
row1.createCell(22).setCellValue(dispatch.getCount().getTotalkilometer());
row1.createCell(23).setCellValue(dispatch.getCount().getTurnover());
row1.createCell(24).setCellValue(dispatch.getCount().getTotalcost());
row1.createCell(25).setCellValue(dispatch.getCount().getProfit());
}
//设置响应类型及头信息
resp.setContentType("application/vnd.ms-excel");
String filename = "";
if(StringUtils.isEmpty(carid) || StringUtils.isEmpty(carnumber)){
filename = URLEncoder.encode("所有车辆派车单与结算单明细.xls", "UTF-8");
}else{
filename = URLEncoder.encode(carnumber+"车辆派车单与结算单明细.xls", "UTF-8");
}
resp.setHeader("Content-Disposition", "attachment;filename="+filename);
//导出
workbook.write(resp.getOutputStream());
//关闭
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,innitial-scale=1">
<title>单车查询</title>
<link rel="stylesheet" type="text/css" href="../../js/easyui/themes/default/easyui.css"/>
<link rel="stylesheet" type="text/css" href="../../js/easyui/themes/icon.css"/>
<link rel="stylesheet" type="text/css" href="../../js/easyui/ext/portal.css"/>
<link rel="stylesheet" type="text/css" href="../../css/default.css"/>
<script src="../../js/jquery-1.8.3.js" type="text/javascript" charset="utf-8"></script>
<script src="../../js/easyui/jquery.easyui.min.js" type="text/javascript" charset="utf-8"></script>
<script src="../../js/easyui/ext/jquery.portal.js" type="text/javascript" charset="utf-8"></script>
<script src="../../js/easyui/locale/easyui-lang-zh_CN.js" type="text/javascript" charset="utf-8"></script>
<script type="text/javascript">
$(function(){
$('#dg').datagrid({
toolbar: [{
text:'车牌号:<input id="carSelect"/>'
},'-',{
text:'查询',
iconCls: 'icon-search',
handler:function(){
var carid = $("#carSelect").combobox('getValue');
var carnumber = $("#carSelect").combobox('getText');
if(carid==null || carid==''){
$.messager.alert('提示信息','请选择要查询的车牌号!','warning');
}else{
$('#dg').datagrid('load',{
carid: carid,
carnumber: carnumber
});
}
}
},'-',{
id: 'export',
text:'导出',
iconCls: 'icon-print',
handler:function(){
$("#carid").val($("#carSelect").combobox('getValue'));
$("#carnumber").val($("#carSelect").combobox('getText'));
$("#ff").attr('action','../../exportOnecar');
$("#ff").submit();
}
}]
});
$("#carSelect").combobox({
url:'../../queryAllCar',
valueField:'id',
textField:'carnumber'
});
});
</script>
</head>
<body>
<form action="" id="ff" method="post">
<input type="hidden" id="carid" name="carid"/>
<input type="hidden" id="carnumber" name="carnumber"/>
</form>
<table id="dg" class="easyui-datagrid"
data-options="url:'../../queryOnecar',fitColumns:true,rownumbers:true">
<thead>
<tr>
<th data-options="field:'id',width:70,align:'center'">派车单号</th>
<th data-options="field:'car.carnumber',width:80,align:'center',formatter: function(value,row,index){return row.car.carnumber;}">车牌号码</th>
<th data-options="field:'senddate',width:100,align:'center'">出车日期</th>
<th data-options="field:'customer.company',width:100,align:'center',formatter: function(value,row,index){return row.customer.company;}">用车单位</th>
<th data-options="field:'clerk.name',width:70,align:'center',formatter: function(value,row,index){return row.clerk.name;}">业务员</th>
<th data-options="field:'driver.name',width:70,align:'center',formatter: function(value,row,index){return row.driver.name;}">驾驶员</th>
<th data-options="field:'rentmoney',width:70,align:'center'">租车费</th>
<th data-options="field:'discount',width:70,align:'center'">折扣金额</th>
<th data-options="field:'realmoney',width:70,align:'center'">实际金额</th>
<th data-options="field:'count.countyear',width:80,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.countyear;}">结算年份</th>
<th data-options="field:'count.countmonth',width:80,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.countmonth;}">结算月份</th>
<th data-options="field:'count.salary',width:70,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.salary;}">工资</th>
<th data-options="field:'count.gpsmoney',width:70,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.gpsmoney;}">GPS费用</th>
<th data-options="field:'count.premium',width:70,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.premium;}">保险费</th>
<th data-options="field:'count.loan',width:50,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.loan;}">还贷</th>
<th data-options="field:'count.dividend',width:50,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.dividend;}">分红</th>
<th data-options="field:'count.totalgas',width:70,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.totalgas;}">总油费</th>
<th data-options="field:'count.totaltoll',width:70,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.totaltoll;}">总过路费</th>
<th data-options="field:'count.totalparking',width:70,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.totalparking;}">总停车费</th>
<th data-options="field:'count.totalrepair',width:70,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.totalrepair;}">总修理费</th>
<th data-options="field:'count.totaltire',width:70,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.totaltire;}">总轮胎费</th>
<th data-options="field:'count.totalsubsidy',width:70,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.totalsubsidy;}">总车补贴</th>
<th data-options="field:'count.totalkilometer',width:90,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.totalkilometer;}">总行驶公里数</th>
<th data-options="field:'count.turnover',width:80,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.turnover;}">总营业额</th>
<th data-options="field:'count.totalcost',width:80,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.totalcost;}">费用总计</th>
<th data-options="field:'count.profit',width:70,align:'center',formatter: function(value,row,index){return row.count==null?'':row.count.profit;}">毛利润</th>
</tr>
</thead>
</table>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,innitial-scale=1">
<title>结算明细</title>
<link rel="stylesheet" type="text/css" href="../../js/easyui/themes/default/easyui.css"/>
<link rel="stylesheet" type="text/css" href="../../js/easyui/themes/icon.css"/>
<link rel="stylesheet" type="text/css" href="../../js/easyui/ext/portal.css"/>
<link rel="stylesheet" type="text/css" href="../../css/default.css"/>
<script src="../../js/jquery-1.8.3.js" type="text/javascript" charset="utf-8"></script>
<script src="../../js/easyui/jquery.easyui.min.js" type="text/javascript" charset="utf-8"></script>
<script src="../../js/easyui/ext/jquery.portal.js" type="text/javascript" charset="utf-8"></script>
<script src="../../js/easyui/locale/easyui-lang-zh_CN.js" type="text/javascript" charset="utf-8"></script>
<script type="text/javascript">
$(function(){
$('#dg').datagrid({
toolbar: [{
text:'结算年份:<input id="year"/>'
},'-',{
text:'结算月份:<input id="month"/>'
},'-',{
text:'查询',
iconCls: 'icon-search',
handler:function(){
var year = $("#year").combobox('getText');
var month = $("#month").combobox('getText');
if(year==null || year==''){
$.messager.alert('提示信息','请选择要查询的结算年份!','warning');
}else if(month==null || month==''){
$.messager.alert('提示信息','请选择要查询的结算月份!','warning');
}else{
$('#dg').datagrid('load',{
year: year,
month: month
});
}
}
},'-',{
id: 'export',
text:'导出',
iconCls: 'icon-print',
handler:function(){
$("#ffyear").val($("#year").combobox('getText'));
$("#ffmonth").val($("#month").combobox('getText'));
$("#ff").attr('action','../../exportAccounts');
$("#ff").submit();
}
}]
});
var years = [{id:new Date().getFullYear()-2},{id:new Date().getFullYear()-1},{id:new Date().getFullYear()}];
$("#year").combobox({
data:years,
valueField:'id',
textField:'id',
onSelect:function(val){
var months = [];
var currMonth = 12;
if(val.id==new Date().getFullYear()){
currMonth = new Date().getMonth()+1;
}
for(var i=0;i<currMonth;i++){
var monthValue = {};
monthValue.id = "0"+(i+1);
months.push(monthValue);
}
$("#month").combobox('loadData',months);
}
});
$("#month").combobox({
valueField:'id',
textField:'id'
});
});
</script>
</head>
<body>
<form action="" id="ff" method="post">
<input type="hidden" id="ffyear" name="year"/>
<input type="hidden" id="ffmonth" name="month"/>
</form>
<table id="dg" class="easyui-datagrid"
data-options="url:'../../queryAllCount',fitColumns:true,rownumbers:true">
<thead>
<tr>
<th data-options="field:'id',width:70,align:'center',checkbox:true">月结算编号</th>
<th data-options="field:'carnumber',width:100,align:'center'">车牌号码</th>
<th data-options="field:'countyear',width:80,align:'center'">结算年份</th>
<th data-options="field:'countmonth',width:80,align:'center'">结算月份</th>
<th data-options="field:'salary',width:70,align:'center'">工资</th>
<th data-options="field:'gpsmoney',width:70,align:'center'">GPS费用</th>
<th data-options="field:'premium',width:70,align:'center'">保险费</th>
<th data-options="field:'loan',width:70,align:'center'">还贷</th>
<th data-options="field:'dividend',width:70,align:'center'">分红</th>
<th data-options="field:'totalgas',width:70,align:'center'">总油费</th>
<th data-options="field:'totaltoll',width:70,align:'center'">总过路费</th>
<th data-options="field:'totalparking',width:70,align:'center'">总停车费</th>
<th data-options="field:'totalrepair',width:70,align:'center'">总修理费</th>
<th data-options="field:'totaltire',width:70,align:'center'">总轮胎费</th>
<th data-options="field:'totalsubsidy',width:70,align:'center'">总车补贴</th>
<th data-options="field:'totalkilometer',width:90,align:'center'">总行驶公里数</th>
<th data-options="field:'turnover',width:80,align:'center'">总营业额</th>
<th data-options="field:'totalcost',width:80,align:'center'">费用总计</th>
<th data-options="field:'profit',width:70,align:'center'">毛利润</th>
<th data-options="field:'remark',width:80,align:'center'">备注</th>
</tr>
</thead>
</table>
</body>
</html>
3、遇到的问题:
(1)单车查询要将查询出的派车单信息和结算信息一起显示
4、解决的方法:
(1)在业务方法中使用查询出来将结算信息放入派车单集合中
@Override
public List<Dispatch> queryOne(Integer carid, String carnumber) throws Exception {
List<Dispatch> dispatchs = dm.selectByExample(carid, null, null,null,null,null,null,null);
List<Count> counts = cm.selectByExample(null, null, carnumber);
for (Dispatch dispatch : dispatchs) {
String senddate = dispatch.getSenddate();
Car car = dispatch.getCar();
for (Count count : counts) {
String yearmonth = count.getCountyear()+"-"+count.getCountmonth();
if(car.getCarnumber().equals(count.getCarnumber())&&senddate.startsWith(yearmonth)){
dispatch.setCount(count);
}
}
}
return dispatchs;
}
5、项目燃尽图更新: