protected bool isDuplicate(DBSite site, ExamMarksEntity exam)
{
bool isDuplicateRecord = true;
string qry = "SELECT COUNT(*) records FROM tblExamMarks"
+ " WHERE examID = " + exam.ExamId
+ " AND SubjectID = " + exam.SubjectId
+ " AND studentID = " + exam.StudentMasterId
+ " AND ClassID = " + exam.classId
+ " AND SectionId = " + exam.SectionId
+ " AND UserID = " + exam.UserID
+ " AND FYear = " + exam.FYear;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
isDuplicateRecord = (util.CheckNullInt(dr["records"])) > 0;
}
return(isDuplicateRecord);
}
public List <ExamMarksEntity> GetExamMarks(DBSite site, int classId, int sectionId, int examId, string subjectIds, int studentId, int IsNew)
{
// if isNew=0 then get reocrds from tblStudentMaster
// else get the reocrds from tblExamMarks
List <ExamMarksEntity> marks = new List <ExamMarksEntity>();
ExamMarksEntity mark = null;
string qry = "";
ExamMarksEntity exam = new ExamMarksEntity();
//exam.StudentMasterId = stu
if (IsNew == 1)
{
qry = " SELECT "
+ " StudentMasterId "
+ " , StudentName "
+ " , MobileF "
+ " , FatherName "
+ " , 0 ExamMarksId "
+ " , 0 ExamId "
+ " , '' ExamName "
+ " , '' ExamCode "
+ ", '' MaxMarks "
+ " , 0 SubjectID"
+ " , '' SubjectName"
+ ", '' SubjectGroupType "
+ " , ClassID "
+ " , ClassName "
+ " , SectionID "
+ " , SectionName "
+ " , 1 IsPresent "
+ " , '0' MarksObtained "
+ " , st.SubUserId, st.UserId, st.FYear "
+ " FROM tblStudentMaster st "
+ " LEFT OUTER JOIN tblClassMaster c ON c.ClassMasterId = st.ClassID "
+ " LEFT OUTER JOIN tblSectionMaster s On s.SectionMasterID = st.SectionId ";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User, "st");
}
else
{
// update
qry = " SELECT "
+ " StudentMasterId "
+ " , StudentName "
+ " , FatherName "
+ " , MobileF "
+ " , ExamMarksId "
+ " , ExamID "
+ " , ExamName "
+ " , ExamCode "
+ ", MaxMarks "
+ " , m.ClassID "
+ " , ClassName "
+ " , m.SectionID "
+ " , SectionName "
+ " , SubjectID "
+ " , SubjectName "
+ " , SubjectGroupType "
+ " , IsNull(IsPresent, 1) IsPresent "
+ ", IsNull(MarksObtained, '0') MarksObtained "
+ " , m.SubUserId, m.UserId, m.FYear "
+ " FROM tblExamMarks m "
+ " LEFT OUTER JOIN tblStudentMaster st ON m.studentID = st.StudentMasterID "
+ " LEFT OUTER JOIN tblClassMaster c ON c.ClassMasterId = m.ClassID "
+ " LEFT OUTER JOIN tblSectionMaster sm ON sm.sectionMasterID = m.SectionID "
+ " LEFT OUTER JOIN tblExamMaster em ON em.ExamMasterID = m.ExamID "
+ " LEFT OUTER JOIN tblSubjectMaster sub ON sub.SubjectMasterID = m.SubjectId "
+ " LEFT OUTER JOIN tblSubjectGroupMaster sgm ON sgm.SubjectGroupMasterID = sub.SubjectGroupMasterID ";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User, "m");
}
if (IsNew == 0 && subjectIds.Length > 1) // update
{
qry += " AND m.subjectID IN (" + subjectIds + ")";
}
if (IsNew == 0 && examId > 0) //update
{
qry += " AND m.ExamID = " + examId;
}
if (classId > 0)
{
qry += " AND st.ClassID = " + classId;
}
if (sectionId > 0)
{
qry += " AND st.sectionID = " + sectionId;
}
if (studentId > 0)
{
qry += " AND st.studentMasterId = " + studentId;
}
if (IsNew == 1)
{
qry += " ORDER BY StudentName, ClassOrder, SectionOrder ";
}
else
{
if (studentId > 0)
{
qry += " ORDER BY ExamOrder, subjectOrder ";
}
else
{
qry += " ORDER BY StudentName, ClassOrder, SectionOrder, subjectOrder ";
}
}
int SerialCount = 0;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
mark = new ExamMarksEntity();
SerialCount += 1;
mark.SlNo = SerialCount;
mark.ExamMarksId = util.CheckNullInt(dr["ExamMarksId"]);
mark.StudentMasterId = util.CheckNullInt(dr["StudentMasterId"]);
mark.StudentName = util.CheckNull(dr["StudentName"]);
mark.FatherName = util.CheckNull(dr["FatherName"]);
mark.MobileNo = util.CheckNull(dr["MobileF"]);
mark.ExamId = util.CheckNullInt(dr["ExamId"]);
mark.ExamName = util.CheckNull(dr["ExamName"]);
mark.ExamCode = util.CheckNull(dr["ExamCode"]);
mark.MaxMarks = util.CheckNull(dr["MaxMarks"]);
mark.SubjectId = util.CheckNullInt(dr["SubjectID"]);
mark.SubjectName = util.CheckNull(dr["SubjectName"]);
mark.SubjectGroupType = util.CheckNull(dr["SubjectGroupType"]);
mark.classId = util.CheckNullInt(dr["ClassID"]);
mark.ClassName = util.CheckNull(dr["ClassName"]);
mark.SectionId = util.CheckNullInt(dr["SectionID"]);
mark.SectionName = util.CheckNull(dr["SectionName"]);
mark.MarksObtained = util.CheckNull(dr["MarksObtained"]);
mark.IsPresent = util.CheckNullInt(dr["IsPresent"]);
mark.SubUserID = util.CheckNullInt(dr["SubUserId"]);
mark.UserID = util.CheckNullInt(dr["userID"]);
mark.FYear = util.CheckNullInt(dr["FYear"]);
marks.Add(mark);
}
return(marks);
}
public List <SMSExamMarksEntity> GetSMSExamMarks(DBSite site, int classId, int sectionId, int examId, string subjectIds, int IsNew)
{
List <SMSExamMarksEntity> SMSMarksList = new List <SMSExamMarksEntity>();
SMSExamMarksEntity smsMarks = null;
List <ExamMarksEntity> marks_list = GetExamMarks(site, classId, sectionId, examId, subjectIds, -1, IsNew);
List <ExamMarksEntity> distinct_studentList = new List <ExamMarksEntity>();
int studentId = -1;
ExamMarksEntity marks2 = null;
foreach (ExamMarksEntity marks in marks_list)
{
marks2 = new ExamMarksEntity();
if (marks.StudentMasterId != studentId)
{
marks2.StudentMasterId = marks.StudentMasterId;
marks2.StudentName = marks.StudentName;
marks2.FatherName = marks.FatherName;
marks2.MobileNo = marks.MobileNo;
marks2.ClassName = marks.ClassName;
marks2.SectionName = marks.SectionName;
marks2.ExamName = marks.ExamName;
distinct_studentList.Add(marks2);
}
studentId = marks.StudentMasterId;
}
string smsStr = "";
foreach (ExamMarksEntity marks in distinct_studentList)
{
smsMarks = new SMSExamMarksEntity();
smsMarks.StudentMasterId = marks.StudentMasterId;
smsMarks.StudentName = marks.StudentName;
smsMarks.FatherName = marks.FatherName;
smsMarks.MobileNo = marks.MobileNo;
smsMarks.ClassName = marks.ClassName;
smsMarks.SectionName = marks.SectionName;
smsMarks.ExamName = marks.ExamName;
smsStr = "";
foreach (ExamMarksEntity marks3 in marks_list)
{
if (marks3.StudentMasterId == marks.StudentMasterId)
{
smsStr += ", " + marks3.SubjectName + " " + marks3.MarksObtained + "/" + marks3.MaxMarks;
}
}
if (smsStr.Length > 2)
{
smsStr = smsStr.Substring(2);
}
smsMarks.SMS = smsStr;
SMSMarksList.Add(smsMarks);
}
return(SMSMarksList);
}