'.__('You don\'t have enough privileges to access this area!').''); } require SIMBIO.'simbio_GUI/table/simbio_table.inc.php'; require SIMBIO.'simbio_GUI/paging/simbio_paging.inc.php'; require SIMBIO.'simbio_GUI/form_maker/simbio_form_element.inc.php'; require SIMBIO.'simbio_DB/datagrid/simbio_dbgrid.inc.php'; require MDLBS.'reporting/report_dbgrid.inc.php'; $page_title = 'Titles Report'; $reportView = false; $num_recs_show = 20; if (isset($_GET['reportView'])) { $reportView = true; } if (!$reportView) { ?>

query('SELECT gmd_id, gmd_name FROM mst_gmd'); $gmd_options[] = array('0', __('ALL')); while ($gmd_d = $gmd_q->fetch_row()) { $gmd_options[] = array($gmd_d[0], $gmd_d[1]); } echo simbio_form_element::selectList('gmd[]', $gmd_options, '','multiple="multiple" size="5"'); ?>
query('SELECT coll_type_id, coll_type_name FROM mst_coll_type'); $coll_type_options = array(); $coll_type_options[] = array('0', __('ALL')); while ($coll_type_d = $coll_type_q->fetch_row()) { $coll_type_options[] = array($coll_type_d[0], $coll_type_d[1]); } echo simbio_form_element::selectList('collType[]', $coll_type_options, '', 'multiple="multiple" size="5"'); ?>
query('SELECT language_id, language_name FROM mst_language'); $lang_options = array(); $lang_options[] = array('0', __('ALL')); while ($lang_d = $lang_q->fetch_row()) { $lang_options[] = array($lang_d[0], $lang_d[1]); } echo simbio_form_element::selectList('language', $lang_options); ?>
query('SELECT location_id, location_name FROM mst_location'); $loc_options = array(); $loc_options[] = array('0', __('ALL')); while ($loc_d = $loc_q->fetch_row()) { $loc_options[] = array($loc_d[0], $loc_d[1]); } echo simbio_form_element::selectList('location', $loc_options); ?>
setSQLColumn('b.biblio_id', 'b.title AS \''.__('Title').'\'', 'COUNT(item_id) AS \''.__('Copies').'\'', 'pl.place_name AS \''.__('Publishing Place').'\'', 'pb.publisher_name AS \''.__('Publisher').'\'', 'b.publish_year AS \''.__('Year').'\'', 'b.call_number AS \''.__('Call Number').'\''); $reportgrid->setSQLorder('b.title ASC'); $reportgrid->invisible_fields = array(0); // is there any search $criteria = 'bsub.biblio_id IS NOT NULL '; $outer_criteria = 'b.biblio_id > 0 '; if (isset($_GET['title']) AND !empty($_GET['title'])) { $keyword = $dbs->escape_string(trim($_GET['title'])); $words = explode(' ', $keyword); if (count($words) > 1) { $concat_sql = ' AND ('; foreach ($words as $word) { $concat_sql .= " (bsub.title LIKE '%$word%' OR bsub.isbn_issn LIKE '%$word%') AND"; } // remove the last AND $concat_sql = substr_replace($concat_sql, '', -3); $concat_sql .= ') '; $criteria .= $concat_sql; } else { $criteria .= ' AND (bsub.title LIKE \'%'.$keyword.'%\' OR bsub.isbn_issn LIKE \'%'.$keyword.'%\')'; } } if (isset($_GET['author']) AND !empty($_GET['author'])) { $author = $dbs->escape_string($_GET['author']); $criteria .= ' AND ma.author_name LIKE \'%'.$author.'%\''; } if (isset($_GET['class']) AND !empty($_GET['class'])) { $class = $dbs->escape_string($_GET['class']); $criteria .= ' AND bsub.classification LIKE \''.$class.'%\''; } if (isset($_GET['gmd']) AND !empty($_GET['gmd'])) { $gmd_IDs = ''; foreach ($_GET['gmd'] as $id) { $id = (integer)$id; if ($id) { $gmd_IDs .= "$id,"; } } $gmd_IDs = substr_replace($gmd_IDs, '', -1); if ($gmd_IDs) { $outer_criteria .= " AND b.gmd_id IN($gmd_IDs)"; } } if (isset($_GET['collType'])) { $coll_type_IDs = ''; foreach ($_GET['collType'] as $id) { $id = (integer)$id; if ($id) { $coll_type_IDs .= "$id,"; } } $coll_type_IDs = substr_replace($coll_type_IDs, '', -1); if ($coll_type_IDs) { $outer_criteria .= " AND i.coll_type_id IN($coll_type_IDs)"; } } if (isset($_GET['language']) AND !empty($_GET['language'])) { $language = $dbs->escape_string(trim($_GET['language'])); $criteria .= ' AND bsub.language_id=\''.$language.'\''; } if (isset($_GET['location']) AND !empty($_GET['location'])) { $location = $dbs->escape_string(trim($_GET['location'])); $outer_criteria .= ' AND i.location_id=\''.$location.'\''; } if (isset($_GET['recsEachPage'])) { $recsEachPage = (integer)$_GET['recsEachPage']; $num_recs_show = ($recsEachPage >= 20 && $recsEachPage <= 200)?$recsEachPage:$num_recs_show; } // subquery/view string $subquery_str = '(SELECT DISTINCT bsub.biblio_id, bsub.gmd_id, bsub.title, bsub.isbn_issn, bsub.publish_year, bsub.call_number, bsub.classification, bsub.language_id, bsub.publish_place_id, bsub.publisher_id FROM biblio AS bsub LEFT JOIN biblio_author AS ba ON bsub.biblio_id = ba.biblio_id LEFT JOIN mst_author AS ma ON ba.author_id = ma.author_id LEFT JOIN biblio_topic AS bt ON bsub.biblio_id = bt.biblio_id LEFT JOIN mst_topic AS mt ON bt.topic_id = mt.topic_id WHERE '.$criteria.')'; // table spec $table_spec = $subquery_str.' AS b LEFT JOIN item AS i ON b.biblio_id=i.biblio_id LEFT JOIN mst_place AS pl ON b.publish_place_id=pl.place_id LEFT JOIN mst_publisher AS pb ON b.publisher_id=pb.publisher_id'; // set group by $reportgrid->sql_group_by = 'b.biblio_id'; $reportgrid->setSQLCriteria($outer_criteria); // callback function to show title and authors function showTitleAuthors($obj_db, $array_data) { // author name query $_biblio_q = $obj_db->query('SELECT b.title, a.author_name FROM biblio AS b LEFT JOIN biblio_author AS ba ON b.biblio_id=ba.biblio_id LEFT JOIN mst_author AS a ON ba.author_id=a.author_id WHERE b.biblio_id='.$array_data[0]); $_authors = ''; while ($_biblio_d = $_biblio_q->fetch_row()) { $_title = $_biblio_d[0]; $_authors .= $_biblio_d[1].' - '; } $_authors = substr_replace($_authors, '', -3); $_output = $_title.'
'.$_authors.''."\n"; return $_output; } // modify column value $reportgrid->modifyColumnContent(1, 'callback{showTitleAuthors}'); // put the result into variables echo $reportgrid->createDataGrid($dbs, $table_spec, $num_recs_show); echo ''; $xlsquery = 'SELECT b.biblio_id, b.title AS \''.__('Title').'\', COUNT(item_id) AS \''.__('Copies').'\''. ', pl.place_name AS \''.__('Publishing Place').'\''. ', pb.publisher_name AS \''.__('Publisher').'\''. ', b.isbn_issn AS \''.__('ISBN/ISSN').'\', b.call_number AS \''.__('Call Number').'\' FROM '. $table_spec . ' WHERE '. $outer_criteria . ' group by b.biblio_id'; // echo $xlsquery; unset($_SESSION['xlsdata']); $_SESSION['xlsquery'] = $xlsquery; $_SESSION['tblout'] = "title_list"; echo '

'.__('Export to spreadsheet format').'

'; $content = ob_get_clean(); // include the page template require SB.'/admin/'.$sysconf['admin_template']['dir'].'/printed_page_tpl.php'; }