Update TicketTags - correction of SerialNumber

V5 Question
1

Hello,

Tonight - end of the month, in 12 hours - I need to correct all the doubles that have been created with my serialnumber problem:

  • put a “anulado” stamp on each printed ticket - done.
  • Update the Ticket Tag of the double ticket with the new serial number
  • Reprint all double tickets with the correct serial number

Below the excel sheet with the OLD ticket tags.
The new serial numbers will depend on the last one printed this night when closing the working period.

Unfortunately there is no TicketTags table but TicketTags value in the Tickets Table.
And this TicketTags value is kind of weird, so I know for sure that I’ll have syntax errors when trying to update my ticket tags, that’s why I am asking for help :slight_smile:

UpdateTicketTags.zip (41.3 KB)

Marc

2

Maybe run a series of UPDATE statements like the following in SSMS. It won’t let me post all of them due to character limit in the Forum …

UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160128"}','{"TN":"SunatNumber","TT":0,"TV":"163118"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='9'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160149"}','{"TN":"SunatNumber","TT":0,"TV":"163119"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='35'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160153"}','{"TN":"SunatNumber","TT":0,"TV":"163120"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='46'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160168"}','{"TN":"SunatNumber","TT":0,"TV":"163121"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='51'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160169"}','{"TN":"SunatNumber","TT":0,"TV":"163122"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='67'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160170"}','{"TN":"SunatNumber","TT":0,"TV":"163123"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='70'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160171"}','{"TN":"SunatNumber","TT":0,"TV":"163124"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='71'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160172"}','{"TN":"SunatNumber","TT":0,"TV":"163125"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='82'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160173"}','{"TN":"SunatNumber","TT":0,"TV":"163126"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='83'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160175"}','{"TN":"SunatNumber","TT":0,"TV":"163127"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='88'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160176"}','{"TN":"SunatNumber","TT":0,"TV":"163128"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='76'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160178"}','{"TN":"SunatNumber","TT":0,"TV":"163129"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='85'
UPDATE [Tickets] SET [TicketTags]=REPLACE([TicketTags],'{"TN":"SunatNumber","TT":0,"TV":"160179"}','{"TN":"SunatNumber","TT":0,"TV":"163130"},{"TN":"Corregido","TT":0,"TV":"1"}') WHERE [TicketNumber]='99'

Those ^ were auto-generated in Excel using some CONCATENATE functions …

image.png1922×452 59.8 KB

3

Another way to do it would be to use JScript.

Convert your XLS file to CSV and have it only contain:

  • TicketNumber
  • Old SunatNumber
  • (optional) New SunatNumber
  • (optional) Corregido

Then:

  • use file.ReadFromFile(fileName) Helper to read the CSV line by line.
  • split the 3 columns (by comma) into variables.
  • use sql.ExecSql(stmt) Helper to execute crafted UPDATE statements.
  • (optional) use cmd.Execute('name:value') Helper to execute an Automation Command that fires a Print Job containing the ticketId

image.png696×340 15.3 KB

#Script

function correct(serialStart) {
	serialStart = typeof serialStart!=='undefined' && serialStart!=='' ? serialStart : 163118;

	var fileName = "D:/Programs/POS/UpdateTicketTags/badserials.csv";

	var fi = file.ReadFromFile(fileName);
	
	var lines = fi.split("\r\n");

	var serialInc = serialStart;

	var counter = 0;
	
	for (var a=1; a<lines.length; a++) {
		var line = lines[a];
		
		if (line != "") {
			var cols = line.split(',');
			
			var ticketNumber = cols[0];
			var oldSerial = cols[1];
			var newSerial = serialInc;
			var corregido = 1;
			
			// find matching (old) record
			qry = "SELECT [TicketTags] FROM [Tickets] WHERE [TicketNumber]='"+ticketNumber+"'";
			qry+= " AND [TicketTags] LIKE '%SunatNumber%'";
			qry+= " AND [TicketTags] LIKE '%"+oldSerial+"%'";

			var res = sql.Exec(qry);

			// if matching record found, UPDATE the record
			if (typeof res[0]!=='undefined' && res[0]!='') {
				var qry = "UPDATE [Tickets] SET [TicketTags]=";
					qry+= "REPLACE(";
					qry+= "[TicketTags],";
					qry+= "'{\"TN\":\"SunatNumber\",\"TT\":0,\"TV\":\""+oldSerial+"\"}',";
					qry+= "'{\"TN\":\"SunatNumber\",\"TT\":0,\"TV\":\""+newSerial+"\"},";
					qry+= "{\"TN\":\"Corregido\",\"TT\":0,\"TV\":\""+corregido+"\"}'";
					qry+= ")";
					qry+= " WHERE [TicketNumber]='"+ticketNumber+"'"
					
				//dlg.ShowMessage(sql);
				
				var res = sql.Exec(qry);
				
				// find matching (new) record
				qry = "SELECT [TicketTags] FROM [Tickets] WHERE [TicketNumber]='"+ticketNumber+"'";
				qry+= " AND [TicketTags] LIKE '%SunatNumber%'";
				qry+= " AND [TicketTags] LIKE '%Corregido%'";
				qry+= " AND [TicketTags] LIKE '%"+newSerial+"%'";
	
				var res = sql.Exec(qry);
				
				if (typeof res[0]!=='undefined' && res[0]!='') {
					counter++;
				}

				serialInc++;
			}
		}
	}
	
	return "Read "+(lines.length-2)+" lines, found "+(serialInc-serialStart)+" matching Records, and Updated "+counter+" Records.";
}
4

Thank you @QMcKay

I’ll be doing it this night :slight_smile: