siddur.solidtrust.autoscoutde.AutoscoutDeService.java Source code

Java tutorial

Introduction

Here is the source code for siddur.solidtrust.autoscoutde.AutoscoutDeService.java

Source

package siddur.solidtrust.autoscoutde;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
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.autoscout.ScoutCar;
import siddur.solidtrust.marktplaats.Fast100;
import siddur.solidtrust.marktplaats.Sorter;
import siddur.solidtrust.marktplaats.MarktplaatsService.CarItemToSort;

@Service
public class AutoscoutDeService {

    private static final Logger log4j = Logger.getLogger(AutoscoutDeService.class);

    @PersistenceContext
    private EntityManager em;

    private String selectSQL = "select m.id, m.licensePlate, m.brand, m.model, m.build, m.color, m.price, m.enginesize, m.fuel, m.ownerType, m.mileage, m.dateScraped, m.dateRemoved, m.dateRegisted, datediff(m.dateRemoved, m.dateScraped)as days ";

    public List<AutoscoutDe> findEntities(String brand, String model, String build) {
        String jqpl = "from AutoscoutDe m where m.brand='" + brand + "' and m.model='" + model + "' and m.build='"
                + build + "' and m.dateRemoved='0000-00-00'" + " order by m.dateScraped desc";
        log4j.info(jqpl);
        return em.createQuery(jqpl, AutoscoutDe.class).getResultList();
    }

    public Page<ScoutCar> timeOnSale(Pageable pageable, String brand, String model, String build) {
        String baseJpql = "from AutoscoutDe m where m.dateRemoved != '0000-00-00'";
        if (!StringUtils.isEmpty(brand)) {
            baseJpql += " and m.brand = '" + brand + "'";
        }
        if (!StringUtils.isEmpty(model)) {
            baseJpql += " and m.model = '" + model + "'";
        }
        if (!StringUtils.isEmpty(build)) {
            baseJpql += " and m.build = '" + build + "'";
        }

        long count = em.createQuery("select count(m) " + baseJpql, Long.class).getSingleResult();

        String jpql = selectSQL + baseJpql;
        log4j.info(jpql);

        List<Object[]> list = em.createQuery(jpql, Object[].class)
                .setFirstResult(pageable.getPageSize() * pageable.getPageNumber())
                .setMaxResults(pageable.getPageSize()).getResultList();
        List<ScoutCar> results = ScoutCar.toScoutCarList(list, true);
        Page<ScoutCar> page = new PageImpl<ScoutCar>(results, pageable, (int) count);
        return page;
    }

    public int averageTimeOnSale(String brand, String model, String build) {
        String baseJpql = "select avg(datediff(m.dateRemoved, m.dateScraped)) from AutoscoutDe m where m.dateRemoved != '0000-00-00'";
        if (!StringUtils.isEmpty(brand)) {
            baseJpql += " and m.brand = '" + brand + "'";
        }
        if (!StringUtils.isEmpty(model)) {
            baseJpql += " and m.model = '" + model + "'";
        }
        if (!StringUtils.isEmpty(build)) {
            baseJpql += " and m.build = '" + build + "'";
        }

        BigDecimal avg = (BigDecimal) em.createNativeQuery(baseJpql).getSingleResult();
        return avg.intValue();
    }

    public List<Fast100> top100(int maxCount, String brand, Integer buildStart, Integer buildEnd,
            Integer priceStart, Integer priceEnd) {
        String brandCondition = "";
        if (!StringUtils.isEmpty(brand)) {
            brandCondition = " and m.brand = '" + brand + "'";
        }

        String buildCondition = "";
        if (buildEnd != null && buildStart != null) {
            buildCondition = " and m.build between '" + buildStart + "' and '" + buildEnd + "'";
        } else {
            buildCondition = " and m.build != '0'";
        }
        String priceCondition = "";
        if (priceEnd != null && priceStart != null) {
            priceCondition = " and price between " + priceStart + " and " + priceEnd;
        }

        String jqpl = "select m.brand, m.model, m.build, m.enginesize, m.fuel, m.arrangement, count(1) as count, avg(datediff(m.dateRemoved, m.dateScraped)) as avg, avg(m.price) as price"
                + " from AutoscoutDe m" + " where m.dateRemoved != '0000-00-00' and m.enginesize != 0"
                + brandCondition + buildCondition
                + " group by m.brand, m.model, m.build, m.enginesize, m.fuel, m.arrangement" + " having count > "
                + maxCount + priceCondition + " order by avg asc limit 100";

        log4j.info(jqpl);

        @SuppressWarnings("unchecked")
        List<Object[]> list = em.createNativeQuery(jqpl).getResultList();
        List<Fast100> results = new ArrayList<Fast100>(list.size());
        for (Object[] objs : list) {
            Fast100 f = new Fast100();
            results.add(f);
            int i = 0;
            f.setBrand((String) objs[i++]);
            f.setModel((String) objs[i++]);
            f.setBuild((String) objs[i++]);
            f.setEngineSize((Integer) objs[i++]);
            f.setFuelType((String) objs[i++]);
            f.setArrangement((String) objs[i++]);
            f.setCount(((BigInteger) objs[i++]).intValue());
            f.setAvg(((BigDecimal) objs[i++]).intValue() + 1); //plus 1
            f.setAvgPrice(((BigDecimal) objs[i++]).intValue());
        }

        return results;
    }

    public Sorter top100Sort(Fast100 f) {
        String baseJpql = "from AutoscoutDe m where m.dateRemoved != '0000-00-00'" + " and m.brand = '"
                + f.getBrand() + "' and m.model = '" + f.getModel() + "' and m.build = '" + f.getBuild()
                + "' and m.enginesize = " + f.getEngineSize() + " and m.fuel = '" + f.getFuelType()
                + "' and m.arrangement = '" + f.getArrangement() + "'";
        String selectSql = "select m.ownerType, m.color, m.price, m.mileage, datediff(m.dateRemoved, m.dateScraped)as days ";
        String jpql = selectSql + baseJpql;
        log4j.info(jpql);

        @SuppressWarnings("unchecked")
        List<Object[]> list = em.createNativeQuery(jpql).getResultList();
        List<CarItemToSort> results = new ArrayList<CarItemToSort>(list.size());
        for (Object[] objs : list) {
            CarItemToSort car = new CarItemToSort();
            results.add(car);
            int i = 0;
            car.setOwnerType((Integer) objs[i++]);
            car.setColor((String) objs[i++]);
            car.setPrice((Integer) objs[i++]);
            car.setMileage((Integer) objs[i++]);
            car.setDays(((BigInteger) objs[i++]).intValue() + 1); //plus 1
        }

        Sorter sorter = new Sorter(results);
        sorter.sort();
        return sorter;
    }

    public Page<ScoutCar> top100Detail(Pageable pageable, Fast100 f, String type, int index, String name) {
        String baseJpql = "from AutoscoutDe m where m.dateRemoved != '0000-00-00'" + " and m.brand = '"
                + f.getBrand() + "' and m.model = '" + f.getModel() + "' and m.build = '" + f.getBuild()
                + "' and m.enginesize = " + f.getEngineSize() + " and m.fuel = '" + f.getFuelType()
                + "' and m.arrangement = '" + f.getArrangement() + "'";

        if (type.equals("color")) {
            baseJpql += " and m.color='" + name + "'";
        } else {
            Integer[] range = type.equals("price") ? Sorter.PRICE_RANGE : Sorter.MILEAGE_RANGE;
            if (index == 0) {
                baseJpql += " and m." + type + " < " + range[index];
            } else if (index == range.length) {
                baseJpql += " and m." + type + " >= " + range[index - 1];
            } else {
                baseJpql += " and m." + type + " < " + range[index];
                baseJpql += " and m." + type + " >= " + range[index - 1];
            }
        }

        String jpql = selectSQL + baseJpql;
        log4j.info(jpql);

        List<Object[]> list = em.createQuery(jpql, Object[].class)
                .setFirstResult(pageable.getPageSize() * pageable.getPageNumber())
                .setMaxResults(pageable.getPageSize()).getResultList();
        List<ScoutCar> results = ScoutCar.toScoutCarList(list, true);

        long count = em.createQuery("select count(m) " + baseJpql, Long.class).getSingleResult();
        Page<ScoutCar> page = new PageImpl<ScoutCar>(results, pageable, (int) count);
        return page;
    }

}