SambaPOS API Integration with NewBook PMS/Booking System


#205

Sorry for my ignorance but does that work out more beneficial than updating the data into the entity custom data fields directly when running the main script?
It would still be a scheduled update of the data…
In regards to scheduling I was thinking I would also add an entity screen button to run a manual interim update should the need ever arrise.


#206

Well, that is up to you… there is more than one way to skin a cat.

The pertinent thing you should do though is separate the auth/post/download into a separate script that runs periodically in the background, and save the data locally as a file.

Whether or not you update Entity Fields on the fly by reading the file is your choice, but I would expect there would not be much performance hit, because reading and parsing a 75K file on your HD should be very fast.


#207

Fair enough, that’s what Ill do :slight_smile: was just curious if there was specific reason you said to do it that way.
Will obviously have a play and see if one way or another works better in testing.
Have asked if I can get access to a testing account for the booking side rather than just testing API to be able to run some real world workflow trials.


#208

Ok, so its time to get on with some more progress on this integration.
Am wanting to work on the script parts before doing the samba automation as the scripting is the new part for me.

First bit I need to work on is a script to post a ticket to the REST.
To do this I am wanting to pull product group totals from the ticket, convert the product group into the ‘gl_account’ code for the corresponding PMS departmental/analysis account.

I imagine we can use report tags in scripts??
On that basis the first hurdle is I have done absolutely no work in custom reports to date.

Anyone recommend a good tutorial for beginners use of custom reports?
Or would there be a better way to achieve in scripts?


#209

http://www.sambapos.com/wiki/doku.php?id=custom_reporting_tags

Thats the best resource for custom reports. But scripts do not support Custom Report tags inside the script. Can you explain more of what your trying to do?

It might be better to use SQL and sql.Query function and use a parameter to separate results to comma separated values for example.


#210

I thought that might be the case hense why I asked before ploughing into custom reports too deeply at this point.

I will need to setup two posting scripts.
First and most important one is the room posts script.
I will use the information pulled from the previous script (site_id and account_code) to post the charges to that room.
I want to post all charges as a ‘departmental’ (ideally draft, bottles, wine etc or very least wet/dry)
I need to pull the totals for these from the current ticket.


#211

The second post which will be more compex will be to do similar departmental report for the epos paid orders/tickets (non posted to rooms) into the appropriate master accounts so we can have a centralized accounting/sales reports between EPOS and bookings.

The room posts is first task.


#212

Ok I understand. So your ringing up items to be billed to room.


#213

Yes, am just pasting the sample JSON request/posts which should also clarify the plan;

This is sample post for single charge;

{
“api_key”: “your_api_key_here”,
“account_id”: “2”,
“description”: “Misc Charge”,
“gl_account_code”: “POS”,
“amount”: “3.95”,
“tax_free”: “0”,
“generated_when”: “2015-09-13 14:00:00”
}

This is what I will most likely need to use for both;

{
   "api_key": "your_api_key_here",
   "sales": [
                  {
                   "account_id": 889,
                   "generated_when": "now",
                   "charges": [
                                     {
                                        "description": "Phone Charge Local",
                                        "gl_account_code": "POS",
                                        "amount": "3.95",
                                        "tax_free": 0
                                    }
                                 ],
                  "payments": [
                                      {
                                      "description": "Visa",
                                      "type": "visa",
                                      "amount": 8.95
                                       }
                                     ]
              }
       ]
   }

There would not be a payment array for room posts as they are just posts to be paid through the booking side.
Non room post sales will be sent with payments.
So long as total sales posts and payments posts match totals it can just be lumped through in bulk totals.


#214

The non room posts sales charges part would look like this;

                   "charges": [
                                     {
                                        "description": "Draft Beer",
                                        "gl_account_code": "102",
                                        "amount": "12.95",
                                        "tax_free": 1
                                    }
                                     {
                                        "description": "Bottles",
                                        "gl_account_code": "103",
                                        "amount": "3.95",
                                        "tax_free": 1
                                    }
                                     {
                                        "description": "Wine",
                                        "gl_account_code": "104",
                                        "amount": "19.95",
                                        "tax_free": 1
                                    }
                                     {
                                        "description": "Soft Drinks",
                                        "gl_account_code": "105",
                                        "amount": "4.65",
                                        "tax_free": 1
                                    }
                                     {
                                        "description": "Food",
                                        "gl_account_code": "108",
                                        "amount": "35.65",
                                        "tax_free": 1
                                    }
                                 ],

#215

As report tags cant be used in scripts, @QMcKay would you be able to point me in the right direction regarding using SQL reports?
I couldnt see much in the way of ‘starter tutorial’ for this but then I guess it is not a samba based method…


#216

You don’t use SQL reports. You use SQL. When I get home I’ll show example


#217

Sorry, that is what I meant to mean :slight_smile: sql code for reporting values :smile:


#218

Do template tags not work in script either?


#219

Yes they work. How are you trying to use them?


#220

To confess I haven’t yet…
If ticket template tags work I can get a ticket posting script done using those however the end of day post of non room post sales will then require the SQL code.

In regards the per ticket room postings I am not sure of the best way to summarise an analysis level totals for posting, as per my other post about analysis departments the first thought that comes to mind is to define a analysis level value for each product using product tag and convert to a state on order added and then use a ORDER TOTAL BY STATE tag to summarise the post within the script for the that ticket.

Any suggestions? If you are understanding what I’m saying…


#221

Have let the topic get quiet as manager wasn’t sure if the change in booking system was the right thing to do however now all is set and am getting the wheels rolling so will see good progress on this project the next month.

@QMcKay I am hoping you will be able to help with the SQL code to return sales totals for the day in the script to post the end of day sales to the pms system.


#222

@emre can JSON.stringify handle sub arrays?
This is the request I need to build.

{
   "api_key": "your_api_key_here",
   "sales": [
                   {
                      "account_id": 889,
                      "generated_when": "now",
                      "charges": [
                                          {
                                             "description": "Food",
                                             "gl_account_code": "Food",
                                             "amount": "15",
                                             "tax_free": 0
                                          }
                                          {
                                             "description": "Drinks",
                                             "gl_account_code": "Food",
                                             "amount": "10",
                                             "tax_free": 0
                                          }
                                     ],
                  }
             ]
}

I have manually built the arrays into the following;

function room_post()
{
/*--- VARABLES ---*/ 
var url = 'https://testau.newbookpms.com/rest/pos_sale';
var username       	    	= 'xxxx';
var password        	   	= 'xxxx';
var apikey          	   	= "xxxxx";

var test_account_id			= '14095';
var generated_when			= 'now';

/*--- EPOS POST VARIABLES---*/
var wet_gl_account_code = '10002';
var wet_description = 'Drinks';
var dry_gl_account_code = '10003';
var dry_description = 'Food';
	
/*--- WET POST JSON DATA---*/
var wet_json_data = new Object();
wet_json_data.description = wet_description;
wet_json_data.gl_account_code = wet_gl_account_code;
wet_json_data.amount = '115.00';
wet_json_data.tax_free = '0';
var wet_json = JSON.stringify(wet_json_data);

/*--- DRY POST JSON DATA---*/
var dry_json_data = new Object();
dry_json_data.description = dry_description;
dry_json_data.gl_account_code = dry_gl_account_code;
dry_json_data.amount = '210.00';
dry_json_data.tax_free = '0';
var dry_json = JSON.stringify(dry_json_data);

/*--- POST DETAILS JSON DATA---*/
var sales_json = 
	'{'+
	'\"account_id\":\"' + test_account_id + '\",' +
	'\"generated_when\":\"' + generated_when + '\",' +
	'\"charges\":[' + dry_json + ',' + wet_json + ']}';	

/*--- JSON REQUEST BUILDING---*/ 
var pos_sale_json =
	'{'+
		'\"api_key\":\"' + apikey + '\",' +
		'\"sales\":[' + sales_json + ']}';

/*--- JSON POST & RESPONCE---*/ 
var response = web.PostJson(url,pos_sale_json,username,password);
var responseObject = JSON.parse(response);


return response;
}

WHICH WORKS :smile:
Renders as;

{  
"api_key":"xxxxx",
"sales":[  
    {  
        "account_id":"14095",
        "generated_when":"now",
        "charges":[  
            {  
                "description":"Food",
                "gl_account_code":"10003",
                "amount":"210.00",
                "tax_free":"0"
            },
            {  
                "description":"Drinks",
                "gl_account_code":"10002",
                "amount":"115.00",
                "tax_free":"0"
            }
        ]
    }
]
}

Although this is still a testing script and doesnt draw any data from samba yet is step in nice direction.

Anyone have a suggestion to clean up this part?

	/*--- POST DETAILS JSON DATA---*/
        var sales_json = 
	   '{'+
    	        '\"account_id\":\"' + test_account_id + '\",' +
	        '\"generated_when\":\"' + generated_when + '\",' +
	        '\"charges\":[' + dry_json + ',' + wet_json + ']}'      ;	

        /*--- JSON REQUEST BUILDING---*/ 
        var pos_sale_json =
	    '{'+
		'\"api_key\":\"' + apikey + '\",' +
		'\"sales\":[' + sales_json + ']}'     ;

#223

@QMcKay nice to see you back on the forum, havn’t seen you post for a while, have you been on holiday? :smile:

Am counting on some assistance from you with your SQL skills at some point when I come to the part of this project of posting end of day figures over to PMS.


#224

You can beautify it better than me but this is how it should be implemented.

function jsontest() {
    var data = {
        api_key: 'api-xxx',
        sales: [{
            account_id: 889,
            generated_when: 'now',
            charges: [{
                description: 'Food',
                gl_account_code: 'Food',
                amount: '15',
                tax_free: 0
            }, {
                description: 'Drinks',
                gl_account_code: 'Food',
                amount: '10',
                tax_free: 0
            }]
        }]
    };

    var response = JSON.stringify(data);
    return response;
}