[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [43] trunk
- Subject: SF.net SVN: ledger-smb: [43] trunk
- From: ..hidden..
- Date: Thu, 07 Sep 2006 23:21:58 -0700
Revision: 43
http://svn.sourceforge.net/ledger-smb/?rev=43&view=rev
Author: einhverfr
Date: 2006-09-07 23:21:56 -0700 (Thu, 07 Sep 2006)
Log Message:
-----------
Added inventory activity report
Modified Paths:
--------------
trunk/LedgerSMB/AA.pm
trunk/LedgerSMB/RP.pm
trunk/TODO
trunk/bin/mozilla/rp.pl
trunk/menu.ini
Modified: trunk/LedgerSMB/AA.pm
===================================================================
--- trunk/LedgerSMB/AA.pm 2006-09-08 00:37:32 UTC (rev 42)
+++ trunk/LedgerSMB/AA.pm 2006-09-08 06:21:56 UTC (rev 43)
@@ -619,6 +619,10 @@
$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->{$_}) {
Modified: trunk/LedgerSMB/RP.pm
===================================================================
--- trunk/LedgerSMB/RP.pm 2006-09-08 00:37:32 UTC (rev 42)
+++ trunk/LedgerSMB/RP.pm 2006-09-08 06:21:56 UTC (rev 43)
@@ -15,7 +15,68 @@
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) = @_;
Modified: trunk/TODO
===================================================================
--- trunk/TODO 2006-09-08 00:37:32 UTC (rev 42)
+++ trunk/TODO 2006-09-08 06:21:56 UTC (rev 43)
@@ -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 @@
- 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 @@
- 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 @@
- 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 @@
- 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.
Modified: trunk/bin/mozilla/rp.pl
===================================================================
--- trunk/bin/mozilla/rp.pl 2006-09-08 00:37:32 UTC (rev 42)
+++ trunk/bin/mozilla/rp.pl 2006-09-08 06:21:56 UTC (rev 43)
@@ -77,6 +77,7 @@
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 @@
|;
}
+ 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,7 +765,153 @@
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 {
$form->{padding} = " ";
Modified: trunk/menu.ini
===================================================================
--- trunk/menu.ini 2006-09-08 00:37:32 UTC (rev 42)
+++ trunk/menu.ini 2006-09-08 06:21:56 UTC (rev 43)
@@ -511,6 +511,11 @@
action=report
report=balance_sheet
+[Reports--Inventory Activity]
+module=rp.pl
+action=report
+report=inv_activity
+
[Recurring Transactions]
module=am.pl
action=recurring_transactions
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.