How-To

Show UDF data in listing screens

To support an unlimited number of UDFs, the UDF data cannot be stored in the booking, it must be stored in a separate table where each row is one UDF's values. If you join the booking table to the udf_values table and you have say 3 UDFs defined, you will get 3 copies of each booking row, one for each UDF.
The only way around this is to use some fancy SQL aggregating and grouping. 

You will need to:

  1. Modify the query that pulls booking data
  2. Modify the view to display the UDF data (the screen is already full so you may wish to replace some exiting columns with your UDF columns.)
 
#UPDATED Jan 23/15. This was originally written for ABPro 2, all file and table references have been changed to ABPro 3 #
 
 
Examples


Example #1 My Bookings

1. Modify the query 

In the case of the My Booking screen the query to fetch data is in the view itself.
File:\components\com_rsappt_pro3\views\mybookings\tmpl\default.php

 

Around line 101 look for:

// find requests

$sql = "SELECT #__sv_apptpro3_requests.*, #__sv_apptpro3_resources.resource_admins, ".
"#__sv_apptpro3_resources.name as resname, ".
//"#__sv_apptpro3_services.name as ServiceName, ".
"CONCAT(#__sv_apptpro3_requests.startdate,#__sv_apptpro3_requests.starttime) as startdatetime, ".
" IF(CONCAT(#__sv_apptpro3_requests.startdate, ' ', #__sv_apptpro3_requests.starttime) > Now(),'no','yes') as expired, ";
if($apptpro_config->timeFormat == "12"){
$sql = $sql." DATE_FORMAT(#__sv_apptpro3_requests.startdate, '%a %b %e, %Y') as display_startdate, ".
"DATE_FORMAT(#__sv_apptpro3_requests.starttime, '%l:%i %p') as display_starttime, ".
"DATE_FORMAT(#__sv_apptpro3_requests.enddate, '%b %e, %Y') as display_enddate, ".
"DATE_FORMAT(#__sv_apptpro3_requests.endtime, '%l:%i %p') as display_endtime ";
} else {
$sql = $sql." DATE_FORMAT(#__sv_apptpro3_requests.startdate, '%a %b %e, %Y') as display_startdate, ".
"DATE_FORMAT(#__sv_apptpro3_requests.starttime, '%k:%i') as display_starttime, ".
"DATE_FORMAT(#__sv_apptpro3_requests.enddate, '%b %e, %Y') as display_enddate, ".
"DATE_FORMAT(#__sv_apptpro3_requests.endtime, '%k:%i') as display_endtime ";
}
$sql = $sql." FROM #__sv_apptpro3_requests INNER JOIN #__sv_apptpro3_resources ".
"ON #__sv_apptpro3_requests.resource = #__sv_apptpro3_resources.id_resources ".
//" INNER JOIN #__sv_apptpro3_services ON #__sv_apptpro3_requests.service = #__sv_apptpro3_services.id_services ".
"WHERE request_status!='deleted' AND ";
if($filter != ""){
$sql = $sql." request_status='".$filter."' AND ";
}
if($startdateFilter != ""){
$sql = $sql." startdate>='".$startdateFilter."' AND ";
}
if($enddateFilter != ""){
$sql = $sql." enddate<='".$enddateFilter."' AND ";
}
$sql = $sql."#__sv_apptpro3_requests.user_id = ".$user->id.
// " AND CONCAT(#__sv_apptpro3_requests.startdate, ' ', #__sv_apptpro3_requests.starttime) >= NOW() ".
" ORDER BY ".$ordering.' '.$direction;

 

Add the red code:

// find requests
$sql = "SELECT #__sv_apptpro3_requests.*, #__sv_apptpro3_resources.resource_admins, ".

"#__sv_apptpro3_resources.name as resname, ".
//"#__sv_apptpro3_services.name as ServiceName, ".

"GROUP_CONCAT(CASE #__sv_apptpro3_udfvalues.udf_id WHEN 3 THEN #__sv_apptpro3_udfvalues.udf_value END) as Fruit, ".

"GROUP_CONCAT(CASE #__sv_apptpro3_udfvalues.udf_id WHEN 6 THEN #__sv_apptpro3_udfvalues.udf_value END) as Cell, ".

"CONCAT(#__sv_apptpro3_requests.startdate,#__sv_apptpro3_requests.starttime) as startdatetime, ".

" IF(CONCAT(#__sv_apptpro3_requests.startdate, ' ', #__sv_apptpro3_requests.starttime) > Now(),'no','yes') as expired, ";
if($apptpro_config->timeFormat == "12"){
$sql = $sql." DATE_FORMAT(#__sv_apptpro3_requests.startdate, '%a %b %e, %Y') as display_startdate, ".
"DATE_FORMAT(#__sv_apptpro3_requests.starttime, '%l:%i %p') as display_starttime, ".
"DATE_FORMAT(#__sv_apptpro3_requests.enddate, '%b %e, %Y') as display_enddate, ".
"DATE_FORMAT(#__sv_apptpro3_requests.endtime, '%l:%i %p') as display_endtime ";
} else {
$sql = $sql." DATE_FORMAT(#__sv_apptpro3_requests.startdate, '%a %b %e, %Y') as display_startdate, ".
"DATE_FORMAT(#__sv_apptpro3_requests.starttime, '%k:%i') as display_starttime, ".
"DATE_FORMAT(#__sv_apptpro3_requests.enddate, '%b %e, %Y') as display_enddate, ".
"DATE_FORMAT(#__sv_apptpro3_requests.endtime, '%k:%i') as display_endtime ";
}
$sql = $sql." FROM #__sv_apptpro3_requests INNER JOIN #__sv_apptpro3_resources ".
"ON #__sv_apptpro3_requests.resource = #__sv_apptpro3_resources.id_resources ".

" LEFT JOIN #__sv_apptpro3_udfvalues ON request_id = #__sv_apptpro3_requests.id_requests ".

//" INNER JOIN #__sv_apptpro3_services ON #__sv_apptpro3_requests.service = #__sv_apptpro3_services.id_services ".
"WHERE request_status!='deleted' AND ";
if($filter != ""){
$sql = $sql." request_status='".$filter."' AND ";
}
if($startdateFilter != ""){
$sql = $sql." startdate>='".$startdateFilter."' AND ";
}
if($enddateFilter != ""){
$sql = $sql." enddate<='".$enddateFilter."' AND ";
}
$sql = $sql."#__sv_apptpro3_requests.user_id = ".$user->id.
// " AND CONCAT(#__sv_apptpro3_requests.startdate, ' ', #__sv_apptpro3_requests.starttime) >= NOW() ".

" GROUP BY #__sv_apptpro3_requests.id_requests ".

" ORDER BY ".$ordering.' '.$direction;

 

In the above code I am joining to the udf_values table and grabbing two UDFs, Favourite Fruit (udf id #3) and 'Cell' (udf id #6).
I am naming the returned data fields 'Fruit' and 'Cell'. These names will be used below to display the data.

 

 

Modify the view

You may prefer to replace an existing display field rather than add a new field just for space consideration.

For this example I will replace the 'Booked Seats' column with the UDF Favourite Fruit.

The headers for the the bookings display start in the same file around line 307.

The booked_seats header is..

<th class="title" align="center"><?php echo JHTML::_( 'grid.sort', JText::_('RS1_MYBOOKINGS_SCRN_SEATS_HEAD'), 'booked_seats', $direction, $ordering); ?></th>

 

Change the header to:
<th class="title" align="center"><?php echo JHTML::_( 'grid.sort', 'Fav Fruit', 'fruit', $direction, $ordering); ?></th>

 

Now change the data section of the display a few lines below...
      <td align="center"><?php echo $row->booked_seats; ?> </td>

to:
      <td align="center"><?php echo $row->Fruit; ?> </td>

 

Advanced Admin Appointments List

1. Modify the query 

This is a bit different in that the query to get data is in the Model file, a more normal place for it to be.
File: \components\com_rsappt_pro3\models\requests.php

Around line 231 look for:

$query = ' SELECT '.
'#__sv_apptpro3_requests.*, #__sv_apptpro3_resources.name AS '.
'ResourceName, #__sv_apptpro3_services.name AS ServiceName, '.
'#__sv_apptpro3_categories.name AS CategoryName, '.
"CONCAT(#__sv_apptpro3_requests.startdate,#__sv_apptpro3_requests.starttime) as startdatetime, ".
"DATE_FORMAT(#__sv_apptpro3_requests.startdate, '%a %b %e ') as display_startdate, ";
// if($apptpro_config->timeFormat == "12"){
// $query .= "DATE_FORMAT(#__sv_apptpro3_requests.starttime, ' %h:%i %p') as display_starttime, ";
// } else {
$query .= "DATE_FORMAT(#__sv_apptpro3_requests.starttime, ' %H:%i') as display_starttime, ";
// }
$query .= '#__sv_apptpro3_paypal_transactions.id_paypal_transactions AS id_transaction '.
'FROM ('.
'#__sv_apptpro3_requests LEFT JOIN '.
'#__sv_apptpro3_resources ON #__sv_apptpro3_requests.resource = '.
'#__sv_apptpro3_resources.id_resources LEFT JOIN '.
'#__sv_apptpro3_services ON #__sv_apptpro3_requests.service = '.
'#__sv_apptpro3_services.id_services LEFT JOIN '.
'#__sv_apptpro3_categories ON #__sv_apptpro3_resources.category_id = '.
'#__sv_apptpro3_categories.id_categories LEFT JOIN '.
'#__sv_apptpro3_paypal_transactions ON '.
'#__sv_apptpro3_paypal_transactions.custom = '.
'#__sv_apptpro3_requests.id_requests) '.
' WHERE #__sv_apptpro3_resources.resource_admins LIKE \'%|'.$user->id.'|%\' ';

if($filter != ""){
$query = $query." AND ".$filter;
}

$query = $query.' '.$orderby;

 

Add the red code:

$query = ' SELECT '.
'#__sv_apptpro3_requests.*, #__sv_apptpro3_resources.name AS '.
'ResourceName, #__sv_apptpro3_services.name AS ServiceName, '.
'#__sv_apptpro3_categories.name AS CategoryName, '.

'GROUP_CONCAT(CASE #__sv_apptpro3_udfvalues.udf_id WHEN 3 THEN #__sv_apptpro3_udfvalues.udf_value END) as Fruit, '.

"CONCAT(#__sv_apptpro3_requests.startdate,#__sv_apptpro3_requests.starttime) as startdatetime, ".
"DATE_FORMAT(#__sv_apptpro3_requests.startdate, '%a %b %e ') as display_startdate, ";
// if($apptpro_config->timeFormat == "12"){
// $query .= "DATE_FORMAT(#__sv_apptpro3_requests.starttime, ' %h:%i %p') as display_starttime, ";
// } else {
$query .= "DATE_FORMAT(#__sv_apptpro3_requests.starttime, ' %H:%i') as display_starttime, ";
// }
$query .= '#__sv_apptpro3_paypal_transactions.id_paypal_transactions AS id_transaction '.
'FROM ('.
'#__sv_apptpro3_requests LEFT JOIN '.
'#__sv_apptpro3_resources ON #__sv_apptpro3_requests.resource = '.
'#__sv_apptpro3_resources.id_resources LEFT JOIN '.
'#__sv_apptpro3_services ON #__sv_apptpro3_requests.service = '.
'#__sv_apptpro3_services.id_services LEFT JOIN '.
'#__sv_apptpro3_categories ON #__sv_apptpro3_resources.category_id = '.
'#__sv_apptpro3_categories.id_categories LEFT JOIN '.
'#__sv_apptpro3_paypal_transactions ON '.
'#__sv_apptpro3_paypal_transactions.custom = '.
'#__sv_apptpro3_requests.id_requests '.   <-- note: remove the  .

'LEFT JOIN #__sv_apptpro3_udfvalues ON request_id = #__sv_apptpro3_requests.id_requests ) '.

' WHERE #__sv_apptpro3_resources.resource_admins LIKE \'%|'.$user->id.'|%\' ';

if($filter != ""){
$query = $query." AND ".$filter;
}

$query = $query.' GROUP BY #__sv_apptpro3_requests.id_requests ';

$query = $query.' '.$orderby;

In the above code I am joining to the udf_values table and grabbing the UDF Favourite Fruit (udf id #3). 
I am naming the returned data field as 'Fruit'. This name will be used below to display the data.

  

Modify the View

 
File: \components\com_rsappt_pro3\views\advadmin\tmpl\default.php
 

You may prefer to replace an existing display field rather than add a new field just for space consideration.

For this example I will replace the 'ServiceName' column with the UDF Favourite Fruit.

The headers for the the bookings display start around line 594.

The ServiceName header is..

<th class="svtitle" align="left"><?php echo JHTML::_( 'grid.sort', JText::_('RS1_ADMIN_SCRN_SERVICE_COL_HEAD'), 'ServiceName', $this->lists['order_Dir_req'], $this->lists['order_req'], 'req_'); ?></th>

Change to:

<th class="svtitle" align="left"><?php echo JHTML::_( 'grid.sort', 'Fav Fruit', 'Fruit', $this->lists['order_Dir_req'], $this->lists['order_req'], 'req_'); ?></th>

 

Now change the data section of the display a few lines below...
<td align="left"><?php echo JText::_(stripslashes($row->ServiceName)); ?> </td>

Change to:

<td align="left"><?php echo $row->Fruit; ?> </td>

 

 

 

Front Desk, Week and Day views

1. Modify the query 

For the Front Desk everything happens in one file:
\components\com_rsappt_pro3\svcalendar.php

 
Around line 607 792 look for:

$sql = "SELECT #__sv_apptpro3_requests.*, #__sv_apptpro3_resources.resource_admins, #__sv_apptpro3_resources.id_resources as res_id, ".
    "#__sv_apptpro3_resources.max_seats, #__sv_apptpro3_resources.name as resname, #__sv_apptpro3_services.name AS ServiceName, ".
    // "#__sv_apptpro3_categories.name AS CategoryName, ".
    "#__sv_apptpro3_resources.id_resources as resid, DATE_FORMAT(#__sv_apptpro3_requests.startdate, '%a %b %e ') as display_startdate, ";
    if($apptpro_config->timeFormat == '24'){
        $sql .=" DATE_FORMAT(#__sv_apptpro3_requests.starttime, ' %H:%i') as display_starttime, ";
        $sql .=" DATE_FORMAT(#__sv_apptpro3_requests.endtime, ' %H:%i') as display_endtime ";
    } else {
        $sql .=" DATE_FORMAT(#__sv_apptpro3_requests.starttime, ' %l:%i %p') as display_starttime, ";
        $sql .=" DATE_FORMAT(#__sv_apptpro3_requests.endtime, ' %l:%i %p') as display_endtime ";
    }
    $sql .= " FROM ( ".
    '#__sv_apptpro3_requests LEFT JOIN '.
    '#__sv_apptpro3_resources ON #__sv_apptpro3_requests.resource = '.
    '#__sv_apptpro3_resources.id_resources LEFT JOIN '.
    // '#__sv_apptpro3_categories ON #__sv_apptpro3_requests.category = '.
    // '#__sv_apptpro3_categories.id_categories LEFT JOIN '.
    '#__sv_apptpro3_services ON #__sv_apptpro3_requests.service = '.
    '#__sv_apptpro3_services.id_services ) '.
    "WHERE ";
... 

Add the red code.

$sql = "SELECT #__sv_apptpro3_requests.*, #__sv_apptpro3_resources.resource_admins, #__sv_apptpro3_resources.id_resources as res_id, ".
    "#__sv_apptpro3_resources.max_seats, #__sv_apptpro3_resources.name as resname, #__sv_apptpro3_services.name AS ServiceName, ". 
    // "#__sv_apptpro3_categories.name AS CategoryName, ". 

"GROUP_CONCAT(CASE #__sv_apptpro3_udfvalues.udf_id WHEN 3 THEN #__sv_apptpro3_udfvalues.udf_value END) as Fruit, ".

    "#__sv_apptpro3_resources.id_resources as resid, DATE_FORMAT(#__sv_apptpro3_requests.startdate, '%a %b %e ') as display_startdate, ";
    if($apptpro_config->timeFormat == '24'){
        $sql .=" DATE_FORMAT(#__sv_apptpro3_requests.starttime, ' %H:%i') as display_starttime, ";
        $sql .=" DATE_FORMAT(#__sv_apptpro3_requests.endtime, ' %H:%i') as display_endtime ";
    } else {
        $sql .=" DATE_FORMAT(#__sv_apptpro3_requests.starttime, ' %l:%i %p') as display_starttime, ";
        $sql .=" DATE_FORMAT(#__sv_apptpro3_requests.endtime, ' %l:%i %p') as display_endtime ";
    } 
    $sql .= " FROM ( ".
    '#__sv_apptpro3_requests LEFT JOIN '.
    '#__sv_apptpro3_resources ON #__sv_apptpro3_requests.resource = '.
    '#__sv_apptpro3_resources.id_resources LEFT JOIN '. 
    // '#__sv_apptpro3_categories ON #__sv_apptpro3_requests.category = '.
    // '#__sv_apptpro3_categories.id_categories LEFT JOIN '.
    '#__sv_apptpro3_services ON #__sv_apptpro3_requests.service = '.
    '#__sv_apptpro3_services.id_services '.   <-- note: remove the  

'LEFT JOIN #__sv_apptpro3_udfvalues ON request_id = #__sv_apptpro3_requests.id_requests ) '.

    "WHERE ";
... 

 
Around line 664 855 look for:

$sql .= " ORDER BY startdate, starttime";

Add the red code:
 
$sql = $sql.' GROUP BY #__sv_apptpro3_requests.id_requests ';
$sql .= " ORDER BY startdate, starttime";
 
 

Modify the View

For this example I will replace the 'ServiceName' column with the UDF Favourite Fruit.

The file has 3 sections; month, week and day.

 

For the Week view, around line 441 513 look for:

$s .= " <td width=\"15%\" align=\"left\"> ".stripslashes($booking->ServiceName)."</td>";

Change to:

$s .= " <td width=\"15%\" align=\"left\">".$booking->Fruit."</td>";

 

 

For the Day View, around line 560 595 look for:

$s .= " <td align=\"left\"> ".stripslashes($booking->ServiceName)."</td>\n";

Change to:

$s .= "  <td align=\"left\"> ".$booking->Fruit."</td>\n";

 

 

 

Passenger Manifest

1. Modify the query 

In the case of the Manifest screen the query to fetch data is in the view itself.
File:\components\com_rsappt_pro3\views\display_manifest\tmpl\form.php

 

Around line 75 look for the query, and add the red code:

// find requests
$sql = "SELECT ".
"#__sv_apptpro3_requests.*, #__sv_apptpro3_requests.payment_status, ".
"#__sv_apptpro3_seat_types.seat_type_label, ".
"#__sv_apptpro3_seat_counts.seat_type_qty, ".

"GROUP_CONCAT(CASE #__sv_apptpro3_udfvalues.udf_id WHEN 3 THEN #__sv_apptpro3_udfvalues.udf_value END) as Fruit, ".

"GROUP_CONCAT(CASE #__sv_apptpro3_udfvalues.udf_id WHEN 6 THEN #__sv_apptpro3_udfvalues.udf_value END) as Cell, ".

"#__sv_apptpro3_seat_types.ordering ".
"FROM ".
"#__sv_apptpro3_requests LEFT JOIN ".
"#__sv_apptpro3_seat_counts ON #__sv_apptpro3_requests.id_requests = ".
"#__sv_apptpro3_seat_counts.request_id LEFT JOIN ".
"#__sv_apptpro3_seat_types ON #__sv_apptpro3_seat_counts.seat_type_id = ".
"#__sv_apptpro3_seat_types.id_seat_types ".

" LEFT JOIN #__sv_apptpro3_udfvalues ON #__sv_apptpro3_udfvalues.request_id = #__sv_apptpro3_requests.id_requests ".

"WHERE ".
"#__sv_apptpro3_requests.resource = '".$database->escape($resid)."' AND ".
"#__sv_apptpro3_requests.startdate = '".$database->escape($startdate)."' AND ".
"#__sv_apptpro3_requests.starttime = '".$database->escape($starttime)."' ".

" GROUP BY #__sv_apptpro3_requests.id_requests, #__sv_apptpro3_seat_types.id_seat_types ".

"ORDER BY ".
"#__sv_apptpro3_requests.name, #__sv_apptpro3_requests.id_requests, #__sv_apptpro3_seat_types.ordering";

 

In the above we are adding UDF ID #3 as 'Fruit' and UDF ID #6 as 'Cell'.

 

Modify the View

For this example I will add Fruit column with the UDF Favourite Fruit.

Around line 194 look for the header code and add red..

<tr class="adminheading" bgcolor="#F4F4F4">
<th class="sv_title" align="left" width="5%">&nbsp;</th>
<th class="sv_title" align="left"><?php echo JText::_('RS1_MANIFEST_NAME_COL_HEAD'); ?></th>
<th class="sv_title" align="left"><?php echo JText::_('RS1_MANIFEST_PHONE_COL_HEAD'); ?></th>
<th class="sv_title" align="left"><?php echo JText::_('RS1_MANIFEST_EMAIL_COL_HEAD'); ?></th>
<th class="sv_title" align="left"><?php echo JText::_('RS1_MANIFEST_STATUS_COL_HEAD'); ?></th>
<th class="sv_title" align="left"><?php echo JText::_('RS1_MANIFEST_PAYMENT_COL_HEAD'); ?></th>
<th class="sv_title" align="left"><?php echo JText::_('RS1_MANIFEST_SEAT_TYPE_COL_HEAD'); ?></th>
<th class="sv_title" align="left"><?php echo JText::_('RS1_MANIFEST_SEAT_COUNT_COL_HEAD'); ?></th>
<th class="sv_title" align="left"><?php echo JText::_('Fruit'); ?></th>
</tr>

Around line 249 look for the data section and add the red code..

<td align="left" <?php echo ($show_booking_header==true?" style=\"border-top:solid 1px\"":"")?>><?php echo JText::_($row->seat_type_label); ?> </td>
<td align="left" <?php echo ($show_booking_header==true?" style=\"border-top:solid 1px\"":"")?>><?php echo $row->seat_type_qty; ?> </td>
<td align="left" style="border-top:solid 1px"><?php echo $row->Fruit; ?> </td>

 

That's it.