Promotional Codes 2.2

//===== Custom Script ===========================================================
//= Promotional Codes
//===== By: =====================================================================
//= Arzzzae
//===== Current Version: ========================================================
//= 1.0 Arzzzae
//= 2.2 by Innos for www.ex-ro.de
//===== Compatible With: ========================================================
//= Latest svn
//===== Description: ============================================================
//= Adds 2 custom atcommands. @claim for players to claim
//= promotional code created by Game Masters. @code is for
//= administrators to add/view/delete promotional codes.
//===== Additional Comments: ====================================================
//= 1.0 - Initial Release.
//= 2.0 - SQL Table with confirmed and close Code
//= 2.1 - Code can be used only from one Player or every Account once time.
//= 2.2 - Code can store Text from 4-10 length.
//===============================================================================
//SQL for DBmain
/*
DROP TABLE IF EXISTS `reward_codes`;
CREATE TABLE IF NOT EXISTS `reward_codes` (
  `code` VARCHAR(10) NOT NULL DEFAULT '',
  `nameid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `item_name` VARCHAR(45) NOT NULL DEFAULT '',
  `amount` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
  `time_created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `redeem_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `account_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `confirmed` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`code`)
) ENGINE=MYISAM;
*/
// SQL for DBlog
/*
DROP TABLE IF EXISTS `rewardlogs`;
CREATE TABLE `rewardlogs` (
`account_id` INT( 10 ) NOT NULL ,
`code` VARCHAR( 10 ) NOT NULL
) ENGINE = InnoDB;
*/
//===============================================================================

-   script  promoclaim  -1,{

OnInit:
    bindatcmd "claim",strnpcinfo(3)+"::OnClaim";
    bindatcmd "code",strnpcinfo(3)+"::OnEditCode",99,0;
    end;

OnClaim:
    mes "^FF0000 Enter a new promotional code.";
    input .@pc$;
    close2;

        if ( query_sql("SELECT nameid,amount FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'", .@item,.@amt) == 0) {
            dispbottom "Invalid promotional code.";
            end;
            }
            else if (query_sql("SELECT code,confirmed FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"' AND `confirmed`='1'")) {
            dispbottom "This promotional code already claimed.";
            end;
            }
            else if (query_logsql("SELECT account_id,code FROM `rewardlogs` WHERE `account_id`='"+getcharid(3)+"' AND `code`='"+escape_sql(.@pc$)+"'")) {
            dispbottom "You already claimed this promotional code.";
            end;
            }
            else if (query_sql("SELECT code,confirmed FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"' AND `confirmed` >= '2'")) {
            getitem .@item,.@amt;
            dispbottom "Promotional code successfuly redeemed.";
            query_sql("update `reward_codes` SET `redeem_time`=NOW(),`account_id`='"+getcharid(3)+"', `confirmed` = `confirmed` + 1 WHERE `code`='"+escape_sql(.@pc$)+"'");
            query_logsql("INSERT INTO `rewardlogs` (code,account_id) VALUES ('"+.@pc$+"', "+getcharid(3)+")");
            end;
            }
            else {
            getitem .@item,.@amt;
            dispbottom "Promotional code successfuly redeemed.";
            Query_SQL( "UPDATE `reward_codes` SET `redeem_time`=NOW(),`account_id`='"+getcharid(3)+"',`confirmed`='1' WHERE `code` = " +escape_sql(.@pc$) );
            query_logsql("INSERT INTO `rewardlogs` (code,account_id) VALUES ('"+.@pc$+"', "+getcharid(3)+")");
            end;
            }

OnEditCode:
    menu "Setup a new promotional code",ncode,"View existing codes",vcode,"Delete exisiting codes",dcode,"Cancel",cancel;

ncode:

    set .@clengthmin,4; //Promotional Code Length
    set .@clengthmax,10; //Promotional Code Length

        input .@pcode$;
            if (getstrlen(.@pcode$) < .@clengthmin) {
                dispbottom "Codes must be 4 characters long.";
                end;
                }
            if (getstrlen(.@pcode$) > .@clengthmax) {
                dispbottom "Codes can't more than 10 characters long.";
                end;
                }
            mes "You have entered ^FF0000"+.@pcode$+"^000000 as the code.";
            mes "Are you sure?";
            next;
            if(select("Yes:No, thanks")==2) close;
            query_sql ("SELECT COUNT(code) FROM `reward_codes` WHERE `code`='"+escape_sql(.@pcode$)+"'", .@pcode_exists);
            if (.@pcode_exists) {
                dispbottom "Sorry, that code already exist.";
                end;
            }
            mes "Please set a reward for the players who has entered the code.";
            input .@reward;
            next;
            if (.@reward == 0) {
                dispbottom "Sorry, that is an invalid item id.";
                close;
            }
            set .@iname$, getitemname(.@reward);
            mes "Item number ^FF0000"+.@reward+"^000000";
            mes "is equivalent to ^FF0000"+.@iname$+"^000000.";
            mes "Are you sure?";
            next;
            if(select("Yes:No, thanks")==2) close;
            mes "How many ^FF0000"+.@iname$+"^000000.?";
            input .@amount;
            next;
            if (.@amount == 0) {
                dispbottom "Sorry, that is an invalid amount number.";
                close;
            }

            mes "Are you sure?";
            next;
            if(select("Yes:No, thanks")==2) close;
            query_sql ("INSERT INTO `reward_codes` (code,nameid,item_name,amount,time_created,redeem_time,account_id,confirmed) VALUES ('"+.@pcode$+"', "+.@reward+", '"+.@iname$+"', "+.@amount+", NOW(), '0000-00-00 00:00:00', '0', '0')");
            next;
            mes "Code for more than 1 use?";
            if(select("Yes:No")==2) close;
            dispbottom "Promotional Code ^FF0000"+.@pcode$+" ^000000is now activated and the reward is ^FF0000"+.@amount+" "+.@iname$+".";
            Query_SQL( "UPDATE `reward_codes` SET `confirmed` = '2' WHERE `code`='"+escape_sql(.@pcode$)+"'");
            close;

vcode:

        set .@nb, query_sql("SELECT code, item_name, amount, confirmed FROM `reward_codes` ORDER BY time_created DESC LIMIT 20", .@code$, .@nid$, .@amount, .@conf);

            if (.@nb == 0) {
            dispbottom "No exisiting codes.";
            end;
            }
            dispbottom "=============================================";
            dispbottom "==============  EXISITING CODES  ==============";
            dispbottom " 0: Open     1:Closed     2+: Free for all    ";
            dispbottom "=============================================";
            for(set .@i,0; .@i < .@nb; set .@i,.@i+1)
            dispbottom ""+.@code$[.@i]+" (  Reward: "+.@amount[.@i]+" "+.@nid$[.@i]+" ) Used: "+.@conf[.@i]+" ";
            dispbottom "=============================================";
            end;

dcode:
        input .@dcode$;
            query_sql ("SELECT COUNT(code) FROM `reward_codes` WHERE `code`='"+escape_sql(.@dcode$)+"'", .@pcode_exists);
            if (.@pcode_exists) {
                mes "Would you like to delete that code?";
                next;
                if(select("Yes:No, thanks")==2) close;

                query_sql ("DELETE FROM `reward_codes` WHERE `code`='"+escape_sql(.@dcode$)+"'");
                dispbottom "Promotional code successfuly deleted.";
                close;
            }
            dispbottom "Promotional code not found.";
            end;

cancel:
        close;
}