[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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



Revision: 4773
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4773&view=rev
Author:   einhverfr
Date:     2012-05-24 13:36:23 +0000 (Thu, 24 May 2012)
Log Message:
-----------

Aging reports now resubmit criteria back to them.  This provides the basic groundwork for emailing of statements.
A number of aspects of the aging reports have been corrected.  Now we can start getting statements working (via email and printers).

Modified Paths:
--------------
    trunk/LedgerSMB/DBObject/Report/Aging.pm
    trunk/LedgerSMB/Scripts/report_aging.pm
    trunk/LedgerSMB/Template.pm
    trunk/UI/Reports/aging_report.html
    trunk/UI/lib/dynatable.html
    trunk/sql/modules/Report.sql

Modified: trunk/LedgerSMB/DBObject/Report/Aging.pm
===================================================================
--- trunk/LedgerSMB/DBObject/Report/Aging.pm	2012-05-24 08:46:16 UTC (rev 4772)
+++ trunk/LedgerSMB/DBObject/Report/Aging.pm	2012-05-24 13:36:23 UTC (rev 4773)
@@ -92,7 +92,7 @@
       {col_id => 'select',
          type => 'checkbox'},
  
-      {col_id => 'credit_acct',
+      {col_id => 'name',
          name => $credit_label,
          type => 'text',
        pwidth => 1, },

Modified: trunk/LedgerSMB/Scripts/report_aging.pm
===================================================================
--- trunk/LedgerSMB/Scripts/report_aging.pm	2012-05-24 08:46:16 UTC (rev 4772)
+++ trunk/LedgerSMB/Scripts/report_aging.pm	2012-05-24 13:36:23 UTC (rev 4773)
@@ -29,6 +29,7 @@
 
 sub run_report{
     my ($request) = @_;
+
     delete $request->{category} if ($request->{category} = 'X');
     $request->{business_units} = [];
     for my $count (1 .. $request->{bc_count}){
@@ -41,6 +42,17 @@
     $report->render($request);
 }
 
+
+=item select_all
+
+Runs a report again, selecting all items
+
+=cut
+
+sub select_all {
+    run_report(@_);
+}
+
 =item THE FOLLOWING ARE TODO
 
 =item retrieve_statement

Modified: trunk/LedgerSMB/Template.pm
===================================================================
--- trunk/LedgerSMB/Template.pm	2012-05-24 08:46:16 UTC (rev 4772)
+++ trunk/LedgerSMB/Template.pm	2012-05-24 13:36:23 UTC (rev 4773)
@@ -282,6 +282,24 @@
         $vars->{ENVARS} = \%ENV;
         $vars->{USER} = $LedgerSMB::App_State::User;
         $vars->{CSSDIR} = $LedgerSMB::Sysconfig::cssdir;
+
+        my @stdformats = ();
+        for (qw(HTML PDF PS)){
+           if (scalar(grep {/^$_$/} @{$vars->{FORMATS}})){
+               push @stdformats, $_;
+           }
+        }
+        $vars->{STDFORMATS} = ..hidden..;
+        eval {
+             $vars->{PRINTERS} = [
+                   {text => $LedgerSMB::App_State::Locale->text('Screen'),
+                   value => 'screen'},
+             ]; 
+        };
+        for (keys %LedgerSMB::Sysconfig::printers){
+            push @{$vars->{PRINTERS}}, { text => $_, value => $_ };
+        }
+
 	if ($self->{format} !~ /^\p{IsAlnum}+$/) {
 		throw Error::Simple "Invalid format";
 	}

Modified: trunk/UI/Reports/aging_report.html
===================================================================
--- trunk/UI/Reports/aging_report.html	2012-05-24 08:46:16 UTC (rev 4772)
+++ trunk/UI/Reports/aging_report.html	2012-05-24 13:36:23 UTC (rev 4773)
@@ -25,24 +25,79 @@
 </div>
 <?lsmb END ?>
 
-<form method="get" action="aging_report.pl">
-<?lsmb PROCESS dynatable tbody = {rows => rows } 
-               attributes = {class = 'report' } ?>
-<?lsmb PROCESS button element_data = {
-    text = text('Print')
+<form method="get" action="report_aging.pl">
+<?lsmb
+
+IF request.action == 'select_all';
+   FOREACH ROW IN rows;
+      ROW.select = 1;
+   END;
+END;
+
+FOREACH KEY IN report.keys();
+    IF KEY == 'rows' or KEY.keys();
+       NEXT;
+    ELSE;
+        PROCESS input element_data = {
+             name = KEY
+             type = 'hidden'
+            value = report.$KEY
+        };
+    END;
+END;
+
+PROCESS dynatable tbody = {rows = rows } 
+               attributes = {id = 'aging', class = 'report' };
+PROCESS button element_data = {
+    text = text('Select All') #'
     name = 'action'
-   value = 'print_statements'
+   value = 'select_all'
     type = 'submit'
    class = 'submit'
-} ?>
-<?lsmb PROCESS button element_data = {
-    text = text('Email')
+};
+PROCESS select element_data = {
+    name = 'print_template'
+ options = [{text = 'Statement', value = 'Statement'}]
+   class = 'print_template'
+};
+
+PRINTERS.push({text = text('Email'), value = 'email' });
+
+PFORMATS = [];
+
+FOREACH F IN STDFORMATS;
+  PFORMATS.push({text = F, value = F});
+END;
+
+PROCESS select element_data = {
+   name = 'print_format'
+options = PFORMATS
+  class = 'print_format'
+};
+
+PROCESS select element_data = {
+   name = 'print_to'
+options = PRINTERS
+  class = 'printers'
+};
+
+PROCESS input element_data = {
+   name = 'copies'
+  value = '1'
+   type = 'text'
+   size = '2'
+  class = 'smallint'
+};
+
+PROCESS button element_data = {
+    text = text('Generate')
     name = 'action'
-   value = 'email_statements'
+   value = 'generate_statements'
     type = 'submit'
    class = 'submit'
-} ?>
-</form>
+};
+ ?>
+</form><br />
 <a href="<?lsmb LINK ?>">[<?lsmb text('permalink') ?>]</a>&nbsp;
 <?lsmb IF FORMATS.grep('PDF').size()
 ?>

Modified: trunk/UI/lib/dynatable.html
===================================================================
--- trunk/UI/lib/dynatable.html	2012-05-24 08:46:16 UTC (rev 4772)
+++ trunk/UI/lib/dynatable.html	2012-05-24 13:36:23 UTC (rev 4773)
@@ -37,7 +37,7 @@
               value="<?lsmb ROW.${COL.col_id} ?>"
          />
          <?lsmb- ELSIF COL.type == 'checkbox';
-                   IF ROW.${COL.name};
+                   IF ROW.${COL.col_id};
                       CHECKED = "checked='CHECKED'";
                    ELSE; 
                       CHECKED = '';

Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql	2012-05-24 08:46:16 UTC (rev 4772)
+++ trunk/sql/modules/Report.sql	2012-05-24 13:36:23 UTC (rev 4773)
@@ -8,13 +8,6 @@
 	entity_id int,
 	account_number varchar(24),
 	name text,
-	address1 text,
-	address2 text,
-	address3 text,
-	city text,
-        state text,
-	mail_code text,
-	country text,
 	contact_name text,
 	invnumber text,
 	transdate date,
@@ -30,13 +23,14 @@
 	id int,
 	curr varchar(3),
 	exchangerate numeric,
-	line_items text[][]
+	line_items text[][],
+        age int
 );
 
 
 CREATE OR REPLACE FUNCTION report__invoice_aging_detail
-(in_entity_id int, in_entity_class int, in_accno text, in_to_date timestamp,
- in_business_units int[]) 
+(in_entity_id int, in_entity_class int, in_accno text, in_to_date date,
+ in_business_units int[], in_use_duedate bool) 
 RETURNS SETOF report_aging_item
 AS
 $$
@@ -54,39 +48,23 @@
                   JOIN bu_tree ON bu_tree.id = bu.parent_id
                        )
 		SELECT c.entity_id, c.meta_number, e.name,
-		       l.line_one, l.line_two, 
-		       l.line_three,
-		       l.city, l.state, l.mail_code, country.name as country, 
 		       e.name as contact_name, 
 	               a.invnumber, a.transdate, a.till, a.ordnumber, 
 		       a.ponumber, a.notes, 
-		       CASE WHEN 
-		                 EXTRACT(days FROM age(min(ac.transdate), 
-                                                       coalesce(in_to_date,
-                                                                now()))/30) 
-		                 = 0
-		                 THEN (a.amount - a.sign * sum(ac.amount)) 
+		       CASE WHEN a.age/30 = 0
+		                 THEN (a.sign * sum(ac.amount)) 
                             ELSE 0 END
 		            as c0, 
-		       CASE WHEN EXTRACT(days FROM age(min(ac.transdate), 
-                                                       coalesce(in_to_date,
-                                                                now()))/30)
-		                 = 1
-		                 THEN (a.amount - a.sign * sum(ac.amount)) 
+		       CASE WHEN a.age/30 = 1
+		                 THEN (a.sign * sum(ac.amount))
                             ELSE 0 END
 		            as c30, 
-		       CASE WHEN EXTRACT(days FROM age(min(ac.transdate),
-                                                       coalesce(in_to_date,
-                                                                now()))/30)
-		                 = 2
-		            THEN (a.amount - (a.sign * sum(ac.amount)))
+		       CASE WHEN a.age/30 = 2
+		            THEN (a.sign * sum(ac.amount))
                             ELSE 0 END
 		            as c60, 
-		       CASE WHEN EXTRACT(days FROM age(min(ac.transdate),
-                                                       coalesce(in_to_date,
-                                                                now()))/30)
-		                 > 2
-		            THEN (a.amount - a.sign * sum(ac.amount))
+		       CASE WHEN a.age/30 > 2
+		            THEN (a.sign * sum(ac.amount))
                             ELSE 0 END
 		            as c90, 
 		       a.duedate, a.id, a.curr,
@@ -98,16 +76,29 @@
 					i.description, i.qty::text]])
 				FROM parts p 
 				JOIN invoice i ON (i.parts_id = p.id)
-				WHERE i.trans_id = a.id) AS line_items
+				WHERE i.trans_id = a.id) AS line_items,
+                   (coalesce(in_to_date, now())::date - a.transdate) as age
 		  FROM (select id, invnumber, till, ordnumber, amount, duedate,
                                curr, ponumber, notes, entity_credit_account,
-                               -1 AS sign, transdate
+                               -1 AS sign, transdate,
+                               CASE WHEN in_use_duedate 
+                                    THEN coalesce(in_to_date, now())::date
+                                         - duedate
+                                    ELSE coalesce(in_to_date, now())::date
+                                         - transdate 
+                               END as age
                           FROM ar
                          WHERE in_entity_class = 2
                          UNION 
                         SELECT id, invnumber, null, ordnumber, amount, duedate,
                                curr, ponumber, notes, entity_credit_account,
-                               1 as sign, transdate
+                               1 as sign, transdate,
+                               CASE WHEN in_use_duedate 
+                                    THEN coalesce(in_to_date, now())::date
+                                         - duedate
+                                    ELSE coalesce(in_to_date, now())::date
+                                         - transdate 
+                               END as age
                           FROM ap
                          WHERE in_entity_class = 1) a
                   JOIN acc_trans ac ON ac.trans_id = a.id
@@ -134,7 +125,7 @@
                        l.city, l.state, l.mail_code, country.name,
                        a.invnumber, a.transdate, a.till, a.ordnumber,
                        a.ponumber, a.notes, a.amount, a.sign,
-                       a.duedate, a.id, a.curr
+                       a.duedate, a.id, a.curr, a.age
                 HAVING in_business_units is null or in_business_units 
                        <@ compound_array(string_to_array(bu_tree.path, 
                                          ',')::int[])
@@ -146,18 +137,16 @@
 $$ language plpgsql;
 
 CREATE OR REPLACE FUNCTION report__invoice_aging_summary
-(in_entity_id int, in_entity_class int, in_accno text, in_to_date timestamp,
- in_business_units int[]) 
+(in_entity_id int, in_entity_class int, in_accno text, in_to_date date,
+ in_business_units int[], in_use_duedate bool) 
 RETURNS SETOF report_aging_item
 AS $$
-SELECT entity_id, account_number, name, address1, address2, address3, city, 
-       state, mail_code, country, contact_name, invnumber, null::date, 
+SELECT entity_id, account_number, name, contact_name, null::text, null::date, 
        null::text, null::text, null::text, null::text, 
        sum(c0), sum(c30), sum(c60), sum(c90), null::date, null::int, curr,
-       null::numeric, null::text[]
-  FROM report__invoice_aging_detail($1, $2, $3, $4, $5)
- GROUP BY entity_id, account_number, name, address1, address2, address3, city, 
-       state, mail_code, country, contact_name, invnumber, curr
+       null::numeric, null::text[], null::int
+  FROM report__invoice_aging_detail($1, $2, $3, $4, $5, $6)
+ GROUP BY entity_id, account_number, name, contact_name, curr
  ORDER BY account_number
 $$ LANGUAGE SQL;
 

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