How to send Weekly Rewards to players

now I have this script CREATE TABLE `poring` ( `char_id` INT PRIMARY KEY, `name` VARCHAR(24), `kills` INT, KEY(`kills`) ) ENGINE = InnoDB; prontera,155,185,5 script Poring Rank 1_F_MARIA,{ .@nb = query_sql("SELECT `name`, RANK() OVER(ORDER BY `kills` DESC), `kills` FROM `poring` ORDER BY `kills` DESC LIMIT 5", .@name$, .@rank, .@kills); if (!.@nb) mes "no ranking yet"; for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; next; if (query_sql("SELECT RANK() OVER(ORDER BY `kills` DESC), `kills` FROM `poring` WHERE `char_id` = "+ getcharid(CHAR_ID_CHAR), .@rank, .@kills)) { mes "Your kills -> "+ .@kills; mes "Your Rank -> No."+ .@rank; } else mes "You haven't kill a poring"; close; OnNPCKillEvent: if (killedrid == PORING) query_sql "INSERT INTO `poring` VALUES ("+ getcharid(CHAR_ID_CHAR) +", '"+ escape_sql(strcharinfo(PC_NAME)) +"', 1) ON DUPLICATE KEY UPDATE `name` = '"+ escape_sql(strcharinfo(PC_NAME)) +"', `kills` = `kills` +1"; end; } I want to send the weekly reward to players by mail, here's my take on how to tackle this problem prontera,155,185,5 script Poring Rank 1_F_MARIA,{ .@nb = query_sql("SELECT `name`, RANK() OVER(ORDER BY `kills` DESC), `kills` FROM `poring` ORDER BY `kills` DESC LIMIT 5", .@name$, .@rank, .@kills); if (!.@nb) mes "no ranking yet"; for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; next; if (query_sql("SELECT RANK() OVER(ORDER BY `kills` DESC), `kills` FROM `poring` WHERE `char_id` = "+ getcharid(CHAR_ID_CHAR), .@rank, .@kills)) { mes "Your kills -> "+ .@kills; mes "Your Rank -> No."+ .@rank; } else mes "You haven't kill a poring"; close; OnNPCKillEvent: if (killedrid == PORING) query_sql "INSERT INTO `poring` VALUES ("+ getcharid(CHAR_ID_CHAR) +", '"+ escape_sql(strcharinfo(PC_NAME)) +"', 1) ON DUPLICATE KEY UPDATE `name` = '"+ escape_sql(strcharinfo(PC_NAME)) +"', `kills` = `kills` +1"; end; OnInit: if (!$poring_rank_1st_sunday) { query_sql "SELECT REPLACE(SUBDATE(CURDATE(), INTERVAL (DAYOFWEEK(CURDATE()) -1) DAY), '-', '')", $poring_rank_1st_sunday; // TRICK !! how to save the 1st sunday once the script installed end; } OnSun0000: query_sql "SELECT DATEDIFF(CURDATE(), '"+ $poring_rank_1st_sunday +"')/7", .this_week_index; if (.this_week_index == $poring_rank_week_index) end; .@nb = query_sql("SELECT `char_id` FROM `poring` ORDER BY `kills` DESC LIMIT 5", .@cid); if (.@nb) { for (.@i = 0; .@i < .@nb; ++.@i) rodex_sendmail .@cid[.@i], "[Poring Rank]", "Poring Rank Weekly Reward", "Thanks for killing porings ~ You are Rank No."+(.@i +1)+" on the ladder. Please accept this reward.", 0, Jellopy, 1; query_sql "TRUNCATE `poring`"; } $poring_rank_week_index = .this_week_index; end; }yup, basically I just added a few more line down there

now, this is what happen when the script first loaded

1. when the server load the script for the first time, it will execute OnInit: and try to save the date of Sunday on that week

• CURDATE() means today is '20201014'
• DAYOFWEEK(CURDATE()) means today is Wednesday(4) -> Sunday is (1)
•  that's why needs a -1 , DAYOFWEEK(CURDATE())-1 means -3
• SUBDATE(CURDATE(), INTERVAL (DAYOFWEEK(CURDATE()) -1) DAY)
• SUBDATE('20201014', INTERVAL (3) DAY)
• return '2020-10-11' is the Sunday of that week
•  since it return '2020-10-11', have to kill the '-' symbol, use REPLACE syntax to remove that symbol
  

this value '20201011' is save into $poring_rank_1st_sunday and remain unchange forever
and the script will always try to compare to this value in DATEDIFF

now, this is what happen on the next week when triggering OnSun0000: label
"SELECT DATEDIFF(CURDATE(), '"+ $poring_rank_1st_sunday +"')/7"

• CURDATE() is '20201018', the Sunday on the next week
• DATEDIFF('20201018', '20201011') return 7, since we need to compare in weeks, do a /7, 7/7 = 1
• .this_week_index = 1, $poring_rank_week_index = 0, the value are different now, thus it will try to reset the ladder

and once the ladder is reset, set $poring_rank_week_index(0) into .this_week_index(1) so it won't trigger on OnInit: label again
even doing a @reloadscript, it will compare the week index as 1 == 1, and won't execute ladder reset
only the next week again, compare the week index as 2 == 1, only will execute ladder reset

unlike the monthly reset, which save the actual date,
this one has to increase the week index counter even if there are no ranking in that week

External Links:
Weekly MVP Rewards

See Also:
How to send Monthly Rewards to players

How to send Monthly Reward to players

now I have this script CREATE TABLE `poring` ( `char_id` INT PRIMARY KEY, `name` VARCHAR(24), `kills` INT, KEY(`kills`) ) ENGINE = InnoDB; prontera,155,185,5 script Poring Rank 1_F_MARIA,{ .@nb = query_sql("SELECT `name`, RANK() OVER(ORDER BY `kills` DESC), `kills` FROM `poring` ORDER BY `kills` DESC LIMIT 5", .@name$, .@rank, .@kills); if (!.@nb) mes "no ranking yet"; for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; next; if (query_sql("SELECT RANK() OVER(ORDER BY `kills` DESC), `kills` FROM `poring` WHERE `char_id` = "+ getcharid(CHAR_ID_CHAR), .@rank, .@kills)) { mes "Your kills -> "+ .@kills; mes "Your Rank -> No."+ .@rank; } else mes "You haven't kill a poring"; close; OnNPCKillEvent: if (killedrid == PORING) query_sql "INSERT INTO `poring` VALUES ("+ getcharid(CHAR_ID_CHAR) +", '"+ escape_sql(strcharinfo(PC_NAME)) +"', 1) ON DUPLICATE KEY UPDATE `name` = '"+ escape_sql(strcharinfo(PC_NAME)) +"', `kills` = `kills` +1"; end; } I want to send the monthly reward to players by mail, here's my take on how to tackle this problem prontera,155,185,5 script Poring Rank 1_F_MARIA,{ .@nb = query_sql("SELECT `name`, RANK() OVER(ORDER BY `kills` DESC), `kills` FROM `poring` ORDER BY `kills` DESC LIMIT 5", .@name$, .@rank, .@kills); if (!.@nb) mes "no ranking yet"; for ( .@i = 0; .@i < .@nb; ++.@i ) mes "No."+ .@rank[.@i] +" ["+ .@name$[.@i] +"] ~ "+ .@kills[.@i] +" kills"; next; if (query_sql("SELECT RANK() OVER(ORDER BY `kills` DESC), `kills` FROM `poring` WHERE `char_id` = "+ getcharid(CHAR_ID_CHAR), .@rank, .@kills)) { mes "Your kills -> "+ .@kills; mes "Your Rank -> No."+ .@rank; } else mes "You haven't kill a poring"; close; OnNPCKillEvent: if (killedrid == PORING) query_sql "INSERT INTO `poring` VALUES ("+ getcharid(CHAR_ID_CHAR) +", '"+ escape_sql(strcharinfo(PC_NAME)) +"', 1) ON DUPLICATE KEY UPDATE `name` = '"+ escape_sql(strcharinfo(PC_NAME)) +"', `kills` = `kills` +1"; end; OnInit: if (!$poringkill_last_given) { $poringkill_last_given = atoi(gettime(GETTIME_YEAR) +""+ gettime(GETTIME_MONTH)); end; } else if ($poringkill_last_given != atoi(gettime(GETTIME_YEAR) +""+ gettime(GETTIME_MONTH))) goto L_GiveMonthlyReward; end; OnClock0000: if (gettime(GETTIME_DAYOFMONTH) != 1) end; L_GiveMonthlyReward: $poringkill_last_given = atoi(gettime(GETTIME_YEAR) +""+ gettime(GETTIME_MONTH)); .@nb = query_sql("SELECT `char_id` FROM `poring` ORDER BY `kills` DESC LIMIT 5", .@cid); if (!.@nb) end; for (.@i = 0; .@i < .@nb; ++.@i) rodex_sendmail .@cid[.@i], "[Poring Rank]", "Poring Rank Monthly Reward", "Thanks for killing porings ~ You are Rank No."+(.@i +1)+" on the ladder. Please accept this reward.", 0, Jellopy, 1; query_sql "TRUNCATE `poring`"; end; }
yup, basically I just added a few more line down there

now break it into 3 parts

1. when the server first load the script, it will execute OnInit: and store the $poringkill_last_given variable
   it will store the value as '202010', 2020 is the year, and 10 is the current month
2.  then on 12:00am, it will execute OnClock0000: label,
   with the if (gettime(GETTIME_DAYOFMONTH) != 1) end; to halt the script if it isn't the day 1 of the month
   since it is a new month, '202011' is different from '202010' and thus execute the condition below
3. The second condition of OnInit: label is to make sure it will run L_GiveMonthlyReward: label IF the server isn't online during 12:00am of day 1 of the month.
   It will compare to the $poringkill_last_given variable every time, if it is different month, then it will execute the statement below
   it has to bypass the if (gettime(GETTIME_DAYOFMONTH) != 1) end; so even if the server somehow didn't even online on day 1 of the month, it will still giving out monthly reward for previous month

External Link:
Monthly MVP Rank

See Also:
How to send Weekly Rewards to players

compare regular expression on rAthena & Hercules

The most famous script that uses Regular Expression is GmOcean's Disguise Event
to learn more about Regular Expression, refer to Wikipedia
Visit this site for more in-depth tutorial

This NPC will Copy whatever you type prontera,150,185,5 script dasdasd 1_F_MARIA,{ input .@a$; // if (.@a$ ~= "^(\\w{1,70})$") // unfortunately this line doesn't return true on special characters if (.@a$ ~= "^(.*)$") npctalk $@regexmatch$[1]; end; L_0: npctalk $@p2$; consolemes CONSOLEMES_DEBUG, $@p2$; end; OnInit: defpattern 1, "([^:]+): (.*)$","L_0"; activatepset 1; end; }
It's common practice that you should include ^ at the beginning and $ at the end
without it, players can input whitespace to bypass the check

Example:
(.@a$ ~= "asdf") = it can be "asdf", "aaasdf", "asdfffff", " asdf   " and so on
(.@a$ ~= "^asdf") = it can be "asdf", "asdffffff", "asdfasdf", "asdf   " <-- this just check the begining string
(.@a$ ~= "^asdf$") = it can only be "asdf", no other match

defpattern 1, "([^:]+): asdf","L_0"; <-- it can be "asdf", "asdfffffff", "asdfasdf", "asdf    " and so on
defpattern 1, "([^:]+): asdf$","L_0"; <-- only "asdf" will trigger the label L_0, OR "ASDF", "AsDf" and so on ...

yup, *pcre_match is case-sensitive, but *defpattern isn't, remember to keep this in mind
to make *pcre_match case-insensitive, use (?i) flag
to make *defpattern case-sensitive, use (?-i) flag
prontera,150,185,5 script dasdasd 1_F_MARIA,{ input .@a$; if (.@a$ ~= "^(?i)(asdf)$") // becomes non-case-sensitive npctalk $@regexmatch$[1]; end; L_0: npctalk $@p2$; consolemes CONSOLEMES_DEBUG, $@p2$; end; OnInit: defpattern 1, "([^:]+): (?-i)(asdf)$","L_0"; // only "asdf" will trigger me now activatepset 1; end; }

in practice, this command is most useful to check invalid character input
- only allow 0-9, a-z, A-Z if (.@tmp$ ~! "^[0-9A-Za-z]+$") mes "Invalid Code"; or follow a format, like MySQL datetime format if (.@tmp$ ~= "^(\\d{4,4})-(\\d{2,2})-(\\d{2,2}) (\\d{2,2}):(\\d{2,2}):(\\d{2,2})$")) { mesf "Year: %s", $@regexmatch$[1]; mesf "Month: %s", $@regexmatch$[2]; mesf "Day: %s", $@regexmatch$[3]; mesf "Hour: %s", $@regexmatch$[4]; mesf "Minute: %s", $@regexmatch$[5]; mesf "Second: %s", $@regexmatch$[6]; } else mes "wrong format";

for rAthena counterpart, rAthena has *preg_match instead, which follow PHP style input if (!preg_match("^[0-9A-Za-z]+$", .@tmp$)) mes "Invalid Code";
However unlike Hercules, there are no way to capture the strings within a bracket
rAthena's *preg_match can only returns 0 or 1
correct me if I'm wrong, the source code seems to indicate it can return 0~10 but I couldn't get it display 2 or above
dispbottom ""+ preg_match("asdf", "asdf"); // return 1 dispbottom ""+ preg_match("asdf", "asdfasdf"); // return 1 dispbottom ""+ preg_match("asdf", "asdfasdf", 4); // return 1 dispbottom ""+ preg_match("asdf", "asdfasdf", 5); // return 0


Some older client that diff with multi-language support will return |00 in front of character speech
try check it with *consolemes with the very first script (rAthena is *debugmes)
if that happens, your syntax has to change into
defpattern 1, "([^:]+): (\\|\\d{2})?(.*)$","L_0"; activatepset 1;

External Links:
Disguise Event fix
defpattern regexp
ignore case sensitive in regexp

*input with optional min/max parameter

I still see a lot people do this
prontera,150,180,5 script kjdfkfsdh 1_F_MARIA,{ mes "how many "+ F_MesItemInfo(Poring_Coin) +" you want to bet?"; mes "Maximum : 100"; next; input .@tmp; if (!.@tmp) close; if (.@tmp > 100) { mes "You have input more than maximum amount"; close; } if (countitem(Poring_Coin) < .@tmp) { mes "You don't have enough "+ F_MesItemInfo(Poring_Coin); close; } mes "Are you sure you want to bet "+ .@tmp +"x "+ F_MesItemInfo(Poring_Coin) +"?"; next; if (select("Yes", "No") == 2) close; delitem Poring_Coin, .@tmp; close; }
although this isn't bad by any means, but we can hijack it with *input <var>,<min>,<max>;

prontera,150,180,5 script kjdfkfsdh 1_F_MARIA,{ mes "how many "+ F_MesItemInfo(Poring_Coin) +" you want to bet?"; mes "Maximum : 100"; next; if (input(.@tmp, 1, min(100, countitem(Poring_Coin))) < 0) close; mes "Are you sure you want to bet "+ .@tmp +"x "+ F_MesItemInfo(Poring_Coin) +"?"; next; if (select("Yes", "No") == 2) close; delitem Poring_Coin, .@tmp; close; }
yup, just 1 line, do all 3 conditions above

No. 1 - *input script command can return -1,0,1 these 3 values
if the input amount is smaller than <min>, then it will return with the condition -1
since we don't want to close; the script on condition 0 or 1, then put the <min> as 1,
when player input as 0, it will return as condition -1

No. 2 - The <max> can insert with the condition countitem(Poring_Coin), or 100, depends on the script
Or ... just do both by having a *min script command <-- pick the lowest value in the group

DO NOT UNDERESTIMATE THIS TRICK
This is extremely useful if there is a complex +/- calculation involve
Example this custom bank script
Player only needs to input 9999999999 and it will automatically convert into accepted value without having to pull out a calculator

External Links:
Zeny in separate table