/** * Get User Goals * @param ctx context * @param AD_User_ID user * @return array of goals */ public static MGoal[] GetUserGoals(Ctx ctx, int AD_User_ID) { if (AD_User_ID < 0) { return(GetTestGoals(ctx)); } List <MGoal> list = new List <MGoal>(); String sql = "SELECT * FROM PA_Goal g " + "WHERE IsActive='Y'" + " AND AD_Client_ID=@ADClientID" // #1 + " AND ((AD_User_ID IS NULL AND AD_Role_ID IS NULL)" + " OR AD_User_ID=@ADUserID" // #2 + " OR EXISTS (SELECT * FROM AD_User_Roles ur " + "WHERE g.AD_User_ID=ur.AD_User_ID AND g.AD_Role_ID=ur.AD_Role_ID AND ur.IsActive='Y')) " + "ORDER BY SeqNo"; DataTable dt; IDataReader idr = null; try { SqlParameter[] param = new SqlParameter[2]; param[0] = new SqlParameter("@ADClientID", ctx.GetAD_Client_ID()); param[1] = new SqlParameter("@ADUserID", AD_User_ID); idr = DataBase.DB.ExecuteReader(sql, null, null); dt = new DataTable(); dt.Load(idr); idr.Close(); foreach (DataRow dr in dt.Rows) { MGoal goal = new MGoal(ctx, dr, null); goal.UpdateGoal(false); list.Add(goal); } } catch (Exception e) { if (idr != null) { idr.Close(); } _log.Log(Level.SEVERE, sql, e); } finally { dt = null; } MGoal[] retValue = new MGoal[list.Count]; retValue = list.ToArray(); return(retValue); }
/** * Get Accessible Goals * @param ctx context * @return array of goals */ public static MGoal[] GetGoals(Ctx ctx) { List <MGoal> list = new List <MGoal>(); String sql = "SELECT * FROM PA_Goal WHERE IsActive='Y' " + "ORDER BY SeqNo"; sql = MRole.GetDefault(ctx, false).AddAccessSQL(sql, "PA_Goal", false, true); // RW to restrict Access DataTable dt = null; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, null); dt = new DataTable(); dt.Load(idr); idr.Close(); foreach (DataRow dr in dt.Rows) { MGoal goal = new MGoal(ctx, dr, null); goal.UpdateGoal(false); list.Add(goal); } } catch (Exception e) { if (idr != null) { idr.Close(); } _log.Log(Level.SEVERE, sql, e); } finally { dt = null; } MGoal[] retValue = new MGoal[list.Count]; retValue = list.ToArray(); return(retValue); }