Player-Made ornamentations

[Leetsauce]

This is a quest that will allow a player to turn in any weapon (or item for that matter, you can tailor this quest to your servers needs, but for this example we're using a weapon, and a weapon graphic) into an augment that fits into the 6th slot of your weapons so as to act as an ornamentation. I have tested this on my server and it works though I am sure it can be cleaned up a bit. I am not the original author of the majority of this code. I have mainly just updated the columns, and added some definitions for customization for my own server. Credit goes to Sonikboom from: http://www.eqemulator.org/forums/showthread.php?t=33666

#!/usr/bin/perl
use DBI;


#database configuration information
$db="database";
$host="serverip";
$user="username";
$password="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 ornamentations", 1);
	if($text=~/Hail/i)
	{
		plugin::Whisper("I am the only one in the stable area who can transmute a weapon you have into an ornamentation. I work the same way the others do, but my magic will ONLY work on weapons. When I perform this service, you will provide me a weapon along with the correct payment, and roughly 24 hours later I will have your tailor-made ornamentation ready to use. These ornamentations can only be inserted into the ornamentation slot on your weapon by users of newer clients. After inserting you may return to an older client and maintain the graphic and additional benefit from the ornamentation. If you would like to check your work  " . quest::saylink("orders",1 ) . ", or if you wish to redeem any finished items, click " . quest::saylink("redeem ornamentations",1, "redeem") . ".") ;
	}
	
	if($text=~/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_augs where id = '$newAugId'");
			$workOrderItem->execute();
			my @row = $workOrderItem->fetchrow_array();
			
			#display information to the client about their item
			plugin::Whisper("--------$row[2]:---------'");
			plugin::Whisper("Damage: $row[51], Delay: $row[54]") ;  
			plugin::Whisper("HP: $row[77], Mana: $row[90]") ; 
			plugin::Whisper("Mana Regen: $row[91], HP Regen: $row[78]") ;
			plugin::Whisper("AC: $row[4], ATK: $row[12]");
			plugin::Whisper("STR: $row[11]") ; 		
			plugin::Whisper("STA: $row[10]") ; 		
			plugin::Whisper("AGI: $row[3]") ; 			
			plugin::Whisper("DEX: $row[7]") ; 		
			plugin::Whisper("WIS: $row[28]") ; 		
			plugin::Whisper("INT: $row[8]") ; 		
			plugin::Whisper("CHA: $row[6]") ; 		
			plugin::Whisper("PR: $row[100]") ; 		
			plugin::Whisper("MR: $row[96]") ; 		
			plugin::Whisper("DR: $row[57]") ; 		
			plugin::Whisper("FR: $row[73]") ;			
			plugin::Whisper("CR: $row[50]") ; 	
			plugin::Whisper("Haste: $row[75]");
		}
		
		#if there weren't any work orders, tell them
		if(!$hasWorkOrder)
		{
			plugin::Whisper("I currently do not have any work orders from you.");
		}
	}
	
	if($text =~/Redeem ornamentations/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( );
		}
		plugin::Whisper("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_augs where id = '$item1'");
	$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;	
	my $augtype = 0;
	my $spelldmg = 0;
	my $healamt = 0;
	my $ds = 0;
	my $hstr = 0;
	my $hsta = 0;
	my $hagi = 0;
	my $hdex = 0;
	my $hwis = 0;
	my $hint = 0;
	my $hcha = 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[51];       	
		$delay = $delay + $row[52];				
		$hp = $hp + $row[77]; 				
		$regen = $regen + $row[78];				
		$mana = $mana + $row[90];			
		$manaregen = $manaregen + $row[91]; 
		$endurance = $row[62];
		$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[28]; 				
		$int = $int + $row[8]; 					
		$cha = $cha + $row[6]; 				
		$pr = $pr + $row[100]; 				
		$mr = $mr + $row[96]; 					
		$dr = $dr + $row[57]; 					
		$fr = $fr + $row[73];					
		$cr = $cr + $row[50]; 					
		$haste = $haste + $row[75]; 
		$augtype = 524288;
		$slot = $row[112];
		$spelldmg = $row[237]+50;
		$healamt = $row[236]+50;
		$clairvoyance = $row[238]+50;
		$icon = $row[79];
		$class = $row[44];
		$proceffect = $row[145];
		$procrate = $row[101];
		$ds = $row[52]+50;
		$hstr = $row[223]+1;
		$hsta = $row[228]+1;
		$hdex = $row[227]+1;
		$hagi = $row[226]+1;
		$hwis = $row[225]+1;
		$hint = $row[224]+1;
		$hcha = $row[229]+1;
		$focuseffect = $row[72];
		$worneffect = $row[150];
		$worntype = $row[151];
		$focustype = $row[155];
		$loreitem = $row[2];
		$elemdmg = $row[61];
		$elemdmgtype = $row[60];
		$skillmodtype = $row[110];
		$skillmodvalue = $row[111];
		$graphic = $row[80];
		
		#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; #same and resists
	$achpmanaPrice = 1000; #same
	$hStatsCost = 5000; # heroics
	$hasteCost = 100; #dmg and haste
	$DmgregenCost = 2500; #mod2s
	
	#calculate regular stats totals and add to cost
	my $regularStats = $str + $sta + $agi + $dex + $wis + $int + $cha + $pr + $mr + $dr + $fr + $cr; 
	$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 $hStats = $hstr + $hsta + $hagi + $hdex + $hwis + $hint + $cha;
	$cost = $cost + ($hStatsCost * $hStats);
	
	#calculate Haste totals and add to cost
	my $hasteStat = $haste + $dmg;
	$cost = $cost + ($hasteCost * $hasteStat);
	
	#calculate DMG and Regen totals and add to cost
	my $DmgregenStat = $regen + $manaregen + $spelldmg + $healamt + $dmgshield + $ds;
	$cost = $cost + ($DmgregenCost * $DmgregenStat);
	
	
	#multiply cost by tier to get final price
	$cost = $cost * 13 ;
	$cost = $cost * $tier; #tier is how many times the item has been turned in. price increases dramatically
	
	#setmax
	if($cost > 90000000) {
	$cost = 90000000;
	}
	
	#display price to client
	plugin::Whisper("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_augs`(`id`,`minstatus`,`Name`,`aagi`,`ac`,`accuracy`,`acha`,`adex`,`aint`,`artifactflag`,`asta`,`astr`,`attack`,`augrestrict`,`augslot1type`,`augslot1visible`,`augslot2type`,`augslot2visible`,`augslot3type`,`augslot3visible`,`augslot4type`,`augslot4visible`,`augslot5type`,`augslot5visible`,`augslot6type`,`augslot6visible`,`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`, `herosforgemodel`,`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`,`augslot6unk2`,`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', '$name`s Custom Ornamentation', '$agi', '$ac', '0', '$cha', '$dex', '$int', '0', '$sta','$str', '$atk', '0', '0', '1', '0', '1', '0', '1', '0','1', '0', '1', '0', '1', '$augtype', '0', '$wis', '0', '0', '0', '0','0', '0', '0', '0', '0', '0', '0', '0', '0', '','0', '$class', '4278190080', '0', '0', '0', '0', '$cr', '$dmg', '$ds','0', '0', '47001', '0', '$dr', '0', '0', '$elemdmgtype', '$elemdmg', '$endurance','0', '0', '0', '0', '0', '0', '0', '0', '', '$focuseffect','$fr', '0', '$haste', '0', '$hp', '$regen', '$icon', '$graphic', '0', '54','0', '16', '1', '0', '$name made this Ornamentation with $loreitem .', '0', '0', '$mana', '$manaregen', '0','0', '0', '0', '$mr', '0', '1', '0', '$pr', '$procrate', '65535', '0','0', '0', '0', '1', '0', '0', '$skillmodtype', '$skillmodvalue', '$slot', '-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', '0', '0', '0', '', '0', '$proceffect', '0', '0', '0', '0', '$worneffect', '$worntype', '0', '0', '0', '$focustype', '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', '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', '$hstr', '$hint', '$hwis', '$hagi', '$hdex', '$hsta', '$hcha', '0', '0', '0', '0', '0', '0', '$healamt', '$spelldmg', '$clairvoyance', '0', '20031014223933', '0', '70', '0', '0', '0', '-1', '0', '0', '0', '0', '0', '0', '0', '0', '', '-1', '$tier' from items_augs );";
			my $createAug = $dbh->prepare("$newAugStatement");
			$createAug->execute( );
			
			#get the new items id
			my $lastid = $dbh->prepare( ("SELECT id from items_augs 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
			plugin::Whisper("Work order created successfully");
		}else
		{
			plugin::return_items(\%itemcount);
		}
}

I have a few of these on my server that allows players to turn in any piece of armor or weapon in the game and turn it into an aug, and the player chooses which slot the aug comes out as. So, from the quest we can tell it's simply running an insert query into a bogus table (note: i have mine pointed to table "items_augs" so as to prevent memory leaks by messing with the itemID column while the server is running) from which it is also reading the data (make a copy of your items table so as to allow the items to be read for correct stats) and applying the defaults I have pre-defined.

You can also tell from the script that there are customizations such as additional stats when this process takes place, and you can also see the "tier" field which is basically an inner-working "ranking" system which allows you to upgrade your newly-transformed-augment each time you turn it into one of these npc's. This also allows you to change aug slots of any given aug the player may already have. As an example with this particular script, if you hand in an augment into this npc it will turn it into a slot 20 (ornamentation slot) augment, and it will carry over the item_texture ID of a bag (this is the default graphic for augments) so when you apply it to any given weapon, that weapon will then appear as a bag, but will carry over any stats the augment previously had, and then apply a bonus that I have given for each increment.


If you have any questions, or need additional help getting it set up feel free to shoot me a PM and I can train you to the best of my ability on how to customize this.

Edit:

Before:


After:



Edit2: I should have mentioned before that I had run into issues previously with having this script use 2 separate tables from the database hence my suggestion for creating a duplicate table to which the script can write and from which can read as opposed to messing with your current, and in-force items table that is being used when the server is active. You may have better luck than I did, but this is the method that I was able to find that worked without too much intervention/change. Also, this is a super easy process for exporting from items_augs, and inserting them into items. It only requires you to find/replace some text and running a query that is produced by Heidi for you if you have the correct selection and export it as an Insert statement.

[Figback65]

Nice!!!!! I will play with this later!

[Akkadius]

A+ for sharing worthwhile work!!!

[Mortow]

I have this working but I need a little help on a perl script to copy the newly created aug from the items_aug table to the items table. I plan on running a task to shutdown the server every morning, run the script and restart the server. I know I can do a statement like: perl augcreate.pl to actually execute the script but I am unsure as to the actual code to insert the new item. I add items all the time to my items table so I have to have the code increment the item id. Thanks in advance.

[Leetsauce]

I do it manually a lot of the time because I'm always in a hurry trying to do shit in between reboots. With HeidiSQL which is built into the repack if you're using it, will allow you to export grid rows in the form of an insert statement, I copy it to clipboard and find_replace where table is items and change it to items_augs. Then run that query. This is basically the lazy way to do it =p

[Mortow]

Not sure what I have messed up but every time I redeem an item, it gives me a small leather gorget (item# 2015) I assume this is pulling from the date on the work order and not the item id but looking at the code, I have no idea how to proceed.

[javewow]

No one specifically tells us the order?
step 1 ？
step 2 ？
step 3 ？
step 4 ？
step 5 ？

THX

[Leetsauce]

What do you mean? The order of operations? Look at the code...

[javewow]

Originally Posted by Leetsauce

What do you mean? The order of operations? Look at the code...

Cannot work！

Do you need MYSQL to add tables?
---------------------------------------------

Or trouble you to

"Edge of Seething Hatred" and "Nightmare's Custom Omamentation" items

SQL list is available here for download

Such as:

INSERT INTO `items` () VALUES (737388, 0, 'Serpent\'s Seal', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', '0', 65535, 4278190080, '0', 0, 0, 1000000, 0, 0, 0, 0, 0, 0, 0, 9, 0, 0, 0, 0, -30, 0, 0, 0, 0, 0, 0, 0, 0, '', -1, 8, 0, 0, 0, 0, 0, 647, 'IT63', 0, 11, 0, 0, 0, 0, 'A finely made seal', 0, 1, 0, 0, 0, 0, 0, 0, 8, 1, 1, 0, 17, 0, 65535, 0, -15, 0, -20, 1, 0, 1, -1, 0, 0, -1, 0, 0, 0, 0, 1, 1000, 5, 0, 1, 0, 0, 0, 0, 0, 0, 1000, 0, 0, 0, 0, '2012-12-27 10:35:05', '', 0, 0, 0, 0, 20, 0, 1, '', 0, -1, 0, 0, 0, 0, -1, 0, 0, 0, 0, 0, 0, 0, 0, -1, 0, 0, 0, 0, NULL, '2010-11-09 09:45:17', NULL, '13THFLOOR', 0, '', 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, 0, 0, 0, '2007-11-29 18:14:03', 0, 70, 0, 0, 0, -1, 0, 0, 0, 0, 0, 0, 0, 0, '', -1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, 0, -1, 0, 0, 0, 0, 0, 0, 0, 0, 0);