From d0aca10ecd6edb171555eb87b1570daa3d2820b1 Mon Sep 17 00:00:00 2001 From: jeff Date: Tue, 29 Jan 2008 03:34:12 +0000 Subject: [PATCH] bulk package changing (RT#1394) --- FS/FS/cust_pkg.pm | 280 ++++++++++++++++++++++++++++ httemplate/misc/bulk_change_pkg.cgi | 69 +++++++ httemplate/misc/process/bulk_change_pkg.cgi | 56 ++++++ httemplate/search/cust_pkg.cgi | 193 ++++--------------- httemplate/search/elements/search.html | 5 + 5 files changed, 447 insertions(+), 156 deletions(-) create mode 100755 httemplate/misc/bulk_change_pkg.cgi create mode 100755 httemplate/misc/process/bulk_change_pkg.cgi diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index 71ae0ad13..9d4ca1c00 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -1551,6 +1551,228 @@ sub cancel_sql { "cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0"; } +=item search_sql HREF + +Returns a qsearch hash expression to search for parameters specified in HREF. +Valid parameters are + +=over 4 +=item agentnum +=item magic - /^(active|inactive|suspended|cancell?ed)$/ +=item status - /^(active|inactive|suspended|one-time charge|inactive|cancell?ed)$/ +=item classnum +=item pkgpart - list specified how? +=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/APKG_pkgnum)$/ +=item cust_fields - a value suited to passing to FS::UI::Web::cust_header +=item CurrentUser - specifies the user for agent virtualization +=back + +=cut + +sub search_sql { + my ($class, $params) = @_; + my @where = (); + + ## + # parse agent + ## + + if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) { + push @where, + "agentnum = $1"; + } + + ## + # parse status + ## + + if ( $params->{'magic'} eq 'active' + || $params->{'status'} eq 'active' ) { + + push @where, FS::cust_pkg->active_sql(); + + } elsif ( $params->{'magic'} eq 'inactive' + || $params->{'status'} eq '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(); + + } elsif ( $params->{'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 ( exists($params->{'classnum'}) + && $params->{'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 part_pkg + ### + + my $pkgpart = join (' OR pkgpart=', + grep {$_} map { /^(\d+)$/; } ($params->{'pkgpart'})); + push @where, '(pkgpart=' . $pkgpart . ')' if $pkgpart; + + ### + # 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' => {}, + '' => {}, + ); + + foreach my $field (qw( setup last_bill bill adjourn susp expire 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; + }else{ + push @where, "1=0"; + } + }else{ + push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; + } + + my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; + + 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( + $params->{'cust_fields'} + ), + ), + 'extra_sql' => "$extra_sql $orderby", + 'addl_from' => $addl_from, + 'count_query' => $count_query, + }; + +} + =head1 SUBROUTINES =over 4 @@ -1698,6 +1920,64 @@ If there is an error, returns the error, otherwise returns false. =cut +=item bulk_change PKGPARTS_ARYREF, REMOVE_PKGNUMS_ARYREF [ RETURN_CUST_PKG_ARRAYREF ] + +PKGPARTS is a list of pkgparts specifying the the billing item definitions (see +L) to order for this customer. Duplicates are of course +permitted. + +REMOVE_PKGNUMS is an list of pkgnums specifying the billing items to +replace. The services (see L) are moved to the +new billing items. An error is returned if this is not possible (see +L). + +RETURN_CUST_PKG_ARRAYREF, if specified, will be filled in with the +newly-created cust_pkg objects. + +=cut + +sub bulk_change { + my ($pkgparts, $remove_pkgnum, $return_cust_pkg) = @_; + + # Transactionize this whole mess + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + my @errors; + my @old_cust_pkg = map { qsearchs('cust_pkg', { pkgnum => $_ }) } + @$remove_pkgnum; + + while(scalar(@old_cust_pkg)) { + my @return = (); + my $custnum = $old_cust_pkg[0]->custnum; + my (@remove) = map { $_->pkgnum } + grep { $_->custnum == $custnum } @old_cust_pkg; + @old_cust_pkg = grep { $_->custnum != $custnum } @old_cust_pkg; + + my $error = order $custnum, $pkgparts, \@remove, \@return; + + push @errors, $error + if $error; + push @$return_cust_pkg, @return; + } + + if (scalar(@errors)) { + $dbh->rollback if $oldAutoCommit; + return join(' / ', @errors); + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + ''; +} + sub insert_reason { my ($self, %options) = @_; diff --git a/httemplate/misc/bulk_change_pkg.cgi b/httemplate/misc/bulk_change_pkg.cgi new file mode 100755 index 000000000..ebf20ebc2 --- /dev/null +++ b/httemplate/misc/bulk_change_pkg.cgi @@ -0,0 +1,69 @@ +<% include('/elements/header-popup.html', "Change Packages") %> + +% if ( $cgi->param('error') ) { + Error: <% $cgi->param('error') %> +

+% } + +
+ + +% for my $param (qw(agentnum magic status classnum pkgpart)) { + +% } +% +% foreach my $field (qw( setup last_bill bill adjourn susp expire cancel )) { +% + "> + "> + "> + "> +% } + +<% ntable('#cccccc') %> + + + New package: + <% include('/elements/select-table.html', + 'table' => 'part_pkg', + 'name_col' => 'pkg', + 'empty_label' => 'Select package', + 'label_callback' => sub { $_[0]->pkgpart. ': '. + $_[0]->pkg. ' - '. + $_[0]->comment; + }, + 'element_name' => 'new_pkgpart', + 'curr_value' => ( $cgi->param('error') + ? scalar($cgi->param('new_pkgpart')) + : '' + ), + ) + %> + + + + + +
+ + +
+ + + +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Bulk change customer packages'); + +#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' => {}, + '' => {}, +); + + diff --git a/httemplate/misc/process/bulk_change_pkg.cgi b/httemplate/misc/process/bulk_change_pkg.cgi new file mode 100755 index 000000000..d2ab4bf49 --- /dev/null +++ b/httemplate/misc/process/bulk_change_pkg.cgi @@ -0,0 +1,56 @@ +% if ($error) { +<% $cgi->redirect(popurl(2)."/bulk_change_pkg.cgi?".$cgi->query_string ) %> +% } +<% include('/elements/header-popup.html', "Packages Changed") %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Bulk change customer packages'); + +my %search_hash = (); + +$search_hash{'query'} = $cgi->param('query'); + +for my $param (qw(agentnum magic status classnum pkgpart)) { + $search_hash{$param} = $cgi->param($param) + if $cgi->param($param); +} + +### +# parse dates +### + +#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' => {}, + '' => {}, +); + +foreach my $field (qw( setup last_bill bill adjourn susp expire cancel )) { + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field); + + next if $beginning == 0 && $ending == 4294967295 + or $disable{$cgi->param('status')}->{$field}; + + $search_hash{$field} = [ $beginning, $ending ]; + +} + +my $sql_query = FS::cust_pkg->search_sql(\%search_hash); +$sql_query->{'select'} = 'cust_pkg.pkgnum'; + +my $error = FS::cust_pkg::bulk_change( [ $cgi->param('new_pkgpart') ], + [ map { $_->pkgnum } qsearch($sql_query) ], + ); + +$cgi->param("error", substr($error, 0, 512)); # arbitrary length believed + # suited for all supported + # browsers + + + diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index b354bc133..97b4412fe 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,4 +1,5 @@ <% include( 'elements/search.html', + 'html_init' => $html_init, 'title' => 'Package Search Results', 'name' => 'packages', 'query' => $sql_query, @@ -120,6 +121,7 @@ ), '', ], + 'extra_choices_callback'=> $extra_choices, ) %> <%init> @@ -129,97 +131,19 @@ die "access denied" # my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); -my($query) = $cgi->keywords; - -my @where = (); - -## -# parse agent -## - -if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { - push @where, - "cust_main.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 'inactive' - || $cgi->param('status') eq 'inactive' ) { - - push @where, FS::cust_pkg->inactive_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 ( exists($cgi->Vars->{'classnum'}) - && $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"; + my %search_hash = (); + + $search_hash{'query'} = $cgi->keywords; + + for my $param (qw(agentnum magic status classnum pkgpart)) { + $search_hash{$param} = $cgi->param($param) + if $cgi->param($param); } -} -#eslaf - -### -# parse part_pkg -### - -my $pkgpart = join (' OR pkgpart=', - grep {$_} map { /^(\d+)$/; } ($cgi->param('pkgpart'))); -push @where, '(pkgpart=' . $pkgpart . ')' if $pkgpart; ### # parse dates ### -my $orderby = ''; - #false laziness w/report_cust_pkg.html my %disable = ( 'all' => {}, @@ -237,80 +161,12 @@ foreach my $field (qw( setup last_bill bill adjourn susp expire cancel )) { next if $beginning == 0 && $ending == 4294967295 or $disable{$cgi->param('status')}->{$field}; - 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 ( $cgi->param('magic') && - $cgi->param('magic') =~ /^(active|inactive|suspended|cancell?ed)$/ -) { - - $orderby = 'ORDER BY pkgnum'; - - if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { - push @where, "pkgpart = $1"; - } - -} elsif ( $query eq 'pkgnum' ) { - - $orderby = 'ORDER BY pkgnum'; - -} elsif ( $query eq 'APKG_pkgnum' ) { - - $orderby = 'ORDER BY pkgnum'; + $search_hash{$field} = [ $beginning, $ending ]; - 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 -push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main'); - -my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; - -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( - $cgi->param('cust_fields') - ), - ), - 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => $addl_from, -}; +my $sql_query = FS::cust_pkg->search_sql(\%search_hash); +my $count_query = delete($sql_query->{'count_query'}); my $link = sub { [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ]; @@ -349,4 +205,29 @@ sub time_or_blank { }; } +my $html_init = ''; +for (qw (overlibmws overlibmws_iframe overlibmws_draggable iframecontentmws)) +{ + $html_init .= + qq!!; +} + +my $extra_choices = sub { + my $query = shift; + my $choices = ''; + + my $url = qq!overlib( OLiframeContent('!. popurl(2). + qq!misc/bulk_change_pkg.cgi?$query', 768, 336, !. + qq!'bulk_pkg_change_popup' ), CAPTION, 'Change Packages'!. + qq!, STICKY, AUTOSTATUSCAP, MIDX, 0, MIDY, 0, DRAGGABLE, !. + qq!CLOSECLICK ); return false;!; + + if ($FS::CurrentUser::CurrentUser->access_right('Bulk change customer packages')) { + $choices .= qq!
Change these packages!; + } + + return $choices; +}; + diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index c6021e500..14c6a0b6a 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -384,6 +384,11 @@ Example: % $cgi->param('_type', 'html-print'); as printable copy + <% $opt{'extra_choices_callback'} + ? &{$opt{'extra_choices_callback'}}($cgi->query_string) + : '' + %> + % $cgi->param('_type', "html" ); % } -- 2.11.0