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