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
Where is the SQL query built for Company Tickets?
-
- Moderator
- Posts: 644
- Joined: 19 Jun 2007, 17:11
- Znuny Version: various
- Real Name: Daniel Obée
- Location: Berlin
Re: Where is the SQL query built for Company Tickets?
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
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
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Where is the SQL query built for Company Tickets?
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.
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 6.0.x (private/testing/public) on Linux with MySQL database.
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
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
-
- Moderator
- Posts: 644
- Joined: 19 Jun 2007, 17:11
- Znuny Version: various
- Real Name: Daniel Obée
- Location: Berlin
Re: Where is the SQL query built for Company Tickets?
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
Greets across the big blue bathtub
Dan
Re: Where is the SQL query built for Company Tickets?
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
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
Re: Where is the SQL query built for Company Tickets?
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
Thanks,
Scott
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Where is the SQL query built for Company Tickets?
<!-- comment deleted for not reading the OP -->
Last edited by crythias on 25 Jan 2011, 23:41, edited 1 time in total.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
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
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
Re: Where is the SQL query built for Company Tickets?
Please use the address in my signature for contact with OTRS Group....
"Production": OTRS™ 8, 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
"Testing": ((OTRS Community Edition)) and git Master
Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Where is the SQL query built for Company Tickets?
<!-- removed for now -->
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
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
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