From 7ecfcfeda7f249f89534f01da509a51a6c44b082 Mon Sep 17 00:00:00 2001 From: Jonathan Prykop Date: Thu, 9 Jun 2016 19:50:39 -0500 Subject: RT#71210: Update Payment Report for recent BIN changes --- httemplate/search/elements/cust_pay_or_refund.html | 24 +++++++++++++++------- 1 file changed, 17 insertions(+), 7 deletions(-) diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index 9f725bb0b..c6617b142 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -327,7 +327,9 @@ if ( $cgi->param('magic') ) { #avoid posix regexes for portability $search = + # Visa " ( ( substring($table.payinfo from 1 for 1) = '4' ". + # is not Switch " AND substring($table.payinfo from 1 for 4) != '4936' ". " AND substring($table.payinfo from 1 for 6) ". " NOT $similar_to '49030[2-9]' ". @@ -340,13 +342,18 @@ if ( $cgi->param('magic') ) { " AND substring($table.payinfo from 1 for 6) ". " NOT $similar_to '49118[1-2]' ". " )". + # MasterCard " OR substring($table.payinfo from 1 for 2) = '51' ". " OR substring($table.payinfo from 1 for 2) = '52' ". " OR substring($table.payinfo from 1 for 2) = '53' ". " OR substring($table.payinfo from 1 for 2) = '54' ". " OR substring($table.payinfo from 1 for 2) = '54' ". " OR substring($table.payinfo from 1 for 2) = '55' ". -# " OR substring($table.payinfo from 1 for 2) = '36' ". #Diner's int'l was processed as Visa/MC inside US, now Discover + " OR substring($table.payinfo from 1 for 4) $similar_to '222[1-9]' ". + " OR substring($table.payinfo from 1 for 3) $similar_to '22[3-9]' ". + " OR substring($table.payinfo from 1 for 2) $similar_to '2[3-6]' ". + " OR substring($table.payinfo from 1 for 3) $similar_to '27[0-1]' ". + " OR substring($table.payinfo from 1 for 4) = '2720' ". " ) "; } elsif ( $cardtype eq 'Amex' ) { @@ -363,14 +370,14 @@ if ( $cgi->param('magic') ) { $search = " ( substring($table.payinfo from 1 for 4 ) = '6011' ". " OR substring($table.payinfo from 1 for 2 ) = '65' ". - " OR substring($table.payinfo from 1 for 3 ) = '300' ". + " OR substring($table.payinfo from 1 for 3 ) = '300' ". # diner's 300-305 " OR substring($table.payinfo from 1 for 3 ) = '301' ". " OR substring($table.payinfo from 1 for 3 ) = '302' ". " OR substring($table.payinfo from 1 for 3 ) = '303' ". " OR substring($table.payinfo from 1 for 3 ) = '304' ". " OR substring($table.payinfo from 1 for 3 ) = '305' ". - " OR substring($table.payinfo from 1 for 4 ) = '3095' ". - " OR substring($table.payinfo from 1 for 2 ) = '36' ". + " OR substring($table.payinfo from 1 for 4 ) = '3095' ". # diner's 3095 + " OR substring($table.payinfo from 1 for 2 ) = '36' ". # diner's 36, 38, 39 " OR substring($table.payinfo from 1 for 2 ) = '38' ". " OR substring($table.payinfo from 1 for 2 ) = '39' ". " OR substring($table.payinfo from 1 for 3 ) = '644' ". @@ -379,8 +386,8 @@ if ( $cgi->param('magic') ) { " OR substring($table.payinfo from 1 for 3 ) = '647' ". " OR substring($table.payinfo from 1 for 3 ) = '648' ". " OR substring($table.payinfo from 1 for 3 ) = '649' ". - ( $country =~ /^(US|CA)$/ - ?" OR substring($table.payinfo from 1 for 4 ) = '3528' ". # JCB cards in the 3528-3589 range identified as Discover inside US/CA + ( $country =~ /^(US|PR|VI|MP|PW|GU)$/ + ?" OR substring($table.payinfo from 1 for 4 ) = '3528' ". # JCB cards in the 3528-3589 range identified as Discover inside US & territories (NOT Canada) " OR substring($table.payinfo from 1 for 4 ) = '3529' ". " OR substring($table.payinfo from 1 for 3 ) = '353' ". " OR substring($table.payinfo from 1 for 3 ) = '354' ". @@ -390,7 +397,10 @@ if ( $cgi->param('magic') ) { " OR substring($table.payinfo from 1 for 3 ) = '358' " :"" ). - " OR substring($table.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN + ( $country =~ /^(US|MX|AI|AG|AW|BS|BB|BM|BQ|VG|KY|CW|DM|DO|GD|GP|JM|MQ|MS|BL|KN|LC|VC|MF|SX|TT|TC)$/ + ?" OR substring($table.payinfo from 1 for 3 ) $similar_to '62[24-68]' " #China Union Pay processed as Discover outside CN + :"" + ). " ) "; } elsif ( $cardtype eq 'Maestro' ) { -- cgit v1.2.1 From 68fbaf7cc232930ef94015f1ee3d503bdb55c388 Mon Sep 17 00:00:00 2001 From: Jonathan Prykop Date: Thu, 9 Jun 2016 23:50:43 -0500 Subject: RT#42297: Show total usage on Selfservice View my usage page --- FS/FS/ClientAPI/MyAccount.pm | 12 ++++++++++++ fs_selfservice/FS-SelfService/cgi/view_usage.html | 12 ++++++++++++ 2 files changed, 24 insertions(+) diff --git a/FS/FS/ClientAPI/MyAccount.pm b/FS/FS/ClientAPI/MyAccount.pm index 986306524..531b2e249 100644 --- a/FS/FS/ClientAPI/MyAccount.pm +++ b/FS/FS/ClientAPI/MyAccount.pm @@ -1835,6 +1835,7 @@ sub list_svcs { # @svc_x; my @svcs; # stuff to return to the client + my %bytes_used_total; # for _used columns only foreach my $cust_svc (@cust_svc) { my $svc_x = $cust_svc->svc_x; my($label, $value) = $cust_svc->label; @@ -1868,6 +1869,10 @@ sub list_svcs { 'downbytes_used' => display_bytecount($down_used), 'totalbytes_used' => display_bytecount($up_used + $down_used) ); + $bytes_used_total{'seconds_used'} += $hash{'seconds_used'}; + $bytes_used_total{'upbytes_used'} += $up_used; + $bytes_used_total{'downbytes_used'} += $down_used; + $bytes_used_total{'totalbytes_used'} += $up_used + $down_used; } if ( $svcdb eq 'svc_acct' ) { @@ -1942,12 +1947,19 @@ sub list_svcs { push @svcs, \%hash; } # foreach $cust_svc + foreach my $field (keys %bytes_used_total) { + if ($field =~ /bytes/) { + $bytes_used_total{$field} = display_bytecount($bytes_used_total{$field}); + } + } + return { 'svcnum' => $session->{'svcnum'}, 'custnum' => $custnum, 'date_format' => $conf->config('date_format') || '%m/%d/%Y', 'view_usage_nodomain' => $conf->exists('selfservice-view_usage_nodomain'), 'svcs' => \@svcs, + 'bytes_used_total' => \%bytes_used_total, 'usage_pools' => [ map { $usage_pools{$_} } sort { $a cmp $b } diff --git a/fs_selfservice/FS-SelfService/cgi/view_usage.html b/fs_selfservice/FS-SelfService/cgi/view_usage.html index 07ccfedf1..2b9eb8bc2 100644 --- a/fs_selfservice/FS-SelfService/cgi/view_usage.html +++ b/fs_selfservice/FS-SelfService/cgi/view_usage.html @@ -75,6 +75,18 @@ $OUT .= ''; } } + if ((@bytes_svcs > 1) and (grep { $bytes_show{$_.'_used'} } qw(seconds upbytes downbytes totalbytes) )) { + $OUT .= ''; + $OUT .= 'Total Used'; + foreach my $field (@bytes_cols) { + if ($bytes_show{$field}) { + $OUT .= ''; + $OUT .= $bytes_used_total{$field} || '0' if $field =~ /_used$/; + $OUT .= ''; + } + } + $OUT .= ''; + } %> <%= scalar(@bytes_svcs) ? '

' : '' %> -- cgit v1.2.1 From b3ec8af7aa18a9bdada4136657cde8013b3196b3 Mon Sep 17 00:00:00 2001 From: Jonathan Prykop Date: Sat, 11 Jun 2016 02:47:53 -0500 Subject: RT#42348: Add IDT transaction ID to the Agent commission report --- httemplate/elements/menu.html | 3 +- httemplate/search/agent_credit_payment.html | 155 +++++++++++++++++++++ httemplate/search/report_agent_credit_payment.html | 30 ++++ 3 files changed, 187 insertions(+), 1 deletion(-) create mode 100644 httemplate/search/agent_credit_payment.html create mode 100755 httemplate/search/report_agent_credit_payment.html diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 93bebb17a..0f98bc960 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -364,7 +364,8 @@ tie my %report_commissions, 'Tie::IxHash', 'Agent per package' => [ $fsurl.'search/report_agent_commission_pkg.html' ], 'Sales Person' => [ $fsurl.'search/report_sales_commission.html' ], 'Sales Person per package' => [ $fsurl.'search/report_sales_commission_pkg.html' ], - 'Employee' => [ $fsurl.'search/report_employee_commission.html', '' ] + 'Employee' => [ $fsurl.'search/report_employee_commission.html', '' ], + 'Agent Credits and Payments' => [ $fsurl.'search/report_agent_credit_payment.html' ], ; tie my %report_financial, 'Tie::IxHash'; diff --git a/httemplate/search/agent_credit_payment.html b/httemplate/search/agent_credit_payment.html new file mode 100644 index 000000000..0dda83bbd --- /dev/null +++ b/httemplate/search/agent_credit_payment.html @@ -0,0 +1,155 @@ +<& elements/grid-report.html, + title => $title.'Package Agent Credits and Payments', + rows => $rows, + cells => $cells, + head => <Shows agent commission credits, and payments applied to invoices for packages that triggered those credits.

+ +END +&> + +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; +die "access denied" + unless $curuser->access_right('Financial reports'); + +my $extra_sql = ''; + +# search for agent +my ($agentnum,$sel_agent); +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + die "agentnum $agentnum not found!" unless $sel_agent; + $extra_sql .= " AND cust_credit.commission_agentnum = $agentnum\n"; +} +my $title = $sel_agent ? $sel_agent->agent.' ' : ''; + +# search for credits in time period (applied to payments in $query) +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +$extra_sql .= " AND cust_credit._date >= $beginning\n"; +$extra_sql .= " AND cust_credit._date <= $ending\n"; + +# agent virtualization +my $agentnums_sql = $curuser->agentnums_sql( table => 'agent' ); + +my $join = <= $beginning + AND cust_pay._date <= $ending + AND $agentnums_sql +$extra_sql +UNION +SELECT DISTINCT + agent, + cust_pkg.custnum AS xcustnum, + cust_credit.commission_pkgnum AS xpkgnum, + pkg, + 'cust_credit' AS xtable, + cust_credit.crednum AS xnum, + to_timestamp(cust_credit._date) AS xdate, + cust_credit.amount AS xamount, + '' AS order_number +FROM cust_credit +$join +WHERE cust_credit.commission_pkgnum is not null + AND $agentnums_sql +$extra_sql +ORDER BY agent, xcustnum, xpkgnum, xdate +END + +my $sth = dbh->prepare($query) or die dbh->errstr; +$sth->execute() or die $sth->errstr; + +my $cells = []; +my $rows = []; +my $agentstack = []; +my $custstack = []; +my $pkgstack = []; +my ($prev_agent,$count_agent,$prev_cust,$count_cust,$prev_pkg,$count_pkg); +while (my $row = $sth->fetchrow_arrayref) { + my @row = @$row; + my $curr_agent = shift @row; + my $curr_cust = shift @row; + my $curr_pkg = (shift @row) . ': ' . (shift @row); + + if ($curr_pkg eq $prev_pkg) { + $count_pkg += 1; + } else { + unshift @{$$pkgstack[0]}, { value => $prev_pkg, rowspan => $count_pkg } if @$pkgstack;; + push @$custstack, @$pkgstack; + $pkgstack = []; + $count_pkg = 1; + } + $prev_pkg = $curr_pkg; + + if ($curr_cust eq $prev_cust) { + $count_cust += 1; + } else { + if (@$custstack) { + my $cust_main = qsearchs('cust_main',{ custnum => $prev_cust }); + unshift @{$$custstack[0]}, { value => $cust_main->name, rowspan => $count_cust } if @$custstack;; + } + push @$agentstack, @$custstack; + $custstack = []; + $count_cust = 1; + } + $prev_cust = $curr_cust; + + if ($curr_agent eq $prev_agent) { + $count_agent += 1; + } else { + unshift @{$$agentstack[0]}, { value => $prev_agent, rowspan => $count_agent } if @$agentstack;; + push @$cells, @$agentstack; + $agentstack = []; + $count_agent = 1; + } + $prev_agent = $curr_agent; + + my %coloropts = ($row[0] eq 'cust_credit') ? ( 'class' => 'creditcell' ) : ( 'class' => 'paycell' ); + push @$pkgstack, [ map { { value => $_, %coloropts } } @row ]; +} + +unshift @{$$pkgstack[0]}, { value => $prev_pkg, rowspan => $count_pkg } if @$pkgstack;; +push @$custstack, @$pkgstack; +if (@$custstack) { + my $cust_main = qsearchs('cust_main',{ custnum => $prev_cust }); + unshift @{$$custstack[0]}, { value => $cust_main->name, rowspan => $count_cust } if @$custstack;; +} +push @$agentstack, @$custstack; +unshift @{$$agentstack[0]}, { value => $prev_agent, rowspan => $count_agent } if @$agentstack;; +push @$cells, @$agentstack; + +$sth->finish; + +my $rows = [ map { {} } @$cells ]; + +unshift @$cells, [ map { { value => $_, header => 1 } } ('Agent','Customer','Package','Table','#','Date','Amount','Order Number') ]; +unshift @$rows, { header => 1 }; + + diff --git a/httemplate/search/report_agent_credit_payment.html b/httemplate/search/report_agent_credit_payment.html new file mode 100755 index 000000000..57fc6a79c --- /dev/null +++ b/httemplate/search/report_agent_credit_payment.html @@ -0,0 +1,30 @@ +<& /elements/header.html, 'Package Agent Credits and Payments' &> + +

Shows agent commission credits, and payments applied to invoices for packages that triggered those credits.

+ +
+ + + + <& /elements/tr-select-agent.html, + 'curr_value' => scalar( $cgi->param('agentnum') ), + 'label' => 'Agent ', + 'disable_empty' => 0, + &> + + <& /elements/tr-input-beginning_ending.html &> + +
+ +
+ + +
+ +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + -- cgit v1.2.1 From ca55bb70ef502d50c088201fc6ded73907e5d5e1 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Mon, 13 Jun 2016 13:12:53 -0700 Subject: report correct card type for all discovers when we only have the first two digits, RT#71207 --- httemplate/search/elements/cust_pay_or_refund.html | 27 +++++++++++++++++----- 1 file changed, 21 insertions(+), 6 deletions(-) diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index c6617b142..4ed297dac 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -354,6 +354,7 @@ if ( $cgi->param('magic') ) { " OR substring($table.payinfo from 1 for 2) $similar_to '2[3-6]' ". " OR substring($table.payinfo from 1 for 3) $similar_to '27[0-1]' ". " OR substring($table.payinfo from 1 for 4) = '2720' ". + " OR substring($table.payinfo from 1 for 3) = '2[2-7]x' ". " ) "; } elsif ( $cardtype eq 'Amex' ) { @@ -369,38 +370,52 @@ if ( $cgi->param('magic') ) { $search = " ( substring($table.payinfo from 1 for 4 ) = '6011' ". + " OR substring($table.payinfo from 1 for 3 ) = '60x' ". " OR substring($table.payinfo from 1 for 2 ) = '65' ". - " OR substring($table.payinfo from 1 for 3 ) = '300' ". # diner's 300-305 + + # diner's 300-305 / 3095 + " OR substring($table.payinfo from 1 for 3 ) = '300' ". " OR substring($table.payinfo from 1 for 3 ) = '301' ". " OR substring($table.payinfo from 1 for 3 ) = '302' ". " OR substring($table.payinfo from 1 for 3 ) = '303' ". " OR substring($table.payinfo from 1 for 3 ) = '304' ". " OR substring($table.payinfo from 1 for 3 ) = '305' ". - " OR substring($table.payinfo from 1 for 4 ) = '3095' ". # diner's 3095 - " OR substring($table.payinfo from 1 for 2 ) = '36' ". # diner's 36, 38, 39 + " OR substring($table.payinfo from 1 for 4 ) = '3095' ". + " OR substring($table.payinfo from 1 for 3 ) = '30x' ". + + # diner's 36, 38, 39 + " OR substring($table.payinfo from 1 for 2 ) = '36' ". " OR substring($table.payinfo from 1 for 2 ) = '38' ". " OR substring($table.payinfo from 1 for 2 ) = '39' ". + " OR substring($table.payinfo from 1 for 3 ) = '644' ". " OR substring($table.payinfo from 1 for 3 ) = '645' ". " OR substring($table.payinfo from 1 for 3 ) = '646' ". " OR substring($table.payinfo from 1 for 3 ) = '647' ". " OR substring($table.payinfo from 1 for 3 ) = '648' ". " OR substring($table.payinfo from 1 for 3 ) = '649' ". + " OR substring($table.payinfo from 1 for 3 ) = '64x' ". + + # JCB cards in the 3528-3589 range identified as Discover inside US & territories (NOT Canada) ( $country =~ /^(US|PR|VI|MP|PW|GU)$/ - ?" OR substring($table.payinfo from 1 for 4 ) = '3528' ". # JCB cards in the 3528-3589 range identified as Discover inside US & territories (NOT Canada) + ?" OR substring($table.payinfo from 1 for 4 ) = '3528' ". " OR substring($table.payinfo from 1 for 4 ) = '3529' ". " OR substring($table.payinfo from 1 for 3 ) = '353' ". " OR substring($table.payinfo from 1 for 3 ) = '354' ". " OR substring($table.payinfo from 1 for 3 ) = '355' ". " OR substring($table.payinfo from 1 for 3 ) = '356' ". " OR substring($table.payinfo from 1 for 3 ) = '357' ". - " OR substring($table.payinfo from 1 for 3 ) = '358' " + " OR substring($table.payinfo from 1 for 3 ) = '358' ". + " OR substring($table.payinfo from 1 for 3 ) = '35x' " :"" ). + + #China Union Pay processed as Discover in US, Mexico and Caribbean ( $country =~ /^(US|MX|AI|AG|AW|BS|BB|BM|BQ|VG|KY|CW|DM|DO|GD|GP|JM|MQ|MS|BL|KN|LC|VC|MF|SX|TT|TC)$/ - ?" OR substring($table.payinfo from 1 for 3 ) $similar_to '62[24-68]' " #China Union Pay processed as Discover outside CN + ?" OR substring($table.payinfo from 1 for 3 ) $similar_to '62[24-68x]' " :"" ). + " ) "; } elsif ( $cardtype eq 'Maestro' ) { -- cgit v1.2.1