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); }