Esempio n. 1
0
  /**
   * Find the periods in a calendar year it need not be a standard period (used in MRP)
   *
   * @param C_Year_ID Year
   * @param periodType Period Type
   * @param ctx context
   * @param trx trx
   * @return MPeriod[]
   */
  public static MPeriod[] getAllPeriodsInYear(int C_Year_ID, String periodType, Ctx ctx, Trx trx) {

    List<MPeriod> periods = new ArrayList<MPeriod>();
    String sql = "SELECT * FROM C_Period WHERE IsActive='Y'";

    sql = sql + " AND C_Year_ID = ?";

    if (periodType != null) sql = sql + " AND PeriodType = ? ";

    sql = sql + " order by StartDate ";

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, C_Year_ID);

      if (periodType != null) pstmt.setString(2, periodType);

      rs = pstmt.executeQuery();
      while (rs.next()) periods.add(new MPeriod(ctx, rs, trx));
    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    MPeriod[] retValue = new MPeriod[periods.size()];
    periods.toArray(retValue);
    return retValue;
  }
Esempio n. 2
0
  /**
   * Find all the year records in a Calendar, it need not be a standard period (used in MRP)
   *
   * @param C_Calendar_ID calendar
   * @param ctx context
   * @param trx trx
   * @return MYear[]
   */
  public static MYear[] getAllYearsInCalendar(int C_Calendar_ID, Ctx ctx, Trx trx) {

    List<MYear> years = new ArrayList<MYear>();
    String sql = "SELECT * FROM C_Year WHERE " + "IsActive='Y' AND C_Calendar_ID = ? ";

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, C_Calendar_ID);
      rs = pstmt.executeQuery();
      while (rs.next()) years.add(new MYear(ctx, rs, trx));

    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {

      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }

    MYear[] retValue = new MYear[years.size()];
    years.toArray(retValue);
    return retValue;
  }
Esempio n. 3
0
  /**
   * Returns the previous period
   *
   * @param period MPeriod
   * @param periodCount Count
   * @param trx trx
   * @param ctx Ctx
   * @return MPeriod
   */
  public static MPeriod getPreviousPeriod(MPeriod period, Ctx ctx, Trx trx) {

    MPeriod newPeriod = null;
    String sql =
        "SELECT * FROM C_Period WHERE "
            + "C_Period.IsActive='Y' AND PeriodType='S' "
            + "AND C_Period.C_Year_ID IN "
            + "(SELECT C_Year_ID FROM C_Year WHERE C_Year.C_Calendar_ID = ? ) "
            + "AND ((C_Period.C_Year_ID * 1000) + C_Period.PeriodNo) "
            + " < ((? * 1000) + ?) ORDER BY C_Period.C_Year_ID DESC, C_Period.PeriodNo DESC";

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, period.getC_Calendar_ID());
      pstmt.setInt(2, period.getC_Year_ID());
      pstmt.setInt(3, period.getPeriodNo());
      rs = pstmt.executeQuery();
      if (rs.next()) newPeriod = new MPeriod(ctx, rs, trx);
    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    return newPeriod;
  }
Esempio n. 4
0
 /**
  * Find first Year Period of DateAcct based on Client Calendar
  *
  * @param ctx context
  * @param C_Calendar_ID calendar
  * @param DateAcct date
  * @return active first Period
  */
 public static MPeriod getFirstInYear(Ctx ctx, int C_Calendar_ID, Timestamp DateAcct) {
   MPeriod retValue = null;
   String sql =
       "SELECT * "
           + "FROM C_Period "
           + "WHERE C_Year_ID IN "
           + "(SELECT p.C_Year_ID "
           + "FROM C_Year y"
           + " INNER JOIN C_Period p ON (y.C_Year_ID=p.C_Year_ID) "
           + "WHERE y.C_Calendar_ID=?"
           + "	AND ? BETWEEN StartDate AND EndDate)"
           + " AND IsActive='Y' AND PeriodType='S' "
           + "ORDER BY StartDate";
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   try {
     pstmt = DB.prepareStatement(sql, (Trx) null);
     pstmt.setInt(1, C_Calendar_ID);
     pstmt.setTimestamp(2, DateAcct);
     rs = pstmt.executeQuery();
     if (rs.next()) // 	first only
     retValue = new MPeriod(ctx, rs, null);
   } catch (SQLException e) {
     s_log.log(Level.SEVERE, sql, e);
   } finally {
     DB.closeStatement(pstmt);
     DB.closeResultSet(rs);
   }
   return retValue;
 } //	getFirstInYear
Esempio n. 5
0
  /**
   * Find standard Period of DateAcct based on Client Calendar
   *
   * @param ctx context
   * @param C_Calendar_ID calendar
   * @param DateAcct date
   * @return active Period or null
   */
  public static MPeriod getOfCalendar(Ctx ctx, int C_Calendar_ID, Timestamp DateAcct) {
    if (DateAcct == null) {
      s_log.warning("No DateAcct");
      return null;
    }
    if (C_Calendar_ID == 0) {
      s_log.warning("No Calendar");
      return null;
    }
    //	Search in Cache first
    Iterator<MPeriod> it = s_cache.values().iterator();
    while (it.hasNext()) {
      MPeriod period = it.next();
      if (period.getC_Calendar_ID() == C_Calendar_ID
          && period.isStandardPeriod()
          && period.isInPeriod(DateAcct)) return period;
    }

    //	Get it from DB
    MPeriod retValue = null;
    String sql =
        "SELECT * FROM C_Period "
            + "WHERE C_Year_ID IN "
            + "(SELECT C_Year_ID FROM C_Year WHERE C_Calendar_ID=?)"
            + " AND ? BETWEEN TRUNC(StartDate,'DD') AND TRUNC(EndDate,'DD')"
            + " AND IsActive='Y' AND PeriodType='S'";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, (Trx) null);
      pstmt.setInt(1, C_Calendar_ID);
      pstmt.setTimestamp(2, TimeUtil.getDay(DateAcct));
      rs = pstmt.executeQuery();
      while (rs.next()) {
        MPeriod period = new MPeriod(ctx, rs, null);
        Integer key = Integer.valueOf(period.getC_Period_ID());
        s_cache.put(key, period);
        if (period.isStandardPeriod()) retValue = period;
      }
    } catch (SQLException e) {
      s_log.log(Level.SEVERE, "DateAcct=" + DateAcct, e);
    } finally {
      DB.closeStatement(pstmt);
      DB.closeResultSet(rs);
    }
    if (retValue == null)
      s_log.warning(
          "No Standard Period for " + DateAcct + " (C_Calendar_ID=" + C_Calendar_ID + ")");
    return retValue;
  } //	get
Esempio n. 6
0
  /**
   * Load Regions (cached)
   *
   * @param ctx context
   */
  private static void loadAllRegions(Ctx ctx) {
    String sql = "SELECT * FROM C_Region WHERE IsActive='Y'";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, (Trx) null);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        MRegion r = new MRegion(ctx, rs, null);
        s_regions.put(String.valueOf(r.getC_Region_ID()), r);
        if (r.isDefault()) s_default = r;
      }
    } catch (SQLException e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }

    s_log.fine(s_regions.size() + " - default=" + s_default);
  } //	loadAllRegions
Esempio n. 7
0
 /**
  * Get Period Control
  *
  * @param requery requery
  * @return period controls
  */
 public MPeriodControl[] getPeriodControls(boolean requery) {
   if (m_controls != null && !requery) return m_controls;
   //
   ArrayList<MPeriodControl> list = new ArrayList<MPeriodControl>();
   String sql = "SELECT * FROM C_PeriodControl " + "WHERE C_Period_ID=?";
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   try {
     pstmt = DB.prepareStatement(sql, get_Trx());
     pstmt.setInt(1, getC_Period_ID());
     rs = pstmt.executeQuery();
     while (rs.next()) list.add(new MPeriodControl(getCtx(), rs, get_Trx()));
   } catch (Exception e) {
     log.log(Level.SEVERE, sql, e);
   } finally {
     DB.closeResultSet(rs);
     DB.closeStatement(pstmt);
   }
   m_controls = new MPeriodControl[list.size()];
   list.toArray(m_controls);
   return m_controls;
 } //	getPeriodControls
Esempio n. 8
0
  /**
   * Find Period of Date based on Client Calendar, it need not be a standard period (used in MRP)
   *
   * @param ctx context
   * @param C_Calendar_ID calendar
   * @param Date date
   * @param trx trx
   * @return active Period or null
   */
  public static MPeriod getPeriod(Ctx ctx, int C_Calendar_ID, Timestamp Date, Trx trx) {
    if (Date == null) {
      s_log.warning("No Date");
      return null;
    }
    if (C_Calendar_ID == 0) {
      s_log.warning("No Calendar");
      return null;
    }

    //	Get it from DB
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    MPeriod retValue = null;
    String sql =
        "SELECT * FROM C_Period "
            + "WHERE C_Year_ID IN "
            + "(SELECT C_Year_ID FROM C_Year WHERE C_Calendar_ID=?)"
            + " AND ? BETWEEN TRUNC(StartDate,'DD') AND TRUNC(EndDate,'DD')"
            + " AND IsActive='Y' ";
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, C_Calendar_ID);
      pstmt.setTimestamp(2, TimeUtil.getDay(Date));
      rs = pstmt.executeQuery();
      if (rs.next()) {
        retValue = new MPeriod(ctx, rs, trx);
      }
    } catch (SQLException e) {
      s_log.log(Level.SEVERE, "DateAcct=" + Date, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    if (retValue == null)
      s_log.warning("No Period for " + Date + " (C_Calendar_ID=" + C_Calendar_ID + ")");
    return retValue;
  } //	getPeriod
Esempio n. 9
0
  /**
   * gets all Periods in the Range
   *
   * @param startPeriod
   * @param endPeriod
   * @param calendar_ID
   * @return MPeriod[]
   */
  public static MPeriod[] getAllPeriodsInRange(
      MPeriod startPeriod, MPeriod endPeriod, int calendar_ID, Ctx ctx, Trx trx) {
    if ((startPeriod.getC_Calendar_ID() != calendar_ID)
        || (endPeriod.getC_Calendar_ID() != calendar_ID)) {
      log.saveError("Error", "Periods do not belong to the calendar");
      return null;
    }

    ArrayList<MPeriod> periods = new ArrayList<MPeriod>();
    String sql =
        "SELECT * FROM C_Period WHERE "
            + "C_Period.IsActive='Y' AND PeriodType='S' "
            + "AND C_Period.C_Year_ID IN "
            + "(SELECT C_Year_ID FROM C_Year WHERE C_Year.C_Calendar_ID = ? ) "
            + // calendar_ID
            "AND ((C_Period.C_Year_ID * 1000) + C_Period.PeriodNo) BETWEEN"
            + " (? * 1000 + ?) AND (? * 1000 + ? )"
            + // start Period year ID, Period Number , End Period Year ID, Period Number
            " ORDER BY C_Period.C_Year_ID ASC, C_Period.PeriodNo ASC";

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, calendar_ID);
      pstmt.setInt(2, startPeriod.getC_Year_ID());
      pstmt.setInt(3, startPeriod.getPeriodNo());
      pstmt.setInt(4, endPeriod.getC_Year_ID());
      pstmt.setInt(5, endPeriod.getPeriodNo());
      rs = pstmt.executeQuery();
      while (rs.next()) periods.add(new MPeriod(ctx, rs, trx));
    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    MPeriod[] retValue = new MPeriod[periods.size()];
    periods.toArray(retValue);
    return retValue;
  }