<?php
function doPDOQ($sql, $valarr=[], $scalar=false){
	global $PDO;
	// preprint($valarr);
	
	try{
		$stmt = $PDO->prepare( $sql);
		// $stmt->debugDumpParams();
		$qtype = strtoupper(firstWord($sql));
		
		if(count($valarr) !== count($valarr, COUNT_RECURSIVE)){//it is multi
			foreach($valarr as $i=>$v){
				$stmt->execute($v);
				
				if($qtype=='SELECT' || $qtype=='DESCRIBE' || $qtype =='SHOW' || $qtype =='CALL' ){
					$out[$i] = $scalar ? $stmt->fetch(PDO::FETCH_ASSOC) : $stmt->fetchAll(PDO::FETCH_ASSOC);
				} elseif($qtype=='UPDATE' || $qtype=='INSERT' || $qtype=='DELETE'){
					$out[$i]['affected'] = $stmt->rowCount();
				}else{
					$out[$i] = $stmt->rowCount();//pending!
				}
				
			}
			//preprint($out);
		}else{//its NOT multidimensional
			$stmt->execute($valarr);
			// preprint($stmt);
			// $qtype = strtoupper(firstWord($sql));
			$out['qtype'] = $qtype;
			if($qtype=='SELECT' || $qtype=='DESCRIBE' || $qtype =='SHOW' || $qtype =='CALL' ){
				$out = $scalar ? $stmt->fetch(PDO::FETCH_ASSOC) : $stmt->fetchAll(PDO::FETCH_ASSOC);
			} elseif($qtype=='UPDATE' || $qtype=='INSERT' || $qtype=='DELETE'){
				$out['result'] = 1;
				$out['error'] = 0;
				$out['new_id'] = $PDO->lastInsertId();
				$out['id'] = $PDO->lastInsertId();
				$out['affected'] = $stmt->rowCount();
			}else{
				$out = $stmt->rowCount();//pending!
			}
		}
		
		// return;
	}catch (Exception $e) {
		
		$out['id'] = 0;
		$out['affected'] = 0;
		$out['error'] = $e->getMessage();
		$out['message'] = $e->getMessage();
		$out['sql'] = $e->getTrace()[1]['args'][0];
		$out['args'] = $e->getTrace()[1]['args'];
	}
	
	// var_dump($out);
	return $out;
}

function doQuery($sql, $return_error=false, $showfields=false){
global $LINK;
	$out=array();
	//$qtype = strtoupper(substr($sql,0,6));
	//$qtype = strtoupper(firstWord($sql));
	$qtype = strtoupper(preg_split('/[^\w]/',$sql, 2)[0]);
	
	$out['qtype']=$qtype;
	$out['sql']=$sql;
	$result=mysqli_query($LINK, $sql);

	if(!$result){
		$errno_conn=mysqli_connect_errno($LINK);
		if($errno_conn=='1062' || $errno_conn=='1452'){
			return $errno_conn.'\r\n'.mysqli_connect_error().'\r\n'.$sql;
		} else {//no conn error
			$errno = mysqli_errno($LINK);
			$error = mysqli_error($LINK);
			switch($errno){
				case 1451: $txt = 'A foreign constraint is preventing the task from completing. The record you were trying to change is in use elsewhere this operation is not permitted!'; $result='Foreign Constraint'; break;
				case 1062: $txt = 'A duplicate was found preventing the task from completing. You were trying to enter a new record somewhere it already exists!'; $result="Duplicate Entry"; break;
				default: $txt = 'There was a technical problem, your request cannot be completed now.'; $result='Query Error';
			}
			if($return_error){//don't spit out
				$out['errno']=$errno;
				$out['error']=$error;
				$out['affected']=0;
				
			}else{	
				echo $sql.' / '.$txt;		
			}
		}
		$out['result']=null;
		
	 }else{ 
		if($qtype=='SELECT' || $qtype=='DESCRIBE' || $qtype =='SHOW' ){
			if( is_object($result)){
				$out['assoc']=mysqli_fetch_assoc($result);
				$out['numrows']=mysqli_num_rows($result);

					if(!$out['numrows'] || $showfields){						
						foreach(mysqli_fetch_fields($result) as $obj){
							$out['fields'][$obj->name]=null;
						}
					}
			}//else its boolean TRUE for e.g. SELECT INTO OUTFILE
		} elseif($qtype=='UPDATE' || $qtype=='INSERT' || $qtype=='DELETE'){
			$out['affected']=mysqli_affected_rows($LINK);
			$out['error']=0;
			//preprint($out);
			//preprint('ret:'.$return_error);
		}else{
			//$out['affected']=false;
		}
			//$out['link']=$LINK;
			$out['result']=$result;
			//unset($out['fields']);
			//unset($out['result']->lengths);
			//preprint($out['result']);
			
	//mysqli_free_result($result);
	}
	//mysqli_close($LINK);
	
	//preprint($out);
	return $out;
}

function doQueryMenu($sql, $return_error=false, $showfields=false, $limit=10, $pgno=1){
	$pgno = max($pgno, 1);
	$sq = $sql.' LIMIT '.(($pgno-1)*$limit).', '.$limit;
	$q = doQuery($sq, $return_error=false, $showfields=false);
	
	$total = SW('FOUND_ROWS()');
	$nextnum = $total - ($limit*$pgno);
	$leftover = $total % $limit;
	
	$completes = floor($total/$limit);
	
	$q['paging']['found_rows'] = $total;
	$q['paging']['numrows'] = $q['numrows'];
	$q['paging']['batch_count'] = ceil($total/$limit);
	$q['paging']['nextnumrows'] = $pgno == $q['paging']['batch_count'] ? 0 : ($pgno == $completes || $completes == 0 ? $leftover : $limit);
	$q['paging']['leftover'] = $leftover;
	
	return $q;
}

function doMQuery($stmts, $with_tx=true){
	global $LINK;

	if(!is_array($stmts)){
		$stmts = array($stmts);//force array
	}
	if($with_tx) mysqli_autocommit($LINK, FALSE);
	for($i=0; $i < count($stmts); $i++){
		if(is_array($stmts[$i])){
			$s=$stmts[$i][0]; $s2=$stmts[$i][1];
		}else{
			$s=$stmts[$i]; $s2 = '';
		}
		$query[$i]=doQuery($s);
		if($s2 && $query[$i]['affected']) doQuery($s2);
	}
	
	if($with_tx) mysqli_commit($LINK);
	return $query;
}

/////////////////////////////////////////////end of doQuery////////////////////////////////////////////////////

function SW($field, $table="DUAL", $condition=1, $arr=false, $return_flds=false, $pdo_arr=null){//returns SINGLE row: value from a select query or assoc ARRAY if $arr

	if($pdo_arr){		
		$is_scalar = (!$arr && $field != "*");
		
		$sql = 'SELECT '.$field.' '.($is_scalar ? ' AS fld' : '').' FROM '.$table.' WHERE '.$condition;
		$resultarr=doPDOQ($sql, $pdo_arr, $is_scalar );
		// preprint($resultarr);
		
		if($is_scalar){
			return $resultarr['fld'];
		}else{
			return $resultarr[0] ?? null;
		}
		
	}else{
		$query=doQuery('SELECT '.$field.(!$arr && $field != "*" ? ' AS fld' : '').' FROM '.$table.' WHERE '.$condition);
		// preprint($query['assoc']);
		$val = ($field=='*' || $arr) ? $query['assoc'] : ($query['assoc']['fld'] ?? null);
		
		//preprint($query);
		if(isset($query['fields']) && $return_flds){
			return $query['fields'];
		}
		
		
		if(!is_array($val)){
			if(strlen(strval($val)) > 0){
				return $val;
			} else {
				return false;
			}
		} else {
			return $val;//this is an array
		}
	}
	
}

//preprint($_arr_);

function SAW($field, $table, $condition=1, $assoc=0, $dictionary=0){//returns Array from a select query yielding multiple rows

	$q=doQuery('SELECT '.$field.(!$assoc ? ' AS fld' : '').' FROM '.$table.' WHERE '.$condition);
	//preprint($q);
	$valarray = [];
	if($q['numrows']){
		do{
			if($dictionary){
				if($field == '*' || in_str(',', $field)){
					$key = array_shift($q['assoc']);

					/*
					$arr_vals = array_values($q['assoc']);
					$valarray[ $arr_vals[0] ] =  $arr_vals[1];
					*/
					$arr_vals = $q['assoc'];
					$valarray[$key] = count($arr_vals)==1 ? array_shift($arr_vals) : $arr_vals;
				}else{
					$valarray[]=$q['assoc'][$field];
				}
			}elseif($assoc){
			//echo 'assoc:'.$q['assoc'][$field].'__<br>';
				if($field == '*' || in_str(',', $field)){
					$valarray[]=$q['assoc'];
				}else{
					$valarray[]=$q['assoc'][$field];
				}
			}else{
				$valarray[]=$q['assoc']['fld'];
			
			}
		} while ($q['assoc']=mysqli_fetch_assoc($q['result']));
	}
//		preprint($valarray);
	//$strval=strval($val);
	return $valarray;
	
	
	if(count($valarray) > 0){
		return $valarray;
	} else {
		return false;
	}
	
}


function selArray($field, $table, $qsuff='', $assoc=false, $concat_order=''){//assoc means use first fld as key
	$arr = array();
	$multi = in_str(',',$field);
	
	$field = $multi ? "CONCAT_WS('_^_', {$field})" : $field;
	$q = doQuery("SELECT GROUP_CONCAT({$field} {$concat_order} SEPARATOR '^~$') AS val FROM {$table} {$qsuff}");

	if($q['numrows']){
		//preprint($q['assoc']);
		
		$arr1 = explode('^~$', $q['assoc']['val']);
		if($multi){
			foreach($arr1 as $item){

			$arr2 = explode('_^_', $item);
				if($assoc){
					$key = array_shift($arr2);
					$arr[$key] = $arr2;
				}else{
					$arr[] = $arr2;
				}
			}
		}
	}
	//return $q['assoc'];
	return $arr;
}



//UNDER CONSTR
function selArrayNEW($field, $table, $qsuff='', $assoc=false, $concat_order=''){//assoc means use first fld as key
	$arr = array();
	$multi = in_str(',',$field);
	$fields = explode(',', $field);
	
	$newfld='';	
	//preprint($fields);
	foreach($fields as $i=>$fld){
		$newfld .= 'IFNULL('.$fld.', "")';
		if($i < count($fields)-1) $newfld .= ',';
	}
	//preprint($newfld);
	
	$field = $multi ? "CONCAT_WS('_^_', {$newfld})" : $field;
	
	$q = doQuery("SELECT GROUP_CONCAT({$field} {$concat_order} SEPARATOR '^~$') AS val FROM {$table} {$qsuff}");

	if($q['numrows']){
		$arr1 = explode('^~$', $q['assoc']['val']);
		//preprint($arr1);
		if($multi){
			foreach($arr1 as $item){

			$arr2 = explode('_^_', $item);
				if($assoc){
					$key = array_shift($arr2);
					$arr[$key] = $arr2;
				}else{
					$arr[] = $arr2;
				}
			}
		}
	}
	return $arr;
}


function getTableColumns($tbl, $cols='', $whsuff='', $index=''){
	$Q = doQuery('SELECT '.($cols ?: '*').' FROM '.$tbl.' WHERE '.( strlen($whsuff) ? $whsuff : '1'));
	$out=[];
	if($Q['assoc']){
		$flds =  array_flip(array_keys($Q['assoc']));

		do{
			$arr[] = $Q['assoc'];
		}while($Q['assoc'] = mysqli_fetch_assoc($Q['result']));

		if($Q['numrows']){
			foreach($flds as $Q_fld=>$NULL){
				$out[$Q_fld] = array_column( $arr, $Q_fld, $index);
			}
		}
	}else{
	}
		
	return $out;
}


function getTableRows($tbl, $cols='', $whsuff=1, $json=false, $keyfld=''){
	$Q = doQuery('SELECT '.($cols ?: '*').' FROM '.$tbl.' WHERE '.$whsuff);
	
	$out=[];
	if($Q['numrows']){		
		do{
			if($keyfld){
				$out[ $Q['assoc'][$keyfld] ] = $Q['assoc'];
			}else{				
				$out[] = $Q['assoc'];
			}
		}while($Q['assoc'] = mysqli_fetch_assoc($Q['result']));
	}

	return $json ? json_encode($out) : $out;
}


function preprint($array){
	print '<pre style="text-align:left">';
	print_r($array);
	print '</pre>';
}


function prereturn($array){
	$out = '<pre>';
	$out .= print_r($array,1);
	$out .= '</pre>';
	return $out;
}


function buildSqlMsg($query, $txt=''){//for CLI version
global $LINK;
	$msg=$query.breakLine().'ERROR NO:'.mysqli_errno($LINK).breakLine().'ERROR DETAILS:'.mysqli_error($LINK);
	echo $txt.breakLine().'QUERY:'.breakLine(2).$msg.breakLine(2).'If the problem persists, contact the system administrator for assistance.';
}////////////////////////////////////////////////end of buildSqlMsg//////////////////////////////////////

function breakLine($lines=1){
	return str_repeat(chr(13).chr(10), $lines);
}


function submitList($arr, $tbl, $array_cast='', $keys=[], $return_arr=0, $ignore='', $pdo=0){// array
	// preprint($arr);
	global $LINK;
	$newid=0;
	
	if( !(is_array($arr) && count($arr)) ) return false;
	
	if(isset($arr['triggerdata'])){
		$triggerdata = $arr['triggerdata'];
		unset($arr['triggerdata']);
		mysqli_autocommit($LINK, FALSE);
	}
	if($array_cast=='serialize'){
		$arr = array_map(function($v){ return is_array($v) ? serialize($v) : $v; }, $arr );
	}elseif($array_cast=='implode'){
		$arr = array_map(function($v){ return is_array($v) ? implode(',',$v) : $v; }, $arr );
	}

	if(isEmptyArray($keys)){//for multiple unique key
		$keys=array( array_keys($arr)[0] );
	}
	
	$keyarr = array_intersect_key($arr, array_flip($keys));
	// preprint($keyarr);
	
	
	
	if(count($keys) == 1){
		if( $arr[ $keys[0] ] === 0 ) return false; //prevent key insert/update on zero
	}
	
		
	$i=0; $wh='';		
	foreach($keyarr as $k=>$v){
		$wh .= ($i != 0) ? ' AND ' : '';
		if($pdo){
			$wh .= '('.$k.' = :'.$k.')';	
		}else{
			$wh .= '('.$k.' = "'.$v.'"'.( is_string($v) && strlen($v) ? '' : ' AND '.$k.' > 0' ).')';	
		}
		
		
		$i++;
	}
		


	
	$insitudata = SW('*', $tbl, $wh, 1, 0, ($pdo ? $keyarr : null));
	// preprint($keyarr);
	// var_dump($insitudata);
	
	// $insitu = SW('COUNT(*)', $tbl, $wh);
	$insitu = !empty($insitudata) && !isEmptyArray($insitudata);
			
	if( $insitu ){//its for update
		$action='update';
		$suff=' WHERE '.$wh;
		
		$pks = getPrimaryKeyFlds($tbl);		
		


		$lastids = array_values( array_intersect_key($insitudata, array_flip( $pks )) );
		
		// preprint($lastids);
		if(count($pks) > 1){
			$lastid = $lastids;
		}else{
			// echo 'lastID1:'.$lastid = $insitudata[ $pks[0] ];
			$lastid = $lastids[0];
		}
		
		$dotrigger = count($pks) === 1;
	}else{//its for insert
		$action='insert';
		$suff='';
		$dotrigger = 1;
	}
	
	if($pdo){
		// preprint($arr);
		// echo '<BR>AcTION is:'.$action;
		$qin=doPDOEntryQuery($tbl,$arr, $action,$suff, true, $ignore);
	}else{
		if($insitu){
			foreach($keys as $k2){
				unset($arr[$k2]);
			}
		}
		
		$incols=array_keys($arr);
		$invals=doSqlInString(array('str'=>array_values($arr)));
		// preprint($incols);
		// preprint($invals);
		
		
		$qin=doEntryQuery($tbl,$incols,$invals,$action,$suff, true, $ignore);
	}
	// preprint($qin);
	
	
	if($qin['affected'] > 0 || ($insitu  && empty($qin['error']))){//entry of details successful
		
		if($qin['affected'] > 0) $newid = (($qin['new_id']??0) ?: mysqli_insert_id($LINK));	
		
	
		if(isset($triggerdata) && $dotrigger){//triggerdata CANNOT support compound PK
			//preprint($triggerdata);
			
			$trigsuff = is_array($triggerdata[2]) ? arr2qstr($triggerdata[2]) : rawurldecode($triggerdata[2]);
			$trstr = (isset($triggerdata[1]) ? $triggerdata[1].'='.($insitu ? $lastid : $newid).'&' : '').$trigsuff;
			parse_str($trstr, $trarr);
			$trig_uniques = isset($triggerdata[3]) ? explode(',', rawurldecode($triggerdata[3])) : [];
			
			
			//preprint($trarr);
			$sub = submitList($trarr, $triggerdata[0], '', $trig_uniques, true);	
			
			//$dump .= "===".PHP_EOL.prereturn($sub).PHP_EOL."===";
			
			if(!empty($sub['result'])){
				mysqli_commit($LINK);
				$res =  1;
			}else{
				$res =  0;
			}
		}else{
			$res = 1;
		}
		
		//file_put_contents("F:/temp/submit_query/".REQTIMESTAMP.".txt", $dump);
		
	}elseif(intval($qin['affected'] > 0 )){//maybe 1062 duplicate
		$res = null;//
	}else{
		$res = 0;
	}
	
	if($return_arr){
		$out = [ 'result'=>$res, 'id'=>($insitu ? $lastid : $newid), 'output'=>$qin ];
		if(isset($sub)) $out['trigger_res'] = $sub;//not yet working
		return $out;
	}else{		
		return $res;
	}
}

?>