<& elements/search.html,
                  'title'       => $title,
                  'name'        => 'packages',
                  'query'       => $sql_query,
                  'count_query' => $count_query,
                  'header'      => [ emt('#'),
                                     emt('Quantity'),
                                     emt('Package'),
                                     emt('Class'),
                                     emt('Sales Person'),
                                     emt('Ordered by'),
                                     emt('Setup Fee'),
                                     emt('Base Recur'),
                                     emt('Freq.'),
                                     emt('Setup'),
                                     emt('Last bill'),
                                     emt('Next bill'),
                                     emt('Susp.'),
                                     emt('Changed'),
                                     emt('Cancel'),
                                     @reason_header,
                                     FS::UI::Web::cust_header(
                                       $cgi->param('cust_fields')
                                     ),
                                     #emt('Services'), # even harder
                                   ],
                  'fields'      => [
                    'pkgnum',
                    'quantity',
                    'pkg',
                    'classname',
                    'salesperson',
                    'otaker',
                    sub { sprintf( $money_char.'%.2f',
                                   shift->part_pkg->option('setup_fee'),
                                 );
                        },
                    sub { my $c = shift;
                          sprintf( $money_char.'%.2f',
                                   $c->part_pkg->base_recur($c)
                                 );
                        },
                    sub { FS::part_pkg::freq_pretty(shift); },

                    ( map { time_or_blank($_) }
                      qw( setup last_bill bill susp change_date cancel ) ),

                    @reason_fields,
                    \&FS::UI::Web::cust_fields,
                  ],
                  'sort_fields' => [
                    'cust_pkg.pkgnum',
                    ('') x 5, # can use as-is
                    ('') x 3, # can't use at all
                    # use the plain SQL column names
                    qw( setup last_bill bill susp change_date cancel ),
                    @reason_blank,
                    # cust_fields can take care of themselves
                  ],
                  'color' => [
                    ('') x 15,
                    @reason_blank,
                    FS::UI::Web::cust_colors(),
                  ],
                  'style' => [ ('') x 15,
                               @reason_blank,
                               FS::UI::Web::cust_styles() ],
                  'size'  => [ '', '', '', '', '-1' ],
                  'align' => 'rrlcccrrlrrrrrr'.$reason_align. FS::UI::Web::cust_aligns(). 'r',
                  'links' => [
                    $link,
                    $link,
                    $link,
                    ('') x 12,
                    @reason_blank,
                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
                          FS::UI::Web::cust_header(
                                                    $cgi->param('cust_fields')
                                                  )
                    ),
                  ],
&>
<%once>
my %title = (
  'active' => 'Active packages as of ',
  'setup'  => 'Packages started between ',
  'cancel' => 'Packages canceled between ',
  'susp'   => 'Packages suspended between ',
  'unsusp' => 'Packages unsuspended between ',
);
</%once>
<%init>

my $curuser = $FS::CurrentUser::CurrentUser;

die "access denied"
  unless $curuser->access_right('List packages');

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

my %search_hash = ();

# pass a very limited set of parameters through
#scalars
for (qw( agentnum zip )) 
{
  $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
}

#arrays / comma-separated lists
for my $param (qw( pkgpart classnum refnum towernum )) {
  my @values = map { split(',') } $cgi->param($param);
  $search_hash{$param} = \@values if scalar(@values);
}

###
# do not pass dates to FS::cust_pkg->search; use the special churn_fromwhere
# logic.
###

my $pkg_query = FS::cust_pkg->search(\%search_hash);
#warn Dumper $pkg_query;

my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
my $status = $cgi->param('status');

my $title = emt($title{$status}) .
            time2str('%b %o %Y', $beginning);
if ($status ne 'active') {
  $title .= emt(' to ') . time2str('%b %o %Y', $ending);
}

my ($from, @where) = FS::h_cust_pkg->churn_fromwhere_sql($status, $beginning, $ending);

push @where, "freq != '0'";

# split off the primary table name
$from =~ s/^(\w+)(.*)$/$2/s;
my $table = $1;

# merge with $pkg_query
$from .= ' ' . $pkg_query->{addl_from};

my $extra_sql;
if ($pkg_query->{extra_sql}) {
  $extra_sql = $pkg_query->{extra_sql} . ' AND ';
} else {
  $extra_sql = 'WHERE ';
}
$extra_sql .= join(' AND ', @where);

my $sql_query = {
  'select'    => $pkg_query->{select},
  'table'     => $table,
  'addl_from' => $from,
  'extra_sql' => $extra_sql,
};
warn (Dumper $sql_query) if $cgi->param('debug');

my $count_query = "SELECT COUNT(*) FROM $table $from $extra_sql";

my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/
             ? ''
             : ';show=packages';

my $link = sub {
  my $self = shift;
  my $frag = 'cust_pkg'. $self->pkgnum; #hack for IE ignoring real #fragment
  [ "${p}view/cust_main.cgi?custnum=".$self->custnum.
                           "$show;fragment=$frag#cust_pkg",
    'pkgnum'
  ];
};

my $clink = sub {
  my $cust_pkg = shift;
  $cust_pkg->cust_main_custnum
    ? [ "${p}view/cust_main.cgi?", 'custnum' ] 
    : '';
};

sub time_or_blank {
   my $column = shift;
   return sub {
     my $record = shift;
     my $value = $record->get($column); #mmm closures
     $value ? time2str('%b %d %Y', $value ) : '';
   };
}

my (@reason_header,@reason_fields,@reason_blank);
my $reason_align = '';
if ($status eq 'cancel') {
  push @reason_header, emt('Cancel Reason');
  push @reason_fields, sub {
    my $c = shift;
    my $cust_pkg_reason = $c->last_cust_pkg_reason('cancel');
    $cust_pkg_reason ? $cust_pkg_reason->reason->reason : '';
  };
  push @reason_blank, '';
  $reason_align = 'l';
}
</%init>