<& 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)
# while browse/part_pkg.cgi is config->package definitions
my $curuser = $FS::CurrentUser::CurrentUser;
die "access denied"
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 $title = 'Package definition report';
my $name = 'package definition';
my $select = '';
my $addl_from = '';
my @where = ();
my @top_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;
push @top_where, 'access_user.usernum = '. $access_user->usernum;
} 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 $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.*';
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);
#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;