From 1a033848671cad2cbe7687b37fc718b3b2a68b83 Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 22 Apr 2006 00:58:40 +0000 Subject: start of package class web UI (add/edit package classes, package class selection in package def edit) --- httemplate/search/cust_pkg.cgi | 8 +++++--- 1 file changed, 5 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 5da4d82fb..a2fb89c12 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -19,8 +19,10 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @where, - "bill >= $beginning ", - "bill <= $ending", + #"bill >= $beginning ", + #"bill <= $ending", + "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ", + "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending", '( cancel IS NULL OR cancel = 0 )'; } else { @@ -141,7 +143,7 @@ sub time_or_blank { 'name' => 'packages', 'query' => $sql_query, 'count_query' => $count_query, - 'redirect' => $link, + #'redirect' => $link, 'header' => [ '#', 'Package', 'Status', -- cgit v1.2.1 From c0e8da2f1e89729efa1032241e4239765a296514 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 19 Jun 2006 02:33:52 +0000 Subject: agent virtualization, take one (stuff from "inactive" changeset snuck into cust_main.pm and the package reporting changeset in search/cust_pkg.cgi here too) --- httemplate/search/cust_pkg.cgi | 162 ++++++++++++++++++++++++++++++----------- 1 file changed, 118 insertions(+), 44 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index a2fb89c12..e8b3f490d 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,19 +1,80 @@ <% -my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); +# my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); my($query) = $cgi->keywords; -my $orderby; -my @where; -my $cjoin = ''; +my @where = (); + +## +# parse agent +## if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { - $cjoin = "LEFT JOIN cust_main USING ( custnum )"; push @where, "agentnum = $1"; } +## +# parse status +## + +if ( $cgi->param('magic') eq 'active' + || $cgi->param('status') eq 'active' ) { + + push @where, FS::cust_pkg->active_sql(); + +} elsif ( $cgi->param('magic') eq 'suspended' + || $cgi->param('status') eq 'suspended' ) { + + push @where, FS::cust_pkg->suspended_sql(); + +} elsif ( $cgi->param('magic') =~ /^cancell?ed$/ + || $cgi->param('status') =~ /^cancell?ed$/ ) { + + push @where, FS::cust_pkg->cancelled_sql(); + +} elsif ( $cgi->param('status') =~ /^(one-time charge|inactive)$/ ) { + + push @where, FS::cust_pkg->inactive_sql(); + +} + +### +# parse package class +### + +#false lazinessish w/graph/cust_bill_pkg.cgi +my $classnum = 0; +my @pkg_class = (); +if ( $cgi->param('classnum') =~ /^(\d*)$/ ) { + $classnum = $1; + if ( $classnum ) { #a specific class + push @where, "classnum = $classnum"; + + #@pkg_class = ( qsearchs('pkg_class', { 'classnum' => $classnum } ) ); + #die "classnum $classnum not found!" unless $pkg_class[0]; + #$title .= $pkg_class[0]->classname.' '; + + } elsif ( $classnum eq '' ) { #the empty class + + push @where, "classnum IS NULL"; + #$title .= 'Empty class '; + #@pkg_class = ( '(empty class)' ); + } elsif ( $classnum eq '0' ) { + #@pkg_class = qsearch('pkg_class', {} ); # { 'disabled' => '' } ); + #push @pkg_class, '(empty class)'; + } else { + die "illegal classnum"; + } +} +#eslaf + +### +# parse magic, legacy, etc. +### + +my $orderby; if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $orderby = 'ORDER BY bill'; @@ -23,7 +84,8 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { #"bill <= $ending", "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ", "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending", - '( cancel IS NULL OR cancel = 0 )'; + #'( cancel IS NULL OR cancel = 0 )' + ; } else { @@ -33,30 +95,6 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { $orderby = 'ORDER BY pkgnum'; - if ( $cgi->param('magic') eq 'active' ) { - - #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' ) { - - 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"; - } - if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { push @where, "pkgpart = $1"; } @@ -84,21 +122,35 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } +## +# setup queries, links, subs, etc. for the search +## + +# here is the agent virtualization +push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; + my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; -my $count_query = "SELECT COUNT(*) FROM cust_pkg $cjoin $extra_sql"; +my $addl_from = 'LEFT JOIN cust_main USING ( custnum ) '. + 'LEFT JOIN part_pkg USING ( pkgpart ) '. + 'LEFT JOIN pkg_class USING ( classnum ) '; + +my $count_query = "SELECT COUNT(*) FROM cust_pkg $addl_from $extra_sql"; my $sql_query = { 'table' => 'cust_pkg', 'hashref' => {}, 'select' => join(', ', 'cust_pkg.*', + ( map "part_pkg.$_", qw( pkg freq ) ), + 'pkg_class.classname', 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), + FS::UI::Web::cust_sql_fields( + $cgi->param('cust_fields') + ), ), 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => ' LEFT JOIN cust_main USING ( custnum ) ', - #' LEFT JOIN part_pkg USING ( pkgpart ) ' + 'addl_from' => $addl_from, }; my $link = sub { @@ -138,6 +190,10 @@ sub time_or_blank { }; } +### +# and finally, include the search template +### + %><%= include( 'elements/search.html', 'title' => 'Package Search Results', 'name' => 'packages', @@ -146,6 +202,7 @@ sub time_or_blank { #'redirect' => $link, 'header' => [ '#', 'Package', + 'Class', 'Status', 'Freq.', 'Setup', @@ -154,18 +211,25 @@ sub time_or_blank { 'Susp.', 'Expire', 'Cancel', - FS::UI::Web::cust_header(), + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ), 'Services', ], 'fields' => [ 'pkgnum', - sub { my $part_pkg = $part_pkg{shift->pkgpart}; - $part_pkg->pkg; # ' - '. $part_pkg->comment; + sub { #my $part_pkg = $part_pkg{shift->pkgpart}; + #$part_pkg->pkg; # ' - '. $part_pkg->comment; + $_[0]->pkg; # ' - '. $_[0]->comment; }, + 'classname', sub { ucfirst(shift->status); }, sub { #shift->part_pkg->freq_pretty; - my $part_pkg = $part_pkg{shift->pkgpart}; - $part_pkg->freq_pretty; + + #my $part_pkg = $part_pkg{shift->pkgpart}; + #$part_pkg->freq_pretty; + + FS::part_pkg::freq_pretty(shift); }, #sub { time2str('%b %d %Y', shift->setup); }, @@ -202,6 +266,7 @@ sub time_or_blank { }, ], 'color' => [ + '', '', '', sub { shift->statuscolor; }, @@ -212,12 +277,16 @@ sub time_or_blank { '', '', '', - ( map { '' } FS::UI::Web::cust_header() ), + ( map { '' } + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ) + ), '', ], - 'style' => [ '', '', 'b' ], - 'size' => [ '', '', '-1', ], - 'align' => 'rlclrrrrrr', + 'style' => [ '', '', '', 'b' ], + 'size' => [ '', '', '', '-1', ], + 'align' => 'rllclrrrrrr', 'links' => [ $link, $link, @@ -229,7 +298,12 @@ sub time_or_blank { '', '', '', - ( map { $clink } FS::UI::Web::cust_header() ), + '', + ( map { $clink } + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ) + ), '', ], ) -- cgit v1.2.1 From a0732f52fdcc2bca7c399d1249ccceb191de51cd Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 25 Jul 2006 08:33:46 +0000 Subject: this should finish adding the "inactive" status, i think? --- httemplate/search/cust_pkg.cgi | 13 +++++++++++-- 1 file changed, 11 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 e8b3f490d..614e9b509 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -24,6 +24,12 @@ if ( $cgi->param('magic') eq 'active' push @where, FS::cust_pkg->active_sql(); +} elsif ( $cgi->param('magic') eq 'inactive' + || $cgi->param('status') eq 'inactive' ) { + + push @where, FS::cust_pkg->inactive_sql(); + + } elsif ( $cgi->param('magic') eq 'suspended' || $cgi->param('status') eq 'suspended' ) { @@ -47,7 +53,10 @@ if ( $cgi->param('magic') eq 'active' #false lazinessish w/graph/cust_bill_pkg.cgi my $classnum = 0; my @pkg_class = (); -if ( $cgi->param('classnum') =~ /^(\d*)$/ ) { +if ( exists($cgi->Vars->{'classnum'}) + && $cgi->param('classnum') =~ /^(\d*)$/ + ) +{ $classnum = $1; if ( $classnum ) { #a specific class push @where, "classnum = $classnum"; @@ -90,7 +99,7 @@ if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { } else { if ( $cgi->param('magic') && - $cgi->param('magic') =~ /^(active|suspended|cancell?ed)$/ + $cgi->param('magic') =~ /^(active|inactive|suspended|cancell?ed)$/ ) { $orderby = 'ORDER BY pkgnum'; -- cgit v1.2.1 From 3ce7691203a7737406bf2d4442f7fd84b81f847e Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 23 Aug 2006 22:25:39 +0000 Subject: Will things ever be the same again? It's the final masonize --- httemplate/search/cust_pkg.cgi | 413 +++++++++++++++++++++-------------------- 1 file changed, 207 insertions(+), 206 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 614e9b509..8dbc600e9 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,209 +1,210 @@ -<% - -# my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); - -my($query) = $cgi->keywords; - -my @where = (); - -## -# parse agent -## - -if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { - push @where, - "agentnum = $1"; -} - -## -# parse status -## - -if ( $cgi->param('magic') eq 'active' - || $cgi->param('status') eq 'active' ) { - - push @where, FS::cust_pkg->active_sql(); - -} elsif ( $cgi->param('magic') eq 'inactive' - || $cgi->param('status') eq 'inactive' ) { - - push @where, FS::cust_pkg->inactive_sql(); - - -} elsif ( $cgi->param('magic') eq 'suspended' - || $cgi->param('status') eq 'suspended' ) { - - push @where, FS::cust_pkg->suspended_sql(); - -} elsif ( $cgi->param('magic') =~ /^cancell?ed$/ - || $cgi->param('status') =~ /^cancell?ed$/ ) { - - push @where, FS::cust_pkg->cancelled_sql(); - -} elsif ( $cgi->param('status') =~ /^(one-time charge|inactive)$/ ) { - - push @where, FS::cust_pkg->inactive_sql(); - -} - -### -# parse package class -### - -#false lazinessish w/graph/cust_bill_pkg.cgi -my $classnum = 0; -my @pkg_class = (); -if ( exists($cgi->Vars->{'classnum'}) - && $cgi->param('classnum') =~ /^(\d*)$/ - ) -{ - $classnum = $1; - if ( $classnum ) { #a specific class - push @where, "classnum = $classnum"; - - #@pkg_class = ( qsearchs('pkg_class', { 'classnum' => $classnum } ) ); - #die "classnum $classnum not found!" unless $pkg_class[0]; - #$title .= $pkg_class[0]->classname.' '; - - } elsif ( $classnum eq '' ) { #the empty class - - push @where, "classnum IS NULL"; - #$title .= 'Empty class '; - #@pkg_class = ( '(empty class)' ); - } elsif ( $classnum eq '0' ) { - #@pkg_class = qsearch('pkg_class', {} ); # { 'disabled' => '' } ); - #push @pkg_class, '(empty class)'; - } else { - die "illegal classnum"; - } -} -#eslaf - -### -# parse magic, legacy, etc. -### - -my $orderby; -if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { - $orderby = 'ORDER BY bill'; - - my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - push @where, - #"bill >= $beginning ", - #"bill <= $ending", - "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ", - "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending", - #'( cancel IS NULL OR cancel = 0 )' - ; - -} else { - - if ( $cgi->param('magic') && - $cgi->param('magic') =~ /^(active|inactive|suspended|cancell?ed)$/ - ) { - - $orderby = 'ORDER BY pkgnum'; - - if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { - push @where, "pkgpart = $1"; - } - - } elsif ( $query eq 'pkgnum' ) { - - $orderby = 'ORDER BY pkgnum'; - - } elsif ( $query eq 'APKG_pkgnum' ) { - - $orderby = 'ORDER BY pkgnum'; - - 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!"; - } - -} - -## -# setup queries, links, subs, etc. for the search -## - -# here is the agent virtualization -push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; - -my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; - -my $addl_from = 'LEFT JOIN cust_main USING ( custnum ) '. - 'LEFT JOIN part_pkg USING ( pkgpart ) '. - 'LEFT JOIN pkg_class USING ( classnum ) '; - -my $count_query = "SELECT COUNT(*) FROM cust_pkg $addl_from $extra_sql"; - -my $sql_query = { - 'table' => 'cust_pkg', - 'hashref' => {}, - 'select' => join(', ', - 'cust_pkg.*', - ( map "part_pkg.$_", qw( pkg freq ) ), - 'pkg_class.classname', - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields( - $cgi->param('cust_fields') - ), - ), - 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => $addl_from, -}; - -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 ) : ''; - }; -} - -### -# and finally, include the search template -### - -%><%= include( 'elements/search.html', +% +% +%# my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); +% +%my($query) = $cgi->keywords; +% +%my @where = (); +% +%## +%# parse agent +%## +% +%if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { +% push @where, +% "agentnum = $1"; +%} +% +%## +%# parse status +%## +% +%if ( $cgi->param('magic') eq 'active' +% || $cgi->param('status') eq 'active' ) { +% +% push @where, FS::cust_pkg->active_sql(); +% +%} elsif ( $cgi->param('magic') eq 'inactive' +% || $cgi->param('status') eq 'inactive' ) { +% +% push @where, FS::cust_pkg->inactive_sql(); +% +% +%} elsif ( $cgi->param('magic') eq 'suspended' +% || $cgi->param('status') eq 'suspended' ) { +% +% push @where, FS::cust_pkg->suspended_sql(); +% +%} elsif ( $cgi->param('magic') =~ /^cancell?ed$/ +% || $cgi->param('status') =~ /^cancell?ed$/ ) { +% +% push @where, FS::cust_pkg->cancelled_sql(); +% +%} elsif ( $cgi->param('status') =~ /^(one-time charge|inactive)$/ ) { +% +% push @where, FS::cust_pkg->inactive_sql(); +% +%} +% +%### +%# parse package class +%### +% +%#false lazinessish w/graph/cust_bill_pkg.cgi +%my $classnum = 0; +%my @pkg_class = (); +%if ( exists($cgi->Vars->{'classnum'}) +% && $cgi->param('classnum') =~ /^(\d*)$/ +% ) +%{ +% $classnum = $1; +% if ( $classnum ) { #a specific class +% push @where, "classnum = $classnum"; +% +% #@pkg_class = ( qsearchs('pkg_class', { 'classnum' => $classnum } ) ); +% #die "classnum $classnum not found!" unless $pkg_class[0]; +% #$title .= $pkg_class[0]->classname.' '; +% +% } elsif ( $classnum eq '' ) { #the empty class +% +% push @where, "classnum IS NULL"; +% #$title .= 'Empty class '; +% #@pkg_class = ( '(empty class)' ); +% } elsif ( $classnum eq '0' ) { +% #@pkg_class = qsearch('pkg_class', {} ); # { 'disabled' => '' } ); +% #push @pkg_class, '(empty class)'; +% } else { +% die "illegal classnum"; +% } +%} +%#eslaf +% +%### +%# parse magic, legacy, etc. +%### +% +%my $orderby; +%if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { +% $orderby = 'ORDER BY bill'; +% +% my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +% push @where, +% #"bill >= $beginning ", +% #"bill <= $ending", +% "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ", +% "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending", +% #'( cancel IS NULL OR cancel = 0 )' +% ; +% +%} else { +% +% if ( $cgi->param('magic') && +% $cgi->param('magic') =~ /^(active|inactive|suspended|cancell?ed)$/ +% ) { +% +% $orderby = 'ORDER BY pkgnum'; +% +% if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { +% push @where, "pkgpart = $1"; +% } +% +% } elsif ( $query eq 'pkgnum' ) { +% +% $orderby = 'ORDER BY pkgnum'; +% +% } elsif ( $query eq 'APKG_pkgnum' ) { +% +% $orderby = 'ORDER BY pkgnum'; +% +% 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!"; +% } +% +%} +% +%## +%# setup queries, links, subs, etc. for the search +%## +% +%# here is the agent virtualization +%push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; +% +%my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; +% +%my $addl_from = 'LEFT JOIN cust_main USING ( custnum ) '. +% 'LEFT JOIN part_pkg USING ( pkgpart ) '. +% 'LEFT JOIN pkg_class USING ( classnum ) '; +% +%my $count_query = "SELECT COUNT(*) FROM cust_pkg $addl_from $extra_sql"; +% +%my $sql_query = { +% 'table' => 'cust_pkg', +% 'hashref' => {}, +% 'select' => join(', ', +% 'cust_pkg.*', +% ( map "part_pkg.$_", qw( pkg freq ) ), +% 'pkg_class.classname', +% 'cust_main.custnum as cust_main_custnum', +% FS::UI::Web::cust_sql_fields( +% $cgi->param('cust_fields') +% ), +% ), +% 'extra_sql' => "$extra_sql $orderby", +% 'addl_from' => $addl_from, +%}; +% +%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 ) : ''; +% }; +%} +% +%### +%# and finally, include the search template +%### +% +% +<% include( 'elements/search.html', 'title' => 'Package Search Results', 'name' => 'packages', 'query' => $sql_query, -- cgit v1.2.1 From 0c2f4bccc3810426d4b9e31dca645711c32f0b63 Mon Sep 17 00:00:00 2001 From: jeff Date: Wed, 25 Oct 2006 04:44:48 +0000 Subject: select multiple pkgparts on advanced package report --- httemplate/search/cust_pkg.cgi | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 8dbc600e9..7a651c4bf 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -80,6 +80,14 @@ %#eslaf % %### +%# parse part_pkg +%### +% +%my $pkgpart = join (' OR pkgpart=', +% grep {$_} map { /^(\d+)$/; } ($cgi->param('pkgpart'))); +%push @where, '(pkgpart=' . $pkgpart . ')' if $pkgpart; +% +%### %# parse magic, legacy, etc. %### % -- cgit v1.2.1 From 590603ecaea3184f64530755a76626be8205da49 Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 3 Feb 2007 11:36:30 +0000 Subject: add customer status column to customer & most other reports. also put the C in ACL in the search/ and graph/ directories. --- httemplate/search/cust_pkg.cgi | 442 ++++++++++++++++++++--------------------- 1 file changed, 219 insertions(+), 223 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 7a651c4bf..bb2307641 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -1,218 +1,4 @@ -% -% -%# my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); -% -%my($query) = $cgi->keywords; -% -%my @where = (); -% -%## -%# parse agent -%## -% -%if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { -% push @where, -% "agentnum = $1"; -%} -% -%## -%# parse status -%## -% -%if ( $cgi->param('magic') eq 'active' -% || $cgi->param('status') eq 'active' ) { -% -% push @where, FS::cust_pkg->active_sql(); -% -%} elsif ( $cgi->param('magic') eq 'inactive' -% || $cgi->param('status') eq 'inactive' ) { -% -% push @where, FS::cust_pkg->inactive_sql(); -% -% -%} elsif ( $cgi->param('magic') eq 'suspended' -% || $cgi->param('status') eq 'suspended' ) { -% -% push @where, FS::cust_pkg->suspended_sql(); -% -%} elsif ( $cgi->param('magic') =~ /^cancell?ed$/ -% || $cgi->param('status') =~ /^cancell?ed$/ ) { -% -% push @where, FS::cust_pkg->cancelled_sql(); -% -%} elsif ( $cgi->param('status') =~ /^(one-time charge|inactive)$/ ) { -% -% push @where, FS::cust_pkg->inactive_sql(); -% -%} -% -%### -%# parse package class -%### -% -%#false lazinessish w/graph/cust_bill_pkg.cgi -%my $classnum = 0; -%my @pkg_class = (); -%if ( exists($cgi->Vars->{'classnum'}) -% && $cgi->param('classnum') =~ /^(\d*)$/ -% ) -%{ -% $classnum = $1; -% if ( $classnum ) { #a specific class -% push @where, "classnum = $classnum"; -% -% #@pkg_class = ( qsearchs('pkg_class', { 'classnum' => $classnum } ) ); -% #die "classnum $classnum not found!" unless $pkg_class[0]; -% #$title .= $pkg_class[0]->classname.' '; -% -% } elsif ( $classnum eq '' ) { #the empty class -% -% push @where, "classnum IS NULL"; -% #$title .= 'Empty class '; -% #@pkg_class = ( '(empty class)' ); -% } elsif ( $classnum eq '0' ) { -% #@pkg_class = qsearch('pkg_class', {} ); # { 'disabled' => '' } ); -% #push @pkg_class, '(empty class)'; -% } else { -% die "illegal classnum"; -% } -%} -%#eslaf -% -%### -%# parse part_pkg -%### -% -%my $pkgpart = join (' OR pkgpart=', -% grep {$_} map { /^(\d+)$/; } ($cgi->param('pkgpart'))); -%push @where, '(pkgpart=' . $pkgpart . ')' if $pkgpart; -% -%### -%# parse magic, legacy, etc. -%### -% -%my $orderby; -%if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { -% $orderby = 'ORDER BY bill'; -% -% my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -% push @where, -% #"bill >= $beginning ", -% #"bill <= $ending", -% "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ", -% "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending", -% #'( cancel IS NULL OR cancel = 0 )' -% ; -% -%} else { -% -% if ( $cgi->param('magic') && -% $cgi->param('magic') =~ /^(active|inactive|suspended|cancell?ed)$/ -% ) { -% -% $orderby = 'ORDER BY pkgnum'; -% -% if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { -% push @where, "pkgpart = $1"; -% } -% -% } elsif ( $query eq 'pkgnum' ) { -% -% $orderby = 'ORDER BY pkgnum'; -% -% } elsif ( $query eq 'APKG_pkgnum' ) { -% -% $orderby = 'ORDER BY pkgnum'; -% -% 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!"; -% } -% -%} -% -%## -%# setup queries, links, subs, etc. for the search -%## -% -%# here is the agent virtualization -%push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; -% -%my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; -% -%my $addl_from = 'LEFT JOIN cust_main USING ( custnum ) '. -% 'LEFT JOIN part_pkg USING ( pkgpart ) '. -% 'LEFT JOIN pkg_class USING ( classnum ) '; -% -%my $count_query = "SELECT COUNT(*) FROM cust_pkg $addl_from $extra_sql"; -% -%my $sql_query = { -% 'table' => 'cust_pkg', -% 'hashref' => {}, -% 'select' => join(', ', -% 'cust_pkg.*', -% ( map "part_pkg.$_", qw( pkg freq ) ), -% 'pkg_class.classname', -% 'cust_main.custnum as cust_main_custnum', -% FS::UI::Web::cust_sql_fields( -% $cgi->param('cust_fields') -% ), -% ), -% 'extra_sql' => "$extra_sql $orderby", -% 'addl_from' => $addl_from, -%}; -% -%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 ) : ''; -% }; -%} -% -%### -%# and finally, include the search template -%### -% -% -<% include( 'elements/search.html', +<% include( 'elements/search.html', 'title' => 'Package Search Results', 'name' => 'packages', 'query' => $sql_query, @@ -295,16 +81,13 @@ '', '', '', - ( map { '' } - FS::UI::Web::cust_header( - $cgi->param('cust_fields') - ) - ), + FS::UI::Web::cust_colors(), '', ], - 'style' => [ '', '', '', 'b' ], + 'style' => [ '', '', '', 'b', '', '', '', '', '', '', '', + FS::UI::Web::cust_styles() ], 'size' => [ '', '', '', '-1', ], - 'align' => 'rllclrrrrrr', + 'align' => 'rllclrrrrrr'. FS::UI::Web::cust_aligns(). 'r', 'links' => [ $link, $link, @@ -317,7 +100,7 @@ '', '', '', - ( map { $clink } + ( map { $_ ne 'Cust. Status' ? $clink : '' } FS::UI::Web::cust_header( $cgi->param('cust_fields') ) @@ -326,3 +109,216 @@ ], ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List packages'); + +# my %part_pkg = map { $_->pkgpart => $_ } qsearch('part_pkg', {}); + +my($query) = $cgi->keywords; + +my @where = (); + +## +# parse agent +## + +if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) { + push @where, + "agentnum = $1"; +} + +## +# parse status +## + +if ( $cgi->param('magic') eq 'active' + || $cgi->param('status') eq 'active' ) { + + push @where, FS::cust_pkg->active_sql(); + +} elsif ( $cgi->param('magic') eq 'inactive' + || $cgi->param('status') eq 'inactive' ) { + + push @where, FS::cust_pkg->inactive_sql(); + + +} elsif ( $cgi->param('magic') eq 'suspended' + || $cgi->param('status') eq 'suspended' ) { + + push @where, FS::cust_pkg->suspended_sql(); + +} elsif ( $cgi->param('magic') =~ /^cancell?ed$/ + || $cgi->param('status') =~ /^cancell?ed$/ ) { + + push @where, FS::cust_pkg->cancelled_sql(); + +} elsif ( $cgi->param('status') =~ /^(one-time charge|inactive)$/ ) { + + push @where, FS::cust_pkg->inactive_sql(); + +} + +### +# parse package class +### + +#false lazinessish w/graph/cust_bill_pkg.cgi +my $classnum = 0; +my @pkg_class = (); +if ( exists($cgi->Vars->{'classnum'}) + && $cgi->param('classnum') =~ /^(\d*)$/ + ) +{ + $classnum = $1; + if ( $classnum ) { #a specific class + push @where, "classnum = $classnum"; + + #@pkg_class = ( qsearchs('pkg_class', { 'classnum' => $classnum } ) ); + #die "classnum $classnum not found!" unless $pkg_class[0]; + #$title .= $pkg_class[0]->classname.' '; + + } elsif ( $classnum eq '' ) { #the empty class + + push @where, "classnum IS NULL"; + #$title .= 'Empty class '; + #@pkg_class = ( '(empty class)' ); + } elsif ( $classnum eq '0' ) { + #@pkg_class = qsearch('pkg_class', {} ); # { 'disabled' => '' } ); + #push @pkg_class, '(empty class)'; + } else { + die "illegal classnum"; + } +} +#eslaf + +### +# parse part_pkg +### + +my $pkgpart = join (' OR pkgpart=', + grep {$_} map { /^(\d+)$/; } ($cgi->param('pkgpart'))); +push @where, '(pkgpart=' . $pkgpart . ')' if $pkgpart; + +### +# parse magic, legacy, etc. +### + +my $orderby; +if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { + $orderby = 'ORDER BY bill'; + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + push @where, + #"bill >= $beginning ", + #"bill <= $ending", + "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ", + "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending", + #'( cancel IS NULL OR cancel = 0 )' + ; + +} else { + + if ( $cgi->param('magic') && + $cgi->param('magic') =~ /^(active|inactive|suspended|cancell?ed)$/ + ) { + + $orderby = 'ORDER BY pkgnum'; + + if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { + push @where, "pkgpart = $1"; + } + + } elsif ( $query eq 'pkgnum' ) { + + $orderby = 'ORDER BY pkgnum'; + + } elsif ( $query eq 'APKG_pkgnum' ) { + + $orderby = 'ORDER BY pkgnum'; + + 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!"; + } + +} + +## +# setup queries, links, subs, etc. for the search +## + +# here is the agent virtualization +push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; + +my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; + +my $addl_from = 'LEFT JOIN cust_main USING ( custnum ) '. + 'LEFT JOIN part_pkg USING ( pkgpart ) '. + 'LEFT JOIN pkg_class USING ( classnum ) '; + +my $count_query = "SELECT COUNT(*) FROM cust_pkg $addl_from $extra_sql"; + +my $sql_query = { + 'table' => 'cust_pkg', + 'hashref' => {}, + 'select' => join(', ', + 'cust_pkg.*', + ( map "part_pkg.$_", qw( pkg freq ) ), + 'pkg_class.classname', + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields( + $cgi->param('cust_fields') + ), + ), + 'extra_sql' => "$extra_sql $orderby", + 'addl_from' => $addl_from, +}; + +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 ) : ''; + }; +} + + -- cgit v1.2.1 From ab9e07a6507647e1aaf59df75edc54b40a745643 Mon Sep 17 00:00:00 2001 From: ivan Date: Wed, 14 Feb 2007 08:48:36 +0000 Subject: add more options to advanced package reporting --- httemplate/search/cust_pkg.cgi | 87 +++++++++++++++++++++++++----------------- 1 file changed, 51 insertions(+), 36 deletions(-) (limited to 'httemplate/search/cust_pkg.cgi') diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index bb2307641..84b083a3d 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -202,55 +202,70 @@ my $pkgpart = join (' OR pkgpart=', push @where, '(pkgpart=' . $pkgpart . ')' if $pkgpart; ### -# parse magic, legacy, etc. +# parse dates ### -my $orderby; -if ( $cgi->param('magic') && $cgi->param('magic') eq 'bill' ) { - $orderby = 'ORDER BY bill'; +my $orderby = ''; + +#false laziness w/report_cust_pkg.html +my %disable = ( + 'all' => {}, + 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, }, + 'active' => { 'susp'=>1, 'cancel'=>1 }, + 'suspended' => { 'cancel' => 1 }, + 'cancelled' => {}, + '' => {}, +); + +foreach my $field (qw( setup last_bill bill susp expire cancel )) { + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field); + + next if $beginning == 0 && $ending == 4294967295 + or $disable{$cgi->param('status')}->{$field}; - my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @where, - #"bill >= $beginning ", - #"bill <= $ending", - "CASE WHEN bill IS NULL THEN 0 ELSE bill END >= $beginning ", - "CASE WHEN bill IS NULL THEN 0 ELSE bill END <= $ending", - #'( cancel IS NULL OR cancel = 0 )' - ; + "$field IS NOT NULL", + "$field >= $beginning", + "$field <= $ending"; -} else { + $orderby ||= "ORDER BY $field"; - if ( $cgi->param('magic') && - $cgi->param('magic') =~ /^(active|inactive|suspended|cancell?ed)$/ - ) { +} - $orderby = 'ORDER BY pkgnum'; +$orderby ||= 'ORDER BY bill'; - if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { - push @where, "pkgpart = $1"; - } +### +# parse magic, legacy, etc. +### - } elsif ( $query eq 'pkgnum' ) { +if ( $cgi->param('magic') && + $cgi->param('magic') =~ /^(active|inactive|suspended|cancell?ed)$/ +) { - $orderby = 'ORDER BY pkgnum'; + $orderby = 'ORDER BY pkgnum'; - } elsif ( $query eq 'APKG_pkgnum' ) { - - $orderby = 'ORDER BY pkgnum'; - - 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!"; + if ( $cgi->param('pkgpart') =~ /^(\d+)$/ ) { + push @where, "pkgpart = $1"; } +} elsif ( $query eq 'pkgnum' ) { + + $orderby = 'ORDER BY pkgnum'; + +} elsif ( $query eq 'APKG_pkgnum' ) { + + $orderby = 'ORDER BY pkgnum'; + + 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 + ) + )'; + } ## -- cgit v1.2.1