Java tutorial
package siddur.solidtrust.wok; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.text.MessageFormat; import java.util.Calendar; import java.util.Date; import java.util.List; import javax.mail.internet.MimeMessage; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Query; import javax.servlet.http.HttpServletResponse; import org.apache.commons.io.IOUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.time.DateUtils; import org.apache.log4j.Logger; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.core.io.FileSystemResource; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageImpl; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Pageable; import org.springframework.mail.javamail.JavaMailSenderImpl; import org.springframework.mail.javamail.MimeMessageHelper; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Controller; import org.springframework.transaction.annotation.Transactional; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import siddur.solidtrust.FileSystemUtil; import siddur.solidtrust.SolidtrustConstants; import siddur.solidtrust.util.DateUtil; @Controller @RequestMapping("wok") public class WokController { private static final Logger log4j = Logger.getLogger(WokController.class); @PersistenceContext private EntityManager em; @Autowired JavaMailSenderImpl mailSender; @RequestMapping("list") @Transactional(readOnly = true) public String list(@RequestParam(value = "id", required = false) String id, @RequestParam(value = "onlyRemoved", required = false, defaultValue = "false") boolean onlyRemoved, @RequestParam(value = "page", required = false, defaultValue = "1") Integer pageIndex, @RequestParam(value = "pageSize", required = false, defaultValue = "20") Integer pageSize, Model model) { String jpql = "select a.licensePlate, a.brand, a.type, w.addedAt, w.removedAt from Wachtopkeuren w, AzureCar a where w.licensePlate = a.licensePlate"; if (onlyRemoved) { jpql += " and w.removedAt > w.addedAt"; model.addAttribute("onlyRemoved", onlyRemoved); } if (!StringUtils.isEmpty(id)) { jpql += " and w.licensePlate = '" + id + "'"; List<Object[]> results = em.createQuery(jpql, Object[].class).getResultList(); if (!results.isEmpty()) { model.addAttribute("car", results.get(0)); } model.addAttribute("key", id); } else { Pageable pageable = new PageRequest(pageIndex - 1, pageSize); List<Object[]> results = em.createQuery(jpql, Object[].class).setFirstResult(pageable.getOffset()) .setMaxResults(pageable.getPageSize()).getResultList(); String countQL = "select count(w) from Wachtopkeuren w"; if (onlyRemoved) { countQL += " where w.removedAt > w.addedAt"; } long count = em.createQuery(countQL, Long.class).getSingleResult(); Page<Object[]> page = new PageImpl<Object[]>(results, pageable, (int) count); model.addAttribute("page", page); } return "wok/list"; } @Transactional(readOnly = true) @RequestMapping("count") public String count(Model model) { Calendar c = Calendar.getInstance(); c.add(Calendar.MONTH, -10); Query query = em.createNativeQuery( "select count(1), year(w.addedAt) y, month(w.addedAt) m from Wachtopkeuren w where w.addedAt > :from group by y, m order by y desc, m desc"); List<?> counts = query.setParameter("from", c.getTime()).getResultList(); model.addAttribute("counts", counts); return "wok/count"; } @Transactional(readOnly = true) @RequestMapping("export.xlsx") public void export(@RequestParam("year") int year, @RequestParam("month") int month, HttpServletResponse resp) { Calendar today = Calendar.getInstance(); boolean thisMonth = today.get(Calendar.YEAR) == year && today.get(Calendar.MONTH) == month - 1; String filename = year + "-" + month + ".xlsx"; File file = new File(FileSystemUtil.getWokDir(), filename); if (thisMonth || !file.isFile()) { generateFile(year, month, file); } resp.setHeader("Content-Disposition", "attachment;filename= " + file.getName()); resp.setContentType("application/octet-stream"); OutputStream os = null; InputStream is = null; try { os = resp.getOutputStream(); is = new FileInputStream(file); IOUtils.copy(is, os); } catch (IOException e) { log4j.error(e.getMessage(), e); } finally { IOUtils.closeQuietly(is); IOUtils.closeQuietly(os); } } @Scheduled(cron = "0 5 1 2 * ?") //01:05 2th monthly @RequestMapping("notify") public void sendMail() throws Exception { Calendar today = Calendar.getInstance(); int year = today.get(Calendar.YEAR); int month = today.get(Calendar.MONTH); String filename = year + "-" + month + ".xlsx"; File file = new File(FileSystemUtil.getWokDir(), filename); generateFile(year, month, file); log4j.info("Send email for WOK records: " + file.getName()); MimeMessage message = mailSender.createMimeMessage(); // use the true flag to indicate you need a multipart message MimeMessageHelper helper = new MimeMessageHelper(message, true); helper.setFrom("gbg1_spsms_gtrak@pactera-pgs-mail.chinacloudapp.cn", "Solidtrust Admin"); helper.setTo(SolidtrustConstants.WOK_EMAIL); helper.addCc(SolidtrustConstants.BEN_EMAIL); helper.addCc(SolidtrustConstants.MY_EMAIL); helper.setSubject(MessageFormat.format("WOK[{0}]", (year + "-" + month))); // use the true flag to indicate the text included is HTML helper.setText("<html><body>Here is WOK data of last month. Thanks.</body></html>", true); // let's include the infamous windows Sample file (this time copied to c:/) FileSystemResource res = new FileSystemResource(file); helper.addAttachment(file.getName(), res); mailSender.send(message); } private void generateFile(int year, int month, File file) { log4j.info("Not found file: " + file.getAbsolutePath() + ", to generate it"); Calendar cal = Calendar.getInstance(); cal.set(Calendar.YEAR, year); cal.set(Calendar.MONTH, month - 1); cal.set(Calendar.DAY_OF_MONTH, 1); Date from = DateUtils.truncate(cal.getTime(), Calendar.MONTH); Date to = DateUtils.ceiling(from, Calendar.MONTH); String ql = "select new siddur.solidtrust.wok.WokModel(w.licensePlate, m.brand, m.type, w.addedAt, w.removedAt) from Wachtopkeuren w, AzureCar m where w.licensePlate = m.licensePlate and w.addedAt between :from and :to"; List<WokModel> results = em.createQuery(ql, WokModel.class).setParameter("from", from) .setParameter("to", to).getResultList(); Workbook wb = new XSSFWorkbook(); Sheet sheet1 = wb.createSheet("sheet1"); Row row = sheet1.createRow(0); int i = 0; row.createCell(i++).setCellValue("License Plate"); row.createCell(i++).setCellValue("Brand Model"); row.createCell(i++).setCellValue("WOK STATUS BEGIN"); row.createCell(i++).setCellValue("WOK STATUS END"); for (int j = 0; j < results.size(); j++) { row = sheet1.createRow(j + 1); WokModel w = results.get(j); i = 0; row.createCell(i++).setCellValue(w.getLicensePlate()); row.createCell(i++).setCellValue(w.getBrand() + " " + w.getModel()); row.createCell(i++).setCellValue(DateUtil.date2String(w.getAddedAt())); if (w.getRemovedAt() != null) { row.createCell(i++).setCellValue(DateUtil.date2String(w.getRemovedAt())); } } OutputStream os = null; try { os = new FileOutputStream(file); wb.write(os); } catch (IOException e) { log4j.error(e.getMessage(), e); } finally { IOUtils.closeQuietly(os); } } }