/** * Compiles a list of records * @param database - A database connector object * select categories */ function showBooks($option) { // Display table n the Books Tab $mainframe = JFactory::getApplication(); global $database, $mosConfig_list_limit; $limit = $mainframe->getUserStateFromRequest("viewlistlimit", 'limit', $mosConfig_list_limit); $limitstart = $mainframe->getUserStateFromRequest("view{$option}limitstart", 'limitstart', 0); $catid = $mainframe->getUserStateFromRequest("catid{$option}", 'catid', '-1'); //old 0 $lend = $mainframe->getUserStateFromRequest("lend{$option}", 'lend', '-1'); $pub = $mainframe->getUserStateFromRequest("pub{$option}", 'pub', '-1'); $owneremail = $mainframe->getUserStateFromRequest("owneremail{$option}", 'owneremail', '-1'); $srch_for = $mainframe->getUserStateFromRequest("srch_for{$option}", 'srch_for', '0'); // $search = $mainframe->getUserStateFromRequest("search{$option}", 'search', ''); //$search = $database->getEscaped(trim(strtolower($search))); $where = array(); if ($owneremail !== "-1" && $owneremail !== "0") { array_push($where, "jei.owneremail = '" . $owneremail . "' "); } if ($lend == "lend") { array_push($where, "jei.fk_lendid <> 0"); } else { if ($lend == "not_lend") { array_push($where, "jei.fk_lendid = 0"); } } if ($pub == "pub") { array_push($where, "jei.published = 1"); } else { if ($pub == "not_pub") { array_push($where, "jei.published = 0"); } } if ($catid > 0) { array_push($where, "jea.catid='{$catid}'"); } if ($search) { switch ($srch_for) { case 'isbn': array_push($where, "LOWER(jei.isbn) LIKE '%{$search}%'"); break; case 'title': array_push($where, "LOWER(jei.title) LIKE '%{$search}%'"); break; case 'autors': array_push($where, "LOWER(jei.authors) LIKE '%{$search}%'"); break; case 'category': array_push($where, "LOWER(cc.title) LIKE '%{$search}%'"); break; case 'publisher': array_push($where, "LOWER(jei.manufacturer) LIKE '%{$search}%'"); break; case 'description': array_push($where, "LOWER(jei.comment) LIKE '%{$search}%'"); break; case 'id': array_push($where, "LOWER(jei.bookid) LIKE '%{$search}%'"); break; default: array_push($where, "(LOWER(jei.title) LIKE '%{$search}%' OR LOWER(jei.authors) LIKE '%{$search}%' OR LOWER(jei.isbn) LIKE '%{$search}%' OR LOWER(jei.comment) LIKE '%{$search}%')"); break; } } $q = "SELECT count(*) FROM #__booklibrary_categories AS jea" . "\nLEFT JOIN #__booklibrary_main_categories AS cc ON cc.id = jea.catid" . "\nLEFT JOIN #__booklibrary AS jei ON jei.id = jea.bookid" . "\nLEFT JOIN #__booklibrary_lend AS l" . "\nON jei.fk_lendid = l.id" . (count($where) ? "\nWHERE " . implode(' AND ', $where) : ""); $database->setQuery($q); $total = $database->loadResult(); echo $database->getErrorMsg(); // Sort start // SORTING parameters start $prefix = ''; $item_session = JFactory::getSession(); $item_sort_param = mosGetParam($_GET, 'sort', ''); $item_sort_param = preg_replace('/[^A-Za-z0-9_]*/', '', $item_sort_param); if ($item_sort_param == '') { if (is_array($sort_arr = $item_session->get('bl_bn_booksort', ''))) { $sort_string = $sort_arr['field'] . " " . $sort_arr['direction']; } else { $sort_string = 'title'; $sort_arr = array(); $sort_arr['field'] = 'title'; $sort_arr['direction'] = ''; $item_session->set('bl_bn_booksort', $sort_arr); } } else { if (is_array($sort_arr = $item_session->get('bl_bn_booksort', ''))) { if ($item_sort_param == $sort_arr['field']) { if ($sort_arr['direction'] == 'DESC') { $sort_arr['direction'] = ''; } else { $sort_arr['direction'] = 'DESC'; } } else { $sort_arr['field'] = $item_sort_param; $sort_arr['direction'] = ''; } if ($sort_arr['field'] != 'category') { $prefix = 'jei.'; } $sort_string = $prefix . $sort_arr['field'] . " " . $sort_arr['direction']; } else { $sort_string = 'title'; $sort_arr = array(); $sort_arr['field'] = 'title'; $sort_arr['direction'] = ''; } $item_session->set('bl_bn_booksort', $sort_arr); } $sort_let = mosGetParam($_GET, 'sortlet', ''); $value = $sort_let; if ($sort_let != '' and $sort_let != 'none') { switch ($sort_let) { case 'lend_out': $sort_string = ""; break; case 'lend_out_desc': $sort_string = ""; break; case 'lend_until': $sort_string = "lend_until"; break; case 'lend_until_desc': $sort_string = "lend_until DESC"; break; case 'lend_from': $sort_string = "lend_from"; break; case 'lend_from_desc': $sort_string = "lend_from DESC"; break; } $sort_arr['field'] = ""; } // Sort end $pageNav = new JPagination($total, $limitstart, $limit); $selectstring = "\n\tSELECT GROUP_CONCAT(cc.title SEPARATOR ', ') AS category , jei.* , l.id as lendid, l.lend_from as lend_from, l.lend_return as lend_return, l.lend_until as lend_until, username AS owner_name\n\tFROM #__booklibrary_categories AS jea\n \nLEFT JOIN #__booklibrary_main_categories AS cc ON cc.id = jea.catid\n \nLEFT JOIN #__booklibrary AS jei ON jei.id = jea.bookid\n \nLEFT JOIN #__booklibrary_lend AS l ON l.id = jei.fk_lendid\n \nLEFT JOIN #__users AS usr ON usr.id = jei.owner_id " . (count($where) ? "\nWHERE " . implode(' AND ', $where) : "") . " GROUP BY jea.bookid " . "\nORDER BY {$sort_string}" . "\nLIMIT {$pageNav->limitstart},{$pageNav->limit};"; $database->setQuery($selectstring); $rows = $database->loadObjectList(); $date = date(time()); foreach ($rows as $row) { $check = strtotime($row->checked_out_time); $remain = $check + 7200 - $date; if ($remain <= 0 && $row->checked_out != 0) { $database->setQuery("UPDATE #__booklibrary SET checked_out=0,checked_out_time=0 WHERE id='" . $row->id . "'"); $database->query(); } } if ($database->getErrorNum()) { echo $database->stderr(); return false; } // get list of categories /* * select list treeSelectList */ $categories[] = mosHTML::makeOption('0', _BOOKLIBRARY_LABEL_SELECT_CATEGORIES); $categories[] = mosHTML::makeOption('-1', _BOOKLIBRARY_LABEL_SELECT_ALL_CATEGORIES); //************* begin add for sub category in select in manager books *************/ $options = $categories; $id = 0; $list = CAT_Utils::categoryArray(); $cat = new mainBooklibraryCategories($database); //for 1.6 $cat->load($id); $this_treename = ''; foreach ($list as $item) { if ($this_treename) { if ($item->id != $cat->id && strpos($item->treename, $this_treename) === false) { $options[] = mosHTML::makeOption($item->id, $item->treename); } } else { if ($item->id != $cat->id) { $options[] = mosHTML::makeOption($item->id, $item->treename); } else { $this_treename = "{$item->treename}/"; } } } $clist = mosHTML::selectList($options, 'catid', 'class="inputbox" size="1" onchange="document.adminForm.submit();"', 'value', 'text', $catid); //new nik edit //***** end add for sub category in select in manager books **********/ $usermenu[] = mosHTML::makeOption('0', _BOOKLIBRARY_LABEL_SELECT_USER); $usermenu[] = mosHTML::makeOption('-1', _BOOKLIBRARY_LABEL_SELECT_ALL_USERS); $selectstring = "SELECT id,name,email FROM #__users GROUP BY name ORDER BY id "; $database->setQuery($selectstring); $users_list = $database->loadObjectList(); $useranonimus = new stdClass(); $useranonimus->name = 'anonymous'; $useranonimus->email = 'anonymous'; if (count($users_list) > 1) { $users_list[count($users_list)] = $useranonimus; } if (!isset($users_list[0]->username)) { unset($users_list[0]); } if ($database->getErrorNum()) { echo $database->stderr(); return false; } $startSelecterPosition = 0; foreach ($users_list as $item) { $usermenu[2 + $startSelecterPosition] = mosHTML::makeOption($item->email, $item->name); $startSelecterPosition++; } $userlist = mosHTML::selectList($usermenu, 'name', 'class="inputbox" size="1" onchange="document.adminForm.submit();"', 'value', 'text', $owneremail); $lendmenu[] = mosHTML::makeOption('0', _BOOKLIBRARY_LABEL_SELECT_TO_LEND); $lendmenu[] = mosHTML::makeOption('-1', _BOOKLIBRARY_LABEL_SELECT_ALL_LEND); $lendmenu[] = mosHTML::makeOption('not_lend', _BOOKLIBRARY_LABEL_SELECT_NOT_LEND); $lendmenu[] = mosHTML::makeOption('lend', _BOOKLIBRARY_LABEL_SELECT_LEND); $lendlist = mosHTML::selectList($lendmenu, 'lend', 'class="inputbox" size="1" onchange="document.adminForm.submit();"', 'value', 'text', $lend); $pubmenu[] = mosHTML::makeOption('0', _BOOKLIBRARY_LABEL_SELECT_TO_PUBLIC); $pubmenu[] = mosHTML::makeOption('-1', _BOOKLIBRARY_LABEL_SELECT_ALL_PUBLIC); $pubmenu[] = mosHTML::makeOption('not_pub', _BOOKLIBRARY_LABEL_SELECT_NOT_PUBLIC); $pubmenu[] = mosHTML::makeOption('pub', _BOOKLIBRARY_LABEL_SELECT_PUBLIC); $publist = mosHTML::selectList($pubmenu, 'pub', 'class="inputbox" size="1" onchange="document.adminForm.submit();"', 'value', 'text', $pub); $search_for[] = mosHTML::makeOption('0', _BOOKLIBRARY_SHOW_SEARCH_FOR_ALL); $search_for[] = mosHTML::makeOption('id', _BOOKLIBRARY_LABEL_BOOKID); $search_for[] = mosHTML::makeOption('isbn', _BOOKLIBRARY_SHOW_SEARCH_FOR_ISBN); $search_for[] = mosHTML::makeOption('title', _BOOKLIBRARY_SHOW_SEARCH_FOR_TITLE); $search_for[] = mosHTML::makeOption('autors', _BOOKLIBRARY_SHOW_SEARCH_FOR_AUTORS); $search_for[] = mosHTML::makeOption('category', _BOOKLIBRARY_SHOW_SEARCH_FOR_CATEGORY); $search_for[] = mosHTML::makeOption('publisher', _BOOKLIBRARY_SHOW_SEARCH_FOR_PUBLISHER); $search_for[] = mosHTML::makeOption('description', _BOOKLIBRARY_SHOW_SEARCH_FOR_DESCRIPTION); $search_for_list = mosHTML::selectList($search_for, 'srch_for', 'class="inputbox" size="1" ', 'value', 'text', $srch_for); HTML_booklibrary::showBooks($option, $rows, $clist, $lendlist, $userlist, $publist, $search, $pageNav, $sort_arr, $search_for_list, $value); }