NPC Send/read messages from MySQL

[PhoOwned]

NPC send player messages [from NPCs window] to MySQL table and read messages from MySQL and send them to players NPCs chat [if online].

TFS VERSIONS:
* 0.3.6 - tested
* 0.4 - should work

IN THIS VERSION:
* you can block spam with parameter messagesMinInterval
* script store messages from players in cache, it will not execute more then 20 SQL queries per second [default compiled TFS 0.3.6 will execute 4-6 queries/second]
* script store player messages in table with type 'memory' (it use RAM, not HDD)
* script move messages older then minute to table with type 'myisam' - use 1 query for aaallll messages ^_^

TODO:
* messagesOfflineLimit doesn't work
* show archive of last messagesOfflineLimit messages to player when he says 'hi'

I will post .php+.js script 'Website Online Customers Support' soon

Don't forget that player can write to NPC only when he is near him, but NPC can write to player from any distance.

MySQL schema.

CREATE TABLE IF NOT EXISTS `web_cs_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL DEFAULT '0',
  `from` int(11) NOT NULL DEFAULT '0',
  `to` int(11) NOT NULL DEFAULT '0',
  `text` varchar(255) NOT NULL DEFAULT '',
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

CREATE TABLE IF NOT EXISTS `web_cs_messages_tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL DEFAULT '0',
  `from` int(11) NOT NULL DEFAULT '0',
  `to` int(11) NOT NULL DEFAULT '0',
  `text` varchar(255) NOT NULL DEFAULT '',
  KEY `id` (`id`)
) ENGINE=MEMORY  DEFAULT CHARSET=latin1 ;

NPC xml:

<npc name="NPC Customer Supporter" script="data/npc/scripts/wocs.lua" walkinterval="25" floorchange="0" access="5" level="1" maglevel="1">
	<health now="150" max="150"/>
	<look type="128" head="20" body="100" legs="50" feet="99" addons="3"/>
	<parameters>
	</parameters>
</npc>

NPC script:

local messagesOfflineLimit = 20
local messagesMinInterval = 5

local lastPlayerMsg = {}
local nextReadId = 0
local nextTempDatabaseTableUpdate = 0
local messagesToSendCache = {}


function WOCS_getSupportersList()
	return {}
end

function WOCS_readMessages()
	local messagesList = {}
	if(nextReadId == 0) then
		local readLastId = db.getResult("SELECT `id` FROM `web_cs_messages_tmp` ORDER BY `id` DESC LIMIT 1")
		if(readLastId:getID() ~= -1) then
			nextReadId = readLastId:getDataInt("id") + 1
			readLastId:free()
		else
			nextReadId = 1
		end
	end
	local readMessages = db.getResult("SELECT `id`, `to`, `text` FROM `web_cs_messages_tmp` WHERE `id` >= " .. nextReadId .. " AND `from` = 0 ORDER BY `id`")
	if(readMessages:getID() ~= -1) then
		repeat
			nextReadId = readMessages:getDataInt("id") + 1
			table.insert(messagesList, {['to'] = readMessages:getDataInt("to"), ['text'] = readMessages:getDataString("text")})
		until not(readMessages:next())
		readMessages:free()
	end
	return messagesList
end

function WOCS_sendMessages()
	if(#messagesToSendCache > 0) then
		local tmpMessagesToSend = {}
		for wm, tmpMessageToSend in pairs(messagesToSendCache) do
			table.insert(tmpMessagesToSend, "(NULL, " .. tmpMessageToSend.time .. ", " .. tmpMessageToSend.guid .. ", 0, " .. db.escapeString(tmpMessageToSend.text) .. ")")
		end
		messagesToSendCache = {}
		db.executeQuery("INSERT INTO `web_cs_messages_tmp` (`id`, `time`, `from`, `to`, `text`) VALUES " .. table.concat(tmpMessagesToSend, ", "));
	end
end

local keywordHandler = KeywordHandler:new()
local npcHandler = NpcHandler:new(keywordHandler)
NpcSystem.parseParameters(npcHandler)


function onCreatureAppear(cid)				npcHandler:onCreatureAppear(cid)			end
function onCreatureDisappear(cid)			npcHandler:onCreatureDisappear(cid)			end
function onThink()
	WOCS_sendMessages()
	local messagesTmp = WOCS_readMessages()
	if(#messagesTmp > 0) then
		for i, messageToPlayer in pairs(messagesTmp) do
			local tmpPlayer = getPlayerByGUID(messageToPlayer.to)
			if(tmpPlayer ~= nil) then
				lastPlayerMsg[messageToPlayer.to] = nil
				selfSay(messageToPlayer.text, tmpPlayer)
			end
		end
	end
	if(nextTempDatabaseTableUpdate < os.time()) then
		nextTempDatabaseTableUpdate = os.time() + 10
		local messageMaxIdRes = db.getResult("SELECT `id` FROM `web_cs_messages_tmp` WHERE `time` <= " .. (os.time() - 60))
		if(messageMaxIdRes:getID() ~= -1) then
			local messageMaxId = messageMaxIdRes:getDataInt("id")
			messageMaxIdRes:free()
			local tempDatabaseMessages = db.getResult("SELECT * FROM `web_cs_messages_tmp` WHERE `id` <= " .. messageMaxId)
			if(tempDatabaseMessages:getID() ~= -1) then
				db.executeQuery("DELETE FROM `web_cs_messages_tmp` WHERE `id` <= " .. messageMaxId);
				local messagesToStore = {}
				repeat
					table.insert(messagesToStore, "(" .. tempDatabaseMessages:getDataInt("id") ..", " .. tempDatabaseMessages:getDataInt("time") .. ", " .. tempDatabaseMessages:getDataInt("from") .. ", " .. tempDatabaseMessages:getDataInt("to") .. ", " .. db.escapeString(tempDatabaseMessages:getDataString("text")) .. ")")
				until not(tempDatabaseMessages:next())
				tempDatabaseMessages:free()
				db.executeQuery("INSERT INTO `web_cs_messages` (`id`, `time`, `from`, `to`, `text`) VALUES " .. table.concat(messagesToStore, ", "));
			end
		end
	end
	npcHandler:onThink()
end

function onCreatureSay(cid, type, msg)
	local guid = getPlayerGUID(cid)
	if(not npcHandler:isFocused(cid)) then
		if(msgcontains(msg, 'hi') or msgcontains(msg, 'hello')) then
			selfSay('Hello ' .. getCreatureName(cid) .. '!', 0)
			local supportersList = WOCS_getSupportersList()
			if(#supportersList > 0) then
				selfSay(#supportersList .. ' customer supporters online.', cid)
			else
				selfSay('0 customer supporters online. Your messages (max. ' .. messagesOfflineLimit .. ') will be stored in database and you will get answers later.', cid)
			end
			npcHandler:addFocus(cid)
		end
	elseif(type == TALKTYPE_PRIVATE_PN) then
		if(lastPlayerMsg[guid] == nil or lastPlayerMsg[guid] + messagesMinInterval <= os.time()) then
			table.insert(messagesToSendCache, {['time'] = os.time(), ['guid'] = guid, ['text'] = msg})
			lastPlayerMsg[guid] = os.time()
		else
			selfSay("You spam too much. Wait few seconds! Your message: " .. string.sub(msg, 0, 20) .. "... WAS NOT SEND!", cid)
		end
	end
end

npcHandler:addModule(FocusModule:new())