Hi, I'm pretty new to osTicket, but have been having a lot of fun building and modding a version for a project im working on, however have got the stage where i need a few hints/push in the right direction :)I have customized the individual users page (within staff section) 'includes/user-view.inc.php' which calls the template file 'templates/tickets.tmpl.php' so now it is displaying three tables (one for each of my helpdesk topics).  However within these tables, i want to include fields from the custom form data that i have assigned for each helptopic.  I think i could use a few pointers for SQL. (all code extracts are from 'includes/templates/tickets.tmpl.php' unless otherwise specified)I've added  'IF(form_entry.object_type = "T",form_entry.id, "") as fe_id ' to the $select variable (~line7)and .' LEFT JOIN '.TABLE_PREFIX.'form_entry form_entry ON (form_entry.object_id = ticket.ticket_id) ' to the $from statement (line19) at the top of the file.so for each ticket I can get the form_entry id related.  (the IF statement is just in case a ticket and user have the same object_id).However, from here i'm not sure how i join the form_entry.id (alias fe_id ) to all the entries in the form_entry_values table, and pull those through into the array for each ticket.  I guess i need to write some SQL to select form_entry_values.field_id and form_entry_values.value where fe_id=form_entry_values.entry_id - but this will return multiple rows so im not sure how to handle JOINing those... any help would be appreciated!!I can provide longer code examples if its helpful....Perhaps this is a long winded way of doing this? Are there functions i could use to short circuit the process?

It's already late here, but what you write seems to be correct for me. A colleague and I wrote an osTicket add on, and we joined stuff like that:

LEFT JOIN ost_form_entry_values on ost_form_entry.id=ost_form_entry_values.entry_id

Based on another coding project I did recently, I had a similar issue, so that multiple lines have been returned. Solution was that I had to add another join to the SQL query so the query was executed correctly and returned the result once as expected. So my guess is that you miss something, e.g. another join or a where clause etc.

Since I am not sure whether this helps you or not (but I hope so) you can have a look at our osTicket add-on here over at github:

https://github.com/Chefkeks/osTicket-Addons/

For the SQL queries, look especially at that file: https://github.com/Chefkeks/osTicket-Addons/blob/master/ldap_user_info/update_user_info.php

Cheers,

Michael

Thanks Michael,As per your suggestion I added the additional line:'form_entry_values.field_id as fev_id, form_entry_values.value as fev_value  ' to the select variable, and then left joined the form_entry_values table:.' LEFT JOIN '.TABLE_PREFIX.'form_entry_values AS form_entry_values ON (form_entry.id = form_entry_values.entry_id) 'so it is correctly pulling through the entry_id, field_id and value, however as expected, it is just returning the last entry. e.g. for a ticket, there are 10 entries in form_entry_values with id '40', but im only getting one field_id (the most recent).  How can i get it to return an array of field_id s and corresponding values ?Thanks for help so far!

So i have been able to get slightly closer using the GROUP_CONCAT function.e.g. GROUP_CONCAT(form_entry_values.value SEPARATOR ", ") as fev_valueThis allows me to return all the values from the custom form, for each ticket, however, for some reason it forces the SQL query to only return one result. so i can pull through a ticket with the correct data, but am now only getting the latest ticket and none of the others.If i take out the group concat it works again... not sure why this is happening.. although my SQL skills are lacking,

So i have fixed this, code below if anyone needs it.On includes/staff/templates/tickets.tmpl.php change:$select ='SELECT ticket.ticket_id,ticket.`number`,ticket.dept_id,ticket.staff_id,ticket.team_id, ticket.user_id '        .' ,dept.dept_name,status.name as status, status.id as status_id,ticket.source,ticket.isoverdue,ticket.isanswered,ticket.created '        .' ,CAST(GREATEST(IFNULL(ticket.lastmessage, 0), IFNULL(ticket.reopened, 0), ticket.created) as datetime) as effective_date '        .' ,CONCAT_WS(" ", staff.firstname, staff.lastname) as staff, team.name as team '        .' ,IF(staff.staff_id IS NULL,team.name,CONCAT_WS(" ", staff.lastname, staff.firstname)) as assigned '        .' ,IF(form_entry.object_type = "T",form_entry.id, "") as form_entry '        .' ,GROUP_CONCAT(form_entry_values.field_id," " ,form_entry_values.value SEPARATOR " @ ") as fev_values'        .' ,IF(ptopic.topic_pid IS NULL, topic.topic, CONCAT_WS(" / ", ptopic.topic, topic.topic)) as helptopic '        .' ,topic.topic_id as helptopic_id, cdata.priority as priority_id, cdata.subject, user.name, email.address as email';$from =' FROM '.TICKET_TABLE.' ticket '      .' LEFT JOIN '.TICKET_STATUS_TABLE.' status ON status.id = ticket.status_id '      .' LEFT JOIN '.USER_TABLE.' user ON user.id = ticket.user_id '      .' LEFT JOIN '.USER_EMAIL_TABLE.' email ON user.id = email.user_id '      .' LEFT JOIN '.USER_ACCOUNT_TABLE.' account ON (ticket.user_id=account.user_id) '      .' LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '      .' LEFT JOIN '.STAFF_TABLE.' staff ON (ticket.staff_id=staff.staff_id) '      .' LEFT JOIN '.TEAM_TABLE.' team ON (ticket.team_id=team.team_id) '      .' LEFT JOIN '.TOPIC_TABLE.' topic ON (ticket.topic_id=topic.topic_id) '      .' LEFT JOIN '.TOPIC_TABLE.' ptopic ON (ptopic.topic_id=topic.topic_pid) '      .' LEFT JOIN '.TABLE_PREFIX.'ticket__cdata cdata ON (cdata.ticket_id = ticket.ticket_id) '      .' LEFT JOIN '.TABLE_PREFIX.'form_entry form_entry ON (form_entry.object_id = ticket.ticket_id) '      .' LEFT JOIN '.TABLE_PREFIX.'form_entry_values form_entry_values ON (form_entry_values.entry_id = form_entry.id) '      .' LEFT JOIN '.PRIORITY_TABLE.' pri ON (pri.priority_id = cdata.priority)';You will also need to change the $query variable to:$query ="$select $from $where  GROUP BY form_entry_values.entry_id ORDER BY ticket.created DESC";Then within the foreach statement for each ticket at the bottom of the page, you can use <?php echo  $row ; ?>to call the data from the custom forms assigned. Note that the field IDs and there entries will be seaparted by ' ' and rows will be separated by ' @ '.  You can then use a php statement to cut the bits of data you need (i havent included this).

WOW! Great post!Thanks for sharing with us *THUMBSUP*

Might as well include my php solution for pulling out the custom form values:I changed the fields in the GROUP_CONCAT to use wacky delimiters to avoid replacing legitimate characters:.' ,GROUP_CONCAT(form_entry_values.field_id,"@,@",form_entry_values.value SEPARATOR "@;@ ") as fev_values'   <?php  // loads the grouped strings into a multidimensional array            $custom_form_data = array_map (function ($_) {return explode ('@,@', $_);}, explode ('@;@', $row));            // If you want to print raw content to debug use: print_r($custom_form_data) ;            foreach ($custom_form_data as $k) { //cycle through array                                  if ($k=='40') $my_field_variable = $k ; // In my case 40 is the id of a specific custom form field that i want to display.            }echo $my_field_variable ;

So now i can just use a bunch of if statements to set all the variables that i need, and echo them into my layout. of course they will be hardcoded, but as this is a bespoke application i'm building its okay.thanks osTicket for a solid open-source platform :)

Write a Reply...