1 <% include( 'elements/search.html',
3 'name_singular' => $name,
5 'query' => { 'select' => $select,
7 'addl_from' => $addl_from,
9 'extra_sql' => $extra_sql,
10 'order_by' => "ORDER BY $order_by",
12 'count_query' => $count_query,
21 #this is about reports about packages definitions (starting w/commission ones)
22 # while browse/part_pkg.cgi is config->package definitions
24 my $curuser = $FS::CurrentUser::CurrentUser;
26 unless $curuser->access_right('Financial reports');
28 my $conf = new FS::Conf;
29 my $money_char = $conf->config('money_char') || '$';
31 my $title = 'Package definition report';
32 my $name = 'package definition';
45 if (1) { #commission reports
47 if (1) { #employee commission reports
49 $group_by = join(', ',
50 ( map "access_user.$_", qw( usernum username ) ),
51 ( map "part_pkg.$_", qw( pkgpart pkg comment plan ) ),
56 $addl_from .= ' CROSS JOIN access_user ';
58 $match = 'cust_pkg.usernum = access_user.usernum';
60 if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
62 #XXX in this context, agent virt for employees, not package defs
63 my $access_user = qsearchs('access_user', { 'usernum' => $1, })
64 or die "unknown usernum";
66 $title = $access_user->name;
68 $match = 'cust_pkg.usernum = '. $access_user->usernum;
72 push @header, 'Employee';
73 push @fields, sub { shift->get('username'); }; #access_user->name
74 push @links, ''; #link to employee edit w/ACL?
77 push @order_by, 'usernum'; #join to username? we're mostly interested in grouping rather than order
83 } elsif (0) { #agent commission reports
85 $match = 'cust_main.agentnum = agent.agentnum'; #XXX need to join to this
87 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
90 my $agent = qsearchs('agent', { 'agentnum' => $1 })
91 or die "unknown agentnum";
93 $title = $agent->agent;
95 push @header, 'Agent';
96 push @fields, sub { 'XXXagent' };
97 push @links, ''; #link to agent edit w/ACL?
100 push @order_by, 'agentnum'; #join to agent? we're mostly interested in grouping rather than order
109 LEFT JOIN cust_pkg ON ( part_pkg.pkgpart = cust_pkg.pkgpart
111 LEFT JOIN cust_bill_pkg USING ( pkgnum )
112 LEFT JOIN cust_bill USING ( invnum )
115 $title .= ' commission report';
116 $name = "commissionable $name";
121 push @header, 'Package definition';
122 push @fields, 'pkg_comment';
123 push @links, ''; #link to pkg definition edit w/ACL?
126 if (1) { #commission reports
128 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
130 push @where, " cust_bill_pkg.pkgnum > 0 ",
131 " cust_bill._date >= $beginning ",
132 " cust_bill._date <= $ending ";
134 push @header, '#'; # of sales';
135 push @links, ''; #link to detail report
137 push @fields, 'num_cust_pkg';
138 $select .= ", COUNT(DISTINCT cust_pkg.pkgnum) AS num_cust_pkg";
140 push @header, 'Sales';
141 push @links, ''; #link to detail report
143 # push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_sales')); };
145 # ", SUM( SELECT setup+recur $from_cust_bill_pkg_where ) AS pkg_sales";
147 my $part_pkg = shift;
148 my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur)".
149 # " FROM cust_bill_pkg $addl_from $where AND pkgpart = ? AND ";
151 LEFT JOIN cust_pkg USING ( pkgnum )
152 LEFT JOIN cust_bill USING ( invnum )
153 WHERE ". join(' AND ', @where).
154 " AND pkgpart = ? AND ";
155 my @arg = ($part_pkg->pkgpart);
156 if (1) { #employee commission reports
157 $sql .= 'usernum = ?';
158 push @arg, $part_pkg->get('usernum');
159 } elsif (0) { #agent commission reports
160 $match = 'cust_main.agentnum = agent.agentnum';
162 my $sth = dbh->prepare($sql) or die dbh->errstr;
163 $sth->execute(@arg) or die $sth->errstr;
164 $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] );
167 push @header, 'Commission';
168 push @links, ''; #link to detail report
170 #push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_commission')); };
172 my $part_pkg = shift;
173 my $sql = "SELECT SUM(amount) FROM cust_credit
174 LEFT JOIN cust_event USING ( eventnum )
175 LEFT JOIN part_event USING ( eventpart )
176 LEFT JOIN cust_pkg ON ( cust_event.tablenum = cust_pkg.pkgnum )
177 WHERE eventnum IS NOT NULL
178 AND action IN ( 'pkg_employee_credit',
179 'pkg_employee_credit_pkg'
181 AND cust_credit._date >= $beginning
182 AND cust_credit._date <= $ending
184 AND cust_credit.custnum = ?
186 my @arg = ($part_pkg->pkgpart);
187 if (1) { #employee commission reports
189 #XXX in this context, agent virt for employees, not package defs
190 my $access_user = qsearchs('access_user', { 'usernum' => $part_pkg->get('usernum'), })
191 or die "unknown usernum";
193 return $money_char.'0.00' unless $access_user->user_custnum;
194 push @arg, $access_user->user_custnum;
196 } elsif (0) { #agent commission reports
197 push @arg, 'XXXagent_custnum'; #$agent->agent_custnum
199 my $sth = dbh->prepare($sql) or die dbh->errstr;
200 $sth->execute(@arg) or die $sth->errstr;
201 $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] );
207 push @order_by, 'pkgpart'; #pkg?
209 $select ||= 'part_pkg.*';
210 my $extra_sql = scalar(@where) ? 'WHERE ' . join(' AND ', @where) : '';
211 $extra_sql .= "GROUP BY $group_by" if $group_by;
212 my $order_by = join(', ', @order_by);
215 #my $count_query = "SELECT COUNT(*) FROM ( SELECT 1 FROM part_pkg $addl_from $extra_sql ) AS num";
217 my $count_query = "SELECT COUNT(*) FROM ( SELECT DISTINCT part_pkg.pkgpart FROM part_pkg $addl_from $extra_sql ) AS num";