From 0bde4d5e36f26d346ccc521df89ad29d9e7bfe90 Mon Sep 17 00:00:00 2001
From: mark <mark>
Date: Wed, 26 May 2010 06:39:14 +0000
Subject: [PATCH] RT#8460: inventory activity report

---
 httemplate/elements/menu.html                  |   2 +-
 httemplate/search/h_inventory_item.html        | 103 +++++++++++++++++++++++++
 httemplate/search/report_h_inventory_item.html |  25 ++++++
 3 files changed, 129 insertions(+), 1 deletion(-)
 create mode 100644 httemplate/search/h_inventory_item.html
 create mode 100644 httemplate/search/report_h_inventory_item.html

diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html
index 2d28e49d4..5ce49c3a7 100644
--- a/httemplate/elements/menu.html
+++ b/httemplate/elements/menu.html
@@ -215,7 +215,7 @@ $report_packages{'Advanced package reports'} =  [ $fsurl.'search/report_cust_pkg
 
 tie my %report_inventory, 'Tie::IxHash',
   'Inventory by agent' => [ $fsurl.'search/report_agent_inventory.html', '' ],
-  #'Inventory activity' => [ $fsurl.'search/report_h_inventory_item.html', '' ],
+  'Inventory activity' => [ $fsurl.'search/report_h_inventory_item.html', '' ],
 ;
 
 tie my %report_rating, 'Tie::IxHash',
diff --git a/httemplate/search/h_inventory_item.html b/httemplate/search/h_inventory_item.html
new file mode 100644
index 000000000..2ac00651c
--- /dev/null
+++ b/httemplate/search/h_inventory_item.html
@@ -0,0 +1,103 @@
+<% include('/elements/header.html', "$classname Inventory Activity Report") %>
+<% include('/elements/table-grid.html') %>
+  <TR>
+% my $TH = 'TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1';
+    <<%$TH%> WIDTH="10%" ALIGN="left">Day (<% time2str("%B %Y", $sdate) %>)</TH>
+% foreach my $day (0..$numdays-1) {
+    <<%$TH%> WIDTH="2%" ALIGN="right"><% $day+1 %></TH>
+% }
+  </TR>
+% for (my $r=0; $r < scalar(@rows); $r++) {
+  <TR>
+% my $TD = 'TD CLASS="grid" BGCOLOR="'.($r % 2 ? '#ffffff' : '#eeeeee').'"';
+    <<%$TD%>><% $labels[$r] %></TD>
+%   for my $day (0..$numdays-1) {
+    <<%$TD%> ALIGN="right"><% $rows[$r][$day] %></TD>
+%   }
+  </TR>
+% }
+</TABLE>
+
+<%init>
+use Date::Parse 'str2time';
+use Date::Format 'time2str';
+#use Data::Dumper::HTML 'dumper_html';
+
+my ($classnum, $month, $year, $sdate, $edate);
+$classnum = $cgi->param('classnum'); # may be empty
+my $classname = '';
+if($classnum) {
+  my $class = qsearchs('inventory_class', { classnum => $classnum });
+  die "classnum $classnum not found!" if !$class;
+  $classname = $class->classname . ' ';
+}
+
+$month = $cgi->param('_month') || time2str('%m', time);
+$year = $cgi->param('_year') || time2str('%Y', time);
+
+$sdate = str2time("$year-$month-01");
+$edate = str2time($year + ($month == 12 ? 1 : 0) .
+                  '-' .
+                  (($month + 1) % 12 || 12) .
+                  '-01');
+my $numdays = sprintf("%.0f",($edate-$sdate)/86400);
+my @days = (0..$numdays - 1);
+# Initialize each row with zeroes.
+my @labels = (
+  'Opening Balance',
+  'Quantity Received',
+  'Quantity Sold',
+  'Quantity Returned',
+  'Closing Balance',
+);
+my @rows = ( map {[ (0) x $numdays ]} @labels);
+
+foreach my $day (0..$numdays-1) {
+  $rows[0][$day] = $rows[4][$day-1] if($day > 0);
+
+  my %history;
+  foreach my $action (qw(insert replace_new replace_old)) {
+    $history{$action} = [
+                        qsearch({
+                          'table'     => 'h_inventory_item',
+                          'hashref'   => { $classnum ? ('classnum' => $classnum) : (),
+                                           'history_action' => $action },
+                          'order_by'  => 'ORDER BY itemnum, history_date',
+                          'extra_sql' => 
+                            ' AND history_date >= '.($sdate + 86400*$day).
+                            ' AND history_date < ' .($sdate + 86400*($day+1)),
+                          } ) 
+                        ];
+  }
+  # Incoming items: simple, just count the inserts
+  $rows[1][$day] = scalar(@{ $history{'insert'} });
+
+  # Other item changes: trickier.
+  # Notice the order_by parameter above.
+  # Both lists are sorted by itemnum, then by date, so unless some villain has 
+  # been rapidly replacing the same record several times per second, the 
+  # replace_old and replace_new from the same operation will be in the same 
+  # position.
+  while(my $h_new = shift @{ $history{'replace_new'} }) {
+    my $h_old = shift @{ $history{'replace_old'} };
+    die "history error" if !defined($h_old) 
+                           or $h_old->itemnum != $h_new->itemnum;
+    if(!$h_old->svcnum and $h_new->svcnum) {
+      # item was put into service.
+      $rows[2][$day]++;
+    }
+    elsif($h_old->svcnum and !$h_new->svcnum) {
+      # item was taken out of service.
+      $rows[3][$day]++;
+    }
+    # Add other cases here.
+  }
+  # Closing balance
+  $rows[4][$day] = $rows[0][$day]
+                 + $rows[1][$day]
+                 - $rows[2][$day]
+                 + $rows[3][$day];
+}
+
+</%init>
+
diff --git a/httemplate/search/report_h_inventory_item.html b/httemplate/search/report_h_inventory_item.html
new file mode 100644
index 000000000..110455813
--- /dev/null
+++ b/httemplate/search/report_h_inventory_item.html
@@ -0,0 +1,25 @@
+<% include('/elements/header.html', 'Inventory activity report') %>
+
+<FORM ACTION="h_inventory_item.html" METHOD="GET">
+<TABLE BGCOLOR="#cccccc" CELLSPACING="0">
+  <TR>
+    <TD ALIGN="right">Inventory class: </TD>
+    <TD><% include('/elements/select-table.html',
+                      'element_name' => 'classnum',
+                      'table'        => 'inventory_class',
+                      'name_col'     => 'classname',
+                      'value'        => '',
+                      'empty_label'  => '(all)') %></TD>
+  </TR>
+  <TR>
+    <TD ALIGN="right">Time period: </TD>
+    <TD><% include('/elements/select-month_year.html') %></TD>
+  </TR>
+</TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="Get Report">
+</FORM>
+
+<%init>
+</%init>
-- 
2.11.0