EQEmulator Forums - Player-Made ornamentations

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

Code:

#!/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:
http://i59.tinypic.com/35bzhhv.png

After:
http://i57.tinypic.com/erkhas.png

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.