0.5.1 or lower? If so then this OpenBiblio 0.6.x report is for you!
Note: OpenBiblio 0.5.1 or lower stored member fields School Grade and Additional search criteria:
Additional sorting options:
Layout: code for overdue .pdf layout appended below.
.title "Over Due Member List: Grade, Teacher"
.category Circulation
.layout overdue title="Overdue Letters"
.column bibid hidden
.column copyid hidden
.column mbrid hidden
.column barcode_nmbr sort=barcode_nmbr
.column title sort=title func=biblio_link
.column author sort=author
.column member_bcode sort=member_bcode
.column name sort=name func=member_link
.column school_grade title="Grade" sort=school_grade
.column school_teacher title="Teacher" sort=school_teacher
.column status_begin_dt sort=status_begin_dt
.column due_back_dt sort=due_back_dt
.column days_late sort=days_late
.parameters
. date as_of title="As of" default="today"
. select grade_sel title="Grade"
. item ''
.sql
# To prevent error [Unexpected token "SQLCODE" expecting "end"] ,
# SQL section in RPT .select parameter is one (long) line, not wrapped.
# This occurs only with 0.6.0 file release, bug is fixed in CVS.
SELECT DISTINCT data AS value, data AS title FROM member_fields WHERE code = 'schoolGrade' ORDER BY title
.end sql
. end select
. select criteria1 default=start title="... or Grade"
. item start title="Starts With"
. item trunc title="Contains"
. end select
. string grade title="..."
. select criteria2 default=trunc title="Teacher Name"
. item start title="Starts With"
. item trunc title="Contains"
. end select
. string teacher title="..."
. order_by default=school_grade
. item barcode_nmbr expr='c.barcode_nmbr'
. item title
. item author
. item member_bcode expr='m.barcode_nmbr'
. item name expr="concat(m.last_name, ', ', m.first_name)"
. item school_grade title="Grade, Name" expr="concat_ws(' ', school_grade, m.last_name, ', ', m.first_name)"
. item school_teacher title="Teacher, Grade, Name" expr="concat_ws(' ', school_teacher, school_grade, m.last_name, ', ', m.first_name)"
. item status_begin_dt
. item due_back_dt
. end order_by
.end parameters
.sql
SELECT c.bibid, c.copyid, m.mbrid, c.barcode_nmbr,
b.title, b.author, c.status_begin_dt,
c.due_back_dt, m.barcode_nmbr member_bcode,
concat(m.last_name, ', ', m.first_name) name,
school_grade.data school_grade, school_teacher.data school_teacher,
floor(to_days(now())-to_days(c.due_back_dt)) days_late
FROM biblio b, biblio_copy c, member m
LEFT JOIN member_fields AS school_grade ON school_grade.mbrid=m.mbrid
AND school_grade.code='schoolGrade'
LEFT JOIN member_fields AS school_teacher ON school_teacher.mbrid=m.mbrid
AND school_teacher.code='schoolTeacher'
WHERE b.bibid = c.bibid
AND c.mbrid = m.mbrid
AND c.status_cd = 'out'
. if_set as_of
AND c.due_back_dt < %as_of%
. end if_set
. if_set grade
. if_equal criteria1 start
AND school_grade.data LIKE '%"grade%%%'
. end if_set
. if_equal criteria1 trunc
AND school_grade.data LIKE '%%%"grade%%%'
. end if_set
. else
. if_not_equal grade_sel ''
AND school_grade.data = %grade_sel%
. end if_set
. end if_set
. if_set teacher
. if_equal criteria2 start
AND school_teacher.data LIKE '%"teacher%%%'
. end if_set
. if_equal criteria2 trunc
AND school_teacher.data LIKE '%%%"teacher%%%'
. end if_set
. end if_set
. order_by_expr
.end sql
Create file /layouts/overdue.php
<?php
/* This file is part of a copyrighted work; it is distributed with NO WARRANTY.
* See the file COPYRIGHT.html for more details.
*/
require_once('../classes/Lay.php');
require_once('../classes/MemberQuery.php');
class Layout_overdue {
function render($rpt) {
list($rpt, $errs) = $rpt->variant_el(array('order_by'=>'member'));
if (!empty($errs)) {
Fatal::internalError('Unexpected report error');
}
$mbrQ = new MemberQuery;
$lay = new Lay;
$lay->pushFont('Helvetica', 10);
$lay->container('Columns', array(
'margin-left'=>'1in', 'margin-right'=>'1in',
'margin-top'=>'1in', 'margin-bottom'=>'1in',
));
$mbr = NULL;
$oldmbr = NULL;
while ($row = $rpt->each()) {
if ($row['mbrid'] != $oldmbr) {
if ($oldmbr !== NULL) {
$lay->close();
$lay->container('Columns', array(
'margin-left'=>'1in', 'margin-right'=>'1in',
'margin-top'=>'1in', 'margin-bottom'=>'1in',
));
}
$mbr = $mbrQ->get($row['mbrid']);
$oldmbr = $row['mbrid'];
$lay->container('TextLine');
$lay->text('Grade: '.$row['school_grade']);
$lay->close();
$lay->container('TextLine');
$lay->text('Teacher: '.$row['school_teacher']);
$lay->close();
$lay->container('Column', array('margin-left'=>'3.25in'));
$lay->container('TextLine');
$lay->text(date('m/d/Y'));
$lay->close();
$lay->element('Spacer', array('height'=>14));
$lines = array(
OBIB_LIBRARY_NAME,
'101 1st Street',
'Busytown, IA 11111-2222',
'phone: '.OBIB_LIBRARY_PHONE,
'hours: '.OBIB_LIBRARY_HOURS,
);
foreach ($lines as $l) {
$lay->container('TextLine');
$lay->text($l);
$lay->close();
}
$lay->close();
$lay->element('Spacer', array('height'=>14));
$lay->container('TextLine');
$lay->text($mbr->getFirstName().' '.$mbr->getLastName());
$lay->close();
foreach (explode("\n", $mbr->getAddress()) as $l) {
$lay->container('TextLine');
$lay->text($l);
$lay->close();
}
$lay->element('Spacer', array('height'=>14));
$lay->container('TextLine');
$lay->text('Dear '.$mbr->getFirstName().' '.$mbr->getLastName().':');
$lay->close();
$lay->element('Spacer', array('height'=>9));
$lay->container('Paragraph');
$lay->container('TextLines');
$lay->text('Our records show that the following library items '
. 'are checked out under your name and are past due. Please '
. 'return them as soon as possible and pay any late fees due.');
$lay->close();
$lay->close();
$lay->element('Spacer', array('height'=>28));
$lay->container('TextLine');
$lay->text('Sincerely,');
$lay->close();
$lay->element('Spacer', array('height'=>14));
$lay->container('TextLine');
$lay->text('The library staff at '.OBIB_LIBRARY_NAME);
$lay->close();
$lay->element('Spacer', array('height'=>14));
$lay->pushFont('Times-Italic', 12);
$lay->container('Line');
$lay->container('TextLine', array('width'=>'1.5in', 'underline'=>1));
$lay->text('Title');
$lay->close();
$lay->container('TextLine', array('width'=>'1.5in', 'underline'=>1));
$lay->text('Author');
$lay->close();
$lay->container('TextLine', array('width'=>'1in', 'underline'=>1));
$lay->text('Due Date');
$lay->close();
$lay->container('TextLine', array('width'=>'0.75in', 'underline'=>1));
$lay->text('Days Late');
$lay->close();
$lay->close();
$lay->popFont();
}
$lay->container('Line');
$lay->container('TextLine', array('width'=>'1.5in'));
$lay->text($row['title']);
$lay->close();
$lay->container('TextLine', array('width'=>'1.5in'));
$lay->text($row['author']);
$lay->close();
$lay->container('TextLine', array('width'=>'1in'));
$lay->text(date('m/d/y', strtotime($row['due_back_dt'])));
$lay->close();
$lay->container('TextLine', array('width'=>'0.75in'));
$lay->text($row['days_late']);
$lay->close();
$lay->close();
}
$lay->close();
$lay->popFont();
$lay->close();
}
}
?>
|