#!/usr/local/bin/perl

#takes a Woocommerce order export in ODS or XLS, extracts addresses,
#and emails sponsors to verify.

use warnings;
use v5.10;
use open qw/:std :utf8/;
use Spreadsheet::Read;
$book_data = ReadData ("n4sa-print-sponsors-TEST.xlsx");
$DEBUG=0;

$title="Networking for System Administrators, 2nd Edition";


if ($DEBUG) {
    say 'A1: ' . $book_data->[1]{A1};

    my @rowsingle = Spreadsheet::Read::row($book_data->[1],1);
    for my $cell (0 .. $#rowsingle)
    {
	#    say 'A' . ($cell+1).' '
	#	($rowsingle[$cell] // '');
	
	say 'A' . ($cell + 1) . ' ' . 
	    ($rowsingle[$cell] // '');
	
    }
    print "\n\n";
}



# Fetching all file content
my @rows = Spreadsheet::Read::rows($book_data->[1]);
#skip the first row, it's headers
foreach my $i (2 .. scalar @rows) 
{

    $orderNumber = ($rows[$i-1][0]);
    $accountName = ($rows[$i-1][1]);
    $email = ($rows[$i-1][2]);
    $company = ($rows[$i-1][3]);
    $shippingName = ($rows[$i-1][4]);
    $shippingCompany = ($rows[$i-1][5]);
    $address1 = ($rows[$i-1][6]);
    $address2 = ($rows[$i-1][7]);
    $city = ($rows[$i-1][8]);
    $state = ($rows[$i-1][9]);
    $postcode = ($rows[$i-1][10]);
    $countryCode = ($rows[$i-1][11]);
    $countryName  = ($rows[$i-1][12]);
    $phone = ($rows[$i-1][13]);

    if ($DEBUG) {
	say "Order Number:    " . $orderNumber;
	say "account Name:    " . $accountName;
	say "email:           " . $email;
	say "company:         " . $company;
	say "shippingName:    " . $shippingName;
	say "shippingCompany: " . $shippingCompany;
	say "address1:        " . $address1;
	say "address2:        " . $address2;
	say "city:            " . $city;
	say "state:           " . $state;
	say "postcode         " . $postcode;
	say "countryCode      " . $countryCode;
	say "countryName      " . $countryName;
	say "phone:           " . $phone;
	say "------";
    }


    

    open(MAIL, "|/usr/sbin/sendmail -t") or die "Failed to open sendmail";
    binmode(MAIL, ":utf8");
    print MAIL "To: $email\n" or die "can't print to MAIL";
    print MAIL "From: Michael W. Lucas <mwl\@mwl.io>\n";
    print MAIL "Subject: Sponsor Shipping Address Verification\n\n";
    print MAIL<<"MESSAGE";

Hello $accountName,

You're getting this message because you sponsored my book
$title in order $orderNumber
at https://tiltedwindmillpress.com.

This is an automated message, generated from your order information.

I'm almost ready to ship your sponsor gift. Since you could have moved
in the last year, I'm double-checking your address. Please let me know
if this is correct, or not.

MESSAGE


    say MAIL $shippingName;
    if ($shippingCompany) {
	say MAIL $shippingCompany
    };
    say MAIL $address1;
    if ($address2) {
	say MAIL $address2
    };
    say MAIL $city;
    if ($state) {
	say MAIL $state
    };
    say MAIL $postcode;
    say MAIL $countryCode;
    if ($phone) {
	say MAIL $phone;
    }

    say MAIL " ";
    say MAIL $countryName;
    

    unless ($phone) { print MAIL <<"PHONE";

My shipping providers (PirateShip and goshippo) are requiring phone
numbers for most packages, even within the United States. Overseas
shipments are increasingly using text messages for customs
communications. You did not include a phone number with your
order. Please give one that can receive text messages. I promise that
I won't use that number for any purpose other than shipping.

PHONE

    };

    print MAIL<<"CLOSING";
If your address and phone number are correct, do nothing.

If either or both are incorrect, please contact me with a correction
as soon as possible. I am preparing to ship books within weeks.

Thank you for your support!

==mwl
    
CLOSING

    close (MAIL);
    say "Mail sent to $email";
    
}