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);
}
List <MPeriod> periods = new List <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 = @param1 ) " + //calendar_ID
"AND ((C_Period.C_Year_ID * 1000) + C_Period.PeriodNo) BETWEEN" +
" (@param2 * 1000 + @param3) AND (@param4 * 1000 + @param5 )" + //start Period year ID, Period Number , End Period Year ID, Period Number
" ORDER BY C_Period.C_Year_ID ASC, C_Period.PeriodNo ASC";
SqlParameter[] param = null;
IDataReader idr = null;
DataTable dt = new DataTable();
try
{
param = new SqlParameter[5];
param[0] = new SqlParameter("@param1", calendar_ID);
param[1] = new SqlParameter("@param2", startPeriod.GetC_Year_ID());
param[2] = new SqlParameter("@param3", startPeriod.GetPeriodNo());
param[3] = new SqlParameter("@param4", endPeriod.GetC_Year_ID());
param[4] = new SqlParameter("@param5", endPeriod.GetPeriodNo());
idr = DB.ExecuteReader(sql, param, trx);
dt.Load(idr);
if (idr != null)
{
idr.Close();
idr = null;
}
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
periods.Add(new MPeriod(ctx, dt.Rows[i], trx));
}
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
idr = null;
}
_log.Log(Level.SEVERE, sql, e);
}
MPeriod[] retValue = new MPeriod[periods.Count];
retValue = periods.ToArray();
return(retValue);
}
public static MPeriod GetNextPeriod(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 = @param1 ) " +
"AND ((C_Period.C_Year_ID * 1000) + C_Period.PeriodNo) " +
" > ((@param2 * 1000) + @param3) ORDER BY C_Period.C_Year_ID ASC, C_Period.PeriodNo ASC";
IDataReader idr = null;
SqlParameter[] param = null;
DataTable dt = new DataTable();
try
{
param = new SqlParameter[3];
param[0] = new SqlParameter("@param1", period.GetC_Calendar_ID());
param[1] = new SqlParameter("@param2", period.GetC_Year_ID());
param[2] = new SqlParameter("@param3", period.GetPeriodNo());
idr = DB.ExecuteReader(sql, param, null);
dt.Load(idr);
if (idr != null)
{
idr.Close();
idr = null;
}
if (dt.Rows.Count > 0)
{
newPeriod = new MPeriod(ctx, dt.Rows[0], trx);
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
idr = null;
}
_log.Log(Level.SEVERE, sql, e);
}
return(newPeriod);
}