From: Ivan Kohler Date: Mon, 25 Apr 2016 16:58:14 +0000 (-0700) Subject: Merge branch 'master' of git.freeside.biz:/home/git/freeside X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=46fe3dbcb3ca97d1f3c70d49351846cf0ab6461d;hp=cc4caa54e9974ea3d6ac7cf55cf45863d2a8905e Merge branch 'master' of git.freeside.biz:/home/git/freeside --- diff --git a/FS/FS/API.pm b/FS/FS/API.pm index 32400f7c3..9d0ef2515 100644 --- a/FS/FS/API.pm +++ b/FS/FS/API.pm @@ -8,6 +8,7 @@ use FS::cust_location; use FS::cust_pay; use FS::cust_credit; use FS::cust_refund; +use FS::cust_pkg; =head1 NAME @@ -556,6 +557,87 @@ sub location_info { return \%return; } +=item change_package_location + +Updates package location. Takes a list of keys and values +as paramters with the following keys: + +pkgnum + +secret + +locationnum - pass this, or the following keys (don't pass both) + +locationname + +address1 + +address2 + +city + +county + +state + +zip + +addr_clean + +country + +censustract + +censusyear + +location_type + +location_number + +location_kind + +incorporated + +On error, returns a hashref with an 'error' key. +On success, returns a hashref with 'pkgnum' and 'locationnum' keys, +containing the new values. + +=cut + +sub change_package_location { + my $class = shift; + my %opt = @_; + return _shared_secret_error() unless _check_shared_secret($opt{'secret'}); + + my $cust_pkg = qsearchs('cust_pkg', { 'pkgnum' => $opt{'pkgnum'} }) + or return { 'error' => 'Unknown pkgnum' }; + + my %changeopt; + + foreach my $field ( qw( + locationnum + locationname + address1 + address2 + city + county + state + zip + addr_clean + country + censustract + censusyear + location_type + location_number + location_kind + incorporated + )) { + $changeopt{$field} = $opt{$field} if $opt{$field}; + } + + $cust_pkg->API_change(%changeopt); +} + =item bill_now OPTION => VALUE, ... Bills a single customer now, in the same fashion as the "Bill now" link in the diff --git a/FS/FS/ClientAPI/MyAccount.pm b/FS/FS/ClientAPI/MyAccount.pm index d6fa8652c..ed7e35317 100644 --- a/FS/FS/ClientAPI/MyAccount.pm +++ b/FS/FS/ClientAPI/MyAccount.pm @@ -3605,11 +3605,6 @@ sub adjust_ticket_priority { my($context, $session, $custnum) = _custoragent_session_custnum($p); return { 'error' => $session } if $context eq 'error'; - # temporary instrumentation for RT#39536 - local $DEBUG = 1; - local $FS::TicketSystem::RT_Internal::DEBUG = 1; - warn "[adjust_ticket_priority]\n" . Dumper($p->{'values'}) . "\n\n"; - # warn "$me adjust_ticket_priority: initializing ticket system\n" if $DEBUG; # FS::TicketSystem->init; my $ss_priority = FS::TicketSystem->selfservice_priority; diff --git a/FS/FS/ClientAPI/Signup.pm b/FS/FS/ClientAPI/Signup.pm index df9ee88e5..5010ec97d 100644 --- a/FS/FS/ClientAPI/Signup.pm +++ b/FS/FS/ClientAPI/Signup.pm @@ -7,7 +7,7 @@ use Data::Dumper; use Tie::RefHash; use Digest::SHA qw(sha512_hex); use FS::Conf; -use FS::Record qw(qsearch qsearchs dbdef); +use FS::Record qw(qsearch qsearchs dbdef dbh); use FS::CGI qw(popurl); use FS::Msgcat qw(gettext); use FS::Misc qw(card_types); @@ -31,6 +31,25 @@ use FS::cust_payby; $DEBUG = 1; $me = '[FS::ClientAPI::Signup]'; +=head1 NAME + +FS::ClientAPI::Signup - Front-end API for signing up customers + +=head1 DESCRIPTION + +This module provides the ClientAPI functions for talking to a signup +server. The signup server is open to the public, i.e. does not require a +login. The back-end Freeside server creates customers, orders packages and +services, and processes initial payments. + +=head1 METHODS + +=over 4 + +=cut + +# document the rest of this as we work on it + sub clear_cache { warn "$me clear_cache called\n" if $DEBUG; my $cache = new FS::ClientAPI_SessionCache( { @@ -499,21 +518,8 @@ sub new_customer { #possibly some validation will be needed } - my $agentnum; - if ( exists $packet->{'session_id'} ) { - my $cache = new FS::ClientAPI_SessionCache( { - 'namespace' => 'FS::ClientAPI::Agent', - } ); - my $session = $cache->get($packet->{'session_id'}); - if ( $session ) { - $agentnum = $session->{'agentnum'}; - } else { - return { 'error' => "Can't resume session" }; #better error message - } - } else { - $agentnum = $packet->{agentnum} - || $conf->config('signup_server-default_agentnum'); - } + my $agentnum = get_agentnum($packet); + return $agentnum if ref($agentnum); my ($bill_hash, $ship_hash); foreach my $f (FS::cust_main->location_fields) { @@ -924,21 +930,8 @@ sub new_customer_minimal { #possibly some validation will be needed } - my $agentnum; - if ( exists $packet->{'session_id'} ) { - my $cache = new FS::ClientAPI_SessionCache( { - 'namespace' => 'FS::ClientAPI::Agent', - } ); - my $session = $cache->get($packet->{'session_id'}); - if ( $session ) { - $agentnum = $session->{'agentnum'}; - } else { - return { 'error' => "Can't resume session" }; #better error message - } - } else { - $agentnum = $packet->{agentnum} - || $conf->config('signup_server-default_agentnum'); - } + my $agentnum = get_agentnum($packet); + return $agentnum if ref($agentnum); #shares some stuff with htdocs/edit/process/cust_main.cgi... take any # common that are still here and library them. @@ -1220,4 +1213,186 @@ sub capture_payment { } +=item get_agentnum PACKET + +Given a PACKET from the signup server, looks up the agentnum to use for signing +up a customer. This will use 'session_id' if the agent is authenticated, +otherwise 'agentnum', otherwise the 'signup_server-default_agentnum' config. If +the agent can't be found, returns an error packet. + +=cut + +sub get_agentnum { + my $packet = shift; + my $conf = new FS::Conf; + my $agentnum; + if ( exists $packet->{'session_id'} ) { + my $cache = new FS::ClientAPI_SessionCache( { + 'namespace' => 'FS::ClientAPI::Agent', + } ); + my $session = $cache->get($packet->{'session_id'}); + if ( $session ) { + $agentnum = $session->{'agentnum'}; + } else { + return { 'error' => "Can't resume session" }; #better error message + } + } else { + $agentnum = $packet->{agentnum} + || $conf->config('signup_server-default_agentnum'); + } + if ( $agentnum and FS::agent->count('agentnum = ?', $agentnum) ) { + return $agentnum; + } + return { 'error' => 'Signup is not configured' }; +} + +=item new_prospect PACKET + +Creates a new L entry. PACKET must contain: + +- either agentnum or session_id; if not, signup_server-default_agentnum will +be used and must not be empty + +- either refnum or referral_title; if not, signup_server-default_refnum will +be used and must not be empty + +- last and first (names), and optionally company and title + +- address1, city, state, country, zip, and optionally address2 + +- emailaddress + +and can also contain: + +- one or more of phone_daytime, phone_night, phone_mobile, and phone_fax + +- a 'comment' (will be attached to the contact) + +State and country will be normalized to Freeside state/country codes if +necessary. + +=cut + +sub new_prospect { + + my $packet = shift; + warn "$me new_prospect called\n".Dumper($packet) if $DEBUG; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + my $conf = FS::Conf->new; + + my $error; + + my $agentnum = get_agentnum($packet); + return $agentnum if ref $agentnum; + my $refnum; + if ( my $title = $packet->{referral_title} ) { + my $part_referral = qsearchs('part_referral', { + 'agentnum' => $agentnum, + 'title' => $title, + }); + $part_referral ||= qsearchs('part_referral', { + 'agentnum' => '', + 'title' => $title, + }); + if (!$part_referral) { + return { error => "Unknown referral type: '$title'" }; + } + $refnum = $part_referral->refnum; + } elsif ( $packet->{refnum} ) { + $refnum = $packet->{refnum}; + } + $refnum ||= $conf->config('signup_server-default_refnum'); + return { error => "Signup referral type is not configured" } if !$refnum; + + my $prospect = FS::prospect_main->new({ + 'agentnum' => $agentnum, + 'refnum' => $refnum, + 'company' => $packet->{company}, + }); + + my $location = FS::cust_location->new; + foreach ( qw(address1 address2 city county zip ) ) { + $location->set($_, $packet->{$_}); + } + # normalize country and state if they're not already ISO codes + # easier than doing it on the client side--we already have the tables here + my $country = $packet->{country}; + my $state = $packet->{state}; + if (length($country) > 2) { + # it likes title case + $country = join(' ', map ucfirst, split(/\s+/, $country)); + my $lsc = Locale::SubCountry->new($country); + if ($lsc) { + $country = uc($lsc->country_code); + + if ($lsc->has_sub_countries) { + if ( $lsc->full_name($state) eq 'unknown' ) { + # then we were probably given a full name, so resolve it + $state = $lsc->code($state); + if ( $state eq 'unknown' ) { + # doesn't resolve as a full name either, return an error + $error = "Unknown state: ".$packet->{state}; + } else { + $state = uc($state); + } + } + } # else state doesn't matter + } else { + # couldn't find the country in LSC + $error = "Unknown country: $country"; + } + } + $location->set('country', $country); + $location->set('state', $state); + $prospect->set('cust_location', $location); + + $error ||= $prospect->insert; # also does location + return { error => $error } if $error; + + my $contact = FS::contact->new({ + prospectnum => $prospect->prospectnum, + locationnum => $location->locationnum, + invoice_dest => 'Y', + }); + # use emailaddress pseudo-field behavior here + foreach (qw(last first title emailaddress comment)) { + $contact->set($_, $packet->{$_}); + } + $error = $contact->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return { error => $error }; + } + + foreach my $phone_type (qsearch('phone_type', {})) { + my $key = 'phone_' . lc($phone_type->typename); + my $phonenum = $packet->{$key}; + if ( $phonenum ) { + # just to not have to supply country code from the other end + my $number = Number::Phone->new($location->country, $phonenum); + if (!$number) { + $error = 'invalid phone number'; + } else { + my $phone = FS::contact_phone->new({ + contactnum => $contact->contactnum, + phonenum => $phonenum, + countrycode => $number->country_code, + phonetypenum => $phone_type->phonetypenum, + }); + $error = $phone->insert; + } + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return { error => $phone_type->typename . ' phone: ' . $error }; + } + } + } # foreach $phone_type + + $dbh->commit if $oldAutoCommit; + return { prospectnum => $prospect->prospectnum }; +} + 1; diff --git a/FS/FS/ClientAPI_XMLRPC.pm b/FS/FS/ClientAPI_XMLRPC.pm index 2dea80129..622f3df05 100644 --- a/FS/FS/ClientAPI_XMLRPC.pm +++ b/FS/FS/ClientAPI_XMLRPC.pm @@ -192,6 +192,7 @@ sub ss2clientapi { 'new_customer_minimal' => 'Signup/new_customer_minimal', 'capture_payment' => 'Signup/capture_payment', 'clear_signup_cache' => 'Signup/clear_cache', + 'new_prospect' => 'Signup/new_prospect', 'new_agent' => 'Agent/new_agent', 'agent_login' => 'Agent/agent_login', 'agent_logout' => 'Agent/agent_logout', diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm index d0b1180cb..745315816 100644 --- a/FS/FS/Conf.pm +++ b/FS/FS/Conf.pm @@ -1389,7 +1389,7 @@ and customer address. Include units.', { 'key' => 'invoice_latexextracouponspace', 'section' => 'invoicing', - 'description' => 'Optional LaTeX invoice textheight space to reserve for a tear off coupon. Include units. Default is 3.6cm', + 'description' => 'Optional LaTeX invoice textheight space to reserve for a tear off coupon. Include units. Default is 2.7 inches.', 'type' => 'text', 'per_agent' => 1, 'validate' => sub { shift =~ @@ -1401,7 +1401,7 @@ and customer address. Include units.', { 'key' => 'invoice_latexcouponfootsep', 'section' => 'invoicing', - 'description' => 'Optional LaTeX invoice separation between tear off coupon and footer. Include units.', + 'description' => 'Optional LaTeX invoice separation between bottom of coupon address and footer. Include units. Default is 0.2 inches.', 'type' => 'text', 'per_agent' => 1, 'validate' => sub { shift =~ @@ -1413,7 +1413,7 @@ and customer address. Include units.', { 'key' => 'invoice_latexcouponamountenclosedsep', 'section' => 'invoicing', - 'description' => 'Optional LaTeX invoice separation between total due and amount enclosed line. Include units.', + 'description' => 'Optional LaTeX invoice separation between total due and amount enclosed line. Include units. Default is 2.25 em.', 'type' => 'text', 'per_agent' => 1, 'validate' => sub { shift =~ @@ -1424,7 +1424,7 @@ and customer address. Include units.', { 'key' => 'invoice_latexcoupontoaddresssep', 'section' => 'invoicing', - 'description' => 'Optional LaTeX invoice separation between invoice data and the to address (usually invoice_latexreturnaddress). Include units.', + 'description' => 'Optional LaTeX invoice separation between invoice data and the address (usually invoice_latexreturnaddress). Include units. Default is 1 inch.', 'type' => 'text', 'per_agent' => 1, 'validate' => sub { shift =~ @@ -1972,7 +1972,7 @@ and customer address. Include units.', 'description' => 'Enables the automatic unsuspension of suspended packages when a customer\'s balance due is at or below the specified amount after a payment or credit', 'type' => 'select', 'select_enum' => [ - '', 'Zero', 'Latest invoice charges' + '', 'Zero', 'Latest invoice charges', 'Charges not past due' ], }, @@ -5306,7 +5306,7 @@ and customer address. Include units.', my @part_export = map { qsearch( 'part_export', {exporttype => $_ } ) } keys %{FS::part_export::export_info('cust_main')}; - map { $_->exportnum => $_->exporttype.' to '.$_->machine } @part_export; + map { $_->exportnum => $_->exportname } @part_export; }, 'option_sub' => sub { require FS::Record; @@ -5315,7 +5315,7 @@ and customer address. Include units.', 'part_export', { 'exportnum' => shift } ); $part_export - ? $part_export->exporttype.' to '.$part_export->machine + ? $part_export->exportname : ''; }, }, @@ -5324,7 +5324,7 @@ and customer address. Include units.', { 'key' => 'cust_location-exports', 'section' => '', - 'description' => 'Export(s) to call on cust_location insert, modification and deletion.', + 'description' => 'Export(s) to call on cust_location insert or modification', 'type' => 'select-sub', 'multiple' => 1, 'options_sub' => sub { @@ -5333,7 +5333,7 @@ and customer address. Include units.', my @part_export = map { qsearch( 'part_export', {exporttype => $_ } ) } keys %{FS::part_export::export_info('cust_location')}; - map { $_->exportnum => $_->exporttype.' to '.$_->machine } @part_export; + map { $_->exportnum => $_->exportname } @part_export; }, 'option_sub' => sub { require FS::Record; @@ -5342,7 +5342,7 @@ and customer address. Include units.', 'part_export', { 'exportnum' => shift } ); $part_export - ? $part_export->exporttype.' to '.$part_export->machine + ? $part_export->exportname : ''; }, }, diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm index aa9d3bebf..7b4db9932 100644 --- a/FS/FS/Mason.pm +++ b/FS/FS/Mason.pm @@ -146,7 +146,8 @@ if ( -e $addl_handler_use_file ) { use FS::Report::Table; use FS::Report::Table::Monthly; use FS::Report::Table::Daily; - use FS::Report::Tax; + use FS::Report::Tax::ByName; + use FS::Report::Tax::All; use FS::TicketSystem; use FS::NetworkMonitoringSystem; use FS::Tron qw( tron_lint ); diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm deleted file mode 100644 index f1f6be38e..000000000 --- a/FS/FS/Report/Tax.pm +++ /dev/null @@ -1,675 +0,0 @@ -package FS::Report::Tax; - -use strict; -use vars qw($DEBUG); -use FS::Record qw(dbh qsearch qsearchs group_concat_sql); -use Date::Format qw( time2str ); - -use Data::Dumper; - -$DEBUG = 0; - -=item report_internal OPTIONS - -Constructor. Generates a tax report using the internal tax rate system -(L). - -Required parameters: - -- beginning, ending: the date range as Unix timestamps. -- taxname: the name of the tax (corresponds to C). -- country: the country code. - -Optional parameters: -- agentnum: limit to this agentnum.num. -- breakdown: hashref of the fields to group by. Keys can be 'city', 'district', - 'pkgclass', or 'taxclass'; values should be true. -- debug: sets the debug level. 1 will warn the data collected for the report; - 2 will also warn all of the SQL statements. - -=cut - -sub report_internal { - my $class = shift; - my %opt = @_; - - $DEBUG ||= $opt{debug}; - - my $conf = new FS::Conf; - - my($beginning, $ending) = @opt{'beginning', 'ending'}; - - my ($taxname, $country, %breakdown); - - # taxname can contain arbitrary punctuation; escape it properly and - # include $taxname unquoted elsewhere - $taxname = dbh->quote($opt{'taxname'}); - - if ( $opt{country} =~ /^(\w\w)$/ ) { - $country = $1; - } else { - die "country required"; - } - - # %breakdown: short name => field identifier - # null classnum should remain null, not be converted to zero - %breakdown = ( - 'taxclass' => 'cust_main_county.taxclass', - 'pkgclass' => 'COALESCE(part_fee.classnum,part_pkg.classnum)', - 'city' => 'cust_main_county.city', - 'district' => 'cust_main_county.district', - 'state' => 'cust_main_county.state', - 'county' => 'cust_main_county.county', - ); - foreach (qw(taxclass pkgclass city district)) { - delete $breakdown{$_} unless $opt{breakdown}->{$_}; - } - - my $join_cust = ' JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; - - my $join_cust_pkg = $join_cust. - ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN part_fee USING ( feepart ) '; - - my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg "; - - # all queries MUST be linked to both cust_bill and cust_main_county - - # Either or both of these can be used to link cust_bill_pkg to - # cust_main_county. This one links a taxed line item (billpkgnum) to a tax rate - # (taxnum), and gives the amount of tax charged on that line item under that - # rate (as tax_amount). - my $pkg_tax = "SELECT SUM(amount) as tax_amount, taxnum, ". - "taxable_billpkgnum AS billpkgnum ". - "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ". - "GROUP BY taxable_billpkgnum, taxnum"; - - # This one links a tax-exempted line item (billpkgnum) to a tax rate - # (taxnum), and gives the amount of the tax exemption. EXEMPT_WHERE must - # be replaced with an expression to further limit the tax exemptions - # that will be included, or "TRUE" to not limit them. - # - # Note that tax exemptions with non-null creditbillpkgnum are always - # excluded. Those are "negative exemptions" created by crediting a sale - # that had received an exemption. - my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ". - "FROM cust_tax_exempt_pkg WHERE - ( EXEMPT_WHERE ) - AND cust_tax_exempt_pkg.creditbillpkgnum IS NULL - GROUP BY billpkgnum, taxnum"; - - my $where = "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending ". - "AND COALESCE(cust_main_county.taxname,'Tax') = $taxname ". - "AND cust_main_county.country = '$country'"; - # SELECT/GROUP clauses for first-level queries - my $select = "SELECT "; - my $group = "GROUP BY "; - foreach (qw(pkgclass taxclass state county city district)) { - if ( $breakdown{$_} ) { - $select .= "$breakdown{$_} AS $_, "; - $group .= "$breakdown{$_}, "; - } else { - $select .= "NULL AS $_, "; - } - } - $select .= group_concat_sql('DISTINCT(cust_main_county.taxnum)', ',') . - ' AS taxnums, '; - $group =~ s/, $//; - - # SELECT/GROUP clauses for second-level (totals) queries - # breakdown by package class only, if anything - my $select_all = "SELECT NULL AS pkgclass, "; - my $group_all = ""; - if ( $breakdown{pkgclass} ) { - $select_all = "SELECT $breakdown{pkgclass} AS pkgclass, "; - $group_all = "GROUP BY $breakdown{pkgclass}"; - } - $select_all .= group_concat_sql('DISTINCT(cust_main_county.taxnum)', ',') . - ' AS taxnums, '; - - my $agentnum; - if ( $opt{agentnum} and $opt{agentnum} =~ /^(\d+)$/ ) { - $agentnum = $1; - my $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); - die "agent not found" unless $agent; - $where .= " AND cust_main.agentnum = $agentnum"; - } - - my $nottax = - '(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)'; - - # one query for each column of the report - # plus separate queries for the totals row - my (%sql, %all_sql); - - # SALES QUERIES (taxable sales, all types of exempt sales) - # ------------- - - # general form - my $exempt = "$select SUM(exempt_charged) - FROM cust_main_county - JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) - $join_cust_pkg $where AND $nottax - $group"; - - my $all_exempt = "$select_all SUM(exempt_charged) - FROM cust_main_county - JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) - $join_cust_pkg $where AND $nottax - $group_all"; - - # sales to tax-exempt customers - $sql{exempt_cust} = $exempt; - $sql{exempt_cust} =~ s/EXEMPT_WHERE/exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; - $all_sql{exempt_cust} = $all_exempt; - $all_sql{exempt_cust} =~ s/EXEMPT_WHERE/exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; - - # sales of tax-exempt packages - $sql{exempt_pkg} = $exempt; - $sql{exempt_pkg} =~ s/EXEMPT_WHERE/exempt_setup = 'Y' OR exempt_recur = 'Y'/; - $all_sql{exempt_pkg} = $all_exempt; - $all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/exempt_setup = 'Y' OR exempt_recur = 'Y'/; - - # monthly per-customer exemptions - $sql{exempt_monthly} = $exempt; - $sql{exempt_monthly} =~ s/EXEMPT_WHERE/exempt_monthly = 'Y'/; - $all_sql{exempt_monthly} = $all_exempt; - $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/exempt_monthly = 'Y'/; - - # credits applied to taxable sales - # Note that negative exemptions (from exempt sales being credited) are NOT - # counted when calculating the exempt amount. (See above.) Therefore we need - # to NOT include any credits against exempt sales in this amount, either. - # These two subqueries implement that. They have joins to cust_credit_bill - # and cust_bill so that credits can be filtered by application date if - # requested. - - # Each row here is the sum of credits applied to a line item. - my $sales_credit = - "SELECT billpkgnum, SUM(cust_credit_bill_pkg.amount) AS credited - FROM cust_credit_bill_pkg - JOIN cust_credit_bill USING (creditbillnum) - JOIN cust_bill USING (invnum) - WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending - GROUP BY billpkgnum - "; - - # Each row here is the sum of negative exemptions applied to a combination - # of line item and tax definition. - my $exempt_credit = - "SELECT cust_credit_bill_pkg.billpkgnum, taxnum, - 0 - SUM(cust_tax_exempt_pkg.amount) AS exempt_credited - FROM cust_credit_bill_pkg - LEFT JOIN cust_tax_exempt_pkg USING (creditbillpkgnum) - JOIN cust_credit_bill USING (creditbillnum) - JOIN cust_bill USING (invnum) - WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending - GROUP BY cust_credit_bill_pkg.billpkgnum, taxnum - "; - - if ( $opt{credit_date} eq 'cust_credit_bill' ) { - $sales_credit =~ s/cust_bill._date/cust_credit_bill._date/g; - $exempt_credit =~ s/cust_bill._date/cust_credit_bill._date/g; - } - - $sql{sales_credited} = "$select - SUM(COALESCE(credited, 0) - COALESCE(exempt_credited, 0)) - FROM cust_main_county - JOIN ($pkg_tax) AS pkg_tax USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) - LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) - LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) - $join_cust_pkg $where AND $nottax - $group - "; - - $all_sql{sales_credited} = "$select_all - SUM(COALESCE(credited, 0) - COALESCE(exempt_credited, 0)) - FROM cust_main_county - JOIN ($pkg_tax) AS pkg_tax USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) - LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) - LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) - $join_cust_pkg $where AND $nottax - $group_all - "; - - # also include the exempt-sales credit amount, for the credit report - $sql{exempt_credited} = "$select - SUM(COALESCE(exempt_credited, 0)) - FROM cust_main_county - LEFT JOIN ($exempt_credit) AS exempt_credit USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) - $join_cust_pkg $where AND $nottax - $group - "; - - $all_sql{exempt_credited} = "$select_all - SUM(COALESCE(exempt_credited, 0)) - FROM cust_main_county - LEFT JOIN ($exempt_credit) AS exempt_credit USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) - $join_cust_pkg $where AND $nottax - $group_all - "; - - # taxable sales - $sql{taxable} = "$select - SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - + COALESCE(exempt_credited, 0) - ) - FROM cust_main_county - JOIN ($pkg_tax) AS pkg_tax USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) - LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) - LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) - LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) - $join_cust_pkg $where AND $nottax - $group"; - - $all_sql{taxable} = "$select_all - SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - + COALESCE(exempt_credited, 0) - ) - FROM cust_main_county - JOIN ($pkg_tax) AS pkg_tax USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) - LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) - LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) - LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) - $join_cust_pkg $where AND $nottax - $group_all"; - - $sql{taxable} =~ s/EXEMPT_WHERE/TRUE/; # unrestricted - $all_sql{taxable} =~ s/EXEMPT_WHERE/TRUE/; - - # estimated tax (taxable * rate) - $sql{estimated} = "$select - SUM(cust_main_county.tax / 100 * - (cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - + COALESCE(exempt_credited, 0) - ) - ) - FROM cust_main_county - JOIN ($pkg_tax) AS pkg_tax USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) - LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) - LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) - LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) - $join_cust_pkg $where AND $nottax - $group"; - - $all_sql{estimated} = "$select_all - SUM(cust_main_county.tax / 100 * - (cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - + COALESCE(exempt_credited, 0) - ) - ) - FROM cust_main_county - JOIN ($pkg_tax) AS pkg_tax USING (taxnum) - JOIN cust_bill_pkg USING (billpkgnum) - LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) - LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) - LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) - $join_cust_pkg $where AND $nottax - $group_all"; - - $sql{estimated} =~ s/EXEMPT_WHERE/TRUE/; # unrestricted - $all_sql{estimated} =~ s/EXEMPT_WHERE/TRUE/; - - # there isn't one for 'sales', because we calculate sales by adding up - # the taxable and exempt columns. - - # TAX QUERIES (billed tax, credited tax, collected tax) - # ----------- - - # sum of billed tax: - # join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location - my $taxfrom = " FROM cust_bill_pkg - $join_cust - LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) - LEFT JOIN cust_main_county USING ( taxnum )"; - - if ( $breakdown{pkgclass} ) { - # If we're not grouping by package class, this is unnecessary, and - # probably really expensive. - # Remember that fees also have package classes. - $taxfrom .= " - LEFT JOIN cust_bill_pkg AS taxable - ON (cust_bill_pkg_tax_location.taxable_billpkgnum = taxable.billpkgnum) - LEFT JOIN cust_pkg ON (taxable.pkgnum = cust_pkg.pkgnum) - LEFT JOIN part_pkg USING (pkgpart) - LEFT JOIN part_fee ON (taxable.feepart = part_fee.feepart) "; - } - - my $istax = "cust_bill_pkg.pkgnum = 0 and cust_bill_pkg.feepart is null"; - - $sql{tax} = "$select COALESCE(SUM(cust_bill_pkg_tax_location.amount),0) - $taxfrom - $where AND $istax - $group"; - - $all_sql{tax} = "$select_all COALESCE(SUM(cust_bill_pkg_tax_location.amount),0) - $taxfrom - $where AND $istax - $group_all"; - - # sum of credits applied against billed tax - # ($creditfrom includes join of taxable item to part_pkg/part_fee if - # with_pkgclass is on) - my $creditfrom = $taxfrom . - ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)' . - ' JOIN cust_credit_bill USING (creditbillnum)'; - my $creditwhere = $where . - ' AND billpkgtaxratelocationnum IS NULL'; - - # if the credit_date option is set to application date, change - # $creditwhere accordingly - if ( $opt{credit_date} eq 'cust_credit_bill' ) { - $creditwhere =~ s/cust_bill._date/cust_credit_bill._date/g; - } - - $sql{tax_credited} = "$select COALESCE(SUM(cust_credit_bill_pkg.amount),0) - $creditfrom - $creditwhere AND $istax - $group"; - - $all_sql{tax_credited} = "$select_all COALESCE(SUM(cust_credit_bill_pkg.amount),0) - $creditfrom - $creditwhere AND $istax - $group_all"; - - # sum of tax paid - # this suffers from the same ambiguity as anything else that applies - # received payments to specific packages, but in reality the discrepancy - # should be minimal since people either pay their bill or don't. - # the join is on billpkgtaxlocationnum to avoid cross-producting. - - my $paidfrom = $taxfrom . - ' JOIN cust_bill_pay_pkg'. - ' ON (cust_bill_pay_pkg.billpkgtaxlocationnum ='. - ' cust_bill_pkg_tax_location.billpkgtaxlocationnum)'; - - $sql{tax_paid} = "$select COALESCE(SUM(cust_bill_pay_pkg.amount),0) - $paidfrom - $where AND $istax - $group"; - - $all_sql{tax_paid} = "$select_all COALESCE(SUM(cust_bill_pay_pkg.amount),0) - $paidfrom - $where AND $istax - $group_all"; - - my %data; - my %total; - # note that we use keys(%sql) here and keys(%all_sql) later. nothing - # obligates us to use the same set of variables for the total query - # as for the individual category queries - foreach my $k (keys(%sql)) { - my $stmt = $sql{$k}; - warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG > 1; - my $sth = dbh->prepare($stmt); - # eight columns: pkgclass, taxclass, state, county, city, district - # taxnums (comma separated), value - $sth->execute - or die "failed to execute $k query: ".$sth->errstr; - while ( my $row = $sth->fetchrow_arrayref ) { - my $bin = $data - {$row->[0]} # pkgclass - {$row->[1] # taxclass - || ($breakdown{taxclass} ? 'Unclassified' : '')} - {$row->[2]} # state - {$row->[3] ? $row->[3] . ' County' : ''} # county - {$row->[4]} # city - {$row->[5]} # district - ||= []; - push @$bin, [ $k, $row->[6], $row->[7] ]; - } - } - warn "DATA:\n".Dumper(\%data) if $DEBUG; - - foreach my $k (keys %all_sql) { - warn "\nTOTAL ".uc($k).":\n".$all_sql{$k}."\n" if $DEBUG; - my $sth = dbh->prepare($all_sql{$k}); - # three columns: pkgclass, taxnums (comma separated), value - $sth->execute - or die "failed to execute $k totals query: ".$sth->errstr; - while ( my $row = $sth->fetchrow_arrayref ) { - my $bin = $total{$row->[0]} ||= []; - push @$bin, [ $k, $row->[1], $row->[2] ]; - } - } - warn "TOTALS:\n".Dumper(\%total) if $DEBUG > 1; - - # $data{$pkgclass}{$taxclass}{$state}{$county}{$city}{$district} = [ - # [ 'taxable', taxnums, amount ], - # [ 'exempt_cust', taxnums, amount ], - # ... - # ] - # non-requested grouping levels simply collapse into key = '' - - # the much-maligned "out of taxable region"... - # find sales that are not linked to any tax with this name - # but are still inside the date range/agent criteria. - # - # This doesn't use $select_all/$group_all because we want a single number, - # not a breakdown by pkgclass. Unless someone needs that eventually, - # in which case we'll turn it into an %all_sql query. - - my $outside_where = - "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending"; - if ( $agentnum ) { - $outside_where .= " AND cust_main.agentnum = $agentnum"; - } - $outside_where .= " - AND NOT EXISTS( - SELECT 1 FROM cust_tax_exempt_pkg - JOIN cust_main_county USING (taxnum) - WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum - AND COALESCE(cust_main_county.taxname,'Tax') = $taxname - AND cust_tax_exempt_pkg.creditbillpkgnum IS NULL - ) - AND NOT EXISTS( - SELECT 1 FROM cust_bill_pkg_tax_location - JOIN cust_main_county USING (taxnum) - WHERE cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum - AND COALESCE(cust_main_county.taxname,'Tax') = $taxname - )"; - my $sql_outside = "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) - FROM cust_bill_pkg - $join_cust_pkg - $outside_where - AND $nottax - "; - warn "\nOUT_SALES:\n$sql_outside\n" if $DEBUG; - my $out_sales = FS::Record->scalar_sql($sql_outside); - - # and out-of-region credit applications, also (excluding those applied - # to out-of-region sales _or taxes_) - if ( $opt{credit_date} eq 'cust_credit_bill' ) { - $outside_where =~ s/cust_bill._date/cust_credit_bill._date/g; - } - - $sql_outside = "SELECT SUM(cust_credit_bill_pkg.amount) - FROM cust_credit_bill_pkg - JOIN cust_bill_pkg USING (billpkgnum) - $join_cust_pkg - JOIN cust_credit_bill USING (creditbillnum) - $outside_where - AND NOT EXISTS( - SELECT 1 FROM cust_bill_pkg_tax_location - JOIN cust_main_county USING (taxnum) - WHERE cust_bill_pkg_tax_location.billpkgnum = cust_bill_pkg.billpkgnum - AND COALESCE(cust_main_county.taxname,'Tax') = $taxname - ) - "; - warn "\nOUT_CREDIT:\n$sql_outside\n" if $DEBUG; - my $out_credit = FS::Record->scalar_sql($sql_outside); - - my %taxrates; - foreach my $tax ( - qsearch('cust_main_county', { - country => $country, - tax => { op => '>', value => 0 } - }) ) - { - $taxrates{$tax->taxnum} = $tax->tax; - } - - # return the data - bless { - 'opt' => \%opt, - 'data' => \%data, - 'total' => \%total, - 'taxrates' => \%taxrates, - 'out_sales' => $out_sales, - 'out_credit' => $out_credit, - }, $class; -} - -sub opt { - my $self = shift; - $self->{opt}; -} - -sub data { - my $self = shift; - $self->{data}; -} - -# sub fetchall_array... - -sub table { - my $self = shift; - my @columns = (qw(pkgclass taxclass state county city district)); - # taxnums, field headings, and amounts - my @rows; - my %row_template; - - # de-treeify this thing - my $descend; - $descend = sub { - my ($tree, $level) = @_; - if ( ref($tree) eq 'HASH' ) { - foreach my $k ( sort { - -1*($b eq '') # sort '' to the end - or ($a eq '') # sort '' to the end - or ($a <=> $b) # sort numbers as numbers - or ($a cmp $b) # sort alphabetics as alphabetics - } keys %$tree ) - { - $row_template{ $columns[$level] } = $k; - &{ $descend }($tree->{$k}, $level + 1); - if ( $level == 0 ) { - # then insert the total row for the pkgclass - $row_template{'total'} = 1; # flag it as a total - &{ $descend }($self->{total}->{$k}, 1); - $row_template{'total'} = 0; - } - } - } elsif ( ref($tree) eq 'ARRAY' ) { - # then we've reached the bottom; elements of this array are arrayrefs - # of [ field, taxnums, amount ]. - # start with the inherited location-element fields - my %this_row = %row_template; - my %taxnums; - foreach my $x (@$tree) { - # accumulate taxnums - foreach (split(',', $x->[1])) { - $taxnums{$_} = 1; - } - # and money values - $this_row{ $x->[0] } = $x->[2]; - } - # store combined taxnums - $this_row{taxnums} = join(',', sort { $a cmp $b } keys %taxnums); - # and calculate row totals - $this_row{sales} = sprintf('%.2f', - $this_row{taxable} + - $this_row{sales_credited} + - $this_row{exempt_cust} + - $this_row{exempt_pkg} + - $this_row{exempt_monthly} - ); - $this_row{credits} = sprintf('%.2f', - $this_row{sales_credited} + - $this_row{exempt_credited} + - $this_row{tax_credited} - ); - # and give it a label - if ( $this_row{total} ) { - $this_row{label} = 'Total'; - } else { - $this_row{label} = join(', ', grep $_, - $this_row{taxclass}, - $this_row{state}, - $this_row{county}, # already has ' County' suffix - $this_row{city}, - $this_row{district} - ); - } - # and indicate the tax rate, if any - my $rate; - foreach (keys %taxnums) { - $rate ||= $self->{taxrates}->{$_}; - if ( $rate != $self->{taxrates}->{$_} ) { - $rate = 'variable'; - last; - } - } - if ( $rate eq 'variable' ) { - $this_row{rate} = 'variable'; - } elsif ( $rate > 0 ) { - $this_row{rate} = sprintf('%.2f', $rate); - } - push @rows, \%this_row; - } - }; - - &{ $descend }($self->{data}, 0); - - warn "TABLE:\n".Dumper(\@rows) if $self->{opt}->{debug}; - return @rows; -} - -sub taxrates { - my $self = shift; - $self->{taxrates} -} - -sub title { - my $self = shift; - my $string = ''; - if ( $self->{opt}->{agentnum} ) { - my $agent = qsearchs('agent', { agentnum => $self->{opt}->{agentnum} }); - $string .= $agent->agent . ' '; - } - $string .= 'Tax Report: '; # XXX localization - if ( $self->{opt}->{beginning} ) { - $string .= time2str('%h %o %Y ', $self->{opt}->{beginning}); - } - $string .= 'through '; - if ( $self->{opt}->{ending} and $self->{opt}->{ending} < 4294967295 ) { - $string .= time2str('%h %o %Y', $self->{opt}->{ending}); - } else { - $string .= 'now'; - } - $string .= ' - ' . $self->{opt}->{taxname}; - return $string; -} - -1; diff --git a/FS/FS/Report/Tax/All.pm b/FS/FS/Report/Tax/All.pm new file mode 100644 index 000000000..26dbf5f0f --- /dev/null +++ b/FS/FS/Report/Tax/All.pm @@ -0,0 +1,110 @@ +package FS::Report::Tax::All; + +use strict; +use vars qw($DEBUG); +use FS::Record qw(dbh qsearch qsearchs group_concat_sql); +use FS::Report::Tax::ByName; +use Date::Format qw( time2str ); + +use Data::Dumper; + +$DEBUG = 0; + +=item report OPTIONS + +Constructor. Generates a tax report using the internal tax rate system, +showing all taxes, broken down by tax name and country. + +Required parameters: +- beginning, ending: the date range as Unix timestamps. + +Optional parameters: +- debug: sets the debug level. 1 will warn the data collected for the report; +2 will also warn all of the SQL statements. + +=cut + +# because there's not yet a "DBIx::DBSchema::View"... + +sub report { + my $class = shift; + my %opt = @_; + + $DEBUG ||= $opt{debug}; + + my($beginning, $ending) = @opt{'beginning', 'ending'}; + + # figure out which reports we need to run + my @taxname_and_country = qsearch({ + table => 'cust_main_county', + select => 'country, taxname', + hashref => { + tax => { op => '>', value => '0' } + }, + order_by => 'GROUP BY country, taxname ORDER BY country, taxname', + }); + my @table; + foreach (@taxname_and_country) { + my $taxname = $_->taxname || 'Tax'; + my $country = $_->country; + my $report = FS::Report::Tax::ByName->report( + %opt, + taxname => $taxname, + country => $country, + total_only => 1, + ); + # will have only one total row (should be only one row at all) + my ($total_row) = grep { $_->{total} } $report->table; + $total_row->{total} = 0; # but in this context it's a detail row + $total_row->{taxname} = $taxname; + $total_row->{country} = $country; + $total_row->{label} = "$country - $taxname"; + push @table, $total_row; + } + my $self = bless { + 'opt' => \%opt, + 'table' => \@table, + }, $class; + + $self; +} + +sub opt { + my $self = shift; + $self->{opt}; +} + +sub data { + my $self = shift; + $self->{data}; +} + +# sub fetchall_array... + +sub table { + my $self = shift; + @{ $self->{table} }; +} + +sub title { + my $self = shift; + my $string = ''; + if ( $self->{opt}->{agentnum} ) { + my $agent = qsearchs('agent', { agentnum => $self->{opt}->{agentnum} }); + $string .= $agent->agent . ' '; + } + $string .= 'Tax Report: '; # XXX localization + if ( $self->{opt}->{beginning} ) { + $string .= time2str('%h %o %Y ', $self->{opt}->{beginning}); + } + $string .= 'through '; + if ( $self->{opt}->{ending} and $self->{opt}->{ending} < 4294967295 ) { + $string .= time2str('%h %o %Y', $self->{opt}->{ending}); + } else { + $string .= 'now'; + } + $string .= ' - all taxes'; + return $string; +} + +1; diff --git a/FS/FS/Report/Tax/ByName.pm b/FS/FS/Report/Tax/ByName.pm new file mode 100644 index 000000000..88695b909 --- /dev/null +++ b/FS/FS/Report/Tax/ByName.pm @@ -0,0 +1,678 @@ +package FS::Report::Tax::ByName; + +use strict; +use vars qw($DEBUG); +use FS::Record qw(dbh qsearch qsearchs group_concat_sql); +use Date::Format qw( time2str ); + +use Data::Dumper; + +$DEBUG = 0; + +=item report OPTIONS + +Constructor. Generates a tax report using the internal tax rate system +(L), showing all taxes with a specified tax name, +broken down by state/county. Optionally, the taxes can be broken down further +by city/district, tax class, or package class. + +Required parameters: + +- beginning, ending: the date range as Unix timestamps. +- taxname: the name of the tax (corresponds to C). +- country: the country code. + +Optional parameters: +- agentnum: limit to this agentnum.num. +- breakdown: hashref of the fields to group by. Keys can be 'city', +'district', 'pkgclass', or 'taxclass'; values should be true. +- total_only: don't run the tax group queries, only the totals queries. +Returns one row, except in the unlikely event you're using breakdown by +package class. +- debug: sets the debug level. 1 will warn the data collected for the report; +2 will also warn all of the SQL statements. + +=cut + +sub report { + my $class = shift; + my %opt = @_; + + $DEBUG ||= $opt{debug}; + + my($beginning, $ending) = @opt{'beginning', 'ending'}; + + my ($taxname, $country, %breakdown); + + # taxname can contain arbitrary punctuation; escape it properly and + # include $taxname unquoted elsewhere + $taxname = dbh->quote($opt{'taxname'}); + + if ( $opt{country} =~ /^(\w\w)$/ ) { + $country = $1; + } else { + die "country required"; + } + + # %breakdown: short name => field identifier + # null classnum should remain null, not be converted to zero + %breakdown = ( + 'taxclass' => 'cust_main_county.taxclass', + 'pkgclass' => 'COALESCE(part_fee.classnum,part_pkg.classnum)', + 'city' => 'cust_main_county.city', + 'district' => 'cust_main_county.district', + 'state' => 'cust_main_county.state', + 'county' => 'cust_main_county.county', + ); + foreach (qw(taxclass pkgclass city district)) { + delete $breakdown{$_} unless $opt{breakdown}->{$_}; + } + + my $join_cust = ' JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) '; + + my $join_cust_pkg = $join_cust. + ' LEFT JOIN cust_pkg USING ( pkgnum ) + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN part_fee USING ( feepart ) '; + + my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg "; + + # all queries MUST be linked to both cust_bill and cust_main_county + + # Either or both of these can be used to link cust_bill_pkg to + # cust_main_county. This one links a taxed line item (billpkgnum) to a tax rate + # (taxnum), and gives the amount of tax charged on that line item under that + # rate (as tax_amount). + my $pkg_tax = "SELECT SUM(amount) as tax_amount, taxnum, ". + "taxable_billpkgnum AS billpkgnum ". + "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ". + "GROUP BY taxable_billpkgnum, taxnum"; + + # This one links a tax-exempted line item (billpkgnum) to a tax rate + # (taxnum), and gives the amount of the tax exemption. EXEMPT_WHERE must + # be replaced with an expression to further limit the tax exemptions + # that will be included, or "TRUE" to not limit them. + # + # Note that tax exemptions with non-null creditbillpkgnum are always + # excluded. Those are "negative exemptions" created by crediting a sale + # that had received an exemption. + my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ". + "FROM cust_tax_exempt_pkg WHERE + ( EXEMPT_WHERE ) + AND cust_tax_exempt_pkg.creditbillpkgnum IS NULL + GROUP BY billpkgnum, taxnum"; + + my $where = "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending ". + "AND COALESCE(cust_main_county.taxname,'Tax') = $taxname ". + "AND cust_main_county.country = '$country'"; + # SELECT/GROUP clauses for first-level queries + my $select = "SELECT "; + my $group = "GROUP BY "; + foreach (qw(pkgclass taxclass state county city district)) { + if ( $breakdown{$_} ) { + $select .= "$breakdown{$_} AS $_, "; + $group .= "$breakdown{$_}, "; + } else { + $select .= "NULL AS $_, "; + } + } + $select .= group_concat_sql('DISTINCT(cust_main_county.taxnum)', ',') . + ' AS taxnums, '; + $group =~ s/, $//; + + # SELECT/GROUP clauses for second-level (totals) queries + # breakdown by package class only, if anything + my $select_all = "SELECT NULL AS pkgclass, "; + my $group_all = ""; + if ( $breakdown{pkgclass} ) { + $select_all = "SELECT $breakdown{pkgclass} AS pkgclass, "; + $group_all = "GROUP BY $breakdown{pkgclass}"; + } + $select_all .= group_concat_sql('DISTINCT(cust_main_county.taxnum)', ',') . + ' AS taxnums, '; + + my $agentnum; + if ( $opt{agentnum} and $opt{agentnum} =~ /^(\d+)$/ ) { + $agentnum = $1; + my $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + die "agent not found" unless $agent; + $where .= " AND cust_main.agentnum = $agentnum"; + } + + my $nottax = + '(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)'; + + # one query for each column of the report + # plus separate queries for the totals row + my (%sql, %all_sql); + + # SALES QUERIES (taxable sales, all types of exempt sales) + # ------------- + + # general form + my $exempt = "$select SUM(exempt_charged) + FROM cust_main_county + JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax + $group"; + + my $all_exempt = "$select_all SUM(exempt_charged) + FROM cust_main_county + JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax + $group_all"; + + # sales to tax-exempt customers + $sql{exempt_cust} = $exempt; + $sql{exempt_cust} =~ s/EXEMPT_WHERE/exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; + $all_sql{exempt_cust} = $all_exempt; + $all_sql{exempt_cust} =~ s/EXEMPT_WHERE/exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; + + # sales of tax-exempt packages + $sql{exempt_pkg} = $exempt; + $sql{exempt_pkg} =~ s/EXEMPT_WHERE/exempt_setup = 'Y' OR exempt_recur = 'Y'/; + $all_sql{exempt_pkg} = $all_exempt; + $all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/exempt_setup = 'Y' OR exempt_recur = 'Y'/; + + # monthly per-customer exemptions + $sql{exempt_monthly} = $exempt; + $sql{exempt_monthly} =~ s/EXEMPT_WHERE/exempt_monthly = 'Y'/; + $all_sql{exempt_monthly} = $all_exempt; + $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/exempt_monthly = 'Y'/; + + # credits applied to taxable sales + # Note that negative exemptions (from exempt sales being credited) are NOT + # counted when calculating the exempt amount. (See above.) Therefore we need + # to NOT include any credits against exempt sales in this amount, either. + # These two subqueries implement that. They have joins to cust_credit_bill + # and cust_bill so that credits can be filtered by application date if + # requested. + + # Each row here is the sum of credits applied to a line item. + my $sales_credit = + "SELECT billpkgnum, SUM(cust_credit_bill_pkg.amount) AS credited + FROM cust_credit_bill_pkg + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_bill USING (invnum) + WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending + GROUP BY billpkgnum + "; + + # Each row here is the sum of negative exemptions applied to a combination + # of line item and tax definition. + my $exempt_credit = + "SELECT cust_credit_bill_pkg.billpkgnum, taxnum, + 0 - SUM(cust_tax_exempt_pkg.amount) AS exempt_credited + FROM cust_credit_bill_pkg + LEFT JOIN cust_tax_exempt_pkg USING (creditbillpkgnum) + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_bill USING (invnum) + WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending + GROUP BY cust_credit_bill_pkg.billpkgnum, taxnum + "; + + if ( $opt{credit_date} eq 'cust_credit_bill' ) { + $sales_credit =~ s/cust_bill._date/cust_credit_bill._date/g; + $exempt_credit =~ s/cust_bill._date/cust_credit_bill._date/g; + } + + $sql{sales_credited} = "$select + SUM(COALESCE(credited, 0) - COALESCE(exempt_credited, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) + $join_cust_pkg $where AND $nottax + $group + "; + + $all_sql{sales_credited} = "$select_all + SUM(COALESCE(credited, 0) - COALESCE(exempt_credited, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) + $join_cust_pkg $where AND $nottax + $group_all + "; + + # also include the exempt-sales credit amount, for the credit report + $sql{exempt_credited} = "$select + SUM(COALESCE(exempt_credited, 0)) + FROM cust_main_county + LEFT JOIN ($exempt_credit) AS exempt_credit USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax + $group + "; + + $all_sql{exempt_credited} = "$select_all + SUM(COALESCE(exempt_credited, 0)) + FROM cust_main_county + LEFT JOIN ($exempt_credit) AS exempt_credit USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax + $group_all + "; + + # taxable sales + $sql{taxable} = "$select + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + + COALESCE(exempt_credited, 0) + ) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) + $join_cust_pkg $where AND $nottax + $group"; + + $all_sql{taxable} = "$select_all + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + + COALESCE(exempt_credited, 0) + ) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) + $join_cust_pkg $where AND $nottax + $group_all"; + + $sql{taxable} =~ s/EXEMPT_WHERE/TRUE/; # unrestricted + $all_sql{taxable} =~ s/EXEMPT_WHERE/TRUE/; + + # estimated tax (taxable * rate) + $sql{estimated} = "$select + SUM(cust_main_county.tax / 100 * + (cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + + COALESCE(exempt_credited, 0) + ) + ) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) + $join_cust_pkg $where AND $nottax + $group"; + + $all_sql{estimated} = "$select_all + SUM(cust_main_county.tax / 100 * + (cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + + COALESCE(exempt_credited, 0) + ) + ) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) + $join_cust_pkg $where AND $nottax + $group_all"; + + $sql{estimated} =~ s/EXEMPT_WHERE/TRUE/; # unrestricted + $all_sql{estimated} =~ s/EXEMPT_WHERE/TRUE/; + + # there isn't one for 'sales', because we calculate sales by adding up + # the taxable and exempt columns. + + # TAX QUERIES (billed tax, credited tax, collected tax) + # ----------- + + # sum of billed tax: + # join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location + my $taxfrom = " FROM cust_bill_pkg + $join_cust + LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) + LEFT JOIN cust_main_county USING ( taxnum )"; + + if ( $breakdown{pkgclass} ) { + # If we're not grouping by package class, this is unnecessary, and + # probably really expensive. + # Remember that fees also have package classes. + $taxfrom .= " + LEFT JOIN cust_bill_pkg AS taxable + ON (cust_bill_pkg_tax_location.taxable_billpkgnum = taxable.billpkgnum) + LEFT JOIN cust_pkg ON (taxable.pkgnum = cust_pkg.pkgnum) + LEFT JOIN part_pkg USING (pkgpart) + LEFT JOIN part_fee ON (taxable.feepart = part_fee.feepart) "; + } + + my $istax = "cust_bill_pkg.pkgnum = 0 and cust_bill_pkg.feepart is null"; + + $sql{tax} = "$select COALESCE(SUM(cust_bill_pkg_tax_location.amount),0) + $taxfrom + $where AND $istax + $group"; + + $all_sql{tax} = "$select_all COALESCE(SUM(cust_bill_pkg_tax_location.amount),0) + $taxfrom + $where AND $istax + $group_all"; + + # sum of credits applied against billed tax + # ($creditfrom includes join of taxable item to part_pkg/part_fee if + # with_pkgclass is on) + my $creditfrom = $taxfrom . + ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)' . + ' JOIN cust_credit_bill USING (creditbillnum)'; + my $creditwhere = $where . + ' AND billpkgtaxratelocationnum IS NULL'; + + # if the credit_date option is set to application date, change + # $creditwhere accordingly + if ( $opt{credit_date} eq 'cust_credit_bill' ) { + $creditwhere =~ s/cust_bill._date/cust_credit_bill._date/g; + } + + $sql{tax_credited} = "$select COALESCE(SUM(cust_credit_bill_pkg.amount),0) + $creditfrom + $creditwhere AND $istax + $group"; + + $all_sql{tax_credited} = "$select_all COALESCE(SUM(cust_credit_bill_pkg.amount),0) + $creditfrom + $creditwhere AND $istax + $group_all"; + + # sum of tax paid + # this suffers from the same ambiguity as anything else that applies + # received payments to specific packages, but in reality the discrepancy + # should be minimal since people either pay their bill or don't. + # the join is on billpkgtaxlocationnum to avoid cross-producting. + + my $paidfrom = $taxfrom . + ' JOIN cust_bill_pay_pkg'. + ' ON (cust_bill_pay_pkg.billpkgtaxlocationnum ='. + ' cust_bill_pkg_tax_location.billpkgtaxlocationnum)'; + + $sql{tax_paid} = "$select COALESCE(SUM(cust_bill_pay_pkg.amount),0) + $paidfrom + $where AND $istax + $group"; + + $all_sql{tax_paid} = "$select_all COALESCE(SUM(cust_bill_pay_pkg.amount),0) + $paidfrom + $where AND $istax + $group_all"; + + my %data; + my %total; + # note that we use keys(%sql) here and keys(%all_sql) later. nothing + # obligates us to use the same set of variables for the total query + # as for the individual category queries + foreach my $k (keys(%sql)) { + my $stmt = $sql{$k}; + warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG > 1; + my $sth = dbh->prepare($stmt); + # eight columns: pkgclass, taxclass, state, county, city, district + # taxnums (comma separated), value + $sth->execute + or die "failed to execute $k query: ".$sth->errstr; + while ( my $row = $sth->fetchrow_arrayref ) { + my $bin = $data + {$row->[0]} # pkgclass + {$row->[1] # taxclass + || ($breakdown{taxclass} ? 'Unclassified' : '')} + {$row->[2]} # state + {$row->[3] ? $row->[3] . ' County' : ''} # county + {$row->[4]} # city + {$row->[5]} # district + ||= []; + push @$bin, [ $k, $row->[6], $row->[7] ]; + } + } + warn "DATA:\n".Dumper(\%data) if $DEBUG; + + foreach my $k (keys %all_sql) { + warn "\nTOTAL ".uc($k).":\n".$all_sql{$k}."\n" if $DEBUG; + my $sth = dbh->prepare($all_sql{$k}); + # three columns: pkgclass, taxnums (comma separated), value + $sth->execute + or die "failed to execute $k totals query: ".$sth->errstr; + while ( my $row = $sth->fetchrow_arrayref ) { + my $bin = $total{$row->[0]} ||= []; + push @$bin, [ $k, $row->[1], $row->[2] ]; + } + } + warn "TOTALS:\n".Dumper(\%total) if $DEBUG > 1; + + # $data{$pkgclass}{$taxclass}{$state}{$county}{$city}{$district} = [ + # [ 'taxable', taxnums, amount ], + # [ 'exempt_cust', taxnums, amount ], + # ... + # ] + # non-requested grouping levels simply collapse into key = '' + + # the much-maligned "out of taxable region"... + # find sales that are not linked to any tax with this name + # but are still inside the date range/agent criteria. + # + # This doesn't use $select_all/$group_all because we want a single number, + # not a breakdown by pkgclass. Unless someone needs that eventually, + # in which case we'll turn it into an %all_sql query. + + my $outside_where = + "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending"; + if ( $agentnum ) { + $outside_where .= " AND cust_main.agentnum = $agentnum"; + } + $outside_where .= " + AND NOT EXISTS( + SELECT 1 FROM cust_tax_exempt_pkg + JOIN cust_main_county USING (taxnum) + WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum + AND COALESCE(cust_main_county.taxname,'Tax') = $taxname + AND cust_tax_exempt_pkg.creditbillpkgnum IS NULL + ) + AND NOT EXISTS( + SELECT 1 FROM cust_bill_pkg_tax_location + JOIN cust_main_county USING (taxnum) + WHERE cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum + AND COALESCE(cust_main_county.taxname,'Tax') = $taxname + )"; + my $sql_outside = "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) + FROM cust_bill_pkg + $join_cust_pkg + $outside_where + AND $nottax + "; + warn "\nOUT_SALES:\n$sql_outside\n" if $DEBUG; + my $out_sales = FS::Record->scalar_sql($sql_outside); + + # and out-of-region credit applications, also (excluding those applied + # to out-of-region sales _or taxes_) + if ( $opt{credit_date} eq 'cust_credit_bill' ) { + $outside_where =~ s/cust_bill._date/cust_credit_bill._date/g; + } + + $sql_outside = "SELECT SUM(cust_credit_bill_pkg.amount) + FROM cust_credit_bill_pkg + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg + JOIN cust_credit_bill USING (creditbillnum) + $outside_where + AND NOT EXISTS( + SELECT 1 FROM cust_bill_pkg_tax_location + JOIN cust_main_county USING (taxnum) + WHERE cust_bill_pkg_tax_location.billpkgnum = cust_bill_pkg.billpkgnum + AND COALESCE(cust_main_county.taxname,'Tax') = $taxname + ) + "; + warn "\nOUT_CREDIT:\n$sql_outside\n" if $DEBUG; + my $out_credit = FS::Record->scalar_sql($sql_outside); + + my %taxrates; + foreach my $tax ( + qsearch('cust_main_county', { + country => $country, + tax => { op => '>', value => 0 } + }) ) + { + $taxrates{$tax->taxnum} = $tax->tax; + } + + # return the data + bless { + 'opt' => \%opt, + 'data' => \%data, + 'total' => \%total, + 'taxrates' => \%taxrates, + 'out_sales' => $out_sales, + 'out_credit' => $out_credit, + }, $class; +} + +sub opt { + my $self = shift; + $self->{opt}; +} + +sub data { + my $self = shift; + $self->{data}; +} + +# sub fetchall_array... + +sub table { + my $self = shift; + my @columns = (qw(pkgclass taxclass state county city district)); + # taxnums, field headings, and amounts + my @rows; + my %row_template; + + # de-treeify this thing + my $descend; + $descend = sub { + my ($tree, $level) = @_; + if ( ref($tree) eq 'HASH' ) { + foreach my $k ( sort { + -1*($b eq '') # sort '' to the end + or ($a eq '') # sort '' to the end + or ($a <=> $b) # sort numbers as numbers + or ($a cmp $b) # sort alphabetics as alphabetics + } keys %$tree ) + { + $row_template{ $columns[$level] } = $k; + &{ $descend }($tree->{$k}, $level + 1); + if ( $level == 0 ) { + # then insert the total row for the pkgclass + $row_template{'total'} = 1; # flag it as a total + &{ $descend }($self->{total}->{$k}, 1); + $row_template{'total'} = 0; + } + } + } elsif ( ref($tree) eq 'ARRAY' ) { + # then we've reached the bottom; elements of this array are arrayrefs + # of [ field, taxnums, amount ]. + # start with the inherited location-element fields + my %this_row = %row_template; + my %taxnums; + foreach my $x (@$tree) { + # accumulate taxnums + foreach (split(',', $x->[1])) { + $taxnums{$_} = 1; + } + # and money values + $this_row{ $x->[0] } = $x->[2]; + } + # store combined taxnums + $this_row{taxnums} = join(',', sort { $a cmp $b } keys %taxnums); + # and calculate row totals + $this_row{sales} = sprintf('%.2f', + $this_row{taxable} + + $this_row{sales_credited} + + $this_row{exempt_cust} + + $this_row{exempt_pkg} + + $this_row{exempt_monthly} + ); + $this_row{credits} = sprintf('%.2f', + $this_row{sales_credited} + + $this_row{exempt_credited} + + $this_row{tax_credited} + ); + # and give it a label + if ( $this_row{total} ) { + $this_row{label} = 'Total'; + } else { + $this_row{label} = join(', ', grep $_, + $this_row{taxclass}, + $this_row{state}, + $this_row{county}, # already has ' County' suffix + $this_row{city}, + $this_row{district} + ); + } + # and indicate the tax rate, if any + my $rate; + foreach (keys %taxnums) { + $rate ||= $self->{taxrates}->{$_}; + if ( $rate != $self->{taxrates}->{$_} ) { + $rate = 'variable'; + last; + } + } + if ( $rate eq 'variable' ) { + $this_row{rate} = 'variable'; + } elsif ( $rate > 0 ) { + $this_row{rate} = sprintf('%.2f', $rate); + } + push @rows, \%this_row; + } + }; + + &{ $descend }($self->{data}, 0); + + warn "TABLE:\n".Dumper(\@rows) if $self->{opt}->{debug}; + return @rows; +} + +sub taxrates { + my $self = shift; + $self->{taxrates} +} + +sub title { + my $self = shift; + my $string = ''; + if ( $self->{opt}->{agentnum} ) { + my $agent = qsearchs('agent', { agentnum => $self->{opt}->{agentnum} }); + $string .= $agent->agent . ' '; + } + $string .= 'Tax Report: '; # XXX localization + if ( $self->{opt}->{beginning} ) { + $string .= time2str('%h %o %Y ', $self->{opt}->{beginning}); + } + $string .= 'through '; + if ( $self->{opt}->{ending} and $self->{opt}->{ending} < 4294967295 ) { + $string .= time2str('%h %o %Y', $self->{opt}->{ending}); + } else { + $string .= 'now'; + } + $string .= ' - ' . $self->{opt}->{taxname}; + return $string; +} + +1; diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 2cb942524..d94f963a6 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -923,12 +923,13 @@ sub tables_hashref { '_date', @date_type, '', '', 'status', 'varchar', '', $char_d, '', '', 'statustext', 'text', 'NULL', '', '', '', + 'no_action', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'eventnum', #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ], 'unique' => [], 'index' => [ ['eventpart'], ['tablenum'], ['status'], - ['statustext'], ['_date'], + ['statustext'], ['_date'], ['no_action'], ], 'foreign_keys' => [ { columns => [ 'eventpart' ], @@ -3590,10 +3591,11 @@ sub tables_hashref { 'refnum', 'serial', '', '', '', '', 'referral', 'varchar', '', $char_d, '', '', 'disabled', 'char', 'NULL', 1, '', '', - 'agentnum', 'int', 'NULL', '', '', '', + 'agentnum', 'int', 'NULL', '', '', '', + 'title', 'varchar', 'NULL', $char_d, '', '', ], 'primary_key' => 'refnum', - 'unique' => [], + 'unique' => [ ['agentnum', 'title'] ], 'index' => [ ['disabled'], ['agentnum'], ], 'foreign_keys' => [ { columns => [ 'agentnum' ], diff --git a/FS/FS/TicketSystem/RT_Internal.pm b/FS/FS/TicketSystem/RT_Internal.pm index 6b2d3544c..1c4513e6d 100644 --- a/FS/FS/TicketSystem/RT_Internal.pm +++ b/FS/FS/TicketSystem/RT_Internal.pm @@ -468,9 +468,6 @@ sub get_ticket_object { } my $Tickets = RT::Tickets->new($session->{CurrentUser}); $Tickets->FromSQL($query); - if ( $DEBUG ) { # temporary for RT#39536 - warn "[get_ticket_object] " . $Tickets->BuildSelectQuery . "\n\n"; - } return $Tickets->First; } diff --git a/FS/FS/cust_event.pm b/FS/FS/cust_event.pm index 1d8af1e6e..3edfaefde 100644 --- a/FS/FS/cust_event.pm +++ b/FS/FS/cust_event.pm @@ -54,6 +54,13 @@ L and L for conversion functions. =item statustext - additional status detail (i.e. error or progress message) +=item no_action - 'Y' if the event action wasn't performed. Some actions +contain an internal check to see if the action is going to be impossible (for +example, emailing a notice to a customer who has no email address), and if so, +won't attempt the action. It shouldn't be reported as a failure because +there's no need to retry it. However, the action should set no_action = 'Y' +so that there's a record. + =back =head1 METHODS @@ -141,6 +148,7 @@ sub check { || $self->ut_number('_date') || $self->ut_enum('status', [qw( new locked done failed initial)]) || $self->ut_anything('statustext') + || $self->ut_flag('no_action') ; return $error if $error; @@ -372,11 +380,46 @@ sub search_sql_where { push @search, "cust_event._date <= $1"; } - if ( $param->{'failed'} ) { - push @search, "statustext != ''", - "statustext IS NOT NULL", - "statustext != 'N/A'"; - } + #if ( $param->{'failed'} ) { + # push @search, "statustext != ''", + # "statustext IS NOT NULL", + # "statustext != 'N/A'"; + #} + # huh? + + if ( $param->{'event_status'} ) { + + my @status; + my ($done_Y, $done_N); + foreach (@{ $param->{'event_status'} }) { + if ($_ eq 'done_Y') { + $done_Y = 1; + } elsif ( $_ eq 'done_N' ) { + $done_N = 1; + } else { + push @status, $_; + } + } + if ( $done_Y or $done_N ) { + push @status, 'done'; + } + if ( @status ) { + push @search, "cust_event.status IN(" . + join(',', map "'$_'", @status) . + ')'; + } + + if ( $done_Y and not $done_N ) { + push @search, "cust_event.no_action IS NULL"; + } elsif ( $done_N and not $done_Y ) { + push @search, "cust_event.no_action = 'Y'"; + } # else they're both true, so don't add a constraint, or both false, + # and it doesn't matter. + + } # event_status + + # always hide initialization + push @search, 'cust_event.status != \'initial\''; if ( $param->{'custnum'} =~ /^(\d+)$/ ) { push @search, "cust_main.custnum = '$1'"; diff --git a/FS/FS/cust_location.pm b/FS/FS/cust_location.pm index 2b8a5c88d..f38e8efcd 100644 --- a/FS/FS/cust_location.pm +++ b/FS/FS/cust_location.pm @@ -249,20 +249,22 @@ sub insert { # cust_location exports #my $export_args = $options{'export_args'} || []; - my @part_export = - map qsearch( 'part_export', {exportnum=>$_} ), - $conf->config('cust_location-exports'); #, $agentnum - - foreach my $part_export ( @part_export ) { - my $error = $part_export->export_insert($self); #, @$export_args); - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return "exporting to ". $part_export->exporttype. - " (transaction rolled back): $error"; + # don't export custnum_pending cases, let follow-up replace handle that + if ($self->custnum || $self->prospectnum) { + my @part_export = + map qsearch( 'part_export', {exportnum=>$_} ), + $conf->config('cust_location-exports'); #, $agentnum + + foreach my $part_export ( @part_export ) { + my $error = $part_export->export_insert($self); #, @$export_args); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "exporting to ". $part_export->exporttype. + " (transaction rolled back): $error"; + } } } - $dbh->commit or die $dbh->errstr if $oldAutoCommit; ''; } @@ -311,20 +313,22 @@ sub replace { # cust_location exports #my $export_args = $options{'export_args'} || []; - my @part_export = - map qsearch( 'part_export', {exportnum=>$_} ), - $conf->config('cust_location-exports'); #, $agentnum - - foreach my $part_export ( @part_export ) { - my $error = $part_export->export_replace($self, $old); #, @$export_args); - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return "exporting to ". $part_export->exporttype. - " (transaction rolled back): $error"; + # don't export custnum_pending cases, let follow-up replace handle that + if ($self->custnum || $self->prospectnum) { + my @part_export = + map qsearch( 'part_export', {exportnum=>$_} ), + $conf->config('cust_location-exports'); #, $agentnum + + foreach my $part_export ( @part_export ) { + my $error = $part_export->export_replace($self, $old); #, @$export_args); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "exporting to ". $part_export->exporttype. + " (transaction rolled back): $error"; + } } } - $dbh->commit or die $dbh->errstr if $oldAutoCommit; ''; } @@ -724,7 +728,7 @@ sub label_prefix { $prefix; } -=item county_state_county +=item county_state_country Returns a string consisting of just the county, state and country. diff --git a/FS/FS/cust_main_Mixin.pm b/FS/FS/cust_main_Mixin.pm index 9a2a9d7b1..bbba8c5f7 100644 --- a/FS/FS/cust_main_Mixin.pm +++ b/FS/FS/cust_main_Mixin.pm @@ -669,11 +669,25 @@ sub unsuspend_balance { my $maxbalance; if ($setting eq 'Zero') { $maxbalance = 0; + + # kind of a pain to load/check all cust_bill instead of just open ones, + # but if for some reason payment gets applied to later bills before + # earlier ones, we still want to consider the later ones as allowable balance } elsif ($setting eq 'Latest invoice charges') { my @cust_bill = $cust_main->cust_bill(); my $cust_bill = $cust_bill[-1]; #always want the most recent one - return unless $cust_bill; - $maxbalance = $cust_bill->charged || 0; + if ($cust_bill) { + $maxbalance = $cust_bill->charged || 0; + } else { + $maxbalance = 0; + } + } elsif ($setting eq 'Charges not past due') { + my $now = time; + $maxbalance = 0; + foreach my $cust_bill ($cust_main->cust_bill()) { + next unless $now <= ($cust_bill->due_date || $cust_bill->_date); + $maxbalance += $cust_bill->charged || 0; + } } elsif (length($setting)) { warn "Unrecognized unsuspend_balance setting $setting"; return; diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index 1bd18e015..1cc82357e 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -442,6 +442,21 @@ sub insert { my $conf = new FS::Conf; + if ($self->locationnum) { + my @part_export = + map qsearch( 'part_export', {exportnum=>$_} ), + $conf->config('cust_location-exports'); #, $agentnum + + foreach my $part_export ( @part_export ) { + my $error = $part_export->export_pkg_location($self); #, @$export_args); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "exporting to ". $part_export->exporttype. + " (transaction rolled back): $error"; + } + } + } + if ( ! $import && $conf->config('ticket_system') && $options{ticket_subject} ) { #this init stuff is still inefficient, but at least its limited to @@ -696,6 +711,24 @@ sub replace { } } + # also run exports if removing locationnum? + # doesn't seem to happen, and we don't export blank locationnum on insert... + if ($new->locationnum and ($new->locationnum != $old->locationnum)) { + my $conf = new FS::Conf; + my @part_export = + map qsearch( 'part_export', {exportnum=>$_} ), + $conf->config('cust_location-exports'); #, $agentnum + + foreach my $part_export ( @part_export ) { + my $error = $part_export->export_pkg_location($new); #, @$export_args); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "exporting to ". $part_export->exporttype. + " (transaction rolled back): $error"; + } + } + } + $dbh->commit or die $dbh->errstr if $oldAutoCommit; ''; @@ -2132,7 +2165,7 @@ sub change { my $time = time; - $hash{'setup'} = $time if $self->setup; + $hash{'setup'} = $time if $self->get('setup'); $hash{'change_date'} = $time; $hash{"change_$_"} = $self->$_() @@ -2153,16 +2186,18 @@ sub change { my $unused_credit = 0; my $keep_dates = $opt->{'keep_dates'}; - # Special case. If the pkgpart is changing, and the customer is - # going to be credited for remaining time, don't keep setup, bill, - # or last_bill dates, and DO pass the flag to cancel() to credit - # the customer. + # Special case. If the pkgpart is changing, and the customer is going to be + # credited for remaining time, don't keep setup, bill, or last_bill dates, + # and DO pass the flag to cancel() to credit the customer. If the old + # package had a setup date, set the new package's setup to the package + # change date so that it has the same status as before. if ( $opt->{'pkgpart'} and $opt->{'pkgpart'} != $self->pkgpart and $self->part_pkg->option('unused_credit_change', 1) ) { $unused_credit = 1; $keep_dates = 0; - $hash{$_} = '' foreach qw(setup bill last_bill); + $hash{'last_bill'} = ''; + $hash{'bill'} = ''; } if ( $keep_dates ) { diff --git a/FS/FS/cust_pkg/API.pm b/FS/FS/cust_pkg/API.pm index f87eed345..837cf40cc 100644 --- a/FS/FS/cust_pkg/API.pm +++ b/FS/FS/cust_pkg/API.pm @@ -10,4 +10,66 @@ sub API_getinfo { } +# currently only handles location change... +# eventually have it handle all sorts of package changes +sub API_change { + my $self = shift; + my %opt = @_; + + return { 'error' => 'Cannot change canceled package' } + if $self->get('cancel'); + + my %changeopt; + + # update location--accepts raw fields OR location + my %location_hash; + foreach my $field ( qw( + locationname + address1 + address2 + city + county + state + zip + addr_clean + country + censustract + censusyear + location_type + location_number + location_kind + incorporated + ) ) { + $location_hash{$field} = $opt{$field} if $opt{$field}; + } + return { 'error' => 'Cannot pass both locationnum and location fields' } + if $opt{'locationnum'} && %location_hash; + + if (%location_hash) { + my $cust_location = FS::cust_location->new({ + 'custnum' => $self->custnum, + %location_hash, + }); + $changeopt{'cust_location'} = $cust_location; + } elsif ($opt{'locationnum'}) { + $changeopt{'locationnum'} = $opt{'locationnum'}; + } + + # not quite "nothing changed" because passed changes might be identical to current record, + # we don't currently check for that, don't want to imply that we do...but maybe we should? + return { 'error' => 'No changes passed to method' } + unless $changeopt{'cust_location'} || $changeopt{'locationnum'}; + + $changeopt{'keep_dates'} = 1; + + my $pkg_or_error = $self->change( \%changeopt ); + my $error = ref($pkg_or_error) ? '' : $pkg_or_error; + + return { 'error' => $error } if $error; + + # return all fields? we don't yet expose them through FS::API + return { map { $_ => $pkg_or_error->get($_) } qw( pkgnum locationnum ) }; + +} + 1; diff --git a/FS/FS/part_event/Action/cust_bill_email.pm b/FS/FS/part_event/Action/cust_bill_email.pm index 3331a4cb6..80bcaa1a7 100644 --- a/FS/FS/part_event/Action/cust_bill_email.pm +++ b/FS/FS/part_event/Action/cust_bill_email.pm @@ -20,12 +20,18 @@ sub option_fields { sub default_weight { 51; } sub do_action { - my( $self, $cust_bill ) = @_; + my( $self, $cust_bill, $cust_event ) = @_; my $cust_main = $cust_bill->cust_main; $cust_bill->set('mode' => $self->option('modenum')); - $cust_bill->email unless $cust_main->invoice_noemail; + if ( $cust_main->invoice_noemail ) { + # what about if the customer has no email dest? + $cust_event->set('no_action', 'Y'); + return "customer has invoice_noemail flag"; + } else { + $cust_bill->email; + } } 1; diff --git a/FS/FS/part_event/Action/cust_bill_print.pm b/FS/FS/part_event/Action/cust_bill_print.pm index b94e882ff..e6a27a34e 100644 --- a/FS/FS/part_event/Action/cust_bill_print.pm +++ b/FS/FS/part_event/Action/cust_bill_print.pm @@ -24,14 +24,22 @@ sub option_fields { sub default_weight { 51; } sub do_action { - my( $self, $cust_bill ) = @_; + my( $self, $cust_bill, $cust_event ) = @_; #my $cust_main = $self->cust_main($cust_bill); my $cust_main = $cust_bill->cust_main; $cust_bill->set('mode' => $self->option('modenum')); - $cust_bill->print unless $self->option('skip_nopost') - && ! grep { $_ eq 'POST' } $cust_main->invoicing_list; + if ( $self->option('skip_nopost') + && ! grep { $_ eq 'POST' } $cust_main->invoicing_list + ) { + # then skip customers + $cust_event->set('no_action', 'Y'); + return "customer doesn't receive postal invoices"; # as statustext + + } else { + $cust_bill->print; + } } 1; diff --git a/FS/FS/part_export/bandwidth_com.pm b/FS/FS/part_export/bandwidth_com.pm index 4fbc2de21..6d868e640 100644 --- a/FS/FS/part_export/bandwidth_com.pm +++ b/FS/FS/part_export/bandwidth_com.pm @@ -56,6 +56,16 @@ with this IP address exists, one will be created.

If you are operating a central SIP gateway to receive traffic for all (or a subset of) customers, you should configure a phone service with a fixed value, or a list of fixed values, for the sip_server field.

+

To find your account ID and site ID: +

    +
  • Login to the Dashboard. +
    • +
  • Under "Your subaccounts", find the subaccount (site) that you want to use + for exported DIDs. Click the "manage sub-account" link.
    • +
  • Look at the URL. It will end in {"a":xxxxxxx,"s":yyyy}.
    • +
  • Your account ID is xxxxxxx, and the site ID is yyyy.
    • +
+

END ); diff --git a/FS/FS/part_export/cust_http.pm b/FS/FS/part_export/cust_http.pm index f72d00698..c13e18db1 100644 --- a/FS/FS/part_export/cust_http.pm +++ b/FS/FS/part_export/cust_http.pm @@ -55,7 +55,7 @@ tie %options, 'Tie::IxHash', ; %info = ( - 'svc' => [qw( cust_main cust_location )], + 'svc' => [qw( cust_main )], 'desc' => 'Send an HTTP or HTTPS GET or POST request, for customers.', 'options' => \%options, 'no_machine' => 1, diff --git a/FS/FS/part_export/cust_location_http.pm b/FS/FS/part_export/cust_location_http.pm new file mode 100644 index 000000000..fe7722340 --- /dev/null +++ b/FS/FS/part_export/cust_location_http.pm @@ -0,0 +1,196 @@ +package FS::part_export::cust_location_http; + +use strict; +use base qw( FS::part_export::http ); +use vars qw( %options %info ); + +my @location_fields = qw( + custnum + prospectnum + locationname + address1 + address2 + city + county + state + zip + country + latitude + longitude + censustract + censusyear + district + geocode + location_type + location_number + location_kind + incorporated +); + +tie %options, 'Tie::IxHash', + 'method' => { label =>'Method', + type =>'select', + #options =>[qw(POST GET)], + options =>[qw(POST)], + default =>'POST' }, + 'location_url' => { label => 'Location URL' }, + 'package_url' => { label => 'Package URL' }, + 'ssl_no_verify' => { label => 'Skip SSL certificate validation', + type => 'checkbox', + }, + 'include_fields' => { 'label' => 'Include fields', + 'type' => 'select', + 'multiple' => 1, + 'options' => [ @location_fields ] }, + 'location_data' => { 'label' => 'Location data', + 'type' => 'textarea' }, + 'package_data' => { 'label' => 'Package data', + 'type' => 'textarea' }, + 'success_regexp' => { + label => 'Success Regexp', + default => '', + }, +; + +%info = ( + 'svc' => [qw( cust_location )], + 'desc' => 'Send an HTTP or HTTPS GET or POST request, for customer locations', + 'options' => \%options, + 'no_machine' => 1, + 'notes' => <<'END', +Send an HTTP or HTTPS GET or POST to the specified URLs on customer location +creation/update (action 'location') and package location assignment/change (action 'package'). +Leave a URL blank to skip that action. +Always sends locationnum, action, and fields specified in the export options. +Action 'package' also sends pkgnum and change_pkgnum (the previous pkgnum, +because location changes usually instigate a pkgnum change.) +Simple field values can be selected in 'Include fields', and more complex +values can be specified in the data field options as perl code using vars +$cust_location, $cust_main and (where relevant) $cust_pkg. +Action 'location' only sends on update if a specified field changed. +Note that scheduled future package changes are currently sent when the change is scheduled +(this may not be the case in future versions of this export.) +For HTTPS support, Crypt::SSLeay +or IO::Socket::SSL is required. +END +); + +# we don't do anything on deletion because we generally don't delete locations +# +# we don't send blank custnum/prospectnum because we do a lot of inserting/replacing +# with blank values and then immediately overwriting, but that unfortunately +# makes it difficult to indicate if this is the first time we've sent the location +# to the customer--hence we don't distinguish insert from update in the cgi vars + +# gets invoked by FS::part_export::http _export_insert +sub _export_command { + my( $self, $action, $cust_location ) = @_; + + # redundant--cust_location exports don't get invoked by cust_location->delete, + # or by any status trigger, but just to be clear, since http export has other actions... + return '' unless $action eq 'insert'; + + $self->_http_queue_standard( + 'action' => 'location', + (map { $_ => $cust_location->get($_) } ('locationnum', $self->_include_fields)), + $self->_eval_replace('location_data',$cust_location,$cust_location->cust_main), + ); + +} + +sub _export_replace { + my( $self, $new, $old ) = @_; + + my $changed = 0; + + # even if they don't want custnum/prospectnum exported, + # inserts that lack custnum/prospectnum don't trigger exports, + # so we might not have previously reported these + $changed = 1 if $new->custnum && !$old->custnum; + $changed = 1 if $new->prospectnum && !$old->prospectnum; + + foreach my $field ($self->_include_fields) { + last if $changed; + next if $new->get($field) eq $old->get($field); + next if ($field =~ /latitude|longitude/) and $new->get($field) == $old->get($field); + $changed = 1; + } + + my %old_eval; + unless ($changed) { + %old_eval = $self->_eval_replace('location_data', $old, $old->cust_main), + } + + my %eval = $self->_eval_replace('location_data', $new, $new->cust_main); + + foreach my $key (keys %eval) { + last if $changed; + next if $eval{$key} eq $old_eval{$key}; + $changed = 1; + } + + return '' unless $changed; + + $self->_http_queue_standard( + 'action' => 'location', + (map { $_ => $new->get($_) } ('locationnum', $self->_include_fields)), + %eval, + ); +} + +# not to be confused with export_pkg_change, which is for svcs +sub export_pkg_location { + my ($self, $cust_pkg) = @_; + + return '' unless $cust_pkg->locationnum; + + my $cust_location = $cust_pkg->cust_location; + + $self->_http_queue_standard( + 'action' => 'package', + (map { $_ => $cust_pkg->get($_) } ('pkgnum', 'change_pkgnum', 'locationnum')), + (map { $_ => $cust_location->get($_) } $self->_include_fields), + $self->_eval_replace('package_data',$cust_location,$cust_pkg->cust_main,$cust_pkg), + ); +} + +sub _http_queue_standard { + my $self = shift; + my %opts = @_; + my $url; + if ($opts{'action'} eq 'location') { + $url = $self->option('location_url'); + return '' unless $url; + } elsif ($opts{'action'} eq 'package') { + $url = $self->option('package_url'); + return '' unless $url; + } else { + return "Bad action ".$opts{'action'}; + } + $self->http_queue( '', + ( $self->option('ssl_no_verify') ? 'ssl_no_verify' : '' ), + $self->option('method'), + $url, + $self->option('success_regexp'), + %opts + ); +} + +sub _include_fields { + my $self = shift; + split( /\s+/, $self->option('include_fields') ); +} + +sub _eval_replace { + my ($self,$option,$cust_location,$cust_main,$cust_pkg) = @_; + return + map { + /^\s*(\S+)\s+(.*)$/ or /()()/; + my( $field, $value_expression ) = ( $1, $2 ); + my $value = eval $value_expression; + die $@ if $@; + ( $field, $value ); + } split(/\n/, $self->option($option) ); +} + +1; diff --git a/FS/FS/part_export/portaone.pm b/FS/FS/part_export/portaone.pm index 8d5e19e8a..986a556ba 100644 --- a/FS/FS/part_export/portaone.pm +++ b/FS/FS/part_export/portaone.pm @@ -40,7 +40,7 @@ tie my %options, 'Tie::IxHash', 'customer_name' => { label => 'Customer Name', default => 'FREESIDE CUST $custnum' }, 'account_id' => { label => 'Account ID', - default => 'FREESIDE SVC $svcnum' }, + default => 'SVC$svcnum' }, 'product_id' => { label => 'Account Product ID' }, 'debug' => { type => 'checkbox', label => 'Enable debug warnings' }, diff --git a/FS/FS/part_referral.pm b/FS/FS/part_referral.pm index e4a582374..2df8a7571 100644 --- a/FS/FS/part_referral.pm +++ b/FS/FS/part_referral.pm @@ -44,6 +44,9 @@ The following fields are currently supported: =item agentnum - Optional agentnum (see L) +=item title - an optional external string that identifies this +referral source, such as an advertising campaign code. + =back =head1 NOTE @@ -101,6 +104,7 @@ sub check { || $self->ut_text('referral') || $self->ut_enum('disabled', [ '', 'Y' ] ) #|| $self->ut_foreign_keyn('agentnum', 'agent', 'agentnum') + || $self->ut_textn('title') || ( $setup_hack ? $self->ut_foreign_keyn('agentnum', 'agent', 'agentnum' ) : $self->ut_agentnum_acl('agentnum', 'Edit global advertising sources') diff --git a/FS/FS/part_svc.pm b/FS/FS/part_svc.pm index 612c59013..621a55410 100644 --- a/FS/FS/part_svc.pm +++ b/FS/FS/part_svc.pm @@ -590,6 +590,26 @@ sub num_cust_svc { $sth->fetchrow_arrayref->[0]; } +=item num_cust_svc_cancelled + +Returns the number of associated customer services that are +attached to cancelled packages. + +=cut + +sub num_cust_svc_cancelled { + my $self = shift; + my $sth = dbh->prepare( + "SELECT COUNT(*) FROM cust_svc + LEFT JOIN cust_pkg USING ( pkgnum ) + WHERE svcpart = ? + AND cust_pkg.cancel IS NOT NULL" + ) or die dbh->errstr; + $sth->execute($self->svcpart) + or die $sth->errstr; + $sth->fetchrow_arrayref->[0]; +} + =item svc_x Returns a list of associated FS::svc_* records. diff --git a/FS/FS/reason_Mixin.pm b/FS/FS/reason_Mixin.pm index 9c436ab1e..a1b32f2b5 100644 --- a/FS/FS/reason_Mixin.pm +++ b/FS/FS/reason_Mixin.pm @@ -22,13 +22,8 @@ voided payment / voided invoice. This can no longer be used to set the sub reason { my $self = shift; - my $reason_text; - if ( $self->reasonnum ) { - my $reason = FS::reason->by_key($self->reasonnum); - $reason_text = $reason->reason; - } else { # in case one of these somehow still exists - $reason_text = $self->get('reason'); - } + my $reason_text = $self->reason_only; + if ( $self->get('addlinfo') ) { $reason_text .= ' ' . $self->get('addlinfo'); } @@ -36,6 +31,28 @@ sub reason { return $reason_text; } +=item reason_only + +Returns only the text of the associated reason, +absent any addlinfo that is included by L. +(Currently only affects credit and credit void reasons.) + +=cut + +# a bit awkward, but much easier to invoke this in the few reports +# that need separate fields than to update every place +# that displays them together + +sub reason_only { + my $self = shift; + if ( $self->reasonnum ) { + my $reason = FS::reason->by_key($self->reasonnum); + return $reason->reason; + } else { # in case one of these somehow still exists + return $self->get('reason'); + } +} + # Used by FS::Upgrade to migrate reason text fields to reasonnum. # Note that any new tables that get reasonnum fields do NOT need to be # added here unless they have previously had a free-text "reason" field. diff --git a/FS/FS/svc_Common.pm b/FS/FS/svc_Common.pm index 748bcae12..1dd9ffb63 100644 --- a/FS/FS/svc_Common.pm +++ b/FS/FS/svc_Common.pm @@ -1387,11 +1387,17 @@ Parameters: =item order_by +=item cancelled - if true, only returns svcs attached to cancelled pkgs; +if defined and false, only returns svcs not attached to cancelled packages + =back =cut -# svc_broadband::search should eventually use this instead +### Don't call the 'cancelled' option 'Service Status' +### There is no such thing +### See cautionary note in httemplate/browse/part_svc.cgi + sub search { my ($class, $params) = @_; @@ -1495,6 +1501,14 @@ sub search { push @where, "exportnum = $1"; } + if ( defined($params->{'cancelled'}) ) { + if ($params->{'cancelled'}) { + push @where, "cust_pkg.cancel IS NOT NULL"; + } else { + push @where, "cust_pkg.cancel IS NULL"; + } + } + # # sector and tower # my @where_sector = $class->tower_sector_sql($params); # if ( @where_sector ) { diff --git a/FS/MANIFEST b/FS/MANIFEST index d0bf99b85..83359f118 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -56,6 +56,8 @@ FS/Report.pm FS/Report/FCC_477.pm FS/Report/Table.pm FS/Report/Table/Monthly.pm +FS/Report/Tax/All.pm +FS/Report/Tax/ByName.pm FS/SearchCache.pm FS/UI/Web.pm FS/UID.pm diff --git a/FS/bin/freeside-cdr-evariste-import b/FS/bin/freeside-cdr-evariste-import index 0487ae539..d5e13f98c 100755 --- a/FS/bin/freeside-cdr-evariste-import +++ b/FS/bin/freeside-cdr-evariste-import @@ -100,7 +100,7 @@ while (my $row = $csth->fetchrow_hashref) { 'cdrbatchnum' => $cdr_batch->cdrbatchnum, 'uniqueid' => $row->{'id'}, 'src' => $row->{'src'}, - 'dst' => $row->{'dest'}, + 'dst' => $row->{'routing_target'} || $row->{'dest'}, # dest_orig? dest_trans? 'startdate' => int(str2time($row->{'start_time'})), 'answerdate' => int(str2time($row->{'answer_time'})), 'enddate' => int(str2time($row->{'end_time'})), diff --git a/conf/invoice_latex b/conf/invoice_latex index b64cc690b..2a0f882c1 100644 --- a/conf/invoice_latex +++ b/conf/invoice_latex @@ -66,7 +66,7 @@ } } -\newcommand{\extracouponspace}{[@-- defined($extracouponspace) ? $extracouponspace : '3.6cm' --@]} +\newcommand{\extracouponspace}{[@-- defined($extracouponspace) ? $extracouponspace : '2.7in' --@]} % Adjust the inset of the mailing address \newcommand{\addressinset}[1][]{\hspace{1.0cm}} @@ -95,7 +95,9 @@ $OUT .= '\vspace{-\extracouponspace}'; $OUT .= '\rule[0.5em]{\textwidth}{\footrulewidth}\\\\'; $OUT .= $coupon; - $OUT .= '\vspace{'. $couponfootsep. '}' if defined($couponfootsep); + $OUT .= '\vspace{'. + (defined($couponfootsep) ? $couponfootsep : '0.2in') . + '}'; } ''; --@] [@-- $smallerfooter ? '\scriptsize{' : '\small{' --@] @@ -209,7 +211,7 @@ \hline } -}% ...description... +% ...description... \newcommand{\FSdesc}[5]{ \multicolumn{1}{c}{\rule{0pt}{2.5ex}\textbf{#1}} & \multicolumn{[@-- $unitprices ? '4' : '6' --@]}{l}{\textbf{#2}} & diff --git a/conf/invoice_latexcoupon b/conf/invoice_latexcoupon index a0338bd9a..238022680 100644 --- a/conf/invoice_latexcoupon +++ b/conf/invoice_latexcoupon @@ -24,7 +24,7 @@ Customer\#& \textbf{[@-- $custnum --@]} & \\ Total Due & \textbf{[@-- $balance --@]} & \\ \rule{0pt}{[@-- defined($amountenclosedsep) ? $amountenclosedsep : '2.25em' --@]}Amount Enclosed & \rule{2cm}{1pt}& \\ \end{tabular}\\ -\rule{0pt}{[@-- defined($coupontoaddresssep) ? $coupontoaddresssep : '1cm' --@]} &\\ +\rule{0pt}{[@-- defined($coupontoaddresssep) ? $coupontoaddresssep : '1in' --@]} &\\ \end{tabular}\\ \begin{tabular}{ll} \addressinset \rule{0.5cm}{0cm} & diff --git a/fs_selfservice/FS-SelfService/SelfService.pm b/fs_selfservice/FS-SelfService/SelfService.pm index e90210028..49d6f3109 100644 --- a/fs_selfservice/FS-SelfService/SelfService.pm +++ b/fs_selfservice/FS-SelfService/SelfService.pm @@ -112,6 +112,7 @@ $socket .= '.'.$tag if defined $tag && length($tag); 'new_customer' => 'Signup/new_customer', 'new_customer_minimal' => 'Signup/new_customer_minimal', 'capture_payment' => 'Signup/capture_payment', + 'new_prospect' => 'Signup/new_prospect', #N/A 'clear_signup_cache' => 'Signup/clear_cache', 'new_agent' => 'Agent/new_agent', 'agent_login' => 'Agent/agent_login', diff --git a/httemplate/browse/part_svc.cgi b/httemplate/browse/part_svc.cgi index 88f8d8d19..dee439466 100755 --- a/httemplate/browse/part_svc.cgi +++ b/httemplate/browse/part_svc.cgi @@ -119,9 +119,13 @@ function part_export_areyousure(href) { <% $svcdb %> CLASS="grid" BGCOLOR="<% $bgcolor %>"> - <% $num_active_cust_svc{$part_svc->svcpart} %> <% $num_active_cust_svc{$part_svc->svcpart} ? svc_url( 'ahref' => 1, 'm' => $m, 'action' => 'search', 'part_svc' => $part_svc, 'query' => "svcpart=". $part_svc->svcpart ) : '' %>active - -% if ( $num_active_cust_svc{$part_svc->svcpart} ) { +% my $svcurl_active = svc_url( 'ahref' => 1, 'm' => $m, 'action' => 'search', 'part_svc' => $part_svc, 'query' => "svcpart=". $part_svc->svcpart . "&cancelled=0"); +% my $svcurl_cancel = svc_url( 'ahref' => 1, 'm' => $m, 'action' => 'search', 'part_svc' => $part_svc, 'query' => "svcpart=". $part_svc->svcpart . "&cancelled=1"); + <% $num_cust_svc_active{$part_svc->svcpart} %> <% $num_cust_svc_active{$part_svc->svcpart} ? $svcurl_active : '' %>active<% $num_cust_svc_active{$part_svc->svcpart} ? '' : '' %> +% if ( $num_cust_svc_cancelled{$part_svc->svcpart} ) { +
<% $num_cust_svc_cancelled{$part_svc->svcpart} %> <% $svcurl_cancel %>cancelled +% } +% if ( $num_cust_svc{$part_svc->svcpart} ) {
[ change ] % } @@ -245,11 +249,25 @@ my @part_svc = qsearch('part_svc', \%search ); my $total = scalar(@part_svc); -my %num_active_cust_svc = map { $_->svcpart => $_->num_cust_svc } @part_svc; +## The Active/Cancelled distinction is a bit awkward, +## active currently includes unattached and suspended services, +## but we've previously referred to EVERY existing cust_svc as "Active", +## and we don't really want to know numbers by individual package status, +## so for now the UI will distinguish these as "Active" and "Cancelled", +## but please let's not go so far as to introduce the idea of "Service Status" + +my %num_cust_svc_active; +my %num_cust_svc_cancelled; +my %num_cust_svc; +foreach my $part_svc (@part_svc) { + $num_cust_svc{$part_svc->svcpart} = $part_svc->num_cust_svc; + $num_cust_svc_cancelled{$part_svc->svcpart} = $part_svc->num_cust_svc_cancelled; + $num_cust_svc_active{$part_svc->svcpart} = $num_cust_svc{$part_svc->svcpart} - $num_cust_svc_cancelled{$part_svc->svcpart}; +} if ( $cgi->param('orderby') eq 'active' ) { - @part_svc = sort { $num_active_cust_svc{$b->svcpart} <=> - $num_active_cust_svc{$a->svcpart} } @part_svc; + @part_svc = sort { $num_cust_svc{$b->svcpart} <=> + $num_cust_svc{$a->svcpart} } @part_svc; } elsif ( $cgi->param('orderby') eq 'svc' ) { @part_svc = sort { lc($a->svc) cmp lc($b->svc) } @part_svc; } diff --git a/httemplate/browse/tower.html b/httemplate/browse/tower.html index e2f9fd0bd..c8812e57b 100644 --- a/httemplate/browse/tower.html +++ b/httemplate/browse/tower.html @@ -1,22 +1,23 @@ -<% include( 'elements/browse.html', - 'title' => 'Towers', - 'name' => 'towers', - 'menubar' => [ 'Add a new tower' => - $p.'edit/tower.html', - ], - 'query' => { 'table' => 'tower', }, - 'count_query' => 'SELECT COUNT(*) FROM tower', - 'disableable' => 1, - 'disabled_statuspos' => 1, - 'header' => [ 'Name', 'Location', 'Sectors', ], - 'fields' => [ $tower_sub, - $coord_sub, - $sector_sub, - ], - 'links' => [ ], - 'cell_style' => [ $tagdesc_style ], - ) -%> +<& elements/browse.html, + 'title' => 'Towers', + 'name' => 'towers', + 'menubar' => [ 'Add a new tower' => + $p.'edit/tower.html', + 'Download CSV for towercoverage.com' => + $p.'misc/tower-export.html?format=tc' + ], + 'query' => { 'table' => 'tower', }, + 'count_query' => 'SELECT COUNT(*) FROM tower', + 'disableable' => 1, + 'disabled_statuspos' => 1, + 'header' => [ 'Name', 'Location', 'Sectors', ], + 'fields' => [ $tower_sub, + $coord_sub, + $sector_sub, + ], + 'links' => [ ], + 'cell_style' => [ $tagdesc_style ], +&> <%init> die "access denied" diff --git a/httemplate/edit/cust_refund.cgi b/httemplate/edit/cust_refund.cgi index bfcbfe725..32da4543e 100755 --- a/httemplate/edit/cust_refund.cgi +++ b/httemplate/edit/cust_refund.cgi @@ -10,7 +10,7 @@ <% small_custview($custnum, $conf->config('countrydefault')) %> % } -
+ @@ -116,7 +116,7 @@
- +
diff --git a/httemplate/edit/part_referral.html b/httemplate/edit/part_referral.html index e9fd79452..04287d632 100755 --- a/httemplate/edit/part_referral.html +++ b/httemplate/edit/part_referral.html @@ -3,11 +3,13 @@ 'table' => 'part_referral', 'fields' => [ 'referral', { field=>'agentnum', type=>'select-agent', }, + 'title', { field=>'disabled', type=>'checkbox', value=>'Y' } , ], 'labels' => { 'refnum' => 'Ad Source', 'referral' => 'Advertising source', 'agentnum' => 'Agent', + 'title' => 'External ID', 'disabled' => 'Disabled', }, 'viewall_dir' => 'browse', diff --git a/httemplate/edit/tower.html b/httemplate/edit/tower.html index fa3838dcf..4d8ad1e89 100644 --- a/httemplate/edit/tower.html +++ b/httemplate/edit/tower.html @@ -26,9 +26,9 @@ 'default_ip_addr' => 'Tower IP address', 'latitude' => 'Latitude', 'longitude' => 'Longitude', - 'altitude' => 'Altitude', - 'height' => 'Height', - 'veg_height' => 'Vegetation height', + 'altitude' => 'Altitude (feet)', + 'height' => 'Height (feet)', + 'veg_height' => 'Vegetation height (feet)', 'color' => 'Color', }, &> diff --git a/httemplate/elements/tr-cust_svc_cancel.html b/httemplate/elements/tr-cust_svc_cancel.html index 44276ec82..52dedd6c8 100644 --- a/httemplate/elements/tr-cust_svc_cancel.html +++ b/httemplate/elements/tr-cust_svc_cancel.html @@ -18,7 +18,14 @@ for use in view/cust_main. % } -%# no action links, the service is canceled +%# no action links except unprovision, the service is canceled +% if ( $curuser->access_right('Unprovision customer service') && ! $opt{no_links} ) { + + + ( <% $svc_unprovision_link %> ) + + +% } <%init> my %opt = @_; @@ -29,4 +36,9 @@ my $part_svc = $opt{'part_svc'} || $cust_svc->part_svc; my $cust_pkg = $opt{'cust_pkg'} || $cust_svc->cust_pkg; my $svc_x = $cust_svc->svc_x; +my $svc_unprovision_link = + qq!!.emt('Unprovision').''; + diff --git a/httemplate/misc/tax-fetch_and_import.cgi b/httemplate/misc/tax-fetch_and_import.cgi index 33a6c9b01..970d47c32 100644 --- a/httemplate/misc/tax-fetch_and_import.cgi +++ b/httemplate/misc/tax-fetch_and_import.cgi @@ -29,8 +29,9 @@ Import a tax data update. diff --git a/httemplate/misc/tax-fetch_and_replace.cgi b/httemplate/misc/tax-fetch_and_replace.cgi index 3290a3c44..ff64e6320 100644 --- a/httemplate/misc/tax-fetch_and_replace.cgi +++ b/httemplate/misc/tax-fetch_and_replace.cgi @@ -29,8 +29,9 @@ Replace tax data. diff --git a/httemplate/misc/tower-export.html b/httemplate/misc/tower-export.html new file mode 100644 index 000000000..9d63640f0 --- /dev/null +++ b/httemplate/misc/tower-export.html @@ -0,0 +1,36 @@ +<%init> +# currently, browse/tower just shows all towers, so do the same here +my @towers = qsearch({ table => 'tower' }); +http_header('Content-Type' => 'text/csv'); +http_header('Content-Disposition' => 'attachment;filename=towers.csv'); +if ( $cgi->param('format') eq 'tc' ) { + # towercoverage.com format: not a true CSV, no quoting (so no way to include + # commas in any field, so we strip them) + + # lat/long are signed decimals, northeast positive + # height is in meters + # Description/Group are not necessary + # sector/antenna information (orientation, beamwidth, gain, frequency, + # etc.) is in what TC calls a "Coverage", which can't be edited this way. + my $text = "SiteName,Latitude,Longitude,Description,Group,Height\n"; + + foreach my $tower (@towers) { + next if ( !$tower->latitude or !$tower->longitude ); + + my $name = $tower->towername; + my $height = ($tower->height || 0) / 3.28; + $name =~ s(,)( )g; + $text .= join(',', + $name, + $tower->latitude, + $tower->longitude, + '', + '', + $height, + ) . "\n"; + } + $m->print($text); +} else { + die('unknown format '.$cgi->param('format')); +} + diff --git a/httemplate/misc/xmlhttp-ticket-update.html b/httemplate/misc/xmlhttp-ticket-update.html index e81e353c5..01fb1b44d 100644 --- a/httemplate/misc/xmlhttp-ticket-update.html +++ b/httemplate/misc/xmlhttp-ticket-update.html @@ -13,7 +13,13 @@ my $ticket = FS::TicketSystem->get_ticket_object( \%session, ticket_id=>$id ); my $return; if ( $ticket ) { - my($orv, $omsg) = $ticket->SetOwner( $username ); + my $curowner = $ticket->OwnerObj->Name; + my($orv, $omsg); + if (( $curowner eq $FS::CurrentUser::CurrentUser->username ) or ( $curowner eq 'nobody' )) { + ($orv, $omsg) = $ticket->SetOwner( $username ); + } else { + ($orv, $omsg) = $ticket->SetOwner( $username, 'Steal' ); + } $orv = 1 if ! $orv && $omsg =~ /already own/i; if ( $orv ) { @@ -40,15 +46,18 @@ if ( $ticket ) { my %hash = $m->comp('/rt/Ticket/Elements/Customers', Ticket => $ticket); my @cust_main = values( %{$hash{cust_main}} ); + my $timelabel = FS::sched_avail::pretty_time($sh*60+$sm). '-'. + FS::sched_avail::pretty_time($eh*60+$em); + my $titlelabel = encode_entities($cust_main[0]->_FreesideURILabel); + $return = { 'error' => '', #'starts' => $starts, #'due' => $due, #'username' => $username, #false laziness w/CalendarSlotSchedule - 'sched_label' => - FS::sched_avail::pretty_time($sh*60+$sm). '-'. - FS::sched_avail::pretty_time($eh*60+$em). ': '. - encode_entities($cust_main[0]->_FreesideURILabel), + 'sched_label' => $timelabel . ': ' . $titlelabel, + 'sched_label_time' => $timelabel, + 'sched_label_title' => $titlelabel, }; } else { $return = { 'error' => $smsg }; diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index 2241f02e3..dbf0ff333 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -56,24 +56,29 @@ if ($unapplied) { push @header, emt('Date'), emt('By'), emt('Reason'), + emt('Info'), ; push @fields, sub { time2str('%b %d %Y', shift->_date ) }, 'otaker', - 'reason', + 'reason_only', + 'addlinfo', ; -push @sort_fields, '_date', 'otaker', 'reason'; -$align .= 'rll'; +push @sort_fields, '_date', 'otaker', 'reasonnum', 'addlinfo'; +$align .= 'rlll'; push @links, '', '', '', + '', ; push @color, '', '', '', + '', ; push @style, '', '', '', + '', ; # insert customer email after 'Reason' if this is a commission report diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html index b9bbc4dbb..0cdd8defd 100644 --- a/httemplate/search/cust_credit_bill_pkg.html +++ b/httemplate/search/cust_credit_bill_pkg.html @@ -14,6 +14,7 @@ 'Date', 'By', 'Reason', + 'Info', # line item 'Description', @@ -33,7 +34,8 @@ sub { time2str('%b %d %Y', shift->get('cust_credit_date') ) }, sub { shift->cust_credit_bill->cust_credit->otaker }, - sub { shift->cust_credit_bill->cust_credit->reason }, + sub { shift->cust_credit_bill->cust_credit->reason_only }, + sub { shift->cust_credit_bill->cust_credit->addlinfo }, sub { $_[0]->pkgnum > 0 ? $_[0]->get('pkg') # possibly use override.pkg @@ -51,6 +53,7 @@ 'cust_credit_date', '', #'otaker', '', #reason + '', #addlinfo '', #line item description '', #location @post_desc_null, @@ -66,6 +69,7 @@ '', '', '', + '', @post_desc_null, $ilink, $ilink, @@ -73,7 +77,7 @@ FS::UI::Web::cust_header() ), ], - 'align' => 'rrrllll'. + 'align' => 'rrrlllll'. $post_desc_align. 'rr'. FS::UI::Web::cust_aligns(), @@ -85,6 +89,7 @@ '', '', '', + '', @post_desc_null, '', '', @@ -98,6 +103,7 @@ '', '', '', + '', @post_desc_null, '', '', diff --git a/httemplate/search/cust_credit_source_bill_pkg.html b/httemplate/search/cust_credit_source_bill_pkg.html index 3ef88bdf9..1d5f8d2a0 100644 --- a/httemplate/search/cust_credit_source_bill_pkg.html +++ b/httemplate/search/cust_credit_source_bill_pkg.html @@ -28,7 +28,7 @@ sub { time2str('%b %d %Y', shift->get('cust_credit_date') ) }, sub { shift->cust_credit->otaker }, - sub { shift->cust_credit->reason }, + sub { shift->cust_credit->reason }, # split into reason_only/addlinfo if addlinfo ever gets used here sub { $_[0]->pkgnum > 0 ? $_[0]->get('pkg') # possibly use override.pkg diff --git a/httemplate/search/cust_credit_void.html b/httemplate/search/cust_credit_void.html index 18731d144..8a8b4133e 100755 --- a/httemplate/search/cust_credit_void.html +++ b/httemplate/search/cust_credit_void.html @@ -47,6 +47,7 @@ push @header, emt('Void Date'), emt('Date'), emt('By'), emt('Reason'), + emt('Info'), FS::UI::Web::cust_header(), ; push @fields, sub { time2str('%b %d %Y', shift->void_date ) }, @@ -54,7 +55,8 @@ push @fields, sub { time2str('%b %d %Y', shift->void_date ) }, 'void_reason', sub { time2str('%b %d %Y', shift->_date ) }, 'otaker', - 'reason', + 'reason_only', + 'addlinfo', \&FS::UI::Web::cust_fields, ; push @sort_fields, 'void_date', @@ -63,14 +65,16 @@ push @sort_fields, 'void_date', '_date', 'usernum', #ditto 'reasonnum, reason', #ditto + 'addlinfo', FS::UI::Web::cust_sort_fields(); -$align .= 'rllrll'.FS::UI::Web::cust_aligns(); +$align .= 'rllrlll'.FS::UI::Web::cust_aligns(); push @links, '', '', '', '', '', '', + '', ( map { $_ ne 'Cust. Status' ? $clink : '' } FS::UI::Web::cust_header() ), @@ -81,6 +85,7 @@ push @color, '', '', '', '', + '', FS::UI::Web::cust_colors(), ; push @style, '', @@ -89,6 +94,7 @@ push @style, '', '', '', '', + '', FS::UI::Web::cust_styles(), ; diff --git a/httemplate/search/cust_event.html b/httemplate/search/cust_event.html index 757982b95..241a0267a 100644 --- a/httemplate/search/cust_event.html +++ b/httemplate/search/cust_event.html @@ -152,7 +152,12 @@ die "access denied" || $cgi->param('pkgnum') =~ /^(\d+)$/ ); -my $title = $cgi->param('failed') ? 'Failed billing events' : 'Billing events'; +my @statuses = $cgi->multi_param('event_status'); +my $title = 'Billing events'; +if ( $statuses[0] eq 'failed' and !defined($statuses[1]) ) { + # tweak the title if we're showing only failed events + $title = 'Failed billing events'; +} my %search = (); @@ -161,6 +166,7 @@ for my $param (@scalars) { $search{$param} = scalar( $cgi->param($param) ) if $cgi->param($param); } +$search{event_status} = \@statuses; #lists my @lists = qw( eventpart ); diff --git a/httemplate/search/cust_svc.html b/httemplate/search/cust_svc.html index 3b770432e..7000e3048 100644 --- a/httemplate/search/cust_svc.html +++ b/httemplate/search/cust_svc.html @@ -1,50 +1,63 @@ <& elements/search.html, 'title' => emt('Service search results'), - 'name' => emt('services'), - 'query' => $sql_query, - 'count_query' => $count_query, - 'redirect' => $link, - 'header' => [ emt('#'), - emt('Service'), - # package? - FS::UI::Web::cust_header(), - ], - 'fields' => [ 'svcnum', - sub { - #$_[0]->svc. ': '. $_[0]->label; - my($label, $value, $svcdb) = $_[0]->label; - my $id = $_[0]->agent_svcid - ? $_[0]->agent_svcid.': ' - : ''; - "$label: $id$value"; - }, - # package? - \&FS::UI::Web::cust_fields, - ], - 'links' => [ $link, - $link, - # package? - ( 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(), - ], + 'name' => emt('services'), + 'query' => $sql_query, + 'count_query' => $count_query, + 'redirect' => $link, + 'header' => [ emt('#'), + emt('Service'), + emt('Pkg. Status'), + # package? + FS::UI::Web::cust_header(), + ], + 'fields' => [ 'svcnum', + sub { + #$_[0]->svc. ': '. $_[0]->label; + my($label, $value, $svcdb) = $_[0]->label; + my $id = $_[0]->agent_svcid + ? $_[0]->agent_svcid.': ' + : ''; + "$label: $id$value"; + }, + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, + # package? + \&FS::UI::Web::cust_fields, + ], + 'links' => [ $link, + $link, + '', # pkg status + # package? + ( map { $_ ne 'Cust. Status' ? $link_cust : '' } + FS::UI::Web::cust_header() + ), + ], + 'align' => 'rlr'. FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + 'b', # pkg status + FS::UI::Web::cust_styles(), + ], &> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my %cust_pkg_cache; + my $sql_query; my $orderby = 'ORDER BY cust_svc.svcnum'; #has to be ordered by something @@ -83,6 +96,13 @@ if ( length( $cgi->param('search_svc') ) ) { } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { push @extra_sql, "svcpart = $1"; + if (defined($cgi->param('cancelled'))) { + if ($cgi->param('cancelled')) { + push @extra_sql, "cust_pkg.cancel IS NOT NULL"; + } else { + push @extra_sql, "cust_pkg.cancel IS NULL"; + } + } } else { errorpage("No search term specified"); diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index 7d7fb73e9..b07f5e9d2 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -63,7 +63,7 @@ Examples: sub { sprintf($money, $_[0]->$amount_field) }, ], 'total_row' => [ 'Total', - sub { warn Dumper @_; sprintf("$money", $_[0]->$amount_field) }, + sub { sprintf("$money", $_[0]->$amount_field) }, ], 'show_combined' => 1, &> diff --git a/httemplate/search/report_cust_event.html b/httemplate/search/report_cust_event.html index 0dd98d479..6453500ad 100644 --- a/httemplate/search/report_cust_event.html +++ b/httemplate/search/report_cust_event.html @@ -5,7 +5,6 @@ %>
- @@ -15,7 +14,8 @@ <% include( '/elements/tr-select-agent.html', 'disable_empty'=>0 ) %> <% include( '/elements/tr-select-cust_main-status.html', - 'label' => 'Status' + 'label' => 'Customer status', + # this field is just called 'status' ) %> @@ -26,6 +26,34 @@ ) %> +% if ( $cgi->param('failed') ) { + <& /elements/tr-fixed.html, + 'label' => 'Event status', + 'field' => 'event_status', + 'curr_value' => 'failed', + 'formatted_value' => 'Failed', + &> +% } else { + +% # 'initial' is not on here, since nobody needs to see it. also, +% # 'done_Y' and 'done_N' are shorthand for "done, and no_action +% # is null" and "done, and no_action = 'Y'". + <& /elements/tr-select.html, + 'label' => 'Event status', + 'field' => 'event_status', + 'multiple' => 1, + 'all_selected' => 1, + 'size' => 5, + 'options' => [ qw( done_Y done_N failed new locked ) ], + 'option_labels' => { done_Y => 'Completed', + done_N => 'Completed, no action taken', + failed => 'Failed', + new => 'Not yet processed', + locked => 'Running', + }, + &> +% } + <% include( '/elements/tr-input-beginning_ending.html' ) %>
diff --git a/httemplate/search/report_tax-xls.cgi b/httemplate/search/report_tax-xls.cgi index c914d5adc..30b32e8d8 100755 --- a/httemplate/search/report_tax-xls.cgi +++ b/httemplate/search/report_tax-xls.cgi @@ -13,9 +13,7 @@ my %params = ( beginning => $beginning, ending => $ending, ); -$params{country} = $cgi->param('country'); $params{debug} = $DEBUG; -$params{breakdown} = { map { $_ => 1 } $cgi->param('breakdown') }; my $agentname; if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { @@ -24,15 +22,38 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $agentname = $agent->agentname; } -# allow anything in here; FS::Report::Tax will treat it as unsafe -if ( length($cgi->param('taxname')) ) { - $params{taxname} = $cgi->param('taxname'); +# credit date behavior: limit by the date of the credit application, or +# the invoice? +if ( $cgi->param('credit_date') eq 'cust_credit_bill' ) { + $params{credit_date} = 'cust_credit_bill'; } else { - die "taxname required"; + $params{credit_date} = 'cust_bill'; +} + +my $all = $cgi->param('all'); +my $report_class; + +if ( $all ) { + $report_class = 'FS::Report::Tax::All'; +} else { + $report_class = 'FS::Report::Tax::ByName'; + $params{country} = $cgi->param('country'); + $params{breakdown} = { map { $_ => 1 } $cgi->param('breakdown') }; + + # allow anything in here; FS::Report::Tax will treat it as unsafe + if ( length($cgi->param('taxname')) ) { + $params{taxname} = $cgi->param('taxname'); + } else { + die "taxname required"; + } +} + +if ($DEBUG) { + warn "REPORT: $report_class\nPARAMS:\n".Dumper(\%params)."\n\n"; } # generate the report -my $report = FS::Report::Tax->report_internal(%params); +my $report = $report_class->report(%params); my @rows = $report->table; # array of hashrefs my %pkgclass_name = map { $_->classnum, $_->classname } qsearch('pkg_class'); diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index bbb3bc199..410fe4603 100644 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -78,14 +78,6 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } % # cust_bill_pkg.cgi wants a list of specific taxnums (and package class) % # cust_credit_bill_pkg.html wants a geographic scope (and package class) % my $rowlink = ';taxnum=' . $row->{taxnums}; -% # DON'T EVER USE THIS -% # my $rowregion = ';country=' . $cgi->param('country'); -% # foreach my $loc (qw(state county city district)) { -% # if ( $row->{$loc} ) { -% # $rowregion .= ";$loc=" . uri_escape($row->{$loc}); -% # } -% # } -% # and also the package class, if we're limiting package class % if ( $params{breakdown}->{pkgclass} ) { % $rowlink .= ';classnum=' . ($row->{pkgclass} || 0); % # $rowregion .= ';classnum=' . ($row->{pkgclass} || 0); @@ -96,7 +88,26 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } % } % # Row label - <% $row->{label} |h %> +% # Special: If this report is showing all taxes, link the row label to +% # the detailed tax report for that taxname/country. + +% if ( $all ) { +% my $newcgi = CGI->new($cgi); +% $newcgi->delete('all'); +% $newcgi->param('country', $row->{country}); +% $newcgi->param('taxname', $row->{taxname}); +% $newcgi->param('breakdown', qw(city district)); + + + <% $row->{label} |h %> + + +% } else { # on the per-taxname report, just show the label with no link + + <% $row->{label} |h %> + +% } + % # Total sales @@ -167,7 +178,8 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } % } # foreach my $row % # at the end of everything -% if ( $report->{out_sales} > 0 ) { +% # the all-taxes report doesn't have "out of region" +% if ( !$all and $report->{out_sales} > 0 ) { @@ -175,7 +187,7 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } - <% $money_sprintf->( $report->{out_sales } ) %> + <% $money_sprintf->( $report->{out_sales} ) %> @@ -254,7 +266,7 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } % $prev_row = $row; % } # foreach my $row % # "out of taxable region" for credits (there is a need for it) -% if ( $report->{out_credit} > 0 ) { +% if ( !$all and $report->{out_credit} > 0 ) { % my $creditlink = "cust_credit_bill_pkg.html?out=1;$dateagentlink"; % if ( $params{'credit_date'} eq 'cust_credit_bill' ) { % $creditlink =~ s/begin/credit_begin/; @@ -268,7 +280,7 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } - <% $money_sprintf->( $report->{out_credit } ) %> + <% $money_sprintf->( $report->{out_credit} ) %> @@ -295,33 +307,48 @@ my %params = ( beginning => $beginning, ending => $ending, ); -$params{country} = $cgi->param('country'); $params{debug} = $DEBUG; -$params{breakdown} = { map { $_ => 1 } $cgi->param('breakdown') }; - my $agentname; + +# filter by agentnum if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { my $agent = FS::agent->by_key($1) or die "unknown agentnum $1"; $params{agentnum} = $1; $agentname = $agent->agentname; } -# allow anything in here; FS::Report::Tax will treat it as unsafe -if ( length($cgi->param('taxname')) ) { - $params{taxname} = $cgi->param('taxname'); -} else { - die "taxname required"; -} - +# credit date behavior: limit by the date of the credit application, or +# the invoice? if ( $cgi->param('credit_date') eq 'cust_credit_bill' ) { $params{credit_date} = 'cust_credit_bill'; } else { $params{credit_date} = 'cust_bill'; } -warn "PARAMS:\n".Dumper(\%params)."\n\n" if $DEBUG; +my $all = $cgi->param('all'); +my $report_class; + +if ( $all ) { + # then show the master report, no country, no taxname, no breakdown + $report_class = 'FS::Report::Tax::All'; +} else { + $report_class = 'FS::Report::Tax::ByName'; + $params{country} = $cgi->param('country'); + $params{breakdown} = { map { $_ => 1 } $cgi->param('breakdown') }; + + # allow anything in here; FS::Report::Tax will treat it as unsafe + if ( length($cgi->param('taxname')) ) { + $params{taxname} = $cgi->param('taxname'); + } else { + die "taxname required"; + } +} + +if ($DEBUG) { + warn "REPORT: $report_class\nPARAMS:\n".Dumper(\%params)."\n\n"; +} -my $report = FS::Report::Tax->report_internal(%params); +my $report = $report_class->report(%params); my @rows = $report->table; # array of hashrefs my $money_char = $conf->config('money_char') || '$'; diff --git a/httemplate/search/report_tax.html b/httemplate/search/report_tax.html index 8d8d1084c..f920adbac 100755 --- a/httemplate/search/report_tax.html +++ b/httemplate/search/report_tax.html @@ -8,6 +8,20 @@ <& /elements/tr-input-beginning_ending.html &> + + + + <& /elements/radio.html, + 'field' => 'all', + 'value' => 1, + 'curr_value' => 1, + &> All taxes + <& /elements/radio.html, + 'field' => 'all', + 'value' => 0, + &> A specific tax + + <& /elements/tr-select.html, 'label' => 'Country', 'field' => 'country', @@ -49,6 +63,21 @@
+ + <% include('/elements/footer.html') %> <%init> diff --git a/httemplate/search/report_unprovisioned_services.html b/httemplate/search/report_unprovisioned_services.html index fe4d46bf7..54181bb20 100755 --- a/httemplate/search/report_unprovisioned_services.html +++ b/httemplate/search/report_unprovisioned_services.html @@ -13,6 +13,7 @@ 'field' => 'svcpart', 'label' => 'Services', 'multiple' => 1, + 'size' => 20, ) %> diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index 58764f881..ef89f01c8 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -59,6 +59,8 @@ my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" unless $curuser->access_right('List services'); +my %cust_pkg_cache; + my $link = [ "${p}view/svc_acct.cgi?", 'svcnum' ]; my $link_cust = sub { my $svc_acct = shift; @@ -130,6 +132,7 @@ for (qw( towernum sectornum )) { my $timepermonth = ''; my $orderby = 'ORDER BY svcnum'; +my $addl_from = ''; if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { $search_hash{'unlinked'} = 1 @@ -281,6 +284,9 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } elsif ( $cgi->param('svcpart') ) { $orderby = "ORDER BY uid"; #$orderby = "ORDER BY svcnum"; + if ( defined($cgi->param('cancelled')) ) { + $search_hash{'cancelled'} = $cgi->param('cancelled') ? 1 : 0; + } } else { $orderby = "ORDER BY uid"; @@ -347,6 +353,19 @@ foreach my $pkg_field ( @pkg_fields ) { } +push @header, emt('Pkg. Status'); +push @fields, sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status; +}; +push @links, ''; +$align .= 'r'; +push @color, sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; +}; +push @style, 'b'; + push @header, FS::UI::Web::cust_header($cgi->param('cust_fields')); push @fields, \&FS::UI::Web::cust_fields, push @links, map { $_ ne 'Cust. Status' ? $link_cust : '' } @@ -357,6 +376,7 @@ push @style, FS::UI::Web::cust_styles(); $search_hash{'order_by'} = $orderby; $search_hash{'where'} = \@extra_sql; +$search_hash{'addl_from'} = $addl_from; my $sql_query = FS::svc_acct->search(\%search_hash); my $count_query = delete($sql_query->{'count_query'}); diff --git a/httemplate/search/svc_broadband.cgi b/httemplate/search/svc_broadband.cgi index 6bf4f0850..ff2538c93 100755 --- a/httemplate/search/svc_broadband.cgi +++ b/httemplate/search/svc_broadband.cgi @@ -10,6 +10,7 @@ 'Router', @tower_header, 'IP Address', + emt('Pkg. Status'), FS::UI::Web::cust_header($cgi->param('cust_fields')), ], 'fields' => [ 'svcnum', @@ -20,6 +21,10 @@ }, @tower_fields, 'ip_addr', + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, @@ -27,11 +32,12 @@ '', #$link_router, (map '', @tower_fields), $link, + '', # pkg status ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header($cgi->param('cust_fields')) ), ], - 'align' => 'rll'.('r' x @tower_fields).'r'. + 'align' => 'rll'.('r' x @tower_fields).'rr'. FS::UI::Web::cust_aligns(), 'color' => [ '', @@ -39,6 +45,10 @@ '', (map '', @tower_fields), '', + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status FS::UI::Web::cust_colors(), ], 'style' => [ @@ -47,6 +57,7 @@ '', (map '', @tower_fields), '', + 'b', FS::UI::Web::cust_styles(), ], @@ -56,6 +67,8 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my %cust_pkg_cache; + my $conf = new FS::Conf; my %search_hash; @@ -68,6 +81,9 @@ if ( $cgi->param('magic') eq 'unlinked' ) { foreach (qw(pkgpart routernum towernum sectornum)) { $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_); } + if ( defined($cgi->param('cancelled')) ) { + $search_hash{'cancelled'} = $cgi->param('cancelled') ? 1 : 0; + } } if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { diff --git a/httemplate/search/svc_circuit.cgi b/httemplate/search/svc_circuit.cgi index 8f05e0488..2174734ea 100644 --- a/httemplate/search/svc_circuit.cgi +++ b/httemplate/search/svc_circuit.cgi @@ -10,6 +10,7 @@ 'Termination', 'Circuit ID', 'IP Address', + emt('Pkg. Status'), FS::UI::Web::cust_header($cgi->param('cust_fields')), ], 'fields' => [ 'svcnum', @@ -18,6 +19,10 @@ 'termination', 'circuit_id', 'ip_addr', + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, @@ -26,15 +31,21 @@ '', $link, $link, + '', # pkg status FS::UI::Web::cust_links($cgi->param('cust_fields')), ], - 'align' => 'rlllll'. FS::UI::Web::cust_aligns(), + 'align' => 'rlllllr'. FS::UI::Web::cust_aligns(), 'color' => [ ('') x 6, + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status FS::UI::Web::cust_colors(), ], 'style' => [ ('') x 6, + 'b', FS::UI::Web::cust_styles(), ], @@ -44,6 +55,8 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my %cust_pkg_cache; + my $conf = new FS::Conf; my %search_hash; @@ -56,6 +69,9 @@ if ( $cgi->param('magic') eq 'unlinked' ) { foreach (qw(pkgpart routernum towernum sectornum)) { $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_); } + if ( defined($cgi->param('cancelled')) ) { + $search_hash{'cancelled'} = $cgi->param('cancelled') ? 1 : 0; + } } my $query = FS::svc_circuit->search(\%search_hash); diff --git a/httemplate/search/svc_dish.cgi b/httemplate/search/svc_dish.cgi index 1f8cbc395..1e7330804 100755 --- a/httemplate/search/svc_dish.cgi +++ b/httemplate/search/svc_dish.cgi @@ -7,31 +7,42 @@ 'header' => [ '#', 'Service', 'Account #', + emt('Pkg. Status'), FS::UI::Web::cust_header(), ], 'fields' => [ 'svcnum', 'svc', 'acctnum', + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, $link, $link, + '', # pkg status ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], - 'align' => 'rll'. FS::UI::Web::cust_aligns(), + 'align' => 'rllr'. FS::UI::Web::cust_aligns(), 'color' => [ '', '', '', + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status FS::UI::Web::cust_colors(), ], 'style' => [ '', '', '', + 'b', FS::UI::Web::cust_styles(), ], @@ -41,6 +52,8 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my %cust_pkg_cache; + #my $conf = new FS::Conf; my $orderby = 'ORDER BY svcnum'; @@ -56,6 +69,13 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { push @extra_sql, "svcpart = $1"; + if (defined($cgi->param('cancelled'))) { + if ($cgi->param('cancelled')) { + push @extra_sql, "cust_pkg.cancel IS NOT NULL"; + } else { + push @extra_sql, "cust_pkg.cancel IS NULL"; + } + } } my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi index 56cfa30c8..c8fca9fdc 100755 --- a/httemplate/search/svc_domain.cgi +++ b/httemplate/search/svc_domain.cgi @@ -7,31 +7,42 @@ 'header' => [ '#', 'Service', 'Domain', + emt('Pkg. Status'), FS::UI::Web::cust_header(), ], 'fields' => [ 'svcnum', 'svc', 'domain', + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, $link, $link, + '', # pkg status ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], - 'align' => 'rll'. FS::UI::Web::cust_aligns(), + 'align' => 'rllr'. FS::UI::Web::cust_aligns(), 'color' => [ '', '', '', + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status FS::UI::Web::cust_colors(), ], 'style' => [ '', '', '', + 'b', FS::UI::Web::cust_styles(), ], @@ -41,6 +52,8 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my %cust_pkg_cache; + my $conf = new FS::Conf; my $orderby = 'ORDER BY svcnum'; @@ -58,6 +71,13 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { push @extra_sql, "svcpart = $1"; + if (defined($cgi->param('cancelled'))) { + if ($cgi->param('cancelled')) { + push @extra_sql, "cust_pkg.cancel IS NOT NULL"; + } else { + push @extra_sql, "cust_pkg.cancel IS NULL"; + } + } } else { $cgi->param('domain') =~ /^([\w\-\.]+)$/; $svc_domain{'domain'} = $1; diff --git a/httemplate/search/svc_external.cgi b/httemplate/search/svc_external.cgi index b282939a7..5f9056165 100755 --- a/httemplate/search/svc_external.cgi +++ b/httemplate/search/svc_external.cgi @@ -8,29 +8,39 @@ 'Service', ( FS::Msgcat::_gettext('svc_external-id') || 'External ID' ), ( FS::Msgcat::_gettext('svc_external-title') || 'Title' ), + emt('Pkg. Status'), FS::UI::Web::cust_header(), ], 'fields' => [ 'svcnum', 'svc', 'id', 'title', + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, $link, $link, $link, + '', # pkg status ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], - 'align' => 'rlrr'. + 'align' => 'rlrrr'. FS::UI::Web::cust_aligns(), 'color' => [ '', '', '', '', + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status FS::UI::Web::cust_colors(), ], 'style' => [ @@ -38,6 +48,7 @@ '', '', '', + 'b', FS::UI::Web::cust_styles(), ], @@ -47,6 +58,8 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my %cust_pkg_cache; + my $conf = new FS::Conf; my %svc_external; @@ -69,6 +82,13 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { push @extra_sql, "svcpart = $1"; + if (defined($cgi->param('cancelled'))) { + if ($cgi->param('cancelled')) { + push @extra_sql, "cust_pkg.cancel IS NOT NULL"; + } else { + push @extra_sql, "cust_pkg.cancel IS NULL"; + } + } } elsif ( $cgi->param('title') =~ /^(.*)$/ ) { diff --git a/httemplate/search/svc_fiber.html b/httemplate/search/svc_fiber.html index 0cb735c96..3960a1635 100644 --- a/httemplate/search/svc_fiber.html +++ b/httemplate/search/svc_fiber.html @@ -10,6 +10,7 @@ 'ONT', 'Model', 'Serial', + emt('Pkg. Status'), FS::UI::Web::cust_header($cgi->param('cust_fields')), ], 'fields' => [ 'svcnum', @@ -20,6 +21,10 @@ 'ont_id', 'ont_description', 'ont_serial', + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, @@ -28,15 +33,21 @@ $link, $link, $link, + '', # pkg status FS::UI::Web::cust_links($cgi->param('cust_fields')), ], - 'align' => 'rlllll'. FS::UI::Web::cust_aligns(), + 'align' => 'rlllllr'. FS::UI::Web::cust_aligns(), 'color' => [ ('') x 6, + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status FS::UI::Web::cust_colors(), ], 'style' => [ ('') x 6, + 'b', FS::UI::Web::cust_styles(), ], @@ -48,6 +59,8 @@ die "access denied" unless 'List services' ]); +my %cust_pkg_cache; + my $conf = new FS::Conf; my %search_hash; @@ -58,6 +71,9 @@ if ( $cgi->param('magic') eq 'unlinked' ) { ont_typenum oltnum shelf olt_port card vlan )) { $search_hash{$_} = $cgi->param($_) if defined($cgi->param($_)); } + if ( defined($cgi->param('cancelled')) ) { + $search_hash{'cancelled'} = $cgi->param('cancelled') ? 1 : 0; + } } my $query = FS::svc_fiber->search(\%search_hash); diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi index 6a23bb3bb..ca2c28808 100755 --- a/httemplate/search/svc_forward.cgi +++ b/httemplate/search/svc_forward.cgi @@ -8,28 +8,38 @@ 'Service', 'Mail to', 'Forwards to', + emt('Pkg. Status'), FS::UI::Web::cust_header(), ], 'fields' => [ 'svcnum', 'svc', $format_src, $format_dst, + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, $link, $link_src, $link_dst, + '', # pkg status ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], - 'align' => 'rlll'. FS::UI::Web::cust_aligns(), + 'align' => 'rlllr'. FS::UI::Web::cust_aligns(), 'color' => [ '', '', '', '', + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status FS::UI::Web::cust_colors(), ], 'style' => [ @@ -37,6 +47,7 @@ '', '', '', + 'b', FS::UI::Web::cust_styles(), ], @@ -46,6 +57,8 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my %cust_pkg_cache; + my $conf = new FS::Conf; my $orderby = 'ORDER BY svcnum'; @@ -62,6 +75,13 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { push @extra_sql, "svcpart = $1"; + if (defined($cgi->param('cancelled'))) { + if ($cgi->param('cancelled')) { + push @extra_sql, "cust_pkg.cancel IS NOT NULL"; + } else { + push @extra_sql, "cust_pkg.cancel IS NULL"; + } + } } my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. diff --git a/httemplate/search/svc_hardware.cgi b/httemplate/search/svc_hardware.cgi index 93fc2c391..78f413e30 100644 --- a/httemplate/search/svc_hardware.cgi +++ b/httemplate/search/svc_hardware.cgi @@ -12,6 +12,7 @@ 'Hardware addr.', 'IP addr.', 'Smartcard', + emt('Pkg. Status'), FS::UI::Web::cust_header(), ], 'fields' => [ 'svcnum', @@ -22,24 +23,36 @@ 'display_hw_addr', 'ip_addr', 'smartcard', + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, \&FS::UI::Web::cust_fields, ], 'links' => [ ($link_svc) x 8, + '', # pkg status ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ) ], - 'align' => 'rlllllll' . FS::UI::Web::cust_aligns(), + 'align' => 'rlllllllr' . FS::UI::Web::cust_aligns(), 'color' => [ ('') x 8, - FS::UI::Web::cust_colors() ], + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status + FS::UI::Web::cust_colors() ], 'style' => [ $svc_cancel_style, ('') x 7, - FS::UI::Web::cust_styles() ], + 'b', + FS::UI::Web::cust_styles() ], &> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my %cust_pkg_cache; + my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) LEFT JOIN part_svc USING ( svcpart ) @@ -93,6 +106,13 @@ if ( $cgi->param('typenum') =~ /^(\d+)$/ ) { if ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { push @extra_sql, "cust_svc.svcpart = $1"; + if (defined($cgi->param('cancelled'))) { + if ($cgi->param('cancelled')) { + push @extra_sql, "cust_pkg.cancel IS NOT NULL"; + } else { + push @extra_sql, "cust_pkg.cancel IS NULL"; + } + } } my ($orderby) = $cgi->param('orderby') =~ /^(\w+( ASC| DESC)?)$/i; diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi index f3a056475..29e745690 100644 --- a/httemplate/search/svc_phone.cgi +++ b/httemplate/search/svc_phone.cgi @@ -9,6 +9,7 @@ 'Country code', 'Phone number', @header, + emt('Pkg. Status'), FS::UI::Web::cust_header($cgi->param('cust_fields')), ], 'fields' => [ 'svcnum', @@ -16,6 +17,10 @@ 'countrycode', 'phonenum', @fields, + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, @@ -23,12 +28,14 @@ $link, $link, ( map '', @header ), + '', # pkg status ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header($cgi->param('cust_fields')) ), ], 'align' => 'rlrr'. join('', map 'r', @header). + 'r'. FS::UI::Web::cust_aligns(), 'color' => [ '', @@ -36,6 +43,10 @@ '', '', ( map '', @header ), + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status FS::UI::Web::cust_colors(), ], 'style' => [ @@ -44,6 +55,7 @@ '', '', ( map '', @header ), + 'b', FS::UI::Web::cust_styles(), ], @@ -53,6 +65,8 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my %cust_pkg_cache; + my $conf = new FS::Conf; my @select = (); @@ -132,6 +146,9 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { $search_hash{'svcpart'} = [ $1 ]; + if ( defined($cgi->param('cancelled')) ) { + $search_hash{'cancelled'} = $cgi->param('cancelled') ? 1 : 0; + } } else { $cgi->param('phonenum') =~ /^([\d\- ]+)$/; my $phonenum = $1; diff --git a/httemplate/search/svc_www.cgi b/httemplate/search/svc_www.cgi index 7410262e8..4f6611f17 100755 --- a/httemplate/search/svc_www.cgi +++ b/httemplate/search/svc_www.cgi @@ -8,6 +8,7 @@ 'Service', 'Zone', 'User', + emt('Pkg. Status'), FS::UI::Web::cust_header(), ], 'fields' => [ 'svcnum', @@ -20,22 +21,31 @@ ? $svc_acct->email : ''; }, + sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status + }, \&FS::UI::Web::cust_fields, ], 'links' => [ $link, $link, '', $ulink, + '', # pkg status ( map { $_ ne 'Cust. Status' ? $link_cust : '' } FS::UI::Web::cust_header() ), ], - 'align' => 'rlll'. FS::UI::Web::cust_aligns(), + 'align' => 'rlllr'. FS::UI::Web::cust_aligns(), 'color' => [ '', '', '', '', + sub { + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; + }, # pkg status FS::UI::Web::cust_colors(), ], 'style' => [ @@ -43,6 +53,7 @@ '', '', '', + 'b', FS::UI::Web::cust_styles(), ], @@ -52,6 +63,8 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my %cust_pkg_cache; + #my $conf = new FS::Conf; my $orderby = 'ORDER BY svcnum'; @@ -68,6 +81,13 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { push @extra_sql, "svcpart = $1"; + if (defined($cgi->param('cancelled'))) { + if ($cgi->param('cancelled')) { + push @extra_sql, "cust_pkg.cancel IS NOT NULL"; + } else { + push @extra_sql, "cust_pkg.cancel IS NULL"; + } + } } my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. diff --git a/httemplate/search/tax_sales.cgi b/httemplate/search/tax_sales.cgi index 4b28c934a..91abd1bd3 100644 --- a/httemplate/search/tax_sales.cgi +++ b/httemplate/search/tax_sales.cgi @@ -113,7 +113,7 @@ while ($countdate < $enddate) { # run a report for each tax name foreach my $taxname (@taxnames) { $params{'taxname'} = $taxname; - my $report = FS::Report::Tax->report_internal(%params); + my $report = FS::Report::Tax::ByName->report(%params); # extract totals from report, kinda awkward my $pkgclass = ''; # this will get more complicated if we breakdown by pkgclass diff --git a/httemplate/view/elements/svc_edit_link.html b/httemplate/view/elements/svc_edit_link.html index 3ff2f58b6..2de5ecf9c 100644 --- a/httemplate/view/elements/svc_edit_link.html +++ b/httemplate/view/elements/svc_edit_link.html @@ -1,18 +1,18 @@ -% if ( $cancel_date ) { -<% mt("Canceled [_1]", time2str('%b %o %Y', $cancel_date) ) |h %> -% } else { +% if ( $cancel_date ) { +| <% mt("Canceled [_1]", time2str('%b %o %Y', $cancel_date) ) |h %> +% } else { % if ( $curuser->access_right('Provision customer service') ) { | <% mt("Edit this [_1]", $label) |h %> % } -% if ( $curuser->access_right('Unprovision customer service') ) { +% } +% if ( $curuser->access_right('Unprovision customer service') ) { | <% mt('Unprovision this Service') |h %> -% } % } <& /elements/manage_device_link.html, 'svc' => $svc_x, diff --git a/ng_selfservice/coverage_post.php b/ng_selfservice/coverage_post.php new file mode 100644 index 000000000..657a2ffb8 --- /dev/null +++ b/ng_selfservice/coverage_post.php @@ -0,0 +1,43 @@ +CustomerDetails; +if ($DEBUG) { + error_log(var_dump($cd)); +} + +// State and Country are names rather than codes, but we fix that on the other +// end. +// It doesn't look like TowerCoverage ever sends a company name. +$map_fields = Array( + 'first' => 'FirstName', + 'last' => 'LastName', + 'address1' => 'StreetAddress', + 'city' => 'City', + 'state' => 'State', + 'country' => 'Country', + 'zip' => 'ZIP', + 'phone_daytime' => 'PhoneNumber', + 'emailaddress' => 'EmailAddress', + 'comment' => 'Comment', + 'referral_title' => 'HearAbout', +); + +$prospect = Array(); +// missing from this: any way to set the agent. this should use the API key. +foreach ($map_fields as $k => $v) { + $prospect[$k] = (string)($cd->$v); +} +error_log(var_dump($prospect)); +$freeside = new FreesideSelfService(); +$result = $freeside->new_prospect($prospect); +error_log(var_dump($result)); + +?> diff --git a/rt/share/html/Elements/CalendarSlotSchedule b/rt/share/html/Elements/CalendarSlotSchedule index b5c08d6ac..f12b4a6be 100644 --- a/rt/share/html/Elements/CalendarSlotSchedule +++ b/rt/share/html/Elements/CalendarSlotSchedule @@ -21,7 +21,8 @@ % % my $bgcolor = '#666666'; % my $border = '1px solid #555555'; -% my $label = ''; +% my $label_time = ''; +% my $label_title = ''; % my $selectable = 0; # can we schedule a new appointment % my $ticketid = 0; % my $draggable_length = 0; @@ -72,10 +73,10 @@ % my @cust_main = values( %{$hash{cust_main}} ); % % #false laziness w/xmlhttp-ticket-update.html -% $label .= FS::sched_avail::pretty_time($starts). '-'. -% FS::sched_avail::pretty_time($due). -% ': '. $cust_main[0]->_FreesideURILabel; -% #'install for custname XX miles away'; #XXX placeholder/more +% $label_time = FS::sched_avail::pretty_time($starts). '-'. +% FS::sched_avail::pretty_time($due); +% $label_title = $cust_main[0]->_FreesideURILabel; +% #'install for custname XX miles away'; #XXX placeholder/more % % $offset = int( ($tod_row - $starts) / $timestep ); % $offset++ if ($tod_row - $starts) % $timestep; @@ -158,7 +159,8 @@ $cell_<% $td_id %>, <% $ticketid |js_string %>, <% $bgcolor |n,js_string %>, - <% $label |n,js_string %>, + <% $label_time |n,js_string %>, + <% $label_title |n,js_string %>, <% $draggable_length * 60 %>, <% $cells %>, <% $offset %> diff --git a/rt/share/html/Search/Schedule.html b/rt/share/html/Search/Schedule.html index 6a62c2722..df7b53d81 100644 --- a/rt/share/html/Search/Schedule.html +++ b/rt/share/html/Search/Schedule.html @@ -12,9 +12,10 @@ $cell.data('div',$div); $cell.append($div); } + $div.css('white-space','nowrap'); $div.css('width','100%'); $div.css('background-color', bgcolor); - $div.html(content || ' '); + $div.html(content || ' 
 
 '); } // gives cell the appearance dictated by its data @@ -37,12 +38,13 @@ } // sets cell data and appearance as an appointment - function set_appointment_cell ($cell,ticketid,bgcolor,label,length,cells,offset) { + function set_appointment_cell ($cell,ticketid,bgcolor,labeltime,labeltitle,length,cells,offset) { $cell.data('bgcolor', bgcolor ); $cell.data('ticketid', ticketid ); $cell.data('length', length ); $cell.data('cells', cells ); $cell.data('offset', offset ); + var label = labeltime + '
' + labeltitle + '
'; $cell.data('label', label ); $cell.data('content', ''); if ( offset == 0 ) { // first row @@ -295,6 +297,8 @@ } else { var label = data.sched_label; + var labeltime = data.sched_label_time; + var labeltitle = data.sched_label_title; // jump to first cell in appointment var rownum = dragcell.parent().prevAll('tr').length; @@ -325,7 +329,7 @@ '_' + String( n_tod_row ) + '_' + username; var $cell = $('#'+n_td_id); - set_appointment_cell($cell,ticketid,bgcolor,label,length,cells,d); + set_appointment_cell($cell,ticketid,bgcolor,labeltime,labeltitle,length,cells,d); set_draggable_cell($cell); set_droppable_cell($cell); }