user points touch-up after Kunena migration
user points touch-up after Kunena migration
After our sucessful Kunena migration, I decided to tidy up a bit by updating the points to catch some minor stuff that was not covered by the migration. Thought I would post this here to share with other users...
I wanted to update all migrated users points with some of their legacy Kunena activity as follows
Previous forum post = 1 point
Previous forum reply = 1 point
Previous Thank-You given = 3 points
Previous Thank-You received = 11 points
so I applied these queries ...
( In the queries below - JXX = 'your joomla db prefix')
UPDATE `JXX_cjforum_users`,`JXX_kunena_users` SET JXX_cjforum_users.points= JXX_cjforum_users.points+ (JXX_kunena_users.thankyou * 11) WHERE JXX_cjforum_users.id = JXX_kunena_users.userid
--query above is one time thankyou received points update after migration
UPDATE `JXX_cjforum_users`,`JXX_kunena_users` SET JXX_cjforum_users.points= JXX_cjforum_users.points + ((SELECT count(*) FROM `JXX_kunena_thankyou` where `userid` = JXX_kunena_users.userid)*3) WHERE JXX_cjforum_users.id = JXX_kunena_users.userid
--query above is one time thankyou given points update after migration
UPDATE `JXX_cjforum_users`,`JXX_kunena_users` SET JXX_cjforum_users.points= JXX_cjforum_users.points + (SELECT count(*) FROM `JXX_kunena_messages` WHERE parent<>0 AND `userid` = JXX_kunena_users.userid) WHERE JXX_cjforum_users.id = JXX_kunena_users.userid
--query above is one time reply post points update after migration
UPDATE `JXX_cjforum_users`,`JXX_kunena_users` SET JXX_cjforum_users.points= JXX_cjforum_users.points + (SELECT count(*) FROM `JXX_kunena_topics` WHERE `first_post_userid` = JXX_kunena_users.userid) WHERE JXX_cjforum_users.id = JXX_kunena_users.userid
--query above is one time initial post points update after migration
( Obviously my assigned point values are chosen arbitrarily, but you can adjust to taste!) ?
This worked great after a migration from Kunena 5.1.13 to CJforum 3.14 (July 2019). I doubt very much these tables would change in the future, but if major version upgrades have happened by the time you read this, you might want to check the structure before applying these queries... (to your DEV server first, of course!) ?