pixiReportScanInPerTimeQuant

Builds a time series of scan-in counts for dashboards.

Defaults are applied: if startDateTime is NULL, the start of the current day (midnight) is used; if endDateTime is NULL, the current date and time is used; if timePeriod is passed as NULL, it becomes 1 (the declared default when the parameter is omitted is 2).
The range from startDateTime to endDateTime is split into consecutive slots of timePeriod hours (fractions such as 0.5 for 30 minutes are possible). The last slot is cut off at endDateTime.
For each slot, the rows in orderHistory with status = 'EIN' and a StatusChangeDate between slot start and slot end (both inclusive) are counted.
If shopID is empty or NULL, all shops are counted; otherwise only history rows of that shop.
One row per slot is returned in chronological order, including slots with a count of 0.

If startDateTime is not before endDateTime, the result set is empty.

Notes

orderHistory is read WITH (NOLOCK); counts may include uncommitted data.
Because BETWEEN includes both boundaries, a history entry exactly on a slot boundary is counted in two adjacent slots.
startTimePeriod and endTimePeriod are strings produced by CONVERT(VARCHAR, …) with the default style (e.g. Jan 15 2024 8:00AM); seconds are not shown and the format depends on the server language. Parse them accordingly or compute the slot times on the client side.
The shopID column echoes the passed parameter, so it is empty (or NULL) when all shops are counted.
Pitfall: a timePeriod of 0 or a negative value makes the loop never end; always pass a positive value.
Large ranges with small slots run one count query per slot and can be slow.

Tables used

orderHistory · Read

Parameter	Type	Default	Description
`startDateTime`	datetime dateTime	NULL	Start of the evaluated range. NULL means the start of the current day (00:00). Example: `2024-01-15T06:00:00`
`endDateTime`	datetime dateTime	NULL	End of the evaluated range. NULL means the current date and time. Example: `2024-01-15T18:00:00`
`timePeriod`	float decimal	2	Length of one time slot in hours; fractions are allowed. Explicit NULL is treated as 1. Must be greater than 0. Example: `1`
`shopID`	varchar(3) string	–	Shop whose scan-ins are counted. Empty or NULL counts all shops. Example: `SHP`

Result set 1 · Always; empty if `startDateTime` is not before `endDateTime`.

One row per time slot in chronological order with the number of scan-ins.

Column	Type	Description
`shopID` Nullable	varchar(3) string	Value of the `shopID` parameter as passed (empty when all shops are counted).
`SUMScanIn` Nullable	int	Number of `orderHistory` entries with status `EIN` in this slot.
`startTimePeriod` Nullable	varchar(50) string	Start of the slot as text in SQL Server default datetime format (e.g. `Jan 15 2024 6:00AM`).
`endTimePeriod` Nullable	varchar(50) string	End of the slot as text in SQL Server default datetime format; the last slot ends at `endDateTime`.

Response example

<SqlRowSet1>
  <row>
    <shopID>SHP</shopID>
    <SUMScanIn>42</SUMScanIn>
    <startTimePeriod>Jan 15 2024  6:00AM</startTimePeriod>
    <endTimePeriod>Jan 15 2024  7:00AM</endTimePeriod>
  </row>
  <row>
    <shopID>SHP</shopID>
    <SUMScanIn>57</SUMScanIn>
    <startTimePeriod>Jan 15 2024  7:00AM</startTimePeriod>
    <endTimePeriod>Jan 15 2024  8:00AM</endTimePeriod>
  </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:pixiReportScanInPerTimeQuant>
      <!-- <startDateTime>2024-01-15T06:00:00</startDateTime> -->
      <!-- <endDateTime>2024-01-15T18:00:00</endDateTime> -->
      <!-- <timePeriod>1</timePeriod> -->
      <!-- <shopID>SHP</shopID> -->
    </ns1:pixiReportScanInPerTimeQuant>
  </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:pixiReportScanInPerTimeQuant>
      <!-- <startDateTime>2024-01-15T06:00:00</startDateTime> -->
      <!-- <endDateTime>2024-01-15T18:00:00</endDateTime> -->
      <!-- <timePeriod>1</timePeriod> -->
      <!-- <shopID>SHP</shopID> -->
    </ns1:pixiReportScanInPerTimeQuant>
  </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 = [
    // 'startDateTime' => '2024-01-15T06:00:00',
    // 'endDateTime' => '2024-01-15T18:00:00',
    // 'timePeriod' => '1',
    // 'shopID' => 'SHP',
];

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

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

Notes

Tables used

Result set 1 · Always; empty if startDateTime is not before endDateTime.

Response example

Result set 1 · Always; empty if `startDateTime` is not before `endDateTime`.