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

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



Revision: 5505
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5505&view=rev
Author:   einhverfr
Date:     2013-01-07 10:33:34 +0000 (Mon, 07 Jan 2013)
Log Message:
-----------
Added missing parnumber search for ar/ap transactions search-- present but exposed only indirectly in 1.3 and below

Modified Paths:
--------------
    trunk/LedgerSMB/RP.pm
    trunk/LedgerSMB/Report/Invoices/Transactions.pm
    trunk/UI/Reports/filters/invoice_search.html
    trunk/sql/modules/Goods.sql
    trunk/sql/modules/Report.sql

Modified: trunk/LedgerSMB/RP.pm
===================================================================
--- trunk/LedgerSMB/RP.pm	2013-01-07 10:04:05 UTC (rev 5504)
+++ trunk/LedgerSMB/RP.pm	2013-01-07 10:33:34 UTC (rev 5505)
@@ -35,75 +35,6 @@
 use Log::Log4perl;
 our $logger = Log::Log4perl->get_logger('LedgerSMB::Form');
 
-sub inventory_activity {
-    my ( $self, $myconfig, $form ) = @_;
-    ( $form->{fromdate}, $form->{todate} ) =
-      $form->from_to( $form->{fromyear}, $form->{frommonth}, $form->{interval} )
-      if $form->{fromyear} && $form->{frommonth};
-
-    my $dbh = $form->{dbh};
-
-    unless ( $form->{sort_col} ) {
-        $form->{sort_col} = 'partnumber';
-    }
-
-    my $where = '';
-    if ( $form->{fromdate} ) {
-        $where .=
-          "AND coalesce(ar.transdate, ap.transdate) >= "
-          . $dbh->quote( $form->{fromdate} );
-    }
-    if ( $form->{todate} ) {
-        $where .=
-          "AND coalesce(ar.transdate, ap.transdate) < "
-          . $dbh->quote( $form->{todate} ) . " ";
-    }
-    if ( $form->{partnumber} ) {
-        $where .=
-          qq| AND p.partnumber ILIKE |
-          . $dbh->quote( '%' . "$form->{partnumber}%" );
-    }
-    if ( $form->{description} ) {
-        $where .=
-          q| AND p.description ILIKE |
-          . $dbh->quote( '%' . "$form->{description}%" );
-    }
-    $where =~ s/^\s?AND/WHERE/;
-
-    my $query = qq|
-		   SELECT min(p.description) AS description, 
-		          min(p.partnumber) AS partnumber, sum(
-		          CASE WHEN i.qty > 0 THEN i.qty ELSE 0 END) AS sold, 
-		          sum (CASE WHEN i.qty > 0 
-		                    THEN i.sellprice * i.qty 
-		                    ELSE 0 END) AS revenue, 
-		          sum(CASE WHEN i.qty < 0 THEN i.qty * -1 ELSE 0 END) 
-		          AS received, sum(CASE WHEN i.qty < 0 
-		                                THEN i.sellprice * i.qty * -1
-		                                ELSE 0 END) as expenses, 
-		          min(p.id) as id
-		     FROM invoice i
-		     JOIN parts p ON (i.parts_id = p.id)
-		LEFT JOIN ar ON (ar.id = i.trans_id)
-		LEFT JOIN ap ON (ap.id = i.trans_id)
-		   $where
-		 GROUP BY i.parts_id
-		 ORDER BY $form->{sort_col}|;
-    my $sth = $dbh->prepare($query) || $form->dberror($query);
-    $sth->execute() || $form->dberror($query);
-    @cols = qw(description sold revenue partnumber received expense);
-    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
-        $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
-        $ref->{net_income} = $ref->{revenue} - $ref->{expense};
-        map { $ref->{$_} =~ s/^\s*// } @cols;
-        map { $ref->{$_} =~ s/\s*$// } @cols;
-        push @{ $form->{TB} }, $ref;
-    }
-    $sth->finish;
-    $dbh->commit;
-
-}
-
 sub balance_sheet {
     my ( $self, $myconfig, $form ) = @_;
 

Modified: trunk/LedgerSMB/Report/Invoices/Transactions.pm
===================================================================
--- trunk/LedgerSMB/Report/Invoices/Transactions.pm	2013-01-07 10:04:05 UTC (rev 5504)
+++ trunk/LedgerSMB/Report/Invoices/Transactions.pm	2013-01-07 10:33:34 UTC (rev 5505)
@@ -96,6 +96,14 @@
 
 has ponumber => (is => 'ro', isa => 'Str', required => 0);
 
+=item partnumber string
+
+If set only include invoices including the relevant part number
+
+=cut
+
+has partnumber => (is => 'ro', isa => 'Str', required => 0);
+
 =item source string
 
 Prefix string on source number

Modified: trunk/UI/Reports/filters/invoice_search.html
===================================================================
--- trunk/UI/Reports/filters/invoice_search.html	2013-01-07 10:04:05 UTC (rev 5504)
+++ trunk/UI/Reports/filters/invoice_search.html	2013-01-07 10:33:34 UTC (rev 5505)
@@ -113,6 +113,17 @@
           class = "control-code"
        } ?></td>
 </tr>
+<tr id="partnumber-row">
+   <th><?lsmb text('Partnumber') ?></th>
+   <td><?lsmb PROCESS input element_data = {
+           name = "partnumber"
+          value = partnumber
+           type = 'text'
+           size = 16
+          class = "control-code"
+       } ?></td>
+</tr>
+
 <tr id="source-row">
   <th><?lsmb text('Source') ?></th>
   <td><?lsmb PROCESS input element_data = {

Modified: trunk/sql/modules/Goods.sql
===================================================================
--- trunk/sql/modules/Goods.sql	2013-01-07 10:04:05 UTC (rev 5504)
+++ trunk/sql/modules/Goods.sql	2013-01-07 10:33:34 UTC (rev 5505)
@@ -134,5 +134,40 @@
 ORDER BY pricegroup;
 $$;
 
+DROP TYPE IF EXISTS inv_activity_line CASCADE;
+CREATE TYPE inv_activity_line AS (
+   id int,
+   description text,
+   partnumber text,
+   sold numeric,
+   revenue numeric,
+   receivable numeric,
+   payable numeric
+);
+   
+CREATE OR REPLACE FUNCTION inventory__activity
+(in_date_from date, in_date_to date, in_partnumber text, in_descripiton text)
+RETURNS SETOF inv_activity_line LANGUAGE SQL AS
+$$
+    SELECT p.id, p.description, p.partnumber,  
+           SUM(CASE WHEN transtype = 'ar' THEN i.qty ELSE 0 END) AS sold,
+           SUM(CASE WHEN transtype = 'ar' THEN i.sellprice * i.qty ELSE 0 END)
+           AS receivable,
+           SUM(CASE WHEN transtype = 'ap' THEN i.qty * -1 ELSE 0 END) 
+           AS payable,
+           SUM(CASE WHEN transtype = 'ap' THEN -1 * i.sellprice * i.qty ELSE 0 
+                END) AS expenses
+      FROM invoice i
+      JOIN parts p ON (i.parts_id = p.id)
+      JOIN (select id, approved, transdate, 'ar' as transtype FROM ar
+             UNION
+            SELECT id, approved, transdate, 'ap' as transdate FROM ap) a
+            ON (a.id = i.trans_id AND a.approved)
+     WHERE ($1 IS NULL OR a.transdate >= $1)
+           AND ($2 IS NULL OR a.transdate <= $2)
+           AND ($3 IS NULL OR p.partnumber ilike $3 || '%')
+           AND ($4 IS NULL OR plainto_tsquery($4) @@ p.description); 
+  GROUP BY p.id, p.description, p.partnumber
+$$;
 
 COMMIT;

Modified: trunk/sql/modules/Report.sql
===================================================================
--- trunk/sql/modules/Report.sql	2013-01-07 10:04:05 UTC (rev 5504)
+++ trunk/sql/modules/Report.sql	2013-01-07 10:33:34 UTC (rev 5505)
@@ -372,7 +372,7 @@
 (in_entity_class int, in_account_id int, in_entity_name text, 
  in_meta_number text,
  in_employee_id int, in_business_units int[], in_ship_via text, in_on_hold bool,
- in_date_from date, in_date_to date)
+ in_date_from date, in_date_to date, in_partnumber text)
 RETURNS SETOF aa_transactions_line LANGUAGE PLPGSQL AS $$
 DECLARE retval aa_transactions_line;
 
@@ -423,6 +423,11 @@
        AND (in_date_from IS NULL OR a.transdate >= in_date_from)
        AND (in_date_to IS NULL OR a.transdate <= in_date_to)
        AND p.due::numeric(100,2) <> 0
+       AND (in_partnumber IS NULL 
+          OR EXISTS(SELECT 1 FROM invoice inv 
+                      JOIN parts ON inv.parts_id = parts.id
+                     WHERE inv.trans_id = a.id))
+           
 LOOP
    RETURN NEXT retval;
 END LOOP;

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