<%doc> Display the Discount Detail Report: Parameters: - discount_classnum - usernum - agentnum - beginning - ending - include_waived_setup <& elements/search.html, title => 'Discounts', name => 'discounts', query => $query, count_query => $count_query, count_addl => [ $money_char . '%.2f total' ], header => [ qw( Discount Class Amount Months Package Invoice Date ), FS::UI::Web::cust_header(), ], fields => [ # Discount sub { if ( $_[0]->pkgdiscountnum ) { # Standard discount, not a waived setup fee my $discount = qsearchs('discount',{ pkgdiscountnum => $_[0]->pkgdiscountnum }); return $discount->description; } else { return 'Waive setup fee'; } }, # Class sub { if ( $_[0]->discountnum ) { # Standard discount, not a waived setup fee my $discount = qsearchs('discount',{ discountnum => $_[0]->discountnum }); return $discount->classname; } else { return 'n/a'; } }, # Amount sub { sprintf( $money_char.'%.2f', $_[0]->amount ? $_[0]->amount : $_[0]->setup_fee ) }, # Months sub { my $row = shift; if ($row->pkgdiscountnum) { $row->setuprecur eq 'setup' ? 'Setup' : sprintf( '%.2f', $row->months ), } else { return 'Setup'; } }, # Package 'pkg', # Invoice 'invnum', # Date sub { time2str('%b %d %Y', $_[0]->_date) }, # Customer \&FS::UI::Web::cust_fields, ], sort_fields => [ '', # Discount '', # Class 'amount', # Amount 'months', # Months 'pkg', # Package 'invnum', # Invoice '_date', # Date ], links => [ '', # Discount '', # Class '', # Amount '', # Months '', # Package [ "${p}view/cust_bill.cgi?", 'invnum' ], # Invoice [ "${p}view/cust_bill.cgi?", 'invnum' ], # Date ( # Customer map { $_ ne 'Cust. Status' ? [ "${p}view/cust_main.cgi?", 'custnum' ] : '' } FS::UI::Web::cust_header() ), ], align => 'lcrrlrr'.FS::UI::Web::cust_aligns(), color => [ '','','','','','','',FS::UI::Web::cust_colors() ], style => [ '','','','','','','',FS::UI::Web::cust_styles() ], &> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); my $conf = new FS::Conf; # Query building blocks my @select = (qw( cust_bill_pkg_discount.billpkgdiscountnum cust_bill_pkg_discount.pkgdiscountnum cust_bill_pkg_discount.amount cust_bill_pkg_discount.months cust_bill.invnum cust_bill._date part_pkg.pkg cust_main.custnum cust_pkg_discount.setuprecur cust_pkg_discount.discountnum cust_pkg.waive_setup), " ( SELECT optionvalue FROM part_pkg_option WHERE part_pkg_option.pkgpart = cust_pkg.pkgpart AND optionname = 'setup_fee' ) as setup_fee ", " COALESCE( CAST(cust_bill_pkg_discount.amount AS TEXT), ( SELECT optionvalue FROM part_pkg_option WHERE part_pkg_option.pkgpart = cust_pkg.pkgpart AND optionname = 'setup_fee' ) ) as discountorwaive ", FS::UI::Web::cust_sql_fields(), ); my $join = " LEFT JOIN cust_bill_pkg_discount USING (billpkgnum) LEFT JOIN cust_pkg_discount ON cust_bill_pkg_discount.pkgdiscountnum = cust_pkg_discount.pkgdiscountnum LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum LEFT JOIN part_pkg USING (pkgpart) LEFT JOIN cust_bill USING (invnum) LEFT JOIN cust_main ON cust_bill.custnum = cust_main.custnum "; # Each entry in @where will be joined with AND my @where = ( # Agent Virtualization $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main'), # Select only rows where there is a discount or waived setup fee "( cust_bill_pkg_discount.billpkgdiscountnum IS NOT NULL OR ( cust_pkg.setup = cust_bill_pkg.sdate AND cust_pkg.waive_setup = 'Y' ) )", ); # Filter: Date Range my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @where, "_date >= $beginning", "_date <= $ending"; # Filter: Agent if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } # Filter: User if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { push @where, "( cust_pkg_discount.usernum = $1 OR cust_pkg_discount.usernum IS NULL )"; } # Filter: Include waived setup fees if ( !$cgi->param('include_waived_setup') ) { push @where, "cust_bill_pkg_discount.pkgdiscountnum IS NOT NULL"; } # Filter: Discount Class if ( grep { $_ eq 'discount_classnum' } $cgi->param ) { # my @classnum = (); # if ( ref($params->{'discount_classnum'}) ) { # # if ( ref($params->{'discount_classnum'}) eq 'HASH' ) { # @classnum = grep $params->{'discount_classnum'}{$_}, keys %{ $params->{'discount_classnum'} }; # } elsif ( ref($params->{'discount_classnum'}) eq 'ARRAY' ) { # @classnum = @{ $params->{'discount_classnum'} }; # } else { # die 'unhandled discount_classnum ref '. $params->{'discount_classnum'}; # } # # # } elsif ( $params->{'discount_classnum'} =~ /^(\d*)$/ && $1 ne '0' ) { # @classnum = ( $1 ); # } # # if ( @classnum ) { if ( $cgi->param('discount_classnum') =~ /^(\d*)$/ && $1 ne '0' ) { my @classnum = ( $1 ); $join .= ' LEFT JOIN discount USING (discountnum) '; my @c_where = (); my @nums = grep $_, @classnum; push @c_where, 'discount.classnum IN ('. join(',',@nums). ')' if @nums; my $null = scalar( grep { $_ eq '' } @classnum ); push @c_where, 'discount.classnum IS NULL' if $null; if ( scalar(@c_where) == 1 ) { push @where, @c_where; } elsif ( @c_where ) { push @where, ' ( '. join(' OR ', @c_where). ' ) '; } } } # #(package) classnum # # not specified: all classes # # 0: empty class # # N: classnum # my $use_override = $cgi->param('use_override'); # if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { # my $comparison = ''; # if ( $1 == 0 ) { # $comparison = "IS NULL"; # } else { # $comparison = "= $1"; # } # # if ( $use_override ) { # push @where, "( # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR # override.classnum $comparison AND pkgpart_override IS NOT NULL # )"; # } else { # push @where, "part_pkg.classnum $comparison"; # } # } my $count_query; if ( $cgi->param('include_waived_setup') ) { $count_query = " SELECT COUNT(*), SUM( COALESCE( cust_bill_pkg_discount.amount, CAST(( SELECT optionvalue FROM part_pkg_option WHERE part_pkg_option.pkgpart = cust_pkg.pkgpart AND optionname = 'setup_fee' ) AS NUMERIC ) ) ) "; } else { $count_query = "SELECT COUNT(*), SUM(cust_bill_pkg_discount.amount)"; } my $where = ' WHERE '. join(' AND ', @where); $count_query .= " FROM cust_bill_pkg $join $where"; my $query = { 'table' => 'cust_bill_pkg', 'addl_from' => $join, 'hashref' => {}, 'select' => join(', ', @select ), 'extra_sql' => $where, 'order_by' => 'ORDER BY _date, billpkgdiscountnum', }; my $money_char = $conf->config('money_char') || '$';