Following is the script. I'm tinkering with format but no success yet. I'd like the output in the email to come out nicely formatted in columns. Using tab gets close but not quite.
#!/usr/bin/perl -w
#-------------------------------------------------
use strict;
use DBI;
use Mail::Sender;
#---------------- SQL DB setup -----------------------
my $dbh = DBI->connect('DBI:Oracle:xxxx','xxxx','xxxx')
or die "Couldn't connect to database: " . DBI->errstr;
my $sth1 = $dbh->prepare("SELECT b.suno, c.spa_nbr,b.invoice_nbr,b.po_number,b.po_line,b.po_release, b.item,
b.receipt_date, b.receipt_qty,
--d.curr_price, c.special_price,
TO_CHAR(((d.curr_price-c.special_price)* b.receipt_qty),'99999.99')
FROM UK_VI_HEADER_HISTORY a, UK_VI_LINE_HISTORY b, UK_SPA c, CP_PRICE_UK d
WHERE (a.company = b.company AND a.suno = b.suno AND a.po_number = b.po_number
AND a.invoice_nbr = b.invoice_nbr)
--AND a.issue_date = TRUNC(SYSDATE)
AND (b.suno = c.suno AND b.item = c.item)
AND (c.suno = d.suno AND c.item = d.item)
AND c.status = 'T'
AND (a.invoice_date > c.start_date AND a.invoice_date < c.end_date)
AND b.receipt_qty > c.po_qty
order by b.suno,b.po_number,b.po_line,b.po_release")
or die "Couldn't prepare statement 1: " . $dbh->errstr;
my $sth2 = $dbh->prepare("SELECT buyer_name,buyer_email FROM CP_SUPPLIER_UK
WHERE suno = ?")
or die "Couldn't prepare statement 2: " . $dbh->errstr;
my $sth3 = $dbh->prepare("SELECT user_email FROM USER_MASTER
WHERE suno = ? and cp_user in ('A','B')")
or die "Couldn't prepare statement 3: " . $dbh->errstr;
#-- Variable setup -----------------------
#---------------- sth1 variable setup -----------------------
my $suno = "";
my $spa_nbr = "";
my $invoice_nbr = "";
my $po_number = "";
my $po_line = "";
my $po_release = "";
my $item = "";
my $receipt_date = "";
my $receipt_qty = "";
my $credit_amt = "";
#---------------- sth2 variable setup -----------------------
my $buyer_name = "";
my $buyer_email = "";
#---------------- sth3 variable setup -----------------------
my $user_email = "";
#---------------- General variable setup --------------------
my @data1;
my @data2;
my @data3;
my $error_msg;
my $first_time = "Y";
my $prev_suno = "";
my $prev_po = "";
my $email = "";
my $counter = 0;
my $lees = "xxxx\@xxxx.com";
my $sender = new Mail::Sender {
smtp => 'SMTP.xxxx.com',
from => 'xxxx@xxxx.com',
on_errors => undef,
}
or die "Can't create the Mail::Sender object: $Mail::Sender::Error\n";
format line_detail =
@||||| @||||||| @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @||||||||||| @>>>>>>>>>>>> @>>>>>>>>>>>> @<<<<<<<<<<<<<<<<<<<<
$spa_nbr,$po_number,$item,$receipt_date,$receipt_qty,$credit_amt,$invoice_nbr
.
#---------- Start ---------------------
#------------------ Retrieve transaction data ---------------------
$sth1->execute()
or die "Couldn't execute SQL statement 1: " . $sth1->errstr;
while (@data1 = $sth1->fetchrow_array()) {
$suno = $data1[0];
$spa_nbr = $data1[1];
$invoice_nbr = $data1[2];
$po_number = $data1[3];
$po_line = $data1[4];
$po_release = $data1[5];
$item = $data1[6];
$receipt_date = $data1[7];
$receipt_qty = $data1[8];
$credit_amt = $data1[9];
print "$invoice_nbr"." found\n";
#---------------- Send Email -----------------------
if ($suno eq $prev_suno) {
$sender->SendLineEnc(" $spa_nbr $po_number $item\t$receipt_date\t$receipt_qty\t$credit_amt\t$invoice_nbr");
$sender->SendLineEnc("$line_detail");
}
else {
if ($first_time eq "Y") {
$first_time = "N";
$counter = $counter + 1;
}
else {
if ($email eq "") {
$email = $lees
}
$sender->SendLineEnc(" $spa_nbr $po_number $item\t$receipt_date\t$receipt_qty\t$credit_amt\t$invoice_nbr");
&email2;
$email = "";
}
#------------------ Retrieve buyer info ---------------------
#print "prev_suno="."$suno"."\n";
$sth2->execute($suno)
or die "Couldn't execute SQL statement 2: " . $sth2->errstr;
while (@data2 = $sth2->fetchrow_array()) {
$buyer_name = $data2[0];
$buyer_email = $data2[1];
}
if ($sth2->rows == 0) {
print "No buyer info found.\n\n";
}
$sth2->finish;
#------------------ Retrieve user email address(es) ---------------------
#print "prev_suno="."$suno"."\n";
$sth3->execute($suno)
or die "Couldn't execute SQL statement 3: " . $sth3->errstr;
while (@data3 = $sth3->fetchrow_array()) {
$user_email = $data3[0];
if ($email eq "") {
$email = $user_email;
} else {
$email = "$email, $user_email";
}
}
if ($sth3->rows == 0) {
print "No email found.\n\n";
}
$sth3->finish;
&email1;
$prev_suno = $suno;
}
}
if ($counter > 0) {
$sender->Close()
or die "Failed to send the message: $sender->{'error_msg'}\n";
}
if ($sth1->rows == 0) {
print "No records found.\n\n";
}
$sth1->finish;
$dbh->disconnect;
exit 0;
sub email1 {
$sender->Open({
# to => $user_email,
to => $lees,
# cc => $buyer_email,
# bcc => $lees,
subject => ' Special Pricing Agreement'
})
or die "Can't open the message: $sender->{'error_msg'}\n";
$sender->SendLineEnc("To: $email");
$sender->SendLineEnc("\ncc: $buyer_email");
$sender->SendLineEnc("\nThe following invoice lines meet the conditions of a Special Pricing Agreement and will be included in a credit request:");
$sender->SendLineEnc("\n SPA # PO # Item \tReceipt date\tReceipt qty Credit Amt\tInvoice #");
$sender->SendLineEnc(" $spa_nbr $po_number $item\t$receipt_date\t$receipt_qty\t$credit_amt\t$invoice_nbr");
}