diff options
Diffstat (limited to 'httemplate/search')
24 files changed, 1513 insertions, 507 deletions
diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 082073339..017e8298f 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -1,61 +1,82 @@ <& elements/search.html, - 'title' => emt('Invoice Search Results'), - 'html_init' => $html_init, - 'menubar' => $menubar, - 'name' => 'invoices', - 'query' => $sql_query, - 'count_query' => $count_query, - 'count_addl' => $count_addl, - 'redirect' => $link, - 'header' => [ emt('Invoice #'), - emt('Balance'), - emt('Net Amount'), - emt('Gross Amount'), - emt('Date'), - FS::UI::Web::cust_header(), - ], - 'fields' => [ - 'display_invnum', - sub { sprintf($money_char.'%.2f', shift->get('owed') ) }, - sub { sprintf($money_char.'%.2f', shift->get('net') ) }, - sub { sprintf($money_char.'%.2f', shift->charged ) }, - sub { time2str('%b %d %Y', shift->_date ) }, - \&FS::UI::Web::cust_fields, - ], - 'sort_fields' => [ - 'COALESCE( agent_invid, invnum )', - FS::cust_bill->owed_sql, - FS::cust_bill->net_sql, - 'charged', - '_date', - ], - 'align' => 'rrrrl'.FS::UI::Web::cust_aligns(), - 'links' => [ - $link, - $link, - $link, - $link, - $link, - ( map { $_ ne 'Cust. Status' ? $clink : '' } - FS::UI::Web::cust_header() - ), - ], - 'color' => [ - '', - '', - '', - '', - '', - FS::UI::Web::cust_colors(), - ], - 'style' => [ - '', - '', - '', - '', - '', - FS::UI::Web::cust_styles(), - ], + 'title' => emt('Invoice Search Results'), + 'html_init' => $html_init, + 'menubar' => $menubar, + 'name' => 'invoices', + 'query' => $sql_query, + 'count_query' => $count_query, + 'count_addl' => $count_addl, + 'redirect' => $link, + 'header' => [ emt('Invoice #'), + emt($invoiced ? 'Charged' : 'Gross Amount'), + emt('Discount'), + emt('Credits'), + emt('Net Amount'), + emt('Balance'), + emt('Date'), + FS::UI::Web::cust_header(), + ], + 'fields' => [ + 'display_invnum', + $invoiced ? 'charged' : 'gross', + 'discounted', + 'credited', + 'net', + 'owed', + sub { time2str('%b %d %Y', shift->_date ) }, + \&FS::UI::Web::cust_fields, + ], + 'sort_fields' => [ + 'COALESCE( agent_invid, invnum )', + $invoiced ? 'charged' : 'gross', + 'discounted', + 'credited', + 'net', + 'owed', + '_date', + ], + 'format' => [ + '', + $money_char.'%.2f', + $money_char.'%.2f', + $money_char.'%.2f', + $money_char.'%.2f', + $money_char.'%.2f', + '', + ], + 'align' => 'rrrrrrl'.FS::UI::Web::cust_aligns(), + 'links' => [ + $link, + $link, + $link, + $link, + $link, + $link, + $link, + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), + ], + 'color' => [ + '', + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], &> <%init> @@ -66,6 +87,9 @@ my( $count_query, $sql_query ); my $count_addl = ''; my %search = (); +# show invoiced amount (charged) instead of gross sales +my $invoiced = $cgi->param('invoiced') ? 1 : 0; + if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill'); @@ -111,7 +135,7 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { #scalars for (qw( agentnum custnum cust_status refnum invnum_min invnum_max - open net newest_percust + open net newest_percust invoiced )) { diff --git a/httemplate/search/cust_bill_pay_pkg.html b/httemplate/search/cust_bill_pay_pkg.html index a6738f32c..5a3be7551 100644 --- a/httemplate/search/cust_bill_pay_pkg.html +++ b/httemplate/search/cust_bill_pay_pkg.html @@ -10,6 +10,7 @@ 'Amount', 'By', '#', + 'Card Type', #payment 'Date', @@ -20,6 +21,7 @@ # line item 'Description', + 'Package class', 'Location', @post_desc_header, @@ -36,7 +38,10 @@ $cust_pay->payby =~ /^(CARD|CHEK)$/ ? $cust_pay->paymask : $cust_pay->payinfo; }, - + sub { my $cust_pay = shift->cust_bill_pay->cust_pay; + $cust_pay->payby =~ /^CARD$/ + ? cardtype($cust_pay->paymask) : ''; + }, sub { time2str('%b %d %Y', shift->get('cust_pay_date') ) }, sub { shift->cust_bill_pay->cust_pay->otaker }, @@ -46,6 +51,7 @@ ? $_[0]->get('pkg') # possibly use override.pkg : $_[0]->get('itemdesc') # but i think this correct }, + 'classname', #package class $location_sub, @post_desc, 'invnum', @@ -55,10 +61,13 @@ 'sort_fields' => [ 'paid', '', #payby + '', #payinfo/paymask + '', #cardtype 'cust_pay_date', '', #'otaker', '', #amount '', #line item description + '', #package class '', #location @post_desc_null, 'invnum', @@ -74,6 +83,8 @@ '', '', '', + '', + '', @post_desc_null, $ilink, $ilink, @@ -81,7 +92,10 @@ FS::UI::Web::cust_header() ), ], - 'align' => 'rcrrlrlllrrcl'. + 'align' => 'rcrlrlrlll', +#original value before cardtype & package were added +#why are there 13 cols? +#'rcrrlrlllrrcl'. $post_desc_align. 'rr'. FS::UI::Web::cust_aligns(), @@ -94,6 +108,8 @@ '', '', '', + '', + '', @post_desc_null, '', '', @@ -108,6 +124,8 @@ '', '', '', + '', + '', @post_desc_null, '', '', @@ -415,6 +433,7 @@ if ( $cgi->param('nottax') ) { $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN pkg_class USING ( classnum ) LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart '; $join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) ' @@ -454,7 +473,8 @@ if ( $cgi->param('nottax') ) { #warn "neither nottax nor istax parameters specified"; #same as before? $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) '; + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN pkg_class USING ( classnum )'; } @@ -477,7 +497,7 @@ my @select = ( 'cust_bill_pay_pkg.*', 'cust_pay._date AS cust_pay_date', 'cust_bill._date', ); -push @select, 'part_pkg.pkg' unless $cgi->param('istax'); +push @select, 'part_pkg.pkg, pkg_class.classname' unless $cgi->param('istax'); push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); @@ -493,10 +513,10 @@ if ( $conf->exists('enable_taxclasses') && ! $cgi->param('istax') ) { push @select, 'part_pkg.taxclass'; # or should this use override? } -warn "$join_cust_bill_pkg - $join_pkg - $join_pay - $join_cust"; +#warn "$join_cust_bill_pkg +# $join_pkg +# $join_pay +# $join_cust"; my $query = { 'table' => 'cust_bill_pay_pkg', diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 91fe4e028..82e87fba9 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -193,8 +193,8 @@ my $conf = new FS::Conf; my $money_char = $conf->config('money_char') || '$'; my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' ); -my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)'); -my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings +my @total = ( 'COUNT(*)' ); +my @total_desc = (); my @peritem = ( 'setup', 'recur' ); my @peritem_desc = ( 'Setup charges', 'Recurring charges' ); @@ -240,7 +240,7 @@ if ( $conf->exists('enable_taxclasses') ) { } # used in several places -my $itemdesc = 'COALESCE(part_fee.itemdesc, part_pkg.pkg, cust_bill_pkg.itemdesc)'; +my $itemdesc = 'COALESCE(cust_bill_pkg.itemdesc, part_fee.itemdesc, part_pkg.pkg, cust_bill_pkg.itemdesc)'; # valid in both the tax and non-tax cases my $join_cust = @@ -315,14 +315,14 @@ if ( $use_override ) { $part_pkg = 'override'; } push @select, "$part_pkg.pkgpart", "$part_pkg.pkg"; +push @select, "($itemdesc) AS itemdesc"; # available in all report modes + push @select, "COALESCE($part_pkg.taxclass, part_fee.taxclass) AS taxclass" if $conf->exists('enable_taxclasses'); # the non-tax case if ( $cgi->param('nottax') ) { - push @select, "($itemdesc) AS itemdesc"; - push @where, '(cust_bill_pkg.pkgnum > 0 OR cust_bill_pkg.feepart IS NOT NULL)'; @@ -516,14 +516,32 @@ if ( $cgi->param('nottax') ) { # setup/recur/usage separation my %charges = map { $_ => 1 } split('', $cgi->param('charges') || 'SRU'); - if ( $charges{R} and $charges{U} ) { + if ( $charges{S} and $charges{R} and $charges{U} ) { + # in this case, show discounts - # default, don't change @peritem or @total - if ( !$charges{S} ) { - push @where, 'cust_bill_pkg.recur > 0'; - $total[1] = "SUM(cust_bill_pkg.recur)"; - $total_desc[0] = "$money_char%.2f recurring"; - } + $join_pkg .= ' JOIN ( + SELECT billpkgnum, COALESCE(SUM(amount), 0) AS discounted + FROM cust_bill_pkg_discount RIGHT JOIN cust_bill_pkg USING (billpkgnum) + GROUP BY billpkgnum + ) AS _discount ON (cust_bill_pkg.billpkgnum = _discount.billpkgnum) + '; + push @select, '_discount.discounted'; + + push @peritem, 'discounted'; + push @peritem_desc, 'Discount'; + push @total, 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur + discounted)', + 'SUM(discounted)', + 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)'; + push @total_desc, "$money_char%.2f gross sales", + "− $money_char%.2f discounted", + "= $money_char%.2f invoiced"; + + } elsif ( $charges{R} and $charges{U} ) { + + # hide rows with no recurring fee, and show the sum of recurring fees only + push @where, 'cust_bill_pkg.recur > 0'; + push @total, "SUM(cust_bill_pkg.recur)"; + push @total_desc, "$money_char%.2f recurring"; } elsif ( $charges{R} and !$charges{U} ) { @@ -532,8 +550,8 @@ if ( $cgi->param('nottax') ) { push @select, "($recur_no_usage) AS recur_no_usage"; $peritem[1] = 'recur_no_usage'; $peritem_desc[1] = 'Recurring charges (excluding usage)'; - $total[1] = "SUM($recur_no_usage)"; - $total_desc[0] = "$money_char%.2f recurring"; + push @total, "SUM($recur_no_usage)"; + push @total_desc, "$money_char%.2f recurring"; if ( !$charges{S} ) { push @where, "($recur_no_usage) > 0"; } @@ -545,8 +563,8 @@ if ( $cgi->param('nottax') ) { # there's already a method named 'usage' $peritem[1] = '_usage'; $peritem_desc[1] = 'Usage charge'; - $total[1] = "SUM($usage)"; - $total_desc[0] = "$money_char%.2f usage charges"; + push @total, "SUM($usage)"; + push @total_desc, "$money_char%.2f usage charges"; if ( !$charges{S} ) { push @where, "($usage) > 0"; } @@ -554,8 +572,8 @@ if ( $cgi->param('nottax') ) { } elsif ( $charges{S} ) { push @where, "cust_bill_pkg.setup > 0"; - $total[1] = "SUM(cust_bill_pkg.setup)"; - $total_desc[0] = "$money_char%.2f setup"; + push @total, "SUM(cust_bill_pkg.setup)"; + push @total_desc, "$money_char%.2f setup"; } # else huh? you have to have SOME charges @@ -578,10 +596,11 @@ if ( $cgi->param('nottax') ) { } } - $total[1] = 'SUM( + push @total, 'SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur) )'; + push @total_desc, "$money_char%.2f total"; } else { # the internal-tax case @@ -591,8 +610,9 @@ if ( $cgi->param('nottax') ) { '; # don't double-count the components of consolidated taxes - $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)'; - $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)'; + @total = ( 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)', + 'SUM(cust_bill_pkg_tax_location.amount)' ); + @total_desc = "$money_char%.2f total"; # package classnum if ( grep { $_ eq 'classnum' } $cgi->param ) { diff --git a/httemplate/search/cust_pkg_summary.cgi b/httemplate/search/cust_pkg_summary.cgi index c0eb69920..76ca8956b 100644 --- a/httemplate/search/cust_pkg_summary.cgi +++ b/httemplate/search/cust_pkg_summary.cgi @@ -56,7 +56,8 @@ foreach my $column (keys %conds) { my $count_query = $h_search->{count_query}; # push a select expression for the total packages with pkgpart=main.pkgpart - push @select, "($count_query AND h_cust_pkg.pkgpart = main.pkgpart) AS $column"; + # (have to quote $column, otherwise mysql thinks before/after are keywords) + push @select, "($count_query AND h_cust_pkg.pkgpart = main.pkgpart) AS \"$column\""; # and query the total packages with pkgpart=any of the main.pkgparts my $total = FS::Record->scalar_sql($count_query . diff --git a/httemplate/search/customer_accounting_summary.html b/httemplate/search/customer_accounting_summary.html index 0dab7cecf..744b313f9 100644 --- a/httemplate/search/customer_accounting_summary.html +++ b/httemplate/search/customer_accounting_summary.html @@ -141,9 +141,20 @@ $title .= 'Customer Accounting Summary Report'; my @items = ('netsales', 'cashflow'); my @params = ( [], [] ); -my $setuprecur = ''; -if ( $cgi->param('setuprecur') ) { - $setuprecur = 1; +my $grossdiscount = $cgi->param('grossdiscount'); +my $setuprecur = $cgi->param('setuprecur'); +if ($setuprecur && $grossdiscount) { + #see blocks below for more details on each option + @items = ('gross', 'discounted', 'receipts', 'gross', 'discounted', 'receipts'); + @params = ( + [ setuprecur => 'setup' ], + [ setuprecur => 'setup' ], + [ setuprecur => 'setup' ], + [ setuprecur => 'recur' ], + [ setuprecur => 'recur' ], + [ setuprecur => 'recur' ], + ); +} elsif ($setuprecur) { # instead of 'cashflow' (payments - refunds), use 'receipts' # (applied payments), because it's divisible into setup and recur. @items = ('netsales', 'receipts', 'netsales', 'receipts'); @@ -153,7 +164,14 @@ if ( $cgi->param('setuprecur') ) { [ setuprecur => 'recur' ], [ setuprecur => 'recur' ], ); +} elsif ($grossdiscount) { + # instead of 'netsales' (invoiced - netcredits) + # use 'gross' (invoiced + discounted) and 'discounted' (sum of discounts on invoices) + @items = ('gross', 'discounted', 'cashflow'); + @params = ( [], [], [] ); } + + my @labels = (); my @cross_params = (); @@ -208,7 +226,7 @@ $cells[0] = [ { header => 1, rowspan => 2, colspan => ($setuprecur ? 4 : 3) }, ($setuprecur ? '' : ()), map { - { header => 1, colspan => 2, value => time2str('%b %Y', $_) }, + { header => 1, colspan => ($grossdiscount ? 3 : 2), value => time2str('%b %Y', $_) }, '' } @{ $data->{speriod} } ]; @@ -218,8 +236,14 @@ $rows[1] = {}; $cells[1] = [ '', ($setuprecur ? '' : ()), map { - ( { header => 1, value => mt('Billed') }, - { header => 1, value => mt('Paid') } + ( ($grossdiscount + ? ( + { header => 1, value => mt('Gross') }, + { header => 1, value => mt('Discount') } + ) + : { header => 1, value => mt('Billed') } + ), + { header => 1, value => mt('Paid') }, ) } (1..$ncols) ]; @@ -256,12 +280,12 @@ foreach my $cust_main (@cust_main) { # correspond to cross_params header => 1 }; } for my $col (0..$ncols-1) { # the month - for my $subcol (0..1) { # the billed/paid axis - my $item = $subrow * 2 + $subcol; + for my $subcol (0..($grossdiscount ? 2 : 1)) { # the billed/paid or gross/discount/paid axis + my $item = $subrow * ($grossdiscount ? 3 : 2) + $subcol; my $value = $data->{data}[$item][$col][$row]; $skip = 0 if abs($value) > 0.005; push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' }; - $total[( ($ncols * $subrow) + $col ) * 2 + $subcol] += $value; + $total[( ($ncols * $subrow) + $col ) * ($grossdiscount ? 3 : 2) + $subcol] += $value; } #subcol } #col push @cells, \@thisrow; @@ -294,8 +318,8 @@ for my $subrow (0..($setuprecur ? 1 : 0)) { { value => $subrow ? mt('recurring') : mt('setup'), header => 1 }; } - for my $col (0..($ncols * 2)-1) { # month and billed/paid axis - my $value = $total[($subrow * $ncols * 2) + $col]; + for my $col (0..($ncols * ($grossdiscount ? 3 : 2))-1) { # month and billed/paid or gross/discount/paid axis + my $value = $total[($subrow * $ncols * ($grossdiscount ? 3 : 2)) + $col]; push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' }; } push @cells, \@thisrow; diff --git a/httemplate/search/elements/cust_main_dayranges.html b/httemplate/search/elements/cust_main_dayranges.html index e5b1f478c..951eff29d 100644 --- a/httemplate/search/elements/cust_main_dayranges.html +++ b/httemplate/search/elements/cust_main_dayranges.html @@ -21,7 +21,7 @@ Example: 'query' => $sql_query, 'count_query' => $count_sql, 'header' => [ - @act_blank, + @act_head, @cust_header, '0-30', '30-60', @@ -248,7 +248,7 @@ if($opt{'payment_links'} && $curuser->access_right('Process payment') && @payby) @payby ); } -my (@act_blank, @act_fields, $act_align, $html_foot); +my (@act_head, @act_blank, @act_fields, $act_align, $html_foot); if (delete($opt{'email_checkboxes'})) { my $email_link = q!var url = toCGIString(); !; $email_link .= q/if (!url) { alert('No customers selected'); return false; }; /; @@ -271,6 +271,7 @@ if (delete($opt{'email_checkboxes'})) { qq!<input type="checkbox" name="custnum" value="$custnum">!; }; $act_align = 'l'; + push @act_head, {nodownload => 1}; push @act_blank, ''; } diff --git a/httemplate/search/elements/cust_pay_batch_top.html b/httemplate/search/elements/cust_pay_batch_top.html index 2dbf62019..626d7c3ea 100644 --- a/httemplate/search/elements/cust_pay_batch_top.html +++ b/httemplate/search/elements/cust_pay_batch_top.html @@ -19,7 +19,7 @@ function format_changed() { % or ( $status eq 'I' and $curuser->access_right('Reprocess batches') ) % or ( $status eq 'R' and $curuser->access_right('Redownload resolved batches') ) % ) { -<FORM ACTION="<%$p%>misc/download-batch.cgi" NAME="download" METHOD="POST"> +<FORM ACTION="<%$p%>misc/download-batch.cgi" NAME="download" METHOD="GET"> <INPUT TYPE="hidden" NAME="batchnum" VALUE="<%$batchnum%>"> % if ( $fixed ) { <INPUT TYPE="hidden" NAME="format" VALUE="<%$fixed%>"> diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index 0e3fce303..5808e5f3e 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -39,7 +39,7 @@ Examples: ) </%doc> -<& search.html, +<& grouped-search.html, 'title' => $title, # XXX: translate 'name_singular' => $name_singular, 'query' => $sql_query, @@ -54,6 +54,17 @@ Examples: 'link_onclicks' => \@link_onclicks, 'color' => \@color, 'style' => \@style, + + 'group_column' => 'payby', + 'group_label' => 'payby_name', + 'subtotal' => { $opt{name_verb} => "sum($amount_field)" }, + 'subtotal_row' => [ 'Subtotal', + sub { sprintf($money, $_[0]->$amount_field) }, + ], + 'total_row' => [ '<B>Total</B>', + sub { sprintf("<B>$money</B>", $_[0]->$amount_field) }, + ], + 'show_combined' => 1, &> <%init> @@ -61,6 +72,9 @@ my %opt = @_; my $curuser = $FS::CurrentUser::CurrentUser; +my $conf = FS::Conf->new; +my $money = ($conf->config('money_char') || '$') . '%.2f'; + die "access denied" unless $curuser->access_right('Financial reports'); @@ -165,16 +179,16 @@ push @header, "\u$name_singular", $align .= 'rr'; push @links, '', ''; push @fields, 'payby_payinfo_pretty', - sub { sprintf('$%.2f', shift->$amount_field() ) }, + sub { sprintf($money, shift->$amount_field() ) }, ; push @link_onclicks, $sub_receipt, ''; -push @sort_fields, '', $amount_field; +push @sort_fields, 'paysort', $amount_field; if ( $unapplied ) { push @header, emt('Unapplied'); $align .= 'r'; push @links, ''; - push @fields, sub { sprintf('$%.2f', shift->unapplied_amount) }; + push @fields, sub { sprintf($money, shift->unapplied_amount) }; push @sort_fields, ''; } @@ -230,6 +244,7 @@ if ( $cgi->param('magic') ) { my @search = (); my @select = ( "$table.*", + "( $table.payby || ' ' || coalesce($table.paymask, $table.payinfo) ) AS paysort", FS::UI::Web::cust_sql_fields(), 'cust_main.custnum AS cust_main_custnum', ); @@ -252,14 +267,10 @@ if ( $cgi->param('magic') ) { $title = $part_referral->referral. " $title"; } - # cust_classnum (false laziness w/ elements/cust_main_dayranges.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg.html cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql) - if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { - my @classnums = grep /^\d*$/, $cgi->param('cust_classnum'); - push @search, 'COALESCE( cust_main.classnum, 0) IN ( '. - join(',', map { $_ || '0' } @classnums ). - ' )' - if @classnums; - } + # cust_classnum - standard matching + push @search, $m->comp('match-classnum', + param => 'cust_classnum', field => 'cust_main.classnum' + ); if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { push @search, "$table.custnum = $1"; @@ -321,7 +332,6 @@ if ( $cgi->param('magic') ) { } elsif ( $cardtype eq 'Discover' ) { - my $conf = new FS::Conf; my $country = $conf->config('countrydefault') || 'US'; $search = diff --git a/httemplate/search/elements/grouped-search.html b/httemplate/search/elements/grouped-search.html new file mode 100644 index 000000000..56fc88d38 --- /dev/null +++ b/httemplate/search/elements/grouped-search.html @@ -0,0 +1,49 @@ +<%doc> + +<& elements/grouped-search/html, + + # required + 'title' => 'Page title', + + 'name_singular' => 'item', + + 'query' => { + 'table' => 'tablename', + 'hashref' => { 'field' => 'value', ... }, # optional + 'select' => '*', + 'addl_from' => '', + 'extra_sql' => '', + #'order_by' is not allowed + }, + 'group_column' => 'classnum', # must be a single field + # 'group_table' => 'mytable', # optional; defaults to the one in the query + 'group_label' => 'classname', + 'subtotal' => { amount => 'sum(amount)', ... } + # The subtotal row will be generated as an FS::* object of the same type + # as all the detail rows. + # The only fields present will be the grouping column and any subtotals + # defined in 'subtotal'. Key is a field name (in the FS::* object), + # value is an SQL aggregate expression. + + # How to display that object: arrayref of literal strings or coderefs + # (the subtotal object will be passed). These will be placed in table + # cells, and can contain HTML. + 'subtotal_row' => + [ 'Total' + sub { sprintf('%.2f', $_[0]->amount) }, + ], + + 'order_by' => '_date', # SQL ordering expression for the detail rows + + 'header' => [ ... ], # per search.html + 'fields' => [ ... ], + +&> +</%doc> +<%init> +my $type = 'html'; +if ($cgi->param('type') =~ /^(html|html-print|xls)$/) { + $type = $1; +} +</%init> +<& "grouped-search/$type", @_ &> diff --git a/httemplate/search/elements/grouped-search/core b/httemplate/search/elements/grouped-search/core new file mode 100644 index 000000000..ffa8cee39 --- /dev/null +++ b/httemplate/search/elements/grouped-search/core @@ -0,0 +1,162 @@ +<%doc> + +my $group_data = $m->comp( 'elements/grouped-search/core', + + 'query' => { + 'table' => 'tablename', + 'hashref' => { 'field' => 'value', ... }, # optional + 'select' => '*', + 'addl_from' => '', + 'extra_sql' => '', + #'order_by' is not allowed + }, + 'group_column' => 'classnum', # must be a single field + # 'group_table' => 'mytable', # optional; defaults to the one in the query + 'group_label' => 'classname', + 'subtotal' => { amount => 'sum(amount)', ... } + # The subtotal row will be generated as an FS::* object of the same type + # as all the detail rows. + # The only fields present will be the grouping column and any subtotals + # defined in 'subtotal'. Key is a field name (in the FS::* object), + # value is an SQL aggregate expression. + + # How to display that object: arrayref of literal strings or coderefs + # (the subtotal object will be passed). These will be placed in table + # cells, and can contain HTML. + 'subtotal_row' => + [ 'Subtotal', sub { sprintf('%.2f', $_[0]->amount) }, ], + + # The subtotal aggregates will also be calculated for the entire data + # set, without grouping. How to display those (if not present, will + # not be displayed); + 'total_row' => + [ 'Total', sub { sprintf('%.2f', $_[0]->amount) }, ], + + 'order_by' => '_date', # SQL ordering expression for the detail rows + + 'header' => [ ... ], # per search.html + 'fields' => [ ... ], +); + +returns: +{ + num => number of groups, + groups => [ group summary objects, + with group key columns, subtotals, and "num_rows" ], + group_labels => [ label strings ], + group_footers => [ formatted subtotal row arrayrefs ], + queries => [ FS::Query objects to evaluate to get detail rows ], +} + +</%doc> +<%shared> +my $conf = FS::Conf->new; +</%shared> +<%init> +my %opt = @_; + +my $base_query = FS::Query->new( $opt{'query'} ); +my $main_table = $base_query->{table}; + +# $opt{subtotal} is a hashref of field names => SQL aggregate expressions. + +my @subtotal_names = sort keys(%{ $opt{'subtotal'} }); +my @subtotal_exprs = map { $opt{'subtotal'}->{$_} } @subtotal_names; + +# qualify the group key to the main table unless otherwise specified +my $group_table = $opt{group_table} || $main_table; +my $group_key = $group_table . '.' . $opt{group_column}; + +my @select = ( + $group_key, # need this to identify groups + 'COUNT(*) as num_rows', # and this for pagination + map { '(' . $subtotal_exprs[$_] . ') AS "' . $subtotal_names[$_] . '"' } + 0 .. (scalar(@subtotal_names) - 1) +); + +my $group_query = $base_query->clone; +$group_query->{order_by} = "GROUP BY $group_key"; +$group_query->{select} = join(',', @select); +# keep table, addl_from, extra_sql, and hashref as they are + +#warn Dumper($group_query); #DEBUG + +# run the group query and create a tab label for each group +my @groups = $group_query->qsearch; + +# also run it with no grouping, and just get overall totals +my $total_query = $base_query->clone; +shift @select; # remove $group_key +$total_query->{select} = join(',', @select); +$total_query->{order_by} = ''; + +my $totals = $total_query->qsearchs; + +my $order_by = ''; +if ( $cgi->param('order_by') =~ /^(\w+\.?\w*(?: DESC)?)$/ ) { + $order_by = $1; +} +if ( $opt{order_by} ) { + $order_by .= ', ' if length($order_by); # user selection takes priority + $order_by .= $opt{order_by}; +} +$order_by = "ORDER BY $order_by " if $order_by; +$base_query->{order_by} = $order_by; + +my $group_label = $opt{group_label} || $opt{group_column}; +my (@group_labels, @group_footers, @queries); +for my $i (0 .. scalar(@groups) - 1) { + my $label = $groups[$i]->$group_label . ' (' . $groups[$i]->num_rows . ')'; + push @group_labels, $label; + + my @footer; + if ($opt{'subtotal_row'}) { + for( my $col = 0; + exists($opt{'subtotal_row'}[$col]) or exists($opt{'header'}[$col]); + $col++ + ) { + my $value = $opt{'subtotal_row'}[$col] || ''; + $value = &$value( $groups[$i] ) if ref($value) eq 'CODE'; + $footer[$col] = $value; + } + } + push @group_footers, \@footer; + + my $detail_query = $base_query->clone; + my $group_key_value = $groups[$i]->get( $opt{group_column} ); + $group_key_value = dbh->quote($group_key_value) + unless looks_like_number($group_key_value); + $detail_query->and_where("$group_key = $group_key_value"); + push @queries, $detail_query; +} + +if ( $opt{show_combined} ) { + # set up group 0 as a combined view + unshift @groups, $totals; + unshift @group_labels, 'All ' . PL($opt{name_singular}) . + ' (' . $totals->num_rows . ')'; + unshift @group_footers, []; # the total footer will suffice + unshift @queries, $base_query->clone; +} + +my @total_footer; +if ($opt{'total_row'}) { + for( my $col = 0; + exists($opt{'total_row'}[$col]) or exists($opt{'header'}[$col]); + $col++ + ) { + my $value = $opt{'total_row'}[$col] || ''; + $value = &$value( $totals ) if ref($value) eq 'CODE'; + $total_footer[$col] = $value; + } +} + +return { + num => scalar(@groups), + groups => \@groups, + group_labels => \@group_labels, + group_footers => \@group_footers, + queries => \@queries, + total_footer => \@total_footer, +}; +</%init> diff --git a/httemplate/search/elements/grouped-search/html b/httemplate/search/elements/grouped-search/html new file mode 100644 index 000000000..df1471a52 --- /dev/null +++ b/httemplate/search/elements/grouped-search/html @@ -0,0 +1,149 @@ +<%shared> +my $conf = FS::Conf->new; +</%shared> +<%init> +my %opt = @_; +$opt{'name'} ||= PL($opt{'name_singular'}); + +my $group_info = $m->comp('core', %opt); + +my $redirect; + +if ( $group_info->{num} == 0 ) { + $redirect = $opt{'redirect_empty'}; + if ($redirect) { + $redirect = &$redirect($cgi) if ref($redirect) eq 'CODE'; + redirect( $redirect ); + } else { # just print this stuff and exit + $m->comp('/elements/header.html', $opt{'title'}); + $m->print('<BR><BR>No matching ' . $opt{'name'} . ' found.<BR>'); + $m->comp('/elements/footer.html'); + $m->abort; + } +} + +# this mode has a concept of "current group" +my $curr_group = 0; +if ($cgi->param('group') =~ /^(\d+)$/) { + $curr_group = $1; +} + +my $group = $group_info->{groups}[$curr_group]; +my $query = $group_info->{queries}[$curr_group]; +my $footer = $group_info->{group_footers}[$curr_group]; +my $total_footer = $group_info->{total_footer} || []; +# pagination +my ($limit, $offset); +my $maxrecords = $conf->config('maxsearchrecordsperpage') || 50; +if ( $cgi->param('maxrecords') =~ /^(\d+)$/ ) { + $maxrecords = $1; +} +if ( $maxrecords ) { + $limit = "LIMIT $maxrecords"; + if ( $cgi->param('offset') =~ /^(\d+)$/ ) { + $offset = $1; + $limit .= " OFFSET $offset"; + } +} +$query->{order_by} .= $limit if $limit; + +#warn Dumper($query); #DEBUG + +# run the query +my @rows = $query->qsearch; + +#warn Dumper(\@rows); #DEBUG + +my $pager = ''; +# show pager if needed +if ( $group->num_rows > scalar(@rows) ) { + $pager = include( '/elements/pager.html', + 'offset' => $offset, + 'num_rows' => scalar(@rows), + 'total' => $group->num_rows, + 'maxrecords' => $maxrecords, + ); +} + +# set up tab bar +my @menubar; +for (my $i = 0; $i < $group_info->{num}; $i++) { + push @menubar, $group_info->{group_labels}[$i], ";group=$i"; +} + +# not enabled yet; if we need this at some point, enable it on a per-report +# basis and then disable it for search/cust_pay.html, because it's redundant +# to see "Check Check #130108", "Credit card Card #401...", etc. + +## if this is the combined view, add a column for the group key +#if ( $curr_group == 0 and $opt{'show_combined'} ) { +# unshift @{$opt{'header'}}, ''; +# unshift @{$opt{'fields'}}, $opt{group_label}; +# unshift @{$opt{'sort_fields'}}, $opt{group_column} if $opt{'sort_fields'}; +# $opt{'align'} = 'c'.$opt{'align'}; +# foreach (qw(header2 links link_onclicks color size style cell_style xls_format)) { +# if ( $opt{$_} ) { +# unshift @{$opt{$_}}, ''; +# } +# } +#} + +</%init> + +<& /elements/header.html, $opt{title} &> + +%# tab bar +% $cgi->delete('group'); +% $cgi->delete('offset'); +% $cgi->delete('type'); +<& /elements/menubar.html, + { newstyle => 1, + url_base => $cgi->self_url, + selected => $group_info->{group_labels}[$curr_group] }, + @menubar +&> + +<DIV CLASS="fstabcontainer"> +%# download links +<P><% emt('Download full results') %><BR> +% $cgi->param('type', 'xls'); +<A HREF="<% $cgi->self_url %>"><% emt('as Excel spreadsheet') %></A><BR> +% $cgi->param('type', 'html-print'); +<A HREF="<% $cgi->self_url %>"><% emt('as printable copy') %></A><BR> +% $cgi->delete('type'); +</P> + +<% $pager %> + +<STYLE> + table.grid { + border-spacing: 0; + } +</STYLE> +<table class="grid"> + <thead> + <& /search/elements/search-html.html:header_row, + 'header' => $opt{'header'}, + 'header2' => $opt{'header2'}, + 'sort_fields' => ($opt{'sort_fields'} || $opt{'fields'}), + &> + </thead> + <tbody> + <& /search/elements/search-html.html:data_rows, + 'rows' => \@rows, + 'opt' => \%opt, + &> + </tbody> + <tfoot> + <& /search/elements/search-html.html:footer_row, row => $footer, opt => \%opt &> +% if ( scalar @$total_footer ) { + <& /search/elements/search-html.html:footer_row, row => $total_footer, opt => \%opt &> +% } + </tfoot> +</table> + +<% $pager %> +</DIV> + +<& /elements/footer.html &> + diff --git a/httemplate/search/elements/grouped-search/html-print b/httemplate/search/elements/grouped-search/html-print new file mode 100644 index 000000000..6d9521ba3 --- /dev/null +++ b/httemplate/search/elements/grouped-search/html-print @@ -0,0 +1,81 @@ +<%doc> + +The "printable" view (all groups on one page). +</%doc> +<%init> +my %opt = @_; + +my $group_info = $m->comp('core', %opt, + 'show_combined' => 0 +); +my $ncols = scalar(@{ $opt{header} }); + +my $total_footer = $group_info->{total_footer} || []; +</%init> +<& /elements/header-popup.html, $opt{title} &> + +<STYLE> +.grouphead { + text-align: left; + font-size: 120%; + padding: 1ex 0 0.5ex 0.1ex; + border-top: 1px solid black; +} +.subtotal td { + background-color: #dddddd; + font-style: italic; + border-top: 1px dashed black; +} +.total td { + background-color: #dddddd; + font-style: italic; + font-weight: bold; + border-top: 2px solid black !important; +} +</STYLE> +<& /elements/table-grid.html &> +<THEAD> +<& /search/elements/search-html.html:header_row, + 'header' => $opt{'header'}, + 'header2' => $opt{'header2'}, + 'sort_fields' => ($opt{'sort_fields'} || $opt{'fields'}), +&> +</THEAD> + +% for (my $curr_group = 0; $curr_group < $group_info->{num}; $curr_group++) { +% my $group = $group_info->{groups}[$curr_group]; +% my $query = $group_info->{queries}[$curr_group]; +% my $footer = $group_info->{group_footers}[$curr_group]; +% my $label = $group_info->{group_labels}[$curr_group]; +% # run the query +% my @rows = $query->qsearch; +% #warn Dumper(\@rows); #DEBUG + +<TBODY> +<TR><TH CLASS="grouphead" COLSPAN="<% $ncols %>"> + <% $label %> +</TH></TR> + +<& /search/elements/search-html.html:data_rows, + rows => \@rows, + opt => \%opt, +&> +</TBODY> +<TBODY CLASS="subtotal"> +<& /search/elements/search-html.html:footer_row, + row => $footer, + opt => \%opt, +&> +</TBODY> +% } # for $curr_group + +% if ( scalar @$total_footer ) { +<TFOOT CLASS="total"> +<& /search/elements/search-html.html:footer_row, + row => $total_footer, + opt => \%opt, +&> +</TFOOT> +% } +</TABLE> +<& /elements/footer.html &> diff --git a/httemplate/search/elements/grouped-search/xls b/httemplate/search/elements/grouped-search/xls new file mode 100644 index 000000000..8540dcf06 --- /dev/null +++ b/httemplate/search/elements/grouped-search/xls @@ -0,0 +1,61 @@ +<%doc> + +Excel spreadsheet view. + +</%doc> +<%init> +my %opt = @_; + +my $group_info = $m->comp('core', %opt); + +# minor false laziness with search-xls.html +my $override = ''; +$override = 'XLSX' if grep { $_->num_rows >= 65536 } + @{ $group_info->{groups} }; + +my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override); + +my $filename = $opt{'name'} || PL($opt{'name_singular'}); +$filename .= $format->{extension}; + +http_header('Content-Type' => $format->{mime_type} ); +http_header('Content-Disposition' => qq!attachment;filename="$filename"! ); +$HTML::Mason::Commands::r->headers_out->{'Cache-control'} = 'max-age=0'; + +my $data = ''; +my $XLS = new IO::Scalar \$data; +my $workbook = $format->{class}->new($XLS) + or die "Error opening Excel file: $!"; + +my $title = $opt{'title'}; +$title =~ s/[\[\]\:\*\?\/\/]//g; +$title = substr($title, 0, 31); + +for (my $curr_group = 0; $curr_group < $group_info->{num}; $curr_group++) { + my $group = $group_info->{groups}[$curr_group]; + my $query = $group_info->{queries}[$curr_group]; + my $footer = $group_info->{group_footers}[$curr_group]; + my $label = $group_info->{group_labels}[$curr_group]; + # run the query + my @rows = $query->qsearch; + #warn Dumper(\@rows); #DEBUG + + # pass arrayrefs to write_row to write multiple rows + $opt{footer} = [ List::MoreUtils::pairwise { [ $a, $b ] } + @$footer, + @{$group_info->{total_footer}} + ]; + $m->comp('/search/elements/search-xls.html:worksheet', + workbook => $workbook, + title => $label, + header => $opt{header}, + opt => \%opt, + rows => \@rows, + ); +} + +$workbook->close(); + +$m->clear_buffer(); +$m->print($data); +</%init> diff --git a/httemplate/search/elements/match-classnum b/httemplate/search/elements/match-classnum new file mode 100644 index 000000000..ed1efd53c --- /dev/null +++ b/httemplate/search/elements/match-classnum @@ -0,0 +1,12 @@ +<%args> +$param # name of the form field containing the classnum +$field => $param # SQL field that must match that form field +</%args> +<%init> +my @values = grep /^(\d+)$/, $cgi->param( $param ); +if (@values) { + return "COALESCE($field, 0) IN (" . join(',', @values) . ')'; +} else { + return; +} +</%init> diff --git a/httemplate/search/elements/report_cust_pay_or_refund.html b/httemplate/search/elements/report_cust_pay_or_refund.html index 9c4ca2761..70727c007 100644 --- a/httemplate/search/elements/report_cust_pay_or_refund.html +++ b/httemplate/search/elements/report_cust_pay_or_refund.html @@ -27,36 +27,14 @@ Examples: </TH> </TR> - <TR> - <TD ALIGN="right"><% ucfirst(PL($name_singular)) %> of type: </TD> - <TD> - <SELECT NAME="payby" SIZE=16 MULTIPLE> - -%# <OPTION VALUE=""><% mt('all') |h %></OPTION> -%# <OPTION VALUE="CARD"><% mt('credit card (all)') |h %></OPTION> - - <OPTION VALUE="CARD-VisaMC" SELECTED><% mt('credit card (Visa/MasterCard)') |h %></OPTION> - <OPTION VALUE="CARD-Amex" SELECTED><% mt('credit card (American Express)') |h %></OPTION> - <OPTION VALUE="CARD-Discover" SELECTED><% mt('credit card (Discover)') |h %></OPTION> - <OPTION VALUE="CARD-Maestro" SELECTED><% mt('credit card (Maestro/Switch/Solo)') |h %></OPTION> - <OPTION VALUE="CARD-Tokenized" SELECTED><% mt('credit card (Tokenized)') |h %></OPTION> - - <OPTION VALUE="CHEK" SELECTED><% mt('electronic check / ACH') |h %></OPTION> - <OPTION VALUE="BILL" SELECTED><% mt('check') |h %></OPTION> - <OPTION VALUE="CASH" SELECTED><% mt('cash') |h %></OPTION> - <OPTION VALUE="PPAL" SELECTED><% mt('Paypal') |h %></OPTION> - <OPTION VALUE="APPL" SELECTED><% mt('Apple Store') |h %></OPTION> - <OPTION VALUE="ANRD" SELECTED><% mt('Android Market') |h %></OPTION> - - <OPTION VALUE="PREP" SELECTED><% mt('prepaid card') |h %></OPTION> - <OPTION VALUE="WIRE" SELECTED><% mt('wire transfer') |h %></OPTION> - <OPTION VALUE="WEST" SELECTED><% mt('Western Union') |h %></OPTION> - <OPTION VALUE="EDI" SELECTED><% mt('Electronic Debit (EDI)') |h %></OPTION> - <OPTION VALUE="MCRD" SELECTED><% mt('manual credit card') |h %></OPTION> - <OPTION VALUE="MCHK" SELECTED><% mt('manual electronic check') |h %></OPTION> - </SELECT> - </TD> - </TR> + <& /elements/tr-select.html, + label => ucfirst(PL($name_singular)) . ' of type:', + field => 'payby', + options => [ keys(\%payby) ], + labels => \%payby, + multiple => 1, + size => 16 + &> <TR> <TD ALIGN="right"><% mt('Check #:') |h %> </TD> @@ -137,8 +115,7 @@ Examples: 'label' => emt('Customer class'), 'field' => 'cust_classnum', 'multiple' => 1, - 'pre_options' => [ '' => emt('(none)') ], - 'all_selected' => 1, + 'pre_options' => [ 0 => emt('(none)') ], &> </TABLE> @@ -189,4 +166,26 @@ my $title = $void ? "Voided $name_singular report" : "\u$name_singular report" ; $table .= '_void' if $void; +tie (my %payby, 'Tie::IxHash', + 'CARD-VisaMC' => 'credit card (Visa/MasterCard)', + 'CARD-Amex' => 'credit card (American Express)', + 'CARD-Discover' => 'credit card (Discover)', + 'CARD-Maestro' => 'credit card (Maestro/Switch/Solo)', + 'CARD-Tokenized' => 'credit card (Tokenized)', + + 'CHEK' => 'electronic check / ACH', + 'BILL' => 'check', + 'CASH' => 'cash', + 'PPAL' => 'Paypal', + 'APPL' => 'Apple Store', + 'ANRD' => 'Android Market', + + 'PREP' => 'prepaid card', + 'WIRE' => 'wire transfer', + 'WEST' => 'Western Union', + 'EDI' => 'Electronic Debit (EDI)', + 'MCRD' => 'manual credit card', + 'MCHK' => 'manual electronic check', +); + </%init> diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html index 10cc95539..12f6c1e04 100644 --- a/httemplate/search/elements/search-html.html +++ b/httemplate/search/elements/search-html.html @@ -181,300 +181,13 @@ % } - <% 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; -% } - -% my $rowstyle = ''; -% if ( $row eq $opt{'footer_data'} ) { -% $rowstyle = ' STYLE="border-top: dashed 1px black; font-style: italic"'; -% $bgcolor = '#dddddd'; -% } - -% my $trid = ''; -% if ( $opt{'link_field' } ) { -% my $link_field = $opt{'link_field'}; -% if ( ref($link_field) eq 'CODE' ) { -% $trid = &{$link_field}($row); -% } else { -% $trid = $row->$link_field(); -% } -% } - <TR ID="<%$trid |h%>"<%$rowstyle%>> - - -% if ( $opt{'fields'} ) { -% -% my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; -% my $onclicks = $opt{'link_onclicks'} ? [ @{$opt{'link_onclicks'}} ] : []; -% my $tooltips = $opt{'tooltips'} ? [ @{$opt{'tooltips'}} ] : []; -% 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->{'onclick'} # don't use with 'link' -% ? '<A HREF="#" onclick="' . -% $e->{'onclick'}.'">' -% : '' -% ). -% ( $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'} || $e->{'onclick'} -% ? '</A>' -% : '' ). -% '</td>'; -% -% } @$rowref ). -% -% '</tr>'; -% } @$tableref ). -% -% '</table>'; -% -% } else { -% $_; -% } -% } -% -% map { -% if ( ref($_) eq 'CODE' ) { -% &{$_}($row); -% } elsif ( ref($row) eq 'ARRAY' and -% $_ =~ /^\d+$/ ) { -% # for the 'straight SQL' case: specify fields -% # by position -% encode_entities($row->[$_]); -% } else { -% encode_entities($row->$_()); -% } -% } -% @{$opt{'fields'}} -% -% ) { -% -%# my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid'; -% my $class = 'grid'; -% -% my $align = $aligns ? shift @$aligns : ''; -% $align = " ALIGN=$align" if $align; -% -% my $a = ''; -% if ( $links ) { -% my $link = shift @$links; -% my $onclick = shift @$onclicks; -% my $tooltip = shift @$tooltips; -% -% 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; -% -% $tooltip = &{$tooltip}($row) -% if ref($tooltip) eq 'CODE'; -% $tooltip = qq! id="a$id" !. -% qq! onmouseover="return overlib(!. -% $m->interp->apply_escapes($tooltip, 'h', 'js_string'). -% qq!, FGCLASS, 'tooltip', REF, 'a$id', !. -% qq!REFC, 'LL', REFP, 'UL')"! if $tooltip; -% -% 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$tooltip>); -% } -% elsif ( $onclick ) { -% $a = qq(<A HREF="javascript:void(0);"$onclick>); -% } -% elsif ( $tooltip ) { -% $a = qq(<A $tooltip>); -% } -% $id++; - -% } -% -% } -% -% 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 %>><% $a %><% $font %><% $s %><% $field %><% $es %><% $font ? '</FONT>' : '' %><% $a ? '</A>' : '' %></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> + <& SELF:data_table, + rows => $rows, + null_link => $null_link, + link_agentnums => \@link_agentnums, + self_url => $self_url, + %opt + &> <% $pager %> @@ -521,14 +234,374 @@ 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; +# must be an arrayref of the row count, followed by any other totals +my $count_arrayref = $args{'totals'}; my $total = $count_arrayref->[0]; -my $id = 0; +# there used to be an option to override this, for highly dubious reasons +my $self_url = $cgi->url('-path_info' => 1, '-full' =>1); + </%init> +<%method data_table> +% my %opt = @_; +% my $rows = delete $opt{rows}; +% my $self_url = delete $opt{self_url}; +<& /elements/table-grid.html &> + +<THEAD> +<& SELF:header_row, + 'header' => $opt{'header'}, + 'header2' => $opt{'header2'}, + 'sort_fields' => ($opt{'sort_fields'} || $opt{'fields'}), +&> +</THEAD> + +<TBODY> +<& SELF:data_rows, rows => $rows, opt => \%opt &> +</TBODY> + +% if ( $opt{'footer'} ) { +<TFOOT> +<& SELF:footer_row, row => $opt{'footer'}, opt => \%opt &> +</TFOOT> +% } +</TABLE> +</%method> +<%method header_row> +<%args> +@sort_fields +@header +@header2 => () +</%args> + <TR> +% my $h2 = 0; +% my $colspan = 0; +% my $order_by = $cgi->param('order_by'); +% my $self_url = $cgi->url('-path_info' => 1, '-full' =>1); +% foreach my $header ( @header ) { +% +% my $field = shift @sort_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 ( @header2 ) { +% if ( !length($header2[$h2]) ) { +% $rowspan = 2; +% splice @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 ( @header2 ) { + <TR> +% foreach my $header ( @header2 ) { +% my $label = ref($header) ? $header->{label} : $header; + <TH CLASS="grid" BGCOLOR="#cccccc"> + <FONT SIZE="-1"><% $label %></FONT> + </TH> +% } + </TR> +% } +</%method> +<%method data_rows> +<%args> +$rows => [] +%opt +</%args> +% my %align = ( +% 'l' => 'left', +% 'r' => 'right', +% 'c' => 'center', +% ' ' => '', +% '.' => '', +% ); +% if ( $opt{align} and !ref($opt{align}) ) { +% $opt{align} = [ map $align{$_}, split(//, $opt{align}) ]; +% } + +% my $i = 0; # for row striping # XXX CSS - nth-child +% my $id = 0; +% foreach my $row ( @$rows ) { +% +% my $rowstyle = ''; +% if ( $row eq $opt{'footer_data'} ) { # XXX CSS - tfoot +% $rowstyle = ' STYLE="border-top: dashed 1px black; font-style: italic background-color=#dddddd"'; +% } +% +% my $trid = ''; +% if ( $opt{'link_field' } ) { +% my $link_field = $opt{'link_field'}; +% if ( ref($link_field) eq 'CODE' ) { +% $trid = &{$link_field}($row); +% } else { +% $trid = $row->$link_field(); +% } +% } + <TR ID="<%$trid |h%>" CLASS="row<% $i % 2 %>"<%$rowstyle%>> + +% if ( $opt{'fields'} ) { +% +% my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : ''; +% my $onclicks = $opt{'link_onclicks'} ? [ @{$opt{'link_onclicks'}} ] : []; +% my $tooltips = $opt{'tooltips'} ? [ @{$opt{'tooltips'}} ] : []; +% 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'}} ] : []; +% my $formats = $opt{'format'} ? [ @{$opt{'format'}} ] : []; +% +% foreach my $field ( +% +% # if the value of the field is an arrayref, then construct a table in +% # the cell. +% # if it's a (non-empty) scalar, and a format has been specified, then +% # format the scalar with that. +% # otherwise, just output the value. +% # XXX we should also do date formats like this +% 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->{'onclick'} # don't use with 'link' +% ? '<A HREF="#" onclick="' . +% $e->{'onclick'}.'">' +% : '' +% ). +% ( $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'} || $e->{'onclick'} +% ? '</A>' +% : '' ). +% '</td>'; +% +% } @$rowref ). +% +% '</tr>'; +% } @$tableref ). +% +% '</table>'; +% +% } else { +% if ( length($_) > 0 and my $format = shift @$formats ) { +% $_ = sprintf($format, $_); +% } +% $_; +% } +% } +% +% # get the value of the field spec: +% # - if the spec is a coderef, evaluate the coderef +% # - if the spec is a string, call that string as a method +% # - if the spec is an integer, get the field in that position +% map { +% if ( ref($_) eq 'CODE' ) { +% &{$_}($row); +% } elsif ( ref($row) eq 'ARRAY' and +% $_ =~ /^\d+$/ ) { +% # for the 'straight SQL' case: specify fields +% # by position +% encode_entities($row->[$_]); +% } else { +% encode_entities($row->$_()); +% } +% } +% @{$opt{'fields'}} +% +% ) { +% +% my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid'; +% my $class = 'grid'; +% +% my $align = $aligns ? shift @$aligns : ''; +% $align = " ALIGN=$align" if $align; +% +% my $a = ''; +% if ( $links ) { +% my $link = shift @$links; +% my $onclick = shift @$onclicks; +% my $tooltip = shift @$tooltips; +% +% if ( ! $opt{'agent_virt'} +% || ( $opt{'null_link'} && ! $row->agentnum ) +% || grep { $row->agentnum == $_ } +% @{ $opt{link_agentnums} } +% ) { +% +% $link = &{$link}($row) +% if ref($link) eq 'CODE'; +% +% $onclick = &{$onclick}($row) +% if ref($onclick) eq 'CODE'; +% $onclick = qq( onClick="$onclick") if $onclick; +% +% $tooltip = &{$tooltip}($row) +% if ref($tooltip) eq 'CODE'; +% $tooltip = qq! id="a$id" !. +% qq! onmouseover="return overlib(!. +% $m->interp->apply_escapes($tooltip, 'h', 'js_string'). +% qq!, FGCLASS, 'tooltip', REF, 'a$id', !. +% qq!REFC, 'LL', REFP, 'UL')"! if $tooltip; +% +% 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$tooltip>); +% } +% elsif ( $onclick ) { +% $a = qq(<A HREF="javascript:void(0);"$onclick>); +% } +% elsif ( $tooltip ) { +% $a = qq(<A $tooltip>); +% } +% $id++; + +% } +% +% } +% +% 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 %>" <% $align %> <% $cstyle %>><% $a %><% $font %><% $s %><% $field %><% $es %><% $font ? '</FONT>' : '' %><% $a ? '</A>' : '' %></TD> + +% } +% +% } else { # not $opt{'fields'} +% +% foreach ( @$row ) { + <TD CLASS="grid"><% $_ %></TD> +% } +% +% } + + </TR> + +% $i++; +% +% } # foreach $row +</%method> +<%method footer_row> +<%args> +$row +%opt +</%args> +%# don't try to respect all the styling options, just the ones that are +%# hard to replicate with CSS +% my %align = ( +% 'l' => 'left', +% 'r' => 'right', +% 'c' => 'center', +% ' ' => '', +% '.' => '', +% ); +% if ( $opt{align} and !ref($opt{align}) ) { +% $opt{align} = [ map $align{$_}, split(//, $opt{align}) ]; +% } +% my @aligns = @{ $opt{align} }; + +<TR> +% foreach my $footer ( @$row ) { +% $footer = &{$footer}() if ref($footer) eq 'CODE'; +% my $align = shift @aligns; +% my $style = ''; +% $style .= "text-align: $align;" if $align; + <TD CLASS="grid" STYLE="<% $style %>"><% $footer %></TD> +% } +</TR> +</%method> + diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html index 8334497d2..c4265e8c8 100644 --- a/httemplate/search/elements/search-xls.html +++ b/httemplate/search/elements/search-xls.html @@ -1,13 +1,10 @@ <%init> my %args = @_; -my $type = $args{'type'}; my $header = $args{'header'}; my $rows = $args{'rows'}; my %opt = %{ $args{'opt'} }; -my $style = $opt{'style'}; - my $override = scalar(@$rows) >= 65536 ? 'XLSX' : ''; my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override); @@ -35,9 +32,38 @@ my $workbook = $format->{class}->new($XLS) my $title = $opt{'title'}; $title =~ s/[\[\]\:\*\?\/\/]//g; $title = substr($title, 0, 31); + +# append a single worksheet +$m->comp( 'SELF:worksheet', + workbook => $workbook, + title => $title, + opt => \%opt, + header => $header, + rows => $rows +); + +$workbook->close();# or die "Error creating .xls file: $!"; + +http_header('Content-Length' => length($data) ); +$m->clear_buffer(); +$m->print($data); + +</%init> +<%method worksheet> +<%args> +$workbook +$title +%opt +$header +$rows +</%args> +<%perl> + my $worksheet = $workbook->add_worksheet($title); -$worksheet->protect(); +#$worksheet->protect(); + +my $style = $opt{style}; my($r,$c) = (0,0); @@ -63,12 +89,29 @@ xl_parse_date_init(); my %bold_format; -my $writer = sub { +my @widths; + +my $writer; +$writer = sub { # Wrapper for $worksheet->write. # Do any massaging of the value/format here. my ($r, $c, $value, $format) = @_; #warn "writer called with format $format\n"; + if ( ref $value eq 'ARRAY' ) { + # imitate the write_row() method: write the array into a column starting + # with $r. + # (currently only used in the footer; to use it anywhere else we'd need + # some way to return the number of rows written) + foreach my $v (@$value) { + $writer->($r, $c, $v, $format); + $r++; + } + return; + } + + my $bold = 0; + my $date = 0; if ( $style->[$c] eq 'b' or $value =~ /<b>/i ) { # the only one in common use $value =~ s[</?b>][]ig; if ( !exists($bold_format{$format}) ) { @@ -77,6 +120,7 @@ my $writer = sub { $bold_format{$format}->set_bold(); } $format = $bold_format{$format}; + $bold = 1; } # convert HTML entities @@ -106,6 +150,7 @@ my $writer = sub { $date_format{$format}->set_num_format('mmm dd yyyy'); } $format = $date_format{$format}; + $date = 1; } else { # String: replace line breaks with newlines @@ -113,6 +158,14 @@ my $writer = sub { } #warn "writing with format $format\n"; $worksheet->write($r, $c, $value, $format); + + # estimate width + # use Font::TTFMetrics; # would work, but we can't redistribute the font... + my $width = length($value); + $width = 11 if $date; + $width *= 1.1 if $bold; + $width += 1; # pad it a little + $widths[$c] = $width if $width > ($widths[$c] || 0); }; $writer->( $r, $c++, $_, $header_format ) foreach @$header; @@ -170,9 +223,9 @@ if ( $opt{'footer'} ) { } } -$workbook->close();# or die "Error creating .xls file: $!"; - -http_header('Content-Length' => length($data) ); -$m->print($data); +for ( my $x = 0; $x < scalar @widths; $x++ ) { + $worksheet->set_column($x, $x, $widths[$x]); +} -</%init> +</%perl> +</%method> diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 64f3a665f..beb017300 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -106,7 +106,8 @@ Example: 'disable_maxselect' => '', # set true to disable record/page selection 'disable_nonefound' => '', # set true to disable the "No matching Xs found" # message - + 'nohtmlheader' => '', # set true to remove the header and menu bar + #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" @@ -200,6 +201,7 @@ Example: confmax => $confmax, maxrecords => $maxrecords, offset => $offset, + totals => $totals, opt => \%opt ) %> @@ -220,6 +222,7 @@ if ( !$curuser->access_right('Download report data') ) { $type = 'html'; } +# split/map aligns here before doing anything else my %align = ( 'l' => 'left', 'r' => 'right', @@ -227,6 +230,7 @@ my %align = ( ' ' => '', '.' => '', ); + $opt{align} = [ map $align{$_}, split(//, $opt{align}) ], unless !$opt{align} || ref($opt{align}); @@ -253,6 +257,12 @@ $opt{disable_download} = 0 $opt{disable_download} = 1 if $opt{really_disable_download}; +# get our queries ready +my $query = $opt{query} or die "query required"; +my $count_query = $opt{count_query} or die "count_query required"; +# there was a default count_query but it hasn't worked in about ten years + +# set up agent restriction my @link_agentnums = (); my $null_link = ''; if ( $opt{'agent_virt'} ) { @@ -264,16 +274,22 @@ if ( $opt{'agent_virt'} ) { my $agentnums_sql = $curuser->agentnums_sql( 'null' => $opt{'agent_null'}, 'null_right' => $opt{'agent_null_right'}, - 'table' => $opt{'query'}{'table'}, + 'table' => $query->{'table'}, ); - $opt{'query'}{'extra_sql'} .= - ( $opt{'query'}{'extra_sql'} =~ /WHERE/i || keys %{$opt{'query'}{'hashref'}} - ? ' AND ' - : ' WHERE ' ). $agentnums_sql; + # this is ridiculous, but we do have searches where $query has constraints + # and $count_query doesn't, or vice versa. + if ( $query->{'extra_sql'} =~ /\bWHERE\b/i or keys %{$query->{hashref}} ) { + $query->{'extra_sql'} .= " AND $agentnums_sql"; + } else { + $query->{'extra_sql'} .= " WHERE $agentnums_sql"; + } - $opt{'count_query'} .= - ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ). $agentnums_sql; + if ( $count_query =~ /\bWHERE\b/i ) { + $count_query .= " AND $agentnums_sql"; + } else { + $count_query .= " WHERE $agentnums_sql"; + } if ( $opt{'agent_pos'} || $opt{'agent_pos'} eq '0' and scalar($curuser->agentnums) > 1 ) { @@ -304,13 +320,13 @@ if ( $opt{'disableable'} ) { unless ( $cgi->param('showdisabled') ) { #modify searches - $opt{'query'}{'hashref'}{'disabled'} = ''; - $opt{'query'}{'extra_sql'} =~ s/^\s*WHERE/ AND/i; + $query->{'hashref'}{'disabled'} = ''; + $query->{'extra_sql'} =~ s/^\s*\bWHERE\b/ AND/i; - my $table = $opt{'query'}{'table'}; + my $table = $query->{'table'}; - $opt{'count_query'} .= - ( $opt{'count_query'} =~ /WHERE/i ? ' AND ' : ' WHERE ' ). + $count_query .= + ( $count_query =~ /\bWHERE\b/i ? ' AND ' : ' WHERE ' ). "( $table.disabled = '' OR $table.disabled IS NULL )"; } elsif ( $opt{'disabled_statuspos'} @@ -357,10 +373,6 @@ my($confmax, $maxrecords, $offset ); unless ( $type =~ /^(csv|xml|\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:/ - } unless ( $type eq 'html-print' ) { @@ -393,39 +405,34 @@ $order_by = $cgi->param('order_by') if $cgi->param('order_by'); my $header = [ map { ref($_) ? $_->{'label'} : $_ } @{$opt{header}} ]; my $rows; -if ( ref($opt{query}) ) { +if ( ref $query ) { my @query; - if (ref($opt{query}) eq 'HASH') { - @query = ( $opt{query} ); + if (ref($query) eq 'HASH') { + @query = $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"; + if ( $query->{'order_by'} ) { + if ( $query->{'order_by'} =~ /^(\s*ORDER\s+BY\s+)?(\S.*)$/is ) { + $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'}; + warn "unparsable query order_by: ". $query->{'order_by'}; + die "unparsable query order_by: ". $query->{'order_by'}; } } else { - $opt{query}->{'order_by'} = "ORDER BY $order_by"; + $query->{'order_by'} = "ORDER BY $order_by"; } } - $opt{query}->{'order_by'} .= " $limit"; + $query->{'order_by'} .= " $limit"; - } elsif (ref($opt{query}) eq 'ARRAY') { - @query = @{ $opt{query} }; + } elsif (ref($query) eq 'ARRAY') { + # do we still use this? it was for the old 477 report. + @query = @{ $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 debug ); $rows = [ qsearch( [ map { my $query = $_; @@ -436,18 +443,25 @@ if ( ref($opt{query}) ) { #'order_by' => $opt{order_by}. " ". $limit, ) ]; -} else { - my $sth = dbh->prepare("$opt{'query'} $limit") - or die "Error preparing $opt{'query'}: ". dbh->errstr; + +} else { # not ref $query; plain SQL (still used as of 07/2015) + + $query .= " $limit"; + my $sth = dbh->prepare($query) + or die "Error preparing $query: ". dbh->errstr; $sth->execute - or die "Error executing $opt{'query'}: ". $sth->errstr; + or die "Error executing $query: ". $sth->errstr; - #can get # of rows without fetching them all? $rows = $sth->fetchall_arrayref; - $header ||= $sth->{NAME}; } +# run the count query to get number of rows and other totals +my $count_sth = dbh->prepare($count_query); +$count_sth->execute + or die "Error executing '$count_query': ".$count_sth->errstr; +my $totals = $count_sth->fetchrow_arrayref; + push @$rows, $opt{'footer_data'} if $opt{'footer_data'}; </%init> diff --git a/httemplate/search/report_cust_bill.html b/httemplate/search/report_cust_bill.html index 3efe830e3..bd40b969b 100644 --- a/httemplate/search/report_cust_bill.html +++ b/httemplate/search/report_cust_bill.html @@ -29,8 +29,7 @@ label => mt('Customer Class'), field => 'cust_classnum', multiple => 1, - 'pre_options' => [ '' => emt('(none)') ], - 'all_selected' => 1, + 'pre_options' => [ 0 => emt('(none)') ], &> </TABLE> @@ -89,7 +88,12 @@ <TR> <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="open" VALUE="1" CHECKED></TD> <TD><% mt('Show only open invoices') |h %></TD> - </TR> +</TR> + +<TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="invoiced" VALUE="1"></TD> + <TD><% emt('Exclude discounts from gross amount billed'), %></TD> +</TR> % unless ( $custnum ) { <TR> diff --git a/httemplate/search/report_customer_accounting_summary.html b/httemplate/search/report_customer_accounting_summary.html index 8206f34ca..cd8622ee3 100755 --- a/httemplate/search/report_customer_accounting_summary.html +++ b/httemplate/search/report_customer_accounting_summary.html @@ -37,6 +37,14 @@ 'value' => 1, &> + <& /elements/tr-checkbox.html, + 'label' => 'Show Gross & Discounted', + 'field' => 'grossdiscount', + 'value' => 1, + 'curr_value' => defined($cgi->param('grossdiscount')) + ? scalar($cgi->param('grossdiscount')) + : 1, #default to on in v4 + &> </TABLE> diff --git a/httemplate/search/report_rt_cust.html b/httemplate/search/report_rt_cust.html new file mode 100644 index 000000000..07d497fc5 --- /dev/null +++ b/httemplate/search/report_rt_cust.html @@ -0,0 +1,40 @@ +<& /elements/header.html, 'Time worked per-customer summary' &> + +<FORM ACTION="rt_cust.html" METHOD="GET"> + +<TABLE> + + <& /elements/tr-select-cust_main-status.html, + 'label' => emt('Status'), + &> + + <& /elements/tr-input-beginning_ending.html &> + +<!-- + <& /elements/tr-select.html, + label => 'Time category:', + field => 'category', + options => [ '', 'development', 'support' ], + option_labels => { '' => 'all' }, + curr_value => 'development', + &> + + <& /elements/tr-select-otaker.html &> +--> + +</TABLE> + +<BR> +<INPUT TYPE="submit" VALUE="Search"> + +</FORM> + +<& /elements/footer.html &> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); + +my $conf = new FS::Conf; + +</%init> diff --git a/httemplate/search/rt_cust.html b/httemplate/search/rt_cust.html new file mode 100644 index 000000000..7c31e976b --- /dev/null +++ b/httemplate/search/rt_cust.html @@ -0,0 +1,174 @@ +<& elements/search.html, + 'title' => 'Time worked per-customer summary', + 'name_singular' => 'customer', + 'query' => $sql_query, + 'count_query' => $count_query, + 'header' => [ FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ), + @extra_headers, + 'Support time', + #'Development time', + 'Unclassified time', + ], + 'fields' => [ + \&FS::UI::Web::cust_fields, + @extra_fields, + $support_time_sub, + $unclass_time_sub, + ], + 'color' => [ FS::UI::Web::cust_colors(), + map '', @extra_fields + ], + 'style' => [ FS::UI::Web::cust_styles(), + map '', @extra_fields + ], + 'align' => [ FS::UI::Web::cust_aligns(), + map '', @extra_fields + ], + 'links' => [ ( map { $_ ne 'Cust. Status' ? $link : '' } + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ) + ), + map '', @extra_fields + ], + +&> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data') +; + +#false laziness w/cust_main.html (we're really only filtering on status for now) + +my %search_hash = (); + +#$search_hash{'query'} = $cgi->keywords; + +#scalars +my @scalars = qw ( + agentnum salesnum status address city county state zip country + invoice_terms + no_censustract with_geocode with_email tax no_tax POST no_POST + custbatch usernum + cancelled_pkgs + cust_fields flattened_pkgs + all_tags + all_pkg_classnums + any_pkg_status +); + +for my $param ( @scalars ) { + $search_hash{$param} = scalar( $cgi->param($param) ) + if length($cgi->param($param)); +} + +#lists +for my $param (qw( classnum refnum tagnum pkg_classnum )) { + $search_hash{$param} = [ $cgi->param($param) ]; +} + +### +# etc +### + +my $sql_query = FS::cust_main::Search->search(\%search_hash); +my $count_query = delete($sql_query->{'count_query'}); +my @extra_headers = @{ delete($sql_query->{'extra_headers'}) }; +my @extra_fields = @{ delete($sql_query->{'extra_fields'}) }; + +my $link = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +#eofalse (cust_main.html) + +#false laziness / cribbed from search/rt_ticket.html + +my $twhere = " + WHERE Transactions.ObjectType = 'RT::Ticket' +"; #AND Transactions.ObjectId = Tickets.Id + +my $transaction_time = " +CASE transactions.type when 'Set' + THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60 + ELSE timetaken*60 +END"; + +$twhere .= " + AND ( ( Transactions.Type = 'Set' + AND Transactions.Field = 'TimeWorked' + AND Transactions.NewValue != Transactions.OldValue ) + OR ( Transactions.Type IN ( 'Create', 'Comment', 'Correspond', 'Touch' ) + AND Transactions.TimeTaken > 0 + ) + )"; + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +# TIMESTAMP is Pg-specific... ? +if ( $beginning > 0 ) { + $beginning = "TIMESTAMP '". time2str('%Y-%m-%d %X', $beginning). "'"; + $twhere .= " AND Transactions.Created >= $beginning "; +} +if ( $ending < 4294967295 ) { + $ending = "TIMESTAMP '". time2str('%Y-%m-%d %X', $ending). "'"; + $twhere .= " AND Transactions.Created <= $ending "; +} + +my $transactions = "FROM Transactions $twhere"; + +#eofalse (rt_ticket.html) + +my $support_time_sub = sub { + my $cust_main = shift; + my $sec = 0; + foreach my $ticket ($cust_main->tickets) { + + my $TimeType = FS::Record->scalar_sql( + "SELECT Content FROM ObjectCustomFieldValues + JOIN CustomFields + ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) + WHERE CustomFields.Name = 'TimeType' + AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket' + AND ObjectCustomFieldValues.Disabled = 0 + AND ObjectId = ". $ticket->{id} + ); + next unless $TimeType eq 'support'; + + $sec += FS::Record->scalar_sql( + "SELECT SUM($transaction_time) $transactions ". + " AND Transactions.ObjectId = ". $ticket->{id} + ); + } + + (($sec < 0) ? '-' : '' ). int(abs($sec)/3600)."h".sprintf("%02d",(abs($sec)%3600)/60)."m"; + +}; + +my $unclass_time_sub = sub { + my $cust_main = shift; + my $sec = 0; + foreach my $ticket ($cust_main->tickets) { + + my $TimeType = FS::Record->scalar_sql( + "SELECT Content FROM ObjectCustomFieldValues + JOIN CustomFields + ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) + WHERE CustomFields.Name = 'TimeType' + AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket' + AND ObjectCustomFieldValues.Disabled = 0 + AND ObjectId = ". $ticket->{id} + ); + next unless $TimeType eq ''; + + $sec += FS::Record->scalar_sql( + "SELECT SUM($transaction_time) $transactions ". + " AND Transactions.ObjectId = ". $ticket->{id} + ); + } + + (($sec < 0) ? '-' : '' ). int(abs($sec)/3600)."h".sprintf("%02d",(abs($sec)%3600)/60)."m"; + +}; + +</%init> diff --git a/httemplate/search/sql.html b/httemplate/search/sql.html index 71aa00671..54d6c2d79 100644 --- a/httemplate/search/sql.html +++ b/httemplate/search/sql.html @@ -2,7 +2,7 @@ 'title' => 'Query Results', 'name' => 'rows', 'query' => "SELECT $sql", - + 'count_query' => $count, &> <%init> @@ -12,4 +12,7 @@ die "access denied" my $sql = $cgi->param('sql') or errorpage('Empty query'); $sql =~ s/;+\s*$//; #remove trailing ; +my $count = $sql; +$count =~ s/.* FROM /SELECT COUNT(*) FROM /i; + </%init> diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index b9e5a7cc9..58764f881 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -148,10 +148,34 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { if ( $sortby eq 'seconds' ) { my $tot_time = 0; push @header, emt('Time'); - push @fields, sub { my $svc_acct = shift; - $tot_time += $svc_acct->seconds; - format_time($svc_acct->seconds); - }; + + if ( $conf->exists('svc_acct-display_paid_time_remaining') ) { + push @fields, sub { my $svc_acct = shift; + my $seconds = $svc_acct->seconds; + my $cust_pkg = $svc_acct->cust_svc->cust_pkg; + my $part_pkg = $cust_pkg->part_pkg; + + $tot_time += $svc_acct->seconds; + + $timepermonth = $part_pkg->option('seconds'); + $timepermonth = $timepermonth / $part_pkg->freq + if $part_pkg->freq =~ /^\d+$/ && $part_pkg->freq != 0; + my $recur = $part_pkg->base_recur($cust_pkg); + + return format_time($seconds) + unless $timepermonth && $recur; + + format_time($seconds). + sprintf(' (%.2fx monthly)', $seconds / $timepermonth ); + + }; + } else { + push @fields, sub { my $svc_acct = shift; + $tot_time += $svc_acct->seconds; + format_time($svc_acct->seconds); + }; + } + push @links, ''; $align .= 'r'; push @color, ''; |