/**
*
* This function is used to perform the actual linking in the database and then refresh the table layout
*
**/
private void btnLink_Click(object sender, EventArgs e)
{
// Check if the dropdows were selected
if (cmbPrograms.SelectedIndex < 0 || cmbLink.SelectedIndex < 0)
{
// Display error message
MessageBox.Show("Oops... Looks like a Program or " + linkSection + " selection has not been made. Please make a selection and try linking again.", "Selection not Made", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
// Store the program into a variable
Programs program = (Programs)cmbPrograms.SelectedItem;
// Switch based on the linkSection
switch (linkSection)
{
// Perform action when case is Course
case "Course":
// Create a variable to hold the Course
Course course = (Course)cmbLink.SelectedItem;
// Add the link into the database
using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.StudentManagerDBConnectionString)) {
// Open the DB connection
connection.Open();
// Set the SqlCommand
SqlCommand command;
// Check if the link exists
using (command = new SqlCommand("SELECT COUNT(*) FROM CourseProgram WHERE courseId = @courseId AND programId = @programId", connection)) {
command.Parameters.AddWithValue("@courseId", course.id);
command.Parameters.AddWithValue("@programId", program.id);
Int32 count = (Int32)command.ExecuteScalar();
// Check if the link exists
if (count > 0)
{
// Display Error Message
MessageBox.Show("Oops... Look like that Program to " + linkSection + " link already exists. Please create a new link and try again!", "Link Exists", MessageBoxButtons.OK, MessageBoxIcon.Error);
// Close the DB connection
connection.Close();
}
else
{
// Add the Link
using (command = new SqlCommand("INSERT INTO CourseProgram (courseId, programId) VALUES (@courseId, @programId)", connection)) {
command.Parameters.AddWithValue("@courseId", course.id);
command.Parameters.AddWithValue("@programId", program.id);
command.ExecuteNonQuery();
// Add the data to the table
tlpLinks.RowCount++;
tlpLinks.RowStyles.Add(new RowStyle(SizeType.AutoSize));
tlpLinks.Controls.Add(new TextBox()
{
Text = course.name, ReadOnly = true, Dock = DockStyle.Fill
}, 0, (tlpLinks.RowCount - 1));
tlpLinks.Controls.Add(new Label()
{
Text = "=>", TextAlign = ContentAlignment.MiddleCenter
}, 1, (tlpLinks.RowCount - 1));
tlpLinks.Controls.Add(new TextBox()
{
Text = program.name, ReadOnly = true, Dock = DockStyle.Fill
}, 2, (tlpLinks.RowCount - 1));
// Display Success Message
MessageBox.Show("Success! Link successfully made!", "Link Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmbPrograms.SelectedIndex = -1;
cmbLink.SelectedIndex = -1;
// Close the DB connection
connection.Close();
}
}
}
}
break;
// Perform action when case is Student
case "Student":
// Create a variable to hold the Student
Student student = (Student)cmbLink.SelectedItem;
// Add the link into the database
using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.StudentManagerDBConnectionString)) {
// Open the DB connection
connection.Open();
// Set the SqlCommand
SqlCommand command;
// Check if the link exists
using (command = new SqlCommand("SELECT COUNT(*) FROM StudentProgram WHERE programid = @progId AND studentId = @studentId", connection)) {
command.Parameters.AddWithValue("@progId", program.id);
command.Parameters.AddWithValue("@studentId", student.id);
Int32 count = (Int32)command.ExecuteScalar();
// Check if the link exists
if (count > 0)
{
// Display Error Message
MessageBox.Show("Oops... Look like that Course to " + linkSection + " link already exists. Please create a new link and try again!", "Link Exists", MessageBoxButtons.OK, MessageBoxIcon.Error);
// Close the DB connection
connection.Close();
}
else
{
// Add the Link
using (command = new SqlCommand("INSERT INTO StudentProgram (programId, studentId) VALUES (@progId, @studentId)", connection)) {
command.Parameters.AddWithValue("@progId", program.id);
command.Parameters.AddWithValue("@studentId", student.id);
command.ExecuteNonQuery();
// Add the data to the table
tlpLinks.RowCount++;
tlpLinks.RowStyles.Add(new RowStyle(SizeType.AutoSize));
tlpLinks.Controls.Add(new TextBox()
{
Text = program.name, ReadOnly = true, Dock = DockStyle.Fill
}, 0, (tlpLinks.RowCount - 1));
tlpLinks.Controls.Add(new Label()
{
Text = "=>", TextAlign = ContentAlignment.MiddleCenter
}, 1, (tlpLinks.RowCount - 1));
tlpLinks.Controls.Add(new TextBox()
{
Text = student.name, ReadOnly = true, Dock = DockStyle.Fill
}, 2, (tlpLinks.RowCount - 1));
// Display Success Message
MessageBox.Show("Success! Link successfully made!", "Link Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmbPrograms.SelectedIndex = -1;
cmbLink.SelectedIndex = -1;
// Close the DB connection
connection.Close();
}
}
}
}
break;
}
}
}
private void btnGenRep_Click(object sender, EventArgs e)
{
//Fill the adapter
programTableAdapter1.Fill(progFrm.dtsAllData.Program);
// Sort the DataSetViews by course id
// Course-Program Data View
DataView programView = new DataView(progFrm.dtsAllData.Program);
programView.Sort = "id ASC";
// Create the needed rendering variable
HtmlToPdf Renderer = new HtmlToPdf();
// Set rendering options
Renderer.PrintOptions.Title = "All Programs Report - " + DateTime.Now.ToLongDateString();
Renderer.PrintOptions.DPI = 600;
// Header Options
Renderer.PrintOptions.FirstPageNumber = 1;
Renderer.PrintOptions.Header.DrawDividerLine = true;
Renderer.PrintOptions.Header.LeftText = "All Programs - " + DateTime.Now.ToLongDateString();
Renderer.PrintOptions.Header.FontFamily = "Helvetica,Arial";
Renderer.PrintOptions.Header.FontSize = 12;
// Footer options
Renderer.PrintOptions.Footer.DrawDividerLine = true;
Renderer.PrintOptions.Footer.FontFamily = "Arial";
Renderer.PrintOptions.Footer.FontSize = 10;
Renderer.PrintOptions.Footer.LeftText = "{date} {time}";
Renderer.PrintOptions.Footer.RightText = "{page} of {total-pages}";
// Create the needed HTML to create the PDF
string htmlPDFFile = "";
// Add file CSS Style
htmlPDFFile += "<style>" +
"* {" +
"font-family: Helvetica, Arial;" +
"}" +
"table, th, td {" +
"border: 1px solid black;" +
"border-collapse: collapse;" +
"}" +
"</style>";
// Add in the Programs Table
htmlPDFFile += "<h3>Programs Information<h3><table style='width:100%;'>" +
"<thead>" +
"<tr>" +
"<th>Program Id</th>" +
"<th>Program Name</th>" +
"<th>Duration</th>" +
"<th>Co-op Option</th>" +
"<th>Outcome</th>" +
"</tr>" +
"</thead>" +
"<tbody>";
// Loop throug each program
foreach (DataRow row in progFrm.dtsAllData.Program)
{
// Create a new Program instance with the correct information
Programs rowProgram = new Programs(int.Parse(row["id"].ToString()), row["name"].ToString(), int.Parse(row["duration"].ToString()), char.Parse(row["coop"].ToString()), row["outcome"].ToString());
// Hold Real Coop Value
string coop = (rowProgram.coop.ToString().Equals("0")) ? "No" : "Yes";
// Add the program to the table
htmlPDFFile += "<tr>" +
"<td>" + rowProgram.id + "</td>" +
"<td>" + rowProgram.name + "</td>" +
"<td>" + rowProgram.duration + " months</td>" +
"<td>" + coop + "</td>" +
"<td>" + rowProgram.outcome + "</td>" +
"</tr>";
}
// Close Program Table
htmlPDFFile += "</tbody></table>";
// Create the needed objects
System.IO.Stream reportStream;
SaveFileDialog sfdReport = new SaveFileDialog();
// Set the SaveFileDialog Options
sfdReport.Filter = "PDF Files (*.PDF)|*.pdf";
sfdReport.FileName = new String(("Programs Report (" + DateTime.Now.ToShortDateString() + ") Student Content Management System").ToCharArray()).Replace(" ", "_").Replace("/", "-") + ".pdf";
sfdReport.Title = "Save Program Report";
sfdReport.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
// Perform the saving of the file
// Check if the user wants to save the file
if (sfdReport.ShowDialog() == DialogResult.OK)
{
// Check if open file and be made with the stream
if ((reportStream = sfdReport.OpenFile()) != null)
{
// Copy the PDF stream to the reportStream
Renderer.RenderHtmlAsPdf(htmlPDFFile).Stream.CopyTo(reportStream);
// Close the stream
reportStream.Close();
MessageBox.Show("Report Successfully Generated!");
}
}
}