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

setSQLColumn('m.member_id AS \''.__('Member ID').'\''); $reportgrid->setSQLorder('l.due_date DESC'); $reportgrid->sql_group_by = 'm.member_id'; $overdue_criteria = ' (l.is_lent=1 AND l.is_return=0 AND TO_DAYS(due_date) < TO_DAYS(\''.date('Y-m-d').'\')) '; // is there any search if (isset($_GET['id_name']) AND $_GET['id_name']) { $keyword = $dbs->escape_string(trim($_GET['id_name'])); $words = explode(' ', $keyword); if (count($words) > 1) { $concat_sql = ' ('; foreach ($words as $word) { $concat_sql .= " (m.member_id LIKE '%$word%' OR m.member_name LIKE '%$word%') AND"; } // remove the last AND $concat_sql = substr_replace($concat_sql, '', -3); $concat_sql .= ') '; $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 = (integer)$_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, b.title, l.loan_date, l.due_date, (TO_DAYS(DATE(NOW()))-TO_DAYS(due_date)) AS \'Overdue Days\' FROM loan AS l LEFT JOIN item AS i ON l.item_code=i.item_code LEFT JOIN biblio AS b ON i.biblio_id=b.biblio_id WHERE (l.is_lent=1 AND l.is_return=0 AND TO_DAYS(due_date) < TO_DAYS(\''.date('Y-m-d').'\')) AND l.member_id=\''.$array_data[0].'\''.( !empty($date_criteria)?$date_criteria:'' )); $_buffer = '

'.$member_name.' ('.$array_data[0].')

'; $_buffer .= '

'.$member_mail_address.'

'; $_buffer .= '

'.__('E-mail').': '.$member_d[1].' - Send Notification e-mail - '.__('Phone Number').': '.$member_d[2].'

'; $_buffer .= ''; while ($ovd_title_d = $ovd_title_q->fetch_assoc()) { $_buffer .= ''; $_buffer .= ''; $_buffer .= ''; $_buffer .= ''; $_buffer .= ''; $_buffer .= ''; } $_buffer .= '

'.$ovd_title_d['item_code'].'

'.$ovd_title_d['title'].'

'.__('Price').': '.$ovd_title_d['price'].' '.$ovd_title_d['price_currency'].'

'.__('Overdue').': '.$ovd_title_d['Overdue Days'].' '.__('day(s)').'

'.__('Loan Date').': '.$ovd_title_d['loan_date'].' '.__('Due Date').': '.$ovd_title_d['due_date'].'

'; return $_buffer; } // modify column value $reportgrid->modifyColumnContent(0, 'callback{showOverduedList}'); // put the result into variables echo $reportgrid->createDataGrid($dbs, $table_spec, $num_recs_show); ?>