path: root/httemplate/search/cust_bill_pkg_referral.html

<& elements/search.html,
  'title'       => emt('Sales with advertising source'),
  'name'        => emt('line items'),
  'query'       => $query,
  'count_query' => $count_query,
  'count_addl'  => [ 
                     ($setup ? $money_char. '%.2f setup' : ()),
                     ($recur ? $money_char. '%.2f recurring' : ()),
                     ($usage ? $money_char. '%.2f usage' : ()),
                   ],
  'header'      => [
    emt('Description'),
    ($setup ? emt('Setup') : ()),
    ($recur ? emt('Recurring') : ()),
    ($usage ? emt('Usage') : ()),
    emt('Invoice'),
    emt('Invoice date'),
    emt('Paid'),
    emt('Payment date'),
    emt('Pkg. status'),
    emt('Pkg. class'),
    '', #report class
    emt('Cust#'),
    emt('Customer'),
    emt('Ad source'),
    emt('Agent'),
  ],
  'fields'      => [
    'pkg',
    ($setup ? money_sub('setup') : ()),
    ($recur ? money_sub('recur_no_usage') : ()),
    ($usage ? money_sub('recur_usage') : ()),
    'invnum',
    date_sub('_date'),
    money_sub('paid'),
    date_sub('last_pay'),
    sub {
      my $cust_pkg = shift->cust_pkg;
      $cust_pkg ? ucfirst($cust_pkg->status) : '';
    },
    'classname',
    sub { # report_option
      my $cust_bill_pkg = shift;
      my $pkgpart = $cust_bill_pkg->pkgpart_override;
      unless ( $pkgpart ) {
        my $cust_pkg = $cust_bill_pkg->cust_pkg or return '';
        $pkgpart = $cust_pkg->pkgpart;
      }
      if ( !exists($report_classes{$pkgpart}) ) {
        my $part_pkg = FS::part_pkg->by_key($pkgpart);
        my %opts = $part_pkg->options;
        $report_classes{$pkgpart} = [
          map { /^report_option_(\d+)/ ? 
                $report_option_name{$1} :
                () }
          keys %opts
        ];
      }
      join( '<BR>', @{ $report_classes{$pkgpart} });
    },
    'custnum',
    'name',
    'referral', # from query
    'agent',
  ],
  'sort_fields' => [
    '',
    ($setup ? 'setup' : ()),
    ($recur ? 'recur_no_usage' : ()),
    ($usage ? 'recur_usage' : ()),
    'invnum',
    '_date',
    'paid',
    'last_pay',
    '', #package status
    'classname',
    '', #report_option
    'custnum',
    '',
    'referral',
    'agent',
  ],
  'links'       => [
    '', #package/item desc
    ('') x $x, #setup/recur/usage
    $ilink, #invnum
    $ilink, #invoice date
    '', #paid amt
    '', #payment date
    '', #pkg status
    '', #classnum
    '', #report class
    $clink, #custnum
    $clink, #customer name
    '', #referral
    '', #agent
  ],
  #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
  'align' => 'l' . ('r' x $x) . 'rcrccccrlll',
  'color' => [ ('') x (5 + $x),
                sub {
                  my $cust_pkg = shift->cust_pkg;
                  $cust_pkg ? $cust_pkg->statuscolor : '';
                },
               ('') x 6,
             ],
  'style' => [
               ('') x (5 + $x),
               'b',
               ('') x 6
             ],
&>
<%init>

die "access denied"
  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');

my $conf = new FS::Conf;

my $setup = $cgi->param('setup') ? 1 : 0;
my $recur = $cgi->param('recur') ? 1 : 0;
my $usage = $cgi->param('usage') ? 1 : 0;

my $x = $setup + $recur + $usage;

my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
my ($join_cust, $join_pkg ) = ('', '');

#here is the agent virtualization
my $agentnums_sql =
  $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );

my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);

my @where = ( $agentnums_sql,
              # exclude taxes
              '(cust_bill_pkg.pkgnum != 0 OR '.
                'cust_bill_pkg.feepart IS NOT NULL)',
              "cust_bill._date >= $beginning",
              "cust_bill._date <= $ending",
            );

my @status_where;
foreach my $status ($cgi->param('status')) {
  if ( $status =~ /^([- a-z]+)$/ ) { #"one-time charge"
    push @status_where, "'$status'";
  }
}
if ( @status_where ) {
  push @where, '('. FS::cust_pkg->status_sql.
    ') IN (' . join(',', @status_where) .')';
}

my @refnum;
foreach my $refnum ($cgi->param('refnum')) {
  if ( $refnum =~ /^\d+$/ ) {
    push @refnum, $refnum;
  }
}
if ( @refnum ) {
  push @where, 'cust_main.refnum IN ('.join(',', @refnum).')';
}

# 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)
if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
  my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
  push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
                   join(',', map { $_ || '0' } @classnums ).
               ' )'
    if @classnums;
}

if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
  push @where, "cust_main.agentnum = $1";
}

#classnum
# not specified: all classes
# 0: empty class
# N: classnum
my $use_override = 1; #$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) OR
      (part_fee.classnum $comparison AND feepart IS NOT NULL)
    )";
  } else {
    push @where, 
    "(part_pkg.classnum $comparison) OR (part_fee.classnum $comparison)";
  }
}

# report option
my @report_option = grep /^\d+$/, ( $cgi->param('report_option') );
if ( @report_option ) {
  @report_option = map { "'report_option_$_'" } @report_option;
  push @where, "EXISTS( 
    SELECT 1 FROM part_pkg_option WHERE optionname IN (".
    join(',', @report_option).") AND (
      part_pkg_option.pkgpart = cust_pkg.pkgpart AND pkgpart_override IS NULL
      OR part_pkg_option.pkgpart = pkgpart_override
    )
  )";
}

my $setup_sql =
  FS::cust_bill_pkg->charged_sql('', '', setuprecur => 'setup');
my $recur_sql =
  FS::cust_bill_pkg->charged_sql('', '', setuprecur => 'recur', no_usage => 1);
my $usage_sql = FS::cust_bill_pkg->usage_sql;

# exclude zero-amount items
my @orwhere;
push @orwhere, "(cust_bill_pkg.setup > 0)" if $setup;
push @orwhere, "($recur_sql > 0)"          if $recur;
push @orwhere, "($usage_sql > 0)"          if $usage;
push @where, '('.join(' OR ', @orwhere).')' if @orwhere;

$join_cust =  '        JOIN cust_bill     USING ( invnum )
                  LEFT JOIN cust_main     USING ( custnum )
                  LEFT JOIN part_referral USING ( refnum )
                  LEFT JOIN agent ON cust_main.agentnum = agent.agentnum
              ';

$join_pkg .=  ' LEFT JOIN cust_pkg USING ( pkgnum )
                LEFT JOIN part_pkg USING ( pkgpart )
                LEFT JOIN part_pkg AS override
                  ON pkgpart_override = override.pkgpart 
                LEFT JOIN part_fee USING ( feepart )
                LEFT JOIN pkg_class ON '; #...

if ( $use_override ) {
  # join to whichever pkgpart is appropriate
  $join_pkg .= '
      ( pkgpart_override IS NULL     AND part_pkg.classnum = pkg_class.classnum )
   OR ( pkgpart_override IS NOT NULL AND override.classnum = pkg_class.classnum )
   OR ( feepart IS NOT NULL AND part_fee.classnum = pkg_class.classnum )';
} else {
  $join_pkg .= '
      ( part_pkg.classnum = pkg_class.classnum )
   OR ( part_fee.classnum = pkg_class.classnum )';
}

my $where = ' WHERE '. join(' AND ', @where);

# setup and recurring only
my $count_query = "SELECT 
  COUNT(billpkgnum)".
  ($setup ? ", SUM($setup_sql)" : '').
  ($recur ? ", SUM($recur_sql)" : '').
  ($usage ? ", SUM($usage_sql)" : '').
  " FROM cust_bill_pkg
  $join_cust
  $join_pkg
  $where
  ";

my $paid_sql = FS::cust_bill_pkg->paid_sql('', '');
my $last_pay_sql = "SELECT MAX(_date)
  FROM cust_bill_pay JOIN cust_bill_pay_pkg USING (billpaynum)
  WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum";

push @select, 'COALESCE(part_pkg.pkg, part_fee.itemdesc) AS pkg',
              'part_pkg.freq',
              'cust_main.custnum',
              'cust_main.first',
              'cust_main.last',
              'cust_main.company',
              'part_referral.referral',
              "($paid_sql) AS paid",
              "($last_pay_sql) AS last_pay",
              "($recur_sql) AS recur_no_usage",
              "($usage_sql) AS recur_usage",
              'pkg_class.classname',
              'agent.agent',
              ;

my $query = {
  'table'     => 'cust_bill_pkg',
  'addl_from' => "$join_cust $join_pkg",
  'hashref'   => {},
  'select'    => join(",\n", @select ),
  'extra_sql' => $where,
  'order_by'  => 'ORDER BY cust_bill._date, billpkgnum',
};

my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];

my $conf = new FS::Conf;
my $money_char = $conf->config('money_char') || '$';

my %report_classes; #cache
my %report_option_name = 
  map { $_->num => $_->name } qsearch('part_pkg_report_option', {});

# should this be in Mason.pm or something?
sub money_sub {
  $conf ||= new FS::Conf;
  $money_char ||= $conf->config('money_char') || '$';
  my $field = shift;
  sub {
    $money_char . sprintf('%.2f', $_[0]->get($field));
  };
}

sub date_sub {
  my $field = shift;
  sub {
    my $value = $_[0]->get($field);
    $value ? time2str('%b %d %Y', $value) : '';
  };
}

</%init>