# Multicraigs by Intarcorp.  Licensed under the GNU GPL
# Consult Craigslist's terms before using
# must hardcode search terms in advance below at my $url
# run this script from the command line with "perl multicraigs.pl"

my $host = "hostname";
my $database = "dbname";
my $dbuser = "dbuser";
my $dbpass = "dbpass";

//install all these libraries with cpan

use DBI;
use DBD::mysql;
use POSIX;
use XML::RSS;
use LWP::Simple;
use Data::Dumper;

my $date = strftime("%F %T",localtime);

#enter cities as comma delimited list
@city = ('seattle','portland','nashville','memphis','neworleans','orlando','kalamazoo','detroit','milwaukee','chicago','minneapolis','lacrosse','spokane','kpr','vancouver','boise','losangeles','austin','houston','dallas','atlanta','lasvegas','washingtondc','raleigh','denver','boulder','boston');

$x = 0;

foreach (@city) {

#	print @city[$x]." = city\n";
	$xcity = @city[$x];
	# enter your keywords here
	my $url = get("http://$xcity.craigslist.org/search/cpg?query=keyword&format=rss");
	# for multiple key words, use +, for instance "your+keywords"

#print $url;
print "xcity = " . $xcity . "\n";
sleep(2);
	$x++;
	my $rss = XML::RSS->new;
	$rss->parse($url);
	$entered = $rss->channel('syn')->{'updateBase'},"\n";
	print $rss->channel('title'), "\n";

#print Dumper($rss);
	print "Good Luck!\n";
	$i=1;

	$dsn = "dbi:mysql:$database:$host:3306";
	$dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Unable to connect: $DBI::errstr\n";

	my $query = "SELECT * FROM cl_linux WHERE city = '$xcity'";
        
	print "quer = ". $query . "\n";
        my $sth = $dbh->prepare($query);
        $sth->execute();
        my( $id, $title, $url, $description, $time_entered, $time_stored, $city);
        $sth->bind_columns( undef, \$id, \$title, \$url, \$description, \$time_entered, \$time_stored, \$city );
        while( $sth->fetch() ) {
		$o_ts = $time_stored;
		$o_te = $time_entered;
	}
	$sth->finish();

	foreach my $item (@{$rss->{'items'}}) {
#	print Dumper($item);
	$issued = $item->{'http://purl.org/dc/terms/'}{'issued'}."\n";
	
	$array[$i] = ("$i#69#$item->{'title'}#69#$item->{'link'}#69#$item->{'description'}#69#$item->{'http://purl.org/dc/terms/'}{'issued'}\n");
	$i++;
	}

	for ($a=1; $a <@{$rss->{'items'}}+1; $a++) {
		my($id,$title,$link,$description,$issued)=split(/#69#/,$array[$a]);
		$title =~ s/\'/^/g;
		$description =~ s/\'/^/g;
		my($first,$last)=split(T,$issued);
		my($second,$third)=split(/ /,$o_ts);
		$first =~ y/-//d;
		$second =~ y/-//d;
		$first = $first + 0; $second = $second + 0;
		if ($first gt $second) { # || $url == '') {
print "I added " . $title . " because " . $first . " is more than " . $second . " btw ...url = " . $url . "\n";
		$insert = "INSERT INTO multicraigs SET title = '$title', url = '$link', description = '$description', time_entered = '$issued', time_stored = '$date', city = '$xcity'";
$dbh->do($insert);
} else { print "already exists - ".$o_title."\n"; }

		print "done\n";
	}
}