Added inventory activity report

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@43 4979c152-3d1c-0410-bac9-87ea11338e46

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) = @_;
diff --git a/TODO b/TODO
index da57b5c0..84671d3a 100644
--- a/TODO
+++ b/TODO
@@ -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, "&nbsp;");
+    $credit = $form->format_amount(\%myconfig, $ref->{credit}, 2, "&nbsp;");
+    $begbalance = $form->format_amount(\%myconfig, $ref->{balance} * $ml, 2, "&nbsp;");
+    $endbalance = $form->format_amount(\%myconfig, ($ref->{balance} + $ref->{amount}) * $ml, 2, "&nbsp;");
+
+    $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>&nbsp;</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 {
 
diff --git a/menu.ini b/menu.ini
index 8a9b4c67..3bb3b7c5 100644
--- a/menu.ini
+++ b/menu.ini
@@ -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