Blackboard_Activity_Accumulator

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Windows.Documents;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Reflection;
using System.Collections;


namespace Blackboard_Activity_Accumulator
{

    public static class ExtensionMethods
    {

        public static void DoubleBuffered(this DataGridView dgv, bool setting)
       {
           Type dgvType = dgv.GetType();
            PropertyInfo pi = dgvType.GetProperty("DoubleBuffered",
              BindingFlags.Instance | BindingFlags.NonPublic);
            pi.SetValue(dgv, setting, null);
        }
    }


    public partial class Form1 : Form
    {
        StringFormat strFormat; //Used to format the grid rows.
        ArrayList arrColumnLefts = new ArrayList();//Used to save left coordinates of columns
        ArrayList arrColumnWidths = new ArrayList();//Used to save column widths
        int iCellHeight = 0; //Used to get/set the datagridview cell height
        int iTotalWidth = 0; //

        int iRow = 0;//Used as counter
        bool bFirstPage = false; //Used to check whether we are printing first page
        bool bNewPage = false;// Used to check whether we are printing a new page
        int iHeaderHeight = 0; //Used for the header height




        public Form1()
        {
            InitializeComponent();
            dataGridView1.DoubleBuffered(true);
            this.Location = new Point(0, 0);
            this.Size = Screen.PrimaryScreen.WorkingArea.Size;

        }




        private void btnRunQuery_Click(object sender, EventArgs e)
        {
            if (txtBoxUsername.Text == "" && txtBoxCourseID.Text == "")
            {
                MessageBox.Show("The username and course ID fields were blank.",
                "Warning",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation,
                MessageBoxDefaultButton.Button1);
            }
            else if (txtBoxUsername.Text == "" && txtBoxCourseID.Text != "")
            {
                MessageBox.Show("The username field was blank.",
                "Warning",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation,
                MessageBoxDefaultButton.Button1);
            }
            else if (txtBoxUsername.Text != "" && txtBoxCourseID.Text == "")
            {


                SqlConnection myConnection = new SqlConnection("user id=<removed_for_security>;" +
                                        "password=<removed_for_security>;server=<removed_for_security>;" +
                                        "Trusted_Connection=no;" +
                                        "connection timeout=30");
                try
                {
                    myConnection.Open();
                }
                catch (Exception ex1)
                {
                    Console.WriteLine(ex1.ToString());
                }
                string username = string.Empty;
                myCommand1.Parameters.Add(new SqlParameter("@userID", System.Data.SqlDbType.VarChar));
                myCommand1.Parameters["@userID"].Value = txtBoxUsername.Text;
                SqlCommand myCommand1 = new SqlCommand("use BBLEARN select user_id from users where user_id = @userID", myConnection);
                username = (string)myCommand1.ExecuteScalar();



                if (username == null)
                {
                    MessageBox.Show("A user by the handle of " + txtBoxUsername.Text + " could not be identified in the database. Their Blackboard account may be disabled. Please double check your entry and try again.",
                "Warning",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation,
                MessageBoxDefaultButton.Button1);
                }


                else
                {

                    try
                    {
                myCommand.Parameters.Add(new SqlParameter("@userID", System.Data.SqlDbType.VarChar));
                myCommand.Parameters["@userID"].Value = txtBoxUsername.Text;
                        SqlCommand myCommand = new SqlCommand("SELECT u.user_id, aa.timestamp, cm.batch_uid, cc.title, aa.event_type, aa.data, aa.session_id FROM users u INNER JOIN activity_accumulator aa on u.pk1 = aa.user_pk1 LEFT JOIN course_main cm ON aa.course_pk1 = cm.pk1 LEFT JOIN course_contents cc on aa.content_pk1 = cc.pk1 WHERE u.user_id = @userID AND DateDiff(Hour, aa.timestamp, CURRENT_TIMESTAMP) <= 120 UNION SELECT u.user_id, aa.timestamp, cm.batch_uid, cc.title, aa.event_type, aa.data, aa.session_id FROM users u INNER JOIN activity_accumulator_queue aa on u.pk1 = aa.user_pk1 LEFT JOIN course_main cm ON aa.course_pk1 = cm.pk1 LEFT JOIN course_contents cc on aa.content_pk1 = cc.pk1 WHERE u.user_id = @userID AND DateDiff(Hour, aa.timestamp, CURRENT_TIMESTAMP) <= 120 ORDER BY aa.timestamp desc", myConnection);
                        SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
                        DataTable t = new DataTable();
                        adapter.Fill(t);

                        dataGridView1.DataSource = t;

                    }
                    catch (Exception ex2)
                    {
                        Console.WriteLine(ex2.ToString());
                    }

                    try
                    {
                        myConnection.Close();
                    }
                    catch (Exception ex3)
                    {
                        Console.WriteLine(ex3.ToString());
                    }
                }
            }






            else
            {
                SqlConnection myConnection = new SqlConnection("user id=<removed_for_security>;" +
                                       "password=<removed_for_security>;server=<removed_for_security>;" +
                                       "Trusted_Connection=no;" +
                                       "connection timeout=30");
                try
                {
                    myConnection.Open();
                }
                catch (Exception ex1)
                {
                    Console.WriteLine(ex1.ToString());
                }
                string username = string.Empty;
                myCommand1.Parameters.Add(new SqlParameter("@userID", System.Data.SqlDbType.VarChar));
                myCommand1.Parameters["@userID"].Value = txtBoxUsername.Text;
                SqlCommand myCommand1 = new SqlCommand("use BBLEARN select user_id from users where user_id = @userID", myConnection);
                username = (string)myCommand1.ExecuteScalar();

                string courseID = string.Empty;
                myCommand2.Parameters.Add(new SqlParameter("@courseID", System.Data.SqlDbType.VarChar));
                myCommand2.Parameters["@courseID"].Value = txtBoxCourseID.Text;
                SqlCommand myCommand2 = new SqlCommand("use BBLEARN select course_id from course_main where course_id = @courseID", myConnection);
                courseID = (string)myCommand2.ExecuteScalar();

                try
                {
                    myConnection.Close();
                }
                catch (Exception ex3)
                {
                    Console.WriteLine(ex3.ToString());
                }

                int invalidDataCounter = -1;

                if (username == null)
                {
                    invalidDataCounter = 0;
                }
                if (courseID == null)
                {
                    invalidDataCounter = 1;
                }
                if (username == null && courseID == null)
                {
                    invalidDataCounter = 2;
                }

                switch (invalidDataCounter)
                {
                    case 0:
                        MessageBox.Show("A user by the handle of " + txtBoxUsername.Text + " could not be identified in the database. Their Blackboard account may be disabled. Please double check your entry and try again.",
                "Warning",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation,
                MessageBoxDefaultButton.Button1);
                        break;
                    case 1:
                        MessageBox.Show("A course by the ID of " + txtBoxCourseID.Text + " could not be identified in the database. The course might be disabled if it didn't make. Please double check your entry and try again. ",
                "Warning",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation,
                MessageBoxDefaultButton.Button1);
                        break;
                    case 2:
                        MessageBox.Show("Both the username and course ID were invalid. Please double check your entries and try again.",
                "Warning",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation,
                MessageBoxDefaultButton.Button1);
                        break;
                    default:
                        break;
                }
                if (invalidDataCounter == -1)
                {
                    try
                    {
                        myConnection.Open();
                    }
                    catch (Exception ex1)
                    {
                        Console.WriteLine(ex1.ToString());
                    }
                    try
                    {
myCommand.Parameters.Add(new SqlParameter("@userID", System.Data.SqlDbType.VarChar));
                myCommand.Parameters["@userID"].Value = txtBoxUsername.Text;
myCommand.Parameters.Add(new SqlParameter("@courseID", System.Data.SqlDbType.VarChar));
                myCommand.Parameters["@courseID"].Value = txtBoxCourseID.Text;
                        SqlCommand myCommand = new SqlCommand("SELECT u.user_id, aa.timestamp, cm.batch_uid, cc.title, aa.event_type, aa.data, aa.session_id FROM users u INNER JOIN activity_accumulator aa on u.pk1 = aa.user_pk1 LEFT JOIN course_main cm ON aa.course_pk1 = cm.pk1 LEFT JOIN course_contents cc on aa.content_pk1 = cc.pk1 WHERE u.user_id = @userID and cm.course_id = @courseID AND DateDiff(Hour, aa.timestamp, CURRENT_TIMESTAMP) <= 120 UNION SELECT u.user_id, aa.timestamp, cm.batch_uid, cc.title, aa.event_type, aa.data, aa.session_id FROM users u INNER JOIN activity_accumulator_queue aa on u.pk1 = aa.user_pk1 LEFT JOIN course_main cm ON aa.course_pk1 = cm.pk1 LEFT JOIN course_contents cc on aa.content_pk1 = cc.pk1 WHERE u.user_id = @userID and cm.course_id = @courseID AND DateDiff(Hour, aa.timestamp, CURRENT_TIMESTAMP) <= 120 ORDER BY aa.timestamp desc", myConnection);
                        SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
                        DataTable t = new DataTable();
                        adapter.Fill(t);

                        dataGridView1.DataSource = t;

                    }
                    catch (Exception ex2)
                    {
                        Console.WriteLine(ex2.ToString());
                    }

                    try
                    {
                        myConnection.Close();
                    }
                    catch (Exception ex3)
                    {
                        Console.WriteLine(ex3.ToString());
                    }
                }
            }

        }

        private void exportToExcelSpreadsheetxlsToolStripMenuItem_Click(object sender, EventArgs e)
        {

            copyAlltoClipboard();
            Microsoft.Office.Interop.Excel.Application xlexcel;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlexcel = new Microsoft.Office.Interop.Excel.Application();
            xlexcel.Visible = true;
            xlWorkBook = xlexcel.Workbooks.Add(misValue);
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];
            Microsoft.Office.Interop.Excel.Range colorRange;
            colorRange = xlWorkSheet.get_Range("A1", "G1");
            colorRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
            CR.Cells[1, 1] = "user_id";
            CR.Cells[1, 2] = "timestamp";
            CR.Cells[1, 2].EntireColumn.NumberFormat = "M/d/yyyy h:mm:ss AM/PM";
            CR.Cells[1, 3] = "batch_uid";
            CR.Cells[1, 4] = "title";
            CR.Cells[1, 5] = "event_type";
            CR.Cells[1, 6] = "data";
            CR.Cells[1, 7] = "session_id";
            CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[2, 1];
            CR.Select();
            xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
            xlWorkSheet.Columns.AutoFit();

        }

        private void copyAlltoClipboard()
        {
            dataGridView1.SelectAll();
            DataObject dataObj = dataGridView1.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);
        }

        private void printActivityReportToolStripMenuItem_Click(object sender, EventArgs e)
        {



            PrintPreviewDialog objPPdialog = new PrintPreviewDialog();
            objPPdialog.Document = printDocument2;

            ToolStrip ts = new ToolStrip();
            ts.Name = "wrongToolStrip";
            foreach (Control ctl in objPPdialog.Controls)
            {
                if (ctl.Name.Equals("toolStrip1"))
                {
                    ts = ctl as ToolStrip;
                    break;
                }
            }
            ToolStripButton printButton = new ToolStripButton();
            foreach (ToolStripItem tsi in ts.Items)
            {
                if (tsi.Name.Equals("printToolStripButton"))
                {
                    printButton = tsi as ToolStripButton;
                }
            }

            printButton.Click += new EventHandler(this.SelectPrinterAfterPreview);

            ts.Items.Remove(printButton);
            ToolStripButton b = new ToolStripButton();
            b.ImageIndex = printButton.ImageIndex;
            b.Visible = true;
            ts.Items.Insert(0, b);
            b.Click += new EventHandler(this.SelectPrinterAfterPreview);

            printDocument2.DefaultPageSettings.Landscape = true;
            //((ToolStripButton)((ToolStrip)objPPdialog.Controls[1]).Items[0]).
            objPPdialog.ShowDialog();

        }

        private void SelectPrinterAfterPreview(object sender, EventArgs e)
        {
            PagedPrintDocument ppd = new PagedPrintDocument();
            PrintDialog printDialog = new PrintDialog();
            printDialog.Document = printDocument2;
            printDocument2.DefaultPageSettings.Landscape = true;

            ppd.PageFrom = printDialog.PrinterSettings.FromPage;
            ppd.PageTo = printDialog.PrinterSettings.ToPage;

            printDialog.AllowSomePages = true;
            printDialog.UseEXDialog = true;
            if (DialogResult.OK == printDialog.ShowDialog())
            {
                printDocument2.DocumentName = "Activity Report";
                printDocument2.Print();
            }
        }

        private void printDocument1_BeginPrint(object sender, System.Drawing.Printing.PrintEventArgs e)
        {


            try
            {
                strFormat = new StringFormat();
                strFormat.Alignment = StringAlignment.Near;
                strFormat.LineAlignment = StringAlignment.Center;
                strFormat.Trimming = StringTrimming.EllipsisCharacter;

                arrColumnLefts.Clear();
                arrColumnWidths.Clear();
                iCellHeight = 0;
                iRow = 0;
                bFirstPage = true;
                bNewPage = true;

                // Calculating Total Widths
                iTotalWidth = 0;
                foreach (DataGridViewColumn dgvGridCol in dataGridView1.Columns)
                {
                    iTotalWidth += dgvGridCol.Width;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void printDocument1_PrintPage(object sender,
    System.Drawing.Printing.PrintPageEventArgs e)
        {
            try
            {
                //Set the left margin
                int iLeftMargin = e.MarginBounds.Left;
                //Set the top margin
                int iTopMargin = e.MarginBounds.Top;
                //Whether more pages have to print or not
                bool bMorePagesToPrint = false;
                int iTmpWidth = 0;

                //For the first page to print set the cell width and header height
                if (bFirstPage)
                {
                    foreach (DataGridViewColumn GridCol in dataGridView1.Columns)
                    {
                        iTmpWidth = (int)(Math.Floor((double)((double)GridCol.Width /
                            (double)iTotalWidth * (double)iTotalWidth *
                            ((double)e.MarginBounds.Width / (double)iTotalWidth))));

                        iHeaderHeight = (int)(e.Graphics.MeasureString(GridCol.HeaderText,
                            GridCol.InheritedStyle.Font, iTmpWidth).Height) + 11;

                        // Save width and height of headers
                        arrColumnLefts.Add(iLeftMargin);
                        arrColumnWidths.Add(iTmpWidth);
                        iLeftMargin += iTmpWidth;
                    }
                }
                //Loop till all the grid rows not get printed
                while (iRow <= dataGridView1.Rows.Count - 1)
                {
                    DataGridViewRow GridRow = dataGridView1.Rows[iRow];
                    //Set the cell height
                    iCellHeight = GridRow.Height;
                    int iCount = 0;
                    //Check whether the current page settings allows more rows to print
                    if (iTopMargin + iCellHeight >= e.MarginBounds.Height + e.MarginBounds.Top)
                    {
                        bNewPage = true;
                        bFirstPage = false;
                        bMorePagesToPrint = true;
                        break;
                    }
                    else
                    {
                        if (bNewPage)
                        {
                            //Draw Header
                            e.Graphics.DrawString("Activity Report for " + txtBoxUsername.Text + "",
                                new Font(dataGridView1.Font, FontStyle.Bold),
                                Brushes.Black, e.MarginBounds.Left,
                                e.MarginBounds.Top - e.Graphics.MeasureString("Activity Report for " + txtBoxUsername.Text + "",
                                new Font(dataGridView1.Font, FontStyle.Bold),
                                e.MarginBounds.Width).Height - 13);

                            String strDate = DateTime.Now.ToLongDateString() + " " +
                                DateTime.Now.ToShortTimeString();
                            //Draw Date
                            e.Graphics.DrawString(strDate,
                                new Font(dataGridView1.Font, FontStyle.Bold), Brushes.Black,
                                e.MarginBounds.Left +
                                (e.MarginBounds.Width - e.Graphics.MeasureString(strDate,
                                new Font(dataGridView1.Font, FontStyle.Bold),
                                e.MarginBounds.Width).Width),
                                e.MarginBounds.Top - e.Graphics.MeasureString("Activity Report for " + txtBoxUsername.Text + "",
                                new Font(new Font(dataGridView1.Font, FontStyle.Bold),
                                FontStyle.Bold), e.MarginBounds.Width).Height - 13);

                            //Draw Columns
                            iTopMargin = e.MarginBounds.Top;
                            foreach (DataGridViewColumn GridCol in dataGridView1.Columns)
                            {
                                e.Graphics.FillRectangle(new SolidBrush(Color.LightGray),
                                    new Rectangle((int)arrColumnLefts[iCount], iTopMargin,
                                    (int)arrColumnWidths[iCount], iHeaderHeight));

                                e.Graphics.DrawRectangle(Pens.Black,
                                    new Rectangle((int)arrColumnLefts[iCount], iTopMargin,
                                    (int)arrColumnWidths[iCount], iHeaderHeight));

                                e.Graphics.DrawString(GridCol.HeaderText,
                                    GridCol.InheritedStyle.Font,
                                    new SolidBrush(GridCol.InheritedStyle.ForeColor),
                                    new RectangleF((int)arrColumnLefts[iCount], iTopMargin,
                                    (int)arrColumnWidths[iCount], iHeaderHeight), strFormat);
                                iCount++;
                            }
                            bNewPage = false;
                            iTopMargin += iHeaderHeight;
                        }
                        iCount = 0;
                        //Draw Columns Contents
                        foreach (DataGridViewCell Cel in GridRow.Cells)
                        {
                            if (Cel.Value != null)
                            {
                                Font font = new Font("Arial", 7);
                                e.Graphics.DrawString(Cel.Value.ToString(), font, new SolidBrush(Cel.InheritedStyle.ForeColor), new RectangleF((int)arrColumnLefts[iCount], (float)iTopMargin, (int)arrColumnWidths[iCount], (float)iCellHeight), strFormat);
                            }
                            //Drawing Cells Borders
                            e.Graphics.DrawRectangle(Pens.Black,
                                new Rectangle((int)arrColumnLefts[iCount], iTopMargin,
                                (int)arrColumnWidths[iCount], iCellHeight));
                            iCount++;
                        }
                    }
                    iRow++;
                    iTopMargin += iCellHeight;
                }
                //If more lines exist, print another page.
                if (bMorePagesToPrint)
                    e.HasMorePages = true;
                else
                    e.HasMorePages = false;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message, "Error", MessageBoxButtons.OK,
                   MessageBoxIcon.Error);
            }
        }

        private void btnSearchResults_Click(object sender, EventArgs e)
        {
            string filterType = dropDownFilter.SelectedItem.ToString() + " = '{0}'";
            (dataGridView1.DataSource as DataTable).DefaultView.RowFilter = string.Format(filterType, txtBoxSearchResults.Text);

        }



    }
}