<?php // Newsletter Builder CMS Menu Functions

// plugin hooks
addAction('section_init',              'nlb_pluginSetup_upgradeInstallAndSchemas',  null, 2);
addAction('section_init',              'nlb_all_listMenuAlerts', null, 2);
addFilter('listHeader_displayLabel',   'nlb_createMessage_listColumns', null, 3);
addFilter('listRow_displayValue',      'nlb_createMessage_listColumns', null, 4);
addAction('record_preedit',            'nlb_createMessage_customWysiwyg', null, 2);
addAction('record_preedit',            'nlb_createMessage_preEdit', null, 2);
addFilter('userHasFieldAccess',        'nlb_createMessage_hideShowFields', null, 2);
addAction('record_save_errorchecking', 'nlb_createMessage_errorChecking', null, 4);
addAction('record_postsave',           'nlb_createMessage_postSave', null, 4);
addAction('record_save_errorchecking', 'nlb_settings_errorChecking', null, 4);
addAction('record_save_errorchecking', 'nlb_createTemplate_errorChecking', null, 4);
addAction('section_init',              'nlb_subscriberMenu_altTableAlert', null, 2);
addFilter('list_advancedCommands',     'nlb_subscriberMenu_showAdvancedCommands', null, 1);
addAction('section_unknownAction',     'nlb_subscriberMenu_execAdvancedCommands', null, 2);
addFilter('listHeader_displayLabel',   'nlb_lists_listColumns', null, 3);
addAction('section_init',              'nls_lists_recalculateCounts', null, 2);
addAction('section_init',              'nlb_lists_updateCountOnEditAndSave', null, 2);
addAction('section_init',              'nlb_lists_addAllConfirmedSubscribers', null, 2);
addAction('record_postsave',           'nlb_lists_updateMysqlEtcOnSave', null, 4);



//
function nlb_all_listMenuAlerts($tableName, $action) {
  if ($action != 'list') { return; } // skip all but list actions
  $message = "";
  
  // show warning if bgtasks not running
  if ($tableName == '_nlb_messages') { 
    $isBgTasksRunning = $GLOBALS['SETTINGS']['bgtasks_lastRun'] > strtotime('-61 minutes');
    if (!$isBgTasksRunning) { 
      $message  = t(sprintf("Warning: Please enable %sBackground Tasks%s so mail will be sent in background (no tasks have run in the last 60 minutes).", "<a href='?menu=admin&action=general#background-tasks'>", "</a>")) . "<br/>\n";
    }
  }

  // show warning for internal tables
  $internalTables = array('_nlb_subscribers2lists','_nlb_log','_nlb_queue');
  if (in_array($tableName, $internalTables)) { 
    $message = "Advanced Users Only: This is an internal program table which you can safely ignore.";
    //$message  = t(sprintf("Warning: Please enable %sBackground Tasks%s so mail will be sent in background (no tasks have run in the last 60 minutes).", "<a href='?menu=admin&action=general#background-tasks'>", "</a>")) . "<br/>\n";
  }
  
  //
  if ($message) { alert($message); }

}


//
function nlb_createMessage_listColumns($displayValue, $tableName, $fieldname, $record = array()) {
  global $TABLE_PREFIX;
  if ($tableName != '_nlb_messages') { return $displayValue; } // skip all by our table
  $isHeader = !$record; // we detect the header hook by checking if the 4th argument is set
  $isRow    = !$isHeader;

  //
  if ($fieldname == '_status_') {
    if ($isHeader) { return t("Status"); }
    if ($isRow) {
      if ($record['send'] == 'all') {
        $totalPending     = mysql_count("_nlb_queue", array('messageNum' => $record['num']));
        $totalSubscribers = number_format($record['subscriber_count']);
        $totalSent        = number_format($record['subscriber_count'] - $totalPending);

        if ($totalPending) { return sprintf( t("Sending (%s of %s)"), $totalSent, $totalSubscribers ); }
        else               { return sprintf( t("Sent (%s)"), $totalSubscribers ); }
      }
      elseif ($record['send'] == 'archive') {
        return t("Archive Only");
      }
      elseif ($record['test_date'] && $record['test_date'] != '0000-00-00 00:00:00') {
        return t("Sent to test list");
      }
      elseif ($record['send'] == 'none') {
        return t("Draft Message");
      }
      elseif ($record['send'] == 'scheduled') {
        return t("Scheduled");
      }
      else {
        return $record['send'];
      }
    }
  }

  //
  if ($fieldname == 'sent_date') {
    if ($isHeader) { return $displayValue; }
    if ($isRow && $record['send'] == 'scheduled') {
      return $record['scheduled_date'];
    }
  }


  //
  if ($fieldname == '_opens_') {
    if ($isHeader) { return t("Opens"); }
    if ($isRow) {
      if ($record['send'] != 'all') { return ''; } // only show for sent newsletters
      $where = mysql_escapef("messageNum = ? AND eventNum = ? AND subscriberNum != 0", $record['num'], 5);
      $query = "SELECT COUNT(DISTINCT(subscriberNum)) FROM {$TABLE_PREFIX}_nlb_log WHERE $where";
      list($count) = mysql_get_query($query, true);
      return number_format($count);
    }
  }

  //
  if ($fieldname == '_clickers_') {
    if ($isHeader) { return t("Clickers"); }
    if ($isRow) {
      if ($record['send'] != 'all') { return ''; } // only show for sent newsletters
      $where = mysql_escapef("messageNum = ? AND eventNum = ? AND subscriberNum != 0", $record['num'], 6);
      $query = "SELECT COUNT(DISTINCT(subscriberNum)) FROM {$TABLE_PREFIX}_nlb_log WHERE $where";
      list($count) = mysql_get_query($query, true);
      return number_format($count);
    }
  }

  //
  if ($fieldname == '_clicks_') {
    if ($isHeader) { return t("Clicks"); }
    if ($isRow) {
      if ($record['send'] != 'all') { return ''; } // only show for sent newsletters
      $where = mysql_escapef("messageNum = ? AND eventNum = ? AND subscriberNum != 0", $record['num'], 6);
      $query = "SELECT COUNT(*) FROM {$TABLE_PREFIX}_nlb_log WHERE $where";
      list($count) = mysql_get_query($query, true);
      return number_format($count);
    }
  }

  //
  if ($fieldname == '_unsubscribes_') {
    if ($isHeader) { return t("Unsubscribes"); }
    if ($isRow) {
      if ($record['send'] != 'all') { return ''; } // only show for sent newsletters
      $where = mysql_escapef("messageNum = ? AND eventNum = ? AND subscriberNum != 0", $record['num'], 3);
      $query = "SELECT COUNT(DISTINCT subscriberNum) FROM {$TABLE_PREFIX}_nlb_log WHERE $where";
      list($count) = mysql_get_query($query, true);
      return number_format($count);
    }
  }

  //
  return $displayValue; // for all other fields

}

//
function nlb_createMessage_hideShowFields($hasAccess, $fieldSchema) {
  if ($fieldSchema['_tableName'] != '_nlb_messages') { return $hasAccess; } // ignore all but this table

  // always hide these fields
  if ($fieldSchema['name'] == 'subscriber_count') { return false; } // hide field
  if ($fieldSchema['name'] == 'test_date')        { return false; } // hide field

  // "Send Message" radio button field
  if ($fieldSchema['name'] == 'send') {
    if     (@$GLOBALS['RECORD']['send'] == 'all')        { return false; } // hide if message already sent
    elseif (!@$GLOBALS['RECORD'] && !@$_REQUEST['send']) { return false; } // onsave: don't save value if field not submitted
    else { return true; } // show otherwise
  }

  // "Send Date" radio button field
  if ($fieldSchema['name'] == 'sent_date') {
    if (@$GLOBALS['RECORD']['send'] == 'all')     { return true; } // edit page values
    if (@$GLOBALS['RECORD']['send'] == 'archive') { return true; }
    if (@$_REQUEST['sent_date:mon'])              { return true; } // on save: if value submitted
    return false; // hide otherwise
  }

  // Multiple fields - show unless message has already been sent
  if (in_array($fieldSchema['name'], array('confirm_send','__separator002__','scheduled_date'))) {
    if (@$GLOBALS['RECORD']['send'] == 'all')        { return false; } //
    if (!@$GLOBALS['RECORD'] && !@$_REQUEST['send']) { return false; } //
    return true; // show otherwise
  }

  //
  return $hasAccess;
}

//
function nlb_createMessage_customWysiwyg($tableName, $recordNum) {
  if (!startsWith('_nlb_', $tableName)) { return; } // skip all tables not related to our plugin

  // force wysiwyg to save full links
  $GLOBALS['SETTINGS']['wysiwyg']['includeDomainInLinks'] = 1;

}

//
function nlb_createMessage_preEdit($tableName, $recordNum) {
  if ($tableName != '_nlb_messages') { return; } // skip all but this table

  // set defaults
  if (!$recordNum) {  // only set defaults for new records
    $newsletterSettings  = mysql_get('_nlb_settings', 1);
    $_REQUEST['subject'] = $newsletterSettings['default_subject'];
    $_REQUEST['html']    = $newsletterSettings['default_html'];
    $_REQUEST['send']    = 'none';
  }

  // all message - always uncheck confirm_send
  if ($GLOBALS['RECORD']) { // if record exists
    $GLOBALS['RECORD']['confirm_send'] = '';
  }

  //
}

//
function nlb_createMessage_errorChecking($tableName, $recordExists, $oldRecord) {
  if ($tableName != '_nlb_messages') { return; } // skip all but this table
  $newsletterSettings = mysql_get('_nlb_settings', 1);
  $errors             = "";

  // check settings
  if (!$newsletterSettings['from_name'])     { $errors .= t("Can't save, no 'From Name' specified under newsletter settings!") . "\n"; }
  if (!$newsletterSettings['from_email'])    { $errors .= t("Can't save, no 'From Email' specified under newsletter settings!") . "\n"; }
  if (!$newsletterSettings['manage_url'])    { $errors .= t("Can't save, no 'Manage URL' specified under newsletter settings!") . "\n"; }

  // check subscriber count
  if (!mysql_count(nlb_subscriber_table(), array(nlb_subscriber_confirmedField() => '1'))) { $errors .= t("Can't send, there are no confirmed newsletter subscribers!") . "\n"; }

  // check confirm_send
  $requestSend = in_array(@$_REQUEST['send'], array('all', 'test', 'scheduled'));
  if ($requestSend && !@$_REQUEST['confirm_send']) {
    $errors .= t("You must select 'Confirm Send' before sending any emails!") ."\n";
  }

  // check send rules
  $hasSentTest = @$oldRecord['test_date'] && $oldRecord['test_date'] != '0000-00-00 00:00:00';
  if (in_array(@$_REQUEST['send'], array('all','scheduled')) && !$hasSentTest) { $errors .= t("You must send to your test list before you can send to your full list!") . "\n"; }

  // prevent sent messages from being re-sent
  if (@$oldRecord['send'] == 'all' && @$_REQUEST['send']) {
     $errors .= t("Can't change send message status once message has been sent out!"). "\n";
  }

  // scheduled send
  if (@$_REQUEST['send'] == 'scheduled') {
    if (!@$_REQUEST['scheduled_date:year']) { // we only need to check year to check if the field is submitted, because partially filled out date fields generate their own errors automatically
      $errors .= t("You must specify a scheduled date to send the newsletter on!") . "\n";
    }
  }

  // show errors
  if ($errors) { die($errors); }
}



// on save, update send queue with messages to be sent
function nlb_createMessage_postSave($tableName, $isNewRecord, $oldRecord, $recordNum) {
  global $TABLE_PREFIX;
  if ($tableName != '_nlb_messages') { return; } // skip all but this table

  // lock database
  if (!mysql_get_lock(__FUNCTION__, 1)) { die(__FUNCTION__. ": Timed out waiting for lock."); } // lock mysql

  //
  $newsletterSettings = mysql_get('_nlb_settings', 1);
  $message            = mysql_get('_nlb_messages', $recordNum);

  ### save archive date
  if (@$_REQUEST['send'] == 'archive' && !@$_REQUEST['sent_date:mon']) {
    mysql_update('_nlb_messages', $recordNum, null, array('sent_date=' => 'NOW()'));
  }

  ### send to test list
  if (@$_REQUEST['send'] == 'test') {
    $testEmails = preg_split("/\s+/", $newsletterSettings['test_emails']);
    $testEmailsAsCSV = implode(', ', $testEmails);

    nlb_log($message['num'], '0', '7', $testEmailsAsCSV);

    $sendErrors = '';
    foreach ($testEmails as $to) {
      if (!isValidEmail($to)) { continue; }
      $sendErrors .= nlb_sendMessage($to, $message['subject'], $message['html'], null, $message['num']);
    }
    if ($sendErrors) { die($sendErrors); }

    // if no errors, save test_date so full mailing can be sent
    mysql_update('_nlb_messages', $recordNum, null, array('test_date=' => 'NOW()'));
  }

  ### send to full list
  if (@$_REQUEST['send'] == 'all') {

    // error checking
    if (@$oldRecord['send'] == 'all') { die("Record has already been sent to full list!"); }

    // add to mail queue and mark as sent
    nlb_mailer_addToMailQueue_andMarkAsSent($message);
  }

  // unlock database
  mysql_release_lock(__FUNCTION__); // unlock mysql
}


//
function nlb_settings_errorChecking($tableName, $recordExists, $oldRecord) {
  if ($tableName != '_nlb_settings') { return; } // skip all but this table

  // error checking
  $emailOnly = array_value(isValidEmail(@$_REQUEST['from_email']), 0, 0); // get user@example.com from "User Name" <user@example.com>
  $errors = '';
  $testEmailsAsCSV = implode(',', array_filter(preg_split("/\s+/", @$_REQUEST['test_emails'])));
  if     (!@$_REQUEST['from_name'])                { $errors .= t("'From Name' is required") . "\n"; }
  elseif (isValidEmail(@$_REQUEST['from_name']))   { $errors .= t("'From Name' must be a name, not an email address.") . "\n"; }
  if     (!@$_REQUEST['from_email'])               { $errors .= t("'From Email' is required") . "\n"; }
  elseif ($_REQUEST['from_email'] != $emailOnly)   { $errors .= t("'From Email' must be the email address only (eg: user@example.com)") . "\n"; }
  if     (!@$_REQUEST['test_emails'])              { $errors .= t("'Test Emails' is required") . "\n"; }
  elseif (!isValidEmail($testEmailsAsCSV, true))   { $errors .= t("There are no valid emails in 'Test Emails'") . "\n"; }
  if ($errors) { die($errors); }

}


//
function nlb_createTemplate_errorChecking($tableName, $recordExists, $oldRecord) {
  if ($tableName != "_nlb_templates")  { return; }

  $contentPlaceholderCount = substr_count(@$_REQUEST['html'], "#content#");

  if (!$contentPlaceholderCount)    { die(t("You must use the placeholder #content# in your template")); }
  if ($contentPlaceholderCount > 1) { die(t("You can only use the placeholder #content# once in your template")); }
}

//
function nlb_subscriberMenu_altTableAlert($tableName, $action) {
  if ($tableName != '_nlb_subscribers') { return; } // skip all but this table
  if (!$GLOBALS['NLB_ALT_ENABLED'])     { return; } // only show if alternate tables in use
  
  $tableLinkHTML = "<a href='?menu={$GLOBALS['NLB_ALT_TABLE']}'>{$GLOBALS['NLB_ALT_TABLE']}</a>";
  $message = sprintf(t("Subscribers are stored in the %s menu."), $tableLinkHTML); 
  showInterfaceError($message);
  exit; 
}


//
function nlb_subscriberMenu_showAdvancedCommands($labelsToValues) {
  if ($GLOBALS['tableName'] != nlb_subscriber_table()) { return $labelsToValues; } // only show for subscriber table

  $labelsToValues['Create Mailing List (from Search Results)']  = 'nlb_createSubscriberGroup';
  return $labelsToValues;
}

//
function nlb_subscriberMenu_execAdvancedCommands($tableName, $action) {
  if ($GLOBALS['tableName'] != nlb_subscriber_table()) { return; } // only show for subscriber table
  if ($action != 'nlb_createSubscriberGroup') { return; } // only show when option selected

  // get search terms
  $ignoreKeys = array('menu','page','perPage','showAdvancedSearch','_defaultAction','_unused','_tableName','_advancedAction','_advancedActionSubmit');
  $searchTerms = array();
  foreach ($_REQUEST as $key => $value) {
    if (in_array($key, $ignoreKeys)) { continue; }
    if ($value === '') { continue; }
    $searchTerms[$key] = $value;
  }
  $searchQuery = http_build_query($searchTerms, null, '&');
  $searchQuery = str_replace('%2C', ',', $searchQuery); // replace %2C with , for readability (comment this line if this causes problems)
  
  // redirect to "Subscriber Group" add page
  $redirectUrl = "?menu=_nlb_lists&action=add&database_query=1&search_query=" . urlencode($searchQuery);
  redirectBrowserToURL($redirectUrl);

exit;
}



// show recalculate link in header
function nlb_lists_listColumns($displayValue, $tableName, $fieldname, $record = array()) {
  global $TABLE_PREFIX;
  if ($tableName != '_nlb_lists') { return $displayValue; } // skip all by our table
  $isHeader = !$record; // we detect the header hook by checking if the 4th argument is set
  $isRow    = !$isHeader;

  //
  if ($fieldname == 'subscriber_count') {
    if ($isHeader) {
      $displayValue .= " (<a href='?menu=$tableName&amp;recalculate=1'>" .t('recalculate'). "</a>)";
    }
    if ($isRow) { 
    }
  }

  //
  return $displayValue; // for all other fields

}


// update mysql field for non-mysql mailing lists so we can always rely on using the MySQL only
function nlb_lists_updateMysqlEtcOnSave($tableName, $isNewRecord, $oldRecord, $recordNum) {
  if ($tableName != '_nlb_lists') { return; }    // skip all but this table
  $listRecord  = mysql_get('_nlb_lists', $recordNum); // get list record
  
  ### regular lists
  if     (!$listRecord['database_query']) {
    $listRecord['mysql_query'] = trim("
SELECT s.num
  FROM `<?php echo \$TABLE_PREFIX . nlb_subscriber_table() ?>` s
  JOIN `<?php echo \$TABLE_PREFIX ?>_nlb_subscribers2lists` s2l
    ON s2l.subscriberNum = s.num
 WHERE s.`<?php echo nlb_subscriber_confirmedField() ?>` = 1 AND s2l.listNum = {$listRecord['num']}");
    mysql_update('_nlb_lists', $recordNum, null, array('mysql_query' => $listRecord['mysql_query']));
  }
  
  
  ### search query lists
  elseif ($listRecord['database_query'] && $listRecord['search_query']) {
    // get mysql where from searchQuery - wrapper for internal viewer_functions.php function:
    $oldRequest = $_REQUEST;                                                // save old $_REQUEST superglobal
    parse_str($listRecord['search_query'], $_REQUEST);                                     // set $_REQUEST with values from search query
    require_once SCRIPT_DIR . "/lib/viewer_functions.php";
    $where      = _createDefaultWhereWithFormInput(loadSchema(nlb_subscriber_table()), '', array());
    $_REQUEST   = $oldRequest;                                              // restore $_REQUEST superglobal

    //
    $listRecord['mysql_query'] = trim("
SELECT s.num FROM `<?php echo \$TABLE_PREFIX . nlb_subscriber_table() ?>` s
 WHERE s.`<?php echo nlb_subscriber_confirmedField() ?>` = 1
   AND $where");
    mysql_update('_nlb_lists', $recordNum, null, array('mysql_query' => $listRecord['mysql_query']));
  }

  ### mysql lists
  elseif ($listRecord['database_query']) {
    // nothing required
  }
  
  
  ### all others... 
  else {
    die(__FUNCTION__ . ": Unknown list type '" .htmlencode($listRecord['type']). "'!"); 
  }
    
  ### error checking
  
  // check for mysql errors
  $query = getEvalOutput( $listRecord['mysql_query'] );
  // $result = @mysql_query($query); //old function
  //Abhishek new mysqli function
  $result = @mysqli()->query($query);
  if ($result === false) { die("MySQL Error: ". htmlencode(mysqli()->error) . "\n"); }
  else                   { mysqli_free_result($result); }

  // check for confirmation field:
  $confirmedField = nlb_subscriber_confirmedField();
  $regexp = preg_quote( $confirmedField, '/');
  if (!preg_match("/$regexp/", $query)) {
    die("MySQL query must reference column '" .htmlencode($confirmedField). "' and filter results on it.\n$query"); 
  }
}


// Update subscriber count for specified list 
function nls_lists_updateCount($listNum) {
  if (!$listNum) { return; }

  // load list
  $listRecord = mysql_get('_nlb_lists', $listNum);
  if (!$listRecord)                 { die("List record #" .htmlencode($listNum). " not found!"); }
  if (!@$listRecord['mysql_query']) { die("List record #" .htmlencode($listNum). " has no mysql_query defined!"); }
  
  // get subscriber_count
  $subscriberCount = 0;
  $subquery        = getEvalOutput($listRecord['mysql_query']);
  $query           = "SELECT COUNT(*) FROM ($subquery) as temp";
  // $result          = @mysql_query($query);      // manual query instead of mysql_get_query() type functions so we can catch MySQL errors in queries (such as when referenced columns change)
  // if ($result) { // if mysql_query returns error, this will leave $subscriberCount set to 0
  //   $subscriberCount = mysql_result($result, 0, 0);
  //   if (is_resource($result)) { mysql_free_result($result); }
  // }

  //Abhishek new mysqli function
  $result          = @mysqli()->query($query);      // manual query instead of mysql_get_query() type functions so we can catch MySQL errors in queries (such as when referenced columns change)
  if ($result) { // if mysql_query returns error, this will leave $subscriberCount set to 0
    mysqli_data_seek($result, 0);
    $subscriberCount = mysqli_fetch_row($result)[0];
    if (is_resource($result)) { mysqli_free_result($result); }
  }
  
  // update list data
  $colsToValues = array();
  $colsToValues['subscriber_count'] = $subscriberCount;
  mysql_update('_nlb_lists', $listRecord['num'], null, $colsToValues);
  
  //
  return $subscriberCount;  
}

// Update subscriber count on all lists (if user clicks)
function nls_lists_recalculateCounts($tableName, $action) {
  if ($tableName != '_nlb_lists') { return; }
  if ($action    != 'list')       { return; }
  if (!@$_REQUEST['recalculate']) { return; }

  // update counts on all lists
  $listRecords = mysql_select('_nlb_lists');
  foreach ($listRecords as $listRecord) { nls_lists_updateCount($listRecord['num']); }
}

// Update subscriber count on all lists (if user clicks)
function nlb_lists_updateCountOnEditAndSave($tableName, $action) {
  if ($tableName != '_nlb_lists') { return; }
  if (!in_array($action, array('edit', 'save'))) { return; }

  // update count for this list before we load it
  $listNum = intval(@$_REQUEST['num']);
  $_REQUEST['subscriber_count'] = nls_lists_updateCount($listNum);
  
}

//
function nlb_lists_addAllConfirmedSubscribers($tableName, $action) {
  if ($tableName != '_nlb_lists') { return; }              // skip all but this table
  if (!in_array($action, array('add','edit'))) { return; } // skip all but these actions
  if (!@$_REQUEST['_addAllConfirmedSubscribers']) { return; } // skip all but this table
  $recordNum = @$_REQUEST['num'];
  
  // error checking
  $listRecord = mysql_get('_nlb_lists', $recordNum);
  if (!$recordNum)                   { die("Please save the mailing list first before adding users."); }
  if ($listRecord['database_query']) { die("You can't add users to lists that are generated from database queries."); }
  
  // delete all current subscribers
  mysql_delete('_nlb_subscribers2lists', null, array('listNum' => $recordNum));
  
  // add all confirmed subscribers
  $subscribers2Lists = $GLOBALS['TABLE_PREFIX'] . '_nlb_subscribers2lists';
  $subscriberTable   = $GLOBALS['TABLE_PREFIX'] . nlb_subscriber_table();
  $listNum           = intval($recordNum);
  $confirmedField    = nlb_subscriber_confirmedField();
  $insertAllQuery    = "INSERT INTO `$subscribers2Lists` (subscriberNum, listNum) SELECT num, $listNum FROM `$subscriberTable` WHERE `$confirmedField` = 1\n";
  mysqli()->query($insertAllQuery) or dieAsCaller("MySQL Error: ". mysqli()->error. "\n");  
  
  // update subscriber_count field for this record
  nls_lists_updateCount($listNum);
}



?>