<?php
  include '../lib/common.php';
  include '../lib/db.php';
  
  // return HTML for category select list
  if (isset($\_GET[\text{'}retrieve\_category\_select\text{'}]))\; \{\; echo\; \text{'}$<select id="cat_select" name="cat_select">';
      echo '<option>Select</option>';
      echo '<option value="new">Create New Category</option>';
  
      query = sprintf('
          SELECT
              C.CATEGORY_ID, CATEGORY_NAME, COUNT(ITEM_ID) AS ITEM_COUNT
          FROM
              \%sSHOP_CATEGORY C
                  LEFT JOIN \%sSHOP_INVENTORY I ON C.CATEGORY_ID = I.CATEGORY_ID
          GROUP BY
              C.CATEGORY_ID
          ORDER BY
              CATEGORY_NAME ASC',
          DB_TBL_PREFIX,
          DB_TBL_PREFIX);
      $result\; =\; mysql\_query($query, $GLOBALS[\text{'}DB\text{'}]);\; while\; ($row = mysql_fetch_assoc($result))\; \{\; printf(\text{'}$<option value="\%d">\%s &nbsp; (\%s)</option>',
              row['CATEGORY_ID'], $row[\text{'}CATEGORY\_NAME\text{'}],$row['ITEM_COUNT']); 
      }
      mysql_free_result($result);\; echo\; \text{'}$</select>';
  }
  
  // return JSON-encoded string with category information
  else if (isset(_GET['retrieve_category']))
  {
      $query\; =\; sprintf(\text{'}SELECT\; CATEGORY\_NAME\; FROM$\%sSHOP_CATEGORY WHERE ' .
          'CATEGORY_ID = \%d',
          DB_TBL_PREFIX,
          _GET['id']);
      $result\; =\; mysql\_query($query, $GLOBALS[\text{'}DB\text{'}]);$row = mysql_fetch_assoc($result);\; echo\; json\_encode(array(\text{'}cat\_name\text{'}\; =>$row['CATEGORY_NAME']));
  
      mysql_free_result($result);\; \}$// process save request for category information
  else if (isset(_GET['save_category']))
  {
      // create a new record
      if ($\_POST[\text{'}id\text{'}]\; ==\; \text{'}new\text{'})\; \{$query = sprintf('INSERT INTO \%sSHOP_CATEGORY (CATEGORY_NAME) ' .
              'VALUES ("\%s")',
              DB_TBL_PREFIX,
              mysql_real_escape_string($\_POST[\text{'}name\text{'}],$GLOBALS['DB']));
      }
      else
      {
          // delete an existing record
          if (isset($\_POST[\text{'}delete\text{'}]))\; \{$query = sprintf('DELETE FROM \%sSHOP_CATEGORY WHERE ' . 
                  'CATEGORY_ID = \%d',
                  DB_TBL_PREFIX,
                  $\_POST[\text{'}id\text{'}]);\; \}$// update an existing record
          else
          {
              query = sprintf('UPDATE \%sSHOP_CATEGORY SET ' . 
                  'CATEGORY_NAME = "\%s" WHERE CATEGORY_ID = \%d',
                  DB_TBL_PREFIX,
                  mysql_real_escape_string($\_POST[\text{'}name\text{'}],$GLOBALS['DB']),
                  $\_POST[\text{'}id\text{'}]);\; \}\; \}\; mysql\_query($query, $GLOBALS[\text{'}DB\text{'}]);\; \}$// return HTML for item select list
  else if (isset(_GET['retrieve_item_select']))
  {
      echo '<select id="item_select" name="item_select">';
      echo '<option>Select</option>';
      echo '<option value="new">Create New Item</option>';
  
      $query\; =\; sprintf(\text{'}SELECT\; ITEM\_ID,\; ITEM\_NAME\; FROM$\%sSHOP_INVENTORY ' .
          'WHERE CATEGORY_ID = \%d ORDER BY ITEM_NAME ASC',
          DB_TBL_PREFIX,
          _GET['id']);
      $result\; =\; mysql\_query($query, $GLOBALS[\text{'}DB\text{'}]);\; while\; ($row = mysql_fetch_assoc($result))\; \{\; echo\; \text{'}$<option value="' . row['ITEM_ID'] . '">' . $row[\text{'}ITEM\_NAME\text{'}]\; .\; \text{'}$</option>'; 
      }
      mysql_free_result(result);
  
      echo '</select>';
  }
  
  // return JSON-encoded string with item information
  else if (isset($\_GET[\text{'}retrieve\_item\text{'}]))\; \{$query = sprintf('SELECT ITEM_NAME, ITEM_DESCRIPTION, PRICE, ' . 
          'ITEM_IMAGE FROM \%sSHOP_INVENTORY WHERE ITEM_ID = \%d',
          DB_TBL_PREFIX,
          $\_GET[\text{'}id\text{'}]);$result = mysql_query($query,$GLOBALS['DB']);
  
      $row\; =\; mysql\_fetch\_assoc($result);
      echo json_encode(array(
          'item_name' => $row[\text{'}ITEM\_NAME\text{'}],\; \text{'}item\_description\text{'}\; =>$row['ITEM_DESCRIPTION'],
          'item_price' => $row[\text{'}PRICE\text{'}],\; \text{'}item\_image\text{'}\; =>$row['ITEM_IMAGE']));
  
      mysql_free_result($result);\; \}$// process save request for item information
  else if (isset(_GET['save_item']))
  {
      // create a new record
      if ($\_POST[\text{'}id\text{'}]\; ==\; \text{'}new\text{'})\; \{$query = sprintf('INSERT INTO \%sSHOP_INVENTORY (ITEM_NAME, ' . 
              'ITEM_DESCRIPTION, PRICE, ITEM_IMAGE, CATEGORY_ID) VALUES ' . 
              '("\%s", "\%s", %02f, \%d)',
              DB_TBL_PREFIX,
              mysql_real_escape_string($\_POST[\text{'}name\text{'}],$GLOBALS['DB']),
              mysql_real_escape_string($\_POST[\text{'}description\text{'}],$GLOBALS['DB']),
              $\_POST[\text{'}price\text{'}],\; mysql\_real\_escape\_string($_POST['image'], $GLOBALS[\text{'}DB\text{'}]),$_POST['cat_id']);
      }
      else
      {
          // delete an existing record
          if (isset($\_POST[\text{'}delete\text{'}]))\; \{$query = sprintf('DELETE FROM \%sSHOP_INVENTORY WHERE ' . 
                  'ITEM_ID = \%d',
                  DB_TBL_PREFIX,
                  $\_POST[\text{'}id\text{'}]);\; \}$// update an existing record
          else
          {
              query = sprintf('UPDATE \%sSHOP_INVENTORY SET ' . 
                  'ITEM_NAME = "\%s", ITEM_DESCRIPTION = "\%s", ' . 
                  'PRICE = %02d, ITEM_IMAGE = "\%s", CATEGORY_ID = \%d ' .
                  'WHERE ITEM_ID = \%d',
                  DB_TBL_PREFIX,
                  mysql_real_escape_string($\_POST[\text{'}name\text{'}],$GLOBALS['DB']),
                  mysql_real_escape_string($\_POST[\text{'}description\text{'}],$GLOBALS['DB']),
                  $\_POST[\text{'}price\text{'}],\; mysql\_real\_escape\_string($_POST['image'], $GLOBALS[\text{'}DB\text{'}]),$_POST['cat_id'],
                  $\_POST[\text{'}id\text{'}]);\; \}\; \}\; mysql\_query($query, $GLOBALS[\text{'}DB\text{'}]);\; \}\; ?>$