1 <% include( 'elements/search.html',
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,
20 #this is about reports about packages definitions (starting w/commission ones)
21 # while browse/part_pkg.cgi is config->package definitions
23 my $curuser = $FS::CurrentUser::CurrentUser;
25 unless $curuser->access_right('Financial reports');
27 my $conf = new FS::Conf;
28 my $money_char = $conf->config('money_char') || '$';
30 my $title = 'Package definition report';
31 my $name = 'package definition';
42 if (1) { #commission reports
44 if (1) { #employee commission reports
46 $select = 'DISTINCT usernum, username, part_pkg.*';
48 $addl_from .= ' CROSS JOIN access_user ';
50 if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
52 #XXX in this context, agent virt for employees, not package defs
53 my $access_user = qsearchs('access_user', { 'usernum' => $1, })
54 or die "unknown usernum";
56 $title = $access_user->name;
60 push @header, 'Employee';
61 push @fields, sub { shift->get('username'); }; #access_user->name
62 push @links, ''; #link to employee edit w/ACL?
65 push @order_by, 'usernum'; #join to username? we're mostly interested in grouping rather than order
71 } elsif (0) { #agent commission reports
73 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
76 my $agent = qsearchs('agent', { 'agentnum' => $1 })
77 or die "unknown agentnum";
79 $title = $agent->agent;
81 push @header, 'Agent';
82 push @fields, sub { 'XXXagent' };
83 push @links, ''; #link to agent edit w/ACL?
86 push @order_by, 'agentnum'; #join to agent? we're mostly interested in grouping rather than order
94 $title .= ' commission report';
95 $name = "commissionable $name";
100 push @header, 'Package definition';
101 push @fields, 'pkg_comment';
102 push @links, ''; #link to pkg definition edit w/ACL?
105 if (1) { #commission reports
107 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
110 if (1) { #employee commission reports
111 $match = 'cust_pkg.usernum = access_user.usernum';
112 } elsif (0) { #agent commission reports
113 $match = 'cust_main.agentnum = agent.agentnum';
116 my $from_cust_bill_pkg_where = "FROM cust_bill_pkg
117 LEFT JOIN cust_bill USING ( invnum )
118 LEFT JOIN cust_pkg USING ( pkgnum )
119 WHERE cust_bill_pkg.pkgnum > 0
120 AND cust_bill._date >= $beginning
121 AND cust_bill._date <= $ending ";
122 my $and = " AND $match
123 AND cust_pkg.pkgpart = part_pkg.pkgpart";
125 push @where, "EXISTS( SELECT 1 $from_cust_bill_pkg_where $and )";
127 push @header, '#'; # of sales';
128 push @links, ''; #link to detail report
130 push @fields, 'num_cust_pkg';
131 $select .= ", ( SELECT COUNT(DISTINCT pkgnum)
132 $from_cust_bill_pkg_where $and )
134 # push @fields, sub {
135 # my $part_pkg = shift;
137 # #"SELECT COUNT( SELECT DISTINCT pkgnum $from_cust_bill_pkg_where )";
138 # "SELECT COUNT(DISTINCT pkgnum) $from_cust_bill_pkg_where";
139 # my $sth = dbh->prepare($sql) or die dbh->errstr;
140 # $sth->execute or die $sth->errstr;
141 # $sth->fetchrow_arrayref->[0];
144 push @header, 'Sales';
145 push @links, ''; #link to detail report
147 # push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_sales')); };
149 # ", SUM( SELECT setup+recur $from_cust_bill_pkg_where ) AS pkg_sales";
151 my $part_pkg = shift;
152 my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $from_cust_bill_pkg_where AND pkgpart = ? AND ";
153 my @arg = ($part_pkg->pkgpart);
154 if (1) { #employee commission reports
155 $sql .= 'usernum = ?';
156 push @arg, $part_pkg->get('usernum');
157 } elsif (0) { #agent commission reports
158 $match = 'cust_main.agentnum = agent.agentnum';
160 my $sth = dbh->prepare($sql) or die dbh->errstr;
161 $sth->execute(@arg) or die $sth->errstr;
162 $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] );
165 push @header, 'Commission';
166 push @links, ''; #link to detail report
168 #push @fields, sub { $money_char. sprintf('%.2f', shift->get('pkg_commission')); };
170 my $part_pkg = shift;
171 my $sql = "SELECT SUM(amount) FROM cust_credit
172 LEFT JOIN cust_event USING ( eventnum )
173 LEFT JOIN part_event USING ( eventpart )
174 LEFT JOIN cust_pkg ON ( cust_event.tablenum = cust_pkg.pkgnum )
175 WHERE eventnum IS NOT NULL
176 AND action IN ( 'pkg_employee_credit',
177 'pkg_employee_credit_pkg'
179 AND cust_credit._date >= $beginning
180 AND cust_credit._date <= $ending
182 AND cust_credit.custnum = ?
184 my @arg = ($part_pkg->pkgpart);
185 if (1) { #employee commission reports
187 #XXX in this context, agent virt for employees, not package defs
188 my $access_user = qsearchs('access_user', { 'usernum' => $part_pkg->get('usernum'), })
189 or die "unknown usernum";
191 return 0 unless $access_user->user_custnum;
192 push @arg, $access_user->user_custnum;
194 } elsif (0) { #agent commission reports
195 push @arg, 'XXXagent_custnum'; #$agent->agent_custnum
197 my $sth = dbh->prepare($sql) or die dbh->errstr;
198 $sth->execute(@arg) or die $sth->errstr;
199 $money_char. sprintf('%.2f', $sth->fetchrow_arrayref->[0] );
205 push @order_by, 'pkgpart'; #pkg?
207 $select ||= 'part_pkg.*';
208 my $extra_sql = scalar(@where) ? 'WHERE ' . join(' AND ', @where) : '';
209 my $order_by = join(', ', @order_by);
211 my $count_query = "SELECT COUNT(*) FROM part_pkg $addl_from $extra_sql";