public static XDoc AppendRatingXml(XDoc doc, PageBE page, UserBE user) { bool endDoc = false; if (doc == null) { doc = new XDoc("rating"); } else { doc.Start("rating"); endDoc = true; } RatingComputedBE ratingEffective = DbUtils.CurrentSession.Rating_GetResourceRating(page.ID, RatingBE.Type.PAGE); RatingBE userRating = null; if (ratingEffective != null) { doc.Attr("score", ratingEffective.Score) .Attr("score.trend", ratingEffective.ScoreTrend) .Attr("count", ratingEffective.Count) .Attr("date", ratingEffective.Timestamp); userRating = DbUtils.CurrentSession.Rating_GetUserResourceRating(user.ID, page.ID, RatingBE.Type.PAGE); if (userRating != null) { doc.Start("user.ratedby") .Attr("id", user.ID) .Attr("score", userRating.Score.ToString()) .Attr("date", userRating.Timestamp) .Attr("href", UserBL.GetUri(user)) .End(); } } else { // No ratings exist for page: Output placeholder doc.Attr("score", string.Empty) .Attr("count", 0); } if (endDoc) { doc.End(); // rating } return(doc); }
private RatingBE Rating_PopulateRating(IDataReader dr) { RatingBE r = new RatingBE { Id = dr.Read <uint>("rating_id"), UserId = dr.Read <uint>("rating_user_id"), ResourceId = dr.Read <uint>("rating_resource_id"), ResourceType = (RatingBE.Type)dr.Read <byte>("rating_resource_type"), ResourceRevision = dr.Read <ulong?>("rating_resource_revision", null), Score = dr.Read <float>("rating_score"), Timestamp = dr.Read <DateTime>("rating_timestamp"), TimestampReset = dr.Read <DateTime?>("rating_reset_timestamp", null) }; return(r); }
public void Rating_ResetUserResourceRating(uint userId, ulong resourceId, RatingBE.Type resourceType, DateTime resetTimestamp) { Catalog.NewQuery(@" /* RatingDA::Rating_ResetUserResourceRating */ UPDATE ratings SET rating_reset_timestamp = ?RATING_RESET_TIMESTAMP WHERE rating_resource_id = ?RATING_RESOURCE_ID AND rating_resource_type = ?RATING_RESOURCE_TYPE AND rating_user_id = ?RATING_USER_ID AND rating_reset_timestamp is NULL;") .With("RATING_RESOURCE_ID", resourceId) .With("RATING_RESOURCE_TYPE", (byte)resourceType) .With("RATING_USER_ID", userId) .With("RATING_RESET_TIMESTAMP", resetTimestamp) .Execute(); Rating_Compute(resourceId, resourceType, resetTimestamp); }
//--- Methods --- public static void SetRating(PageBE page, UserBE user, float?score) { ThrowOnInvalidLicense(); RatingBE currentRating = DbUtils.CurrentSession.Rating_GetUserResourceRating(user.ID, page.ID, RatingBE.Type.PAGE); if (score == null) { if (currentRating == null) { // no rating exists currently: noop return; } // reset a user ratings for a page DbUtils.CurrentSession.Rating_ResetUserResourceRating(user.ID, page.ID, RatingBE.Type.PAGE, DekiContext.Current.Now); } else { // set or update a page rating // Valid score is limited to 0 and 1. if (score != 0 && score != 1) { throw new RatingInvalidArgumentException(); } if (currentRating != null && currentRating.Score == score) { // an equal score already exists: noop return; } RatingBE rating = new RatingBE(); rating.ResourceId = page.ID; rating.ResourceType = RatingBE.Type.PAGE; rating.ResourceRevision = page.Revision; rating.Timestamp = DekiContext.Current.Now; rating.TimestampReset = null; rating.UserId = user.ID; rating.Score = score.Value; // Set a new rating DbUtils.CurrentSession.Rating_Insert(rating); } // Trigger a notification DekiContext.Current.Instance.EventSink.PageRated(DekiContext.Current.Now, page, user); }
public RatingComputedBE Rating_GetResourceRating(ulong resourceId, RatingBE.Type resourceType) { RatingComputedBE rc = null; string query = @" /* RatingDA::Rating_GetResourceRating */ SELECT * FROM ratingscomputed WHERE ratingscomputed_resource_id = ?RATINGSCOMPUTED_RESOURCE_ID AND ratingscomputed_resource_type = ?RATINGSCOMPUTED_RESOURCE_TYPE; "; Catalog.NewQuery(query) .With("RATINGSCOMPUTED_RESOURCE_ID", resourceId) .With("RATINGSCOMPUTED_RESOURCE_TYPE", (byte)resourceType) .Execute(delegate(IDataReader dr) { if(dr.Read()) { rc = Rating_PopulateRatingComputed(dr); } }); return rc; }
private const int RATING_TREND_SIZE = 10; // trend score includes this many recent ratings public void Rating_Insert(RatingBE rating) { // Reset previous rating Rating_ResetUserResourceRating(rating.UserId, rating.ResourceId, rating.ResourceType, rating.Timestamp); // Insert new rating Catalog.NewQuery(@" /* RatingDA::Rating_Insert */ INSERT INTO ratings (rating_user_id, rating_resource_id, rating_resource_type, rating_resource_revision, rating_score, rating_timestamp, rating_reset_timestamp) VALUES (?RATING_USER_ID, ?RATING_RESOURCE_ID, ?RATING_RESOURCE_TYPE, ?RATING_RESOURCE_REVISION, ?RATING_SCORE, ?RATING_TIMESTAMP, ?RATING_RESET_TIMESTAMP); ") .With("RATING_RESOURCE_ID", rating.ResourceId) .With("RATING_RESOURCE_TYPE", (byte)rating.ResourceType) .With("RATING_USER_ID", rating.UserId) .With("RATING_RESOURCE_REVISION", rating.ResourceRevision) .With("RATING_SCORE", rating.Score) .With("RATING_TIMESTAMP", rating.Timestamp) .With("RATING_RESET_TIMESTAMP", rating.TimestampReset) .Execute(); // Compute new rating and trend Rating_Compute(rating.ResourceId, rating.ResourceType, rating.Timestamp); }
public RatingBE Rating_GetUserResourceRating(uint userId, ulong resourceId, RatingBE.Type resourceType) { RatingBE r = null; string query = @" /* RatingDA::Rating_GetUserResourceRating */ SELECT * FROM ratings WHERE rating_resource_id = ?RATING_RESOURCE_ID AND rating_resource_type = ?RATING_RESOURCE_TYPE AND rating_user_id = ?RATING_USER_ID AND rating_reset_timestamp is NULL; "; Catalog.NewQuery(query) .With("RATING_RESOURCE_ID", resourceId) .With("RATING_RESOURCE_TYPE", (byte)resourceType) .With("RATING_USER_ID", userId) .Execute(delegate(IDataReader dr) { if (dr.Read()) { r = Rating_PopulateRating(dr); } }); return(r); }
public RatingBE Rating_GetUserResourceRating(uint userId, ulong resourceId, RatingBE.Type resourceType) { RatingBE r = null; string query = @" /* RatingDA::Rating_GetUserResourceRating */ SELECT * FROM ratings WHERE rating_resource_id = ?RATING_RESOURCE_ID AND rating_resource_type = ?RATING_RESOURCE_TYPE AND rating_user_id = ?RATING_USER_ID AND rating_reset_timestamp is NULL; "; Catalog.NewQuery(query) .With("RATING_RESOURCE_ID", resourceId) .With("RATING_RESOURCE_TYPE", (byte)resourceType) .With("RATING_USER_ID", userId) .Execute(delegate(IDataReader dr) { if(dr.Read()) { r = Rating_PopulateRating(dr); } }); return r; }
private RatingBE Rating_PopulateRating(IDataReader dr) { RatingBE r = new RatingBE { Id = dr.Read<uint>("rating_id"), UserId = dr.Read<uint>("rating_user_id"), ResourceId = dr.Read<uint>("rating_resource_id"), ResourceType = (RatingBE.Type)dr.Read<byte>("rating_resource_type"), ResourceRevision = dr.Read<ulong?>("rating_resource_revision", null), Score = dr.Read<float>("rating_score"), Timestamp = dr.Read<DateTime>("rating_timestamp"), TimestampReset = dr.Read<DateTime?>("rating_reset_timestamp", null) }; return r; }
private void Rating_Compute(ulong resourceId, RatingBE.Type resourceType, DateTime timestamp) { int sumRatingScore = 0; int ratingsCount = 0; int trendSumRatingScore = 0; int trendRatingsCount = 0; Catalog.NewQuery(@" /* RatingDA::Rating_Compute: determine score */ SELECT CAST(SUM(rating_score) as UNSIGNED INTEGER) as sum_rating_score, COUNT(*) as count_ratings FROM ratings WHERE rating_resource_id = ?RATING_RESOURCE_ID AND rating_resource_type = ?RATING_RESOURCE_TYPE AND rating_reset_timestamp IS NULL; SELECT CAST(SUM(rating_score) as UNSIGNED INTEGER) as trend_sum_rating_score, COUNT(*) as trend_count_ratings FROM ratings WHERE rating_resource_id = ?RATING_RESOURCE_ID AND rating_resource_type = ?RATING_RESOURCE_TYPE AND rating_reset_timestamp IS NULL ORDER BY rating_timestamp DESC LIMIT ?TREND_SIZE; ") .With("RATING_RESOURCE_ID", resourceId) .With("RATING_RESOURCE_TYPE", (byte)resourceType) .With("TREND_SIZE", RATING_TREND_SIZE) .Execute(delegate(IDataReader dr) { if(dr.Read()) { sumRatingScore = dr.Read<int>("sum_rating_score", 0); ratingsCount = dr.Read<int>("count_ratings", 0); } if(dr.NextResult() && dr.Read()) { trendSumRatingScore = dr.Read<int>("trend_sum_rating_score", 0); trendRatingsCount = dr.Read<int>("trend_count_ratings", 0); } }); if(ratingsCount > 0 && trendRatingsCount > 0) { float score = (float)sumRatingScore / (float)ratingsCount; float scoreTrend = (float)trendSumRatingScore / (float)trendRatingsCount; Catalog.NewQuery(@" /* RatingDA::Rating_Compute: set computed rating */ REPLACE INTO ratingscomputed SET ratingscomputed_resource_id = ?RATING_RESOURCE_ID, ratingscomputed_resource_type = ?RATING_RESOURCE_TYPE, ratingscomputed_timestamp = ?RATING_TIMESTAMP, ratingscomputed_score = ?RATING_SCORE, ratingscomputed_count = ?RATING_COUNT, ratingscomputed_score_trend = ?RATING_SCORE_TREND; ") .With("RATING_RESOURCE_ID", resourceId) .With("RATING_RESOURCE_TYPE", (byte)resourceType) .With("RATING_TIMESTAMP", timestamp) .With("RATING_SCORE", score) .With("RATING_COUNT", ratingsCount) .With("RATING_SCORE_TREND", scoreTrend) .Execute(); } else { // if no active ratings exist for a page, remove the computed rating Catalog.NewQuery(@" /* RatingDA::Rating_Compute: delete computed rating */ DELETE FROM ratingscomputed WHERE ratingscomputed_resource_id = ?RATING_RESOURCE_ID AND ratingscomputed_resource_type = ?RATING_RESOURCE_TYPE; ") .With("RATING_RESOURCE_ID", resourceId) .With("RATING_RESOURCE_TYPE", (byte)resourceType) .Execute(); } }