private void btnCombined_Click(object sender, EventArgs e) { var splitter = clsFunctions.SplitByString(listView1.SelectedItems[0].SubItems[1].Text, "-"); var form = new frmSubmitText("Combined Score", "Please enter combined score: (%)"); form.ShowDialog(); var date = new frmSubmitDate("Combined Score","Please enter date for assessment:" ); date.ShowDialog(); var outcome = new frmSubmitText("Outcome", "Please enter outcome: (NYC/Competent/Requirements met"); outcome.ShowDialog(); if (date.Selection.ToString("dd/MM/yyyy") == "01/01/0001") return; if (form.Selection == "***") return; try { if (Convert.ToDouble(form.Selection) > 100) { MessageBox.Show("Mark cannot be greater than 100%.", "Invalid Mark"); return; } } catch { MessageBox.Show("Invalid Mark submitted", "Invalid Format"); return; } if (outcome.Selection == "***") return; if (Database.Count("enrollmentcomponents", "idCourseComponents = '" + listView2.SelectedItems[0].Text + "' AND idEnrollments = '" + splitter[1] + "'") > 0) Database.Command("UPDATE enrollmentcomponents SET result = '" + form.Selection + "' , outcome = '" + outcome.Selection + "', dateModerated = '" + date.Selection.ToString("yyyyMMdd") + "', moderated = 'yes', printed = 'yes' WHERE idCourseComponents = '" + listView2.SelectedItems[0].Text + "' AND idEnrollments = '" + splitter[1] + "'"); else { Database.Command("INSERT INTO enrollmentcomponents(idCourseComponents,idEnrollments,result, outcome, dateModerated, moderated, printed) VALUES('" + listView2.SelectedItems[0].Text + "','" + splitter[1] + "','" + form.Selection + "','" + outcome.Selection + "','" + date.Selection.ToString("yyyyMMdd") + "','yes','yes')"); } load_results(listView2.SelectedItems[0].Text, splitter[1]); }
private void temporaryPostponementButton_ItemClick(object sender, ItemClickEventArgs e) { var number = new frmSubmitText("Learner Number", "Please enter learner number"); number.ShowDialog(); var reason = new frmSubmitText("Reason", "Please enter reason for cancellation"); reason.ShowDialog(); var dateReturn = new frmSubmitDate("Expected Return Date", "Expected Return Date"); dateReturn.ShowDialog(); if (Database.Count("learner_details", "LearnerNumber = '" + number.Selection + "' AND Division = '" + GlobalProperties.loggedOnUserDivison + "'") == 0) { if (number.Selection == "***" || reason.Selection == "***" || !dateReturn.status) return; MessageBox.Show("Invalid Learner Number", "Error"); return; } var dataList = Database.Query(@"SELECT * FROM learner_details a JOIN enrollments b ON b.LearnerKey = a.LearnerKey JOIN enrollmentscourses c ON c.idEnrollments = b.idEnrollments LEFT JOIN ( SELECT * FROM courses d WHERE d.type<> 'CC' ) as e on e.idCourses = c.idCourses WHERE a.Division = '" + GlobalProperties.loggedOnUserDivison + "' AND a.LearnerNumber = '" + number.Selection + "'"); var data = dataList.Tables[0].Rows[0]; /* report.DataDefinition.FormulaFields["date"].Text = "\"" + DateTime.Now.ToLongDateString() + "\""; report.DataDefinition.FormulaFields["college"].Text = "\"" + clsGlobal.LoggedOnUserDivision + "\""; report.DataDefinition.FormulaFields["principle"].Text = "\"" + clsGlobal.DivisionPrinciple + "\""; report.DataDefinition.FormulaFields["learnerNumber"].Text = "\"" + number.Selection + "\""; report.DataDefinition.FormulaFields["reason"].Text = "\"" + reason.Selection + "\""; report.DataDefinition.FormulaFields["currentProgram"].Text = String.Format("\"{0}\"", (data == null ? "Custom Course" : Convert.ToString(data["Name1"]))); report.DataDefinition.FormulaFields["name"].Text = "\"" + Convert.ToString(data["Name"]) + " " + Convert.ToString(data["Surname"]) + "\""; */ Reports.rptPostponementContract report = new rptPostponementContract(); report.dateCell.Text = DateTime.Now.ToLongDateString(); report.collegeCell.Text = GlobalProperties.LoggedOnUserDivision; report.principalCell.Text = GlobalProperties.DivisionPrinciple; report.learnerNumberCell.Text = number.Selection; report.reasonCell.Text = reason.Selection; report.currentCourseCell.Text = (data["Name1"] == DBNull.Value ? "Custom Course" : Convert.ToString(data["Name1"])); report.learnerNameCell.Text = Convert.ToString(data["Name"]) + " " + Convert.ToString(data["Surname"]); report.expectedDateCell.Text = dateReturn.Selection.ToLongDateString(); report.ShowPreviewDialog(); }
private void barButtonItem15_ItemClick(object sender, ItemClickEventArgs e) { var number = new frmSubmitText("Learner Number", "Please enter learner number"); number.ShowDialog(); while (Database.Count("learner_details", "LearnerNumber = '" + number.Selection + "' AND Division = '" + GlobalProperties.loggedOnUserDivison + "'") == 0) { if (number.Selection == "***") return; MessageBox.Show("Invalid Learner Number", "Error"); number.ShowDialog(); } clsFunctions.WriteLog("Generated confirmation letter for learner: " + number.Selection); var date = new frmSubmitDate("Submit Date", "Please select Expiry Date"); date.ShowDialog(); if (date.status) { var report2 = new rptConfrimationLetter(); var report = new rptBankConfirmation(); var data = Database.Row("learner_details a, enrollments b, enrollmentscourses c, courses d", "a.Division = '" + GlobalProperties.loggedOnUserDivison + "' AND a.LearnerNumber = '" + number.Selection + "' AND b.LearnerKey = a.LearnerKey AND c.idEnrollments = b.idEnrollments AND" + " d.idCourses = c.idCourses and not d.Type = 'CC'"); if (data == null) { data = Database.Row("learner_details a, enrollments b, enrollmentscourses c, courses d", "a.Division = '" + GlobalProperties.loggedOnUserDivison + "' AND a.LearnerNumber = '" + number.Selection + "' AND b.LearnerKey = a.LearnerKey AND c.idEnrollments = b.idEnrollments AND" + " d.idCourses = c.idCourses"); } report.date.Text = DateTime.Today.ToString("dd/MM/yyyy"); report.regDate.Text = ((DateTime)data["regDate1"]).ToString("dd/MM/yyyy"); report.expiryDate.Text = ((DateTime)data["regDate1"]).AddYears(2 * Convert.ToInt16(data["numRegistrations"])).ToString("dd/MM/yyyy"); report.learnerNumber.Text = number.Selection; report.principal.Text = GlobalProperties.DivisionPrinciple; report.fullName.Text = Convert.ToString(data["Name"]) + " " + Convert.ToString(data["Surname"]); report.header.Rtf = report.header.Rtf.Replace("?formalName?", Convert.ToString(data["surname"]) + ", " + Convert.ToString(data["name"])) .Replace("?fullName?", Convert.ToString(data["Name"]) + " " + Convert.ToString(data["Surname"])); report.course.Text = Convert.ToString(data["Name1"]); report.idNumber.Text = Convert.ToString(data["ID"]); report.footer.Rtf = report.footer.Rtf.Replace("?expiryDate?", date.Selection.ToString("dd/MM/yyyy")); report.learnerType.Text = (MessageBox.Show("Is this a part time learner?", "Part Time Learner", MessageBoxButtons.YesNo) == DialogResult.Yes ? "Part Time" : "Full Time"); report.street.Text = Convert.ToString(data["street"]); report.suburb.Text = Convert.ToString(data["subburb"]); report.city.Text = Convert.ToString(data["city"]); report.code.Text = Convert.ToString(data["postalCode"]); //report.DataDefinition.FormulaFields["college"].Text = "\"" + clsGlobal.LoggedOnUserDivision + "\""; //report.DataDefinition.FormulaFields["course"].Text = "\"" + Convert.ToString(data["Name1"]) + "\""; //logs.WriteXml(@"C:\tmpXml.xml"); //var frmReport = new frmReport(report2) { Text = "Bank Confirmation Request" }; report.ShowPreviewDialog(); //frmReport.ShowDialog(); } }
private void postponementActivationButton_ItemClick(object sender, ItemClickEventArgs e) { var theForm = new frmSubmitText("Submit Learner Number", "Submit Learner Number"); theForm.ShowDialog(); if (theForm.Selection == "***") return; var startDateSelect = new frmSubmitDate("Submit Date", "Please submit start date:"); startDateSelect.ShowDialog(); if (startDateSelect.status == false) return; var startDate = startDateSelect.Selection; var endDateSelect = new frmSubmitDate("Submit Date", "Please submit end date:"); endDateSelect.ShowDialog(); if (endDateSelect.status == false) return; var endDate = endDateSelect.Selection; if (Database.Count("learner_details", "learnerNumber = '" + theForm.Selection + "' AND division = '" + GlobalProperties.loggedOnUserDivison + "'") == 0) { MessageBox.Show("Learner Not Found."); return; } var data = Database.Query(@"SELECT learnerNumber, CONCAT(Name,' ',Surname)as Name, PostalStreet, PostalSubburb, PostalCity, postalCode, ld.division, principle, d.street, d.subburb, d.city, d.code, d.tel, d.fax, d.AccountNumber FROM learner_details ld JOIN division d on d.divisionAbrev = ld.division WHERE division = '" + GlobalProperties.loggedOnUserDivison + "' AND learnerNumber = '" + theForm.Selection + "'"); //data.WriteXml("C:\\LAD_DATA_DUMP\\cancellationReply.xml", XmlWriteMode.WriteSchema); var report = new rptPostponementActivation(); report.detailLabel.Text = report.detailLabel.Text .Replace("[start_date]", startDate.ToString("dd MMMM yyyy")) .Replace("[end_date]", endDate.ToString("dd MMMM yyyy")) .Replace("[counsel_date]", DateTime.Now.AddDays(14).ToString("dd MMMM yyyy")); report.DataSource = data; report.ShowPreviewDialog(); }
private void massAssessmentReport_ItemClick(object sender, ItemClickEventArgs e) { bool allColleges = DevExpress.XtraEditors.XtraMessageBox.Show("This report can take a long time to compile. Please wait for the report to finish before printing or exporting. Do you want to continue?", "Warning!", MessageBoxButtons.YesNo) == DialogResult.Yes; if (!allColleges) return; var dateThing = new frmSubmitDate("Select Date for attendance.", "Attendance Date"); dateThing.ShowDialog(); if (!dateThing.status) return; var data = Database.Query(@"SELECT ld.learnerKey, e.idCourseComponents, CONCAT(e.idCourseComponents,'%'), e.Name, d.result, x.result, if(x.CapturedDate,x.CapturedDate,d.resultDate) as CapturedDate, d.dateModerated, d.moderated, d.outcome, x.CapturedDate as resultDate, v.Name, a.idEnrollments FROM learner_details ld JOIN enrollments a on a.learnerKey = ld.learnerKey JOIN enrollmentscourses b on a.idEnrollments = b.idEnrollments LEFT JOIN ( SELECT e.idEnrollments, ld.learnerKey, c.Name FROM enrollmentsCourses ec JOIN courses c on c.idCourses = ec.idCourses JOIN enrollments e USING(idEnrollments) JOIN learner_details ld USING (LearnerKey) WHERE c.Type <> 'CC' and e.completed IS NULL AND ld.division = '" + GlobalProperties.loggedOnUserDivison + @"' ) as v ON v.idEnrollments = a.idEnrollments LEFT JOIN courses cor on cor.idCourses = b.idCourses JOIN coursecomponents c on c.idCourses = b.idCourses JOIN components e on e.idCourseComponents = c.idComponents LEFT OUTER JOIN enrollmentcomponents d on d.idEnrollments = a.idenrollments AND d.idCourseComponents = c.idComponents LEFT JOIN ( SELECT r.capturedDate, r.idEnrollments, r.idCourseComponents, (r.result/g.totalMark)*100 as result FROM results r JOIN components g on g.idCourseComponents = r.idCourseComponents JOIN enrollments e on e.idEnrollments = r.idEnrollments JOIN learner_details ld on ld.learnerKey = e.LearnerKey WHERE g.componentAbrev = 'EX' and (NOT g.Name LIKE '%Pre %') AND ld.learnerStatus IN ('Active','Handover_Attending') AND ld.division = '" + GlobalProperties.loggedOnUserDivison + @"' ORDER BY capturedDate DESC ) as x ON x.idEnrollments = a.idEnrollments AND x.idCourseComponents LIKE CONCAT(e.idCourseComponents,'%') WHERE ld.division = '" + GlobalProperties.loggedOnUserDivison + @"' AND ld.learnerStatus IN ('Active','Handover_Attending') AND ((NOT (e.isElective = '1' AND d.idEnrollments is null)) OR isElective IS NULL) AND (( NOT d.Status = 'Inactive') OR d.Status IS Null) AND a.completed IS NULL GROUP BY a.idEnrollments, c.idCourseComponents ORDER BY ld.learnerNumber, a.idEnrollments, e.idCourseComponents"); var myTable = Database.Table(@"SELECT la.*, lb.*, ld.learnerKey, DATE(la.dateTime) as InDate, DATE(lb.dateTime) as outDate, TIME(SUBTIME(TIME(lb.dateTime),TIME(la.dateTime))) as Duration, d.divisionName FROM learnerActivity la JOIN learner_details ld on ld.learnerNumber = la.learnerNumber AND ld.division = la.division LEFT JOIN learnerActivity lb on lb.idLearnerActivity = la.partnerID JOIN division d on d.divisionAbrev = la.division WHERE DATE(la.`dateTime`) > " + dateThing.Selection.ToString("yyyyMMdd") + " AND DATE(lb.`dateTime`) > " + dateThing.Selection.ToString("yyyyMMdd") + " AND la.division = '" + GlobalProperties.loggedOnUserDivison + @"' AND la.`type` IN ('Attendance','Exam') AND ld.learnerStatus IN ('Active','Handover_Attending') AND la.details = 'IN' ORDER BY la.learnerNumber, la.dateTime"); data.Tables.Add(myTable); var learners = Database.Table(@"SELECT *, CONCAT(Name,' ',Surname) as learnerName , idEnrollments FROM learner_details ld JOIN enrollments e on e.learnerKey = ld.learnerKey WHERE division = '" + GlobalProperties.loggedOnUserDivison + "' AND learnerStatus IN ('Active','Handover_Attending') AND e.completed IS NULL"); learners.TableName = "learners"; data.Tables.Add(learners); var relation = new DataRelation("one", new DataColumn[] {data.Tables["learners"].Columns["learnerKey"]}, new DataColumn[] {data.Tables["GeneratedTable"].Columns["learnerKey"]},false); var relation2 = new DataRelation("two",data.Tables["learners"].Columns["idEnrollments"] ,data.Tables["MyTable"].Columns["idEnrollments"]); data.Relations.Add(relation); data.Relations.Add(relation2); var Report = new rptAssesmentReportNew(); Report.divisionLabel.Text = GlobalProperties.LoggedOnUserDivision; var college = Database.Row("SELECT * FROM division WHERE divisionAbrev = '" + GlobalProperties.loggedOnUserDivison+ "'"); Report.streetText.Text = Convert.ToString(college["Street"]); Report.suburbText.Text = Convert.ToString(college["Subburb"]); Report.cityText.Text = Convert.ToString(college["City"]); Report.telText.Text = "Tel. " + Convert.ToString(college["Tel"]); Report.commentText.Text = Report.commentText.Text.Replace("##","This Assesment report was generated on " + DateTime.Now.ToString("dd/MM/yyyy")); Report.DataSource = data; Report.ShowPreviewDialog(); //data.WriteXml(@"C:\LAD_DATA_DUMP\assRepMass.xml", XmlWriteMode.WriteSchema); }
private void ackDebtButton_ItemClick(object sender, ItemClickEventArgs e) { var learnerNumber = new frmSubmitText("Learner Number", "Submit Learner Number:"); learnerNumber.ShowDialog(); DataRow studentRow; if (learnerNumber.Selection == "***") return; try { studentRow = Database.Row("learner_details", "learnerNumber = '" + learnerNumber.Selection + "' AND division = '" + GlobalProperties.loggedOnUserDivison + "'"); } catch { MessageBox.Show("Learner not found!"); return; } var amount = new frmSubmitText("Indebted Amount", "Please submit indebted amount:"); amount.ShowDialog(); double amountDouble = 0; if (amount.Selection != "***" && !Double.TryParse(amount.Selection, out amountDouble)) { MessageBox.Show("Invalid amount. Please try again!"); return; } var dueDate = new frmSubmitDate("Due Date", "Please submit due date:"); dueDate.ShowDialog(); if (learnerNumber.Selection == "***" || amount.Selection == "***" || !dueDate.status) return; var collegeData = Database.Row("division", "divisionAbrev = '" + GlobalProperties.loggedOnUserDivison + "'"); var report = new rptAcknowledgementOfDebt(); report.collegeName.Text = GlobalProperties.LoggedOnUserDivision; report.collegeStreet.Text = collegeData["Street"].ToString() + ", " + collegeData["Subburb"].ToString(); report.collegeCity.Text = collegeData["City"].ToString() + ", " + collegeData["Code"].ToString(); report.telephoneLabel.Text = "TEL: " + collegeData["Tel"].ToString(); report.faxLabel.Text = "FAX: " + collegeData["Fax"].ToString(); report.nameLabel.Text = report.nameLabel.Text.Replace("[StudentName]", studentRow["Name"].ToString() + " " + studentRow["Surname"].ToString()); report.idLabel.Text = report.idLabel.Text.Replace("[StudentID]", studentRow["ID"].ToString()); report.studentStreet.Text = studentRow["Street"].ToString() + ", " + studentRow["Subburb"].ToString(); report.studentCity.Text = studentRow["City"].ToString() + ", " + studentRow["postcode"].ToString(); report.studentName.Text = studentRow["Name"].ToString() + " " + studentRow["Surname"].ToString(); report.detailsLabel.Text = report.detailsLabel.Text.Replace("[Amount]", amountDouble.ToString("F")).Replace("[Date]", dueDate.Selection.ToString("dd/MM/yyyy")); report.bankDetails.Text = report.bankDetails.Text.Replace("[AccountNumber]", collegeData["AccountNumber"].ToString()); report.principalName.Text = collegeData["Principle"].ToString(); report.ShowPreviewDialog(); }
private void barButtonItem20_ItemClick(object sender, ItemClickEventArgs e) { var number = new frmSubmitText("Learner Number", "Please enter learner number"); number.ShowDialog(); while (Database.Count("learner_details", "LearnerNumber = '" + number.Selection + "' AND Division = '" + GlobalProperties.loggedOnUserDivison + "'") == 0) { if (number.Selection == "***") return; MessageBox.Show("Invalid Learner Number", "Error"); number.ShowDialog(); } var enrollments = Database.Query("learner_details a, enrollments b", "a.Division = '" + GlobalProperties.loggedOnUserDivison + "' AND a.LearnerNumber = '" + number.Selection + "' AND b.LearnerKey = a.LearnerKey"); var myList = new ArrayList(); foreach (DataRow theRow in enrollments.Tables[0].Rows) { try { myList.Add((string)("ENR" + theRow["idEnrollments"] + " - " + ((DateTime)theRow["regDate1"]).ToString("dd/MM/yyyy"))); } catch { } } var theList = new frmSelectSingle("Please Select Enrollment","Select Enrollment",myList); theList.ShowDialog(); if (theList.Selection == "***") return; string[] theEnrollment = theList.Selection.Split(new string[] {" - "},StringSplitOptions.RemoveEmptyEntries); var endDateSelect = new frmSubmitDate("Submit Date", "Please submit end of studies date:"); endDateSelect.ShowDialog(); if (endDateSelect.status == false) return; var report = new rptDiplomaRequest(); var data = Database.Query("learner_details a, enrollments b, enrollmentscourses c, courses d", "a.Division = '" + GlobalProperties.loggedOnUserDivison + "' AND a.LearnerNumber = '" + number.Selection + "' AND b.LearnerKey = a.LearnerKey AND c.idEnrollments = b.idEnrollments AND" + " d.idCourses = c.idCourses AND b.idEnrollments = " + theEnrollment[0].Replace("ENR","") + " ORDER BY d.Type DESC LIMIT 1"); report.startDateLabel.Text = theEnrollment[1]; report.endDateTraining.Text = endDateSelect.Selection.ToString("dd/MM/yyyy"); report.prinicipleLabel.Text = GlobalProperties.DivisionPrinciple; report.DataSource = data; report.ShowPreviewDialog(); //data.WriteXml(@"C:\LAD_DATA_DUMP\diploma.xml", XmlWriteMode.WriteSchema); //report.DataDefinition.FormulaFields["date"].Text = "\"" + DateTime.Now.ToLongDateString() + "\""; //report.DataDefinition.FormulaFields["startdate"].Text = "\"" + theEnrollment[1] + "\""; //report.DataDefinition.FormulaFields["enddate"].Text = "\"" + endDateSelect.Selection.ToString("dd/MM/yyyy") + "\""; //report.DataDefinition.FormulaFields["college"].Text = "\"" + clsGlobal.LoggedOnUserDivision + "\""; //report.DataDefinition.FormulaFields["learnerNumber"].Text = "\"" + number.Selection + "\""; //report.DataDefinition.FormulaFields["principle"].Text = "\"" + clsGlobal.DivisionPrinciple + "\""; //report.DataDefinition.FormulaFields["learnerName"].Text = "\"" + Convert.ToString(data["Name"]) + "\""; //report.DataDefinition.FormulaFields["surname"].Text = "\"" + Convert.ToString(data["Surname"]) + "\""; //report.DataDefinition.FormulaFields["diplomaName"].Text = "\"" + Convert.ToString(data["Name1"]) + "\""; //report.DataDefinition.FormulaFields["respParty"].Text = "\"" + clsGlobal.loggedOnUser + "\""; ////report.DataDefinition.FormulaFields["course"].Text = "\"" + Convert.ToString(data["Name1"]) + "\""; ////data.WriteXml(@"C:\LAD_DATA_DUMP\tmpXml.xml"); //var frmReport = new frmReport(report) { Text = "Diploma Request" }; //frmReport.ShowDialog(); }
private void listView3_DoubleClick(object sender, EventArgs e) { var splitter = clsFunctions.SplitByString(listView1.SelectedItems[0].SubItems[1].Text, "-"); try { if (headOfficeCheck.Checked == false && Database.Retrieve("enrollmentcomponents", "idEnrollments = '" + splitter[1] + "' AND idCourseComponents = '" + listView2.SelectedItems[0].Text + "'", "moderated") == "yes") return; } catch { }; var select = new frmSubmitText("Learner Marks", "Enter Learner Mark:"); select.ShowDialog(); if (select.Selection == "***") { select.Close(); return; } try { while (!clsFunctions.IsNumeric(select.Selection)) { MessageBox.Show("Please enter valid score."); if (select.Selection == "***") return; select.ShowDialog(); } while (!clsFunctions.IsNumeric(select.Selection) || Convert.ToDouble(select.Selection) > Convert.ToDouble(listView3.FocusedItem.SubItems[2].Text)) { MessageBox.Show("Please enter valid score."); select.ShowDialog(); } } catch { MessageBox.Show("Error in input format. Please try again."); return; } var mark = select.Selection; select.Close(); select.Dispose(); if (listView3.FocusedItem.SubItems[7].Text == "FASS") { //mark = Convert.ToString(Math.Round(Convert.ToDouble(mark), MidpointRounding.AwayFromZero)); try { if ( Convert.ToDouble(Database.Retrieve("results", "idEnrollments = '" + splitter[1] + "' and idCourseComponents = '" + listView3.SelectedItems[0].Text + "'", "attempt")) >= 3) { if (MessageBox.Show("Number of attempts exceeds 3. Do you want to enter a correction?", "Too Many Attempts",MessageBoxButtons.YesNo,MessageBoxIcon.Question) == DialogResult.No) return; } } catch (Exception) {} var date = new frmSubmitDate("Date", "Please select date for this attempt:"); date.ShowDialog(); if (date.Selection.ToString("dd/MM/yyyy") == "01/01/0001") { MessageBox.Show("Process Aborted"); return; } try { if (Convert.ToDouble(Database.Retrieve("results", "idEnrollments = '" + splitter[1] + "' and idCourseComponents = '" + listView3.SelectedItems[0].Text +"'","attempt")) > 0 && MessageBox.Show("Is this a correction of an old mark?", "Attempt or Correction", MessageBoxButtons.YesNo) == DialogResult.No) { Database.Command("UPDATE results SET result = '" + mark + "', attempt = attempt + 1, capturedDate = '" + date.Selection.ToString("yyyyMMdd") + "', date = '" + DateTime.Now.ToString("yyyyMMdd") + "', serialNumber = '" + textBox2.Text + "' WHERE idEnrollments = '" + splitter[1] + "' and idCourseComponents = '" + listView3.SelectedItems[0].Text + "'"); } else { Database.Command("UPDATE results SET result = '" + mark + "', capturedDate = '" + date.Selection.ToString("yyyyMMdd") + "', date = '" + DateTime.Now.ToString("yyyyMMdd") + "', serialNumber = '" + textBox2.Text + "' WHERE idEnrollments = '" + splitter[1] + "' and idCourseComponents = '" + listView3.SelectedItems[0].Text + "'"); } //var update = Database.Query("results", // "idEnrollments = '" + splitter[1] + "' and idCourseComponents = '" + // listView3.SelectedItems[0].Text + "'"); //update.Tables[0].Rows[0]["result"] = mark; //update.Tables[0].Rows[0]["attempt"] = Convert.ToString(Convert.ToDouble(Database.Retrieve("results", // "idEnrollments = '" + // splitter[1] + // "' and idCourseComponents = '" + // listView3. // SelectedItems // [0]. // Text + // "'", // "attempt")) + // 1); //update.Tables[0].Rows[0]["date"] = date.Selection; //update.Tables[0].Rows[0]["serialNo"] = textBox2.Text; //Database.Update(update, "results", // "idEnrollments = '" + splitter[1] + "' and idCourseComponents = '" + // listView3.SelectedItems[0].Text + "'"); } catch { string[] columns = { "idEnrollments", "idCourseComponents", "Result", "Attempt", "capturedDate","Date", "serialNumber" }; try { string[] values = { splitter[1], listView3.SelectedItems[0].Text, mark, Convert.ToString(Convert.ToDouble(Database.Retrieve("results", "idEnrollments = '" + splitter[1] + "' and idCourseComponents = '" + listView3. SelectedItems [0]. Text + "'", "attempt")) + 1), date.Selection.ToString("yyyyMMdd"), DateTime.Now.ToString("yyyyMMdd"), textBox2.Text }; Database.Insert("results", columns, values); } catch { string[] values = { splitter[1], listView3.SelectedItems[0].Text, mark, "1", date.Selection.ToString("yyyyMMdd"), DateTime.Now.ToString("yyyyMMdd"), textBox2.Text }; Database.Insert("results", columns, values); } } listView3.FocusedItem.SubItems[3].Text = mark; listView3.FocusedItem.SubItems[5].Text = Convert.ToString(Convert.ToDouble(Database.Retrieve("results", "idEnrollments = '" + splitter[1] + "' and idCourseComponents = '" + listView3. SelectedItems [0]. Text + "'", "attempt"))) ; listView3.FocusedItem.SubItems[6].Text = date.Selection.ToString("dd/MM/yyyy"); if (System.Text.RegularExpressions.Regex.IsMatch(listView3.FocusedItem.SubItems[1].Text, ".*Theory.*")) { if (Math.Round(Convert.ToDouble(listView3.FocusedItem.SubItems[3].Text) / Convert.ToDouble(listView3.FocusedItem.SubItems[2].Text) * 100) >= 60) { listView3.FocusedItem.SubItems[4].Text = "3 - Requirements have been met"; listView3.FocusedItem.ForeColor = Color.Blue; } else if (Math.Round(Convert.ToDouble(listView3.FocusedItem.SubItems[3].Text) / Convert.ToDouble(listView3.FocusedItem.SubItems[2].Text) * 100) <= 59) { listView3.FocusedItem.SubItems[4].Text = "2 - Some requirements have been met"; listView3.FocusedItem.ForeColor = Color.Red; } else if (Math.Round(Convert.ToDouble(listView3.FocusedItem.SubItems[3].Text) / Convert.ToDouble(listView3.FocusedItem.SubItems[2].Text) * 100) <= 49) { listView3.FocusedItem.SubItems[4].Text = "1 - Requirements have not been met"; listView3.FocusedItem.ForeColor = Color.Red; } } else if (System.Text.RegularExpressions.Regex.IsMatch(listView3.FocusedItem.SubItems[1].Text, ".*Skills.*")) { if (Math.Round(Convert.ToDouble(listView3.FocusedItem.SubItems[3].Text) / Convert.ToDouble(listView3.FocusedItem.SubItems[2].Text) * 100) >= 85) { listView3.FocusedItem.SubItems[4].Text = "3 - Requirements have been met"; listView3.FocusedItem.ForeColor = Color.Blue; } else if (Math.Round(Convert.ToDouble(listView3.FocusedItem.SubItems[3].Text) / Convert.ToDouble(listView3.FocusedItem.SubItems[2].Text) * 100) <= 84) { listView3.FocusedItem.SubItems[4].Text = "2 - Some requirements have been met"; listView3.FocusedItem.ForeColor = Color.Red; } else if (Math.Round(Convert.ToDouble(listView3.FocusedItem.SubItems[3].Text) / Convert.ToDouble(listView3.FocusedItem.SubItems[2].Text) * 100) <= 59) { listView3.FocusedItem.SubItems[4].Text = "1 - Requirements have not been met"; listView3.FocusedItem.ForeColor = Color.Red; } } date.Dispose(); if (listView3.FocusedItem.Index < listView3.Items.Count - 1) { listView3.Items[listView3.FocusedItem.Index + 1].Selected = true; listView3.Items[listView3.FocusedItem.Index + 1].Focused = true; } var requirements = true; foreach (ListViewItem theItem in listView3.Items) { if (theItem.ForeColor != Color.Blue) requirements = false; } if (!requirements) { label11.Text = "1 - Requirements have not been met"; label11.Visible = true; //button3.Enabled = false; } else { label11.Visible = false; button3.Enabled = true; } return; } Database.Command("UPDATE results SET result = '" + mark + "', serialNumber = '" + textBox2.Text + "'WHERE idEnrollments = '" + splitter[1] + "' and idCourseComponents = '" + listView3.SelectedItems[0].Text + "'"); if (Database.Count("results", "idEnrollments = '" + splitter[1] + "' and idCourseComponents = '" + listView3.SelectedItems[0].Text + "'") == 0) { string[] columns2 = {"idEnrollments", "idCourseComponents", "Result", "serialNumber"}; string[] values2 = { splitter[1], listView3.SelectedItems[0].Text, mark, textBox2.Text }; Database.Insert("results", columns2, values2); } listView3.FocusedItem.SubItems[3].Text = mark; if (listView3.FocusedItem.SubItems[7].Text == "PRAC" || listView3.FocusedItem.SubItems[7].Text == "ASS") { //try //{ // while (!clsFunctions.IsNumeric(select.Selection)) // { // MessageBox.Show("Please enter valid score."); // select.ShowDialog(); // } // while (!clsFunctions.IsNumeric(select.Selection) || // Convert.ToDouble(select.Selection) > Convert.ToDouble(listView3.FocusedItem.SubItems[2].Text)) // { // MessageBox.Show("Please enter valid score."); // select.ShowDialog(); // } //} //catch //{ // MessageBox.Show("Error in input format. Please try again."); // return; //} if (Convert.ToDouble(listView3.FocusedItem.SubItems[3].Text) >= 0.5 * Convert.ToDouble(listView3.FocusedItem.SubItems[2].Text)) { listView3.FocusedItem.SubItems[4].Text = "Competent"; listView3.FocusedItem.ForeColor = Color.Blue; } else { listView3.FocusedItem.SubItems[4].Text = "N.Y.C"; listView3.FocusedItem.ForeColor = Color.Red; } } else if (listView3.FocusedItem.SubItems[7].Text == "ASS") { if (Convert.ToDouble(listView3.FocusedItem.SubItems[3].Text) >= 0.5 * Convert.ToDouble(listView3.FocusedItem.SubItems[2].Text)) { listView3.FocusedItem.SubItems[4].Text = "Competent"; listView3.FocusedItem.ForeColor = Color.Blue; } else { listView3.FocusedItem.SubItems[4].Text = "N.Y.C"; listView3.FocusedItem.ForeColor = Color.Red; } } if (listView3.FocusedItem.Index < listView3.Items.Count - 1) { listView3.Items[listView3.FocusedItem.Index + 1].Selected = true; listView3.Items[listView3.FocusedItem.Index + 1].Focused = true; } var allBlue2 = true; foreach (ListViewItem item in listView3.Items) { if (item.ForeColor != Color.Blue) allBlue2 = false; } if (allBlue2) { cboExams.Enabled = true; cboExams.BackColor = Color.White; listView4.BackColor = Color.White; var exams = Database.Query("components", "myParent = '" + listView2.SelectedItems[0].Text + "' AND ComponentAbrev = 'EXV'"); cboExams.Items.Clear(); foreach (DataRow newRow in exams.Tables[0].Rows) { cboExams.Items.Add(Convert.ToString(newRow["myParent"]) + " - " + Convert.ToString(newRow["Name"])); } if (exams.Tables[0].Rows.Count > 0) cboExams.Text = (string)cboExams.Items[0]; label11.Visible = false; button3.Enabled = true; } else { cboExams.Enabled = false; cboExams.BackColor = Color.Lavender; cboExams.Items.Clear(); listView4.BackColor = Color.Lavender; label11.Text = "Not all practicals are competent"; label11.Visible = true; //button3.Enabled = false; } }
private void listView4_DoubleClick(object sender, EventArgs e) { var tempIndex = listView4.FocusedItem.Index; var tempID = cboExams.SelectedIndex; var splitter = clsFunctions.SplitByString(listView1.SelectedItems[0].SubItems[1].Text, "-"); if (checkBox2.Checked == false) { if (label10.Visible && !System.Text.RegularExpressions.Regex.IsMatch(listView4.FocusedItem.Text, ".*Resub.*") && !System.Text.RegularExpressions.Regex.IsMatch(listView4.FocusedItem.Text, ".*Pre.*")) return; if ((label11.Visible||label10.Visible) && (Database.Count("enrollmentcomponents","idCourseComponents = '" + listView2.SelectedItems[0].SubItems[0].Text + "' AND outcome = 'Competent' AND idEnrollments = " + splitter[1]) > 0)) return; if (System.Text.RegularExpressions.Regex.IsMatch(listView4.FocusedItem.Text, ".*Final.*") && listView4.FocusedItem.SubItems[2].Text == "") { foreach (ListViewItem item in listView4.Items) { if (System.Text.RegularExpressions.Regex.IsMatch(item.Text, ".*Final.*") && item.SubItems[2].Text != "") { MessageBox.Show("There is already a Final mark for this Short Course"); return; } } } if (System.Text.RegularExpressions.Regex.IsMatch(listView4.FocusedItem.Text, ".*Resub.*") && listView4.FocusedItem.SubItems[2].Text == "") { var found = false; foreach (ListViewItem item in listView4.Items) { if (item.SubItems[2].Text != "" && System.Text.RegularExpressions.Regex.IsMatch(item.Text, ".*Final.*")) found = true; } if (!found) { if (Database.Count("results r JOIN components c ON r.idCourseComponents = c.idCourseComponents", "idEnrollments = '" + splitter[1] + "' AND r.idCourseComponents LIKE '" + listView2.SelectedItems[0].SubItems[0].Text + "%' AND c.ComponentAbrev = 'EX'") > 0) { var myRow = Database.Row( "results r JOIN components c ON r.idCourseComponents = c.idCourseComponents", "idEnrollments = '" + splitter[1] + "' AND r.idCourseComponents LIKE '" + listView2.SelectedItems[0].SubItems[0].Text + "%' AND c.ComponentAbrev = 'EX'"); if(MessageBox.Show("Final Exam for this version was not found but results for exam " + myRow["idCourseComponents"] + " were found. Continue?","Results",MessageBoxButtons.YesNo) != DialogResult.Yes) return; } else { if (MessageBox.Show("Final Exam for this version was not found. Continue?","Results",MessageBoxButtons.YesNo) != DialogResult.Yes) return; } } } Database.Command("UPDATE enrollmentcomponents SET moderated = 'no' WHERE idEnrollments = '" + splitter[1] + "' AND idCourseComponents = '" + listView2.SelectedItems[0].Text + "'"); label10.Visible = false; double prac = 0.0; double outOf = 0.0; try { foreach (ListViewItem theItem in listView3.Items) { prac += Convert.ToDouble(theItem.SubItems[3].Text); outOf += Convert.ToDouble(theItem.SubItems[2].Text); } prac = (prac/outOf)*100; prac = Math.Round(prac, 2); } catch {} if (listView2.SelectedItems.Count == 0) return; var tmpFrm = new FrmExamResults(listView4.FocusedItem.SubItems[5].Text, splitter[1], listView2.SelectedItems[0].SubItems[1].Text, textBox2.Text,prac); tmpFrm.ShowDialog(); } else { var form = new frmSubmitText("Combined Score", "Please enter combined score:"); form.ShowDialog(); var date = new frmSubmitDate("Combined Score","Please enter date for assessment:" ); date.ShowDialog(); if (date.Selection.ToString("dd/MM/yyyy") == "01/01/0001") return; if (form.Selection == "***") return; if (Database.Count("results", "idCourseComponents = '" + listView4.FocusedItem.SubItems[5].Text + "' AND idEnrollments = '" + splitter[1] + "'") > 0) Database.Command("UPDATE results SET result = '" + form.Selection + "' , date = '" + date.Selection.ToString("yyyyMMdd") + "', capturedDate = '" + DateTime.Now.ToString("yyyyMMdd") + "' WHERE idCourseComponents = '" + listView4.FocusedItem.SubItems[5].Text + "' AND idEnrollments = '" + splitter[1] + "'"); else { Database.Command("INSERT INTO results(idCourseComponents,idEnrollments,result, date, capturedDate) VALUES('" + listView4.FocusedItem.SubItems[5].Text + "','" + splitter[1] + "','" + form.Selection + "','" +date.Selection.ToString("yyyyMMdd") + "','" + DateTime.Now.ToString("yyyyMMdd") + "')"); } } load_results(listView2.SelectedItems[0].Text, splitter[1]); try { cboExams.SelectedIndex = tempID; listView4.Items[tempIndex].Selected = true; listView4.Items[tempIndex].Focused = true; } catch { return; } }
private void FrmExamResults_Load(object sender, EventArgs e) { try { label2.Text = course + "\n" + Database.Retrieve("components", "idCourseComponents = '" + examID + "'", "Name"); } catch { } if (Database.Count("components c LEFT OUTER JOIN results r ON c.idCourseComponents = r.idCourseComponents", "myParent = '" + examID + "' AND idEnrollments = '" + enrollmentID + "'") > 0) { var divisions = Database.Query( "components c LEFT OUTER JOIN (SELECT * FROM results WHERE idEnrollments = " + enrollmentID + ") as r ON c.idCourseComponents = r.idCourseComponents", "myParent = '" + examID + "'"); foreach (DataRow theRow in divisions.Tables[0].Rows) { if (Convert.ToString(theRow["idEnrollments"]) != "" && Convert.ToString(theRow["idEnrollments"]) != enrollmentID) continue; var x1 = listView1.Items.Add(Convert.ToString(theRow["Name"])); try { x1.SubItems.Add(Convert.ToString(theRow["result"])); } catch { x1.SubItems.Add(""); } x1.SubItems.Add(Convert.ToString(theRow["totalMark"])); x1.SubItems.Add(""); x1.SubItems.Add(Convert.ToString(theRow["idCourseComponents"])); try { total += Convert.ToDouble(x1.SubItems[2].Text); } catch { } try { marks += Convert.ToDouble(x1.SubItems[1].Text); } catch { marks += 0; } if (x1.SubItems[1].Text.Length > 0) if (System.Text.RegularExpressions.Regex.IsMatch(label2.Text, ".*Typing.*") || System.Text.RegularExpressions.Regex.IsMatch(label2.Text, ".*ATS.*")) { if (System.Text.RegularExpressions.Regex.IsMatch(x1.Text, ".*words per minute.*")) x1.SubItems[3].Text = (Convert.ToDouble(x1.SubItems[2].Text) <= Convert.ToDouble(x1.SubItems[1].Text)) ? "Competent" : "N.Y.C"; if (System.Text.RegularExpressions.Regex.IsMatch(x1.Text, ".*Accuracy.*")) x1.SubItems[3].Text = 90 <= Convert.ToDouble(x1.SubItems[1].Text) ? "Competent" : "N.Y.C"; } else x1.SubItems[3].Text = Convert.ToDouble(x1.SubItems[1].Text) / Convert.ToDouble(x1.SubItems[2].Text) * 100 > 60 ? "Competent" : "N.Y.C"; } } else { var dateSelect = new frmSubmitDate("Please select exam date:", "Please select exam date:"); dateSelect.ShowDialog(); if (dateSelect.status == false) { Close(); return; } datDate.Value = dateSelect.Selection; var divisions = Database.Query("components", "myParent = '" + examID + "'"); foreach (DataRow theRow in divisions.Tables[0].Rows) { var x1 = listView1.Items.Add(Convert.ToString(theRow["Name"])); x1.SubItems.Add(""); x1.SubItems.Add(Convert.ToString(theRow["totalMark"])); x1.SubItems.Add(""); x1.SubItems.Add(Convert.ToString(theRow["idCourseComponents"])); if (IsDouble(x1.SubItems[2].Text)) total += Convert.ToDouble(x1.SubItems[2].Text); try { marks += Convert.ToDouble(x1.SubItems[1].Text); } catch { marks += 0; } } } perc = marks / total * 100; label3.Text = string.Format("Result: {0}/{1}({2}%)", marks, total, Math.Round(perc, 2)); if (System.Text.RegularExpressions.Regex.IsMatch(label2.Text, ".*Typing.*") || System.Text.RegularExpressions.Regex.IsMatch(label2.Text, ".*ATS.*")) { try { label3.Text = string.Format("Result: {0}-Acc/{1}-Wpm", listView1.Items[0].SubItems[1].Text, listView1.Items[1].SubItems[1].Text); } catch { } var competentbool = true; foreach (ListViewItem theItem in listView1.Items) { if (theItem.SubItems[3].Text != "Competent") competentbool = false; } competent = competentbool ? "Competent" : "N.Y.C."; try { label = listView1.Items[0].SubItems[1].Text + "-Acc/" + listView1.Items[1].SubItems[1].Text + "-Wpm"; } catch { } } if (Database.Count("results", "idCourseComponents = '" + examID + "' AND idEnrollments = '" + enrollmentID + "'") > 0) datDate.Value = Convert.ToDateTime(Database.Retrieve("results", "idCourseComponents = '" + examID + "' AND idEnrollments = '" + enrollmentID + "'", "captureddate")); }
private void subjectEnrollmentToolStripMenuItem_Click(object sender, EventArgs e) { //ArrayList list = new ArrayList(); //DataSet courses = Database.Query("courses", "Not Type = 'CC'"); //foreach (DataRow theRow in courses.Tables["courses"].Rows) //{ // list.Add(theRow["idCourses"] + " - " + theRow["Name"]); //} DataSet temp = Database.Query("enrollments", "idEnrollments = 0"); string[] columns = { "LearnerKey", "regDate", "numRegistrations", "dateOfFirstLesson" }; string[] values = new string[temp.Tables[0].Columns.Count - 3]; values[0] = Database.Retrieve("learner_details", "division = '" + GlobalProperties.loggedOnUserDivison + "' AND learnerNumber = '" + GlobalProperties.strLearnerNumber + "'", "LearnerKey"); var dateSelect = new frmSubmitDate("Please select registration date", "Please select registration date"); dateSelect.ShowDialog(); if (dateSelect.status == false) return; values[1] = dateSelect.Selection.ToString("yyyyMMdd"); values[2] = "1"; values[3] = DateTime.Now.ToString("yyyyMMdd"); Database.Insert("enrollments", columns, values); DataSet tempe = Database.Query("enrollments", "LearnerKey = '" + clsFunctions.GetLearnerKey(GlobalProperties.varLearner_id) + "' AND regDate = '" + dateSelect.Selection.ToString("yyyyMMdd") + "' ORDER BY idEnrollments DESC"); string[] coursescolumns = { "idcourses", "Name", "Status", "xPeriod", "Type" }; string[] coursesvalues = new string[5]; coursesvalues[0] = "ENR" + Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); coursesvalues[1] = "Custom Course Selection"; coursesvalues[2] = "1"; coursesvalues[3] = "0"; coursesvalues[4] = "CC"; Database.Insert("courses", coursescolumns, coursesvalues); //check if this course has electives //columns = new string[2]; //values = new string[2]; //values[0] = Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); //values[1] = arrSplit3[0]; //Database.Insert("enrollmentscourses", columns, values); //frmSelect.Dispose(); columns = new string[2]; values = new string[2]; columns[0] = "idEnrollments"; columns[1] = "idCourses"; values[0] = Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); values[1] = "ENR" + Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); Database.Insert("enrollmentscourses", columns, values); var colleges = new ArrayList(); var tempDS = Database.Query("Components", "(ComponentAbrev = 'PROJ' OR ComponentAbrev = 'SC') AND singleSubject = 1" + " ORDER BY Name"); foreach (DataRow theRow in tempDS.Tables["Components"].Rows) { colleges.Add(Convert.ToString(theRow["Name"]) + " - " + Convert.ToString(theRow["idCourseComponents"])); } var tempSelect = new frmSelectSingle("Replace Course", "Select Course", colleges); tempSelect.ShowDialog(); if (tempSelect.Selection == "***") return; string[] separate = { " - " }; string[] select = tempSelect.Selection.Split(separate, StringSplitOptions.None); DataSet Check = Database.Query("components", "idCourseComponents = '" + select[1] + "'"); string[] columns2 = { "idCourses", "idComponents" }; string[] values2 = { "ENR" + Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]), select[1] }; Database.Insert("coursecomponents", columns2, values2); if (Convert.ToString(Check.Tables[0].Rows[0]["isElective"]) == "1") { columns2 = new string[2]; columns2[0] = "idEnrollments"; columns2[1] = "idCourseComponents"; values2[0] = Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); values2[1] = select[1]; Database.Insert("enrollmentcomponents", columns, values); } clsFunctions.WriteLog("Created new enrollment for learner (" + GlobalProperties.strLearnerNumber + ") : Course " + select[1] + ": Enrollment (" + Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]) + ")\nEnrolled By:" + GlobalProperties.loggedOnUser); string subject = (cboEnrollments.Items.Count >= 1 ? "Additional Enrollment :" : "NewEnrollment :"); string message = "Created new enrollment for learner (" + GlobalProperties.strLearnerNumber + ") : Project/Short Course " + select[1] + ": Enrollment (" + Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]) + ")\nEnrolled By:" + GlobalProperties.loggedOnUser; clsFunctions.SendEmail("*****@*****.**", subject + GlobalProperties.LoggedOnUserDivision, message); DataSet payment = Database.Query("paymentinfo", "idenrollments = '0'"); columns = new string[12]; values = new string[12]; for (int i = 0; i < payment.Tables[0].Columns.Count; i++) { DataColumn theColumn = payment.Tables[0].Columns[i]; columns[i] = theColumn.ColumnName; } values[0] = "0"; values[1] = "0"; values[2] = "0"; values[3] = "0"; values[4] = "0"; values[5] = "0"; values[6] = "0"; values[7] = "0"; values[8] = "No"; values[9] = "No"; values[10] = "0"; values[11] = Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); Database.Insert("paymentinfo", columns, values); load_enrollmentstab(); cboEnrollments.SelectedIndex = cboEnrollments.Items.Count - 1; string[] arrEnrollmentID = new string[5]; arrEnrollmentID = SplitByString(cboEnrollments.Text, "-"); load_enrollments(arrEnrollmentID[1]); panel1.Visible = true; }
private void lstEnrollments_MouseDoubleClick(object sender, MouseEventArgs e) { var arrEnrollmentID = SplitByString(cboEnrollments.Text, "-"); var temp = new frmSubmitText("Serial Number", "Enter Serial number for this course:"); temp.ShowDialog(); if (temp.Selection == "***") return; if (Database.Count("enrollmentComponents", "serialnumber = '" + temp.Selection + "'") > 0) { MessageBox.Show("Serial number already exists.", "Error"); return; } var dateSelect = new frmSubmitDate("Submit Date", "Please submit date of issue."); if (dateSelect.status == false) return; dateSelect.ShowDialog(); if (Database.Count("enrollmentcomponents", "idCourseComponents = '" + lstEnrollments.FocusedItem.Text + "' AND idEnrollments = '" + arrEnrollmentID[1] + "'") == 0) { string[] columns = { "idCourseComponents", "idEnrollments", "serialNumber", "dateIssued" }; string[] values = { lstEnrollments.FocusedItem.Text, arrEnrollmentID[1], temp.Selection, dateSelect.Selection.ToString("yyyyMMdd") }; Database.Insert("enrollmentcomponents", columns, values); } else { var component = Database.Query("enrollmentcomponents", "idCourseComponents = '" + lstEnrollments.FocusedItem.Text + "' AND idEnrollments = '" + arrEnrollmentID[1] + "'"); component.Tables[0].Rows[0]["serialNumber"] = temp.Selection; component.Tables[0].Rows[0]["DateIssued"] = dateSelect.Selection; Database.Update(component, "enrollmentcomponents", "idCourseComponents = '" + lstEnrollments.FocusedItem.Text + "' AND idEnrollments = '" + arrEnrollmentID[1] + "'"); } load_enrollments(arrEnrollmentID[1]); foreach (ListViewItem item in lstEnrollments.Items) { if (item.SubItems[3].Text.Length == 0) return; } MessageBox.Show("This is the final book for this learner. Prepare for learners and drivers!", "Learners & Drivers Preparation"); }
private void assesmentReportToolStripMenuItem_Click(object sender, EventArgs e) { if (!cboEnrollments.Text.Contains("-")) { MessageBox.Show("Invalid Enrollment"); return; } var splitter = SplitByString(cboEnrollments.Text, "-"); var choice = DevExpress.XtraEditors.XtraMessageBox.Show("Do you want to view the learner's attendance as well?", "Attendance Logs", MessageBoxButtons.YesNo) == DialogResult.Yes; var data = Database.Query(@"SELECT ld.learnerKey, e.idCourseComponents, CONCAT(e.idCourseComponents,'%'), e.Name, d.result, x.result, if(x.CapturedDate,x.CapturedDate,d.resultDate) as CapturedDate, d.dateModerated, d.moderated, d.outcome, x.CapturedDate as resultDate, v.Name, a.idEnrollments FROM learner_details ld JOIN enrollments a on a.learnerKey = ld.learnerKey JOIN enrollmentscourses b on a.idEnrollments = b.idEnrollments LEFT JOIN ( SELECT e.idEnrollments, ld.learnerKey, c.Name FROM enrollmentsCourses ec JOIN courses c on c.idCourses = ec.idCourses JOIN enrollments e USING(idEnrollments) JOIN learner_details ld USING (LearnerKey) WHERE c.Type <> 'CC' AND e.idEnrollments = " + splitter[1] + @" ) as v ON v.idEnrollments = a.idEnrollments LEFT JOIN courses cor on cor.idCourses = b.idCourses JOIN coursecomponents c on c.idCourses = b.idCourses JOIN components e on e.idCourseComponents = c.idComponents LEFT OUTER JOIN enrollmentcomponents d on d.idEnrollments = a.idenrollments AND d.idCourseComponents = c.idComponents LEFT JOIN ( SELECT r.capturedDate, r.idEnrollments, r.idCourseComponents, (r.result/g.totalMark)*100 as result FROM results r JOIN components g on g.idCourseComponents = r.idCourseComponents JOIN enrollments e on e.idEnrollments = r.idEnrollments JOIN learner_details ld on ld.learnerKey = e.LearnerKey WHERE g.componentAbrev = 'EX' and (NOT g.Name LIKE '%Pre %') AND r.idEnrollments = " + splitter[1] + @" ORDER BY capturedDate DESC ) as x ON x.idEnrollments = a.idEnrollments AND x.idCourseComponents LIKE CONCAT(e.idCourseComponents,'%') WHERE a.idEnrollments = " + splitter[1] + @" AND ((NOT (e.isElective = '1' AND d.idEnrollments is null)) OR isElective IS NULL) AND (( NOT d.Status = 'Inactive') OR d.Status IS Null) GROUP BY a.idEnrollments, c.idCourseComponents ORDER BY ld.learnerNumber, a.idEnrollments, e.idCourseComponents"); var learners = Database.Table("SELECT *, CONCAT(Name,' ',Surname) as learnerName FROM learner_details WHERE ID = '" + GlobalProperties.varLearner_id + "'"); learners.TableName = "learners"; data.Tables.Add(learners); var relation2 = new DataRelation("two", data.Tables["learners"].Columns["learnerKey"], data.Tables["MyTable"].Columns["learnerKey"]); data.Relations.Add(relation2); var Report = new rptAssesmentReportNew(); if (choice) { var dateThing = new frmSubmitDate("Select Date for attendance.", "Attendance Date"); dateThing.ShowDialog(); if (!dateThing.status) return; var myTable = Database.Table(@"SELECT la.*, lb.*, ld.learnerKey, DATE(la.dateTime) as InDate, DATE(lb.dateTime) as outDate, TIME(SUBTIME(TIME(lb.dateTime),TIME(la.dateTime))) as Duration, d.divisionName FROM learnerActivity la JOIN learner_details ld on ld.learnerNumber = la.learnerNumber AND ld.division = la.division LEFT JOIN learnerActivity lb on lb.idLearnerActivity = la.partnerID JOIN division d on d.divisionAbrev = la.division WHERE DATE(la.`dateTime`) >= " + dateThing.Selection.ToString("yyyyMMdd") + " AND ld.ID = '" + GlobalProperties.varLearner_id + @"' AND la.`type` IN ('Attendance','Exam') AND la.details = 'IN' ORDER BY la.learnerNumber, la.dateTime"); data.Tables.Add(myTable); var relation = new DataRelation("one", new DataColumn[] { data.Tables["learners"].Columns["learnerKey"] }, new DataColumn[] { data.Tables["GeneratedTable"].Columns["learnerKey"] }, false); data.Relations.Add(relation); } else { Report.attendanceReport.Visible = false; } var college = Database.Row("SELECT * FROM division WHERE divisionAbrev = '" + GlobalProperties.loggedOnUserDivison + "'"); Report.streetText.Text = Convert.ToString(college["Street"]); Report.suburbText.Text = Convert.ToString(college["Subburb"]); Report.cityText.Text = Convert.ToString(college["City"]); Report.telText.Text = "Tel. " + Convert.ToString(college["Tel"]); Report.divisionLabel.Text = GlobalProperties.LoggedOnUserDivision; var theText = new frmSubmitText("Please enter comment", "Please enter comment for learner (Optional):", false); theText.ShowDialog(); if (theText.Selection == "***") return; Report.commentText.Rtf = Report.commentText.Rtf.Replace("##", theText.Selection); Report.DataSource = data; Report.ShowPreviewDialog(); }
private void courseEnrollmentToolStripMenuItem_Click(object sender, EventArgs e) { //ArrayList list = new ArrayList(); //DataSet courses = Database.Query("courses", "Not Type = 'CC'"); //foreach (DataRow theRow in courses.Tables["courses"].Rows) //{ // list.Add(theRow["idCourses"] + " - " + theRow["Name"]); //} var frmSelect = new frmCourseSelect("Select Course", "Course to Enroll In"); frmSelect.ShowDialog(); if (frmSelect.Selection == "***") return; string[] arrSplit = new string[5]; string[] splitter = { " - " }; arrSplit = SplitByString(frmSelect.Selection, " - "); DataSet temp = Database.Query("enrollments", "idEnrollments = 0"); string[] columns = new string[4]; string[] values = new string[4]; for (int i2 = 1; i2 <= 4; i2++) { DataColumn theColumn = temp.Tables[0].Columns[i2]; columns[i2 - 1] = theColumn.ColumnName; } values[0] = Database.Retrieve("learner_details", "division = '" + GlobalProperties.loggedOnUserDivison + "' AND learnerNumber = '" + GlobalProperties.strLearnerNumber + "'", "LearnerKey"); var dateSelect = new frmSubmitDate("Please select registration date", "Please select registration date"); dateSelect.ShowDialog(); if (dateSelect.status == false) return; values[1] = dateSelect.Selection.ToString("yyyyMMdd"); values[2] = "1"; values[3] = DateTime.Now.ToString("yyyyMMdd"); Database.Insert("enrollments", columns, values); DataSet tempe = Database.Query("enrollments", "LearnerKey = '" + clsFunctions.GetLearnerKey(GlobalProperties.varLearner_id) + "' AND regDate = '" + dateSelect.Selection.ToString("yyyyMMdd") + "' ORDER BY idEnrollments DESC"); DataSet haselectives = Database.Query("courses", "idCourses = '" + arrSplit[0] + "'"); if (haselectives.Tables[0].Rows.Count > 0) { if (Convert.ToString(haselectives.Tables[0].Rows[0]["hasElectives"]) != "0") { //Retrieve Elective Component Links DataSet electives = Database.Query("coursecomponents", "idCourses = '" + arrSplit[0] + "'"); //foreach elective ArrayList theList = new ArrayList(); foreach (DataRow theRow in electives.Tables[0].Rows) { DataSet row = Database.Query("components", "idCourseComponents = '" + theRow["idComponents"] + "'"); DataRow thetheRow = row.Tables[0].Rows[0]; if (Convert.ToString(thetheRow["isElective"]) == "1") theList.Add(Convert.ToString(thetheRow["idCourseComponents"]) + " - " + Convert.ToString(thetheRow["Name"])); } frmSelectMulti frmMultiSelect = null; try { frmMultiSelect = new frmSelectMulti("Select Electives", "Please Select Electives:", Convert.ToInt16( haselectives.Tables[0].Rows[0][ "hasElectives"]), Convert.ToInt16( haselectives.Tables[0].Rows[0][ "maxElectives"]), theList); } catch { MessageBox.Show( "An error has occured with this action. This is most likely due to no elective information for the course."); return; } if (frmMultiSelect != null) { frmMultiSelect.ShowDialog(); if (frmMultiSelect.Selection == null) return; foreach (object item in frmMultiSelect.Selection) { string[] arrSplit2 = new string[5]; arrSplit2 = SplitByString((string)item, " - "); string[] columns2 = { "idEnrollments", "idCourseComponents" }; string[] values2 = new string[2]; values2[0] = Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); values2[1] = arrSplit2[0]; Database.Insert("enrollmentcomponents", columns2, values2); } if (frmMultiSelect.Selection.Count == 0) return; } } } string[] arrSplit3 = new string[5]; arrSplit3 = SplitByString(frmSelect.Selection, " - "); string[] coursescolumns = { "idcourses", "Name", "Status", "xPeriod", "Type" }; string[] coursesvalues = new string[5]; coursesvalues[0] = "ENR" + Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); coursesvalues[1] = "Custom Course Selection"; coursesvalues[2] = "1"; coursesvalues[3] = "0"; coursesvalues[4] = "CC"; Database.Insert("courses", coursescolumns, coursesvalues); //check if this course has electives clsFunctions.WriteLog("Created new enrollment for learner (" + GlobalProperties.strLearnerNumber + ") : Course " + arrSplit[0] + ": Enrollment (" + Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]) + ")"); columns = new string[2]; columns[0] = "idEnrollments"; columns[1] = "idCourses"; values = new string[2]; values[0] = Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); values[1] = arrSplit3[0]; Database.Insert("enrollmentscourses", columns, values); frmSelect.Dispose(); values[0] = Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); values[1] = "ENR" + Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); string subject = (cboEnrollments.Items.Count >= 1 ? "Additional Enrollment :" : "NewEnrollment :"); Database.Insert("enrollmentscourses", columns, values); string message = GlobalProperties.LoggedOnUserDivision + " Learner " + GlobalProperties.strLearnerNumber + " has enrolled for " + arrSplit[1] + ".\nEnrolled By:" + GlobalProperties.loggedOnUser; clsFunctions.SendEmail("*****@*****.**", subject + GlobalProperties.LoggedOnUserDivision, message); DataSet payment = Database.Query("paymentinfo", "idenrollments = '0'"); columns = new string[12]; values = new string[12]; for (int i = 0; i < payment.Tables[0].Columns.Count; i++) { DataColumn theColumn = payment.Tables[0].Columns[i]; columns[i] = theColumn.ColumnName; } values[0] = ""; values[1] = ""; values[2] = ""; values[3] = ""; values[4] = ""; values[5] = ""; values[6] = ""; values[7] = ""; values[8] = "No"; values[9] = "No"; values[10] = ""; values[11] = Convert.ToString(tempe.Tables[0].Rows[0]["idEnrollments"]); Database.Insert("paymentinfo", columns, values); frmSelect.Dispose(); load_enrollmentstab(); cboEnrollments.SelectedIndex = cboEnrollments.Items.Count - 1; string[] arrEnrollmentID = new string[5]; arrEnrollmentID = SplitByString(cboEnrollments.Text, "-"); load_enrollments(arrEnrollmentID[1]); panel1.Visible = true; txtTotalContractPrice.Focus(); }