[RO] SQL Bank

// https://rathena.org/board/topic/115606-zeny-in-separate-table/

/*
CREATE TABLE IF NOT EXISTS `e_bank` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`aid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`bank_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`zeny` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
	`daily_deposit` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`daily_withdraw` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	KEY `aid` (`aid`)
) ENGINE=InnoDB;
*/


prontera,155,181,5	script	Sample	4_F_KAFRA1,{
	doevent "bank_main::OnTalk";
}

-	script	bank_main	-1,{
	function func_SetupBank;

	OnInit:
		// func_SetupBank(<id>, "<name>", <cost>, <deposit_limit>, <withdraw_limit>);
		func_SetupBank(1, "Basic Account", 1000000, 100000000, 10000000);
		func_SetupBank(2, "Premium Account", 5000000, 1000000000, 100000000);
		// func_SetupBank(3, "Wicked Account", 10000000, 5000000000, 1000000000); // by default emulator only can hold 2.1b integer
		end;

	OnTalk:
		.@aid = getcharid(3);
		query_sql("SELECT `id`,`bank_id`,`daily_deposit`,`daily_withdraw`,`zeny` FROM `e_bank` WHERE `aid` = "+.@aid+" LIMIT 1", .@id, .@bank_id, .@daily_deposit, .@daily_withdraw, .@total_zeny$);

		if (.@id > 0 && .@bank_id > 0) {
			mes "^0055FF" + .bank_name$[.@i]+"^000000";
			mes "Total Zeny: " + F_InsertComma(.@total_zeny$)+" z";
			mes "Daily deposit limit:";
			mes F_InsertComma(.@daily_deposit) + " / " + F_InsertComma(.bank_limit_deposit[.@bank_id - 1])+" z";
			mes "Daily withdraw limit:";
			mes F_InsertComma(.@daily_withdraw) + " / " + F_InsertComma(.bank_limit_withdraw[.@bank_id - 1])+" z";
			next;
			switch (select(
				"Deposit Zeny",
				"Withdraw Zeny",
				"^FF0000Cancel Account^000000"
			)) {
				case 1:
					.@max_amount = (.bank_limit_deposit[.@bank_id - 1] - .@daily_deposit);
					if (.@max_amount > Zeny)
						.@max_amount = Zeny;
					mes "How many Zeny will be deposit into the account? (max: "+F_InsertComma(.@max_amount)+"z)";
					input .@amount, 0, .@max_amount;
					if (.@amount > 0) {
						Zeny -= .@amount;
						query_sql("UPDATE `e_bank` SET `zeny` = `zeny` + "+.@amount+", `daily_deposit` = `daily_deposit` + "+.@amount+" WHERE `aid` = "+.@aid+" LIMIT 1");
						mes "You have deposit "+F_InsertComma(.@amount)+" Zeny into the Bank.";
					}
					break;
				case 2:
					.@max_amount = (.bank_limit_withdraw[.@bank_id - 1] - .@daily_withdraw);
					if (.@max_amount > (MAX_ZENY - Zeny))
						.@max_amount = (MAX_ZENY - Zeny);
					mes "How many Zeny will be withdraw into the account? (max: "+F_InsertComma(.@max_amount)+"z)";
					input .@amount, 0, .@max_amount;
					if (.@amount > 0) {
						Zeny += .@amount;
						query_sql("UPDATE `e_bank` SET `zeny` = `zeny` - "+.@amount+", `daily_withdraw` = `daily_withdraw` + "+.@amount+" WHERE `aid` = "+.@aid+" LIMIT 1");
						mes "You have withdraw "+F_InsertComma(.@amount)+" Zeny from the Bank.";
					}
					break;
				case 3:
					if (.@total_zeny$ != "0") {
						mes "You need to withdraw all the remaining "+F_InsertComma(.@total_zeny$)+" zeny before you can cancel your account.";
					}
					else {
						query_sql("DELETE FROM `e_bank` WHERE `aid` = "+.@aid+" LIMIT 1");
						mes "Your bank account has been cancelled.";
					}
					break;
				default:
					break;
			}
		}
		else {
			mes "You didnt have a bank account yet.";
			next;
			if (select("Create a Bank?", "Cancel") == 1) {
				mes "Pick a bank type...";
				for (.@i = 0; .@i < .bank_size; .@i++) {
					mes " ";
					mes "^0055FF" + .bank_name$[.@i]+"^000000";
					mes "Daily deposit limit:";
					mes F_InsertComma(.bank_limit_deposit[.@i])+" z";
					mes "Daily withdraw limit:";
					mes F_InsertComma(.bank_limit_withdraw[.@i])+" z";
					mes "Cost: " + F_InsertComma(.bank_cost[.@i])+" z";
					.@menu$ = .@menu$ + .bank_name$[.@i] + ":";
				}
				next;
				.@i = select(.@menu$) - 1;
				if (Zeny < .bank_cost[.@i]) {
					mes "You dont have enough zeny to purchase this bank type.";
				}
				else {
					Zeny -= .bank_cost[.@i];
					query_sql("INSERT INTO `e_bank` (`aid`,`bank_id`) VALUES ("+.@aid+", "+.bank_id[.@i]+")");
					mes "You have successfully purchased this bank type.";
				}
			}
		}
		close;

	OnHour00:
		query_sql("UPDATE `e_bank` SET `daily_deposit` = 0, `daily_withdraw` = 0");
		end;

	function	func_SetupBank	{
		.@id = getarg(0, 0);
		.@name$ = getarg(1, "");
		.@cost = getarg(2, MAX_ZENY);
		.@limit_deposit = getarg(3, MAX_ZENY);
		.@limit_withdraw = getarg(4, MAX_ZENY);

		if (.@id > 0 && .@name$ != "") {
			.bank_id[.bank_size] = .@id;
			.bank_name$[.bank_size] = .@name$;
			.bank_cost[.bank_size] = .@cost;
			.bank_limit_deposit[.bank_size] = .@limit_deposit;
			.bank_limit_withdraw[.bank_size] = .@limit_withdraw;
			.bank_size++;
		}
		return;
	}

}