/**
* 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;
}
/**
* 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;
}
/**
* 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;
}
/**
* 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
/**
* 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
/**
* 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;
}
/**
* 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
/**
* 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
