qbuilder/function.php

<?php

	$_ENV["logdir"] = dirname(__FILE__).DIRECTORY_SEPARATOR.'logs'.DIRECTORY_SEPARATOR;
	define('LOG4PHP_DIR', dirname(__FILE__).DIRECTORY_SEPARATOR.'log4php');
	require_once(LOG4PHP_DIR . '/LoggerManager.php');

	function openLogger($main) {
		global $logger;

		if (isset($_SERVER['SERVER_PROTOCOL'])) {
			echo "<pre>";
			/*
			$rootLogger = LoggerManager::getRootLogger();
			$appenderA2 =& $rootLogger->getAppender('A2');
			$layout =& LoggerLayout::factory('LoggerLayoutHtml');
			$layout->setLocationInfo(false);
			$appenderA2->setLayout($layout);
			*/
		}
		$logger = LoggerManager::getLogger($main);
		$logger->info("start");
	}

	function closeLogger() {
		global $logger;
		$logger->info("finish");
		LoggerManager::shutdown();

		if (isset($_SERVER['SERVER_PROTOCOL'])) {
			echo "</pre>";
		}
	}

	function createTableCRC($tableid) {
		global $adodb;

		$logger = LoggerManager::getLogger(__FUNCTION__);

		## CHECK COLUMN ##
		$metaCol = $adodb->MetaColumns($tableid);
		if (!is_array($metaCol)) {
			return $logger->error("Columns must be defined on ".$tableid."!");
		}

		$metaTables = $adodb->MetaTables();
		foreach ($metaTables as $k => $v) $metaTables[$k] = strtolower($v);

		## <table>_CRC ##
		$rs = $adodb->Execute("SELECT * FROM qb_config ".
		" WHERE tableid = ? ", array($tableid));
		if (!$rs->EOF) {
			$sqlCreate = $rs->fields['sqlcreate'];
			if (! is_int(array_search(strtolower($tableid)."_crc", $metaTables))) {
				$rs = $adodb->Execute("CREATE TABLE ".$tableid."_crc ".
				" ( ".$sqlCreate." ) ");
				if (!$rs) return $logger->error($adodb->ErrorMsg());
			}
		} else {
			return $logger->error("tableid '".$tableid."' not found on qb_config! ".
			" ".$adodb->ErrorMsg());
		}

		## CHECK COLUMN ##
		$metaCol = $adodb->MetaColumns($tableid."_crc");
		if (!is_array($metaCol)) {
			return $logger->error("Columns must be defined on ".$tableid."_crc !");
		}

		// DROP column where not PK,crc
		$evalStr = $colPK = $evalStr2 = $metaColStr2 = '';
		foreach ($metaCol as $k => $v) {
			if ($v->name === 'crc') continue;
			$evalStr2 .= '($rs->fields["'.$v->name.'"])."\\t".';
			if ($v->primary_key !== true) {
				$adodb->Execute("ALTER TABLE ".$tableid."_crc DROP ".$v->name);
			} else {
				$evalStr .= '($rs->fields["'.$v->name.'"])."\\t".'; // fwrite
				$colPK .= $v->name.",";
			}
			$metaColStr2 .= $v->name.",";
		}
		if ($evalStr) $evalStr = substr($evalStr, 0, -5);
		if ($evalStr2) $evalStr2 = substr($evalStr2, 0, -5);
		if ($colPK) $colPK = substr($colPK, 0, -1);
		if ($metaColStr2) $metaColStr2 = substr($metaColStr2, 0, -1);
		$evalStr = 'fwrite($fpTemp, '.$evalStr.'"\\t".$crc32."\\n");';

		// COLUMN crc, INDEX
		$metaCol = $adodb->MetaColumns($tableid."_crc");
		if (!$metaCol["CRC"]) {
			$adodb->Execute("ALTER TABLE ".$tableid."_crc ADD crc INTEGER");
		}
		$metaIndexes = $adodb->MetaIndexes($tableid."_crc");
		if (! is_array($metaIndexes[$tableid."_crc"."_idx1"])) {
			$adodb->Execute("CREATE INDEX ".$tableid."_crc"."_idx1 ".
			" ON ".$tableid."_crc(crc)");
		}

		return compact("evalStr", "evalStr2", "colPK", "metaColStr2");
	}

	function createTableHistory($tableid) {
		global $adodb;

		$logger = LoggerManager::getLogger(__FUNCTION__);

		## CHECK COLUMN ##
		$metaCol = $adodb->MetaColumns($tableid);
		if (!is_array($metaCol)) {
			return $logger->error("Columns must be defined on ".$tableid."!");
		}

		$metaTables = $adodb->MetaTables();
		foreach ($metaTables as $k => $v) $metaTables[$k] = strtolower($v);

		## <table>_history ##
		$rs = $adodb->Execute("SELECT * FROM qb_config ".
		" WHERE tableid = ? ", array($tableid));
		if (!$rs->EOF) {
			$sqlCreate = $rs->fields['sqlcreate'];
			if (! is_int(array_search(strtolower($tableid)."_history", $metaTables))) {
				$rs = $adodb->Execute("CREATE TABLE ".$tableid."_history ".
				" ( ".$sqlCreate." ) ");
				if (!$rs) return $logger->error($adodb->ErrorMsg());
			}
		} else {
			return $logger->error("tableid '".$tableid."' not found on qb_config! ".
			" ".$adodb->ErrorMsg());
		}

		## CHECK COLUMN ##
		$metaCol = $adodb->MetaColumns($tableid."_history");
		if (!is_array($metaCol)) {
			return $logger->error("Columns must be defined on ".$tableid."_history !");
		}

		// COLUMN date_deleted
		$metaCol = $adodb->MetaColumns($tableid."_history");
		if (!$metaCol["DATE_DELETED"]) {
			$adodb->Execute("ALTER TABLE ".$tableid."_history ADD date_deleted DATETIME NOT NULL");
            $pks = $adodb->MetaPrimaryKeys($tableid."_history");
            $adodb->Execute("ALTER TABLE ".$tableid."_history DROP PRIMARY KEY");
            $adodb->Execute("ALTER TABLE ".$tableid."_history ADD PRIMARY KEY (".join(", ", $pks).", date_deleted )");
		}

		return true;
	}

	function crcTable($conf) {
		global $adodb;

		$tableid = $conf['tableid'];

		$logger = LoggerManager::getLogger(__FUNCTION__);
		$logger->info("Table: ".$tableid);

		## CHECK PK ##
		$rs3 = $adodb->MetaPrimaryKeys($tableid);
		if (!is_array($rs3) || (is_array($rs3) && count($rs3) == 0)) {
			return $logger->error("PrimaryKeys must be defined !");
		}

		$adodb->Execute("DROP TABLE ".$tableid."_crc");
		$result = createTableCRC($tableid);
		if (! is_array($result)) return false;
		extract($result);

		## FILE TEMPORARY ##
		if (!isset($tmpTemp)) $tmpTemp = tempnam("/tmp/", "tableTemp"); // create
		chmod($tmpTemp, 0777);
		$fpTemp = fopen($tmpTemp, "w");

		$rs = $adodb->Execute("SELECT * FROM ".$tableid);

		$j = 0;
		$logger->info("Progress: ".$j." record(s)");

		while (!$rs->EOF) {
			foreach ($rs->fields as $k => $v) {
				$rs->fields[$k] = addcslashes($v, "\t\\");
			}

			eval('$buffer = addcslashes('.substr($evalStr2, 0, -1).', "\n");');
			$crc32 = crc32($buffer);
			eval($evalStr);

			$j++;

			if ($j%1000===0) {
				$logger->info("Progress: ".$j." record(s)");
			}

			if ($j%10000===0) {
				fclose($fpTemp);
				$rs2 = $adodb->Execute(
				" LOAD DATA INFILE '".addslashes($tmpTemp)."' ".
				" REPLACE INTO TABLE ".$tableid."_crc (".$colPK.", crc);");
				if (!$rs2) return $logger->error($adodb->ErrorMsg());

				$fpTemp = fopen($tmpTemp, "w");
			}
			$rs->MoveNext();
		}

		if ($j%10000!==0) {
			## FILE TEMP ##
			fclose($fpTemp);
			$rs2 = $adodb->Execute(
			" LOAD DATA INFILE '".addslashes($tmpTemp)."' ".
			" REPLACE INTO TABLE ".$tableid."_crc (".$colPK.", crc);");
			if (!$rs2) return $logger->error($adodb->ErrorMsg());
		}

		if (file_exists($tmpTemp)) @unlink($tmpTemp);
		return $logger->info("Total: ".$j." record(s)");
	}

	function checkNew() {
		global $new_record2, $bufferLog, $fpLog;
		if ($new_record2 > 0) {
			$buffer = $bufferLog.
			"\t"."new".
			"\t".$new_record2." records";
			fwrite($fpLog, $buffer."\n");
		}
		$new_record2 = 0;
	}

	function openTemp() {
		global $tmpData, $tmpLog, $tmpTemp, $openTemp;

		## FILE DATA ##
		$tmpData = tempnam("/tmp/", "tableData");
		chmod($tmpData, 0777);

		## FILE LOG ##
		$tmpLog = tempnam("/tmp/", "tableLog");
		chmod($tmpLog, 0777);

		## FILE TEMP ##
		$tmpTemp = tempnam("/tmp/", "tableTemp");
		chmod($tmpTemp, 0777);

		$openTemp = true;
	}

	function openFile() {
		global $openTemp;
		global $fpData, $tmpData;
		global $fpLog, $tmpLog;
		global $fpTemp, $tmpTemp;

		if(!isset($openTemp)) openTemp();

		## FILE DATA ##
		$fpData = fopen($tmpData, "w");

		## FILE LOG ##
		$fpLog = fopen($tmpLog, "w");

		## FILE TEMP ##
		$fpTemp = fopen($tmpTemp, "w");
	}

	function loadData() {
		global $adodb;
		global $tableid, $columnTable;
		global $fpData, $tmpData;
		global $fpLog, $tmpLog;
		global $fpTemp, $tmpTemp;
		global $colPK, $loadDataSolution;
		global $sqlType;

		$logger = LoggerManager::getLogger(__FUNCTION__);

		## FILE DATA ##
		fclose($fpData);
		#if (filesize($tmpData)>0)
		#	echo fread(fopen($tmpData, 'r'), filesize($tmpData));
		if ($sqlType === 'new') {
			checkNew();
			$query = ("LOAD DATA INFILE '".addslashes($tmpData)."' ".
			" REPLACE INTO TABLE ".$tableid." (".$columnTable.");");
			$logger->info($query);
			$rs2 = $adodb->Execute($query);
			if (!$rs2) return $logger->error($adodb->ErrorMsg());
		}

		## FILE LOG ##
		fclose($fpLog);
		#if (filesize($tmpLog)>0)
		#	echo fread(fopen($tmpLog, 'r'), filesize($tmpLog));
		$query = ("LOAD DATA INFILE '".addslashes($tmpLog)."' ".
		" INTO TABLE qb_log (id, tableid, userid, jam, status, message);");
		$logger->info($query);
		$rs2 = $adodb->Execute($query);
		if (!$rs2) return $logger->error($adodb->ErrorMsg());

		## FILE TEMP ##
		fclose($fpTemp);
		if ($sqlType === 'new' || $sqlType === 'updated') {
			$query = ("LOAD DATA INFILE '".addslashes($tmpTemp)."' ".
			" REPLACE INTO TABLE ".$tableid."_crc (".$colPK.", crc);");
			$logger->info($query);
			$rs2 = $adodb->Execute($query);
			if (!$rs2) return $logger->error($adodb->ErrorMsg());
		}
	}

	function closeFile() {
		global $tmpData, $tmpLog, $tmpTemp;
		if (file_exists($tmpData)) @unlink($tmpData);
		if (file_exists($tmpLog)) @unlink($tmpLog);
		if (file_exists($tmpTemp)) @unlink($tmpTemp);
	}

	function diffTable($conf) {
		global $adodb;
		global $tableid, $columnTable;
		global $fpData, $tmpData;
		global $fpLog, $tmpLog;
		global $fpTemp, $tmpTemp;
		global $colPK, $loadDataSolution;
		global $sqlType;
		global $bufferLog, $new_record2;

		$logger = LoggerManager::getLogger(__FUNCTION__);

		//ini_set('max_execution_time', 0);
		//require_once 'auth.php';

		$new_record2 = $new_record = $unchanges = $updated = 0;
		$del_record2 = $del_record = 0;

		$tableid = $conf['tableid'];
		$logger->info("Table: ".$tableid);

		## CHECK PK ##
		$rs3 = $adodb->MetaPrimaryKeys($tableid);
		if (!is_array($rs3) || (is_array($rs3) && count($rs3) == 0)) {
			$logger->error("PrimaryKeys must be defined !");
			return;
		}

		$dummysql = $bindsql = $param = '';
		foreach ($rs3 as $k => $v) {
			if ($bindsql) $bindsql .= " AND ";
			$bindsql .= $v." = ?";
			if ($param) $param .= ', ';
			$param .= '$record["'.$v.'"]';
		}
		$dummysql = '$rs4 = $adodb->Execute('.
		'"SELECT * FROM '.$tableid.' WHERE '.$bindsql.'", array('.$param.'));';

		$result = createTableHistory($tableid);
		if ($result !== true) return false;
		$rs5 = $adodb->Execute("SELECT * FROM ".$tableid."_history");

		$deletesql = '$rs4 = $adodb->Execute('.
		'"DELETE FROM '.$tableid.' WHERE '.$bindsql.'", array('.$param.'));';
		$deletecrcsql = '$rs4 = $adodb->Execute('.
		'"DELETE FROM '.$tableid.'_crc WHERE '.$bindsql.'", array('.$param.'));';

		$result = createTableCRC($tableid);
		if (! is_array($result)) return false;
		extract($result);
		$evalStr = str_replace("\$crc32", "\$rs->fields[\"crc\"]", $evalStr);

		## DUMMY LOG ##
		$recordLog = array();
		$recordLog['tableid'] = $tableid;
		$recordLog['userid'] = $_SESSION['userid'];
		$recordLog['jam'] = date('Y-m-d H:i:s');
		$bufferLog = "\t".$recordLog['tableid'].
		"\t".$recordLog['userid'].
		"\t".$recordLog['jam'];

		$arrSQL = array();

		## EXECUTE ##
		$sqlBase = "SELECT qb_temp_".$tableid.".* FROM qb_temp_".$tableid." ".
		" LEFT JOIN ".$tableid."_crc USING (".$colPK.") ";
		$sqlNew = " WHERE ".$tableid."_crc.crc IS NULL ";
		$sqlUpd = " WHERE qb_temp_".$tableid.".crc <> ".$tableid."_crc.crc "; //.

		$arrSQL['new'] = $sqlBase.$sqlNew;
		$arrSQL['updated'] = $sqlBase.$sqlUpd;

		$sqlBase = "SELECT ".$tableid."_crc.* FROM ".$tableid."_crc ".
		" LEFT JOIN qb_temp_".$tableid." USING (".$colPK.") ";
		$sqlDel = " WHERE qb_temp_".$tableid.".crc IS NULL ";
		$arrSQL['deleted'] = $sqlBase.$sqlDel;

		foreach ($arrSQL as $sqlType => $sql) {

			$j=0;
			openFile();

			$logger->info("SQL: ".$sql);
			$rs = $adodb->Execute($sql);

			if (!$rs) {
				$logger->error($adodb->ErrorMsg());
				continue;
			}

			$logger->info("Progress: ".$j." record(s)");
			if (!$rs->EOF) {
				$newColumn = $rs->fields;
				unset($newColumn['crc']);
				$columnTable = implode(",", array_keys($newColumn));
				//$columnTable = $metaColStr2;
			}

			while (!$rs->EOF) {
				$recordLog['status'] = $recordLog['message'] = '';
				$record =& $rs->fields;
				foreach ($record as $k => $v) {
					$record[$k] = addcslashes($v, "\t\\");
				}

				if ($sqlType === 'new') {
					$recordLog['status'] = 'new';
					$new_record++;
					$new_record2++;
				} else if ($sqlType === 'updated') {
					eval($dummysql);
					if (!$rs4) {
						$logger->error($adodb->ErrorMsg());
						$rs->MoveNext();
						continue;
					} else if ($rs4->EOF) {
						$logger->error("EOF");
						$rs->MoveNext();
						continue;
					}

					$updateSQL = $adodb->GetUpdateSQL($rs4, $rs->fields);
					if (empty($updateSQL)) {
						$unchanges++;
					} else {
						$recordLog['status'] = 'updated';
						$updated++;

						foreach ($rs4->fields as $k => $v) {
							if ($v === @$record[$k]) continue;
							$msg = $k.":'".$v."'=>'".$record[$k]."', ";
							$recordLog['message'] .= $msg;
						}
						$adodb->Execute($updateSQL);
					}
				} else {
					#$adodb->debug = 1;
					#echo ($dummysql);

					// TODO: bikin table history
					eval($deletecrcsql);
					eval($dummysql);
					if (!$rs4) {
						$logger->error($adodb->ErrorMsg());
						$rs->MoveNext();
						continue;
					} else if ($rs4->EOF) {
						$logger->error("EOF");
						$rs->MoveNext();
						continue;
					}

					foreach ($rs4->fields as $k => $v) {
						$msg = $k.":'".$record[$k]."', ";
						$recordLog['message'] .= $msg;
					}

					$recordHistory = $rs4->fields;
					$recordHistory['date_deleted'] = $recordLog['jam'];
					$historysql = $adodb->GetInsertSQL($rs5, $recordHistory);
					$historysql = str_replace("INSERT INTO", "REPLACE INTO", $historysql);
					$result = $adodb->Execute($historysql);
					if (!$result) $logger->error($adodb->ErrorMsg());
					//else $del_record2++;

					eval($deletesql);
					$recordLog['status'] = 'deleted';

					$del_record++;
				}

				$j++;
				if ($j%1000===0) {
					$logger->info("Progress: ".$j." record(s)");
				}

				if ($j%20000===0) {
					loadData();
					openFile();
				}

				## CRC ##
				eval($evalStr);

				## LOG ##
				if ($recordLog['message']) {
					$buffer = $bufferLog.
					"\t".$recordLog['status'].
					"\t".$recordLog['message'];
					fwrite($fpLog, $buffer."\n");
				} else {

					## DATA ##
					unset($record['crc']);
					$buffer = addcslashes(implode("\t", $record), "\n");
					fwrite($fpData, $buffer."\n");
				}

				$rs->MoveNext();
			}

			$logger->info("Total: ".$j." record(s)");
			if ($j%20000!==0) {
				loadData();
				//openFile();
			}
		}

		closeFile();

		$logger->info($new_record." new record(s)");
		$logger->info($updated." updated record(s)");
		$logger->info($unchanges." unchanges record(s)");
		$logger->info($del_record." deleted record(s)");
	}


	function replaceTable($conf) {
		global $adodb;
		global $tableid, $columnTable;
		global $fpData, $tmpData;
		global $fpLog, $tmpLog;
		global $fpTemp, $tmpTemp;
		global $colPK, $loadDataSolution;
		global $sqlType;
		global $bufferLog, $new_record2;

		$logger = LoggerManager::getLogger(__FUNCTION__);

		//ini_set('max_execution_time', 0);
		//require_once 'auth.php';

		$new_record2 = $new_record = $unchanges = $updated = 0;
		$del_record2 = $del_record = 0;

		$tableid = $conf['tableid'];
		$logger->info("Table: ".$tableid);

		## CHECK PK ##
		$rs3 = $adodb->MetaPrimaryKeys($tableid);
		if (!is_array($rs3) || (is_array($rs3) && count($rs3) == 0)) {
			$logger->error("PrimaryKeys must be defined !");
			return;
		}

		$dummysql = $bindsql = $param = '';
		foreach ($rs3 as $k => $v) {
			if ($bindsql) $bindsql .= " AND ";
			$bindsql .= $v." = ?";
			if ($param) $param .= ', ';
			$param .= '$record["'.$v.'"]';
		}
		$dummysql = '$rs4 = $adodb->Execute('.
		'"SELECT * FROM '.$tableid.' WHERE '.$bindsql.'", array('.$param.'));';

		$result = createTableHistory($tableid);
		if ($result !== true) return false;
		$rs5 = $adodb->Execute("SELECT * FROM ".$tableid."_history");

		$deletesql = '$rs4 = $adodb->Execute('.
		'"DELETE FROM '.$tableid.' WHERE '.$bindsql.'", array('.$param.'));';
		$deletecrcsql = '$rs4 = $adodb->Execute('.
		'"DELETE FROM '.$tableid.'_crc WHERE '.$bindsql.'", array('.$param.'));';

		$result = createTableCRC($tableid);
		if (! is_array($result)) return false;
		extract($result);
		$evalStr = str_replace("\$crc32", "\$rs->fields[\"crc\"]", $evalStr);

		## DUMMY LOG ##
		$recordLog = array();
		$recordLog['tableid'] = $tableid;
		$recordLog['userid'] = $_SESSION['userid'];
		$recordLog['jam'] = date('Y-m-d H:i:s');
		$bufferLog = "\t".$recordLog['tableid'].
		"\t".$recordLog['userid'].
		"\t".$recordLog['jam'];

		$arrSQL = array();

		## EXECUTE ##
		$sqlBase = "SELECT qb_temp_".$tableid.".* FROM qb_temp_".$tableid." ".
		" LEFT JOIN ".$tableid."_crc USING (".$colPK.") ";
		$sqlNew = " WHERE ".$tableid."_crc.crc IS NULL ";
		$sqlUpd = " WHERE qb_temp_".$tableid.".crc <> ".$tableid."_crc.crc "; //.

		$arrSQL['new'] = $sqlBase.$sqlNew;
		$arrSQL['updated'] = $sqlBase.$sqlUpd;

		$sqlBase = "SELECT ".$tableid."_crc.* FROM ".$tableid."_crc ".
		" LEFT JOIN qb_temp_".$tableid." USING (".$colPK.") ";
		$sqlDel = " WHERE qb_temp_".$tableid.".crc IS NULL ";
		$arrSQL['deleted'] = $sqlBase; //.$sqlDel;

		foreach ($arrSQL as $sqlType => $sql) {

			$j=0;
			openFile();

			$logger->info("SQL: ".$sql);
			$rs = $adodb->Execute($sql);

			if (!$rs) {
				$logger->error($adodb->ErrorMsg());
				continue;
			}

			$logger->info("Progress: ".$j." record(s)");
			if (!$rs->EOF) {
				$newColumn = $rs->fields;
				unset($newColumn['crc']);
				$columnTable = implode(",", array_keys($newColumn));
				//$columnTable = $metaColStr2;
			}

			while (!$rs->EOF) {
				$recordLog['status'] = $recordLog['message'] = '';
				$record =& $rs->fields;
				foreach ($record as $k => $v) {
					$record[$k] = addcslashes($v, "\t\\");
				}

				if ($sqlType === 'new') {
					$recordLog['status'] = 'new';
					$new_record++;
					$new_record2++;
				} else if ($sqlType === 'updated') {
					eval($dummysql);
					if (!$rs4) {
						$logger->error($adodb->ErrorMsg());
						$rs->MoveNext();
						continue;
					} else if ($rs4->EOF) {
						$logger->error("EOF");
						$rs->MoveNext();
						continue;
					}

					$updateSQL = $adodb->GetUpdateSQL($rs4, $rs->fields);
					if (empty($updateSQL)) {
						$unchanges++;
					} else {
						$recordLog['status'] = 'updated';
						$updated++;

						foreach ($rs4->fields as $k => $v) {
							if ($v === $record[$k]) continue;
							$msg = $k.":'".$v."'=>'".$record[$k]."', ";
							$recordLog['message'] .= $msg;
						}
						$adodb->Execute($updateSQL);
					}
				} else {
					#$adodb->debug = 1;
					#echo ($dummysql);

					// TODO: bikin table history
					//eval($deletecrcsql);
					eval($dummysql);
					if (!$rs4) {
						$logger->error($adodb->ErrorMsg());
						$rs->MoveNext();
						continue;
					} else if ($rs4->EOF) {
						$logger->error("EOF");
						$rs->MoveNext();
						continue;
					}

					foreach ($rs4->fields as $k => $v) {
						$msg = $k.":'".$record[$k]."', ";
						$recordLog['message'] .= $msg;
					}

					$recordHistory = $rs4->fields;
					$recordHistory['date_deleted'] = $recordLog['jam'];
					$historysql = $adodb->GetInsertSQL($rs5, $recordHistory);
					//$historysql = str_replace("INSERT INTO", "REPLACE INTO", $historysql);
					$result = $adodb->Execute($historysql);
					if (!$result) $logger->error($adodb->ErrorMsg());
					//else $del_record2++;

					//eval($deletesql);
					$recordLog['status'] = 'deleted';

					$del_record++;
				}

				$j++;
				if ($j%1000===0) {
					$logger->info("Progress: ".$j." record(s)");
				}

				if ($j%20000===0) {
					loadData();
					openFile();
				}

				## CRC ##
				eval($evalStr);

				## LOG ##
				if ($recordLog['message']) {
					$buffer = $bufferLog.
					"\t".$recordLog['status'].
					"\t".$recordLog['message'];
					fwrite($fpLog, $buffer."\n");
				} else {

					## DATA ##
					unset($record['crc']);
					$buffer = addcslashes(implode("\t", $record), "\n");
					fwrite($fpData, $buffer."\n");
				}

				$rs->MoveNext();
			}

			$logger->info("Total: ".$j." record(s)");
			if ($j%20000!==0) {
				loadData();
				//openFile();
			}
		}

		closeFile();

		$logger->info($new_record." new record(s)");
		$logger->info($updated." updated record(s)");
		$logger->info($unchanges." unchanges record(s)");
		$logger->info($del_record." deleted record(s)");
	}

	function updateTable($conf) {
		global $adodb;
		global $tableid, $columnTable;
		global $fpData, $tmpData;
		global $fpLog, $tmpLog;
		global $fpTemp, $tmpTemp;
		global $colPK, $loadDataSolution;
		global $sqlType;
		global $bufferLog, $new_record2;

		$logger = LoggerManager::getLogger(__FUNCTION__);

		//ini_set('max_execution_time', 0);
		//require_once 'auth.php';

		$new_record2 = $new_record = $unchanges = $updated = 0;
		$del_record2 = $del_record = 0;

		$tableid = $conf['tableid'];
		$logger->info("Table: ".$tableid);

		## CHECK PK ##
		$rs3 = $adodb->MetaPrimaryKeys($tableid);
		if (!is_array($rs3) || (is_array($rs3) && count($rs3) == 0)) {
			$logger->error("PrimaryKeys must be defined !");
			return;
		}

		$dummysql = $bindsql = $param = '';
		foreach ($rs3 as $k => $v) {
			if ($bindsql) $bindsql .= " AND ";
			$bindsql .= $v." = ?";
			if ($param) $param .= ', ';
			$param .= '$record["'.$v.'"]';
		}
		$dummysql = '$rs4 = $adodb->Execute('.
		'"SELECT * FROM '.$tableid.' WHERE '.$bindsql.'", array('.$param.'));';

		$result = createTableHistory($tableid);
		if ($result !== true) return false;
		$rs5 = $adodb->Execute("SELECT * FROM ".$tableid."_history");

		$deletesql = '$rs4 = $adodb->Execute('.
		'"DELETE FROM '.$tableid.' WHERE '.$bindsql.'", array('.$param.'));';
		$deletecrcsql = '$rs4 = $adodb->Execute('.
		'"DELETE FROM '.$tableid.'_crc WHERE '.$bindsql.'", array('.$param.'));';

		$result = createTableCRC($tableid);
		if (! is_array($result)) return false;
		extract($result);
		$evalStr = str_replace("\$crc32", "\$rs->fields[\"crc\"]", $evalStr);

		## DUMMY LOG ##
		$recordLog = array();
		$recordLog['tableid'] = $tableid;
		$recordLog['userid'] = $_SESSION['userid'];
		$recordLog['jam'] = date('Y-m-d H:i:s');
		$bufferLog = "\t".$recordLog['tableid'].
		"\t".$recordLog['userid'].
		"\t".$recordLog['jam'];

		$arrSQL = array();

		## EXECUTE ##
		$sqlBase = "SELECT qb_temp_".$tableid.".* FROM qb_temp_".$tableid." ".
		" LEFT JOIN ".$tableid."_crc USING (".$colPK.") ";
		$sqlNew = " WHERE ".$tableid."_crc.crc IS NULL ";
		$sqlUpd = " WHERE qb_temp_".$tableid.".crc <> ".$tableid."_crc.crc "; //.

		$arrSQL['new'] = $sqlBase.$sqlNew;
		$arrSQL['updated'] = $sqlBase.$sqlUpd;

		$sqlBase = "SELECT ".$tableid."_crc.* FROM ".$tableid."_crc ".
		" LEFT JOIN qb_temp_".$tableid." USING (".$colPK.") ";
//		$sqlDel = " WHERE qb_temp_".$tableid.".crc IS NULL ";
//		$arrSQL['deleted'] = $sqlBase.$sqlDel;

		foreach ($arrSQL as $sqlType => $sql) {

			$j=0;
			openFile();

			$logger->info("SQL: ".$sql);
			$rs = $adodb->Execute($sql);

			if (!$rs) {
				$logger->error($adodb->ErrorMsg());
				continue;
			}

			$logger->info("Progress: ".$j." record(s)");
			if (!$rs->EOF) {
				$newColumn = $rs->fields;
				unset($newColumn['crc']);
				$columnTable = implode(",", array_keys($newColumn));
				//$columnTable = $metaColStr2;
			}

			while (!$rs->EOF) {
				$recordLog['status'] = $recordLog['message'] = '';
				$record =& $rs->fields;
				foreach ($record as $k => $v) {
					$record[$k] = addcslashes($v, "\t\\");
				}

				if ($sqlType === 'new') {
					$recordLog['status'] = 'new';
					$new_record++;
					$new_record2++;
				} else if ($sqlType === 'updated') {
					eval($dummysql);
					if (!$rs4) {
						$logger->error($adodb->ErrorMsg());
						$rs->MoveNext();
						continue;
					} else if ($rs4->EOF) {
						$logger->error("EOF");
						$rs->MoveNext();
						continue;
					}

					$updateSQL = $adodb->GetUpdateSQL($rs4, $rs->fields);
					if (empty($updateSQL)) {
						$unchanges++;
					} else {
						$recordLog['status'] = 'updated';
						$updated++;

						foreach ($rs4->fields as $k => $v) {
							if ($v === $record[$k]) continue;
							$msg = $k.":'".$v."'=>'".$record[$k]."', ";
							$recordLog['message'] .= $msg;
						}
						$adodb->Execute($updateSQL);
					}
				} else {
					#$adodb->debug = 1;
					#echo ($dummysql);

					// TODO: bikin table history
					eval($deletecrcsql);
					eval($dummysql);
					if (!$rs4) {
						$logger->error($adodb->ErrorMsg());
						$rs->MoveNext();
						continue;
					} else if ($rs4->EOF) {
						$logger->error("EOF");
						$rs->MoveNext();
						continue;
					}

					foreach ($rs4->fields as $k => $v) {
						$msg = $k.":'".$record[$k]."', ";
						$recordLog['message'] .= $msg;
					}

					$recordHistory = $rs4->fields;
					$recordHistory['date_deleted'] = $recordLog['jam'];
					$historysql = $adodb->GetInsertSQL($rs5, $recordHistory);
					$historysql = str_replace("INSERT INTO", "REPLACE INTO", $historysql);
					$result = $adodb->Execute($historysql);
					if (!$result) $logger->error($adodb->ErrorMsg());
					//else $del_record2++;

					eval($deletesql);
					$recordLog['status'] = 'deleted';

					$del_record++;
				}

				$j++;
				if ($j%1000===0) {
					$logger->info("Progress: ".$j." record(s)");
				}

				if ($j%20000===0) {
					loadData();
					openFile();
				}

				## CRC ##
				eval($evalStr);

				## LOG ##
				if ($recordLog['message']) {
					$buffer = $bufferLog.
					"\t".$recordLog['status'].
					"\t".$recordLog['message'];
					fwrite($fpLog, $buffer."\n");
				} else {

					## DATA ##
					unset($record['crc']);
					$buffer = addcslashes(implode("\t", $record), "\n");
					fwrite($fpData, $buffer."\n");
				}

				$rs->MoveNext();
			}

			$logger->info("Total: ".$j." record(s)");
			if ($j%20000!==0) {
				loadData();
				//openFile();
			}
		}

		closeFile();

		$logger->info($new_record." new record(s)");
		$logger->info($updated." updated record(s)");
		$logger->info($unchanges." unchanges record(s)");
		$logger->info($del_record." deleted record(s)");
	}

	function importTable($conf) {
		global $adodb, $tmpTemp;

		$logger = LoggerManager::getLogger(__FUNCTION__);

		$odbc_dsn = $conf['dsn'];
		if (empty($odbc_dsn)) {
			return $logger->error("Error: Empty DSN");
		}
		$logger->info("DSN: ".$odbc_dsn);

		$odbc =& ADONewConnection($odbc_dsn);
		if (!$odbc || $errorMsg=$odbc->ErrorMsg()) {
			if ($errorMsg) return $logger->error($errorMsg);
			else return $logger->error("Connect: FAILED");
		}

		$odbc->SetFetchMode(ADODB_FETCH_ASSOC);

		## CHECK PK ##
		$tableid = $conf['tableid'];
		$rs3 = $adodb->MetaPrimaryKeys($tableid);
		if (!is_array($rs3) || (is_array($rs3) && count($rs3) == 0)) {
			return $logger->error("PrimaryKeys must be defined !");
		}

		$sql = $conf['sqlimport'];
		if (isset($conf['sql'])) $sql = $conf['sql'];
		if (!$sql) {
			return $logger->error("Empty SQL Import");
		}

		// $logger->info("SQL Import: ".$sql);

		## START EXECUTE ##
		$rs = $odbc->Execute($sql);

		$j = 0; $idConfig = null;
		if (!is_object($rs)) {
			return $logger->error($odbc->ErrorMsg());
		} else {

			## qb_temp_<table> ##
			$adodb->Execute("DROP TABLE qb_temp_".$tableid);
			$rs2 = $adodb->Execute("SELECT * FROM qb_config ".
			" WHERE tableid = ? ", array($tableid));
			if (!$rs2->EOF) {
				$idConfig = $rs2->fields['id'];
				$sqlCreate = $rs2->fields['sqlcreate'];
				$rs2 = $adodb->Execute("CREATE TABLE qb_temp_".$tableid." ".
				" ( ".$sqlCreate." ) ");
				if (!$rs2) return $logger->error($adodb->ErrorMsg());
			} else {
				return $logger->error("tableid '".$tableid."' not found ".
				" on qb_config! ".$adodb->ErrorMsg());
			}

			// COLUMN crc, INDEX
			$metaCol = $adodb->MetaColumns("qb_temp_".$tableid);
			if (!$metaCol["CRC"]) {
				$adodb->Execute("ALTER TABLE qb_temp_".$tableid." ADD crc INTEGER");
			}
			$metaIndexes = $adodb->MetaIndexes("qb_temp_".$tableid);
			if (! is_array($metaIndexes["qb_temp_".$tableid."_idx1"])) {
				$adodb->Execute("CREATE INDEX qb_temp_".$tableid."_idx1 ".
				" ON qb_temp_".$tableid."(crc)");
			}

			## FILE TEMPORARY ##
			if (!isset($tmpTemp)) $tmpTemp = tempnam("/tmp/", "tableTemp"); // create
			@chmod($tmpTemp, 0777); // chmod
			$logger->info("File: ".$tmpTemp);
			$fpTemp = fopen($tmpTemp, "w"); // open
			if (!$rs->EOF) {
				// save odbc columns
				$metaTables = $adodb->MetaTables();
				foreach ($metaTables as $k => $v) $metaTables[$k] = strtolower($v);

				if (! is_int(array_search("qb_config_odbc", $metaTables))) {
					$rs2 = $adodb->Execute("CREATE TABLE qb_config_odbc ".
					" ( id INTEGER, serialize TEXT, PRIMARY KEY (id) )");
					if (!$rs2) return $logger->error($adodb->ErrorMsg());
				}

				$record = array();
				$record['id'] = $idConfig;
				$record['serialize'] = serialize(array_keys($rs->fields));
				$rs2 = $adodb->Execute("SELECT * FROM qb_config_odbc ".
				" WHERE id = ? ", array($record['id']));
				$sql = '';
				if (!$rs2) return $logger->error($adodb->ErrorMsg());
				if ($rs2->EOF) {
					$sql = $adodb->GetInsertSQL($rs2, $record);
				} else {
					$sql = $adodb->GetUpdateSQL($rs2, $record);
				}
				if ($sql) {
					$rs2 = $adodb->Execute($sql);
					if (!$rs2) $logger->error($adodb->ErrorMsg());
				}

				$columnTable = implode(",", array_keys($rs->fields));
				$metaCol = $adodb->MetaColumns("qb_temp_".$tableid);
				if (!is_array($metaCol)) {
					return $logger->error("Columns must be defined ".
					" on qb_temp_".$tableid."!");
				}
				foreach ($metaCol as $k => $v) {
					if (!array_key_exists($v->name, $rs->fields)
					&& $v->name != 'crc') {
						$adodb->Execute("ALTER TABLE qb_temp_".$tableid.
						" DROP ".$v->name);
					}
				}
			}

			$logger->info("Progress: ".$j." record(s)");
			while (!$rs->EOF) {
				foreach ($rs->fields as $k => $v) {
					$rs->fields[$k] = addcslashes($v, "\t\\");
				}
				$buffer = addcslashes(implode("\t", $rs->fields), "\n");
				$crc32 = crc32($buffer);
				fwrite($fpTemp, $buffer."\t".$crc32."\n");

				$j++;
				if ($j%1000===0) $logger->info("Progress: ".$j." record(s)");

				$rs->MoveNext();
			}
			fclose($fpTemp);

			$query = ("LOAD DATA INFILE '".addslashes($tmpTemp)."' ".
			" INTO TABLE qb_temp_".$tableid." (".$columnTable.", crc);");
			$logger->info($query);
			$rs = $adodb->Execute($query);
			if (!$rs) return $logger->error($adodb->ErrorMsg());

			if (file_exists($tmpTemp)) @unlink($tmpTemp);
		}
		$logger->info("Total: ".$j." record(s)");
		return 1;
	}

?>