diff options
Diffstat (limited to 'httemplate/search')
32 files changed, 3669 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..3ae624af2 --- /dev/null +++ b/httemplate/search/cust_bill.html @@ -0,0 +1,101 @@ +<% + my( $count_query, $sql_query ); + if ( $cgi->param('begin') || $cgi->param('end') || $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; + my $orderby = 'ORDER BY cust_bill._date'; + + if ( $cgi->param('begin') =~ /^(\d+)$/ ) { + push @where, "cust_bill._date >= $1", + } + if ( $cgi->param('end') =~ /^(\d+)$/ ) { + push @where, "cust_bill._date < $1", + } + + my($query) = $cgi->keywords; + 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; + } + + my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; + + $count_query = "SELECT COUNT(*), sum(charged), sum($owed) + FROM cust_bill $extra_sql"; + + $sql_query = { + 'table' => 'cust_bill', + 'hashref' => {}, + 'select' => "cust_bill.*, $owed as owed", + 'extra_sql' => "$extra_sql $orderby" + }; + + } else { + $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' => '*', + }; + } + + my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; + my $clink = sub { + my $cust_bill = shift; + my $cust_main = $cust_bill->cust_main; + $cust_main + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; + }; + +%> +<%= include( 'elements/search.html', + 'title' => 'Invoice Search Results', + 'name' => 'invoices', + 'query' => $sql_query, + 'count_query' => $count_query, + 'count_addl' => [ '$%.2f total invoiced', + '$%.2f total outstanding balance', + ], + 'redirect' => $link, + 'header' => + [ 'Invoice #', qw(Balance Amount Date), 'Contact name', + 'Company' ], + 'fields' => [ + 'invnum', + sub { sprintf('$%.2f', shift->get('owed') ) }, + sub { sprintf('$%.2f', shift->charged ) }, + sub { time2str('%b %d %Y', shift->_date ) }, + sub { my $cust_bill = shift; + my $cust_main = $cust_bill->cust_main; + $cust_main + ? $cust_main->get('last'). ', '. $cust_main->first + : "WARNING: can't find cust_main.custnum ". + $cust_bill->custnum. ' (cust_bill.invnum '. + $cust_bill->invnum. ')'; + }, + sub { my $cust_main = shift->cust_main; + $cust_main ? $cust_main->company : ''; + }, + ], + 'links' => [ + $link, + $link, + $link, + $link, + $clink, + $clink, + ], + + ) +%> diff --git a/httemplate/search/cust_bill_event.cgi b/httemplate/search/cust_bill_event.cgi new file mode 100644 index 000000000..7c2b3a24c --- /dev/null +++ b/httemplate/search/cust_bill_event.cgi @@ -0,0 +1,62 @@ +<!-- mason kludge --> +<% + +#false laziness with view/cust_bill.cgi + +$cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/; +my $beginning = str2time($1) || 0; + +$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/; +my $ending = ( $1 ? str2time($1) : 4294880896 ) + 86399; + +my @cust_bill_event = + sort { $a->_date <=> $b->_date } + qsearch('cust_bill_event', { + _date => { op=> '>=', value=>$beginning }, + statustext => { op=> '!=', value=>'' }, +# i wish... +# _date => { op=> '<=', value=>$ending }, + }, '', "AND _date <= $ending"); + +%> + +<%= header('Failed billing events') %> + +<%= table() %> +<TR> + <TH>Event</TH> + <TH>Date</TH> + <TH>Status</TH> + <TH>Invoice</TH> + <TH>(bill) name</TH> + <TH>company</TH> +<% if ( defined dbdef->table('cust_main')->column('ship_last') ) { %> + <TH>(service) name</TH> + <TH>company</TH> +<% } %> +</TR> + +<% foreach my $cust_bill_event ( @cust_bill_event ) { + my $status = $cust_bill_event->status; + $status .= ': '.$cust_bill_event->statustext if $cust_bill_event->statustext; + my $cust_bill = $cust_bill_event->cust_bill; + my $cust_main = $cust_bill->cust_main; + my $invlink = "${p}view/cust_bill.cgi?". $cust_bill->invnum; + my $custlink = "${p}view/cust_main.cgi?". $cust_main->custnum; +%> +<TR> + <TD><%= $cust_bill_event->part_bill_event->event %></TD> + <TD><%= time2str("%a %b %e %T %Y", $cust_bill_event->_date) %></TD> + <TD><%= $status %></TD> + <TD><A HREF="<%=$invlink%>">Invoice #<%= $cust_bill->invnum %> (<%= time2str("%D", $cust_bill->_date ) %>)</A></TD> + <TD><A HREF="<%=$custlink%>"><%= $cust_main->last. ', '. $cust_main->first %></A></TD> + <TD><A HREF="<%=$custlink%>"><%= $cust_main->company %></A></TD> + <% if ( defined dbdef->table('cust_main')->column('ship_last') ) { %> + <TD><A HREF="<%=$custlink%>"><%= $cust_main->ship_last. ', '. $cust_main->ship_first %></A></TD> + <TD><A HREF="<%=$custlink%>"><%= $cust_main->ship_company %></A></TD> + <% } %> +</TR> +<% } %> +</TABLE> + +</BODY></HTML> diff --git a/httemplate/search/cust_bill_event.html b/httemplate/search/cust_bill_event.html new file mode 100755 index 000000000..cd96ddf51 --- /dev/null +++ b/httemplate/search/cust_bill_event.html @@ -0,0 +1,54 @@ +<HTML> + <HEAD> + <TITLE>Invoice event errors</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Invoice event errors</H1> + <FORM ACTION="cust_bill_event.cgi" METHOD="post"> + <TABLE> + <!--<TR> + <TD ALIGN="right">Customer type</TD> + <TD><SELECT MULTIPLE NAME="perhaps_payby"> + <OPTION SELECTED VALUE="CARD">Credit card (automatic) + <OPTION SELECTED VALUE="CHEK">E-check (automatic) + <OPTION SELECTED VALUE="LECB">Phone bill billing + <OPTION SELECTED VALUE="BILL">Billing + <OPTION SELECTED VALUE="DCRD">Credit card (on-demand) + <OPTION SELECTED VALUE="DCHK">E-check (on-demand) + </TD> + </TR> + --> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> + </TABLE> + <BR><INPUT TYPE="submit" VALUE="Get Report"> + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html new file mode 100755 index 000000000..faaa7a817 --- /dev/null +++ b/httemplate/search/cust_credit.html @@ -0,0 +1,80 @@ +<% + #my( $count_query, $sql_query ); + + my @search = (); + + if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { + push @search, "otaker = '$1'"; + } + + #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 $where"; + my $sql_query = { + 'table' => 'cust_credit', + 'hashref' => {}, + 'extra_sql' => $where, + }; + + my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +%> +<%= include( 'elements/search.html', + 'title' => 'Credit Search Results', + 'name' => 'credits', + 'query' => $sql_query, + 'count_query' => $count_query, + 'count_addl' => [ '$%.2f total credited', ], + #'redirect' => $link, + 'header' => + [ qw(Amount Date), 'Cust #', 'Contact name', + qw(Company By Reason) ], + 'fields' => [ + #'crednum', + sub { sprintf('$%.2f', shift->amount ) }, + sub { time2str('%b %d %Y', shift->_date ) }, + 'custnum', + sub { my $cust_main = shift->cust_main; + $cust_main->get('last'). ', '. $cust_main->first; + }, + sub { my $cust_main = shift->cust_main; + $cust_main->company; + }, + 'otaker', + 'reason', + ], + 'align' => 'rrrllll', + 'links' => [ + '', + '', + $clink, + $clink, + $clink, + '', + '', + ], + ) +%> diff --git a/httemplate/search/cust_main-otaker.cgi b/httemplate/search/cust_main-otaker.cgi new file mode 100755 index 000000000..44214368a --- /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="post"> + 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..671b5ef08 --- /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="post"> + 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..077d290d9 --- /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="post"> + <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..27f23de36 --- /dev/null +++ b/httemplate/search/cust_main.cgi @@ -0,0 +1,608 @@ +<% + +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); +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 )"; + } 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"; + } + } + + @cust_main = qsearch('cust_main', \%search, '', + "$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=POST>'. + 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>'; + } + + 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 +} + +print <<END; + <TH>Packages</TH> + <TH COLSPAN=2>Services</TH> + </TR> +END + + my(%saw,$cust_main); + my $p = popurl(2); + 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 + } + + 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 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..5a066e453 --- /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="post"> + <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..3f5b72ab5 --- /dev/null +++ b/httemplate/search/cust_pay.cgi @@ -0,0 +1,137 @@ +<% + my( $count_query, $sql_query ); + if ( $cgi->param('magic') && $cgi->param('magic') eq '_date' ) { + + my %search; + my @search; + + if ( $cgi->param('payby') ) { + $cgi->param('payby') =~ /^(CARD|CHEK|BILL)(-(VisaMC|Amex|Discover))?$/ + or die "illegal payby ". $cgi->param('payby'); + $search{'payby'} = $1; + if ( $3 ) { + if ( $3 eq 'VisaMC' ) { + #avoid posix regexes for portability + push @search, " ( substring(payinfo from 1 for 1) = '4' ". + " OR substring(payinfo from 1 for 2) = '51' ". + " OR substring(payinfo from 1 for 2) = '52' ". + " OR substring(payinfo from 1 for 2) = '53' ". + " OR substring(payinfo from 1 for 2) = '54' ". + " OR substring(payinfo from 1 for 2) = '54' ". + " OR substring(payinfo from 1 for 2) = '55' ". + " ) "; + } elsif ( $3 eq 'Amex' ) { + push @search, " ( substring(payinfo from 1 for 2 ) = '34' ". + " OR substring(payinfo from 1 for 2 ) = '37' ". + " ) "; + } elsif ( $3 eq 'Discover' ) { + push @search, " substring(payinfo from 1 for 4 ) = '6011' "; + } else { + die "unknown card type $3"; + } + } + } + + #false laziness with cust_pkg.cgi + if ( $cgi->param('beginning') + && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { + my $beginning = str2time($1); + push @search, "_date >= $beginning "; + } + if ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { + my $ending = str2time($1) + 86399; + push @search, " _date <= $ending "; + } + if ( $cgi->param('begin') + && $cgi->param('begin') =~ /^(\d+)$/ ) { + push @search, "_date >= $1 "; + } + if ( $cgi->param('end') + && $cgi->param('end') =~ /^(\d+)$/ ) { + push @search, " _date < $1 "; + } + + my $search; + if ( @search ) { + $search = ( scalar(keys %search) ? ' AND ' : ' WHERE ' ). + join(' AND ', @search); + } + + my $hsearch = join(' AND ', map { "$_ = '$search{$_}'" } keys %search ); + $count_query = "SELECT COUNT(*), SUM(paid) FROM cust_pay ". + ( $hsearch ? " WHERE $hsearch " : '' ). + $search; + + $sql_query = { + 'table' => 'cust_pay', + 'hashref' => \%search, + 'extra_sql' => "$search ORDER BY _date", + }; + + } else { + + $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo"; + my $payinfo = $1; + + $cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby"; + my $payby = $1; + + $count_query = "SELECT COUNT(*), SUM(paid) FROM cust_pay ". + "WHERE payinfo = '$payinfo' AND payby = '$payby'"; + + $sql_query = { + 'table' => 'cust_pay', + 'hashref' => { 'payinfo' => $payinfo, + 'payby' => $payby }, + 'extra_sql' => "ORDER BY _date", + }; + + } + + my $link = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +%> +<%= include( 'elements/search.html', + 'title' => 'Payment Search Results', + 'name' => 'payments', + 'query' => $sql_query, + 'count_query' => $count_query, + 'count_addl' => [ '$%.2f total paid', ], + 'header' => + [ qw(Payment Amount Date), 'Cust #', 'Contact name', + 'Company', ], + 'fields' => [ + sub { + my $cust_pay = shift; + if ( $cust_pay->payby eq 'CARD' ) { + 'Card #'. $cust_pay->payinfo_masked; + } elsif ( $cust_pay->payby eq 'CHEK' ) { + 'E-check acct#'. $cust_pay->payinfo; + } elsif ( $cust_pay->payby eq 'BILL' ) { + 'Check #'. $cust_pay->payinfo; + } else { + $cust_pay->payby. ' '. $cust_pay->payinfo; + } + }, + sub { sprintf('$%.2f', shift->paid ) }, + sub { time2str('%b %d %Y', shift->_date ) }, + 'custnum', + sub { my $cust_main = shift->cust_main; + $cust_main->get('last'). ', '. $cust_main->first; + }, + sub { my $cust_main = shift->cust_main; + $cust_main->company; + }, + ], + 'align' => 'lrrrll', + 'links' => [ + '', + '', + '', + $link, + $link, + $link, + ], + ) +%> diff --git a/httemplate/search/cust_pay.html b/httemplate/search/cust_pay.html new file mode 100755 index 000000000..3848d66f7 --- /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="post"> + Search for <B>check #</B>: + <INPUT TYPE="text" NAME="payinfo"> + <INPUT TYPE="hidden" NAME="payby" VALUE="BILL"> + <BR><BR><INPUT TYPE="submit" VALUE="Search"> + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi new file mode 100755 index 000000000..6d26317e0 --- /dev/null +++ b/httemplate/search/cust_pkg.cgi @@ -0,0 +1,363 @@ +<% + +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); + +my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); + +my $limit = ''; +$limit .= "LIMIT $maxrecords" if $maxrecords; + +my $offset = $cgi->param('offset') || 0; +$limit .= " OFFSET $offset" if $offset; + +my $total; + +my($query) = $cgi->keywords; +my $sortby; +my @cust_pkg; + +if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { + $sortby=\*bill_sort; + + #false laziness with cust_pay.cgi + my $range = ''; + if ( $cgi->param('beginning') + && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { + my $beginning = str2time($1); + $range = " WHERE bill >= $beginning "; + } + if ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { + my $ending = str2time($1) + 86399; + $range .= ( $range ? ' AND ' : ' WHERE ' ). " bill <= $ending "; + } + + $range .= ( $range ? 'AND ' : ' WHERE ' ). '( cancel IS NULL OR cancel = 0 )'; + + if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { + $range .= ( $range ? 'AND ' : ' WHERE ' ). + "$1 = ( SELECT agentnum FROM cust_main". + " WHERE cust_main.custnum = cust_pkg.custnum )"; + } + + #false laziness with below + my $statement = "SELECT COUNT(*) FROM cust_pkg $range"; + warn $statement; + my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; + $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; + + $total = $sth->fetchrow_arrayref->[0]; + + @cust_pkg = qsearch('cust_pkg',{}, '', " $range ORDER BY bill $limit" ); + +} else { + + my $qual = ''; + if ( $cgi->param('magic') && + $cgi->param('magic') =~ /^(active|suspended|canceled)$/ + ) { + + if ( $cgi->param('magic') eq 'active' ) { + $qual = 'WHERE ( susp IS NULL OR susp = 0 )'. + ' AND ( cancel IS NULL OR cancel = 0)'; + } elsif ( $cgi->param('magic') eq 'suspended' ) { + $qual = 'WHERE susp IS NOT NULL AND susp != 0'. + ' AND ( cancel IS NULL OR cancel = 0)'; + } elsif ( $cgi->param('magic') eq 'canceled' ) { + $qual = 'WHERE cancel IS NOT NULL AND cancel != 0'; + } else { + die "guru meditation #420"; + } + + $sortby = \*pkgnum_sort; + + if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { + $qual .= " AND pkgpart = $1"; + } + + } elsif ( $query eq 'pkgnum' ) { + + $sortby=\*pkgnum_sort; + + } elsif ( $query eq 'APKG_pkgnum' ) { + + $sortby=\*pkgnum_sort; + + #@cust_pkg=(); + ##perhaps this should go in cust_pkg as a qsearch-like constructor? + #my($cust_pkg); + #foreach $cust_pkg ( + # qsearch('cust_pkg',{}, '', "ORDER BY pkgnum $limit" ) + #) { + # my($flag)=0; + # my($pkg_svc); + # PKG_SVC: + # foreach $pkg_svc (qsearch('pkg_svc',{ 'pkgpart' => $cust_pkg->pkgpart })) { + # if ( $pkg_svc->quantity + # > scalar(qsearch('cust_svc',{ + # 'pkgnum' => $cust_pkg->pkgnum, + # 'svcpart' => $pkg_svc->svcpart, + # })) + # ) + # { + # $flag=1; + # last PKG_SVC; + # } + # } + # push @cust_pkg, $cust_pkg if $flag; + #} + + if ( driver_name eq 'mysql' ) { + #$query = "DROP TABLE temp1_$$,temp2_$$;"; + #my $sth = dbh->prepare($query); + #$sth->execute; + + $query = "CREATE TEMPORARY TABLE temp1_$$ TYPE=MYISAM + SELECT cust_svc.pkgnum,cust_svc.svcpart,COUNT(*) as count + FROM cust_pkg,cust_svc,pkg_svc + WHERE cust_pkg.pkgnum = cust_svc.pkgnum + AND cust_svc.svcpart = pkg_svc.svcpart + AND cust_pkg.pkgpart = pkg_svc.pkgpart + GROUP BY cust_svc.pkgnum,cust_svc.svcpart"; + my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + + $query = "CREATE TEMPORARY TABLE temp2_$$ TYPE=MYISAM + SELECT cust_pkg.pkgnum FROM cust_pkg + LEFT JOIN pkg_svc ON (cust_pkg.pkgpart=pkg_svc.pkgpart) + LEFT JOIN temp1_$$ ON (cust_pkg.pkgnum = temp1_$$.pkgnum + AND pkg_svc.svcpart=temp1_$$.svcpart) + WHERE ( pkg_svc.quantity > temp1_$$.count + OR temp1_$$.pkgnum IS NULL ) + AND pkg_svc.quantity != 0;"; + $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + $qual = " LEFT JOIN temp2_$$ ON cust_pkg.pkgnum = temp2_$$.pkgnum + WHERE temp2_$$.pkgnum IS NOT NULL"; + + } else { + + $qual = " + WHERE 0 < + ( SELECT count(*) FROM pkg_svc + WHERE pkg_svc.pkgpart = cust_pkg.pkgpart + AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc + WHERE cust_svc.pkgnum = cust_pkg.pkgnum + AND cust_svc.svcpart = pkg_svc.svcpart + ) + ) + "; + + } + + } else { + die "Empty or unknown QUERY_STRING!"; + } + + my $statement = "SELECT COUNT(*) FROM cust_pkg $qual"; + my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; + $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; + + $total = $sth->fetchrow_arrayref->[0]; + + my $tblname = driver_name eq 'mysql' ? 'cust_pkg.' : ''; + @cust_pkg = + qsearch('cust_pkg',{}, '', "$qual ORDER BY ${tblname}pkgnum $limit" ); + + if ( driver_name eq 'mysql' ) { + $query = "DROP TABLE temp1_$$,temp2_$$;"; + my $sth = dbh->prepare($query) or die dbh->errstr. " doing $query"; + $sth->execute; # or die "Error executing \"$query\": ". $sth->errstr; + } + +} + +if ( scalar(@cust_pkg) == 1 ) { + print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum. + "#cust_pkg". $cust_pkg[0]->pkgnum ); + #exit; +} elsif ( scalar(@cust_pkg) == 0 ) { #error +%> +<!-- mason kludge --> +<% + eidiot("No packages found"); +} else { +%> +<!-- mason kludge --> +<% + $total ||= scalar(@cust_pkg); + + #begin pager + my $pager = ''; + if ( $total != scalar(@cust_pkg) && $maxrecords ) { + unless ( $offset == 0 ) { + $cgi->param('offset', $offset - $maxrecords); + $pager .= '<A HREF="'. $cgi->self_url. + '"><B><FONT SIZE="+1">Previous</FONT></B></A> '; + } + my $poff; + my $page; + for ( $poff = 0; $poff < $total; $poff += $maxrecords ) { + $page++; + if ( $offset == $poff ) { + $pager .= qq!<FONT SIZE="+2">$page</FONT> !; + } else { + $cgi->param('offset', $poff); + $pager .= qq!<A HREF="!. $cgi->self_url. qq!">$page</A> !; + } + } + unless ( $offset + $maxrecords > $total ) { + $cgi->param('offset', $offset + $maxrecords); + $pager .= '<A HREF="'. $cgi->self_url. + '"><B><FONT SIZE="+1">Next</FONT></B></A> '; + } + } + #end pager + + print header('Package Search Results',''), + "$total matching packages found<BR><BR>$pager", &table(), <<END; + <TR> + <TH>Package</TH> + <TH><FONT SIZE=-1>Setup</FONT></TH> +END + + print '<TH><FONT SIZE=-1>Last<BR>bill</FONT></TH>' + if defined dbdef->table('cust_pkg')->column('last_bill'); + + print <<END; + <TH><FONT SIZE=-1>Next<BR>bill</FONT></TH> + <TH><FONT SIZE=-1>Susp.</FONT></TH> + <TH><FONT SIZE=-1>Expire</FONT></TH> + <TH><FONT SIZE=-1>Cancel</FONT></TH> + <TH><FONT SIZE=-1>Cust#</FONT></TH> + <TH>(bill) name</TH> + <TH>company</TH> +END + + print '<TH>(service) name</TH><TH>company</TH>' + if defined dbdef->table('cust_main')->column('ship_last'); + + print '<TH COLSPAN=2>Services</TH></TR>'; + + my $n1 = '<TR>'; + my(%saw,$cust_pkg); + foreach $cust_pkg ( + sort $sortby grep(!$saw{$_->pkgnum}++, @cust_pkg) + ) { + my($cust_main)=qsearchs('cust_main',{'custnum'=>$cust_pkg->custnum}); + my($pkgnum, $setup, $bill, $susp, $expire, $cancel, + $custnum, $last, $first, $company ) = ( + $cust_pkg->pkgnum, + $cust_pkg->getfield('setup') + ? time2str("%D", $cust_pkg->getfield('setup') ) + : '', + $cust_pkg->getfield('bill') + ? time2str("%D", $cust_pkg->getfield('bill') ) + : '', + $cust_pkg->getfield('susp') + ? time2str("%D", $cust_pkg->getfield('susp') ) + : '', + $cust_pkg->getfield('expire') + ? time2str("%D", $cust_pkg->getfield('expire') ) + : '', + $cust_pkg->getfield('cancel') + ? time2str("%D", $cust_pkg->getfield('cancel') ) + : '', + $cust_pkg->custnum, + $cust_main ? $cust_main->last : '', + $cust_main ? $cust_main->first : '', + $cust_main ? $cust_main->company : '', + ); + + my $last_bill = $cust_pkg->getfield('last_bill') + ? time2str("%D", $cust_pkg->getfield('last_bill') ) + : '' + if defined dbdef->table('cust_pkg')->column('last_bill'); + + my($ship_last, $ship_first, $ship_company); + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + ($ship_last, $ship_first, $ship_company) = ( + $cust_main + ? ( $cust_main->ship_last || $cust_main->getfield('last') ) + : '', + $cust_main + ? ( $cust_main->ship_last + ? $cust_main->ship_first + : $cust_main->first ) + : '', + $cust_main + ? ( $cust_main->ship_last + ? $cust_main->ship_company + : $cust_main->company ) + : '', + ); + } + my $pkg = $part_pkg{$cust_pkg->pkgpart}->pkg; + #$pkg .= ' - '. $part_pkg{$cust_pkg->pkgpart}->comment; + my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } ); + my $rowspan = scalar(@cust_svc) || 1; + my $p = popurl(2); + print $n1, <<END; + <TD ROWSPAN=$rowspan><A HREF="${p}view/cust_main.cgi?$custnum#cust_pkg$pkgnum"><FONT SIZE=-1>$pkgnum - $pkg</FONT></A></TD> + <TD ROWSPAN=$rowspan>$setup</TD> +END + + print "<TD ROWSPAN=$rowspan>$last_bill</TD>" + if defined dbdef->table('cust_pkg')->column('last_bill'); + + print <<END; + <TD ROWSPAN=$rowspan>$bill</TD> + <TD ROWSPAN=$rowspan>$susp</TD> + <TD ROWSPAN=$rowspan>$expire</TD> + <TD ROWSPAN=$rowspan>$cancel</TD> +END + if ( $cust_main ) { + print <<END; + <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$custnum</A></FONT></TD> + <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$last, $first</A></FONT></TD> + <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$company</A></FONT></TD> +END + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <<END; + <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$ship_last, $ship_first</A></FONT></TD> + <TD ROWSPAN=$rowspan><FONT SIZE=-1><A HREF="${p}view/cust_main.cgi?$custnum">$ship_company</A></FONT></TD> +END + } + } else { + my $colspan = defined dbdef->table('cust_main')->column('ship_last') + ? 5 : 3; + print <<END; + <TD ROWSPAN=$rowspan COLSPAN=$colspan>WARNING: couldn't find cust_main.custnum $custnum (cust_pkg.pkgnum $pkgnum)</TD> +END + } + + my $n2 = ''; + foreach my $cust_svc ( @cust_svc ) { + my($label, $value, $svcdb) = $cust_svc->label; + my $svcnum = $cust_svc->svcnum; + my $sview = $p. "view"; + print $n2,qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!, + qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!; + $n2="</TR><TR>"; + } + + $n1 = "</TR><TR>"; + + } + print '</TR>'; + + print "</TABLE>$pager</BODY></HTML>"; + +} + +sub pkgnum_sort { + $a->getfield('pkgnum') <=> $b->getfield('pkgnum'); +} + +sub bill_sort { + $a->getfield('bill') <=> $b->getfield('bill'); +} + +%> diff --git a/httemplate/search/cust_pkg_report.cgi b/httemplate/search/cust_pkg_report.cgi new file mode 100755 index 000000000..b31674540 --- /dev/null +++ b/httemplate/search/cust_pkg_report.cgi @@ -0,0 +1,63 @@ +<HTML> + <HEAD> + <TITLE>Packages</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Packages</H1> + <FORM ACTION="cust_pkg.cgi" METHOD="post"> + <INPUT TYPE="hidden" NAME="magic" VALUE="bill"> + Return packages with next bill date:<BR><BR> + <TABLE> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><I>m/d/y</I></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><I>m/d/y</I></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> +<% my %agent_search = dbdef->table('agent')->column('disabled') + ? ( 'disabled' => '' ) : (); + my @agents = qsearch( 'agent', \%agent_search ); + if ( scalar(@agents) == 1 ) { +%> + <INPUT TYPE="hidden" NAME="agentnum" VALUE="<%= $agents[0]->agentnum %>"> +<% } else { %> + + <TR> + <TD ALIGN="right">Agent: </TD> + <TD><SELECT NAME="agentnum"><OPTION VALUE="">(all) + <% foreach my $agent ( sort { $a->agent cmp $b->agent; } @agents) { %> + <OPTION VALUE="<%= $agent->agentnum %>"><%= $agent->agent %> + <% } %> + </TD> + </TR> +<% } %> + </TABLE> + <BR><INPUT TYPE="submit" VALUE="Get Report"> + + </FORM> + + </BODY> +</HTML> + diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html new file mode 100644 index 000000000..566ea8391 --- /dev/null +++ b/httemplate/search/elements/search.html @@ -0,0 +1,139 @@ +<% + + my(%opt) = @_; + + my %align = ( + 'l' => 'left', + 'r' => 'right', + 'c' => 'center', + ' ' => '', + '.' => '', + ); + $opt{align} = [ map $align{$_}, split(//, $opt{align}) ], + unless !$opt{align} || ref($opt{align}); + + if ( ref($opt{'query'}) ) { + + } + + 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; + my $maxrecords = $conf->config('maxsearchrecordsperpage'); + + my $limit = $maxrecords ? "LIMIT $maxrecords" : ''; + + my $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; + my $count_arrayref = $count_sth->fetchrow_arrayref; + my $total = $count_arrayref->[0]; + + #warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n"; + + 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", + ) ]; + } 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 ( exists($opt{'redirect'}) && scalar(@$rows) == 1 && $total == 1 ) { + my( $url, $method ) = @{$opt{'redirect'}}; + redirect( $url. $rows->[0]->$method() ); + } else { + $opt{'name'} =~ s/s$// if $total == 1; +%> +<%= include( '/elements/header.html', $opt{'title'}, + include( '/elements/menubar.html', 'Main menu' => $p ) + ) +%> +<% 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 { %> + <%= $total %> total <%= $opt{'name'} %><BR> + <% if ( $opt{'count_addl'} ) { %> + <% my $n=0; foreach my $count ( @{$opt{'count_addl'}} ) { %> + <%= sprintf( $count, $count_arrayref->[++$n] ) %><BR> + <% } %> + <% } %> + <BR><%= $pager %> + <%= include( '/elements/table.html' ) %> + <TR> + <% foreach my $header ( @$header ) { %> + <TH><%= $header %></TH> + <% } %> + </TR> + <% foreach my $row ( @$rows ) { %> + <TR> + <% 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' ) { %> + <TD<%= $align %>><%= $a %><%= &{$field}($row) %><%= $a ? '</A>' : '' %></TD> + <% } else { %> + <TD<%= $align %>><%= $a %><%= $row->$field() %><%= $a ? '</A>' : '' %></TD> + <% } %> + <% } %> + <% } else { %> + <% foreach ( @$row ) { %> + <TD><%= $_ %></TD> + <% } %> + <% } %> + </TR> + <% } %> + + </TABLE> + <%= $pager %> +<% } %> +</BODY> +</HTML> +<% } %> + diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html new file mode 100644 index 000000000..ceffca75d --- /dev/null +++ b/httemplate/search/report_cust_credit.html @@ -0,0 +1,58 @@ +<HTML> + <HEAD> + <TITLE>Credit report criteria</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Credit report criteria</H1> + <FORM ACTION="cust_credit.html" METHOD="post"> + <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> + <TABLE> + <TR> + <TD ALIGN="right">Credits by employee: </TD> +<% + my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_credit") + or die dbh->errstr; + $sth->execute or die $sth->errstr; + my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; +%> + <TD><SELECT NAME="otaker"> + <OPTION VALUE="">all</OPTION> + <% foreach my $otaker ( @otakers ) { %> + <OPTION VALUE="<%= $otaker %>"><%= $otaker %></OPTION> + <% } %> + </SELECT> + </TD> + </TR> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> + </TABLE> + <BR><INPUT TYPE="submit" VALUE="Get Report"> + </FORM> + </BODY> +</HTML> diff --git a/httemplate/search/report_cust_pay.html b/httemplate/search/report_cust_pay.html new file mode 100644 index 000000000..95198c7b2 --- /dev/null +++ b/httemplate/search/report_cust_pay.html @@ -0,0 +1,55 @@ +<HTML> + <HEAD> + <TITLE>Payment report criteria</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Payment report criteria</H1> + <FORM ACTION="cust_pay.cgi" METHOD="post"> + <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> + <TABLE> + <TR> + <TD ALIGN="right">Payments of type: </TD> + <TD><SELECT NAME="payby"> + <OPTION VALUE="">all</OPTION> + <OPTION VALUE="CARD">credit card (all)</OPTION> + <OPTION VALUE="CARD-VisaMC">credit card (Visa/MasterCard)</OPTION> + <OPTION VALUE="CARD-Amex">credit card (American Express)</OPTION> + <OPTION VALUE="CARD-Discover">credit card (Discover)</OPTION> + <OPTION VALUE="CHEK">electronic check / ACH</OPTION> + <OPTION VALUE="BILL">check / cash</OPTION> + </SELECT> + </TD> + </TR> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> + </TABLE> + <BR><INPUT TYPE="submit" VALUE="Get Report"> + </FORM> + </BODY> +</HTML> diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/report_prepaid_income.cgi new file mode 100644 index 000000000..1677591a3 --- /dev/null +++ b/httemplate/search/report_prepaid_income.cgi @@ -0,0 +1,86 @@ +<!-- mason kludge --> +<% + + #doesn't yet deal with daily/weekly packages + + #needs to be re-written in sql for efficiency + + my $time = time; + + my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time; + $now =~ /^(\d+)$/ or die "unparsable date?"; + $now = $1; + + my( $total, $total_legacy ) = ( 0, 0 ); + + my @cust_bill_pkg = + grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[dw])$/ } + qsearch( 'cust_bill_pkg', { + 'recur' => { op=>'!=', value=>0 }, + 'edate' => { op=>'>', value=>$now }, + }, ); + + my @cust_pkg = + grep { $_->part_pkg->recur != 0 + && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/ + } + qsearch ( 'cust_pkg', { + 'bill' => { op=>'>', value=>$now } + } ); + + foreach my $cust_bill_pkg ( @cust_bill_pkg) { + my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; + + my $elapsed = $now - $cust_bill_pkg->sdate; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + my $unearned = $remaining * $cust_bill_pkg->recur; + $total += $unearned; + + } + + foreach my $cust_pkg ( @cust_pkg ) { + my $period = $cust_pkg->bill - $cust_pkg->last_bill; + + my $elapsed = $now - $cust_pkg->last_bill; + $elapsed = 0 if $elapsed < 0; + + my $remaining = 1 - $elapsed/$period; + + my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy + $total_legacy += $unearned; + + } + + $total = sprintf('%.2f', $total); + $total_legacy = sprintf('%.2f', $total_legacy); + +%> + +<%= header( 'Prepaid Income (Unearned Revenue) Report', + menubar( 'Main Menu'=>$p, ) ) %> +<%= table() %> + <TR> + <TH>Actual Unearned Revenue</TH> + <TH>Legacy Unearned Revenue</TH> + </TR> + <TR> + <TD ALIGN="right">$<%= $total %> + <TD ALIGN="right"> + <%= $now == $time ? "\$$total_legacy" : '<i>N/A</i>'%> + </TD> + </TR> + +</TABLE> +<BR> +Actual unearned revenue is the amount of unearned revenue Freeside has +actually invoiced for packages with longer-than monthly terms. +<BR><BR> +Legacy unearned revenue is the amount of unearned revenue represented by +customer packages. This number may be larger than actual unearned +revenue if you have imported longer-than monthly customer packages from +a previous billing system. +</BODY> +</HTML> diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html new file mode 100644 index 000000000..e8b6ac4b1 --- /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="post"> + <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..0e95ad73c --- /dev/null +++ b/httemplate/search/report_receivables.cgi @@ -0,0 +1,158 @@ +<!-- mason kludge --> +<% + + 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_plus, + + 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 $sql = <<END; + +select *, $owed_cols, $packages_cols from cust_main +where 0 < + coalesce( + ( select $charged from cust_bill + where cust_main.custnum = cust_bill.custnum + ) + ,0 + ) + +order by coalesce(lower(company), ''), lower(last) + +END + + my $total_sql = "select $owed_cols"; + + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute or die $sth->errstr; + + my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; + $total_sth->execute or die $total_sth->errstr; + +%> +<%= header('Accounts Receivable Aging Summary', menubar( 'Main Menu'=>$p, ) ) %> +<%= table() %> + <TR> + <TH>Customer</TH> + <TH>Status</TH> + <TH>0-30</TH> + <TH>30-60</TH> + <TH>60-90</TH> + <TH>90+</TH> + <TH>Total</TH> + </TR> +<% while ( my $row = $sth->fetchrow_hashref() ) { + my $status = 'Cancelled'; + my $statuscol = 'FF0000'; + if ( $row->{uncancelled_pkgs} ) { + $status = 'Suspended'; + $statuscol = 'FF9900'; + if ( $row->{active_pkgs} ) { + $status = 'Active'; + $statuscol = '00CC00'; + } + } +%> + <TR> + <TD><A HREF="<%= $p %>view/cust_main.cgi?<%= $row->{'custnum'} %>"><%= $row->{'custnum'} %>: + <%= $row->{'company'} ? $row->{'company'}. ' (' : '' %><%= $row->{'last'}. ', '. $row->{'first'} %><%= $row->{'company'} ? ')' : '' %></A> + </TD> + <TD><B><FONT SIZE=-1 COLOR="#<%= $statuscol %>"><%= $status %></FONT></B></TD> + <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD> + <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD> + <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD> + <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD> + <TD ALIGN="right"><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></TD> + </TR> +<% } %> +<% my $row = $total_sth->fetchrow_hashref(); %> + <TR> + <TD COLSPAN=6> </TD> + </TR> + <TR> + <TD COLSPAN=2><I>Total</I></TD> + <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD> + <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD> + <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD> + <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD> + <TD ALIGN="right"><I><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></I></TD> + </TR> +</TABLE> +</BODY> +</HTML> diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi new file mode 100755 index 000000000..587665740 --- /dev/null +++ b/httemplate/search/report_tax.cgi @@ -0,0 +1,253 @@ +<!-- mason kludge --> +<% + +my $user = getotaker; + +$cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/; +my $pbeginning = $1; +my $beginning = $1 ? str2time($1) : 0; + +$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/; +my $pending = $1; +my $ending = ( $1 ? str2time($1) : 4294880896 ) + 86399; + +my $from_join_cust = " + FROM cust_bill_pkg + JOIN cust_bill USING ( invnum ) + JOIN cust_main USING ( custnum ) +"; +my $join_pkg = " + JOIN cust_pkg USING ( pkgnum ) + JOIN part_pkg USING ( pkgpart ) +"; +my $where = " + WHERE _date >= $beginning AND _date <= $ending + AND ( county = ? OR ? = '' ) + AND ( state = ? OR ? = '' ) + AND ( country = ? ) + AND payby != 'COMP' +"; +my @base_param = qw( county county state state country ); + +my $gotcust = " + WHERE 0 < ( SELECT COUNT(*) FROM cust_main + WHERE ( cust_main.county = cust_main_county.county + OR cust_main_county.county = '' + OR cust_main_county.county IS NULL ) + AND ( cust_main.state = cust_main_county.state + OR cust_main_county.state = '' + OR cust_main_county.state IS NULL ) + AND ( cust_main.country = cust_main_county.country ) + LIMIT 1 + ) +"; + +my $monthly_exempt_warning = 0; +my($total, $exempt, $taxable, $owed, $tax) = ( 0, 0, 0, 0, 0 ); +my $out = 'Out of taxable region(s)'; +my %regions; +foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) { + #warn $r->county. ' '. $r->state. ' '. $r->country. "\n"; + + my $label = getlabel($r); + $regions{$label}->{'label'} = $label; + + my $fromwhere = $from_join_cust. $join_pkg. $where; + my @param = @base_param; + + if ( $r->taxclass ) { + $fromwhere .= " AND ( taxclass = ? ) "; + push @param, 'taxclass'; + } + + my $nottax = 'pkgnum != 0'; + + my $a = scalar_sql($r, \@param, + "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax" + ); + $total += $a; + $regions{$label}->{'total'} += $a; + + foreach my $e ( grep { $r->get($_.'tax') =~ /^Y/i } + qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { + my $x = scalar_sql($r, \@param, + "SELECT SUM($e) $fromwhere AND $nottax" + ); + $exempt += $x; + $regions{$label}->{'exempt'} += $x; + } + + my($t, $x) = (0, 0); + foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i } + qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { + $t += scalar_sql($r, \@param, + "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )" + ); + + $x += scalar_sql($r, \@param, + "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'" + ); + } + + my($sday,$smon,$syear) = (localtime($beginning) )[ 3, 4, 5 ]; + $monthly_exempt_warning=1 if $sday != 1 && $beginning; + $smon++; $syear+=1900; + + my $eending = ( $ending == 4294967295 ) ? time : $ending; + my($eday,$emon,$eyear) = (localtime($eending) )[ 3, 4, 5 ]; + $emon++; $eyear+=1900; + + my $monthly_exemption = scalar_sql($r, [ 'taxnum' ], + "SELECT SUM(amount) FROM cust_tax_exempt where taxnum = ? ". + " AND ( year > $syear OR ( year = $syear and month >= $smon ) )". + " AND ( year < $eyear OR ( year = $eyear and month <= $emon ) )" + ); + #warn $r->taxnum(). ": $monthly_exemption\n"; + if ( $monthly_exemption ) { + $t -= $monthly_exemption; + $x += $monthly_exemption; + } + + $taxable += $t; + $regions{$label}->{'taxable'} += $t; + + $exempt += $x; + $regions{$label}->{'exempt'} += $x; + + $owed += $t * ($r->tax/100); + $regions{$label}->{'owed'} += $t * ($r->tax/100); + + if ( defined($regions{$label}->{'rate'}) + && $regions{$label}->{'rate'} != $r->tax.'%' ) { + $regions{$label}->{'rate'} = 'variable'; + } else { + $regions{$label}->{'rate'} = $r->tax.'%'; + } + +} + +my $taxwhere = "$from_join_cust $where"; +my @taxparam = @base_param; + +#foreach my $label ( keys %regions ) { +foreach my $r ( + qsearch( 'cust_main_county', + {}, + 'DISTINCT ON (country, state, county, taxname) *', + $gotcust + ) +) { + + #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n"; + + my $label = getlabel($r); + + my $fromwhere = $join_pkg. $where; + my @param = @base_param; + + #match itemdesc if necessary! + my $named_tax = + $r->taxname + ? 'AND itemdesc = '. dbh->quote($r->taxname) + : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; + my $x = scalar_sql($r, \@taxparam, + "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $taxwhere ". + "AND pkgnum = 0 $named_tax", + ); + $tax += $x; + $regions{$label}->{'tax'} += $x; + +} + +#ordering +my @regions = map $regions{$_}, + sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } + keys %regions; + +push @regions, { + 'label' => 'Total', + 'total' => $total, + 'exempt' => $exempt, + 'taxable' => $taxable, + 'rate' => '', + 'owed' => $owed, + 'tax' => $tax, +}; + +#-- + +sub getlabel { + my $r = shift; + + my $label; + if ( $r->tax == 0 ) { + #kludge to avoid "will not stay shared" warning + my $out = 'Out of taxable region(s)'; + $label = $out; + } elsif ( $r->taxname ) { + $label = $r->taxname; +# $regions{$label}->{'taxname'} = $label; +# push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country ); + } else { + $label = $r->country; + $label = $r->state.", $label" if $r->state; + $label = $r->county." county, $label" if $r->county; + #$label = $r->taxname. " ($label)" if $r->taxname; + } + return $label; +} + +#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up +#to FS::Report or FS::Record or who the fuck knows where) +sub scalar_sql { + my( $r, $param, $sql ) = @_; + #warn "$sql\n"; + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute( map $r->$_(), @$param ) + or die "Unexpected error executing statement $sql: ". $sth->errstr; + $sth->fetchrow_arrayref->[0] || 0; +} + +%> + +<%= header( "Sales Tax Report - $pbeginning through ".($pending||'now'), + menubar( 'Main Menu'=>$p, ) ) %> +<%= table() %> + <TR> + <TH ROWSPAN=2></TH> + <TH COLSPAN=3>Sales</TH> + <TH ROWSPAN=2>Rate</TH> + <TH ROWSPAN=2>Tax owed</TH> + <TH ROWSPAN=2>Tax invoiced</TH> + </TR> + <TR> + <TH>Total</TH> + <TH>Non-taxable</TH> + <TH>Taxable</TH> + </TR> + <% foreach my $region ( @regions ) { %> + <TR> + <TD><%= $region->{'label'} %></TD> + <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'total'} ) %></TD> + <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'exempt'} ) %></TD> + <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'taxable'} ) %></TD> + <TD ALIGN="right"><%= $region->{'rate'} %></TD> + <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'owed'} ) %></TD> + <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'tax'} ) %></TD> + </TR> + <% } %> + +</TABLE> + +<% if ( $monthly_exempt_warning ) { %> + <BR> + Partial-month tax reports (except for current month) may not be correct due + to month-granularity tax exemption (usually "texas tax"). For an accurate + report, start on the first of a month and end on the last day of a month (or + leave blank for to now). +<% } %> + +</BODY> +</HTML> + + diff --git a/httemplate/search/report_tax.html b/httemplate/search/report_tax.html new file mode 100755 index 000000000..d217e5671 --- /dev/null +++ b/httemplate/search/report_tax.html @@ -0,0 +1,44 @@ +<HTML> + <HEAD> + <TITLE>Tax Report Criteria</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Tax Report Criteria</H1> + <FORM ACTION="report_tax.cgi" METHOD="post"> + Return <B>tax report</B> for period: + <TABLE> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><i>m/d/y</i></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> + </TABLE> + + <BR><INPUT TYPE="submit" VALUE="Get Report"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/sql.html b/httemplate/search/sql.html new file mode 100644 index 000000000..b28c045d1 --- /dev/null +++ b/httemplate/search/sql.html @@ -0,0 +1,7 @@ +<%= include( 'elements/search.html', + 'title' => 'Query Results', + 'name' => 'rows', + 'query' => 'SELECT '. ( $cgi->param('sql') + || eidiot('Empty query') ), + ) +%> diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi new file mode 100644 index 000000000..b506ba1cb --- /dev/null +++ b/httemplate/search/sqlradius.cgi @@ -0,0 +1,260 @@ +<%= 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; + } + + ### + # 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 ( map $_->rebless, + qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ), + qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } ) + ) { + %user2svc_acct = (); +%> + +<%= $part_export->exporttype %> to <%= $part_export->machine %><BR> +<%= include( '/elements/table.html' ) %> +<TR> + <% foreach my $field ( keys %fields ) { %> + <TH> + <%= $fields{$field}->{name} %><BR> + <FONT SIZE=-2><%= $fields{$field}->{attrib} %></FONT> + </TH> + <% } %> +</TR> +<% foreach my $session ( + @{ $part_export->usage_sessions( $beginning, $ending, $cgi_svc_acct, $ip ) } +) { %> + <TR> + <% foreach my $field ( keys %fields ) { %> + <TD ALIGN="<%= $fields{$field}->{align} %>"> + <%= &{ $fields{$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..48a3d8680 --- /dev/null +++ b/httemplate/search/sqlradius.html @@ -0,0 +1,70 @@ +<%= 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="POST"> +<% #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> +<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> +<TR> + <TD ALIGN="right">From: </TD> + <TD> + <INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"> + </TD> + <SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); + </SCRIPT> +</TR> +<TR> + <TD></TD> + <TD><i>m/d/y</i></TD> +</TR> +<TR> + <TD ALIGN="right">To: </TD> + <TD> + <INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor:pointer" TITLE="Select date"> + </TD> + <SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); + </SCRIPT> +</TR> +<TR> + <TD></TD> + <TD><i>m/d/y</i> + <BR><FONT SIZE="-1">(leave one or both dates blank for an open-ended search)</FONT> + </TD> +</TR> +</TABLE> +<BR><INPUT TYPE="submit" VALUE="View sessions"> +</FORM> +</BODY> +</HTML> + + diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi new file mode 100755 index 000000000..1e4a03d84 --- /dev/null +++ b/httemplate/search/svc_acct.cgi @@ -0,0 +1,294 @@ +<% + +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); + +my $orderby = ''; #removeme + +my $limit = ''; +$limit .= "LIMIT $maxrecords" if $maxrecords; + +my $offset = $cgi->param('offset') || 0; +$limit .= " OFFSET $offset" if $offset; + +my $total; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors + +my $unlinked = ''; +if ( $query =~ /^UN_(.*)$/ ) { + $query = $1; + my $empty = driver_name eq 'Pg' ? qq('') : qq(""); + if ( driver_name eq 'mysql' ) { + $unlinked = "LEFT JOIN cust_svc ON cust_svc.svcnum = svc_acct.svcnum + WHERE cust_svc.pkgnum IS NULL + OR cust_svc.pkgnum = 0 + OR cust_svc.pkgnum = $empty"; + } else { + $unlinked = " + WHERE 0 < + ( SELECT count(*) FROM cust_svc + WHERE cust_svc.svcnum = svc_acct.svcnum + AND ( pkgnum IS NULL OR pkgnum = 0 ) + ) + "; + } +} + +my $tblname = driver_name eq 'mysql' ? 'svc_acct.' : ''; +my(@svc_acct, $sortby); +if ( $query eq 'svcnum' ) { + $sortby=\*svcnum_sort; + $orderby = "ORDER BY ${tblname}svcnum"; +} elsif ( $query eq 'username' ) { + $sortby=\*username_sort; + $orderby = "ORDER BY ${tblname}username"; +} elsif ( $query eq 'uid' ) { + $sortby=\*uid_sort; + $orderby = ( $unlinked ? ' AND' : ' WHERE' ). + " ${tblname}uid IS NOT NULL ORDER BY ${tblname}uid"; +} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) { + $unlinked .= ( $unlinked ? 'AND' : 'WHERE' ). + " popnum = $1"; + $sortby=\*username_sort; + $orderby = "ORDER BY ${tblname}username"; +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + $unlinked .= ( $unlinked ? ' AND' : ' WHERE' ). + " $1 = ( SELECT svcpart FROM cust_svc ". + " WHERE cust_svc.svcnum = svc_acct.svcnum ) "; + $sortby=\*uid_sort; + #$sortby=\*svcnum_sort; +} else { + $sortby=\*uid_sort; + @svc_acct = @{&usernamesearch}; +} + + +if ( $query eq 'svcnum' + || $query eq 'username' + || $query eq 'uid' + || $cgi->param('popnum') =~ /^(\d+)$/ + || $cgi->param('svcpart') =~ /^(\d+)$/ + ) { + + my $statement = "SELECT COUNT(*) FROM svc_acct $unlinked"; + my $sth = dbh->prepare($statement) + or die dbh->errstr. " doing $statement"; + $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; + + $total = $sth->fetchrow_arrayref->[0]; + + @svc_acct = qsearch('svc_acct', {}, '', "$unlinked $orderby $limit"); + +} + +if ( scalar(@svc_acct) == 1 ) { + my($svcnum)=$svc_acct[0]->svcnum; + print $cgi->redirect(popurl(2). "view/svc_acct.cgi?$svcnum"); #redirect + #exit; +} elsif ( scalar(@svc_acct) == 0 ) { #error +%> +<!-- mason kludge --> +<% + idiot("Account not found"); +} else { +%> +<!-- mason kludge --> +<% + $total ||= scalar(@svc_acct); + + #begin pager + my $pager = ''; + if ( $total != scalar(@svc_acct) && $maxrecords ) { + unless ( $offset == 0 ) { + $cgi->param('offset', $offset - $maxrecords); + $pager .= '<A HREF="'. $cgi->self_url. + '"><B><FONT SIZE="+1">Previous</FONT></B></A> '; + } + my $poff; + my $page; + for ( $poff = 0; $poff < $total; $poff += $maxrecords ) { + $page++; + if ( $offset == $poff ) { + $pager .= qq!<FONT SIZE="+2">$page</FONT> !; + } else { + $cgi->param('offset', $poff); + $pager .= qq!<A HREF="!. $cgi->self_url. qq!">$page</A> !; + } + } + unless ( $offset + $maxrecords > $total ) { + $cgi->param('offset', $offset + $maxrecords); + $pager .= '<A HREF="'. $cgi->self_url. + '"><B><FONT SIZE="+1">Next</FONT></B></A> '; + } + } + #end pager + + print header("Account Search Results",menubar('Main Menu'=>popurl(2))), + "$total matching accounts found<BR><BR>$pager", + &table(), <<END; + <TR> + <TH><FONT SIZE=-1>#</FONT></TH> + <TH><FONT SIZE=-1>Username</FONT></TH> + <TH><FONT SIZE=-1>Domain</FONT></TH> + <TH><FONT SIZE=-1>UID</FONT></TH> + <TH><FONT SIZE=-1>Service</FONT></TH> + <TH><FONT SIZE=-1>Cust#</FONT></TH> + <TH><FONT SIZE=-1>(bill) name</FONT></TH> + <TH><FONT SIZE=-1>company</FONT></TH> +END + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <<END; + <TH><FONT SIZE=-1>(service) name</FONT></TH> + <TH><FONT SIZE=-1>company</FONT></TH> +END + } + print "</TR>"; + + my(%saw,$svc_acct); + my $p = popurl(2); + foreach $svc_acct ( + sort $sortby grep(!$saw{$_->svcnum}++, @svc_acct) + ) { + my $cust_svc = qsearchs('cust_svc', { 'svcnum' => $svc_acct->svcnum }) + or die "No cust_svc record for svcnum ". $svc_acct->svcnum; + my $part_svc = qsearchs('part_svc', { 'svcpart' => $cust_svc->svcpart }) + or die "No part_svc record for svcpart ". $cust_svc->svcpart; + + my $domain; + my $svc_domain = qsearchs('svc_domain', { 'svcnum' => $svc_acct->domsvc }); + if ( $svc_domain ) { + $domain = "<A HREF=\"${p}view/svc_domain.cgi?". $svc_domain->svcnum. + "\">". $svc_domain->domain. "</A>"; + } else { + die "No svc_domain.svcnum record for svc_acct.domsvc: ". + $svc_acct->domsvc; + } + my($cust_pkg,$cust_main); + if ( $cust_svc->pkgnum ) { + $cust_pkg = qsearchs('cust_pkg', { 'pkgnum' => $cust_svc->pkgnum }) + or die "No cust_pkg record for pkgnum ". $cust_svc->pkgnum; + $cust_main = qsearchs('cust_main', { 'custnum' => $cust_pkg->custnum }) + or die "No cust_main record for custnum ". $cust_pkg->custnum; + } + my($svcnum, $username, $uid, $svc, $custnum, $last, $first, $company) = ( + $svc_acct->svcnum, + $svc_acct->getfield('username'), + $svc_acct->getfield('uid'), + $part_svc->svc, + $cust_svc->pkgnum ? $cust_main->custnum : '', + $cust_svc->pkgnum ? $cust_main->getfield('last') : '', + $cust_svc->pkgnum ? $cust_main->getfield('first') : '', + $cust_svc->pkgnum ? $cust_main->company : '', + ); + my($pcustnum) = $custnum + ? "<A HREF=\"${p}view/cust_main.cgi?$custnum\"><FONT SIZE=-1>$custnum</FONT></A>" + : "<I>(unlinked)</I>" + ; + my $pname = $custnum ? "<A HREF=\"${p}view/cust_main.cgi?$custnum\">$last, $first</A>" : ''; + my $pcompany = $custnum ? "<A HREF=\"${p}view/cust_main.cgi?$custnum\">$company</A>" : ''; + my($pship_name, $pship_company); + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + my($ship_last, $ship_first, $ship_company) = ( + $cust_svc->pkgnum ? ( $cust_main->ship_last || $last ) : '', + $cust_svc->pkgnum ? ( $cust_main->ship_last + ? $cust_main->ship_first + : $first + ) : '', + $cust_svc->pkgnum ? ( $cust_main->ship_last + ? $cust_main->ship_company + : $company + ) : '', + ); + $pship_name = $custnum ? "<A HREF=\"${p}view/cust_main.cgi?$custnum\">$ship_last, $ship_first</A>" : ''; + $pship_company = $custnum ? "<A HREF=\"${p}view/cust_main.cgi?$custnum\">$ship_company</A>" : ''; + } + print <<END; + <TR> + <TD><A HREF="${p}view/svc_acct.cgi?$svcnum"><FONT SIZE=-1>$svcnum</FONT></A></TD> + <TD><A HREF="${p}view/svc_acct.cgi?$svcnum"><FONT SIZE=-1>$username</FONT></A></TD> + <TD><FONT SIZE=-1>$domain</FONT></TD> + <TD><A HREF="${p}view/svc_acct.cgi?$svcnum"><FONT SIZE=-1>$uid</FONT></A></TD> + <TD><FONT SIZE=-1>$svc</FONT></TH> + <TD><FONT SIZE=-1>$pcustnum</FONT></TH> + <TD><FONT SIZE=-1>$pname<FONT></TH> + <TD><FONT SIZE=-1>$pcompany</FONT></TH> +END + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <<END; + <TD><FONT SIZE=-1>$pship_name<FONT></TH> + <TD><FONT SIZE=-1>$pship_company</FONT></TH> +END + } + print "</TR>"; + + } + + print "</TABLE>$pager<BR>". + '</BODY></HTML>'; + +} + +sub svcnum_sort { + $a->getfield('svcnum') <=> $b->getfield('svcnum'); +} + +sub username_sort { + $a->getfield('username') cmp $b->getfield('username'); +} + +sub uid_sort { + $a->getfield('uid') <=> $b->getfield('uid'); +} + +sub usernamesearch { + + my @svc_acct; + + my %username_type; + foreach ( $cgi->param('username_type') ) { + $username_type{$_}++; + } + + $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text + my $username = $1; + + if ( $username_type{'Exact'} || $username_type{'Fuzzy'} ) { + push @svc_acct, qsearch( 'svc_acct', + { 'username' => { 'op' => 'ILIKE', + 'value' => $username } } ); + } + + if ( $username_type{'Substring'} || $username_type{'All'} ) { + push @svc_acct, qsearch( 'svc_acct', + { 'username' => { 'op' => 'ILIKE', + 'value' => "%$username%" } } ); + } + + if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { + &FS::svc_acct::check_and_rebuild_fuzzyfiles; + my $all_username = &FS::svc_acct::all_username; + + my %username; + if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { + foreach ( amatch($username, [ qw(i) ], @$all_username) ) { + $username{$_}++; + } + } + + #if ($username_type{'Sound-alike'}) { + #} + + foreach ( keys %username ) { + push @svc_acct, qsearch('svc_acct',{'username'=>$_}); + } + + } + + #[ qsearch('svc_acct',{'username'=>$username}) ]; + \@svc_acct; + +} + +%> diff --git a/httemplate/search/svc_acct.html b/httemplate/search/svc_acct.html new file mode 100755 index 000000000..742360596 --- /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="post"> + 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_domain.cgi b/httemplate/search/svc_domain.cgi new file mode 100755 index 000000000..948b1d9ae --- /dev/null +++ b/httemplate/search/svc_domain.cgi @@ -0,0 +1,161 @@ +<% + +my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors +my(@svc_domain,$sortby); +if ( $query eq 'svcnum' ) { + $sortby=\*svcnum_sort; + @svc_domain=qsearch('svc_domain',{}); +} elsif ( $query eq 'domain' ) { + $sortby=\*domain_sort; + @svc_domain=qsearch('svc_domain',{}); +} elsif ( $query eq 'UN_svcnum' ) { + $sortby=\*svcnum_sort; + @svc_domain = grep qsearchs('cust_svc',{ + 'svcnum' => $_->svcnum, + 'pkgnum' => '', + }), qsearch('svc_domain',{}); +} elsif ( $query eq 'UN_domain' ) { + $sortby=\*domain_sort; + @svc_domain = grep qsearchs('cust_svc',{ + 'svcnum' => $_->svcnum, + 'pkgnum' => '', + }), qsearch('svc_domain',{}); +} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { + @svc_domain = + qsearch( 'svc_domain', {}, '', + " WHERE $1 = ( SELECT svcpart FROM cust_svc ". + " WHERE cust_svc.svcnum = svc_domain.svcnum ) " + ); + $sortby=\*svcnum_sort; +} else { + $cgi->param('domain') =~ /^([\w\-\.]+)$/; + my($domain)=$1; + #push @svc_domain, qsearchs('svc_domain',{'domain'=>$domain}); + @svc_domain = qsearchs('svc_domain',{'domain'=>$domain}); +} + +if ( scalar(@svc_domain) == 1 ) { + print $cgi->redirect(popurl(2). "view/svc_domain.cgi?". $svc_domain[0]->svcnum); + #exit; +} elsif ( scalar(@svc_domain) == 0 ) { +%> +<!-- mason kludge --> +<% + eidiot "No matching domains found!\n"; +} else { +%> +<!-- mason kludge --> +<% + my($total)=scalar(@svc_domain); + print header("Domain Search Results",''), <<END; + + $total matching domains found + <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> + <TR> + <TH>Service #</TH> + <TH>Domain</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> +--> + </TR> +END + +# my(%saw); # if we've multiple domains with the same + # svcnum, then we've a corrupt database + + foreach my $svc_domain ( +# sort $sortby grep(!$saw{$_->svcnum}++, @svc_domain) + sort $sortby (@svc_domain) + ) { + my($svcnum,$domain)=( + $svc_domain->svcnum, + $svc_domain->domain, + ); + + #don't display all accounts here + my $rowspan = 1; + + #my @svc_acct=qsearch('svc_acct',{'domsvc' => $svcnum}); + #my $rowspan = 0; + # + #my $n1 = ''; + #my($svc_acct, @rows); + #foreach $svc_acct ( + # sort {$b->getfield('username') cmp $a->getfield('username')} (@svc_acct) + #) { + # + # my (@forwards) = (); + # + # my($svcnum,$username)=( + # $svc_acct->svcnum, + # $svc_acct->username, + # ); + # + # my @svc_forward = qsearch( 'svc_forward', { 'srcsvc' => $svcnum } ); + # my $svc_forward; + # foreach $svc_forward (@svc_forward) { + # my($dstsvc,$dst) = ( + # $svc_forward->dstsvc, + # $svc_forward->dst, + # ); + # if ($dstsvc) { + # my $dst_svc_acct=qsearchs( 'svc_acct', { 'svcnum' => $dstsvc } ); + # my $destination=$dst_svc_acct->email; + # push @forwards, qq!<TD><A HREF="!, popurl(2), + # qq!view/svc_acct.cgi?$dstsvc">$destination</A>!, + # qq!</TD></TR>! + # ; + # }else{ + # push @forwards, qq!<TD>$dst</TD></TR>! + # ; + # } + # } + # + # push @rows, qq!$n1<TD ROWSPAN=!, (scalar(@svc_forward) || 1), + # qq!><A HREF="!. popurl(2). qq!view/svc_acct.cgi?$svcnum">!, + # #print '', ( ($domuser eq '*') ? "<I>(anything)</I>" : $domuser ); + # ( ($username eq '*') ? "<I>(anything)</I>" : $username ), + # qq!\@$domain</A> </TD>!, + # ; + # + # push @rows, @forwards; + # + # $rowspan += (scalar(@svc_forward) || 1); + # $n1 = "</TR><TR>"; + #} + ##end of false laziness + # + # + + print <<END; + <TR> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_domain.cgi?$svcnum">$svcnum</A></TD> + <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_domain.cgi?$svcnum">$domain</A></TD> +END + + #print @rows; + print "</TR>"; + + } + + print <<END; + </TABLE> + </BODY> +</HTML> +END + +} + +sub svcnum_sort { + $a->getfield('svcnum') <=> $b->getfield('svcnum'); +} + +sub domain_sort { + $a->getfield('domain') cmp $b->getfield('domain'); +} + + +%> diff --git a/httemplate/search/svc_domain.html b/httemplate/search/svc_domain.html new file mode 100755 index 000000000..94bb9a66d --- /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="post"> + 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..10094bc99 --- /dev/null +++ b/httemplate/search/svc_forward.cgi @@ -0,0 +1,79 @@ +<% + +my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors +my(@svc_forward,$sortby); +if ( $query eq 'svcnum' ) { + $sortby=\*svcnum_sort; + @svc_forward=qsearch('svc_forward',{}); +} else { + eidiot('unimplemented'); +} + +if ( scalar(@svc_forward) == 1 ) { + print $cgi->redirect(popurl(2). "view/svc_forward.cgi?". $svc_forward[0]->svcnum); + #exit; +} elsif ( scalar(@svc_forward) == 0 ) { +%> +<!-- mason kludge --> +<% + eidiot "No matching forwards found!\n"; +} else { +%> +<!-- mason kludge --> +<% + my $total = scalar(@svc_forward); + print header("Mail forward Search Results",''), <<END; + + $total matching mail forwards found + <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> + <TR> + <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> + </TR> +END + + foreach my $svc_forward ( + sort $sortby (@svc_forward) + ) { + my $svcnum = $svc_forward->svcnum; + + my $src = $svc_forward->src; + $src = "<I>(anything)</I>$src" if $src =~ /^@/; + if ( $svc_forward->srcsvc_acct ) { + $src = qq!<A HREF="${p}view/svc_acct.cgi?!. $svc_forward->srcsvc. '">'. + $svc_forward->srcsvc_acct->email. '</A>'; + } + + my $dst = $svc_forward->dst; + if ( $svc_forward->dstsvc_acct ) { + $dst = qq!<A HREF="${p}view/svc_acct.cgi?!. $svc_forward->dstsvc. '">'. + $svc_forward->dstsvc_acct->email. '</A>'; + } + + print <<END; + <TR> + <TD><A HREF="${p}view/svc_forward.cgi?$svcnum">$svcnum</A></TD> + <TD>$src</TD> + <TD>$dst</TD> + </TR> +END + + } + + print <<END; + </TABLE> + </BODY> +</HTML> +END + +} + +sub svcnum_sort { + $a->getfield('svcnum') <=> $b->getfield('svcnum'); +} + +%> diff --git a/httemplate/search/svc_www.cgi b/httemplate/search/svc_www.cgi new file mode 100755 index 000000000..1f05c2377 --- /dev/null +++ b/httemplate/search/svc_www.cgi @@ -0,0 +1,42 @@ +<% + +#my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors +my(@svc_www, $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' => {}, + 'extra_sql' => $orderby, +}; + +my $link = [ "${p}view/svc_www.cgi?", 'svcnum', ]; +#my $dlink = [ "${p}view/svc_www.cgi?", 'svcnum', ]; +my $ulink = [ "${p}view/svc_acct.cgi?", 'usersvc', ]; + + +%> +<%= include( 'elements/search.html', + 'title' => 'Virtual Host Search Results', + 'name' => 'virtual hosts', + 'query' => $sql_query, + 'count_query' => $count_query, + 'header' => [ '#', 'Zone', 'User', ], + 'fields' => [ 'svcnum', + sub { $_[0]->domain_record->zone }, + sub { $_[0]->svc_acct->email }, + ], + 'links' => [ $link, + '', + $ulink, + ], + ) +%> |