[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[5505] trunk
- Subject: SF.net SVN: ledger-smb:[5505] trunk
- From: ..hidden..
- Date: Mon, 07 Jan 2013 10:33:34 +0000
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.