<% include( 'elements/search.html',
              'title'       => $title,
              'name'        => $name,
              'header'      => \@header,
              'query'       => { 'select'    => $select,
                                 'table'     => 'part_pkg',
                                 'addl_from' => $addl_from,
                                 'hashref'   => {},
                                 'extra_sql' => $extra_sql,
                                 'order_by'  => "ORDER BY $order_by",
                               },
              'count_query' => $count_query,
              'fields'      => \@fields,
              'links'       => \@links,
              'align'       => $align,
          )
%>
<%init>

#this is about reports about packages definitions (starting w/commission ones)
# while browse/part_pkg.cgi is config->package definitions

my $curuser = $FS::CurrentUser::CurrentUser;
die "access denied"
  unless $curuser->access_right('Financial reports');

my $conf = new FS::Conf;
my $money_char = $conf->config('money_char') || '$';

my $title = 'Package definition report';
my $name = 'package definition';

my $select = '';
my $addl_from = '';
my @where = ();
my @order_by = ();
my @header = ();
my @fields = ();
my @links  = ();
my $align  = '';
my $group_by = '';
my $match = '';

if (1) { #commission reports

  if (1) { #employee commission reports 

    $group_by = join(', ',
      ( map "access_user.$_", qw( usernum username    ) ),
      ( map "part_pkg.$_",    qw( pkgpart pkg comment plan ) ),
    );

    $select = $group_by;

    $addl_from .= ' CROSS JOIN access_user ';

    $match = 'cust_pkg.usernum = access_user.usernum';

    if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {

      #XXX in this context, agent virt for employees, not package defs
      my $access_user = qsearchs('access_user', { 'usernum' => $1, })
        or die "unknown usernum";

      $title = $access_user->name;

    } else {

      push @header, 'Employee';
      push @fields, sub { shift->get('username'); }; #access_user->name
      push @links, ''; #link to employee edit w/ACL?
      $align .= 'c';

      push @order_by, 'usernum'; #join to username?  we're mostly interested in grouping rather than order

      $title = 'Employee';

    }

  } elsif (0) { #agent commission reports

    $match = 'cust_main.agentnum = agent.agentnum'; #XXX need to join to this

    if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {

      #agent virt
      my $agent = qsearchs('agent', { 'agentnum' => $1 })
        or die "unknown agentnum";

      $title = $agent->agent;

      push @header, 'Agent';
      push @fields, sub { 'XXXagent' };
      push @links, ''; #link to agent edit w/ACL?
      $align .= 'c';

      push @order_by, 'agentnum'; #join to agent?  we're mostly interested in grouping rather than order

    } else {
      $title = 'Agent';
    }

  }

  $addl_from .= "
    LEFT JOIN cust_pkg ON ( part_pkg.pkgpart = cust_pkg.pkgpart
                              AND $match )
    LEFT JOIN cust_bill_pkg USING ( pkgnum )
    LEFT JOIN cust_bill USING ( invnum )
  ";

  $title .= ' commission report';
  $name = "commissionable $name";


}

push @header, 'Package definition';
push @fields, 'pkg_comment';
push @links, ''; #link to pkg definition edit w/ACL?
$align .= 'l';

if (1) { #commission reports

  my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);

  push @where, " cust_bill_pkg.pkgnum > 0      ",
               " cust_bill._date >= $beginning ",
               " cust_bill._date <= $ending    ";

  push @header, '#'; # of sales';
  push @links, ''; #link to detail report
  $align .= 'r';
  push @fields, 'num_cust_pkg';
  $select .= ", COUNT(DISTINCT cust_pkg.pkgnum) AS num_cust_pkg";

  push @header, 'Sales';
  push @links, ''; #link to detail report
  $align .= 'r';
#  push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_sales')); };
#  $select .=
#    ", SUM( SELECT setup+recur $from_cust_bill_pkg_where ) AS pkg_sales";
  push @fields, sub {
    my $part_pkg = shift;
    my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur)".
#              " FROM cust_bill_pkg $addl_from $where AND pkgpart = ? AND ";
              " FROM cust_bill_pkg
                LEFT JOIN cust_pkg  USING ( pkgnum )
                LEFT JOIN cust_bill USING ( invnum )
               WHERE ". join(' AND ', @where).
               " AND pkgpart = ? AND ";
    my @arg = ($part_pkg->pkgpart);
    if (1) { #employee commission reports 
      $sql .= 'usernum = ?';
      push @arg, $part_pkg->get('usernum');
    } elsif (0) { #agent commission reports
      $match = 'cust_main.agentnum = agent.agentnum';
    }
    my $sth = dbh->prepare($sql) or die dbh->errstr;
    $sth->execute(@arg) or die $sth->errstr;
    $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] );
  };

  push @header, 'Commission';
  push @links, ''; #link to detail report
  $align .= 'r';
  #push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_commission')); };
  push @fields, sub {
    my $part_pkg = shift;
    my $sql = "SELECT SUM(amount) FROM cust_credit
                   LEFT JOIN cust_event USING ( eventnum )
                   LEFT JOIN part_event USING ( eventpart )
                   LEFT JOIN cust_pkg ON ( cust_event.tablenum = cust_pkg.pkgnum )
                 WHERE eventnum IS NOT NULL
                   AND action IN ( 'pkg_employee_credit',
                                   'pkg_employee_credit_pkg'
                                 )
                   AND cust_credit._date >= $beginning
                   AND cust_credit._date <= $ending         
                   AND pkgpart = ?
                   AND cust_credit.custnum = ?
              ";
    my @arg = ($part_pkg->pkgpart);
    if (1) { #employee commission reports 

      #XXX in this context, agent virt for employees, not package defs
      my $access_user = qsearchs('access_user', { 'usernum' => $part_pkg->get('usernum'), })
        or die "unknown usernum";

      return 0 unless $access_user->user_custnum;
      push @arg, $access_user->user_custnum;

    } elsif (0) { #agent commission reports
      push @arg, 'XXXagent_custnum'; #$agent->agent_custnum
    }
    my $sth = dbh->prepare($sql) or die dbh->errstr;
    $sth->execute(@arg) or die $sth->errstr;
    $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] );
     
  };

}

push @order_by, 'pkgpart'; #pkg?

$select ||= 'part_pkg.*';
my $extra_sql = scalar(@where) ? 'WHERE ' . join(' AND ', @where) : ''; 
$extra_sql .= "GROUP BY $group_by" if $group_by;
my $order_by = join(', ', @order_by);

#XXX agent?
my $count_query = "SELECT COUNT(*) FROM ( SELECT 1 FROM part_pkg $addl_from $extra_sql ) AS num";
warn $count_query;

</%init>