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;
}
}
