)
"; }
+=item uncancel_sql
+=item uncancelled_sql
+
+Returns an SQL expression identifying un-cancelled cust_main records.
+
+=cut
+
+sub uncancelled_sql { uncancel_sql(@_); }
+sub uncancel_sql { "
+ ( 0 < ( SELECT COUNT(*) FROM cust_pkg
+ WHERE cust_pkg.custnum = cust_main.custnum
+ AND ( cust_pkg.cancel IS NULL
+ OR cust_pkg.cancel = 0
+ )
+ )
+ OR 0 = ( SELECT COUNT(*) FROM cust_pkg
+ WHERE cust_pkg.custnum = cust_main.custnum
+ )
+ )
+"; }
+
=item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ]
Performs a fuzzy (approximate) search and returns the matching FS::cust_main
use Fcntl qw(:flock);
my $dir = $FS::UID::conf_dir. "cache.". $FS::UID::datasrc;
+ mkdir $dir, 0700 unless -d $dir;
#last
-<!-- mason kludge -->
<% my $conf = new FS::Conf; %>
-<HTML>
- <HEAD>
- <TITLE>
- Freeside Main Menu
- </TITLE>
- </HEAD>
- <BODY BGCOLOR="#FFFFFF">
- <table width="100%">
- <tr>
- <td rowspan=2>
- <IMG BORDER=0 ALT="freeside" SRC="images/small-logo.png">
- </td>
- <td align=left rowspan=2> <!-- valign="top" -->
- <font size=6><%= $conf->config('company_name') %> Billing</font>
- </td>
- <td align=right valign=top>Logged in as <b><%= getotaker %></b>
- </td>
- </tr>
- <tr>
- <td align=right valign=bottom>
-
- <table>
- <tr>
- <td align=right>
- <FONT SIZE="-2">
- <A HREF="http://www.sisd.com/freeside">Freeside</A> v<%= $FS::VERSION %><BR>
- <A HREF="docs/">Documentation</A><BR>
- </FONT>
- </td>
- <% if ( $conf->config('ticket_system') eq 'RT_Internal' ) { %>
- <% eval "use RT;"; %>
- <td bgcolor=#000000></td>
- <td align=left>
- <FONT SIZE="-2">
- <A HREF="http://www.bestpractical.com/rt">RT<A> v<%= $RT::VERSION %><BR>
- <A HREF="http://wiki.bestpractical.com/">Documentation</A><BR>
- </FONT>
- </td>
- <% } %>
-
- </tr>
- </table>
-
- </td>
- </tr>
- </table>
+<%= include('/elements/header.html', 'Freeside Main Menu' ) %>
<BR>
-
[<A NAME="customer_service" style="background-color: #cccccc"> Sales / Customer service </A>]
<% if ( $conf->config('ticket_system') ) { %>
[ <A HREF="#ticketing">Support / Ticketing</A> ]
[ <A HREF="#bookkeeping">Bookkeeping / Collections</A> ]
[ <A HREF="#reports">Reports</A> ]
[ <A HREF="#sysadmin">Sysadmin</A> ]
- <TABLE CELLSPACING=2 CELLPADDING=0 BORDER=0" WIDTH="100%" BGCOLOR="#eeeeee">
+ <TABLE CELLSPACING=2 CELLPADDING=0 BORDER=0" WIDTH="100%" BGCOLOR="#eeeeee" STYLE="border: 1px solid black">
<TR><TH BGCOLOR="#cccccc">Sales / Customer service</TH></TR>
<TR><TD>
<BR><FONT SIZE="+1"><A HREF="edit/cust_main.cgi">New Customer</A></FONT>
[ <A HREF="#bookkeeping">Bookkeeping / Collections</A> ]
[ <A HREF="#reports">Reports</A> ]
[ <A HREF="#sysadmin">Sysadmin</A> ]
- <TABLE CELLSPACING=2 CELLPADDING=0 BORDER=0" WIDTH="100%" BGCOLOR="#eeeeee">
+ <TABLE CELLSPACING=2 CELLPADDING=0 BORDER=0" WIDTH="100%" BGCOLOR="#eeeeee" STYLE="border: 1px solid black">
<TR><TH BGCOLOR="#cccccc">Support/Ticketing</TH></TR>
<TR><TD>
<% if ( $conf->config('ticket_system') eq 'RT_Internal' ) { %>
[<A NAME="bookkeeping" style="background-color: #cccccc"> Bookkeeping / Collections </A>]
[ <A HREF="#reports">Reports</A> ]
[ <A HREF="#sysadmin">Sysadmin</A> ]
- <TABLE CELLSPACING=2 CELLPADDING=0 BORDER=0 WIDTH="100%" BGCOLOR="#eeeeee">
+ <TABLE CELLSPACING=2 CELLPADDING=0 BORDER=0 WIDTH="100%" BGCOLOR="#eeeeee" STYLE="border: 1px solid black">
<TR><TH BGCOLOR="#cccccc">Bookkeeping / Collections</TH></TR>
<TR><TD>
<BR><A HREF="misc/batch-cust_pay.html">Quick payment entry</A>
[ <A HREF="#bookkeeping">Bookkeeping / Collections</A> ]
[<A NAME="reports" style="background-color: #cccccc"> Reports </A>]
[ <A HREF="#sysadmin">Sysadmin</A> ]
- <TABLE CELLSPACING=2 CELLPADDING=0 BORDER=0 WIDTH="100%" BGCOLOR="#eeeeee">
+ <TABLE CELLSPACING=2 CELLPADDING=0 BORDER=0 WIDTH="100%" BGCOLOR="#eeeeee" STYLE="border: 1px solid black">
<TR><TH BGCOLOR="#cccccc">Reports</TH></TR>
<TR><TD>
<BR>
<UL>
<LI><A HREF="search/cust_main-otaker.cgi">Search customers by ordering employee</A>
</UL>
+ <A HREF="search/report_cust_main-zip.html">Zip code distribution</A><BR><BR>
<FORM ACTION="search/sql.html" METHOD="GET">SQL query: <TT>SELECT </TT><INPUT TYPE="text" NAME="sql" SIZE=32><INPUT TYPE="submit" VALUE="Query"></FORM>
<BR>
[ <A HREF="#bookkeeping">Bookkeeping / Collections</A> ]
[ <A HREF="#reports">Reports</A> ]
[<A NAME="sysadmin" style="background-color: #cccccc"> Sysadmin </A>]
- <TABLE CELLSPACING=2 CELLPADDING=0 BORDER=0 WIDTH="100%" BGCOLOR="#eeeeee">
+ <TABLE CELLSPACING=2 CELLPADDING=0 BORDER=0 WIDTH="100%" BGCOLOR="#eeeeee" STYLE="border: 1px solid black">
<TR><TH BGCOLOR="#cccccc">Sysadmin</TH></TR>
<TR><TD>
<BR>
<BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR>
<BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR>
<BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR>
- </BODY>
-</HTML>
+
+<%= include('/elements/footer.html') %>
+
--- /dev/null
+<%
+
+# XXX link to customers
+
+my @where = ();
+
+# select status
+
+if ( $cgi->param('status') =~ /^(prospect|uncancel|active|susp|cancel)$/ ) {
+ my $method = $1.'_sql';
+ push @where, FS::cust_main->$method();
+}
+
+# select agent
+# XXX this needs to be virtualized by agent too (like lots of stuff)
+
+my $agentnum = '';
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ $agentnum = $1;
+ push @where, "cust_main.agentnum = $agentnum";
+}
+my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+
+# bill zip vs ship zip
+
+sub fieldorempty {
+ my $field = shift;
+ "CASE WHEN $field IS NULL THEN '' ELSE $field END";
+}
+
+sub strip_plus4 {
+ my $field = shift;
+ "CASE WHEN $field is NULL
+ THEN ''
+ ELSE CASE WHEN $field LIKE '_____-____'
+ THEN SUBSTRING($field FROM 1 FOR 5)
+ ELSE $field
+ END
+ END";
+}
+
+my( $zip, $czip);
+if ( $cgi->param('column') eq 'ship_zip' ) {
+
+ my $casewhen_noship =
+ "CASE WHEN ( ship_last IS NULL OR ship_last = '' ) THEN ";
+
+ $czip = "$casewhen_noship zip ELSE ship_zip END";
+
+ if ( $cgi->param('ignore_plus4') ) {
+ $zip = $casewhen_noship. strip_plus4('zip').
+ " ELSE ". strip_plus4('ship_zip'). ' END';
+
+ } else {
+ $zip = $casewhen_noship. fieldorempty('zip').
+ " ELSE ". fieldorempty('ship_zip'). ' END';
+ }
+
+} else {
+
+ $czip = 'zip';
+
+ if ( $cgi->param('ignore_plus4') ) {
+ $zip = strip_plus4('zip');
+ } else {
+ $zip = fieldorempty('zip');
+ }
+
+}
+
+# construct the queries and send 'em off
+
+my $sql_query =
+ "SELECT $zip AS zipcode,
+ COUNT(*) AS num_cust
+ FROM cust_main
+ $where
+ GROUP BY zipcode
+ ORDER BY num_cust DESC
+ ";
+
+my $count_sql = "select count(distinct $czip) from cust_main $where";
+
+# XXX should link...
+
+%><%= include( 'elements/search.html',
+ 'title' => 'Zip code Search Results',
+ 'name' => 'zip codes',
+ 'query' => $sql_query,
+ 'count_query' => $count_sql,
+ 'header' => [ 'Zip code', 'Customers', ],
+ #'fields' => [ 'zip', 'num_cust', ],
+ 'links' => [ '', sub { 'somewhere'; } ],
+ )
+%>
&& ! $cgi->param('showcancelledcustomers') )
) {
#grep { $_->ncancelled_pkgs || ! $_->all_pkgs }
- push @qual, "
- ( 0 < ( SELECT COUNT(*) FROM cust_pkg
- WHERE cust_pkg.custnum = cust_main.custnum
- AND ( cust_pkg.cancel IS NULL
- OR cust_pkg.cancel = 0
- )
- )
- OR 0 = ( SELECT COUNT(*) FROM cust_pkg
- WHERE cust_pkg.custnum = cust_main.custnum
- )
- )
- ";
+ push @qual, FS::cust_main->uncancel_sql;
+
}
push @qual, FS::cust_main->cancel_sql if $cgi->param('cancelled');
# # or a coderef that returns the same
# 'redirect' =>
+ my $DEBUG = 0;
+
my(%opt) = @_;
#warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n";
my $header = $opt{'header'};
my $rows;
if ( ref($opt{'query'}) ) {
+
#eval "use FS::$opt{'query'};";
$rows = [ qsearch(
$opt{'query'}->{'table'},
'',
(exists($opt{'query'}->{'addl_from'}) ? $opt{'query'}->{'addl_from'} : '')
) ];
+
} else {
+
my $sth = dbh->prepare("$opt{'query'} $limit")
or die "Error preparing $opt{'query'}: ". dbh->errstr;
$sth->execute
$rows = $sth->fetchall_arrayref;
$header ||= $sth->{NAME};
+
}
+ warn scalar(@$rows). ' rows returned from '.
+ ( ref($opt{'query'}) ? 'qsearch query' : 'literal SQL query' )
+ if $DEBUG || $opt{'debug'};
+
+ # display the results - csv, xls or html
+
if ( $type eq 'csv' ) {
#http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes
<% } %>
<% } else { %>
<% foreach ( @$row ) { %>
- <TD CLASS="grid" BGCOLOR="$bgcolor"><%= $_ %></TD>
+ <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $_ %></TD>
<% } %>
<% } %>
</TR>
--- /dev/null
+<%= include('/elements/header.html', 'Zip code report') %>
+
+ <FORM ACTION="cust_main-zip.html" METHOD="GET">
+
+ <TABLE>
+
+ <TR>
+ <TD ALIGN="right">Billing or service zip</TD>
+ <TD>
+ <SELECT NAME="column">
+ <OPTION VALUE="zip">Billing zip
+ <OPTION VALUE="ship_zip">Service zip
+ </SELECT>
+ </TD>
+ </TR>
+
+ <TR>
+ <TD ALIGN="right">Ignore +4 for US zip codes</TD>
+ <TD><INPUT TYPE="checkbox" NAME="ignore_plus4" VALUE="yes" CHECKED> </TD>
+ </TR>
+
+ <TR>
+ <TD ALIGN="right">Show customers with status:</TD>
+ <TD>
+ <SELECT NAME="status">
+ <OPTION VALUE="">all
+ <OPTION VALUE="prospect">prospect (no packages ever)
+ <OPTION SELECTED VALUE="uncancel">all except cancelled
+ <OPTION VALUE="active">active recurring packages
+ <OPTION VALUE="susp">suspended
+ <OPTION VALUE="cancel">cancelled
+ </SELECT>
+ </TD>
+ </TR>
+
+ <%= include( '/elements/tr-select-agent.html',
+ $cgi->param('agentnum'),
+ 'label' => 'for agent: ',
+ )
+ %>
+
+ </TABLE>
+ <BR><INPUT TYPE="submit" VALUE="Get Report">
+ </FORM>
+ </BODY>
+</HTML>
projects / freeside.git / commitdiff