Where is the SQL query built for Company Tickets?

English! place to talk about OTRS development, programming and coding
Post Reply
scott-GN
OTRS newbie
Posts: 3
Joined: 01 Jan 2011, 22:36
OTRS Version?: 3.0.4

Where is the SQL query built for Company Tickets?

Post by scott-GN »

I see on these forums that a lot of people have trouble implementing CustomerIDs and Company Tickets. The existing method is clumsy and not scalable.

My goal is to change the SQL query that generates the list of "Company Tickets" to use a "LIKE" statement instead of explicitly matching the ticket customer_id field to each value in CustomerIDs. I want to designate a manager from each Customer be able to see all tickets from his employees only. I intend to enforce that each customer's CustomerID be their e-mail address, then just add the domain name to the CustomerIDs field of the Manager and have that match all employees using that domain in their CustomerID.

I already make changes to the CustomerIDs query in: /opt/otrs/Kernel/System/Ticket.pm. But this didn't work - Company Tickets is not matching on the wildcard for Manger users. Can someone tell me where the SQL query that builds the ticket list for "Company Tickets" is located?

Changes I made to /opt/otrs/Kernel/System/Ticket.pm
# get all customer ids
$SQLExt .= ' AND (';
my @CustomerIDs = $Self->{CustomerUserObject}->CustomerIDs(
User => $Param{CustomerUserID},
);
if (@CustomerIDs) {

# $SQLExt .= 'LOWER(st.customer_id) IN (';
# don't match explicity
$SQLExt .= '(LOWER(st.customer_id) LIKE ';
my $Exists = 0;
for (@CustomerIDs) {
if ($Exists) {
# $SQLExt .= ', ';
# don't use IN , use like
$SQLExt .= 'OR LOWER(st.customer_id) LIKE ';
}
else {
$Exists = 1;
}
# $SQLExt .= "LOWER('" . $Self->{DBObject}->Quote($_) . "')";
# add a wildcard to match all in the domain.
$SQLExt .= "LOWER('%" . $Self->{DBObject}->Quote($_) . "')";
}
$SQLExt .= ') OR ';

}


Changes to add permissions in /opt/otrs/Kernel/System/Ticket/CustomerPermission/CustomerIDCheck.pm

# return 1 if ( lc $Ticket{CustomerID} eq lc $CustomerID );
return 1 if ( lc($Ticket{CustomerID}) =~ /$CustomerID/ );


Thanks,

Scott

Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: Where is the SQL query built for Company Tickets?

Post by Daniel Obee »

Hey Scott,

unfortunately I don't exactly see where you think OTRS company handling is insufficient. Did you enable the customer company module in first place? I'm not too familiar with the feature but I thought it'd do almost everything you wanna achieve.

What you're trying to do seems rather inefficient (adding 'LIKE' to the customer search) will kill a lot of performance. You should really dive into the search before changing something there. Ticket.pm does a part of the job but you should also have a look at the System/DB.pm and the System/CustomerUser/DB.pm.

Greets
Dan

crythias
Moderator
Posts: 10112
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Where is the SQL query built for Company Tickets?

Post by crythias »

I know what Scott's saying, and he's correct.

Assume that a client company (or department) wants to have multiple (>10) employees submit tickets to your otrs system as customers/users. Further assume that the client company wants the submitters of tickets to be isolated from each other knowing what other tickets have been submitted, for security or other reasons (users have individual CustomerID). Then assume that there is a manager that wants to observe those subordinate tickets. If there are employee changes at the client, the manager's customer IDs need to be changed. A big list of customerIDs isn't easily manageable.
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
OTRS Version?: various
Real Name: Daniel Obée
Location: Berlin

Re: Where is the SQL query built for Company Tickets?

Post by Daniel Obee »

hmkay, makes sense. Thank's for the explanation. I'm not sure, but I think the point of interest is the UserLoginSearch in the System/CustomerUser/DB.pm.

Greets across the big blue bathtub
Dan

scott-GN
OTRS newbie
Posts: 3
Joined: 01 Jan 2011, 22:36
OTRS Version?: 3.0.4

Re: Where is the SQL query built for Company Tickets?

Post by scott-GN »

Crythias, -- Thanks, you have re-phrased the problem well.

Dan -- Greetings to you, I appreciate your help with this. I had previously located System/CustomerUser/DB.pm by grepping on "CustomerIDs." I see that the list of CustomerIDs is pulled out here, however, I am still looking for the code where the list of "Company tickets" is pulled to match that list of CustomerIDs.

Best Wishes for the New Year,

Scott

scott-GN
OTRS newbie
Posts: 3
Joined: 01 Jan 2011, 22:36
OTRS Version?: 3.0.4

Re: Where is the SQL query built for Company Tickets?

Post by scott-GN »

I tried contacting OTRS to get a quote for custom development on this, but they don't offer that service. If there are any developers out there that would be interested in taking this on as a paid contract. Please let me know.


Thanks,
Scott

crythias
Moderator
Posts: 10112
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Where is the SQL query built for Company Tickets?

Post by crythias »

<!-- comment deleted for not reading the OP -->
Last edited by crythias on 25 Jan 2011, 23:41, edited 1 time in total.
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

jojo
Moderator
Posts: 14665
Joined: 26 Jan 2007, 14:50
OTRS Version?: Git Master
Contact:

Re: Where is the SQL query built for Company Tickets?

Post by jojo »

Please use the address in my signature for contact with OTRS Group....
"Production": OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com :: Share your ideas

crythias
Moderator
Posts: 10112
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Where is the SQL query built for Company Tickets?

Post by crythias »

<!-- removed for now -->
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

Post Reply