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); } }