diff options
Diffstat (limited to 'httemplate/search/elements')
-rw-r--r-- | httemplate/search/elements/cust_main_dayranges.html | 287 | ||||
-rwxr-xr-x | httemplate/search/elements/cust_pay_or_refund.html | 456 | ||||
-rw-r--r-- | httemplate/search/elements/metasearch.html | 71 | ||||
-rw-r--r-- | httemplate/search/elements/report_cust_pay_or_refund.html | 149 | ||||
-rw-r--r-- | httemplate/search/elements/search-csv.html | 54 | ||||
-rw-r--r-- | httemplate/search/elements/search-html.html | 491 | ||||
-rw-r--r-- | httemplate/search/elements/search-xls.html | 85 | ||||
-rw-r--r-- | httemplate/search/elements/search-xml.html | 89 | ||||
-rw-r--r-- | httemplate/search/elements/search.html | 439 |
9 files changed, 0 insertions, 2121 deletions
diff --git a/httemplate/search/elements/cust_main_dayranges.html b/httemplate/search/elements/cust_main_dayranges.html deleted file mode 100644 index 91e039d28..000000000 --- a/httemplate/search/elements/cust_main_dayranges.html +++ /dev/null @@ -1,287 +0,0 @@ -<%doc> - -Example: - - include( 'elements/cust_main_dayranges.html', - 'title' => 'Accounts Receivable Aging Summary', - 'range_sub' => $mysub, - ) - - my $mysub = sub { - my( $start, $end ) = @_; - - "SQL EXPRESSION BASED ON $start AND $end"; - # where $start and $end are unix timestamps - }; - -</%doc> -<% include( 'search.html', - 'name' => 'customers', - 'query' => $sql_query, - 'count_query' => $count_sql, - 'header' => [ - FS::UI::Web::cust_header(), - '0-30', - '30-60', - '60-90', - '90+', - 'Total', - @pay_head, - ], - 'footer' => [ - 'Total', - ( map '', - ( 1 .. - scalar(FS::UI::Web::cust_header()-1) - ), - ), - - sprintf( $money_char.'%.2f', - $row->{'rangecol_0_30'} ), - sprintf( $money_char.'%.2f', - $row->{'rangecol_30_60'} ), - sprintf( $money_char.'%.2f', - $row->{'rangecol_60_90'} ), - sprintf( $money_char.'%.2f', - $row->{'rangecol_90_0'} ), - sprintf( '<b>'. $money_char.'%.2f'. '</b>', - $row->{'rangecol_0_0'} ), - ('') x @pay_labels, - ], - 'fields' => [ - FS::UI::Web::cust_fields_subs(), - format_rangecol('0_30'), - format_rangecol('30_60'), - format_rangecol('60_90'), - format_rangecol('90_0'), - format_rangecol('0_0'), - @pay_labels, - ], - 'links' => [ - ( map { $_ ne 'Cust. Status' ? $clink : '' } - FS::UI::Web::cust_header() - ), - '', - '', - '', - '', - '', - @pay_links, - ], - #'align' => 'rlccrrrrr', - 'align' => FS::UI::Web::cust_aligns(). - 'rrrrr'. - ('c' x @pay_labels), - #'size' => [ '', '', '-1', '-1', '', '', '', '', '', ], - #'style' => [ '', '', 'b', 'b', '', '', '', '', 'b', ], - 'size' => [ ( map '', FS::UI::Web::cust_header() ), - #'-1', '', '', '', '', '', ], - '', '', '', '', '', '', - ( map '', @pay_labels ), - ], - 'style' => [ FS::UI::Web::cust_styles(), - #'b', '', '', '', '', 'b', ], - '', '', '', '', 'b', - ( map '', @pay_labels ), - ], - 'color' => [ - FS::UI::Web::cust_colors(), - '', - '', - '', - '', - '', - '', - ( map '', @pay_labels ), - ], - %opt, - ) -%> -<%init> - -my %opt = @_; - -#actually need to auto-generate other things too for a passed-in ranges to work -my $ranges = $opt{'ranges'} ? delete($opt{'ranges'}) : [ - [ 0, 30 ], - [ 30, 60 ], - [ 60, 90 ], - [ 90, 0 ], - [ 0, 0 ], -]; - -my $range_sub = delete($opt{'range_sub'}); #or die - -my $offset = 0; -if($cgi->param('as_of')) { - $offset = int((time - parse_datetime($cgi->param('as_of'))) / 86400); - $opt{'title'} .= ' ('.$cgi->param('as_of').')' if $offset > 0; -} - -#my $range_cols = join(',', map &{$range_sub}( @$_ ), @ranges ); -my $range_cols = join(',', map call_range_sub($range_sub, @$_, 'offset' => $offset ), @$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->suspended_sql; -my $cancelled_sql = FS::cust_pkg->cancelled_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 = (); - -unless ( $cgi->param('all_customers') ) { -# Exclude entire cust_main records where the balance is >0 - my $days = 0; - if ( $cgi->param('days') =~ /^\s*(\d+)\s*$/ ) { - $days = $1; - } - - # If this is set, allow cust_main records with nonzero balances - my $negative = $cgi->param('negative') || 0; - - push @where, - call_range_sub($range_sub, $days, 0, 'offset' => $offset, 'no_as'=>1). - ($negative ? ' != 0' : ' > 0'); -} - -if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - my $agentnum = $1; - push @where, "agentnum = $agentnum"; -} - -#status (false laziness w/cust_main::search_sql - -#prospect active inactive suspended cancelled -if ( grep { $cgi->param('status') eq $_ } FS::cust_main->statuses() ) { - my $method = $cgi->param('status'). '_sql'; - #push @where, $class->$method(); - push @where, FS::cust_main->$method(); -} - -#here is the agent virtualization -push @where, $FS::CurrentUser::CurrentUser->agentnums_sql; - -my $where = join(' AND ', @where); -$where = "WHERE $where" if $where; - -my $count_sql = "select count(*) from cust_main $where"; - -my $sql_query = { - 'table' => 'cust_main', - 'hashref' => {}, - 'select' => join(',', - #'cust_main.*', - 'custnum', - $range_cols, - $packages_cols, - FS::UI::Web::cust_sql_fields(), - 'payby', - ), - 'extra_sql' => $where, - 'order_by' => "order by coalesce(lower(company), ''), lower(last)", -}; - -my $total_sql = - "SELECT ". - join(',', map call_range_sub( $range_sub, @$_, 'offset' => $offset, 'sum'=>1 ), @$ranges). - " FROM cust_main $where"; - -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 $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; - -my (@payby, @pay_head, @pay_labels, @pay_links); - -my %payby = map {$_ => 1} $conf->config('payby'); -if(%payby) { - push @payby, 'CARD' if ($payby{'CARD'} or $payby{'DCRD'}); - push @payby, 'CHEK' if ($payby{'CHEK'} or $payby{'DCHK'}); -} -else { - @payby = ('CARD','CHEK') -} - -if($opt{'payment_links'} && $curuser->access_right('Process payment') && @payby) { - my %label = ( CARD => 'Card', - CHEK => 'E-Check' ); - push @pay_head, ({nodownload => 1}) foreach @payby; - $pay_head[0] = { label => 'Process', - nodownload => 1, - colspan => scalar(@payby) }; - - @pay_labels = (map { my $payby = $_; - my $label = $label{$payby}; - sub {($payby eq $_[0]->payby) ? "<b>$label (on file)</b>" : $label} - } @payby ); - - @pay_links = (map { [ "${p}misc/payment.cgi?payby=$_;custnum=", 'custnum' ] } - @payby ); -} - -</%init> -<%once> - -my $conf = new FS::Conf; -my $curuser = $FS::CurrentUser::CurrentUser; - -my $money_char = $conf->config('money_char') || '$'; - -#Example: -# -# my $balance = balance( -# $start, $end, -# 'no_as' => 1, #set to true when using in a WHERE clause (supress AS clause) -# #or 0 / omit when using in a SELECT clause as a column -# # ("AS balance_$start_$end") -# 'sum' => 1, #set to true to get a SUM() of the values, for totals -# -# #obsolete? options for totals (passed to cust_main::balance_date_sql) -# 'total' => 1, #set to true to remove all customer comparison clauses -# 'join' => $join, #JOIN clause -# 'where' => \@where, #WHERE clause hashref (elements "AND"ed together) -# ) - -sub call_range_sub { - my($range_sub, $startdays, $enddays, %opt) = @_; - - my $as = $opt{'no_as'} ? '' : " AS rangecol_${startdays}_$enddays"; - - my $offset = $opt{'offset'} || 0; - # Always use $offset - 1day + 1sec = the last second of that day - my $cutoff = DateTime->now->set(hour => 23, minute => 59, second => 59); - $cutoff->subtract(days => $offset); - - my $start = $cutoff->clone; - $start->subtract(days => $startdays); - - my $end = $cutoff->clone; - $end->subtract(days => $enddays); - - #warn "offset $offset (".$cutoff->epoch."), range $startdays-$enddays (".$start->epoch . '-' . ($enddays ? $end->epoch : '').")\n"; - my $sql = &{$range_sub}( $start->epoch, - $enddays ? $end->epoch : '', - $cutoff->epoch ); #%opt? - - $sql = "SUM($sql)" if $opt{'sum'}; - - $sql.$as; - -} - -sub format_rangecol { #closures help alot - my $range = shift; - sub { sprintf( $money_char.'%.2f', shift->get("rangecol_$range") ) }; -} - -</%once> diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html deleted file mode 100755 index fccb9eef7..000000000 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ /dev/null @@ -1,456 +0,0 @@ -<%doc> - -Examples: - - include( 'elements/cust_pay_or_refund.html', - 'thing' => 'pay', - 'amount_field' => 'paid', - 'name_singular' => 'payment', - 'name_verb' => 'paid', - ) - - include( 'elements/cust_pay_or_refund.html', - 'thing' => 'refund', - 'amount_field' => 'refund', - 'name_singular' => 'refund', - 'name_verb' => 'refunded', - ) - - include( 'elements/cust_pay_or_refund.html', - 'thing' => 'pay_pending', - 'amount_field' => 'paid', - 'name_singular' => 'pending payment', - 'name_verb' => 'pending', - 'disable_link' => 1, - 'disable_by' => 1, - 'html_init' => '', - 'addl_header' => [], - 'addl_fields' => [], - 'redirect_empty' => $redirect_empty, - ) - - include( 'elements/cust_pay_or_refund.html', - 'table' => 'h_cust_pay', - 'amount_field' => 'paid', - 'name_singular' => 'payment', - 'name_verb' => 'paid', - 'pre_header' => [ 'Transaction', 'By' ], - 'pre_fields' => [ 'history_action', 'history_user' ], - ) - -</%doc> -<% include( 'search.html', - 'title' => $title, - 'name_singular' => $name_singular, - 'query' => $sql_query, - 'count_query' => $count_query, - 'count_addl' => \@count_addl, - 'redirect_empty' => $opt{'redirect_empty'}, - 'header' => \@header, - 'fields' => \@fields, - 'sort_fields' => \@sort_fields, - 'align' => $align, - 'links' => \@links, - 'color' => \@color, - 'style' => \@style, - ) -%> -<%init> - -my %opt = @_; - -my $curuser = $FS::CurrentUser::CurrentUser; - -die "access denied" - unless $curuser->access_right('Financial reports'); - -my $table = $opt{'table'} || 'cust_'.$opt{'thing'}; - -my $amount_field = $opt{'amount_field'}; -my $name_singular = $opt{'name_singular'}; - -my $unapplied = $cgi->param('unapplied'); -my $title = ''; -$title = 'Unapplied ' if $unapplied; -$title .= "\u$name_singular Search Results"; - -my $link = ''; -if ( ( $curuser->access_right('View invoices') #XXX for now - || $curuser->access_right('View customer payments') - ) - && ! $opt{'disable_link'} - ) -{ - - my $key; - my $q = ''; - if ( $table eq 'cust_pay_void' ) { - $key = 'paynum'; - $q .= 'void=1;'; - } elsif ( $table eq /^cust_(\w+)$/ ) { - $key = $1.'num'; - } - - if ( $key ) { - $q .= "$key="; - $link = [ "${p}view/$table.html?$q", $key ] - } -} - -my $cust_link = sub { - my $cust_thing = shift; - $cust_thing->cust_main_custnum - ? [ "${p}view/cust_main.cgi?", 'custnum' ] - : ''; -}; - -# only valid for $table == 'cust_pay' atm -my $tax_names = ''; -if ( $cgi->param('tax_names') ) { - if ( dbh->{Driver}->{Name} eq 'Pg' ) { - - $tax_names = " - array_to_string( - array( - SELECT itemdesc - FROM cust_bill_pay - LEFT JOIN cust_bill_pay_pkg USING ( billpaynum ) - LEFT JOIN cust_bill_pkg USING ( billpkgnum ) - WHERE cust_bill_pkg.pkgnum = 0 - AND cust_bill_pay.paynum = cust_pay.paynum - ), '|' - ) AS tax_names" - ; - - } elsif ( dbh->{Driver}->{Name} =~ /^mysql/i ) { - - $tax_names = "GROUP_CONCAT(itemdesc SEPARATOR '|') AS tax_names"; - - } else { - - warn "warning: unknown database type ". dbh->{Driver}->{Name}. - "omitting tax name information from report."; - - } -} - -my @header = (); -my @fields = (); -my @sort_fields = (); -my $align = ''; -my @links = (); -if ( $opt{'pre_header'} ) { - push @header, @{ $opt{'pre_header'} }; - $align .= 'c' x scalar(@{ $opt{'pre_header'} }); - push @links, map '', @{ $opt{'pre_header'} }; - push @fields, @{ $opt{'pre_fields'} }; - push @sort_fields, @{ $opt{'pre_fields'} }; -} - -push @header, "\u$name_singular", - 'Amount', -; -$align .= 'rr'; -push @links, '', ''; -push @fields, 'payby_payinfo_pretty', - sub { sprintf('$%.2f', shift->$amount_field() ) }, -; -push @sort_fields, '', $amount_field; - -if ( $unapplied ) { - push @header, 'Unapplied'; - $align .= 'r'; - push @links, ''; - push @fields, sub { sprintf('$%.2f', shift->unapplied_amount) }; - push @sort_fields, ''; -} - -push @header, 'Date'; -$align .= 'r'; -push @links, ''; -push @fields, sub { time2str('%b %d %Y', shift->_date ) }; -push @sort_fields, '_date'; - -unless ( $opt{'disable_by'} ) { - push @header, 'By'; - $align .= 'c'; - push @links, ''; - push @fields, sub { my $o = shift->otaker; - $o = 'auto billing' if $o eq 'fs_daily'; - $o = 'customer self-service' if $o eq 'fs_selfservice'; - $o; - }; -} - -if ( $tax_names ) { - push @header, ('Tax names', 'Tax province'); - $align .= 'cc'; - push @links, ('',''); - push @fields, sub { join (' + ', map { /^(.*?)(, \w\w)?$/; $1 } - split('\|', shift->tax_names) - ); - }; - push @fields, sub { join (' + ', map { if (/^(?:.*)(?:, )(\w\w)$/){ $1 } - else { () } - } - split('\|', shift->tax_names) - ); - }; -} - -push @header, FS::UI::Web::cust_header(); -$align .= FS::UI::Web::cust_aligns(); -push @links, map { $_ ne 'Cust. Status' ? $cust_link : '' } - FS::UI::Web::cust_header(); -my @color = ( ( map '', @fields ), FS::UI::Web::cust_colors() ); -my @style = ( ( map '', @fields ), FS::UI::Web::cust_styles() ); -push @fields, \&FS::UI::Web::cust_fields; - -push @header, @{ $opt{'addl_header'} } - if $opt{'addl_header'}; -push @fields, @{ $opt{'addl_fields'} } - if $opt{'addl_fields'}; - -my( $count_query, $sql_query, @count_addl ); -if ( $cgi->param('magic') ) { - - my @search = (); - my @select = ( - "$table.*", - FS::UI::Web::cust_sql_fields(), - 'cust_main.custnum AS cust_main_custnum', - ); - push @select, $tax_names if $tax_names; - - 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('custnum') =~ /^(\d+)$/ ) { - push @search, "custnum = $1"; - } - - 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, "$table.payby = '$1'"; - if ( $3 ) { - - my $cardtype = $3; - - my $search; - if ( $cardtype eq 'VisaMC' ) { - #avoid posix regexes for portability - $search = - " ( ( substring($table.payinfo from 1 for 1) = '4' ". - " AND substring($table.payinfo from 1 for 4) != '4936' ". - " AND substring($table.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49030[2-9]' ". - " AND substring($table.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49033[5-9]' ". - " AND substring($table.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49110[1-2]' ". - " AND substring($table.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49117[4-9]' ". - " AND substring($table.payinfo from 1 for 6) ". - " NOT SIMILAR TO '49118[1-2]' ". - " )". - " OR substring($table.payinfo from 1 for 2) = '51' ". - " OR substring($table.payinfo from 1 for 2) = '52' ". - " OR substring($table.payinfo from 1 for 2) = '53' ". - " OR substring($table.payinfo from 1 for 2) = '54' ". - " OR substring($table.payinfo from 1 for 2) = '54' ". - " OR substring($table.payinfo from 1 for 2) = '55' ". - " OR substring($table.payinfo from 1 for 2) = '36' ". #Diner's int'l processed as Visa/MC inside US - " ) "; - } elsif ( $cardtype eq 'Amex' ) { - $search = - " ( substring($table.payinfo from 1 for 2 ) = '34' ". - " OR substring($table.payinfo from 1 for 2 ) = '37' ". - " ) "; - } elsif ( $cardtype eq 'Discover' ) { - $search = - " ( substring($table.payinfo from 1 for 4 ) = '6011' ". - " OR substring($table.payinfo from 1 for 2 ) = '65' ". - " OR substring($table.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN - " ) "; - } elsif ( $cardtype eq 'Maestro' ) { - $search = - " ( substring($table.payinfo from 1 for 2 ) = '63' ". - " OR substring($table.payinfo from 1 for 2 ) = '67' ". - " OR substring($table.payinfo from 1 for 6 ) = '564182' ". - " OR substring($table.payinfo from 1 for 4 ) = '4936' ". - " OR substring($table.payinfo from 1 for 6 ) ". - " SIMILAR TO '49030[2-9]' ". - " OR substring($table.payinfo from 1 for 6 ) ". - " SIMILAR TO '49033[5-9]' ". - " OR substring($table.payinfo from 1 for 6 ) ". - " SIMILAR TO '49110[1-2]' ". - " OR substring($table.payinfo from 1 for 6 ) ". - " SIMILAR TO '49117[4-9]' ". - " OR substring($table.payinfo from 1 for 6 ) ". - " SIMILAR TO '49118[1-2]' ". - " ) "; - } else { - die "unknown card type $cardtype"; - } - - my $masksearch = $search; - $masksearch =~ s/$table\.payinfo/$table.paymask/gi; - - push @search, - "( $search OR ( $table.paymask IS NOT NULL AND $masksearch ) )"; - - } - } - - if ( $cgi->param('payinfo') ) { - $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ - or die "illegal payinfo ". $cgi->param('payinfo'); - push @search, "$table.payinfo = '$1'"; - } - - if ( $cgi->param('usernum') =~ /^(\d+)$/ ) { - push @search, "$table.usernum = $1"; - } - - #for cust_pay_pending... statusNOT=done - if ( $cgi->param('statusNOT') =~ /^(\w+)$/ ) { - push @search, "status != '$1'"; - } - - my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); - push @search, "_date >= $beginning ", - "_date <= $ending"; - - if ( $table eq 'cust_pay_void' ) { - my($v_beginning, $v_ending) = - FS::UI::Web::parse_beginning_ending($cgi, 'void'); - push @search, "void_date >= $v_beginning ", - "void_date <= $v_ending"; - } - - push @search, FS::UI::Web::parse_lt_gt($cgi, $amount_field ); - - $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'); - } - - #unapplied payment/refund - if ( $unapplied ) { - push @select, '(' . "FS::$table"->unapplied_sql . ') AS unapplied_amount'; - push @search, "FS::$table"->unapplied_sql . ' > 0'; - - } - - #for the history search - if ( $cgi->param('history_action') =~ /^([\w,]+)$/ ) { - my @history_action = split(/,/, $1); - push @search, 'history_action IN ('. - join(',', map "'$_'", @history_action ). ')'; - } - - if ( $cgi->param('history_date_beginning') - || $cgi->param('history_date_ending') ) { - my($h_beginning, $h_ending) = - FS::UI::Web::parse_beginning_ending($cgi, 'history_date'); - push @search, "history_date >= $h_beginning ", - "history_date <= $h_ending"; - } - - #here is the agent virtualization - push @search, $curuser->agentnums_sql; - - my $addl_from = ' LEFT JOIN cust_main USING ( custnum ) '; - my $group_by = ''; - - if ( $cgi->param('tax_names') ) { - if ( dbh->{Driver}->{Name} eq 'Pg' ) { - - 0;#twiddle thumbs - - } elsif ( dbh->{Driver}->{Name} =~ /^mysql/i ) { - - $addl_from .= "LEFT JOIN cust_bill_pay USING ( paynum ) - LEFT JOIN cust_bill_pay_pkg USING ( billpaynum ) - LEFT JOIN cust_bill_pkg USING ( billpkgnum ) AS tax_names"; - $group_by .= "GROUP BY $table.*,cust_main_custnum,". - FS::UI::Web::cust_sql_fields(); - push @search, - "( cust_bill_pkg.pkgnum = 0 OR cust_bill_pkg.pkgnum is NULL )"; - - } else { - - warn "warning: unknown database type ". dbh->{Driver}->{Name}. - "omitting tax name information from report."; - - } - } - - my $search = ' WHERE '. join(' AND ', @search); - - $count_query = "SELECT COUNT(*), SUM($amount_field) "; - $count_query .= ', SUM(' . "FS::$table"->unapplied_sql . ') ' - if $unapplied; - $count_query .= "FROM $table $addl_from". - "$search $group_by"; - - @count_addl = ( '$%.2f total '.$opt{name_verb} ); - push @count_addl, '$%.2f unapplied' if $unapplied; - - $sql_query = { - 'table' => $table, - 'select' => join(', ', @select), - 'hashref' => {}, - 'extra_sql' => "$search $group_by", - 'order_by' => "ORDER BY $orderby", - 'addl_from' => $addl_from, - }; - -} else { - - #hmm... is this still used? - - $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($amount_field) FROM $table". - " WHERE payinfo = '$payinfo' AND payby = '$payby'". - " AND ". $curuser->agentnums_sql; - @count_addl = ( '$%.2f total '.$opt{name_verb} ); - - $sql_query = { - 'table' => $table, - 'hashref' => { 'payinfo' => $payinfo, - 'payby' => $payby }, - 'extra_sql' => $curuser->agentnums_sql. - " ORDER BY _date", - }; - -} - -# for consistency -$title = join('',map {ucfirst} split(/\b/,$title)); - -</%init> diff --git a/httemplate/search/elements/metasearch.html b/httemplate/search/elements/metasearch.html deleted file mode 100644 index b9d3e3ce2..000000000 --- a/httemplate/search/elements/metasearch.html +++ /dev/null @@ -1,71 +0,0 @@ -<%doc> - -Example: - - include( 'elements/metasearch.html', - - ### - # required - ### - - 'title' => 'Page title', - - #arrayref of hashrefs suited for passing to elements/search.html - #see that documentation - 'search' => [ - { - query => { 'table' => 'tablename', - #everything else is optional... - 'hashref' => { 'f1' => 'value', - 'f2' => { 'op' => '<', - 'value' => '54', - }, - }, - 'select' => '*', - 'order_by' => 'ORDER BY something', - - }, - count_query => 'SELECT COUNT(*) FROM tablename', - }, - { - query => 'table' => 'anothertablename', - count_query => 'SELECT COUNT(*) FROM anothertablename', - }, - ], - - ### - # optional - ### - - # some HTML callbacks... - 'menubar' => '', #menubar arrayref - 'html_init' => '', #after the header/menubar and before the pager - 'html_form' => '', #after the pager, right before the results - # (only shown if there are results) - # (use this for any form-opening tag rather than - # html_init, to avoid a nested form) - 'html_foot' => '', #at the bottom - 'html_posttotal' => '', #at the bottom - # (these three can be strings or coderefs) - - ); - -</%doc> -% foreach my $search ( @{$opt{search}} ) { -<% include('search.html', - %$search, - 'type' => $type, - 'nohtmlheader' => 1, - ) -%> -% -% } -<%init> - -my(%opt) = @_; -#warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n"; - -my $type = $cgi->param('_type') =~ /^(csv|\w*\.xls|select|html(-print)?)$/ - ? $1 : 'html' ; - -</%init> diff --git a/httemplate/search/elements/report_cust_pay_or_refund.html b/httemplate/search/elements/report_cust_pay_or_refund.html deleted file mode 100644 index 9af4e33dc..000000000 --- a/httemplate/search/elements/report_cust_pay_or_refund.html +++ /dev/null @@ -1,149 +0,0 @@ -<%doc> - -Examples: - - include( 'elements/report_cust_pay_or_refund.html', - 'thing' => 'pay', - 'name_singular' => 'payment', - ) - - include( 'elements/report_cust_pay_or_refund.html', - 'thing' => 'refund', - 'name_singular' => 'refund', - ) - -</%doc> -<% include('/elements/header.html', $title ) %> - -<FORM ACTION="<% $table %>.html" METHOD="GET"> -<INPUT TYPE="hidden" NAME="magic" VALUE="_date"> -<INPUT TYPE="hidden" NAME="unapplied" VALUE="<% $unapplied %>"> - -<TABLE BGCOLOR="#cccccc" CELLSPACING=0> - - <TR> - <TH CLASS="background" COLSPAN=2 ALIGN="left"> - <FONT SIZE="+1">Search options</FONT> - </TH> - </TR> - - <TR> - <TD ALIGN="right"><% ucfirst(PL($name_singular)) %> of type: </TD> - <TD> - <SELECT NAME="payby" onChange="payby_changed(this)"> - <OPTION VALUE="">all</OPTION> - <OPTION VALUE="CARD">credit card (all)</OPTION> - <OPTION VALUE="CARD-VisaMC">credit card (Visa/MasterCard)</OPTION> - <OPTION VALUE="CARD-Amex">credit card (American Express)</OPTION> - <OPTION VALUE="CARD-Discover">credit card (Discover)</OPTION> - <OPTION VALUE="CARD-Maestro">credit card (Maestro/Switch/Solo)</OPTION> - <OPTION VALUE="CHEK">electronic check / ACH</OPTION> - <OPTION VALUE="BILL">check</OPTION> - <OPTION VALUE="PREP">prepaid card</OPTION> - <OPTION VALUE="CASH">cash</OPTION> - <OPTION VALUE="WEST">Western Union</OPTION> - <OPTION VALUE="MCRD">manual credit card</OPTION> - </SELECT> - </TD> - </TR> - - <SCRIPT TYPE="text/javascript"> - - function payby_changed(what) { - if ( what.options[what.selectedIndex].value == 'BILL' ) { - document.getElementById('checkno_caption').style.color = '#000000'; - what.form.payinfo.disabled = false; - what.form.payinfo.style.backgroundColor = '#ffffff'; - } else { - document.getElementById('checkno_caption').style.color = '#bbbbbb'; - what.form.payinfo.disabled = true; - what.form.payinfo.style.backgroundColor = '#dddddd'; - } - } - - </SCRIPT> - - <TR> - <TD ALIGN="right"><FONT ID="checkno_caption" COLOR="#bbbbbb">Check #: </FONT></TD> - <TD> - <INPUT TYPE="text" NAME="payinfo" DISABLED STYLE="background-color: #dddddd"> - </TD> - </TR> - - <% include( '/elements/tr-select-agent.html', - 'curr_value' => scalar($cgi->param('agentnum')), - 'label' => 'for agent: ', - 'disable_empty' => 0, - ) - %> - - <% include( '/elements/tr-select-user.html' ) %> - - <TR> - <TD ALIGN="right" VALIGN="center">Payment</TD> - <TD> - <TABLE> - <% include( '/elements/tr-input-beginning_ending.html', - layout => 'horiz', - ) - %> - </TABLE> - </TD> - </TR> - -% if ( $void ) { - <TR> - <TD ALIGN="right" VALIGN="center">Voided</TD> - <TD> - <TABLE> - <% include( '/elements/tr-input-beginning_ending.html', - prefix => 'void', - layout => 'horiz', - ) - %> - </TABLE> - </TD> - </TR> -% } - - <% include( '/elements/tr-input-lessthan_greaterthan.html', - 'label' => 'Amount', - 'field' => 'paid', - ) - %> - -% if ( $table eq 'cust_pay' ) { - <% include( '/elements/tr-checkbox.html', - 'label' => 'Include tax names', - 'field' => 'tax_names', - 'value' => 1, - ) - %> -% } - -</TABLE> - -<BR> -<INPUT TYPE="submit" VALUE="Get Report"> - -</FORM> - -<% include('/elements/footer.html') %> -<%init> - -my %opt = @_; -my $table = 'cust_'.$opt{'thing'}; -my $name_singular = $opt{'name_singular'}; - -die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); - -my $void = $cgi->param('void') ? 1 : 0; -my $unapplied = $cgi->param('unapplied') ? 1 : 0; - -my $title = $void ? "Voided $name_singular report" : - $unapplied ? "Unapplied $name_singular report" : - "\u$name_singular report" ; -$table .= '_void' if $void; - -</%init> diff --git a/httemplate/search/elements/search-csv.html b/httemplate/search/elements/search-csv.html deleted file mode 100644 index 9eb1b66d1..000000000 --- a/httemplate/search/elements/search-csv.html +++ /dev/null @@ -1,54 +0,0 @@ -% $csv->combine(@$header); #or die $csv->status; -% -<% $opt{no_csv_header} ? '' : $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 %>\ -% -% } -<%init> - -my %args = @_; -my $header = $args{'header'}; -my $rows = $args{'rows'}; -my %opt = %{ $args{'opt'} }; - -#http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes -#http_header('Content-Type' => 'text/plain' ); -http_header('Content-Type' => 'text/csv' ); # So saith RFC 4180 -http_header('Content-Disposition' => - 'attachment;filename="'.($opt{'name'} || PL($opt{'name_singular'}) ).'.csv"'); - -my $quote_char = '"'; -$quote_char = $opt{csv_quote} if exists($opt{csv_quote}); - -my $csv = new Text::CSV_XS { 'always_quote' => $opt{avoid_quote} ? 0 : 1, - 'eol' => "\n", #"\015\012", #"\012" - }; - -</%init> diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html deleted file mode 100644 index d6cc62691..000000000 --- a/httemplate/search/elements/search-html.html +++ /dev/null @@ -1,491 +0,0 @@ -% if ( exists($opt{'redirect'}) && $opt{'redirect'} -% && scalar(@$rows) == 1 && $total == 1 -% && $type ne 'html-print' -% ) { -% my $redirect = $opt{'redirect'}; -% $redirect = &{$redirect}($rows->[0], $cgi) if ref($redirect) eq 'CODE'; -% my( $url, $method ) = @$redirect; -% redirect( $url. $rows->[0]->$method() ); -% } elsif ( exists($opt{'redirect_empty'}) && ! scalar(@$rows) && $total == 0 -% && $type ne 'html-print' -% && $opt{'redirect_empty'} -% && ( ref($opt{'redirect_empty'}) ne 'CODE' -% || &{$opt{'redirect_empty'}}($cgi) ) -% ) { -% my $redirect = $opt{'redirect_empty'}; -% $redirect = &{$redirect}($cgi) if ref($redirect) eq 'CODE'; -% redirect( $redirect ); -% } else { -% if ( $opt{'name_singular'} ) { -% $opt{'name'} = PL($opt{'name_singular'}); -% } -% ( my $xlsname = $opt{'name'} ) =~ s/\W//g; -% if ( $total == 1 ) { -% if ( $opt{'name_singular'} ) { -% $opt{'name'} = $opt{'name_singular'} -% } else { -% #$opt{'name'} =~ s/s$// if $total == 1; -% $opt{'name'} =~ s/((s)e)?s$/$2/ if $total == 1; -% } -% } -% -% if ( $type eq 'html-print' ) { - - <% $opt{nohtmlheader} - ? '' - : include( '/elements/header-popup.html', $opt{'title'} ) - %> - -% } elsif ( $type eq 'select' ) { - - <% $opt{nohtmlheader} - ? '' - : include( '/elements/header-popup.html', $opt{'title'} ) - %> - <% defined($opt{'html_init'}) - ? ( ref($opt{'html_init'}) - ? &{$opt{'html_init'}}() - : $opt{'html_init'} - ) - : '' - %> - -% } else { -% -% my @menubar = (); -% if ( $opt{'menubar'} ) { -% @menubar = @{ $opt{'menubar'} }; -% #} else { -% # @menubar = ( 'Main menu' => $p ); -% } - - <% $opt{nohtmlheader} - ? '' - : include( '/elements/header.html', $opt{'title'}, - include( '/elements/menubar.html', @menubar ) - ) - %> - - <% defined($opt{'html_init'}) - ? ( ref($opt{'html_init'}) - ? &{$opt{'html_init'}}() - : $opt{'html_init'} - ) - : '' - %> - -% } - -% unless ( $total ) { -% unless ( $opt{'disable_nonefound'} ) { - No matching <% $opt{'name'} %> found.<BR> -% } -% } -% -% if ( $total || $opt{'disableable'} ) { #hmm... and there *are* ones to show?? - - <TABLE> - <TR> - - <TD VALIGN="bottom"> - - <FORM> - -% if (! $opt{'disable_total'}) { - <% $total %> total <% $opt{'name'} %> -% } - -% if ( $confmax && $total > $confmax -% && ! $opt{'disable_maxselect'} -% && $type ne 'html-print' ) -% { -% $cgi->delete('maxrecords'); -% $cgi->param('_dummy', 1); - - ( show <SELECT NAME="maxrecords" onChange="window.location = '<% "$self_url?". $cgi->query_string %>;maxrecords=' + this.options[this.selectedIndex].value;"> - -% foreach my $max ( map { $_ * $confmax } qw( 1 5 10 25 ) ) { - <OPTION VALUE="<% $max %>" <% ( $maxrecords == $max ) ? 'SELECTED' : '' %>><% $max %></OPTION> -% } - - </SELECT> per page ) - -% $cgi->param('maxrecords', $maxrecords); -% } - -% if ( defined($opt{'html_posttotal'}) && $type ne 'html-print' ) { - <% ref($opt{'html_posttotal'}) - ? &{$opt{'html_posttotal'}}() - : $opt{'html_posttotal'} - %> -% } - <BR> - -% if ( $opt{'count_addl'} ) { -% my $n=0; -% foreach my $count ( @{$opt{'count_addl'}} ) { -% my $data = $count_arrayref->[++$n]; -% if ( ref($count) ) { - <% &{ $count }( $data ) %> -% } else { - <% sprintf( $count, $data ) %><BR> -% } -% } -% } - </FORM> - - </TD> - -% unless ( $opt{'disable_download'} || $type eq 'html-print' ) { - - <TD ALIGN="right"> - - Download full results<BR> - -% $cgi->param('_type', "$xlsname.xls" ); - as <A HREF="<% "$self_url?". $cgi->query_string %>">Excel spreadsheet</A><BR> - -% $cgi->param('_type', 'csv'); - as <A HREF="<% "$self_url?". $cgi->query_string %>">CSV file</A><BR> - -% if ( defined($opt{xml_elements}) ) { -% $cgi->param('_type', 'xml'); - as <A HREF="<% "$self_url?". $cgi->query_string %>">XML file</A><BR> -% } - -% $cgi->param('_type', 'html-print'); - as <A HREF="<% "$self_url?". $cgi->query_string %>">printable copy</A> - - </TD> -% $cgi->param('_type', "html" ); -% } - - </TR> - <TR> - <TD COLSPAN=2> - -% my $pager = ''; -% unless ( $type eq 'html_print' ) { - - <% $pager = include( '/elements/pager.html', - 'offset' => $offset, - 'num_rows' => scalar(@$rows), - 'total' => $total, - 'maxrecords' => $maxrecords, - ) - %> - - <% defined($opt{'html_form'}) - ? ( ref($opt{'html_form'}) - ? &{$opt{'html_form'}}() - : $opt{'html_form'} - ) - : '' - %> - -% } - - <% include('/elements/table-grid.html') %> - - <TR> -% my $h2 = 0; -% my $colspan = 0; -% my @fields = @{ $opt{'sort_fields'} || $opt{'fields'}}; -% my $order_by = $cgi->param('order_by'); -% foreach my $header ( @{ $opt{header} } ) { -% -% my $field = shift @fields; -% -% $colspan-- if $colspan > 0; -% next if $colspan; -% -% my $label = ref($header) ? $header->{label} : $header; -% unless ( ref($field) || !$field ) { -% if ( $order_by eq $field ) { -% $cgi->param('order_by', "$field DESC"); -% } else { -% $cgi->param('order_by', $field); -% } -% $label = qq(<A HREF="$self_url?). $cgi->query_string. -% qq(">$label</A>); -% } -% -% $colspan = ref($header) ? $header->{colspan} : 0; -% my $rowspan = 1; -% my $style = ''; -% if ( $opt{header2} ) { -% if ( !length($opt{header2}->[$h2]) ) { -% $rowspan = 2; -% splice @{ $opt{header2} }, $h2, 1; -% } else { -% $h2++; -% $style = 'STYLE="border-bottom: none"' -% } -% } - <TH CLASS = "grid" - BGCOLOR = "#cccccc" - ROWSPAN = "<% $rowspan %>" - <% $colspan ? 'COLSPAN = "'.$colspan.'"' : '' %> - <% $style %> - - > - <% $label %> - </TH> -% } - </TR> - -% if ( $opt{header2} ) { - <TR> -% foreach my $header ( @{ $opt{header2} } ) { -% my $label = ref($header) ? $header->{label} : $header; - <TH CLASS="grid" BGCOLOR="#cccccc"> - <FONT SIZE="-1"><% $label %></FONT> - </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 $onclicks = $opt{'link_onclicks'} ? [ @{$opt{'link_onclicks'}} ] : []; -% my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; -% my $colors = $opt{'color'} ? [ @{$opt{'color'}} ] : []; -% my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : []; -% my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : []; -% my $cstyles = $opt{'cell_style'} ? [ @{$opt{'cell_style'}} ] : []; -% -% foreach my $field ( -% -% map { -% if ( ref($_) eq 'ARRAY' ) { -% -% my $tableref = $_; -% -% '<TABLE CLASS="inv" CELLSPACING=0 CELLPADDING=0 WIDTH="100%">'. -% -% join('', map { -% -% my $rowref = $_; -% -% '<tr>'. -% -% join('', map { -% -% my $e = $_; -% -% '<TD '. -% join(' ', map { -% uc($_).'="'. $e->{$_}. '"'; -% } -% grep exists($e->{$_}), -% qw( align bgcolor colspan rowspan -% style valign width ) -% ). -% '>'. -% -% ( $e->{'link'} -% ? '<A HREF="'. $e->{'link'}. '">' -% : '' -% ). -% ( $e->{'size'} -% ? '<FONT SIZE="'.uc($e->{'size'}).'">' -% : '' -% ). -% ( $e->{'data_style'} -% ? '<'. uc($e->{'data_style'}). '>' -% : '' -% ). -% $e->{'data'}. -% ( $e->{'data_style'} -% ? '</'. uc($e->{'data_style'}). '>' -% : '' -% ). -% ( $e->{'size'} ? '</FONT>' : '' ). -% ( $e->{'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; -% my $onclick = shift @$onclicks; -% -% if ( ! $opt{'agent_virt'} -% || ( $null_link && ! $row->agentnum ) -% || grep { $row->agentnum == $_ } -% @link_agentnums -% ) { -% -% $link = &{$link}($row) -% if ref($link) eq 'CODE'; -% -% $onclick = &{$onclick}($row) -% if ref($onclick) eq 'CODE'; -% $onclick = qq( onClick="$onclick") if $onclick; -% -% 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"$onclick>); -% } -% elsif ( $onclick ) { -% $a = qq(<A HREF="javascript:void(0);"$onclick>); -% } -% } -% -% } -% -% 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) ); -% } -% -% my $cstyle = shift @$cstyles; -% $cstyle = &{$cstyle}($row) if ref($cstyle) eq 'CODE'; -% $cstyle = qq(STYLE="$cstyle") -% if $cstyle; - - <TD CLASS="<% $class %>" BGCOLOR="<% $bgcolor %>" <% $align %> <% $cstyle %>><% $font %><% $a %><% $s %><% $field %><% $es %><% $a ? '</A>' : '' %><% $font ? '</FONT>' : '' %></TD> - -% } -% -% } else { -% -% foreach ( @$row ) { - <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $_ %></TD> -% } -% -% } - - </TR> - -% } - -% if ( $opt{'footer'} ) { - - <TR> - -% foreach my $footer ( @{ $opt{'footer'} } ) { -% $footer = &{$footer}() if ref($footer) eq 'CODE'; - <TD CLASS="grid" BGCOLOR="#dddddd" STYLE="border-top: dashed 1px black;"><i><% $footer %></i></TD> -% } - - </TR> -% } - - </TABLE> - - <% $pager %> - - </TD> - </TR> - </TABLE> -% } - -% if ( $type eq 'html-print' ) { -% unless ( $opt{nohtmlheader} ) { - - </BODY></HTML> - -% } -% } else { - - <% defined($opt{'html_foot'}) - ? ( ref($opt{'html_foot'}) - ? &{$opt{'html_foot'}}() - : $opt{'html_foot'} - ) - : '' - %> - - <% $opt{nohtmlheader} - ? '' - : include( '/elements/footer.html' ) - %> - -% } - -% } -<%init> - -my %args = @_; -my $type = $args{'type'}; -my $header = $args{'header'}; -my $rows = $args{'rows'}; -my @link_agentnums = @{ $args{'link_agentnums'} }; -my $null_link = $args{'null_link'}; -my $confmax = $args{'confmax'}; -my $maxrecords = $args{'maxrecords'}; -my $offset = $args{'offset'}; -my %opt = %{ $args{'opt'} }; -my $self_url = $opt{'url'} || $cgi->url('-path_info' => 1, '-full' =>1); - -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; -my $count_arrayref = $count_sth->fetchrow_arrayref; -my $total = $count_arrayref->[0]; - -</%init> diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html deleted file mode 100644 index 8323f55de..000000000 --- a/httemplate/search/elements/search-xls.html +++ /dev/null @@ -1,85 +0,0 @@ -<% $data %> -<%init> - -my %args = @_; -my $type = $args{'type'}; -my $header = $args{'header'}; -my $rows = $args{'rows'}; -my %opt = %{ $args{'opt'} }; - -#http_header('Content-Type' => 'application/excel' ); #eww -#http_header('Content-Type' => 'application/msexcel' ); #alas -#http_header('Content-Type' => 'application/x-msexcel' ); #? - -#http://support.microsoft.com/kb/199841 -http_header('Content-Type' => 'application/vnd.ms-excel' ); -http_header('Content-Disposition' => - 'attachment;filename="'.($opt{'name'} || PL($opt{'name_singular'}) ).'.xls"'); - -#http://support.microsoft.com/kb/812935 -#http://support.microsoft.com/kb/323308 -$HTML::Mason::Commands::r->headers_out->{'Cache-control'} = 'max-age=0'; - -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)); - -$worksheet->protect(); - -my($r,$c) = (0,0); - -my $header_format = $workbook->add_format( - bold => 1, - locked => 1, - bg_color => 55, #22, - bottom => 3, -); - -$worksheet->write($r, $c++, $_, $header_format ) foreach @$header; - -foreach my $row ( @$rows ) { - $r++; - $c = 0; - - if ( $opt{'fields'} ) { - - #my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; - #my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : ''; - #could also translate color, size, style into xls equivalents? - my $formats = $opt{'xls_format'} ? [ @{$opt{'xls_format'}} ] : []; - - foreach my $field ( @{$opt{'fields'}} ) { - - my $format = shift @$formats; - $format = &{$format}($row) if ref($format) eq 'CODE'; - $format ||= {}; - my $xls_format = $workbook->add_format(locked=>0, %$format); - - 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, $xls_format ); - } - } - } else { - $worksheet->write($r, $c++, $row->$field(), $xls_format ); - } - } - - } else { - my $xls_format = $workbook->add_format(locked=>0); - $worksheet->write($r, $c++, $_, $xls_format ) foreach @$row; - } - -} - -$workbook->close();# or die "Error creating .xls file: $!"; - -http_header('Content-Length' => length($data) ); - -</%init> diff --git a/httemplate/search/elements/search-xml.html b/httemplate/search/elements/search-xml.html deleted file mode 100644 index 50b191610..000000000 --- a/httemplate/search/elements/search-xml.html +++ /dev/null @@ -1,89 +0,0 @@ -% foreach my $row ( @$rows ) { -% -% if (&{$beginrow}($row)){ -<% &{$beginrow}($row) %> -% } -% -% foreach my $i ( 0 .. scalar( @{$opt{'fields'}} ) - 1 ) { -% my $field = $opt{'fields'}->[$i]; -% my $value = ''; -% if ( ref($field) eq 'CODE' ) { -% $value = &{$field}($row); -% $value = '(N/A)' #unimplemented -% if ref($value) eq 'ARRAY'; -% } else { -% $value = $row->$field(); -% } -% next unless ($value || !$opt{xml_omit_empty}); -% -<% &{$beginfield}($row, $i) %><% $value |h %><% &{$endfield}($row, $i) %> -% -% } -% -% if (&{$endrow}($row)) { -<% &{$endrow}($row) %> -% } -% -% } -<%init> - -my %args = @_; -my $header = $args{'header'}; -my $rows = $args{'rows'}; -my %opt = %{ $args{'opt'} }; - -http_header('Content-Type' => 'application/XML' ); # So saith RFC 4180 -http_header('Content-Disposition' => - 'attachment;filename="'.($opt{'name'} || PL($opt{'name_singular'}) ).'.xml"'); - -unless ( $opt{'fields'} ) { - foreach my $i ( 0 .. ( $#{ @$rows[0] } ) ) { - $opt{'fields'}->[$i] = sub { my $row = shift; $row->[$i]; }; - } -} - -my $beginrow = sub { return ''; }; -my $endrow = sub { return ''; }; -if ($opt{xml_row_element}) { - $beginrow = sub { my ($row, $index) = @_; - my $value; - if ( ref($opt{xml_row_element}) eq 'CODE' ) { - $value = &{$opt{xml_row_element}}($row); - } else { - $value = $opt{xml_row_element}; - } - return "<$value>"; - }; - $endrow = sub { my ($row, $index) = @_; - my $value; - if ( ref($opt{xml_row_element}) eq 'CODE' ) { - $value = &{$opt{xml_row_element}}($row); - } else { - $value = $opt{xml_row_element}; - } - return "</$value>"; - }; -} -my $beginfield = sub { my ($row, $index) = @_; - my $value; - if ( ref($opt{xml_elements}->[$index]) eq 'CODE' ) { - $value = &{$opt{xml_elements}->[$index]}($row); - } else { - $value = $opt{xml_elements}->[$index]; - } - return "<$value>"; - }; -my $endfield = sub { my ($row, $index) = @_; - my $value; - if ( ref($opt{xml_elements}->[$index]) eq 'CODE' ) { - $value = &{$opt{xml_elements}->[$index]}($row); - } else { - $value = $opt{xml_elements}->[$index]; - } - return "</$value>"; - }; - -$beginfield = sub { return ''; } if $opt{no_field_elements}; #hmm -$endfield = sub { return ''; } if $opt{no_field_elements}; #hmm - -</%init> diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html deleted file mode 100644 index a8e9f086a..000000000 --- a/httemplate/search/elements/search.html +++ /dev/null @@ -1,439 +0,0 @@ -<%doc> - -Example: - - include( 'elements/search.html', - - ### - # required - ### - - 'title' => 'Page title', - - 'name_singular' => 'item', #singular name for the records returned - #OR# # (preferred, will be pluralized automatically) - 'name' => 'items', #plural name for the records returned - # (deprecated, will be singularlized - # simplisticly) - - #literal SQL query string (deprecated?) or qsearch hashref or arrayref - #of qsearch hashrefs for a union of qsearches - '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', - 'order_by' => 'ORDER BY something', - - }, - # "select * from tablename"; - - #required unless 'query' is an SQL query string (shouldn't be...) - 'count_query' => 'SELECT COUNT(*) FROM tablename', - - ### - # recommended / common - ### - - #listref of column labels, <TH> - #recommended unless 'query' is an SQL query string - # (if not specified the database column names will be used) - 'header' => [ '#', - 'Item', - { 'label' => 'Another Item', - - }, - ], - - #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; }, - ], - - #redirect if there's only one item... - # listref of URL base and column name (or method) - # or a coderef that returns the same - 'redirect' => sub { my( $record, $cgi ) = @_; - [ popurl(2).'view/item.html', 'primary_key' ]; - }, - - #redirect if there's no items - # scalar URL or a coderef that returns a URL - 'redirect_empty' => sub { my( $cgi ) = @_; - popurl(2).'view/item.html'; - }, - - ### - # optional - ### - - # some HTML callbacks... - 'menubar' => '', #menubar arrayref - 'html_init' => '', #after the header/menubar and before the pager - 'html_form' => '', #after the pager, right before the results - # (only shown if there are results) - # (use this for any form-opening tag rather than - # html_init, to avoid a nested form) - 'html_foot' => '', #at the bottom - 'html_posttotal' => '', #at the bottom - # (these three can be strings or coderefs) - - 'count_addl' => [], #additional count fields listref of sprintf strings or coderefs - # [ $money_char.'%.2f total paid', ], - - #second (smaller) header line, currently only for HTML - 'header2 => [ '#', - 'Item', - { 'label' => 'Another Item', - - }, - ], - - #listref of column footers - 'footer' => [], - - #disabling things - 'disable_download' => '', # set true to hide the CSV/Excel download links - 'disable_total' => '', # set true to hide the total" - 'disable_maxselect' => '', # set true to disable record/page selection - 'disable_nonefound' => '', # set true to disable the "No matching Xs found" - # message - - #handling "disabled" fields in the records - 'disableable' => 1, # set set to 1 (or column position for "disabled" - # status col) to enable if this table has a "disabled" - # field, to hide disabled records & have - # "show disabled/hide disabled" links - #(can't be used with a literal query) - 'disabled_statuspos' => 3, #optional position (starting from 0) to insert - #a Status column when showing disabled records - #(query needs to be a qsearch hashref and - # header & fields need to be defined) - - #handling agent virtualization - 'agent_virt' => 1, # set true if this search should be - # agent-virtualized - 'agent_null' => 1, # set true to view global records always - 'agent_null_right' => 'Access Right', # optional right to view global - # records - 'agent_null_right_link' => 'Access Right' # optional right to link to - # global records; defaults to - # same as agent_null_right - 'agent_pos' => 3, # optional position (starting from 0) to - # insert an Agent column (query needs to be a - # qsearch hashref and header & fields need to - # be defined) - - # sort, link & display properties for fields - - 'sort_fields' => [], #optional list of field names or SQL expressions for - # sorts - - #listref - each item is the empty string, - # or a listref of link and method name to append, - # or a listref of link and coderef to run and append - # or a coderef that returns such a listref - 'links' => [],` - - #listref - each item is the empty string, - # or a string onClick handler for the corresponding link - # or a coderef that returns string onClick handler - 'link_onclicks' => [], - - #one letter for each column, left/right/center/none - # or pass a listref with full values: [ 'left', 'right', 'center', '' ] - 'align' => 'lrc.', - - #listrefs of ( scalars or coderefs ) - # currently only HTML, maybe eventually Excel too - 'color' => [], - 'size' => [], - 'style' => [], #<B> or <I>, etc. - 'cell_style' => [], #STYLE= attribute of TR, very HTML-specific... - - # Excel-specific listref of ( hashrefs or coderefs ) - # each hashref: http://search.cpan.org/dist/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#Format_methods_and_Format_properties - 'xls_format' => => [], - - ) - -</%doc> -% if ( $type eq 'csv' ) { -% -<% include('search-csv.html', header=>$header, rows=>$rows, opt=>\%opt ) %> -% -% #} elsif ( $type eq 'excel' ) { -% } elsif ( $type =~ /\.xls$/ ) { -% -<% include('search-xls.html', header=>$header, rows=>$rows, opt=>\%opt ) %> -% -% } elsif ( $type eq 'xml' ) { -% -<% include('search-xml.html', rows=>$rows, opt=>\%opt ) %> -% -% } else { # regular HTML -% -<% include('search-html.html', - type => $type, - header => $header, - rows => $rows, - link_agentnums => \@link_agentnums, - null_link => $null_link, - confmax => $confmax, - maxrecords => $maxrecords, - offset => $offset, - opt => \%opt - ) -%> -% -% } -<%init> - -my(%opt) = @_; -#warn join(' / ', map { "$_ => $opt{$_}" } keys %opt ). "\n"; - -my $curuser = $FS::CurrentUser::CurrentUser; - -my $type = $cgi->param('_type') =~ /^(csv|\w*\.xls|xml|select|html(-print)?)$/ - ? $1 : 'html' ; - -my %align = ( - 'l' => 'left', - 'r' => 'right', - 'c' => 'center', - ' ' => '', - '.' => '', -); -$opt{align} = [ map $align{$_}, split(//, $opt{align}) ], - unless !$opt{align} || ref($opt{align}); - -if($type =~ /csv|xls/) { - my $h = $opt{'header'}; - my @del; - my $i = 0; - do { - if( ref($h->[$i]) and exists($h->[$i]->{'nodownload'}) ) { - splice(@{$opt{$_}}, $i, 1) foreach - qw(header footer fields links link_onclicks - align color size style cell_style xls_format); - } - else { - $i++; - } - } while ( exists($h->[$i]) ); -} - -# wtf? -$opt{disable_download} = 0 - if $opt{disable_download} && $curuser->access_right('Configuration download'); - -$opt{disable_download} = 1 - if $opt{really_disable_download}; - -my @link_agentnums = (); -my $null_link = ''; -if ( $opt{'agent_virt'} ) { - - @link_agentnums = $curuser->agentnums; - $null_link = $curuser->access_right( $opt{'agent_null_right_link'} - || $opt{'agent_null_right'} ); - - my $agentnums_sql = $curuser->agentnums_sql( - 'null' => $opt{'agent_null'}, - 'null_right' => $opt{'agent_null_right'}, - 'table' => $opt{'query'}{'table'}, - ); - - $opt{'query'}{'extra_sql'} .= - ( $opt{'query'}{'extra_sql'} =~ /WHERE/i || keys %{$opt{'query'}{'hashref'}} - ? ' AND ' - : ' WHERE ' ). $agentnums_sql; - - $opt{'count_query'} .= - ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ). $agentnums_sql; - - if ( $opt{'agent_pos'} || $opt{'agent_pos'} eq '0' - and scalar($curuser->agentnums) > 1 ) { - #false laziness w/statuspos above - my $pos = $opt{'agent_pos'}; - - foreach my $att (qw( align color size style cell_style xls_format )) { - $opt{$att} ||= [ map '', @{ $opt{'fields'} } ]; - } - - splice @{ $opt{'header'} }, $pos, 0, 'Agent'; - splice @{ $opt{'align'} }, $pos, 0, 'c'; - splice @{ $opt{'style'} }, $pos, 0, ''; - splice @{ $opt{'size'} }, $pos, 0, ''; - splice @{ $opt{'fields'} }, $pos, 0, - sub { $_[0]->agentnum ? $_[0]->agent->agent : '(global)'; }; - splice @{ $opt{'color'} }, $pos, 0, ''; - splice @{ $opt{'links'} }, $pos, 0, '' #[ 'agent link?', 'agentnum' ] - if $opt{'links'}; - splice @{ $opt{'link_onclicks'} }, $pos, 0, '' - if $opt{'link_onclicks'}; - - } - -} - -if ( $opt{'disableable'} ) { - - unless ( $cgi->param('showdisabled') ) { #modify searches - - $opt{'query'}{'hashref'}{'disabled'} = ''; - $opt{'query'}{'extra_sql'} =~ s/^\s*WHERE/ AND/i; - - $opt{'count_query'} .= - ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ). - "( disabled = '' OR disabled IS NULL )"; - - } elsif ( $opt{'disabled_statuspos'} - || $opt{'disabled_statuspos'} eq '0' ) { #add status column - - my $pos = $opt{'disabled_statuspos'}; - - foreach my $att (qw( align style color size )) { - $opt{$att} ||= [ map '', @{ $opt{'fields'} } ]; - } - - splice @{ $opt{'header'} }, $pos, 0, 'Status'; - splice @{ $opt{'align'} }, $pos, 0, 'c'; - splice @{ $opt{'style'} }, $pos, 0, 'b'; - splice @{ $opt{'size'} }, $pos, 0, ''; - splice @{ $opt{'fields'} }, $pos, 0, - sub { shift->disabled ? 'DISABLED' : 'Active'; }; - splice @{ $opt{'color'} }, $pos, 0, - sub { shift->disabled ? 'FF0000' : '00CC00'; }; - splice @{ $opt{'links'} }, $pos, 0, '' - if $opt{'links'}; - splice @{ $opt{'link_onlicks'} }, $pos, 0, '' - if $opt{'link_onlicks'}; - } - - #add show/hide disabled links - my $items = $opt{'name'} || PL($opt{'name_singular'}); - if ( $cgi->param('showdisabled') ) { - $cgi->param('showdisabled', 0); - $opt{'html_posttotal'} .= - '( <a href="'. $cgi->self_url. qq!">hide disabled $items</a> )!; #" - $cgi->param('showdisabled', 1); - } else { - $cgi->param('showdisabled', 1); - $opt{'html_posttotal'} .= - '( <a href="'. $cgi->self_url. qq!">show disabled $items</a> )!; #" - $cgi->param('showdisabled', 0); - } - -} - -my $limit = ''; -my($confmax, $maxrecords, $offset ); - -unless ( $type =~ /^(csv|\w*.xls)$/) { -# 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; #silly vim:/ - } - - if ( $opt{disableable} && ! $cgi->param('showdisabled') ) { - $opt{count_query} .= - ( ( $opt{count_query} =~ /WHERE/i ) ? ' AND ' : ' WHERE ' ). - "( disabled = '' OR disabled IS NULL )"; - } - - unless ( $type eq 'html-print' ) { - - #setup some pagination things if we're in html mode - - my $conf = new FS::Conf; - $confmax = $conf->config('maxsearchrecordsperpage'); - if ( $cgi->param('maxrecords') =~ /^(\d+)$/ ) { - $maxrecords = $1; - } else { - $maxrecords ||= $confmax; - } - - $limit = $maxrecords ? "LIMIT $maxrecords" : ''; - - $offset = $cgi->param('offset') =~ /^(\d+)$/ ? $1 : 0; - $limit .= " OFFSET $offset" if $offset; - - } - -} - -#order by override -my $order_by = ''; -#if ( $cgi->param('order_by') =~ /^([\w\, ]+)$/ ) { -# $order_by = $1; -#} -$order_by = $cgi->param('order_by') if $cgi->param('order_by'); - -# run the query - -my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ]; -my $rows; -if ( ref($opt{query}) ) { - - my @query; - if (ref($opt{query}) eq 'HASH') { - @query = ( $opt{query} ); - - if ( $order_by ) { - if ( $opt{query}->{'order_by'} ) { - if ( $opt{query}->{'order_by'} =~ /^(\s*ORDER\s+BY\s+)?(\S.*)$/is ) { - $opt{query}->{'order_by'} = "ORDER BY $order_by, $2"; - } else { - warn "unparsable query order_by: ". $opt{query}->{'order_by'}; - die "unparsable query order_by: ". $opt{query}->{'order_by'}; - } - } else { - $opt{query}->{'order_by'} = "ORDER BY $order_by"; - } - } - - } elsif (ref($opt{query}) eq 'ARRAY') { - @query = @{ $opt{query} }; - } else { - die "invalid query reference"; - } - - if ( $opt{disableable} && ! $cgi->param('showdisabled') ) { - #%search = ( 'disabled' => '' ); - $opt{'query'}->{'hashref'}->{'disabled'} = ''; - $opt{'query'}->{'extra_sql'} =~ s/^\s*WHERE/ AND/i; - } - - #eval "use FS::$opt{'query'};"; - my @param = qw( select table addl_from hashref extra_sql order_by ); - $rows = [ qsearch( [ map { my $query = $_; - ({ map { $_ => $query->{$_} } @param }); - } - @query - ], - 'order_by' => $opt{order_by}. " ". $limit, - ) - ]; -} 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}; -} - -</%init> |