evcz Posted July 25, 2019 Share Posted July 25, 2019 Hi, do you think having an SQL query to delete old (older then 1 month for example) tickets filled by anonymous users completely removed would be doable without breaking WHMCS? Basically we are talking about sales requests or similar things. It makes sense to retain tickets from account, but the ones from anonymous sources should be deleted in order to have the least possible personal data stored. Is there any example to start from or any already existing module? Thank you 0 Quote Link to comment Share on other sites More sharing options...
Kian Posted July 25, 2019 Share Posted July 25, 2019 Hi, Use any of the available cron hook points (AfterCronJob, DailyCronJob, DailyCronJobPreEmail, PreCronJob) and run a query to select the ID of all "junk" tickets. Once finished, use DeleteTicket inside a foreach to delete all tickets you have previously selected. 0 Quote Link to comment Share on other sites More sharing options...
evcz Posted July 25, 2019 Author Share Posted July 25, 2019 (edited) hooking at the cronjob make sense, but my main question is running these two queries: DELETE FROM `tblticketreplies` WHERE `userid` = 0 AND `date` < DATE_SUB(NOW(), INTERVAL 1 MONTH) DELETE FROM `tbltickets` WHERE `userid` = '0' AND `lastreply` < DATE_SUB(NOW(), INTERVAL 1 MONTH) is safe to run and do not leave things broken behind the only thing left broken behind are the attachments, but those are taken care on 7.8 beta with automatic old attachments pruning another incosistency is removing a ticket before all tickets before their respective ticket replies are deleted, but that's not a big deal, just a metter of delayed days Edited July 25, 2019 by evcz 0 Quote Link to comment Share on other sites More sharing options...
brian! Posted July 25, 2019 Share Posted July 25, 2019 1 minute ago, evcz said: the only thing left broken behind are the attachments, but those are taken care on 7.8 beta with automatic old attachments pruning it would leave behind replies to those tickets too, as any ticket replies (from admins or users) would still be in tblticketreplies - a similarSQL query wouldn't work as is because all admin replies have the userid of 0, so it would need to be a more nuanced query... Kian's idea of using the API would get rid of both the original ticket and any replies. either way, i'd suggest making a copy of the database before running any sql queries like that. 0 Quote Link to comment Share on other sites More sharing options...
brian! Posted July 25, 2019 Share Posted July 25, 2019 11 minutes ago, evcz said: another incosistency is removing a ticket before all tickets before their respective ticket replies are deleted, but that's not a big deal, just a metter of delayed days note what I said about ALL admin replies to tickets having a userid of 0 - that replies query would delete replies to ALL (clients and non clients) tickets between those dates... you might have to join the tables to get an accurate list f valid tickets to delete. 0 Quote Link to comment Share on other sites More sharing options...
evcz Posted July 25, 2019 Author Share Posted July 25, 2019 ouch, didn't know that admin replies had userid 0 then this as a select makes more sense then SELECT * FROM tbltickets RIGHT OUTER JOIN tblticketreplies ON tbltickets.id = tblticketreplies.tid WHERE tbltickets.userid = '0' AND tbltickets.status = 'Closed' and tbltickets.date < DATE_SUB(NOW(), INTERVAL 1 MONTH) change the SELECT * with DELETE tbltickets, tbltitcketreplies and we should be good. how does it looks to you? 0 Quote Link to comment Share on other sites More sharing options...
brian! Posted July 25, 2019 Share Posted July 25, 2019 1 hour ago, evcz said: how does it looks to you? it looks fine to me, but always backup the db before running a delete query. 0 Quote Link to comment Share on other sites More sharing options...
evcz Posted August 6, 2019 Author Share Posted August 6, 2019 Pulled the trigger and did DELETE tbltickets, tblticketreplies FROM tbltickets RIGHT OUTER JOIN tblticketreplies ON tbltickets.id = tblticketreplies.tid WHERE tbltickets.userid = '0' AND tbltickets.status = 'Closed' and tbltickets.date < DATE_SUB(NOW(), INTERVAL 6 MONTH) to remove tickets from anonymous users older then 6 months, now just need to wait for the next WHMCS version for the auto pruning of attachments before doing the delete here and we should be fine 🙂 0 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.