public bool Delete(Guid feeTypeId, string modifiedBy, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"UPDATE FeeType SET lastmodifiedby = @modifiedBy , isdeleted = now() , islive = null WHERE feeTypeId = @feeTypeId AND IsDeleted IS NULL
";
var id = connection.Query <int>(update, new
{
feeTypeId = feeTypeId,
modifiedBy = modifiedBy
}).FirstOrDefault();
if (id > 0)
{
return(true);
}
return(false);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public bool Delete(Guid levelId, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @" UPDATE Level SET lastmodifiedby = @modifiedby , isdeleted = now() , islive = null WHERE LevelId = @levelId
AND LevelID Not IN (Select LevelID FROM Class WHERE LevelID = @levelId AND IsDeleted IS NULL);
";
var id = connection.Query <int>(update, new
{
levelId = levelId,
modifiedby = modifiedby
}).FirstOrDefault();
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public List <TestMarkDto> GetListTestMarksByTestId(Guid testID, 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 tes.TestId = @TestID
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
{
TestID = testID
}
).AsList();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public bool Save(ClassTeacher objClass, string modifiedBy, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"UPDATE ClassTeacher SET TeacherID = @TeacherID ,
LastModifiedBy = @modifiedBy
WHERE ClassID = @ClassID
AND TermID = @TermID
AND IsDeleted Is NULL
";
var id = connection.Execute(update,
new
{
ClassID = objClass.ClassID,
TermID = objClass.TermID,
TeacherID = objClass.TeacherID,
modifiedBy = modifiedBy,
});
if (id <= 0)
{
var insert = @"
INSERT INTO ClassTeacher
(
ClassID ,
TermID ,
TeacherID ,
LastModifiedBy
)
VALUES
(
@ClassID,
@TermID ,
@TeacherID ,
@modifiedBy
)
";
var idI = connection.Execute(insert,
new
{
ClassID = objClass.ClassID,
TermID = objClass.TermID,
TeacherID = objClass.TeacherID,
modifiedBy = modifiedBy
});
}
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public List <ExamDto> GetListByYearAndStudentID(Guid studentID, int year, 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 ,
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 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 tm.Year = @year
AND stu.StudentID = @studentID
AND stu.IsDeleted IS NULL
AND cl.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,
year = year
}
).AsList();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public int BulkInsert(List <StudentTermRegister> studentTermRegisters, string modifiedBy, ref bool dbError)
{
int savedRows = 0;
try
{
using (var connection = GetConnection())
{
foreach (var studentTermRegister in studentTermRegisters)
{
var ids = Guid.NewGuid();
var sql = @"
INSERT INTO StudentTermRegister
(StudentTermRegisterID
,StudentID
,ClassID
,TermID
,IsPhoneSent
,IsEmailSent
,IsAllowedSent , LastModifiedBy)
VALUES
(
@id
,@StudentID
,@ClassID
,@TermID
,@IsPhoneSent
,@IsEmailSent
,@IsAllowedSent ,@modifiedBy
)
" ;
var id = connection.Execute(sql, new
{
id = Guid.NewGuid(),
StudentID = studentTermRegister.StudentID,
ClassID = studentTermRegister.ClassID,
IsAllowedSent = studentTermRegister.IsAllowedSent,
IsEmailSent = studentTermRegister.IsEmailSent,
IsPhoneSent = studentTermRegister.IsPhoneSent,
TermID = studentTermRegister.TermID,
modifiedBy = modifiedBy
});
if (id >= 0)
{
savedRows++;
}
}
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(savedRows);
}
public List <TestDto> GetListTestBySubjectIdAndYear(Guid subjectID, int year, ref bool dbError)
{
try
{
var sqlTestMark = @"SELECT
te.TestID ,
te.TeacherClassSubjectID ,
te.TestTitle ,
te.OutOf ,
te.TestDateCreated ,
(
SELECT Avg(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL
) As Average ,
(
SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND TestMark.Percentage > 49 AND ISDELETED IS NULL
) As NumberPassed ,
(
SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND TestMark.Percentage < 50 AND ISDELETED IS NULL
) As NumberFailed ,
(
SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL
) As TotalWritten
FROM
Test te
Where te.TeacherClassSubjectID
IN
(
SELECT TeacherClassSubject.TeacherClassSubjectID FROM Term
INNER JOIN TeacherClassSubject on TeacherClassSubject.TermID = Term.TermID
Where Year = @year
And SubjectID = @sid
AND TeacherClassSubject.ISDELETED IS NULL
AND Term.ISDELETED IS NULL
) AND te.ISDELETED IS NULL ";
using (var connection = GetConnection())
{
var list = connection.Query <TestDto>(sqlTestMark,
new
{
// te.* , tcs.* , cla.* , sub.* , tea.*
sid = subjectID,
year = year
}
).AsList();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public TeacherClassSubjectFile Insert(TeacherClassSubjectFile teacherClassSubjectFile, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
teacherClassSubjectFile.TeacherClassSubjectFileId = Guid.NewGuid();
var insert = @"
INSERT INTO TeacherClassSubjectFile
(TeacherClassSubjectFileId
,TeacherClassSubjectID
,TeacherClassSubjectFileTypeID
,Title
,Description
,FileSizeInBytes
,Filename
,FullUrl
,LastModifiedBy
)
VALUES
(@TeacherClassSubjectFileId ,
@TeacherClassSubjectID ,
@TeacherClassSubjectFileTypeID ,
@Title ,
@Description ,
@FileSizeInBytes ,
@Filename ,
@FullUrl ,
@modifiedby
)
";
var id = connection.Execute(insert,
new
{
TeacherClassSubjectFileId = teacherClassSubjectFile.TeacherClassSubjectFileId,
TeacherClassSubjectID = teacherClassSubjectFile.TeacherClassSubjectId,
TeacherClassSubjectFileTypeID = teacherClassSubjectFile.TeacherClassSubjectFileTypeId,
Title = teacherClassSubjectFile.Title,
Description = teacherClassSubjectFile.Description,
FileSizeInBytes = teacherClassSubjectFile.FileSizeInBytes,
Filename = teacherClassSubjectFile.Filename,
FullUrl = teacherClassSubjectFile.FullUrl,
modifiedby = modifiedby
});
return(teacherClassSubjectFile);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(null);
}
}
public Grade Save(Grade grade, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
if (grade.GradeID == null || Guid.Empty == grade.GradeID)
{
grade.GradeID = Guid.NewGuid();
var update = @"
INSERT INTO Grade
(GradeID
,SchoolID
,Description , lastmodifiedby)
VALUES
(
@id
,@SchoolID
,@Description , @modifiedby)
";
var id = connection.Execute(update,
new
{
id = Guid.NewGuid(),
SchoolID = grade.SchoolID,
Description = grade.Description,
modifiedby = modifiedby
});
}
else
{
var update = @"
UPDATE Grade
SET Description = @description , lastmodifiedby = @modifiedby
WHERE GradeID = @GradeID AND IsDeleted IS NULL
";
var id = connection.Execute(update,
new
{
description = grade.Description,
GradeID = grade.GradeID,
modifiedby = modifiedby
});
}
return(grade);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(null);
}
}
public Test Save(Test test, string modifiedby, ref bool dbError)
{
try
{
if (test.TestID == null || test.TestID == Guid.Empty)
{
var sql = @"
INSERT INTO Test
(TestID
,TeacherClassSubjectID
,TestTitle
,OutOf
,TestDateCreated , Lastmodifiedby )
VALUES
(@id
,@TeacherClassSubjectID
,@TestTitle
,@OutOf
,@dateCreated ,@modifiedby )";
var id = GetConnection().Execute(sql, new
{
id = Guid.NewGuid(),
TeacherClassSubjectID = test.TeacherClassSubjectID,
TestTitle = test.TestTitle,
OutOf = test.OutOf,
dateCreated = test.TestDateCreated,
modifiedby = modifiedby
});
}
else
{
var sql = @"UPDATE Test
SET
TestTitle = @TestTitle
,OutOf = @OutOf ,
LastModifiedBy = @modifiedby
WHERE TestID = @TestID ";
var id = GetConnection().Execute(sql, new
{
TestTitle = test.TestTitle,
OutOf = test.OutOf,
TestID = test.TestID,
modifiedby = modifiedby
});
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(null);
}
return(test);
}
public PagedList <TeacherDto> GetPagedSearch(Guid schoolID, int PageSize, int PageNo, string searchValue, ref bool dbError)
{
try
{
var columnName = "TeacherFullname";
if (PageNo <= 0)
{
PageNo = 1;
}
var results = new PagedList <TeacherDto>();
var sql = string.Format(@"SELECT *
FROM Teacher
WHERE SchoolID = @schoolID
AND ISDELETED IS NULL
AND {0} LIKE @searchValue
ORDER BY {1}
LIMIT @PageSize OFFSET @Offset ;
SELECT Count(*)
FROM Teacher
WHERE SchoolID = @schoolID
AND ISDELETED IS NULL
AND {2} LIKE @searchValue
" , columnName, columnName, columnName);
using (var connection = GetConnection())
{
using (var multi = connection.QueryMultiple(sql
,
new
{
schoolID = schoolID,
searchValue = "%" + searchValue + "%",
PageSize = PageSize,
PageNo = PageNo,
OffSet = (PageNo - 1) * PageSize
}))
{
results.PagedData = multi.Read <TeacherDto>().ToList();
results.TotalCount = multi.Read <int>().FirstOrDefault();
results.Page = PageNo;
results.Size = PageSize;
return(results);
}
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public StudentTermRegister Insert(StudentTermRegister studentTermRegister, string modifiedBy, ref bool dbError)
{
try
{
var sql = @"
INSERT INTO StudentTermRegister
(StudentTermRegisterID
,StudentID
,ClassID
,TermID
,IsPhoneSent
,IsEmailSent
,IsAllowedSent
, LastModifiedBy
)
VALUES
(
@id
,@StudentID
,@ClassID
,@TermID
,@IsPhoneSent
,@IsEmailSent
,@IsAllowedSent
,@modifiedBy
)
" ;
studentTermRegister.StudentTermRegisterID = Guid.NewGuid();
var id = GetConnection().Execute(sql, new
{
id = studentTermRegister.StudentTermRegisterID,
StudentID = studentTermRegister.StudentID,
ClassID = studentTermRegister.ClassID,
IsAllowedSent = studentTermRegister.IsAllowedSent,
IsEmailSent = studentTermRegister.IsEmailSent,
IsPhoneSent = studentTermRegister.IsPhoneSent,
TermID = studentTermRegister.TermID,
modifiedBy = modifiedBy
});
if (id <= 0)
{
return(null);
}
return(studentTermRegister);
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(null);
}
}
public bool Save(StudentTermReview studentTermReview, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"
INSERT INTO StudentTermReview
(StudentTermReviewID
,studentTermRegisterID
,TeacherID
,IsReviewGood
,Body
,CreatedDate
,Star5 , lastmodifiedby
)
VALUES
(
@id ,
@studentTermRegisterID ,
@TeacherID ,
@IsReviewGood ,
@Body ,
@dateToday,
@Star5 ,@modifiedby
)
";
var id = connection.Execute(update,
new
{
id = Guid.NewGuid(),
StudentTermRegisterID = studentTermReview.StudentTermRegisterID,
TeacherID = studentTermReview.TeacherID,
IsReviewGood = studentTermReview.IsReviewGood,
Body = studentTermReview.Body,
dateToday = DateTime.Today,
Star5 = studentTermReview.Star5,
modifiedby = modifiedby
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public Term Insert(Term term, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
term.TermID = Guid.NewGuid();
var update = @"
INSERT INTO Term
(
TermID
,Year
,TermNumber
,SchoolID
,StartDate
,EndDate , LAstModifiedBy
)
VALUES
(
@id ,
@Year ,
@TermNumber ,
@SchoolID ,
@FromDate ,
@ToDate ,@modifiedby
)
";
var id = connection.Execute(update,
new
{
id = term.TermID,
Year = term.Year,
TermNumber = term.TermNumber,
FromDate = term.StartDate,
ToDate = term.EndDate,
SchoolID = term.SchoolID,
modifiedby = modifiedby
});
return(term);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(null);
}
}
public List <Student> GetIsExistListStudentInRegNumberOrNationalID(List <string> ids, Guid schoolID, bool isRegNumber, ref bool dbError)
{
try
{
if (ids == null)
{
return(null);
}
string[] idsString = ids.ToArray();
string sql = @"SELECT st.*
FROM Student st
INNER JOIN School sc on sc.SchoolID = st.SchoolID
Where st.SchoolID = @schoolID
And st.IDnational IN @idsString
AND st.ISDELETED IS NULL
AND sc.ISDELETED IS NULL";
if (isRegNumber)
{
sql = @"SELECT st.*
FROM Student st
INNER JOIN School sc on sc.SchoolID = st.SchoolID
Where st.SchoolID = @schoolID
AND st.ISDELETED IS NULL
AND sc.ISDELETED IS NULL
And st.RegNumber IN @idsString";
}
using (var connection = GetConnection())
{
var list = connection.Query <Student>(sql,
new
{
idsString = idsString,
schoolID = schoolID
}
).AsList();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public List <TestDto> GetListDtoTestByTeacherClassSubjectId(Guid TeacherClassSubjectID, ref bool dbError)
{
try
{
var sqlTestMark = @"SELECT
te.TestID ,
te.TeacherClassSubjectID ,
te.TestTitle ,
te.OutOf ,
te.TestDateCreated ,
(
SELECT Avg(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL
) As Average ,
(
SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL AND TestMark.Percentage > 49
) As NumberPassed ,
(
SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL AND TestMark.Percentage < 50
) As NumberFailed ,
(
SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL
) As TotalWritten
FROM
Test te
Where te.teacherClassSubjectID = @tcsID AND te.ISDELETED IS NULL";
using (var connection = GetConnection())
{
var list = connection.Query <TestDto>(sqlTestMark,
new
{
// te.* , tcs.* , cla.* , sub.* , tea.*
tcsID = TeacherClassSubjectID
}
).AsList();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public bool Save(AbsentFromSchool absent, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @"
INSERT INTO AbsentFromSchool
(AbsentFromSchoolID ,
StudentTermRegisterID
,DayAbsent
,Reason ,
DateCreated , LastModifiedBy
)
VALUES
( @AbsentFromSchoolID ,
@StudentTermRegisterID ,
@DayAbsent
,@Reason
,@DateCreated , @modifiedby
)
";
var id = connection.Execute(update,
new
{
AbsentFromSchoolID = Guid.NewGuid(),
StudentTermRegisterID = absent.StudentTermRegisterID,
DayAbsent = absent.DayAbsent,
Reason = absent.Reason,
DateCreated = DateTime.Now,
modifiedby = modifiedby
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public Log Save(Log log, ref bool dbError)
{
try
{
var id = Guid.NewGuid();
var sql = @"
INSERT INTO Log
(LogID
,TeacherID
,EntityType
,ActionType
,ActionDetails
,ActionDate
)
VALUES
(@id
,@TeacherID
,@EntityType
,@ActionType
,@ActionDetails
,@ActionDate
);
Select @id
";
var ids = GetConnection().Query <Guid>(sql, new
{
id = id,
TeacherID = log.TeacherID,
EntityType = log.EntityType,
ActionType = log.ActionType,
ActionDetails = log.ActionDetails,
ActionDate = log.ActionDate
}).FirstOrDefault();
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(null);
}
return(log);
}
public List <Teacher> GetListTeacherBySchoolId(Guid schoolId, ref bool dbError)
{
try
{
var sql = @"SELECT tec.*
FROM Teacher tec
inner join School sch on sch.SchoolID = tec.SchoolID
where tec.SchoolID = @schoolId AND tec.ISDELETED IS NULL AND sch.ISDELETED IS NULL ";
var parameters = new DynamicParameters();
parameters.Add("@schoolID", schoolId);
return(GetList <Teacher>(sql, parameters));
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public static Log SaveActivity(Log log, ref bool dbError)
{
try
{
var id = Guid.NewGuid();
var sql = @"
INSERT INTO Log
(LogID
,TeacherID
,EntityType
,ActionType
,ActionDetails
,ActionDate
)
VALUES
(@id
,@TeacherID
,@EntityType
,@ActionType
,@ActionDetails
,@ActionDate
);
";
var ids = GetStaticConnection().Execute(sql, new
{
id = id,
TeacherID = log.TeacherID,
EntityType = log.EntityType,
ActionType = log.ActionType,
ActionDetails = log.ActionDetails,
ActionDate = log.ActionDate.AddMinutes(2)
});
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(null);
}
return(log);
}
public int CountTeachersBySchoolId(Guid schoolId, ref bool dbError)
{
try
{
var sql = @"SELECT Count(*)
FROM Teacher tec
inner join School sch on sch.SchoolID = tec.SchoolID
where tec.SchoolID = @schoolId AND sch.ISDELETED IS NULL AND tec.ISDELETED IS NULL ";
using (var connection = GetConnection())
{
return(connection.ExecuteScalar <int>(sql, new { schoolID = schoolId }));
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(0);
}
public bool DeleteByID(Guid studentTermRegisterId, string modifiedBy, ref bool dbError)
{
try
{
// remove exam , test based on studentTermRegisterId
var sql = "UPDATE StudentTermRegister SET isdeleted = now() , islive = null , LASTMODIFIEDBY = @modifiedBy Where StudentTermRegisterID = @StudentTermRegisterID ";
var id = GetConnection().Execute(sql, new
{
StudentTermRegisterID = studentTermRegisterId,
modifiedBy = modifiedBy
});
return(true);
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public List <Subject> GetUniqueSubjectsTestsWrittenByStudent(Guid studentId, ref bool dbError)
{
try
{
var sqlTestMark = @"SELECT *
FROM Subject Where SubjectID IN(
SELECT SubjectId FROM testmark
INNER JOIN test on testmark.testid = test.testid
INNER JOIN teacherclasssubject on teacherclasssubject.teacherclasssubjectId = test.teacherclasssubjectid
INNER JOIN StudentTermRegister on StudentTermRegister.StudentTermRegisterID = testmark.StudentTermRegisterID
WHERE
StudentTermRegister.StudentId = @studentId
AND test.IsDeleted IS NULL
AND testmark.IsDeleted IS NULL
AND Subject.IsDeleted IS NULL
AND teacherclasssubject.IsDeleted IS NULL
GROUP BY SubjectId
) ";
using (var connection = GetConnection())
{
var list = connection.Query <Subject>(sqlTestMark
,
new
{
studentId = studentId
}
).AsList();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public Teacher GetTeacherByEmail(string email, ref bool dbError)
{
try
{
var sql = @"SELECT tec.*
FROM Teacher tec
where tec.TeacherEmail = @email AND tec.ISDELETED IS NULL ";
using (var connection = GetConnection())
{
var record = connection.Query <Teacher>(sql,
new { email = email }
).FirstOrDefault();
return(record);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public Term GetByID(Guid termID, ref bool dbFlag)
{
var sql = @"SELECT *
FROM Term
WHERE TermID = @termID AND ISDELETED IS NULL";
using (var connection = GetConnection())
{
try
{
var list = connection.Query <Term>(sql
, new { termID = termID }
).FirstOrDefault();
return(list);
}
catch (Exception er)
{
dbFlag = true;
DbLog.Error(er);
return(null);
}
}
}
public Student GetStudentById(Guid studentID, ref bool dbError)
{
try
{
var sql = @"SELECT st.*
FROM Student st
INNER JOIN School sc on sc.SchoolID = st.SchoolID
AND st.StudentID = @studentID
AND st.ISDELETED IS NULL
AND sc.ISDELETED IS NULL ";
using (var connection = GetConnection())
{
var list = connection.Query <Student>(sql, new { studentID = studentID }).FirstOrDefault();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public bool Delete(Guid gradeID, string modifiedBy, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @" UPDATE Grade SET lastmodifiedby = @modifiedBy , isdeleted = now() , islive = null WHERE GradeID = @GradeID AND ISDELETED IS NULL
AND GRADEID NOT IN (SELECT GRADEID FROM GRADEMARK WHERE GRADEID = @GradeID AND ISDELETED IS NULL ) ";
var id = connection.Query <int>(update, new
{
GradeID = gradeID,
modifiedBy = modifiedBy
}).FirstOrDefault();
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public Test GetTestByTestId(Guid testID, ref bool dbError)
{
try
{
var sqlTestMark = @"SELECT te.*
FROM Test te
INNER JOIN TeacherClassSubject tcs on te.TeacherClassSubjectID = tcs.TeacherClassSubjectID
inner join Class cla on cla.ClassID = tcs.ClassID
inner join Subject sub on sub.SubjectId = tcs.SubjectId
inner join Teacher tea on tea.TeacherID = tcs.TeacherID
WHERE te.TestID = @testID
AND te.ISDELETED IS NULL
AND tcs.ISDELETED IS NULL
AND cla.ISDELETED IS NULL
AND sub.ISDELETED IS NULL
AND tea.ISDELETED IS NULL ";
using (var connection = GetConnection())
{
var list = connection.Query <Test>(sqlTestMark
,
new
{
TestID = testID
}
).FirstOrDefault();
return(list);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
public bool Delete(Guid teacherId, string modifiedby, ref bool dbError)
{
try
{
using (var connection = GetConnection())
{
var update = @" UPDATE Teacher SET isdeleted = now() , islive = null , LAstModifiedby = @modifiedby WHERE TeacherId = @teacherId
AND TeacherID NOT IN ( SELECT TeacherID FROM TeacherClassSubject Where TeacherID = @teacherId AND ISDELETED IS NULL )";
var id = connection.Execute(update, new
{
teacherId = teacherId,
modifiedby = modifiedby
});
return(true);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
return(false);
}
}
public Teacher GetTeacherById(Guid teacherID, ref bool dbError)
{
try
{
var sql = @"SELECT tec.*
FROM Teacher tec
inner join School sch on sch.SchoolID = tec.SchoolID
where tec.TeacherId = @teacherID AND tec.IsDELETED IS NULL AND sch.ISDELETED IS NULL";
using (var connection = GetConnection())
{
var record = connection.Query <Teacher>(sql,
new { teacherID = teacherID }
).FirstOrDefault();
return(record);
}
}
catch (Exception er)
{
dbError = true;
DbLog.Error(er);
}
return(null);
}
