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