#!/usr/bin/env perl
# vim:ts=8 sw=4 sts=4 ai
require v5.6.1;
use strict;
use warnings;

=head1 NAME

sqlw_mail - send mail with data from an SQLite table.

=head1 VERSION

This describes version B<0.1001> of sqlw_mail.

=cut

our $VERSION = '0.1001';

=head1 SYNOPSIS

sqlw_mail --help | --manpage | --version

sqlw_mail --database I<database_file>
( --email_col I<column> | --email_address I<address> )
[ --limit I<number> ] [ --mailer I<string> ]
{ --not_where I<colname>=1 } [ --page I<number> ] 
[ --row_template I<template> ] { --show I<colname> }
{ --sort_by I<colname> } { --sort_reversed I<colname>=1 }
[ --subject I<string> ] --table I<table>
{ --where I<colname>=I<string> }

=head1 DESCRIPTION

This sends emails; one email for each matching row from the
given table in the given database.

=head1 OPTIONS

=over

=item --email_address

An array of email addresses to send the email to.  If this is given,
this will send an email for each matching row, to each address in the array.
Useful for broadcast mailing.

Give either this option or the 'email_col' option.

=item --email_col

The name of the column to take email addresses from.  If this is given,
then each row is sent to the email address value in that column for
that row.  Useful for individual notification.

Give either this option or the 'email_address' option.

=item --database

The name of the database file to use. (required)

=item --help

Print help message and exit.

=item --index_template

Similar to the L<report_template>, but this is used for the index-pages
in multi-page and split reports.  It has the same format, but it
can be useful to have them as two separate templates as one may wish
to change the way the title is treated for indexes versus actual
reports.

=item --limit

The maximum number of rows to display per page.  If this is zero,
then all rows are displayed in one page.

=item --manpage

Print the full help documentation (manual page) and exit.

=item --not_where

A hash containing the column names where the selection criteria
in L<where> should be negated.

=item --page

Select which page to generate, if limit is not zero.

=item --row_template

The template for each row.  This uses the same format as for L<headers>.
If none is given, then a default row_template will be generated,
depending on which columns are going to be shown (see L<show>).

The format is as follows:

=over

=item {$colname}

A variable; will display the value of the column, or nothing if
that value is empty.

=item {?colname stuff [$colname] more stuff}

A conditional.  If the value of 'colname' is not empty, this will
display "stuff value-of-column more stuff"; otherwise it displays
nothing.

    {?col1 stuff [$col1] thing [$col2]}

This would use both the values of col1 and col2 if col1 is not
empty.

=item {?colname stuff [$colname] more stuff!!other stuff}

A conditional with "else".  If the value of 'colname' is not empty, this
will display "stuff value-of-column more stuff"; otherwise it displays
"other stuff".

This version can likewise use multiple columns in its display parts.

    {?col1 stuff [$col1] thing [$col2]!![$col3]}

=back

=item --show

An array of columns to select; also the order in which they should
be shown when a L<row_template> has not been given.
If this option is not used, all columns in the table will be shown.

=item --sort_by

An array of column names by which the result should be sorted.
(Repeat the argument for each new value)

=item --sort_reversed

A hash of column names where the sorting given in L<sort_by> should
be reversed.

=item --subject

A template (as for L<row_template>) of the Subject: line of the
emails.

=item --table

The table to report on. (required)

=item --verbose

Print informational messages.

=item --version

Print version information and exit.

=item --where

A hash containing selection criteria.  The keys are the column names
and the values are strings suitable for using in a LIKE condition;
that is, '%' is a multi-character wildcard, and '_' is a
single-character wildcard.  All the conditions will be ANDed together.

Yes, this is limited and doesn't use the full power of SQL, but it's
useful enough for most purposes.

=back

=head1 REQUIRES

    Getopt::Long
    Pod::Usage
    Getopt::ArgvFile
    SQLite::Work;

=head1 SEE ALSO

perl(1)
Getopt::Long
Getopt::ArgvFile
Pod::Usage

=cut

use Getopt::Long 2.34;
use Getopt::ArgvFile qw(argvFile);
use Pod::Usage;
use SQLite::Work::Mail;

#========================================================
# Subroutines

sub init_data ($) {
    my $data_ref = shift;

    $data_ref->{args} = {};
    $data_ref->{args}->{manpage} = 0;
    $data_ref->{args}->{verbose} = 0;
    $data_ref->{args}->{debug} = 0;
} # init_data

sub process_args ($) {
    my $data_ref = shift;

    my $ok = 1;

    argvFile(home=>1,
	current=>1,
	startupFilename=>'.sqlw_mailrc',
	fileOption=>'options');

    pod2usage(2) unless @ARGV;

    my $op = new Getopt::Long::Parser;
    $op->configure(qw(auto_version auto_help));
    $op->getoptions($data_ref->{args},
	       'verbose!',
	       'debug!',
	       'manpage',
	       'database=s',
	       'table=s',
	       'where=s%',
	       'not_where=s%',
	       'sort_by=s@',
	       'sort_reversed=s%',
	       'show=s@',
	       'email_address=s@',
	       'email_col=s',
	       'limit=i',
	       'page=i',
	       'row_template=s',
	       'subject=s',
	       'mailer=s',
	       'default_format=s%',
	      ) or pod2usage(2);

    if ($data_ref->{'manpage'})
    {
	pod2usage({ -message => "$0 version $VERSION",
		    -exitval => 0,
		    -verbose => 2,
	    });
    }
    # set the parameters for 'new'
    $data_ref->{new_params} = {};
    foreach my $key (qw(database))
    {
	if (exists $data_ref->{args}->{$key})
	{
	    $data_ref->{new_params}->{$key}
		= $data_ref->{args}->{$key};
	}
    }
    if (!$data_ref->{new_params}->{database})
    {
	warn "$0: no database given!\n";
	pod2usage({ -message => "$0 version $VERSION",
		    -exitval => 1,
		    -verbose => 0,
	    });
    }

    # parse the default_format argument
    if (exists $data_ref->{args}->{default_format}
	and defined $data_ref->{args}->{default_format})
    {
	$data_ref->{new_params}->{default_format} = {};
	while (my ($key, $val) = each %{$data_ref->{args}->{default_format}})
	{
	    if ($key =~ m/(\w+)\+(\w+)/)
	    {
		my $table = $1;
		my $col = $2;
		$data_ref->{new_params}->{default_format}->{$table}->{$col}
		    = $val;
	    }
	}
    }

    # set the parameters for 'report'
    $data_ref->{mail_params} = {};
    foreach my $key (qw(verbose mailer subject table where not_where sort_by sort_reversed show email_address email_col limit page row_template debug))
    {
	if (exists $data_ref->{args}->{$key})
	{
	    $data_ref->{mail_params}->{$key}
		= $data_ref->{args}->{$key};
	}
    }
    if (!$data_ref->{mail_params}->{table})
    {
	warn "$0: no table given!\n";
	pod2usage({ -message => "$0 version $VERSION",
		    -exitval => 1,
		    -verbose => 0,
	    });
    }

} # process_args

#========================================================
# Main

MAIN: {
    my %data = ();

    init_data(\%data);
    process_args(\%data);
    my $rep = SQLite::Work::Mail->new(%{$data{new_params}});
    if ($rep->do_connect())
    {
	$rep->send_email(%{$data{mail_params}});
	$rep->do_disconnect();
    }
}

=head1 BUGS

Please report any bugs or feature requests to the author.

=head1 AUTHOR

    Kathryn Andersen (RUBYKAT)
    perlkat AT katspace dot com
    http://www.katspace.com

=head1 COPYRIGHT AND LICENCE

Copyright (c) 2005 by Kathryn Andersen

This program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.


=cut

__END__