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