From e366deeb9550fe352cd32957f045c3a9dfb33875 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Wed, 16 Aug 2006 17:55:26 +0000
Subject: [PATCH] de-smartifying dbd::pg and adding user payment objects

git-svn-id: svn://svn.open-ils.org/ILS/trunk@5535 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/examples/fm_IDL.xml                  | 15 ++++
 .../Application/Storage/Publisher/money.pm    | 73 +++++++++++++++++--
 Open-ILS/src/sql/Pg/080.schema.money.sql      |  5 ++
 3 files changed, 86 insertions(+), 7 deletions(-)

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 3f79c77dea..ee69480400 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -1,6 +1,21 @@
 <IDL xmlns="http://opensrf.org/spec/IDL/base/v1" xmlns:oils_persist="http://open-ils.org/spec/opensrf/IDL/persistance/v1" xmlns:oils_obj="http://open-ils.org/spec/opensrf/IDL/objects/v1">
 
 	<!-- Virutal classes -->
+	<class id="mups" oils_obj:fieldmapper="money::user_payment_summary" oils_persist:virtual="true">
+		<fields>
+			<field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" />
+			<field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" />
+			<field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" />
+			<field name="usr" oils_obj:array_position="3" oils_persist:virtual="true" />
+			<field name="forgive_payment" oils_obj:array_position="4" oils_persist:virtual="true" />
+			<field name="work_payment" oils_obj:array_position="5" oils_persist:virtual="true" />
+			<field name="credit_payment" oils_obj:array_position="6" oils_persist:virtual="true" />
+		</fields>
+		<links>
+			<link field="usr" reltype="has_a" key="id" map="" class="au"/>
+		</links>
+	</class>
+
 	<class id="mwps" oils_obj:fieldmapper="money::workstation_payment_summary" oils_persist:virtual="true">
 		<fields>
 			<field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" />
diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm
index 838578d3ef..740180a210 100644
--- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm
+++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/money.pm
@@ -113,22 +113,26 @@ sub ou_desk_payments {
 	my $startdate = shift;
 	my $enddate = shift;
 
-	my $sql = <<'	SQL';
+	return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
+	return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
+	return undef unless ($lib =~ /^\d+$/o);
+
+	my $sql = <<"	SQL";
 
 SELECT	*
-  FROM	crosstab($$
+  FROM	crosstab(\$\$
 	 SELECT	ws.id,
 		p.payment_type,
 		SUM(COALESCE(p.amount,0.0))
 	  FROM	money.desk_payment_view p
 		JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
-	  WHERE	p.payment_ts >= ?
-		AND p.payment_ts < ?::TIMESTAMPTZ + INTERVAL '1 day'
+	  WHERE	p.payment_ts >= '$startdate'
+		AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
 		AND p.voided IS FALSE
-		AND ws.owning_lib = ?
+		AND ws.owning_lib = $lib
 	 GROUP BY 1, 2
 	 ORDER BY 1,2
-	$$) AS X(
+	\$\$) AS X(
 	  workstation int,
 	  cash_payment numeric(10,2),
 	  check_payment numeric(10,2),
@@ -136,7 +140,7 @@ SELECT	*
 
 	SQL
 
-	my $rows = money::payment->db_Main->selectall_arrayref( $sql, {}, $startdate, $enddate, $lib );
+	my $rows = money::payment->db_Main->selectall_arrayref( $sql );
 
 	for my $r (@$rows) {
 		my $x = new Fieldmapper::money::workstation_payment_summary;
@@ -157,5 +161,60 @@ __PACKAGE__->register_method(
 	argc		=> 3,
 );
 
+sub ou_user_payments {
+	my $self = shift;
+	my $client = shift;
+	my $lib = shift;
+	my $startdate = shift;
+	my $enddate = shift;
+
+	return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
+	return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
+	return undef unless ($lib =~ /^\d+$/o);
+
+	my $sql = <<"	SQL";
+
+SELECT  *
+  FROM  crosstab(\$\$
+         SELECT au.id,
+                p.payment_type,
+                SUM(COALESCE(p.amount,0.0))
+          FROM  money.bnm_payment_view p
+                JOIN actor.usr au ON (au.id = p.accepting_usr)
+          WHERE p.payment_ts >= '$startdate'
+                AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
+                AND p.voided IS FALSE
+                AND au.home_ou = $lib
+         GROUP BY 1, 2
+         ORDER BY 1,2
+        \$\$) AS X(
+          usr int,
+          forgive_payment numeric(10,2),
+          work_payment numeric(10,2),
+          credit_payment numeric(10,2) );
+
+	SQL
+
+	my $rows = money::payment->db_Main->selectall_arrayref( $sql );
+
+	for my $r (@$rows) {
+		my $x = new Fieldmapper::money::user_payment_summary;
+		$x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
+		$x->forgive_payment($$r[1]);
+		$x->work_payment($$r[2]);
+		$x->credit_payment($$r[3]);
+
+		$client->respond($x);
+	}
+
+	return undef;
+}
+__PACKAGE__->register_method(
+	method		=> 'ou_user_payments',
+	api_name	=> 'open-ils.storage.money.org_unit.user_payments',
+	stream		=> 1,
+	argc		=> 3,
+);
+
 
 1;
diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql
index 144ec12bb1..c4505166e7 100644
--- a/Open-ILS/src/sql/Pg/080.schema.money.sql
+++ b/Open-ILS/src/sql/Pg/080.schema.money.sql
@@ -278,6 +278,11 @@ CREATE OR REPLACE VIEW money.desk_payment_view AS
 	  FROM	money.bnm_desk_payment p
 	  	JOIN pg_class c ON (p.tableoid = c.oid);
 
+CREATE OR REPLACE VIEW money.bnm_payment_view AS
+	SELECT	p.*,c.relname AS payment_type
+	  FROM	money.bnm_payment p
+	  	JOIN pg_class c ON (p.tableoid = c.oid);
+
 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
 ALTER TABLE money.cash_payment ADD PRIMARY KEY (id);
 CREATE INDEX money_cash_id_idx ON money.cash_payment (id);
-- 
2.43.2