function buildListMaint()
{
/*
Find errors in the database:
1. Lines missing listId, charId, and/or cId.
2. Folders or lists with more than one owner or no owner.
3. Verify that all users of lists inside folders have a folderRoleId.
4. Verify that all users of lists inside folders have a userSiteSettings.
*/
global $debug, $message, $success, $Dbc, $returnThis;
$output = '';
try {
//Check for lines missing listId, charId, and/or cId.
$badLinesCountStmt = $Dbc->query("SELECT\n\tCOUNT(*) AS 'count'\nFROM\n\tlinesTable\nWHERE\n\tlistId = '' OR\n\tlistId IS NULL OR\n\tcharId = '' OR\n\tcharId IS NULL OR\n\tcId = '' OR\n\tcId IS NULL");
$badLinesCountStmt->execute();
$badLinesCount = $badLinesCountStmt->fetch(PDO::FETCH_ASSOC);
$badLinesCount = $badLinesCount['count'];
$badLinesPagination = new Adrlist_Pagination('buildListMaint', 'badLines', $badLinesCount);
list($offset, $limit) = $badLinesPagination->offsetLimit();
$checkLinesStmt = $Dbc->query("SELECT\n\tlinesTable.lineId AS 'lineId',\n\tlinesTable.listId AS 'listId',\n\tlists.listName AS 'listName',\n\tlinesTable.charId AS 'charId',\n\tlinesTable.cId AS 'cId',\n\tlinesTable.line AS 'line'\nFROM\n\tlinesTable\nLEFT JOIN\n\tlists ON lists.listId = linesTable.listId\nWHERE\n\tlinesTable.listId = '' OR\n\tlinesTable.listId IS NULL OR\n\tlinesTable.charId = '' OR\n\tlinesTable.charId IS NULL OR\n\tlinesTable.cId = '' OR\n\tlinesTable.cId IS NULL\nLIMIT {$offset}, {$limit}");
$checkLinesStmt->execute();
$badLines = array();
$foundBadLines = false;
while ($row = $checkLinesStmt->fetch(PDO::FETCH_ASSOC)) {
$badLines[] = $row;
$foundBadLines = true;
}
//$debug->printArray($badLines,'$badLines');
//Verify all lists have one owner.
$multipleListOwnersCountStmt = $Dbc->query("SELECT\n\tCOUNT(*) AS 'count'\nFROM\n\tlists\nJOIN\n\t(userListSettings JOIN users ON userListSettings.userId = users.userId) ON lists.listId = userListSettings.listId AND\n\tuserListSettings.listRoleId = 4 AND\n\tlists.listId IN (SELECT listId FROM userListSettings WHERE listRoleId = 4 GROUP BY listId HAVING COUNT(userId)>1)");
$multipleListOwnersCountStmt->execute();
$multipleListOwnersCount = $multipleListOwnersCountStmt->fetch(PDO::FETCH_ASSOC);
$multipleListOwnersCount = $multipleListOwnersCount['count'];
$mulitpleListOwnersPagination = new Adrlist_Pagination('buildListMaint', 'multipleListOwners', $multipleListOwnersCount);
list($offset, $limit) = $mulitpleListOwnersPagination->offsetLimit();
$multipleListOwnersStmt = $Dbc->query("SELECT\n\tlists.listId AS 'listId',\n\tlists.listName AS 'listName',\n\tlists.cId AS 'cId',\n\tusers.userId AS 'userId',\n\tprimaryEmail AS 'primaryEmail',\n\tCONCAT_WS(' ', users.firstName, users.lastName) AS 'userName'\nFROM\n\tlists\nJOIN\n\t(userListSettings JOIN users ON userListSettings.userId = users.userId) ON lists.listId = userListSettings.listId AND\n\tuserListSettings.listRoleId = 4 AND\n\tlists.listId IN (SELECT listId FROM userListSettings WHERE listRoleId = 4 GROUP BY listId HAVING COUNT(userId)>1)\nORDER BY\n\tlists.listId ASC\nLIMIT {$offset}, {$limit}");
$multipleListOwnersStmt->execute();
$listOwners = array();
$foundMultipleListOwners = false;
while ($row = $multipleListOwnersStmt->fetch(PDO::FETCH_ASSOC)) {
$multipleListOwners[] = $row;
$foundMultipleListOwners = true;
}
//$debug->printArray($listOwners,'$listOwners');
if ($foundBadLines) {
$badLinesTitleArray = array(array('lineId'), array('listId'), array('List Name'), array('charId'), array('cId'), array('line'));
$buildBadLines = new Adrlist_BuildRows('badLines', $badLinesTitleArray, $badLines);
$badLinesOutput = $badLinesPagination->output() . $buildBadLines->output();
} else {
$badLinesOutput = '<div class="break textCenter">
All lines are good.
</div>';
}
if ($foundMultipleListOwners) {
$multipleListOwnersTitleArray = array(array('listId'), array('List Name'), array('cId'), array('userId'), array('Email'), array('Name'));
$multipleListOwnersBuildRows = new Adrlist_BuildRows('multipleOwners', $multipleListOwnersTitleArray, $multipleListOwners);
$multipleOwnersOuput = $mulitpleListOwnersPagination->output() . $multipleListOwnersBuildRows->output();
} else {
$multipleOwnersOuput = '<div class="break textCenter">
All lists have proper ownership.
</div>';
}
//Build the output.
$output .= '<div class="bold textCenter textLarge">Bad Lines</div>
' . $badLinesOutput . '
<div class="break" style="margin-top:2em">
<div class="bold textCenter textLarge">Multiple List Owners</div>
' . $multipleOwnersOuput . '
</div>';
if (MODE == 'buildListMaint') {
$success = true;
$returnThis['holder'] = 'listMaintHolder';
$returnThis['output'] = $output;
}
} catch (PDOException $e) {
error(__LINE__, '', '<pre>' . $e . '</pre>');
}
if (MODE == 'buildListMaint') {
returnData();
} else {
return $output;
}
}
function buildAmazonBilling()
{
global $debug, $message, $success, $Dbc, $returnThis;
$output = '';
try {
//See if the user has an account.
$accountCheckCountStmt = "SELECT\n\tCOUNT(*) AS 'count'\nFROM\n\tbillingOffers\nJOIN\n\tuserBilling ON userBilling.billingOfferId = billingOffers.billingOfferId\nJOIN\n\tusers ON users.userId = userBilling.userId\nWHERE\n\tbillingOffers.renewable = 1";
$accountCheckStmt = "SELECT\n\tuserBilling.userId AS 'userId',\n\t(SELECT CONCAT_WS(' ', users.firstName, users.lastName)) as 'userName',\n\tbillingOffers.billingOfferId AS 'billingOfferId',\n\tbillingOffers.offerName AS 'planName',\n\tbillingOffers.period AS 'period',\n\tuserBilling.dateAdded AS 'dateAdded'\nFROM\n\tbillingOffers\nJOIN\n\tuserBilling ON userBilling.billingOfferId = billingOffers.billingOfferId\nJOIN\n\tusers ON users.userId = userBilling.userId\nWHERE\n\tbillingOffers.renewable = 1";
if (empty($_POST['searchVal'])) {
$search = false;
$accountCheckParams = array();
$accountCheckCountStmt = $Dbc->prepare($accountCheckCountStmt);
} else {
$search = true;
$searchVal = '%' . trim($_POST['searchVal']) . '%';
$debug->add('$searchval: ' . $searchVal);
$endStmt = " AND\n\t(users.firstName LIKE ? || users.lastName LIKE ? || billingOffers.offerName LIKE ?)\n";
$accountCheckStmt .= $endStmt;
$accountCheckParams = array($searchVal, $searchVal, $searchVal);
$accountCheckCountStmt = $Dbc->prepare($accountCheckCountStmt . $endStmt);
}
$accountCheckCountStmt->execute($accountCheckParams);
$count = $accountCheckCountStmt->fetch(PDO::FETCH_ASSOC);
$itemCount = $count['count'];
$pagination = new Adrlist_Pagination('buildAmazonBilling', 'buildAmazonBilling', $itemCount, 'Search Billing', $search);
list($offset, $limit) = $pagination->offsetLimit();
$accountCheckStmt .= "\nLIMIT {$offset}, {$limit}";
$accountCheckStmt = $Dbc->prepare($accountCheckStmt);
$accountCheckStmt->execute($accountCheckParams);
$userPlans = array();
$foundRows = false;
while ($row = $accountCheckStmt->fetch(PDO::FETCH_ASSOC)) {
$foundRows = true;
//Add the question to the user's support section.
if ($row['period'] == 'month') {
$date = Adrlist_Time::addToDate($row['dateAdded'], $row['period'], 1);
$row[] = $date = $date->format('Y-m-d');
} elseif ($row['period'] == 'year') {
$date = Adrlist_Time::addToDate($row['dateAdded'], $row['period'], 1);
$row[] = $date = $date->format('Y-m-d');
}
$userPlans[] = $row;
}
$cssWidths = array(3, 20, 10, 20, 5, 20, 20);
$titleRowArray = array('userId', 'User', 'billingOfferId', 'Plan Name', 'Period', 'Date Added', 'Next Billing Date');
$buildRows = new Adrlist_BuildRows($titleRowArray, $userPlans, $cssWidths);
$output .= '<div>
<input type="text" style="width:20em" id="billingDate"> Date <span class="buttonBlueThin" id="addMonth">Add a Month</span> <input type="text" id="dateDestination">
</div>' . $pagination->output();
$output .= $foundRows ? $buildRows->output() : '<div class="textCenter" style="margin:1em">No records were found.</div>';
if (MODE == 'buildAmazonBilling') {
$success = true;
$returnThis['holder'] = 'amazonBillingHolder';
$returnThis['output'] = $output;
}
} catch (PDOException $e) {
error(__LINE__, '', '<pre>' . $e . '</pre>');
}
if (MODE == 'buildAmazonBilling') {
returnData();
} else {
return $output;
}
}
function buildFaqs()
{
//This gets FAQs with and without searching.
global $debug, $message, $success, $Dbc, $returnThis;
$output = '';
try {
$faqQuery = "SELECT\n\tfaqs.faqId AS 'faqId',\n\tfaqs.q AS 'q',\n\tfaqs.a AS 'a',\n\tfaqTopics.topicId AS 'topicId',\n\tfaqTopics.topic AS 'topic'\nFROM\n\tfaqs\nJOIN\n\tfaqTopics ON faqTopics.topicId = faqs.topicId AND\n\tfaqs.hidden = '0'\n";
if (empty($_POST['searchVal'])) {
$search = false;
$stmt = $Dbc->query($faqQuery . "\nORDER BY\n\tfaqTopics.topic");
$faqCountStmt = $Dbc->query("SELECT COUNT(*) AS 'count' FROM faqs WHERE hidden = 0");
} else {
$search = true;
$searchVal = '%' . trim($_POST['searchVal']) . '%';
$faqSearchQuery = "\n AND (faqTopics.topic LIKE ? || faqs.q LIKE ? || faqs.a LIKE ?)\nGROUP BY\n\tfaqs.faqId";
$stmt = $Dbc->prepare($faqQuery . $faqSearchQuery);
$stmt->execute(array($searchVal, $searchVal, $searchVal));
$faqCountStmt = $Dbc->prepare("SELECT COUNT(*) AS 'count' FROM faqs JOIN\n\tfaqTopics ON faqTopics.topicId = faqs.topicId AND\n\tfaqs.hidden = '0'" . $faqSearchQuery);
$faqCountStmt->execute(array($searchVal, $searchVal, $searchVal));
}
$row = $faqCountStmt->fetch(PDO::FETCH_ASSOC);
$itemCount = empty($row['count']) ? 0 : $row['count'];
$debug->add('$itemCount: ' . $itemCount);
$success = true;
$lastTopic = '';
$topicNumber = 0;
$foundRows = false;
$rowsArray = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$foundRows = true;
$question = convertFaqLink($row['q']);
$answer = convertFaqLink($row['a']);
$currentTopic = $row['topic'];
if ($currentTopic != $lastTopic) {
$topicNumber++;
$lastTopic = $currentTopic;
}
$rowsArray[$currentTopic][] = array('<div class="faq hand" faqid="' . $row['faqId'] . '">
<div class="faqQuestion" id="faq' . $row['faqId'] . '" toggle="faqAnswer' . $row['faqId'] . '">' . nl2br($question, 1) . ' <span class="faqId">FAQ #' . $row['faqId'] . '</span></div>
<div class="faqAnswer textLeft" id="faqAnswer' . $row['faqId'] . '">' . nl2br($answer, 1) . '</div>
</div>
');
}
//$debug->printArray($rowsArray,'$rowsArray');
$cssWidths = array('100%');
$temp = $search ? '<div class="red textCenter">Results for "' . $_POST['searchVal'] . '"</div>' : '';
foreach ($rowsArray as $key => $value) {
$temp .= '<div class="sectionTitle textCenter">' . $key . '</div>
';
$faqRows = new Adrlist_BuildRows('faqs', '', $value, false);
$temp .= $faqRows->output();
}
$pagination = new Adrlist_Pagination('buildFaqs', 'buildFaqs', $itemCount, 'Search FAQs', $search);
$pagination->_searchOnly();
$output .= '<div class="textCenter">
Click on a topic to view FAQs
<div class="break" style="margin:1em">
<button class="ui-btn ui-btn-inline ui-btn-a ui-shadow ui-corner-all" id="faqHideAll" data-role="false">Hide All</button><button class="ui-btn ui-btn-inline ui-btn-a ui-shadow ui-corner-all" id="faqShowAll" data-role="false">Show All</button>
</div>
</div>
' . $pagination->output() . $temp;
if (empty($foundRows)) {
pdoError(__LINE__, $stmt, $params = false, true);
if ($search) {
$output .= '<div class="red textCenter" style="margin:1em">There were no matches for ' . $_POST['searchVal'] . '.</div>';
} else {
$output .= '<div class="break" style="padding:5px 0px 10px 0px;">
There are no faqs to show.
</div>
';
// <span class="buttonBlueThin" id="faqHideAll">Hide All</span><span class="buttonBlueThin" id="faqShowAll">Show All</button>
}
}
$returnThis['holder'] = 'buildFaqsHolder';
$returnThis['output'] = $output;
$returnThis['buildFaqs'] = $output;
} catch (PDOException $e) {
error(__LINE__, '', '<pre>' . $e . '</pre>');
}
if (MODE == 'buildFaqs') {
returnData();
} else {
return $output;
}
}
function buildBillingHistory()
{
//Build the billing history.
global $debug, $message, $success, $Dbc, $returnThis;
$output = '';
try {
$billingHistoryCount = "SELECT\n\tCOUNT(*) AS 'count'\nFROM\n\tuserBillingActions\nJOIN\n\tbillingOffers ON billingOffers.billingOfferId = userBillingActions.billingOfferId\nLEFT JOIN\n\tamazonCBUIResponses ON (amazonCBUIResponses.callerReference = userBillingActions.userBillingActionId OR\n\tamazonCBUIResponses.userBillingActionId = userBillingActions.userBillingActionId)\nLEFT JOIN\n\tamazonIPNListener ON (amazonIPNListener.callerReference = amazonCBUIResponses.callerReference OR\n\tamazonIPNListener.userBillingActionId = userBillingActions.userBillingActionId)\nWHERE\n\tuserBillingActions.userId = ?\n";
//Get transactions from userBillingActions.
$billingHistoryStmt = "SELECT\n\tbillingOffers.offerName AS 'offerName',\n\tbillingOffers.offerLength AS 'length',\n\tbillingOffers.period AS 'period',\n\tbillingOffers.price AS 'price',\n\tbillingOffers.credits AS 'credits',\n\tbillingOffers.terms AS 'terms',\n\tamazonCBUIResponses.aDatetime AS 'responsesDatetime',\n\tamazonIPNListener.aDatetime AS 'listenerDatetime',\n\tamazonIPNListener.operation AS 'operation',\n\tamazonIPNListener.parentTransactionId AS 'parentTransactionId',\n\tamazonIPNListener.transactionAmount AS 'transactionAmount',\n\tamazonIPNListener.transactionId AS 'transactionId',\n\tamazonIPNListener.transactionStatus AS 'transactionStatus',\n\tamazonCBUIResponses.tokenId AS 'tokenId',\n\tuserBillingActions.billingDatetime AS 'datetime',\n\tuserBillingActions.userBillingActionId AS 'userBillingActionId',\n\tuserBillingActions.billingActionId AS 'billingActionId'\nFROM\n\tuserBillingActions\nJOIN\n\tbillingOffers ON billingOffers.billingOfferId = userBillingActions.billingOfferId\nLEFT JOIN\n\tamazonCBUIResponses ON (amazonCBUIResponses.callerReference = userBillingActions.userBillingActionId OR\n\tamazonCBUIResponses.userBillingActionId = userBillingActions.userBillingActionId)\nLEFT JOIN\n\tamazonIPNListener ON (amazonIPNListener.callerReference = amazonCBUIResponses.callerReference OR\n\tamazonIPNListener.userBillingActionId = userBillingActions.userBillingActionId)\nWHERE\n\tuserBillingActions.userId = ?\n";
if (empty($_POST['searchVal'])) {
$search = false;
$billingHistoryStmt .= "\nGROUP BY\n\tuserBillingActions.userBillingActionId\nORDER BY\n\tuserBillingActions.userBillingActionId, userBillingActions.billingDatetime, amazonIPNListener.aDatetime, amazonIPNListener.microtime,amazonCBUIResponses.aDatetime";
$billingHistoryParams = array($_SESSION['userId']);
$billingHistoryCount = $Dbc->prepare($billingHistoryCount);
} else {
$search = true;
$searchVal = '%' . trim($_POST['searchVal']) . '%';
$debug->add('$searchval: ' . $searchVal);
$endStmt = " AND\n\t(billingActions.billingAction LIKE ? || billingOffers.offerName LIKE ?)\nGROUP BY\n\tuserBillingActions.userBillingActionId\nORDER BY\n\tuserBillingActions.userBillingActionId, userBillingActions.billingDatetime, amazonIPNListener.aDatetime, amazonIPNListener.microtime,amazonCBUIResponses.aDatetime";
$billingHistoryStmt .= $endStmt;
$billingHistoryParams = array($_SESSION['userId'], $searchVal, $searchVal, $searchVal);
$billingHistoryCount = $Dbc->prepare($billingHistoryCount . $endStmt);
}
$billingHistoryCount->execute($billingHistoryParams);
$count = $billingHistoryCount->fetch(PDO::FETCH_ASSOC);
$itemCount = $count['count'];
$pagination = new Adrlist_Pagination('buildBillingHistory', 'buildBillingHistory', $itemCount, 'Search History', $search);
list($offset, $limit) = $pagination->offsetLimit();
$billingHistoryStmt = $Dbc->prepare($billingHistoryStmt . "\nLIMIT {$offset}, {$limit}");
$billingHistoryStmt->execute($billingHistoryParams);
//pdoError(__LINE__,$billingHistoryStmt,$billingHistoryParams);
$foundRows = false;
$rowArray = array();
$termsArray = array();
$billingActions = Adrlist_Billing::getBillingActions();
$nestedTransactions = array();
while ($row = $billingHistoryStmt->fetch(PDO::FETCH_ASSOC)) {
$transactionId = $row['parentTransactionId'] ? $row['parentTransactionId'] : $row['transactionId'];
$transactionAmount = $row['transactionAmount'] ? $row['transactionAmount'] : 'USD ' . $row['price'];
//Use the payment authorization request (billingActionId = 1) as the main transaction. All other transactions will be referenced by transactionId or parentTransactionId.
if ($row['billingActionId'] == 1) {
$rowArray[$transactionId] = array($row['userBillingActionId'], $row['offerName'], $row['length'] . ' ' . $row['period'], $transactionAmount, $row['credits'], Adrlist_Time::utcToLocal($row['datetime']), '<button class="ui-btn ui-icon-carat-r ui-btn-icon-right ui-btn-inline ui-corner-all ui-mini" toggle="BillingHistoryNested' . $transactionId . '">View Transactions</button>');
$foundRows = true;
} else {
//This is for nested, related transactions that are not a payment authorization request.
if ($row['billingActionId'] == 1) {
$date = $row['datetime'];
} elseif ($row['billingActionId'] == 2) {
//An amazonCBUIResponse date.
$date = $row['responsesDatetime'];
} elseif ($row['billingActionId'] == 3 || $row['billingActionId'] == 4 || $row['billingActionId'] == 5 || $row['billingActionId'] == 6) {
//An amazonIPNListener date.
$date = $row['listenerDatetime'];
} else {
$date = $row['datetime'];
}
//Build the nested transactions.
$termsArray[$transactionId] = '<div class="textLeft">' . nl2br($row['terms']) . '</div>';
$nestedTransactions[$transactionId][] = array(Adrlist_Time::utcToLocal($date), $billingActions[$row['billingActionId']], $transactionAmount);
}
}
$output .= '<div class="bold textLarge">Billing History</div>';
if ($foundRows) {
$nestedTransactionsTitleRowArray = array(array('Date', 15), array('Billing Action', 30), array('Transaction Amount', 15));
$debug->printArray($nestedTransactions, '$nestedTransactions');
$hiddenRows = array();
foreach ($nestedTransactions as $transactionId => $value) {
$buildNestedRows = new Adrlist_BuildRows('nothing' . $transactionId, $nestedTransactionsTitleRowArray, $value);
$hiddenRows[$transactionId] = array('BillingHistoryNested' . $transactionId, '<div>' . $buildNestedRows->output() . '</div>' . '<h2>Terms</h2>' . $termsArray[$transactionId]);
}
$debug->printArray($hiddenRows, '$hiddenRows');
$rowArray = array_reverse($rowArray);
$debug->printArray($rowArray, '$rowArray');
$titleArray = array(array('Billing ID', 6), array('Offer Name', 10), array('Period', 8), array('Price', 8), array('Credits', 8), array('Purchase Date', 16), array('Transaction Details', 15));
$buildLists = new Adrlist_BuildRows('BillingHistory', $titleArray, $rowArray);
$buildLists->addHiddenRows($hiddenRows);
$output .= $pagination->output() . $buildLists->output();
//$titleRowArray = array('Billing ID','Offer Name','Period','Price','Credits','Date','Terms','Transactions');
//$cssWidths = array(6,10,8,8,8,15,15,15);
} else {
$output .= 'There is no billing history for this account.';
}
if (MODE == 'buildBillingHistory') {
$success = true;
$returnThis['output'] = $output;
}
} catch (Adrlist_CustomException $e) {
} catch (PDOException $e) {
error(__LINE__, '', '<pre>' . $e . '</pre>');
}
if (MODE == 'buildBillingHistory') {
$success = true;
returnData();
} else {
return $output;
}
}
function buildListUsers()
{
//Build the users of the selected list.
global $debug, $message, $success, $Dbc, $returnThis;
$output = '';
try {
if (empty($_POST['listId'])) {
throw new Adrlist_CustomException('', '$_POST[\'listId\'] is empty.');
} elseif (!is_numeric($_POST['listId'])) {
throw new Adrlist_CustomException('', '$_POST[\'listId\'] is not numeric.');
}
//Get the user's list role.
$listInfo = getListInfo($_SESSION['userId'], $_POST['listId']);
if ($listInfo === false || $listInfo['listRoleId'] < 3) {
//The user must be a Manager (3) or higher to view list users.
throw new Adrlist_CustomException("Your role does not allow you to edit this list.", '');
}
//Select the existing users.
$listUsersCountStmt = "SELECT\n\tCOUNT(users.userId) AS 'count'\nFROM\n\tusers\nJOIN\n\tuserListSettings ON userListSettings.userId = users.userId AND\n\tuserListSettings.listId = ?\nWHERE\n\tusers.userId != ?";
$listUsersStmt = "SELECT\n\tusers.userId AS 'userId',\n\tCONCAT_WS(' ',users.firstName,users.lastName) AS 'name',\n\tusers.primaryEmail AS 'primaryEmail',\n\tuserListSettings.listRoleId AS 'listRoleId',\n\tuserListSettings.dateAdded AS 'dateAdded'\nFROM\n\tusers\nJOIN\n\tuserListSettings ON userListSettings.userId = users.userId AND\n\tuserListSettings.listId = ?\nWHERE\n\tusers.userId != ?";
/*GROUP BY
users.primaryEmail*/
$listUsersEndStmt = "\nORDER BY\n\tCONCAT_WS(' ',users.firstName,users.lastName), users.primaryEmail";
//Select the users with pending invitations.
$pendingUsersCountStmt = "SELECT\n\tCOUNT(email) AS 'count'\nFROM\n\tinvitations\nWHERE\n\tlistId = ? AND\n\trespondDate IS NULL AND\n\temail NOT IN (SELECT users.primaryEmail FROM users)";
$pendingUsersStmt = "SELECT\n\tinvitationId AS 'invitationId',\n\temail AS 'email',\n\tlistRoleId AS 'listRoleId',\n\tsentDate AS 'sentDate',\n\tsenderId AS 'senderId'\nFROM\n\tinvitations\nWHERE\n\tlistId = ? AND\n\trespondDate IS NULL AND\n\temail NOT IN (SELECT users.primaryEmail FROM users)";
$pendingUsersEndStmt = "\nORDER BY\n\temail";
if (!empty($_POST['searchVal']) && !empty($_POST['searchFor']) && $_POST['searchFor'] == 'listUsers') {
$searchListUsers = true;
$searchVal = '%' . trim($_POST['searchVal']) . '%';
$listUsersSearchQuery = " AND\n\t(users.firstName LIKE ? || users.lastName LIKE ? || users.primaryEmail LIKE ?)";
$listUsersStmt = $listUsersStmt . $listUsersSearchQuery . $listUsersEndStmt;
$listUsersParams = array($_POST['listId'], $_SESSION['userId'], $searchVal, $searchVal, $searchVal);
$listUsersCountStmt .= $listUsersSearchQuery;
} else {
$searchListUsers = false;
$listUsersStmt .= $listUsersEndStmt;
$listUsersParams = array($_POST['listId'], $_SESSION['userId']);
}
if (!empty($_POST['searchVal']) && !empty($_POST['searchFor']) && $_POST['searchFor'] == 'pendingListUsers') {
$searchPendingListUsers = true;
$searchVal = '%' . trim($_POST['searchVal']) . '%';
$pendingUsersSearchQuery = " AND\nemail LIKE ?";
$pendingUsersStmt = $pendingUsersStmt . $pendingUsersSearchQuery . $pendingUsersEndStmt;
$pendingUsersParams = array($_POST['listId'], $searchVal);
$pendingUsersCountStmt .= $pendingUsersSearchQuery;
} else {
$searchPendingListUsers = false;
$pendingUsersStmt = $pendingUsersStmt . $pendingUsersEndStmt;
$pendingUsersParams = array($_POST['listId']);
}
$listUsersCountStmt = $Dbc->prepare($listUsersCountStmt);
$listUsersCountStmt->execute($listUsersParams);
$row = $listUsersCountStmt->fetch(PDO::FETCH_ASSOC);
$itemCount = $row['count'];
$pagination = new Adrlist_Pagination('buildListUsers', 'listUsers', $itemCount, 'Search Users', $searchListUsers);
$pagination->addSearchParameters(array('listId' => $_POST['listId'], 'searchFor' => 'listUsers'));
list($offset, $limit) = $pagination->offsetLimit();
$listUsersStmt = $Dbc->prepare($listUsersStmt . " LIMIT {$offset}, {$limit}");
$listUsersStmt->execute($listUsersParams);
$pendingUsersCountStmt = $Dbc->prepare($pendingUsersCountStmt);
$pendingUsersCountStmt->execute($pendingUsersParams);
$row = $pendingUsersCountStmt->fetch(PDO::FETCH_ASSOC);
$itemCount = $row['count'];
$pendingPagination = new Adrlist_Pagination('buildListUsers', 'pendingListUsers', $itemCount, 'Search Pending Users', $searchPendingListUsers);
$pendingPagination->addSearchParameters(array('listId' => $_POST['listId'], 'searchFor' => 'pendingListUsers'));
list($pendingOffset, $pendingLimit) = $pendingPagination->offsetLimit();
$pendingUsersStmt = $Dbc->prepare($pendingUsersStmt . " LIMIT {$pendingOffset}, {$pendingLimit}");
$pendingUsersStmt->execute($pendingUsersParams);
$listInfo = getListInfo($_SESSION['userId'], $_POST['listId']);
$listUsersCount = 0;
$listUsersArray = array();
$listUsersHiddenRow = array();
while ($row = $listUsersStmt->fetch(PDO::FETCH_ASSOC)) {
$listUsersCount++;
$name = '<button class="ui-btn ui-mini ui-btn-icon-right ui-icon-carat-r ui-btn-inline ui-corner-all" toggle="existingUser' . $row['userId'] . '">' . $row['name'] . '</button>';
$email = '<a href="mailto:' . $row['primaryEmail'] . '">' . breakEmail($row['primaryEmail'], 30) . '</a>';
$date = $row['dateAdded'] != '0000-00-00 00:00:00' ? Adrlist_Time::utcToLocal($row['dateAdded']) : 'n/a';
$listUsersArray[$row['userId']] = array($name, $date);
//The user rowActions.
$userActions = '';
if ($listInfo['listRoleId'] <= 3 && $row['listRoleId'] >= 3) {
//List managers cannot change the role of managers or owners.
$role = role($row['listRoleId']);
} else {
$additionalAttributes = array('class' => 'changeListRole', 'userId' => $row['userId'], 'listId' => $_POST['listId']);
$role = buildRoles('changeListRole' . $row['userId'], $row['listRoleId'], array(0, 1, 2, 3), $additionalAttributes);
}
if ($listInfo['listRoleId'] >= 3) {
$userActions .= '<div class="ui-field-contain"><label for="existingRole' . $row['userId'] . '">List Role ' . faqLink(24) . '</label>' . $role . '</div>';
$userActions .= $row['listRoleId'] < 3 || $listInfo['listRoleId'] == 4 ? '<button class="removeUserFromList ui-btn ui-btn-inline ui-corner-all ui-mini" listId="' . $_POST['listId'] . '" userId="' . $row['userId'] . '" listName="' . $listInfo['listName'] . '"><i class="fa fa-times" ></i>Remove User</button>' : '';
}
$listUsersHiddenRow[$row['userId']] = array('existingUser' . $row['userId'], $userActions);
}
//Build pending list users.
$pendingListUsersCount = 0;
$pendingUsersArray = array();
$pendingUsersHiddenRow = array();
while ($pendingRow = $pendingUsersStmt->fetch(PDO::FETCH_ASSOC)) {
$pendingListUsersCount++;
$name = '<button class="ui-btn ui-mini ui-btn-icon-right ui-icon-carat-r ui-btn-inline ui-corner-all" toggle="pendingUser' . $pendingRow['invitationId'] . '">' . $pendingRow['email'] . '</button>';
$email = '<a href="mailto:' . $pendingRow['email'] . '">' . breakEmail($pendingRow['email'], 40) . '</a>';
$sentDate = $pendingRow['sentDate'] != '0000-00-00 00:00:00' ? Adrlist_Time::utcToLocal($pendingRow['sentDate']) : 'n/a';
$pendingUsersArray[$pendingRow['invitationId']] = array($name, $sentDate);
//The pending list user rowActions.
$userActions = '';
if ($listInfo['listRoleId'] == 3 && $pendingRow['listRoleId'] >= 3) {
//Managers cannot change the role of other managers.
$role = role($pendingRow['listRoleId']);
} else {
$additionalAttributes = array('class' => 'changePendingRole', 'invitationId' => $pendingRow['invitationId'], 'adrtype' => 'list', 'typeid' => $_POST['listId']);
$role = buildRoles('', $pendingRow['listRoleId'], array(0, 1, 2, 3), $additionalAttributes);
}
if ($listInfo['listRoleId'] >= 3) {
$userActions .= '<div class="ui-field-contain"><label for="pendingRole' . $pendingRow['invitationId'] . '">List Role ' . faqLink(24) . '</label>' . $role . '</div>
<button adrType="list" class="removeInvitation ui-btn ui-btn-inline ui-corner-all ui-mini" invitationId="' . $pendingRow['invitationId'] . '" typeId="' . $_POST['listId'] . '"><i class="fa fa-times" ></i>Remove User</button>';
}
$pendingUsersHiddenRow[$pendingRow['invitationId']] = array('pendingUser' . $pendingRow['invitationId'], $userActions);
}
//Build list users.
$output .= '<div class="textCenter textLarge">
<i class="fa fa-file-o" ></i><span class="bold">' . $listInfo['listName'] . '</span> Users
</div>';
if (empty($listUsersCount)) {
pdoError(__LINE__, $listUsersStmt, $listUsersParams, true);
$output .= '<div class="break red" style="padding:5px 0px 10px 0px;">
There are no users.
</div>';
}
if ($searchListUsers) {
$results = intThis($listUsersCount);
$output .= '<div class="break red">';
$output .= $results == 1 ? $results . ' result' : $results . ' results';
$output .= ' for "' . $_POST['searchVal'] . '".</div>';
}
$listUsersTitleArray = array(array('Name'), array('Shared On', 1));
$buildListUsers = new Adrlist_BuildRows('existingListUsers', $listUsersTitleArray, $listUsersArray);
$buildListUsers->addHiddenRows($listUsersHiddenRow);
$output .= $pagination->output('listUsersViewOptions') . $buildListUsers->output();
$output .= '<div class="hr3" style="margin:2em 0;"></div>';
//Build pending users.
$output .= '<div class="break textCenter textLarge" style="margin:1em 0 0 0">
Pending Users
</div>';
if (empty($pendingListUsersCount)) {
pdoError(__LINE__, $pendingUsersStmt, $pendingUsersParams, true);
$output .= '<div class="break red" style="padding:5px 0px 10px 0px;">
There are no pending users.
</div>';
}
if ($searchPendingListUsers) {
$results = intThis($pendingListUsersCount);
$output .= '<div class="break red">';
$output .= $results == 1 ? $results . ' result' : $results . ' results';
$output .= ' for "' . $_POST['searchVal'] . '".</div>';
}
$pendingUsersTitleArray = array(array('Pending User'), array('Shared On', 1));
$buildPendingUsers = new Adrlist_BuildRows('pendingListUsers', $pendingUsersTitleArray, $pendingUsersArray);
$buildPendingUsers->addHiddenRows($pendingUsersHiddenRow);
$output .= $pendingPagination->output('pendingListUsersViewOptions') . $buildPendingUsers->output();
if (MODE == 'buildListUsers') {
$success = true;
$returnThis['output'] = $output;
}
} catch (Adrlist_CustomException $e) {
} catch (PDOException $e) {
error(__LINE__, '', '<pre>' . $e . '</pre>');
}
if (MODE == 'buildListUsers') {
returnData();
} else {
return $output;
}
}
