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