diff options
Diffstat (limited to 'httemplate/search')
27 files changed, 2367 insertions, 0 deletions
diff --git a/httemplate/search/cust_bill.cgi b/httemplate/search/cust_bill.cgi new file mode 100755 index 0000000..586399a --- /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; + +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"><FONT SIZE=-1>$invnum</FONT></A></TD> + <TD ROWSPAN=$rowspan ALIGN="right"><A HREF="$view"><FONT SIZE=-1>\$$owed</FONT></A></TD> + <TD ROWSPAN=$rowspan ALIGN="right"><A HREF="$view"><FONT SIZE=-1>\$$charged</FONT></A></TD> + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$pdate</FONT></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"><FONT SIZE=-1>$name</FONT></A></TD> + <TD ROWSPAN=$rowspan><A HREF="$cview"><FONT SIZE=-1>$company</FONT></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><FONT SIZE=-1>Total<BR>Balance</FONT></TH><TH><FONT SIZE=-1>Total<BR>Amount</FONT></TH></TR> + <TR><TD></TD><TD ALIGN="right"><FONT SIZE=-1>\$$tot_balance</FONT></TD><TD ALIGN="right"><FONT SIZE=-1>\$$tot_amount</FONT></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 0000000..36e8bc9 --- /dev/null +++ b/httemplate/search/cust_bill.html @@ -0,0 +1,19 @@ +<HTML> + <HEAD> + <TITLE>Invoice Search</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Invoice Search + </FONT> + <BR><BR> + <FORM ACTION="cust_bill.cgi" METHOD="post"> + Search for <B>invoice #</B>: + <INPUT TYPE="text" NAME="invnum"> + + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + </BODY> +</HTML> + diff --git a/httemplate/search/cust_bill_event.cgi b/httemplate/search/cust_bill_event.cgi new file mode 100644 index 0000000..9cb36d2 --- /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); + +$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/; +my $ending = str2time($1) + 86400; + +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 0000000..d76ce3c --- /dev/null +++ b/httemplate/search/cust_bill_event.html @@ -0,0 +1,23 @@ +<HTML> + <HEAD> + <TITLE>Failed billing events</TITLE> + </HEAD> + <BODY> + <CENTER> + <H1>Failed billing events</H1> + </CENTER> + <HR> + <FORM ACTION="cust_bill_event.cgi" METHOD="post"> + Return <B>failed billing events</B> for period: + from <INPUT TYPE="text" NAME="beginning"> <i>m/d/y</i> + to <INPUT TYPE="text" NAME="ending"> <i>m/d/y</i> + + <P><INPUT TYPE="submit" VALUE="Get Report"> + + </FORM> + + <HR> + + </BODY> +</HTML> + diff --git a/httemplate/search/cust_main-otaker.cgi b/httemplate/search/cust_main-otaker.cgi new file mode 100755 index 0000000..b7173c4 --- /dev/null +++ b/httemplate/search/cust_main-otaker.cgi @@ -0,0 +1,29 @@ +<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 $dbh = dbh; + my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_main") + or eidiot $dbh->errstr; + $sth->execute() or eidiot $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 0000000..671b5ef --- /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 0000000..9f39db9 --- /dev/null +++ b/httemplate/search/cust_main-quickpay.html @@ -0,0 +1,43 @@ +<HTML> + <HEAD> + <TITLE>Quick payment entry</TITLE> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <FONT SIZE=7> + Quick payment entry + </FONT> + <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 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_main.cgi b/httemplate/search/cust_main.cgi new file mode 100755 index 0000000..586f8d9 --- /dev/null +++ b/httemplate/search/cust_main.cgi @@ -0,0 +1,659 @@ +<% + +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') +) { + + 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; + } else { + die "unknown query..."; + } + } + + my $ncancelled = ''; + + if ( driver_name eq 'mysql' ) { + + my $query = "CREATE TEMPORARY TABLE temp1_$$ TYPE=MYISAM + SELECT cust_pkg.custnum,COUNT(*) as count + FROM cust_pkg,cust_main + WHERE cust_pkg.custnum = cust_main.custnum + AND ( cust_pkg.cancel IS NULL + OR cust_pkg.cancel = 0 ) + GROUP BY cust_pkg.custnum"; + 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.custnum,COUNT(*) as count + FROM cust_pkg,cust_main + WHERE cust_pkg.custnum = cust_main.custnum + GROUP BY cust_pkg.custnum"; + my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + } + + if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me + || ( $conf->exists('hidecancelledcustomers') + && ! $cgi->param('showcancelledcustomers') ) + ) { + #grep { $_->ncancelled_pkgs || ! $_->all_pkgs } + if ( driver_name eq 'mysql' ) { + $ncancelled = " + temp1_$$.custnum = cust_main.custnum + AND temp2_$$.custnum = cust_main.custnum + AND (temp1_$$.count > 0 + OR temp2_$$.count = 0 ) + "; + } else { + $ncancelled = " + 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 + ) + "; + } + + } + + #EWWWWWW + my $qual = join(' AND ', + map { "$_ = ". dbh->quote($search{$_}) } keys %search ); + + if ( $ncancelled ) { + $qual .= ' AND ' if $qual; + $qual .= $ncancelled; + } + + $qual = " WHERE $qual" if $qual; + my $statement; + if ( driver_name eq 'mysql' ) { + $statement = "SELECT COUNT(*) FROM cust_main"; + $statement .= ", temp1_$$, temp2_$$ $qual" if $qual; + } else { + $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 ( $ncancelled ) { + if ( %search ) { + $ncancelled = " AND $ncancelled"; + } else { + $ncancelled = " WHERE $ncancelled"; + } + } + + my @just_cust_main; + if ( driver_name eq 'mysql' ) { + @just_cust_main = qsearch('cust_main', \%search, 'cust_main.*', + ",temp1_$$,temp2_$$ $ncancelled $orderby $limit"); + } else { + @just_cust_main = qsearch('cust_main', \%search, '', + "$ncancelled $orderby $limit" ); + } + if ( driver_name eq 'mysql' ) { + $query = "DROP TABLE temp1_$$,temp2_$$;"; + my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + } + @cust_main = @just_cust_main; + +# 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('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 + + 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 cancelled customers</a> )!; + } else { + $cgi->param('showcancelledcustomers', 0); + $cgi->param('offset', 0); + print qq!( <a href="!. $cgi->self_url. qq!">hide 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_pkg.cgi?'. $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"; + my $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'}) ]; +} + +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'} ) { + + &FS::cust_main::check_and_rebuild_fuzzyfiles; + my $all_last = &FS::cust_main::all_last; + + my %last; + if ( $last_type{'Fuzzy'} || $last_type{'All'} ) { + foreach ( amatch($last, [ qw(i) ], @$all_last) ) { + $last{$_}++; + } + } + + #if ($last_type{'Sound-alike'}) { + #} + + foreach ( keys %last ) { + push @cust_main, qsearch('cust_main',{'last'=>$_}); + push @cust_main, qsearch('cust_main',{'ship_last'=>$_}) + if defined dbdef->table('cust_main')->column('ship_last'); + } + + } + + \@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'} ) { + + &FS::cust_main::check_and_rebuild_fuzzyfiles; + my $all_company = &FS::cust_main::all_company; + + my %company; + if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { + foreach ( amatch($company, [ qw(i) ], @$all_company ) ) { + $company{$_}++; + } + } + + #if ($company_type{'Sound-alike'}) { + #} + + foreach ( keys %company ) { + push @cust_main, qsearch('cust_main',{'company'=>$_}); + push @cust_main, qsearch('cust_main',{'ship_company'=>$_}) + if defined dbdef->table('cust_main')->column('ship_last'); + } + + } + + \@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'); + + #false laziness with Record::ut_phonen, only works with US/CA numbers... + $phone =~ s/\D//g; + $phone =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ + or eidiot gettext('illegal_phone'). ": $phone"; + $phone = "$1-$2-$3"; + $phone .= " x$4" if $4; + + 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 0000000..5a066e4 --- /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 0000000..b5bdf82 --- /dev/null +++ b/httemplate/search/cust_pay.cgi @@ -0,0 +1,103 @@ +<% + +$cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo"; +my $payinfo = $1; +$cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby"; +my $payby = $1; +my @cust_pay = qsearch('cust_pay', { 'payinfo' => $payinfo, + 'payby' => $payby } ); +my $sortby = \*date_sort; + +if (0) { +#if ( scalar(@cust_pay) == 1 ) { +# my $invnum = $cust_bill[0]->invnum; +# print $cgi->redirect(popurl(2). "view/cust_bill.cgi?$invnum"); #redirect +} elsif ( scalar(@cust_pay) == 0 ) { +%> +<!-- mason kludge --> +<% + idiot("Check # not found."); + #exit; +} else { + my $total = scalar(@cust_pay); + my $s = $total > 1 ? 's' : ''; +%> +<!-- mason kludge --> +<% + print header("Check # Search Results", menubar( + 'Main Menu', popurl(2) + )), "$total matching check$s found<BR>", &table(), <<END; + <TR> + <TH></TH> + <TH>Amount</TH> + <TH>Date</TH> + <TH>Contact name</TH> + <TH>Company</TH> + </TR> +END + + my(%saw, $cust_pay); + foreach my $cust_pay ( + sort $sortby grep(!$saw{$_->paynum}++, @cust_pay) + ) { + my($paynum, $custnum, $payinfo, $amount, $date ) = ( + $cust_pay->paynum, + $cust_pay->custnum, + $cust_pay->payinfo, + sprintf("%.2f", $cust_pay->paid), + $cust_pay->_date, + ); + my $pdate = time2str("%b %d %Y", $date); + + my $rowspan = 1; + + my $view = popurl(2). "view/cust_main.cgi?". $custnum. + "#". $payby. $payinfo; + + print <<END; + <TR> + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$payinfo</FONT></A></TD> + <TD ROWSPAN=$rowspan ALIGN="right"><A HREF="$view"><FONT SIZE=-1>\$$amount</FONT></A></TD> + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$pdate</FONT></A></TD> +END + 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="$view"><FONT SIZE=-1>$name</FONT></A></TD> + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$company</FONT></A></TD> +END + } else { + print <<END + <TD ROWSPAN=$rowspan COLSPAN=2>WARNING: couldn't find cust_main.custnum $custnum (cust_pay.paynum $paynum)</TD> +END + } + + print "</TR>"; + } + print <<END; + </TABLE> + </BODY> +</HTML> +END + +} + +# + +#sub invnum_sort { +# $a->invnum <=> $b->invnum; +#} +# +#sub custnum_sort { +# $a->custnum <=> $b->custnum || $a->invnum <=> $b->invnum; +#} + +sub date_sort { + $a->_date <=> $b->_date || $a->invnum <=> $b->invnum; +} +%> diff --git a/httemplate/search/cust_pay.html b/httemplate/search/cust_pay.html new file mode 100755 index 0000000..3848d66 --- /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 0000000..abf6eee --- /dev/null +++ b/httemplate/search/cust_pkg.cgi @@ -0,0 +1,319 @@ +<% + +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; + 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) + 86400; + $range .= ( $range ? ' AND ' : ' WHERE ' ). " bill <= $ending "; + } + + #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 $unconf = ''; + if ( $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"; + $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; + $unconf = " LEFT JOIN temp2_$$ ON cust_pkg.pkgnum = temp2_$$.pkgnum + WHERE temp2_$$.pkgnum IS NOT NULL"; + + } else { + + $unconf = " + 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 QUERY_STRING!"; + } + + my $statement = "SELECT COUNT(*) FROM cust_pkg $unconf"; + 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',{}, '', "$unconf 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 ) { + my($pkgnum)=$cust_pkg[0]->pkgnum; + print $cgi->redirect(popurl(2). "view/cust_pkg.cgi?$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> + <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 + +if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <<END; + <TH>(service) name</TH> + <TH>company</TH> +END +} + +print <<END; + <TH COLSPAN=2>Services</TH> + </TR> +END + + 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($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_pkg.cgi?$pkgnum"><FONT SIZE=-1>$pkgnum - $pkg</FONT></A></TD> + <TD>$setup</TD> + <TD>$bill</TD> + <TD>$susp</TD> + <TD>$expire</TD> + <TD>$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.html b/httemplate/search/cust_pkg.html new file mode 100755 index 0000000..bb0a540 --- /dev/null +++ b/httemplate/search/cust_pkg.html @@ -0,0 +1,24 @@ +<HTML> + <HEAD> + <TITLE>Packages</TITLE> + </HEAD> + <BODY> + <CENTER> + <H1>Packages</H1> + </CENTER> + <HR> + <FORM ACTION="cust_pkg.cgi" METHOD="post"> + <INPUT TYPE="hidden" NAME="magic" VALUE="bill"> + Return <B>packages</B> with next bill date: + from <INPUT TYPE="text" NAME="beginning"> <i>m/d/y</i> + to <INPUT TYPE="text" NAME="ending"> <i>m/d/y</i> + + <P><INPUT TYPE="submit" VALUE="Get Report"> + + </FORM> + + <HR> + + </BODY> +</HTML> + diff --git a/httemplate/search/report_cc.cgi b/httemplate/search/report_cc.cgi new file mode 100755 index 0000000..c2ab726 --- /dev/null +++ b/httemplate/search/report_cc.cgi @@ -0,0 +1,25 @@ +<!-- mason kludge --> +<% + +my $user = getotaker; + +$cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/; +my $beginning = $1; + +$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/; +my $ending = $1; + +print header('Credit Card Recipt Report Results'); + +open (REPORT, "freeside-cc-receipts-report -v -s $beginning -f $ending $user |"); + +print '<PRE>'; +while(<REPORT>) { + print $_; +} +print '</PRE>'; + +print '</BODY></HTML>'; + +%> + diff --git a/httemplate/search/report_cc.html b/httemplate/search/report_cc.html new file mode 100755 index 0000000..8653dcc --- /dev/null +++ b/httemplate/search/report_cc.html @@ -0,0 +1,23 @@ +<HTML> + <HEAD> + <TITLE>Credit Card Receipt Report Criteria</TITLE> + </HEAD> + <BODY> + <CENTER> + <H1>Credit Card Receipt Report Criteria</H1> + </CENTER> + <HR> + <FORM ACTION="report_cc.cgi" METHOD="post"> + Return <B>credit card receipt report</B> for period: + from <INPUT TYPE="text" NAME="beginning"> <i>m/d/y</i> + to <INPUT TYPE="text" NAME="ending"> <i>m/d/y</i> + + <P><INPUT TYPE="submit" VALUE="Get Report"> + + </FORM> + + <HR> + + </BODY> +</HTML> + diff --git a/httemplate/search/report_credit.cgi b/httemplate/search/report_credit.cgi new file mode 100755 index 0000000..2adafc0 --- /dev/null +++ b/httemplate/search/report_credit.cgi @@ -0,0 +1,25 @@ +<!-- mason kludge --> +<% + +my $user = getotaker; + +$cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/; +my $beginning = $1; + +$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/; +my $ending = $1; + +print header('In House Credit Report Results'); + +open (REPORT, "freeside-credit-report -v -s $beginning -f $ending $user |"); + +print '<PRE>'; +while(<REPORT>) { + print $_; +} +print '</PRE>'; + +print '</BODY></HTML>'; + +%> + diff --git a/httemplate/search/report_credit.html b/httemplate/search/report_credit.html new file mode 100755 index 0000000..df9b958 --- /dev/null +++ b/httemplate/search/report_credit.html @@ -0,0 +1,23 @@ +<HTML> + <HEAD> + <TITLE>In House Credit Report Criteria</TITLE> + </HEAD> + <BODY> + <CENTER> + <H1>In House Credit Report Criteria</H1> + </CENTER> + <HR> + <FORM ACTION="report_credit.cgi" METHOD="post"> + Return <B>in house credit report</B> for period: + from <INPUT TYPE="text" NAME="beginning"> <i>m/d/y</i> + to <INPUT TYPE="text" NAME="ending"> <i>m/d/y</i> + + <P><INPUT TYPE="submit" VALUE="Get Report"> + + </FORM> + + <HR> + + </BODY> +</HTML> + diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi new file mode 100755 index 0000000..fdd3779 --- /dev/null +++ b/httemplate/search/report_receivables.cgi @@ -0,0 +1,19 @@ +<!-- mason kludge --> +<% + +my $user = getotaker; + +print header('Current Receivables Report Results'); + +open (REPORT, "freeside-receivables-report -v $user |"); + +print '<PRE>'; +while(<REPORT>) { + print $_; +} +print '</PRE>'; + +print '</BODY></HTML>'; + +%> + diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi new file mode 100755 index 0000000..ac76fad --- /dev/null +++ b/httemplate/search/report_tax.cgi @@ -0,0 +1,25 @@ +<!-- mason kludge --> +<% + +my $user = getotaker; + +$cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/; +my $beginning = $1; + +$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/; +my $ending = $1; + +print header('Tax Report Results'); + +open (REPORT, "freeside-tax-report -v -s $beginning -f $ending $user |"); + +print '<PRE>'; +while(<REPORT>) { + print $_; +} +print '</PRE>'; + +print '</BODY></HTML>'; + +%> + diff --git a/httemplate/search/report_tax.html b/httemplate/search/report_tax.html new file mode 100755 index 0000000..7bf681b --- /dev/null +++ b/httemplate/search/report_tax.html @@ -0,0 +1,23 @@ +<HTML> + <HEAD> + <TITLE>Tax Report Criteria</TITLE> + </HEAD> + <BODY> + <CENTER> + <H1>Tax Report Criteria</H1> + </CENTER> + <HR> + <FORM ACTION="report_tax.cgi" METHOD="post"> + Return <B>tax report</B> for period: + from <INPUT TYPE="text" NAME="beginning"> <i>m/d/y</i> + to <INPUT TYPE="text" NAME="ending"> <i>m/d/y</i> + + <P><INPUT TYPE="submit" VALUE="Get Report"> + + </FORM> + + <HR> + + </BODY> +</HTML> + diff --git a/httemplate/search/sql.cgi b/httemplate/search/sql.cgi new file mode 100755 index 0000000..b83ef03 --- /dev/null +++ b/httemplate/search/sql.cgi @@ -0,0 +1,76 @@ +<% + +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); + +my $limit = ''; +$limit .= "LIMIT $maxrecords" if $maxrecords; + +my $offset = $cgi->param('offset') || 0; +$limit .= " OFFSET $offset" if $offset; + +my $total; + +my $sql = $cgi->param('sql'); +$sql =~ s/^\s*SELECT//i; + +my $count_sql = $sql; +$count_sql =~ s/^(.*)\s+FROM\s/COUNT(*) FROM /i; + +my $sth = dbh->prepare("SELECT $count_sql") + or eidiot dbh->errstr. " doing $count_sql\n"; +$sth->execute or eidiot "Error executing \"$count_sql\": ". $sth->errstr; + +$total = $sth->fetchrow_arrayref->[0]; + +my $sth = dbh->prepare("SELECT $sql $limit") + or eidiot dbh->errstr. " doing $sql\n"; +$sth->execute or eidiot "Error executing \"$sql\": ". $sth->errstr; +my $rows = $sth->fetchall_arrayref; + +%> +<!-- mason kludge --> +<% + + #begin pager + my $pager = ''; + if ( $total != scalar(@$rows) && $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('Query Results', menubar('Main Menu'=>$p) ). + "$total total rows<BR><BR>$pager". table(). + "<TR>"; + print "<TH>$_</TH>" foreach @{$sth->{NAME}}; + print "</TR>"; + + foreach $row ( @$rows ) { + print "<TR>"; + print "<TD>$_</TD>" foreach @$row; + print "</TR>"; + } + + print "</TABLE>$pager</BODY></HTML>"; + +%> diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi new file mode 100755 index 0000000..549231d --- /dev/null +++ b/httemplate/search/svc_acct.cgi @@ -0,0 +1,294 @@ +<% + +my $mydomain = ''; + +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 OR pkgnum = $empty ) + ) + "; + } +} + +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"; +} else { + $sortby=\*uid_sort; + @svc_acct = @{&usernamesearch}; +} + +if ( $query eq 'svcnum' || $query eq 'username' || $query eq 'uid' ) { + + 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 { + unless ( $mydomain ) { + my $conf = new FS::Conf; + unless ( $mydomain = $conf->config('domain') ) { + die "No legacy domain config file and no svc_domain.svcnum record ". + "for svc_acct.domsvc: ". $svc_acct->domsvc; + } + } + $domain = "<i>$mydomain</i><FONT COLOR=\"#FF0000\">*</FONT>"; + } + 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>"; + + if ( $mydomain ) { + print "<BR><FONT COLOR=\"#FF0000\">*</FONT> The <I>$mydomain</I> domain ". + "is contained in your legacy <CODE>domain</CODE> ". + "<A HREF=\"${p}docs/config.html#domain\">configuration file</A>. ". + "You should run the <CODE>bin/fs-migrate-svc_acct_sm</CODE> script ". + "to create a proper svc_domain record for this domain."; + } + + print '</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 0000000..7423605 --- /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_acct_sm.cgi b/httemplate/search/svc_acct_sm.cgi new file mode 100755 index 0000000..4ee3006 --- /dev/null +++ b/httemplate/search/svc_acct_sm.cgi @@ -0,0 +1,84 @@ +<% + +my $conf = new FS::Conf; +my $mydomain = $conf->config('domain'); + +$cgi->param('domuser') =~ /^([a-z0-9_\-]{0,32})$/; +my $domuser = $1; + +$cgi->param('domain') =~ /^([\w\-\.]+)$/ or die "Illegal domain"; +my $svc_domain = qsearchs('svc_domain',{'domain'=>$1}) + or die "Unknown domain"; +my $domsvc = $svc_domain->svcnum; + +my @svc_acct_sm; +if ($domuser) { + @svc_acct_sm=qsearch('svc_acct_sm',{ + 'domuser' => $domuser, + 'domsvc' => $domsvc, + }); +} else { + @svc_acct_sm=qsearch('svc_acct_sm',{'domsvc' => $domsvc}); +} + +if ( scalar(@svc_acct_sm) == 1 ) { + my($svcnum)=$svc_acct_sm[0]->svcnum; + print $cgi->redirect(popurl(2). "view/svc_acct_sm.cgi?$svcnum"); +} elsif ( scalar(@svc_acct_sm) > 1 ) { +%> +<!-- mason kludge --> +<% + print header('Mail Alias Search Results'), &table(), <<END; + <TR> + <TH>Mail to<BR><FONT SIZE=-1>(click to view mail alias)</FONT></TH> + <TH>Forwards to<BR><FONT SIZE=-1>(click to view account)</FONT></TH> + </TR> +END + + my($svc_acct_sm); + foreach $svc_acct_sm (@svc_acct_sm) { + my($svcnum,$domuser,$domuid,$domsvc)=( + $svc_acct_sm->svcnum, + $svc_acct_sm->domuser, + $svc_acct_sm->domuid, + $svc_acct_sm->domsvc, + ); + + my $svc_domain = qsearchs( 'svc_domain', { 'svcnum' => $domsvc } ); + if ( $svc_domain ) { + my $domain = $svc_domain->domain; + + print qq!<TR><TD><A HREF="!. popurl(2). qq!view/svc_acct_sm.cgi?$svcnum">!, + #print '', ( ($domuser eq '*') ? "<I>(anything)</I>" : $domuser ); + ( ($domuser eq '*') ? "<I>(anything)</I>" : $domuser ), + qq!\@$domain</A> </TD>!, + ; + } else { + my $warning = "couldn't find svc_domain.svcnum $svcnum ( svc_acct_sm.svcnum $svcnum"; + warn $warning; + print "<TR><TD>WARNING: $warning</TD>"; + } + + my $svc_acct = qsearchs( 'svc_acct', { 'uid' => $domuid } ); + if ( $svc_acct ) { + my $username = $svc_acct->username; + my $svc_acct_svcnum =$svc_acct->svcnum; + print qq!<TD><A HREF="!, popurl(2), + qq!view/svc_acct.cgi?$svc_acct_svcnum">$username\@$mydomain</A>!, + qq!</TD></TR>! + ; + } else { + my $warning = "couldn't find svc_acct.uid $domuid (svc_acct_sm.svcnum $svcnum)!"; + warn $warning; + print "<TD>WARNING: $warning</TD></TR>"; + } + + } + + print '</TABLE></BODY></HTML>'; + +} else { #error + idiot("Mail Alias not found"); +} + +%> diff --git a/httemplate/search/svc_acct_sm.html b/httemplate/search/svc_acct_sm.html new file mode 100755 index 0000000..0719856 --- /dev/null +++ b/httemplate/search/svc_acct_sm.html @@ -0,0 +1,23 @@ +<HTML> + <HEAD> + <TITLE>Mail Alias Search</TITLE> + </HEAD> + <BODY> + <CENTER> + <H1>Mail Alias Search</H1> + </CENTER> + <HR> + <FORM ACTION="svc_acct_sm.cgi" METHOD="post"> + Search for <B>mail alias</B>: + <INPUT TYPE="text" NAME="domuser"><FONT SIZE=-1>(opt.)</FONT> @ + <INPUT TYPE="text" NAME="domain"><FONT SIZE=-1>(req.)</FONT> + + <P><INPUT TYPE="submit" VALUE="Search"> + + </FORM> + + <HR> + + </BODY> +</HTML> + diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi new file mode 100755 index 0000000..fb372db --- /dev/null +++ b/httemplate/search/svc_domain.cgi @@ -0,0 +1,163 @@ +<% + +my $conf = new FS::Conf; +my $mydomain = $conf->config('domain'); + +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',{}); +} 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, + ); + #my($malias); + #if ( qsearch('svc_acct_sm',{'domsvc'=>$svcnum}) ) { + # $malias=( + # qq|<FORM ACTION="svc_acct_sm.cgi" METHOD="post">|. + # qq|<INPUT TYPE="hidden" NAME="domuser" VALUE="">|. + # qq|<INPUT TYPE="hidden" NAME="domain" VALUE="$domain">|. + # qq|<INPUT TYPE="submit" VALUE="(mail aliases)">|. + # qq|</FORM>| + # ); + #} else { + # $malias=''; + #} + + 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"><FONT SIZE=-1>$svcnum</FONT></A></TD> + <TD ROWSPAN=$rowspan>$domain</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 0000000..94bb9a6 --- /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> + |