public static RatingHistory Select(int ID, Database pDb = null)
{
Database db;
if (pDb == null)
{
db = new Database();
db.Connect();
}
else
{
db = (Database)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT_ID);
command.Parameters.AddWithValue("@ID", ID);
SqlDataReader reader = db.Select(command);
Collection <RatingHistory> ratingHistories = Read(reader);
RatingHistory ratingHistory = null;
if (ratingHistories.Count == 1)
{
ratingHistory = ratingHistories[0];
}
reader.Close();
if (pDb == null)
{
db.Close();
}
return(ratingHistory);
}
public static int Update(RatingHistory ratingHistory, Database pDb = null)
{
Database db;
if (pDb == null)
{
db = new Database();
db.Connect();
}
else
{
db = (Database)pDb;
}
SqlCommand command = db.CreateCommand(SQL_UPDATE);
PrepareCommand(command, ratingHistory);
int ret = db.ExecuteNonQuery(command);
if (pDb == null)
{
db.Close();
}
return(ret);
}
private static void PrepareCommand(SqlCommand command, RatingHistory ratingHistory)
{
command.Parameters.AddWithValue("@ID", ratingHistory.Id);
command.Parameters.AddWithValue("@rate", ratingHistory.Rate);
command.Parameters.AddWithValue("@dateOfChange", ratingHistory.Dateofchange);
command.Parameters.AddWithValue("@comment", ratingHistory.Comment);
command.Parameters.AddWithValue("@Rating_movie_id", ratingHistory.Rating_movie_id);
command.Parameters.AddWithValue("@Rating_userinfo_id", ratingHistory.Rating_userinfo_id);
}
private void SaveRatingHistory()
{
var ratingHistoryChunk = new RatingHistory[ChunkSize];
var count = 0;
var userIdentities = _gameSessions.Identities;
foreach (var game in _gameSessions.Games)
{
if (count < ChunkSize)
{
var userRatingHistory = new RatingHistory
{
UserId = userIdentities[game.Key],
ManualStepsCount = game.Value.ManualStepNumber,
RootCounter = (int)game.Value.GameCash.RootCounter.Value,
Data = DateTime.Now,
TotalEarned = game.Value.GameCash.TotalEarned,
};
ratingHistoryChunk[count] = userRatingHistory;
count++;
}
else
{
if (ratingHistoryChunk.FirstOrDefault() != null)
{
_localNodeRatingDataAccess.PersistUsersRatingHistory(ratingHistoryChunk);
ratingHistoryChunk = new RatingHistory[ChunkSize];
count = 0;
}
}
}
if (count < ChunkSize)
{
if (ratingHistoryChunk.FirstOrDefault() != null)
{
_localNodeRatingDataAccess.PersistUsersRatingHistory(ratingHistoryChunk);
ratingHistoryChunk = new RatingHistory[ChunkSize];
count = 0;
}
}
//RatingHistory userRatingHistory;
//foreach (var game in _gameSessions.Games)
//{
// userRatingHistory = new RatingHistory
// {
// UserId = game.Key,
// ManualStepsCount = game.Value.ManualStepNumber,
// RootCounter = (Int32)game.Value.GameCash.RootCounter.Value,
// Data = DateTime.Now,
// TotalEarned = game.Value.GameCash.TotalEarned,
// };
// _localRatingDataAccess.PersistUsersRatingHistory(ratingHistoryChunk);
//};
}
private RatingElementDto CreateRatingElementDto(RatingHistory ratings)
{
var dto = _mapper.Map <RatingElementDto>(ratings);
dto.Url = Url.Link(nameof(GetRating), new { id = ratings.UserId });
//dto.Url = "2";
return(dto);
}
public ActionResult Upload(HttpPostedFileBase file)
{
if (file != null && file.ContentLength > 0)
{
try
{
using (var package = new ExcelPackage())
{
package.Load(file.InputStream);
var sheet = package.Workbook.Worksheets[1];
long userId = Convert.ToInt64(sheet.Cells[2, 2].Value);
long periodId = DatabaseManager.Instance.Periods.Where(x => x.IsPending).FirstOrDefault().Id;
int location = 7; // starting point of restaurant list in document
for (int i = 0; i < DatabaseManager.Instance.Restaurants.Count(); i++)
{
var rating = new RatingHistory();
rating.UserId = userId;
rating.PeriodId = periodId;
rating.RestaurantId = Convert.ToInt64(sheet.Cells[location + i, 1].Value);
rating.Taste = GetExcelValue(sheet.Cells[location + i, 5].Value);
rating.Speed = GetExcelValue(sheet.Cells[location + i, 6].Value);
rating.Service = GetExcelValue(sheet.Cells[location + i, 7].Value);
DatabaseManager.Instance.RatingHistories.Add(rating);
}
DatabaseManager.Instance.SaveChanges();
var pendingUsersCount = (from a in DatabaseManager.Instance.Users
where !DatabaseManager.Instance.RatingHistories.Any(x => x.PeriodId == periodId && x.UserId == a.Id)
select a).Count();
if (pendingUsersCount == 0)
{
var period = DatabaseManager.Instance.Periods.Find(periodId);
period.IsPending = false;
period.IsActive = true;
}
DatabaseManager.Instance.SaveChanges();
TempData["Message"] = "File is uploaded.";
TempData["Result"] = true;
}
}
catch (Exception ex)
{
TempData["Message"] = "File could not be uploaded. Inner message: " + ex.Message;
TempData["Result"] = false;
return(RedirectToAction("Index"));
}
}
return(RedirectToAction("Index"));;
}
public void PersistRatingHistory()
{
var client = new MongoClient("mongodb://*****:*****@10.10.139.148:27017/gdcame?authSource=gdcame");
var database = client.GetDatabase("gdcame");
RatingStatisticsRepository _testStatisticsRating = new RatingStatisticsRepository(database);
RatingHistoryRepository _testHistoryRating = new RatingHistoryRepository(database);
RatingHistory ratingHistory = new RatingHistory
{
Data = new DateTime(2016, 11, 28, 12, 25, 25),
UserId = "accepted",
ManualStepsCount = 122,
RootCounter = 225,
TotalEarned = 928,
};
// _testHistoryRating.PersistRatingHistory(ratingHistory);
}
public int UpdateRating(Recipe recipe, double rating, out double parsedRating)
{
parsedRating = recipe.Rating > 0 ? (recipe.Rating + rating) / 2 : rating;
recipe.Rating = parsedRating;
var rh = new RatingHistory()
{
AccountId = recipe.AccountId,
RecipeId = recipe.RecipeId,
Account = recipe.Account,
Recipe = recipe
};
_context.Add(rh);
_context.Update(recipe);
return(_context.SaveChanges());
}
private static Collection <RatingHistory> Read(SqlDataReader reader)
{
Collection <RatingHistory> ratingHistories = new Collection <RatingHistory>();
while (reader.Read())
{
int i = -1;
RatingHistory ratingHistory = new RatingHistory();
ratingHistory.Id = reader.GetInt32(++i);
ratingHistory.Rate = reader.GetDecimal(++i);
ratingHistory.Dateofchange = reader.GetDateTime(++i);
if (!reader.IsDBNull(++i))
{
ratingHistory.Comment = reader.GetString(i);
}
ratingHistory.Rating_movie_id = reader.GetInt32(++i);
ratingHistory.Rating_userinfo_id = reader.GetInt32(++i);
ratingHistories.Add(ratingHistory);
}
return(ratingHistories);
}
public static void TryAllOperationsOfModel()
{
Database db = new Database();
db.Connect();
Console.WriteLine(" Delete all data from database ");
Console.WriteLine("------------------------------------------------------------------");
DeleteAllDataFromDatabase();
Console.WriteLine("\n\n User ");
Console.WriteLine("------------------------------------------------------------------");
UserInfo userInfo = new UserInfo();
userInfo.Id = 1;
userInfo.Nickname = "sob28";
userInfo.Firstname = "Tonda";
userInfo.Lastname = "Sobota";
userInfo.Email = "*****@*****.**";
userInfo.Points = 42;
userInfo.Rank = "Pokročilý";
userInfo.Sex = null;
userInfo.Country = null;
userInfo.Shortinfo = null;
UserTable.Insert(userInfo, db);
userInfo.Lastname = "Nedela";
UserTable.Update(userInfo, db);
UserTable.UserValuation(); // 5.7 Ocenění uživatelů
UserTable.RecalculatePoints(); // 5.8 Přepočet získaných bodů
Collection <UserInfo> users = UserTable.Select();
foreach (UserInfo user in users)
{
Console.WriteLine(UserTable.Select(user.Id, db).ToString());
}
foreach (UserInfo user in UserTable.SelectUsersWithNumberOfRates(0)) //5.6 Pocet ohodnocených filmu větší než parametr
{
Console.WriteLine("User ID: " + user.Id + ", Number of rates: " + user.NumberOfRates);
}
Console.WriteLine("\n\n Director ");
Console.WriteLine("------------------------------------------------------------------");
Director director = new Director();
director.Id = 1;
director.Firstname = "Jakub";
director.Lastname = "Novák";
director.Nationality = "CZ";
director.Birthplace = "Brno";
director.Height = 1.5m;
DirectorTable.Insert(director, db);
director.Lastname = "Holý";
DirectorTable.Update(director, db);
Collection <Director> directors = DirectorTable.Select();
foreach (Director dir in directors)
{
Console.WriteLine(DirectorTable.Select(dir.Id, db).ToString());
}
Console.WriteLine("\n\n Movie ");
Console.WriteLine("------------------------------------------------------------------");
Movie movie = new Movie();
movie.Id = 1;
movie.Title = "Zmizení";
movie.Year = 2016;
movie.Time = 215;
movie.Language = "*****@*****.**";
movie.Description = "Velice dobrý film";
movie.Country = "USA";
movie.Award = true;
movie.Premiere = new DateTime(2017, 05, 06);
movie.Director_id = director.Id;
MovieTable.Insert(movie, db);
movie.Year = 2018;
MovieTable.Update(movie, db);
Collection <Movie> movies = MovieTable.Select();
foreach (Movie mov in movies)
{
Console.WriteLine(MovieTable.Select(mov.Id, db).ToString());
Console.WriteLine("Actor number: " + MovieTable.Select_Actor_Number(mov.Id)); //1.6 Výpis filmů a k nim počet obsazených herců
}
Console.WriteLine("\n\n Genre ");
Console.WriteLine("------------------------------------------------------------------");
Genre genre = new Genre();
genre.Id = 1;
genre.Name = "Thriller";
genre.Description = "Mrazivý žánr";
GenreTable.Insert(genre, db);
genre.Name = "Horror";
GenreTable.Update(genre, db);
Collection <Genre> genres = GenreTable.Select();
foreach (Genre gen in genres)
{
Console.WriteLine(GenreTable.Select(gen.Id, db).ToString());
}
Console.WriteLine("\n\n Actor ");
Console.WriteLine("------------------------------------------------------------------");
Actor actor = new Actor();
actor.Id = 1;
actor.Firstname = "Čeněk";
actor.Lastname = "Volant";
actor.Nationality = "CZ";
actor.Birthplace = "Ostrava";
actor.Height = 1.5m;
ActorTable.Insert(actor, db);
actor.Firstname = "Jozef";
ActorTable.Update(actor, db);
Collection <Actor> actors = ActorTable.Select();
foreach (Actor act in actors)
{
Console.WriteLine(ActorTable.Select(act.Id, db).ToString());
}
Console.WriteLine("\n\n Rating ");
Console.WriteLine("------------------------------------------------------------------");
Rating rating = new Rating();
rating.Movie_id = 1;
rating.User_id = 1;
rating.Rate = 8.5m;
rating.Dateofadd = new DateTime(2018, 05, 08);
rating.Comment = "Nic moc film";
RatingTable.Insert(rating, db);
rating.Dateofadd = new DateTime(2018, 05, 09);
RatingTable.Update(rating, db);
RatingTable.AddRating(2, 2, rating.Rate, rating.Comment); // 6.5 Přidání hodnocení
Collection <Rating> ratings = RatingTable.Select();
foreach (Rating rate in ratings)
{
Console.WriteLine(RatingTable.SelectMovieID(rate.Movie_id, db).ToString());
}
Console.WriteLine("\n\n Rating History ");
Console.WriteLine("------------------------------------------------------------------");
RatingHistory ratingHistory = new RatingHistory();
ratingHistory.Id = 1;
ratingHistory.Rate = 9.5m;
ratingHistory.Dateofchange = new DateTime(2018, 04, 30);
ratingHistory.Comment = "Uleželo se mi to v hlavě";
ratingHistory.Rating_movie_id = 1;
ratingHistory.Rating_userinfo_id = 1;
RatingHistoryTable.Insert(ratingHistory, db);
ratingHistory.Rate = 9m;
RatingHistoryTable.Update(ratingHistory, db);
Collection <RatingHistory> ratingHistories = RatingHistoryTable.Select();
foreach (RatingHistory ratingHist in ratingHistories)
{
Console.WriteLine(RatingHistoryTable.Select(ratingHist.Id, db).ToString());
}
Console.WriteLine("\n\n Movie Genre ");
Console.WriteLine("------------------------------------------------------------------");
MovieGenre movieGenre = new MovieGenre();
movieGenre.Movie_id = 1;
movieGenre.Genre_id = 1;
movieGenre.dateOfAdd = new DateTime(2016, 03, 22);
MovieGenreTable.Insert(movieGenre, db);
movieGenre.dateOfAdd = new DateTime(2016, 03, 25);
MovieGenreTable.Update(movieGenre, db);
Collection <MovieGenre> movieGenres = MovieGenreTable.Select();
foreach (MovieGenre movieGen in movieGenres)
{
Console.WriteLine(MovieGenreTable.SelectMovieId(movieGen.Movie_id, db).ToString());
}
Console.WriteLine("\n\n Movie Actor ");
Console.WriteLine("------------------------------------------------------------------");
MovieActor movieActor = new MovieActor();
movieActor.Movie_id = 1;
movieActor.Actor_id = 1;
movieActor.Role = "Hlavní hrdina";
movieActor.Fee = 22000;
MovieActorTable.Insert(movieActor, db);
movieActor.Fee = 25000;
MovieActorTable.Update(movieActor, db);
Collection <MovieActor> movieActors = MovieActorTable.Select();
foreach (MovieActor movieAct in movieActors)
{
Console.WriteLine(MovieActorTable.SelectMovieId(movieAct.Movie_id, db).ToString());
}
Console.WriteLine("\n\n");
db.Close();
}
