Recomendation for 'Marking' tickets via Script or SQL INSERT

V5 Question
1

OK, so following recent posts regarding the PMS integration I am trying to work the best way to separate over the counter tickets from PMS posted tickets (Cash & Card vs Room Charge)

As the over the counter sales will also be transferred at EOD what would also be good would be a way to mark the ticket as part of the script for EOD.

What I am thinking is either ticket tag or ticket state.

A room charge would set a ticket state or tag of PMS-Transfer = RoomCharge
The end of day script would set a ticket state of OMS-Transfer = EODTransfer

Any thoughts so far?

The RoomCharge state shouldn’t be to hard as these are posted per ticket they can be implemented before ticket closing however the EODTransfer will obviously be at end of day after ticket closed.

Do we have any script helpers for ticket tags or states?

Am thinking a for loop to run through all the tickets from the work period.
The tag/state either causing a skipped loop in the script or possibly a filter on the SQL ticket list call.

2

Think I have got this sorted, anyone see any potential issues with the following?

Ticket Tag

function setTicketChargesTag(inputTicketId,inputChargeIds){
	var currentTags						= getTicketTags(inputTicketId);
	if (!currentTags || currentTags == '[]'){
		insertTicketChargesTag(inputTicketId,'[{"TN":"NewBook Charge IDs","TT":0,"TV":"'+inputChargeIds+'"}]');
		} else {
		var currentTagsSliced			= currentTags.slice(0,-1);
		var newTagsString				= ',{"TN":"NewBook Charge IDs","TT":0,"TV":"'+inputChargeIds+'"}]';
		insertTicketChargesTag(inputTicketId,currentTagsSliced+newTagsString);
		}
}

Ticket State

function setTicketTransferedState(inputTicketId,inputTime){
	var currentStates					= getTicketStates(inputTicketId);
	if (!currentStates || currentStates == '[]'){
		insertTicketTransferedState(inputTicketId,'[{"D":"\/Date('+inputTime+'+0000)\/","S":"Transfered","SN":"NewBook","SV":""}]');
		} else {
		var currentStatesSliced			= currentStates.slice(0,-1);
		var newStatesString				= ',{"D":"\/Date('+inputTime+'+0000)\/","S":"Transfered","SN":"NewBook","SV":""}]';
		insertTicketTransferedState(inputTicketId,currentStatesSliced+newStatesString);
		}
}

SQL Qry/Exec

function getTicketTags(inputTicketId){
	qry = "SELECT [TicketTags]												";
	qry += "FROM [Tickets] 													";
	qry += "WHERE [Id]= '"+inputTicketId+"'									";										//--QRY Variable
	var TicketTags = sql.Query(qry).First;																			//--SQL Query responce
	return TicketTags;																								//--Return
}

function insertTicketChargesTag(inputTicketId,inputTag){
	qry = "UPDATE [Tickets]													";
	qry += "SET [TicketTags] = '"+inputTag+"'								";
	qry += "WHERE [Id]= '"+inputTicketId+"'									";										//--QRY Variable
	var TicketTags = sql.ExecSql(qry);																				//--SQL Query responce
}

function getTicketStates(inputTicketId){
	qry = "SELECT [TicketStates]											";
	qry += "FROM [Tickets] 													";
	qry += "WHERE [Id]= '"+inputTicketId+"'									";										//--QRY Variable
	var TicketTags = sql.Query(qry).First;																			//--SQL Query responce
	return TicketTags;																								//--Return
}

function insertTicketTransferedState(inputTicketId,inputState){
	qry = "UPDATE [Tickets]													";
	qry += "SET [TicketStates] = '"+inputState+"'							";
	qry += "WHERE [Id]= '"+inputTicketId+"'									";										//--QRY Variable
	var TicketTags = sql.ExecSql(qry);																				//--SQL Query responce
}

Preliminary testing seems good, no issues yet.

3

Interesting implementation. Would be great if we had API for this instead (which I am sure is on the To Do list), but in the meantime, if this works for you then great.

The only real issue is that you need to really have a set/defined list of tags that you know should be there. The problem could arise when you add more tags via automation that are not accounted for here in your script, since you are hard-coding the list. This is where the API should take care of dynamic nature of tagging, much in the sme way the Entity API handles states and Custom Data fields.

4

It seams to work as expected, and samba recognizes the tags without problem.
The undefined or [] will cover no tag or [] presumably is had a tag but not anymore by inserting a fresh array.
Otherwise removing the ending ] and slipping it in on the end of the tag list.
Seems ok so far :smile:

5

Ahh, ok I see what you’re doing there. You are in fact attempting to take into account any state or tag that already exists, and either writing a new list or appending to the current one. What about duplicate tags/states? You do not appear to be checking for that. Or maybe the last-listed state/tag overrides any previous values for that particular state/tag? This is where an API would help, because you wouldn’t need to care.

6

The script which is using these in theory should only ever look to a ticket once and after creating the api request to charge to PMS it takes the corresponding charge/payment ids and tags them into the ticket.

7

Hi @JTRTech,

I have implemented a script that adds tags to a ticket also. The problem is that I do not see the newly created tags. If I close the ticket window and open it again I can see the new tags.

It seems to be a refreshing problem. Did you have this problem? If so, do you know how to solve it?

8

I didn’t as my scripts were purely background and only for reference encase there was an issue with the sync between Samba and PMS.
Editing SQL while ticket is already open is probably not going to update the visible info of the open ticket…
Opening a ticket twice (on two terminals) doesn’t update in this way so wasnt even a thought I went into (plus didnt matter to my use)
Closest you might get is if you use the GraphQL API but since that is just a non UI interface I expect it will act the same as whatever happens if you make changes on a 2nd terminal.

9

@rodrigorila I am wondering why you would edit SQL directly if you have a ticket open, why wouldn’t automation solve that?