From 1293d137b061f097190eda53e4e78214e18832e6 Mon Sep 17 00:00:00 2001 From: jeff Date: Thu, 16 Aug 2007 13:40:46 +0000 Subject: [PATCH] support hours 'usage' tracking for our own internal use (#1733) --- FS/FS/AccessRight.pm | 1 + FS/FS/ClientAPI/MyAccount.pm | 14 + FS/FS/Conf.pm | 7 + FS/FS/Schema.pm | 13 + FS/FS/cust_main.pm | 46 +++ FS/FS/svc_acct_rt_transaction.pm | 260 +++++++++++++++ FS/MANIFEST | 2 + FS/t/svc_acct_rt_transaction.t | 5 + fs_selfservice/FS-SelfService/cgi/myaccount.html | 35 ++ htetc/handler.pl | 1 + httemplate/elements/menu.html | 2 + httemplate/misc/batch-cust_pay.html | 379 +--------------------- httemplate/misc/elements/customer-table.html | 387 +++++++++++++++++++++++ httemplate/misc/process/timeworked.html | 47 +++ httemplate/misc/timeworked.html | 77 +++++ httemplate/search/timeworked.html | 74 +++++ httemplate/view/cust_main/tickets.html | 27 +- 17 files changed, 980 insertions(+), 397 deletions(-) create mode 100644 FS/FS/svc_acct_rt_transaction.pm create mode 100644 FS/t/svc_acct_rt_transaction.t create mode 100644 httemplate/misc/elements/customer-table.html create mode 100644 httemplate/misc/process/timeworked.html create mode 100755 httemplate/misc/timeworked.html create mode 100644 httemplate/search/timeworked.html diff --git a/FS/FS/AccessRight.pm b/FS/FS/AccessRight.pm index fb7e538c2..4071e9730 100644 --- a/FS/FS/AccessRight.pm +++ b/FS/FS/AccessRight.pm @@ -203,6 +203,7 @@ tie my %rights, 'Tie::IxHash', ### 'Miscellaneous rights' => [ { rightname=>'Job queue', global=>1 }, + { rightname=>'Time queue', global=>1 }, { rightname=>'Process batches', global=>1 }, { rightname=>'Reprocess batches', global=>1 }, { rightname=>'Import', global=>1 }, #some of these are ag-virt'ed now? give em their own ACLs diff --git a/FS/FS/ClientAPI/MyAccount.pm b/FS/FS/ClientAPI/MyAccount.pm index 459558d61..32c37e9d4 100644 --- a/FS/FS/ClientAPI/MyAccount.pm +++ b/FS/FS/ClientAPI/MyAccount.pm @@ -120,6 +120,12 @@ sub customer_info { $return{balance} = $cust_main->balance; + $return{tickets} = [ ($cust_main->tickets) ]; + use Data::Dumper; + open(MYFILE, ">>/tmp/debugger"); + print MYFILE Dumper($return{tickets}); + close MYFILE; + my @open = map { { invnum => $_->invnum, @@ -149,6 +155,14 @@ sub customer_info { $return{'postal_invoicing'} = 0 < ( grep { $_ eq 'POST' } $cust_main->invoicing_list ); + if (scalar($conf->config('support_packages'))) { + my $support = 0; + foreach ($cust_main->support_services) { + $support += $_->svc_x->seconds; + } + $return{support_time} = (($support < 0) ? '-' : '' ). int(abs($support)/3600)."h".sprintf("%02d",(abs($support)%3600)/60)."m"; + } + } elsif ( $session->{'svcnum'} ) { #no customer record my $svc_acct = qsearchs('svc_acct', { 'svcnum' => $session->{'svcnum'} } ) diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm index 7f64058b8..e1fa0517a 100644 --- a/FS/FS/Conf.pm +++ b/FS/FS/Conf.pm @@ -2047,6 +2047,13 @@ httemplate/docs/config.html 'type' => 'checkbox', }, + { + 'key' => 'support_packages', + 'section' => '', + 'description' => 'A list of packages eligible for RT ticket time transfer', + 'type' => 'textarea', + }, + ); 1; diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index b7e754573..da264fa05 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -988,6 +988,19 @@ sub tables_hashref { 'index' => [ ['username'], ['domsvc'] ], }, + 'svc_acct_rt_transaction' => { + 'columns' => [ + 'svcrtid', 'int', '', '', '', '', + 'svcnum', 'int', '', '', '', '', + 'transaction_id', 'int', '', '', '', '', + '_date', @date_type, '', '', + 'seconds', 'int', '', '', '', '', #uhhhh + ], + 'primary_key' => 'svcrtid', + 'unique' => [], + 'index' => [ ['svcnum', 'transaction_id'] ], + }, + #'svc_charge' => { # 'columns' => [ # 'svcnum', 'int', '', '', diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index f437abe41..afcba0874 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -4620,6 +4620,52 @@ sub cust_statuscolor { $statuscolor{$self->cust_status}; } +=item tickets + +Returns an array of hashes representing the customer's RT tickets. + +=cut + +sub tickets { + my $self = shift; + + my $num = $conf->config('cust_main-max_tickets') || 10; + my @tickets = (); + + unless ( $conf->config('ticket_system-custom_priority_field') ) { + + @tickets = @{ FS::TicketSystem->customer_tickets($self->custnum, $num) }; + + } else { + + foreach my $priority ( + $conf->config('ticket_system-custom_priority_field-values'), '' + ) { + last if scalar(@tickets) >= $num; + push @tickets, + @{ FS::TicketSystem->customer_tickets( $self->custnum, + $num - scalar(@tickets), + $priority, + ) + }; + } + } + (@tickets); +} + +# Return services representing svc_accts in customer support packages +sub support_services { + my $self = shift; + my %packages = map { $_ => 1 } $conf->config('support_packages'); + + grep { $_->pkg_svc && $_->pkg_svc->primary_svc eq 'Y' } + grep { $_->part_svc->svcdb eq 'svc_acct' } + map { $_->cust_svc } + grep { exists $packages{ $_->pkgpart } } + $self->ncancelled_pkgs; + +} + =back =head1 CLASS METHODS diff --git a/FS/FS/svc_acct_rt_transaction.pm b/FS/FS/svc_acct_rt_transaction.pm new file mode 100644 index 000000000..0fb64456b --- /dev/null +++ b/FS/FS/svc_acct_rt_transaction.pm @@ -0,0 +1,260 @@ +package FS::svc_acct_rt_transaction; + +use strict; +use vars qw( @ISA ); +use FS::Record qw( qsearch qsearchs dbh ); + +@ISA = qw(FS::Record); + +=head1 NAME + +FS::svc_acct_rt_transaction - Object methods for svc_acct_rt_transaction records + +=head1 SYNOPSIS + + use FS::svc_acct_rt_transaction; + + $record = new FS::svc_acct_rt_transaction \%hash; + $record = new FS::svc_acct_rt_transaction { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::svc_acct_rt_transaction object represents an application of time +from a rt transaction to a svc_acct. FS::svc_acct_rt_transaction inherits from +FS::Record. The following fields are currently supported: + +=over 4 + +=item svcrtid - primary key + +=item svcnum - the svcnum of the svc_acct to which the time applies + +=item transaction_id - the id of the rt transtaction from which the time applies + +=item seconds - the amount of time which applies + + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new svc_acct_rt_transaction. To add the example to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I method. + +=cut + +sub table { 'svc_acct_rt_transaction'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=cut + +sub insert { + my( $self, %options ) = @_; + + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + my $error = $self->SUPER::insert($options{options} ? %{$options{options}} : ()); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + + my $svc_acct = qsearchs('svc_acct', {'svcnum' => $self->svcnum}); + unless ($svc_acct) { + $dbh->rollback if $oldAutoCommit; + return "Can't find svc_acct " . $self->svcnum; + } + + my $error = $svc_acct->decrement_seconds($self->seconds); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "Error incrementing service seconds: $error"; + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + ''; + +} + + +=item delete + +Delete this record from the database. + +=cut + +sub delete { + my $self = shift; + + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + my $error = $self->SUPER::delete; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + + my $svc_acct = qsearchs('svc_acct', {'svcnum' => $self->svcnum}); + unless ($svc_acct) { + $dbh->rollback if $oldAutoCommit; + return "Can't find svc_acct " . $self->svcnum; + } + + my $error = $svc_acct->increment_seconds($self->seconds); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "Error incrementing service seconds: $error"; + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + ''; + +} + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=cut + +=item check + +Checks all fields to make sure this is a valid svc_acct_rt_transaction. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +sub check { + my $self = shift; + + my ($selfref) = $self->hashref; + + my $error = + $self->ut_numbern('svcrtid') + || $self->ut_numbern('svcnum') + || $self->ut_number('transaction_id') + || $self->ut_numbern('_date') + || $self->ut_snumber('seconds') + ; + return $error if $error; + + $self->_date(time) unless $self->_date; + + if ($selfref->{custnum}) { + my $conf = new FS::Conf; + my %packages = map { $_ => 1 } $conf->config('support_packages'); + my $cust_main = qsearchs('cust_main',{ 'custnum' => $selfref->{custnum} } ); + return "Invalid custnum: " . $selfref->{custnum} unless $cust_main; + + my (@svcs) = map { $_->svcnum } $cust_main->support_services; + return "svcnum ". $self->svcnum. " invalid for custnum ".$selfref->{custnum} + unless (!$self->svcnum || scalar(grep { $_ == $self->svcnum } @svcs)); + + $self->svcnum($svcs[0]) unless $self->svcnum; + return "Can't find support service for custnum ".$selfref->{custnum} + unless $self->svcnum; + } + + $self->SUPER::check; +} + +=item batch_insert SVC_ACCT_RT_TRANSACTION_OBJECT, ... + +Class method which inserts multiple time applications. Takes a list of +FS::svc_acct_rt_transaction objects. If there is an error inserting any +application, the entire transaction is rolled back, i.e. all time is applied +or none is. + +For example: + + my $errors = FS::svc_acct_rt_transaction->batch_insert(@transactions); + if ( $error ) { + #success; all payments were inserted + } else { + #failure; no payments were inserted. + } + +=cut + +sub batch_insert { + my $self = shift; #class method + + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + my $error; + foreach (@_) { + $error = $_->insert; + last if $error; + } + + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + } else { + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + } + + $error; + +} + +=back + +=head1 BUGS + +Possibly the delete method or others. + +=head1 SEE ALSO + +L, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/MANIFEST b/FS/MANIFEST index c30cd1521..07b1eabf8 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -388,3 +388,5 @@ FS/cust_pkg_option.pm t/cust_pkg_option.t FS/conf.pm t/conf.t +FS/svc_acct_rt_transaction.pm +t/svc_acct_rt_transaction.t diff --git a/FS/t/svc_acct_rt_transaction.t b/FS/t/svc_acct_rt_transaction.t new file mode 100644 index 000000000..d9958317e --- /dev/null +++ b/FS/t/svc_acct_rt_transaction.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::svc_acct_rt_transaction; +$loaded=1; +print "ok 1\n"; diff --git a/fs_selfservice/FS-SelfService/cgi/myaccount.html b/fs_selfservice/FS-SelfService/cgi/myaccount.html index 546ca1112..ef0740fd6 100644 --- a/fs_selfservice/FS-SelfService/cgi/myaccount.html +++ b/fs_selfservice/FS-SelfService/cgi/myaccount.html @@ -36,6 +36,41 @@ Hello <%= $name %>!

} %> +<%= + if ( defined($support_time) ) { + $OUT .= ''. + ''. + "

"; + } +%> + +<%= + if ( @tickets ) { + $OUT .= '
Support Time Remaining
$support_time
'. + ''. + ''. + ''; + my $col1 = "ffffff"; + my $col2 = "dddddd"; + my $col = $col1; + + foreach my $ticket ( @tickets ) { + my $td = qq!$td". $ticket->{'id'}. "". + $td. $ticket->{'subject'}. "". + $td. $ticket->{'priority'}. "". + $td. $ticket->{'name'}. "". + $td. $ticket->{'status'}. "". + ''; + $col = $col eq $col1 ? $col2 : $col1; + } + $OUT .= '
Open Tickets
#SubjectPriorityQueueStatus
!; + $OUT .= + "
'; + } else { + $OUT .= ''; + } +%> +
powered by freeside diff --git a/htetc/handler.pl b/htetc/handler.pl index 25962412b..c5873a878 100644 --- a/htetc/handler.pl +++ b/htetc/handler.pl @@ -169,6 +169,7 @@ sub handler use FS::session; use FS::svc_acct; use FS::svc_acct_pop qw(popselector); + use FS::svc_acct_rt_transaction; use FS::svc_domain; use FS::svc_forward; use FS::svc_www; diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 616900c2d..455c7b364 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -214,6 +214,8 @@ $tools_menu{'Process payment batches'} = [ $fsurl.'search/pay_batch.cgi?magic=_d if $conf->exists('batch-enable') && $curuser->access_right('Process batches'); $tools_menu{'Job Queue'} = [ $fsurl.'search/queue.html', 'View pending job queue' ] if $curuser->access_right('Job queue'); +$tools_menu{'Time Queue'} = [ $fsurl.'search/timeworked.html', 'View pending support time' ] + if $curuser->access_right('Time queue'); $tools_menu{'Importing'} = [ \%tools_importing, 'Import tools' ] if $curuser->access_right('Import'); $tools_menu{'Exporting'} = [ \%tools_exporting, 'Export tools' ] diff --git a/httemplate/misc/batch-cust_pay.html b/httemplate/misc/batch-cust_pay.html index d85f3b6c3..f2f446001 100644 --- a/httemplate/misc/batch-cust_pay.html +++ b/httemplate/misc/batch-cust_pay.html @@ -1,8 +1,5 @@ <% include("/elements/header.html", 'Quick payment entry', - menubar( - 'Main Menu' => $p, #popurl(1), - ), - ( $cgi->param('error') ? '' : 'onload="addRow()"' ), + menubar( 'Main Menu' => $p ), ) %> % if ( $cgi->param('error') ) { @@ -16,284 +13,13 @@ - - - - - - - - - - - -% my $row = 0; -% if ( $cgi->param('error') ) { -% my $param = $cgi->Vars; -% -% for ( $row = 0; exists($param->{"custnum$row"}); $row++ ) { - - - - - - - - - - - - - - - -% } -% } - - -
Cust #CustomerAmountCheck #
- " rownum="<% $row %>"> - - - " rownum="<% $row %>"> - - - - - $" > - - " > - -% if ( $param->{"error$row"} ) { - - Error: <% $param->{"error$row"} %> -% } - -
+<% include( "elements/customer-table.html", + header => [ '', 'Amount', 'Check #', '' ], + fields => [ sub {'$'}, 'paid', 'payinfo', 'error', ], + types => [ 'immutable', '', '', 'immutable', ], + sizes => [ 0, 8, 10, 0, ], + param => { () }, + ) %> @@ -302,94 +28,5 @@ - - -<% include('/elements/xmlhttp.html', - 'url' => $p. 'misc/xmlhttp-cust_main-search.cgi', - 'subs' => [qw( custnum_search smart_search )], - ) -%> - - - diff --git a/httemplate/misc/elements/customer-table.html b/httemplate/misc/elements/customer-table.html new file mode 100644 index 000000000..fc298b03e --- /dev/null +++ b/httemplate/misc/elements/customer-table.html @@ -0,0 +1,387 @@ +% # options example... +% # +% # #listrefs... +% # 'header' => [ '#', 'Item' ], +% # 'fields' => [ +% # 'column', +% # sub { my ($row,$param) = @_; $param->{"column$row"}; }, +% # ], +% # 'sizes' => [], # sizes ignored for immutable +% # 'types' => ['immutable', ''], # immutable or ''/text +% # 'param' => { column0 => 1 }, # preset column of row 0 to 1 +% # + + + + + + + + +% foreach my $header ( @{$opt{header}} ) { + +% } + +% my $row = 0; +% for ( $row = 0; exists($param->{"custnum$row"}); $row++ ) { + + + + + + + +% my $col = 0; +% foreach my $field ( @{$opt{fields}} ) { +% my $value; +% if ( ref($field) eq 'CODE' ) { +% $value = &{$field}($row,$param); +% } else { +% $value = $param->{"$field$row"}; +% } +% my $name = (ref($field) eq 'CODE') ? "column${col}_$row" : "$field$row"; +% my $size = $sizes->[$col] || 10; + +% $col++; +% } + + +% } + + +
Cust #Customer<% $header %>
+ " rownum="<% $row %>"> + + + " rownum="<% $row %>"> + + + + +% if (! $types->[$col] || $types->[$col] eq 'text') { + +% } elsif ($types->[$col] eq 'immutable') { + <% $value %> + +% } else { + Cannot represent unknown type: <% $types->[$col] %> +% } +
+ +<% include('/elements/xmlhttp.html', + 'url' => $p. 'misc/xmlhttp-cust_main-search.cgi', + 'subs' => [qw( custnum_search smart_search )], + ) +%> + + + +<%init> + +my(%opt) = @_; + +$opt{prefix} = '' unless defined $opt{prefix}; +$opt{prefix} .= '_' if $opt{prefix}; + +my $types = $opt{'types'} ? [ @{$opt{'types'}} ] : []; +my $sizes = $opt{'sizes'} ? [ @{$opt{'sizes'}} ] : []; + +my $param = $opt{param}; +$param = $cgi->Vars if $cgi->param('error'); + + diff --git a/httemplate/misc/process/timeworked.html b/httemplate/misc/process/timeworked.html new file mode 100644 index 000000000..078d645d1 --- /dev/null +++ b/httemplate/misc/process/timeworked.html @@ -0,0 +1,47 @@ +% if ($error) { +<% $cgi->redirect(popurl(2). "timeworked.html?". $cgi->query_string) %> +% } else { +<% $cgi->redirect(popurl(3). "search/timeworked.html") %> +% } +<%init> + +my %multipliers = map { /^custnum(\d+)$/; ($cgi->param("custnum$1") => $cgi->param("multiplier$1")); } + grep /^custnum\d+$/, $cgi->param; + +my @svc_acct_rt_transaction; +foreach my $transaction ( + map { /^transactionid(\d+)$/; $1; } grep /^transactionid\d+$/, $cgi->param +) { + my $seconds = $cgi->param("seconds$transaction"); + my %seconds = map { $_ => sprintf("%.0f", $seconds * $multipliers{$_}) } + (keys %multipliers); + my $sum = 0; + my $count = 0; + foreach (values %seconds) { + $sum += $_; + $count++; + } + + #fudge in some time if we're close + if (abs($seconds-$sum) <= $count) { + my $adjustment = $seconds-$sum; + foreach (keys %seconds) { # explicitly choose one? + $seconds{$_} += $adjustment; + last; + } + } + + foreach my $customer ( grep {$seconds{$_}} keys %seconds ) { + push @svc_acct_rt_transaction, new FS::svc_acct_rt_transaction { + 'custnum' => $customer, + 'transaction_id' => $transaction, + 'seconds' => $seconds{$customer}, + }; + } + +} + +my $error = FS::svc_acct_rt_transaction->batch_insert(@svc_acct_rt_transaction); +$cgi->param('error', $error) if $error; + + diff --git a/httemplate/misc/timeworked.html b/httemplate/misc/timeworked.html new file mode 100755 index 000000000..2b288f233 --- /dev/null +++ b/httemplate/misc/timeworked.html @@ -0,0 +1,77 @@ +<% include('/elements/header.html', $title, '' ) %> + +% if ( $cgi->param('error') ) { + Error: <% $cgi->param('error') %> +

+% } + +
+ +

+<% include("elements/customer-table.html", header => [ 'Multiplier' ], + fields => [ 'multiplier' ], + param => { %param }, + ) %> + +
+ +
+
+ +for transactions/tickets: + + +% foreach ( sort { $a <=> $b } keys %ticket ) { + + + + " > + +% } + +
<% $_ %><% $ticket{$_} %>
+
+ + + +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Time queue'); + +my($svcnum, %ticket, %customers, %param); +my $title = 'Assign Time Worked'; + +RT::Init(); + +my $CurrentUser = RT::CurrentUser->new(); +$CurrentUser->LoadByName($FS::CurrentUser::CurrentUser->username); + +foreach my $id ( map { /^transactionid(\d+)$/; $1; } + grep /^transactionid\d+$/, $cgi->param) { + my $transaction = new RT::Transaction($CurrentUser); + $transaction->Load($id); + my $ticket = new RT::Ticket($CurrentUser); + $ticket->Load($transaction->ObjectId); + $ticket{$id} = $ticket->Subject; + foreach my $customerURI ( + grep { $_->Resolver->{'fstable'} eq 'cust_main' } + grep { $_->Scheme eq 'freeside' } + map { $_->TargetURI } + @{ $ticket->_Links('Base')->ItemsArrayRef } + ) { + $customers{$customerURI->Resolver->AsString} = 1; + } +} + +my $row = 0; +foreach ( keys %customers ) { + my ($number, $name) = split(':', $_, 2); + $param{"custnum$row"} = $number; + $param{"customer$row"} = $name; + $param{"multiplier$row"} = sprintf("%.2f", 1/scalar(keys(%customers))); + $row++; +} + + + diff --git a/httemplate/search/timeworked.html b/httemplate/search/timeworked.html new file mode 100644 index 000000000..18af74641 --- /dev/null +++ b/httemplate/search/timeworked.html @@ -0,0 +1,74 @@ +<% include( 'elements/search.html', + 'title' => 'Time Worked', + 'menubar' => [ 'Main menu' => $p, ], + 'name' => 'time', + 'html_form' => qq!
!, + 'query' => $query, + 'count_query' => $count_query, + 'header' => [ '#', + 'Ticket', + 'Date', + 'Time', + '', # checkbox column + ], + 'fields' => [ sub { shift->[0] }, + sub { shift->[1] }, + sub { shift->[2] }, + sub { my $seconds = shift->[3]; + (($seconds < 0) ? '-' : '') . + concise(duration($seconds)); + }, + sub { + my $row = shift; + my $seconds = $row->[3]; + my $id = $row->[4]; + qq!!. + qq!!; + }, + ], + 'html_foot' => sub { + '
'. + ''. + '

'. + ''; + }, + ) + +%> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Time queue'); + +my @groupby = (); + +my $transactiontime = "CASE transactions.type when 'Set' THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60 ELSE timetaken*60 END"; +push @groupby, "transactions.type"; +push @groupby, "newvalue"; +push @groupby, "oldvalue"; +push @groupby, "timetaken"; + +my $appliedtimeclause = "coalesce (sum(svc_acct_rt_transaction.seconds), 0)"; + +my $appliedtimeselect = "SELECT sum(seconds) FROM svc_acct_rt_transaction where transaction_id = transactions.id"; +push @groupby, "transactions.id"; + +my $wheretimeleft = "($transactiontime != ($appliedtimeselect) OR ($appliedtimeselect) is NULL)"; + +push @groupby, "tickets.id"; +push @groupby, "tickets.subject"; +push @groupby, "transactions.created"; + +my $groupby = join(',', @groupby); + +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 svc_acct_rt_transaction on transactions.id = svc_acct_rt_transaction.transaction_id WHERE objecttype='RT::Ticket' AND ((transactions.type='Set' AND field='TimeWorked') OR transactions.type='Comment' OR transactions.type='Correspond') AND ($wheretimeleft) GROUP BY $groupby ORDER BY transactions.created"; +my $count_query = "SELECT count(*) FROM transactions WHERE objecttype='RT::Ticket' AND ((transactions.type='Set' AND field='TimeWorked') OR transactions.type='Comment' OR transactions.type='Correspond') AND ($wheretimeleft)"; + + diff --git a/httemplate/view/cust_main/tickets.html b/httemplate/view/cust_main/tickets.html index 84cc90299..c4183ae55 100644 --- a/httemplate/view/cust_main/tickets.html +++ b/httemplate/view/cust_main/tickets.html @@ -1,31 +1,6 @@ % % my( $cust_main ) = @_; -% -% my $conf = new FS::Conf; -% my $num = $conf->config('cust_main-max_tickets') || 10; -% -% my @tickets = (); -% unless ( $conf->config('ticket_system-custom_priority_field') ) { -% -% @tickets = -% @{ FS::TicketSystem->customer_tickets($cust_main->custnum, $num) }; -% -% } else { -% -% foreach my $priority ( -% $conf->config('ticket_system-custom_priority_field-values'), '' -% ) { -% last if scalar(@tickets) >= $num; -% push @tickets, -% @{ FS::TicketSystem->customer_tickets( $cust_main->custnum, -% $num - scalar(@tickets), -% $priority, -% ) -% }; -% } -% -% } -% +% my( @tickets ) = $cust_main->tickets; % Tickets -- 2.11.0