The problem is, I'm not strong enough in sql and perl to whip that out. I understand the HomepageComponents, I've added a couple, and I can edit usually to suit my needs, but I can't just whip out a portlet. Not sure how to proceed.<br>
<br><div class="gmail_quote">On Tue, Feb 22, 2011 at 1:17 PM, Kevin Falcone <span dir="ltr"><<a href="mailto:falcone@bestpractical.com">falcone@bestpractical.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div><div></div><div class="h5">On Tue, Feb 22, 2011 at 01:10:00PM -0500, Chris Hall wrote:<br>
> Sorry for the ignorance here, but I'm trying to muddle my way through this. I've installed<br>
> DBIx::SearchBuilder via cpan and I've made a copy of the Results.html found in<br>
> /opt/rt3/share/html/Search, and called it Results_custom.html. I've put the following code in:<br>
> use DBIx::SearchBuilder::Handle;<br>
> my $handle = DBIx::SearchBuilder::Handle->new();<br>
> $handle->Connect(<br>
> Driver => 'mysql',<br>
> Database => 'rt3',<br>
> Host => 'localhost',<br>
> User => 'rt_user',<br>
> Password => 'abcd1234'<br>
> );<br>
> my ($title, $ticketcount);<br>
> $session{'i'}++;<br>
> $session{'tickets'} = RT::Tickets->new($session{'CurrentUser'}) ;<br>
> $session{'tickets'}->$handle->FetchResult("select<br>
> Transactions.id,ObjectType,ObjectId,Type,Transactions.Created,Name,EmailAddress from<br>
> Transactions inner join Users on Users.id=Transactions.Creator where ObjectType='RT::Ticket'<br>
> and Type='Comment' and Transactions.Created>='2011-02-04 01:00:00' and<br>
> Transactions.Created<='2011-02-07 23:59:00';");<br>
> in place of:<br>
> my ($title, $ticketcount);<br>
> $session{'i'}++;<br>
> $session{'tickets'} = RT::Tickets->new($session{'CurrentUser'}) ;<br>
> $session{'tickets'}->FromSQL($Query) if ($Query);<br>
> to try to force a query, but it seems to return no results. I can't seem to find a place to<br>
> troubleshoot this from, so it's kinda like shooting in the dark. I was hoping maybe somebody<br>
> could give me a little insight as to where I'm mis-stepping. Again, sorry about all the<br>
> trouble and questions about this.<br>
<br>
</div></div>This seems the wrong way to go about it. Also, DBIx::SearchBuilder is<br>
required for RT, so I'd assume you have it installed already.<br>
<br>
I'd build a portlet in local/html/Elements that makes an RT::Tickets<br>
object (which is a DBIx::SearchBuilder object) and calls Limit to<br>
build the queries you want and then passes the result to<br>
CollectionList.<br>
<br>
You can add that portlet to your HomepageComponents and add it to your<br>
front page like any other search once you get there.<br>
<br>
You may want to take apart the other portlets in HomepageCOmponents to<br>
get an idea of what is going on.<br>
<br>
-kevin<br>
<div class="im"><br>
<br>
> On Mon, Feb 21, 2011 at 11:59 AM, Thomas Sibley <[1]<a href="mailto:trs@bestpractical.com">trs@bestpractical.com</a>> wrote:<br>
><br>
> On 21 Feb 2011 11:40, Chris Hall wrote:<br>
> > ..so I've been racking my brain on this one for awhile. Any guidance is<br>
> > appreciated. The aforementioned query works great, I just need to<br>
> > integrate it into RT. However, the RT queries seem to (I'm sure for<br>
> > good reason) strip off alot of that query to the point that it doesn't<br>
> > work. I've tried manually setting it as the query in a custom<br>
> > Results.html, still to no avail. I'm not quite sure how to proceed.<br>
> > Does anyone have any suggestions?<br>
><br>
> RT's user-exposed queries are written in TicketSQL, not SQL. TicketSQL<br>
> is a SQL-like DSL to describe tickets you want to find. TicketSQL<br>
> doesn't search for transactions, only tickets, so you can't express your<br>
</div>> SQL query in TicketSQL. [2]<a href="http://requesttracker.wikia.com/wiki/TicketSQL" target="_blank">http://requesttracker.wikia.com/wiki/TicketSQL</a><br>
<div class="im">><br>
> To show the results of your SQL below in RT, you'd need to modify the<br>
> query to use the DBIx::SearchBuilder API and then display the results.<br>
><br>
> Thomas<br>
</div><div class="im">> > On Fri, Feb 11, 2011 at 11:48 AM, Kenneth Crocker <[3]<a href="mailto:kfcrocker@lbl.gov">kfcrocker@lbl.gov</a><br>
> > <mailto:[4]<a href="mailto:kfcrocker@lbl.gov">kfcrocker@lbl.gov</a>>> wrote:<br>
> ><br>
> > Chris,<br>
> ><br>
> > We do something similar to Payam. We have an Oracle DataBase so we<br>
> > created a "view" of the RT data to allow us to use COGNOS (or other<br>
> > reporting software) to create reports from RT data. Works great.<br>
> ><br>
> > Kenn<br>
> > LBNL<br>
> ><br>
</div><div class="im">> > On Fri, Feb 11, 2011 at 1:32 AM, Payam Poursaied <[5]<a href="mailto:payam@rasana.net">payam@rasana.net</a><br>
> > <mailto:[6]<a href="mailto:payam@rasana.net">payam@rasana.net</a>>> wrote:<br>
> ><br>
> > You could start by database<br>
> ><br>
> > select<br>
> > Transactions.id,ObjectType,ObjectId,Type,Transactions.Created,Name,EmailAddr<br>
> > ess from Transactions<br>
> > inner join Users on Users.id=Transactions.Creator<br>
> > where ObjectType='RT::Ticket' and Type='Comment' and<br>
> > Transactins.Created>='YYYY-MM-DD HH:MM:SS' and<br>
> > Transactions.Created<='YYYY-MM-DD HH:MM:SS';<br>
> ><br>
> > But be aware of 2 things:<br>
> > First: the time is not your localtime, it is GMT0<br>
> > second: if someone rather than your support team is allowed to<br>
> > comment on<br>
> > tickets, you would have them in the report<br>
> ><br>
> ><br>
> ><br>
> ><br>
> > Date: Thu, 10 Feb 2011 16:25:10 -0500<br>
</div>> > From: Chris Hall <[7]<a href="mailto:hiro24@gmail.com">hiro24@gmail.com</a> <mailto:[8]<a href="mailto:hiro24@gmail.com">hiro24@gmail.com</a>>><br>
> > To: [9]<a href="mailto:rt-users@lists.bestpractical.com">rt-users@lists.bestpractical.com</a><br>
> > <mailto:[10]<a href="mailto:rt-users@lists.bestpractical.com">rt-users@lists.bestpractical.com</a>><br>
<div><div></div><div class="h5">> > Subject: Re: [rt-users] search by CommentedOnBy?<br>
> ><br>
> > I hate to necro an old, dead thread but.. this seems to have<br>
> > come up again..<br>
> > from two separate people. I did figure out if I edited the<br>
> > advanced section<br>
> > and added __CommentedOnBy__ I could at least see who the last<br>
> > person was to<br>
> > comment on a ticket, but here's my current problem:<br>
> ><br>
> > Helpdesk manager person has some software that spits out how<br>
> > many calls a<br>
> > given worker does, and wants to compare this with the RT<br>
> > records. For<br>
> > example, Worker A takes 54 calls in a night. Helpdesk manager<br>
> > person wants<br>
> > to check the RT records to see if he has made 54 comments for<br>
> > that given<br>
> > night. Kind of a making sure ppl are doing what they're<br>
> > supposed to be<br>
> > doing.<br>
> ><br>
> > So sometime before the end of the month, I need to figure out a<br>
> > way to form<br>
> > a query to show all comments in a given month. Maybe not "show"<br>
> > them, but<br>
> > just a list to say... Worker A commented on the following<br>
> > tickets this<br>
> > month.<br>
> ><br>
> > Does anybody have any suggestions where I could start with<br>
> > something like<br>
> > this?<br>
> ><br>
> ><br>
> ><br>
><br>
</div></div>> References<br>
><br>
> Visible links<br>
> 1. mailto:<a href="mailto:trs@bestpractical.com">trs@bestpractical.com</a><br>
<div class="im">> 2. <a href="http://requesttracker.wikia.com/wiki/TicketSQL" target="_blank">http://requesttracker.wikia.com/wiki/TicketSQL</a><br>
</div>> 3. mailto:<a href="mailto:kfcrocker@lbl.gov">kfcrocker@lbl.gov</a><br>
> 4. mailto:<a href="mailto:kfcrocker@lbl.gov">kfcrocker@lbl.gov</a><br>
> 5. mailto:<a href="mailto:payam@rasana.net">payam@rasana.net</a><br>
> 6. mailto:<a href="mailto:payam@rasana.net">payam@rasana.net</a><br>
> 7. mailto:<a href="mailto:hiro24@gmail.com">hiro24@gmail.com</a><br>
> 8. mailto:<a href="mailto:hiro24@gmail.com">hiro24@gmail.com</a><br>
> 9. mailto:<a href="mailto:rt-users@lists.bestpractical.com">rt-users@lists.bestpractical.com</a><br>
> 10. mailto:<a href="mailto:rt-users@lists.bestpractical.com">rt-users@lists.bestpractical.com</a><br>
</blockquote></div><br>