<% include('/elements/header.html', "$classname Inventory Activity Report") %> <% include('/elements/table-grid.html') %> % my $TH = 'TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1'; <<%$TH%> WIDTH="10%" ALIGN="left">Day (<% time2str("%B %Y", $sdate) %>) % foreach my $day (0..$numdays-1) { <<%$TH%> WIDTH="2%" ALIGN="right"><% $day+1 %> % } % for (my $r=0; $r < scalar(@rows); $r++) { % my $TD = 'TD CLASS="grid" BGCOLOR="'.($r % 2 ? '#ffffff' : '#eeeeee').'"'; <<%$TD%>><% $labels[$r] %> % for my $day (0..$numdays-1) { <<%$TD%> ALIGN="right"><% $rows[$r][$day] %> % } % } <%init> use Date::Parse 'str2time'; use Date::Format 'time2str'; use Data::Dumper 'Dumper'; my ($agentnum, $classnum, $month, $year, $sdate, $edate); $classnum = $cgi->param('classnum'); # may be empty $agentnum = $cgi->param('agentnum'); # may also 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', ); if($agentnum) { push @labels, 'Transfer In', 'Transfer Out'; } push @labels, 'Closing Balance'; my %agent = ('agentnum' => $agentnum) if $agentnum; my %class = ('classnum' => $classnum) if $classnum; my @rows = ( map {[ (0) x $numdays ]} @labels); local($FS::Record::qsearch_qualify_columns) = 0; my $opening_balance = scalar( qsearch('h_inventory_item', { 'svcnum' => '', %agent, %class }, FS::h_inventory_item->sql_h_search($sdate) ) ) || 0; foreach my $day (0..$numdays-1) { $rows[0][$day] = ($day == 0) ? $opening_balance : $rows[-1][$day-1]; my %history; foreach my $action (qw(insert replace_new replace_old)) { $history{$action} = [ qsearch({ 'table' => 'h_inventory_item', 'hashref' => { 'history_action' => $action, %class }, '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(grep {!$agentnum or $_->agentnum == $agentnum} @{ $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(!$agentnum or $h_new->agentnum == $agentnum) { 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]++; } } if($agentnum and $h_old->agentnum != $agentnum and $h_new->agentnum == $agentnum) { # item was transferred from another agent $rows[4][$day]++; } elsif($agentnum and $h_old->agentnum == $agentnum and $h_new->agentnum != $agentnum) { # item was transferred to another agent $rows[5][$day]++; } # Add other cases here. } # Closing balance $rows[-1][$day] = $rows[0][$day] + $rows[1][$day] - $rows[2][$day] + $rows[3][$day]; if($agentnum) { $rows[-1][$day] += $rows[4][$day] - $rows[5][$day]; } }