<?php
/*======================================================================*\
|| #################################################################### ||
|| # vBExperience 3.8                                                 # ||
|| # ---------------------------------------------------------------- # ||
|| # Copyright ©2006-2009 Marius Czyz. All Rights Reserved.           # ||
|| #################################################################### ||
\*======================================================================*/

function CalculatePPD()
{
	global $vbulletin;
	

		
	if (strlen($vbulletin->options['xperience_ignore_users'])>0) { $IgnoreUser=" WHERE u.userid NOT IN(".$vbulletin->options['xperience_ignore_users'].")"; }
	
	$users = $vbulletin->db->query_read("SELECT 
		u.userid
		FROM " . TABLE_PREFIX . "user as u".$IgnoreUser);


	while ($user = $vbulletin->db->fetch_array($users))
	{
		$userppd = $xPerience->GetPPD($user, $avgppd);
		$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "user SET xperience_ppd=".$userppd." WHERE userid=".$user['userid']);
		//Update only because of Awards
		if ($userppd > 90)
		{
			$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "xperience_stats SET points_user_activity=".$userppd." WHERE userid=".$user['userid']);
		}
	}

	ValidateActivity();
	
}

function GetProfileFields()
{
	global $vbulletin;
	
	$profilefieldsq =$vbulletin->db->query_read("SELECT 
		profilefieldid
		FROM " . TABLE_PREFIX . "profilefield
		WHERE (editable=1 OR editable=2) AND def=0
		");
	
	$profilefields = array();
	if ($vbulletin->db->num_rows($profilefieldsq) > 0) 
	{
		while ($profilefield = $vbulletin->db->fetch_array($profilefieldsq))
		{
			$profilefields[] = $profilefield['profilefieldid'];
		}
	} 

	return $profilefields;

}


function ValidateActivity()
{
	global $vbulletin;

	
	$valactq = $vbulletin->db->query_read("SELECT
		xperience_ppd, userid
		FROM " . TABLE_PREFIX . "user
		WHERE xperience_ppd=100
		ORDER BY xperience
		");
		
	if ($vbulletin->db->num_rows($valactq) > 0)
	{
		$firstrow = $vbulletin->db->fetch_array($valactq);
		
		while ($valact = $vbulletin->db->fetch_array($valactq))
		{
			$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "xperience_stats 
				SET
				points_user_activity=99
				WHERE userid=".$valact['userid']); 
			
			$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "user 
				SET
				xperience_ppd=99
				WHERE userid=".$valact['userid']);
		}
	}
	
	
	
}


function AddTransaction($transaction, $tatype)
{
	global $vbulletin, $vbphrase, $aTA;
	
	$taid = $transaction['taid'];

	
	$aTA['$taid']['ID'] = $taid;
	$aTA['$taid']['type'] = $tatype;
	$aTA['$taid']['item'] = $transaction['itemname'];
	$aTA['$taid']['itemid'] = $transaction['itemid'];
	$aTA['$taid']['cost'] = "-".vb_number_format($transaction['costs']);
	$aTA['$taid']['costfield'] = $vbphrase['xperience_'.$transaction[costfield]];
		
	switch ($tatype)
	{
		case 1:
			$aTA['$taid']['icon'] = "icon_item_nobuy.png";
			$aTA['$taid']['text'] = $vbphrase['xperience_shop_ta_sold'];
			$aTA['$taid']['date'] = vbdate($vbulletin->options['dateformat'], $transaction['dateline_sell'])." ".vbdate($vbulletin->options['timeformat'], $transaction['dateline_sell']);
			$aTA['$taid']['cost'] = vb_number_format($transaction['sell']);
			$aTA['$taid']['who'] = $vbphrase['xperience_shop_name'];
			$aTA['$taid']['whoid'] = 0;
			break;
		case 2:
			$aTA['$taid']['icon'] = "icon_give_away.png";
			$aTA['$taid']['text'] = $vbphrase['xperience_shop_ta_ga'];
			$aTA['$taid']['date'] = vbdate($vbulletin->options['dateformat'], $transaction['dateline_ga'])." ".vbdate($vbulletin->options['timeformat'], $transaction['dateline_ga']);
			$myuser = fetch_userinfo($transaction['userid']);
			$aTA['$taid']['who'] = $myuser['username'];
			$aTA['$taid']['whoid'] = $transaction['userid'];
			break;
		case 3:
			$aTA['$taid']['icon'] = "icon_give_away_get.png";
			$aTA['$taid']['text'] = $vbphrase['xperience_shop_ta_ga_received'];
			$aTA['$taid']['date'] = vbdate($vbulletin->options['dateformat'], $transaction['dateline_ga'])." ".vbdate($vbulletin->options['timeformat'], $transaction['dateline_ga']);
			$myuser = fetch_userinfo($transaction['buyer']);
			$aTA['$taid']['who'] = $myuser['username'];
			$aTA['$taid']['whoid'] = $transaction['buyer'];
			break;
		default:
			$aTA['$taid']['icon'] = "icon_item_buy.png";
			$aTA['$taid']['text'] = $vbphrase['xperience_shop_ta_purchased'];
			$aTA['$taid']['date'] = vbdate($vbulletin->options['dateformat'], $transaction['dateline'])." ".vbdate($vbulletin->options['timeformat'], $transaction['dateline']);
			$aTA['$taid']['who'] = $vbphrase['xperience_shop_name'];
			$aTA['$taid']['whoid'] = 0;
			break;
	}
	
}


function ResolveAssociation($option_name, $tablecheck = "")
{
	global $vbulletin, $settingphrase;
		
	if ($vbulletin->options["$option_name"] == 0) 
	{
		return "";
	}

	if (strlen($tablecheck) > 0)
	{
		$istable =$vbulletin->db->query_read("SHOW TABLES LIKE '" . TABLE_PREFIX . $tablecheck."'");
		if ($vbulletin->db->num_rows($istable) == 0)
		{
			return "";
		}
	}

	switch ($type)
	{
		case "1":
			$count_sum = "Sum";
			break;
		default:
			$count_sum = "Count";
			break;
	}
	
	
	$phrase = "setting_".$option_name."_title";
	$name = $settingphrase["$phrase"];
	
	$descphrase = "setting_".$option_name."_desc";
	$description= $settingphrase["$descphrase"];
	
	$value = $vbulletin->options["$option_name"];
	
	global $bgclass, $altbgclass;
	exec_switch_bg();
	eval('$xperience_earn_entry .= "' . fetch_template('xperience_earn_entry') . '";');
	return $xperience_earn_entry;
}


function WriteAward($award, $userid)
{
		global $vbulletin;


		if (!verify_id("user", $userid, false, false))
		{
			return "";
		}
		
		if (!is_array($award))
		{
			return "";
		}

		$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "user 
			SET
			xperience_awards=CONCAT('".addslashes($award['awardid'])."|".addslashes($award['awardname'])."|".addslashes($award['awardtitle'])."', ',', xperience_awards)
			WHERE userid=".$userid);
}

function WriteShopItem($item, $userid)
{
		global $vbulletin;

		if (strlen($item['itemicon']) < 3)
		{
			$item['itemicon'] = $vbulletin->options['bburl']."/images/misc/level/icon_item_s.png";
		}
		$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "user 
			SET
			xperience_shopitems=CONCAT('".addslashes($item['itemid'])."|".addslashes($item['cnt_items'])."|".addslashes($item['itemname'])."|".addslashes($item['itemicon'])."', ',', xperience_shopitems)
			WHERE userid=".$userid);
}

function WriteShopItems($userinfo)
{
	global $vbulletin;
	
	if ($vbulletin->options['xperience_shop_postbit'] == 0)
	{
		return;
	}
	
	$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "user 
	SET
	xperience_shopitems=''
	WHERE userid=".$userinfo['userid']);
	
	$itemsaq =$vbulletin->db->query_read("SELECT
		COUNT(t.itemid) as cnt_items, s.itemname, s.itemicon, s.itemid
		FROM " . TABLE_PREFIX . "xperience_shop_ta as t
		INNER JOIN " . TABLE_PREFIX . "xperience_shop as s ON s.itemid=t.itemid
		WHERE sell=0
		AND ((t.buyer=".$userinfo['userid']." AND t.userid=0) OR (t.userid=".$userinfo['userid']."))
		GROUP BY t.itemid
		ORDER BY dateline DESC
		LIMIT ".$vbulletin->options['xperience_shop_postbit']);

	if ($vbulletin->db->num_rows($itemsaq) > 0)
	{
		while ($itemsa = $vbulletin->db->fetch_array($itemsaq))
		{
			WriteShopItem($itemsa, $userinfo['userid']);
		}			
	}	
}

function GetShopItems($userinfo)
{
	global $vbulletin, $vbphrase;

	$xperience_items = explode(",", $userinfo['xperience_shopitems']);

	foreach($xperience_items AS $itemk => $iteme)
	{ 
		$itemi = explode("|", $iteme);
		$itemid = $itemi[0];
		if (is_numeric($itemid))
		{
			$itemcount = $itemi[1];
			$itemname = $itemi[2];
			$itemicon = $itemi[3];
			
			eval('$items .= " ' . fetch_template('xperience_shop_item_p') . '";');
			
			$mycount++;
			if ($mycount > $vbulletin->options['xperience_shop_postbit_break'] - 1)
			{
				$items .= "<br/>";
				$mycount = 0;
			}

		}
	}

	return $items;
}

function GetAwards($MyAwards) 
{
	global $vbulletin, $stylevar;

	$xperience_awards = explode(",", substr($MyAwards, 0, strlen($MyAwards)-1));

	foreach($xperience_awards AS $awardk => $awarde) 
	{
		$awardi=explode("|", $awarde);
		$awardid=$awardi[0];
		if (is_numeric($awardid))
		{

			$awardtitle = $awardi[2];

			//$awardurlq = $vbulletin->db->query_read("SELECT awardurl FROM " . TABLE_PREFIX . "xperience_awards WHERE awardid=".$awardid);
			//if ($vbulletin->db->num_rows($awardurlq) > 0)
			//{
			//	$awardurl = $vbulletin->db->fetch_array($awardurlq);
			//}
			//if (strlen($awardurl['awardurl']) > 0)
			//{
			//	$awards.='<img class="inlineimg" border="0" src="'.$awardurl['awardurl'].'" title="'.$awardtitle.'">';
			//}
			//else
			//{
				$awardname = $awardi[1];
		
				if ($vbulletin->options['xperience_awardgfx']) 
				{
					$awardname .= ".png";
				} else {
					$awardname .= ".gif";
				}
				
				//this has to be hardcoded as the fetch_musername hook comes before style_fetch
				if (strlen($stylevar[imgdir_misc])<2) $stylevar[imgdir_misc]="images/misc";
				//this has to be hardcoded as the fetch_musername hook comes before cache_templates
				$awards.='<img class="inlineimg" border="0" src="'.$stylevar[imgdir_misc].'/level/award_'.$awardname.'" title="'.$awardtitle.'">';
			//}
		}
	}
	return $awards;
}



function GetAvatar($userinfo)
{
	global $vbulletin;
	
	$avatarurl=$userinfo['avatarurl'];
	
	if ($userinfo['avatarid']) {
		$avatarurl = $userinfo['avatarpath'];
	} else 	{
		if ($userinfo['hascustomavatar'] AND $vbulletin->options['avatarenabled']) 	{
			if ($vbulletin->options['usefileavatar']) 	{
				$avatarurl = $vbulletin->options['avatarurl'] . "/avatar$userinfo[userid]_$userinfo[avatarrevision].gif";
			} else 	{
				$avatarurl = 'image.php?' . $vbulletin->session->vars['sessionurl'] . "u=$userinfo[userid]&amp;dateline=$userinfo[avatardateline]";
			}
		} else {
			if (!empty($userinfo['avatarurl'])) {
				$avatarurl=$userinfo['avatarurl'];
			} else {
				$avatarurl = '';
			}
		}
	}

return $avatarurl;
} 


function GetItemCount($catid)
{
	global $vbulletin;
	$count_items = 0;
	if (strlen($catid > 0))
	{
		$itemsq = $vbulletin->db->query_read("SELECT COUNT(itemid) as c_items FROM " . TABLE_PREFIX . "xperience_shop WHERE catid=".$catid);
	
		if ($vbulletin->db->num_rows($itemsq) > 0)
		{
			$items = $vbulletin->db->fetch_array($itemsq);
			$count_items = $items['c_items'];
		}
	}
	return $count_items;
}

function construct_xp_categories($parentid, $current, $prechars = '') 
{
	global $vbulletin, $vbphrase, $stylevar;
	$prechars .= " &nbsp; &nbsp; ";
	$catq =$vbulletin->db->query_read("SELECT * FROM " . TABLE_PREFIX . "xperience_shop_cat WHERE catstatus<>0 AND catparent=".$parentid." ORDER BY catstatus");
	if ($vbulletin->db->num_rows($catq) > 0)
	{
		while ($cat = $vbulletin->db->fetch_array($catq))
		{
				global $bgclass, $altbgclass;
				exec_switch_bg(); 
				
				if ($current == $cat['catid'])
				{
					$icon = "icon_cat_opened.png";
				}
				else
				{
					$icon = "icon_cat_closed.png";
				}
				$count_items = GetItemCount($cat['catid']);
				eval('$categories .= "' . fetch_template('xperience_shop_cat') . '";');
				$categories .= construct_xp_categories($cat['catid'], $current, $prechars);
		}
		
	}
	return $categories;
}

function CheckBuyConditions($itemsa, $userstats, $getphrase = false)
{
	global $vbulletin;
	
	$allow = true;
	$buyphrase = "xperience_shop_buy";
	
	if ($itemsa['quantity'] == 0)
	{
		$allow = false;
		$buyphrase = "xperience_shop_err_qnt";
	}
	
	if ($itemsa['costs'] > $userstats["$itemsa[costfield]"])
	{
		$allow = false;
		$buyphrase = "xperience_shop_err_cost";
	}
	
	if (!$vbulletin->options['xperience_shop_allow_multi'])
	{
		$userq =$vbulletin->db->query_read("SELECT itemid FROM " . TABLE_PREFIX . "xperience_shop_ta WHERE itemid=".$itemsa['itemid']." AND sell=0 AND buyer=".$userstats['userid']);
		if ($vbulletin->db->num_rows($userq) > 0)
		{
			$allow = false;
			$buyphrase = "xperience_shop_err_own";
		}
	}
		
	if ($getphrase) 
	{
		return $buyphrase;
	} else {
		return $allow;
	}
	
}


function fetch_xperience($userid)
{
	global $vbulletin;
	
	$userstats = array();
	$userq = $vbulletin->db->query_read("SELECT * FROM " . TABLE_PREFIX . "xperience_stats WHERE userid=".$userid);
	if ($vbulletin->db->num_rows($userq) > 0)
	{
		$userstats = $vbulletin->db->fetch_array($userq);
	} else {
		$userstats['userid'] = $userid;
	}
	
	return $userstats;
}


function fetch_statistics($fetchthis)
{
	global $vbulletin, $vbphrase;
	global $bgclass, $altbgclass;
	
	$optionsq =$vbulletin->db->query_read("SHOW COLUMNS
		FROM " . TABLE_PREFIX . "xperience_stats LIKE 'points_".$fetchthis."%'");
					
	if ($vbulletin->db->num_rows($optionsq) > 0)
	{

		
		while ($options = $vbulletin->db->fetch_array($optionsq)) 
		{
			$statsname = $vbphrase['xperience_'.$options[Field]];

			//
			$getstatsq =$vbulletin->db->query_read("SELECT
				u.userid, u.username, s.".$options['Field']." AS cnt_count
				FROM " . TABLE_PREFIX . "xperience_stats AS s
				INNER JOIN " . TABLE_PREFIX . "user AS u ON u.userid = s.userid
				WHERE s.".$options['Field']." <> 0
				ORDER BY s.".$options['Field']." DESC, s.points_xperience DESC
				LIMIT ".$vbulletin->options['xperience_stats_maxtop']);

			if ($vbulletin->db->num_rows($getstatsq) > 0)
			{

				$statscontent = '';
				$entrycounter = 0;
				while ($getstats = $vbulletin->db->fetch_array($getstatsq)) 
				{
					$entrycounter++;
					$getstats['cnt_count'] = vb_number_format($getstats['cnt_count']);
					eval('$statscontent .= "' . fetch_template('xperience_stats_entry') . '";');
				}
				
				if ($entrycounter < $vbulletin->options['xperience_stats_maxtop'] )
				{
					for($i=$entrycounter; $i <= $vbulletin->options['xperience_stats_maxtop'] - 1; $i++)
					{
						eval('$statscontent .= "' . fetch_template('xperience_stats_entry_empty') . '";');
					}
				}
				
				$columncounter++;
				eval('$stats .= "' . fetch_template('xperience_stats_entries') . '";');
				if ($columncounter > 4)
				{
					$columncounter = 0;
					$stats .="</tr><tr>";
				}
			}
		}
				

			
		
	}

	if ($columncounter > 0 AND $columncounter < 5 )
	{
		for($i=$columncounter; $i <= 4; $i++)
		{
			eval('$stats .= "' . fetch_template('xperience_stats_entries_empty') . '";');
		}
	}
	return $stats;
}

function DoDropColumns()
{
	global $vbulletin;
	
	$vbulletin->db->hide_errors();
	$vbulletin->db->query_write("DROP TABLE IF EXISTS " . TABLE_PREFIX . "xperience_stats");
	$vbulletin->db->query_write("DROP TABLE IF EXISTS " . TABLE_PREFIX . "xperience_groups");	
	$vbulletin->db->query_write("ALTER TABLE `". TABLE_PREFIX ."user` DROP `xperience`");
	$vbulletin->db->query_write("ALTER TABLE `". TABLE_PREFIX ."user` DROP `xperience_points`");
	$vbulletin->db->query_write("ALTER TABLE `". TABLE_PREFIX ."user` DROP `xperience_done`");
	$vbulletin->db->query_write("ALTER TABLE `". TABLE_PREFIX ."user` DROP `xperience_level`");
	$vbulletin->db->query_write("ALTER TABLE `". TABLE_PREFIX ."user` DROP `xperience_levelp`");
	$vbulletin->db->query_write("ALTER TABLE `". TABLE_PREFIX ."user` DROP `xperience_next_level`");
	$vbulletin->db->query_write("ALTER TABLE `". TABLE_PREFIX ."user` DROP `xperience_next_level_points`");
	$vbulletin->db->query_write("ALTER TABLE `". TABLE_PREFIX ."user` DROP `xperience_ppd`");
	$vbulletin->db->query_write("ALTER TABLE `". TABLE_PREFIX ."user` DROP `xperience_awards`");
	$vbulletin->db->query_write("ALTER TABLE `". TABLE_PREFIX ."user` DROP `xperience_shopitems`");
	$vbulletin->db->show_errors();
	
}



function DoInstall()
{
	global $vbulletin;
	
	$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "xperience_promotion_log (
		promotionid INT UNSIGNED NOT NULL AUTO_INCREMENT,
		userid INT(10) DEFAULT '0',
		from_ug INT(10) DEFAULT '0',
		to_ug INT(10) DEFAULT '0',
		comment VARCHAR(250) NOT NULL DEFAULT '-',
		dateline INT(10) DEFAULT '0',
		PRIMARY KEY (promotionid),
		KEY userid (userid)
	)"); 
	
	
	$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "xperience_shop (
		itemid INT UNSIGNED NOT NULL AUTO_INCREMENT,
		itemname VARCHAR(250) NOT NULL DEFAULT 'award',
		itemdesc MEDIUMTEXT,
		itempicture VARCHAR(250) DEFAULT '',
		itemicon VARCHAR(250) DEFAULT '',
		itemthumb VARCHAR(250) DEFAULT '',
		catid INT(6) DEFAULT '1',
		quantity SMALLINT NOT NULL DEFAULT '1', 
		costs INT NOT NULL DEFAULT '1',
		costfield VARCHAR(250) DEFAULT '',
		itemstatus INT(6) DEFAULT '1',
		PRIMARY KEY (itemid)
		)"); 
	
	$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "xperience_shop_ta (
		taid INT UNSIGNED NOT NULL AUTO_INCREMENT,
		itemid SMALLINT NOT NULL DEFAULT '1',
		buyer BIGINT NOT NULL DEFAULT '0', 
		dateline INT(10) DEFAULT '0',
		costs INT NOT NULL DEFAULT '1',
		costfield VARCHAR(250) DEFAULT '',
		dateline_sell INT(10) DEFAULT '0',
		sell INT NOT NULL DEFAULT '0',
		dateline_ga INT(10) DEFAULT '0',
		userid BIGINT NOT NULL DEFAULT '0', 
		PRIMARY KEY (taid)
		)"); 
	
	$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "xperience_shop_cat (
		catid INT UNSIGNED NOT NULL AUTO_INCREMENT,
		catname VARCHAR(250) NOT NULL DEFAULT 'award',
		catdesc MEDIUMTEXT, 
		catparent SMALLINT NOT NULL DEFAULT '0',
		catstatus INT(6) DEFAULT '1',
		PRIMARY KEY (catid)
		)"); 
	
	$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "xperience_awards (
		awardid INT UNSIGNED NOT NULL AUTO_INCREMENT,
		awardname VARCHAR(250) NOT NULL DEFAULT 'award',
		awardtitle VARCHAR(250) NOT NULL DEFAULT 'My Award',
		awarddesc MEDIUMTEXT, 
		awardurl VARCHAR(250) DEFAULT '',
		awardstatus SMALLINT NOT NULL DEFAULT '1', 
		awardfields MEDIUMTEXT,
		awardexclusions VARCHAR(250) DEFAULT '',
		awardlimit SMALLINT(6) DEFAULT '0',
		manualassign VARCHAR(250) NOT NULL DEFAULT '0',
		PRIMARY KEY (awardid),
		KEY awardname (awardname)
		)"); 
		
	$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "xperience_groups (
		groupid INT(10) DEFAULT '0',
		members INT(10) DEFAULT '0',
		points BIGINT DEFAULT '0',
		points_max BIGINT DEFAULT '0',
		points_min BIGINT DEFAULT '0'
	)"); 
		
	$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "xperience_custompoints (
		pointid INT UNSIGNED NOT NULL AUTO_INCREMENT,
		userid INT(10) DEFAULT '0',
		points_misc_custom BIGINT DEFAULT '0',
		adminid INT(10) DEFAULT '0',
		comment VARCHAR(250) NOT NULL DEFAULT '-',
		dateline INT(10) DEFAULT '0',
		PRIMARY KEY (pointid),
		KEY userid (userid)
	)"); 
	
	$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "xperience_stats (
		userid INT(10) DEFAULT '0',
		points_xperience BIGINT DEFAULT '0',
		points_thread BIGINT DEFAULT '0',
		points_threads BIGINT DEFAULT '0',
		points_threads_sg BIGINT DEFAULT '0',
		points_thread_group_discussion BIGINT DEFAULT '0',
		points_thread_votes BIGINT DEFAULT '0',
		points_thread_replycount BIGINT DEFAULT '0',
		points_thread_views BIGINT DEFAULT '0',
		points_thread_stickies BIGINT DEFAULT '0',
		points_thread_tags BIGINT DEFAULT '0',
		points_post BIGINT DEFAULT '0',
		points_posts BIGINT DEFAULT '0',
		points_posts_sg BIGINT DEFAULT '0',
		points_post_group_discussion BIGINT DEFAULT '0',
		points_post_thanks BIGINT DEFAULT '0',
		points_post_thanks_use BIGINT DEFAULT '0',
		points_post_attachment BIGINT DEFAULT '0',
		points_post_attachment_views BIGINT DEFAULT '0',
		points_user BIGINT DEFAULT '0',
		points_user_profile BIGINT DEFAULT '0',
		points_user_infractions BIGINT DEFAULT '0',
		points_user_reputation BIGINT DEFAULT '0',
		points_user_reputation_use BIGINT DEFAULT '0',
		points_user_online BIGINT DEFAULT '0',
		points_user_socialgroup BIGINT DEFAULT '0',
		points_user_visitormessages BIGINT DEFAULT '0',
		points_user_albumpictures BIGINT DEFAULT '0',
		points_user_referrals BIGINT DEFAULT '0',
		points_user_friends SMALLINT(6) DEFAULT '0',
		points_user_activity BIGINT DEFAULT '0',
		points_misc BIGINT DEFAULT '0',
		points_misc_ldm BIGINT DEFAULT '0',
		points_misc_dl2 BIGINT DEFAULT '0',
		points_misc_ppd BIGINT DEFAULT '0',
		points_misc_vbblog BIGINT DEFAULT '0',
		points_misc_custom BIGINT DEFAULT '0',
		shop BIGINT DEFAULT '0',
		dateline INT(10) DEFAULT '0',
		promoted SMALLINT NOT NULL DEFAULT '0',
		PRIMARY	KEY userid (userid)
	)"); 
	
	$vbulletin->db->query_write("CREATE TABLE IF NOT EXISTS " . TABLE_PREFIX . "xperience_level (
		usergroupid SMALLINT(6) DEFAULT '0',
		xperience_points FLOAT DEFAULT '0',
		xperience_level SMALLINT(6) DEFAULT '1',
		PRIMARY KEY	(xperience_level)
	)"); 
	
	if (!vbexp_field_exists('user', 'xperience')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user ADD xperience BIGINT DEFAULT '1'");
	if (!vbexp_field_exists('user', 'xperience_done')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user ADD xperience_done SMALLINT(6) DEFAULT '0'");
	if (!vbexp_field_exists('user', 'xperience_level')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user ADD xperience_level SMALLINT(6) DEFAULT '1'");
	if (!vbexp_field_exists('user', 'xperience_levelp')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user ADD xperience_levelp SMALLINT(6) DEFAULT '1'");
	if (!vbexp_field_exists('user', 'xperience_next_level')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user ADD xperience_next_level BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('user', 'xperience_next_level_points')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user ADD xperience_next_level_points BIGINT DEFAULT '1'");
	if (!vbexp_field_exists('user', 'xperience_ppd')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user ADD xperience_ppd FLOAT DEFAULT '0'");
	if (!vbexp_field_exists('user', 'xperience_awards')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user ADD xperience_awards VARCHAR(250) NOT NULL DEFAULT ''");
	if (!vbexp_field_exists('user', 'xperience_shopitems')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user ADD xperience_shopitems MEDIUMTEXT");
	if (!vbexp_field_exists('user', 'xperience_lastupdate')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user ADD xperience_lastupdate INT(10) DEFAULT '0'");
	
	if (vbexp_field_exists('user', 'xperience_awards')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user MODIFY xperience_awards MEDIUMTEXT");
	if (vbexp_field_exists('user', 'xperience_shopitems')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "user MODIFY xperience_shopitems MEDIUMTEXT");
	

	if (!vbexp_field_exists('xperience_awards', 'awardlimit')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_awards ADD awardlimit SMALLINT(6) DEFAULT '1'");
	if (!vbexp_field_exists('xperience_awards', 'awardurl')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_awards ADD awardurl VARCHAR(250) DEFAULT ''");

	if (vbexp_field_exists('xperience_shop_ta', 'buyer')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_shop_ta MODIFY buyer BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_shop_ta', 'userid')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_shop_ta ADD userid BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_shop_ta', 'sell')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_shop_ta ADD sell INT NOT NULL DEFAULT '0'");
	if (!vbexp_field_exists('xperience_shop_ta', 'dateline_sell')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_shop_ta ADD dateline_sell INT(10) DEFAULT '0'");
	if (!vbexp_field_exists('xperience_shop_ta', 'dateline_ga')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_shop_ta ADD dateline_ga INT(10) DEFAULT '0'");
	
	if (!vbexp_field_exists('xperience_shop', 'itemicon')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_shop ADD itemicon VARCHAR(250) DEFAULT ''");
	if (!vbexp_field_exists('xperience_shop', 'itemthumb')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_shop ADD itemthumb VARCHAR(250) DEFAULT ''");
	
	if (!vbexp_field_exists('xperience_stats', 'shop')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD shop BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_xperience')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_xperience BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'promoted')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD promoted SMALLINT NOT NULL DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_misc_custom')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_misc_custom BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_misc_vbblog')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_misc_vbblog BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_misc_ppd')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_misc_ppd BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_misc_dl2')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_misc_dl2 BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_misc_ldm')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_misc_ldm BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_misc')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_misc BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_thread_tags')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_thread_tags BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_post_attachment')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_post_attachment BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_post_attachment_views')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_post_attachment_views BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_post_thanks_use')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_post_thanks_use BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_user_reputation_use')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_user_reputation_use BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_user_referrals')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_user_referrals BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_user_friends')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_user_friends BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_user_activity')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_user_activity BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_user_socialgroup')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_user_socialgroup BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_user_visitormessages')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_user_visitormessages BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_user_albumpictures')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_user_albumpictures BIGINT DEFAULT '0'");
	if (vbexp_field_exists('xperience_stats', 'points_post_attachement')) $vbulletin->db->query_write("ALTER TABLE ". TABLE_PREFIX ."xperience_stats DROP points_post_attachement");
	if (!vbexp_field_exists('xperience_stats', 'points_thread_group_discussion')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_thread_group_discussion BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_post_group_discussion')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_post_group_discussion BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_user_profile')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_user_profile BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_threads_sg')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_threads_sg BIGINT DEFAULT '0'");
	if (!vbexp_field_exists('xperience_stats', 'points_posts_sg')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD points_posts_sg BIGINT DEFAULT '0'");

	
	if (vbexp_field_exists('xperience_awards', 'manualassign')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_awards MODIFY manualassign VARCHAR(250) NOT NULL DEFAULT '0'");
	if (!vbexp_field_exists('xperience_awards', 'manualassign')) $vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_awards ADD manualassign VARCHAR(250) NOT NULL DEFAULT '0'");
	if (vbexp_field_exists('user', 'xperience_points')) $vbulletin->db->query_write("ALTER TABLE ". TABLE_PREFIX ."user DROP xperience_points");
	
	$istable = $vbulletin->db->query_read("SELECT awardid FROM " . TABLE_PREFIX . "xperience_awards");
	
	if ($vbulletin->db->num_rows($istable)==0) 
	{
		$vbulletin->db->query_write("INSERT INTO " . TABLE_PREFIX . "xperience_awards (awardlimit, awardname, awardtitle, awarddesc, awardfields) VALUES ('0', 'activity', 'Activity Award', 'Very Active', 'points_user_activity')");
		$vbulletin->db->query_write("INSERT INTO " . TABLE_PREFIX . "xperience_awards (awardlimit, awardname, awardtitle, awarddesc, awardfields) VALUES ('0', 'posting', 'Posting Award', 'Zealousness posting and tagging', 'points_threads+points_thread_votes+points_thread_replycount+points_thread_views+points_thread_stickies+points_thread_tags+points_posts+points_post_attachment')");
		$vbulletin->db->query_write("INSERT INTO " . TABLE_PREFIX . "xperience_awards (awardlimit, awardname, awardtitle, awarddesc, awardfields) VALUES ('0', 'community', 'Community Award', 'Social engaged', 'points_user_visitormessages+points_user_socialgroup+points_user_friends+points_user_reputation')");
	}
	

	$vbulletin->db->hide_errors();
	$vbulletin->db->query_write("ALTER TABLE " . TABLE_PREFIX . "xperience_stats ADD PRIMARY KEY (userid)");
	$vbulletin->db->show_errors();
	
}
			
function vbexp_field_exists($table, $field)
{
	global $vbulletin;
	return ($vbulletin->db->num_rows($vbulletin->db->query_read("SHOW COLUMNS FROM `" . TABLE_PREFIX .$table."` LIKE '".$field."'"))> 0);
}
?>