protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { pnlServices.Visible = true; pnlAddService.Visible = false; //Get user role if (User.Identity.IsAuthenticated) { string strID = User.Identity.Name; //string strRole = lnUser.GetUserRole(strID); if (User.IsInRole("Admin")) { lblRole.Text = "Admin"; } if (User.IsInRole("Manager")) { lblRole.Text = "Manager"; } } //Bind Sku Groups BD.DataBindToDropDownList(ddlServiceGroupFilter, "SELECT SkuGroupID,SkuGroup FROM EduSphere.SkuGroups WHERE SkuGroupID>=100"); //In case the logged in role is Admin then display delete button. if (User.IsInRole("Admin")) { BD.DataBindToGridView(dgServices, "SELECT * FROM EduSphere.Sku WHERE SkuId>=100", "NA"); } else { BD.DataBindToGridView(dgServicesView, "SELECT * FROM EduSphere.Sku WHERE SkuId>=100", "NA"); } } }
protected void ddlMyPrograms_SelectedIndexChanged(object sender, EventArgs e) { int intProgramID = -99; if (ddlMyPrograms.SelectedValue.ToString() != "Select") { intProgramID = Convert.ToInt32(ddlMyPrograms.SelectedValue.ToString()); } //List of Videos for Chosen Program //string strCourseVideos = string.Format(@"SELECT * FROM EduSphere.CourseVideos WHERE ProgramID={0}", intProgramID); string strCourseVideos = string.Format(@"SELECT * FROM EduSphere.CourseVideos v JOIN EduSphere.Courses c ON v.CourseID=c.CourseID JOIN EduSphere.LearningTokens t ON v.CourseID=t.CourseID WHERE t.TokenStatus='YES' AND t.MemberID=(SELECT MemberID FROM EduSphere.Members WHERE Email='{0}')", User.Identity.Name.ToString()); BD.DataBindToGridView(gvCourseVideos, strCourseVideos, "NA"); //List of Documents for Chosen Program string strCourseDocs = string.Format(@"SELECT * FROM EduSphere.CourseDocs v JOIN EduSphere.Courses c ON v.CourseID=c.CourseID JOIN EduSphere.LearningTokens t ON v.CourseID=t.CourseID WHERE t.TokenStatus='YES' AND t.MemberID=(SELECT MemberID FROM EduSphere.Members WHERE Email='{0}')", User.Identity.Name.ToString()); BD.DataBindToGridView(gvCourseDocs, strCourseDocs, "NA"); }
//Reports for AccountTxs public void GetAccountTxsReport(object sender, CommandEventArgs e) { string queryStr, strCmd, strArg; strCmd = e.CommandName.ToString(); strArg = e.CommandArgument.ToString(); int strSpender = 0; int store = 0; //string format = "yyyy-mm-dd hh:mm:ss"; DateTime dtFrom, dtTo; DateTime.TryParseExact(txtBoxExpFrom.Text, "yyyy-mm-dd", new CultureInfo("en-US"), DateTimeStyles.None, out dtFrom); DateTime.TryParseExact(txtBoxExpTo.Text, "yyyy-mm-dd", new CultureInfo("en-US"), DateTimeStyles.None, out dtTo); string querydtFrom = dtFrom.ToString("mm/dd/yyyy"); string querydtTo = dtTo.ToString("mm/dd/yyyy"); if (ddlSpender.SelectedValue.ToString() != "Select") { strSpender = Convert.ToInt32(ddlSpender.SelectedValue.ToString()); } string strPaymentMode = ddlPayMode.SelectedValue.ToString(); if (ddlSite.SelectedValue.ToString() != "Select") { store = Convert.ToInt32(ddlSite.SelectedValue.ToString()); } switch (strCmd) { case "AllAccountTxsByDate": if (strPaymentMode == "All") { queryStr = string.Format("SELECT em.FullName,et.AccountTxTitleGroup,et.AccountTxTitle,ex.DebitAmount,ex.CreditAmount,ex.AccountTxDate,ex.OrganizationID,ex.AccountTxDetails,ex.PaymentMode,ex.ConfirmationString,ex.DocPath FROM EduSphere.AccountTxs ex JOIN EduSphere.AccountTxTitles et ON ex.AccountTxTitleID=et.AccountTxTitleID JOIN EduSphere.Staff em ON ex.EmployeeID=em.EmployeeID WHERE (ex.AccountTxDate BETWEEN '{0}' AND '{1}') ORDER BY ex.AccountTxDate DESC", querydtFrom, querydtTo); } else { queryStr = string.Format("SELECT em.FullName,et.AccountTxTitleGroup,et.AccountTxTitle,ex.DebitAmount,ex.CreditAmount,ex.AccountTxDate,ex.OrganizationID,ex.AccountTxDetails,ex.PaymentMode,ex.ConfirmationString,ex.DocPath FROM EduSphere.AccountTxs ex JOIN EduSphere.AccountTxTitles et ON ex.AccountTxTitleID=et.AccountTxTitleID JOIN EduSphere.Staff em ON ex.EmployeeID=em.EmployeeID WHERE ex.OrganizationID={0} AND (ex.AccountTxDate BETWEEN '{1}' AND '{2}') AND PaymentMode='{3}' ORDER BY ex.AccountTxDate DESC", store, querydtFrom, querydtTo, strPaymentMode); } if (strArg == "exportToExcel") { gvAccountTxsDashboard.AllowPaging = false; } BD.DataBindToGridView(gvAccountTxsDashboard, queryStr, "NA"); break; case "AccountTxsByDateByGroupByStore": queryStr = string.Format("SELECT em.FullName,et.AccountTxTitleGroup,et.AccountTxTitle,ex.DebitAmount,ex.CreditAmount,ex.AccountTxDate,ex.OrganizationID,ex.AccountTxDetails,ex.PaymentMode,ex.ConfirmationString,ex.DocPath FROM EduSphere.AccountTxs ex JOIN EduSphere.AccountTxTitles et ON ex.AccountTxTitleID=et.AccountTxTitleID JOIN EduSphere.Staff em ON ex.EmployeeID=em.EmployeeID WHERE em.EmployeeID='{0}' AND (ex.AccountTxDate BETWEEN '{1}' AND '{2}') ORDER BY ex.AccountTxDate DESC", strSpender, querydtFrom, querydtTo); if (strArg == "exportToExcel") { gvAccountTxsDashboard.AllowPaging = false; } BD.DataBindToGridView(gvAccountTxsDashboard, queryStr, "NA"); break; default: break; } lblTotalAccountTxs.Text = GetColumnTotalDec(gvAccountTxsDashboard, 4); lblTotalInflow.Text = GetColumnTotalDec(gvAccountTxsDashboard, 5); if (strArg == "exportToExcel") { ExportToExcel(gvAccountTxsDashboard); } }
//Display panel to choose subject and call function to create test paper protected void ManageTestPanelVisibility(object sender, CommandEventArgs e) { string cmdName = e.CommandName.ToString(); pnlCreateTest.Visible = false; pnlTest.Visible = false; pnlCandidate.Visible = false; switch (cmdName) { case "pnlCreateTest": pnlCreateTest.Visible = true; pnlAssignCandidates.Visible = false; pnlCandidate.Visible = false; pnlViewTestResults.Visible = false; break; case "pnlAssignCandidates": pnlCreateTest.Visible = false; pnlAssignCandidates.Visible = true; pnlCandidate.Visible = false; pnlViewTestResults.Visible = false; //Test Details string qTest = string.Format(@"SELECT TOP 1 * FROM Evaluations.TestConfigParameters c JOIN Evaluations.ObjTestPaper p ON c.TestID=p.TestID ORDER BY c.TestID DESC"); DataTable dtTest = new DataTable(); SqlCommand cmd = new SqlCommand(qTest, BD.ObjCn); dtTest = BD.GetDataTable(cmd); lblTestTitle.Text = dtTest.Rows[0]["TestTitle"].ToString(); lblTestID.Text = dtTest.Rows[0]["TestID"].ToString(); //Candidates string q = ""; if (e.CommandArgument.ToString() == "SearchCandidate") { q = string.Format(@"SELECT * FROM Evaluations.CandidateTestAttendance a JOIN EduSphere.Members n ON a.CandidateID=n.MemberID WHERE a.TestID={0} AND n.FullName LIKE '%{1}%'", Convert.ToInt32(lblTestID.Text), txtBoxSearch.Text); } else { q = string.Format(@"SELECT * FROM Evaluations.CandidateTestAttendance a JOIN EduSphere.Members n ON a.CandidateID=n.MemberID WHERE a.TestID={0}", Convert.ToInt32(lblTestID.Text)); } BD.DataBindToGridView(gvAssignCandidates, q, "NA"); break; case "pnlTest": pnlCreateTest.Visible = false; pnlCandidate.Visible = true; pnlAssignCandidates.Visible = false; pnlViewTestResults.Visible = false; string query = string.Format(@"select * from Evaluations.ObjTestPaper qp JOIN EduSphere.Courses cs ON qp.CourseID=cs.CourseID"); SqlDataAdapter da = new SqlDataAdapter(query, BD.ObjCn); DataSet ds = new DataSet(); da.Fill(ds, "Question"); //Display course ID DataRow dr = ds.Tables[0].Rows[0]; lblCourseName.Text = dr["CourseID"].ToString(); lblCourseTitle.Text = dr["CourseTitle"].ToString(); txtName.Text = User.Identity.Name; break; case "pnlViewTestResults": pnlCreateTest.Visible = false; pnlCandidate.Visible = false; pnlAssignCandidates.Visible = false; pnlViewTestResults.Visible = true; //Filter option for selecting TestID BD.DataBindToDropDownList(ddlTestList, string.Format(@"SELECT r.TestID, TestTitle FROM Evaluations.OnlineTestResults r JOIN Evaluations.TestConfigParameters c ON r.TestID=c.TestID GROUP BY r.TestID,TestTitle")); //Display Results--Do not display by default. Deisplay after the test iD is selected //string qresults = string.Format(@"SELECT * FROM Evaluations.OnlineTestResults r JOIN EduSphere.Members n ON r.CandidateID=n.MemberID"); //BD.DataBindToGridView(gvTestResults, qresults, "NA"); break; default: break; } }
//Display panel to choose subject and call function to create test paper protected void ManageTestPanelVisibility(object sender, CommandEventArgs e) { string cmdName = e.CommandName.ToString(); switch (cmdName) { case "pnlTest": pnlCandidate.Visible = true; pnlTest.Visible = false; //made visible to candidate SUBSCRIBED for test pnlQuestions.Visible = false; //is made visible after start click pnlResult.Visible = false; //Extract test ID //int quizid = Convert.ToInt32(lblTestID.Text.ToString()); DataTable dtTestStatus = new DataTable(); //select candidate details based on Email and latest TestID string queryStatus = string.Format(@"SELECT FullName,MemberID,AttendanceStatus FROM EduSphere.Members n JOIN Evaluations.CandidateTestAttendance a ON n.MemberID=a.CandidateID WHERE n.Email='{0}' AND a.TestID={1}", User.Identity.Name.ToString(), quizid); SqlCommand cmd = new SqlCommand(queryStatus, BD.ObjCn); dtTestStatus = BD.GetDataTable(cmd); if ((dtTestStatus.Rows[0]["AttendanceStatus"].ToString() == "SUBSCRIBED") && testDate <= DateTime.Now && DateTime.Now <= testClosureDate) { pnlTest.Visible = true; lblCandidateID.Text = dtTestStatus.Rows[0]["MemberID"].ToString(); lblCandidateName.Text = dtTestStatus.Rows[0]["FullName"].ToString(); //BD.DataBindToTable(lblCandidateID, string.Format(@"SELECT MemberID FROM EduSphere.Members WHERE Email='{0}'", User.Identity.Name.ToString())); //string query = string.Format("SELECT TOP * FROM Evaluations.ObjTestPaper qp JOIN EduSphere.Courses cs ON qp.CourseID=cs.CourseID"); string query = string.Format(@"SELECT TOP 1 * FROM Evaluations.ObjTestPaper p JOIN Evaluations.TestConfigParameters c ON p.TestID=c.TestID"); SqlDataAdapter da = new SqlDataAdapter(query, BD.ObjCn); DataSet ds = new DataSet(); da.Fill(ds, "Question"); //Display Test ID DataRow dr = ds.Tables[0].Rows[0]; lblTestID.Text = dr["TestID"].ToString(); lblTestTitle.Text = dr["TestTitle"].ToString(); //CREATE Answer sheet for candidate SqlCommand ObjCmd = new SqlCommand("spOnlineTestTransaction", BD.ObjCn); ObjCmd.CommandType = CommandType.StoredProcedure; ObjCmd.Parameters.AddWithValue("@CandidateID", Convert.ToInt32(lblCandidateID.Text)); ObjCmd.Parameters.AddWithValue("@TestID", Convert.ToInt32(lblTestID.Text)); BD.UpdateParameters(ObjCmd); //Allow to start the test btnStartExam.Visible = true; } else { lblTestTitle.Text = "No Test Scheduled"; btnStartExam.Visible = false; pnlTest.Visible = false; } //lblCandidateName.Text = User.Identity.Name; break; case "pnlResult": pnlCandidate.Visible = false; pnlTest.Visible = false; pnlQuestions.Visible = false; pnlResult.Visible = true; //Fetch details of candidate, exam DataTable dtTestCandidate = new DataTable(); string queryCandidate = string.Format(@"SELECT TOP 1 FullName,MemberID,AttendanceStatus,p.TestID,p.TestTitle,CandidateID FROM Evaluations.CandidateTestAttendance a JOIN Evaluations.ObjTestPaper p ON a.TestID=p.TestID JOIN EduSphere.Members n ON a.CandidateID=n.MemberID WHERE n.Email='{0}'", User.Identity.Name.ToString()); SqlCommand cmdCandidate = new SqlCommand(queryCandidate, BD.ObjCn); dtTestCandidate = BD.GetDataTable(cmdCandidate); lblResultTestID.Text = dtTestCandidate.Rows[0]["TestID"].ToString(); lblResultTestTitle.Text = dtTestCandidate.Rows[0]["TestTitle"].ToString(); lblResultCandidateName.Text = dtTestCandidate.Rows[0]["FullName"].ToString(); lblResultCandidateID.Text = dtTestCandidate.Rows[0]["MemberID"].ToString(); lblAttendanceStatus.Text = dtTestCandidate.Rows[0]["AttendanceStatus"].ToString(); //display result if candidate APPEARED for EXAM if (dtTestCandidate.Rows[0]["AttendanceStatus"].ToString() == "APPEARED") { string qResult = string.Format(@"SELECT * FROM Evaluations.OnlineTestTransaction WHERE CandidateID={0} AND TestID={1} AND EvaluationStatus!='{2}'", Convert.ToInt32(dtTestCandidate.Rows[0]["CandidateID"].ToString()), Convert.ToInt32(dtTestCandidate.Rows[0]["TestID"].ToString()), "SKIPPED"); BD.DataBindToGridView(gvResult, qResult, "NA"); } break; default: break; } }