# @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;
'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' ) {
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 }
--- /dev/null
+#!/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";
+}
$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>' : '' %>
'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';
--- /dev/null
+<& 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>
#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]' ".
" 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' ) {
$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' ".
" 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' ".
" 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' ) {
--- /dev/null
+<& /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>
projects / freeside.git / commitdiff