Skip to content

API – Help document

How to see swagger to check json Struture – Access http://locahost:PORT/swagger

Go to endpoint v1/itemTransaction for example, and you can see example value to structure (this endpoint is used for Sales and Purchase documents):

Using QUERY Endpoint to get data:

Sage 50c Database Tables Structure: https://smartdigit.pt/SageDataDicionary.html

QUERY Endpoint: http://locahost:PORT/swagger/ui/index#!/Query/Query_V2Post

How to retrieve exception logs:

SELECT TOP(10) * FROM UX_SD_LOGS

How to filter by date

CONCAT(CONVERT(VARCHAR(10), i.DtAlt, 120),' ', CAST(CONVERT(VARCHAR,DATEADD(SECOND, i.HrAlt*86400, 0),108) AS TIME)) > '2021-02-20 10:00:00'

Item list filtered by the language PTG and the date of the last change

SELECT i.itemID, ItemNames.Description, i.ItemPictureName, i.FamilyID, f.Description
FROM Item I
INNER JOIN ItemNames ON i.itemID = ItemNames.ItemID
INNER JOIN Family f ON f.FamilyID = i.FamilyID
WHERE ItemNames.LanguageID = 'PTG' AND
CONCAT(CONVERT(VARCHAR(10), i.DtAlt, 120),' ', CAST(CONVERT(VARCHAR,DATEADD(SECOND, i.HrAlt*86400, 0),108) AS TIME)) > '2021-02-20 10:00:00'
SELECT        ItemID, BarCode, BarCodeType, ColorID, SizeID, PropertyID1, Description, UnitPrice, UnitOfSaleID, MaximumDecimals, Quantity, FindON, ItemType
FROM            (SELECT        i.ItemID, i.BarCode, i.BarCodeType, ISNULL(c.ColorID, 0) AS ColorID, ISNULL(s.SizeID, 0) AS SizeID, ISNULL(i.PropertyID1, NULL) AS PropertyID1, [in].Description,
                                                    CASE WHEN ispS.UnitPrice > 0 THEN ispS.UnitPrice ELSE isp.UnitPrice END AS UnitPrice, i.UnitOfSaleID, um.MaximumDecimals, NULL AS Quantity, 1 AS FindON, i.ItemType
                          FROM            dbo.Item AS i INNER JOIN
                                                    dbo.ItemNames AS [in] ON i.ItemID = [in].ItemID AND [in].LanguageID =
                                                        (SELECT        DefaultLanguageID
                                                          FROM            dbo.ConfSystem) INNER JOIN
                                                    dbo.UnitOfMeasure AS um ON um.UnitOfMeasureID = i.UnitOfSaleID INNER JOIN
                                                    dbo.ItemSellingPrices AS isp ON i.ItemID = isp.ItemID AND isp.PriceLineID = 1 AND isp.SizeID = 0 LEFT OUTER JOIN
                                                    dbo.ItemColor AS c ON i.ItemID = c.ItemID LEFT OUTER JOIN
                                                    dbo.ItemSize AS s ON i.ItemID = s.ItemID LEFT OUTER JOIN
                                                    dbo.ItemSellingPrices AS ispS ON i.ItemID = ispS.ItemID AND ispS.PriceLineID = 1 AND ispS.SizeID = s.SizeID
                          WHERE        (i.Discontinued = 0)
                          UNION
                          SELECT        POSi.ItemID, POSi.POSItemID, POSi.BarCodeType, ISNULL(POSi.ColorID, 0) AS ColorID, ISNULL(POSi.SizeID, 0) AS SizeID, ISNULL(i.PropertyID1, NULL) AS PropertyID1,
                                                   CASE WHEN POSi.Description <> '' THEN POSi.Description ELSE [in].Description END AS Description,
                                                   (CASE WHEN POSi.SalePrice > 0 THEN POSi.SalePrice ELSE (CASE WHEN ispS.UnitPrice > 0 THEN ispS.UnitPrice ELSE isp.UnitPrice END) END) AS UnitPrice, POSi.UnitOfMeasure, um.MaximumDecimals,
                                                   POSi.Quantity, 3 AS FindON, i.ItemType
                          FROM            dbo.POSIdentity AS POSi INNER JOIN
                                                   dbo.Item AS i ON POSi.ItemID = i.ItemID INNER JOIN
                                                   dbo.ItemNames AS [in] ON POSi.ItemID = [in].ItemID AND [in].LanguageID =
                                                       (SELECT        DefaultLanguageID
                                                         FROM            dbo.ConfSystem) INNER JOIN
                                                   dbo.UnitOfMeasure AS um ON um.UnitOfMeasureID = POSi.UnitOfMeasure INNER JOIN
                                                   dbo.ItemSellingPrices AS isp ON POSi.ItemID = isp.ItemID AND isp.PriceLineID = 1 AND isp.SizeID = POSi.SizeID LEFT OUTER JOIN
                                                   dbo.ItemSellingPrices AS ispS ON POSi.ItemID = ispS.ItemID AND ispS.PriceLineID = 1 AND ispS.SizeID = POSi.SizeID
                          WHERE        (POSi.POSItemID <> '') AND (i.Discontinued = 0)) AS T

Get document identifiers and filter them by date

SELECT TransDocument, TransSerial, TransDocNumber, CONCAT(CONVERT(VARCHAR(10), DtAlt, 120),' ', CAST(CONVERT(VARCHAR,DATEADD(SECOND, HrAlt*86400, 0),108) AS TIME)) AS DtAlt FROM SaleTransaction 
WHERE CONCAT(CONVERT(VARCHAR(10), DtAlt, 120),' ', CAST(CONVERT(VARCHAR,DATEADD(SECOND, HrAlt*86400, 0),108) AS TIME)) > '2021-03-20 23:59:00'

Get list Open unpaid document of specific customer ordered by deferred payment date

SELECT CreateDate, DeferredPaymentDate, ContractReferenceNumber, TransSerial, TransDocument, TransDocNumber, TransInstallmentID, TotalAmount, TotalPendingAmount, DebitTotalAmount, CreditTotalAmount, PartyAccountTypeID, PartyAddressID
FROM CustomerLedgerAccount
  where TotalPendingAmount <> '0' and PartyID = '1'
  Order By DeferredPaymentDate