Author Topic: Showing Fees in Drupal Views (Read 2403 times)

general_ludd · November 25, 2011, 12:57:40 pm

I am trying to figure out how to show event fees in the views module in Drupal. I have tried the following in views_php field:

Code: [Select]

<? 
/*
$db = mysqli_connect("localhost","USERNAME","PASSWORD","DATABASE");
$sql = "SELECT `opt`.`label` as `label`, `opt`.`value` as `value` FROM `civicrm_option_value` JOIN `civicrm_option_value` as `opt` USING( `option_group_id`) WHERE `civicrm_option_value`.`id` = (SELECT `default_fee_id` FROM `civicrm_event` WHERE `id` = '$row->id' LIMIT 1) ";
$result = mysqli_query($db,$sql);
$output = mysqli_fetch_assoc($result);
print_r($output);
if(count($output)>0){
foreach($output as $row){
echo "<p>" . $row['label'] . ": $" . $row['value'] . "</p>";
}
}
*/
 ?>

but it only shows one field despite the loop. It's ugly and exposes the database access which is a bad idea. It also doesn't work.

Hershel · November 26, 2011, 12:05:58 pm

To execute SQL against the CiviCRM database you should use CRM_Core_DAO::executeQuery . You can find examples here http://wiki.civicrm.org/confluence/display/CRMDOC40/CiviCRM+hook+specification

It could well be that you can get this data via the API, and if so, that's recommended, but if not, the SQL is:

Code: [Select]

 SELECT `label`, `value` 
 FROM `civicrm_option_value` 
 LEFT JOIN `civicrm_event` on `civicrm_event`.`default_fee_id` = `civicrm_option_value`.`option_group_id`
 WHERE `civicrm_event`.`id` = '{$row->id}';

general_ludd · November 27, 2011, 12:28:33 pm

Thanks for the info. I am pretty new to Civi and coding in Drupal. What hook might I use to extract this data for a view? I can understand how I might get it to work with a node_view_hook, but not with a view. Can you point me in the direction of a useful beginner's tutorial for this?

Or better yet, how might I extend the Civi Views module so I can get the fees included? It seems like this might be a really valuable feature for other users if you want to list events and include their fees in the display.

Donald Lobo · November 27, 2011, 03:26:00 pm

views exposes a set of hooks that civicrm implements and hence integrates with views.

the code for these hooks is at: drupal/modules/views/ in your civicrm install directory

Note that there have been significant changes and improvements in CiviCRM v4.1 (alpha coming this week) and Views3 integration. So might be a better bet to use that version

lobo

Hershel · November 28, 2011, 03:04:18 am

Quote from: general_ludd on November 27, 2011, 12:28:33 pm

What hook might I use to extract this data for a view?

I would suggest you customize one of the templates for the View. You can then execute PHP in that template. You can access the templates from the Views edit page.

Eileen · November 28, 2011, 12:13:08 pm

Hi

I just wrote a report that adds fees in. This is a CiviCRM report not a view but here are the relevant parts of the code (note it assumes one fee per event because that's what we needed). The way fees are recorded is difficult to work with because you can't easily join on the event id & if a priceset is involved the fees are stored elsewhere

Code: [Select]

    function from( ) {
        $this->_from = " FROM civicrm_event  "
          . " LEFT JOIN " . $this->_getFeeTable() . " fees ON civicrm_event.id =   fees.event_id" ;
    }

Code: [Select]

   /*
    * Generates a table of fees & returns table name
    * 
    * @return string Name of the table
    */ 
   function _getFeeTable(){
     static $tempTable;
      if(!empty($tempTable)){
        return $tempTable;
      }
       $tempTable = 'temp_fee' . rand(10,99); 
       $tempPayments = CRM_Core_DAO::executeQuery(
       "CREATE TEMPORARY  TABLE $tempTable 
     
SELECT  v.value as fee, v.`label`, g.name, RIGHT(g.name,LENGTH(g.name)-21) as event_id
  FROM civicrm_option_group g,
       civicrm_option_value v
 WHERE g.id = v.option_group_id
   AND g.name LIKE 'civicrm_event.amount.%'
GROUP BY g.name
ORDER BY v.weight ");

       CRM_Core_DAO::executeQuery("ALTER TABLE $tempTable ADD INDEX (event_id)");
       return $tempTable;
       
      
    }

general_ludd · November 28, 2011, 02:18:25 pm

My original query does select all the values I need (nesting a SELECT as part of the WHERE criteria), but when I try extract the results in a view it does not produce the results that the scripts outside of the view context should produce. Maybe this is a views_php issue.

general_ludd · December 02, 2011, 02:25:36 pm

I have solved it! My query was correct, but it was a long time since I last ran a php script without an database abstraction layer. Here is the solution (pasted into a views_php field in the views module in D7).

Code: [Select]

<? 
$db = mysqli_connect("localhost","db_user","db_pass","db_name");
$sql = "SELECT `opt`.`label` as `label`, `opt`.`value` as `value` FROM `civicrm_option_value` JOIN `civicrm_option_value` as `opt` USING( `option_group_id`) WHERE `civicrm_option_value`.`id` = (SELECT `default_fee_id` FROM `civicrm_event` WHERE `id` = '1053') ";

$result = mysqli_query($db, $sql);


if(mysqli_num_rows($result)>0){
while($row = mysqli_fetch_object($result)){

echo "<p>" . $row->label . ": $" . $row->value . "</p>";
}
}
?>

It would still be ideal if there were a way to do this using a module or back-end. Is there a better way to get the data from Civi instead of the raw mysql with passwords etc?

Donald Lobo · December 02, 2011, 05:37:16 pm

you can do something like:

Code: [Select]

  civicrm_initialize( );

  $sql = ....
  $dao =  CRM_Core_DAO::executeQuery( $sql );
   while ( $dao->fetch( ) ) {
     echo ...
   }

Note that you will need civicrm enabled for the above to work. It will only work when bootstrapped with drupal. For it to be a bin script, check the examples in the bin directory

lobo

general_ludd · December 05, 2011, 01:45:18 pm

Here's the code I posted. Unfortunately the query result is not usable for a loop. What is wrong with this code?

Code: [Select]

<? 
civicrm_initialize();
$sql = "SELECT `opt`.`label` as `label`, `opt`.`value` as `value` FROM `civicrm_option_value` JOIN `civicrm_option_value` as `opt` USING( `option_group_id`) WHERE `civicrm_option_value`.`id` = (SELECT `default_fee_id` FROM `civicrm_event` WHERE `id` = '{$row->id}') ";

$dao = CRM_Core_DAO::executeQuery( $sql );

while($row = $dao->fetch() ) {
$output[] = $row->label . ": $" . $row->value ;
}

print implode("<br/>", $output);
?>

Hershel · December 06, 2011, 01:43:55 am

Your SQL is wrong. Please see above in this post on this thead where I already provided the correct SQL.

general_ludd · December 06, 2011, 11:41:28 am

The SQL produces the results I need. The one you suggested did not appear to do that. This is because the tables with the data to be displayed do not have the necessary key information without the nested select (unless there is a column I am not noticing).

Hershel · December 06, 2011, 11:48:03 am

If your SQL works, then what do you mean "query result is not usable for a loop" ?

Try putting

print_r($dao);

inside your while loop to see more...

general_ludd · December 06, 2011, 12:04:39 pm

I realized that my syntax was not correct in my original for the loop. By using the $dao object I was able to achieve both the correct output and not expose the password in any potentially user-accessible parts of the system. Thanks for your help!

Hershel · December 06, 2011, 12:33:21 pm

My pleasure.

CiviCRM Community Forums (archive)

News:

Author Topic: Showing Fees in Drupal Views (Read 2403 times)

general_ludd

Showing Fees in Drupal Views

Hershel

Re: Showing Fees in Drupal Views

general_ludd

Re: Showing Fees in Drupal Views

Donald Lobo

Re: Showing Fees in Drupal Views

Hershel

Re: Showing Fees in Drupal Views

Eileen

Re: Showing Fees in Drupal Views

general_ludd

Re: Showing Fees in Drupal Views

general_ludd

Re: Showing Fees in Drupal Views

Donald Lobo

Re: Showing Fees in Drupal Views

general_ludd

Re: Showing Fees in Drupal Views

Hershel

Re: Showing Fees in Drupal Views

general_ludd

Re: Showing Fees in Drupal Views

Hershel

Re: Showing Fees in Drupal Views

general_ludd

Re: Showing Fees in Drupal Views

Hershel

Re: Showing Fees in Drupal Views