From fb422c3d83a9f066b45e2540247824a2309b25d4 Mon Sep 17 00:00:00 2001
From: Johnnie Lamar Odom II <jlodom00@alumni.amherst.edu>
Date: Wed, 6 Jun 2018 00:09:34 -0500
Subject: [PATCH] Trying to modify sendlist to return better results.

---
 code/clientlib/3.00/send.php |  7 +++--
 code/lib/Send.php            | 68 +++++++++++++++++++++++++++++++++-----------
 2 files changed, 57 insertions(+), 18 deletions(-)

diff --git a/code/clientlib/3.00/send.php b/code/clientlib/3.00/send.php
index e250e73..9e39250 100644
--- a/code/clientlib/3.00/send.php
+++ b/code/clientlib/3.00/send.php
@@ -23,6 +23,7 @@ function sendGet($arrayRestInputs){
 					for($i = 0; $i < $limit; $i++){
 						$sendReturn[$i]['sent'] = date(DATE_ATOM, $sendReturn[$i]['sent']);
 						$sendReturn[$i]['seen'] = date(DATE_ATOM, $sendReturn[$i]['seen']);
+						$sendReturn[$i]['requestinguser'] = $objectToken->username;
 					}
 				}
 				return $sendReturn;
@@ -77,8 +78,10 @@ function sendlistGet($arrayRestInputs){
 				if(is_array($sendlistReturn)){
 					$limit = count($sendlistReturn);
 					for($i = 0; $i < $limit; $i++){
-						$sendlistReturn[$i]['senddate'] = date(DATE_ATOM, $sendlistReturn[$i]['senddate']);
-						$sendlistReturn[$i]['seen'] = date(DATE_ATOM, $sendlistReturn[$i]['seen']);
+						$sendlistReturn[$i]['selfsenddate'] = date(DATE_ATOM, $sendlistReturn[$i]['selfsenddate']);
+						$sendlistReturn[$i]['selfseen'] = date(DATE_ATOM, $sendlistReturn[$i]['selfseen']);
+						$sendlistReturn[$i]['othersenddate'] = date(DATE_ATOM, $sendlistReturn[$i]['othersenddate']);
+						$sendlistReturn[$i]['otherseen'] = date(DATE_ATOM, $sendlistReturn[$i]['otherseen']);
 					}
 				}
 				return $sendlistReturn;
diff --git a/code/lib/Send.php b/code/lib/Send.php
index 124846f..f965bef 100644
--- a/code/lib/Send.php
+++ b/code/lib/Send.php
@@ -115,36 +115,72 @@ class Send {
 	}
 
 
-	/**
-	 * Return a watchlist of send information. Validation and date processing handled by api library.
-	 */
+	/* Return a watchlist of send information.
+		Validation and date processing handled by api library, although we do some validation here as a backstop.
+		We grab two simple queries and combine them in code because the unified query was 5X slower and it made the API slow.
+		On the reference Planworld database, the complex query was nearing 6 seconds, and the code below returns in 1 */
 
 	function getSendList ($uid) {
 		$planworldForGetSendList = new Planworld();
 		if($planworldForGetSendList->isUser($uid)){
 			$dbh = Planworld::_connect();
+			$outboundreturn = '';
+			$inboundreturn = '';
+			$indexedreturn = array();
+			$indexedkeylist = array();
+			$finalarray = array();
 			try{
-				$query = $dbh->prepare('SELECT name, isinbound, senddate, seen FROM
-					(SELECT U.username AS name, "TRUE" AS isinbound, MAX(S.SENT) AS senddate, MAX(S.SEEN) as seen FROM send S, users U
-						WHERE U.id=S.uid  AND S.to_uid=:uid GROUP BY S.uid
-					UNION
-					SELECT U.username AS name,  "FALSE" AS isinbound, MAX(S.sent) AS senddate, MAX(S.SEEN) as seen FROM send S, users U
-						WHERE U.id=S.to_uid AND S.uid=:uid GROUP BY S.to_uid
-						ORDER BY name, senddate DESC) SUB
-					GROUP BY name ORDER BY name');
-				$queryArray = array('uid' => $uid);
-				$query->execute($queryArray);
-				$result = $query->fetchAll();
-				if (!$result){
+				$query1 = $dbh->prepare('SELECT U.username AS name, MAX(S.SENT) AS othersenddate, MAX(S.SEEN) as selfseen FROM send S, users U WHERE U.id=S.uid  AND S.to_uid=:uid GROUP BY S.uid  ORDER BY name');
+				$queryArray1 = array('uid' => $uid);
+				$query1->execute($queryArray1);
+				$return1 = $query1->fetchAll();
+				if (!$return1){
 					return PLANWORLD_ERROR;
 				}
 				else {
-					return $result;
+					$inboundreturn = $return1;
 				}
 			}
 			catch(PDOException $badquery){
 				return PLANWORLD_ERROR;
 			}
+			/* Get message information about messages from the self to the other. */
+			try{
+				$query2 = $dbh->prepare('SELECT U.username AS name, MAX(S.sent) AS selfsenddate, MAX(S.SEEN) as otherseen FROM send S, users U WHERE U.id=S.to_uid AND S.uid=:uid GROUP BY S.to_uid ORDER BY name');
+				$queryArray2 = array('uid' => $uid);
+				$query2->execute($queryArray2);
+				$return2 = $query2->fetchAll();
+				if (!$return2){
+					return PLANWORLD_ERROR;
+				}
+				else {
+					$outboundreturn = $return2;
+				}
+			}
+			catch(PDOException $badquery){
+				return PLANWORLD_ERROR;
+			}
+			foreach($inboundreturn as $row){
+				$indexedreturn[$row["name"]] = array("name"=> $row["name"], "othersenddate"=> $row["othersenddate"], "selfsenddate"=> 0, "selfseen"=> $row["selfseen"], "otherseen"=> 0);
+				$indexedkeylist[] = $row["name"];
+			}
+
+			foreach($outboundreturn as $row){
+				if(in_array($row["name"], $indexedkeylist)){
+					$indexedreturn[$row["name"]]["selfsenddate"]=$row["selfsenddate"];
+					$indexedreturn[$row["name"]]["otherseen"]=$row["otherseen"];
+				}
+				else{
+					$indexedreturn[$row["name"]] = array("name"=> $row["name"], "othersenddate"=>0, $row["selfsenddate"], "selfseen"=>0, "otherseen"=> $row["otherseen"]);
+					$indexedkeylist[] = $row["name"];
+				}
+			}
+
+			foreach($indexedreturn as $row){
+				$finalarray[] = $row;
+			}
+
+			return $finalarray;
 		}
 		return PLANWORLD_ERROR;
 	}
-- 
1.8.3.1