X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcustomer_cdr_profit.html;fp=httemplate%2Fsearch%2Fcustomer_cdr_profit.html;h=8dc06636ae51d007ed0aa2b5b9be9c763914c4a0;hb=2f3fd5cbfdec082d780f79b1ff9fab07a0c832ce;hp=0000000000000000000000000000000000000000;hpb=0cb1b7d0916777dacdfd7c7f905e0bca03a5dfd9;p=freeside.git

diff --git a/httemplate/search/customer_cdr_profit.html b/httemplate/search/customer_cdr_profit.html
new file mode 100644
index 000000000..8dc06636a
--- /dev/null
+++ b/httemplate/search/customer_cdr_profit.html
@@ -0,0 +1,253 @@
+% if ( $cgi->param('_type') =~ /(xls)$/ ) {
+<%perl>
+  # egregious false laziness w/ search/report_tax-xls.cgi
+  my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format;
+  my $filename = $cgi->url(-relative => 1);
+  $filename =~ s/\.html$//;
+  $filename .= $format->{extension};
+  http_header('Content-Type' => $format->{mime_type});
+  http_header('Content-Disposition' => qq!attachment;filename="$filename"!);
+
+  my $output = '';
+  my $XLS = IO::String->new($output);
+  my $workbook = $format->{class}->new($XLS)
+    or die "Error opening .xls file: $!";
+
+  my $worksheet = $workbook->add_worksheet('Summary');
+
+  my %format = (
+    header => {
+      size      => 11,
+      bold      => 1,
+      align     => 'center',
+      valign    => 'vcenter',
+      text_wrap => 1,
+    },
+    money => {
+      size      => 11,
+      align     => 'right',
+      valign    => 'bottom',
+      num_format=> 8,
+    },
+    '' => {},
+  );
+  my %default = (
+      font      => 'Calibri',
+      border    => 1,
+  );
+  foreach (keys %format) {
+    my %f = (%default, %{$format{$_}});
+    $format{$_} = $workbook->add_format(%f);
+    $format{"m_$_"} = $workbook->add_format(%f);
+  }
+
+  my ($r, $c) = (0, 0);
+  for my $row (@rows) {
+    $c = 0;
+    my $thisrow = shift @cells;
+    for my $cell (@$thisrow) {
+      if (!ref($cell)) {
+        # placeholder, so increment $c so that we write to the correct place
+        $c++;
+        next;
+      }
+      # format name
+      my $f = '';
+      $f = 'header' if $row->{header} or $cell->{header};
+      $f = 'money' if $cell->{format} eq 'money';
+      if ( $cell->{rowspan} > 1 or $cell->{colspan} > 1 ) {
+        my $range = xl_range_formula(
+          'Summary',
+          $r, $r - 1 + ($cell->{rowspan} || 1),
+          $c, $c - 1 + ($cell->{colspan} || 1)
+        );
+        #warn "merging $range\n";
+        $worksheet->merge_range($range, $cell->{value}, $format{"m_$f"});
+      } else {
+      #warn "writing ".xl_rowcol_to_cell($r, $c)."\n";
+        $worksheet->write( $r, $c, $cell->{value}, $format{$f} );
+      }
+      $c += $cell->{colspan} || 1;
+    } #$cell
+  $r++;
+  } #$row
+  $workbook->close;
+
+  http_header('Content-Length' => length($output));
+  $m->print($output);
+</%perl>
+% } else {
+<& /elements/header.html, $title &>
+% my $myself = $cgi->self_url;
+<P ALIGN="right" CLASS="noprint">
+Download full reports<BR>
+as <A HREF="<% "$myself;_type=xls" %>">Excel spreadsheet</A>
+</P>
+<style type="text/css">
+.report * {
+  background-color: #f8f8f8;
+  border: 1px solid #999999;
+  padding: 2px;
+}
+.report td {
+  text-align: right;
+}
+.total { background-color: #f5f6be; }
+.shaded { background-color: #c8c8c8; }
+.totalshaded { background-color: #bfc094; }
+</style>
+<table class="report" width="100%" cellspacing=0>
+% foreach my $rowinfo (@rows) {
+  <tr<% $rowinfo->{class} ? ' class="'.$rowinfo->{class}.'"' : ''%>>
+%   my $thisrow = shift @cells;
+%   foreach my $cell (@$thisrow) {
+%     next if !ref($cell); # placeholders
+%     my $td = $cell->{header} ? 'th' : 'td';
+%     my $style = '';
+%     $style .= ' class="'.$cell->{class}.'"' if $cell->{class};
+%     $style .= " rowspan=".$cell->{rowspan} if $cell->{rowspan} > 1;
+%     $style .= " colspan=".$cell->{colspan} if $cell->{colspan} > 1;
+%     $style .= ' style="color: red"' if $cell->{value} < 0;
+      <<%$td%><%$style%>><% $cell->{value} |h %></<%$td%>>
+%   }
+  </tr>
+% }
+</table>
+
+<& /elements/footer.html &>
+% }
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports')
+      && $FS::CurrentUser::CurrentUser->access_right('List rating data');
+
+my ($agentnum,$sel_agent);
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+  $agentnum = $1;
+  $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } );
+  die "agentnum $agentnum not found!" unless $sel_agent;
+}
+my $title = $sel_agent ? $sel_agent->agent.' ' : '';
+
+$title .= 'Customer CDRs Profit/Loss Report';
+
+my @items  = ('cust_bill_pkg_recur', 'cust_bill_pkg_recur', 'cust_bill_pkg_detail', 'cust_bill_pkg_detail' );
+my @params = ( [], [ 'cost' => 1 ], [], [ 'cost' => 1 ] );
+
+my @labels = ();
+my @cross_params = ();
+
+my %search_hash;
+foreach (qw(agentnum)) {
+  if ( defined $cgi->param($_) ) {
+    $search_hash{$_} = $cgi->param($_);
+  }
+}
+
+my $query = FS::cust_main::Search->search(\%search_hash);
+my @cust_main = qsearch($query);
+
+foreach my $cust_main (@cust_main) {
+  push @cross_params, [ ('custnum' => $cust_main->custnum) ];
+}
+
+my %opt = (
+  items         => \@items,
+  params        => \@params,
+  cross_params  => \@cross_params,
+  agentnum      => $agentnum,
+);
+for ( qw(start_month start_year end_month end_year) ) {
+  if ( $cgi->param($_) =~ /^(\d+)$/ ) {
+    $opt{$_} = $1;
+  }
+}
+
+my $report = FS::Report::Table::Monthly->new(%opt);
+my $data = $report->data;
+
+### False laziness with customer_accounting_summary.html
+my @total;
+
+my @rows; # hashes of row info
+my @cells; # arrayrefs of cell info
+# We use Excel currency format, but not Excel dates, because
+# these are whole months and there's no nice way to express that.
+# This is the historical behavior for monthly reports.
+
+# header row
+$rows[0] = {};
+$cells[0] = [
+  { header => 1, rowspan => 2 },
+  map {
+    { header => 1, colspan => 5, value => time2str('%b %Y', $_) }
+  } @{ $data->{speriod} }
+];
+my $ncols = scalar(@{ $data->{speriod} });
+
+$rows[1] = {};
+$cells[1] = [ '',
+  map { 
+  ( 
+    { header => 1, value => mt('Recur Fee') },
+    { header => 1, value => mt('Recur Cost') },
+    { header => 1, value => mt('Usage Fee') },
+    { header => 1, value => mt('Usage Cost') },
+    { header => 1, value => mt('Profit'), class => 'shaded' },
+  ) } (1..$ncols)
+];
+
+my $row = 0;
+foreach my $cust_main (@cust_main) { # correspond to cross_params
+  my $skip = 1; # skip the customer iff ALL of their values are zero
+  push @rows, {};
+  my @thisrow;
+  # customer name
+  push @thisrow,
+    { value   => $cust_main->name,
+      header  => 1
+    };
+  for my $col (0..$ncols-1) { # the month
+    my $profit = 0;
+    for my $item (0..3) { # recur/recur_cost/usage/usage_cost
+      my $value = $data->{data}[$item][$col][$row];
+      $skip = 0 if abs($value) > 0.005;
+      push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' };
+      $total[$col * 5 + $item] += $value;
+      $profit += (($item % 2) ? -1 : 1) * $value;
+    } #item
+    push @thisrow, { 
+      value => sprintf('%0.2f', $profit), 
+      format => 'money',
+      class => 'shaded',
+    };
+    $total[$col * 5 + 4] += $profit;
+  } #month
+  push @cells, \@thisrow;
+
+  if ( $skip ) {
+    # all values are zero--remove the rows we just added
+    pop @rows;
+    pop @cells;
+  }
+  $row++;
+}
+
+push @rows, { class => 'total' };
+my @thisrow;
+push @thisrow,
+  { value => mt('Total'),
+    header => 1
+  };
+for my $col (0..($ncols * 5)-1) { # month and recur/recur_cost/usage/usage_cost/profit
+  my $value = $total[$col];
+  push @thisrow, { 
+    value => sprintf('%0.2f', $value), 
+    format => 'money',
+    class => ($col % 5 == 4) ? 'totalshaded' : 'total',
+  };
+}
+push @cells, \@thisrow;
+
+</%init>