基于springboot和mybatis,根据年、月、日生成对应统计表格数据,x轴y轴跟随改变
1、开发思路
1、用一个类型来判断前端传入年、月、日
2、给前端4个参数、type,data1,data2,data3
3、后端用Map来存储起来
4、传入service层处理逻辑
5、用mybatis做sql处理
注:命名没有和需求统一,为了记录开发思路
2、Controller层接收对应参数
使用map来接收参数
点击查看代码
@ApiOperation("统计" + TAG_DESC)
@PostMapping("/find")
public String findBetween( @ValidEmptyParam(desc = "时间类型,1为年,2为月,3为日") String user_date_type,
@ValidEmptyParam(desc = "年,列:2021") String user_date1,
@ValidEmptyParam(desc = "年-月.列:2021-09") String user_date2,
@ValidEmptyParam(desc = "年-月-日:2021-09-17") String user_date3
) throws Exception {
Map<String ,String > map = new LinkedHashMap<String, String>();
map.put("user_date_type",user_date_type);
map.put("user_date1",user_date1);
map.put("user_date2",user_date2);
map.put("user_date3",user_date3);
return purchaseOrderService.findUserAddData(map);
}
3、service层
service层代码
点击查看代码
public String findUserAddData(Map<String, String> mapStr)throws Exception{
StringBuffer sql = new StringBuffer(); Map<String, Object> mapSQLParameter = new HashMap<String, Object>();
Map<String, Object> dataMap = new LinkedHashMap<String, Object>();
String name="";
List<String> xList=new LinkedList<String>();
List<String> yList=new LinkedList<String>();//订单量
List<String> zList=new LinkedList<String>();//订单金额
int totalnum=0;
//获取时间段
if("1".equals(mapStr.get("user_date_type"))){//年
if(!utilValiDate.isEmpty(mapStr.get("user_date1"))) {
return utilStr.return_fail("请选择年份", mapStr);
}
//分12月
name=mapStr.get("user_date1")+"年 订单信息";
// sql.append("select count(*) as num,date_format(t.create_time,'%m') date from t_purchase_order t where date_format(t.create_time,'%Y')=:date ");
// sql.append(" group by date_format(t.create_time,'%Y-%m') ");
mapSQLParameter.put("date", "2021");
mapSQLParameter.put("date1", "%m");
mapSQLParameter.put("date2", "%Y");
mapSQLParameter.put("date3", "%Y-%m");
List<Map<String, Object>> dataList = purchaseOrderDao.findSqlList(mapSQLParameter);
for (int i = 1; i <= 12; i++) {
xList.add(i+"月");
String num="0";
String sum = "0.00";
for (Map<String, Object> map : dataList) {
if(utilValiDate.isEmpty(map.get("date")) && i== utilStr.getInt(map.get("date"))){
num=map.get("num")+"";
totalnum+= utilStr.getInt(map.get("num"));
sum = map.get("sum") + " ";
}
}
yList.add(num);
zList.add(sum);
}
}else if("2".equals(mapStr.get("user_date_type"))){//月
if(!utilValiDate.isEmpty(mapStr.get("user_date2"))) {
return utilStr.return_fail("请选择月份", mapStr);
}
//分每日
name=mapStr.get("user_date2")+" 订单信息";
// sql.append("select count(*) as num,date_format(t.create_time,'%d') date from t_purchase_order t where date_format(t.create_time,'%Y-%m')=:date ");
// sql.append(" group by date_format(t.create_time,'%Y-%m-%d') ");
mapSQLParameter.put("date", mapStr.get("user_date2"));
mapSQLParameter.put("date1", "%d");
mapSQLParameter.put("date2", "%Y-%m");
mapSQLParameter.put("date3", "%Y-%m-%d");
List<Map<String, Object>> dataList = purchaseOrderDao.findSqlList(mapSQLParameter);
for (int i = 1; i <= utilTime.getDateByDay(mapStr.get("user_date2")); i++) {
xList.add(i+"号");
String num="0";
String sum = "0.00";
for (Map<String, Object> map : dataList) {
if(utilValiDate.isEmpty(map.get("date")) && i== utilStr.getInt(map.get("date"))){
num=map.get("num")+"";
totalnum+= utilStr.getInt(map.get("num"));
sum = map.get("sum") + " ";
}
}
yList.add(num);
zList.add(sum);
}
}else{//日
if(!utilValiDate.isEmpty(mapStr.get("user_date3"))) {
return utilStr.return_fail("请选择日期", mapStr);
}
//分24小时
name=mapStr.get("user_date3")+" 订单信息";
// sql.append("select count(*) as num,date_format(t.create_time,'%H') date from t_purchase_order t where date_format(t.create_time,'%Y-%m-%d')=:date ");
// sql.append(" group by date_format(t.create_time,'%Y-%m-%d %H') ");
mapSQLParameter.put("date", mapStr.get("user_date3"));
mapSQLParameter.put("date1", "%H");
mapSQLParameter.put("date2", "%Y-%m-%d");
mapSQLParameter.put("date3", "%Y-%m-%d %H");
List<Map<String, Object>> dataList = purchaseOrderDao.findSqlList(mapSQLParameter);
for (int i = 0; i < 24; i++) {
xList.add(i+"点");
String num="0";
String sum = "0.00";
for (Map<String, Object> map : dataList) {
if(utilValiDate.isEmpty(map.get("date")) && i== utilStr.getInt(map.get("date"))){
num=map.get("num")+"";
totalnum+= utilStr.getInt(map.get("num"));
sum = map.get("sum") + " ";
}
}
yList.add(num);
zList.add(sum);
}
}
dataMap.put("name", name);//名称
dataMap.put("xList", xList);//时间段
dataMap.put("yList", yList);//订单量
dataMap.put("zList", zList);//价格
dataMap.put("totalnum", totalnum+"");//总订单量
return utilStr.return_success("", dataMap);
}
4、dao层
点击查看代码
@Repository
public interface PurchaseOrderDao extends BaseDao<PurchaseOrder, Long> {
/**
*根据条件得到所有指定记录 返回值 List<Map<String, Object>>
*/
List<Map<String, Object>> findSqlList(Map<String, Object> mapSQLParameter) throws Exception;
}
5、Mapper层
使用的Mybatis框架
点击查看代码
<select id="findSqlList" parameterType="java.util.Map" resultType="java.util.Map">
select count(*) as num, date_format(t.create_time, #{date1}) date ,sum(act_pay) as sum
from t_purchase_order t
where date_format(t.create_time, #{date2})=#{date}
group by date_format(t.create_time, #{date3})
</select>
6、最终Swagger页面
小白一枚,很多不足地方,请多多指教,欢迎指点