diff options
Diffstat (limited to 'httemplate/search')
23 files changed, 956 insertions, 442 deletions
diff --git a/httemplate/search/cust_bill.cgi b/httemplate/search/cust_bill.cgi index 586399a41..985e3dbf5 100755 --- a/httemplate/search/cust_bill.cgi +++ b/httemplate/search/cust_bill.cgi @@ -11,7 +11,7 @@ $limit .= "LIMIT $maxrecords" if $maxrecords; my $offset = $cgi->param('offset') || 0; $limit .= " OFFSET $offset" if $offset; -my $total; +my($total, $tot_amount, $tot_balance); my(@cust_bill); if ( $cgi->keywords ) { diff --git a/httemplate/search/cust_bill_event.cgi b/httemplate/search/cust_bill_event.cgi index 9cb36d28e..ec952ea5b 100644 --- a/httemplate/search/cust_bill_event.cgi +++ b/httemplate/search/cust_bill_event.cgi @@ -4,10 +4,10 @@ #false laziness with view/cust_bill.cgi $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/; -my $beginning = str2time($1); +my $beginning = str2time($1) || 0; $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/; -my $ending = str2time($1) + 86400; +my $ending = str2time($1) + 86399; my @cust_bill_event = sort { $a->_date <=> $b->_date } diff --git a/httemplate/search/cust_main-quickpay.html b/httemplate/search/cust_main-quickpay.html index 9f39db914..d48f1d08f 100755 --- a/httemplate/search/cust_main-quickpay.html +++ b/httemplate/search/cust_main-quickpay.html @@ -7,27 +7,28 @@ Quick payment entry </FONT> <BR><BR> + <A HREF="../">Main Menu</A><BR><BR> <FORM ACTION="cust_main.cgi" METHOD="post"> <INPUT TYPE="hidden" NAME="quickpay" VALUE="yes"> <INPUT TYPE="checkbox" NAME="last_on" CHECKED> Search for <B>last name</B>: <INPUT TYPE="text" NAME="last_text"> using search method: <SELECT NAME="last_type"> - <OPTION SELECTED>All + <OPTION>All <OPTION>Fuzzy <OPTION>Substring - <OPTION>Exact + <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 SELECTED>All + <OPTION>All <OPTION>Fuzzy <OPTION>Substring - <OPTION>Exact + <OPTION SELECTED>Exact </SELECT> - <P><INPUT TYPE="submit" VALUE="Search"> Note: Fuzzy searching can take a while. Please be patient. + <P><INPUT TYPE="submit" VALUE="Search"> </FORM> diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index 586f8d991..50d367e1e 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -50,6 +50,7 @@ my $total = 0; my(@cust_main, $sortby, $orderby); if ( $cgi->param('browse') || $cgi->param('otaker_on') + || $cgi->param('agentnum_on') ) { my %search = (); @@ -73,6 +74,9 @@ if ( $cgi->param('browse') 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..."; } @@ -82,22 +86,22 @@ if ( $cgi->param('browse') 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; + 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 @@ -112,6 +116,7 @@ if ( $cgi->param('browse') AND (temp1_$$.count > 0 OR temp2_$$.count = 0 ) "; + } else { $ncancelled = " 0 < ( SELECT COUNT(*) FROM cust_pkg @@ -124,15 +129,32 @@ if ( $cgi->param('browse') 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 ( $ncancelled ) { + if ( $cancelled ) { + $qual .= ' AND ' if $qual; + $qual .= $cancelled; + } elsif ( $ncancelled ) { $qual .= ' AND ' if $qual; $qual .= $ncancelled; } @@ -150,26 +172,27 @@ if ( $cgi->param('browse') $total = $sth->fetchrow_arrayref->[0]; - if ( $ncancelled ) { + my $rqual = $cancelled || $ncancelled; + if ( $rqual ) { if ( %search ) { - $ncancelled = " AND $ncancelled"; + $rqual = " AND $rqual"; } else { - $ncancelled = " WHERE $ncancelled"; + $rqual = " WHERE $rqual"; } } my @just_cust_main; if ( driver_name eq 'mysql' ) { @just_cust_main = qsearch('cust_main', \%search, 'cust_main.*', - ",temp1_$$,temp2_$$ $ncancelled $orderby $limit"); + ",temp1_$$,temp2_$$ $rqual $orderby $limit"); } else { @just_cust_main = qsearch('cust_main', \%search, '', - "$ncancelled $orderby $limit" ); + "$rqual $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; + 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; @@ -213,9 +236,12 @@ if ( $cgi->param('browse') } @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') ); + 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; @@ -277,19 +303,22 @@ if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { } } #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> )!; + + 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+)$/ @@ -457,7 +486,7 @@ 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; + $custnum = $1; [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; } @@ -469,7 +498,9 @@ sub cardsearch { $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'=>'CARD'}), + qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'}) + ]; } sub referralsearch { @@ -636,12 +667,19 @@ sub phonesearch { my $phone = $cgi->param('phone_text'); - #false laziness with Record::ut_phonen, only works with US/CA numbers... + #(no longer really) 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; + 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) @@ -650,7 +688,7 @@ sub phonesearch { for my $field ( @fields ) { push @cust_main, qsearch ( 'cust_main', { $field => { 'op' => 'LIKE', - 'value' => "$phone%" } } ); + 'value' => "%$phone%" } } ); } \@cust_main; diff --git a/httemplate/search/cust_pay.cgi b/httemplate/search/cust_pay.cgi index b5bdf8296..51dd3b340 100755 --- a/httemplate/search/cust_pay.cgi +++ b/httemplate/search/cust_pay.cgi @@ -1,12 +1,73 @@ <% -$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, +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 } ); -my $sortby = \*date_sort; + $sortby = \*date_sort; + +} if (0) { #if ( scalar(@cust_pay) == 1 ) { @@ -16,7 +77,7 @@ if (0) { %> <!-- mason kludge --> <% - idiot("Check # not found."); + idiot("Payment not found."); #exit; } else { my $total = scalar(@cust_pay); @@ -24,9 +85,9 @@ if (0) { %> <!-- mason kludge --> <% - print header("Check # Search Results", menubar( + print header("Payment Search Results", menubar( 'Main Menu', popurl(2) - )), "$total matching check$s found<BR>", &table(), <<END; + )), "$total matching payment$s found<BR>", &table(), <<END; <TR> <TH></TH> <TH>Amount</TH> @@ -37,26 +98,41 @@ if (0) { END my(%saw, $cust_pay); + my $tot_amount = 0; foreach my $cust_pay ( sort $sortby grep(!$saw{$_->paynum}++, @cust_pay) ) { - my($paynum, $custnum, $payinfo, $amount, $date ) = ( + 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, ); - my $pdate = time2str("%b %d %Y", $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>$payinfo</FONT></A></TD> + <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 @@ -79,7 +155,13 @@ END print "</TR>"; } - print <<END; + + $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> diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index abf6eee4c..45420f4c4 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -19,6 +19,8 @@ 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})$/ ) { @@ -27,10 +29,18 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } if ( $cgi->param('ending') && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { - my $ending = str2time($1) + 86400; + 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; @@ -43,8 +53,31 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } else { - my $unconf = ''; - if ( $query eq 'pkgnum' ) { + 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' ) { @@ -87,7 +120,7 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { 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"; + my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; $sth->execute or die "Error executing \"$query\": ". $sth->errstr; @@ -101,12 +134,12 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { 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"; + $qual = " LEFT JOIN temp2_$$ ON cust_pkg.pkgnum = temp2_$$.pkgnum + WHERE temp2_$$.pkgnum IS NOT NULL"; } else { - $unconf = " + $qual = " WHERE 0 < ( SELECT count(*) FROM pkg_svc WHERE pkg_svc.pkgpart = cust_pkg.pkgpart @@ -120,10 +153,10 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } } else { - die "Empty QUERY_STRING!"; + die "Empty or unknown QUERY_STRING!"; } - my $statement = "SELECT COUNT(*) FROM cust_pkg $unconf"; + 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; @@ -131,7 +164,7 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { my $tblname = driver_name eq 'mysql' ? 'cust_pkg.' : ''; @cust_pkg = - qsearch('cust_pkg',{}, '', "$unconf ORDER BY ${tblname}pkgnum $limit" ); + qsearch('cust_pkg',{}, '', "$qual ORDER BY ${tblname}pkgnum $limit" ); if ( driver_name eq 'mysql' ) { $query = "DROP TABLE temp1_$$,temp2_$$;"; @@ -188,6 +221,12 @@ if ( scalar(@cust_pkg) == 1 ) { <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> @@ -197,17 +236,10 @@ if ( scalar(@cust_pkg) == 1 ) { <TH>company</TH> END -if ( defined dbdef->table('cust_main')->column('ship_last') ) { - print <<END; - <TH>(service) name</TH> - <TH>company</TH> -END -} + print '<TH>(service) name</TH><TH>company</TH>' + if defined dbdef->table('cust_main')->column('ship_last'); -print <<END; - <TH COLSPAN=2>Services</TH> - </TR> -END + print '<TH COLSPAN=2>Services</TH></TR>'; my $n1 = '<TR>'; my(%saw,$cust_pkg); @@ -238,6 +270,12 @@ END $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) = ( @@ -263,11 +301,17 @@ END 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> + <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; diff --git a/httemplate/search/cust_pkg.html b/httemplate/search/cust_pkg.html deleted file mode 100755 index bb0a5407c..000000000 --- a/httemplate/search/cust_pkg.html +++ /dev/null @@ -1,24 +0,0 @@ -<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/cust_pkg_report.cgi b/httemplate/search/cust_pkg_report.cgi new file mode 100755 index 000000000..b31674540 --- /dev/null +++ b/httemplate/search/cust_pkg_report.cgi @@ -0,0 +1,63 @@ +<HTML> + <HEAD> + <TITLE>Packages</TITLE> + <LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> + <SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> + </HEAD> + <BODY BGCOLOR="#e8e8e8"> + <H1>Packages</H1> + <FORM ACTION="cust_pkg.cgi" METHOD="post"> + <INPUT TYPE="hidden" NAME="magic" VALUE="bill"> + Return packages with next bill date:<BR><BR> + <TABLE> + <TR> + <TD ALIGN="right">From: </TD> + <TD><INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"><BR><I>m/d/y</I></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); +</SCRIPT> + </TR> + <TR> + <TD ALIGN="right">To: </TD> + <TD><INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor: pointer" TITLE="Select date"><BR><I>m/d/y</I></TD> +<SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); +</SCRIPT> + </TR> +<% my %agent_search = dbdef->table('agent')->column('disabled') + ? ( 'disabled' => '' ) : (); + my @agents = qsearch( 'agent', \%agent_search ); + if ( scalar(@agents) == 1 ) { +%> + <INPUT TYPE="hidden" NAME="agentnum" VALUE="<%= $agents[0]->agentnum %>"> +<% } else { %> + + <TR> + <TD ALIGN="right">Agent: </TD> + <TD><SELECT NAME="agentnum"><OPTION VALUE="">(all) + <% foreach my $agent ( sort { $a->agent cmp $b->agent; } @agents) { %> + <OPTION VALUE="<%= $agent->agentnum %>"><%= $agent->agent %> + <% } %> + </TD> + </TR> +<% } %> + </TABLE> + <BR><INPUT TYPE="submit" VALUE="Get Report"> + + </FORM> + + </BODY> +</HTML> + diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html new file mode 100644 index 000000000..fbedcaa26 --- /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.html b/httemplate/search/report_cc.html index 8653dcc69..595a7b150 100755 --- a/httemplate/search/report_cc.html +++ b/httemplate/search/report_cc.html @@ -1,23 +1,43 @@ <HTML> <HEAD> <TITLE>Credit Card Receipt Report Criteria</TITLE> - </HEAD> - <BODY> - <CENTER> - <H1>Credit Card Receipt Report Criteria</H1> - </CENTER> - <HR> + <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: - from <INPUT TYPE="text" NAME="beginning"> <i>m/d/y</i> - to <INPUT TYPE="text" NAME="ending"> <i>m/d/y</i> + <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> - <P><INPUT TYPE="submit" VALUE="Get Report"> + <BR><INPUT TYPE="submit" VALUE="Get Report"> </FORM> - - <HR> - </BODY> </HTML> diff --git a/httemplate/search/report_credit.html b/httemplate/search/report_credit.html index df9b9581f..11cb32ed8 100755 --- a/httemplate/search/report_credit.html +++ b/httemplate/search/report_credit.html @@ -1,23 +1,43 @@ <HTML> <HEAD> <TITLE>In House Credit Report Criteria</TITLE> - </HEAD> - <BODY> - <CENTER> - <H1>In House Credit Report Criteria</H1> - </CENTER> - <HR> + <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: - from <INPUT TYPE="text" NAME="beginning"> <i>m/d/y</i> - to <INPUT TYPE="text" NAME="ending"> <i>m/d/y</i> + <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> - <P><INPUT TYPE="submit" VALUE="Get Report"> + <BR><INPUT TYPE="submit" VALUE="Get Report"> </FORM> - - <HR> - </BODY> </HTML> diff --git a/httemplate/search/report_cust_pay.html b/httemplate/search/report_cust_pay.html new file mode 100644 index 000000000..1b30685dc --- /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 000000000..eb8bbb55e --- /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 000000000..b85a481be --- /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 index fdd3779a9..ad353a1b3 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -1,19 +1,158 @@ <!-- mason kludge --> <% -my $user = getotaker; + 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 + ) -print header('Current Receivables Report Results'); + ) +END -open (REPORT, "freeside-receivables-report -v $user |"); + 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, -print '<PRE>'; -while(<REPORT>) { - print $_; -} -print '</PRE>'; + 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, -print '</BODY></HTML>'; + 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.html b/httemplate/search/report_tax.html index 7bf681b42..7bc35e3ed 100755 --- a/httemplate/search/report_tax.html +++ b/httemplate/search/report_tax.html @@ -1,23 +1,43 @@ <HTML> <HEAD> <TITLE>Tax Report Criteria</TITLE> - </HEAD> - <BODY> - <CENTER> - <H1>Tax Report Criteria</H1> - </CENTER> - <HR> + <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: - from <INPUT TYPE="text" NAME="beginning"> <i>m/d/y</i> - to <INPUT TYPE="text" NAME="ending"> <i>m/d/y</i> + 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> - <P><INPUT TYPE="submit" VALUE="Get Report"> + <BR><INPUT TYPE="submit" VALUE="Get Report"> </FORM> - - <HR> - </BODY> </HTML> diff --git a/httemplate/search/sql.cgi b/httemplate/search/sql.cgi deleted file mode 100755 index b83ef039f..000000000 --- a/httemplate/search/sql.cgi +++ /dev/null @@ -1,76 +0,0 @@ -<% - -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/sql.html b/httemplate/search/sql.html new file mode 100644 index 000000000..7d7fc0890 --- /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 index 549231d3f..1e4a03d84 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -1,7 +1,5 @@ <% -my $mydomain = ''; - my $conf = new FS::Conf; my $maxrecords = $conf->config('maxsearchrecordsperpage'); @@ -32,7 +30,7 @@ if ( $query =~ /^UN_(.*)$/ ) { WHERE 0 < ( SELECT count(*) FROM cust_svc WHERE cust_svc.svcnum = svc_acct.svcnum - AND ( pkgnum IS NULL OR pkgnum = 0 OR pkgnum = $empty ) + AND ( pkgnum IS NULL OR pkgnum = 0 ) ) "; } @@ -48,14 +46,31 @@ if ( $query eq 'svcnum' ) { $orderby = "ORDER BY ${tblname}username"; } elsif ( $query eq 'uid' ) { $sortby=\*uid_sort; - $orderby = ( $unlinked ? 'AND' : 'WHERE' ). + $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' ) { + +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) @@ -147,14 +162,8 @@ END $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>"; + die "No svc_domain.svcnum record for svc_acct.domsvc: ". + $svc_acct->domsvc; } my($cust_pkg,$cust_main); if ( $cust_svc->pkgnum ) { @@ -216,17 +225,8 @@ END } - 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>'; + print "</TABLE>$pager<BR>". + '</BODY></HTML>'; } diff --git a/httemplate/search/svc_acct_sm.cgi b/httemplate/search/svc_acct_sm.cgi deleted file mode 100755 index 4ee300612..000000000 --- a/httemplate/search/svc_acct_sm.cgi +++ /dev/null @@ -1,84 +0,0 @@ -<% - -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 deleted file mode 100755 index 0719856db..000000000 --- a/httemplate/search/svc_acct_sm.html +++ /dev/null @@ -1,23 +0,0 @@ -<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 index fb372db14..948b1d9ae 100755 --- a/httemplate/search/svc_domain.cgi +++ b/httemplate/search/svc_domain.cgi @@ -1,7 +1,6 @@ <% my $conf = new FS::Conf; -my $mydomain = $conf->config('domain'); my($query)=$cgi->keywords; $query ||= ''; #to avoid use of unitialized value errors @@ -24,6 +23,13 @@ if ( $query eq 'svcnum' ) { '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; @@ -51,8 +57,9 @@ if ( scalar(@svc_domain) == 1 ) { <TR> <TH>Service #</TH> <TH>Domain</TH> - <TH>Mail to<BR><FONT SIZE=-1>(click to view account)</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 @@ -67,78 +74,69 @@ END $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>| + + #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, # ); - #} else { - # $malias=''; + # + # 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>"; #} - - 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 - - + ##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> + <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 @rows; print "</TR>"; } diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi new file mode 100755 index 000000000..10094bc99 --- /dev/null +++ b/httemplate/search/svc_forward.cgi @@ -0,0 +1,79 @@ +<% + +my $conf = new FS::Conf; + +my($query)=$cgi->keywords; +$query ||= ''; #to avoid use of unitialized value errors +my(@svc_forward,$sortby); +if ( $query eq 'svcnum' ) { + $sortby=\*svcnum_sort; + @svc_forward=qsearch('svc_forward',{}); +} else { + eidiot('unimplemented'); +} + +if ( scalar(@svc_forward) == 1 ) { + print $cgi->redirect(popurl(2). "view/svc_forward.cgi?". $svc_forward[0]->svcnum); + #exit; +} elsif ( scalar(@svc_forward) == 0 ) { +%> +<!-- mason kludge --> +<% + eidiot "No matching forwards found!\n"; +} else { +%> +<!-- mason kludge --> +<% + my $total = scalar(@svc_forward); + print header("Mail forward Search Results",''), <<END; + + $total matching mail forwards found + <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> + <TR> + <TH>Service #<BR><FONT SIZE=-1>(click to view forward)</FONT></TH> + <TH>Mail to<BR><FONT SIZE=-1>(click to view account)</FONT></TH> + <TH>Forwards to<BR><FONT SIZE=-1>(click to view account)</FONT></TH> + </TR> +END + + foreach my $svc_forward ( + sort $sortby (@svc_forward) + ) { + my $svcnum = $svc_forward->svcnum; + + my $src = $svc_forward->src; + $src = "<I>(anything)</I>$src" if $src =~ /^@/; + if ( $svc_forward->srcsvc_acct ) { + $src = qq!<A HREF="${p}view/svc_acct.cgi?!. $svc_forward->srcsvc. '">'. + $svc_forward->srcsvc_acct->email. '</A>'; + } + + my $dst = $svc_forward->dst; + if ( $svc_forward->dstsvc_acct ) { + $dst = qq!<A HREF="${p}view/svc_acct.cgi?!. $svc_forward->dstsvc. '">'. + $svc_forward->dstsvc_acct->email. '</A>'; + } + + print <<END; + <TR> + <TD><A HREF="${p}view/svc_forward.cgi?$svcnum">$svcnum</A></TD> + <TD>$src</TD> + <TD>$dst</TD> + </TR> +END + + } + + print <<END; + </TABLE> + </BODY> +</HTML> +END + +} + +sub svcnum_sort { + $a->getfield('svcnum') <=> $b->getfield('svcnum'); +} + +%> |