PositionService.java

package service;

import api.v1.Position;
import api.v1.PositionSearch;
import api.v1.PositionTag;
import com.avaje.ebean.Ebean;
import com.avaje.ebean.Query;
import com.avaje.ebean.RawSql;
import com.avaje.ebean.RawSqlBuilder;
import exceptions.PoseidonException;
import mapper.PositionMapper;
import mapper.PositionTagMapper;
import models.OrderModel;
import models.PositionModel;
import models.PositionTagCount;
import models.PositionTagModel;
import play.db.DB;
import play.mvc.Http;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class PositionService extends PoseidonService {

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

  public List<PositionModel> findAll() {
    List<PositionModel> mPositionList = PositionModel.find.where().isNull("deleted").findList();

    for (PositionModel position : mPositionList) {
      position.isDeletable = isDeletable(position.id);
    }
    return mPositionList;
  }

  public PositionModel getById(long id) {
    PositionModel mPosition = PositionModel.find.byId(id);
    mPosition.isDeletable = isDeletable(id);
    return mPosition;
  }

  public PositionModel findByName(String name) {
    PositionModel mPosition = PositionModel.find.where().ieq("name", name).isNull("deleted").findUnique();
    if (mPosition != null) {
      mPosition.isDeletable = isDeletable(mPosition.id);
    }
    return mPosition;
  }


  public PositionModel deletePosition(PositionModel mPosition) {

    if (mPosition.isDeletable) {
      // dont physically delete, just mark as deleted
      mPosition.deleted = PoseidonService.getNow().toDate();
      mPosition.deletedBy = getUser();
      mPosition.save();
    } else {
      throw new PoseidonException(Http.Status.BAD_REQUEST, "Kan ikke slette posisjonen, da denne refereres av en eller flere bestillinger");
    }
    return mPosition;
  }

  public PositionModel createOrUpdate(Position apiPosition) {
    PositionModel mPosition;
    if ((apiPosition.id != null) && (apiPosition.id > 0)) {
      mPosition = updateExisting(apiPosition);
    } else {
      PositionModel p = PositionModel.findByName(apiPosition.name);
      if (p != null && p.deleted == null) {
        throw new PoseidonException(Http.Status.BAD_REQUEST, "Posisjonens navn er ikke unikt");
      }
      if (p != null) {  // exists, but deleted, resurrect it
        mPosition = p;
        mPosition.deleted = null;
        mPosition.deletedBy = null;
      } else {
        if (apiPosition.tags.length == 0) {
          throw new PoseidonException(Http.Status.BAD_REQUEST, "Posisjonen må ha minst 1 tag");
        }
        mPosition = new PositionMapper().mapToModel(apiPosition);
      }
      mPosition.isDeletable = true;
      mPosition.created =PoseidonService.getNow().toDate();
      mPosition.createdBy = getUser();
    }
    mPosition.save();
    return mPosition;
  }

  private PositionModel updateExisting(Position aPosition) {
    PositionModel mPosition = getById(aPosition.id);
    PositionMapper mapper = new PositionMapper();

    if (aPosition.tags.length == 0) {
      throw new PoseidonException(Http.Status.BAD_REQUEST, "Posisjonen må ha minst 1 tag");
    }

    mPosition.name = aPosition.name;
    mapper.mapCoordinate(aPosition, mPosition);
    mPosition.isDeletable = isDeletable(aPosition.id);

    mPosition.tags = new ArrayList<>();
    for (PositionTag tag : aPosition.tags) {
      PositionTagModel mTag;
      if (tag.id == null || tag.id == 0) {
        mTag = PositionTagModel.findByName(tag.name);
        if (mTag == null) {
          mTag = new PositionTagModel(tag.name);
          mTag.save();
        }
      } else {
        mTag = PositionTagModel.find.byId(tag.id);
        if (mTag == null) {
          throw new PoseidonException(Http.Status.BAD_REQUEST, "Invalid position tag id specified");
        }
      }
      if (mTag.positionModels == null) mTag.positionModels = new ArrayList<>();
      mTag.positionModels.add(mPosition);
      mPosition.tags.add(mTag);
    }
    mPosition.updated = PoseidonService.getNow().toDate();
    mPosition.updatedBy = getUser();
    return mPosition;
  }


  public List<PositionModel> getPositionsForCustomer(Long id) {
    String positionsSql =
        "select distinct " +
            "      p.id, " +
            "      p.name, " +
            "      o.position_name, " +
            "      p.latitude, " +
            "      p.longitude " +
            "from  pos_position p, " +
            "      pos_order o " +
            "where o.customer_id = ? " +
            "and   o.position_id = p.id " +
            "and   p.deleted is null " +
            "and   o.deleted is null ";

    // Her brukes ren JDBC pga en bug i Ebean med hensyn på transiente verdier (position.alias)
    Connection connection = DB.getConnection(PoseidonPropertyService.getDefaultDatasource());
    PreparedStatement positionStatement = null;
    PreparedStatement tagStatement = null;

    List<PositionModel> positions = new ArrayList<>();
    try {
      int i = 1;
      positionStatement = connection.prepareStatement(positionsSql);
      positionStatement.setLong(i, id);
      String tagSql = "select distinct t.id,t.name " +
          "from pos_position p, pos_position_tag t, pos_position_tags pt " +
          "where p.id = ? " +
          "and pt.position_id = p.id " +
          "and pt.tag_id = t.id " +
          "ORDER by t.name";

      tagStatement = connection.prepareStatement(tagSql);

      ResultSet rs = positionStatement.executeQuery();

      while (rs.next()) {
        PositionModel p = new PositionModel();
        p.id = rs.getLong(1);
        p.name = rs.getString(2);
        p.alias = rs.getString(3);
        p.latitude = rs.getDouble(4);
        p.longitude = rs.getDouble(5);

        tagStatement.setLong(i, p.id);
        ResultSet tagRs = null;
        try {
          tagRs = tagStatement.executeQuery();
          while (tagRs.next()) {
            PositionTagModel tagModel = new PositionTagModel();
            tagModel.id = tagRs.getLong(1);
            tagModel.name = tagRs.getString(2);
            p.tags.add(tagModel);
          }
        } finally {
          if (tagRs != null) {
            tagRs.close();
          }
        }

        positions.add(p);
      }
    } catch (SQLException e) {
      throw new PoseidonException(Http.Status.INTERNAL_SERVER_ERROR, "En feil oppstod i kommunikasjon med databasen. Feilen har blitt logget", e);
    } finally {
      if (positionStatement != null) try {
        positionStatement.close();
        if (tagStatement != null) tagStatement.close();
        connection.close();
      } catch (SQLException e) {
        throw new PoseidonException(Http.Status.INTERNAL_SERVER_ERROR, "En feil oppstod i kommunikasjon med databasen. Feilen har blitt logget", e);
      }

    }

    for (PositionModel p : positions) {
      p.isDeletable = isDeletable(p.id);
    }

    return positions;

  }

  public boolean isDeletable(long positionId) {

    boolean isDeletable = false;
    String sql = "select distinct o.id " +
        "from pos_position p, pos_order o " +
        "where p.id = :id " +
        "and p.id = o.position_id " +
        "and p.deleted is null " + 
        "and o.deleted is null ";

    RawSql rawSql = RawSqlBuilder.parse(sql).
        columnMapping("o.id", "id").create();
    Query<OrderModel> query = Ebean.find(OrderModel.class);
    query.setRawSql(rawSql);
    query.setParameter("id",positionId);

    List<OrderModel> referencedOrders = query.findList();

    if (referencedOrders.size() == 0) {
      isDeletable = true;
    }

    return isDeletable;
  }


  public List<PositionModel> search(PositionSearch searchParams) {
    String select = "select distinct p.id, p.name, p.latitude, p.longitude ";

    String from = " from pos_position p ";

    String where = " where p.deleted is null ";

    if ((searchParams.radius != null && searchParams.radius > 0) &&
        (searchParams.position_alias != null && !searchParams.position_alias.isEmpty()) &&
        (searchParams.position_name != null && !searchParams.position_name.isEmpty())) {
      throw new PoseidonException(Http.Status.BAD_REQUEST, "Søkeradius kan ikke angis når både posisjonsnavn og alias er angitt");
    }

    if ((searchParams.radius != null && searchParams.radius > 0) &&
        (searchParams.position_alias == null || searchParams.position_alias.isEmpty()) &&
        (searchParams.position_name == null || searchParams.position_name.isEmpty())) {
      searchParams.radius = null;  // ignore search radius if no position name/alias is specified
    }

    if (searchParams.radius != null && searchParams.radius > 0.0) {
      from = "from (select p.id, p.name, p.latitude, p.longitude " +
          "          from pos_position p where lower(p.name) like '%" + searchParams.position_name.toLowerCase() + "%' ) as p1,\n" +
          "          pos_position p\n";
      if (where.length() > 7) where += " and ";
      where += " acos( sin(radians(p1.latitude))*sin(radians(p.latitude)) + cos(radians(p1.latitude))*cos(radians(p.latitude))*cos(radians(p.longitude-p1.longitude))) * 6371  < " + searchParams.radius;
    } else {

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

      if (searchParams.searchArea != null && searchParams.searchArea.isValid()) {
        if (where.length() > 7) where += " and ";
        where += " p.latitude between " + searchParams.searchArea.latitude_lower_left + " and " + searchParams.searchArea.latitude_upper_right +
            "and p.longitude between " + searchParams.searchArea.longitude_lower_left + " and " + searchParams.searchArea.longitude_upper_right;

      }
      if (searchParams.tags != null && searchParams.tags.length > 0) {
        from += ", pos_position_tag tag, pos_position_tags tags ";
        if (where.length() > 7) where += " and ";

        String taglist = "";
        for (int i = 0; i < searchParams.tags.length; i++) {
          taglist += "'" + searchParams.tags[i].name + "'";
          if (i < searchParams.tags.length - 1)
            taglist += ",";
        }
        where += " tag.id = tags.tag_id " +
            "and tags.position_id = p.id " +
            "and tag.name in (" + taglist + ")";
      }
    }

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

    if (where.length() > 7) sql += where;

    RawSqlBuilder rawSqlBuilder = RawSqlBuilder.parse(sql);

    RawSqlBuilder columnMapping = rawSqlBuilder
        .columnMapping("p.id", "id")
        .columnMapping("p.name", "name")
        .columnMapping("p.latitude", "latitude")
        .columnMapping("p.longitude", "longitude");

    RawSql rawSql = columnMapping.create();

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

    List<PositionModel> positions = query.findList();

    for (PositionModel p : positions) {
      p.isDeletable = isDeletable(p.id);
    }

    return positions;

  }

  public List<PositionTagModel> findAllTags() {
    return PositionTagModel.find.all();
  }

  public PositionTagModel getTagById(Long id) {
    return PositionTagModel.find.byId(id);
  }

  public List<PositionTagModel> findAllMatchingTags(String query) {
    return PositionTagModel.find.where().icontains("name", query).findList();
  }

  public PositionTagModel createOrUpdateTag(PositionTag apiTag) {

    PositionTagModel mTag;
    if ((apiTag.id != null) && (apiTag.id > 0)) {
      mTag = updateExistingTag(apiTag);
    } else {
      PositionTagModel tag = PositionTagModel.findByName(apiTag.name);
      if (tag != null) {
        throw new PoseidonException(Http.Status.BAD_REQUEST, "Tagens navn er ikke unikt");
      }

      mTag = new PositionTagMapper().mapToModel(apiTag);
      mTag.created = PoseidonService.getNow().toDate();
      mTag.createdBy = getUser();
    }
    mTag.save();
    return mTag;
  }

  private PositionTagModel updateExistingTag(PositionTag apiTag) {
    PositionTagModel mTag = getTagById(apiTag.id);
    mTag.name = apiTag.name;
    mTag.updated = PoseidonService.getNow().toDate();
    mTag.updatedBy = getUser();
    return mTag;
  }

  public List<PositionTagCount> getTagsForCustomer(Long id) {
    String select = "select distinct t.id, t.name, count(t.id)";
    String from = " from pos_position p , pos_position_tag t, pos_position_tags pt, pos_order o, pos_customer c";
    String where = " where " +
        " t.id = pt.tag_id " +
        " and pt.position_id = p.id " +
        " and o.position_id = p.id " +
        " and p.deleted is null " +
        " and o.deleted is null " +
        " and c.id = o.customer_id " +
        " and c.id = " + id + "" +
        " group by t.name, t.id ";

    String sql = select + from + where;
    RawSqlBuilder rawSqlBuilder = RawSqlBuilder.parse(sql);

    RawSqlBuilder columnMapping = rawSqlBuilder
        .columnMapping("t.id", "tag.id")
        .columnMapping("t.name", "tag.name")
        .columnMapping("count(t.id)", "count");

    RawSql rawSql = columnMapping.create();

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

    List<PositionTagCount> tags = query.findList();
    return tags;
  }

}