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