#!/usr/bin/perl

use DBI;
use HTTP::Request  ();
use LWP::UserAgent ();

use strict;

##################################################
######################
## DATABASE CODE!!

my $dbname = 'everydevel'; ##Name of DATABASE
my $key_database_name = "node"; ##--TABLE-- name that contains all data for a node
my $database_node_id = "node_id"; ##--name of the row in Table-- that contains a given node's ID
my $database_node_title = "title"; ##--name of the row in Table-- that contains a given node's title
my $database_node_amazon_code = "amazon_code"; ##--name of row in table-- that contains all the amazon code
my $cached_database_name = "cached_start_id"; ##--TABLE-- name that contains all data for last cached node's ID
my $db_cached_stop_start = "last_id"; ##--name of the row in Table-- that contains the node ID of the last cached node

my $max_allowed_downloads = 1000; #No. of node's you want to be updated

my ($dbuser, $dbpasswd) = ('root', '');
my $dbh = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpasswd) 
	or die "can't connect: $DBI::errstr\n";

my $start_node_id = get_start_id();
my $stop_node_id = put_stop_id();


my $node_id_string = "SELECT $database_node_id FROM $key_database_name limit $start_node_id, $max_allowed_downloads";
my $sth_id = $dbh->prepare($node_id_string);
$sth_id->execute();
my $node_title_string = "SELECT $database_node_title FROM $key_database_name order by $database_node_id limit $start_node_id, $max_allowed_downloads";
my $sth_title = $dbh->prepare($node_title_string);
$sth_title->execute();



while ((my $row = $sth_id->fetchrow_arrayref) && (my $row_title = $sth_title->fetchrow_arrayref)) {
	my $call_code = get(@$row_title);
	my $query = "UPDATE $key_database_name SET $database_node_amazon_code=".$dbh->quote($call_code)." where $database_node_id='@$row'";
	my $check = $dbh->prepare($query);

	$check->execute;
	$check->finish;

}


sub get_start_id {
	my $sub_start_node_grabbed_id;
	my $sub_start_node_id = "SELECT $db_cached_stop_start FROM $cached_database_name";
	my $sub_start_node_sth_id = $dbh->prepare($sub_start_node_id);
	$sub_start_node_sth_id->execute();
	while ($sub_start_node_grabbed_id = $sub_start_node_sth_id->fetchrow_arrayref) {
		if (@$sub_start_node_grabbed_id == "") {
			my $returnable_sub_start_node_grabbed_id = $sub_start_node_grabbed_id->[0];
			my $sub_start_node_grabbed_id_insert = "0";
			my $sub_query = "UPDATE $cached_database_name SET $db_cached_stop_start='$sub_start_node_grabbed_id_insert'";
			my $sub_check = $dbh->prepare($sub_query);
			$sub_check->execute;
			$sub_check->finish;
			return $returnable_sub_start_node_grabbed_id;
		}
		else {
			my $returnable_sub_start_node_grabbed_id = $sub_start_node_grabbed_id->[0];
			return $returnable_sub_start_node_grabbed_id;
		}
	}

}

sub put_stop_id {
	my $sub_stop_node_grabbed_id;
	my $sub_start_node_grabbed_id;
	my $sub_start_node_id = "SELECT $db_cached_stop_start FROM $cached_database_name";
	my $sub_start_node_sth_id = $dbh->prepare($sub_start_node_id);
	$sub_start_node_sth_id->execute();
	while ($sub_stop_node_grabbed_id = $sub_start_node_sth_id->fetchrow_arrayref) {
		my $sub_todo_rows = get_todo_rows();
		if ($sub_todo_rows <= $max_allowed_downloads) {
			$sub_stop_node_grabbed_id = "0";
			my $sub_query = "UPDATE $cached_database_name SET $db_cached_stop_start='$sub_stop_node_grabbed_id'";
			my $sub_check = $dbh->prepare($sub_query);
			$sub_check->execute;
			$sub_check->finish;
			$sub_stop_node_grabbed_id = $sub_todo_rows;
			my $return = $sub_stop_node_grabbed_id + $sub_todo_rows;	
			return $return;	
			}
		else {
			my $sub_new_stop_node_grabbed_id;
			$sub_new_stop_node_grabbed_id =  @$sub_stop_node_grabbed_id[0] + $max_allowed_downloads;
			my $sub_query = "UPDATE $cached_database_name SET $db_cached_stop_start='$sub_new_stop_node_grabbed_id'";
			my $sub_check = $dbh->prepare($sub_query);
			$sub_check->execute;
			$sub_check->finish;
			return $sub_new_stop_node_grabbed_id;	
			}
	
		}

	}

####
#Taken out to ensure a quick response time
####
#sub get_todo_rows {
#	print "BEGIN\n";
#	my $sub_stop_node_id = $start_node_id + $max_allowed_downloads;
#	my $sub_count_rows = 0;
#	my $temp_sub_node_id_string = "SELECT $database_node_id FROM $key_database_name";
#	my $temp_sub_sth_id = $dbh->prepare($temp_sub_node_id_string);
#	$temp_sub_sth_id->execute();
#	my $temp_sub_count_rows = 0;
#	while ((my $temp_sub_row = $temp_sub_sth_id->fetchrow_arrayref)) {
#		$temp_sub_count_rows++;
	

#		my $sub_node_id_string = "SELECT $database_node_id FROM $key_database_name limit $start_node_id, $temp_sub_count_rows";
#		my $sub_sth_id = $dbh->prepare($sub_node_id_string);
#		$sub_sth_id->execute();
#		while ((my $sub_row = $sub_sth_id->fetchrow_arrayref)) {
#			$sub_count_rows++;
#		}
#	}
#	print "END\n";
#	return $sub_count_rows;
#}

sub get_todo_rows {
	my $temp_sub_node_id_string = "select count(*) from $key_database_name";
	my $temp_sub_sth_id = $dbh->prepare($temp_sub_node_id_string);
	$temp_sub_sth_id->execute();
	while (my $temp_cal = $temp_sub_sth_id->fetchrow_arrayref) {
	my $todo_rows = @$temp_cal[0] - $start_node_id;
	return $todo_rows;
	}
}


##################################################
######################
## AMAZON CODE!!

sub get {


my $node_id = @_[0]; 
my $count_links = 0; #starting point of number of links you want on your site (0) will be 1 per book,vhs,dvd,etc.
my $Associates_Id = "everything208";


## Change this code

my $xml = keywords($node_id, "books"); # or:
my $xml_music = keywords_music($node_id, "music"); 
my $xml_vhs = keywords_vhs($node_id, "vhs");
my $xml_dvd = keywords_dvd($node_id, "dvd");


my @p = parse_xml($xml);
my @q = parse_xml_music($xml_music);
my @r = parse_xml_vhs($xml_vhs);
my @s = parse_xml_dvd($xml_dvd);

##Begin Table Code
my $str.="<table bgcolor=666666 width=170><font size=\"1\">\n"."<tr>";
#$p is for books
foreach my $p (@p) {
      $str.="<td><a href=".$p->{'tagged_url'}."><img src=".$p->{'image'}."></a></td><td><font size=\"1\">".$p->{'author'}."<br>"."<a href=".$p->{'tagged_url'}.">".$p->{'title'}."</a><br><i>".$p->{'binding'}."</i><br>List:".$p->{'list_price'}."<br>Amazon:".$p->{'our_price'}."</td></tr></font>"; 
}
#$q is for music
foreach my $q (@q) {
      $str.="<tr><td><a href=".$q->{'tagged_url'}."><img src=".$q->{'small_image'}."></a></td><td><font size=\"1\">".$q->{'author'}."<br>"."<a href=".$q->{'tagged_url'}.">".$q->{'title'}."</a><br><i>".$q->{'binding'}."</i><br>List:".$q->{'list_price'}."<br>Amazon:".$q->{'our_price'}."</tr></td></font>"; 
}
#$r is for vhs
foreach my $r (@r) {
      $str.="<tr><td><a href=".$r->{'tagged_url'}."><img src=".$r->{'image'}."></a></td><td><font size=\"1\">".$r->{'author'}."<br>"."<a href=".$r->{'tagged_url'}.">".$r->{'title'}."</a><br><i>".$r->{'binding'}."</i><br>List:".$r->{'list_price'}."<br>Amazon:".$r->{'our_price'}."</tr></td></font>"; 
}

#$s is for dvd
foreach my $s (@s) {
      $str.="<tr><td><a href=".$s->{'tagged_url'}."><img src=".$s->{'image'}."></a></td><td><font size=\"1\">".$s->{'author'}."<br>"."<a href=".$s->{'tagged_url'}.">".$s->{'title'}."</a><br><i>".$s->{'binding'}."</i><br>List:".$s->{'list_price'}."<br>Amazon:".$s->{'our_price'}."</tr></td></font>"; 
}

$str.="</tr>\n</font></table>\n";


## END OF CHANGES
########################################################################





########################################################################
########################################################################
########################################################################

sub parse_xml {
    my $xml = shift;
    my @p;
    my $count = 0;
    my $count_links = 0;
    while ($xml =~ m:<product>(.*?)</product>:gs && $count_links < 1) {
	my $p = $1;
	while ($p =~ m:<(.*?)>(.*?)</\1>:gs) {
	    $p[$count]->{$1} = $2;
         }
	$count++;
        $count_links++;
    }
    	
	return @p;
}

sub parse_xml_music {
    my $xml_music = shift;
    my @q;
    my $count = 0;
    my $count_links = 0;
    while ($xml_music =~ m:<product>(.*?)</product>:gs && $count_links < 1) {
	my $q = $1;
	while ($q =~ m:<(.*?)>(.*?)</\1>:gs) {
	    $q[$count]->{$1} = $2;
         }
	$count++;
        $count_links++;
    }
    return @q;
}

sub parse_xml_vhs {
    my $xml_vhs = shift;
    my @r;
    my $count = 0;
    my $count_links = 0;
    while ($xml_vhs =~ m:<product>(.*?)</product>:gs && $count_links < 1) {
	my $r = $1;
	while ($r =~ m:<(.*?)>(.*?)</\1>:gs) {
	    $r[$count]->{$1} = $2;
         }
	$count++;
        $count_links++;
    }
    return @r;
}
sub parse_xml_dvd {

    my $xml_dvd = shift;
    my @s;
    my $count = 0;
    my $count_links = 0;
    while ($xml_dvd =~ m:<product>(.*?)</product>:gs && $count_links < 1) {
	my $s = $1;
	while ($s =~ m:<(.*?)>(.*?)</\1>:gs) {
	    $s[$count]->{$1} = $2;
         }
	$count++;
        $count_links++;
    }
    return @s;
}

sub keywords {
    my $keywords = shift;
    my $mode = shift;
    my $id = shift || $Associates_Id;
    my $url = qq{http://rcm.amazon.com/e/cm?t=$id&l=st1&search=$keywords&mode=$mode&p=102&o=1&f=xml};
    my $data = get_it($url);
    return $data;
}

sub keywords_music {
    my $keywords_music = shift;
    my $mode = shift;
    my $id = shift || $Associates_Id;
    my $url = qq{http://rcm.amazon.com/e/cm?t=$id&l=st1&search=$keywords_music&mode=$mode&p=102&o=1&f=xml};
    my $data = get_it($url);
    return $data;
}

sub keywords_vhs {
    my $keywords_vhs = shift;
    my $mode = shift;
    my $id = shift || $Associates_Id;
    my $url = qq{http://rcm.amazon.com/e/cm?t=$id&l=st1&search=$keywords_vhs&mode=$mode&p=102&o=1&f=xml};
    my $data = get_it($url);
    return $data;
}
sub keywords_dvd {
    my $keywords_dvd = shift;
    my $mode = shift;
    my $id = shift || $Associates_Id;
    my $url = qq{http://rcm.amazon.com/e/cm?t=$id&l=st1&search=$keywords_dvd&mode=$mode&p=102&o=1&f=xml};
    my $data = get_it($url);
    return $data;
}

sub get_it {
    my $url = shift     || die "Not enough parameters: get_it - url";
    my $timeout = shift || 20;
    my $result;
    my $ua = new LWP::UserAgent
	(
	 agent => "Simple Amazon XML Robo\@everything2.com",
	 keep_alive => 0,
	 timeout => $timeout);
    my $req = HTTP::Request->new('GET', "$url");
    my $res = $ua->request($req);

    if ($res->is_success) {
	$result = $res->content;
    }
    return $result;
    }
return $str;
}