From 590603ecaea3184f64530755a76626be8205da49 Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 3 Feb 2007 11:36:30 +0000 Subject: [PATCH] add customer status column to customer & most other reports. also put the C in ACL in the search/ and graph/ directories. --- FS/FS/AccessRight.pm | 7 +- FS/FS/ConfDefaults.pm | 44 +- FS/FS/UI/Web.pm | 95 +++- FS/FS/cust_main.pm | 30 +- FS/FS/cust_main_Mixin.pm | 92 +++- httemplate/elements/menu.html | 11 +- httemplate/graph/cust_bill_pkg.cgi | 202 ++++---- httemplate/graph/money_time.cgi | 134 ++--- httemplate/graph/report_cust_bill_pkg.html | 6 + httemplate/graph/report_money_time.html | 6 + httemplate/search/cdr.html | 55 +- httemplate/search/cust_bill.html | 350 +++++++------ httemplate/search/cust_bill_event.cgi | 114 ++-- httemplate/search/cust_bill_event.html | 6 + httemplate/search/cust_bill_pkg.cgi | 280 +++++----- httemplate/search/cust_credit.html | 128 +++-- httemplate/search/cust_main-otaker.cgi | 6 + httemplate/search/cust_main-zip.html | 175 +++---- httemplate/search/cust_main.cgi | 14 +- httemplate/search/cust_main.html | 5 + httemplate/search/cust_pay.cgi | 350 +++++++------ httemplate/search/cust_pay_batch.cgi | 231 +++++---- httemplate/search/cust_pkg.cgi | 442 ++++++++-------- httemplate/search/cust_svc.html | 16 +- httemplate/search/cust_tax_exempt_pkg.cgi | 27 +- httemplate/search/inventory_item.html | 120 +++-- httemplate/search/pay_batch.cgi | 85 +-- httemplate/search/pay_batch.html | 6 + httemplate/search/queue.html | 31 +- httemplate/search/reg_code.html | 23 +- httemplate/search/report_cdr.html | 5 + httemplate/search/report_cust_bill.html | 6 + httemplate/search/report_cust_credit.html | 3 + httemplate/search/report_cust_main-zip.html | 10 +- httemplate/search/report_cust_pay.html | 6 + httemplate/search/report_cust_pay_batch.html | 6 + httemplate/search/report_cust_pkg.html | 6 + httemplate/search/report_prepaid_income.cgi | 124 ++--- httemplate/search/report_prepaid_income.html | 6 + httemplate/search/report_receivables.cgi | 300 +++++------ httemplate/search/report_receivables.html | 5 + httemplate/search/report_tax.cgi | 742 +++++++++++++-------------- httemplate/search/report_tax.html | 6 + httemplate/search/sql.html | 6 + httemplate/search/sqlradius.cgi | 5 +- httemplate/search/sqlradius.html | 6 + httemplate/search/svc_acct.cgi | 273 +++++----- httemplate/search/svc_broadband.cgi | 3 + httemplate/search/svc_domain.cgi | 154 +++--- httemplate/search/svc_external.cgi | 3 + httemplate/search/svc_forward.cgi | 215 ++++---- httemplate/search/svc_phone.cgi | 156 +++--- httemplate/search/svc_www.cgi | 136 ++--- 53 files changed, 2936 insertions(+), 2337 deletions(-) diff --git a/FS/FS/AccessRight.pm b/FS/FS/AccessRight.pm index 038cb4113..cc0bf7ff6 100644 --- a/FS/FS/AccessRight.pm +++ b/FS/FS/AccessRight.pm @@ -160,7 +160,7 @@ assigned to users and/or groups. # report/listing rights... ### 'List customers', - #'List zip codes', + 'List zip codes', 'List invoices', 'List packages', 'List services', @@ -179,6 +179,11 @@ assigned to users and/or groups. 'Export', # ### +# misc misc rights +### + 'Raw SQL', + +### # setup/config rights ### 'Edit advertising sources', diff --git a/FS/FS/ConfDefaults.pm b/FS/FS/ConfDefaults.pm index b9cbcfbdf..baee0bb08 100644 --- a/FS/FS/ConfDefaults.pm +++ b/FS/FS/ConfDefaults.pm @@ -28,28 +28,28 @@ labels for customer fields values. # XXX should use msgcat for "Day phone" and "Night phone", but how? sub cust_fields_avail { ( - 'Customer' => - 'Last, First or Company (Last, First)', - 'Cust# | Customer' => - 'custnum | Last, First or Company (Last, First)', - - 'Name | Company' => - 'Last, First | Company', - 'Cust# | Name | Company' => - 'custnum | Last, First | Company', - - '(bill) Customer | (service) Customer' => - 'Last, First or Company (Last, First) | (same for service contact if present)', - 'Cust# | (bill) Customer | (service) Customer' => - 'custnum | Last, First or Company (Last, First) | (same for service contact if present)', - - '(bill) Name | (bill) Company | (service) Name | (service) Company' => - 'Last, First | Company | (same for service address if present)', - 'Cust# | (bill) Name | (bill) Company | (service) Name | (service) Company' => - 'custnum | Last, First | Company | (same for service address if present)', - - 'Cust# | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Invoicing email(s)' => - 'custnum | Last, First | Company | (all address fields ) | Day phone | Night phone | Invoicing email(s)', + 'Cust. Status | Customer' => + 'Status | Last, First or Company (Last, First)', + 'Cust# | Cust. Status | Customer' => + 'custnum | Status | Last, First or Company (Last, First)', + + 'Cust. Status | Name | Company' => + 'Status | Last, First | Company', + 'Cust# | Cust. Status | Name | Company' => + 'custnum | Status | Last, First | Company', + + 'Cust. Status | (bill) Customer | (service) Customer' => + 'Status | Last, First or Company (Last, First) | (same for service contact if present)', + 'Cust# | Cust. Status | (bill) Customer | (service) Customer' => + 'custnum | Status | Last, First or Company (Last, First) | (same for service contact if present)', + + 'Cust. Status | (bill) Name | (bill) Company | (service) Name | (service) Company' => + 'Status | Last, First | Company | (same for service address if present)', + 'Cust# | Cust. Status | (bill) Name | (bill) Company | (service) Name | (service) Company' => + 'custnum | Status | Last, First | Company | (same for service address if present)', + + 'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Invoicing email(s)' => + 'custnum | Status | Last, First | Company | (all address fields ) | Day phone | Night phone | Invoicing email(s)', ); } diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm index 23f0e83c4..c81581665 100644 --- a/FS/FS/UI/Web.pm +++ b/FS/FS/UI/Web.pm @@ -198,15 +198,16 @@ configuration value. =cut -use vars qw( @cust_fields ); +use vars qw( @cust_fields @cust_colors @cust_styles @cust_aligns ); sub cust_header { - warn "FS::svc_Common::cust_header called" + warn "FS::UI:Web::cust_header called" if $DEBUG; my %header2method = ( 'Customer' => 'name', + 'Cust. Status' => 'ucfirst_cust_status', 'Cust#' => 'custnum', 'Name' => 'contact', 'Company' => 'company', @@ -227,6 +228,16 @@ sub cust_header { 'Invoicing email(s)' => 'invoicing_list_emailonly_scalar', ); + my %header2colormethod = ( + 'Cust. Status' => 'cust_statuscolor', + ); + my %header2style = ( + 'Cust. Status' => 'b', + ); + my %header2align = ( + 'Cust. Status' => 'c', + ); + my $cust_fields; my @cust_header; if ( @_ && $_[0] ) { @@ -247,15 +258,24 @@ sub cust_header { if $DEBUG; $cust_fields = $1; } else { - warn " no cust-fields configuration value found; using default 'Customer'" + warn " no cust-fields configuration value found; using default 'Cust. Status | Customer'" if $DEBUG; - $cust_fields = 'Customer'; + $cust_fields = 'Cust. Status | Customer'; } } @cust_header = split(/ \| /, $cust_fields); @cust_fields = map { $header2method{$_} } @cust_header; + @cust_colors = map { exists $header2colormethod{$_} + ? $header2colormethod{$_} + : '' + } + @cust_header; + @cust_styles = map { exists $header2style{$_} ? $header2style{$_} : '' } + @cust_header; + @cust_aligns = map { exists $header2align{$_} ? $header2align{$_} : 'l' } + @cust_header; #my $svc_x = shift; @cust_header; @@ -286,9 +306,9 @@ sub cust_sql_fields { map "cust_main.$_", @fields; } -=item cust_fields SVC_OBJECT [ CUST_FIELDS_VALUE ] +=item cust_fields OBJECT [ CUST_FIELDS_VALUE ] -Given a svc_ object that contains fields from cust_main (say, from a +Given an object that contains fields from cust_main (say, from a JOINed search. See httemplate/search/svc_* for examples), returns an array of customer information, or "(unlinked)" if this service is not linked to a customer. @@ -301,7 +321,7 @@ setting is supplied, the cust-fields configuration value. sub cust_fields { my $svc_x = shift; - warn "FS::svc_Common::cust_fields called for $svc_x ". + warn "FS::UI::Web::cust_fields called for $svc_x ". "(cust_fields: @cust_fields)" if $DEBUG > 1; @@ -322,6 +342,67 @@ sub cust_fields { } @cust_fields; } +=item cust_colors + +Returns an array of subroutine references (or empty strings) for returning +customer information colors. + +As with L, the fields returned are +defined by the supplied customer fields setting, or if no customer fields +setting is supplied, the cust-fields configuration value. + +=cut + +sub cust_colors { + map { + my $method = $_; + if ( $method ) { + sub { shift->$method(@_) }; + } else { + ''; + } + } @cust_colors; +} + +=item cust_styles + +Returns an array of customer information styles. + +As with L, the fields returned are +defined by the supplied customer fields setting, or if no customer fields +setting is supplied, the cust-fields configuration value. + +=cut + +sub cust_styles { + map { + if ( $_ ) { + $_; + } else { + ''; + } + } @cust_styles; +} + +=item cust_aligns + +Returns an array or scalar (depending on context) of customer information +alignments. + +As with L, the fields returned are +defined by the supplied customer fields setting, or if no customer fields +setting is supplied, the cust-fields configuration value. + +=cut + +sub cust_aligns { + if ( wantarray ) { + @cust_aligns; + } else { + join('', @cust_aligns); + } +} + ### # begin JSRPC code... ### diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 1daf6ba6f..e1708cc53 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -3886,6 +3886,8 @@ sub country_full { code2country($self->country); } +=item cust_status + =item status Returns a status string for this customer, currently: @@ -3906,17 +3908,35 @@ Returns a status string for this customer, currently: =cut -sub status { +sub status { shift->cust_status(@_); } + +sub cust_status { my $self = shift; 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; - $sth->execute( ($self->custnum) x $numnum ) or die $sth->errstr; + $sth->execute( ($self->custnum) x $numnum ) + or die "Error executing 'SELECT $sql': ". $sth->errstr; return $status if $sth->fetchrow_arrayref->[0]; } } +=item ucfirst_cust_status + +=item ucfirst_status + +Returns the status with the first character capitalized. + +=cut + +sub ucfirst_status { shift->ucfirst_cust_status(@_); } + +sub ucfirst_cust_status { + my $self = shift; + ucfirst($self->cust_status); +} + =item statuscolor Returns a hex triplet color string for this customer's status. @@ -3932,9 +3952,11 @@ use vars qw(%statuscolor); 'cancelled' => 'FF0000', #red ); -sub statuscolor { +sub statuscolor { shift->cust_statuscolor(@_); } + +sub cust_statuscolor { my $self = shift; - $statuscolor{$self->status}; + $statuscolor{$self->cust_status}; } =back diff --git a/FS/FS/cust_main_Mixin.pm b/FS/FS/cust_main_Mixin.pm index 3952f198c..fbfb8c236 100644 --- a/FS/FS/cust_main_Mixin.pm +++ b/FS/FS/cust_main_Mixin.pm @@ -2,9 +2,10 @@ package FS::cust_main_Mixin; use strict; use vars qw( $DEBUG ); +use FS::UID qw(dbh); use FS::cust_main; -$DEBUG = 0; +$DEBUG = 1; =head1 NAME @@ -138,7 +139,7 @@ this object is not linked to a customer. sub invoicing_list_emailonly_scalar { my $self = shift; - warn "invoicing_list_email only called on $self, ". + warn "invoicing_list_emailonly called on $self, ". "custnum ". $self->custnum. "\n" if $DEBUG; $self->cust_linked @@ -165,8 +166,95 @@ sub invoicing_list { : (); } +=item status + +Given an object that contains fields from cust_main (say, from a JOINed +search; see httemplate/search/ for examples), returns the equivalent of the +FS::cust_main I method, or "(unlinked)" if this object is not linked to +a customer. + +=cut + +sub cust_status { + my $self = shift; + return $self->cust_unlinked_msg unless $self->cust_linked; + + #FS::cust_main::status($self) + #false laziness w/actual cust_main::status + # (make sure FS::cust_main methods are called) + for my $status (qw( prospect active inactive suspended cancelled )) { + my $method = $status.'_sql'; + my $sql = FS::cust_main->$method();; + my $numnum = ( $sql =~ s/cust_main\.custnum/?/g ); + my $sth = dbh->prepare("SELECT $sql") or die dbh->errstr; + $sth->execute( ($self->custnum) x $numnum ) + or die "Error executing 'SELECT $sql': ". $sth->errstr; + return $status if $sth->fetchrow_arrayref->[0]; + } +} + +=item ucfirst_cust_status + +Given an object that contains fields from cust_main (say, from a JOINed +search; see httemplate/search/ for examples), returns the equivalent of the +FS::cust_main I method, or "(unlinked)" if this object is not +linked to a customer. + +=cut + +sub ucfirst_cust_status { + my $self = shift; + $self->cust_linked + ? ucfirst( $self->cust_status(@_) ) + : $self->cust_unlinked_msg; +} + +=item cust_statuscolor + +Given an object that contains fields from cust_main (say, from a JOINed +search; see httemplate/search/ for examples), returns the equivalent of the +FS::cust_main I method, or "000000" if this object is not linked to +a customer. + =cut +sub cust_statuscolor { + my $self = shift; + + $self->cust_linked + ? FS::cust_main::cust_statuscolor($self) + : '000000'; +} + +=item prospect_sql + +=item active_sql + +=item inactive_sql + +=item suspended_sql + +=item cancelled_sql + +Given an object that contains fields from cust_main (say, from a JOINed +search; see httemplate/search/ for examples), returns the equivalent of the +corresponding FS::cust_main method, or "0" if this object is not linked to +a customer. + +=cut + +foreach my $sub (qw( prospect active inactive suspended cancelled )) { + eval " + sub ${sub}_sql { + my \$self = shift; + \$self->cust_linked + ? FS::cust_main::${sub}_sql(\$self) + : '0'; + } + "; + die $@ if $@; +} + =back =head1 BUGS diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 94bb0e0ba..9565ff2d0 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -66,15 +66,16 @@ tie my %report_customers_lists, 'Tie::IxHash', $report_customers_lists{'by active trouble tickets'} = [ $fsurl. 'search/cust_main.cgi?browse=tickets', '' ] if $conf->config('ticket_system'); -tie my %report_customers_search, 'Tie::IxHash', - 'by ordering employee' => [ $fsurl. 'search/cust_main-otaker.cgi' ], -; +tie my %report_customers_search, 'Tie::IxHash'; +$report_customers_search{'by ordering employee'} = [ $fsurl. 'search/cust_main-otaker.cgi' ] + if $curuser->access_right('Configuration'); tie my %report_customers, 'Tie::IxHash', 'List customers' => [ \%report_customers_lists, 'List customers' ], - 'Search customers' => [ \%report_customers_search, 'Search customers' ], - 'Zip code distribution' => [ $fsurl.'search/report_cust_main-zip.html', 'Zip codes by number of customers' ], ; +$report_customers{'Search customers'} = [ \%report_customers_search, 'Search customers' ] + if keys %report_customers_search; +$report_customers{'Zip code distribution'} = [ $fsurl.'search/report_cust_main-zip.html', 'Zip codes by number of customers' ]; tie my %report_invoices_open, 'Tie::IxHash', 'All open invoices' => [ $fsurl.'search/cust_bill.html?OPEN_date', 'All invoices with an unpaid balance' ], diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi index ea5ae0b2b..4070069ac 100644 --- a/httemplate/graph/cust_bill_pkg.cgi +++ b/httemplate/graph/cust_bill_pkg.cgi @@ -1,103 +1,3 @@ -% -% -% -%#find first month -%my $syear = $cgi->param('start_year'); # || 1899+$curyear; -%my $smonth = $cgi->param('start_month'); # || $curmon+1; -% -%#find last month -%my $eyear = $cgi->param('end_year'); # || 1900+$curyear; -%my $emonth = $cgi->param('end_month'); # || $curmon+1; -% -%#XXX or virtual -%my( $agentnum, $sel_agent ) = ('', ''); -%if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { -% $agentnum = $1; -% $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } ); -% die "agentnum $agentnum not found!" unless $sel_agent; -%} -%my $title = $sel_agent ? $sel_agent->agent.' ' : ''; -% -%#false lazinessish w/search/cust_pkg.cgi -%my $classnum = 0; -%my @pkg_class = (); -%if ( $cgi->param('classnum') =~ /^(\d*)$/ ) { -% $classnum = $1; -% if ( $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 '' ) { -% $title .= 'Empty class '; -% @pkg_class = ( '(empty class)' ); -% } elsif ( $classnum eq '0' ) { -% @pkg_class = qsearch('pkg_class', {} ); # { 'disabled' => '' } ); -% push @pkg_class, '(empty class)'; -% } -%} -%#eslaf -% -%my $hue = 0; -%#my $hue_increment = 170; -%#my $hue_increment = 145; -%my $hue_increment = 125; -% -%my @items = (); -%my @params = (); -%my @labels = (); -%my @colors = (); -%my @links = (); -% -%my $link = "${p}search/cust_bill_pkg.cgi?nottax=1;include_comp_cust=1"; -% -%foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) { -% -% my $col_scheme = Color::Scheme->new -% ->from_hue($hue) #->from_hex($agent->color) -% ->scheme('analogic') -% ; -% my @recur_colors = (); -% my @onetime_colors = (); -% -% ### fixup the color handling for package classes... -% my $n = 0; -% -% foreach my $pkg_class ( @pkg_class ) { -% -% push @items, 'cust_bill_pkg'; -% -% -% push @labels, -% ( $sel_agent ? '' : $agent->agent.' ' ). -% ( $classnum eq '0' -% ? ( ref($pkg_class) ? $pkg_class->classname : $pkg_class ) -% : '' -% ); -% -% my $row_classnum = ref($pkg_class) ? $pkg_class->classnum : 0; -% my $row_agentnum = $agent->agentnum; -% push @params, [ 'classnum' => $row_classnum, -% 'agentnum' => $row_agentnum, -% ]; -% -% push @links, "$link;agentnum=$row_agentnum;classnum=$row_classnum;"; -% -% @recur_colors = ($col_scheme->colors)[0,4,8,1,5,9] -% unless @recur_colors; -% @onetime_colors = ($col_scheme->colors)[2,6,10,3,7,11] -% unless @onetime_colors; -% push @colors, shift @recur_colors; -% -% } -% -% $hue += $hue_increment; -% -%} -% -%#use Data::Dumper; -%#warn Dumper(\@items); -% -% <% include('elements/monthly.html', 'title' => $title. 'Sales Report (Gross)', 'graph_type' => 'Mountain', @@ -117,3 +17,105 @@ 'agentnum' => $agentnum, ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +#find first month +my $syear = $cgi->param('start_year'); # || 1899+$curyear; +my $smonth = $cgi->param('start_month'); # || $curmon+1; + +#find last month +my $eyear = $cgi->param('end_year'); # || 1900+$curyear; +my $emonth = $cgi->param('end_month'); # || $curmon+1; + +#XXX or virtual +my( $agentnum, $sel_agent ) = ('', ''); +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + die "agentnum $agentnum not found!" unless $sel_agent; +} +my $title = $sel_agent ? $sel_agent->agent.' ' : ''; + +#false lazinessish w/search/cust_pkg.cgi +my $classnum = 0; +my @pkg_class = (); +if ( $cgi->param('classnum') =~ /^(\d*)$/ ) { + $classnum = $1; + if ( $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 '' ) { + $title .= 'Empty class '; + @pkg_class = ( '(empty class)' ); + } elsif ( $classnum eq '0' ) { + @pkg_class = qsearch('pkg_class', {} ); # { 'disabled' => '' } ); + push @pkg_class, '(empty class)'; + } +} +#eslaf + +my $hue = 0; +#my $hue_increment = 170; +#my $hue_increment = 145; +my $hue_increment = 125; + +my @items = (); +my @params = (); +my @labels = (); +my @colors = (); +my @links = (); + +my $link = "${p}search/cust_bill_pkg.cgi?nottax=1;include_comp_cust=1"; + +foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) { + + my $col_scheme = Color::Scheme->new + ->from_hue($hue) #->from_hex($agent->color) + ->scheme('analogic') + ; + my @recur_colors = (); + my @onetime_colors = (); + + ### fixup the color handling for package classes... + my $n = 0; + + foreach my $pkg_class ( @pkg_class ) { + + push @items, 'cust_bill_pkg'; + + + push @labels, + ( $sel_agent ? '' : $agent->agent.' ' ). + ( $classnum eq '0' + ? ( ref($pkg_class) ? $pkg_class->classname : $pkg_class ) + : '' + ); + + my $row_classnum = ref($pkg_class) ? $pkg_class->classnum : 0; + my $row_agentnum = $agent->agentnum; + push @params, [ 'classnum' => $row_classnum, + 'agentnum' => $row_agentnum, + ]; + + push @links, "$link;agentnum=$row_agentnum;classnum=$row_classnum;"; + + @recur_colors = ($col_scheme->colors)[0,4,8,1,5,9] + unless @recur_colors; + @onetime_colors = ($col_scheme->colors)[2,6,10,3,7,11] + unless @onetime_colors; + push @colors, shift @recur_colors; + + } + + $hue += $hue_increment; + +} + +#use Data::Dumper; +#warn Dumper(\@items); + + diff --git a/httemplate/graph/money_time.cgi b/httemplate/graph/money_time.cgi index 829b1e66f..2b98af838 100644 --- a/httemplate/graph/money_time.cgi +++ b/httemplate/graph/money_time.cgi @@ -1,68 +1,3 @@ -% -% -%#find first month -%my $syear = $cgi->param('start_year'); # || 1899+$curyear; -%my $smonth = $cgi->param('start_month'); # || $curmon+1; -% -%#find last month -%my $eyear = $cgi->param('end_year'); # || 1900+$curyear; -%my $emonth = $cgi->param('end_month'); # || $curmon+1; -% -%#XXX or virtual -%my( $agentnum, $agent ) = ('', ''); -%if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { -% $agentnum = $1; -% $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); -% die "agentnum $agentnum not found!" unless $agent; -%} -%my $agentname = $agent ? $agent->agent.' ' : ''; -% -%my @items = qw( invoiced netsales credits payments receipts ); -%if ( $cgi->param('12mo') == 1 ) { -% @items = map $_.'_12mo', @items; -%} -% -%my %label = ( -% 'invoiced' => 'Gross Sales', -% 'netsales' => 'Net Sales', -% 'credits' => 'Credits', -% 'payments' => 'Gross Receipts', -% 'receipts' => 'Net Receipts', -%); -% -%my %graph_suffix = ( -% 'invoiced' => ' (invoiced)', -% 'netsales' => ' (invoiced - applied credits)', -% 'credits' => '', -% 'payments' => ' (payments)', -% 'receipts' => '/Cashflow (payments - refunds)', -%); -%my %graph_label = map { $_ => $label{$_}.$graph_suffix{$_} } keys %label; -% -%$label{$_.'_12mo'} = $label{$_}. " (previous 12 months)" -% foreach keys %label; -% -%$graph_label{$_.'_12mo'} = $graph_label{$_}. " (previous 12 months)" -% foreach keys %graph_label; -% -%my %color = ( -% 'invoiced' => '9999ff', #light blue -% 'netsales' => '0000cc', #blue -% 'credits' => 'cc0000', #red -% 'payments' => '99cc99', #light green -% 'receipts' => '00cc00', #green -%); -%$color{$_.'_12mo'} = $color{$_} -% foreach keys %color; -% -%my %link = ( -% 'invoiced' => "${p}search/cust_bill.html?agentnum=$agentnum;", -% 'credits' => "${p}search/cust_credit.html?agentnum=$agentnum;", -% 'payments' => "${p}search/cust_pay.cgi?magic=_date;agentnum=$agentnum;", -%); -%# XXX link 12mo? -% -% <% include('elements/monthly.html', 'title' => $agentname. 'Sales, Credits and Receipts Summary', @@ -79,3 +14,72 @@ 'nototal' => scalar($cgi->param('12mo')), ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +#find first month +my $syear = $cgi->param('start_year'); # || 1899+$curyear; +my $smonth = $cgi->param('start_month'); # || $curmon+1; + +#find last month +my $eyear = $cgi->param('end_year'); # || 1900+$curyear; +my $emonth = $cgi->param('end_month'); # || $curmon+1; + +#XXX or virtual +my( $agentnum, $agent ) = ('', ''); +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + die "agentnum $agentnum not found!" unless $agent; +} + +my $agentname = $agent ? $agent->agent.' ' : ''; + +my @items = qw( invoiced netsales credits payments receipts ); +if ( $cgi->param('12mo') == 1 ) { + @items = map $_.'_12mo', @items; +} + +my %label = ( + 'invoiced' => 'Gross Sales', + 'netsales' => 'Net Sales', + 'credits' => 'Credits', + 'payments' => 'Gross Receipts', + 'receipts' => 'Net Receipts', +); + +my %graph_suffix = ( + 'invoiced' => ' (invoiced)', + 'netsales' => ' (invoiced - applied credits)', + 'credits' => '', + 'payments' => ' (payments)', + 'receipts' => '/Cashflow (payments - refunds)', +); +my %graph_label = map { $_ => $label{$_}.$graph_suffix{$_} } keys %label; + +$label{$_.'_12mo'} = $label{$_}. " (previous 12 months)" + foreach keys %label; + +$graph_label{$_.'_12mo'} = $graph_label{$_}. " (previous 12 months)" + foreach keys %graph_label; + +my %color = ( + 'invoiced' => '9999ff', #light blue + 'netsales' => '0000cc', #blue + 'credits' => 'cc0000', #red + 'payments' => '99cc99', #light green + 'receipts' => '00cc00', #green +); +$color{$_.'_12mo'} = $color{$_} + foreach keys %color; + +my %link = ( + 'invoiced' => "${p}search/cust_bill.html?agentnum=$agentnum;", + 'credits' => "${p}search/cust_credit.html?agentnum=$agentnum;", + 'payments' => "${p}search/cust_pay.cgi?magic=_date;agentnum=$agentnum;", +); +# XXX link 12mo? + + diff --git a/httemplate/graph/report_cust_bill_pkg.html b/httemplate/graph/report_cust_bill_pkg.html index c81e1e0e8..c18e94d5d 100644 --- a/httemplate/graph/report_cust_bill_pkg.html +++ b/httemplate/graph/report_cust_bill_pkg.html @@ -27,3 +27,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + diff --git a/httemplate/graph/report_money_time.html b/httemplate/graph/report_money_time.html index b80696b65..652d7152e 100644 --- a/httemplate/graph/report_money_time.html +++ b/httemplate/graph/report_money_time.html @@ -31,3 +31,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html index 827a50895..54c804c1a 100644 --- a/httemplate/search/cdr.html +++ b/httemplate/search/cdr.html @@ -1,29 +1,3 @@ -% -% -%my $title = 'Call Detail Records'; -%my $hashref = {}; -%my $count_query = 'SELECT COUNT(*) FROM cdr'; -% -%#process params for CDR search, populate $hashref... -%# and fixup $count_query -% -%if ( $cgi->param('freesidestatus') eq 'NULL' ) { -% -% my $title = "Unprocessed $title"; -% $hashref->{'freesidestatus'} = ''; # Record.pm will take care of it -% #$count_query .= " AND ( freesidestatus IS NULL OR freesidestatus = '' )"; -% $count_query .= " WHERE ( freesidestatus IS NULL OR freesidestatus = '' )"; -% -%} elsif ( $cgi->param('freesidestatus') =~ /^([\w ]+)$/ ) { -% -% my $title = "Processed $title"; -% $hashref->{'freesidestatus'} = $1; -% #$count_query .= " AND freesidestatus = '$1'"; -% $count_query .= " WHERE freesidestatus = '$1'"; -% -%} -% -% <% include( 'elements/search.html', 'title' => $title, 'name' => 'call detail records', @@ -36,3 +10,32 @@ # processing, etc. ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); + +my $title = 'Call Detail Records'; +my $hashref = {}; +my $count_query = 'SELECT COUNT(*) FROM cdr'; + +#process params for CDR search, populate $hashref... +# and fixup $count_query + +if ( $cgi->param('freesidestatus') eq 'NULL' ) { + + my $title = "Unprocessed $title"; + $hashref->{'freesidestatus'} = ''; # Record.pm will take care of it + #$count_query .= " AND ( freesidestatus IS NULL OR freesidestatus = '' )"; + $count_query .= " WHERE ( freesidestatus IS NULL OR freesidestatus = '' )"; + +} elsif ( $cgi->param('freesidestatus') =~ /^([\w ]+)$/ ) { + + my $title = "Processed $title"; + $hashref->{'freesidestatus'} = $1; + #$count_query .= " AND freesidestatus = '$1'"; + $count_query .= " WHERE freesidestatus = '$1'"; + +} + + diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 894ddad51..b65608eab 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -1,166 +1,3 @@ -% -% -% 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 ) = ( '' ); -% my($begin, $end) = ( '', '' ); -% my($agentnum) = ( '' ); -% my($open, $days) = ( '', '' ); -% if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { -% $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') -% # || $cgi->param('beginning') || $cgi->param('ending') -% # || $cgi->keywords -% # ) -% #{ -% -% #some false laziness w/cust_bill::re_X -% my @where; -% my $orderby = 'ORDER BY cust_bill._date'; -% -% if ( $cgi->param('beginning') -% && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { -% $begin = str2time($1); -% push @where, "cust_bill._date >= $begin"; -% } -% if ( $cgi->param('ending') -% && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { -% $end = str2time($1) + 86399; -% push @where, "cust_bill._date < $end"; -% } -% -% if ( $cgi->param('begin') =~ /^(\d+)$/ ) { -% $begin = $1; -% push @where, "cust_bill._date >= $begin"; -% } -% if ( $cgi->param('end') =~ /^(\d+)$/ ) { -% $end = $1; -% push @where, "cust_bill._date < $end"; -% } -% -% if ( $cgi->param('invnum_min') =~ /^\s*(\d+)\s*$/ ) { -% push @where, "cust_bill.invnum >= $1"; -% } -% if ( $cgi->param('invnum_max') =~ /^\s*(\d+)\s*$/ ) { -% push @where, "cust_bill.invnum <= $1"; -% } -% -% if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { -% $agentnum = $1; -% push @where, "cust_main.agentnum = $agentnum"; -% } -% -% my $owed = -% "charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay -% WHERE cust_bill_pay.invnum = cust_bill.invnum ) -% - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill -% WHERE cust_credit_bill.invnum = cust_bill.invnum )"; -% -% if ( $cgi->param('open') ) { -% push @where, "0 != $owed"; -% $open = 1; -% } -% -% my($query) = $cgi->keywords; -% if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) { -% ($open, $days, my $field) = ($1, $2, $3); -% $field = "_date" if $field eq 'date'; -% $orderby = "ORDER BY cust_bill.$field"; -% push @where, "0 != $owed" if $open; -% 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) : ''; -% -% if ( $cgi->param('newest_percust') ) { -% $distinct = 'DISTINCT ON ( cust_bill.custnum )'; -% $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; -% #$count_query = "SELECT 'N/A', 'N/A', 'N/A'"; #XXXXXXX fix -% $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"; -% } -% -% unless ( $count_query ) { -% $count_query = "SELECT COUNT(*), sum(charged), sum($owed)"; -% $count_addl = [ '$%.2f total invoiced', -% '$%.2f total outstanding balance', -% ]; -% } -% $count_query .= " FROM cust_bill $join_cust_main $extra_sql"; -% -% $sql_query = { -% 'table' => 'cust_bill', -% 'addl_from' => $join_cust_main, -% 'hashref' => {}, -% 'select' => "$distinct ". join(', ', -% 'cust_bill.*', -% #( map "cust_main.$_", qw(custnum last first company) ), -% 'cust_main.custnum as cust_main_custnum', -% FS::UI::Web::cust_sql_fields(), -% "$owed as owed", -% ), -% 'extra_sql' => "$extra_sql $orderby" -% }; -% -% } -% -% my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; -% my $clink = sub { -% my $cust_bill = shift; -% $cust_bill->cust_main_custnum -% ? [ "${p}view/cust_main.cgi?", 'custnum' ] -% : ''; -% }; -% -% my $conf = new FS::Conf; -% my $money_char = $conf->config('money_char') || '$'; -% -% my $html_init = join("\n", map { -% ( my $action = $_ ) =~ s/_$//; -% include('/elements/progress-init.html', -% $_.'form', -% [ 'begin', 'end', 'agentnum', 'open', 'days', 'newest_percust' ], -% "../misc/${_}invoices.cgi", -% { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but... -% $_, #key -% ), -% qq!
!, -% qq!!, -% qq!!, -% qq!!, -% qq!!, -% qq!!, -% qq!
! -% } qw( print_ email_ fax_ ) ); -% -% my $menubar = [ -% 'Main menu' => $p, -% 'Print these invoices' => -% "javascript:print_process()", -% 'Email these invoices' => -% "javascript:email_process()", -% ]; -% -% push @$menubar, 'Fax these invoices' => -% "javascript:fax_process()" -% if $conf->exists('hylafax'); -% -% <% include( 'elements/search.html', 'title' => 'Invoice Search Results', 'html_init' => $html_init, @@ -183,14 +20,197 @@ sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, ], - 'align' => 'rrrrll', + 'align' => 'rrrr'.FS::UI::Web::cust_aligns(), 'links' => [ $link, $link, $link, $link, - ( map { $clink } FS::UI::Web::cust_header() ), + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), ], + 'color' => [ + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], + ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List invoices'); + +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 ) = ( '' ); +my($begin, $end) = ( '', '' ); +my($agentnum) = ( '' ); +my($open, $days) = ( '', '' ); +if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { + $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') +# || $cgi->param('beginning') || $cgi->param('ending') +# || $cgi->keywords +# ) +#{ + + #some false laziness w/cust_bill::re_X + my @where; + my $orderby = 'ORDER BY cust_bill._date'; + + if ( $cgi->param('beginning') + && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $begin = str2time($1); + push @where, "cust_bill._date >= $begin"; + } + if ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $end = str2time($1) + 86399; + push @where, "cust_bill._date < $end"; + } + + if ( $cgi->param('begin') =~ /^(\d+)$/ ) { + $begin = $1; + push @where, "cust_bill._date >= $begin"; + } + if ( $cgi->param('end') =~ /^(\d+)$/ ) { + $end = $1; + push @where, "cust_bill._date < $end"; + } + + if ( $cgi->param('invnum_min') =~ /^\s*(\d+)\s*$/ ) { + push @where, "cust_bill.invnum >= $1"; + } + if ( $cgi->param('invnum_max') =~ /^\s*(\d+)\s*$/ ) { + push @where, "cust_bill.invnum <= $1"; + } + + if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + push @where, "cust_main.agentnum = $agentnum"; + } + + my $owed = + "charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill_pay.invnum = cust_bill.invnum ) + - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_credit_bill.invnum = cust_bill.invnum )"; + + if ( $cgi->param('open') ) { + push @where, "0 != $owed"; + $open = 1; + } + + my($query) = $cgi->keywords; + if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) { + ($open, $days, my $field) = ($1, $2, $3); + $field = "_date" if $field eq 'date'; + $orderby = "ORDER BY cust_bill.$field"; + push @where, "0 != $owed" if $open; + 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) : ''; + + if ( $cgi->param('newest_percust') ) { + $distinct = 'DISTINCT ON ( cust_bill.custnum )'; + $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; + #$count_query = "SELECT 'N/A', 'N/A', 'N/A'"; #XXXXXXX fix + $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"; + } + + unless ( $count_query ) { + $count_query = "SELECT COUNT(*), sum(charged), sum($owed)"; + $count_addl = [ '$%.2f total invoiced', + '$%.2f total outstanding balance', + ]; + } + $count_query .= " FROM cust_bill $join_cust_main $extra_sql"; + + $sql_query = { + 'table' => 'cust_bill', + 'addl_from' => $join_cust_main, + 'hashref' => {}, + 'select' => "$distinct ". join(', ', + 'cust_bill.*', + #( map "cust_main.$_", qw(custnum last first company) ), + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields(), + "$owed as owed", + ), + 'extra_sql' => "$extra_sql $orderby" + }; + +} + +my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; +my $clink = sub { + my $cust_bill = shift; + $cust_bill->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my $html_init = join("\n", map { + ( my $action = $_ ) =~ s/_$//; + include('/elements/progress-init.html', + $_.'form', + [ 'begin', 'end', 'agentnum', 'open', 'days', 'newest_percust' ], + "../misc/${_}invoices.cgi", + { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but... + $_, #key + ), + qq!
!, + qq!!, + qq!!, + qq!!, + qq!!, + qq!!, + qq!
! +} qw( print_ email_ fax_ ) ); + +my $menubar = [ + 'Main menu' => $p, + 'Print these invoices' => + "javascript:print_process()", + 'Email these invoices' => + "javascript:email_process()", + ]; + +push @$menubar, 'Fax these invoices' => + "javascript:fax_process()" + if $conf->exists('hylafax'); + + diff --git a/httemplate/search/cust_bill_event.cgi b/httemplate/search/cust_bill_event.cgi index 7c31c257d..ada7e4362 100644 --- a/httemplate/search/cust_bill_event.cgi +++ b/httemplate/search/cust_bill_event.cgi @@ -1,5 +1,72 @@ +<% include( 'elements/search.html', + 'title' => $title, + 'html_init' => $html_init, + 'menubar' => $menubar, + 'name' => 'billing events', + 'query' => $sql_query, + 'count_query' => $count_sql, + 'header' => [ 'Event', + 'Date', + 'Status', + #'Inv #', 'Inv Date', 'Cust #', + 'Invoice', + FS::UI::Web::cust_header(), + ], + 'fields' => [ + 'event', + sub { time2str("%b %d %Y %T", $_[0]->_date) }, + sub { + #my $cust_bill_event = shift; + my $status = $_[0]->status; + $status .= ': '.$_[0]->statustext + if $_[0]->statustext; + $status; + }, + sub { + #my $cust_bill_event = shift; + 'Invoice #'. $_[0]->invnum. + ' ('. + time2str("%D", $_[0]->cust_bill_date). + ')'; + }, + \&FS::UI::Web::cust_fields, + ], + 'align' => 'lrlr'.FS::UI::Web::cust_aligns(), + 'links' => [ + '', + '', + '', + sub { + my $part_bill_event = shift; + my $template = $part_bill_event->templatename; + $template .= '-' if $template; + [ "${p}view/cust_bill.cgi?$template", 'invnum']; + }, + ( map { $_ ne 'Cust. Status' ? $link_cust : '' } + FS::UI::Web::cust_header() + ), + ], + 'color' => [ + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], + ) +%> <%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Billing event reports'); + my $title = $cgi->param('failed') ? 'Failed invoice events' : 'Invoice events'; @@ -92,50 +159,3 @@ my $link_cust = sub { }; -<% include( 'elements/search.html', - 'title' => $title, - 'html_init' => $html_init, - 'menubar' => $menubar, - 'name' => 'billing events', - 'query' => $sql_query, - 'count_query' => $count_sql, - 'header' => [ 'Event', - 'Date', - 'Status', - #'Inv #', 'Inv Date', 'Cust #', - 'Invoice', - FS::UI::Web::cust_header(), - ], - 'fields' => [ - 'event', - sub { time2str("%b %d %Y %T", $_[0]->_date) }, - sub { - #my $cust_bill_event = shift; - my $status = $_[0]->status; - $status .= ': '.$_[0]->statustext - if $_[0]->statustext; - $status; - }, - sub { - #my $cust_bill_event = shift; - 'Invoice #'. $_[0]->invnum. - ' ('. - time2str("%D", $_[0]->cust_bill_date). - ')'; - }, - \&FS::UI::Web::cust_fields, - ], - 'links' => [ - '', - '', - '', - sub { - my $part_bill_event = shift; - my $template = $part_bill_event->templatename; - $template .= '-' if $template; - [ "${p}view/cust_bill.cgi?$template", 'invnum']; - }, - ( map { $link_cust } FS::UI::Web::cust_header() ), - ], - ) -%> diff --git a/httemplate/search/cust_bill_event.html b/httemplate/search/cust_bill_event.html index 2aa2ccc6c..334bda3d3 100755 --- a/httemplate/search/cust_bill_event.html +++ b/httemplate/search/cust_bill_event.html @@ -56,3 +56,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Billing event reports'); + + diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index b5289d713..17b4bc240 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -1,130 +1,3 @@ -% -% -%my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -% -%my $join_cust = " -% JOIN cust_bill USING ( invnum ) -% LEFT JOIN cust_main USING ( custnum ) -%"; -% -%my $join_pkg = " -% LEFT JOIN cust_pkg USING ( pkgnum ) -% LEFT JOIN part_pkg USING ( pkgpart ) -%"; -% -%my $where = " WHERE _date >= $beginning AND _date <= $ending "; -% -%$where .= " AND payby != 'COMP' " -% unless $cgi->param('include_comp_cust'); -% -%if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { -% $where .= " AND agentnum = $1 "; -%} -% -%if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { -% if ( $1 == 0 ) { -% $where .= " AND classnum IS NULL "; -% } else { -% $where .= " AND classnum = $1 "; -% } -%} -% -%if ( $cgi->param('out') ) { -% -% $where .= " -% AND 0 = ( -% SELECT COUNT(*) FROM cust_main_county -% WHERE ( cust_main_county.county = cust_main.county -% OR ( cust_main_county.county IS NULL AND cust_main.county = '' ) -% OR ( cust_main_county.county = '' AND cust_main.county IS NULL) -% OR ( cust_main_county.county IS NULL AND cust_main.county IS NULL) -% ) -% AND ( cust_main_county.state = cust_main.state -% OR ( cust_main_county.state IS NULL AND cust_main.state = '' ) -% OR ( cust_main_county.state = '' AND cust_main.state IS NULL ) -% OR ( cust_main_county.state IS NULL AND cust_main.state IS NULL ) -% ) -% AND cust_main_county.country = cust_main.country -% AND cust_main_county.tax > 0 -% ) -% "; -% -%} elsif ( $cgi->param('country' ) ) { -% -% my $county = dbh->quote( $cgi->param('county') ); -% my $state = dbh->quote( $cgi->param('state') ); -% my $country = dbh->quote( $cgi->param('country') ); -% $where .= " -% AND ( county = $county OR $county = '' ) -% AND ( state = $state OR $state = '' ) -% AND country = $country -% "; -% $where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') ) -% if $cgi->param('taxclass'); -% -%} -% -%$where .= ' AND pkgnum != 0' if $cgi->param('nottax'); -% -%$where .= ' AND pkgnum = 0' if $cgi->param('istax'); -% -%$where .= " AND tax = 'Y'" if $cgi->param('cust_tax'); -% -%my $count_query; -%if ( $cgi->param('pkg_tax') ) { -% -% $count_query = -% "SELECT COUNT(*), SUM( -% ( CASE WHEN part_pkg.setuptax = 'Y' -% THEN cust_bill_pkg.setup -% ELSE 0 -% END -% ) -% + -% ( CASE WHEN part_pkg.recurtax = 'Y' -% THEN cust_bill_pkg.recur -% ELSE 0 -% END -% ) -% ) -% "; -% -% $where .= " AND ( -% ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) -% OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) -% ) -% AND ( tax != 'Y' OR tax IS NULL ) -% "; -% -%} else { -% -% $count_query = -% "SELECT COUNT(*), SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; -% -%} -%$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; -% -%my $query = { -% 'table' => 'cust_bill_pkg', -% 'addl_from' => "$join_cust $join_pkg", -% 'hashref' => {}, -% 'select' => join(', ', -% 'cust_bill_pkg.*', -% 'cust_bill._date', -% 'part_pkg.pkg', -% 'cust_main.custnum', -% FS::UI::Web::cust_sql_fields(), -% ), -% 'extra_sql' => $where, -%}; -% -%my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; -%my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; -% -%my $conf = new FS::Conf; -%my $money_char = $conf->config('money_char') || '$'; -% -% <% include( 'elements/search.html', 'title' => 'Line items', 'name' => 'line items', @@ -161,9 +34,158 @@ '', $ilink, $ilink, - ( map { $clink } FS::UI::Web::cust_header() ), + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), ], - 'align' => 'rlrrrc', + 'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], ) %> +<%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + +my $join_cust = " + JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) +"; + +my $join_pkg = " + LEFT JOIN cust_pkg USING ( pkgnum ) + LEFT JOIN part_pkg USING ( pkgpart ) +"; + +my $where = " WHERE _date >= $beginning AND _date <= $ending "; + +$where .= " AND payby != 'COMP' " + unless $cgi->param('include_comp_cust'); + +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $where .= " AND agentnum = $1 "; +} + +if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { + if ( $1 == 0 ) { + $where .= " AND classnum IS NULL "; + } else { + $where .= " AND classnum = $1 "; + } +} + +if ( $cgi->param('out') ) { + + $where .= " + AND 0 = ( + SELECT COUNT(*) FROM cust_main_county + WHERE ( cust_main_county.county = cust_main.county + OR ( cust_main_county.county IS NULL AND cust_main.county = '' ) + OR ( cust_main_county.county = '' AND cust_main.county IS NULL) + OR ( cust_main_county.county IS NULL AND cust_main.county IS NULL) + ) + AND ( cust_main_county.state = cust_main.state + OR ( cust_main_county.state IS NULL AND cust_main.state = '' ) + OR ( cust_main_county.state = '' AND cust_main.state IS NULL ) + OR ( cust_main_county.state IS NULL AND cust_main.state IS NULL ) + ) + AND cust_main_county.country = cust_main.country + AND cust_main_county.tax > 0 + ) + "; + +} elsif ( $cgi->param('country' ) ) { + + my $county = dbh->quote( $cgi->param('county') ); + my $state = dbh->quote( $cgi->param('state') ); + my $country = dbh->quote( $cgi->param('country') ); + $where .= " + AND ( county = $county OR $county = '' ) + AND ( state = $state OR $state = '' ) + AND country = $country + "; + $where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') ) + if $cgi->param('taxclass'); + +} + +$where .= ' AND pkgnum != 0' if $cgi->param('nottax'); + +$where .= ' AND pkgnum = 0' if $cgi->param('istax'); + +$where .= " AND tax = 'Y'" if $cgi->param('cust_tax'); + +my $count_query; +if ( $cgi->param('pkg_tax') ) { + + $count_query = + "SELECT COUNT(*), SUM( + ( CASE WHEN part_pkg.setuptax = 'Y' + THEN cust_bill_pkg.setup + ELSE 0 + END + ) + + + ( CASE WHEN part_pkg.recurtax = 'Y' + THEN cust_bill_pkg.recur + ELSE 0 + END + ) + ) + "; + + $where .= " AND ( + ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) + OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) + ) + AND ( tax != 'Y' OR tax IS NULL ) + "; + +} else { + + $count_query = + "SELECT COUNT(*), SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; + +} +$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; + +my $query = { + 'table' => 'cust_bill_pkg', + 'addl_from' => "$join_cust $join_pkg", + 'hashref' => {}, + 'select' => join(', ', + 'cust_bill_pkg.*', + 'cust_bill._date', + 'part_pkg.pkg', + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => $where, +}; + +my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; +my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + + diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index c52394a5b..e4975c8de 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -1,55 +1,3 @@ -% -% my $title = 'Credit Search Results'; -% #my( $count_query, $sql_query ); -% -% my @search = (); -% -% if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { -% push @search, "cust_credit.otaker = '$1'"; -% } -% -% if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { -% push @search, "agentnum = $1"; -% my $agent = qsearchs('agent', { 'agentnum' => $1 } ); -% die "unknown agentnum $1" unless $agent; -% $title = $agent->agent. " $title"; -% } -% -% my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -% push @search, "_date >= $beginning ", -% "_date <= $ending"; -% -% push @search, FS::UI::Web::parse_lt_gt($cgi, 'amount' ); -% -% #here is the agent virtualization -% push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; -% -% my $where = 'WHERE '. join(' AND ', @search); -% -% my $count_query = 'SELECT COUNT(*), SUM(amount) '. -% 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. -% $where; -% -% my $sql_query = { -% 'table' => 'cust_credit', -% 'select' => join(', ', -% 'cust_credit.*', -% 'cust_main.custnum as cust_main_custnum', -% FS::UI::Web::cust_sql_fields(), -% ), -% 'hashref' => {}, -% 'extra_sql' => $where, -% 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', -% }; -% -% my $clink = sub { -% my $cust_bill = shift; -% $cust_bill->cust_main_custnum -% ? [ "${p}view/cust_main.cgi?", 'custnum' ] -% : ''; -% }; -% -% <% include( 'elements/search.html', 'title' => $title, 'name' => 'credits', @@ -72,13 +20,85 @@ 'reason', ], #'align' => 'rrrllll', - 'align' => 'rr', + 'align' => 'rr'.FS::UI::Web::cust_aligns().'ll', 'links' => [ '', '', - ( map { $clink } FS::UI::Web::cust_header() ), + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), '', '', ], + 'color' => [ + '', + '', + FS::UI::Web::cust_colors(), + '', + '', + ], + 'style' => [ + '', + '', + FS::UI::Web::cust_styles(), + '', + '', + ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $title = 'Credit Search Results'; +#my( $count_query, $sql_query ); + +my @search = (); + +if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { + push @search, "cust_credit.otaker = '$1'"; +} + +if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { + push @search, "agentnum = $1"; + my $agent = qsearchs('agent', { 'agentnum' => $1 } ); + die "unknown agentnum $1" unless $agent; + $title = $agent->agent. " $title"; +} + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +push @search, "_date >= $beginning ", + "_date <= $ending"; + +push @search, FS::UI::Web::parse_lt_gt($cgi, 'amount' ); + +#here is the agent virtualization +push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; + +my $where = 'WHERE '. join(' AND ', @search); + +my $count_query = 'SELECT COUNT(*), SUM(amount) '. + 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. + $where; + +my $sql_query = { + 'table' => 'cust_credit', + 'select' => join(', ', + 'cust_credit.*', + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'hashref' => {}, + 'extra_sql' => $where, + 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', +}; + + my $clink = sub { + my $cust_bill = shift; + $cust_bill->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; + }; + + diff --git a/httemplate/search/cust_main-otaker.cgi b/httemplate/search/cust_main-otaker.cgi index 210172fc0..0c252e44b 100755 --- a/httemplate/search/cust_main-otaker.cgi +++ b/httemplate/search/cust_main-otaker.cgi @@ -23,3 +23,9 @@ Search for Order taker: <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + + diff --git a/httemplate/search/cust_main-zip.html b/httemplate/search/cust_main-zip.html index 9790c0fef..56df924bc 100644 --- a/httemplate/search/cust_main-zip.html +++ b/httemplate/search/cust_main-zip.html @@ -1,89 +1,3 @@ -% -% -%# XXX link to customers -% -%my @where = (); -% -%# select status -% -%if ( $cgi->param('status') =~ /^(prospect|uncancel|active|susp|cancel)$/ ) { -% my $method = $1.'_sql'; -% push @where, FS::cust_main->$method(); -%} -% -%# select agent -%# XXX this needs to be virtualized by agent too (like lots of stuff) -% -%my $agentnum = ''; -%if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { -% $agentnum = $1; -% push @where, "cust_main.agentnum = $agentnum"; -%} -%my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; -% -%# bill zip vs ship zip -% -%sub fieldorempty { -% my $field = shift; -% "CASE WHEN $field IS NULL THEN '' ELSE $field END"; -%} -% -%sub strip_plus4 { -% my $field = shift; -% "CASE WHEN $field is NULL -% THEN '' -% ELSE CASE WHEN $field LIKE '_____-____' -% THEN SUBSTRING($field FROM 1 FOR 5) -% ELSE $field -% END -% END"; -%} -% -%my( $zip, $czip); -%if ( $cgi->param('column') eq 'ship_zip' ) { -% -% my $casewhen_noship = -% "CASE WHEN ( ship_last IS NULL OR ship_last = '' ) THEN "; -% -% $czip = "$casewhen_noship zip ELSE ship_zip END"; -% -% if ( $cgi->param('ignore_plus4') ) { -% $zip = $casewhen_noship. strip_plus4('zip'). -% " ELSE ". strip_plus4('ship_zip'). ' END'; -% -% } else { -% $zip = $casewhen_noship. fieldorempty('zip'). -% " ELSE ". fieldorempty('ship_zip'). ' END'; -% } -% -%} else { -% -% $czip = 'zip'; -% -% if ( $cgi->param('ignore_plus4') ) { -% $zip = strip_plus4('zip'); -% } else { -% $zip = fieldorempty('zip'); -% } -% -%} -% -%# construct the queries and send 'em off -% -%my $sql_query = -% "SELECT $zip AS zipcode, -% COUNT(*) AS num_cust -% FROM cust_main -% $where -% GROUP BY zipcode -% ORDER BY num_cust DESC -% "; -% -%my $count_sql = "select count(distinct $czip) from cust_main $where"; -% -%# XXX should link... -% -% <% include( 'elements/search.html', 'title' => 'Zip code Search Results', 'name' => 'zip codes', @@ -94,3 +8,92 @@ 'links' => [ '', sub { 'somewhere'; } ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List zip codes'); + +# XXX link to customers + +my @where = (); + +# select status + +if ( $cgi->param('status') =~ /^(prospect|uncancel|active|susp|cancel)$/ ) { + my $method = $1.'_sql'; + push @where, FS::cust_main->$method(); +} + +# select agent +# XXX this needs to be virtualized by agent too (like lots of stuff) + +my $agentnum = ''; +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + push @where, "cust_main.agentnum = $agentnum"; +} +my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; + +# bill zip vs ship zip + +sub fieldorempty { + my $field = shift; + "CASE WHEN $field IS NULL THEN '' ELSE $field END"; +} + +sub strip_plus4 { + my $field = shift; + "CASE WHEN $field is NULL + THEN '' + ELSE CASE WHEN $field LIKE '_____-____' + THEN SUBSTRING($field FROM 1 FOR 5) + ELSE $field + END + END"; +} + +my( $zip, $czip); +if ( $cgi->param('column') eq 'ship_zip' ) { + + my $casewhen_noship = + "CASE WHEN ( ship_last IS NULL OR ship_last = '' ) THEN "; + + $czip = "$casewhen_noship zip ELSE ship_zip END"; + + if ( $cgi->param('ignore_plus4') ) { + $zip = $casewhen_noship. strip_plus4('zip'). + " ELSE ". strip_plus4('ship_zip'). ' END'; + + } else { + $zip = $casewhen_noship. fieldorempty('zip'). + " ELSE ". fieldorempty('ship_zip'). ' END'; + } + +} else { + + $czip = 'zip'; + + if ( $cgi->param('ignore_plus4') ) { + $zip = strip_plus4('zip'); + } else { + $zip = fieldorempty('zip'); + } + +} + +# construct the queries and send 'em off + +my $sql_query = + "SELECT $zip AS zipcode, + COUNT(*) AS num_cust + FROM cust_main + $where + GROUP BY zipcode + ORDER BY num_cust DESC + "; + +my $count_sql = "select count(distinct $czip) from cust_main $where"; + +# XXX should link... + + diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index e15447ae0..e87fe36d7 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -1,3 +1,6 @@ +%die "access denied" +% unless $FS::CurrentUser::CurrentUser->access_right('List customers'); +% %my $conf = new FS::Conf; %my $maxrecords = $conf->config('maxsearchrecordsperpage'); % @@ -82,6 +85,8 @@ % } % % if ( $cgi->param('otaker_on') ) { +% die "access denied" +% unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); % $cgi->param('otaker') =~ /^(\w{1,32})$/ or eidiot "Illegal otaker\n"; % $search{otaker} = $1; % } elsif ( $cgi->param('agentnum_on') ) { @@ -305,7 +310,8 @@ % % print "

". $pager. include('/elements/table-grid.html'). < -% +% # +% Status % (bill) name % company %END @@ -369,10 +375,12 @@ % ? qq!$company! % : ' '; % - +% my $status = $cust_main->status; +% my $statuscol = $FS::cust_main::statuscolor{$status}; - ><% $custnum %> + ><% $custnum %> + >><% ucfirst($status) %> ><% "$last, $first" %> ><% $pcompany %> % diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html index 4f7508447..8ef1ecb9a 100755 --- a/httemplate/search/cust_main.html +++ b/httemplate/search/cust_main.html @@ -39,4 +39,9 @@ +<%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List customers'); + + diff --git a/httemplate/search/cust_pay.cgi b/httemplate/search/cust_pay.cgi index 199c66d81..6215b4b2f 100755 --- a/httemplate/search/cust_pay.cgi +++ b/httemplate/search/cust_pay.cgi @@ -1,167 +1,3 @@ -% -% my $title = 'Payment Search Results'; -% my( $count_query, $sql_query ); -% if ( $cgi->param('magic') ) { -% -% my @search = (); -% my $orderby; -% if ( $cgi->param('magic') eq '_date' ) { -% -% -% if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { -% push @search, "agentnum = $1"; # $search{'agentnum'} = $1; -% my $agent = qsearchs('agent', { 'agentnum' => $1 } ); -% die "unknown agentnum $1" unless $agent; -% $title = $agent->agent. " $title"; -% } -% -% if ( $cgi->param('payby') ) { -% $cgi->param('payby') =~ -% /^(CARD|CHEK|BILL|PREP|CASH|WEST|MCRD)(-(VisaMC|Amex|Discover|Maestro))?$/ -% or die "illegal payby ". $cgi->param('payby'); -% push @search, "cust_pay.payby = '$1'"; -% if ( $3 ) { -% -% my $cardtype = $3; -% -% my $search; -% if ( $cardtype eq 'VisaMC' ) { -% #avoid posix regexes for portability -% $search = -% " ( ( substring(cust_pay.payinfo from 1 for 1) = '4' ". -% " AND substring(cust_pay.payinfo from 1 for 4) != '4936' ". -% " AND substring(cust_pay.payinfo from 1 for 6) ". -% " NOT SIMILAR TO '49030[2-9]' ". -% " AND substring(cust_pay.payinfo from 1 for 6) ". -% " NOT SIMILAR TO '49033[5-9]' ". -% " AND substring(cust_pay.payinfo from 1 for 6) ". -% " NOT SIMILAR TO '49110[1-2]' ". -% " AND substring(cust_pay.payinfo from 1 for 6) ". -% " NOT SIMILAR TO '49117[4-9]' ". -% " AND substring(cust_pay.payinfo from 1 for 6) ". -% " NOT SIMILAR TO '49118[1-2]' ". -% " )". -% " OR substring(cust_pay.payinfo from 1 for 2) = '51' ". -% " OR substring(cust_pay.payinfo from 1 for 2) = '52' ". -% " OR substring(cust_pay.payinfo from 1 for 2) = '53' ". -% " OR substring(cust_pay.payinfo from 1 for 2) = '54' ". -% " OR substring(cust_pay.payinfo from 1 for 2) = '54' ". -% " OR substring(cust_pay.payinfo from 1 for 2) = '55' ". -% " OR substring(cust_pay.payinfo from 1 for 2) = '36' ". #Diner's int'l processed as Visa/MC inside US -% " ) "; -% } elsif ( $cardtype eq 'Amex' ) { -% $search = -% " ( substring(cust_pay.payinfo from 1 for 2 ) = '34' ". -% " OR substring(cust_pay.payinfo from 1 for 2 ) = '37' ". -% " ) "; -% } elsif ( $cardtype eq 'Discover' ) { -% $search = -% " ( substring(cust_pay.payinfo from 1 for 4 ) = '6011' ". -% " OR substring(cust_pay.payinfo from 1 for 2 ) = '65' ". -% " OR substring(cust_pay.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN -% " ) "; -% } elsif ( $cardtype eq 'Maestro' ) { -% $search = -% " ( substring(cust_pay.payinfo from 1 for 2 ) = '63' ". -% " OR substring(cust_pay.payinfo from 1 for 2 ) = '67' ". -% " OR substring(cust_pay.payinfo from 1 for 6 ) = '564182' ". -% " OR substring(cust_pay.payinfo from 1 for 4 ) = '4936' ". -% " OR substring(cust_pay.payinfo from 1 for 6 ) ". -% " SIMILAR TO '49030[2-9]' ". -% " OR substring(cust_pay.payinfo from 1 for 6 ) ". -% " SIMILAR TO '49033[5-9]' ". -% " OR substring(cust_pay.payinfo from 1 for 6 ) ". -% " SIMILAR TO '49110[1-2]' ". -% " OR substring(cust_pay.payinfo from 1 for 6 ) ". -% " SIMILAR TO '49117[4-9]' ". -% " OR substring(cust_pay.payinfo from 1 for 6 ) ". -% " SIMILAR TO '49118[1-2]' ". -% " ) "; -% } else { -% die "unknown card type $cardtype"; -% } -% -% my $masksearch = $search; -% $masksearch =~ s/cust_pay\.payinfo/cust_pay.paymask/gi; -% -% push @search, -% "( $search OR ( cust_pay.paymask IS NOT NULL AND $masksearch ) )"; -% -% } -% } -% -% my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -% push @search, "_date >= $beginning ", -% "_date <= $ending"; -% -% push @search, FS::UI::Web::parse_lt_gt($cgi, 'paid' ); -% -% $orderby = '_date'; -% -% } elsif ( $cgi->param('magic') eq 'paybatch' ) { -% -% $cgi->param('paybatch') =~ /^([\w\/\:\-\.]+)$/ -% or die "illegal paybatch: ". $cgi->param('paybatch'); -% -% push @search, "paybatch = '$1'"; -% -% $orderby = "LOWER(company || ' ' || last || ' ' || first )"; -% -% } else { -% die "unknown search magic: ". $cgi->param('magic'); -% } -% -% #here is the agent virtualization -% push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; -% -% my $search = ' WHERE '. join(' AND ', @search); -% -% $count_query = "SELECT COUNT(*), SUM(paid) ". -% "FROM cust_pay LEFT JOIN cust_main USING ( custnum )". -% $search; -% -% $sql_query = { -% 'table' => 'cust_pay', -% 'select' => join(', ', -% 'cust_pay.*', -% 'cust_main.custnum as cust_main_custnum', -% FS::UI::Web::cust_sql_fields(), -% ), -% 'hashref' => {}, -% 'extra_sql' => "$search ORDER BY $orderby", -% 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', -% }; -% -% } else { -% -% $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo"; -% my $payinfo = $1; -% -% $cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby"; -% my $payby = $1; -% -% $count_query = "SELECT COUNT(*), SUM(paid) FROM cust_pay". -% " WHERE payinfo = '$payinfo' AND payby = '$payby'". -% " AND ". $FS::CurrentUser::CurrentUser->agentnums_sql; -% -% $sql_query = { -% 'table' => 'cust_pay', -% 'hashref' => { 'payinfo' => $payinfo, -% 'payby' => $payby }, -% 'extra_sql' => $FS::CurrentUser::CurrentUser->agentnums_sql. -% " ORDER BY _date", -% }; -% -% } -% -% my $link = sub { -% my $cust_pay = shift; -% $cust_pay->cust_main_custnum -% ? [ "${p}view/cust_main.cgi?", 'custnum' ] -% : ''; -% }; -% -% <% include( 'elements/search.html', 'title' => $title, 'name' => 'payments', @@ -199,12 +35,194 @@ \&FS::UI::Web::cust_fields, ], #'align' => 'lrrrll', - 'align' => 'rrr', + 'align' => 'rrr'.FS::UI::Web::cust_aligns(), 'links' => [ '', '', '', - ( map { $link } FS::UI::Web::cust_header() ), + ( map { $_ ne 'Cust. Status' ? $link : '' } + FS::UI::Web::cust_header() + ), ], + 'color' => [ + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + FS::UI::Web::cust_styles(), + ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $title = 'Payment Search Results'; +my( $count_query, $sql_query ); +if ( $cgi->param('magic') ) { + + my @search = (); + my $orderby; + if ( $cgi->param('magic') eq '_date' ) { + + + if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { + push @search, "agentnum = $1"; # $search{'agentnum'} = $1; + my $agent = qsearchs('agent', { 'agentnum' => $1 } ); + die "unknown agentnum $1" unless $agent; + $title = $agent->agent. " $title"; + } + + if ( $cgi->param('payby') ) { + $cgi->param('payby') =~ + /^(CARD|CHEK|BILL|PREP|CASH|WEST|MCRD)(-(VisaMC|Amex|Discover|Maestro))?$/ + or die "illegal payby ". $cgi->param('payby'); + push @search, "cust_pay.payby = '$1'"; + if ( $3 ) { + + my $cardtype = $3; + + my $search; + if ( $cardtype eq 'VisaMC' ) { + #avoid posix regexes for portability + $search = + " ( ( substring(cust_pay.payinfo from 1 for 1) = '4' ". + " AND substring(cust_pay.payinfo from 1 for 4) != '4936' ". + " AND substring(cust_pay.payinfo from 1 for 6) ". + " NOT SIMILAR TO '49030[2-9]' ". + " AND substring(cust_pay.payinfo from 1 for 6) ". + " NOT SIMILAR TO '49033[5-9]' ". + " AND substring(cust_pay.payinfo from 1 for 6) ". + " NOT SIMILAR TO '49110[1-2]' ". + " AND substring(cust_pay.payinfo from 1 for 6) ". + " NOT SIMILAR TO '49117[4-9]' ". + " AND substring(cust_pay.payinfo from 1 for 6) ". + " NOT SIMILAR TO '49118[1-2]' ". + " )". + " OR substring(cust_pay.payinfo from 1 for 2) = '51' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '52' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '53' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '54' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '54' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '55' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '36' ". #Diner's int'l processed as Visa/MC inside US + " ) "; + } elsif ( $cardtype eq 'Amex' ) { + $search = + " ( substring(cust_pay.payinfo from 1 for 2 ) = '34' ". + " OR substring(cust_pay.payinfo from 1 for 2 ) = '37' ". + " ) "; + } elsif ( $cardtype eq 'Discover' ) { + $search = + " ( substring(cust_pay.payinfo from 1 for 4 ) = '6011' ". + " OR substring(cust_pay.payinfo from 1 for 2 ) = '65' ". + " OR substring(cust_pay.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN + " ) "; + } elsif ( $cardtype eq 'Maestro' ) { + $search = + " ( substring(cust_pay.payinfo from 1 for 2 ) = '63' ". + " OR substring(cust_pay.payinfo from 1 for 2 ) = '67' ". + " OR substring(cust_pay.payinfo from 1 for 6 ) = '564182' ". + " OR substring(cust_pay.payinfo from 1 for 4 ) = '4936' ". + " OR substring(cust_pay.payinfo from 1 for 6 ) ". + " SIMILAR TO '49030[2-9]' ". + " OR substring(cust_pay.payinfo from 1 for 6 ) ". + " SIMILAR TO '49033[5-9]' ". + " OR substring(cust_pay.payinfo from 1 for 6 ) ". + " SIMILAR TO '49110[1-2]' ". + " OR substring(cust_pay.payinfo from 1 for 6 ) ". + " SIMILAR TO '49117[4-9]' ". + " OR substring(cust_pay.payinfo from 1 for 6 ) ". + " SIMILAR TO '49118[1-2]' ". + " ) "; + } else { + die "unknown card type $cardtype"; + } + + my $masksearch = $search; + $masksearch =~ s/cust_pay\.payinfo/cust_pay.paymask/gi; + + push @search, + "( $search OR ( cust_pay.paymask IS NOT NULL AND $masksearch ) )"; + + } + } + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + push @search, "_date >= $beginning ", + "_date <= $ending"; + + push @search, FS::UI::Web::parse_lt_gt($cgi, 'paid' ); + + $orderby = '_date'; + + } elsif ( $cgi->param('magic') eq 'paybatch' ) { + + $cgi->param('paybatch') =~ /^([\w\/\:\-\.]+)$/ + or die "illegal paybatch: ". $cgi->param('paybatch'); + + push @search, "paybatch = '$1'"; + + $orderby = "LOWER(company || ' ' || last || ' ' || first )"; + + } else { + die "unknown search magic: ". $cgi->param('magic'); + } + + #here is the agent virtualization + push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; + + my $search = ' WHERE '. join(' AND ', @search); + + $count_query = "SELECT COUNT(*), SUM(paid) ". + "FROM cust_pay LEFT JOIN cust_main USING ( custnum )". + $search; + + $sql_query = { + 'table' => 'cust_pay', + 'select' => join(', ', + 'cust_pay.*', + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'hashref' => {}, + 'extra_sql' => "$search ORDER BY $orderby", + 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + }; + +} else { + + $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo"; + my $payinfo = $1; + + $cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby"; + my $payby = $1; + + $count_query = "SELECT COUNT(*), SUM(paid) FROM cust_pay". + " WHERE payinfo = '$payinfo' AND payby = '$payby'". + " AND ". $FS::CurrentUser::CurrentUser->agentnums_sql; + + $sql_query = { + 'table' => 'cust_pay', + 'hashref' => { 'payinfo' => $payinfo, + 'payby' => $payby }, + 'extra_sql' => $FS::CurrentUser::CurrentUser->agentnums_sql. + " ORDER BY _date", + }; + +} + +my $link = sub { + my $cust_pay = shift; + $cust_pay->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + + diff --git a/httemplate/search/cust_pay_batch.cgi b/httemplate/search/cust_pay_batch.cgi index 9b0aaeca9..fec3dfdeb 100755 --- a/httemplate/search/cust_pay_batch.cgi +++ b/httemplate/search/cust_pay_batch.cgi @@ -1,116 +1,3 @@ -%my( $count_query, $sql_query ); -%my $hashref = {}; -%my @search = (); -%my $orderby = 'paybatchnum'; -% -%my( $pay_batch, $batchnum ) = ( '', ''); -%if ( $cgi->param('batchnum') && $cgi->param('batchnum') =~ /^(\d+)$/ ) { -% push @search, "batchnum = $1"; -% $pay_batch = qsearchs('pay_batch', { 'batchnum' => $1 } ); -% die "Batch $1 not found!" unless $pay_batch; -% $batchnum = $pay_batch->batchnum; -%} -% -%if ( $cgi->param('payby') ) { -% $cgi->param('payby') =~ /^(CARD|CHEK)$/ -% or die "illegal payby " . $cgi->param('payby'); -% -% push @search, "cust_pay_batch.payby = '$1'"; -%} -% -%if ( not $cgi->param('dcln') ) { -% push @search, "cpb.status IS DISTINCT FROM 'Approved'"; -%} -% -%my ($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -%unless ($pay_batch){ -% push @search, "pay_batch.upload >= $beginning" if ($beginning); -% push @search, "pay_batch.upload <= $ending" if ($ending < 4294967295);#2^32-1 -% $orderby = "pay_batch.download,paybatchnum"; -%} -% -%push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; -%my $search = ' WHERE ' . join(' AND ', @search); -% -%$count_query = 'SELECT COUNT(*) FROM cust_pay_batch AS cpb ' . -% 'LEFT JOIN cust_main USING ( custnum ) ' . -% 'LEFT JOIN pay_batch USING ( batchnum )' . -% $search; -% -%#grr -%$sql_query = "SELECT paybatchnum,invnum,custnum,cpb.last,cpb.first," . -% "cpb.payname,cpb.payinfo,cpb.exp,amount,cpb.status " . -% "FROM cust_pay_batch AS cpb " . -% 'LEFT JOIN cust_main USING ( custnum ) ' . -% 'LEFT JOIN pay_batch USING ( batchnum ) ' . -% "$search ORDER BY $orderby"; -% -%my $html_init = ''; -%if ( $pay_batch ) { -% my $conf = new FS::Conf; -% my $fixed = $conf->config('batch-fixed_format-'. $pay_batch->payby); -% if ( -% $pay_batch->status eq 'O' -% || ( $pay_batch->status eq 'I' -% && $FS::CurrentUser::CurrentUser->access_right('Reprocess batches') -% ) -% ) { -% $html_init .= qq!
!; -% if ( $fixed ) { -% $html_init .= qq!!; -% } else { -% $html_init .= qq!Download batch in format !; -% } -% $html_init .= qq!

!; -% } -% -% if ( -% $pay_batch->status eq 'I' -% || ( $pay_batch->status eq 'R' -% && $FS::CurrentUser::CurrentUser->access_right('Reprocess batches') -% ) -% ) { -% $html_init .= qq!
!. -% qq!Upload results
!. -% qq!Filename
!; -% if ( $fixed ) { -% $html_init .= qq!!; -% } else { -% $html_init .= qq!Format
!; -% } -% $html_init .= qq!!; -% $html_init .= '

'; -% } -% -%} -% -%if ($pay_batch) { -% my $sth = dbh->prepare($count_query) or die dbh->errstr. "doing $count_query"; -% $sth->execute or die "Error executing \"$count_query\": ". $sth->errstr; -% my $cards = $sth->fetchrow_arrayref->[0]; -% -% my $st = "SELECT SUM(amount) from cust_pay_batch WHERE batchnum=". $batchnum; -% $sth = dbh->prepare($st) or die dbh->errstr. "doing $st"; -% $sth->execute or die "Error executing \"$st\": ". $sth->errstr; -% my $total = $sth->fetchrow_arrayref->[0]; -% -% $html_init .= "$cards credit card payments batched
\$" . -% sprintf("%.2f", $total) ." total in batch
"; -%} -% -% <% include('elements/search.html', 'title' => 'Batch payment details', 'name' => 'batch details', @@ -169,4 +56,122 @@ ], ) %> +<%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports') + || $FS::CurrentUser::CurrentUser->access_right('Process batches'); + +my( $count_query, $sql_query ); +my $hashref = {}; +my @search = (); +my $orderby = 'paybatchnum'; + +my( $pay_batch, $batchnum ) = ( '', ''); +if ( $cgi->param('batchnum') && $cgi->param('batchnum') =~ /^(\d+)$/ ) { + push @search, "batchnum = $1"; + $pay_batch = qsearchs('pay_batch', { 'batchnum' => $1 } ); + die "Batch $1 not found!" unless $pay_batch; + $batchnum = $pay_batch->batchnum; +} + +if ( $cgi->param('payby') ) { + $cgi->param('payby') =~ /^(CARD|CHEK)$/ + or die "illegal payby " . $cgi->param('payby'); + + push @search, "cust_pay_batch.payby = '$1'"; +} + +if ( not $cgi->param('dcln') ) { + push @search, "cpb.status IS DISTINCT FROM 'Approved'"; +} + +my ($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +unless ($pay_batch){ + push @search, "pay_batch.upload >= $beginning" if ($beginning); + push @search, "pay_batch.upload <= $ending" if ($ending < 4294967295);#2^32-1 + $orderby = "pay_batch.download,paybatchnum"; +} + +push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; +my $search = ' WHERE ' . join(' AND ', @search); + +$count_query = 'SELECT COUNT(*) FROM cust_pay_batch AS cpb ' . + 'LEFT JOIN cust_main USING ( custnum ) ' . + 'LEFT JOIN pay_batch USING ( batchnum )' . + $search; + +#grr +$sql_query = "SELECT paybatchnum,invnum,custnum,cpb.last,cpb.first," . + "cpb.payname,cpb.payinfo,cpb.exp,amount,cpb.status " . + "FROM cust_pay_batch AS cpb " . + 'LEFT JOIN cust_main USING ( custnum ) ' . + 'LEFT JOIN pay_batch USING ( batchnum ) ' . + "$search ORDER BY $orderby"; + +my $html_init = ''; +if ( $pay_batch ) { + my $conf = new FS::Conf; + my $fixed = $conf->config('batch-fixed_format-'. $pay_batch->payby); + if ( + $pay_batch->status eq 'O' + || ( $pay_batch->status eq 'I' + && $FS::CurrentUser::CurrentUser->access_right('Reprocess batches') + ) + ) { + $html_init .= qq!
!; + if ( $fixed ) { + $html_init .= qq!!; + } else { + $html_init .= qq!Download batch in format !; + } + $html_init .= qq!

!; + } + + if ( + $pay_batch->status eq 'I' + || ( $pay_batch->status eq 'R' + && $FS::CurrentUser::CurrentUser->access_right('Reprocess batches') + ) + ) { + $html_init .= qq!
!. + qq!Upload results
!. + qq!Filename
!; + if ( $fixed ) { + $html_init .= qq!!; + } else { + $html_init .= qq!Format
!; + } + $html_init .= qq!!; + $html_init .= '

'; + } + +} + +if ($pay_batch) { + my $sth = dbh->prepare($count_query) or die dbh->errstr. "doing $count_query"; + $sth->execute or die "Error executing \"$count_query\": ". $sth->errstr; + my $cards = $sth->fetchrow_arrayref->[0]; + + my $st = "SELECT SUM(amount) from cust_pay_batch WHERE batchnum=". $batchnum; + $sth = dbh->prepare($st) or die dbh->errstr. "doing $st"; + $sth->execute or die "Error executing \"$st\": ". $sth->errstr; + my $total = $sth->fetchrow_arrayref->[0]; + + $html_init .= "$cards credit card payments batched
\$" . + sprintf("%.2f", $total) ." total in batch
"; +} + + diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 7a651c4bf..bb2307641 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,218 +1,4 @@ -% -% -%# 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, -% "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"; -% } -%} -%#eslaf -% -%### -%# parse part_pkg -%### -% -%my $pkgpart = join (' OR pkgpart=', -% grep {$_} map { /^(\d+)$/; } ($cgi->param('pkgpart'))); -%push @where, '(pkgpart=' . $pkgpart . ')' if $pkgpart; -% -%### -%# parse magic, legacy, etc. -%### -% -%my $orderby; -%if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { -% $orderby = 'ORDER BY bill'; -% -% my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -% push @where, -% #"bill >= $beginning ", -% #"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 )' -% ; -% -%} else { -% -% 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'; -% -% 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 -% ) -% )'; -% -% } else { -% die "Empty or unknown QUERY_STRING!"; -% } -% -%} -% -%## -%# 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 $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 $link = sub { -% [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ]; -%}; -% -%my $clink = sub { -% my $cust_pkg = shift; -% $cust_pkg->cust_main_custnum -% ? [ "${p}view/cust_main.cgi?", 'custnum' ] -% : ''; -%}; -% -%#if ( scalar(@cust_pkg) == 1 ) { -%# print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum. -%# "#cust_pkg". $cust_pkg[0]->pkgnum ); -% -%# my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } ); -%# my $rowspan = scalar(@cust_svc) || 1; -% -%# 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!; -%# $n2=""; -%# } -% -%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 ) : ''; -% }; -%} -% -%### -%# and finally, include the search template -%### -% -% -<% include( 'elements/search.html', +<% include( 'elements/search.html', 'title' => 'Package Search Results', 'name' => 'packages', 'query' => $sql_query, @@ -295,16 +81,13 @@ '', '', '', - ( map { '' } - FS::UI::Web::cust_header( - $cgi->param('cust_fields') - ) - ), + FS::UI::Web::cust_colors(), '', ], - 'style' => [ '', '', '', 'b' ], + 'style' => [ '', '', '', 'b', '', '', '', '', '', '', '', + FS::UI::Web::cust_styles() ], 'size' => [ '', '', '', '-1', ], - 'align' => 'rllclrrrrrr', + 'align' => 'rllclrrrrrr'. FS::UI::Web::cust_aligns(). 'r', 'links' => [ $link, $link, @@ -317,7 +100,7 @@ '', '', '', - ( map { $clink } + ( map { $_ ne 'Cust. Status' ? $clink : '' } FS::UI::Web::cust_header( $cgi->param('cust_fields') ) @@ -326,3 +109,216 @@ ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List packages'); + +# 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, + "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"; + } +} +#eslaf + +### +# parse part_pkg +### + +my $pkgpart = join (' OR pkgpart=', + grep {$_} map { /^(\d+)$/; } ($cgi->param('pkgpart'))); +push @where, '(pkgpart=' . $pkgpart . ')' if $pkgpart; + +### +# parse magic, legacy, etc. +### + +my $orderby; +if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { + $orderby = 'ORDER BY bill'; + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + push @where, + #"bill >= $beginning ", + #"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 )' + ; + +} else { + + 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'; + + 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 + ) + )'; + + } else { + die "Empty or unknown QUERY_STRING!"; + } + +} + +## +# 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 $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 $link = sub { + [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ]; +}; + +my $clink = sub { + my $cust_pkg = shift; + $cust_pkg->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +#if ( scalar(@cust_pkg) == 1 ) { +# print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum. +# "#cust_pkg". $cust_pkg[0]->pkgnum ); + +# my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } ); +# my $rowspan = scalar(@cust_svc) || 1; + +# 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!; +# $n2=""; +# } + +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 ) : ''; + }; +} + + diff --git a/httemplate/search/cust_svc.html b/httemplate/search/cust_svc.html index 127e7abfb..6369b202e 100644 --- a/httemplate/search/cust_svc.html +++ b/httemplate/search/cust_svc.html @@ -21,14 +21,28 @@ 'links' => [ $link, $link, # package? - ( map { $link_cust } + ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], + 'align' => 'rl'. FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + FS::UI::Web::cust_styles(), + ], ) %> <%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List services'); + my $addl_from = ' LEFT JOIN part_svc USING ( svcpart ) '. ' LEFT JOIN cust_pkg USING ( pkgnum ) '. ' LEFT JOIN cust_main USING ( custnum ) '; diff --git a/httemplate/search/cust_tax_exempt_pkg.cgi b/httemplate/search/cust_tax_exempt_pkg.cgi index 990e344b7..604502d6f 100644 --- a/httemplate/search/cust_tax_exempt_pkg.cgi +++ b/httemplate/search/cust_tax_exempt_pkg.cgi @@ -54,9 +54,29 @@ $ilink, $ilink, - ( map { $clink } FS::UI::Web::cust_header() ), + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), ], - 'align' => 'rrrlrc', # 'rlrrrc', + 'align' => 'rrrlrc'.FS::UI::Web::cust_aligns(), # 'rlrrrc', + 'color' => [ + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], ) %> <%once> @@ -80,6 +100,9 @@ my $join = " <%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('View customer tax exemptions'); + my @where = (); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); diff --git a/httemplate/search/inventory_item.html b/httemplate/search/inventory_item.html index fc690b33b..1e7bdd91c 100644 --- a/httemplate/search/inventory_item.html +++ b/httemplate/search/inventory_item.html @@ -1,54 +1,4 @@ -% -% -%my $classnum = $cgi->param('classnum'); -%$classnum =~ /^(\d+)$/ or eidiot "illegal classnum $classnum"; -%$classnum = $1; -% -%my $inventory_class = qsearchs( { -% 'table' => 'inventory_class', -% 'hashref' => { 'classnum' => $classnum }, -%} ); -% -%my $title = $inventory_class->classname. ' Inventory'; -% -%#little false laziness with SQL fragments in inventory_class.pm -%my $extra_sql = ''; -%if ( $cgi->param('avail') ) { -% $extra_sql = 'AND ( svcnum IS NULL OR svcnum = 0 )'; -% $title .= ' - Available'; -%} elsif ( $cgi->param('used') ) { -% $extra_sql = 'AND svcnum IS NOT NULL AND svcnum > 0'; -% $title .= ' - In use'; -%} -% -%my $count_query = -% "SELECT COUNT(*) FROM inventory_item WHERE classnum = $classnum $extra_sql"; -% -%my $link = sub { -% my $inventory_item = shift; -% if ( $inventory_item->svcnum ) { -% [ "${p}view/svc_acct.cgi?", 'svcnum' ]; -% } else { -% ''; -% } -%}; -%my $link_cust = sub { -% my $inventory_item = shift; -% if ( $inventory_item->custnum ) { -% [ "${p}view/cust_main.cgi?", 'custnum' ]; -% } else { -% ''; -% } -%}; -% -%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 ) '; -% -% <% include( 'elements/search.html', - 'title' => $title, #less lame to use Lingua:: something to pluralize @@ -97,13 +47,79 @@ \&FS::UI::Web::cust_fields, ], - + 'align' => 'rll'.FS::UI::Web::cust_aligns(), 'links' => [ '', '', $link, - ( map { $link_cust } FS::UI::Web::cust_header() ), + ( map { $_ ne 'Cust. Status' ? $link_cust : '' } + FS::UI::Web::cust_header() + ), ], + 'color' => [ + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + FS::UI::Web::cust_styles(), + ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + +my $classnum = $cgi->param('classnum'); +$classnum =~ /^(\d+)$/ or eidiot "illegal classnum $classnum"; +$classnum = $1; + +my $inventory_class = qsearchs( { + 'table' => 'inventory_class', + 'hashref' => { 'classnum' => $classnum }, +} ); + +my $title = $inventory_class->classname. ' Inventory'; + +#little false laziness with SQL fragments in inventory_class.pm +my $extra_sql = ''; +if ( $cgi->param('avail') ) { + $extra_sql = 'AND ( svcnum IS NULL OR svcnum = 0 )'; + $title .= ' - Available'; +} elsif ( $cgi->param('used') ) { + $extra_sql = 'AND svcnum IS NOT NULL AND svcnum > 0'; + $title .= ' - In use'; +} + +my $count_query = + "SELECT COUNT(*) FROM inventory_item WHERE classnum = $classnum $extra_sql"; + +my $link = sub { + my $inventory_item = shift; + if ( $inventory_item->svcnum ) { + [ "${p}view/svc_acct.cgi?", 'svcnum' ]; + } else { + ''; + } +}; +my $link_cust = sub { + my $inventory_item = shift; + if ( $inventory_item->custnum ) { + [ "${p}view/cust_main.cgi?", 'custnum' ]; + } else { + ''; + } +}; + +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 ) '; + + diff --git a/httemplate/search/pay_batch.cgi b/httemplate/search/pay_batch.cgi index 7b2b9f00b..cb2171799 100755 --- a/httemplate/search/pay_batch.cgi +++ b/httemplate/search/pay_batch.cgi @@ -1,44 +1,3 @@ -% -% -%my %statusmap = ('I'=>'In Transit', 'O'=>'Open', 'R'=>'Resolved'); -%my $hashref = {}; -%my $count_query = 'SELECT COUNT(*) FROM pay_batch'; -% -%my($begin, $end) = ( '', '' ); -% -%my @where; -%if ( $cgi->param('beginning') -% && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { -% $begin = str2time($1); -% push @where, "download >= $begin"; -%} -%if ( $cgi->param('ending') -% && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { -% $end = str2time($1) + 86399; -% push @where, "download < $end"; -%} -% -%my @status; -%if ( $cgi->param('open') ) { -% push @status, "O"; -%} -% -%if ( $cgi->param('intransit') ) { -% push @status, "I"; -%} -% -%if ( $cgi->param('resolved') ) { -% push @status, "R"; -%} -% -%push @where, -% scalar(@status) ? q!(status='! . join(q!' OR status='!, @status) . q!')! -% : q!status='X'!; # kludgy, X is unused at present -% -%my $extra_sql = scalar(@where) ? 'WHERE ' . join(' AND ', @where) : ''; -% -%my $link = [ "${p}search/cust_pay_batch.cgi?batchnum=", 'batchnum' ]; -% <% include( 'elements/search.html', 'title' => 'Payment Batches', 'name_singular' => 'batch', @@ -123,5 +82,49 @@ ) %> +<%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports') + || $FS::CurrentUser::CurrentUser->access_right('Process batches'); +my %statusmap = ('I'=>'In Transit', 'O'=>'Open', 'R'=>'Resolved'); +my $hashref = {}; +my $count_query = 'SELECT COUNT(*) FROM pay_batch'; + +my($begin, $end) = ( '', '' ); + +my @where; +if ( $cgi->param('beginning') + && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $begin = str2time($1); + push @where, "download >= $begin"; +} +if ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $end = str2time($1) + 86399; + push @where, "download < $end"; +} + +my @status; +if ( $cgi->param('open') ) { + push @status, "O"; +} + +if ( $cgi->param('intransit') ) { + push @status, "I"; +} + +if ( $cgi->param('resolved') ) { + push @status, "R"; +} + +push @where, + scalar(@status) ? q!(status='! . join(q!' OR status='!, @status) . q!')! + : q!status='X'!; # kludgy, X is unused at present + +my $extra_sql = scalar(@where) ? 'WHERE ' . join(' AND ', @where) : ''; + +my $link = [ "${p}search/cust_pay_batch.cgi?batchnum=", 'batchnum' ]; + + diff --git a/httemplate/search/pay_batch.html b/httemplate/search/pay_batch.html index a966f68f5..5907169d8 100644 --- a/httemplate/search/pay_batch.html +++ b/httemplate/search/pay_batch.html @@ -25,3 +25,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + diff --git a/httemplate/search/queue.html b/httemplate/search/queue.html index 675fccd0b..c343014cc 100644 --- a/httemplate/search/queue.html +++ b/httemplate/search/queue.html @@ -1,17 +1,3 @@ -% -% -%my $hashref = {}; -% -%my $conf = new FS::Conf; -%my $dangerous = $conf->exists('queue_dangerous_controls'); -% -%my $noactions = 0; -% -%my $count_query = 'SELECT COUNT(*) FROM queue'; # + $hashref -% -%my $areboxes = 0; -% -% <% include( 'elements/search.html', 'title' => 'Job Queue', 'menubar' => [ 'Main menu' => $p, ], @@ -134,3 +120,20 @@ ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Job queue'); + +my $hashref = {}; + +my $conf = new FS::Conf; +my $dangerous = $conf->exists('queue_dangerous_controls'); + +my $noactions = 0; + +my $count_query = 'SELECT COUNT(*) FROM queue'; # + $hashref + +my $areboxes = 0; + + diff --git a/httemplate/search/reg_code.html b/httemplate/search/reg_code.html index dc388db2e..87e0fcdd5 100644 --- a/httemplate/search/reg_code.html +++ b/httemplate/search/reg_code.html @@ -1,13 +1,3 @@ -% -% -%my $agentnum = $cgi->param('agentnum'); -%$agentnum =~ /^(\d+)$/ or eidiot "illegal agentnum $agentnum"; -%$agentnum = $1; -%my $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); -% -%my $count_query = "SELECT COUNT(*) FROM reg_code WHERE agentnum = $agentnum"; -% -% <% include( 'elements/search.html', 'title' => 'Unused Registration Codes for '. $agent->agent, @@ -35,3 +25,16 @@ ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + +my $agentnum = $cgi->param('agentnum'); +$agentnum =~ /^(\d+)$/ or eidiot "illegal agentnum $agentnum"; +$agentnum = $1; +my $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + +my $count_query = "SELECT COUNT(*) FROM reg_code WHERE agentnum = $agentnum"; + +<%init> diff --git a/httemplate/search/report_cdr.html b/httemplate/search/report_cdr.html index c480c05f1..819ba2195 100644 --- a/httemplate/search/report_cdr.html +++ b/httemplate/search/report_cdr.html @@ -9,4 +9,9 @@ Status: <% include('/elements/footer.html') %> +<%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); + + diff --git a/httemplate/search/report_cust_bill.html b/httemplate/search/report_cust_bill.html index ec57d2094..4fa09f96c 100644 --- a/httemplate/search/report_cust_bill.html +++ b/httemplate/search/report_cust_bill.html @@ -26,3 +26,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List invoices'); + + diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html index ab481025e..993209763 100644 --- a/httemplate/search/report_cust_credit.html +++ b/httemplate/search/report_cust_credit.html @@ -42,6 +42,9 @@ <%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_credit") or die dbh->errstr; $sth->execute or die $sth->errstr; diff --git a/httemplate/search/report_cust_main-zip.html b/httemplate/search/report_cust_main-zip.html index db5e65c8f..1cd07ef76 100644 --- a/httemplate/search/report_cust_main-zip.html +++ b/httemplate/search/report_cust_main-zip.html @@ -42,5 +42,11 @@
- - + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List zip codes'); + + diff --git a/httemplate/search/report_cust_pay.html b/httemplate/search/report_cust_pay.html index 90c5d255d..0327e042e 100644 --- a/httemplate/search/report_cust_pay.html +++ b/httemplate/search/report_cust_pay.html @@ -70,3 +70,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + diff --git a/httemplate/search/report_cust_pay_batch.html b/httemplate/search/report_cust_pay_batch.html index 0dc4bc1b7..f57a9557e 100644 --- a/httemplate/search/report_cust_pay_batch.html +++ b/httemplate/search/report_cust_pay_batch.html @@ -35,3 +35,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + diff --git a/httemplate/search/report_cust_pkg.html b/httemplate/search/report_cust_pkg.html index 2193cac8b..92138b507 100755 --- a/httemplate/search/report_cust_pkg.html +++ b/httemplate/search/report_cust_pkg.html @@ -46,3 +46,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List packages'); + + diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/report_prepaid_income.cgi index 241e8a008..fd9b01ec1 100644 --- a/httemplate/search/report_prepaid_income.cgi +++ b/httemplate/search/report_prepaid_income.cgi @@ -1,65 +1,3 @@ - -% -% -% #doesn't yet deal with daily/weekly packages -% -% #needs to be re-written in sql for efficiency -% -% my $time = time; -% -% my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time; -% $now =~ /^(\d+)$/ or die "unparsable date?"; -% $now = $1; -% -% my( $total, $total_legacy ) = ( 0, 0 ); -% -% my @cust_bill_pkg = -% grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[dw])$/ } -% qsearch( 'cust_bill_pkg', { -% 'recur' => { op=>'!=', value=>0 }, -% 'edate' => { op=>'>', value=>$now }, -% }, ); -% -% my @cust_pkg = -% grep { $_->part_pkg->recur != 0 -% && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/ -% } -% qsearch ( 'cust_pkg', { -% 'bill' => { op=>'>', value=>$now } -% } ); -% -% foreach my $cust_bill_pkg ( @cust_bill_pkg) { -% my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; -% -% my $elapsed = $now - $cust_bill_pkg->sdate; -% $elapsed = 0 if $elapsed < 0; -% -% my $remaining = 1 - $elapsed/$period; -% -% my $unearned = $remaining * $cust_bill_pkg->recur; -% $total += $unearned; -% -% } -% -% foreach my $cust_pkg ( @cust_pkg ) { -% my $period = $cust_pkg->bill - $cust_pkg->last_bill; -% -% my $elapsed = $now - $cust_pkg->last_bill; -% $elapsed = 0 if $elapsed < 0; -% -% my $remaining = 1 - $elapsed/$period; -% -% my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy -% $total_legacy += $unearned; -% -% } -% -% $total = sprintf('%.2f', $total); -% $total_legacy = sprintf('%.2f', $total_legacy); -% -% - - <% include("/elements/header.html", 'Prepaid Income (Unearned Revenue) Report', menubar( 'Main Menu'=>$p, ) ) %> <% table() %> @@ -85,3 +23,65 @@ revenue if you have imported longer-than monthly customer packages from a previous billing system. +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +#doesn't yet deal with daily/weekly packages + +#needs to be re-written in sql for efficiency + +my $time = time; + +my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time; +$now =~ /^(\d+)$/ or die "unparsable date?"; +$now = $1; + +my( $total, $total_legacy ) = ( 0, 0 ); + +my @cust_bill_pkg = + grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[dw])$/ } + qsearch( 'cust_bill_pkg', { + 'recur' => { op=>'!=', value=>0 }, + 'edate' => { op=>'>', value=>$now }, + }, ); + +my @cust_pkg = + grep { $_->part_pkg->recur != 0 + && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/ + } + qsearch ( 'cust_pkg', { + 'bill' => { op=>'>', value=>$now } + } ); + +foreach my $cust_bill_pkg ( @cust_bill_pkg) { + my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; + + my $elapsed = $now - $cust_bill_pkg->sdate; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + my $unearned = $remaining * $cust_bill_pkg->recur; + $total += $unearned; + +} + +foreach my $cust_pkg ( @cust_pkg ) { + my $period = $cust_pkg->bill - $cust_pkg->last_bill; + + my $elapsed = $now - $cust_pkg->last_bill; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy + $total_legacy += $unearned; + +} + +$total = sprintf('%.2f', $total); +$total_legacy = sprintf('%.2f', $total_legacy); + + diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html index 305441db7..81adb64ad 100644 --- a/httemplate/search/report_prepaid_income.html +++ b/httemplate/search/report_prepaid_income.html @@ -35,3 +35,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi index 46b3ca85e..af8e07678 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -1,139 +1,3 @@ -% -% -% sub owed { -% my($start, $end, %opt) = @_; -% -% my @where = (); -% -% #handle start and end ranges -% -% #24h * 60m * 60s -% push @where, "cust_bill._date <= extract(epoch from now())-". -% ($start * 86400) -% if $start; -% -% push @where, "cust_bill._date > extract(epoch from now()) - ". -% ($end * 86400) -% if $end; -% -% #handle 'cust' option -% -% push @where, "cust_main.custnum = cust_bill.custnum" -% if $opt{'cust'}; -% -% #handle 'agentnum' option -% my $join = ''; -% if ( $opt{'agentnum'} ) { -% $join = 'LEFT JOIN cust_main USING ( custnum )'; -% push @where, "agentnum = '$opt{'agentnum'}'"; -% } -% -% my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : ''; -% -% my $as = $opt{'noas'} ? '' : "as owed_${start}_$end"; -% -% my $charged = <1 ), @ranges ); -% -% my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql; -% -% my $active_sql = FS::cust_pkg->active_sql; -% my $inactive_sql = FS::cust_pkg->inactive_sql; -% my $suspended_sql = FS::cust_pkg->suspended_sql; -% my $cancelled_sql = FS::cust_pkg->cancelled_sql; -% -% my $packages_cols = <1, 'noas'=>1). " > 0"; -% -% my $agentnum = ''; -% if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { -% $agentnum = $1; -% $where .= " AND agentnum = '$agentnum' "; -% } -% -% #here is the agent virtualization -% $where .= ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql; -% -% my $count_sql = "select count(*) from cust_main $where"; -% -% my $sql_query = { -% 'table' => 'cust_main', -% 'hashref' => {}, -% 'select' => "*, $owed_cols, $packages_cols", -% 'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)", -% }; -% -% my $total_sql = "select ". -% join(',', map owed( @$_, 'agentnum'=>$agentnum ), @ranges ); -% -% my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; -% $total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr; -% my $row = $total_sth->fetchrow_hashref(); -% -% my $conf = new FS::Conf; -% my $money_char = $conf->config('money_char') || '$'; -% -% my $align = join('', map { /#/ ? 'r' : 'l' } FS::UI::Web::cust_header() ). -% 'crrrrr'; -% -% my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; -% -% my $status_statuscol = sub { -% #conceptual false laziness with cust_main::status... -% my $row = shift; -% -% my $status = 'unknown'; -% if ( $row->num_pkgs_sql == 0 ) { -% $status = 'prospect'; -% } elsif ( $row->active_pkgs > 0 ) { -% $status = 'active'; -% } elsif ( $row->inactive_pkgs > 0 ) { -% $status = 'inactive'; -% } elsif ( $row->suspended_pkgs > 0 ) { -% $status = 'suspended'; -% } elsif ( $row->cancelled_pkgs > 0 ) { -% $status = 'cancelled' -% } -% -% ( ucfirst($status), $FS::cust_main::statuscolor{$status} ); -% }; -% -% -% <% include( 'elements/search.html', 'title' => 'Accounts Receivable Aging Summary', 'name' => 'customers', @@ -141,7 +5,7 @@ 'count_query' => $count_sql, 'header' => [ FS::UI::Web::cust_header(), - 'Status', # (me)', + #'Status', # (me)', #'Status', # (cust_main)', '0-30', '30-60', @@ -156,7 +20,7 @@ scalar(FS::UI::Web::cust_header()-1) ) ), - '', + #'', #'', sprintf( $money_char.'%.2f', $row->{'owed_0_30'} ), @@ -171,7 +35,7 @@ ], 'fields' => [ \&FS::UI::Web::cust_fields, - sub { ( &{$status_statuscol}(shift) )[0] }, + #sub { ( &{$status_statuscol}(shift) )[0] }, #sub { ucfirst(shift->status) }, sub { sprintf( $money_char.'%.2f', shift->get('owed_0_30') ) }, @@ -185,8 +49,10 @@ shift->get('owed_0_0') ) }, ], 'links' => [ - ( map $clink, FS::UI::Web::cust_header() ), - '', + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), + #'', #'', '', '', @@ -199,12 +65,14 @@ #'size' => [ '', '', '-1', '-1', '', '', '', '', '', ], #'style' => [ '', '', 'b', 'b', '', '', '', '', 'b', ], 'size' => [ ( map '', FS::UI::Web::cust_header() ), - '-1', '', '', '', '', '', ], - 'style' => [ ( map '', FS::UI::Web::cust_header() ), - 'b', '', '', '', '', 'b', ], + #'-1', '', '', '', '', '', ], + '', '', '', '', '', ], + 'style' => [ FS::UI::Web::cust_styles(), + #'b', '', '', '', '', 'b', ], + '', '', '', '', 'b', ], 'color' => [ - ( map '', FS::UI::Web::cust_header() ), - sub { ( &{$status_statuscol}(shift) )[1] }, + FS::UI::Web::cust_colors(), + #sub { ( &{$status_statuscol}(shift) )[1] }, #sub { shift->statuscolor; }, '', '', @@ -215,3 +83,143 @@ ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +sub owed { + my($start, $end, %opt) = @_; + + my @where = (); + + #handle start and end ranges + + #24h * 60m * 60s + push @where, "cust_bill._date <= extract(epoch from now())-". + ($start * 86400) + if $start; + + push @where, "cust_bill._date > extract(epoch from now()) - ". + ($end * 86400) + if $end; + + #handle 'cust' option + + push @where, "cust_main.custnum = cust_bill.custnum" + if $opt{'cust'}; + + #handle 'agentnum' option + my $join = ''; + if ( $opt{'agentnum'} ) { + $join = 'LEFT JOIN cust_main USING ( custnum )'; + push @where, "agentnum = '$opt{'agentnum'}'"; + } + + my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : ''; + + my $as = $opt{'noas'} ? '' : "as owed_${start}_$end"; + + my $charged = <1 ), @ranges ); + +my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql; + +my $active_sql = FS::cust_pkg->active_sql; +my $inactive_sql = FS::cust_pkg->inactive_sql; +my $suspended_sql = FS::cust_pkg->suspended_sql; +my $cancelled_sql = FS::cust_pkg->cancelled_sql; + +my $packages_cols = <1, 'noas'=>1). " > 0"; + +my $agentnum = ''; +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + $where .= " AND agentnum = '$agentnum' "; +} + +#here is the agent virtualization +$where .= ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql; + +my $count_sql = "select count(*) from cust_main $where"; + +my $sql_query = { + 'table' => 'cust_main', + 'hashref' => {}, + 'select' => "*, $owed_cols, $packages_cols", + 'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)", +}; + +my $total_sql = "select ". + join(',', map owed( @$_, 'agentnum'=>$agentnum ), @ranges ); + +my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; +$total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr; +my $row = $total_sth->fetchrow_hashref(); + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +#my $align = join('', map { /#/ ? 'r' : 'l' } FS::UI::Web::cust_header() ). +# 'crrrrr'; +my $align = FS::UI::Web::cust_aligns(). + 'rrrrr'; + +my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +my $status_statuscol = sub { + #conceptual false laziness with cust_main::status... + my $row = shift; + + my $status = 'unknown'; + if ( $row->num_pkgs_sql == 0 ) { + $status = 'prospect'; + } elsif ( $row->active_pkgs > 0 ) { + $status = 'active'; + } elsif ( $row->inactive_pkgs > 0 ) { + $status = 'inactive'; + } elsif ( $row->suspended_pkgs > 0 ) { + $status = 'suspended'; + } elsif ( $row->cancelled_pkgs > 0 ) { + $status = 'cancelled' + } + + ( ucfirst($status), $FS::cust_main::statuscolor{$status} ); +}; + + diff --git a/httemplate/search/report_receivables.html b/httemplate/search/report_receivables.html index 1ae4b8e6f..21ca1e185 100755 --- a/httemplate/search/report_receivables.html +++ b/httemplate/search/report_receivables.html @@ -13,4 +13,9 @@ +<%init> +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 569e6e79a..918383b67 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -1,374 +1,3 @@ -% -% -%my $conf = new FS::Conf; -%my $money_char = $conf->config('money_char') || '$'; -% -%my $user = getotaker; -% -%my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -% -%my $join_cust = " -% JOIN cust_bill USING ( invnum ) -% LEFT JOIN cust_main USING ( custnum ) -%"; -%my $from_join_cust = " -% FROM cust_bill_pkg -% $join_cust -%"; -%my $join_pkg = " -% LEFT JOIN cust_pkg USING ( pkgnum ) -% LEFT JOIN part_pkg USING ( pkgpart ) -%"; -% -%my $where = "WHERE _date >= $beginning AND _date <= $ending "; -%my @base_param = qw( county county state state country ); -%if ( $conf->exists('tax-ship_address') ) { -% -% $where .= " -% AND ( ( ( ship_last IS NULL OR ship_last = '' ) -% AND ( county = ? OR ? = '' ) -% AND ( state = ? OR ? = '' ) -% AND country = ? -% ) -% OR ( ship_last IS NOT NULL AND ship_last != '' -% AND ( ship_county = ? OR ? = '' ) -% AND ( ship_state = ? OR ? = '' ) -% AND ship_country = ? -% ) -% ) -% "; -% # AND payby != 'COMP' -% -% push @base_param, @base_param; -% -%} else { -% -% $where .= " -% AND ( county = ? OR ? = '' ) -% AND ( state = ? OR ? = '' ) -% AND country = ? -% "; -% # AND payby != 'COMP' -% -%} -% -%my $agentname = ''; -%if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { -% my $agent = qsearchs('agent', { 'agentnum' => $1 } ); -% die "agent not found" unless $agent; -% $agentname = $agent->agent; -% $where .= ' AND agentnum = '. $agent->agentnum; -%} -% -%my $gotcust = " -% WHERE 0 < ( SELECT COUNT(*) FROM cust_main -%"; -%if ( $conf->exists('tax-ship_address') ) { -% -% $gotcust .= " -% WHERE -% -% ( cust_main_county.country = cust_main.country -% OR cust_main_county.country = cust_main.ship_country -% ) -% -% AND -% -% ( -% -% ( ( ship_last IS NULL OR ship_last = '' ) -% AND ( cust_main_county.country = cust_main.country ) -% AND ( cust_main_county.state = cust_main.state -% OR cust_main_county.state = '' -% OR cust_main_county.state IS NULL ) -% AND ( cust_main_county.county = cust_main.county -% OR cust_main_county.county = '' -% OR cust_main_county.county IS NULL ) -% ) -% -% OR -% -% ( ship_last IS NOT NULL AND ship_last != '' -% AND ( cust_main_county.country = cust_main.ship_country ) -% AND ( cust_main_county.state = cust_main.ship_state -% OR cust_main_county.state = '' -% OR cust_main_county.state IS NULL ) -% AND ( cust_main_county.county = cust_main.ship_county -% OR cust_main_county.county = '' -% OR cust_main_county.county IS NULL ) -% ) -% -% ) -% -% LIMIT 1 -% ) -% "; -% -%} else { -% -% $gotcust .= " -% WHERE ( cust_main.county = cust_main_county.county -% OR cust_main_county.county = '' -% OR cust_main_county.county IS NULL ) -% AND ( cust_main.state = cust_main_county.state -% OR cust_main_county.state = '' -% OR cust_main_county.state IS NULL ) -% AND ( cust_main.country = cust_main_county.country ) -% LIMIT 1 -% ) -% "; -% -%} -% -%my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0, 0 ); -%my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0 ); -%my $out = 'Out of taxable region(s)'; -%my %regions = (); -%foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) { -% #warn $r->county. ' '. $r->state. ' '. $r->country. "\n"; -% -% my $label = getlabel($r); -% $regions{$label}->{'label'} = $label; -% $regions{$label}->{'url_param'} = join(';', map "$_=".$r->$_(), qw( county state country ) ); -% -% my @param = @base_param; -% my $mywhere = $where; -% -% if ( $r->taxclass ) { -% $mywhere .= " AND taxclass = ? "; -% push @param, 'taxclass'; -% $regions{$label}->{'url_param'} .= ';taxclass='. $r->taxclass -% if $cgi->param('show_taxclasses'); -% } -% -% my $fromwhere = $from_join_cust. $join_pkg. $mywhere. " AND payby != 'COMP' "; -% -%# my $label = getlabel($r); -%# $regions{$label}->{'label'} = $label; -% -% my $nottax = 'pkgnum != 0'; -% -% ## calculate total for this region -% -% my $t = scalar_sql($r, \@param, -% "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax" -% ); -% $total += $t; -% $regions{$label}->{'total'} += $t; -% -% ## calculate customer-exemption for this region -% -%## my $taxable = $t; -% -%# my($taxable, $x_cust) = (0, 0); -%# foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i } -%# qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { -%# $taxable += scalar_sql($r, \@param, -%# "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )" -%# ); -%# -%# $x_cust += scalar_sql($r, \@param, -%# "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'" -%# ); -%# } -% -% my $x_cust = scalar_sql($r, \@param, -% "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) -% $fromwhere AND $nottax AND tax = 'Y' " -% ); -% -% $exempt_cust += $x_cust; -% $regions{$label}->{'exempt_cust'} += $x_cust; -% -% ## calculate package-exemption for this region -% -% my $x_pkg = scalar_sql($r, \@param, -% "SELECT SUM( -% ( CASE WHEN part_pkg.setuptax = 'Y' -% THEN cust_bill_pkg.setup -% ELSE 0 -% END -% ) -% + -% ( CASE WHEN part_pkg.recurtax = 'Y' -% THEN cust_bill_pkg.recur -% ELSE 0 -% END -% ) -% ) -% $fromwhere -% AND $nottax -% AND ( -% ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) -% OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) -% ) -% AND ( tax != 'Y' OR tax IS NULL ) -% " -% ); -% $exempt_pkg += $x_pkg; -% $regions{$label}->{'exempt_pkg'} += $x_pkg; -% -% ## calculate monthly exemption (texas tax) for this region -% -% # count up all the cust_tax_exempt_pkg records associated with -% # the actual line items. -% -% my $x_monthly = scalar_sql($r, \@param, -% "SELECT SUM(amount) -% FROM cust_tax_exempt_pkg -% JOIN cust_bill_pkg USING ( billpkgnum ) -% $join_cust $join_pkg -% $mywhere" -% ); -%# if ( $x_monthly ) { -%# #warn $r->taxnum(). ": $x_monthly\n"; -%# $taxable -= $x_monthly; -%# } -% -% $exempt_monthly += $x_monthly; -% $regions{$label}->{'exempt_monthly'} += $x_monthly; -% -% my $taxable = $t - $x_cust - $x_pkg - $x_monthly; -% -% $tot_taxable += $taxable; -% $regions{$label}->{'taxable'} += $taxable; -% -% $owed += $taxable * ($r->tax/100); -% $regions{$label}->{'owed'} += $taxable * ($r->tax/100); -% -% if ( defined($regions{$label}->{'rate'}) -% && $regions{$label}->{'rate'} != $r->tax.'%' ) { -% $regions{$label}->{'rate'} = 'variable'; -% } else { -% $regions{$label}->{'rate'} = $r->tax.'%'; -% } -% -%} -% -%my $taxwhere = "$from_join_cust $where AND payby != 'COMP' "; -%my @taxparam = @base_param; -%my %base_regions = (); -%#foreach my $label ( keys %regions ) { -%foreach my $r ( -% qsearch( 'cust_main_county', -% {}, -% 'DISTINCT ON (country, state, county, taxname) *', -% $gotcust -% ) -%) { -% -% #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n"; -% -% my $label = getlabel($r); -% -% #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' "; -% #my @param = @base_param; -% -% #match itemdesc if necessary! -% my $named_tax = -% $r->taxname -% ? 'AND itemdesc = '. dbh->quote($r->taxname) -% : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; -% my $x = scalar_sql($r, \@taxparam, -% "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $taxwhere ". -% "AND pkgnum = 0 $named_tax", -% ); -% $tax += $x; -% $regions{$label}->{'tax'} += $x; -% -% if ( $cgi->param('show_taxclasses') ) { -% my $base_label = getlabel($r, 'no_taxclass'=>1 ); -% $base_regions{$base_label}->{'label'} = $base_label; -% $base_regions{$base_label}->{'url_param'} = -% join(';', map "$_=".$r->$_(), qw( county state country ) ); -% $base_regions{$base_label}->{'tax'} += $x; -% } -% -%} -% -%#ordering -%my @regions = -% map $regions{$_}, -% sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } -% keys %regions; -% -%my @base_regions = -% map $base_regions{$_}, -% sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } -% keys %base_regions; -% -%push @regions, { -% 'label' => 'Total', -% 'url_param' => '', -% 'total' => $total, -% 'exempt_cust' => $exempt_cust, -% 'exempt_pkg' => $exempt_pkg, -% 'exempt_monthly' => $exempt_monthly, -% 'taxable' => $tot_taxable, -% 'rate' => '', -% 'owed' => $owed, -% 'tax' => $tax, -%}; -% -%#-- -% -%sub getlabel { -% my $r = shift; -% my %opt = @_; -% -% my $label; -% if ( -% $r->tax == 0 -% && ! scalar( qsearch('cust_main_county', { 'state' => $r->state, -% 'county' => $r->county, -% 'country' => $r->country, -% 'tax' => { op=>'>', value=>0 }, -% } -% ) -% ) -% -% ) { -% #kludge to avoid "will not stay shared" warning -% my $out = 'Out of taxable region(s)'; -% $label = $out; -% } elsif ( $r->taxname ) { -% $label = $r->taxname; -%# $regions{$label}->{'taxname'} = $label; -%# push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country ); -% } else { -% $label = $r->country; -% $label = $r->state.", $label" if $r->state; -% $label = $r->county." county, $label" if $r->county; -% $label = "$label (". $r->taxclass. ")" -% if $r->taxclass -% && $cgi->param('show_taxclasses') -% && ! $opt{'no_taxclass'}; -% #$label = $r->taxname. " ($label)" if $r->taxname; -% } -% return $label; -%} -% -%#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up -%#to FS::Report or FS::Record or who the fuck knows where) -%sub scalar_sql { -% my( $r, $param, $sql ) = @_; -% #warn "$sql\n"; -% my $sth = dbh->prepare($sql) or die dbh->errstr; -% $sth->execute( map $r->$_(), @$param ) -% or die "Unexpected error executing statement $sql: ". $sth->errstr; -% $sth->fetchrow_arrayref->[0] || 0; -%} -% -% -% -%my $dateagentlink = "begin=$beginning;end=$ending"; -%$dateagentlink .= ';agentnum='. $cgi->param('agentnum') -% if length($agentname); -%my $baselink = $p. "search/cust_bill_pkg.cgi?$dateagentlink"; -%my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; -% - - <% include("/elements/header.html", "$agentname Sales Tax Report - ". ( $beginning ? time2str('%h %o %Y ', $beginning ) @@ -535,5 +164,376 @@ +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my $user = getotaker; + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + +my $join_cust = " + JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) +"; +my $from_join_cust = " + FROM cust_bill_pkg + $join_cust +"; +my $join_pkg = " + LEFT JOIN cust_pkg USING ( pkgnum ) + LEFT JOIN part_pkg USING ( pkgpart ) +"; + +my $where = "WHERE _date >= $beginning AND _date <= $ending "; +my @base_param = qw( county county state state country ); +if ( $conf->exists('tax-ship_address') ) { + + $where .= " + AND ( ( ( ship_last IS NULL OR ship_last = '' ) + AND ( county = ? OR ? = '' ) + AND ( state = ? OR ? = '' ) + AND country = ? + ) + OR ( ship_last IS NOT NULL AND ship_last != '' + AND ( ship_county = ? OR ? = '' ) + AND ( ship_state = ? OR ? = '' ) + AND ship_country = ? + ) + ) + "; + # AND payby != 'COMP' + + push @base_param, @base_param; + +} else { + + $where .= " + AND ( county = ? OR ? = '' ) + AND ( state = ? OR ? = '' ) + AND country = ? + "; + # AND payby != 'COMP' + +} + +my $agentname = ''; +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + my $agent = qsearchs('agent', { 'agentnum' => $1 } ); + die "agent not found" unless $agent; + $agentname = $agent->agent; + $where .= ' AND agentnum = '. $agent->agentnum; +} + +my $gotcust = " + WHERE 0 < ( SELECT COUNT(*) FROM cust_main +"; +if ( $conf->exists('tax-ship_address') ) { + + $gotcust .= " + WHERE + + ( cust_main_county.country = cust_main.country + OR cust_main_county.country = cust_main.ship_country + ) + + AND + + ( + + ( ( ship_last IS NULL OR ship_last = '' ) + AND ( cust_main_county.country = cust_main.country ) + AND ( cust_main_county.state = cust_main.state + OR cust_main_county.state = '' + OR cust_main_county.state IS NULL ) + AND ( cust_main_county.county = cust_main.county + OR cust_main_county.county = '' + OR cust_main_county.county IS NULL ) + ) + + OR + + ( ship_last IS NOT NULL AND ship_last != '' + AND ( cust_main_county.country = cust_main.ship_country ) + AND ( cust_main_county.state = cust_main.ship_state + OR cust_main_county.state = '' + OR cust_main_county.state IS NULL ) + AND ( cust_main_county.county = cust_main.ship_county + OR cust_main_county.county = '' + OR cust_main_county.county IS NULL ) + ) + + ) + + LIMIT 1 + ) + "; + +} else { + + $gotcust .= " + WHERE ( cust_main.county = cust_main_county.county + OR cust_main_county.county = '' + OR cust_main_county.county IS NULL ) + AND ( cust_main.state = cust_main_county.state + OR cust_main_county.state = '' + OR cust_main_county.state IS NULL ) + AND ( cust_main.country = cust_main_county.country ) + LIMIT 1 + ) + "; + +} + +my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0, 0 ); +my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0 ); +my $out = 'Out of taxable region(s)'; +my %regions = (); +foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) { + #warn $r->county. ' '. $r->state. ' '. $r->country. "\n"; + + my $label = getlabel($r); + $regions{$label}->{'label'} = $label; + $regions{$label}->{'url_param'} = join(';', map "$_=".$r->$_(), qw( county state country ) ); + + my @param = @base_param; + my $mywhere = $where; + + if ( $r->taxclass ) { + $mywhere .= " AND taxclass = ? "; + push @param, 'taxclass'; + $regions{$label}->{'url_param'} .= ';taxclass='. $r->taxclass + if $cgi->param('show_taxclasses'); + } + + my $fromwhere = $from_join_cust. $join_pkg. $mywhere. " AND payby != 'COMP' "; + +# my $label = getlabel($r); +# $regions{$label}->{'label'} = $label; + + my $nottax = 'pkgnum != 0'; + + ## calculate total for this region + + my $t = scalar_sql($r, \@param, + "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax" + ); + $total += $t; + $regions{$label}->{'total'} += $t; + + ## calculate customer-exemption for this region + +## my $taxable = $t; + +# my($taxable, $x_cust) = (0, 0); +# foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i } +# qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { +# $taxable += scalar_sql($r, \@param, +# "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )" +# ); +# +# $x_cust += scalar_sql($r, \@param, +# "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'" +# ); +# } + + my $x_cust = scalar_sql($r, \@param, + "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) + $fromwhere AND $nottax AND tax = 'Y' " + ); + + $exempt_cust += $x_cust; + $regions{$label}->{'exempt_cust'} += $x_cust; + + ## calculate package-exemption for this region + + my $x_pkg = scalar_sql($r, \@param, + "SELECT SUM( + ( CASE WHEN part_pkg.setuptax = 'Y' + THEN cust_bill_pkg.setup + ELSE 0 + END + ) + + + ( CASE WHEN part_pkg.recurtax = 'Y' + THEN cust_bill_pkg.recur + ELSE 0 + END + ) + ) + $fromwhere + AND $nottax + AND ( + ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) + OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) + ) + AND ( tax != 'Y' OR tax IS NULL ) + " + ); + $exempt_pkg += $x_pkg; + $regions{$label}->{'exempt_pkg'} += $x_pkg; + + ## calculate monthly exemption (texas tax) for this region + + # count up all the cust_tax_exempt_pkg records associated with + # the actual line items. + + my $x_monthly = scalar_sql($r, \@param, + "SELECT SUM(amount) + FROM cust_tax_exempt_pkg + JOIN cust_bill_pkg USING ( billpkgnum ) + $join_cust $join_pkg + $mywhere" + ); +# if ( $x_monthly ) { +# #warn $r->taxnum(). ": $x_monthly\n"; +# $taxable -= $x_monthly; +# } + + $exempt_monthly += $x_monthly; + $regions{$label}->{'exempt_monthly'} += $x_monthly; + + my $taxable = $t - $x_cust - $x_pkg - $x_monthly; + + $tot_taxable += $taxable; + $regions{$label}->{'taxable'} += $taxable; + + $owed += $taxable * ($r->tax/100); + $regions{$label}->{'owed'} += $taxable * ($r->tax/100); + + if ( defined($regions{$label}->{'rate'}) + && $regions{$label}->{'rate'} != $r->tax.'%' ) { + $regions{$label}->{'rate'} = 'variable'; + } else { + $regions{$label}->{'rate'} = $r->tax.'%'; + } + +} + +my $taxwhere = "$from_join_cust $where AND payby != 'COMP' "; +my @taxparam = @base_param; +my %base_regions = (); +#foreach my $label ( keys %regions ) { +foreach my $r ( + qsearch( 'cust_main_county', + {}, + 'DISTINCT ON (country, state, county, taxname) *', + $gotcust + ) +) { + + #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n"; + + my $label = getlabel($r); + + #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' "; + #my @param = @base_param; + + #match itemdesc if necessary! + my $named_tax = + $r->taxname + ? 'AND itemdesc = '. dbh->quote($r->taxname) + : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; + my $x = scalar_sql($r, \@taxparam, + "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $taxwhere ". + "AND pkgnum = 0 $named_tax", + ); + $tax += $x; + $regions{$label}->{'tax'} += $x; + + if ( $cgi->param('show_taxclasses') ) { + my $base_label = getlabel($r, 'no_taxclass'=>1 ); + $base_regions{$base_label}->{'label'} = $base_label; + $base_regions{$base_label}->{'url_param'} = + join(';', map "$_=".$r->$_(), qw( county state country ) ); + $base_regions{$base_label}->{'tax'} += $x; + } + +} + +#ordering +my @regions = + map $regions{$_}, + sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } + keys %regions; + +my @base_regions = + map $base_regions{$_}, + sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } + keys %base_regions; + +push @regions, { + 'label' => 'Total', + 'url_param' => '', + 'total' => $total, + 'exempt_cust' => $exempt_cust, + 'exempt_pkg' => $exempt_pkg, + 'exempt_monthly' => $exempt_monthly, + 'taxable' => $tot_taxable, + 'rate' => '', + 'owed' => $owed, + 'tax' => $tax, +}; + +#-- + +sub getlabel { + my $r = shift; + my %opt = @_; + + my $label; + if ( + $r->tax == 0 + && ! scalar( qsearch('cust_main_county', { 'state' => $r->state, + 'county' => $r->county, + 'country' => $r->country, + 'tax' => { op=>'>', value=>0 }, + } + ) + ) + + ) { + #kludge to avoid "will not stay shared" warning + my $out = 'Out of taxable region(s)'; + $label = $out; + } elsif ( $r->taxname ) { + $label = $r->taxname; +# $regions{$label}->{'taxname'} = $label; +# push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country ); + } else { + $label = $r->country; + $label = $r->state.", $label" if $r->state; + $label = $r->county." county, $label" if $r->county; + $label = "$label (". $r->taxclass. ")" + if $r->taxclass + && $cgi->param('show_taxclasses') + && ! $opt{'no_taxclass'}; + #$label = $r->taxname. " ($label)" if $r->taxname; + } + return $label; +} + +#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up +#to FS::Report or FS::Record or who the fuck knows where) +sub scalar_sql { + my( $r, $param, $sql ) = @_; + #warn "$sql\n"; + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute( map $r->$_(), @$param ) + or die "Unexpected error executing statement $sql: ". $sth->errstr; + $sth->fetchrow_arrayref->[0] || 0; +} + + +my $dateagentlink = "begin=$beginning;end=$ending"; +$dateagentlink .= ';agentnum='. $cgi->param('agentnum') + if length($agentname); +my $baselink = $p. "search/cust_bill_pkg.cgi?$dateagentlink"; +my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; + diff --git a/httemplate/search/report_tax.html b/httemplate/search/report_tax.html index 6e78d354a..35b290c19 100755 --- a/httemplate/search/report_tax.html +++ b/httemplate/search/report_tax.html @@ -34,3 +34,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + diff --git a/httemplate/search/sql.html b/httemplate/search/sql.html index 681a95d60..5f64ebc28 100644 --- a/httemplate/search/sql.html +++ b/httemplate/search/sql.html @@ -5,3 +5,9 @@ || eidiot('Empty query') ), ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Raw SQL'); + + diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi index 486b94d94..324729b6a 100644 --- a/httemplate/search/sqlradius.cgi +++ b/httemplate/search/sqlradius.cgi @@ -84,8 +84,11 @@ % } - <%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); + ### # parse cgi params ### diff --git a/httemplate/search/sqlradius.html b/httemplate/search/sqlradius.html index 1fd50da8d..660a54f3c 100644 --- a/httemplate/search/sqlradius.html +++ b/httemplate/search/sqlradius.html @@ -51,3 +51,9 @@ <% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); + + diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index 592aa150a..a702d604b 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -1,128 +1,3 @@ -%my @extra_sql = (); -% -% if ( $cgi->param('domain') ) { -% my $svc_domain = -% qsearchs('svc_domain', { 'domain' => $cgi->param('domain') } ); -% unless ( $svc_domain ) { -% #it would be nice if this looked more like the other "not found" -% #errors, but this will do for now. -% eidiot "Domain ". $cgi->param('domain'). " not found at all"; -% } else { -% push @extra_sql, 'domsvc = '. $svc_domain->svcnum; -% } -% } -% -%my $orderby = 'ORDER BY svcnum'; -%if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { -% -% push @extra_sql, 'pkgnum IS NULL' -% if $cgi->param('magic') eq 'unlinked'; -% -% if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { -% my $sortby = $1; -% $sortby = "LOWER($sortby)" -% if $sortby eq 'username'; -% push @extra_sql, "$sortby IS NOT NULL" -% if $sortby eq 'uid'; -% $orderby = "ORDER BY $sortby"; -% } -% -%} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) { -% push @extra_sql, "popnum = $1"; -% $orderby = "ORDER BY LOWER(username)"; -%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { -% push @extra_sql, "svcpart = $1"; -% $orderby = "ORDER BY uid"; -% #$orderby = "ORDER BY svcnum"; -%} else { -% $orderby = "ORDER BY uid"; -% -% my @username_sql; -% -% my %username_type; -% foreach ( $cgi->param('username_type') ) { -% $username_type{$_}++; -% } -% -% $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text -% my $username = $1; -% -% push @username_sql, "username ILIKE '$username'" -% if $username_type{'Exact'} -% || $username_type{'Fuzzy'}; -% -% push @username_sql, "username ILIKE '\%$username\%'" -% if $username_type{'Substring'} -% || $username_type{'All'}; -% -% if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { -% &FS::svc_acct::check_and_rebuild_fuzzyfiles; -% my $all_username = &FS::svc_acct::all_username; -% -% my %username; -% if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { -% foreach ( amatch($username, [ qw(i) ], @$all_username) ) { -% $username{$_}++; -% } -% } -% -% #if ($username_type{'Sound-alike'}) { -% #} -% -% push @username_sql, "username = '$_'" -% foreach (keys %username); -% -% } -% -% push @extra_sql, '( '. join( ' OR ', @username_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 = -% scalar(@extra_sql) -% ? ' WHERE '. join(' AND ', @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{$_}), -%# keys %svc_acct -%# ); -%#} -% -%my $sql_query = { -% 'table' => 'svc_acct', -% 'hashref' => {}, # \%svc_acct, -% 'select' => join(', ', -% 'svc_acct.*', -% 'part_svc.svc', -% 'cust_main.custnum', -% FS::UI::Web::cust_sql_fields(), -% ), -% 'extra_sql' => "$extra_sql $orderby", -% 'addl_from' => $addl_from, -%}; -% -%my $link = [ "${p}view/svc_acct.cgi?", 'svcnum' ]; -%my $link_cust = sub { -% my $svc_acct = shift; -% if ( $svc_acct->custnum ) { -% [ "${p}view/cust_main.cgi?", 'custnum' ]; -% } else { -% ''; -% } -%}; -% -% <% include( 'elements/search.html', 'title' => 'Account Search Results', 'name' => 'accounts', @@ -145,9 +20,155 @@ $link, $link, $link, - ( map { $link_cust } + ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], + 'align' => 'rlll'. FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List services'); + +my @extra_sql = (); + + if ( $cgi->param('domain') ) { + my $svc_domain = + qsearchs('svc_domain', { 'domain' => $cgi->param('domain') } ); + unless ( $svc_domain ) { + #it would be nice if this looked more like the other "not found" + #errors, but this will do for now. + eidiot "Domain ". $cgi->param('domain'). " not found at all"; + } else { + push @extra_sql, 'domsvc = '. $svc_domain->svcnum; + } + } + +my $orderby = 'ORDER BY svcnum'; +if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { + + push @extra_sql, 'pkgnum IS NULL' + if $cgi->param('magic') eq 'unlinked'; + + if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { + my $sortby = $1; + $sortby = "LOWER($sortby)" + if $sortby eq 'username'; + push @extra_sql, "$sortby IS NOT NULL" + if $sortby eq 'uid'; + $orderby = "ORDER BY $sortby"; + } + +} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) { + push @extra_sql, "popnum = $1"; + $orderby = "ORDER BY LOWER(username)"; +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + push @extra_sql, "svcpart = $1"; + $orderby = "ORDER BY uid"; + #$orderby = "ORDER BY svcnum"; +} else { + $orderby = "ORDER BY uid"; + + my @username_sql; + + my %username_type; + foreach ( $cgi->param('username_type') ) { + $username_type{$_}++; + } + + $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text + my $username = $1; + + push @username_sql, "username ILIKE '$username'" + if $username_type{'Exact'} + || $username_type{'Fuzzy'}; + + push @username_sql, "username ILIKE '\%$username\%'" + if $username_type{'Substring'} + || $username_type{'All'}; + + if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { + &FS::svc_acct::check_and_rebuild_fuzzyfiles; + my $all_username = &FS::svc_acct::all_username; + + my %username; + if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { + foreach ( amatch($username, [ qw(i) ], @$all_username) ) { + $username{$_}++; + } + } + + #if ($username_type{'Sound-alike'}) { + #} + + push @username_sql, "username = '$_'" + foreach (keys %username); + + } + + push @extra_sql, '( '. join( ' OR ', @username_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 = + scalar(@extra_sql) + ? ' WHERE '. join(' AND ', @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{$_}), +# keys %svc_acct +# ); +#} + +my $sql_query = { + 'table' => 'svc_acct', + 'hashref' => {}, # \%svc_acct, + 'select' => join(', ', + 'svc_acct.*', + 'part_svc.svc', + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => "$extra_sql $orderby", + 'addl_from' => $addl_from, +}; + +my $link = [ "${p}view/svc_acct.cgi?", 'svcnum' ]; +my $link_cust = sub { + my $svc_acct = shift; + if ( $svc_acct->custnum ) { + [ "${p}view/cust_main.cgi?", 'custnum' ]; + } else { + ''; + } +}; + + + diff --git a/httemplate/search/svc_broadband.cgi b/httemplate/search/svc_broadband.cgi index 297d74c1d..1bbdbfcdb 100755 --- a/httemplate/search/svc_broadband.cgi +++ b/httemplate/search/svc_broadband.cgi @@ -1,3 +1,6 @@ +%die "access denied" +% unless $FS::CurrentUser::CurrentUser->access_right('List services'); +% %my $conf = new FS::Conf; % %my @svc_broadband = (); diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi index 8643ea0dc..b14a1cc4f 100755 --- a/httemplate/search/svc_domain.cgi +++ b/httemplate/search/svc_domain.cgi @@ -1,70 +1,3 @@ -%my $conf = new FS::Conf; -% -%my $orderby = 'ORDER BY svcnum'; -%my %svc_domain = (); -%my @extra_sql = (); -%if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { -% -% push @extra_sql, 'pkgnum IS NULL' -% if $cgi->param('magic') eq 'unlinked'; -% -% if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { -% my $sortby = $1; -% $orderby = "ORDER BY $sortby"; -% } -% -%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { -% push @extra_sql, "svcpart = $1"; -%} else { -% $cgi->param('domain') =~ /^([\w\-\.]+)$/; -% $svc_domain{'domain'} = $1; -%} -% -%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', -% 'hashref' => \%svc_domain, -% 'select' => join(', ', -% 'svc_domain.*', -% 'part_svc.svc', -% 'cust_main.custnum', -% FS::UI::Web::cust_sql_fields(), -% ), -% 'extra_sql' => "$extra_sql $orderby", -% 'addl_from' => $addl_from, -%}; -% -%my $link = [ "${p}view/svc_domain.cgi?", 'svcnum' ]; -% -%#smaller false laziness w/svc_*.cgi here -%my $link_cust = sub { -% my $svc_x = shift; -% $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; -%}; -% -% <% include( 'elements/search.html', 'title' => "Domain Search Results", 'name' => 'domains', @@ -84,9 +17,94 @@ 'links' => [ $link, $link, $link, - ( map { $link_cust } + ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], + 'align' => 'rll'. FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + FS::UI::Web::cust_styles(), + ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List services'); + +my $conf = new FS::Conf; + +my $orderby = 'ORDER BY svcnum'; +my %svc_domain = (); +my @extra_sql = (); +if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { + + push @extra_sql, 'pkgnum IS NULL' + if $cgi->param('magic') eq 'unlinked'; + + if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { + my $sortby = $1; + $orderby = "ORDER BY $sortby"; + } + +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + push @extra_sql, "svcpart = $1"; +} else { + $cgi->param('domain') =~ /^([\w\-\.]+)$/; + $svc_domain{'domain'} = $1; +} + +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', + 'hashref' => \%svc_domain, + 'select' => join(', ', + 'svc_domain.*', + 'part_svc.svc', + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => "$extra_sql $orderby", + 'addl_from' => $addl_from, +}; + +my $link = [ "${p}view/svc_domain.cgi?", 'svcnum' ]; + +#smaller false laziness w/svc_*.cgi here +my $link_cust = sub { + my $svc_x = shift; + $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +}; + + diff --git a/httemplate/search/svc_external.cgi b/httemplate/search/svc_external.cgi index 5502bfc25..2710d75bc 100755 --- a/httemplate/search/svc_external.cgi +++ b/httemplate/search/svc_external.cgi @@ -1,3 +1,6 @@ +%die "access denied" +% unless $FS::CurrentUser::CurrentUser->access_right('List services'); +% %my $conf = new FS::Conf; % %my @svc_external = (); diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi index 4d44c9ca6..eeb4c1075 100755 --- a/httemplate/search/svc_forward.cgi +++ b/httemplate/search/svc_forward.cgi @@ -1,99 +1,3 @@ -%my $conf = new FS::Conf; -% -%my $orderby = 'ORDER BY svcnum'; -%my @extra_sql = (); -%if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { -% -% push @extra_sql, 'pkgnum IS NULL' -% if $cgi->param('magic') eq 'unlinked'; -% -% if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { -% my $sortby = $1; -% $orderby = "ORDER BY $sortby"; -% } -% -%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { -% push @extra_sql, "svcpart = $1"; -%} -% -%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 $addl_from $extra_sql"; -%my $sql_query = { -% 'table' => 'svc_forward', -% 'hashref' => {}, -% 'select' => join(', ', -% 'svc_forward.*', -% 'part_svc.svc', -% 'cust_main.custnum', -% FS::UI::Web::cust_sql_fields(), -% ), -% 'extra_sql' => "$extra_sql $orderby", -% 'addl_from' => $addl_from, -%}; -% -%# Service #
(click to view forward) -%# Mail to
(click to view account) -%# Forwards to
(click to view account) -% -%my $link = [ "${p}view/svc_forward.cgi?", 'svcnum' ]; -% -%my $format_src = sub { -% my $svc_forward = shift; -% if ( $svc_forward->srcsvc_acct ) { -% $svc_forward->srcsvc_acct->email; -% } else { -% my $src = $svc_forward->src; -% $src = "(anything)$src" if $src =~ /^@/; -% $src; -% } -%}; -% -%my $link_src = sub { -% my $svc_forward = shift; -% if ( $svc_forward->srcsvc_acct ) { -% [ "${p}view/svc_acct.cgi?", 'srcsvc' ]; -% } else { -% ''; -% } -%}; -% -%my $format_dst = sub { -% my $svc_forward = shift; -% if ( $svc_forward->dstsvc_acct ) { -% $svc_forward->dstsvc_acct->email; -% } else { -% $svc_forward->dst; -% } -%}; -% -%my $link_dst = sub { -% my $svc_forward = shift; -% if ( $svc_forward->dstsvc_acct ) { -% [ "${p}view/svc_acct.cgi?", 'dstsvc' ]; -% } else { -% ''; -% } -%}; -% -%#smaller false laziness w/svc_*.cgi here -%my $link_cust = sub { -% my $svc_x = shift; -% $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; -%}; -% -% <% include( 'elements/search.html', 'title' => "Mail forward Search Results", 'name' => 'mail forwards', @@ -116,9 +20,126 @@ $link, $link_src, $link_dst, - ( map { $link_cust } + ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], + 'align' => 'rlll'. FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List services'); + + +my $conf = new FS::Conf; + +my $orderby = 'ORDER BY svcnum'; +my @extra_sql = (); +if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { + + push @extra_sql, 'pkgnum IS NULL' + if $cgi->param('magic') eq 'unlinked'; + + if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { + my $sortby = $1; + $orderby = "ORDER BY $sortby"; + } + +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + push @extra_sql, "svcpart = $1"; +} + +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 $addl_from $extra_sql"; +my $sql_query = { + 'table' => 'svc_forward', + 'hashref' => {}, + 'select' => join(', ', + 'svc_forward.*', + 'part_svc.svc', + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => "$extra_sql $orderby", + 'addl_from' => $addl_from, +}; + +# Service #
(click to view forward) +# Mail to
(click to view account) +# Forwards to
(click to view account) + +my $link = [ "${p}view/svc_forward.cgi?", 'svcnum' ]; + +my $format_src = sub { + my $svc_forward = shift; + if ( $svc_forward->srcsvc_acct ) { + $svc_forward->srcsvc_acct->email; + } else { + my $src = $svc_forward->src; + $src = "(anything)$src" if $src =~ /^@/; + $src; + } +}; + +my $link_src = sub { + my $svc_forward = shift; + if ( $svc_forward->srcsvc_acct ) { + [ "${p}view/svc_acct.cgi?", 'srcsvc' ]; + } else { + ''; + } +}; + +my $format_dst = sub { + my $svc_forward = shift; + if ( $svc_forward->dstsvc_acct ) { + $svc_forward->dstsvc_acct->email; + } else { + $svc_forward->dst; + } +}; + +my $link_dst = sub { + my $svc_forward = shift; + if ( $svc_forward->dstsvc_acct ) { + [ "${p}view/svc_acct.cgi?", 'dstsvc' ]; + } else { + ''; + } +}; + +#smaller false laziness w/svc_*.cgi here +my $link_cust = sub { + my $svc_x = shift; + $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +}; + + diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi index 229dd5d89..0c1d57887 100644 --- a/httemplate/search/svc_phone.cgi +++ b/httemplate/search/svc_phone.cgi @@ -1,70 +1,3 @@ -%my $conf = new FS::Conf; -% -%my $orderby = 'ORDER BY svcnum'; -%my %svc_phone = (); -%my @extra_sql = (); -%if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { -% -% push @extra_sql, 'pkgnum IS NULL' -% if $cgi->param('magic') eq 'unlinked'; -% -% if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { -% my $sortby = $1; -% $orderby = "ORDER BY $sortby"; -% } -% -%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { -% push @extra_sql, "svcpart = $1"; -%} else { -% $cgi->param('phonenum') =~ /^([\d\- ]+)$/; -% ( $svc_phone{'phonenum'} = $1 ) =~ s/\D//g; -%} -% -%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_phone) ? ' AND ' : ' WHERE ' ). -% join(' AND ', @extra_sql ); -%} -% -%my $count_query = "SELECT COUNT(*) FROM svc_phone $addl_from "; -%if ( keys %svc_phone ) { -% $count_query .= ' WHERE '. -% join(' AND ', map "$_ = ". dbh->quote($svc_phone{$_}), -% keys %svc_phone -% ); -%} -%$count_query .= $extra_sql; -% -%my $sql_query = { -% 'table' => 'svc_phone', -% 'hashref' => \%svc_phone, -% 'select' => join(', ', -% 'svc_phone.*', -% 'part_svc.svc', -% 'cust_main.custnum', -% FS::UI::Web::cust_sql_fields(), -% ), -% 'extra_sql' => "$extra_sql $orderby", -% 'addl_from' => $addl_from, -%}; -% -%my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ]; -% -%#smaller false laziness w/svc_*.cgi here -%my $link_cust = sub { -% my $svc_x = shift; -% $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; -%}; -% -% <% include( 'elements/search.html', 'title' => "Phone number search results", 'name' => 'phone numbers', @@ -87,9 +20,96 @@ $link, $link, $link, - ( map { $link_cust } + ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], + 'align' => 'rlrr'. FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List services'); + +my $conf = new FS::Conf; + +my $orderby = 'ORDER BY svcnum'; +my %svc_phone = (); +my @extra_sql = (); +if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { + + push @extra_sql, 'pkgnum IS NULL' + if $cgi->param('magic') eq 'unlinked'; + + if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { + my $sortby = $1; + $orderby = "ORDER BY $sortby"; + } + +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + push @extra_sql, "svcpart = $1"; +} else { + $cgi->param('phonenum') =~ /^([\d\- ]+)$/; + ( $svc_phone{'phonenum'} = $1 ) =~ s/\D//g; +} + +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_phone) ? ' AND ' : ' WHERE ' ). + join(' AND ', @extra_sql ); +} + +my $count_query = "SELECT COUNT(*) FROM svc_phone $addl_from "; +if ( keys %svc_phone ) { + $count_query .= ' WHERE '. + join(' AND ', map "$_ = ". dbh->quote($svc_phone{$_}), + keys %svc_phone + ); +} +$count_query .= $extra_sql; + +my $sql_query = { + 'table' => 'svc_phone', + 'hashref' => \%svc_phone, + 'select' => join(', ', + 'svc_phone.*', + 'part_svc.svc', + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => "$extra_sql $orderby", + 'addl_from' => $addl_from, +}; + +my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ]; + +#smaller false laziness w/svc_*.cgi here +my $link_cust = sub { + my $svc_x = shift; + $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +}; + + diff --git a/httemplate/search/svc_www.cgi b/httemplate/search/svc_www.cgi index ae1482b9f..d3b0549c4 100755 --- a/httemplate/search/svc_www.cgi +++ b/httemplate/search/svc_www.cgi @@ -1,60 +1,3 @@ -%#my $conf = new FS::Conf; -% -%my $orderby = 'ORDER BY svcnum'; -%my @extra_sql = (); -%if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { -% -% push @extra_sql, 'pkgnum IS NULL' -% if $cgi->param('magic') eq 'unlinked'; -% -% if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { -% my $sortby = $1; -% $orderby = "ORDER BY $sortby"; -% } -% -%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { -% push @extra_sql, "svcpart = $1"; -%} -% -%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_www'; -%my $sql_query = { -% 'table' => 'svc_www', -% 'hashref' => {}, -% 'select' => join(', ', -% 'svc_www.*', -% 'part_svc.svc', -% 'cust_main.custnum', -% FS::UI::Web::cust_sql_fields(), -% ), -% 'extra_sql' => $orderby, -% 'addl_from' => $addl_from, -%}; -% -%my $link = [ "${p}view/svc_www.cgi?", 'svcnum', ]; -%#my $dlink = [ "${p}view/svc_www.cgi?", 'svcnum', ]; -%my $ulink = [ "${p}view/svc_acct.cgi?", 'usersvc', ]; -% -%#smaller false laziness w/svc_*.cgi here -%my $link_cust = sub { -% my $svc_x = shift; -% $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; -%}; -% -% <% include( 'elements/search.html', 'title' => 'Virtual Host Search Results', 'name' => 'virtual hosts', @@ -83,9 +26,86 @@ $link, '', $ulink, - ( map { $link_cust } + ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], + 'align' => 'rlll'. FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List services'); + +#my $conf = new FS::Conf; + +my $orderby = 'ORDER BY svcnum'; +my @extra_sql = (); +if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { + + push @extra_sql, 'pkgnum IS NULL' + if $cgi->param('magic') eq 'unlinked'; + + if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { + my $sortby = $1; + $orderby = "ORDER BY $sortby"; + } + +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + push @extra_sql, "svcpart = $1"; +} + +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_www'; +my $sql_query = { + 'table' => 'svc_www', + 'hashref' => {}, + 'select' => join(', ', + 'svc_www.*', + 'part_svc.svc', + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => $orderby, + 'addl_from' => $addl_from, +}; + +my $link = [ "${p}view/svc_www.cgi?", 'svcnum', ]; +#my $dlink = [ "${p}view/svc_www.cgi?", 'svcnum', ]; +my $ulink = [ "${p}view/svc_acct.cgi?", 'usersvc', ]; + +#smaller false laziness w/svc_*.cgi here +my $link_cust = sub { + my $svc_x = shift; + $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +}; + + -- 2.11.0