(Custom) SQL Query on User Upgrade

shadoom

Member
Hey,

I was wondering if it is possible to have a custom sql query running on User Upgrade which uses the forum nickname of the user and adds an amount to a field (ingame balance).

Thanks

edit:

something like this:
UPDATE `iConomy` SET Balance = Balance + 3000 WHERE username='$username';

where $username is the forum username of that user who just upgraded
 
I just made an addon for you. It works in my controlled testing, but I don't have a Paypal sandbox setup to test this with an actual purchase. You need to test it yourself and make sure it's working like you want. You may also want to edit the library/ExtendUserUpgrade/UserUpgradeModel.php file to set your own query. I used the query from your example:

Rich (BB code):
<?php

class ExtendUserUpgrade_UserUpgradeModel extends XFCP_ExtendUserUpgrade_UserUpgradeModel
{
	public function upgradeUser($userId, array $upgrade, $allowInsertUnpurchasable = false, $endDate = null)
	{
		$retval = parent::upgradeUser($userId, $upgrade, $allowInsertUnpurchasable, $endDate);

		$db = XenForo_Application::get('db');

		$db->query("
			UPDATE iConomy
			SET Balance = Balance + 3000
			WHERE username = (
				SELECT username
				FROM xf_user
				WHERE user_id = " . $userId . "
			)
		");

		return $retval;
	}
}
 

Attachments

I just made an addon for you. It works in my controlled testing, but I don't have a Paypal sandbox setup to test this with an actual purchase. You need to test it yourself and make sure it's working like you want. You may also want to edit the library/ExtendUserUpgrade/UserUpgradeModel.php file to set your own query. I used the query from your example:

Rich (BB code):
<?php
 
class ExtendUserUpgrade_UserUpgradeModel extends XFCP_ExtendUserUpgrade_UserUpgradeModel
{
public function upgradeUser($userId, array $upgrade, $allowInsertUnpurchasable = false, $endDate = null)
{
$retval = parent::upgradeUser($userId, $upgrade, $allowInsertUnpurchasable, $endDate);
 
$db = XenForo_Application::get('db');
 
$db->query("
UPDATE iConomy
 SET Balance = Balance + 3000
 WHERE username = (
 SELECT username
 FROM xf_user
 WHERE user_id = " . $userId . "
 )
");
 
return $retval;
}
}

Thanks a lot for such a quick reply and even taking the time and writing your own plugin. I really appreciate it.
I have one question though, this executes the query on all user upgrades right? Is it possible to limit the query to selected usergroups and give different upgrades different queries?

I guess that would be a little too complex right? :(
 
Thanks a lot for such a quick reply and even taking the time and writing your own plugin. I really appreciate it.
I have one question though, this executes the query on all user upgrades right? Is it possible to limit the query to selected usergroups and give different upgrades different queries?

I guess that would be a little too complex right? :(

Yes it executes on all upgrades.

You should be able to use a condition like this to check the user_upgrade_id:

Rich (BB code):
if ($upgrade['user_upgrade_id'] == 10)
{

}

That way you can take a different action for different upgrades that are purchased.
 
hm, I tried it like this:

Code:
                if ($upgrade['user_upgrade_id'] == 6)
                {
                $db->query("
USE minecraft_main;
UPDATE iConomy
SET Balance = Balance + 10000
WHERE username = (
SELECT username
FROM xf_user
WHERE user_id = " . $userId . "
)
");
                }

I tested it with another paypal account and upgraded myself... nothing happened, can i not use "USE minecraft_main" ? as it is in another database that iConomy table

output:
http://pastebin.com/0Pqqe9AJ
 
You will need to create a new connection for your other database so you can run the query. The database object in XenForo ($db in my code) uses your XenForo database.
 
You will need to create a new connection for your other database so you can run the query. The database object in XenForo ($db in my code) uses your XenForo database.

Thats what I've tried, I always got either: PHP Fatal error: Call to a member function query() on a non-object
or
a white screen on upgrading

I'm a terrible noob at this

worst thing I probably did was:
http://pastebin.com/7UZAXWvb

After doing that I could not access "User Upgrades" anymore. Are there just some mistakes in what I've done or is it completely messed up? I've read a lot on the php manual but as a first timer it doesn't feel like an easy thing to do :(
 
An example with a second database connection:

Rich (BB code):
<?php

class ExtendUserUpgrade_UserUpgradeModel extends XFCP_ExtendUserUpgrade_UserUpgradeModel
{
	public function upgradeUser($userId, array $upgrade, $allowInsertUnpurchasable = false, $endDate = null)
	{
		$retval = parent::upgradeUser($userId, $upgrade, $allowInsertUnpurchasable, $endDate);

		$db = XenForo_Application::get('db');
		$mcdb = new Zend_Db_Adapter_Pdo_Mysql(array(
			'host'     => 'localhost',
			'username' => 'dbuser',
			'password' => 'dbpass',
			'dbname'   => 'dbname'
		));

		$username = $db->fetchOne("
			SELECT username
			FROM xf_user
			WHERE user_id = " . $userId . "
		");

		$mcdb->query("
			UPDATE iConomy
			SET Balance = Balance + 3000
			WHERE username = '" . addslashes($username) . "'
		");

		return $retval;
	}
}
 
An example with a second database connection:

Rich (BB code):
<?php
 
class ExtendUserUpgrade_UserUpgradeModel extends XFCP_ExtendUserUpgrade_UserUpgradeModel
{
...

Sorry, I just found out it only works if I "manually" Upgrade a user via the Admin CP.
If they upgrade themselves via paypal, it does not work, no error in apaches error.log either
 
I am using a modified version of this:

Code:
<?php
 
class ExtendUserUpgrade_UserUpgradeModel extends XFCP_ExtendUserUpgrade_UserUpgradeModel
{
    public function upgradeUser($userId, array $upgrade, $allowInsertUnpurchasable = false, $endDate = null)
    {
        $retval = parent::upgradeUser($userId, $upgrade, $allowInsertUnpurchasable, $endDate);
 
        $db = XenForo_Application::get('db');
 
        if ($upgrade['user_upgrade_id'] == 1) { $db->query("UPDATE xf_monthly_balance SET balance = balance + 15"); }
        if ($upgrade['user_upgrade_id'] == 2) { $db->query("UPDATE xf_monthly_balance SET balance = balance + 10"); }
        if ($upgrade['user_upgrade_id'] == 3) { $db->query("UPDATE xf_monthly_balance SET balance = balance + 5"); }
        if ($upgrade['user_upgrade_id'] == 4) { $db->query("UPDATE xf_monthly_balance SET balance = balance + 2"); }
               
        return $retval;
    }
}

And it doesn't work, when somebody buys an upgrade from paypal nothing happens in the database. If I upgrade him from the AdminCP it works ok.

So pretty much the same shadoom pointed out :)
 
Sorry, I just found out it only works if I "manually" Upgrade a user via the Admin CP.
If they upgrade themselves via paypal, it does not work, no error in apaches error.log either

Does the upgrade itself work? Are the group changes for the upgrade applied and does the user show as upgraded in the Admin CP (ignoring the points for now)?

Admin CP -> Users -> List User Upgrades -> Controls: View Users

You can also check the xf_user_upgrade_log table in the database which will contain a record of any errors with the payment. And here are some things you can check:

http://xenforo.com/community/threads/paypal-error.7733/#post-108234

If the upgrade itself isn't working then this addon won't work either. This addon only works for successful payments and upgrades.

I am using a modified version of this:

Code:
<?php
 
class ExtendUserUpgrade_UserUpgradeModel extends XFCP_ExtendUserUpgrade_UserUpgradeModel
{
    public function upgradeUser($userId, array $upgrade, $allowInsertUnpurchasable = false, $endDate = null)
    {
        $retval = parent::upgradeUser($userId, $upgrade, $allowInsertUnpurchasable, $endDate);
 
        $db = XenForo_Application::get('db');
 
        if ($upgrade['user_upgrade_id'] == 1) { $db->query("UPDATE xf_monthly_balance SET balance = balance + 15"); }
        if ($upgrade['user_upgrade_id'] == 2) { $db->query("UPDATE xf_monthly_balance SET balance = balance + 10"); }
        if ($upgrade['user_upgrade_id'] == 3) { $db->query("UPDATE xf_monthly_balance SET balance = balance + 5"); }
        if ($upgrade['user_upgrade_id'] == 4) { $db->query("UPDATE xf_monthly_balance SET balance = balance + 2"); }
               
        return $retval;
    }
}

And it doesn't work, when somebody buys an upgrade from paypal nothing happens in the database. If I upgrade him from the AdminCP it works ok.

So pretty much the same shadoom pointed out :)

The same applies to you... is the upgrade itself working (see above)?
 
Yes it is working ok, 100% sure.

I just made a trial upgrade from a test account and after doing the payment I could inmediatly see it under the Control: View Users in the Admin CP and in the database table as well.

But at the same time, there were no changes in the xf_monthly_balance table.
 
Yes it is working ok, 100% sure.

I just made a trial upgrade from a test account and after doing the payment I could inmediatly see it under the Control: View Users in the Admin CP and in the database table as well.

But at the same time, there were no changes in the xf_monthly_balance table.

Your queries aren't matching any particular users. I assume this is intentional for testing purposes?

I see two possibilities:

1) Your 'user_upgrade_id' conditions aren't working. But I assume you tested this with some manual upgrades.

2) My addon doesn't work. Like I said before, I didn't test this with actual purchases because I don't have a sandbox setup.

I want to hear back from shadoom. If both of you are having the same problem then I need to do further testing with a paypal sandbox, or maybe one of you can give me your Paypal address so I can test a real payment using my own account.
 
sqlonupgrade.jpg

The Xenforo User Upgrades with Paypal are working flawless, see above.
The SQL Query on User upgrade does not get executed somehow when the user "buys" the rank with paypal.

When I manually upgrade him via this button:
manual.jpg

The SQL Query gets executed and works as planned.


Maybe the addon only listens for manual upgrades and not for the automatic/paypal ones?

edit: oh and you should have my paypal address, i donated some to the address in "nodes as tab" thread which appears to be from you ^^
 
Problem confirmed. I did some testing and it appears as though the extended model is not in effect during the payment callback, but it is during a manual upgrade. I have posted a bug report as this appears to be an inconsistent behavior in XenForo:

http://xenforo.com/community/threads/extended-model-not-working-for-payment_callback-php.25257/

In the meantime you can skip the addon and simply edit the files directly:

library/XenForo/Model/UserUpgrade.php

For example, add the red code:

Rich (BB code):
	/**
	 * Upgrades the user with the specified upgrade.
	 *
	 * @param integer $userId
	 * @param array $upgrade Info about upgrade to apply
	 * @param boolean $allowInsertUnpurchasable Allow insert of a new upgrade even if not purchasable
	 * @param integer|null $endDate Forces a specific end date; if null, don't overwrite
	 *
	 * @return integer|false User upgrade record ID
	 */
	public function upgradeUser($userId, array $upgrade, $allowInsertUnpurchasable = false, $endDate = null)
	{
		$db = $this->_getDb();



		$db->query("
			UPDATE iConomy
			SET Balance = Balance + 3000
			WHERE username = (
				SELECT username
				FROM xf_user
				WHERE user_id = " . $userId . "
			)
		");



		$active = $this->getActiveUserUpgradeRecord($userId, $upgrade['user_upgrade_id']);
		if ($active)
		{
			// updating an existing upgrade - if no end date override specified, extend the upgrade
			$activeExtra = unserialize($active['extra']);

			if ($endDate === null)
			{
				if ($active['end_date'] == 0 || !$activeExtra['length_unit'])
				{
					$endDate = 0;
				}
				else
				{
					$endDate = strtotime('+' . $activeExtra['length_amount'] . ' ' . $activeExtra['length_unit'], $active['end_date']);
				}
			}
			else
			{
				$endDate = intval($endDate);
			}

			if ($endDate != $active['end_date'])
			{
				$db->update('xf_user_upgrade_active',
					array('end_date' => $endDate),
					'user_id = ' . $db->quote($userId) . ' AND user_upgrade_id = ' . $db->quote($upgrade['user_upgrade_id'])
				);
			}

			return $active['user_upgrade_record_id'];
		}
		else
		{
			if (!$upgrade['can_purchase'] && !$allowInsertUnpurchasable)
			{
				return false;
			}

			// inserting a new new upgrade
			if ($endDate === null)
			{
				if (!$upgrade['length_unit'])
				{
					$endDate = 0;
				}
				else
				{
					$endDate = strtotime('+' . $upgrade['length_amount'] . ' ' . $upgrade['length_unit']);
				}
			}
			else
			{
				$endDate = intval($endDate);
			}

			$extra = array(
				'cost_amount' => $upgrade['cost_amount'],
				'cost_currency' => $upgrade['cost_currency'],
				'length_amount' => $upgrade['length_amount'],
				'length_unit' => $upgrade['length_unit']
			);

			XenForo_Db::beginTransaction($db);

			$db->insert('xf_user_upgrade_active', array(
				'user_id' => $userId,
				'user_upgrade_id' => $upgrade['user_upgrade_id'],
				'extra' => serialize($extra),
				'start_date' => XenForo_Application::$time,
				'end_date' => $endDate
			));
			$upgradeRecordId = $db->lastInsertId();

			$this->getModelFromCache('XenForo_Model_User')->addUserGroupChange(
				$userId, 'userUpgrade-' . $upgrade['user_upgrade_id'], $upgrade['extra_group_ids']
			);

			XenForo_Db::commit($db);

			return $upgradeRecordId;
		}
	}

You can use the same code as in the extended model from the addon, but $db is already there so you don't need to fetch that again, and you don't need to bother with the $retval stuff (the first and last lines of that function in the extended model).
 
Top Bottom