#!/usr/bin/perl
use DBI;
use Email::Send;
use Email::MIME;
use Email::MIME::CreateHTML;
use Email::MIME::Modifier;
my $dbh    = &open_db();
my $query  = "select id from nwslt where pending = '2'";
my $topsth = $dbh->prepare($query);
$topsth->execute();

while ( my $tres = $topsth->fetchrow_arrayref ) {
    print "sending newsletter " . $tres->[0] . "\n";
    my $ans = sendNewsletter( nwslid    => $tres->[0],
                              addresses => 50 );
}

sub sendNewsletter {
    my %in = @_;
    print "compose newsletter " . $in{nwslid} . "\n";
    my $nwsth = $dbh->prepare( "select * from nwslt where id=" . $in{nwslid} );
    $nwsth->execute();
    my $nwslr       = $nwsth->fetchrow_arrayref;
    my $nwslsubject = $nwslr->[2];
    my $nwslcontent = $nwslr->[3];
    my $newsletterbody = getNewsletterBody( nwslid  => $in{nwslid},
                                            content => $nwslcontent );
    my $ststh = $dbh->prepare("select value from setting where name=\'fromemail\'");
    $ststh->execute();
    my $fromemail = $ststh->fetchrow_arrayref->[0];

    #aici ne ocupam de trimitere
    #descriem un pic procesul ca sa fie foarte clar. Scriptul trimite mailuri in rafale, fiecare rafala fiind de 50, 100 de mailuri.
    #la fiecare trimitere scriptul introduce un entry in tabelul nwslsend.
    #vedem care sunt emailurile la care trimitem
    my $emsthq =
        "select newsletter.id, newsletter.email from newsletter left join nwslsend on newsletter.id=nwslsend.email and nwslsend.newsletter = "
      . $in{nwslid}
      . " where nwslsend.id is null limit 100";
    $emsth = $dbh->prepare($emsthq);
    $emsth->execute();
    while ( my $email = $emsth->fetchrow_arrayref ) {
        my $emailmsg = Email::MIME->create_html(
                                                 header => [
                                                             From    => $fromemail,
                                                             To      => $email->[1],
                                                             Subject => $nwslsubject,
                                                 ],
                                                 body  => $newsletterbody,
                                                 embed => 0,
        );
        $emailmsg->charset_set('utf8');
        eval { $ans = Email::Send->new( { mailer => 'Test' } )->send($emailmsg); };
        if ( $ans == '1' ) {
            my $sq  = "insert into nwslsend (newsletter, email, time) values (" . $in{nwslid} . "," . $email->[0] . "," . time() . ")";
            my $stw = $dbh->prepare($sq);
            $stw->execute();
        }
    }
}

sub getNewsletterBody {
    my %in      = @_;
    my $nwslid  = $in{nwslid};
    my $content = $in{content};
    print "compose newsletter $nwslid\n";
    my $tquery  = "select header, footer, oferta, pozitietext from nwslt,nltemplate where nwslt.template=nltemplate.id and nwslt.id=$nwslid";
    my $tmplsth = $dbh->prepare($tquery);
    $tmplsth->execute();
    my $tmplr       = $tmplsth->fetchrow_arrayref;
    my $header      = $tmplr->[0];
    my $footer      = $tmplr->[1];
    my $oferta      = $tmplr->[2];
    my $pozitietext = $tmplr->[3];
    my $checkof     =
      $dbh->prepare("select count(*) from oferta, nwsl_has_oferta where nwsl_has_oferta.oferta=oferta.id and nwsl_has_oferta.nwsl=$nwslid");
    $checkof->execute();
    my $smth = $checkof->fetchrow_arrayref->[0];
    my $ofertastring;

    if ( $smth > 0 ) {
        $ofertastring = getOfertaString( nwslid     => $nwslid,
                                         ofertatmpl => $oferta );
    }
    my $newsletterbody;
    if ( $pozitietext < 0 ) {
        $newsletterbody = $header . $ofertastring . $content . $footer;
    } else {
        $newsletterbody = $header . $content . $ofertastring . $footer;
    }
    return $newsletterbody;
}

sub getOfertaString {
    my %in   = @_;
    my $atof =
      $dbh->prepare( "select * from oferta, nwsl_has_oferta where nwsl_has_oferta.oferta=oferta.id and nwsl_has_oferta.nwsl=" . $in{nwslid} );
    print "select * from oferta, nwsl_has_oferta where nwsl_has_oferta.oferta=oferta.id and nwsl_has_oferta.nwsl=" . $in{nwslid} . "\n";
    $atof->execute();
    while ( my $tmplr = $atof->fetchrow_arrayref ) {
        my $ofertaid     = $tmplr->[0];
        my $ofname       = $tmplr->[2];
        my $ofrezumat    = $tmplr->[3];
        my $ofpret       = $tmplr->[4];
        my $ofdpret      = $tmplr->[6];
        my $ofinclude    = $tmplr->[7];
        my $ofnuinclude  = $tmplr->[8];
        my $ofobservatii = $tmplr->[9];
        my $ofdepart     = $tmplr->[14];
        my $ofarrival    = $tmplr->[15];
        my $fileid       = $tmplr->[24];

        #       print "FileId: $fileid\n";
        my $fileprop = $dbh->prepare("select mnode, snode, name from file where id=$fileid");
        $fileprop->execute();

        #sa vedem unde sunt fisierele
        my $row      = $fileprop->fetchrow_arrayref;
        my $snode    = $row->[1];
        my $mnode    = $row->[0];
        my $filename = $row->[2];
        my $vsthumb  = '<img src="http://www.daltravel.ro/static/uploads/' . $mnode . '/' . $snode . '/vsthumb-' . $filename . '">';
        my $sthumb   = '<img src="http://www.daltravel.ro/static/uploads/' . $mnode . '/' . $snode . '/sthumb-' . $filename . '">';
        my $thumb    = '<img src="http://www.daltravel.ro/static/uploads/' . $mnode . '/' . $snode . '/thumb-' . $filename . '">';

        #link
        my $dlink    = "http://www.daltravel.ro/oferta/view/" . $ofertaid;
        my $tnstring = $in{ofertatmpl};
        $tnstring =~ s/\{\{name\}\}/$ofname/g;
        $tnstring =~ s/\{\{pret\}\}/$ofpret/g;
        $tnstring =~ s/\{\{rezumat\}\}/$ofrezumat/g;
        $tnstring =~ s/\{\{detaliipret\}\}/$ofdpret/g;
        $tnstring =~ s/\{\{include\}\}/$ofinclude/g;
        $tnstring =~ s/\{\{nuinclude\}\}/$ofnuinclude/g;
        $tnstring =~ s/\{\{observatii\}\}/$ofobservatii/g;
        $tnstring =~ s/\{\{depart\}\}/$ofdepart/g;
        $tnstring =~ s/\{\{arrival\}\}/$ofarrival/g;
        $tnstring =~ s/\{\{vsthumb\}\}/$vsthumb/g;
        $tnstring =~ s/\{\{sthumb\}\}/$sthumb/g;
        $tnstring =~ s/\{\{thumb\}\}/$thumb/g;
        $tnstring =~ s/\{\{dlink\}\}/$dlink/g;
        $nstring .= $tnstring;
    }
    return $nstring;
}

sub open_db {
    $dbh = DBI->connect( 'DBI:mysql:dbname=travel', 'root', 'binebine', );
    return $dbh;
}