Java tutorial
package siddur.solidtrust.insurancehelper; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.io.InputStreamReader; import java.io.Reader; import java.io.StringWriter; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.List; import java.util.StringTokenizer; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.time.DateUtils; import org.apache.log4j.Logger; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageImpl; import org.springframework.data.domain.Pageable; import org.springframework.stereotype.Service; import siddur.solidtrust.ConfigBean; import siddur.solidtrust.autoscout.AutoscoutNl; import siddur.solidtrust.autoscout.AutoscoutService; import siddur.solidtrust.azure.AzureCarService; import siddur.solidtrust.entity.AzureCar; import siddur.solidtrust.marktplaats.MarktplaatsService; import siddur.solidtrust.util.DateUtil; @Service public class InsuranceService { private final static Logger log4j = Logger.getLogger(InsuranceService.class); private final static int OVERDUE_STADAGEN = 7; private final static int OVERDUE_ALERT = 7 * 4; public final static String AHEAD_DAYS = "aheadDaysOfAlert"; public static final String[] tables = { "NewMarktplaats", "AutoscoutNl" }; @PersistenceContext private EntityManager em; @Autowired private MarktplaatsService mService; @Autowired private AutoscoutService aService; @Autowired private AzureCarService azureService; @Autowired private InsurancePersister persister; @Autowired private ConfigBean configBean; /* * parses file uploaded. */ public int saveRecordsFromFile(File file, boolean replace, List<String> exceptions) throws Exception { if (replace) { //delete old ones String ql = "truncate table InsuranceCar"; int rows = persister.executeNativeQuery(ql); log4j.info("clean " + rows + " overdue rows"); } InputStream is = new FileInputStream(file); Reader br = new InputStreamReader(is); StringWriter ws = new StringWriter((int) file.length()); int bufSize = 1024 * 8; char[] buf = new char[bufSize]; int len = 0; while ((len = br.read(buf)) > 0) { ws.write(buf, 0, len); } br.close(); ws.close(); int batch = 5000; List<InsuranceCar> cars = new ArrayList<InsuranceCar>(batch); int i = 0; StringTokenizer st = new StringTokenizer(ws.toString()); while (st.hasMoreTokens()) { String s = st.nextToken().trim(); if (s.length() != 6) { exceptions.add(s); continue; } i++; InsuranceCar car = new InsuranceCar(); car.setLicensePlate(s); car.setTableName(tables[0]); // Marktplaats cars.add(car); if (i % batch == 0) { if (replace) { persister.persist(cars); } else { persister.merge(cars); } log4j.info("Saved " + cars.size() + " records"); cars = new ArrayList<InsuranceCar>(batch); } } if (replace) { persister.persist(cars); } else { persister.merge(cars); } log4j.info("Saved " + cars.size() + " records"); log4j.info("Totally saved " + i + " records"); log4j.info("Save parralel records of other tables"); for (int j = 1; j < tables.length; j++) { String table = tables[j]; String ql = "insert into InsuranceCar(licensePlate, tableName, stadagen, status)" + " select licensePlate, '" + table + "', 0, 0 from InsuranceCar" + " where tableName='" + tables[0] + "'"; int rows = persister.executeNativeQuery(ql); log4j.info("Duplicate '" + table + "' from '" + tables[0] + "': " + rows); } return i; } /** * calculate alert data for special day * * @param aheadDays, default: 4 * 7days * * Always alert today */ public void alert() { String ahead = configBean.getValue(AHEAD_DAYS); int aheadDays = StringUtils.isEmpty(ahead) ? (4 * 7) : Integer.parseInt(ahead); Date start = new Date(); String startStr = DateUtil.date2String(start); Date end = DateUtils.addDays(start, aheadDays); String endStr = DateUtil.date2String(end); String ql = "update InsuranceCar set status = 2," //mark status = 2 + " alertAt = '" + startStr + "'" //mark alert date + " where status = 1" // only ones ready to alert + " and expectedSoldDate <= '" + endStr + "'"; // can include past days' records without alert long count = persister.executeNativeQuery(ql); log4j.info("Archieve " + count + " records on [" + start + "-" + endStr + "]"); } @SuppressWarnings("unchecked") public List<Object[]> getAlert(String date) { //if date = today, then re-calculate if (date.equals(DateUtil.date2String(new Date()))) { alert(); } String ql = "select i.licensePlate, min(datediff(i.expectedSoldDate, now())), i.stadagen from InsuranceCar i where" + " i.alertAt = '" + date + "'" + " group by licensePlate" + " order by expectedSoldDate"; return em.createNativeQuery(ql).getResultList(); } /** * demo in admin page * @param daysToAlert days from today * @param aheadDays default: 4 * 7days * @return */ public Page<InsuranceCar> alertDemo(Pageable pageable, int daysToAlert, int aheadDays) { Date start = new Date(); Date end = DateUtils.addDays(start, aheadDays); end = DateUtils.addDays(end, daysToAlert); String date = DateUtil.date2String(end); String baseJpql = "from InsuranceCar i" + " where i.status > 0" //include status = 1 or 2. It is different from alert() function. + " and i.expectedSoldDate <= '" + date + "'"; log4j.info(baseJpql); String ql = baseJpql + " order by i.licensePlate"; long count = em.createQuery("select count(i) " + baseJpql, Long.class).getSingleResult(); List<InsuranceCar> list = em.createQuery(ql, InsuranceCar.class) .setFirstResult(pageable.getPageSize() * pageable.getPageNumber()) .setMaxResults(pageable.getPageSize()).getResultList(); Page<InsuranceCar> page = new PageImpl<InsuranceCar>(list, pageable, (int) count); return page; } public void scanToAlert(Date from) { for (int i = 0; i < tables.length; i++) { scanEachSourceToAlert(from, tables[i]); } } /** * Scan table InsuranceCar periodically to find out the cars to be alerted. * @param from, default = today * * 0 delete overdue stadagen from Stadagen * 1 check new AD car with adDate everyday * 2 copy stadagen to MarktplaatsRecord * 3 copy records without stadagen * 4 calc stadagen * 5 copy stadagen to MarktplaatsRecord again * 6 update InsuranceCar * */ public void scanEachSourceToAlert(Date from, String table) { String startField = table.equals("Marktplaats") ? "adDate" : "dateScraped"; log4j.info("Start to scan " + table); String _4WeeksAgo = daysAgo(OVERDUE_ALERT); String _1WeekAgo = daysAgo(OVERDUE_STADAGEN); String fromStr = DateUtil.date2String(from); //0 delete overdue stadagen from Stadagen String ql = "delete from Stadagen where" + " updateAt < '" + _1WeekAgo + "'"; int rows = persister.executeNativeQuery(ql); log4j.info(ql); log4j.info("[Stadagen] clean " + rows + " overdue rows"); //1 check new AD car with adDate everyday persister.executeNativeQuery("truncate table " + table + "Record"); ql = "insert into " + table + "Record(licensePlate, adDate)" + " select m.licensePlate, min(m." + startField + ") adDate from " + table + " m, InsuranceCar i" + " where m.licensePlate = i.licensePlate and i.tableName = '" + table + "'" + " and m.dateScraped > '" + fromStr + "'" // dateScraped > from + " and m.dateRegisted is null" // car not sold + " and (i.status = 0 or (i.status = 2 and i.alertAt <= '" + _4WeeksAgo + "'))" // lastAlert date <= 7 days, not including stadagen=0 + " group by m.licensePlate"; // need to be checked rows = persister.executeNativeQuery(ql); log4j.info(ql); log4j.info("[" + table + " -> " + table + "Record] " + rows + " records to alert from " + fromStr); //2 copy stadagen to MarktplaatsRecord ql = "update " + table + "Record m, Stadagen s" + " set m.stadagen = s.stadagen" + " where m.licensePlate = s.licensePlate" + " and s.tableName = '" + table + "'"; rows = persister.executeNativeQuery(ql); log4j.info(ql); log4j.info("[" + table + "Record] " + rows + " records with stadagen"); //3 copy records without stadagen ql = "delete from Stadagen where stadagen is null"; persister.executeNativeQuery(ql); log4j.info(ql); log4j.info("[Stadagen] " + rows + " records without stadagen removed"); ql = "insert into Stadagen(licensePlate, tablename) " + "select licensePlate, '" + table + "'" + " from " + table + "Record" + " where stadagen is null"; rows = persister.executeNativeQuery(ql); log4j.info(ql); log4j.info("[" + table + "Record -> Stadagen] " + rows + " records without stadagen"); //4 calc stadagen String todayStr = DateUtil.date2String(new Date()); ql = "from Stadagen s where s.stadagen is null" + " and s.tableName = '" + table + "'"; int size = 1000; int total = 0; while (true) { List<Stadagen> list = em.createQuery(ql, Stadagen.class).setFirstResult(0).setMaxResults(size) .getResultList(); total += list.size(); calcStadagen(list, todayStr, table); persister.merge(list); if (list.size() < size) { break; } } log4j.info("[Stadagen] " + total + " records' stadagen calculated"); //5 copy stadagen to MarktplaatsRecord again ql = "update " + table + "Record m, Stadagen s" + " set m.stadagen = s.stadagen" //include 0 instead of NULL, it means 1)license plate not found, 2)no stadagen value + " where m.licensePlate = s.licensePlate" + " and s.tableName = '" + table + "'" + " and s.updateAt = '" + todayStr + "'"; rows = persister.executeNativeQuery(ql); log4j.info(ql); log4j.info("[" + table + "Record] " + rows + " records with new stadagen"); //6 update InsuranceCar ql = "update InsuranceCar i, " + table + "Record m" + " set i.stadagen = m.stadagen," + " i.adDate = m.adDate," + " i.expectedSoldDate = ADDDATE(m.adDate, m.stadagen)," + " i.status = 1" //wait to alert + " where i.licensePlate = m.licensePlate and i.tableName = '" + table + "'"; rows = persister.executeNativeQuery(ql); log4j.info(ql); log4j.info("[InsuranceCar] " + rows + " records with new stadagen"); log4j.info("Done"); } public int changeAheadDays(Integer newValue) { int oldValue = getAheadDays(); if (newValue == null) { return oldValue; } if (newValue != oldValue) { //reset status String ql = "update InsuranceCar set status = 1 where status = 2"; persister.executeNativeQuery(ql); configBean.setValue(AHEAD_DAYS, Integer.toString(newValue)); } return newValue; } private void calcStadagen(List<Stadagen> list, String today, String table) { for (Stadagen s : list) { String id = s.getLicensePlate(); int avg = 0; try { AzureCar entity = azureService.findByLicensePlate(id); if (entity != null) { String brand = entity.getBrand(); String _model = entity.getType(); @SuppressWarnings("deprecation") String build = (entity.getDateOfBuild().getYear() + 1900) + ""; if (table.equals("Marktplaats")) { avg = mService.averageTimeOnSaleByLucene(brand, _model, build); } else if (table.equals("AutoscoutNl")) { Object obj = persister.search(brand, _model, build, AutoscoutNl.class); if (obj != null) { AutoscoutNl car = (AutoscoutNl) obj; avg = aService.averageTimeOnSale(car.getBrand(), car.getModel(), car.getBuild()); } } } } catch (Exception e) { log4j.error(e.getMessage(), e); } s.setUpdateAt(today); s.setStadagen(avg); } } private int getAheadDays() { String ahead = configBean.getValue(AHEAD_DAYS); int aheadDays = StringUtils.isEmpty(ahead) ? (4 * 7) : Integer.parseInt(ahead); return aheadDays; } private static String daysAgo(int distance) { Calendar today = Calendar.getInstance(); today.add(Calendar.DATE, -distance); String date = DateUtil.date2String(today.getTime()); return date; } }