Promotional Codes 1.0.2

//===== Custom Script ===========================================================
//= Promotional Codes
//===== By: =====================================================================
//= Arzzzae
//===== Current Version: ========================================================
//= 1.0.2
//===== 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.
//= 1.0.1 - Only 1 SQL Table with confirmed and close Code
//= 1.0.2 - Code can be used only from one Player or every Account on time.
//===============================================================================

/*
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;
*/

-   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;
                    }
//-> Check if code confirmed to 1
                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;
                    }
//-> v1.0.2 Code can use more than 1
                else if (#pcode$ == .@pc$) {dispbottom "You have used this Code already."; 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 `confirmed` = `confirmed` + 1 WHERE `code` = " + .@pc$);
                    set #pcode$,.@pc$;
                    end;
                    }
//-----------------------------------
                else {
                    getitem .@item,.@amt;
                    dispbottom "Promotional code successfuly redeemed.";
//-> INSERT INTO change to UPDATE / WHERE Code set confirmed to 1
                    Query_SQL( "UPDATE `reward_codes` SET `redeem_time`=NOW(),`account_id`='"+getcharid(3)+"',`confirmed`='1' WHERE `code` = " + .@pc$ );
                    end;
                    }

OnEditCode:

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

    ncode:

        set .@clength,4; //Promotional Code Length


        input .@pcode$;
            if (getstrlen(.@pcode$)!=.@clength) {
                dispbottom "Codes must be 4 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;
//-> both entry in 1 table
            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')");

//-> v1.0.2 - Code can use more than 1 (set confirmed 2)
            mes "Code for more than 1 use?";
            if(select("Yes:No")==2) close;
            Query_SQL( "UPDATE `reward_codes` SET `confirmed`='2' WHERE `code` = " + .@pcode$ );
//-------------------------------------
            dispbottom "Promotional Code ^FF0000"+.@pcode$+" ^000000is now activated and the reward is ^FF0000"+.@amount+" "+.@iname$+".";
            close;

    vcode:
//-> show confirmed yes/no
        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;
}