From c0e8da2f1e89729efa1032241e4239765a296514 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 19 Jun 2006 02:33:52 +0000 Subject: [PATCH] agent virtualization, take one (stuff from "inactive" changeset snuck into cust_main.pm and the package reporting changeset in search/cust_pkg.cgi here too) --- FS/FS/CurrentUser.pm | 50 ++++++++ FS/FS/Record.pm | 29 +++-- FS/FS/Schema.pm | 13 +- FS/FS/UID.pm | 19 ++- FS/FS/access_user.pm | 51 +++++++- FS/FS/cust_main.pm | 213 ++++++++++++++++++++----------- FS/FS/part_pkg.pm | 3 +- FS/MANIFEST | 1 + httemplate/elements/select-agent.html | 2 + httemplate/elements/select-table.html | 7 +- httemplate/elements/tr-select-agent.html | 11 +- httemplate/search/cust_bill.html | 19 ++- httemplate/search/cust_main.cgi | 49 ++++--- httemplate/search/cust_pkg.cgi | 162 ++++++++++++++++------- httemplate/search/svc_acct.cgi | 24 ++-- httemplate/search/svc_domain.cgi | 43 ++++--- httemplate/search/svc_forward.cgi | 24 ++-- 17 files changed, 514 insertions(+), 206 deletions(-) create mode 100644 FS/FS/CurrentUser.pm diff --git a/FS/FS/CurrentUser.pm b/FS/FS/CurrentUser.pm new file mode 100644 index 000000000..13d34167d --- /dev/null +++ b/FS/FS/CurrentUser.pm @@ -0,0 +1,50 @@ +package FS::CurrentUser; + +use vars qw($CurrentUser); + +#not at compile-time, circular dependancey causes trouble +#use FS::Record qw(qsearchs); +#use FS::access_user; + +=head1 NAME + +FS::CurrentUser - Package representing the current user + +=head1 SYNOPSIS + +=head1 DESCRIPTION + +=cut + +sub load_user { + my( $class, $user ) = @_; #, $pass + + #XXX remove me at some point + return "" if $user =~ /^fs_(queue|selfservice)$/; + + #not the best thing in the world... + eval "use FS::Record qw(qsearchs);"; + die $@ if $@; + eval "use FS::access_user;"; + die $@ if $@; + + $CurrentUser = qsearchs('access_user', { + 'username' => $user, + #'_password' => + } ); + + die "unknown user: $user" unless $CurrentUser; # or bad password + + $CurrentUser; +} + +=head1 BUGS + +Creepy crawlies + +=head1 SEE ALSO + +=cut + +1; + diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index 67de071c6..fa0d2d87e 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -29,14 +29,12 @@ $me = '[FS::Record]'; $nowarn_identical = 0; -my $conf; my $rsa_module; my $rsa_loaded; my $rsa_encrypt; my $rsa_decrypt; FS::UID->install_callback( sub { - $conf = new FS::Conf; $File::CounterFile::DEFAULT_DIR = "/usr/local/etc/freeside/counters.". datasrc; } ); @@ -441,6 +439,7 @@ sub qsearch { } # Check for encrypted fields and decrypt them. + my $conf = new FS::Conf; if ($conf->exists('encryption') && eval 'defined(@FS::'. $table . '::encrypted_fields)') { foreach my $record (@return) { foreach my $field (eval '@FS::'. $table . '::encrypted_fields') { @@ -711,6 +710,7 @@ sub insert { # Encrypt before the database + my $conf = new FS::Conf; if ($conf->exists('encryption') && defined(eval '@FS::'. $table . 'encrypted_fields')) { foreach my $field (eval '@FS::'. $table . '::encrypted_fields') { $self->{'saved'} = $self->getfield($field); @@ -727,12 +727,18 @@ sub insert { my @values = map { _quote( $self->getfield($_), $table, $_) } @real_fields; #eslaf - my $statement = "INSERT INTO $table ( ". - join( ', ', @real_fields ). - ") VALUES (". - join( ', ', @values ). - ")" - ; + my $statement = "INSERT INTO $table "; + if ( @real_fields ) { + $statement .= + "( ". + join( ', ', @real_fields ). + ") VALUES (". + join( ', ', @values ). + ")" + ; + } else { + $statement .= 'DEFAULT VALUES'; + } warn "[debug]$me $statement\n" if $DEBUG > 1; my $sth = dbh->prepare($statement) or return dbh->errstr; @@ -995,6 +1001,7 @@ sub replace { return $error if $error; # Encrypt for replace + my $conf = new FS::Conf; my $saved = {}; if ($conf->exists('encryption') && defined(eval '@FS::'. $new->table . 'encrypted_fields')) { foreach my $field (eval '@FS::'. $new->table . '::encrypted_fields') { @@ -1635,7 +1642,8 @@ sub virtual_fields { "WHERE dbtable = '$table'"; my $dbh = dbh; my $result = $dbh->selectcol_arrayref($query); - confess $dbh->errstr if $dbh->err; + confess "Error executing virtual fields query: $query: ". $dbh->errstr + if $dbh->err; $virtual_fields_cache{$table} = $result; } @@ -1788,6 +1796,7 @@ sub encrypt { my ($self, $value) = @_; my $encrypted; + my $conf = new FS::Conf; if ($conf->exists('encryption')) { if ($self->is_encrypted($value)) { # Return the original value if it isn't plaintext. @@ -1821,6 +1830,7 @@ sub is_encrypted { sub decrypt { my ($self,$value) = @_; my $decrypted = $value; # Will return the original value if it isn't encrypted or can't be decrypted. + my $conf = new FS::Conf; if ($conf->exists('encryption') && $self->is_encrypted($value)) { $self->loadRSA; if (ref($rsa_decrypt) =~ /::RSA/) { @@ -1836,6 +1846,7 @@ sub loadRSA { #Initialize the Module $rsa_module = 'Crypt::OpenSSL::RSA'; # The Default + my $conf = new FS::Conf; if ($conf->exists('encryptionmodule') && $conf->config('encryptionmodule') ne '') { $rsa_module = $conf->config('encryptionmodule'); } diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 17d541e8c..7219274e6 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -16,12 +16,13 @@ use FS::UID qw(datasrc); $DEBUG = 0; $me = '[FS::Schema]'; -#ask FS::UID to run this stuff for us later -FS::UID->install_callback( sub { - #$conf = new FS::Conf; - &reload_dbdef("/usr/local/etc/freeside/dbdef.". datasrc) - unless $setup_hack; #$setup_hack needed now? -} ); +#hardcoded now... +##ask FS::UID to run this stuff for us later +#FS::UID->install_callback( sub { +# #$conf = new FS::Conf; +# &reload_dbdef("/usr/local/etc/freeside/dbdef.". datasrc) +# unless $setup_hack; #$setup_hack needed now? +#} ); =head1 NAME diff --git a/FS/FS/UID.pm b/FS/FS/UID.pm index 83b652b0f..21df9441b 100644 --- a/FS/FS/UID.pm +++ b/FS/FS/UID.pm @@ -13,6 +13,7 @@ use Exporter; use Carp qw(carp croak cluck); use DBI; use FS::Conf; +use FS::CurrentUser; @ISA = qw(Exporter); @EXPORT_OK = qw(checkeuid checkruid cgisuidsetup adminsuidsetup forksuidsetup @@ -87,6 +88,12 @@ sub forksuidsetup { $dbh = &myconnect; + use FS::Schema qw(reload_dbdef); + reload_dbdef("/usr/local/etc/freeside/dbdef.$datasrc") + unless $FS::Schema::setup_hack; + + FS::CurrentUser->load_user($user); + foreach ( keys %callback ) { &{$callback{$_}}; # breaks multi-database installs # delete $callback{$_}; #run once @@ -98,7 +105,11 @@ sub forksuidsetup { } sub myconnect { - DBI->connect( getsecrets, {'AutoCommit' => 0, 'ChopBlanks' => 1, } ) + DBI->connect( getsecrets, { 'AutoCommit' => 0, + 'ChopBlanks' => 1, + 'ShowErrorStatement' => 1, + } + ) or die "DBI->connect error: $DBI::errstr\n"; } @@ -256,10 +267,10 @@ sub getsecrets { $user = $setuser if $setuser; die "No user!" unless $user; my($conf) = new FS::Conf $conf_dir; - my($line) = grep /^\s*$user\s/, $conf->config('mapsecrets'); + my($line) = grep /^\s*($user|\*)\s/, $conf->config('mapsecrets'); die "User $user not found in mapsecrets!" unless $line; - $line =~ /^\s*$user\s+(.*)$/; - $secrets = $1; + $line =~ /^\s*($user|\*)\s+(.*)$/; + $secrets = $2; die "Illegal mapsecrets line for user?!" unless $secrets; ($datasrc, $db_user, $db_pass) = $conf->config($secrets) or die "Can't get secrets: $secrets: $!\n"; diff --git a/FS/FS/access_user.pm b/FS/FS/access_user.pm index ca311d3b8..c95d02984 100644 --- a/FS/FS/access_user.pm +++ b/FS/FS/access_user.pm @@ -2,7 +2,7 @@ package FS::access_user; use strict; use vars qw( @ISA ); -use FS::Record qw( qsearch qsearchs ); +use FS::Record qw( qsearch qsearchs dbh ); use FS::m2m_Common; use FS::access_usergroup; @@ -29,7 +29,7 @@ FS::access_user - Object methods for access_user records =head1 DESCRIPTION -An FS::access_user object represents an example. FS::access_user inherits from +An FS::access_user object represents an internal access user. FS::access_user inherits from FS::Record. The following fields are currently supported: =over 4 @@ -52,7 +52,7 @@ FS::Record. The following fields are currently supported: =item new HASHREF -Creates a new example. To add the example to the database, see L<"insert">. +Creates a new internal access user. To add the user to the database, see L<"insert">. Note that this stores the hash reference, not a distinct copy of the hash it points to. You can ask the object for a copy with the I method. @@ -91,7 +91,7 @@ returns the error, otherwise returns false. =item check -Checks all fields to make sure this is a valid example. If there is +Checks all fields to make sure this is a valid internal access user. If there is an error, returns the error, otherwise returns false. Called by the insert and replace methods. @@ -151,12 +151,51 @@ sub access_usergroup { # #} +=item agentnums + +Returns a list of agentnums this user can view (via group membership). + +=cut + +sub agentnums { + my $self = shift; + my $sth = dbh->prepare( + "SELECT DISTINCT agentnum FROM access_usergroup + JOIN access_groupagent USING ( groupnum ) + WHERE usernum = ?" + ) or die dbh->errstr; + $sth->execute($self->usernum) or die $sth->errstr; + map { $_->[0] } @{ $sth->fetchall_arrayref }; +} + +=item agentnums_href + +Returns a hashref of agentnums this user can view. + +=cut + +sub agentnums_href { + my $self = shift; + { map { $_ => 1 } $self->agentnums }; +} + +=item agentnums_sql + +Returns an sql fragement to select only agentnums this user can view. + +=cut + +sub agentnums_sql { + my $self = shift; + '( '. + join( ' OR ', map "agentnum = $_", $self->agentnums ). + ' )'; +} + =back =head1 BUGS -The author forgot to customize this manpage. - =head1 SEE ALSO L, schema.html from the base documentation. diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 2763c1386..8956d5b26 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -20,6 +20,7 @@ use Date::Parse; #use Date::Manip; use String::Approx qw(amatch); use Business::CreditCard 0.28; +use Locale::Country; use FS::UID qw( getotaker dbh ); use FS::Record qw( qsearchs qsearch dbdef ); use FS::Misc qw( send_email ); @@ -79,7 +80,7 @@ sub _cache { my $self = shift; my ( $hashref, $cache ) = @_; if ( exists $hashref->{'pkgnum'} ) { -# #@{ $self->{'_pkgnum'} } = (); + #@{ $self->{'_pkgnum'} } = (); my $subcache = $cache->subcache( 'pkgnum', 'cust_pkg', $hashref->{custnum}); $self->{'_pkgnum'} = $subcache; #push @{ $self->{'_pkgnum'} }, @@ -3186,6 +3187,7 @@ This interface may change in the future. sub invoicing_list { my( $self, $arrayref ) = @_; + if ( $arrayref ) { my @cust_main_invoice; if ( $self->custnum ) { @@ -3220,12 +3222,14 @@ sub invoicing_list { warn $error if $error; } } + if ( $self->custnum ) { map { $_->address } qsearch( 'cust_main_invoice', { 'custnum' => $self->custnum } ); } else { (); } + } =item check_invoicing_list ARRAYREF @@ -3303,6 +3307,18 @@ sub invoicing_list_addpost { $self->invoicing_list(\@invoicing_list); } +=item invoicing_list_emailonly + +Returns the list of email invoice recipients (invoicing_list without non-email +destinations such as POST and FAX). + +=cut + +sub invoicing_list_emailonly { + my $self = shift; + grep { $_ !~ /^([A-Z]+)$/ } $self->invoicing_list; +} + =item referral_cust_main [ DEPTH [ EXCLUDE_HASHREF ] ] Returns an array of customers referred by this customer (referral_custnum set @@ -3600,6 +3616,17 @@ sub ship_contact { : $self->contact; } +=item country_full + +Returns this customer's full country name + +=cut + +sub country_full { + my $self = shift; + code2country($self->country); +} + =item status Returns a status string for this customer, currently: @@ -3610,6 +3637,8 @@ Returns a status string for this customer, currently: =item active - One or more recurring packages is active +=item inactive - No active recurring packages, but otherwise unsuspended/uncancelled (the inactive status is new - previously inactive customers were mis-identified as cancelled) + =item suspended - All non-cancelled recurring packages are suspended =item cancelled - All recurring packages are cancelled @@ -3620,7 +3649,7 @@ Returns a status string for this customer, currently: sub status { my $self = shift; - for my $status (qw( prospect active suspended cancelled )) { + for my $status (qw( prospect active inactive suspended cancelled )) { my $method = $status.'_sql'; my $numnum = ( my $sql = $self->$method() ) =~ s/cust_main\.custnum/?/g; my $sth = dbh->prepare("SELECT $sql") or die dbh->errstr; @@ -3635,14 +3664,18 @@ Returns a hex triplet color string for this customer's status. =cut -my %statuscolor = ( - 'prospect' => '000000', - 'active' => '00CC00', - 'suspended' => 'FF9900', - 'cancelled' => 'FF0000', -); + sub statuscolor { my $self = shift; + + my %statuscolor = ( + 'prospect' => '7e0079', #'000000', #black? naw, purple + 'active' => '00CC00', #green + 'inactive' => '0000CC', #blue + 'suspended' => 'FF9900', #yellow + 'cancelled' => 'FF0000', #red + ); + $statuscolor{$self->status}; } @@ -3659,25 +3692,40 @@ with no packages ever ordered) =cut +use vars qw($select_count_pkgs); +$select_count_pkgs = + "SELECT COUNT(*) FROM cust_pkg + WHERE cust_pkg.custnum = cust_main.custnum"; + sub prospect_sql { " - 0 = ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - ) + 0 = ( $select_count_pkgs ) "; } =item active_sql -Returns an SQL expression identifying active cust_main records. +Returns an SQL expression identifying active cust_main records (customers with +no active recurring packages, but otherwise unsuspended/uncancelled). =cut sub active_sql { " - 0 < ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - AND ". FS::cust_pkg->active_sql. " + 0 < ( $select_count_pkgs AND ". FS::cust_pkg->active_sql. " ) "; } +=item inactive_sql + +Returns an SQL expression identifying inactive cust_main records (customers with +active recurring packages). + +=cut + +sub inactive_sql { " + 0 = ( $select_count_pkgs AND ". FS::cust_pkg->active_sql. " ) + AND + 0 < ( $select_count_pkgs AND ". FS::cust_pkg->inactive_sql. " ) +"; } + =item susp_sql =item suspended_sql @@ -3685,23 +3733,12 @@ Returns an SQL expression identifying suspended cust_main records. =cut -#my $recurring_sql = FS::cust_pkg->recurring_sql; -my $recurring_sql = " - '0' != ( select freq from part_pkg - where cust_pkg.pkgpart = part_pkg.pkgpart ) -"; sub suspended_sql { susp_sql(@_); } sub susp_sql { " - 0 < ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - AND $recurring_sql - AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) - ) - AND 0 = ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - AND ". FS::cust_pkg->active_sql. " - ) + 0 < ( $select_count_pkgs AND ". FS::cust_pkg->suspended_sql. " ) + AND + 0 = ( $select_count_pkgs AND ". FS::cust_pkg->active_sql. " ) "; } =item cancel_sql @@ -3712,16 +3749,21 @@ Returns an SQL expression identifying cancelled cust_main records. =cut sub cancelled_sql { cancel_sql(@_); } -sub cancel_sql { " - 0 < ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - ) - AND 0 = ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - AND $recurring_sql - AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) - ) -"; } +sub cancel_sql { + + my $recurring_sql = FS::cust_pkg->recurring_sql; + #my $recurring_sql = " + # '0' != ( select freq from part_pkg + # where cust_pkg.pkgpart = part_pkg.pkgpart ) + #"; + + " + 0 < ( $select_count_pkgs ) + AND 0 = ( $select_count_pkgs AND $recurring_sql + AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) + ) + "; +} =item uncancel_sql =item uncancelled_sql @@ -3732,15 +3774,12 @@ Returns an SQL expression identifying un-cancelled cust_main records. sub uncancelled_sql { uncancel_sql(@_); } sub uncancel_sql { " - ( 0 < ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum + ( 0 < ( $select_count_pkgs AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) ) - OR 0 = ( SELECT COUNT(*) FROM cust_pkg - WHERE cust_pkg.custnum = cust_main.custnum - ) + OR 0 = ( $select_count_pkgs ) ) "; } @@ -3802,11 +3841,18 @@ Returns a (possibly empty) array of FS::cust_main objects. sub smart_search { my %options = @_; my $search = delete $options{'search'}; - my @cust_main = (); + #here is the agent virtualization + my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; + + my @cust_main = (); if ( $search =~ /^\s*(\d+)\s*$/ ) { # customer # search - push @cust_main, qsearch('cust_main', { 'custnum' => $1, %options } ); + push @cust_main, qsearch( { + 'table' => 'cust_main', + 'hashref' => { 'custnum' => $1, %options }, + 'extra_sql' => " AND $agentnums_sql", #agent virtualization + } ); } elsif ( $search =~ /^\s*(\S.*\S)\s*$/ ) { #value search @@ -3820,50 +3866,65 @@ sub smart_search { if defined dbdef->table('cust_main')->column('ship_last'); $sql .= ' )'; - push @cust_main, qsearch( 'cust_main', \%options, '', $sql ); + push @cust_main, qsearch( { + 'table' => 'cust_main', + 'hashref' => \%options, + 'extra_sql' => "$sql AND $agentnums_sql", #agent virtualization + } ); unless ( @cust_main ) { #no exact match, trying substring/fuzzy #still some false laziness w/ search/cust_main.cgi #substring - push @cust_main, qsearch( 'cust_main', - { 'last' => { 'op' => 'ILIKE', - 'value' => "%$q_value%" }, - %options, - } - ); - push @cust_main, qsearch( 'cust_main', - { 'ship_last' => { 'op' => 'ILIKE', - 'value' => "%$q_value%" }, - %options, - - } - ) + push @cust_main, qsearch( { + 'table' => 'cust_main', + 'hashref' => { 'last' => { 'op' => 'ILIKE', + 'value' => "%$q_value%" }, + %options, + }, + 'extra_sql' => " AND $agentnums_sql", #agent virtualizaiton + } ); + push @cust_main, qsearch( { + 'table' => 'cust_main', + 'hashref' => { 'ship_last' => { 'op' => 'ILIKE', + 'value' => "%$q_value%" }, + %options, + }, + 'extra_sql' => " AND $agentnums_sql", #agent virtualization + } ) if defined dbdef->table('cust_main')->column('ship_last'); - push @cust_main, qsearch( 'cust_main', - { 'company' => { 'op' => 'ILIKE', - 'value' => "%$q_value%" }, - %options, - } - ); - push @cust_main, qsearch( 'cust_main', - { 'ship_company' => { 'op' => 'ILIKE', - 'value' => "%$q_value%" }, - %options, - } - ) + push @cust_main, qsearch( { + 'table' => 'cust_main', + 'hashref' => { 'company' => { 'op' => 'ILIKE', + 'value' => "%$q_value%" }, + %options, + }, + 'extra_sql' => " AND $agentnums_sql", #agent virtualization + } ); + push @cust_main, qsearch( { + 'table' => 'cust_main', + 'hashref' => { 'ship_company' => { 'op' => 'ILIKE', + 'value' => "%$q_value%" }, + %options, + }, + 'extra_sql' => " AND $agentnums_sql", #agent virtualization + } ) if defined dbdef->table('cust_main')->column('ship_last'); #fuzzy push @cust_main, FS::cust_main->fuzzy_search( - { 'last' => $value }, - \%options, + { 'last' => $value }, #fuzzy hashref + \%options, #hashref + '', #select + " AND $agentnums_sql", #extra_sql #agent virtualization ); push @cust_main, FS::cust_main->fuzzy_search( - { 'company' => $value }, - \%options, + { 'company' => $value }, #fuzzy hashref + \%options, #hashref + '', #select + " AND $agentnums_sql", #extra_sql #agent virtualization ); } diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm index de4d047de..aa39d890b 100644 --- a/FS/FS/part_pkg.pm +++ b/FS/FS/part_pkg.pm @@ -608,7 +608,8 @@ sub freq_pretty { my $self = shift; my $freq = $self->freq; - my $freqs_href = $self->freqs_href; + #my $freqs_href = $self->freqs_href; + my $freqs_href = freqs_href(); if ( exists($freqs_href->{$freq}) ) { $freqs_href->{$freq}; diff --git a/FS/MANIFEST b/FS/MANIFEST index 098fe4a64..6db82710c 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -345,3 +345,4 @@ t/pay_batch.t FS/ConfDefaults.pm t/ConfDefaults.t FS/m2name_Common.pm +FS/CurrentUser.pm diff --git a/httemplate/elements/select-agent.html b/httemplate/elements/select-agent.html index aa480a54b..78ec25f82 100644 --- a/httemplate/elements/select-agent.html +++ b/httemplate/elements/select-agent.html @@ -11,6 +11,8 @@ 'value' => $agentnum, 'empty_label' => 'all', 'hashref' => { 'disabled' => '' }, + 'extra_sql' => ' AND '. + $FS::CurrentUser::CurrentUser->agentnums_sql, %select_opt, ) %> diff --git a/httemplate/elements/select-table.html b/httemplate/elements/select-table.html index 96f0177fb..6c8089b31 100644 --- a/httemplate/elements/select-table.html +++ b/httemplate/elements/select-table.html @@ -10,6 +10,7 @@ ##opt # 'empty_label' => '', #better specify it though, the default might change # 'hashref' => {}, + # 'extra_sql' => '', # 'records' => \@records, #instead of hashref # 'pre_options' => [ 'value' => 'option' ], #before normal options @@ -25,7 +26,11 @@ if ( $opt{'records'} ) { @records = @{ $opt{'records'} }; } else { - @records = qsearch( $opt{'table'}, ( $opt{'hashref'} || {} ) ); + @records = qsearch( { + 'table' => $opt{'table'}, + 'hashref' => ( $opt{'hashref'} || {} ), + 'extra_sql' => ( $opt{'extra_sql'} || '' ), + }); } my @pre_options = $opt{'pre_options'} ? @{ $opt{'pre_options'} } : (); diff --git a/httemplate/elements/tr-select-agent.html b/httemplate/elements/tr-select-agent.html index 2227262b6..83c8d1758 100644 --- a/httemplate/elements/tr-select-agent.html +++ b/httemplate/elements/tr-select-agent.html @@ -3,9 +3,16 @@ my @agents; if ( $opt{'agents'} ) { - @agents = @{ $opt{'agents'} }; + #@agents = @{ $opt{'agents'} }; + #here is the agent virtualization + my $agentnums_href = $FS::CurrentUser::CurrentUser->agentnums_href; + @agents = grep $agentnums_href->{$_->agentnum}, @{ $opt{'agents'} }; } else { - @agents = qsearch( 'agent', { disabled=>'' } ); + @agents = qsearch( { + 'table' => 'agent', + 'hashref' => { disabled=>'' }, + 'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql, + }); } %> diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 2108653a8..16128d5c8 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -1,4 +1,9 @@ <% + + my $join_cust_main = 'LEFT JOIN cust_main USING ( custnum )'; + #here is the agent virtualization + my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; + my( $count_query, $sql_query ); my( $count_addl ) = ( '' ); my( $distinct ) = ( '' ); @@ -6,11 +11,15 @@ my($agentnum) = ( '' ); my($open, $days) = ( '', '' ); if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { - $count_query = "SELECT COUNT(*) FROM cust_bill WHERE invnum = $2"; + $count_query = + "SELECT COUNT(*) FROM cust_bill $join_cust_main". + " WHERE invnum = $2 AND $agentnums_sql"; #agent virtualization $sql_query = { 'table' => 'cust_bill', + 'addl_from' => $join_cust_main, 'hashref' => { 'invnum' => $2 }, #'select' => '*', + 'extra_sql' => " AND $agentnums_sql", #agent virtualization }; } else { #if ( $cgi->param('begin') || $cgi->param('end') @@ -68,10 +77,10 @@ push @where, "cust_bill._date < ". (time-86400*$days) if $days; } + #here is the agent virtualization + push @where, $agentnums_sql; my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; - my $addl_from = 'left join cust_main using ( custnum )'; - if ( $cgi->param('newest_percust') ) { $distinct = 'DISTINCT ON ( cust_bill.custnum )'; $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; @@ -85,11 +94,11 @@ '$%.2f total outstanding balance', ]; } - $count_query .= " FROM cust_bill $addl_from $extra_sql"; + $count_query .= " FROM cust_bill $join_cust_main $extra_sql"; $sql_query = { 'table' => 'cust_bill', - 'addl_from' => $addl_from, + 'addl_from' => $join_cust_main, 'hashref' => {}, 'select' => "$distinct ". join(', ', 'cust_bill.*', diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 733e5dc15..5b7ccec1c 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -117,6 +117,10 @@ if ( $cgi->param('browse') my $addl_qual = join(' AND ', @qual); + #here is the agent virtualization + $addl_qual .= ( $addl_qual ? ' AND ' : '' ). + $FS::CurrentUser::CurrentUser->agentnums_sql; + if ( $addl_qual ) { $qual .= ' AND ' if $qual; $qual .= $addl_qual; @@ -322,32 +326,43 @@ END print "

". $pager. include('/elements/table-grid.html'). < - - (bill) name - company + + (bill) name + company END if ( defined dbdef->table('cust_main')->column('ship_last') ) { print <(service) name - company + (service) name + company END } foreach my $addl_header ( @addl_headers ) { - print "$addl_header"; + print ''. "$addl_header"; } print <Packages - Services + Packages + Services END + my $bgcolor1 = '#eeeeee'; + my $bgcolor2 = '#ffffff'; + my $bgcolor; + my(%saw,$cust_main); foreach $cust_main ( sort $sortby grep(!$saw{$_->custnum}++, @cust_main) ) { + + if ( $bgcolor eq $bgcolor1 ) { + $bgcolor = $bgcolor2; + } else { + $bgcolor = $bgcolor1; + } + my($custnum,$last,$first,$company)=( $cust_main->custnum, $cust_main->getfield('last'), @@ -377,9 +392,9 @@ END %> - ><%= $custnum %> - ><%= "$last, $first" %> - ><%= $pcompany %> + ><%= $custnum %> + ><%= "$last, $first" %> + ><%= $pcompany %> <% if ( defined dbdef->table('cust_main')->column('ship_last') ) { @@ -393,14 +408,14 @@ END : ' '; %> - ><%= "$ship_last, $ship_first" %> - ><%= $pship_company %> + ><%= "$ship_last, $ship_first" %> + ><%= $pship_company %> <% } foreach my $addl_col ( @addl_cols ) { %> - ALIGN=right> + ALIGN=right> <% if ( $addl_col eq 'tickets' ) { if ( @custom_priorities ) { @@ -457,14 +472,14 @@ END #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); my $rowspan = scalar(@cust_svc) || 1; - print $n1, qq!$pkg - $comment!; + print $n1, qq!$pkg - $comment!; my($n2)=''; foreach my $cust_svc ( @cust_svc ) { my($label, $value, $svcdb) = $cust_svc->label; my($svcnum) = $cust_svc->svcnum; my($sview) = $p.'view'; - print $n2,qq!$label!, - qq!$value!; + print $n2,qq!$label!, + qq!$value!; $n2=""; } #print qq!\n!; diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index a2fb89c12..e8b3f490d 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,19 +1,80 @@ <% -my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); +# my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); my($query) = $cgi->keywords; -my $orderby; -my @where; -my $cjoin = ''; +my @where = (); + +## +# parse agent +## if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { - $cjoin = "LEFT JOIN cust_main USING ( custnum )"; push @where, "agentnum = $1"; } +## +# parse status +## + +if ( $cgi->param('magic') eq 'active' + || $cgi->param('status') eq 'active' ) { + + push @where, FS::cust_pkg->active_sql(); + +} elsif ( $cgi->param('magic') eq 'suspended' + || $cgi->param('status') eq 'suspended' ) { + + push @where, FS::cust_pkg->suspended_sql(); + +} elsif ( $cgi->param('magic') =~ /^cancell?ed$/ + || $cgi->param('status') =~ /^cancell?ed$/ ) { + + push @where, FS::cust_pkg->cancelled_sql(); + +} elsif ( $cgi->param('status') =~ /^(one-time charge|inactive)$/ ) { + + push @where, FS::cust_pkg->inactive_sql(); + +} + +### +# parse package class +### + +#false lazinessish w/graph/cust_bill_pkg.cgi +my $classnum = 0; +my @pkg_class = (); +if ( $cgi->param('classnum') =~ /^(\d*)$/ ) { + $classnum = $1; + if ( $classnum ) { #a specific class + push @where, "classnum = $classnum"; + + #@pkg_class = ( qsearchs('pkg_class', { 'classnum' => $classnum } ) ); + #die "classnum $classnum not found!" unless $pkg_class[0]; + #$title .= $pkg_class[0]->classname.' '; + + } elsif ( $classnum eq '' ) { #the empty class + + push @where, "classnum IS NULL"; + #$title .= 'Empty class '; + #@pkg_class = ( '(empty class)' ); + } elsif ( $classnum eq '0' ) { + #@pkg_class = qsearch('pkg_class', {} ); # { 'disabled' => '' } ); + #push @pkg_class, '(empty class)'; + } else { + die "illegal classnum"; + } +} +#eslaf + +### +# parse magic, legacy, etc. +### + +my $orderby; if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $orderby = 'ORDER BY bill'; @@ -23,7 +84,8 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { #"bill <= $ending", "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ", "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending", - '( cancel IS NULL OR cancel = 0 )'; + #'( cancel IS NULL OR cancel = 0 )' + ; } else { @@ -33,30 +95,6 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $orderby = 'ORDER BY pkgnum'; - if ( $cgi->param('magic') eq 'active' ) { - - #push @where, - # '( susp IS NULL OR susp = 0 )', - # '( cancel IS NULL OR cancel = 0)'; - push @where, FS::cust_pkg->active_sql(); - - } elsif ( $cgi->param('magic') eq 'suspended' ) { - - push @where, - 'susp IS NOT NULL', - 'susp != 0', - '( cancel IS NULL OR cancel = 0)'; - - } elsif ( $cgi->param('magic') =~ /^cancell?ed$/ ) { - - push @where, - 'cancel IS NOT NULL', - 'cancel != 0'; - - } else { - die "guru meditation #420"; - } - if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { push @where, "pkgpart = $1"; } @@ -84,21 +122,35 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } +## +# setup queries, links, subs, etc. for the search +## + +# here is the agent virtualization +push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; + my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; -my $count_query = "SELECT COUNT(*) FROM cust_pkg $cjoin $extra_sql"; +my $addl_from = 'LEFT JOIN cust_main USING ( custnum ) '. + 'LEFT JOIN part_pkg USING ( pkgpart ) '. + 'LEFT JOIN pkg_class USING ( classnum ) '; + +my $count_query = "SELECT COUNT(*) FROM cust_pkg $addl_from $extra_sql"; my $sql_query = { 'table' => 'cust_pkg', 'hashref' => {}, '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(), + FS::UI::Web::cust_sql_fields( + $cgi->param('cust_fields') + ), ), 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ', - #' LEFT JOIN part_pkg USING ( pkgpart ) ' + 'addl_from' => $addl_from, }; my $link = sub { @@ -138,6 +190,10 @@ sub time_or_blank { }; } +### +# and finally, include the search template +### + %><%= include( 'elements/search.html', 'title' => 'Package Search Results', 'name' => 'packages', @@ -146,6 +202,7 @@ sub time_or_blank { #'redirect' => $link, 'header' => [ '#', 'Package', + 'Class', 'Status', 'Freq.', 'Setup', @@ -154,18 +211,25 @@ sub time_or_blank { 'Susp.', 'Expire', 'Cancel', - FS::UI::Web::cust_header(), + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ), 'Services', ], 'fields' => [ 'pkgnum', - sub { my $part_pkg = $part_pkg{shift->pkgpart}; - $part_pkg->pkg; # ' - '. $part_pkg->comment; + sub { #my $part_pkg = $part_pkg{shift->pkgpart}; + #$part_pkg->pkg; # ' - '. $part_pkg->comment; + $_[0]->pkg; # ' - '. $_[0]->comment; }, + 'classname', sub { ucfirst(shift->status); }, sub { #shift->part_pkg->freq_pretty; - my $part_pkg = $part_pkg{shift->pkgpart}; - $part_pkg->freq_pretty; + + #my $part_pkg = $part_pkg{shift->pkgpart}; + #$part_pkg->freq_pretty; + + FS::part_pkg::freq_pretty(shift); }, #sub { time2str('%b %d %Y', shift->setup); }, @@ -204,6 +268,7 @@ sub time_or_blank { 'color' => [ '', '', + '', sub { shift->statuscolor; }, '', '', @@ -212,12 +277,16 @@ sub time_or_blank { '', '', '', - ( map { '' } FS::UI::Web::cust_header() ), + ( map { '' } + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ) + ), '', ], - 'style' => [ '', '', 'b' ], - 'size' => [ '', '', '-1', ], - 'align' => 'rlclrrrrrr', + 'style' => [ '', '', '', 'b' ], + 'size' => [ '', '', '', '-1', ], + 'align' => 'rllclrrrrrr', 'links' => [ $link, $link, @@ -229,7 +298,12 @@ sub time_or_blank { '', '', '', - ( map { $clink } FS::UI::Web::cust_header() ), + '', + ( map { $clink } + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ) + ), '', ], ) diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index b14591958..ef68ba05e 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -5,11 +5,9 @@ my $orderby = 'ORDER BY svcnum'; my($query)=$cgi->keywords; $query ||= ''; #to avoid use of unitialized value errors -my $cjoin = ''; my @extra_sql = (); if ( $query =~ /^UN_(.*)$/ ) { $query = $1; - $cjoin = 'LEFT JOIN cust_svc USING ( svcnum )'; push @extra_sql, 'pkgnum IS NULL'; } @@ -24,7 +22,6 @@ if ( $query eq 'svcnum' ) { push @extra_sql, "popnum = $1"; $orderby = "ORDER BY LOWER(username)"; } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { - $cjoin ||= 'LEFT JOIN cust_svc USING ( svcnum )'; push @extra_sql, "svcpart = $1"; $orderby = "ORDER BY uid"; #$orderby = "ORDER BY svcnum"; @@ -72,12 +69,20 @@ if ( $query eq 'svcnum' ) { } +my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. + ' LEFT JOIN part_svc USING ( svcpart ) '. + ' LEFT JOIN cust_pkg USING ( pkgnum ) '. + ' LEFT JOIN cust_main USING ( custnum ) '; + +#here is the agent virtualization +push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; + my $extra_sql = scalar(@extra_sql) ? ' WHERE '. join(' AND ', @extra_sql ) : ''; -my $count_query = "SELECT COUNT(*) FROM svc_acct $cjoin $extra_sql"; +my $count_query = "SELECT COUNT(*) FROM svc_acct $addl_from $extra_sql"; #if ( keys %svc_acct ) { # $count_query .= ' WHERE '. # join(' AND ', map "$_ = ". dbh->quote($svc_acct{$_}), @@ -94,10 +99,7 @@ my $sql_query = { FS::UI::Web::cust_sql_fields(), ), 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) ', + 'addl_from' => $addl_from, }; my $link = [ "${p}view/svc_acct.cgi?", 'svcnum' ]; @@ -117,21 +119,21 @@ my $link_cust = sub { 'count_query' => $count_query, 'redirect' => $link, 'header' => [ '#', + 'Service', 'Account', 'UID', - 'Service', FS::UI::Web::cust_header(), ], 'fields' => [ 'svcnum', + 'svc', 'email', 'uid', - 'svc', \&FS::UI::Web::cust_fields, ], 'links' => [ $link, $link, $link, - '', + $link, ( map { $link_cust } FS::UI::Web::cust_header() ), diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi index b02eea8bd..1eda3733c 100755 --- a/httemplate/search/svc_domain.cgi +++ b/httemplate/search/svc_domain.cgi @@ -6,38 +6,50 @@ my($query)=$cgi->keywords; $query ||= ''; #to avoid use of unitialized value errors my $orderby = 'ORDER BY svcnum'; -my $join = ''; my %svc_domain = (); -my $extra_sql = ''; +my @extra_sql = (); if ( $query eq 'svcnum' ) { #$orderby = 'ORDER BY svcnum'; } elsif ( $query eq 'domain' ) { $orderby = 'ORDER BY domain'; -} elsif ( $query eq 'UN_svcnum' ) { +} elsif ( $query eq 'UN_svcnum' ) { #UN searches need to be acl'ed (and need to + #fix $agentnums_sql #$orderby = 'ORDER BY svcnum'; - $join = 'LEFT JOIN cust_svc USING ( svcnum )'; - $extra_sql = ' WHERE pkgnum IS NULL'; -} elsif ( $query eq 'UN_domain' ) { + push @extra_sql, 'pkgnum IS NULL'; +} elsif ( $query eq 'UN_domain' ) { #UN searches need to be acl'ed (and need to + #fix $agentnums_sql $orderby = 'ORDER BY domain'; - $join = 'LEFT JOIN cust_svc USING ( svcnum )'; - $extra_sql = ' WHERE pkgnum IS NULL'; + push @extra_sql, 'pkgnum IS NULL'; } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { #$orderby = 'ORDER BY svcnum'; - $join = 'LEFT JOIN cust_svc USING ( svcnum )'; - $extra_sql = " WHERE svcpart = $1"; + push @extra_sql, "svcpart = $1"; } else { $cgi->param('domain') =~ /^([\w\-\.]+)$/; - $join = ''; $svc_domain{'domain'} = $1; } -my $count_query = "SELECT COUNT(*) FROM svc_domain $join $extra_sql"; +my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. + ' LEFT JOIN part_svc USING ( svcpart ) '. + ' LEFT JOIN cust_pkg USING ( pkgnum ) '. + ' LEFT JOIN cust_main USING ( custnum ) '; + +#here is the agent virtualization +push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; + +my $extra_sql = ''; +if ( @extra_sql ) { + $extra_sql = ( keys(%svc_domain) ? ' AND ' : ' WHERE ' ). + join(' AND ', @extra_sql ); +} + +my $count_query = "SELECT COUNT(*) FROM svc_domain $addl_from "; if ( keys %svc_domain ) { $count_query .= ' WHERE '. join(' AND ', map "$_ = ". dbh->quote($svc_domain{$_}), keys %svc_domain ); } +$count_query .= $extra_sql; my $sql_query = { 'table' => 'svc_domain', @@ -48,9 +60,7 @@ my $sql_query = { FS::UI::Web::cust_sql_fields(), ), 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => 'LEFT JOIN cust_svc USING ( svcnum ) '. - 'LEFT JOIN cust_pkg USING ( pkgnum ) '. - 'LEFT JOIN cust_main USING ( custnum ) ', + 'addl_from' => $addl_from, }; my $link = [ "${p}view/svc_domain.cgi?", 'svcnum' ]; @@ -68,15 +78,18 @@ my $link_cust = sub { 'count_query' => $count_query, 'redirect' => $link, 'header' => [ '#', + 'Service', 'Domain', FS::UI::Web::cust_header(), ], 'fields' => [ 'svcnum', + 'svc', 'domain', \&FS::UI::Web::cust_fields, ], 'links' => [ $link, $link, + $link, ( map { $link_cust } FS::UI::Web::cust_header() ), diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi index a204e345f..4961967d7 100755 --- a/httemplate/search/svc_forward.cgi +++ b/httemplate/search/svc_forward.cgi @@ -5,14 +5,12 @@ my $conf = new FS::Conf; my($query)=$cgi->keywords; $query ||= ''; #to avoid use of unitialized value errors - my $orderby; -my $cjoin = ''; my @extra_sql = (); -if ( $query =~ /^UN_(.*)$/ ) { +if ( $query =~ /^UN_(.*)$/ ) { #UN searches need to be acl'ed (and need to + #fix $agentnums_sql $query = $1; - $cjoin = 'LEFT JOIN cust_svc USING ( svcnum )'; push @extra_sql, 'pkgnum IS NULL'; } @@ -22,12 +20,20 @@ if ( $query eq 'svcnum' ) { eidiot('unimplemented'); } +my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. + ' LEFT JOIN part_svc USING ( svcpart ) '. + ' LEFT JOIN cust_pkg USING ( pkgnum ) '. + ' LEFT JOIN cust_main USING ( custnum ) '; + +#here is the agent virtualization +push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; + my $extra_sql = scalar(@extra_sql) ? ' WHERE '. join(' AND ', @extra_sql ) : ''; -my $count_query = "SELECT COUNT(*) FROM svc_forward $cjoin $extra_sql"; +my $count_query = "SELECT COUNT(*) FROM svc_forward $addl_from $extra_sql"; my $sql_query = { 'table' => 'svc_forward', 'hashref' => {}, @@ -37,10 +43,7 @@ my $sql_query = { FS::UI::Web::cust_sql_fields(), ), 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) ', + 'addl_from' => $addl_from, }; # Service #
(click to view forward) @@ -100,16 +103,19 @@ my $link_cust = sub { 'count_query' => $count_query, 'redirect' => $link, 'header' => [ '#', + 'Service', 'Mail to', 'Forwards to', FS::UI::Web::cust_header(), ], 'fields' => [ 'svcnum', + 'svc', $format_src, $format_dst, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, + $link, $link_src, $link_dst, ( map { $link_cust } -- 2.11.0