Augment Creation Quest

[Sonikboom]

I'm in the process of creating a new server with some fun custom content. I just finished a perl script to create custom augments from gear/items you collect. Since this is my first major script, I thought I would put it up on here to get some tips. I've never developed in perl before, but I don't find it much different than any other scripting I've done.

If you want to try the script out, it's live on the server I'm working on: "The Augmented Server". The guy who gives quest is "Augment Master" in the bazaar.

The point of this script is to allow a player to turn in 1-4 items and have the quest giver turn it into an augment with the combined stats of those items.

There are two important configuration notes that goes along with this script:
1. The server MUST be restarted before the augment can be rewarded to player. I've set my server up to restart every night at midnight PST which will accommodate my configuration of not passing out the augments until the next calendar day.
2. You must add a table to the database (table definition at bottom of page).

#!/usr/bin/perl
use DBI;


#database configuration information
$db="peq";
$host="localhost";
$user="####";
$password="####"; 

#connect to MySQL database
my $dbh = DBI->connect ("DBI:mysql:database=$db:host=$host", $user, $password);

sub EVENT_SAY 
{ 
	my $workOrders = quest::saylink("Work Orders", 1);
	my $redeem = quest::saylink("Redeem Augments", 1);
	my $NPCName = "Augment Master";
	if($text=~/Hail/i)
	{
		$client->Message(315,"$NPCName whispers to you, 'I am the augmentation master. If you can provide me with the proper materials and a payment for work, I can imbue an augment with the powers of your armor and it's current augments.'") ;
		$client->Message(315,"$NPCName whispers to you, 'If you wish to have an item appraised for work, just hand me the item.'");
		$client->Message(315,"$NPCName whispers to you, 'If you wish to view your current work orders, click here [$workOrders].'");
		$client->Message(315,"$NPCName whispers to you, 'If you wish to redeem any finished items, click here [$redeem].'");
	}
	
	if($text=~/Work Orders/i)
	{
		#Get current work orders that need to be fulfilled for player.
		my $workorders = $dbh->prepare( "SELECT * from augment_work_orders where player_id = '$charid'");
		$workorders->execute( );
		
		my $hasWorkOrder = false;
		while ( my @workorderrow = $workorders->fetchrow_array( ) )  {
			$hasWorkOrder = true;
			my $newAugId = @workorderrow[2];
			
			#get the stats for this augment
			my $workOrderItem = $dbh->prepare("SELECT * from items where id = '$newAugId'");
			$workOrderItem->execute();
			my @row = $workOrderItem->fetchrow_array();
			
			#display information to the client about their item
			$client->Message(315,"--------$row[2]:---------'");
			$client->Message(315,"Damage: $row[49], Delay: $row[52]") ;  
			$client->Message(315,"HP: $row[75], Mana: $row[88]") ; 
			$client->Message(315,"Mana Regen: $row[89], HP Regen: $row[76]") ;
			$client->Message(315,"AC: $row[4], ATK: $row[12]");
			$client->Message(315,"STR: $row[11]") ; 		
			$client->Message(315,"STA: $row[10]") ; 		
			$client->Message(315,"AGI: $row[3]") ; 			
			$client->Message(315,"DEX: $row[7]") ; 		
			$client->Message(315,"WIS: $row[26]") ; 		
			$client->Message(315,"INT: $row[8]") ; 		
			$client->Message(315,"CHA: $row[6]") ; 		
			$client->Message(315,"PR: $row[97]") ; 		
			$client->Message(315,"MR: $row[93]") ; 		
			$client->Message(315,"DR: $row[55]") ; 		
			$client->Message(315,"FR: $row[71]") ;			
			$client->Message(315,"CR: $row[48]") ; 	
			$client->Message(315,"Haste: $row[73]");
		}
		
		#if there weren't any work orders, tell them
		if(!$hasWorkOrder)
		{
			$client->Message(315,"$NPCName whispers to you, 'I currently do not have any work orders from you.'");
		}
	}
	
	if($text =~/Redeem Augments/i)
	{
		#get the augments that have been added long enough to go through a server restart
		my $workorders = $dbh->prepare( "SELECT * from augment_work_orders where player_id = '$charid' and order_date != CURDATE();");
		$workorders->execute( );
		while ( my @workorderrow = $workorders->fetchrow_array( ) ) 
		{
			#give item to player
			quest::summonitem(@workorderrow[2]);
			
			#remove work order from the augment_work_order table
			my $removeWorkOrder = $dbh->prepare( "delete from augment_work_orders where player_id = '$charid' and item_id = '$workorderrow[2]'");
			$removeWorkOrder->execute( );
		}
		$client->Message(315,"$NPCName whispers to you, 'I have nothing more to return to you at this time.'");
		
	}
}

sub EVENT_ITEM 
{
	#get the stats on each of the items given to the NPC
	my $sth = $dbh->prepare( "SELECT * FROM items where id = '$item1' or id = '$item2' or id = '$item3' or id = '$item4' ");
	$sth->execute( );
	
	#create variables for all of the stats to be added together
	my $dmg = 0;       	
	my $delay = 0;		
	my $hp = 0; 		
	my $regen = 0;		
	my $mana = 0;		
	my $manaregen = 0; 
	my $ac = 0; 		
	my $atk = 0; 		
	my $str = 0; 		
	my $sta = 0; 		
	my $agi = 0; 		
	my $dex = 0; 		
	my $wis = 0; 		
	my $int = 0; 		
	my $cha = 0;		
	my $pr = 0; 		
	my $mr = 0; 		
	my $dr = 0; 		
	my $fr = 0;			
	my $cr = 0;			
	my $haste = 0;		
	
	#create variable to store tier (Used for pricing increases)
	my $tier = 0;
	
	while ( my @row = $sth->fetchrow_array( ) )  {
		#increment stats for each item
		$dmg = $dmg + $row[49];       	
		$delay = $delay + $row[52];				
		$hp = $hp + $row[75]; 				
		$regen = $regen + $row[76];				
		$mana = $mana + $row[88];			
		$manaregen = $manaregen + $row[89]; 
		$ac = $ac + $row[4]; 					
		$atk = $atk + $row[12]; 				
		$str = $str + $row[11]; 			
		$sta = $sta + $row[10]; 				
		$agi = $agi + $row[3]; 					
		$dex = $dex + $row[7]; 					
		$wis = $wis + $row[26]; 				
		$int = $int + $row[8]; 					
		$cha = $cha + $row[6]; 				
		$pr = $pr + $row[97]; 				
		$mr = $mr + $row[93]; 					
		$dr = $dr + $row[55]; 					
		$fr = $fr + $row[71];					
		$cr = $cr + $row[48]; 					
		$haste = $haste + $row[73]; 			
		
		#determine the highest tier and set it as tear
		if($row[253] > $tier)
		{
			$tier = $row[253];
		}
	}
	
	#increment tier for new item
	$tier = $tier + 1;
	
	#pricing structure for stat types
	$regularStatPrice = 10;
	$achpmanaPrice = 10;
	$resistCost = 5;
	$hasteCost = 1000;
	$dmgregenCost = 1000;
	
	#calculate regular stats totals and add to cost
	my $regularStats = $str + $sta + $agi + $dex + $wis + $int + $cha; 
	$cost = $regularStatPrice * $regularStats;
	
	#calculate AC + HP + Mana totals and add to cost
	my $achpmanaStats = $hp + $mana + $ac;
	$cost = $cost + ($achpmanaStats * $achpmanaPrice);
	
	#calculate resists totals and add to cost
	my $resistStats = $pr + $mr + $dr + $fr + $cr;
	$cost = $cost + ($resistCost * $resistStats);
	
	#calculate Haste totals and add to cost
	my $hasteStat = $haste;
	$cost = $cost + ($hasteCost * $hasteStat);
	
	#calculate DMG and Regen totals and add to cost
	my $dmgregenStat = $dmg + $regen + $manaregen;
	$cost = $cost + ($dmgregenCost * $dmgregenStat);
	
	#multiply cost by tier to get final price
	$cost = $cost * $tier;
	
	#display price to client
	$client->Message(315,"$NPCName whispers to you, 'The total cost to turn these items into an augment is $cost platinum pieces.'") ;
	
	#if they included the correct amount of money, create the item and the work order
	if($platinum == $cost)
		{
			#insert statement for new item
			my $newAugStatement = "INSERT INTO `peq`.`items`(`id`,`minstatus`,`Name`,`aagi`,`ac`,`accuracy`,`acha`,`adex`,`aint`,`artifactflag`,`asta`,`astr`,`attack`,`augrestrict`,`augslot1type`,`augslot1visible`,`augslot2type`,`augslot2visible`,`augslot3type`,`augslot3visible`,`augslot4type`,`augslot4visible`,`augslot5type`,`augslot5visible`,`augtype`,`avoidance`,`awis`,`bagsize`,`bagslots`,`bagtype`,`bagwr`,`banedmgamt`,`banedmgraceamt`,`banedmgbody`,`banedmgrace`,`bardtype`,`bardvalue`,`book`,`casttime`,`casttime_`,`charmfile`,`charmfileid`,`classes`,`color`,`combateffects`,`extradmgskill`,`extradmgamt`,`price`,`cr`,`damage`,`damageshield`,`deity`,`delay`,`augdistiller`,`dotshielding`,`dr`,`clicktype`,`clicklevel2`,`elemdmgtype`,`elemdmgamt`,`endur`,`factionamt1`,`factionamt2`,`factionamt3`,`factionamt4`,`factionmod1`,`factionmod2`,`factionmod3`,`factionmod4`,`filename`,`focuseffect`,`fr`,`fvnodrop`,`haste`,`clicklevel`,`hp`,`regen`,`icon`,`idfile`,`itemclass`,`itemtype`,`ldonprice`,`ldontheme`,`ldonsold`,`light`,`lore`,`loregroup`,`magic`,`mana`,`manaregen`,`enduranceregen`,`material`,`maxcharges`,`mr`,`nodrop`,`norent`,`pendingloreflag`,`pr`,`procrate`,`races`,`range`,`reclevel`,`recskill`,`reqlevel`,`sellrate`,`shielding`,`size`,`skillmodtype`,`skillmodvalue`,`slots`,`clickeffect`,`spellshield`,`strikethrough`,`stunresist`,`summonedflag`,`tradeskills`,`favor`,`weight`,`UNK012`,`UNK013`,`benefitflag`,`UNK054`,`UNK059`,`booktype`,`recastdelay`,`recasttype`,`guildfavor`,`UNK123`,`UNK124`,`attuneable`,`nopet`,`updated`,`comment`,`UNK127`,`pointtype`,`potionbelt`,`potionbeltslots`,`stacksize`,`notransfer`,`stackable`,`UNK134`,`UNK137`,`proceffect`,`proctype`,`proclevel2`,`proclevel`,`UNK142`,`worneffect`,`worntype`,`wornlevel2`,`wornlevel`,`UNK147`,`focustype`,`focuslevel2`,`focuslevel`,`UNK152`,`scrolleffect`,`scrolltype`,`scrolllevel2`,`scrolllevel`,`UNK157`,`serialized`,`verified`,`serialization`,`source`,`UNK033`,`lorefile`,`UNK014`,`svcorruption`,`UNK038`,`UNK060`,`augslot1unk2`,`augslot2unk2`,`augslot3unk2`,`augslot4unk2`,`augslot5unk2`,`UNK120`,`UNK121`,`questitemflag`,`UNK132`,`clickunk5`,`clickunk6`,`clickunk7`,`procunk1`,`procunk2`,`procunk3`,`procunk4`,`procunk6`,`procunk7`,`wornunk1`,`wornunk2`,`wornunk3`,`wornunk4`,`wornunk5`,`wornunk6`,`wornunk7`,`focusunk1`,`focusunk2`,`focusunk3`,`focusunk4`,`focusunk5`,`focusunk6`,`focusunk7`,`scrollunk1`,`scrollunk2`,`scrollunk3`,`scrollunk4`,`scrollunk5`,`scrollunk6`,`scrollunk7`,`UNK193`,`purity`,`evolvinglevel`,`clickname`,`procname`,`wornname`,`focusname`,`scrollname`,`dsmitigation`,`heroic_str`,`heroic_int`,`heroic_wis`,`heroic_agi`,`heroic_dex`,`heroic_sta`,`heroic_cha`,`heroic_pr`,`heroic_dr`,`heroic_fr`,`heroic_cr`,`heroic_mr`,`heroic_svcorrup`,`healamt`,`spelldmg`,`clairvoyance`,`backstabdmg`,`created`,`elitematerial`,`ldonsellbackrate`,`scriptfileid`,`expendablearrow`,`powersourcecapacity`,`bardeffect`,`bardeffecttype`,`bardlevel2`,`bardlevel`,`bardunk1`,`bardunk2`,`bardunk3`,`bardunk4`,`bardunk5`,`bardname`,`bardunk7`,`UNK214`)
				( select max(id) + 1, '0', 'Augment Master Forged Augment', '$agi', '$ac', '0', '$cha', '$dex', '$int', '0', '$sta','$str', '$atk', '0', '0', '1', '0', '1', '0', '1', '0','1', '0', '1', '4091', '0', '$wis', '0', '0', '0', '0','0', '0', '0', '0', '0', '0', '0', '0', '0', '','0', '65535', '4278190080', '0', '0', '0', '0', '$cr', '$dmg', '0','0', '0', '47006', '0', '$dr', '0', '0', '0', '0', '0','0', '0', '0', '0', '0', '0', '0', '0', '', '-1','$fr', '0', '$haste', '0', '$hp', '$regen', '963', 'IT63', '0', '54','510', '16', '1', '0', 'An aura emanates from within', '0', '0', '$mana', '$manaregen', '0','0', '0', '$mr', '0', '1', '0', '$pr', '0', '65535', '0','0', '0', '0', '1', '0', '0', '-1', '0', '2097150', '-1','0', '0', '0', '0', '0', '0', '0', '0', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '2010-09-11 11:23:49', '', '0', '1', '0', '0', '1', '0', '0', '', '0', '-1', '0', '0', '0', '0', '-1', '0', '0', '0', '0', '0', '0', '0', '0', '-1', '0', '0', '0', '0', NULL, '2009-04-10 17:38:02', NULL, '13THFLOOR', '0', '', '0', '0', '0', '0', '0', '0', '0', '0', '0', '-1', '0', '0', '0000000000000000000', '0', '', '-1', '0', '0', '0', '0', '', '-1', '0', '0', '0', '0', '0', '', '-1', '0', '0', '0', '0', '0', '', '-1', '0', '0', '0', '0', '0', '', '-1', '0', '0', '0', '', '', '', '', '', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '20031014223933', '0', '70', '0', '0', '0', '-1', '0', '0', '0', '0', '0', '0', '0', '0', '', '-1', '$tier' from items );";
			my $createAug = $dbh->prepare("$newAugStatement");
			$createAug->execute( );
			
			#get the new items id
			my $lastid = $dbh->prepare( "SELECT id from items order by id desc limit 1");
			$lastid->execute( );
			my $newAugId = 0;
			while ( my @row2 = $lastid->fetchrow_array( ) )  {
				$newAugId = @row2[0];
			}
			
			
			#create new augment work order with newly created item
			#has to work like this since items are not automatically added to the game when added in DB
			my $workOrderCreate = $dbh->prepare("insert into `peq`.`augment_work_orders` (player_id, item_id, order_date) values ('$charid', '$newAugId', NOW());");
			$workOrderCreate->execute();
			
			
			#Notify the Player that the work order was successful
			$client->Message(315, "$NPCName whispers to you, 'Work order created successfully'");
		}else
		{
			plugin::return_items(\%itemcount);
		}
}

The create statement for augment_work_order table is:

CREATE TABLE `augment_work_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  PRIMARY KEY (`id`)
);

Thanks for any feedback guys!

[Bristlebane[nj]]

Really nice, thumbs up.

[revloc02c]

I can't get this to work:

use DBI;

#database configuration information
$db="peq";
$host="localhost";
$user="root";
$password="password";

#connect to MySQL database
my $dbh = DBI->connect ("DBI:mysql:database=$db:host=$host", $user, $password);

sub EVENT_SAY {
	if($text=~/Hail/i) {
		$client->Message(315,"I am attempting to connect to the database.");
		
		my $workOrderItem = $dbh->prepare("SELECT * from items where id = '11621'");
		$workOrderItem->execute();
		$client->Message(315,"Hey, I have apparently connected.");
		my @row = $workOrderItem->fetchrow_array();
		
		$client->Message(315,"--------$row[2]:---------'");
		$client->Message(315,"Damage: $row[49], Delay: $row[52]") ;  
		$client->Message(315,"HP: $row[75], Mana: $row[88]") ; 
		$client->Message(315,"Mana Regen: $row[89], HP Regen: $row[76]") ;
		$client->Message(315,"AC: $row[4], ATK: $row[12]");
		$client->Message(315,"STR: $row[11]") ; 		
		$client->Message(315,"STA: $row[10]") ; 		
		$client->Message(315,"AGI: $row[3]") ; 			
		$client->Message(315,"DEX: $row[7]") ; 		
		$client->Message(315,"WIS: $row[26]") ; 		
		$client->Message(315,"INT: $row[8]") ; 		
		$client->Message(315,"CHA: $row[6]") ; 		
		$client->Message(315,"PR: $row[97]") ; 		
		$client->Message(315,"MR: $row[93]") ; 		
		$client->Message(315,"DR: $row[55]") ; 		
		$client->Message(315,"FR: $row[71]") ;			
		$client->Message(315,"CR: $row[48]") ; 	
		$client->Message(315,"Haste: $row[73]");
	}
}

The only thing I get is: "I am attempting to connect to the database."

Any ideas?

[joligario]

Never tried to use DBI in eqemu. Did you add the #!/usr/bin/perl to the top of the script? Are your perl environment variables set?

[revloc02c]

Originally Posted by joligario

Did you add the #!/usr/bin/perl to the top of the script?

Yes.

Originally Posted by joligario

Are your perl environment variables set?

Ummmmmm...what?

[Akkadius]

Originally Posted by revloc02c

Yes.


Ummmmmm...what?

DBI and DBD::Mysql modules aren't installed by default.

Go to cmd ->

ppm install dbi
ppm install dbd::mysql

I'm going to just assume that you are using windows, that's what you'll need to do above.

[revloc02c]

Originally Posted by Akkadius

ppm install dbi

I had this one already...

Originally Posted by Akkadius

ppm install dbd::mysql

but not this, and it works now.

Thanks so much, I really appreciate the responses and help.

[Fridgecritter]

I have this set up on an NPC in the right folder for the right zone, and the quest script replies to me, but when I hand in an item the NPC just eats it, and he doesn't respond to clicking any of the links for pending orders or redeem orders. I have my username and password in the spots, and I have dbd-mysql and DBI installed in my Perl. I'm at a loss. I also created the table in the database successfully.

[Fridgecritter]

Sorry for the necro post, and maybe it's just changes in the DB but I just implemented this quest on a fresh install using Akka's installer, and it lets me appraise the item, lets me hand it in with the plat, creates the item in the Augment_work_orders table, but it enters the wrong item number, for an item I already have in the database. I'm looking through the script and I can't for the life of me find the part where it creates the new item number. The item number it created was 150256 which on my server is an item called "Proof: Head of Statue of Rallos Zek"

So when I set the creation date on the custom augment to yesterday's date, he will hand it to me, but it's the Proof: Head of Statue of Rallos Zek item and not the custom augment that I want him to create. I handed him a sword by the way, just some random sword I summoned to test it.

[Fridgecritter]

Just compared the insert SQL statements and this is not compatible with the newest version of PEQ. Bummer. I don't know how to fix it either.

[Fridgecritter]

I see the select max(id) + 1 part, but it's not doing that. It's using an item I already have in the DB. Is the items database still compatible with this script? Maybe there's been some fields added since this was made?

[Burningsoul]

Elvis from Leetsauce's Server may be able to assist, they make extensive use of this.

[Fridgecritter]

Yeah I have several characters on Leetsauce and I love this quest. But if Elvis hasn't updated their database since this script was written it will still work fine.

[Burningsoul]

I could've sworn they updated source/binaries within the last year or so, but the changelog doesn't mention it. Worth a shot at least, cuz yeah this quest is a HUUUGE boon for OCD gearing and a great plat sink.

[JimB_1958]

We had to do quite a bit of customization on Leetsauce to get this to work.

Unless you are pretty good at working with mySQL it may be difficult.

And there are issues trying to add records directly to the items table so we changed it to add to a copy then updated changes during reboots.

Send me a pm here or on our server forums and I may be able to help

Oh... and talk about a plat sink, I tweaked one of these NPCs to be a gambler. He has a chance to increase random stats of the item at a 50/50 chance of destroying it.
Since creating this NPC almost everyone on the server is broke.