diff options
| -rw-r--r-- | Changes.1.5.8 | 3 | ||||
| -rw-r--r-- | FS/FS/UI/Web.pm | 18 | ||||
| -rw-r--r-- | FS/FS/agent.pm | 48 | ||||
| -rw-r--r-- | FS/FS/cust_pkg.pm | 113 | ||||
| -rw-r--r-- | FS/FS/cust_svc.pm | 3 | ||||
| -rwxr-xr-x | httemplate/browse/agent.cgi | 42 | ||||
| -rwxr-xr-x | httemplate/search/cust_pay.cgi | 26 | ||||
| -rwxr-xr-x | httemplate/search/cust_pkg.cgi | 517 | ||||
| -rw-r--r-- | httemplate/search/elements/search.html | 59 |
9 files changed, 470 insertions, 359 deletions
diff --git a/Changes.1.5.8 b/Changes.1.5.8 index 113078bb2..06b512a8a 100644 --- a/Changes.1.5.8 +++ b/Changes.1.5.8 @@ -1,2 +1,5 @@ - move account search (httemplate/search/svc_acct.cgi) to new template - cust-fields configuration value to control which customer fields are shown on reports +- add unlinked mail forward (svc_forward) report +- move cust_pkg search (httemplate/search/cust_pkg.cgi) to new template +- add active/suspended/cancelled customer packages to agent browse diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm index 031a0e67a..85b85081a 100644 --- a/FS/FS/UI/Web.pm +++ b/FS/FS/UI/Web.pm @@ -12,12 +12,20 @@ use Date::Parse; sub parse_beginning_ending { my($cgi) = @_; - $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/; - my $beginning = str2time($1) || 0; + my $beginning = 0; + if ( $cgi->param('begin') =~ /^(\d+)$/ ) { + $beginning = $1; + } elsif ( $cgi->param('beginning') =~ /^([ 0-9\-\/]{1,64})$/ ) { + $beginning = str2time($1) || 0; + } - #need an option to turn off the + 86399 ??? - $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/; - my $ending = ( $1 ? str2time($1) : 4294880896 ) + 86399; + my $ending = 4294967295; #2^32-1 + if ( $cgi->param('end') =~ /^(\d+)$/ ) { + $ending = $1 - 1; + } elsif ( $cgi->param('ending') =~ /^([ 0-9\-\/]{1,64})$/ ) { + #probably need an option to turn off the + 86399 + my $ending = str2time($1) + 86399; + } ( $beginning, $ending ); } diff --git a/FS/FS/agent.pm b/FS/FS/agent.pm index fc1d1a93e..0a0e4f7ba 100644 --- a/FS/FS/agent.pm +++ b/FS/FS/agent.pm @@ -4,6 +4,7 @@ use strict; use vars qw( @ISA ); use FS::Record qw( dbh qsearch qsearchs ); use FS::cust_main; +use FS::cust_pkg; use FS::agent_type; use FS::reg_code; #use Crypt::YAPassGen; @@ -179,10 +180,9 @@ sub num_prospect_cust_main { sub num_sql { my( $self, $sql ) = @_; - my $sth = dbh->prepare( - "SELECT COUNT(*) FROM cust_main WHERE agentnum = ? AND $sql" - ) or die dbh->errstr; - $sth->execute($self->agentnum) or die $sth->errstr; + my $statement = "SELECT COUNT(*) FROM cust_main WHERE agentnum = ? AND $sql"; + my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; + $sth->execute($self->agentnum) or die $sth->errstr. "executing $statement"; $sth->fetchrow_arrayref->[0]; } @@ -266,6 +266,46 @@ sub cancel_cust_main { shift->cust_main_sql(FS::cust_main->cancel_sql); } +=item num_active_cust_pkg + +Returns the number of active customer packages for this agent. + +=cut + +sub num_active_cust_pkg { + shift->num_pkg_sql(FS::cust_pkg->active_sql); +} + +sub num_pkg_sql { + my( $self, $sql ) = @_; + my $statement = + "SELECT COUNT(*) FROM cust_pkg LEFT JOIN cust_main USING ( custnum )". + " WHERE agentnum = ? AND $sql"; + my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; + $sth->execute($self->agentnum) or die $sth->errstr. "executing $statement"; + $sth->fetchrow_arrayref->[0]; +} + +=item num_susp_cust_pkg + +Returns the number of suspended customer packages for this agent. + +=cut + +sub num_susp_cust_pkg { + shift->num_pkg_sql(FS::cust_pkg->susp_sql); +} + +=item num_cancel_cust_pkg + +Returns the number of cancelled customer packages for this agent. + +=cut + +sub num_cancel_cust_pkg { + shift->num_pkg_sql(FS::cust_pkg->cancel_sql); +} + =item generate_reg_codes NUM PKGPART_ARRAYREF Generates the specified number of registration codes, allowing purchase of the diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index f5c1de3e2..0b1a291db 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -3,8 +3,9 @@ package FS::cust_pkg; use strict; use vars qw(@ISA $disable_agentcheck @SVCDB_CANCEL_SEQ $DEBUG); use FS::UID qw( getotaker dbh ); -use FS::Record qw( qsearch qsearchs ); use FS::Misc qw( send_email ); +use FS::Record qw( qsearch qsearchs ); +use FS::cust_main_Mixin; use FS::cust_svc; use FS::part_pkg; use FS::cust_main; @@ -25,7 +26,7 @@ use FS::svc_forward; # for sending cancel emails in sub cancel use FS::Conf; -@ISA = qw( FS::Record ); +@ISA = qw( FS::cust_main_Mixin FS::Record ); $DEBUG = 0; @@ -141,6 +142,12 @@ Create a new billing item. To add the item to the database, see L<"insert">. =cut sub table { 'cust_pkg'; } +sub cust_linked { $_[0]->cust_main_custnum; } +sub cust_unlinked_msg { + my $self = shift; + "WARNING: can't find cust_main.custnum ". $self->custnum. + ' (cust_pkg.pkgnum '. $self->pkgnum. ')'; +} =item insert [ OPTION => VALUE ... ] @@ -748,6 +755,54 @@ sub available_part_svc { $self->part_pkg->pkg_svc; } +=item status + +Returns a short status string for this package, currently: + +=over 4 + +=item not yet billed + +=item one-time charge + +=item active + +=item suspended + +=item cancelled + +=back + +=cut + +sub status { + my $self = shift; + + return 'cancelled' if $self->get('cancel'); + return 'suspended' if $self->susp; + return 'not yet billed' unless $self->setup; + return 'one-time charge' if $self->part_pkg->freq =~ /^(0|$)/; + return 'active'; +} + +=item statuscolor + +Returns a hex triplet color string for this package's status. + +=cut + +my %statuscolor = ( + 'not yet billed' => '000000', + 'one-time charge' => '000000', + 'active' => '00CC00', + 'suspended' => 'FF9900', + 'cancelled' => 'FF0000', +); +sub statuscolor { + my $self = shift; + $statuscolor{$self->status}; +} + =item labels Returns a list of lists, calling the label method for all services @@ -1062,6 +1117,60 @@ sub reexport { =back +=head1 CLASS METHOD + +=over 4 + +=item recurring_sql + +Returns an SQL expression identifying recurring packages. + +=cut + +sub recurring_sql { " + '0' != ( select freq from part_pkg + where cust_pkg.pkgpart = part_pkg.pkgpart ) +"; } + +=item active_sql + +Returns an SQL expression identifying active packages. + +=cut + +sub active_sql { " + ". $_[0]->recurring_sql(). " + AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) + AND ( cust_pkg.susp IS NULL OR cust_pkg.susp = 0 ) +"; } + +=item susp_sql +=item suspended_sql + +Returns an SQL expression identifying suspended packages. + +=cut + +sub suspended_sql { susp_sql(@_); } +sub susp_sql { " + ". $_[0]->recurring_sql(). " + AND ( cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0 ) + AND cust_pkg.susp IS NOT NULL AND cust_pkg.susp != 0 +"; } + +=item cancel_sql +=item cancelled_sql + +Returns an SQL exprression identifying cancelled packages. + +=cut + +sub cancelled_sql { cancel_sql(@_); } +sub cancel_sql { " + ". $_[0]->recurring_sql(). " + AND cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0 +"; } + =head1 SUBROUTINES =over 4 diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm index 86894f791..2e349f6bd 100644 --- a/FS/FS/cust_svc.pm +++ b/FS/FS/cust_svc.pm @@ -274,6 +274,7 @@ Returns a list consisting of: - The name of this service (from part_svc) - A meaningful identifier (username, domain, or mail alias) - The table name (i.e. svc_domain) for this service +- svcnum =cut @@ -326,7 +327,7 @@ sub _svc_label { $tag = $svc_x->getfield('svcnum'); } - $self->part_svc->svc, $tag, $svcdb; + $self->part_svc->svc, $tag, $svcdb, $self->svcnum; } diff --git a/httemplate/browse/agent.cgi b/httemplate/browse/agent.cgi index c5c992704..fd360ab3c 100755 --- a/httemplate/browse/agent.cgi +++ b/httemplate/browse/agent.cgi @@ -32,6 +32,7 @@ full offerings (via their type).<BR><BR> <TH COLSPAN=<%= ( $cgi->param('showdisabled') || !dbdef->table('agent')->column('disabled') ) ? 2 : 3 %>>Agent</TH> <TH>Type</TH> <TH>Customers</TH> + <TH><FONT SIZE=-1>Customer<BR>packages</FONT></TH> <TH>Reports</TH> <TH>Registration codes</TH> <TH>Prepaid cards</TH> @@ -50,6 +51,8 @@ foreach my $agent ( sort { my $cust_main_link = $p. 'search/cust_main.cgi?agentnum_on=1&'. 'agentnum='. $agent->agentnum; + my $cust_pkg_link = $p. 'search/cust_pkg.cgi?agentnum='. $agent->agentnum; + %> <TR> @@ -65,7 +68,6 @@ foreach my $agent ( sort { <TD><A HREF="<%=$p%>edit/agent_type.cgi?<%= $agent->typenum %>"><%= $agent->agent_type->atype %></A></TD> <TD> - <TABLE CELLSPACING=0 CELLPADDING=0> <TR> <TH ALIGN="right" WIDTH="40%"> @@ -113,6 +115,44 @@ foreach my $agent ( sort { </TD> <TD> + <TABLE CELLSPACING=0 CELLPADDING=0> + <TR> + <TH ALIGN="right" WIDTH="40%"> + <FONT COLOR="#00CC00"> + <%= my $num_active_pkg = $agent->num_active_cust_pkg %> + </FONT> + </TH> + <TD> + <% if ( $num_active_pkg ) { %> + <A HREF="<%= $cust_pkg_link %>&magic=active"><% } %>active<% if ( $num_active_pkg ) { %></A><% } %> + </TD> + </TR> + <TR> + <TH ALIGN="right" WIDTH="40%"> + <FONT COLOR="#FF9900"> + <%= my $num_susp_pkg = $agent->num_susp_cust_pkg %> + </FONT> + </TH> + <TD> + <% if ( $num_susp_pkg ) { %> + <A HREF="<%= $cust_pkg_link %>&magic=suspended"><% } %>suspended<% if ( $num_susp_pkg ) { %></A><% } %> + </TD> + </TR> + <TR> + <TH ALIGN="right" WIDTH="40%"> + <FONT COLOR="#FF0000"> + <%= my $num_cancel_pkg = $agent->num_cancel_cust_pkg %> + </FONT> + </TH> + <TD> + <% if ( $num_cancel_pkg ) { %> + <A HREF="<%= $cust_pkg_link %>&magic=cancelled"><% } %>cancelled<% if ( $num_cancel_pkg ) { %></A><% } %> + </TD> + </TR> + </TABLE> + </TD> + + <TD> <A HREF="<%= $p %>search/report_cust_pay.html?agentnum=<%= $agent->agentnum %>">Payments</A> <BR><A HREF="<%= $p %>search/report_cust_credit.html?agentnum=<%= $agent->agentnum %>">Credits</A> <BR><A HREF="<%= $p %>search/report_receivables.cgi?agentnum=<%= $agent->agentnum %>">A/R Aging</A> diff --git a/httemplate/search/cust_pay.cgi b/httemplate/search/cust_pay.cgi index a680479f3..da3d12523 100755 --- a/httemplate/search/cust_pay.cgi +++ b/httemplate/search/cust_pay.cgi @@ -41,27 +41,11 @@ } } } - - #false laziness with cust_pkg.cgi - if ( $cgi->param('beginning') - && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { - my $beginning = str2time($1); - push @search, "_date >= $beginning "; - } - if ( $cgi->param('ending') - && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { - my $ending = str2time($1) + 86399; - push @search, " _date <= $ending "; - } - if ( $cgi->param('begin') - && $cgi->param('begin') =~ /^(\d+)$/ ) { - push @search, "_date >= $1 "; - } - if ( $cgi->param('end') - && $cgi->param('end') =~ /^(\d+)$/ ) { - push @search, " _date < $1 "; - } - + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + push @search, "_date >= $beginning ", + "_date <= $ending"; + my $search = ''; if ( @search ) { $search = ' WHERE '. join(' AND ', @search); diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 6d26317e0..5da4d82fb 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,363 +1,234 @@ <% -my $conf = new FS::Conf; -my $maxrecords = $conf->config('maxsearchrecordsperpage'); - my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); -my $limit = ''; -$limit .= "LIMIT $maxrecords" if $maxrecords; - -my $offset = $cgi->param('offset') || 0; -$limit .= " OFFSET $offset" if $offset; - -my $total; - my($query) = $cgi->keywords; -my $sortby; -my @cust_pkg; -if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { - $sortby=\*bill_sort; - - #false laziness with cust_pay.cgi - my $range = ''; - if ( $cgi->param('beginning') - && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { - my $beginning = str2time($1); - $range = " WHERE bill >= $beginning "; - } - if ( $cgi->param('ending') - && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { - my $ending = str2time($1) + 86399; - $range .= ( $range ? ' AND ' : ' WHERE ' ). " bill <= $ending "; - } +my $orderby; +my @where; +my $cjoin = ''; - $range .= ( $range ? 'AND ' : ' WHERE ' ). '( cancel IS NULL OR cancel = 0 )'; +if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { + $cjoin = "LEFT JOIN cust_main USING ( custnum )"; + push @where, + "agentnum = $1"; +} - if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { - $range .= ( $range ? 'AND ' : ' WHERE ' ). - "$1 = ( SELECT agentnum FROM cust_main". - " WHERE cust_main.custnum = cust_pkg.custnum )"; - } +if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { + $orderby = 'ORDER BY bill'; - #false laziness with below - my $statement = "SELECT COUNT(*) FROM cust_pkg $range"; - warn $statement; - my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; - $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; - - $total = $sth->fetchrow_arrayref->[0]; - - @cust_pkg = qsearch('cust_pkg',{}, '', " $range ORDER BY bill $limit" ); + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + push @where, + "bill >= $beginning ", + "bill <= $ending", + '( cancel IS NULL OR cancel = 0 )'; } else { - my $qual = ''; if ( $cgi->param('magic') && - $cgi->param('magic') =~ /^(active|suspended|canceled)$/ + $cgi->param('magic') =~ /^(active|suspended|cancell?ed)$/ ) { + $orderby = 'ORDER BY pkgnum'; + if ( $cgi->param('magic') eq 'active' ) { - $qual = 'WHERE ( susp IS NULL OR susp = 0 )'. - ' AND ( cancel IS NULL OR cancel = 0)'; + + #push @where, + # '( susp IS NULL OR susp = 0 )', + # '( cancel IS NULL OR cancel = 0)'; + push @where, FS::cust_pkg->active_sql(); + } elsif ( $cgi->param('magic') eq 'suspended' ) { - $qual = 'WHERE susp IS NOT NULL AND susp != 0'. - ' AND ( cancel IS NULL OR cancel = 0)'; - } elsif ( $cgi->param('magic') eq 'canceled' ) { - $qual = 'WHERE cancel IS NOT NULL AND cancel != 0'; + + push @where, + 'susp IS NOT NULL', + 'susp != 0', + '( cancel IS NULL OR cancel = 0)'; + + } elsif ( $cgi->param('magic') =~ /^cancell?ed$/ ) { + + push @where, + 'cancel IS NOT NULL', + 'cancel != 0'; + } else { die "guru meditation #420"; } - $sortby = \*pkgnum_sort; - if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { - $qual .= " AND pkgpart = $1"; + push @where, "pkgpart = $1"; } } elsif ( $query eq 'pkgnum' ) { - $sortby=\*pkgnum_sort; + $orderby = 'ORDER BY pkgnum'; } elsif ( $query eq 'APKG_pkgnum' ) { - $sortby=\*pkgnum_sort; - - #@cust_pkg=(); - ##perhaps this should go in cust_pkg as a qsearch-like constructor? - #my($cust_pkg); - #foreach $cust_pkg ( - # qsearch('cust_pkg',{}, '', "ORDER BY pkgnum $limit" ) - #) { - # my($flag)=0; - # my($pkg_svc); - # PKG_SVC: - # foreach $pkg_svc (qsearch('pkg_svc',{ 'pkgpart' => $cust_pkg->pkgpart })) { - # if ( $pkg_svc->quantity - # > scalar(qsearch('cust_svc',{ - # 'pkgnum' => $cust_pkg->pkgnum, - # 'svcpart' => $pkg_svc->svcpart, - # })) - # ) - # { - # $flag=1; - # last PKG_SVC; - # } - # } - # push @cust_pkg, $cust_pkg if $flag; - #} - - if ( driver_name eq 'mysql' ) { - #$query = "DROP TABLE temp1_$$,temp2_$$;"; - #my $sth = dbh->prepare($query); - #$sth->execute; - - $query = "CREATE TEMPORARY TABLE temp1_$$ TYPE=MYISAM - SELECT cust_svc.pkgnum,cust_svc.svcpart,COUNT(*) as count - FROM cust_pkg,cust_svc,pkg_svc - WHERE cust_pkg.pkgnum = cust_svc.pkgnum - AND cust_svc.svcpart = pkg_svc.svcpart - AND cust_pkg.pkgpart = pkg_svc.pkgpart - GROUP BY cust_svc.pkgnum,cust_svc.svcpart"; - my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; - - $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + $orderby = 'ORDER BY pkgnum'; - $query = "CREATE TEMPORARY TABLE temp2_$$ TYPE=MYISAM - SELECT cust_pkg.pkgnum FROM cust_pkg - LEFT JOIN pkg_svc ON (cust_pkg.pkgpart=pkg_svc.pkgpart) - LEFT JOIN temp1_$$ ON (cust_pkg.pkgnum = temp1_$$.pkgnum - AND pkg_svc.svcpart=temp1_$$.svcpart) - WHERE ( pkg_svc.quantity > temp1_$$.count - OR temp1_$$.pkgnum IS NULL ) - AND pkg_svc.quantity != 0;"; - $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; - $sth->execute or die "Error executing \"$query\": ". $sth->errstr; - $qual = " LEFT JOIN temp2_$$ ON cust_pkg.pkgnum = temp2_$$.pkgnum - WHERE temp2_$$.pkgnum IS NOT NULL"; - - } else { - - $qual = " - WHERE 0 < - ( SELECT count(*) FROM pkg_svc - WHERE pkg_svc.pkgpart = cust_pkg.pkgpart - AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc - WHERE cust_svc.pkgnum = cust_pkg.pkgnum - AND cust_svc.svcpart = pkg_svc.svcpart - ) - ) - "; - - } + push @where, '0 < ( + SELECT count(*) FROM pkg_svc + WHERE pkg_svc.pkgpart = cust_pkg.pkgpart + AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc + WHERE cust_svc.pkgnum = cust_pkg.pkgnum + AND cust_svc.svcpart = pkg_svc.svcpart + ) + )'; } else { die "Empty or unknown QUERY_STRING!"; } - - my $statement = "SELECT COUNT(*) FROM cust_pkg $qual"; - my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; - $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; - - $total = $sth->fetchrow_arrayref->[0]; - - my $tblname = driver_name eq 'mysql' ? 'cust_pkg.' : ''; - @cust_pkg = - qsearch('cust_pkg',{}, '', "$qual ORDER BY ${tblname}pkgnum $limit" ); - - if ( driver_name eq 'mysql' ) { - $query = "DROP TABLE temp1_$$,temp2_$$;"; - my $sth = dbh->prepare($query) or die dbh->errstr. " doing $query"; - $sth->execute; # or die "Error executing \"$query\": ". $sth->errstr; - } - -} - -if ( scalar(@cust_pkg) == 1 ) { - print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum. - "#cust_pkg". $cust_pkg[0]->pkgnum ); - #exit; -} elsif ( scalar(@cust_pkg) == 0 ) { #error -%> -<!-- mason kludge --> -<% - eidiot("No packages found"); -} else { -%> -<!-- mason kludge --> -<% - $total ||= scalar(@cust_pkg); - - #begin pager - my $pager = ''; - if ( $total != scalar(@cust_pkg) && $maxrecords ) { - unless ( $offset == 0 ) { - $cgi->param('offset', $offset - $maxrecords); - $pager .= '<A HREF="'. $cgi->self_url. - '"><B><FONT SIZE="+1">Previous</FONT></B></A> '; - } - my $poff; - my $page; - for ( $poff = 0; $poff < $total; $poff += $maxrecords ) { - $page++; - if ( $offset == $poff ) { - $pager .= qq!<FONT SIZE="+2">$page</FONT> !; - } else { - $cgi->param('offset', $poff); - $pager .= qq!<A HREF="!. $cgi->self_url. qq!">$page</A> !; - } - } - unless ( $offset + $maxrecords > $total ) { - $cgi->param('offset', $offset + $maxrecords); - $pager .= '<A HREF="'. $cgi->self_url. - '"><B><FONT SIZE="+1">Next</FONT></B></A> '; - } - } - #end pager - - print header('Package Search Results',''), - "$total matching packages found<BR><BR>$pager", &table(), <<END; - <TR> - <TH>Package</TH> - <TH><FONT SIZE=-1>Setup</FONT></TH> -END - - print '<TH><FONT SIZE=-1>Last<BR>bill</FONT></TH>' - if defined dbdef->table('cust_pkg')->column('last_bill'); - - print <<END; - <TH><FONT SIZE=-1>Next<BR>bill</FONT></TH> - <TH><FONT SIZE=-1>Susp.</FONT></TH> - <TH><FONT SIZE=-1>Expire</FONT></TH> - <TH><FONT SIZE=-1>Cancel</FONT></TH> - <TH><FONT SIZE=-1>Cust#</FONT></TH> - <TH>(bill) name</TH> - <TH>company</TH> -END - - print '<TH>(service) name</TH><TH>company</TH>' - if defined dbdef->table('cust_main')->column('ship_last'); - - print '<TH COLSPAN=2>Services</TH></TR>'; - - my $n1 = '<TR>'; - my(%saw,$cust_pkg); - foreach $cust_pkg ( - sort $sortby grep(!$saw{$_->pkgnum}++, @cust_pkg) - ) { - my($cust_main)=qsearchs('cust_main',{'custnum'=>$cust_pkg->custnum}); - my($pkgnum, $setup, $bill, $susp, $expire, $cancel, - $custnum, $last, $first, $company ) = ( - $cust_pkg->pkgnum, - $cust_pkg->getfield('setup') - ? time2str("%D", $cust_pkg->getfield('setup') ) - : '', - $cust_pkg->getfield('bill') - ? time2str("%D", $cust_pkg->getfield('bill') ) - : '', - $cust_pkg->getfield('susp') - ? time2str("%D", $cust_pkg->getfield('susp') ) - : '', - $cust_pkg->getfield('expire') - ? time2str("%D", $cust_pkg->getfield('expire') ) - : '', - $cust_pkg->getfield('cancel') - ? time2str("%D", $cust_pkg->getfield('cancel') ) - : '', - $cust_pkg->custnum, - $cust_main ? $cust_main->last : '', - $cust_main ? $cust_main->first : '', - $cust_main ? $cust_main->company : '', - ); - - my $last_bill = $cust_pkg->getfield('last_bill') - ? time2str("%D", $cust_pkg->getfield('last_bill') ) - : '' - if defined dbdef->table('cust_pkg')->column('last_bill'); - - my($ship_last, $ship_first, $ship_company); - if ( defined dbdef->table('cust_main')->column('ship_last') ) { - ($ship_last, $ship_first, $ship_company) = ( - $cust_main - ? ( $cust_main->ship_last || $cust_main->getfield('last') ) - : '', - $cust_main - ? ( $cust_main->ship_last - ? $cust_main->ship_first - : $cust_main->first ) - : '', - $cust_main - ? ( $cust_main->ship_last - ? $cust_main->ship_company - : $cust_main->company ) - : '', - ); - } - my $pkg = $part_pkg{$cust_pkg->pkgpart}->pkg; - #$pkg .= ' - '. $part_pkg{$cust_pkg->pkgpart}->comment; - my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } ); - my $rowspan = scalar(@cust_svc) || 1; - my $p = popurl(2); - print $n1, <<END; - <TD ROWSPAN=$rowspan><A HREF="${p}view/cust_main.cgi?$custnum#cust_pkg$pkgnum"><FONT SIZE=-1>$pkgnum - $pkg</FONT></A></TD> - <TD ROWSPAN=$rowspan>$setup</TD> -END - - print "<TD ROWSPAN=$rowspan>$last_bill</TD>" - if defined dbdef->table('cust_pkg')->column('last_bill'); - - print <<END; - <TD ROWSPAN=$rowspan>$bill</TD> - <TD ROWSPAN=$rowspan>$susp</TD> - <TD ROWSPAN=$rowspan>$expire</TD> - <TD ROWSPAN=$rowspan>$cancel</TD> -END - if ( $cust_main ) { - print <<END; - <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$custnum</A></FONT></TD> - <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$last, $first</A></FONT></TD> - <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$company</A></FONT></TD> -END - if ( defined dbdef->table('cust_main')->column('ship_last') ) { - print <<END; - <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$ship_last, $ship_first</A></FONT></TD> - <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$ship_company</A></FONT></TD> -END - } - } else { - my $colspan = defined dbdef->table('cust_main')->column('ship_last') - ? 5 : 3; - print <<END; - <TD ROWSPAN=$rowspan COLSPAN=$colspan>WARNING: couldn't find cust_main.custnum $custnum (cust_pkg.pkgnum $pkgnum)</TD> -END - } - - my $n2 = ''; - foreach my $cust_svc ( @cust_svc ) { - my($label, $value, $svcdb) = $cust_svc->label; - my $svcnum = $cust_svc->svcnum; - my $sview = $p. "view"; - print $n2,qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!, - qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!; - $n2="</TR><TR>"; - } - - $n1 = "</TR><TR>"; - - } - print '</TR>'; - - print "</TABLE>$pager</BODY></HTML>"; - -} -sub pkgnum_sort { - $a->getfield('pkgnum') <=> $b->getfield('pkgnum'); } -sub bill_sort { - $a->getfield('bill') <=> $b->getfield('bill'); +my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; + +my $count_query = "SELECT COUNT(*) FROM cust_pkg $cjoin $extra_sql"; + +my $sql_query = { + 'table' => 'cust_pkg', + 'hashref' => {}, + 'select' => join(', ', + 'cust_pkg.*', + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => "$extra_sql $orderby", + 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ', + #' LEFT JOIN part_pkg USING ( pkgpart ) ' +}; + +my $link = sub { + [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ]; +}; + +my $clink = sub { + my $cust_pkg = shift; + $cust_pkg->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +#if ( scalar(@cust_pkg) == 1 ) { +# print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum. +# "#cust_pkg". $cust_pkg[0]->pkgnum ); + +# my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } ); +# my $rowspan = scalar(@cust_svc) || 1; + +# my $n2 = ''; +# foreach my $cust_svc ( @cust_svc ) { +# my($label, $value, $svcdb) = $cust_svc->label; +# my $svcnum = $cust_svc->svcnum; +# my $sview = $p. "view"; +# print $n2,qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!, +# qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!; +# $n2="</TR><TR>"; +# } + +sub time_or_blank { + my $column = shift; + return sub { + my $record = shift; + my $value = $record->get($column); #mmm closures + $value ? time2str('%b %d %Y', $value ) : ''; + }; } +%><%= include( 'elements/search.html', + 'title' => 'Package Search Results', + 'name' => 'packages', + 'query' => $sql_query, + 'count_query' => $count_query, + 'redirect' => $link, + 'header' => [ '#', + 'Package', + 'Status', + 'Freq.', + 'Setup', + 'Last bill', + 'Next bill', + 'Susp.', + 'Expire', + 'Cancel', + FS::UI::Web::cust_header(), + 'Services', + ], + 'fields' => [ + 'pkgnum', + sub { my $part_pkg = $part_pkg{shift->pkgpart}; + $part_pkg->pkg; # ' - '. $part_pkg->comment; + }, + sub { ucfirst(shift->status); }, + sub { #shift->part_pkg->freq_pretty; + my $part_pkg = $part_pkg{shift->pkgpart}; + $part_pkg->freq_pretty; + }, + + #sub { time2str('%b %d %Y', shift->setup); }, + #sub { time2str('%b %d %Y', shift->last_bill); }, + #sub { time2str('%b %d %Y', shift->bill); }, + #sub { time2str('%b %d %Y', shift->susp); }, + #sub { time2str('%b %d %Y', shift->expire); }, + #sub { time2str('%b %d %Y', shift->get('cancel')); }, + ( map { time_or_blank($_) } + qw( setup last_bill bill susp expire cancel ) ), + + \&FS::UI::Web::cust_fields, + #sub { '<table border=0 cellspacing=0 cellpadding=0 STYLE="border:none">'. + # join('', map { '<tr><td align="right" style="border:none">'. $_->[0]. + # ':</td><td style="border:none">'. $_->[1]. '</td></tr>' } + # shift->labels + # ). + # '</table>'; + # }, + sub { + [ map { + [ + { 'data' => $_->[0]. ':', + 'align'=> 'right', + }, + { 'data' => $_->[1], + 'align'=> 'left', + 'link' => $p. 'view/' . + $_->[2]. '.cgi?'. $_->[3], + }, + ]; + } shift->labels + ]; + }, + ], + 'color' => [ + '', + '', + sub { shift->statuscolor; }, + '', + '', + '', + '', + '', + '', + '', + ( map { '' } FS::UI::Web::cust_header() ), + '', + ], + 'style' => [ '', '', 'b' ], + 'size' => [ '', '', '-1', ], + 'align' => 'rlclrrrrrr', + 'links' => [ + $link, + $link, + '', + '', + '', + '', + '', + '', + '', + '', + ( map { $clink } FS::UI::Web::cust_header() ), + '', + ], + ) %> diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 78754fe00..0f499d2d0 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -91,7 +91,12 @@ foreach my $field ( @{$opt{'fields'}} ) { if ( ref($field) eq 'CODE' ) { - push @line, &{$field}($row); + push @line, map { + ref($_) eq 'ARRAY' + ? '(N/A)' #unimplemented + : $_; + } + &{$field}($row); } else { push @line, $row->$field(); } @@ -153,7 +158,11 @@ #} if ( ref($field) eq 'CODE' ) { foreach my $value ( &{$field}($row) ) { - $worksheet->write($r, $c++, $value ); + if ( ref($value) eq 'ARRAY' ) { + $worksheet->write($r, $c++, '(N/A)' ); #unimplemented + } else { + $worksheet->write($r, $c++, $value ); + } } } else { $worksheet->write($r, $c++, $row->$field() ); @@ -258,6 +267,52 @@ foreach my $field ( map { + if ( ref($_) eq 'ARRAY' ) { + + my $tableref = $_; + + '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0'. + ' STYLE="border:none">'. + + join('', map { + + my $rowref = $_; + + '<tr>'. + + join('', map { + + my $element = $_; + + '<TD STYLE="border:none"'. + ( $element->{'align'} + ? ' ALIGN="'. $element->{'align'}. '"' + : '' + ). '>'. + ( $element->{'link'} + ? '<A HREF="'. $element->{'link'}.'">' + : '' + ). + $element->{'data'}. + ( $element->{'link'} + ? '</A>' + : '' + ). + '</td>'; + + } @$rowref ). + + '</tr>'; + } @$tableref ). + + '</table>'; + + } else { + $_; + } + } + + map { if ( ref($_) eq 'CODE' ) { &{$_}($row); } else { |