diff options
Diffstat (limited to 'httemplate/search')
37 files changed, 0 insertions, 4494 deletions
diff --git a/httemplate/search/cust_bill.cgi b/httemplate/search/cust_bill.cgi deleted file mode 100755 index 5b0538ca3..000000000 --- a/httemplate/search/cust_bill.cgi +++ /dev/null @@ -1,165 +0,0 @@ -<% - -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, $tot_amount, $tot_balance); - -my(@cust_bill); -if ( $cgi->keywords ) { - my($query) = $cgi->keywords; - 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 )"; - my @where; - if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) { - my($open, $days, $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; - } else { - die "unknown query string $query"; - } - - my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; - - my $statement = "SELECT COUNT(*), sum(charged), sum($owed) - FROM cust_bill $extra_sql"; - my $sth = dbh->prepare($statement) or die dbh->errstr. " doing $statement"; - $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; - - ( $total, $tot_amount, $tot_balance ) = @{$sth->fetchrow_arrayref}; - - @cust_bill = qsearch( - 'cust_bill', - {}, - "cust_bill.*, $owed as owed", - "$extra_sql $orderby $limit" - ); -} else { - $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/; - my $invnum = $2; - @cust_bill = qsearchs('cust_bill', { 'invnum' => $invnum } ); - $total = scalar(@cust_bill); -} - -#if ( scalar(@cust_bill) == 1 ) { -if ( $total == 1 ) { - my $invnum = $cust_bill[0]->invnum; - print $cgi->redirect(popurl(2). "view/cust_bill.cgi?$invnum"); #redirect -} elsif ( scalar(@cust_bill) == 0 ) { -%> -<!-- mason kludge --> -<% - eidiot("Invoice not found."); -} else { -%> -<!-- mason kludge --> -<% - - #begin pager - my $pager = ''; - if ( $total != scalar(@cust_bill) && $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("Invoice Search Results", menubar( - 'Main Menu', popurl(2) - )). - "$total matching invoices found<BR>". - "\$$tot_balance total balance<BR>". - "\$$tot_amount total amount<BR>". - "<BR>$pager". table(). <<END; - <TR> - <TH></TH> - <TH>Balance</TH> - <TH>Amount</TH> - <TH>Date</TH> - <TH>Contact name</TH> - <TH>Company</TH> - </TR> -END - - foreach my $cust_bill ( @cust_bill ) { - my($invnum, $owed, $charged, $date ) = ( - $cust_bill->invnum, - sprintf("%.2f", $cust_bill->getfield('owed')), - sprintf("%.2f", $cust_bill->charged), - $cust_bill->_date, - ); - my $pdate = time2str("%b %d %Y", $date); - - my $rowspan = 1; - - my $view = popurl(2). "view/cust_bill.cgi?$invnum"; - print <<END; - <TR> - <TD ROWSPAN=$rowspan><A HREF="$view">$invnum</A></TD> - <TD ROWSPAN=$rowspan ALIGN="right"><A HREF="$view">\$$owed</A></TD> - <TD ROWSPAN=$rowspan ALIGN="right"><A HREF="$view">\$$charged</A></TD> - <TD ROWSPAN=$rowspan><A HREF="$view">$pdate</A></TD> -END - my $custnum = $cust_bill->custnum; - my $cust_main = qsearchs('cust_main', { 'custnum' => $custnum } ); - if ( $cust_main ) { - my $cview = popurl(2). "view/cust_main.cgi?". $cust_main->custnum; - my ( $name, $company ) = ( - $cust_main->last. ', '. $cust_main->first, - $cust_main->company, - ); - print <<END; - <TD ROWSPAN=$rowspan><A HREF="$cview">$name</A></TD> - <TD ROWSPAN=$rowspan><A HREF="$cview">$company</A></TD> -END - } else { - print <<END - <TD ROWSPAN=$rowspan COLSPAN=2>WARNING: couldn't find cust_main.custnum $custnum (cust_bill.invnum $invnum)</TD> -END - } - - print "</TR>"; - } - $tot_balance = sprintf("%.2f", $tot_balance); - $tot_amount = sprintf("%.2f", $tot_amount); - print "</TABLE>$pager<BR>". table(). <<END; - <TR><TD> </TD><TH>Total<BR>Balance</TH><TH>Total<BR>Amount</TH></TR> - <TR><TD></TD><TD ALIGN="right">\$$tot_balance</TD><TD ALIGN="right">\$$tot_amount</TD></TD></TR> - </TABLE> - </BODY> -</HTML> -END - -} - -%> diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html deleted file mode 100755 index 2108653a8..000000000 --- a/httemplate/search/cust_bill.html +++ /dev/null @@ -1,179 +0,0 @@ -<% - 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', - FS::UI::Web::cust_sql_fields(), - "$owed as owed", - ), - 'extra_sql' => "$extra_sql $orderby" - }; - - } - - my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; - my $clink = sub { - my $cust_bill = shift; - $cust_bill->cust_main_custnum - ? [ "${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 #', - 'Balance', - 'Amount', - 'Date', - FS::UI::Web::cust_header(), - ], - 'fields' => [ - 'invnum', - sub { sprintf($money_char.'%.2f', shift->get('owed') ) }, - sub { sprintf($money_char.'%.2f', shift->charged ) }, - sub { time2str('%b %d %Y', shift->_date ) }, - \&FS::UI::Web::cust_fields, - ], - 'align' => 'rrrrll', - 'links' => [ - $link, - $link, - $link, - $link, - ( map { $clink } FS::UI::Web::cust_header() ), - ], - - ) -%> diff --git a/httemplate/search/cust_bill_event.cgi b/httemplate/search/cust_bill_event.cgi deleted file mode 100644 index d82a83368..000000000 --- a/httemplate/search/cust_bill_event.cgi +++ /dev/null @@ -1,137 +0,0 @@ -<% - -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"; - -if ( $cgi->param('failed') ) { - $where .= " AND statustext != '' ". - " AND statustext IS NOT NULL ". - " AND statustext != 'N/A' " -} - -if ( $cgi->param('part_bill_event.payby') =~ /^(\w+)$/ ) { - $where .= " AND part_bill_event.payby = '$1' "; -} - -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', - 'cust_main.custnum AS cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), - '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 ". - "LEFT JOIN part_bill_event USING ( eventpart ) ". - $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'); - -my $link_cust = sub { - my $cust_bill_event = shift; - $cust_bill_event->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; -}; - -%><%= include( 'elements/search.html', - 'title' => $title, - 'html_init' => $html_init, - 'menubar' => $menubar, - 'name' => 'billing events', - 'query' => $sql_query, - 'count_query' => $count_sql, - 'header' => [ 'Event', - 'Date', - 'Status', - #'Inv #', 'Inv Date', 'Cust #', - 'Invoice', - FS::UI::Web::cust_header(), - ], - '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). - ')'; - }, - \&FS::UI::Web::cust_fields, - ], - 'links' => [ - '', - '', - '', - sub { - my $part_bill_event = shift; - my $template = $part_bill_event->templatename; - $template .= '-' if $template; - [ "${p}view/cust_bill.cgi?$template", 'invnum']; - }, - ( map { $link_cust } FS::UI::Web::cust_header() ), - ], - ) -%> diff --git a/httemplate/search/cust_bill_event.html b/httemplate/search/cust_bill_event.html deleted file mode 100755 index 197f28028..000000000 --- a/httemplate/search/cust_bill_event.html +++ /dev/null @@ -1,58 +0,0 @@ -<%= include( - '/elements/header.html', - ( $cgi->param('failed') ? 'Failed invoice events' : 'Invoice events' ), - include('/elements/menubar.html', - 'Main menu' => $p, # popurl(2), - ), - - ) -%> - - <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> - --> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> - <!-- - <TR> - <TD ALIGN="right">Events: </TD> - <TD> - <SELECT NAME="eventpart"> - <OPTION SELECTED VALUE=""><%= $cgi->param('failed') ? '(all failed events)' : '(all events)' %> - <% foreach my $part_bill_event ( qsearch( 'part_bill_event', {} ) ) { %> - <% } %> - </SELECT> - </TD> - </TR> - --> - <TR> - <TD ALIGN="right">Events for payment type: </TD> - <TD> - <SELECT NAME="part_bill_event.payby"> - <OPTION SELECTED VALUE="">(all) - <OPTION VALUE="CARD">Credit card (automatic) - <OPTION VALUE="BILL">Billing - <OPTION VALUE="CHEK">Electronic check (automatic) - <OPTION VALUE="DCRD">Credit card (on-demand) - <OPTION VALUE="DCHK">Electronic check (on-demand) - <OPTION VALUE="LECB">Phone bill billing - <OPTION VALUE="COMP">Complimentary - </SELECT> - </TD> - </TR> - </TABLE> - <BR><INPUT TYPE="submit" VALUE="Get Report"> - </FORM> - </BODY> -</HTML> diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi deleted file mode 100644 index 082ccc893..000000000 --- a/httemplate/search/cust_bill_pkg.cgi +++ /dev/null @@ -1,148 +0,0 @@ -<% - -my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - -my $join_cust = " - JOIN cust_bill USING ( invnum ) - JOIN cust_main USING ( custnum ) -"; - -my $join_pkg = " - LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) -"; - -my $where = " - WHERE _date >= $beginning AND _date <= $ending - AND payby != 'COMP' -"; - -if ( $cgi->param('out') ) { - - $where .= " - AND 0 = ( - SELECT COUNT(*) FROM cust_main_county - WHERE ( cust_main_county.county = cust_main.county - OR ( cust_main_county.county IS NULL AND cust_main.county = '' ) - OR ( cust_main_county.county = '' AND cust_main.county IS NULL) - OR ( cust_main_county.county IS NULL AND cust_main.county IS NULL) - ) - AND ( cust_main_county.state = cust_main.state - OR ( cust_main_county.state IS NULL AND cust_main.state = '' ) - OR ( cust_main_county.state = '' AND cust_main.state IS NULL ) - OR ( cust_main_county.state IS NULL AND cust_main.state IS NULL ) - ) - AND cust_main_county.country = cust_main.country - AND cust_main_county.tax > 0 - ) - "; - -} elsif ( $cgi->param('country' ) ) { - - my $county = dbh->quote( $cgi->param('county') ); - my $state = dbh->quote( $cgi->param('state') ); - my $country = dbh->quote( $cgi->param('country') ); - $where .= " - AND ( county = $county OR $county = '' ) - AND ( state = $state OR $state = '' ) - AND country = $country - "; - $where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') ) - if $cgi->param('taxclass'); - -} - -$where .= ' AND pkgnum != 0' if $cgi->param('nottax'); - -$where .= ' AND pkgnum = 0' if $cgi->param('istax'); - -$where .= " AND tax = 'Y'" if $cgi->param('cust_tax'); - -my $count_query; -if ( $cgi->param('pkg_tax') ) { - - $count_query = - "SELECT COUNT(*), SUM( ( CASE WHEN part_pkg.setuptax = 'Y' - THEN cust_bill_pkg.setup - ELSE 0 ) - + - ( CASE WHEN part_pkg.recurtax = 'Y' - THEN cust_bill_pkg.recur - ELSE 0 ) - )"; - - $where .= " AND ( - ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) - OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) - )"; - -} else { - - $count_query = - "SELECT COUNT(*), SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; - -} -$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; - -my $query = { - 'table' => 'cust_bill_pkg', - 'addl_from' => "$join_cust $join_pkg", - 'hashref' => {}, - 'select' => join(', ', - 'cust_bill_pkg.*', - 'cust_bill._date', - 'part_pkg.pkg', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => $where, -}; - -my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; -my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; - -my $conf = new FS::Conf; -my $money_char = $conf->config('money_char') || '$'; - -%><%= include( 'elements/search.html', - 'title' => 'Line items', - 'name' => 'line items', - 'query' => $query, - 'count_query' => $count_query, - 'count_addl' => [ $money_char. '%.2f total', ], - 'header' => [ - '#', - 'Description', - 'Setup charge', - 'Recurring charge', - 'Invoice', - 'Date', - FS::UI::Web::cust_header(), - ], - 'fields' => [ - 'billpkgnum', - sub { $_[0]->pkgnum > 0 - ? $_[0]->get('pkg') - : $_[0]->get('itemdesc') - }, - #strikethrough or "N/A ($amount)" or something these when - # they're not applicable to pkg_tax search - sub { sprintf($money_char.'%.2f', shift->setup ) }, - sub { sprintf($money_char.'%.2f', shift->recur ) }, - 'invnum', - sub { time2str('%b %d %Y', shift->_date ) }, - \&FS::UI::Web::cust_fields, - ], - 'links' => [ - '', - '', - '', - '', - $ilink, - $ilink, - ( map { $clink } FS::UI::Web::cust_header() ), - ], - 'align' => 'rlrrrc', - ) -%> - diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html deleted file mode 100755 index 279d682cd..000000000 --- a/httemplate/search/cust_credit.html +++ /dev/null @@ -1,97 +0,0 @@ -<% - my $title = 'Credit Search Results'; - #my( $count_query, $sql_query ); - - my @search = (); - - if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { - push @search, "cust_credit.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' => join(', ', - 'cust_credit.*', - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'hashref' => {}, - 'extra_sql' => $where, - 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', - }; - - my $clink = sub { - my $cust_bill = shift; - $cust_bill->cust_main_custnum - ? [ "${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' => [ 'Amount', - 'Date', - FS::UI::Web::cust_header(), - 'By', - 'Reason' - ], - 'fields' => [ - #'crednum', - sub { sprintf('$%.2f', shift->amount ) }, - sub { time2str('%b %d %Y', shift->_date ) }, - \&FS::UI::Web::cust_fields, - 'otaker', - 'reason', - ], - #'align' => 'rrrllll', - 'align' => 'rr', - 'links' => [ - '', - '', - ( map { $clink } FS::UI::Web::cust_header() ), - '', - '', - ], - ) -%> diff --git a/httemplate/search/cust_main-otaker.cgi b/httemplate/search/cust_main-otaker.cgi deleted file mode 100755 index 03c2619af..000000000 --- a/httemplate/search/cust_main-otaker.cgi +++ /dev/null @@ -1,28 +0,0 @@ -<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 deleted file mode 100755 index b82b610d8..000000000 --- a/httemplate/search/cust_main-payinfo.html +++ /dev/null @@ -1,20 +0,0 @@ -<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 deleted file mode 100755 index 154a64199..000000000 --- a/httemplate/search/cust_main-quickpay.html +++ /dev/null @@ -1,44 +0,0 @@ -<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 deleted file mode 100755 index 665f5637d..000000000 --- a/httemplate/search/cust_main.cgi +++ /dev/null @@ -1,685 +0,0 @@ -<% - -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 deleted file mode 100755 index 4f7508447..000000000 --- a/httemplate/search/cust_main.html +++ /dev/null @@ -1,42 +0,0 @@ -<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 deleted file mode 100755 index 99ffc3d20..000000000 --- a/httemplate/search/cust_pay.cgi +++ /dev/null @@ -1,192 +0,0 @@ -<% - my $title = 'Payment Search Results'; - my( $count_query, $sql_query ); - if ( $cgi->param('magic') ) { - - my @search = (); - my $orderby; - if ( $cgi->param('magic') eq '_date' ) { - - - 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|PREP|CASH|WEST|MCRD)(-(VisaMC|Amex|Discover|Maestro))?$/ - 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' ". - " AND substring(cust_pay.payinfo from 1 for 4) != '4936' ". - " AND substring(cust_pay.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49030[2-9]' ". - " AND substring(cust_pay.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49033[5-9]' ". - " AND substring(cust_pay.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49110[1-2]' ". - " AND substring(cust_pay.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49117[4-9]' ". - " AND substring(cust_pay.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49118[1-2]' ". - " )". - " 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' ". - " OR substring(cust_pay.payinfo from 1 for 3 ) = '650' ". - " ) "; - } elsif ( $3 eq 'Maestro' ) { - push @search, - " ( substring(cust_pay.payinfo from 1 for 2 ) = '63' ". - " OR substring(cust_pay.payinfo from 1 for 2 ) = '67' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) = '564182' ". - " OR substring(cust_pay.payinfo from 1 for 4 ) = '4936' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) ". - " SIMILAR TO '49030[2-9]' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) ". - " SIMILAR TO '49033[5-9]' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) ". - " SIMILAR TO '49110[1-2]' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) ". - " SIMILAR TO '49117[4-9]' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) ". - " SIMILAR TO '49118[1-2]' ". - " ) "; - } else { - die "unknown card type $3"; - } - } - } - - my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - push @search, "_date >= $beginning ", - "_date <= $ending"; - - $orderby = '_date'; - - } elsif ( $cgi->param('magic') eq 'paybatch' ) { - - $cgi->param('paybatch') =~ /^([\w\/\:\-\.]+)$/ - or die "illegal paybatch: ". $cgi->param('paybatch'); - - push @search, "paybatch = '$1'"; - - $orderby = "LOWER(company || ' ' || last || ' ' || first )"; - - } else { - die "unknown search magic: ". $cgi->param('magic'); - } - - 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' => join(', ', - 'cust_pay.*', - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'hashref' => {}, - 'extra_sql' => "$search ORDER BY $orderby", - '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 = sub { - my $cust_pay = shift; - $cust_pay->cust_main_custnum - ? [ "${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' => [ 'Payment', - 'Amount', - 'Date', - FS::UI::Web::cust_header(), - ], - '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; - } elsif ( $cust_pay->payby eq 'PREP' ) { - 'Prepaid card #'. $cust_pay->payinfo; - } elsif ( $cust_pay->payby eq 'CASH' ) { - 'Cash '. $cust_pay->payinfo; - } elsif ( $cust_pay->payby eq 'WEST' ) { - 'Western Union'; #. $cust_pay->payinfo; - } elsif ( $cust_pay->payby eq 'MCRD' ) { - 'Manual credit card'; #. $cust_pay->payinfo; - } else { - $cust_pay->payby. ' '. $cust_pay->payinfo; - } - }, - sub { sprintf('$%.2f', shift->paid ) }, - sub { time2str('%b %d %Y', shift->_date ) }, - \&FS::UI::Web::cust_fields, - ], - #'align' => 'lrrrll', - 'align' => 'rrr', - 'links' => [ - '', - '', - '', - ( map { $link } FS::UI::Web::cust_header() ), - ], - ) -%> diff --git a/httemplate/search/cust_pay.html b/httemplate/search/cust_pay.html deleted file mode 100755 index 6414cf771..000000000 --- a/httemplate/search/cust_pay.html +++ /dev/null @@ -1,18 +0,0 @@ -<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 deleted file mode 100755 index 5da4d82fb..000000000 --- a/httemplate/search/cust_pkg.cgi +++ /dev/null @@ -1,234 +0,0 @@ -<% - -my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); - -my($query) = $cgi->keywords; - -my $orderby; -my @where; -my $cjoin = ''; - -if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { - $cjoin = "LEFT JOIN cust_main USING ( custnum )"; - push @where, - "agentnum = $1"; -} - -if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { - $orderby = 'ORDER BY bill'; - - my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - push @where, - "bill >= $beginning ", - "bill <= $ending", - '( cancel IS NULL OR cancel = 0 )'; - -} else { - - if ( $cgi->param('magic') && - $cgi->param('magic') =~ /^(active|suspended|cancell?ed)$/ - ) { - - $orderby = 'ORDER BY pkgnum'; - - if ( $cgi->param('magic') eq 'active' ) { - - #push @where, - # '( susp IS NULL OR susp = 0 )', - # '( cancel IS NULL OR cancel = 0)'; - push @where, FS::cust_pkg->active_sql(); - - } elsif ( $cgi->param('magic') eq 'suspended' ) { - - push @where, - 'susp IS NOT NULL', - 'susp != 0', - '( cancel IS NULL OR cancel = 0)'; - - } elsif ( $cgi->param('magic') =~ /^cancell?ed$/ ) { - - push @where, - 'cancel IS NOT NULL', - 'cancel != 0'; - - } else { - die "guru meditation #420"; - } - - if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { - push @where, "pkgpart = $1"; - } - - } elsif ( $query eq 'pkgnum' ) { - - $orderby = 'ORDER BY pkgnum'; - - } elsif ( $query eq 'APKG_pkgnum' ) { - - $orderby = 'ORDER BY pkgnum'; - - push @where, '0 < ( - SELECT count(*) FROM pkg_svc - WHERE pkg_svc.pkgpart = cust_pkg.pkgpart - AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc - WHERE cust_svc.pkgnum = cust_pkg.pkgnum - AND cust_svc.svcpart = pkg_svc.svcpart - ) - )'; - - } else { - die "Empty or unknown QUERY_STRING!"; - } - -} - -my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; - -my $count_query = "SELECT COUNT(*) FROM cust_pkg $cjoin $extra_sql"; - -my $sql_query = { - 'table' => 'cust_pkg', - 'hashref' => {}, - 'select' => join(', ', - 'cust_pkg.*', - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ', - #' LEFT JOIN part_pkg USING ( pkgpart ) ' -}; - -my $link = sub { - [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ]; -}; - -my $clink = sub { - my $cust_pkg = shift; - $cust_pkg->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; -}; - -#if ( scalar(@cust_pkg) == 1 ) { -# print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum. -# "#cust_pkg". $cust_pkg[0]->pkgnum ); - -# my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } ); -# my $rowspan = scalar(@cust_svc) || 1; - -# my $n2 = ''; -# foreach my $cust_svc ( @cust_svc ) { -# my($label, $value, $svcdb) = $cust_svc->label; -# my $svcnum = $cust_svc->svcnum; -# my $sview = $p. "view"; -# print $n2,qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!, -# qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!; -# $n2="</TR><TR>"; -# } - -sub time_or_blank { - my $column = shift; - return sub { - my $record = shift; - my $value = $record->get($column); #mmm closures - $value ? time2str('%b %d %Y', $value ) : ''; - }; -} - -%><%= include( 'elements/search.html', - 'title' => 'Package Search Results', - 'name' => 'packages', - 'query' => $sql_query, - 'count_query' => $count_query, - 'redirect' => $link, - 'header' => [ '#', - 'Package', - 'Status', - 'Freq.', - 'Setup', - 'Last bill', - 'Next bill', - 'Susp.', - 'Expire', - 'Cancel', - FS::UI::Web::cust_header(), - 'Services', - ], - 'fields' => [ - 'pkgnum', - sub { my $part_pkg = $part_pkg{shift->pkgpart}; - $part_pkg->pkg; # ' - '. $part_pkg->comment; - }, - sub { ucfirst(shift->status); }, - sub { #shift->part_pkg->freq_pretty; - my $part_pkg = $part_pkg{shift->pkgpart}; - $part_pkg->freq_pretty; - }, - - #sub { time2str('%b %d %Y', shift->setup); }, - #sub { time2str('%b %d %Y', shift->last_bill); }, - #sub { time2str('%b %d %Y', shift->bill); }, - #sub { time2str('%b %d %Y', shift->susp); }, - #sub { time2str('%b %d %Y', shift->expire); }, - #sub { time2str('%b %d %Y', shift->get('cancel')); }, - ( map { time_or_blank($_) } - qw( setup last_bill bill susp expire cancel ) ), - - \&FS::UI::Web::cust_fields, - #sub { '<table border=0 cellspacing=0 cellpadding=0 STYLE="border:none">'. - # join('', map { '<tr><td align="right" style="border:none">'. $_->[0]. - # ':</td><td style="border:none">'. $_->[1]. '</td></tr>' } - # shift->labels - # ). - # '</table>'; - # }, - sub { - [ map { - [ - { 'data' => $_->[0]. ':', - 'align'=> 'right', - }, - { 'data' => $_->[1], - 'align'=> 'left', - 'link' => $p. 'view/' . - $_->[2]. '.cgi?'. $_->[3], - }, - ]; - } shift->labels - ]; - }, - ], - 'color' => [ - '', - '', - sub { shift->statuscolor; }, - '', - '', - '', - '', - '', - '', - '', - ( map { '' } FS::UI::Web::cust_header() ), - '', - ], - 'style' => [ '', '', 'b' ], - 'size' => [ '', '', '-1', ], - 'align' => 'rlclrrrrrr', - 'links' => [ - $link, - $link, - '', - '', - '', - '', - '', - '', - '', - '', - ( map { $clink } FS::UI::Web::cust_header() ), - '', - ], - ) -%> diff --git a/httemplate/search/cust_pkg_report.cgi b/httemplate/search/cust_pkg_report.cgi deleted file mode 100755 index 412c3f79d..000000000 --- a/httemplate/search/cust_pkg_report.cgi +++ /dev/null @@ -1,23 +0,0 @@ -<HTML> - <HEAD> - <TITLE>Packages</TITLE> - </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> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> - <%= include( '/elements/tr-select-agent.html', - $cgi->param('agentnum'), - ) - %> - </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 deleted file mode 100644 index d19fb4acd..000000000 --- a/httemplate/search/elements/search.html +++ /dev/null @@ -1,392 +0,0 @@ -<% - - 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, map { - ref($_) eq 'ARRAY' - ? '(N/A)' #unimplemented - : $_; - } - &{$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' ) { - foreach my $value ( &{$field}($row) ) { - if ( ref($value) eq 'ARRAY' ) { - $worksheet->write($r, $c++, '(N/A)' ); #unimplemented - } else { - $worksheet->write($r, $c++, $value ); - } - } - } else { - $worksheet->write($r, $c++, $row->$field() ); - } - } - - } 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 $redirect = $opt{'redirect'}; - $redirect = &{$redirect}($rows->[0]) if ref($redirect) eq 'CODE'; - my( $url, $method ) = @$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 %> - - <%= include('/elements/table-grid.html') %> - - <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 ( - - map { - if ( ref($_) eq 'ARRAY' ) { - - my $tableref = $_; - - '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0'. - ' STYLE="border:none">'. - - join('', map { - - my $rowref = $_; - - '<tr>'. - - join('', map { - - my $element = $_; - - '<TD STYLE="border:none"'. - ( $element->{'align'} - ? ' ALIGN="'. $element->{'align'}. '"' - : '' - ). '>'. - ( $element->{'link'} - ? '<A HREF="'. $element->{'link'}.'">' - : '' - ). - $element->{'data'}. - ( $element->{'link'} - ? '</A>' - : '' - ). - '</td>'; - - } @$rowref ). - - '</tr>'; - } @$tableref ). - - '</table>'; - - } else { - $_; - } - } - - map { - if ( ref($_) eq 'CODE' ) { - &{$_}($row); - } else { - $row->$_(); - } - } - @{$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) ); - } - - %> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"<%= $align %>><%= $font %><%= $a %><%= $s %><%= $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 deleted file mode 100644 index 8c8f57b5a..000000000 --- a/httemplate/search/prepay_credit.html +++ /dev/null @@ -1,43 +0,0 @@ -<% -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 deleted file mode 100644 index 52a99ff66..000000000 --- a/httemplate/search/reg_code.html +++ /dev/null @@ -1,36 +0,0 @@ -<% - -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 deleted file mode 100644 index a7be76689..000000000 --- a/httemplate/search/report_cust_bill.html +++ /dev/null @@ -1,28 +0,0 @@ - <HEAD> - <TITLE>Invoice report criteria</TITLE> - </HEAD> - <BODY BGCOLOR="#e8e8e8"> - <H1>Invoice report criteria</H1> - <FORM ACTION="cust_bill.html" METHOD="GET"> - <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> - <TABLE> - <%= include( '/elements/tr-select-agent.html', - $cgi->param('agentnum'), - 'label' => 'Invoices for agent: ', - ) - %> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> - <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 deleted file mode 100644 index 56bbd0ac0..000000000 --- a/httemplate/search/report_cust_credit.html +++ /dev/null @@ -1,36 +0,0 @@ -<HTML> - <HEAD> - <TITLE>Credit report criteria</TITLE> - </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> - <%= include( '/elements/tr-select-agent.html', - $cgi->param('agentnum'), - 'label' => 'for agent: ', - ) - %> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> - </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 deleted file mode 100644 index 5d8b74e77..000000000 --- a/httemplate/search/report_cust_pay.html +++ /dev/null @@ -1,38 +0,0 @@ -<HTML> - <HEAD> - <TITLE>Payment report criteria</TITLE> - </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="CARD-Maestro">credit card (Maestro/Switch/Solo)</OPTION> - <OPTION VALUE="CHEK">electronic check / ACH</OPTION> - <OPTION VALUE="BILL">check</OPTION> - <OPTION VALUE="PREP">prepaid card</OPTION> - <OPTION VALUE="CASH">cash</OPTION> - <OPTION VALUE="WEST">Western Union</OPTION> - <OPTION VALUE="MCRD">manual credit card</OPTION> - </SELECT> - </TD> - </TR> - <%= include( '/elements/tr-select-agent.html', - $cgi->param('agentnum'), - 'label' => 'for agent: ', - ) - %> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> - </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 deleted file mode 100644 index 1677591a3..000000000 --- a/httemplate/search/report_prepaid_income.cgi +++ /dev/null @@ -1,86 +0,0 @@ -<!-- 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 deleted file mode 100644 index 57c318eba..000000000 --- a/httemplate/search/report_prepaid_income.html +++ /dev/null @@ -1,39 +0,0 @@ -<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 deleted file mode 100755 index d675346f0..000000000 --- a/httemplate/search/report_receivables.cgi +++ /dev/null @@ -1,232 +0,0 @@ -<% - - 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') || '$'; - - my $align = join('', map { /#/ ? 'r' : 'l' } FS::UI::Web::cust_header() ). - 'crrrrr'; - - my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; - -%><%= include( 'elements/search.html', - 'title' => 'Accounts Receivable Aging Summary', - 'name' => 'customers', - 'query' => $sql_query, - 'count_query' => $count_sql, - 'header' => [ - FS::UI::Web::cust_header(), - 'Status', # (me)', - #'Status', # (cust_main)', - '0-30', - '30-60', - '60-90', - '90+', - 'Total', - ], - 'footer' => [ - 'Total', - ( map '', - ( 1 .. - scalar(FS::UI::Web::cust_header()-1) - ) - ), - '', - #'', - 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' => [ - \&FS::UI::Web::cust_fields, - 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' => [ - ( map $clink, FS::UI::Web::cust_header() ), - '', - #'', - '', - '', - '', - '', - '', - ], - #'align' => 'rlccrrrrr', - 'align' => $align, - #'size' => [ '', '', '-1', '-1', '', '', '', '', '', ], - #'style' => [ '', '', 'b', 'b', '', '', '', '', 'b', ], - 'size' => [ ( map '', FS::UI::Web::cust_header() ), - '-1', '', '', '', '', '', ], - 'style' => [ ( map '', FS::UI::Web::cust_header() ), - 'b', '', '', '', '', 'b', ], - 'color' => [ - ( map '', FS::UI::Web::cust_header() ), - 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 deleted file mode 100755 index 9062f0626..000000000 --- a/httemplate/search/report_tax.cgi +++ /dev/null @@ -1,432 +0,0 @@ -<% - -my $conf = new FS::Conf; -my $money_char = $conf->config('money_char') || '$'; - -my $user = getotaker; - -my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - -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 $agentname = ''; -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - my $agent = qsearchs('agent', { 'agentnum' => $1 } ); - die "agent not found" unless $agent; - $agentname = $agent->agent; - $where .= ' AND agentnum = '. $agent->agentnum; -} - -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 $taxclass_flag = 0; -my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0, 0 ); -my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 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; - $regions{$label}->{'url_param'} = join(';', map "$_=".$r->$_(), qw( county state country ) ); - - my $fromwhere = $from_join_cust. $join_pkg. $where; - my @param = @base_param; - - if ( $r->taxclass ) { - $fromwhere .= " AND taxclass = ? "; - push @param, 'taxclass'; - $regions{$label}->{'url_param'} .= ';taxclass='. $r->taxclass - if $cgi->param('show_taxclasses'); - $taxclass_flag = 1; - } - -# my $label = getlabel($r); -# $regions{$label}->{'label'} = $label; - - my $nottax = 'pkgnum != 0'; - - ## calculate total for this region - - my $t = scalar_sql($r, \@param, - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax" - ); - $total += $t; - $regions{$label}->{'total'} += $t; - - ## calculate package-exemption for this region - - 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_pkg += $x; - $regions{$label}->{'exempt_pkg'} += $x; - } - - ## calculate customer-exemption for this region - - my($taxable, $x_cust) = (0, 0); - foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i } - qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { - $taxable += scalar_sql($r, \@param, - "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )" - ); - - $x_cust += scalar_sql($r, \@param, - "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'" - ); - } - - $exempt_cust += $x_cust; - $regions{$label}->{'exempt_cust'} += $x_cust; - - ## calculate monthly exemption (texas tax) for this region - - 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 $x_monthly = 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 ) )" - ); - if ( $x_monthly ) { - warn $r->taxnum(). ": $x_monthly\n"; - $taxable -= $x_monthly; - } - - $exempt_monthly += $x_monthly; - $regions{$label}->{'exempt_monthly'} += $x_monthly; - - $tot_taxable += $taxable; - $regions{$label}->{'taxable'} += $taxable; - - $owed += $taxable * ($r->tax/100); - $regions{$label}->{'owed'} += $taxable * ($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; -my %base_regions = (); -#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; - - if ( $cgi->param('show_taxclasses') ) { - my $base_label = getlabel($r, 'no_taxclass'=>1 ); - $base_regions{$base_label}->{'label'} = $base_label; - $base_regions{$base_label}->{'url_param'} = - join(';', map "$_=".$r->$_(), qw( county state country ) ); - $base_regions{$base_label}->{'tax'} += $x; - } - -} - -#ordering -my @regions = - map $regions{$_}, - sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } - keys %regions; - -my @base_regions = - map $base_regions{$_}, - sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } - keys %base_regions; - -push @regions, { - 'label' => 'Total', - 'url_param' => '', - 'total' => $total, - 'exempt_cust' => $exempt_cust, - 'exempt_pkg' => $exempt_pkg, - 'exempt_monthly' => $exempt_monthly, - 'taxable' => $tot_taxable, - 'rate' => '', - 'owed' => $owed, - 'tax' => $tax, -}; - -#-- - -sub getlabel { - my $r = shift; - my %opt = @_; - - my $label; - if ( - $r->tax == 0 - && ! scalar( qsearch('cust_main_county', { 'state' => $r->state, - 'county' => $r->county, - 'country' => $r->country, - 'tax' => { op=>'>', value=>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 = "$label (". $r->taxclass. ")" - if $r->taxclass - && $cgi->param('show_taxclasses') - && ! $opt{'no_taxclasses'}; - #$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; -} - -%> - -<% - -my $baselink = $p. "search/cust_bill_pkg.cgi?begin=$beginning;end=$ending"; - -%> - - -<%= header( "$agentname Sales Tax Report - ". - time2str('%h %o %Y through ', $beginning ). - ( $ending == 4294967295 - ? 'now' - : time2str('%h %o %Y', $ending ) - ), - menubar( 'Main Menu'=>$p, ) - ) -%> - -<%= include('/elements/table-grid.html') %> - - <TR> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> - <TH CLASS="grid" BGCOLOR="#cccccc" COLSPAN=9>Sales</TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Rate</TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax owed</TH> - <% unless ( $cgi->param('show_taxclasses') ) { %> - <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax invoiced</TH> - <% } %> - </TR> - <TR> - <TH CLASS="grid" BGCOLOR="#cccccc">Total</TH> - <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(tax-exempt customer)</FONT></TH> - <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(tax-exempt package)</FONT></TH> - <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(monthly exemption)</FONT></TH> - <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">Taxable</TH> - </TR> - -<% my $bgcolor1 = '#eeeeee'; - my $bgcolor2 = '#ffffff'; - my $bgcolor; -%> - - <% foreach my $region ( @regions ) { - - if ( $bgcolor eq $bgcolor1 ) { - $bgcolor = $bgcolor2; - } else { - $bgcolor = $bgcolor1; - } - - my $link = $baselink; - if ( $region->{'label'} ne 'Total' ) { - if ( $region->{'label'} eq $out ) { - $link .= ';out=1'; - } else { - $link .= ';'. $region->{'url_param'}; - } - } - %> - - <TR> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $region->{'label'} %></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $link %>;nottax=1"><%= $money_char %><%= sprintf('%.2f', $region->{'total'} ) %></A> - </TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $link %>;nottax=1;cust_tax=Y"><%= $money_char %><%= sprintf('%.2f', $region->{'exempt_cust'} ) %></A> - </TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $link %>;nottax=1;pkg_tax=Y"><%= $money_char %><%= sprintf('%.2f', $region->{'exempt_pkg'} ) %></A> - </TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <%= $money_char %><%= sprintf('%.2f', $region->{'exempt_monthly'} ) %></A> - </TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><FONT SIZE="+1"><B> = </B></FONT></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <%= $money_char %><%= sprintf('%.2f', $region->{'taxable'} ) %></A> - </TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $region->{'label'} eq 'Total' ? '' : '<FONT FACE="sans-serif" SIZE="+1"><B> X </B></FONT>' %></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"><%= $region->{'rate'} %></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $region->{'label'} eq 'Total' ? '' : '<FONT FACE="sans-serif" SIZE="+1"><B> = </B></FONT>' %></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <%= $money_char %><%= sprintf('%.2f', $region->{'owed'} ) %> - </TD> - <% unless ( $cgi->param('show_taxclasses') ) { %> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $link %>;istax=1"><%= $money_char %><%= sprintf('%.2f', $region->{'tax'} ) %></A> - </TD> - <% } %> - </TR> - - <% } %> - -</TABLE> - - -<% if ( $cgi->param('show_taxclasses') ) { %> - - <BR> - <%= include('/elements/table-grid.html') %> - <TR> - <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH> - </TR> - - <% #some false laziness w/above - foreach my $region ( @base_regions ) { - - if ( $bgcolor eq $bgcolor1 ) { - $bgcolor = $bgcolor2; - } else { - $bgcolor = $bgcolor1; - } - - my $link = $baselink; - #if ( $region->{'label'} ne 'Total' ) { - if ( $region->{'label'} eq $out ) { - $link .= ';out=1'; - } else { - $link .= ';'. $region->{'url_param'}; - } - #} - %> - - <TR> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $region->{'label'} %></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $link %>;istax=1"><%= $money_char %><%= sprintf('%.2f', $region->{'tax'} ) %></A> - </TD> - </TR> - - <% } %> - - <TR> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>">Total</TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $baselink %>;istax=1"><%= $money_char %><%= sprintf('%.2f', $tax ) %></A> - </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 deleted file mode 100755 index eeaccc1ab..000000000 --- a/httemplate/search/report_tax.html +++ /dev/null @@ -1,22 +0,0 @@ -<HTML> - <HEAD> - <TITLE>Tax Report Criteria</TITLE> - </HEAD> - <BODY BGCOLOR="#e8e8e8"> - <H1>Tax Report Criteria</H1> - <FORM ACTION="report_tax.cgi" METHOD="GET"> - <TABLE> - <%= include( '/elements/tr-select-agent.html' ) %> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> - <TR> - <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="show_taxclasses" VALUE="1"></TD> - <TD>Show tax classes</TD> - </TR> - </TABLE> - - <BR><INPUT TYPE="submit" VALUE="Get Report"> - - </FORM> - </BODY> -</HTML> - diff --git a/httemplate/search/sql.html b/httemplate/search/sql.html deleted file mode 100644 index b28c045d1..000000000 --- a/httemplate/search/sql.html +++ /dev/null @@ -1,7 +0,0 @@ -<%= 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 deleted file mode 100644 index b84df1a03..000000000 --- a/httemplate/search/sqlradius.cgi +++ /dev/null @@ -1,290 +0,0 @@ -<%= 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 deleted file mode 100644 index 8f4878dbc..000000000 --- a/httemplate/search/sqlradius.html +++ /dev/null @@ -1,94 +0,0 @@ -<%= 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 deleted file mode 100755 index b14591958..000000000 --- a/httemplate/search/svc_acct.cgi +++ /dev/null @@ -1,140 +0,0 @@ -<% - -my $orderby = 'ORDER BY svcnum'; - -my($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors - -my $cjoin = ''; -my @extra_sql = (); -if ( $query =~ /^UN_(.*)$/ ) { - $query = $1; - $cjoin = 'LEFT JOIN cust_svc USING ( svcnum )'; - push @extra_sql, 'pkgnum IS NULL'; -} - -if ( $query eq 'svcnum' ) { - #$orderby = "ORDER BY svcnum"; -} elsif ( $query eq 'username' ) { - $orderby = "ORDER BY LOWER(username)"; -} elsif ( $query eq 'uid' ) { - $orderby = "ORDER BY uid"; - push @extra_sql, "uid IS NOT NULL"; -} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) { - push @extra_sql, "popnum = $1"; - $orderby = "ORDER BY LOWER(username)"; -} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { - $cjoin ||= 'LEFT JOIN cust_svc USING ( svcnum )'; - push @extra_sql, "svcpart = $1"; - $orderby = "ORDER BY uid"; - #$orderby = "ORDER BY svcnum"; -} else { - $orderby = "ORDER BY uid"; - - my @username_sql; - - my %username_type; - foreach ( $cgi->param('username_type') ) { - $username_type{$_}++; - } - - $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text - my $username = $1; - - push @username_sql, "username ILIKE '$username'" - if $username_type{'Exact'} - || $username_type{'Fuzzy'}; - - push @username_sql, "username ILIKE '\%$username\%'" - if $username_type{'Substring'} - || $username_type{'All'}; - - 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'}) { - #} - - push @username_sql, "username = '$_'" - foreach (keys %username); - - } - - push @extra_sql, '( '. join( ' OR ', @username_sql). ' )'; - -} - -my $extra_sql = - scalar(@extra_sql) - ? ' WHERE '. join(' AND ', @extra_sql ) - : ''; - -my $count_query = "SELECT COUNT(*) FROM svc_acct $cjoin $extra_sql"; -#if ( keys %svc_acct ) { -# $count_query .= ' WHERE '. -# join(' AND ', map "$_ = ". dbh->quote($svc_acct{$_}), -# keys %svc_acct -# ); -#} - -my $sql_query = { - 'table' => 'svc_acct', - 'hashref' => {}, # \%svc_acct, - 'select' => join(', ', - 'svc_acct.*', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) ', -}; - -my $link = [ "${p}view/svc_acct.cgi?", 'svcnum' ]; -my $link_cust = sub { - my $svc_acct = shift; - if ( $svc_acct->custnum ) { - [ "${p}view/cust_main.cgi?", 'custnum' ]; - } else { - ''; - } -}; - -%><%= include( 'elements/search.html', - 'title' => 'Account Search Results', - 'name' => 'accounts', - 'query' => $sql_query, - 'count_query' => $count_query, - 'redirect' => $link, - 'header' => [ '#', - 'Account', - 'UID', - 'Service', - FS::UI::Web::cust_header(), - ], - 'fields' => [ 'svcnum', - 'email', - 'uid', - 'svc', - \&FS::UI::Web::cust_fields, - ], - 'links' => [ $link, - $link, - $link, - '', - ( map { $link_cust } - FS::UI::Web::cust_header() - ), - ], - ) -%> diff --git a/httemplate/search/svc_acct.html b/httemplate/search/svc_acct.html deleted file mode 100755 index c504c2f34..000000000 --- a/httemplate/search/svc_acct.html +++ /dev/null @@ -1,19 +0,0 @@ -<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 deleted file mode 100755 index efadce600..000000000 --- a/httemplate/search/svc_broadband.cgi +++ /dev/null @@ -1,96 +0,0 @@ -<% - -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 deleted file mode 100755 index f261ea9f3..000000000 --- a/httemplate/search/svc_domain.cgi +++ /dev/null @@ -1,85 +0,0 @@ -<% - -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.*', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - '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', - FS::UI::Web::cust_header(), - ], - 'fields' => [ 'svcnum', - 'domain', - \&FS::UI::Web::cust_fields, - ], - 'links' => [ $link, - $link, - ( map { $link_cust } - FS::UI::Web::cust_header() - ), - ], - ) -%> diff --git a/httemplate/search/svc_domain.html b/httemplate/search/svc_domain.html deleted file mode 100755 index b759102f4..000000000 --- a/httemplate/search/svc_domain.html +++ /dev/null @@ -1,19 +0,0 @@ -<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 deleted file mode 100755 index c5ac13498..000000000 --- a/httemplate/search/svc_external.cgi +++ /dev/null @@ -1,101 +0,0 @@ -<% - -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 deleted file mode 100755 index a204e345f..000000000 --- a/httemplate/search/svc_forward.cgi +++ /dev/null @@ -1,120 +0,0 @@ -<% - -my $conf = new FS::Conf; - -my($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors - - -my $orderby; - -my $cjoin = ''; -my @extra_sql = (); -if ( $query =~ /^UN_(.*)$/ ) { - $query = $1; - $cjoin = 'LEFT JOIN cust_svc USING ( svcnum )'; - push @extra_sql, 'pkgnum IS NULL'; -} - -if ( $query eq 'svcnum' ) { - $orderby = 'ORDER BY svcnum'; -} else { - eidiot('unimplemented'); -} - -my $extra_sql = - scalar(@extra_sql) - ? ' WHERE '. join(' AND ', @extra_sql ) - : ''; - -my $count_query = "SELECT COUNT(*) FROM svc_forward $cjoin $extra_sql"; -my $sql_query = { - 'table' => 'svc_forward', - 'hashref' => {}, - 'select' => join(', ', - 'svc_forward.*', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' 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', - FS::UI::Web::cust_header(), - ], - 'fields' => [ 'svcnum', - $format_src, - $format_dst, - \&FS::UI::Web::cust_fields, - ], - 'links' => [ $link, - $link_src, - $link_dst, - ( map { $link_cust } - FS::UI::Web::cust_header() - ), - ], - ) -%> diff --git a/httemplate/search/svc_www.cgi b/httemplate/search/svc_www.cgi deleted file mode 100755 index ae51c61fc..000000000 --- a/httemplate/search/svc_www.cgi +++ /dev/null @@ -1,69 +0,0 @@ -<% - -#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.*', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - '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', - FS::UI::Web::cust_header(), - ], - 'fields' => [ 'svcnum', - sub { $_[0]->domain_record->zone }, - sub { - my $svc_www = shift; - my $svc_acct = $svc_www->svc_acct; - $svc_acct - ? $svc_acct->email - : ''; - }, - \&FS::UI::Web::cust_fields, - ], - 'links' => [ $link, - '', - $ulink, - ( map { $link_cust } - FS::UI::Web::cust_header() - ), - ], - ) -%> |