-<% 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,
- )
-%>
+<& elements/search.html,
+ 'title' => $title,
+ 'name_singular' => $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,
+ 'sort_fields' => [],
+
+&>
<%init>
#this is about reports about packages definitions (starting w/commission ones)
my $curuser = $FS::CurrentUser::CurrentUser;
die "access denied"
- unless $curuser->access_right('Financial reports');
+ unless $curuser->access_right('Employees: Commission Report'); #that's all this does so far
my $conf = new FS::Conf;
my $money_char = $conf->config('money_char') || '$';
my $select = '';
my $addl_from = '';
my @where = ();
+my @top_where = ();
my @order_by = ();
my @header = ();
my @fields = ();
if (1) { #employee commission reports
- $group_by = join(', ', ( map "access_user.$_", qw( usernum username ) ),
- ( map "part_pkg.$_", qw( pkgpart pkg comment ) ),
- );
+ $group_by = join(', ',
+ ( map "access_user.$_", qw( usernum username ) ),
+ ( map "part_pkg.$_", qw( pkgpart pkg comment plan ) ),
+ );
$select = $group_by;
$title = $access_user->name;
+ push @top_where, 'access_user.usernum = '. $access_user->usernum;
+
} else {
push @header, 'Employee';
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
- my $where = "WHERE cust_bill_pkg.pkgnum > 0
- AND cust_bill._date >= $beginning
- AND cust_bill._date <= $ending ";
- my $and = " AND $match
- AND cust_pkg.pkgpart = part_pkg.pkgpart";
+ 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
" FROM cust_bill_pkg
LEFT JOIN cust_pkg USING ( pkgnum )
LEFT JOIN cust_bill USING ( invnum )
- $where AND pkgpart = ? AND ";
+ WHERE ". join(' AND ', @where).
+ " AND pkgpart = ? AND ";
my @arg = ($part_pkg->pkgpart);
if (1) { #employee commission reports
$sql .= 'usernum = ?';
my $access_user = qsearchs('access_user', { 'usernum' => $part_pkg->get('usernum'), })
or die "unknown usernum";
- return 0 unless $access_user->user_custnum;
+ return $money_char.'0.00' unless $access_user->user_custnum;
push @arg, $access_user->user_custnum;
} elsif (0) { #agent commission reports
push @arg, 'XXXagent_custnum'; #$agent->agent_custnum
}
+ #warn $sql;
+ #warn join(',', @arg);
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) : '';
+push @top_where, @where;
+my $extra_sql = scalar(@top_where) ? 'WHERE ' . join(' AND ', @top_where) : '';
$extra_sql .= "GROUP BY $group_by" if $group_by;
my $order_by = join(', ', @order_by);
-my $count_query = "SELECT COUNT(*) FROM part_pkg $addl_from $extra_sql";
+#XXX agent?
+#my $count_query = "SELECT COUNT(*) FROM ( SELECT 1 FROM part_pkg $addl_from $extra_sql ) AS num";
+#mysql?
+my $count_query = "SELECT COUNT(*) FROM ( SELECT DISTINCT part_pkg.pkgpart FROM part_pkg $addl_from $extra_sql ) AS num";
+#warn $count_query;
</%init>