Some of my clients have submitted tickets from different addresses over the course of time, and as I teach them to access the system to view all their tickets from one account, I would like to merge all the tickets they have created from different email addresses into one account (user). I cannot find an easy way to do a batch transfer of tickets from one user to another, although I am able to change the user per individual ticket. Would it be possible to do batch changes?
Currently, as far as I know, this is not possible (as you already found out).
I've done something like this (and I've done it just recently), but the way I ended up doing it isn't for the feint of heart. I present this mostly for informational purposes, please please please make a back up prior to trying any of this.Here's what I did to do it. There is probably an easier way but this is how I did it.Always Back up your Database before making changes like this.Using your preferred 3rd party vendor SQL software and open your database. (I use Navicat Lite)I scrolled through ost_user_email and looked for obvious things (misspelled email addresses) and noted the user_id. I looked up the user_id in ost_user and then used find to see how many instances there were for similar names.At this point you have two options. They are use the ui to one at a time re-assign the tickets to one person (Safer), or use SQL queries (to try to do it a lot faster).Option A - using the uiI personally found that many of the multiples only had 1 or 2 tickets with the bad emails. But your case may vary. This is probably the safer of the two options.Pick one user to keep and then make a list of all the id's to delete. DO NOT DELETE THEM YET.Use search to search for the misspellings / bad emails and assign those to the one you want to keepnow deleted the ones you want to get rid of (in both ost_user and ost_user_email).
Option B - using SQLPick one user to keep and then make a list of the Keep and Delete ids. DO NOT DELETE THEM YET.Run the following SQL Query:UPDATE ost_ticket SET user_id='#1',SET user_email_id='#2' WHERE user_id='old#'
notes:
#1 is the user id # that you kept. #2 is the user id email (from os_user_email) that you kept.old# is the old number that it was before (the ones that you deleted)Then run this.UPDATE ost_thread SET user_id='#1' WHERE user_id='old#'This updates the ticket threads so that the comments/message refer to an existing user. I have no idea what happens if this isn't done, but I presume that it would throw a getid() error.Then run this.UPDATE ost_ticket_collaborator SET user_id='#1' WHERE user_id='old#'This changes all the collaborators on closed tickets to be the proper user.Now that you have made all those changes, you can delete the bad/dupe ones.
