Avatar of Tek Info
Tek Info
 asked on

Modify Powershell script to report separate lines for a specific condition

I would like to change the attached script to match the Desired Output.

When an "Item" which reports a "Part P on G",
and the same "Item" is found which shares the same "Code", then the "Item" and "Qty" which is reporting the "Part P on G"
must be reported on it's own line,  as shown in the "DesiredOutput" file.


For example in the Current Output file you will see:

3       744  12-12121   Desc2 8      0      1           2020/OC/03=744  

But the Desired Output must show it separately like this.

3       672  12-12121   Desc2 8      0      0           2020/OC/03=672
4        72  12-12121   Desc2 0      0      1           2020/OC/03=72

What change needs to be made to the script tp report it this way?
OldScript.ps1
Source.in
CurrentOutput.out
DesiredOutput.out
PowershellScripting LanguagesCOBOL

Avatar of undefined
Last Comment
Tek Info

8/22/2022 - Mon
oBdA

Here you go:
$inFile = 'C:\Source.in'
$outFile = 'C:\Output.out'
$mailArgs = @{
	To = 'someone@domain.com'
	From = 'someone.else@domain.com'
	Subject = 'Output report'
	SmtpServer = 'smtp.domain.com'
	BodyAsHtml = $true
}
$htmlHead = @'
<style>
	table	{border-width:1px; border-style:solid; border-color:black;}
	th		{border-width:1px; border-style:solid; border-color:black; padding:1px;}
	td		{border-width:1px; border-style:solid; border-color:black; padding:1px;}
</style>
'@
$totalProperties = @(
	'Tot PC Qty'
	'Code 1 Qty'
	'Code 2 Qty'
	'Code 3 Qty'
	'Full P'
	'Part P'
	'Part P on G'
	'Stnd PU'
	'C PU'
	'Extra C PU'
	'Extra Stnd PU'
)
$reportTotalProperties = @(
	'Stnd PU'
	'C PU'
	'Extra C PU'
	'Extra Stnd PU'
)
$removeProperties = @(
	'Ref# / Imp #'
	'Enter Date'
	'Tantmt #'
	'Out From'
	'Out To'
	'L By'
	'Going'
	'Sight # /Control'
	'Code 1 ED'
	'Code 2 ED'
	'Code 3 EDate'
#	'Tot PC Qty'
	'Code 1 Qty'
	'Code 2'
	'Code 2 Qty'
	'Code 3'
	'Code 3 Qty'
	'Stnd PU'
	'C PU'
	'Extra C PU'
	'Extra Stnd PU'
)
$groupProperties = @(
	'Item'
	'Code 1'
	'Code 2'
	'Code 3'
	'Part P on G'
)
$orderProperties = @(
	'Tot PC Qty'
#	'Sight # /Control'
	'Item'
	'Docu'
	'Full P'
	'Part P'
	'Part P on G'
#	'Stnd PU'
#	'C PU'
#	'Extra C PU'
#	'Extra Stnd PU'
	'Code 1'		## Will contain the merged Code properties
#	'Notes'
)
$renameProperties = @{
#	'Sight # /Control' = 'Cnt#'
	'Tot PC Qty' = 'Qty'
	'Code 1' = 'Code'
}

$header = Get-Content -Path $inFile -TotalCount 2 | ConvertFrom-Csv
$ref = $header[0].'Ref# / Imp #'
$date = $header[0].'Enter Date'.Split(' ')[0]
$tantmt = $header[0].'Tantmt #'
$originalProperties = $header[0].psobject.Properties | Select-Object -ExpandProperty Name
$data = Import-Csv -Path $inFile

$data = $data | Group-Object -Property $groupProperties | ForEach-Object {
	If ($_.Count -eq 1) {
		$_.Group
	} Else {
		$subtotal = $_.Group[0]
		ForEach ($prop in ($originalProperties | Where-Object {$totalProperties -contains $_})) {
			$subtotal.$prop = ($_.Group | Measure-Object -Property $prop -Sum).Sum
		}
		If ($originalProperties -contains 'Notes') {
			$subtotal.Notes = ($_.Group | Select-Object -ExpandProperty Notes) -join ', '
		}
		$subtotal
	}
}
$totalAll = @{}
ForEach ($prop in ($originalProperties | Where-Object {$totalProperties -contains $_})) {
	$totalAll[$prop] = ($data | Measure-Object -Property $prop -Sum).Sum
}

ForEach ($row in $data) {
	$codes = @()
	If ($row.'Code 1') {$codes += "$($row.'Code 1')=$($row.'Code 1 Qty')"}
	If ($row.'Code 2') {$codes += "$($row.'Code 2')=$($row.'Code 2 Qty')"}
	If ($row.'Code 3') {$codes += "$($row.'Code 3')=$($row.'Code 3 Qty')"}
	If ($codes) {$row.'Code 1' = $codes -join ', '}
}

$properties = @('Line#') + $orderProperties + ($originalProperties | Where-Object {($removeProperties -notcontains $_) -and ($orderProperties -notcontains $_)})
$separator = '' | Select-Object -Property $properties
$total = '' | Select-Object -Property $properties
$total.($properties[0]) = 'Totals:'
ForEach ($prop in ($properties | Where-Object {$totalProperties -contains $_})) {
	$total.$prop = $totalAll[$prop]
}
$totalP = 0
$reportTotalProperties | ForEach-Object {$totalP += $totalAll[$_]}

$i = 1
$summary = $data | Select-Object -Property $properties | ForEach-Object {$_.'Line#' = $i++; $_}
$summary = $summary + $separator + $total

$newProperties = $properties
ForEach ($key in $renameProperties.Keys) {
	$newProperties = $newProperties -replace "^$($key)$", $renameProperties[$key]
}
$summary = $summary | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | ConvertFrom-Csv -Header $newProperties

$newProperties | ForEach-Object {
	$summary[-2].$_ = '=' * $_.Length
}

@"
General Report

text here..

Ref#: $($ref)
Date: $($date)
Tantmt#: $($tantmt)

text here..

$($summary | Format-Table -Property * -AutoSize | Out-String -Width 1024)
$(($reportTotalProperties | ForEach-Object {"Total $($_): $($totalAll[$_])"}) -join "`r`n")
Total Pallets: $($totalP)

text here.. 

Signature:           ________________________

Date:                ________________________
"@ | Set-Content -Path $outFile

$htmlArgs = @{
	PreContent = "<H3>Ref#: $($ref)</H3><H3>Date: $($date)</H3><H3>Tantmt#: $($tantmt)</H3>"
	PostContent = "<H3>$(($reportTotalProperties | ForEach-Object {"Total $($_): $($totalAll[$_])"}) -join '<br />')<br />Total Pallets: $($totalP)</H3>"
}
$body = ($summary | ConvertTo-Html -Head $htmlHead @htmlArgs) -join "`r`n"
# Send-MailMessage @mailArgs -Body $body -Attachments $outFile

Open in new window

ASKER CERTIFIED SOLUTION
oBdA

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Michael B. Smith

*snort*

At this point you should be using RPG or COBOL. :-)
Tek Info

ASKER
@oBdA
Excellent work as usual.  Many thanks.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy