private void InstantiateProgramsGrid(int universityId)
{
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Group", typeof(string));
table.Columns.Add("Direction", typeof(string));
table.Columns.Add("Program", typeof(string));
table.Columns.Add("Presentation of content", typeof(string));
table.Columns.Add("Clarity of expectations", typeof(string));
table.Columns.Add("Clear feedback on performance", typeof(string));
table.Columns.Add("Encouragment of participation/discussion", typeof(string));
table.Columns.Add("Overall teaching effectiveness", typeof(string));
table.Columns.Add("How satisfied were you with this course", typeof(string));
var programmes = SqlDriver.Fetch($"SELECT studyProgramId,[group],direction,program,city," +
$"avg(presentation) as presentation,avg(clarity) as clarity,avg(feedback) as feedback, avg(encouragement) as encouragement,avg(effectiveness) as effectiveness,avg(satisfaction) as satisfaction " +
$"FROM studyProgrammes left join courseReviews on studyProgramId=courseId" +
$" WHERE universityId = {universityId} group by studyProgramId,[group], direction, program, city");
foreach (Dictionary <string, object> row in programmes)
{
table.Rows.Add(row["studyProgramId"], row["group"], row["direction"], row["program"], row["presentation"], row["clarity"], row["feedback"], row["encouragement"], row["effectiveness"], row["satisfaction"]);
}
programmesGrid.DataSource = null;
programmesGrid.Rows.Clear();
programmesGrid.DataSource = table;
}
public void ChangeUniversity(string selectedUniversity)
{
if (!selectedUniversity.Equals(GetCurrentUniversity()))
{
var sqlGetNewUniversityID = "select universityid from universities where name ='" + selectedUniversity + "';";
var newUniversityIdFromDB = SqlDriver.Fetch(sqlGetNewUniversityID);
var newUniversityId = ((Dictionary <string, object>)newUniversityIdFromDB[0])["universityId"].ToString();
var sqlUpdateUniversityID = "update users set universityid =" + newUniversityId + " where username ='******';";
try
{
if (SqlDriver.Execute(sqlUpdateUniversityID))
{
MessageBox.Show(Messages.universityUpdateSuccess);
Logger.Log(Messages.universityUpdateSuccess);
}
else
{
MessageBox.Show(Messages.universityUpdateFailed);
Logger.Log(Messages.universityUpdateFailed);
}
}
catch (Exception ex)
{
Logger.Log(ex.Message);
}
}
else
{
MessageBox.Show(Messages.newUniversitySameAsOld);
Logger.Log(Messages.newUniversitySameAsOld);
}
}
public List <Dictionary <string, object> > GetAllReviews()
{
return(SqlDriver.Fetch($"SELECT users.status, studyProgrammes.program, courseReviews.review," +
$" courseReviews.date FROM courseReviews, studyProgrammes, users WHERE review IS NOT NULL AND " +
$"studyProgrammes.studyProgramId=users.courseId AND users.userId=courseReviews.userId " +
$"AND courseReviews.courseId={StudyProgramId}"));
}
public List <Dictionary <string, object> > RecommendedCourses(UserModel user)
{
string cities, groups, directions;
if (user.CityPreferences != null)
{
cities = user.CityPreferences.Replace(",", "\",\"");
cities = "\"" + cities + "\"";
}
else
{
cities = "";
}
if (user.GroupPreferences != null)
{
groups = user.GroupPreferences.Replace(",", "\",\"");
groups = "\"" + groups + "\"";
directions = user.DirectionPreferences.Replace(",", "\",\"");
directions = "\"" + directions + "\"";
if (cities != "")
{
return(SqlDriver.Fetch($"SELECT * FROM studyProgrammes WHERE city IN ({cities}) AND \"group\" IN ({groups}) AND direction IN ({directions})"));
}
else
{
return(SqlDriver.Fetch($"SELECT * FROM studyProgrammes WHERE \"group\" IN ({groups}) AND direction IN ({directions})"));
}
}
;
return(null);
}
private bool ValidateFields(string username, string password)
{
var hashedPassword = PasswordHasher.CreateMD5(password);
var credentialResult = SqlDriver.Fetch($"SELECT username, password FROM users WHERE username='******' AND password='******';");
return(credentialResult != null && credentialResult.Count == 1);
}
private void InstantiateGrid()
{
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Variety of courses", typeof(string));
table.Columns.Add("Availability of extracurricular activities ", typeof(string));
table.Columns.Add("Access to faculty", typeof(string));
table.Columns.Add("Quality of academic facilities (library, PCs, etc.)", typeof(string));
table.Columns.Add("Student unions", typeof(string));
table.Columns.Add("Cost of studying", typeof(string));
var universities = SqlDriver.Fetch("SELECT u.universityId,name,avg(variety) as variety,avg(availability) as availability,avg(accessability) as accessability,avg(quality) as quality,avg(unions) as unions,avg(cost) as cost " +
"FROM universities u LEFT JOIN universityReviews ur ON u.universityId=ur.universityId " +
"GROUP BY u.universityId,name");
foreach (Dictionary <string, object> row in universities)
{
table.Rows.Add(row["universityId"], row["name"], row["variety"], row["availability"], row["accessability"], row["quality"], row["unions"], row["cost"]);
}
universitiesGrid.DataSource = null;
universitiesGrid.Rows.Clear();
universitiesGrid.DataSource = table;
}
public List <Dictionary <string, object> > GetUniversitiesWithRatings()
{
return(SqlDriver.Fetch("SELECT u.universityId, ud.wikipedia_link, ud.rank_country, ud.rank_world, u.image, u.address, name, 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 universities u LEFT JOIN universityReviews ur ON u.universityId=ur.universityId LEFT JOIN university_details_lt ud ON u.universityId=ud.universityId " +
"GROUP BY u.universityId,name"));
}
public List <Dictionary <string, object> > GetCoursesWithRatings()
{
return(SqlDriver.Fetch($"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 studyProgrammes left join courseReviews on studyProgramId=courseId" +
$" WHERE universityId = {UniversityId} group by studyProgramId,[group], direction, program, city"));
}
public int QuestionsCount()
{
var questionsCount = SqlDriver.Fetch("SELECT * FROM questions WHERE userId =" + UserId + "; ");
if (questionsCount != null)
{
return(questionsCount.Count);
}
return(0);
}
public int AnswersCount()
{
var answersCount = SqlDriver.Fetch("SELECT * FROM answers WHERE userId =" + UserId + "; ");
if (answersCount != null)
{
return(answersCount.Count);
}
return(0);
}
public List <String> GetUserInfo()
{
var sqlUserInfo = SqlDriver.Fetch("select email, first_name, last_name, universities.name, status from universities, users where users.universityid = universities.universityId and username = '******';");
var userInfo = new List <string>();
userInfo.Add(((Dictionary <string, object>)sqlUserInfo[0])["email"].ToString());
userInfo.Add(((Dictionary <string, object>)sqlUserInfo[0])["first_name"].ToString());
userInfo.Add(((Dictionary <string, object>)sqlUserInfo[0])["last_name"].ToString());
userInfo.Add(((Dictionary <string, object>)sqlUserInfo[0])["name"].ToString());
userInfo.Add(((Dictionary <string, object>)sqlUserInfo[0])["status"].ToString());
return(userInfo);
}
public List <String> GetAllUniversities()
{
var universityResult = SqlDriver.Fetch("SELECT name FROM universities");
var universityList = new List <string>();
if (universityResult.Count != 0)
{
foreach (Dictionary <string, object> row in universityResult)
{
universityList.Add(row["name"].ToString());
}
}
return(universityList);
}
public List <SelectListItem> GetAllCourses()
{
var courseResult = SqlDriver.Fetch("Select program FROM studyProgrammes");
var courses = new List <SelectListItem>();
if (courseResult.Count != 0)
{
foreach (Dictionary <string, object> row in courseResult)
{
courses.Add(new SelectListItem(row["program"].ToString(), row["program"].ToString()));
}
}
return(courses);
}
public List <SelectListItem> GetAllUniversities()
{
var universityResult = SqlDriver.Fetch("SELECT name, universityId FROM universities");
var universities = new List <SelectListItem>();
if (universityResult.Count != 0)
{
foreach (Dictionary <string, object> row in universityResult)
{
universities.Add(new SelectListItem(row["name"].ToString(), row["name"].ToString()));
}
}
return(universities);
}
public List <SelectListItem> GetCourses(int userId)
{
var courseResult = SqlDriver.Fetch("SELECT * FROM studyProgrammes, users WHERE studyProgrammes.universityId = users.universityId AND users.userId =" + userId.ToString() + "; ");
var courses = new List <SelectListItem>();
if (courseResult.Count != 0)
{
foreach (Dictionary <string, object> row in courseResult)
{
courses.Add(new SelectListItem(row["program"].ToString(), row["program"].ToString()));
}
}
return(courses);
}
public List <CourseModel> GetBookmarkedCourses()
{
var courses = new List <CourseModel>();
var sqlData = SqlDriver.Fetch($"Select * FROM studyProgrammes WHERE studyProgramId IN ({CourseBookmarks})");
if (sqlData == null)
{
return(courses);
}
foreach (var course in sqlData)
{
courses.Add(new CourseModel(Convert.ToInt32(course["studyProgramId"].ToString())));
}
return(courses);
}
public MapModel(string address, string showOnMap = "", double range = 0) //if range = 0, show all locations
{
var geoCoordinate = GetCoordinates(ConstructApiUrl(address));
MapCenter = new GeoCoordinate(geoCoordinate.Item1, geoCoordinate.Item2);
Range = range;
Address = address;
if (showOnMap == "Universities")
{
LocationsInRange = GetLocationsInRangeMarkers(SqlDriver.Fetch("SELECT name,latitude,longitude,universityId FROM universities"));
}
else
{
LocationsInRange = new List <MarkerModel>();
}
}
public List <ImageModel> GetAllIcons()
{
var sqlSelectIcons = SqlDriver.Fetch("SELECT * FROM icons");
var iconsList = new List <ImageModel>();
foreach (var icon in sqlSelectIcons)
{
var imageId = Convert.ToInt32(icon["iconId"].ToString());
var imageAddress = icon["iconAddress"].ToString();
var title = icon["title"].ToString();
var authorCredit = icon["credit"].ToString();
var newIcon = new ImageModel {
ImageId = imageId,
ImageAddress = imageAddress,
Title = title,
AuthorCredit = authorCredit,
};
iconsList.Add(newIcon);
}
return(iconsList);
}
private bool ValidateFields(string username, string email)
{
var credentialResult = SqlDriver.Fetch($"SELECT username, email FROM users WHERE username='******' AND email='{email}';");
return(credentialResult != null && credentialResult.Count == 1);
}
public List <Dictionary <string, object> > DirectionList()
{
return(SqlDriver.Fetch("SELECT DISTINCT(direction) FROM studyProgrammes"));
}
public List <Dictionary <string, object> > GroupList()
{
return(SqlDriver.Fetch("SELECT * FROM courseGroup"));
}
public List <Dictionary <string, object> > GetCourses()
{
return(SqlDriver.Fetch($"SELECT * FROM studyProgrammes WHERE universityId = {UniversityId}"));
}
public List <Dictionary <string, object> > CoursesList()
{
var sqlData = SqlDriver.Fetch("SELECT * FROM studyProgrammes");
return(sqlData);
}
public List <Dictionary <string, object> > GetAllCoursesWithUniversityNames()
{
return(SqlDriver.Fetch("SELECT courses.program, courses.studyProgramId, uni.name " +
"FROM studyProgrammes courses " +
"LEFT JOIN universities uni WHERE courses.universityId = uni.universityId"));
}
public List <Dictionary <string, object> > GetUserById(object userId)
{
int user = Convert.ToInt32(userId);
return(SqlDriver.Fetch($"SELECT first_name, last_name, status FROM users WHERE userId={user}"));
}
public string GetCurrentSetting(string setting)
{
var settingFromDB = SqlDriver.Fetch("SELECT " + setting + " from users where username='******';");
return(((Dictionary <string, object>)settingFromDB[0])[setting].ToString());
}
public List <Dictionary <string, object> > GetUniversities()
{
return(SqlDriver.Fetch("SELECT * FROM universities"));
}
public string GetCurrentUniversity()
{
var universityIdFromDB = SqlDriver.Fetch("select universities.name from universities, users where universities.universityId = users.universityid and users.username = '******';");
return(((Dictionary <string, object>)universityIdFromDB[0])["name"].ToString());
}
private string GetUserId(string username)
{
var user = (Dictionary <string, object>)SqlDriver.Fetch($"SELECT userId FROM users WHERE username = '******'")[0];
return(user["userId"].ToString());
}
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();
}
}