From d453a487d95258e1f11f134fc5864f7e6458a6ef Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 12 Apr 2006 12:36:39 +0000 Subject: [PATCH] zip code report --- FS/FS/cust_main.pm | 22 +++++++ httemplate/index.html | 65 +++----------------- httemplate/search/cust_main-zip.html | 95 +++++++++++++++++++++++++++++ httemplate/search/cust_main.cgi | 14 +---- httemplate/search/elements/search.html | 14 ++++- httemplate/search/report_cust_main-zip.html | 46 ++++++++++++++ 6 files changed, 188 insertions(+), 68 deletions(-) create mode 100644 httemplate/search/cust_main-zip.html create mode 100644 httemplate/search/report_cust_main-zip.html diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index a2eb72473..06dcd0ccf 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -3711,6 +3711,27 @@ sub cancel_sql { " ) "; } +=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 @@ -3860,6 +3881,7 @@ sub rebuild_fuzzyfiles { use Fcntl qw(:flock); my $dir = $FS::UID::conf_dir. "cache.". $FS::UID::datasrc; + mkdir $dir, 0700 unless -d $dir; #last diff --git a/httemplate/index.html b/httemplate/index.html index b8f300d2d..89fe7fcae 100644 --- a/httemplate/index.html +++ b/httemplate/index.html @@ -1,55 +1,8 @@ - <% my $conf = new FS::Conf; %> - - - - Freeside Main Menu - - - - - - - - - - - - -
- freeside - - <%= $conf->config('company_name') %> Billing - Logged in as <%= getotaker %> -
- - - - - <% if ( $conf->config('ticket_system') eq 'RT_Internal' ) { %> - <% eval "use RT;"; %> - - - <% } %> - - -
- - Freeside v<%= $FS::VERSION %>
- Documentation
- - 		- - RT v<%= $RT::VERSION %>
- Documentation
- -
- -
+<%= include('/elements/header.html', 'Freeside Main Menu' ) %>
- [ Sales / Customer service ] <% if ( $conf->config('ticket_system') ) { %> [ Support / Ticketing ] @@ -57,7 +10,7 @@ [ Bookkeeping / Collections ] [ Reports ] [ Sysadmin ] - +
Sales / Customer service

New Customer @@ -88,7 +41,7 @@ [ Bookkeeping / Collections ] [ Reports ] [ Sysadmin ] - +
Support/Ticketing
<% if ( $conf->config('ticket_system') eq 'RT_Internal' ) { %> @@ -117,7 +70,7 @@ [ Bookkeeping / Collections ] [ Reports ] [ Sysadmin ] - +
Bookkeeping / Collections

Quick payment entry @@ -165,7 +118,7 @@ [ Bookkeeping / Collections ] [ Reports ] [ Sysadmin ] - +
Reports

@@ -190,6 +143,7 @@ + Zip code distribution

SQL query: SELECT

@@ -208,7 +162,7 @@ [ Bookkeeping / Collections ] [ Reports ] [ Sysadmin ] - +
+ <% } %> <% } %> diff --git a/httemplate/search/report_cust_main-zip.html b/httemplate/search/report_cust_main-zip.html new file mode 100644 index 000000000..30020f3aa --- /dev/null +++ b/httemplate/search/report_cust_main-zip.html @@ -0,0 +1,46 @@ +<%= include('/elements/header.html', 'Zip code report') %> + + + +
Sysadmin

@@ -289,5 +243,6 @@















































- - + +<%= include('/elements/footer.html') %> + diff --git a/httemplate/search/cust_main-zip.html b/httemplate/search/cust_main-zip.html new file mode 100644 index 000000000..333a1e0b1 --- /dev/null +++ b/httemplate/search/cust_main-zip.html @@ -0,0 +1,95 @@ +<% + +# 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'; } ], + ) +%> diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 665f5637d..36ad39da8 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -102,18 +102,8 @@ if ( $cgi->param('browse') && ! $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'); diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index b14bded10..7f7243588 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -68,6 +68,8 @@ # # or a coderef that returns the same # 'redirect' => + my $DEBUG = 0; + my(%opt) = @_; #warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n"; @@ -118,6 +120,7 @@ my $header = $opt{'header'}; my $rows; if ( ref($opt{'query'}) ) { + #eval "use FS::$opt{'query'};"; $rows = [ qsearch( $opt{'query'}->{'table'}, @@ -127,7 +130,9 @@ '', (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 @@ -137,8 +142,15 @@ $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 @@ -437,7 +449,7 @@ <% } %> <% } else { %> <% foreach ( @$row ) { %> - 		<%= $_ %><%= $_ %>
+ + + + + + + + + + + + + + + + + <%= include( '/elements/tr-select-agent.html', + $cgi->param('agentnum'), + 'label' => 'for agent: ', + ) + %> + +
Billing or service zip + +
Ignore +4 for US zip codes
Show customers with status: + +
+
+ + + -- 2.11.0