From 51984ac3d3da3006809c6866fdecd4ad83610731 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 30 Jul 2001 07:36:04 +0000 Subject: templates!!! --- httemplate/search/cust_pkg.cgi | 155 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 155 insertions(+) create mode 100755 httemplate/search/cust_pkg.cgi (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi new file mode 100755 index 000000000..d63d2d188 --- /dev/null +++ b/httemplate/search/cust_pkg.cgi @@ -0,0 +1,155 @@ +<% +# +# $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, + })) + ) + { + $flag=1; + last PKG_SVC; + } + } + push @cust_pkg, $cust_pkg if $flag; + } +} else { + die "Empty QUERY_STRING!"; +} + +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 + eidiot("No packages found"); +} else { + my($total)=scalar(@cust_pkg); + print $cgi->header( '-expires' => 'now' ), header('Package Search Results',''), < + + Package # + Customer # + Contact name + Company + +END + + 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)=( + $cust_pkg->pkgnum, + $cust_pkg->custnum, + $cust_main ? $cust_main->last. ', '. $cust_main->first : '', + $cust_main ? $cust_main->company : '', + ); + my $p = popurl(2); + print < + $pkgnum +END + if ( $cust_main ) { + print <$custnum + $name + $company + +END + } else { + print <WARNING: couldn't find cust_main.custnum $custnum (cust_pkg.pkgnum $pkgnum) + +END + } + } + + print < + + +END + exit; + +} + +sub pkgnum_sort { + $a->getfield('pkgnum') <=> $b->getfield('pkgnum'); +} + +%> -- cgit v1.2.1 From 926e9ed1c29c929e0f8c1b4dc57e02b21cf14598 Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 15 Aug 2001 10:04:53 +0000 Subject: remove (some of the) bad direct exit; calls --- httemplate/search/cust_pkg.cgi | 44 +----------------------------------------- 1 file changed, 1 insertion(+), 43 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index d63d2d188..50028e3e3 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,46 +1,5 @@ <% -# -# $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 ); @@ -144,7 +103,6 @@ END END - exit; } -- cgit v1.2.1 From cb85608dc292b4693ccb80913fc5a1959d6e9afc Mon Sep 17 00:00:00 2001 From: ivan Date: Sun, 19 Aug 2001 08:32:43 +0000 Subject: display bill and service name/company, and services --- httemplate/search/cust_pkg.cgi | 96 ++++++++++++++++++++++++++++++++---------- 1 file changed, 74 insertions(+), 22 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 50028e3e3..2ab6e2b57 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,13 +1,13 @@ <% -# +# use strict; -use vars qw ( $cgi @cust_pkg $sortby $query ); +use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg ); 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::Record qw(qsearch qsearchs dbdef); +use FS::CGI qw(header eidiot popurl table); use FS::cust_pkg; use FS::pkg_svc; use FS::cust_svc; @@ -16,6 +16,8 @@ use FS::cust_main; $cgi = new CGI; &cgisuidsetup($cgi); +my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); + ($query) = $cgi->keywords; #this tree is a little bit redundant if ( $query eq 'pkgnum' ) { @@ -56,47 +58,97 @@ if ( scalar(@cust_pkg) == 1 ) { eidiot("No packages found"); } else { my($total)=scalar(@cust_pkg); - print $cgi->header( '-expires' => 'now' ), header('Package Search Results',''), < + print $cgi->header( '-expires' => 'now' ), + header('Package Search Results',''), + "$total matching packages found
", &table(), < - Package # - Customer # - Contact name - Company + Package + Cust# + (bill) name + company +END + +if ( defined dbdef->table('cust_main')->column('ship_last') ) { + print <(service) name + company +END +} + +print <Services END + 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, $custnum, $last, $first, $company, + $ship_last, $ship_first, $ship_company)=( $cust_pkg->pkgnum, $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 : '', + $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 < - $pkgnum + print $n1, <$pkgnum - $pkg 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 < -- cgit v1.2.1 From 75cbdf6ab6af5539382a63d21c8f4042d53ba6c1 Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 21 Aug 2001 02:03:32 +0000 Subject: deal with databases w/o separate shipping address --- httemplate/search/cust_pkg.cgi | 36 ++++++++++++++++++++---------------- 1 file changed, 20 insertions(+), 16 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 2ab6e2b57..8a0dfd132 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,5 +1,5 @@ <% -# +# use strict; use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg ); @@ -86,27 +86,31 @@ END sort $sortby grep(!$saw{$_->pkgnum}++, @cust_pkg) ) { my($cust_main)=qsearchs('cust_main',{'custnum'=>$cust_pkg->custnum}); - my($pkgnum, $custnum, $last, $first, $company, - $ship_last, $ship_first, $ship_company)=( + my($pkgnum, $custnum, $last, $first, $company) = ( $cust_pkg->pkgnum, $cust_pkg->custnum, $cust_main ? $cust_main->last : '', $cust_main ? $cust_main->first : '', $cust_main ? $cust_main->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($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 } ); -- cgit v1.2.1 From 91387f8f489e561deaf1de052d80ef800a4970a3 Mon Sep 17 00:00:00 2001 From: ivan Date: Fri, 26 Oct 2001 10:24:56 +0000 Subject: cache foo *sigh* --- httemplate/search/cust_pkg.cgi | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 8a0dfd132..f7cb50674 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,5 +1,5 @@ <% -# +# use strict; use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg ); @@ -58,7 +58,7 @@ if ( scalar(@cust_pkg) == 1 ) { eidiot("No packages found"); } else { my($total)=scalar(@cust_pkg); - print $cgi->header( '-expires' => 'now' ), + print $cgi->header( @FS::CGI::header ), header('Package Search Results',''), "$total matching packages found
", &table(), < -- cgit v1.2.1 From c14a267229fc0d6f2d2afdebab3bd34d825df2ef Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 30 Oct 2001 14:54:07 +0000 Subject: get rid of header foo in individual templates --- httemplate/search/cust_pkg.cgi | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index f7cb50674..93ce7ae29 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,5 +1,5 @@ <% -# +# use strict; use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg ); @@ -58,8 +58,7 @@ if ( scalar(@cust_pkg) == 1 ) { eidiot("No packages found"); } else { my($total)=scalar(@cust_pkg); - print $cgi->header( @FS::CGI::header ), - header('Package Search Results',''), + print header('Package Search Results',''), "$total matching packages found
", &table(), < Package -- cgit v1.2.1 From 63ec39a8c2a32b5ac325c4973945a26398678347 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 3 Dec 2001 10:59:25 +0000 Subject: paged package browse --- httemplate/search/cust_pkg.cgi | 127 ++++++++++++++++++++++++++++++----------- 1 file changed, 95 insertions(+), 32 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 93ce7ae29..e862cefab 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,11 +1,13 @@ <% -# +# use strict; -use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg ); +use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg + $conf $maxrecords $limit $offset ); use CGI; use CGI::Carp qw(fatalsToBrowser); -use FS::UID qw(cgisuidsetup); +use FS::UID qw(dbh cgisuidsetup); +use FS::Conf; use FS::Record qw(qsearch qsearchs dbdef); use FS::CGI qw(header eidiot popurl table); use FS::cust_pkg; @@ -16,40 +18,79 @@ use FS::cust_main; $cgi = new CGI; &cgisuidsetup($cgi); +$conf = new FS::Conf; +$maxrecords = $conf->config('maxsearchrecordsperpage'); + my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); +$limit = ''; +$limit .= "LIMIT $maxrecords" if $maxrecords; + +$offset = $cgi->param('offset') || 0; +$limit .= " OFFSET $offset" if $offset; + +my $total; + ($query) = $cgi->keywords; +my $unconf = ''; #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, - })) - ) - { - $flag=1; - last PKG_SVC; - } - } - push @cust_pkg, $cust_pkg if $flag; - } + + $unconf = " + WHERE 0 < + ( SELECT count(*) FROM pkg_svc + WHERE pkg_svc.pkgpart = cust_pkg.pkgpart + AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc + WHERE cust_svc.pkgnum = cust_pkg.pkgnum + AND cust_svc.svcpart = pkg_svc.svcpart + ) + ) + "; + + #@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; + #} + } else { die "Empty QUERY_STRING!"; } +my $statement = "SELECT COUNT(*) FROM cust_pkg $unconf"; +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]; + +@cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); + + if ( scalar(@cust_pkg) == 1 ) { my($pkgnum)=$cust_pkg[0]->pkgnum; print $cgi->redirect(popurl(2). "view/cust_pkg.cgi?$pkgnum"); @@ -57,9 +98,35 @@ if ( scalar(@cust_pkg) == 1 ) { } elsif ( scalar(@cust_pkg) == 0 ) { #error eidiot("No packages found"); } else { - my($total)=scalar(@cust_pkg); + $total ||= scalar(@cust_pkg); + + 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 '; + } + } + print header('Package Search Results',''), - "$total matching packages found
", &table(), <
$pager", &table(), < Package Cust# @@ -153,11 +220,7 @@ END } print ''; - print < - - -END + print "$pager"; } -- cgit v1.2.1 From 301ef098a56e1fc011d26d1f108a48e99ad701f4 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 3 Dec 2001 11:33:19 +0000 Subject: paged service browse!! --- httemplate/search/cust_pkg.cgi | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index e862cefab..9f13dbb33 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,5 +1,5 @@ <% -# +# use strict; use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg @@ -31,13 +31,11 @@ $limit .= " OFFSET $offset" if $offset; my $total; -($query) = $cgi->keywords; my $unconf = ''; -#this tree is a little bit redundant +($query) = $cgi->keywords; if ( $query eq 'pkgnum' ) { $sortby=\*pkgnum_sort; - } elsif ( $query eq 'APKG_pkgnum' ) { $sortby=\*pkgnum_sort; @@ -86,7 +84,7 @@ 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]; +$total = $sth->fetchrow_arrayref->[0]; @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); @@ -100,6 +98,7 @@ if ( scalar(@cust_pkg) == 1 ) { } else { $total ||= scalar(@cust_pkg); + #begin pager my $pager = ''; if ( $total != scalar(@cust_pkg) && $maxrecords ) { unless ( $offset == 0 ) { @@ -124,6 +123,7 @@ if ( scalar(@cust_pkg) == 1 ) { '">Next '; } } + #end pager print header('Package Search Results',''), "$total matching packages found

$pager", &table(), < Date: Tue, 18 Dec 2001 06:38:25 +0000 Subject: show dates on package browse --- httemplate/search/cust_pkg.cgi | 12 +++++++++++- 1 file changed, 11 insertions(+), 1 deletion(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 9f13dbb33..7426276bd 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,5 +1,5 @@ <% -# +# use strict; use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg @@ -129,6 +129,11 @@ if ( scalar(@cust_pkg) == 1 ) { "$total matching packages found

$pager", &table(), < Package + Setup + Next
bill + Susp. + Expire + Cancel Cust# (bill) name company @@ -154,6 +159,11 @@ END my($cust_main)=qsearchs('cust_main',{'custnum'=>$cust_pkg->custnum}); my($pkgnum, $custnum, $last, $first, $company) = ( $cust_pkg->pkgnum, + time2str("%D", $cust_pkg->setup ), + time2str("%D", $cust_pkg->bill ), + time2str("%D", $cust_pkg->susp ), + time2str("%D", $cust_pkg->expire ), + time2str("%D", $cust_pkg->cancel ), $cust_pkg->custnum, $cust_main ? $cust_main->last : '', $cust_main ? $cust_main->first : '', -- cgit v1.2.1 From 428301a8083e09832ac414d2b89db26ba6ee5f4e Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 18 Dec 2001 06:45:32 +0000 Subject: oops. *sigh* --- httemplate/search/cust_pkg.cgi | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 7426276bd..070871e02 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,5 +1,5 @@ <% -# +# use strict; use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg @@ -159,11 +159,11 @@ END my($cust_main)=qsearchs('cust_main',{'custnum'=>$cust_pkg->custnum}); my($pkgnum, $custnum, $last, $first, $company) = ( $cust_pkg->pkgnum, - time2str("%D", $cust_pkg->setup ), - time2str("%D", $cust_pkg->bill ), - time2str("%D", $cust_pkg->susp ), - time2str("%D", $cust_pkg->expire ), - time2str("%D", $cust_pkg->cancel ), + time2str("%D", $cust_pkg->getfield('setup') ), + time2str("%D", $cust_pkg->getfield('bill') ), + time2str("%D", $cust_pkg->getfield('susp') ), + time2str("%D", $cust_pkg->getfield('expire') ), + time2str("%D", $cust_pkg->getfield('cancel') ), $cust_pkg->custnum, $cust_main ? $cust_main->last : '', $cust_main ? $cust_main->first : '', -- cgit v1.2.1 From a44aaabe9b21f0e474f7317852235600afcc5733 Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 18 Dec 2001 07:08:16 +0000 Subject: working dates on package browse --- httemplate/search/cust_pkg.cgi | 31 ++++++++++++++++++++++++------- 1 file changed, 24 insertions(+), 7 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 070871e02..137dc5f19 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,5 +1,5 @@ <% -# +# use strict; use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg @@ -157,13 +157,25 @@ END sort $sortby grep(!$saw{$_->pkgnum}++, @cust_pkg) ) { my($cust_main)=qsearchs('cust_main',{'custnum'=>$cust_pkg->custnum}); - my($pkgnum, $custnum, $last, $first, $company) = ( + my($pkgnum, $setup, $bill, $susp, $expire, $cancel, + $custnum, $last, $first, $company + ) = ( $cust_pkg->pkgnum, - time2str("%D", $cust_pkg->getfield('setup') ), - time2str("%D", $cust_pkg->getfield('bill') ), - time2str("%D", $cust_pkg->getfield('susp') ), - time2str("%D", $cust_pkg->getfield('expire') ), - time2str("%D", $cust_pkg->getfield('cancel') ), + $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 : '', @@ -194,6 +206,11 @@ END my $p = popurl(2); print $n1, <$pkgnum - $pkg + $setup + $bill + $susp + $expire + $cancel END if ( $cust_main ) { print < Date: Tue, 18 Dec 2001 07:12:00 +0000 Subject: really working dates on package browse. ouch. --- httemplate/search/cust_pkg.cgi | 13 ++++++------- 1 file changed, 6 insertions(+), 7 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 137dc5f19..dabcf46fb 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,5 +1,5 @@ <% -# +# use strict; use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg @@ -158,23 +158,22 @@ END ) { my($cust_main)=qsearchs('cust_main',{'custnum'=>$cust_pkg->custnum}); my($pkgnum, $setup, $bill, $susp, $expire, $cancel, - $custnum, $last, $first, $company - ) = ( + $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') ), + ? time2str("%D", $cust_pkg->getfield('bill') ) : '', $cust_pkg->getfield('susp') - ? time2str("%D", $cust_pkg->getfield('susp') ), + ? time2str("%D", $cust_pkg->getfield('susp') ) : '', $cust_pkg->getfield('expire') - ? time2str("%D", $cust_pkg->getfield('expire') ), + ? time2str("%D", $cust_pkg->getfield('expire') ) : '', $cust_pkg->getfield('cancel') - ? time2str("%D", $cust_pkg->getfield('cancel') ), + ? time2str("%D", $cust_pkg->getfield('cancel') ) : '', $cust_pkg->custnum, $cust_main ? $cust_main->last : '', -- cgit v1.2.1 From 1fd6d8cf5d7854860ef4fd10ed89828e0c04ec39 Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 30 Jan 2002 14:18:09 +0000 Subject: remove use Module; and $cgi = new CGI; &cgisuidsetup(); from all templates. should work better under Mason. --- httemplate/search/cust_pkg.cgi | 32 ++++++++------------------------ 1 file changed, 8 insertions(+), 24 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index dabcf46fb..9705bd658 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,38 +1,22 @@ + <% -# -use strict; -use vars qw ( $cgi @cust_pkg $sortby $query %part_pkg - $conf $maxrecords $limit $offset ); -use CGI; -use CGI::Carp qw(fatalsToBrowser); -use FS::UID qw(dbh cgisuidsetup); -use FS::Conf; -use FS::Record qw(qsearch qsearchs dbdef); -use FS::CGI qw(header eidiot popurl table); -use FS::cust_pkg; -use FS::pkg_svc; -use FS::cust_svc; -use FS::cust_main; - -$cgi = new CGI; -&cgisuidsetup($cgi); - -$conf = new FS::Conf; -$maxrecords = $conf->config('maxsearchrecordsperpage'); +my $conf = new FS::Conf; +my $maxrecords = $conf->config('maxsearchrecordsperpage'); my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); -$limit = ''; +my $limit = ''; $limit .= "LIMIT $maxrecords" if $maxrecords; -$offset = $cgi->param('offset') || 0; +my $offset = $cgi->param('offset') || 0; $limit .= " OFFSET $offset" if $offset; my $total; my $unconf = ''; -($query) = $cgi->keywords; +my($query) = $cgi->keywords; +my $sortby; if ( $query eq 'pkgnum' ) { $sortby=\*pkgnum_sort; @@ -86,7 +70,7 @@ $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; $total = $sth->fetchrow_arrayref->[0]; -@cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); +my @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); if ( scalar(@cust_pkg) == 1 ) { -- cgit v1.2.1 From 0bdec843e4a9bb7f947c9ba980a40f7bf37020fb Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 9 Feb 2002 18:24:02 +0000 Subject: no more exit() in templates --- httemplate/search/cust_pkg.cgi | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 9705bd658..b2199e9dc 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,4 +1,4 @@ - + <% my $conf = new FS::Conf; @@ -76,7 +76,7 @@ my @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); 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 { -- cgit v1.2.1 From 22a35047ecdffff80110e06cc08fc84f9ddba9b0 Mon Sep 17 00:00:00 2001 From: ivan Date: Sun, 10 Feb 2002 13:21:31 +0000 Subject: removed from all files to fix any redirects, whew Mason handler.pl overrides CGI::redirect fixed strict; problems in edit/part_pkg.cgi & edit/process/part_pkg.cgi --- httemplate/search/cust_pkg.cgi | 1 - 1 file changed, 1 deletion(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index b2199e9dc..6b25fec03 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,4 +1,3 @@ - <% my $conf = new FS::Conf; -- cgit v1.2.1 From a4c96748eb6eab29a70f3a944c6520283a635c78 Mon Sep 17 00:00:00 2001 From: ivan Date: Sun, 10 Feb 2002 16:05:22 +0000 Subject: *finally* seems to be working under Mason. sheesh. *finally* seems to be working under Mason. sheesh. *finally* seems to be working under Mason. sheesh. *finally* seems to be working under Mason. sheesh. *finally* seems to be working under Mason. sheesh. *finally* seems to be working under Mason. sheesh. *finally* seems to be working under Mason. sheesh. *finally* seems to be working under Mason. sheesh. --- httemplate/search/cust_pkg.cgi | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 6b25fec03..54e02ce9d 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -77,8 +77,14 @@ if ( scalar(@cust_pkg) == 1 ) { print $cgi->redirect(popurl(2). "view/cust_pkg.cgi?$pkgnum"); #exit; } elsif ( scalar(@cust_pkg) == 0 ) { #error +%> + +<% eidiot("No packages found"); } else { +%> + +<% $total ||= scalar(@cust_pkg); #begin pager -- cgit v1.2.1 From b697a86ff35212aaf5ab8cf06d05ab3f7c619d20 Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 4 May 2002 00:32:21 +0000 Subject: lilunixbtch: trying to pull accounts based on next billdate tofu_beast420: hmm a report ordered by next bill date? tofu_beast420: i don't know how you'd do that per _customer_ since a customer could have lots of packages, but you could do a per-package report maybe? --- httemplate/search/cust_pkg.cgi | 135 +++++++++++++++++++++++++---------------- 1 file changed, 84 insertions(+), 51 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 54e02ce9d..dee2fbdab 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -13,64 +13,93 @@ $limit .= " OFFSET $offset" if $offset; my $total; -my $unconf = ''; my($query) = $cgi->keywords; my $sortby; -if ( $query eq 'pkgnum' ) { - $sortby=\*pkgnum_sort; - -} elsif ( $query eq 'APKG_pkgnum' ) { - - $sortby=\*pkgnum_sort; - - $unconf = " - WHERE 0 < - ( SELECT count(*) FROM pkg_svc - WHERE pkg_svc.pkgpart = cust_pkg.pkgpart - AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc - WHERE cust_svc.pkgnum = cust_pkg.pkgnum - AND cust_svc.svcpart = pkg_svc.svcpart - ) - ) - "; - - #@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; - #} +my @cust_pkg; + +if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { + $sortby=\*bill_sort; + my($beginning, $ending) = (0, 0); + my $range = ''; + if ( $cgi->param('beginning') + && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { + my $beginning = str2time($1); + $range = " WHERE bill >= $beginning "; + } elsif ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $ending = str2time($1) + 86400; + $range = ( $range ? ' AND ' : ' WHERE ' ). " bill <= $ending "; + } + + #false laziness with below + my $statement = "SELECT COUNT(*) FROM cust_pkg $range"; + my $sth = dbh->prepare($statement) + or die dbh->errstr. " doing $statement"; + $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; -} else { - die "Empty QUERY_STRING!"; -} + $total = $sth->fetchrow_arrayref->[0]; + + @cust_pkg = qsearch('cust_pkg',{}, '', " $range ORDER BY bill $limit" ); -my $statement = "SELECT COUNT(*) FROM cust_pkg $unconf"; -my $sth = dbh->prepare($statement) - or die dbh->errstr. " doing $statement"; -$sth->execute or die "Error executing \"$statement\": ". $sth->errstr; +} else { -$total = $sth->fetchrow_arrayref->[0]; + my $unconf = ''; + if ( $query eq 'pkgnum' ) { + $sortby=\*pkgnum_sort; -my @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); + } elsif ( $query eq 'APKG_pkgnum' ) { + + $sortby=\*pkgnum_sort; + + $unconf = " + WHERE 0 < + ( SELECT count(*) FROM pkg_svc + WHERE pkg_svc.pkgpart = cust_pkg.pkgpart + AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc + WHERE cust_svc.pkgnum = cust_pkg.pkgnum + AND cust_svc.svcpart = pkg_svc.svcpart + ) + ) + "; + + #@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; + #} + + } else { + die "Empty QUERY_STRING!"; + } + + my $statement = "SELECT COUNT(*) FROM cust_pkg $unconf"; + 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]; + + @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); +} if ( scalar(@cust_pkg) == 1 ) { my($pkgnum)=$cust_pkg[0]->pkgnum; @@ -243,4 +272,8 @@ sub pkgnum_sort { $a->getfield('pkgnum') <=> $b->getfield('pkgnum'); } +sub bill_sort { + $a->getfield('bill') <=> $b->getfield('bill'); +} + %> -- cgit v1.2.1 From 1c773e927ea1bc90ec94873f801d9b3edb57acd7 Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 4 May 2002 00:59:08 +0000 Subject: fix ranges on cust_pkg search --- httemplate/search/cust_pkg.cgi | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index dee2fbdab..8c5d35be5 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -28,7 +28,7 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } elsif ( $cgi->param('ending') && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { $ending = str2time($1) + 86400; - $range = ( $range ? ' AND ' : ' WHERE ' ). " bill <= $ending "; + $range .= ( $range ? ' AND ' : ' WHERE ' ). " bill <= $ending "; } #false laziness with below -- cgit v1.2.1 From 8af9903c96ca6a3d78e7779cbc4c5e773aaf3ae0 Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 4 May 2002 01:11:04 +0000 Subject: add package search by next bill date to main menu --- httemplate/search/cust_pkg.cgi | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 8c5d35be5..ec1bda900 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -19,20 +19,21 @@ my @cust_pkg; if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $sortby=\*bill_sort; - my($beginning, $ending) = (0, 0); my $range = ''; if ( $cgi->param('beginning') && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { my $beginning = str2time($1); $range = " WHERE bill >= $beginning "; - } elsif ( $cgi->param('ending') + } + if ( $cgi->param('ending') && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { - $ending = str2time($1) + 86400; + 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. " doing $statement"; $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; -- cgit v1.2.1 From 7fc18b419e3cec20eb7c898017673b40a7424cde Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 4 Jun 2002 14:02:18 +0000 Subject: mysql support! thanks to Donald Greer for the SQL and Dale Hege for the patches --- httemplate/search/cust_pkg.cgi | 74 +++++++++++++++++++++++++++++++++--------- 1 file changed, 58 insertions(+), 16 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index ec1bda900..7dfacf189 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -34,8 +34,7 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { #false laziness with below my $statement = "SELECT COUNT(*) FROM cust_pkg $range"; warn $statement; - my $sth = dbh->prepare($statement) - or die dbh->errstr. " doing $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]; @@ -52,17 +51,6 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $sortby=\*pkgnum_sort; - $unconf = " - WHERE 0 < - ( SELECT count(*) FROM pkg_svc - WHERE pkg_svc.pkgpart = cust_pkg.pkgpart - AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc - WHERE cust_svc.pkgnum = cust_pkg.pkgnum - AND cust_svc.svcpart = pkg_svc.svcpart - ) - ) - "; - #@cust_pkg=(); ##perhaps this should go in cust_pkg as a qsearch-like constructor? #my($cust_pkg); @@ -86,20 +74,74 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { # } # 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.svcnum"; + $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + + $query = "CREATE TEMPORARY TABLE temp2_$$ TYPE=MYISAM + SELECT cust_pkg.pkgnum FROM cust_pkg + LEFT JOIN pkg_svc ON (cust_pkg.pkgpart=pkg_svc.pkgpart) + LEFT JOIN temp1_$$ ON (cust_pkg.pkgnum = temp1_$$.pkgnum + AND pkg_svc.svcpart=temp1_$$.svcpart) + WHERE ( pkg_svc.quantity > temp1_$$.count + OR temp1_$$.pkgnum IS NULL ) + AND pkg_svc.quantity != 0;"; + $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; + $sth->execute or die "Error executing \"$query\": ". $sth->errstr; + $unconf = " LEFT JOIN temp2_$$ ON cust_pkg.pkgnum = temp2_$$.pkgnum + WHERE temp2_$$.pkgnum IS NOT NULL"; + + } else { + + $unconf = " + WHERE 0 < + ( SELECT count(*) FROM pkg_svc + WHERE pkg_svc.pkgpart = cust_pkg.pkgpart + AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc + WHERE cust_svc.pkgnum = cust_pkg.pkgnum + AND cust_svc.svcpart = pkg_svc.svcpart + ) + ) + "; + + } } else { die "Empty QUERY_STRING!"; } my $statement = "SELECT COUNT(*) FROM cust_pkg $unconf"; - my $sth = dbh->prepare($statement) - or die dbh->errstr. " doing $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]; - + + #if ( driver_name eq 'mysql' ) { #remove ORDER BY for mysql? hua? + # @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf $limit" ); + #} else { + # @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); + #} @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY 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 ) { -- cgit v1.2.1 From c9b608b9c0df8921d30055f18c23fd2d030afb25 Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 5 Jun 2002 22:46:26 +0000 Subject: more mysql goodness, thanks dale --- httemplate/search/cust_pkg.cgi | 11 ++++------- 1 file changed, 4 insertions(+), 7 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 7dfacf189..abf6eee4c 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -86,7 +86,7 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { 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.svcnum"; + GROUP BY cust_svc.pkgnum,cust_svc.svcpart"; $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query"; $sth->execute or die "Error executing \"$query\": ". $sth->errstr; @@ -129,12 +129,9 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $total = $sth->fetchrow_arrayref->[0]; - #if ( driver_name eq 'mysql' ) { #remove ORDER BY for mysql? hua? - # @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf $limit" ); - #} else { - # @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); - #} - @cust_pkg = qsearch('cust_pkg',{}, '', "$unconf ORDER BY pkgnum $limit" ); + my $tblname = driver_name eq 'mysql' ? 'cust_pkg.' : ''; + @cust_pkg = + qsearch('cust_pkg',{}, '', "$unconf ORDER BY ${tblname}pkgnum $limit" ); if ( driver_name eq 'mysql' ) { $query = "DROP TABLE temp1_$$,temp2_$$;"; -- cgit v1.2.1 From 068eda26651713cf318f69908b82d7d34f9716b4 Mon Sep 17 00:00:00 2001 From: ivan Date: Thu, 29 Aug 2002 14:11:00 +0000 Subject: UI ROWSPAN fix --- httemplate/search/cust_pkg.cgi | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index abf6eee4c..5f0782b8c 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -263,11 +263,11 @@ END my $p = popurl(2); print $n1, <$pkgnum - $pkg - $setup - $bill - $susp - $expire - $cancel + $setup + $bill + $susp + $expire + $cancel END if ( $cust_main ) { print < Date: Tue, 8 Oct 2002 11:10:51 +0000 Subject: add suspended package browse (closes: Bug#467) --- httemplate/search/cust_pkg.cgi | 20 +++++++++++++------- 1 file changed, 13 insertions(+), 7 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 5f0782b8c..78a5bb3bb 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -43,10 +43,16 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } else { - my $unconf = ''; + 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; @@ -101,12 +107,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 +126,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 +137,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_$$;"; -- cgit v1.2.1 From f3b8b72d2a07683b2deb2774f29407e25e725b5a Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 1 Apr 2003 01:22:25 +0000 Subject: correct last_bill problems with $0 invoice (non-existant) edge cases --- httemplate/search/cust_pkg.cgi | 31 +++++++++++++++++++++---------- 1 file changed, 21 insertions(+), 10 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 78a5bb3bb..3cc520fa8 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -194,6 +194,12 @@ if ( scalar(@cust_pkg) == 1 ) { Package Setup +END + + print 'Next
bill' + if defined dbdef->table('cust_pkg')->column('last_bill'); + + print <Next
bill Susp. Expire @@ -203,17 +209,10 @@ if ( scalar(@cust_pkg) == 1 ) { company END -if ( defined dbdef->table('cust_main')->column('ship_last') ) { - print <(service) name - company -END -} + print '(service) namecompany' + if defined dbdef->table('cust_main')->column('ship_last'); -print <Services - -END + print 'Services'; my $n1 = ''; my(%saw,$cust_pkg); @@ -244,6 +243,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) = ( @@ -270,6 +275,12 @@ END print $n1, <$pkgnum - $pkg $setup +END + + print "$last_bill" + if defined dbdef->table('cust_pkg')->column('last_bill'); + + print <$bill $susp $expire -- cgit v1.2.1 From bace32bf9341c78d1a068b302dbba031c79c128d Mon Sep 17 00:00:00 2001 From: ivan Date: Sun, 11 May 2003 22:53:49 +0000 Subject: patch from Richard Siddall for strict vars --- httemplate/search/cust_pkg.cgi | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 3cc520fa8..538edf3f1 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -93,7 +93,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; -- cgit v1.2.1 From 4bf86e5f7f44e77159564804d09e0938a8edc905 Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 4 Jun 2003 12:44:57 +0000 Subject: fix last bill label --- httemplate/search/cust_pkg.cgi | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 538edf3f1..12179eaca 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -196,7 +196,7 @@ if ( scalar(@cust_pkg) == 1 ) { Setup END - print 'Next
bill' + print 'Last
bill' if defined dbdef->table('cust_pkg')->column('last_bill'); print < Date: Fri, 11 Jul 2003 15:23:33 +0000 Subject: payments report for inventivemedia / hpnx --- httemplate/search/cust_pkg.cgi | 2 ++ 1 file changed, 2 insertions(+) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 12179eaca..603b5654c 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})$/ ) { -- cgit v1.2.1 From 7c319f1608fd3459c992eda7743696fc745fccf1 Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 12 Jul 2003 11:09:33 +0000 Subject: - better description of payment search - some clarification of services/packages vs. service/package definitions in browse/part_{svc,pkg}.cgi - package definition report by # of active customer packages (with links to customer package listing) --- httemplate/search/cust_pkg.cgi | 14 +++++++++++++- 1 file changed, 13 insertions(+), 1 deletion(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 603b5654c..8b2fd0ca0 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -46,7 +46,19 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } else { my $qual = ''; - if ( $query eq 'pkgnum' ) { + if ( $cgi->param('magic') && $cgi->param('magic') eq 'active' ) { + + $qual = 'WHERE ( susp IS NULL OR susp = 0 )'. + ' AND ( cancel IS NULL OR cancel = 0)'; + + $sortby = \*pkgnum_sort; + + if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { + $qual .= " AND pkgpart = $1"; + } + + } elsif ( $query eq 'pkgnum' ) { + $sortby=\*pkgnum_sort; } elsif ( $query eq 'SUSP_pkgnum' ) { -- cgit v1.2.1 From 32ba5c8d264479a302c1524165d708ce11243d69 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 15 Sep 2003 21:33:03 +0000 Subject: package search skips cancelled packages when searching by date range --- httemplate/search/cust_pkg.cgi | 2 ++ 1 file changed, 2 insertions(+) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 8b2fd0ca0..f735fbf42 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -33,6 +33,8 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $range .= ( $range ? ' AND ' : ' WHERE ' ). " bill <= $ending "; } + $range .= ( $range ? 'AND ' : ' WHERE ' ). '( cancel IS NULL OR cancel = 0 )'; + #false laziness with below my $statement = "SELECT COUNT(*) FROM cust_pkg $range"; warn $statement; -- cgit v1.2.1 From de38e11cb5a40c78ef5543f59f6ce968e7fd613b Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 3 Nov 2003 05:48:14 +0000 Subject: add suspended/canceled package browse --- httemplate/search/cust_pkg.cgi | 17 ++++++++++++++--- 1 file changed, 14 insertions(+), 3 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index f735fbf42..1d54a53f6 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -48,10 +48,21 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } else { my $qual = ''; - if ( $cgi->param('magic') && $cgi->param('magic') eq 'active' ) { + if ( $cgi->param('magic') && + $cgi->param('magic') =~ /^(active|suspended|canceled)$/ + ) { - $qual = 'WHERE ( susp IS NULL OR susp = 0 )'. - ' AND ( cancel IS NULL OR cancel = 0)'; + 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; -- cgit v1.2.1 From d9c51bdc266798d01247e0ff72f6d64aefb91a2e Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 3 Nov 2003 05:57:28 +0000 Subject: add suspended/canceled browse, fix the old suspended browse --- httemplate/search/cust_pkg.cgi | 10 ++-------- 1 file changed, 2 insertions(+), 8 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 1d54a53f6..3c3e17864 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -56,10 +56,10 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $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'. + $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'; + $qual = 'WHERE cancel IS NOT NULL AND cancel != 0'; } else { die "guru meditation #420"; } @@ -74,12 +74,6 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $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; -- cgit v1.2.1 From abc64a997eeacdc95988296889754d708aa97cad Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 27 Dec 2003 08:23:38 +0000 Subject: package reports by agent --- httemplate/search/cust_pkg.cgi | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 3c3e17864..c2f6272c4 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -35,6 +35,12 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $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; -- cgit v1.2.1 From 0c0cb89fbf23c575cab2cac5519b6051288a7d3f Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 21 Jan 2004 00:04:07 +0000 Subject: don't include the _next_ day, just the full ending day --- httemplate/search/cust_pkg.cgi | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index c2f6272c4..45420f4c4 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -29,7 +29,7 @@ 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 "; } -- cgit v1.2.1 From e3f327b2af9e6a591bae41d315fda731ec7f5128 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 22 Mar 2004 11:05:58 +0000 Subject: remove everything that links to package view also, closes: Bug#569 --- httemplate/search/cust_pkg.cgi | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 45420f4c4..6d26317e0 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -175,8 +175,8 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } if ( scalar(@cust_pkg) == 1 ) { - my($pkgnum)=$cust_pkg[0]->pkgnum; - print $cgi->redirect(popurl(2). "view/cust_pkg.cgi?$pkgnum"); + print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum. + "#cust_pkg". $cust_pkg[0]->pkgnum ); #exit; } elsif ( scalar(@cust_pkg) == 0 ) { #error %> @@ -300,7 +300,7 @@ END my $rowspan = scalar(@cust_svc) || 1; my $p = popurl(2); print $n1, <$pkgnum - $pkg + $pkgnum - $pkg $setup END -- cgit v1.2.1 From 6ed5d51b3a72e2935dd5d084c9e24567150b03ca Mon Sep 17 00:00:00 2001 From: ivan Date: Sun, 7 Aug 2005 00:40:02 +0000 Subject: move cust_pkg search to new template, add active/suspended/cancelled customer packages to agent browse --- httemplate/search/cust_pkg.cgi | 517 ++++++++++++++++------------------------- 1 file changed, 194 insertions(+), 323 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 6d26317e0..5da4d82fb 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,363 +1,234 @@ <% -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 "; - } +my $orderby; +my @where; +my $cjoin = ''; - $range .= ( $range ? 'AND ' : ' WHERE ' ). '( cancel IS NULL OR cancel = 0 )'; +if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { + $cjoin = "LEFT JOIN cust_main USING ( custnum )"; + push @where, + "agentnum = $1"; +} - if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { - $range .= ( $range ? 'AND ' : ' WHERE ' ). - "$1 = ( SELECT agentnum FROM cust_main". - " WHERE cust_main.custnum = cust_pkg.custnum )"; - } +if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { + $orderby = 'ORDER BY bill'; - #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" ); + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + push @where, + "bill >= $beginning ", + "bill <= $ending", + '( cancel IS NULL OR cancel = 0 )'; } else { - my $qual = ''; if ( $cgi->param('magic') && - $cgi->param('magic') =~ /^(active|suspended|canceled)$/ + $cgi->param('magic') =~ /^(active|suspended|cancell?ed)$/ ) { + $orderby = 'ORDER BY pkgnum'; + if ( $cgi->param('magic') eq 'active' ) { - $qual = 'WHERE ( susp IS NULL OR susp = 0 )'. - ' AND ( cancel IS NULL OR cancel = 0)'; + + #push @where, + # '( susp IS NULL OR susp = 0 )', + # '( cancel IS NULL OR cancel = 0)'; + push @where, FS::cust_pkg->active_sql(); + } 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'; + + push @where, + 'susp IS NOT NULL', + 'susp != 0', + '( cancel IS NULL OR cancel = 0)'; + + } elsif ( $cgi->param('magic') =~ /^cancell?ed$/ ) { + + push @where, + 'cancel IS NOT NULL', + 'cancel != 0'; + } else { die "guru meditation #420"; } - $sortby = \*pkgnum_sort; - if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { - $qual .= " AND pkgpart = $1"; + push @where, "pkgpart = $1"; } } elsif ( $query eq 'pkgnum' ) { - $sortby=\*pkgnum_sort; + $orderby = 'ORDER BY pkgnum'; } 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; + $orderby = 'ORDER BY pkgnum'; - $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 - ) - ) - "; - - } + push @where, '0 < ( + SELECT count(*) FROM pkg_svc + WHERE pkg_svc.pkgpart = cust_pkg.pkgpart + AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc + WHERE cust_svc.pkgnum = cust_pkg.pkgnum + AND cust_svc.svcpart = pkg_svc.svcpart + ) + )'; } else { die "Empty or unknown QUERY_STRING!"; } - - my $statement = "SELECT COUNT(*) FROM cust_pkg $qual"; - my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; - $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; - - $total = $sth->fetchrow_arrayref->[0]; - - my $tblname = driver_name eq 'mysql' ? 'cust_pkg.' : ''; - @cust_pkg = - qsearch('cust_pkg',{}, '', "$qual ORDER BY ${tblname}pkgnum $limit" ); - - if ( driver_name eq 'mysql' ) { - $query = "DROP TABLE temp1_$$,temp2_$$;"; - my $sth = dbh->prepare($query) or die dbh->errstr. " doing $query"; - $sth->execute; # or die "Error executing \"$query\": ". $sth->errstr; - } - -} - -if ( scalar(@cust_pkg) == 1 ) { - print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum. - "#cust_pkg". $cust_pkg[0]->pkgnum ); - #exit; -} elsif ( scalar(@cust_pkg) == 0 ) { #error -%> - -<% - eidiot("No packages found"); -} else { -%> - -<% - $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 - 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, $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, <$pkgnum - $pkg - $setup -END - - print "$last_bill" - if defined dbdef->table('cust_pkg')->column('last_bill'); - - print <$bill - $susp - $expire - $cancel -END - if ( $cust_main ) { - print <$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) -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 "$pager"; - -} -sub pkgnum_sort { - $a->getfield('pkgnum') <=> $b->getfield('pkgnum'); } -sub bill_sort { - $a->getfield('bill') <=> $b->getfield('bill'); +my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; + +my $count_query = "SELECT COUNT(*) FROM cust_pkg $cjoin $extra_sql"; + +my $sql_query = { + 'table' => 'cust_pkg', + 'hashref' => {}, + 'select' => join(', ', + 'cust_pkg.*', + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'extra_sql' => "$extra_sql $orderby", + 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ', + #' LEFT JOIN part_pkg USING ( pkgpart ) ' +}; + +my $link = sub { + [ "${p}view/cust_main.cgi?".shift->custnum.'#cust_pkg', 'pkgnum' ]; +}; + +my $clink = sub { + my $cust_pkg = shift; + $cust_pkg->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'custnum' ] + : ''; +}; + +#if ( scalar(@cust_pkg) == 1 ) { +# print $cgi->redirect("${p}view/cust_main.cgi?". $cust_pkg[0]->custnum. +# "#cust_pkg". $cust_pkg[0]->pkgnum ); + +# my @cust_svc = qsearch( 'cust_svc', { 'pkgnum' => $pkgnum } ); +# my $rowspan = scalar(@cust_svc) || 1; + +# 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=""; +# } + +sub time_or_blank { + my $column = shift; + return sub { + my $record = shift; + my $value = $record->get($column); #mmm closures + $value ? time2str('%b %d %Y', $value ) : ''; + }; } +%><%= include( 'elements/search.html', + 'title' => 'Package Search Results', + 'name' => 'packages', + 'query' => $sql_query, + 'count_query' => $count_query, + 'redirect' => $link, + 'header' => [ '#', + 'Package', + 'Status', + 'Freq.', + 'Setup', + 'Last bill', + 'Next bill', + 'Susp.', + 'Expire', + 'Cancel', + FS::UI::Web::cust_header(), + 'Services', + ], + 'fields' => [ + 'pkgnum', + sub { my $part_pkg = $part_pkg{shift->pkgpart}; + $part_pkg->pkg; # ' - '. $part_pkg->comment; + }, + sub { ucfirst(shift->status); }, + sub { #shift->part_pkg->freq_pretty; + my $part_pkg = $part_pkg{shift->pkgpart}; + $part_pkg->freq_pretty; + }, + + #sub { time2str('%b %d %Y', shift->setup); }, + #sub { time2str('%b %d %Y', shift->last_bill); }, + #sub { time2str('%b %d %Y', shift->bill); }, + #sub { time2str('%b %d %Y', shift->susp); }, + #sub { time2str('%b %d %Y', shift->expire); }, + #sub { time2str('%b %d %Y', shift->get('cancel')); }, + ( map { time_or_blank($_) } + qw( setup last_bill bill susp expire cancel ) ), + + \&FS::UI::Web::cust_fields, + #sub { ''. + # join('', map { '' } + # shift->labels + # ). + # '
'. $_->[0]. + # ':'. $_->[1]. '
'; + # }, + sub { + [ map { + [ + { 'data' => $_->[0]. ':', + 'align'=> 'right', + }, + { 'data' => $_->[1], + 'align'=> 'left', + 'link' => $p. 'view/' . + $_->[2]. '.cgi?'. $_->[3], + }, + ]; + } shift->labels + ]; + }, + ], + 'color' => [ + '', + '', + sub { shift->statuscolor; }, + '', + '', + '', + '', + '', + '', + '', + ( map { '' } FS::UI::Web::cust_header() ), + '', + ], + 'style' => [ '', '', 'b' ], + 'size' => [ '', '', '-1', ], + 'align' => 'rlclrrrrrr', + 'links' => [ + $link, + $link, + '', + '', + '', + '', + '', + '', + '', + '', + ( map { $clink } FS::UI::Web::cust_header() ), + '', + ], + ) %> -- cgit v1.2.1