private string RegionQuery() { if (ReportHelper.StatusChosen(StatusComboBox) == "All Opened") { return("SELECT New_Issues.ID AS ID, Priority_Number, Sys_Impact AS [System], Category, TFS_BC_HDFS_Num AS BID, Assigned_To AS [Owner], " + "Req_Name AS Req, New_Issues.[Status], Title, Impact, DATEDIFF(DAY, Opened_Date, Getdate()) AS [Days] " + "FROM New_Issues WHERE (Req_Dept LIKE '%" + RegionChosen() + "%' OR Req_Dept LIKE '" + RegionChosen() + "%' OR Req_Dept LIKE '%" + RegionChosen() + "') AND (New_Issues.[Status] NOT LIKE '%closed%' " + "AND New_Issues.[Status] NOT LIKE '%Implemented%' " + "AND New_Issues.[Status] NOT LIKE '%dropped%' AND New_Issues.[Status] NOT LIKE '%deferred%') ORDER BY Priority_Number ASC;"); } else if (ReportHelper.StatusChosen(StatusComboBox) == "All Closed") { return("SELECT New_Issues.ID AS ID, Priority_Number, Sys_Impact AS [System], Category, TFS_BC_HDFS_Num AS BID, Assigned_To AS [Owner], " + "Req_Name AS Req, New_Issues.[Status], Title, Impact, DATEDIFF(DAY, Opened_Date, Getdate()) AS [Days] " + "FROM New_Issues WHERE (Req_Dept LIKE '%" + RegionChosen() + "%' OR Req_Dept LIKE '" + RegionChosen() + "%' OR Req_Dept LIKE '%" + RegionChosen() + "') AND (New_Issues.[Status] = 'closed' " + "OR New_Issues.[Status] = 'Implemented' " + "OR New_Issues.[Status] = 'dropped' OR New_Issues.[Status] = 'deferred') ORDER BY Priority_Number ASC;"); } else { return("SELECT New_Issues.ID AS ID, Priority_Number, Sys_Impact AS [System], Category, TFS_BC_HDFS_Num AS BID, Assigned_To AS [Owner], " + "Req_Name AS Req, New_Issues.[Status], Title, Impact, DATEDIFF(DAY, Opened_Date, Getdate()) AS [Days] " + "FROM New_Issues WHERE (Req_Dept LIKE '%" + RegionChosen() + "%' OR Req_Dept LIKE '" + RegionChosen() + "%' OR Req_Dept LIKE '%" + RegionChosen() + "') " + "AND New_Issues.[Status] = '" + ReportHelper.StatusChosen(StatusComboBox) + "' ORDER BY Priority_Number ASC;"); } }
/*Name: Michael Figueroa * Function Name: StatusQuery * Purpose: This is a method that determines the end of the query in agingQuery() when the StatusComboBox item selected is "All Opened" or "All Closed" * Parameters: None * Return Value: string * Local Variables: None * Algorithm: if All Opened is chosen from StatusComboBox, then all non-closed, non-implemented, non-dropped, non-deffered, not assigned, and completed items are chosen; else, all opened items are chosen * Version: 2.0.0.4 * Date modified: Prior to 1/1/20 * Assistance Received: N/A */ private string StatusQuery() { if (ReportHelper.StatusChosen(StatusComboBox) == "All Opened") { return("AND(New_Issues.[Status] NOT LIKE '%closed%' AND New_Issues.[Status] NOT LIKE '%implemented%' AND New_Issues.[Status] NOT LIKE '%dropped%' " + "AND New_Issues.[Status] NOT LIKE '%deferred%' AND New_Issues.[Status] NOT LIKE '%Not Assigned%' AND New_Issues.[Status] NOT LIKE '%Completed%') "); } else { return("AND(New_Issues.[Status] LIKE '%closed%' OR New_Issues.[Status] LIKE '%implemented%' OR New_Issues.[Status] LIKE '%dropped%' OR New_Issues.[Status] LIKE '%deferred%' OR New_Issues.[Status] NOT LIKE '%Completed%') "); } }
/*Name: Michael Figueroa * Function Name: agingQuery * Purpose: Sets agingQuery that will be used to generate aging report * Parameters: None * Return Value: string query * Local Variables: string query, string statusChosen * Algorithm: calls StatusChosen in order to determine what status is chosen from the ComboBox by the user, then based on the criteria the if-else clause determines the query...Note that SystemComboBox index 0 is "All" * If exception occurs, error message is shown * Version: 2.0.0.4 * Date modified: Prior to 1/1/20 * Assistance Received: N/A */ public string agingQuery() { string query; string statusChosen = ReportHelper.StatusChosen(StatusComboBox); if ((SystemComboBox.SelectedIndex != 0) && ((ReportHelper.StatusChosen(StatusComboBox)) == "All Opened" || (ReportHelper.StatusChosen(StatusComboBox) == "All Closed"))) { query = "SELECT Sys_Impact as [System], New_Issues.[Status], Assigned_To AS[Owner], Category, TFS_BC_HDFS_Num as BID#, Impact, " + "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, ID as ID " + "FROM New_Issues INNER JOIN(SELECT TaskNum, MAX(EntryDate) AS Latest_Status_Update FROM History " + "GROUP BY TaskNum) h1 ON h1.TaskNum = New_Issues.ID WHERE((Category LIKE 'BC%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 180)) " + "OR((Category NOT LIKE 'BC%' AND Impact NOT LIKE '%Not Billed Items%') AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 22))OR(Impact LIKE '%Not Billed Items%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 8)) " + "OR ((Category LIKE '%Strategic Task%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 7)))) AND (Sys_Impact LIKE '%" + SystemChosen() + "%') " + StatusQuery() + " ORDER BY TaskNum ASC; "; } else if (SystemComboBox.SelectedIndex != 0 && ((ReportHelper.StatusChosen(StatusComboBox) != "All Opened") && (ReportHelper.StatusChosen(StatusComboBox) != "All Closed"))) { query = "SELECT Sys_Impact as [System], New_Issues.[Status], Assigned_To AS[Owner], Category, TFS_BC_HDFS_Num as BID#, Impact, " + "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, ID as ID " + "FROM New_Issues INNER JOIN(SELECT TaskNum, MAX(EntryDate) AS Latest_Status_Update FROM History " + "GROUP BY TaskNum) h1 ON h1.TaskNum = New_Issues.ID WHERE((Category LIKE 'BC%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 180)) " + "OR((Category NOT LIKE 'BC%' AND Impact NOT LIKE '%Not Billed Items%') AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 22)) OR (Category LIKE '%Strategic Task%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 7)) OR(Impact LIKE '%Not Billed Items%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 8))) " + "AND New_Issues.[Status] = '" + ReportHelper.StatusChosen(StatusComboBox) + "' AND Sys_Impact LIKE '%" + SystemChosen() + "%' ORDER BY TaskNum ASC; "; } else if (SystemComboBox.SelectedIndex == 0 && (ReportHelper.StatusChosen(StatusComboBox) != "All Opened") && (ReportHelper.StatusChosen(StatusComboBox) != "All Closed")) { query = "SELECT Sys_Impact as [System], New_Issues.[Status], Assigned_To AS[Owner], Category, TFS_BC_HDFS_Num as BID#, Impact, " + "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, ID as ID " + "FROM New_Issues INNER JOIN(SELECT TaskNum, MAX(EntryDate) AS Latest_Status_Update FROM History " + "GROUP BY TaskNum) h1 ON h1.TaskNum = New_Issues.ID WHERE((Category LIKE 'BC%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 180)) " + "OR((Category NOT LIKE 'BC%' AND Impact NOT LIKE '%Not Billed Items%') AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 22)) OR (Category LIKE '%Strategic Task%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 7)) OR (Impact LIKE '%Not Billed Items%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 8))) " + "AND New_Issues.[Status] = '" + ReportHelper.StatusChosen(StatusComboBox) + "' ORDER BY TaskNum ASC; "; } else { query = "SELECT Sys_Impact as [System], New_Issues.[Status], Assigned_To AS[Owner], Category, TFS_BC_HDFS_Num as BID#, Impact, " + "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, ID as ID " + "FROM New_Issues INNER JOIN(SELECT TaskNum, MAX(EntryDate) AS Latest_Status_Update FROM History " + "GROUP BY TaskNum) h1 ON h1.TaskNum = New_Issues.ID WHERE((Category LIKE 'BC%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 180)) " + "OR((Category NOT LIKE 'BC%' AND Impact NOT LIKE '%Not Billed Items%') AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 22)) OR (Category LIKE '%Strategic Task%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 7)) OR(Impact LIKE '%Not Billed Items%' AND(DATEDIFF(day, h1.Latest_Status_Update, CONVERT(date, GETDATE())) > 8))) " + StatusQuery() + " ORDER BY TaskNum ASC;"; } return(query); }
/*Name: Michael Figueroa * Function Name: StatusString * Purpose: To set the [Status] condition in the WHERE clause of the BusinessCases query based on what option is chosen from the ComboBox * Parameters: None * Return Value: string * Local Variables: None * Algorithm: If "All Opened" chosen from StatusComboBox, then the issues that are not closed/implemented/dropped/deferred/or BC Approved are chosen; else, closed issues are * chosen * Version: 2.0.0.4 * Date modified: Prior to 1/1/20 * Assistance Received: N/A */ private string StatusString() { if (ReportHelper.StatusChosen(StatusComboBox) == "All Opened") { return("(New_Issues.[Status] NOT LIKE '%closed%' " + "AND New_Issues.[Status] NOT LIKE '%Implemented%' " + "AND New_Issues.[Status] NOT LIKE '%dropped%' AND New_Issues.[Status] NOT LIKE '%deferred%' AND New_Issues.[Status] NOT LIKE '%BC Approved%') "); } else if (ReportHelper.StatusChosen(StatusComboBox) == "All Closed") { return("(New_Issues.[Status] LIKE '%closed%' " + "OR New_Issues.[Status] LIKE '%Implemented%' " + "OR New_Issues.[Status] LIKE '%dropped%' OR New_Issues.[Status] LIKE '%deferred%' OR New_Issues.[Status] LIKE '%BC Approved%') "); } else { return("(New_Issues.[Status] = '" + ReportHelper.StatusChosen(StatusComboBox) + "') "); } }