path: root/httemplate/search/cust_bill_pkg_discount.html

<%doc>

Display the Discount Detail Report:

Parameters:

 - discount_classnum
 - usernum
 - agentnum
 - beginning
 - ending
 - include_waived_setup

</%doc>
<& 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',{
                discountnum => $_[0]->discountnum
            }) || return 'Bad discountnum '.$_[0]->pkgdiscountnum;
            return encode_entities $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 encode_entities $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') ) {
  # Filter a hidden fee attached to a package with a waived setup fee from
  # causing the waived-fee for that package to be double-counted
  push @where, 'cust_bill_pkg.pkgpart_override IS NULL';
} else {
  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') || '$';

</%init>