selleck --手机端-- 销售打卡记录下载

1. check-in-log.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags" %>
<html lang="cmn">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="shortcut icon" href="<c:url value="/favicon.ico"/>">
    <title>Excel</title>
    <%@include file="include-libs.jsp"%>
</head>
<body>
<jsp:include page="header.jsp">
    <jsp:param name="path" value="check-in-log"/>
</jsp:include>
<div id="url"
    data-download="<spring:url value="/admin/mobile/check-in-log/excel"/>">
</div>
<div class="container">
    <form class="form-horizontal">
        <div class="form-group">
            <p class="col-md-1 control-label">Date</p>
            <div class="col-md-2">
                <input class="form-control" id="dateFrom" data-bind="value: dateFrom" readonly>
            </div>
            <div class="col-md-2">
                <input class="form-control" id="dateTo" data-bind="value: dateTo" readonly>
            </div>
            <div class="col-md-3">
                <button type="button" class="btn btn-primary" data-bind="click: download">Download</button>
            </div>
        </div>
        <div class="form-group">
        </div>
    </form>
</div>
<script src="<c:url value="/resources/app/wechat/excel.min.js"/>"></script>
</body>
</html>

2. MobileAdminController.groovy

    @RequestMapping('check-in-log/excel')
    def downloadExcel(String dateFrom, String dateTo, HttpServletResponse response) {
        def dateFormat = new SimpleDateFormat('yyyy-M-d')
        def dateDateFrom = null
        def dateDateTo = null
        if (StringUtils.isNotBlank(dateFrom)) {
            dateDateFrom = dateFormat.parse(dateFrom)
        }
        if (StringUtils.isNotBlank(dateTo)) {
            dateDateTo = dateFormat.parse(dateTo)
        }
        def list = mobileService.listCheckInLog(dateDateFrom, dateDateTo)
        def workbook = new XSSFWorkbook()
        def sheet = workbook.createSheet()
        def row = sheet.createRow(0)
        def c = 0
        row.createCell(c++).cellValue = 'username'
        row.createCell(c++).cellValue = 'display_name'
        row.createCell(c++).cellValue = 'date'
        row.createCell(c++).cellValue = 'check_in'
        row.createCell(c++).cellValue = 'check_out'
        for (int i = 0; i < list.size(); i++) {
            Object[] val = list.get(i)
            row = sheet.createRow(i + 1)
            c = 0
            row.createCell(c++).setCellValue(val[0].toString())
            row.createCell(c++).setCellValue((String) val[1])
            row.createCell(c++).setCellValue((String) val[2])
            row.createCell(c++).setCellValue((String) val[3])
            row.createCell(c++).setCellValue((String) val[4])
        }
        response.setHeader('Content-Disposition', 'attachment;filename=check-in-log_' + dateFrom + '_' + dateTo + '.xlsx')
        response.setContentType('application/vnd.ms-excel')
        def buffer = new ByteArrayOutputStream()
        workbook.write(buffer)
        def bytes = buffer.toByteArray()
        response.setContentLength(bytes.length)
        response.getOutputStream().write(bytes)
    }

3. MobileServiceImpl

    public List<Object[]> listCheckInLog(Date dateFrom, Date dateTo) {
        Map<String, Object> params = new HashMap<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select u.username, u.display_name, date_format(c.created_at, '%Y-%m-%d') date")
                .append("
 , date_format(min(c.created_at), '%H:%i:%S') check_in")
                .append("
 , date_format(max(c.created_at), '%H:%i:%S') check_out")
                .append("
 from user u")
                .append("
 join mobile_check_in_image c on c.user=u.id")
                .append("
 where ifnull(view_english,0)=0 and ifnull(if_resign,0)=0 and role='sales' and ifnull(locked,0)=0");
        if (dateFrom != null) {
            sql.append("
 and c.created_at >= :dateFrom");
            params.put("dateFrom", dateFrom);
        }
        if (dateTo != null) {
            DateTime date = new DateTime(dateTo.getTime());
            date = date.plusDays(1).withTimeAtStartOfDay();
            sql.append("
 and c.created_at < :dateTo");
            params.put("dateTo", date.toDate());
        }
        sql.append("
 group by u.username, u.display_name, date_format(c.created_at, '%y-%m-%d')")
                .append("
 order by 1, 2");
        Query query = entityManager.createNativeQuery(sql.toString());
        params.forEach(query::setParameter);
        return (List<Object[]>) query.getResultList();
    }

4.附录

/*
Date todayTime = new DateTime().withTimeAtStartOfDay().toDate();//今天凌晨
Date tomorrowTime = new DateTime().plusDays(1).withTimeAtStartOfDay().toDate();//明日凌晨
Date theWeekStartTime = new DateTime().minusDays(new DateTime().getDayOfWeek()-1).withTimeAtStartOfDay().toDate();//本周开始时间
Date theWeekEndTime = new DateTime().plusDays(8-new DateTime().getDayOfWeek()).withTimeAtStartOfDay().toDate();//本周结束时间
Date theMonthStartTime = new DateTime().minusDays(new DateTime().getDayOfMonth()-1).withTimeAtStartOfDay().toDate();//本月开始时间
Date theMonthEndTime = new DateTime().minusDays(new DateTime().getDayOfMonth()-1).plusMonths(1).withTimeAtStartOfDay().toDate();//本月结束时间
*/