diff options
Diffstat (limited to 'httemplate/search')
40 files changed, 3946 insertions, 3875 deletions
diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html index e3d6043e8..827a50895 100644 --- a/httemplate/search/cdr.html +++ b/httemplate/search/cdr.html @@ -1,29 +1,30 @@ -<% - -my $title = 'Call Detail Records'; -my $hashref = {}; -my $count_query = 'SELECT COUNT(*) FROM cdr'; - -#process params for CDR search, populate $hashref... -# and fixup $count_query - -if ( $cgi->param('freesidestatus') eq 'NULL' ) { - - my $title = "Unprocessed $title"; - $hashref->{'freesidestatus'} = ''; # Record.pm will take care of it - #$count_query .= " AND ( freesidestatus IS NULL OR freesidestatus = '' )"; - $count_query .= " WHERE ( freesidestatus IS NULL OR freesidestatus = '' )"; - -} elsif ( $cgi->param('freesidestatus') =~ /^([\w ]+)$/ ) { - - my $title = "Processed $title"; - $hashref->{'freesidestatus'} = $1; - #$count_query .= " AND freesidestatus = '$1'"; - $count_query .= " WHERE freesidestatus = '$1'"; - -} - -%><%= include( 'elements/search.html', +% +% +%my $title = 'Call Detail Records'; +%my $hashref = {}; +%my $count_query = 'SELECT COUNT(*) FROM cdr'; +% +%#process params for CDR search, populate $hashref... +%# and fixup $count_query +% +%if ( $cgi->param('freesidestatus') eq 'NULL' ) { +% +% my $title = "Unprocessed $title"; +% $hashref->{'freesidestatus'} = ''; # Record.pm will take care of it +% #$count_query .= " AND ( freesidestatus IS NULL OR freesidestatus = '' )"; +% $count_query .= " WHERE ( freesidestatus IS NULL OR freesidestatus = '' )"; +% +%} elsif ( $cgi->param('freesidestatus') =~ /^([\w ]+)$/ ) { +% +% my $title = "Processed $title"; +% $hashref->{'freesidestatus'} = $1; +% #$count_query .= " AND freesidestatus = '$1'"; +% $count_query .= " WHERE freesidestatus = '$1'"; +% +%} +% +% +<% include( 'elements/search.html', 'title' => $title, 'name' => 'call detail records', 'query' => { 'table' => 'cdr', diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 79c05dc42..894ddad51 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -1,166 +1,167 @@ -<% - - my $join_cust_main = 'LEFT JOIN cust_main USING ( custnum )'; - #here is the agent virtualization - my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; - - my( $count_query, $sql_query ); - my( $count_addl ) = ( '' ); - my( $distinct ) = ( '' ); - my($begin, $end) = ( '', '' ); - my($agentnum) = ( '' ); - my($open, $days) = ( '', '' ); - if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { - $count_query = - "SELECT COUNT(*) FROM cust_bill $join_cust_main". - " WHERE invnum = $2 AND $agentnums_sql"; #agent virtualization - $sql_query = { - 'table' => 'cust_bill', - 'addl_from' => $join_cust_main, - 'hashref' => { 'invnum' => $2 }, - #'select' => '*', - 'extra_sql' => " AND $agentnums_sql", #agent virtualization - }; - } else { - #if ( $cgi->param('begin') || $cgi->param('end') - # || $cgi->param('beginning') || $cgi->param('ending') - # || $cgi->keywords - # ) - #{ - - #some false laziness w/cust_bill::re_X - my @where; - my $orderby = 'ORDER BY cust_bill._date'; - - if ( $cgi->param('beginning') - && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { - $begin = str2time($1); - push @where, "cust_bill._date >= $begin"; - } - if ( $cgi->param('ending') - && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { - $end = str2time($1) + 86399; - push @where, "cust_bill._date < $end"; - } - - if ( $cgi->param('begin') =~ /^(\d+)$/ ) { - $begin = $1; - push @where, "cust_bill._date >= $begin"; - } - if ( $cgi->param('end') =~ /^(\d+)$/ ) { - $end = $1; - push @where, "cust_bill._date < $end"; - } - - if ( $cgi->param('invnum_min') =~ /^\s*(\d+)\s*$/ ) { - push @where, "cust_bill.invnum >= $1"; - } - if ( $cgi->param('invnum_max') =~ /^\s*(\d+)\s*$/ ) { - push @where, "cust_bill.invnum <= $1"; - } - - if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - $agentnum = $1; - push @where, "cust_main.agentnum = $agentnum"; - } - - my $owed = - "charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill_pay.invnum = cust_bill.invnum ) - - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_credit_bill.invnum = cust_bill.invnum )"; - - if ( $cgi->param('open') ) { - push @where, "0 != $owed"; - $open = 1; - } - - my($query) = $cgi->keywords; - if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) { - ($open, $days, my $field) = ($1, $2, $3); - $field = "_date" if $field eq 'date'; - $orderby = "ORDER BY cust_bill.$field"; - push @where, "0 != $owed" if $open; - push @where, "cust_bill._date < ". (time-86400*$days) if $days; - } - - #here is the agent virtualization - push @where, $agentnums_sql; - my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; - - if ( $cgi->param('newest_percust') ) { - $distinct = 'DISTINCT ON ( cust_bill.custnum )'; - $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; - #$count_query = "SELECT 'N/A', 'N/A', 'N/A'"; #XXXXXXX fix - $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"; - } - - unless ( $count_query ) { - $count_query = "SELECT COUNT(*), sum(charged), sum($owed)"; - $count_addl = [ '$%.2f total invoiced', - '$%.2f total outstanding balance', - ]; - } - $count_query .= " FROM cust_bill $join_cust_main $extra_sql"; - - $sql_query = { - 'table' => 'cust_bill', - 'addl_from' => $join_cust_main, - 'hashref' => {}, - 'select' => "$distinct ". join(', ', - 'cust_bill.*', - #( map "cust_main.$_", qw(custnum last first company) ), - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - "$owed as owed", - ), - 'extra_sql' => "$extra_sql $orderby" - }; - - } - - my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; - my $clink = sub { - my $cust_bill = shift; - $cust_bill->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; - }; - - my $conf = new FS::Conf; - my $money_char = $conf->config('money_char') || '$'; - - my $html_init = join("\n", map { - ( my $action = $_ ) =~ s/_$//; - include('/elements/progress-init.html', - $_.'form', - [ 'begin', 'end', 'agentnum', 'open', 'days', 'newest_percust' ], - "../misc/${_}invoices.cgi", - { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but... - $_, #key - ), - qq!<FORM NAME="${_}form">!, - qq!<INPUT TYPE="hidden" NAME="begin" VALUE="$begin">!, - qq!<INPUT TYPE="hidden" NAME="end" VALUE="$end">!, - qq!<INPUT TYPE="hidden" NAME="agentnum" VALUE="$agentnum">!, - qq!<INPUT TYPE="hidden" NAME="open" VALUE="$open">!, - qq!<INPUT TYPE="hidden" NAME="days" VALUE="$days">!, - qq!</FORM>! - } qw( print_ email_ fax_ ) ); - - my $menubar = [ - 'Main menu' => $p, - 'Print these invoices' => - "javascript:print_process()", - 'Email these invoices' => - "javascript:email_process()", - ]; - - push @$menubar, 'Fax these invoices' => - "javascript:fax_process()" - if $conf->exists('hylafax'); - -%><%= include( 'elements/search.html', +% +% +% my $join_cust_main = 'LEFT JOIN cust_main USING ( custnum )'; +% #here is the agent virtualization +% my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; +% +% my( $count_query, $sql_query ); +% my( $count_addl ) = ( '' ); +% my( $distinct ) = ( '' ); +% my($begin, $end) = ( '', '' ); +% my($agentnum) = ( '' ); +% my($open, $days) = ( '', '' ); +% if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { +% $count_query = +% "SELECT COUNT(*) FROM cust_bill $join_cust_main". +% " WHERE invnum = $2 AND $agentnums_sql"; #agent virtualization +% $sql_query = { +% 'table' => 'cust_bill', +% 'addl_from' => $join_cust_main, +% 'hashref' => { 'invnum' => $2 }, +% #'select' => '*', +% 'extra_sql' => " AND $agentnums_sql", #agent virtualization +% }; +% } else { +% #if ( $cgi->param('begin') || $cgi->param('end') +% # || $cgi->param('beginning') || $cgi->param('ending') +% # || $cgi->keywords +% # ) +% #{ +% +% #some false laziness w/cust_bill::re_X +% my @where; +% my $orderby = 'ORDER BY cust_bill._date'; +% +% if ( $cgi->param('beginning') +% && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { +% $begin = str2time($1); +% push @where, "cust_bill._date >= $begin"; +% } +% if ( $cgi->param('ending') +% && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { +% $end = str2time($1) + 86399; +% push @where, "cust_bill._date < $end"; +% } +% +% if ( $cgi->param('begin') =~ /^(\d+)$/ ) { +% $begin = $1; +% push @where, "cust_bill._date >= $begin"; +% } +% if ( $cgi->param('end') =~ /^(\d+)$/ ) { +% $end = $1; +% push @where, "cust_bill._date < $end"; +% } +% +% if ( $cgi->param('invnum_min') =~ /^\s*(\d+)\s*$/ ) { +% push @where, "cust_bill.invnum >= $1"; +% } +% if ( $cgi->param('invnum_max') =~ /^\s*(\d+)\s*$/ ) { +% push @where, "cust_bill.invnum <= $1"; +% } +% +% if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { +% $agentnum = $1; +% push @where, "cust_main.agentnum = $agentnum"; +% } +% +% my $owed = +% "charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay +% WHERE cust_bill_pay.invnum = cust_bill.invnum ) +% - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill +% WHERE cust_credit_bill.invnum = cust_bill.invnum )"; +% +% if ( $cgi->param('open') ) { +% push @where, "0 != $owed"; +% $open = 1; +% } +% +% my($query) = $cgi->keywords; +% if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) { +% ($open, $days, my $field) = ($1, $2, $3); +% $field = "_date" if $field eq 'date'; +% $orderby = "ORDER BY cust_bill.$field"; +% push @where, "0 != $owed" if $open; +% push @where, "cust_bill._date < ". (time-86400*$days) if $days; +% } +% +% #here is the agent virtualization +% push @where, $agentnums_sql; +% my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; +% +% if ( $cgi->param('newest_percust') ) { +% $distinct = 'DISTINCT ON ( cust_bill.custnum )'; +% $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; +% #$count_query = "SELECT 'N/A', 'N/A', 'N/A'"; #XXXXXXX fix +% $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"; +% } +% +% unless ( $count_query ) { +% $count_query = "SELECT COUNT(*), sum(charged), sum($owed)"; +% $count_addl = [ '$%.2f total invoiced', +% '$%.2f total outstanding balance', +% ]; +% } +% $count_query .= " FROM cust_bill $join_cust_main $extra_sql"; +% +% $sql_query = { +% 'table' => 'cust_bill', +% 'addl_from' => $join_cust_main, +% 'hashref' => {}, +% 'select' => "$distinct ". join(', ', +% 'cust_bill.*', +% #( map "cust_main.$_", qw(custnum last first company) ), +% 'cust_main.custnum as cust_main_custnum', +% FS::UI::Web::cust_sql_fields(), +% "$owed as owed", +% ), +% 'extra_sql' => "$extra_sql $orderby" +% }; +% +% } +% +% my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; +% my $clink = sub { +% my $cust_bill = shift; +% $cust_bill->cust_main_custnum +% ? [ "${p}view/cust_main.cgi?", 'custnum' ] +% : ''; +% }; +% +% my $conf = new FS::Conf; +% my $money_char = $conf->config('money_char') || '$'; +% +% my $html_init = join("\n", map { +% ( my $action = $_ ) =~ s/_$//; +% include('/elements/progress-init.html', +% $_.'form', +% [ 'begin', 'end', 'agentnum', 'open', 'days', 'newest_percust' ], +% "../misc/${_}invoices.cgi", +% { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but... +% $_, #key +% ), +% qq!<FORM NAME="${_}form">!, +% qq!<INPUT TYPE="hidden" NAME="begin" VALUE="$begin">!, +% qq!<INPUT TYPE="hidden" NAME="end" VALUE="$end">!, +% qq!<INPUT TYPE="hidden" NAME="agentnum" VALUE="$agentnum">!, +% qq!<INPUT TYPE="hidden" NAME="open" VALUE="$open">!, +% qq!<INPUT TYPE="hidden" NAME="days" VALUE="$days">!, +% qq!</FORM>! +% } qw( print_ email_ fax_ ) ); +% +% my $menubar = [ +% 'Main menu' => $p, +% 'Print these invoices' => +% "javascript:print_process()", +% 'Email these invoices' => +% "javascript:email_process()", +% ]; +% +% push @$menubar, 'Fax these invoices' => +% "javascript:fax_process()" +% if $conf->exists('hylafax'); +% +% +<% include( 'elements/search.html', 'title' => 'Invoice Search Results', 'html_init' => $html_init, 'menubar' => $menubar, diff --git a/httemplate/search/cust_bill_event.cgi b/httemplate/search/cust_bill_event.cgi index d82a83368..d1dd65d65 100644 --- a/httemplate/search/cust_bill_event.cgi +++ b/httemplate/search/cust_bill_event.cgi @@ -1,94 +1,95 @@ -<% - -my $title = $cgi->param('failed') ? 'Failed invoice events' : 'Invoice events'; - -my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - -##tie my %hash, 'Tie::DxHash', -#my %hash = ( -# _date => { op=> '>=', value=>$beginning }, -## i wish... -## _date => { op=> '<=', value=>$ending }, -#); -#$hash{'statustext'} = { op=> '!=', value=>'' } -# if $cgi->param('failed'); - -my $where = " WHERE cust_bill_event._date >= $beginning". - " AND cust_bill_event._date <= $ending"; - -if ( $cgi->param('failed') ) { - $where .= " AND statustext != '' ". - " AND statustext IS NOT NULL ". - " AND statustext != 'N/A' " -} - -if ( $cgi->param('part_bill_event.payby') =~ /^(\w+)$/ ) { - $where .= " AND part_bill_event.payby = '$1' "; -} - -my $sql_query = { - 'table' => 'cust_bill_event', - #'hashref' => \%hash, - 'hashref' => {}, - 'select' => join(', ', - 'cust_bill_event.*', - 'part_bill_event.event', - 'cust_bill.custnum', - 'cust_bill._date AS cust_bill_date', - 'cust_main.custnum AS cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => "$where ORDER BY _date ASC", - 'addl_from' => 'LEFT JOIN part_bill_event USING ( eventpart ) '. - 'LEFT JOIN cust_bill USING ( invnum ) '. - 'LEFT JOIN cust_main USING ( custnum ) ', -}; - -my $count_sql = "SELECT COUNT(*) FROM cust_bill_event ". - "LEFT JOIN part_bill_event USING ( eventpart ) ". - $where; - -my $conf = new FS::Conf; - -my $failed = $cgi->param('failed'); - -my $html_init = join("\n", map { - ( my $action = $_ ) =~ s/_$//; - include('/elements/progress-init.html', - $_.'form', - [ 'action', 'beginning', 'ending', 'failed' ], - "../misc/${_}invoice_events.cgi", - { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but... - $_, #key - ), - qq!<FORM NAME="${_}form">!, - qq!<INPUT TYPE="hidden" NAME="action" VALUE="$_">!, #not used though - qq!<INPUT TYPE="hidden" NAME="beginning" VALUE="$beginning">!, - qq!<INPUT TYPE="hidden" NAME="ending" VALUE="$ending">!, - qq!<INPUT TYPE="hidden" NAME="failed" VALUE="$failed">!, - qq!</FORM>! -} qw( print_ email_ fax_ ) ); - -my $menubar = [ - 'Main menu' => $p, - 'Re-print these events' => - "javascript:print_process()", - 'Re-email these events' => - "javascript:email_process()", - ]; - -push @$menubar, 'Re-fax these events' => - "javascript:fax_process()" - if $conf->exists('hylafax'); - -my $link_cust = sub { - my $cust_bill_event = shift; - $cust_bill_event->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; -}; - -%><%= include( 'elements/search.html', +% +% +%my $title = $cgi->param('failed') ? 'Failed invoice events' : 'Invoice events'; +% +%my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +% +%##tie my %hash, 'Tie::DxHash', +%#my %hash = ( +%# _date => { op=> '>=', value=>$beginning }, +%## i wish... +%## _date => { op=> '<=', value=>$ending }, +%#); +%#$hash{'statustext'} = { op=> '!=', value=>'' } +%# if $cgi->param('failed'); +% +%my $where = " WHERE cust_bill_event._date >= $beginning". +% " AND cust_bill_event._date <= $ending"; +% +%if ( $cgi->param('failed') ) { +% $where .= " AND statustext != '' ". +% " AND statustext IS NOT NULL ". +% " AND statustext != 'N/A' " +%} +% +%if ( $cgi->param('part_bill_event.payby') =~ /^(\w+)$/ ) { +% $where .= " AND part_bill_event.payby = '$1' "; +%} +% +%my $sql_query = { +% 'table' => 'cust_bill_event', +% #'hashref' => \%hash, +% 'hashref' => {}, +% 'select' => join(', ', +% 'cust_bill_event.*', +% 'part_bill_event.event', +% 'cust_bill.custnum', +% 'cust_bill._date AS cust_bill_date', +% 'cust_main.custnum AS cust_main_custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'extra_sql' => "$where ORDER BY _date ASC", +% 'addl_from' => 'LEFT JOIN part_bill_event USING ( eventpart ) '. +% 'LEFT JOIN cust_bill USING ( invnum ) '. +% 'LEFT JOIN cust_main USING ( custnum ) ', +%}; +% +%my $count_sql = "SELECT COUNT(*) FROM cust_bill_event ". +% "LEFT JOIN part_bill_event USING ( eventpart ) ". +% $where; +% +%my $conf = new FS::Conf; +% +%my $failed = $cgi->param('failed'); +% +%my $html_init = join("\n", map { +% ( my $action = $_ ) =~ s/_$//; +% include('/elements/progress-init.html', +% $_.'form', +% [ 'action', 'beginning', 'ending', 'failed' ], +% "../misc/${_}invoice_events.cgi", +% { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but... +% $_, #key +% ), +% qq!<FORM NAME="${_}form">!, +% qq!<INPUT TYPE="hidden" NAME="action" VALUE="$_">!, #not used though +% qq!<INPUT TYPE="hidden" NAME="beginning" VALUE="$beginning">!, +% qq!<INPUT TYPE="hidden" NAME="ending" VALUE="$ending">!, +% qq!<INPUT TYPE="hidden" NAME="failed" VALUE="$failed">!, +% qq!</FORM>! +%} qw( print_ email_ fax_ ) ); +% +%my $menubar = [ +% 'Main menu' => $p, +% 'Re-print these events' => +% "javascript:print_process()", +% 'Re-email these events' => +% "javascript:email_process()", +% ]; +% +%push @$menubar, 'Re-fax these events' => +% "javascript:fax_process()" +% if $conf->exists('hylafax'); +% +%my $link_cust = sub { +% my $cust_bill_event = shift; +% $cust_bill_event->cust_main_custnum +% ? [ "${p}view/cust_main.cgi?", 'custnum' ] +% : ''; +%}; +% +% +<% include( 'elements/search.html', 'title' => $title, 'html_init' => $html_init, 'menubar' => $menubar, diff --git a/httemplate/search/cust_bill_event.html b/httemplate/search/cust_bill_event.html index 197f28028..7cdf25112 100755 --- a/httemplate/search/cust_bill_event.html +++ b/httemplate/search/cust_bill_event.html @@ -1,4 +1,4 @@ -<%= include( +<% include( '/elements/header.html', ( $cgi->param('failed') ? 'Failed invoice events' : 'Invoice events' ), include('/elements/menubar.html', @@ -9,7 +9,7 @@ %> <FORM ACTION="cust_bill_event.cgi" METHOD="GET"> - <INPUT TYPE="hidden" NAME="failed" VALUE="<%= $cgi->param('failed') %>"> + <INPUT TYPE="hidden" NAME="failed" VALUE="<% $cgi->param('failed') %>"> <TABLE> <!--<TR> <TD ALIGN="right">Customer type</TD> @@ -23,15 +23,16 @@ </TD> </TR> --> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> + <% include( '/elements/tr-input-beginning_ending.html' ) %> <!-- <TR> <TD ALIGN="right">Events: </TD> <TD> <SELECT NAME="eventpart"> - <OPTION SELECTED VALUE=""><%= $cgi->param('failed') ? '(all failed events)' : '(all events)' %> - <% foreach my $part_bill_event ( qsearch( 'part_bill_event', {} ) ) { %> - <% } %> + <OPTION SELECTED VALUE=""><% $cgi->param('failed') ? '(all failed events)' : '(all events)' %> +% foreach my $part_bill_event ( qsearch( 'part_bill_event', {} ) ) { +% } + </SELECT> </TD> </TR> diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 4779071a4..b5289d713 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -1,130 +1,131 @@ -<% - -my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - -my $join_cust = " - JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) -"; - -my $join_pkg = " - LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) -"; - -my $where = " WHERE _date >= $beginning AND _date <= $ending "; - -$where .= " AND payby != 'COMP' " - unless $cgi->param('include_comp_cust'); - -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - $where .= " AND agentnum = $1 "; -} - -if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { - if ( $1 == 0 ) { - $where .= " AND classnum IS NULL "; - } else { - $where .= " AND classnum = $1 "; - } -} - -if ( $cgi->param('out') ) { - - $where .= " - AND 0 = ( - SELECT COUNT(*) FROM cust_main_county - WHERE ( cust_main_county.county = cust_main.county - OR ( cust_main_county.county IS NULL AND cust_main.county = '' ) - OR ( cust_main_county.county = '' AND cust_main.county IS NULL) - OR ( cust_main_county.county IS NULL AND cust_main.county IS NULL) - ) - AND ( cust_main_county.state = cust_main.state - OR ( cust_main_county.state IS NULL AND cust_main.state = '' ) - OR ( cust_main_county.state = '' AND cust_main.state IS NULL ) - OR ( cust_main_county.state IS NULL AND cust_main.state IS NULL ) - ) - AND cust_main_county.country = cust_main.country - AND cust_main_county.tax > 0 - ) - "; - -} elsif ( $cgi->param('country' ) ) { - - my $county = dbh->quote( $cgi->param('county') ); - my $state = dbh->quote( $cgi->param('state') ); - my $country = dbh->quote( $cgi->param('country') ); - $where .= " - AND ( county = $county OR $county = '' ) - AND ( state = $state OR $state = '' ) - AND country = $country - "; - $where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') ) - if $cgi->param('taxclass'); - -} - -$where .= ' AND pkgnum != 0' if $cgi->param('nottax'); - -$where .= ' AND pkgnum = 0' if $cgi->param('istax'); - -$where .= " AND tax = 'Y'" if $cgi->param('cust_tax'); - -my $count_query; -if ( $cgi->param('pkg_tax') ) { - - $count_query = - "SELECT COUNT(*), SUM( - ( CASE WHEN part_pkg.setuptax = 'Y' - THEN cust_bill_pkg.setup - ELSE 0 - END - ) - + - ( CASE WHEN part_pkg.recurtax = 'Y' - THEN cust_bill_pkg.recur - ELSE 0 - END - ) - ) - "; - - $where .= " AND ( - ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) - OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) - ) - AND ( tax != 'Y' OR tax IS NULL ) - "; - -} else { - - $count_query = - "SELECT COUNT(*), SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; - -} -$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; - -my $query = { - 'table' => 'cust_bill_pkg', - 'addl_from' => "$join_cust $join_pkg", - 'hashref' => {}, - 'select' => join(', ', - 'cust_bill_pkg.*', - 'cust_bill._date', - 'part_pkg.pkg', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => $where, -}; - -my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; -my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; - -my $conf = new FS::Conf; -my $money_char = $conf->config('money_char') || '$'; - -%><%= include( 'elements/search.html', +% +% +%my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +% +%my $join_cust = " +% JOIN cust_bill USING ( invnum ) +% LEFT JOIN cust_main USING ( custnum ) +%"; +% +%my $join_pkg = " +% LEFT JOIN cust_pkg USING ( pkgnum ) +% LEFT JOIN part_pkg USING ( pkgpart ) +%"; +% +%my $where = " WHERE _date >= $beginning AND _date <= $ending "; +% +%$where .= " AND payby != 'COMP' " +% unless $cgi->param('include_comp_cust'); +% +%if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { +% $where .= " AND agentnum = $1 "; +%} +% +%if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { +% if ( $1 == 0 ) { +% $where .= " AND classnum IS NULL "; +% } else { +% $where .= " AND classnum = $1 "; +% } +%} +% +%if ( $cgi->param('out') ) { +% +% $where .= " +% AND 0 = ( +% SELECT COUNT(*) FROM cust_main_county +% WHERE ( cust_main_county.county = cust_main.county +% OR ( cust_main_county.county IS NULL AND cust_main.county = '' ) +% OR ( cust_main_county.county = '' AND cust_main.county IS NULL) +% OR ( cust_main_county.county IS NULL AND cust_main.county IS NULL) +% ) +% AND ( cust_main_county.state = cust_main.state +% OR ( cust_main_county.state IS NULL AND cust_main.state = '' ) +% OR ( cust_main_county.state = '' AND cust_main.state IS NULL ) +% OR ( cust_main_county.state IS NULL AND cust_main.state IS NULL ) +% ) +% AND cust_main_county.country = cust_main.country +% AND cust_main_county.tax > 0 +% ) +% "; +% +%} elsif ( $cgi->param('country' ) ) { +% +% my $county = dbh->quote( $cgi->param('county') ); +% my $state = dbh->quote( $cgi->param('state') ); +% my $country = dbh->quote( $cgi->param('country') ); +% $where .= " +% AND ( county = $county OR $county = '' ) +% AND ( state = $state OR $state = '' ) +% AND country = $country +% "; +% $where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') ) +% if $cgi->param('taxclass'); +% +%} +% +%$where .= ' AND pkgnum != 0' if $cgi->param('nottax'); +% +%$where .= ' AND pkgnum = 0' if $cgi->param('istax'); +% +%$where .= " AND tax = 'Y'" if $cgi->param('cust_tax'); +% +%my $count_query; +%if ( $cgi->param('pkg_tax') ) { +% +% $count_query = +% "SELECT COUNT(*), SUM( +% ( CASE WHEN part_pkg.setuptax = 'Y' +% THEN cust_bill_pkg.setup +% ELSE 0 +% END +% ) +% + +% ( CASE WHEN part_pkg.recurtax = 'Y' +% THEN cust_bill_pkg.recur +% ELSE 0 +% END +% ) +% ) +% "; +% +% $where .= " AND ( +% ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) +% OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) +% ) +% AND ( tax != 'Y' OR tax IS NULL ) +% "; +% +%} else { +% +% $count_query = +% "SELECT COUNT(*), SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; +% +%} +%$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; +% +%my $query = { +% 'table' => 'cust_bill_pkg', +% 'addl_from' => "$join_cust $join_pkg", +% 'hashref' => {}, +% 'select' => join(', ', +% 'cust_bill_pkg.*', +% 'cust_bill._date', +% 'part_pkg.pkg', +% 'cust_main.custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'extra_sql' => $where, +%}; +% +%my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; +%my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; +% +%my $conf = new FS::Conf; +%my $money_char = $conf->config('money_char') || '$'; +% +% +<% include( 'elements/search.html', 'title' => 'Line items', 'name' => 'line items', 'query' => $query, diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index eb78db5cd..80cfc4585 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -1,70 +1,71 @@ -<% - my $title = 'Credit Search Results'; - #my( $count_query, $sql_query ); - - my @search = (); - - if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { - push @search, "cust_credit.otaker = '$1'"; - } - - if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { - push @search, "agentnum = $1"; - my $agent = qsearchs('agent', { 'agentnum' => $1 } ); - die "unknown agentnum $1" unless $agent; - $title = $agent->agent. " $title"; - } - - #false laziness with cust_pkg.cgi and cust_pay.cgi - if ( $cgi->param('beginning') - && $cgi->param('beginning') =~ /^([ 0-9\-\/]{1,10})$/ ) { - my $beginning = str2time($1); - push @search, "_date >= $beginning "; - } - if ( $cgi->param('ending') - && $cgi->param('ending') =~ /^([ 0-9\-\/]{1,10})$/ ) { - my $ending = str2time($1) + 86399; - push @search, " _date <= $ending "; - } - - if ( $cgi->param('begin') - && $cgi->param('begin') =~ /^(\d+)$/ ) { - push @search, "_date >= $1 "; - } - if ( $cgi->param('end') - && $cgi->param('end') =~ /^(\d+)$/ ) { - push @search, " _date < $1 "; - } - - #here is the agent virtualization - push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; - - my $where = 'WHERE '. join(' AND ', @search); - - my $count_query = 'SELECT COUNT(*), SUM(amount) '. - 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. - $where; - - my $sql_query = { - 'table' => 'cust_credit', - 'select' => join(', ', - 'cust_credit.*', - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'hashref' => {}, - 'extra_sql' => $where, - 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', - }; - - my $clink = sub { - my $cust_bill = shift; - $cust_bill->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; - }; - -%><%= include( 'elements/search.html', +% +% my $title = 'Credit Search Results'; +% #my( $count_query, $sql_query ); +% +% my @search = (); +% +% if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { +% push @search, "cust_credit.otaker = '$1'"; +% } +% +% if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { +% push @search, "agentnum = $1"; +% my $agent = qsearchs('agent', { 'agentnum' => $1 } ); +% die "unknown agentnum $1" unless $agent; +% $title = $agent->agent. " $title"; +% } +% +% #false laziness with cust_pkg.cgi and cust_pay.cgi +% if ( $cgi->param('beginning') +% && $cgi->param('beginning') =~ /^([ 0-9\-\/]{1,10})$/ ) { +% my $beginning = str2time($1); +% push @search, "_date >= $beginning "; +% } +% if ( $cgi->param('ending') +% && $cgi->param('ending') =~ /^([ 0-9\-\/]{1,10})$/ ) { +% my $ending = str2time($1) + 86399; +% push @search, " _date <= $ending "; +% } +% +% if ( $cgi->param('begin') +% && $cgi->param('begin') =~ /^(\d+)$/ ) { +% push @search, "_date >= $1 "; +% } +% if ( $cgi->param('end') +% && $cgi->param('end') =~ /^(\d+)$/ ) { +% push @search, " _date < $1 "; +% } +% +% #here is the agent virtualization +% push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; +% +% my $where = 'WHERE '. join(' AND ', @search); +% +% my $count_query = 'SELECT COUNT(*), SUM(amount) '. +% 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '. +% $where; +% +% my $sql_query = { +% 'table' => 'cust_credit', +% 'select' => join(', ', +% 'cust_credit.*', +% 'cust_main.custnum as cust_main_custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'hashref' => {}, +% 'extra_sql' => $where, +% 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', +% }; +% +% my $clink = sub { +% my $cust_bill = shift; +% $cust_bill->cust_main_custnum +% ? [ "${p}view/cust_main.cgi?", 'custnum' ] +% : ''; +% }; +% +% +<% include( 'elements/search.html', 'title' => $title, 'name' => 'credits', 'query' => $sql_query, diff --git a/httemplate/search/cust_main-otaker.cgi b/httemplate/search/cust_main-otaker.cgi index 6ac0bde18..210172fc0 100755 --- a/httemplate/search/cust_main-otaker.cgi +++ b/httemplate/search/cust_main-otaker.cgi @@ -1,23 +1,25 @@ -<%= include('/elements/header.html', 'Customer Search' ) %> +<% include('/elements/header.html', 'Customer Search' ) %> <FORM ACTION="cust_main.cgi" METHOD="GET"> Search for <B>Order taker</B>: <INPUT TYPE="hidden" NAME="otaker_on" VALUE="TRUE"> +% my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_main") +% or die dbh->errstr; +% $sth->execute() or die $sth->errstr; +% #my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; +% -<% my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_main") - or die dbh->errstr; - $sth->execute() or die $sth->errstr; - #my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; -%> <SELECT NAME="otaker"> -<% my $otaker; while ( $otaker = $sth->fetchrow_arrayref ) { %> - <OPTION><%= $otaker->[0] %> -<% } %> +% my $otaker; while ( $otaker = $sth->fetchrow_arrayref ) { + + <OPTION><% $otaker->[0] %> +% } + </SELECT> <P><INPUT TYPE="submit" VALUE="Search"> </FORM> -<%= include('/elements/footer.html') %> +<% include('/elements/footer.html') %> diff --git a/httemplate/search/cust_main-zip.html b/httemplate/search/cust_main-zip.html index 333a1e0b1..9790c0fef 100644 --- a/httemplate/search/cust_main-zip.html +++ b/httemplate/search/cust_main-zip.html @@ -1,89 +1,90 @@ -<% - -# XXX link to customers - -my @where = (); - -# select status - -if ( $cgi->param('status') =~ /^(prospect|uncancel|active|susp|cancel)$/ ) { - my $method = $1.'_sql'; - push @where, FS::cust_main->$method(); -} - -# select agent -# XXX this needs to be virtualized by agent too (like lots of stuff) - -my $agentnum = ''; -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - $agentnum = $1; - push @where, "cust_main.agentnum = $agentnum"; -} -my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; - -# bill zip vs ship zip - -sub fieldorempty { - my $field = shift; - "CASE WHEN $field IS NULL THEN '' ELSE $field END"; -} - -sub strip_plus4 { - my $field = shift; - "CASE WHEN $field is NULL - THEN '' - ELSE CASE WHEN $field LIKE '_____-____' - THEN SUBSTRING($field FROM 1 FOR 5) - ELSE $field - END - END"; -} - -my( $zip, $czip); -if ( $cgi->param('column') eq 'ship_zip' ) { - - my $casewhen_noship = - "CASE WHEN ( ship_last IS NULL OR ship_last = '' ) THEN "; - - $czip = "$casewhen_noship zip ELSE ship_zip END"; - - if ( $cgi->param('ignore_plus4') ) { - $zip = $casewhen_noship. strip_plus4('zip'). - " ELSE ". strip_plus4('ship_zip'). ' END'; - - } else { - $zip = $casewhen_noship. fieldorempty('zip'). - " ELSE ". fieldorempty('ship_zip'). ' END'; - } - -} else { - - $czip = 'zip'; - - if ( $cgi->param('ignore_plus4') ) { - $zip = strip_plus4('zip'); - } else { - $zip = fieldorempty('zip'); - } - -} - -# construct the queries and send 'em off - -my $sql_query = - "SELECT $zip AS zipcode, - COUNT(*) AS num_cust - FROM cust_main - $where - GROUP BY zipcode - ORDER BY num_cust DESC - "; - -my $count_sql = "select count(distinct $czip) from cust_main $where"; - -# XXX should link... - -%><%= include( 'elements/search.html', +% +% +%# XXX link to customers +% +%my @where = (); +% +%# select status +% +%if ( $cgi->param('status') =~ /^(prospect|uncancel|active|susp|cancel)$/ ) { +% my $method = $1.'_sql'; +% push @where, FS::cust_main->$method(); +%} +% +%# select agent +%# XXX this needs to be virtualized by agent too (like lots of stuff) +% +%my $agentnum = ''; +%if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { +% $agentnum = $1; +% push @where, "cust_main.agentnum = $agentnum"; +%} +%my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where) : ''; +% +%# bill zip vs ship zip +% +%sub fieldorempty { +% my $field = shift; +% "CASE WHEN $field IS NULL THEN '' ELSE $field END"; +%} +% +%sub strip_plus4 { +% my $field = shift; +% "CASE WHEN $field is NULL +% THEN '' +% ELSE CASE WHEN $field LIKE '_____-____' +% THEN SUBSTRING($field FROM 1 FOR 5) +% ELSE $field +% END +% END"; +%} +% +%my( $zip, $czip); +%if ( $cgi->param('column') eq 'ship_zip' ) { +% +% my $casewhen_noship = +% "CASE WHEN ( ship_last IS NULL OR ship_last = '' ) THEN "; +% +% $czip = "$casewhen_noship zip ELSE ship_zip END"; +% +% if ( $cgi->param('ignore_plus4') ) { +% $zip = $casewhen_noship. strip_plus4('zip'). +% " ELSE ". strip_plus4('ship_zip'). ' END'; +% +% } else { +% $zip = $casewhen_noship. fieldorempty('zip'). +% " ELSE ". fieldorempty('ship_zip'). ' END'; +% } +% +%} else { +% +% $czip = 'zip'; +% +% if ( $cgi->param('ignore_plus4') ) { +% $zip = strip_plus4('zip'); +% } else { +% $zip = fieldorempty('zip'); +% } +% +%} +% +%# construct the queries and send 'em off +% +%my $sql_query = +% "SELECT $zip AS zipcode, +% COUNT(*) AS num_cust +% FROM cust_main +% $where +% GROUP BY zipcode +% ORDER BY num_cust DESC +% "; +% +%my $count_sql = "select count(distinct $czip) from cust_main $where"; +% +%# XXX should link... +% +% +<% include( 'elements/search.html', 'title' => 'Zip code Search Results', 'name' => 'zip codes', 'query' => $sql_query, diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi index f6841a099..c484496e0 100755 --- a/httemplate/search/cust_main.cgi +++ b/httemplate/search/cust_main.cgi @@ -1,747 +1,745 @@ -<% +% +% +%my $conf = new FS::Conf; +%my $maxrecords = $conf->config('maxsearchrecordsperpage'); +% +%#my $cache; +% +%#my $monsterjoin = <<END; +%#cust_main left outer join ( +%# ( cust_pkg left outer join part_pkg using(pkgpart) +%# ) left outer join ( +%# ( +%# ( +%# ( cust_svc left outer join part_svc using (svcpart) +%# ) left outer join svc_acct using (svcnum) +%# ) left outer join svc_domain using(svcnum) +%# ) left outer join svc_forward using(svcnum) +%# ) using (pkgnum) +%#) using (custnum) +%#END +% +%#my $monsterjoin = <<END; +%#cust_main left outer join ( +%# ( cust_pkg left outer join part_pkg using(pkgpart) +%# ) left outer join ( +%# ( +%# ( +%# ( cust_svc left outer join part_svc using (svcpart) +%# ) left outer join ( +%# svc_acct left outer join ( +%# select svcnum, domain, catchall from svc_domain +%# ) as svc_acct_domsvc ( +%# svc_acct_svcnum, svc_acct_domain, svc_acct_catchall +%# ) on svc_acct.domsvc = svc_acct_domsvc.svc_acct_svcnum +%# ) using (svcnum) +%# ) left outer join svc_domain using(svcnum) +%# ) left outer join svc_forward using(svcnum) +%# ) using (pkgnum) +%#) using (custnum) +%#END +% +%my $limit = ''; +%$limit .= "LIMIT $maxrecords" if $maxrecords; +% +%my $offset = $cgi->param('offset') || 0; +%$limit .= " OFFSET $offset" if $offset; +% +%my $total = 0; +% +%my(@cust_main, $sortby, $orderby); +%my @select = (); +%my @addl_headers = (); +%my @addl_cols = (); +%if ( $cgi->param('browse') +% || $cgi->param('otaker_on') +% || $cgi->param('agentnum_on') +%) { +% +% my %search = (); +% +% if ( $cgi->param('browse') ) { +% my $query = $cgi->param('browse'); +% if ( $query eq 'custnum' ) { +% $sortby=\*custnum_sort; +% $orderby = "ORDER BY custnum"; +% } elsif ( $query eq 'last' ) { +% $sortby=\*last_sort; +% $orderby = "ORDER BY LOWER(last || ' ' || first)"; +% } elsif ( $query eq 'company' ) { +% $sortby=\*company_sort; +% $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; +% } elsif ( $query eq 'tickets' ) { +% $sortby = \*tickets_sort; +% $orderby = "ORDER BY tickets DESC"; +% push @select, FS::TicketSystem->sql_num_customer_tickets. " as tickets"; +% push @addl_headers, 'Tickets'; +% push @addl_cols, 'tickets'; +% } else { +% die "unknown browse field $query"; +% } +% } else { +% $sortby = \*last_sort; #?? +% $orderby = "ORDER BY LOWER(last || ' ' || first)"; #?? +% } +% +% if ( $cgi->param('otaker_on') ) { +% $cgi->param('otaker') =~ /^(\w{1,32})$/ or eidiot "Illegal otaker\n"; +% $search{otaker} = $1; +% } elsif ( $cgi->param('agentnum_on') ) { +% $cgi->param('agentnum') =~ /^(\d+)$/ or eidiot "Illegal agentnum\n"; +% $search{agentnum} = $1; +%# } else { +%# die "unknown query..."; +% } +% +% my @qual = (); +% +% my $ncancelled = ''; +% +% if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me +% || ( $conf->exists('hidecancelledcustomers') +% && ! $cgi->param('showcancelledcustomers') ) +% ) { +% #grep { $_->ncancelled_pkgs || ! $_->all_pkgs } +% push @qual, FS::cust_main->uncancel_sql; +% +% } +% +% push @qual, FS::cust_main->cancel_sql if $cgi->param('cancelled'); +% push @qual, FS::cust_main->prospect_sql if $cgi->param('prospect'); +% push @qual, FS::cust_main->active_sql if $cgi->param('active'); +% push @qual, FS::cust_main->inactive_sql if $cgi->param('inactive'); +% push @qual, FS::cust_main->susp_sql if $cgi->param('suspended'); +% +% #EWWWWWW +% my $qual = join(' AND ', +% map { "$_ = ". dbh->quote($search{$_}) } keys %search ); +% +% my $addl_qual = join(' AND ', @qual); +% +% #here is the agent virtualization +% $addl_qual .= ( $addl_qual ? ' AND ' : '' ). +% $FS::CurrentUser::CurrentUser->agentnums_sql; +% +% if ( $addl_qual ) { +% $qual .= ' AND ' if $qual; +% $qual .= $addl_qual; +% } +% +% $qual = " WHERE $qual" if $qual; +% my $statement = "SELECT COUNT(*) FROM cust_main $qual"; +% my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; +% $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; +% +% $total = $sth->fetchrow_arrayref->[0]; +% +% if ( $addl_qual ) { +% if ( %search ) { +% $addl_qual = " AND $addl_qual"; +% } else { +% $addl_qual = " WHERE $addl_qual"; +% } +% } +% +% my $select; +% if ( @select ) { +% $select = 'cust_main.*, '. join (', ', @select); +% } else { +% $select = '*'; +% } +% +% @cust_main = qsearch('cust_main', \%search, $select, +% "$addl_qual $orderby $limit" ); +% +%# foreach my $cust_main ( @just_cust_main ) { +%# +%# my @one_cust_main; +%# $FS::Record::DEBUG=1; +%# ( $cache, @one_cust_main ) = jsearch( +%# "$monsterjoin", +%# { 'custnum' => $cust_main->custnum }, +%# '', +%# '', +%# 'cust_main', +%# 'custnum', +%# ); +%# push @cust_main, @one_cust_main; +%# } +% +%} else { +% @cust_main=(); +% $sortby = \*last_sort; +% +% push @cust_main, @{&custnumsearch} +% if $cgi->param('custnum_on') && $cgi->param('custnum_text'); +% push @cust_main, @{&cardsearch} +% if $cgi->param('card_on') && $cgi->param('card'); +% push @cust_main, @{&lastsearch} +% if $cgi->param('last_on') && $cgi->param('last_text'); +% push @cust_main, @{&companysearch} +% if $cgi->param('company_on') && $cgi->param('company_text'); +% push @cust_main, @{&address2search} +% if $cgi->param('address2_on') && $cgi->param('address2_text'); +% push @cust_main, @{&phonesearch} +% if $cgi->param('phone_on') && $cgi->param('phone_text'); +% push @cust_main, @{&referralsearch} +% if $cgi->param('referral_custnum'); +% +% if ( $cgi->param('company_on') && $cgi->param('company_text') ) { +% $sortby = \*company_sort; +% push @cust_main, @{&companysearch}; +% } +% +% if ( $cgi->param('search_cust') ) { +% $sortby = \*company_sort; +% $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; +% warn "smart searching for: ". $cgi->param('search_cust'); +% push @cust_main, smart_search( 'search' => $cgi->param('search_cust') ); +% } +% +% @cust_main = grep { $_->ncancelled_pkgs || ! $_->all_pkgs } @cust_main +% if ! $cgi->param('cancelled') +% && ( +% $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me +% || ( $conf->exists('hidecancelledcustomers') +% && ! $cgi->param('showcancelledcustomers') ) +% ); +% +% my %saw = (); +% @cust_main = grep { !$saw{$_->custnum}++ } @cust_main; +%} +% +%my %all_pkgs; +%if ( $conf->exists('hidecancelledpackages' ) ) { +% %all_pkgs = map { $_->custnum => [ $_->ncancelled_pkgs ] } @cust_main; +%} else { +% %all_pkgs = map { $_->custnum => [ $_->all_pkgs ] } @cust_main; +%} +%#%all_pkgs = (); +% +%if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { +% if ( $cgi->param('quickpay') eq 'yes' ) { +% print $cgi->redirect(popurl(2). "edit/cust_pay.cgi?quickpay=yes;custnum=". $cust_main[0]->custnum); +% } else { +% print $cgi->redirect(popurl(2). "view/cust_main.cgi?". $cust_main[0]->custnum); +% } +% #exit; +%} elsif ( scalar(@cust_main) == 0 ) { +% -my $conf = new FS::Conf; -my $maxrecords = $conf->config('maxsearchrecordsperpage'); - -#my $cache; - -#my $monsterjoin = <<END; -#cust_main left outer join ( -# ( cust_pkg left outer join part_pkg using(pkgpart) -# ) left outer join ( -# ( -# ( -# ( cust_svc left outer join part_svc using (svcpart) -# ) left outer join svc_acct using (svcnum) -# ) left outer join svc_domain using(svcnum) -# ) left outer join svc_forward using(svcnum) -# ) using (pkgnum) -#) using (custnum) -#END - -#my $monsterjoin = <<END; -#cust_main left outer join ( -# ( cust_pkg left outer join part_pkg using(pkgpart) -# ) left outer join ( -# ( -# ( -# ( cust_svc left outer join part_svc using (svcpart) -# ) left outer join ( -# svc_acct left outer join ( -# select svcnum, domain, catchall from svc_domain -# ) as svc_acct_domsvc ( -# svc_acct_svcnum, svc_acct_domain, svc_acct_catchall -# ) on svc_acct.domsvc = svc_acct_domsvc.svc_acct_svcnum -# ) using (svcnum) -# ) left outer join svc_domain using(svcnum) -# ) left outer join svc_forward using(svcnum) -# ) using (pkgnum) -#) using (custnum) -#END - -my $limit = ''; -$limit .= "LIMIT $maxrecords" if $maxrecords; - -my $offset = $cgi->param('offset') || 0; -$limit .= " OFFSET $offset" if $offset; - -my $total = 0; - -my(@cust_main, $sortby, $orderby); -my @select = (); -my @addl_headers = (); -my @addl_cols = (); -if ( $cgi->param('browse') - || $cgi->param('otaker_on') - || $cgi->param('agentnum_on') -) { - - my %search = (); - - if ( $cgi->param('browse') ) { - my $query = $cgi->param('browse'); - if ( $query eq 'custnum' ) { - $sortby=\*custnum_sort; - $orderby = "ORDER BY custnum"; - } elsif ( $query eq 'last' ) { - $sortby=\*last_sort; - $orderby = "ORDER BY LOWER(last || ' ' || first)"; - } elsif ( $query eq 'company' ) { - $sortby=\*company_sort; - $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; - } elsif ( $query eq 'tickets' ) { - $sortby = \*tickets_sort; - $orderby = "ORDER BY tickets DESC"; - push @select, FS::TicketSystem->sql_num_customer_tickets. " as tickets"; - push @addl_headers, 'Tickets'; - push @addl_cols, 'tickets'; - } else { - die "unknown browse field $query"; - } - } else { - $sortby = \*last_sort; #?? - $orderby = "ORDER BY LOWER(last || ' ' || first)"; #?? - } - - if ( $cgi->param('otaker_on') ) { - $cgi->param('otaker') =~ /^(\w{1,32})$/ or eidiot "Illegal otaker\n"; - $search{otaker} = $1; - } elsif ( $cgi->param('agentnum_on') ) { - $cgi->param('agentnum') =~ /^(\d+)$/ or eidiot "Illegal agentnum\n"; - $search{agentnum} = $1; -# } else { -# die "unknown query..."; - } - - my @qual = (); - - my $ncancelled = ''; - - if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me - || ( $conf->exists('hidecancelledcustomers') - && ! $cgi->param('showcancelledcustomers') ) - ) { - #grep { $_->ncancelled_pkgs || ! $_->all_pkgs } - push @qual, FS::cust_main->uncancel_sql; - - } - - push @qual, FS::cust_main->cancel_sql if $cgi->param('cancelled'); - push @qual, FS::cust_main->prospect_sql if $cgi->param('prospect'); - push @qual, FS::cust_main->active_sql if $cgi->param('active'); - push @qual, FS::cust_main->inactive_sql if $cgi->param('inactive'); - push @qual, FS::cust_main->susp_sql if $cgi->param('suspended'); - - #EWWWWWW - my $qual = join(' AND ', - map { "$_ = ". dbh->quote($search{$_}) } keys %search ); - - my $addl_qual = join(' AND ', @qual); - - #here is the agent virtualization - $addl_qual .= ( $addl_qual ? ' AND ' : '' ). - $FS::CurrentUser::CurrentUser->agentnums_sql; - - if ( $addl_qual ) { - $qual .= ' AND ' if $qual; - $qual .= $addl_qual; - } - - $qual = " WHERE $qual" if $qual; - my $statement = "SELECT COUNT(*) FROM cust_main $qual"; - my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement"; - $sth->execute or die "Error executing \"$statement\": ". $sth->errstr; - - $total = $sth->fetchrow_arrayref->[0]; - - if ( $addl_qual ) { - if ( %search ) { - $addl_qual = " AND $addl_qual"; - } else { - $addl_qual = " WHERE $addl_qual"; - } - } - - my $select; - if ( @select ) { - $select = 'cust_main.*, '. join (', ', @select); - } else { - $select = '*'; - } - - @cust_main = qsearch('cust_main', \%search, $select, - "$addl_qual $orderby $limit" ); - -# foreach my $cust_main ( @just_cust_main ) { -# -# my @one_cust_main; -# $FS::Record::DEBUG=1; -# ( $cache, @one_cust_main ) = jsearch( -# "$monsterjoin", -# { 'custnum' => $cust_main->custnum }, -# '', -# '', -# 'cust_main', -# 'custnum', -# ); -# push @cust_main, @one_cust_main; -# } - -} else { - @cust_main=(); - $sortby = \*last_sort; - - push @cust_main, @{&custnumsearch} - if $cgi->param('custnum_on') && $cgi->param('custnum_text'); - push @cust_main, @{&cardsearch} - if $cgi->param('card_on') && $cgi->param('card'); - push @cust_main, @{&lastsearch} - if $cgi->param('last_on') && $cgi->param('last_text'); - push @cust_main, @{&companysearch} - if $cgi->param('company_on') && $cgi->param('company_text'); - push @cust_main, @{&address2search} - if $cgi->param('address2_on') && $cgi->param('address2_text'); - push @cust_main, @{&phonesearch} - if $cgi->param('phone_on') && $cgi->param('phone_text'); - push @cust_main, @{&referralsearch} - if $cgi->param('referral_custnum'); - - if ( $cgi->param('company_on') && $cgi->param('company_text') ) { - $sortby = \*company_sort; - push @cust_main, @{&companysearch}; - } - - if ( $cgi->param('search_cust') ) { - $sortby = \*company_sort; - $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )"; - warn "smart searching for: ". $cgi->param('search_cust'); - push @cust_main, smart_search( 'search' => $cgi->param('search_cust') ); - } - - @cust_main = grep { $_->ncancelled_pkgs || ! $_->all_pkgs } @cust_main - if ! $cgi->param('cancelled') - && ( - $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me - || ( $conf->exists('hidecancelledcustomers') - && ! $cgi->param('showcancelledcustomers') ) - ); - - my %saw = (); - @cust_main = grep { !$saw{$_->custnum}++ } @cust_main; -} - -my %all_pkgs; -if ( $conf->exists('hidecancelledpackages' ) ) { - %all_pkgs = map { $_->custnum => [ $_->ncancelled_pkgs ] } @cust_main; -} else { - %all_pkgs = map { $_->custnum => [ $_->all_pkgs ] } @cust_main; -} -#%all_pkgs = (); - -if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) { - if ( $cgi->param('quickpay') eq 'yes' ) { - print $cgi->redirect(popurl(2). "edit/cust_pay.cgi?quickpay=yes;custnum=". $cust_main[0]->custnum); - } else { - print $cgi->redirect(popurl(2). "view/cust_main.cgi?". $cust_main[0]->custnum); - } - #exit; -} elsif ( scalar(@cust_main) == 0 ) { -%> <!-- mason kludge --> -<% - eidiot "No matching customers found!\n"; -} else { -%> -<%= include('/elements/header.html', "Customer Search Results", '' ) %> - - <% $total ||= scalar(@cust_main); %> - - <%= $total %> matching customers found - - <% - #begin pager - my $pager = ''; - if ( $total != scalar(@cust_main) && $maxrecords ) { - unless ( $offset == 0 ) { - $cgi->param('offset', $offset - $maxrecords); - $pager .= '<A HREF="'. $cgi->self_url. - '"><B><FONT SIZE="+1">Previous</FONT></B></A> '; - } - my $poff; - my $page; - for ( $poff = 0; $poff < $total; $poff += $maxrecords ) { - $page++; - if ( $offset == $poff ) { - $pager .= qq!<FONT SIZE="+2">$page</FONT> !; - } else { - $cgi->param('offset', $poff); - $pager .= qq!<A HREF="!. $cgi->self_url. qq!">$page</A> !; - } - } - unless ( $offset + $maxrecords > $total ) { - $cgi->param('offset', $offset + $maxrecords); - $pager .= '<A HREF="'. $cgi->self_url. - '"><B><FONT SIZE="+1">Next</FONT></B></A> '; - } - } - #end pager - - unless ( $cgi->param('cancelled') ) { - if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me - || ( $conf->exists('hidecancelledcustomers') - && ! $cgi->param('showcancelledcustomers') - ) - ) { - $cgi->param('showcancelledcustomers', 1); - $cgi->param('offset', 0); - print qq!( <a href="!. $cgi->self_url. qq!">show!; - } else { - $cgi->param('showcancelledcustomers', 0); - $cgi->param('offset', 0); - print qq!( <a href="!. $cgi->self_url. qq!">hide!; - } - print ' cancelled customers</a> )'; - } - - if ( $cgi->param('referral_custnum') ) { - $cgi->param('referral_custnum') =~ /^(\d+)$/ - or eidiot "Illegal referral_custnum\n"; - my $referral_custnum = $1; - my $cust_main = qsearchs('cust_main', { custnum => $referral_custnum } ); - print '<FORM METHOD="GET">'. - qq!<INPUT TYPE="hidden" NAME="referral_custnum" VALUE="$referral_custnum">!. - 'referrals of <A HREF="'. popurl(2). - "view/cust_main.cgi?$referral_custnum\">$referral_custnum: ". - ( $cust_main->company - || $cust_main->last. ', '. $cust_main->first ). - '</A>'; - print "\n",<<END; - <SCRIPT> - function changed(what) { - what.form.submit(); - } - </SCRIPT> -END - print ' <SELECT NAME="referral_depth" SIZE="1" onChange="changed(this)">'; - my $max = 8; #config file - $cgi->param('referral_depth') =~ /^(\d*)$/ - or eidiot "Illegal referral_depth"; - my $referral_depth = $1; - - foreach my $depth ( 1 .. $max ) { - print '<OPTION', - ' SELECTED'x($depth == $referral_depth), - ">$depth"; - } - print "</SELECT> levels deep". - '<NOSCRIPT> <INPUT TYPE="submit" VALUE="change"></NOSCRIPT>'. - '</FORM>'; - } - - my @custom_priorities = (); - if ( $conf->config('ticket_system-custom_priority_field') - && @{[ $conf->config('ticket_system-custom_priority_field-values') ]} ) { - @custom_priorities = - $conf->config('ticket_system-custom_priority_field-values'); - } - - print "<BR><BR>". $pager. include('/elements/table-grid.html'). <<END; - <TR> - <TH CLASS="grid" BGCOLOR="#cccccc"></TH> - <TH CLASS="grid" BGCOLOR="#cccccc">(bill) name</TH> - <TH CLASS="grid" BGCOLOR="#cccccc">company</TH> -END - -if ( defined dbdef->table('cust_main')->column('ship_last') ) { - print <<END; - <TH CLASS="grid" BGCOLOR="#cccccc">(service) name</TH> - <TH CLASS="grid" BGCOLOR="#cccccc">company</TH> -END -} - -foreach my $addl_header ( @addl_headers ) { - print '<TH CLASS="grid" BGCOLOR="#cccccc">'. "$addl_header</TH>"; -} - -print <<END; - <TH CLASS="grid" BGCOLOR="#cccccc">Packages</TH> - <TH CLASS="grid" BGCOLOR="#cccccc" COLSPAN=2>Services</TH> - </TR> -END +% +% eidiot "No matching customers found!\n"; +%} else { +% + +<% include('/elements/header.html', "Customer Search Results", '' ) %> +% $total ||= scalar(@cust_main); + + + <% $total %> matching customers found +% +% #begin pager +% my $pager = ''; +% if ( $total != scalar(@cust_main) && $maxrecords ) { +% unless ( $offset == 0 ) { +% $cgi->param('offset', $offset - $maxrecords); +% $pager .= '<A HREF="'. $cgi->self_url. +% '"><B><FONT SIZE="+1">Previous</FONT></B></A> '; +% } +% my $poff; +% my $page; +% for ( $poff = 0; $poff < $total; $poff += $maxrecords ) { +% $page++; +% if ( $offset == $poff ) { +% $pager .= qq!<FONT SIZE="+2">$page</FONT> !; +% } else { +% $cgi->param('offset', $poff); +% $pager .= qq!<A HREF="!. $cgi->self_url. qq!">$page</A> !; +% } +% } +% unless ( $offset + $maxrecords > $total ) { +% $cgi->param('offset', $offset + $maxrecords); +% $pager .= '<A HREF="'. $cgi->self_url. +% '"><B><FONT SIZE="+1">Next</FONT></B></A> '; +% } +% } +% #end pager +% +% unless ( $cgi->param('cancelled') ) { +% if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me +% || ( $conf->exists('hidecancelledcustomers') +% && ! $cgi->param('showcancelledcustomers') +% ) +% ) { +% $cgi->param('showcancelledcustomers', 1); +% $cgi->param('offset', 0); +% print qq!( <a href="!. $cgi->self_url. qq!">show!; +% } else { +% $cgi->param('showcancelledcustomers', 0); +% $cgi->param('offset', 0); +% print qq!( <a href="!. $cgi->self_url. qq!">hide!; +% } +% print ' cancelled customers</a> )'; +% } +% +% if ( $cgi->param('referral_custnum') ) { +% $cgi->param('referral_custnum') =~ /^(\d+)$/ +% or eidiot "Illegal referral_custnum\n"; +% my $referral_custnum = $1; +% my $cust_main = qsearchs('cust_main', { custnum => $referral_custnum } ); +% print '<FORM METHOD="GET">'. +% qq!<INPUT TYPE="hidden" NAME="referral_custnum" VALUE="$referral_custnum">!. +% 'referrals of <A HREF="'. popurl(2). +% "view/cust_main.cgi?$referral_custnum\">$referral_custnum: ". +% ( $cust_main->company +% || $cust_main->last. ', '. $cust_main->first ). +% '</A>'; +% print "\n",<<END; +% <SCRIPT> +% function changed(what) { +% what.form.submit(); +% } +% </SCRIPT> +%END +% print ' <SELECT NAME="referral_depth" SIZE="1" onChange="changed(this)">'; +% my $max = 8; #config file +% $cgi->param('referral_depth') =~ /^(\d*)$/ +% or eidiot "Illegal referral_depth"; +% my $referral_depth = $1; +% +% foreach my $depth ( 1 .. $max ) { +% print '<OPTION', +% ' SELECTED'x($depth == $referral_depth), +% ">$depth"; +% } +% print "</SELECT> levels deep". +% '<NOSCRIPT> <INPUT TYPE="submit" VALUE="change"></NOSCRIPT>'. +% '</FORM>'; +% } +% +% my @custom_priorities = (); +% if ( $conf->config('ticket_system-custom_priority_field') +% && @{[ $conf->config('ticket_system-custom_priority_field-values') ]} ) { +% @custom_priorities = +% $conf->config('ticket_system-custom_priority_field-values'); +% } +% +% print "<BR><BR>". $pager. include('/elements/table-grid.html'). <<END; +% <TR> +% <TH CLASS="grid" BGCOLOR="#cccccc"></TH> +% <TH CLASS="grid" BGCOLOR="#cccccc">(bill) name</TH> +% <TH CLASS="grid" BGCOLOR="#cccccc">company</TH> +%END +% +%if ( defined dbdef->table('cust_main')->column('ship_last') ) { +% print <<END; +% <TH CLASS="grid" BGCOLOR="#cccccc">(service) name</TH> +% <TH CLASS="grid" BGCOLOR="#cccccc">company</TH> +%END +%} +% +%foreach my $addl_header ( @addl_headers ) { +% print '<TH CLASS="grid" BGCOLOR="#cccccc">'. "$addl_header</TH>"; +%} +% +%print <<END; +% <TH CLASS="grid" BGCOLOR="#cccccc">Packages</TH> +% <TH CLASS="grid" BGCOLOR="#cccccc" COLSPAN=2>Services</TH> +% </TR> +%END +% +% my $bgcolor1 = '#eeeeee'; +% my $bgcolor2 = '#ffffff'; +% my $bgcolor; +% +% my(%saw,$cust_main); +% foreach $cust_main ( +% sort $sortby grep(!$saw{$_->custnum}++, @cust_main) +% ) { +% +% if ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } +% +% my($custnum,$last,$first,$company)=( +% $cust_main->custnum, +% $cust_main->getfield('last'), +% $cust_main->getfield('first'), +% $cust_main->company, +% ); +% +% my(@lol_cust_svc); +% my($rowspan)=0;#scalar( @{$all_pkgs{$custnum}} ); +% foreach ( @{$all_pkgs{$custnum}} ) { +% #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); +% my @cust_svc = $_->cust_svc; +% push @lol_cust_svc, \@cust_svc; +% $rowspan += scalar(@cust_svc) || 1; +% } +% +% #my($rowspan) = scalar(@{$all_pkgs{$custnum}}); +% my $view; +% if ( defined $cgi->param('quickpay') && $cgi->param('quickpay') eq 'yes' ) { +% $view = $p. 'edit/cust_pay.cgi?quickpay=yes;custnum='. $custnum; +% } else { +% $view = $p. 'view/cust_main.cgi?'. $custnum; +% } +% my $pcompany = $company +% ? qq!<A HREF="$view"><FONT SIZE=-1>$company</FONT></A>! +% : '<FONT SIZE=-1> </FONT>'; +% - my $bgcolor1 = '#eeeeee'; - my $bgcolor2 = '#ffffff'; - my $bgcolor; - - my(%saw,$cust_main); - foreach $cust_main ( - sort $sortby grep(!$saw{$_->custnum}++, @cust_main) - ) { - - if ( $bgcolor eq $bgcolor1 ) { - $bgcolor = $bgcolor2; - } else { - $bgcolor = $bgcolor1; - } - - my($custnum,$last,$first,$company)=( - $cust_main->custnum, - $cust_main->getfield('last'), - $cust_main->getfield('first'), - $cust_main->company, - ); - - my(@lol_cust_svc); - my($rowspan)=0;#scalar( @{$all_pkgs{$custnum}} ); - foreach ( @{$all_pkgs{$custnum}} ) { - #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); - my @cust_svc = $_->cust_svc; - push @lol_cust_svc, \@cust_svc; - $rowspan += scalar(@cust_svc) || 1; - } - - #my($rowspan) = scalar(@{$all_pkgs{$custnum}}); - my $view; - if ( defined $cgi->param('quickpay') && $cgi->param('quickpay') eq 'yes' ) { - $view = $p. 'edit/cust_pay.cgi?quickpay=yes;custnum='. $custnum; - } else { - $view = $p. 'view/cust_main.cgi?'. $custnum; - } - my $pcompany = $company - ? qq!<A HREF="$view"><FONT SIZE=-1>$company</FONT></A>! - : '<FONT SIZE=-1> </FONT>'; - %> <TR> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ROWSPAN=<%= $rowspan || 1 %>><A HREF="<%= $view %>"><FONT SIZE=-1><%= $custnum %></FONT></A></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ROWSPAN=<%= $rowspan || 1 %>><A HREF="<%= $view %>"><FONT SIZE=-1><%= "$last, $first" %></FONT></A></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ROWSPAN=<%= $rowspan || 1 %>><%= $pcompany %></TD> - - <% - if ( defined dbdef->table('cust_main')->column('ship_last') ) { - my($ship_last,$ship_first,$ship_company)=( - $cust_main->ship_last || $cust_main->getfield('last'), - $cust_main->ship_last ? $cust_main->ship_first : $cust_main->first, - $cust_main->ship_last ? $cust_main->ship_company : $cust_main->company, - ); - my $pship_company = $ship_company - ? qq!<A HREF="$view"><FONT SIZE=-1>$ship_company</FONT></A>! - : '<FONT SIZE=-1> </FONT>'; - %> - - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ROWSPAN=<%= $rowspan || 1 %>><A HREF="<%= $view %>"><FONT SIZE=-1><%= "$ship_last, $ship_first" %></FONT></A></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ROWSPAN=<%= $rowspan || 1 %>><%= $pship_company %></A></TD> - - <% } - - foreach my $addl_col ( @addl_cols ) { %> - - <% if ( $addl_col eq 'tickets' ) { %> - - <% if ( @custom_priorities ) { %> - - <TD CLASS="inv" BGCOLOR="<%= $bgcolor %>" ROWSPAN=<%= $rowspan || 1 %> ALIGN=right><FONT SIZE=-1> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan || 1 %>><A HREF="<% $view %>"><FONT SIZE=-1><% $custnum %></FONT></A></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan || 1 %>><A HREF="<% $view %>"><FONT SIZE=-1><% "$last, $first" %></FONT></A></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan || 1 %>><% $pcompany %></TD> +% +% if ( defined dbdef->table('cust_main')->column('ship_last') ) { +% my($ship_last,$ship_first,$ship_company)=( +% $cust_main->ship_last || $cust_main->getfield('last'), +% $cust_main->ship_last ? $cust_main->ship_first : $cust_main->first, +% $cust_main->ship_last ? $cust_main->ship_company : $cust_main->company, +% ); +% my $pship_company = $ship_company +% ? qq!<A HREF="$view"><FONT SIZE=-1>$ship_company</FONT></A>! +% : '<FONT SIZE=-1> </FONT>'; +% + + + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan || 1 %>><A HREF="<% $view %>"><FONT SIZE=-1><% "$ship_last, $ship_first" %></FONT></A></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan || 1 %>><% $pship_company %></A></TD> +% } +% +% foreach my $addl_col ( @addl_cols ) { +% if ( $addl_col eq 'tickets' ) { +% if ( @custom_priorities ) { + + + <TD CLASS="inv" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan || 1 %> ALIGN=right><FONT SIZE=-1> <TABLE CLASS="inv" CELLSPACING=0 CELLPADDING=0> +% foreach my $priority ( @custom_priorities, '' ) { +% +% my $num = +% FS::TicketSystem->num_customer_tickets($custnum,$priority); +% my $ahref = ''; +% $ahref= '<A HREF="'. +% FS::TicketSystem->href_customer_tickets($custnum,$priority). +% '">' +% if $num; +% - <% foreach my $priority ( @custom_priorities, '' ) { %> - - <% - my $num = - FS::TicketSystem->num_customer_tickets($custnum,$priority); - my $ahref = ''; - $ahref= '<A HREF="'. - FS::TicketSystem->href_customer_tickets($custnum,$priority). - '">' - if $num; - %> <TR> <TD ALIGN=right> - <FONT SIZE=-1><%= $ahref.$num %></A></FONT> + <FONT SIZE=-1><% $ahref.$num %></A></FONT> </TD> <TD ALIGN=left> - <FONT SIZE=-1><%= $ahref %><%= $priority || '<i>(none)</i>' %></A></FONT> + <FONT SIZE=-1><% $ahref %><% $priority || '<i>(none)</i>' %></A></FONT> </TD> </TR> - - <% } %> +% } + <TR> <TH ALIGN=right STYLE="border-top: dashed 1px black"> <FONT SIZE=-1> +% } else { - <% } else { %> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ROWSPAN=<%= $rowspan || 1 %> ALIGN=right><FONT SIZE=-1> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan || 1 %> ALIGN=right><FONT SIZE=-1> +% } +% +% my $ahref = ''; +% $ahref = '<A HREF="'. +% FS::TicketSystem->href_customer_tickets($custnum). +% '">' +% if $cust_main->get($addl_col); +% - <% } %> - <% - my $ahref = ''; - $ahref = '<A HREF="'. - FS::TicketSystem->href_customer_tickets($custnum). - '">' - if $cust_main->get($addl_col); - %> + <% $ahref %><% $cust_main->get($addl_col) %></A> +% if ( @custom_priorities ) { - <%= $ahref %><%= $cust_main->get($addl_col) %></A> - - <% if ( @custom_priorities ) { %> </FONT></TH> <TH ALIGN=left STYLE="border-top: dashed 1px black"> - <FONT SIZE=-1><%= ${ahref} %>Total</A><FONT> + <FONT SIZE=-1><% ${ahref} %>Total</A><FONT> </TH> </TR> </TABLE> +% } - <% } %> </FONT></TD> +% } else { - <% } else { %> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ROWSPAN=<%= $rowspan || 1 %> ALIGN=right><FONT SIZE=-1> - <%= $cust_main->get($addl_col) %> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan || 1 %> ALIGN=right><FONT SIZE=-1> + <% $cust_main->get($addl_col) %> </FONT></TD> +% +% } +% } +% +% my($n1)=''; +% foreach ( @{$all_pkgs{$custnum}} ) { +% my $pkgnum = $_->pkgnum; +%# my $part_pkg = qsearchs( 'part_pkg', { pkgpart => $_->pkgpart } ); +% my $part_pkg = $_->part_pkg; +% +% my $pkg = $part_pkg->pkg; +% my $comment = $part_pkg->comment; +% my $pkgview = "${p}view/cust_main.cgi?$custnum#cust_pkg$pkgnum"; +% my @cust_svc = @{shift @lol_cust_svc}; +% #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); +% my $rowspan = scalar(@cust_svc) || 1; +% +% print $n1, qq!<TD CLASS="grid" BGCOLOR="$bgcolor" ROWSPAN=$rowspan><A HREF="$pkgview"><FONT SIZE=-1>$pkg - $comment</FONT></A></TD>!; +% +% my($n2)=''; +% foreach my $cust_svc ( @cust_svc ) { +% my($label, $value, $svcdb) = $cust_svc->label; +% my($svcnum) = $cust_svc->svcnum; +% my($sview) = $p.'view'; +% print $n2,qq!<TD CLASS="grid" BGCOLOR="$bgcolor" ><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!, +% qq!<TD CLASS="grid" BGCOLOR="$bgcolor" ><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!; +% $n2="</TR><TR>"; +% } +% +% unless ( @cust_svc ) { +% print qq!<TD CLASS="grid" BGCOLOR="$bgcolor" COLSPAN=2> </TD>!; +% } +% +% #print qq!</TR><TR>\n!; +% $n1="</TR><TR>"; +% } +% +% unless ( @{$all_pkgs{$custnum}} ) { +% print qq!<TD CLASS="grid" BGCOLOR="$bgcolor" COLSPAN=3> </TD>!; +% } +% +% print "</TR>"; +% } +% +% -<% - } - } - - my($n1)=''; - foreach ( @{$all_pkgs{$custnum}} ) { - my $pkgnum = $_->pkgnum; -# my $part_pkg = qsearchs( 'part_pkg', { pkgpart => $_->pkgpart } ); - my $part_pkg = $_->part_pkg; - - my $pkg = $part_pkg->pkg; - my $comment = $part_pkg->comment; - my $pkgview = "${p}view/cust_main.cgi?$custnum#cust_pkg$pkgnum"; - my @cust_svc = @{shift @lol_cust_svc}; - #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } ); - my $rowspan = scalar(@cust_svc) || 1; - - print $n1, qq!<TD CLASS="grid" BGCOLOR="$bgcolor" ROWSPAN=$rowspan><A HREF="$pkgview"><FONT SIZE=-1>$pkg - $comment</FONT></A></TD>!; - - my($n2)=''; - foreach my $cust_svc ( @cust_svc ) { - my($label, $value, $svcdb) = $cust_svc->label; - my($svcnum) = $cust_svc->svcnum; - my($sview) = $p.'view'; - print $n2,qq!<TD CLASS="grid" BGCOLOR="$bgcolor" ><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!, - qq!<TD CLASS="grid" BGCOLOR="$bgcolor" ><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!; - $n2="</TR><TR>"; - } - - unless ( @cust_svc ) { - print qq!<TD CLASS="grid" BGCOLOR="$bgcolor" COLSPAN=2> </TD>!; - } - - #print qq!</TR><TR>\n!; - $n1="</TR><TR>"; - } - - unless ( @{$all_pkgs{$custnum}} ) { - print qq!<TD CLASS="grid" BGCOLOR="$bgcolor" COLSPAN=3> </TD>!; - } - - print "</TR>"; - } - - %> - </TABLE><%= $pager %> - - <%= include('/elements/footer.html') %> - -<% } - -#undef $cache; #does this help? - -# - -sub last_sort { - lc($a->getfield('last')) cmp lc($b->getfield('last')) - || lc($a->first) cmp lc($b->first); -} - -sub company_sort { - return -1 if $a->company && ! $b->company; - return 1 if ! $a->company && $b->company; - lc($a->company) cmp lc($b->company) - || lc($a->getfield('last')) cmp lc($b->getfield('last')) - || lc($a->first) cmp lc($b->first);; -} - -sub custnum_sort { - $a->getfield('custnum') <=> $b->getfield('custnum'); -} - -sub tickets_sort { - $b->getfield('tickets') <=> $a->getfield('tickets'); -} - -sub custnumsearch { - - my $custnum = $cgi->param('custnum_text'); - $custnum =~ s/\D//g; - $custnum =~ /^(\d{1,23})$/ or eidiot "Illegal customer number\n"; - $custnum = $1; - - [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; -} - -sub cardsearch { - - my($card)=$cgi->param('card'); - $card =~ s/\D//g; - $card =~ /^(\d{13,16})$/ or eidiot "Illegal card number\n"; - my($payinfo)=$1; - - [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}), - qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'}) - ]; -} - -sub referralsearch { - $cgi->param('referral_custnum') =~ /^(\d+)$/ - or eidiot "Illegal referral_custnum"; - my $cust_main = qsearchs('cust_main', { 'custnum' => $1 } ) - or eidiot "Customer $1 not found"; - my $depth; - if ( $cgi->param('referral_depth') ) { - $cgi->param('referral_depth') =~ /^(\d+)$/ - or eidiot "Illegal referral_depth"; - $depth = $1; - } else { - $depth = 1; - } - [ $cust_main->referral_cust_main($depth) ]; -} - -sub lastsearch { - my(%last_type); - my @cust_main; - foreach ( $cgi->param('last_type') ) { - $last_type{$_}++; - } - - $cgi->param('last_text') =~ /^([\w \,\.\-\']*)$/ - or eidiot "Illegal last name"; - my($last)=$1; - - if ( $last_type{'Exact'} || $last_type{'Fuzzy'} ) { - push @cust_main, qsearch( 'cust_main', - { 'last' => { 'op' => 'ILIKE', - 'value' => $last } } ); - - push @cust_main, qsearch( 'cust_main', - { 'ship_last' => { 'op' => 'ILIKE', - 'value' => $last } } ) - if defined dbdef->table('cust_main')->column('ship_last'); - } - - if ( $last_type{'Substring'} || $last_type{'All'} ) { - - push @cust_main, qsearch( 'cust_main', - { 'last' => { 'op' => 'ILIKE', - 'value' => "%$last%" } } ); - - push @cust_main, qsearch( 'cust_main', - { 'ship_last' => { 'op' => 'ILIKE', - 'value' => "%$last%" } } ) - if defined dbdef->table('cust_main')->column('ship_last'); - - } - - if ( $last_type{'Fuzzy'} || $last_type{'All'} ) { - push @cust_main, FS::cust_main->fuzzy_search( { 'last' => $last } ); - } - - #if ($last_type{'Sound-alike'}) { - #} - - \@cust_main; -} - -sub companysearch { - - my(%company_type); - my @cust_main; - foreach ( $cgi->param('company_type') ) { - $company_type{$_}++ - }; - - $cgi->param('company_text') =~ - /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ - or eidiot "Illegal company"; - my $company = $1; - - if ( $company_type{'Exact'} || $company_type{'Fuzzy'} ) { - push @cust_main, qsearch( 'cust_main', - { 'company' => { 'op' => 'ILIKE', - 'value' => $company } } ); - - push @cust_main, qsearch( 'cust_main', - { 'ship_company' => { 'op' => 'ILIKE', - 'value' => $company } } ) - if defined dbdef->table('cust_main')->column('ship_last'); - } - - if ( $company_type{'Substring'} || $company_type{'All'} ) { - - push @cust_main, qsearch( 'cust_main', - { 'company' => { 'op' => 'ILIKE', - 'value' => "%$company%" } } ); - - push @cust_main, qsearch( 'cust_main', - { 'ship_company' => { 'op' => 'ILIKE', - 'value' => "%$company%" } }) - if defined dbdef->table('cust_main')->column('ship_last'); - - } - - if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { - push @cust_main, FS::cust_main->fuzzy_search( { 'company' => $company } ); - } - - if ($company_type{'Sound-alike'}) { - } - - \@cust_main; -} - -sub address2search { - my @cust_main; - - $cgi->param('address2_text') =~ - /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ - or eidiot "Illegal address2"; - my $address2 = $1; - - push @cust_main, qsearch( 'cust_main', - { 'address2' => { 'op' => 'ILIKE', - 'value' => $address2 } } ); - push @cust_main, qsearch( 'cust_main', - { 'address2' => { 'op' => 'ILIKE', - 'value' => $address2 } } ) - if defined dbdef->table('cust_main')->column('ship_last'); - - \@cust_main; -} - -sub phonesearch { - my @cust_main; - - my $phone = $cgi->param('phone_text'); - - #(no longer really) false laziness with Record::ut_phonen - #only works with US/CA numbers... - $phone =~ s/\D//g; - if ( $phone =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ ) { - $phone = "$1-$2-$3"; - $phone .= " x$4" if $4; - } elsif ( $phone =~ /^(\d{3})(\d{4})$/ ) { - $phone = "$1-$2"; - } elsif ( $phone =~ /^(\d{3,4})$/ ) { - $phone = $1; - } else { - eidiot gettext('illegal_phone'). ": $phone"; - } - - my @fields = qw(daytime night fax); - push @fields, qw(ship_daytime ship_night ship_fax) - if defined dbdef->table('cust_main')->column('ship_last'); - - for my $field ( @fields ) { - push @cust_main, qsearch ( 'cust_main', - { $field => { 'op' => 'LIKE', - 'value' => "%$phone%" } } ); - } - - \@cust_main; -} + </TABLE><% $pager %> + + <% include('/elements/footer.html') %> +% } +% +%#undef $cache; #does this help? +% +%# +% +%sub last_sort { +% lc($a->getfield('last')) cmp lc($b->getfield('last')) +% || lc($a->first) cmp lc($b->first); +%} +% +%sub company_sort { +% return -1 if $a->company && ! $b->company; +% return 1 if ! $a->company && $b->company; +% lc($a->company) cmp lc($b->company) +% || lc($a->getfield('last')) cmp lc($b->getfield('last')) +% || lc($a->first) cmp lc($b->first);; +%} +% +%sub custnum_sort { +% $a->getfield('custnum') <=> $b->getfield('custnum'); +%} +% +%sub tickets_sort { +% $b->getfield('tickets') <=> $a->getfield('tickets'); +%} +% +%sub custnumsearch { +% +% my $custnum = $cgi->param('custnum_text'); +% $custnum =~ s/\D//g; +% $custnum =~ /^(\d{1,23})$/ or eidiot "Illegal customer number\n"; +% $custnum = $1; +% +% [ qsearchs('cust_main', { 'custnum' => $custnum } ) ]; +%} +% +%sub cardsearch { +% +% my($card)=$cgi->param('card'); +% $card =~ s/\D//g; +% $card =~ /^(\d{13,16})$/ or eidiot "Illegal card number\n"; +% my($payinfo)=$1; +% +% [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}), +% qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'}) +% ]; +%} +% +%sub referralsearch { +% $cgi->param('referral_custnum') =~ /^(\d+)$/ +% or eidiot "Illegal referral_custnum"; +% my $cust_main = qsearchs('cust_main', { 'custnum' => $1 } ) +% or eidiot "Customer $1 not found"; +% my $depth; +% if ( $cgi->param('referral_depth') ) { +% $cgi->param('referral_depth') =~ /^(\d+)$/ +% or eidiot "Illegal referral_depth"; +% $depth = $1; +% } else { +% $depth = 1; +% } +% [ $cust_main->referral_cust_main($depth) ]; +%} +% +%sub lastsearch { +% my(%last_type); +% my @cust_main; +% foreach ( $cgi->param('last_type') ) { +% $last_type{$_}++; +% } +% +% $cgi->param('last_text') =~ /^([\w \,\.\-\']*)$/ +% or eidiot "Illegal last name"; +% my($last)=$1; +% +% if ( $last_type{'Exact'} || $last_type{'Fuzzy'} ) { +% push @cust_main, qsearch( 'cust_main', +% { 'last' => { 'op' => 'ILIKE', +% 'value' => $last } } ); +% +% push @cust_main, qsearch( 'cust_main', +% { 'ship_last' => { 'op' => 'ILIKE', +% 'value' => $last } } ) +% if defined dbdef->table('cust_main')->column('ship_last'); +% } +% +% if ( $last_type{'Substring'} || $last_type{'All'} ) { +% +% push @cust_main, qsearch( 'cust_main', +% { 'last' => { 'op' => 'ILIKE', +% 'value' => "%$last%" } } ); +% +% push @cust_main, qsearch( 'cust_main', +% { 'ship_last' => { 'op' => 'ILIKE', +% 'value' => "%$last%" } } ) +% if defined dbdef->table('cust_main')->column('ship_last'); +% +% } +% +% if ( $last_type{'Fuzzy'} || $last_type{'All'} ) { +% push @cust_main, FS::cust_main->fuzzy_search( { 'last' => $last } ); +% } +% +% #if ($last_type{'Sound-alike'}) { +% #} +% +% \@cust_main; +%} +% +%sub companysearch { +% +% my(%company_type); +% my @cust_main; +% foreach ( $cgi->param('company_type') ) { +% $company_type{$_}++ +% }; +% +% $cgi->param('company_text') =~ +% /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ +% or eidiot "Illegal company"; +% my $company = $1; +% +% if ( $company_type{'Exact'} || $company_type{'Fuzzy'} ) { +% push @cust_main, qsearch( 'cust_main', +% { 'company' => { 'op' => 'ILIKE', +% 'value' => $company } } ); +% +% push @cust_main, qsearch( 'cust_main', +% { 'ship_company' => { 'op' => 'ILIKE', +% 'value' => $company } } ) +% if defined dbdef->table('cust_main')->column('ship_last'); +% } +% +% if ( $company_type{'Substring'} || $company_type{'All'} ) { +% +% push @cust_main, qsearch( 'cust_main', +% { 'company' => { 'op' => 'ILIKE', +% 'value' => "%$company%" } } ); +% +% push @cust_main, qsearch( 'cust_main', +% { 'ship_company' => { 'op' => 'ILIKE', +% 'value' => "%$company%" } }) +% if defined dbdef->table('cust_main')->column('ship_last'); +% +% } +% +% if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { +% push @cust_main, FS::cust_main->fuzzy_search( { 'company' => $company } ); +% } +% +% if ($company_type{'Sound-alike'}) { +% } +% +% \@cust_main; +%} +% +%sub address2search { +% my @cust_main; +% +% $cgi->param('address2_text') =~ +% /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/ +% or eidiot "Illegal address2"; +% my $address2 = $1; +% +% push @cust_main, qsearch( 'cust_main', +% { 'address2' => { 'op' => 'ILIKE', +% 'value' => $address2 } } ); +% push @cust_main, qsearch( 'cust_main', +% { 'address2' => { 'op' => 'ILIKE', +% 'value' => $address2 } } ) +% if defined dbdef->table('cust_main')->column('ship_last'); +% +% \@cust_main; +%} +% +%sub phonesearch { +% my @cust_main; +% +% my $phone = $cgi->param('phone_text'); +% +% #(no longer really) false laziness with Record::ut_phonen +% #only works with US/CA numbers... +% $phone =~ s/\D//g; +% if ( $phone =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ ) { +% $phone = "$1-$2-$3"; +% $phone .= " x$4" if $4; +% } elsif ( $phone =~ /^(\d{3})(\d{4})$/ ) { +% $phone = "$1-$2"; +% } elsif ( $phone =~ /^(\d{3,4})$/ ) { +% $phone = $1; +% } else { +% eidiot gettext('illegal_phone'). ": $phone"; +% } +% +% my @fields = qw(daytime night fax); +% push @fields, qw(ship_daytime ship_night ship_fax) +% if defined dbdef->table('cust_main')->column('ship_last'); +% +% for my $field ( @fields ) { +% push @cust_main, qsearch ( 'cust_main', +% { $field => { 'op' => 'LIKE', +% 'value' => "%$phone%" } } ); +% } +% +% \@cust_main; +%} +% +% -%> diff --git a/httemplate/search/cust_pay.cgi b/httemplate/search/cust_pay.cgi index 0247b0b0d..0664bf796 100755 --- a/httemplate/search/cust_pay.cgi +++ b/httemplate/search/cust_pay.cgi @@ -1,152 +1,153 @@ -<% - my $title = 'Payment Search Results'; - my( $count_query, $sql_query ); - if ( $cgi->param('magic') ) { - - my @search = (); - my $orderby; - if ( $cgi->param('magic') eq '_date' ) { - - - if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { - push @search, "agentnum = $1"; # $search{'agentnum'} = $1; - my $agent = qsearchs('agent', { 'agentnum' => $1 } ); - die "unknown agentnum $1" unless $agent; - $title = $agent->agent. " $title"; - } - - if ( $cgi->param('payby') ) { - $cgi->param('payby') =~ - /^(CARD|CHEK|BILL|PREP|CASH|WEST|MCRD)(-(VisaMC|Amex|Discover|Maestro))?$/ - or die "illegal payby ". $cgi->param('payby'); - push @search, "cust_pay.payby = '$1'"; - if ( $3 ) { - if ( $3 eq 'VisaMC' ) { - #avoid posix regexes for portability - push @search, - " ( ( substring(cust_pay.payinfo from 1 for 1) = '4' ". - " AND substring(cust_pay.payinfo from 1 for 4) != '4936' ". - " AND substring(cust_pay.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49030[2-9]' ". - " AND substring(cust_pay.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49033[5-9]' ". - " AND substring(cust_pay.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49110[1-2]' ". - " AND substring(cust_pay.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49117[4-9]' ". - " AND substring(cust_pay.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49118[1-2]' ". - " )". - " OR substring(cust_pay.payinfo from 1 for 2) = '51' ". - " OR substring(cust_pay.payinfo from 1 for 2) = '52' ". - " OR substring(cust_pay.payinfo from 1 for 2) = '53' ". - " OR substring(cust_pay.payinfo from 1 for 2) = '54' ". - " OR substring(cust_pay.payinfo from 1 for 2) = '54' ". - " OR substring(cust_pay.payinfo from 1 for 2) = '55' ". - " ) "; - } elsif ( $3 eq 'Amex' ) { - push @search, - " ( substring(cust_pay.payinfo from 1 for 2 ) = '34' ". - " OR substring(cust_pay.payinfo from 1 for 2 ) = '37' ". - " ) "; - } elsif ( $3 eq 'Discover' ) { - push @search, - " ( substring(cust_pay.payinfo from 1 for 4 ) = '6011' ". - " OR substring(cust_pay.payinfo from 1 for 3 ) = '650' ". - " ) "; - } elsif ( $3 eq 'Maestro' ) { - push @search, - " ( substring(cust_pay.payinfo from 1 for 2 ) = '63' ". - " OR substring(cust_pay.payinfo from 1 for 2 ) = '67' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) = '564182' ". - " OR substring(cust_pay.payinfo from 1 for 4 ) = '4936' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) ". - " SIMILAR TO '49030[2-9]' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) ". - " SIMILAR TO '49033[5-9]' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) ". - " SIMILAR TO '49110[1-2]' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) ". - " SIMILAR TO '49117[4-9]' ". - " OR substring(cust_pay.payinfo from 1 for 6 ) ". - " SIMILAR TO '49118[1-2]' ". - " ) "; - } else { - die "unknown card type $3"; - } - } - } - - my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - push @search, "_date >= $beginning ", - "_date <= $ending"; - - $orderby = '_date'; - - } elsif ( $cgi->param('magic') eq 'paybatch' ) { - - $cgi->param('paybatch') =~ /^([\w\/\:\-\.]+)$/ - or die "illegal paybatch: ". $cgi->param('paybatch'); - - push @search, "paybatch = '$1'"; - - $orderby = "LOWER(company || ' ' || last || ' ' || first )"; - - } else { - die "unknown search magic: ". $cgi->param('magic'); - } - - #here is the agent virtualization - push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; - - my $search = ' WHERE '. join(' AND ', @search); - - $count_query = "SELECT COUNT(*), SUM(paid) ". - "FROM cust_pay LEFT JOIN cust_main USING ( custnum )". - $search; - - $sql_query = { - 'table' => 'cust_pay', - 'select' => join(', ', - 'cust_pay.*', - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'hashref' => {}, - 'extra_sql' => "$search ORDER BY $orderby", - 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', - }; - - } else { - - $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo"; - my $payinfo = $1; - - $cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby"; - my $payby = $1; - - $count_query = "SELECT COUNT(*), SUM(paid) FROM cust_pay". - " WHERE payinfo = '$payinfo' AND payby = '$payby'". - " AND ". $FS::CurrentUser::CurrentUser->agentnums_sql; - - $sql_query = { - 'table' => 'cust_pay', - 'hashref' => { 'payinfo' => $payinfo, - 'payby' => $payby }, - 'extra_sql' => $FS::CurrentUser::CurrentUser->agentnums_sql. - " ORDER BY _date", - }; - - } - - my $link = sub { - my $cust_pay = shift; - $cust_pay->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; - }; - -%><%= include( 'elements/search.html', +% +% my $title = 'Payment Search Results'; +% my( $count_query, $sql_query ); +% if ( $cgi->param('magic') ) { +% +% my @search = (); +% my $orderby; +% if ( $cgi->param('magic') eq '_date' ) { +% +% +% if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { +% push @search, "agentnum = $1"; # $search{'agentnum'} = $1; +% my $agent = qsearchs('agent', { 'agentnum' => $1 } ); +% die "unknown agentnum $1" unless $agent; +% $title = $agent->agent. " $title"; +% } +% +% if ( $cgi->param('payby') ) { +% $cgi->param('payby') =~ +% /^(CARD|CHEK|BILL|PREP|CASH|WEST|MCRD)(-(VisaMC|Amex|Discover|Maestro))?$/ +% or die "illegal payby ". $cgi->param('payby'); +% push @search, "cust_pay.payby = '$1'"; +% if ( $3 ) { +% if ( $3 eq 'VisaMC' ) { +% #avoid posix regexes for portability +% push @search, +% " ( ( substring(cust_pay.payinfo from 1 for 1) = '4' ". +% " AND substring(cust_pay.payinfo from 1 for 4) != '4936' ". +% " AND substring(cust_pay.payinfo from 1 for 6) ". +% " NOT SIMILAR TO '49030[2-9]' ". +% " AND substring(cust_pay.payinfo from 1 for 6) ". +% " NOT SIMILAR TO '49033[5-9]' ". +% " AND substring(cust_pay.payinfo from 1 for 6) ". +% " NOT SIMILAR TO '49110[1-2]' ". +% " AND substring(cust_pay.payinfo from 1 for 6) ". +% " NOT SIMILAR TO '49117[4-9]' ". +% " AND substring(cust_pay.payinfo from 1 for 6) ". +% " NOT SIMILAR TO '49118[1-2]' ". +% " )". +% " OR substring(cust_pay.payinfo from 1 for 2) = '51' ". +% " OR substring(cust_pay.payinfo from 1 for 2) = '52' ". +% " OR substring(cust_pay.payinfo from 1 for 2) = '53' ". +% " OR substring(cust_pay.payinfo from 1 for 2) = '54' ". +% " OR substring(cust_pay.payinfo from 1 for 2) = '54' ". +% " OR substring(cust_pay.payinfo from 1 for 2) = '55' ". +% " ) "; +% } elsif ( $3 eq 'Amex' ) { +% push @search, +% " ( substring(cust_pay.payinfo from 1 for 2 ) = '34' ". +% " OR substring(cust_pay.payinfo from 1 for 2 ) = '37' ". +% " ) "; +% } elsif ( $3 eq 'Discover' ) { +% push @search, +% " ( substring(cust_pay.payinfo from 1 for 4 ) = '6011' ". +% " OR substring(cust_pay.payinfo from 1 for 3 ) = '650' ". +% " ) "; +% } elsif ( $3 eq 'Maestro' ) { +% push @search, +% " ( substring(cust_pay.payinfo from 1 for 2 ) = '63' ". +% " OR substring(cust_pay.payinfo from 1 for 2 ) = '67' ". +% " OR substring(cust_pay.payinfo from 1 for 6 ) = '564182' ". +% " OR substring(cust_pay.payinfo from 1 for 4 ) = '4936' ". +% " OR substring(cust_pay.payinfo from 1 for 6 ) ". +% " SIMILAR TO '49030[2-9]' ". +% " OR substring(cust_pay.payinfo from 1 for 6 ) ". +% " SIMILAR TO '49033[5-9]' ". +% " OR substring(cust_pay.payinfo from 1 for 6 ) ". +% " SIMILAR TO '49110[1-2]' ". +% " OR substring(cust_pay.payinfo from 1 for 6 ) ". +% " SIMILAR TO '49117[4-9]' ". +% " OR substring(cust_pay.payinfo from 1 for 6 ) ". +% " SIMILAR TO '49118[1-2]' ". +% " ) "; +% } else { +% die "unknown card type $3"; +% } +% } +% } +% +% my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +% push @search, "_date >= $beginning ", +% "_date <= $ending"; +% +% $orderby = '_date'; +% +% } elsif ( $cgi->param('magic') eq 'paybatch' ) { +% +% $cgi->param('paybatch') =~ /^([\w\/\:\-\.]+)$/ +% or die "illegal paybatch: ". $cgi->param('paybatch'); +% +% push @search, "paybatch = '$1'"; +% +% $orderby = "LOWER(company || ' ' || last || ' ' || first )"; +% +% } else { +% die "unknown search magic: ". $cgi->param('magic'); +% } +% +% #here is the agent virtualization +% push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; +% +% my $search = ' WHERE '. join(' AND ', @search); +% +% $count_query = "SELECT COUNT(*), SUM(paid) ". +% "FROM cust_pay LEFT JOIN cust_main USING ( custnum )". +% $search; +% +% $sql_query = { +% 'table' => 'cust_pay', +% 'select' => join(', ', +% 'cust_pay.*', +% 'cust_main.custnum as cust_main_custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'hashref' => {}, +% 'extra_sql' => "$search ORDER BY $orderby", +% 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )', +% }; +% +% } else { +% +% $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo"; +% my $payinfo = $1; +% +% $cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby"; +% my $payby = $1; +% +% $count_query = "SELECT COUNT(*), SUM(paid) FROM cust_pay". +% " WHERE payinfo = '$payinfo' AND payby = '$payby'". +% " AND ". $FS::CurrentUser::CurrentUser->agentnums_sql; +% +% $sql_query = { +% 'table' => 'cust_pay', +% 'hashref' => { 'payinfo' => $payinfo, +% 'payby' => $payby }, +% 'extra_sql' => $FS::CurrentUser::CurrentUser->agentnums_sql. +% " ORDER BY _date", +% }; +% +% } +% +% my $link = sub { +% my $cust_pay = shift; +% $cust_pay->cust_main_custnum +% ? [ "${p}view/cust_main.cgi?", 'custnum' ] +% : ''; +% }; +% +% +<% include( 'elements/search.html', 'title' => $title, 'name' => 'payments', 'query' => $sql_query, 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!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!, -# qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!; -# $n2="</TR><TR>"; -# } - -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!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$label</FONT></A></TD>!, +%# qq!<TD><A HREF="$sview/$svcdb.cgi?$svcnum"><FONT SIZE=-1>$value</FONT></A></TD>!; +%# $n2="</TR><TR>"; +%# } +% +%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, diff --git a/httemplate/search/cust_tax_exempt_pkg.cgi b/httemplate/search/cust_tax_exempt_pkg.cgi index e61947b7d..3cec04f92 100644 --- a/httemplate/search/cust_tax_exempt_pkg.cgi +++ b/httemplate/search/cust_tax_exempt_pkg.cgi @@ -1,92 +1,93 @@ -<% - -my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - -my $join_cust = " - JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) -"; - -my $join_pkg = " - LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) -"; - -my $join = " - JOIN cust_bill_pkg USING ( billpkgnum ) - $join_cust - $join_pkg -"; - -my $where = " - WHERE _date >= $beginning AND _date <= $ending -"; -# AND payby != 'COMP' - -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - $where .= " AND agentnum = $1 "; -} - -if ( $cgi->param('out') ) { - - $where .= " - AND 0 = ( - SELECT COUNT(*) FROM cust_main_county AS county_out - WHERE ( county_out.county = cust_main.county - OR ( county_out.county IS NULL AND cust_main.county = '' ) - OR ( county_out.county = '' AND cust_main.county IS NULL) - OR ( county_out.county IS NULL AND cust_main.county IS NULL) - ) - AND ( county_out.state = cust_main.state - OR ( county_out.state IS NULL AND cust_main.state = '' ) - OR ( county_out.state = '' AND cust_main.state IS NULL ) - OR ( county_out.state IS NULL AND cust_main.state IS NULL ) - ) - AND county_out.country = cust_main.country - AND county_out.tax > 0 - ) - "; - -} elsif ( $cgi->param('country' ) ) { - - my $county = dbh->quote( $cgi->param('county') ); - my $state = dbh->quote( $cgi->param('state') ); - my $country = dbh->quote( $cgi->param('country') ); - $where .= " - AND ( county = $county OR $county = '' ) - AND ( state = $state OR $state = '' ) - AND country = $country - "; - $where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') ) - if $cgi->param('taxclass'); - -} - -my $count_query = "SELECT COUNT(*), SUM(amount)". - " FROM cust_tax_exempt_pkg $join $where"; - -my $query = { - 'table' => 'cust_tax_exempt_pkg', - 'addl_from' => $join, - 'hashref' => {}, - 'select' => join(', ', - 'cust_tax_exempt_pkg.*', - 'cust_bill_pkg.*', - 'cust_bill.*', - 'part_pkg.pkg', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => $where, -}; - -my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; -my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; - -my $conf = new FS::Conf; -my $money_char = $conf->config('money_char') || '$'; - -%><%= include( 'elements/search.html', +% +% +%my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +% +%my $join_cust = " +% JOIN cust_bill USING ( invnum ) +% LEFT JOIN cust_main USING ( custnum ) +%"; +% +%my $join_pkg = " +% LEFT JOIN cust_pkg USING ( pkgnum ) +% LEFT JOIN part_pkg USING ( pkgpart ) +%"; +% +%my $join = " +% JOIN cust_bill_pkg USING ( billpkgnum ) +% $join_cust +% $join_pkg +%"; +% +%my $where = " +% WHERE _date >= $beginning AND _date <= $ending +%"; +%# AND payby != 'COMP' +% +%if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { +% $where .= " AND agentnum = $1 "; +%} +% +%if ( $cgi->param('out') ) { +% +% $where .= " +% AND 0 = ( +% SELECT COUNT(*) FROM cust_main_county AS county_out +% WHERE ( county_out.county = cust_main.county +% OR ( county_out.county IS NULL AND cust_main.county = '' ) +% OR ( county_out.county = '' AND cust_main.county IS NULL) +% OR ( county_out.county IS NULL AND cust_main.county IS NULL) +% ) +% AND ( county_out.state = cust_main.state +% OR ( county_out.state IS NULL AND cust_main.state = '' ) +% OR ( county_out.state = '' AND cust_main.state IS NULL ) +% OR ( county_out.state IS NULL AND cust_main.state IS NULL ) +% ) +% AND county_out.country = cust_main.country +% AND county_out.tax > 0 +% ) +% "; +% +%} elsif ( $cgi->param('country' ) ) { +% +% my $county = dbh->quote( $cgi->param('county') ); +% my $state = dbh->quote( $cgi->param('state') ); +% my $country = dbh->quote( $cgi->param('country') ); +% $where .= " +% AND ( county = $county OR $county = '' ) +% AND ( state = $state OR $state = '' ) +% AND country = $country +% "; +% $where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') ) +% if $cgi->param('taxclass'); +% +%} +% +%my $count_query = "SELECT COUNT(*), SUM(amount)". +% " FROM cust_tax_exempt_pkg $join $where"; +% +%my $query = { +% 'table' => 'cust_tax_exempt_pkg', +% 'addl_from' => $join, +% 'hashref' => {}, +% 'select' => join(', ', +% 'cust_tax_exempt_pkg.*', +% 'cust_bill_pkg.*', +% 'cust_bill.*', +% 'part_pkg.pkg', +% 'cust_main.custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'extra_sql' => $where, +%}; +% +%my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; +%my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; +% +%my $conf = new FS::Conf; +%my $money_char = $conf->config('money_char') || '$'; +% +% +<% include( 'elements/search.html', 'title' => 'Tax exemptions', 'name' => 'tax exemptions', 'query' => $query, diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 3e689eba1..cbf0887a6 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -1,324 +1,333 @@ -<% - - # options example... - # (everything not commented required is optional) - # - # # basic options, required - # 'title' => 'Page title', - # 'name' => 'items', #name for the records returned - # - # # some HTML callbacks... - # 'menubar' => '', #menubar arrayref - # 'html_init' => '', #after the header/menubar and before the pager - # 'html_foot' => '', #at the bottom - # 'html_posttotal' => '', #at the bottom - # # (these three can be strings or coderefs) - # - # - # #literal SQL query string or qsearch hashref, required - # 'query' => { - # 'table' => 'tablename', - # #everything else is optional... - # 'hashref' => { 'field' => 'value', - # 'field' => { 'op' => '<', - # 'value' => '54', - # }, - # }, - # 'select' => '*', - # 'addl_from' => '', #'LEFT JOIN othertable USING ( key )', - # 'extra_sql' => '', #'AND otherstuff', #'WHERE onlystuff', - # - # - # }, - # # "select * from tablename"; - # - # #required unless 'query' is an SQL query string (shouldn't be...) - # 'count_query' => 'SELECT COUNT(*) FROM tablename', - # - # 'count_addl' => [], #additional count fields listref of sprintf strings - # # [ $money_char.'%.2f total paid', ], - # - # #listref of column labels, <TH> - # #required unless 'query' is an SQL query string - # # (if not specified the database column names will be used) - # 'header' => [ '#', 'Item' ], - # - # 'disable_download' => '', # set true to hide the CSV/Excel download links - # 'disable_nonefound' => '', # set true to disable the "No matching Xs found" - # # message - # - # #listref - each item is a literal column name (or method) or coderef - # #if not specified all columns will be shown - # 'fields' => [ - # 'column', - # sub { my $row = shift; $row->column; }, - # ], - # - # #listref of column footers - # 'footer' => [], - # - # #listref - each item is the empty string, or a listref of ... - # 'links' => - # - # - # 'align' => 'lrc.', #one letter for each column, left/right/center/none - # # can also pass a listref with full values: - # # [ 'left', 'right', 'center', '' ] - # - # #listrefs... - # #currently only HTML, maybe eventually Excel too - # 'color' => [], - # 'size' => [], - # 'style' => [], - # - # #redirect if there's only one item... - # # listref of URL base and column name (or method) - # # or a coderef that returns the same - # 'redirect' => - - my $DEBUG = 0; - - my(%opt) = @_; - #warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n"; - - my %align = ( - 'l' => 'left', - 'r' => 'right', - 'c' => 'center', - ' ' => '', - '.' => '', - ); - $opt{align} = [ map $align{$_}, split(//, $opt{align}) ], - unless !$opt{align} || ref($opt{align}); - - my $type = ''; - my $limit = ''; - my($maxrecords, $total, $offset, $count_arrayref); - - if ( $cgi->param('_type') =~ /^(csv|\w*\.xls)$/ ) { - - $type = $1; - - } else { #setup some pagination things if we're in html mode - - unless (exists($opt{'count_query'}) && length($opt{'count_query'})) { - ( $opt{'count_query'} = $opt{'query'} ) =~ - s/^\s*SELECT\s*(.*?)\s+FROM\s/SELECT COUNT(*) FROM /i; - } - - my $conf = new FS::Conf; - $maxrecords = $conf->config('maxsearchrecordsperpage'); - - $limit = $maxrecords ? "LIMIT $maxrecords" : ''; - - $offset = $cgi->param('offset') || 0; - $limit .= " OFFSET $offset" if $offset; - - my $count_sth = dbh->prepare($opt{'count_query'}) - or die "Error preparing $opt{'count_query'}: ". dbh->errstr; - $count_sth->execute - or die "Error executing $opt{'count_query'}: ". $count_sth->errstr; - $count_arrayref = $count_sth->fetchrow_arrayref; - $total = $count_arrayref->[0]; - - } - - # run the query - - my $header = $opt{'header'}; - my $rows; - if ( ref($opt{'query'}) ) { - - #eval "use FS::$opt{'query'};"; - $rows = [ qsearch( - $opt{'query'}->{'table'}, - $opt{'query'}->{'hashref'} || {}, - $opt{'query'}->{'select'}, - $opt{'query'}->{'extra_sql'}. " $limit", - '', - (exists($opt{'query'}->{'addl_from'}) ? $opt{'query'}->{'addl_from'} : '') - ) ]; - - } else { - - my $sth = dbh->prepare("$opt{'query'} $limit") - or die "Error preparing $opt{'query'}: ". dbh->errstr; - $sth->execute - or die "Error executing $opt{'query'}: ". $sth->errstr; - - #can get # of rows without fetching them all? - $rows = $sth->fetchall_arrayref; - - $header ||= $sth->{NAME}; - - } - - warn scalar(@$rows). ' rows returned from '. - ( ref($opt{'query'}) ? 'qsearch query' : 'literal SQL query' ) - if $DEBUG || $opt{'debug'}; - - # display the results - csv, xls or html - - if ( $type eq 'csv' ) { - - #http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes - http_header('Content-Type' => 'text/plain' ); - - my $csv = new Text::CSV_XS { 'always_quote' => 1, - 'eol' => "\n", #"\015\012", #"\012" - }; - - $csv->combine(@$header); #or die $csv->status; - %><%= $csv->string %><% - - foreach my $row ( @$rows ) { - - if ( $opt{'fields'} ) { - - my @line = (); - - foreach my $field ( @{$opt{'fields'}} ) { - if ( ref($field) eq 'CODE' ) { - push @line, map { - ref($_) eq 'ARRAY' - ? '(N/A)' #unimplemented - : $_; - } - &{$field}($row); - } else { - push @line, $row->$field(); - } - } - - $csv->combine(@line); #or die $csv->status; - - } else { - $csv->combine(@$row); #or die $csv->status; - } - - %><%= $csv->string %><% - - } - - #} elsif ( $type eq 'excel' ) { - } elsif ( $type =~ /\.xls$/ ) { - - #http_header('Content-Type' => 'application/excel' ); #eww - http_header('Content-Type' => 'application/vnd.ms-excel' ); - #http_header('Content-Type' => 'application/msexcel' ); #alas - - my $data = ''; - my $XLS = new IO::Scalar \$data; - my $workbook = Spreadsheet::WriteExcel->new($XLS) - or die "Error opening .xls file: $!"; - - my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31)); - - my($r,$c) = (0,0); - - $worksheet->write($r, $c++, $_) foreach @$header; - - foreach my $row ( @$rows ) { - $r++; - $c = 0; - - if ( $opt{'fields'} ) { - - #my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; - #my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; - - foreach my $field ( @{$opt{'fields'}} ) { - #my $align = $aligns ? shift @$aligns : ''; - #$align = " ALIGN=$align" if $align; - #my $a = ''; - #if ( $links ) { - # my $link = shift @$links; - # $link = &{$link}($row) if ref($link) eq 'CODE'; - # if ( $link ) { - # my( $url, $method ) = @{$link}; - # if ( ref($method) eq 'CODE' ) { - # $a = $url. &{$method}($row); - # } else { - # $a = $url. $row->$method(); - # } - # $a = qq(<A HREF="$a">); - # } - #} - if ( ref($field) eq 'CODE' ) { - foreach my $value ( &{$field}($row) ) { - if ( ref($value) eq 'ARRAY' ) { - $worksheet->write($r, $c++, '(N/A)' ); #unimplemented - } else { - $worksheet->write($r, $c++, $value ); - } - } - } else { - $worksheet->write($r, $c++, $row->$field() ); - } - } - - } else { - $worksheet->write($r, $c++, $_) foreach @$row; - } - - } - - $workbook->close();# or die "Error creating .xls file: $!"; - - http_header('Content-Length' => length($data) ); - %><%= $data %><% - - } else { # regular HTML - - if ( exists($opt{'redirect'}) && scalar(@$rows) == 1 && $total == 1 ) { - my $redirect = $opt{'redirect'}; - $redirect = &{$redirect}($rows->[0]) if ref($redirect) eq 'CODE'; - my( $url, $method ) = @$redirect; - redirect( $url. $rows->[0]->$method() ); - } else { - ( my $xlsname = $opt{'name'} ) =~ s/\W//g; - #$opt{'name'} =~ s/s$// if $total == 1; - $opt{'name'} =~ s/((s)e)?s$/$2/ if $total == 1; #should use Lingua::bs - # to "depluralize" - - my @menubar = (); - if ( $opt{'menubar'} ) { - @menubar = @{ $opt{'menubar'} }; - } else { - @menubar = ( 'Main menu' => $p ); - } - - - %> - <%= include( '/elements/header.html', $opt{'title'}, +% +% +% # options example... +% # (everything not commented required is optional) +% # +% # # basic options, required +% # 'title' => 'Page title', +% # 'name' => 'items', #name for the records returned +% # +% # # some HTML callbacks... +% # 'menubar' => '', #menubar arrayref +% # 'html_init' => '', #after the header/menubar and before the pager +% # 'html_foot' => '', #at the bottom +% # 'html_posttotal' => '', #at the bottom +% # # (these three can be strings or coderefs) +% # +% # +% # #literal SQL query string or qsearch hashref, required +% # 'query' => { +% # 'table' => 'tablename', +% # #everything else is optional... +% # 'hashref' => { 'field' => 'value', +% # 'field' => { 'op' => '<', +% # 'value' => '54', +% # }, +% # }, +% # 'select' => '*', +% # 'addl_from' => '', #'LEFT JOIN othertable USING ( key )', +% # 'extra_sql' => '', #'AND otherstuff', #'WHERE onlystuff', +% # +% # +% # }, +% # # "select * from tablename"; +% # +% # #required unless 'query' is an SQL query string (shouldn't be...) +% # 'count_query' => 'SELECT COUNT(*) FROM tablename', +% # +% # 'count_addl' => [], #additional count fields listref of sprintf strings +% # # [ $money_char.'%.2f total paid', ], +% # +% # #listref of column labels, <TH> +% # #required unless 'query' is an SQL query string +% # # (if not specified the database column names will be used) +% # 'header' => [ '#', 'Item' ], +% # +% # 'disable_download' => '', # set true to hide the CSV/Excel download links +% # 'disable_nonefound' => '', # set true to disable the "No matching Xs found" +% # # message +% # +% # #listref - each item is a literal column name (or method) or coderef +% # #if not specified all columns will be shown +% # 'fields' => [ +% # 'column', +% # sub { my $row = shift; $row->column; }, +% # ], +% # +% # #listref of column footers +% # 'footer' => [], +% # +% # #listref - each item is the empty string, or a listref of ... +% # 'links' => +% # +% # +% # 'align' => 'lrc.', #one letter for each column, left/right/center/none +% # # can also pass a listref with full values: +% # # [ 'left', 'right', 'center', '' ] +% # +% # #listrefs... +% # #currently only HTML, maybe eventually Excel too +% # 'color' => [], +% # 'size' => [], +% # 'style' => [], +% # +% # #redirect if there's only one item... +% # # listref of URL base and column name (or method) +% # # or a coderef that returns the same +% # 'redirect' => +% +% my $DEBUG = 0; +% +% my(%opt) = @_; +% #warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n"; +% +% my %align = ( +% 'l' => 'left', +% 'r' => 'right', +% 'c' => 'center', +% ' ' => '', +% '.' => '', +% ); +% $opt{align} = [ map $align{$_}, split(//, $opt{align}) ], +% unless !$opt{align} || ref($opt{align}); +% +% my $type = ''; +% my $limit = ''; +% my($maxrecords, $total, $offset, $count_arrayref); +% +% if ( $cgi->param('_type') =~ /^(csv|\w*\.xls)$/ ) { +% +% $type = $1; +% +% } else { #setup some pagination things if we're in html mode +% +% unless (exists($opt{'count_query'}) && length($opt{'count_query'})) { +% ( $opt{'count_query'} = $opt{'query'} ) =~ +% s/^\s*SELECT\s*(.*?)\s+FROM\s/SELECT COUNT(*) FROM /i; +% } +% +% my $conf = new FS::Conf; +% $maxrecords = $conf->config('maxsearchrecordsperpage'); +% +% $limit = $maxrecords ? "LIMIT $maxrecords" : ''; +% +% $offset = $cgi->param('offset') || 0; +% $limit .= " OFFSET $offset" if $offset; +% +% my $count_sth = dbh->prepare($opt{'count_query'}) +% or die "Error preparing $opt{'count_query'}: ". dbh->errstr; +% $count_sth->execute +% or die "Error executing $opt{'count_query'}: ". $count_sth->errstr; +% $count_arrayref = $count_sth->fetchrow_arrayref; +% $total = $count_arrayref->[0]; +% +% } +% +% # run the query +% +% my $header = $opt{'header'}; +% my $rows; +% if ( ref($opt{'query'}) ) { +% +% #eval "use FS::$opt{'query'};"; +% $rows = [ qsearch( +% $opt{'query'}->{'table'}, +% $opt{'query'}->{'hashref'} || {}, +% $opt{'query'}->{'select'}, +% $opt{'query'}->{'extra_sql'}. " $limit", +% '', +% (exists($opt{'query'}->{'addl_from'}) ? $opt{'query'}->{'addl_from'} : '') +% ) ]; +% +% } else { +% +% my $sth = dbh->prepare("$opt{'query'} $limit") +% or die "Error preparing $opt{'query'}: ". dbh->errstr; +% $sth->execute +% or die "Error executing $opt{'query'}: ". $sth->errstr; +% +% #can get # of rows without fetching them all? +% $rows = $sth->fetchall_arrayref; +% +% $header ||= $sth->{NAME}; +% +% } +% +% warn scalar(@$rows). ' rows returned from '. +% ( ref($opt{'query'}) ? 'qsearch query' : 'literal SQL query' ) +% if $DEBUG || $opt{'debug'}; +% +% # display the results - csv, xls or html +% +% if ( $type eq 'csv' ) { +% +% #http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes +% http_header('Content-Type' => 'text/plain' ); +% +% my $csv = new Text::CSV_XS { 'always_quote' => 1, +% 'eol' => "\n", #"\015\012", #"\012" +% }; +% +% $csv->combine(@$header); #or die $csv->status; +% +<% $csv->string %> +% +% +% foreach my $row ( @$rows ) { +% +% if ( $opt{'fields'} ) { +% +% my @line = (); +% +% foreach my $field ( @{$opt{'fields'}} ) { +% if ( ref($field) eq 'CODE' ) { +% push @line, map { +% ref($_) eq 'ARRAY' +% ? '(N/A)' #unimplemented +% : $_; +% } +% &{$field}($row); +% } else { +% push @line, $row->$field(); +% } +% } +% +% $csv->combine(@line); #or die $csv->status; +% +% } else { +% $csv->combine(@$row); #or die $csv->status; +% } +% +% +<% $csv->string %> +% +% +% } +% +% #} elsif ( $type eq 'excel' ) { +% } elsif ( $type =~ /\.xls$/ ) { +% +% #http_header('Content-Type' => 'application/excel' ); #eww +% http_header('Content-Type' => 'application/vnd.ms-excel' ); +% #http_header('Content-Type' => 'application/msexcel' ); #alas +% +% my $data = ''; +% my $XLS = new IO::Scalar \$data; +% my $workbook = Spreadsheet::WriteExcel->new($XLS) +% or die "Error opening .xls file: $!"; +% +% my $worksheet = $workbook->add_worksheet(substr($opt{'title'},0,31)); +% +% my($r,$c) = (0,0); +% +% $worksheet->write($r, $c++, $_) foreach @$header; +% +% foreach my $row ( @$rows ) { +% $r++; +% $c = 0; +% +% if ( $opt{'fields'} ) { +% +% #my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; +% #my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; +% +% foreach my $field ( @{$opt{'fields'}} ) { +% #my $align = $aligns ? shift @$aligns : ''; +% #$align = " ALIGN=$align" if $align; +% #my $a = ''; +% #if ( $links ) { +% # my $link = shift @$links; +% # $link = &{$link}($row) if ref($link) eq 'CODE'; +% # if ( $link ) { +% # my( $url, $method ) = @{$link}; +% # if ( ref($method) eq 'CODE' ) { +% # $a = $url. &{$method}($row); +% # } else { +% # $a = $url. $row->$method(); +% # } +% # $a = qq(<A HREF="$a">); +% # } +% #} +% if ( ref($field) eq 'CODE' ) { +% foreach my $value ( &{$field}($row) ) { +% if ( ref($value) eq 'ARRAY' ) { +% $worksheet->write($r, $c++, '(N/A)' ); #unimplemented +% } else { +% $worksheet->write($r, $c++, $value ); +% } +% } +% } else { +% $worksheet->write($r, $c++, $row->$field() ); +% } +% } +% +% } else { +% $worksheet->write($r, $c++, $_) foreach @$row; +% } +% +% } +% +% $workbook->close();# or die "Error creating .xls file: $!"; +% +% http_header('Content-Length' => length($data) ); +% +<% $data %> +% +% +% } else { # regular HTML +% +% if ( exists($opt{'redirect'}) && scalar(@$rows) == 1 && $total == 1 ) { +% my $redirect = $opt{'redirect'}; +% $redirect = &{$redirect}($rows->[0]) if ref($redirect) eq 'CODE'; +% my( $url, $method ) = @$redirect; +% redirect( $url. $rows->[0]->$method() ); +% } else { +% ( my $xlsname = $opt{'name'} ) =~ s/\W//g; +% #$opt{'name'} =~ s/s$// if $total == 1; +% $opt{'name'} =~ s/((s)e)?s$/$2/ if $total == 1; #should use Lingua::bs +% # to "depluralize" +% +% my @menubar = (); +% if ( $opt{'menubar'} ) { +% @menubar = @{ $opt{'menubar'} }; +% } else { +% @menubar = ( 'Main menu' => $p ); +% } +% +% +% + + <% include( '/elements/header.html', $opt{'title'}, include( '/elements/menubar.html', @menubar ) ) %> - <%= defined($opt{'html_init'}) + <% defined($opt{'html_init'}) ? ( ref($opt{'html_init'}) ? &{$opt{'html_init'}}() : $opt{'html_init'} ) : '' %> - <% my $pager = include ( '/elements/pager.html', - 'offset' => $offset, - 'num_rows' => scalar(@$rows), - 'total' => $total, - 'maxrecords' => $maxrecords, - ); - %> - <% unless ( $total ) { %> - <% unless ( $opt{'disable_nonefound'} ) { %> - No matching <%= $opt{'name'} %> found.<BR> - <% } %> - <% } else { %> +% my $pager = include ( '/elements/pager.html', +% 'offset' => $offset, +% 'num_rows' => scalar(@$rows), +% 'total' => $total, +% 'maxrecords' => $maxrecords, +% ); +% +% unless ( $total ) { +% unless ( $opt{'disable_nonefound'} ) { + + No matching <% $opt{'name'} %> found.<BR> +% } +% } else { + <TABLE> <TR> <TD VALIGN="bottom"> - <%= $total %> total <%= $opt{'name'} %> - <%= defined($opt{'html_posttotal'}) + <% $total %> total <% $opt{'name'} %> + <% defined($opt{'html_posttotal'}) ? ( ref($opt{'html_posttotal'}) ? &{$opt{'html_posttotal'}}() : $opt{'html_posttotal'} @@ -326,185 +335,201 @@ : '' %> <BR> - <% if ( $opt{'count_addl'} ) { %> - <% my $n=0; foreach my $count ( @{$opt{'count_addl'}} ) { %> - <%= sprintf( $count, $count_arrayref->[++$n] ) %><BR> - <% } %> - <% } %> +% if ( $opt{'count_addl'} ) { +% my $n=0; foreach my $count ( @{$opt{'count_addl'}} ) { + + <% sprintf( $count, $count_arrayref->[++$n] ) %><BR> +% } +% } + </TD> - <% unless ( $opt{'disable_download'} ) { %> +% unless ( $opt{'disable_download'} ) { + <TD ALIGN="right"> - <% $cgi->param('_type', "$xlsname.xls" ); %> +% $cgi->param('_type', "$xlsname.xls" ); + Download full results<BR> - as <A HREF="<%= $cgi->self_url %>">Excel spreadsheet</A><BR> - <% $cgi->param('_type', 'csv'); %> - as <A HREF="<%= $cgi->self_url %>">CSV file</A> + as <A HREF="<% $cgi->self_url %>">Excel spreadsheet</A><BR> +% $cgi->param('_type', 'csv'); + + as <A HREF="<% $cgi->self_url %>">CSV file</A> </TD> - <% } %> +% } + </TR> <TR> <TD COLSPAN=2> - <%= $pager %> + <% $pager %> - <%= include('/elements/table-grid.html') %> + <% include('/elements/table-grid.html') %> <TR> - <% - foreach my $header ( @$header ) { %> - <TH CLASS="grid" BGCOLOR="#cccccc"><%= $header %></TH> - <% } %> - </TR> - <% my $bgcolor1 = '#eeeeee'; - my $bgcolor2 = '#ffffff'; - my $bgcolor; - foreach my $row ( @$rows ) { - if ( $bgcolor eq $bgcolor1 ) { - $bgcolor = $bgcolor2; - } else { - $bgcolor = $bgcolor1; - } - %> - <TR> - <% if ( $opt{'fields'} ) { - - my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; - my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; - my $colors = $opt{'color'} ? [ @{$opt{'color'}} ] : []; - my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : []; - my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : []; - - foreach my $field ( - - map { - if ( ref($_) eq 'ARRAY' ) { - - my $tableref = $_; +% +% foreach my $header ( @$header ) { - '<TABLE CLASS="inv" CELLSPACING=0 CELLPADDING=0>'. + <TH CLASS="grid" BGCOLOR="#cccccc"><% $header %></TH> +% } - join('', map { - - my $rowref = $_; - - '<tr>'. - - join('', map { - - my $element = $_; - - '<TD'. - ( $element->{'align'} - ? ' ALIGN="'. $element->{'align'}. '"' - : '' - ). '>'. - ( $element->{'link'} - ? '<A HREF="'. $element->{'link'}.'">' - : '' - ). - $element->{'data'}. - ( $element->{'link'} - ? '</A>' - : '' - ). - '</td>'; - - } @$rowref ). - - '</tr>'; - } @$tableref ). - - '</table>'; - - } else { - $_; - } - } - - map { - if ( ref($_) eq 'CODE' ) { - &{$_}($row); - } else { - $row->$_(); - } - } - @{$opt{'fields'}} - - ) { - - my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid'; - - my $align = $aligns ? shift @$aligns : ''; - $align = " ALIGN=$align" if $align; - - my $a = ''; - if ( $links ) { - my $link = shift @$links; - $link = &{$link}($row) if ref($link) eq 'CODE'; - if ( $link ) { - my( $url, $method ) = @{$link}; - if ( ref($method) eq 'CODE' ) { - $a = $url. &{$method}($row); - } else { - $a = $url. $row->$method(); - } - $a = qq(<A HREF="$a">); - } - } - - my $font = ''; - my $color = shift @$colors; - $color = &{$color}($row) if ref($color) eq 'CODE'; - my $size = shift @$sizes; - $size = &{$size}($row) if ref($size) eq 'CODE'; - if ( $color || $size ) { - $font = '<FONT '. - ( $color ? "COLOR=#$color " : '' ). - ( $size ? qq(SIZE="$size" ) : '' ). - '>'; - } + </TR> +% my $bgcolor1 = '#eeeeee'; +% my $bgcolor2 = '#ffffff'; +% my $bgcolor; +% foreach my $row ( @$rows ) { +% if ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } +% - my($s, $es) = ( '', '' ); - my $style = shift @$styles; - $style = &{$style}($row) if ref($style) eq 'CODE'; - if ( $style ) { - $s = join( '', map "<$_>", split('', $style) ); - $es = join( '', map "</$_>", split('', $style) ); - } + <TR> +% if ( $opt{'fields'} ) { +% +% my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; +% my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; +% my $colors = $opt{'color'} ? [ @{$opt{'color'}} ] : []; +% my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : []; +% my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : []; +% +% foreach my $field ( +% +% map { +% if ( ref($_) eq 'ARRAY' ) { +% +% my $tableref = $_; +% +% '<TABLE CLASS="inv" CELLSPACING=0 CELLPADDING=0>'. +% +% join('', map { +% +% my $rowref = $_; +% +% '<tr>'. +% +% join('', map { +% +% my $element = $_; +% +% '<TD'. +% ( $element->{'align'} +% ? ' ALIGN="'. $element->{'align'}. '"' +% : '' +% ). '>'. +% ( $element->{'link'} +% ? '<A HREF="'. $element->{'link'}.'">' +% : '' +% ). +% $element->{'data'}. +% ( $element->{'link'} +% ? '</A>' +% : '' +% ). +% '</td>'; +% +% } @$rowref ). +% +% '</tr>'; +% } @$tableref ). +% +% '</table>'; +% +% } else { +% $_; +% } +% } +% +% map { +% if ( ref($_) eq 'CODE' ) { +% &{$_}($row); +% } else { +% $row->$_(); +% } +% } +% @{$opt{'fields'}} +% +% ) { +% +% my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid'; +% +% my $align = $aligns ? shift @$aligns : ''; +% $align = " ALIGN=$align" if $align; +% +% my $a = ''; +% if ( $links ) { +% my $link = shift @$links; +% $link = &{$link}($row) if ref($link) eq 'CODE'; +% if ( $link ) { +% my( $url, $method ) = @{$link}; +% if ( ref($method) eq 'CODE' ) { +% $a = $url. &{$method}($row); +% } else { +% $a = $url. $row->$method(); +% } +% $a = qq(<A HREF="$a">); +% } +% } +% +% my $font = ''; +% my $color = shift @$colors; +% $color = &{$color}($row) if ref($color) eq 'CODE'; +% my $size = shift @$sizes; +% $size = &{$size}($row) if ref($size) eq 'CODE'; +% if ( $color || $size ) { +% $font = '<FONT '. +% ( $color ? "COLOR=#$color " : '' ). +% ( $size ? qq(SIZE="$size" ) : '' ). +% '>'; +% } +% +% my($s, $es) = ( '', '' ); +% my $style = shift @$styles; +% $style = &{$style}($row) if ref($style) eq 'CODE'; +% if ( $style ) { +% $s = join( '', map "<$_>", split('', $style) ); +% $es = join( '', map "</$_>", split('', $style) ); +% } +% +% + + <TD CLASS="<% $class %>" BGCOLOR="<% $bgcolor %>"<% $align %>><% $font %><% $a %><% $s %><% $field %><% $es %><% $a ? '</A>' : '' %><% $font ? '</FONT>' : '' %></TD> +% } +% } else { +% foreach ( @$row ) { + + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $_ %></TD> +% } +% } - %> - <TD CLASS="<%= $class %>" BGCOLOR="<%= $bgcolor %>"<%= $align %>><%= $font %><%= $a %><%= $s %><%= $field %><%= $es %><%= $a ? '</A>' : '' %><%= $font ? '</FONT>' : '' %></TD> - <% } %> - <% } else { %> - <% foreach ( @$row ) { %> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $_ %></TD> - <% } %> - <% } %> </TR> - <% } %> +% } +% if ( $opt{'footer'} ) { - <% if ( $opt{'footer'} ) { %> <TR> - <% foreach my $footer ( @{ $opt{'footer'} } ) { %> - <TD CLASS="grid" BGCOLOR="#dddddd" STYLE="border-top: dashed 1px black;"><i><%= $footer %></i></TH> - <% } %> +% foreach my $footer ( @{ $opt{'footer'} } ) { + + <TD CLASS="grid" BGCOLOR="#dddddd" STYLE="border-top: dashed 1px black;"><i><% $footer %></i></TH> +% } + </TR> - <% } %> +% } + </TABLE> - <%= $pager %> + <% $pager %> </TD> </TR> </TABLE> - - <% } %> - <%= defined($opt{'html_foot'}) +% } + + <% defined($opt{'html_foot'}) ? ( ref($opt{'html_foot'}) ? &{$opt{'html_foot'}}() : $opt{'html_foot'} ) : '' %> - <%= include( '/elements/footer.html' ) %> - <% } %> -<% } %> + <% include( '/elements/footer.html' ) %> +% } +% } + diff --git a/httemplate/search/inventory_item.html b/httemplate/search/inventory_item.html index 7049c8841..fc690b33b 100644 --- a/httemplate/search/inventory_item.html +++ b/httemplate/search/inventory_item.html @@ -1,52 +1,53 @@ -<% - -my $classnum = $cgi->param('classnum'); -$classnum =~ /^(\d+)$/ or eidiot "illegal classnum $classnum"; -$classnum = $1; - -my $inventory_class = qsearchs( { - 'table' => 'inventory_class', - 'hashref' => { 'classnum' => $classnum }, -} ); - -my $title = $inventory_class->classname. ' Inventory'; - -#little false laziness with SQL fragments in inventory_class.pm -my $extra_sql = ''; -if ( $cgi->param('avail') ) { - $extra_sql = 'AND ( svcnum IS NULL OR svcnum = 0 )'; - $title .= ' - Available'; -} elsif ( $cgi->param('used') ) { - $extra_sql = 'AND svcnum IS NOT NULL AND svcnum > 0'; - $title .= ' - In use'; -} - -my $count_query = - "SELECT COUNT(*) FROM inventory_item WHERE classnum = $classnum $extra_sql"; - -my $link = sub { - my $inventory_item = shift; - if ( $inventory_item->svcnum ) { - [ "${p}view/svc_acct.cgi?", 'svcnum' ]; - } else { - ''; - } -}; -my $link_cust = sub { - my $inventory_item = shift; - if ( $inventory_item->custnum ) { - [ "${p}view/cust_main.cgi?", 'custnum' ]; - } else { - ''; - } -}; - -my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) '; - -%><%= include( 'elements/search.html', +% +% +%my $classnum = $cgi->param('classnum'); +%$classnum =~ /^(\d+)$/ or eidiot "illegal classnum $classnum"; +%$classnum = $1; +% +%my $inventory_class = qsearchs( { +% 'table' => 'inventory_class', +% 'hashref' => { 'classnum' => $classnum }, +%} ); +% +%my $title = $inventory_class->classname. ' Inventory'; +% +%#little false laziness with SQL fragments in inventory_class.pm +%my $extra_sql = ''; +%if ( $cgi->param('avail') ) { +% $extra_sql = 'AND ( svcnum IS NULL OR svcnum = 0 )'; +% $title .= ' - Available'; +%} elsif ( $cgi->param('used') ) { +% $extra_sql = 'AND svcnum IS NOT NULL AND svcnum > 0'; +% $title .= ' - In use'; +%} +% +%my $count_query = +% "SELECT COUNT(*) FROM inventory_item WHERE classnum = $classnum $extra_sql"; +% +%my $link = sub { +% my $inventory_item = shift; +% if ( $inventory_item->svcnum ) { +% [ "${p}view/svc_acct.cgi?", 'svcnum' ]; +% } else { +% ''; +% } +%}; +%my $link_cust = sub { +% my $inventory_item = shift; +% if ( $inventory_item->custnum ) { +% [ "${p}view/cust_main.cgi?", 'custnum' ]; +% } else { +% ''; +% } +%}; +% +%my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. +% ' LEFT JOIN part_svc USING ( svcpart ) '. +% ' LEFT JOIN cust_pkg USING ( pkgnum ) '. +% ' LEFT JOIN cust_main USING ( custnum ) '; +% +% +<% include( 'elements/search.html', 'title' => $title, diff --git a/httemplate/search/prepay_credit.html b/httemplate/search/prepay_credit.html index 8c8f57b5a..dff8a3d9a 100644 --- a/httemplate/search/prepay_credit.html +++ b/httemplate/search/prepay_credit.html @@ -1,15 +1,16 @@ -<% -my $agent = ''; -my $hashref = {}; -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - $hashref->{agentnum} = $1; - $agent = qsearchs('agent', { 'agentnum' => $1 } ); -} - -my $count_query = 'SELECT COUNT(*) FROM prepay_credit'; -$count_query .= ' WHERE agentnum = '. $agent->agentnum if $agent; - -%><%= include( 'elements/search.html', +% +%my $agent = ''; +%my $hashref = {}; +%if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { +% $hashref->{agentnum} = $1; +% $agent = qsearchs('agent', { 'agentnum' => $1 } ); +%} +% +%my $count_query = 'SELECT COUNT(*) FROM prepay_credit'; +%$count_query .= ' WHERE agentnum = '. $agent->agentnum if $agent; +% +% +<% include( 'elements/search.html', 'title' => 'Unused Prepaid Cards'. ($agent ? ' for '. $agent->agent : ''), 'menubar' => [ diff --git a/httemplate/search/queue.html b/httemplate/search/queue.html index 132c05129..fa6c1a1ca 100644 --- a/httemplate/search/queue.html +++ b/httemplate/search/queue.html @@ -1,17 +1,18 @@ -<% - -my $hashref = {}; - -my $conf = new FS::Conf; -my $dangerous = $conf->exists('queue_dangerous_controls'); - -my $noactions = 0; - -my $count_query = 'SELECT COUNT(*) FROM queue'; # + $hashref - -my $areboxes = 0; - -%><%= include( 'elements/search.html', +% +% +%my $hashref = {}; +% +%my $conf = new FS::Conf; +%my $dangerous = $conf->exists('queue_dangerous_controls'); +% +%my $noactions = 0; +% +%my $count_query = 'SELECT COUNT(*) FROM queue'; # + $hashref +% +%my $areboxes = 0; +% +% +<% include( 'elements/search.html', 'title' => 'Job Queue', 'menubar' => [ 'Main menu' => $p, ], 'name' => 'jobs', diff --git a/httemplate/search/reg_code.html b/httemplate/search/reg_code.html index 52a99ff66..dc388db2e 100644 --- a/httemplate/search/reg_code.html +++ b/httemplate/search/reg_code.html @@ -1,13 +1,14 @@ -<% - -my $agentnum = $cgi->param('agentnum'); -$agentnum =~ /^(\d+)$/ or eidiot "illegal agentnum $agentnum"; -$agentnum = $1; -my $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); - -my $count_query = "SELECT COUNT(*) FROM reg_code WHERE agentnum = $agentnum"; - -%><%= include( 'elements/search.html', +% +% +%my $agentnum = $cgi->param('agentnum'); +%$agentnum =~ /^(\d+)$/ or eidiot "illegal agentnum $agentnum"; +%$agentnum = $1; +%my $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); +% +%my $count_query = "SELECT COUNT(*) FROM reg_code WHERE agentnum = $agentnum"; +% +% +<% include( 'elements/search.html', 'title' => 'Unused Registration Codes for '. $agent->agent, 'name' => 'registration codes', diff --git a/httemplate/search/report_cdr.html b/httemplate/search/report_cdr.html index 6febe6c4a..c480c05f1 100644 --- a/httemplate/search/report_cdr.html +++ b/httemplate/search/report_cdr.html @@ -1,4 +1,4 @@ -<%= include('/elements/header.html', 'Call Detail Record Search' ) %> +<% include('/elements/header.html', 'Call Detail Record Search' ) %> <FORM ACTION="cdr.html" METHOD="GET"> Status: <SELECT NAME="freesidestatus"> @@ -8,5 +8,5 @@ Status: <SELECT NAME="freesidestatus"> </SELECT><BR> <INPUT TYPE="submit" VALUE="Search Call Detail Records"> -<%= include('/elements/footer.html') %> +<% include('/elements/footer.html') %> diff --git a/httemplate/search/report_cust_bill.html b/httemplate/search/report_cust_bill.html index f1b7bfa14..ec57d2094 100644 --- a/httemplate/search/report_cust_bill.html +++ b/httemplate/search/report_cust_bill.html @@ -1,15 +1,15 @@ -<%= include('/elements/header.html', 'Invoice report criteria' ) %> +<% include('/elements/header.html', 'Invoice report criteria' ) %> <FORM ACTION="cust_bill.html" METHOD="GET"> <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> <TABLE> - <%= include( '/elements/tr-select-agent.html', + <% include( '/elements/tr-select-agent.html', $cgi->param('agentnum'), 'label' => 'Invoices for agent: ', ) %> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> + <% include( '/elements/tr-input-beginning_ending.html' ) %> <TR> <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="open" VALUE="1" CHECKED></TD> <TD>Show only open invoices</TD> @@ -25,4 +25,4 @@ </FORM> -<%= include('/elements/footer.html') %> +<% include('/elements/footer.html') %> diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html index 8ca52dc9a..96c0b5acb 100644 --- a/httemplate/search/report_cust_credit.html +++ b/httemplate/search/report_cust_credit.html @@ -1,4 +1,4 @@ -<%= include('/elements/header.html', 'Credit report' ) %> +<% include('/elements/header.html', 'Credit report' ) %> <FORM ACTION="cust_credit.html" METHOD="GET"> <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> @@ -6,28 +6,30 @@ <TABLE> <TR> <TD ALIGN="right">Credits by employee: </TD> +% +% my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_credit") +% or die dbh->errstr; +% $sth->execute or die $sth->errstr; +% my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; +% -<% - my $sth = dbh->prepare("SELECT DISTINCT otaker FROM cust_credit") - or die dbh->errstr; - $sth->execute or die $sth->errstr; - my @otakers = map { $_->[0] } @{$sth->fetchall_arrayref}; -%> <TD><SELECT NAME="otaker"> <OPTION VALUE="">all</OPTION> - <% foreach my $otaker ( @otakers ) { %> - <OPTION VALUE="<%= $otaker %>"><%= $otaker %></OPTION> - <% } %> +% foreach my $otaker ( @otakers ) { + + <OPTION VALUE="<% $otaker %>"><% $otaker %></OPTION> +% } + </SELECT> </TD> </TR> - <%= include( '/elements/tr-select-agent.html', + <% include( '/elements/tr-select-agent.html', $cgi->param('agentnum'), 'label' => 'for agent: ', ) %> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> + <% include( '/elements/tr-input-beginning_ending.html' ) %> </TABLE> <BR> @@ -35,4 +37,4 @@ </FORM> -<%= include('/elements/footer.html') %> +<% include('/elements/footer.html') %> diff --git a/httemplate/search/report_cust_main-zip.html b/httemplate/search/report_cust_main-zip.html index 30020f3aa..db5e65c8f 100644 --- a/httemplate/search/report_cust_main-zip.html +++ b/httemplate/search/report_cust_main-zip.html @@ -1,4 +1,4 @@ -<%= include('/elements/header.html', 'Zip code report') %> +<% include('/elements/header.html', 'Zip code report') %> <FORM ACTION="cust_main-zip.html" METHOD="GET"> @@ -33,7 +33,7 @@ </TD> </TR> - <%= include( '/elements/tr-select-agent.html', + <% include( '/elements/tr-select-agent.html', $cgi->param('agentnum'), 'label' => 'for agent: ', ) diff --git a/httemplate/search/report_cust_pay.html b/httemplate/search/report_cust_pay.html index 8adf7dc13..f9875e20c 100644 --- a/httemplate/search/report_cust_pay.html +++ b/httemplate/search/report_cust_pay.html @@ -1,4 +1,4 @@ -<%= include('/elements/header.html', 'Payment report' ) %> +<% include('/elements/header.html', 'Payment report' ) %> <FORM ACTION="cust_pay.cgi" METHOD="GET"> <INPUT TYPE="hidden" NAME="magic" VALUE="_date"> @@ -25,13 +25,13 @@ </TD> </TR> - <%= include( '/elements/tr-select-agent.html', + <% include( '/elements/tr-select-agent.html', $cgi->param('agentnum'), 'label' => 'for agent: ', ) %> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> + <% include( '/elements/tr-input-beginning_ending.html' ) %> </TABLE> @@ -40,4 +40,4 @@ </FORM> -<%= include('/elements/footer.html') %> +<% include('/elements/footer.html') %> diff --git a/httemplate/search/report_cust_pkg.html b/httemplate/search/report_cust_pkg.html index 98e91217f..8fabf107a 100755 --- a/httemplate/search/report_cust_pkg.html +++ b/httemplate/search/report_cust_pkg.html @@ -1,4 +1,4 @@ -<%= include('/elements/header.html', 'Package Report' ) %> +<% include('/elements/header.html', 'Package Report' ) %> <FORM ACTION="cust_pkg.cgi" METHOD="GET"> <INPUT TYPE="hidden" NAME="magic" VALUE="bill"> @@ -8,22 +8,23 @@ <TR> <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"><FONT SIZE="+1">Search options</FONT></TH> </TR> - <%= include( '/elements/tr-select-agent.html', + <% include( '/elements/tr-select-agent.html', $cgi->param('agentnum'), ) %> - <%= include( '/elements/tr-select-cust_pkg-status.html' ) %> - <%= include( '/elements/tr-select-pkg_class.html', '', + <% include( '/elements/tr-select-cust_pkg-status.html' ) %> + <% include( '/elements/tr-select-pkg_class.html', '', 'pre_options' => [ '0' => 'all' ], 'empty_label' => '(empty class)', ) %> - <% #include( '/elements/tr-selectmultiple-part_pkg.html' ) %> +% #include( '/elements/tr-selectmultiple-part_pkg.html' ) + <TR> <TD ALIGN="right" VALIGN="center">Next bill date</TD> <TD> <TABLE> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> + <% include( '/elements/tr-input-beginning_ending.html' ) %> </TABLE> </TD> </TR> @@ -35,7 +36,7 @@ <TR> <TH BGCOLOR="#e8e8e8" COLSPAN=2 ALIGN="left"><FONT SIZE="+1">Display options</FONT></TH> </TR> - <%= include( '/elements/tr-select-cust-fields.html' ) %> + <% include( '/elements/tr-select-cust-fields.html' ) %> </TABLE> @@ -44,4 +45,4 @@ </FORM> -<%= include('/elements/footer.html') %> +<% include('/elements/footer.html') %> diff --git a/httemplate/search/report_prepaid_income.cgi b/httemplate/search/report_prepaid_income.cgi index f18224fd1..241e8a008 100644 --- a/httemplate/search/report_prepaid_income.cgi +++ b/httemplate/search/report_prepaid_income.cgi @@ -1,75 +1,76 @@ <!-- mason kludge --> -<% - - #doesn't yet deal with daily/weekly packages - - #needs to be re-written in sql for efficiency - - my $time = time; - - my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time; - $now =~ /^(\d+)$/ or die "unparsable date?"; - $now = $1; - - my( $total, $total_legacy ) = ( 0, 0 ); - - my @cust_bill_pkg = - grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[dw])$/ } - qsearch( 'cust_bill_pkg', { - 'recur' => { op=>'!=', value=>0 }, - 'edate' => { op=>'>', value=>$now }, - }, ); - - my @cust_pkg = - grep { $_->part_pkg->recur != 0 - && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/ - } - qsearch ( 'cust_pkg', { - 'bill' => { op=>'>', value=>$now } - } ); - - foreach my $cust_bill_pkg ( @cust_bill_pkg) { - my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; - - my $elapsed = $now - $cust_bill_pkg->sdate; - $elapsed = 0 if $elapsed < 0; - - my $remaining = 1 - $elapsed/$period; - - my $unearned = $remaining * $cust_bill_pkg->recur; - $total += $unearned; - - } - - foreach my $cust_pkg ( @cust_pkg ) { - my $period = $cust_pkg->bill - $cust_pkg->last_bill; - - my $elapsed = $now - $cust_pkg->last_bill; - $elapsed = 0 if $elapsed < 0; - - my $remaining = 1 - $elapsed/$period; - - my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy - $total_legacy += $unearned; - - } - - $total = sprintf('%.2f', $total); - $total_legacy = sprintf('%.2f', $total_legacy); - -%> - -<%= include("/elements/header.html", 'Prepaid Income (Unearned Revenue) Report', +% +% +% #doesn't yet deal with daily/weekly packages +% +% #needs to be re-written in sql for efficiency +% +% my $time = time; +% +% my $now = $cgi->param('date') && str2time($cgi->param('date')) || $time; +% $now =~ /^(\d+)$/ or die "unparsable date?"; +% $now = $1; +% +% my( $total, $total_legacy ) = ( 0, 0 ); +% +% my @cust_bill_pkg = +% grep { $_->cust_pkg && $_->cust_pkg->part_pkg->freq !~ /^([01]|\d+[dw])$/ } +% qsearch( 'cust_bill_pkg', { +% 'recur' => { op=>'!=', value=>0 }, +% 'edate' => { op=>'>', value=>$now }, +% }, ); +% +% my @cust_pkg = +% grep { $_->part_pkg->recur != 0 +% && $_->part_pkg->freq !~ /^([01]|\d+[dw])$/ +% } +% qsearch ( 'cust_pkg', { +% 'bill' => { op=>'>', value=>$now } +% } ); +% +% foreach my $cust_bill_pkg ( @cust_bill_pkg) { +% my $period = $cust_bill_pkg->edate - $cust_bill_pkg->sdate; +% +% my $elapsed = $now - $cust_bill_pkg->sdate; +% $elapsed = 0 if $elapsed < 0; +% +% my $remaining = 1 - $elapsed/$period; +% +% my $unearned = $remaining * $cust_bill_pkg->recur; +% $total += $unearned; +% +% } +% +% foreach my $cust_pkg ( @cust_pkg ) { +% my $period = $cust_pkg->bill - $cust_pkg->last_bill; +% +% my $elapsed = $now - $cust_pkg->last_bill; +% $elapsed = 0 if $elapsed < 0; +% +% my $remaining = 1 - $elapsed/$period; +% +% my $unearned = $remaining * $cust_pkg->part_pkg->recur; #!! only works for flat/legacy +% $total_legacy += $unearned; +% +% } +% +% $total = sprintf('%.2f', $total); +% $total_legacy = sprintf('%.2f', $total_legacy); +% +% + + +<% include("/elements/header.html", 'Prepaid Income (Unearned Revenue) Report', menubar( 'Main Menu'=>$p, ) ) %> -<%= table() %> +<% table() %> <TR> <TH>Actual Unearned Revenue</TH> <TH>Legacy Unearned Revenue</TH> </TR> <TR> - <TD ALIGN="right">$<%= $total %> + <TD ALIGN="right">$<% $total %> <TD ALIGN="right"> - <%= $now == $time ? "\$$total_legacy" : '<i>N/A</i>'%> + <% $now == $time ? "\$$total_legacy" : '<i>N/A</i>'%> </TD> </TR> diff --git a/httemplate/search/report_prepaid_income.html b/httemplate/search/report_prepaid_income.html index 4359918f9..305441db7 100644 --- a/httemplate/search/report_prepaid_income.html +++ b/httemplate/search/report_prepaid_income.html @@ -1,4 +1,4 @@ -<%= include('/elements/header.html', 'Prepaid Income (Unearned Revenue) Report', +<% include('/elements/header.html', 'Prepaid Income (Unearned Revenue) Report', '', '', '<LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> @@ -34,4 +34,4 @@ <INPUT TYPE="submit" VALUE="Generate report"> -<%= include('/elements/footer.html') %> +<% include('/elements/footer.html') %> diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi index 3052ea93c..4074f2680 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -1,139 +1,140 @@ -<% - - sub owed { - my($start, $end, %opt) = @_; - - my @where = (); - - #handle start and end ranges - - #24h * 60m * 60s - push @where, "cust_bill._date <= extract(epoch from now())-". - ($start * 86400) - if $start; - - push @where, "cust_bill._date > extract(epoch from now()) - ". - ($end * 86400) - if $end; - - #handle 'cust' option - - push @where, "cust_main.custnum = cust_bill.custnum" - if $opt{'cust'}; - - #handle 'agentnum' option - my $join = ''; - if ( $opt{'agentnum'} ) { - $join = 'LEFT JOIN cust_main USING ( custnum )'; - push @where, "agentnum = '$opt{'agentnum'}'"; - } - - my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : ''; - - my $as = $opt{'noas'} ? '' : "as owed_${start}_$end"; - - my $charged = <<END; - sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_credit_bill - where cust_bill.invnum = cust_credit_bill.invnum ) - ,0 - ) - - ) -END - - "coalesce( ( select $charged from cust_bill $join $where ) ,0 ) $as"; - - } - - my @ranges = ( - [ 0, 30 ], - [ 30, 60 ], - [ 60, 90 ], - [ 90, 0 ], - [ 0, 0 ], - ); - - my $owed_cols = join(',', map owed( @$_, 'cust'=>1 ), @ranges ); - - my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql; - - my $active_sql = FS::cust_pkg->active_sql; - my $inactive_sql = FS::cust_pkg->inactive_sql; - my $suspended_sql = FS::cust_pkg->inactive_sql; - my $cancelled_sql = FS::cust_pkg->inactive_sql; - - my $packages_cols = <<END; - ( $select_count_pkgs ) AS num_pkgs_sql, - ( $select_count_pkgs AND $active_sql ) AS active_pkgs, - ( $select_count_pkgs AND $inactive_sql ) AS inactive_pkgs, - ( $select_count_pkgs AND $suspended_sql ) AS suspended_pkgs, - ( $select_count_pkgs AND $cancelled_sql ) AS cancelled_pkgs -END - - my $where = "where ". owed(0, 0, 'cust'=>1, 'noas'=>1). " > 0"; - - my $agentnum = ''; - if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - $agentnum = $1; - $where .= " AND agentnum = '$agentnum' "; - } - - #here is the agent virtualization - $where .= ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql; - - my $count_sql = "select count(*) from cust_main $where"; - - my $sql_query = { - 'table' => 'cust_main', - 'hashref' => {}, - 'select' => "*, $owed_cols, $packages_cols", - 'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)", - }; - - my $total_sql = "select ". - join(',', map owed( @$_, 'agentnum'=>$agentnum ), @ranges ); - - my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; - $total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr; - my $row = $total_sth->fetchrow_hashref(); - - my $conf = new FS::Conf; - my $money_char = $conf->config('money_char') || '$'; - - my $align = join('', map { /#/ ? 'r' : 'l' } FS::UI::Web::cust_header() ). - 'crrrrr'; - - my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; - - my $status_statuscol = sub { - #conceptual false laziness with cust_main::status... - my $row = shift; - - my $status = 'unknown'; - if ( $row->num_pkgs_sql == 0 ) { - $status = 'prospect'; - } elsif ( $row->active_pkgs > 0 ) { - $status = 'active'; - } elsif ( $row->inactive_pkgs > 0 ) { - $status = 'inactive'; - } elsif ( $row->suspended_pkgs > 0 ) { - $status = 'suspended'; - } elsif ( $row->cancelled_pkgs > 0 ) { - $status = 'cancelled' - } - - ( ucfirst($status), $FS::cust_main::statuscolor{$status} ); - }; - - -%><%= include( 'elements/search.html', +% +% +% sub owed { +% my($start, $end, %opt) = @_; +% +% my @where = (); +% +% #handle start and end ranges +% +% #24h * 60m * 60s +% push @where, "cust_bill._date <= extract(epoch from now())-". +% ($start * 86400) +% if $start; +% +% push @where, "cust_bill._date > extract(epoch from now()) - ". +% ($end * 86400) +% if $end; +% +% #handle 'cust' option +% +% push @where, "cust_main.custnum = cust_bill.custnum" +% if $opt{'cust'}; +% +% #handle 'agentnum' option +% my $join = ''; +% if ( $opt{'agentnum'} ) { +% $join = 'LEFT JOIN cust_main USING ( custnum )'; +% push @where, "agentnum = '$opt{'agentnum'}'"; +% } +% +% my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : ''; +% +% my $as = $opt{'noas'} ? '' : "as owed_${start}_$end"; +% +% my $charged = <<END; +% sum( charged +% - coalesce( +% ( select sum(amount) from cust_bill_pay +% where cust_bill.invnum = cust_bill_pay.invnum ) +% ,0 +% ) +% - coalesce( +% ( select sum(amount) from cust_credit_bill +% where cust_bill.invnum = cust_credit_bill.invnum ) +% ,0 +% ) +% +% ) +%END +% +% "coalesce( ( select $charged from cust_bill $join $where ) ,0 ) $as"; +% +% } +% +% my @ranges = ( +% [ 0, 30 ], +% [ 30, 60 ], +% [ 60, 90 ], +% [ 90, 0 ], +% [ 0, 0 ], +% ); +% +% my $owed_cols = join(',', map owed( @$_, 'cust'=>1 ), @ranges ); +% +% my $select_count_pkgs = FS::cust_main->select_count_pkgs_sql; +% +% my $active_sql = FS::cust_pkg->active_sql; +% my $inactive_sql = FS::cust_pkg->inactive_sql; +% my $suspended_sql = FS::cust_pkg->inactive_sql; +% my $cancelled_sql = FS::cust_pkg->inactive_sql; +% +% my $packages_cols = <<END; +% ( $select_count_pkgs ) AS num_pkgs_sql, +% ( $select_count_pkgs AND $active_sql ) AS active_pkgs, +% ( $select_count_pkgs AND $inactive_sql ) AS inactive_pkgs, +% ( $select_count_pkgs AND $suspended_sql ) AS suspended_pkgs, +% ( $select_count_pkgs AND $cancelled_sql ) AS cancelled_pkgs +%END +% +% my $where = "where ". owed(0, 0, 'cust'=>1, 'noas'=>1). " > 0"; +% +% my $agentnum = ''; +% if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { +% $agentnum = $1; +% $where .= " AND agentnum = '$agentnum' "; +% } +% +% #here is the agent virtualization +% $where .= ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql; +% +% my $count_sql = "select count(*) from cust_main $where"; +% +% my $sql_query = { +% 'table' => 'cust_main', +% 'hashref' => {}, +% 'select' => "*, $owed_cols, $packages_cols", +% 'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)", +% }; +% +% my $total_sql = "select ". +% join(',', map owed( @$_, 'agentnum'=>$agentnum ), @ranges ); +% +% my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; +% $total_sth->execute or die "error executing $total_sql: ". $total_sth->errstr; +% my $row = $total_sth->fetchrow_hashref(); +% +% my $conf = new FS::Conf; +% my $money_char = $conf->config('money_char') || '$'; +% +% my $align = join('', map { /#/ ? 'r' : 'l' } FS::UI::Web::cust_header() ). +% 'crrrrr'; +% +% my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; +% +% my $status_statuscol = sub { +% #conceptual false laziness with cust_main::status... +% my $row = shift; +% +% my $status = 'unknown'; +% if ( $row->num_pkgs_sql == 0 ) { +% $status = 'prospect'; +% } elsif ( $row->active_pkgs > 0 ) { +% $status = 'active'; +% } elsif ( $row->inactive_pkgs > 0 ) { +% $status = 'inactive'; +% } elsif ( $row->suspended_pkgs > 0 ) { +% $status = 'suspended'; +% } elsif ( $row->cancelled_pkgs > 0 ) { +% $status = 'cancelled' +% } +% +% ( ucfirst($status), $FS::cust_main::statuscolor{$status} ); +% }; +% +% +% +<% include( 'elements/search.html', 'title' => 'Accounts Receivable Aging Summary', 'name' => 'customers', 'query' => $sql_query, diff --git a/httemplate/search/report_receivables.html b/httemplate/search/report_receivables.html index fc5174116..1ae4b8e6f 100755 --- a/httemplate/search/report_receivables.html +++ b/httemplate/search/report_receivables.html @@ -1,10 +1,10 @@ -<%= include('/elements/header.html', 'Accounts Receivable Aging Summary' ) %> +<% include('/elements/header.html', 'Accounts Receivable Aging Summary' ) %> <FORM ACTION="report_receivables.cgi" METHOD="GET"> <TABLE> - <%= include( '/elements/tr-select-agent.html' ) %> + <% include( '/elements/tr-select-agent.html' ) %> </TABLE> diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 1b6f40b8a..569e6e79a 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -1,375 +1,375 @@ -<% - -my $conf = new FS::Conf; -my $money_char = $conf->config('money_char') || '$'; - -my $user = getotaker; - -my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - -my $join_cust = " - JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) -"; -my $from_join_cust = " - FROM cust_bill_pkg - $join_cust -"; -my $join_pkg = " - LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) -"; - -my $where = "WHERE _date >= $beginning AND _date <= $ending "; -my @base_param = qw( county county state state country ); -if ( $conf->exists('tax-ship_address') ) { - - $where .= " - AND ( ( ( ship_last IS NULL OR ship_last = '' ) - AND ( county = ? OR ? = '' ) - AND ( state = ? OR ? = '' ) - AND country = ? - ) - OR ( ship_last IS NOT NULL AND ship_last != '' - AND ( ship_county = ? OR ? = '' ) - AND ( ship_state = ? OR ? = '' ) - AND ship_country = ? - ) - ) - "; - # AND payby != 'COMP' - - push @base_param, @base_param; - -} else { - - $where .= " - AND ( county = ? OR ? = '' ) - AND ( state = ? OR ? = '' ) - AND country = ? - "; - # AND payby != 'COMP' - -} - -my $agentname = ''; -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - my $agent = qsearchs('agent', { 'agentnum' => $1 } ); - die "agent not found" unless $agent; - $agentname = $agent->agent; - $where .= ' AND agentnum = '. $agent->agentnum; -} - -my $gotcust = " - WHERE 0 < ( SELECT COUNT(*) FROM cust_main -"; -if ( $conf->exists('tax-ship_address') ) { - - $gotcust .= " - WHERE - - ( cust_main_county.country = cust_main.country - OR cust_main_county.country = cust_main.ship_country - ) - - AND - - ( - - ( ( ship_last IS NULL OR ship_last = '' ) - AND ( cust_main_county.country = cust_main.country ) - AND ( cust_main_county.state = cust_main.state - OR cust_main_county.state = '' - OR cust_main_county.state IS NULL ) - AND ( cust_main_county.county = cust_main.county - OR cust_main_county.county = '' - OR cust_main_county.county IS NULL ) - ) - - OR - - ( ship_last IS NOT NULL AND ship_last != '' - AND ( cust_main_county.country = cust_main.ship_country ) - AND ( cust_main_county.state = cust_main.ship_state - OR cust_main_county.state = '' - OR cust_main_county.state IS NULL ) - AND ( cust_main_county.county = cust_main.ship_county - OR cust_main_county.county = '' - OR cust_main_county.county IS NULL ) - ) - - ) - - LIMIT 1 - ) - "; - -} else { - - $gotcust .= " - WHERE ( cust_main.county = cust_main_county.county - OR cust_main_county.county = '' - OR cust_main_county.county IS NULL ) - AND ( cust_main.state = cust_main_county.state - OR cust_main_county.state = '' - OR cust_main_county.state IS NULL ) - AND ( cust_main.country = cust_main_county.country ) - LIMIT 1 - ) - "; - -} - -my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0, 0 ); -my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0 ); -my $out = 'Out of taxable region(s)'; -my %regions = (); -foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) { - #warn $r->county. ' '. $r->state. ' '. $r->country. "\n"; - - my $label = getlabel($r); - $regions{$label}->{'label'} = $label; - $regions{$label}->{'url_param'} = join(';', map "$_=".$r->$_(), qw( county state country ) ); - - my @param = @base_param; - my $mywhere = $where; - - if ( $r->taxclass ) { - $mywhere .= " AND taxclass = ? "; - push @param, 'taxclass'; - $regions{$label}->{'url_param'} .= ';taxclass='. $r->taxclass - if $cgi->param('show_taxclasses'); - } - - my $fromwhere = $from_join_cust. $join_pkg. $mywhere. " AND payby != 'COMP' "; - -# my $label = getlabel($r); -# $regions{$label}->{'label'} = $label; - - my $nottax = 'pkgnum != 0'; - - ## calculate total for this region - - my $t = scalar_sql($r, \@param, - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax" - ); - $total += $t; - $regions{$label}->{'total'} += $t; - - ## calculate customer-exemption for this region - -## my $taxable = $t; - -# my($taxable, $x_cust) = (0, 0); -# foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i } -# qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { -# $taxable += scalar_sql($r, \@param, -# "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )" -# ); -# -# $x_cust += scalar_sql($r, \@param, -# "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'" -# ); -# } - - my $x_cust = scalar_sql($r, \@param, - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) - $fromwhere AND $nottax AND tax = 'Y' " - ); - - $exempt_cust += $x_cust; - $regions{$label}->{'exempt_cust'} += $x_cust; - - ## calculate package-exemption for this region - - my $x_pkg = scalar_sql($r, \@param, - "SELECT SUM( - ( CASE WHEN part_pkg.setuptax = 'Y' - THEN cust_bill_pkg.setup - ELSE 0 - END - ) - + - ( CASE WHEN part_pkg.recurtax = 'Y' - THEN cust_bill_pkg.recur - ELSE 0 - END - ) - ) - $fromwhere - AND $nottax - AND ( - ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) - OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) - ) - AND ( tax != 'Y' OR tax IS NULL ) - " - ); - $exempt_pkg += $x_pkg; - $regions{$label}->{'exempt_pkg'} += $x_pkg; - - ## calculate monthly exemption (texas tax) for this region - - # count up all the cust_tax_exempt_pkg records associated with - # the actual line items. - - my $x_monthly = scalar_sql($r, \@param, - "SELECT SUM(amount) - FROM cust_tax_exempt_pkg - JOIN cust_bill_pkg USING ( billpkgnum ) - $join_cust $join_pkg - $mywhere" - ); -# if ( $x_monthly ) { -# #warn $r->taxnum(). ": $x_monthly\n"; -# $taxable -= $x_monthly; -# } - - $exempt_monthly += $x_monthly; - $regions{$label}->{'exempt_monthly'} += $x_monthly; - - my $taxable = $t - $x_cust - $x_pkg - $x_monthly; - - $tot_taxable += $taxable; - $regions{$label}->{'taxable'} += $taxable; - - $owed += $taxable * ($r->tax/100); - $regions{$label}->{'owed'} += $taxable * ($r->tax/100); - - if ( defined($regions{$label}->{'rate'}) - && $regions{$label}->{'rate'} != $r->tax.'%' ) { - $regions{$label}->{'rate'} = 'variable'; - } else { - $regions{$label}->{'rate'} = $r->tax.'%'; - } - -} - -my $taxwhere = "$from_join_cust $where AND payby != 'COMP' "; -my @taxparam = @base_param; -my %base_regions = (); -#foreach my $label ( keys %regions ) { -foreach my $r ( - qsearch( 'cust_main_county', - {}, - 'DISTINCT ON (country, state, county, taxname) *', - $gotcust - ) -) { - - #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n"; - - my $label = getlabel($r); - - #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' "; - #my @param = @base_param; - - #match itemdesc if necessary! - my $named_tax = - $r->taxname - ? 'AND itemdesc = '. dbh->quote($r->taxname) - : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; - my $x = scalar_sql($r, \@taxparam, - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $taxwhere ". - "AND pkgnum = 0 $named_tax", - ); - $tax += $x; - $regions{$label}->{'tax'} += $x; - - if ( $cgi->param('show_taxclasses') ) { - my $base_label = getlabel($r, 'no_taxclass'=>1 ); - $base_regions{$base_label}->{'label'} = $base_label; - $base_regions{$base_label}->{'url_param'} = - join(';', map "$_=".$r->$_(), qw( county state country ) ); - $base_regions{$base_label}->{'tax'} += $x; - } - -} - -#ordering -my @regions = - map $regions{$_}, - sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } - keys %regions; - -my @base_regions = - map $base_regions{$_}, - sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } - keys %base_regions; - -push @regions, { - 'label' => 'Total', - 'url_param' => '', - 'total' => $total, - 'exempt_cust' => $exempt_cust, - 'exempt_pkg' => $exempt_pkg, - 'exempt_monthly' => $exempt_monthly, - 'taxable' => $tot_taxable, - 'rate' => '', - 'owed' => $owed, - 'tax' => $tax, -}; - -#-- - -sub getlabel { - my $r = shift; - my %opt = @_; - - my $label; - if ( - $r->tax == 0 - && ! scalar( qsearch('cust_main_county', { 'state' => $r->state, - 'county' => $r->county, - 'country' => $r->country, - 'tax' => { op=>'>', value=>0 }, - } - ) - ) - - ) { - #kludge to avoid "will not stay shared" warning - my $out = 'Out of taxable region(s)'; - $label = $out; - } elsif ( $r->taxname ) { - $label = $r->taxname; -# $regions{$label}->{'taxname'} = $label; -# push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country ); - } else { - $label = $r->country; - $label = $r->state.", $label" if $r->state; - $label = $r->county." county, $label" if $r->county; - $label = "$label (". $r->taxclass. ")" - if $r->taxclass - && $cgi->param('show_taxclasses') - && ! $opt{'no_taxclass'}; - #$label = $r->taxname. " ($label)" if $r->taxname; - } - return $label; -} - -#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up -#to FS::Report or FS::Record or who the fuck knows where) -sub scalar_sql { - my( $r, $param, $sql ) = @_; - #warn "$sql\n"; - my $sth = dbh->prepare($sql) or die dbh->errstr; - $sth->execute( map $r->$_(), @$param ) - or die "Unexpected error executing statement $sql: ". $sth->errstr; - $sth->fetchrow_arrayref->[0] || 0; -} - -%> - -<% -my $dateagentlink = "begin=$beginning;end=$ending"; -$dateagentlink .= ';agentnum='. $cgi->param('agentnum') - if length($agentname); -my $baselink = $p. "search/cust_bill_pkg.cgi?$dateagentlink"; -my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; -%> - -<%= include("/elements/header.html", "$agentname Sales Tax Report - ". +% +% +%my $conf = new FS::Conf; +%my $money_char = $conf->config('money_char') || '$'; +% +%my $user = getotaker; +% +%my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +% +%my $join_cust = " +% JOIN cust_bill USING ( invnum ) +% LEFT JOIN cust_main USING ( custnum ) +%"; +%my $from_join_cust = " +% FROM cust_bill_pkg +% $join_cust +%"; +%my $join_pkg = " +% LEFT JOIN cust_pkg USING ( pkgnum ) +% LEFT JOIN part_pkg USING ( pkgpart ) +%"; +% +%my $where = "WHERE _date >= $beginning AND _date <= $ending "; +%my @base_param = qw( county county state state country ); +%if ( $conf->exists('tax-ship_address') ) { +% +% $where .= " +% AND ( ( ( ship_last IS NULL OR ship_last = '' ) +% AND ( county = ? OR ? = '' ) +% AND ( state = ? OR ? = '' ) +% AND country = ? +% ) +% OR ( ship_last IS NOT NULL AND ship_last != '' +% AND ( ship_county = ? OR ? = '' ) +% AND ( ship_state = ? OR ? = '' ) +% AND ship_country = ? +% ) +% ) +% "; +% # AND payby != 'COMP' +% +% push @base_param, @base_param; +% +%} else { +% +% $where .= " +% AND ( county = ? OR ? = '' ) +% AND ( state = ? OR ? = '' ) +% AND country = ? +% "; +% # AND payby != 'COMP' +% +%} +% +%my $agentname = ''; +%if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { +% my $agent = qsearchs('agent', { 'agentnum' => $1 } ); +% die "agent not found" unless $agent; +% $agentname = $agent->agent; +% $where .= ' AND agentnum = '. $agent->agentnum; +%} +% +%my $gotcust = " +% WHERE 0 < ( SELECT COUNT(*) FROM cust_main +%"; +%if ( $conf->exists('tax-ship_address') ) { +% +% $gotcust .= " +% WHERE +% +% ( cust_main_county.country = cust_main.country +% OR cust_main_county.country = cust_main.ship_country +% ) +% +% AND +% +% ( +% +% ( ( ship_last IS NULL OR ship_last = '' ) +% AND ( cust_main_county.country = cust_main.country ) +% AND ( cust_main_county.state = cust_main.state +% OR cust_main_county.state = '' +% OR cust_main_county.state IS NULL ) +% AND ( cust_main_county.county = cust_main.county +% OR cust_main_county.county = '' +% OR cust_main_county.county IS NULL ) +% ) +% +% OR +% +% ( ship_last IS NOT NULL AND ship_last != '' +% AND ( cust_main_county.country = cust_main.ship_country ) +% AND ( cust_main_county.state = cust_main.ship_state +% OR cust_main_county.state = '' +% OR cust_main_county.state IS NULL ) +% AND ( cust_main_county.county = cust_main.ship_county +% OR cust_main_county.county = '' +% OR cust_main_county.county IS NULL ) +% ) +% +% ) +% +% LIMIT 1 +% ) +% "; +% +%} else { +% +% $gotcust .= " +% WHERE ( cust_main.county = cust_main_county.county +% OR cust_main_county.county = '' +% OR cust_main_county.county IS NULL ) +% AND ( cust_main.state = cust_main_county.state +% OR cust_main_county.state = '' +% OR cust_main_county.state IS NULL ) +% AND ( cust_main.country = cust_main_county.country ) +% LIMIT 1 +% ) +% "; +% +%} +% +%my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0, 0 ); +%my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0 ); +%my $out = 'Out of taxable region(s)'; +%my %regions = (); +%foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) { +% #warn $r->county. ' '. $r->state. ' '. $r->country. "\n"; +% +% my $label = getlabel($r); +% $regions{$label}->{'label'} = $label; +% $regions{$label}->{'url_param'} = join(';', map "$_=".$r->$_(), qw( county state country ) ); +% +% my @param = @base_param; +% my $mywhere = $where; +% +% if ( $r->taxclass ) { +% $mywhere .= " AND taxclass = ? "; +% push @param, 'taxclass'; +% $regions{$label}->{'url_param'} .= ';taxclass='. $r->taxclass +% if $cgi->param('show_taxclasses'); +% } +% +% my $fromwhere = $from_join_cust. $join_pkg. $mywhere. " AND payby != 'COMP' "; +% +%# my $label = getlabel($r); +%# $regions{$label}->{'label'} = $label; +% +% my $nottax = 'pkgnum != 0'; +% +% ## calculate total for this region +% +% my $t = scalar_sql($r, \@param, +% "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax" +% ); +% $total += $t; +% $regions{$label}->{'total'} += $t; +% +% ## calculate customer-exemption for this region +% +%## my $taxable = $t; +% +%# my($taxable, $x_cust) = (0, 0); +%# foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i } +%# qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { +%# $taxable += scalar_sql($r, \@param, +%# "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )" +%# ); +%# +%# $x_cust += scalar_sql($r, \@param, +%# "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'" +%# ); +%# } +% +% my $x_cust = scalar_sql($r, \@param, +% "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) +% $fromwhere AND $nottax AND tax = 'Y' " +% ); +% +% $exempt_cust += $x_cust; +% $regions{$label}->{'exempt_cust'} += $x_cust; +% +% ## calculate package-exemption for this region +% +% my $x_pkg = scalar_sql($r, \@param, +% "SELECT SUM( +% ( CASE WHEN part_pkg.setuptax = 'Y' +% THEN cust_bill_pkg.setup +% ELSE 0 +% END +% ) +% + +% ( CASE WHEN part_pkg.recurtax = 'Y' +% THEN cust_bill_pkg.recur +% ELSE 0 +% END +% ) +% ) +% $fromwhere +% AND $nottax +% AND ( +% ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) +% OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) +% ) +% AND ( tax != 'Y' OR tax IS NULL ) +% " +% ); +% $exempt_pkg += $x_pkg; +% $regions{$label}->{'exempt_pkg'} += $x_pkg; +% +% ## calculate monthly exemption (texas tax) for this region +% +% # count up all the cust_tax_exempt_pkg records associated with +% # the actual line items. +% +% my $x_monthly = scalar_sql($r, \@param, +% "SELECT SUM(amount) +% FROM cust_tax_exempt_pkg +% JOIN cust_bill_pkg USING ( billpkgnum ) +% $join_cust $join_pkg +% $mywhere" +% ); +%# if ( $x_monthly ) { +%# #warn $r->taxnum(). ": $x_monthly\n"; +%# $taxable -= $x_monthly; +%# } +% +% $exempt_monthly += $x_monthly; +% $regions{$label}->{'exempt_monthly'} += $x_monthly; +% +% my $taxable = $t - $x_cust - $x_pkg - $x_monthly; +% +% $tot_taxable += $taxable; +% $regions{$label}->{'taxable'} += $taxable; +% +% $owed += $taxable * ($r->tax/100); +% $regions{$label}->{'owed'} += $taxable * ($r->tax/100); +% +% if ( defined($regions{$label}->{'rate'}) +% && $regions{$label}->{'rate'} != $r->tax.'%' ) { +% $regions{$label}->{'rate'} = 'variable'; +% } else { +% $regions{$label}->{'rate'} = $r->tax.'%'; +% } +% +%} +% +%my $taxwhere = "$from_join_cust $where AND payby != 'COMP' "; +%my @taxparam = @base_param; +%my %base_regions = (); +%#foreach my $label ( keys %regions ) { +%foreach my $r ( +% qsearch( 'cust_main_county', +% {}, +% 'DISTINCT ON (country, state, county, taxname) *', +% $gotcust +% ) +%) { +% +% #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n"; +% +% my $label = getlabel($r); +% +% #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' "; +% #my @param = @base_param; +% +% #match itemdesc if necessary! +% my $named_tax = +% $r->taxname +% ? 'AND itemdesc = '. dbh->quote($r->taxname) +% : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; +% my $x = scalar_sql($r, \@taxparam, +% "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $taxwhere ". +% "AND pkgnum = 0 $named_tax", +% ); +% $tax += $x; +% $regions{$label}->{'tax'} += $x; +% +% if ( $cgi->param('show_taxclasses') ) { +% my $base_label = getlabel($r, 'no_taxclass'=>1 ); +% $base_regions{$base_label}->{'label'} = $base_label; +% $base_regions{$base_label}->{'url_param'} = +% join(';', map "$_=".$r->$_(), qw( county state country ) ); +% $base_regions{$base_label}->{'tax'} += $x; +% } +% +%} +% +%#ordering +%my @regions = +% map $regions{$_}, +% sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } +% keys %regions; +% +%my @base_regions = +% map $base_regions{$_}, +% sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } +% keys %base_regions; +% +%push @regions, { +% 'label' => 'Total', +% 'url_param' => '', +% 'total' => $total, +% 'exempt_cust' => $exempt_cust, +% 'exempt_pkg' => $exempt_pkg, +% 'exempt_monthly' => $exempt_monthly, +% 'taxable' => $tot_taxable, +% 'rate' => '', +% 'owed' => $owed, +% 'tax' => $tax, +%}; +% +%#-- +% +%sub getlabel { +% my $r = shift; +% my %opt = @_; +% +% my $label; +% if ( +% $r->tax == 0 +% && ! scalar( qsearch('cust_main_county', { 'state' => $r->state, +% 'county' => $r->county, +% 'country' => $r->country, +% 'tax' => { op=>'>', value=>0 }, +% } +% ) +% ) +% +% ) { +% #kludge to avoid "will not stay shared" warning +% my $out = 'Out of taxable region(s)'; +% $label = $out; +% } elsif ( $r->taxname ) { +% $label = $r->taxname; +%# $regions{$label}->{'taxname'} = $label; +%# push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country ); +% } else { +% $label = $r->country; +% $label = $r->state.", $label" if $r->state; +% $label = $r->county." county, $label" if $r->county; +% $label = "$label (". $r->taxclass. ")" +% if $r->taxclass +% && $cgi->param('show_taxclasses') +% && ! $opt{'no_taxclass'}; +% #$label = $r->taxname. " ($label)" if $r->taxname; +% } +% return $label; +%} +% +%#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up +%#to FS::Report or FS::Record or who the fuck knows where) +%sub scalar_sql { +% my( $r, $param, $sql ) = @_; +% #warn "$sql\n"; +% my $sth = dbh->prepare($sql) or die dbh->errstr; +% $sth->execute( map $r->$_(), @$param ) +% or die "Unexpected error executing statement $sql: ". $sth->errstr; +% $sth->fetchrow_arrayref->[0] || 0; +%} +% +% +% +%my $dateagentlink = "begin=$beginning;end=$ending"; +%$dateagentlink .= ';agentnum='. $cgi->param('agentnum') +% if length($agentname); +%my $baselink = $p. "search/cust_bill_pkg.cgi?$dateagentlink"; +%my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; +% + + +<% include("/elements/header.html", "$agentname Sales Tax Report - ". ( $beginning ? time2str('%h %o %Y ', $beginning ) : '' @@ -383,7 +383,7 @@ my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; ) %> -<%= include('/elements/table-grid.html') %> +<% include('/elements/table-grid.html') %> <TR> <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> @@ -392,9 +392,11 @@ my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Rate</TH> <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH> <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax owed</TH> - <% unless ( $cgi->param('show_taxclasses') ) { %> +% unless ( $cgi->param('show_taxclasses') ) { + <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax invoiced</TH> - <% } %> +% } + </TR> <TR> <TH CLASS="grid" BGCOLOR="#cccccc">Total</TH> @@ -407,130 +409,129 @@ my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; <TH CLASS="grid" BGCOLOR="#cccccc"></TH> <TH CLASS="grid" BGCOLOR="#cccccc">Taxable</TH> </TR> - -<% my $bgcolor1 = '#eeeeee'; - my $bgcolor2 = '#ffffff'; - my $bgcolor; -%> - - <% foreach my $region ( @regions ) { - - if ( $bgcolor eq $bgcolor1 ) { - $bgcolor = $bgcolor2; - } else { - $bgcolor = $bgcolor1; - } - - my $link = ''; - if ( $region->{'label'} ne 'Total' ) { - if ( $region->{'label'} eq $out ) { - $link = ';out=1'; - } else { - $link = ';'. $region->{'url_param'}; - } - } +% my $bgcolor1 = '#eeeeee'; +% my $bgcolor2 = '#ffffff'; +% my $bgcolor; +% +% foreach my $region ( @regions ) { +% +% if ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } +% +% my $link = ''; +% if ( $region->{'label'} ne 'Total' ) { +% if ( $region->{'label'} eq $out ) { +% $link = ';out=1'; +% } else { +% $link = ';'. $region->{'url_param'}; +% } +% } +% +% +% +% +% - - - %> - <TR> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $region->{'label'} %></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $baselink. $link %>;nottax=1"><%= $money_char %><%= sprintf('%.2f', $region->{'total'} ) %></A> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} %></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> + <A HREF="<% $baselink. $link %>;nottax=1"><% $money_char %><% sprintf('%.2f', $region->{'total'} ) %></A> </TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $baselink. $link %>;nottax=1;cust_tax=Y"><%= $money_char %><%= sprintf('%.2f', $region->{'exempt_cust'} ) %></A> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> + <A HREF="<% $baselink. $link %>;nottax=1;cust_tax=Y"><% $money_char %><% sprintf('%.2f', $region->{'exempt_cust'} ) %></A> </TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $baselink. $link %>;nottax=1;pkg_tax=Y"><%= $money_char %><%= sprintf('%.2f', $region->{'exempt_pkg'} ) %></A> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> + <A HREF="<% $baselink. $link %>;nottax=1;pkg_tax=Y"><% $money_char %><% sprintf('%.2f', $region->{'exempt_pkg'} ) %></A> </TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $exemptlink. $link %>"><%= $money_char %><%= sprintf('%.2f', $region->{'exempt_monthly'} ) %></A> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> + <A HREF="<% $exemptlink. $link %>"><% $money_char %><% sprintf('%.2f', $region->{'exempt_monthly'} ) %></A> </TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><FONT SIZE="+1"><B> = </B></FONT></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <%= $money_char %><%= sprintf('%.2f', $region->{'taxable'} ) %></A> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> = </B></FONT></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> + <% $money_char %><% sprintf('%.2f', $region->{'taxable'} ) %></A> </TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $region->{'label'} eq 'Total' ? '' : '<FONT FACE="sans-serif" SIZE="+1"><B> X </B></FONT>' %></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"><%= $region->{'rate'} %></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $region->{'label'} eq 'Total' ? '' : '<FONT FACE="sans-serif" SIZE="+1"><B> = </B></FONT>' %></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <%= $money_char %><%= sprintf('%.2f', $region->{'owed'} ) %> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} eq 'Total' ? '' : '<FONT FACE="sans-serif" SIZE="+1"><B> X </B></FONT>' %></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"><% $region->{'rate'} %></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} eq 'Total' ? '' : '<FONT FACE="sans-serif" SIZE="+1"><B> = </B></FONT>' %></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> + <% $money_char %><% sprintf('%.2f', $region->{'owed'} ) %> </TD> - <% unless ( $cgi->param('show_taxclasses') ) { %> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $baselink. $link %>;istax=1"><%= $money_char %><%= sprintf('%.2f', $region->{'tax'} ) %></A> +% unless ( $cgi->param('show_taxclasses') ) { + + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> + <A HREF="<% $baselink. $link %>;istax=1"><% $money_char %><% sprintf('%.2f', $region->{'tax'} ) %></A> </TD> - <% } %> +% } + </TR> - - <% } %> +% } -</TABLE> +</TABLE> +% if ( $cgi->param('show_taxclasses') ) { -<% if ( $cgi->param('show_taxclasses') ) { %> <BR> - <%= include('/elements/table-grid.html') %> + <% include('/elements/table-grid.html') %> <TR> <TH CLASS="grid" BGCOLOR="#cccccc"></TH> <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH> </TR> +% #some false laziness w/above +% $bgcolor1 = '#eeeeee'; +% $bgcolor2 = '#ffffff'; +% foreach my $region ( @base_regions ) { +% +% if ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } +% +% my $link = ''; +% #if ( $region->{'label'} ne 'Total' ) { +% if ( $region->{'label'} eq $out ) { +% $link = ';out=1'; +% } else { +% $link = ';'. $region->{'url_param'}; +% } +% #} +% - <% #some false laziness w/above - $bgcolor1 = '#eeeeee'; - $bgcolor2 = '#ffffff'; - foreach my $region ( @base_regions ) { - - if ( $bgcolor eq $bgcolor1 ) { - $bgcolor = $bgcolor2; - } else { - $bgcolor = $bgcolor1; - } - - my $link = ''; - #if ( $region->{'label'} ne 'Total' ) { - if ( $region->{'label'} eq $out ) { - $link = ';out=1'; - } else { - $link = ';'. $region->{'url_param'}; - } - #} - %> <TR> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $region->{'label'} %></TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $baselink. $link %>;istax=1"><%= $money_char %><%= sprintf('%.2f', $region->{'tax'} ) %></A> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} %></TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> + <A HREF="<% $baselink. $link %>;istax=1"><% $money_char %><% sprintf('%.2f', $region->{'tax'} ) %></A> </TD> </TR> +% } +% +% if ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } +% - <% } %> - - <% - if ( $bgcolor eq $bgcolor1 ) { - $bgcolor = $bgcolor2; - } else { - $bgcolor = $bgcolor1; - } - %> <TR> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>">Total</TD> - <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>" ALIGN="right"> - <A HREF="<%= $baselink %>;istax=1"><%= $money_char %><%= sprintf('%.2f', $tax ) %></A> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>">Total</TD> + <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> + <A HREF="<% $baselink %>;istax=1"><% $money_char %><% sprintf('%.2f', $tax ) %></A> </TD> </TR> </TABLE> +% } -<% } %> </BODY> </HTML> diff --git a/httemplate/search/report_tax.html b/httemplate/search/report_tax.html index bdeb8e237..6e78d354a 100755 --- a/httemplate/search/report_tax.html +++ b/httemplate/search/report_tax.html @@ -1,30 +1,31 @@ -<%= include('/elements/header.html', 'Tax Report' ) %> +<% include('/elements/header.html', 'Tax Report' ) %> <FORM ACTION="report_tax.cgi" METHOD="GET"> <TABLE> - <%= include( '/elements/tr-select-agent.html' ) %> + <% include( '/elements/tr-select-agent.html' ) %> - <%= include( '/elements/tr-input-beginning_ending.html' ) %> + <% include( '/elements/tr-input-beginning_ending.html' ) %> +% my $conf = new FS::Conf; +% if ( $conf->exists('enable_taxclasses') ) { +% - <% my $conf = new FS::Conf; - if ( $conf->exists('enable_taxclasses') ) { - %> <TR> <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="show_taxclasses" VALUE="1"></TD> <TD>Show tax classes</TD> </TR> - <% } %> +% } +% my @pkg_class = qsearch('pkg_class', {}); +% if ( @pkg_class ) { +% - <% my @pkg_class = qsearch('pkg_class', {}); - if ( @pkg_class ) { - %> <TR> <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="show_pkgclasses" VALUE="1"></TD> <TD>Show package classes</TD> </TR> - <% } %> +% } + </TABLE> @@ -32,4 +33,4 @@ </FORM> -<%= include('/elements/footer.html') %> +<% include('/elements/footer.html') %> diff --git a/httemplate/search/sql.html b/httemplate/search/sql.html index b28c045d1..681a95d60 100644 --- a/httemplate/search/sql.html +++ b/httemplate/search/sql.html @@ -1,4 +1,4 @@ -<%= include( 'elements/search.html', +<% include( 'elements/search.html', 'title' => 'Query Results', 'name' => 'rows', 'query' => 'SELECT '. ( $cgi->param('sql') diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi index b84df1a03..caa2454e9 100644 --- a/httemplate/search/sqlradius.cgi +++ b/httemplate/search/sqlradius.cgi @@ -1,290 +1,296 @@ -<%= include( '/elements/header.html', 'RADIUS Sessions', +<% include( '/elements/header.html', 'RADIUS Sessions', include('/elements/menubar.html', 'Main menu' => $p, # popurl(2), ), ) %> +% +% ### +% # parse cgi params +% ### +% +% #sort of false laziness w/cust_pay.cgi +% my $beginning = ''; +% my $ending = ''; +% if ( $cgi->param('beginning') +% && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { +% $beginning = str2time($1); +% } +% if ( $cgi->param('ending') +% && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { +% $ending = str2time($1) + 86399; +% } +% if ( $cgi->param('begin') && $cgi->param('begin') =~ /^(\d+)$/ ) { +% $beginning = $1; +% } +% if ( $cgi->param('end') && $cgi->param('end') =~ /^(\d+)$/ ) { +% $ending = $1; +% } +% +% my $cgi_svc_acct = ''; +% if ( $cgi->param('svcnum') =~ /^(\d+)$/ ) { +% $cgi_svc_acct = qsearchs( 'svc_acct', { 'svcnum' => $1 } ); +% } elsif ( $cgi->param('username') =~ /^([^@]+)\@([^@]+)$/ ) { +% my %search = { 'username' => $1 }; +% my $svc_domain = qsearchs('svc_domain', { 'domain' => $2 } ); +% if ( $svc_domain ) { +% $search{'domsvc'} = $svc_domain->svcnum; +% } else { +% delete $search{'username'}; +% } +% $cgi_svc_acct = qsearchs( 'svc_acct', \%search ) +% if keys %search; +% } elsif ( $cgi->param('username') =~ /^(.+)$/ ) { +% $cgi_svc_acct = qsearchs( 'svc_acct', { 'username' => $1 } ); +% } +% +% my $ip = ''; +% if ( $cgi->param('ip') =~ /^((\d+\.){3}\d+)$/ ) { +% $ip = $1; +% } +% +% my $prefix = $cgi->param('prefix'); +% $prefix =~ s/\D//g; +% if ( $prefix =~ /^(\d+)$/ ) { +% $prefix = $1; +% $prefix = "011$prefix" unless $prefix =~ /^1/; +% } else { +% $prefix = ''; +% } +% +% ### +% # field formatting subroutines +% ### +% +% my %user2svc_acct = (); +% my $user_format = sub { +% my ( $user, $session, $part_export ) = @_; +% +% my $svc_acct = ''; +% if ( exists $user2svc_acct{$user} ) { +% $svc_acct = $user2svc_acct{$user}; +% } else { +% my %search = (); +% if ( $part_export->exporttype eq 'sqlradius_withdomain' ) { +% my $domain; +% if ( $user =~ /^([^@]+)\@([^@]+)$/ ) { +% $search{'username'} = $1; +% $domain = $2; +% } else { +% $search{'username'} = $user; +% $domain = $session->{'realm'}; +% } +% my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ); +% if ( $svc_domain ) { +% $search{'domsvc'} = $svc_domain->svcnum; +% } else { +% delete $search{'username'}; +% } +% } elsif ( $part_export->exporttype eq 'sqlradius' ) { +% $search{'username'} = $user; +% } else { +% die 'unknown export type '. $part_export->exporttype. +% " for $part_export\n"; +% } +% if ( keys %search ) { +% my @svc_acct = +% grep { qsearchs( 'export_svc', { +% 'exportnum' => $part_export->exportnum, +% 'svcpart' => $_->cust_svc->svcpart, +% } ) +% } qsearch( 'svc_acct', \%search ); +% if ( @svc_acct ) { +% warn 'multiple svc_acct records for user $user found; '. +% 'using first arbitrarily' +% if scalar(@svc_acct) > 1; +% $user2svc_acct{$user} = $svc_acct = shift @svc_acct; +% } +% } +% } +% +% if ( $svc_acct ) { +% my $svcnum = $svc_acct->svcnum; +% qq(<A HREF="${p}view/svc_acct.cgi?$svcnum"><B>$user</B></A>); +% } else { +% "<B>$user</B>"; +% } +% +% }; +% +% my $customer_format = sub { +% my( $unused, $session ) = @_; +% return ' ' unless exists $user2svc_acct{$session->{'username'}}; +% my $svc_acct = $user2svc_acct{$session->{'username'}}; +% my $cust_pkg = $svc_acct->cust_svc->cust_pkg; +% return ' ' unless $cust_pkg; +% my $cust_main = $cust_pkg->cust_main; +% +% qq!<A HREF="${p}view/cust_main.cgi?!. $cust_main->custnum. '">'. +% $cust_pkg->cust_main->name. '</A>'; +% }; +% +% my $time_format = sub { +% my $time = shift; +% return ' ' if $time == 0; +% my $pretty = time2str('%T%P %a %b %o %Y', $time ); +% $pretty =~ s/ (\d)(st|dn|rd|th)/$1$2/; +% $pretty; +% }; +% +% my $duration_format = sub { +% my $seconds = shift; +% my $hour = int($seconds/3600); +% my $min = int( ($seconds%3600) / 60 ); +% my $sec = $seconds%60; +% '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0>'. +% '<TR><TD ALIGN="right">'. +% ( $hour ? "<B>$hour</B>h" : ' ' ). +% '</TD><TD ALIGN="right">'. +% ( ( $hour || $min ) ? "<B>$min</B>m" : ' ' ). +% '</TD><TD ALIGN="right">'. +% "<B>$sec</B>s". +% '</TD></TR></TABLE>'; +% }; +% +% my $octets_format = sub { +% my $octets = shift; +% my $megs = $octets / 1048576; +% sprintf('<B>%.3f</B> megs', $megs); +% #my $gigs = $octets / 1073741824 +% #sprintf('<B>%.3f</B> gigabytes', $gigs); +% }; +% +% ### +% # the fields +% ### +% +% tie my %fields, 'Tie::IxHash', +% 'username' => { +% name => 'User', +% attrib => 'UserName', +% fmt => $user_format, +% align => 'left', +% }, +% 'realm' => { +% name => 'Realm', +% attrib => 'Realm', +% align => 'left', +% }, +% 'dummy' => { +% name => 'Customer', +% attrib => '', +% fmt => $customer_format, +% align => 'left', +% }, +% 'framedipaddress' => { +% name => 'IP Address', +% attrib => 'Framed-IP-Address', +% fmt => sub { my $ip = shift; +% length($ip) ? $ip : ' '; +% }, +% align => 'right', +% }, +% 'acctstarttime' => { +% name => 'Start time', +% attrib => 'Acct-Start-Time', +% fmt => $time_format, +% align => 'left', +% }, +% 'acctstoptime' => { +% name => 'End time', +% attrib => 'Acct-Stop-Time', +% fmt => $time_format, +% align => 'left', +% }, +% 'acctsessiontime' => { +% name => 'Duration', +% attrib => 'Acct-Session-Time', +% fmt => $duration_format, +% align => 'right', +% }, +% 'acctinputoctets' => { +% name => 'Upload', # (from user)', +% attrib => 'Acct-Input-Octets', +% fmt => $octets_format, +% align => 'right', +% }, +% 'acctoutputoctets' => { +% name => 'Download', # (to user)', +% attrib => 'Acct-Output-Octets', +% fmt => $octets_format, +% align => 'right', +% }, +% ; +% $fields{$_}->{fmt} ||= sub { length($_[0]) ? shift : ' '; } +% foreach keys %fields; +% +% ### +% # and finally, display the thing +% ### +% +% foreach my $part_export ( +% #grep $_->can('usage_sessions'), qsearch( 'part_export' ) +% qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ), +% qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } ) +% ) { +% %user2svc_acct = (); +% +% my $efields = tie my %efields, 'Tie::IxHash', %fields; +% delete $efields{'framedipaddress'} if $part_export->option('hide_ip'); +% if ( $part_export->option('hide_data') ) { +% delete $efields{$_} foreach qw(acctinputoctets acctoutputoctets); +% } +% if ( $part_export->option('show_called_station') ) { +% $efields->Splice(1, 0, +% 'calledstationid' => { +% 'name' => 'Destination', +% 'attrib' => 'Called-Station-ID', +% 'fmt' => +% sub { length($_[0]) ? shift : ' '; }, +% 'align' => 'left', +% }, +% ); +% } +% +% -<% - ### - # parse cgi params - ### - #sort of false laziness w/cust_pay.cgi - my $beginning = ''; - my $ending = ''; - if ( $cgi->param('beginning') - && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { - $beginning = str2time($1); - } - if ( $cgi->param('ending') - && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { - $ending = str2time($1) + 86399; - } - if ( $cgi->param('begin') && $cgi->param('begin') =~ /^(\d+)$/ ) { - $beginning = $1; - } - if ( $cgi->param('end') && $cgi->param('end') =~ /^(\d+)$/ ) { - $ending = $1; - } - - my $cgi_svc_acct = ''; - if ( $cgi->param('svcnum') =~ /^(\d+)$/ ) { - $cgi_svc_acct = qsearchs( 'svc_acct', { 'svcnum' => $1 } ); - } elsif ( $cgi->param('username') =~ /^([^@]+)\@([^@]+)$/ ) { - my %search = { 'username' => $1 }; - my $svc_domain = qsearchs('svc_domain', { 'domain' => $2 } ); - if ( $svc_domain ) { - $search{'domsvc'} = $svc_domain->svcnum; - } else { - delete $search{'username'}; - } - $cgi_svc_acct = qsearchs( 'svc_acct', \%search ) - if keys %search; - } elsif ( $cgi->param('username') =~ /^(.+)$/ ) { - $cgi_svc_acct = qsearchs( 'svc_acct', { 'username' => $1 } ); - } - - my $ip = ''; - if ( $cgi->param('ip') =~ /^((\d+\.){3}\d+)$/ ) { - $ip = $1; - } - - my $prefix = $cgi->param('prefix'); - $prefix =~ s/\D//g; - if ( $prefix =~ /^(\d+)$/ ) { - $prefix = $1; - $prefix = "011$prefix" unless $prefix =~ /^1/; - } else { - $prefix = ''; - } - - ### - # field formatting subroutines - ### - - my %user2svc_acct = (); - my $user_format = sub { - my ( $user, $session, $part_export ) = @_; - - my $svc_acct = ''; - if ( exists $user2svc_acct{$user} ) { - $svc_acct = $user2svc_acct{$user}; - } else { - my %search = (); - if ( $part_export->exporttype eq 'sqlradius_withdomain' ) { - my $domain; - if ( $user =~ /^([^@]+)\@([^@]+)$/ ) { - $search{'username'} = $1; - $domain = $2; - } else { - $search{'username'} = $user; - $domain = $session->{'realm'}; - } - my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ); - if ( $svc_domain ) { - $search{'domsvc'} = $svc_domain->svcnum; - } else { - delete $search{'username'}; - } - } elsif ( $part_export->exporttype eq 'sqlradius' ) { - $search{'username'} = $user; - } else { - die 'unknown export type '. $part_export->exporttype. - " for $part_export\n"; - } - if ( keys %search ) { - my @svc_acct = - grep { qsearchs( 'export_svc', { - 'exportnum' => $part_export->exportnum, - 'svcpart' => $_->cust_svc->svcpart, - } ) - } qsearch( 'svc_acct', \%search ); - if ( @svc_acct ) { - warn 'multiple svc_acct records for user $user found; '. - 'using first arbitrarily' - if scalar(@svc_acct) > 1; - $user2svc_acct{$user} = $svc_acct = shift @svc_acct; - } - } - } - - if ( $svc_acct ) { - my $svcnum = $svc_acct->svcnum; - qq(<A HREF="${p}view/svc_acct.cgi?$svcnum"><B>$user</B></A>); - } else { - "<B>$user</B>"; - } - - }; - - my $customer_format = sub { - my( $unused, $session ) = @_; - return ' ' unless exists $user2svc_acct{$session->{'username'}}; - my $svc_acct = $user2svc_acct{$session->{'username'}}; - my $cust_pkg = $svc_acct->cust_svc->cust_pkg; - return ' ' unless $cust_pkg; - my $cust_main = $cust_pkg->cust_main; - - qq!<A HREF="${p}view/cust_main.cgi?!. $cust_main->custnum. '">'. - $cust_pkg->cust_main->name. '</A>'; - }; - - my $time_format = sub { - my $time = shift; - return ' ' if $time == 0; - my $pretty = time2str('%T%P %a %b %o %Y', $time ); - $pretty =~ s/ (\d)(st|dn|rd|th)/$1$2/; - $pretty; - }; - - my $duration_format = sub { - my $seconds = shift; - my $hour = int($seconds/3600); - my $min = int( ($seconds%3600) / 60 ); - my $sec = $seconds%60; - '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0>'. - '<TR><TD ALIGN="right">'. - ( $hour ? "<B>$hour</B>h" : ' ' ). - '</TD><TD ALIGN="right">'. - ( ( $hour || $min ) ? "<B>$min</B>m" : ' ' ). - '</TD><TD ALIGN="right">'. - "<B>$sec</B>s". - '</TD></TR></TABLE>'; - }; - - my $octets_format = sub { - my $octets = shift; - my $megs = $octets / 1048576; - sprintf('<B>%.3f</B> megs', $megs); - #my $gigs = $octets / 1073741824 - #sprintf('<B>%.3f</B> gigabytes', $gigs); - }; - - ### - # the fields - ### - - tie my %fields, 'Tie::IxHash', - 'username' => { - name => 'User', - attrib => 'UserName', - fmt => $user_format, - align => 'left', - }, - 'realm' => { - name => 'Realm', - attrib => 'Realm', - align => 'left', - }, - 'dummy' => { - name => 'Customer', - attrib => '', - fmt => $customer_format, - align => 'left', - }, - 'framedipaddress' => { - name => 'IP Address', - attrib => 'Framed-IP-Address', - fmt => sub { my $ip = shift; - length($ip) ? $ip : ' '; - }, - align => 'right', - }, - 'acctstarttime' => { - name => 'Start time', - attrib => 'Acct-Start-Time', - fmt => $time_format, - align => 'left', - }, - 'acctstoptime' => { - name => 'End time', - attrib => 'Acct-Stop-Time', - fmt => $time_format, - align => 'left', - }, - 'acctsessiontime' => { - name => 'Duration', - attrib => 'Acct-Session-Time', - fmt => $duration_format, - align => 'right', - }, - 'acctinputoctets' => { - name => 'Upload', # (from user)', - attrib => 'Acct-Input-Octets', - fmt => $octets_format, - align => 'right', - }, - 'acctoutputoctets' => { - name => 'Download', # (to user)', - attrib => 'Acct-Output-Octets', - fmt => $octets_format, - align => 'right', - }, - ; - $fields{$_}->{fmt} ||= sub { length($_[0]) ? shift : ' '; } - foreach keys %fields; - - ### - # and finally, display the thing - ### - - foreach my $part_export ( - #grep $_->can('usage_sessions'), qsearch( 'part_export' ) - qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ), - qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } ) - ) { - %user2svc_acct = (); - - my $efields = tie my %efields, 'Tie::IxHash', %fields; - delete $efields{'framedipaddress'} if $part_export->option('hide_ip'); - if ( $part_export->option('hide_data') ) { - delete $efields{$_} foreach qw(acctinputoctets acctoutputoctets); - } - if ( $part_export->option('show_called_station') ) { - $efields->Splice(1, 0, - 'calledstationid' => { - 'name' => 'Destination', - 'attrib' => 'Called-Station-ID', - 'fmt' => - sub { length($_[0]) ? shift : ' '; }, - 'align' => 'left', - }, - ); - } - -%> - -<%= $part_export->exporttype %> to <%= $part_export->machine %><BR> -<%= include( '/elements/table.html' ) %> +<% $part_export->exporttype %> to <% $part_export->machine %><BR> +<% include( '/elements/table.html' ) %> <TR> - <% foreach my $field ( keys %efields ) { %> +% foreach my $field ( keys %efields ) { + <TH> - <%= $efields{$field}->{name} %><BR> - <FONT SIZE=-2><%= $efields{$field}->{attrib} %></FONT> + <% $efields{$field}->{name} %><BR> + <FONT SIZE=-2><% $efields{$field}->{attrib} %></FONT> </TH> - <% } %> +% } + </TR> -<% foreach my $session ( - @{ $part_export->usage_sessions( - $beginning, $ending, $cgi_svc_acct, $ip, $prefix, ) } - ) { -%> +% foreach my $session ( +% @{ $part_export->usage_sessions( +% $beginning, $ending, $cgi_svc_acct, $ip, $prefix, ) } +% ) { +% + <TR> - <% foreach my $field ( keys %efields ) { %> - <TD ALIGN="<%= $efields{$field}->{align} %>"> - <%= &{ $efields{$field}->{fmt} }( $session->{$field}, +% foreach my $field ( keys %efields ) { + + <TD ALIGN="<% $efields{$field}->{align} %>"> + <% &{ $efields{$field}->{fmt} }( $session->{$field}, $session, $part_export, ) %> </TD> - <% } %> +% } + </TR> -<% } %> +% } + </TABLE> <BR><BR> +% } -<% } %> diff --git a/httemplate/search/sqlradius.html b/httemplate/search/sqlradius.html index 645505101..c0791f1ed 100644 --- a/httemplate/search/sqlradius.html +++ b/httemplate/search/sqlradius.html @@ -1,8 +1,9 @@ -<%= include( '/elements/header.html', 'Search RADIUS sessions' ) %> +<% include( '/elements/header.html', 'Search RADIUS sessions' ) %> <FORM NAME="OneTrueForm" ACTION="sqlradius.cgi" METHOD="GET"> -<% #include( '/elements/table.html' ) %> -<%= ntable('#cccccc') %> +% #include( '/elements/table.html' ) + +<% ntable('#cccccc') %> <TR> <TD ALIGN="right">Username: </TD> <TD><INPUT TYPE="text" NAME="username"></TD> @@ -11,13 +12,12 @@ <TD></TD> <TD><FONT SIZE="-1"><I>(leave blank to show all users)</I></FONT></TD> </TR> +% my @part_export = qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ); +% push @part_export, +% qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } ); +% +% if ( grep { ! $_->option('hide_ip') } @part_export ) { -<% my @part_export = qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ); - push @part_export, - qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } ); -%> - -<% if ( grep { ! $_->option('hide_ip') } @part_export ) { %> <TR> <TD ALIGN="right">IP address: </TD> <TD><INPUT TYPE="text" NAME="ip"></TD> @@ -26,9 +26,9 @@ <TD></TD> <TD><FONT SIZE="-1"><I>(leave blank to show all IPs)</I></FONT></TD> </TR> -<% } %> +% } +% if ( grep { $_->option('show_called_station') } @part_export ) { -<% if ( grep { $_->option('show_called_station') } @part_export ) { %> <TR> <TD ALIGN="right">Destination prefix:</TD> <TD><INPUT TYPE="text" NAME="prefix"></TD> @@ -41,12 +41,13 @@ <TD></TD> <TD><FONT SIZE="-1"><I>(leave blank to show all destinations)</I></FONT></TD> </TR> -<% } %> +% } + -<%= include( '/elements/tr-input-beginning_ending.html' ) %> +<% include( '/elements/tr-input-beginning_ending.html' ) %> </TABLE> <BR><INPUT TYPE="submit" VALUE="View sessions"> </FORM> -<%= include('/elements/footer.html') %> +<% include('/elements/footer.html') %> diff --git a/httemplate/search/svc_Smart.html b/httemplate/search/svc_Smart.html index 93dbbeadd..4d8f3e20e 100644 --- a/httemplate/search/svc_Smart.html +++ b/httemplate/search/svc_Smart.html @@ -1,22 +1,29 @@ -<% +% +% +%if ( $cgi->param('search_svc') =~ /\.[a-z]{2,8}$/i ) { +% +% # looks (enough) like a domain +% +% +<% $cgi->redirect('svc_domain.cgi?domain='. uri_escape( $cgi->param('search_svc') ) ) %> +% +% +%} elsif ( $cgi->param('search_svc') =~ /\w/ ) { +% +% #looks (enough) like a username +% +% +<% $cgi->redirect('svc_acct.cgi?username_type=Exact;username='. uri_escape( $cgi->param('search_svc') ) ) %> +% +% +%} else { +% +% +<% include('/elements/header.html', 'Unrecognized service string') %> + <% include('/elements/footer.html') %> +% +% +%} +% +% -if ( $cgi->param('search_svc') =~ /\.[a-z]{2,8}$/i ) { - - # looks (enough) like a domain - - %><%= $cgi->redirect('svc_domain.cgi?domain='. uri_escape( $cgi->param('search_svc') ) ) %><% - -} elsif ( $cgi->param('search_svc') =~ /\w/ ) { - - #looks (enough) like a username - - %><%= $cgi->redirect('svc_acct.cgi?username_type=Exact;username='. uri_escape( $cgi->param('search_svc') ) ) %><% - -} else { - -%><%= include('/elements/header.html', 'Unrecognized service string') %> - <%= include('/elements/footer.html') %><% - -} - -%> diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index 0f2f3ef45..d293e2455 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -1,119 +1,120 @@ -<% - -my $orderby = 'ORDER BY svcnum'; - -my($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors - -my @extra_sql = (); -if ( $query =~ /^UN_(.*)$/ ) { - $query = $1; - push @extra_sql, 'pkgnum IS NULL'; -} - -if ( $query eq 'svcnum' ) { - #$orderby = "ORDER BY svcnum"; -} elsif ( $query eq 'username' ) { - $orderby = "ORDER BY LOWER(username)"; -} elsif ( $query eq 'uid' ) { - $orderby = "ORDER BY uid"; - push @extra_sql, "uid IS NOT NULL"; -} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) { - push @extra_sql, "popnum = $1"; - $orderby = "ORDER BY LOWER(username)"; -} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { - push @extra_sql, "svcpart = $1"; - $orderby = "ORDER BY uid"; - #$orderby = "ORDER BY svcnum"; -} else { - $orderby = "ORDER BY uid"; - - my @username_sql; - - my %username_type; - foreach ( $cgi->param('username_type') ) { - $username_type{$_}++; - } - - $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text - my $username = $1; - - push @username_sql, "username ILIKE '$username'" - if $username_type{'Exact'} - || $username_type{'Fuzzy'}; - - push @username_sql, "username ILIKE '\%$username\%'" - if $username_type{'Substring'} - || $username_type{'All'}; - - if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { - &FS::svc_acct::check_and_rebuild_fuzzyfiles; - my $all_username = &FS::svc_acct::all_username; - - my %username; - if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { - foreach ( amatch($username, [ qw(i) ], @$all_username) ) { - $username{$_}++; - } - } - - #if ($username_type{'Sound-alike'}) { - #} - - push @username_sql, "username = '$_'" - foreach (keys %username); - - } - - push @extra_sql, '( '. join( ' OR ', @username_sql). ' )'; - -} - -my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) '; - -#here is the agent virtualization -push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; - -my $extra_sql = - scalar(@extra_sql) - ? ' WHERE '. join(' AND ', @extra_sql ) - : ''; - -my $count_query = "SELECT COUNT(*) FROM svc_acct $addl_from $extra_sql"; -#if ( keys %svc_acct ) { -# $count_query .= ' WHERE '. -# join(' AND ', map "$_ = ". dbh->quote($svc_acct{$_}), -# keys %svc_acct -# ); -#} - -my $sql_query = { - 'table' => 'svc_acct', - 'hashref' => {}, # \%svc_acct, - 'select' => join(', ', - 'svc_acct.*', - 'part_svc.svc', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => $addl_from, -}; - -my $link = [ "${p}view/svc_acct.cgi?", 'svcnum' ]; -my $link_cust = sub { - my $svc_acct = shift; - if ( $svc_acct->custnum ) { - [ "${p}view/cust_main.cgi?", 'custnum' ]; - } else { - ''; - } -}; - -%><%= include( 'elements/search.html', +% +% +%my $orderby = 'ORDER BY svcnum'; +% +%my($query)=$cgi->keywords; +%$query ||= ''; #to avoid use of unitialized value errors +% +%my @extra_sql = (); +%if ( $query =~ /^UN_(.*)$/ ) { +% $query = $1; +% push @extra_sql, 'pkgnum IS NULL'; +%} +% +%if ( $query eq 'svcnum' ) { +% #$orderby = "ORDER BY svcnum"; +%} elsif ( $query eq 'username' ) { +% $orderby = "ORDER BY LOWER(username)"; +%} elsif ( $query eq 'uid' ) { +% $orderby = "ORDER BY uid"; +% push @extra_sql, "uid IS NOT NULL"; +%} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) { +% push @extra_sql, "popnum = $1"; +% $orderby = "ORDER BY LOWER(username)"; +%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { +% push @extra_sql, "svcpart = $1"; +% $orderby = "ORDER BY uid"; +% #$orderby = "ORDER BY svcnum"; +%} else { +% $orderby = "ORDER BY uid"; +% +% my @username_sql; +% +% my %username_type; +% foreach ( $cgi->param('username_type') ) { +% $username_type{$_}++; +% } +% +% $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text +% my $username = $1; +% +% push @username_sql, "username ILIKE '$username'" +% if $username_type{'Exact'} +% || $username_type{'Fuzzy'}; +% +% push @username_sql, "username ILIKE '\%$username\%'" +% if $username_type{'Substring'} +% || $username_type{'All'}; +% +% if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { +% &FS::svc_acct::check_and_rebuild_fuzzyfiles; +% my $all_username = &FS::svc_acct::all_username; +% +% my %username; +% if ( $username_type{'Fuzzy'} || $username_type{'All'} ) { +% foreach ( amatch($username, [ qw(i) ], @$all_username) ) { +% $username{$_}++; +% } +% } +% +% #if ($username_type{'Sound-alike'}) { +% #} +% +% push @username_sql, "username = '$_'" +% foreach (keys %username); +% +% } +% +% push @extra_sql, '( '. join( ' OR ', @username_sql). ' )'; +% +%} +% +%my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. +% ' LEFT JOIN part_svc USING ( svcpart ) '. +% ' LEFT JOIN cust_pkg USING ( pkgnum ) '. +% ' LEFT JOIN cust_main USING ( custnum ) '; +% +%#here is the agent virtualization +%push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; +% +%my $extra_sql = +% scalar(@extra_sql) +% ? ' WHERE '. join(' AND ', @extra_sql ) +% : ''; +% +%my $count_query = "SELECT COUNT(*) FROM svc_acct $addl_from $extra_sql"; +%#if ( keys %svc_acct ) { +%# $count_query .= ' WHERE '. +%# join(' AND ', map "$_ = ". dbh->quote($svc_acct{$_}), +%# keys %svc_acct +%# ); +%#} +% +%my $sql_query = { +% 'table' => 'svc_acct', +% 'hashref' => {}, # \%svc_acct, +% 'select' => join(', ', +% 'svc_acct.*', +% 'part_svc.svc', +% 'cust_main.custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'extra_sql' => "$extra_sql $orderby", +% 'addl_from' => $addl_from, +%}; +% +%my $link = [ "${p}view/svc_acct.cgi?", 'svcnum' ]; +%my $link_cust = sub { +% my $svc_acct = shift; +% if ( $svc_acct->custnum ) { +% [ "${p}view/cust_main.cgi?", 'custnum' ]; +% } else { +% ''; +% } +%}; +% +% +<% include( 'elements/search.html', 'title' => 'Account Search Results', 'name' => 'accounts', 'query' => $sql_query, diff --git a/httemplate/search/svc_broadband.cgi b/httemplate/search/svc_broadband.cgi index efadce600..ae32ccd7e 100755 --- a/httemplate/search/svc_broadband.cgi +++ b/httemplate/search/svc_broadband.cgi @@ -1,96 +1,99 @@ -<% +% +% +%my $conf = new FS::Conf; +% +%my($query)=$cgi->keywords; +%$query ||= ''; #to avoid use of unitialized value errors +%my(@svc_broadband,$sortby); +%if ( $query eq 'svcnum' ) { +% $sortby=\*svcnum_sort; +% @svc_broadband=qsearch('svc_broadband',{}); +%} elsif ( $query eq 'blocknum' ) { +% $sortby=\*blocknum_sort; +% @svc_broadband=qsearch('svc_broadband',{}); +%} else { +% $cgi->param('ip_addr') =~ /^(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})$/; +% my($ip_addr)=$1; +% @svc_broadband = qsearchs('svc_broadband',{'ip_addr'=>$ip_addr}); +%} +% +%my %routerbyblock = (); +%foreach my $router (qsearch('router', {})) { +% foreach ($router->addr_block) { +% $routerbyblock{$_->blocknum} = $router; +% } +%} +% +%if ( scalar(@svc_broadband) == 1 ) { +% print $cgi->redirect(popurl(2). "view/svc_broadband.cgi?". $svc_broadband[0]->svcnum); +% #exit; +%} elsif ( scalar(@svc_broadband) == 0 ) { +% -my $conf = new FS::Conf; - -my($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors -my(@svc_broadband,$sortby); -if ( $query eq 'svcnum' ) { - $sortby=\*svcnum_sort; - @svc_broadband=qsearch('svc_broadband',{}); -} elsif ( $query eq 'blocknum' ) { - $sortby=\*blocknum_sort; - @svc_broadband=qsearch('svc_broadband',{}); -} else { - $cgi->param('ip_addr') =~ /^(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})$/; - my($ip_addr)=$1; - @svc_broadband = qsearchs('svc_broadband',{'ip_addr'=>$ip_addr}); -} - -my %routerbyblock = (); -foreach my $router (qsearch('router', {})) { - foreach ($router->addr_block) { - $routerbyblock{$_->blocknum} = $router; - } -} - -if ( scalar(@svc_broadband) == 1 ) { - print $cgi->redirect(popurl(2). "view/svc_broadband.cgi?". $svc_broadband[0]->svcnum); - #exit; -} elsif ( scalar(@svc_broadband) == 0 ) { -%> -<!-- mason kludge --> -<% - eidiot "No matching ip address found!\n"; -} else { -%> <!-- mason kludge --> -<% - my($total)=scalar(@svc_broadband); - print header("IP Address Search Results",''), <<END; - - $total matching broadband services found - <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> - <TR> - <TH>Service #</TH> - <TH>Router</TH> - <TH>IP Address</TH> - </TR> -END - - foreach my $svc_broadband ( - sort $sortby (@svc_broadband) - ) { - my($svcnum,$ip_addr,$routername,$routernum)=( - $svc_broadband->svcnum, - $svc_broadband->ip_addr, - $routerbyblock{$svc_broadband->blocknum}->routername, - $routerbyblock{$svc_broadband->blocknum}->routernum, - ); - - my $rowspan = 1; - - print <<END; - <TR> - <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_broadband.cgi?$svcnum">$svcnum</A></TD> - <TD ROWSPAN=$rowspan><A HREF="${p}view/router.cgi?$routernum">$routername</A></TD> - <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_broadband.cgi?$svcnum">$ip_addr</A></TD> -END - - #print @rows; - print "</TR>"; - - } - - print <<END; - </TABLE> - </BODY> -</HTML> -END - -} - -sub svcnum_sort { - $a->getfield('svcnum') <=> $b->getfield('svcnum'); -} - -sub blocknum_sort { - if ($a->getfield('blocknum') == $b->getfield('blocknum')) { - $a->getfield('ip_addr') cmp $b->getfield('ip_addr'); - } else { - $a->getfield('blocknum') cmp $b->getfield('blocknum'); - } -} +% +% eidiot "No matching ip address found!\n"; +%} else { +% +<!-- mason kludge --> +% +% my($total)=scalar(@svc_broadband); +% print header("IP Address Search Results",''), <<END; +% +% $total matching broadband services found +% <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> +% <TR> +% <TH>Service #</TH> +% <TH>Router</TH> +% <TH>IP Address</TH> +% </TR> +%END +% +% foreach my $svc_broadband ( +% sort $sortby (@svc_broadband) +% ) { +% my($svcnum,$ip_addr,$routername,$routernum)=( +% $svc_broadband->svcnum, +% $svc_broadband->ip_addr, +% $routerbyblock{$svc_broadband->blocknum}->routername, +% $routerbyblock{$svc_broadband->blocknum}->routernum, +% ); +% +% my $rowspan = 1; +% +% print <<END; +% <TR> +% <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_broadband.cgi?$svcnum">$svcnum</A></TD> +% <TD ROWSPAN=$rowspan><A HREF="${p}view/router.cgi?$routernum">$routername</A></TD> +% <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_broadband.cgi?$svcnum">$ip_addr</A></TD> +%END +% +% #print @rows; +% print "</TR>"; +% +% } +% +% print <<END; +% </TABLE> +% </BODY> +%</HTML> +%END +% +%} +% +%sub svcnum_sort { +% $a->getfield('svcnum') <=> $b->getfield('svcnum'); +%} +% +%sub blocknum_sort { +% if ($a->getfield('blocknum') == $b->getfield('blocknum')) { +% $a->getfield('ip_addr') cmp $b->getfield('ip_addr'); +% } else { +% $a->getfield('blocknum') cmp $b->getfield('blocknum'); +% } +%} +% +% +% -%> diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi index ecb77792f..85ae94a80 100755 --- a/httemplate/search/svc_domain.cgi +++ b/httemplate/search/svc_domain.cgi @@ -1,78 +1,79 @@ -<% - -my $conf = new FS::Conf; - -my($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors - -my $orderby = 'ORDER BY svcnum'; -my %svc_domain = (); -my @extra_sql = (); -if ( $query eq 'svcnum' ) { - #$orderby = 'ORDER BY svcnum'; -} elsif ( $query eq 'domain' ) { - $orderby = 'ORDER BY domain'; -} elsif ( $query eq 'UN_svcnum' ) { #UN searches need to be acl'ed (and need to - #fix $agentnums_sql - #$orderby = 'ORDER BY svcnum'; - push @extra_sql, 'pkgnum IS NULL'; -} elsif ( $query eq 'UN_domain' ) { #UN searches need to be acl'ed (and need to - #fix $agentnums_sql - $orderby = 'ORDER BY domain'; - push @extra_sql, 'pkgnum IS NULL'; -} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { - #$orderby = 'ORDER BY svcnum'; - push @extra_sql, "svcpart = $1"; -} else { - $cgi->param('domain') =~ /^([\w\-\.]+)$/; - $svc_domain{'domain'} = $1; -} - -my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) '; - -#here is the agent virtualization -push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; - -my $extra_sql = ''; -if ( @extra_sql ) { - $extra_sql = ( keys(%svc_domain) ? ' AND ' : ' WHERE ' ). - join(' AND ', @extra_sql ); -} - -my $count_query = "SELECT COUNT(*) FROM svc_domain $addl_from "; -if ( keys %svc_domain ) { - $count_query .= ' WHERE '. - join(' AND ', map "$_ = ". dbh->quote($svc_domain{$_}), - keys %svc_domain - ); -} -$count_query .= $extra_sql; - -my $sql_query = { - 'table' => 'svc_domain', - 'hashref' => \%svc_domain, - 'select' => join(', ', - 'svc_domain.*', - 'part_svc.svc', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => $addl_from, -}; - -my $link = [ "${p}view/svc_domain.cgi?", 'svcnum' ]; - -#smaller false laziness w/svc_*.cgi here -my $link_cust = sub { - my $svc_x = shift; - $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; -}; - -%><%= include( 'elements/search.html', +% +% +%my $conf = new FS::Conf; +% +%my($query)=$cgi->keywords; +%$query ||= ''; #to avoid use of unitialized value errors +% +%my $orderby = 'ORDER BY svcnum'; +%my %svc_domain = (); +%my @extra_sql = (); +%if ( $query eq 'svcnum' ) { +% #$orderby = 'ORDER BY svcnum'; +%} elsif ( $query eq 'domain' ) { +% $orderby = 'ORDER BY domain'; +%} elsif ( $query eq 'UN_svcnum' ) { #UN searches need to be acl'ed (and need to +% #fix $agentnums_sql +% #$orderby = 'ORDER BY svcnum'; +% push @extra_sql, 'pkgnum IS NULL'; +%} elsif ( $query eq 'UN_domain' ) { #UN searches need to be acl'ed (and need to +% #fix $agentnums_sql +% $orderby = 'ORDER BY domain'; +% push @extra_sql, 'pkgnum IS NULL'; +%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { +% #$orderby = 'ORDER BY svcnum'; +% push @extra_sql, "svcpart = $1"; +%} else { +% $cgi->param('domain') =~ /^([\w\-\.]+)$/; +% $svc_domain{'domain'} = $1; +%} +% +%my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. +% ' LEFT JOIN part_svc USING ( svcpart ) '. +% ' LEFT JOIN cust_pkg USING ( pkgnum ) '. +% ' LEFT JOIN cust_main USING ( custnum ) '; +% +%#here is the agent virtualization +%push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; +% +%my $extra_sql = ''; +%if ( @extra_sql ) { +% $extra_sql = ( keys(%svc_domain) ? ' AND ' : ' WHERE ' ). +% join(' AND ', @extra_sql ); +%} +% +%my $count_query = "SELECT COUNT(*) FROM svc_domain $addl_from "; +%if ( keys %svc_domain ) { +% $count_query .= ' WHERE '. +% join(' AND ', map "$_ = ". dbh->quote($svc_domain{$_}), +% keys %svc_domain +% ); +%} +%$count_query .= $extra_sql; +% +%my $sql_query = { +% 'table' => 'svc_domain', +% 'hashref' => \%svc_domain, +% 'select' => join(', ', +% 'svc_domain.*', +% 'part_svc.svc', +% 'cust_main.custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'extra_sql' => "$extra_sql $orderby", +% 'addl_from' => $addl_from, +%}; +% +%my $link = [ "${p}view/svc_domain.cgi?", 'svcnum' ]; +% +%#smaller false laziness w/svc_*.cgi here +%my $link_cust = sub { +% my $svc_x = shift; +% $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +%}; +% +% +<% include( 'elements/search.html', 'title' => "Domain Search Results", 'name' => 'domains', 'query' => $sql_query, diff --git a/httemplate/search/svc_external.cgi b/httemplate/search/svc_external.cgi index 7968f3c43..e85d6d7b3 100755 --- a/httemplate/search/svc_external.cgi +++ b/httemplate/search/svc_external.cgi @@ -1,102 +1,105 @@ -<% - -my $conf = new FS::Conf; - -my($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors -my(@svc_external,$sortby); -if ( $query eq 'svcnum' ) { - $sortby=\*svcnum_sort; - @svc_external=qsearch('svc_external',{}); -} elsif ( $query eq 'id' ) { - $sortby=\*id_sort; - @svc_external=qsearch('svc_external',{}); -} elsif ( $query eq 'UN_svcnum' ) { - $sortby=\*svcnum_sort; - @svc_external = grep qsearchs('cust_svc',{ - 'svcnum' => $_->svcnum, - 'pkgnum' => '', - }), qsearch('svc_external',{}); -} elsif ( $query eq 'UN_id' ) { - $sortby=\*id_sort; - @svc_external = grep qsearchs('cust_svc',{ - 'svcnum' => $_->svcnum, - 'pkgnum' => '', - }), qsearch('svc_external',{}); -} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { - @svc_external = - qsearch( 'svc_external', {}, '', - " WHERE $1 = ( SELECT svcpart FROM cust_svc ". - " WHERE cust_svc.svcnum = svc_external.svcnum ) " - ); - $sortby=\*svcnum_sort; -} else { - $cgi->param('id') =~ /^([\w\-\.]+)$/; - my($id)=$1; - #push @svc_domain, qsearchs('svc_domain',{'domain'=>$domain}); - @svc_external = qsearchs('svc_external',{'id'=>$id}); -} - -if ( scalar(@svc_external) == 1 ) { - - %><%= $cgi->redirect(popurl(2). "view/svc_external.cgi?". $svc_external[0]->svcnum) %><% - -} elsif ( scalar(@svc_external) == 0 ) { - - %><%= include('/elements/header.html', 'External Search Results' ) %> +% +% +%my $conf = new FS::Conf; +% +%my($query)=$cgi->keywords; +%$query ||= ''; #to avoid use of unitialized value errors +%my(@svc_external,$sortby); +%if ( $query eq 'svcnum' ) { +% $sortby=\*svcnum_sort; +% @svc_external=qsearch('svc_external',{}); +%} elsif ( $query eq 'id' ) { +% $sortby=\*id_sort; +% @svc_external=qsearch('svc_external',{}); +%} elsif ( $query eq 'UN_svcnum' ) { +% $sortby=\*svcnum_sort; +% @svc_external = grep qsearchs('cust_svc',{ +% 'svcnum' => $_->svcnum, +% 'pkgnum' => '', +% }), qsearch('svc_external',{}); +%} elsif ( $query eq 'UN_id' ) { +% $sortby=\*id_sort; +% @svc_external = grep qsearchs('cust_svc',{ +% 'svcnum' => $_->svcnum, +% 'pkgnum' => '', +% }), qsearch('svc_external',{}); +%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { +% @svc_external = +% qsearch( 'svc_external', {}, '', +% " WHERE $1 = ( SELECT svcpart FROM cust_svc ". +% " WHERE cust_svc.svcnum = svc_external.svcnum ) " +% ); +% $sortby=\*svcnum_sort; +%} else { +% $cgi->param('id') =~ /^([\w\-\.]+)$/; +% my($id)=$1; +% #push @svc_domain, qsearchs('svc_domain',{'domain'=>$domain}); +% @svc_external = qsearchs('svc_external',{'id'=>$id}); +%} +% +%if ( scalar(@svc_external) == 1 ) { +% +% +<% $cgi->redirect(popurl(2). "view/svc_external.cgi?". $svc_external[0]->svcnum) %> +% +% +%} elsif ( scalar(@svc_external) == 0 ) { +% +% +<% include('/elements/header.html', 'External Search Results' ) %> No matching external services found +% } else { +% +% +<% include('/elements/header.html', 'External Search Results', '') %> -<% } else { - - %><%= include('/elements/header.html', 'External Search Results', '') %> - - <%= scalar(@svc_external) %> matching external services found + <% scalar(@svc_external) %> matching external services found <TABLE BORDER=4 CELLSPACING=0 CELLPADDING=0> <TR> <TH>Service #</TH> - <TH><%= FS::Msgcat::_gettext('svc_external-id') || 'External ID' %></TH> - <TH><%= FS::Msgcat::_gettext('svc_external-title') || 'Title' %></TH> + <TH><% FS::Msgcat::_gettext('svc_external-id') || 'External ID' %></TH> + <TH><% FS::Msgcat::_gettext('svc_external-title') || 'Title' %></TH> </TR> +% +% foreach my $svc_external ( +% sort $sortby (@svc_external) +% ) { +% my($svcnum, $id, $title)=( +% $svc_external->svcnum, +% $svc_external->id, +% $svc_external->title, +% ); +% +% my $rowspan = 1; +% +% print <<END; +% <TR> +% <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$svcnum</A></TD> +% <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$id</A></TD> +% <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$title</A></TD> +%END +% +% #print @rows; +% print "</TR>"; +% +% } +% +% print <<END; +% </TABLE> +% </BODY> +%</HTML> +%END +% +%} +% +%sub svcnum_sort { +% $a->getfield('svcnum') <=> $b->getfield('svcnum'); +%} +% +%sub id_sort { +% $a->getfield('id') <=> $b->getfield('id'); +%} +% +% -<% - foreach my $svc_external ( - sort $sortby (@svc_external) - ) { - my($svcnum, $id, $title)=( - $svc_external->svcnum, - $svc_external->id, - $svc_external->title, - ); - - my $rowspan = 1; - - print <<END; - <TR> - <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$svcnum</A></TD> - <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$id</A></TD> - <TD ROWSPAN=$rowspan><A HREF="${p}view/svc_external.cgi?$svcnum">$title</A></TD> -END - - #print @rows; - print "</TR>"; - - } - - print <<END; - </TABLE> - </BODY> -</HTML> -END - -} - -sub svcnum_sort { - $a->getfield('svcnum') <=> $b->getfield('svcnum'); -} - -sub id_sort { - $a->getfield('id') <=> $b->getfield('id'); -} - -%> diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi index d391a1834..dc002d96a 100755 --- a/httemplate/search/svc_forward.cgi +++ b/httemplate/search/svc_forward.cgi @@ -1,103 +1,104 @@ -<% - -my $conf = new FS::Conf; - -my($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors - -my $orderby; - -my @extra_sql = (); -if ( $query =~ /^UN_(.*)$/ ) { #UN searches need to be acl'ed (and need to - #fix $agentnums_sql - $query = $1; - push @extra_sql, 'pkgnum IS NULL'; -} - -if ( $query eq 'svcnum' ) { - $orderby = 'ORDER BY svcnum'; -} else { - eidiot('unimplemented'); -} - -my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) '; - -#here is the agent virtualization -push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; - -my $extra_sql = - scalar(@extra_sql) - ? ' WHERE '. join(' AND ', @extra_sql ) - : ''; - -my $count_query = "SELECT COUNT(*) FROM svc_forward $addl_from $extra_sql"; -my $sql_query = { - 'table' => 'svc_forward', - 'hashref' => {}, - 'select' => join(', ', - 'svc_forward.*', - 'part_svc.svc', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => $addl_from, -}; - -# <TH>Service #<BR><FONT SIZE=-1>(click to view forward)</FONT></TH> -# <TH>Mail to<BR><FONT SIZE=-1>(click to view account)</FONT></TH> -# <TH>Forwards to<BR><FONT SIZE=-1>(click to view account)</FONT></TH> - -my $link = [ "${p}view/svc_forward.cgi?", 'svcnum' ]; - -my $format_src = sub { - my $svc_forward = shift; - if ( $svc_forward->srcsvc_acct ) { - $svc_forward->srcsvc_acct->email; - } else { - my $src = $svc_forward->src; - $src = "<I>(anything)</I>$src" if $src =~ /^@/; - $src; - } -}; - -my $link_src = sub { - my $svc_forward = shift; - if ( $svc_forward->srcsvc_acct ) { - [ "${p}view/svc_acct.cgi?", 'srcsvc' ]; - } else { - ''; - } -}; - -my $format_dst = sub { - my $svc_forward = shift; - if ( $svc_forward->dstsvc_acct ) { - $svc_forward->dstsvc_acct->email; - } else { - $svc_forward->dst; - } -}; - -my $link_dst = sub { - my $svc_forward = shift; - if ( $svc_forward->dstsvc_acct ) { - [ "${p}view/svc_acct.cgi?", 'dstsvc' ]; - } else { - ''; - } -}; - -#smaller false laziness w/svc_*.cgi here -my $link_cust = sub { - my $svc_x = shift; - $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; -}; - -%><%= include( 'elements/search.html', +% +% +%my $conf = new FS::Conf; +% +%my($query)=$cgi->keywords; +%$query ||= ''; #to avoid use of unitialized value errors +% +%my $orderby; +% +%my @extra_sql = (); +%if ( $query =~ /^UN_(.*)$/ ) { #UN searches need to be acl'ed (and need to +% #fix $agentnums_sql +% $query = $1; +% push @extra_sql, 'pkgnum IS NULL'; +%} +% +%if ( $query eq 'svcnum' ) { +% $orderby = 'ORDER BY svcnum'; +%} else { +% eidiot('unimplemented'); +%} +% +%my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. +% ' LEFT JOIN part_svc USING ( svcpart ) '. +% ' LEFT JOIN cust_pkg USING ( pkgnum ) '. +% ' LEFT JOIN cust_main USING ( custnum ) '; +% +%#here is the agent virtualization +%push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; +% +%my $extra_sql = +% scalar(@extra_sql) +% ? ' WHERE '. join(' AND ', @extra_sql ) +% : ''; +% +%my $count_query = "SELECT COUNT(*) FROM svc_forward $addl_from $extra_sql"; +%my $sql_query = { +% 'table' => 'svc_forward', +% 'hashref' => {}, +% 'select' => join(', ', +% 'svc_forward.*', +% 'part_svc.svc', +% 'cust_main.custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'extra_sql' => "$extra_sql $orderby", +% 'addl_from' => $addl_from, +%}; +% +%# <TH>Service #<BR><FONT SIZE=-1>(click to view forward)</FONT></TH> +%# <TH>Mail to<BR><FONT SIZE=-1>(click to view account)</FONT></TH> +%# <TH>Forwards to<BR><FONT SIZE=-1>(click to view account)</FONT></TH> +% +%my $link = [ "${p}view/svc_forward.cgi?", 'svcnum' ]; +% +%my $format_src = sub { +% my $svc_forward = shift; +% if ( $svc_forward->srcsvc_acct ) { +% $svc_forward->srcsvc_acct->email; +% } else { +% my $src = $svc_forward->src; +% $src = "<I>(anything)</I>$src" if $src =~ /^@/; +% $src; +% } +%}; +% +%my $link_src = sub { +% my $svc_forward = shift; +% if ( $svc_forward->srcsvc_acct ) { +% [ "${p}view/svc_acct.cgi?", 'srcsvc' ]; +% } else { +% ''; +% } +%}; +% +%my $format_dst = sub { +% my $svc_forward = shift; +% if ( $svc_forward->dstsvc_acct ) { +% $svc_forward->dstsvc_acct->email; +% } else { +% $svc_forward->dst; +% } +%}; +% +%my $link_dst = sub { +% my $svc_forward = shift; +% if ( $svc_forward->dstsvc_acct ) { +% [ "${p}view/svc_acct.cgi?", 'dstsvc' ]; +% } else { +% ''; +% } +%}; +% +%#smaller false laziness w/svc_*.cgi here +%my $link_cust = sub { +% my $svc_x = shift; +% $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +%}; +% +% +<% include( 'elements/search.html', 'title' => "Mail forward Search Results", 'name' => 'mail forwards', 'query' => $sql_query, diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi index a68a13e39..26e2090e6 100644 --- a/httemplate/search/svc_phone.cgi +++ b/httemplate/search/svc_phone.cgi @@ -1,70 +1,71 @@ -<% - -my $conf = new FS::Conf; - -my($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors - -my $orderby = 'ORDER BY svcnum'; -my %svc_phone = (); -my @extra_sql = (); -if ( $query eq 'svcnum' ) { - #$orderby = 'ORDER BY svcnum'; -} elsif ( $query eq 'phonenum' ) { - $orderby = 'ORDER BY phonenum'; -} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { - #$orderby = 'ORDER BY svcnum'; - push @extra_sql, "svcpart = $1"; -} else { - $cgi->param('phonenum') =~ /^([\d\- ]+)$/; - ( $svc_phone{'phonenum'} = $1 ) =~ s/\D//g; -} - -my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) '; - -#here is the agent virtualization -push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; - -my $extra_sql = ''; -if ( @extra_sql ) { - $extra_sql = ( keys(%svc_phone) ? ' AND ' : ' WHERE ' ). - join(' AND ', @extra_sql ); -} - -my $count_query = "SELECT COUNT(*) FROM svc_phone $addl_from "; -if ( keys %svc_phone ) { - $count_query .= ' WHERE '. - join(' AND ', map "$_ = ". dbh->quote($svc_phone{$_}), - keys %svc_phone - ); -} -$count_query .= $extra_sql; - -my $sql_query = { - 'table' => 'svc_phone', - 'hashref' => \%svc_phone, - 'select' => join(', ', - 'svc_phone.*', - 'part_svc.svc', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => "$extra_sql $orderby", - 'addl_from' => $addl_from, -}; - -my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ]; - -#smaller false laziness w/svc_*.cgi here -my $link_cust = sub { - my $svc_x = shift; - $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; -}; - -%><%= include( 'elements/search.html', +% +% +%my $conf = new FS::Conf; +% +%my($query)=$cgi->keywords; +%$query ||= ''; #to avoid use of unitialized value errors +% +%my $orderby = 'ORDER BY svcnum'; +%my %svc_phone = (); +%my @extra_sql = (); +%if ( $query eq 'svcnum' ) { +% #$orderby = 'ORDER BY svcnum'; +%} elsif ( $query eq 'phonenum' ) { +% $orderby = 'ORDER BY phonenum'; +%} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { +% #$orderby = 'ORDER BY svcnum'; +% push @extra_sql, "svcpart = $1"; +%} else { +% $cgi->param('phonenum') =~ /^([\d\- ]+)$/; +% ( $svc_phone{'phonenum'} = $1 ) =~ s/\D//g; +%} +% +%my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. +% ' LEFT JOIN part_svc USING ( svcpart ) '. +% ' LEFT JOIN cust_pkg USING ( pkgnum ) '. +% ' LEFT JOIN cust_main USING ( custnum ) '; +% +%#here is the agent virtualization +%push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql; +% +%my $extra_sql = ''; +%if ( @extra_sql ) { +% $extra_sql = ( keys(%svc_phone) ? ' AND ' : ' WHERE ' ). +% join(' AND ', @extra_sql ); +%} +% +%my $count_query = "SELECT COUNT(*) FROM svc_phone $addl_from "; +%if ( keys %svc_phone ) { +% $count_query .= ' WHERE '. +% join(' AND ', map "$_ = ". dbh->quote($svc_phone{$_}), +% keys %svc_phone +% ); +%} +%$count_query .= $extra_sql; +% +%my $sql_query = { +% 'table' => 'svc_phone', +% 'hashref' => \%svc_phone, +% 'select' => join(', ', +% 'svc_phone.*', +% 'part_svc.svc', +% 'cust_main.custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'extra_sql' => "$extra_sql $orderby", +% 'addl_from' => $addl_from, +%}; +% +%my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ]; +% +%#smaller false laziness w/svc_*.cgi here +%my $link_cust = sub { +% my $svc_x = shift; +% $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +%}; +% +% +<% include( 'elements/search.html', 'title' => "Phone number search results", 'name' => 'phone numbers', 'query' => $sql_query, diff --git a/httemplate/search/svc_www.cgi b/httemplate/search/svc_www.cgi index ef4045cf9..b0f1d5c80 100755 --- a/httemplate/search/svc_www.cgi +++ b/httemplate/search/svc_www.cgi @@ -1,43 +1,44 @@ -<% - -#my $conf = new FS::Conf; - -my($query)=$cgi->keywords; -$query ||= ''; #to avoid use of unitialized value errors -my $orderby; -if ( $query eq 'svcnum' ) { - $orderby = 'ORDER BY svcnum'; -} else { - eidiot('unimplemented'); -} - -my $count_query = 'SELECT COUNT(*) FROM svc_www'; -my $sql_query = { - 'table' => 'svc_www', - 'hashref' => {}, - 'select' => join(', ', - 'svc_www.*', - 'part_svc.svc', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => $orderby, - 'addl_from' => 'LEFT JOIN cust_svc USING ( svcnum )'. - 'LEFT JOIN cust_pkg USING ( pkgnum )'. - 'LEFT JOIN cust_main USING ( custnum )', -}; - -my $link = [ "${p}view/svc_www.cgi?", 'svcnum', ]; -#my $dlink = [ "${p}view/svc_www.cgi?", 'svcnum', ]; -my $ulink = [ "${p}view/svc_acct.cgi?", 'usersvc', ]; - -#smaller false laziness w/svc_*.cgi here -my $link_cust = sub { - my $svc_x = shift; - $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; -}; - -%><%= include( 'elements/search.html', +% +% +%#my $conf = new FS::Conf; +% +%my($query)=$cgi->keywords; +%$query ||= ''; #to avoid use of unitialized value errors +%my $orderby; +%if ( $query eq 'svcnum' ) { +% $orderby = 'ORDER BY svcnum'; +%} else { +% eidiot('unimplemented'); +%} +% +%my $count_query = 'SELECT COUNT(*) FROM svc_www'; +%my $sql_query = { +% 'table' => 'svc_www', +% 'hashref' => {}, +% 'select' => join(', ', +% 'svc_www.*', +% 'part_svc.svc', +% 'cust_main.custnum', +% FS::UI::Web::cust_sql_fields(), +% ), +% 'extra_sql' => $orderby, +% 'addl_from' => 'LEFT JOIN cust_svc USING ( svcnum )'. +% 'LEFT JOIN cust_pkg USING ( pkgnum )'. +% 'LEFT JOIN cust_main USING ( custnum )', +%}; +% +%my $link = [ "${p}view/svc_www.cgi?", 'svcnum', ]; +%#my $dlink = [ "${p}view/svc_www.cgi?", 'svcnum', ]; +%my $ulink = [ "${p}view/svc_acct.cgi?", 'usersvc', ]; +% +%#smaller false laziness w/svc_*.cgi here +%my $link_cust = sub { +% my $svc_x = shift; +% $svc_x->custnum ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; +%}; +% +% +<% include( 'elements/search.html', 'title' => 'Virtual Host Search Results', 'name' => 'virtual hosts', 'query' => $sql_query, |