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' } |