private/steps/Update-JiraIssues.ps1
|
function Update-JiraIssues { [CmdletBinding()] param ( # The JQL to use when retrieving issues to update [Parameter(Mandatory,Position=0)] [string] $Jql, # Keys of projects to be obfuscated [Parameter(Position=1)] [string[]] $Obfuscate, # The ID value of the refresh underway [Parameter(Mandatory,Position=2)] [int] $RefreshId, # The sql instance to update data in [Parameter(Mandatory,Position=3)] [string] $SqlInstance, # The sql database to update data in [Parameter(Mandatory,Position=4)] [string] $SqlDatabase, # The schema to use when updating data [Parameter(Position=5)] [string] $SchemaName="dbo", # Synchronization options to override the defaults [Parameter()] [hashtable] $SyncOptions ) begin { Write-Verbose "Updating Issues" $issueTable = "tbl_stg_Jira_Issue" $sprintTable = "tbl_stg_Jira_Sprint" $changelogTable = "tbl_stg_Jira_Changelog" $issueTypeTable = "tbl_stg_Jira_Issue_Type" $issueSprintTable = "tbl_stg_Jira_Issue_Sprint" $issueComponentTable = "tbl_stg_Jira_Issue_Component" $issueFixVersionTable = "tbl_stg_Jira_Issue_Fix_Version" $issueLabelTable = "tbl_stg_Jira_Issue_Label" $issueLinkTable = "tbl_stg_Jira_Issue_Link" #results arrays $allChangelogs = @() $allIssues = @() $allIssueTypes = @() $allSprints = @() # arrays to hold one to many relationship information $issueSprints = @() $issueComponents = @() $issueFixVersions = @() $issueLabels = @() $issueLinks = @() #looping variables $startAt = 0 $lastPageReached = $false #options $syncChangelogs = $null -ne $SyncOptions -and $SyncOptions.ContainsKey("Changelogs") -and $SyncOptions.Changelogs -ne $false } process { Write-Verbose "Getting Issues using JQL $Jql" if (!$syncChangelogs) { Write-Verbose "Skipping Changelogs" } do { #keep current list of all Ids $idList = $allIssues | ForEach-Object { $_.Issue_Id } #get results Write-Verbose ("Getting Issue results $startAt to " + [string]($startAt + 100)) $searchParams = @{ Jql = $jql GET = $true MaxResults = 100 StartAt = $startAt } if ($syncChangelogs) { $searchParams.Add("Expand","changelog") } $result = Invoke-JiraSearchIssues @searchParams #if there were results, process them if ($result.issues.Count -ne 0) { #parse issue results into objects (and grab other objects along the way) $allIssues += $result.issues | Where-Object { $idList -notcontains $_.id } | ForEach-Object { #capture convenience variables $issue = $_ $issueId = $issue.id $fields = $issue.fields $issueProjectKey = $fields.project.key # create and keep issue component mappings if ($fields.components -and $fields.components.Count -gt 0) { $issueComponents += $fields.components | ForEach-Object { $_.id } | Read-JiraIssueComponent -IssueId $issueId -RefreshId $refreshId } # create and keep issue fix version mappings if ($fields.fixVersions -and $fields.fixVersions.Count -gt 0) { $issueFixVersions += $fields.fixVersions | ForEach-Object { $_.id } | Read-JiraIssueVersion -IssueId $issueId -RefreshId $refreshId } # create and keep issue type information, if we don't have it already if (($allIssueTypes | ForEach-Object { $_.Issue_Type_Id }) -notcontains $fields.issueType.id) { $allIssueTypes += Read-JiraIssueType -Data $fields.issueType -RefreshId $refreshId } # capture label data if ($fields.labels -and $fields.labels.Count -gt 0) { $issueLabels += $fields.labels | Read-JiraIssueLabel -IssueId $issueId -RefreshId $refreshId } #capture issue link data if ($fields.issueLinks -and $fields.issueLinks.Count -gt 0) { #get a list of parsed relationships $tempLinks = $fields.issueLinks | Read-JiraIssueLink -IssueId $issueId -IssueKey $issue.key -RefreshId $refreshId #loop through the list we parsed and check to make sure we don't already have it from the other issue in the link #if we don't, added it to the master list foreach($link in $tempLinks) { $count = ($issueLinks | Where-Object { ($_.Out_Issue_Id -eq $link.Out_Issue_Id) -and ($_.In_Issue_Id -eq $link.In_Issue_Id) -and ($_.Link_Type_Id -eq $link.Link_Type_Id) }).Length if ($count -eq 0) { $issueLinks += $link } } } # create and keep sprint information $sprints = $fields.customfield_10127 if ($sprints -and $sprints.Count -gt 0) { #parse sprints $sprintList = $sprints | Where-Object { $_ } | Read-JiraSprint -RefreshId $refreshId #add new ones to the master list $currSprintIds = $allSprints | ForEach-Object { $_.Sprint_Id } $allSprints += $sprintList | Where-Object { $currSprintIds -notcontains $_.Sprint_Id } #add to the list of issue sprint mappings $issueSprints += $sprintList | ForEach-Object { [int]$_.Sprint_Id } | Read-JiraIssueSprint -IssueId $issueId -RefreshId $refreshId } # capture changelog data if ($syncChangelogs -and $issue.changelog.histories -and $issue.changelog.histories.Count -gt 0) { $allChangelogs += $issue.changelog.histories | Where-Object { $_ } | Read-JiraChangelog -IssueId $issueId -RefreshId $RefreshId } # create and return issue object $readSplat = @{ Data = $issue RefreshId = $refreshId Obfuscate = $Obfuscate -contains $issueProjectKey } Read-JiraIssue @readSplat } } else { $lastPageReached = $true } #iterate the start counter $startAt += $result.issues.Count #check to see if we're at the end if ($idList.Count -eq $result.total) { $lastPageReached = $true } #keep going unless we've hit the last page of results } while (!$lastPageReached) } end { $issueTypesCount = $allIssueTypes.Count Write-Verbose "Writing $issueTypesCount Issue Type record(s) to staging table" $allIssueTypes | Write-SqlTableData -ServerInstance $sqlInstance -DatabaseName $sqlDatabase -SchemaName $schemaName -TableName $issueTypeTable $sprintCount = $allSprints.Count Write-Verbose "Writing $sprintCount Sprint record(s) to staging table" $allSprints | Write-SqlTableData -ServerInstance $sqlInstance -DatabaseName $sqlDatabase -SchemaName $schemaName -TableName $sprintTable if ($syncChangelogs) { $clCount = $allChangelogs.Count Write-Verbose "Writing $clCount Changelog record(s) to staging table" $allChangelogs | Write-SqlTableData -ServerInstance $sqlInstance -DatabaseName $sqlDatabase -SchemaName $schemaName -TableName $changelogTable } $issueCount = $allIssues.Count Write-Verbose "Writing $issueCount Issue record(s) to staging table" $allIssues | Write-SqlTableData -ServerInstance $SqlInstance -DatabaseName $SqlDatabase -SchemaName $SchemaName -TableName $issueTable $labelCount = $issueLabels.Count Write-Verbose "Writing $labelCount Issue Label record(s) to staging table" $issueLabels | Write-SqlTableData -ServerInstance $sqlInstance -DatabaseName $sqlDatabase -SchemaName $schemaName -TableName $issueLabelTable $linksCount = $issueLinks.Count Write-Verbose "Writing $linksCount Issue Link record(s) to staging table" $issueLinks | Write-SqlTableData -ServerInstance $sqlInstance -DatabaseName $sqlDatabase -SchemaName $schemaName -TableName $issueLinkTable $issSprintCount = $issueSprints.Count Write-Verbose "Writing $issSprintCount Issue-Sprint Mapping record(s) to staging table" $issueSprints | Write-SqlTableData -ServerInstance $sqlInstance -DatabaseName $sqlDatabase -SchemaName $schemaName -TableName $issueSprintTable $issCompCount = $issueComponents.Count Write-Verbose "Writing $issCompCount Issue-Component Mapping record(s) to staging table" $issueComponents | Write-SqlTableData -ServerInstance $sqlInstance -DatabaseName $sqlDatabase -SchemaName $schemaName -TableName $issueComponentTable $issFixCount = $issueFixVersions.Count Write-Verbose "Writing $issFixCount Issue-Version Mapping record(s) to staging table" $issueFixVersions | Write-SqlTableData -ServerInstance $sqlInstance -DatabaseName $sqlDatabase -SchemaName $schemaName -TableName $issueFixVersionTable } } |