Employee Reviews

using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Drawing;
using System.Web.SessionState;
using System.IO;
using System.Text.RegularExpressions;
using System.Globalization;
using System.Web.UI.DataVisualization.Charting;

public partial class EmployeeReviews : CODE
{
    /*THIS PAGE SHOW ALL THE CURRENT W2 EMPLOYEES FOR QMS AND WHEN THEIR NEXT REVIEW DATE AS WELL AS THEIR REVIEWER(S)*/

    List<SqlParameter> _params;

    protected void Page_Load(object sender, EventArgs e)
    {
        Master.lblMsg.Text = "Employee Reviews Report";
        SqlConnection myConn = new SqlConnection("some info you dont need");

        myConn.Open();
        string sql = "SELECT EmployeeName, DateHired, SixMonthReview, Sixmonthreviewcompleted, "
                    + "AnnualReview, Annualreviewcompleted, Rater, Senior_Rater, LastReviewDate "
                    + "from EmployeeReview where Active = 1";
        SqlCommand myCommand = new SqlCommand(sql, myConn);
        SqlDataReader myReader = DAL.executeQuery(sql, _params); //myCommand.ExecuteReader();

        DataTable table1 = new DataTable();
        //create columns
        DataColumn name = new DataColumn("Employee");
        DataColumn datehired = new DataColumn("Date Hired");
        DataColumn sixmonthreview = new DataColumn("6th Month Retention (days)");
        DataColumn sixmonthcompletion = new DataColumn("Six-Month Review Completed");
        DataColumn annualreview = new DataColumn("Annual Review (days)");
        DataColumn annualcompletion = new DataColumn("Annual Review Completed ");
        DataColumn rater = new DataColumn("Rater");
        DataColumn seniorrater = new DataColumn("Senior Rater");
        DataColumn nextreview = new DataColumn("Next Review");
        //define datatypes
        name.DataType = System.Type.GetType("System.String");
        datehired.DataType = System.Type.GetType("System.String");
        sixmonthreview.DataType = System.Type.GetType("System.Int32"); //not datetime
        sixmonthcompletion.DataType = System.Type.GetType("System.String");
        annualreview.DataType = System.Type.GetType("System.Int32");
        annualcompletion.DataType = System.Type.GetType("System.String");
        rater.DataType = System.Type.GetType("System.String");
        seniorrater.DataType = System.Type.GetType("System.String");
        nextreview.DataType = System.Type.GetType("System.DateTime");
        //add columns to table
        table1.Columns.Add(name);
        table1.Columns.Add(datehired);
        table1.Columns.Add(sixmonthreview);
        table1.Columns.Add(sixmonthcompletion);
        table1.Columns.Add(annualreview);
        table1.Columns.Add(annualcompletion);
        table1.Columns.Add(rater);
        table1.Columns.Add(seniorrater);
        table1.Columns.Add(nextreview);

        DateTime curDate = DateTime.Now;

        if (myReader.HasRows)
        {
            while (myReader.Read())
            {
                //Create row
                DataRow row = table1.NewRow();
                //fill columns
                if (myReader.GetDateTime(1) != null)
                {
                    row[name] = myReader.GetString(0);
                    row[datehired] = Convert.ToDateTime(myReader.GetDateTime(1)).ToString("MM/dd/yyyy");
                    if (myReader.GetBoolean(3) == false)
                    {
                        TimeSpan ts = curDate - myReader.GetDateTime(1);
                        row[sixmonthreview] = 182 - Convert.ToInt32(ts.Days);
                        row[sixmonthcompletion] = "No";
                        row[annualreview] = Convert.ToInt32(row[sixmonthreview]) + 365;
                        row[annualcompletion] = "No";
                        if (myReader.GetValue(6).ToString() == string.Empty)
                        {
                            row[rater] = "None";
                        }
                        else
                            row[rater] = myReader.GetValue(6).ToString();
                        if (myReader.GetValue(7).ToString() == string.Empty)
                        {
                            row[seniorrater] = "None";
                        }
                        else
                            row[seniorrater] = myReader.GetValue(7).ToString();
                        row[nextreview] = curDate.AddDays(Convert.ToInt32(row[sixmonthreview])).ToString("MM/dd/yyyy");
                    }
                    else
                    {
                        TimeSpan ts2 = curDate - myReader.GetDateTime(8);
                        string xyz = myReader.GetDateTime(8).ToString();
                        row[sixmonthreview] = 0;
                        row[sixmonthcompletion] = "Yes";
                        row[annualreview] = 365 - ts2.Days;
                        if(myReader.GetBoolean(5) == true)
                            row[annualcompletion] = "Yes";
                        else
                            row[annualcompletion] = "No";
                        if (myReader.GetValue(6).ToString() == string.Empty)
                        {
                            row[rater] = "None";
                        }
                        else
                            row[rater] = myReader.GetValue(6).ToString();
                        if (myReader.GetValue(7).ToString() == string.Empty)
                        {
                            row[seniorrater] = "None";
                        }
                        else
                            row[seniorrater] = myReader.GetValue(7).ToString();
                        row[nextreview] = myReader.GetDateTime(8).AddDays(365).ToString("MM/dd/yyyy");

                    }

                    //add the row
                    table1.Rows.Add(row);
                }

            }
        }
        else
            Console.WriteLine("No Rows Returned");
        myReader.Close();
        myConn.Close();

        table1.DefaultView.Sort = "Next Review asc";

        gvEmployeeReview.DataSource = table1;
        DataView view = table1.DefaultView;
        //view.Sort = "Next Review asc";
        gvEmployeeReview.DataBind();
        ViewState["dtbl"] = table1;

    }

    protected void ComponentGridViewSorting(object sender, GridViewSortEventArgs e)
    {
        DataTable dataTable1 = ViewState["dtbl"] as DataTable;

        if (dataTable1 != null)
        {
            DataView dataview1 = new DataView(dataTable1);
            dataview1.Sort = e.SortExpression + " " + ConvertSortDirection(e.SortDirection);

            gvEmployeeReview.DataSource = dataview1;
            gvEmployeeReview.DataBind();
        }
    }

    private string ConvertSortDirection(SortDirection sortDirection)
    {
        string newSortDirection = String.Empty;

        switch (sortDirection)
        {
            case SortDirection.Ascending:
                newSortDirection = "ASC";
                break;

            case SortDirection.Descending:
                newSortDirection = "DESC";
                break;
        }

        return newSortDirection;
    }


}