X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_pkg.cgi;h=12179eacaea28179f8161afa90159bd791979765;hp=d63d2d188348699b621d5e93fbec969ef663a86f;hb=4bf86e5f7f44e77159564804d09e0938a8edc905;hpb=51984ac3d3da3006809c6866fdecd4ad83610731 diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index d63d2d188..12179eaca 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,150 +1,327 @@ <% -# -# $Id: cust_pkg.cgi,v 1.1 2001-07-30 07:36:04 ivan Exp $ -# -# based on search/svc_acct.cgi ivan@sisd.com 98-jul-17 -# -# $Log: cust_pkg.cgi,v $ -# Revision 1.1 2001-07-30 07:36:04 ivan -# templates!!! -# -# Revision 1.11 2000/07/17 16:45:41 ivan -# first shot at invoice browsing and some other cleanups -# -# Revision 1.10 2000/07/17 12:49:29 ivan -# better error message if a package isn't linked to a customer (that shouldn't happen) -# -# Revision 1.9 1999/07/17 10:38:52 ivan -# scott nelson noticed this mod_perl-triggered bug and -# gave me a great bugreport at the last rhythmethod -# -# Revision 1.8 1999/02/09 09:22:57 ivan -# visual and bugfixes -# -# Revision 1.7 1999/02/07 09:59:37 ivan -# more mod_perl fixes, and bugfixes Peter Wemm sent via email -# -# Revision 1.6 1999/01/19 05:14:13 ivan -# for mod_perl: no more top-level my() variables; use vars instead -# also the last s/create/new/; -# -# Revision 1.5 1999/01/18 09:41:38 ivan -# all $cgi->header calls now include ( '-expires' => 'now' ) for mod_perl -# (good idea anyway) -# -# Revision 1.4 1999/01/18 09:22:33 ivan -# changes to track email addresses for email invoicing -# -# Revision 1.3 1998/12/23 03:05:59 ivan -# $cgi->keywords instead of $cgi->query_string -# -# Revision 1.2 1998/12/17 09:41:09 ivan -# s/CGI::(Base|Request)/CGI.pm/; -# - -use strict; -use vars qw ( $cgi @cust_pkg $sortby $query ); -use CGI; -use CGI::Carp qw(fatalsToBrowser); -use FS::UID qw(cgisuidsetup); -use FS::Record qw(qsearch qsearchs); -use FS::CGI qw(header eidiot popurl); -use FS::cust_pkg; -use FS::pkg_svc; -use FS::cust_svc; -use FS::cust_main; - -$cgi = new CGI; -&cgisuidsetup($cgi); - -($query) = $cgi->keywords; -#this tree is a little bit redundant -if ( $query eq 'pkgnum' ) { - $sortby=\*pkgnum_sort; - @cust_pkg=qsearch('cust_pkg',{}); -} 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',{})) { - 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, - })) + +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); + +my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); + +my $limit = ''; +$limit .= "LIMIT $maxrecords" if $maxrecords; + +my $offset = $cgi->param('offset') || 0; +$limit .= " OFFSET $offset" if $offset; + +my $total; + +my($query) = $cgi->keywords; +my $sortby; +my @cust_pkg; + +if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { + $sortby=\*bill_sort; + my $range = ''; + if ( $cgi->param('beginning') + && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { + my $beginning = str2time($1); + $range = " WHERE bill >= $beginning "; + } + if ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { + my $ending = str2time($1) + 86400; + $range .= ( $range ? ' AND ' : ' WHERE ' ). " bill <= $ending "; + } + + #false laziness with below + my $statement = "SELECT COUNT(*) FROM cust_pkg $range"; + warn $statement; + my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; + $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; + + $total = $sth->fetchrow_arrayref->[0]; + + @cust_pkg = qsearch('cust_pkg',{}, '', " $range ORDER BY bill $limit" ); + +} else { + + my $qual = ''; + if ( $query eq 'pkgnum' ) { + $sortby=\*pkgnum_sort; + + } elsif ( $query eq 'SUSP_pkgnum' ) { + + $sortby=\*pkgnum_sort; + + $qual = 'WHERE susp IS NOT NULL AND susp != 0'; + + } 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 + ) ) - { - $flag=1; - last PKG_SVC; - } + "; + } - push @cust_pkg, $cust_pkg if $flag; + + } else { + die "Empty or unknown QUERY_STRING!"; } -} else { - die "Empty 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; + #exit; } elsif ( scalar(@cust_pkg) == 0 ) { #error +%> + +<% eidiot("No packages found"); } else { - my($total)=scalar(@cust_pkg); - print $cgi->header( '-expires' => 'now' ), header('Package Search Results',''), < +%> + +<% + $total ||= scalar(@cust_pkg); + + #begin pager + my $pager = ''; + if ( $total != scalar(@cust_pkg) && $maxrecords ) { + unless ( $offset == 0 ) { + $cgi->param('offset', $offset - $maxrecords); + $pager .= 'Previous '; + } + my $poff; + my $page; + for ( $poff = 0; $poff < $total; $poff += $maxrecords ) { + $page++; + if ( $offset == $poff ) { + $pager .= qq!$page !; + } else { + $cgi->param('offset', $poff); + $pager .= qq!$page !; + } + } + unless ( $offset + $maxrecords > $total ) { + $cgi->param('offset', $offset + $maxrecords); + $pager .= 'Next '; + } + } + #end pager + + print header('Package Search Results',''), + "$total matching packages found

$pager", &table(), < - Package # - Customer # - Contact name - Company - + Package + Setup END + print 'Last
bill' + if defined dbdef->table('cust_pkg')->column('last_bill'); + + print <Next
bill + Susp. + Expire + Cancel + Cust# + (bill) name + company +END + + print '(service) namecompany' + if defined dbdef->table('cust_main')->column('ship_last'); + + print 'Services'; + + my $n1 = ''; 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,$custnum,$name,$company)=( + 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->first : '', + $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, <$pkgnum - $pkg + $setup +END + + print "$last_bill" + if defined dbdef->table('cust_pkg')->column('last_bill'); + print < - $pkgnum + $bill + $susp + $expire + $cancel END if ( $cust_main ) { print <$custnum - $name - $company - + $custnum + $last, $first + $company +END + if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <$ship_last, $ship_first + $ship_company END + } } else { + my $colspan = defined dbdef->table('cust_main')->column('ship_last') + ? 5 : 3; print <WARNING: couldn't find cust_main.custnum $custnum (cust_pkg.pkgnum $pkgnum) - + WARNING: couldn't find cust_main.custnum $custnum (cust_pkg.pkgnum $pkgnum) 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!$label!, + qq!$value!; + $n2=""; + } + + $n1 = ""; + } + print ''; - print < - - -END - exit; + print "$pager"; } @@ -152,4 +329,8 @@ sub pkgnum_sort { $a->getfield('pkgnum') <=> $b->getfield('pkgnum'); } +sub bill_sort { + $a->getfield('bill') <=> $b->getfield('bill'); +} + %>