diff options
Diffstat (limited to 'httemplate/search')
37 files changed, 4494 insertions, 0 deletions
diff --git a/httemplate/search/cust_bill.cgi b/httemplate/search/cust_bill.cgi new file mode 100755 index 000000000..5b0538ca3 --- /dev/null +++ b/httemplate/search/cust_bill.cgi @@ -0,0 +1,165 @@ +<% + +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 new file mode 100755 index 000000000..2108653a8 --- /dev/null +++ b/httemplate/search/cust_bill.html @@ -0,0 +1,179 @@ +<% + 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 new file mode 100644 index 000000000..d82a83368 --- /dev/null +++ b/httemplate/search/cust_bill_event.cgi @@ -0,0 +1,137 @@ +<% + +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 new file mode 100755 index 000000000..197f28028 --- /dev/null +++ b/httemplate/search/cust_bill_event.html @@ -0,0 +1,58 @@ +<%= include( + '/elements/header.html', + ( $cgi->param('failed') ? 'Failed invoice events' : 'Invoice events' ), + include('/elements/menubar.html', + 'Main menu' => $p, # popurl(2), + ), + + ) +%> + + <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 new file mode 100644 index 000000000..082ccc893 --- /dev/null +++ b/httemplate/search/cust_bill_pkg.cgi @@ -0,0 +1,148 @@ +<% + +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 new file mode 100755 index 000000000..279d682cd --- /dev/null +++ b/httemplate/search/cust_credit.html @@ -0,0 +1,97 @@ +<% + 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 new file mode 100755 index 000000000..03c2619af --- /dev/null +++ b/httemplate/search/cust_main-otaker.cgi @@ -0,0 +1,28 @@ +<HTML> + <HEAD> + <TITLE>Customer Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Customer Search + </FONT> + <BR> + <FORM ACTION="cust_main.cgi" METHOD="GET"> + Search for <B>Order taker</B>: + <INPUT TYPE="hidden" NAME="otaker_on" VALUE="TRUE"> + <% my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_main") + or die dbh->errstr; + $sth->execute() or die $sth->errstr; +# my @otakers = map { $_->[0] } @{$sth->selectall_arrayref}; + %> + <SELECT NAME="otaker"> + <% my $otaker; while ( $otaker = $sth->fetchrow_arrayref ) { %> + <OPTION><%= $otaker->[0] %></OTAKER> + <% } %> + </SELECT> + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_main-payinfo.html b/httemplate/search/cust_main-payinfo.html new file mode 100755 index 000000000..b82b610d8 --- /dev/null +++ b/httemplate/search/cust_main-payinfo.html @@ -0,0 +1,20 @@ +<HTML> + <HEAD> + <TITLE>Customer Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Customer Search + </FONT> + <BR> + <FORM ACTION="cust_main.cgi" METHOD="GET"> + Search for <B>Credit card #</B>: + <INPUT TYPE="hidden" NAME="card_on" VALUE="TRUE"> + <INPUT TYPE="text" NAME="card"> + + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_main-quickpay.html b/httemplate/search/cust_main-quickpay.html new file mode 100755 index 000000000..154a64199 --- /dev/null +++ b/httemplate/search/cust_main-quickpay.html @@ -0,0 +1,44 @@ +<HTML> + <HEAD> + <TITLE>Quick payment entry</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Quick payment entry + </FONT> + <BR><BR> + <A HREF="../">Main Menu</A><BR><BR> + <FORM ACTION="cust_main.cgi" METHOD="GET"> + <INPUT TYPE="hidden" NAME="quickpay" VALUE="yes"> + <INPUT TYPE="checkbox" NAME="last_on" CHECKED> Search for <B>last name</B>: + <INPUT TYPE="text" NAME="last_text"> + using search method: <SELECT NAME="last_type"> + <OPTION SELECTED>All + <OPTION>Fuzzy + <OPTION>Substring + <OPTION>Exact + </SELECT> + + <P><INPUT TYPE="checkbox" NAME="company_on" CHECKED> Search for <B>company</B>: + <INPUT TYPE="text" NAME="company_text"> + using search method: <SELECT NAME="company_type"> + <OPTION SELECTED>All + <OPTION>Fuzzy + <OPTION>Substring + <OPTION>Exact + </SELECT> + + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + + <HR>Explanation of search methods: + <UL> + <LI><B>All</B> - Try all search methods. + <LI><B>Fuzzy</B> - Searches for matches that are close to your text. + <LI><B>Substring</B> - Searches for matches that contain your text. + <LI><B>Exact</B> - Finds exact matches only, but much faster than the other search methods. + </UL> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi new file mode 100755 index 000000000..665f5637d --- /dev/null +++ b/httemplate/search/cust_main.cgi @@ -0,0 +1,685 @@ +<% + +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); + +#my $cache; + +#my $monsterjoin = <<END; +#cust_main left outer join ( +# ( cust_pkg left outer join part_pkg using(pkgpart) +# ) left outer join ( +# ( +# ( +# ( cust_svc left outer join part_svc using (svcpart) +# ) left outer join svc_acct using (svcnum) +# ) left outer join svc_domain using(svcnum) +# ) left outer join svc_forward using(svcnum) +# ) using (pkgnum) +#) using (custnum) +#END + +#my $monsterjoin = <<END; +#cust_main left outer join ( +# ( cust_pkg left outer join part_pkg using(pkgpart) +# ) left outer join ( +# ( +# ( +# ( cust_svc left outer join part_svc using (svcpart) +# ) left outer join ( +# svc_acct left outer join ( +# select svcnum, domain, catchall from svc_domain +# ) as svc_acct_domsvc ( +# svc_acct_svcnum, svc_acct_domain, svc_acct_catchall +# ) on svc_acct.domsvc = svc_acct_domsvc.svc_acct_svcnum +# ) using (svcnum) +# ) left outer join svc_domain using(svcnum) +# ) left outer join svc_forward using(svcnum) +# ) using (pkgnum) +#) using (custnum) +#END + +my $limit = ''; +$limit .= "LIMIT $maxrecords" if $maxrecords; + +my $offset = $cgi->param('offset') || 0; +$limit .= " OFFSET $offset" if $offset; + +my $total = 0; + +my(@cust_main, $sortby, $orderby); +my @select = (); +my @addl_headers = (); +my @addl_cols = (); +if ( $cgi->param('browse') + || $cgi->param('otaker_on') + || $cgi->param('agentnum_on') +) { + + my %search = (); + + if ( $cgi->param('browse') ) { + my $query = $cgi->param('browse'); + if ( $query eq 'custnum' ) { + $sortby=\*custnum_sort; + $orderby = "ORDER BY custnum"; + } elsif ( $query eq 'last' ) { + $sortby=\*last_sort; + $orderby = "ORDER BY LOWER(last || ' ' || first)"; + } elsif ( $query eq 'company' ) { + $sortby=\*company_sort; + $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; + } elsif ( $query eq 'tickets' ) { + $sortby = \*tickets_sort; + $orderby = "ORDER BY tickets DESC"; + push @select, FS::TicketSystem->sql_num_customer_tickets. " as tickets"; + push @addl_headers, 'Tickets'; + push @addl_cols, 'tickets'; + } else { + die "unknown browse field $query"; + } + } else { + $sortby = \*last_sort; #?? + $orderby = "ORDER BY LOWER(last || ' ' || first)"; #?? + } + + if ( $cgi->param('otaker_on') ) { + $cgi->param('otaker') =~ /^(\w{1,32})$/ or eidiot "Illegal otaker\n"; + $search{otaker} = $1; + } elsif ( $cgi->param('agentnum_on') ) { + $cgi->param('agentnum') =~ /^(\d+)$/ or eidiot "Illegal agentnum\n"; + $search{agentnum} = $1; +# } else { +# die "unknown query..."; + } + + my @qual = (); + + my $ncancelled = ''; + + if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $cgi->param('showcancelledcustomers') ) + ) { + #grep { $_->ncancelled_pkgs || ! $_->all_pkgs } + push @qual, " + ( 0 < ( SELECT COUNT(*) FROM cust_pkg + WHERE cust_pkg.custnum = cust_main.custnum + AND ( cust_pkg.cancel IS NULL + OR cust_pkg.cancel = 0 + ) + ) + OR 0 = ( SELECT COUNT(*) FROM cust_pkg + WHERE cust_pkg.custnum = cust_main.custnum + ) + ) + "; + } + + push @qual, FS::cust_main->cancel_sql if $cgi->param('cancelled'); + push @qual, FS::cust_main->prospect_sql if $cgi->param('prospect'); + push @qual, FS::cust_main->active_sql if $cgi->param('active'); + push @qual, FS::cust_main->susp_sql if $cgi->param('suspended'); + + #EWWWWWW + my $qual = join(' AND ', + map { "$_ = ". dbh->quote($search{$_}) } keys %search ); + + my $addl_qual = join(' AND ', @qual); + + if ( $addl_qual ) { + $qual .= ' AND ' if $qual; + $qual .= $addl_qual; + } + + $qual = " WHERE $qual" if $qual; + my $statement = "SELECT COUNT(*) FROM cust_main $qual"; + my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; + $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; + + $total = $sth->fetchrow_arrayref->[0]; + + if ( $addl_qual ) { + if ( %search ) { + $addl_qual = " AND $addl_qual"; + } else { + $addl_qual = " WHERE $addl_qual"; + } + } + + my $select; + if ( @select ) { + $select = 'cust_main.*, '. join (', ', @select); + } else { + $select = '*'; + } + + @cust_main = qsearch('cust_main', \%search, $select, + "$addl_qual $orderby $limit" ); + +# foreach my $cust_main ( @just_cust_main ) { +# +# my @one_cust_main; +# $FS::Record::DEBUG=1; +# ( $cache, @one_cust_main ) = jsearch( +# "$monsterjoin", +# { 'custnum' => $cust_main->custnum }, +# '', +# '', +# 'cust_main', +# 'custnum', +# ); +# push @cust_main, @one_cust_main; +# } + +} else { + @cust_main=(); + $sortby = \*last_sort; + + push @cust_main, @{&custnumsearch} + if $cgi->param('custnum_on') && $cgi->param('custnum_text'); + push @cust_main, @{&cardsearch} + if $cgi->param('card_on') && $cgi->param('card'); + push @cust_main, @{&lastsearch} + if $cgi->param('last_on') && $cgi->param('last_text'); + push @cust_main, @{&companysearch} + if $cgi->param('company_on') && $cgi->param('company_text'); + push @cust_main, @{&address2search} + if $cgi->param('address2_on') && $cgi->param('address2_text'); + push @cust_main, @{&phonesearch} + if $cgi->param('phone_on') && $cgi->param('phone_text'); + push @cust_main, @{&referralsearch} + if $cgi->param('referral_custnum'); + + if ( $cgi->param('company_on') && $cgi->param('company_text') ) { + $sortby = \*company_sort; + push @cust_main, @{&companysearch}; + } + + @cust_main = grep { $_->ncancelled_pkgs || ! $_->all_pkgs } @cust_main + if ! $cgi->param('cancelled') + && ( + $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $cgi->param('showcancelledcustomers') ) + ); + + my %saw = (); + @cust_main = grep { !$saw{$_->custnum}++ } @cust_main; +} + +my %all_pkgs; +if ( $conf->exists('hidecancelledpackages' ) ) { + %all_pkgs = map { $_->custnum => [ $_->ncancelled_pkgs ] } @cust_main; +} else { + %all_pkgs = map { $_->custnum => [ $_->all_pkgs ] } @cust_main; +} +#%all_pkgs = (); + +if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { + if ( $cgi->param('quickpay') eq 'yes' ) { + print $cgi->redirect(popurl(2). "edit/cust_pay.cgi?quickpay=yes;custnum=". $cust_main[0]->custnum); + } else { + print $cgi->redirect(popurl(2). "view/cust_main.cgi?". $cust_main[0]->custnum); + } + #exit; +} elsif ( scalar(@cust_main) == 0 ) { +%> +<!-- mason kludge --> +<% + eidiot "No matching customers found!\n"; +} else { +%> +<!-- mason kludge --> +<% + + $total ||= scalar(@cust_main); + print header("Customer Search Results",menubar( + 'Main Menu', popurl(2) + )), "$total matching customers found "; + + #begin pager + my $pager = ''; + if ( $total != scalar(@cust_main) && $maxrecords ) { + unless ( $offset == 0 ) { + $cgi->param('offset', $offset - $maxrecords); + $pager .= '<A HREF="'. $cgi->self_url. + '"><B><FONT SIZE="+1">Previous</FONT></B></A> '; + } + my $poff; + my $page; + for ( $poff = 0; $poff < $total; $poff += $maxrecords ) { + $page++; + if ( $offset == $poff ) { + $pager .= qq!<FONT SIZE="+2">$page</FONT> !; + } else { + $cgi->param('offset', $poff); + $pager .= qq!<A HREF="!. $cgi->self_url. qq!">$page</A> !; + } + } + unless ( $offset + $maxrecords > $total ) { + $cgi->param('offset', $offset + $maxrecords); + $pager .= '<A HREF="'. $cgi->self_url. + '"><B><FONT SIZE="+1">Next</FONT></B></A> '; + } + } + #end pager + + unless ( $cgi->param('cancelled') ) { + if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $cgi->param('showcancelledcustomers') + ) + ) { + $cgi->param('showcancelledcustomers', 1); + $cgi->param('offset', 0); + print qq!( <a href="!. $cgi->self_url. qq!">show!; + } else { + $cgi->param('showcancelledcustomers', 0); + $cgi->param('offset', 0); + print qq!( <a href="!. $cgi->self_url. qq!">hide!; + } + print ' cancelled customers</a> )'; + } + + if ( $cgi->param('referral_custnum') ) { + $cgi->param('referral_custnum') =~ /^(\d+)$/ + or eidiot "Illegal referral_custnum\n"; + my $referral_custnum = $1; + my $cust_main = qsearchs('cust_main', { custnum => $referral_custnum } ); + print '<FORM METHOD="GET">'. + qq!<INPUT TYPE="hidden" NAME="referral_custnum" VALUE="$referral_custnum">!. + 'referrals of <A HREF="'. popurl(2). + "view/cust_main.cgi?$referral_custnum\">$referral_custnum: ". + ( $cust_main->company + || $cust_main->last. ', '. $cust_main->first ). + '</A>'; + print "\n",<<END; + <SCRIPT> + function changed(what) { + what.form.submit(); + } + </SCRIPT> +END + print ' <SELECT NAME="referral_depth" SIZE="1" onChange="changed(this)">'; + my $max = 8; #config file + $cgi->param('referral_depth') =~ /^(\d*)$/ + or eidiot "Illegal referral_depth"; + my $referral_depth = $1; + + foreach my $depth ( 1 .. $max ) { + print '<OPTION', + ' SELECTED'x($depth == $referral_depth), + ">$depth"; + } + print "</SELECT> levels deep". + '<NOSCRIPT> <INPUT TYPE="submit" VALUE="change"></NOSCRIPT>'. + '</FORM>'; + } + + my @custom_priorities = (); + if ( $conf->config('ticket_system-custom_priority_field') + && @{[ $conf->config('ticket_system-custom_priority_field-values') ]} ) { + @custom_priorities = + $conf->config('ticket_system-custom_priority_field-values'); + } + + print "<BR><BR>". $pager. &table(). <<END; + <TR> + <TH></TH> + <TH>(bill) name</TH> + <TH>company</TH> +END + +if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <<END; + <TH>(service) name</TH> + <TH>company</TH> +END +} + +foreach my $addl_header ( @addl_headers ) { + print "<TH>$addl_header</TH>"; +} + +print <<END; + <TH>Packages</TH> + <TH COLSPAN=2>Services</TH> + </TR> +END + + my(%saw,$cust_main); + foreach $cust_main ( + sort $sortby grep(!$saw{$_->custnum}++, @cust_main) + ) { + my($custnum,$last,$first,$company)=( + $cust_main->custnum, + $cust_main->getfield('last'), + $cust_main->getfield('first'), + $cust_main->company, + ); + + my(@lol_cust_svc); + my($rowspan)=0;#scalar( @{$all_pkgs{$custnum}} ); + foreach ( @{$all_pkgs{$custnum}} ) { + #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); + my @cust_svc = $_->cust_svc; + push @lol_cust_svc, \@cust_svc; + $rowspan += scalar(@cust_svc) || 1; + } + + #my($rowspan) = scalar(@{$all_pkgs{$custnum}}); + my $view; + if ( defined $cgi->param('quickpay') && $cgi->param('quickpay') eq 'yes' ) { + $view = $p. 'edit/cust_pay.cgi?quickpay=yes;custnum='. $custnum; + } else { + $view = $p. 'view/cust_main.cgi?'. $custnum; + } + my $pcompany = $company + ? qq!<A HREF="$view"><FONT SIZE=-1>$company</FONT></A>! + : '<FONT SIZE=-1> </FONT>'; + print <<END; + <TR> + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$custnum</FONT></A></TD> + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$last, $first</FONT></A></TD> + <TD ROWSPAN=$rowspan>$pcompany</TD> +END + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + my($ship_last,$ship_first,$ship_company)=( + $cust_main->ship_last || $cust_main->getfield('last'), + $cust_main->ship_last ? $cust_main->ship_first : $cust_main->first, + $cust_main->ship_last ? $cust_main->ship_company : $cust_main->company, + ); + my $pship_company = $ship_company + ? qq!<A HREF="$view"><FONT SIZE=-1>$ship_company</FONT></A>! + : '<FONT SIZE=-1> </FONT>'; + print <<END; + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$ship_last, $ship_first</FONT></A></TD> + <TD ROWSPAN=$rowspan>$pship_company</A></TD> +END + } + + foreach my $addl_col ( @addl_cols ) { + print "<TD ROWSPAN=$rowspan ALIGN=right><FONT SIZE=-1>"; + if ( $addl_col eq 'tickets' ) { + if ( @custom_priorities ) { + print &itable('', 0); + foreach my $priority ( @custom_priorities, '' ) { + + my $num = + FS::TicketSystem->num_customer_tickets($custnum,$priority); + my $ahref = ''; + $ahref= '<A HREF="'. + FS::TicketSystem->href_customer_tickets($custnum,$priority). + '">' + if $num; + + print '<TR>'. + " <TD ALIGN=right><FONT SIZE=-1>$ahref$num</A></FONT></TD>". + "<TD ALIGN=left><FONT SIZE=-1>$ahref". + ( $priority || '<i>(none)</i>' ). + "</A></FONT></TD></TR>"; + + } + print '<TR><TD BGCOLOR="#000000" COLSPAN=2></TD></TR>'. + '<TR><TD ALIGN=right><FONT SIZE=-1>'; + } + + my $ahref = ''; + $ahref = '<A HREF="'. + FS::TicketSystem->href_customer_tickets($custnum). + '">' + if $cust_main->get($addl_col); + + print $ahref. $cust_main->get($addl_col). '</A>'; + print "</FONT></TD><TD ALIGN=left>". + "<FONT SIZE=-1>${ahref}Total</A><FONT>". + "</TD></TR></TABLE>" + if @custom_priorities; + + } else { + print $cust_main->get($addl_col); + } + print "</FONT></TD>"; + } + + my($n1)=''; + foreach ( @{$all_pkgs{$custnum}} ) { + my $pkgnum = $_->pkgnum; +# my $part_pkg = qsearchs( 'part_pkg', { pkgpart => $_->pkgpart } ); + my $part_pkg = $_->part_pkg; + + my $pkg = $part_pkg->pkg; + my $comment = $part_pkg->comment; + my $pkgview = "${p}view/cust_main.cgi?$custnum#cust_pkg$pkgnum"; + my @cust_svc = @{shift @lol_cust_svc}; + #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); + my $rowspan = scalar(@cust_svc) || 1; + + print $n1, qq!<TD ROWSPAN=$rowspan><A HREF="$pkgview"><FONT SIZE=-1>$pkg - $comment</FONT></A></TD>!; + my($n2)=''; + foreach my $cust_svc ( @cust_svc ) { + my($label, $value, $svcdb) = $cust_svc->label; + my($svcnum) = $cust_svc->svcnum; + my($sview) = $p.'view'; + print $n2,qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!, + qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!; + $n2="</TR><TR>"; + } + #print qq!</TR><TR>\n!; + $n1="</TR><TR>"; + } + print "</TR>"; + } + + print "</TABLE>$pager</BODY></HTML>"; + +} + +#undef $cache; #does this help? + +# + +sub last_sort { + lc($a->getfield('last')) cmp lc($b->getfield('last')) + || lc($a->first) cmp lc($b->first); +} + +sub company_sort { + return -1 if $a->company && ! $b->company; + return 1 if ! $a->company && $b->company; + lc($a->company) cmp lc($b->company) + || lc($a->getfield('last')) cmp lc($b->getfield('last')) + || lc($a->first) cmp lc($b->first);; +} + +sub custnum_sort { + $a->getfield('custnum') <=> $b->getfield('custnum'); +} + +sub tickets_sort { + $b->getfield('tickets') <=> $a->getfield('tickets'); +} + +sub custnumsearch { + + my $custnum = $cgi->param('custnum_text'); + $custnum =~ s/\D//g; + $custnum =~ /^(\d{1,23})$/ or eidiot "Illegal customer number\n"; + $custnum = $1; + + [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; +} + +sub cardsearch { + + my($card)=$cgi->param('card'); + $card =~ s/\D//g; + $card =~ /^(\d{13,16})$/ or eidiot "Illegal card number\n"; + my($payinfo)=$1; + + [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}), + qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'}) + ]; +} + +sub referralsearch { + $cgi->param('referral_custnum') =~ /^(\d+)$/ + or eidiot "Illegal referral_custnum"; + my $cust_main = qsearchs('cust_main', { 'custnum' => $1 } ) + or eidiot "Customer $1 not found"; + my $depth; + if ( $cgi->param('referral_depth') ) { + $cgi->param('referral_depth') =~ /^(\d+)$/ + or eidiot "Illegal referral_depth"; + $depth = $1; + } else { + $depth = 1; + } + [ $cust_main->referral_cust_main($depth) ]; +} + +sub lastsearch { + my(%last_type); + my @cust_main; + foreach ( $cgi->param('last_type') ) { + $last_type{$_}++; + } + + $cgi->param('last_text') =~ /^([\w \,\.\-\']*)$/ + or eidiot "Illegal last name"; + my($last)=$1; + + if ( $last_type{'Exact'} || $last_type{'Fuzzy'} ) { + push @cust_main, qsearch( 'cust_main', + { 'last' => { 'op' => 'ILIKE', + 'value' => $last } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_last' => { 'op' => 'ILIKE', + 'value' => $last } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + } + + if ( $last_type{'Substring'} || $last_type{'All'} ) { + + push @cust_main, qsearch( 'cust_main', + { 'last' => { 'op' => 'ILIKE', + 'value' => "%$last%" } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_last' => { 'op' => 'ILIKE', + 'value' => "%$last%" } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + + } + + if ( $last_type{'Fuzzy'} || $last_type{'All'} ) { + push @cust_main, FS::cust_main->fuzzy_search( { 'last' => $last } ); + } + + #if ($last_type{'Sound-alike'}) { + #} + + \@cust_main; +} + +sub companysearch { + + my(%company_type); + my @cust_main; + foreach ( $cgi->param('company_type') ) { + $company_type{$_}++ + }; + + $cgi->param('company_text') =~ + /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ + or eidiot "Illegal company"; + my $company = $1; + + if ( $company_type{'Exact'} || $company_type{'Fuzzy'} ) { + push @cust_main, qsearch( 'cust_main', + { 'company' => { 'op' => 'ILIKE', + 'value' => $company } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_company' => { 'op' => 'ILIKE', + 'value' => $company } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + } + + if ( $company_type{'Substring'} || $company_type{'All'} ) { + + push @cust_main, qsearch( 'cust_main', + { 'company' => { 'op' => 'ILIKE', + 'value' => "%$company%" } } ); + + push @cust_main, qsearch( 'cust_main', + { 'ship_company' => { 'op' => 'ILIKE', + 'value' => "%$company%" } }) + if defined dbdef->table('cust_main')->column('ship_last'); + + } + + if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { + push @cust_main, FS::cust_main->fuzzy_search( { 'company' => $company } ); + } + + if ($company_type{'Sound-alike'}) { + } + + \@cust_main; +} + +sub address2search { + my @cust_main; + + $cgi->param('address2_text') =~ + /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ + or eidiot "Illegal address2"; + my $address2 = $1; + + push @cust_main, qsearch( 'cust_main', + { 'address2' => { 'op' => 'ILIKE', + 'value' => $address2 } } ); + push @cust_main, qsearch( 'cust_main', + { 'address2' => { 'op' => 'ILIKE', + 'value' => $address2 } } ) + if defined dbdef->table('cust_main')->column('ship_last'); + + \@cust_main; +} + +sub phonesearch { + my @cust_main; + + my $phone = $cgi->param('phone_text'); + + #(no longer really) false laziness with Record::ut_phonen + #only works with US/CA numbers... + $phone =~ s/\D//g; + if ( $phone =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ ) { + $phone = "$1-$2-$3"; + $phone .= " x$4" if $4; + } elsif ( $phone =~ /^(\d{3})(\d{4})$/ ) { + $phone = "$1-$2"; + } elsif ( $phone =~ /^(\d{3,4})$/ ) { + $phone = $1; + } else { + eidiot gettext('illegal_phone'). ": $phone"; + } + + my @fields = qw(daytime night fax); + push @fields, qw(ship_daytime ship_night ship_fax) + if defined dbdef->table('cust_main')->column('ship_last'); + + for my $field ( @fields ) { + push @cust_main, qsearch ( 'cust_main', + { $field => { 'op' => 'LIKE', + 'value' => "%$phone%" } } ); + } + + \@cust_main; +} + +%> diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html new file mode 100755 index 000000000..4f7508447 --- /dev/null +++ b/httemplate/search/cust_main.html @@ -0,0 +1,42 @@ +<HTML> + <HEAD> + <TITLE>Customer Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Customer Search + </FONT> + <BR><BR> + <FORM ACTION="cust_main.cgi" METHOD="GET"> + <INPUT TYPE="checkbox" NAME="last_on" CHECKED> Search for <B>last name</B>: + <INPUT TYPE="text" NAME="last_text"> + using search method: <SELECT NAME="last_type"> + <OPTION SELECTED>All + <OPTION>Fuzzy + <OPTION>Substring + <OPTION>Exact + </SELECT> + + <P><INPUT TYPE="checkbox" NAME="company_on" CHECKED> Search for <B>company</B>: + <INPUT TYPE="text" NAME="company_text"> + using search methods: <SELECT NAME="company_type"> + <OPTION SELECTED>All + <OPTION>Fuzzy + <OPTION>Substring + <OPTION>Exact + </SELECT> + + <P><INPUT TYPE="submit" VALUE="Search"> Note: Fuzzy searching can take a while. Please be patient. + + </FORM> + + <HR>Explanation of search methods: + <UL> + <LI><B>All</B> - Try all search methods. + <LI><B>Fuzzy</B> - Searches for matches that are close to your text. + <LI><B>Substring</B> - Searches for matches that contain your text. + <LI><B>Exact</B> - Finds exact matches only, but much faster than the other search methods. + </UL> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_pay.cgi b/httemplate/search/cust_pay.cgi new file mode 100755 index 000000000..99ffc3d20 --- /dev/null +++ b/httemplate/search/cust_pay.cgi @@ -0,0 +1,192 @@ +<% + 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 new file mode 100755 index 000000000..6414cf771 --- /dev/null +++ b/httemplate/search/cust_pay.html @@ -0,0 +1,18 @@ +<HTML> + <HEAD> + <TITLE>Check # Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Check # Search + </FONT> + <BR><BR> + <FORM ACTION="cust_pay.cgi" METHOD="GET"> + Search for <B>check #</B>: + <INPUT TYPE="text" NAME="payinfo"> + <INPUT TYPE="hidden" NAME="payby" VALUE="BILL"> + <BR><BR><INPUT TYPE="submit" VALUE="Search"> + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi new file mode 100755 index 000000000..5da4d82fb --- /dev/null +++ b/httemplate/search/cust_pkg.cgi @@ -0,0 +1,234 @@ +<% + +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 new file mode 100755 index 000000000..412c3f79d --- /dev/null +++ b/httemplate/search/cust_pkg_report.cgi @@ -0,0 +1,23 @@ +<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 new file mode 100644 index 000000000..d19fb4acd --- /dev/null +++ b/httemplate/search/elements/search.html @@ -0,0 +1,392 @@ +<% + + 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 new file mode 100644 index 000000000..8c8f57b5a --- /dev/null +++ b/httemplate/search/prepay_credit.html @@ -0,0 +1,43 @@ +<% +my $agent = ''; +my $hashref = {}; +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $hashref->{agentnum} = $1; + $agent = qsearchs('agent', { 'agentnum' => $1 } ); +} + +my $count_query = 'SELECT COUNT(*) FROM prepay_credit'; +$count_query .= ' WHERE agentnum = '. $agent->agentnum if $agent; + +%><%= include( 'elements/search.html', + 'title' => 'Unused Prepaid Cards'. + ($agent ? ' for '. $agent->agent : ''), + 'menubar' => [ + 'Main menu' => $p, + 'Generate cards' => $p.'edit/prepay_credit.cgi', + ], + 'name' => 'prepaid cards', + 'query' => { 'table' => 'prepay_credit', + 'hashref' => $hashref, + }, + 'count_query' => $count_query, + #'redirect' => $link, + 'header' => [ '#', qw(Amount Time Agent) ], + 'fields' => [ + 'identifier', + sub { sprintf('$%.2f', shift->amount ) }, + sub { my $c = shift; $c ? duration_exact($c->seconds) : '' }, + sub { my $agent = shift->agent; + $agent ? $agent->agent : ''; + }, + ], + 'links' => [ + '', + '', + '', + sub { my $agent = shift->agent; + $agent ? [ "${p}view/agent.cgi?", 'agentnum' ] : ''; + }, + ], + ) +%> diff --git a/httemplate/search/reg_code.html b/httemplate/search/reg_code.html new file mode 100644 index 000000000..52a99ff66 --- /dev/null +++ b/httemplate/search/reg_code.html @@ -0,0 +1,36 @@ +<% + +my $agentnum = $cgi->param('agentnum'); +$agentnum =~ /^(\d+)$/ or eidiot "illegal agentnum $agentnum"; +$agentnum = $1; +my $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + +my $count_query = "SELECT COUNT(*) FROM reg_code WHERE agentnum = $agentnum"; + +%><%= include( 'elements/search.html', + 'title' => 'Unused Registration Codes for '. + $agent->agent, + 'name' => 'registration codes', + 'query' => { 'table' => 'reg_code', + 'hashref' => { 'agentnum' => $agentnum, }, + }, + 'count_query' => $count_query, + #'redirect' => $link, + 'header' => [ qw(Code Packages) ], + 'fields' => [ + 'code', + sub { + map { + qq!<A HREF="${p}edit/part_pkg.cgi?!. $_->pkgpart. '">'. + $_->pkg. ' - '. $_->comment. + '</A><BR>' + } $_[0]->part_pkg + }, + ], + 'links' => [ + '', + #$plink, + '', + ], + ) +%> diff --git a/httemplate/search/report_cust_bill.html b/httemplate/search/report_cust_bill.html new file mode 100644 index 000000000..a7be76689 --- /dev/null +++ b/httemplate/search/report_cust_bill.html @@ -0,0 +1,28 @@ + <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 new file mode 100644 index 000000000..56bbd0ac0 --- /dev/null +++ b/httemplate/search/report_cust_credit.html @@ -0,0 +1,36 @@ +<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 new file mode 100644 index 000000000..5d8b74e77 --- /dev/null +++ b/httemplate/search/report_cust_pay.html @@ -0,0 +1,38 @@ +<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 new file mode 100644 index 000000000..1677591a3 --- /dev/null +++ b/httemplate/search/report_prepaid_income.cgi @@ -0,0 +1,86 @@ +<!-- mason kludge --> +<% + + #doesn't yet deal with daily/weekly packages + + #needs to be re-written in sql for efficiency + + my $time = time; + + my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time; + $now =~ /^(\d+)$/ or die "unparsable date?"; + $now = $1; + + my( $total, $total_legacy ) = ( 0, 0 ); + + my @cust_bill_pkg = + grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[dw])$/ } + qsearch( 'cust_bill_pkg', { + 'recur' => { op=>'!=', value=>0 }, + 'edate' => { op=>'>', value=>$now }, + }, ); + + my @cust_pkg = + grep { $_->part_pkg->recur != 0 + && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/ + } + qsearch ( 'cust_pkg', { + 'bill' => { op=>'>', value=>$now } + } ); + + foreach my $cust_bill_pkg ( @cust_bill_pkg) { + my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; + + my $elapsed = $now - $cust_bill_pkg->sdate; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + my $unearned = $remaining * $cust_bill_pkg->recur; + $total += $unearned; + + } + + foreach my $cust_pkg ( @cust_pkg ) { + my $period = $cust_pkg->bill - $cust_pkg->last_bill; + + my $elapsed = $now - $cust_pkg->last_bill; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy + $total_legacy += $unearned; + + } + + $total = sprintf('%.2f', $total); + $total_legacy = sprintf('%.2f', $total_legacy); + +%> + +<%= header( 'Prepaid Income (Unearned Revenue) Report', + menubar( 'Main Menu'=>$p, ) ) %> +<%= table() %> + <TR> + <TH>Actual Unearned Revenue</TH> + <TH>Legacy Unearned Revenue</TH> + </TR> + <TR> + <TD ALIGN="right">$<%= $total %> + <TD ALIGN="right"> + <%= $now == $time ? "\$$total_legacy" : '<i>N/A</i>'%> + </TD> + </TR> + +</TABLE> +<BR> +Actual unearned revenue is the amount of unearned revenue Freeside has +actually invoiced for packages with longer-than monthly terms. +<BR><BR> +Legacy unearned revenue is the amount of unearned revenue represented by +customer packages. This number may be larger than actual unearned +revenue if you have imported longer-than monthly customer packages from +a previous billing system. +</BODY> +</HTML> diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html new file mode 100644 index 000000000..57c318eba --- /dev/null +++ b/httemplate/search/report_prepaid_income.html @@ -0,0 +1,39 @@ +<HTML> + <HEAD> + <TITLE>Prepaid Income (Unearned Revenue) Report</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Prepaid Income (Unearned Revenue) Report</H1> + <FORM ACTION="report_prepaid_income.cgi" METHOD="GET"> + <TABLE> + <TR> + <TD>Prepaid income (unearned revenue) as of </TD> + <TD> + <INPUT TYPE="text" NAME="date" ID="date_text" VALUE="now"> + <IMG SRC="../images/calendar.png" ID="date_button" STYLE="cursor: pointer" TITLE="Select date"> + </TD> + </TR> + <TR> + <TD> + </TD> + <TD><i>m/d/y</i></TD> + </TR> + </TABLE> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "date_text", + ifFormat: "%m/%d/%Y", + button: "date_button", + align: "BR" + }); +</SCRIPT> + + <INPUT TYPE="submit" VALUE="Generate report"> + </BODY> +</HTML> + <TABLE> + diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi new file mode 100755 index 000000000..d675346f0 --- /dev/null +++ b/httemplate/search/report_receivables.cgi @@ -0,0 +1,232 @@ +<% + + 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 new file mode 100755 index 000000000..9062f0626 --- /dev/null +++ b/httemplate/search/report_tax.cgi @@ -0,0 +1,432 @@ +<% + +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 new file mode 100755 index 000000000..eeaccc1ab --- /dev/null +++ b/httemplate/search/report_tax.html @@ -0,0 +1,22 @@ +<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 new file mode 100644 index 000000000..b28c045d1 --- /dev/null +++ b/httemplate/search/sql.html @@ -0,0 +1,7 @@ +<%= include( 'elements/search.html', + 'title' => 'Query Results', + 'name' => 'rows', + 'query' => 'SELECT '. ( $cgi->param('sql') + || eidiot('Empty query') ), + ) +%> diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi new file mode 100644 index 000000000..b84df1a03 --- /dev/null +++ b/httemplate/search/sqlradius.cgi @@ -0,0 +1,290 @@ +<%= include( '/elements/header.html', 'RADIUS Sessions', + include('/elements/menubar.html', + 'Main menu' => $p, # popurl(2), + ), + + ) +%> + +<% + ### + # parse cgi params + ### + + #sort of false laziness w/cust_pay.cgi + my $beginning = ''; + my $ending = ''; + if ( $cgi->param('beginning') + && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $beginning = str2time($1); + } + if ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $ending = str2time($1) + 86399; + } + if ( $cgi->param('begin') && $cgi->param('begin') =~ /^(\d+)$/ ) { + $beginning = $1; + } + if ( $cgi->param('end') && $cgi->param('end') =~ /^(\d+)$/ ) { + $ending = $1; + } + + my $cgi_svc_acct = ''; + if ( $cgi->param('svcnum') =~ /^(\d+)$/ ) { + $cgi_svc_acct = qsearchs( 'svc_acct', { 'svcnum' => $1 } ); + } elsif ( $cgi->param('username') =~ /^([^@]+)\@([^@]+)$/ ) { + my %search = { 'username' => $1 }; + my $svc_domain = qsearchs('svc_domain', { 'domain' => $2 } ); + if ( $svc_domain ) { + $search{'domsvc'} = $svc_domain->svcnum; + } else { + delete $search{'username'}; + } + $cgi_svc_acct = qsearchs( 'svc_acct', \%search ) + if keys %search; + } elsif ( $cgi->param('username') =~ /^(.+)$/ ) { + $cgi_svc_acct = qsearchs( 'svc_acct', { 'username' => $1 } ); + } + + my $ip = ''; + if ( $cgi->param('ip') =~ /^((\d+\.){3}\d+)$/ ) { + $ip = $1; + } + + my $prefix = $cgi->param('prefix'); + $prefix =~ s/\D//g; + if ( $prefix =~ /^(\d+)$/ ) { + $prefix = $1; + $prefix = "011$prefix" unless $prefix =~ /^1/; + } else { + $prefix = ''; + } + + ### + # field formatting subroutines + ### + + my %user2svc_acct = (); + my $user_format = sub { + my ( $user, $session, $part_export ) = @_; + + my $svc_acct = ''; + if ( exists $user2svc_acct{$user} ) { + $svc_acct = $user2svc_acct{$user}; + } else { + my %search = (); + if ( $part_export->exporttype eq 'sqlradius_withdomain' ) { + my $domain; + if ( $user =~ /^([^@]+)\@([^@]+)$/ ) { + $search{'username'} = $1; + $domain = $2; + } else { + $search{'username'} = $user; + $domain = $session->{'realm'}; + } + my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ); + if ( $svc_domain ) { + $search{'domsvc'} = $svc_domain->svcnum; + } else { + delete $search{'username'}; + } + } elsif ( $part_export->exporttype eq 'sqlradius' ) { + $search{'username'} = $user; + } else { + die 'unknown export type '. $part_export->exporttype. + " for $part_export\n"; + } + if ( keys %search ) { + my @svc_acct = + grep { qsearchs( 'export_svc', { + 'exportnum' => $part_export->exportnum, + 'svcpart' => $_->cust_svc->svcpart, + } ) + } qsearch( 'svc_acct', \%search ); + if ( @svc_acct ) { + warn 'multiple svc_acct records for user $user found; '. + 'using first arbitrarily' + if scalar(@svc_acct) > 1; + $user2svc_acct{$user} = $svc_acct = shift @svc_acct; + } + } + } + + if ( $svc_acct ) { + my $svcnum = $svc_acct->svcnum; + qq(<A HREF="${p}view/svc_acct.cgi?$svcnum"><B>$user</B></A>); + } else { + "<B>$user</B>"; + } + + }; + + my $customer_format = sub { + my( $unused, $session ) = @_; + return ' ' unless exists $user2svc_acct{$session->{'username'}}; + my $svc_acct = $user2svc_acct{$session->{'username'}}; + my $cust_pkg = $svc_acct->cust_svc->cust_pkg; + return ' ' unless $cust_pkg; + my $cust_main = $cust_pkg->cust_main; + + qq!<A HREF="${p}view/cust_main.cgi?!. $cust_main->custnum. '">'. + $cust_pkg->cust_main->name. '</A>'; + }; + + my $time_format = sub { + my $time = shift; + return ' ' if $time == 0; + my $pretty = time2str('%T%P %a %b %o %Y', $time ); + $pretty =~ s/ (\d)(st|dn|rd|th)/$1$2/; + $pretty; + }; + + my $duration_format = sub { + my $seconds = shift; + my $hour = int($seconds/3600); + my $min = int( ($seconds%3600) / 60 ); + my $sec = $seconds%60; + '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0>'. + '<TR><TD ALIGN="right">'. + ( $hour ? "<B>$hour</B>h" : ' ' ). + '</TD><TD ALIGN="right">'. + ( ( $hour || $min ) ? "<B>$min</B>m" : ' ' ). + '</TD><TD ALIGN="right">'. + "<B>$sec</B>s". + '</TD></TR></TABLE>'; + }; + + my $octets_format = sub { + my $octets = shift; + my $megs = $octets / 1048576; + sprintf('<B>%.3f</B> megs', $megs); + #my $gigs = $octets / 1073741824 + #sprintf('<B>%.3f</B> gigabytes', $gigs); + }; + + ### + # the fields + ### + + tie my %fields, 'Tie::IxHash', + 'username' => { + name => 'User', + attrib => 'UserName', + fmt => $user_format, + align => 'left', + }, + 'realm' => { + name => 'Realm', + attrib => 'Realm', + align => 'left', + }, + 'dummy' => { + name => 'Customer', + attrib => '', + fmt => $customer_format, + align => 'left', + }, + 'framedipaddress' => { + name => 'IP Address', + attrib => 'Framed-IP-Address', + fmt => sub { my $ip = shift; + length($ip) ? $ip : ' '; + }, + align => 'right', + }, + 'acctstarttime' => { + name => 'Start time', + attrib => 'Acct-Start-Time', + fmt => $time_format, + align => 'left', + }, + 'acctstoptime' => { + name => 'End time', + attrib => 'Acct-Stop-Time', + fmt => $time_format, + align => 'left', + }, + 'acctsessiontime' => { + name => 'Duration', + attrib => 'Acct-Session-Time', + fmt => $duration_format, + align => 'right', + }, + 'acctinputoctets' => { + name => 'Upload', # (from user)', + attrib => 'Acct-Input-Octets', + fmt => $octets_format, + align => 'right', + }, + 'acctoutputoctets' => { + name => 'Download', # (to user)', + attrib => 'Acct-Output-Octets', + fmt => $octets_format, + align => 'right', + }, + ; + $fields{$_}->{fmt} ||= sub { length($_[0]) ? shift : ' '; } + foreach keys %fields; + + ### + # and finally, display the thing + ### + + foreach my $part_export ( + #grep $_->can('usage_sessions'), qsearch( 'part_export' ) + qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ), + qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } ) + ) { + %user2svc_acct = (); + + my $efields = tie my %efields, 'Tie::IxHash', %fields; + delete $efields{'framedipaddress'} if $part_export->option('hide_ip'); + if ( $part_export->option('hide_data') ) { + delete $efields{$_} foreach qw(acctinputoctets acctoutputoctets); + } + if ( $part_export->option('show_called_station') ) { + $efields->Splice(1, 0, + 'calledstationid' => { + 'name' => 'Destination', + 'attrib' => 'Called-Station-ID', + 'fmt' => + sub { length($_[0]) ? shift : ' '; }, + 'align' => 'left', + }, + ); + } + +%> + +<%= $part_export->exporttype %> to <%= $part_export->machine %><BR> +<%= include( '/elements/table.html' ) %> +<TR> + <% foreach my $field ( keys %efields ) { %> + <TH> + <%= $efields{$field}->{name} %><BR> + <FONT SIZE=-2><%= $efields{$field}->{attrib} %></FONT> + </TH> + <% } %> +</TR> +<% foreach my $session ( + @{ $part_export->usage_sessions( + $beginning, $ending, $cgi_svc_acct, $ip, $prefix, ) } + ) { +%> + <TR> + <% foreach my $field ( keys %efields ) { %> + <TD ALIGN="<%= $efields{$field}->{align} %>"> + <%= &{ $efields{$field}->{fmt} }( $session->{$field}, + $session, + $part_export, + ) + %> + </TD> + <% } %> + </TR> +<% } %> + +</TABLE> +<BR><BR> + +<% } %> diff --git a/httemplate/search/sqlradius.html b/httemplate/search/sqlradius.html new file mode 100644 index 000000000..8f4878dbc --- /dev/null +++ b/httemplate/search/sqlradius.html @@ -0,0 +1,94 @@ +<%= include( '/elements/header.html', 'Search RADIUS sessions', '', '', ' +<LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> +<SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> +<SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> +<SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> +') %> +<FORM NAME="OneTrueForm" ACTION="sqlradius.cgi" METHOD="GET"> +<% #include( '/elements/table.html' ) %> +<%= ntable('#cccccc') %> +<TR> + <TD ALIGN="right">Username: </TD> + <TD><INPUT TYPE="text" NAME="username"></TD> +</TR> +<TR> + <TD></TD> + <TD><FONT SIZE="-1"><I>(leave blank to show all users)</I></FONT></TD> +</TR> + +<% my @part_export = qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ); + push @part_export, + qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } ); +%> + +<% if ( grep { ! $_->option('hide_ip') } @part_export ) { %> + <TR> + <TD ALIGN="right">IP address: </TD> + <TD><INPUT TYPE="text" NAME="ip"></TD> + </TR> + <TR> + <TD></TD> + <TD><FONT SIZE="-1"><I>(leave blank to show all IPs)</I></FONT></TD> + </TR> +<% } %> + +<% if ( grep { $_->option('show_called_station') } @part_export ) { %> + <TR> + <TD ALIGN="right">Destination prefix:</TD> + <TD><INPUT TYPE="text" NAME="prefix"></TD> + </TR> + <TR> + <TD></TD> + <TD><FONT SIZE="-1"><I>(country code or country code and prefix)</I></FONT></TD> + </TR> + <TR> + <TD></TD> + <TD><FONT SIZE="-1"><I>(leave blank to show all destinations)</I></FONT></TD> + </TR> +<% } %> + +<TR> + <TD ALIGN="right">From: </TD> + <TD> + <INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"> + </TD> + <SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); + </SCRIPT> +</TR> +<TR> + <TD></TD> + <TD><i>m/d/y</i></TD> +</TR> +<TR> + <TD ALIGN="right">To: </TD> + <TD> + <INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor:pointer" TITLE="Select date"> + </TD> + <SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); + </SCRIPT> +</TR> +<TR> + <TD></TD> + <TD><i>m/d/y</i> + <BR><FONT SIZE="-1">(leave one or both dates blank for an open-ended search)</FONT> + </TD> +</TR> +</TABLE> +<BR><INPUT TYPE="submit" VALUE="View sessions"> +</FORM> +</BODY> +</HTML> + + diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi new file mode 100755 index 000000000..b14591958 --- /dev/null +++ b/httemplate/search/svc_acct.cgi @@ -0,0 +1,140 @@ +<% + +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 new file mode 100755 index 000000000..c504c2f34 --- /dev/null +++ b/httemplate/search/svc_acct.html @@ -0,0 +1,19 @@ +<HTML> + <HEAD> + <TITLE>Account Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Account Search + </FONT> + <BR><BR> + <FORM ACTION="svc_acct.cgi" METHOD="GET"> + Search for <B>username</B>: + <INPUT TYPE="text" NAME="username"> + + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/svc_broadband.cgi b/httemplate/search/svc_broadband.cgi new file mode 100755 index 000000000..efadce600 --- /dev/null +++ b/httemplate/search/svc_broadband.cgi @@ -0,0 +1,96 @@ +<% + +my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors +my(@svc_broadband,$sortby); +if ( $query eq 'svcnum' ) { + $sortby=\*svcnum_sort; + @svc_broadband=qsearch('svc_broadband',{}); +} elsif ( $query eq 'blocknum' ) { + $sortby=\*blocknum_sort; + @svc_broadband=qsearch('svc_broadband',{}); +} else { + $cgi->param('ip_addr') =~ /^(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})$/; + my($ip_addr)=$1; + @svc_broadband = qsearchs('svc_broadband',{'ip_addr'=>$ip_addr}); +} + +my %routerbyblock = (); +foreach my $router (qsearch('router', {})) { + foreach ($router->addr_block) { + $routerbyblock{$_->blocknum} = $router; + } +} + +if ( scalar(@svc_broadband) == 1 ) { + print $cgi->redirect(popurl(2). "view/svc_broadband.cgi?". $svc_broadband[0]->svcnum); + #exit; +} elsif ( scalar(@svc_broadband) == 0 ) { +%> +<!-- mason kludge --> +<% + eidiot "No matching ip address found!\n"; +} else { +%> +<!-- mason kludge --> +<% + my($total)=scalar(@svc_broadband); + print header("IP Address Search Results",''), <<END; + + $total matching broadband services found + <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> + <TR> + <TH>Service #</TH> + <TH>Router</TH> + <TH>IP Address</TH> + </TR> +END + + foreach my $svc_broadband ( + sort $sortby (@svc_broadband) + ) { + my($svcnum,$ip_addr,$routername,$routernum)=( + $svc_broadband->svcnum, + $svc_broadband->ip_addr, + $routerbyblock{$svc_broadband->blocknum}->routername, + $routerbyblock{$svc_broadband->blocknum}->routernum, + ); + + my $rowspan = 1; + + print <<END; + <TR> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_broadband.cgi?$svcnum">$svcnum</A></TD> + <TD ROWSPAN=$rowspan><A HREF="${p}view/router.cgi?$routernum">$routername</A></TD> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_broadband.cgi?$svcnum">$ip_addr</A></TD> +END + + #print @rows; + print "</TR>"; + + } + + print <<END; + </TABLE> + </BODY> +</HTML> +END + +} + +sub svcnum_sort { + $a->getfield('svcnum') <=> $b->getfield('svcnum'); +} + +sub blocknum_sort { + if ($a->getfield('blocknum') == $b->getfield('blocknum')) { + $a->getfield('ip_addr') cmp $b->getfield('ip_addr'); + } else { + $a->getfield('blocknum') cmp $b->getfield('blocknum'); + } +} + + +%> diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi new file mode 100755 index 000000000..f261ea9f3 --- /dev/null +++ b/httemplate/search/svc_domain.cgi @@ -0,0 +1,85 @@ +<% + +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 new file mode 100755 index 000000000..b759102f4 --- /dev/null +++ b/httemplate/search/svc_domain.html @@ -0,0 +1,19 @@ +<HTML> + <HEAD> + <TITLE>Domain Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Domain Search + </FONT> + <BR><BR> + <FORM ACTION="svc_domain.cgi" METHOD="GET"> + Search for <B>domain</B>: + <INPUT TYPE="text" NAME="domain"> + + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/svc_external.cgi b/httemplate/search/svc_external.cgi new file mode 100755 index 000000000..c5ac13498 --- /dev/null +++ b/httemplate/search/svc_external.cgi @@ -0,0 +1,101 @@ +<% + +my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors +my(@svc_external,$sortby); +if ( $query eq 'svcnum' ) { + $sortby=\*svcnum_sort; + @svc_external=qsearch('svc_external',{}); +} elsif ( $query eq 'id' ) { + $sortby=\*id_sort; + @svc_external=qsearch('svc_external',{}); +} elsif ( $query eq 'UN_svcnum' ) { + $sortby=\*svcnum_sort; + @svc_external = grep qsearchs('cust_svc',{ + 'svcnum' => $_->svcnum, + 'pkgnum' => '', + }), qsearch('svc_external',{}); +} elsif ( $query eq 'UN_id' ) { + $sortby=\*id_sort; + @svc_external = grep qsearchs('cust_svc',{ + 'svcnum' => $_->svcnum, + 'pkgnum' => '', + }), qsearch('svc_external',{}); +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + @svc_external = + qsearch( 'svc_external', {}, '', + " WHERE $1 = ( SELECT svcpart FROM cust_svc ". + " WHERE cust_svc.svcnum = svc_external.svcnum ) " + ); + $sortby=\*svcnum_sort; +} else { + $cgi->param('id') =~ /^([\w\-\.]+)$/; + my($id)=$1; + #push @svc_domain, qsearchs('svc_domain',{'domain'=>$domain}); + @svc_external = qsearchs('svc_external',{'id'=>$id}); +} + +if ( scalar(@svc_external) == 1 ) { + print $cgi->redirect(popurl(2). "view/svc_external.cgi?". $svc_external[0]->svcnum); + #exit; +} elsif ( scalar(@svc_external) == 0 ) { +%> +<!-- mason kludge --> +<% + eidiot "No matching external services found!\n"; +} else { +%> +<!-- mason kludge --> +<%= header("External Search Results",'') %> + + <%= scalar(@svc_external) %> matching external services found + <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> + <TR> + <TH>Service #</TH> + <TH><%= FS::Msgcat::_gettext('svc_external-id') || 'External ID' %></TH> + <TH><%= FS::Msgcat::_gettext('svc_external-title') || 'Title' %></TH> + </TR> + +<% + foreach my $svc_external ( + sort $sortby (@svc_external) + ) { + my($svcnum, $id, $title)=( + $svc_external->svcnum, + $svc_external->id, + $svc_external->title, + ); + + my $rowspan = 1; + + print <<END; + <TR> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$svcnum</A></TD> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$id</A></TD> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$title</A></TD> +END + + #print @rows; + print "</TR>"; + + } + + print <<END; + </TABLE> + </BODY> +</HTML> +END + +} + +sub svcnum_sort { + $a->getfield('svcnum') <=> $b->getfield('svcnum'); +} + +sub id_sort { + $a->getfield('id') <=> $b->getfield('id'); +} + +%> diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi new file mode 100755 index 000000000..a204e345f --- /dev/null +++ b/httemplate/search/svc_forward.cgi @@ -0,0 +1,120 @@ +<% + +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 new file mode 100755 index 000000000..ae51c61fc --- /dev/null +++ b/httemplate/search/svc_www.cgi @@ -0,0 +1,69 @@ +<% + +#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() + ), + ], + ) +%> |