Where is the SQL query built for Company Tickets?

[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]

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]

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.

[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]

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]

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]

<!-- comment deleted for not reading the OP -->

[jojo]

Please use the address in my signature for contact with OTRS Group....

[crythias]

<!-- removed for now -->