/*Name: Mike Figueroa * Function Name: BindDataGrid * Purpose: HotTopics Constructor to set query to populate data in the DataGrid based on dropdown selection. * Parameters: string[] user_data * Return Value: query * Local Variables: query - Stores SQL Query that is executed on the data grid * Algorithm: 1. Looks to see selection from combo box * 1a. if selection from combobox is All, select all issues * 1b. if else populate based on critera selected in the combobox * 2. Populate selected data in the Data Grid. * Version: 2.0.0.4 * Date modified: Prior to 1/1/20 * Assistance Received: Comments By Dom Carrubba */ public void BindDataGrid() { string query; if (SystemComboBox.SelectedItem.ToString() == "All") { query = "SELECT New_Issues.Sys_Impact as [System], New_Issues.Assigned_To AS[Owner], New_Issues.TFS_BC_HDFS_Num as BID, New_Issues.Impact, New_Issues.Supporting_Details, " + "New_Issues.Title, FORMAT(Latest_Status_Update, 'MM/dd/yyyy') as Latest_Status_Update, (SELECT DATEDIFF(day, Opened_Date, CONVERT(date, GETDATE()))) as Open_Days, " + "(SELECT DATEDIFF(day, Latest_Status_Update, CONVERT(date, GETDATE()))) as Status_Days, h1.TaskNum, New_Issues.ID as ID " + "FROM New_Issues " + "LEFT JOIN(SELECT TaskNum, MAX(EntryDate) AS Latest_Status_Update FROM History GROUP BY TaskNum) h1 " + "ON h1.TaskNum = New_Issues.ID " + "WHERE Hot_Topic = 1 " + "ORDER BY New_Issues.Priority_Number ASC;"; reportQuery = query; } else { query = "SELECT New_Issues.Sys_Impact as [System], New_Issues.Assigned_To AS[Owner], New_Issues.TFS_BC_HDFS_Num as BID, New_Issues.Impact, New_Issues.Supporting_Details, " + "New_Issues.Title, FORMAT(Latest_Status_Update, 'MM/dd/yyyy') as Latest_Status_Update, (SELECT DATEDIFF(day, Opened_Date, CONVERT(date, GETDATE()))) as Open_Days, " + "(SELECT DATEDIFF(day, Latest_Status_Update, CONVERT(date, GETDATE()))) as Status_Days, h1.TaskNum, New_Issues.ID as ID " + "FROM New_Issues " + "LEFT JOIN(SELECT TaskNum, MAX(EntryDate) AS Latest_Status_Update FROM History GROUP BY TaskNum) h1 " + "ON h1.TaskNum = New_Issues.ID " + "WHERE (Hot_Topic = 1) AND Sys_Impact = '" + ReportHelper.SystemChosen(SystemComboBox) + "' " + "ORDER BY Priority_Number ASC;"; reportQuery = query; } using (SqlConnection con = new SqlConnection(connectionString)) try { con.Open(); SqlCommand cmd = new SqlCommand(query, con); DataTable Reports = new DataTable(); SqlDataAdapter sda = new SqlDataAdapter(cmd); //fill report DataGrid with the query generated using (sda) { sda.Fill(Reports); HotTopicsReport.Visibility = Visibility.Visible; HotTopicsReport.ItemsSource = Reports.DefaultView; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { con.Close(); } }
/*Name: Michael Figueroa * Function Name: BusinessCases * Purpose: Constructor for the BusinessCases form * Parameters: string[] user_data * Return Value: None * Local Variables: None * Algorithm: Fills SystemComboBox, then calls FillStatusComboBoxWithAll, assigns both combo boxes to index 0 ("All"), then calls BindDataGrid * Version: 2.0.0.4 * Date modified: Prior to 1/1/20 * Assistance Received: N/A */ public BusinessCases(string[] user_data) { InitializeComponent(); arr = user_data; FillSystemComboBox(); ReportHelper.FillStatusComboBoxWithAll(StatusComboBox); SystemComboBox.SelectedIndex = 0; StatusComboBox.SelectedIndex = 0; BindDataGrid(ReportHelper.SystemChosen(SystemComboBox)); //Collapses all DataGrids until a system is chosen from the Combo Box }
private string SysQuery() { if (ReportHelper.SystemChosen(SystemComboBox) != "All") { return("AND (Sys_Impact = '" + ReportHelper.SystemChosen(SystemComboBox) + "')"); } else { return(""); } }
/*Name: Michael Figueroa * Function Name: BindDataGrid * Purpose: Binds data to DataGrid * Parameters: string[] user_data * Return Value: None * Local Variables: None * Algorithm: If system chosen is "All", no sys_impact in WHERE clause of query; else, query has sys_Impact = sys; ReportQuery is = query and used for excel export (this may no * longer be necessary), then standard SQL-DataTable procedures follow, and DataTable is used to bind to DataGrid * Version: 2.0.0.4 * Date modified: Prior to 1/1/20 - Would like this method simplified or eliminated all-together at some point as part of code cleanup * Assistance Received: N/A */ public void BindDataGrid(string sys) { string query; using (SqlConnection con = new SqlConnection(connectionString)) try { if (ReportHelper.SystemChosen(SystemComboBox) == "All") { query = "SELECT ID, Priority_Number, Sys_Impact as [System], Category, Req_Name as Req, TFS_BC_HDFS_Num as BID_ID, " + "Assigned_To as [Owner], FORMAT(Opened_Date,'MM/dd/yyyy') AS Opened_Date, [Status], Title, " + "Impact, IIf(Completed_Date Is Not Null, DATEDIFF(DAY, Opened_Date, Completed_Date), DATEDIFF(DAY, Opened_Date, Getdate())) as [Days] " + "FROM New_Issues WHERE (Category LIKE 'BC%') AND " + StatusString() + "ORDER BY Priority_Number ASC;"; } else { query = "SELECT ID, Priority_Number, Sys_Impact as [System], Category, Req_Name as Req, TFS_BC_HDFS_Num as BID_ID, " + "Assigned_To as [Owner], FORMAT(Opened_Date,'MM/dd/yyyy') AS Opened_Date, [Status], Title, " + "Impact, IIf(Completed_Date Is Not Null, DATEDIFF(DAY, Opened_Date, Completed_Date), DATEDIFF(DAY, Opened_Date, Getdate())) as [Days] " + "FROM New_Issues WHERE (Sys_Impact LIKE '%" + sys + "%') AND (Category LIKE 'BC%') AND " + StatusString() + "ORDER BY Priority_Number ASC;"; } reportQuery = query; con.Open(); SqlCommand cmd = new SqlCommand(query, con); DataTable dt = new DataTable(); SqlDataAdapter sda = new SqlDataAdapter(cmd); using (sda) { sda.Fill(dt); } Report.ItemsSource = dt.DefaultView; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { con.Close(); } }
/*Name: Michael Figueroa * Function Name: StatusComboBox_SelectionChanged * Purpose: Event handler for when a new Status is chosen from combobox * Parameters: Auto-Generated * Return Value: None * Local Variables: None * Algorithm: Calls BindDataGrid to refresh datagrid * Version: 2.0.0.4 * Date modified: Prior to 1/1/20 * Assistance Received: N/A */ private void StatusComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e) { BindDataGrid(ReportHelper.SystemChosen(SystemComboBox)); }