gmstrikker
Well-Known Member
- Joined
- Jul 30, 2014
- Messages
- 458
- Solutions
- 1
- Reaction score
- 50
On Tibia and on OTs there is no good guilds systems like in other games that u can do tasks on guild...
But i found a system that bring it possible to OTs: Advanced Guild System (https://www.xtibia.com/forum/topic/227593-advanced-guild-system/) (with Guild Rank; Guild Tasks; Guild Banker.)
But i got two errors:
1- Add guild balance give a error
When i try to deposit with NPC guild banker, it shows this error:
Same thing when finish guild task system (it should add money to guild bank too):
The error looks be in this function:
Guild bank full code:
2 Problem two was on installation, one command return an SQL error
This command was necessary (idk what it do):
Shows this SQL error:
All others commands works fine, full commands:
Guild System full code lib:
Is anybody knows how to fix the problem 1 or 2, or both?
There anything wrong on this SQL func??
There is any other way to:
1- change this query func:
2- do this query command:
is anybody knows someones who knows SQL here?
But i found a system that bring it possible to OTs: Advanced Guild System (https://www.xtibia.com/forum/topic/227593-advanced-guild-system/) (with Guild Rank; Guild Tasks; Guild Banker.)
But i got two errors:
1- Add guild balance give a error
When i try to deposit with NPC guild banker, it shows this error:
Code:
[12:58:40.908] mysql_real_query(): UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = 2) + 50000 WHERE `id` = 2 - MYSQL ERROR: Table 'guilds' is specified twice, both as a target for 'UPDATE' and as a separate source for data (1093)
Same thing when finish guild task system (it should add money to guild bank too):
Code:
[13:51:22.851] mysql_real_query(): UPDATE guilds SET points = (1 + (SELECT points FROM guilds WHERE id = 2)) WHERE id = 2 - MYSQL ERROR: Table 'guilds' is specified twice, both as a target for 'UPDATE' and as a separate source for data (1093)
[13:51:22.851] mysql_real_query(): UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = 2) + 150000 WHERE `id` = 2 - MYSQL ERROR: Table 'guilds' is specified twice, both as a target for 'UPDATE' and as a separate source for data (1093)
The error looks be in this function:
Code:
function doGuildAddBalance(guild_id, value)
local func = db.executeQuery or db.query
func("UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = "..guild_id..") + "..value.." WHERE `id` = "..guild_id)
return true
end
Guild bank full code:
Boris.xml
Code:<npc name="Boris The Guild Banker" script="data/npc/scripts/guildbank.lua" skull="green" floorchange="0" access="5" level="1" maglevel="1"> <health now="150" max="150"/> <look type="132" head="115" body="95" legs="125" feet="115" addons="1" corpse="2212"/> <parameters> <parameter key="message_greet" value="Welcome to the {guild bank}, |PLAYERNAME|. How can I help you?"/> <parameter key="message_farewell" value="Time is money. Remember that." /> <parameter key="message_walkaway" value="Next please!" /> </parameters> </npc>
guildbank.lua
Code:local keywordHandler = KeywordHandler:new() local npcHandler = NpcHandler:new(keywordHandler) NpcSystem.parseParameters(npcHandler) local talkState = {} function onCreatureAppear(cid) npcHandler:onCreatureAppear(cid) end function onCreatureDisappear(cid) npcHandler:onCreatureDisappear(cid) end function onCreatureSay(cid, type, msg) npcHandler:onCreatureSay(cid, type, msg) end function onThink() npcHandler:onThink() end function writeBankFile(guildname, name, amount, operation) local file = io.open("guild_bank/" .. guildname .. ".txt", "a+") if file then file:write(name .. " - " .. operation .. " - " .. amount .. " - " .. os.date("%c") .. "\n") file:close() return true end return false end function testGuildFile(guildname) local file = io.open("guild_bank/" .. guildname .. ".txt", "r") if file then file:close() return true end return false end function getBankLog(guildname) local file = io.open("guild_bank/" .. guildname .. ".txt", "r") local content = file:read("*a") file:close() return content end function creatureSayCallback(cid, typ, msg) if(not npcHandler:isFocused(cid)) then return false end local talkUser = NPCHANDLER_CONVBEHAVIOR == CONVERSATION_DEFAULT and 0 or cid local STG_ACCOUNT = 950 local STG_WITHDRAW_PERMISSION = 951 local STG_DEPOSIT_PERMISSION = 952 local guild_rank = getPlayerGuildLevel(cid) local guild = getPlayerGuildId(cid) if msgcontains(msg, "guild bank") then if not hasGuild(cid) then selfSay("This is the Guild Bank, " .. getCreatureName(cid) .. ". You need a guild to open an account here.", cid) elseif getGuildStorageValue(guild, STG_ACCOUNT) == 1 then selfSay("I see here that your guild already has an account. Would you like to {manage} it?", cid) talkState[talkUser] = 1 elseif guild_rank >= 3 then selfSay("Your guild doesn't have an account yet. Would you like to {create} one?", cid) talkState[talkUser] = 2 else selfSay("I'm sorry, your guild doesn't have an account with us yet. Please, tell your leader to come and create one here.", cid) end elseif msgcontains(msg, "manage") and talkState[talkUser] == 1 then local str = guild_rank < 3 and "Ok, then. You can check the {balance}, {withdraw} or {deposit}." or "Ok, then. You can check the {balance}, the transaction {list}, {withdraw}, {deposit} or manage account's {permissions}." selfSay(str, cid) talkState[talkUser] = 3 elseif msgcontains(msg, "create") and talkState[talkUser] == 2 then selfSay("In order to create your account, we need to set some permissions. There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders. To withdraw money from the guild account, which permission do you want to set?", cid) talkState[talkUser] = 4 elseif talkState[talkUser] == 4 then local permission, permissions = tonumber(msg), {1, 2, 3} if isInArray(permissions, permission) then setGuildStorageValue(guild, STG_WITHDRAW_PERMISSION, permission) selfSay("Ok. Withdraw permission is set. Now, to deposit money into your guild's account, which permission do you want to set?", cid) talkState[talkUser] = 5 else selfSay("That wasn't a valid permission. There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders.", cid) end elseif talkState[talkUser] == 5 then local permission, permissions = tonumber(msg), {1, 2, 3} if isInArray(permissions, permission) then setGuildStorageValue(guild, STG_DEPOSIT_PERMISSION, permission) setGuildStorageValue(guild, STG_ACCOUNT, 1) selfSay("Ok. Deposit permission is set and your guild account has been successfully created. Would you like to make a {deposit}?", cid) talkState[talkUser] = 3 else selfSay("That wasn't a valid permission. There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders.", cid) end elseif msgcontains(msg, "withdraw") and talkState[talkUser] == 3 then if guild_rank >= getGuildStorageValue(guild, STG_WITHDRAW_PERMISSION) then selfSay("How much would you like to withdraw?", cid) talkState[talkUser] = 6 else selfSay("Sorry, but you are not allowed to withdraw money from this account. Speak to your guild leader.", cid) end elseif talkState[talkUser] == 6 then local withdraw_amount = tonumber(msg) local guild_balance = getGuildBalance(guild) if withdraw_amount and withdraw_amount >= 1 and guild_balance > withdraw_amount then withdraw_amount = math.floor(withdraw_amount) if doGuildAddBalance(guild, - withdraw_amount) then doPlayerAddMoney(cid, withdraw_amount) selfSay("Done. Would you like anything else?", cid) writeBankFile(getGuildName(guild), getCreatureName(cid), withdraw_amount, "withdraw") talkState[talkUser] = 3 else selfSay("An error has ocurred and the transaction couldn't be completed.", cid) talkState[talkUser] = 3 end else selfSay("You cannot withdraw that amount from your guild's account. Your balance is " .. guild_balance .. ".", cid) end elseif msgcontains(msg, "deposit") and talkState[talkUser] == 3 then if guild_rank >= getGuildStorageValue(guild, STG_WITHDRAW_PERMISSION) then selfSay("How much would you like to deposit?", cid) talkState[talkUser] = 10 else selfSay("Sorry, but you are not allowed to deposit money into this account. Speak to your guild leader.", cid) end elseif talkState[talkUser] == 10 then local deposit_amount = tonumber(msg) local guild_balance = getGuildBalance(guild) if deposit_amount and math.floor(deposit_amount) > 0 then deposit_amount = math.floor(deposit_amount) if doPlayerRemoveMoney(cid,deposit_amount) then doGuildAddBalance(guild, deposit_amount) writeBankFile(getGuildName(guild), getCreatureName(cid), deposit_amount, "deposit") selfSay("Done. Would you like anything else?", cid) talkState[talkUser] = 3 else selfSay("I'm sorry, but you do not have that much gold.", cid) end else selfSay("Please, don't waste my time.", cid) end elseif msgcontains(msg, "balance") and talkState[talkUser] == 3 then selfSay("Account's balance is " .. getGuildBalance(guild) .. ". Is there anything else I can help you with?", cid) elseif msgcontains(msg, "permissions") and talkState[talkUser] == 3 and guild_rank >= 3 then selfSay("There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders. To withdraw money from the guild account, which permission do you want to set?", cid) talkState[talkUser] = 7 elseif talkState[talkUser] == 7 then local permission, permissions = tonumber(msg), {1, 2, 3} if isInArray(permissions, permission) then setGuildStorageValue(guild, STG_WITHDRAW_PERMISSION, permission) selfSay("Ok. Withdraw permission is set. Now, to deposit money into your guild's account, which permission do you want to set?", cid) talkState[talkUser] = 8 else selfSay("That wasn't a valid permission. There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders.", cid) end elseif talkState[talkUser] == 8 then local permission, permissions = tonumber(msg), {1, 2, 3} if isInArray(permissions, permission) then setGuildStorageValue(guild, STG_DEPOSIT_PERMISSION, permission) selfSay("Ok. Deposit permission is set. Can I help you with anything else?", cid) talkState[talkUser] = 3 else selfSay("That wasn't a valid permission. There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders.", cid) end elseif msgcontains(msg, "list") and talkState[talkUser] == 3 and guild_rank >= 3 then local guildName = getGuildName(guild) if not testGuildFile(guildName) then selfSay("That option is currently disabled.", cid) else local str = getBankLog(guildName) str = string.explode(str, "\n") local newStr = "" local tableSize = type(str) == "table" and #str or false if not tableSize or (tableSize and tableSize <= 1) then selfSay("No transactions have been registered yet. Can I help you with anything else?", cid) else for i = 0, 9 do local str_add = str[tableSize - 10 + i] if str_add then newStr = newStr .. str_add .. "\n" end end selfSay("Here you go, the last 10 transactions made in that account.", cid) doPlayerPopupFYI(cid, newStr) end end end return true end npcHandler:setCallback(CALLBACK_MESSAGE_DEFAULT, creatureSayCallback) npcHandler:addModule(FocusModule:new())
2 Problem two was on installation, one command return an SQL error
This command was necessary (idk what it do):
Code:
CREATE TRIGGER `guilds_update`
AFTER UPDATE OF rank_id ON players
BEGIN
UPDATE players
SET guild_id = IFNULL( (
SELECT guild_id
FROM guild_ranks
WHERE id = rank_id
)
, 0 );
END;
Shows this SQL error:
Code:
SQL query: [IMG alt="Documentation"]http://localhost/phpmyadmin/themes/dot.gif[/IMG]
CREATE TRIGGER `guilds_update`
AFTER UPDATE OF rank_id ON players
BEGIN
UPDATE players
SET guild_id = IFNULL( (
SELECT guild_id
FROM guild_ranks
WHERE id = rank_id
)
, 0 )
MySQL said: [IMG alt="Documentation"]http://localhost/phpmyadmin/themes/dot.gif[/IMG]
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OF rank_id ON players
BEGIN
UPDATE players
' at line 2
All others commands works fine, full commands:
FULL SQL installation commands:
CREATE TABLEguild_storages
(
guild_id
int NOT NULL default 0,
key
int NOT NULL default 0,
value
varchar(255) NOT NULL default 0
);
CREATE TRIGGERguilds_update
AFTER UPDATE OF rank_id ON players
BEGIN
UPDATE players
SET guild_id = IFNULL( (
SELECT guild_id
FROM guild_ranks
WHERE id = rank_id
)
, 0 );
END;
ALTER TABLE guilds ADD points INT DEFAULT 0 NOT NULL
ALTER TABLE guilds ADD balance BIGINT default 0 NOT NULL
ALTER TABLE players ADD guild_id INT DEFAULT 0 NOT NULL
UPDATE players SET guild_id = IFNULL((SELECT guild_id FROM guild_ranks WHERE id = rank_id), 0)
Guild System full code lib:
lib/guildlib.lua
Code:--[[ Queries: CREATE TABLE `guild_storages` ( `guild_id` int NOT NULL default 0, `key` int NOT NULL default 0, `value` varchar(255) NOT NULL default 0 ); CREATE TRIGGER `guilds_update` AFTER UPDATE OF rank_id ON players BEGIN UPDATE players SET guild_id = IFNULL( ( SELECT guild_id FROM guild_ranks WHERE id = rank_id ) , 0 ); END; ALTER TABLE guilds ADD points INT DEFAULT 0 NOT NULL ALTER TABLE guilds ADD balance BIGINT default 0 NOT NULL ALTER TABLE players ADD guild_id INT DEFAULT 0 NOT NULL UPDATE players SET guild_id = IFNULL((SELECT guild_id FROM guild_ranks WHERE id = rank_id), 0) ]] -- Advanced Guild System maintenance functions function installAdvancedGuildSystem() local func = db.query or db.executeQuery local query = db.getResult("SELECT balance FROM guilds") if query:getID() == -1 then func("ALTER TABLE guilds ADD balance INT default 0 NOT NULL") else query:free() end local query2 = db.getResult("SELECT points FROM guilds;") if query:getID() == -1 then func("ALTER TABLE guilds ADD points INT DEFAULT 0 NOT NULL") else query2:free() end if func("ALTER TABLE players ADD guild_id INT DEFAULT 0 NOT NULL") then if func("UPDATE players SET guild_id = IFNULL((SELECT guild_id FROM guild_ranks WHERE `id` = rank_id), 0)") then if func("CREATE TABLE `guild_storages` (`guild_id` INT NOT NULL DEFAULT 0, `key` INT NOT NULL DEFAULT 0, `value` VARCHAR(255) NOT NULL DEFAULT 0);") then if func("CREATE TRIGGER [`rank_id update`] AFTER UPDATE OF rank_id ON players BEGIN UPDATE players SET guild_id = IFNULL((SELECT guild_id FROM guild_ranks WHERE `id` = rank_id), 0); END;") then setGlobalStorageValue(50050, 1) print("Advanced Guild System installation successful.") return true end end end end print("Advanced Guild System could NOT be installed.") return false end function updateGuildColumn() print("Updating guild column in players table...") local func = db.query or db.executeQuery local str = "failure." if func("UPDATE players SET guild_id = IFNULL((SELECT guild_id FROM guild_ranks WHERE `id` = rank_id),0)") then str = "success!" end print("Update was a "..str) end isAGSInstalled = getGlobalStorageValue(50050) == 1 -- Guild Points functions function getGuildPoints(guild_id) local ret = db.getResult("SELECT points FROM guilds WHERE id = " .. guild_id) if ret:getID() == -1 then return -1 end return ret:getDataInt("points") end function doGuildAddPoints(guild_id, value) local func = db.executeQuery or db.query return func("UPDATE guilds SET points = ("..value.." + (SELECT points FROM guilds WHERE id = "..guild_id..")) WHERE id = "..guild_id) end -- General guild functions function hasGuild(cid) return getPlayerGuildId(cid) > 0 end function isInGuild(cid, guildId) return getPlayerGuildId(cid) == guildId end function getGuildName(guild_id) local query = db.getResult("SELECT name FROM guilds WHERE id = "..guild_id) local ret = query:getID() == -1 and false or query:getDataString("name") query:free() return ret end function getGuildMembersGUID(guild_id) if db.getResult("SELECT ownerid FROM guilds WHERE id = "..guild_id):getID() > -1 then local members = {} local query = db.getResult("SELECT `id` FROM players WHERE guild_id = "..guild_id) if query:getID() == -1 then return false end repeat local member = query:getDataInt("id") table.insert(members, member) until not query:next() query:free() return members end return false end function getPlayerGuildIdByGUID(guid) local query = db.getResult("SELECT guild_id FROM players WHERE id = "..guid) return query:getDataInt("guild_id") end function getOnlineGuildMembersByRank(guildId, rank, selectLowerRanks) if not rank then rank = 3 selectLowerRanks = true end local list = {} for _, pid in pairs(getPlayersOnline()) do if getPlayerGuildId(pid) == guildId then local playerRank = getPlayerGuildLevel(pid) if playerRank == rank or (selectLowerRanks and playerRank <= rank) then table.insert(list, pid) end end end return list end function doFunctionOnGuildMembers(guildId, func) local players = getPlayersOnline() local count = 0 for _, pid in pairs(players) do if getPlayerGuildId(pid) == guildId then func(pid) count = count + 1 end end return count end -- Guild Storage functions function setGuildStorageValue(guild_id, key, value) local func = db.executeQuery or db.query local query = db.getResult("SELECT `value` FROM guild_storages WHERE `key` = "..key.." AND guild_id = "..guild_id) if query:getID() == -1 then return func("INSERT INTO guild_storages (guild_id, `key`, `value`) VALUES ("..guild_id..", "..key..", "..value..")") end return func("UPDATE guild_storages SET `value` = "..value.." WHERE `key` = "..key.." AND guild_id = "..guild_id) end function getGuildStorageValue(guild_id, key) local ret = db.getResult("SELECT `value` FROM guild_storages WHERE guild_id = "..guild_id.." AND `key` = "..key) if ret:getID() == -1 then return -1 end return ret:getDataInt("value") or ret:getDataString("value") end -- Guild Balance functions function getGuildBalance(guild_id) local balance = db.getResult("SELECT balance FROM guilds WHERE `id` = "..guild_id) local ret = balance:getID() >= 0 and balance:getDataInt("balance") or false balance:free() return ret end function setGuildBalance(guild_id, value) local func = db.executeQuery or db.query func("UPDATE guilds SET balance = "..value.." WHERE `id` = "..guild_id) return true end function doGuildAddBalance(guild_id, value) local func = db.executeQuery or db.query func("UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = "..guild_id..") + "..value.." WHERE `id` = "..guild_id) return true end
talkactions.xml
Code:<talkaction words="/agsinstall" access="5" event="script" value="advanced_guild_system_install.lua"/>
advanced_guild_system_install.lua
Code:function onSay(cid, words, param) installAdvancedGuildSystem() end
Is anybody knows how to fix the problem 1 or 2, or both?
There anything wrong on this SQL func??
Code:
function doGuildAddBalance(guild_id, value)
local func = db.executeQuery or db.query
func("UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = "..guild_id..") + "..value.." WHERE `id` = "..guild_id)
return true
end
There is any other way to:
1- change this query func:
Code:
function doGuildAddBalance(guild_id, value)
local func = db.executeQuery or db.query
func("UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = "..guild_id..") + "..value.." WHERE `id` = "..guild_id)
return true
end
2- do this query command:
Code:
CREATE TRIGGER `guilds_update`
AFTER UPDATE OF rank_id ON players
BEGIN
UPDATE players
SET guild_id = IFNULL( (
SELECT guild_id
FROM guild_ranks
WHERE id = rank_id
)
, 0 );
END;
is anybody knows someones who knows SQL here?