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