diff options
Diffstat (limited to 'httemplate/search/cust_bill.html')
| -rwxr-xr-x | httemplate/search/cust_bill.html | 350 |
1 files changed, 185 insertions, 165 deletions
diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 894ddad51..b65608eab 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -1,166 +1,3 @@ -% -% -% 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, @@ -183,14 +20,197 @@ sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, ], - 'align' => 'rrrrll', + 'align' => 'rrrr'.FS::UI::Web::cust_aligns(), 'links' => [ $link, $link, $link, $link, - ( map { $clink } FS::UI::Web::cust_header() ), + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), ], + 'color' => [ + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], + ) %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List invoices'); + +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'); + +</%init> |