1

I have created a API. In this a simple select query is running and data is displaying as JSON format

But I don't how to handle / pass all the conditions to the select query

Parameter = 1 or Parameter <= 1 or Parameter => 1 or Parameter != 1 or,...

enter image description here

Currently my query is as below

Select col1,col2,col3 from civicrm_table

I want to build my select query as

Select col1,col2,col3 from civicrm_table where condition

Here condition may be any one from the list

Is there a simplest way to handle this in Civi API

My API code

<?php

/**
 * Membershipperiod.Get API specification (optional)
 * This is used for documentation and validation.
 *
 * @param array $spec description of fields supported by this API call
 * @return void
 * @see http://wiki.civicrm.org/confluence/display/CRMDOC/API+Architecture+Standards
 */
function _civicrm_api3_membershipperiod_Get_spec(&$spec) {
//  $spec['magicword2']['api.required'] = 1;
  $spec['membership_id']['api.required'] = 1;
}

/**
 * Membershipperiod.Get API
 *
 * @param array $params
 * @return array API result descriptor
 * @see civicrm_api3_create_success
 * @see civicrm_api3_create_error
 * @throws API_Exception
 */
function civicrm_api3_membershipperiod_Get($params) {

$condition="";
if($params['membership_id'])
{
  if(is_array($params['membership_id']))
    {
    foreach($params['membership_id'] as  $key=>$val)
         $val="p.membership_id $key $val";
    }
else
$val="p.membership_id=".$params['membership_id'];
$condition=" and $val";
}


  $query = "SELECT c1.display_name, m.contact_id, c.total_amount,p.membership_id 
FROM civicrm_membership m
RIGHT JOIN civicrm_membership_payment p ON m.id = p.membership_id
INNER JOIN civicrm_contribution c ON c.id = p.contribution_id
INNER JOIN civicrm_contact c1 ON c1.id = m.contact_id $condition";

  $result_array=array();
//    $result_array[]=$params['membership_id'];
  $dao = CRM_Core_DAO::executeQuery($query);
  while ($dao->fetch()) {
    $display_name=$dao->display_name;
    $contact_id=$dao->contact_id;
    $total_amount=$dao->total_amount;
    $membership_id=$dao->membership_id;

    $result_array[]=array("contact_id" => $contact_id,"membership_id" =>$membership_id, "name" => $display_name, "amount_paid" => $total_amount);
   }

$returnValues=$result_array;
    // Spec: civicrm_api3_create_success($values = 1, $params = array(), $entity = NULL, $action = NULL)
    return civicrm_api3_create_success($returnValues, $params, 'NewEntity', 'NewAction');

}
?>

BAO

class CRM_History_BAO_MembershipPeriod extends CRM_History_DAO_MembershipPeriod {

  /**
   * Create a new MembershipPeriod based on array-data
   *
   * @param array $params key-value pairs
   * @return CRM_History_DAO_MembershipPeriod|NULL
   *
  public static function create($params) {
    $className = 'CRM_History_DAO_MembershipPeriod';
    $entityName = 'MembershipPeriod';
    $hook = empty($params['id']) ? 'create' : 'edit';

    CRM_Utils_Hook::pre($hook, $entityName, CRM_Utils_Array::value('id', $params), $params);
    $instance = new $className();
    $instance->copyValues($params);
    $instance->save();
    CRM_Utils_Hook::post($hook, $entityName, $instance->id, $instance);

    return $instance;
  } */

}
2

It looks like you pretty much have it almost done from your screenshot. The where conditions are in the parameters, you have membership_id selected, and you can choose an operator and value for each of the parameters.

Regarding the SELECT col1, col2, col3, if you want to limit to only certain fields you can do that with the "Fields to return" box.

  • I want to create where condition based on the drop down values and input parameters – ManiMuthuPandi Jul 7 '17 at 3:55
  • I'm not sure I understand, is this for a custom api in an extension for CiviCRM that you've created? – mickadoo Jul 7 '17 at 9:31
  • I have created this API using CIVIX, in API function I am wring my own query where I have to pass the conditions dynamically; – ManiMuthuPandi Jul 7 '17 at 13:01
  • Do you see your custom entity in the API explorer with the fields it has in the parameter list? If you do and you're not seeing the selections reflected in the query are you calling the basic functions from inside your custom api functions, you can see examples here – mickadoo Jul 7 '17 at 13:05
  • I have placed my Code in the question. Could you please check and guide – ManiMuthuPandi Jul 7 '17 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.