<?php
  $hostName = "localhost";
  $databaseName = "winestore";
  $username = "fred"; 
  $password = "shhh";
?>

<?php
   // This file contains functions used in more than
   // one script in the cart module

   include 'db.inc';
   include 'error.inc';

   // Untaint user data
   function clean($input, $maxlength)
   {
     $input = substr($input, 0, $maxlength);
     $input = EscapeShellCmd($input);
     return ($input);
   }

   // Print out the varieties for a wineID
   function showVarieties($connection, $wineID)
   {
      // Find the varieties of the current wine,
      // and order them by id
      $query = "SELECT gv.variety 
                FROM grape_variety gv, 
                     wine_variety wv, wine w 
                WHERE w.wine_id = wv.wine_id 
                AND wv.variety_id = gv.variety_id 
                AND w.wine_id = $wineID
                ORDER BY wv.id";

      // Run the query
      if (!($result = @ mysql_query($query, $connection)))
         showerror( );
         
      $varieties = "";
         
      // Retrieve and print the varieties
      while ($row = @ mysql_fetch_array($result))
         $varieties .= " " . $row["variety"];

      return $varieties;
   }

   // Show the user the details of one wine in their 
   // cart
   function showWine($wineId, $connection)
   {
      global $username;
      global $password;
      global $databaseName;

      $wineQuery = "SELECT year, winery_name, wine_name
                    FROM winery, wine
                    WHERE wine.winery_id = winery.winery_id
                    AND wine.wine_id = $wineId";  

      $open = false;

      // If a connection parameter is not passed, then 
      // use our own connection to avoid any 
      // locking problems
      if (!isset($connection))
      {
         if (!($connection = @ mysql_connect($hostName, 
                                             $username, 
                                             $password)))
            showerror( );

         if (!mysql_select_db($databaseName, $connection))
            showerror( );

         $open = true;
      }

      // Run the query created above on the database
      // through the connection
      if (!($result = @ mysql_query ($wineQuery,
                                     $connection)))
         showerror( );

      $row = @ mysql_fetch_array($result);

      // Print the wine details
      $result = $row["year"] . " " .
                $row["winery_name"] . " " .
                $row["wine_name"];

      // Print the varieties for this wine
      $result .= showVarieties($connection, $wineId);

      if ($open == true)
         @ mysql_close($connection);
      
      return $result;
   }

   // Print out the pricing information for a wineID
   function showPricing($connection, $wineID)
   {
      // Find the price of the cheapest inventory
      $query = "SELECT min(cost)
                FROM inventory
                WHERE wine_id = $wineID";

      // Run the query
      if (!($result = @ mysql_query($query, 
                                    $connection)))
         showerror( );

      // Retrieve the cheapest price
      $row = @ mysql_fetch_array($result);

      printf("<b>Our price: </b>$%.2f", 
             $row["min(cost)"]);
      printf(" ($%.2f a dozen)", 
             ($row["min(cost)"] * 12));
   }

   // Show the total number of items and dollar value of
   // the shopping cart, as well as a clickable cart icon
   function showCart($connection)
   {
      global $order_no;

      // Initialise an empty cart
      $cartAmount = 0;
      $cartCount = 0;

      // If the user has added items to their cart,
      // then the variable order_no will be registered
      if (session_is_registered("order_no"))
      {
         $cartQuery = "SELECT qty, price " .
                      "FROM items " .
                      "WHERE cust_id = -1 " .
                      "AND order_id = " . $order_no;

         // Find out the number and the dollar value of
         // the items in the cart. To do this, we run 
         // the cartQuery through the connection on 
         // the database
         if (!($result = @ mysql_query ($cartQuery,
                                        $connection)))
            showerror( );                                          

         while ($row = @ mysql_fetch_array($result))
         {
            $cartAmount += $row["price"] * $row["qty"];
            $cartCount += $row["qty"];          
         }
      }

      // This sets up the cart picture.
      // The user can click on it to see the contents of
      // their cart. It also contains JavaScript, so that
      // the cart highlights
      // when the mouse is over it (a "roll-over")
      echo "<table>\n<tr>\n\t<td>";
      echo "<a href=\"example.cart.2.php\" " .
           "onMouseOut=\"cart.src='cart_off.jpg'\" " .
           "onMouseOver=\"cart.src='cart_on.jpg'\"> " .
           "<img src=\"cart_off.jpg\" vspace=0 border=0 " .
           "alt=\"cart picture\" name=\"cart\"></a>\n";
      echo "\t</td>\n";
      printf("\t<td>Total in cart: $%.2f (%d items)</td>\n",
             $cartAmount, $cartCount);
      echo "</tr>\n</table>";
   }

   // Display any messages that are set, and then
   // clear the message
   function showMessage( )
   {
      global $message;
      
      // Is there an error message to show the user?
      if (session_is_registered("message"))
      {
         echo "<h3>";
         echo "<font color=\"red\">$message</font></h3>";
         // Clear the error message
         session_unregister("message");
         $message = "";
      }
   }

   // Show whether the user is logged in or not
   function showLogin( )
   {
      global $loginUsername;

      // Is the user logged in?
      if (session_is_registered("loginUsername"))
         echo "<p align=\"right\">You are currently " .
              "logged in as <b>$loginUsername</b></p>\n";
      else
         echo "<p align=\"right\">You are currently " .
              "not logged in</p>\n";
   }

   // Show the user a login or logout button. 
   // Also, show them membership buttons as appropriate.
   function loginButtons( )
   {
      if (session_is_registered("loginUsername"))
      {
         echo "\n\t<td><input type=\"submit\"" .
              " name=\"logout\" value=\"Logout\"></td>\n";
         echo "\n\t<td><input type=\"submit\"" .
              "name=\"account\" value=\"Change " .
              "Details\"></td>\n";
      }
      else      
      {
         echo "\t<td><input type=\"submit\" " .
              "name=\"login\" value=\"Login\"></td>\n";
         echo "\n\t<td><input type=\"submit\" " .
              "name=\"account\" value=\"Become " .
              "a Member\"></td>\n";
      }
   }

   // Get the cust_id using loginUsername
   function getCustomerID($loginUsername, $connection)
   {
      global $databaseName;
      global $username;
      global $password;
      global $hostName;

      $open = false;

      // If a connection parameter is not passed, then 
      // use our own connection to avoid any locking
      // problems
      if (!isset($connection))
      { 
          if (!($connection = @ mysql_connect($hostName, 
                                             $username, 
                                             $password)))
             showerror( );

          if (!mysql_select_db($databaseName, 
                               $connection))
             showerror( );

          $open = true;
      }

      // We find the cust_id through the users table, 
      // using the session variable holding their
      // loginUsername.
      $query = "SELECT cust_id 
                FROM users 
                WHERE user_name = \"$loginUsername\"";
  
      if (($result = @ mysql_query ($query, $connection)))
         $row = mysql_fetch_array($result);         
      else
         showerror( );
 
      if ($open == true)
         @ mysql_close($connection);
  
     return($row["cust_id"]);
   }

   // Produce a <select> list containing database
   // elements
   function selectDistinct ($connection,
                            $tableName,
                            $columnName,
                            $pulldownName,
                            $additionalOption,
                            $defaultValue)
  {
     $defaultWithinResultSet = FALSE;

     // Query to find distinct values of $columnName
     // in $tableName
     $distinctQuery = "SELECT DISTINCT $columnName
                       FROM $tableName";

     // Run the distinctQuery on the databaseName
     if (!($resultId = @ mysql_query ($distinctQuery, 
                                      $connection)))
        showerror( );

     // Retrieve all distinct values
     $i = 0;
     while ($row = @ mysql_fetch_array($resultId))
        $resultBuffer[$i++] = $row[$columnName];

     // Start the select widget
     echo "\n<select name=\"$pulldownName\">";       

     // Is there an additional option?
     if (isset($additionalOption))
        // Yes, but is it the default option?
        if ($defaultValue == $additionalOption)
           // Show the additional option as selected
           echo "\n\t<option selected>$additionalOption";
        else
           // Just show the additional option
           echo "\n\t<option>$additionalOption";

     // check for a default value
     if (isset($defaultValue))
     {
        // Yes, there's a default value specified

        // Check if the defaultValue is in the 
        // database values
        foreach ($resultBuffer as $result)
           if ($result == $defaultValue)
              // Yes, show as selected
              echo "\n\t<option selected>$result";
           else
              // No, just show as an option
              echo "\n\t<option>$result";
     }  // end if defaultValue
     else 
     { 
        // No defaultValue
       
        // Show database values as options
        foreach ($resultBuffer as $result)
           echo "\n\t<option>$result";
     }
     echo "\n</select>";
  } // end of function
?>