# encoding: ascii
# api: powershell
# title: Join-Collections
# description: Performs an inner join on two collections of objects which share a common key value.
# version: 1.0
# type: script
# author: Joel Bennett
# license: CC0
# function: Join-Collections
# x-poshcode-id: 1460
# x-derived-from-id: 1461
# x-archived: 2016-07-10T18:53:08
# x-published: 2010-11-04T12:58:00
#
#
#requires -version 2.0

## Version 1.0 First post http://poshcode.org/1459
## Version 1.1 Fixed column uniqueness bug

#.Note
#  Depends on ConvertFrom-HashTable http://poshcode.org/1118
#.Synopsis
#  Performs a inner join on two collections of objects based on a common key column.
#.Description
#  Takes two sets of objects where there are multiple "rows" and where each set has a shared column where the values match, and generates new objects with all the values from each.
#.Parameter GroupOnColumn
#  The name of the property to merge on. Items with the same value in this column will be combined.
#.Parameter FirstCollection
#  The first set of data
#.Parameter FirstJoinColumn
#  The name of the key id column in the first set
#.Parameter SecondCollection
#  The second set of data
#.Parameter SecondJoinColumn
#  The name of the matching key id column in the second set
#  OPTIONAL. Defaults to the same as FirstJoinColum
#.Example
#  Import-CSV data.csv | Pivot-Objects SamAccountName Attribute Value
#
#  Imports csv data containing multiple rows per-record such that a pair of columns named "Attribute" and "Value" are actually different in each row, and contain a name and value pair for attributes you want to add to the output objects.
#
#.Example
# $FirstCollection = @"
#  FirstName,  LastName,   MailingAddress,    EmployeeID
#  John,       Doe,        123 First Ave,     J8329029
#  Susan Q.,   Public,     3025 South Street, K4367143
#"@.Split("`n") | ConvertFrom-Csv                               
#
# $SecondCollection = @"
#  ID,    Week, HrsWorked,   PayRate,  EmployeeID
#  12276, 12,   40,          55,       J8329029
#  12277, 13,   40,          55,       J8329029
#  12278, 14,   42,          55,       J8329029
#  12279, 12,   35,          40,       K4367143
#  12280, 13,   32,          40,       K4367143
#  12281, 14,   48,          40,       K4367143
#"@.Split("`n") | ConvertFrom-Csv                               
#
# Join-Collections $FirstCollection EmployeeID $SecondCollection | ft -auto        
#
#.Notes
#  Author: Joel Bennett

# function Join-Collections {
PARAM(
   $FirstCollection
,  [string]$FirstJoinColumn
,  $SecondCollection
,  [string]$SecondJoinColumn=$FirstJoinColumn
)
PROCESS {

   $properties1 = $FirstCollection[0] | gm -type Properties | Select -Expand Name
   $properties2 = $SecondCollection[0] | gm -type Properties | Where { $Properties1 -notcontains $_.Name } | Select -Expand Name

   foreach($first in $FirstCollection) {
      foreach($second in $SecondCollection | Where{ $_."$SecondJoinColumn" -eq $first."$FirstJoinColumn" } ) {
        [string]$join = $first | gm -type Properties | select -expand Definition | %{($_ -split " ",2)[1]}
        $join += "`n"
        $join += $second | gm -Name $Properties2 -type Properties | select -expand Definition | %{($_ -split " ",2)[1]}
        ConvertFrom-StringData $join | ConvertFrom-Hashtable
      }
   }
}
#}
