#!/usr/bin/perl -w
use DBI;
use strict;

# get_fi_results('money');
print get_fi_results('Automotive',10, 5);


##############################################################
# usage:  $xml=get_fi_results(keyword, $max_results, $sid)
#

sub get_fi_results {
	my $database = 'advertisers';
	my $hostname = 'localhost';
	my $user = 'leadhound';
	my $password = 'new.program';

	my $keyword = $_[0];
	my $limit = $_[1] ? "LIMIT 0,".$_[1] : '';
	my $affiliate_site_id = $_[2] ? int($_[2]) : 0;

	my $dbh = DBI->connect("DBI:mysql:$database:$hostname", $user, $password, { PrintError => 1, RaiseError => 1} )
        || die ("cant connect to DataBase $database");

	my $sth = $dbh->prepare("SELECT bids.*
		FROM bids 
			LEFT JOIN advertiser_sites ON bids.site_id = advertiser_sites.site_id
			LEFT JOIN blocked_affiliate_sites as ban_local On
				(ban_local.advertiser_id=advertiser_sites.advertiser_id
				AND ban_local.affiliate_site_id=$affiliate_site_id)
			LEFT JOIN blocked_affiliate_sites as ban_global On
				(ban_global.advertiser_id=If(advertiser_sites.global_ban='Y', 0, Null)
				AND ban_global.affiliate_site_id=$affiliate_site_id) 
		WHERE bids.status = 'granted'
			AND bids.keyword = ".$dbh->quote($keyword)."
			AND bids.bid >= 0.01
			AND bids.is_susp = 'N'
			AND advertiser_sites.status = 'approved'
			AND advertiser_sites.advertiser_inactive = 'N'
			AND advertiser_sites.is_susp='N'
			AND ban_global.advertiser_id IS NULL
			AND ban_local.advertiser_id IS NULL
		ORDER BY bid DESC, bids.when_created ASC $limit");
	$sth->execute();
	my $xml="<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>\n";
	$xml .= "<response>\n";
	while (my $res = $sth->fetchrow_hashref) {
        $xml .= "<entry>\n";
        $xml .= "\t<title><![CDATA[".$res->{title}."]]></title>\n";
        $xml .= "\t<site_url><![CDATA[".$res->{url}."]]></site_url>\n";
        $xml .= "\t<description><![CDATA[".$res->{description}."]]></description>\n";
        $xml .= "\t<click_url><![CDATA[http://www.findology.com/bidclick.php?bid_id=".$res->{bid_id}."&url=".urlencode($res->{url})."]]></click_url>\n";
        $xml .= "\t<bid>".$res->{bid}."</bid>\n";
        $xml .= "</entry>\n";
	}
	$xml .= "</response>";
	return $xml;
}


sub urlencode{
	my $value=shift;
	$value =~ s/([^a-zA-Z0-9])/'%'.unpack("H*",$1)/ge;
	$value =~ tr/ /+/;
	return $value;
};