Extract all items in a ticket to a Single JSON

V5 Question
1

Using the POSTJsonData Script i can get all the data but its not merged
i have been stuck here and guess i need some help.
how can i merge this data
Preformatted text{
“TicketId”: “398351”,
“Id”: “608640”,
“OrderNumber”: “67”,
“OrderUid”: “JtS6xlfa9kqU-VxoboNqEQ”,
“MenuItemId”: “39247”,
“MenuItemName”: “DELMONTE JUICE -PACKET”,
“PortionName”: “Normal”,
“Price”: “100.00”,
“Quantity”: “1.000”,
“PortionCount”: “1”,
“PriceTag”: “”,
“Tag”: “”,
“Taxes”: “[{“AC”:false,“AT”:14,“RN”:2,“TN”:“16% VAT”,“TR”:16.00,“TT”:1},{“AC”:false,“AT”:16,“RN”:2,“TN”:“2% - CTL”,“TR”:2.00,“TT”:2}]”,
“OrderTags”: “”,
“OrderStates”: “[{“D”:”\/Date(1639889124475+0300)\/",“OK”:“000000”,“S”:“Submitted”,“SN”:“Status”,“SV”:"",“U”:1},{“D”:"\/Date(1639885785037+0300)\/",“OK”:“000000”,“S”:“Active”,“SN”:“DStatus”,“SV”:"",“U”:1},{“D”:"\/Date(1639889123258+0300)\/",“OK”:“000000”,“S”:“NotPrinted”,“SN”:“KDStatus”,“SV”:"",“U”:1},{“D”:"\/Date(1639889123305+0300)\/",“OK”:“001001”,“S”:“Void”,“SN”:“GStatus”,“SV”:"",“U”:1}]"
}Preformatted text

TO get this type of data, i can be able to get the names and values i just want the items to be nested in a single json.

Preformatted text{“items”:[
{“MenuItemName”:“SINGLETON”,“Quantity”:“4.000”,“Price”:“400.00”},
{“MenuItemName”:“SODA”,“Quantity”:“2.000”,“Price”:“200.00”}
]
}Preformatted text

2

Suspecting this could be the cause since just need some clarification
var data = new Object();

for (var d=0; d < datafieldscount; d++)
{
data[datafields[d]] = qrydata[d];
}

3 
    var q = '';
        q+= 'something';
            q+= 'about';
            q+= 'my';
            q+= 'data"'+variableName+'"';

etc.

4

let me try thanks for the tip

5

https://forum.sambapos.com/t/membership-api-integration/12205/25

This is a good example, almost identical of what you are trying to achieve, might be useful to you

6

Managed to Nest it but the output is being looped wrongly it is repeating the same order
for (var i = 0; i < orderid_qty; i++) {

      ItemsObject={};
			var post_tax = '16';
			var post_json_data = new Object();
			post_json_data.OrderID = order_id;
			post_json_data.MenuItemName = order_name;
			post_json_data.Quantity = order_quantity;
			post_json_data.Price = order_price;
			post_json_data.amount = order_total;
			post_json_data.tax = post_tax;
        ItemsObject=(post_json_data);
        ItemsArray[i] = ItemsObject;
        }

[{“OrderID”:“608643”,“MenuItemName”:“STILL WATER -500 ML”,“Quantity”:“1.000”,“Price”:“300.00”,“amount”:300,“tax”:“16”},{“OrderID”:“608643”,“MenuItemName”:“STILL WATER -500 ML”,“Quantity”:“1.000”,“Price”:“300.00”,“amount”:300,“tax”:“16”},{“OrderID”:“608643”,“MenuItemName”:“STILL WATER -500 ML”,“Quantity”:“1.000”,“Price”:“300.00”,“amount”:300,“tax”:“16”},{“OrderID”:“608643”,“MenuItemName”:“STILL WATER -500 ML”,“Quantity”:“1.000”,“Price”:“300.00”,“amount”:300,“tax”:“16”}]

1 Like
7

I am curious what you are building this for. Is there any other methods we can help you get the desired result? How are you getting the original json?

8

We have a Control Unit for the Tax man that requires the ticket number and the items ordered per ticket number also the last details on the receipt, tax amount , vatable amount and finally the total.
The control unit runs a backend (express ) web server that requires the json is a specific format. IE
{
“Transactions”: {
“SenderId”: “{{SENDER_ID}}”,
“MiddlewareInvoiceNumber”:9,
“TransactionLines”:[
{
“HSDesc”: “BURGER WITH CHIPS”,
“TaxRate”: 16,
“ItemAmount”: 250,
“TaxAmount”: 34.48,
“UnitPrice”: 250,
“Quantity”: 1
},
{
“HSDesc”: “SODA 300ML”,
“TaxRate”: 16,
“ItemAmount”: 100,
“TaxAmount”: 13.79,
“UnitPrice”: 100,
“Quantity”: 1
}
]
}
}
IT shall verify the invoice details and respond with a CONTROL CODE that we use to generate a qr code that is supposed to be printed to on the receipt
“Code”: “9990000030000000007”,
ie “QRCode”: “url=9990000030000000007”

1 Like
9

How are you looping through the ticket details? Just a script, URL printer, something else?

10

What country is this for?

11

Looping using a script and passing the values to the php handler

12

Kenya, by August this year all business should be compliant

13

Great Progress Hope it will work now
{
“TransactionsLines”: [{
“Id”: 608640,
“MenuItemName”: “DELMONTE JUICE -PACKET”,
“Price”: 100.00,
“Quantity”: 1.000,
“Taxes”: “[{“AC”:false,“AT”:14,“RN”:2,“TN”:“16% VAT”,“TR”:16.00,“TT”:1},{“AC”:false,“AT”:16,“RN”:2,“TN”:“2% - CTL”,“TR”:2.00,“TT”:2}]”
},
{
“Id”: 608641,
“MenuItemName”: “SPECIAL SODA”,
“Price”: 250.00,
“Quantity”: 1.000,
“Taxes”: “[{“AC”:false,“AT”:14,“RN”:2,“TN”:“16% VAT”,“TR”:16.00,“TT”:1},{“AC”:false,“AT”:16,“RN”:2,“TN”:“2% - CTL”,“TR”:2.00,“TT”:2}]”
},
{
“Id”: 608642,
“MenuItemName”: “STILL WATER -1 LITRE”,
“Price”: 150.00,
“Quantity”: 1.000,
“Taxes”: “[{“AC”:false,“AT”:14,“RN”:2,“TN”:“16% VAT”,“TR”:16.00,“TT”:1},{“AC”:false,“AT”:16,“RN”:2,“TN”:“2% - CTL”,“TR”:2.00,“TT”:2}]”
},
{
“Id”: 608643,
“MenuItemName”: “STILL WATER -500 ML”,
“Price”: 300.00,
“Quantity”: 1.000,
“Taxes”: “[{“AC”:false,“AT”:14,“RN”:2,“TN”:“16% VAT”,“TR”:16.00,“TT”:1},{“AC”:false,“AT”:16,“RN”:2,“TN”:“2% - CTL”,“TR”:2.00,“TT”:2}]”
}
]
}

1 Like
14

Sorry, I should have been more specific:

How are you initially pulling the data from SambaPOS before generating your JSON data to your external handler? Once we know this we can possible figure out why you’re seeing duplicate orders.

1 Like
15

Hello @Wisefix
How are you achieving this am having the challenge I have to send item name , price , barcode , quantity and total price to API but I have search around and I can find any solution

16

@Posflow how do i achieve this , i want to get the order details including product name , unit price , bar code and quantity in a json to post to Api endpoint

goodsDetails": [{
"item": "apple",
"itemCode": "101",
"qty": "2",
"unitOfMeasure": "kg",
"unitPrice": "150.00",
"total": "1",
"taxRate": "0.18",
"tax": "12.88",
"discountTotal": "18.00",
"discountTaxRate": "0.18",
"orderNumber": "1",
"discountFlag": "1",
"deemedFlag": "1",
"exciseFlag": "2",
"categoryId": "1234",
"categoryName": "Test",
"goodsCategoryId": "5467",
"goodsCategoryName": "Test",
"exciseRate": "0.12",
"exciseRule": "1",
"exciseTax": "20.22",
"pack": "1",
"stick": "20",
"exciseUnit": "101",
"exciseCurrency": "UGX",
"exciseRateName": "123",
"vatApplicableFlag": "1"
}, {
"item": "car",
"itemCode": "101",
"qty": "2",
"unitOfMeasure": "kg",
"unitPrice": "150.00",
"total": "1",
"taxRate": "0.18",
"tax": "12.88",
"discountTotal": "18.00",
"discountTaxRate": "0.18",
"orderNumber": "2",
"discountFlag": "1",
"deemedFlag": "1",
"exciseFlag": "2",
"categoryId": "Test",
"categoryName": "Test",
"goodsCategoryId": "Test",
"goodsCategoryName": "Test",
"exciseRate": "0.12",
"exciseRule": "1",
"exciseTax": "20.22",
"pack": "1",
"stick": "20",
"exciseUnit": "101",
"exciseCurrency": "UGX",
"exciseRateName": "123",
"vatApplicableFlag": "1"
}],
17

You can generate JSON from a db query:


SELECT o.MenuItemName AS item,
       o.MenuItemId   AS itemCode,
       o.Quantity     AS qty,
       o.Price        AS unitPrice,
       o.OrderNumber  AS orderNumber,
       smc.Id         AS categoryId,
       smc.Name       AS categoryName
    FROM dbo.Orders                          o
         INNER JOIN dbo.ScreenMenuItems      smi
                 ON smi.MenuItemId = o.MenuItemId
         INNER JOIN dbo.ScreenMenuCategories smc
                 ON smc.Id         = smi.ScreenMenuCategoryId
FOR JSON PATH, ROOT('goodsDetails');

But taxes are stored as a JSON array in the db. It’s possible to extract values from the JSON within the db. But it’s something I’ve not really used a whole lot. And even when I did, it was hacked copy-and-paste from StackOverflow.

My recommendation is to query the db for the data you need then generate the JSON payload in whatever backend you’re running.

Use a query like this

SELECT o.MenuItemName,
       o.MenuItemId,
       o.Quantity,
       o.Price,
       o.OrderNumber,
       o.Taxes,
       smc.Id AS CategoryId,
       smc.Name AS CategoryName
    FROM dbo.Orders                          o
         INNER JOIN dbo.ScreenMenuItems      smi
                 ON smi.MenuItemId = o.MenuItemId
         INNER JOIN dbo.ScreenMenuCategories smc
                 ON smc.Id         = smi.ScreenMenuCategoryId

then you can parse the taxes column data separately and map everything to your DTO

1 Like
18

@Memo how do i incorporate this into a function ??

19

Functions are JavaScript so it’s up to you to build something. There are helpers for SQL queries, and HTTP requests. There’s a forum thread somewhere with it all documented.

20

check out stored procedures and you must be using slq 2016 to run jsonpath