//===== eAthena Script =======================================
//= Donation NPC
//===== By ===================================================
//= Josh
//===== Version ==============================================
//= 1.0 - First release. Probably contains bugs/security
//=	  risks
//= 1.1 - Added a check for whether the account exists when
//=	  adding a donator. Need to improve ordering when
//=	  viewing all donations.
//= 1.2 - Modified for public use. Added checkweight feature.
//= 2.0 - Many changes, especially ones I had always wanted
//=	  to add to this script. Includes reading items from
//=	  a separate SQL table and more database manipulation
//=	  options for GMs.
//= 2.1 - Made few changes including the add/remove items
//=	  feature.
//= 3.0 - All strings inputed by a user and user/char names
//=	  in SQL queries are now escaped. Each item has a
//=	  price rather than a quantity. This script can work
//=	  with decimals.
//= 3.1 - Added quotes to some queries, fixed a variable and
//=	  removed a comment.
//= 3.2 - Fixed a problem where eAthena would crash if a
//=	  query returned NULL.
//= 3.3 - Optimized query speeds by combining a few select 
//=	  queries into one. Requires Trunk 7975 OR
//=	  Stable 8637.
//===== Compatible With ======================================
//= eAthena - any version that contains the new query_sql
//=	      command (Stable 8637 OR Trunk 7975).
//===== Description ==========================================
//= A script that lets a player claim an item for donating.
//= Allows a GM to input each donation.
//===== Comments =============================================
//= This script uses SQL tables to store variables for the
//= amount donated by users and the items claimable.
//===== Installation =========================================
//= You must execute donate.sql and donate_item_db.sql before
//= using this script.
//============================================================
//= Thanks to Vich for helping me with the SQL syntax.
//= Thanks to Lance for helping me with the the arrays and 
//= for implementing query_sql.
//= Thanks to Skotlex for implementing escape_sql.
//= Thanks to Toms for implementing the new multi-column 
//= query_sql command.
//============================================================

prontera,145,179,5	script	Donation Girl	714,{

if (getgmlevel() >= 80) goto L_GM;

L_START:
mes "[Donation Girl]";
mes "Hello! I'm the Donation Girl!";
mes "If you have made a donation,";
mes "you are entitled to a reward!";
next;
menu "More info",-,"Make a claim",L_CHECK,"Statistics",L_STATS;
L_INFO:
mes "[Donation Girl]";
mes "Each month, a lot of money is paid to keep this server running.";
next;
mes "[Donation Girl]";
mes "You can support us by donating any amount of money.";
next;
mes "[Donation Girl]";
mes "To show our appreciation, we will gladly give you a reward.";
next;
menu "Continue",L_START,"Cancel",-;
close;

L_CHECK:
query_sql "SELECT `amount`,`claimed` FROM `donate` WHERE `account_id` = "+getcharid(3)+"", @amount$, @claimed$;
query_sql "SELECT MIN(price) FROM `donate_item_db`", @min$;
query_sql "SELECT '"+@amount$+"' - '"+@claimed$+"'", @value$;
query_sql "SELECT '"+@value$+"' >= '"+@min$+"'", @enough;
if(@enough) goto L_CLAIM;
mes "[Donation Girl]";
mes "Sorry, you do not have enough to make a claim.";
mes "If you have donated but have not made a claim,";
mes "Please give us time to process your donation.";
close;

L_CLAIM:
mes "[Donation Girl]";
mes "Thankyou for donating!";
mes "You have $"+@value$+" worth of credit!";
mes "Would you like to claim an item now?";
next;
menu "No",-,"Yes",L_YES;
mes "[Donation Girl]";
mes "Ok! You are able to collect your item(s) any time!";
close;

L_YES:
mes "[Donation Girl]";
mes "Very well. Which item would you like?";
next;
query_sql "SELECT `name` FROM `donate_item_db` ORDER BY `name` ASC",$@name$;
set $@menu$, $@name$[0];
	for(set $@i, 1; $@i < 127; set $@i, $@i + 1){
		set $@menu$, $@menu$ + ":" + $@name$[$@i];
	}

set @menu, (select($@menu$))-1;

query_sql "SELECT `id`,`price` FROM `donate_item_db` WHERE `name` = '"+$@name$[@menu]+"'", @id, @price$;
query_sql "SELECT TRUNCATE("+@value$+" / "+@price$+",0)", @max;

mes "[Donation Girl]";
mes ""+$@name$[@menu]+"s cost $"+@price$+" each.";
mes "How many "+$@name$[@menu]+"s would you like to claim?";
mes "Maximum: "+@max+".";
input @quantity;

if(@quantity>@max) {
	mes "[Donation Girl]";
	mes "Sorry, but you do not have enough to claim "+@quantity+" "+$@name$[@menu]+"s.";
	next;
	goto L_CLAIM;
	}

if(!@quantity) {
	mes "[Donation Girl]";
	mes "You can't have 0 as an amount!";
	next;
	goto L_CLAIM;
	}

if (checkweight(@id,@quantity) == 0) {
	mes "[Donation Girl]";
	mes "I'm sorry, but you cannot carry "+@quantity+" "+$@name$[@menu]+"s.";
	next;
	goto L_CLAIM;
	}

query_sql "SELECT "+@quantity+" * "+@price$+"", @total$;
mes "Are you sure you want to claim "+@quantity+" "+$@name$[@menu]+"s for $"+@total$+"?";
next;
menu "No",L_CLAIM,"Yes",-;
query_sql "UPDATE `donate` SET `claimed` = `claimed` + "+@total$+" WHERE `account_id` = '"+getcharid(3)+"'";
getitem @id,@quantity;
mes "[Donation Girl]";
mes "Thankyou for donating! We hope you enjoy your gift!";
close;

L_STATS:
mes "[Donation Girl]";
query_sql "SELECT IFNULL((SELECT SUM(amount) FROM `donate`),0)", @total$; 
mes "Our fund is at a total of $"+@total$+"";
next;
menu "More info",L_INFO,"Make a claim",L_CHECK,"Statistics",L_STATS;
close;

L_GM:
mes "[GM Menu]";
mes "Hello GM!";
mes "What would you like to do?";
next;
menu "Add/Remove Donation",L_GM2,"Add/Remove Items",L_ITEM,"Test Script",L_START;
close;

L_GM2:
menu "Add a donation",L_DONATE,"Remove a donation",L_REMOVE,"View all donations",L_VIEWALL;
close;

L_ITEM:
menu "Add an item",L_NEWITEM,"Remove an item",L_DELITEM,"View all items",L_ALLITEMS,"Return to main menu",L_GM;
close;


L_NEWITEM:
mes "[GM Menu]";
mes "Please enter the item name:";
input @itemname$;
query_sql "SELECT `id` FROM `item_db` WHERE `name_english` = '"+escape_sql(@itemname$)+"'", @iid;
query_sql "SELECT `id` FROM `donate_item_db` WHERE `name` = '"+escape_sql(@itemname$)+"'", @check;
if(@iid==0) goto L_INONE;
mes "[GM Menu]";
mes "Please enter the cost of each "+@itemname$+":";
input @cost$;
query_sql "SELECT "+escape_sql(@cost$)+" = 0", @invalid;
if(@invalid) goto L_ZERO;
query_sql "SELECT CAST('"+escape_sql(@cost$)+"' AS DECIMAL)", @cost$;
mes "[GM Menu]";
mes "You have specified that donators can claim "+@itemname$+"s for $"+@cost$+" each.";
mes "Would you like to continue?";
next;
menu "No",L_ITEM,"Yes",-;
if(@check!=0) goto L_REPLACE;
query_sql "INSERT INTO `donate_item_db` VALUES ('"+@iid+"', '"+escape_sql(@itemname$)+"', '"+@cost$+"')";
mes "[GM Menu]";
mes "Item added successfully!";
next;
menu "Add annother item",L_NEWITEM,"Remove an item",L_DELITEM,"View all items",L_ALLITEMS;
close;

L_REPLACE:
mes "[GM Menu]";
mes "Item "+@itemname$+" already exists in the database.";
mes "Would you like to replace it?";
next;
menu "No",L_ITEM,"Yes",-;
query_sql "REPLACE INTO `donate_item_db` VALUES ('"+@iid+"', '"+@itemname$+"', '"+@cost$+"')";
mes "[GM Menu]";
mes "Item replaced successfully!";
next;
menu "Add annother item",L_NEWITEM,"Remove an item",L_DELITEM,"View all items",L_ALLITEMS;
close;

L_INONE:
mes "[GM Menu]";
mes "Item "+@itemname$+" does not exist.";
next;
goto L_ITEM;

L_DELITEM:
mes "[GM Menu]";
mes "Please enter the item name:";
input @itemname$;
query_sql "SELECT `id` FROM `donate_item_db` WHERE `name` = '"+escape_sql(@itemname$)+"'", @iid;
if(@iid==0) goto L_INONE;
next;
mes "[GM Menu]";
mes "You have specified to delete "+@itemname$+" from the database.";
mes "Would you like to continue?";
next;
menu "No",L_ITEM,"Yes",-;
query_sql "DELETE FROM `donate_item_db` WHERE `id` = '"+@iid+"'";
mes "[GM Menu]";
mes "Item deleted successfully!";
next;
menu "Add an item",L_NEWITEM,"Remove another item",L_DELITEM,"View all items",L_ALLITEMS;
close;

L_ALLITEMS:
mes "[GM Menu]";
query_sql "SELECT `name`,`price` FROM `donate_item_db` ORDER BY `name` ASC", @items$, @itemamount$;
for(set @i, 0; @i < getarraysize(@items$); set @i, @i + 1){
		mes ""+@items$[@i]+" - $"+@itemamount$[@i]+"";
	}
next;
goto L_GM;

L_DONATE:
mes "[GM Menu]";
mes "Please enter the donator's username:";
input @donator$;
query_sql "SELECT `account_id` FROM `login` WHERE `userid` = '"+escape_sql(@donator$)+"'", @aid;
if(@aid==0) goto L_NONE;
query_sql "SELECT `amount` FROM `donate` WHERE `account_id` = "+@aid+"", @donated$;
query_sql "SELECT '"+@donated$+"' > 0", @donated;
switch(@donated) {
	case 0:
		mes ""+@donator$+" has not donated before.";
		break;
	case 1:
		mes ""+@donator$+" has donated $"+@donated$+".";
		break;
	}
next;
mes "[GM Menu]";
mes "Please enter the amount donated by "+@donator$+"";
input @donating$;
query_sql "SELECT "+escape_sql(@donating$)+" = 0", @invalid;
if(@invalid) goto L_ZERO;
query_sql "SELECT CAST('"+escape_sql(@donating$)+"' AS DECIMAL)", @donating$;
mes "[GM Menu]";
mes "You have specified that "+@donator$+" has donated $"+@donating$+".";
mes "Would you like to continue?";
next;
menu "No",L_GM,"Yes",-;
switch(@donated) {
	case 0:
		query_sql "INSERT INTO `donate` VALUES ('"+@aid+"', '"+@donating$+"', '0')";
		break;
	case 1:
		query_sql "UPDATE `donate` SET `amount` = `amount` + "+@donating$+" WHERE `account_id` = '"+@aid+"'";
		break;
	}
query_sql "SELECT `amount` FROM `donate` WHERE `account_id` = "+@aid+"", @newdonated$;
mes "[GM Menu]";
mes "Donation added successfully!";
mes ""+@donator$+" has donated a total of $"+@newdonated$+"";
next;
goto L_GM;

L_ZERO:
mes "[GM Menu]";
mes "You can't have 0 as an amount!";
next;
goto L_GM;

L_NONE:
mes "[GM Menu]";
mes "Account name "+@donator$+" does not exist.";
next;
goto L_GM;

L_REMOVE:
mes "[GM Menu]";
mes "Please enter the donator's username:";
input @donator$;
query_sql "SELECT `account_id` FROM `login` WHERE `userid` = '"+escape_sql(@donator$)+"'", @aid;
if(@aid==0) goto L_NONE;
query_sql "SELECT `amount` FROM `donate` WHERE `account_id` = "+@aid+"", @donated$;
query_sql "SELECT '"+@donated$+"' > 0", @donated;

if(@donated==0) {
	query_sql "DELETE FROM `donate` WHERE `account_id` = '"+@aid+"'";
	mes ""+@donator$+" is not a donator and has been deleted from the donation database.";
	goto L_GM;
	}

mes ""+@donator$+" has donated $"+@donated$+".";
next;
menu "Deduct an amount from "+@donator$+"",L_MINUS,"Remove "+@donator$+" from the donation database",L_DELETE;
close;

L_MINUS:
mes "[GM Menu]";
mes "Please enter the amount "+@donator$+" is to be deducted by:";
input @deduct$;
query_sql "SELECT "+escape_sql(@deduct$)+" = 0", @invalid;
if(@invalid) goto L_ZERO;
query_sql "SELECT CAST('"+escape_sql(@deduct$)+"' AS DECIMAL)", @deduct$;
mes "[GM Menu]";
mes "You have specified that "+@donator$+" is to be deducted by $"+@deduct$+".";
mes "Would you like to continue?";
next;
menu "No",L_GM,"Yes",-;
query_sql "UPDATE `donate` SET `amount` = `amount` - "+@deduct$+" WHERE `account_id` = '"+@aid+"'";
query_sql "SELECT `amount` FROM `donate` WHERE `account_id` = "+@aid+"", @afterdeduct$;
mes "[GM Menu]";
mes "Donation deducted successfully!";
mes ""+@donator$+" has donated a total of $"+@afterdeduct$+"";
next;
goto L_GM;

L_DELETE:
mes "[GM Menu]";
mes "You have specified to remove "+@donator$+" from the donation database.";
mes "Would you like to continue?";
next;
menu "No",L_GM,"Yes",-;
query_sql "DELETE FROM `donate` WHERE `account_id` = '"+@aid+"'";
mes "[GM Menu]";
mes "Donator deleted successfully!";
next;
goto L_GM;

L_VIEWALL:
mes "[GM Menu]";
query_sql "SELECT `account_id`,`amount` FROM `donate` ORDER BY `amount` DESC", @donatoraid, @donatedamount$;
for(set @i, 0; @i < getarraysize(@donatoraid); set @i, @i + 1){
	query_sql "SELECT `userid` FROM `login` WHERE `account_id` = '"+@donatoraid[@i]+"'", @donateruserid$;
	for(set @j, 0; @j < getarraysize(@donateruserid$); set @j, @j + 1){
		mes ""+@donateruserid$[@j]+" - "+@donatedamount$[@i]+"";
	}
}
next;
goto L_GM;

}