examples/03-Sales-Database.ps1

# 03 - A real little sales database: Customers and Orders with primary keys, a unique index on the customer
# e-mail, a relationship between the tables (Access draws the join line), and a sales report per customer.
# Shows that kAccess keeps your data correct: a duplicate e-mail is refused and NOTHING of that batch is written.
Import-Module kAccess
$Folder = Join-Path ([IO.Path]::GetTempPath()) 'kAccess-examples'
New-Item $Folder -ItemType Directory -Force | Out-Null
$Db = Join-Path $Folder 'Sales.accdb'
Remove-Item $Db -ErrorAction Ignore
New-kAccessDatabase $Db | Out-Null

New-kAccessTable $Db Customers -Column @{ Name = 'CustomerId'; Type = 'LONG'; AutoNumber = $true },
    @{ Name = 'Company'; Type = 'TEXT'; Length = 80 }, @{ Name = 'Email'; Type = 'TEXT'; Length = 120 }, @{ Name = 'Country'; Type = 'TEXT'; Length = 40 } `
    -Index @{ Name = 'PrimaryKey'; Columns = @('CustomerId'); PrimaryKey = $true }, @{ Name = 'Email'; Columns = @('Email'); Unique = $true }
New-kAccessTable $Db Orders -Column @{ Name = 'OrderId'; Type = 'LONG'; AutoNumber = $true },
    @{ Name = 'CustomerId'; Type = 'LONG' }, @{ Name = 'OrderDate'; Type = 'SHORT_DATE_TIME' }, @{ Name = 'Amount'; Type = 'MONEY' }, @{ Name = 'Paid'; Type = 'BOOLEAN' } `
    -Index @{ Name = 'PrimaryKey'; Columns = @('OrderId'); PrimaryKey = $true }, @{ Name = 'CustomerId'; Columns = @('CustomerId') }
$Relation = New-kAccessRelationship $Db -PrimaryTable Customers -PrimaryColumn CustomerId -SecondaryTable Orders -SecondaryColumn CustomerId
"Relationship $Relation created"

@(
    @{ Company = 'Contoso'; Email = 'buy@contoso.com'; Country = 'Sweden' }
    @{ Company = 'Fabrikam'; Email = 'orders@fabrikam.com'; Country = 'Germany' }
    @{ Company = 'Northwind'; Email = 'sales@northwind.com'; Country = 'USA' }
) | Add-kAccessRow $Db Customers

$Random = [Random]::new(7)
1..300 | ForEach-Object {
    @{ CustomerId = $Random.Next(1, 4); OrderDate = (Get-Date).Date.AddDays(-$Random.Next(0, 365))
       Amount = [decimal] ($Random.Next(500, 50000) / 10); Paid = $Random.Next(0, 5) -gt 0 }
} | Add-kAccessRow $Db Orders

# The unique index guards the data: this batch has one duplicate e-mail, so neither row is written.
try {
    @(@{ Company = 'Adventure Works'; Email = 'hi@adventure-works.com'; Country = 'UK' },
      @{ Company = 'Contoso again'; Email = 'buy@contoso.com'; Country = 'Sweden' }) | Add-kAccessRow $Db Customers
} catch { "Refused as it should be: $($_.Exception.Message)" }
"Customers now: $(@(Get-kAccessRow $Db Customers).Count)"

# Sales per customer: a join and a group in plain PowerShell.
$Customers = @{}
Get-kAccessRow $Db Customers | ForEach-Object { $Customers[$_.CustomerId] = $_ }
Get-kAccessRow $Db Orders | Group-Object CustomerId | ForEach-Object {
    $Unpaid = @($_.Group | Where-Object { -not $_.Paid })
    [pscustomobject] @{
        Company = $Customers[[int] $_.Name].Company
        Country = $Customers[[int] $_.Name].Country
        Orders  = $_.Count
        Total   = ($_.Group | Measure-Object Amount -Sum).Sum
        Unpaid  = ($Unpaid | Measure-Object Amount -Sum).Sum
    }
} | Sort-Object Total -Descending | Format-Table Company, Country, Orders, @{ n = 'Total'; e = { '{0:n2}' -f $_.Total }; a = 'right' }, @{ n = 'Unpaid'; e = { '{0:n2}' -f $_.Unpaid }; a = 'right' }