private bool CheckIfUserExists(UserModel user)
{
var row = SqlDriver.Row($"SELECT username from users where username='******' and email='{user.Email}';");
if (row == null)
{
return(false);
}
else
{
return(true);
}
}
public void UpdateBookmarks()
{
var sqlData = SqlDriver.Row($"SELECT bookmarks FROM users WHERE userid = {UserId}");
var newBookmarks = CourseBookmarks.Split(",");
var oldBookmarks = sqlData["bookmarks"].ToString().Split(",");
var mergedBookmarks = newBookmarks.Union(oldBookmarks).ToArray();
CourseBookmarks = String.Join(",", mergedBookmarks);
CourseBookmarks = CourseBookmarks.TrimEnd(',');
SqlDriver.Execute($"UPDATE users SET bookmarks =@0 WHERE userid=@1;", new ArrayList {
CourseBookmarks, UserId
});
}
public bool ValidateFields(UserModel user)
{
var hashedPassword = _passwordHasher.CreateMD5(user.Password);
Dictionary <string, object> result = SqlDriver.Row($"SELECT userId, universityId, courseId from users WHERE username='******' AND password='******';");
if (result != null && result.Count > 0)
{
user.UserId = (int)(long)result["userId"];
user.UniversityId = (int)(long)result["universityId"];
user.CourseId = (int)(long)result["courseId"];
}
else
{
return(false);
}
return(true);
}
public UserModel(int userId)
{
var sqlUser = SqlDriver.Row($"SELECT username, email, first_name, last_name, universities.name, studyProgrammes.program, status, schoolPreferences, groupPreferences, directionPreferences, cityPreferences, bookmarks FROM universities JOIN users on universities.universityId = users.universityId JOIN studyProgrammes on users.courseId = studyProgrammes.studyProgramId WHERE userId =" + userId.ToString() + ";");
UserId = userId;
Username = sqlUser["username"].ToString();
Email = sqlUser["email"].ToString();
FirstName = sqlUser["first_name"].ToString();
LastName = sqlUser["last_name"].ToString();
University = sqlUser["name"].ToString();
Course = sqlUser["program"].ToString();
Status = sqlUser["status"].ToString();
SchoolSubjectPreferences = sqlUser["schoolPreferences"].ToString();
GroupPreferences = sqlUser["groupPreferences"].ToString();
DirectionPreferences = sqlUser["directionPreferences"].ToString();
CityPreferences = sqlUser["cityPreferences"].ToString();
CourseBookmarks = sqlUser["bookmarks"].ToString();
}
public CourseModel(int studyProgramId)
{
var sqlData = SqlDriver.Row($"SELECT * FROM studyProgrammes WHERE studyProgramId = {studyProgramId}");
if (sqlData != null)
{
Program = sqlData["program"].ToString();
UniversityId = Convert.ToInt32(sqlData["universityId"].ToString());
Direction = sqlData["direction"].ToString();
Group = sqlData["group"].ToString();
City = sqlData["city"].ToString();
}
this.StudyProgramId = studyProgramId;
StudyProgramName = SqlDriver.Row($"SELECT program FROM studyProgrammes WHERE studyProgramId = {studyProgramId}")["program"].ToString();
Description = SqlDriver.Row($"SELECT description FROM studyProgrammes WHERE studyProgramId = {studyProgramId}")["description"].ToString();
var sqlCourseReviews = SqlDriver.Row($"SELECT round(avg(presentation),1) as presentation, round(avg(clarity),1) as clarity," +
$"round(avg(feedback),1) as feedback, round(avg(encouragement),1) as encouragement, round(avg(effectiveness),1) as effectiveness, " +
$"round(avg(satisfaction),1) as satisfaction" +
$" FROM courseReviews WHERE courseId = {studyProgramId};");
if (!String.IsNullOrEmpty(sqlCourseReviews["presentation"].ToString()))
{
Presentation = sqlCourseReviews["presentation"].ToString();
Clarity = sqlCourseReviews["clarity"].ToString();
Feedback = sqlCourseReviews["feedback"].ToString();
Encouragement = sqlCourseReviews["encouragement"].ToString();
Effectiveness = sqlCourseReviews["effectiveness"].ToString();
Satisfaction = sqlCourseReviews["satisfaction"].ToString();
}
else
{
Presentation = "N/A";
Clarity = "N/A";
Feedback = "N/A";
Encouragement = "N/A";
Effectiveness = "N/A";
Satisfaction = "N/A";
}
}
public long CountReviews()
{
return((long)SqlDriver.Row($"SELECT COUNT(*) as count FROM courseReviews WHERE review IS NOT NULL AND courseId={StudyProgramId}")["count"]);
}
public void GetCurrentEmail(int userId)
{
Email = SqlDriver.Row($"SELECT email FROM users WHERE userId= " + userId.ToString() + ";")["email"].ToString();
}
public void GetCurrentPassword(int userId)
{
Password = SqlDriver.Row($"SELECT password FROM users WHERE userId= " + userId.ToString() + ";")["password"].ToString();
}
public CourseReviewModel(int studyProgramId)
{
this.StudyProgramId = studyProgramId;
StudyProgramName = SqlDriver.Row($"SELECT program FROM studyProgrammes WHERE studyProgramId = {studyProgramId}")["program"].ToString();
}
public string GetUniversityName(int universityId)
{
return(SqlDriver.Row($"SELECT name FROM universities WHERE universityId={universityId}")["name"].ToString());
}
public long CountReviews()
{
return((long)SqlDriver.Row($"SELECT COUNT(*) as count FROM universityReviews WHERE review IS NOT NULL AND universityId={UniversityId}")["count"]);
}
public UniversityModel(int universityId)
{
this.UniversityId = universityId;
var sqlUniversity = SqlDriver.Row($"SELECT name, description, image FROM universities WHERE universityId = {universityId};");
var sqlUniversityDetails = SqlDriver.Fetch($"SELECT * FROM university_details_lt WHERE universityId = {universityId}");
UniversityName = sqlUniversity["name"].ToString();
Description = sqlUniversity["description"].ToString();
Image = sqlUniversity["image"].ToString();
var sqlUniversityReviews = SqlDriver.Row($"SELECT round(avg(variety),1) as variety, round(avg(availability),1) as availability," +
$"round(avg(accessability),1) as accessability, round(avg(quality),1) as quality, round(avg(unions),1) as unions, " +
$"round(avg(cost),1) as cost" +
$" FROM universityReviews WHERE universityId = {universityId};");
if (!String.IsNullOrEmpty(sqlUniversityReviews["variety"].ToString()))
{
Variety = sqlUniversityReviews["variety"].ToString();
Availability = sqlUniversityReviews["availability"].ToString();
Accessability = sqlUniversityReviews["accessability"].ToString();
Quality = sqlUniversityReviews["quality"].ToString();
Unions = sqlUniversityReviews["unions"].ToString();
Cost = sqlUniversityReviews["cost"].ToString();
}
else
{
Variety = "N/A";
Availability = "N/A";
Accessability = "N/A";
Quality = "N/A";
Unions = "N/A";
Cost = "N/A";
}
foreach (var university in sqlUniversityDetails)
{
RankCountry = Convert.ToInt32(university["rank_country"].ToString());
RankWorld = Convert.ToInt32(university["rank_world"].ToString());
ControlType = university["control_type"].ToString();
EntityType = university["entity_type"].ToString();
AcademicCalendar = university["academic_calendar"].ToString();
CampusSetting = university["campus_setting"].ToString();
ReligiousAffiliation = university["religious_affiliation"].ToString();
Library = university["library"].ToString();
Housing = university["housing"].ToString();
SportFacilities = university["sport_facilities"].ToString();
FinancialAids = university["financial_aids"].ToString();
StudyAbroad = university["study_abroad"].ToString();
DistanceLearning = university["distance_learning"].ToString();
FacebookLink = university["facebook_link"].ToString();
TwitterLink = university["twitter_link"].ToString();
LinkedinLink = university["linkedin_link"].ToString();
YoutubeLink = university["youtube_link"].ToString();
InstagramLink = university["instagram_link"].ToString();
WikipediaLink = university["wikipedia_link"].ToString();
Acronym = university["acronym"].ToString();
Founded = university["founded"].ToString();
MottoNative = university["motto_native"].ToString();
MottoEnglish = university["motto_english"].ToString();
TelephoneNum = university["tel_nr"].ToString();
FaxNum = university["fax_nr"].ToString();
LocalUndergraduateCost = university["local_undergraduate_cost"].ToString();
InternationalUndergraduateCost = university["international_undergraduate_cost"].ToString();
LocalPostgraduateCost = university["local_postgraduate_cost"].ToString();
InternationalPostgraduateCost = university["international_postgraduate_cost"].ToString();
}
}