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 return sprintf( '%.2f', $row->months );
87 sub { time2str('%b %d %Y', $_[0]->_date) },
90 \&FS::UI::Web::cust_fields,
109 [ "${p}view/cust_bill.cgi?", 'invnum' ], # Invoice
110 [ "${p}view/cust_bill.cgi?", 'invnum' ], # Date
114 ? [ "${p}view/cust_main.cgi?", 'custnum' ]
116 } FS::UI::Web::cust_header()
120 align => 'lcrrlrr'.FS::UI::Web::cust_aligns(),
121 color => [ '','','','','','','',FS::UI::Web::cust_colors() ],
122 style => [ '','','','','','','',FS::UI::Web::cust_styles() ],
127 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
129 my $conf = new FS::Conf;
131 # Query building blocks
133 cust_bill_pkg_discount.billpkgdiscountnum
134 cust_bill_pkg_discount.pkgdiscountnum
135 cust_bill_pkg_discount.amount
136 cust_bill_pkg_discount.months
145 cust_pkg_discount.discountnum
146 cust_pkg.waive_setup),
152 part_pkg_option.pkgpart = cust_pkg.pkgpart
153 AND optionname = 'setup_fee'
158 CAST(cust_bill_pkg_discount.amount AS TEXT),
162 part_pkg_option.pkgpart = cust_pkg.pkgpart
163 AND optionname = 'setup_fee'
168 FS::UI::Web::cust_sql_fields(),
172 LEFT JOIN cust_bill_pkg_discount
175 LEFT JOIN cust_pkg_discount
176 ON cust_bill_pkg_discount.pkgdiscountnum = cust_pkg_discount.pkgdiscountnum
179 ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
184 LEFT JOIN cust_bill USING (invnum)
187 ON cust_bill.custnum = cust_main.custnum
191 # Each entry in @where will be joined with AND
194 # Agent Virtualization
195 $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main'),
197 # Select only rows where there is a discount or waived setup fee
199 cust_bill_pkg_discount.billpkgdiscountnum IS NOT NULL
201 cust_pkg.setup = cust_bill_pkg.sdate
202 AND cust_pkg.waive_setup = 'Y'
210 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
211 push @where, "_date >= $beginning", "_date <= $ending";
215 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
216 push @where, "cust_main.agentnum = $1";
221 if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
223 "( cust_pkg_discount.usernum = $1 OR cust_pkg_discount.usernum IS NULL )";
226 # Filter: Include waived setup fees
227 if ( $cgi->param('include_waived_setup') ) {
228 # Filter a hidden fee attached to a package with a waived setup fee from
229 # causing the waived-fee for that package to be double-counted
230 push @where, 'cust_bill_pkg.pkgpart_override IS NULL';
232 push @where, "cust_bill_pkg_discount.pkgdiscountnum IS NOT NULL";
235 # Filter: Discount Class
236 if ( grep { $_ eq 'discount_classnum' } $cgi->param ) {
239 # if ( ref($params->{'discount_classnum'}) ) {
241 # if ( ref($params->{'discount_classnum'}) eq 'HASH' ) {
242 # @classnum = grep $params->{'discount_classnum'}{$_}, keys %{ $params->{'discount_classnum'} };
243 # } elsif ( ref($params->{'discount_classnum'}) eq 'ARRAY' ) {
244 # @classnum = @{ $params->{'discount_classnum'} };
246 # die 'unhandled discount_classnum ref '. $params->{'discount_classnum'};
250 # } elsif ( $params->{'discount_classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
251 # @classnum = ( $1 );
256 if ( $cgi->param('discount_classnum') =~ /^(\d*)$/ && $1 ne '0' ) {
257 my @classnum = ( $1 );
259 $join .= ' LEFT JOIN discount USING (discountnum) ';
262 my @nums = grep $_, @classnum;
263 push @c_where, 'discount.classnum IN ('. join(',',@nums). ')' if @nums;
264 my $null = scalar( grep { $_ eq '' } @classnum );
265 push @c_where, 'discount.classnum IS NULL' if $null;
267 if ( scalar(@c_where) == 1 ) {
268 push @where, @c_where;
269 } elsif ( @c_where ) {
270 push @where, ' ( '. join(' OR ', @c_where). ' ) ';
277 # #(package) classnum
278 # # not specified: all classes
281 # my $use_override = $cgi->param('use_override');
282 # if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
283 # my $comparison = '';
285 # $comparison = "IS NULL";
287 # $comparison = "= $1";
290 # if ( $use_override ) {
292 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
293 # override.classnum $comparison AND pkgpart_override IS NOT NULL
296 # push @where, "part_pkg.classnum $comparison";
301 if ( $cgi->param('include_waived_setup') ) {
307 cust_bill_pkg_discount.amount,
308 CAST(( SELECT optionvalue
311 part_pkg_option.pkgpart = cust_pkg.pkgpart
312 AND optionname = 'setup_fee'
317 $count_query = "SELECT COUNT(*), SUM(cust_bill_pkg_discount.amount)";
320 my $where = ' WHERE '. join(' AND ', @where);
322 $count_query .= " FROM cust_bill_pkg $join $where";
325 'table' => 'cust_bill_pkg',
326 'addl_from' => $join,
328 'select' => join(', ', @select ),
329 'extra_sql' => $where,
330 'order_by' => 'ORDER BY _date, billpkgdiscountnum',
333 my $money_char = $conf->config('money_char') || '$';