统计报表-crm

时间格式问题

QueryObject

 // 当前页
    private int currentPage = 1;
    // 每页条数
    private int pageSize = 3;

报表QueryObject继承QueryObject

 private String keyword;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date beginDate;//开始时间
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date endDate;//结束时间
    private String groupType = "e.name";//默认按照员工姓名分组

    public Date getEndDate() { // 获取结束时间当天最晚的时候
        return DateUtil.getEndDate(endDate);
    }

DateUtil

public abstract class DateUtil {
    //获取该日期的当天最晚的时候 xxxx 23:59:59
    public static Date getEndDate(Date date) {
        if (date == null) {
            return null;
        }
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.set(Calendar.HOUR,23);
        c.set(Calendar.MINUTE,59);
        c.set(Calendar.SECOND,59);
        return c.getTime();
    }
}

MessageUtil

public abstract class MessageUtil {
    public static String changMsg(CustomerReportQueryObject qo) {
        String msg = null;
        switch (qo.getGroupType()) {
            case "DATE_FORMAT(c.input_time, '%Y')":
                msg = "年份";
                break;
            case "DATE_FORMAT(c.input_time, '%Y-%m')":
                msg = "月份";
                break;
            case "DATE_FORMAT(c.input_time, '%Y-%m-%d')":
                msg = "日期";
                break;
            /*case "sex":
                msg="性别";
                break;*/
            default:
                msg = "员工";
        }
        return msg;
    }
}

CustomerReportMapper接口

List<HashMap> selectCustomerReport(CustomerReportQueryObject qo);

    List<Map> listAll(CustomerReportQueryObject qo);

ICustomerReportService

 PageInfo selectCustomerReport(CustomerReportQueryObject qo);

    List<Map> listAll(CustomerReportQueryObject qo);

CustomerReportServiceImpl

@Autowired
    private CustomerReportMapper customerReportMapper;


    @Override
    public PageInfo selectCustomerReport(CustomerReportQueryObject qo) {

        PageHelper.startPage(qo.getCurrentPage(),qo.getPageSize());
        List<HashMap> list = customerReportMapper.selectCustomerReport(qo);
        return new PageInfo(list);
    }

    @Override
    public List<Map> listAll(CustomerReportQueryObject qo) {
        return customerReportMapper.listAll(qo);
    }

CustomerReportController
@Controller
@RequestMapping("/customerReport")
public class CustomerReportController {
@Autowired
private ICustomerReportService customerReportService;

@RequestMapping("/list")
public String list(Model model,@ModelAttribute("qo") CustomerReportQueryObject qo){
    PageInfo result = customerReportService.selectCustomerReport(qo);
    model.addAttribute("pageResult",result);
    return "customerReport/list";
}

@RequestMapping("/listByBar")
public String listByBar(Model model, @ModelAttribute("qo") CustomerReportQueryObject qo){
    //获取所有数据(不分页)
    List<Map> list = customerReportService.listAll(qo);
    //提供一个集合存储x轴的数据
    ArrayList xList = new ArrayList();
    //提供一个集合存储y轴的数据
    ArrayList yList = new ArrayList();
    //需要把数据转换为echart需要结构 x和y轴的数据要分开
    for (Map map : list) {
        xList.add(map.get("groupType"));
        yList.add(map.get("number"));
    }
    //共享到页面(freemarker不能直接显示非字符串的数据(集合,时间))
    System.out.println(JSON.toJSONString(xList));
    model.addAttribute("xList", JSON.toJSONString(xList));
    model.addAttribute("yList",JSON.toJSONString(yList));
    //分组类型转为文字显示
    model.addAttribute("groupTypeName", MessageUtil.changMsg(qo));
    return "customerReport/listByBar";
}


@RequestMapping("/listByPie")
public String listByPie(Model model, @ModelAttribute("qo") CustomerReportQueryObject qo){
    //获取所有数据(不分页)
    List<Map> list = customerReportService.listAll(qo);
    //提供一个集合存储分组类型的数据
    ArrayList groupTypeValues = new ArrayList();
    //提供一个集合存储饼图的数据
    ArrayList data = new ArrayList();
    //需要把数据转换为echart需要结构
    for (Map map : list) {
        groupTypeValues.add(map.get("groupType"));
        HashMap<String, Object> temp = new HashMap<>();
        temp.put("name",map.get("groupType"));
        temp.put("value",map.get("number"));
        data.add(temp); // {value: 335, name: '孙总'}
    }
    //共享到页面(freemarker不能直接显示非字符串的数据(集合,时间))
    model.addAttribute("groupTypeValues", JSON.toJSONString(groupTypeValues));
    model.addAttribute("data",JSON.toJSONString(data));
    model.addAttribute("groupTypeName", MessageUtil.changMsg(qo));//分组类型
    return "customerReport/listByPie";
}

}

页面list.ftl

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>潜在客户报表查询</title>
    <#include "../common/link.ftl" >
    <!--引入日期插件的样式文件-->
    <link rel="stylesheet" href="/js/plugins/bootstrap-datepicker/css/bootstrap-datepicker.min.css"/>
    <!--引入日期插件的js文件-->
    <script type="text/javascript" src="/js/plugins/bootstrap-datepicker/js/bootstrap-datepicker.min.js"></script>
    <!--引入中文国际化文件-->
    <script type="text/javascript" src="/js/plugins/bootstrap-datepicker/locales/bootstrap-datepicker.zh-CN.min.js"></script>

    <script type="text/javascript" src="/js/plugins/echarts/echarts.common.min.js"></script>

    <script>
        $(function (){
            $("#groupType").val("${qo.groupType!}");
            $("#keyword").val("${qo.keyword!}");
            $('.input-daterange').datepicker({
                language: "zh-CN",
                autoclose: true,
                todayHighlight: true,
                clearBtn: true
            });

            $(".btn-chart").click(function () {
                //清空模态框的缓存
                $('#chartModal').removeData('bs.modal');
                //获取url地址
                var url = $(this).data('url');
                //告诉模态框图形报表url是哪个,加载内容并且放到模态框
                $('#chartModal').modal({
                    remote : url + "?" + $("#searchForm").serialize() //加上高级查询的条件
                })
                $("#chartModal").modal('show');
            });
        })


    </script>
</head>
<body class="hold-transition skin-blue sidebar-mini">
<div class="wrapper">
    <#include "../common/navbar.ftl" >
    <#assign currentMenu="customerReport"/>
    <#include "../common/menu.ftl" >
    <div class="content-wrapper">
        <section class="content-header">
            <h1>潜在客户报表查询</h1>
        </section>
        <section class="content">
            <div class="box">
                <div style="margin: 10px;">
                    <!--高级查询--->
                    <form class="form-inline" id="searchForm" action="/customerReport/list.do" method="post">
                        <input type="hidden" name="currentPage" id="currentPage" value="1">
                        <div class="form-group">
                            <label for="keyword">员工姓名:</label>
                            <input type="text" class="form-control" id="keyword" name="keyword">
                        </div>
                        <div class="form-group">
                            <label>时间段查询:</label>
                        </div>
                        <div class="input-daterange input-group" id="datepicker">
                            <input type="text" class="input-sm form-control" name="beginDate"
                                   value="${(qo.beginDate?string('yyyy-MM-dd'))!}" />
                            <span class="input-group-addon">to</span>
                            <input type="text" class="input-sm form-control" name="endDate"
                                   value="${(qo.endDate?string('yyyy-MM-dd'))!}" />
                        </div>
                        <div class="form-group">
                            <label for="status">分组类型:</label>
                            <select class="form-control" id="groupType" name="groupType">
                                <option value="e.name">员工</option>
                                <option value="DATE_FORMAT(c.input_time, '%Y')">年</option>
                                <option value="DATE_FORMAT(c.input_time, '%Y-%m')">月</option>
                                <option value="DATE_FORMAT(c.input_time, '%Y-%m-%d')">日</option>
                                <option value="c.gender">性别</option>
                            </select>
                        </div>
                        <button id="btn_query" class="btn btn-primary">
                            <span class="glyphicon glyphicon-search"></span> 查询
                        </button>

                        <button type="button" class="btn btn-info btn-chart" data-url="/customerReport/listByBar.do">
                            <span class="glyphicon glyphicon-stats"></span> 柱状图
                        </button>
                        <button type="button" class="btn btn-warning btn-chart"  data-url="/customerReport/listByPie.do">
                            <span class="glyphicon glyphicon-dashboard"></span> 饼状图
                        </button>

                    </form>
                </div>
                <div class="box-body table-responsive no-padding ">
                    <table class="table table-hover table-bordered">
                        <tr>
                            <th>分组类型</th>
                            <th>潜在客户新增数</th>
                        </tr>
                        <#list pageResult.list as map>
                            <tr>
                                <td>
                                    <#if (map.groupType)?string =="1">
                                        男
                                        <#elseif (map.groupType!)?string == "0">
                                        女
                                        <#else >
                                        ${map.groupType}
                                    </#if>
                                </td>
                                <td>${map.number!}</td>
                            </tr>
                        </#list>
                    </table>
                    <#include "../common/page.ftl">
                </div>
            </div>
        </section>
    </div>
    <#include "../common/footer.ftl">
</div>

<!-- Modal模态框 -->
<div class="modal fade" id="chartModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
        </div>
    </div>
</div>
</body>
</html>

柱状图

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <!-- 引入 ECharts 文件 -->
    <script src="/js/plugins/echarts/echarts.common.min.js"></script>
</head>

<body>
<!-- 为 ECharts 准备一个具备大小（宽高）的 DOM -->
<div id="main" style="width: 600px;height:400px;"></div>
<script type="text/javascript">
    // 基于准备好的dom，初始化echarts实例
    var myChart = echarts.init(document.getElementById('main'));

    // 指定图表的配置项和数据
    var option = {
        title: {
            text: '潜在客户报表',
            subtext: "分组类型:${(groupTypeName)!}"
        },
        tooltip: {
            trigger: 'axis'
        },
        legend: {
            data: ['潜在客户数']
        },
        toolbox: {
            show: true,
            feature: {
                dataView: {show: true, readOnly: false},
                magicType: {show: true, type: ['line', 'bar']},
                restore: {show: true},
                saveAsImage: {show: true}
            }
        },
        calculable: true,
        xAxis: [
            {
                type: 'category',
                data: ${xList}
                /*['孙总','钱二明',"赵一明","王五"]*/
            }
        ],
        yAxis: [
            {
                type: 'value'
            }
        ],
        series: [
            {
                name: '潜在客户数',
                type: 'bar',
                data: ${yList},  //[8,15,30,3]
                markPoint: {
                    data: [
                        {type: 'max', name: '最大值'},
                        {type: 'min', name: '最小值'}
                    ]
                },
                markLine: {
                    data: [
                        {type: 'average', name: '平均值'}
                    ]
                }
            }
        ]
    };

    // 使用刚指定的配置项和数据显示图表。
    myChart.setOption(option);
</script>
</body>

</html>

controller

@Controller
@RequestMapping("/customerReport")
public class CustomerReportController {
    @Autowired
    private ICustomerReportService customerReportService;

    @RequestMapping("/list")
    public String list(Model model,@ModelAttribute("qo") CustomerReportQueryObject qo){
        PageInfo result = customerReportService.selectCustomerReport(qo);
        model.addAttribute("pageResult",result);
        return "customerReport/list";
    }

    @RequestMapping("/listByBar")
    public String listByBar(Model model, @ModelAttribute("qo") CustomerReportQueryObject qo){
        //获取所有数据(不分页)
        List<Map> list = customerReportService.listAll(qo);
        //提供一个集合存储x轴的数据
        ArrayList xList = new ArrayList();
        //提供一个集合存储y轴的数据
        ArrayList yList = new ArrayList();
        //需要把数据转换为echart需要结构 x和y轴的数据要分开
        for (Map map : list) {
            xList.add(map.get("groupType"));
            yList.add(map.get("number"));
        }
        //共享到页面(freemarker不能直接显示非字符串的数据(集合,时间))
        System.out.println(JSON.toJSONString(xList));
        model.addAttribute("xList", JSON.toJSONString(xList));
        model.addAttribute("yList",JSON.toJSONString(yList));
        //分组类型转为文字显示
        model.addAttribute("groupTypeName", MessageUtil.changMsg(qo));
        return "customerReport/listByBar";
    }