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) + "') ");
}
}
