Custom data source in queue?

hallonpower

Can I create a custom data source for my queue?
Here is my_custom_field which I want to make available to the data sources in my queue.

select 
(CASE 
WHEN criteria > 0 THEN 0
ELSE 1
END) as my_custom_field
from ost_ticket t
inner join ost_thread th on t.ticket_id = th.object_id and th.object_type='T'

How would I go about creating this?

KevinTheJedi

hallonpower

You must customize the code to accomplish this. As with any customizations, you are on your own. You can look at the current data sources for examples.

Cheers.

hallonpower

Can you give me a starting point or an example?
Thanks.

KevinTheJedi

hallonpower

You can start with include/class.queue.php. It's way too complicated to explain you'll just have to read the code and follow along.

We also have ERDs here that shows the relations of the tables and columns:

https://docs.osticket.com/en/latest/Developer%20Documentation/Database%20ERDs.html

Cheers.

hallonpower

I will attempt to do some digging into the php file you mentioned.
Say I create a new row in table ost_queue_column table, as below,

is there a file in which I have to go and specify an SQL statement manually to cater for this new entry?

KevinTheJedi

hallonpower

Yes, there will be potentially multiple files you'll need to update, but you'll have to convert that SQL to our custom-ORM statements for best results.

Cheers.

hallonpower

KevinTheJedi Thanks Kevin. I will come back later with more specific questions.

hallonpower

KevinTheJedi
I have a related question regarding query annotations. e.g.
return $query->annotate(array(
'myfield' => (SqlCase::N()
->when(array('tablefieldgt'=>'my value'), 0)
->otherwise(1)
),
));

When table field is greater than 'my value' is easily specified with a text field using 'gt'.
I am having a hard time specifying 'greater than' with regards to SqlFunction::timestampdiff.
Here is my SqlFunction::timestampdiff(new SqlCode('SECOND'), new SqlField('tablefield'), SqlFunction::NOW()).
I want to modify the annotation to include a 'when' my SqlFunction::timestampdiff 'greater than' 'my value'.
How would I do that?

KevinTheJedi

hallonpower

->annotate(array('field' =>
    new SqlExpr(new Q(array(
        'table_field__gt' => 'your value'
    )))
))

Other than that you’ll have to look at further examples in the code. I’m a core dev so I can’t really assist with code modifications as it’s considered support which, with free open source osTicket, is a paid service.

Cheers.

hallonpower

KevinTheJedi
Yes I was aware of table_field__gt.
What is doing my brain in, is when using a function:
->when( SqlFunction::anSQLfunction(parameters) "is greater than 5" , 0)
If you cannot answer, I understand and you can close this thread.
Thanks mate.

hallonpower

Hi Kevin.
I have delved into the depths of your ORM and have come back bruised and battered but still alive. I achieved my goals neverthelsess.
I have a question for you.
Do you have some provision in place to enable additional entries to be added to the getSearchableFields() array?
I would like to add an entry without modifying the actual files (e.g. class.thread.php).

KevinTheJedi

hallonpower

Add the field to either the Ticket Details Form or a Custom Form and it should show up as a searchable Field. Other than that you'd have to modify the code.

Cheers.