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