• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Run query on user upgrade (code example) 1.x

No permission to download

Jake Bunce

XenForo moderator
Staff member
#1
Jake Bunce submitted a new resource:

Run query on user upgrade (code example) (version 1.x) - Useful if you need to grant additional awards or status when a user pays for an upgrade.

You can edit the library/ExtendUserUpgrade/UserUpgradeModel.php file to set your own query or code to execute when a user pays for an upgrade. The file already includes a query that I used for the person I made this for:

Code:
<?php

class ExtendUserUpgrade_UserUpgradeModel extends XFCP_ExtendUserUpgrade_UserUpgradeModel
{
	public function upgradeUser($userId, array $upgrade, $allowInsertUnpurchasable = false, $endDate = null)
	{
		$retval = parent::upgradeUser($userId, $upgrade,...
Read more about this resource...
 

Andy.N

Well-known member
#3

Jake Bunce

XenForo moderator
Staff member
#4
Jake,
This is extremely useful. Can't believe I overlook this.
But more importantly, can this addon be built so that we don't edit core XF files? I probably will forget about this when I upgrade XF over time.
You edit the files for this addon, not the core files. They won't be overwritten during an upgrade.
 

Andy.N

Well-known member
#5
Also, I have multiple upgrades.
How can I make one query when upgradeID1 happens and another query when upgradeID2 happens.
Thanks
 

Jake Bunce

XenForo moderator
Staff member
#7
The $upgrade parameter is the xf_user_upgrade record. You can check fields from that table. For example:

Code:
if ($upgrade['user_upgrade_id'] == 1)
{
	...
}
else if ($upgrade['user_upgrade_id'] == 2)
{
	...
}
That should do it.
 

Andy.N

Well-known member
#8
The $upgrade parameter is the xf_user_upgrade record. You can check fields from that table. For example:

Code:
if ($upgrade['user_upgrade_id'] == 1)
{
...
}
else if ($upgrade['user_upgrade_id'] == 2)
{
...
}
That should do it.
Wow. This works great right off the bat. Amazing.
I'm trying to insert a record into a custom table when an upgrade occurs.
Code:
 if ($upgrade['user_upgrade_id'] == 3)
 {
 $db->query("
 INSERT INTO xf_qntta_relation (ta_user_id, student_user_id, note)
 VALUES (1, 167, 'this is a notes')
          ");
 }
I hardcoded the value 167 where it should be the user_id of the member who is being upgraded.
'this is a note' is also hardcoded. I'd really like to get some variables such as the user's email address, the date of the upgrade. This is nice to have but not important.
Jake,
How do I get the userid and insert that into the table? You have this query but it's for update query, not insert query
Code:
WHERE username = (
        SELECT username
        FROM xf_user
        WHERE user_id = " . $userId . "
                 )
Thank you, Jake.
 

Andy.N

Well-known member
#11
The userid is already one of the function parameters, $userId
Jake,
1) I'd like to add an extra 4 weeks (28 days) to the start date of an existing upgrade when an additional upgrade takes place. The start_date is unix time. This is what I have so far. I think I need to convert the date before I can add?
Code:
 else if ($upgrade['user_upgrade_id'] == 3)
 {
 $db->query("
 UPDATE xf_user_upgrade_active
 SET start_date = start_date + 28
 WHERE user_id = " . $userId . "
 AND user_upgrade_id = 2
            ");
 }
2) How do I run multiple sql queries within this block. Do I do this?
Code:
 else if ($upgrade['user_upgrade_id'] == 3)
 {
 $db->query("
 query one;
query two;
query three
            ");
 }
 

Jake Bunce

XenForo moderator
Staff member
#12
1) Use this in your query:

SET start_date = start_date + 28*86400

86400 is the number of seconds in one day.

2) Do this:

Code:
else if ($upgrade['user_upgrade_id'] == 3)
 {
 $db->query("
 query one
            ");

 $db->query("
query two
            ");

 $db->query("
query three
            ");
 }
 

Andy.N

Well-known member
#13
1) Use this in your query:

SET start_date = start_date + 28*86400
Great stuff, Jake. Many thanks.
I'd like to keep records of the original start date and the new start update in a table, How can I achieve it?
Code:
###Get current start_date
$db->query("
UPDATE xf_qntta_relation
SET note = concat(note,'Original start date is ',SELECT CURRENT START_DATE from xf_user_upgrade_active table WHERE user_id = " . $userId . " AND user_upgrade_id = 2 )
WHERE student_user_id = " . $userId . "
            ");
 
#####Update start_date
$db->query("
UPDATE xf_user_upgrade_active
SET start_date = start_date + 28*86400
WHERE user_id = " . $userId . "
AND user_upgrade_id = 2
    ");
 
#########Add a note of new start date
$db->query("
UPDATE xf_qntta_relation
SET note = concat(note,'New start date is ', SELECT CURRENT START_DATE from xf_user_upgrade_active table where user_id = " . $userId . " AND user_upgrade_id = 2)
WHERE student_user_id = " . $userId . "
    ");
 

Andy.N

Well-known member
#15
Code:
$stateDate = $db->fetchOne("
SELECT start_date
FROM xf_user_upgrade_active
WHERE .....
");
Last one, I promise ;)
I'm trying to fetch the datetime, convert it mm/dd/yyy from unix time and concat it at the front of the note field. Somehow, it does not add the date format but keep the unix date time when it adds in the note field.
Code:
$startDate = $db->fetchOne("
SELECT FROM_UNIXTIME(min(start_date),'%c/%e/%Y')
FROM xf_user_upgrade_active
WHERE user_id = " . $userId . "
");
 
$db->query("
UPDATE xf_qntta_relation
SET note = concat('Extension. Original start date is '," . $startDate . ", note)
 

Jake Bunce

XenForo moderator
Staff member
#19
This is pretty awesome, but we need a way to run a query when the user's subscription runs out as well. Any advice?
You can add a downgradeUserUpgrades() function to the extended class in this addon.

Code:
	public function downgradeUserUpgrades(array $upgrades)
	{
		parent::downgradeUserUpgrades($upgrades);

		// YOUR STUFF HERE
	}