diff options
Diffstat (limited to 'httemplate')
| -rw-r--r-- | httemplate/browse/deploy_zone.html | 2 | ||||
| -rw-r--r-- | httemplate/edit/deploy_zone-fixed.html | 1 | ||||
| -rw-r--r-- | httemplate/edit/deploy_zone-mobile.html | 1 | ||||
| -rw-r--r-- | httemplate/elements/select-table.html | 3 | ||||
| -rw-r--r-- | httemplate/elements/select.html | 2 | ||||
| -rw-r--r-- | httemplate/graph/cust_bill_pkg.cgi | 25 | ||||
| -rw-r--r-- | httemplate/graph/cust_pkg.cgi | 134 | ||||
| -rw-r--r-- | httemplate/graph/report_cust_pkg.html | 57 | ||||
| -rw-r--r-- | httemplate/misc/process/payment.cgi | 2 | ||||
| -rw-r--r-- | httemplate/search/477.html | 30 | ||||
| -rwxr-xr-x | httemplate/search/cust_pkg.cgi | 5 | ||||
| -rwxr-xr-x | httemplate/search/report_477.html | 6 | ||||
| -rw-r--r-- | httemplate/search/report_rt_ticket.html | 20 | ||||
| -rw-r--r-- | httemplate/search/report_rt_transaction.html | 15 | ||||
| -rw-r--r-- | httemplate/search/report_timeworked.html | 6 | ||||
| -rw-r--r-- | httemplate/search/rt_ticket.html | 100 | ||||
| -rw-r--r-- | httemplate/search/rt_transaction.html | 104 | ||||
| -rw-r--r-- | httemplate/search/timeworked.html | 31 |
18 files changed, 351 insertions, 193 deletions
diff --git a/httemplate/browse/deploy_zone.html b/httemplate/browse/deploy_zone.html index ddfbde43d..3bd9d07dd 100644 --- a/httemplate/browse/deploy_zone.html +++ b/httemplate/browse/deploy_zone.html @@ -52,7 +52,7 @@ '(adv_speed_down, adv_speed_up)', '(cir_speed_down, cir_speed_up)', ], - links => [ '', $link_fixed, ], + links => [ $link_fixed, $link_fixed, ], align => 'clllllr', nohtmlheader => 1, disable_maxselect => 1, diff --git a/httemplate/edit/deploy_zone-fixed.html b/httemplate/edit/deploy_zone-fixed.html index 1a79500ff..fb26c4958 100644 --- a/httemplate/edit/deploy_zone-fixed.html +++ b/httemplate/edit/deploy_zone-fixed.html @@ -2,6 +2,7 @@ 'name_singular' => 'deployment zone', 'table' => 'deploy_zone', 'post_url' => popurl(1).'process/deploy_zone-fixed.html', + 'viewall_dir' => 'browse', 'labels' => { 'description' => 'Description', 'agentnum' => 'Agent', diff --git a/httemplate/edit/deploy_zone-mobile.html b/httemplate/edit/deploy_zone-mobile.html index 8e985b1c9..d049cb018 100644 --- a/httemplate/edit/deploy_zone-mobile.html +++ b/httemplate/edit/deploy_zone-mobile.html @@ -2,6 +2,7 @@ 'name_singular' => 'deployment zone', 'table' => 'deploy_zone', 'post_url' => popurl(1).'process/deploy_zone-mobile.html', + 'viewall_dir' => 'browse', 'labels' => { 'description' => 'Description', 'agentnum' => 'Agent', diff --git a/httemplate/elements/select-table.html b/httemplate/elements/select-table.html index 9f26a3591..e73638801 100644 --- a/httemplate/elements/select-table.html +++ b/httemplate/elements/select-table.html @@ -70,6 +70,7 @@ Example: NAME = "<% $opt{'element_name'} || $opt{'field'} || $key %>" ID = "<% $opt{'id'} || $key %>" <% $onchange %> + <% $size %> <% $opt{'element_etc'} %> > @@ -212,4 +213,6 @@ unless ( !ref($value) && $value < 1 # !$value #ignore negatives too my @pre_options = $opt{pre_options} ? @{ $opt{pre_options} } : (); my @post_options = $opt{post_options} ? @{ $opt{post_options} } : (); +my $size = $opt{'size'} ? 'SIZE=' . $opt{'size'} : ''; + </%init> diff --git a/httemplate/elements/select.html b/httemplate/elements/select.html index efcf27b0e..67ef51418 100644 --- a/httemplate/elements/select.html +++ b/httemplate/elements/select.html @@ -4,6 +4,7 @@ ID = "<% $opt{id} %>" previousValue = "<% $curr_value %>" previousText = "<% $labels->{$curr_value} || $curr_value %>" + <% $size %> <% $style %> <% $opt{disabled} %> <% $onchange %> @@ -71,5 +72,6 @@ my @style = ref($opt{'style'}) my $style = scalar(@style) ? 'STYLE="'. join(';', @style). '"' : ''; +my $size = $opt{'size'} ? 'SIZE='.$opt{'size'} : ''; </%init> diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi index 151b29504..ea7fee932 100644 --- a/httemplate/graph/cust_bill_pkg.cgi +++ b/httemplate/graph/cust_bill_pkg.cgi @@ -1,7 +1,7 @@ <% include('elements/monthly.html', #Dumper( 'title' => $title, - 'graph_type' => 'Mountain', + 'graph_type' => $graph_type, 'items' => \@items, 'params' => \@params, 'labels' => \@labels, @@ -10,7 +10,8 @@ 'links' => \@links, 'no_graph' => \@no_graph, 'remove_empty' => 1, - 'bottom_total' => 1, + 'bottom_total' => $show_total, + 'nototal' => !$show_total, 'bottom_link' => $bottom_link, 'agentnum' => $agentnum, 'cust_classnum'=> \@cust_classnums, @@ -30,6 +31,15 @@ my $use_override = $cgi->param('use_override') ? 1 : 0; my $average_per_cust_pkg = $cgi->param('average_per_cust_pkg') ? 1 : 0; my $distribute = $cgi->param('distribute') ? 1 : 0; +my $show_total = 1; +my $graph_type = 'Mountain'; + +if ( $average_per_cust_pkg ) { + # then the rows are not additive + $show_total = 0; + $graph_type = 'LinesPoints'; +} + my %charge_labels = ( 'SR' => 'setup + recurring', 'RU' => 'recurring', @@ -356,6 +366,17 @@ foreach my $agent ( $all_agent || $sel_agent || $FS::CurrentUser::CurrentUser->a } +# may be useful at some point... +#if ( $average_per_cust_pkg ) { +# @items = map { ('cust_bill_pkg', 'cust_bill_pkg_count_pkgnum') } @items; +# @labels = map { $_, "Packages" } @labels; +# @params = map { $_, $_ } @params; +# @links = map { $_, $_ } @links; +# @colors = map { $_, $_ } @colors; +# @no_graph = map { $_, 1 } @no_graph; +#} +# + #use Data::Dumper; if ( $cgi->param('debug') == 1 ) { $FS::Report::Table::DEBUG = 1; diff --git a/httemplate/graph/cust_pkg.cgi b/httemplate/graph/cust_pkg.cgi index 21ce07d21..cdd95e10a 100644 --- a/httemplate/graph/cust_pkg.cgi +++ b/httemplate/graph/cust_pkg.cgi @@ -1,20 +1,22 @@ -<% include('elements/monthly.html', - 'title' => $agentname. 'Package Churn', - 'items' => \@items, - 'labels' => \%label, - 'graph_labels' => \%graph_label, - 'colors' => \%color, - 'links' => \%link, - 'agentnum' => $agentnum, - 'sprintf' => '%u', - 'disable_money' => 1, - ) -%> +<& elements/monthly.html, + 'title' => $agentname. 'Package Churn', + 'items' => \@items, + 'labels' => \@labels, + 'graph_labels' => \@labels, + 'colors' => \@colors, + 'links' => \@links, + 'params' => \@params, + 'agentnum' => $agentnum, + 'sprintf' => '%u', + 'disable_money' => 1, + 'remove_empty' => (scalar(@group_keys) > 1 ? 1 : 0), +&> <%init> #XXX use a different ACL for package churn? +my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + unless $curuser->access_right('Financial reports'); #false laziness w/money_time.cgi, cust_bill_pkg.cgi @@ -28,24 +30,23 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { my $agentname = $agent ? $agent->agent.' ' : ''; -my @items = qw( setup_pkg susp_pkg cancel_pkg ); +my @base_items = qw( setup_pkg susp_pkg cancel_pkg ); -my %label = ( +my %base_labels = ( 'setup_pkg' => 'New orders', 'susp_pkg' => 'Suspensions', # 'unsusp' => 'Unsuspensions', 'cancel_pkg' => 'Cancellations', ); -my %graph_label = %label; -my %color = ( +my %base_colors = ( 'setup_pkg' => '00cc00', #green 'susp_pkg' => 'ff9900', #yellow #'unsusp' => '', #light green? 'cancel_pkg' => 'cc0000', #red ? 'ff0000' ); -my %link = ( +my %base_links = ( 'setup_pkg' => { 'link' => "${p}search/cust_pkg.cgi?agentnum=$agentnum;", 'fromparam' => 'setup_begin', 'toparam' => 'setup_end', @@ -60,4 +61,101 @@ my %link = ( }, ); +my %filter_params = ( + # not agentnum, that's elsewhere + 'refnum' => [ $cgi->param('refnum') ], + 'classnum' => [ $cgi->param('classnum') ], + 'towernum' => [ $cgi->param('towernum') ], +); +if ( $cgi->param('zip') =~ /^(\w+)/ ) { + $filter_params{zip} = $1; +} +foreach my $link (values %base_links) { + foreach my $key (keys(%filter_params)) { + my $value = $filter_params{$key}; + if (ref($value)) { + $value = join(',', @$value); + } + $link->{'link'} .= "$key=$value;" if length($value); + } +} + + +# In order to keep this from being the same trainwreck as cust_bill_pkg.cgi, +# we allow ONE breakdown axis, besides the setup/susp/cancel inherent in +# the report. + +my $breakdown = $cgi->param('breakdown_by'); +my ($name_col, $table); +if ($breakdown eq 'classnum') { + $table = 'pkg_class'; + $name_col = 'classname'; +} elsif ($breakdown eq 'refnum') { + $table = 'part_referral'; + $name_col = 'referral'; +} elsif ($breakdown eq 'towernum') { + $table = 'tower'; + $name_col = 'towername'; +} elsif ($breakdown) { + die "unknown breakdown column '$breakdown'\n"; +} + +my @group_keys; +my @group_labels; +if ( $table ) { + my @groups; + if ( $cgi->param($breakdown) ) { + foreach my $key ($cgi->param($breakdown)) { + next if $key =~ /\D/; + push @groups, qsearch( $table, { $breakdown => $key }); + } + } else { + @groups = qsearch( $table ); + } + foreach (@groups) { + push @group_keys, $_->get($breakdown); + push @group_labels, $_->get($name_col); + } +} + +my (@items, @labels, @colors, @links, @params); +if (scalar(@group_keys) > 1) { + my $hue = 180; + foreach my $key (@group_keys) { + # this gives a decent level of contrast as long as there aren't too many + # result sets + my $scheme = Color::Scheme->new + ->scheme('triade') + ->from_hue($hue) + ->distance(0.5); + my $label = shift @group_labels; + my $i = 0; # item index + foreach (@base_items) { + # append the item + push @items, $_; + # and its parameters + push @params, [ + %filter_params, + $breakdown => $key + ]; + # and a label prefixed with the group label + push @labels, "$label - $base_labels{$_}"; + # and colors (?!) + push @colors, $scheme->colorset->[$i]->[1]; + # and links... + my %this_link = %{ $base_links{$_} }; + $this_link{link} .= "$breakdown=$key;"; + push @links, \%this_link; + $i++; + } #foreach (@base_items + $hue += 35; + } # foreach @group_keys +} else { + @items = @base_items; + @labels = @base_labels{@base_items}; + @colors = @base_colors{@base_items}; + @links = @base_links{@base_items}; + @params = map { [ %filter_params ] } @base_items; +} + </%init> diff --git a/httemplate/graph/report_cust_pkg.html b/httemplate/graph/report_cust_pkg.html index 22ccd5def..1425ff089 100644 --- a/httemplate/graph/report_cust_pkg.html +++ b/httemplate/graph/report_cust_pkg.html @@ -2,16 +2,57 @@ <FORM ACTION="cust_pkg.cgi" METHOD="GET"> -<TABLE> +<TABLE BGCOLOR="#cccccc" CELLSPACING=0> -<% include('/elements/tr-select-from_to.html' ) %> +<& /elements/tr-select-from_to.html &> -<% include('/elements/tr-select-agent.html', - 'curr_value' => scalar($cgi->param('agentnum')), - 'label' => 'For agent: ', - 'disable_empty' => 0, - ) -%> +<& /elements/tr-select-agent.html, + 'curr_value' => scalar($cgi->param('agentnum')), + 'label' => 'For agent: ', + 'disable_empty' => 0, +&> + +<& /elements/tr-select-pkg_class.html, + 'multiple' => 1, + 'pre_options' => [ '0' => '(empty class)' ], + 'disable_empty' => 1, +&> + +<& /elements/tr-select-part_referral.html, + 'multiple' => 1, + 'disable_empty' => 1, +&> + +<& /elements/tr-select-table.html, + 'label' => 'Tower', + 'table' => 'tower', + 'field' => 'towernum', + 'name_col' => 'towername', + 'multiple' => 1, + 'pre_options' => [ 0 => '(none)' ], + 'size' => 8, + 'hashref' => { disabled => '' }, +&> + +<& /elements/tr-input-text.html, + 'field' => 'zip', + 'label' => 'Zip', +&> + +<& /elements/tablebreak-tr-title.html, + 'value' => mt('Display options') +&> + +<& /elements/tr-select.html, + 'field' => 'breakdown_by', + 'label' => 'Breakdown by: ', + 'options' => [ '', 'classnum', 'refnum', 'towernum' ], + 'labels' => { '' => '(none)', + 'classnum' => 'Package class', + 'refnum' => 'Advertising source', + 'towernum' => 'Tower', + }, +&> </TABLE> diff --git a/httemplate/misc/process/payment.cgi b/httemplate/misc/process/payment.cgi index 981614e76..27b818660 100644 --- a/httemplate/misc/process/payment.cgi +++ b/httemplate/misc/process/payment.cgi @@ -122,6 +122,8 @@ if ( $payby eq 'CHEK' ) { or errorpage("CVV2 (CVC2/CID) is three digits."); $paycvv = $1; } + }elsif( $conf->exists('backoffice-require_cvv') ){ + errorpage("CVV2 is required"); } } diff --git a/httemplate/search/477.html b/httemplate/search/477.html index fb85f1e09..244bfa1df 100644 --- a/httemplate/search/477.html +++ b/httemplate/search/477.html @@ -49,13 +49,23 @@ a.download { <thead> <& $header &> </thead> +% my $rownum = 0; % foreach my $row (@$data) { <tr> +% my $first = 1; % foreach my $item (@$row) { - <td><% $item %></td> + <td> +% if ($first and $part_link{$partname}) { + <a href="<% $part_link{$partname} . "477rownum=$rownum" %>"><% $item || '(empty)' %></a> +% $first = 0; +% } else { + <% $item %> % } + </td> +% } #foreach $item </tr> -% } +% $rownum++; +% } #foreach $row </table> % } # foreach $partname <& /elements/footer.html &> @@ -64,6 +74,7 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List packages'); my %parts; +my %part_link; # load from cache if possible my $session; if ( $cgi->param('session') =~ /^(\d+)$/ ) { @@ -80,11 +91,22 @@ if ($cgi->param('agentnum') =~ /^(\d+)$/ ) { } my $date = parse_datetime($cgi->param('date')) || time; my @partnames = grep /^\w+$/, $cgi->param('parts'); +my $ignore_quantity = ($cgi->param('ignore_quantity') ? 1 : 0); + foreach my $partname (@partnames) { $parts{$partname} ||= FS::Report::FCC_477->report( $partname, - date => $date, - agentnum => $agentnum + date => $date, + agentnum => $agentnum, + ignore_quantity => $ignore_quantity, ); + my $detail_table = FS::Report::FCC_477->part_table($partname); + if ($detail_table eq 'cust_pkg') { + my $link = popurl(1).'cust_pkg.cgi?477part='.$partname.";date=$date;"; + if ($agentnum) { + $link .= "agentnum=$agentnum;"; + } + $part_link{$partname} = $link; + } # don't include detail links to deploy_blocks, that's pointless } $m->cache->set($session, \%parts, '1h'); diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index 54bfa00bf..c88b3a1d5 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -157,14 +157,15 @@ $search_hash{'query'} = $cgi->keywords; #scalars for (qw( agentnum cust_status cust_main_salesnum salesnum custnum magic status - custom cust_fields pkgbatch + custom cust_fields pkgbatch zip + 477part 477rownum date )) { $search_hash{$_} = $cgi->param($_) if length($cgi->param($_)); } #arrays -for my $param (qw( pkgpart classnum )) { +for my $param (qw( pkgpart classnum refnum towernum )) { $search_hash{$param} = [ $cgi->param($param) ] if grep { $_ eq $param } $cgi->param; } diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html index cbbd5d902..38073ad33 100755 --- a/httemplate/search/report_477.html +++ b/httemplate/search/report_477.html @@ -40,6 +40,12 @@ 'labels' => $part_titles, 'options' => [ keys %$part_titles ] &> + + <& /elements/tr-checkbox.html, + 'label' => 'Ignore package quantities', + 'field' => 'ignore_quantity', + 'value' => 1, + &> </TABLE> <BR> diff --git a/httemplate/search/report_rt_ticket.html b/httemplate/search/report_rt_ticket.html index a4ceaa6a4..67fc208b6 100644 --- a/httemplate/search/report_rt_ticket.html +++ b/httemplate/search/report_rt_ticket.html @@ -6,15 +6,13 @@ <% include ( '/elements/tr-input-beginning_ending.html' ) %> - <& /elements/tr-td-label.html, label => 'Time category:' &> - <TD> - <& /elements/select-rt-customfield.html, - name => 'cfname', - lookuptype => 'RT::Transaction', - valuetype => 'TimeValue', - empty_label => 'Worked', + <& /elements/tr-select.html, + label => 'Time category:', + field => 'category', + options => [ '', 'development', 'support' ], + option_labels => { '' => 'all' }, + curr_value => 'development', &> - </TD></TR> <% include ( '/elements/tr-select-otaker.html' ) %> @@ -71,10 +69,4 @@ $CFs->Limit(FIELD => 'LookupType', $CFs->Limit(FIELD => 'Type', VALUE => 'TimeValue'); -my @time_fields = ('', 'Worked'); -while (my $CF = $CFs->Next) { - push @time_fields, $CF->Name, ($CF->Description || $CF->Name); -} - - </%init> diff --git a/httemplate/search/report_rt_transaction.html b/httemplate/search/report_rt_transaction.html index b8454d968..91dc4a0cf 100644 --- a/httemplate/search/report_rt_transaction.html +++ b/httemplate/search/report_rt_transaction.html @@ -6,16 +6,13 @@ <% include ( '/elements/tr-input-beginning_ending.html' ) %> - <& /elements/tr-td-label.html, label => 'Time category:' &> - <TD> - <& /elements/select-rt-customfield.html, - name => 'cfname', - lookuptype => 'RT::Transaction', - valuetype => 'TimeValue', - empty_label => 'Worked', + <& /elements/tr-select.html, + label => 'Time category:', + field => 'category', + options => [ '', 'development', 'support' ], + option_labels => { '' => 'all' }, + curr_value => 'development', &> - </TD></TR> - <% include ( '/elements/tr-select-otaker.html' ) %> diff --git a/httemplate/search/report_timeworked.html b/httemplate/search/report_timeworked.html index 492e738ad..d2d8173d7 100644 --- a/httemplate/search/report_timeworked.html +++ b/httemplate/search/report_timeworked.html @@ -12,6 +12,12 @@ <% include ('/elements/tr-input-beginning_ending.html') %> + <& /elements/tr-select.html, + label => 'Time category:', + field => 'category', + options => [ 'development', 'support' ] + &> + </TABLE> <BR> diff --git a/httemplate/search/rt_ticket.html b/httemplate/search/rt_ticket.html index f5ac023b5..2826cd771 100644 --- a/httemplate/search/rt_ticket.html +++ b/httemplate/search/rt_ticket.html @@ -66,68 +66,27 @@ my $twhere = " AND Transactions.ObjectId = Tickets.Id "; -my $transaction_time; my $applied = ''; -my $cfname = ''; -if ( $cgi->param('cfname') =~ /^\w(\w|\s)*$/ ) { - - $cfname = $cgi->param('cfname'); - - $transaction_time = "(CASE Transactions.Type - WHEN 'CustomField' THEN - ( coalesce(to_number(ocfv_new.Content,'999999'),0) - - coalesce(to_number(ocfv_old.Content,'999999'),0) ) - ELSE ( to_number(ocfv_main.Content,'999999') ) - END) * 60"; - - $join .= " - LEFT JOIN ObjectCustomFieldValues ocfv_new - ON ( ocfv_new.Id = Transactions.NewReference ) - LEFT JOIN ObjectCustomFieldValues ocfv_old - ON ( ocfv_old.Id = Transactions.OldReference ) - LEFT JOIN ObjectCustomFieldValues ocfv_main - ON ( ocfv_main.ObjectType = 'RT::Transaction' - AND ocfv_main.ObjectId = Transactions.Id ) - JOIN CustomFields - ON ( ( CustomFields.LookupType = 'RT::Queue-RT::Ticket-RT::Transaction' - AND CustomFields.Id = ocfv_main.CustomField - AND ocfv_main.Id IS NOT NULL - ) - OR - ( CustomFields.LookupType = 'RT::Queue-RT::Ticket' - AND (CustomFields.Id = ocfv_new.CustomField OR ocfv_new.Id IS NULL) - AND (CustomFields.Id = ocfv_old.CustomField OR ocfv_old.Id IS NULL) - AND ocfv_main.Id IS NULL - ) ) - "; - - $twhere .= " AND CustomFields.Name = '$cfname' - AND (ocfv_new.Id IS NOT NULL OR ocfv_old.Id IS NOT NULL OR ocfv_main.Id IS NOT NULL)"; - -} else { - - $transaction_time = " - CASE transactions.type when 'Set' - THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60 - ELSE timetaken*60 - END"; - - if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) { - $twhere .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )"; - $applied = "AND svcnum = $1"; - } - - $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 $transaction_time = " +CASE transactions.type when 'Set' + THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60 + ELSE timetaken*60 +END"; + +if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) { + $twhere .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )"; + $applied = "AND svcnum = $1"; } +$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... ? @@ -145,9 +104,12 @@ if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { } my $transactions = "FROM Transactions $join $twhere"; - my $where = "WHERE EXISTS ( SELECT 1 $transactions )"; +if ( $cgi->param('category') =~ /^(\w+)$/ ) { + $where .= " AND ocfv_TimeType.Content = '$1'"; +} + my $ticket_time = "( SELECT SUM($transaction_time) $transactions )"; push @select, "$ticket_time AS ticket_time"; push @select_total, "SUM($ticket_time)"; @@ -161,18 +123,30 @@ if ( $applied ) { } +my $addl_from = " LEFT JOIN ( + SELECT DISTINCT ON (ObjectId) + ObjectId, Content + FROM ObjectCustomFieldValues + JOIN CustomFields + ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) + WHERE CustomFields.Name = 'TimeType' + AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket' + AND ObjectCustomFieldValues.Disabled = 0 + ORDER BY ObjectId ASC, ObjectCustomFieldValues.LastUpdated DESC + ) AS ocfv_TimeType ON (Tickets.Id = ocfv_TimeType.ObjectId) +"; + my $query = { 'select' => join(', ', @select), 'table' => 'tickets', #Pg-ism #'table' => 'Tickets', - 'addl_from' => '', #$join, + 'addl_from' => $addl_from, 'extra_sql' => $where, 'order by' => 'ORDER BY Created', }; -my $count_query = "SELECT ".join(', ', @select_total)." FROM Tickets $where"; - #"SELECT COUNT(*), SUM($transactiontime), SUM(acct_rt_transaction.support) FROM Transactions $join $where"; - #"SELECT COUNT(*), ( SUM($transactiontime) $transactions ) FROM Tickets"; # $join $where"; +my $count_query = "SELECT ".join(', ', @select_total). + " FROM Tickets $addl_from $where"; my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ]; diff --git a/httemplate/search/rt_transaction.html b/httemplate/search/rt_transaction.html index c9a305f03..aace4e9b2 100644 --- a/httemplate/search/rt_transaction.html +++ b/httemplate/search/rt_transaction.html @@ -59,80 +59,50 @@ my @select_total = ( 'COUNT(*)' ); my $transaction_time; my $applied_time = ''; my $join = 'JOIN Tickets ON Transactions.ObjectId = Tickets.Id '. - 'JOIN Users ON Transactions.Creator = Users.Id '; #. + 'JOIN Users ON Transactions.Creator = Users.Id '. + "LEFT JOIN ( + SELECT DISTINCT ON (ObjectId) + ObjectId, Content + FROM ObjectCustomFieldValues + JOIN CustomFields + ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) + WHERE CustomFields.Name = 'TimeType' + AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket' + AND ObjectCustomFieldValues.Disabled = 0 + ORDER BY ObjectId ASC, ObjectCustomFieldValues.LastUpdated DESC + ) AS ocfv_TimeType ON (Tickets.Id = ocfv_TimeType.ObjectId) + "; my $where = "WHERE Transactions.ObjectType = 'RT::Ticket'"; -my $cfname = ''; -if ( $cgi->param('cfname') =~ /^\w(\w|\s)*$/ ) { - - # a TimeValue-type custom field - $cfname = $cgi->param('cfname'); - - $transaction_time = "(CASE Transactions.Type - WHEN 'CustomField' THEN - ( coalesce(to_number(ocfv_new.Content,'999999'),0) - - coalesce(to_number(ocfv_old.Content,'999999'),0) ) - ELSE ( to_number(ocfv_main.Content,'999999') ) - END) * 60"; - - # complicated because we have to deal with the case of editing the - # ticket custom field directly (OldReference/NewReference) as well as - # entering a transaction with a custom field value (ObjectId) - $join .= " - LEFT JOIN ObjectCustomFieldValues ocfv_new - ON ( ocfv_new.Id = Transactions.NewReference ) - LEFT JOIN ObjectCustomFieldValues ocfv_old - ON ( ocfv_old.Id = Transactions.OldReference ) - LEFT JOIN ObjectCustomFieldValues ocfv_main - ON ( ocfv_main.ObjectType = 'RT::Transaction' - AND ocfv_main.ObjectId = Transactions.Id ) - JOIN CustomFields - ON ( ( CustomFields.LookupType = 'RT::Queue-RT::Ticket-RT::Transaction' - AND CustomFields.Id = ocfv_main.CustomField - AND ocfv_main.Id IS NOT NULL - ) - OR - ( CustomFields.LookupType = 'RT::Queue-RT::Ticket' - AND (CustomFields.Id = ocfv_new.CustomField OR ocfv_new.Id IS NULL) - AND (CustomFields.Id = ocfv_old.CustomField OR ocfv_old.Id IS NULL) - AND ocfv_main.Id IS NULL - ) ) - "; - - $where .= " AND CustomFields.Name = '$cfname' - AND (ocfv_new.Id IS NOT NULL OR ocfv_old.Id IS NOT NULL OR ocfv_main.Id IS NOT NULL)"; +# the intrinsic TimeWorked/TimeTaken fields +$transaction_time = "CASE Transactions.Type when 'Set' + THEN (to_number(NewValue,'999999')-to_number(OldValue, '999999')) * 60 + ELSE TimeTaken*60 + END"; +my $applied = ''; +if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) { + $where .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )"; + $applied = "AND svcnum = $1"; } -else { - - # the intrinsic TimeWorked/TimeTaken fields - $transaction_time = "CASE Transactions.Type when 'Set' - THEN (to_number(NewValue,'999999')-to_number(OldValue, '999999')) * 60 - ELSE TimeTaken*60 - END"; - - my $applied = ''; - if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) { - $where .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )"; - $applied = "AND svcnum = $1"; - } - - $applied_time = "( SELECT SUM(support) from acct_rt_transaction where transaction_id = Transactions.id $applied )"; - - $where .= " - AND ( ( Transactions.Type = 'Set' - AND Transactions.Field = 'TimeWorked' - AND Transactions.NewValue != Transactions.OldValue ) - OR ( ( Transactions.Type='Create' OR Transactions.Type='Comment' OR Transactions.Type='Correspond' OR Transactions.Type='Touch' ) - AND Transactions.TimeTaken > 0 - ) - ) - "; +$applied_time = "( SELECT SUM(support) from acct_rt_transaction where transaction_id = Transactions.id $applied )"; + +$where .= " + AND ( ( Transactions.Type = 'Set' + AND Transactions.Field = 'TimeWorked' + AND Transactions.NewValue != Transactions.OldValue ) + OR ( ( Transactions.Type='Create' OR Transactions.Type='Comment' OR Transactions.Type='Correspond' OR Transactions.Type='Touch' ) + AND Transactions.TimeTaken > 0 + ) + ) +"; + +if ( $cgi->param('category') =~ /^(\w+)$/ ) { + $where .= " AND ocfv_TimeType.Content = '$1'"; } -#AND transaction_time != 0 -#AND $wheretimeleft + push @select, "($transaction_time) AS transaction_time"; push @select_total, "SUM($transaction_time)"; if ( $applied_time ) { diff --git a/httemplate/search/timeworked.html b/httemplate/search/timeworked.html index fa4b89539..3e3ddcbba 100644 --- a/httemplate/search/timeworked.html +++ b/httemplate/search/timeworked.html @@ -86,25 +86,46 @@ my $where = " my $str2time_sql = str2time_sql; my $closing = str2time_sql_closing; + my($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi); $where .= " AND $str2time_sql Transactions.Created $closing >= $begin ". " AND $str2time_sql Transactions.Created $closing <= $end "; +if ($cgi->param('category') =~ /^(\w+)$/) { + $where .= " AND ocfv_TimeType.Content = '$1'"; +} +warn $where."\n";; + +my $from = " + FROM Transactions + JOIN Tickets ON Transactions.ObjectId = Tickets.id + LEFT JOIN acct_rt_transaction + ON Transactions.id = acct_rt_transaction.transaction_id + LEFT JOIN ( + SELECT DISTINCT ON (ObjectId) + ObjectId, Content + FROM ObjectCustomFieldValues + JOIN CustomFields + ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) + WHERE CustomFields.Name = 'TimeType' + AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket' + AND ObjectCustomFieldValues.Disabled = 0 + ORDER BY ObjectId ASC, ObjectCustomFieldValues.LastUpdated DESC + ) AS ocfv_TimeType ON (Tickets.Id = ocfv_TimeType.ObjectId) +"; + my $query = " SELECT Tickets.id, Tickets.Subject, TO_CHAR(Transactions.Created, 'Dy Mon DD HH24:MI:SS YYYY'), $transactiontime-$appliedtimeclause, Transactions.id - FROM Transactions - JOIN Tickets ON Transactions.ObjectId = Tickets.id - LEFT JOIN acct_rt_transaction - ON Transactions.id = acct_rt_transaction.transaction_id + $from $where GROUP BY $groupby ORDER BY Transactions.Created "; -my $count_query = "SELECT COUNT(*) FROM Transactions $where"; +my $count_query = "SELECT COUNT(*) $from $where"; my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->[0]; } ]; |