From 7e2a4d3ae0e73311ebb4fe01869544c6047f59c8 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Wed, 4 Oct 2017 11:37:20 -0400 Subject: RT# 76968 - added header logo and css to map page, fixed address look up error, added leaflet to license --- httemplate/docs/license.html | 4 ++++ httemplate/misc/confirm-censustract.html | 2 +- httemplate/misc/openmap.html | 17 +++++++++-------- 3 files changed, 14 insertions(+), 9 deletions(-) diff --git a/httemplate/docs/license.html b/httemplate/docs/license.html index 4ab596db2..e47398283 100644 --- a/httemplate/docs/license.html +++ b/httemplate/docs/license.html @@ -146,6 +146,10 @@ under the terms of the MIT license. Contains the form validation jQuery plugin jQuery Validation by Jörn Zaefferer, licensed under the terms of MIT License. +

+Contains the leaflet JavaScript library Leaflet JS by Vladimir Agafonkin, +licensed under the terms of MIT License. +

diff --git a/httemplate/misc/confirm-censustract.html b/httemplate/misc/confirm-censustract.html index b491d4954..0f115e5d7 100644 --- a/httemplate/misc/confirm-censustract.html +++ b/httemplate/misc/confirm-censustract.html @@ -16,7 +16,7 @@ Confirm census tract <% $location{address1} |h %> <% $location{address2} |h %>
<% $location{city} |h %>, <% $location{state} |h %> <% $location{zip} |h %>

-% my $querystring = "census_year=$year&address=$location{address1}, $location{address2}, $location{city}, $location{state}, $location{zip}"; +% my $querystring = "census_year=$year&address=$location{address1}, $location{address2}, $location{city}, $location{state}"; Map service module location
% $querystring = "census_year=$year&pre=$pre&zip_code=" . $cache->get('zip'); diff --git a/httemplate/misc/openmap.html b/httemplate/misc/openmap.html index 6ccc72491..73f107142 100644 --- a/httemplate/misc/openmap.html +++ b/httemplate/misc/openmap.html @@ -1,11 +1,7 @@ - - - Find Census Tract Map - - - - -

Please select your location on the map

+<& /elements/header-logo.html, { title => 'Find Census Tract Map', head => $head, } &> + +

Please select your location on the map

+

 

@@ -79,6 +75,11 @@ local $SIG{__DIE__}; #disable Mason error trap my $DEBUG = 0; +my $head = ' + + +'; + my $census_year = $cgi->param('census_year'); my $pre = $cgi->param('pre'); my $zip_code = $cgi->param('zip_code'); -- cgit v1.2.1 From 0d62051d0e0ed3bd2e26e1a619dcb6190b653ab8 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Thu, 5 Oct 2017 10:17:36 -0400 Subject: RT# 76968 - Added logo only header file --- httemplate/elements/header-logo.html | 114 +++++++++++++++++++++++++++++++++++ 1 file changed, 114 insertions(+) create mode 100644 httemplate/elements/header-logo.html diff --git a/httemplate/elements/header-logo.html b/httemplate/elements/header-logo.html new file mode 100644 index 000000000..f272c56f6 --- /dev/null +++ b/httemplate/elements/header-logo.html @@ -0,0 +1,114 @@ +<%doc> + +Example: + + <& /elements/header-logo.html', + { + 'title' => 'Title', + 'menubar' => \@menubar, + 'etc' => '', #included in tag, for things like onLoad= + 'head' => '', #included before closing tag + 'nobr' => 0, #1 for no

after the title + 'no_jquery' => #for use from RT, which loads its own + } + &> + + + +%# +%# above is what RT declares, should we switch now? hopefully no glitches result +%# or just fuck it, XHTML died anyway, HTML 5 or bust? + + + + <% encode_entities($title) || $title_noescape |n %> + + + + + + + +% if ( $mobile ) { + +% } + +% unless ( $nocss ) { + + +% } + +% unless ( $no_jquery ) { + + + + +% if ( $FS::CurrentUser::CurrentUser->option('printtofit') ) { + +% } +% } + <% include('init_overlib.html') |n %> + <% include('rs_init_object.html') |n %> + + <% $head |n %> + +%# announce our base path, and the Mason comp path of this page + + + + STYLE="margin-top:0; margin-bottom:0; margin-left:0px; margin-right:0px"> + + + + + +
<% $company_url ? qq() : '' |n %>freeside<% $company_url ? '' : '' |n %> + <% $company_name || 'ExampleCo' %> +
+ +<%init> + +my( $title, $title_noescape, $menubar, $etc, $head ) = ( '', '', '', '', '' ); +my( $nobr, $nocss, $no_jquery ) = ( 0, 0, 0 ); + +my $mobile; + +my $opt = shift; +$title = $opt->{title}; +$title_noescape = $opt->{title_noescape}; +$menubar = $opt->{menubar}; +$etc = $opt->{etc}; +$head = $opt->{head}; +$nobr = $opt->{nobr}; +$nocss = $opt->{nocss}; +$mobile = $opt->{mobile}; +$no_jquery = $opt->{no_jquery}; + +my $conf = new FS::Conf; + +my $curuser = $FS::CurrentUser::CurrentUser; + +my $menu_position = $curuser->option('menu_position') + || 'top'; #new default for 1.9 + +if ( !defined($mobile) ) { + $mobile = $curuser->option('mobile_menu',1) && FS::UI::Web::is_mobile(); +} +if ( $cgi->param('mobile') =~ /^(\d)$/ ) { # allow client to override + $mobile = $1; +} + +my($company_name, $company_url); +my @agentnums = $curuser->agentnums; +if ( scalar(@agentnums) == 1 ) { + $company_name = $conf->config('company_name', $agentnums[0] ); + $company_url = $conf->config('company_url', $agentnums[0] ); +} else { + $company_name = $conf->config('company_name'); + $company_url = $conf->config('company_url'); +} + + \ No newline at end of file -- cgit v1.2.1 From b9c2b7cc73ad6455613268ae9e884ca0d34a127d Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Fri, 6 Oct 2017 10:36:54 -0400 Subject: RT# 77193 - Added files to alter creditcard or check information via selfservice --- .../FS-SelfService/cgi/change_check_pay.html | 23 ++++++++++++++++++++++ .../FS-SelfService/cgi/change_creditcard_pay.html | 23 ++++++++++++++++++++++ .../cgi/process_change_check_pay.html | 3 +++ .../cgi/process_change_creditcard_pay.html | 3 +++ 4 files changed, 52 insertions(+) create mode 100644 fs_selfservice/FS-SelfService/cgi/change_check_pay.html create mode 100644 fs_selfservice/FS-SelfService/cgi/change_creditcard_pay.html create mode 100644 fs_selfservice/FS-SelfService/cgi/process_change_check_pay.html create mode 100644 fs_selfservice/FS-SelfService/cgi/process_change_creditcard_pay.html diff --git a/fs_selfservice/FS-SelfService/cgi/change_check_pay.html b/fs_selfservice/FS-SelfService/cgi/change_check_pay.html new file mode 100644 index 000000000..7dd2583c4 --- /dev/null +++ b/fs_selfservice/FS-SelfService/cgi/change_check_pay.html @@ -0,0 +1,23 @@ +<%= include('header', 'Change ach payment information') %> + +<%= if ( $error ) { + $OUT .= qq!Error: $error

!; + } ''; %> + +
+ + + + <%= include('check') %> + +
+ Charge future payments to this card automatically +
+ + + +
+ + + +<%= include('footer') %> \ No newline at end of file diff --git a/fs_selfservice/FS-SelfService/cgi/change_creditcard_pay.html b/fs_selfservice/FS-SelfService/cgi/change_creditcard_pay.html new file mode 100644 index 000000000..cce555978 --- /dev/null +++ b/fs_selfservice/FS-SelfService/cgi/change_creditcard_pay.html @@ -0,0 +1,23 @@ +<%= include('header', 'Change credit card payment information') %> + +<%= if ( $error ) { + $OUT .= qq!Error: $error

!; + } ''; %> + + + + + + <%= include('card') %> + +
+ Charge future payments to this card automatically +
+ + + +
+ + + +<%= include('footer') %> \ No newline at end of file diff --git a/fs_selfservice/FS-SelfService/cgi/process_change_check_pay.html b/fs_selfservice/FS-SelfService/cgi/process_change_check_pay.html new file mode 100644 index 000000000..a1ad60cd5 --- /dev/null +++ b/fs_selfservice/FS-SelfService/cgi/process_change_check_pay.html @@ -0,0 +1,3 @@ +<%= include('header', 'ACH information updated successfully' ) %> +Ach information updated successfully. +<%= include('footer') %> \ No newline at end of file diff --git a/fs_selfservice/FS-SelfService/cgi/process_change_creditcard_pay.html b/fs_selfservice/FS-SelfService/cgi/process_change_creditcard_pay.html new file mode 100644 index 000000000..c95e09a77 --- /dev/null +++ b/fs_selfservice/FS-SelfService/cgi/process_change_creditcard_pay.html @@ -0,0 +1,3 @@ +<%= include('header', 'Information updated successfully' ) %> +Information updated successfully. +<%= include('footer') %> \ No newline at end of file -- cgit v1.2.1 From ee27cd8eea7b135db2ba8a6b1a6ac8933171d9d0 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Mon, 9 Oct 2017 14:36:07 -0400 Subject: RT# 77193 - fixed error when inserting new ach payby thru selfservice on V4. Now works --- FS/FS/ClientAPI/MyAccount.pm | 17 ++++++----- FS/FS/cust_payby.pm | 5 ++-- fs_selfservice/FS-SelfService/cgi/selfservice.cgi | 36 +++++++++++++++++++---- 3 files changed, 43 insertions(+), 15 deletions(-) diff --git a/FS/FS/ClientAPI/MyAccount.pm b/FS/FS/ClientAPI/MyAccount.pm index 30ab96b49..e28aa1870 100644 --- a/FS/FS/ClientAPI/MyAccount.pm +++ b/FS/FS/ClientAPI/MyAccount.pm @@ -1675,14 +1675,15 @@ sub insert_payby { #XXX payinfo1 + payinfo2 for CHEK? #or take the opportunity to use separate, more well- named fields? - # my $payinfo; - # $p->{'payinfo1'} =~ /^([\dx]+)$/ - # or return { 'error' => "illegal account number ". $p->{'payinfo1'} }; - # my $payinfo1 = $1; - # $p->{'payinfo2'} =~ /^([\dx\.]+)$/ # . turned on by echeck-country CA ? - # or return { 'error' => "illegal ABA/routing number ". $p->{'payinfo2'} }; - # my $payinfo2 = $1; - # $payinfo = $payinfo1. '@'. $payinfo2; + if ($p->{'payby'} eq 'CHEK') { + $p->{'payinfo1'} =~ /^([\dx]+)$/ + or return { 'error' => "illegal account number ". $p->{'payinfo1'} }; + my $payinfo1 = $1; + $p->{'payinfo2'} =~ /^([\dx\.]+)$/ # . turned on by echeck-country CA ? + or return { 'error' => "illegal ABA/routing number ". $p->{'payinfo2'} }; + my $payinfo2 = $1; + $p->{'payinfo'} = $payinfo1. '@'. $payinfo2; + } my $cust_payby = new FS::cust_payby { 'custnum' => $custnum, diff --git a/FS/FS/cust_payby.pm b/FS/FS/cust_payby.pm index fd90597bf..704741f3d 100644 --- a/FS/FS/cust_payby.pm +++ b/FS/FS/cust_payby.pm @@ -159,8 +159,9 @@ sub insert { local $FS::UID::AutoCommit = 0; my $dbh = dbh; - my $error = $self->check_payinfo_cardtype - || $self->SUPER::insert; + my $error = $self->check_payinfo_cardtype if $self->payby =~/^(CARD|DCRD)$/; + $self->SUPER::insert unless $error; + if ( $error ) { $dbh->rollback if $oldAutoCommit; return $error; diff --git a/fs_selfservice/FS-SelfService/cgi/selfservice.cgi b/fs_selfservice/FS-SelfService/cgi/selfservice.cgi index f194746c5..6cf264c08 100755 --- a/fs_selfservice/FS-SelfService/cgi/selfservice.cgi +++ b/fs_selfservice/FS-SelfService/cgi/selfservice.cgi @@ -337,6 +337,32 @@ sub _process_change_payby { } } +sub _process_insert_payby { + my ($erroraction, @fields) = @_; + + my $results = ''; + + $results ||= insert_payby ( + 'session_id' => $session_id, + map { ($_ => $cgi->param($_)) } grep { defined($cgi->param($_)) } @fields, + ); + + ## check error + + + if ( $results->{'error'} ) { + no strict 'refs'; + $action = $erroraction; + return { + $cgi->Vars, + %{&$action()}, + 'error' => ''. $results->{'error'}. '', + }; + } else { + return $results; + } +} + sub process_change_bill { _process_change_info( 'change_bill', qw( first last company address1 address2 city state @@ -389,20 +415,20 @@ sub process_change_creditcard_pay { address1 address2 city county state zip country auto paytype paystate ss stateid stateid_state invoicing_list ); - - _process_change_payby( 'change_creditcard_pay', @list ); + if ($cgi->param( 'custpaybynum' )) { _process_change_payby( 'change_creditcard_pay', @list ); } + else { _process_insert_payby( 'change_creditcard_pay', @list ); } } sub process_change_check_pay { my $payby = $cgi->param( 'payby' ); - $cgi->param('paydate', $cgi->param('year') . '-' . $cgi->param('month') . '-01'); + #$cgi->param('paydate', '2039-12-01'); my @list = qw( payby payinfo payinfo1 payinfo2 paydate payname custpaybynum address1 address2 city county state zip country auto paytype paystate ss stateid stateid_state invoicing_list ); - - _process_change_payby( 'change_check_pay', @list ); + if ($cgi->param( 'custpaybynum' )) { _process_change_payby( 'change_check_pay', @list ); } + else { _process_insert_payby( 'change_check_pay', @list ); } } sub view_invoice { -- cgit v1.2.1 From fba604e65e810922ce0269cc887b5c57ce135352 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Tue, 10 Oct 2017 10:04:07 -0400 Subject: RT# 77193 - fixed error updating ACH payby thru selfservice, not saving changed account or aba number on V4. Now works --- FS/FS/ClientAPI/MyAccount.pm | 10 ++++++++++ 1 file changed, 10 insertions(+) diff --git a/FS/FS/ClientAPI/MyAccount.pm b/FS/FS/ClientAPI/MyAccount.pm index e28aa1870..ce887efcd 100644 --- a/FS/FS/ClientAPI/MyAccount.pm +++ b/FS/FS/ClientAPI/MyAccount.pm @@ -1707,6 +1707,16 @@ sub update_payby { my($context, $session, $custnum) = _custoragent_session_custnum($p); return { 'error' => $session } if $context eq 'error'; + if ($p->{'payby'} eq 'CHEK') { + $p->{'payinfo1'} =~ /^([\dx]+)$/ + or return { 'error' => "illegal account number ". $p->{'payinfo1'} }; + my $payinfo1 = $1; + $p->{'payinfo2'} =~ /^([\dx\.]+)$/ # . turned on by echeck-country CA ? + or return { 'error' => "illegal ABA/routing number ". $p->{'payinfo2'} }; + my $payinfo2 = $1; + $p->{'payinfo'} = $payinfo1. '@'. $payinfo2; + } + my $cust_payby = qsearchs('cust_payby', { 'custnum' => $custnum, 'custpaybynum' => $p->{'custpaybynum'}, -- cgit v1.2.1 From 3baaec7890cb90834878c915b589a2eaf2ee437c Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Sat, 14 Oct 2017 17:04:04 -0700 Subject: no AGPL on customer self-service --- httemplate/docs/license.html | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/httemplate/docs/license.html b/httemplate/docs/license.html index 4ab596db2..91fbbe286 100644 --- a/httemplate/docs/license.html +++ b/httemplate/docs/license.html @@ -36,7 +36,9 @@ All rights reserved
option) any later version.

- At your option, you may also redistribute and/or modify the + At your option, you may also redistribute and/or modify the files in + fs_selfservice/drupal/ fs_selfservice/wordpress/ fs_selfservice/perl/ and + fs_selfservice/java/ directories and the fs_selfservice/php/freeside.class.php file (but not the rest of the software) under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, -- cgit v1.2.1 From 5ac72e74de9e105c8952eee7cf905ab9116926e1 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Sat, 14 Oct 2017 17:04:17 -0700 Subject: show compliance solutions JSON, RT#77789 --- FS/FS/Schema.pm | 2 ++ FS/FS/TaxEngine/compliance_solutions.pm | 1 + httemplate/view/cust_bill-taxengine_request.html | 14 ++++++++++++++ httemplate/view/cust_bill.cgi | 12 ++++++++++++ 4 files changed, 29 insertions(+) create mode 100644 httemplate/view/cust_bill-taxengine_request.html diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index d347c0653..6d7520bd9 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -695,6 +695,7 @@ sub tables_hashref { 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy 'promised_date', @date_type, '', '', + 'taxengine_request', 'text', 'NULL', '', '', '', 'pending', 'char', 'NULL', 1, '', '', ], @@ -732,6 +733,7 @@ sub tables_hashref { 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy 'promised_date', @date_type, '', '', + 'taxengine_request', 'text', 'NULL', '', '', '', #void fields 'void_date', @date_type, '', '', diff --git a/FS/FS/TaxEngine/compliance_solutions.pm b/FS/FS/TaxEngine/compliance_solutions.pm index 1f0c16605..33b6a3ef1 100644 --- a/FS/FS/TaxEngine/compliance_solutions.pm +++ b/FS/FS/TaxEngine/compliance_solutions.pm @@ -226,6 +226,7 @@ sub make_taxlines { } ); warn $request_json if $DEBUG > 1; + $cust_bill->taxengine_request($request_json); my $soap = SOAP::Lite->service("http://tcms1.csilongwood.com/cgi-bin/taxcalc.wsdl"); diff --git a/httemplate/view/cust_bill-taxengine_request.html b/httemplate/view/cust_bill-taxengine_request.html new file mode 100644 index 000000000..991e65f46 --- /dev/null +++ b/httemplate/view/cust_bill-taxengine_request.html @@ -0,0 +1,14 @@ +<& /elements/header-popup.html &> +<% $cust_bill->taxengine_request |h %> +<& /elements/footer-popup.html &> +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" + unless $curuser->access_right('View invoices'); + +my $invnum = $cgi->param('invnum'); +my $cust_bill = qsearchs('cust_bill', { invnum=>$invnum }); + + diff --git a/httemplate/view/cust_bill.cgi b/httemplate/view/cust_bill.cgi index e35d7f1cf..762eec033 100755 --- a/httemplate/view/cust_bill.cgi +++ b/httemplate/view/cust_bill.cgi @@ -171,6 +171,18 @@ function change_invoice_mode(obj) { &> % $br++; % } +% if ( $cust_bill->taxengine_request ) { # inefficient +<% $br ? '|' : '' %> +<& /elements/popup_link.html, + 'action' => 'cust_bill-taxengine_request.html?invnum=' . $cust_bill->invnum, + 'label' => mt('View raw tax engine request'), + 'actionlabel' => mt('Tax engine request'), + 'width' => 1050, + 'height' => 600, + 'title' => emt('Tax engine request'), +&> +% $br++; +% }

-- cgit v1.2.1 From 1b80af57bc11e732ee1811d58f286d5a183c317c Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Tue, 17 Oct 2017 09:41:12 -0400 Subject: RT# 74918 - Added total revenue line to Package churn report --- FS/FS/Report/Table.pm | 48 ++++++++++++++++++++++++++++++++++ FS/FS/h_cust_pkg.pm | 6 ++--- httemplate/graph/cust_pkg.html | 13 ++++++--- httemplate/graph/elements/monthly.html | 2 ++ httemplate/graph/elements/report.html | 3 ++- 5 files changed, 65 insertions(+), 7 deletions(-) diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 5fb56404d..e3e0854ec 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -863,6 +863,54 @@ sub unsusp_pkg { $self->churn_pkg('unsusp', @_); } +sub total_revenue_pkg { + my $self = shift; + my $active_revenue = $self->revenue_pkg('active', @_); + my $setup_revenue = $self->revenue_pkg('setup', @_); + my $return = sprintf("%.2f", $active_revenue + $setup_revenue); + + return $return; +} + +sub revenue_pkg { + my $self = shift; + my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $totalrevenue; + + my ($from, @where) = + FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod); + + push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum); + + my $sql; + + if ($status eq "active") { + $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.recur AS revenue + FROM $from + JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart) + JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum) + JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND cust_pkg.history_date + 5 > revenue.history_date) + "; + } + elsif ($status eq "setup") { + $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.setup AS revenue + FROM $from + JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart) + JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum) + JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND cust_pkg.setup + 15 > revenue.history_date) + "; + } + + $sql .= ' WHERE '.join(' AND ', @where) + if scalar(@where); + + $sql .= "ORDER BY revenue.pkgnum ASC, revenue.history_date DESC"; + + my $revenue_sql = "SELECT sum(rev.revenue) AS total_revenue FROM ( $sql ) AS rev"; + + $self->scalar_sql($revenue_sql); +} + sub churn_pkg { my $self = shift; my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_; diff --git a/FS/FS/h_cust_pkg.pm b/FS/FS/h_cust_pkg.pm index 423b44250..f0746476c 100644 --- a/FS/FS/h_cust_pkg.pm +++ b/FS/FS/h_cust_pkg.pm @@ -140,9 +140,9 @@ sub churn_fromwhere_sql { # XXX or should we include if they were created by a pkgpart change? $from = "cust_pkg"; @where = ( - "setup >= $speriod", - "setup < $eperiod", - "change_pkgnum IS NULL" + "cust_pkg.setup >= $speriod", + "cust_pkg.setup < $eperiod", + "cust_pkg.change_pkgnum IS NULL" ); } elsif ( $status eq 'cancel' ) { # also simple, because packages should only be canceled once diff --git a/httemplate/graph/cust_pkg.html b/httemplate/graph/cust_pkg.html index 3b6552ba8..68c5b2136 100644 --- a/httemplate/graph/cust_pkg.html +++ b/httemplate/graph/cust_pkg.html @@ -7,12 +7,13 @@ 'links' => \@links, 'params' => \@params, 'agentnum' => $agentnum, - 'sprintf' => ( $normalize ? '%0.1f%%' : '%u'), + 'sprintf' => ( $normalize ? '%0.1f%%' : '%u'), + 'sprintf_fields' => $sprintf_fields, 'normalize' => ( $normalize ? 0 : undef ), 'disable_money' => 1, 'remove_empty' => (scalar(@group_keys) > 1 ? 1 : 0), 'nototal' => 1, - 'no_graph' => [ 1, 0, 0, 0, 0 ], # don't graph 'active' + 'no_graph' => [ 1, 0, 0, 0, 0, 1 ], # don't graph 'active, total_revenue' &> <%init> @@ -33,7 +34,7 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { my $agentname = $agent ? $agent->agent.' ' : ''; -my @base_items = qw( active_pkg setup_pkg susp_pkg unsusp_pkg cancel_pkg ); +my @base_items = qw( active_pkg setup_pkg susp_pkg unsusp_pkg cancel_pkg total_revenue_pkg ); my %base_labels = ( 'active_pkg' => 'Active packages', @@ -41,6 +42,7 @@ my %base_labels = ( 'susp_pkg' => 'Suspensions', 'unsusp_pkg' => 'Unsuspensions', 'cancel_pkg' => 'Cancellations', + 'total_revenue_pkg' => 'Total Revenue' ); my %base_colors = ( @@ -49,8 +51,13 @@ my %base_colors = ( 'susp_pkg' => 'ff9900', #yellow 'unsusp_pkg' => '44ff44', #light green 'cancel_pkg' => 'cc0000', #red + 'total_revenue_pkg' => '0000ff', #blue ); +my $sprintf_fields = { + 'total_revenue_pkg' => '%.2f', #format to 2 decimal places +}; + my %base_links; foreach my $status (qw(active setup cancel susp unsusp)) { $base_links{$status.'_pkg'} = diff --git a/httemplate/graph/elements/monthly.html b/httemplate/graph/elements/monthly.html index 1a9428115..cfe5a3c6d 100644 --- a/httemplate/graph/elements/monthly.html +++ b/httemplate/graph/elements/monthly.html @@ -59,6 +59,7 @@ Example: 'no_graph' => \@no_graph, 'bottom_link' => \@bottom_link, 'transpose' => $opt{'daily'}, + 'sprintf_fields' => $sprintf_fields, map { $_, $opt{$_} } (qw(title nototal graph_type @@ -79,6 +80,7 @@ my $fromparam = $opt{'link_fromparam'} || 'begin'; my $toparam = $opt{'link_toparam'} || 'end'; my @items = @{ $opt{'items'} }; +my $sprintf_fields = $opt{'sprintf_fields'}; foreach my $other (qw( labels graph_labels colors links )) { if ( ref($opt{$other}) eq 'HASH' ) { diff --git a/httemplate/graph/elements/report.html b/httemplate/graph/elements/report.html index b5d214816..70c3a9e94 100644 --- a/httemplate/graph/elements/report.html +++ b/httemplate/graph/elements/report.html @@ -249,7 +249,7 @@ any delimiter and linked from the elements in @data. % my $e = 0; % foreach ( @$data_row ) { % my $entry = $_; -% $entry = $money_char . sprintf($sprintf, $entry); +% $entry = $money_char . sprintf($sprintf_fields->{$row} ? $sprintf_fields->{$row} : $sprintf, $entry); % $entry = $link_prefix . shift(@$links) . "\">$entry" if $link_prefix; % push @{$cell[$i]}, $entry; % $bottom_total[$e++] += $_ unless $opt{no_graph}[$i-1]; @@ -343,6 +343,7 @@ my $conf = new FS::Conf; my $money_char = $opt{'disable_money'} ? '' : $conf->config('money_char'); my @items = @{ $opt{'items'} }; +my $sprintf_fields = $opt{'sprintf_fields'}; foreach my $other (qw( col_labels row_labels graph_labels axis_labels colors links )) { if ( ref($opt{$other}) eq 'HASH' ) { -- cgit v1.2.1 From f654e068b6e7be55bdbd749293c1bda7737cf870 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Tue, 17 Oct 2017 09:41:12 -0400 Subject: RT# 78019 - Added total revenue line to Package churn report --- FS/FS/Report/Table.pm | 48 ++++++++++++++++++++++++++++++++++ FS/FS/h_cust_pkg.pm | 6 ++--- httemplate/graph/cust_pkg.html | 13 ++++++--- httemplate/graph/elements/monthly.html | 2 ++ httemplate/graph/elements/report.html | 3 ++- 5 files changed, 65 insertions(+), 7 deletions(-) diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 5fb56404d..e3e0854ec 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -863,6 +863,54 @@ sub unsusp_pkg { $self->churn_pkg('unsusp', @_); } +sub total_revenue_pkg { + my $self = shift; + my $active_revenue = $self->revenue_pkg('active', @_); + my $setup_revenue = $self->revenue_pkg('setup', @_); + my $return = sprintf("%.2f", $active_revenue + $setup_revenue); + + return $return; +} + +sub revenue_pkg { + my $self = shift; + my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $totalrevenue; + + my ($from, @where) = + FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod); + + push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum); + + my $sql; + + if ($status eq "active") { + $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.recur AS revenue + FROM $from + JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart) + JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum) + JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND cust_pkg.history_date + 5 > revenue.history_date) + "; + } + elsif ($status eq "setup") { + $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.setup AS revenue + FROM $from + JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart) + JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum) + JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND cust_pkg.setup + 15 > revenue.history_date) + "; + } + + $sql .= ' WHERE '.join(' AND ', @where) + if scalar(@where); + + $sql .= "ORDER BY revenue.pkgnum ASC, revenue.history_date DESC"; + + my $revenue_sql = "SELECT sum(rev.revenue) AS total_revenue FROM ( $sql ) AS rev"; + + $self->scalar_sql($revenue_sql); +} + sub churn_pkg { my $self = shift; my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_; diff --git a/FS/FS/h_cust_pkg.pm b/FS/FS/h_cust_pkg.pm index 423b44250..f0746476c 100644 --- a/FS/FS/h_cust_pkg.pm +++ b/FS/FS/h_cust_pkg.pm @@ -140,9 +140,9 @@ sub churn_fromwhere_sql { # XXX or should we include if they were created by a pkgpart change? $from = "cust_pkg"; @where = ( - "setup >= $speriod", - "setup < $eperiod", - "change_pkgnum IS NULL" + "cust_pkg.setup >= $speriod", + "cust_pkg.setup < $eperiod", + "cust_pkg.change_pkgnum IS NULL" ); } elsif ( $status eq 'cancel' ) { # also simple, because packages should only be canceled once diff --git a/httemplate/graph/cust_pkg.html b/httemplate/graph/cust_pkg.html index 3b6552ba8..68c5b2136 100644 --- a/httemplate/graph/cust_pkg.html +++ b/httemplate/graph/cust_pkg.html @@ -7,12 +7,13 @@ 'links' => \@links, 'params' => \@params, 'agentnum' => $agentnum, - 'sprintf' => ( $normalize ? '%0.1f%%' : '%u'), + 'sprintf' => ( $normalize ? '%0.1f%%' : '%u'), + 'sprintf_fields' => $sprintf_fields, 'normalize' => ( $normalize ? 0 : undef ), 'disable_money' => 1, 'remove_empty' => (scalar(@group_keys) > 1 ? 1 : 0), 'nototal' => 1, - 'no_graph' => [ 1, 0, 0, 0, 0 ], # don't graph 'active' + 'no_graph' => [ 1, 0, 0, 0, 0, 1 ], # don't graph 'active, total_revenue' &> <%init> @@ -33,7 +34,7 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { my $agentname = $agent ? $agent->agent.' ' : ''; -my @base_items = qw( active_pkg setup_pkg susp_pkg unsusp_pkg cancel_pkg ); +my @base_items = qw( active_pkg setup_pkg susp_pkg unsusp_pkg cancel_pkg total_revenue_pkg ); my %base_labels = ( 'active_pkg' => 'Active packages', @@ -41,6 +42,7 @@ my %base_labels = ( 'susp_pkg' => 'Suspensions', 'unsusp_pkg' => 'Unsuspensions', 'cancel_pkg' => 'Cancellations', + 'total_revenue_pkg' => 'Total Revenue' ); my %base_colors = ( @@ -49,8 +51,13 @@ my %base_colors = ( 'susp_pkg' => 'ff9900', #yellow 'unsusp_pkg' => '44ff44', #light green 'cancel_pkg' => 'cc0000', #red + 'total_revenue_pkg' => '0000ff', #blue ); +my $sprintf_fields = { + 'total_revenue_pkg' => '%.2f', #format to 2 decimal places +}; + my %base_links; foreach my $status (qw(active setup cancel susp unsusp)) { $base_links{$status.'_pkg'} = diff --git a/httemplate/graph/elements/monthly.html b/httemplate/graph/elements/monthly.html index 1a9428115..cfe5a3c6d 100644 --- a/httemplate/graph/elements/monthly.html +++ b/httemplate/graph/elements/monthly.html @@ -59,6 +59,7 @@ Example: 'no_graph' => \@no_graph, 'bottom_link' => \@bottom_link, 'transpose' => $opt{'daily'}, + 'sprintf_fields' => $sprintf_fields, map { $_, $opt{$_} } (qw(title nototal graph_type @@ -79,6 +80,7 @@ my $fromparam = $opt{'link_fromparam'} || 'begin'; my $toparam = $opt{'link_toparam'} || 'end'; my @items = @{ $opt{'items'} }; +my $sprintf_fields = $opt{'sprintf_fields'}; foreach my $other (qw( labels graph_labels colors links )) { if ( ref($opt{$other}) eq 'HASH' ) { diff --git a/httemplate/graph/elements/report.html b/httemplate/graph/elements/report.html index b5d214816..70c3a9e94 100644 --- a/httemplate/graph/elements/report.html +++ b/httemplate/graph/elements/report.html @@ -249,7 +249,7 @@ any delimiter and linked from the elements in @data. % my $e = 0; % foreach ( @$data_row ) { % my $entry = $_; -% $entry = $money_char . sprintf($sprintf, $entry); +% $entry = $money_char . sprintf($sprintf_fields->{$row} ? $sprintf_fields->{$row} : $sprintf, $entry); % $entry = $link_prefix . shift(@$links) . "\">$entry" if $link_prefix; % push @{$cell[$i]}, $entry; % $bottom_total[$e++] += $_ unless $opt{no_graph}[$i-1]; @@ -343,6 +343,7 @@ my $conf = new FS::Conf; my $money_char = $opt{'disable_money'} ? '' : $conf->config('money_char'); my @items = @{ $opt{'items'} }; +my $sprintf_fields = $opt{'sprintf_fields'}; foreach my $other (qw( col_labels row_labels graph_labels axis_labels colors links )) { if ( ref($opt{$other}) eq 'HASH' ) { -- cgit v1.2.1