public List <TestMarkDto> GetListTestMarksByStudentID(Guid StudentID, ref bool dbError)
{
try
{
var sqlTestMark = @"SELECT enrol.StudentID,
ex.TestID,
ex.studentTermRegisterID,
stu.SchoolID,
tm.TermID ,
tm.Year ,
tm.TermNumber ,
sub.SubjectCode,
sub.SubjectName,
stu.RegNumber,
CONCAT(stu.StudentName,' ',stu.StudentSurname) As FullName,
ex.Mark,
ex.Percentage As MarkPercentage ,
tes.OutOf ,
tes.TestTitle ,
tes.TestDateCreated
FROM TestMark ex
INNER JOIN Test tes on tes.TestID = ex.TestID
INNER JOIN TeacherClassSubject tcs on tcs.TeacherClassSubjectID = tes.TeacherClassSubjectID
INNER JOIN Term tm on tcs.TermID = tm.TermID
INNER JOIN Class cl on tcs.ClassID = cl.ClassID
INNER JOIN Subject sub on sub.SubjectID = tcs.SubjectID
INNER JOIN StudentTermRegister enrol on enrol.studentTermRegisterID = ex.studentTermRegisterID
INNER JOIN Student stu on stu.StudentID = enrol.StudentID
Where stu.StudentID = @StudentID
AND ex.ISDELETED IS NULL
AND tes.ISDELETED IS NULL
AND tcs.ISDELETED IS NULL
AND tm.ISDELETED IS NULL
AND cl.ISDELETED IS NULL
AND sub.ISDELETED IS NULL
AND enrol.ISDELETED IS NULL
AND stu.ISDELETED IS NULL
";
using (var connection = GetConnection())
{
var list = connection.Query <TestMarkDto>(sqlTestMark
,
new
{
StudentID = StudentID
}
).AsList();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public Term GetByTermNumberAndTermYearAndSchoolID(Guid schoolId, int termNumber, int termYear, ref bool dbFlag)
{
var sql = @"SELECT *
FROM Term
WHERE Year = @termYear
AND TermNumber = @termNumber
AND SchoolID = @schoolId AND ISDELETED IS NULL";
using (var connection = GetConnection())
{
try
{
var list = connection.Query <Term>(sql
, new { termYear = termYear, termNumber = termNumber, schoolId = schoolId }
).FirstOrDefault();
return(list);
}
catch (Exception er)
{
dbFlag = true;
DbLog.Error(er);
return(null);
}
}
}
public bool Delete(Guid termID, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"
update Term set isdeleted = now() , islive = null , lastmodifiedby =@modifiedby Where TermID = @termID
AND TermID Not IN ( Select TermID FROM Setting where TermID = @termID AND IsDeleted IS NULL)
AND TermID Not IN ( Select TermID FROM TeacherClassSubject where TermID = @termID AND IsDeleted IS NULL)
";
var id = connection.Execute(update,
new
{
termID = termID,
modifiedby = modifiedby
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool UpdateOneTimePin(Guid teacherID, string oneTimePin, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var newHasOnetimePin = HashPassword(oneTimePin);
var date = DateTime.Today;
var update = @"
UPDATE Auth
SET LastLogin = @date ,
OneTimePin = @oneTimePin
WHERE TeacherID = @teacherID
AND IsDeleted IS NULL
";
var id = connection.Execute(update,
new
{
teacherID = teacherID,
oneTimePin = newHasOnetimePin,
date = DateTime.Now
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public Auth GetForgotPasswordCode(Guid teacherId, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"Select * FROM Auth WHERE TeacherID = @teacherID
AND IsDeleted IS NULL
";
var id = connection.Query <Auth>(update,
new
{
teacherID = teacherId
}).FirstOrDefault();
return(id);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public bool Delete(Guid subjectId, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"UPDATE Subject SET lastmodifiedby = @modifiedby , isdeleted = now() , islive = null WHERE SubjectId = @subjectId
AND SubjectID Not IN ( Select SubjectID FROM TeacherClassSubject where SubjectId = @subjectId AND IsDeleted IS NULL)";
var id = connection.Execute(update, new
{
SubjectID = subjectId,
modifiedby = modifiedby
});
if (id > 0)
{
return(true);
}
return(false);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool PasswordChange_Parent(string email, string newPassword, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var date = DateTime.Today;
newPassword = HashPassword(newPassword);
var update = @"
UPDATE Parent
SET Password = @newPassword
WHERE ParentEmail = @email
AND IsDeleted IS NULL
";
var id = connection.Execute(update,
new
{
email = email
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool Delete(Guid gradeMarkID, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"UPDATE GradeMark SET lastmodifiedby = @modifiedby , isdeleted = now() , islive = null where GradeMarkID = @GradeID ANd ISDELETED IS NULL ;
Select 1;
";
var id = connection.Query <int>(update, new
{
GradeID = gradeMarkID,
modifiedby = modifiedby
}).FirstOrDefault();
if (id > 0)
{
return(true);
}
return(false);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool Delete(Guid committeMemberID, string modifiedBy, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @" UPDATE CommitteMember SET isdeleted = now() , islive = null ,
LastModifiedBy = @modifiedBy WHERE CommitteMemberID = @committeMemberID
AND ISDELETED IS NULL;
";
var id = connection.Execute(update, new
{
committeMemberID = committeMemberID,
modifiedBy = modifiedBy
});
if (id > 0)
{
return(true);
}
return(false);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public Student GetStudentByRegNumberAndSchoolCode(string regNumber, string schoolCode, ref bool dbError)
{
try
{
var sql = @"SELECT st.*
FROM Student st
INNER JOIN School sc on sc.SchoolID = st.SchoolID
AND st.RegNumber = @regNumber
AND st.SchoolID IN (SELECT schoolID FROM School WHERE SchoolCode = @schoolCode )
AND st.ISDELETED IS NULL
AND sc.ISDELETED IS NULL";
using (var connection = GetConnection())
{
var list = connection.Query <Student>(sql,
new
{
RegNumber = regNumber,
schoolCode = schoolCode
}).FirstOrDefault();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public Student GetStudentByNationalID(string iDnational, Guid schoolID, ref bool dbError)
{
try
{
var sql = @"SELECT st.*
FROM Student st
INNER JOIN School sc on sc.SchoolID = st.SchoolID
AND st.IDnational = @studentID
AND st.SchoolID = @schoolID
AND st.ISDELETED IS NULL
AND sc.ISDELETED IS NULL";
using (var connection = GetConnection())
{
var list = connection.Query <Student>(sql
,
new { IDnational = iDnational,
schoolID = schoolID }
).FirstOrDefault();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public List <Student> GetListSearchStudentByRegNumberAndSchoolID(string regNumber, Guid schoolID, ref bool dbError)
{
try
{
var sql = @"SELECT st.*
FROM Student st
INNER JOIN School sc on sc.SchoolID = st.SchoolID
Where st.SchoolID = @schoolID
And st.RegNumber = @regNumber
AND st.ISDELETED IS NULL
AND sc.ISDELETED IS NULL";
using (var connection = GetConnection())
{
var list = connection.Query <Student>(sql,
new
{
schoolID = schoolID,
regNumber = regNumber
}
).AsList();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public List <Student> GetListOfStudentsLinkedToEmailAndSchoolID(string email, Guid schoolID, ref bool dbError)
{
try
{
var sql = @"SELECT st.*
FROM Student st
INNER JOIN School sc on sc.SchoolID = st.SchoolID
Where st.SchoolID = @schoolID
And st.Email = @email
AND st.ISDELETED IS NULL
AND sc.ISDELETED IS NULL";
using (var connection = GetConnection())
{
var list = connection.Query <Student>(sql,
new
{
schoolID = schoolID,
email = email
}
).AsList();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public bool Delete(Guid studentID, string modifiedby, ref bool dbError)
{
try
{
var sql = @" UPDATE Student SET lastmodifiedby = @modifiedby , isdeleted = now() , islive = null WHERE StudentID = @studentID AND ISDeleted IS NULL
AND StudentID NOT IN (Select StudentID FROM Enrollment WHERE IsDeleted IS NULL AND StudentID = @studentID )
";
var id = GetConnection().Execute(sql, new
{
StudentID = studentID,
modifiedby = modifiedby
});
if (id > 0)
{
return(true);
}
return(true);
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(false);
}
public bool Save(TeacherDepartment objClass, string modifiedBy, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"UPDATE TeacherDepartment SET TeacherID = @TeacherID ,
DepartmentId = @DepartmentId ,
LastModifiedBy = @modifiedBy
WHERE teacherDepartmentId = @teacherDepartmentId
AND IsDeleted Is NULL
";
var id = connection.Execute(update,
new
{
DepartmentId = objClass.DepartmentId,
TeacherID = objClass.TeacherId,
teacherDepartmentId = objClass.TeacherDepartmentId,
modifiedBy = modifiedBy,
});
if (id <= 0)
{
var insert = @"
INSERT INTO TeacherDepartment
(
DepartmentId ,
teacherDepartmentId ,
TeacherID ,
LastModifiedBy
)
VALUES
(
@DepartmentId,
@teacherDepartmentId ,
@TeacherID ,
@modifiedBy
)
";
var idI = connection.Execute(insert,
new
{
DepartmentId = objClass.DepartmentId,
TeacherID = objClass.TeacherId,
teacherDepartmentId = Guid.NewGuid(),
modifiedBy = modifiedBy,
});
}
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool Delete(Guid Id, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @" UPDATE TeacherClassSubject SET isdeleted = now() , islive = null , lastmodifiedby = @modifiedby WHERE TeacherClassSubjectID = @Id
AND ISDELETED IS NULL
AND TeacherClassSubjectID Not IN (Select TeacherClassSubjectID FROM EXAM WHERE TeacherClassSubjectID = @Id AND IsDeleted IS NULL)
AND TeacherClassSubjectID Not IN(Select TeacherClassSubjectID FROM TEST WHERE TeacherClassSubjectID = @Id AND IsDeleted IS NULL)";
var id = connection.Execute(update, new
{
Id = Id,
modifiedby = modifiedby
});
if (id > 0)
{
return(true);
}
return(false);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool Delete(Guid id, string modifiedBy, ref bool dbFlag)
{
try
{
using (var connection = GetConnection())
{
var update = @" UPDATE AbsentFromSchool SET isdeleted = now() , islive = null , LastModifiedBy = @modifiedBy WHERE AbsentFromSchoolID = @id
";
var count = connection.Execute(update,
new
{
id = id,
modifiedBy = modifiedBy
});
if (count >= 1)
{
return(true);
}
else
{
return(false);
}
}
}
catch (Exception er)
{
dbFlag = true;
DbLog.Error(er);
return(false);
}
}
public List <ExamDto> GetListByStudentID(Guid studentID, ref bool dbError)
{
try
{
var sqlTestMark = @"SELECT enrol.StudentID,
ex.TeacherClassSubjectID,
ex.StudentTermRegisterID,
stu.SchoolID,
tm.TermID ,
tm.Year ,
tm.TermNumber ,
sub.SubjectCode,
sub.SubjectName,
stu.RegNumber,
CONCAT(stu.StudentName,' ',stu.StudentSurname) As FullName,
ex.Mark,
ex.Grade,
ex.Comment,
stu.IsMale ,
cl.ClassName ,
lv.LevelName ,
tm.endDate AS EndOfTermDate
FROM Exam ex
INNER JOIN TeacherClassSubject tcs on tcs.TeacherClassSubjectID = ex.TeacherClassSubjectID
INNER JOIN Term tm on tcs.TermID = tm.TermID
INNER JOIN Class cl on tcs.ClassID = cl.ClassID
INNER JOIN Level lv on lv.LevelID = cl.LevelID
INNER JOIN Subject sub on sub.SubjectID = tcs.SubjectID
INNER JOIN StudentTermRegister enrol on enrol.StudentTermRegisterID = ex.StudentTermRegisterID
INNER JOIN Student stu on stu.StudentID = enrol.StudentID
Where stu.StudentID = @studentID
AND stu.IsDeleted IS NULL
AND cl.IsDeleted IS NULL
AND lv.IsDeleted IS NULL
AND tcs.IsDeleted IS NULL
AND ex.IsDeleted IS NULL
AND tm.IsDeleted IS NULL
AND sub.IsDeleted IS NULL
AND enrol.IsDeleted IS NULL
ORDER BY tm.Year DESC, tm.TermNumber ASC
" ;
using (var connection = GetConnection())
{
var list = connection.Query <ExamDto>(sqlTestMark,
new
{
studentID = studentID
}
).AsList();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public bool PasswordChange_Admin(string username, string newPassword, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var date = DateTime.Today;
newPassword = HashPassword(newPassword);
var update = @"
UPDATE Admin
SET Password = @newPassword
WHERE Username = @username
AND IsDeleted IS NULL
";
var id = connection.Execute(update,
new
{
username = username
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool Delete(Guid studentTermReviewId, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @" UPDATE StudentTermReview SET lastmodifiedby = @modifiedby , isdeleted = now() , islive = null WHERE studentTermReviewId = @studentTermReviewId
";
var id = connection.Execute(update, new
{
studentTermReviewId = studentTermReviewId,
modifiedby = modifiedby
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool ForgotPasswordCodeUpdate(Guid teacherID, string ForgotPassowrdCode, bool isExpired, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var date = DateTime.Today;
var update = @"
UPDATE Auth
SET ForgotPassowrdCode = @ForgotPassowrdCode ,
isExpired = 0 ,
CreatedDate = @date
WHERE TeacherID = @teacherID
AND IsDeleted IS NULL
";
var id = connection.Execute(update,
new
{
teacherID = teacherID,
ForgotPassowrdCode = ForgotPassowrdCode,
date = date
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public PagedList <LogDto> GetPagedTeacherLog(Guid teacherID, int PageSize, int PageNo, ref bool dbError)
{
try
{
if (PageNo <= 0)
{
PageNo = 1;
}
if (PageSize <= 0)
{
PageSize = 5;
}
var offset = (PageNo - 1) * PageSize;
if (offset < 0)
{
offset = 0;
}
var results = new PagedList <LogDto>();
var sql = @"SELECT te.TeacherUsername As Username ,
te.TeacherFullname As TeacherName ,
lg.EntityType ,
lg.ActionType ,
lg.ActionDetails ,
lg.ActionDate FROM Log lg
INNER JOIN Teacher te ON te.TeacherID = lg.TeacherID
WHERE te.TeacherID = @teacherID
ORDER BY lg.ActionDate
LIMIT @PageSize OFFSET @Offset ;
SELECT Count(*) FROM Log INNER JOIN Teacher ON Teacher.TeacherID = Log.TeacherID
WHERE Teacher.TeacherID = @teacherID ;
" ;
using (IDbConnection connection = GetConnection())
{
using (var multi = connection.QueryMultiple(sql
,
new
{
teacherID = teacherID,
PageSize = PageSize,
OffSet = (PageNo - 1) * PageSize
}))
{
results.PagedData = multi.Read <LogDto>().ToList();
results.TotalCount = multi.Read <int>().FirstOrDefault();
results.Page = PageNo;
results.Size = PageNo;
return(results);
}
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public bool UpdateLastLoginDateAndToken(Guid teacherID, string newToken, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var date = DateTime.Today;
var update = @"
UPDATE Auth
SET LastLogin = @date ,
WebToken = @newToken
WHERE TeacherID = @teacherID
AND IsDeleted IS NULL
";
var id = connection.Execute(update,
new
{
teacherID = teacherID,
newToken = newToken,
date = DateTime.Now
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool Delete(Guid classId, string modifiedBy, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @" UPDATE Class SET isdeleted = now() , islive = null , LastModifiedBy = @modifiedBy WHERE ClassID = @ClassID AND IsDeleted IS NULL ;
";
var id = connection.Query <int>(update, new
{
ClassID = classId,
modifiedBy = modifiedBy
}).FirstOrDefault();
if (id > 0)
{
return(true);
}
return(false);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public Auth GetAuthByForgotPassword(string forgotPassowrdCode, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"Select * FROM Auth WHERE ForgotPassowrdCode = @forgotPassowrdCode
AND IsDeleted IS NULL
";
var id = connection.Query <Auth>(update,
new
{
forgotPassowrdCode = forgotPassowrdCode
}).FirstOrDefault();
return(id);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public StudentTermRegister Update(StudentTermRegister studentTermRegister, string modifiedBy, ref bool dbError)
{
try
{
var sql = @"UPDATE StudentTermRegister
SET IsPhoneSent = @IsPhoneSent
,IsEmailSent = @IsEmailSent
,IsAllowedSent = @IsAllowedSent
,CanParentViewExamData = @CanParentViewExamData
,LastModifiedBy = @modifiedBy
WHERE StudentTermRegisterID = @StudentTermRegisterID AND IsDeleted IS NULL";
var id = GetConnection().Execute(sql, new
{
StudentTermRegisterID = studentTermRegister.StudentTermRegisterID,
IsAllowedSent = studentTermRegister.IsAllowedSent,
IsEmailSent = studentTermRegister.IsEmailSent,
IsPhoneSent = studentTermRegister.IsPhoneSent,
TermID = studentTermRegister.TermID,
CanParentViewExamData = studentTermRegister.CanParentViewExamData,
modifiedBy = modifiedBy
});
return(studentTermRegister);
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(null);
}
}
public Term Update(Term term, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"
UPDATE Term SET StartDate = @FromDate , EndDate = @ToDate , lastmodifiedby = @modifiedby WHERE TermID = @termID
";
var id = connection.Execute(update,
new
{
termID = term.TermID,
FromDate = term.StartDate,
ToDate = term.EndDate,
modifiedby = modifiedby
});
return(term);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(null);
}
}
public bool Delete(Guid teacherDepartmentId, string modifiedBy, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"
UPDATE TeacherDepartment SET isdeleted = now() , islive = null ,
LastModifiedBy = @modifiedBy
where
teacherDepartmentId = @teacherDepartmentId
";
var id = connection.Execute(update, new
{
teacherDepartmentId = teacherDepartmentId,
modifiedBy = modifiedBy
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool IsPhoneExist(string phone, ref bool dbError)
{
try
{
var sql = @"SELECT tec.*
FROM Teacher tec
where tec.TeacherPhone = @phone ";
using (var connection = GetConnection())
{
var record = connection.Query <Teacher>(sql,
new { phone = phone }
).FirstOrDefault();
if (record != null)
{
return(true);
}
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(false);
}
public bool Delete(Guid feeTermId, string modifiedBy, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @" UPDATE feeTerm set lastmodifiedby = @modifiedBy , isdeleted = now() , islive = null WHERE feeTermId = @feeTermId AND IsDeleted IS NULL ;
";
var id = connection.Query <int>(update, new
{
feeTermId = feeTermId,
modifiedBy = modifiedBy
}).FirstOrDefault();
if (id > 0)
{
return(true);
}
return(false);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}