Custom web service operation for multiple records fetch with custom query

Add new webservice custom method you have to manipulates in 2 tables vtiger_ws_operation , vtiger_ws_operation_parameters and vtiger_ws_operation_seq.

vtiger_ws_operation Table

INSERT INTO `vtiger_ws_operation` ( `name`, `handler_path`, `handler_method`, `type`, `prelogin`) VALUES (‘custom_query_multi_result’, ‘include/Webservices/CustomQueryMultiResult.php’, ‘get_custom_query__multi_result, ‘POST’, 0);

Add parameters for custom operation to vtiger_ws_operation_parameters with a query

INSERT INTO `vtiger_ws_operation_parameters` (`operationid`, `name`, `type`, `sequence`) VALUES (38, ‘fields’, ‘String’, 3);
INSERT INTO `vtiger_ws_operation_parameters` (`operationid`, `name`, `type`, `sequence`) VALUES (38, ‘params’, ‘String’, 2);
INSERT INTO `vtiger_ws_operation_parameters` (`operationid`, `name`, `type`, `sequence`) VALUES (38, ‘query’, ‘String’, 1);

and continue with increament values for the last field

INSERT INTO `vtiger_ws_operation_seq` (`id`) VALUES (38);

Create a file named CustomQueryMultiResult.php in the folder include/Webservices/ In this file there will be a function called get_custom_query__multi_result like this

pquery($query,$params);
$count = $adb->num_rows($result);
$return_arr = array();
for($i = 0; $i < $count; $i++){ foreach ($fields as $key => $value) {
$return_arr[$i][$value] = $adb->query_result($result,$i,$value);
}
}
return $return_arr;
}
?>

How to use custom query for multiple records fetch with custom query

global $current_user,$master_URL,$MasterUsername,$master_Key;

require_once ‘Zend/Loader.php’;
require_once ‘HTTP/Client.php’;

Zend_Loader::loadClass(Zend_Json);
if(!function_exists(‘getBody’))
{
function getBody($response) {
return Zend_Json::decode($response[“body”]);
}
}

//Get Challenge Operation call
$httpc = new HTTP_CLIENT();
$httpc->get(“$master_URL?operation=getchallenge&username=$MasterUsername”);
$response = $httpc->currentResponse();
$jsonResponse = Zend_JSON::decode($response[‘body’]);
if($jsonResponse[‘success’]==false)
die(‘getchallenge failed:’.$jsonResponse[‘error’][‘errorMsg’]);

//Login Operation Call
$challengeToken = $jsonResponse[‘result’][‘token’];
$generatedKey = md5($challengeToken.$master_Key);
$httpc->post(“$master_URL”,
array(‘operation’=>’login’, ‘username’=>$MasterUsername, ‘accessKey’=>$generatedKey), true);
$response = $httpc->currentResponse();
$jsonResponse = Zend_JSON::decode($response[‘body’]);
if($jsonResponse[‘success’]==false)
die(‘login failed:’.$jsonResponse[‘error’][‘errorMsg’]);
$sessionId = $jsonResponse[‘result’][‘sessionName’];
$userId = $jsonResponse[‘result’][‘userId’];

//Multiple records fetch from Custom Query
$query = “SELECT * FROM vtiger_products
INNER JOIN vtiger_crmentity ON vtiger_products.productid = vtiger_crmentity.crmid
WHERE vtiger_crmentity.deleted = 0 AND vtiger_products.prd_type = ‘Plan’
ORDER BY vtiger_crmentity.createdtime”;
$arrayName = array();
//Fiels array for selected column
$fields = array(‘product_no’,’productname’,’qty_per_unit’,’unit_price’,’discontinued’,’module_list’);
$objectJson = Zend_JSON::encode($query);
$objectJsonParam = Zend_JSON::encode($arrayName);
$objectJsonfields = Zend_JSON::encode($fields);
$params = array(“sessionName”=>$sessionId, “operation”=>’custom_query_multi_result’, “query”=>$objectJson, “params”=>$objectJsonParam,”fields”=>$objectJsonfields);
$httpc->post(“$master_URL”, $params, true);
$response = $httpc->currentResponse();
$jsonResponse = Zend_JSON::decode($response[‘body’]);//Result

Enjoy!