Event Logs using Tasks

I was thinking about Ticket Logs the other day and thought I should add some Logging at my Venues. Naturally, being me, my mind went straight to using Tasks for the logging. Why?

We know that there is already an Add Ticket Log Action in SambaPOS that allows us to log details of what happens in a Ticket, and @emre created a very good Tutorial for this:

Great Tutorial. But there is one problem with it, and I believe this could be a common scenario where operators could be putting a lot of money into their pocket without you knowing it happened, short of looking at hours of camera surveillance:

  • Add Order(s) to a Ticket
  • Ticket Total is shown on Customer Display
  • Customer Pays and leaves
  • Operator Cancels all Orders and puts money in pocket

The problem with Ticket Logs is that they are stored in the Database in the [Tickets] table, in the column named [TicketLogs]. But with the above scenario, a Ticket is NOT actually created nor is it saved to the DB, so any “logging” using the method in the Tutorial simply disappears into the ether, and we have no idea any of this happened.

And, what if we want to Log other Events that have nothing to do with a Ticket at all? Like a Drawer Open Event, or an Account Payment, or whatever? We need something more flexible.

So how do we handle this situation? Tasks again come to the rescue!

##Task Type

Define a Task Type and add any Custom Fields you think you might need.

:bulb: TIP: You don’t actually need to define Custom Fields to use them. You can dynamically set Custom Fields in a Task even if they are not defined in the Task Type.


##Methods

To Add or Update Tasks, we can use an Add Task Action in our Rules, or we can use JScript and GraphQL:

  • Method 1: the simple method is to use the Add Task Action. That Action can determine whether to Add a Task or Update an existing Task based on the Custom Data Field named Id (Identifier). If the Identifier is already found in the DB, the Task will be Updated, and if it is not found, the Task will be Added. Each time we update the Task, new data will be appended to the Content Field.

  • Method 2: the more complex method involves using JScript and GraphQL instead of using the Add Task Action. Though it is more complex, it is also much more flexible.

This Tutorial uses the JScript/GraphQL Method because of it’s flexibility and finer control.

4 Likes

##JScript and GraphQL

Have I mentioned how much I love GraphQL? :stuck_out_tongue:

The reason I like this method the most is because we have a lot more control over Tasks when we use GraphQL. With native SambaPOS Actions, all we have is Add Task which can Add or Update a Task, but that is it.

Using GraphQL, we can do more, including overwriting the Content field (instead of appending to it), we can mark Tasks as Completed or not Completed, and we can even Delete Tasks. Check it out:

// Queries
getTask
getTasks

// Mutations
addTask
updateTask
updateTaskState
deleteTask

##Script: ELG Event Log

##ELG Event Log JScript GQL [ELG] (Script)##

Script Name: ELG Event Log JScript GQL
Script Handler: ELG

Script:

function updateLog(terminalName,userName,eventDate,eventName,eventData,taskIdentifier,ticketId,ticketNo,ticketTotal) {
// updateLog('Server','Q','2017-05-31 10:43:10','eName1','eData1','asdfasdf','0','','10.00')
// updateLog('Server','Q','','eName1','eData1')

	// if eventDate parameter is not available, we will use NOW
	var nowDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

	// if taskIdentifier is not available, we will generate one
	var generatedIdent = randomString(32,'aA#');
	
	// the eventDate will contain the ticketDate if this Event is Ticket-related, otherwise it will be blank
	// in that case, we will use nowDate, generated above
	eventDate = typeof eventDate==='undefined' || eventDate=='' || (eventDate.indexOf('TICKET DATE')>-1) ? nowDate : eventDate;
	
	// the taskIdentifier will contain the ticketUid if this Event is Ticket-related, otherwise it will be blank
	taskIdentifier = typeof taskIdentifier==='undefined' || taskIdentifier=='' || (taskIdentifier.indexOf('TICKET UID')>-1) ? '' : taskIdentifier;
	
	ticketId = typeof ticketId==='undefined' || ticketId=='' || (ticketId.indexOf('TICKET ID')>-1) ? 0 : ticketId;
	ticketNo = typeof ticketNo==='undefined' || ticketNo=='' || (ticketNo.indexOf('TICKET NO')>-1) ? '' : ticketNo;
	ticketTotal = typeof ticketTotal==='undefined' || ticketTotal=='' || (ticketTotal.indexOf('TICKET TOTAL')>-1) ? 0 : ticketTotal;
	
	ticketTotal = Helper.Format(ticketTotal);
	
	var workperiodIsOpen = getWorkPeriodData('isOpen');
	var workperiodId = getWorkPeriodData('id');

	var eventCount = 0;

	var taskType = 'ELG Event Log';
	
	var taskName = eventDate;
		taskName+= ' - ' + ticketTotal;
		taskName+= ' - ' + ticketTotal;
		taskName+= ' - ' + ticketId;
		taskName+= ' - ' + ticketNo;
		taskName+= ' - ' + eventName;
		

	// the taskIdentifier will contain the ticketUid if this Event is Ticket-related, otherwise it will be blank
	// if the taskIdentifier is blank, we will use our generatedIdent instead
	// if we did NOT get a taskIdentifier (ie. ticketUid), there is NO Ticket, so empty the Ticket Properties (ticketDate,ticketUid,ticketId,ticketNo)
	// if we DID get a taskIdentifier (ie. ticketUid), there IS a Ticket, so set the Ticket Properties (ticketDate,ticketUid,ticketId,ticketNo)
	var ident      = taskIdentifier!='' ? taskIdentifier : generatedIdent;
	var ticketDate = taskIdentifier=='' ? '' : eventDate;
	var ticketUid  = taskIdentifier=='' ? '' : ident;
		ticketId   = taskIdentifier=='' ? '' : ticketId;
		ticketNo   = taskIdentifier=='' ? '' : ticketNo;
	
	var customData = [];
		customData.push({name:"Id",value:ident});
		customData.push({name:"workperiodId",value:workperiodId});
		customData.push({name:"terminalName",value:terminalName});
		customData.push({name:"userName",value:userName});
		customData.push({name:"eventDate",value:eventDate});
		customData.push({name:"ticketUid",value:ticketUid});
		customData.push({name:"ticketId",value:ticketId});
		customData.push({name:"ticketNo",value:ticketNo});
		customData.push({name:"ticketDate",value:ticketDate});
		customData.push({name:"ticketTotal",value:ticketTotal});
//		customData.push({name:"ticketTendered",value:ticketTendered});
//		customData.push({name:"ticketRemaining",value:ticketRemaining});
//		customData.push({name:"ticketChange",value:ticketChange});


	
	// check to see if we have an EXISTING Task that needs to be UPDATED
	var resp = gqlEXEC(getTask(taskType, ident));
		//return resp;
		resp = JSON.parse(resp);

	var task = resp.data!=null ? resp.data.task : null;
	
	var oldContent = "";
	var oldCustomData = [];
	
	// if the Task exists, append new eventName to the Task Name
	// and update the ticketId and ticketNo
	// and count the events (Custom Data Fields "eventN") to update the Custom Data Field "eventCount"
	// and find the Largest ticketTotal
	if (task) {
		//dlg.ShowMessage(task.name);
				
		// find the Largest ticketTotal
		// this is stored in task.contentText for every event
		// content lines are separated by "\n"
		// each content line has this format:
		// eventNum>date time >>> ticketTotal >>> eventName >>> eventData
		// event1>2017-05-31 09:51:35 >>> 3.00 >>> Order Added >>> itemName>Barena~itemPrice>3.000000~itemQuantity>1
		// so we can split() the line on " >>> " and grab idx 1 to find the ticketTotal at the time of the Event
		
		// split Content Lines
		var contentLines = task.contentText.split("\n");
		//dlg.ShowMessage(contentLines[0]);

		// iterate Content Lines
		var largestTotal = 0;
		for (var c=0; c<contentLines.length; c++) {
			var line = contentLines[c];
			// split the Line and grab idx 1 (ticketTotal at the time of the Event)
			var ttl = line.split(" >>> ");
				ttl = Helper.ToNumber(ttl[1]);
			largestTotal = ttl > largestTotal ? ttl : largestTotal;
		}
		
		// check latest Event ticketTotal to see if it is larger
		largestTotal = Helper.ToNumber(ticketTotal) > largestTotal ? Helper.ToNumber(ticketTotal) : largestTotal;
		
		//dlg.ShowMessage('LT:'+largestTotal);


		// we rebuild the Task Name because we want to retain the Largest Ticket Total through all Events
		// and we also want to update the ticketId and ticketNo with new values once they are available
		// the Task Name has this format:
		// date time - largestTotal - ticketTotal - ticketId - ticketNo - eventName1 - eventName2 - eventName3 - etc
		// 2017-05-31 09:51 - 6 - 3 - 40111 - 14689 - Order Added - Order Added - Order Cancelled - Drawer Opened - Payment Processed - Ticket Paid Full - Ticket Closing
		
		var nameParts = task.name.split(" - ");
		
		taskName = "";
		
		// iterate nameParts and rebuild Task Name
		for (var e=0; e<nameParts.length; e++) {
			if (e==0) {
				taskName += nameParts[e]; // eventDate / ticketDate
			} else if (e==1) {
				taskName += " - " + Helper.Format(largestTotal);
			} else if (e==2) {
				taskName += " - " + Helper.Format(ticketTotal);
			} else if (e==3) {
				taskName += " - " + ticketId;
			} else if (e==4) {
				taskName += " - " + ticketNo;
			} else {
				taskName += " - " + nameParts[e];
			}
		}
		
		// append newest Event to the Task Name
		taskName += ' - ' + eventName;
		
		//dlg.ShowMessage(taskName);
		
		// save old Content
		oldContent = task.contentText;

		// for debugging
		var cd = "";

		// count the Events
		// and save old Custom Data
		for (var c=0; c<task.customData.length; c++) {
			// save old Custom Data
			oldCustomData.push(task.customData[c]);
			if (task.customData[c].name.indexOf('event') > -1 && task.customData[c].name.indexOf('eventCount') < 0) {
				// increment Event Counter
				eventCount++;
			}
			// for debugging
			cd+=task.customData[c].name + " : " + task.customData[c].value + "\r";
		}		

	}
	

	// if we have a Reopened Ticket, we need to update the Task to set it NOT Completed
	if (eventName.indexOf("Ticket Reopened") > -1) {
		isCompleted = 'false';
		// updateTaskByIdentifier(taskTypes, taskIdents, isCompleted, taskName, customData, content, state)
		var resp = gqlEXEC(updateTaskByIdentifier([taskType], [ident], isCompleted, taskName));
		//dlg.ShowMessage(resp);
		//return resp;
	}


	eventCount++;

	// set up the NEW Event
	var eventNum  = "event" + eventCount;
		eventData = nowDate + ' >>> ' + ticketTotal + ' >>> ' + eventName + ' >>> ' + eventData;
	
	// set the Custom Data to update eventCount and add NEW Event
	customData.push({name:"eventCount",value:eventCount});
	customData.push({name:eventNum,value:eventData});

	var isCompleted = eventName=='Ticket Closing' ? 'true' : 'false';
	
	// set the Task Content to APPEND
	var content = eventNum+'>'+eventData;

	// we ALWAYS ADD a Task - this will update existing Tasks with new Custom Data, and APPEND to the Task Content
	// addTasks(taskTypes,taskNames,isCompleted,customData,userName,content,state)
	var resp = gqlEXEC(addTasks([taskType], [taskName], isCompleted, customData, userName, content));
		//return resp;
		//resp = JSON.parse(resp);
	//var task = resp.data.m0;
	
	//ident = task.identifier;

	// if the Ticket is Paid and is Closing, it will be marked IsClosed, so we can UPDATE the Task to mark it Completed
	// or if there is NO Ticket (taskIdentifier / ticketUid is empty), then this Task/Event is not related to a Ticket, so we can mark it Completed as well
	if (eventName=='Ticket Closing' || ticketUid=='' || taskIdentifier=='') {
		isCompleted = 'true';
		// updateTaskByIdentifier(taskTypes, taskIdents, isCompleted, taskName, customData, content, state)
		var resp = gqlEXEC(updateTaskByIdentifier([taskType], [ident], isCompleted, taskName));
		//return resp;
	}
	
	return resp;
}

// gets Last Workperiod Data
function getWorkPeriodData(dataType,date) {
	var SQL = "SELECT [Id],convert(varchar(25),[StartDate],126),convert(varchar(25),[EndDate],126),[StartDescription],[EndDescription] FROM [WorkPeriods] WHERE 1=1 AND [Id] = (SELECT MAX([Id]) FROM [WorkPeriods])";
	var res = sql.Exec(SQL);
	var	wpdata = res[0].split(',');
	var retval = '';
	switch (dataType) {
		case 'id':
			retval = wpdata[0];
			break;
		case 'start':
			retval = wpdata[1].replace('T',' ');
			break;
		case 'end':
			retval = wpdata[2].replace('T',' ');
			break;
		case 'isOpen':
			retval = wpdata[1]==wpdata[2] ? true : false;
			break;
		case 'descStart':
			retval = wpdata[3];
			break;
		case 'descEnd':
			retval = wpdata[4];
			break;
		default:
			retval = wpdata[0];
			break;
	}
	return retval;
}


function randomString(length, chars) {
    var mask = '';
    if (chars.indexOf('a') > -1) mask += 'abcdefghijklmnopqrstuvwxyz';
    if (chars.indexOf('A') > -1) mask += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    if (chars.indexOf('#') > -1) mask += '0123456789';
    if (chars.indexOf('!') > -1) mask += '~`!@#$%^&*()_+-={}[]:";\'<>?,./|\\';
    var result = '';
    for (var i = length; i > 0; --i) result += mask[Math.floor(Math.random() * mask.length)];
    return result;
}


// main GraphQL Execute Function
function gqlEXEC(query, callback) {
	//return query;
    var data = gql.Exec(query);  // returns JSON as String
    var dobj = JSON.parse(data); // converts JSON String to Object
    return data;
};

// Mutation for addTasks
function addTasks(taskTypes,taskNames,isCompleted,customData,userName,content,state) {
    var q = '';
        q+= 'mutation m{';
        for (var t=0; t<taskNames.length; t++) {
            var taskType = taskTypes[t];
            var taskName = taskNames[t];
            q += 'm'+t+': ';
            q+= 'addTask(';
            q+= 'task:{';
            q+= 'taskType:"'+taskType+'"';
            q+= ',name:"'+taskName+'"';
            q+= ',isCompleted:'+isCompleted;
            q+= ',userName:"'+userName+'"';
            q+= typeof(content)==='undefined' ? '' : ',content:' + '"'+content+'"';
            q+= typeof(state)==='undefined' ? '' : ',state:' + '"'+state+'"';
            q+= ',customData:[';
            if (customData) {
                for (var d=0; d<customData.length; d++) {
                    q+= (d==0 ? '' : ',');
                    q+= '{name:"'+customData[d].name+'",value:"'+customData[d].value+'"}';
                }
            }
            q+= ']';
            q+= '}';
            q+= ')';
            q+= '{id,name,identifier,content,isCompleted,userName,customData{name,value}}';
            q += ((t+1) != taskNames.length ? ', ' : '');
        }
        q+= '}';
    return q;
};

// Query for getTask
function getTask(taskType, ident) {
    var q = '';
        q+= '{task:getTask(';
        q+= 'taskType:"'+taskType+'"';
        q+= 'identifier:"'+ident+'"';
        q+= ')';
        q+= '{id,isCompleted,identifier,name,state,content,contentText,customData{name,value},stateLog{state,start,end},stateDuration{state,duration},startDate,endDate,userName}';
        q+= '}';
    return q;
};

// Mutation for updateTask (by identifier and taskType)
function updateTaskByIdentifier(taskTypes, taskIdents, isCompleted, taskName, customData, content, state){
    var q = 'mutation m {';
    for (var t=0; t<taskIdents.length; t++) {
        var taskIdent = taskIdents[t];
        var taskType = taskTypes[t];
        q += 'm'+t+': updateTask(';
        q += 'identifier:"'+taskIdent+'"';
        q += ', taskType:"'+taskType+'"';
        q += ', task:{';
        q += 'taskType:"'+taskType+'"';
        q += (isCompleted!='' ? ',isCompleted:'+isCompleted : '');
        q += (taskName ? ', name:"'+taskName+'"' : '');
        q += content ? ',content:"'+content+'"' : '';
        q += state ? ',state:"'+state+'"' : '';
        q += ',customData:[';
        if (customData) {
            for (var d=0; d<customData.length; d++) {
                q+= (d==0 ? '' : ',');
                q+= '{name:"'+customData[d].name+'",value:"'+customData[d].value+'"}';
            }
        }
        q+= ']';
        q += '}';
        q += ')';
        q+= '{id,isCompleted,identifier,name,state,content,contentText,customData{name,value},stateLog{state,start,end},stateDuration{state,duration},startDate,endDate,userName}';
        //q += '}';
        q += ((t+1) != taskIdents.length ? ', ' : '');

    }
    	q += '}';
    return q;
};


1 Like

#Actions

We will need an Action for Execute Script

##Action: ELG Execute Script


And some Actions to store some Local Program Settings will be helpful …

##Action: ELG Store eventName


##Action: ELG Store eventData

1 Like

#Rules

You can create Rules to log information for nearly any Event that occurs in the System. In general, all of the Rules will follow the same format and contain the same Actions:

  • set the eventName with Action ELG Store eventName
  • set the eventData with Action ELG Store eventData
  • execute the Script with Action ELG Exec Script, which always contains this:
ELG.updateLog('{:CURRENTTERMINAL}','{:CURRENTUSER}','{TICKET DATE} {TICKET TIME}','{SETTING:ELG eventName}','{SETTING:ELG eventData}','{TICKET UID}','{TICKET ID}','{TICKET NO}','{TICKET TOTAL}')

##ELG Order Added [Order Added] (Rule)##

Rule Name: ELG Order Added
Event Name: Order Added
Rule Tags:
Custom Constraint List (0):
Execute Rule if: Matches

##Actions (3):##

ELG Store eventName

Constraint: (none)

ELG eventName: Order Added
ELG Store eventData

Constraint: (none)

ELG eventData: itemName>{NAME}~itemPrice>{PRICE}~itemQuantity>{QUANTITY}
ELG Exec Script

Constraint: (none)

handler.func(): ELG.updateLog('{:CURRENTTERMINAL}','{:CURRENTUSER}','{TICKET DATE} {TICKET TIME}','{SETTING:ELG eventName}','{SETTING:ELG eventData}','{TICKET UID}','{TICKET ID}','{TICKET NO}','{TICKET TOTAL}')
runBG:

##ELG Order Cancelled [Order Cancelled] (Rule)##

Rule Name: ELG Order Cancelled
Event Name: Order Cancelled
Rule Tags:
Custom Constraint List (0):
Execute Rule if: Matches

##Actions (3):##

ELG Store eventName

Constraint: (none)

ELG eventName: Order Cancelled
ELG Store eventData

Constraint: (none)

ELG eventData: itemName>{NAME}~itemPrice>{PRICE}~itemQuantity>{QUANTITY}
ELG Exec Script

Constraint: (none)

handler.func(): ELG.updateLog('{:CURRENTTERMINAL}','{:CURRENTUSER}','{TICKET DATE} {TICKET TIME}','{SETTING:ELG eventName}','{SETTING:ELG eventData}','{TICKET UID}','{TICKET ID}','{TICKET NO}','{TICKET TOTAL}')
runBG:

##ELG Order Price Changed [Automation Command Executed] (Rule)##

Rule Name: ELG Order Price Changed
Event Name: Automation Command Executed
Rule Tags:
Custom Constraint List (1):
Execute Rule if: Matches
Automation Command NameEqualsPrice Change

##Actions (3):##

ELG Store eventName

Constraint: (none)

ELG eventName: Order Price Changed
ELG Store eventData

Constraint: (none)

ELG eventData: itemName>{NAME}~itemPrice>{PRICE}~itemQuantity>{QUANTITY}
ELG Exec Script

Constraint: (none)

handler.func(): ELG.updateLog('{:CURRENTTERMINAL}','{:CURRENTUSER}','{TICKET DATE} {TICKET TIME}','{SETTING:ELG eventName}','{SETTING:ELG eventData}','{TICKET UID}','{TICKET ID}','{TICKET NO}','{TICKET TOTAL}')
runBG:

:warning: NOTE: Sort your Rules so that this Rule comes after your Price Change Rule


#More Rules

I have a lot of other Rules to Log information and they all follow the same format as the Rules posted above.

All of the Rules shown below are in the DB Tools import file in a later post.

1 Like

#Reports (basic)

##ELG Event Log [0] (Report)##

Report Name: ELG Event Log
Page Size: 25cm
Display in Report Explorer: checked
Visual Printing: unchecked

Template:

[ELG Event Log:1,9]
@{REPORT TASK DETAILS:T.Id.asc:(TST=ELG Event Log):,}

>>{REPORT TASK DETAILS:T.Id,[T.Identifier]+' (Task Id/Ident)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.terminalName,[TSC.userName]+' (Terminal/User)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.ticketId,[TSC.ticketNo]+' (Ticket Id/No)':(TST=ELG Event Log) && T.Id=$1}
>{REPORT TASK DETAILS:[T.Name]:(TST=ELG Event Log) && T.Id=$1}

{REPORT TASK DETAILS:T.ContentText:(TST=ELG Event Log) && T.Id=$1}


##ELG Event Log Voids Gifts Cancels PriceChange Reopen [0] (Report)##

Report Name: ELG Event Log Voids Gifts Cancels PriceChange Reopen
Page Size: 25cm
Display in Report Explorer: checked
Visual Printing: unchecked

Template:

[ELG Event Log:1,9]
@{REPORT TASK DETAILS:T.Id.asc:(TST=ELG Event Log) && 'content.Contains("Void")' OR 'content.Contains("Gift")' OR 'content.Contains("Price Change")' OR 'content.Contains("Reopen")':,}

>>{REPORT TASK DETAILS:T.Id,[T.Identifier]+' (Task Id/Ident)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.terminalName,[TSC.userName]+' (Terminal/User)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.ticketId,[TSC.ticketNo]+' (Ticket Id/No)':(TST=ELG Event Log) && T.Id=$1}
>{REPORT TASK DETAILS:[T.Name]:(TST=ELG Event Log) && T.Id=$1}

{REPORT TASK DETAILS:T.ContentText:(TST=ELG Event Log) && T.Id=$1}

#Reports (SQL)

##ELG Event Log SQL [@@ELGeventLogSQL] (Script)##

Script Name: ELG Event Log SQL
Script Handler: @@ELGeventLogSQL

Script:

declare @taskId int = @1
declare @taskType varchar(20) = '@2'
declare @isCompleted int = @3

SELECT
-- t.[Id]
--,tt.[Name] as [TaskType]
--,t.[Completed]
--,t.[Name]
--,t.[Content]
 cdName
,cdValue
--,t.[CustomData]
--,t.[UserName]
--,t.[Identifier]
--,t.[StartDate]
--,t.[EndDate]
--,t.[LastUpdateTime]
--,t.[State]
--,t.[StateLog]
FROM [Tasks] t
JOIN [TaskTypes] tt on tt.[Id] = t.[TaskTypeId]

CROSS APPLY OPENJSON(t.[CustomData])
-- this WITH portion allows explicit definition of the schema JSON Keys for output
-- and gives references to the columns/field above in the SELECT portion
WITH (   
 cdName         varchar(1000) '$.N'
,cdValue        varchar(1000) '$.V'
) jsonData

WHERE 1=1
AND t.[Id] = @taskId
AND t.[Completed] = @isCompleted
AND tt.[Name] = @taskType
AND cdName != 'eventCount'
AND cdName LIKE 'event%'

ORDER BY t.[Id], t.[LastUpdateTime]


##ELG Event Log SQL Completed [0] (Report)##

Report Name: ELG Event Log SQL Completed
Page Size: 25cm
Display in Report Explorer: checked
Visual Printing: unchecked

Template:

[ELG Event Log SQL:1,9]
@{REPORT TASK DETAILS:T.Id.asc:(TST=ELG Event Log) && T.Completed=true:,}

>>{REPORT TASK DETAILS:T.Id,[T.Identifier]+' (Task Id/Ident)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.terminalName,[TSC.userName]+' (Terminal/User)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.ticketId,[TSC.ticketNo]+' (Ticket Id/No)':(TST=ELG Event Log) && T.Id=$1}
>{REPORT TASK DETAILS:T.Name:(TST=ELG Event Log) && T.Id=$1}

@@ELGeventLogSQL:$1,ELG Event Log,1

##ELG Event Log SQL NOT Completed [0] (Report)##

Report Name: ELG Event Log SQL NOT Completed
Page Size: 25cm
Display in Report Explorer: checked
Visual Printing: unchecked

Template:

[ELG Event Log SQL:1,9]
@{REPORT TASK DETAILS:T.Id.asc:(TST=ELG Event Log) && T.Completed=false:,}

>>{REPORT TASK DETAILS:T.Id,[T.Identifier]+' (Task Id/Ident)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.terminalName,[TSC.userName]+' (Terminal/User)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.ticketId,[TSC.ticketNo]+' (Ticket Id/No)':(TST=ELG Event Log) && T.Id=$1}
>{REPORT TASK DETAILS:T.Name:(TST=ELG Event Log) && T.Id=$1}

@@ELGeventLogSQL:$1,ELG Event Log,0

##ELG Event Log SQL Voids Gifts Cancels PriceChange Reopen [@@ELGeventLogSQLVoidsGiftsCancels] (Script)##

Script Name: ELG Event Log SQL Voids Gifts Cancels PriceChange Reopen
Script Handler: @@ELGeventLogSQLVoidsGiftsCancels

Script:

declare @taskId int = @1
declare @taskType varchar(20) = '@2'
declare @isCompleted int = @3

SELECT
-- t.[Id]
--,tt.[Name] as [TaskType]
--,t.[Completed]
--,t.[Name]
--,t.[Content]
 cdName
,cdValue
--,t.[CustomData]
--,t.[UserName]
--,t.[Identifier]
--,t.[StartDate]
--,t.[EndDate]
--,t.[LastUpdateTime]
--,t.[State]
--,t.[StateLog]
FROM [Tasks] t
JOIN [TaskTypes] tt on tt.[Id] = t.[TaskTypeId]

CROSS APPLY OPENJSON(t.[CustomData])
-- this WITH portion allows explicit definition of the schema JSON Keys for output
-- and gives references to the columns/field above in the SELECT portion
WITH (   
 cdName         varchar(1000) '$.N'
,cdValue        varchar(1000) '$.V'
) jsonData

WHERE 1=1
AND t.[Id] = @taskId
--AND t.[Completed] = @isCompleted
AND (t.[Content] LIKE '%Void%' OR t.[Content] LIKE '%Gift%' OR t.[Content] LIKE '%Cancel%' OR t.[Content] LIKE '%Price Change%' OR t.[Content] LIKE '%Ticket Reopen%')
AND tt.[Name] = @taskType
AND cdName != 'eventCount'
AND cdName LIKE 'event%'
--AND (cdValue LIKE '%Void%' OR cdValue LIKE '%Gift%' OR cdValue LIKE '%Cancel%' OR cdValue LIKE '%Price Change%')

ORDER BY t.[Id], t.[LastUpdateTime]


##ELG Event Log SQL Voids Gifts Cancels PriceChange Reopen [0] (Report)##

Report Name: ELG Event Log SQL Voids Gifts Cancels PriceChange Reopen
Page Size: 25cm
Display in Report Explorer: checked
Visual Printing: unchecked

Template:

[ELG Event Log SQL:1,9]
@{REPORT TASK DETAILS:T.Id.asc:(TST=ELG Event Log) && 'content.Contains("Void")' OR 'content.Contains("Gift")' OR 'content.Contains("Price Change")' OR 'content.Contains("Reopen")':,}

>>{REPORT TASK DETAILS:T.Id,[T.Identifier]+' (Task Id/Ident)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.terminalName,[TSC.userName]+' (Terminal/User)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.ticketId,[TSC.ticketNo]+' (Ticket Id/No)':(TST=ELG Event Log) && T.Id=$1}
>{REPORT TASK DETAILS:T.Name:(TST=ELG Event Log) && T.Id=$1}

@@ELGeventLogSQLVoidsGiftsCancels:$1,ELG Event Log,0

#DB Tools

If you are unfamiliar with DB Tools files, see How to use DB Tools.


Separate Imports:

ELG Task Type.zip (548 Bytes)

ELG JScript.zip (4.3 KB)

ELG Automation.zip (1.7 KB)

ELG Reports.zip (1.3 KB)


Everything in 1 File:

!ELG Event Log ALL.zip (6.4 KB)

Great timing, this was something I was moving to look in to next.

In my dive shop we have only one till, but several terminals where we prepare customer bills and also record expenses using SambaPOS. We don’t have many til operations throughout the day, and because some of the terminals are nowhere near the actual till I don’t have any rules to automatically open the cash drawer, instead I have an “Open Drawer” button always visible and the usage of this button is logged.

We are often over of under at the end of the day, but it is nearly always something stupid (in a manic rush, we forgot we took a payment, or paid an expense). So the cash drawer log will help to narrow down what section of the CCTV need to be reviewed. But mroe of a ticket action log, coupled with the cash drawer log will really help me identify the times we open the cash drawer without having just settled a ticket.

I have created a Monster (this Report is included in the DB Tools file) …

4 Likes

Trying to get this to work in v5.7.10 but there appears to be a change in the updateTask mutation that is throwing an error. I can get this working in v5.3.0 but not v5.7.10.

I have this setup on a sample database with no further modification other than importing the db tools tile.

If I just test the function in the script editor for like this:

updateLog('Server','Administrator','','test1','test1')

On v5.3.0 I get this response:

{"data":{"m0":{"id":4,"isCompleted":true,"identifier":"aGsLU17t0IzYvCSlAgQfCRcfbBkNJbor","name":"2024-07-11 05:55:42 - 0.00 - 0.00 - 0 -  - test1","state":null,"content":"event1>2024-07-11 05:55:42 >>> 0.00 >>> test1 >>> test1","contentText":"event1>2024-07-11 05:55:42 >>> 0.00 >>> test1 >>> test1","customData":[{"name":"workperiodId","value":"1"},{"name":"terminalName","value":"Server"},{"name":"userName","value":"Administrator"},{"name":"eventDate","value":"2024-07-11 05:55:42"},{"name":"ticketTotal","value":"0.00"},{"name":"eventCount","value":"1"},{"name":"event1","value":"2024-07-11 05:55:42 >>> 0.00 >>> test1 >>> test1"}],"stateLog":[],"stateDuration":[],"startDate":"2024-07-11T04:55:42.913Z","endDate":"2024-07-11T04:55:42.9190349Z","userName":"Administrator"}}}

(i.e. it is responding with the task details)

But on v5.7.10, I get this response:

{"data":null,"errors":[{"message":"Error trying to resolve updateTask.","locations":[{"line":0,"column":0}]}]}

Hence my believe that the parameters for the updateTask mutation has changed recently. Does anyone know what has chanegd so I can modify the script accordingly? Thanks!

1 Like