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 adjustAttemptNumberToolStripMenuItem_Click(object sender, EventArgs e)
{
var splitter = clsFunctions.SplitByString(listView1.SelectedItems[0].SubItems[1].Text, "-");
var submit = new frmSubmitText("Please enter attempt number", "Please enter attempt number");
submit.ShowDialog();
if (listView3.SelectedItems.Count != 1)
return;
while (!clsFunctions.IsNumeric(submit.Selection) || Convert.ToInt16(submit.Selection) > 3)
{
if (submit.Selection == "***")
return;
MessageBox.Show("Please enter a valid number.", "Invalid Number");
submit.ShowDialog();
}
Database.Command("UPDATE results SET Attempt = '" + submit.Selection + "' WHERE idEnrollments = '" + splitter[1] + "' AND idCourseComponents = '" + listView3.SelectedItems[0].Text + "'");
listView3.SelectedItems[0].SubItems[5].Text = submit.Selection;
}
private void barButtonItem26_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 reason = new frmSubmitText("Reason", "Please enter reason forwithdrawel");
reason.ShowDialog();
if (reason.Selection == "***")
return;
var Data = Database.Row("learner_details", "LearnerNumber = '" + number.Selection + "' AND Division = '" +
GlobalProperties.loggedOnUserDivison + "'");
var report = new rptWithdrawn();
report.DataDefinition.FormulaFields["college"].Text = "\"" + GlobalProperties.LoggedOnUserDivision + "\"";
report.DataDefinition.FormulaFields["clerk"].Text = "\"" + GlobalProperties.loggedOnUser + "\"";
report.DataDefinition.FormulaFields["Name"].Text = "\"" + Data["Name"] + " " + Data["Surname"] + "\"";
report.DataDefinition.FormulaFields["learnerNumber"].Text = "\"" + number.Selection + "\"";
report.DataDefinition.FormulaFields["Reason"].Text = "\"" + reason.Selection + "\"";
var frmReport = new frmReport(report) { Text = "Withdrawel Request" };
frmReport.ShowDialog();
}
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 barButtonItem18_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();
while (Database.Count("learner_details", "LearnerNumber = '" + number.Selection + "' AND Division = '" +
GlobalProperties.loggedOnUserDivison + "'") == 0)
{
if (number.Selection == "***" || reason.Selection == "***")
return;
MessageBox.Show("Invalid Learner Number", "Error");
number.ShowDialog();
}
var report = new rptCancellation();
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 = "\"" + GlobalProperties.LoggedOnUserDivision + "\"";
report.DataDefinition.FormulaFields["principle"].Text = "\"" + GlobalProperties.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"]) + "\"";
//report.DataDefinition.FormulaFields["course"].Text = "\"" + Convert.ToString(data["Name1"]) + "\"";
//logs.WriteXml(@"C:\tmpXml.xml");
var frmReport = new frmReport(report) { Text = "Cancellation Request" };
frmReport.ShowDialog();
}
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 learnerRefundButton_ItemClick(object sender, ItemClickEventArgs e)
{
var number = new frmSubmitText("Learner Number", "Please enter learner number");
number.ShowDialog();
DataRow learner;
try
{
learner = Database.Row("SELECT * FROM learner_details WHERE learnerNumber = '" + number.Selection +
"' AND division = '" + GlobalProperties.loggedOnUserDivison + "'");
}
catch
{
MessageBox.Show("Invalid learner number.");
return;
}
var reason = new frmSubmitText("Reason", "Please enter reason for refund");
reason.ShowDialog();
var amount = new frmSubmitText("Amount", "Please enter amount for refund");
amount.ShowDialog();
double amountVal;
while (!Double.TryParse(amount.Selection,out amountVal) && !(amount.Selection=="***"))
{
MessageBox.Show("Invalid Amount");
amount.ShowDialog();
}
var bankName = new frmSubmitText("Bank Name", "Please enter bank name");
bankName.ShowDialog();
var accountName = new frmSubmitText("Account Name", "Please enter name of account holder");
accountName.ShowDialog();
var accountNumber = new frmSubmitText("Account Number", "Please enter account number");
accountNumber.ShowDialog();
var branchCodeName = new frmSubmitText("Branch Name/Number", "Please enter branch Name/Number");
branchCodeName.ShowDialog();
if (number.Selection == "***" || reason.Selection == "***" || amount.Selection == "***"
|| accountName.Selection == "***" || accountNumber.Selection == "***"
|| branchCodeName.Selection == "***" || bankName.Selection == "***") return;
var report = new Reports.rptRequestLearnerRefund();
report.collegeCell.Text = GlobalProperties.LoggedOnUserDivision;
report.dateCell.Text = DateTime.Now.ToString("dd/MM/yyyy");
report.learnerNumCell.Text = number.Selection;
report.nameCell.Text = Convert.ToString(learner["Name"]) + " " + Convert.ToString(learner["Surname"]);
report.respPartyCell.Text = GlobalProperties.loggedOnUser;
report.refundAmountCell.Text = "R" + Math.Round(amountVal, 2).ToString("#.00");
report.accountCell.Text = accountNumber.Selection;
report.branchCodeCell.Text = branchCodeName.Selection;
report.bankNameCell.Text = bankName.Selection;
report.accountNameCell.Text = accountName.Selection;
report.reasonLabel.Text = reason.Selection;
report.ShowPreviewDialog();
}
private void barButtonItem7_ItemClick_1(object sender, ItemClickEventArgs e)
{
int Year;
var myForm = new frmSubmitText("Sumit Year", "Submit the year for the report", true);
myForm.ShowDialog();
if (myForm.Selection == "***")
return;
Year = Convert.ToInt32(clsFunctions.scrubNumber(myForm.Selection));
var data = Database.Query(@"SELECT ec.idEnrollments, ld.learnerNumber, CONCAT(ld.Name,' ',ld.Surname) as Name,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments AND MONTH(paymentDate) = 1 AND
year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As Jan,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 2 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As Feb,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 3 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As March,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 4 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As April,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 5 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As May,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 6 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As June,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 7 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As July,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 8 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As Aug,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 9 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As Sept,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 10 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As Oct,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 11 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As Nov,
(SELECT SUM(amount) FROM ecdpayments f WHERE f.idEnrollments = ec.idEnrollments
AND MONTH(paymentDate) = 12 AND year(paymentDate) = " + Year + @" GROUP BY idEnrollments) As `Dec`,
ei.Name, ei.ID
FROM ecdPayments ec RIGHT JOIN Enrollments ee ON ee.idEnrollments = ec.idEnrollments
JOIN learner_Details ld on ld.learnerKEy = ee.LearnerKey
LEFT JOIN ecdInstructors ei on ei.idEcdInstructors = ee.ecdInstructor
WHERE ld.division = '" + GlobalProperties.loggedOnUserDivison + @"'
GROUP BY ld.learnerKEy
ORDER BY ld.Surname");
data.WriteXml("C:\\LAD_DATA_DUMP\\ecdSummary.xml", XmlWriteMode.WriteSchema);
var result = MessageBox.Show("Would you like to group the payments by facilitator?", "Grouping", MessageBoxButtons.YesNo);
var report = new rptECDSummary();
if (result == DialogResult.No)
{
report.GroupHeader1.GroupFields.Clear();
report.GroupHeader1.Visible = false;
report.xrTable3.Visible = true;
}
else
{
report.xrTable3.Visible = false;
}
report.titleLabel.Text = "ECD Payments Summary for " + GlobalProperties.LoggedOnUserDivision;
report.DataSource = data;
report.ShowPreviewDialog();
}
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 listView1_MouseDoubleClick(object sender, MouseEventArgs e)
{
if (listView1.SelectedItems.Count == 0)
{
return;
}
var tmpFrm = new frmSubmitText("Please enter score:", "Enter Score");
tmpFrm.ShowDialog();
if (tmpFrm.Selection == "***")
return;
while (!clsFunctions.IsNumeric(tmpFrm.Selection) || (clsFunctions.IsNumeric(listView1.FocusedItem.SubItems[2].Text) &&
Convert.ToDouble(tmpFrm.Selection) > Convert.ToDouble(listView1.FocusedItem.SubItems[2].Text)))
{
if (!clsFunctions.IsNumeric(tmpFrm.Selection))
{
MessageBox.Show("Please enter valid number.");
tmpFrm.ShowDialog();
}
if (System.Text.RegularExpressions.Regex.IsMatch(listView1.SelectedItems[0].Text, ".*WPM.*")) break;
if (Convert.ToDouble(tmpFrm.Selection) <= Convert.ToDouble(listView1.FocusedItem.SubItems[2].Text)) continue;
MessageBox.Show("Please enter valid number.");
tmpFrm.ShowDialog();
}
listView1.FocusedItem.SubItems[1].Text = tmpFrm.Selection;
if
(Database.Count("results", "idCourseComponents = '" +
listView1.FocusedItem.SubItems[4].Text + "' AND idEnrollments = '" +
enrollmentID + "'") > 0)
{
Database.Command("UPDATE results SET result = '" + tmpFrm.Selection +
"', capturedDate = '" + DateTime.Now.ToString("yyyyMMdd") + "', date = '" + datDate.Value.ToString("yyyyMMdd") + "', serialNumber = '" + serialNumber + "' WHERE idCourseComponents = '" +
listView1.FocusedItem.SubItems[4].Text + "' AND idEnrollments = '" + enrollmentID +
"'");
}
else
{
Database.Command("INSERT INTO results(idCourseComponents,idEnrollments,result, capturedDate, date, serialNumber) VALUES('" +
listView1.FocusedItem.SubItems[4].Text + "','" + enrollmentID + "','" +
tmpFrm.Selection + "','" + DateTime.Now.ToString("yyyyMMdd") + "','" + datDate.Value.ToString("yyyyMMdd") + "','" + serialNumber + "')");
}
if (System.Text.RegularExpressions.Regex.IsMatch(label2.Text, ".*Typing.*") ||
System.Text.RegularExpressions.Regex.IsMatch(label2.Text, ".*ATS.*"))
{
if (System.Text.RegularExpressions.Regex.IsMatch(listView1.FocusedItem.Text, ".*words per minute.*"))
listView1.FocusedItem.SubItems[3].Text =
(Convert.ToDouble(listView1.FocusedItem.SubItems[2].Text) <=
Convert.ToDouble(listView1.FocusedItem.SubItems[1].Text))
? "Competent"
: "N.Y.C";
if (System.Text.RegularExpressions.Regex.IsMatch(listView1.FocusedItem.Text, ".*Accuracy.*"))
listView1.FocusedItem.SubItems[3].Text = 90 <=
Convert.ToDouble(listView1.FocusedItem.SubItems[1].Text)
? "Competent"
: "N.Y.C";
}
else
listView1.FocusedItem.SubItems[3].Text = Convert.ToDouble(listView1.FocusedItem.SubItems[1].Text) / Convert.ToDouble(listView1.FocusedItem.SubItems[2].Text) * 100 >= 50 ? "Competent" : "N.Y.C.";
marks = 0;
total = 0;
foreach (ListViewItem theItem in listView1.Items)
{
if (IsDouble(theItem.SubItems[2].Text))
total += Convert.ToDouble(theItem.SubItems[2].Text);
try
{
marks += Convert.ToDouble(theItem.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.*"))
{
label3.Text = string.Format("Result: {0}-Acc/{1}-Wpm", listView1.Items[0].SubItems[1].Text,
listView1.Items[1].SubItems[1].Text);
label = listView1.Items[0].SubItems[1].Text + "-Acc/" + listView1.Items[1].SubItems[1].Text + "-Wpm";
}
if (listView1.FocusedItem.Index >= listView1.Items.Count - 1) return;
listView1.Items[listView1.FocusedItem.Index + 1].Selected = true;
listView1.Items[listView1.FocusedItem.Index + 1].Focused = true;
}
private void button7_Click(object sender, EventArgs e)
{
var submit = new frmSubmitText("Enter Name:", "Enter Group Name:");
submit.ShowDialog();
if (submit.Selection == "***")
return;
Database.Command("INSERT INTO divisionGroups(Name) VALUES('" + submit.Selection + "')");
loadGroups();
}
private void barButtonItem27_ItemClick(object sender, ItemClickEventArgs e)
{
var select = new frmSubmitText("Serial Number search", "Serial Number Search:\r\nPlease enter serial number");
select.ShowDialog();
if (select.Selection == "***")
{
return;
}
try
{
var row = Database.Row("enrollmentcomponents a, enrollments b, learner_details c, components d, division e",
"a.idEnrollments = b.idEnrollments AND b.LearnerKey = c.LearnerKey and serialNumber = '" +
select.Selection + "' AND a.idCourseComponents = d.idCourseComponents AND e.DivisionAbrev = division");
MessageBox.Show("Serial number is booked out for " + Convert.ToString(row["Name1"]) + "\r\nCollege: " + Convert.ToString(row["division"]) +
"\r\nLearner Number: " + Convert.ToString(row["LearnerNumber"]) + "\r\nPlease Contact " + Convert.ToString(row["Tel"]), "Serial Number");
}
catch
{
MessageBox.Show("Serial number not in database", "Serial Number Not Found");
return;
}
select.Dispose();
}
private void deleteThisComponentAndAllSubComponentsToolStripMenuItem_Click(object sender, EventArgs e)
{
var splitter = SplitByString(trvCourses.SelectedNode.Text, " - ");
Database.Delete("coursecomponents", "idCourses = '" + splitter[0] + "'");
Database.Delete("courses", "idCourses = '" + splitter[0] + "'");
//Database.Delete("results","idCourseComponents");
try
{
Database.Delete("components",
"myParent = '" + splitter[0] + "' AND NOT myParent = idCourseComponents");
Database.Delete("coursecomponents", "idComponents = '" + splitter[0] + "'");
Database.Delete("components", "idCourseComponents = '" + splitter[0] + "'");
}
catch (MySqlException)
{
var Submit = new frmSubmitText("Warning","Warning, this component has marks stored for learners. Cannot delete this course without validation code!");
Submit.ShowDialog();
if (Submit.Selection != "***" && Submit.Selection == "7789")
{
Database.Delete("results r, components c", "c.idCourseComponents = r.idCourseComponents AND myParent = '" + splitter[0] + "'");
Database.Delete("coursecomponents", "idComponents = '" + splitter[0] + "'");
Database.Delete("components", "idCourseComponents = '" + splitter[0] + "'");
Database.Delete("components",
"myParent = '" + splitter[0] + "' AND NOT myParent = idCourseComponents");
}
Submit.Close();
}
var tempNode = trvCourses.SelectedNode;
trvCourses.SelectedNode = trvCourses.SelectedNode.Parent;
tempNode.Remove();
var spliting = SplitByString(splitter[0], "-");
if (spliting.Length < 2)
{
load_items();
}
}
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 cancellationReplyButton_ItemClick(object sender, ItemClickEventArgs e)
{
var theForm = new frmSubmitText("Submit Learner Number", "Submit Learner Number");
theForm.ShowDialog();
if (theForm.Selection == "***")
return;
if (Database.Count("learner_details", "learnerNumber = '" + theForm.Selection + "' AND division = '" +
GlobalProperties.loggedOnUserDivison + "'") == 0)
{
MessageBox.Show("Learner Not Found.");
return;
}
var theSecondForm = new frmCancellationReply();
theSecondForm.ShowDialog();
if (theSecondForm.status == false)
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 rptCancellationReply();
report.detailLabel.Text = theSecondForm.theStuff;
report.DataSource = data;
report.ShowPreviewDialog();
}
private void barButtonItem21_ItemClick(object sender, ItemClickEventArgs e)
{
var number = new frmSubmitText("Please enter Learner Number:", "Please enter Learner Number:");
number.ShowDialog();
var learner = new frmSubmitText("Please enter amount:", "Please enter amount");
learner.ShowDialog();
if (learner.Selection == "***" || number.Selection == "***")
return;
DataRow data;
try
{
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");
}
catch
{
MessageBox.Show("Error Finding Student.");
return;
}
var report = new rptSny();
report.DataDefinition.FormulaFields["college"].Text = "\"" + GlobalProperties.LoggedOnUserDivision + "\"";
report.DataDefinition.FormulaFields["ClientNr"].Text = "\"" + Database.Retrieve("division", "DivisionAbrev = '" +
GlobalProperties.loggedOnUserDivison + "'", "SnyVen") + "\"";
report.DataDefinition.FormulaFields["RespSurname"].Text = "\"" + data["RespPartySurname"] + "\"";
report.DataDefinition.FormulaFields["Employer"].Text = "\"" + data["RespPartyEmployer"] + "\"";
report.DataDefinition.FormulaFields["RespName"].Text = "\"" + data["RespPartyName"] + "\"";
report.DataDefinition.FormulaFields["Title"].Text = "\"" + data["RespPartyTitle"] + "\"";
report.DataDefinition.FormulaFields["HandOverDate"].Text = "\"" + DateTime.Now.ToLongDateString() + "\"";
report.DataDefinition.FormulaFields["Amount"].Text = "\"" + learner.Selection + "\"";
report.DataDefinition.FormulaFields["RespID"].Text = "\"" + data["RespPartyID"] + "\"";
report.DataDefinition.FormulaFields["Name"].Text = "\"" + data["Name"] + "\"";
report.DataDefinition.FormulaFields["Surname"].Text = "\"" + data["Surname"] + "\"";
report.DataDefinition.FormulaFields["ID"].Text = "\"" + data["ID"] + "\"";
report.DataDefinition.FormulaFields["LearnerNr"].Text = "\"" + number.Selection + "\"";
try
{
report.DataDefinition.FormulaFields["startDate"].Text = "\"" + Convert.ToDateTime(data["regDate1"]).ToString("dd/MM/yyyy") + "\"";
}
catch
{
MessageBox.Show("This learner does not have a valid registration date. Please correct this!");
return;
}
report.DataDefinition.FormulaFields["RespPartyAdd"].Text = "\"" + data["respPartyStreet"] + "\"+Chr(13)+\" " +
data["RespPartySubburb"] + "\"+Chr(13)+\" " + data["RespPartyCity"] + "\"+Chr(13)+\" " +
data["RespPartyCountry"] + "\"+Chr(13)+\" " + data["RespPartyCode"] + "\"";
report.DataDefinition.FormulaFields["Add"].Text = "\"" + data["Street"] + "\"+Chr(13)+\" " +
data["Subburb"] + "\"+Chr(13)+\" " + data["City"] + "\"+Chr(13)+\" " +
data["Country"] + "\"+Chr(13)+\" " + data["postCode"] + "\"";
report.DataDefinition.FormulaFields["PostalAdd"].Text = "\"" + data["PostalStreet"] + "\"+Chr(13)+\" " +
data["PostalSubburb"] + "\"+Chr(13)+\" " + data["PostalCity"] + "\"+Chr(13)+\" " +
data["PostalCountry"] + "\"+Chr(13)+\" " + data["PostalCode"] + "\"";
report.DataDefinition.FormulaFields["RespTelWork"].Text = "\"" + data["RespPartyTel2"] + "\"";
report.DataDefinition.FormulaFields["RespTel"].Text = "\"" + data["RespPartyTel1"] + "\"";
report.DataDefinition.FormulaFields["RespCell"].Text = "\"" + data["RespPartyCell"] + "\"";
report.DataDefinition.FormulaFields["TelWork"].Text = "\"" + data["Tel1"] + "\"";
report.DataDefinition.FormulaFields["Tel"].Text = "\"" + data["Tel2"] + "\"";
report.DataDefinition.FormulaFields["Cell"].Text = "\"" + data["Cell"] + "\"";
var frmReport = new frmReport(report) { Text = "Hand Over Request" };
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 barButtonItem22_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 reasonDe = new frmSubmitText("Learner Number", "Please enter reason for deactivation");
reasonDe.ShowDialog();
var reasonRe = new frmSubmitText("Learner Number", "Please enter reason for reactivation");
reasonRe.ShowDialog();
var report = new rptReactivation();
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");
report.DataDefinition.FormulaFields["date"].Text = "\"" + DateTime.Now.ToLongDateString() + "\"";
report.DataDefinition.FormulaFields["college"].Text = "\"" + GlobalProperties.LoggedOnUserDivision + "\"";
report.DataDefinition.FormulaFields["learnerNumber"].Text = "\"" + number.Selection + "\"";
report.DataDefinition.FormulaFields["principle"].Text = "\"" + GlobalProperties.DivisionPrinciple + "\"";
report.DataDefinition.FormulaFields["learnerName"].Text = "\"" + Convert.ToString(data["Name"]) + " " +
Convert.ToString(data["Surname"]) + "\"";
report.DataDefinition.FormulaFields["regDate"].Text = "\"" + (Convert.ToDateTime(data["regDate1"])).ToLongDateString() + "\"";
report.DataDefinition.FormulaFields["reasonDe"].Text = "\"" + reasonDe.Selection + "\"";
report.DataDefinition.FormulaFields["reasonRe"].Text = "\"" + reasonRe.Selection + "\"";
//report.DataDefinition.FormulaFields["course"].Text = "\"" + Convert.ToString(data["Name1"]) + "\"";
//logs.WriteXml(@"C:\tmpXml.xml");
var frmReport = new frmReport(report) { Text = "Cancellation Request" };
frmReport.ShowDialog();
}
private void ribbon_ItemClick_1(object sender, ItemClickEventArgs e)
{
if ((Control.ModifierKeys & Keys.Control) == Keys.Control && clsFunctions.CheckRights("12"))
{
frmSubmitText titleForm = new frmSubmitText();
titleForm.ShowDialog();
frmSubmitParagraph theForm = new frmSubmitParagraph("Tooltip", "Tooltip Text");
theForm.ShowDialog();
if (theForm.Selection == "***" && titleForm.Selection == "***") return;
if (Database.Count("SELECT COUNT(*) FROM tooltips WHERE buttonName = '" + e.Item.Name + "'") > 0)
{
Database.Command("UPDATE tooltips SET tooltip= '" + theForm.Selection + "', tooltipTitle = '" + titleForm.Selection +
"' WHERE buttonName = '" + e.Item.Name + "'");
}
else
{
Database.Command("INSERT INTO tooltips(buttonName,toolTip,toolTipTitle) VALUES('" + e.Item.Name + "','" + theForm.Selection + "','" +
titleForm.Selection + "')");
}
recursiveToolTip(ribbon.Items);
return;
}
}
private void barButtonItem23_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 response = InputDialog.Show("Contact Details", "Enter learner contact number:");
if (response.Result != DialogResult.OK)
{
return;
}
var divisions = Database.Retrieve("division");
var theList = new ArrayList();
foreach (DataRow theRow in divisions.Tables[0].Rows)
{
theList.Add(Convert.ToString(theRow["DivisionName"]));
}
var select = new frmSelectSingle("Division", "Please select new UCO branch", theList);
select.ShowDialog();
//var report = new rptTransfer();
DataSet data;
try
{
data =
Database.Query(@"SELECT *
FROM learner_details a LEFT JOIN enrollments b on b.LEarnerKEy = a.LearnerKey
LEFT JOIN enrollmentscourses c on c.idEnrollments = b.idEnrollments
LEFT JOIN courses d on d.idCourses = c.idCourses
LEFT JOIN paymentinfo e ON e.idEnrollments = b.idEnrollments
WHERE a.Division = '" + GlobalProperties.loggedOnUserDivison + "' AND a.LearnerNumber = '" +
number.Selection + @"'
AND (d.Type <> 'CC' OR d.Type IS NULL) LIMIT 1");
//data.WriteXml(@"C:\LAD_DATA_DUMP\transfer.xml",XmlWriteMode.WriteSchema);
}
catch
{
MessageBox.Show("There is an error with this learner's enrollment. Please correct before trying to redo the transaction.");
return;
}
var report = new rptTransfer();
report.transferLable.Text = select.Selection;
report.responsibleClarkLabel.Text = GlobalProperties.loggedOnUser;
report.contactNumberLabel.Text = response.Submission;
report.divisionLabel.Text = GlobalProperties.LoggedOnUserDivision;
//report.DataDefinition.FormulaFields["learnerNumber"].Text = "\"" + number.Selection + "\"";
//report.DataDefinition.FormulaFields["transferBranch"].Text = "\"" + select.Selection + "\"";
//report.DataDefinition.FormulaFields["learnerName"].Text = "\"" + Convert.ToString(data["Name"]) + " " +
// Convert.ToString(data["Surname"]) + "\"";
//report.DataDefinition.FormulaFields["learnerTel"].Text = "\"" + response.Submission + "\"";
//report.DataDefinition.FormulaFields["Date"].Text = "\"" + DateTime.Now.ToString("dd/MM/yyyy") + "\"";
//report.DataDefinition.FormulaFields["Branch"].Text = "\"" + clsGlobal.LoggedOnUserDivision + "\"";
//report.DataDefinition.FormulaFields["clerk"].Text = "\"" + clsGlobal.LoggedOnUser + "\"";
//report.DataDefinition.FormulaFields["course"].Text = "\"" + Convert.ToString(data["Name1"]) + "\"";
//report.DataDefinition.FormulaFields["coursePrice"].Text = "\"" + Convert.ToString(data["totalContractPrice"]) + "\"";
//report.DataDefinition.FormulaFields["course"].Text = "\"" + Convert.ToString(data["Name1"]) + "\"";
//logs.WriteXml(@"C:\tmpXml.xml");
report.DataSource = data;
report.ShowPreviewDialog();
}
private void barButtonItem17_ItemClick(object sender, ItemClickEventArgs e)
{
var learner = new frmSubmitText("Please enter learner number:", "Please enter learner number:");
learner.ShowDialog();
if (learner.Selection == "***")
return;
if (Database.Count("learner_details", "division = '" + GlobalProperties.loggedOnUserDivison +
"' AND learnerNumber = '" + learner.Selection + "'") == 0)
{
MessageBox.Show("Invalid student number");
}
clsFunctions.WriteLog("Generated Testimonial for learner: " + learner.Selection);
var theForm = new frmTestimonial(learner.Selection);
theForm.ShowDialog();
}
private void barButtonItem25_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();
}
string reactivate =
MessageBox.Show("Would you like to request a re-activation?", "Re-Activation Request",
MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes
? "Yes"
: "No";
var Data = Database.Row("learner_details", "LearnerNumber = '" + number.Selection + "' AND Division = '" +
GlobalProperties.loggedOnUserDivison + "'");
var report = new rptEnquiry();
report.DataDefinition.FormulaFields["college"].Text = "\"" + GlobalProperties.LoggedOnUserDivision + "\"";
report.DataDefinition.FormulaFields["clerk"].Text = "\"" + GlobalProperties.loggedOnUser + "\"";
report.DataDefinition.FormulaFields["learnerName"].Text = "\"" + Data["Name"] + " " + Data["Surname"] + "\"";
report.DataDefinition.FormulaFields["learnerNumber"].Text = "\"" + number.Selection + "\"";
report.DataDefinition.FormulaFields["reactivate"].Text = "\"" + reactivate + "\"";
var frmReport = new frmReport(report) { Text = "Enquiry on Handover / Re-Activation" };
frmReport.ShowDialog();
}
private void barButtonItem19_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 reason = new frmSubmitText("Reason", "Please enter reason for course change");
reason.ShowDialog();
var list = new ArrayList();
var courses = Database.Query("courses", "Not Type = 'CC'");
foreach (DataRow theRow in courses.Tables["courses"].Rows)
{
list.Add(theRow["Name"]);
}
var frmSelect = new frmSelectSingle("Select Course", "New Course to Enroll In", list);
frmSelect.ShowDialog();
if (frmSelect.Selection == "***" || reason.Selection == "***")
return;
var report = new rptCourseChange();
var data =
Database.Row(@"SELECT *
FROM learner_details a JOIN enrollments b ON b.LearnerKey = a.LearnerKey
LEFT JOIN enrollmentscourses c ON c.idEnrollments = b.idEnrollments
LEFT JOIN courses d ON d.idCourses = c.idCourses
WHERE a.Division = '" + GlobalProperties.loggedOnUserDivison + "' AND a.LearnerNumber = '" +
number.Selection + "' AND d.Type <> 'CC'");
if (data == null)
{
data =
Database.Row(@"SELECT *
FROM learner_details a JOIN enrollments b ON b.LearnerKey = a.LearnerKey
LEFT JOIN enrollmentscourses c ON c.idEnrollments = b.idEnrollments
LEFT JOIN courses d ON d.idCourses = c.idCourses
WHERE a.Division = '" + GlobalProperties.loggedOnUserDivison + "' AND a.LearnerNumber = '" +
number.Selection + "'");
}
report.DataDefinition.FormulaFields["date"].Text = "\"" + DateTime.Now.ToLongDateString() + "\"";
report.DataDefinition.FormulaFields["college"].Text = "\"" + GlobalProperties.LoggedOnUserDivision + "\"";
report.DataDefinition.FormulaFields["principle"].Text = "\"" + GlobalProperties.DivisionPrinciple + "\"";
report.DataDefinition.FormulaFields["learnerNumber"].Text = "\"" + number.Selection + "\"";
report.DataDefinition.FormulaFields["reason"].Text = "\"" + reason.Selection + "\"";
report.DataDefinition.FormulaFields["currentProgramme"].Text = "\"" + Convert.ToString(data["Name1"]) + "\"";
report.DataDefinition.FormulaFields["learnerName"].Text = "\"" + Convert.ToString(data["Name"]) + " " +
Convert.ToString(data["Surname"]) + "\"";
report.DataDefinition.FormulaFields["newProgram"].Text = "\"" + frmSelect.Selection + "\"";
//report.DataDefinition.FormulaFields["course"].Text = "\"" + Convert.ToString(data["Name1"]) + "\"";
//logs.WriteXml(@"C:\tmpXml.xml");
var frmReport = new frmReport(report) { Text = "Cancellation Request" };
frmReport.ShowDialog();
}
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();
}