<?php
// $Id$

function textbook_companion_search_form($form,&$form_state)
{
 // $form['#redirect'] = FALSE;
  $form['search'] = array(
      '#type' => 'textfield',
      '#title' => t('Search'),
      '#size' => 48,
  );
 
  $form['sort_by'] = array(
      '#type' => 'radios',
      '#default_value' => TRUE,
      '#title' => t('Sort by'),
      '#options' => array('title'=>t('Title'),'author'=>t('Author'),'contributor'=>t('Contributor')),
      '#default_value' => 'title',
  );

  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Search'),
    '#ajax' => array(
		 'callback' => 'textbook_companion_search_ajax_callback',
		),
  );
  $form['cancel'] = array(
    '#type' => 'item',
    '#markup' => l(t('Cancel'), ''),
  );
  $form['search_results'] = array(
          '#type' => 'item',
        '#prefix' => '<div id ="search-result">',
         // '#title' => t('Search results for "') . $_POST['search'] . '"',
          '#markup' => '',
        '#suffix' => '</div>',
      );

  return $form;
}
function textbook_companion_search_ajax_callback($form,$form_state){
    if ($form_state['values']['sort_by'] == 'title' ){
          $search_q = db_query("
          SELECT pe.id AS pref_id, po.full_name, pe.book as book, pe.author as author,
          pe.publisher as publisher, pe.year as year, pe.id as pe_id, po.approval_date as approval_date
          FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id
          WHERE po.proposal_status = 3 AND pe.approval_status = 1 AND
          (book LIKE :book OR author LIKE :author OR po.full_name LIKE :contributor) ORDER BY pe.book ASC
          ",
          array(":book" => '%%'.trim($form_state['values']['search']).'%%',
                 ":author" => '%%'.trim($form_state['values']['search']).'%%',
                 ":contributor" => '%%'.trim($form_state['values']['search']).'%%'
                  ));

while ($search_data = $search_q->fetchObject())
    {
      $search_rows[] = array(l($search_data->book, 'textbook_run/' . $search_data->pref_id), $search_data->author, $search_data->full_name);
    }

    if ($search_rows)
    {
      $search_header = array('Title of the Book', 'Author Name', 'Contributor');
      $output .= theme('table',array('header'=>$search_header, 'rows'=>$search_rows));
      $form['search_results']['#markup'] = $output;
      $form['search_results']['#title'] =  t('Search results for "') . $_POST['search'] . '"';

      }
     else{
      $form['search_results']['#title'] =  t('Search results for "') . $_POST['search'] . '" is not available';
   
 }

    }elseif ($form_state['values']['sort_by'] =='author'){
                        $search_q = db_query("
          SELECT pe.id AS pref_id, po.full_name, pe.book as book, pe.author as author,
          pe.publisher as publisher, pe.year as year, pe.id as pe_id, po.approval_date as approval_date
          FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id
          WHERE po.proposal_status = 3 AND pe.approval_status = 1 AND
          (book LIKE :book OR author LIKE :author OR po.full_name LIKE :contributor) ORDER BY author ASC
          ",
          array(":book" => '%%'.trim($form_state['values']['search']).'%%',
                 ":author" => '%%'.trim($form_state['values']['search']).'%%',
                 ":contributor" => '%%'.trim($form_state['values']['search']).'%%'
                  ));
while ($search_data = $search_q->fetchObject())
    {
      $search_rows[] = array($search_data->author,l($search_data->book, 'textbook_run/' . $search_data->pref_id),  $search_data->full_name);
    }

    if ($search_rows)
    {
      $search_header = array('Author Name', 'Title of the Book', 'Contributor');
      $output .= theme('table',array('header'=>$search_header, 'rows'=>$search_rows));
      $form['search_results']['#markup'] = $output;
      $form['search_results']['#title'] =  t('Search results for "') . $_POST['search'] . '"';

      }
     else{
      $form['search_results']['#title'] =  t('Search results for "') . $_POST['search'] . '" is not available';
   
 }
 	
	}else if ($form_state['values']['sort_by']== 'contributor'){
                        $search_q = db_query("
          SELECT pe.id AS pref_id, po.full_name, pe.book as book, pe.author as author,
          pe.publisher as publisher, pe.year as year, pe.id as pe_id, po.approval_date as approval_date
          FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id
          WHERE po.proposal_status = 3 AND pe.approval_status = 1 AND
          (book LIKE :book OR author LIKE :author OR po.full_name LIKE :contributor) ORDER BY po.full_name ASC
          ",
          array(":book" => '%%'.trim($form_state['values']['search']).'%%',
                 ":author" => '%%'.trim($form_state['values']['search']).'%%',
                 ":contributor" => '%%'.trim($form_state['values']['search']).'%%'
                  ));
while ($search_data = $search_q->fetchObject())
    {
      $search_rows[] = array($search_data->full_name, l($search_data->book, 'textbook_run/' . $search_data->pref_id),  $search_data->author);
    }

    if ($search_rows)
    {
      $search_header = array('Contributor', 'Title of the Book', 'Author Name');
      $output .= theme('table',array('header'=>$search_header, 'rows'=>$search_rows));
      $form['search_results']['#markup'] = $output;
      $form['search_results']['#title'] =  t('Search results for "') . $_POST['search'] . '"';

      }
     else{
      $form['search_results']['#title'] =  t('Search results for "') . $_POST['search'] . '" is not available';
   
 }
}
     $commands[] = ajax_command_replace("#search-result", drupal_render($form['search_results']));

     return array('#type' => 'ajax', '#commands' => $commands);
}

function textbook_search_display($search_q){
while ($search_data = $search_q->fetchObject())
    {
      $search_rows[] = array(l($search_data->book, 'textbook_run/' . $search_data->id), $search_data->author);
    }

    if ($search_rows)
    {
      $search_header = array('Title of the Book', 'Author Name');
      $output .= theme('table',array('header'=>$search_header, 'rows'=>$search_rows));
      }
 else{$output = 'not available'; }
    return $output;
      

}
/******************************************************************************/
/**************************** BROWSE BY FORMS *********************************/
/******************************************************************************/

function textbook_companion_browse_book()
{
  $return_html = _browse_list('book');
  $return_html .= '<br /><br />';
  $query_character = arg(2);
  if (!$query_character) {
    /* all books */
    $return_html .= "Please select the starting character of the title of the book";
    return $return_html;
  }
  $book_rows = array();

  /*$book_q = db_query("SELECT * FROM {textbook_companion_preference} WHERE book like '%s%%' AND approval_status = 1", $query_character);*/

	$query = db_select('textbook_companion_preference');
	$query->fields('textbook_companion_preference');
	$query->condition('book', ''.$query_character.'%%', 'like');
	$query->condition('approval_status', 1);
	$book_q = $query->execute();

  while ($book_data = $book_q->fetchObject())
  {
    $book_rows[] = array(l($book_data->book, 'textbook_run/' . $book_data->id), $book_data->author);
  }
  if (!$book_rows)
  {
    $return_html .= "Sorry no books are available with that title";
  } else {
      $book_header = array('Title of the Book', 'Author Name');
      $return_html .= theme('table',array('headers'=>$book_header,'rows'=> $book_rows));
  }
  return $return_html;
}

function textbook_companion_browse_author()
{
  $return_html = _browse_list('author');
  $return_html .= '<br /><br />';
  $query_character = arg(2);
  if (!$query_character) {
    /* all books */
    $return_html .= "Please select the starting character of the author's name";
    return $return_html;
  }
  $book_rows = array();

  /*$book_q = db_query("SELECT pe.book as book, pe.author as author, pe.publisher as publisher, pe.year as year, pe.id as id FROM {textbook_companion_preference} pe RIGHT JOIN  {textbook_companion_proposal} po on pe.proposal_id=po.id  WHERE po.proposal_status=3 and pe.approval_status = 1", $query_character);*/

	$query = db_select('textbook_companion_preference', 'pe');
	$query->fields('pe', array('book', 'author', 'publisher', 'year', 'id'));
	$query->rightJoin('textbook_companion_proposal', 'po', 'pe.proposal_id = po.id');
	$query->condition('po.proposal_status', 3);
	$query->condition('pe.approval_status', 1);
	$book_q = $query->execute();

  while ($book_data = $book_q->fetchObject())
  {
    /* Initial's fix algorithm */
    preg_match_all("/{$query_character}[a-z]+/", $book_data->author, $matches);

    if (count($matches) > 0) {
      /* Remove the word "And"/i from the match array and make match bold */
        if (count($matches[0]) > 0) {
          
          foreach ($matches[0] as $key => $value) {
            
            if (strtolower($value) == "and") {
              unset($matches[$key]);
            }
            else {
              $matches[0][$key] = "<b>" . $value . "</b>";
              $book_data->author = str_replace($value, $matches[0][$key], $book_data->author);
            }
          }            
        }

        /* Check count of matches after removing And */
        if (count($matches[0]) > 0) {
          $book_rows[] = array(l($book_data->book, 'textbook_run/' . $book_data->id), $book_data->author);
        }
    }
  }


  if (!$book_rows)
  {
    $return_html .= "Sorry no books are available with that author's name";
  } else {
      $book_header = array('Title of the Book', 'Author Name');
      $return_html .= theme('table',array('headers'=>$book_header,'rows'=>$book_rows));
  }
  return $return_html;
}

function textbook_companion_browse_student()
{
  $return_html = _browse_list('student');
  $return_html .= '<br /><br />';
  $query_character = arg(2);
  //print $query_character;
  //die();
  if (!$query_character) {
    /* all books */
    $return_html .= "Please select the starting character of the student's name";
    return $return_html;
  }
  $book_rows = array();

  /*$student_q = db_query("
                        SELECT po.full_name, pe.book as book, pe.author as author, pe.publisher as publisher, pe.year as year, pe.id as pe_id, po.approval_date as approval_date
                        FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id 
                        WHERE po.proposal_status = 3 AND pe.approval_status = 1 AND full_name LIKE '%s%%'
                        ", $query_character);*/

	$query = db_select('textbook_companion_preference', 'pe');
	$query->fields('po', array('full_name', 'approval_date'));
	$query->fields('pe', array('book', 'author', 'publisher', 'year', 'id'));
	$query->leftJoin('textbook_companion_proposal', 'po', 'pe.proposal_id = po.id');
	$query->condition('po.proposal_status', 3);
	$query->condition('pe.approval_status', 1);
	$query->condition('full_name', ''.$query_character.'%%', 'LIKE');
	$student_q = $query->execute();
 
  while ($student_data = $student_q->fetchObject())
  {   
        $book_rows[] = array(l($student_data->book, 'textbook_run/' . $student_data->pe_id), $student_data->full_name);   
  }
  if (!$book_rows)
  {
    $return_html .= "Sorry no books are available with that student's name";
  } else {
      $book_header = array('Title of the Book', 'Student Name');
      $return_html .= theme('table',array('headers'=>$book_header, 'rows'=>$book_rows));
  }
  return $return_html;
}

function _browse_list($type)
{
  $return_html = '';
  $char_list = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
  foreach ($char_list as $char_name)
  {
    $return_html .= l($char_name, 'textbook_search/' . $type . '/' . $char_name);
    if ($char_name != 'Z')
      $return_html .= ' | ';
  }
  return '<div id="filter-links">' . $return_html . "</div>";
}
function _list_of_colleges()
{
  $college_names = array('0' => '--- select ---');

  $college_names_q = db_query("SELECT DISTINCT university FROM {textbook_companion_proposal} WHERE proposal_status=1 OR proposal_status=3 ORDER BY university ASC");

	/*$query = db_select('textbook_companion_proposal');
	$query =distinct();
	$query->fields('university', array(''));
	$or = db_or();
	$or->condition('proposal_status', 1);
	$or->condition('proposal_status', 3);
	$query->condition($or);
	$query->orderBy('university', 'ASC');*/
	$college_names_q =  $college_names_q->execute();

  while ($college_names_data = $college_names_q->fetchObject())
  {
    $college_names[$college_names_data->university] = $college_names_data->university;
  }
  return $college_names;
}

function _list_books_by_college($college) {

	/*$query = "SELECT pro.full_name, pro.proposal_status, pre.id as preid, pre.book, pre.isbn FROM textbook_companion_proposal pro, textbook_companion_preference pre WHERE pro.university='".$college."' AND (pro.proposal_status=1 OR pro.proposal_status=3) AND pre.proposal_id=pro.id AND pre.approval_status=1";
	$result = db_query($query);*/

	$query = db_select('textbook_companion_proposal', 'pro');
	$query->fields('pro', array('full_name', 'proposal_status'));
	$query->fields('pre', array('id', 'book', 'isbn'));
	$query->condition('pro.university', $college);

	$or = db_or();
	$or->condition('pro.proposal_status', 1);
	$or->condition('pro.proposal_status', 3);
	$query->condition($or);
	$query->condition('pre.proposal_id', 'pro.id');
	$query->condition('pre.approval_status', 1);
	$result = $query->execute();

	$output = '<table><tr><th>SNO</th><th>Name</th><th>Book</th><th>ISBN</th><th>Status</th></tr>';
	$sno = 1;
	while($row = $result->fetchObject()) {
		
		if($row->proposal_status == 1) {
			$output .= '<tr><td>'.$sno++.'</td><td>'.$row->full_name.'</td><td>'.$row->book.'</td><td>'.str_replace("-", "", $row->isbn).'</td><td style="color: orange;">Approved</td>';
		}else {
			$output .= '<tr><td>'.$sno++.'</td><td>'.$row->full_name.'</td><td><a target="_blank" href="'.base_path().'textbook_run/'.$row->preid.'">'.$row->book.'</a></td><td>'.str_replace("-", "", $row->isbn).'</td><td style="color: green;">Completed</td>';
		}
		$output .= '</tr>';
	}
	
	return $output;
}

function textbook_companion_browse_college_form($form, $form_state) {
  $form = array();

  //ahah_helper_register($form, $form_state);

  
  if (!isset($form_state['storage']['college_info']['college'])) {
    $usage_default_value = '0';
  }
  else {
    $usage_default_value =  $form_state['storage']['college_info']['college'];
  }

  $form['college_info'] = array(
    '#type'   => 'fieldset',
    '#prefix' => '<div id="college-info-wrapper">',
    '#suffix' => '</div>',
    '#tree'   => TRUE,
  );
  $form['college_info']['college'] = array(
    '#type' => 'select',
    '#title' => t('College Name'),
    '#options' => _list_of_colleges(),
    '#default_value' => $usage_default_value,
  /*  '#ahah' => array(
      'event'   => 'change',
      'path'    => ahah_helper_path(array('college_info')),
      'wrapper' => 'college-info-wrapper',
    ),*/
  );
  
  
  if ($usage_default_value != '0') {
    $form['college_info']['book_details'] = array(
			'#type' => 'item',
			'#value' => _list_books_by_college($usage_default_value)
		);
  }

  return $form;
}

function textbook_companion_browse_college_form_validate($form, &$form_state) {
	
}

function textbook_companion_browse_college_form_submit($form, &$form_state) {
  
}
SNO	Name	Book	ISBN	Status
'.$sno++.'	'.$row->full_name.'	'.$row->book.'	'.str_replace("-", "", $row->isbn).'	Approved
'.$sno++.'	'.$row->full_name.'	'.$row->book.'	'.str_replace("-", "", $row->isbn).'	Completed