public List <TermMasterEntity> GetTermList(DBSite site, int userId, string id = "")
{
List <TermMasterEntity> termList = new List <TermMasterEntity>();
string qry = "";
qry += "SELECT "
+ " TermId, TermName, TermStartDate, TermEndDate, UserID, FYear "
+ " FROM tblTerm tm "
+ " WHERE tm.UserId = " + Util_BLL.User.UserId
+ " ";
if (id != string.Empty)
{
qry += "AND TermId = " + id;
}
//qry += " ORDER BY TermStartDate ";
DataTable dt = site.ExecuteSelect(qry);
TermMasterEntity term;
foreach (DataRow dr in dt.Rows)
{
term = new TermMasterEntity();
term.TermMasterId = util.CheckNullInt(dr["TermId"]);
term.TermName = util.CheckNull(dr["TermName"]);
term.TermStartDate = util.CheckNullDate(dr["TermStartDate"]);
term.TermEndDate = util.CheckNullDate(dr["TermEndDate"]);
term.UserID = util.CheckNullInt(dr["UserID"]);
term.FYear = util.CheckNullInt(dr["FYear"]);
termList.Add(term);
}
return(termList);
}
public List <ExamMasterEntity> GetExamList(DBSite site, int userId, string id = "")
{
List <ExamMasterEntity> examList = new List <ExamMasterEntity>();
string qry = "";
qry += "SELECT "
+ " ExamMasterId, ExamDate, ExamName, ExamCode, MaxMarks, PassMarks "
+ ", TermId, ClassMasterID, SectionMasterID, ExamOrder, IsFormula, UserID, FYear "
+ " FROM tblExamMaster em "
+ " WHERE em.UserId = " + Util_BLL.User.UserId
+ " AND isFormula = 0 ";
if (id != string.Empty)
{
qry += "AND ExamMasterId = " + id;
}
qry += " ORDER BY ExamOrder ";
DataTable dt = site.ExecuteSelect(qry);
ExamMasterEntity exam;
foreach (DataRow dr in dt.Rows)
{
exam = new ExamMasterEntity();
exam.ExamMasterId = util.CheckNullInt(dr["ExamMasterId"]);
exam.ExamDate = util.CheckNullDate(dr["ExamDate"]);
exam.ExamName = util.CheckNull(dr["ExamName"]);
exam.ExamCode = util.CheckNull(dr["ExamCode"]);
exam.TermId = util.CheckNullInt(dr["TermId"]);
exam.ClassMasterID = util.CheckNullInt(dr["ClassMasterID"]);
exam.SectionMasterID = util.CheckNullInt(dr["SectionMasterID"]);
exam.MaxMarks = util.CheckNullInt(dr["MaxMarks"]);
exam.PassMarks = util.CheckNullInt(dr["PassMarks"]);
exam.ExamOrder = util.CheckNullInt(dr["ExamOrder"]);
exam.IsFormula = util.CheckNullInt(dr["IsFormula"]);
exam.UserID = util.CheckNullInt(dr["UserID"]);
exam.FYear = util.CheckNullInt(dr["FYear"]);
examList.Add(exam);
}
return(examList);
}
public List <ReportCardCBSEEntity> GetReportCardCBSE(int studentId)
{
DBSite site = new DBSite();
List <ReportCardCBSEEntity> ReportCardList = new List <ReportCardCBSEEntity>();
DataTable dt = site.ExecuteSelect(GetCBSEQry_OakWood(studentId
, "Scholastic"
, "Unit Test 1"
, "Unit Test 2"
, "Unit Test 1"
, "Unit Test 2"
, "SEA 1"
, "SEA 2"
, "Half Yearly"
, "Annual"
, "Term I"
, "Term II"
));
ReportCardCBSEEntity reportCard;
foreach (DataRow dr in dt.Rows)
{
reportCard = new ReportCardCBSEEntity();
reportCard.StudentMasterId = studentId;
reportCard.StudentName = util.CheckNull(dr["StudentName"]);
reportCard.DOB = util.CheckNullDate(dr["dob"]);
reportCard.Subject = util.CheckNull(dr["SubjectName"]);
reportCard.PerTest1 = util.CheckNull(dr["PerTest1"]);
reportCard.NoteBook1 = util.CheckNull(dr["NoteBook1"]);
reportCard.SEA1 = util.CheckNull(dr["SEA1"]);
reportCard.HalfYearly = util.CheckNull(dr["HalfYearly"]);
reportCard.ClassName = util.CheckNull(dr["ClassName"]);
reportCard.SectionName = util.CheckNull(dr["SectionName"]);
reportCard.MotherName = util.CheckNull(dr["MotherName"]);
reportCard.FatherName = util.CheckNull(dr["FatherName"]);
reportCard.PerTest2 = util.CheckNull(dr["PerTest2"]);
reportCard.NoteBook2 = util.CheckNull(dr["NoteBook2"]);
reportCard.SEA2 = util.CheckNull(dr["SEA2"]);
reportCard.Yearly = util.CheckNull(dr["Yearly"]);
ReportCardList.Add(reportCard);
}
return(ReportCardList);
}
public List <StudenEntity> GetStudentsByClass(DBSite site, string ids, string classIds = "")
{
List <StudenEntity> studentList = new List <StudenEntity>();
string qry = "";
qry += " SELECT ";
qry += " StudentMasterId, StudentName, AdmissionNo "
+ " , st.ClassId, ClassName "
+ " , sc.SectionMasterID, SectionName "
+ " , IDCardNo, FatherName, MotherName, MobileF, MobileM ";
qry += " , DOB, Email, st.UserID, st.FYear, st.CreatedAt ";
qry += " FROM tblStudentMaster as st "
+ " LEFT OUTER JOIN tblClassMaster c on c.classMasterID = st.classID and c.userid = " + Util_BLL.User.UserId
+ " LEFT OUTER JOIN tblSectionMaster sc on sc.SectionMasterID = st.SectionID and sc.userId = " + Util_BLL.User.UserId;
qry += " WHERE st.UserID = " + Util_BLL.User.UserId;
if (ids != "")
{
qry += " AND StudentMasterId IN (" + ids + ")";
}
if (classIds != "" && classIds != "ALL")
{
qry += " AND st.ClassId In (" + classIds + ")";
}
else if (ids == "" && classIds == "")
{
classIds = "-999";
qry += " AND st.ClassId In (" + classIds + ")";
}
else if (classIds == "ALL")
{
qry += " ";
}
qry += " order by classOrder, StudentName ";
DataTable dt = site.ExecuteSelect(qry);
StudenEntity st;
foreach (DataRow dr in dt.Rows)
{
st = new StudenEntity();
st.StudentMasterId = util.CheckNullInt(dr["StudentMasterId"]);
st.StudentName = util.CheckNull(dr["StudentName"]);
st.AdmNo = util.CheckNull(dr["AdmissionNo"]);
st.PresentClass = util.CheckNullInt(dr["ClassID"]);
st.PresentClassName = util.CheckNull(dr["ClassName"]);
st.SectionId = util.CheckNullInt(dr["SectionMasterID"]);
st.Section = util.CheckNull(dr["SectionName"]);
st.IdCardNo = util.CheckNull(dr["IdCardNo"]);
st.FatherName = util.CheckNull(dr["FatherName"]);
st.MotherName = util.CheckNull(dr["MotherName"]);
st.MobileNoF = util.CheckNull(dr["MobileF"]);
st.MobileNoM = util.CheckNull(dr["MobileM"]);
st.Email = util.CheckNull(dr["Email"]);
st.DOB = util.CheckNullDate(dr["DOB"]);
//DateTime date = Convert.ToDateTime(dr["ProductDate"]);
//pme.productDate = date;
st.UserID = util.CheckNullInt(dr["UserID"]);
st.FYear = util.CheckNullInt(dr["FYear"]);
studentList.Add(st);
}
return(studentList);
}
public List <AccountMasterEntity> GetMatchedRecords(DBSite site, string value_to_search)
{
List <AccountMasterEntity> selected_account_list = new List <AccountMasterEntity>();
string qry = "";
qry += "SELECT ";
qry += "AccountMasterId, AccountName, CreationDate, OpeningBalance,";
qry += "DRCR, Address, City, Phone, Mobile, Email,";
qry += " Remarks, acc.UserID, FYear ";
qry += ", acc.GroupId ";
qry += ", GroupName ";
qry += " FROM tblAccountMaster as acc ";
qry += " LEFT OUTER JOIN tblGroup as gp ON ";
qry += " acc.GroupId = gp.GroupId ";
//----- selection condition ----------
qry += " WHERE acc.UserID = " + Util_BLL.User.UserId + " AND FYear= " + Util_BLL.User.fYear + " AND "; //------ user from session -----
qry += " (( AccountName LIKE '%" + value_to_search + "%' ) OR";
if (IsDate(value_to_search))
{
qry += " ( CreationDate = '" + value_to_search + "' ) OR";
}
qry += " ( Address LIKE '%" + value_to_search + "%' ) OR";
qry += " ( City LIKE '%" + value_to_search + "%' ) OR";
if (util.IsNumber(value_to_search))
{
qry += " ( OpeningBalance = '" + value_to_search + "' ) OR";
qry += " ( Phone = '" + value_to_search + "' ) OR";
qry += " ( Mobile = '" + value_to_search + "' ) OR";
}
qry += " ( Email LIKE '%" + value_to_search + "%' ) OR";
qry += " ( GroupName LIKE '%" + value_to_search + "%') )";
DataTable dt = site.ExecuteSelect(qry);
AccountMasterEntity ac;
foreach (DataRow dr in dt.Rows)
{
ac = new AccountMasterEntity();
ac.AccountMasterId = util.CheckNullInt(dr["AccountMasterId"].ToString());
ac.AccountName = dr["AccountName"].ToString();
ac.CreationDate = util.CheckNullDate(dr["CreationDate"]);
ac.OpeningBalance = util.ToDecimal(util.CheckNullDecimal(dr["OpeningBalance"]));
ac.DRCR = util.CheckNullInt(dr["DRCR"]);
ac.DRCR_Name = (util.CheckNullInt(dr["DRCR"]) == 0) ? "Credit" : "Debit";
ac.Address = util.CheckNull(dr["Address"].ToString());
ac.City = util.CheckNull(dr["City"].ToString());
ac.Phone = util.CheckNull(dr["Phone"].ToString());
ac.Mobile = util.CheckNull(dr["Mobile"].ToString());
ac.Email = util.CheckNull(dr["Email"].ToString());
ac.Remarks = util.CheckNull(dr["Remarks"].ToString());
ac.GroupId = util.CheckNullInt(dr["GroupId"]);
ac.GroupName = util.CheckNull(dr["GroupName"]);
ac.UserID = util.CheckNullInt(dr["UserID"]);
ac.FYear = util.CheckNullInt(dr["FYear"]);
selected_account_list.Add(ac);
}
return(selected_account_list);
}
void SaveExam()
{
exmBll = new ExamMasterBll();
ExamMasterBll.ExamMasterEntity exam = new ExamMasterBll.ExamMasterEntity();
try
{
string message = "";
exam.ExamName = this.txtExamName.Text;
exam.ExamCode = this.txtExamCode.Text;
exam.ExamDate = util.CheckNullDate(DateTime.Today);
exam.ClassMasterID = util.CheckNullInt(ddlClass.SelectedValue);
exam.SectionMasterID = util.CheckNullInt(ddlSection.SelectedValue);
exam.TermId = util.CheckNullInt(ddlTerm.SelectedValue);
exam.MaxMarks = util.CheckNullInt(txtMaxMarks.Text);
exam.PassMarks = util.CheckNullInt(txtPassMarks.Text);
exam.ExamOrder = util.CheckNullInt(txtExamOrder.Text);
site = new DBSite();
// save or updare
site.BeginTrans();
if (lblExamMasterId.Text == "New")
{
exmBll.SaveExam(site, exam);
message = "\t '" + exam.ExamName + "' Successfully Added !";
}
else
{
exam.ExamMasterId = util.CheckNullInt(lblExamMasterId.Text);
exmBll.UpdateExam(site, exam);
message = "\t '" + exam.ExamName + "' Successfully Updated !";
}
site.Commit();
lblMessage.Text = message;
lblMessage.Visible = true;
ClearErrorMessage();
}
catch (Exception ex)
{
site.RollBack();
if (ex.Message.Contains("UNQ_ExamName"))
{
lblMessage.Text = "\t '" + exam.ExamName + "' is already present !";
lblMessage.Visible = true;
}
else
{
util.HandleExpception(ex, Response);
}
}
finally
{
site.Close();
}
}