Synchronize PrestaShop using webservices API and PowerShell

The code below uploads to a PrestaShop catalog data fetched from an ERP. The code is generic up to the ### ERP specific section; ERP dependend SQL queries fetch data that is sent to the products, images/products and specific_prices PrestaShop webservices end points; note, the name of the query fields must match exactly the names of the XML elements to be updated, unmatched names are ignored, also, product categories, groups and tax rates should have already been defined. The code generates a log file that reports all the REST calls made during a run. The SQL queries in the code are specific to EasyFatt.

Tested with PowerShell 7.

# PrestaShop Web Services
$SHOP_API = 'https://www.acme.com/api';
$KEY = 'RH3654M4QTHZB5VY2KVJEXBR2INZICAL'; # The key should allow access to the managed resources
$HEADERS = @{
	Authorization = ("Basic {0}" -f [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $KEY, 'password')))) 
};
$LANGUAGE = 'it';

# Company ERP
$DSN = 'DSN=ERP'; # Using ODBC
$IMAGES = '\\server\images'; # Local images

$LOG = $PSCommandPath.Replace('.ps1', "_$(Get-Date -Format 'dddd').log");
('# Start: ' + ((Get-Date).toString('yyyy/MM/dd HH:mm:ss'))) > $LOG;

# $LASTRUN may be used to conditionally sync products and images, the initial date is arbitrary
$LASTRUN = Get-Date -Date '2020/01/01';
if (Test-Path "$PSScriptRoot\lastrun") {
  $LASTRUN = (Get-Item "$PSScriptRoot\lastrun").LastWriteTime;
}

function Write-Log {
  [CmdletBinding()]
  Param(
    [String] $message,
    [Switch] $NoTimeStamp
  )

  if ($NoTimeStamp) {
    Add-Content $LOG -Value (' ' + $message) -NoNewLine;
  }
  else {
    Add-Content $LOG -Value '';
    Add-Content $LOG -Value ((Get-Date).toString('yyyy/MM/dd HH:mm:ss ') + $message) -NoNewLine;
  }
}

function Get-ResourceId {
  [CmdletBinding()]
  param(
    [String] $resource,
    [String] $filters # Syntax: filter[]={&filter[]=...}
  )
  return (Invoke-RestMethod -Method Get -Uri "${SHOP_API}/${resource}?display=[id]&${filters}" -Headers $HEADERS).DocumentElement.selectSingleNode('//id').InnerText;
}
$LANGUAGE = Get-ResourceId 'languages' "filter[iso_code]=$LANGUAGE";

function Get-Data {
  [CmdletBinding()]
  param(
    [String] $query = $(throw 'query is required.')
  )

  $cnn = New-Object System.Data.Odbc.OdbcConnection;
  $cnn.ConnectionString = $DSN;
  $cnn.Open();
  $cmd = New-Object System.Data.ODBC.OdbcCommand($query, $cnn);
  $ds = New-Object System.Data.DataSet;
  (New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($ds) | Out-Null;
  $cnn.Close();
  
  return $ds.Tables[0];
}

function Expand-Value {
  [CmdletBinding()]
  param(
    [String] $name,
    [String] $value,
    [System.Xml.XmlDocument] $currentData
  )

  switch ($name) {
    'id_category_default' {
      [String] $xml = '';
      $value.Split(',') | ForEach-Object {
        if ($xml -eq '') {
          $value = (Get-ResourceId 'categories' ('filter[name]=' + $_.Trim()));
        }
        $xml += '<![CDATA[' + (Get-ResourceId 'categories' ('filter[name]=' + $_.Trim())) + ']]>';
      }
      $currentData.DocumentElement.SelectSingleNode('//associations/categories').InnerXml = $xml;
      break;
    }
  }
  return $value;
}

function Sync-Values {
  [CmdletBinding()]
  param(
    [System.Xml.XmlDocument] $currentData, 
    [System.Data.DataRow] $data,
    [System.Xml.XmlDocument] $sysnopsis
  )

  # Remove from $currentData readOnly elements
  $synopsis.DocumentElement.SelectNodes('//*[@readOnly]') | ForEach-Object { 
    $element = $currentData.DocumentElement.selectSingleNode('//' + $_.Name);
    if ($element) {
      $element.ParentNode.RemoveChild($element) | Out-Null;
    }
  }
  # Pre-Fill empty $currentData required elements
  $synopsis.DocumentElement.SelectNodes('//*[@required]') | ForEach-Object { 
    $element = $currentData.DocumentElement.selectSingleNode('//' + $_.Name);
    if ($element -and $element.InnerXml -eq '') {
      $element.InnerXml = '<![CDATA[0]]>';
    }
  }

  $data.Table.Columns | ForEach-Object {
    $value = $data[$_];

    [System.Xml.XmlElement]$element = $currentData.DocumentElement.selectSingleNode('/*/*/' + $_.ColumnName.ToLower());
    if ($element) {
      switch ($synopsis.DocumentElement.selectSingleNode('//' + $element.Name).GetAttribute('format').ToString()) {
        'isLinkRewrite' {
          $value = ($value.split('-')[0].Trim().ToLower() -replace '[^\p{L}\p{Nd} \-_]', '').Replace(' ', '_');
          break; 
        }
        'isDate' {
          if ($value) {
            $value = (Get-Date -Date $value -Format 'yyyy-MM-dd HH:mm:ss');
          }
          else {
            $value = '0000-00-00 00:00:00';
          }
          break;
        }
      }

      if ($element.selectSingleNode("language[@id=$LANGUAGE]")) {
        $element.selectSingleNode("language[@id=$LANGUAGE]").InnerXml = '<![CDATA[' + (Expand-Value $element.Name $value $currentData) + ']]>';
      }
      else {
        $element.InnerXml = '<![CDATA[' + (Expand-Value $element.Name $value $currentData) + ']]>';
      }
    }
  }

  return $currentData;
}

function Invoke-WebService {
  [CmdletBinding()]
  param(
    [String] $resource,
    [String] $id,
    [System.Data.DataRow] $data,
    [System.Xml.XmlDocument] $synopsis
  )

  if ($id) {
    # UPDATE
    [System.Xml.XmlDocument]$xml = (Invoke-RestMethod -Method Get -Uri "$SHOP_API/$resource/$id" -Headers $HEADERS);

    try {
      Write-Log "Put: $SHOP_API/$resource/$id" -NoTimeStamp;
      $xml = Sync-Values $xml $data $synopsis;
      $body = [System.Text.Encoding]::UTF8.GetBytes($xml.InnerXml);
      return (Invoke-RestMethod -Method Put -Uri "$SHOP_API/$resource" -ContentType 'text/xml' -Body $body -Headers $HEADERS);
    }
    catch {
      Write-Log $_.ToString();
      $outfile = $resource + '_put_' + (Get-Date).toString('yyyy-MM-dd_T_HH-mm-ss') + '.xml';
      $body > $outfile;
    }

  }
  else {
    # INSERT
    [System.Xml.XmlDocument]$xml = (Invoke-RestMethod -Method Get -Uri "$SHOP_API/${resource}?schema=blank" -Headers $HEADERS);
    
    try {
      Write-Log "Post: $SHOP_API/$resource" -NoTimeStamp;
      $element = $xml.DocumentElement.selectSingleNode('/*/*/id');
      $element.ParentNode.RemoveChild($element) | Out-Null;
      $xml = Sync-Values $xml $data $synopsis;
      $body = [System.Text.Encoding]::UTF8.GetBytes($xml.InnerXml);
      return (Invoke-RestMethod -Method Post -Uri "$SHOP_API/$resource" -ContentType 'text/xml' -Body $body -Headers $HEADERS);
    }
    catch {
      Write-Log $_.ToString();
      $outfile = $resource + '_post_' + (Get-Date).toString('yyyy-MM-dd_T_HH-mm-ss') + '.xml';
      $body > $outfile;
    }
  }
  return $null;
}

### ERP specific section

# Manage products
$sql = @"
  select
    Extra3 as id_category_default, 
    PathImmagine_Import,
    CodIva as id_tax_rules_group,
    CodArticolo as reference,
    DimImballoX as width,
    DimImballoY as depth,
    DimImballoZ as height,
    PesoLordo as weight,
    PrezzoNetto1 as price,
    1 as active,
    1 as state,
    1 as available_for_order,
    1 as minimal_quantity,
    Desc as link_rewrite,
    Desc as name,
    DescHtml as description,
    Desc as description_short,
    Desc as meta_description,
    CodBarre as ean13,
    cast('NOW' as timestamp) as date_upd
  from
    TArticoli
  where
    PubblicaSuWeb = 1
"@
[System.Xml.XmlDocument]$synopsis = (Invoke-RestMethod -method Get -Uri "$SHOP_API/products?schema=synopsis" -Headers $HEADERS);
Get-Data $sql | ForEach-Object {
  $_['name'] = ($_['name'] -replace '^(.+?)(-|,).*', '$1').Trim();
  $_['id_tax_rules_group'] = Get-ResourceId 'tax_rule_groups' 'filter[name]=IT Standard Rate (22%)';

  Write-Log ('Sync: ' + $_['reference']);
  $xml = (Invoke-WebService -resource 'products' -id (Get-ResourceId 'products' ('filter[reference]=' + $_['reference'])) -data $_ -synopsis $synopsis);

  if ($xml -and $_['PathImmagine_Import']) {
    $id = $xml.DocumentElement.SelectSingleNode('//id').InnerText;
    $_['PathImmagine_Import'] = $IMAGES + '\' + $_['PathImmagine_Import'];
    try {
      if (Get-ChildItem $_['PathImmagine_Import'] | Where-Object { $_.LastWriteTime -gt $LASTRUN }) {
        $form = @{
          'image' = Get-Item -Path $_['PathImmagine_Import']
        };
        (Invoke-RestMethod -Method Post -Uri "$SHOP_API/images/products/$id" -From $form -Headers $HEADERS) | Out-Null;
      }
    }
    catch {
      Write-Log $_.ToString();
    }
  }
}

# Manage price list
$sql = @"
  select
    CodArticolo,
    CodArticolo as id_product,
    PrezzoNetto2 as price,
    'Cliente PRO' as id_group
  from
    TArticoli
  where
    PubblicaSuWeb = 1
"@
[System.Xml.XmlDocument]$synopsis = (Invoke-RestMethod -method Get -Uri "$SHOP_API/specific_prices?schema=synopsis" -Headers $HEADERS);
Get-Data $sql | ForEach-Object {
  $_['id_product'] = Get-ResourceId 'products' ('filter[reference]=' + $_['id_product']);
  if ($_['id_product'].ToString() -ne '') {
    $_['id_group'] = Get-ResourceId 'groups' ('filter[name]=' + $_['id_group']);
    $specificPrice = Get-ResourceId 'specific_prices' ('filter[id_product]=' + $_['id_product'] + '&filter[id_group]=' + $_['id_group']);
    if ($_['price'].ToString() -eq '') {
      if ($specificPrice) {
        Write-Log "Delete: $SHOP_API/specific_prices/$specificPrice";
        (Invoke-RestMethod -Method Delete -Uri "$SHOP_API/specific_prices/$specificPrice" -Headers $HEADERS) | Out-Null;
      }
    }
    else {
      Write-Log ('Sync: ' + $_['CodArticolo']);
      (Invoke-WebService -resource 'specific_prices' -id $specificPrice -data $_ -synopsis $synopsis) | Out-Null;
    }
  }
}

'' > "$PSScriptRoot\lastrun";

 Last update 2020-03-22 07:09