3 Display the Discount Detail Report:
12 - include_waived_setup
15 <& elements/search.html,
19 count_query => $count_query,
20 count_addl => [ $money_char . '%.2f total' ],
32 FS::UI::Web::cust_header(),
38 if ( $_[0]->pkgdiscountnum ) {
39 # Standard discount, not a waived setup fee
40 my $discount = qsearchs('discount',{
41 discountnum => $_[0]->discountnum
43 return $discount->description;
45 return 'Waive setup fee';
51 if ( $_[0]->discountnum ) {
52 # Standard discount, not a waived setup fee
53 my $discount = qsearchs('discount',{
54 discountnum => $_[0]->discountnum
56 return $discount->classname;
66 $_[0]->amount ? $_[0]->amount : $_[0]->setup_fee
73 if ($row->pkgdiscountnum) {
74 $row->setuprecur eq 'setup'
76 : sprintf( '%.2f', $row->months ),
89 sub { time2str('%b %d %Y', $_[0]->_date) },
92 \&FS::UI::Web::cust_fields,
111 [ "${p}view/cust_bill.cgi?", 'invnum' ], # Invoice
112 [ "${p}view/cust_bill.cgi?", 'invnum' ], # Date
116 ? [ "${p}view/cust_main.cgi?", 'custnum' ]
118 } FS::UI::Web::cust_header()
122 align => 'lcrrlrr'.FS::UI::Web::cust_aligns(),
123 color => [ '','','','','','','',FS::UI::Web::cust_colors() ],
124 style => [ '','','','','','','',FS::UI::Web::cust_styles() ],
130 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
132 my $conf = new FS::Conf;
134 # Query building blocks
136 cust_bill_pkg_discount.billpkgdiscountnum
137 cust_bill_pkg_discount.pkgdiscountnum
138 cust_bill_pkg_discount.amount
139 cust_bill_pkg_discount.months
148 cust_pkg_discount.setuprecur
149 cust_pkg_discount.discountnum
150 cust_pkg.waive_setup),
156 part_pkg_option.pkgpart = cust_pkg.pkgpart
157 AND optionname = 'setup_fee'
162 CAST(cust_bill_pkg_discount.amount AS TEXT),
166 part_pkg_option.pkgpart = cust_pkg.pkgpart
167 AND optionname = 'setup_fee'
172 FS::UI::Web::cust_sql_fields(),
176 LEFT JOIN cust_bill_pkg_discount
179 LEFT JOIN cust_pkg_discount
180 ON cust_bill_pkg_discount.pkgdiscountnum = cust_pkg_discount.pkgdiscountnum
183 ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
188 LEFT JOIN cust_bill USING (invnum)
191 ON cust_bill.custnum = cust_main.custnum
195 # Each entry in @where will be joined with AND
198 # Agent Virtualization
199 $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main'),
201 # Select only rows where there is a discount or waived setup fee
203 cust_bill_pkg_discount.billpkgdiscountnum IS NOT NULL
205 cust_pkg.setup = cust_bill_pkg.sdate
206 AND cust_pkg.waive_setup = 'Y'
214 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
215 push @where, "_date >= $beginning", "_date <= $ending";
219 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
220 push @where, "cust_main.agentnum = $1";
225 if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
227 "( cust_pkg_discount.usernum = $1 OR cust_pkg_discount.usernum IS NULL )";
230 # Filter: Include waived setup fees
231 if ( $cgi->param('include_waived_setup') ) {
232 # Filter a hidden fee attached to a package with a waived setup fee from
233 # causing the waived-fee for that package to be double-counted
234 push @where, 'cust_bill_pkg.pkgpart_override IS NULL';
236 push @where, "cust_bill_pkg_discount.pkgdiscountnum IS NOT NULL";
239 # Filter: Discount Class
240 if ( grep { $_ eq 'discount_classnum' } $cgi->param ) {
243 # if ( ref($params->{'discount_classnum'}) ) {
245 # if ( ref($params->{'discount_classnum'}) eq 'HASH' ) {
246 # @classnum = grep $params->{'discount_classnum'}{$_}, keys %{ $params->{'discount_classnum'} };
247 # } elsif ( ref($params->{'discount_classnum'}) eq 'ARRAY' ) {
248 # @classnum = @{ $params->{'discount_classnum'} };
250 # die 'unhandled discount_classnum ref '. $params->{'discount_classnum'};
254 # } elsif ( $params->{'discount_classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
255 # @classnum = ( $1 );
260 if ( $cgi->param('discount_classnum') =~ /^(\d*)$/ && $1 ne '0' ) {
261 my @classnum = ( $1 );
263 $join .= ' LEFT JOIN discount USING (discountnum) ';
266 my @nums = grep $_, @classnum;
267 push @c_where, 'discount.classnum IN ('. join(',',@nums). ')' if @nums;
268 my $null = scalar( grep { $_ eq '' } @classnum );
269 push @c_where, 'discount.classnum IS NULL' if $null;
271 if ( scalar(@c_where) == 1 ) {
272 push @where, @c_where;
273 } elsif ( @c_where ) {
274 push @where, ' ( '. join(' OR ', @c_where). ' ) ';
281 # #(package) classnum
282 # # not specified: all classes
285 # my $use_override = $cgi->param('use_override');
286 # if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
287 # my $comparison = '';
289 # $comparison = "IS NULL";
291 # $comparison = "= $1";
294 # if ( $use_override ) {
296 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
297 # override.classnum $comparison AND pkgpart_override IS NOT NULL
300 # push @where, "part_pkg.classnum $comparison";
305 if ( $cgi->param('include_waived_setup') ) {
311 cust_bill_pkg_discount.amount,
312 CAST(( SELECT optionvalue
315 part_pkg_option.pkgpart = cust_pkg.pkgpart
316 AND optionname = 'setup_fee'
321 $count_query = "SELECT COUNT(*), SUM(cust_bill_pkg_discount.amount)";
324 my $where = ' WHERE '. join(' AND ', @where);
326 $count_query .= " FROM cust_bill_pkg $join $where";
329 'table' => 'cust_bill_pkg',
330 'addl_from' => $join,
332 'select' => join(', ', @select ),
333 'extra_sql' => $where,
334 'order_by' => 'ORDER BY _date, billpkgdiscountnum',
337 my $money_char = $conf->config('money_char') || '$';