15th wowhead NPC to TDB 4.3.4 - 2015-08-11

#!/usr/bin/perl
# adjust path to fit your host

# NOT INTENDED FOR UNEDITED IMPORTING TO DATABASE

# This script does NOT import NPC location data or create NPC entries
# Only vendor, training, and skinning/mining loot are extracted

# Fourteenth generation NPC scanner
# Designed for use with Trinity DB 4.3.4
# Other DB formats should be easily implemented

# Output sorted in ascending order (lowest value first)
# Output is NOT sent to a file. Redirect output as desired.

# usage:
#        npc.pl                       (takes input from STDIN, aka piped stream)
#        npc.pl npc#                  (reads the specified NPC from www.wowhead.com)
#        npc.pl startnpc endnpc       (reads the given range of NPCs from www.wowhead.com)
#        npc.pl startnpc endnpc delay (reads the given range of NPCs from www.wowhead.com and
#                                      pauses for "delay" seconds between each read)

# Generation history:
#      First: basic npc_vendor query generation
#     Second: added sorting of items by ilvl
#      Third: add npc_trainer query generation
#      Forth: minor code changes, added ExtendedCost gathering for vendors
#      Fifth: added item ID as secondary sort criteria for item sorting
#      Sixth: updated filter to remove "sourcemore" sub-field which broke vendlist() operation
#             previous filter was only filtering the tail end of this sub-field
#    Seventh: added conditional for header display so only 1 header printed
#             also changed header format
#     Eighth: changed to an updated "sourcemore" (sub-group in the wowhead item entry) filter
#      Ninth: changed filter from "sourcemore" to "sourcemore"|"modelviewer" to strip both sub-groups
#             replaced split function with a regex method for splitting the items up
#             altered comment header format
#      Tenth: streamlined code for item data output
#             added notice to check for extended cost value (from ItemExtendedCost.dbc)
#             added additional output to help identify the `ExtendedCost` value
#   Eleventh: added mining/skinning loot processing
#    Twelfth: relocated ExtendedCost comments
#             added `reqlevel` support for trainer lists
#             tweaked a few print statements
# Thirteenth: added support for user specified sleep period (in seconds) between Wowhead reads
# Fourteenth: removed spacing between values in generated SQL output (Trinity preferred format)
#  Fifteenth: updated regex for current (for August 11 of 2015) wowhead page structure

use warnings;
use strict;
use LWP::Simple;
use constant { true => 1, false => 0 };

my $currentNPC;
my $page;
my $line;
my @lines;
my $NPCname;
my $NPCid;
my $head = false;

# item level then id sort order
sub itemcmp {
  $a =~ /^.*\,\"level\":(\d+)\,/;
  my $x = $1;
  $a =~ /^.*\"id\":(.+?)\,/;
  my $xx = $1;
  $b =~ /^.*\,\"level\":(\d+)\,/;
  my $y = $1;
  $b =~ /^.*\"id\":(.+?)\,/;
  my $yy = $1;
  if ($x eq $y) {
    return ($xx <=> $yy);
  } else {
    return ($x <=> $y);
  }
}

# build MySQL queries
sub vendlist {
  my ($npc, $items) = @_;
  my $slot = 1;
  my $extcost;
  my $extcostamt;
  my $extcosttype;
  $items =~ s/\,\"sourcemore\":\[.+?\]\,|\,\"modelviewer\":\{.+?\}\,/\,/g;
  my @itemlist = ($items =~ m/\"classs\".+?\}/g);
  @itemlist = sort itemcmp @itemlist;
  print "-- CHECK FOR ItemExtendedCost.dbc LOOKUP REQUIREMENT\n" x 4, "\n";
  print "DELETE FROM `npc_vendor` WHERE `entry`=$npc;\n";
  print "INSERT INTO `npc_vendor` (`entry`,`slot`,`item`,`maxcount`,`incrtime`,`ExtendedCost`,`type`) VALUES\n";
  foreach my $itemdata (@itemlist) {
    $extcost = 0;
# extended cost match pattern for currency and/or item: \,cost:\[0\,\[(\[(.+?)\,(.+?)\])?\]\,\[(\[(.+?)\,(.+?)\])?\]\]
    if ($itemdata =~ /\,cost:\[0\,\[\[(.+?)\,(.+?)\]\]\,\[\]\]/) {
# extended cost is the specified amount of a given points id
      $extcost = $1;
      $extcostamt = $2;
      $extcosttype = "points id";
    } elsif ($itemdata =~ /\,cost:\[0\,\[\]\,\[\[(.+?)\,(.+?)\]\]\]/) {
# extended cost is the specified number of a given item id
      $extcost = $1;
      $extcostamt = $2;
      $extcosttype = "item id";
    }
    $itemdata =~ /^.*id\":(.+?)\,\"level\":(.+?)\,\"name\":\"(.+?)\".*\,avail:(.+?)\,/;
    my $itemid = $1;
    my $itemlvl = $2;
    my $itemname = $3;
    my $itemavail = $4;
    if ($itemname =~ /^[+-]?\d/) {
      $itemname =~ s/^.//;
    }
    print "\t($npc,$slot,$itemid,";
# `incrtime` = how often to restock vendor, timer based on seconds (900 = 15 minutes)
    print $itemavail > 0 ? "$itemavail,900" : "0,0";
    print $extcost > 0 ? ", ItemExtendedCost [$extcosttype:$extcost / amount:$extcostamt]" : ",0";
    print ",1)", ($slot < scalar(@itemlist)) ? "," : ";", " -- $itemname (ilvl $itemlvl)\n";
    $slot++;
  }
  print "\n";
}

# required skill sort
sub skcmp {
  $a =~ /^.*\,\"learnedat\":(\d+)\,/;
  my $x = $1;
  $b =~ /^.*\,\"learnedat\":(\d+)\,/;
  my $y = $1;
  return ($x <=> $y);
}

# build MySQL queries
sub trainlist {
  my ($npc, $spells) = @_;
  my $slot = 1;
# shouldn't need the filter here, but we'll have it as a precaution
  $spells =~ s/\,\"sourcemore\":\[.+?\]\,|\,\"modelviewer\":\{.+?\}\,/\,/g;
  my @spellarr = ($spells =~ m/\{.+?\}/g);
  @spellarr = sort skcmp @spellarr;
  print "DELETE FROM `npc_trainer` WHERE `entry`=$npc;\n";
  print "INSERT INTO `npc_trainer` (`entry`,`spell`,`spellcost`,`reqskill`,`reqskillvalue`,`reqlevel`) VALUES\n";
  foreach my $data (@spellarr) {
    $data =~ /^.*id\":(.+?)\,\"learnedat\":(\d+)\,\"level\":(\d+),\"name\":\"(.+?)\".*\,\"skill\":\[(\d+)\].*\,\"trainingcost\":(\d+)/;
    my $spell = $1;
    my $learned = $2;
    my $reqlevel = $3;
    my $name = $4;
    my $skill = $5;
    my $cost = $6;
    if (($name =~ /^[+-]?\d/) || ($name =~ /^@?/)) {
      $name =~ s/^.//;
    }
    print "\t($npc,$spell,$cost,$skill,$learned,$reqlevel)", ($slot < scalar(@spellarr)) ? "," : ";", " -- $name\n";
    $slot++;
  }
  print "\n";
}

# build MySQL queries
sub skinloot {
  my ($npc, $opened, $items) = @_;
  my $slot = 1;
  $items =~ s/\,\"sourcemore\":\[.+?\]\,|\,\"modelviewer\":\{.+?\}\,/\,/g;
  my @itemlist = ($items =~ m/\{.+?\}/g);
  @itemlist = sort itemcmp @itemlist;
  print "-- verify that UNIT_FLAG_SKINNABLE is enabled and point to loot template\n";
  print "UPDATE `creature_template` SET `skinloot`=$npc,`unit_flags`=`unit_flags` | 67108864 WHERE `entry`=$npc;\n\n";
  print "-- (re)build skinning_loot_template\n";
  print "-- CHECK/VERIFY/CORRECT `ChanceOrQuestChance` AND `groupid` VALUES\n";
  print "DELETE FROM `skinning_loot_template` WHERE `entry`=$npc;\n";
  print "INSERT INTO `skinning_loot_template` (`entry`,`item`,`ChanceOrQuestChance`,`lootmode`,`groupid`,`mincountOrRef`,`maxcount`)VALUES\n";
  foreach my $itemdata (@itemlist) {
    $itemdata =~ /^.*\"id\":(.+?)\,\"level\":(.+?)\,\"name\":\"(.+?)\".*\,count:(.+?)\,stack:\[(.+?)\,(.+?)\]/;
    my $id = $1;
    my $ilvl = $2;
    my $name = $3;
    my $count = $4;
    my $minval = $5;
    my $maxval = $6;
    my $pct = sprintf("%.2f", (($count / $opened) * 100));
    if ($name =~ /^[+-]?\d/) {
      $name =~ s/^.//;
    }
    print "\t($npc,$id,$pct,1,0,$minval,$maxval)", ($slot < scalar(@itemlist)) ? "," : ";";
    print " -- $name (ilvl $ilvl) / $count time", ($count > 1) ? "s" : " ", "\n";
    $slot++;
  }
  print "\n";
}

# print name & ID as a comment header
sub header {
  if ($head == false) {
    foreach $line (@lines) {
      if (defined $line && length $line > 0) {
        chop($line);
        if ($line =~ /\$\.extend\(g_npcs/) {
          $line =~ /\$\.extend\(g_npcs\[(.+?)\].*\,\"name\":\"(.+?)\"/;
          $NPCid = $1;
          $NPCname = $2;
          my $dashes = length($1) + length($2) + 3;
          print "-- ---", "-" x $dashes, "---\n";
          print "-- -- $2 \($1\) --\n";
          print "-- ---", "-" x $dashes, "---\n\n";
        }
      }
    }
    $head = true;
  }
}

# scan the page, obviously
sub parsepage {
  my $data;
  my $totalcount;
  foreach $line (@lines) {
    if (defined $line && length $line > 0) {
      if ($line =~ /^new.Listview.*id:..sells/) {
         $line =~ /^new.Listview.*id:..sells.*data: \[(.+?)\]\}\)/;
         $data = $1;
         header();
         vendlist($NPCid, $data);
      } elsif ($line =~ /^new.Listview.*id:..teaches-recipe/) {
         $line =~ /^new.Listview.*id:..teaches-recipe.*data: \[(.+?)\]\}\)/;
         $data = $1;
         header();
         trainlist($NPCid, $data);
      } elsif ($line =~ /^new.Listview.*id:..(mining|skinning)/) {
         $line =~ /^.*_totalCount:.(.+?)\,/;
         $totalcount = $1;
         $line =~ /^new.Listview.*data: \[(.+?)\]\}\)\;/;
         $data = $1;
         header();
         skinloot($NPCid, $totalcount, $data);
      }
    }
  }
}

# generate a URL and get the HTML source
sub getpage {
  my ($NPC) = @_;
  my $URL = "http://www.wowhead.com/npc=$NPC";
  $page = get($URL);
  if (defined $page) {
    $page =~ s/(\r\n)|(\r)/\n/g;
    if ($page =~ /\<title\>Error.-.Wowhead\<\/title\>/) {
      print "\nNPC #$NPC not on Wowhead!\n\n";
      exit;
    }
  } else {
    print "\nUnable to load $URL\n\n";
    exit;
  }
}

if ($#ARGV == -1) {
  while ($_ = <>) {
    push (@lines, $_);
  }
  parsepage();
  exit;
} elsif ($#ARGV == 0) {
  $currentNPC = $ARGV[0];
  getpage($currentNPC);
  @lines = split(/(\r)|(\n)|(\r\n)/, $page);
  parsepage();
  exit;
} elsif ($#ARGV == 1 || $#ARGV == 2) {
  for ($currentNPC = $ARGV[0]; $currentNPC <= $ARGV[1]; $currentNPC++) {
    getpage($currentNPC);
    @lines = split(/(\r)|(\n)|(\r\n)/, $page);
    parsepage();
    if ($currentNPC < $ARGV[1]) {
      if ($#ARGV == 1) {
        sleep(5);
      } else {
        sleep($ARGV[2]) if ($ARGV[2] != 0);
      }
    }
  }
}