diff options
| -rwxr-xr-x | LedgerSMB/AA.pm | 4 | ||||
| -rwxr-xr-x | LedgerSMB/RP.pm | 61 | ||||
| -rw-r--r-- | TODO | 22 | ||||
| -rwxr-xr-x | bin/mozilla/rp.pl | 177 | ||||
| -rw-r--r-- | menu.ini | 5 |
5 files changed, 263 insertions, 6 deletions
diff --git a/LedgerSMB/AA.pm b/LedgerSMB/AA.pm index 92816650..f1606153 100755 --- a/LedgerSMB/AA.pm +++ b/LedgerSMB/AA.pm @@ -619,6 +619,10 @@ sub transactions { $form->{open} = $form->{closed} = 0; } } + if ($form->{partsid}){ + $where .= " AND a.id IN (select trans_id FROM invoice + WHERE parts_id = $form->{partsid})"; + } for (qw(ponumber shipvia notes)) { if ($form->{$_}) { diff --git a/LedgerSMB/RP.pm b/LedgerSMB/RP.pm index 946d4c69..9586c298 100755 --- a/LedgerSMB/RP.pm +++ b/LedgerSMB/RP.pm @@ -15,6 +15,67 @@ package RP; +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}; + + + unless ($form->{sort_col}){ + $form->{sort_col} = 'partnumber'; + } + + my $dbh = $form->dbconnect($myconfig) || $form->dberror(); + + my $where = ''; + if ($form->{fromdate}){ + $where .= "AND coalesce(ar.duedate, ap.duedate) >= ".$dbh->quote($form->{fromdate}); + } + if ($form->{todate}){ + $where .= "AND coalesce(ar.duedate, ap.duedate) < ".$dbh->quote($form->{todate}). " "; + } + if ($form->{partnumber}){ + $where .= qq|AND p.partnumber ILIKE '%|.$form->{partnumber}.qq|%' |; + } + if ($form->{description}){ + $where .= q|AND p.description ILIKE '%|.$form->{description}.q|%' |; + } + $where =~ 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 + INNER 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)) { + $ref->{net_income} = $ref->{revenue} - $ref->{expense}; + map {$ref->{$_} =~ s/^\s*//} @cols; + map {$ref->{$_} =~ s/\s*$//} @cols; + push @{$form->{TB}}, $ref; + } + $sth->finish; + $dbh->disconnect; + +} + + sub yearend_statement { my ($self, $myconfig, $form) = @_; @@ -1,4 +1,9 @@ +* indicates items which will make the next release +% are tasks that are good for helping get up to speed in the code. + Higher priorities +- Fix the db upgrade routine so that it can be safely used between software + versions - Move all other floats to NUMERICs in the database schemas. - Create a new naming system that allows us to track db schema changes between different versions of SQL-Ledger and LedgerSMB and @@ -15,10 +20,11 @@ Higher priorities - drop Oracle support? Oracle support is already largely abandoned. Not sure if we should drop the code though... Chris Travers -- code cleanup (move to established coding standards, not structural yet) -- xhtml compliant code +- %code cleanup (move to established coding standards, not structural yet) + In Progress +- %xhtml compliant code - central default database (maybe authentication abstraction here?) -- Integrate a sales data report. +- * Integrate a sales data report. Available as a diff at http://www.metatrontech.com/projects/ - Bundle script for generating email reports when parts are low. To be included in next version. Work is already done. @@ -26,7 +32,7 @@ Higher priorities - Remove Dieter's sessionid timestamp since it serves no purpose. - authentication abstraction for Kerberos, LDAP authentication, and the like. - Authentication module for PostgreSQL databases. -- Account transactions available from Balance Sheets and Income Statements via +- %Account transactions available from Balance Sheets and Income Statements via links. - Replace the defaults table with one that is more normalized. - Fix printing for Windows. @@ -41,10 +47,10 @@ Medium-longer term - Command-line wrapper that is easy to use.... - SOAP or HTTP based interfaces (OpenLedger?) - Credit Card Processing -- Tighten up integrity controls in schema by replacing the insert/update with +- %Tighten up integrity controls in schema by replacing the insert/update with a single insert and adding foreign keys. - Portable Data Terminal integration. -- Merge web directories so that maintaining frames and no-frames browsers +- %Merge web directories so that maintaining frames and no-frames browsers is not a problem. No-frame browsers are important in some areas where screen resolution is limited and text-based keyboard entry is used. I don't think @@ -54,3 +60,7 @@ Medium-longer term - Convert to use persistant database connections. - Basic payroll module - Multiple Shipto's associated with customers/vendors. +- Enforce transaction safety and protect against lost updates when editing + transactions +- %Add a javascript calendar to all date fields. Must not cause problems when + Javascript is not available. diff --git a/bin/mozilla/rp.pl b/bin/mozilla/rp.pl index c4e6eec9..ff3beec2 100755 --- a/bin/mozilla/rp.pl +++ b/bin/mozilla/rp.pl @@ -77,6 +77,7 @@ sub report { receipts => { title => 'Receipts', vc => 'customer' }, payments => { title => 'Payments' }, projects => { title => 'Project Transactions' }, + inv_activity => { title => 'Inventory Activity'}, ); $form->{title} = $locale->text($report{$form->{report}}->{title}); @@ -209,6 +210,36 @@ sub report { |; } + if ($form->{report} eq "inv_activity"){ + $gifi = ''; + print qq| + <input type=hidden name=nextsub value=generate_inv_activity> + <tr> + <th align=right>|.$locale->text('From').qq|</th> + <td><input name=fromdate size=11 title="$myconfig{dateformat}" value=$form->{fromdate}></td> + <th align=right>|.$locale->text('To').qq|</th> + <td><input name=todate size=11 title="$myconfig{dateformat}"></td> + </tr> + <tr> + <th align=right>|.$locale->text('Period').qq|</th> + <td colspan=3> + <select name=frommonth>$form->{selectaccountingmonth}</select> + <select name=fromyear>$form->{selectaccountingyear}</select> + <input name=interval class=radio type=radio value=0 checked>|.$locale->text('Current').qq| + <input name=interval class=radio type=radio value=1>|.$locale->text('Month').qq| + <input name=interval class=radio type=radio value=3>|.$locale->text('Quarter').qq| + <input name=interval class=radio type=radio value=12>|.$locale->text('Year').qq| + </td> + </tr> + </table> + <table> + <tr> + <th>|.$locale->text("Part Number").qq|</th> + <td><input name=partnumber></td> + <th>|.$locale->text('Description').qq|</th> + <td><input type=text name=description></td> + </tr>|; + } if ($form->{report} eq "income_statement") { print qq| $project @@ -734,6 +765,152 @@ $gifi sub continue { &{$form->{nextsub}} }; +sub generate_inv_activity { + $form->header; + + RP->inventory_activity(\%myconfig, \%$form); + + $title = $form->escape($form->{title}); + +# if ($form->{department}) { +# ($department) = split /--/, $form->{department}; +# $options = $locale->text('Department')." : $department<br>"; +# $department = $form->escape($form->{department}); +# } +## if ($form->{projectnumber}) { +# ($projectnumber) = split /--/, $form->{projectnumber}; +# $options .= $locale->text('Project Number')." : $projectnumber<br>"; +# $projectnumber = $form->escape($form->{projectnumber}); +# } + + # if there are any dates + if ($form->{fromdate} || $form->{todate}) { + if ($form->{fromdate}) { + $fromdate = $locale->date(\%myconfig, $form->{fromdate}, 1); + } + if ($form->{todate}) { + $todate = $locale->date(\%myconfig, $form->{todate}, 1); + } + + $form->{period} = "$fromdate - $todate"; + } else { + $form->{period} = $locale->date(\%myconfig, $form->current_date(\%myconfig), 1); + + } + $options .= $form->{period}; + + @column_index = qw(partnumber description sold revenue received expense); + + $href = qq|rp.pl?path=$form->{path}&action=continue&accounttype=$form->{accounttype}&login=$form->{login}&sessionid=$form->{sessionid}&fromdate=$form->{fromdate}&todate=$form->{todate}&l_heading=$form->{l_heading}&l_subtotal=$form->{l_subtotal}&department=$department&projectnumber=$projectnumber&project_id=$form->{project_id}&title=$title&nextsub=$form->{nextsub}|; + + $column_header{partnumber} = qq| + <th class=listheading><a class=listheading href="$href&sort_col=partnumber">| + .$locale->text('Part Number').qq|</a></th>|; + $column_header{description} = qq| + <th class=listheading><a class=listheading href="$href&sort_col=description">| + .$locale->text('Description').qq|</a></th>|; + $column_header{sold} = qq| + <th class=listheading><a class=listheading href="$href&sort_col=sold">| + .$locale->text('Sold').qq|</a></th>|; + $column_header{revenue} = qq| + <th class=listheading><a class=listheading href="$href&sort_col=revenue">| + .$locale->text('Revenue').qq|</a></th>|; + $column_header{received} = qq| + <th class=listheading><a class=listheading href="$href&sort_col=received">| + .$locale->text('Received').qq|</a></th>|; + $column_header{expense} = qq| + <th class=listheading><a class=listheading href="$href&sort_col=expense">| + .$locale->text('Expense').qq|</a></th>|; + + + + print qq| +<body> + +<table width=100%> + <tr> + <th class=listtop>$form->{title}</th> + </tr> + <tr height="5"></tr> + <tr> + <td>$options</td> + </tr> + <tr> + <td> + <table width=100%> + <tr>|; + + map { print "$column_header{$_}\n" } @column_index; + + print qq| + </tr> +|; + + + + if ($form->{sort_col} eq 'qty' || $form->{sort_col} eq 'revenue'){ + $form->{sort_type} = 'numeric'; + } + $i = 0; + $cols = "l_transdate=Y&l_name=Y&l_invnumber=Y&summary=1"; + $dates= "transdatefrom=$form->{fromdate}&transdateto=$form->{todate}&year=$form->{fromyear}&month=$form->{frommonth}&interval=$form->{interval}"; + $base="path=$form->{path}&login=$form->{login}&sessionid=$form->{sessionid}"; + + $form->{callback} = "rp.pl?action=continue&$base"; + $form->{callback} = $form->escape($form->{callback}); + $callback = "callback=$form->{callback}"; + # sort the whole thing by account numbers and display + foreach $ref (@{ $form->{TB} }) { + $description = $form->escape($ref->{description}); + $i = $i % 2; + + $pnumhref="ic.pl?action=edit&id=$ref->{id}&$base&callback=$form->{callback}"; + $soldhref="ar.pl?action=transactions&partsid=$ref->{id}&$base&$cols&$dates&$callback"; + $rechref="ap.pl?action=transactions&partsid=$ref->{id}&$base&$cols&$dates&callback=$form->{callback}"; + + $ml = ($ref->{category} =~ /(A|E)/) ? -1 : 1; + + $debit = $form->format_amount(\%myconfig, $ref->{debit}, 2, " "); + $credit = $form->format_amount(\%myconfig, $ref->{credit}, 2, " "); + $begbalance = $form->format_amount(\%myconfig, $ref->{balance} * $ml, 2, " "); + $endbalance = $form->format_amount(\%myconfig, ($ref->{balance} + $ref->{amount}) * $ml, 2, " "); + + $ref->{partnumber} = qq|<a href="$pnumhref">$ref->{partnumber}</a>|; + $ref->{sold} = qq|<a href="$soldhref">$ref->{sold}</a>|; + $ref->{received} = qq|<a href="$rechref">$ref->{received}<a/>|; + map { $column_data{$_} = "<td> </td>" } + @column_index; + + + + print qq| + <tr class=listrow$i> + |; + map { print "<td>$ref->{$_}</td>\n" } @column_index; + + print qq| + </tr> +|; + ++$i; + } + + + print qq| + </tr> + </table> + </td> + </tr> + <tr> + <td><hr size=3 noshade></td> + </tr> +</table> + +</body> +</html> +|; + +} + sub generate_income_statement { @@ -511,6 +511,11 @@ module=rp.pl action=report report=balance_sheet +[Reports--Inventory Activity] +module=rp.pl +action=report +report=inv_activity + [Recurring Transactions] module=am.pl action=recurring_transactions |