Need some Perl help
Let me give a little background to answer some questions about this script. First off, I did not write this and I cannot remember who did but my thanks to whomever it was because my players use it all the time. I have had to modify it to keep it working with changes that have been made to the items table.
Ok, first off this script takes an item and turns it in to an Ornament with most of the stats of the original item, for a price. I have noticed that my players are turning in a lot of items that are the same as other players. this script creates a new item in the items table each time even if it results in a duplicate item. All newly created items are named 'Ornament of <itemname>' obviously with a new unique ID.
I am trying to get the script to search the items table and see if the item already exists there and just hand the ID to the player once they have turned in the item and the correct amount of plat but if the item does not exist, created it and the work order so the player can claim it the next day after a server reboot.
I have added some code and it is kind of working but returning item IDs that are hugely out of range for whats in my table. Item IDs currently only go up to about 200k and it is returning number in the 200mil. I think It may be adding all IDs that start with Ornament of but I have not been able to figure it out. Any help is greatly appreciated.
Code:
use DBI;
use DBD::mysql;
my $dbh = DBI->connect ("DBI:mysql:peq:localhost", root, Logrus8);
sub EVENT_SAY
{
my $workorders = quest::saylink("workorders");
my $redeem = quest::saylink("redeem");
if($text=~/Hail/i)
{
plugin::Whisper("Through my research, I have discovered a way to transmute a weapon into an ornamentation. It takes some time, but the magic will only work on weapons. If you would like me to turn a weapon of yours into an ornamentation, you will need to provide me with a weapon along with some money, you know, for my time and hard work. It will take roughly 24 hours but that is a small wait to have a tailor-made ornamentation. These ornamentations can only be inserted into the ornamentation slot on your weapon. If you would like to check your $workorders, or if you wish to $redeem any finished items.") ;
}
if($text=~/workorders/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[1];
#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
plugin::Whisper("--------$row[2]:---------'");
plugin::Whisper("Damage: $row[51], Delay: $row[54]") ;
plugin::Whisper("HP: $row[77], Mana: $row[90]") ;
plugin::Whisper("AC: $row[4]");
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("Backstab Damage: $row[242]");
}
#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/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[1]);
#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[1]'");
$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 where id = '$item1'");
$sth->execute( );
#create variables for all of the stats to be added together
my $itid = 0;
my $itemname = 0;
my $dmg = 0;
my $hp = 0;
my $mana = 0;
my $endurance = 0;
my $ac = 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 $augtype = 0;
my $hstr = 0;
my $hsta = 0;
my $hagi = 0;
my $hdex = 0;
my $hwis = 0;
my $hint = 0;
my $hcha = 0;
my $bsdmg = 0;
my $extradamskill = 0;
my $extradamamt = 0;
while ( my @row = $sth->fetchrow_array( ) ) {
#increment stats for each item
$itid = $row[1];
$itemname = $row[2];
$dmg = $row[51];
$hp = $row[77];
$mana = $row[90];
$endurance = $row[62];
$ac = $row[4];
$str = $row[11];
$sta = $row[10];
$agi = $row[3];
$dex = $row[7];
$wis = $row[28];
$int = $row[8];
$cha = $row[6];
$pr = $row[100];
$mr = $row[96];
$dr = $row[57];
$fr = $row[73];
$cr = $row[50];
$svcorrup = $row[171];
$augtype = 524288;
$slot = $row[112];
$icon = $row[79];
$class = $row[44];
$proceffect = $row[145];
$procrate = $row[101];
$hstr = $row[226];
$hsta = $row[231];
$hdex = $row[230];
$hagi = $row[229];
$hwis = $row[228];
$hint = $row[227];
$hcha = $row[232];
$hpr = $row[233];
$hdr = $row[234];
$hfr = $row[235];
$hcr = $row[236];
$hmr = $row[237];
$elemdmgtype = $row[60];
$elemdmgamt = $row[61];
$hcorrup = $row[238];
$healamt = $row[239];
$spelldmg = $row[240];
$clairvoyance = $row[241];
$dsmit = $row[225];
$stunresist = $row[116];
$strikethrough = $row[115];
$spellshield = $row[114];
$shielding = $row[108];
$enduranceregen = $row[92];
$manaregen = $row[91];
$regen = $row[78];
$dotshielding = $row[56];
$damageshield = $row[52];
$combateffects = $row[46];
$avoidance = $row[27];
$attack = $row[12];
$accuracy = $row[5];
$focuseffect = $row[72];
$worneffect = $row[150];
$worntype = $row[151];
$focustype = $row[155];
$skillmodtype = $row[110];
$skillmodvalue = $row[111];
$graphic = $row[80];
$bsdmg = $row[242];
$extradamskill = $row[47];
$extradamamt = $row[48];
}
#pricing structure for stat types
$regularStatPrice = 25; #same and resists
$achpmanaPrice = 35; #same
$hStatsCost = 45; # heroics
$DmgregenCost = 45; #mod2s
#calculate regular stats totals and add to cost
my $regularStats = $str + $sta + $agi + $dex + $wis + $int + $cha + $pr + $mr + $dr + $fr + $cr + $svcorrup;
$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 + $hpr + $hdr + $hfr + $hcr + $hmr + $hcorrup;
$cost = $cost + ($hStatsCost * $hStats);
#calculate DMG and Regen totals and add to cost
my $DmgregenStat = $dmg + $bsdmg + $extradamamt + $skillmodvalue + $healamt + $spelldmg + $clairvoyance + $dsmit + $attack + $avoidance + $combateffects + $damageshield + $dotshielding + $regen + $manaregen + $enduranceregen + $shielding + $spellshield + $strikethrough + $stunresist + $accuracy + $elemdmgamt;
$cost = $cost + ($DmgregenCost * $DmgregenStat);
#display price to client
plugin::Whisper("The total cost to turn this weapon 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)
{
my $ItemID1 = $dbh->prepare(("SELECT name FROM items where id = '$itid'"));
$ItemID1->execute( );
my $Const = ("Ornament of ");
my $Exists = $dbh->prepare(("SELECT id from items where name = Concat($Const, $ItemID1) order by id desc limit 1"));
$Exists->execute( );
if($Exists > 0)
{
$client->Message(315, "$NPCName whispers to you, 'You're in luck $name, I have this ornament in stock. Here you go.");
quest::summonitem($Exists);
}
else
{
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`, `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`, `skillmodmax`, `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`, `evoitem`, `evoid`, `evolvinglevel`, `evomax`, `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`, `UNK219`, `UNK220`, `UNK221`, `heirloom`, `UNK223`, `UNK224`, `UNK225`, `UNK226`, `UNK227`, `UNK228`, `UNK229`, `UNK230`, `UNK231`, `UNK232`, `UNK233`, `UNK234`, `placeable`, `UNK236`, `UNK237`, `UNK238`, `UNK239`, `UNK240`, `UNK241`, `epicitem`)
VALUES ((SELECT MAX(id) FROM items C) +1, '0', 'Ornament of $itemname', '$agi', '$ac', '$accuracy', '$cha', '$dex', '$int', '0', '$sta','$str', '$attack', '0', '0', '1', '0', '1', '0', '1', '0','1', '0', '1', '0', '1', '$augtype', '$avoidance', '$wis', '0', '0', '0', '0','0', '0', '0', '0', '0', '0', '0', '0', '0', '','0', '$class', '4278190080', '$combateffects', '$extradamskill', '$extradamamt', '0', '$cr', '$dmg', '$damageshield','0', '0', '47018', '$dotshielding', '$dr', '0', '0', '$elemdmgtype', '$elemdmgamt', '$endurance','0', '0', '0', '0', '0', '0', '0', '0', '', '$focuseffect','$fr', '0', '0', '0', '$hp', '$regen', '$icon', '$graphic', '0', '54','0', '16', '1', '0', '$name made this Ornamentation with $itemname .', '0', '0', '$mana', '$manaregen', '$enduranceregen','0', '0', '0', '$mr', '0', '1', '0', '$pr', '$procrate', '65535', '0','0', '0', '0', '1', '$shielding', '0', '$skillmodtype', '$skillmodvalue', '$slot', '-1','$spellshield', '$strikethrough', '$stunresist', '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, 'Mortow', '0', '', '0', '$svcorrup', '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', '', '', '', '', '', '', '$dsmit', '$hstr', '$hint', '$hwis', '$hagi', '$hdex', '$hsta', '$hcha', '$hpr', '$hdr', '$hfr', '$hcr', '$hmr', '$hcorrup', '$healamt', '$spelldmg', '$clairvoyance', '$bsdmg', '20031014223933', '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, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);";
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, 'I am sorry $name, I do not have this ornament in stock. Come back tomorrow and I will have it made for you.");
plugin::Whisper("Work order created successfully");
}
}
else
{
plugin::return_items(\%itemcount);
}
}
This is what I have added that is not working properly:
Code:
my $ItemID1 = $dbh->prepare(("SELECT name FROM items where id = '$itid'"));
$ItemID1->execute( );
my $Const = ("Ornament of ");
my $Exists = $dbh->prepare(("SELECT id from items where name = Concat($Const, $ItemID1) order by id desc limit 1"));
$Exists->execute( );
if($Exists > 0)
{
$client->Message(315, "$NPCName whispers to you, 'You're in luck $name, I have this ornament in stock. Here you go.");
quest::summonitem($Exists);
}
|
Linear Mode
