SF.net SVN: ledger-smb:[6242] trunk

[..hidden..]

Revision: 6242
          http://sourceforge.net/p/ledger-smb/code/6242
Author:   einhverfr
Date:     2013-11-07 11:16:25 +0000 (Thu, 07 Nov 2013)
Log Message:
-----------
Overpayment search criteria procedure

Modified Paths:
--------------
    trunk/LedgerSMB/Report/Listings/Overpayments.pm
    trunk/sql/modules/Payment.sql

Modified: trunk/LedgerSMB/Report/Listings/Overpayments.pm
===================================================================
--- trunk/LedgerSMB/Report/Listings/Overpayments.pm	2013-11-07 10:40:19 UTC (rev 6241)
+++ trunk/LedgerSMB/Report/Listings/Overpayments.pm	2013-11-07 11:16:25 UTC (rev 6242)
@@ -26,10 +26,8 @@
 
 =over 
 
-=item amount_from
+=item name_part
 
-=item amount_to
-
 =item control_code
 
 =item meta_number
@@ -38,12 +36,8 @@
 
 =cut
 
-has amount_from => (is => 'ro', isa => 'LedgerSMB::Moose::Number', coerce=> 1,
-              required => 0);
+has name_part => (is => 'ro', isa => 'Str', required => 0);
 
-has amount_to => (is => 'ro', isa => 'LedgerSMB::Moose::Number', coerce=> 1,
-            required => 0);
-
 has control_code => (is => 'ro', isa => 'Str', required => 0);
 
 has meta_number => (is => 'ro', isa => 'Str', required => 0);

Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql	2013-11-07 10:40:19 UTC (rev 6241)
+++ trunk/sql/modules/Payment.sql	2013-11-07 11:16:25 UTC (rev 6242)
@@ -1486,7 +1486,7 @@
 SELECT * FROM gl WHERE id = (select id from payment where id = $1);
 $$;
 
-CREATE FUNCTION payment__reverse_overpayment
+CREATE OR REPLACE FUNCTION payment__reverse_overpayment
 (in_payment_id int, in_batch_id int, in_account_class int) 
 RETURNS voucher LANGUAGE PLPGSQL AS
 $$
@@ -1523,4 +1523,41 @@
 END;
 $$;
 
+DROP TYPE IF EXISTS overpayment_list_item;
+CREATE TYPE overpayment_list_item AS (
+  payment_id int,
+  entity_name text,
+  available numeric,
+  transdate date,
+  amount numeric
+);
+CREATE OR REPLACE FUNCTION payment__overpayments_list
+(in_date_from date, in_date_to date, in_control_code text, in_meta_number text,
+ in_name_part text)
+RETURNS SETOF overpayment_list_item
+LANGUAGE SQL AS
+$$
+-- I don't like the subquery below but we are looking for the first line, and
+-- I can't think of a better way to do that. --CT
+
+-- This should never hit an income statement-side account but I have handled it 
+-- in case of configuration error. --CT
+SELECT o.payment_id, e.name, o.available, g.transdate, 
+       (select amount * CASE WHEN c.category in ('A', 'E') THEN -1 ELSE 1 END
+          from acc_trans 
+         where g.id = trans_id 
+               AND chart_id = o.chart_id ORDER BY entry_id ASC LIMIT 1) as amount
+  FROM overpayments o
+  JOIN payment p ON o.payment_id = p.id
+  JOIN gl g ON g.id = p.gl_id
+  JOIN account c ON c.id = o.chart_id
+  JOIN entity_credit_account eca ON eca.id = o.entity_credit_id
+  JOIN entity e ON eca.entity_id = e.id
+ WHERE ($1 IS NULL OR $1 <= g.transdate) AND
+       ($2 IS NULL OR $2 >= g.transdate) AND
+       ($3 IS NULL OR $3 = e.control_code) AND
+       ($4 IS NULL OR $4 = eca.meta_number) AND
+       ($5 IS NULL OR e.name @@ plainto_tsquery($5));
+$$;
+
 COMMIT;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.


------------------------------------------------------------------------------
November Webinars for C, C++, Fortran Developers
Accelerate application performance with scalable programming models. Explore
techniques for threading, error checking, porting, and tuning. Get the most 
from the latest Intel processors and coprocessors. See abstracts and register
http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits