[help]dle+la2

<?php

/********************************************************************/
/* */
/* Lineage2 DLE Module, Copyright © 2007-2008, hellEVIL TEAM */
/* _http://www.hellevil.net */
/* */
/***************************F*****************************************/

if(!defined('DATALIFEENGINE')) {
die("Hacking attempt!");
}

/********************************************************************/
/* */
/* L2jFree */
/* */
/********************************************************************/

// Список полей
$fList_L2jFree = array(
"accounts" => "accounts.login, accounts.password, accounts.lastactive AS lastActive, accounts.access_level AS accessLevel, accounts.lastIP, accounts.email, accounts.answer, accounts.question, accounts.hash",

"char_templates" => "char_templates.ClassName AS classname, char_templates.STR AS str, char_templates.CON AS con, char_templates.DEX AS dex, char_templates._INT AS _int, char_templates.MEN AS men, char_templates.WIT AS wit",

"characters" => "characters.obj_Id AS charId, characters.clanid, characters.account_name, characters.lastAccess, characters.char_name, characters.online, characters.level, characters.sex, characters.pvpkills, characters.pkkills, characters.onlinetime, characters.base_class, characters.exp, characters.sp, characters.karma, characters.maxHp, characters.maxMp, characters.x, characters.y, characters.z, characters.in_jail, characters.jail_timer, characters.nobless, characters.title ",

"character_friends" => "character_friends.friend_id, character_friends.friend_name",

"character_subclasses" => "character_subclasses.*",

"castle" => "castle.name AS castleName, castle.id AS castleID, castle.taxPercent, castle.siegeDate",

"clanhall" => "clanhall.name AS clanhallName, clanhall.location AS clanhallLocation",

"clan_data" => "clan_data.clan_name, clan_data.clan_id, clan_data.ally_id, clan_data.ally_name, clan_data.clan_level, clan_data.reputation_score, clan_data.hasCastle, clan_data.leader_id",

"items" => "items.item_id, items.object_id, items.loc_data, items.count, items.loc, items.enchant_level",

"items_info" => "
CASE
WHEN armor.name != '' THEN armor.name
WHEN weapon.name != '' THEN weapon.name
WHEN etcitem.name != '' THEN etcitem.name
END AS name,
CASE
WHEN armor.crystal_type != '' THEN armor.crystal_type
WHEN weapon.crystal_type != '' THEN weapon.crystal_type
WHEN etcitem.crystal_type != '' THEN etcitem.crystal_type
END AS crystal_type,
CASE
WHEN armor.crystal_type != '' THEN 'armor'
WHEN weapon.crystal_type != '' THEN 'weapon'
WHEN etcitem.crystal_type != '' THEN 'etc'
END AS `type`",
);

// Запросы для таблиц
$qList_L2jFree = array(
// Таблица account
"accounts" => array (
"GetAccount" => "
SELECT ".$fList_L2jFree["accounts"]."
FROM `accounts`
WHERE login='{param0}'
LIMIT 1",

"GetCount" => "
SELECT count(*)
FROM `accounts`",

"GetCountPlayers" => "
SELECT count(*)
FROM accounts
WHERE accessLevel=0",

"GetList" => "
SELECT ".$fList_L2jFree["accounts"]."
FROM `accounts`
ORDER BY `accessLevel` DESC {param0}
LIMIT {param1}",

"SetPassword" => "
UPDATE `accounts`
SET `password` = '{param1}'
WHERE `login`='{param0}'",

"SetAnswerQuestion" => "
UPDATE `accounts`
SET
`question` = '{param1}',
`answer` = '{param2}'
WHERE `login`='{param0}'
",

"SetEmail" => "
UPDATE `accounts`
SET
`email` = '{param1}',
WHERE `login`='{param0}'
",

"InsertAccount" => "
INSERT INTO `accounts`
(`login`, `password`, `accessLevel`, `email`, `answer`, `question`)
VALUES ('{param0}', '{param1}', 0, '{param2}','{param3}','{param4}')
",

"DeleteAccount" => "
DELETE FROM accounts
WHERE login='{param0}'
",

"UpdateAccount" => "
UPDATE `accounts`
SET `accessLevel`={param1}, `email`='{param2}', `answer`='{param3}', `question`='{param4}'
WHERE login='{param0}'
",

"GetAccountByEmail" => "
SELECT *
FROM `accounts`
WHERE login='{param0}'
AND email='{param1}'",

"SetHash" => "
UPDATE `accounts`
SET `hash` = '{param1}'
WHERE `login`='{param0}'",
),

"l2top" => array (
"GetId" => "
SELECT `obj_Id` FROM `characters` WHERE `char_name`='{param0}'",

"GetIp" => "
SELECT * FROM `l2top` WHERE `ip`='{param0}' AND `time`='{param1}'",

"GetOnline" => "
SELECT `online` FROM `characters` WHERE `char_name`='{param0}'",

"GetCount" => "
SELECT `count` FROM `items` WHERE `owner_id`='{param0}' AND `item_id`='{param1}'",

"GetMax" => "
SELECT MAX(object_id)+1 FROM items WHERE 1",

"InsertItem" => "
INSERT INTO `items` (`owner_id`,`object_id`,`item_id`,`count`,`loc`) VALUES ('{param0}', '{param1}', '{param2}', '{param3}', 'INVENTORY')",

"UpdateItem" => "
UPDATE `items` SET `count`='{param0}' WHERE `owner_id`='{param1}' AND `item_id`='{param2}'",

"InsertDb" => "
INSERT INTO `l2top` (`nick`,`ip`,`time`) VALUES ('{param0}','{param1}','{param2}')",
),

// Таблица characters
"characters" => array (
"byID" => "
SELECT ".$fList_L2jFree["characters"].",".$fList_L2jFree["clan_data"].",".$fList_L2jFree["char_templates"]."
FROM `characters`
LEFT JOIN `char_templates` ON characters.classid = char_templates.classid
LEFT JOIN `clan_data` ON characters.clanid = clan_data.clan_id
WHERE characters.charId = '{param0}'
LIMIT 1",

"byLogin" => "
SELECT ".$fList_L2jFree["characters"].",".$fList_L2jFree["clan_data"].",".$fList_L2jFree["char_templates"]."
FROM `characters`
LEFT JOIN `char_templates` ON characters.classid = char_templates.classid
LEFT JOIN `clan_data` ON characters.clanid = clan_data.clan_id
WHERE characters.account_name = '{param0}'",

"GetCountPlayers" => "
SELECT count(*)
FROM characters
WHERE accessLevel = 0",

"GetCountOnline" => "
SELECT count(*)
FROM characters
WHERE accessLevel = 0 AND online = 1",

"GetCount" => "
SELECT count(*)
FROM characters
[0:IFSETNN=]
WHERE
[1:IFSETNN=0]
characters.account_name like '%{param0}%'
[2:IFSETNN=1,2,3,4,5] AND [2:/IFSET]
[1:/IFSET]
[1:IFSETNN=1]
characters.char_name like '%{param1}%'
[2:IFSETNN=2,3,4,5] AND [2:/IFSET]
[1:/IFSET]
[1:IFSETNN=2]
characters.classid = '{param2}'
[2:IFSETNN=3,4,5] AND [2:/IFSET]
[1:/IFSET]
[1:IFSETNN=3,4,5] ( [1:/IFSET]
[1:IFSETNN=3]
characters.accessLevel > '0'
[2:IFSETNN=4,5] OR [2:/IFSET]
[1:/IFSET]
[1:IFSETNN=4]
characters.accessLevel = '0'
[2:IFSETNN=5] OR [2:/IFSET]
[1:/IFSET]
[1:IFSETNN=5]
characters.accessLevel < '0'
[1:/IFSET]
[1:IFSETNN=3,4,5] ) [1:/IFSET]
[0:/IFSET]
",

"GetCountGM" => "
SELECT count(*)
FROM characters
WHERE characters.accessLevel > 0",

"GetCountBanned" => "
SELECT count(*)
FROM characters
WHERE accessLevel < 0",

"GetList" => "
SELECT ".$fList_L2jFree["characters"].",".$fList_L2jFree["clan_data"].",".$fList_L2jFree["char_templates"]."
FROM `characters`
LEFT JOIN `char_templates` ON characters.classid = char_templates.classid
LEFT JOIN `clan_data` ON characters.clanid = clan_data.clan_id
[0:IFSETNN=3,4,5,6,7,8]
WHERE
[1:IFSETNN=3]
account_name like '%{param3}%'
[2:IFSETNN=4,5,6,7,8] AND [2:/IFSET]
[1:/IFSET]
[1:IFSETNN=4]
char_name like '%{param4}%'
[2:IFSETNN=5,6,7,8] AND [2:/IFSET]
[1:/IFSET]
[1:IFSETNN=5]
characters.classid = '{param5}'
[2:IFSETNN=6,7,8] AND [2:/IFSET]
[1:/IFSET]
[1:IFSETNN=6,7,8] ( [1:/IFSET]
[1:IFSETNN=6]
accessLevel > '0'
[2:IFSETNN=7,8] OR [2:/IFSET]
[1:/IFSET]
[1:IFSETNN=7]
accessLevel = '0'
[2:IFSETNN=8] OR [2:/IFSET]
[1:/IFSET]
[1:IFSETNN=8]
accessLevel < '0'
[1:/IFSET]
[1:IFSETNN=6,7,8] ) [1:/IFSET]
[0:/IFSET]
ORDER BY online DESC, accesslevel DESC [0:IFSET=0], {param0}[0:/IFSET]
[0:IFSET=0]LIMIT {param1}
[0:IFSETNN=2] ,{param2} [0:/IFSET]
[0:/IFSET]",

"GetOnline" => "
SELECT ".$fList_L2jFree["characters"].",".$fList_L2jFree["clan_data"].",".$fList_L2jFree["char_templates"]."
FROM `characters`
LEFT JOIN char_templates ON char_templates.ClassId = characters.classid
LEFT JOIN clan_data ON clan_data.clan_id = characters.clanid
WHERE accesslevel=0 AND online=1
ORDER BY online DESC, accesslevel DESC, exp DESC
LIMIT {param0}",

"GetClanMembersList" => "
SELECT ".$fList_L2jFree["characters"].", ".$fList_L2jFree["clan_data"].",".$fList_L2jFree["char_templates"]."
FROM `characters`
LEFT JOIN char_templates ON char_templates.classid = characters.classid
LEFT JOIN clan_data ON clan_data.clan_id = characters.clanid
WHERE clan_data.clan_name = '{param0}'
ORDER BY exp DESC",

"GetClanMembersListByID" => "
SELECT ".$fList_L2jFree["characters"].", ".$fList_L2jFree["clan_data"].",".$fList_L2jFree["char_templates"]."
FROM `characters`
LEFT JOIN char_templates ON char_templates.classid = characters.classid
LEFT JOIN clan_data ON clan_data.clan_id = characters.clanid
WHERE clan_data.clan_id = '{param0}'
ORDER BY exp DESC",

"GetCountRaceHumans" => "
SELECT count(*)
FROM characters
WHERE race=0 AND accesslevel=0",

"GetCountRaceElfs" => "
SELECT count(*)
FROM characters
WHERE race=1 AND accesslevel=0",

"GetCountRaceDElfs" => "
SELECT count(*)
FROM characters
WHERE race=2 AND accesslevel=0",

"GetCountRaceOrcs" => "
SELECT count(*)
FROM characters
WHERE race=3 AND accesslevel=0",

"GetCountRaceDwarfs" => "
SELECT count(*)
FROM characters
WHERE race=4 AND accesslevel=0",

"GetCountRaceKamaels" => "
SELECT count(*)
FROM characters
WHERE race=5 AND accesslevel=0",

"GetTop" => "
SELECT ".$fList_L2jFree["characters"].",".$fList_L2jFree["clan_data"].",".$fList_L2jFree["char_templates"]."
FROM `characters`
LEFT JOIN char_templates ON char_templates.classid = characters.classid
LEFT JOIN clan_data ON clan_data.clan_id = characters.clanid
WHERE accesslevel=0
ORDER BY exp DESC
LIMIT {param0}",

"GetTopPVP" => "
SELECT ".$fList_L2jFree["characters"].",".$fList_L2jFree["clan_data"].",".$fList_L2jFree["char_templates"]."
FROM `characters`
LEFT JOIN char_templates ON char_templates.ClassId = characters.classid
LEFT JOIN clan_data ON clan_data.clan_id = characters.clanid
WHERE accesslevel=0 AND pvpkills>0
ORDER BY pvpkills DESC, exp DESC
LIMIT {param0}",

"GetTopPK" => "
SELECT ".$fList_L2jFree["characters"].",".$fList_L2jFree["clan_data"].",".$fList_L2jFree["char_templates"]."
FROM `characters`
LEFT JOIN char_templates ON char_templates.ClassId = characters.classid
LEFT JOIN clan_data ON clan_data.clan_id = characters.clanid
WHERE accesslevel=0 AND pkkills>0
ORDER BY pkkills DESC, exp DESC
LIMIT {param0}",

"GetTopOnline" => "
SELECT ".$fList_L2jFree["characters"].",".$fList_L2jFree["clan_data"].",".$fList_L2jFree["char_templates"]."
FROM `characters`
LEFT JOIN char_templates ON char_templates.ClassId = characters.classid
LEFT JOIN clan_data ON clan_data.clan_id = characters.clanid
WHERE accesslevel=0
ORDER BY onlinetime DESC, exp DESC
LIMIT {param0}",

"DeleteCharacter" => "
DELETE FROM characters
WHERE charId='{param0}'",

"SetLocation" => "
UPDATE `characters`
SET
x='{param0}',
y='{param1}',
z='{param2}'
{param3}
WHERE charId='{param4}'",

"UpdateCharacter" => "
UPDATE `characters`
SET
`char_name` = '{param1}',
`race` = '{param2}',
`sex` = '{param3}',
`base_class` = '{param4}',
`title` = '{param5}',
`accesslevel` = '{param6}',
`level` = '{param7}',
`exp` = '{param8}',
`sp` = '{param9}',
`pvpkills` = {param10},
`pkkills` = {param11},
`karma` = {param12},
`nobless` = {param13},
`in_jail` = {param14},
`jail_timer` = {param15},
`banchat_timer` = {param16}
WHERE charId = {param0}
",
),

// character_subclasses
"character_subclasses" => array (
"GetSubclass" => "
SELECT ".$fList_L2jFree["character_subclasses"].", ".$fList_L2jFree["char_templates"]."
FROM `character_subclasses`
LEFT JOIN `char_templates` ON character_subclasses.class_id = char_templates.classid
WHERE charId = '{param0}'
ORDER BY level DESC",

"DeleteSubclasses" => "
DELETE FROM character_subclasses WHERE charId='{param0}'",
),

// character_friends
"character_friends" => array (
"GetFriends" => "
SELECT ".$fList_L2jFree["character_friends"]."
FROM `character_friends`
WHERE charId = '{param0}'
ORDER BY friend_name ASC",

"DeleteFriends" => "
DELETE FROM character_friends
WHERE charId='{param0}' OR friendId='{param0}'",
),

// character_hennas
"character_hennas" => array (
"DeleteHennas" => "
DELETE FROM character_hennas
WHERE charId='{param0}'",
),

// character_macroses
"character_macroses" => array (
"DeleteMacroses" => "
DELETE FROM character_macroses
WHERE charId='{param0}'",
),

// character_quests
"character_quests" => array (
"DeleteQuests" => "
DELETE FROM character_quests
WHERE charId='{param0}'",
),

// character_raidpoints
"character_raidpoints" => array (
"DeleteRaidPoints" => "
DELETE FROM character_raidpoints
WHERE owner_id='{param0}'",
),

// character_recipebook
"character_recipebook" => array (
"DeleteRecipebook" => "
DELETE FROM character_recipebook
WHERE charId='{param0}'",
),

// character_recommends
"character_recommends" => array (
"DeleteRecommends" => "
DELETE FROM character_recommends
WHERE charId='{param0}'",
),

// character_shortcuts
"character_shortcuts" => array (
"DeleteShortcuts" => "
DELETE FROM character_shortcuts
WHERE charId='{param0}'",
),

// character_skills
"character_skills" => array (
"DeleteSkills" => "
DELETE FROM character_skills
WHERE charId='{param0}'"
),

// character_skills_save
"character_skills_save" => array (
"Delete" => "
DELETE FROM character_skills_save
WHERE charId='{param0}'",
),

// clan_data
"clan_data" => array (
"GetAlly" => "
SELECT ".$fList_L2jFree["clan_data"].",".$fList_L2jFree["characters"].",".$fList_L2jFree["castle"].",".$fList_L2jFree["clanhall"]."
FROM `clan_data`
LEFT JOIN `characters` ON characters.charId = clan_data.leader_id
LEFT JOIN `castle` ON castle.id = clan_data.hasCastle
LEFT JOIN `clanhall` ON clanhall.ownerId = clan_data.clan_id
WHERE ally_id = '{param0}'
ORDER BY {param1}
LIMIT {param2}",

"GetAllyList" => "
SELECT ".$fList_L2jFree["clan_data"].",".$fList_L2jFree["characters"].",".$fList_L2jFree["castle"].",".$fList_L2jFree["clanhall"]."
FROM `clan_data`
LEFT JOIN `characters` ON characters.charId = clan_data.leader_id
LEFT JOIN `castle` ON castle.id = clan_data.hasCastle
LEFT JOIN `clanhall` ON clanhall.ownerId = clan_data.clan_id
WHERE ally_id
ORDER BY {param0}
LIMIT {param1}",

"GetAllyCount" => "
SELECT count(*)
FROM `clan_data`
WHERE ally_id = '{param0}'",

"GetAllyListCount" => "
SELECT count(*)
FROM `clan_data`
WHERE ally_id = '{param0}'",

"GetClan" => "
SELECT ".$fList_L2jFree["clan_data"].",".$fList_L2jFree["characters"].",".$fList_L2jFree["castle"].",".$fList_L2jFree["clanhall"]."
FROM `clan_data`
LEFT JOIN `characters` ON characters.charId = clan_data.leader_id
LEFT JOIN `clanhall` ON clanhall.ownerId = clan_data.clan_id
LEFT JOIN `castle` ON castle.id = clan_data.hasCastle
WHERE clan_id = '{param0}'",

"GetClanList" => "
SELECT ".$fList_L2jFree["clan_data"].",".$fList_L2jFree["characters"].",".$fList_L2jFree["castle"].",".$fList_L2jFree["clanhall"]."
FROM `clan_data`
LEFT JOIN `characters` ON characters.charId = clan_data.leader_id
LEFT JOIN `clanhall` ON clanhall.ownerId = clan_data.clan_id
LEFT JOIN `castle` ON castle.id = clan_data.hasCastle
[0:IFSETNN=0]ORDER BY {param0}[0:/IFSET]
[0:IFSETNN=1]LIMIT {param1}[0:/IFSET]",

"GetClanCount" => "
SELECT count(*)
FROM `clan_data`",

"GetTopClanList" => "
SELECT ".$fList_L2jFree["clan_data"].",".$fList_L2jFree["characters"].",csum,ccount,cavg
FROM clan_data
LEFT JOIN characters ON clan_data.leader_id=characters.charId
LEFT JOIN (
SELECT clanid, SUM(level) AS csum, count(level) AS ccount,
AVG(level) AS cavg
FROM characters
WHERE clanid GROUP BY clanid
) AS levels ON clan_data.clan_id = levels.clanid
ORDER BY csum DESC
LIMIT {param0}",

"DeleteClanByLeader" => "
DELETE FROM clan_data
WHERE leader_id='{param0}'",
),

// clan_wars
"clan_wars" => array(
"GetWarList" => "
SELECT ".$fList_L2jFree["clan_data"]."
FROM `clan_wars`
LEFT JOIN `clan_data` ON clan_wars.clan2 = clan_data.clan_id
WHERE clan_wars.clan1 = '{param0}'",
),

// castle
"castle" => array(
"GetCastles" => "
SELECT ".$fList_L2jFree["castle"].", ".$fList_L2jFree["clan_data"]."
FROM castle
LEFT JOIN clan_data ON clan_data.hasCastle = castle.id",
),

// siege_clans
"siege_clans" => array(
"GetSiegeInfo" => "
SELECT siege_clans.clan_id,type,castle_owner,clan_data.clan_name
FROM siege_clans
LEFT JOIN clan_data ON clan_data.clan_id = siege_clans.clan_id
WHERE castle_id = '{param0}'",
),

// seven_signs
"seven_signs" => array(
"GetCountDusk" => "
SELECT count(*)
FROM seven_signs
WHERE cabal='dusk'",

"GetCountDawn" => "
SELECT count(*)
FROM seven_signs
WHERE cabal='dawn'",

"GetSevenSignsStatus" => "
SELECT *
FROM seven_signs_status
LIMIT 1",

"DeleteCharacter" => "
DELETE FROM seven_signs
WHERE charId = '{param0}'",
),


"raidboss_spawnlist" => array(
"GetStatus" => "
SELECT raidboss_spawnlist.boss_id, raidboss_spawnlist.respawn_time, npc.level, npc.name
FROM raidboss_spawnlist
LEFT JOIN npc ON raidboss_spawnlist.boss_id = npc.id
ORDER BY raidboss_spawnlist.respawn_time ASC, npc.level DESC",
),

// items
"items" => array(
"GetItem" => "
SELECT ".$fList_L2jFree["items"]."
FROM `items`
WHERE items.object_id = '{param0}'
LIMIT 1",

"GetList" => "
SELECT ".$fList_L2jFree["items"].", ".$fList_L2jFree["items_info"]."
FROM items
LEFT JOIN armor ON armor.item_id = items.item_id
LEFT JOIN weapon ON weapon.item_id = items.item_id
LEFT JOIN etcitem ON etcitem.item_id = items.item_id
WHERE items.owner_id = '{param0}'",

"GetPaperdoll" => "
SELECT ".$fList_L2jFree["items"]."
FROM `items`
WHERE owner_id = '{param0}' and loc = 'PAPERDOLL'
ORDER BY loc_data",

"GetInventory" => "
SELECT ".$fList_L2jFree["items"]."
FROM `items`
WHERE owner_id = '{param0}' and loc = 'INVENTORY'
ORDER BY loc_data",
"GetMaxItem" => "
SELECT MAX(`object_id`)+1 FROM `items`",

"GetItemsWarehouse" => "
SELECT item_id, loc_data, count, loc, enchant_level FROM `items`
WHERE owner_id = '{param0}' and loc = 'WAREHOUSE'
ORDER BY loc_data",

"SearchItem" => "
SELECT object_id, count
FROM `items`
WHERE owner_id = '{param0}' AND item_id = '{param1}' AND loc = 'INVENTORY'
LIMIT 1",

"InsertItem" => "
INSERT INTO items (`owner_id`, `object_id`, `item_id`, `count`, `enchant_level`, `loc`)
VALUES ('{param0}', '{param1}', '{param2}', '{param3}', 0, 'INVENTORY')
",

"UpdateItem" => "
UPDATE items
SET
count = '{param1}',
enchant_level = '{param2}'
WHERE object_id = '{param0}'
",

"UpdateItemCount" => "
UPDATE items
SET
count = '{param1}'
WHERE object_id = '{param0}'
",

"DeleteByOwner" => "
DELETE FROM items
WHERE owner_id = '{param0}'",

"DeleteByOwnerItemID" => "
DELETE FROM items
WHERE owner_id = '{param0}' AND item_id = '{param1}'
[0:IFSETNN=2]LIMIT {param2}[0:/IFSET]",

"DeleteByOwnerObjectID" => "
DELETE FROM items
WHERE owner_id = '{param0}' AND object_id = '{param1}'
[0:IFSETNN=2]LIMIT {param2}[0:/IFSET]",
),
);

unset($fList_L2jFree);

?>
скрыть