public static void test(string testloc)
{
AllStatsRow stat;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
SqlCommand command = new SqlCommand("", conn);
//SqlDataReader read = null;
FileStream stream = File.Open(testloc, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = true,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true,
FilterRow = (rowReader) =>
{
return(true);
}
}
}
);
reader.Close();
//need to remove the top three rows
result.Tables[0].Rows.RemoveAt(0);
result.Tables[0].Rows.RemoveAt(0);
result.Tables[0].Rows.RemoveAt(0);
List <Ticket> ticket_list = new List <Ticket>();
Ticket temp;
foreach (DataRow r in result.Tables[0].Rows)
{
temp = new Ticket(r[1].ToString(), Convert.ToDateTime(r[3]),
((r[1].ToString() == "Closed" || r[1].ToString() == "Cancelled") ? Convert.ToDateTime(r[4]) : DateTime.MinValue),
((DateTime.Now - Convert.ToDateTime(r[3].ToString())).Days), r[0].ToString(),
"NULL", r[2].ToString(), r[5].ToString());
ticket_list.Add(temp);
}
//info manipulations
int open_c = 0;
int ass_c = 0;
int seven_create = 0;
int thirt_create = 0;
int ninet_create = 0;
int seven_close = 0;
int thirt_close = 0;
int nint_close = 0;
int seven_age = 0;
int eight_age = 0;
int fifteen_age = 0;
int twenty_age = 0;
int thirt_age = 0;
foreach (Ticket tick in ticket_list)
{
if (tick.Age < 8)
{
seven_create++;
}
if (tick.Age < 31)
{
thirt_create++;
}
if (tick.Age < 91)
{
ninet_create++;
}
if (tick.Status == "Closed" || tick.Status == "Cancelled")
{
if (tick.Age < 8)
{
seven_close++;
}
if (tick.Age < 31)
{
thirt_close++;
}
if (tick.Age < 91)
{
nint_close++;
}
}
else
{
open_c++;
if (tick.Age < 8)
{
seven_age++;
}
else if (tick.Age >= 8 && tick.Age < 15)
{
eight_age++;
}
else if (tick.Age >= 15 && tick.Age < 22)
{
fifteen_age++;
}
else if (tick.Age >= 22 && tick.Age < 31)
{
twenty_age++;
}
else
{
thirt_age++;
}
if (tick.AssignedName != "Unassigned")
{
ass_c++;
}
}
}
//stat = new AllStatsRow( new DateTime(2018, 2, 21).ToString("MM-dd-yy"), open_c, ass_c, seven_create, thirt_create, ninet_create, seven_close, thirt_close, nint_close, seven_age, eight_age, fifteen_age, twenty_age, thirt_age);
stat = new AllStatsRow(DateTime.Now.ToString("MM-dd-yy"), open_c, ass_c, seven_create, thirt_create, ninet_create, seven_close, thirt_close, nint_close, seven_age, eight_age, fifteen_age, twenty_age, thirt_age);
try
{
Console.WriteLine("Inserting to Velocity table");
conn.Open();
command = new SqlCommand("Velocity_Insert", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
//insert information from stat
command.Parameters.Add("@reportdate", SqlDbType.DateTime).Value = stat.Report_Date;
command.Parameters.Add("@opencount", SqlDbType.Int).Value = stat.Open_count;
command.Parameters.Add("@assignedcount", SqlDbType.Int).Value = stat.Assigned_count;
command.Parameters.Add("@sevendaycreate", SqlDbType.Int).Value = stat.Seven_Day_Created_Count;
command.Parameters.Add("@thirtydaycreate", SqlDbType.Int).Value = stat.Thirty_Day_Created_Count;
command.Parameters.Add("@ninetydaycreate", SqlDbType.Int).Value = stat.Ninety_Day_Created_Count;
command.Parameters.Add("@sevendayclose", SqlDbType.Int).Value = stat.Seven_Day_Closed_Count;
command.Parameters.Add("@thirtydayclose", SqlDbType.Int).Value = stat.Thirty_Day_Closed_Count;
command.Parameters.Add("@ninetydayclose", SqlDbType.Int).Value = stat.Ninety_Day_Closed_Count;
command.Parameters.Add("@sevenage", SqlDbType.Int).Value = stat.Less_Than_Seven_Days_Old;
command.Parameters.Add("@eightage", SqlDbType.Int).Value = stat.Eight_To_Fourteen_Days_Old;
command.Parameters.Add("@fifteenage", SqlDbType.Int).Value = stat.Fifteen_To_Twenty_Days_Old;
command.Parameters.Add("@twentdayage", SqlDbType.Int).Value = stat.TwentyTwo_To_Thirty_Days_Old;
command.Parameters.Add("@thirtydayage", SqlDbType.Int).Value = stat.More_Than_Thirty_Day_Old;
int tp = command.ExecuteNonQuery();
//means that one row was not affected on the one row insert, therefore there was a error
if (tp != -1)
{
throw new Exception("Insert Velocity did not insert correctly");
}
Console.WriteLine("Insert to Velocity table successfully completed");
conn.Close();
//Run the snapshot clear
Console.WriteLine("Clearing snapshot");
conn.Open();
command = new SqlCommand("Ticket_Clear", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
////this should have a check around it after the first couple times to make sure its fully cleared out
command.ExecuteNonQuery();
conn.Close();
Console.WriteLine("Snapshot cleared");
//insert for the ticket snap shot
Console.WriteLine("Inserting snapshot");
foreach (Ticket t in ticket_list)
{
conn.Open();
command = new SqlCommand("Ticket_Insert", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("@assignedname", SqlDbType.VarChar).Value = t.AssignedName;
command.Parameters.Add("@stat", SqlDbType.VarChar).Value = t.Status;
command.Parameters.Add("@prior", SqlDbType.VarChar).Value = t.Priority;
command.Parameters.Add("@dater", SqlDbType.DateTime).Value = t.Requested;
command.Parameters.Add("@support", SqlDbType.VarChar).Value = t.Support_Group;
if (t.Status == "Closed")
{
command.Parameters.Add("@datec", SqlDbType.DateTime).Value = t.Completed;
}
else if (t.Status == "Cancelled")
{
command.Parameters.Add("@datec", SqlDbType.DateTime).Value = t.Completed;
}
else
{
//nullable on database so I dont pass it
command.Parameters.Add("@datec", SqlDbType.DateTime).Value = DBNull.Value;
}
if (command.ExecuteNonQuery() != -1)
{
throw new Exception("Insert Ticket did not insert correctly");
}
conn.Close();
}
Console.WriteLine("Insert snapshot completed");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
Console.WriteLine("wait");
}
//reports section of the program
private void rbtnSelect_Click(object sender, RoutedEventArgs e)
{
//No option selected
if (cbReport.SelectedIndex == -1)
{
System.Windows.MessageBox.Show("Please select a report option from the list");
}
//Employee Reports Selected
else if (cbReport.SelectedIndex == 0)
{
SqlCommand command = new SqlCommand("StatsTracker_GetTickets", sql);
SqlDataReader reader = null;
List <EmpStatRow> rows = new List <EmpStatRow>();
EmpStatRow row = null;
try
{
sql.Open();
command.CommandType = System.Data.CommandType.StoredProcedure;
//command.Parameters.Add("@Date", SqlDbType.DateTime).Value = rDatepicker.SelectedDate.Value.AddDays(-90);
reader = command.ExecuteReader();
while (reader.Read())
{
row = new EmpStatRow(reader["Assigned_To_Name"].ToString(), reader["Status"].ToString(), Convert.ToDateTime(reader["Date_Requested"]));
if (row.status == "Closed")
{
row.com_date = Convert.ToDateTime(reader["Date_Completed"]);
}
else
{
row.com_date = DateTime.MinValue;
}
rows.Add(row);
}
sql.Close();
List <Employee> staff = new List <Employee>();
Employee emp = null;
int age;
foreach (EmpStatRow Row in rows)
{
age = 0;
if (staff.Any(item => item.Username == Row.ass_to_Username) == true)
{
//staff.Find(i => i.Username == Row.ass_to_Username)
if (Row.status != "Closed")
{
//add to assigned count
staff.Find(i => i.Username == Row.ass_to_Username).Assigned_Count++;
//calculate how old the ticket is
age = (rDatepicker.SelectedDate.Value - Row.req_date).Days;
if (age < 8 && age >= 0)
{
staff.Find(i => i.Username == Row.ass_to_Username).LessThanSevenDays_Count++;
}
else if (age >= 8 && age < 15)
{
staff.Find(i => i.Username == Row.ass_to_Username).EightToFourteen_Count++;
}
else if (age >= 15 && age < 22)
{
staff.Find(i => i.Username == Row.ass_to_Username).FourteenToTwentySecond_Count++;
}
else if (age >= 22)
{
staff.Find(i => i.Username == Row.ass_to_Username).TwentySecondToThirty_Count++;
}
}
else
{
//ticket is closed, incremment the closed counter
if (Row.com_date != DateTime.MinValue)
{
age = (rDatepicker.SelectedDate.Value - Row.com_date).Days;
}
else
{
//This should never happen but just in case we set it to the requested date and not the completed date
age = (rDatepicker.SelectedDate.Value - Row.com_date).Days;
}
if (age < 8)
{
staff.Find(i => i.Username == Row.ass_to_Username).SevenDay_ClosedCount++;
}
if (age < 31)
{
staff.Find(i => i.Username == Row.ass_to_Username).ThirtyDay_ClosedCount++;
}
if (age < 91)
{
staff.Find(i => i.Username == Row.ass_to_Username).NinetyDay_ClosedCount++;
}
}
}
else
{
if (Row.status != "Closed")
{
//add to assigned count
//calculate how old the ticket is
age = (rDatepicker.SelectedDate.Value - Row.req_date).Days;
if (age < 8 && age >= 0)
{
emp = new Employee(Row.ass_to_Username, 0, 0, 0, 1, 1, 0, 0, 0);
}
else if (age >= 8 && age < 15)
{
emp = new Employee(Row.ass_to_Username, 0, 0, 0, 1, 0, 1, 0, 0);
}
else if (age >= 15 && age < 22)
{
emp = new Employee(Row.ass_to_Username, 0, 0, 0, 1, 0, 0, 1, 0);
}
else if (age >= 22)
{
emp = new Employee(Row.ass_to_Username, 0, 0, 0, 1, 0, 0, 0, 1);
}
}
else
{
//ticket is closed, incremment the closed counter
if (Row.com_date != DateTime.MinValue)
{
age = (rDatepicker.SelectedDate.Value - Row.com_date).Days;
}
else
{
//This should never happen but just in case we set it to the requested date and not the completed date
age = (rDatepicker.SelectedDate.Value - Row.com_date).Days;
}
if (age < 8)
{
emp = new Employee(Row.ass_to_Username, 1, 1, 1, 0, 0, 0, 0, 0);
}
else if (age < 31)
{
emp = new Employee(Row.ass_to_Username, 0, 1, 1, 0, 0, 0, 0, 0);
}
else if (age < 91)
{
emp = new Employee(Row.ass_to_Username, 0, 0, 1, 0, 0, 0, 0, 0);
}
}
staff.Add(emp);
}
}
ObservableCollection <Employee> data_grid_content = new ObservableCollection <Employee>();
foreach (Employee empp in staff)
{
data_grid_content.Add(empp);
}
rRightPanel.Visibility = Visibility.Visible;
rGrid.AutoGenerateColumns = true;
rGrid.ItemsSource = data_grid_content;
rGrid.DataContext = data_grid_content;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
sql.Close();
//System.Windows.MessageBox.Show("Problem connecting or retrieving content from database");
}
}
//Ticket age selected
else if (cbReport.SelectedIndex == 1)
{
SqlCommand commnd = new SqlCommand("StatsTracker_GetTickets", sql);
SqlDataReader readr = null;
try
{
sql.Open();
commnd.CommandType = System.Data.CommandType.StoredProcedure;
//commnd.Parameters.Add("@Date", SqlDbType.DateTime).Value = rDatepicker.SelectedDate.Value.AddDays(-90);
readr = commnd.ExecuteReader();
List <Ticket> data = new List <Ticket>();
Ticket temp = null;
while (readr.Read())
{
if (readr["Status"].ToString() == "Closed")
{
temp = new Ticket(readr["Status"].ToString(), Convert.ToDateTime(readr["Date_Requested"]), Convert.ToDateTime(readr["Date_Completed"]));
temp.Age = (rDatepicker.SelectedDate.Value - temp.Completed).Days;
temp.AssignedName = readr["Assigned_To_Name"].ToString();
}
else
{
temp = new Ticket(readr["Status"].ToString(), Convert.ToDateTime(readr["Date_Requested"]), DateTime.MinValue);
temp.Age = (rDatepicker.SelectedDate.Value - temp.Requested).Days;
temp.AssignedName = readr["Assigned_To_Name"].ToString();
}
data.Add(temp);
}
sql.Close();
int[] age_data = new int[90];
for (int i = 0; i < 90; i++)
{
age_data[i] = 0;
}
foreach (Ticket it in data)
{
if (it.Status != "Closed")
{
if (it.Age < 91 && it.Age > 0)
{
age_data[(it.Age - 1)]++;
}
else if (it.Age == 0)
{
age_data[0]++;
}
}
}
commnd = new SqlCommand("StatsTracker_Velocity", sql);
readr = null;
commnd.CommandType = System.Data.CommandType.StoredProcedure;
sql.Open();
//commnd.Parameters.Add("@Date", SqlDbType.DateTime).Value = rDatepicker.SelectedDate.Value.AddDays(-90);
readr = commnd.ExecuteReader();
List <AllStatsRow> rws = new List <AllStatsRow>();
AllStatsRow stats;
while (readr.Read())
{
stats = new AllStatsRow(Convert.ToDateTime(readr["Report_Date"]).ToString(), Convert.ToInt32(readr["Open_Count"]),
Convert.ToInt32(readr["Assigned_Count"]), Convert.ToInt32(readr["Seven_Day_Created_Count"]), Convert.ToInt32(readr["Thirty_Day_Created_Count"]),
Convert.ToInt32(readr["Ninety_Day_Created_Count"]), Convert.ToInt32(readr["Seven_Day_Closed_Count"]),
Convert.ToInt32(readr["Thirty_Day_Closed_Count"]), Convert.ToInt32(readr["Ninety_Day_Closed_Count"]),
Convert.ToInt32(readr["Seven_Day_Age"]), Convert.ToInt32(readr["Eight_Day_Age"]), Convert.ToInt32(readr["Fifteen_Day_Age"]),
Convert.ToInt32(readr["Twent_Day_Age"]), Convert.ToInt32(readr["Thirty_Day_Age"]));
rws.Add(stats);
}
sql.Close();
rBarGraph.Visibility = Visibility.Visible;
rRightPanel.Visibility = Visibility.Visible;
rbgGraph.PlotBars(age_data);
rGrid.ItemsSource = rws;
rGrid.DataContext = rws;
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.ToString());
sql.Close();
}
}
//shits borked
else
{
System.Windows.MessageBox.Show("World is ending closing");
Application.Current.Shutdown(99);
}
}
