Location: PHPKode > projects > SOA For Virtuemart (WebServices) > com_vm_soa/admin/services/VM_SQLQueriesService.php
<?php

define( '_VALID_MOS', 1 );
define( '_JEXEC', 1 );

/**
 * Virtuemart Category SOA Connector
 *
 * Virtuemart SQLQueries SOA Connector (Provide functions execute generic SQL queries, INSERT, UPDATE, SELECT queries)
 * The return classe is a "SQLResult" 
 * 
 *
 * @package    com_vm_soa
 * @subpackage component
 * @author     Mickael cabanas (cabanas.mickael|at|gmail.com)
 * @copyright  2010 Mickael Cabanas
 * @license    http://www.gnu.org/copyleft/gpl.html GNU/GPL
 * @version    $Id:$
 */
 
global $mosConfig_absolute_path, $sess;
global $jscook_type, $jscookMenu_style, $jscookTree_style;

$mosConfig_absolute_path= realpath( dirname(__FILE__).'/../../../..' );

// Load the joomla main cfg
if( file_exists(dirname(__FILE__).'/configuration.php' )) {
	require_once( $mosConfig_absolute_path.'/configuration.php' );
	
} else {
	require_once( $mosConfig_absolute_path.'/configuration.php');
}

// Load the virtuemart main parse code
if( file_exists(dirname(__FILE__).'/../../../../components/com_virtuemart/virtuemart_parser.php' )) {
	require_once( dirname(__FILE__).'/../../../../components/com_virtuemart/virtuemart_parser.php' );
	$mosConfig_absolute_path = realpath( dirname(__FILE__).'/../..' );
} else {
	require_once( dirname(__FILE__).'/../../../../components/com_virtuemart/virtuemart_parser.php');
}

require_once(CLASSPATH.'ps_product_category.php');

include('../vm_soa_conf.php');

global $mosConfig_live_site;
$URL_BASE ='';
if( $mosConfig_live_site[strlen( $mosConfig_live_site)-1] == '/' ) {
	$URL_BASE = $mosConfig_live_site;
}
else {
	$URL_BASE = $mosConfig_live_site.'/';
}


/**
 * Class SQLResult
 *
 * Class "SQLResult" 
 *
 * @author     Mickael cabanas (cabanas.mickael|at|gmail.com)
 * @copyright  Mickael cabanas
 * @license    http://www.gnu.org/copyleft/gpl.html GNU/GPL
 * @version    Release:
 */
	class SQLResult {
		public $columnsAndValues;

		//constructeur
		function __construct($columnsAndValues) {
			$this->columnsAndValues = $columnsAndValues;
		}
	}
 
 /**
 * Class SQLResult
 *
 * Class "SQLResult" 
 *
 * @author     Mickael cabanas (cabanas.mickael|at|gmail.com)
 * @copyright  Mickael cabanas
 * @license    http://www.gnu.org/copyleft/gpl.html GNU/GPL
 * @version    Release:
 */
	class columnAndValue {
		public $idx="";
		public $column="";
		public $value="";

		//constructeur
		function __construct($idx,$column,$value) {
			$this->idx = $idx;
			$this->column = $column;
			$this->value = $value;
		}
	}
 /**
 * Class SQLResult
 *
 * Class "SQLResult" with attribute : id, name, description,  image, fulliamage , parent category
 * attributes, parent produit, child id)
 *
 * @author     Mickael cabanas (cabanas.mickael|at|gmail.com)
 * @copyright  Mickael cabanas
 * @license    http://www.gnu.org/copyleft/gpl.html GNU/GPL
 * @version    Release:
 */
	/*class SQLSelectRequest {
		public $table="";
		public $columns;
		public $whereClause="";

		//constructeur
		function __construct($table,$columns,$whereClause) {
			$this->table = $table;
			
			$count = count($columns->column);
			for ($i = 0; $i < $count; $i++) {
				$this->columns[] = $columns->column[$i];	
			}
			
			$this->whereClause = $whereClause;
		}
	}*/
 
	/**
    * This function get Childs of a category for a category ID
	* (expose as WS)
    * @param string The id of the category
    * @return array of Categories
   */
	function ExecuteSQLSelectQuery($params) {
	
		include('../vm_soa_conf.php');
	
		$SQLSelectRequest= $params;
		/* Authenticate*/
		$result = onAdminAuthenticate($params->loginInfo->login, $params->loginInfo->password);
		
		if ($conf['auth_sql_select']=="off"){
			$result = "true";
		}	
		
		//Auth OK
		if ($result == "true"){
	
			$SQLSelectRequest= $_SQLSelectRequest;
			
			$q = "SELECT " ;//FROM #__{vm}_category WHERE 1 ";
			$strTmp;
						
			if (is_array($params->columns->column)){
				$count = count($params->columns->column);
				for ($i = 0; $i < $count; $i++) {
					if ($i==$count-1){
						$strTmp .= $params->columns->column[$i]." ";
					}else{
						$strTmp .= $params->columns->column[$i].", ";
					}
				}
			}else {
					$count = 1;
					$strTmp .= $params->columns->column." ";
			}
			
			$q .= $strTmp;
			$q .= " FROM $params->table ";
			$q .= $params->whereClause;

			$db = new ps_DB;
			$db->setQuery($q);
			$db->query();
			
			while( $db->next_record() ) {
				$strResult=null;
				$arrayCol;
				$strResult;
				
				if ($count == 1){
					$columnAndValue = new columnAndValue(0,$params->columns->column,$db->f($params->columns->column));
					$columnAndValueArray[] = $columnAndValue;
				
				} else {
					for ($i = 0; $i < $count; $i++) {
						$columnAndValue = new columnAndValue($i,$params->columns->column[$i],$db->f($params->columns->column[$i]));
						$columnAndValueArray[] = $columnAndValue;
						/*$arrayCol=  array( $params->columns->column[$i] =>$db->f($params->columns->column[$i]));
						$strResult .=  $params->columns->column[$i]." : ".$db->f($params->columns->column[$i])." | ";*/
					}
				}
				$SQLResult= new SQLResult($columnAndValueArray);
				$resultArray[] = $SQLResult;
				$columnAndValueArray=null;
			}
			//$SQLResult2= new SQLResult($q);
			//$resultArray[]=$SQLResult2;
			
			$errMsg=  $db->getErrorMsg();
			
			if ($errMsg==null){
				return $resultArray;
			} else {
				return new SoapFault("JoomlaExecuteSQLSelectQueryFault", "cannot execute SQL Select Query  ".$q." | ERRLOG : ".$errMsg);				
			}

			
			
		}else if ($result== "false"){
			return new SoapFault("JoomlaServerAuthFault", "Autification KO for : ".$params->loginInfo->login);
		}else if ($result == "no_admin"){
			return new SoapFault("JoomlaServerAuthFault", "User is not a Super Administrator : ".$params->loginInfo->login);
		}else{
			return new SoapFault("JoomlaServerAuthFault", "User does not exist : ".$params->loginInfo->login);
		}		
	
	}
	
	/**
    * This function execute a SQL querie
	* (expose as WS)
    * @param string the SQL request
    * @return array resultSet
   */
	function ExecuteSQLQuery($params) {
	
		include('../vm_soa_conf.php');
		
		/* Authenticate*/
		$result = onAdminAuthenticate($params->loginInfo->login, $params->loginInfo->password);
		
		if ($conf['auth_sql_sqlrqst']=="off"){
			$result = "true";
		}	
		
		//Auth OK
		if ($result == "true"){	
		
			$q = $params->sqlRequest;
			$db = new ps_DB;
			$db->setQuery($q);
			$db->query();
			
			while( $db->next_record() ) {
				$strResult=null;
				$arrayCol;
				$strResult;
				
				$row=  $db->get_row();
				
				$i=0;
				foreach($row as $cle=>$valeur)
				{
					$columnAndValue = new columnAndValue($i,$cle,$valeur);
					$columnAndValueArray[] = $columnAndValue;
					$i++;
					//$strResult .= $cle.' : '.$valeur.' | ';
				} 
				$SQLResult= new SQLResult($columnAndValueArray);
				$resultArray[] =$SQLResult;
				$columnAndValueArray=null;
			}
			
			$errMsg=  $db->getErrorMsg();
			
			if ($errMsg==null){
				return $resultArray;
			} else {
				return new SoapFault("JoomlaExecuteSQLQueryFault", "cannot execute SQL Query  ".$params->sqlRequest." | ERRLOG : ".$errMsg);				
			}

			
		
		}else if ($result== "false"){
			return new SoapFault("JoomlaServerAuthFault", "Autification KO for : ".$params->loginInfo->login);
		}else if ($result == "no_admin"){
			return new SoapFault("JoomlaServerAuthFault", "User is not a Super Administrator : ".$params->loginInfo->login);
		}else{
			return new SoapFault("JoomlaServerAuthFault", "User does not exist : ".$params->loginInfo->login);
		}		
		
	}
	
		/**
    * This function execute a SQL insert Queries
	* (expose as WS)
    * @param string The id of the category
    * @return array of Categories
   */
	function ExecuteSQLInsertQuery($params) {
	
		include('../vm_soa_conf.php');

		/* Authenticate*/
		$result = onAdminAuthenticate($params->loginInfo->login, $params->loginInfo->password);
		
		if ($conf['auth_sql_insert']=="off"){
			$result = "true";
		}	
		
		//Auth OK
		if ($result == "true"){
	
			//$SQLSelectRequest= $_SQLSelectRequest;
			$type='INSERT';
			;
			if (is_array($params->columns->column)){
				$count = count($params->columns->column);
				for ($i = 0; $i < $count; $i++) {
					
					$values[$params->columns->column[$i]]=$params->values->value[$i];
				}
			}else {
					$values[$params->columns->column]=$params->values->value;
			}
			$db = new ps_DB;
			$db->buildQuery($type,$params->table,$values,$params->whereClause);
			$result = $db->query();
			$errMsg=  $db->getErrorMsg();
			
			if ($errMsg==null){
				$SQLResult= new SQLResult("INSERT OK in table : ".$params->table);
				$resultArray[] =$SQLResult;
				return $resultArray;
			}else{
				return new SoapFault("JoomlaSQLInsertFault", "cannot execute INSERT into ".$params->table." | ERRLOG : ".$errMsg);				
			}
			//$resultArray[] =new SQLResult($q);
			

		}else if ($result== "false"){
			return new SoapFault("JoomlaServerAuthFault", "Autification KO for : ".$params->loginInfo->login);
		}else if ($result == "no_admin"){
			return new SoapFault("JoomlaServerAuthFault", "User is not a Super Administrator : ".$params->loginInfo->login);
		}else{
			return new SoapFault("JoomlaServerAuthFault", "User does not exist : ".$params->loginInfo->login);
		}		
	
	}
	
		/**
    * This function execute a SQL Update querie
	* (expose as WS)
    * @param string the SQL update request
    * @return result
   */
	function ExecuteSQLUpdateQuery($params) {
	
		include('../vm_soa_conf.php');
	
		/* Authenticate*/
		$result = onAdminAuthenticate($params->loginInfo->login, $params->loginInfo->password);
		
		if ($conf['auth_sql_update']=="off"){
			$result = "true";
		}	
		
		//Auth OK
		if ($result == "true"){
	
			$type='UPDATE';
			
			if (is_array($params->columns->column)){
				$count = count($params->columns->column);
				for ($i = 0; $i < $count; $i++) {
					
					$values[$params->columns->column[$i]]=$params->values->value[$i];
				}
			}else {
					$values[$params->columns->column]=$params->values->value;
			}
			
			$db = new ps_DB;
			$db->buildQuery($type,$params->table,$values,$params->whereClause);
			$result = $db->query();
			$errMsg=  $db->getErrorMsg();
			
			if ($errMsg==null){
				$SQLResult= new SQLResult("UPDATE OK in table : ".$params->table);
				$resultArray[] =$SQLResult;
				return $resultArray;
			}else{
				return new SoapFault("JoomlaSQLUpdateFault", "cannot execute UPDATE into ".$params->table." | ERRLOG : ".$errMsg);				
			}
			//$resultArray[] =new SQLResult($q);
			

		}else if ($result== "false"){
			return new SoapFault("JoomlaServerAuthFault", "Autification KO for : ".$params->loginInfo->login);
		}else if ($result == "no_admin"){
			return new SoapFault("JoomlaServerAuthFault", "User is not a Super Administrator : ".$params->loginInfo->login);
		}else{
			return new SoapFault("JoomlaServerAuthFault", "User does not exist : ".$params->loginInfo->login);
		}		
	
	}

	/**
    *  function onAuthenticate
	* (not expose as WS)
    * @param login/pass
    * @return true/false
    */
	function onAdminAuthenticate($login,$passwd){
	
		jimport('joomla.user.helper');
		$response="false";
		$db = new ps_DB;

		$list  = "SELECT id, username, password, usertype FROM `#__users` ";
		$list .= "WHERE username='".$login."' ";
		
		$response=$list;
		//$list .= $q . " LIMIT 0,100 "; 
		
		$db = new ps_DB;
		$db->query($list);

		/* function inspired by onAuthenticate (joomla.php) | Verify password is good*/
		if($db->next_record())
		{
			$parts	= explode( ':', $db->f("password") );
			$crypt	= $parts[0];
			$salt	= @$parts[1];
			$testcrypt = JUserHelper::getCryptedPassword($passwd, $salt);

			if ($crypt == $testcrypt ) {
				$response= "no_admin";
				if ( $db->f("usertype") == "Super Administrator" || $db->f("usertype") == "Administrator"){
					$response= "true";
				}
			} else {
				$response= "false";
			}
		}
		else
		{
			$response="no_user";
		}
		/////////////////////////////
		return $response;
	}
	

	if ($conf['querie_actif']=="on"){

		/* SOAP SETTINGS */
		$cache = "0";
		if ($conf['querie_cache'] == "on")$cache = "1";
		ini_set("soap.wsdl_cache_enabled", $cache); // wsdl cache settings
		
		if ($conf['soap_version'] == "SOAP_1_1"){
			$options = array('soap_version' => SOAP_1_1);
		}else {
			$options = array('soap_version' => SOAP_1_2);
		}
		
		if (empty($conf['BASESITE']) && empty($conf['URL'])){
			$server = new SoapServer($URL_BASE.'administrator/components/com_vm_soa/services/VM_SQLQueriesWSDL.php');
		}else if (!empty($conf['BASESITE'])){
			$server = new SoapServer('http://'.$conf['URL'].'/'.$conf['BASESITE'].'/administrator/components/com_vm_soa/services/VM_SQLQueriesWSDL.php');
		}else {
			$server = new SoapServer('http://'.$conf['URL'].'/administrator/components/com_vm_soa/services/VM_SQLQueriesWSDL.php');
		}
		
		//$server = new SoapServer($mosConfig_live_site.'/VM_SQLQueriesWSDL.php');
		/*if (!empty($conf['BASESITE'])){
			$server = new SoapServer('http://'.$conf['URL'].'/'.$conf['BASESITE'].'/administrator/components/com_vm_soa/services/VM_SQLQueriesWSDL.php');
		}else {
			$server = new SoapServer('http://'.$conf['URL'].'/administrator/components/com_vm_soa/services/VM_SQLQueriesWSDL.php');
		}*/

		/* Add Functions */
		$server->addFunction("ExecuteSQLQuery");
		$server->addFunction("ExecuteSQLSelectQuery");
		$server->addFunction("ExecuteSQLInsertQuery");
		$server->addFunction("ExecuteSQLUpdateQuery");
		$server->handle();
	}else{
		echo "This Web Service (SQL Queries) is desactived";
	}
?> 
Return current item: SOA For Virtuemart (WebServices)