#!/usr/bin/perl

use Martian::Toolkit;
use Martian::Countries;
use DBI;

$database="leadcapture";
$hostname="localhost";
$user="root";
$password="##########";
$options="";

$tab = "\t";
$sourcenames{"..."} = "...";


$driver = "mysql";
$dsn = "DBI:$driver:database=$database;$options";
$dbh = DBI->connect($dsn, $user, $password) || die "Can't connect";

$orders = $dbh->prepare("SELECT leads.firstname as firstname,leads.lastname as lastname,leads.title as title,leads.company as company,leads.addr as addr,leads.addr2 as addr2,leads.city as city,leads.state as state,leads.postal as postal,leads.country as country,leads.sourceids as sourceids FROM leads,orders WHERE leads.leadid=orders.leadid AND orders.fulfilled=\"no\" AND orders.whatordered=?;");

open(F,">cd_orders.csv");
print F "\"First Name\",\"Last Name\",\"Title\",\"Company\",\"Address\",\"Address 2\",\"City\",\"State\",\"Postal\",\"Country\",\"Sources\"\n";
$orders->execute("CD") || die $dbh->errstr();
while(@d = $orders->fetchrow_array()) {
  @sources = split(", ", $d[10]);
  $d[9] = &country($d[9]);
  $d[10] = &parse_sources($d[10]);
  print F "\"", join("\",\"", @d), "\"\n";
}
$orders->finish();
close(F);

open(F,">paper_orders.csv");
print F "\"First Name\",\"Last Name\",\"Title\",\"Company\",\"Address\",\"Address 2\",\"City\",\"State\",\"Postal\",\"Country\",\"Sources\"\n";
$orders->execute("Paper") || die $dbh->errstr();
while(@d = $orders->fetchrow_array()) {
  $d[9] = &country($d[9]);
  $d[10] = &parse_sources($d[10]);
  print F "\"", join("\",\"", @d), "\"\n";
}
$orders->finish();
close(F);

use Mail::Sender;

ref ($sender = new Mail::Sender({from => '#####@###########',smtp => 'mail.###########'})) or die "$Mail::Sender::Error\n";

(ref ($sender->MailFile(
 {to =>'########@###########', subject => 'Lead Capture Output', bcc => '#####@###########',
  msg => "Lead Capture Data for " . localtime(),
  file => 'cd_orders.csv,paper_orders.csv'
 }))
 and print "Mail sent OK.\n"
)
or die "$Mail::Sender::Error\n";

$dbh->do("UPDATE orders SET fulfilled=\"pending\" WHERE orders.fulfilled=\"no\";");

$dbh->disconnect();

sub parse_sources {
  my($sources) = @_;
  @sources = split(", ", $sources);
  $s="";
  while(@sources) {
    $si = pop @sources;
    $s .= ", " if $s;
    $s .= $sourcenames{$si};
  }
  return $s || "Unknown/Other source";
}