Add exception in script

[Sigoles]

Hi, I have this script that cleans account / players inactives, but I wanted to add an exception where the script does not delete accounts ids that are in this table:

Help? tfs 1.2

table:

CREATE TABLE IF NOT EXISTS `z_character_trade` (
  `player_id` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `old_accountid` int(11) NOT NULL,
  PRIMARY KEY (`player_id`),
  UNIQUE KEY `player_id` (`player_id`),
  KEY `z_account_player` (`old_accountid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

script:

---
--- Generated by EmmyLua(https://github.com/EmmyLua)
--- Created by leu.
--- DateTime: 04/04/18 18:42
---
--[[ Clean Database by Cjaker | Refactor and SQL Optimizations by Leu ]]--
local inactiveMonths = 5 --> Quantos meses o player ficou inativo
local createdMonths = 2 --> Quantos meses a conta foi criada e não possui character criado.
local protectedAccIdEnd = 20 --ignorar accounts com id <= 20
local function clearInactivePlayers()
    local inactiveTimestamp = os.time() - (86400 * (inactiveMonths*30))
    local totalClear=0
    local fromClause = "`players` WHERE `account_id` > ".. protectedAccIdEnd .." AND lastlogin <= "..inactiveTimestamp
    local resultId = db.storeQuery("SELECT COUNT(*) as num_inativos FROM "..fromClause)
    if resultId ~= false then
        totalClear = result.getDataInt(resultId, 'num_inativos')
        result.free(resultId)
        if totalClear > 0 then
            db.asyncQuery("DELETE FROM "..fromClause)
        end
    end
    return totalClear
end
local function clearEmptyAccounts()
    local totalClear = 0
    local createdTimestamp = os.time() - (86400 * (createdMonths*30))
    local fromClause = "`accounts` as ACCS WHERE `id` > ".. protectedAccIdEnd .." AND `creation` <= "..createdTimestamp.." AND (SELECT COUNT(*) from `players` WHERE `account_id` = `ACCS`.`id`) > 0"
    local resultId = db.storeQuery("SELECT COUNT(*) as num_inativas FROM "..fromClause)
    if resultId~= false then
        totalClear = result.getDataInt(resultId,'num_inativas')
        result.free(resultId)
        if totalClear > 0 then
            db.asyncQuery("DELETE FROM "..fromClause)
        end
    end
    return totalClear
end
function onStartup()
    print('>> ' ..clearInactivePlayers().. " players inativos deletados.")
    print('>> ' ..clearEmptyAccounts().. " contas vazias deletadas.")
end

 

[Sigoles]

uppp

 

[Frija]

Try changing line 13 to:

local fromClause = "`players` WHERE `account_id` > ".. protectedAccIdEnd .." AND lastlogin <= "..inactiveTimestamp .. " AND `id` not in (select `player_id` from `z_character_trade`)"

Make sure it works correctly or just make database backup before executing it.

 

[Sigoles]

Try changing line 13 to:

local fromClause = "`players` WHERE `account_id` > ".. protectedAccIdEnd .." AND lastlogin <= "..inactiveTimestamp .. " AND `id` not in (select `player_id` from `z_character_trade`)"

Make sure it works correctly or just make database backup before executing it.

thanks, but got one error:

[Error - mysql_store_result] Query: SELECT COUNT(*) as num_inativos FROM `players` WHERE `account_id` > 20 AND lastlogin <= 1511746281 AND `id` NOT IN (SELECT `player_id` FROM `z_character_trade`
Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

[Bogart]

line 14 to:

local resultId = db.storeQuery("SELECT COUNT(*) as num_inativos, `id` FROM "..fromClause)

 

[Sigoles]

line 14 to:

local resultId = db.storeQuery("SELECT COUNT(*) as num_inativos, `id` FROM "..fromClause)

It worked without error, but now deleted many chars that were not inactive, created has only 1 month ... missed the part of the exception in clearEmptyAccounts () also,

thanks <>

 

[Sigoles]

upppp

 

[Bogart]

Line 27 and 28:

local fromClause = "`accounts` as ACCS WHERE `id` > ".. protectedAccIdEnd .." AND `creation` <= "..createdTimestamp.." AND (SELECT COUNT(*) from `players` WHERE `account_id` = `ACCS`.`id`) > 0 AND `ACCS`.`id` NOT IN (select `old_accountid` from `z_character_trade`)"
local resultId = db.storeQuery("SELECT COUNT(*) as num_inativas, `id` FROM "..fromClause)