From fed12e8214a8a951377961ccbe7a09171b1e38c2 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Tue, 5 Feb 2013 16:23:29 -0800 Subject: [PATCH] make customer location columns available in various reports, #940 --- FS/FS/UI/Web.pm | 100 +++++++++++++++++++++----- FS/FS/cust_main/Search.pm | 6 ++ FS/FS/cust_pkg.pm | 10 +-- FS/FS/svc_Common.pm | 2 +- FS/FS/svc_acct.pm | 2 +- FS/FS/svc_broadband.pm | 2 +- httemplate/search/cust_bill.html | 3 +- httemplate/search/cust_bill_event.cgi | 2 +- httemplate/search/cust_bill_pay.html | 8 +-- httemplate/search/cust_bill_pkg.cgi | 10 +-- httemplate/search/cust_bill_pkg_discount.html | 12 ++-- httemplate/search/cust_credit.html | 4 +- httemplate/search/cust_credit_bill.html | 8 +-- httemplate/search/cust_credit_bill_pkg.html | 4 +- httemplate/search/cust_credit_refund.html | 8 +-- httemplate/search/cust_event.html | 8 ++- httemplate/search/cust_pkg_discount.html | 4 +- httemplate/search/cust_svc.html | 2 +- httemplate/search/cust_tax_exempt.cgi | 4 +- httemplate/search/cust_tax_exempt_pkg.cgi | 7 +- httemplate/search/inventory_item.html | 2 +- httemplate/search/phone_avail.html | 4 +- httemplate/search/quotation.html | 2 +- httemplate/search/svc_dish.cgi | 2 +- httemplate/search/svc_domain.cgi | 2 +- httemplate/search/svc_external.cgi | 2 +- httemplate/search/svc_forward.cgi | 2 +- httemplate/search/svc_hardware.cgi | 4 +- httemplate/search/svc_www.cgi | 2 +- httemplate/search/unearned_detail.html | 8 +-- httemplate/search/unprovisioned_services.html | 3 +- 31 files changed, 158 insertions(+), 81 deletions(-) diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm index c2ea0a61c..91d2034dd 100644 --- a/FS/FS/UI/Web.pm +++ b/FS/FS/UI/Web.pm @@ -235,20 +235,20 @@ sub cust_header { '(service) Name' => 'ship_contact', '(bill) Company' => 'company', '(service) Company' => 'ship_company', - 'Address 1' => 'address1', - 'Address 2' => 'address2', - 'City' => 'city', - 'State' => 'state', - 'Zip' => 'zip', + 'Address 1' => 'bill_address1', + 'Address 2' => 'bill_address2', + 'City' => 'bill_city', + 'State' => 'bill_state', + 'Zip' => 'bill_zip', 'Country' => 'country_full', 'Day phone' => 'daytime', # XXX should use msgcat, but how? 'Night phone' => 'night', # XXX should use msgcat, but how? 'Fax number' => 'fax', - '(bill) Address 1' => 'address1', - '(bill) Address 2' => 'address2', - '(bill) City' => 'city', - '(bill) State' => 'state', - '(bill) Zip' => 'zip', + '(bill) Address 1' => 'bill_address1', + '(bill) Address 2' => 'bill_address2', + '(bill) City' => 'bill_city', + '(bill) State' => 'bill_state', + '(bill) Zip' => 'bill_zip', '(bill) Country' => 'country_full', '(bill) Day phone' => 'daytime', # XXX should use msgcat, but how? '(bill) Night phone' => 'night', # XXX should use msgcat, but how? @@ -335,17 +335,21 @@ setting is supplied, the cust-fields configuration value. sub cust_sql_fields { my @fields = qw( last first company ); - push @fields, map "ship_$_", @fields; - push @fields, 'country'; +# push @fields, map "ship_$_", @fields; cust_header(@_); #inefficientish, but tiny lists and only run once per page - my @add_fields = qw( address1 address2 city state zip daytime night fax ); - push @fields, - grep { my $field = $_; grep { $_ eq $field } @cust_fields } - ( @add_fields, ( map "ship_$_", @add_fields ), 'payby' ); - + my @location_fields; + foreach my $field (qw( address1 address2 city state zip )) { + foreach my $pre ('bill_','ship_') { + if ( grep { $_ eq $pre.$field } @cust_fields ) { + push @location_fields, $pre.'location.'.$field.' AS '.$pre.$field; + } + } + } + + push @fields, 'payby' if grep { $_ eq 'payby'} @cust_fields; push @fields, 'agent_custid'; my @extra_fields = (); @@ -353,7 +357,67 @@ sub cust_sql_fields { push @extra_fields, FS::cust_main->balance_sql . " AS current_balance"; } - map("cust_main.$_", @fields), @extra_fields; + map("cust_main.$_", @fields), @location_fields, @extra_fields; +} + +=item join_cust_main [ TABLE[.CUSTNUM] ] [ LOCATION_TABLE[.LOCATIONNUM] ] + +Returns an SQL join phrase for the FROM clause so that the fields listed +in L will be available. Currently joins to cust_main +itself, as well as cust_location (under the aliases 'bill_location' and +'ship_location') if address fields are needed. L should have +been called already. + +All of these will be left joins; if you want to exclude rows with no linked +cust_main record (or bill_location/ship_location), you can do so in the +WHERE clause. + +TABLE is the table containing the custnum field. If CUSTNUM (a field name +in that table) is specified, that field will be joined to cust_main.custnum. +Otherwise, this function will assume the field is named "custnum". If the +argument isn't present at all, the join will just say "USING (custnum)", +which might work. + +LOCATION_TABLE is an optional table name to use for joining ship_location, +in case your query also includes package information and you want the +"service address" columns to reflect package addresses. + +=cut + +sub join_cust_main { + my ($cust_table, $location_table) = @_; + my ($custnum, $locationnum); + ($cust_table, $custnum) = split(/\./, $cust_table); + $custnum ||= 'custnum'; + ($location_table, $locationnum) = split(/\./, $location_table); + $locationnum ||= 'locationnum'; + + my $sql = ' LEFT JOIN cust_main '; + if ( $cust_table ) { + $sql .= "ON (cust_main.custnum = $cust_table.$custnum)"; + } else { + $sql .= "USING (custnum)"; + } + + if ( !@cust_fields or grep /^bill_/, @cust_fields ) { + + $sql .= ' LEFT JOIN cust_location bill_location'. + ' ON (bill_location.locationnum = cust_main.bill_locationnum)'; + + } + + if ( !@cust_fields or grep /^ship_/, @cust_fields ) { + + if (!$location_table) { + $location_table = 'cust_main'; + $locationnum = 'ship_locationnum'; + } + + $sql .= ' LEFT JOIN cust_location ship_location'. + " ON (ship_location.locationnum = $location_table.$locationnum) "; + } + + $sql; } =item cust_fields OBJECT [ CUST_FIELDS_VALUE ] diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 1047890c3..349f3e348 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -814,6 +814,12 @@ sub search { my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; my $addl_from = ''; + # always make address fields available in results + for my $pre ('bill_', 'ship_') { + $addl_from .= + 'LEFT JOIN cust_location AS '.$pre.'location '. + 'ON (cust_main.'.$pre.'locationnum = '.$pre.'location.locationnum) '; + } my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql"; diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index 6d85a11f2..c2077c533 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -603,7 +603,9 @@ replace methods. sub check { my $self = shift; - $self->locationnum('') if !$self->locationnum || $self->locationnum == -1; + if ( !$self->locationnum or $self->locationnum == -1 ) { + $self->set('locationnum', $self->cust_main->ship_locationnum); + } my $error = $self->ut_numbern('pkgnum') @@ -3817,10 +3819,10 @@ sub search { my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; - my $addl_from = 'LEFT JOIN cust_main USING ( custnum ) '. - 'LEFT JOIN part_pkg USING ( pkgpart ) '. + my $addl_from = 'LEFT JOIN part_pkg USING ( pkgpart ) '. 'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '. - 'LEFT JOIN cust_location USING ( locationnum ) '; + 'LEFT JOIN cust_location USING ( locationnum ) '. + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); my $select; my $count_query; diff --git a/FS/FS/svc_Common.pm b/FS/FS/svc_Common.pm index 21710226a..f7f0b4094 100644 --- a/FS/FS/svc_Common.pm +++ b/FS/FS/svc_Common.pm @@ -1324,7 +1324,7 @@ sub search { 'LEFT JOIN cust_svc USING ( svcnum )', 'LEFT JOIN part_svc USING ( svcpart )', 'LEFT JOIN cust_pkg USING ( pkgnum )', - 'LEFT JOIN cust_main USING ( custnum )', + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'), ); my @where = (); diff --git a/FS/FS/svc_acct.pm b/FS/FS/svc_acct.pm index bd71b0ba1..39f969f09 100644 --- a/FS/FS/svc_acct.pm +++ b/FS/FS/svc_acct.pm @@ -2829,7 +2829,7 @@ sub search { ' LEFT JOIN cust_svc USING ( svcnum ) ', ' LEFT JOIN part_svc USING ( svcpart ) ', ' LEFT JOIN cust_pkg USING ( pkgnum ) ', - ' LEFT JOIN cust_main USING ( custnum ) ', + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg') ); my @where = (); diff --git a/FS/FS/svc_broadband.pm b/FS/FS/svc_broadband.pm index af8135304..1a3adf414 100755 --- a/FS/FS/svc_broadband.pm +++ b/FS/FS/svc_broadband.pm @@ -182,7 +182,7 @@ sub search { 'LEFT JOIN cust_svc USING ( svcnum )', 'LEFT JOIN part_svc USING ( svcpart )', 'LEFT JOIN cust_pkg USING ( pkgnum )', - 'LEFT JOIN cust_main USING ( custnum )', + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'), ); # based on FS::svc_acct::search, probably the most mature of the bunch diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 3c0530e4f..88cdaf5ab 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -62,7 +62,7 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List invoices'); -my $join_cust_main = 'LEFT JOIN cust_main USING ( custnum )'; +my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill'); #here is the agent virtualization my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; @@ -198,7 +198,6 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { }; } - my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; my $clink = sub { my $cust_bill = shift; diff --git a/httemplate/search/cust_bill_event.cgi b/httemplate/search/cust_bill_event.cgi index 90c89139c..00f7cd564 100644 --- a/httemplate/search/cust_bill_event.cgi +++ b/httemplate/search/cust_bill_event.cgi @@ -100,7 +100,7 @@ my $where = 'WHERE '. FS::cust_bill_event->search_sql_where( \%search ); my $join = 'LEFT JOIN part_bill_event USING ( eventpart ) '. 'LEFT JOIN cust_bill USING ( invnum ) '. - 'LEFT JOIN cust_main USING ( custnum ) '; + FS::UI::Web::join_cust_main('cust_bill'); my $sql_query = { 'table' => 'cust_bill_event', diff --git a/httemplate/search/cust_bill_pay.html b/httemplate/search/cust_bill_pay.html index 79de74985..871227aa4 100644 --- a/httemplate/search/cust_bill_pay.html +++ b/httemplate/search/cust_bill_pay.html @@ -117,8 +117,8 @@ my $where = 'WHERE '. join(' AND ', @search); # my $count_query = 'SELECT COUNT(*), SUM(amount) FROM cust_bill_pay - LEFT JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '. + LEFT JOIN cust_bill USING ( invnum ) '. + FS::UI::Web::join_cust_main('cust_bill') . $where; my $sql_query = { @@ -137,8 +137,8 @@ my $sql_query = { 'hashref' => {}, 'extra_sql' => $where, 'addl_from' => 'LEFT JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_pay USING ( paynum ) - LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum )', + LEFT JOIN cust_pay USING ( paynum ) '. + FS::UI::Web::join_cust_main('cust_bill') }; my $cust_bill_link = sub { diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 1e67e9320..6a9f8775e 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -222,9 +222,9 @@ if ( $conf->exists('enable_taxclasses') ) { # valid in both the tax and non-tax cases my $join_cust = - " LEFT JOIN cust_bill USING (invnum) - LEFT JOIN cust_main USING (custnum) - "; + " LEFT JOIN cust_bill USING (invnum)". + # use cust_pkg.locationnum if it exists + FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg'); #agent virtualization my $agentnums_sql = @@ -647,7 +647,7 @@ $where &&= "WHERE $where"; my $query = { 'table' => 'cust_bill_pkg', - 'addl_from' => "$join_cust $join_pkg", + 'addl_from' => "$join_pkg $join_cust", 'hashref' => {}, 'select' => join(",\n", @select ), 'extra_sql' => $where, @@ -656,7 +656,7 @@ my $query = { my $count_query = 'SELECT ' . join(',', @total) . - " FROM cust_bill_pkg $join_cust $join_pkg + " FROM cust_bill_pkg $join_pkg $join_cust $where"; @peritem_desc = map {emt($_)} @peritem_desc; diff --git a/httemplate/search/cust_bill_pkg_discount.html b/httemplate/search/cust_bill_pkg_discount.html index bb8038a44..4225e91e1 100644 --- a/httemplate/search/cust_bill_pkg_discount.html +++ b/httemplate/search/cust_bill_pkg_discount.html @@ -127,12 +127,12 @@ my $join_cust_pkg_discount = 'LEFT JOIN cust_pkg_discount USING (pkgdiscountnum)'; my $join_cust = - ' JOIN cust_bill_pkg USING ( billpkgnum ) - JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; + ' JOIN cust_bill USING ( invnum ) '. + FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg'); my $join_pkg = - ' LEFT JOIN cust_pkg ON ( cust_bill_pkg.pkgnum = cust_pkg.pkgnum ) + ' JOIN cust_bill_pkg USING ( billpkgnum ) + LEFT JOIN cust_pkg ON ( cust_bill_pkg.pkgnum = cust_pkg.pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) '; #LEFT JOIN part_pkg AS override # ON pkgpart_override = override.pkgpart '; @@ -140,7 +140,7 @@ my $join_pkg = my $where = ' WHERE '. join(' AND ', @where); $count_query .= - " FROM cust_bill_pkg_discount $join_cust_pkg_discount $join_cust $join_pkg ". + " FROM cust_bill_pkg_discount $join_cust_pkg_discount $join_pkg $join_cust ". $where; my @select = ( @@ -155,7 +155,7 @@ push @select, 'cust_main.custnum', my $query = { 'table' => 'cust_bill_pkg_discount', - 'addl_from' => "$join_cust_pkg_discount $join_cust $join_pkg", + 'addl_from' => "$join_cust_pkg_discount $join_pkg $join_cust", 'hashref' => {}, 'select' => join(', ', @select ), 'extra_sql' => $where, diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index f5d8fa19f..d1f41df00 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -137,7 +137,7 @@ my $where = 'WHERE '. join(' AND ', @search); my $count_query = 'SELECT COUNT(*), SUM(amount) '; $count_query .= ', SUM(' . FS::cust_credit->unapplied_sql . ') ' if $unapplied; -$count_query .= 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. +$count_query .= 'FROM cust_credit'. FS::UI::Web::join_cust_main('cust_credit'). $where; my @count_addl = ( $money_char.'%.2f total credited (gross)' ); @@ -148,7 +148,7 @@ my $sql_query = { 'select' => join(', ',@select), 'hashref' => {}, 'extra_sql' => $where, - 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + 'addl_from' => FS::UI::Web::join_cust_main('cust_credit') }; diff --git a/httemplate/search/cust_credit_bill.html b/httemplate/search/cust_credit_bill.html index 9fd6a987a..7e11ee676 100644 --- a/httemplate/search/cust_credit_bill.html +++ b/httemplate/search/cust_credit_bill.html @@ -103,8 +103,8 @@ my $where = 'WHERE '. join(' AND ', @search); # my $count_query = 'SELECT COUNT(*), SUM(amount) FROM cust_credit_bill - LEFT JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '. + LEFT JOIN cust_bill USING ( invnum ) '. + FS::UI::Web::join_cust_main('cust_bill') . $where; my $sql_query = { @@ -121,8 +121,8 @@ my $sql_query = { 'hashref' => {}, 'extra_sql' => $where, 'addl_from' => 'LEFT JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_credit USING ( crednum ) - LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum )', + LEFT JOIN cust_credit USING ( crednum )'. + FS::UI::Web::join_cust_main('cust_bill') }; my $cust_bill_link = sub { diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html index 8b21f371c..26da106e2 100644 --- a/httemplate/search/cust_credit_bill_pkg.html +++ b/httemplate/search/cust_credit_bill_pkg.html @@ -383,8 +383,8 @@ my $count_query = "SELECT COUNT(DISTINCT creditbillpkgnum), SUM(cust_credit_bill_pkg.amount)"; my $join_cust = - ' JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum ) - LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum ) '; + ' JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum )'. + FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg'); my $join_pkg; diff --git a/httemplate/search/cust_credit_refund.html b/httemplate/search/cust_credit_refund.html index 75138e99d..5d89ebe67 100644 --- a/httemplate/search/cust_credit_refund.html +++ b/httemplate/search/cust_credit_refund.html @@ -103,8 +103,8 @@ my $where = 'WHERE '. join(' AND ', @search); # my $count_query = 'SELECT COUNT(*), SUM(cust_credit_refund.amount) FROM cust_credit_refund - LEFT JOIN cust_credit USING ( crednum ) - LEFT JOIN cust_main USING ( custnum ) '. + LEFT JOIN cust_credit USING ( crednum ) '. + FS::UI::Web::join_cust_main('cust_credit') . $where; my $sql_query = { @@ -121,8 +121,8 @@ my $sql_query = { 'hashref' => {}, 'extra_sql' => $where, 'addl_from' => 'LEFT JOIN cust_credit USING ( crednum ) - LEFT JOIN cust_refund USING ( refundnum ) - LEFT JOIN cust_main ON ( cust_credit.custnum = cust_main.custnum )', + LEFT JOIN cust_refund USING ( refundnum )'. + FS::UI::Web::join_cust_main('cust_credit') }; #my $cust_credit_link = sub { diff --git a/httemplate/search/cust_event.html b/httemplate/search/cust_event.html index deb34b9e5..bd6ba6974 100644 --- a/httemplate/search/cust_event.html +++ b/httemplate/search/cust_event.html @@ -175,7 +175,13 @@ $search{'ending'} = $ending; my $where = ' WHERE '. FS::cust_event->search_sql_where( \%search ); -my $join = FS::cust_event->join_sql(); +my $join = FS::cust_event->join_sql() . + 'LEFT JOIN cust_location bill_location '. + 'ON (cust_main.bill_locationnum = bill_location.locationnum) '. + 'LEFT JOIN cust_location ship_location '. + 'ON (cust_main.ship_locationnum = ship_location.locationnum)'; + # warning: does not show the true service address for package events. + # the query to do that would be painfully slow. my $sql_query = { 'table' => 'cust_event', diff --git a/httemplate/search/cust_pkg_discount.html b/httemplate/search/cust_pkg_discount.html index d70c3116f..bd28da87d 100644 --- a/httemplate/search/cust_pkg_discount.html +++ b/httemplate/search/cust_pkg_discount.html @@ -92,8 +92,8 @@ my $count_query = "SELECT COUNT(*), SUM(amount)"; my $join = ' LEFT JOIN discount USING ( discountnum ) LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN cust_main USING ( custnum ) '; + LEFT JOIN part_pkg USING ( pkgpart ) '. + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); my $where = ' WHERE '. join(' AND ', @where); diff --git a/httemplate/search/cust_svc.html b/httemplate/search/cust_svc.html index 2adcbd76f..e2a83b7de 100644 --- a/httemplate/search/cust_svc.html +++ b/httemplate/search/cust_svc.html @@ -62,7 +62,7 @@ if ( length( $cgi->param('search_svc') ) ) { my $addl_from = ' LEFT JOIN part_svc USING ( svcpart ) '. ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); my @extra_sql = (); diff --git a/httemplate/search/cust_tax_exempt.cgi b/httemplate/search/cust_tax_exempt.cgi index 3704b208a..f075d1926 100644 --- a/httemplate/search/cust_tax_exempt.cgi +++ b/httemplate/search/cust_tax_exempt.cgi @@ -50,9 +50,7 @@ %> <%init> -my $join_cust = " - LEFT JOIN cust_main USING ( custnum ) -"; +my $join_cust = FS::UI::Web::join_cust_main('cust_tax_exempt'); die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('View customer tax exemptions'); diff --git a/httemplate/search/cust_tax_exempt_pkg.cgi b/httemplate/search/cust_tax_exempt_pkg.cgi index 1b767f846..7e1a239b4 100644 --- a/httemplate/search/cust_tax_exempt_pkg.cgi +++ b/httemplate/search/cust_tax_exempt_pkg.cgi @@ -82,9 +82,8 @@ <%once> my $join_cust = " - JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) -"; + JOIN cust_bill USING ( invnum )" . + FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg'); my $join_pkg = " LEFT JOIN cust_pkg USING ( pkgnum ) @@ -93,8 +92,8 @@ my $join_pkg = " my $join = " JOIN cust_bill_pkg USING ( billpkgnum ) - $join_cust $join_pkg + $join_cust "; diff --git a/httemplate/search/inventory_item.html b/httemplate/search/inventory_item.html index 086c8e92d..64722ff41 100644 --- a/httemplate/search/inventory_item.html +++ b/httemplate/search/inventory_item.html @@ -157,7 +157,7 @@ my $link_cust = sub { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); my $areboxes = 0; my $sub_checkbox = sub { diff --git a/httemplate/search/phone_avail.html b/httemplate/search/phone_avail.html index 1335379ae..312c84ea9 100644 --- a/httemplate/search/phone_avail.html +++ b/httemplate/search/phone_avail.html @@ -125,9 +125,11 @@ my $search = scalar(@search) 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); my $count_query = "SELECT COUNT(*) FROM phone_avail $search"; #$addl_from? +# All of these relationships are left joined in the many-to-one direction, +# so including $addl_from won't affect the count. Logic! my $hashref = {}; $hashref->{'ordernum'} = $1 if $cgi->param('ordernum') =~ /^(\d+)$/; diff --git a/httemplate/search/quotation.html b/httemplate/search/quotation.html index 259c85c22..fbc35bea1 100755 --- a/httemplate/search/quotation.html +++ b/httemplate/search/quotation.html @@ -72,7 +72,7 @@ die "access denied" unless $curuser->access_right('List quotations'); my $join_prospect_main = 'LEFT JOIN prospect_main USING ( prospectnum )'; -my $join_cust_main = 'LEFT JOIN cust_main ON ( quotation.custnum = cust_main.custnum )'; +my $join_cust_main = FS::UI::Web::join_cust_main('quotation'); #here is the agent virtualization my $agentnums_sql = ' ( '. $curuser->agentnums_sql( table=>'prospect_main' ). diff --git a/httemplate/search/svc_dish.cgi b/httemplate/search/svc_dish.cgi index 94da03537..a2e56d24d 100755 --- a/httemplate/search/svc_dish.cgi +++ b/httemplate/search/svc_dish.cgi @@ -61,7 +61,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); #here is the agent virtualization push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi index 9827b8d38..508d88931 100755 --- a/httemplate/search/svc_domain.cgi +++ b/httemplate/search/svc_domain.cgi @@ -66,7 +66,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); #here is the agent virtualization push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( diff --git a/httemplate/search/svc_external.cgi b/httemplate/search/svc_external.cgi index cb51d44fd..7d57697b1 100755 --- a/httemplate/search/svc_external.cgi +++ b/httemplate/search/svc_external.cgi @@ -90,7 +90,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); #here is the agent virtualization push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi index f17f131ab..eda790a0a 100755 --- a/httemplate/search/svc_forward.cgi +++ b/httemplate/search/svc_forward.cgi @@ -67,7 +67,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); #here is the agent virtualization push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( diff --git a/httemplate/search/svc_hardware.cgi b/httemplate/search/svc_hardware.cgi index ec09be82b..085d7500a 100644 --- a/httemplate/search/svc_hardware.cgi +++ b/httemplate/search/svc_hardware.cgi @@ -44,8 +44,8 @@ die "access denied" 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 ) + LEFT JOIN cust_pkg USING ( pkgnum )'. + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg').' LEFT JOIN hardware_type USING ( typenum )'; my @extra_sql; diff --git a/httemplate/search/svc_www.cgi b/httemplate/search/svc_www.cgi index adc31c88a..5132f9470 100755 --- a/httemplate/search/svc_www.cgi +++ b/httemplate/search/svc_www.cgi @@ -73,7 +73,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { 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 ) '; + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); #here is the agent virtualization push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( diff --git a/httemplate/search/unearned_detail.html b/httemplate/search/unearned_detail.html index f61de052e..425aa5a4e 100644 --- a/httemplate/search/unearned_detail.html +++ b/httemplate/search/unearned_detail.html @@ -210,8 +210,8 @@ push @select, '(edate - 82799) AS before_edate'; #usage always excluded # always 'nottax', not 'istax' -$join_cust = ' JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; +$join_cust = ' JOIN cust_bill USING ( invnum ) '. + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) @@ -222,7 +222,7 @@ my $where = ' WHERE '. join(' AND ', @where); my $count_query = "SELECT COUNT(DISTINCT billpkgnum), SUM( $unearned_base ), SUM( $unearned_sql ) - FROM cust_bill_pkg $join_cust $join_pkg $where"; + FROM cust_bill_pkg $join_pkg $join_cust $where"; push @select, 'part_pkg.pkg', 'part_pkg.freq', @@ -231,7 +231,7 @@ push @select, 'part_pkg.pkg', my $query = { 'table' => 'cust_bill_pkg', - 'addl_from' => "$join_cust $join_pkg", + 'addl_from' => "$join_pkg $join_cust", 'hashref' => {}, 'select' => join(",\n", @select ), 'extra_sql' => $where, diff --git a/httemplate/search/unprovisioned_services.html b/httemplate/search/unprovisioned_services.html index f85e4fb19..89d5f2d66 100644 --- a/httemplate/search/unprovisioned_services.html +++ b/httemplate/search/unprovisioned_services.html @@ -74,7 +74,8 @@ my $search = " where cust_pkg.cancel is null and pkg_svc.quantity > 0 and " . " cust_svc.pkgnum = cust_pkg.pkgnum and " . " cust_svc.svcpart = pkg_svc.svcpart) $svcpart_limit"; -my $addl_from = " join pkg_svc using (pkgpart) join cust_main using (custnum) "; +my $addl_from = " join pkg_svc using (pkgpart) ". + FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'); # this was very painful to derive but it appears correct #select cust_pkg.custnum,cust_pkg.pkgpart,cust_pkg.pkgnum, pkg_svc.svcpart from cust_pkg join -- 2.11.0