Recent Changes Printable View Page History Edit Page

Bibliography Checkout Listing with custom member fields: Grade, Teacher

Included in downloadable customization package

.title "Bibliography Checkout Listing : Grade, Teacher"
.category Circulation
.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
.	date due_before title="Due before"
.	date out_since title="Out since"
.	select grade_sel title="Grade"
.		item ''
# 0.6.0: SQL in RPT .select parameter should be one line, unwrapped
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
	SELECT c.bibid, c.copyid, m.mbrid, c.barcode_nmbr,
		b.title,, c.status_begin_dt,
		c.due_back_dt, m.barcode_nmbr member_bcode,
		concat(m.last_name, ', ', m.first_name) name, school_grade, school_teacher
	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 due_before
		AND c.due_back_dt <= %due_before%
.	end if_set
.	if_set out_since
		AND c.status_begin_dt >= %out_since%
.	end if_set
.	if_set grade
.		if_equal criteria1 start
			AND LIKE '%"grade%%%'
.		end if_set
.		if_equal criteria1 trunc
			AND LIKE '%%%"grade%%%'		
.		end if_set
.	else
.		if_not_equal grade_sel ''
			AND = %grade_sel%
.		end if_set
.	end if_set
.	if_set teacher
.		if_equal criteria2 start
			AND LIKE '%"teacher%%%'
.		end if_set
.		if_equal criteria2 trunc
			AND LIKE '%%%"teacher%%%'
.		end if_set
.	end if_set
.	order_by_expr
.end sql Logo
Edit Page - Page History - Printable View - Recent Changes - Search
Page last modified on January 21, 2010, at 04:22 PM