From: Mark Wells Date: Wed, 24 Sep 2014 23:29:46 +0000 (-0700) Subject: change "support time" to use regular ticket time + a custom field, #30921 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=a6def4c68914a9d8a282eaa34dcff5f6fd002568 change "support time" to use regular ticket time + a custom field, #30921 --- 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:' &> - - <& /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', &> - <% 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); -} - - 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:' &> - - <& /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', &> - - <% 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' ] + &> +
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 eb250fb27..71a266961 100644 --- a/httemplate/search/rt_transaction.html +++ b/httemplate/search/rt_transaction.html @@ -58,80 +58,50 @@ my @select_total = ( 'COUNT(*)' ); my ($transaction_time, $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]; } ];