tool/Automation/functions/Export-OpportunityResults.ps1
|
function Export-OpportunityResults { <# .SYNOPSIS Export scored results to Excel, CSV, and JSON formats. .DESCRIPTION Reads scoring-results.json and produces: - opportunity-report.xlsx (auto-sized columns, frozen header, band colours) - opportunity-report.csv - opportunity-report.json (flat export) Excel generation uses ImportExcel module. CSV/JSON use PowerShell. .PARAMETER ScoringPath Path to scoring-results.json from the score stage. .PARAMETER OutputPath Path to output/ directory where reports will be written. .OUTPUTS PSCustomObject with files_created. #> [CmdletBinding()] param( [Parameter(Mandatory)] [string]$ScoringPath, [Parameter(Mandatory)] [string]$OutputPath ) # Load scoring results $scoring = Get-Content $ScoringPath -Raw | ConvertFrom-Json # Ensure output directory if (-not (Test-Path $OutputPath -PathType Container)) { New-Item -ItemType Directory -Path $OutputPath -Force | Out-Null } # --- Flatten results for export --- $flatResults = foreach ($result in $scoring.results) { [ordered]@{ Band = $result.band Urgency = $result.urgency 'Final Score' = $result.final_score 'Composite Score' = $result.composite_score 'Recency Penalty' = $result.recency_penalty_applied 'Contact Name' = $result.contact_name 'Contact Email' = $result.contact_email Organisation = $result.organisation Category = ($result.category -join ', ') 'Strategic Fit' = $result.sub_scores.strategic_fit.raw Seniority = $result.sub_scores.seniority.raw 'Engagement Warmth' = $result.sub_scores.engagement_warmth.raw 'Market Activity' = $result.sub_scores.market_activity.raw 'Conversation Stage' = $result.sub_scores.conversation_stage.raw Recency = $result.sub_scores.recency.raw 'Research Confidence' = $result.sub_scores.research_confidence.raw 'Recommended Action' = $result.recommended_action 'Action Rationale' = $result.action_rationale 'Source File' = $result.file_path } } $flatResults = @($flatResults) $filesCreated = @() # --- CSV Export --- $csvPath = Join-Path $OutputPath 'opportunity-report.csv' $flatResults | ForEach-Object { [PSCustomObject]$_ } | Export-Csv -Path $csvPath -NoTypeInformation -Encoding UTF8 $filesCreated += $csvPath Write-Host " CSV: $csvPath" -ForegroundColor Gray # --- JSON Export (flat) --- $jsonPath = Join-Path $OutputPath 'opportunity-report.json' $jsonExport = [ordered]@{ exported_at = (Get-Date).ToString('o') profile = $scoring.metadata.profile_used total = $flatResults.Count results = $flatResults } $jsonExport | ConvertTo-Json -Depth 5 | Set-Content -Path $jsonPath -Encoding UTF8 $filesCreated += $jsonPath Write-Host " JSON: $jsonPath" -ForegroundColor Gray # --- Excel Export via ImportExcel --- $xlsxPath = Join-Path $OutputPath 'opportunity-report.xlsx' try { if (-not (Get-Module ImportExcel -ListAvailable)) { Write-Warning "Excel export skipped: ImportExcel module not installed. Run: Install-Module ImportExcel -Scope CurrentUser" } else { # Remove existing file (Export-Excel appends by default) if (Test-Path $xlsxPath) { Remove-Item $xlsxPath -Force } # Band colour mapping $bandColours = @{ 1 = [System.Drawing.Color]::FromArgb(46, 204, 113) # Green 2 = [System.Drawing.Color]::FromArgb(52, 152, 219) # Blue 3 = [System.Drawing.Color]::FromArgb(243, 156, 18) # Orange 4 = [System.Drawing.Color]::FromArgb(231, 76, 60) # Red 5 = [System.Drawing.Color]::FromArgb(149, 165, 166) # Grey } $excelData = $flatResults | ForEach-Object { [PSCustomObject]$_ } $excel = $excelData | Export-Excel -Path $xlsxPath ` -WorksheetName 'Opportunity Report' ` -AutoSize ` -AutoFilter ` -FreezeTopRow ` -BoldTopRow ` -PassThru $ws = $excel.Workbook.Worksheets['Opportunity Report'] # Style header row $headerStyle = $ws.Cells["1:1"].Style $headerStyle.Font.Color.SetColor([System.Drawing.Color]::White) $headerStyle.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid $headerStyle.Fill.BackgroundColor.SetColor([System.Drawing.Color]::FromArgb(44, 62, 80)) $headerStyle.HorizontalAlignment = [OfficeOpenXml.Style.ExcelHorizontalAlignment]::Center # Apply band-based row colours $bandCol = ($excelData[0].PSObject.Properties.Name).IndexOf('Band') + 1 for ($row = 2; $row -le $ws.Dimension.End.Row; $row++) { $band = $ws.Cells[$row, $bandCol].Value if ($bandColours.ContainsKey([int]$band)) { $rowColour = $bandColours[[int]$band] $lightColour = [System.Drawing.Color]::FromArgb(40, $rowColour.R, $rowColour.G, $rowColour.B) $ws.Cells["$($row):$($row)"].Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid $ws.Cells["$($row):$($row)"].Style.Fill.BackgroundColor.SetColor($lightColour) } } Close-ExcelPackage $excel $filesCreated += $xlsxPath Write-Host " Excel: $xlsxPath" -ForegroundColor Gray } } catch { Write-Warning "Excel export failed: $($_.Exception.Message)" } # --- HTML Report --- try { $htmlMeta = @{ profile = $scoring.metadata.profile_used exported_at = (Get-Date).ToString('o') } $htmlFile = Export-HtmlReport -Results $flatResults -Metadata $htmlMeta -OutputPath $OutputPath $filesCreated += $htmlFile Write-Host " HTML: $htmlFile" -ForegroundColor Gray } catch { Write-Warning "HTML report failed: $($_.Exception.Message)" } [PSCustomObject]@{ files_created = $filesCreated total_results = $flatResults.Count } } |