Go Back   EQEmulator Home > EQEmulator Forums > Quests > Quests::Custom

Quests::Custom Custom Quests here

Reply
 
Thread Tools Display Modes
  #1  
Old 04-27-2020, 02:15 PM
Mortow's Avatar
Mortow
Hill Giant
 
Join Date: Apr 2013
Posts: 215
Default 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);
				}
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 08:30 AM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3