Count total of silver coins got in fishing per week and upload it in database

[CyanZoldyck]

Hello! Anyone would be able to help me on this script? I would like to make a count on total of Silver Coins farmed/loot from fishing and then put in a NPC that will display the total of farmed silver coins. I also would like to reset the total count of farmed silver coins weekly every Friday.

 

Here's my script for my fishing:

brasilis,258,60,0	script	Fishing Hole 1	723,{
	
//Fishing rod
set .@Rod,2764;
//Fishing Lure
set .@Lure,2775;
//Auto-Fish
set .@Auto,1;
//Auto-Fish on Fail
set .@AutoFail,1;
Fish:
	if (isequipped(.@Rod)) && (isequipped(.@Lure)){
		specialeffect EF_BUBBLE,"Fishing Hole";
		soundeffect "fishingrod.wav",0;
		dispbottom "[Fishing] Casting...";
		set .@fcast,20;
			if (isequipped(2550)) { //Fisher's Muffler
				set .@fcast,.@fcast - 2;
			}
			if (isequipped(2443)) { //Fisher's Boots
				set .@fcast,.@fcast - 2;
			}
			if (isequipped(5317)) { //Fish_Hat
			set .@fcast,.@fcast - 2;
		}
			if (isequipped(2764)) { //Fishing Pole
				set .@fcast,.@fcast - 3;
			}
			if (isequipped(2775)) { //Fishing Lure
				set .@fcast,.@fcast - 1;
			}
		progressbar "ffffff",.@fcast;
		if (rand(1,20) == 2){
		getitem 30203,2; //Fish with Blue Back
		specialeffect2 EF_TEMP_OK;
		mapannounce strcharinfo(3),strcharinfo(0)+" has caught a Silver Coin 2x!",bc_map,"0xff77ff";
		if(.@Auto==1){
		goto Fish;}else{
		end;}
		}
			if (rand(1,6) == 1) ||(rand(1,6) == 3) || (rand(1,6) == 6){
			setarray .@Catch[0],30203;// List of Junk/Other
			set .@CatchRand,.@Catch[rand(getarraysize(.@Catch))];
			getitem .@CatchRand,1;
			}
			else {
			dispbottom "[Fishing] Nothing was caught...";
			if(.@AutoFail == 1){
			goto Fish;} else{
			end;}
			}
		if(.@Auto == 1){
		goto Fish;} else{
		end;}
		}
	else {
	dispbottom "[Fishing] You need a Rod and Lure.";
	end;
	}
}

 

[CyanZoldyck]

*bump* anyone can help me on this? I really appreciate it. Thanks so much!

[Racaae]

Hi. Find this line

getitem 30203,2; //Fish with Blue Back

And insert this below:

		.@silver_coins = 2; //coins fished just now
		$fishing_total += .@silver_coins; //server total
		.@current_points = 0; //anti bug
		query_sql "SELECT `points` FROM `fishing_rank` WHERE `player_id` = '"+getcharid(0)+"'", .@current_points;
		if (.@current_points)
			query_sql "UPDATE `fishing_rank` SET `points` = " + (.@current_points + .@silver_coins) + ", `last_fished` = now() WHERE `player_id` = '"+getcharid(0)+"'";
		else
			query_sql "INSERT INTO fishing_rank VALUES ( "+ getcharid(0) +", "+ .@silver_coins +", now() )";

 

And put this NPC:

Spoiler

brasilis,261,72,4	script	Fishing Scoreboard	4_BOARD3,{
	mes "Adventurers fished a total of ^0000FF" + F_InsertComma($fishing_total) + " Silver Coins^000000 this week.";
	mes "[Top Fishers]";
	.@nb = query_sql( "SELECT points, (SELECT name FROM `char` where char_id = player_id) FROM fishing_rank GROUP BY player_id ORDER BY points DESC LIMIT 3", .@points, .@name$ );
	if ( !.@nb )
		mes "---------";
	else
		for ( .@i = 0; .@i < .@nb; ++.@i )
			mes "" + (.@i+1<10?" ":"") + ( .@i +1 )+"° "+ .@name$[.@i] +" ~ "+ .@points[.@i] +" coins";
	mes " ";
	mes "          Resets every Friday.";
	if (getgroupid() > 80) {
		if (select("Do nothing.", "^FF0000[GM] Force reset^000000") == 1)
			close;
		clear;
		mes "Are you sure?";
		mes "^FF0000This cannot be undone.^000000";
		next;
		if(select("Cancel.", "Confirm.") == 1)
			close;
		query_sql "TRUNCATE TABLE `fishing_rank`";
		$fishing_total = 0;
		announce "Brasilis Mayor: It's now the start of a new fishing week! Good luck.", 0;
		mes "Done.";
	}
	close;

// The count resets every Friday hour03 minute00
OnFriday0300:
	.@nb = query_sql( "SELECT points, (SELECT name FROM `char` where char_id = player_id) FROM fishing_rank GROUP BY player_id ORDER BY points DESC LIMIT 3", .@points, .@name$ );
	if (.@nb )
		announce "Brasilis Mayor: Congratulations, " + .@name$[0] + " is the best fisher of the week!", 0;
	query_sql "TRUNCATE TABLE `fishing_rank`";
	$fishing_total = 0;
	end;

OnInit:
	query_sql "CREATE TABLE IF NOT EXISTS `fishing_rank` (`player_id` int(11) unsigned NOT NULL default '0', `points` int(11) unsigned NOT NULL default '0', `last_fished` datetime NOT NULL, KEY `player_id` (`player_id`) ) ENGINE=MyISAM";
	end;
}