<?php
require_once('config.php');
require_once('libraries/phpmailer/PHPMailerAutoload.php');
require_once("common_cadline_libraries/crm_database.class.php");

$date = new DateTime();

$date->modify('+1 month');
$nextMonth = $date->format('Y-m');

$query = "SELECT p.prPass AS prPass, p.prHlNum AS prHlNum, p.prFizetve AS prFizetve, h.hlUser AS nUserID
    FROM cl_hlusers.h_programs p
    LEFT JOIN cl_hlusers.hardlock h ON h.hlNum = p.prHlNum
    WHERE p.prStat = 0 AND p.prOrdNum LIKE 'E-%' AND p.prFizetve LIKE '{$nextMonth}-%' AND p.prContact = 1 AND p.prStat = 0";
Database::getInstance()->query($query);
$res = Database::getInstance()->fetchAssoc();

if (!empty($res)) {
    $i = 1;

    $message = '<html><head></head><body style="font-family: Raleway, sans-serif; padding: 20px; line-height: 25px; font-size: 12pt;">';

    foreach ($res as $value) {
        $query2 = "SELECT distributors.name AS partnerName, partnerek.email AS partnerEmail, o_orders.user_name AS userName, o_orders.user_email AS userEmail,
                o_orders.country AS country
            FROM cl_hlusers.o_orders
            LEFT JOIN cl_hlusers.distributors ON distributors.ctrID = o_orders.country
            LEFT JOIN szamla_agent.partnerek ON partnerek.ország = o_orders.country
            WHERE output_key = '" . $value['prPass'] . "'";
        Database::getInstance()->query($query2);
        $res2 = Database::getInstance()->fetchNext();

        $message .= '<b>' . $i . '. ' . $res2['partnerName'] . ' (' . $res2['partnerEmail'] . ')' . ':</b> <br><br>';
        $message .= '<hr style="max-width: 500px; margin-left:0;">';
        $message .= '<div style="margin-left: 25px;">';

        if (is_numeric($value['nUserID']))
            $message .= '<b>User Name:</b> <a href="https://crm.cadline.hu/en/users/show/' . $value['nUserID'] . '">' . $res2['userName'] . '</a><br>';
        else
            $message .= '<b>User Name:</b> ' . $res2['userName'] . '<br>';

        $message .= '<b>User Email:</b> ' . $res2['userEmail'] . '<br>';
        $message .= '<b>Serial Number:</b> <a href="https://crm.cadline.hu/' . $res2['country'] . '/hardlock/show/' . $value['prHlNum'] . '">' . $value['prPass'] . '</a><br>';
        $message .= '<b>Program expiration date:</b> ' . $value['prFizetve'] . '<br><br>';
        $message .= '</div>';

        $i++;
    }

    $message .= '</body></html>';

    $mail = new PHPMailer;
    $mail->isSMTP();
    $mail->SMTPAuth = true;
    $mail->CharSet = 'UTF-8';
    $mail->Host = $mailconfig['Host'];
    $mail->Username = $mailconfig['Username'];
    $mail->Password = $mailconfig['Password'];
    $mail->SMTPSecure = $mailconfig['SMTPSecure'];
    $mail->Port = $mailconfig['Port'];
    $mail->isHTML(TRUE);
    $mail->setFrom(DEFAULT_EMAIL);
    $mail->addAddress('office@cadline.hu');
    $mail->addCC('mate.nagy@cadline.hu');
    $mail->Subject = 'Lejáró weborder előfizetések';
    $mail->msgHTML($message);
    $mail->AltBody = strip_tags($message);
    $mail->send();
}
