diff options
Diffstat (limited to 'httemplate/search/part_pkg.html')
| -rw-r--r-- | httemplate/search/part_pkg.html | 213 |
1 files changed, 213 insertions, 0 deletions
diff --git a/httemplate/search/part_pkg.html b/httemplate/search/part_pkg.html new file mode 100644 index 0000000..915dbf4 --- /dev/null +++ b/httemplate/search/part_pkg.html @@ -0,0 +1,213 @@ +<% 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 = ''; + +if (1) { #commission reports + + if (1) { #employee commission reports + + $select = 'DISTINCT usernum, username, part_pkg.*'; + + $addl_from .= ' CROSS JOIN access_user '; + + 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 + + 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'; + } + + } + + $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); + + my $match = ''; + if (1) { #employee commission reports + $match = 'cust_pkg.usernum = access_user.usernum'; + } elsif (0) { #agent commission reports + $match = 'cust_main.agentnum = agent.agentnum'; + } + + my $from_cust_bill_pkg_where = "FROM cust_bill_pkg + LEFT JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_pkg USING ( pkgnum ) + 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, "EXISTS( SELECT 1 $from_cust_bill_pkg_where $and )"; + + push @header, '#'; # of sales'; + push @links, ''; #link to detail report + $align .= 'r'; + push @fields, 'num_cust_pkg'; + $select .= ", ( SELECT COUNT(DISTINCT pkgnum) + $from_cust_bill_pkg_where $and ) + AS num_cust_pkg"; +# push @fields, sub { +# my $part_pkg = shift; +# my $sql = +# #"SELECT COUNT( SELECT DISTINCT pkgnum $from_cust_bill_pkg_where )"; +# "SELECT COUNT(DISTINCT pkgnum) $from_cust_bill_pkg_where"; +# my $sth = dbh->prepare($sql) or die dbh->errstr; +# $sth->execute or die $sth->errstr; +# $sth->fetchrow_arrayref->[0]; +# }; + + 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_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) : ''; +my $order_by = join(', ', @order_by); + +my $count_query = "SELECT COUNT(*) FROM part_pkg $addl_from $extra_sql"; + +</%init> |