<?
// require("../indi/lib/mysqliTnx.php");
// $DB = new mysqliTnx("localhost", "root", "", "carlo");
// $DB->setSchema();//per avere subito tutti gli indici e le foreignKey (richiede 0.1 sec su un db base_admin_tnx)

class mysqliTnx extends mysqli{
	
	var $schema = array();
	
	// var $insert_id;//esiste nella classe mysqli (per ricerche: last_insert_id)
	var $gestoreErrori;
	var $gestoreErroriQuery;//contiene ultima query
	var $gestoreErroriParametri;//contiene ultimi parametri
   
   //usati da gestore errore default (erroreDb)
	var $dbError;
	var $dbErrNo;
	
	// var $affected_rows;//esiste in mysqli
	
	var $useOrgname = false;
	
	function __construct($host, $user, $pass, $db = null, $gestoreErrori = null){
      
		$connect = parent::__construct($host, $user, $pass, $db);
		if(!$gestoreErrori) $gestoreErrori = array($this, 'erroreDb');
		$this->gestoreErrori = $gestoreErrori;    
		if($connect === false){
			$this->triggerError($this->connect_error);
		}
		else $this->set_charset('utf8');//Sets the default character set to be used when SENDING data from and to the database server. 
// 		$this->query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");
	}
	
	
	function erroreDb($db){
		//appena php libera la memoria dello statement si perde la info dell'errore (vedi return false su duplicate insert)
		$this->dbError = $db->error;
		$this->dbErrNo = $db->errno;

		$error = array(
			"$db->error ($db->errno)",
			"Query: ".$db->gestoreErroriQuery
		);
		if($db->gestoreErroriParametri) $error[] = "Parametri: ".implode(", ",$db->gestoreErroriParametri);
      
		
		trigger_error(implode(", ", $error));
		
		$db->gestoreErroriQuery = '';
		$db->gestoreErroriParametri = '';
	}
	
   
	function escape($s) {
	    if ($s === null) {
	        return "null";
	    } else {
	        return "'" . $this->escape_string($s) . "'";
	    }
	}
	/*
	ho tolto l'auto setSchema che era piuttosto gravoso
	function set_database($db){
		trigger_error("questa funzione chiamava parent::set_database che non esiste, l'ho rinominata in select_db che credo fosse quello che volevo");
		return $this->select_db($db);
	}
	
	function select_db($db){
		$ret = parent::select_db($db);
		$this->setSchema();
		return $ret;
	}
	*/
	function triggerError($customError = null){
		//cecca 24/07/2019 - php 5.6 non permette di sovrascriere la proprietà error di mysqli
		//if($customError) $this->error = $customError;
		if($customError) $this->error_custom = $customError;
		call_user_func($this->gestoreErrori, $this);
	}
	
	//http://php.net/manual/en/mysqli-stmt.bind-param.php#100879
	function refValues($arr){
        if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
        {
            $refs = array();
            foreach($arr as $key => $value)
                $refs[$key] = &$arr[$key];
            return $refs;
        }
        return $arr;
    }
	
	//necessario per gestire _tnx_affected_rows
	//function query(){
	function query($query, $result_mode = MYSQLI_STORE_RESULT){
		$args = func_get_args();
		$return = call_user_func_array(array('parent', __FUNCTION__), $args);
		$this->_tnx_affected_rows = $this->affected_rows;
		return $return;
	}
	
	/*
	ritorna false in caso di errore o lo *** statement ***
	*/
	
	function _q($query, $data = null){
		/*
		You can default to 's' for most parameter arguments in most cases.  The value will then be automatically cast to string on the back-end before being passed to the Mysql engine.  Mysql will then perform its own conversions with values it receives from PHP on execute.  This allows you to bind not only to larger numbers without concern for precision, but also to objects as long as that object has a '__toString' method.
		This auto-string casting behavior greatly improves things like datetime handling.  For example: if you extended DateTime class to add a __toString method which outputs the datetime format expected by Mysql, you can just bind to that DateTime_Extended object using type 's'.  i.e.:
		// DateTime_Extended has __toString defined to return the Mysql formatted datetime
		$var = new DateTime_Extended;
		bind_param('s', $var); // forwarded to Mysql as '2011-03-14 17:00:01'
		*/
		
		$stmt = $this->prepare($query);
		
		if($stmt === false){
			$errore = true;
		}
		else{
			if(!is_null($data)){
				if(!is_array($data)) $data = array($data);	
				array_unshift($data, str_repeat("s", count($data)));//aggiungo il parametro $types
				
				
				call_user_func_array(array($stmt, 'bind_param'), $this->refValues($data));
			}
			// console_log((string)$stmt);
			if($stmt->execute() === false){
				$errore = true;
				//d_info(md5(serialize($data)),"mysqliTnx:_q 1 ERR data $query","DATAB");
			} else {
				//d_info(md5(serialize($data)),"mysqliTnx:_q 2 data $query","DATAB");
			}
		} 
		
		//verificato che con $_DB->q("delete from prenotazioni_tavoli where prenotazione_id = 4 and tavolo_id = 2") che dopo il return di questa funzione, dentro il metodo q() affected_rows era uguale a -1
		$this->_tnx_affected_rows = $this->affected_rows;
 	
		if($errore && $this->gestoreErrori){
			$this->gestoreErroriQuery = $query;
			$this->gestoreErroriParametri = $data;
			// if (is_array($data)) {
				// array_shift($data);//tolgo parametro $type
			// }
			$this->triggerError();
// 			$this->asd = $stmt;
			return false;
		}
		
		return $stmt;
	}
	
	function getAffectedRows(){
		return $this->_tnx_affected_rows;
	}
	
	/*
	lancia eccezione in caso di errore
	*/
	function _tryq($query, $data = null){
		$stmt = $this->prepare($query);
		
		$this->gestoreErrori[0]->segnala("_tryq:$query");
		
		if($stmt === false){
			$this->gestoreErrori[0]->segnala("_tryq:$query -> stmt === false");
			throw new Exception('stmt === false');
		}
		else{
			if(!is_null($data)){
				if(!is_array($data)) $data = array($data);	
				array_unshift($data, str_repeat("s", count($data)));//aggiungo il parametro $types
				call_user_func_array(array($stmt, 'bind_param'), $this->refValues($data));
			}
			
			if($stmt->execute() === false){
				$this->gestoreErrori[0]->segnala("_tryq:$query -> stmt->execute() === false");
				throw new DbException("Query error: $this->error ($this->errno)", $this->errno, $query, $data);
			}
		} 
		
		return $stmt;
	}	
	
	
	function q($query, $data = null){
		$return = $this->_q($query, $data) !== false;
		// d_info(md5(serialize($data)) . " " . $query, "mysqliTnx q");
		if($questo_e_il_motivo_per_cui_esiste__tnx_affected_rows){
			echo '<style type="text/css">svg{width:1em;}</style><pre style="text-align:left">';
			$debugBacktrace = debug_backtrace(); array_unshift($debugBacktrace, array('file'=>__FILE__, 'line'=>__LINE__, 'function'=>'debugTnx')); foreach($debugBacktrace as $debugLine) echo "<a target=\"_blank\" href=\"https://www.demo.tnx.it/tnx/guarda/?command=".urlencode($debugLine['file'])."&pspadline=".$debugLine['line']."&auto_close=1\">".str_replace("/tnx/www/html/www/", "", $debugLine['file'])."</a>"." ".$debugLine['function']."()<br />";
			$printMe = $this->affected_rows; ob_start(); if(is_object($printMe)||is_array($printMe)) print_r($printMe); else var_dump($printMe); echo htmlentities(ob_get_clean(), ENT_COMPAT|ENT_HTML401|ENT_SUBSTITUTE, 'UTF-8');
			echo '</pre>';
			// die;
		}
		return $return;
	}
	
	function tryq($query, $data = null){
		return $this->_tryq($query, $data) !== false;
	}

	function qkv($query, $data, $keyField, $valueField){
		
		if($GLOBALS['DATI']['sito'] != 'avatable') mailtnx($_SERVER['TNX_SERVER_ID']=='demo'?'c@localhost':"carlo@tnx.it", "\$_DB->qkv: vorrei cambiare l'ordine dei paramentri per rendere opzionale l'ultimo", __FILE__.":".__LINE__."\n".print_r(array($_REQUEST, $_SERVER), true));
		
		$data = $this->qa($query, $data);
		$return = [];
		foreach($data as $v){
			if($return[$v[$keyField]]) trigger_error("attenzione, chiave non univoca in qkv()");
			$return[$v[$keyField]] = $v[$valueField];
		}
		return $return;
	}
	function qak($fields, $table, $where = "1=1", $data = null){
		$this->setSchema($table);
		$pk = $this->schema[$table]['pry'];
		$aggiuntoId = false;
		if(!in_array($pk, $fields)){
			$fields[] = $pk;
			$aggiuntoId = true;
		}
		$return = $this->qa("select `".implode("`,`", $fields)."` from $table where $where", $data);
		$returnK = array();
		foreach($return as $i){
			$returnK[$i[$pk]] = $i;
			if($aggiuntoId) unset($returnK[$i[$pk]][$pk]);
		}
		return $returnK;
	}
	
	function qa($query, $data = null, $chiava = null){
	
		$stmt = $this->_q($query, $data);
		
		if($stmt === false){
			return false;
		}
		
		$return = array();
		
		// CARLO 9/1/2019: usando gli statement non posso fare fetch_array/fetch_all con php < 5.3 perchè non ho mysqli_stmt::get_result
		// con php >= 5.3 posso usare questo codice (sicuramente più ottimizzato), a patto di aver avuto useOrgname=false (è true invece su invoicex_rest)
		// altrimenti alcune chiavi possono essere diverse, ad esempio quelle di uno SHOW TABLES (TABLE_NAME > Tables_in_base_admin_tnx)
		if(method_exists($stmt, "get_result")){
			$return = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
			if(!$chiava) return $return;
			else{
				$returnChiavato = [];
				foreach($return as $r) $returnChiavato[$r[$chiava]] = $r;
				return $returnChiavato;
			}
			
		}
		
		//cecca: importante altrimenti la bind_result schianta per memoria esaurita http://php.net/manual/en/mysqli-stmt.bind-result.php#57564
		$stmt->store_result();
 	
		//The result set returned by mysqli_stmt_result_metadata() contains only metadata. It does not contain any row results. The rows are obtained by using the statement handle with mysqli_stmt_fetch(). 
		$fields = $stmt->result_metadata()->fetch_fields();
		
		$riga = array();
		$call_user_func_array_param = array();
		foreach($fields as $k=>$v){
			/*
			SHOW FULL TABLES WHERE `Table_type`='BASE TABLE'
			[name] => Tables_in_inv_test_sync_master
    		[orgname] => TABLE_NAME
    		
    		select max(...
    		[name] => max(id)
    		[orgname] => 
    		
    		describe $table
    		[name] => Field
    		[orgname] => COLUMN_NAME
			*/
			$name = ($this->useOrgname && $v->orgname) ? $v->orgname : $v->name;
		
			$riga[$name] = null;
			$call_user_func_array_param[] = &$riga[$name];
		}
		
		call_user_func_array(array($stmt, 'bind_result'), $call_user_func_array_param);
		
		while($stmt->fetch()){
			foreach($riga as $k=>$v)//necessario perchè lavoro per reference
				$return[][$k] = $v;
		}
	   
		$stmt->free_result();
		$stmt->close();
		
		return $return;
	}
	
	function qr($query, $data = null){
		$data = $this->qa($query, $data);
		if (is_array($data)) {
			return array_shift($data);
		} else {
			return false;
		}
	}
	
	function qrs($query, $data = null){
		$data = $this->qr($query, $data);
		if (is_array($data)) {
			return array_shift($data);
		} else {
			return false;
		}
	}
	
	
	function insert($table, $data, $multipleLines = false, $insertParams = ''/*'IGNORE'*/){
		$fields = array();
		$values = array();
		$placeholders = array();
		$placeholdersLine = '';
		$placeholdersLines = array();
		$lines = array();
		
		if(!$multipleLines) $lines = array($data);
		else $lines = $data;
		
		foreach($lines as $l=>$linea) foreach($linea as $i=>$campo){
			if($l==0){
				$fields[] = $i;
				$placeholders[] = "?";
			}
			$values[] = $lines[$l][$i];
		}
		
		$placeholdersLine = implode(", ", $placeholders);
		
		foreach($lines as $l=>$linea) $placeholdersLines[] = $placeholdersLine;
		
		return $this->q(
			"INSERT $insertParams INTO " . $table . " (`"
			. implode("`,`", $fields)
			. "`) VALUES ("
			. implode("), (", $placeholdersLines)
			. ");"
		, $values);
		
	}
	
	//FUNZIONA SOLO SU SIGOLO RECORD PER ID
	function update($table, $data, $id){
		$this->setSchema($table);
		$values = array();
		$placeholders = array();
		foreach($data as $i=>$d){
			$values[] = $data[$i];
			$placeholders[] = "`$i`"."=?";
		}
		
		$values[] = $id;

		return $this->q(
			"UPDATE " . $table 
			. " SET "
			. implode(",", $placeholders)
			. " WHERE "
			. $this->schema[$table]['pry'] . "=?"
		, $values);
	}
	
	function delete($table, $primaryValue){
		$this->setSchema($table);
		return $this->q(
			"DELETE FROM " . $table 
			. " WHERE "
			. $this->schema[$table]['pry'] . "= ?"
		, array($primaryValue));
	}
	
	function getTables(){
		$tables = array();		
		$result = $this->qa("SHOW FULL TABLES WHERE `Table_type`='BASE TABLE'");
		foreach($result as $r){
			if($this->useOrgname && $r['TABLE_NAME']) $tables[] = $r['TABLE_NAME'];
			else $tables[] = array_shift($r);
		}
		
		return $tables; 
	}
	
	function setSchema($onlyTableNoForeingKey = null){
		if($onlyTableNoForeingKey) $tables = array($onlyTableNoForeingKey);
		else $tables = $this->getTables();
		
		foreach($tables as $table) if(!$this->schema[$table]){
			$this->schema[$table] = array("pry"=>"", "pryFks"=>array(), "pryAi"=>false);

			$describe_result = $this->qa("describe $table");
			foreach($describe_result as $d){
				if($d[$this->useOrgname?'COLUMN_KEY':'Key'] == 'PRI'){
					$this->schema[$table]["pry"] = $d[$this->useOrgname?'COLUMN_NAME':'Field'];
					$this->schema[$table]["pryAi"] = $d[$this->useOrgname?'EXTRA':"Extra"] == 'auto_increment';
					break;//era continue; errore?
				}
			}
		}
		
		
		if($onlyTableNoForeingKey) return;
		
		
		foreach($tables as $table){
		
			$create = $this->qr('SHOW CREATE TABLE '.$table);
			//CONSTRAINT `FK_articoli_categorie_articoli` FOREIGN KEY (`categoria`) REFERENCES `categorie_articoli` (`id`) ON UPDATE CASCADE
			
			
			preg_match_all(
				"/CONSTRAINT `([^`]+)` FOREIGN KEY \(`([^`]+)`\) REFERENCES `([^`]+)` \(`([^`]+)`\)( ON DELETE CASCADE)?( ON UPDATE CASCADE)?/im",
				$create['Create Table'],
				$res,
				PREG_SET_ORDER
			);
			foreach($res as $r){
				if($r[4] != $this->schema[$r[3]]["pry"]){
					$this->triggerError("C'è una foreignKey che si appoggia a un campo diverso dal primary su $table");
				}
				else $this->schema[$r[3]]["pryFks"][] = array(
// 					'name' => $r[1],
					'table' => $table,
					'field' => $r[2],
				);
			}
		}
	}
}


class DbException extends Exception {
	var $sql;
	var $data;
    
	public function __construct($message, $code = 0, $sql = null, $data = null) {
	
		$this->sql = $sql;
		$this->data = $data;
	
		// make sure everything is assigned properly
		parent::__construct($message, $code);
	}
	
   public function __toString() {
		return __CLASS__ . ": [{$this->code}]: {$this->message} sql:$this->sql\n";
	}
}

?>