Statistics query

hello’s!

i’m unable to solve a statistics query problem. specifically, the
’deleted’ count is always zero, presumably because of this query:

SELECT count(main.id) FROM Tickets main WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’))
AND ( ( (main.Status = ‘deleted’) ) AND ( (main.Queue = ‘1’) ) AND (
(main.LastUpdated <= ‘2006-01-17 16:00’)AND(main.LastUpdated >=
‘2006-01-16 16:00’) ) );

i think the problem is:

((main.Status != ‘deleted’)) … AND ( ( (main.Status = ‘deleted’) )

when i run the query manually without the !=deleted, the count returns
correct.

the only instance i’ve found in the rt3 codebase is
./lib/RT/Tickets_Overlay.pm, but removing the ‘AND ((main.Status !=
‘deleted’))’ doesn’t change the query that’s being run (viewing in mysql
log).

i’m running rt 3.4.4 and RTx-Statistics v1.8.

has someone solved this problem and i’ve glossed over it in the lists?

thanks!
joe

hello’s!

i’m unable to solve a statistics query problem. specifically, the
’deleted’ count is always zero, presumably because of this query:

SELECT count(main.id) FROM Tickets main WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’))
AND ( ( (main.Status = ‘deleted’) ) AND ( (main.Queue = ‘1’) ) AND (
(main.LastUpdated <= ‘2006-01-17 16:00’)AND(main.LastUpdated >=
‘2006-01-16 16:00’) ) );

i think the problem is:

((main.Status != ‘deleted’)) … AND ( ( (main.Status = ‘deleted’) )

when i run the query manually without the !=deleted, the count returns
correct.

the only instance i’ve found in the rt3 codebase is
./lib/RT/Tickets_Overlay.pm, but removing the ‘AND ((main.Status !=
‘deleted’))’ doesn’t change the query that’s being run (viewing in mysql
log).

i’m running rt 3.4.4 and RTx-Statistics v1.8.

Hello, Kelly, Joseph and co.
RT doesn’t allow searching for deleted tickets unless you use special option
$tickets_obj->{‘allow_deleted_search’} = 1
This functionality is available in 3.4.2 and greater, for earlier
versions you should patch RT.

See also usage in RTx::Shredder::Plugin::Tickets; and patch is also
shipped with shredder distribution.

has someone solved this problem and i’ve glossed over it in the lists?

thanks!
joe


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Download a free sample chapter of RT Essentials from O’Reilly Media at http://rtbook.bestpractical.com

WE’RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
San Francisco - Find out more at http://bestpractical.com/services/training.html

Best regards, Ruslan.

-----Original Message-----
From: Ruslan Zakirov [mailto:ruslan.zakirov@gmail.com]
Sent: Tuesday, January 17, 2006 8:21 AM
To: joseph kacmarcik
Cc: rt-users@lists.bestpractical.com; Kelly F. Hickel
Subject: Re: [rt-users] statistics query

hello’s!

i’m unable to solve a statistics query problem. specifically, the
’deleted’ count is always zero, presumably because of this query:

SELECT count(main.id) FROM Tickets main WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type =
‘ticket’))

AND ( ( (main.Status = ‘deleted’) ) AND ( (main.Queue = ‘1’) ) AND (
(main.LastUpdated <= ‘2006-01-17 16:00’)AND(main.LastUpdated >=
‘2006-01-16 16:00’) ) );

i think the problem is:

((main.Status != ‘deleted’)) … AND ( ( (main.Status = ‘deleted’) )

when i run the query manually without the !=deleted, the count
returns

correct.

the only instance i’ve found in the rt3 codebase is
./lib/RT/Tickets_Overlay.pm, but removing the ‘AND ((main.Status !=
‘deleted’))’ doesn’t change the query that’s being run (viewing in
mysql

log).

i’m running rt 3.4.4 and RTx-Statistics v1.8.

Hello, Kelly, Joseph and co.
RT doesn’t allow searching for deleted tickets unless you use special
option
$tickets_obj->{‘allow_deleted_search’} = 1
This functionality is available in 3.4.2 and greater, for earlier
versions you should patch RT.

See also usage in RTx::Shredder::Plugin::Tickets; and patch is also
shipped with shredder distribution.
[Kelly F. Hickel] Also, I’ve looked at this and can’t sync that query up
with the source. Joseph, can you tell us which report in RTx:Statistics
you’re using to generate that query?

-Kelly

has someone solved this problem and i’ve glossed over it in the
lists?

thanks!
joe


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Download a free sample chapter of RT Essentials from O’Reilly Media
at
http://rtbook.bestpractical.com

WE’RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston
and

[Kelly F. Hickel] Also, I’ve looked at this and can’t sync that query up
with the source. Joseph, can you tell us which report in RTx:Statistics
you’re using to generate that query?

this is just the ‘Tickets per Day’ target under RTx-Statistics. i did
try changing the allow_deleted_search to no avail.

in the file “./lib/RT/Tickets_Overlay.pm” @line 1390:

RT adds Status != ‘deleted’ until object has
allow_deleted_search internal property set.
$tickets->{‘allow_deleted_search’} = 1;
$tickets->LimitStatus( VALUE => ‘deleted’ );

i do want deleted tickets in the results, but changed to 0 and also
changed $tickets->LimitStatus( VALUE => ‘deleted’ ); to
$tickets->LimitStatus( VALUE => ‘’ );.

whether i restart apache or not, the query is not changed and results
are all zero for deleted tickets.

thanks for looking into this!
joe

-----Original Message-----
From: joseph kacmarcik [mailto:joe@earth.care2.com]
Sent: Tuesday, January 17, 2006 11:42 AM
To: Kelly F. Hickel
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] statistics query

[Kelly F. Hickel] Also, I’ve looked at this and can’t sync that
query up

with the source. Joseph, can you tell us which report in
RTx:Statistics

you’re using to generate that query?

this is just the ‘Tickets per Day’ target under RTx-Statistics. i did
try changing the allow_deleted_search to no avail.

in the file “./lib/RT/Tickets_Overlay.pm” @line 1390:

RT adds Status != ‘deleted’ until object has
allow_deleted_search internal property set.
$tickets->{‘allow_deleted_search’} = 1;
$tickets->LimitStatus( VALUE => ‘deleted’ );

i do want deleted tickets in the results, but changed to 0 and also
changed $tickets->LimitStatus( VALUE => ‘deleted’ ); to
$tickets->LimitStatus( VALUE => ‘’ );.

whether i restart apache or not, the query is not changed and results
are all zero for deleted tickets.

thanks for looking into this!
joe

Looking at the code in CallsQueueDay/index.html, I don’t see any way to
overcome this in the RTx::Statistics package, it must be something
happening under the covers. The excerpted code that generates this
query looks like this:

% my $tix = new RT::Tickets($session{‘CurrentUser’});
% $tix->LimitQueue (VALUE => $Queue);
% $tix->LimitStatus(VALUE => ‘deleted’);
% $tix->LimitLastUpdated(VALUE => $dates[$d]->ISO, OPERATOR
=> “>=”);
% if ($dates[$d+1]) {
% $tix->LimitLastUpdated(VALUE => $dates[$d+1]->ISO,
OPERATOR => “<=”);
% }

–Kelly

Looking at the code in CallsQueueDay/index.html, I don’t see any way to
overcome this in the RTx::Statistics package, it must be something
happening under the covers. The excerpted code that generates this
query looks like this:

% my $tix = new RT::Tickets($session{‘CurrentUser’});
% $tix->LimitQueue (VALUE => $Queue);
% $tix->LimitStatus(VALUE => ‘deleted’);
% $tix->LimitLastUpdated(VALUE => $dates[$d]->ISO, OPERATOR
=> “>=”);
% if ($dates[$d+1]) {
% $tix->LimitLastUpdated(VALUE => $dates[$d+1]->ISO,
OPERATOR => “<=”);
% }

i just found something else that’s alleviated ‘my’ problem. in
rt-3.4.4/lib/RT/Tickets_Overlay_SQL.pm @line 511:

We don’t want deleted tickets unless ‘allow_deleted_search’ is set

unless( $self->{‘allow_deleted_search’} ) {
$self->SUPER::Limit(FIELD => ‘Status’,
OPERATOR => ‘!=’,
VALUE => ‘deleted’);
}

it’s not a solution for everyone, but i’ve added:

$self->{‘allow_deleted_search’} = 1;

which gives me the results i want. i had tried changing
CallsQueueDay/index.html, but didn’t get anywhere. i guess it’s just not
picking up this variable.(?)

i’ll check out rt 3.4.5 shortly to see if there’s any difference in this
behavior.

thanks again!
joe

[snip]

Looking at the code in CallsQueueDay/index.html, I don’t see any way to
overcome this in the RTx::Statistics package, it must be something
happening under the covers. The excerpted code that generates this
query looks like this:

% my $tix = new RT::Tickets($session{‘CurrentUser’});
put here
$tix->{‘allow_deleted_search’} = 1; # and put it
whenever you want deleted in stats

% $tix->LimitQueue (VALUE => $Queue);
% $tix->LimitStatus(VALUE => ‘deleted’);
% $tix->LimitLastUpdated(VALUE => $dates[$d]->ISO, OPERATOR
=> “>=”);
% if ($dates[$d+1]) {
% $tix->LimitLastUpdated(VALUE => $dates[$d+1]->ISO,
OPERATOR => “<=”);
% }

–Kelly

Best regards, Ruslan.