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

(Custom) SQL Query on User Upgrade

Discussion in 'XenForo Questions and Support' started by shadoom, Dec 26, 2011.

  1. shadoom

    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
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Possible yes. I would write an addon to extend XenForo_Model_UserUpgrade::upgradeUser. That will allow you to add your own query following the execution of an upgrade. Or you can edit the file directly (library/XenForo/Model/UserUpgrade.php), but an addon is better for this.

    You can post a request:

    http://xenforo.com/community/forums/add-on-requests.35/
     
    shadoom likes this.
  3. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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:

    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;
    	}
    }
    
     

    Attached Files:

    Darq, lasertits, Walter and 2 others like this.
  4. shadoom

    shadoom Member

    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? :(
     
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Yes it executes on all upgrades.

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

    Code:
    if ($upgrade['user_upgrade_id'] == 10)
    {
    
    }
    
    That way you can take a different action for different upgrades that are purchased.
     
    shadoom likes this.
  6. shadoom

    shadoom Member

    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
     
  7. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
  8. shadoom

    shadoom Member

    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 :(
     
  9. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    An example with a second database connection:

    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;
    	}
    }
    
     
    shadoom likes this.
  10. shadoom

    shadoom Member

    Working like a charm.

    Thank you so much,
    You are my hero :)
     
  11. shadoom

    shadoom Member

    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
     
  12. Xosé Estrada

    Xosé Estrada Member

    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 :)
     
  13. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.

    The same applies to you... is the upgrade itself working (see above)?
     
  14. Xosé Estrada

    Xosé Estrada Member

    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.
     
  15. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
  16. shadoom

    shadoom Member

    [​IMG]
    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:
    [​IMG]
    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 ^^
     
  17. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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:

    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).
     
    Xosé Estrada and shadoom like this.
  18. Xosé Estrada

    Xosé Estrada Member

    Thanks Jake!

    And yes, my queries are provisional just for testing :)
     
  19. shadoom

    shadoom Member

    adding it directly is working fine thanks a lot :)
     
  20. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

Share This Page