1 package OpenILS::Application::Storage::Publisher::money;
2 use base qw/OpenILS::Application::Storage/;
3 use OpenSRF::Utils::Logger qw/:level/;
5 my $log = 'OpenSRF::Utils::Logger';
14 my $mct = money::collections_tracker->table;
15 my $mb = money::billing->table;
16 my $circ = action::circulation->table;
17 my $mg = money::grocery->table;
18 my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
22 MAX(bl.billing_ts) AS last_pertinent_billing,
23 SUM(bl.amount) - COALESCE(SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)),0) AS threshold_amount
24 FROM ( SELECT id,usr,billing_location AS location FROM money.grocery
26 SELECT id,usr,circ_lib AS location FROM action.circulation ) AS lt
27 JOIN $descendants d ON (lt.location = d.id)
28 JOIN money.billing bl ON (lt.id = bl.xact AND bl.voided IS FALSE)
29 WHERE AGE(bl.billing_ts) > ?
33 FROM money.collections_tracker
40 AND (SUM(bl.amount) - COALESCE(SUM((SELECT SUM(amount) FROM money.payment WHERE xact = lt.id)),0)) > ?
45 my $sth = money::collections_tracker->db_Main->prepare($SQL);
46 $sth->execute(uc($l), $age, uc($l), $amount );
47 while (my $row = $sth->fetchrow_hashref) {
48 #$row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
49 $client->respond( $row );
54 __PACKAGE__->register_method(
55 method => 'new_collections',
56 api_name => 'open-ils.storage.money.collections.users_of_interest',
61 sub active_in_collections {
64 my $startdate = shift;
68 my $mct = money::collections_tracker->table;
69 my $mb = money::billing->table;
70 my $circ = action::circulation->table;
71 my $mg = money::grocery->table;
72 my $descendants = "actor.org_unit_descendants((select id from actor.org_unit where shortname = ?))";
76 MAX(bl.billing_ts) AS last_pertinent_billing,
77 MAX(pm.payment_ts) AS last_pertinent_payment
78 FROM ( SELECT id,usr,billing_location AS location, 'g'::char AS x_type FROM money.grocery
80 SELECT id,usr,circ_lib AS location, 'c'::char AS x_type FROM action.circulation
82 SELECT id,usr,circ_lib AS location, 'i'::char AS x_type FROM action.circulation
83 WHERE checkin_time between ? and ? ) AS lt
84 JOIN $descendants d ON (lt.location = d.id)
85 JOIN money.collections_tracker cl ON (lt.usr = cl.usr)
86 LEFT JOIN money.billing bl ON (lt.id = bl.xact)
87 LEFT JOIN money.payment pm ON (lt.id = pm.xact)
88 WHERE bl.billing_ts between ? and ?
89 OR pm.payment_ts between ? and ?
90 OR lt.x_type = 'i'::char
96 my $sth = money::collections_tracker->db_Main->prepare($SQL);
97 $sth->execute( $startdate, $enddate, uc($l), $startdate, $enddate, $startdate, $enddate );
98 while (my $row = $sth->fetchrow_hashref) {
99 $row->{usr} = actor::user->retrieve($row->{usr})->to_fieldmapper;
100 $client->respond( $row );
105 __PACKAGE__->register_method(
106 method => 'active_in_collections',
107 api_name => 'open-ils.storage.money.collections.users_with_activity',
112 sub ou_desk_payments {
116 my $startdate = shift;
119 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
120 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
121 return undef unless ($lib =~ /^\d+$/o);
129 SUM(COALESCE(p.amount,0.0))
130 FROM money.desk_payment_view p
131 JOIN actor.workstation ws ON (ws.id = p.cash_drawer)
132 WHERE p.payment_ts >= '$startdate'
133 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
134 AND p.voided IS FALSE
135 AND ws.owning_lib = $lib
140 cash_payment numeric(10,2),
141 check_payment numeric(10,2),
142 credit_card_payment numeric(10,2) );
146 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
149 my $x = new Fieldmapper::money::workstation_payment_summary;
150 $x->workstation( actor::workstation->retrieve($$r[0])->to_fieldmapper );
151 $x->cash_payment($$r[1]);
152 $x->check_payment($$r[2]);
153 $x->credit_card_payment($$r[3]);
155 $client->respond($x);
160 __PACKAGE__->register_method(
161 method => 'ou_desk_payments',
162 api_name => 'open-ils.storage.money.org_unit.desk_payments',
167 sub ou_user_payments {
171 my $startdate = shift;
174 return undef unless ($startdate =~ /^\d{4}-\d{2}-\d{2}$/o);
175 return undef unless ($enddate =~ /^\d{4}-\d{2}-\d{2}$/o);
176 return undef unless ($lib =~ /^\d+$/o);
184 SUM(COALESCE(p.amount,0.0))
185 FROM money.bnm_payment_view p
186 JOIN actor.usr au ON (au.id = p.accepting_usr)
187 WHERE p.payment_ts >= '$startdate'
188 AND p.payment_ts < '$enddate'::TIMESTAMPTZ + INTERVAL '1 day'
189 AND p.voided IS FALSE
190 AND au.home_ou = $lib
191 AND p.payment_type IN ('credit_payment','forgive_payment','work_payment')
196 forgive_payment numeric(10,2),
197 work_payment numeric(10,2),
198 credit_payment numeric(10,2) );
202 my $rows = money::payment->db_Main->selectall_arrayref( $sql );
205 my $x = new Fieldmapper::money::user_payment_summary;
206 $x->usr( actor::user->retrieve($$r[0])->to_fieldmapper );
207 $x->forgive_payment($$r[1]);
208 $x->work_payment($$r[2]);
209 $x->credit_payment($$r[3]);
211 $client->respond($x);
216 __PACKAGE__->register_method(
217 method => 'ou_user_payments',
218 api_name => 'open-ils.storage.money.org_unit.user_payments',