#!/usr/bin/perl -w

use strict;

# http://wow.allakhazam.com/dev/wow/item-xml.pl?witem=16898

open(LOG,">>build_sql_from_xml.log");

use DBI;

my $base = "DATABASE";
my $user = "USER";
my $pass = "PASS";

my $base2 = "DATABASE2";
my $user2 = "USER2";
my $pass2 = "PASS2";

my $dbh;

$dbh = DBI->connect("DBI:mysql:$base:localhost", "$user", "$pass") or $dbh = DBI->connect("DBI:mysql:$base2:localhost", "$user2", "$pass2") or die $DBI::err.": ".$DBI::errstr;
my($query,$sth,$ref,$sth_upd);
my(@value,$line,$check,$result,$sql_updates,$tmp);

my %entries = (
"binds" => 1,
"classes" => 1,
"gemid" => 1,
"icon" => 1,
"iscrafted" => 1,
"israid" => 1,
"isvendor" => 1,
"isworld" => 1,
"itemclass" => 1,
"itemsubclass" => 1,
"level" => 1,
"minlevel" => 1,
"quality" => 1,
"races" => 1,
"recskill_amount" => 1,
"recskill_skill" => 1,
"req_spell" => 1,
"reqfaction" => 1,
"reqfactionlvl" => 1,
"reqpvprank" => 1,
"sellprice" => 1,
"slot" => 1,
"socket_1" => 1,
"socket_2" => 1,
"socket_3" => 1,
"updated" => 1,
"version" => 1
);

$query = "
	SELECT main-id, item-id, item-name, xml-is-cached, xml-cache, NOW() AS time-now
	FROM item-db
	WHERE xml-is-cached = 1
    AND item-changed <> '2007-03-18 00:00:00'
		AND item-id <> 116898
		AND xml-is-parsed <> 3
	ORDER BY zam_id ASC
	LIMIT 1000
";

$sth = $dbh->prepare($query);
$sth->execute();
while( $ref = $sth->fetchrow_hashref() ) {

	$sql_updates = "";

	@value = split( "\n", $ref->{xml-cache} );

	my $stamp = $ref->{time-now};

	foreach $line ( @value ) {
	
		$line =~ /<([_\w]+)>(.+)<\/[_\w]+>/gis;
		$check = $1;
		$result = $2;
		
		if( $check ) {
			#print "Check = $check\n";
		
			#print "Comparing $check with %entries.\n";
			
			if( exists( $entries{$check} ) ) {
		
				#print "$ref->{item-id}: $check = $result\n";

				$sql_updates .= " item-".$check."='".$result."',";
			}
		}
	}
	
	#print "$sql_add\n";
	
	if( $sql_updates ne "" ) {

		$query = sprintf("
			UPDATE item-db
			SET %s item-changed=%s, xml-is-parsed=1
			WHERE item-id=%d
		",
			$sql_updates,
			"NOW()",
			$ref->{item-id}
		);

		#print "$query\n";

		$sth_upd = $dbh->prepare($query);
		$sth_upd->execute();
		
	}
	
	$tmp = "$stamp: ".($sql_updates ne "" ? "Updated" : "Did NOT update")." fields from XML for ID $ref->{item-id} with name $ref->{item-name}.\n";
	print LOG $tmp;
	print $tmp;


}

close(LOG);

print "\nDone. Press q and Enter to quit.\n";

while( <STDIN> ) {
	if( /q/ ) {
		exit;
	}
}