ForecastService.java

package service;

import api.v1.ForecastSearchParams;
import api.v1.OrderForecast;
import api.v1.WorklistItem;
import api.v1.WorklistItemStatus;
import com.avaje.ebean.Ebean;
import com.avaje.ebean.Query;
import com.avaje.ebean.RawSql;
import com.avaje.ebean.RawSqlBuilder;
import mapper.WorklistMapper;
import models.ForecastModel;
import models.ForecastSearchResult;
import models.OrderModel;
import org.joda.time.DateTime;
import org.joda.time.Duration;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import quba.service.QubaService;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ForecastService extends PoseidonService {

  private final static Logger logger = LoggerFactory.getLogger(ForecastService.class);

  public ForecastService(String username) {
    super(username);
  }

  public List<WorklistItem> getWorklistItems(DateTime dato, Integer termin, List<OrderModel> orderModels) {

    List<WorklistItem> items = new WorklistMapper().mapToApi(orderModels);

    for (WorklistItem item : items) {

      item.date = dato.toDate();
      DateTimeFormatter sdf = DateTimeFormat.forPattern("MM-dd");
      item.termin = sdf.print(dato) + "/" + (termin < 10 ? "0" + termin : termin);


      item.previousForecastDate = null;

      ForecastModel fm = getLatestForecast(item.metref);
      if (fm != null) {
        item.previousForecastDate = fm.forecastedAt;
        item.previousForecastComment = fm.comment;
        item.previousForecastConfidence = fm.confidence;
      }

      DateTime dateOfProduction = new DateTime(dato, getTimeZone());
      ForecastModel forecastModel = ForecastModel.findByMetrefAndTermin(item.metref, item.termin);

      if (forecastModel != null) {  // varslet før -> enten failed eller produced
        if (forecastModel.failedAt != null) {
          item.status = WorklistItemStatus.FAILED;
          item.error = forecastModel.failureReason;
        } else {
          if (forecastModel.producedAt != null) {
            item.status = WorklistItemStatus.PRODUCED;
          } else if (forecastModel.forecastedAt != null) {
            item.status = WorklistItemStatus.SENT;
          }
        }
      } else {
        // ikke varslet tidligere, -> READY, OLD_DATA eller NO_DATA
        item.status = item.product.positionRequired ?
            findItemStatus(item.position_name, dateOfProduction, termin):
            WorklistItemStatus.READY ;
      }

      item.strongWindWarning =
          item.product.positionRequired &&
          (item.status == WorklistItemStatus.READY ||
           item.status == WorklistItemStatus.OLD_DATA) &&
          QubaService.fetchStrongWindWarning(item.position_name, dateOfProduction, termin);

      logger.info("item {}", item);
    }
    return items;
  }

  protected WorklistItemStatus findItemStatus(String position_name, DateTime dateOfProduction, Integer termin) {
    WorklistItemStatus status;
    DateTime now = getNow();

    DateTime terminDateTime = dateOfProduction.withHourOfDay(termin != null ? termin : 0);
    Duration d = new Duration(terminDateTime, now);
    long hoursUntilTermin = d.getStandardHours();

    logger.debug("dateOfProduction = {}, termin={}, hoursUntilTermin = {}", dateOfProduction, termin, hoursUntilTermin);

    boolean exists = QubaService.qubaHasDataForPositionAndTermin(position_name, dateOfProduction, termin);

    if (!exists) {
      status = WorklistItemStatus.NO_DATA;
    } else if (Math.abs(hoursUntilTermin) > 6) {
      status = WorklistItemStatus.OLD_DATA;
    } else {
      status = WorklistItemStatus.READY;
    }
    return status;
  }

  private ForecastModel getLatestForecast(String metref) {
    OrderModel order = OrderModel.findByMetref(metref);
    return findLastForecastForOrder(order);
  }

  private ForecastModel findLastForecastForOrder(OrderModel order) {
    return ForecastModel.findLastForecastForMetref(order.met_ref);
  }

  public List<ForecastModel> produceAndSend(OrderForecast apiOrderForecast, String forecasterName) {
    List<ForecastModel> forecastModels = new ArrayList<>();
    for (WorklistItem item : apiOrderForecast.worklistItems) {
      ForecastModel forecastModel = ForecastModel.findByMetrefAndTermin(item.metref, item.termin);

      Date forecastedAt = PoseidonService.getNow().toDate();

      if (forecastModel == null) {
        OrderModel order = OrderModel.findByMetref(item.metref);


        forecastModel = new ForecastModel(
            apiOrderForecast.comment,
            apiOrderForecast.confidence,
            forecasterName,
            forecastedAt,
            null,
            item.termin,
            order);

        forecastModel.created = forecastedAt;
        forecastModel.createdBy = getUser();

        // bestillinger uten posisjon blir produsert umiddelbart
        if (order.product.positionRequired == false) {
          forecastModel.producedAt = forecastedAt;
        }

      } else {
        // this order/termin has been forecasted before...
        forecastModel.forecastedBy = forecasterName;
        forecastModel.forecastedAt = forecastedAt;
        forecastModel.comment = apiOrderForecast.comment;
        forecastModel.confidence = apiOrderForecast.confidence;
        forecastModel.producedAt = null;
        forecastModel.updated = forecastModel.forecastedAt;
        forecastModel.updatedBy = getUser();
        forecastModel.failedAt = null;
        forecastModel.failureReason = null;
      }
      forecastModel.save();
      forecastModels.add(forecastModel);
    }
    return forecastModels;
  }

  public List<ForecastSearchResult> search(ForecastSearchParams searchParams) {

    String select =
        "  select c.name as customer_name," +
            "  o.met_ref as met_ref ," +
            "  o.custref_po_calloff as po_calloff," +
            "  o.custref_contractnum as contractnum," +
            "  o.custref_email as email," +
            "  o.customer_name as customer_contact," +
            "  o.position_name as position_name," +
            "  p.name as position_alias," +
            "  min(f.produced_at) as forecast_start," +
            "  max(f.produced_at) as forecast_end," +
            "  count(f.id) as forecast_count, " +
            "  f.invoiced as invoiced ";

    String from = " FROM POS_FORECAST f" +
        "  JOIN POS_ORDER o ON o.id = f.order_id" +
        "  JOIN POS_CUSTOMER c ON o.customer_id = c.id" +
        "  JOIN pos_position p ON o.position_id = p.id";

    String where = "where (f.produced_at IS NOT NULL) ";

    if (searchParams.position_name != null && !searchParams.position_name.isEmpty()) {
      where += " and lower(p.name) like '%" + searchParams.position_name.toLowerCase() + "%'";
    } else if (searchParams.position_alias != null && !searchParams.position_alias.isEmpty()) {
      where += " and lower(o.position_name) like '%" + searchParams.position_alias.toLowerCase() + "%'";
    }

    if (searchParams.customer_name != null && !searchParams.customer_name.isEmpty()) {
      where += " and lower(c.name) like '%" + searchParams.customer_name.toLowerCase() + "%'";

    }
    if (searchParams.met_ref != null && !searchParams.met_ref.isEmpty()) {
      where += "and lower(o.met_ref) like '%" + searchParams.met_ref.toLowerCase() + "%'";
    }
    if (searchParams.custref_po_calloff != null && !searchParams.custref_po_calloff.isEmpty()) {
      where += " and lower(o.custref_po_calloff) like '%" + searchParams.custref_po_calloff.toLowerCase() + "%'";
    }
    if (searchParams.custref_contractnum != null && !searchParams.custref_contractnum.isEmpty()) {
      where += " and lower(o.custref_contractnum) like '%" + searchParams.custref_contractnum.toLowerCase() + "%'";
    }
    if (searchParams.custref_email != null && !searchParams.custref_email.isEmpty()) {
      where += " and lower(o.custref_email) like '%" + searchParams.custref_email.toLowerCase() + "%'";
    }
    if (searchParams.customer_contactname != null && !searchParams.customer_contactname.isEmpty()) {
      where += " and lower(o.customer_name) like '%" + searchParams.customer_name.toLowerCase() + "%'";
    }
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    if (searchParams.start_date != null) {
      String date;
      if (searchParams.start_term != null) {
        DateTime start = new DateTime(searchParams.start_date).withHourOfDay(searchParams.start_term);
        date = sdf.format(start.toDate());
      } else {
        date = sdf.format(searchParams.start_date);
      }
      where += " and f.produced_at >= '" + date + "'";
    }
    if (searchParams.end_date != null) {
      String date;
      if (searchParams.end_term != null) {
        DateTime end = new DateTime(searchParams.end_date).withHourOfDay(searchParams.end_term);
        date = sdf.format(end.toDate());
      } else {
        date = sdf.format(searchParams.end_date);

      }
      where += " and f.produced_at < '" + date + "'";
    }


    String groupBy = " GROUP BY c.name," +
        "  o.met_ref," +
        "  o.custref_po_calloff," +
        "  o.custref_contractnum," +
        "  o.custref_email," +
        "  o.customer_name," +
        "  o.position_name," +
        "  f.invoiced," +
        "  p.name";


    String sql = select + " " + from + " " + where + " " + groupBy;

    RawSqlBuilder rawSqlBuilder = RawSqlBuilder.parse(sql);

    RawSqlBuilder columnMapping = rawSqlBuilder
        .columnMapping("c.name", "customer_name")
        .columnMapping("o.met_ref", "met_ref")
        .columnMapping("o.custref_po_calloff", "custref_po_calloff")
        .columnMapping("o.custref_contractnum", "custref_contractnum")
        .columnMapping("o.custref_email", "custref_email")
        .columnMapping("o.customer_name", "customer_contactname")
        .columnMapping("p.name", "position_name")
        .columnMapping("o.position_name", "position_alias")
        .columnMapping("min(f.produced_at)", "start_date")
        .columnMapping("max(f.produced_at)", "end_date")
        .columnMapping("count(f.id)", "produced_forecasts")
        .columnMapping("f.invoiced", "invoiced");

    RawSql rawSql = columnMapping.create();

    Query<ForecastSearchResult> query = Ebean.find(ForecastSearchResult.class);
    query.setRawSql(rawSql);

    List<ForecastSearchResult> results = query.findList();

    return results;
  }
}