WebSVN – cheapmusic – /www/wishlistEmails.php

<?php
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

include_once ($_SERVER['DOCUMENT_ROOT'] . '/php/dnsexit.php');
include_once ($_SERVER['DOCUMENT_ROOT'] . '/php/sessions_db.php');
include_once ($_SERVER['DOCUMENT_ROOT'] . '/php/cryptor.php');
include_once ($_SERVER['DOCUMENT_ROOT'] . "/php/vendors.php");
include_once ($_SERVER['DOCUMENT_ROOT'] . '/php/tools.php');

$configFile = parse_ini_file($_SERVER['DOCUMENT_ROOT'] . FCM_CONFIGFILE, true);
$crypt = Cryptor::getInstance($configFile['cryptor']);
$tmpSessionTab = (isset($_POST["sessionTab"]) && $_POST["sessionTab"] > 0 ? $_POST["sessionTab"] : null);
$handler = MySessionHandler::getInstance($tmpSessionTab, $configFile['mysqli']);
$vendors = Vendors::getInstance();
Vendors::setAllVendors($configFile, $vendors);
$loginConfig = $configFile['login'];
unset($configFile);

ini_set("session.cookie_httponly", 1);
ini_set("session.cookie_secure", 1);
session_set_save_handler($handler, true);
if (!empty($_COOKIE['PHPSESSID'])) {
    session_id($_COOKIE['PHPSESSID']);
}
@session_start();

initSessionVariables();

wlLog("Start");
$sent = processWishlist();
wlLog("End (Sent " . $sent . " emails)");
exit (0);




function processWishlist() {
    global $wlFreqHoursArr;
    global $wlFreqArr;
    $lastUid = -1;
    $lastCount = 0;
    $totalSent = 0;
    $str = '';
    $toc = '';
    $tocFormatted = '';
    $conn = MySessionHandler::getDBSessionId();

    $sql = "SELECT w.id, w.uid, w.barcode, w.title, w.artist, w.cond, w.format, w.currency, w.price, w.checked, u.wlEmailFlag
            FROM wishlist w
            JOIN users u ON w.uid = u.id
            WHERE w.price > 0.00 and u.wlEmailFlag = '1'
            ORDER BY uid;";

    if ($result = mysqli_query($conn, $sql)) {
        if (mysqli_num_rows($result) > 0) {
            while ($wl = mysqli_fetch_assoc($result)) {
                if ($lastUid != $wl['uid']) {
                    if ($lastUid != '-1' && $lastCount > 0) {
                        wlLog("Email User (" . $user['id'] . "): TOC Count: " . $lastCount);
                        updateUserCheckedDate($user['id']);
                        ++$totalSent;
                        emailWishlistResults($user, $toc);
                        storeWishlistResults($user, $str, $tocFormatted);
                        $str = '';
                        $toc = '';
                        $tocFormatted = '';
                    }
                    $user = getUser($wl['uid']);
                    wlLog("Begin User (" . $user['id'] . "/" . $user['email'] . "/" . $wlFreqArr[$user['wlFreq']] . "): Last Check: " . ($user['wlChecked'] > 0 ? date(("Y-m-d h:i:s"), $user['wlChecked']):"Never"));
                    $_SESSION['buyer']['Zip'] = $user['zip'];
                    $lastCount = 0;
                }
                $lastUid = $wl['uid'];

                $diff = (time() - $user['wlChecked']) / 3600;
                if ($diff > $wlFreqHoursArr[$user['wlFreq']]) {
                    if (!empty($wl['barcode'])) {
                        $_SESSION['barcode']['Type'] = clsLibGTIN::GTINCheck($wl['barcode'], false, 1);
                        $_SESSION['barcode']['Value'] = clsLibGTIN::GTINCheck($wl['barcode']);
                    } else {
                        $_SESSION['barcode']['Type'] = '';
                        $_SESSION['barcode']['Value'] = '';
                    }
                    $arr = searchAll($wl['artist'] . " " . $wl['title'], true);
                    $arr = filterWishlistResults($arr, $wl);
                    updateWishlistCheckedDate($wl['id']);
                    if (!empty($arr)) {
                        ++$lastCount;
                        list($a, $b, $c) = formatResults($arr, $wl, $lastCount);
                        $str .= $a;
                        $toc .= $b;
                        $tocFormatted .= $c;
                    }
                }
            }

            if ($lastCount > 0) {
                wlLog("Email User (" . $user['id'] . "): TOC Count: " . $lastCount);
                updateUserCheckedDate($user['id']);
                ++$totalSent;
                emailWishlistResults($user, $toc);
                storeWishlistResults($user, $str, $tocFormatted);
            }

        }
    }
    else if (mysqli_errno($conn)) {
        error_log("MySQL Read Wishlist SQL: " . $sql);
        error_log("MySQL Read Wishlist Error: " . mysqli_error($conn) . " (" . mysqli_errno($conn) . ")");
    }

    return $totalSent;
}

function filterWishlistResults($arr, $wl) {
    foreach ($arr as $key => $row) {
        if ($wl['cond'] != 'Any' && $row["Condition"] != $wl['cond']) {
            unset($arr[$key]);
        }

        if ($wl['format'] != 'Any' && $row["MediaType"] != $wl['format']) {
            unset($arr[$key]);
        }

        if ($row["ConvertedTotalPrice"] > $wl['price']) {
            unset($arr[$key]);
        }
    }

    return $arr;
}

function formatResults($arr, $wl, $cnt) {
    global $mediaTypeTextArr;
    global $mediaTypeIconArr;
    $str = '';
    $toc = '';
    $tocFormatted = '';

    $toc .= "<li>" . $wl["artist"]  . " - " . $wl["title"] . " (" . count($arr) . ")</li>";
    $tocFormatted .= "<li><a style=\"color: inherit;\" href=\"#jump_" . $cnt . "\">" . $wl["artist"]  . " - " . $wl["title"] . "</a>&nbsp;<span class=\"badge badge-pill badge-dark ml-2\">" . count($arr) . "</span></li>";

    $str .= "<h3 id=\"jump_" . $cnt . "\">" . $cnt . ") " . $wl["artist"]  . " - " . $wl["title"] . "</h3>";
    $str .= "<p>Condition: " . $wl["cond"] . " | Format: " . $wl["format"]  . " | Price Cap: " . print_monetary($wl["price"], $wl["currency"]) . "</p>";
    $str .= buildTable($arr);
    $str .= "<a class=\"btn bg-white\" role=\"button\" href=\"#toc\" data-toggle=\"tooltip\" title=\"Table of Contents\" aria-label=\"Go to table of contents\"><i class=\"fas fa-level-up-alt\"></i></a>";

    return array($str, $toc, $tocFormatted);
}

function updateWishlistCheckedDate($id) {
    $nul = 'NULL';
    $conn = MySessionHandler::getDBSessionId();

    $checked = mysqli_real_escape_string($conn, time());

    $sql = "UPDATE wishlist
            SET checked = '$checked'
            WHERE id = '$id'";

    if ($result = mysqli_query($conn, $sql)) {
        return 0;
    }
    else {
        error_log("MySQL Update Wishlist SQL: " . $sql);
        error_log("MySQL Update Wishlist Error: " . mysqli_error($conn) . " (" . $error . ")");
        return -1;
    }

    return -1;
}

function getUser($uid) {
    $conn = MySessionHandler::getDBSessionId();

    $sql = "SELECT id, first_name, last_name, email, zip, wlEmailFlag, wlFreq, wlChecked
            FROM users
            WHERE id = '$uid'";

    if ($result = mysqli_query($conn, $sql)) {
        if (mysqli_num_rows($result) > 0) {
            $row = mysqli_fetch_assoc($result);
            return $row;
        }
    }
    else if (mysqli_errno($conn)) {
        error_log("MySQL Read Users SQL: " . $sql);
        error_log("MySQL Read Users Error: " . mysqli_error($conn) . " (" . mysqli_errno($conn) . ")");
    }

    return [];
}

function updateUserCheckedDate($uid) {
    $nul = 'NULL';
    $conn = MySessionHandler::getDBSessionId();

    $checked = mysqli_real_escape_string($conn, time());

    $sql = "UPDATE users
            SET wlChecked = '$checked'
            WHERE id = '$uid'";

    if ($result = mysqli_query($conn, $sql)) {
        return 0;
    }
    else {
        error_log("MySQL Update Users SQL: " . $sql);
        error_log("MySQL Update Users Error: " . mysqli_error($conn) . " (" . $error . ")");
        return -1;
    }

    return -1;
}

function PHPMailer_Init() {
    // Include PHPMailer library files
    require_once 'login/includes/PHPMailer/Exception.php';
    require_once 'login/includes/PHPMailer/PHPMailer.php';
    require_once 'login/includes/PHPMailer/SMTP.php';

    $mail = new PHPMailer;

    return $mail;
}

function emailWishlistResults($user, $toc) {
    global $loginConfig;
    global $wlFreqArr;

    $to = trim($user["email"]);
    $unsubscribeUrl = "https://" . $_SERVER["SERVER_NAME"] . "/index.php?submit=unsubscribe&amp;id=" . $user["id"] . "&amp;email=" . $to;
    $subject = "Wishlist Price Checks | " . $loginConfig['SITE_NAME'];
    $mailContent = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
                    <html xmlns="http://www.w3.org/1999/xhtml">
                    <head>
                            <title>Find Cheap Music Wishlist Price Checks</title>
                            <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
                            <meta name="viewport" content="width=device-width" />
                    </head>
                    <body>
                        <table cellpadding="0" cellspacing="0" border="0" width="100%" style="border-radius:6px;background-color:#ffffff;padding-top:15px;border-collapse:separate">
                                <tbody>
                                        <tr>
                                                <td style="color:#616471;font-weight:400;text-align:left;line-height:190%;padding-top:15px;padding-right:40px;padding-bottom:30px;padding-left:40px;font-size:15px">
                                                        <h1 style="font-weight:500;font-size:22px;letter-spacing:-1px;line-height:115%;margin:18px 0 0;padding:0;text-align:left;color:#3c7bb6">Wishlist Price Checks</h1>
                                                        <br/>
                                                        Hi ' . $user["first_name"] . ',<br/>This is your ' . strtolower($wlFreqArr[$user["wlFreq"]]) . ' wishlist price check email from <a href="https://' . $_SERVER["SERVER_NAME"] . '/index.php">Find Cheap Music</a>. The price monitor found matching listings for the following wishlist entries:.
                                                        <ol>' . $toc . '</ol>
                                                        Please login to your account at <a href="https://' . $_SERVER["SERVER_NAME"] . '/index.php">Find Cheap Music</a> and navigate to the wishlist.
                                                        <br/>We look forward to serving you,<br/><strong>' . $loginConfig['SITE_NAME'] . ' Team</strong>
                                                        <br/><p style="font-style: italic;font-weight: 300;">You subscribed to this periodic email with email address ' . $to . '. Click <a href="' . $unsubscribeUrl . '">here</a> to unsubscribe from further emails. You can reinstate the emails at any time by setting the option \'Email Price Checks\' for your account at <a href="https://' . $_SERVER["SERVER_NAME"] . '/index.php">Find Cheap Music</a> back to \'Yes\'.</p>
                                                </td>
                                        </tr>
                                </tbody>
                        </table>
                    </body>
                    </html>';

    $mailContentText = "This email contains the periodic price checks for your wishlist in HTML fornmat. Please login to your account at https://" . $_SERVER["SERVER_NAME"] . "/index.php and navigate to the wishlist.\r\n\r\n";
    $mailContentText .= "\r\nWe look forward to serving you,\r\n" . $loginConfig['SITE_NAME'] . " Team";

    if ($loginConfig['SMTP'] == true) {
        $mail = PHPMailer_Init();

        // SMTP configuration
        $mail->isSMTP();
        $mail->CharSet = "text/html; charset=UTF-8;";
        $mail->WordWrap = 80;
        $mail->Host = $loginConfig['SMTP_HOST'];
        $mail->SMTPAuth = true;
        $mail->Username = $loginConfig['SMTP_USERNAME'];
        $mail->Password = $loginConfig['SMTP_PASSWORD'];
        $mail->SMTPSecure = $loginConfig['SMTP_SECURE'];
        $mail->Port = $loginConfig['SMTP_PORT'];

        $mail->addCustomHeader("List-Unsubscribe-Post", "List-Unsubscribe=One-Click");
        $mail->addCustomHeader("List-Unsubscribe", "<" . $unsubscribeUrl . ">");

        $mail->setFrom($loginConfig['SENDER_EMAIL'], $loginConfig['SENDER_NAME']);

        $mail->addAddress($to);
        $mail->Subject = $subject;
        $mail->isHTML(true);
        $mail->Body = $mailContent;
        $mail->AltBody = $mailContentText;

        $mail->DKIM_domain = $loginConfig['DKIM_DOMAIN'];
        $mail->DKIM_private = $_SERVER['DOCUMENT_ROOT'] . $loginConfig['DKIM_PRIVATE'];
        $mail->DKIM_selector = $loginConfig['DKIM_SELECTOR'];
        $mail->DKIM_passphrase = $loginConfig['DKIM_PASSPHRASE'];
        $mail->DKIM_identity = $mail->From;
        $mail->DKIM_copyHeaderFields = false;
        $mail->DKIM_extraHeaders = ['List-Unsubscribe', 'List-Help'];

        //$mail->SMTPDebug = 2; // bugbug

        // Send email
        if (!$mail->send()) {
            error_log('Mailer error: ' . $mail->ErrorInfo);
        }
    }
    else {
        //set content-type header for sending HTML email
        $headers = "MIME-Version: 1.0" . "\r\n";
        $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
        //additional headers
        $headers .= 'From: ' . $loginConfig['SENDER_NAME'] . '<' . $loginConfig['SENDER_EMAIL'] . '>' . "\r\n";
        //send email
        mail($to, $subject, $mailContent, $headers);
    }
}

function storeWishlistResults($user, $html, $toc) {
    $conn = MySessionHandler::getDBSessionId();

    $created = mysqli_real_escape_string($conn, time());
    $str = '<h2 id="toc">Table of Contents:</h2><ol>' . $toc . '</ol><hr/>' . $html;
    //$data = mysqli_real_escape_string($conn, $str);
    $data = base64_encode(gzencode($str));
    $ip = inet_pton($_SERVER['REMOTE_ADDR']);

    $sql = "INSERT INTO pricemonitor (userId, created, ip, data)
            VALUES ('" . $user['id'] . "', $created, '$ip', '$data')
            ON DUPLICATE KEY UPDATE
            created = $created,
            data = '$data'";

    if ($result = mysqli_query($conn, $sql)) {
        return 0;
    }
    else {
        error_log("MySQL Update Users SQL: " . $sql);
        error_log("MySQL Update Users Error: " . mysqli_error($conn) . " (" . $error . ")");
        return -1;
    }

    return -1;
}

function wlLog($msg) {

    if ($fh = fopen($_SERVER['DOCUMENT_ROOT'] . FCM_WLLOGFILE, "a")) {
        $date = new DateTime();
        $date = $date->format("Y-m-d h:i:s");    
        
        fwrite($fh, $date . " " . $msg . PHP_EOL);
        
        fclose($fh);
    }
}
Subversion Repositories cheapmusic

(root)/www/wishlistEmails.php – Rev 107
