Employee Review Updated

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("Data Source=66.18.125.86,1433;Network Library=DBMSSOCN;Initial Catalog=Audits;User ID=webusr;Password=adm!n4qms1; Connect Timeout=500;");

        myConn.Open();
        string sql = "SELECT EmployeeName, DateHired, SixMonthReview, Sixmonthreviewcompleted, "
                    + "AnnualReview, Annualreviewcompleted, Rater, Senior_Rater, LastReviewDate, "
                    + "case when sixmonthreviewcompleted = 0 then dateadd(day,180,datehired) else dateadd(year, 1, LastReviewDate) end as nextreview "
                    + ", CONVERT(varchar(10), lastreviewdate, 101) as Today "
                    + "from EmployeeReview where Active = 1 order by nextreview asc";
        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");
                        row[nextreview] = myReader.GetDateTime(9);
                    }
                    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");
                        row[nextreview] = myReader.GetValue(10);
                    }

                    //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;

        ((BoundField)gvEmployeeReview.Columns[1]).DataFormatString = "{0:d}"; //date hired column
        ((BoundField)gvEmployeeReview.Columns[8]).DataFormatString = "{0:d}"; //next review colum


    }

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


}