NPC [READY]functions for NPC Banker (transfer!)

[Gesior.pl]

Today I wrote one useful function.
getBA(name)
(bank account storage id = 300)
Add this code to you NPC / npc.lua.

function getBA(name)
    local cid = getPlayerByName(name)
    if isPlayer(cid) == TRUE then
        --player is online 
        local player_cash_str = getPlayerStorageValue(cid, 300)
        --create bank account if not exist
        if player_cash_str < 0 then
            setPlayerStorageValue(cid,300,0)
            player_cash = 0
        else
            player_cash = player_cash_str
        end
    else
        --player is offline, load DB connection info and connect
        dofile("./config.lua")
        if sqlType == "mysql" then 
             env = assert(luasql.mysql()) 
             con = assert(env:connect(mysqlDatabase, mysqlUser, mysqlPass, mysqlHost, mysqlPort)) 
        else -- sqlite 
             env = assert(luasql.sqlite3()) 
             con = assert(env:connect(sqliteDatabase)) 
        end 
        --check is player exist
        local nametodb = escapeString(name)
        result_plr = assert(con:execute("SELECT `id` FROM `players` WHERE `name` = '" .. nametodb .. "';"))
        player = result_plr:fetch({}, "a")
        local players = 0
        local guid = 0
        while player do
            players = players + 1
            guid = tonumber(player.id)
            player = result_plr:fetch (player, "a")
        end
        if players > 0 then
            if guid > 0 then
            --player exist, check his account status
                storageqry = assert(con:execute("SELECT `value` FROM `player_storage` WHERE `player_id` = '" .. guid .. "' AND `key` = 300;"))
                storage = storageqry:fetch({}, "a")
                local whiles = 0
                while storage do
                    whiles = whiles + 1
                    player_cash = tonumber(storage.value)
                    storage = storageqry:fetch (storage, "a")
                end
                if whiles < 1 then
                    --if player hasn't account, create account and set account balance value to 0
                    assert(con:execute("INSERT INTO `player_storage` (`player_id` ,`key` ,`value`) VALUES ('" .. guid .. "', '300', '0');"))
                    player_cash = 0
                end
            end
        else
            --player with this name doesn't exist
            player_cash = -1
        end
        con:close()
        env:close()
    end
    --if player not exist return -1
    --if player exist return his bank account value
    return player_cash
end

Old balance check code in NPC scripts:

        local balance = getPlayerStorageValue(cid, STORAGE_BANK )
        if  balance >= 0 then
            selfSay("Your account balance is " .. tostring(balance) .. " gold.")
        else
            selfSay("You haven't stored anything in your account yet.")
        end

New balance check code in NPC scripts :

        local name = getPlayerName(cid)
        local balance = getBA(name)
        if  balance > 0 then
            selfSay("Your account balance is " .. tostring(balance) .. " gold.")
        else
            selfSay("You haven't stored anything in your account yet.")
        end

It check balance of offline and online players, auto create account if player doesn't have.
RETURN:
-1 - if player with name doesn't exist on server
0 or more - cash on account
I'll try to post "setBA(name, value)" - it set back account value for offline and online players.

 

[Gesior.pl]

setBA(name, value)
Second function

function setBA(name, nvalue)
	local cid = getPlayerByName(name)
	set_status = -1
	if isPlayer(cid) == TRUE then
		--player is online 
		setPlayerStorageValue(cid,300,nvalue)
		set_status = 1
		return 1
	else
		--player is offline, load DB connection info and connect
		dofile("./config.lua")
	    if sqlType == "mysql" then 
	         env = assert(luasql.mysql()) 
	         con = assert(env:connect(mysqlDatabase, mysqlUser, mysqlPass, mysqlHost, mysqlPort)) 
	    else -- sqlite 
	         env = assert(luasql.sqlite3()) 
	         con = assert(env:connect(sqliteDatabase)) 
	    end 
		--check is player exist
		local nametodb = escapeString(name)
		result_plr = assert(con:execute("SELECT `id` FROM `players` WHERE `name` = '" .. nametodb .. "';"))
		player = result_plr:fetch({}, "a")
		local players = 0
		local guid = 0
		while player do
			players = players + 1
			guid = tonumber(player.id)
			player = result_plr:fetch (player, "a")
		end
		if players > 0 then
			if guid > 0 then
			--player exist, check his account status
				storageqry = assert(con:execute("SELECT `value` FROM `player_storage` WHERE `player_id` = '" .. guid .. "' AND `key` = 300;"))
				storage = storageqry:fetch({}, "a")
				local whiles = 0
				while storage do
					whiles = whiles + 1
					storage = storageqry:fetch (storage, "a")
				end
				if whiles < 1 then
					--if player hasn't account, create account and set account balance value 'nvalue'
					assert(con:execute("INSERT INTO `player_storage` (`player_id` ,`key` ,`value`) VALUES ('" .. guid .. "', '300', '" .. nvalue .. "');"))
					set_status = 1
				else
					assert(con:execute("UPDATE `player_storage` SET `value` = '" .. nvalue .. "' WHERE `player_id` = '" .. guid .. "' AND `key` = 300;"))
					set_status = 1
				end
			end
		else
			--player with this name doesn't exist
			set_status = -1
		end
		con:close()
		env:close()
	end
	--if player not exist return -1
	--if player exist return his bank account value
	return set_status
end

RETURN:
-1 - if player with given name doesn't exist in database/ots = account value not changed
1 - if changed player account value to new
---------------------------------------
I've tested both functions with TFS 0.2.9. If you find any bug post in this thread before I make NPC Banker script

 

[Colandus]

Okay, could you tell me the differences between both functions?

Anyways, nice done, hope someone will use it

 

[Gesior.pl]

getBA - return value of storage id 300 or error (-1)
setBA - set value of storage id 300 or error (-1)
:>
I'll try to make Banker NPC with transfer function.

 

[Jonern]

You must learn to prevent SQL-injections. Names can contain ' which would mess up your query.
Solution: escapeString(str)

 

[Mokerhamer]

Thread Moved to Npc Section since it's an Npc Script

 

[Gesior.pl]

local nametodb = escapeString(name)
result_plr = assert(con:execute("SELECT `id` FROM `players` WHERE `name` = '" .. nametodb .. "';"))

Now good? Safe?

 

[Ronaldino]

local nametodb = escapeString(name)
result_plr = assert(con:execute("SELECT `id` FROM `players` WHERE `name` = '" .. nametodb .. "';"))

Now good? Safe?

I hope so.

Btw. Nice piece of code ^_^

 

[AGS]

Nice =O

I really needed this, I had a bank npc but with no transfers.

Could you post a example of how to do the transfer thing? xD

 

[Gesior.pl]

Player: hi
NPC: hi
Player: transfer
NPC: Please tell me name of player to transfer.
//here you use my first code
Player: <name of player>
NPC: Ok. Now tell me how much gold do you want to transfer? (it say my code if all is ok)
Player: 123 gold
//here use my second script
NPC: You have transferred 123 gold to <name of player>. Your actuall account balance is xx <actual bank status> gold.
Player: Bye?

usefull scripts to this dialog:

//WHEN PLAYER TELL NAME OF PLAYER TO TRANSFER
if getBA(msg) >= 0 then --player with this name exist
transferto = msg
selfSay('Ok. Now tell me how much gold do you want to transfer?')
//now go to (step) code below.. "WHEN PLAYER TELL NUMBER OF GOLD TO TRANSFER in 'msg'"
else -- player with this name doesn't exist
selfSay('Player with name ' .. msg .. ' does not exist. Please tell me other name.')
end

//WHEN PLAYER TELL NUMBER OF GOLD TO TRANSFER in 'msg'
count = getNumber(msg)
if count > 0 than --its number (msg)
playerbalance = getBA(getPlayerName(cid)) --get balance value of player
if playerbalance >= count --player who try to transfer has enought money at bank account
if setBA(transferto, getBA(transferto)+count) > 0 then -- if script set "totransfer" balance get cash from player account
playerbalance = playerbalance - count
setBA(getPlayerName(cid), playerbalance)
selfSay('You have transferred '..count..' gold to '..transferto..'. Your actuall account balance is '..playerbalance..' gold.')
else --problem with database? dont lose money..
selfSay('Problem with transfer. Sorry.')
end
else -- player dont have enought cash at bank account
selfSay('You do not have enought money at your account.')
count = 0
end
else --its not a number (msg)
selfSay('Please tell me how much do you want to transfer.')
end

You need "getNumber" function from Sizaro banker script:

function getNumber(txt)
x = string.gsub(txt,"%a","")
x = tonumber(x)
if x ~= nill and x > 0 then
return x
else
return 0
end
end

 

[Saj]

this is great, good job!

 

[AGS]

Thanks, I did it basing on the example script you posted!

 

[Jester]

My other banker is messed up.. Could you post your full script?

 

[Gesior.pl]

My other banker is messed up.. Could you post your full script?

http://otland.net/showthread.php?t=3962
I made this NPC specialy for you
Maybe someone else need it too