// - SQL TABLE
/*
CREATE TABLE IF NOT EXISTS `callfrnd` (
  `id` int(11) NOT NULL auto_increment,
  `account_id` int(11) NOT NULL default '0',
  `name` varchar(23) NOT NULL default '',
  `last_ip` varchar(100) NOT NULL default '',
   PRIMARY KEY (`account_id`),
  KEY (`id`)
) ENGINE=MyISAM;
*/
 
 
 
 
prontera,160,180,5  script  Freebies NPC    100,{
        
    mes $@name$;
    mes "^616D7EI am one of the staff members of GM Team. And i am assigned to give freebies to players in our server.";
    mes " ";
    next;
    mes $@name$;
    mes "^616D7ESo, How may I help you?";
    next;
    switch(select("- Freebies please:- Close")) {
        mes $@name$;
        case 1:
            if ( getcharid(3) < $@new_aid ) {
                mes "^616D7EYou doesn't seems to be new to server.";
                close;
            }
            
            mes "^616D7EOh.. Nice, so would you like to proceed with the Referral System?";
            switch(select("Yes")) {
                case 1:
                    if (#CallFrnd >= 1) {
                        mes "^616D7EYou are already Registered on our database.";
                        close;
                    }
                    goto ReferralSystem;
                }
        
        case 2:
            mes "^616D7ok, nvm!";
            close;
    }
    end;
    
ReferralSystem:
    next;
    mes $@name$;
    mes " ";
    mes "^ff0000NOTE: ^000000^616D7EEnter your Character Name to claim your Freebies.";
    mes " ";
    mes "^616D7EEnter your Character Name: ";
    next;
    input .@frnd$;
    mes $@name$;
    mes "^616D7ESo your Character Name is [ ^ff000f" + .@frnd$ + "^000000 ]? ^616D7EIs that right??";
    next;
    if(select("Yes:Back")==2) goto ReferralSystem;
        mes $@name$;
        mes "^616D7EIf you play more than [ ^2B60DE" + $@daystoplay + " days ^000000] and spend more than x hours to our server every day. Your friend will get a New Item from the list.";
        next;
        switch(select("Exit:Complete the process")) {
            case 1:
                mes $@name$;
                mes "^616D7ENevermind~, You can come back again!";
                close;
            case 2:
                query_sql("SELECT last_ip FROM `login` WHERE account_id = "+getcharid(3)+"", .@lastip$);    // recruits ip address
                set .@frnd, query_sql ("SELECT name, char.account_id, last_ip FROM `char` LEFT JOIN `login` ON login.account_id = char.account_id where name = '"+escape_sql(.@frnd$)+"'",.@chname$, .@account_id, .@flastip$); // recruiter ip address and information
                
                    if (!.@frnd) {
                        mes $@name$;
                        mes "^ff0000FAILED! FAILED! FAILED!";
                        mes " ";
                        mes "^616D7ESorry, We didn't found your Character Name in our database. Make sure that you Enter's Everything correctly.";
                        close;
                    }
                    if ( .@account_id < getcharid(3) ) {
                        mes $@name$;
                        mes "^ff0000WARNING: ^616D7EThis Player is found on your account. Sorry, Unable to proceed further!!!";
                        mes " ";
                        mes "Enter some other name";
                        close;
                    }
                    if ( .@account_id > getcharid(3) ) {    // i am older than my frnd
                        mes $@name$;
                        mes "^616D7EIt seems, your not newbie to our server. Damn you cheaters. ";
                        close;
                    }
                query_sql("SELECT count(last_ip) FROM `callfrnd` WHERE last_ip='"+.@lastip$+"'", .@countip);    // retrieve registered ip
                    if (.@countip){
                        mes "This ip was already used";
                        mes "You can't have new freebies, see ya";
                        close;
                    }
                    
                set #CallFrnd, 1;
                query_sql("INSERT INTO `callfrnd`(account_id,name,last_ip) VALUES ("+getcharid(3)+",'"+ escape_sql(strcharinfo(0))+"','"+.@lastip$+"')"); //record new ip
                mes $@name$;
                mes "^616D7EYour registration is now accepted.";
                mes "^ff0000Now, you should relog to make changes save and start properly.^000000";
                mes " ";
                mes "and ^0000ff~ We Welcome's you to our server.";
                close;
        }
}