public void Delete(int id)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
conn.Execute($"DELETE FROM {TableName} WHERE Id = @id", new { Id = id });
}
}
public virtual IEnumerable <TModel> Get()
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
return(conn.Query <TModel>($"SELECT * FROM {TableName}"));
}
}
public virtual TModel Get(int id)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
return(conn.QuerySingle <TModel>($@"SELECT * FROM {TableName}
WHERE Id = @id", new { Id = id }));
}
}
public List <SectionTimeModel> GetBySection(SectionModel section)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = $"SELECT * FROM SectionTimes WHERE SectionId = @Id";
return(conn.Query <SectionTimeModel>(query, section).ToList());
}
}
public List <SectionModel> GetByFaculty(FacultyUserModel faculty)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = $"SELECT * FROM Sections WHERE FacultyId = @Id";
return(conn.Query <SectionModel>(query, faculty).ToList());
}
}
public List <StudentUserModel> GetBySection(SectionModel section)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"SELECT * FROM Users where Id in (SELECT StudentId FROM SectionStudents where SectionId = @Id);";
return(conn.Query <StudentUserModel>(query, section).ToList());
}
}
public FacultyUserModel UpdateInfo(FacultyUserModel model)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"UPDATE Users SET FirstName = @FirstName, LastName = @LastName, Email = @Email WHERE Id = @Id";
conn.Execute(query, model);
}
return(model);
}
public void RemoveAllBySection(int SectionId)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"DELETE FROM SectionStudents where SectionId = @SectionId;";
var parameters = new DynamicParameters();
parameters.Add("@SectionID", SectionId);
conn.ExecuteScalar <int>(query, parameters);
}
}
public override SectionModel Update(SectionModel model)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"UPDATE Sections SET SectionName = @SectionName WHERE Id = @Id";
conn.Execute(query, model);
}
return(model);
}
public FacultyUserModel GetPasswordByUser(FacultyUserModel model)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"SELECT Password FROM Users where Id = @Id;";
var parameters = new DynamicParameters();
parameters.Add("@Id", model.Id);
return(conn.QuerySingle <FacultyUserModel>(query, parameters));
}
}
public List <ClassModel> GetBySectionId(int SectionId)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"SELECT * FROM Classes where SectionId = @SectionId;";
var parameters = new DynamicParameters();
parameters.Add("@SectionId", SectionId);
return(conn.Query <ClassModel>(query, parameters).ToList());
}
}
public void DeleteAllBySection(int SectionId)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"DELETE FROM Classes WHERE SectionId = @SectionId;";
var parameters = new DynamicParameters();
parameters.Add("@SectionId", SectionId);
conn.Execute(query, parameters);
}
}
public StudentUserModel GetByAcademicId(string AcademicId)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"SELECT * FROM Users where AcademicId = @AcademicId;";
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@AcademicId", AcademicId);
return(conn.QuerySingle <StudentUserModel>(query, parameters));
}
}
public List <int> GetAllBySection(int SectionId)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"SELECT StudentId from SectionStudents where SectionId = @SectionId;";
var parameters = new DynamicParameters();
parameters.Add("@SectionId", SectionId);
return(conn.Query <int>(query, parameters).ToList());
}
}
public List <SectionTimeModel> GetByFaculty(int FacultyId)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"SELECT * FROM SectionTimes where SectionId in
(SELECT Id FROM Sections WHERE FacultyId = @FacultyId)";
var parameters = new DynamicParameters();
parameters.Add("@Facultyid", FacultyId);
return(conn.Query <SectionTimeModel>(query, parameters).ToList());
}
}
public override SectionTimeModel Update(SectionTimeModel model)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"UPDATE SectionTimes SET StartTimeId = @StartTimeId, EndTimeId = @EndTimeId,
WeekDayId = @WeekDayId, ClassType = @ClassType, RoomNo = @RoomNo WHERE Id = @Id";
conn.Execute(query, model);
}
return(model);
}
public ClassModel InsertQRCode(int ClassId, string QR)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"UPDATE Classes SET QRCode = @QR WHERE Id = @ClassId;";
var parameters = new DynamicParameters();
parameters.Add("@ClassId", ClassId);
parameters.Add("@QR", QR);
return(conn.ExecuteScalar <ClassModel>(query, parameters));
}
}
public ClassModel InsertQRDisplayEndTime(int ClassId, string EndTime)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"UPDATE Classes SET QRDisplayEndTime = @EndTime where Id = @ClassId;";
var parameters = new DynamicParameters();
parameters.Add("@ClassId", ClassId);
parameters.Add("@EndTime", EndTime);
return(conn.ExecuteScalar <ClassModel>(query, parameters));
}
}
public override ClassModel Update(ClassModel model)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"UPDATE Classes SET ClassDate = @ClassDate, StartTimeid = @StartTimeId, EndTimeId = @EndTimeId,
RoomNo = @RoomNo WHERE Id = @Id";
conn.Execute(query, model);
}
return(model);
}
public List <StudentUserModel> SearchByNameAndSectionId(string key, int SectionId)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"SELECT * FROM Users where (FirstName like ('%' || @key || '%') OR LastName like ('%' || @key || '%')) AND UserType = 1 AND id in
(select StudentId FROM SectionStudents where SectionId = @SectionId);";
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@key", key);
parameters.Add("@SectionId", SectionId);
return(conn.Query <StudentUserModel>(query, parameters).ToList());
}
}
public List <ClassModel> GetByFacultyId(int FacultyId)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"SELECT * from Classes where Id IN
(SELECT Id FROM Classes where SectionId IN
(SELECT Id FROM Sections WHERE FacultyId = @FacultyId));" ;
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@FacultyId", FacultyId);
return(conn.Query <ClassModel>(query, parameters).ToList());
}
}
public void UpdatePasswordByUser(int Id, string Password, string salt)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"UPDATE Users SET Password = @Password, Salt = @Salt WHERE Id = @Id";
var parameters = new DynamicParameters();
parameters.Add("@Id", Id);
parameters.Add("@Password", Password);
parameters.Add("@Salt", salt);
conn.Execute(query, parameters);
}
}
public override BaseUserModel Create(BaseUserModel model)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"INSERT INTO Users (AcademicId, FirstName, LastName, Email, Password, Salt, CreatedAt, UserType)
VALUES(@AcademicId, @FirstName, @LastName, @Email, @Password, @Salt, @CreatedAt, @UserType);
SELECT last_insert_rowid();";
var newId = conn.ExecuteScalar <int>(query, model);
model.Id = newId;
}
return(model);
}
public override SectionModel Create(SectionModel model)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"INSERT INTO Sections (SectionName, FacultyId, CreatedAt)
VALUES(@SectionName, @FacultyId, @CreatedAt);
SELECT last_insert_rowid();";
var newId = conn.ExecuteScalar <int>(query, model);
model.Id = newId;
}
return(model);
}
public void Create(int SectionId, int StudentId)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"INSERT INTO SectionStudents (SectionId, StudentId)
VALUES(@SectionID, @StudentID);
SELECT last_insert_rowid();";
var parameters = new DynamicParameters();
parameters.Add("@SectionID", SectionId);
parameters.Add("@StudentID", StudentId);
conn.ExecuteScalar <int>(query, parameters);
}
}
public List <ClassModel> GetByDateAndFacultyId(string date, int FacultyId)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
//Console.WriteLine("date in query: " + date);
var query = @"SELECT * from Classes where ClassDate = @date AND Id IN
(SELECT Id FROM Classes where SectionId IN
(SELECT Id FROM Sections WHERE FacultyId = @FacultyId));" ;
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@date", date);
parameters.Add("@FacultyId", FacultyId);
return(conn.Query <ClassModel>(query, parameters).ToList());
}
}
public override ClassModel Create(ClassModel model)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"INSERT INTO Classes (ClassDate, ClassType, StartTimeId, EndTimeId, RoomNo, QRCode, QRDisplayStartTime,
QRDisplayEndTime, SectionId, CreatedAt) VALUES(@ClassDate, @ClassType, @StartTimeId, @EndTimeId, @RoomNo,
@QRCode, @QRDisplayStartTime, @QRDisplayEndTime, @SectionId, @CreatedAt);
SELECT last_insert_rowid();";
var newId = conn.ExecuteScalar <int>(query, model);
model.Id = newId;
}
return(model);
}
public override SectionTimeModel Create(SectionTimeModel model)
{
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"INSERT INTO SectionTimes (StartTimeId, EndTimeId, WeekDayId, ClassType, RoomNo,
SectionId, CreatedAt) VALUES(@StartTimeId, @EndTimeId, @WeekDayId, @ClassType,
@RoomNo, @SectionId, @CreatedAt);
SELECT last_insert_rowid();";
var newId = conn.ExecuteScalar <int>(query, model);
model.Id = newId;
}
return(model);
}
public FacultyUserModel GetByEmail(string email)
{
FacultyUserModel faculty = null;
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"SELECT * FROM Users WHERE Email = @Email;";
var parameters = new DynamicParameters();
parameters.Add("@Email", email);
try
{
faculty = conn.QuerySingle <FacultyUserModel>(query, parameters);
}
catch (Exception ex)
{
MessageBox.Show("Incorrect email or password. Please try again");
}
return(faculty);
}
}
//public override List<BaseUserModel> Get()
//{
// using (IDbConnection conn = SQLiteDBConnection.Get())
// {
// return conn.Query<dynamic, FacultyUserModel, StudentUserModel, BaseUserModel>
// ($"SELECT * FROM {TableName}", (baseUser, faculty, student) =>
// {
// if (baseUser.UserType == UserTypes.Faculty)
// {
// return new FacultyUserModel();
// }
// else if (baseUser.UserType == UserTypes.Student)
// {
// return new StudentUserModel();
// }
// return baseUser;
// });
// }
//}
public FacultyUserModel ValidateLogin(string email, string password)
{
FacultyUserModel faculty = null;
using (IDbConnection conn = SQLiteDBConnection.Get())
{
var query = @"SELECT * FROM Users where Email = @email AND Password = @password;";
var parameters = new DynamicParameters();
parameters.Add("@email", email);
parameters.Add("@password", password);
try
{
faculty = conn.QuerySingle <FacultyUserModel>(query, parameters);
}
catch (Exception ex)
{
MessageBox.Show("Wrong Login info. Please try again");
}
}
return(faculty);
}