<?php
/*
Plugin Name: Newsletter Builder 3
Description: Electronic direct marketing (EDM) software, create and send newsletters, subscribe, confirm with double opt-in, unsubscribe functionality, and web archive.  Includes incremental mailing settings for web hosts with email per hour limits.
Version: 3.04
Requires at least: 3.00
*/

// Mail limits - If your server or host requires it, you can limit mail sending speed below
$GLOBALS['BACKGROUND_SEND_MAX']     = 500; // max messages to send in each batch, eg: If a cronjob runs the script every minute and this is set to 60, you'll send approx 1800 emails an hour
$GLOBALS['BACKGROUND_SEND_WEB_MAX'] = 25;  // max messages to send when calling mailer through web interface, browser sessions can timeout so set this low (example: 25)
$GLOBALS['BACKGROUND_SEND_DELAY']   = 0;   // second to wait after sending each message

$GLOBALS['NEWSLETTER_BUILDER_PLUGIN'] = "Newsletter Builder"; // this is used to check if the plugin is installed and displayed as the plugin name
$GLOBALS['NLB_TRACK_OPENS'] = true;    // track email "opens" by adding a transparent 1x1 image to outgoing messages (industry standard method)
$GLOBALS['NLB_TRACK_LINKS'] = false;    // track email link "clicks" by rewriting email urls and redirecting them through manage script (industry standard method)

$GLOBALS['NLB_ALT_ENABLED']          = false;            // Set this to true to use an existing alternate table for subscribers
$GLOBALS['NLB_ALT_TABLE']            = 'accounts';       // alternate table for subscribers (eg: members)
$GLOBALS['NLB_ALT_FIELD_EMAIL']      = 'email';          // name of email field in alternate table
$GLOBALS['NLB_ALT_FIELD_CONFIRMED']  = '_nlb_confirmed'; // name of "subscriber" checkbox in alternate table (whether they are subscribed) 
$GLOBALS['NLB_ALT_FIELD_AUTHKEY']    = '_nlb_authkey';   // name of "subscriber" checkbox in alternate table (whether they are subscribed) 
$GLOBALS['NLB_ALT_DEFAULT_VALUES']   = array();          // new subscribers are added to alt table with these default values
$GLOBALS['NLB_ALT_NEW_SUBSCRIBER']['createdDate='] = 'NOW()';  
$GLOBALS['NLB_ALT_NEW_SUBSCRIBER']['updatedDate='] = 'NOW()';  


// DO NOT EDIT ANYTHING BELOW THIS LINE

// Schedule Task/Cronjob
//addCronJob('nlb_mailer',             "Newsletter Mailer",             '* * * * *'); // Run every minute (new mailer won't start if old previous one is still running)
addCronJob('nlb_mailer_log_cleanup', "Newsletter Mailer Log Cleanup", '0 * * * *'); // Run every hour 

// CMS Menus - Functions & Plugin Hooks
if (defined('IS_CMS_ADMIN')) { 
  require_once("newsletterBuilder_cmsMenus.php");
}

// code generator menu
addGenerator('nlb_codeGenerator',  $GLOBALS['NEWSLETTER_BUILDER_PLUGIN'], t("Create newsletter forms and viewer pages."));
function nlb_codeGenerator() { require_once("newsletterBuilder_codeGenerator.php"); nlb_codeGenerator_showPage(); exit; }

// Plugin Menu
if (defined('IS_CMS_ADMIN') && (@$_REQUEST['action'] == 'plugins' || @$_REQUEST['_pluginAction'])) { // if in CMS Plugins Menu
  require_once("newsletterBuilder_pluginsMenu.php");
  pluginAction_addHandlerAndLink('Code Generator',     'nlb_pluginMenu_redirectToCodeGenerator', 'admins');
  pluginAction_addHandlerAndLink('Mass Add Emails',    'nlb_pluginMenu_importEmails', 'admins');
  pluginAction_addHandlerAndLink('Mass Remove Emails', 'nlb_pluginMenu_removeEmails', 'admins');
  //pluginAction_addHandlerAndLink('Newsletter Mailer',  'nlb_mailer', 'admins'); // v3.01 force user to send mail through cron, it caused less confusion.
}


//
function nlb_pluginDir() { return dirname(__FILE__); }


// dispatch code for manage.php - track message opens by sending a 1x1 transparent gif
// Usage: nlb_frontend_trackOpens();
// Keys: n=subscriberNum, e=subscriberEmail, a=authkey, u=unsubscribe(bool), o=open(bool), m=messageNum, l=link (for redirecting)
function nlb_frontend_trackOpens() {
  if (!@$_REQUEST['o']) { return; }

  $ipaddress = general_getIP();
  $location = $ipaddress != "UNKNOWN" ? general_getLocation($ipaddress) : [];
  
  nlb_log(@$_REQUEST['m'], @$_REQUEST['n'], '5', "", $location);
  header('Content-Type: image/gif');
  echo base64_decode('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7'); // 1x1 transparent gif
  exit;
}


// dispatch code for manage.php - track link clicks by redirecting them through this script
// Usage: $errorsAndAlerts = nlb_frontend_dispatcher();
// Keys: n=subscriberNum, e=subscriberEmail, a=authkey, u=unsubscribe(bool), o=open(bool), m=messageNum, l=link (for redirecting)
function nlb_frontend_trackLinks() {
  if (!@$_REQUEST['l']) { return; }

  nlb_log(@$_REQUEST['m'], @$_REQUEST['n'], '6', @$_REQUEST['l']);
  redirectBrowserToURL($_REQUEST['l']);
  exit;
}

// dispatch code for manage.php - the script users use to subscribe, confirm, and unsubscribe
// Keys: n=subscriberNum, e=subscriberEmail, a=authkey, u=unsubscribe(bool), o=open(bool), m=messageNum, l=link (for redirecting)
function nlb_frontend_dispatcher3() {
  $errorsAndAlerts = '';

/*
  Display form
    - if authorized, show previous subscriptions
    - On unsubscribe, show message to uncheck any lists they don't want to receive
      - If user unchecks ALL lists, nlb_subscriber_unsubscribe() to erase their account (built in list) or uncheck "newsletter confirm" for custom accounts tables 
    - On subscribe/submit 
      - if email and email doesn't exist, create account
      - if authorized(n & a fields) AND confirmed, update database
    - else send confirmation email where link also updates database
*/
  
  // track opens and links
  nlb_frontend_trackOpens();
  nlb_frontend_trackLinks();
  
  // load authorized users (a user who clicked a link from received emails, so we know they control that email address)
  list($authUserNum, $authUserEmail, $authUserConfirmed, $errorsAndAlerts) = _nlb_frontend_loadAuthorizedUser(@$_REQUEST['n'], @$_REQUEST['a']);

  // un-authorized users, clear form values
  if (!$authUserNum) {
    $_REQUEST['n'] = '';
    $_REQUEST['a'] = '';
  }
  
  // authorized users: pre-populate form with previously selected subscriptions on first view
  if ($authUserNum && !@$_REQUEST['submitForm'] && !@$_REQUEST['lists']) {
    $_REQUEST['lists'] = array_pluck(mysql_select('_nlb_subscribers2lists', array('subscriberNum' => $authUserNum)), 'listNum');
  }

  // unsubscribe links:
  if (@$_REQUEST['u']) { 
    nlb_subscriber_unsubscribe($authUserNum);
    //$errorsAndAlerts .= t("Uncheck any newsletters you don't want to receive OR uncheck all lists to unsubscribe completely.") . "<br/>\n";
  } // unsubscribe links
  
  // confirm links - confirm any authorized users who aren't already confirmed
  if ($authUserNum && !$authUserConfirmed) {  
    $errorsAndAlerts .= t("Thank you, we've confirmed your subscription.") . "<br/>\n"; 
    nlb_log('0', $authUserNum, '2');
    nlb_subscriber_confirm($authUserNum);
  }
  
  ### process form - on request subscription (from web form)...
  if (@$_REQUEST['submitForm']) {
    $subscribedListNums = array_values((array) @$_REQUEST['lists']);

    // new subscribers - error checking
    @list($sNum, $sEmail, $sAuthkey, $sConfirmed, $sRecord) = array();
    if (!$authUserNum && array_key_exists('e', @$_REQUEST)) {
      if     (!@$_REQUEST['e'])              { $errorsAndAlerts .= t("No email address specified!") . "<br/>\n"; }
      elseif (!isValidEmail($_REQUEST['e'])) { $errorsAndAlerts .= t("Invalid email specified, email must be in the format: user@example.com") . "<br/>\n"; }
      if (!@$_REQUEST['lists'])              { $errorsAndAlerts .= t("Please select the types of messages you would like to receive.") . "<br/>\n"; }
    }
    
    // new subscribers - create or load email
    if (!$errorsAndAlerts && !$authUserNum) {
    
      // convert "Display Name" <local-part@domain> to local-part@domain
      $_REQUEST['e'] = array_value(isValidEmail($_REQUEST['e']), 0, 0);
      list($sNum, $sEmail, $sAuthkey, $sConfirmed, $sRecord) = nlb_subscriber_get(null, $_REQUEST['e']);

      // create subscriber if they don't already exist
      if (!$sNum) {
        $newRecordNum = nlb_subscriber_create($_REQUEST['e'], '0');
        list($sNum, $sEmail, $sAuthkey, $sConfirmed, $sRecord) = nlb_subscriber_get($newRecordNum);
      }
    }

    // remove old subscriptions
    $subscriberNum = coalesce($authUserNum, $sNum);
    if (!$errorsAndAlerts) {
      mysql_delete('_nlb_subscribers2lists', null, array('subscriberNum' => $subscriberNum));
    }
    
    // add new subscriptions
    if (!$errorsAndAlerts) {
      foreach ($subscribedListNums as $listNum) {
        $colsToValues = array('subscriberNum' => $subscriberNum, 'listNum' => $listNum);
        mysql_insert('_nlb_subscribers2lists', $colsToValues, true);
      }
    }
      
    // if no lists selected unsubscribe authorized user (so they don't get emails from database generated lists)
    $unsubscribedFromAll = false;
    if (!$errorsAndAlerts && !$subscribedListNums && $authUserNum) { 
      nlb_subscriber_unsubscribe($authUserNum);
      
      // for subscribe table, clear form values
      if (nlb_subscriber_table() == '_nlb_subscribers') {
        $_REQUEST = array('e' => $authUserEmail);
        $authUserNum   = '';
        $authUserEmail = '';
        $sEmail        = '';
      }

      $errorsAndAlerts .= sprintf("Thanks, we've updated your subscriptions."). "<br/>\n";          
    }

    // display message for authorized users
    if (!$errorsAndAlerts && $authUserNum) {
      $errorsAndAlerts .= sprintf("Thanks, we've updated your subscriptions."). "<br/>\n";    
    }
    
    // send confirmation to unauthorized users
    if (@$sEmail && !$errorsAndAlerts && !$authUserNum) {
      // send message & show alert
      nlb_log('0', $sNum, '1');

      $to                 = $sEmail;
      $newsletterSettings = mysql_get('_nlb_settings', 1);
      $mailErrors         = nlb_sendMessage($to, $newsletterSettings['confirm_subject'], $newsletterSettings['confirm_html'], $sRecord);
      if ($mailErrors) { die("Mail Error: $mailErrors"); }

      // send alert
      $errorsAndAlerts .= sprintf( t("Thanks, we've emailed you at %s, to confirm your subscription just check your mail and click the confirmation link."), htmlencode($to)). "<br/>\n";
      $errorsAndAlerts .= sprintf( t("If you don't receive an email from us within a few minutes check your spam filter for messages from %s"), htmlencode($newsletterSettings['from_email'])). "<br/>\n";

      $_REQUEST = array(); // clear form
    }
  } // END: on form submit...

  
  // load mailing lists
  list($lists, $listsMetaData) = getRecords(array(
    'tableName'     => '_nlb_lists',
    'where'         => "`database_query` != '1'",
    'loadUploads'   => false,
    'allowSearch'   => false,
    'loadCreatedBy' => false,
  ));
  
  //
  return array($errorsAndAlerts, $lists, $authUserNum, $authUserEmail);

}

// list($authUserNum, $authUserEmail, $authUserConfirmed) = nlb_frontend_loadAuthorizedUser(@$_REQUEST['n'], @$_REQUEST['a']);
function _nlb_frontend_loadAuthorizedUser($userNum, $authKey) {
  $errorsAndAlerts = '';
  
  // load authorized users (a user who clicked a link from received emails, so we know they control that email address)
  @list($authUserNum, $authUserEmail, $authUserAuthkey, $authUserConfirmed, $authUserRecord) = array(); // set blank vars
  if ($userNum && $authKey) { // newsletter links contain usernum(n) and authkey(a) to securely identify user
    list($authUserNum, $authUserEmail, $authUserAuthkey, $authUserConfirmed, $authUserRecord) = nlb_subscriber_get($userNum);
    if (!$authUserNum) {
      $errorsAndAlerts .= t("Sorry, we couldn't find your email in our system, try signing up again.") . "\n";
    }
    if (!$authUserAuthkey || $authUserAuthkey != $authKey) { 
      $errorsAndAlerts .= sprintf("Invalid authkey for subscriber.", $authUserNum) . "<br/>\n";
      @list($authUserNum, $authUserEmail, $authUserAuthkey, $authUserConfirmed, $authUserRecord) = array(); //reset as blank vars
    }
  }

    return array($authUserNum, $authUserEmail, $authUserConfirmed, $errorsAndAlerts);
}



// send a message and automatically insert from and replace placeholders
function nlb_sendMessage($to, $subject, $html, $subscriber = array(), $messageNum = '', $logging = true) {
  static $newsletterSettings;
  if (!isset($newsletterSettings)) { $newsletterSettings = mysql_get('_nlb_settings', 1); }

  // load and apply template
  if ($messageNum) {
    $messageRecord = mysql_get("_nlb_messages", $messageNum);
    $template      = mysql_get("_nlb_templates", $messageRecord['template']);
    if (!$template) { die(__FUNCTION__ . ": Couldn't load template " .htmlencode($messageRecord['template']). " for message " .htmlencode($messageNum)); }
    $html = str_replace('#content#', $html, $template['html']);
  }

  //
  $emailHeaders = array();
  $emailHeaders['from']    = $newsletterSettings['from_email'];//nlb_sendMessage_fromEmail($newsletterSettings);
  $emailHeaders['to']      = $to;
  $emailHeaders['subject'] = $subject;
  $emailHeaders['text']    = nlb_sendMessage_textContent($newsletterSettings);
  $emailHeaders['html']    = $html;
  $emailHeaders['logging'] = $logging;

  // set return-path for bounces
  if ($newsletterSettings['return_path']) { $emailHeaders['headers']['Return-Path'] = $newsletterSettings['return_path']; }
  else                                    { $emailHeaders['headers']['Return-Path'] = $newsletterSettings['from_email']; }
  
  // track opens - add open-tracking image to message HTML
  if ($GLOBALS['NLB_TRACK_OPENS']) {
    $emailHeaders['html'] .= "\n<img src='#manage_url#?o=1&amp;m=#newsletter.num#&amp;n=#subscriber.num#' height='1' width='1' alt='' />\n";
  }

  //Abhishek Placeholders data for new user email
  $feeds = mysql_select('feed', "`section` = 'photo_uploads' ORDER BY `action_date` DESC LIMIT 0,9");
  $foodie_moments = '';
  foreach ($feeds as $feed) {
    $record = json_decode($feed['json'], true);
    $foodie_moments .='<img  src="'.$record['photos'][0]['thumbUrlPath'].'" alt="" width="100" height="100" />';
  }
  
  $query = 'SELECT REPLACE(LOWER(cuisine)," ","-") as cuisine,count(num) as cuisine_count FROm urbanhal_listings WHERE cuisine != "" GROUP BY cuisine';
  $cuisines = mysql_select_query($query);
  // echo"<pre>";print_r($cuisines);echo"</pre>";
  $listingsCuisineCount = [];
  foreach ($cuisines as $cuisine) {
    $listingsCuisineCount[$cuisine['cuisine']] = $cuisine['cuisine_count'];
  }
  //


  // replace placeholders
  // v3.00 - Moved this code above "track link clicks" to allow for placeholders in links (#placeholders# get url-encoded by track link clicks otherwise and don't work).
  $placeholders         = nlb_placeholders($emailHeaders['to'], $messageNum, $subscriber);

  if($template['name'] == "Friday Email For Featured Menu Items"){
    $orderQuery = "SELECT um.num,um.listingsNum,um.name as menuName,uu.urlPath FROM urbanhal_menu um LEFT JOIN urbanhal_uploads uu ON uu.recordNum = um.num AND uu.tableName = 'menu' WHERE um.is_featured = 1;";
    $orderData = mysql_select_query($orderQuery);

    $object = [];
    if(count($orderData) > 0){
      foreach ($orderData as $key => $value) {
        if(array_key_exists($value['listingsNum'], $object)){
          if(count($object[$value['listingsNum']]) < 2){
            array_push($object[$value['listingsNum']], $value);
          }
        }else{
          $object[$value['listingsNum']][] = $value;
        }
      }
    }

    $products =  productsData($object);
    $unsubUrl = $GLOBALS['SETTINGS']['websiteUrl'].'/unsubscribe.php?e='.$to;
    $placeholders = array_merge([
        "username"=>$to,
        'suggested_products'=>$products,
        'unsubscribe_url' => $unsubUrl
      ], $placeholders);
  }

  //Abhishek merge listings, moments placeholders with default one
  $placeholders = array_merge(["foodie_moments" => $foodie_moments], $listingsCuisineCount, $placeholders);

  list($emailHeaders, ) = emailTemplate_replacePlaceholders($emailHeaders, $placeholders);

  // track link clicks - replace absolute links with links to a redirect script that tracks clicks
  // NOTE: As of v3.00 this WILL track ALL links added with placeholders including confirm and unsubscribe
  // NOTE: But we manually make archive_url trackable.
  if ($GLOBALS['NLB_TRACK_LINKS']) {
    $_preLinkRegexp        = "<a[^>]*?href=['\"]"; // match a href and area href
    $_postLinkRegexp       = "['\"][^>]*>";
    $absLinkRegexp         = "($_preLinkRegexp)(https?:[^'\"]*)($_postLinkRegexp)";
    
    $GLOBALS['CALLBACK_VARS'] = array('messageNum' => $messageNum, 'subscriberNum' => @$subscriber['num']); 
    $emailHeaders['html'] = preg_replace_callback("/$absLinkRegexp/i", 'nlb_sendMessage_replacelink_callback', $emailHeaders['html']);
    unset($GLOBALS['CALLBACK_VARS']);
  }

  // get css
  $defaultCSSText = "p { margin-bottom: 1em; } /* Yahoo Mail drops paragraph spacing - http://www.email-standards.org/blog/entry/yahoo-drops-paragraph-spacing/ */";
  $cssWithTags    = "<style type='text/css'>\n" .coalesce(@$template['css'], $defaultCSSText). "\n</style>\n";

  // add html headers and footer
  $htmlTitle  = htmlencode($emailHeaders['subject']);
  $htmlHeader = <<<__HEADER__
<!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>$htmlTitle</title>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
$cssWithTags
</head>
<body>

$cssWithTags


__HEADER__;


  $htmlFooter = <<<__FOOTER__
</body>
</html>
__FOOTER__;

  $emailHeaders['html'] = $htmlHeader . $emailHeaders['html'] . $htmlFooter;

  // send message
  $mailErrors = sendMessage($emailHeaders);
  return $mailErrors;
}

//
function nlb_sendMessage_replacelink_callback($matches) {
  static $newsletterSettings;
  if (!isset($newsletterSettings)) { $newsletterSettings = mysql_get('_nlb_settings', 1); }

  $output  = $matches[1]; // eg: <a href="
  $output .= $newsletterSettings['manage_url'] .'?';
  $output .= http_build_query(array(
    'l' => htmlspecialchars_decode( $matches[2] ),
    'm' => intval( $GLOBALS['CALLBACK_VARS']['messageNum'] ),
    'n' => intval(  $GLOBALS['CALLBACK_VARS']['subscriberNum']  )
  ), '', '&');
  $output .= $matches[3]; // eg: </a>
  
  return $output;
}


//
function nlb_sendMessage_fromEmail($newsletterSettings) {
  $from = '"' .$newsletterSettings['from_name'].'" <' .$newsletterSettings['from_email']. '>';
  return $from;
}

//
function nlb_sendMessage_textContent($newsletterSettings) {
  static $text;
  if (!isset($text)) { // load (and cache) text
    $text = t("To view this message, please use an HTML compatible email viewer");
    // we can't use this here as it doesn't make sense for newsletter confirmation messages
    if ($newsletterSettings['archive_url']) {
      $text .= sprintf( t("\nor click here to view the newsletter archive:\n %s"), $newsletterSettings['archive_url']);
    }
  }
  return $text;
}

// replace non-email specific placeholders in website viewers (for newsletter archive)
function nlb_viewers_replacePlaceholders($input) {

  // if input is an array iterate over keys - str_replace works on arrays, but not arrays of arrays
  if (is_array($input)) {
    foreach ($input as $key => $value) {
      $input[$key] = nlb_viewers_replacePlaceholders($input[$key]);
    }
    return $input;
  }

  // replace placeholders
  $placeholders       = nlb_placeholders();
  $searchPlaceholders = array();
  foreach (array_keys($placeholders) as $key) { $searchPlaceholders[] = "#$key#"; }
  $replacementHtml    = array_values($placeholders);
  $output             = str_replace($searchPlaceholders, $replacementHtml, $input);
  
  //
  return $output;
}


// return a list of placeholders and replacement values
// for viewer pages:           $placeholders = nlb_placeholders();
// for non-subscriber emails:  $placeholders = nlb_placeholders($to_email);
// for newsletter messages:    $placeholders = nlb_placeholders($to_email, $message['num'], $subscriber);
function nlb_placeholders($to_email = '', $messageNum = '0', $subscriber = array()) {
  $placeholders = array();

  // load/cache newsletter settings
  static $newsletterSettings;
  if (!isset($newsletterSettings)) { $newsletterSettings  = mysql_get('_nlb_settings', 1); }
  
  // define shortcut vars
  $authkeyField      = $GLOBALS['NLB_ALT_ENABLED'] ? $GLOBALS['NLB_ALT_FIELD_AUTHKEY'] : 'authkey';
  $subscriberNum     = @$subscriber['num'] ? $subscriber['num']         : '0';
  $subscriberAuthkey = @$subscriber['num'] ? $subscriber[$authkeyField] : '0';

  // define placeholders - values defined lower in the list can use placeholder defined above them in the list
  // internal placeholders (undocumented)
  $placeholders['manage_url']      = $newsletterSettings['manage_url'];

  // public placeholders - values defined lower in the list can use placeholder defined above them in the list
  $placeholders['email_header']    = ''; // placeholder removed in v2.03 - remove the placeholder if it's left in old newsletter content
  $placeholders['email_footer']    = ''; // placeholder removed in v2.03 - remove the placeholder if it's left in old newsletter content
  $placeholders['hostname']        = coalesce( @parse_url($GLOBALS['SETTINGS']['adminUrl'], PHP_URL_HOST), 'PROGRAM_URL_NOT_SET' );
  $placeholders['from_name']       = $newsletterSettings['from_name'];
  $placeholders['from_email']      = $newsletterSettings['from_email'];
  $placeholders['to_email']        = $to_email;
  $placeholders['archive_url']     = $newsletterSettings['archive_url'];
  $placeholders['confirm_url']     = $newsletterSettings['manage_url'] . "?n=$subscriberNum&a=$subscriberAuthkey";
  $placeholders['unsubscribe_url'] = $newsletterSettings['manage_url'] . "?n=$subscriberNum&a=$subscriberAuthkey&m=$messageNum&u=1"; // if usernum(n) isn't specified user will just get a debug error message


  // add subscriber.* placeholders
  static $skippedSubscriberFields = false;  // get list of subscriber fields we should exclude
  if ($skippedSubscriberFields === false) {
    $skippedSubscriberFields = array();
    $skippedFieldTypes       = array('separator','accessList','relatedRecords');
    foreach (getSchemaFields(nlb_subscriber_table()) as $fieldname => $schema) {
      $skipThisField = in_array(@$schema['type'], $skippedFieldTypes);
      if ($skipThisField) {$skippedSubscriberFields[$fieldname] = 1; }
    }
  }
  if ($subscriber) { // if subscriber array defined, use it's values
    $subscriberPlaceholders = $subscriber;
  }
  else {  // if no subscriber record, load blank subscriber fields (so #subscriber.placeholders# get replaced with empty space)
    // Future: We could implement a global array at the top and insert default values here for undefined subscriber fields, eg (firstname = "friend") so we get Hello friend, instead of "Hello ,"
    static $blankSubscriberPlaceholders = array();
    if (!$blankSubscriberPlaceholders) {
      foreach (getSchemaFields(nlb_subscriber_table()) as $fieldname => $fieldSchema) { $blankSubscriberPlaceholders[$fieldname] = ''; }
    }
    $subscriberPlaceholders = $blankSubscriberPlaceholders;
  }
  $subscriberPlaceholders = array_diff_key($subscriberPlaceholders, $skippedSubscriberFields);
  $placeholders = array_merge($placeholders, array_keys_prefix('subscriber.', $subscriberPlaceholders));
  
  
  // add newsletter.* placeholders
  static $newsletterCacheByNum = array();
  if ($messageNum && !array_key_exists($messageNum, $newsletterCacheByNum)) {
    $newsletterCacheByNum[$messageNum] = mysql_get('_nlb_messages', $messageNum);
  }
  $placeholders['newsletter.num']     = $messageNum ? @$newsletterCacheByNum[$messageNum]['num']     : ''; 
  $placeholders['newsletter.subject'] = $messageNum ? @$newsletterCacheByNum[$messageNum]['subject'] : ''; 

  // we need to replace #hostname# and other placeholders in newsletter.subject
  $searchPlaceholders = array(); 
  foreach (array_keys($placeholders) as $key) { $searchPlaceholders[] = "#$key#"; }
  $replacementHtml = array_values($placeholders);
  $placeholders['newsletter.subject'] = str_replace($searchPlaceholders, $replacementHtml, $placeholders['newsletter.subject']);  
  

  // allow users to define their own custom placeholders
  $placeholdersFile = dirname(__FILE__) . "/newsletterBuilder-custom-placeholders.php";
  if (file_exists($placeholdersFile)) { require $placeholdersFile; }

  //
  return $placeholders;
}

// write message to log.  Event nums: 1=Subscribe (unconfirmed), 2=Confirm Subscription, 3=Unsubscribe, 4=Sent Message, 5=Opened/Viewed Message, 6=Clicked Link, 7=Sent Test Message
function nlb_log($messageNum = '0', $subscriberNum = '0', $eventNum = '0', $data1 = "", $location = []) {
  if (!$eventNum) { dieAsCaller(__FUNCTION__ . ": No eventNum specified!"); }

  $colsToValues = array(
    'createdDate='  => 'NOW()',
    'messageNum'    => intval($messageNum),
    'subscriberNum' => intval($subscriberNum),
    'eventNum'      => intval($eventNum),   // 10=Subscribe, 20=Unsubscribe, 30=Sent Message, 40=Opened/Viewed Message, 50=Clicked Link
    'data1'         => mysql_escape($data1), // optional additional log data
    'city'          => @$location['geoplugin_city'],
    'state'         => @$location['geoplugin_region'],
    'latitude'      => @$location['geoplugin_latitude'],
    'longitude'     => @$location['geoplugin_longitude']
  );
  mysql_insert('_nlb_log', $colsToValues);
}


// this function is called by cron, and logged under: Admin > General > Background Tasks
// return()'d content is logged as "Summary" and and print/echo content is logged as "output"
function nlb_mailer() {
  if (!inCLI()) {
    if (!headers_sent()) { // check if headers have already be sent, such as if cron.php is being run through web
      header("Content-type: text/plain");
      header("Content-Disposition: inline; filename='output.txt'");                                              // Force IE to display as text and not download file
      ob_disable();              // Turn off browser buffering
    }
    ignore_user_abort( true ); // continue running even if user clicks stop on their browser
    set_time_limit(0);         // ignore PHP's max_execution_time directive
    session_write_close();     // v2.51 - End the current session and store session data so locked session data doesn't prevent concurrent access to CMS by user while backup in progress
  }

  // lower send max for web interface
  if (!inCLI()) {  // send headers (for running through web)  
    $GLOBALS['BACKGROUND_SEND_MAX'] = $GLOBALS['BACKGROUND_SEND_WEB_MAX'];
  }

  //
  if (!inCLI()) {
    print "Newsletter Background Mailer - Sending Queued Messages\n";
    print "--------------------------------------------------------------------------------\n";
    print "NOTE: To have this mailer run automatically setup Background Tasks under: Admin > General Settings > Background Tasks.\n";
    print "-------------------------------------------------------------------------------\n";
  }
  
  // error checking
  if (@$GLOBALS['SETTINGS']['advanced']['outgoingMail'] == 'logOnly') {
    print "-------------------------------------------------------------------------------\n";
    print "Not sending - Sending is disabled when Outgoing Mail is in 'Log Only' mode.\n";
    print "To change outgoing mail setting go to: Admin > General Settings > Email Settings.\n";
    print "-------------------------------------------------------------------------------\n";
    exit;
  }

  // Check for "Scheduled Send" messages that need to be moved into the queue
  nlb_mailer_queueScheduledMessages();

  // abort if no messages to send
  $pendingMessages = mysql_count('_nlb_queue');
  if (!$pendingMessages) { return "No messages"; }

  // send mail
  print "Sending queued 'Background Send' emails, ($pendingMessages queued";
  if ($GLOBALS['BACKGROUND_SEND_MAX'])   { print ", sending max {$GLOBALS['BACKGROUND_SEND_MAX']} per batch"; }
  if ($GLOBALS['BACKGROUND_SEND_DELAY']) { print ",{$GLOBALS['BACKGROUND_SEND_DELAY']} second pause between sends"; }
  print ")\n\n";

  //
  list($messagesSent, $sendErrors) = nlb_mailer_sendQueuedEmails();
  if ($sendErrors) { print "\nErrors: $sendErrors\n";  }

  // return summary message
  $summary = "Sent $messagesSent emails";
  if (@$_REQUEST['_pluginAction'] == __FUNCTION__) { exit; }  // exit if being run manually from web
  return $summary;  // otherwise, return summary if being run by cron
}


// erase successful log messages with no output that are 10 minutes old or more
function nlb_mailer_log_cleanup() {

  $deleteWhere = "`function` IN('nlb_mailer','nlb_mailer_log_cleanup') AND `output` = '' AND  `completed` = 1 AND `createdDate` <= (NOW() - INTERVAL 10 MINUTE)";
  mysql_delete('_cron_log', null, $deleteWhere);

}
  

// Check for "Scheduled Send" messages that need to be moved into the queue (only for messages with send dates set in future)
function nlb_mailer_queueScheduledMessages() {
  // get lock to prevent multiple operations happening at the same time
  if (!mysql_get_lock(__FUNCTION__, 2)) { die(__FUNCTION__. ": Timed out waiting for lock."); } // lock mysql

  // get scheduled send messages
  $messages = mysql_select('_nlb_messages', "`send` = 'scheduled' AND `scheduled_date` <= NOW()");

  foreach ($messages as $message) {
    // add to mail queue and mark as sent
    print "Adding messages to the queue for scheduled sent newsletter message #" .$message['num']. "\n\n";
    nlb_mailer_addToMailQueue_andMarkAsSent($message);
  }

  // release lock
  mysql_release_lock(__FUNCTION__);
}

// list($messagesSent, $sendErrors) = nlb_mailer_sendQueuedEmails();
function nlb_mailer_sendQueuedEmails() {
  $newsletterSettings  = mysql_get('_nlb_settings', 1);
  $count               = 0;
  $sendErrors          = '';
  $messagesByNum       = array();

  while (1) {
    if ($GLOBALS['BACKGROUND_SEND_MAX'] && $count >= $GLOBALS['BACKGROUND_SEND_MAX']) { break; }

    // lock table
    if (!mysql_get_lock(__FUNCTION__)) { return array(0, 'Aborting, another mail process is still running'); } // if already locked, another process is still running so abort

    // load next queue record
    $queueRecord = mysql_get('_nlb_queue', null, '1');
    if (!$queueRecord) { break; }

    // load message
    if (!array_key_exists($queueRecord['messageNum'], $messagesByNum)) {
      $messagesByNum[ $queueRecord['messageNum'] ] = mysql_get('_nlb_messages', $queueRecord['messageNum']);
    }
    $message = $messagesByNum[ $queueRecord['messageNum'] ];

    // load subscriber
    list($sNum, $sEmail, $sAuthkey, $sConfirmed, $sRecord) = nlb_subscriber_get($queueRecord['subscriberNum']);

    if ($sNum) {
      // send message
      print "Sending message to $sEmail\n";
      $logging     = false; // disable logging (large mailouts will create many records in _outgoing_mail log and require a lot of disk space)

      $sendErrors .= nlb_sendMessage($sEmail, $message['subject'], $message['html'], $sRecord, $message['num'], $logging);
      if ($sendErrors) { mysql_release_lock(__FUNCTION__); print $sendErrors; exit; }  // exit so this cron execution is marked as an error and admin is emailed

      // add to log
      nlb_log($message['num'], $sNum, '4');
    }

    // delete queue record
    mysql_delete('_nlb_queue', $queueRecord['num']);

    // unlock table
    mysql_release_lock(__FUNCTION__);

    // pause after sending
    if ($GLOBALS['BACKGROUND_SEND_DELAY']) { sleep( $GLOBALS['BACKGROUND_SEND_DELAY'] ); }

    $count++;
  }

  //
  $messagesSent = $count;
  return array($messagesSent, $sendErrors);
}


// add message to mail queue and mark it as mailed
function nlb_mailer_addToMailQueue_andMarkAsSent($message) {
  global $TABLE_PREFIX;

  // error checking
  if (!@$message)        { die(__FUNCTION__. ": No message record specified!"); }
  if (!@$message['num']) { die(__FUNCTION__. ": No message number defined!!"); }
  $messageNum = intval($message['num']); // security/sanitization: force to be integer
  $listNum    = intval(@$message['to_list']); // security/sanitization: force to be integer
  
  // get list record
  $listRecord = mysql_get('_nlb_lists', $listNum);
  if (!$listRecord) { die("Could find list record #$listNum"); }
  
  // Add to mail queue with database query
  if ($listRecord['database_query'] == 1) {
    $listQuery = getEvalOutput($listRecord['mysql_query']);
    $query  = " INSERT INTO {$TABLE_PREFIX}_nlb_queue (`messageNum`, `subscriberNum`) ";
    $query .= " SELECT $messageNum, s.num FROM ($listQuery) s";
  }
  
  // Add to mail queue using regular lookup in subscriber2lists table matching listNum
  else {
    $query  = " INSERT INTO {$TABLE_PREFIX}_nlb_queue (`messageNum`, `subscriberNum`) ";
    $query .= " SELECT $messageNum, s.num ";
    $query .= "   FROM `{$TABLE_PREFIX}" .nlb_subscriber_table(). "` s ";
    $query .= "   JOIN `{$TABLE_PREFIX}_nlb_subscribers2lists` s2l ";
    $query .= "     ON s.num = s2l.subscriberNum AND s2l.listNum = $listNum ";
    $query .= "  WHERE s.`" .nlb_subscriber_confirmedField(). "` = 1 ";
  }
  
  //
  $result = @mysqli()->query($query) or dieAsCaller("MySQL Error: ". htmlencode(mysqli()->error) . "\n");
  if (!$result) { die(__FUNCTION__. ": Couldn't insert message $messageNum into mail queue!"); }

  // get subscriber count for message (same as number of queue records inserted)
  $subscriber_count = mysqli()->affected_rows;
  
  ### Update message with send date and subscriber count
  mysql_update('_nlb_messages', $messageNum, null, array('send' => 'all', 'sent_date=' => 'NOW()', 'subscriber_count' => $subscriber_count));
}


// return name of mysql table used to store subscribers
function nlb_subscriber_table() {
  return $GLOBALS['NLB_ALT_ENABLED'] ? $GLOBALS['NLB_ALT_TABLE'] : '_nlb_subscribers';
}

// return the name of the newsletter confirmed field
function nlb_subscriber_confirmedField() { 
  return $GLOBALS['NLB_ALT_ENABLED'] ? $GLOBALS['NLB_ALT_FIELD_CONFIRMED'] : 'confirmed';
}
  
// get subscriber record by numbers of email
// list($sNum, $sEmail, $sAuthkey, $sConfirmed, $sRecord) = nlb_subscriber_get($num, $email);
function nlb_subscriber_get($num = null, $email = null) {
  
  // get newsletter fields
  $emailField     = $GLOBALS['NLB_ALT_ENABLED'] ? $GLOBALS['NLB_ALT_FIELD_EMAIL']     : 'email';
  $authkeyField   = $GLOBALS['NLB_ALT_ENABLED'] ? $GLOBALS['NLB_ALT_FIELD_AUTHKEY']   : 'authkey';

  // get record
  if ($num)       { $sRecord = mysql_get(nlb_subscriber_table(), $num); }
  elseif ($email) { $sRecord = mysql_get(nlb_subscriber_table(), null, array($emailField => $email)); }
  else            { dieAsCaller(__FUNCTION__. ": You must specify either a subscriber num or an email!"); }
  
  // add pseudo-fields for Newsletter Builder Fields
  
  // return subscriber data
  return array($sRecord['num'], $sRecord[$emailField], $sRecord[$authkeyField], $sRecord[nlb_subscriber_confirmedField()], $sRecord);
}


// create new subscriber record
// Usage: $recordNum = nlb_subscriber_create($email, $confirmed);
// list($sNum, $sEmail, $sAuthkey, $sConfirmed, $sRecord) = nlb_subscriber_get($recordNum);
function nlb_subscriber_create($email, $confirmed) {

  // generate auth key - example: 64a3fb69cc - over a trillian possible values (16**10)
  $authkey = substr( md5(mt_rand()), 0, 12 );

  // get cols to values
  if ($GLOBALS['NLB_ALT_ENABLED']) {
    $colsToValues = $GLOBALS['NLB_ALT_DEFAULT_VALUES'];
    $colsToValues[ $GLOBALS['NLB_ALT_FIELD_EMAIL']     ] = $email;
    $colsToValues[ $GLOBALS['NLB_ALT_FIELD_CONFIRMED'] ] = $confirmed;
    $colsToValues[ $GLOBALS['NLB_ALT_FIELD_AUTHKEY']   ] = $authkey;
  }
  else {
    $colsToValues = array();
    $colsToValues['email']     = $email;
    $colsToValues['authkey']   = $authkey;
    $colsToValues['confirmed'] = $confirmed;
  }
  
  // create record
  $recordNum = mysql_insert(nlb_subscriber_table(), $colsToValues, true);
  
  //
  return $recordNum;
}


// confirm subscription - checks "confirmed" checkbox indicating user has completed "double opt-in"
// nlb_subscriber_confirm($recordNum);
function nlb_subscriber_confirm($recordNum) {
  mysql_update(nlb_subscriber_table(), $recordNum, null, array(nlb_subscriber_confirmedField() => '1'));
}

// unsubscribe - remove user from subscribed database or uncheck subscription if using alternate table
// Usage: list($sNum, $sEmail, $sAuthkey, $sConfirmed, $sRecord) = nlb_subscriber_unsubscribe($recordNum, $email);
function nlb_subscriber_unsubscribe($recordNum, $email = '') {

  // get record
  list($sNum, $sEmail, $sAuthkey, $sConfirmed, $sRecord) = nlb_subscriber_get($recordNum, $email);

  //Get message user unsubscribed from.
  $messageNum = intval(@$_REQUEST['m']);

  // log all lists being unsubscribed from
  nlb_log($messageNum, $sNum, '3', $sEmail);
  
  // delete subscriptions
  mysql_delete('_nlb_subscribers2lists', null, array('subscriberNum' => $sNum));
  
  // unsubscribe or remove
  if ($GLOBALS['NLB_ALT_ENABLED']) { mysql_update(nlb_subscriber_table(), $sNum, null, array(nlb_subscriber_confirmedField() => '0')); }
  else                             { mysql_delete(nlb_subscriber_table(), $sNum); }
  
  //
  return array($sNum, $sEmail, $sAuthkey, $sConfirmed, $sRecord);
}

//
function nlb_pluginSetup_upgradeInstallAndSchemas($tableName, $action) {
  if (!preg_match("/^(_nlb_|admin|database)/", $tableName)) { return; } // only run upgrade code on plugin's menus and admin menus
  
  global $TABLE_PREFIX;
  $messagesSchema   = loadSchema('_nlb_messages');
  $nlSettingsSchema = loadSchema('_nlb_settings');
  $upgradeTo203   = $messagesSchema && !loadSchema('_nlb_templates'); // messages exists if a previous install is present and templates schema was added in 2.03
  $upgradeTo301   = $messagesSchema && !array_key_exists('return_path', $nlSettingsSchema); // Check for $nlSettingsSchema['return_path'] which was added in 3.01
  
  ### upgrade to v3.00 and/or v3.01
  if ($upgradeTo301) {

    // remove outdated schemas
    $oldSchemas = array('_nlb_mail_merge');
    foreach ($oldSchemas as $tablename) {
      @unlink(DATA_DIR."/schema/$tablename.ini.php");
      mysqli()->query("DROP TABLE IF EXISTS `{$TABLE_PREFIX}$tablename`");
    }
  
    // recreate current schemas to update them.  Note: this won't erase any of the existing mysql data
    $schemas = array('_nlb_messages','_nlb_subscribers','_nlb_lists','_nlb_templates','_nlb_settings','_nlb_log','_nlb_queue','_nlb_subscribers2lists'); // specify these in the order they should appear in menu
    foreach ($schemas as $tablename) {
      // keep track of old schema (with any user defined fields)
      $oldSchemaFields = getSchemaFields($tablename);
      
      // erase current schema      
      @unlink(DATA_DIR."/schema/$tablename.ini.php");
      @unlink(DATA_DIR."/schema/$tablename.defaultSqlData.php");
      
      // recreate schema
      plugin_createSchemas(array($tablename));
      
      // re-add user defined fields to schema (they'll still exist in MySQL)
      $userDefinedFields = array_diff_key($oldSchemaFields, getSchemaFields($tablename));
      $userDefinedFields = array_diff_key($userDefinedFields, array_flip(array('__separator014__','__separator002__','__separator015__'))); // ignore these (outdated) fields      
      saveSchema($tablename, array_merge(loadSchema($tablename), $userDefinedFields));
    }
  }
  
  
  ### on install/upgrade - create schemas
  if (!$messagesSchema) { plugin_createSchemas(array('_nlb_menugroup')); } // on first install create menugroup header (so users can remove it if they want and it won't keep getting recreated)
  $schemas = array('_nlb_messages','_nlb_subscribers','_nlb_lists','_nlb_templates','_nlb_settings','_nlb_log','_nlb_queue','_nlb_subscribers2lists'); // specify these in the order they should appear in menu
  plugin_createSchemas($schemas);

  ### v2.03 - add/update schemas
  if ($upgradeTo203) {

    // create 'custom' newsletter template with old email_header and email_footer
    $newsletterSettings  = mysql_get('_nlb_settings', 1);
    mysql_insert('_nlb_templates', array(
      'createdDate='     => 'NOW()',
      'createdByUserNum' => '0',
      'updatedDate='     => 'NOW()',
      'updatedByUserNum' => '0',
      'name'             => 'Custom',
      'html'             => @$newsletterSettings['email_header'] . "\n\n#content#\n\n" . @$newsletterSettings['email_footer'],
    ));
    // NOTE: We don't need to set the new 'template' field for previous messages because they've already been sent out and the old email_header/footer is only used on mailouts, not in the web archive

    // remove old mysql columns
    $query = "ALTER TABLE `{$TABLE_PREFIX}_nlb_settings` DROP COLUMN `email_header`, DROP COLUMN `email_footer`;";
    mysqli()->query($query) or die("MySQL Error: ". htmlencode(mysqli()->error) . "\n");
  } // END: v2.03 - add/update schemas


  ### v3.00+ - add newsletter fields to alternate table if they don't exist
  if ($GLOBALS['NLB_ALT_ENABLED'] && $GLOBALS['NLB_ALT_TABLE']) {
    $altSchema     = loadSchema($GLOBALS['NLB_ALT_TABLE']);
    if (!$altSchema) { die(__FUNCTION__. ": NLB_ALT_TABLE '" .htmlencode($GLOBALS['NLB_ALT_TABLE']). "' doesn't exist!  Developers, please update: " .htmlencode(__FILE__)); }
    
    $fieldsAdded   = 0;
    if (!@$altSchema[ $GLOBALS['NLB_ALT_FIELD_EMAIL'] ]) {
      $fieldsAdded++;
      $altSchema[$GLOBALS['NLB_ALT_FIELD_EMAIL']] = array(
        'customColumnType' => 'VARCHAR(255) NOT NULL',
        'order'           => time() + $fieldsAdded,
        'label'           => t('Newsletter Email'),
        'type'            => 'textfield',
        'defaultValue'    => '',
        'fieldPrefix'     => '',
        'description'     => '',
        'fieldWidth'      => '',
        'isPasswordField' => '0',
        'isRequired'      => '1',
        'isUnique'        => '1',
        'minLength'       => '',
        'maxLength'       => '',
        'charsetRule'     => '',
        'charset'         => '',
      );
    }
    if (!@$altSchema[ $GLOBALS['NLB_ALT_FIELD_CONFIRMED'] ]) {
      $fieldsAdded++;
      $altSchema[$GLOBALS['NLB_ALT_FIELD_CONFIRMED']] = array(
        'order'            => time() + $fieldsAdded,
        'label'            => t('Newsletter Confirmed'),
        'type'             => 'checkbox',
        'fieldPrefix'      => '',
        'checkedByDefault' => '0',
        'description'      => t('User has confirmed they want to receive newsletters'),
        'checkedValue'     => t('Yes'),
        'uncheckedValue'   => t('No'),
      );
    }
    if (!@$altSchema[ $GLOBALS['NLB_ALT_FIELD_AUTHKEY'] ]) {
      $fieldsAdded++;
      $altSchema[$GLOBALS['NLB_ALT_FIELD_AUTHKEY']] = array(
        'order'            => time() + $fieldsAdded,
        'customColumnType' => 'VARCHAR(255) NOT NULL',
        'label'            => t('Newsletter Authkey'),
        'type'             => 'textfield',
        'defaultValue'     => '',
        'fieldPrefix'      => '',
        'description'      => '',
        'fieldWidth'       => '',
        'isPasswordField'  => '0',
        'isRequired'       => '0',
        'isUnique'         => '0',
        'minLength'        => '',
        'maxLength'        => '',
        'charsetRule'      => '',
        'charset'          => '',
      );
    }
    
    if ($fieldsAdded) {
      saveSchema($GLOBALS['NLB_ALT_TABLE'], $altSchema );
      createMissingSchemaTablesAndFields();
    }
  } // END: v3.00 - add fields to alternate table if they don't exist
}

// pre v3.00 form dispatcher
// Remind version 1 & 2 users they need to upgrade their old manage/archive pages
function nlb_manage_dispatch() {
  $email = htmlencode($GLOBALS['SETTINGS']['adminEmail']);
  die("Please email us at <a href='mailto:$email'>$email</a> and let us know this page isn't working.<br/>Developers: This form is outdated, please regenerate it with the Code Generator.<br/>");
}

?>