<?php
/*
	VERSION 3.2.0
	query to find all tables in database "YourDatabase" that have a columns named "columnA" and "columnB"

	SELECT DISTINCT TABLE_NAME
	    FROM INFORMATION_SCHEMA.COLUMNS
	    WHERE COLUMN_NAME IN ('columnA','ColumnB')
	        AND TABLE_SCHEMA='YourDatabase';

	SEE: http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them

	example of query to find duplicate entries:
	SELECT count(  *  ) , cCode FROM  `clientdat` GROUP  BY cCode HAVING count(  *  )  > 1;
*/
	$nonSyncingTables = array("", "system", "todo");


// initialize local and remote (if available) connections
	$mLink = mysql_connect($_SESSION['instance']['mdbIfce'], $_SESSION['instance']['mdbUser'], $_SESSION['instance']['mdbPass'])
		or die("Could not connect to MySQL server [sql.php: 13] [dbName=$dbName; dbUser=$dbUser; dbPswrd=$dbPswrd; dbInterface=$dbInterface]");
//	mysql_select_db($_SESSION['instance']['mdbName'], $mLink) or die("Could not select database {$_SESSION['instance']['mdbName']}");
	if ((mysql_select_db($_SESSION['instance']['mdbName'], $mLink)) === false)
	{	 echo "Could not select database {$_SESSION['instance']['mdbName']}";
		debug();
	}

	include 'sql.default.php';

	// generate a list of tables that have linkRef or jobNo
	if (empty($_SESSION['linkTables']))
	{	$q = "SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (COLUMN_NAME = 'linkRef' or COLUMN_NAME = 'jobNo') AND TABLE_SCHEMA='{$_SESSION['instance']['mdbName']}'";
		$r = mysql_query($q);
		if ($r)
		{	while (list($tblName, $colName) = mysql_fetch_row($r))
				$_SESSION['linkTables'][$tblName] = $colName;
		}
	}

	function sqlEx($q, $show=false)
	{	GLOBAL $mLink;

		$r = mysql_query($q, $mLink);
		if ($r === false)
			echo "<!-- sqlEx Error: $q -->\n";
		else if ($show)
			echo "<!-- sqlEx: $q -->\n";

		return $r;
	}

	function sqlExRtnAssoc($q, $show=false)
	{	GLOBAL $mLink;
		if ($show and ($r = mysql_query($q, $mLink) === false))
		{	echo "<!-- sqlExRtnAssoc Error: $q -->\n";
			return false;
		}
		return mysql_fetch_assoc($r);
	}

	function sqlExRtnRow($q, $show=false)
	{	GLOBAL $mLink;
		if ($show and ($r = mysql_query($q, $mLink) === false))
		{	echo "<!-- sqlExRtnRow Error: $q -->\n";
			return false;
		}
		return mysql_fetch_row($r);
	}

	function sqlAltEx($q, $show=false)
	{	GLOBAL $rLink;

		if ($show and ($r = mysql_query($q, $rLink) === false))
		{	echo "<!-- sqlAltEx Error: $q -->\n";
			return false;
		}
		return $r;
	}

	function sqlAltExRtnAssoc($q, $show=false)
	{	GLOBAL $rLink;
		if ($show and ($r = mysql_query($q, $rLink) === false))
		{	echo "<!-- sqlAltExRtnAssoc Error: $q -->\n";
			return false;
		}
		return mysql_fetch_assoc($r);
	}

	function sqlAltExRtnRow($q, $show=false)
	{	GLOBAL $mLink;
		if ($show and ($r = mysql_query($q, $rLink) === false))
		{	echo "<!-- sqlAltExRtnRow Error: $q -->\n";
			return false;
		}
		return mysql_fetch_row($r);
	}

	function getNextTID()
	{	GLOBAL $mLink;
		// this is a unique TRANSACTION ID. EVERY entry has one

		$tid = mysql_fetch_assoc(mysql_query("select tid from system", $mLink));
		$tid = nextRef($tid['tid']);
		mysql_query("update system set tid='$tid'", $mLink);
		return date("Ymd").$_SESSION['instance']['prefix'].$tid;
	}

	function lastmod($table)
	{	GLOBAL $mLink;
		// we need to know if there is a 'lastmod' column in this table;
		// if there is, we need to update it
		$q = "show columns from $table like 'lastmod'";
		$r = mysql_query($q, $mLink);
//		if ($r = mysql_num_rows($r) === false);
//		{	$error['table'] = $table;
//			$error['query'] = $q;
//			debugDump($error, "sql Lastmod Error", false);
//			// $r['lastmod'] = $errors;
//		}
		if ($r) return "lastmod = '".date("YmdHis")."', ";
		else
			return "";
	}

	function logChange($table, $tid)
	{	GLOBAL $mLink, $rLink, $nonSyncingTables;

		if (false === (array_search($table, $nonSyncingTables)))
		{
			if ($_SESSION['instance']['prefix'] != "000")
			{	$q = "replace into `transaction_log` set tble='$table', tid='$tid', lastmod='".date("YmdHis")."', chgby='{$_SESSION['instance']['prefix']}'";
				mysql_query($q, $mLink);
			}

			$q = "replace into `transaction_mstr_log` set tble='$table', tid='$tid', lastmod='".date("YmdHis")."', chgby='{$_SESSION['instance']['prefix']}'";
			mysql_query($q, $mLink);
		}
	}

	function sqlSelect($table, $conditions, $group = "", $order = "", $fields = "*", $show = false)
	{	GLOBAL $mLink, $rLink;
		$q = "select $fields from $table";
		if ($conditions)
			$q .= " where $conditions";
		if ($group)
			$q .= " group by $group";
		if ($order)
			$q .= " order by $order";
		//$r['r'] = sqlEx($q);
		$r['r'] = mysql_query($q, $mLink);
		if ($show or $r['r'] === false)
		{	$error['table'] = $table;
			$error['conditions'] = $conditions;
			$error['group'] = $group;
			$error['order'] = $order;
			$error['fields'] = $fields;
			$error['show'] = $show;
			$error['query'] = $q;
			$error['mLink'] = ($mLink ? "": "not")." valid";
			$error['rLink'] = ($rLink ? "": "not")." valid";
			$error['show'] = $show;
			debugDump($error, "sqlSelect Error", $show);
			$r['sqlSelect'] = $errors;
		}
		if ($r['r'] !== false)
			$r['n'] = mysql_num_rows($r['r']);
		$r['o'] = array();
		$r['q'] = $q;
		return $r;
	}

	function sqlInsert($table, $values, $no_suspess = true)
	{	GLOBAL $mLink;
		// generate next tid value
		$tid = getNextTID();
		// create a "blank row" with the new tid and the values sent
		$q = "insert into $table set ".lastmod($table)."tid = '$tid', $values";
		mysql_query($q, $mLink);
		logChange($table, $tid);

		// read back the new line
		$r = sqlSelect($table, "tid = '$tid'");
		$r['o'] = sqlSelect($table, "tid = '$tid'");
		$r['n'] = 1;
		$r['t'] = $tid;
		$r['q'] = $q;
		return $r;
	}

	function sqlRecycle($table, $values, $test, $no_suspess = true)
	{	$slot = sqlSelect($table, $test);
		if ($slot['n'])
		{	$slot = mysql_fetch_assoc($slot['r']);
			$slot = $slot['tid'];
			$r = sqlUpdate($table, $values, "tid='$slot'");
			$r['t'] = $slot;
		}
		else
			$r = sqlInsert($table, $values);
		return $r;
	}

	function sqlUpdate($table, $values, $conditions = "1", $mod = true, $no_suspess = true)
	{	GLOBAL $mLink;

		$rT = "select * from $table where $conditions";
		$oldList['r'] = mysql_query($rT, $mLink);
		$o = $oldList;
		$tList = "";
		while ($l = mysql_fetch_assoc($oldList['r']))
		{	//generate a list of tids of affected rows
			if (!empty($tidList))
				$tidList .= " or";
			$tidList .= " tid = '{$l['tid']}'";
			$tList[] = $l['tid'];
		}

		// only do the update if there are affected rows
		if ($tList)
		{	// log the changes
			foreach ($tList as $t)
				logChange($table, $t);

			// do the update
			if ($mod)
				$q = "update $table set ".lastmod($table)."$values where $conditions";
			else
				$q = "update $table set $values where $conditions";
			mysql_query($q, $mLink);
			// finally, capture all the NEW values
			$r = sqlSelect($table, $tidList);
			$r['o'] = $o;
		}
		else
		{	$r['r'] = array();
			$r['n'] = 0;
			$r['o'] = array();
		}
		$r['q'] = $rT;
		return $r;
	}

	function sqlReplace($table, $values, $conditions, $no_suspess = true)
	{	GLOBAL $mLink;

		// see if there are updatable rows
		$r = sqlSelect($table, $conditions);
		if ($r['n'])
		{	while ($s = mysql_fetch_assoc($r['r']))
			{	mysql_query("update $table set lastmod = '".date("YmdHis")."', $values where tid='{$s['tid']}'", $mLink);
				logChange($table, $s['tid']);
			}
			return $r;
		}
		else
		{	// no rows were updated, so do an insert instead
			$r = sqlInsert($table, $values, $no_suspess);
		}
		return $r;
	}

	function sqlDelete($table, $conditions)
	{	$oldList = sqlSelect($table, $conditions);

		$q = "delete from $table where $conditions";
		$r = sqlEx($q);
		if (!is_array($r))
			$r = array();
		$r['o'] = $oldList;
		return $r;
	}

	function sqlCount($table, $conditions)
	{	GLOBAL $mLink;

		$r = mysql_query("select count(*) as cnt from `$table` where $conditions", $mLink);
		$r = mysql_fetch_assoc($r);
		return $r['cnt'];
	}

	function sqlRemCount($table, $conditions)
	{	GLOBAL $rLink;

		$r = mysql_query("select count(*) as cnt from `$table` where $conditions", $rLink);
		$r = mysql_fetch_assoc($r);
		return $r['cnt'];
	}
?>