Delete empty CartInstance table rows from Andi db earlier than today - x days.

prune_cart.ps1

param (
  [Parameter(Mandatory=$true)]
  [string]$server,
  [Parameter(Mandatory=$true)]
  [string]$dbname,
  [Parameter(Mandatory=$true)]
  [int]$daysToKeep,
  [Parameter(Mandatory=$false)]
  [string]$logsrc = "Andi Solr Update"
)

function logError([string]$logsrc, [string]$msg) {
    # write error msg to Application EventLog
    Write-EventLog -LogName Application -Source $logsrc -EventId 500 -EntryType Error -Message $msg -Category 0
}

function logInfo([string]$logsrc, [string]$msg) {
    # write info msg to Application EventLog
    Write-EventLog -LogName Application -Source $logsrc -EventId 200 -EntryType Information -Message $msg -Category 0
}

function logWarning([string]$logsrc, [string]$msg) {
    Write-EventLog -LogName Application -Source $logsrc -EventId 400 -EntryType Warning -Message $msg -Category 0
}

try {
  Push-Location
  $pre = Invoke-Sqlcmd -ServerInstance $server -query "USE $dbname; select count(0) from CartInstance ci left outer join CartItem c on ci.Id = c.CartInstanceId left outer join [Order] o on ci.Id = o.CartInstanceId where c.Id is null and o.Id is null and ci.Created <= (GetDate() - $daysToKeep)"
  Invoke-Sqlcmd -ServerInstance $server -query "USE $dbname; delete ci from CartInstance ci left outer join CartItem c on ci.Id = c.CartInstanceId left outer join [Order] o on ci.Id = o.CartInstanceId where c.Id is null and o.Id is null and ci.Created <= (GetDate() - $daysToKeep)"
  $post = Invoke-Sqlcmd -ServerInstance $server -query "USE $dbname; select count(0) from CartInstance ci left outer join CartItem c on ci.Id = c.CartInstanceId left outer join [Order] o on ci.Id = o.CartInstanceId where c.Id is null and o.Id is null and ci.Created <= (GetDate() - $daysToKeep)"
  Pop-Location

  $dateAgo = (get-date).AddDays(-$daysToKeep)
  $msg = "Pruned Andi cart instances from $dbname prior to $($dateAgo.ToShortDateString()). `n$($pre.count - $post.count) rows affected."
  logInfo $logsrc $msg
  echo $msg
  exit 0
}
catch [Exception] {
  $ex = $_.Exception
  $msg = "Error pruning Andi cart instances from $dbname. `n`n$ex"
  logError $logsrc $msg
  echo $msg
  exit 1
}