Whats the best way to get Samba Entity Data onto a webserver with strict network policies

[the133448]

Ok, spent a bit of time, but this is amazing this can be done in Samba…

I order what I want

And pay it

I do a quick query of my webserver mysql datbase 2 seconds later

And the order, and entity data is all there.

From that I can do so much!

[the133448]

Hey @QMcKay would you have any idea of what to do if the terminal loses network connection? I dont want to lose all the sales on the webserver, and therfore get bad reports. Im thinkin about sominng like storing the ticketid_{number} in the samba db if it doesnt get a response from the web server and from then whenever I next tender an order, if script receives a response, it sends that off and after that it checks if in the datbase there are any values that containt ticketID and if so upload them and remove the value. Another complication with that is, how would I get the current system time that an order was made?

[QMcKay]

@the133448

Another complication with that is, how would I get the current system time that an order was made?

There are a few Date/Time values stored in the DB along with Orders and with Tickets, like Created Date, Last Updated Date etc.

Tickets:

,[Date]
,[LastOrderDate]
,[LastPaymentDate]
,[LastUpdateTime]

Orders:

,[CreatedDateTime]
,[LastUpdateDateTime]

[the133448]

Yeah I saw that, Im confident with doing this, Im not so confident in adding a sort of list to make sure all the orders get onto the webserver

[QMcKay]

It really depends on your intent, and what you are using this for, because the whole thing thus far is based on Order Submission. That is, when the Order Status changes from New to Submitted.

So if you are using this for Reporting purposes on the external Web Server, you should probably change the flow of it all. So instead of capturing and posting “Submitted Orders”, it is probably better to capture and post “Settled Tickets” which are fully Paid. We would use a different event in the Automation to post the entire Ticket all at once, based on:

Event: Before Ticket Closing
Remaining Amount Equals 0

If we do that ^ then we will get more accurate reports because the Ticket is finalized. The problem with posting Order by Order is that we might Void something for example, and the current method is not set up to handle that.

Since we would be dealing with entire finalized Tickets, we could more easily track which have been posted and which have not.

So if we get no response from the server, we simply store the Ticket ID in SambaPOS DB. Then next time we try to post, we look at the Ticket ID(s) which have failed to post, then gather all their information (Ticket Details, Order Details, etc), post the data, then erase the ID from the DB.

[the133448]

My order flow, is so that all orders must wither be settled or have all ites from it deleted, I do not allow unpaid orders to be submitted. Would I still need to use what you suggest?

[QMcKay]

Well, I don’t know what your flow is, but if it is based on what was designed in this Topic, then the answer is YES, you should revamp the flow to be Ticket-based rather than Order-based.

There isn’t such a thing as a “Paid/Settled Order”, only “Paid/Settled Tickets”. Orders are either “New” or “Submitted”, but it does not mean the Orders are “Paid/Settled”.

Hmm… stand by … need to figure a way to get a return from webserver back to SambaPOS. Anyone know how to do that in PHP ?

EDIT: nevermind, found it. PHP never ceases to amaze me.

Instead of this (which does not work):

return "SUCCESS";

We do this (to my surprise):

echo "SUCCESS";

So in the PHP on the server, at the end when we are finished:

$data .= "================================================================\r\n\r\n";

$fileName = "ticket_".$ticketID.".txt";
// FILE_APPEND | LOCK_EX
$fwsuccess = file_put_contents ($fileName, $data, FILE_APPEND);

echo "SUCCESS";   // sends data (SUCCESS) back to caller
return "SUCCESS"; // does nothing - sends nothing back
?>

[QMcKay]

Ok let’s give this a try.

After modification of the PHP to return “SUCCESS” via the echo statement, we modify the JScript to give us a better idea whether or not the Post succeeded …

  var res = web.PostJson(url,jsondata,usr,pwd);
  
  if (res != "SUCCESS")
  {
    //dlg.ShowMessage("Failed to Post Order Data:\r\n"+jsondata);
    return qrydata[0]+"~"+qrydata[1];
  }
  
  processFailedPosts(); // we will build a function for this
  return 0;

So with that ^ when the Post fails, we return <ticketId>~<orderId>
When the Post succeeds, we will process missed posts, then return 0.

---

Now a modification to our Rule that calls the Post Jscript to add another Execute Automation Command Action…

##PD Post Order Data [Automation Command Executed] (Rule)##

Rule Name:	PD Post Order Data
Event Name:	Automation Command Executed
Rule Tags:

Custom Constraint List (1):

Execute Rule if:

Matches

Automation Command Name

Equals

PD Post Order Data

##Actions (2):##

PD Store Value

Constraint: (none)

settingName:	PD_PostResult_[:CommandValue]
settingValue:	{CALL:pd.postJSON('{SETTING:PD_TicketId_[:CommandValue]}', '{SETTING:PD_OrderId_[:CommandValue]}', '{SETTING:PD_OrderNo_[:CommandValue]}', '{SETTING:PD_OrderUid_[:CommandValue]}')}
updateType:	Update
isLocal:	True

PD ExecAMC

Constraint: (none)

AMCname:	PD Update Failed Post ID
AMCvalue:	{SETTING:PD_PostResult_[:CommandValue]}
bg:	False
delay:	0

##Mappings##

Mappings

Terminal	User Role	Department	Ticket Type
*	*	*	*

---

And we need a Rule to handle our new Automation Command from the above Rule, which will be responsible for storing failed Post Data …

##PD Update Failed Post ID [Automation Command Executed] (Rule)##

Rule Name:	PD Update Failed Post ID
Event Name:	Automation Command Executed
Rule Tags:

Custom Constraint List (2):

Execute Rule if:

Matches

Automation Command Name	Equals	PD Update Failed Post ID
Command Value	Not Equals	0

##Actions (1):##

PD Store Value

Constraint: (none)

settingName:	PD_Failed_Postings
settingValue:	[='{SETTING:PD_Failed_Postings}' != '' ? '{SETTING:PD_Failed_Postings}' + ',' + '[:CommandValue]' : '[:CommandValue]']
updateType:	Update
isLocal:	False

##Mappings##

Mappings

Terminal	User Role	Department	Ticket Type
*	*	*	*

---

So now we have in SambaPOS DB, some orders that failed to Post as a comma-separated list using the format <ticketId>~<orderId> …

---

Finally, we need a method to process our list of failed Posts, which we will do in JScript…

function processFailedPosts()
{
  var tid=0;
  var oid=0;
  
  var lqry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='PD_Failed_Postings'";
  var failedPosts = sql.Query(lqry).First;
  if (failedPosts=='')
  {
    return 0;
  }
  
  failedPosts = failedPosts.split(',');
  var failedPostsCount = failedPosts.length;
  //return failedPostsCount;
  
  var datafields = getDataFields();
  var datafieldscount = datafields.length;
  
  for (var f=0; f<failedPostsCount; f++)
  {
    var pair = failedPosts[f].split('~');
    tid = pair[0];
    oid = pair[1];
    
    var qry = "";
    qry += "SELECT";
    for (var d=0; d < datafieldscount; d++)
    {
      qry += (d>0 ? " ," : " ");
      qry += "[" + datafields[d] + "]";
    }
    qry += " FROM [Orders]";
    qry += " WHERE 1=1";
    qry += " AND [TicketId] = " + tid;
    qry += " AND [Id] = " + oid;
    //qry += " AND [OrderNumber] = " + onm;
    //qry += " AND [OrderUid] = '" + ouid + "'";
    //qry += " ORDER BY [Id]";
  
    var qryresult = sql.Query(qry).Delimit('~').First;
    var qrydata = qryresult.split('~');
    var qryfieldcount = qrydata.length;  

    var data = new Object();
  
    for (var d=0; d < datafieldscount; d++)
    {
      data[datafields[d]] = qrydata[d];
    }
  
    var jsondata = JSON.stringify(data);
    //return jsondata;

    // Post missed Data to server
    var res = web.PostJson(url,jsondata,usr,pwd);

    if (res == "SUCCESS")
    {
      // remove missed Post data from DB
      var rval = tid+"~"+oid+",";
      var uqry = "UPDATE [ProgramSettingValues] SET [Value]=(REPLACE([Value],'"+rval+"','')) WHERE [Name]='PD_Failed_Postings'";
      sql.ExecSql(uqry);
      var rval = tid+"~"+oid;
      uqry = "UPDATE [ProgramSettingValues] SET [Value]=(REPLACE([Value],'"+rval+"','')) WHERE [Name]='PD_Failed_Postings'";
      sql.ExecSql(uqry);
    }
  }
  return 0;
}

You will notice that ^ will not work by itself because it calls another function. I re-arranged some of the master script to make it more modular, so here it is in its entirety …

// webserver URL, Username, Password
var url = "http://localhost:82/posthandler.php";
var usr = "q";
var pwd = "nada";
  
function getDataFields()
{
  var datafields = new Array();
  datafields.push("TicketId");
  datafields.push("Id");
  datafields.push("OrderNumber");
  datafields.push("OrderUid");
  
  datafields.push("MenuItemId");
  datafields.push("MenuItemName");
  datafields.push("PortionName");
  datafields.push("Price");
  datafields.push("Quantity");
  datafields.push("PortionCount");

  datafields.push("PriceTag");
  datafields.push("Tag");
  datafields.push("Taxes");
  datafields.push("OrderTags");
  datafields.push("OrderStates");
  
/*
  qry += " ,[CalculatePrice]";
  qry += " ,[DecreaseInventory]";
  qry += " ,[IncreaseInventory]";

  qry += " ,[CreatingUserName]";
  qry += " ,[CreatedDateTime]";
  qry += " ,[LastUpdateDateTime]";
  qry += " ,[AccountTransactionTypeId]";
  qry += " ,[ProductTimerValueId]";

  qry += " ,[GroupTagName]";
  qry += " ,[GroupTagFormat]";
  qry += " ,[Separator]";
  
  qry += " ,[Locked]";
  qry += " ,[WarehouseId]";
  qry += " ,[DepartmentId]";
  qry += " ,[TerminalId]";
*/
  return datafields;
}

function postJSON(tid,oid,onm,ouid)
{
  // override values for testing
  //var tid = 3214;
  //var oid = 7757;
  //var onm = 347;
  //var ouid = 'YTYb4Ra7H0aN4ucamXrwDg';
  //return "TID:"+tid + " OID:"+oid + " ONM:"+onm + " OUID:"+ouid;
  
  var datafields = getDataFields();
  var datafieldscount = datafields.length;
  
  var qry = "";

  qry += "SELECT";
  
  for (var d=0; d < datafieldscount; d++)
  {
    qry += (d>0 ? " ," : " ");
    qry += "[" + datafields[d] + "]";
  }
  
  qry += " FROM [Orders]";

  qry += " WHERE 1=1";
  qry += " AND [TicketId] = " + tid;
  //qry += " AND [Id] = " + oid;
  qry += " AND [OrderNumber] = " + onm;
  qry += " AND [OrderUid] = '" + ouid + "'";
  qry += " ORDER BY [Id]";
  
  //return qry;
  
  var qryresult = sql.Query(qry).Delimit('~').First;
  var qrydata = qryresult.split('~');
  var qryfieldcount = qrydata.length;  

  //return qryfieldcount;
  
  var data = new Object();
  
  for (var d=0; d < datafieldscount; d++)
  {
    data[datafields[d]] = qrydata[d];
  }
  
  var jsondata = JSON.stringify(data);
  //return jsondata;

  var res = web.PostJson(url,jsondata,usr,pwd);
  
  if (res != "SUCCESS")
  {
    //dlg.ShowMessage("Failed to Post Order Data:\r\n"+jsondata);
    return qrydata[0]+"~"+qrydata[1];
  }
  
  processFailedPosts();
  return 0;
}

function processFailedPosts()
{
  var tid=0;
  var oid=0;
  
  var lqry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='PD_Failed_Postings'";
  var failedPosts = sql.Query(lqry).First;
  if (failedPosts=='')
  {
    return 0;
  }
  
  failedPosts = failedPosts.split(',');
  var failedPostsCount = failedPosts.length;
  //return failedPostsCount;
  
  var datafields = getDataFields();
  var datafieldscount = datafields.length;
  
  for (var f=0; f<failedPostsCount; f++)
  {
    var pair = failedPosts[f].split('~');
    tid = pair[0];
    oid = pair[1];
    
    var qry = "";
    qry += "SELECT";
    for (var d=0; d < datafieldscount; d++)
    {
      qry += (d>0 ? " ," : " ");
      qry += "[" + datafields[d] + "]";
    }
    qry += " FROM [Orders]";
    qry += " WHERE 1=1";
    qry += " AND [TicketId] = " + tid;
    qry += " AND [Id] = " + oid;
    //qry += " AND [OrderNumber] = " + onm;
    //qry += " AND [OrderUid] = '" + ouid + "'";
    //qry += " ORDER BY [Id]";
  
    var qryresult = sql.Query(qry).Delimit('~').First;
    var qrydata = qryresult.split('~');
    var qryfieldcount = qrydata.length;  

    var data = new Object();
  
    for (var d=0; d < datafieldscount; d++)
    {
      data[datafields[d]] = qrydata[d];
    }
  
    var jsondata = JSON.stringify(data);
    //return jsondata;

    // Post missed Data to server
    var res = web.PostJson(url,jsondata,usr,pwd);

    if (res == "SUCCESS")
    {
      // remove missed Post data from DB
      var rval = tid+"~"+oid+",";
      var uqry = "UPDATE [ProgramSettingValues] SET [Value]=(REPLACE([Value],'"+rval+"','')) WHERE [Name]='PD_Failed_Postings'";
      sql.ExecSql(uqry);
      var rval = tid+"~"+oid;
      uqry = "UPDATE [ProgramSettingValues] SET [Value]=(REPLACE([Value],'"+rval+"','')) WHERE [Name]='PD_Failed_Postings'";
      sql.ExecSql(uqry);
    }
  }
  return 0;
}

---

That should do it. Next time we Submit an Order, and the Post is successful, we will also process the failed-Post list and post that data as well, then clear the list.

[jay007]

I am trying to follow this but am getting stuck. When I test the script I am getting ‘tid’ is undefined. What action type should the PD ExecAMC action be?

[JTRTech]

Your test expression doesn’t seem right to me, the call variables should surely have values in them but textual strings matching the variables.
Your terminal Id is not going to be tid surely?

[QMcKay]

@jay007

I am getting ‘tid’ is undefined

You need to supply actual real parameter values in the Test facility. You can’t just put letters in there - you need real values, usually enclosed in 'single quotes'.

So tid needs to be a valid Ticket Id, onm needs to be an Order Number (?), and ouid needs to be an Order UID.

---

@jay007

What action type should the PD ExecAMC action be?

ExecAMC is “Execute Automation Command”