You can integrate PixelPoint PoS system to OMMA screens so that they work in line with other add-ons (like drive-thru). There are two main steps:

  1. Implementing SQL Queries

  2. OMMA Panel Setup

Implementing SQL Queries

  • Open dbisql and enter your credentials provided by your software provider.

  • Confirm that syslog database table reflects the actions of your POS by examining syslog table. Copy the below code and paste it in dbisql's interactive console (mark 1 in image below).

select * from syslog order by timeadded desc
  • Execute the query by clicking arrow button (mark 2 in image). Check if the first row's "timeadded" field of result (mark 3 in image) reflects your last actions. You can proceed to next step if it does, otherwise please consult your software provider.

  • After successfully applying previous steps, we'll be executing a series of SQL queries. Beware that none of the queries can be executed twice. If a query is executed twice, it will throw an error (click ok and proceed). Execute the below query the same way as above. This query ensures a clean start by removing the appropriate table, trigger and procedure if they are previously created.

drop table OmmaPos
drop procedure client_post
drop trigger eventaction


If there were previous creations then this deleting execution will be successful. Otherwise you'll see an error message that says corresponding entity "not found". It means it couldn't find the entities to delete. This is fine, just click "ok" and proceed to next step.

Successfully executed query (pictured above) shows the execution time as the above example shows.

Failed execution of the query shows an error (pictured above). Error indicates that it couldn't remove entities because there isn't any. It's not an issue. Just click "OK" and proceed to the next step.

  • Execute the below query. This query creates a procedure that posts data to an address. It does this by sending an HTTP post request. An address could be a simple webpage but in this case, it's the cashier device's HTTP endpoint.

  • Warning: Replace cashierdeviceip with your cashier device's internal IP.

CREATE PROCEDURE client_post(data NVARCHAR(124))
URL 'http://**cashierdeviceip**:7557/content-message/all'
TYPE 'HTTP:POST:text/plain';

In the above example cashier IP used as 192.168.1.105. Please replace this with your cashier device's IP.

  • Execute the query below that creates a table which will later be used to hold orders coming from Syslog.

CREATE TABLE OmmaPos
(
ID INT PRIMARY KEY DEFAULT AUTOINCREMENT,
ACTIONTYPE NVARCHAR(256),
PRODUCTID NVARCHAR(256),
INFO NVARCHAR(4096)
)

  • Execute the query below. This query detects whenever an action's been made and inserted to syslog table. It inspects the inserted row, modifies, and inserts it into or removes from the OmmaPos table if necessary.

create trigger eventaction on syslog for insert
as
begin
declare @syslog_action_type nvarchar(256)
declare @syslog_action_data nvarchar(4096)
select @syslog_action_type = ACTIONID,
@syslog_action_data = replace(ACTMEMO, '"', '`')
from inserted
where UNIQUEID = (SELECT MAX(UNIQUEID) FROM inserted)
and (
ACTIONID = 'OpenTransact' or
ACTIONID = 'CloseTransact' or
ACTIONID = 'OrderItem' or
ACTIONID = 'RemoveItem' or
ACTIONID = 'Refund' or
ACTIONID = 'PostBill' or
ACTIONID = 'LeaveTransact'
)
if isnull(@syslog_action_type, '') != ''
begin
if @syslog_action_type = 'OpenTransact' OR
@syslog_action_type = 'CloseTransact' OR
@syslog_action_type = 'PostBill' OR
@syslog_action_type = 'LeaveTransact'
begin
insert into OmmaPos (ACTIONTYPE, PRODUCTID, INFO) values (
@syslog_action_type,
'',
@syslog_action_data
)
end

if @syslog_action_type = 'OrderItem'
begin
declare @arabicname nvarchar(256)
declare @productid nvarchar(256)
select @productid = stuff(
@syslog_action_data,
charindex(',', @syslog_action_data),
999,
null
)
select top 1 @arabicname = PrintDes2
from product
where PRODNUM = 2012
order by PrintDes2
select @syslog_action_data = stuff(
@syslog_action_data,
charindex(',', @syslog_action_data) + 1 ,'' , @arabicname || ' - ')

insert into OmmaPos (ACTIONTYPE, PRODUCTID, INFO)
values(@syslog_action_type, @productid, @syslog_action_data)
end

if @syslog_action_type = 'RemoveItem'
begin
delete top 1
from OmmaPos
where ACTIONTYPE = 'OrderItem' and
PRODUCTID = (
stuff(
@syslog_action_data,
charindex(',', @syslog_action_data),
999,
null
)
)
order by ID desc
end

if @syslog_action_type = 'Refund'
begin
declare @start_char int
declare @end_char int
declare @refund_productid char(20)

select @start_char = charindex(':', @syslog_action_data) + 2
select @end_char = charindex('(', @syslog_action_data) - 1
select @refund_productid = substring(
@syslog_action_data,
@start_char,
@end_char - @start_char
)
delete top 1 from OmmaPos
where ACTIONTYPE = 'OrderItem' and
PRODUCTID = @refund_productid
order by ID desc
end
end
end

  • Execute the below query. This query detects whenever a row is deleted from the OmmaPos table. It inspects the deleted row and sends current order data to the cashier device by using client_post procedure.

create trigger omma_delete_trigger on OmmaPos for delete
as
begin
declare @actiontype nvarchar(256)
declare @actiondata nvarchar(4096)
declare @payload nvarchar(4096)
declare @resultstatus nvarchar(256)

select @actiontype = ACTIONTYPE,
@actiondata = replace(INFO, '"', '`') from OmmaPos
where ID = (SELECT MAX(ID) FROM OmmaPos)
select @payload = '{
"eventName":"pixelPoint",
"data":{
"event":"OrderItem",
"data":"' ||
( select list(INFO, '|') from OmmaPos
where ACTIONTYPE = 'OrderItem'
) ||
'"}}'
select @resultstatus = Value
from client_post(@payload)
with (
Attribute nvarchar(256),
Value nvarchar(256))
where Attribute = 'Status'
end

  • Execute the below query. This query detects whenever a row is inserted into the OmmaPos table. It inspects the inserted row and sends current order data to the cashier device by using client_post procedure.

create trigger omma_insert_trigger on OmmaPos for insert
as
begin
declare @actiontype nvarchar(256)
declare @actiondata nvarchar(4096)
declare @payload nvarchar(4096)
declare @resultstatus nvarchar(256)
select @actiontype = ACTIONTYPE,
@actiondata = replace(INFO, '"', '`')
from OmmaPos
where ID = (SELECT MAX(ID) FROM OmmaPos)
if @actiontype = 'CloseTransact' or @actiontype = 'LeaveTransact'
begin
select @payload = '{
"eventName":"pixelPoint",
"data":{
"event":"' || @actiontype || '",
"data":"' || (
select list(INFO, '|') from OmmaPos
where ACTIONTYPE = 'OrderItem'
) ||
'"}}'
select @resultstatus = Value
from client_post(@payload)
with (
Attribute nvarchar(256),
Value nvarchar(256))
where Attribute = 'Status'
delete from OmmaPos
end
else
begin
select @payload = '{
"eventName":"pixelPoint",
"data":{
"event":"' || @actiontype || '",
"data":"' || (
select list(INFO, '|') from OmmaPos
where ACTIONTYPE = 'OrderItem'
) || '"}}'

Important note: After executing the above queries upon every related POS action your POS software will try to send data to the cashier device. If cashier device is not online or not in the same network as with the POS device or in anyway POS device fails to send data to the cashier, then you will face this window. Whenever this happens please check if you did everything correctly on your drive-through (link to drive-thru support page) content.

Important note: If in any way this query prevents your POS application to work in a normal way. Please execute the below query to clean the POS state and contact us.

drop table OmmaPos
drop procedure client_post
drop trigger eventaction

OMMA Panel Setup

  • Add the PixelPoint PoS add-on to your content.

  • Hover over the add-on and click on configure.

  • Select the currency you are using and click Done.

  • Request Approval, Approve and Publish when you are done.

Did this answer your question?