diff options
Diffstat (limited to 'httemplate/search')
30 files changed, 2881 insertions, 0 deletions
diff --git a/httemplate/search/cust_bill.cgi b/httemplate/search/cust_bill.cgi new file mode 100755 index 0000000..985e3db --- /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"><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..ec952ea --- /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 = str2time($1) + 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 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..d48f1d0 --- /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>All + <OPTION>Fuzzy + <OPTION>Substring + <OPTION SELECTED>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>All + <OPTION>Fuzzy + <OPTION>Substring + <OPTION SELECTED>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 0000000..50d367e --- /dev/null +++ b/httemplate/search/cust_main.cgi @@ -0,0 +1,697 @@ +<% + +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 $ncancelled = ''; + + if ( driver_name eq 'mysql' ) { + + my $sql = "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($sql) or die dbh->errstr. " preparing $sql"; + $sth->execute or die "Error executing \"$sql\": ". $sth->errstr; + $sql = "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"; + $sth = dbh->prepare($sql) or die dbh->errstr. " preparing $sql"; + $sth->execute or die "Error executing \"$sql\": ". $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 + ) + "; + } + } + + my $cancelled = ''; + if ( $cgi->param('cancelled') ) { + $cancelled = " + 0 = ( SELECT COUNT(*) FROM cust_pkg + WHERE cust_pkg.custnum = cust_main.custnum + AND ( cust_pkg.cancel IS NULL + OR cust_pkg.cancel = 0 + ) + ) + AND 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 ( $cancelled ) { + $qual .= ' AND ' if $qual; + $qual .= $cancelled; + } elsif ( $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]; + + my $rqual = $cancelled || $ncancelled; + if ( $rqual ) { + if ( %search ) { + $rqual = " AND $rqual"; + } else { + $rqual = " WHERE $rqual"; + } + } + + my @just_cust_main; + if ( driver_name eq 'mysql' ) { + @just_cust_main = qsearch('cust_main', \%search, 'cust_main.*', + ",temp1_$$,temp2_$$ $rqual $orderby $limit"); + } else { + @just_cust_main = qsearch('cust_main', \%search, '', + "$rqual $orderby $limit" ); + } + if ( driver_name eq 'mysql' ) { + my $sql = "DROP TABLE temp1_$$,temp2_$$;"; + my $sth = dbh->prepare($sql) or die dbh->errstr. " preparing $sql"; + $sth->execute or die "Error executing \"$sql\": ". $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('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_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"; + $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'} ) { + + &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'); + + #(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 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..51dd3b3 --- /dev/null +++ b/httemplate/search/cust_pay.cgi @@ -0,0 +1,185 @@ +<% + +my $sortby; +my @cust_pay; +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 "; + } + my $search; + if ( @search ) { + $search = ( scalar(keys %search) ? ' AND ' : ' WHERE ' ). + join(' AND ', @search); + } + + @cust_pay = qsearch('cust_pay', \%search, '', $search ); + + $sortby = \*date_sort; + +} 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; + + @cust_pay = qsearch('cust_pay', { 'payinfo' => $payinfo, + 'payby' => $payby } ); + $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("Payment not found."); + #exit; +} else { + my $total = scalar(@cust_pay); + my $s = $total > 1 ? 's' : ''; +%> +<!-- mason kludge --> +<% + print header("Payment Search Results", menubar( + 'Main Menu', popurl(2) + )), "$total matching payment$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); + my $tot_amount = 0; + foreach my $cust_pay ( + sort $sortby grep(!$saw{$_->paynum}++, @cust_pay) + ) { + my($paynum, $custnum, $payby, $payinfo, $amount, $date ) = ( + $cust_pay->paynum, + $cust_pay->custnum, + $cust_pay->payby, + $cust_pay->payinfo, + sprintf("%.2f", $cust_pay->paid), + $cust_pay->_date, + ); + $tot_amount += $amount; + my $pdate = time2str("%b %d %Y", $date); + + my $rowspan = 1; + + my $view = popurl(2). "view/cust_main.cgi?". $custnum. + "#". $payby. $payinfo; + + my $payment_info; + if ( $payby eq 'CARD' ) { + $payment_info = 'Card #'. 'x'x(length($payinfo)-4). + substr($payinfo,(length($payinfo)-4)); + } elsif ( $payby eq 'CHEK' ) { + $payment_info = "E-check acct#$payinfo"; + } elsif ( $payby eq 'BILL' ) { + $payment_info = "Check #$payinfo"; + } else { + $payment_info = "$payby $payinfo"; + } + + print <<END; + <TR> + <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$payment_info</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>"; + } + + $tot_amount = sprintf("%.2f", $tot_amount); + print '</TABLE><BR>'. table(). <<END; + <TR> + <TH>Total Amount</TH> + <TD ALIGN="right">\$$tot_amount</TD> + </TR> + </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..45420f4 --- /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 ) { + 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> +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_pkg.cgi?$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 0000000..b316745 --- /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 0000000..fbedcaa --- /dev/null +++ b/httemplate/search/elements/search.html @@ -0,0 +1,59 @@ +<% + + my %opt = @_; + 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 $total = $count_sth->fetchrow_arrayref->[0]; + + 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? + my $rows = $sth->fetchall_arrayref; + +%> +<!-- mason kludge --> +<% my $pager = include ( '/elements/pager.html', + 'offset' => $offset, + 'num_rows' => scalar(@$rows), + 'total' => $total, + 'maxrecords' => $maxrecords, + ); +%> + +<%= $total %> total <%= $opt{'name'} %><BR><BR><%= $pager %> +<%= include( '/elements/table.html' ) %> + <TR> + <% foreach ( @{$sth->{NAME}} ) { %> + <TH><%= $_ %></TH> + <% } %> + </TR> + <% foreach my $row ( @$rows ) { %> + <TR> + <% foreach ( @$row ) { %> + <TD><%= $_ %></TD> + <% } %> + </TR> + <% } %> + +</TABLE> +<%= $pager %> +</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..595a7b1 --- /dev/null +++ b/httemplate/search/report_cc.html @@ -0,0 +1,43 @@ +<HTML> + <HEAD> + <TITLE>Credit Card Receipt 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 Card Receipt Report Criteria</H1> + <FORM ACTION="report_cc.cgi" METHOD="post"> + Return <B>credit card receipt 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> + <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_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..11cb32e --- /dev/null +++ b/httemplate/search/report_credit.html @@ -0,0 +1,43 @@ +<HTML> + <HEAD> + <TITLE>In House 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>In House Credit Report Criteria</H1> + <FORM ACTION="report_credit.cgi" METHOD="post"> + Return <B>in house credit 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> + <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 0000000..1b30685 --- /dev/null +++ b/httemplate/search/report_cust_pay.html @@ -0,0 +1,54 @@ +<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> + <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 0000000..eb8bbb5 --- /dev/null +++ b/httemplate/search/report_prepaid_income.cgi @@ -0,0 +1,75 @@ +<!-- mason kludge --> +<% + + #doesn't yet deal with daily/weekly packages + + #needs to be re-written in sql for efficiency + + my $now = $cgi->param('date') && str2time($cgi->param('date')) || time; + $now =~ /^(\d+)$/ or die "unparsable date?"; + $now = $1; + + my %prepaid; + + 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 }, + }, ); + + foreach my $cust_bill_pkg ( @cust_bill_pkg ) { + + #conceptual false laziness w/texas tax exempt_amount stuff in + #FS::cust_main::bill + + my $freq = $cust_bill_pkg->cust_pkg->part_pkg->freq; + my $per_month = sprintf("%.2f", $cust_bill_pkg->recur / $freq); + + my($mon, $year) = (localtime($cust_bill_pkg->sdate) )[4,5]; + $mon+=2; $year+=1900; + + foreach my $which_month ( 2 .. $freq ) { + until ( $mon < 13 ) { $mon -= 12; $year++; } + $prepaid{"$year-$mon"} += $per_month; + $mon++; + } + + } + + my @mon = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec); + +%> + +<%= header( 'Prepaid Income (Unearned Revenue) Report', + menubar( 'Main Menu'=>$p, ) ) %> +<%= table() %> +<% + + my $total = 0; + + my ($now_mon, $now_year) = (localtime($now))[4,5]; + $now_mon+=2; $now_year+=1900; + until ( $now_mon < 13 ) { $now_mon -= 12; $now_year++; } + + my $subseq = 0; + for my $year ( $now_year .. 2037 ) { + for my $mon ( ( $subseq++ ? 1 : $now_mon ) .. 12 ) { + if ( $prepaid{"$year-$mon"} ) { + $total += $prepaid{"$year-$mon"}; + %> <TR><TD ALIGN="right"><%= $mon[$mon-1]. ' '. $year %></TD> + <TD ALIGN="right"> + <%= sprintf("%.2f", $prepaid{"$year-$mon"} ) %> + </TD> + </TR> + <% + } + } + + } + +%> +<TR><TH>Total</TH><TD ALIGN="right"><%= sprintf("%.2f", $total) %></TD></TR> +</TABLE> +</BODY> +</HTML> diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html new file mode 100644 index 0000000..b85a481 --- /dev/null +++ b/httemplate/search/report_prepaid_income.html @@ -0,0 +1,17 @@ +<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"> + Prepaid income (unearned revenue) as of <INPUT TYPE="text" NAME="date" VALUE="now"> + <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 0000000..ad353a1 --- /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 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 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..7bc35e3 --- /dev/null +++ b/httemplate/search/report_tax.html @@ -0,0 +1,43 @@ +<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> + <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 0000000..7d7fc08 --- /dev/null +++ b/httemplate/search/sql.html @@ -0,0 +1,12 @@ +<%= include( '/elements/header.html', 'Query Results', + include( '/elements/menubar.html', 'Main Menu' => $p ) + ) +%> + +<%= include( 'elements/search.html', + 'name' => 'rows', + 'query' => 'SELECT '. ( $cgi->param('sql') + || eidiot('Empty query') ), + ) +%> + diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi new file mode 100755 index 0000000..1e4a03d --- /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 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_domain.cgi b/httemplate/search/svc_domain.cgi new file mode 100755 index 0000000..948b1d9 --- /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 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> + diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi new file mode 100755 index 0000000..10094bc --- /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'); +} + +%> |