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