#!/usr/bin/perl

#################
##### Author:	Sebastian Enger 
##### eMail:	sebastian.enger@gmail.com
##### Web:		http://www.sebastian-enger.com
##### Licence:	Its only mine 8-) !
##### Purpose:	Dump yoursite.com database and generate sitemaps for the database and add them for google to index
##### Developed: For http://www.zoozle.net/ eMule, BitTorrent Download Search Engine
#################

# first do a to install needed modules: perl -MCPAN -e 'force install "WWW::Google::SiteMap"'

use WWW::Google::SiteMap;
use WWW::Google::SiteMap::Ping;
use URI::Escape qw(uri_escape);
# use POSIX qw(strftime);
use strict;
use DBI;

my $DBHOST = "localhost";
my $DBNAME = "yourdatahere";	# need to change
my $DBUSER = "yourdatahere";	
my $DBPASS = "yourdatahere";

# my $dbh	   = DBI->connect("DBI:mysql:database=$DBNAME;host=$DBHOST", "$DBUSER", "$DBPASS", {'RaiseError' => 0, AutoCommit => 0});

my $StatementHandle;
my $SiteMapFile;
my $KeyCounts;
my @data = ();
my $SiteMap;
my $data;

my $CurrentDate			= '2006-09-27';
my $StoreSiteMapPath	= '/server/apache2/htdocs/sitemaps';	# no tailing /
my $StoreSiteMapWebPath	= 'http://yoursite.com/sitemaps';		# no tailing /


my %TABLES = (
	# table				=> sitemapname
	"table7"			=> "sitemap_table7",
	"table71"			=> "sitemap_table71",
	"table711"			=> "table711",
);


GenerateSiteMaps();
sub GenerateSiteMaps(){

	my $sitemapUp = 0;

	while ( my ($key, $value) = each(%TABLES) ) {
        # print "$key => $value\n";
		
		$SiteMapFile	= "$StoreSiteMapPath/$value.$sitemapUp.xml.gz";
		$SiteMap		= WWW::Google::SiteMap->new(file => $SiteMapFile );

		print "Generating: $SiteMapFile \n";

		my $dbh	   = DBI->connect("DBI:mysql:database=$DBNAME;host=$DBHOST", "$DBUSER", "$DBPASS", {'RaiseError' => 0, AutoCommit => 0});

		# change your SQL here
		my $StatementHandle = $dbh->prepare("SELECT `DESC` FROM $key");
		$StatementHandle->execute();

		my $sth_count = $dbh->prepare("SELECT count(`DESC`) AS COUNT FROM $key");
		$sth_count->execute();
		my $KeyCounts = $sth_count->fetchrow;

		my $LinkCount = 0;
		
		while( @data = $StatementHandle->fetchrow_array()  ) {
			
			foreach my $data ( @data ) {
				
				# $data is the description of on entry
				# $key is the category

				$data =~ s/(\s)/\+/g;
				$data =~ s/\&(\w);//ig;
				# $data =~ s/\&//ig;
				$data = uri_escape($data);
				# print "$data\n";
				
				# put your mod rewrite link in loc !
				# http://yoursite.com/path/test,bittorrent,de,0.html

				$SiteMap->add({
					loc        => "http://yoursite.com/path/$data,$key,,0.html",
					lastmod    => $CurrentDate,
					changefreq => 'daily',
					priority   => 0.9, # lower priority than the home page
				});

				$LinkCount++;
				$KeyCounts--;

				if ( $LinkCount >= 49998 ) {	# change at ~ 50 000 entries
				
					# close sitemap
					$SiteMap->write;
					
					PingGoogleForSitemap("$StoreSiteMapWebPath/$value.$sitemapUp.xml.gz");
										
					# start new sitemap
					$sitemapUp++;
					
					$SiteMapFile	= "$StoreSiteMapPath/$value.$sitemapUp.xml.gz";
					$SiteMap		= WWW::Google::SiteMap->new(file => $SiteMapFile );

					print "Generating: $SiteMapFile \n";

					# put your mod rewrite link in loc !
					$SiteMap->add({
						loc        => "http://yoursite.com/path/$data,$key,,0.html",
						lastmod    => $CurrentDate,
						changefreq => 'daily',
						priority   => 0.9, # lower priority than the home page
					});

					$LinkCount = 0;

				} elsif ( $KeyCounts <= 0 ) {  # if ( $LinkCount >= 49998 ) {
		
					# close sitemap
					$SiteMap->write;
					
					PingGoogleForSitemap("$StoreSiteMapWebPath/$value.$sitemapUp.xml.gz");
		
				}; # } elsif ( $KeyCounts <= 0 ) { 

			}; # foreach my $data ( @data ) {
		}; # while( $sth->fetch() ) {

		$StatementHandle->finish();
		$dbh->disconnect();

    }; # while ( my ($key, $value) = each(%TABLES) ) {


}; # sub GenerateSiteMaps(){


sub PingGoogleForSitemap(){

	my $SiteMapUrl = shift;	

	open(WH,">>sitemapurls.txt");
		print WH "$SiteMapUrl\n";
	close WH;
  
	my $ping = WWW::Google::SiteMap::Ping->new(
		"$SiteMapUrl",
	);

	$ping->submit;
	
	foreach($ping->success) {
		print "\tSUCESS: $_ \n" if ( $_ =~ /\w/ );
	}

	foreach($ping->failure) {
		print "\t\tFAILURE $_ \n" if ( $_ =~ /\w/ );
	}

	return 1;

}; # sub PingGoogleForSitemap


exit;