MVP Ranking with Rewards [Help Please]

[NyxLucid]

Hello guys. Please help me on this script.. I want to claim the reward by the same NPC by having an option of "Claim Rewards" after a week.. instead of inserting the rewards thru mail.. because in the server we turn-off the mailing system...thank you in advance..

/*
alter table `char` add column mvp_rank int(11) default 0 after delete_date, add index (mvp_rank);

create table mvp_rank_archive (
`date` datetime,
pos tinyint(4) unsigned,
cid int(11) unsigned default 0,
name varchar(23) default '',
points int(11) unsigned not null,
primary key ( `date`, pos )
) engine innodb;
*/
prontera,158,183,3    script    MVP Ladder    100,{
    mes "[MVP Ladder]";
    mes "Hello!";
    mes "What are you doing here?";
    next;
    switch(select("Check rankings.:My stats.:Previous Week ranking:Claim Rewards")) {
    case 1:
        mes "[MVP Ladder]";
        mes "Rankings :->";
        .@nb = query_sql( "select name, mvp_rank from `char` where mvp_rank > 0 order by mvp_rank desc limit 10", .@name$, .@count );
        if ( !.@nb ) {
            mes "no entry";
            close;
        }
        for ( .@i = 0; .@i < .@nb; .@i++ )
            mes ( .@i +1 )+". "+ .@name$[.@i] +" -> "+ .@count[.@i] +" points";
        close;
    case 2:
        query_sql "select mvp_rank from `char` where char_id = "+ getcharid(0), .@killed;
        mes "[MVP Ladder]";
        mes "You have killed "+ .@killed +" MVPs";
        close;
    case 3:
        mes "[MVP Ladder]";
        mes "Last week ranking";
        if ( gettime(6) > 10 )
            .@store_date$ = gettime(7) +"-"+( gettime(6) -1 )+"-00 00:00:00";
        else if ( gettime(6) > 1 )
            .@store_date$ = gettime(7) +"-0"+( gettime(6) -1 )+"-00 00:00:00";
        else
            .@store_date$ = ( gettime(7) -1 ) +"-12-00 00:00:00";
        .@nb = query_sql( "select name, points from mvp_rank_archive where `date` = '"+ .@store_date$ +"' order by pos", .@name$, .@count );
        if ( !.@nb ) {
            mes "no entry";
            close;
        }
        for ( .@i = 0; .@i < .@nb; .@i++ )
            mes ( .@i +1 )+". "+ .@name$[.@i] +" -> "+ .@count[.@i] +" points";
        close;
    }
    close;
OnInit:
    if ( $mvp_ladder_last_given == atoi( gettime(7) +""+ gettime(6) ) ) end;
    goto L_give;
OnClock0000:
    if ( gettime(4) != 1 ) end;
L_give:
    .@nb = query_sql( "select char_id, name, mvp_rank from `char` where mvp_rank > 0 order by mvp_rank desc limit 10", .@cid, .@name$, .@killed );
    if ( !.@nb ) end;
    setarray .@reward, 501, 502, 503, 504, 505, 506, 507, 508, 509, 510;
    setarray .@amount, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1;
    for ( .@i = 0; .@i < .@nb; .@i++ )
        query_sql "insert into mail ( send_name, dest_id, title, message, nameid, amount, identify, zeny, time ) values ( '"+ escape_sql( .@name$[.@i] ) +"', "+ .@cid[.@i] +", '[MVP RANK]', 'Congratulations for earning No. "+( .@i +1 )+" position in MVP rank. Here is your reward.', "+ .@reward[.@i] +", "+ .@amount[.@i] +", 1, 0, unix_timestamp( now() ) )";
    if ( gettime(6) > 10 )
        .@store_date$ = gettime(7) +"-"+( gettime(6) -1 )+"-00 00:00:00";
    else if ( gettime(6) > 1 )
        .@store_date$ = gettime(7) +"-0"+( gettime(6) -1 )+"-00 00:00:00";
    else
        .@store_date$ = ( gettime(7) -1 ) +"-12-00 00:00:00";
    for ( .@i = 0; .@i < .@nb; .@i++ )
        .@store$[.@i] = "( '"+ .@store_date$ +"', "+( .@i +1 )+", "+ .@cid[.@i] +", '"+ escape_sql( .@name$[.@i] ) +"', "+ .@killed[.@i] +" )";
    query_sql "insert into mvp_rank_archive values "+ implode( .@store$, "," );
    query_sql "update `char` set mvp_rank = 0"; // reset the ladder
    $mvp_ladder_last_given = atoi( gettime(7) +""+ gettime(6) );
    end;
OnNPCKillEvent:
    if ( getmonsterinfo( killedrid, MOB_MVPEXP ) ) {
        query_sql "update `char` set mvp_rank = mvp_rank +1 where char_id = "+ getcharid(0);
        query_sql "select mvp_rank from `char` where char_id = "+ getcharid(0), .@killed;
        dispbottom "~ You've killed "+ .@killed +" MVP. ~";
        specialeffect2 EF_HEAL2;
    }
    end;
}

 

Edited April 5, 2018 by Emistry
Please use codebox.

[AnnieRuru]

found a bug,
http://upaste.me/2d7a496551c99fca3
- fix if the previous week has 2 or more players share the same ranking in the top10, this script will throw error when saving into `mvp_rank_archive` because of the primary key = unique key

drop the `mvp_rank_archive` table and rebuilt it

drop table mvp_rank_archive;

 

@hendra814
actually nope, the true fact is I forgotten about the mail system when I saw the claim reward in the npc menu XD
some server actually prefer to keep their database small (periodically clean their database)
so the rodex mail system 14 days auto-deletion is something they love

Edited April 24, 2018 by AnnieRuru

[NyxLucid]

Master @Annie Ruru

Master @Emistry

Please help.. thank you

[AnnieRuru]

I remember I made this script

http://herc.ws/board/topic/4552-mvp-ladder-script-enhancement/

 

curious ... why not just use the new rodex mail ? that also save my time .... very simple to convert into rodex_mail script command

EDIT: oops ~ only hercules has *rodex_mail, rathena doesn't have this script command

 

also this script is to be use monthly ... if wanna change into weekly it needs lots of tweaking ...

 

a claim reward option so player can claim the prize whenever they want ??

that needs another SQL table ...

 

conclusion ... there are 3 heavy modification that you are asking ...

Edited April 5, 2018 by AnnieRuru

[NyxLucid]

51 minutes ago, AnnieRuru said:

I remember I made this script

http://herc.ws/board/topic/4552-mvp-ladder-script-enhancement/

 

curious ... why not just use the new rodex mail ? that also save my time .... very simple to convert into rodex_mail script command

EDIT: oops ~ only hercules has *rodex_mail, rathena doesn't have this script command

 

also this script is to be use monthly ... if wanna change into weekly it needs lots of tweaking ...

 

a claim reward option so player can claim the prize whenever they want ??

that needs another SQL table ...

 

conclusion ... there are 3 heavy modification that you are asking ...

@Annie Ruru thank you for your reponse and I apologize for asking too much..

I am new on scripting..

btw thank you again

master @Annie Ruru

and I am willing to wait even its take a long period of time.. thank you master..

Edited April 5, 2018 by hanxgo

[AnnieRuru]

if your server enable rodex mail system then I can reconsider this request ...

 

yes, I mean write *rodex_mail script command to use in rathena ... very simple to do since hercules already having it

 

 

EDIT: yeah .... everyone mention @Annie Ruru ....

I actually didn't get any notification ...

Edited April 5, 2018 by AnnieRuru

[NyxLucid]

On 4/5/2018 at 10:17 PM, AnnieRuru said:

if your server enable rodex mail system then I can reconsider this request ...

 

yes, I mean write *rodex_mail script command to use in rathena ... very simple to do since hercules already having it

 

 

EDIT: yeah .... everyone mention @Annie Ruru ....

I actually didn't get any notification ...

Hello Master @AnnieRuru thank you..

can you please make it with rodex, and I will enable the rodex in the server.. thank you in advance...

rodex is this one right>? 

thank you so much master @AnnieRuru

 

 

 

 

[AnnieRuru]

question 1st

how come yours is different than mine ?

I'm using KRO 20170705 and 2017-05-17aRagexeRE.exe hexed client

https://drive.google.com/file/d/1kgcT0TBuLDhsZjQyBjm208QEJIjw3fJM/view?usp=sharing

start to wonder issit my client outdated or yours ...

 

anyways, yup, now I get the motivation to write this ~ stay tune ...

[NyxLucid]

1 hour ago, AnnieRuru said:

question 1st

how come yours is different than mine ?

I'm using KRO 20170705 and 2017-05-17aRagexeRE.exe hexed client

https://drive.google.com/file/d/1kgcT0TBuLDhsZjQyBjm208QEJIjw3fJM/view?usp=sharing

start to wonder issit my client outdated or yours ...

 

anyways, yup, now I get the motivation to write this ~ stay tune ...

Thank you in advance Master @AnnieRuru

[NyxLucid]

@AnnieRuru master any updates? thank you.

[AnnieRuru]

the script might look short, but damn lots of tricks are compact inside
http://upaste.me/2d7a496551c99fca3
especially how to tackle the week .... without having go into next year

@hanxgo

EDIT: oh yeah I forgotten about the mail ... HAHAHA ... there is no need using mail in this script, so you can disable it again

Edited April 24, 2018 by AnnieRuru

[hendra814]

44 minutes ago, AnnieRuru said:

the script might look short, but damn lots of tricks are compact inside
http://upaste.me/2d7a496551c99fca3
especially how to tackle the week .... without having go into next year

@hanxgo

EDIT: oh yeah I forgotten about the mail ... HAHAHA ... there is no need using mail in this script, so you can disable it again

So this script will give reward every week to the top player.

But the player must talk to the npc to get the reward right?

[AnnieRuru]

18 minutes ago, hendra814 said:

So this script will give reward every week to the top player.

But the player must talk to the npc to get the reward right?

yes and yes

don't worry, unlike the rodex mail will auto-delete in 14 days, this script using a table to store the reward infinitely ... no time limit
can store infinite rewards too ... I have tested by change my computer date... from this month is April ...  keep debugging until July LOL

[hendra814]

7 minutes ago, AnnieRuru said:

yes and yes

don't worry, unlike the rodex mail will auto-delete in 14 days, this script using a table to store the reward infinitely ... no time limit
can store infinite rewards too ... I have tested by change my computer date... from this month is April ...  keep debugging until July LOL

wow, this is a great script better than using MVP reward with RODEX system.

Thanks @AnnieRuru for this script.

Edited April 24, 2018 by hendra814

[hendra814]

15 hours ago, AnnieRuru said:

found a bug,
http://upaste.me/2d7a496551c99fca3
- fix if the previous week has 2 or more players share the same ranking in the top10, this script will throw error when saving into `mvp_rank_archive` because of the primary key = unique key

drop the `mvp_rank_archive` table and rebuilt it

drop table mvp_rank_archive;

 

@hendra814
actually nope, the true fact is I forgotten about the mail system when I saw the claim reward in the npc menu XD
some server actually prefer to keep their database small (periodically clean their database)
so the rodex mail system 14 days auto-deletion is something they love

so, for the small vps memory it's suppose using rodex system

btw thanks for the new script

[NyxLucid]

23 hours ago, AnnieRuru said:

the script might look short, but damn lots of tricks are compact inside
http://upaste.me/2d7a496551c99fca3
especially how to tackle the week .... without having go into next year

@hanxgo

EDIT: oh yeah I forgotten about the mail ... HAHAHA ... there is no need using mail in this script, so you can disable it again

Thank you Master @AnnieRuru
and another noob question, because I am trying to test the script and I just edit this line to reset the MVP ranking and gives a rewards,

OnWed1430:
    query_sql "select datediff( curdate(), '"+ $mvp_rank_1st_wednesday +"' ) /7", .this_week_index;
    if ( .this_week_index == $mvp_rank_week_index ) end;
 

thank you master

[AnnieRuru]

ok ... now I understand what you mean

this script ... has use some ... tricks ... on it ....
so you can't actually test the script by changing that label,
that OnSun0000: has to be like that because it runs the calculation based on SQL command DATEDIFF

the moment you install the script, it automatically save a the current Sunday of the current week, and it save as weekindex 0
if you test the script that way, it will save the weekindex as -1, which will bug the script

the proper way, like I said previously, is to change your test server's computer time into (future) current week Saturday 11:58pm and wait for it to run that label
and remember time only move forward, it can't roll backwards, so if you change your computer time into the future, and change back original time, this script will also bug XD

that's why I say this script has so many tricks inside
and actually I don't even know the proper way to do this ... I've search on the internet and can't really find a perfect solution

[NyxLucid]

10 minutes ago, AnnieRuru said:

ok ... now I understand what you mean

this script ... has use some ... tricks ... on it ....
so you can't actually test the script by changing that label,
that OnSun0000: has to be like that because it runs the calculation based on SQL command DATEDIFF

the moment you install the script, it automatically save a the current Sunday of the current week, and it save as weekindex 0
if you test the script that way, it will save the weekindex as -1, which will bug the script

the proper way, like I said previously, is to change your test server's computer time into (future) current week Saturday 11:58pm and wait for it to run that label
and remember time only move forward, it can't roll backwards, so if you change your computer time into the future, and change back original time, this script will also bug XD

that's why I say this script has so many tricks inside
and actually I don't even know the proper way to do this ... I've search on the internet and can't really find a perfect solution

Thank you master @AnnieRuru
and more power

[Ruhn]

@AnnieRuru hi . is there a way for your script to disable it on other maps ?.  something like  the poitns is only acquired when mvp is killed in its place ? or something like i can put maps where the poitns can only be earned ? thank you

[luizinhomt]

could someone help me to put hat effect in the first 1 of the rank? could also change the reset to be monthly instead of weekly, ? thank you

Edited October 9, 2022 by luizinhomt
adicionar informaçoes