public function getItems()
{
$items = parent::getItems();
// If current realm list is empty populate from TC database
if ($this->getTotal() <= 0) {
$authdb = JTrinityCoreDBHelper::getAuthDBName();
$sql = "SELECT id, name, address, port, population FROM " . $authdb . ".realmlist";
$dbo = JTrinityCoreDBHelper::getDB();
$dbo->setQuery($sql);
$results = $dbo->loadObjectList();
if (count($results)) {
foreach ($results as $row) {
$data = new stdClass();
$data->id = NULL;
$data->realmid = $row->id;
$data->realmname = $row->name;
$data->ip = $row->address;
$data->port = $row->port;
$data->population = $row->population;
$this->_db->insertObject('#__jtc_realms', $data, 'id');
}
$items = parent::getItems();
}
}
return $items;
}
public function getCharacters($realmid)
{
$chardb = JTrinityCoreUtilities::getCharacterDBName($realmid);
$accid = JTrinityCoreDBHelper::getAccId();
$sql = "SELECT * FROM " . $chardb . ".characters WHERE account=" . $accid;
$db = JTrinityCoreDBHelper::getDB();
$db->setQuery($sql);
return $db->loadObjectList();
}
public function teleport($realmid, $guid, $x, $y, $z, $map, $newGold)
{
$chardb = JTrinityCoreUtilities::getCharacterDBName($realmid);
$db = JTrinityCoreDBHelper::getDB();
$query = 'UPDATE ' . $chardb . '.characters SET position_x="' . $x . '", position_y="' . $y . '", position_z="' . $z . '", map="' . $map . '", money="' . $newGold . '" WHERE guid="' . $guid . '"';
$db->setQuery($query);
if (!$db->query()) {
JLog::add("Error teleporting. SQL=" . $query, JLog::ERROR);
return false;
}
return true;
}
protected function unstuck($realmid, $guid)
{
// Homebind
$db = JTrinityCoreDBHelper::getDB();
$database = JTrinityCoreUtilities::getCharacterDBName($realmid);
$query = 'SELECT * FROM ' . $database . '.character_homebind WHERE guid = ' . $guid . ' LIMIT 1';
$db->setQuery($query);
if (!($obj = $db->loadObject())) {
JLog::add("Unstuck: error. sql=" . $query, JLog::ERROR);
return false;
}
$query = 'UPDATE ' . $database . '.characters SET position_x="' . $obj->position_x . '", position_y="' . $obj->position_y . '", position_z="' . $obj->position_z . '", map="' . $obj->map . '", zone="' . $obj->zone . '" WHERE guid=' . $guid . ' LIMIT 1';
$db->setQuery($query);
if (!$db->query()) {
JLog::add("Unstuck: error. sql=" . $query, JLog::ERROR);
return false;
}
return true;
}
/**
* Buy gold to a character
* @param unknown_type $gold Gold object
* @param unknown_type $realmid Realmid
* @param unknown_type $char Character object
*/
public function buyGold($gold, $realmid, $char)
{
// Start transaction
$this->_db->transactionStart();
try {
// Update user points
$user = JFactory::getUser();
$userid = $user->id;
if (!JTrinityCoreUtilities::substractUserPoints($userid, $gold->cost, $this->_db)) {
$this->_db->transactionRollback();
return false;
}
$description = "Gold " . $gold->quantity;
$donationtype = DONATIONTYPE_GOLD;
// Update order table
if (!JTrinityCoreUtilities::insertOrderTable($userid, $gold->id, $description, $gold->cost, $donationtype, $char->guid, $realmid, $this->_db)) {
$this->_db->transactionRollback();
return false;
}
// Update Gold to character
// Add gold to character
// Get character DB
$chardbname = JTrinityCoreUtilities::getCharacterDBName($realmid);
$dbtc = JTrinityCoreDBHelper::getDB();
$addmoney = $gold->quantity * 10000;
$sql = "UPDATE " . $chardbname . ".characters SET money=money+" . $addmoney . " WHERE guid=" . $char->guid;
$dbtc->setQuery($sql);
if (!$dbtc->query()) {
$this->_db->transactionRollback();
return false;
}
$this->_db->transactionCommit();
return true;
} catch (Exception $e) {
JLog::add("Error buygold(): " . $e->getMessage(), JLog::ERROR);
$this->_db->transactionRollback();
return false;
}
}
public function delete(&$pks)
{
try {
// delete from tc db
$dbo = JTrinityCoreDBHelper::getDB();
$accdb = JTrinityCoreDBHelper::getAuthDBName();
// Iterate the items to delete each one.
$res = true;
$pks = (array) $pks;
$table = $this->getTable();
foreach ($pks as $i => $pk) {
$realmid = 0;
// Get realmid
if ($table->load($pk)) {
$realmid = $table->realmid;
}
$query = "DELETE FROM " . $accdb . ".realmlist WHERE id=" . $realmid;
$dbo->setQuery($query);
$res = $res && $dbo->query();
}
if ($res) {
$res = parent::delete($pks);
}
return $res;
} catch (Exception $e) {
$this->setError($e->getMessage());
return false;
}
}
protected function deleteUser($user)
{
// deletion method
$cparams = JComponentHelper::getParams('com_jtrinitycore');
$db = JTrinityCoreDBHelper::getDB();
$accid = $this->getAccId($user['username']);
if ($accid == -1) {
JLog::add('Error deleting user account. AccId=-1. User='******'username'], JLog::ERROR, $this->logcat);
return false;
}
if ((int) $cparams->get('delete_method') == 0) {
//---- Lock User
// Do not delete the user, just locks it
$query = "UPDATE " . $this->dbaccount . ".account SET locked = 1 WHERE id=" . $accid;
$db->setQuery($query);
if (!$db->query()) {
JLog::add('Error locking user account in trinity db. SQL= ' . $query, JLog::ERROR, $this->logcat);
return false;
} else {
JLog::add('Locking user account in trinity db OK. SQL= ' . $query, JLog::INFO, $this->logcat);
return true;
}
} else {
// TODO: ---- Delete user in Trinity Core
$query = "DELETE FROM " . $this->dbaccount . ".account WHERE id=" . $accid;
$db->setQuery($query);
if (!$db->query()) {
JLog::add('Error deleting from table account. SQL= ' . $query, JLog::ERROR, $this->logcat);
return false;
}
$query = "DELETE FROM " . $this->dbaccount . ".account_access WHERE id=" . $accid;
$db->setQuery($query);
if (!$db->query()) {
JLog::add('Error deleting from table account_access. SQL= ' . $query, JLog::ERROR, $this->logcat);
return false;
}
$query = "DELETE FROM " . $this->dbaccount . ".realmcharacters WHERE acctid = " . $accid;
$db->setQuery($query);
if (!$db->query()) {
JLog::add('Error deleting from table realmcharacters. SQL= ' . $query, JLog::ERROR, $this->logcat);
return false;
}
// Delete from all DB characters
$sql = "SELECT charactersdb FROM #__jtc_realms ";
$dbo = JFactory::getDbo();
$dbo->setQuery($sql);
if ($obj = $dbo->loadObjectList()) {
foreach ($obj as $i => $item) {
$query = 'DELETE FROM ' . $item->charactersdb . '.account_data WHERE accountId = ' . $accid;
$db->setQuery($query);
if (!$db->query()) {
JLog::add('Error deleting from table account_data. SQL= ' . $query, JLog::ERROR, $this->logcat);
return false;
}
$query = 'UPDATE ' . $item->charactersdb . '.characters SET deleteDate=(SELECT UNIX_TIMESTAMP(\'' . date("Y-m-d H:i:s") . '\')) WHERE account = ' . $accid;
//$query = 'DELETE FROM #__characters WHERE account = ' . (int)$userinfo->userid;
$db->setQuery($query);
if (!$db->query()) {
JLog::add('Error deleting from table characters. SQL= ' . $query, JLog::ERROR, $this->logcat);
return false;
}
}
} else {
JLog::add('Error loading characters db from jtc_realms . SQL= ' . $sql, JLog::ERROR, $this->logcat);
}
}
JLog::add('Deleted user from table characters OK.', JLog::INFO, $this->logcat);
return true;
}
/**
* Build an SQL query to load the list data.
*
*
* @return JDatabaseQuery
* @since 1.6
*/
protected function getListQuery()
{
// Create a new query object.
$worlddb = JTrinityCoreUtilities::getWorldDBName(JRequest::getVar('realmid'));
$db = JTrinityCoreDBHelper::getDB();
$query = $db->getQuery(true);
// Select the required fields from the table.
$query->select($this->getState('list.select', 'DISTINCT a.entry as itemid, itemlevel, requiredlevel, a.name as englishname, quality, allowablerace, subclass, class, inventorytype'));
$query->from($worlddb . '.item_template AS a');
$query->where('a.flags<>16 AND (a.name IS NOT NULL) AND (a.name not like \'%deprecated%\') AND (a.name not like \'%test%\')');
$name = "a.name";
$locale = JTrinityCoreUtilities::getLocale();
if ($locale > 1) {
$name = "name_loc" . $locale;
$query->select($name . " as name");
$query->where($name . " IS NOT NULL");
$query->join('INNER', $worlddb . '.locales_item AS li ON li.entry=a.entry ');
#__users AS uc ON uc.id=a.checked_out
} else {
$query->select("a.name as name");
}
// Filter by class state
$class = $this->getState('filter.class_id');
if (is_numeric($class)) {
$query->where('a.class = ' . (int) $class);
}
// Filter on quality.
$quality = $this->getState('filter.quality_id');
if (is_numeric($quality)) {
$query->where('a.Quality =' . $quality);
}
// Filter on Inventory Type.
$inventory = $this->getState('filter.inventory_id');
if (is_numeric($inventory)) {
$query->where('a.InventoryType =' . $inventory);
}
// Filter on allowable class
$allowableclass = $this->getState('filter.allowableclass');
if (is_numeric($allowableclass)) {
$query->where('a.AllowableClass =' . $allowableclass);
}
// Filter by search in title.
$search = $this->getState('filter.search');
if (!empty($search)) {
if (stripos($search, 'itemid:') === 0) {
$query->where('a.displayid = ' . (int) substr($search, 7));
} else {
if (stripos($search, 'itemlevel:') === 0) {
$query->where('itemlevel = ' . (int) substr($search, 10));
}
}
if (stripos($search, 'requiredlevel:') === 0) {
$query->where('requiredlevel = ' . (int) substr($search, 14));
} else {
$search = $db->Quote('%' . $db->escape($search, true) . '%');
$query->where('(' . $name . ' LIKE ' . $search . ')');
}
}
// Add the list ordering clause.
$orderCol = $this->state->get('list.ordering', 'i.name');
$orderDirn = $this->state->get('list.direction', 'asc');
//echo "Order field=".$orderCol."---<br>";
if ($orderCol == 'a.name' || $orderCol == 'name') {
$orderCol = 'name ';
}
if ($orderCol == 'a.itemid' || $orderCol == 'itemid') {
$orderCol = 'itemid ';
}
if ($orderCol == 'a.itemlevel' || $orderCol == 'itemlevel') {
$orderCol = 'itemlevel ';
}
//sqlsrv change
/*if($orderCol == 'access_level')
$orderCol = 'ag.title';*/
$query->order($db->escape($orderCol . ' ' . $orderDirn));
// echo nl2br(str_replace('#__','jos_',$query));
//JLog::add('Query='.$query, JLog::WARNING, 'deprecated');
//JError::raiseError(0, JText::sprintf('DB Name antes='.$db->getDatabase()." Version=".$db->getVersion()." connected=".$db->connected(), 'hola'));
return $query;
}
/**
* Send the item purchased to the mailbox character
* @param unknown_type $item item object from joomla database
* @param unknown_type $realmid the realm of the character
* @param unknown_type $character character object
*/
public function sendItem($item, $realmid, $character)
{
// Get character DB
$chardbname = JTrinityCoreUtilities::getCharacterDBName($realmid);
$dbo = JTrinityCoreDBHelper::getDB();
$characterid = $character->guid;
$itemid = $item->itemid;
//JLog::add("characterid=".$characterid." productid=".$productid." realmid=".$realmid." item name=".$item->name);
// Get the mail id to insert
// Start transaction
$dbo->transactionStart();
try {
$ok = true;
$sql = "SELECT MAX(id) FROM " . $chardbname . ".mail";
$dbo->setQuery($sql);
$id = $dbo->loadResult();
if (!$id) {
$id = 0;
}
$id = $id + 1;
$mail = new stdClass();
$mail->id = $id;
$mail->messageType = 5;
$mail->stationery = 41;
$mail->sender = 0;
// Should be the admin GM
$mail->receiver = $characterid;
$mail->has_items = 1;
$mail->subject = JText::_('COM_JTRINITYCORE_MAIL_ITEM_SUBJECT');
$mail->body = JText::sprintf('COM_JTRINITYCORE_MAIL_ITEM_BODY', $character->name, $item->name);
// Insert into mail table
if ($dbo->insertObject($chardbname . '.mail', $mail, 'id')) {
// Insert into item instance table
$sql = "SELECT MAX(guid) FROM " . $chardbname . ".item_instance";
$dbo->setQuery($sql);
$iteminstanceid = $dbo->loadResult();
if (!$iteminstanceid) {
$iteminstanceid = 0;
}
$iteminstanceid = $iteminstanceid + 1;
$item_instance = new stdClass();
$item_instance->guid = $iteminstanceid;
$item_instance->itemEntry = $item->itemid;
$item_instance->owner_guid = $characterid;
$item_instance->durability = $this->getMaxDurability($item->itemid, $relmid);
// $item->maxdurability;
if ($dbo->insertObject($chardbname . '.item_instance', $item_instance, 'guid')) {
// Insert into mail_items table
$mail_item = new stdClass();
$mail_item->mail_id = $id;
$mail_item->item_guid = $iteminstanceid;
$mail_item->receiver = $characterid;
if (!$dbo->insertObject($chardbname . '.mail_items', $mail_item)) {
$ok = false;
JLog::add("sendItem(): Error inserting item_instance. ", JLog::ERROR, 'donationshop');
}
} else {
$ok = false;
}
} else {
$ok = false;
JLog::add("sendItem(): Error inserting table mail. ", JLog::ERROR, 'donationshop');
}
if (!$ok) {
$dbo->transactionRollback();
return false;
}
$dbo->transactionCommit();
$user = JFactory::getUser();
$cost = $item->cost;
// Update the new points to the user
if (!JTrinityCoreUtilities::substractUserPoints($user->id, $cost)) {
return false;
}
// Insert into the order table
$description = $item->name . " (Level=" . $item->itemlevel . ")";
$donationtype = DONATIONTYPE_ITEM;
$userid = $user->id;
if (!JTrinityCoreUtilities::insertOrderTable($userid, $item->id, $description, $cost, $donationtype, $characterid, $realmid)) {
return false;
}
return true;
} catch (Exception $e) {
JLog::add("Error: " . $e->getMessage(), JLog::ERROR, "donationhop->sendItem");
$dbo->transactionRollback();
return false;
}
}
public function getUptimeServer($realmid)
{
$db = JTrinityCoreDBHelper::getDB();
$accdb = JTrinityCoreDBHelper::getAuthDBName();
$query = "SELECT uptime FROM " . $accdb . ".uptime WHERE realmid=" . $realmid . " ORDER BY starttime DESC LIMIT 1";
$db->setQuery($query);
if (!($uptime_res = $db->loadResult())) {
$uptime = '0';
} else {
$obj = $this->secondsToTime($uptime_res);
$uptime = '';
if ($obj['d'] > 0) {
$uptime = $obj['d'] . JText::_('COM_JTRINITYCORE_DAYS');
}
if ($obj['h'] > 0) {
$uptime .= ' ' . $obj['h'] . JText::_('COM_JTRINITYCORE_HOURS');
}
if ($obj['m'] > 0) {
$uptime .= ' ' . $obj['m'] . JText::_('COM_JTRINITYCORE_MINS');
}
}
return $uptime;
}
