<?php
 // Include the class:

require_once("../include/constants.php");
require_once("../include/db.class.php"); 
     
 class suggestionList
{
     
     //This functions returns a data set with all suggestions for a given application
     function showAppSuggestions($appID, $pageNum=NULL, $limit=NULL)
     {
        
        // Open the database (construct the object):
        $db = new DB();
        
        if(!is_null($pageNum) && !is_null($limit))
        {
           $queryResult= $db->query("SELECT A.*, B.appName, C.statusDescription, D.categoryDescription FROM 
                                    Suggestion A JOIN Application B ON A.appID=B.appID
                                    LEFT OUTER JOIN SuggestionStatus C ON A.suggestionStatusCode=C.statusCode
                                    LEFT OUTER JOIN SuggestionCategory D ON A.suggestionCategoryID=D.categoryID 
                                    WHERE A.appID='$appID' ORDER BY B.appName, A.submissionDateTime DESC
                                    LIMIT ".(($pageNum-1)*$limit).", $limit " );  
        }
        else
        {
            $queryResult= $db->query("SELECT A.*, B.appName, C.statusDescription, D.categoryDescription FROM 
                                    Suggestion A JOIN Application B ON A.appID=B.appID
                                    LEFT OUTER JOIN SuggestionStatus C ON A.suggestionStatusCode=C.statusCode
                                    LEFT OUTER JOIN SuggestionCategory D ON A.suggestionCategoryID=D.categoryID 
                                    WHERE A.appID='$appID' ORDER BY B.appName, A.submissionDateTime DESC");
        }
        return $queryResult;
     
     }

     //This function returns a data row for the given application suggestion
     function querySuggestion($suggestionID)
     {
     
        $db = new DB();
        $queryResult= $db->query("SELECT A.*, B.appName, C.statusDescription, D.categoryDescription, E.appSubLocationDescription, E.appSubLocationHttp FROM 
                                    Suggestion A JOIN Application B ON A.appID=B.appID
                                    LEFT OUTER JOIN SuggestionStatus C ON A.suggestionStatusCode=C.statusCode
                                    LEFT OUTER JOIN SuggestionCategory D ON A.suggestionCategoryID=D.categoryID
                                    LEFT OUTER JOIN AppSubLocation E ON A.appSubLocationID=E.appSubLocationID 
                                    WHERE A.suggestionID='$suggestionID'");
        $result=$db->fetchNextObject($queryResult); 
        return $result;
     
     
     }
          //This function returns a data row for the given application
     function queryApp($appID)
     {
     
        $db = new DB();
        $queryResult= $db->query("SELECT A.*, C.organizationID,C.organizationName FROM 
                                    Application A LEFT OUTER JOIN AppOrganizationIntersection B ON A.appID=B.appID
                                    LEFT OUTER JOIN Organization C ON B.organizationID=C.organizationID  
                                    WHERE A.appID='$appID'");
        $result=$db->fetchNextObject($queryResult); 
        return $result;
     
     
     }
    //This function return list of all possible statuses
     function showStatusList()
     {
     
        $db = new DB();
        $queryResult= $db->query("SELECT * FROM SuggestionStatus");
        return $queryResult;
     
     
     }
     //This function updates suggestion info in the database
     function updateSuggestion($adminID, $suggestionID,$statusCode,$suggestionDisposition,$enablePublicView, $comment)
     {
            $sql1=sprintf("UPDATE Suggestion SET suggestionStatusCode='%s',suggestionDisposition='%s',enablePublicView='%s' WHERE suggestionID='$suggestionID'",
                get_magic_quotes_gpc() ? $statusCode : addslashes($statusCode),
                get_magic_quotes_gpc() ? $suggestionDisposition : addslashes($suggestionDisposition),
                get_magic_quotes_gpc() ? $enablePublicView : addslashes($enablePublicView));
            
            $comment = mysql_real_escape_string($comment); 
            
            $sql2= "DELETE FROM SuggestionComments WHERE suggestionID='$suggestionID' and adminID='$adminID'";   
            
            $sql3="INSERT INTO SuggestionComments (suggestionID,adminID,comment,submissionDateTime)
                VALUES ('$suggestionID','$adminID','$comment',NOW())";
                   
            $db = new DB();
                
            $queryResult1= $db->execute($sql1);
            
            if(trim($comment)!="" && $db->execute($sql2))
            { $db->execute($sql3); }
             
            return $queryResult1;
         
     }
          //This function updates suggestion disposition notification in the database
     function updateDispositionNotification($suggestionID,$notification)
     {
            $sql=sprintf("UPDATE Suggestion SET submitterNotifiedOfDisposition='$notification' WHERE suggestionID='$suggestionID'");
            
            $db = new DB();
                
            $queryResult= $db->execute($sql);

            return $queryResult1;
         
     }
     //This function updates suggestion receipt notification in the database
     function updateReceiptNotification($suggestionID,$notification)
     {
            $sql=sprintf("UPDATE Suggestion SET submitterNotified='$notification' WHERE suggestionID='$suggestionID'");
            
            $db = new DB();
                
            $queryResult= $db->execute($sql);

            return $queryResult1;
         
     }
     //------------------------------------------------------------------------------------------
       //This function deletes a suggestion from the database
     function deleteSuggestion($suggestionID)
     {
     
        $db = new DB();

          $queryResult=$db->execute("DELETE FROM Suggestion WHERE suggestionID='$suggestionID'");    
           //we need some code here to delete comments belonging to this suggestions
          if($queryResult)
            $db->execute("DELETE FROM SuggestionComments WHERE suggestionID='$suggestionID'");
             
        return $queryResult;
     
     }

      //This functions returns a data set with all comments belonging to the a suggestion
     function showSuggestionComments($suggestionID)
     {
        
        // Open the database (construct the object):
        $db = new DB();
        
        
            $queryResult= $db->query("SELECT A.*,B.firstName,B.lastName,B.email  FROM
                                    SuggestionComments A JOIN Administrator B ON A.adminID=B.adminID 
                                    WHERE A.suggestionID='$suggestionID' ORDER BY A.submissionDateTime DESC");

        return $queryResult;
     
     }
           //This functions returns the suggestion comment record
     function showAdminComment($adminID,$suggestionID)
     {
        
        // Open the database (construct the object):
        $db = new DB();
        
        
            $queryResult= $db->query("SELECT * FROM SuggestionComments
                                      WHERE suggestionID='$suggestionID' and adminID='$adminID'");
        $result=$db->fetchNextObject($queryResult); 
        return $result;
     
     }
     //This function checks to see if an application with this ID exists in the system.
     //if an application with this ID exists, return true; else false;
     function isValidAppID($appID)
     {
         $db = new DB(); 
        if(!get_magic_quotes_gpc()){
            $appID = addslashes($appID);
        }
        $q = "SELECT * FROM Application WHERE appID='$appID'";
        $result = $db->query($q);
        return (mysql_numrows($result) > 0);
     }
     
         //This function checks to see if an admin has access to the given app
     //if admin has permission, return true; else false;
     function hasAppAccess($appID,$adminID)
     {
         $db = new DB(); 
        if(!get_magic_quotes_gpc()){
            $appID = addslashes($appID);
            $adminID = addslashes($adminID); 
        }
        $q = "SELECT * FROM AdminAppIntersection WHERE appID='$appID' and adminID='$adminID'";
        $result = $db->query($q);
        return (mysql_numrows($result) > 0);
     }
     
function displayPageNav($totalRows,$limit,$pageNum,$pageAddr)
    {

        if (($totalRows%$limit)<>0) {
            $pmax=floor($totalRows/$limit)+1;  // Divide to total result by the number of query you want
        // to display per page($limit) and create a Max page
        } else {
            $pmax=floor($totalRows/$limit);
        }
        if($pmax==1)  //if there is only 1 page, no need to display page number
            return;
            
        echo "<div class='navpage'>"; 

        // We need to create a Previous page, so we need $pageNum to be bigger than 1, otherwise at the first page we would get a 0.
        //For the previous to show up we need at least to be at the second page.
        if($pageNum > 1) {
            $prevp="<a href='".$pageAddr."&pageNum=".($pageNum-1)."' title='Previous Page'>Previous Page</a>&nbsp;&nbsp;"; 
            echo $prevp;
        } else {echo "";}   

        // Here We want create a page from the results we got by dividing the total by the the limit. So let say you got
        //45 results and you want 5 results per page; these simple lines will create you 8 pages.
        $pid=1;
        while ($pid<=$pmax) {
            $paging= "<a href='".$pageAddr."&pageNum=$pid' title='Page $pid of $pmax'>$pid</a>&nbsp;&nbsp;";
        //So here, let say we are at the 3rd page and we want the 3 to be blank so the user can know where is he now.
        //This will act as Current Page! We need to replace the url by a text
            if($pid==$pageNum)
                echo "<span>$pageNum</span>&nbsp;&nbsp;";
            else 
                echo $paging; 
            $pid++;  // create pages until reach the result
        } 
        //We want to create a next page and a last page, $pageNum have to be less than $pmax.
        if($pageNum < $pmax) {
            $nextp="<a href='".$pageAddr."&pageNum=".($pageNum+1)."' title='Next Page'>Next Page</a>&nbsp;&nbsp;"; 
            echo $nextp;
        } else {echo "";}
        if($pageNum != $pmax) {      
            echo  "<a href='".$pageAddr."&pageNum=$pmax' title='Last Page'>Last Page</a>"; }
        
        echo "</div>";
    }
}
?>
