From 49ee94eb7bb2552003573bf15ddec5d004c37705 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Wed, 9 May 2007 16:54:57 +0000
Subject: [PATCH] adding dewey range and block extraction to the classic circ
 view

git-svn-id: svn://svn.open-ils.org/ILS/trunk@7222 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/examples/fm_IDL.xml                       |  4 ++
 Open-ILS/src/sql/Pg/example.reporter-extension.sql | 52 ++++++++++++++++++++++
 2 files changed, 56 insertions(+)

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index d4f1edce7d..f94c99086b 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -2455,6 +2455,10 @@
 			<field reporter:label="Patron ZIP Code" name="patron_zip" oils_obj:array_position="26" oils_persist:virtual="false" reporter:datatype="text"/>
 			<field reporter:label="Legacy CAT1 Value" name="stat_cat_1" oils_obj:array_position="27" oils_persist:virtual="false" reporter:datatype="int"/>
 			<field reporter:label="Legacy CAT2 Value" name="stat_cat_2" oils_obj:array_position="28" oils_persist:virtual="false" reporter:datatype="int"/>
+			<field reporter:label="Dewey Range -- Tens" name="dewey_range_tens" oils_obj:array_position="29" oils_persist:virtual="false" reporter:datatype="text"/>
+			<field reporter:label="Dewey Range -- Hundreds" name="dewey_range_hundreds" oils_obj:array_position="30" oils_persist:virtual="false" reporter:datatype="text"/>
+			<field reporter:label="Dewey Block -- Tens" name="dewey_block_tens" oils_obj:array_position="31" oils_persist:virtual="false" reporter:datatype="text"/>
+			<field reporter:label="Dewey Block -- Hundreds" name="dewey_block_hundreds" oils_obj:array_position="32" oils_persist:virtual="false" reporter:datatype="text"/>
 		</fields>
 		<links>
 			<link field="id" reltype="has_a" key="id" map="" class="circ"/>
diff --git a/Open-ILS/src/sql/Pg/example.reporter-extension.sql b/Open-ILS/src/sql/Pg/example.reporter-extension.sql
index d1f1b3138b..90f8304032 100644
--- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql
+++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql
@@ -20,6 +20,58 @@ SELECT	cl.shortname AS circ_lib,
 	cn.id AS call_number,
 	cn.label AS call_number_label,
 	call_number_dewey(cn.label) AS dewey,
+	CASE
+		WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
+			THEN
+				btrim(
+					to_char(
+						10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
+					)
+				)
+		ELSE NULL
+	END AS dewey_block_tens,
+	CASE
+		WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
+			THEN
+				btrim(
+					to_char(
+						100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
+					)
+				)
+		ELSE NULL
+	END AS dewey_block_hundreds,
+	CASE
+		WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
+			THEN
+				btrim(
+					to_char(
+						10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
+					)
+				)
+				|| '-' ||
+				btrim(
+					to_char(
+						10 * floor((call_number_dewey(cn.label)::float) / 10) + 9, '000'
+					)
+				)
+		ELSE NULL
+	END AS dewey_range_tens,
+	CASE
+		WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
+			THEN
+				btrim(
+					to_char(
+						100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
+					)
+				)
+				|| '-' ||
+				btrim(
+					to_char(
+						100 * floor((call_number_dewey(cn.label)::float) / 100) + 99, '000'
+					)
+				)
+		ELSE NULL
+	END AS dewey_range_hundreds,
 	hl.id AS patron_home_lib,
 	hl.shortname AS patron_home_lib_shortname,
 	paddr.county AS patron_county,
-- 
2.11.0