X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fpart_pkg.html;h=a90f13c951ff23832c12066b2184d75ae15e2a0c;hp=915dbf4482f56509aead0c5ee874c09f2c70a7d3;hb=f30eaaf66cd0a947f388a03edd4522ba92a367bb;hpb=b5c4237a34aef94976bc343c8d9e138664fc3984 diff --git a/httemplate/search/part_pkg.html b/httemplate/search/part_pkg.html index 915dbf448..a90f13c95 100644 --- a/httemplate/search/part_pkg.html +++ b/httemplate/search/part_pkg.html @@ -1,20 +1,21 @@ -<% 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) @@ -22,7 +23,7 @@ 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') || '$'; @@ -33,20 +34,30 @@ 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 - $select = 'DISTINCT usernum, username, part_pkg.*'; + $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 @@ -55,6 +66,8 @@ if (1) { #commission reports $title = $access_user->name; + push @top_where, 'access_user.usernum = '. $access_user->usernum; + } else { push @header, 'Employee'; @@ -70,6 +83,8 @@ if (1) { #commission reports } elsif (0) { #agent commission reports + $match = 'cust_main.agentnum = agent.agentnum'; #XXX need to join to this + if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { #agent virt @@ -91,6 +106,13 @@ if (1) { #commission reports } + $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"; @@ -106,40 +128,15 @@ 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 @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 .= ", ( 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]; -# }; + $select .= ", COUNT(DISTINCT cust_pkg.pkgnum) AS num_cust_pkg"; push @header, 'Sales'; push @links, ''; #link to detail report @@ -149,7 +146,13 @@ if (1) { #commission reports # ", 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 $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 = ?'; @@ -188,12 +191,14 @@ if (1) { #commission reports 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] ); @@ -205,9 +210,15 @@ if (1) { #commission reports 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;