#!/usr/bin/perl -w
#
# A quick hack to pull SMS news content out of a MSSql database
# and generate an XML document which is then POST'ed to Djuice in Norway... AJM

use strict; # cos we do

require HTTP::Request;
require LWP::UserAgent;

my $url = "http://euclid.wgtn.cat-it.co.nz/~alan/xml/";

my $ua = new LWP::UserAgent;
my $request = new HTTP::Request POST => $url;

$request->content_type('application/x-www-form-urlencoded');
$request->content( SmsMessages() );

my $response = $ua->request( $request );

if ( $response->is_success ) {
	print $response->content;
} else {
	print $request->content;
	print "Bummer: failed to post!";
}


# -- Sub's below this point -- #

sub SmsMessages {

	# use DBI::ODBC - eventually...
	use MSSQL::Sqllib;

	# Local vars
	my ( $sqlstr, $string, $x, $y, @x, @y );
	my ( $id, $service, $message, $keywords, $publishdate );
	my ( $creator, $rights );

	# Log into the server. - this is MSSQL package specific at present.
	# as with the sql calls 

	sql_init("", "sa", "", "smsnews");

	# Constants - please configure here!!!
	my $NEWSTATUS = 1;

	$sqlstr =<<"END";
SELECT m.message_id as id, m.data, m.publish_date, s.service_desc as service
	FROM message m, service s, smsuser u
	WHERE m.status_id=$NEWSTATUS
	AND u.user_id=m.user_id
	AND u.service_id=s.service_id
END

	# Get available messages.
	@x = sql($sqlstr);

	# Print the XML header ...
	$string = XMLHeader();

	foreach $x (@x) {

		# Get element stuff ...

		$id 		= trim($$x{"id"});
		$service 	= trim($$x{"service"});
		$message 	= trim($$x{"data"});
		$publishdate  	= trim($$x{"publish_date"});
		$creator 	= "Telecom Mobile Limited";
		$rights		= $creator;

		# Get Message Categories.

		$sqlstr =<<"END";
SELECT c.category_desc as category 
	FROM messagecategory mc, category c 
	WHERE mc.message_id=$id
	AND mc.category_id=c.category_id
END

		@y = sql($sqlstr);

		$keywords = "";
		foreach $y (@y) {
			# Make a comma separated list.
			if ( $keywords =~ /\S/ ) { $keywords .= ", "; }
			$keywords .= trim($$y{"category"});
		}

		# Actually generate the XML body for each element.
		$string .= XMLElement( 
			   		service		=> $service,
			  		id		=> $id,
					message  	=> $message,
					keywords 	=> $keywords,
					publishdate 	=> $publishdate,
					creator 	=> $creator,
					rights 		=> $rights,
					description 	=> "",
					expiredate 	=> "",
					title 		=> "",
					subject		=> ""
					);
	} 

	$string .= XMLFooter();

	return $string;
}


sub XMLHeader {

	my $tmp = "";

	$tmp .= "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n";
	$tmp .= "<!DOCTYPE Content-list SYSTEM \"http://someserver_thiswillchange/content.dtd\">\n";
	$tmp .= "<Content-list>\n";
	$tmp .= "\n";
	return $tmp;
}

sub XMLElement {

	my %parm = @_;
	my $tmp = "";

	$tmp .= "  <Content-element serviceCode=\"" . $parm{"service"} . "\" id=\"" . $parm{"id"} . "\" language=\"en\">\n";
	$tmp .= "    <Meta>\n";
	$tmp .= "      <Title>" . 	$parm{"title"} 		. "</Title>\n";
	$tmp .= "      <Creator>" . 	$parm{"creator"} 	. "</Creator>\n";
	$tmp .= "      <Subject>" . 	$parm{"subject"} 	. "</Subject>\n";
	$tmp .= "      <Description>" . $parm{"description"} 	. "</Description>\n";
	$tmp .= "      <Rights>" . 	$parm{"rights"} 	. "</Rights>\n";
	$tmp .= "      <KeyWords>" . 	$parm{"keywords"} 	. "</KeyWords>\n";
	$tmp .= "      <PublishDate>" . $parm{"publishdate"} 	. "</PublishDate>\n";
	$tmp .= "      <ExpireDate>" . 	$parm{"expiredate"} 	. "</ExpireDate>\n";
	$tmp .= "    </Meta>\n";
	$tmp .= "    <Data>\n";
	$tmp .= "      <SmsText>" . 	$parm{"message"} 	. "</SmsText>\n";
	$tmp .= "      <WapText>" . 	$parm{"message"} 	. "</WapText>\n";
	$tmp .= "      <EmailText>" . 	$parm{"message"} 	. "</EmailText>\n";
	$tmp .= "    </Data>\n";
	$tmp .= "  </Content-element>\n";
	$tmp .= "\n";

	return $tmp;
}
	
sub XMLFooter {
	return "</Content-list>";
}

sub trim {
	$_ = shift;
	s/^\s+//;
	s/\s*$//;
	# s/\s+/ /g; # collapse internal whitespace ... no yet ;-)
	return $_;
}