package FS::cust_pkg;
-use base qw( FS::otaker_Mixin FS::cust_main_Mixin FS::Sales_Mixin
+use base qw( FS::cust_pkg::Search
+ FS::otaker_Mixin FS::cust_main_Mixin FS::Sales_Mixin
FS::contact_Mixin FS::location_Mixin
- FS::m2m_Common FS::option_Common );
+ FS::m2m_Common FS::option_Common
+ );
use strict;
use Carp qw(cluck);
use FS::cust_pkg_usageprice;
use FS::cust_pkg_discount;
use FS::discount;
-use FS::UI::Web;
use FS::sales;
# for modify_charge
use FS::cust_credit;
END"
}
-=item search HASHREF
-
-(Class method)
-
-Returns a qsearch hash expression to search for parameters specified in HASHREF.
-Valid parameters are
-
-=over 4
-
-=item agentnum
-
-=item magic
-
-active, inactive, suspended, cancel (or cancelled)
-
-=item status
-
-active, inactive, suspended, one-time charge, inactive, cancel (or cancelled)
-
-=item custom
-
- boolean selects custom packages
-
-=item classnum
-
-=item pkgpart
-
-pkgpart or arrayref or hashref of pkgparts
-
-=item setup
-
-arrayref of beginning and ending epoch date
-
-=item last_bill
-
-arrayref of beginning and ending epoch date
-
-=item bill
-
-arrayref of beginning and ending epoch date
-
-=item adjourn
-
-arrayref of beginning and ending epoch date
-
-=item susp
-
-arrayref of beginning and ending epoch date
-
-=item expire
-
-arrayref of beginning and ending epoch date
-
-=item cancel
-
-arrayref of beginning and ending epoch date
-
-=item query
-
-pkgnum or APKG_pkgnum
-
-=item cust_fields
-
-a value suited to passing to FS::UI::Web::cust_header
-
-=item CurrentUser
-
-specifies the user for agent virtualization
-
-=item fcc_line
-
-boolean; if true, returns only packages with more than 0 FCC phone lines.
-
-=item state, country
-
-Limit to packages with a service location in the specified state and country.
-For FCC 477 reporting, mostly.
-
-=item location_cust
-
-Limit to packages whose service locations are the same as the customer's
-default service location.
-
-=item location_nocust
-
-Limit to packages whose service locations are not the customer's default
-service location.
-
-=item location_census
-
-Limit to packages whose service locations have census tracts.
-
-=item location_nocensus
-
-Limit to packages whose service locations do not have a census tract.
-
-=item location_geocode
-
-Limit to packages whose locations have geocodes.
-
-=item location_geocode
-
-Limit to packages whose locations do not have geocodes.
-
-=back
-
-=cut
-
-sub search {
- my ($class, $params) = @_;
- my @where = ();
-
- ##
- # parse agent
- ##
-
- if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
- push @where,
- "cust_main.agentnum = $1";
- }
-
- ##
- # parse cust_status
- ##
-
- if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
- push @where, FS::cust_main->cust_status_sql . " = '$1' ";
- }
-
- ##
- # parse customer sales person
- ##
-
- if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
- push @where, ($1 > 0) ? "cust_main.salesnum = $1"
- : 'cust_main.salesnum IS NULL';
- }
-
-
- ##
- # parse sales person
- ##
-
- if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
- push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
- : 'cust_pkg.salesnum IS NULL';
- }
-
- ##
- # parse custnum
- ##
-
- if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
- push @where,
- "cust_pkg.custnum = $1";
- }
-
- ##
- # custbatch
- ##
-
- if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
- push @where,
- "cust_pkg.pkgbatch = '$1'";
- }
-
- ##
- # parse status
- ##
-
- if ( $params->{'magic'} eq 'active'
- || $params->{'status'} eq 'active' ) {
-
- push @where, FS::cust_pkg->active_sql();
-
- } elsif ( $params->{'magic'} =~ /^not[ _]yet[ _]billed$/
- || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
-
- push @where, FS::cust_pkg->not_yet_billed_sql();
-
- } elsif ( $params->{'magic'} =~ /^(one-time charge|inactive)/
- || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
-
- push @where, FS::cust_pkg->inactive_sql();
-
- } elsif ( $params->{'magic'} eq 'suspended'
- || $params->{'status'} eq 'suspended' ) {
-
- push @where, FS::cust_pkg->suspended_sql();
-
- } elsif ( $params->{'magic'} =~ /^cancell?ed$/
- || $params->{'status'} =~ /^cancell?ed$/ ) {
-
- push @where, FS::cust_pkg->cancelled_sql();
-
- }
-
- ###
- # parse package class
- ###
-
- if ( exists($params->{'classnum'}) ) {
-
- my @classnum = ();
- if ( ref($params->{'classnum'}) ) {
-
- if ( ref($params->{'classnum'}) eq 'HASH' ) {
- @classnum = grep $params->{'classnum'}{$_}, keys %{ $params->{'classnum'} };
- } elsif ( ref($params->{'classnum'}) eq 'ARRAY' ) {
- @classnum = @{ $params->{'classnum'} };
- } else {
- die 'unhandled classnum ref '. $params->{'classnum'};
- }
-
-
- } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
- @classnum = ( $1 );
- }
-
- if ( @classnum ) {
-
- my @c_where = ();
- my @nums = grep $_, @classnum;
- push @c_where, 'part_pkg.classnum IN ('. join(',',@nums). ')' if @nums;
- my $null = scalar( grep { $_ eq '' } @classnum );
- push @c_where, 'part_pkg.classnum IS NULL' if $null;
-
- if ( scalar(@c_where) == 1 ) {
- push @where, @c_where;
- } elsif ( @c_where ) {
- push @where, ' ( '. join(' OR ', @c_where). ' ) ';
- }
-
- }
-
-
- }
-
- ###
- # parse package report options
- ###
-
- my @report_option = ();
- if ( exists($params->{'report_option'}) ) {
- if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
- @report_option = @{ $params->{'report_option'} };
- } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
- @report_option = split(',', $1);
- }
-
- }
-
- 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' )"
- } @report_option;
- }
-
- foreach my $any ( grep /^report_option_any/, keys %$params ) {
-
- my @report_option_any = ();
- if ( ref($params->{$any}) eq 'ARRAY' ) {
- @report_option_any = @{ $params->{$any} };
- } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
- @report_option_any = split(',', $1);
- }
-
- 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' )"
- } @report_option_any
- ). ' ) ';
- }
-
- }
-
- ###
- # parse custom
- ###
-
- push @where, "part_pkg.custom = 'Y'" if $params->{custom};
-
- ###
- # parse fcc_line
- ###
-
- push @where, "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
- if $params->{fcc_line};
-
- ###
- # parse censustract
- ###
-
- if ( exists($params->{'censustract'}) ) {
- $params->{'censustract'} =~ /^([.\d]*)$/;
- my $censustract = "cust_location.censustract = '$1'";
- $censustract .= ' OR cust_location.censustract is NULL' unless $1;
- push @where, "( $censustract )";
- }
-
- ###
- # parse censustract2
- ###
- if ( exists($params->{'censustract2'})
- && $params->{'censustract2'} =~ /^(\d*)$/
- )
- {
- if ($1) {
- push @where, "cust_location.censustract LIKE '$1%'";
- } else {
- push @where,
- "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
- }
- }
-
- ###
- # parse country/state
- ###
- for (qw(state country)) { # parsing rules are the same for these
- if ( exists($params->{$_})
- && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
- {
- # XXX post-2.3 only--before that, state/country may be in cust_main
- push @where, "cust_location.$_ = '$1'";
- }
- }
-
- ###
- # location_* flags
- ###
- if ( $params->{location_cust} xor $params->{location_nocust} ) {
- 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_geocode} xor $params->{location_nogeocode} ) {
- my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
- push @where, "cust_location.geocode $op";
- }
-
- ###
- # parse part_pkg
- ###
-
- if ( ref($params->{'pkgpart'}) ) {
-
- my @pkgpart = ();
- if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
- @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
- } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
- @pkgpart = @{ $params->{'pkgpart'} };
- } else {
- die 'unhandled pkgpart ref '. $params->{'pkgpart'};
- }
-
- @pkgpart = grep /^(\d+)$/, @pkgpart;
-
- push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
-
- } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
- push @where, "pkgpart = $1";
- }
-
- ###
- # parse dates
- ###
-
- my $orderby = '';
-
- #false laziness w/report_cust_pkg.html
- my %disable = (
- 'all' => {},
- 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
- 'active' => { 'susp'=>1, 'cancel'=>1 },
- 'suspended' => { 'cancel' => 1 },
- 'cancelled' => {},
- '' => {},
- );
-
- if( exists($params->{'active'} ) ) {
- # This overrides all the other date-related fields
- my($beginning, $ending) = @{$params->{'active'}};
- push @where,
- "cust_pkg.setup IS NOT NULL",
- "cust_pkg.setup <= $ending",
- "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
- "NOT (".FS::cust_pkg->onetime_sql . ")";
- }
- else {
- foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
-
- 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";
-
- }
- }
-
- $orderby ||= 'ORDER BY bill';
-
- ###
- # parse magic, legacy, etc.
- ###
-
- if ( $params->{'magic'} &&
- $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
- ) {
-
- $orderby = 'ORDER BY pkgnum';
-
- if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
- push @where, "pkgpart = $1";
- }
-
- } elsif ( $params->{'query'} eq 'pkgnum' ) {
-
- $orderby = 'ORDER BY pkgnum';
-
- } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
-
- $orderby = 'ORDER BY pkgnum';
-
- push @where, '0 < (
- SELECT count(*) FROM pkg_svc
- WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
- AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
- WHERE cust_svc.pkgnum = cust_pkg.pkgnum
- AND cust_svc.svcpart = pkg_svc.svcpart
- )
- )';
-
- }
-
- ##
- # setup queries, links, subs, etc. for the search
- ##
-
- # here is the agent virtualization
- if ($params->{CurrentUser}) {
- my $access_user =
- qsearchs('access_user', { username => $params->{CurrentUser} });
-
- if ($access_user) {
- push @where, $access_user->agentnums_sql('table'=>'cust_main');
- } else {
- push @where, "1=0";
- }
- } else {
- push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
- }
-
- my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
-
- my $addl_from = 'LEFT JOIN part_pkg USING ( pkgpart ) '.
- 'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
- 'LEFT JOIN cust_location USING ( locationnum ) '.
- FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
-
- my $select;
- my $count_query;
- if ( $params->{'select_zip5'} ) {
- my $zip = 'cust_location.zip';
-
- $select = "DISTINCT substr($zip,1,5) as zip";
- $orderby = "ORDER BY substr($zip,1,5)";
- $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
- } else {
- $select = join(', ',
- 'cust_pkg.*',
- ( map "part_pkg.$_", qw( pkg freq ) ),
- 'pkg_class.classname',
- 'cust_main.custnum AS cust_main_custnum',
- FS::UI::Web::cust_sql_fields(
- $params->{'cust_fields'}
- ),
- );
- $count_query = 'SELECT COUNT(*)';
- }
-
- $count_query .= " FROM cust_pkg $addl_from $extra_sql";
-
- my $sql_query = {
- 'table' => 'cust_pkg',
- 'hashref' => {},
- 'select' => $select,
- 'extra_sql' => $extra_sql,
- 'order_by' => $orderby,
- 'addl_from' => $addl_from,
- 'count_query' => $count_query,
- };
-
-}
-
=item fcc_477_count
Returns a list of two package counts. The first is a count of packages
--- /dev/null
+package FS::cust_pkg::Search;
+
+use strict;
+use FS::CurrentUser;
+use FS::UI::Web;
+use FS::cust_main;
+use FS::cust_pkg;
+
+=item search HASHREF
+
+(Class method)
+
+Returns a qsearch hash expression to search for parameters specified in HASHREF.
+Valid parameters are
+
+=over 4
+
+=item agentnum
+
+=item magic
+
+active, inactive, suspended, cancel (or cancelled)
+
+=item status
+
+active, inactive, suspended, one-time charge, inactive, cancel (or cancelled)
+
+=item custom
+
+ boolean selects custom packages
+
+=item classnum
+
+=item pkgpart
+
+pkgpart or arrayref or hashref of pkgparts
+
+=item setup
+
+arrayref of beginning and ending epoch date
+
+=item last_bill
+
+arrayref of beginning and ending epoch date
+
+=item bill
+
+arrayref of beginning and ending epoch date
+
+=item adjourn
+
+arrayref of beginning and ending epoch date
+
+=item susp
+
+arrayref of beginning and ending epoch date
+
+=item expire
+
+arrayref of beginning and ending epoch date
+
+=item cancel
+
+arrayref of beginning and ending epoch date
+
+=item query
+
+pkgnum or APKG_pkgnum
+
+=item cust_fields
+
+a value suited to passing to FS::UI::Web::cust_header
+
+=item CurrentUser
+
+specifies the user for agent virtualization
+
+=item fcc_line
+
+boolean; if true, returns only packages with more than 0 FCC phone lines.
+
+=item state, country
+
+Limit to packages with a service location in the specified state and country.
+For FCC 477 reporting, mostly.
+
+=item location_cust
+
+Limit to packages whose service locations are the same as the customer's
+default service location.
+
+=item location_nocust
+
+Limit to packages whose service locations are not the customer's default
+service location.
+
+=item location_census
+
+Limit to packages whose service locations have census tracts.
+
+=item location_nocensus
+
+Limit to packages whose service locations do not have a census tract.
+
+=item location_geocode
+
+Limit to packages whose locations have geocodes.
+
+=item location_geocode
+
+Limit to packages whose locations do not have geocodes.
+
+=back
+
+=cut
+
+sub search {
+ my ($class, $params) = @_;
+ my @where = ();
+
+ ##
+ # parse agent
+ ##
+
+ if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
+ push @where,
+ "cust_main.agentnum = $1";
+ }
+
+ ##
+ # parse cust_status
+ ##
+
+ if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
+ push @where, FS::cust_main->cust_status_sql . " = '$1' ";
+ }
+
+ ##
+ # parse customer sales person
+ ##
+
+ if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
+ push @where, ($1 > 0) ? "cust_main.salesnum = $1"
+ : 'cust_main.salesnum IS NULL';
+ }
+
+
+ ##
+ # parse sales person
+ ##
+
+ if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
+ push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
+ : 'cust_pkg.salesnum IS NULL';
+ }
+
+ ##
+ # parse custnum
+ ##
+
+ if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
+ push @where,
+ "cust_pkg.custnum = $1";
+ }
+
+ ##
+ # custbatch
+ ##
+
+ if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
+ push @where,
+ "cust_pkg.pkgbatch = '$1'";
+ }
+
+ ##
+ # parse status
+ ##
+
+ if ( $params->{'magic'} eq 'active'
+ || $params->{'status'} eq 'active' ) {
+
+ push @where, FS::cust_pkg->active_sql();
+
+ } elsif ( $params->{'magic'} =~ /^not[ _]yet[ _]billed$/
+ || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
+
+ push @where, FS::cust_pkg->not_yet_billed_sql();
+
+ } elsif ( $params->{'magic'} =~ /^(one-time charge|inactive)/
+ || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
+
+ push @where, FS::cust_pkg->inactive_sql();
+
+ } elsif ( $params->{'magic'} eq 'suspended'
+ || $params->{'status'} eq 'suspended' ) {
+
+ push @where, FS::cust_pkg->suspended_sql();
+
+ } elsif ( $params->{'magic'} =~ /^cancell?ed$/
+ || $params->{'status'} =~ /^cancell?ed$/ ) {
+
+ push @where, FS::cust_pkg->cancelled_sql();
+
+ }
+
+ ###
+ # parse package class
+ ###
+
+ if ( exists($params->{'classnum'}) ) {
+
+ my @classnum = ();
+ if ( ref($params->{'classnum'}) ) {
+
+ if ( ref($params->{'classnum'}) eq 'HASH' ) {
+ @classnum = grep $params->{'classnum'}{$_}, keys %{ $params->{'classnum'} };
+ } elsif ( ref($params->{'classnum'}) eq 'ARRAY' ) {
+ @classnum = @{ $params->{'classnum'} };
+ } else {
+ die 'unhandled classnum ref '. $params->{'classnum'};
+ }
+
+
+ } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
+ @classnum = ( $1 );
+ }
+
+ if ( @classnum ) {
+
+ my @c_where = ();
+ my @nums = grep $_, @classnum;
+ push @c_where, 'part_pkg.classnum IN ('. join(',',@nums). ')' if @nums;
+ my $null = scalar( grep { $_ eq '' } @classnum );
+ push @c_where, 'part_pkg.classnum IS NULL' if $null;
+
+ if ( scalar(@c_where) == 1 ) {
+ push @where, @c_where;
+ } elsif ( @c_where ) {
+ push @where, ' ( '. join(' OR ', @c_where). ' ) ';
+ }
+
+ }
+
+
+ }
+
+ ###
+ # parse package report options
+ ###
+
+ my @report_option = ();
+ if ( exists($params->{'report_option'}) ) {
+ if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
+ @report_option = @{ $params->{'report_option'} };
+ } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
+ @report_option = split(',', $1);
+ }
+
+ }
+
+ 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' )"
+ } @report_option;
+ }
+
+ foreach my $any ( grep /^report_option_any/, keys %$params ) {
+
+ my @report_option_any = ();
+ if ( ref($params->{$any}) eq 'ARRAY' ) {
+ @report_option_any = @{ $params->{$any} };
+ } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
+ @report_option_any = split(',', $1);
+ }
+
+ 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' )"
+ } @report_option_any
+ ). ' ) ';
+ }
+
+ }
+
+ ###
+ # parse custom
+ ###
+
+ push @where, "part_pkg.custom = 'Y'" if $params->{custom};
+
+ ###
+ # parse fcc_line
+ ###
+
+ push @where, "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
+ if $params->{fcc_line};
+
+ ###
+ # parse censustract
+ ###
+
+ if ( exists($params->{'censustract'}) ) {
+ $params->{'censustract'} =~ /^([.\d]*)$/;
+ my $censustract = "cust_location.censustract = '$1'";
+ $censustract .= ' OR cust_location.censustract is NULL' unless $1;
+ push @where, "( $censustract )";
+ }
+
+ ###
+ # parse censustract2
+ ###
+ if ( exists($params->{'censustract2'})
+ && $params->{'censustract2'} =~ /^(\d*)$/
+ )
+ {
+ if ($1) {
+ push @where, "cust_location.censustract LIKE '$1%'";
+ } else {
+ push @where,
+ "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
+ }
+ }
+
+ ###
+ # parse country/state
+ ###
+ for (qw(state country)) { # parsing rules are the same for these
+ if ( exists($params->{$_})
+ && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
+ {
+ # XXX post-2.3 only--before that, state/country may be in cust_main
+ push @where, "cust_location.$_ = '$1'";
+ }
+ }
+
+ ###
+ # location_* flags
+ ###
+ if ( $params->{location_cust} xor $params->{location_nocust} ) {
+ 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_geocode} xor $params->{location_nogeocode} ) {
+ my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
+ push @where, "cust_location.geocode $op";
+ }
+
+ ###
+ # parse part_pkg
+ ###
+
+ if ( ref($params->{'pkgpart'}) ) {
+
+ my @pkgpart = ();
+ if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
+ @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
+ } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
+ @pkgpart = @{ $params->{'pkgpart'} };
+ } else {
+ die 'unhandled pkgpart ref '. $params->{'pkgpart'};
+ }
+
+ @pkgpart = grep /^(\d+)$/, @pkgpart;
+
+ push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
+
+ } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
+ push @where, "pkgpart = $1";
+ }
+
+ ###
+ # parse dates
+ ###
+
+ my $orderby = '';
+
+ #false laziness w/report_cust_pkg.html
+ my %disable = (
+ 'all' => {},
+ 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
+ 'active' => { 'susp'=>1, 'cancel'=>1 },
+ 'suspended' => { 'cancel' => 1 },
+ 'cancelled' => {},
+ '' => {},
+ );
+
+ if( exists($params->{'active'} ) ) {
+ # This overrides all the other date-related fields
+ my($beginning, $ending) = @{$params->{'active'}};
+ push @where,
+ "cust_pkg.setup IS NOT NULL",
+ "cust_pkg.setup <= $ending",
+ "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
+ "NOT (".FS::cust_pkg->onetime_sql . ")";
+ }
+ else {
+ foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
+
+ 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";
+
+ }
+ }
+
+ $orderby ||= 'ORDER BY bill';
+
+ ###
+ # parse magic, legacy, etc.
+ ###
+
+ if ( $params->{'magic'} &&
+ $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
+ ) {
+
+ $orderby = 'ORDER BY pkgnum';
+
+ if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
+ push @where, "pkgpart = $1";
+ }
+
+ } elsif ( $params->{'query'} eq 'pkgnum' ) {
+
+ $orderby = 'ORDER BY pkgnum';
+
+ } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
+
+ $orderby = 'ORDER BY pkgnum';
+
+ push @where, '0 < (
+ SELECT count(*) FROM pkg_svc
+ WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
+ AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
+ WHERE cust_svc.pkgnum = cust_pkg.pkgnum
+ AND cust_svc.svcpart = pkg_svc.svcpart
+ )
+ )';
+
+ }
+
+ ##
+ # setup queries, links, subs, etc. for the search
+ ##
+
+ # here is the agent virtualization
+ if ($params->{CurrentUser}) {
+ my $access_user =
+ qsearchs('access_user', { username => $params->{CurrentUser} });
+
+ if ($access_user) {
+ push @where, $access_user->agentnums_sql('table'=>'cust_main');
+ } else {
+ push @where, "1=0";
+ }
+ } else {
+ push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
+ }
+
+ my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
+
+ my $addl_from = 'LEFT JOIN part_pkg USING ( pkgpart ) '.
+ 'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
+ 'LEFT JOIN cust_location USING ( locationnum ) '.
+ FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
+
+ my $select;
+ my $count_query;
+ if ( $params->{'select_zip5'} ) {
+ my $zip = 'cust_location.zip';
+
+ $select = "DISTINCT substr($zip,1,5) as zip";
+ $orderby = "ORDER BY substr($zip,1,5)";
+ $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
+ } else {
+ $select = join(', ',
+ 'cust_pkg.*',
+ ( map "part_pkg.$_", qw( pkg freq ) ),
+ 'pkg_class.classname',
+ 'cust_main.custnum AS cust_main_custnum',
+ FS::UI::Web::cust_sql_fields(
+ $params->{'cust_fields'}
+ ),
+ );
+ $count_query = 'SELECT COUNT(*)';
+ }
+
+ $count_query .= " FROM cust_pkg $addl_from $extra_sql";
+
+ my $sql_query = {
+ 'table' => 'cust_pkg',
+ 'hashref' => {},
+ 'select' => $select,
+ 'extra_sql' => $extra_sql,
+ 'order_by' => $orderby,
+ 'addl_from' => $addl_from,
+ 'count_query' => $count_query,
+ };
+
+}
+
+1;
+
projects / freeside.git / commitdiff