1 <& elements/search.html,
2 'title' => emt('Sales with advertising source'),
3 'name' => emt('line items'),
5 'count_query' => $count_query,
7 ($setup ? $money_char. '%.2f setup' : ()),
8 ($recur ? $money_char. '%.2f recurring' : ()),
9 ($usage ? $money_char. '%.2f usage' : ()),
13 ($setup ? emt('Setup') : ()),
14 ($recur ? emt('Recurring') : ()),
15 ($usage ? emt('Usage') : ()),
30 ($setup ? money_sub('setup') : ()),
31 ($recur ? money_sub('recur_no_usage') : ()),
32 ($usage ? money_sub('recur_usage') : ()),
38 my $cust_pkg = shift->cust_pkg;
39 $cust_pkg ? ucfirst($cust_pkg->status) : '';
43 my $cust_bill_pkg = shift;
44 my $pkgpart = $cust_bill_pkg->pkgpart_override;
46 my $cust_pkg = $cust_bill_pkg->cust_pkg or return '';
47 $pkgpart = $cust_pkg->pkgpart;
49 if ( !exists($report_classes{$pkgpart}) ) {
50 my $part_pkg = FS::part_pkg->by_key($pkgpart);
51 my %opts = $part_pkg->options;
52 $report_classes{$pkgpart} = [
53 map { /^report_option_(\d+)/ ?
54 $report_option_name{$1} :
59 join( '<BR>', @{ $report_classes{$pkgpart} });
63 'referral', # from query
68 ($setup ? 'setup' : ()),
69 ($recur ? 'recur_no_usage' : ()),
70 ($usage ? 'recur_usage' : ()),
84 '', #package/item desc
85 ('') x $x, #setup/recur/usage
94 $clink, #customer name
98 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
99 'align' => 'l' . ('r' x $x) . 'rcrccccrlll',
100 'color' => [ ('') x (5 + $x),
102 my $cust_pkg = shift->cust_pkg;
103 $cust_pkg ? ucfirst($cust_pkg->statuscolor) : '';
116 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
118 my $conf = new FS::Conf;
120 my $setup = $cgi->param('setup') ? 1 : 0;
121 my $recur = $cgi->param('recur') ? 1 : 0;
122 my $usage = $cgi->param('usage') ? 1 : 0;
124 my $x = $setup + $recur + $usage;
126 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
127 my ($join_cust, $join_pkg ) = ('', '');
129 #here is the agent virtualization
131 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
133 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
135 my @where = ( $agentnums_sql,
136 'cust_bill_pkg.pkgnum != 0', # exclude taxes
137 "cust_bill._date >= $beginning",
138 "cust_bill._date <= $ending",
142 foreach my $status ($cgi->param('status')) {
143 if ( $status =~ /^([- a-z]+)$/ ) { #"one-time charge"
144 push @status_where, "'$status'";
147 if ( @status_where ) {
148 push @where, '('. FS::cust_pkg->status_sql.
149 ') IN (' . join(',', @status_where) .')';
153 foreach my $refnum ($cgi->param('refnum')) {
154 if ( $refnum =~ /^\d+$/ ) {
155 push @refnum, $refnum;
159 push @where, 'cust_main.refnum IN ('.join(',', @refnum).')';
162 # cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql)
163 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
164 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
165 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
166 join(',', map { $_ || '0' } @classnums ).
171 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
172 push @where, "cust_main.agentnum = $1";
176 # not specified: all classes
179 my $use_override = 1; #$cgi->param('use_override');
180 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
183 $comparison = "IS NULL";
185 $comparison = "= $1";
188 if ( $use_override ) {
190 part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
191 override.classnum $comparison AND pkgpart_override IS NOT NULL
194 push @where, "part_pkg.classnum $comparison";
199 my @report_option = grep /^\d+$/, ( $cgi->param('report_option') );
200 if ( @report_option ) {
201 @report_option = map { "'report_option_$_'" } @report_option;
202 push @where, "EXISTS(
203 SELECT 1 FROM part_pkg_option WHERE optionname IN (".
204 join(',', @report_option).") AND (
205 part_pkg_option.pkgpart = cust_pkg.pkgpart AND pkgpart_override IS NULL
206 OR part_pkg_option.pkgpart = pkgpart_override
212 FS::cust_bill_pkg->charged_sql('', '', setuprecur => 'setup');
214 FS::cust_bill_pkg->charged_sql('', '', setuprecur => 'recur', no_usage => 1);
215 my $usage_sql = FS::cust_bill_pkg->usage_sql;
217 # exclude zero-amount items
219 push @orwhere, "(cust_bill_pkg.setup > 0)" if $setup;
220 push @orwhere, "($recur_sql > 0)" if $recur;
221 push @orwhere, "($usage_sql > 0)" if $usage;
222 push @where, '('.join(' OR ', @orwhere).')' if @orwhere;
224 $join_cust = ' JOIN cust_bill USING ( invnum )
225 LEFT JOIN cust_main USING ( custnum )
226 LEFT JOIN part_referral USING ( refnum )
227 LEFT JOIN agent ON cust_main.agentnum = agent.agentnum
230 $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum )
231 LEFT JOIN part_pkg USING ( pkgpart )
232 LEFT JOIN part_pkg AS override
233 ON pkgpart_override = override.pkgpart
234 LEFT JOIN pkg_class ON '; #...
236 if ( $use_override ) {
237 # join to whichever pkgpart is appropriate
239 ( pkgpart_override IS NULL AND part_pkg.classnum = pkg_class.classnum )
240 OR ( pkgpart_override IS NOT NULL AND override.classnum = pkg_class.classnum )';
242 $join_pkg .= 'part_pkg.classnum = pkg_class.classnum';
245 my $where = ' WHERE '. join(' AND ', @where);
247 # setup and recurring only
248 my $count_query = "SELECT
250 ($setup ? ", SUM($setup_sql)" : '').
251 ($recur ? ", SUM($recur_sql)" : '').
252 ($usage ? ", SUM($usage_sql)" : '').
259 my $paid_sql = FS::cust_bill_pkg->paid_sql('', '');
260 my $last_pay_sql = "SELECT MAX(_date)
261 FROM cust_bill_pay JOIN cust_bill_pay_pkg USING (billpaynum)
262 WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum";
264 push @select, 'part_pkg.pkg',
270 'part_referral.referral',
271 "($paid_sql) AS paid",
272 "($last_pay_sql) AS last_pay",
273 "($recur_sql) AS recur_no_usage",
274 "($usage_sql) AS recur_usage",
275 'pkg_class.classname',
280 'table' => 'cust_bill_pkg',
281 'addl_from' => "$join_cust $join_pkg",
283 'select' => join(",\n", @select ),
284 'extra_sql' => $where,
285 'order_by' => 'ORDER BY cust_bill._date, billpkgnum',
288 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
289 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
291 my $conf = new FS::Conf;
292 my $money_char = $conf->config('money_char') || '$';
294 my %report_classes; #cache
295 my %report_option_name =
296 map { $_->num => $_->name } qsearch('part_pkg_report_option', {});
298 # should this be in Mason.pm or something?
300 $conf ||= new FS::Conf;
301 $money_char ||= $conf->config('money_char') || '$';
304 $money_char . sprintf('%.2f', $_[0]->get($field));
311 my $value = $_[0]->get($field);
312 $value ? time2str('%b %d %Y', $value) : '';