diff options
Diffstat (limited to 'httemplate/search')
35 files changed, 4278 insertions, 0 deletions
diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html new file mode 100755 index 000000000..5e904e114 --- /dev/null +++ b/httemplate/search/cust_bill.html @@ -0,0 +1,195 @@ +<% + my( $count_query, $sql_query ); + my( $count_addl ) = ( '' ); + my( $distinct ) = ( '' ); + my($begin, $end) = ( '', '' ); + my($agentnum) = ( '' ); + my($open, $days) = ( '', '' ); + if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { + $count_query = "SELECT COUNT(*) FROM cust_bill WHERE invnum = $2"; + $sql_query = { + 'table' => 'cust_bill', + 'hashref' => { 'invnum' => $2 }, + #'select' => '*', + }; + } else { + #if ( $cgi->param('begin') || $cgi->param('end') + # || $cgi->param('beginning') || $cgi->param('ending') + # || $cgi->keywords + # ) + #{ + + #some false laziness w/cust_bill::re_X + my @where; + my $orderby = 'ORDER BY cust_bill._date'; + + if ( $cgi->param('beginning') + && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $begin = str2time($1); + push @where, "cust_bill._date >= $begin"; + } + if ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $end = str2time($1) + 86399; + push @where, "cust_bill._date < $end"; + } + + if ( $cgi->param('begin') =~ /^(\d+)$/ ) { + $begin = $1; + push @where, "cust_bill._date >= $begin"; + } + if ( $cgi->param('end') =~ /^(\d+)$/ ) { + $end = $1; + push @where, "cust_bill._date < $end"; + } + + if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + push @where, "cust_main.agentnum = $agentnum"; + } + + my $owed = + "charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay + WHERE cust_bill_pay.invnum = cust_bill.invnum ) + - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill + WHERE cust_credit_bill.invnum = cust_bill.invnum )"; + + if ( $cgi->param('open') ) { + push @where, "0 != $owed"; + $open = 1; + } + + my($query) = $cgi->keywords; + if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) { + ($open, $days, my $field) = ($1, $2, $3); + $field = "_date" if $field eq 'date'; + $orderby = "ORDER BY cust_bill.$field"; + push @where, "0 != $owed" if $open; + push @where, "cust_bill._date < ". (time-86400*$days) if $days; + } + + my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; + + my $addl_from = 'left join cust_main using ( custnum )'; + + if ( $cgi->param('newest_percust') ) { + $distinct = 'DISTINCT ON ( cust_bill.custnum )'; + $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; + #$count_query = "SELECT 'N/A', 'N/A', 'N/A'"; #XXXXXXX fix + $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"; + } + + unless ( $count_query ) { + $count_query = "SELECT COUNT(*), sum(charged), sum($owed)"; + $count_addl = [ '$%.2f total invoiced', + '$%.2f total outstanding balance', + ]; + } + $count_query .= " FROM cust_bill $addl_from $extra_sql"; + + $sql_query = { + 'table' => 'cust_bill', + 'addl_from' => $addl_from, + 'hashref' => {}, + 'select' => "$distinct ". join(', ', + 'cust_bill.*', + #( map "cust_main.$_", qw(custnum last first company) ), + 'cust_main.custnum as cust_main_custnum', + ( map "cust_main.$_", qw(last first company) ), + "$owed as owed", + ), + 'extra_sql' => "$extra_sql $orderby" + }; + + } + + my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; + my $clink = sub { + my $cust_bill = shift; + my $cust_main = $cust_bill->cust_main; + $cust_main + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; + }; + + my $conf = new FS::Conf; + my $money_char = $conf->config('money_char') || '$'; + + my $html_init = join("\n", map { + ( my $action = $_ ) =~ s/_$//; + include('/elements/progress-init.html', + $_.'form', + [ 'begin', 'end', 'agentnum', 'open', 'days', 'newest_percust' ], + "../misc/${_}invoices.cgi", + { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but... + $_, #key + ), + qq!<FORM NAME="${_}form">!, + qq!<INPUT TYPE="hidden" NAME="begin" VALUE="$begin">!, + qq!<INPUT TYPE="hidden" NAME="end" VALUE="$end">!, + qq!<INPUT TYPE="hidden" NAME="agentnum" VALUE="$agentnum">!, + qq!<INPUT TYPE="hidden" NAME="open" VALUE="$open">!, + qq!<INPUT TYPE="hidden" NAME="days" VALUE="$days">!, + qq!</FORM>! + } qw( print_ email_ fax_ ) ); + + my $menubar = [ + 'Main menu' => $p, + 'Print these invoices' => + "javascript:print_process()", + 'Email these invoices' => + "javascript:email_process()", + ]; + + push @$menubar, 'Fax these invoices' => + "javascript:fax_process()" + if $conf->exists('hylafax'); + +%><%= include( 'elements/search.html', + 'title' => 'Invoice Search Results', + 'html_init' => $html_init, + 'menubar' => $menubar, + 'name' => 'invoices', + 'query' => $sql_query, + 'count_query' => $count_query, + 'count_addl' => $count_addl, + 'redirect' => $link, + 'header' => + [ 'Invoice #', qw(Balance Amount Date), 'Contact name', + 'Company' ], + 'fields' => [ + 'invnum', + sub { sprintf($money_char.'%.2f', shift->get('owed') ) }, + sub { sprintf($money_char.'%.2f', shift->charged ) }, + sub { time2str('%b %d %Y', shift->_date ) }, + sub { my $cust_bill = shift; + # my $cust_main = $cust_bill->cust_main; + # $cust_main + # ? $cust_main->get('last'). ', '. $cust_main->first + # : "WARNING: can't find cust_main.custnum ". + # $cust_bill->custnum. ' (cust_bill.invnum '. + # $cust_bill->invnum. ')'; + if ( $cust_bill->cust_main_custnum ) { + FS::cust_main::name($cust_bill); + } else { + "WARNING: can't find cust_main.custnum ". + $cust_bill->custnum. ' (cust_bill.invnum '. + $cust_bill->invnum. ')'; + } + }, + sub { my $cust_main = shift->cust_main; + $cust_main ? $cust_main->company : ''; + }, + ], + 'align' => 'rrrrll', + 'links' => [ + $link, + $link, + $link, + $link, + $clink, + $clink, + ], + + ) +%> diff --git a/httemplate/search/cust_bill_event.cgi b/httemplate/search/cust_bill_event.cgi new file mode 100644 index 000000000..f34bb68f4 --- /dev/null +++ b/httemplate/search/cust_bill_event.cgi @@ -0,0 +1,120 @@ +<% + +my $title = $cgi->param('failed') ? 'Failed invoice events' : 'Invoice events'; + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + +##tie my %hash, 'Tie::DxHash', +#my %hash = ( +# _date => { op=> '>=', value=>$beginning }, +## i wish... +## _date => { op=> '<=', value=>$ending }, +#); +#$hash{'statustext'} = { op=> '!=', value=>'' } +# if $cgi->param('failed'); + +my $where = " WHERE cust_bill_event._date >= $beginning". + " AND cust_bill_event._date <= $ending"; +$where .= " AND statustext != '' AND statustext IS NOT NULL" + if $cgi->param('failed'); + +my $sql_query = { + 'table' => 'cust_bill_event', + #'hashref' => \%hash, + 'hashref' => {}, + 'select' => join(', ', + 'cust_bill_event.*', + 'part_bill_event.event', + 'cust_bill.custnum', + 'cust_bill._date AS cust_bill_date', + map "cust_main.$_", qw(last first company) + + ), + 'extra_sql' => "$where ORDER BY _date ASC", + 'addl_from' => 'LEFT JOIN part_bill_event USING ( eventpart ) '. + 'LEFT JOIN cust_bill USING ( invnum ) '. + 'LEFT JOIN cust_main USING ( custnum ) ', +}; + +my $count_sql = "select count(*) from cust_bill_event $where"; + +my $conf = new FS::Conf; + +my $failed = $cgi->param('failed'); + +my $html_init = join("\n", map { + ( my $action = $_ ) =~ s/_$//; + include('/elements/progress-init.html', + $_.'form', + [ 'action', 'beginning', 'ending', 'failed' ], + "../misc/${_}invoice_events.cgi", + { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but... + $_, #key + ), + qq!<FORM NAME="${_}form">!, + qq!<INPUT TYPE="hidden" NAME="action" VALUE="$_">!, #not used though + qq!<INPUT TYPE="hidden" NAME="beginning" VALUE="$beginning">!, + qq!<INPUT TYPE="hidden" NAME="ending" VALUE="$ending">!, + qq!<INPUT TYPE="hidden" NAME="failed" VALUE="$failed">!, + qq!</FORM>! +} qw( print_ email_ fax_ ) ); + +my $menubar = [ + 'Main menu' => $p, + 'Re-print these events' => + "javascript:print_process()", + 'Re-email these events' => + "javascript:email_process()", + ]; + +push @$menubar, 'Re-fax these events' => + "javascript:fax_process()" + if $conf->exists('hylafax'); + +%><%= include( 'elements/search.html', + 'title' => $title, + 'html_init' => $html_init, + 'menubar' => $menubar, + 'name' => 'billing events', + 'query' => $sql_query, + 'count_query' => $count_sql, + 'header' => [ qw( Event Date Status ), + #'Inv #', 'Inv Date', 'Cust #', + 'Invoice', 'Cust #', + ], + 'fields' => [ + 'event', + sub { time2str("%b %d %Y %T", $_[0]->_date) }, + sub { + #my $cust_bill_event = shift; + my $status = $_[0]->status; + $status .= ': '.$_[0]->statustext + if $_[0]->statustext; + $status; + }, + sub { + #my $cust_bill_event = shift; + 'Invoice #'. $_[0]->invnum. + ' ('. + time2str("%D", $_[0]->cust_bill_date). + ')'; + }, + sub { FS::cust_main::name($_[0]) }, + + + ], + 'links' => [ + '', + '', + '', + sub { + my $part_bill_event = shift; + my $template = $part_bill_event->templatename; + $template .= '-' if $template; + [ "${p}view/cust_bill.cgi?$template", 'invnum']; + }, + [ "${p}view/cust_main.cgi?", 'custnum' ], + [ "${p}view/cust_main.cgi?", 'custnum' ], + ], + ) +%> diff --git a/httemplate/search/cust_bill_event.html b/httemplate/search/cust_bill_event.html new file mode 100755 index 000000000..476d22161 --- /dev/null +++ b/httemplate/search/cust_bill_event.html @@ -0,0 +1,58 @@ +<%= include( + '/elements/header.html', + ( $cgi->param('failed') ? 'Failed invoice events' : 'Invoice events' ), + include('/elements/menubar.html', + 'Main menu' => $p, # popurl(2), + ), + + ) +%> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + + <FORM ACTION="cust_bill_event.cgi" METHOD="GET"> + <INPUT TYPE="hidden" NAME="failed" VALUE="<%= $cgi->param('failed') %>"> + <TABLE> + <!--<TR> + <TD ALIGN="right">Customer type</TD> + <TD><SELECT MULTIPLE NAME="perhaps_payby"> + <OPTION SELECTED VALUE="CARD">Credit card (automatic) + <OPTION SELECTED VALUE="CHEK">E-check (automatic) + <OPTION SELECTED VALUE="LECB">Phone bill billing + <OPTION SELECTED VALUE="BILL">Billing + <OPTION SELECTED VALUE="DCRD">Credit card (on-demand) + <OPTION SELECTED VALUE="DCHK">E-check (on-demand) + </TD> + </TR> + --> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> + </TABLE> + <BR><INPUT TYPE="submit" VALUE="Get Report"> + </FORM> + </BODY> +</HTML> diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html new file mode 100755 index 000000000..b978e6238 --- /dev/null +++ b/httemplate/search/cust_credit.html @@ -0,0 +1,88 @@ +<% + my $title = 'Credit Search Results'; + #my( $count_query, $sql_query ); + + my @search = (); + + if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { + push @search, "otaker = '$1'"; + } + + if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { + push @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})$/ ) { + my $beginning = str2time($1); + push @search, "_date >= $beginning "; + } + if ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{1,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 $where = scalar(@search) + ? 'WHERE '. join(' AND ', @search) + : ''; + + my $count_query = 'SELECT COUNT(*), SUM(amount) '. + 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. + $where; + + my $sql_query = { + 'table' => 'cust_credit', + 'select' => 'cust_credit.*, cust_main.last, cust_main.first, cust_main.company', + 'hashref' => {}, + 'extra_sql' => $where, + 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + }; + + my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +%><%= include( 'elements/search.html', + 'title' => $title, + 'name' => 'credits', + 'query' => $sql_query, + 'count_query' => $count_query, + 'count_addl' => [ '$%.2f total credited', ], + #'redirect' => $link, + 'header' => + [ qw(Amount Date), 'Cust #', 'Contact name', + qw(Company By Reason) ], + 'fields' => [ + #'crednum', + sub { sprintf('$%.2f', shift->amount ) }, + sub { time2str('%b %d %Y', shift->_date ) }, + 'custnum', + sub { $_[0]->get('last'). ', '. $_[0]->first; }, + 'company', + 'otaker', + 'reason', + ], + 'align' => 'rrrllll', + 'links' => [ + '', + '', + $clink, + $clink, + $clink, + '', + '', + ], + ) +%> diff --git a/httemplate/search/cust_main-otaker.cgi b/httemplate/search/cust_main-otaker.cgi new file mode 100755 index 000000000..03c2619af --- /dev/null +++ b/httemplate/search/cust_main-otaker.cgi @@ -0,0 +1,28 @@ +<HTML> + <HEAD> + <TITLE>Customer Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Customer Search + </FONT> + <BR> + <FORM ACTION="cust_main.cgi" METHOD="GET"> + Search for <B>Order taker</B>: + <INPUT TYPE="hidden" NAME="otaker_on" VALUE="TRUE"> + <% my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_main") + or die dbh->errstr; + $sth->execute() or die $sth->errstr; +# my @otakers = map { $_->[0] } @{$sth->selectall_arrayref}; + %> + <SELECT NAME="otaker"> + <% my $otaker; while ( $otaker = $sth->fetchrow_arrayref ) { %> + <OPTION><%= $otaker->[0] %></OTAKER> + <% } %> + </SELECT> + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_main-payinfo.html b/httemplate/search/cust_main-payinfo.html new file mode 100755 index 000000000..b82b610d8 --- /dev/null +++ b/httemplate/search/cust_main-payinfo.html @@ -0,0 +1,20 @@ +<HTML> + <HEAD> + <TITLE>Customer Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Customer Search + </FONT> + <BR> + <FORM ACTION="cust_main.cgi" METHOD="GET"> + Search for <B>Credit card #</B>: + <INPUT TYPE="hidden" NAME="card_on" VALUE="TRUE"> + <INPUT TYPE="text" NAME="card"> + + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_main-quickpay.html b/httemplate/search/cust_main-quickpay.html new file mode 100755 index 000000000..154a64199 --- /dev/null +++ b/httemplate/search/cust_main-quickpay.html @@ -0,0 +1,44 @@ +<HTML> + <HEAD> + <TITLE>Quick payment entry</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Quick payment entry + </FONT> + <BR><BR> + <A HREF="../">Main Menu</A><BR><BR> + <FORM ACTION="cust_main.cgi" METHOD="GET"> + <INPUT TYPE="hidden" NAME="quickpay" VALUE="yes"> + <INPUT TYPE="checkbox" NAME="last_on" CHECKED> Search for <B>last name</B>: + <INPUT TYPE="text" NAME="last_text"> + using search method: <SELECT NAME="last_type"> + <OPTION SELECTED>All + <OPTION>Fuzzy + <OPTION>Substring + <OPTION>Exact + </SELECT> + + <P><INPUT TYPE="checkbox" NAME="company_on" CHECKED> Search for <B>company</B>: + <INPUT TYPE="text" NAME="company_text"> + using search method: <SELECT NAME="company_type"> + <OPTION SELECTED>All + <OPTION>Fuzzy + <OPTION>Substring + <OPTION>Exact + </SELECT> + + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + + <HR>Explanation of search methods: + <UL> + <LI><B>All</B> - Try all search methods. + <LI><B>Fuzzy</B> - Searches for matches that are close to your text. + <LI><B>Substring</B> - Searches for matches that contain your text. + <LI><B>Exact</B> - Finds exact matches only, but much faster than the other search methods. + </UL> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi new file mode 100755 index 000000000..665f5637d --- /dev/null +++ b/httemplate/search/cust_main.cgi @@ -0,0 +1,685 @@ +<% + +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); + +#my $cache; + +#my $monsterjoin = <<END; +#cust_main left outer join ( +# ( cust_pkg left outer join part_pkg using(pkgpart) +# ) left outer join ( +# ( +# ( +# ( cust_svc left outer join part_svc using (svcpart) +# ) left outer join svc_acct using (svcnum) +# ) left outer join svc_domain using(svcnum) +# ) left outer join svc_forward using(svcnum) +# ) using (pkgnum) +#) using (custnum) +#END + +#my $monsterjoin = <<END; +#cust_main left outer join ( +# ( cust_pkg left outer join part_pkg using(pkgpart) +# ) left outer join ( +# ( +# ( +# ( cust_svc left outer join part_svc using (svcpart) +# ) left outer join ( +# svc_acct left outer join ( +# select svcnum, domain, catchall from svc_domain +# ) as svc_acct_domsvc ( +# svc_acct_svcnum, svc_acct_domain, svc_acct_catchall +# ) on svc_acct.domsvc = svc_acct_domsvc.svc_acct_svcnum +# ) using (svcnum) +# ) left outer join svc_domain using(svcnum) +# ) left outer join svc_forward using(svcnum) +# ) using (pkgnum) +#) using (custnum) +#END + +my $limit = ''; +$limit .= "LIMIT $maxrecords" if $maxrecords; + +my $offset = $cgi->param('offset') || 0; +$limit .= " OFFSET $offset" if $offset; + +my $total = 0; + +my(@cust_main, $sortby, $orderby); +my @select = (); +my @addl_headers = (); +my @addl_cols = (); +if ( $cgi->param('browse') + || $cgi->param('otaker_on') + || $cgi->param('agentnum_on') +) { + + my %search = (); + + if ( $cgi->param('browse') ) { + my $query = $cgi->param('browse'); + if ( $query eq 'custnum' ) { + $sortby=\*custnum_sort; + $orderby = "ORDER BY custnum"; + } elsif ( $query eq 'last' ) { + $sortby=\*last_sort; + $orderby = "ORDER BY LOWER(last || ' ' || first)"; + } elsif ( $query eq 'company' ) { + $sortby=\*company_sort; + $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; + } elsif ( $query eq 'tickets' ) { + $sortby = \*tickets_sort; + $orderby = "ORDER BY tickets DESC"; + push @select, FS::TicketSystem->sql_num_customer_tickets. " as tickets"; + push @addl_headers, 'Tickets'; + push @addl_cols, 'tickets'; + } else { + die "unknown browse field $query"; + } + } else { + $sortby = \*last_sort; #?? + $orderby = "ORDER BY LOWER(last || ' ' || first)"; #?? + } + + if ( $cgi->param('otaker_on') ) { + $cgi->param('otaker') =~ /^(\w{1,32})$/ or eidiot "Illegal otaker\n"; + $search{otaker} = $1; + } elsif ( $cgi->param('agentnum_on') ) { + $cgi->param('agentnum') =~ /^(\d+)$/ or eidiot "Illegal agentnum\n"; + $search{agentnum} = $1; +# } else { +# die "unknown query..."; + } + + my @qual = (); + + my $ncancelled = ''; + + if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $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->cancel_sql if $cgi->param('cancelled'); + push @qual, FS::cust_main->prospect_sql if $cgi->param('prospect'); + push @qual, FS::cust_main->active_sql if $cgi->param('active'); + push @qual, FS::cust_main->susp_sql if $cgi->param('suspended'); + + #EWWWWWW + my $qual = join(' AND ', + map { "$_ = ". dbh->quote($search{$_}) } keys %search ); + + my $addl_qual = join(' AND ', @qual); + + if ( $addl_qual ) { + $qual .= ' AND ' if $qual; + $qual .= $addl_qual; + } + + $qual = " WHERE $qual" if $qual; + my $statement = "SELECT COUNT(*) FROM cust_main $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]; + + if ( $addl_qual ) { + if ( %search ) { + $addl_qual = " AND $addl_qual"; + } else { + $addl_qual = " WHERE $addl_qual"; + } + } + + my $select; + if ( @select ) { + $select = 'cust_main.*, '. join (', ', @select); + } else { + $select = '*'; + } + + @cust_main = qsearch('cust_main', \%search, $select, + "$addl_qual $orderby $limit" ); + +# foreach my $cust_main ( @just_cust_main ) { +# +# my @one_cust_main; +# $FS::Record::DEBUG=1; +# ( $cache, @one_cust_main ) = jsearch( +# "$monsterjoin", +# { 'custnum' => $cust_main->custnum }, +# '', +# '', +# 'cust_main', +# 'custnum', +# ); +# push @cust_main, @one_cust_main; +# } + +} else { + @cust_main=(); + $sortby = \*last_sort; + + push @cust_main, @{&custnumsearch} + if $cgi->param('custnum_on') && $cgi->param('custnum_text'); + push @cust_main, @{&cardsearch} + if $cgi->param('card_on') && $cgi->param('card'); + push @cust_main, @{&lastsearch} + if $cgi->param('last_on') && $cgi->param('last_text'); + push @cust_main, @{&companysearch} + if $cgi->param('company_on') && $cgi->param('company_text'); + push @cust_main, @{&address2search} + if $cgi->param('address2_on') && $cgi->param('address2_text'); + push @cust_main, @{&phonesearch} + if $cgi->param('phone_on') && $cgi->param('phone_text'); + push @cust_main, @{&referralsearch} + if $cgi->param('referral_custnum'); + + if ( $cgi->param('company_on') && $cgi->param('company_text') ) { + $sortby = \*company_sort; + push @cust_main, @{&companysearch}; + } + + @cust_main = grep { $_->ncancelled_pkgs || ! $_->all_pkgs } @cust_main + if ! $cgi->param('cancelled') + && ( + $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $cgi->param('showcancelledcustomers') ) + ); + + my %saw = (); + @cust_main = grep { !$saw{$_->custnum}++ } @cust_main; +} + +my %all_pkgs; +if ( $conf->exists('hidecancelledpackages' ) ) { + %all_pkgs = map { $_->custnum => [ $_->ncancelled_pkgs ] } @cust_main; +} else { + %all_pkgs = map { $_->custnum => [ $_->all_pkgs ] } @cust_main; +} +#%all_pkgs = (); + +if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { + if ( $cgi->param('quickpay') eq 'yes' ) { + print $cgi->redirect(popurl(2). "edit/cust_pay.cgi?quickpay=yes;custnum=". $cust_main[0]->custnum); + } else { + print $cgi->redirect(popurl(2). "view/cust_main.cgi?". $cust_main[0]->custnum); + } + #exit; +} elsif ( scalar(@cust_main) == 0 ) { +%> +<!-- mason kludge --> +<% + eidiot "No matching customers found!\n"; +} else { +%> +<!-- mason kludge --> +<% + + $total ||= scalar(@cust_main); + print header("Customer Search Results",menubar( + 'Main Menu', popurl(2) + )), "$total matching customers found "; + + #begin pager + my $pager = ''; + if ( $total != scalar(@cust_main) && $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 + + unless ( $cgi->param('cancelled') ) { + if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $cgi->param('showcancelledcustomers') + ) + ) { + $cgi->param('showcancelledcustomers', 1); + $cgi->param('offset', 0); + print qq!( <a href="!. $cgi->self_url. qq!">show!; + } else { + $cgi->param('showcancelledcustomers', 0); + $cgi->param('offset', 0); + print qq!( <a href="!. $cgi->self_url. qq!">hide!; + } + print ' cancelled customers</a> )'; + } + + if ( $cgi->param('referral_custnum') ) { + $cgi->param('referral_custnum') =~ /^(\d+)$/ + or eidiot "Illegal referral_custnum\n"; + my $referral_custnum = $1; + my $cust_main = qsearchs('cust_main', { custnum => $referral_custnum } ); + print '<FORM METHOD="GET">'. + qq!<INPUT TYPE="hidden" NAME="referral_custnum" VALUE="$referral_custnum">!. + 'referrals of <A HREF="'. popurl(2). + "view/cust_main.cgi?$referral_custnum\">$referral_custnum: ". + ( $cust_main->company + || $cust_main->last. ', '. $cust_main->first ). + '</A>'; + print "\n",<<END; + <SCRIPT> + function changed(what) { + what.form.submit(); + } + </SCRIPT> +END + print ' <SELECT NAME="referral_depth" SIZE="1" onChange="changed(this)">'; + my $max = 8; #config file + $cgi->param('referral_depth') =~ /^(\d*)$/ + or eidiot "Illegal referral_depth"; + my $referral_depth = $1; + + foreach my $depth ( 1 .. $max ) { + print '<OPTION', + ' SELECTED'x($depth == $referral_depth), + ">$depth"; + } + print "</SELECT> levels deep". + '<NOSCRIPT> <INPUT TYPE="submit" VALUE="change"></NOSCRIPT>'. + '</FORM>'; + } + + my @custom_priorities = (); + if ( $conf->config('ticket_system-custom_priority_field') + && @{[ $conf->config('ticket_system-custom_priority_field-values') ]} ) { + @custom_priorities = + $conf->config('ticket_system-custom_priority_field-values'); + } + + print "<BR><BR>". $pager. &table(). <<END; + <TR> + <TH></TH> + <TH>(bill) name</TH> + <TH>company</TH> +END + +if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <<END; + <TH>(service) name</TH> + <TH>company</TH> +END +} + +foreach my $addl_header ( @addl_headers ) { + print "<TH>$addl_header</TH>"; +} + +print <<END; + <TH>Packages</TH> + <TH COLSPAN=2>Services</TH> + </TR> +END + + my(%saw,$cust_main); + foreach $cust_main ( + sort $sortby grep(!$saw{$_->custnum}++, @cust_main) + ) { + my($custnum,$last,$first,$company)=( + $cust_main->custnum, + $cust_main->getfield('last'), + $cust_main->getfield('first'), + $cust_main->company, + ); + + my(@lol_cust_svc); + my($rowspan)=0;#scalar( @{$all_pkgs{$custnum}} ); + foreach ( @{$all_pkgs{$custnum}} ) { + #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); + my @cust_svc = $_->cust_svc; + push @lol_cust_svc, \@cust_svc; + $rowspan += scalar(@cust_svc) || 1; + } + + #my($rowspan) = scalar(@{$all_pkgs{$custnum}}); + my $view; + if ( defined $cgi->param('quickpay') && $cgi->param('quickpay') eq 'yes' ) { + $view = $p. 'edit/cust_pay.cgi?quickpay=yes;custnum='. $custnum; + } else { + $view = $p. 'view/cust_main.cgi?'. $custnum; + } + my $pcompany = $company + ? qq!<A HREF="$view"><FONT SIZE=-1>$company</FONT></A>! + : '<FONT SIZE=-1> </FONT>'; + print <<END; + <TR> + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$custnum</FONT></A></TD> + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$last, $first</FONT></A></TD> + <TD ROWSPAN=$rowspan>$pcompany</TD> +END + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + my($ship_last,$ship_first,$ship_company)=( + $cust_main->ship_last || $cust_main->getfield('last'), + $cust_main->ship_last ? $cust_main->ship_first : $cust_main->first, + $cust_main->ship_last ? $cust_main->ship_company : $cust_main->company, + ); + my $pship_company = $ship_company + ? qq!<A HREF="$view"><FONT SIZE=-1>$ship_company</FONT></A>! + : '<FONT SIZE=-1> </FONT>'; + print <<END; + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$ship_last, $ship_first</FONT></A></TD> + <TD ROWSPAN=$rowspan>$pship_company</A></TD> +END + } + + foreach my $addl_col ( @addl_cols ) { + print "<TD ROWSPAN=$rowspan ALIGN=right><FONT SIZE=-1>"; + if ( $addl_col eq 'tickets' ) { + if ( @custom_priorities ) { + print &itable('', 0); + foreach my $priority ( @custom_priorities, '' ) { + + my $num = + FS::TicketSystem->num_customer_tickets($custnum,$priority); + my $ahref = ''; + $ahref= '<A HREF="'. + FS::TicketSystem->href_customer_tickets($custnum,$priority). + '">' + if $num; + + print '<TR>'. + " <TD ALIGN=right><FONT SIZE=-1>$ahref$num</A></FONT></TD>". + "<TD ALIGN=left><FONT SIZE=-1>$ahref". + ( $priority || '<i>(none)</i>' ). + "</A></FONT></TD></TR>"; + + } + print '<TR><TD BGCOLOR="#000000" COLSPAN=2></TD></TR>'. + '<TR><TD ALIGN=right><FONT SIZE=-1>'; + } + + my $ahref = ''; + $ahref = '<A HREF="'. + FS::TicketSystem->href_customer_tickets($custnum). + '">' + if $cust_main->get($addl_col); + + print $ahref. $cust_main->get($addl_col). '</A>'; + print "</FONT></TD><TD ALIGN=left>". + "<FONT SIZE=-1>${ahref}Total</A><FONT>". + "</TD></TR></TABLE>" + if @custom_priorities; + + } else { + print $cust_main->get($addl_col); + } + print "</FONT></TD>"; + } + + my($n1)=''; + foreach ( @{$all_pkgs{$custnum}} ) { + my $pkgnum = $_->pkgnum; +# my $part_pkg = qsearchs( 'part_pkg', { pkgpart => $_->pkgpart } ); + my $part_pkg = $_->part_pkg; + + my $pkg = $part_pkg->pkg; + my $comment = $part_pkg->comment; + my $pkgview = "${p}view/cust_main.cgi?$custnum#cust_pkg$pkgnum"; + my @cust_svc = @{shift @lol_cust_svc}; + #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); + my $rowspan = scalar(@cust_svc) || 1; + + print $n1, qq!<TD ROWSPAN=$rowspan><A HREF="$pkgview"><FONT SIZE=-1>$pkg - $comment</FONT></A></TD>!; + 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>"; + } + #print qq!</TR><TR>\n!; + $n1="</TR><TR>"; + } + print "</TR>"; + } + + print "</TABLE>$pager</BODY></HTML>"; + +} + +#undef $cache; #does this help? + +# + +sub last_sort { + lc($a->getfield('last')) cmp lc($b->getfield('last')) + || lc($a->first) cmp lc($b->first); +} + +sub company_sort { + return -1 if $a->company && ! $b->company; + return 1 if ! $a->company && $b->company; + lc($a->company) cmp lc($b->company) + || lc($a->getfield('last')) cmp lc($b->getfield('last')) + || lc($a->first) cmp lc($b->first);; +} + +sub custnum_sort { + $a->getfield('custnum') <=> $b->getfield('custnum'); +} + +sub tickets_sort { + $b->getfield('tickets') <=> $a->getfield('tickets'); +} + +sub custnumsearch { + + my $custnum = $cgi->param('custnum_text'); + $custnum =~ s/\D//g; + $custnum =~ /^(\d{1,23})$/ or eidiot "Illegal customer number\n"; + $custnum = $1; + + [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; +} + +sub cardsearch { + + my($card)=$cgi->param('card'); + $card =~ s/\D//g; + $card =~ /^(\d{13,16})$/ or eidiot "Illegal card number\n"; + my($payinfo)=$1; + + [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}), + qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'}) + ]; +} + +sub referralsearch { + $cgi->param('referral_custnum') =~ /^(\d+)$/ + or eidiot "Illegal referral_custnum"; + my $cust_main = qsearchs('cust_main', { 'custnum' => $1 } ) + or eidiot "Customer $1 not found"; + my $depth; + if ( $cgi->param('referral_depth') ) { + $cgi->param('referral_depth') =~ /^(\d+)$/ + or eidiot "Illegal referral_depth"; + $depth = $1; + } else { + $depth = 1; + } + [ $cust_main->referral_cust_main($depth) ]; +} + +sub lastsearch { + my(%last_type); + my @cust_main; + foreach ( $cgi->param('last_type') ) { + $last_type{$_}++; + } + + $cgi->param('last_text') =~ /^([\w \,\.\-\']*)$/ + or eidiot "Illegal last name"; + my($last)=$1; + + if ( $last_type{'Exact'} || $last_type{'Fuzzy'} ) { + push @cust_main, qsearch( 'cust_main', + { 'last' => { 'op' => 'ILIKE', + 'value' => $last } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_last' => { 'op' => 'ILIKE', + 'value' => $last } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + } + + if ( $last_type{'Substring'} || $last_type{'All'} ) { + + push @cust_main, qsearch( 'cust_main', + { 'last' => { 'op' => 'ILIKE', + 'value' => "%$last%" } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_last' => { 'op' => 'ILIKE', + 'value' => "%$last%" } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + + } + + if ( $last_type{'Fuzzy'} || $last_type{'All'} ) { + push @cust_main, FS::cust_main->fuzzy_search( { 'last' => $last } ); + } + + #if ($last_type{'Sound-alike'}) { + #} + + \@cust_main; +} + +sub companysearch { + + my(%company_type); + my @cust_main; + foreach ( $cgi->param('company_type') ) { + $company_type{$_}++ + }; + + $cgi->param('company_text') =~ + /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ + or eidiot "Illegal company"; + my $company = $1; + + if ( $company_type{'Exact'} || $company_type{'Fuzzy'} ) { + push @cust_main, qsearch( 'cust_main', + { 'company' => { 'op' => 'ILIKE', + 'value' => $company } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_company' => { 'op' => 'ILIKE', + 'value' => $company } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + } + + if ( $company_type{'Substring'} || $company_type{'All'} ) { + + push @cust_main, qsearch( 'cust_main', + { 'company' => { 'op' => 'ILIKE', + 'value' => "%$company%" } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_company' => { 'op' => 'ILIKE', + 'value' => "%$company%" } }) + if defined dbdef->table('cust_main')->column('ship_last'); + + } + + if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { + push @cust_main, FS::cust_main->fuzzy_search( { 'company' => $company } ); + } + + if ($company_type{'Sound-alike'}) { + } + + \@cust_main; +} + +sub address2search { + my @cust_main; + + $cgi->param('address2_text') =~ + /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ + or eidiot "Illegal address2"; + my $address2 = $1; + + push @cust_main, qsearch( 'cust_main', + { 'address2' => { 'op' => 'ILIKE', + 'value' => $address2 } } ); + push @cust_main, qsearch( 'cust_main', + { 'address2' => { 'op' => 'ILIKE', + 'value' => $address2 } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + + \@cust_main; +} + +sub phonesearch { + my @cust_main; + + my $phone = $cgi->param('phone_text'); + + #(no longer really) false laziness with Record::ut_phonen + #only works with US/CA numbers... + $phone =~ s/\D//g; + if ( $phone =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ ) { + $phone = "$1-$2-$3"; + $phone .= " x$4" if $4; + } elsif ( $phone =~ /^(\d{3})(\d{4})$/ ) { + $phone = "$1-$2"; + } elsif ( $phone =~ /^(\d{3,4})$/ ) { + $phone = $1; + } else { + eidiot gettext('illegal_phone'). ": $phone"; + } + + my @fields = qw(daytime night fax); + push @fields, qw(ship_daytime ship_night ship_fax) + if defined dbdef->table('cust_main')->column('ship_last'); + + for my $field ( @fields ) { + push @cust_main, qsearch ( 'cust_main', + { $field => { 'op' => 'LIKE', + 'value' => "%$phone%" } } ); + } + + \@cust_main; +} + +%> diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html new file mode 100755 index 000000000..4f7508447 --- /dev/null +++ b/httemplate/search/cust_main.html @@ -0,0 +1,42 @@ +<HTML> + <HEAD> + <TITLE>Customer Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Customer Search + </FONT> + <BR><BR> + <FORM ACTION="cust_main.cgi" METHOD="GET"> + <INPUT TYPE="checkbox" NAME="last_on" CHECKED> Search for <B>last name</B>: + <INPUT TYPE="text" NAME="last_text"> + using search method: <SELECT NAME="last_type"> + <OPTION SELECTED>All + <OPTION>Fuzzy + <OPTION>Substring + <OPTION>Exact + </SELECT> + + <P><INPUT TYPE="checkbox" NAME="company_on" CHECKED> Search for <B>company</B>: + <INPUT TYPE="text" NAME="company_text"> + using search methods: <SELECT NAME="company_type"> + <OPTION SELECTED>All + <OPTION>Fuzzy + <OPTION>Substring + <OPTION>Exact + </SELECT> + + <P><INPUT TYPE="submit" VALUE="Search"> Note: Fuzzy searching can take a while. Please be patient. + + </FORM> + + <HR>Explanation of search methods: + <UL> + <LI><B>All</B> - Try all search methods. + <LI><B>Fuzzy</B> - Searches for matches that are close to your text. + <LI><B>Substring</B> - Searches for matches that contain your text. + <LI><B>Exact</B> - Finds exact matches only, but much faster than the other search methods. + </UL> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_pay.cgi b/httemplate/search/cust_pay.cgi new file mode 100755 index 000000000..c23653aa9 --- /dev/null +++ b/httemplate/search/cust_pay.cgi @@ -0,0 +1,142 @@ +<% + my $title = 'Payment Search Results'; + my( $count_query, $sql_query ); + if ( $cgi->param('magic') && $cgi->param('magic') eq '_date' ) { + + 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))?$/ + or die "illegal payby ". $cgi->param('payby'); + push @search, "cust_pay.payby = '$1'"; + if ( $3 ) { + if ( $3 eq 'VisaMC' ) { + #avoid posix regexes for portability + push @search, + " ( substring(cust_pay.payinfo from 1 for 1) = '4' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '51' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '52' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '53' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '54' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '54' ". + " OR substring(cust_pay.payinfo from 1 for 2) = '55' ". + " ) "; + } elsif ( $3 eq 'Amex' ) { + push @search, + " ( substring(cust_pay.payinfo from 1 for 2 ) = '34' ". + " OR substring(cust_pay.payinfo from 1 for 2 ) = '37' ". + " ) "; + } elsif ( $3 eq 'Discover' ) { + push @search, + " substring(cust_pay.payinfo from 1 for 4 ) = '6011' "; + } else { + die "unknown card type $3"; + } + } + } + + #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 $search = ''; + if ( @search ) { + $search = ' WHERE '. join(' AND ', @search); + } + + $count_query = "SELECT COUNT(*), SUM(paid) ". + "FROM cust_pay LEFT JOIN cust_main USING ( custnum )". + $search; + + $sql_query = { + 'table' => 'cust_pay', + 'select' => 'cust_pay.*, cust_main.last, cust_main.first, cust_main.company', + 'hashref' => {}, + 'extra_sql' => "$search ORDER BY _date", + 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', + }; + + } else { + + $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo"; + my $payinfo = $1; + + $cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby"; + my $payby = $1; + + $count_query = "SELECT COUNT(*), SUM(paid) FROM cust_pay ". + "WHERE payinfo = '$payinfo' AND payby = '$payby'"; + + $sql_query = { + 'table' => 'cust_pay', + 'hashref' => { 'payinfo' => $payinfo, + 'payby' => $payby }, + 'extra_sql' => "ORDER BY _date", + }; + + } + + my $link = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +%><%= include( 'elements/search.html', + 'title' => $title, + 'name' => 'payments', + 'query' => $sql_query, + 'count_query' => $count_query, + 'count_addl' => [ '$%.2f total paid', ], + 'header' => + [ qw(Payment Amount Date), 'Cust #', 'Contact name', + 'Company', ], + 'fields' => [ + sub { + my $cust_pay = shift; + if ( $cust_pay->payby eq 'CARD' ) { + 'Card #'. $cust_pay->payinfo_masked; + } elsif ( $cust_pay->payby eq 'CHEK' ) { + 'E-check acct#'. $cust_pay->payinfo; + } elsif ( $cust_pay->payby eq 'BILL' ) { + 'Check #'. $cust_pay->payinfo; + } else { + $cust_pay->payby. ' '. $cust_pay->payinfo; + } + }, + sub { sprintf('$%.2f', shift->paid ) }, + sub { time2str('%b %d %Y', shift->_date ) }, + 'custnum', + sub { $_[0]->get('last'). ', '. $_[0]->first; }, + 'company', + ], + 'align' => 'lrrrll', + 'links' => [ + '', + '', + '', + $link, + $link, + $link, + ], + ) +%> diff --git a/httemplate/search/cust_pay.html b/httemplate/search/cust_pay.html new file mode 100755 index 000000000..6414cf771 --- /dev/null +++ b/httemplate/search/cust_pay.html @@ -0,0 +1,18 @@ +<HTML> + <HEAD> + <TITLE>Check # Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Check # Search + </FONT> + <BR><BR> + <FORM ACTION="cust_pay.cgi" METHOD="GET"> + Search for <B>check #</B>: + <INPUT TYPE="text" NAME="payinfo"> + <INPUT TYPE="hidden" NAME="payby" VALUE="BILL"> + <BR><BR><INPUT TYPE="submit" VALUE="Search"> + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi new file mode 100755 index 000000000..6d26317e0 --- /dev/null +++ b/httemplate/search/cust_pkg.cgi @@ -0,0 +1,363 @@ +<% + +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 "; + } + + $range .= ( $range ? 'AND ' : ' WHERE ' ). '( cancel IS NULL OR cancel = 0 )'; + + if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { + $range .= ( $range ? 'AND ' : ' WHERE ' ). + "$1 = ( SELECT agentnum FROM cust_main". + " WHERE cust_main.custnum = cust_pkg.custnum )"; + } + + #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" ); + +} else { + + my $qual = ''; + if ( $cgi->param('magic') && + $cgi->param('magic') =~ /^(active|suspended|canceled)$/ + ) { + + if ( $cgi->param('magic') eq 'active' ) { + $qual = 'WHERE ( susp IS NULL OR susp = 0 )'. + ' AND ( cancel IS NULL OR cancel = 0)'; + } 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'; + } else { + die "guru meditation #420"; + } + + $sortby = \*pkgnum_sort; + + if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { + $qual .= " AND pkgpart = $1"; + } + + } elsif ( $query eq 'pkgnum' ) { + + $sortby=\*pkgnum_sort; + + } 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; + + $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 + ) + ) + "; + + } + + } 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'); +} + +%> diff --git a/httemplate/search/cust_pkg_report.cgi b/httemplate/search/cust_pkg_report.cgi new file mode 100755 index 000000000..6bd918995 --- /dev/null +++ b/httemplate/search/cust_pkg_report.cgi @@ -0,0 +1,63 @@ +<HTML> + <HEAD> + <TITLE>Packages</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Packages</H1> + <FORM ACTION="cust_pkg.cgi" METHOD="GET"> + <INPUT TYPE="hidden" NAME="magic" VALUE="bill"> + Return packages with next bill date:<BR><BR> + <TABLE> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><I>m/d/y</I></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><I>m/d/y</I></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> +<% my %agent_search = dbdef->table('agent')->column('disabled') + ? ( 'disabled' => '' ) : (); + my @agents = qsearch( 'agent', \%agent_search ); + if ( scalar(@agents) == 1 ) { +%> + <INPUT TYPE="hidden" NAME="agentnum" VALUE="<%= $agents[0]->agentnum %>"> +<% } else { %> + + <TR> + <TD ALIGN="right">Agent: </TD> + <TD><SELECT NAME="agentnum"><OPTION VALUE="">(all) + <% foreach my $agent ( sort { $a->agent cmp $b->agent; } @agents) { %> + <OPTION VALUE="<%= $agent->agentnum %>"><%= $agent->agent %> + <% } %> + </TD> + </TR> +<% } %> + </TABLE> + <BR><INPUT TYPE="submit" VALUE="Get Report"> + + </FORM> + + </BODY> +</HTML> + diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html new file mode 100644 index 000000000..47d619444 --- /dev/null +++ b/httemplate/search/elements/search.html @@ -0,0 +1,330 @@ +<% + + my(%opt) = @_; + #warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n"; + + my %align = ( + 'l' => 'left', + 'r' => 'right', + 'c' => 'center', + ' ' => '', + '.' => '', + ); + $opt{align} = [ map $align{$_}, split(//, $opt{align}) ], + unless !$opt{align} || ref($opt{align}); + + my $type = ''; + my $limit = ''; + my($maxrecords, $total, $offset, $count_arrayref); + + if ( $cgi->param('_type') =~ /^(csv|\w*\.xls)$/ ) { + + $type = $1; + + } else { #setup some pagination things if we're in html mode + + unless (exists($opt{'count_query'}) && length($opt{'count_query'})) { + ( $opt{'count_query'} = $opt{'query'} ) =~ + s/^\s*SELECT\s*(.*?)\s+FROM\s/SELECT COUNT(*) FROM /i; + } + + my $conf = new FS::Conf; + $maxrecords = $conf->config('maxsearchrecordsperpage'); + + $limit = $maxrecords ? "LIMIT $maxrecords" : ''; + + $offset = $cgi->param('offset') || 0; + $limit .= " OFFSET $offset" if $offset; + + my $count_sth = dbh->prepare($opt{'count_query'}) + or die "Error preparing $opt{'count_query'}: ". dbh->errstr; + $count_sth->execute + or die "Error executing $opt{'count_query'}: ". $count_sth->errstr; + $count_arrayref = $count_sth->fetchrow_arrayref; + $total = $count_arrayref->[0]; + + } + + # run the query + + my $header = $opt{'header'}; + my $rows; + if ( ref($opt{'query'}) ) { + #eval "use FS::$opt{'query'};"; + $rows = [ qsearch( + $opt{'query'}->{'table'}, + $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") + or die "Error preparing $opt{'query'}: ". dbh->errstr; + $sth->execute + or die "Error executing $opt{'query'}: ". $sth->errstr; + + #can get # of rows without fetching them all? + $rows = $sth->fetchall_arrayref; + + $header ||= $sth->{NAME}; + } + + if ( $type eq 'csv' ) { + + #http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes + http_header('Content-Type' => 'text/plain' ); + + my $csv = new Text::CSV_XS { 'always_quote' => 1, + 'eol' => "\n", #"\015\012", #"\012" + }; + + $csv->combine(@$header); #or die $csv->status; + %><%= $csv->string %><% + + foreach my $row ( @$rows ) { + + if ( $opt{'fields'} ) { + + my @line = (); + + foreach my $field ( @{$opt{'fields'}} ) { + if ( ref($field) eq 'CODE' ) { + push @line, &{$field}($row); + } else { + push @line, $row->$field(); + } + } + + $csv->combine(@line); #or die $csv->status; + + } else { + $csv->combine(@$row); #or die $csv->status; + } + + %><%= $csv->string %><% + + } + + #} elsif ( $type eq 'excel' ) { + } elsif ( $type =~ /\.xls$/ ) { + + #http_header('Content-Type' => 'application/excel' ); #eww + http_header('Content-Type' => 'application/vnd.ms-excel' ); + #http_header('Content-Type' => 'application/msexcel' ); #alas + + my $data = ''; + my $XLS = new IO::Scalar \$data; + my $workbook = Spreadsheet::WriteExcel->new($XLS) + or die "Error opening .xls file: $!"; + + my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31)); + + my($r,$c) = (0,0); + + $worksheet->write($r, $c++, $_) foreach @$header; + + foreach my $row ( @$rows ) { + $r++; + $c = 0; + + if ( $opt{'fields'} ) { + + #my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; + #my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; + + foreach my $field ( @{$opt{'fields'}} ) { + #my $align = $aligns ? shift @$aligns : ''; + #$align = " ALIGN=$align" if $align; + #my $a = ''; + #if ( $links ) { + # my $link = shift @$links; + # $link = &{$link}($row) if ref($link) eq 'CODE'; + # if ( $link ) { + # my( $url, $method ) = @{$link}; + # if ( ref($method) eq 'CODE' ) { + # $a = $url. &{$method}($row); + # } else { + # $a = $url. $row->$method(); + # } + # $a = qq(<A HREF="$a">); + # } + #} + if ( ref($field) eq 'CODE' ) { + $worksheet->write($r, $c++, &{$field}($row) ); + } else { + $worksheet->write($r, $c++, $row->$field() ); + } + } + + } else { + $worksheet->write($r, $c++, $_) foreach @$row; + } + + } + + $workbook->close();# or die "Error creating .xls file: $!"; + + http_header('Content-Length' => length($data) ); + %><%= $data %><% + + } else { # regular HTML + + if ( exists($opt{'redirect'}) && scalar(@$rows) == 1 && $total == 1 ) { + my( $url, $method ) = @{$opt{'redirect'}}; + redirect( $url. $rows->[0]->$method() ); + } else { + ( my $xlsname = $opt{'name'} ) =~ s/\W//g; + $opt{'name'} =~ s/s$// if $total == 1; + + my @menubar = (); + if ( $opt{'menubar'} ) { + @menubar = @{ $opt{'menubar'} }; + } else { + @menubar = ( 'Main menu' => $p ); + } + %> + <%= include( '/elements/header.html', $opt{'title'}, + include( '/elements/menubar.html', @menubar ) + ) + %> + <%= defined($opt{'html_init'}) ? $opt{'html_init'} : '' %> + <% my $pager = include ( '/elements/pager.html', + 'offset' => $offset, + 'num_rows' => scalar(@$rows), + 'total' => $total, + 'maxrecords' => $maxrecords, + ); + %> + <% unless ( $total ) { %> + No matching <%= $opt{'name'} %> found.<BR> + <% } else { %> + + <TABLE> + <TR> + <TD VALIGN="bottom"> + <%= $total %> total <%= $opt{'name'} %><BR> + <% if ( $opt{'count_addl'} ) { %> + <% my $n=0; foreach my $count ( @{$opt{'count_addl'}} ) { %> + <%= sprintf( $count, $count_arrayref->[++$n] ) %><BR> + <% } %> + <% } %> + </TD> + <TD ALIGN="right"> + <% $cgi->param('_type', "$xlsname.xls" ); %> + Download full results<BR> + as <A HREF="<%= $cgi->self_url %>">Excel spreadsheet</A><BR> + <% $cgi->param('_type', 'csv'); %> + as <A HREF="<%= $cgi->self_url %>">CSV file</A> + </TD> + </TR> + <TR> + <TD COLSPAN=2> + + <%= $pager %> + <STYLE TYPE="text/css"> + .grid table { border: solid; empty-cells: show } + .grid TH { padding-left: 3px; padding-right: 3px; border: 1px solid #dddddd; border-bottom: dashed 1px black; border-right: none } + .grid TD { padding-left: 3px; padding-right: 3px; empty-cells: show; border: 1px solid #cccccc; border-bottom: none; border-right: none } + </STYLE> + <TABLE CLASS="grid" CELLSPACING=0 CELLPADDING=0 BORDER=1 BORDERCOLOR="#000000" STYLE="border: solid 1px black; empty-cells: show"> + <TR> + <% foreach my $header ( @$header ) { %> + <TH CLASS="grid" BGCOLOR="#cccccc"><%= $header %></TH> + <% } %> + </TR> + <% my $bgcolor1 = '#eeeeee'; + my $bgcolor2 = '#ffffff'; + my $bgcolor; + foreach my $row ( @$rows ) { + if ( $bgcolor eq $bgcolor1 ) { + $bgcolor = $bgcolor2; + } else { + $bgcolor = $bgcolor1; + } + %> + <TR> + <% if ( $opt{'fields'} ) { + + my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; + my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; + my $colors = $opt{'color'} ? [ @{$opt{'color'}} ] : []; + my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : []; + my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : []; + + foreach my $field ( @{$opt{'fields'}} ) { + + my $align = $aligns ? shift @$aligns : ''; + $align = " ALIGN=$align" if $align; + + my $a = ''; + if ( $links ) { + my $link = shift @$links; + $link = &{$link}($row) if ref($link) eq 'CODE'; + if ( $link ) { + my( $url, $method ) = @{$link}; + if ( ref($method) eq 'CODE' ) { + $a = $url. &{$method}($row); + } else { + $a = $url. $row->$method(); + } + $a = qq(<A HREF="$a">); + } + } + + my $font = ''; + my $color = shift @$colors; + $color = &{$color}($row) if ref($color) eq 'CODE'; + my $size = shift @$sizes; + $size = &{$size}($row) if ref($size) eq 'CODE'; + if ( $color || $size ) { + $font = '<FONT '. + ( $color ? "COLOR=#$color " : '' ). + ( $size ? qq(SIZE="$size" ) : '' ). + '>'; + } + + my($s, $es) = ( '', '' ); + my $style = shift @$styles; + $style = &{$style}($row) if ref($style) eq 'CODE'; + if ( $style ) { + $s = join( '', map "<$_>", split('', $style) ); + $es = join( '', map "</$_>", split('', $style) ); + } + + %> + <% if ( ref($field) eq 'CODE' ) { %> + <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"<%= $align %>><%= $font %><%= $a %><%= $s %><%= &{$field}($row) %><%= $es %><%= $a ? '</A>' : '' %><%= $font ? '</FONT>' : '' %></TD> + <% } else { %> + <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"<%= $align %>><%= $font %><%= $a %><%= $s %><%= $row->$field() %><%= $es %><%= $a ? '</A>' : '' %><%= $font ? '</FONT>' : '' %></TD> + <% } %> + <% } %> + <% } else { %> + <% foreach ( @$row ) { %> + <TD CLASS="grid" BGCOLOR="$bgcolor"><%= $_ %></TD> + <% } %> + <% } %> + </TR> + <% } %> + + <% if ( $opt{'footer'} ) { %> + <TR> + <% foreach my $footer ( @{ $opt{'footer'} } ) { %> + <TD CLASS="grid" BGCOLOR="#dddddd" STYLE="border-top: dashed 1px black;"><i><%= $footer %></i></TH> + <% } %> + </TR> + <% } %> + + </TABLE> + <%= $pager %> + + </TD> + </TR> + </TABLE> + + <% } %> + </BODY> + </HTML> + <% } %> +<% } %> diff --git a/httemplate/search/prepay_credit.html b/httemplate/search/prepay_credit.html new file mode 100644 index 000000000..8c8f57b5a --- /dev/null +++ b/httemplate/search/prepay_credit.html @@ -0,0 +1,43 @@ +<% +my $agent = ''; +my $hashref = {}; +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $hashref->{agentnum} = $1; + $agent = qsearchs('agent', { 'agentnum' => $1 } ); +} + +my $count_query = 'SELECT COUNT(*) FROM prepay_credit'; +$count_query .= ' WHERE agentnum = '. $agent->agentnum if $agent; + +%><%= include( 'elements/search.html', + 'title' => 'Unused Prepaid Cards'. + ($agent ? ' for '. $agent->agent : ''), + 'menubar' => [ + 'Main menu' => $p, + 'Generate cards' => $p.'edit/prepay_credit.cgi', + ], + 'name' => 'prepaid cards', + 'query' => { 'table' => 'prepay_credit', + 'hashref' => $hashref, + }, + 'count_query' => $count_query, + #'redirect' => $link, + 'header' => [ '#', qw(Amount Time Agent) ], + 'fields' => [ + 'identifier', + sub { sprintf('$%.2f', shift->amount ) }, + sub { my $c = shift; $c ? duration_exact($c->seconds) : '' }, + sub { my $agent = shift->agent; + $agent ? $agent->agent : ''; + }, + ], + 'links' => [ + '', + '', + '', + sub { my $agent = shift->agent; + $agent ? [ "${p}view/agent.cgi?", 'agentnum' ] : ''; + }, + ], + ) +%> diff --git a/httemplate/search/reg_code.html b/httemplate/search/reg_code.html new file mode 100644 index 000000000..52a99ff66 --- /dev/null +++ b/httemplate/search/reg_code.html @@ -0,0 +1,36 @@ +<% + +my $agentnum = $cgi->param('agentnum'); +$agentnum =~ /^(\d+)$/ or eidiot "illegal agentnum $agentnum"; +$agentnum = $1; +my $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + +my $count_query = "SELECT COUNT(*) FROM reg_code WHERE agentnum = $agentnum"; + +%><%= include( 'elements/search.html', + 'title' => 'Unused Registration Codes for '. + $agent->agent, + 'name' => 'registration codes', + 'query' => { 'table' => 'reg_code', + 'hashref' => { 'agentnum' => $agentnum, }, + }, + 'count_query' => $count_query, + #'redirect' => $link, + 'header' => [ qw(Code Packages) ], + 'fields' => [ + 'code', + sub { + map { + qq!<A HREF="${p}edit/part_pkg.cgi?!. $_->pkgpart. '">'. + $_->pkg. ' - '. $_->comment. + '</A><BR>' + } $_[0]->part_pkg + }, + ], + 'links' => [ + '', + #$plink, + '', + ], + ) +%> diff --git a/httemplate/search/report_cust_bill.html b/httemplate/search/report_cust_bill.html new file mode 100644 index 000000000..0f6e6814e --- /dev/null +++ b/httemplate/search/report_cust_bill.html @@ -0,0 +1,60 @@ + <HEAD> + <TITLE>Invoice report criteria</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Invoice report criteria</H1> + <FORM ACTION="cust_bill.html" METHOD="GET"> + <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> + <TABLE> + <TR> + <TD ALIGN="right">for agent: </TD> + <TD><SELECT NAME="agentnum"> + <OPTION VALUE="">all</OPTION> + <% foreach my $agent ( qsearch('agent', { disabled=>'' } ) ) { %> + <OPTION VALUE="<%= $agent->agentnum %>"<%= $cgi->param('agentnum') == $agent->agentnum ? ' SELECTED' : '' %>><%= $agent->agent %> + <% } %> + </SELECT> + </TD> + </TR> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="open" VALUE="1" CHECKED></TD> + <TD>Show only open invoices</TD> + </TR> + <TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="newest_percust" VALUE="1"></TD> + <TD>Show only the single most recent invoice per-customer</TD> + </TR> + </TABLE> + <BR><INPUT TYPE="submit" VALUE="Get Report"> + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html new file mode 100644 index 000000000..3a14f44e2 --- /dev/null +++ b/httemplate/search/report_cust_credit.html @@ -0,0 +1,68 @@ +<HTML> + <HEAD> + <TITLE>Credit report criteria</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Credit report criteria</H1> + <FORM ACTION="cust_credit.html" METHOD="GET"> + <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> + <TABLE> + <TR> + <TD ALIGN="right">Credits by employee: </TD> +<% + my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_credit") + or die dbh->errstr; + $sth->execute or die $sth->errstr; + my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; +%> + <TD><SELECT NAME="otaker"> + <OPTION VALUE="">all</OPTION> + <% foreach my $otaker ( @otakers ) { %> + <OPTION VALUE="<%= $otaker %>"><%= $otaker %></OPTION> + <% } %> + </SELECT> + </TD> + </TR> + <TR> + <TD ALIGN="right">for agent: </TD> + <TD><SELECT NAME="agentnum"> + <OPTION VALUE="">all</OPTION> + <% foreach my $agent ( qsearch('agent', { disabled=>'' } ) ) { %> + <OPTION VALUE="<%= $agent->agentnum %>"<%= $cgi->param('agentnum') == $agent->agentnum ? ' SELECTED' : '' %>><%= $agent->agent %> + <% } %> + </SELECT> + </TD> + </TR> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> + </TABLE> + <BR><INPUT TYPE="submit" VALUE="Get Report"> + </FORM> + </BODY> +</HTML> diff --git a/httemplate/search/report_cust_pay.html b/httemplate/search/report_cust_pay.html new file mode 100644 index 000000000..8b9e27302 --- /dev/null +++ b/httemplate/search/report_cust_pay.html @@ -0,0 +1,65 @@ +<HTML> + <HEAD> + <TITLE>Payment report criteria</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Payment report criteria</H1> + <FORM ACTION="cust_pay.cgi" METHOD="GET"> + <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> + <TABLE> + <TR> + <TD ALIGN="right">Payments of type: </TD> + <TD><SELECT NAME="payby"> + <OPTION VALUE="">all</OPTION> + <OPTION VALUE="CARD">credit card (all)</OPTION> + <OPTION VALUE="CARD-VisaMC">credit card (Visa/MasterCard)</OPTION> + <OPTION VALUE="CARD-Amex">credit card (American Express)</OPTION> + <OPTION VALUE="CARD-Discover">credit card (Discover)</OPTION> + <OPTION VALUE="CHEK">electronic check / ACH</OPTION> + <OPTION VALUE="BILL">check / cash</OPTION> + </SELECT> + </TD> + </TR> + <TR> + <TD ALIGN="right">for agent: </TD> + <TD><SELECT NAME="agentnum"> + <OPTION VALUE="">all</OPTION> + <% foreach my $agent ( qsearch('agent', { disabled=>'' } ) ) { %> + <OPTION VALUE="<%= $agent->agentnum %>"<%= $cgi->param('agentnum') == $agent->agentnum ? ' SELECTED' : '' %>><%= $agent->agent %> + <% } %> + </SELECT> + </TD> + </TR> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> + </TABLE> + <BR><INPUT TYPE="submit" VALUE="Get Report"> + </FORM> + </BODY> +</HTML> diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/report_prepaid_income.cgi new file mode 100644 index 000000000..1677591a3 --- /dev/null +++ b/httemplate/search/report_prepaid_income.cgi @@ -0,0 +1,86 @@ +<!-- mason kludge --> +<% + + #doesn't yet deal with daily/weekly packages + + #needs to be re-written in sql for efficiency + + my $time = time; + + my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time; + $now =~ /^(\d+)$/ or die "unparsable date?"; + $now = $1; + + my( $total, $total_legacy ) = ( 0, 0 ); + + my @cust_bill_pkg = + grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[dw])$/ } + qsearch( 'cust_bill_pkg', { + 'recur' => { op=>'!=', value=>0 }, + 'edate' => { op=>'>', value=>$now }, + }, ); + + my @cust_pkg = + grep { $_->part_pkg->recur != 0 + && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/ + } + qsearch ( 'cust_pkg', { + 'bill' => { op=>'>', value=>$now } + } ); + + foreach my $cust_bill_pkg ( @cust_bill_pkg) { + my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; + + my $elapsed = $now - $cust_bill_pkg->sdate; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + my $unearned = $remaining * $cust_bill_pkg->recur; + $total += $unearned; + + } + + foreach my $cust_pkg ( @cust_pkg ) { + my $period = $cust_pkg->bill - $cust_pkg->last_bill; + + my $elapsed = $now - $cust_pkg->last_bill; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy + $total_legacy += $unearned; + + } + + $total = sprintf('%.2f', $total); + $total_legacy = sprintf('%.2f', $total_legacy); + +%> + +<%= header( 'Prepaid Income (Unearned Revenue) Report', + menubar( 'Main Menu'=>$p, ) ) %> +<%= table() %> + <TR> + <TH>Actual Unearned Revenue</TH> + <TH>Legacy Unearned Revenue</TH> + </TR> + <TR> + <TD ALIGN="right">$<%= $total %> + <TD ALIGN="right"> + <%= $now == $time ? "\$$total_legacy" : '<i>N/A</i>'%> + </TD> + </TR> + +</TABLE> +<BR> +Actual unearned revenue is the amount of unearned revenue Freeside has +actually invoiced for packages with longer-than monthly terms. +<BR><BR> +Legacy unearned revenue is the amount of unearned revenue represented by +customer packages. This number may be larger than actual unearned +revenue if you have imported longer-than monthly customer packages from +a previous billing system. +</BODY> +</HTML> diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html new file mode 100644 index 000000000..57c318eba --- /dev/null +++ b/httemplate/search/report_prepaid_income.html @@ -0,0 +1,39 @@ +<HTML> + <HEAD> + <TITLE>Prepaid Income (Unearned Revenue) Report</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Prepaid Income (Unearned Revenue) Report</H1> + <FORM ACTION="report_prepaid_income.cgi" METHOD="GET"> + <TABLE> + <TR> + <TD>Prepaid income (unearned revenue) as of </TD> + <TD> + <INPUT TYPE="text" NAME="date" ID="date_text" VALUE="now"> + <IMG SRC="../images/calendar.png" ID="date_button" STYLE="cursor: pointer" TITLE="Select date"> + </TD> + </TR> + <TR> + <TD> + </TD> + <TD><i>m/d/y</i></TD> + </TR> + </TABLE> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "date_text", + ifFormat: "%m/%d/%Y", + button: "date_button", + align: "BR" + }); +</SCRIPT> + + <INPUT TYPE="submit" VALUE="Generate report"> + </BODY> +</HTML> + <TABLE> + diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi new file mode 100755 index 000000000..82ce5d777 --- /dev/null +++ b/httemplate/search/report_receivables.cgi @@ -0,0 +1,225 @@ +<% + + my $charged = <<END; + sum( charged + - coalesce( + ( select sum(amount) from cust_bill_pay + where cust_bill.invnum = cust_bill_pay.invnum ) + ,0 + ) + - coalesce( + ( select sum(amount) from cust_credit_bill + where cust_bill.invnum = cust_credit_bill.invnum ) + ,0 + ) + + ) +END + + my $owed_cols = <<END; + coalesce( + ( select $charged from cust_bill + where cust_bill._date > extract(epoch from now())-2592000 + and cust_main.custnum = cust_bill.custnum + ) + ,0 + ) as owed_0_30, + + coalesce( + ( select $charged from cust_bill + where cust_bill._date > extract(epoch from now())-5184000 + and cust_bill._date <= extract(epoch from now())-2592000 + and cust_main.custnum = cust_bill.custnum + ) + ,0 + ) as owed_30_60, + + coalesce( + ( select $charged from cust_bill + where cust_bill._date > extract(epoch from now())-7776000 + and cust_bill._date <= extract(epoch from now())-5184000 + and cust_main.custnum = cust_bill.custnum + ) + ,0 + ) as owed_60_90, + + coalesce( + ( select $charged from cust_bill + where cust_bill._date <= extract(epoch from now())-7776000 + and cust_main.custnum = cust_bill.custnum + ) + ,0 + ) as owed_90_pl, + + coalesce( + ( select $charged from cust_bill + where cust_main.custnum = cust_bill.custnum + ) + ,0 + ) as owed_total +END + + my $recurring = <<END; + '0' != ( select freq from part_pkg + where cust_pkg.pkgpart = part_pkg.pkgpart ) +END + + my $packages_cols = <<END; + + ( select count(*) from cust_pkg + where cust_main.custnum = cust_pkg.custnum + and $recurring + and ( cancel = 0 or cancel is null ) + ) as uncancelled_pkgs, + + ( select count(*) from cust_pkg + where cust_main.custnum = cust_pkg.custnum + and $recurring + and ( cancel = 0 or cancel is null ) + and ( susp = 0 or susp is null ) + ) as active_pkgs + +END + + my $where = <<END; +where 0 < + coalesce( + ( select $charged from cust_bill + where cust_main.custnum = cust_bill.custnum + ) + ,0 + ) +END + + my $agentnum = ''; + if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + $where .= " AND agentnum = '$agentnum' "; + } + + my $count_sql = "select count(*) from cust_main $where"; + + my $sql_query = { + 'table' => 'cust_main', + 'hashref' => {}, + 'select' => "*, $owed_cols, $packages_cols", + 'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)", + }; + + if ( $agentnum ) { + $owed_cols =~ + s/cust_bill\.custnum/cust_bill.custnum AND cust_main.agentnum = '$agentnum'/g; + } + my $total_sql = "select $owed_cols"; + my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; + $total_sth->execute or die $total_sth->errstr; + my $row = $total_sth->fetchrow_hashref(); + + my $conf = new FS::Conf; + my $money_char = $conf->config('money_char') || '$'; + +%><%= include( 'elements/search.html', + 'title' => 'Accounts Receivable Aging Summary', + 'name' => 'customers', + 'query' => $sql_query, + 'count_query' => $count_sql, + 'header' => [ + '#', + 'Customer', + 'Status', # (me)', + #'Status', # (cust_main)', + '0-30', + '30-60', + '60-90', + '90+', + 'Total', + ], + 'footer' => [ + '', + 'Total', + '', + #'', + sprintf( $money_char.'%.2f', + $row->{'owed_0_30'} ), + sprintf( $money_char.'%.2f', + $row->{'owed_30_60'} ), + sprintf( $money_char.'%.2f', + $row->{'owed_60_90'} ), + sprintf( $money_char.'%.2f', + $row->{'owed_90_pl'} ), + sprintf( '<b>'. $money_char.'%.2f'. '</b>', + $row->{'owed_total'} ), + ], + 'fields' => [ + 'custnum', + 'name', + sub { + my $row = shift; + my $status = 'Cancelled'; + my $statuscol = 'FF0000'; + if ( $row->uncancelled_pkgs ) { + $status = 'Suspended'; + $statuscol = 'FF9900'; + if ( $row->active_pkgs ) { + $status = 'Active'; + $statuscol = '00CC00'; + } + } + $status; + }, + #sub { ucfirst(shift->status) }, + sub { sprintf( $money_char.'%.2f', + shift->get('owed_0_30') ) }, + sub { sprintf( $money_char.'%.2f', + shift->get('owed_30_60') ) }, + sub { sprintf( $money_char.'%.2f', + shift->get('owed_60_90') ) }, + sub { sprintf( $money_char.'%.2f', + shift->get('owed_90_pl') ) }, + sub { sprintf( $money_char.'%.2f', + shift->get('owed_total') ) }, + ], + 'links' => [ + [ "${p}view/cust_main.cgi?", 'custnum' ], + [ "${p}view/cust_main.cgi?", 'custnum' ], + '', + #'', + '', + '', + '', + '', + '', + ], + #'align' => 'rlccrrrrr', + 'align' => 'rlcrrrrr', + #'size' => [ '', '', '-1', '-1', '', '', '', '', '', ], + #'style' => [ '', '', 'b', 'b', '', '', '', '', 'b', ], + 'size' => [ '', '', '-1', '', '', '', '', '', ], + 'style' => [ '', '', 'b', '', '', '', '', 'b', ], + 'color' => [ + '', + '', + sub { + my $row = shift; + my $status = 'Cancelled'; + my $statuscol = 'FF0000'; + if ( $row->uncancelled_pkgs ) { + $status = 'Suspended'; + $statuscol = 'FF9900'; + if ( $row->active_pkgs ) { + $status = 'Active'; + $statuscol = '00CC00'; + } + } + $statuscol; + }, + #sub { shift->statuscolor; }, + '', + '', + '', + '', + '', + ], + + ) +%> diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi new file mode 100755 index 000000000..587665740 --- /dev/null +++ b/httemplate/search/report_tax.cgi @@ -0,0 +1,253 @@ +<!-- mason kludge --> +<% + +my $user = getotaker; + +$cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/; +my $pbeginning = $1; +my $beginning = $1 ? str2time($1) : 0; + +$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/; +my $pending = $1; +my $ending = ( $1 ? str2time($1) : 4294880896 ) + 86399; + +my $from_join_cust = " + FROM cust_bill_pkg + JOIN cust_bill USING ( invnum ) + JOIN cust_main USING ( custnum ) +"; +my $join_pkg = " + JOIN cust_pkg USING ( pkgnum ) + JOIN part_pkg USING ( pkgpart ) +"; +my $where = " + WHERE _date >= $beginning AND _date <= $ending + AND ( county = ? OR ? = '' ) + AND ( state = ? OR ? = '' ) + AND ( country = ? ) + AND payby != 'COMP' +"; +my @base_param = qw( county county state state country ); + +my $gotcust = " + WHERE 0 < ( SELECT COUNT(*) FROM cust_main + WHERE ( cust_main.county = cust_main_county.county + OR cust_main_county.county = '' + OR cust_main_county.county IS NULL ) + AND ( cust_main.state = cust_main_county.state + OR cust_main_county.state = '' + OR cust_main_county.state IS NULL ) + AND ( cust_main.country = cust_main_county.country ) + LIMIT 1 + ) +"; + +my $monthly_exempt_warning = 0; +my($total, $exempt, $taxable, $owed, $tax) = ( 0, 0, 0, 0, 0 ); +my $out = 'Out of taxable region(s)'; +my %regions; +foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) { + #warn $r->county. ' '. $r->state. ' '. $r->country. "\n"; + + my $label = getlabel($r); + $regions{$label}->{'label'} = $label; + + my $fromwhere = $from_join_cust. $join_pkg. $where; + my @param = @base_param; + + if ( $r->taxclass ) { + $fromwhere .= " AND ( taxclass = ? ) "; + push @param, 'taxclass'; + } + + my $nottax = 'pkgnum != 0'; + + my $a = scalar_sql($r, \@param, + "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax" + ); + $total += $a; + $regions{$label}->{'total'} += $a; + + foreach my $e ( grep { $r->get($_.'tax') =~ /^Y/i } + qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { + my $x = scalar_sql($r, \@param, + "SELECT SUM($e) $fromwhere AND $nottax" + ); + $exempt += $x; + $regions{$label}->{'exempt'} += $x; + } + + my($t, $x) = (0, 0); + foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i } + qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { + $t += scalar_sql($r, \@param, + "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )" + ); + + $x += scalar_sql($r, \@param, + "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'" + ); + } + + my($sday,$smon,$syear) = (localtime($beginning) )[ 3, 4, 5 ]; + $monthly_exempt_warning=1 if $sday != 1 && $beginning; + $smon++; $syear+=1900; + + my $eending = ( $ending == 4294967295 ) ? time : $ending; + my($eday,$emon,$eyear) = (localtime($eending) )[ 3, 4, 5 ]; + $emon++; $eyear+=1900; + + my $monthly_exemption = scalar_sql($r, [ 'taxnum' ], + "SELECT SUM(amount) FROM cust_tax_exempt where taxnum = ? ". + " AND ( year > $syear OR ( year = $syear and month >= $smon ) )". + " AND ( year < $eyear OR ( year = $eyear and month <= $emon ) )" + ); + #warn $r->taxnum(). ": $monthly_exemption\n"; + if ( $monthly_exemption ) { + $t -= $monthly_exemption; + $x += $monthly_exemption; + } + + $taxable += $t; + $regions{$label}->{'taxable'} += $t; + + $exempt += $x; + $regions{$label}->{'exempt'} += $x; + + $owed += $t * ($r->tax/100); + $regions{$label}->{'owed'} += $t * ($r->tax/100); + + if ( defined($regions{$label}->{'rate'}) + && $regions{$label}->{'rate'} != $r->tax.'%' ) { + $regions{$label}->{'rate'} = 'variable'; + } else { + $regions{$label}->{'rate'} = $r->tax.'%'; + } + +} + +my $taxwhere = "$from_join_cust $where"; +my @taxparam = @base_param; + +#foreach my $label ( keys %regions ) { +foreach my $r ( + qsearch( 'cust_main_county', + {}, + 'DISTINCT ON (country, state, county, taxname) *', + $gotcust + ) +) { + + #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n"; + + my $label = getlabel($r); + + my $fromwhere = $join_pkg. $where; + my @param = @base_param; + + #match itemdesc if necessary! + my $named_tax = + $r->taxname + ? 'AND itemdesc = '. dbh->quote($r->taxname) + : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; + my $x = scalar_sql($r, \@taxparam, + "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $taxwhere ". + "AND pkgnum = 0 $named_tax", + ); + $tax += $x; + $regions{$label}->{'tax'} += $x; + +} + +#ordering +my @regions = map $regions{$_}, + sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } + keys %regions; + +push @regions, { + 'label' => 'Total', + 'total' => $total, + 'exempt' => $exempt, + 'taxable' => $taxable, + 'rate' => '', + 'owed' => $owed, + 'tax' => $tax, +}; + +#-- + +sub getlabel { + my $r = shift; + + my $label; + if ( $r->tax == 0 ) { + #kludge to avoid "will not stay shared" warning + my $out = 'Out of taxable region(s)'; + $label = $out; + } elsif ( $r->taxname ) { + $label = $r->taxname; +# $regions{$label}->{'taxname'} = $label; +# push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country ); + } else { + $label = $r->country; + $label = $r->state.", $label" if $r->state; + $label = $r->county." county, $label" if $r->county; + #$label = $r->taxname. " ($label)" if $r->taxname; + } + return $label; +} + +#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up +#to FS::Report or FS::Record or who the fuck knows where) +sub scalar_sql { + my( $r, $param, $sql ) = @_; + #warn "$sql\n"; + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute( map $r->$_(), @$param ) + or die "Unexpected error executing statement $sql: ". $sth->errstr; + $sth->fetchrow_arrayref->[0] || 0; +} + +%> + +<%= header( "Sales Tax Report - $pbeginning through ".($pending||'now'), + menubar( 'Main Menu'=>$p, ) ) %> +<%= table() %> + <TR> + <TH ROWSPAN=2></TH> + <TH COLSPAN=3>Sales</TH> + <TH ROWSPAN=2>Rate</TH> + <TH ROWSPAN=2>Tax owed</TH> + <TH ROWSPAN=2>Tax invoiced</TH> + </TR> + <TR> + <TH>Total</TH> + <TH>Non-taxable</TH> + <TH>Taxable</TH> + </TR> + <% foreach my $region ( @regions ) { %> + <TR> + <TD><%= $region->{'label'} %></TD> + <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'total'} ) %></TD> + <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'exempt'} ) %></TD> + <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'taxable'} ) %></TD> + <TD ALIGN="right"><%= $region->{'rate'} %></TD> + <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'owed'} ) %></TD> + <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'tax'} ) %></TD> + </TR> + <% } %> + +</TABLE> + +<% if ( $monthly_exempt_warning ) { %> + <BR> + Partial-month tax reports (except for current month) may not be correct due + to month-granularity tax exemption (usually "texas tax"). For an accurate + report, start on the first of a month and end on the last day of a month (or + leave blank for to now). +<% } %> + +</BODY> +</HTML> + + diff --git a/httemplate/search/report_tax.html b/httemplate/search/report_tax.html new file mode 100755 index 000000000..85af212d7 --- /dev/null +++ b/httemplate/search/report_tax.html @@ -0,0 +1,44 @@ +<HTML> + <HEAD> + <TITLE>Tax Report Criteria</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Tax Report Criteria</H1> + <FORM ACTION="report_tax.cgi" METHOD="GET"> + Return <B>tax report</B> for period: + <TABLE> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> + </TABLE> + + <BR><INPUT TYPE="submit" VALUE="Get Report"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/sql.html b/httemplate/search/sql.html new file mode 100644 index 000000000..b28c045d1 --- /dev/null +++ b/httemplate/search/sql.html @@ -0,0 +1,7 @@ +<%= include( 'elements/search.html', + 'title' => 'Query Results', + 'name' => 'rows', + 'query' => 'SELECT '. ( $cgi->param('sql') + || eidiot('Empty query') ), + ) +%> diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi new file mode 100644 index 000000000..b84df1a03 --- /dev/null +++ b/httemplate/search/sqlradius.cgi @@ -0,0 +1,290 @@ +<%= include( '/elements/header.html', 'RADIUS Sessions', + include('/elements/menubar.html', + 'Main menu' => $p, # popurl(2), + ), + + ) +%> + +<% + ### + # parse cgi params + ### + + #sort of false laziness w/cust_pay.cgi + my $beginning = ''; + my $ending = ''; + if ( $cgi->param('beginning') + && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $beginning = str2time($1); + } + if ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $ending = str2time($1) + 86399; + } + if ( $cgi->param('begin') && $cgi->param('begin') =~ /^(\d+)$/ ) { + $beginning = $1; + } + if ( $cgi->param('end') && $cgi->param('end') =~ /^(\d+)$/ ) { + $ending = $1; + } + + my $cgi_svc_acct = ''; + if ( $cgi->param('svcnum') =~ /^(\d+)$/ ) { + $cgi_svc_acct = qsearchs( 'svc_acct', { 'svcnum' => $1 } ); + } elsif ( $cgi->param('username') =~ /^([^@]+)\@([^@]+)$/ ) { + my %search = { 'username' => $1 }; + my $svc_domain = qsearchs('svc_domain', { 'domain' => $2 } ); + if ( $svc_domain ) { + $search{'domsvc'} = $svc_domain->svcnum; + } else { + delete $search{'username'}; + } + $cgi_svc_acct = qsearchs( 'svc_acct', \%search ) + if keys %search; + } elsif ( $cgi->param('username') =~ /^(.+)$/ ) { + $cgi_svc_acct = qsearchs( 'svc_acct', { 'username' => $1 } ); + } + + my $ip = ''; + if ( $cgi->param('ip') =~ /^((\d+\.){3}\d+)$/ ) { + $ip = $1; + } + + my $prefix = $cgi->param('prefix'); + $prefix =~ s/\D//g; + if ( $prefix =~ /^(\d+)$/ ) { + $prefix = $1; + $prefix = "011$prefix" unless $prefix =~ /^1/; + } else { + $prefix = ''; + } + + ### + # field formatting subroutines + ### + + my %user2svc_acct = (); + my $user_format = sub { + my ( $user, $session, $part_export ) = @_; + + my $svc_acct = ''; + if ( exists $user2svc_acct{$user} ) { + $svc_acct = $user2svc_acct{$user}; + } else { + my %search = (); + if ( $part_export->exporttype eq 'sqlradius_withdomain' ) { + my $domain; + if ( $user =~ /^([^@]+)\@([^@]+)$/ ) { + $search{'username'} = $1; + $domain = $2; + } else { + $search{'username'} = $user; + $domain = $session->{'realm'}; + } + my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ); + if ( $svc_domain ) { + $search{'domsvc'} = $svc_domain->svcnum; + } else { + delete $search{'username'}; + } + } elsif ( $part_export->exporttype eq 'sqlradius' ) { + $search{'username'} = $user; + } else { + die 'unknown export type '. $part_export->exporttype. + " for $part_export\n"; + } + if ( keys %search ) { + my @svc_acct = + grep { qsearchs( 'export_svc', { + 'exportnum' => $part_export->exportnum, + 'svcpart' => $_->cust_svc->svcpart, + } ) + } qsearch( 'svc_acct', \%search ); + if ( @svc_acct ) { + warn 'multiple svc_acct records for user $user found; '. + 'using first arbitrarily' + if scalar(@svc_acct) > 1; + $user2svc_acct{$user} = $svc_acct = shift @svc_acct; + } + } + } + + if ( $svc_acct ) { + my $svcnum = $svc_acct->svcnum; + qq(<A HREF="${p}view/svc_acct.cgi?$svcnum"><B>$user</B></A>); + } else { + "<B>$user</B>"; + } + + }; + + my $customer_format = sub { + my( $unused, $session ) = @_; + return ' ' unless exists $user2svc_acct{$session->{'username'}}; + my $svc_acct = $user2svc_acct{$session->{'username'}}; + my $cust_pkg = $svc_acct->cust_svc->cust_pkg; + return ' ' unless $cust_pkg; + my $cust_main = $cust_pkg->cust_main; + + qq!<A HREF="${p}view/cust_main.cgi?!. $cust_main->custnum. '">'. + $cust_pkg->cust_main->name. '</A>'; + }; + + my $time_format = sub { + my $time = shift; + return ' ' if $time == 0; + my $pretty = time2str('%T%P %a %b %o %Y', $time ); + $pretty =~ s/ (\d)(st|dn|rd|th)/$1$2/; + $pretty; + }; + + my $duration_format = sub { + my $seconds = shift; + my $hour = int($seconds/3600); + my $min = int( ($seconds%3600) / 60 ); + my $sec = $seconds%60; + '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0>'. + '<TR><TD ALIGN="right">'. + ( $hour ? "<B>$hour</B>h" : ' ' ). + '</TD><TD ALIGN="right">'. + ( ( $hour || $min ) ? "<B>$min</B>m" : ' ' ). + '</TD><TD ALIGN="right">'. + "<B>$sec</B>s". + '</TD></TR></TABLE>'; + }; + + my $octets_format = sub { + my $octets = shift; + my $megs = $octets / 1048576; + sprintf('<B>%.3f</B> megs', $megs); + #my $gigs = $octets / 1073741824 + #sprintf('<B>%.3f</B> gigabytes', $gigs); + }; + + ### + # the fields + ### + + tie my %fields, 'Tie::IxHash', + 'username' => { + name => 'User', + attrib => 'UserName', + fmt => $user_format, + align => 'left', + }, + 'realm' => { + name => 'Realm', + attrib => 'Realm', + align => 'left', + }, + 'dummy' => { + name => 'Customer', + attrib => '', + fmt => $customer_format, + align => 'left', + }, + 'framedipaddress' => { + name => 'IP Address', + attrib => 'Framed-IP-Address', + fmt => sub { my $ip = shift; + length($ip) ? $ip : ' '; + }, + align => 'right', + }, + 'acctstarttime' => { + name => 'Start time', + attrib => 'Acct-Start-Time', + fmt => $time_format, + align => 'left', + }, + 'acctstoptime' => { + name => 'End time', + attrib => 'Acct-Stop-Time', + fmt => $time_format, + align => 'left', + }, + 'acctsessiontime' => { + name => 'Duration', + attrib => 'Acct-Session-Time', + fmt => $duration_format, + align => 'right', + }, + 'acctinputoctets' => { + name => 'Upload', # (from user)', + attrib => 'Acct-Input-Octets', + fmt => $octets_format, + align => 'right', + }, + 'acctoutputoctets' => { + name => 'Download', # (to user)', + attrib => 'Acct-Output-Octets', + fmt => $octets_format, + align => 'right', + }, + ; + $fields{$_}->{fmt} ||= sub { length($_[0]) ? shift : ' '; } + foreach keys %fields; + + ### + # and finally, display the thing + ### + + foreach my $part_export ( + #grep $_->can('usage_sessions'), qsearch( 'part_export' ) + qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ), + qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } ) + ) { + %user2svc_acct = (); + + my $efields = tie my %efields, 'Tie::IxHash', %fields; + delete $efields{'framedipaddress'} if $part_export->option('hide_ip'); + if ( $part_export->option('hide_data') ) { + delete $efields{$_} foreach qw(acctinputoctets acctoutputoctets); + } + if ( $part_export->option('show_called_station') ) { + $efields->Splice(1, 0, + 'calledstationid' => { + 'name' => 'Destination', + 'attrib' => 'Called-Station-ID', + 'fmt' => + sub { length($_[0]) ? shift : ' '; }, + 'align' => 'left', + }, + ); + } + +%> + +<%= $part_export->exporttype %> to <%= $part_export->machine %><BR> +<%= include( '/elements/table.html' ) %> +<TR> + <% foreach my $field ( keys %efields ) { %> + <TH> + <%= $efields{$field}->{name} %><BR> + <FONT SIZE=-2><%= $efields{$field}->{attrib} %></FONT> + </TH> + <% } %> +</TR> +<% foreach my $session ( + @{ $part_export->usage_sessions( + $beginning, $ending, $cgi_svc_acct, $ip, $prefix, ) } + ) { +%> + <TR> + <% foreach my $field ( keys %efields ) { %> + <TD ALIGN="<%= $efields{$field}->{align} %>"> + <%= &{ $efields{$field}->{fmt} }( $session->{$field}, + $session, + $part_export, + ) + %> + </TD> + <% } %> + </TR> +<% } %> + +</TABLE> +<BR><BR> + +<% } %> diff --git a/httemplate/search/sqlradius.html b/httemplate/search/sqlradius.html new file mode 100644 index 000000000..8f4878dbc --- /dev/null +++ b/httemplate/search/sqlradius.html @@ -0,0 +1,94 @@ +<%= include( '/elements/header.html', 'Search RADIUS sessions', '', '', ' +<LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> +<SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> +<SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> +<SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> +') %> +<FORM NAME="OneTrueForm" ACTION="sqlradius.cgi" METHOD="GET"> +<% #include( '/elements/table.html' ) %> +<%= ntable('#cccccc') %> +<TR> + <TD ALIGN="right">Username: </TD> + <TD><INPUT TYPE="text" NAME="username"></TD> +</TR> +<TR> + <TD></TD> + <TD><FONT SIZE="-1"><I>(leave blank to show all users)</I></FONT></TD> +</TR> + +<% my @part_export = qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ); + push @part_export, + qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } ); +%> + +<% if ( grep { ! $_->option('hide_ip') } @part_export ) { %> + <TR> + <TD ALIGN="right">IP address: </TD> + <TD><INPUT TYPE="text" NAME="ip"></TD> + </TR> + <TR> + <TD></TD> + <TD><FONT SIZE="-1"><I>(leave blank to show all IPs)</I></FONT></TD> + </TR> +<% } %> + +<% if ( grep { $_->option('show_called_station') } @part_export ) { %> + <TR> + <TD ALIGN="right">Destination prefix:</TD> + <TD><INPUT TYPE="text" NAME="prefix"></TD> + </TR> + <TR> + <TD></TD> + <TD><FONT SIZE="-1"><I>(country code or country code and prefix)</I></FONT></TD> + </TR> + <TR> + <TD></TD> + <TD><FONT SIZE="-1"><I>(leave blank to show all destinations)</I></FONT></TD> + </TR> +<% } %> + +<TR> + <TD ALIGN="right">From: </TD> + <TD> + <INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"> + </TD> + <SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); + </SCRIPT> +</TR> +<TR> + <TD></TD> + <TD><i>m/d/y</i></TD> +</TR> +<TR> + <TD ALIGN="right">To: </TD> + <TD> + <INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor:pointer" TITLE="Select date"> + </TD> + <SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); + </SCRIPT> +</TR> +<TR> + <TD></TD> + <TD><i>m/d/y</i> + <BR><FONT SIZE="-1">(leave one or both dates blank for an open-ended search)</FONT> + </TD> +</TR> +</TABLE> +<BR><INPUT TYPE="submit" VALUE="View sessions"> +</FORM> +</BODY> +</HTML> + + diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi new file mode 100755 index 000000000..1e4a03d84 --- /dev/null +++ b/httemplate/search/svc_acct.cgi @@ -0,0 +1,294 @@ +<% + +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); + +my $orderby = ''; #removeme + +my $limit = ''; +$limit .= "LIMIT $maxrecords" if $maxrecords; + +my $offset = $cgi->param('offset') || 0; +$limit .= " OFFSET $offset" if $offset; + +my $total; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors + +my $unlinked = ''; +if ( $query =~ /^UN_(.*)$/ ) { + $query = $1; + my $empty = driver_name eq 'Pg' ? qq('') : qq(""); + if ( driver_name eq 'mysql' ) { + $unlinked = "LEFT JOIN cust_svc ON cust_svc.svcnum = svc_acct.svcnum + WHERE cust_svc.pkgnum IS NULL + OR cust_svc.pkgnum = 0 + OR cust_svc.pkgnum = $empty"; + } else { + $unlinked = " + WHERE 0 < + ( SELECT count(*) FROM cust_svc + WHERE cust_svc.svcnum = svc_acct.svcnum + AND ( pkgnum IS NULL OR pkgnum = 0 ) + ) + "; + } +} + +my $tblname = driver_name eq 'mysql' ? 'svc_acct.' : ''; +my(@svc_acct, $sortby); +if ( $query eq 'svcnum' ) { + $sortby=\*svcnum_sort; + $orderby = "ORDER BY ${tblname}svcnum"; +} elsif ( $query eq 'username' ) { + $sortby=\*username_sort; + $orderby = "ORDER BY ${tblname}username"; +} elsif ( $query eq 'uid' ) { + $sortby=\*uid_sort; + $orderby = ( $unlinked ? ' AND' : ' WHERE' ). + " ${tblname}uid IS NOT NULL ORDER BY ${tblname}uid"; +} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) { + $unlinked .= ( $unlinked ? 'AND' : 'WHERE' ). + " popnum = $1"; + $sortby=\*username_sort; + $orderby = "ORDER BY ${tblname}username"; +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + $unlinked .= ( $unlinked ? ' AND' : ' WHERE' ). + " $1 = ( SELECT svcpart FROM cust_svc ". + " WHERE cust_svc.svcnum = svc_acct.svcnum ) "; + $sortby=\*uid_sort; + #$sortby=\*svcnum_sort; +} else { + $sortby=\*uid_sort; + @svc_acct = @{&usernamesearch}; +} + + +if ( $query eq 'svcnum' + || $query eq 'username' + || $query eq 'uid' + || $cgi->param('popnum') =~ /^(\d+)$/ + || $cgi->param('svcpart') =~ /^(\d+)$/ + ) { + + my $statement = "SELECT COUNT(*) FROM svc_acct $unlinked"; + my $sth = dbh->prepare($statement) + or die dbh->errstr. " doing $statement"; + $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; + + $total = $sth->fetchrow_arrayref->[0]; + + @svc_acct = qsearch('svc_acct', {}, '', "$unlinked $orderby $limit"); + +} + +if ( scalar(@svc_acct) == 1 ) { + my($svcnum)=$svc_acct[0]->svcnum; + print $cgi->redirect(popurl(2). "view/svc_acct.cgi?$svcnum"); #redirect + #exit; +} elsif ( scalar(@svc_acct) == 0 ) { #error +%> +<!-- mason kludge --> +<% + idiot("Account not found"); +} else { +%> +<!-- mason kludge --> +<% + $total ||= scalar(@svc_acct); + + #begin pager + my $pager = ''; + if ( $total != scalar(@svc_acct) && $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("Account Search Results",menubar('Main Menu'=>popurl(2))), + "$total matching accounts found<BR><BR>$pager", + &table(), <<END; + <TR> + <TH><FONT SIZE=-1>#</FONT></TH> + <TH><FONT SIZE=-1>Username</FONT></TH> + <TH><FONT SIZE=-1>Domain</FONT></TH> + <TH><FONT SIZE=-1>UID</FONT></TH> + <TH><FONT SIZE=-1>Service</FONT></TH> + <TH><FONT SIZE=-1>Cust#</FONT></TH> + <TH><FONT SIZE=-1>(bill) name</FONT></TH> + <TH><FONT SIZE=-1>company</FONT></TH> +END + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <<END; + <TH><FONT SIZE=-1>(service) name</FONT></TH> + <TH><FONT SIZE=-1>company</FONT></TH> +END + } + print "</TR>"; + + my(%saw,$svc_acct); + my $p = popurl(2); + foreach $svc_acct ( + sort $sortby grep(!$saw{$_->svcnum}++, @svc_acct) + ) { + my $cust_svc = qsearchs('cust_svc', { 'svcnum' => $svc_acct->svcnum }) + or die "No cust_svc record for svcnum ". $svc_acct->svcnum; + my $part_svc = qsearchs('part_svc', { 'svcpart' => $cust_svc->svcpart }) + or die "No part_svc record for svcpart ". $cust_svc->svcpart; + + my $domain; + my $svc_domain = qsearchs('svc_domain', { 'svcnum' => $svc_acct->domsvc }); + if ( $svc_domain ) { + $domain = "<A HREF=\"${p}view/svc_domain.cgi?". $svc_domain->svcnum. + "\">". $svc_domain->domain. "</A>"; + } else { + die "No svc_domain.svcnum record for svc_acct.domsvc: ". + $svc_acct->domsvc; + } + my($cust_pkg,$cust_main); + if ( $cust_svc->pkgnum ) { + $cust_pkg = qsearchs('cust_pkg', { 'pkgnum' => $cust_svc->pkgnum }) + or die "No cust_pkg record for pkgnum ". $cust_svc->pkgnum; + $cust_main = qsearchs('cust_main', { 'custnum' => $cust_pkg->custnum }) + or die "No cust_main record for custnum ". $cust_pkg->custnum; + } + my($svcnum, $username, $uid, $svc, $custnum, $last, $first, $company) = ( + $svc_acct->svcnum, + $svc_acct->getfield('username'), + $svc_acct->getfield('uid'), + $part_svc->svc, + $cust_svc->pkgnum ? $cust_main->custnum : '', + $cust_svc->pkgnum ? $cust_main->getfield('last') : '', + $cust_svc->pkgnum ? $cust_main->getfield('first') : '', + $cust_svc->pkgnum ? $cust_main->company : '', + ); + my($pcustnum) = $custnum + ? "<A HREF=\"${p}view/cust_main.cgi?$custnum\"><FONT SIZE=-1>$custnum</FONT></A>" + : "<I>(unlinked)</I>" + ; + my $pname = $custnum ? "<A HREF=\"${p}view/cust_main.cgi?$custnum\">$last, $first</A>" : ''; + my $pcompany = $custnum ? "<A HREF=\"${p}view/cust_main.cgi?$custnum\">$company</A>" : ''; + my($pship_name, $pship_company); + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + my($ship_last, $ship_first, $ship_company) = ( + $cust_svc->pkgnum ? ( $cust_main->ship_last || $last ) : '', + $cust_svc->pkgnum ? ( $cust_main->ship_last + ? $cust_main->ship_first + : $first + ) : '', + $cust_svc->pkgnum ? ( $cust_main->ship_last + ? $cust_main->ship_company + : $company + ) : '', + ); + $pship_name = $custnum ? "<A HREF=\"${p}view/cust_main.cgi?$custnum\">$ship_last, $ship_first</A>" : ''; + $pship_company = $custnum ? "<A HREF=\"${p}view/cust_main.cgi?$custnum\">$ship_company</A>" : ''; + } + print <<END; + <TR> + <TD><A HREF="${p}view/svc_acct.cgi?$svcnum"><FONT SIZE=-1>$svcnum</FONT></A></TD> + <TD><A HREF="${p}view/svc_acct.cgi?$svcnum"><FONT SIZE=-1>$username</FONT></A></TD> + <TD><FONT SIZE=-1>$domain</FONT></TD> + <TD><A HREF="${p}view/svc_acct.cgi?$svcnum"><FONT SIZE=-1>$uid</FONT></A></TD> + <TD><FONT SIZE=-1>$svc</FONT></TH> + <TD><FONT SIZE=-1>$pcustnum</FONT></TH> + <TD><FONT SIZE=-1>$pname<FONT></TH> + <TD><FONT SIZE=-1>$pcompany</FONT></TH> +END + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <<END; + <TD><FONT SIZE=-1>$pship_name<FONT></TH> + <TD><FONT SIZE=-1>$pship_company</FONT></TH> +END + } + print "</TR>"; + + } + + print "</TABLE>$pager<BR>". + '</BODY></HTML>'; + +} + +sub svcnum_sort { + $a->getfield('svcnum') <=> $b->getfield('svcnum'); +} + +sub username_sort { + $a->getfield('username') cmp $b->getfield('username'); +} + +sub uid_sort { + $a->getfield('uid') <=> $b->getfield('uid'); +} + +sub usernamesearch { + + my @svc_acct; + + my %username_type; + foreach ( $cgi->param('username_type') ) { + $username_type{$_}++; + } + + $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text + my $username = $1; + + if ( $username_type{'Exact'} || $username_type{'Fuzzy'} ) { + push @svc_acct, qsearch( 'svc_acct', + { 'username' => { 'op' => 'ILIKE', + 'value' => $username } } ); + } + + if ( $username_type{'Substring'} || $username_type{'All'} ) { + push @svc_acct, qsearch( 'svc_acct', + { 'username' => { 'op' => 'ILIKE', + 'value' => "%$username%" } } ); + } + + if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { + &FS::svc_acct::check_and_rebuild_fuzzyfiles; + my $all_username = &FS::svc_acct::all_username; + + my %username; + if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { + foreach ( amatch($username, [ qw(i) ], @$all_username) ) { + $username{$_}++; + } + } + + #if ($username_type{'Sound-alike'}) { + #} + + foreach ( keys %username ) { + push @svc_acct, qsearch('svc_acct',{'username'=>$_}); + } + + } + + #[ qsearch('svc_acct',{'username'=>$username}) ]; + \@svc_acct; + +} + +%> diff --git a/httemplate/search/svc_acct.html b/httemplate/search/svc_acct.html new file mode 100755 index 000000000..c504c2f34 --- /dev/null +++ b/httemplate/search/svc_acct.html @@ -0,0 +1,19 @@ +<HTML> + <HEAD> + <TITLE>Account Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Account Search + </FONT> + <BR><BR> + <FORM ACTION="svc_acct.cgi" METHOD="GET"> + Search for <B>username</B>: + <INPUT TYPE="text" NAME="username"> + + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/svc_broadband.cgi b/httemplate/search/svc_broadband.cgi new file mode 100755 index 000000000..efadce600 --- /dev/null +++ b/httemplate/search/svc_broadband.cgi @@ -0,0 +1,96 @@ +<% + +my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors +my(@svc_broadband,$sortby); +if ( $query eq 'svcnum' ) { + $sortby=\*svcnum_sort; + @svc_broadband=qsearch('svc_broadband',{}); +} elsif ( $query eq 'blocknum' ) { + $sortby=\*blocknum_sort; + @svc_broadband=qsearch('svc_broadband',{}); +} else { + $cgi->param('ip_addr') =~ /^(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})$/; + my($ip_addr)=$1; + @svc_broadband = qsearchs('svc_broadband',{'ip_addr'=>$ip_addr}); +} + +my %routerbyblock = (); +foreach my $router (qsearch('router', {})) { + foreach ($router->addr_block) { + $routerbyblock{$_->blocknum} = $router; + } +} + +if ( scalar(@svc_broadband) == 1 ) { + print $cgi->redirect(popurl(2). "view/svc_broadband.cgi?". $svc_broadband[0]->svcnum); + #exit; +} elsif ( scalar(@svc_broadband) == 0 ) { +%> +<!-- mason kludge --> +<% + eidiot "No matching ip address found!\n"; +} else { +%> +<!-- mason kludge --> +<% + my($total)=scalar(@svc_broadband); + print header("IP Address Search Results",''), <<END; + + $total matching broadband services found + <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> + <TR> + <TH>Service #</TH> + <TH>Router</TH> + <TH>IP Address</TH> + </TR> +END + + foreach my $svc_broadband ( + sort $sortby (@svc_broadband) + ) { + my($svcnum,$ip_addr,$routername,$routernum)=( + $svc_broadband->svcnum, + $svc_broadband->ip_addr, + $routerbyblock{$svc_broadband->blocknum}->routername, + $routerbyblock{$svc_broadband->blocknum}->routernum, + ); + + my $rowspan = 1; + + print <<END; + <TR> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_broadband.cgi?$svcnum">$svcnum</A></TD> + <TD ROWSPAN=$rowspan><A HREF="${p}view/router.cgi?$routernum">$routername</A></TD> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_broadband.cgi?$svcnum">$ip_addr</A></TD> +END + + #print @rows; + print "</TR>"; + + } + + print <<END; + </TABLE> + </BODY> +</HTML> +END + +} + +sub svcnum_sort { + $a->getfield('svcnum') <=> $b->getfield('svcnum'); +} + +sub blocknum_sort { + if ($a->getfield('blocknum') == $b->getfield('blocknum')) { + $a->getfield('ip_addr') cmp $b->getfield('ip_addr'); + } else { + $a->getfield('blocknum') cmp $b->getfield('blocknum'); + } +} + + +%> diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi new file mode 100755 index 000000000..06041e03b --- /dev/null +++ b/httemplate/search/svc_domain.cgi @@ -0,0 +1,83 @@ +<% + +my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors + +my $orderby = 'ORDER BY svcnum'; +my $join = ''; +my %svc_domain = (); +my $extra_sql = ''; +if ( $query eq 'svcnum' ) { + #$orderby = 'ORDER BY svcnum'; +} elsif ( $query eq 'domain' ) { + $orderby = 'ORDER BY domain'; +} elsif ( $query eq 'UN_svcnum' ) { + #$orderby = 'ORDER BY svcnum'; + $join = 'LEFT JOIN cust_svc USING ( svcnum )'; + $extra_sql = ' WHERE pkgnum IS NULL'; +} elsif ( $query eq 'UN_domain' ) { + $orderby = 'ORDER BY domain'; + $join = 'LEFT JOIN cust_svc USING ( svcnum )'; + $extra_sql = ' WHERE pkgnum IS NULL'; +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + #$orderby = 'ORDER BY svcnum'; + $join = 'LEFT JOIN cust_svc USING ( svcnum )'; + $extra_sql = " WHERE svcpart = $1"; +} else { + $cgi->param('domain') =~ /^([\w\-\.]+)$/; + $join = ''; + $svc_domain{'domain'} = $1; +} + +my $count_query = "SELECT COUNT(*) FROM svc_domain $join $extra_sql"; +if ( keys %svc_domain ) { + $count_query .= ' WHERE '. + join(' AND ', map "$_ = ". dbh->quote($svc_domain{$_}), + keys %svc_domain + ); + +} + +my $sql_query = { + 'table' => 'svc_domain', + 'hashref' => \%svc_domain, + 'select' => join(', ', + 'svc_domain.*', + map "cust_main.$_", qw(custnum last first company) + ), + 'extra_sql' => "$extra_sql $orderby", + 'addl_from' => 'LEFT JOIN cust_svc USING ( svcnum )'. + 'LEFT JOIN cust_pkg USING ( pkgnum )'. + 'LEFT JOIN cust_main USING ( custnum )', +}; + +my $link = [ "${p}view/svc_domain.cgi?", 'svcnum' ]; + +#smaller false laziness w/svc_*.cgi here +my $link_cust = sub { + my $svc_x = shift; + $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +}; + +%><%= include ('elements/search.html', + 'title' => "Domain Search Results", + 'name' => 'domains', + 'query' => $sql_query, + 'count_query' => $count_query, + 'redirect' => $link, + 'header' => [ '#', + 'Domain', + 'Customer', + ], + 'fields' => [ 'svcnum', + 'domain', + \&FS::svc_Common::cust_name, + ], + 'links' => [ $link, + $link, + $link_cust, + ], + ) +%> diff --git a/httemplate/search/svc_domain.html b/httemplate/search/svc_domain.html new file mode 100755 index 000000000..b759102f4 --- /dev/null +++ b/httemplate/search/svc_domain.html @@ -0,0 +1,19 @@ +<HTML> + <HEAD> + <TITLE>Domain Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Domain Search + </FONT> + <BR><BR> + <FORM ACTION="svc_domain.cgi" METHOD="GET"> + Search for <B>domain</B>: + <INPUT TYPE="text" NAME="domain"> + + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/svc_external.cgi b/httemplate/search/svc_external.cgi new file mode 100755 index 000000000..c5ac13498 --- /dev/null +++ b/httemplate/search/svc_external.cgi @@ -0,0 +1,101 @@ +<% + +my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors +my(@svc_external,$sortby); +if ( $query eq 'svcnum' ) { + $sortby=\*svcnum_sort; + @svc_external=qsearch('svc_external',{}); +} elsif ( $query eq 'id' ) { + $sortby=\*id_sort; + @svc_external=qsearch('svc_external',{}); +} elsif ( $query eq 'UN_svcnum' ) { + $sortby=\*svcnum_sort; + @svc_external = grep qsearchs('cust_svc',{ + 'svcnum' => $_->svcnum, + 'pkgnum' => '', + }), qsearch('svc_external',{}); +} elsif ( $query eq 'UN_id' ) { + $sortby=\*id_sort; + @svc_external = grep qsearchs('cust_svc',{ + 'svcnum' => $_->svcnum, + 'pkgnum' => '', + }), qsearch('svc_external',{}); +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + @svc_external = + qsearch( 'svc_external', {}, '', + " WHERE $1 = ( SELECT svcpart FROM cust_svc ". + " WHERE cust_svc.svcnum = svc_external.svcnum ) " + ); + $sortby=\*svcnum_sort; +} else { + $cgi->param('id') =~ /^([\w\-\.]+)$/; + my($id)=$1; + #push @svc_domain, qsearchs('svc_domain',{'domain'=>$domain}); + @svc_external = qsearchs('svc_external',{'id'=>$id}); +} + +if ( scalar(@svc_external) == 1 ) { + print $cgi->redirect(popurl(2). "view/svc_external.cgi?". $svc_external[0]->svcnum); + #exit; +} elsif ( scalar(@svc_external) == 0 ) { +%> +<!-- mason kludge --> +<% + eidiot "No matching external services found!\n"; +} else { +%> +<!-- mason kludge --> +<%= header("External Search Results",'') %> + + <%= scalar(@svc_external) %> matching external services found + <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> + <TR> + <TH>Service #</TH> + <TH><%= FS::Msgcat::_gettext('svc_external-id') || 'External ID' %></TH> + <TH><%= FS::Msgcat::_gettext('svc_external-title') || 'Title' %></TH> + </TR> + +<% + foreach my $svc_external ( + sort $sortby (@svc_external) + ) { + my($svcnum, $id, $title)=( + $svc_external->svcnum, + $svc_external->id, + $svc_external->title, + ); + + my $rowspan = 1; + + print <<END; + <TR> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$svcnum</A></TD> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$id</A></TD> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$title</A></TD> +END + + #print @rows; + print "</TR>"; + + } + + print <<END; + </TABLE> + </BODY> +</HTML> +END + +} + +sub svcnum_sort { + $a->getfield('svcnum') <=> $b->getfield('svcnum'); +} + +sub id_sort { + $a->getfield('id') <=> $b->getfield('id'); +} + +%> diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi new file mode 100755 index 000000000..1c1c6e4dd --- /dev/null +++ b/httemplate/search/svc_forward.cgi @@ -0,0 +1,100 @@ +<% + +my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors +my $orderby; +if ( $query eq 'svcnum' ) { + $orderby = 'ORDER BY svcnum'; +} else { + eidiot('unimplemented'); +} + +my $count_query = 'SELECT COUNT(*) FROM svc_forward'; +my $sql_query = { + 'table' => 'svc_forward', + 'hashref' => {}, + 'select' => join(', ', + 'svc_forward.*', + map "cust_main.$_", qw(custnum last first company) + ), + 'extra_sql' => $orderby, + 'addl_from' => 'LEFT JOIN cust_svc USING ( svcnum )'. + 'LEFT JOIN cust_pkg USING ( pkgnum )'. + 'LEFT JOIN cust_main USING ( custnum )', +}; + +# <TH>Service #<BR><FONT SIZE=-1>(click to view forward)</FONT></TH> +# <TH>Mail to<BR><FONT SIZE=-1>(click to view account)</FONT></TH> +# <TH>Forwards to<BR><FONT SIZE=-1>(click to view account)</FONT></TH> + +my $link = [ "${p}view/svc_forward.cgi?", 'svcnum' ]; + +my $format_src = sub { + my $svc_forward = shift; + if ( $svc_forward->srcsvc_acct ) { + $svc_forward->srcsvc_acct->email; + } else { + my $src = $svc_forward->src; + $src = "<I>(anything)</I>$src" if $src =~ /^@/; + $src; + } +}; + +my $link_src = sub { + my $svc_forward = shift; + if ( $svc_forward->srcsvc_acct ) { + [ "${p}view/svc_acct.cgi?", 'srcsvc' ]; + } else { + ''; + } +}; + +my $format_dst = sub { + my $svc_forward = shift; + if ( $svc_forward->dstsvc_acct ) { + $svc_forward->dstsvc_acct->email; + } else { + $svc_forward->dst; + } +}; + +my $link_dst = sub { + my $svc_forward = shift; + if ( $svc_forward->dstsvc_acct ) { + [ "${p}view/svc_acct.cgi?", 'dstsvc' ]; + } else { + ''; + } +}; + +#smaller false laziness w/svc_*.cgi here +my $link_cust = sub { + my $svc_x = shift; + $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +}; + +%><%= include ('elements/search.html', + 'title' => "Mail forward Search Results", + 'name' => 'mail forwards', + 'query' => $sql_query, + 'count_query' => $count_query, + 'redirect' => $link, + 'header' => [ '#', + 'Mail to', + 'Forwards to', + 'Customer', + ], + 'fields' => [ 'svcnum', + $format_src, + $format_dst, + \&FS::svc_Common::cust_name, + ], + 'links' => [ $link, + $link_src, + $link_dst, + $link_cust, + ], + ) +%> diff --git a/httemplate/search/svc_www.cgi b/httemplate/search/svc_www.cgi new file mode 100755 index 000000000..de1f5dd78 --- /dev/null +++ b/httemplate/search/svc_www.cgi @@ -0,0 +1,60 @@ +<% + +#my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors +my $orderby; +if ( $query eq 'svcnum' ) { + $orderby = 'ORDER BY svcnum'; +} else { + eidiot('unimplemented'); +} + +my $count_query = 'SELECT COUNT(*) FROM svc_www'; +my $sql_query = { + 'table' => 'svc_www', + 'hashref' => {}, + 'select' => join(', ', + 'svc_www.*', + map "cust_main.$_", qw(custnum last first company) + ), + 'extra_sql' => $orderby, + 'addl_from' => 'LEFT JOIN cust_svc USING ( svcnum )'. + 'LEFT JOIN cust_pkg USING ( pkgnum )'. + 'LEFT JOIN cust_main USING ( custnum )', +}; + +my $link = [ "${p}view/svc_www.cgi?", 'svcnum', ]; +#my $dlink = [ "${p}view/svc_www.cgi?", 'svcnum', ]; +my $ulink = [ "${p}view/svc_acct.cgi?", 'usersvc', ]; + +#smaller false laziness w/svc_*.cgi here +my $link_cust = sub { + my $svc_x = shift; + $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +}; + +%><%= include( 'elements/search.html', + 'title' => 'Virtual Host Search Results', + 'name' => 'virtual hosts', + 'query' => $sql_query, + 'count_query' => $count_query, + 'redirect' => $link, + 'header' => [ '#', + 'Zone', + 'User', + 'Customer', + ], + 'fields' => [ 'svcnum', + sub { $_[0]->domain_record->zone }, + sub { $_[0]->svc_acct->email }, + \&FS::svc_Common::cust_name, + ], + 'links' => [ $link, + '', + $ulink, + $link_cust, + ], + ) +%> |