From b1fc20ef3b68a8536163fbb17c57bca15555f3c4 Mon Sep 17 00:00:00 2001 From: ivan Date: Fri, 4 Mar 2005 12:34:56 +0000 Subject: [PATCH] add agent selection to payment and credit reports, add link to agent browse, closes: Bug#1105 --- ANNOUNCE.1.5 | 5 ++++- FS/FS/Record.pm | 8 ++++---- FS/FS/h_Common.pm | 2 +- httemplate/browse/agent.cgi | 11 +++++++++++ httemplate/search/cust_credit.html | 24 ++++++++++++++++-------- httemplate/search/cust_pay.cgi | 27 +++++++++++++++++---------- httemplate/search/elements/search.html | 8 +++++--- httemplate/search/report_cust_credit.html | 10 ++++++++++ httemplate/search/report_cust_pay.html | 10 ++++++++++ 9 files changed, 78 insertions(+), 27 deletions(-) diff --git a/ANNOUNCE.1.5 b/ANNOUNCE.1.5 index a28a19326..a8322c4bd 100644 --- a/ANNOUNCE.1.5 +++ b/ANNOUNCE.1.5 @@ -28,10 +28,13 @@ - lots of RT integration, integrated RT upgraded to 3.2.2 - one-time referral credits - invoices now use history records (don't lose details) -- option to credit for remaining service upon cancel (peter bowen) +- option to credit for remaining service upon package cancel/change + (peter bowen) - one-time registration codes - "selfservice_server-session_module" config value can be set to "Cache::FileCache" on FreeBSD or elsewhere IPC::ShareLite has trouble. +- package changes don't re-charge setup fee +- per-agent payment and credit reports notyet (1.5.8?): - account merging UI in exports (for example, to consolidate passwd files from diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index 748026a13..e24c0eb9a 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -180,7 +180,7 @@ sub create { } } -=item qsearch TABLE, HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ, AS +=item qsearch TABLE, HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ, ADDL_FROM Searches the database for all records matching (at least) the key/value pairs in HASHREF. Returns all the records found as `FS::TABLE' objects if that @@ -199,7 +199,7 @@ objects. =cut sub qsearch { - my($stable, $record, $select, $extra_sql, $cache, $as ) = @_; + my($stable, $record, $select, $extra_sql, $cache, $addl_from ) = @_; #$stable =~ /^([\w\_]+)$/ or die "Illegal table: $table"; #for jsearch $stable =~ /^([\w\s\(\)\.\,\=]+)$/ or die "Illegal table: $stable"; @@ -223,7 +223,7 @@ sub qsearch { } my $statement = "SELECT $select FROM $stable"; - $statement .= " AS $as" if $as; + $statement .= " $addl_from" if $addl_from; if ( @real_fields or @virtual_fields ) { $statement .= ' WHERE '. join(' AND ', ( map { @@ -428,7 +428,7 @@ sub jsearch { ); } -=item qsearchs TABLE, HASHREF +=item qsearchs TABLE, HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ, ADDL_FROM Same as qsearch, except that if more than one record matches, it Bs but returns the first. If this happens, you either made a logic error in asking diff --git a/FS/FS/h_Common.pm b/FS/FS/h_Common.pm index a7bb937e5..52af42bfc 100644 --- a/FS/FS/h_Common.pm +++ b/FS/FS/h_Common.pm @@ -64,7 +64,7 @@ sub sql_h_search { '', - 'maintable', + 'AS maintable', ); } diff --git a/httemplate/browse/agent.cgi b/httemplate/browse/agent.cgi index f24266fb2..82f18bff7 100755 --- a/httemplate/browse/agent.cgi +++ b/httemplate/browse/agent.cgi @@ -35,6 +35,7 @@ full offerings (via their type).

param('showdisabled') || !dbdef->table('agent')->column('disabled') ) ? 2 : 3 %>>Agent Type Customers + Reports Registration Codes Freq. Prog. @@ -64,6 +65,7 @@ foreach my $agent ( sort { <%= $agent->agent %> <%= $agent->agent_type->atype %> + @@ -90,14 +92,23 @@ foreach my $agent ( sort { <% if ( $num_cancel ) { %> <% } %>cancelled<% if ( $num_cancel ) { %><% } %> + + + Payments +
Credits + + + <%= my $num_reg_code = $agent->num_reg_code %> <% if ( $num_reg_code ) { %> <% } %>Unused<% if ( $num_reg_code ) { %><% } %>
Generate codes + <%= $agent->freq %> <%= $agent->prog %> + <% } %> diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index faaa7a817..a3f88cc21 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -1,4 +1,5 @@ <% + my $title = 'Credit Search Results'; #my( $count_query, $sql_query ); my @search = (); @@ -7,6 +8,13 @@ push @search, "otaker = '$1'"; } + if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { + push @search, "agentnum = $1"; # $search{'agentnum'} = $1; + my $agent = qsearchs('agent', { 'agentnum' => $1 } ); + die "unknown agentnum $1" unless $agent; + $title = $agent->agent. " $title"; + } + #false laziness with cust_pkg.cgi and cust_pay.cgi if ( $cgi->param('beginning') && $cgi->param('beginning') =~ /^([ 0-9\-\/]{1,10})$/ ) { @@ -32,18 +40,22 @@ ? 'WHERE '. join(' AND ', @search) : ''; - my $count_query = "SELECT COUNT(*), SUM(amount) FROM cust_credit $where"; + my $count_query = 'SELECT COUNT(*), SUM(amount) '. + 'FROM cust_credit JOIN cust_main USING ( custnum ) '. + $where; + my $sql_query = { 'table' => 'cust_credit', 'hashref' => {}, 'extra_sql' => $where, + 'addl_from' => 'JOIN cust_main USING ( custnum )', }; my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; %> <%= include( 'elements/search.html', - 'title' => 'Credit Search Results', + 'title' => $title, 'name' => 'credits', 'query' => $sql_query, 'count_query' => $count_query, @@ -57,12 +69,8 @@ sub { sprintf('$%.2f', shift->amount ) }, sub { time2str('%b %d %Y', shift->_date ) }, 'custnum', - sub { my $cust_main = shift->cust_main; - $cust_main->get('last'). ', '. $cust_main->first; - }, - sub { my $cust_main = shift->cust_main; - $cust_main->company; - }, + sub { $_[0]->get('last'). ', '. $_[0]->first; }, + 'company', 'otaker', 'reason', ], diff --git a/httemplate/search/cust_pay.cgi b/httemplate/search/cust_pay.cgi index 3f5b72ab5..42b3c01bc 100755 --- a/httemplate/search/cust_pay.cgi +++ b/httemplate/search/cust_pay.cgi @@ -1,9 +1,17 @@ <% + my $title = 'Payment Search Results'; my( $count_query, $sql_query ); if ( $cgi->param('magic') && $cgi->param('magic') eq '_date' ) { - my %search; - my @search; + my %search = (); + my @search = (); + + if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { + push @search, "agentnum = $1"; # $search{'agentnum'} = $1; + my $agent = qsearchs('agent', { 'agentnum' => $1 } ); + die "unknown agentnum $1" unless $agent; + $title = $agent->agent. " $title"; + } if ( $cgi->param('payby') ) { $cgi->param('payby') =~ /^(CARD|CHEK|BILL)(-(VisaMC|Amex|Discover))?$/ @@ -59,14 +67,17 @@ } my $hsearch = join(' AND ', map { "$_ = '$search{$_}'" } keys %search ); - $count_query = "SELECT COUNT(*), SUM(paid) FROM cust_pay ". + $count_query = "SELECT COUNT(*), SUM(paid) ". + "FROM cust_pay JOIN cust_main USING ( custnum )". ( $hsearch ? " WHERE $hsearch " : '' ). $search; + warn join('-', keys %search); $sql_query = { 'table' => 'cust_pay', 'hashref' => \%search, 'extra_sql' => "$search ORDER BY _date", + 'addl_from' => 'JOIN cust_main USING ( custnum )', }; } else { @@ -93,7 +104,7 @@ %> <%= include( 'elements/search.html', - 'title' => 'Payment Search Results', + 'title' => $title, 'name' => 'payments', 'query' => $sql_query, 'count_query' => $count_query, @@ -117,12 +128,8 @@ sub { sprintf('$%.2f', shift->paid ) }, sub { time2str('%b %d %Y', shift->_date ) }, 'custnum', - sub { my $cust_main = shift->cust_main; - $cust_main->get('last'). ', '. $cust_main->first; - }, - sub { my $cust_main = shift->cust_main; - $cust_main->company; - }, + sub { $_[0]->get('last'). ', '. $_[0]->first; }, + 'company', ], 'align' => 'lrrrll', 'links' => [ diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 566ea8391..712d63a91 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -12,9 +12,9 @@ $opt{align} = [ map $align{$_}, split(//, $opt{align}) ], unless !$opt{align} || ref($opt{align}); - if ( ref($opt{'query'}) ) { - - } + #if ( ref($opt{'query'}) ) { + # + #} unless (exists($opt{'count_query'}) && length($opt{'count_query'})) { ( $opt{'count_query'} = $opt{'query'} ) =~ @@ -47,6 +47,8 @@ $opt{'query'}->{'hashref'} || {}, $opt{'query'}->{'select'}, $opt{'query'}->{'extra_sql'}. " $limit", + '', + (exists($opt{'query'}->{'addl_from'}) ? $opt{'query'}->{'addl_from'} : '') ) ]; } else { my $sth = dbh->prepare("$opt{'query'} $limit") diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html index ceffca75d..b614e87b2 100644 --- a/httemplate/search/report_cust_credit.html +++ b/httemplate/search/report_cust_credit.html @@ -28,6 +28,16 @@ + for agent: + + + + From:
m/d/y