diff options
| -rw-r--r-- | FS/FS/ClientAPI/MyAccount.pm | 12 | ||||
| -rw-r--r-- | bin/cust_pay-delete | 33 | ||||
| -rw-r--r-- | fs_selfservice/FS-SelfService/cgi/view_usage.html | 12 | ||||
| -rw-r--r-- | httemplate/elements/menu.html | 3 | ||||
| -rw-r--r-- | httemplate/search/agent_credit_payment.html | 155 | ||||
| -rwxr-xr-x | httemplate/search/elements/cust_pay_or_refund.html | 24 | ||||
| -rwxr-xr-x | httemplate/search/report_agent_credit_payment.html | 30 |
7 files changed, 261 insertions, 8 deletions
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/bin/cust_pay-delete b/bin/cust_pay-delete new file mode 100644 index 000000000..e266d0daa --- /dev/null +++ b/bin/cust_pay-delete @@ -0,0 +1,33 @@ +#!/usr/bin/perl + +use FS::UID qw( adminsuidsetup ); +use FS::Record qw( qsearch ); +use FS::cust_pay; + +adminsuidsetup shift or die usage(); + +my $custnum = shift or die usage(); + +my $paid = shift or die usage(); + +my $min_paynum = shift or die usage(); + +my @cust_pay = qsearch({ + 'table' => 'cust_pay', + 'hashref' => { 'custnum' => $custnum, + 'paid' => $paid, + 'paynum' => { op=>'>=', value=>$min_paynum, }, + 'payinfo' => '', + }, +}); + +die "Delete ". scalar(@cust_pay). " payments?\n"; + +#foreach my $cust_pay (@cust_pay) { +# my $error = $cust_pay->delete; +# die $error if $error; +#} + +sub usage { + "Usage: cust_pay delete username custnum paid min_paynum\n"; +} 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 .= '</TR>'; } } + if ((@bytes_svcs > 1) and (grep { $bytes_show{$_.'_used'} } qw(seconds upbytes downbytes totalbytes) )) { + $OUT .= '<TR>'; + $OUT .= '<TH align="left">Total Used</TH>'; + foreach my $field (@bytes_cols) { + if ($bytes_show{$field}) { + $OUT .= '<TD ALIGN="right">'; + $OUT .= $bytes_used_total{$field} || '0' if $field =~ /_used$/; + $OUT .= '</TD>'; + } + } + $OUT .= '</TR>'; + } %> <%= scalar(@bytes_svcs) ? '</TABLE><BR><BR>' : '' %> 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 => <<END, +<P>Shows agent commission credits, and payments applied to invoices for packages that triggered those credits.</P> +<STYLE SCOPED> +td.creditcell { background-color: #ffff99; } +td.paycell { background-color: #66ff66; } +</STYLE> +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 = <<END; + LEFT JOIN agent ON ( cust_credit.commission_agentnum = agent.agentnum ) + LEFT JOIN cust_pkg ON ( cust_credit.commission_pkgnum = cust_pkg.pkgnum ) + LEFT JOIN part_pkg ON ( cust_pkg.pkgpart = part_pkg.pkgpart ) +END + +my $query = <<END; +SELECT DISTINCT + agent, + cust_pkg.custnum AS xcustnum, + cust_credit.commission_pkgnum AS xpkgnum, + pkg, + 'cust_pay' AS xtable, + cust_pay.paynum AS xnum, + to_timestamp(cust_pay._date) AS xdate, + cust_pay.paid AS xamount, + cust_pay.order_number AS order_number +FROM cust_pay + INNER JOIN cust_bill_pay ON ( cust_pay.paynum = cust_bill_pay.paynum ) + INNER JOIN cust_bill_pkg ON ( cust_bill_pay.invnum = cust_bill_pkg.invnum ) + INNER JOIN cust_credit ON ( cust_bill_pkg.pkgnum = cust_credit.commission_pkgnum ) +$join +WHERE cust_credit.commission_pkgnum IS NOT NULL + AND cust_pay._date >= $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 }; + +</%init> 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' ) { 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' &> + +<P>Shows agent commission credits, and payments applied to invoices for packages that triggered those credits.</P> + +<FORM ACTION="agent_credit_payment.html" METHOD="GET"> + + <TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <& /elements/tr-select-agent.html, + 'curr_value' => scalar( $cgi->param('agentnum') ), + 'label' => 'Agent ', + 'disable_empty' => 0, + &> + + <& /elements/tr-input-beginning_ending.html &> + + </TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Get Report"> + +</FORM> + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +</%init> |