diff options
| -rw-r--r-- | httemplate/elements/menu.html | 6 | ||||
| -rwxr-xr-x | httemplate/search/cust_main_ADV.cgi | 138 | ||||
| -rwxr-xr-x | httemplate/search/report_cust_main.html | 60 |
3 files changed, 204 insertions, 0 deletions
diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 7b39277c2..1d72bbbe6 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -66,6 +66,11 @@ tie my %report_customers_lists, 'Tie::IxHash', $report_customers_lists{'by active trouble tickets'} = [ $fsurl. 'search/cust_main.cgi?browse=tickets', '' ] if $conf->config('ticket_system'); +$report_customers_lists{'advanced customer reports'} = [ $fsurl. 'search/report_cust_main.html', '' ] + if ( $curuser->access_right('List customers') && + $curuser->access_right('List packages') + ); + tie my %report_customers_search, 'Tie::IxHash'; $report_customers_search{'by ordering employee'} = [ $fsurl. 'search/cust_main-otaker.cgi' ] if $curuser->access_right('Configuration'); @@ -191,6 +196,7 @@ $report_menu{'Financial'} = [ \%report_financial, 'Financial reports' ] tie my %tools_importing, 'Tie::IxHash', 'Import customers from CSV file' => [ $fsurl.'misc/cust_main-import.cgi', '' ], + 'Import payments from CSV file' => [ $fsurl.'misc/cust_pay-import.cgi', '' ], 'Import customer notes from CSV file' => [ $fsurl.'misc/cust_main_note-import.html', '' ], 'Import one-time charges from CSV file' => [ $fsurl.'misc/cust_main-import_charges.cgi', '' ], 'Import Call Detail Records (CDRs) from CSV file' => [ $fsurl.'misc/cdr-import.html', '' ], diff --git a/httemplate/search/cust_main_ADV.cgi b/httemplate/search/cust_main_ADV.cgi new file mode 100755 index 000000000..106b5965f --- /dev/null +++ b/httemplate/search/cust_main_ADV.cgi @@ -0,0 +1,138 @@ +<% include( 'elements/search.html', + 'title' => 'Customer Search Results', + 'name' => 'customers', + 'query' => $sql_query, + 'count_query' => $count_query, + 'header' => [ '#', + 'Name', + 'Address', + 'Phone', + 'Night', + 'Fax', + 'Email', + 'Payment Type', + @extra_headers, + ], + 'fields' => [ + 'custnum', + 'name', + sub { my $c = shift; + $c->address1 . + ($c->address2 ? ' '.$c->address2 : ''). + $c->city. ', '. $c->state. ' '. $c->zip. + ($c->country ne $countrydefault ? ' '. $c->country + : '' + ); + }, + 'daytime', + 'night', + 'fax', + 'email', + 'payby', + @extra_fields, + ], + ) +%> +<%init> + +die "access denied" + unless ( $FS::CurrentUser::CurrentUser->access_right('List customers') && + $FS::CurrentUser::CurrentUser->access_right('List packages') + ); + +my $dbh = dbh; +my $conf = new FS::Conf; +my $countrydefault = $conf->config('countrydefault'); + +my($query) = $cgi->keywords; + +my @where = (); + +## +# parse agent +## + +if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { + push @where, + "agentnum = $1"; +} + +## +# parse cancelled package checkbox +## + +my $pkgwhere = ""; + +$pkgwhere .= "AND (cancel = 0 or cancel is null)" + unless $cgi->param('cancelled_pkgs'); + +my $orderby; + +## +# dates +## + +foreach my $field (qw( signupdate )) { + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field); + + next if $beginning == 0 && $ending == 4294967295; + + push @where, + "cust_main.$field IS NOT NULL", + "cust_main.$field >= $beginning", + "cust_main.$field <= $ending"; + + $orderby ||= "ORDER BY cust_main.$field"; + +} + +## +# setup queries, subs, etc. for the search +## + +$orderby ||= 'ORDER BY custnum'; + +# here is the agent virtualization +push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; + +my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; + +my $addl_from = 'LEFT JOIN cust_pkg USING ( custnum ) '; + +my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql"; + +my $select; +if ($dbh->{Driver}->{Name} eq 'Pg') { + $select = "*, array_to_string(array(select pkg from cust_pkg left join part_pkg using ( pkgpart ) where cust_main.custnum = cust_pkg.custnum $pkgwhere),',') as magic"; +}elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) { + $select = "*, GROUP_CONCAT(pkg SEPARATOR ',') as magic"; +}else{ + warn "warning: unknown database type ". $dbh->{Driver}->{Name}. + "omitting packing information from report."; +} +my $sql_query = { + 'table' => 'cust_main', + 'select' => $select, + 'hashref' => {}, + 'extra_sql' => "$extra_sql $orderby", +}; + +my $header_query = "SELECT COUNT(cust_pkg.custnum = cust_main.custnum) AS count FROM cust_main $addl_from $extra_sql $pkgwhere group by cust_main.custnum order by count desc limit 1"; + +my $sth = dbh->prepare($header_query) or die dbh->errstr; +$sth->execute() or die $sth->errstr; +my $headerrow = $sth->fetchrow_arrayref; +my $headercount = $headerrow ? $headerrow->[0] : 0; +my (@extra_headers) = (); +my (@extra_fields) = (); +while($headercount) { + unshift @extra_headers, "Package ". $headercount; + unshift @extra_fields, eval q!sub {my $c = shift; + my @a = split ',', $c->magic; + my $p = $a[!.--$headercount. q!]; + $p; + };!; +} + +</%init> diff --git a/httemplate/search/report_cust_main.html b/httemplate/search/report_cust_main.html new file mode 100755 index 000000000..f32c3aa18 --- /dev/null +++ b/httemplate/search/report_cust_main.html @@ -0,0 +1,60 @@ +<% include('/elements/header.html', 'Customer Report' ) %> + +<FORM ACTION="cust_main_ADV.cgi" METHOD="GET"> +<INPUT TYPE="hidden" NAME="magic" VALUE="bill"> + + <TABLE BGCOLOR="#cccccc" CELLSPACING=0> + + <TR> + <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"><FONT SIZE="+1">Search options</FONT></TH> + </TR> + <% include( '/elements/tr-select-agent.html', + ($cgi->param('agentnum') || ''), + ) + %> + +% foreach my $field (qw( signupdate )) { + + <TR> + <TD ALIGN="right" VALIGN="center"><% $label{$field} %></TD> + <TD> + <TABLE> + <% include( '/elements/tr-input-beginning_ending.html', + prefix => $field, + layout => 'horiz', + ) + %> + </TABLE> + </TD> + </TR> + +% } + + <TR> + <TD ALIGN="right" VALIGN="center">Include cancelled packages</TD> + <TD><INPUT TYPE="checkbox" NAME="cancelled_pkgs"></TD> + </TR> + + </TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Get Report"> + +</FORM> + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless ( $FS::CurrentUser::CurrentUser->access_right('List customers') && + $FS::CurrentUser::CurrentUser->access_right('List packages') + );; + +</%init> +<%once> + +my %label = ( + 'signupdate' => 'Signup Date', +); + +</%once> |