}
###
+ # parse (customer) refnum (advertising source)
+ ###
+
+ if ( exists($params->{'refnum'}) ) {
+ my @refnum;
+ if (ref $params->{'refnum'}) {
+ @refnum = @{ $params->{'refnum'} };
+ } else {
+ @refnum = ( $params->{'refnum'} );
+ }
+ my $in = join(',', grep /^\d+$/, @refnum);
+ push @where, "cust_main.refnum IN($in)" if length $in;
+ }
+
+ ###
# parse package report options
###
if (@report_option) {
# this will result in the empty set for the dangling comma case as it should
push @where,
- map{ "0 < ( SELECT count(*) FROM part_pkg_option
- WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
- AND optionname = 'report_option_$_'
- AND optionvalue = '1' )"
+ map{ "EXISTS ( SELECT 1 FROM part_pkg_option
+ WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+ AND optionname = 'report_option_$_'
+ AND optionvalue = '1' )"
} @report_option;
}
if (@report_option_any) {
# this will result in the empty set for the dangling comma case as it should
push @where, ' ( '. join(' OR ',
- map{ "0 < ( SELECT count(*) FROM part_pkg_option
- WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
- AND optionname = 'report_option_$_'
- AND optionvalue = '1' )"
+ map{ "EXISTS ( SELECT 1 FROM part_pkg_option
+ WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+ AND optionname = 'report_option_$_'
+ AND optionvalue = '1' )"
} @report_option_any
). ' ) ';
}
my $op = $params->{location_cust} ? '=' : '!=';
push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
}
- if ( $params->{location_census} xor $params->{location_nocensus} ) {
- my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
- push @where, "cust_location.censustract $op";
+ if ( $params->{location_census} ) {
+ push @where, "cust_location.censustract IS NOT NULL",
+ "cust_location.censusyear = '2020' ";
+ } elsif ( $params->{location_nocensus} ) {
+ push @where, "( cust_location.censustract IS NULL ".
+ " OR cust_location.censusyear != '2020' )";
}
if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
'' => {},
);
- if( exists($params->{'active'} ) ) {
+ if ( exists($params->{'active'} ) ) {
+
# This overrides all the other date-related fields, and includes packages
# that were active at some time during the interval. It excludes:
# - packages that were set up after the end of the interval
"(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
"(cust_pkg.susp IS NULL OR cust_pkg.susp >= $beginning )",
"NOT (".FS::cust_pkg->onetime_sql . ")";
- }
- else {
+
+ } else {
+
my $exclude_change_from = 0;
my $exclude_change_to = 0;
foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
- next unless exists($params->{$field});
+ if ( $params->{$field.'_null'} ) {
- my($beginning, $ending) = @{$params->{$field}};
+ push @where, "cust_pkg.$field IS NULL";
+ # this should surely be obsoleted by now: OR cust_pkg.$field == 0 )
- next if $beginning == 0 && $ending == 4294967295;
+ } else {
+
+ next unless exists($params->{$field});
+
+ my($beginning, $ending) = @{$params->{$field}};
+
+ next if $beginning == 0 && $ending == 4294967295;
+
+ push @where,
+ "cust_pkg.$field IS NOT NULL",
+ "cust_pkg.$field >= $beginning",
+ "cust_pkg.$field <= $ending";
+
+ $orderby ||= "ORDER BY cust_pkg.$field";
+
+ if ( $field eq 'setup' ) {
+ $exclude_change_from = 1;
+ } elsif ( $field eq 'cancel' ) {
+ $exclude_change_to = 1;
+ } elsif ( $field eq 'change_date' ) {
+ # if we are given setup and change_date ranges, and the setup date
+ # falls in _both_ ranges, then include the package whether it was
+ # a change or not
+ $exclude_change_from = 0;
+ }
- push @where,
- "cust_pkg.$field IS NOT NULL",
- "cust_pkg.$field >= $beginning",
- "cust_pkg.$field <= $ending";
-
- $orderby ||= "ORDER BY cust_pkg.$field";
-
- if ( $field eq 'setup' ) {
- $exclude_change_from = 1;
- } elsif ( $field eq 'cancel' ) {
- $exclude_change_to = 1;
- } elsif ( $field eq 'change_date' ) {
- # if we are given setup and change_date ranges, and the setup date
- # falls in _both_ ranges, then include the package whether it was
- # a change or not
- $exclude_change_from = 0;
}
+
}
if ($exclude_change_from) {
- push @where, "change_pkgnum IS NULL";
+ push @where, "cust_pkg.change_pkgnum IS NULL";
}
if ($exclude_change_to) {
# a join might be more efficient here
WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
)";
}
+
}
$orderby ||= 'ORDER BY bill';
'agentnum' => $agentnum,
'detail' => 1
);
- my $row = $report->[$rownum]
+ my $pkgnums = $report->{detail}->[$rownum]
or die "row $rownum is past the end of the report";
- my $pkgnums = $row->[-1] || '0';
# '0' so that if there are no pkgnums (empty string) it will create
# a valid query that returns nothing
warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug
push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
}
+ push @where, "cust_pkg_reason.reasonnum = '".$params->{reasonnum}."'" if $params->{reasonnum};
+
my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
my $addl_from = 'LEFT JOIN part_pkg USING ( pkgpart ) '.
'LEFT JOIN cust_location USING ( locationnum ) '.
FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
+ if ($params->{reasonnum}) {
+ $addl_from .= 'LEFT JOIN cust_pkg_reason ON (cust_pkg_reason.pkgnum = cust_pkg.pkgnum) ';
+ }
+
my $select;
my $count_query;
if ( $params->{'select_zip5'} ) {
projects / freeside.git / blobdiff