// subquery/view string
$subquery_str = '(SELECT DISTINCT bsub.biblio_id, bsub.gmd_id, bsub.title, bsub.isbn_issn, 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 . '<br /><i>' . $_authors . '</i>' . "\n";
$gender = intval($_GET['gender']);
$criteria .= ' AND m.gender=' . $gender;
}
if (isset($_GET['address']) and !empty($_GET['address'])) {
$address = $dbs->escape_string(trim($_GET['address']));
$criteria .= ' AND m.member_address LIKE \'%' . $address . '%\'';
}
// register date
if (isset($_GET['startDate']) and isset($_GET['untilDate'])) {
$criteria .= ' AND (TO_DAYS(m.register_date) BETWEEN TO_DAYS(\'' . $_GET['startDate'] . '\') AND
TO_DAYS(\'' . $_GET['untilDate'] . '\'))';
}
if (isset($_GET['recsEachPage'])) {
$recsEachPage = (int) $_GET['recsEachPage'];
$num_recs_show = $recsEachPage >= 20 && $recsEachPage <= 200 ? $recsEachPage : $num_recs_show;
}
$reportgrid->setSQLCriteria($criteria);
// put the result into variables
echo $reportgrid->createDataGrid($dbs, $table_spec, $num_recs_show);
echo '<script type="text/javascript">' . "\n";
echo 'parent.$(\'#pagingBox\').html(\'' . str_replace(array("\n", "\r", "\t"), '', $reportgrid->paging_set) . '\');' . "\n";
echo '</script>';
$xlsquery = 'SELECT m.member_id AS \'' . __('Member ID') . '\'' . ', m.member_name AS \'' . __('Member Name') . '\'' . ', mt.member_type_name AS \'' . __('Membership Type') . '\' FROM ' . $table_spec . ' WHERE ' . $criteria;
unset($_SESSION['xlsdata']);
$_SESSION['xlsquery'] = $xlsquery;
$_SESSION['tblout'] = "member_list";
echo '<p><a href="../xlsoutput.php" class="button">' . __('Export to spreadsheet format') . '</a></p>';
$content = ob_get_clean();
// include the page template
require SB . '/admin/' . $sysconf['admin_template']['dir'] . '/printed_page_tpl.php';
}
$overdue_criteria .= ' AND ' . $concat_sql;
} else {
$overdue_criteria .= " AND m.member_id LIKE '%{$keyword}%' OR m.member_name LIKE '%{$keyword}%'";
}
}
// loan date
if (isset($_GET['startDate']) and isset($_GET['untilDate'])) {
$date_criteria = ' AND (TO_DAYS(l.loan_date) BETWEEN TO_DAYS(\'' . $_GET['startDate'] . '\') AND
TO_DAYS(\'' . $_GET['untilDate'] . '\'))';
$overdue_criteria .= $date_criteria;
}
if (isset($_GET['recsEachPage'])) {
$recsEachPage = (int) $_GET['recsEachPage'];
$num_recs_show = $recsEachPage >= 5 && $recsEachPage <= 200 ? $recsEachPage : $num_recs_show;
}
$reportgrid->setSQLCriteria($overdue_criteria);
// set table and table header attributes
$reportgrid->table_attr = 'align="center" class="dataListPrinted" cellpadding="5" cellspacing="0"';
$reportgrid->table_header_attr = 'class="dataListHeaderPrinted"';
$reportgrid->column_width = array('1' => '80%');
// callback function to show overdued list
function showOverduedList($obj_db, $array_data)
{
global $date_criteria;
// member name
$member_q = $obj_db->query('SELECT member_name, member_email, member_phone, member_mail_address FROM member WHERE member_id=\'' . $array_data[0] . '\'');
$member_d = $member_q->fetch_row();
$member_name = $member_d[0];
$member_mail_address = $member_d[3];
unset($member_q);
$ovd_title_q = $obj_db->query('SELECT l.item_code, i.price, i.price_currency,
