/// <summary> /// Find first Year Period of DateAcct based on Organization Calendar /// </summary> /// <param name="ctx">context</param> /// <param name="dateAcct">date</param> /// <param name="AD_Org_ID">Organization</param> /// <returns>active first Period</returns> public static MPeriod GetFirstInYear(Ctx ctx, DateTime?dateAcct, int AD_Org_ID) { MPeriod retValue = null; // Get Calender ID string qry; int AD_Client_ID = ctx.GetAD_Client_ID(); int Calender_ID = 0; if (AD_Org_ID > 0) { MOrgInfo orgInfo = MOrgInfo.Get(ctx, AD_Org_ID, null); if (orgInfo.Get_ColumnIndex("C_Calendar_ID") >= 0) { Calender_ID = orgInfo.GetC_Calendar_ID(); } } if (Calender_ID == 0) { qry = "SELECT C_Calendar_ID FROM AD_ClientInfo WHERE IsActive = 'Y' AND AD_Client_ID=" + AD_Client_ID; Calender_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry.ToString())); } if (Calender_ID == 0) { return(null); } String sql = "SELECT * " + "FROM C_Period " + "WHERE C_Year_ID IN " + "(SELECT C_Year_ID FROM C_Year WHERE IsActive = 'Y' AND C_Calendar_ID= @calendarID)" + " AND @date BETWEEN StartDate AND EndDate)" + " AND IsActive='Y' AND PeriodType='S' " + "ORDER BY StartDate"; try { SqlParameter[] param = new SqlParameter[2]; param[0] = new SqlParameter("@calendarID", Calender_ID); param[1] = new SqlParameter("@date", dateAcct); DataSet ds = DataBase.DB.ExecuteDataset(sql, param, null); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { DataRow dr = ds.Tables[0].Rows[0]; // first only retValue = new MPeriod(ctx, dr, null); } } catch (Exception e) { _log.Log(Level.SEVERE, sql, e); } return(retValue); }
/// <summary> /// Find standard Period of DateAcct based on Organization Calendar /// </summary> /// <param name="ctx">context</param> /// <param name="dateAcct">date</param> /// <param name="AD_Org_ID">organization</param> /// <returns>active Period or null</returns> public static MPeriod Get(Ctx ctx, DateTime?dateAcct, int AD_Org_ID) { if (dateAcct == null) { return(null); } // Search in Cache first //IEnumerator<MPeriod> it = cache.Values.GetEnumerator(); //it.Reset(); //while (it.MoveNext()) //{ // MPeriod period = it.Current; // if (period.GetAD_Client_ID() == ctx.GetAD_Client_ID() && period.IsStandardPeriod() && period.IsInPeriod((DateTime?)dateAcct)) // return period; //} // Get Calender ID StringBuilder qry = new StringBuilder(""); int Calender_ID = 0; int AD_Client_ID = ctx.GetAD_Client_ID(); if (AD_Org_ID > 0) { MOrgInfo orgInfo = MOrgInfo.Get(ctx, AD_Org_ID, null); if (orgInfo.Get_ColumnIndex("C_Calendar_ID") >= 0) { Calender_ID = orgInfo.GetC_Calendar_ID(); } } if (Calender_ID == 0) { qry.Append("SELECT C_Calendar_ID FROM AD_ClientInfo WHERE IsActive = 'Y' AND AD_Client_ID=" + AD_Client_ID); Calender_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry.ToString())); } if (Calender_ID == 0) { return(null); } // 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 IsActive = 'Y' AND C_Calendar_ID= @calendarID)" + " AND @dateAcc BETWEEN TRUNC(StartDate,'DD') AND TRUNC(EndDate,'DD')" + " AND IsActive='Y' AND PeriodType='S'"; try { //DateTime? dt = ((DateTime?)dateAcct).Date; DateTime?dt = ((DateTime?)dateAcct).Value.Date; SqlParameter[] param = new SqlParameter[2]; param[0] = new SqlParameter("@calendarID", Calender_ID); param[1] = new SqlParameter("@dateAcc", TimeUtil.GetDay((DateTime?)dateAcct)); DataSet ds = DataBase.DB.ExecuteDataset(sql, param, null); if (ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { MPeriod period = new MPeriod(ctx, dr, null); int key = period.GetC_Period_ID(); //cache.Add(key, period); if (period.IsStandardPeriod()) { retValue = period; } } } } catch (Exception e) { _log.Log(Level.SEVERE, "dateAcct=" + dateAcct, e); } if (retValue == null) { _log.Warning("No Standard Period for " + dateAcct + " (AD_Client_ID=" + AD_Client_ID + ")"); } return(retValue); }