pixiReportTurnover · Pixi API documentation

Dashboard report that sums Total_Net of invoices (Invoices) into four turnover figures and returns them as one row.

Date handling

toDate defaults to the current date and time. Its time part matters: the given day and the given month are counted only up to toDate, not to the end of that day.
fromDate defaults to the first day of the month before toDate. If fromDate is later than toDate, it is set to toDate.
Only invoices with InvDate between the start of the previous month and toDate are considered at all.

Shop filter An invoice is counted only if its customer (CustRef) has at least one address in Addresses with AddrShopID = ShopID. An empty or NULL ShopID includes all invoices that have any address.

The four figures

TurnoverNetGivenDate: if fromDate is in a different month than toDate, invoices from the start of the toDate day up to toDate; if fromDate is on the same day as toDate, invoices from fromDate to toDate.
TurnoverNetDayBefore: if fromDate is in a different month, the whole day before toDate; additionally, if fromDate falls on the day before toDate, invoices from fromDate to the end of that day.
TurnoverNetGivenMonth: if fromDate is in a different month, from the 1st of the toDate month up to toDate; if in the same month, from fromDate up to toDate.
TurnoverNetPreviousMonth: if fromDate is before the previous month, the whole previous month; if fromDate is in the previous month, from fromDate to the end of the previous month.

With the default fromDate the result is: today up to now, yesterday, month to date and the full previous month.

Currency If the setting MultiCurrencyEnabled is 1, each invoice amount is divided by the exchange rate from pixi.ExchRate for the database exchange-rate source (DatabaseCurrencyExchRateSourceId) and home currency, matched by exchange-rate date and currency of the invoice's rate. dbCurrency then contains the database currency code. Otherwise the amounts are summed as stored and dbCurrency is NULL.

Notes

All reads use NOLOCK; figures may include uncommitted data.
The values are returned as strings (varchar(30)). A figure is NULL (element missing in the row) if no invoice matches its period, e.g. TurnoverNetGivenDate and TurnoverNetDayBefore are NULL when fromDate is in the same month as toDate but neither on that day nor on the day before. TurnoverNetPreviousMonth is NULL when fromDate lies in the current month.
A fromDate earlier than the start of the previous month has no extra effect, because older invoices are never loaded.
In multi-currency mode, invoices without a matching exchange rate record (or without ExchRateId) are silently excluded because of inner joins. The number of decimal places in the string depends on the data type of ExchRate, which is not visible here.
If the setting MultiCurrencyEnabled is missing (NULL), the code still takes the conversion branch, but dbCurrency is returned as NULL.
On any SQL error the procedure only prints the message and returns -1; no result set is returned.
The temporary table is dropped via util.TempTableDrop (definition not attached).

Tables used

Invoices · Read Addresses · Read Settings · Read Currency · Read ExchRate · Read

Parameter	Type	Default	Description
`fromDate`	datetime dateTime	NULL	Start of the evaluation period. Defaults to the first day of the month before `toDate`; set to `toDate` if later than `toDate`. Determines which period each figure covers (see description). Example: `2024-05-01T00:00:00`
`toDate`	datetime dateTime	NULL	Reference date and time (end of the period). Defaults to the current date and time. Example: `2024-06-15T18:00:00`
`ShopID`	varchar(3) string	–	Shop filter: only invoices of customers with an address of this shop are counted. Empty means all shops. Example: `ABC`

Result set 1 · Always, unless an SQL error occurs

One row with the four net turnover figures and the database currency code.

Column	Type	Description
`TurnoverNetGivenDate` Nullable	varchar(30) string	Net turnover on the given day (or from `fromDate` to `toDate` if both are on the same day).
`TurnoverNetDayBefore` Nullable	varchar(30) string	Net turnover on the day before `toDate`.
`TurnoverNetGivenMonth` Nullable	varchar(30) string	Net turnover of the month of `toDate` up to `toDate` (or from `fromDate` if in the same month).
`TurnoverNetPreviousMonth` Nullable	varchar(30) string	Net turnover of the previous month (or from `fromDate` to its end if `fromDate` lies in it).
`dbCurrency` Nullable	varchar(3) string	Database (home) currency code when `MultiCurrencyEnabled` = 1, otherwise NULL.

Response example

<SqlRowSet1>
  <row>
    <TurnoverNetGivenDate>1523.40</TurnoverNetGivenDate>
    <TurnoverNetDayBefore>4210.75</TurnoverNetDayBefore>
    <TurnoverNetGivenMonth>58320.10</TurnoverNetGivenMonth>
    <TurnoverNetPreviousMonth>112845.60</TurnoverNetPreviousMonth>
    <dbCurrency>EUR</dbCurrency>
  </row>
</SqlRowSet1>

POST https://apigateway.descartes.com/tms/pixi/
Content-Type: text/xml; charset=utf-8
Authorization: Basic BASE64(USERNAME:PASSWORD)

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiReportTurnover>
      <!-- <fromDate>2024-05-01T00:00:00</fromDate> -->
      <!-- <toDate>2024-06-15T18:00:00</toDate> -->
      <!-- <ShopID>ABC</ShopID> -->
    </ns1:pixiReportTurnover>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

curl -X POST 'https://apigateway.descartes.com/tms/pixi/' \
  --user 'USERNAME:PASSWORD' \
  --header 'Content-Type: text/xml; charset=utf-8' \
  --data-binary @- <<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiReportTurnover>
      <!-- <fromDate>2024-05-01T00:00:00</fromDate> -->
      <!-- <toDate>2024-06-15T18:00:00</toDate> -->
      <!-- <ShopID>ABC</ShopID> -->
    </ns1:pixiReportTurnover>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
XML

<?php

$soapClient = new SoapClient(null, [
    'login' => 'USERNAME',
    'password' => 'PASSWORD',
    'location' => 'https://apigateway.descartes.com/tms/pixi/',
    'uri' => 'https://apigateway.descartes.com/tms/pixi/',
]);

$arguments = [
    // 'fromDate' => '2024-05-01T00:00:00',
    // 'toDate' => '2024-06-15T18:00:00',
    // 'ShopID' => 'ABC',
];

$soapArguments = [];
foreach ($arguments as $name => $value) {
    $soapArguments[] = new SoapVar($value, XSD_STRING, '', '', $name);
}

$response = $soapClient->__soapCall('pixiReportTurnover', $soapArguments);