<?php
include_once('etc/vars.php');

// ******************************************************************
// CATEGORY FUNCTIONS
// Functions Available:
//  - getCategoryList
//    (Gets list of all top level categories.)
//  - getCategory
//    (Gets details of a particular category ID.)
//  - getSubCategoryList
//    (Gets list of all categories beneath a particular category.)
//  - getCategoryBookList
//    (Gets list of all books (with authors) available in a particular category.)
//  - getRelatedCategoryList
//    (Gets list of related categories based on the current one.)
// ******************************************************************

// **** getCategoryList Function ****
// Input: --
// Output: Category.Category_ID, Category.Title, Category.Description
// Description: Gets list of all top level categories.
// Sorted By: Alphabetical Order
function getCategoryList()
{
  global $dbPrefix;
  $queryString = 'SELECT cat_id, title, description
                  FROM `' . $dbPrefix . 'category`
                  WHERE parent = 0
                  ORDER BY title;';
  $result = dbQuery($queryString);
  return $result;
}

// **** getCategory Function ****
// Input: Category_ID
// Output: Category.Category_ID, Category.Title, Category.Description
// Description: Gets details of a particular category ID.
function getCategory($category_id)
{
  global $dbPrefix;
  $queryString = 'SELECT cat_id, title, description
                  FROM `' . $dbPrefix . 'category`
                  WHERE cat_id = ' . $category_id . ';';
  $result = dbQuery($queryString);
  return $result;
}

// **** getSubCategoryList Function ****
// Input: Category_ID
// Output: Category.Category_ID, Category.Title, Category.Description
// Description: Gets list of all categories beneath a particular category.
// Sorted By: Alphabetical Order
function getSubCategoryList($category_id)
{
  global $dbPrefix;
  $queryString = 'SELECT cat_id, title, description
                  FROM `' . $dbPrefix . 'category`
                  WHERE parent = ' . $category_id . 
                  ' ORDER BY title;';
  $result = dbQuery($queryString);
  return $result;
}

// **** getCategoryBookList Function ****
// Input: Category_ID
// Output: Book.Doc_ID, Book.Title, Author.L_Name, Author.F_Name, Book.Pub_Year, Book.Posted
// Description: Gets list of all books (with authors) available in a particular category.
// Sorted By: Alphabetical Order (on Book Title)
function getCategoryBookList($category_id)
{
  global $dbPrefix;
  $queryString = 'SELECT DISTINCT b.doc_id, b.title, a.l_name, a.f_name, b.pub_year, b.posted
                  FROM `' . $dbPrefix . 'book` AS b, `' . $dbPrefix . 'author` AS a, `' . $dbPrefix . 'bookcategory` AS bc
                  WHERE b.author_id = a.author_id
                   AND b.doc_id = bc.doc_id
                   AND bc.cat_id = ' . $category_id . 
                  ' ORDER BY b.title;';
  $result = dbQuery($queryString);
  return $result;
}

// **** getRelatedCategoryList Function ****
// Input: Category_ID
// Output: Category.Category_ID, Category.Title, Category.Description
// Description: Gets list of related categories based on the current one.
// Sorted By: Alphabetical Order (on Category Title)
function getRelatedCategoryList($category_id)
{
  global $dbPrefix;
  $queryString = 'SELECT c.cat_id, c.title, c.description
                  FROM `' . $dbPrefix . 'category` AS c, `' . $dbPrefix . 'categoryrelation` AS cr
                  WHERE c.cat_id = cr.to
                   AND cr.from = ' . $category_id . 
                  ' ORDER BY c.title;';
  $result = dbQuery($queryString);
  return $result;
}
?>