#!/usr/bin/perl -Tw
################################################################################
#
# monthlysales.pl
#
# Description:  Get monthly sales from LedgerSMB
#
# Usage:        ./monthlysales.pl dbname
#
# Author:       Pete Houston (ph1@openstrike.co.uk)
# Company:      Openstrike
# Version:      $Id$
# Created:      06/05/11
# Licence:      GPL v2
#
################################################################################

use strict;
use warnings;

use lib '/usr/local/ledgersmb';
BEGIN { chdir '/usr/local/ledgersmb'; }

use LedgerSMB::Sysconfig;
use Digest::MD5;
use LedgerSMB::User;
use LedgerSMB::Form;
use LedgerSMB::Locale;
use LedgerSMB::Session;
use Data::Dumper;
require "common.pl";

# for custom preprocessing logic
eval { require "custom.pl"; };

# Retrieve the connection info from the global handle
my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH};
my ($dbu, $dbp) = ($dbh->{pg_user}, $dbh->{pg_pass});

# Retrieve the dbname from command line
my $database = $ARGV[0];
my $thisdbh = DBI->connect ("dbi:Pg:dbname=$database", $dbu, $dbp) or
	die "Cannot connect to database $database\n";
my $sql = q#SELECT SUM(amount), TO_CHAR(transdate, 'YYYY-MM') AS adate
	FROM ar
	GROUP BY adate
	ORDER BY adate#;
my $sth = $thisdbh->prepare ($sql) or warn $DBI::errstr;
$sth->execute () or warn $DBI::errstr;

my $res = '';
while (my ($sum, $date) = $sth->fetchrow_array()) {
	$res .= sprintf "%7s %10.2f\n", $date, $sum;
}
print $res;
exit;
